<?php

    $date = new DateTime();
    $isReturn = 0 ;
    $method = 'cash';
    $cardType = 'credit';
    $createdBy = 1;
    $isAdvance = 0;
    $accountID = 1;
    $today = $date->format('Y-m-d H:i:s');
    
    $S_id1 = array();
    $S_id2 = array();
    $S_id3 = array();
    $S_id4 = array();
    $U_id1 = array();
    $U_id2 = array();
    
    $result1 = DB::table('transactions as a')
                        ->select('b.id', 'b.business_id','b.final_total','b.contact_id')
                        ->leftjoin('transactions as b','a.return_parent_id', '=', 'b.id')
                        ->where('a.type', 'sell_return')
                        ->where('a.payment_status', 'due')
                        ->where('a.final_total', DB::raw('b.final_total - b.round_off_amount'))
                        ->where('b.type', 'sell')
                        ->where('b.payment_status','due')
                        ->get();
    if(count($result1)>0){
        foreach($result1 as $row){
            DB::insert('insert into transaction_payments (transaction_id, business_id, is_return,amount, method, card_type, paid_on, created_by,is_advance, payment_for, payment_ref_no, account_id, created_at, updated_at ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [$row->id,$row->business_id,$isReturn,$row->final_total, $method, $cardType, $today,$createdBy,$isAdvance,$row->contact_id,'Auto Payment',$accountID,$today,$today]);
            $id1 = DB::getPdo()->lastInsertId();
            array_push($S_id1, $id1);
        }
    }
    
    $result2 = DB::table('transactions as a')
                        ->select('a.id', 'a.business_id','a.final_total','a.contact_id')
                        ->leftjoin('transactions as b','a.return_parent_id', '=', 'b.id')
                        ->where('a.type', 'sell_return')
                        ->where('a.payment_status', 'due')
                        ->where('a.final_total', DB::raw('b.final_total - b.round_off_amount'))
                        ->where('b.type', 'sell')
                        ->where('b.payment_status','due')
                        ->get();

    if(count($result2)>0){
        foreach($result2 as $row){
            DB::insert('insert into transaction_payments (transaction_id, business_id, is_return,amount, method, card_type, paid_on, created_by,is_advance, payment_for, payment_ref_no, account_id, created_at, updated_at ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [$row->id,$row->business_id,$isReturn,$row->final_total, $method, $cardType, $today,$createdBy,$isAdvance,$row->contact_id,'Auto Payment',$accountID,$today,$today]);
            $id2 = DB::getPdo()->lastInsertId();
            array_push($S_id2, $id2);
        }
    }
    $updatedRecord1 = DB::table('transactions as a')
            ->select('a.id')
            ->leftjoin('transaction_payments as b', 'b.transaction_id', '=', 'a.id')
            ->where(DB::raw('b.amount'), DB::raw('a.final_total'))
            ->where('a.payment_status','due')
            ->get();
    foreach($updatedRecord1 as $record) {
        if(!in_array($record->id, $U_id1)){
            array_push($U_id1, $record->id);
        }
    }
    $update1 = DB::table('transactions as a')
                    ->leftjoin('transaction_payments as b', 'b.transaction_id', '=', 'a.id')
                    ->where(DB::raw('b.amount'), DB::raw('a.final_total'))
                    ->where('a.payment_status','due')
                    ->update(['a.payment_status'=>'paid']);
    if($update1 == 0) {
        $U_id1 = array();
    }
    
    $result3 = DB::table('transactions as a')
                        ->select('b.id', 'b.business_id','a.final_total','b.contact_id')
                        ->leftjoin('transactions as b','a.return_parent_id', '=', 'b.id')
                        ->leftjoin(DB::raw("(select transaction_payments.transaction_id, SUM(transaction_payments.amount) AS CTotalAmount FROM transaction_payments GROUP BY transaction_payments.transaction_id
                              ) as c"),function($join){
                                $join->on("b.id","=","c.transaction_id");
                          })
                        ->where('a.type', 'sell_return')
                        ->where('a.payment_status', 'due')
                        ->where('b.type', 'sell')
                        ->where('b.final_total', DB::raw('a.final_total + c.CTotalAmount'))
                        ->where('b.payment_status','partial')
                        ->get();
                        
    if(count($result3)>0){
        foreach($result3 as $row){
            DB::insert('insert into transaction_payments (transaction_id, business_id, is_return,amount, method, card_type, paid_on, created_by,is_advance, payment_for, payment_ref_no, account_id, created_at, updated_at ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [$row->id,$row->business_id,$isReturn,$row->final_total, $method, $cardType, $today,$createdBy,$isAdvance,$row->contact_id,'Auto Payment',$accountID,$today,$today]);
            $id3 = DB::getPdo()->lastInsertId();
            array_push($S_id3, $id3);
        }
    }
    
    $result4 = DB::table('transactions as a')
                    ->select('a.id', 'a.business_id','a.final_total','a.contact_id')
                    ->leftjoin('transactions as b','a.return_parent_id', '=', 'b.id')
                    ->leftjoin(DB::raw("(select transaction_payments.transaction_id, SUM(transaction_payments.amount) AS CTotalAmount FROM transaction_payments GROUP BY transaction_payments.transaction_id
                          ) as c"),function($join){
                            $join->on("b.id","=","c.transaction_id");
                      })
                    ->where('a.type', 'sell_return')
                    ->where('a.payment_status', 'due')
                    ->where('b.type', 'sell')
                    ->where('b.final_total', DB::raw('c.CTotalAmount'))
                    ->where('b.payment_status','partial')
                    ->get();

    if(count($result4)>0){
        foreach($result4 as $row){
            DB::insert('insert into transaction_payments (transaction_id, business_id, is_return,amount, method, card_type, paid_on, created_by,is_advance, payment_for, payment_ref_no, account_id, created_at, updated_at ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [$row->id,$row->business_id,$isReturn,$row->final_total, $method, $cardType, $today,$createdBy,$isAdvance,$row->contact_id,'Auto Payment',$accountID,$today,$today]);
            $id4 = DB::getPdo()->lastInsertId();
            array_push($S_id4, $id4);
        }
    }
                
    $updatedRecord2 = DB::table('transactions as a')
                ->select('a.id')
                ->leftjoin(DB::raw("(select transaction_payments.transaction_id, SUM(transaction_payments.amount) AS CTotalAmount FROM transaction_payments GROUP BY transaction_payments.transaction_id) as c"), function($join){
                         $join->on("c.transaction_id","=","a.id");
                })
                ->where(DB::raw('c.CTotalAmount'), DB::raw('a.final_total'))
                ->where(function ($q) {
                    $q->where('a.payment_status', 'due')->orWhere('a.payment_status', 'partial');
                })
                ->get();
                
    foreach($updatedRecord2 as $record) {
        if(!in_array($record->id, $U_id2)){
            array_push($U_id2, $record->id);
        }
    }
    
    $update2 = DB::table('transactions as a')
                ->leftjoin(DB::raw("(select transaction_payments.transaction_id, SUM(transaction_payments.amount) AS CTotalAmount FROM transaction_payments GROUP BY transaction_payments.transaction_id) as c"), function($join){
                         $join->on("c.transaction_id","=","a.id");
                })
                ->where(DB::raw('c.CTotalAmount'), DB::raw('a.final_total'))
                ->where(function ($q) {
                    $q->where('a.payment_status', 'due')->orWhere('a.payment_status', 'partial');
                })
                ->update(['a.payment_status' => 'paid']);
    if($update2 == 0) {
        $U_id2 = array();
    }
    
    
    $array_value = array(insertR(1,$S_id1), insertR(2,$S_id2), insertR(3,$S_id3), insertR(4,$S_id4), updateR(10,$U_id1), updateR(11,$U_id2));
    
    // *************** Write Log file *************************
    custom_logs($array_value);
    
    // ******************* function to write Log file **********************
    function custom_logs($message) { 
        if(is_array($message)) { 
            $message = json_encode($message); 
        } 
        date_default_timezone_set('Africa/Cairo');
        $current_date = date('m/d/Y h:i:s a', time());
        $file = fopen(base_path()."/resources/views/AutoPayment/paymentLog.log","a"); 
        fwrite($file, "\n" .  $current_date  . " :: " . $message); 
        fclose($file); 
    }
    
    function insertR ($id, $status) {
        if(count($status)>0) {
            return "ID".$id.": Inserted rows Ids is ".implode(",",$status);
        }
        return "ID-".$id.": fail";
    }
    
    function updateR ($id, $status) {
        if(count($status)>0) {
            return "ID".$id.": Updated rows Ids is ".implode(",",$status);
        }
        return "ID-".$id.": fail";
    }
                        
?><?php /**PATH /home/static/public_html/resources/views/AutoPayment/autoPayment.blade.php ENDPATH**/ ?>