<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\ImportDataModel;
use Hash;
use Carbon\Carbon;
use App\Branch;
use App\User;
use App\Transaction;
use App\Payment;




class ImportDataController extends Controller
{
    // STEP 0 => CODE COLUMN ADD IN USER TABLE.
    //        => REMAINING_AMOUNT COLUMN ADD IN BOOKING TABLE.
    //        => VOUCHER NO COLUMN ADD IN INSTALLMENT TABLE.
    //        => MODE COLUMN ADD IN INSTALLMENT TABLE.
    //        => DRAWN ON COLUMN ADD IN INSTALLMENT TABLE.
    //        => INSTRUMENT COLUMN ADD IN INSTALLMENT TABLE.
    //        => INSTRUMENT_DATE COLUMN ADD IN INSTALLMENT TABLE.
    //        => NARRATION COLUMN ADD IN INSTALLMENT TABLE.
    //        => VOUCHER_NO COLUMN ADD IN PAYMENT TABLE.
    //        => NARRATION COLUMN ADD IN PAYMENT TABLE.
    //        => OLD_VOUCHER COLUMN ADD IN TRANSACTION TABLE.




  	// STEP 1 => CALL IMPORTUSERDATA FUNCTION
  	// STEP 2 => CALL IMPORTPLOTDATA FUNCTION
  	// STEP 3 => CALL REPLACE PLOT ID WITH ARTICLE NO IN BOOKING1 PLOT_ID FUNCTION
    // STEP 4 => CALL REPLACE USER ID WITH ARTICLE NO IN BOOKING1 USER_ID FUNCTION
    // STEP 5 => CALL IMPORTBOOKINGDATA FUNCTION
    // STEP 6 => CALL REPLACE BOOKING ID WITH ARTICLE NO IN VOUCHERS1 BOOKING_ID FUNCTION
    // STEP 7 => CALL IMPORTINSTALLMENTDATA FUNCTION
    // STEP 8 => CALL IMPORTPAYMENTDATA FUNCTION



    private $branch_id;
    private $branch_end_date;

    private $ms_no;


    public function __construct()
    {
       $this->setMsNo('RSA/');
    }

    // SETTER AND GETTER
    public function setBranchId($id)
    {
        $branch = new Branch;

        $branch = $branch->getBranchById($id);


        if(isset($branch->id))
        {
            $this->branch_id = $branch->id;
            $this->branch_end_date = $branch->end_date;

        }
        else
        {
           echo 'First Create Branch With Id = '.$id;

           exit;

        }
    }

    public function getBranchId()
    {
        return $this->branch_id;
    }

    public function setMsNo($ms)
    {
        $this->ms_no = $ms;
    }

    public function getMsNo()
    {
        return $this->ms_no;
    }

    // END

    public function importData($branch)
    {
      return \DB::transaction(function () use ($branch)  {

          try
          {
              $this->setBranchId($branch);


              // IMPORT DATA FROM USER1
             $this->importUserData();
              // END

              // IMPORT DATA FROM INVENTORY1
              $this->importPlotData();
              //END
              
              // ATTACH PLOT ORIGNAL ID TO BOOKING1 TABLE ADD PLOT_ID
              $data['parent_table']   = "bookings1";
              $data['parent_column']  = "booking_number";
              $data['column_setter']  = "plot_id";

              $data['child_table']    = "plots";
              $data['child_column']   = "plot_id";
              $data['column_getter']  = "id";

              $import_model = new ImportDataModel;

              $import_model->joinBaceSingleColumnUpdate($data);
              // END


              // ATTACH USER ORIGNAL ID TO BOOKING1 TABLE ADD USER_ID
              $data['parent_table']   = "bookings1";
              $data['parent_column']  = "user_crm";
              $data['column_setter']  = "user_id";

              $data['child_table']    = "users";
              $data['child_column']   = "code";
              $data['column_getter']  = "id";

              $import_model = new ImportDataModel;

              $import_model->joinBaceSingleColumnUpdate($data);
              // END

              // IMPORT DATA FROM BOOKING 1
              $this->importBookingData();
              //END

              $this->importPaymentPlan();

            //   // ATTACH USER ORIGNAL ID TO BOOKING1 TABLE ADD USER_ID
            //   $data['parent_table']   = "vouchers1";
            //   $data['parent_column']  = "article_number";
            //   $data['column_setter']  = "booking_id";

            //   $data['child_table']    = "bookings";
            //   $data['child_column']   = "reference_number";
            //   $data['column_getter']  = "id";

            //   $import_model = new ImportDataModel;

            //   $import_model->joinBaceSingleColumnUpdate($data);
            //   // END

            //   // IMPORT DATA FROM VOUCHER 1
            //   $this->importInstallmentData();
            //   //END

            //   // IMPORT DATA FROM INSTALLMENT
            //   $this->importPaymentData();
            //   //END

            //   // IMPORT DATA FROM INSTALLMENT
            //   $this->importVoucherData();
              //END
        
          } 
          catch (\Exception $e) 
          {
             echo $e;
          }

      });
        

    }

    public function importUserData()
    {

  		$import_model = new ImportDataModel;

  		$export_user_list = $import_model->exportTableData('users1');

  		$import_users = array();

  		foreach ($export_user_list as $rows) 
  		{
  			$import_users[] = [

                'branch_id'      =>  '1', // RED SUN BRANCH KEY 1
                'code'           =>  $rows->crm,
                'name'           =>  $rows->name,
                'fname'          =>  $rows->father_name,
                'cnic'           =>  $rows->cnic,
                'email'          =>  $this->autoGenerateEmail($rows->email,$rows->crm),
                'password'       =>  $this->autoGeneratePassword($rows->crm),
                'contact'        =>  $rows->contact_num,
                'other_contact'  =>  $rows->other_contact,
                'nationality'    =>  $rows->nationality,
                'temp_address'   =>  $rows->temp_address,
                'per_address'    =>  $rows->perm_address,
                'type'    		   =>  'Customer',
                'status'         =>  'Active',
                'dp'    		     =>  $this->generateImage(''),
                'created_at'     =>  now(),
            ];
  		}

  	    $import_model->storeTableData('users',$import_users);
    }

    public function importPlotData()
    {

  		$import_model = new ImportDataModel;

  		$export_plot_list = $import_model->exportTableData('inventory1');

  		$import_plots = array();

  		foreach ($export_plot_list as $rows) 
  		{

  			$import_plots[] = [

                'branch_id'               =>  '2', 
                'status'                  =>  'unsold',
                'plot_id'                 =>  $rows->article_number,
                'type'                    =>  $rows->type,
                'category'                =>  $rows->category,
                'size'                    =>  $rows->area,
                'rate'                    =>  $rows->price,
                'dimensions'              =>  str_replace(' ','',strtolower($rows->dimensions)),
                'feature_description'     =>  $rows->feature_description,


                'created_at'     =>  now(),
            ];
  		}

  	    $import_model->storeTableData('plots',$import_plots);
    }

    public function importBookingData()
    {

      $import_model = new ImportDataModel;

      $export_booking_list = $import_model->exportTableData('bookings1');

      $import_booking = array();

      $sold_plot_list = array();

      $counter = 0;

      foreach ($export_booking_list as $rows) 
      {
        $date = $import_model->getMixByTable('vouchers1','date','article_number',$rows->booking_number);

        array_push($sold_plot_list,$rows->plot_id);

        $import_booking[] = [


                'branch_id'                => $this->getBranchId(),
                'user_id'                  => $rows->user_id,
                'plot_id'                  => $rows->plot_id,
                'ms'                       => $this->generateMsNumber($counter),
                'file_number'              => NULL,
                'reference_number'         => $rows->booking_number,
                'feature_description'      => NULL,
                'discount'                 => 0,
                'feature_charges'          => 0,
                'rate_per_marla'           => $rows->agreed_amount,
                'agreed_amount'            => $rows->agreed_amount,
                // 'remaining_amount'         => $rows->balance,
                'remaining_amount'         => $rows->agreed_amount,

                'receipt_number'           => NULL,
                'token_amount'             => NULL,
                'mode'                     => NULL,
                'drawn_on'                 => NULL,
                'instrument'               => NULL,
                'instrument_date'          => NULL,
                'finger_print'             => NULL,
                'print_width'              => NULL,
                'raw_data'                 => NULL,
                'remarks'                  => NULL,
                'is_reserve'               => NULL,
                'status'                   => 'Approve',
                'is_reserve'               => '0',
                'reserve_edate'            => NULL,
                // 'created_at'               => $date,
                // 'approve_date'             => $date,
                'created_at'               => \EF::dateHtml($rows->booking_date),
                'approve_date'             => \EF::dateHtml($rows->booking_date),
                'down_payment_amount'      => $rows->down_payment,
                'possession_amount'        => $rows->possession,
                'installment_amount'       => $rows->monthly_instalment,
                'number_of_installment'    => $rows->num_of_instalments,

                'down_payment_date'        => $rows->down_payment_date,
                'instalment_start_date'    => $rows->instalment_start_date,
                'possession_date'          => $rows->possession_date,

  
            ];



            $counter++;
      }

        $import_model->storeTableData('bookings',$import_booking);

        $import_model->updateTableData('plots','id',$sold_plot_list,['status' => 'sold']);

    }

    public function importPaymentPlan()
    {
        $import_model = new ImportDataModel;

        $export_booking_list = $import_model->exportTableData('bookings');

        $import_installment = array();

        foreach ($export_booking_list as $booking) 
        {

            $due_date = \EF::dateHtml($booking->down_payment_date);
            // DOWN PAYMENT
            $import_installment [] =
            [
                    'booking_id'              =>  $booking->id,         
                    'user_id'                 =>  $booking->user_id,
                    'branch_id'               =>  $booking->branch_id,
                    'status'                  =>  'Unpaid',
                    'type_of_installment'     =>  \EF::getPlanType()[1],
                    'description'             =>  \EF::getPlanType()[1],
                    'actual_amount'           =>  $booking->down_payment_amount,
                    'amount'                  =>  $booking->down_payment_amount,
                    'received_amount'         =>  0,
                    'due_date'                =>  $due_date,
                    'created_at'              =>  now(),
            ];


            // END DOWN PAYMENT
            $due_date = \EF::dateHtml($booking->instalment_start_date);

            // INSTALLMENT PLAN
            for($i = 1; $i <= $booking->number_of_installment; $i++)
            {

                $import_installment [] =
                [
                        'booking_id'              =>  $booking->id,         
                        'user_id'                 =>  $booking->user_id,
                        'branch_id'               =>  $booking->branch_id,
                        'status'                  =>  'Unpaid',
                        'type_of_installment'     =>  \EF::getPlanType()[8],
                        'description'             =>  \EF::getPlanType()[8].' '.$i,
                        'actual_amount'           =>  $booking->installment_amount,
                        'amount'                  =>  $booking->installment_amount,
                        'received_amount'         =>  0,
                        'due_date'                =>  $due_date,
                        'created_at'              =>  now(),
                ];


                $due_date = Carbon::parse($due_date)->addMonths(3);


            

            }

            // END INSTALLMENT PLAN

            // FINAL AMOUNT / POSSESSION AMOUNT 
            $due_date = \EF::dateHtml($booking->possession_date);
            
            
            

            $import_installment [] =
            [
                    'booking_id'              =>  $booking->id,         
                    'user_id'                 =>  $booking->user_id,
                    'branch_id'               =>  $booking->branch_id,
                    'status'                  =>  'Unpaid',
                    'type_of_installment'     =>  \EF::getPlanType()[6],
                    'description'             =>  \EF::getPlanType()[6],
                    'actual_amount'           =>  $booking->possession_amount,
                    'amount'                  =>  $booking->possession_amount,
                    'received_amount'         =>  0,
                    'due_date'                =>  $this->branch_end_date,
                    'created_at'              =>  now(),
            ];

            // END FINAL / POSSESSION AMOUNT
           
        }



        $import_model->storeTableData('installments',$import_installment);
    }

    public function importInstallmentData()
    {
        $import_model = new ImportDataModel;

        $export_booking_list = $import_model->exportTableData('bookings');

        $import_installment = array();


        foreach ($export_booking_list as $rows) 
        {

          
          $export_installment_list = $import_model->getTableDataByColumn('vouchers1','booking_id',$rows->id,'date');

          $counter = 0;

          foreach ($export_installment_list as $ex_rows) {

            $import_installment[] = [

                  'booking_id'              =>  $rows->id ,         
                  'user_id'                 =>  $rows->user_id,
                  'branch_id'               =>  $rows->branch_id,
                  'status'                  =>  'Paid',
                  'type_of_installment'     =>  'Installment',
                  'description'             =>  'Installment '.++$counter,
                  'actual_amount'           =>  $ex_rows->amount,
                  'amount'                  =>  $ex_rows->amount,
                  'received_amount'         =>  $ex_rows->amount,
                  'due_date'                =>  $ex_rows->date,
                  'created_at'              =>  now(),
                  'mode'                    =>  ucfirst($ex_rows->payment_mode),
                  'drawn_on'                =>  $ex_rows->drawn_bank,
                  'instrument'              =>  $ex_rows->cheque_number,
                  'voucher_no'              =>  $ex_rows->voucher_number,
                  'narration'              =>  $ex_rows->narration,

                  'instrument_date'         =>  NULL,

              ];
           
          }

          if($rows->remaining_amount > 0 && $rows->remaining_amount < $rows->agreed_amount)
          {
              $import_installment[] = [

                  'booking_id'              =>  $rows->id ,         
                  'user_id'                 =>  $rows->user_id,
                  'branch_id'               =>  $rows->branch_id,
                  'status'                  =>  'Unpaid',
                  'type_of_installment'     =>  'Installment',
                  'description'             =>  'Installment '.++$counter,
                  'actual_amount'           =>  $rows->remaining_amount,
                  'amount'                  =>  $rows->remaining_amount,
                  'received_amount'         =>  0,
                  'due_date'                =>  $rows->approve_date,
                  'narration'              =>  $ex_rows->narration,

                  'created_at'              =>  now(),
                  'mode'                    =>  NULL,
                  'drawn_on'                =>  NULL,
                  'instrument'              =>  NULL,
                  'voucher_no'              =>  NULL,
                  'instrument_date'         =>  NULL,

              ];
          }

          
        }

        $import_model->storeTableData('installments',$import_installment);
    }

    public function importPaymentData()
    {
        $import_model = new ImportDataModel;

        $export_installment_list = $import_model->getTableDataByColumn('installments','status','Paid','booking_id');

        $import_payment = array();


        foreach ($export_installment_list as $rows) 
        {
          
            $import_payment[] = [

                'booking_id'          =>  $rows->booking_id,
                'branch_id'           =>  $rows->branch_id,
                'installment_id'      =>  $rows->id,
                'user_id'             =>  $rows->user_id,
                'status'              =>  'Approve',
                'description'         =>  $rows->description,
                'amount'              =>  $rows->received_amount,
                'voucher_no'          =>  $rows->voucher_no,
                'payment_type'        =>  $rows->type_of_installment,
                'mode'                =>  $rows->mode,
                'drawn_on'            =>  $rows->drawn_on,
                'instrument'          =>  $rows->instrument,
                'instrument_date'     =>  $rows->instrument_date,
                'created_at'          =>  $rows->due_date,
                'narration'           =>  $rows->narration,


            ];
        }

        $import_model->storeTableData('payments',$import_payment);
    }

    public function importVoucherData()
    {
        $import_model = new ImportDataModel;

        $export_payment_list = $import_model->getTableDataByColumn('payments','status','Approve','booking_id');

        foreach ($export_payment_list as $rows) 
        {
          
            $import_model->storeTransaction($rows);
        }

        echo 'Voucher succssfully insert.';

    }

    public function AttachDataOtherTable()
    {  
        // // ATTACH PLOT ORIGNAL ID TO BOOKING1 TABLE ADD PLOT_ID
        // $data['parent_table']   = "bookings1";
        // $data['parent_column']  = "booking_number";
        // $data['column_setter']  = "plot_id";

        // $data['child_table']    = "plots";
        // $data['child_column']   = "plot_id";
        // $data['column_getter']  = "id";

        // $import_model = new ImportDataModel;

        // $import_model->joinBaceSingleColumnUpdate($data);


        // // ATTACH USER ORIGNAL ID TO BOOKING1 TABLE ADD USER_ID
        // $data['parent_table']   = "bookings1";
        // $data['parent_column']  = "user_crm";
        // $data['column_setter']  = "user_id";

        // $data['child_table']    = "users";
        // $data['child_column']   = "code";
        // $data['column_getter']  = "id";

        // $import_model = new ImportDataModel;

        // $import_model->joinBaceSingleColumnUpdate($data);
    }


    // GENERIC FUNCTION
     public function autoGenerateEmail($email,$crm_no)
    {
        if(trim(strlen($email)) <= 0)
        {
            $email = $crm_no.'@redsun.com';
        }
        else if($email == "0")
        {
            $email = $crm_no.'@redsun.com';
        }
        
        return $email;
    }

    public function autoGeneratePassword($crm_no)
    {
        return Hash::make($crm_no.'@@re#sun.com');
    }


    public function generateImage($image,$loaded_image = '/graphics/logo/user.png')
    {
        if((isset($image) && trim(strlen($image)) > 0))
        {
            $image =  \EF::uploadFile($image);

        }
        else
        {
            if(strlen(trim($loaded_image)) > 0)
            {
                $image =  $loaded_image;
            }
            else
            {
               $image = '/graphics/logo/user.png'; 
            }    
        }

           
        return $image;
    }

    public function generateMsNumber($count)
    {
        $import_model = new ImportDataModel;

        $insert_id = $import_model->getMaxByTable('bookings','id');

        if($insert_id > 0)
        {
          $count = $insert_id;
        }

        return  $this->getMsNo().str_pad(($count+1), 4, '0', STR_PAD_LEFT); 
    }


    public function importVendorOpeningBalance()
    {
        $import_model = new ImportDataModel;

        $vendor_list = $import_model->exportTableData('redsun_vendor');
        
        $cnic = 0;


       $account_info = \EF::getAccountInfo();

     
        $count = 0;
        
        foreach ($vendor_list as $rows) {

            $cnic = str_pad(($cnic+1), 5, '0', STR_PAD_LEFT); 
        
            $user = User::where('name',trim($rows->vendor_name))->first();

            if(!isset($user->id))
            {
                $user = new User;

                $user->name = $rows->vendor_name;

                $user->email = $user->autoGenerateEmail(NULL);

                $user->password = $user->autoGeneratePassword(NULL);

                $user->cnic = $cnic.'-0000000-0';

                $user->save();

                $user->saveRoleValue($user, 'Vendor');
            }

            $branch = Branch::where('name',trim($rows->project_name))->first();

            if(!isset($branch->id))
            {
                $branch = new Branch;

                $branch->code = $rows->project_code;

                $branch->name = $rows->project_name;


                $branch->save();
            }

            $rows->balance = (double)str_replace(',','',$rows->balance);

            if($rows->balance != 0)
            {
                if($rows->balance > 0)
                {
                    $debit_account = $account_info['control account'];
                    $credit_account = $account_info['vendor'];

                }
                else
                {
                    $debit_account = $account_info['vendor'];
                    $credit_account = $account_info['control account'];

                }

                

               $jv_data['compound_type']            =  "General";
               $jv_data['voucher_list']             =  'JV';
               $jv_data['date']                     =  date('Y-m-d');
               $jv_data['auto_jv']                  =  1;
               $jv_data['role'][0]                  =  5;
               $jv_data['receipt']                  =  NULL;
               $jv_data['branch'][0]                =  $branch->id;
               $jv_data['user'][0]                  =  $user->id;
               $jv_data['booking'][0]               =  NULL;
               $jv_data['amount'][0]                =  abs($rows->balance);
               $jv_data['narration'][0]             =  'Opening balance against '.$user->name.' ('.$branch->code.' - '.$branch->name.')';
               $jv_data['payment_mode'][0]          =   NULL;
               $jv_data['instrument'][0]            =   NULL;
               $jv_data['instrument_date'][0]       =   NULL;
               $jv_data['drawn'][0]                 =   NULL;
               $jv_data['generic_type'][0]          =   0;
               $jv_data['payment_type'][0]          =   'Opening Balance AutoJV';
               $jv_data['payment'][0]               =   0;
               $jv_data['generic'][0]               =   0;
               $jv_data['deposit_no'][0]            =   null;
               $jv_data['deposit_date'][0]          =   null;
               $jv_data['debit_subhead'][0]            =   $debit_account;
               $jv_data['credit_subhead'][0]           =   $credit_account;

              $payment = new Payment;

              $curr_voucher                     = $payment->createVoucher($jv_data);

              $voucher_data['status']           = 'Approve';

              $voucher_data['transaction_id']   = $curr_voucher->id;

              $voucher_data['date']             = now();

              $transaction = new Transaction;

              $approved_voucher = $transaction->updateStatusByTransaction($voucher_data);

              $user->opening_balance = $rows->balance;

              $user->update();

            }
         
            echo $count.'<br>';
            $count++;


        }

    }
    // END

    

}
