<?php
  include("../config.php");
  require('../mail-settings.php');
  $current_date = date('Y-m-d');
    if(isset($_GET['emp_id']) && !empty($_GET['emp_id'])) {
      $emp_id = strtoupper($_GET['emp_id']);
    }

    if(isset($_GET['type']) && !empty($_GET['type'])) {
      $type = strtoupper($_GET['type']);
    }

    if(isset($_GET['vendor_id']) && !empty($_GET['vendor_id'])) {
      $vendor_id = strtoupper($_GET['vendor_id']);
    }

    $sd = mysqli_real_escape_string($conn, date('Y-m-d', strtotime($_GET['start_date'])));
    $ed = mysqli_real_escape_string($conn, date('Y-m-d', strtotime($_GET['end_date'])));
    if(!empty($vendor_id) && !empty($type) && !empty($emp_id)){
      if($type == 'CONSIGNEE'){
        
        $lr = "SELECT lr_mst.lr_no, lr_mst.l_date, lr_mst.truck_no, lr_mst.nature_of_goods, lr_mst.rate_on, lr_mst.consignor_name, lr_mst.consignee_name, lr_mst.from_place, lr_mst.to_place, lr_mst.tracking_status, lr_mst.truck_driver_no, tracking.t_date, tracking.location, tracking.vendor_id, tracking.branch_id, tracking.company_id FROM lr_mst
          RIGHT JOIN tracking ON lr_mst.lr_no=tracking.lr_no
          WHERE tracking.t_date BETWEEN '".$sd."' AND  '".$ed."' AND tracking.tracking_status = 'P' AND lr_mst.emp_id = '".$emp_id."' AND lr_mst.consignee_name = '".$vendor_id."' AND tracking.branch_id = '".$_SESSION['branch_id']."' AND tracking.company_id = '".$_SESSION['company_id']."'";


      }else{
        $lr = "SELECT lr_mst.lr_no, lr_mst.l_date, lr_mst.truck_no, lr_mst.nature_of_goods, lr_mst.rate_on, lr_mst.consignor_name, lr_mst.consignee_name, lr_mst.from_place, lr_mst.to_place, lr_mst.tracking_status, lr_mst.truck_driver_no, tracking.t_date, tracking.location, tracking.vendor_id, tracking.branch_id, tracking.company_id FROM lr_mst
          RIGHT JOIN tracking ON lr_mst.lr_no=tracking.lr_no
          WHERE tracking.t_date BETWEEN '".$sd."' AND  '".$ed."' AND tracking.tracking_status = 'P' AND lr_mst.emp_id = '".$emp_id."' AND lr_mst.consignor_name = '".$vendor_id."' AND tracking.branch_id = '".$_SESSION['branch_id']."' AND tracking.company_id = '".$_SESSION['company_id']."'";
      }

    }else if(!empty($emp_id)){
      $lr = "SELECT lr_mst.lr_no, lr_mst.l_date, lr_mst.truck_no, lr_mst.nature_of_goods, lr_mst.rate_on, lr_mst.consignor_name, lr_mst.consignee_name, lr_mst.from_place, lr_mst.to_place, lr_mst.tracking_status, lr_mst.truck_driver_no, tracking.t_date, tracking.location, tracking.vendor_id, tracking.branch_id, tracking.company_id FROM lr_mst
          RIGHT JOIN tracking ON lr_mst.lr_no=tracking.lr_no
          WHERE tracking.t_date BETWEEN '".$sd."' AND  '".$ed."' AND tracking.tracking_status = 'P' AND lr_mst.emp_id = '".$emp_id."' AND tracking.branch_id = '".$_SESSION['branch_id']."' AND tracking.company_id = '".$_SESSION['company_id']."'";

    }else if(!empty($vendor_id) && !empty($type)){
      if($type == 'CONSIGNEE'){
          $lr = "SELECT lr_mst.lr_no, lr_mst.l_date, lr_mst.truck_no, lr_mst.nature_of_goods, lr_mst.rate_on, lr_mst.consignor_name, lr_mst.consignee_name, lr_mst.from_place, lr_mst.to_place, lr_mst.tracking_status, lr_mst.truck_driver_no, tracking.t_date, tracking.location, tracking.vendor_id, tracking.branch_id, tracking.company_id FROM lr_mst
          RIGHT JOIN tracking ON lr_mst.lr_no=tracking.lr_no
          WHERE tracking.t_date BETWEEN '".$sd."' AND  '".$ed."' AND tracking.tracking_status = 'P' AND lr_mst.consignee_name = '".$vendor_id."' AND tracking.branch_id = '".$_SESSION['branch_id']."' AND tracking.company_id = '".$_SESSION['company_id']."'";
      }else{
          $lr = "SELECT lr_mst.lr_no, lr_mst.l_date, lr_mst.truck_no, lr_mst.nature_of_goods, lr_mst.rate_on, lr_mst.consignor_name, lr_mst.consignee_name, lr_mst.from_place, lr_mst.to_place, lr_mst.tracking_status, lr_mst.truck_driver_no, tracking.t_date, tracking.location, tracking.vendor_id, tracking.branch_id, tracking.company_id FROM lr_mst
          RIGHT JOIN tracking ON lr_mst.lr_no=tracking.lr_no
          WHERE tracking.t_date BETWEEN '".$sd."' AND  '".$ed."' AND tracking.tracking_status = 'P' AND lr_mst.consignor_name = '".$vendor_id."' AND tracking.branch_id = '".$_SESSION['branch_id']."' AND tracking.company_id = '".$_SESSION['company_id']."'";
      }
    }else if(!empty($vendor_id) && !empty($sd) && !empty($ed)){
      $lr = "SELECT lr_mst.lr_no, lr_mst.l_date, lr_mst.truck_no, lr_mst.nature_of_goods, lr_mst.rate_on, lr_mst.consignor_name, lr_mst.consignee_name, lr_mst.from_place, lr_mst.to_place, lr_mst.tracking_status, lr_mst.truck_driver_no, tracking.t_date, tracking.location, tracking.vendor_id, tracking.branch_id, tracking.company_id FROM lr_mst
          RIGHT JOIN tracking ON lr_mst.lr_no=tracking.lr_no
          WHERE tracking.t_date BETWEEN '".$sd."' AND  '".$ed."' AND tracking.tracking_status = 'P' AND (lr_mst.consignee_name = '".$vendor_id."' OR lr_mst.consignor_name = '".$vendor_id."') AND tracking.branch_id = '".$_SESSION['branch_id']."' AND tracking.company_id = '".$_SESSION['company_id']."'";

      
    }else{

      $lr = "SELECT lr_mst.lr_no, lr_mst.l_date, lr_mst.truck_no, lr_mst.nature_of_goods, lr_mst.rate_on, lr_mst.consignor_name, lr_mst.consignee_name, lr_mst.from_place, lr_mst.to_place, lr_mst.tracking_status, lr_mst.truck_driver_no, tracking.t_date, tracking.location, tracking.vendor_id, tracking.branch_id, tracking.company_id FROM lr_mst
          RIGHT JOIN tracking ON lr_mst.lr_no=tracking.lr_no
          WHERE tracking.t_date BETWEEN '".$sd."' AND  '".$ed."' AND tracking.tracking_status = 'P' AND tracking.branch_id = '".$_SESSION['branch_id']."' AND tracking.company_id = '".$_SESSION['company_id']."'";
    }

    $result     = mysqli_query($conn,$lr);
    $total        = mysqli_num_rows($result);

    $master_vendor        = combine(getAllRecords('vendor_master'), 'id', 'name');
    
    

  $output     = ''; 
  $output .= '<!DOCTYPE html>
<html>
<head>
  <title>OUTWARD REPORT</title>
</head>
<style type="text/css">
  table {
    width:100%;
   }
   table, th, td {
     /*border: 1px solid black;*/
     border-collapse: collapse;
   }
   th, td {
     padding: 15px;
     text-align: left;
     text-transform: uppercase;
   }
   table#t01 tr:nth-child(even) {
    background-color: #eee;
   }
   table#t01 tr:nth-child(odd) {
    background-color: #fff;
   }
   table#t01 th {
     background-color: #4c301e;
     color: white;
   }
  .hitz-span{
    font-size: 11px;
    text-align: center;
    margin-left: 363px;
  }
  
</style>
<body>
<table id="t01" class="" style="width: 0%;position: relative;border: 2px solid;">
  <tr>
    <th colspan="11" style="text-align: center;background-color: #f4e79e;color:#fff;padding: 43px 0px -1px 0px;font-size: 27px;"><h1 class="bill-copy-title" style="color:#4c301e;font-size:25px;">OM ROAD CARRIERS PVT.LTD.</h1><p style="font-size:10px;color:#4c301e;position: relative;bottom: 23px;">Head Office : FF-2,3, Shree Shiddheshwar Hub. Nr. Vaikunth-2, Opp.Vrajdham Complex, Khodiyar Nagar Char Rasta, New VIP Road, Vadodara.390018 Ahmedabad</p>
            <p style="font-size:10px;color:#4c301e;position: relative;bottom: 23px;">Ahmedabad Office : (M) 09377914558, 09904409086. Faridabad Office (M) 09812595039, 09466337914</p>
            <p style="font-size:10px;color:#4c301e;position: relative;bottom: 23px;">E-Mail : info@omroadcarriers.com, omroadcarriers@yahoo.in </p>
            <p style="font-size:10px;color:#4c301e;position: relative;bottom: 23px;">Contact # 0265 2530558, 2532668 (M) 09376514558 (M)</p><p style="font-size: 15px;padding: 12px 0px 7px 0px;
            background: #4c301e;position: relative;bottom: 16px;"><span>TRACKING REPORT STATUS</span></p>
    </th>
  </tr>'; 
  $output .='<tr style="background-color: #b33b3b;color: white;height: 36px;">
    <th style="text-align: center;">LR NO</th>
    <th style="text-align: center;">DATE</th>
    <th style="text-align: center;">TRUCK NO</th>
    <th style="text-align: center;">CONSIGNOR</th>
    <th style="text-align: center;">CONSIGNEE</th>
    <th style="text-align: center;">FROM</th>
    <th style="text-align: center;">TO</th>
    <th style="text-align: center;">DESCRIPTION</th>
    <th style="text-align: center;">PAYMENT MODE</th>
    <th style="text-align: center;">DATE</th>
    <th style="text-align: center;">STATUS</th>
  </tr>';
  while ($row = mysqli_fetch_array($result)) {
    $vendor     = "SELECT * FROM `vendor_master` WHERE `id` = '".$row['vendor_id']."'";
    $vendor_res = mysqli_query($conn,$vendor);
    $vendor_row = mysqli_fetch_assoc($vendor_res);

    $to_mail = $vendor_row['email'];
    $lr      = "SELECT * FROM `lr_mst` WHERE `lr_no` = '".$row['lr_no']."'";
    $lr_res  = mysqli_query($conn,$lr);
    $lr_row  = mysqli_fetch_assoc($lr_res);

  $output .='<tr style="background-color: #f4e79e;color: #000;height: 36px;font-weight: bolder;">
    <td style="text-align: center;">'.$row["lr_no"].'</td>
    <td style="text-align: center;">'. risFormatDate($lr_row['l_date']).'</td>
    <td style="text-align: center;">'. $lr_row['truck_no'].'</td>
    <td style="text-align: center;">'.@$master_vendor[$lr_row['consignor_name']].'</td>
    <td style="text-align: center;">'.@$master_vendor[$lr_row['consignee_name']].'</td>
    <td style="text-align: center;">'.$lr_row['from_place'].'</td>
    <td style="text-align: center;">'.$lr_row['to_place'].'</td>
    <td style="text-align: center;">'.$lr_row['nature_of_goods'].'</td>
    <td style="text-align: center;">'.$lr_row["rate_on"].'</td>
    <td style="text-align: center;">'.risFormatDate($row["t_date"]).'</td>
    <td style="text-align: center;">'.$row["location"].'</td>

  </tr>';
  }
  
  $output .='<tr>
    <th style="padding: 15px;border: 2px solid black;border-collapse: collapse;background-color: #4c301e;font-weight: bolder;" colspan="9"><p style="font-size: 13px;text-align: center;color: #fff;">© '.date('Y').' OM ROAD CARRIERS PVT.LTD. ALL RIGHTS RESERVED.</p></th>
    <th style="padding: 15px;border: 2px solid black;border-collapse: collapse;background-color: #4c301e;font-weight: bolder;" colspan="2"><p style="font-size: 13px;text-align: center;color: #fff;">POWERED BY : ATTRANS</p></th>
  </tr>
</table>
</body>
</html>';
  /*echo'<pre>';
  print_r($output);
  echo'</pre>';
  die();*/
$options = array();

$options['to_email']  = '$to_mail';
$options['cc_emails'] = '';
$options['subject']   = 'TRACKING REPORT';
$options['message']   = $output;


sendMail( $options );

echo json_encode(array('code'=>1));die();

?>

