<?php
    if(isset($_POST["paramValues"])){
        $paramObj=json_decode($_POST["paramValues"]);
        $obj = new updateDbTable();
        $obj->updateSiteAllocationDetails_InsertInvoiceDetails($paramObj);
    }else{
        echo "NoData Found";
    }
    class updateDbTable{
        function __construct(){
            require_once dirname(__DIR__).'../../php/DB_Connect.php';
            $obj=new DB_conncect();
            $this->db=$obj->connectToDB();
            $this->db_Pdo=$obj->connectToDBVia_PDO();  
        }
        function updateSiteAllocationDetails_InsertInvoiceDetails($paramObj){
            $invoiceDetailsArray=$paramObj->invoiceDetailsArray;
            $siteAllocationArray=$paramObj->siteAllocationArray;
            $totalInvoiceInsertQueries=count($invoiceDetailsArray);
            if($totalInvoiceInsertQueries>0){
                $this->multiQueries_invoiceDetails_and_siteAllocation_queriesExecution($invoiceDetailsArray, $siteAllocationArray, $totalInvoiceInsertQueries);
            }else{
                $this->siteAllocations_queryExecution($siteAllocationArray);
            }
        }
        function multiQueries_invoiceDetails_and_siteAllocation_queriesExecution($invoiceDetailsArray, $siteAllocationArray, $totalInvoiceInsertRows){
            $responseArray=array();
            $stmt1=$this->getStatementOfInsertInvoiceDetails($invoiceDetailsArray);
            $stmt2=$this->getStatementOfUpdateSiteAllocationDetails($siteAllocationArray);
            try{
                $this->db_Pdo->beginTransaction();
                    $stmt1Execution=$stmt1->execution();
                    if($stmt1Execution){
                        $noOfRowsInserted_InStmnt1=$stmt1->rowCount();
                        if($noOfRowsInserted_InStmnt1==$totalInvoiceInsertRows){
                            $stmt2Execution=$stmt2->execute();
                            if($stmt2Execution){
                                $responseArray["message"]="SUCCESS";
                                $responseArray["Reason"]="";
                            }else{
                                $responseArray["message"]="FAILURE";
                                $responseArray["Reason"]="Site Allocation Insert Query Execution Failed";
                            }
                        }else{
                            $responseArray["message"]="FAILURE";
                            $responseArray["Reason"]="NoOf InvoicesDetails Inserted: $noOfRowsInserted_InStmnt1, Total InvoiceDetails: $totalInvoiceInsertRows";
                        }
                    }else{
                        $responseArray["message"]="FAILURE";
                        $responseArray["Reason"]="Invoice Details Insert Query Execution Failed";
                    }
                $this->db_Pdo->commit();
            }
            catch(PDOException $e){
                $responseArray['message']="FAILURE";
                $responseArray["Reason"]="500 Internal Error: $e";
            }
            echo json_encode($responseArray);
        }
        function siteAllocations_queryExecution($siteAllocationArray){
            $responseArray=array();
            $stmt=$this->getStatementOfUpdateSiteAllocationDetails($siteAllocationArray);
            try{
                $this->db_Pdo->beginTransaction();
                    $stmtExecution=$stmt->execute();
                    if(!$stmtExecution){
                        $responseArray["message"]="FAILURE";
                        $responseArray["Reason"]="site Allocation Query Execution Failed";
                    }else{
                        $responseArray["message"]="SUCCESS";
                        $responseArray["Reason"]="";
                    }
                $this->db_Pdo->commit();
            }
            catch(PDOException $e){
                $responseArray["message"]="FAILURE";
                $responseArray["Reason"]="500 Internal Error: $e";
            }
            echo json_encode($responseArray);
        }
        
        function getStatementOfInsertInvoiceDetails($invoiceDetailsArray){
            $query="INSERT INTO `invoices`(`INVOICE_NUMBER`,`INVOICE_RAISED_ON`, `INVOICE_VALUE`,`REPORT_SUBMITED_TO_CUSTOMER_ON`,`REPORT_REMARK`,`PAYMENT_RECEIVED_ON`,`PAYMENT_RECEIVED_VALUE`,`PAYMENT_REMARKS`, `REMAINING_BALANCE_IF_ANY`) VALUES";
            $lastIndex=count($invoiceDetailsArray)-1;
            forEach($invoiceDetailsArray as $currentLoopIndex=>$currentLoopObj){
                if($currentLoopIndex!=$lastIndex){
                    $query.="('".$currentLoopObj->invoiceNo."','".$currentLoopObj->invoiceDate."','".$currentLoopObj->invoiceValue."',
                                '".$currentLoopObj->reportDate."','".$currentLoopObj->reportRemarks."','".$currentLoopObj->paymentDate."',
                                '".$currentLoopObj->paymentValue."','".$currentLoopObj->paymentRemarks."','".$currentLoopObj->remainingBalance."'),";
                }else{// at lastIndex
                   $query.="('".$currentLoopObj->invoiceNo."','".$currentLoopObj->invoiceDate."','".$currentLoopObj->invoiceValue."',
                                '".$currentLoopObj->reportDate."','".$currentLoopObj->reportRemarks."','".$currentLoopObj->paymentDate."',
                                '".$currentLoopObj->paymentValue."','".$currentLoopObj->paymentRemarks."','".$currentLoopObj->remainingBalance."')"; 
                }
            }
            $stmt=$this->db->prepare($query);
            return $stmt;
        }
        
        function getStatementOfUpdateSiteAllocationDetails($siteAllocationArray){
            //on duplicate key method will update multiple queries at a time, else we need to write 1)update 2) update 3) update...... 
            $query="INSERT INTO `site_allocations` (`SITE_ID`,`SITE_ASSIGNED_TO_EMPID`,`SITE_ASSIGNED_DATE`,`PO_NUMBER`,`INVOICE_NUMBER`) VALUES";
            $lastIndex=count($siteAllocationArray)-1;
            forEach($siteAllocationArray as $currentLoopIndex => $currentLoopObj){
                if($currentLoopIndex!=$lastIndex){
                    $query.="('".$currentLoopObj->siteId."', '".$currentLoopObj->empId."', '".$currentLoopObj->siteVisitDate."', '".$currentLoopObj->poNo."', '".$currentLoopObj->invoiceNo."'),"; 
                }else{//if lastIndex
                    $query.="('".$currentLoopObj->siteId."', '".$currentLoopObj->empId."', '".$currentLoopObj->siteVisitDate."', '".$currentLoopObj->poNo."', '".$currentLoopObj->invoiceNo."')  
                            ON DUPLICATE KEY UPDATE `PO_NUMBER`=VALUES(`PO_NUMBER`), `INVOICE_NUMBER`=VALUES(`INVOICE_NUMBER`)";
                }
            }
            $stmt=$this->db->prepare($query);
            return $stmt;
        }
    }
?>