<?php
   $master_vendor = combine(getAllRecords('vendor_master'), 'id', 'name');
   $bill_table    = combine(getAllRecords('billing'), 'invoice_no', 'vendor_id');
   $CURDATE       = date('Y-m-d');
   $new_date      = date('Y-m-d', strtotime($CURDATE . " + 10 day"));

   $lr             = "SELECT * FROM `lr_mst` WHERE DATE(l_date) = '".$CURDATE."' AND  company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND DATE(created_datetime)>='".$_SESSION['from_year']."' AND DATE(created_datetime)<='".$_SESSION['to_year']."'";
   $lr_res         = mysqli_query($conn,$lr);
   $total_records  = mysqli_num_rows($lr_res);
   
   $vendor     = "SELECT * FROM `vendor_master` WHERE `status` = '0'";
   $vendor_res = mysqli_query($conn,$vendor);
   $vendor_num = mysqli_num_rows($vendor_res);
   
   $truck     = "SELECT * FROM `truck_mst`";
   $truck_res = mysqli_query($conn,$truck);
   $truck_num = mysqli_num_rows($truck_res);
   
   $lrs     = "SELECT * FROM `lr_mst` WHERE  company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND DATE(created_datetime)>='".$_SESSION['from_year']."' AND DATE(created_datetime)<='".$_SESSION['to_year']."'";
   $lrs_res = mysqli_query($conn,$lrs);
   $lrs_num = mysqli_num_rows($lrs_res);
   
   $bill = "SELECT * FROM `billing` WHERE DATE(bill_date) = '".$CURDATE."' AND  company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND DATE(bill_date)>='".$_SESSION['from_year']."' AND DATE(bill_date)<='".$_SESSION['to_year']."' GROUP BY invoice_no";
   $bill_res = mysqli_query($conn,$bill);
   $bill_num = mysqli_num_rows($bill_res);
   
   $emp_mst = mysqli_query($conn, "SELECT * FROM `emp_mst` WHERE `status` = '0'");
   $emp_nos = mysqli_num_rows($emp_mst); 

   $lr_master = mysqli_query($conn,"SELECT * FROM `lr_mst` WHERE  company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND DATE(eway_ex_date)>='".$CURDATE."' AND DATE(eway_ex_date)<='".$new_date."' ORDER BY eway_ex_date ASC");
   $lr_master_num = mysqli_num_rows($lr_master);

   
   $top_delete = mysqli_query($conn, "DELETE FROM `top_five_bill`");

   $bill_query = mysqli_query($conn, "SELECT vendor_id,MONTH(bill_date) AS month,sum(net_amt) AS amount FROM `top_five_bill` WHERE bill_date >= '".$_SESSION['from_year']."' AND bill_date <= '".$_SESSION['to_year']."' GROUP BY vendor_id,MONTH(bill_date) ORDER BY MONTH(bill_date) ASC,amount DESC,vendor_id");
   
   $temp_month = '';
   $seq_no = 0;
   while ($bill_rows = mysqli_fetch_assoc($bill_query)) {
      
      if($temp_month == $bill_rows['month']){
         $seq_no = $seq_no + 1;
         $insert = mysqli_query($conn,"INSERT INTO `top_five_bill`(`invoice_no`, `vendor_id`, `net_amt`, `month`, `seq_id`) VALUES ('".$bill_rows['invoice_no']."','".$bill_rows['vendor_id']."','".$bill_rows['amount']."','".$bill_rows['month']."','".$seq_no."')");
         $temp_month = $bill_rows['month'];
      }else{
         $seq_no = 1;
         $insert = mysqli_query($conn,"INSERT INTO `top_five_bill`(`invoice_no`, `vendor_id`, `net_amt`, `month`, `seq_id`) VALUES ('".$bill_rows['invoice_no']."','".$bill_rows['vendor_id']."','".$bill_rows['amount']."','".$bill_rows['month']."','".$seq_no."')");
         $temp_month = $bill_rows['month'];
      }
   }

   $top5 = mysqli_query($conn, "SELECT * FROM `top_five_bill` WHERE `seq_id` <= '5'");
   $top_five_data = array();
   while ($top5_row = mysqli_fetch_assoc($top5)) {
      $top_five_data[] = $top5_row;
   }
   
   /*30 DAYS NOTIFY*/
   $ageing = mysqli_query($conn ,"SELECT * FROM `billing` WHERE `bill_submited_date` <= '".$CURDATE."' AND DATE(bill_date)>='".$_SESSION['from_year']."' AND DATE(bill_date)<='".$_SESSION['to_year']."'");
   $ageing_data = array();
   while ($ageing_row = mysqli_fetch_assoc($ageing)) {
      $bill_submited_date  = $ageing_row['bill_submited_date'];
      $to_date  = time();
      $day_diff = $to_date - strtotime($bill_submited_date);
      $tilldays = floor($day_diff/(60*60*24));
      if($tilldays >= 30){
         $ageing_data[] = $ageing_row;
      }
   }

   $due_alert = array();
   foreach ($ageing_data as $key => $ageing_datas) {
      $trans = mysqli_query($conn, "SELECT * FROM `trans` WHERE `voucher_no` = '".$ageing_datas['invoice_no']."' AND `flag` = 'BILLING' AND `company_id` = '".$_SESSION['company_id']."' AND `branch_id` = '".$_SESSION['branch_id']."'");
      $trans_row = mysqli_fetch_assoc($trans);

      if($trans_row['balance'] > 0){
         $due_alert[] = $trans_row;
      }
   }
?>

<section class="content-header">
   <h3 class="capital font15 bold">Dashboard</h3>
   <hr/>
</section>

<section class="content">
    <div class="row">
        <div class="col-lg-3 col-xs-6">
        <a href="index.php?page=vendor/list.php">
        <div class="small-box bg-purple">
           <div class="inner">
              <h3><?php echo $vendor_num;?></h3>
              <p>VENDOR</p>
           </div>
           <div class="icon">
              <i class="fa fa-users" aria-hidden="true"></i>
           </div>
        </div>
        </a>
        </div>
        <div class="col-lg-3 col-xs-6">
        <a href="index.php?page=truck_mst/list.php">
        <div class="small-box bg-yellow">
           <div class="inner">
              <h3><?php echo $truck_num;?></h3>
              <p>TRUCK</p>
           </div>
           <div class="icon">
              <i class="fa fa-truck" aria-hidden="true"></i>
           </div>
        </div>
        </a>
        </div>
        <div class="col-lg-3 col-xs-6">
        <a href="index.php?page=lr/list.php">
        <div class="small-box bg-aqua">
           <div class="inner">
              <h3><?php echo $lrs_num;?></h3>
              <p>LR BOOKED</p>
           </div>
           <div class="icon">
              <i class="fa fa-bold" aria-hidden="true"></i>
           </div>
        </div>
        </a>
        </div>
        <div class="col-lg-3 col-xs-6">
        <a href="index.php?page=emp_mst/list.php">
        <div class="small-box bg-green">
           <div class="inner">
              <h3><?php echo $emp_nos;?></h3>
              <p>EMPLOYEE</p>
           </div>
           <div class="icon">
              <i class="fa fa-etsy" aria-hidden="true"></i>
           </div>
        </div>
        </a>
        </div>
    </div>
    <div class="row">
        <section class="col-lg-6">
            <div class="box box-primary">
                <div class="box-header">
                   <h3 class="box-title">TODAY'S LR BOOKING</h3>
                </div>
                <div class="box-body">
                   <div class="table-responsive">
                      <table class="table table-bordered table-striped list_data no-margin">
                         <thead>
                            <tr>
                               <th class="text-center">LR NO</th>
                               <th class="text-center">FROM</th>
                               <th class="text-center">TO</th>
                               <th class="text-center">CONSIGNOR</th>
                               <th class="text-center">CONSIGNEE</th>
                               <th class="text-center">WEIGHT</th>
                               <th class="text-center">PCS</th>
                            </tr>
                         </thead>
                         <tbody>
                            <?php 
                               $count=$total_records;  if($count>0){
                               while ($lr_row = mysqli_fetch_array($lr_res)) { 
                               $vendor = "SELECT * FROM `vendor_master` WHERE `id` = '".$lr_row['consignor_name']."'";
                               $vendor_res = mysqli_query($conn,$vendor);
                               $vendor_row = mysqli_fetch_array($vendor_res);
                               
                               $vendor1 = "SELECT * FROM `vendor_master` WHERE `id` = '".$lr_row['consignee_name']."'";
                               $vendor1_res = mysqli_query($conn,$vendor1);
                               $vendor1_row = mysqli_fetch_array($vendor1_res);
                               ?>
                            <tr align="center">
                               <td><a href="#" id="<?php echo $lr_row['lr_no'];?>" onclick="viewData(this);"><?php echo $lr_row['lr_no'];?></a></td>
                               <td><?php echo $lr_row['from_place'];?></td>
                               <td><?php echo $lr_row['to_place'];?></td>
                               <td><?php echo $vendor_row['name'];?></td>
                               <td><?php echo $vendor1_row['name'];?></td>
                               <td><?php 
                                  if($lr_row['weight'] == ''){
                                    echo "-";
                                  }else{
                                    echo $lr_row['weight'];
                                  }
                                  ?></td>
                               <td><?php 
                                  if($lr_row['pcs'] == ''){
                                    echo "-";
                                  }else{
                                    echo $lr_row['pcs'];
                                  }
                                  ?></td>
                            </tr>
                            <?php } }else{ ?>
                            <tr>
                               <td colspan="7" align="center"><b><?php echo "NO LR BOOKED...!!!!"; ?></b></td>
                            </tr>
                            <?php } ?>
                         </tbody>
                      </table>
                   </div>
                </div>
            </div>
            <div class="box box-primary">
                <div class="box-header">
                   <h3 class="box-title">TODAY'S BILL</h3>
                </div>
                <div class="box-body">
                   <div class="table-responsive">
                      <table class="table table-bordered table-striped list_data no-margin">
                         <thead>
                            <tr>
                               <th class="text-center" width="10">SR.NO</th>
                               <th class="text-center" width="80">BILL NO</th>
                               <th class="text-center">VENDOR</th>
                               <th class="text-center" width="100">NET AMOUNT</th>
                               <th class="text-center" width="100">GROSS AMOUNT</th>
                            </tr>
                         </thead>
                         <tbody>
                            <?php 
                               $cou = 1;
                               $total_net_amnt   = 0;
                               $total_gross_amnt = 0;
                               $counts=$bill_num;  if($counts>0){
                               while ($bill_row = mysqli_fetch_array($bill_res)) {
                               $vendor = "SELECT * FROM `vendor_master` WHERE `id` = '".$bill_row['vendor_id']."'";
                               $vendor_res = mysqli_query($conn,$vendor);
                               $vendor_row = mysqli_fetch_array($vendor_res);
                               $total_net_amnt   += $bill_row['net_amt'];
                               $total_gross_amnt += $bill_row['gross_amt']; 
                               ?>
                            <tr align="center">
                               <td><?php echo $cou++;?></td>
                               <td><a href="#" id="<?php echo $bill_row['invoice_no'];?>" onclick="BillCopyPrint(this);"><?php echo $bill_row['invoice_no'];?></a></td>
                               <td><?php echo $vendor_row['name'];?></td>
                               <td align="right"><?php echo customFormatMoney($bill_row['net_amt']);?></td>
                               <td align="right"><?php echo customFormatMoney($bill_row['gross_amt']);?></td>
                            </tr>
                            <?php }  ?>
                            <tr>
                               <td colspan="3" class="bold">TOTAL</td>
                               <td align="right"><?php echo customFormatMoney($total_net_amnt)?></td>
                               <td align="right"><?php echo customFormatMoney($total_gross_amnt)?></td>
                            </tr>
                            <?php }else{ ?>
                            <tr>
                               <td colspan="7" align="center"><b><?php echo "NO BILL BOOKED...!!!!"; ?></b></td>
                            </tr>
                            <?php } ?>
                         </tbody>
                      </table>
                   </div>
                </div>
            </div>
            <div class="box box-primary">
                <div class="box-header">
                   <h3 class="box-title">ABOVE 30 DAYS</h3>
                </div>
                  <div class="box-body">
                     <div class="table-responsive">
                        <table class="table table-bordered list_data no-margin">
                           <thead>
                              <tr>
                                 <th class="text-left">VENDOR</th>
                                 <th class="text-left" width="15%">BILL</th>
                                 <th class="text-right" width="10%">AMOUNT</th>
                                 <th class="text-right" width="10%">RECEIVED</th>
                                 <th class="text-right" width="10%">DUE</th>
                              </tr>
                           </thead>
                           <tbody>
                           <?php 
                           if(count($due_alert) > 0){
                              foreach ($due_alert as $key => $due_alerts) { ?>
                                 <tr>
                                    <td><?php echo @$master_vendor[$bill_table[$due_alerts['voucher_no']]]?></td>
                                    <td><?php echo $due_alerts['voucher_no']?></td>
                                    <td class="text-right"><?php echo $due_alerts['amount']?></td>
                                    <td class="text-right"><?php echo $due_alerts['adjust']?></td>
                                    <td class="text-right"><?php echo $due_alerts['balance']?></td>
                                 </tr>
                           <?php   }
                           }else{ ?>
                           <tr>
                              <th colspan="3">NO DATA FOUND...!!!!</th>
                           </tr>
                           <?php } ?>
                           </tbody>
                        </table>
                     </div>
                  </div>
            </div>
        </section>
         <section class="col-lg-6">
            <div class="box box-primary">
                <div class="box-header">
                   <h3 class="box-title">TOP 5 BILLING (MONTH)</h3>
                </div>
                  <div class="box-body">
                     <div class="table-responsive">
                        <table class="table table-bordered list_data no-margin">
                           <thead>
                              <tr>
                                 <th class="text-left">VENDOR</th>
                                 <th class="text-right" width="15%">AMOUNT</th>
                              </tr>
                           </thead>
                           <tbody>
                              <?php if(count($top_five_data) > 0){ ?>
                              <?php 
                              $temp_month = '';
                              foreach ($top_five_data as $key => $top_five_datas) { 
                              if($top_five_datas['month'] == '1'){
                                 $month_name = 'January';
                              }else if($top_five_datas['month'] == '2'){
                                 $month_name = 'February';
                              }else if($top_five_datas['month'] == '3'){
                                 $month_name = 'March';
                              }else if($top_five_datas['month'] == '4'){
                                 $month_name = 'April';
                              }else if($top_five_datas['month'] == '5'){
                                 $month_name = 'May';
                              }else if($top_five_datas['month'] == '6'){
                                 $month_name = 'June';
                              }else if($top_five_datas['month'] == '7'){
                                 $month_name = 'July';
                              }else if($top_five_datas['month'] == '8'){
                                 $month_name = 'August';
                              }else if($top_five_datas['month'] == '9'){
                                 $month_name = 'September';
                              }else if($top_five_datas['month'] == '10'){
                                 $month_name = 'October';
                              }else if($top_five_datas['month'] == '11'){
                                 $month_name = 'November';
                              }else{
                                 $month_name = 'December';
                              }
                              ?>
                              <?php if($temp_month == $top_five_datas['month']){ ?>
                              <tr>
                                 <td class="text-left" width=""><?php echo @$master_vendor[$top_five_datas['vendor_id']]?></td>
                                 <td class="text-right" width="15%"><?php echo $top_five_datas['net_amt']?></td>
                              </tr>
                              <?php }else{ ?>
                              <tr>
                                 <th class="text-left" width="100%" colspan="3"><?php echo $month_name?></th>
                              </tr>
                              <tr>
                                 <td class="text-left" width=""><?php echo @$master_vendor[$top_five_datas['vendor_id']]?></td>
                                 <td class="text-right" width="15%"><?php echo $top_five_datas['net_amt']?></td>
                              </tr>
                              <?php } ?>
                              <?php 
                              $temp_month = $top_five_datas['month'];
                              ?>
                              <?php } ?>
                              <?php } ?>
                           </tbody>
                        </table>
                     </div>
                  </div>
            </div>
        </section>
        <section class="col-lg-12">
            <div class="box box-primary">
            <div class="box-header">
               <h3 class="box-title">E-WAY BILL EXPIRED ALERT</h3>
            </div>
            <div class="box-body">
               <div class="table-responsive">
                    <table class="table table-bordered list_data no-margin">
                        <thead>
                            <tr>
                              <th width="10%" class="text-center">LR NO</th>
                              <th width="10%" class="text-center">DATE</th>
                              <th width="10%" class="text-center">TRUCK NO</th>
                              <th width="60%" class="text-left">E-WAY BILL NO</th>
                              <th width="10%" class="text-center">E-WAY EXP. DATE</th>
                            </tr>
                        </thead>
                        <tbody>
                        <?php 
                        $counts=$lr_master_num;  if($counts>0){
                        while ($lr_rows = mysqli_fetch_array($lr_master)) { ?>
                        <tr align="center" <?php echo $lr_rows['eway_ex_date'] <= $CURDATE?'class="divtoBlink"':''; ?>>
                          <td width="10%"><?php echo $lr_rows['lr_no'];?></td>
                          <td width="10%"><?php echo risFormatDate($lr_rows['l_date']);?></td>
                          <td width="10%"><?php echo $lr_rows['truck_no'];?></td>
                          <td width="60%"><?php echo $lr_rows['eway_bill_no'];?></td>
                          <td width="10%"><?php echo risFormatDate($lr_rows['eway_ex_date']);?></td>
                        </tr>
                        <?php } }else{ ?>
                        <tr>
                          <td colspan="11" align="center"><b><?php echo "NO DATA FOUND...!!!!"; ?></b></td>
                        </tr>
                        <?php } ?>
                  </tbody>
                  </table>
               </div>
            </div>
         </div>
        </section>
    </div>
</section>

<script type="text/javascript">
function viewData(e) {
   var $i = $(e).attr("id");
   $.ajax({
       url: "lr/view.php",
       method: "post",
       data: {
           lr_no: $i
       },
       success: function(data) {
           $('#employee_detail').html(data);
           $('#dataModal').modal("show");
       }
   });
}

function BillCopyPrint(e) {
   var $i = $(e).attr("id");
   $.ajax({
       url: "bill/bill_copy_print.php",
       method: "post",
       data: {
           id: $i
       },
       success: function(data) {
           $('#employee_detail').html(data);
           $('#dataModal').modal("show");
       }
   });
}
</script>