<style type="text/css">
    #searchdiv{
        margin-bottom: 10px;
    }
    .searchfieldslist{
        margin-top: 4px;
    }
</style>
<!-- Page Content -->
<div class="page-content">
    <!-- Page Header -->
    <div class="page-header position-relative">
        <div class="header-title">
            <h1>
                Customer Diary
            </h1>
        </div>
    </div>
    <!-- /Page Header -->
    <!-- Page Body -->
    <div class="page-body">
        
        <div class="row">
            <div class="col-xs-12 col-md-12">
                <div class="widget">
                    <div class="widget-body">
                        <div id="searchdiv">
                            <form name="frmsearch" id="frmsearch" method="post">
                                <div class='col-md-2 searchfieldslist'>
                                    <input type="text" name="searchkey" id="searchkey" class="form-control" placeholder="search key"
                                           value="<?php if(isset($_POST['searchkey'])) echo $_POST["searchkey"]; ?>" />
                                </div>
                                <div class='col-md-3 searchfieldslist'>
                                    <input type="text" name="custname" id="custname" class="form-control" placeholder="Customer Name"
                                           value="<?php if(isset($_POST['custname'])) echo $_POST["custname"]; ?>" />
                                </div>
                                <div class='col-md-3 searchfieldslist'>
                                    <select name="PAYMENT" id="PAYMENT" class="form-control">
                                        <option value="">SELECT PAYMENT</option>
                                        <option value="debt" <?php if(isset($_POST['PAYMENT']) && $_POST['PAYMENT']=='debt') echo "selected"; ?>>DEBT</option>
                                        <option value="debtpaid" <?php if(isset($_POST['PAYMENT']) && $_POST['PAYMENT']=='debtpaid') echo "selected"; ?>>DEBT PAID</option>
                                        <option value="cash" <?php if(isset($_POST['PAYMENT']) && $_POST['PAYMENT']=='cash') echo "selected"; ?>>CASH</option>
                                        <option value="magcard" <?php if(isset($_POST['PAYMENT']) && $_POST['PAYMENT']=='magcard') echo "selected"; ?>>CARD</option>
                                    </select>
                                </div>
                                <div class='col-md-2 searchfieldslist'>
                                    <input type="text" name="fromdate" id="fromdate" class="form-control datepickertxt" readonly
                                           placeholder="fromdate" value="<?php if(isset($_POST['fromdate'])) echo $_POST["fromdate"]; else echo date("d-m-Y",strtotime('-1 months')); ?>" />
                                </div>
                                <div class='col-md-2 searchfieldslist'>
                                    <input type="text" name="todate" id="todate" class="form-control datepickertxt" readonly
                                           placeholder="to date" value="<?php if(isset($_POST['todate'])) echo $_POST["todate"]; else echo date("d-m-Y"); ?>" />
                                </div>
                                            <div class="clearfix"></div>
                                    <div class='col-md-3 searchfieldslist'>
                                        <button type="submit" class="btn btn-primary shiny" name="allsearch"><span class="fa fa-search"></span>Execute</button>
                                    </div>
                            </form>
                        </div>
                        
                <form name="exportexcel" id="exportexcel" action="<?php echo base_url(); ?>reports/customerdiary/exportexceldata" method="post">
                    
                    <?php
                    if(isset($_POST["allsearch"])){
                    ?>
                    <div style="float: right;">
                        <input type="hidden" name="hiddensqlquery" id="hiddensqlquery" value="<?php echo $sqlquery; ?>" />
<input type="hidden" name="hiddenfromdate" id="hiddenfromdate" value="<?php if(isset($_POST['fromdate'])) echo $_POST["fromdate"]; ?>" />
                        <div class="btn-group">
                            <button type="submit" class="btn btn-success shiny" name="excelexport"><i class="fa fa-file-excel-o"></i> Excel</button>
                            <button type="submit" class="btn btn-danger shiny" name="pdfxport"><i class="fa fa-file-pdf-o"></i> PDF</button>
                        </div>
                    </div>
                    <?php
                    }
                    ?>
                </form>
                    <div id="tablecontent" class="table-scrollable">
                        <div id="tablecontent" class="table-scrollable">
                            <table class="table table-striped table-hover table-bordered" id="reportdatatable">
                                <thead>
                                    <tr role="row">
                                        <th style="text-align:left;width:50px">Date</th>
                                        <th style="text-align:left;width:70px">Ticket Id</th>
                                        <th style="text-align:left;width:70px">Payment</th>
                                        <th style="text-align:right;width:70px">Amount</th>
                                        <th style="text-align:right;width:70px">Balance</th>
                                    </tr>
                                </thead>
                                <tfoot>
                                    
                                </tfoot>
                                <tbody>
    <?php
    if(isset($resultdata)) {
        if($resultdata) {
            $customername   =   '';
            foreach($resultdata as $result) {
                if($customername!=$result->NAME) {
                    echo '<tr role="row"><td colspan="5"><b>'.$result->NAME.'</b></td></tr>';
                    $customername   =   $result->NAME;
                    $sql = "SELECT SUM(PAYMENTS.TOTAL) AS TOTALAMOUNT"
                    . " FROM RECEIPTS, TICKETS, CUSTOMERS, PAYMENTS"
                    . " WHERE RECEIPTS.ID = TICKETS.ID AND RECEIPTS.ID = PAYMENTS.RECEIPT"
                    . " AND TICKETS.CUSTOMER = CUSTOMERS.ID AND (PAYMENTS.PAYMENT='debt' OR PAYMENTS.PAYMENT='debtpaid') AND CUSTOMERS.ID='".$result->CUSTOMERID."'";
                    if($_POST["fromdate"])
                    $sql = $sql." AND RECEIPTS.DATENEW<'".date("Y-m-d H:i:s",strtotime($_POST["fromdate"]." 00:00:00"))."'";
                    $sql = $sql." ORDER BY CUSTOMERS.NAME, RECEIPTS.DATENEW";
                    $totalamount = $this->common->customquery($sql);
                    echo '<tr role="row"><td colspan="4"> <b>Opening Balance as on '.date("d-M-Y",strtotime($_POST["fromdate"]." -1 day")).'</b></td>'
                    . '<td style="text-align:right;"><b>'.
                    $this->formatslist->formatCurrencywithHash($totalamount[0]->TOTALAMOUNT).'</b></td></tr>';
                    $balancedeflect =   $totalamount[0]->TOTALAMOUNT;
                }
                $balancedeflect =   $balancedeflect+$result->TOTAL;
    ?>
                                        <tr role="row">
                                            <td> <?php echo $this->formatslist->datetimeFormat($result->DATENEW); ?></td>
                                            <td> <?php echo $result->TICKETID; ?></td>
                                            <td> <?php echo $result->PAYMENT; ?></td>
                                            <td> 
                                                <?php
                                                    echo $this->formatslist->formatCurrencywithHash($result->TOTAL);
                                                ?>
                                            </td>
                                            <td> 
                                                <?php
                                                    echo $this->formatslist->formatCurrencywithHash($balancedeflect);
                                                ?>
                                            </td>
                                        </tr>
    <?php
            }
        }
    }
    ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <!-- /Page Body -->
    </div>
    <!-- /Page Content -->
</div>
<!-- /Page Container -->
<!-- Main Container -->
</div>
<script src="<?= site_url() ?>assets/js/datetime/bootstrap-datepicker.js"></script>
<script type="text/javascript">
    $("#fromdate").datepicker({format: 'dd-mm-yyyy', setDate: new Date()});
    $("#todate").datepicker({format: 'dd-mm-yyyy', setDate: new Date()});
    $('.datepickertxt').datepicker().on('changeDate', function (ev) { 
        $('.datepicker').hide();
    });
</script>