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


$accountingSwitchDate = "2017-12-01";

$function = isset($_REQUEST['f'])?$_REQUEST['f']:null;
if($function != null){
    if($function == "processCommissions"){
        set_time_limit(0);
        processCommissions($_REQUEST['fdate'], $_REQUEST['tdate']);   
    }elseif($function == "deleteCommissionRule"){
        deleteCommissionRule($_REQUEST['id']);
    }
    elseif($function == "downloadCompletePaymentCommissionReport"){
        downloadCompletePaymentCommissionReport($_REQUEST['fdate'],$_REQUEST['tdate'], $_REQUEST['st'], $_REQUEST['sm']);
    }elseif($function == "downloadPaymentCommissionReportSummary"){
        downloadPaymentCommissionReportSummary($_REQUEST['fdate'],$_REQUEST['tdate']);
    }elseif($function == "processCompletePaymentCommission"){
        processCompletePaymentCommission($_REQUEST['fdate'], $_REQUEST['tdate']);
    }elseif($function == "saveNewRule"){
        saveNewRule($_REQUEST['line'], $_REQUEST['group'], $_REQUEST['rate']);
    }
}

//$specialCustomers = array('301233', '301230', '311679', '320311', '322316', '322324', '301201');
$specialCustomers = array();
function processCommissions($fromDate, $toDate){
    global $con;
    $query = "SELECT * FROM NEW_COMMISSION_RULES ORDER BY `Line`, `Group` ASC;";
    $result = mysqli_query($con, $query);
    $endResult = true;
    while($row = mysqli_fetch_array($result)){
        $ruleLine = $row['Line'];
        $ruleGroup = $row['Group'];
        if(strlen($ruleLine) == 0){
            $ruleLine = "___";
        }else{
            $ruleLine = str_pad($ruleLine,3," ",STR_PAD_RIGHT);
        }
        if(strlen($ruleGroup) == 0){
            $ruleGroup = "___";
        }else{
            $ruleGroup = str_pad($ruleLine,3," ",STR_PAD_RIGHT);
        }
        $lineCode = $ruleLine . $ruleGroup;
        if($lineCode == "ZZZZZZ"){
            $lineCode = "%";
        }
        $commQuery = "UPDATE NEW_COMMISSION_PAYMENT_HEADER JOIN NEW_COMMISSION_PAYMENT_DETAIL USING(InvoiceNumber) SET CommissionRate = " . $row['Rate'] . ", CommissionAmount = Price*" . $row['Rate'] . " WHERE PaidDate BETWEEN '$fromDate' AND '$toDate' AND LineCode LIKE '$lineCode' AND CommissionRate IS NULL;";
        $endResult = $endResult & mysqli_query($con, $commQuery);
        
    }
    if($endResult){
        echo "true";
    }
}
function saveNewRule($Line, $Group, $Rate){
    global $con;
    $query = "INSERT INTO NEW_COMMISSION_RULES (`Line`, `Group`, `Rate`) VALUES ('$Line', '$Group', $Rate);";
    $result = mysqli_query($con, $query);
    if($result){
        echo "true";
    }else{
        echo mysqli_error($con);
    }
}
function deleteCommissionRule($id){
    global $con;
    $query = "DELETE FROM NEW_COMMISSION_RULES WHERE id = " . $id;
    $result = mysqli_query($con,$query);
    if($result){
        echo "true";
    }else{
        echo mysqli_error($con);
    }
}

function downloadPaymentCommissionReportSummary($fromDate, $toDate){
    global $con;
    $fileName = "PaymentCommissionSummary-$fromDate--$toDate.csv";
    
    $filePath = "downloads/";
    $fp = fopen($filePath . $fileName, 'w');
    
    $query = "SELECT NEW_COMMISSION_PAYMENT_HEADER.ST, Salesman, ROUND(SUM(PaymentAmt),2) as TotalPayment, ROUND(SUM(Discount),2) as TotalDiscount, ROUND(SUM(`InvoiceTotal`),2) as TotalInvoice, ROUND(SUM(IVU),2) AS TotalTax, ROUND(SUM(CommissionAmount),2) as TotalCommission FROM NEW_COMMISSION_PAYMENT_HEADER JOIN NEW_COMMISSION_PAYMENT_DETAIL USING(InvoiceNumber) WHERE PaidDate BETWEEN '$fromDate' AND '$toDate' GROUP BY NEW_COMMISSION_PAYMENT_HEADER.ST, Salesman";
    $result = mysqli_query($con, $query);
    
    $title = array("Location", "Salesman", "TotalPayment", "TotalDiscount", "TotalInvoice", "TotalTax", "TotalCommission");
    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 downloadCompletePaymentCommissionReport($fromDate, $toDate, $ST, $Salesman = "ALL"){
    global $con;
    $fileName = "PaymentCommission-Location$ST-$fromDate--$toDate-Salesman$Salesman.csv";
    
    if($Salesman == "ALL"){
        $SalesmanID = "%";
    }else{
        $SalesmanID = $Salesman;
    }
    if($ST == "ALL"){
        $ST = "%";
    }
    $filePath = "downloads/";
    $fp = fopen($filePath . $fileName, 'w');
    
    $query = "SELECT NEW_COMMISSION_PAYMENT_HEADER.ST, Salesman, NEW_COMMISSION_PAYMENT_DETAIL.AccountNumber, InvoiceNumber, LineCode, PartNumber, Description, Price, GrossMargin, MarginPercent, CommissionRate, CommissionAmount FROM NEW_COMMISSION_PAYMENT_HEADER JOIN NEW_COMMISSION_PAYMENT_DETAIL USING(InvoiceNumber) WHERE PaidDate BETWEEN '$fromDate' AND '$toDate' AND Salesman LIKE '$SalesmanID' AND NEW_COMMISSION_PAYMENT_HEADER.ST LIKE '$ST'";
    $result = mysqli_query($con, $query);
    
    $title = array("Location", "Salesman", "AccountNumber", "InvoiceNumber", "LineCode", "PartNumber", "Description", "Price", "GrossMargin", "MarginPerent", "CommissionRate", "CommissionAmount");
    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 downloadIncompletePaymentsTaxReport($fromDate, $toDate, $ST){
    global $con;
    $fileName = "IncompletePaymentsTaxDetails-Location$ST-$fromDate--$toDate.csv";
    $filePath = "downloads/";
    $fp = fopen($filePath . $fileName, 'w');
    $query = "SELECT ST, AccountNumber, CustomerName, InvoiceNumber, PaidDate, SaleDate, PaymentAmt, Discount, InvoiceTotal, IVU, IVU_E, IVU_M, TaxCode FROM PAYMENT_TAX_DETAIL_EXCEPTIONS WHERE PaidDate BETWEEN '$fromDate' AND '$toDate' AND ST LIKE '$ST';";
    
    $result = mysqli_query($con, $query);
    
    $title = array("Location", "AccountNumber", "CustomerName", "InvoiceNumber", "PaidDate", "SaleDate", "PaymentAmount", "Discount", "InvoiceTotal", "IVU", "IVU_E", "IVU_M", "TaxCode");
    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 clearWorkingTables(){
        global $con;
        $query = "TRUNCATE COMMISSION_SALES_RPT;";
        $query .= "TRUNCATE COMMISSION_PAID_INVOICE_RPT;";
        $query .= "TRUNCATE COMMISSION_SELECTIVE_SPECIAL_CUSTOMER;";
        $query .= "TRUNCATE COMMISSION_SELECTIVE_SPECIAL_ITEM;";
        mysqli_multi_query($con, $query);
        
        
    }
function processCompletePaymentCommission($fromDate, $toDate){
    global $con, $accountingSwitchDate;
    $query = "DELETE NEW_COMMISSION_PAYMENT_HEADER, NEW_COMMISSION_PAYMENT_DETAIL FROM NEW_COMMISSION_PAYMENT_HEADER left JOIN NEW_COMMISSION_PAYMENT_DETAIL USING(InvoiceNumber) WHERE  PaidDate BETWEEN '$fromDate' AND '$toDate'";
    
    mysqli_query($con, $query);
    $query = "INSERT INTO NEW_COMMISSION_PAYMENT_HEADER
(SELECT
	0, 
	SALES_HISTORY_HEADER.ST, 
	SALES_HISTORY_HEADER.InvoiceNumber, 
	PAID_INVOICE.AccountNumber, 
	CustomerName, 
	PaidDate,
	Sum(PaymentAmt) as PaymentAmt,
	Sum(Discount) as Discount,
	subtotal+ivu as InvoiceTotal, 
	Subtotal,
	`SaleDate`,
	SALES_HISTORY_HEADER.TaxCode,
	IVU
FROM (
		PAID_INVOICE
	LEFT JOIN 
		SALES_HISTORY_HEADER on(SALES_HISTORY_HEADER.InvoiceNumber = PAID_INVOICE.InvoiceNumber and PAID_INVOICE.AccountNumber = SALES_HISTORY_HEADER.AccountNumber)
	) 
	WHERE  
		PaidDate BETWEEN '$fromDate' AND '$toDate 23:59' AND
		SaleDate >= '$accountingSwitchDate'
	GROUP BY InvoiceNumber, AccountNumber
    HAVING (PaymentAmt+Subtotal+IVU+Discount) = 0 AND PaymentAmt < 0);";
    if(mysqli_query($con, $query)){
        $query = "INSERT INTO NEW_COMMISSION_PAYMENT_DETAIL
        (SELECT 0, SaleDate, SALES_HISTORY_DETAIL.ST, Salesman, Price, GrossMargin, MarginPercent, PartNumber, Description, NEW_COMMISSION_PAYMENT_HEADER.InvoiceNumber, NEW_COMMISSION_PAYMENT_HEADER.AccountNumber, LineCode, null, null FROM `NEW_COMMISSION_PAYMENT_HEADER` JOIN SALES_HISTORY_DETAIL USING(InvoiceNumber) WHERE PaidDate BETWEEN '$fromDate' AND '$toDate' ORDER BY SaleDate, InvoiceNumber)";
        
        if(mysqli_query($con, $query)){
            echo "true";
        }
        
    }else{
        echo mysqli_error($con);
    }
}
function processPaymentTaxExceptions($fromDate, $toDate){
    global $con, $accountingSwitchDate;
    $query = "DELETE FROM PAYMENT_TAX_DETAIL_EXCEPTIONS WHERE PaidDate BETWEEN '$fromDate' AND '$toDate'";
    mysqli_query($con, $query);
    $query = "INSERT INTO PAYMENT_TAX_DETAIL_EXCEPTIONS
(SELECT
	0, 
	SALES_HISTORY_HEADER.ST, 
	SALES_HISTORY_HEADER.InvoiceNumber, 
	PAID_INVOICE.AccountNumber, 
	CustomerName, 
	PaidDate,
	Sum(PaymentAmt) as PaymentAmt,
	Sum(Discount) as Discount,
	subtotal+ivu as InvoiceTotal, 
	Subtotal,
	`SaleDate`,
	SALES_HISTORY_HEADER.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 (
		PAID_INVOICE
	LEFT JOIN 
		SALES_HISTORY_HEADER on(SALES_HISTORY_HEADER.InvoiceNumber = PAID_INVOICE.InvoiceNumber and PAID_INVOICE.AccountNumber = SALES_HISTORY_HEADER.AccountNumber)
	) 
	left JOIN 
		CUSTOMER_TAX_CODE USING(TaxCode)
	WHERE  
		PaidDate BETWEEN '$fromDate' AND '$toDate' AND
		SaleDate >= '$accountingSwitchDate'
	GROUP BY InvoiceNumber, AccountNumber
	HAVING 	subtotal+IVU + PaymentAmt+discount != 0 AND PaymentAmt < 0);";
    if(mysqli_query($con, $query)){
        echo "true";
        
    }else{
        echo mysqli_error($con);
    }
    
}
?>