<?php
include("../../classes/manager.class.php");
//ini_set('display_errors', 1);
//error_reporting(E_ALL);
$m = new Manager();
$output = '';
$datefrom = "";
$dateto = "";
$category_name = "";
$mainCat = "";
$catName = "";
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 = " AS ex RIGHT JOIN " . TABLE_EXPENSEMARTCATEGORYSETUP . " AS exCat ON ex.category_id = exCat.id WHERE DATE_FORMAT(lastupdated,'%Y-%m-%d')>='$qryDate' and DATE_FORMAT(lastupdated,'%Y-%m-%d')<='$qryTo' ";

if (isset($category_name) && $category_name != 'All') {
    if (isset($sub_category) && $sub_category != 'All') {
        $filterReport .= " AND ex.category_id ='$sub_category'";
    } else {
        $filterReport .= " AND exCat.parent_id ='$category_name'";
    }
    $filterReport .= " ORDER BY ex.category_id";
} else {
    $filterReport .= " ORDER BY exCat.parent_id";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <?php include('../includes/inc_head.php'); ?>
</head>
<div>
    <!-- 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>Expense Mart 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">Category<br>
                                <?php
                                $categoryfilter = " WHERE parent_id = 0 ORDER BY name";
                                $category = ExpenseMartCategorySetup::GetExpenseMartCategorySetupList($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>
                            <td class="col s6">Sub Category<br>
                                <?php
                                $filterSubCategory = " WHERE parent_id = '$category_name' and parent_id <> 0";
                                $subCategory = ExpenseMartCategorySetup::GetExpenseMartCategorySetupList($filterSubCategory);
                                ?>
                                <select class="form-control" required id="sub_category"
                                        name="sub_category">
                                    <option>All</option>
                                    <?php
                                    if ($subCategory) {
                                        foreach ($subCategory AS $subCategoryRow) {
                                            echo '<option ';
                                            if ($subCategoryRow->id == $sub_category) {
                                                echo 'selected';
                                            }
                                            echo ' value="' . $subCategoryRow->id . '" >' . $subCategoryRow->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
                                        onclick="exportTableToCSV('expense-mart-<?= date('d-m-Y-h-i-a') ?>.csv')"
                                        class="btn waves-effect waves-light"
                                        style="float:left; margin-left: 15px;">
                                        Download 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
              			$totalReturn = 0;
                        $withholdingTax = 0;
                        $salesTax = 0;
                if (isset($_GET['view'])) {
                    ?>
                    <div class="table">
                        <table class="table bordered">
                            <?php
                            $qry = ExpenseMartSetup::GetExpenseMartSetupList($filterReport);
                            if ($category_name == 'All' && $sub_category == 'All') {
                                if ($qry) {
                                    $counter = 0;
                                    $counterSerial = 0;
                                    $amountTotal = 0;
                                    $amountSubTotal = 0;
                                    foreach ($qry as $qery) {
                                        $exPCategory = ExpenseMartCategorySetup::GetExpenseMartCategorySetupById($qery->parent_id);
                                        $counter++;
                                        if ($exPCategory->name != $mainCat && $counter != 1) {
                                            echo '<span class="right">' . $amountSubTotal . '</span></th></tr>';
                                        }
                                        if ($catName != $exPCategory->name) {
                                            $amountSubTotal = 0;
                                            $counterSerial = 0;
                                            echo '
<tbody style="border: 1px solid #666;"><tr><th colspan="4"><span class="left">' . $exPCategory->name . '</span>';
                                        }
                                        $counterSerial++;
                                        $amountSubTotal += $qery->amount;
                                        $amountTotal += $qery->amount;
                                        $catName = $exPCategory->name;
                                        $mainCat = $exPCategory->name;
                                    }
                                    echo '<span class="right">' . $amountSubTotal . '</span></th></tr>';

                                    $filterMartSalary = " WHERE DATE_FORMAT(date_time,'%Y-%m-%d')>='$qryDate' and DATE_FORMAT(date_time,'%Y-%m-%d')<='$qryTo' ";
                                    //$filterMartSalary = " ORDER BY employee_id";
                                    $qryMartSalary = SalarySetupMart::GetSalarySetupList($filterMartSalary);
                                    if ($qryMartSalary) {
                                       $martSalary = 0;
                                        foreach ($qryMartSalary AS $qryMartSalaryRow) {
                                            $martSalary += $qryMartSalaryRow->amount;
                                        }
                                      $amountTotalMartSalary += $martSalary;
                                        echo '<tr><th colspan="4"><span class="left">Total Mart Salary</span>';
                                        echo '<span class="right">' . $amountTotalMartSalary . '</span></th></tr>';
                                    }
                                    $amountTotal += $amountTotalMartSalary;

                                    $filterLoan = " WHERE DATE_FORMAT(create_date,'%Y-%m-%d')>='$qryDate' and DATE_FORMAT(create_date,'%Y-%m-%d')<='$qryTo' and status = 'active' ";
                                    $qryLoan = LoanSetup::GetLoanList($filterLoan);
                                    $loan = 0;
                                    if ($qryLoan){
                                        foreach($qryLoan AS $qryLoanRow){
                                            $loan += $qryLoanRow->amount;
                                        }
                                    }

                                    $filterMartLoan = " WHERE DATE_FORMAT(create_date,'%Y-%m-%d')>='$qryDate' and DATE_FORMAT(create_date,'%Y-%m-%d')<='$qryTo' and status = 'active' ";
                                    $qryMartLoan = LoanSetupMart::GetLoanList($filterMartLoan);
                                    $martLoan = 0;
                                    if ($qryMartLoan){
                                        foreach($qryMartLoan AS $qryMartLoanRow){
                                            $martLoan += $qryMartLoanRow->amount;
                                        }
                                    }
                                  
                                                                    
                                   $filterpay = " WHERE DATE_FORMAT(pay.create_date,'%Y-%m-%d')>='$qryDate' and DATE_FORMAT(pay.create_date,'%Y-%m-%d')<='$qryTo' AND pay.status <> 'Cancel' ";
                                  $totalPayment = Payment::GetTotalPaymentLists($filterpay);
                                  
                                  $withholdingTax += $totalPayment->withholding_tax;
                                    $amountTotal += $withholdingTax;
                                    $amountTotal += $martLoan;

                                        echo '<tr><th colspan="4"><span class="left">Total Withholding Tax - (4.5%)</span>';
                                        echo '<span class="right">' . $withholdingTax . '</span></th></tr>';

                                        echo '<tr><th colspan="4"><span class="left">Mart Loan</span>';
                                        echo '<span class="right">' . $martLoan . '</span></th></tr>';
                                } ?>
                          
                          
                          
                                <tr>
                                    <th colspan="3"
                                        style="text-align: right; border: 1px solid #000;">
                                        Total
                                    </th>

                                    <th style="border: 1px solid #000; text-align: right;"><?= $amountTotal ?></th>
                                </tr>
                                </tbody>
                                <?php
                            } else {
                                if ($qry) {
                                    $counter = 0;
                                    $counterSerial = 0;
                                    $amountTotal = 0;
                                    $amountSubTotal = 0;
                                    foreach ($qry as $qery) {
                                        $counter++;
                                        if ($catName != $qery->name) {
                                            $amountSubTotal = 0;
                                            $counterSerial = 0;
                                            echo '<tbody style="border: 1px solid #666;"><tr style="border:1px solid #666;"><th style="border-top:1px solid #666;" colspan="4">' . $qery->name . '</th></tr>
                                                    <tr>
                                                        <th>S.No</th>
                                                        <th>Date</th>
                                                        <th>Description</th>
                                                        <th style="border-right:1px solid #666;">Amount</th>
                                                    </tr>';
                                        }
                                        $counterSerial++;
                                        ?>
                                        <tr>
                                            <td><?= $counterSerial ?></td>
                                            <td><?php echo date('Y-m-d', strtotime($qery->lastupdated)); ?></td>
                                            <td><?php echo $qery->description; ?></td>
                                            <td style="border-right:1px solid #666;"><?php echo number_format(($qery->amount), 2, '.', ''); ?></td>
                                        </tr>
                                        <?php
                                        $amountSubTotal += $qery->amount;
                                        $amountTotal += $qery->amount;
                                        $catName = $qery->name;
                                        if (isset($qry[$counter]->name) && $qry[$counter]->name != $qery->name && $sub_category == 'All') {
                                            echo '<tr style="border:1px solid #666;"><th colspan="3"></th><th style="border-right:1px solid #666;">' . $amountSubTotal . '</th></tr></tbody><body><tr><td colspan="3"></td></tr></body>';
                                        }
                                    }
                                    echo '<tr style="border:1px solid #666;"><th colspan="3"></th><th style="border-right:1px solid #666;">' . $amountSubTotal . '</th></tr></tbody><body><tr><td colspan="3"></td></tr></body>';
                                } ?>
                                <tr>
                                    <th colspan="3"
                                        style="text-align: right; border: 1px solid #000;">
                                        Total
                                    </th>

                                    <th style="border: 1px solid #000;"><?= $amountTotal ?></th>
                                </tr>
                            <?php } ?>
                        </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="application/javascript">
    $(document).ready(function () {
        // initialize
        $('#sub_category').material_select();
        // setup listener for custom event to re-initialize on change
        $('#sub_category').on('contentChanged', function () {
            $(this).material_select();
        });

        $('#category_name').on('change', function () {
            $('body.container').block({message: 'Loading...'});
            $.get('getFilterExpenseMartCat.php?id=' + $(this).val(), function (data) {
                $("#sub_category").empty();
                $("#sub_category").append(data);
                $('body.container').unblock();
                $('#sub_category').trigger('contentChanged');
            });
        });
        $("#print").on("click", function (e) {
            var fewSeconds = 2;
            e.preventDefault();
            $('#searchfrm').prop('target', '_blank');
            $('#searchfrm').prop('action', "<?=PROJECT_ADMIN_URL?>report/print/expense_mart.php?").submit();
            setTimeout(function () {
                $('#searchfrm').prop('target', "");
                $('#searchfrm').prop('action', "");
            }, fewSeconds * 1000);
        });
    });
</script>
</body>
</html>