<?php

require_once './connection.php';

if (!isset($_POST['start_bill_date']) == "" && !isset($_POST['end_bill_date']) == "") {
    $start_bill_date = $_POST['start_bill_date'];
    $start_bill_date = $start_bill_date . ' 00:00:05';
    $end_bill_date = $_POST['end_bill_date'];
    $end_bill_date = $end_bill_date . ' 23:50:05';

    
    $select_broker_id = $_POST['select_broker_id'];
    $select_company_id = $_POST['select_company_id'];
    $vehicle_no = $_POST['vehicle_no'];
    $qry_broker='';
    $qry_company='';
    $qry_vehicle_no='';
    
    $sql = 'select p.*,c.comp_name,b.broker_name from tbl_party as p left join tbl_company as c on p.company_id=c.id left join tbl_broker as b on p.broker_id=b.id where p.bill_date BETWEEN "' . $start_bill_date . '" and "' . $end_bill_date . '" and p.flag="1"';
    
    if ($select_broker_id == 'Select' || $select_broker_id == '') {
        
    }else{
        $sql=$sql.' and p.broker_id="'.$select_broker_id.'"';
    }
    
    if ($select_company_id == 'Select' || $select_company_id == '') {
        
    }else{
        $sql=$sql.' and p.company_id="'.$select_company_id.'"';
    }
    
    if ($vehicle_no == 'Select' || $vehicle_no == '') {
        
    }else{
        $sql=$sql.' and p.vehicle_no="'.$vehicle_no.'"';
    }
    $sql=$sql.' and p.payment_type="Credit" order by p.id asc';
    
//    echo 'query sql is '.$sql;
    
//    if ($select_broker_id == 'Select' || $select_broker_id == '') {
//        $sql = 'select p.*,c.comp_name,b.broker_name from tbl_party as p left join tbl_company as c on p.company_id=c.id left join tbl_broker as b on p.broker_id=b.id where p.bill_date BETWEEN "' . $start_bill_date . '" and "' . $end_bill_date . '" and p.flag="1" and p.payment_type="Credit" order by p.id asc';
//    } else {
//        $sql = 'select p.*,c.comp_name,b.broker_name from tbl_party as p left join tbl_company as c on p.company_id=c.id left join tbl_broker as b on p.broker_id=b.id where p.bill_date BETWEEN "' . $start_bill_date . '" and "' . $end_bill_date . '" and p.flag="1" and p.payment_type="Credit" order by p.id asc';
//    }
//    echo 'Query '.$sql;


    $response1 = '';
    $response1 = $response1 . '<table id="zero_config" class="mb-0 table-hover table table-striped table-bordered table2excel table2excel_with_colors">    
                                <thead>
                                                <tr>
                                                <th colspan="8">
                                                <label><b>Credit Report</b></label> &nbsp;&nbsp;
                                                    <label><b> બીલ તા. ' . date("d-M-Y", strtotime($_POST['start_bill_date'])) . '  થી  ' . date("d-M-Y", strtotime($_POST['end_bill_date'])) . ' સુધી. </b></label>
                                                </th>
                                                </tr>
                                                <tr>
                                                    <th>#</th>
                                                    <th style="font-size: 16px"><b>બીલ તા.</b></th>
                                                    <th style="font-size: 16px"><b>ચલન નં.</b></th>
                                                    <th style="font-size: 16px"><b>ગાડી નં.</b></th>
                                                    <th style="font-size: 16px"><b> કંપનીનું નામ </b></th>
                                                    <th style="font-size: 16px"><b> બ્રોકરનું નામ </b></th>
                                                    <th style="font-size: 16px"><b> રોયલ્ટી (ટન) </b></th>
                                                    <th style="font-size: 16px"><b> Credit <span class="fa fa-rupee-sign"></span></b></th>
                                                </tr>
                                            </thead>';

    try {
        if ($con) {
            $result = mysqli_query($con, $sql);
            if (mysqli_num_rows($result) > 0) {
                $i = 1;
                $amount = 0;
                $qty = 0;
                $cass = 0;
                $credit = 0;
                while ($row = mysqli_fetch_assoc($result)) {
//                    $boat_owner_name=$row["boat_owner_name"];
                    $amount = $amount + $row["amount"];
                    $qty = $qty + $row["royalti_mujab_vajan"];

                    if ($row["payment_type"] == 'Cass') {
                        $cass = $cass + $row["amount"];
                    } else if ($row["payment_type"] == 'Credit') {
                        $credit = $credit + $row["amount"];
                    }

                    $response1 = $response1 . '<tr>
                        <th scope="row">' . $i . '</th>
                        <td title="બીલ તા.">' . date("d-m-Y", strtotime($row["bill_date"])) . '</td>
                        <td title="ચલન નં."> ' . $row["bill_no"] . '</td>
                        <td title="ગાડી નં.">' . $row["vehicle_no"] . '</td>
                         <td title="Company Name">' . $row["comp_name"] . '</td>    
                        <td title="બ્રોકરનું નામ">' . $row["broker_name"] . '</td> 
                            <td title="રોયલ્ટી વજન (ટન)">' . $row["quantity"] . '</td>
                        <td title="Credit">' . $row["amount"] . '</td>
                        </tr>';


                    $i = $i + 1;
                }
                $response1 = $response1 . '<tr>
                                                        <td></td>
                                                    <td></td>
                                                    <td></td>
                                                    <td></td>
                                                    <td></td>
                                                        <td style="text-align: right"><b>કુલ</b></td>
                                                        <td><b>' . $qty . '</b></td>
                                                        <td><b>' . $credit . '</b></td>
                                                    </tr>';
            } else {
                echo "0 results";
            }
        } else {
            echo 'Connection Problem';
        }
    } catch (Exception $ex) {
        echo $e->getMessage();
    }
    $response1 = $response1 . '</table>';
    echo $response1;
}



if (!isset($_POST['start_bill_date_full']) == "" && !isset($_POST['end_bill_date_full']) == "") {
    $start_bill_date = $_POST['start_bill_date_full'];
    $start_bill_date = $start_bill_date . ' 00:00:05';
    $end_bill_date = $_POST['end_bill_date_full'];
    $end_bill_date = $end_bill_date . ' 23:50:05';

    
    $select_broker_id = $_POST['select_broker_id'];
    $select_company_id = $_POST['select_company_id'];
    $vehicle_no = $_POST['vehicle_no'];
    $qry_broker='';
    $qry_company='';
    $qry_vehicle_no='';
    
    $sql = 'select p.*,c.comp_name,b.broker_name from tbl_party as p left join tbl_company as c on p.company_id=c.id left join tbl_broker as b on p.broker_id=b.id where p.bill_date BETWEEN "' . $start_bill_date . '" and "' . $end_bill_date . '" and p.flag="1"';
    
    if ($select_broker_id == 'Select' || $select_broker_id == '') {
        
    }else{
        $sql=$sql.' and p.broker_id="'.$select_broker_id.'"';
    }
    
    if ($select_company_id == 'Select' || $select_company_id == '') {
        
    }else{
        $sql=$sql.' and p.company_id="'.$select_company_id.'"';
    }
    
    if ($vehicle_no == 'Select' || $vehicle_no == '') {
        
    }else{
        $sql=$sql.' and p.vehicle_no="'.$vehicle_no.'"';
    }
    $sql=$sql.' and p.payment_type="Credit" order by p.id asc';
    
//    echo 'query sql is '.$sql;
    
//    if ($select_broker_id == 'Select' || $select_broker_id == '') {
//        $sql = 'select p.*,c.comp_name,b.broker_name from tbl_party as p left join tbl_company as c on p.company_id=c.id left join tbl_broker as b on p.broker_id=b.id where p.bill_date BETWEEN "' . $start_bill_date . '" and "' . $end_bill_date . '" and p.flag="1" and p.payment_type="Credit" order by p.id asc';
//    } else {
//        $sql = 'select p.*,c.comp_name,b.broker_name from tbl_party as p left join tbl_company as c on p.company_id=c.id left join tbl_broker as b on p.broker_id=b.id where p.bill_date BETWEEN "' . $start_bill_date . '" and "' . $end_bill_date . '" and p.flag="1" and p.payment_type="Credit" order by p.id asc';
//    }
//    echo 'Query '.$sql;


    $response1 = '';
    $response1 = $response1 . '<table id="zero_config" class="mb-0 table-hover table table-striped table-bordered table2excel table2excel_with_colors">    
                                <thead>
                                                <tr>
                                                <th colspan="9">
                                                <label><b>Credit Report</b></label> &nbsp;&nbsp;
                                                    <label><b> બીલ તા. ' . date("d-M-Y", strtotime($_POST['start_bill_date_full'])) . '  થી  ' . date("d-M-Y", strtotime($_POST['end_bill_date_full'])) . ' સુધી. </b></label>
                                                </th>
                                                </tr>
                                                <tr>
                                                    <th>#</th>
                                                    <th style="font-size: 16px"><b>બીલ તા.</b></th>
                                                    <th style="font-size: 16px"><b>ચલન નં.</b></th>
                                                    <th style="font-size: 16px"><b>ગાડી નં.</b></th>
                                                    <th style="font-size: 16px"><b> કંપનીનું નામ </b></th>
                                                    <th style="font-size: 16px"><b> બ્રોકરનું નામ </b></th>
                                                    <th style="font-size: 16px"><b> રોયલ્ટી (ટન) </b></th>
                                                    <th style="font-size: 16px"><b> ખરેખર ભરેલ વજન (ટન) </b></th>
                                                    <th style="font-size: 16px"><b> Credit <span class="fa fa-rupee-sign"></span></b></th>
                                                </tr>
                                            </thead>';

    try {
        if ($con) {
            $result = mysqli_query($con, $sql);
            if (mysqli_num_rows($result) > 0) {
                $i = 1;
                $amount = 0;
                $qty = 0;
                $cass = 0;
                $credit = 0;
                $full_qty = 0;
                while ($row = mysqli_fetch_assoc($result)) {
//                    $boat_owner_name=$row["boat_owner_name"];
                    $amount = $amount + $row["amount"];
                    $qty = $qty + $row["royalti_mujab_vajan"];
                    $full_qty = $full_qty + $row["karekhar_barayel_vajan"];

                    if ($row["payment_type"] == 'Cass') {
                        $cass = $cass + $row["amount"];
                    } else if ($row["payment_type"] == 'Credit') {
                        $credit = $credit + $row["amount"];
                    }

                    $response1 = $response1 . '<tr>
                        <th scope="row">' . $i . '</th>
                        <td title="બીલ તા.">' . date("d-m-Y", strtotime($row["bill_date"])) . '</td>
                        <td title="ચલન નં."> ' . $row["bill_no"] . '</td>
                        <td title="ગાડી નં.">' . $row["vehicle_no"] . '</td>
                         <td title="Company Name">' . $row["comp_name"] . '</td>    
                        <td title="બ્રોકરનું નામ">' . $row["broker_name"] . '</td> 
                            <td title="રોયલ્ટી વજન (ટન)">' . $row["quantity"] . '</td>
                                <td title="ખરેખર ભરેલ વજન (ટન)">' . $row["karekhar_barayel_vajan"].'</td>
                        <td title="Credit">' . $row["amount"] . '</td>
                        </tr>';


                    $i = $i + 1;
                }
                $response1 = $response1 . '<tr>
                                                        <td></td>
                                                    <td></td>
                                                    <td></td>
                                                    <td></td>
                                                    <td></td>
                                                        <td style="text-align: right"><b>કુલ</b></td>
                                                        <td><b>' . $qty . '</b></td>
                                                            <td><b>' . $full_qty . '</b></td>
                                                        <td><b>' . $credit . '</b></td>
                                                    </tr>';
            } else {
                echo "0 results";
            }
        } else {
            echo 'Connection Problem';
        }
    } catch (Exception $ex) {
        echo $e->getMessage();
    }
    $response1 = $response1 . '</table>';
    echo $response1;
}