<?php 
include("../../config.php");
if(isset($_GET['start_date']) && !empty($_GET['start_date'])) {
   $start_date = risFormatDate($_GET['start_date'], 'Y-m-d');
}

if(isset($_GET['end_date']) && !empty($_GET['end_date'])) {
   $end_date   = risFormatDate($_GET['end_date'], 'Y-m-d');
}

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

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

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

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

if($agent == ''){
   $accounts = mysqli_query($conn, "SELECT DISTINCT agent AS agent FROM `lr_mst` WHERE `agent` != '' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'");
   $a_string    = '';
   while ($account_row = mysqli_fetch_assoc($accounts)) {

      $account_name = "'" . $account_row['agent'] . "'";
      
      if(strlen($a_string) == '0'){
         $a_string  = 'agent = '.$account_name;
      }else{
         $a_string  = $a_string .' OR agent = '.$account_name;
      }
   }
}else{
   $files_array = explode(",", $agent);
   $a_string   = '';
   foreach ($files_array as $key => $files_arrays) {
      $account_name = "'" . $files_arrays . "'";
      if(strlen($a_string) == '0'){
         $a_string  = 'agent = '.$account_name;
      }else{
         $a_string  = $a_string .' OR agent = '.$account_name;
      }
   }
}

if($truck_no == ''){
   $accounts = mysqli_query($conn, "SELECT DISTINCT truck_no AS truck_no FROM `lr_mst` WHERE `truck_no` != '' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'");
   $t_string    = '';
   while ($account_row = mysqli_fetch_assoc($accounts)) {

      $account_name = "'" . $account_row['truck_no'] . "'";
      
      if(strlen($t_string) == '0'){
         $t_string  = 'truck_no = '.$account_name;
      }else{
         $t_string  = $t_string .' OR truck_no = '.$account_name;
      }
   }
}else{
   $files_array = explode(",", $truck_no);
   $t_string   = '';
   foreach ($files_array as $key => $files_arrays) {
      $account_name = "'" . $files_arrays . "'";
      if(strlen($t_string) == '0'){
         $t_string  = 'truck_no = '.$account_name;
      }else{
         $t_string  = $t_string .' OR truck_no = '.$account_name;
      }
   }
}

if($employee_id == ''){
   $accounts = mysqli_query($conn, "SELECT DISTINCT emp_id AS employee_id FROM `lr_mst` WHERE `emp_id` != '' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'");
   $w_string    = '';
   while ($account_row = mysqli_fetch_assoc($accounts)) {

      $account_name = "'" . $account_row['employee_id'] . "'";
      
      if(strlen($w_string) == '0'){
         $w_string  = 'emp_id = '.$account_name;
      }else{
         $w_string  = $w_string .' OR emp_id = '.$account_name;
      }
   }
}else{
   $files_array = explode(",", $employee_id);
   $w_string   = '';
   foreach ($files_array as $key => $files_arrays) {
      $account_name = "'" . $files_arrays . "'";
      if(strlen($w_string) == '0'){
         $w_string  = 'emp_id = '.$account_name;
      }else{
         $w_string  = $w_string .' OR emp_id = '.$account_name;
      }
   }
}

if($_SESSION['user_detail']['name'] == 'ALPESH PATEL'){
   if($vendor == ''){
      $query = mysqli_query($conn, "SELECT * FROM `lr_mst` WHERE `l_date` >= '".$start_date."' AND `l_date` <= '".$end_date."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND ($a_string) AND ($t_string) AND emp_id = '2'");
   }else{
      $query = mysqli_query($conn, "SELECT * FROM `lr_mst` WHERE `l_date` >= '".$start_date."' AND `l_date` <= '".$end_date."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND ($a_string) AND ($t_string) AND emp_id = '2' AND (`consignor_name` = '".$vendor."' OR `consignee_name` = '".$vendor."' OR `billing_to` = '".$vendor."')");
   }   
}else{
   if($vendor == ''){
      $query = mysqli_query($conn, "SELECT * FROM `lr_mst` WHERE `l_date` >= '".$start_date."' AND `l_date` <= '".$end_date."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND ($a_string) AND ($t_string)");
   }else{
      $query = mysqli_query($conn, "SELECT * FROM `lr_mst` WHERE `l_date` >= '".$start_date."' AND `l_date` <= '".$end_date."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND ($a_string) AND ($t_string) AND (`consignor_name` = '".$vendor."' OR `consignee_name` = '".$vendor."' OR `billing_to` = '".$vendor."')");
   }
}





/*$query = mysqli_query($conn, "SELECT * FROM `lr_mst` WHERE lr_no = 'ADI/00308'");*/

$sdata = array();
while ($rows = mysqli_fetch_assoc($query)) {
   $sdata[] = $rows;
}
$master_vendor = combine(getAllRecords('vendor_master'), 'id', 'name');
$table = '';
if(count($sdata) > 0){
   $total_income  = 0;
   $total_expense = 0;
   foreach ($sdata as $key => $sdatas) {
      
      $bill = mysqli_query($conn, "SELECT * FROM `billing` WHERE `lr_no` = '".$sdatas['lr_no']."'");
      $bill_row = mysqli_fetch_assoc($bill);

      $gs = mysqli_query($conn, "SELECT sum(amount) AS gs_amount FROM `general_sales_details` WHERE `lr_no` = '".$sdatas['lr_no']."'");
      $gs_row = mysqli_fetch_assoc($gs);

      if(strlen($bill_row['gross_amt']) > 0 && strlen($bill_row['lr_amount']) > 0){
         $bill_gross_amount   = $bill_row['gross_amt'] + $gs_row['gs_amount'];
         $lr_amount        = $bill_row['lr_amount'];
      }else{
         $bill_gross_amount   = $sdatas['freight'];
         $lr_amount        = $sdatas['freight'];
      }

      $bill_percentage    = 100 * $lr_amount / $bill_gross_amount;
      
      $bill_add = mysqli_query($conn, "SELECT sum(amount) AS bill_add  FROM `billing_details` WHERE `bill_no` = '".$bill_row['invoice_no']."' AND type = 'add'");
      $bill_add_row    = mysqli_fetch_assoc($bill_add);
      $bill_add_amount = $bill_add_row['bill_add'];

      $bill_less = mysqli_query($conn, "SELECT sum(amount) AS bill_less  FROM `billing_details` WHERE `bill_no` = '".$bill_row['invoice_no']."' AND type = 'sub'");
      $bill_less_row    = mysqli_fetch_assoc($bill_less);
      $bill_less_amount = $bill_less_row['bill_add'];

      $bill_proportionate_add_amount  = $bill_add_amount*$bill_percentage/100;
      $bill_proportionate_less_amount = $bill_less_amount*$bill_percentage/100;
      
      $income = $lr_amount + $bill_proportionate_add_amount - $bill_proportionate_less_amount;

      if(is_nan($income)){
         $final_income = '0.00';
      }else{
         $final_income = $income;
      }
      
      $total_income += $final_income;

      /*EXPENSE*/
      $lhc     = mysqli_query($conn, "SELECT * FROM `lhc` WHERE `lr_no` = '".$sdatas['lr_no']."'");
      $lhc_row = mysqli_fetch_assoc($lhc);

      $lhc_one     = mysqli_query($conn, "SELECT sum(lr_amount) AS lr_amount FROM `lhc` WHERE `lhc_no` = '".$lhc_row['lhc_no']."'");
      $lhc_one_row = mysqli_fetch_assoc($lhc_one);

      $lhc_add = mysqli_query($conn, "SELECT sum(amount) AS lhc_add FROM `lhc_details` WHERE type = 'add' AND `lhc_id` = '".$lhc_row['lhc_no']."'");
      $lhc_add_row = mysqli_fetch_assoc($lhc_add);

      $lhc_sub = mysqli_query($conn, "SELECT sum(amount) AS lhc_less FROM `lhc_details` WHERE type = 'sub' AND `lhc_id` = '".$lhc_row['lhc_no']."'");
      $lhc_sub_row = mysqli_fetch_assoc($lhc_sub);

      $lorry_hire       = $lhc_row['lorry_hire'];
      if($lhc_one_row['lr_amount'] != '0.00'){
         $temp_a = $lhc_one_row['lr_amount'];
      }else{
         $temp_a = $bill_row['lr_amount'];
      }
      $total_lr_amount  = $temp_a;
      $lhc_percentage     = 100 * $lorry_hire / $total_lr_amount;
      
      if($sdatas['freight'] != '0.00'){
         $freight_amount = $sdatas['freight'];
      }else{
         $freight_amount = $bill_row['lr_amount'];
      }

      $lhc_basic_amount       = $freight_amount*$lhc_percentage/100;

      $lhc_extra_percentage   = $lhc_basic_amount*100/$lorry_hire;
      $lhc_add_amount         = $lhc_add_row['lhc_add']*$lhc_extra_percentage/100;
      $lhc_less_amount        = $lhc_sub_row['lhc_less']*$lhc_extra_percentage/100;
      //$expense             = $lhc_basic_amount + $lhc_add_amount - $lhc_less_amount;
      $expense             = $lorry_hire;


      if(is_nan($expense)){
         $final_expense = '0.00';
      }else{
         $final_expense = $expense;
      }
      
      $total_expense          += $final_expense;
      $profit           = $final_income - $final_expense;

      /* END */
      //if($profit > 0){
         $table .='
         <tr>
            <td class="text-center">'.$sdatas['lr_no'].'</td>
            <td class="text-center">'.risFormatDate($sdatas['l_date'],'d-m-Y').'</td>
            <td>'.@$master_vendor[$sdatas['consignor_name']].'</td>
            <td>'.@$master_vendor[$sdatas['consignee_name']].'</td>
            <td>'.$sdatas['agent'].'</td>
            <td>'.$sdatas['from_place'].'</td>
            <td>'.$sdatas['to_place'].'</td>
            <td class="text-center">'.$sdatas['truck_no'].'</td>
            <td class="text-right">'.customFormatMoney($final_income).'</td>
            <td class="text-right">'.customFormatMoney($final_expense).'</td>
            <td class="text-right">'.customFormatMoney($profit).'</td>
         </tr>
         ';
      //}
   }
   $table.='
   <tr>
      <th colspan="8" class="text-right">TOTAL</th>
      <th class="text-right">'.customFormatMoney($total_income).'</th>
      <th class="text-right">'.customFormatMoney($total_expense).'</th>
      <th class="text-right">'.customFormatMoney($total_income - $total_expense).'</th>
   </tr>
   ';
}
$data = array('status' => 'success', 'tbody' => $table);
echo json_encode($data);
die();
?>