<?php
include_once("../connect.php");
global $con;
include_once("../session.php");

$function = isset($_REQUEST['f'])?$_REQUEST['f']:null;
if($function != null){
    if($function == "getWorkingTableStatus"){
        $table = $_REQUEST['table'];
        getWorkingTableStatus($table);
    }elseif($function == "processCommissions"){
        set_time_limit(0);
        $table = $_REQUEST['table'];    
        processCommissions($table);   
    }elseif($function == "saveWorkingTable"){
        saveWorkingTable($_POST);
    }
    elseif($function == "downloadSalesReport"){
        $type  = "";
        $type = isset($_REQUEST["type"])?$_REQUEST['type']:"";
        downloadSalesReport($_REQUEST['id'],$_REQUEST['branch'], $_REQUEST['salesman'], $type);
    }elseif($function == "downloadPaymentsReport"){
        
        downloadPaymentsReport($_REQUEST['sm'],$_REQUEST['fromDate'], $_REQUEST['toDate']);
        
    }elseif($function == "clearWorkingTables"){
        clearWorkingTables();
    }elseif($function == "processPaymentTaxRegular"){
        processPaymentTaxRegular($_REQUEST['id']);
    }elseif($function == "processPaymentTaxExceptions"){
        processPaymentTaxExceptions($_REQUEST['id']);
    }
}
function getWorkingTableStatus($table){
    global $con;
   if($table == "sales"){
        $query = "SELECT COUNT(ID) as `Count`, ROUND(SUM(Price),2) as TotalRevenue, ROUND(SUM(Commission),2) as TotalCommission, ROUND(SUM(Commission),2)/ROUND(SUM(Price),2) as AverageCommissionRate FROM COMMISSION_SALES_RPT";
        $result = mysqli_query($con, $query);
        $row = mysqli_fetch_array($result,MYSQLI_ASSOC);
        $query2 = "SELECT MAX(`Month`) as ToMonth, MIN(`Month`) as `FromMonth` FROM SALES_HISTORY";
        $result2 = mysqli_query($con, $query2);
        $row2 = mysqli_fetch_array($result2,MYSQLI_ASSOC);
        $row['ToMonth'] = $row2['ToMonth'];
        $row['FromMonth'] = $row2['FromMonth'];
        echo json_encode($row);
   }
   if($table == "payments"){
        $query = "SELECT COUNT(ID) as `Count`, ROUND(SUM(PaymentAmt),2) as TotalPayments, ROUND(SUM(Commission),2) as TotalCommission, ROUND(SUM(Discount),2) as TotalDiscount, ROUND(SUM(Tax),2) as TotalTax FROM COMMISSION_PAID_INVOICE_RPT";
        $result = mysqli_query($con, $query);
        $row = mysqli_fetch_array($result,MYSQLI_ASSOC);
        echo json_encode($row);
   }
   if($table == "specials"){
        $query = "SELECT COUNT(ID) as `Count` FROM COMMISSION_SPECIAL";
        $result = mysqli_query($con, $query);
        $row = mysqli_fetch_array($result,MYSQLI_ASSOC);
        echo json_encode($row);
   }
   if($table == "selective_specials"){
        $query = "SELECT COUNT(ID) as `ItemCount` FROM COMMISSION_SELECTIVE_SPECIAL_ITEM;";
        $query .= "SELECT COUNT(ID) as `CustomerCount` FROM COMMISSION_SELECTIVE_SPECIAL_CUSTOMER;";
        $returnData = array();
        if(mysqli_multi_query($con,$query)){
            do{
                if($result = mysqli_store_result($con)){
                    while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
                        array_push($returnData, $row);
                    }
                    mysqli_free_result($result);
                }
            }while(mysqli_more_results($con) && mysqli_next_result($con));
        }
        echo json_encode($returnData);
   }
   if($table == "salesCount"){
        $query = "SELECT COUNT(ID) as `Count` FROM COMMISSION_SALES_RPT";
        $result = mysqli_query($con, $query);
        $row = mysqli_fetch_array($result,MYSQLI_ASSOC);
        echo json_encode($row);
   }
   
}
//$specialCustomers = array('301233', '301230', '311679', '320311', '322316', '322324', '301201');
$specialCustomers = array();
function processCommissions($table){
    global $con;
    if($table == "sales"){
        //Delete Delivery SKU's
        $query = "DELETE FROM COMMISSION_SALES_RPT USING COMMISSION_SALES_RPT, DELIVERY_OPTION WHERE PartNumber LIKE CONCAT('MIS ', SKU)";
        $result = mysqli_query($con, $query);
        
        $query = "SELECT COMMISSION_SALES_RPT.*, CUSTOMER.Name FROM COMMISSION_SALES_RPT LEFT JOIN CUSTOMER USING(AccountNumber) ORDER BY TransactionDate, AccountNumber ASC";
        $result = mysqli_query($con, $query);
        //$specialCustomers = array('301233', '301230', '311679', '320311', '322316', '322324', '301201');
        $specialCustomers = array();
        while($row = mysqli_fetch_array($result)){
            
            //error_log("Processing: " . $row['AccountNumber'] . "(" . $row['Name'] .  ") - INVOICE: " . $row['InvoiceNumber']);
            if(substr($row['Salesman'],1) == "T"){
                $commissionRate = .07;
            }elseif(isSpecial($row['PartNumber'], $row['AccountNumber'])){
                //error_log("special");
                $commissionRate = .00;
            }elseif(array_search($row['AccountNumber'],$specialCustomers) >= 0 && isSpecial($row['PartNumber'], $row['AccountNumber'])){
                $commissionRate = .00;
            }elseif(substr($row['PartNumber'],0,3) == "GAB" || substr($row['PartNumber'],0,3) == "KYB" || substr($row['PartNumber'],0,3) == "DHI"){
                $commissionRate = .03;
            }elseif($row['MarginPercent'] >= 30.00){
                $commissionRate = .05;
            }elseif($row['MarginPercent'] >= 28.00 && $row['MarginPercent'] <= 29.99){
                $commissionRate = .03;
            }elseif($row['MarginPercent'] >= 27.00 && $row['MarginPercent'] <= 27.99){
                $commissionRate = .02;
            }elseif($row['MarginPercent'] <= 26.99){
                $commissionRate = .00;
            }
            $commission = round($commissionRate*$row['Price'],2);
            
            $query2 = "UPDATE COMMISSION_SALES_RPT SET Commission = " . $commission . ", CommissionRate = " . $commissionRate . " WHERE ID = " . $row['ID'];
            $result2 = mysqli_query($con, $query2);
           // echo $query2 . "\n";
        }
    }
    if($table == "payments"){
        $commissionRate = 0.05;
        
        //EL siguiente query borrara las transacciones que sus ventas fueron realizadas en o antes de:
        $switchMonth = "2017-11";
        
        $query = "DELETE COMMISSION_PAID_INVOICE_RPT FROM COMMISSION_PAID_INVOICE_RPT LEFT JOIN SALES_HISTORY USING(InvoiceNumber) WHERE `Month` <= '$switchMonth'";
        //$result = mysqli_query($con, $query);
        
        $query = "UPDATE COMMISSION_PAID_INVOICE_RPT SET Commission = ((PaymentAmt+Tax) * -$commissionRate), CommissionRate = $commissionRate WHERE Salesman NOT LIKE '%T';";
        $result = mysqli_query($con,$query);
        error_log($query);
        error_log(print_r($result, true));
        $commissionRate = 0.00;
        $query = "UPDATE COMMISSION_PAID_INVOICE_RPT SET Commission = ((PaymentAmt+Tax) * -$commissionRate), CommissionRate = $commissionRate WHERE Salesman LIKE '%T';";
        $result = mysqli_query($con,$query);
        error_log($query);
        error_log(print_r($result, true));
        //The next query will try to match and update payments with the invoice reference. This will not be used to compute, this is only to show the field in the commission report.
        $query = "UPDATE COMMISSION_PAID_INVOICE_RPT LEFT JOIN `SALES_HISTORY` USING(InvoiceNumber) SET COMMISSION_PAID_INVOICE_RPT.Reference = `SALES_HISTORY`.Reference";
        $result = mysqli_query($con,$query);
        error_log($query);
        error_log(print_r($result, true));
        $commissionRate = 0.03;
        $query = "UPDATE COMMISSION_PAID_INVOICE_RPT LEFT JOIN `SALES_HISTORY` USING(InvoiceNumber)  SET Commission = ((PaymentAmt+Tax) * -$commissionRate), CommissionRate = $commissionRate WHERE `SALES_HISTORY`.Reference LIKE '%KYB%' AND `Month` >= '2017-01' AND COMMISSION_PAID_INVOICE_RPT.AccountNumber IN (SELECT AccountNumber FROM PRICE_PLAN_LIST_CUSTOMER WHERE PriceListID = 1);";
        //echo $query;
        $result = mysqli_query($con,$query);
        error_log($query);
        error_log(print_r($result, true));
        paymentSalesmanCustomerSubstitution();
    }
}
function paymentSalesmanCustomerSubstitution(){
    global $con;
    $query = "SELECT * FROM COMMISSION_SALESMAN_PAYMENT_SUBSTITUTION";
    $result = mysqli_query($con,$query);
    $returnData = true;
    while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
        $query2 = "UPDATE COMMISSION_PAID_INVOICE_RPT SET Salesman = '" . $row['SalesmanID'] . "' WHERE AccountNumber = '" . $row['AccountNumber'] . "' AND Job = " . $row['Job'] . ';';
        $returnData = $returnData AND mysqli_query($con,$query2);
    }
    return $returnData;
}
//ITEMS THAT RETURN TRUE FROM THIS FUNCTION WONT GENERTATE COMMISSION ON SALE, BUT WILL PAY COMMISSION ON PAYMENT
function isSpecial($partNumber, $accountNumber = null){
    global $con;
    $line = substr($partNumber,0,3);
    $sku = substr($partNumber,4);
    $returnValue = false;
    $exceptions = ["SLE 00500", "SLE 00524", "SLE 00529"];
    if(in_array($partNumber,$exceptions,true)){
        $returnValue = $returnValue || true;
    }
    if($line == "PTR"){
        $returnValue = $returnValue || true;
        //return true;
    }
    
    if($accountNumber == null){
        $query = "SELECT COUNT(SKU) as `Count` FROM COMMISSION_SPECIAL WHERE Line = '" . $line . "' AND SKU = '" . $sku . "'";
        if($line == "SLE"){
            error_log($query);
        }
        //echo $query . "<br />";
        $result = mysqli_query($con, $query);
        $row = mysqli_fetch_array($result);
        if($row['Count'] > 0){
            $returnValue = $returnValue || true;
        }else{
            $returnValue = $returnValue || false;
        }
    }else{
        $query = "SELECT COUNT(SKU) as `Count` FROM COMMISSION_SPECIAL WHERE Line = '" . $line . "' AND SKU = '" . $sku . "'";
        if($line == "SLE"){
            error_log($query);
        }
        //echo $query . "<br />";
        $result = mysqli_query($con, $query);
        $row = mysqli_fetch_array($result);
        if($row['Count'] > 0){
            $returnValue = $returnValue || true;
        }else{
            $returnValue = $returnValue || false;
        }
        
        $query = "SELECT COUNT(SKU) as `Count` FROM COMMISSION_SELECTIVE_SPECIAL_CUSTOMER, COMMISSION_SELECTIVE_SPECIAL_ITEM WHERE AccountNumber = '" . $accountNumber . "' AND Line = '" . $line . "' AND SKU = '" . $sku . "'";
        $result = mysqli_query($con, $query);
        $row = mysqli_fetch_array($result);
        if($row['Count'] > 0){
            //error_log("Selective-item");
            $returnValue = $returnValue || true;
        }else{
            $returnValue = $returnValue || false;
        }
        $query = "SELECT COUNT(SKU) as `Count` FROM PRICE_PLAN_LIST_CUSTOMER JOIN PRICE_PLAN_LIST_DETAIL USING(PriceListID) WHERE Line = '" . $line . "' AND SKU = '" . $sku . "' AND AccountNumber = '" . $accountNumber . "'";
        $result = mysqli_query($con, $query);
        $row = mysqli_fetch_array($result);
        //error_log($query);
        if($row['Count'] > 0){
            error_log("PP-item-$line-$sku-$accountNumber");
            $returnValue = $returnValue || true;
        }else{
            $returnValue = $returnValue || false;
        }
    }
    return $returnValue;
}
function saveCommissionsRun($data){
    global $con;
    $query = "INSERT INTO ";
    
}
function saveWorkingTable($data){
    global $con;
    $result = array();
    $result["Queries"] = array();
    $result["Errors"] = array();
    $query = "INSERT INTO COMMISSION_HISTORY_HEAD (TransactionDate, User, Period, Notes) VALUES (NOW(), '" . $_SESSION['Username'] . "', '" . $data['period'] . "','" . $data['notes'] . "');";
    $result['Queries']['Head'] = $query;
    mysqli_query($con, $query);
    $result['Errors']['Head'] = mysqli_error($con);
    $id = mysqli_insert_id($con);
    $query = "INSERT INTO COMMISSION_HISTORY_SALES_RPT (RunID, `Branch`, `Salesman`, `Price`, `GrossMargin`, `MarginPercent`, `PartNumber`, `Description`, `InvoiceNumber`, `AccountNumber`, `CustomerName`, `TransactionDate`, `LineCode`, `Commission`, `CommissionRate`) (SELECT $id, `Branch`, `Salesman`, `Price`, `GrossMargin`, `MarginPercent`, `PartNumber`, `Description`, `InvoiceNumber`, `AccountNumber`, `CustomerName`, `TransactionDate`, `LineCode`, `Commission`, `CommissionRate` FROM COMMISSION_SALES_RPT);";
    //$result['Queries']['Sales'] = $query;
    ///mysqli_query($con, $query);
    //$result['Errors']['Sales'] = mysqli_error($con);
    $query .= "INSERT INTO COMMISSION_HISTORY_PAID_INVOICE_RPT (RunID, `Salesman`, `Branch`, `AccountNumber`, `CustomerName`, `InvoiceNumber`, `PaidDate`, `PaymentAmt`, `Discount`, `Tax`, `Job`, `Commission`, `Reference`, `CommissionRate`) (SELECT $id, `Salesman`, `Branch`, `AccountNumber`, `CustomerName`, `InvoiceNumber`, `PaidDate`, `PaymentAmt`, `Discount`, `Tax`, `Job`, `Commission`, `Reference`, `CommissionRate` FROM COMMISSION_PAID_INVOICE_RPT);";
    //$query .= "TRUNCATE COMMISSION_SALES_RPT;";
    //$query .= "TRUNCATE COMMISSION_PAID_INVOICE_RPT;";
    $result['Queries']['Copy'] = $query;
    mysqli_multi_query($con, $query);
    $result['Errors']['Copy'] = mysqli_error($con);
    $result['RunID'] = $id;
    echo json_encode($result);
}
function downloadSalesReport($id, $branch, $salesman, $type = ""){
    global $con;
    if($type == "summary"){
        $fileName = "SalesSummary-Batch$id.csv";
        $filePath = "downloads/";
        $fp = fopen($filePath . $fileName, 'w');
        
        $query = "SELECT Branch, Salesman, SUM(Price) as Ventas, SUM(Commission) as Comisiones, SUM(`GrossMargin`) as GP FROM COMMISSION_HISTORY_SALES_RPT WHERE RunID = $id GROUP BY Branch, Salesman ORDER BY Branch, Salesman, AccountNumber";
        $result = mysqli_query($con, $query);
        $title = array("Location", "Ruta", "Ventas", "Comision", "GP");
        fputcsv($fp, $title,",", '"');
        while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
            fputcsv($fp,$row,",", '"');
        }
        fclose($fp);
        header('Content-type: text/csv'); 
        header('Content-disposition: attachment; filename="'.$fileName .'"');                             
        readfile($filePath . $fileName );          
        exit();
    }else{
            
        $fileName = "SalesDetails-Batch$id-Location$branch-Ruta$salesman.csv";
        $filePath = "downloads/";
        if($branch == "ALL"){
            $branch = "%";
        }
        if($salesman == "ALL"){
            $salesman = "%";
        }
        $fp = fopen($filePath . $fileName, 'w');
        
        $query = "SELECT Branch, Salesman, Price, GrossMargin, MarginPercent, PartNumber, Description, InvoiceNumber, AccountNumber, CustomerName, TransactionDate, Commission, CommissionRate FROM COMMISSION_HISTORY_SALES_RPT WHERE RunID = " . $id . " AND Branch LIKE '" . $branch . "' AND Salesman LIKE '" . $salesman . "'";
        $result = mysqli_query($con, $query);
        $title = array("Location", "Ruta", "Price", "GrossMargin", "MarginPercent", "PartNumber", "Description", "InvoiceNumber", "AccountNumber", "CustomerName", "TransactionDate", "Commission", "CommissionRate");
        fputcsv($fp, $title,",", '"');
        while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
            fputcsv($fp,$row,",", '"');
        }
        fclose($fp);
        header('Content-type: text/csv'); 
        header('Content-disposition: attachment; filename="'.$fileName .'"');                             
        readfile($filePath . $fileName );          
        exit();
    }
}
function downloadPaymentsReport($sm, $fromDate, $toDate){
    global $con;
    
        $fileName = "PaymentsReport_$sm-$fromDate-$toDate.csv";
        $filePath = "downloads/";
        $fp = fopen($filePath . $fileName, 'w');
        
        $query = "SELECT PaymentID,INVOICE_HEAD.AccountNumber, PaymentDate, Processed, PaymentAmount, ROUND(PaymentAmount-IF(PartialPayment, 0, TaxAmount),2) Subtotal, DiscountAmount, IF(PartialPayment, 0, TaxAmount) as Tax, (IF(PartialPayment,0,Tax1Total)) Tax1, (IF(PartialPayment,0,Tax2Total)) Tax2 FROM DETAILED_PAYMENT LEFT JOIN INVOICE_HEAD USING(InvoiceID) WHERE PaymentDate BETWEEN '$fromDate' AND '$toDate 23:59:59' AND DETAILED_PAYMENT.Void = 0 and Salesman = '$sm' GROUP BY PaymentID";
        $result = mysqli_query($con, $query);
        $title = array("PaymentID", "AccountNumber", "PaymentDate", "Processed","PaymentAmount", "Subtotal", "DiscountAmount", "TaxTotal", "Tax1", "Tax2");
        fputcsv($fp, $title,",", '"');
        while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
            if($row['Processed'])
                $row['Processed'] = "Yes";
            else
                $row['Processed'] = "No";
            fputcsv($fp,$row,",", '"');
        }
        fclose($fp);
        header('Content-type: text/csv'); 
        header('Content-disposition: attachment; filename="'.$fileName .'"');                             
        readfile($filePath . $fileName );          
        exit();
    
}


function processPaymentTaxExceptions($RunID){
    global $con;
    $query = "DELETE FROM PAYMENT_TAX_DETAIL_EXCEPTIONS WHERE RunID = $RunID";
    //mysqli_query($con, $query);
    $query = "INSERT INTO PAYMENT_TAX_DETAIL_EXCEPTIONS
SELECT
	0, 
	RunID, 
	Period, 
	Branch, 
	SALES_HISTORY.InvoiceNumber, 
	COMMISSION_HISTORY_PAID_INVOICE_RPT.AccountNumber, 
	CustomerName, 
	PaidDate,
	Sum(PaymentAmt) as PaymentAmt,
	subtotal+ivu as InvoiceTotal, 
	Subtotal,
	`Month`,
	TaxCode,
	IVU,
	IF((PercentA+PercentB)=0,0,round(IVU*(PercentA/(PercentA+PercentB)),2)) as IVU_E, 
	IF((PercentA+PercentB)=0,0,round(IVU*(PercentB/(PercentA+PercentB)),2)) as IVU_M 
FROM (
		COMMISSION_HISTORY_PAID_INVOICE_RPT join COMMISSION_HISTORY_HEAD on(RunID = `COMMISSION_HISTORY_HEAD`.ID) 
	LEFT JOIN 
		SALES_HISTORY on(SALES_HISTORY.InvoiceNumber = COMMISSION_HISTORY_PAID_INVOICE_RPT.InvoiceNumber and COMMISSION_HISTORY_PAID_INVOICE_RPT.AccountNumber = SALES_HISTORY.AccountNumber)
	) 
	left JOIN 
		CUSTOMER_TAX_CODE ON(SALES_HISTORY.AccountNumber = CUSTOMER_TAX_CODE.AccountNumber)  
	WHERE  
		RunID = $RunID
	GROUP BY InvoiceNumber, AccountNumber
	HAVING 	subtotal+IVU + PaymentAmt != 0;";
    if(mysqli_query($con, $query)){
        echo "<h3>Exceptions identified....</h3>";
    }
    
    $query = "SELECT * FROM PAYMENT_TAX_DETAIL_EXCEPTIONS WHERE RunID = $RunID";
    $result = mysqli_query($con, $query);
    while($row = mysqli_fetch_array($result)){
        $query2 = "SELECT
            0,
            $RunID, 
            MAX(Period) Period,
            Branch, 
            SALES_HISTORY.InvoiceNumber, 
            COMMISSION_HISTORY_PAID_INVOICE_RPT.AccountNumber, 
            CustomerName, 
            PaidDate,
            SUM(PaymentAmt) PaymentAmt,
            subtotal+ivu as InvoiceTotal, 
            Subtotal,
            `Month`,
            TaxCode,
            IVU,
            IF((PercentA+PercentB)=0,0,round(IVU*(PercentA/(PercentA+PercentB)),2)) as IVU_E, 
            IF((PercentA+PercentB)=0,0,round(IVU*(PercentB/(PercentA+PercentB)),2)) as IVU_M 
        FROM (
                COMMISSION_HISTORY_PAID_INVOICE_RPT join (SELECT * FROM COMMISSION_HISTORY_HEAD GROUP BY Period HAVING MAX(ID)) as COMMISSION_HISTORY_HEAD on(RunID = `COMMISSION_HISTORY_HEAD`.ID) 
            LEFT JOIN 
                SALES_HISTORY on(SALES_HISTORY.InvoiceNumber = COMMISSION_HISTORY_PAID_INVOICE_RPT.InvoiceNumber and COMMISSION_HISTORY_PAID_INVOICE_RPT.AccountNumber = SALES_HISTORY.AccountNumber)
            ) 
            left JOIN 
                CUSTOMER_TAX_CODE ON(SALES_HISTORY.AccountNumber = CUSTOMER_TAX_CODE.AccountNumber)  
            WHERE  
                SALES_HISTORY.AccountNumber = '" . $row['AccountNumber'] . "' and SALES_HISTORY.InvoiceNumber = '" . $row['InvoiceNumber'] . "'  and RunID <= $RunID
        ";
        if($result2 = mysqli_query($con, $query2)){
            
        }else{
            echo "<h3>Error checking invoice: " . $row['InvoiceNumber'] . " from the Account: " . $row['AccountNumber'] . ".....</h3>";
        }
        
        $row2 = mysqli_fetch_array($result2, MYSQLI_ASSOC);
       // echo $row2['PaymentAmt']*-1 . " = " . $row2['InvoiceTotal'] . "<br>";
        if($row2['PaymentAmt']*-1 == $row2['InvoiceTotal']){
            $query3 = "INSERT INTO PAYMENT_TAX_DETAIL
                SELECT
                0,
                $RunID, 
                MAX(Period) Period,
                Branch, 
                SALES_HISTORY.InvoiceNumber, 
                COMMISSION_HISTORY_PAID_INVOICE_RPT.AccountNumber, 
                CustomerName, 
                PaidDate,
                SUM(PaymentAmt) PaymentAmt,
                subtotal+ivu as InvoiceTotal, 
                Subtotal,
                `Month`,
                TaxCode,
                IVU,
                IF((PercentA+PercentB)=0,0,round(IVU*(PercentA/(PercentA+PercentB)),2)) as IVU_E, 
                IF((PercentA+PercentB)=0,0,round(IVU*(PercentB/(PercentA+PercentB)),2)) as IVU_M 
            FROM (
                    COMMISSION_HISTORY_PAID_INVOICE_RPT join (SELECT * FROM COMMISSION_HISTORY_HEAD GROUP BY Period HAVING MAX(ID)) as COMMISSION_HISTORY_HEAD on(RunID = `COMMISSION_HISTORY_HEAD`.ID) 
                LEFT JOIN 
                    SALES_HISTORY on(SALES_HISTORY.InvoiceNumber = COMMISSION_HISTORY_PAID_INVOICE_RPT.InvoiceNumber and COMMISSION_HISTORY_PAID_INVOICE_RPT.AccountNumber = SALES_HISTORY.AccountNumber)
                ) 
                left JOIN 
                    CUSTOMER_TAX_CODE ON(SALES_HISTORY.AccountNumber = CUSTOMER_TAX_CODE.AccountNumber)  
                WHERE  
                    SALES_HISTORY.AccountNumber = '" . $row['AccountNumber'] . "' and SALES_HISTORY.InvoiceNumber = '" . $row['InvoiceNumber'] . "'
            ";
            if(mysqli_query($con, $query3)){
                echo "<h3>Complete payment detected... Inserted value in tax table....</h3>";
            }
            
            $query3 = "DELETE FROM PAYMENT_TAX_DETAIL_EXCEPTIONS WHERE id = " . $row['id'];
            if(mysqli_query($con, $query3)){
                echo "<h3>Deleted Exception...</h3>";
            }
        }
    }
}
?>