<?php
error_reporting(E_ALL);
ini_set('display_errors', '0');
try {

    if (!isset($_REQUEST['datefrom'])) {
        header('location: ../invoice.php');
        exit;
    } else {
        //$id = $_GET['print'];
    }


    $customer_name = $_GET['customer_name'] ?? 'All';

    include_once('../../../classes/manager.class.php');
    include_once('../../../classes/PHPMailer/PHPMailerAutoload.php');
    include_once('../../../fpdf/fpdf1.php');


    $m = new Manager();
    extract($_REQUEST);
    $customer_name = $customer_name ?? 'All';
    $cat_type = $cat_type ?? 'All';
    $cat_name = $cat_name ?? 'All';
    $customers = $customers ?? '';
    $param_data = ControlConfifguration::GetConfiguration();
    define('PROJECT_NAME', $param_data->name);
    define('PROJECT_ADDRESS', $param_data->address);
    define('PROJECT_TEL', $param_data->contact);
    define('PROJECT_EMAIL', $param_data->email);
    define('PROJECT_WEBSITE', $param_data->website);
    define('PROJECT_COLOR', $param_data->color);
    define('PROJECT_DISCOUNT', $param_data->is_discount);
    define('PROJECT_TAX', $param_data->is_tax);

    if ($datefrom == "") {
        $datefrom = date('Y-m-d', strtotime("-30 days"));
    } else {
        $datefrom = date("Y-m-d", strtotime($datefrom));
    }
    if ($dateto == "") {
        $dateto = date('Y-m-d');
    } else {
        $dateto = date("Y-m-d", strtotime($dateto));
    }
    $datefrom = date("d-m-Y", strtotime($datefrom));
    $dateto = date("d-m-Y", strtotime($dateto));

    $qryDate = date("Y-m-d", strtotime($datefrom));
    $qryTo = date("Y-m-d", strtotime($dateto));

    $filterReport = " JOIN customer_setup AS cs ON cs.id = inv.customer_id WHERE DATE_FORMAT(inv.create_date,'%Y-%m-%d')>='$qryDate' and DATE_FORMAT(inv.create_date,'%Y-%m-%d')<='$qryTo'  AND inv.status not in('Draft', 'Cancel') ";
    if (isset($cat_type) && $cat_type == 'All') {
        if (isset($cat_name) && $cat_name != 'All') {
            $cat = CustomerCategorySetup::GetCustomerCategorySetupById($cat_name);
            $filterCat = " WHERE category_id = '$cat->id' AND status = 'active' ORDER BY name";
            $cust = CustomerSetup::GetCustomerSetupList($filterCat);
            $custArray = array();
            foreach($cust AS $custRow){
                $custArray[] = $custRow->id;
            }
            $customers = implode(',', $custArray);
            $filterReport .= " AND inv.customer_id IN (".$customers.")";
        }
    }else{
        $filterReport .= " AND cs.is_mart = '$cat_type' ";
    }
    if (isset($customer_name) && $customer_name != 'All') {
        $filterReport .= " AND inv.customer_id ='$customer_name'";
    }
    $filterReport .= " AND cs.status ='active'";
    $filterReport .= " UNION SELECT 'payment' AS nameTable, pay.create_date, pay.customer_id, pay.amount,
'', '', '','', pay.status,
pay.withholding_tax, pay.sales_tax, pay.goods_return, pay.shelf_rent, pay.builty_charges, pay.other_expense

 from " . TABLE_PAYMENT . " AS pay 
 
 JOIN customer_setup AS cs ON cs.id = pay.customer_id

 WHERE DATE_FORMAT(pay.create_date,'%Y-%m-%d')>='$qryDate' and DATE_FORMAT(pay.create_date,'%Y-%m-%d')<='$qryTo' AND pay.status <> 'Cancel' ";

    if (isset($cat_type) && $cat_type == 'All') {
        if (isset($cat_name) && $cat_name != 'All') {
            $filterReport .= " AND pay.customer_id IN (".$customers.")";
        }
    }else{
        $filterReport .= " AND cs.is_mart = '$cat_type' ";
    }

    if (isset($customer_name) && $customer_name != 'All') {
        $filterReport .= " AND pay.customer_id ='$customer_name'";
    }
    $filterReport .= " AND cs.status ='active'";
    $filterReport .= " ORDER BY customer_id";
    //Opening Balance

    $invoice = Invoice::GetInvoicePaymentReportListsForOwner123($filterReport);
    if (!empty($invoice)) {
      

        $font = 10;
        $picx = 100;
        $setx = 5;
        $multi = 210;
        $picsetx = 150;
        $w = array(50, 80, 50, 80);
        $path = "../../../";

        $pdf = new FPDFWithOutWaterMark('P', 'mm', 'A4');
        $pdf->SetMargins(0, 0, 0);

        $pdf->SetTitle('Customer Payment Report');
        $pdf->AddPage();
        $pdf->Ln();
        $pdf->SetFont('Arial', 'B', 16);
        $pdf->Cell(0, 8, PROJECT_NAME, 0, 0, 'C');
        $pdf->SetX($setx);
        $pdf->SetFont('Arial', '', 8);
        $pdf->Cell(0, 8, 'Printed Date: ' . date('d-m-Y h:i A'), 0, 0, 'L');
        $pdf->Ln();
        $pdf->SetFont('Arial', '', 14);
        $pdf->Cell(0, 6, 'Customer Payment Report', 0, 0, 'C');

        if ($customer_name != 'All') {
            $customer = CustomerSetup::GetCustomerSetupById($customer_name);
            $pdf->Ln();
            $pdf->SetFont('Arial', '', 14);
            $pdf->Cell(0, 6, $customer->name, 0, 0, 'C');
        }

        $pdf->Ln();
        $pdf->SetFont('Arial', '', 7);
        $pdf->Cell(0, 4, 'For the period of: ' . date('d-m-Y', strtotime($datefrom)) . ' - ' . date('d-m-Y', strtotime($dateto)), 0, 0, 'C');

        $pdf->Ln();
        $pdf->Ln();
        $pdf->SetX($setx);
        if ($customer_name == '' || $customer_name == 'All') {
            $header = array('S.No', 'Customer', 'Opening', 'Debit', 'Credit', 'Balance');
        } else {
            $header = array('S.No', 'Customer', 'Opening', 'Debit', 'Credit', 'Balance');
        }

        function FancyTable($header, $data, $customer_name, $qryDate, $setx, $totalDebitAmount, $totalCreditAmount, $balanceAmount, $customers, $cat_name, $cat_type)
        {
            global $pdf;

            $customerids = array();
            $balanceAmount = (float) $balanceAmount;
            $totalDebitAmount = (float) $totalDebitAmount;
            $totalCreditAmount = (float) $totalCreditAmount;
            $subbalanceAmount = 0.0;
            $subtotalCreditAmount = 0.0;
            $subtotalDebitAmount = 0.0;
            $previousbalanceAmounttotal = 0.0;
            $totalProfit = 0;


            // Colors, line width and bold font
            $pdf->SetFillColor(224, 235, 255);
            $pdf->SetTextColor(0);
            $pdf->SetDrawColor(128, 0, 0);
            $pdf->SetLineWidth(.1);
            $pdf->SetFont('Arial', 'B', 10);
            // Header
            if ($customer_name == '' || $customer_name == 'All') {
                $wr = array(10, 80, 25, 25, 30, 30);
            } else {
                $wr = array(10, 80, 25, 25, 30, 30);
            }
            for ($i = 0; $i < count($header); $i++)
                $pdf->Cell($wr[$i], 7, $header[$i], 1, 0, 'C', true);
            $pdf->Ln();
            // Color and font restoration
            $pdf->SetFillColor(224, 235, 255);
            $pdf->SetTextColor(0);
            $pdf->SetFont('');
            // Data
            $fill = false;

            $counter = 0;
            $count = 0;
            $counter_pagebreak = 1;
            if ($data) {
                $countdown = 1;
                foreach ($data AS $row) {
                    $customerids[] = $row->customer_id;
                    $counter++;
                    $qryDatePrevious = date('Y-m-d', strtotime('-1 day', strtotime($qryDate)));

                    $filterReportOpening = " JOIN customer_setup AS cs ON cs.id = inv.customer_id WHERE DATE_FORMAT(inv.create_date,'%Y-%m-%d')>='2016-01-01' and DATE_FORMAT(inv.create_date,'%Y-%m-%d')<='$qryDatePrevious'  AND inv.status not in('Draft', 'Cancel') ";
                    $filterReportOpening .= " AND inv.customer_id ='$row->customer_id'";
                    if (isset($cat_type) && $cat_type == 'All') {
                        if (isset($cat_name) && $cat_name != 'All') {
                            $filterReportOpening .= " AND inv.customer_id IN (".$customers.")";
                        }
                    }else{
                        $filterReportOpening .= " AND cs.is_mart = '$cat_type' ";
                    }
                    $filterReportOpening .= " AND cs.status ='active'";
                    $filterReportOpening .= " UNION SELECT 'payment' AS nameTable, pay.create_date, pay.customer_id AS customer_id, pay.amount,
'', '', '','', '', pay.withholding_tax, pay.sales_tax, pay.goods_return, pay.shelf_rent, pay.builty_charges, pay.other_expense
from " . TABLE_PAYMENT . " AS pay 
JOIN customer_setup AS cs ON cs.id = pay.customer_id
WHERE DATE_FORMAT(pay.create_date,'%Y-%m-%d')>='2016-01-01' and DATE_FORMAT(pay.create_date,'%Y-%m-%d')<='$qryDatePrevious' AND pay.status <> 'Cancel' ";
                    $filterReportOpening .= " AND pay.customer_id ='$row->customer_id'";
                    if (isset($cat_type) && $cat_type == 'All') {
                        if (isset($cat_name) && $cat_name != 'All') {
                            $filterReportOpening .= " AND pay.customer_id IN (".$customers.")";
                        }
                    }else{
                        $filterReportOpening .= " AND cs.is_mart = '$cat_type' ";
                    }
                    $filterReportOpening .= " AND cs.status ='active'";
                    $filterReportOpening .= " ORDER BY create_date";

                    $qryOpening = Invoice::GetInvoicePaymentReportListsForOwner123($filterReportOpening);
                    $previoustotalDebitAmount = 0;
                    $previoustotalCreditAmount = 0;
                    $previousbalanceAmount = 0;
                    if ($qryOpening) {
                        foreach ($qryOpening as $qeryOpening) {
                            if ($qeryOpening->nameTable == 'invoice') {
                                $invoiceAmount = (float) $qeryOpening->total_amount;
                                $previousbalanceAmount += $invoiceAmount;
                                $previoustotalCreditAmount -= $invoiceAmount;
                            } else {
                                //$payment_amount = intval($qeryOpening->amount) + intval($qeryOpening->withholding_tax) + intval($qeryOpening->sales_tax) + intval($qeryOpening->goods_return) + intval($qeryOpening->shelf_rent) + intval($qeryOpening->builty_charges) + intval($qeryOpening->other_expense);
                                $payment_amount = (float) $qeryOpening->amount;
                                $previousbalanceAmount -= $payment_amount;
                                $previoustotalDebitAmount -= $payment_amount;
                            }
                        }
                    }
                    if ($row->nameTable == 'invoice') {
                        $invoiceAmount = (float) $row->total_amount;
                        $balanceAmount -= $invoiceAmount;
                        
                        $subbalanceAmount -= $invoiceAmount;
                        $subtotalCreditAmount += $invoiceAmount;
                    } else {
                        $payment_amount = (float) $row->amount;
                        $balanceAmount += $payment_amount;
                        $subbalanceAmount += $payment_amount;
                        $subtotalDebitAmount += $payment_amount;
                    }
                    if ((isset($data[$counter]->customer_id) && $data[$counter]->customer_id != $row->customer_id) || count($data) == 1 || $counter == count($data)) {
                        $customer = CustomerSetup::GetCustomerSetupById($row->customer_id);
                        $customerOpening = (float) $customer->balance;
                        $previousbalanceAmount += $customerOpening;
                        $previoustotalCreditAmount -= $customerOpening;
                        
                        if(($subtotalCreditAmount - $subtotalDebitAmount) + $previousbalanceAmount != 0){
                            $previousbalanceAmounttotal += $previousbalanceAmount;
                            $count++;
                            $totalDebitAmount += $subtotalDebitAmount;
                            $totalCreditAmount += $subtotalCreditAmount;
                            $pdf->SetX($setx);
                            $pdf->SetFont('Arial', '', 10);
                            $pdf->Cell($wr[0], 6, $count, 'LRBT', 0, 'C', $fill);
                            $pdf->Cell($wr[1], 6, $customer->name, 'LRBT', 0, 'L', $fill);
                            $pdf->Cell($wr[2], 6, number_format($previousbalanceAmount, 2), 'LRBT', 0, 'R', $fill);
                            $pdf->Cell($wr[3], 6, number_format($subtotalDebitAmount, 2), 'LRBT', 0, 'R', $fill);
                            $pdf->Cell($wr[4], 6, number_format($subtotalCreditAmount, 2), 'LRBT', 0, 'R', $fill);
                            $pdf->Cell($wr[5], 6, number_format(($subtotalCreditAmount - $subtotalDebitAmount) + $previousbalanceAmount, 2), 'LRBT', 0, 'R', $fill);
                            $pdf->Ln();
                           // $fill = !$fill;
                            $counter_pagebreak++;
                            $countdown++;
                        }else{
                            // if ($row->nameTable == 'invoice') {
                            //     $totalCreditAmount -= $row->total_amount;
                            // }
                            // $totalDebitAmount -= $row->amount;
                        }
                        $subbalanceAmount = 0;
                        $subtotalCreditAmount = 0;
                        $subtotalDebitAmount = 0;
                    }

                    if ($countdown == 38 || ($countdown != 38 && ($counter_pagebreak) % 42 == 0)) {
                        $countdown++;
                        $counter_pagebreak = 1;
                        $pdf->AddPage();
                        $pdf->SetX($setx);
                        $pdf->SetFont('Arial', 'B', 10);
                        for ($i = 0; $i < count($header); $i++)
                            $pdf->Cell($wr[$i], 7, $header[$i], 1, 0, 'C', true);

                        $pdf->Ln();
                    }

                }
            }
            $customersfilter = " WHERE status = 'active'";
            if (isset($cat_type) && $cat_type == 'All') {
                if (isset($cat_name) && $cat_name != 'All') {
                    $customersfilter .= " AND category_id = '$cat_name'";
                }
            }else{
                $customersfilter .= " AND is_mart = '$cat_type'";
            }
            $customers1 = CustomerSetup::GetCustomerSetupList($customersfilter);
            if ($customers1) {
                foreach ($customers1 AS $customersRow) {
                    if (!in_array($customersRow->id, $customerids)) {
                        $qryDatePrevious = date('Y-m-d', strtotime('-1 day', strtotime($qryDate)));

                        $filterReportOpening = " JOIN customer_setup AS cs ON cs.id = inv.customer_id WHERE DATE_FORMAT(inv.create_date,'%Y-%m-%d')>='2016-01-01' and DATE_FORMAT(inv.create_date,'%Y-%m-%d')<='$qryDatePrevious'  AND inv.status not in('Draft', 'Cancel') ";
                        $filterReportOpening .= " AND inv.customer_id ='$customersRow->id'";
                        if (isset($cat_type) && $cat_type == 'All') {
                            if (isset($cat_name) && $cat_name != 'All') {
                                $filterReportOpening .= " AND inv.customer_id IN (".$customers.")";
                            }
                        }else{
                            $filterReportOpening .= " AND cs.is_mart = '$cat_type' ";
                        }
                        $filterReportOpening .= " AND cs.status ='active'";
                        $filterReportOpening .= " UNION SELECT 'payment' AS nameTable, pay.create_date, pay.customer_id  AS customer_id, pay.amount,
'', '', '','', '', pay.withholding_tax, pay.sales_tax, pay.goods_return, pay.shelf_rent, pay.builty_charges, pay.other_expense
from " . TABLE_PAYMENT . " AS pay 
JOIN customer_setup AS cs ON cs.id = pay.customer_id
WHERE DATE_FORMAT(pay.create_date,'%Y-%m-%d')>='2016-01-01' and DATE_FORMAT(pay.create_date,'%Y-%m-%d')<='$qryDatePrevious' AND pay.status <> 'Cancel' ";
                        $filterReportOpening .= " AND pay.customer_id ='$customersRow->id'";
                        if (isset($cat_type) && $cat_type == 'All') {
                            if (isset($cat_name) && $cat_name != 'All') {
                                $filterReportOpening .= " AND pay.customer_id IN (".$customers.")";
                            }
                        }else{
                            $filterReportOpening .= " AND cs.is_mart = '$cat_type' ";
                        }
                        $filterReportOpening .= " AND cs.status ='active'";
                        $filterReportOpening .= " ORDER BY create_date";

                        $qryOpening = Invoice::GetInvoicePaymentReportListsForOwner123($filterReportOpening);
                        $previoustotalDebitAmount = 0;
                        $previoustotalCreditAmount = 0;
                        $previousbalanceAmount = 0;

                        $customer = CustomerSetup::GetCustomerSetupById($customersRow->id);
                        if ($qryOpening) {
                            foreach ($qryOpening as $qeryOpening) {
                                if ($qeryOpening->nameTable == 'invoice') {
                                    $invoiceAmount = (float) $qeryOpening->total_amount;
                                    $previousbalanceAmount += $invoiceAmount;
                                    $previoustotalCreditAmount -= $invoiceAmount;
                                } else {
                                    //$payment_amount = intval($qeryOpening->amount) + intval($qeryOpening->withholding_tax) + intval($qeryOpening->sales_tax) + intval($qeryOpening->goods_return) + intval($qeryOpening->shelf_rent) + intval($qeryOpening->builty_charges) + intval($qeryOpening->other_expense);
                                    $payment_amount = (float) $qeryOpening->amount;
                                    $previousbalanceAmount -= $payment_amount;
                                    $previoustotalDebitAmount += $payment_amount;
                                }
                            }
                        }
                        $customerOpening = (float) $customer->balance;
                        $previousbalanceAmount += $customerOpening;
                        $previoustotalCreditAmount -= $customerOpening;

                        if(($subtotalCreditAmount - $subtotalDebitAmount) + $previousbalanceAmount != 0){
                            $count++;
                            $pdf->SetX($setx);
                            $pdf->SetFont('Arial', '', 10);
                            $pdf->Cell($wr[0], 6, $count, 'LRBT', 0, 'C', $fill);
                            $pdf->Cell($wr[1], 6, $customer->name, 'LRBT', 0, 'L', $fill);
                            $pdf->Cell($wr[2], 6, number_format($previousbalanceAmount, 2), 'LRBT', 0, 'R', $fill);
                            $pdf->Cell($wr[3], 6, number_format($subtotalDebitAmount, 2), 'LRBT', 0, 'R', $fill);
                            $pdf->Cell($wr[4], 6, number_format($subtotalCreditAmount, 2), 'LRBT', 0, 'R', $fill);
                            $pdf->Cell($wr[5], 6, number_format(($subtotalCreditAmount - $subtotalDebitAmount) + $previousbalanceAmount, 2), 'LRBT', 0, 'R', $fill);
                        
                            $pdf->Ln();
                         //   $fill = !$fill;
                            $counter_pagebreak++;
                            $countdown++;
                            $previousbalanceAmounttotal += $previousbalanceAmount;
                           
                        }

                        $subbalanceAmount = 0;
                        $subtotalCreditAmount = 0;
                        $subtotalDebitAmount = 0;
                        


                        if ($countdown == 38 || ($countdown != 38 && ($counter_pagebreak) % 42 == 0)) {
                            $countdown++;
                            $counter_pagebreak = 1;
                            $pdf->AddPage();
                            $pdf->SetX($setx);
                            $pdf->SetFont('Arial', 'B', 10);
                            for ($i = 0; $i < count($header); $i++)
                                $pdf->Cell($wr[$i], 7, $header[$i], 1, 0, 'C', true);
                            
                            $pdf->Ln();
                        }
                    }
                }
            }
            //$pdf->SetFillColor(255, 204, 0);
            $pdf->SetX($setx);
            $pdf->SetFont('Arial', 'B', 10);
            $pdf->Cell($wr[0], 6, "", 'LBT', 0, 'R');
            $pdf->Cell($wr[1], 6, "Total", 'RBT', 0, 'R');
            $pdf->Cell($wr[2], 6, number_format($previousbalanceAmounttotal, 2, '.', ''), 'LRBT', 0, 'R');
            $pdf->Cell($wr[3], 6, number_format($totalDebitAmount, 2, '.', ''), 'LRBT', 0, 'R');
            $pdf->Cell($wr[4], 6, number_format($totalCreditAmount, 2, '.', ''), 'LRBT', 0, 'R');
            $finalBalance = ($totalCreditAmount + $previousbalanceAmounttotal) - $totalDebitAmount;
            $pdf->Cell($wr[5], 6, number_format($finalBalance, 2, '.', '') . ($totalDebitAmount > $totalCreditAmount ? " Db" : " Cr"), 'LRBT', 0, 'R');

        }

        FancyTable($header, $invoice, $customer_name, $qryDate, $setx, 0.0, 0.0, 0.0, $customers, $cat_name, $cat_type);
    } else {
        header('location: ' . PROJECT_ADMIN_URL);
        exit;
    }

    $pdf->Output();
    if (ob_get_level() > 0) { ob_end_flush(); }
    exit;
} catch (Exception $e) {
    echo $e->getMessage();
}
?>
