<?php
include("../../classes/manager.class.php");
//ini_set('display_errors', 1);
//error_reporting(E_ALL);
$m = new Manager();
$output = '';
$datefrom = "";
$dateto = "";
$customer_name = "";
$totalpaid = 0;
$totalPayablePrevious = 0;
$totalpayable = 0;
$totalPaymentsPrevious = 0;
$openingBalance = 0;

if (!isset($_SESSION['sess_userid'])) {
    header("Location: " . PROJECT_ADMIN_URL);
    exit();
}
extract($_REQUEST);
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 = " WHERE DATE_FORMAT(pur.lastupdated,'%Y-%m-%d')>='$qryDate' and DATE_FORMAT(pur.lastupdated,'%Y-%m-%d')<='$qryTo' ";

if (isset($category_name) && $category_name != 'All') {
    $filterPurhcaseCat = " WHERE parent_id = '$category_name' ";
    $purchaseCat = PurchaseCategorySetup::GetPurchaseCategorySetupList($filterPurhcaseCat);
    if ($purchaseCat) {
        foreach ($purchaseCat AS $purchaseCatRow) {
            $purchaseCategory[] = $purchaseCatRow->id;
        }
        $purchaseCategoryString = implode("','", $purchaseCategory);
        $filterReport .= " AND pur.category_id IN ('" . $purchaseCategoryString . "')";
    }
}

$filterReport .= " UNION SELECT '' AS catname, id,'' AS descr, '' AS purchaseamount, create_date AS purchasedate, 'payment' AS nameTable,
                code , supplier_id, payment_type, amount, cheque_bank_name,
		cheque_number, cheque_payee_name, cheque_date, bank_name, bank_account_number, party_name, from_bank_name, create_date, description
		FROM " . TABLE_PURPAYMENT . " WHERE DATE_FORMAT(create_date,'%Y-%m-%d')>='$qryDate' AND
		DATE_FORMAT(create_date,'%Y-%m-%d')<='$qryTo' AND status <> 'Cancel' ";

if (isset($category_name) && $category_name != 'All') {
    $filterReport .= " AND supplier_id ='$category_name'";
}
$filterReport .= " ORDER BY purchasedate";

//PreviousRecord
$qryDatePrevious = date('Y-m-d', strtotime('-1 day', strtotime($qryDate)));
$filterReportPrevious = " WHERE DATE_FORMAT(pur.lastupdated,'%Y-%m-%d')>='2016-01-01' and DATE_FORMAT(pur.lastupdated,'%Y-%m-%d')<='$qryDatePrevious' ";

if (isset($category_name) && $category_name != 'All') {
    if ($purchaseCategoryString) {
        $filterReportPrevious .= " AND pur.category_id IN ('" . $purchaseCategoryString . "')";
    }
}
$filterReportPrevious .= " UNION SELECT '' AS catname, id,'' AS descr, '' AS purchaseamount, create_date AS purchasedate, 'payment' AS nameTable,
                code , supplier_id, payment_type, amount, cheque_bank_name,
		cheque_number, cheque_payee_name, cheque_date, bank_name, bank_account_number, party_name, from_bank_name, create_date, description
		FROM " . TABLE_PURPAYMENT . "
		WHERE DATE_FORMAT(create_date,'%Y-%m-%d')>='2016-01-01' AND
		DATE_FORMAT(create_date,'%Y-%m-%d')<='$qryDatePrevious' AND status <> 'Cancel' ";

if (isset($category_name) && $category_name != 'All') {
    $filterReportPrevious .= " AND supplier_id ='$category_name'";
}
$filterReportPrevious .= " ORDER BY purchasedate";

//echo $filterReport;
//echo "<br>";
//echo $filterReportPrevious;
//exit;
//Previous Record

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <?php include('../includes/inc_head.php'); ?>
</head>
<body>
<!-- Start Page Loading -->
<div id="loader-wrapper">
    <div id="loader"></div>
    <div class="loader-section section-left"></div>
    <div class="loader-section section-right"></div>
</div>
<!-- End Page Loading -->
<div id="container">
    <?php include('../includes/inc_top.php'); ?>
    <div class="row">
        <div class="col s12 m12">
            <div class="">
                <h3>Purchase Payment Report</h3>
            </div>
            <script>
                $(function () {
                    $('#datepicker').pickadate({
                        format: 'dd-mm-yyyy',
                        selectMonths: true,
                        selectYears: true,
                    });
                    $('#datepicker1').pickadate({
                        format: 'dd-mm-yyyy',
                        selectMonths: true,
                        selectYears: true,
                    });
                });
            </script>
            <form action="" method="get" name="search"
                  id="searchfrm">
                <table class="table">
                    <tr>
                        <td class="col s6">Date From<br>
                            <input type="text" name="datefrom" id="datepicker"
                                   placeholder="Date From"
                                   value="<?= $datefrom ?>" class="form-control"/>
                            <input type="hidden" name="view" id="view" class="form-control"/>
                        </td>
                        <td class="col s6"> Date To<br>
                            <input type="text" name="dateto" id="datepicker1"
                                   placeholder="Date To"
                                   value="<?= $dateto ?>" class="form-control"/>
                        </td>
                    </tr>
                    <tr>
                        <td class="col s6">Supplier<br>
                            <?php
                            $categoryfilter = " WHERE parent_id = 0 ORDER BY name";
                            $category = PurchaseCategorySetup::GetPurchaseCategorySetupList($categoryfilter);
                            ?>
                            <select class="form-control" required id="category_name"
                                    name="category_name">
                                <option>All</option>
                                <?php
                                if ($category) {
                                    foreach ($category AS $categoryRow) {
                                        echo '<option ';
                                        if ($categoryRow->id == $category_name) {
                                            echo 'selected';
                                        }
                                        echo ' value="' . $categoryRow->id . '" >' . $categoryRow->name . '</option>';
                                    }
                                }
                                ?>
                            </select><br/>
                        </td>
                    </tr>
                    <tr>
                        <td colspan="2" class="col s12">
                            <button type="submit" class="btn waves-effect waves-light"
                                    style="float:left;">
                                Search
                            </button>
                            <?php if (isset($_GET['view'])) { ?>
                                <button type="button"
                                        onclick="exportTableToCSV('purchase-payment-<?= date('d-m-Y-h-i-a') ?>.csv')"
                                        class="btn waves-effect waves-light"
                                        style="float:left; margin-left: 15px;">
                                    Export CSV
                                </button>
                                <button
                                    id="print"
                                    class="btn waves-effect waves-light"
                                    style="float:left; margin-left: 15px;">
                                    Print
                                </button>
                            <?php } ?>
                        </td>
                    </tr>
                </table>
            </form>
            <hr>
            <?php
            if (isset($_GET['view'])) {
                ?>
                <?php
                if ($customer_name != '' || $customer_name != 'All') {
                    $filterPurhcaseCategory = " WHERE id = '$category_name' ";
                    $purchaseCategory = PurchaseCategorySetup::GetPurchaseCategorySetupList($filterPurhcaseCategory);
                    if (!empty($purchaseCategory)) {
                        echo '<table style="width: 100%;"><tr><th style="text-align: center;">';
                        echo "<h2 style='text-align: center;'>" . $purchaseCategory[0]->name . "</h2>";
                        echo '</th></tr></table>';
                    }
                }
                ?>
                <div id="floatThead" class="section">
                    <table class="striped demo1 bordered">
                        <thead>
                        <tr style="border: 1px solid #666;">
                            <th>S.No</th>
                            <th>Date</th>
                            <th>Purchases</th>
                            <th>Type</th>
                            <th>Description</th>
                            <th>Detail</th>
                            <th>Payable</th>
                            <th>Paid</th>
                            <th>Balance</th>
                        </tr>
                        </thead>
                        <tbody>
                        <?php
                        $qryPrevious = Purpayment::GetPurpaymentListWithPayment($filterReportPrevious);
                        //print_r($qryPrevious);
                        if ($qryPrevious) {
                            foreach ($qryPrevious AS $qryPreviousRow) {
                                //echo $qryPreviousRow->purchaseamount." - ".$qryPreviousRow->amount."<br>";
                                if ($qryPreviousRow->nameTable == 'purchase') {
                                    $totalPayablePrevious += $qryPreviousRow->purchaseamount;
                                } else if ($qryPreviousRow->nameTable == 'payment') {
                                    $totalPaymentsPrevious += $qryPreviousRow->amount;
                                }
                            }
                            $openingBalance = number_format(($totalPayablePrevious - $totalPaymentsPrevious), 2, '.', '');
                        }
                        $qry = Purpayment::GetPurpaymentListWithPayment($filterReport);
                        if ($qry) {
                            $counter = 1;
                            $totalpaid = 0;
                            $totalpayable = 0;
                            ?>
                            <tr style="border: 1px solid #666;">
                                <td>1</td>
                                <td>-</td>
                                <td>-</td>
                                <td>-</td>
                                <td>Opening</td>
                                <td>-</td>
                                <td>-</td>
                                <td>-</td>
                                <td><?php echo($openingBalance); ?></td>
                            </tr>
                            <?php
                            foreach ($qry as $qery) {
                                $counter++;
                                if ($qery->nameTable == 'purchase') {
                                    ?>
                                    <tr style="border: 1px solid #666;">
                                        <td><?= $counter ?></td>
                                        <td><?php echo date('Y-m-d', strtotime($qery->purchasedate)); ?></td>
                                        <td><?php echo $qery->catname; ?></td>
                                        <?php
                                        $totalpayable += $qery->purchaseamount;
                                        ?>
                                        <td><?php echo $qery->nameTable; ?></td>
                                        <td><?php echo $qery->descr; ?></td>
                                        <td>-</td>
                                        <td><?php echo number_format(($qery->purchaseamount), 2, '.', ''); ?></td>
                                        <td>-</td>
                                        <td><?php echo(number_format(($openingBalance + ($totalpayable - $totalpaid)), 2, '.', '')); ?></td>
                                    </tr>
                                    <?php
                                } else if ($qery->nameTable == 'payment') {
                                    $bank = BankSetup::GetBankSetupById($qery->bank_name); ?>
                                    <tr style="border: 1px solid #666;">
                                        <td><?= $counter ?></td>
                                        <td><?php echo date('Y-m-d', strtotime($qery->purchasedate)); ?></td>
                                        <?php
                                        $purCat = PurchaseCategorySetup::GetPurchaseCategorySetupById($qery->supplier_id);
                                        $totalpaid += $qery->amount;
                                        ?>
                                        <td><?php echo $purCat->name; ?></td>
                                        <td><?php echo $qery->nameTable; ?></td>
                                        <td><?php if ($qery->payment_type == 'party') {
                                                echo 'Party: ' . $qery->party_name . " - " . $qery->cheque_number . "<br>";
                                                echo 'Cheque Details: ' . $qery->cheque_payee_name . " (" . $qery->cheque_bank_name . " - " . date('d-m-Y', strtotime($qery->cheque_date)) . ")";
                                            } else if ($qery->payment_type == 'online') {
                                                echo "From: " . $qery->from_bank_name . "<br>";
                                                echo 'To: ' . $bank->bank_name;
                                            } else {
                                                echo $bank->bank_name;
                                                if ($qery->payment_type == 'bank') {
                                                    echo " - " . $qery->cheque_number . "<br>";
                                                    echo 'Cheque Details: ' . $qery->cheque_payee_name . " (" . $qery->cheque_bank_name . " - " . date('d-m-Y', strtotime($qery->cheque_date)) . ")";
                                                }
                                            } ?></td>
                                        <td><?php echo $qery->description; ?></td>
                                        <td>-</td>
                                        <td><?php echo $qery->amount; ?></td>
                                        <td><?php echo(number_format(($openingBalance + ($totalpayable - $totalpaid)), 2, '.', '')); ?></td>
                                    </tr>
                                    <?php
                                }
                            }
                        } ?>
                        <tr style="border: 1px solid #666;">
                            <?php if ($customer_name == '' || $customer_name == 'All') {
                                $num = 6;
                            } else {
                                $num = 5;
                            } ?>
                            <th colspan="<?= $num ?>"
                                style="text-align: right; border: 1px solid #000;">
                                Total
                            </th>
                            <th style="border: 1px solid #000;"><?= number_format(($totalpayable), 2, '.', '') ?></th>
                            <th style="border: 1px solid #000;"><?= number_format(($totalpaid), 2, '.', '') ?></th>
                            <th style="border: 1px solid #000;"><?= (number_format(($openingBalance + ($totalpayable - $totalpaid)), 2, '.', '')) ?></th>
                        </tr>
                        <tr style="border: 1px solid #666;">
                            <?php if ($customer_name == '' || $customer_name == 'All') {
                                $num = 7;
                            } else {
                                $num = 6;
                            } ?>
                            <th colspan="<?= $num ?>"
                                style="text-align: right; border: 1px solid #000;">
                                Total Opening Balance
                            </th>

                            <th colspan="2"
                                style="border: 1px solid #000;"><?= !empty($openingBalance) ? $openingBalance : 0 ?></th>
                        </tr>
                        <tr style="border: 1px solid #666;">
                            <?php if ($customer_name == '' || $customer_name == 'All') {
                                $num = 7;
                            } else {
                                $num = 6;
                            } ?>
                            <th colspan="<?= $num ?>"
                                style="text-align: right; border: 1px solid #000;">
                                Total Remianing Balance
                            </th>

                            <th colspan="2"
                                style="border: 1px solid #000;"><?= (number_format(($openingBalance + ($totalpayable - $totalpaid)), 2, '.', '')) ?></th>
                        </tr>
                        </tbody>
                    </table>
                </div>
                <?php

            } // close view
            ?>
        </div>
    </div>
</div>
</div>
<?php include('../includes/inc_footer.php'); ?>
<script type="text/javascript" src="<?= PROJECT_URL ?>js/download.js"></script>
<?php include('../includes/inc_js.php'); ?>
<script type="text/javascript" src="<?= PROJECT_URL ?>js/plugins/floatThead/jquery.floatThead.min.js"></script>
<script type="text/javascript"
        src="<?= PROJECT_URL ?>js/plugins/floatThead/jquery.floatThead-slim.min.js"></script>
<script type="application/javascript">
    $(document).ready(function () {
        $("#print").on("click", function (e) {
            var fewSeconds = 2;
            e.preventDefault();
            $('#searchfrm').prop('target', '_blank');
            $('#searchfrm').prop('action', "<?=PROJECT_ADMIN_URL?>report/print/purchasepayment.php?").submit();
            setTimeout(function () {
                $('#searchfrm').prop('target', "");
                $('#searchfrm').prop('action', "");
            }, fewSeconds * 1000);
        });
        $('table.demo1').floatThead({
            position: 'fixed',
            scrollingTop: 65
        });
        $('table.demo1 thead').css('background', '#ECEFF1');
    });
</script>
</body>
</html>