<?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) AND DATE(l_date)>='".$_SESSION['from_year']."' AND DATE(l_date)<='".$_SESSION['to_year']."'");
   }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."') AND DATE(l_date)>='".$_SESSION['from_year']."' AND DATE(l_date)<='".$_SESSION['to_year']."'");
   }
}

$query_delete = mysqli_query($conn, "delete FROM `temp_trip_profit`");
$query_fetch_le = mysqli_query($conn, "SELECT * FROM `lr_mst` WHERE company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' AND DATE(l_date)>='".$_SESSION['from_year']."' AND DATE(l_date)<='".$_SESSION['to_year']."'");
$all_lr_aaray = array();
while ($row_fetch_le = mysqli_fetch_array($query_fetch_le)) {
    $all_lr_aaray[] = $row_fetch_le['lr_no'];
}
//print_r($all_lr_aaray);
$lr_no_list			   = "'" . implode("', '", $all_lr_aaray) . "'";

$query_fetch_all_lr  = "SELECT lr_no, total_amnt, lr_amount, invoice_no, other_charges FROM billing where lr_no 
                      IN ($lr_no_list) AND DATE(bill_date)>='".$_SESSION['from_year']."' AND DATE(bill_date)<='".$_SESSION['to_year']."'";
$result_fetch_all_lr = mysqli_query($conn, $query_fetch_all_lr);
$lr_final_amount_val = 0;
$lr_row = array();
$lr_no_val = array();

//$count_lr_val_row = mysqli_num_rows($result_fetch_all_lr);
// echo " ROW COunt - ".$count_lr_val_row."<br>";
while ($row = mysqli_fetch_assoc($result_fetch_all_lr)) {
   $sum_query = "SELECT SUM(amount) as total_other_amount FROM `billing_details` WHERE bill_no = '".$row['invoice_no']."' AND type = 'add'";
	$result_sum = mysqli_query($conn, $sum_query);
	$row_sum = mysqli_fetch_assoc($result_sum);
	
	$extra_row_amount  = $row_sum['total_other_amount'];
	$lr_no_val[] = $row['lr_no'];
	$lr_no = $row['lr_no'];
	$total_amnt = $row['total_amnt'];
	$lr_amount = $row['lr_amount'];
	$other_charges = $row['other_charges'];
	$sum_of_lr_and_other = $lr_amount + $other_charges;
	
	if ($sum_of_lr_and_other != 0) {
   	$percentage = ($sum_of_lr_and_other*100)/$total_amnt;
	}else{
		$percentage = 0;
	}
	$extra_row_amount_get = ($extra_row_amount*$percentage)/100;
	$lr_final_amount = $extra_row_amount_get + $sum_of_lr_and_other;
	$lr_row[] = $lr_final_amount;
	$lr_final_amount_val += $lr_final_amount;
   $lr_exp_ins = "INSERT INTO `temp_trip_profit`(`lr_no`, `income`, `expense`) VALUES ('".$row['lr_no']."', '".$lr_final_amount."','0' )";
	$lr_exp_ins_res = mysqli_query($conn, $lr_exp_ins);
}

$query_fetch_all_bill = "SELECT lr_no FROM billing WHERE 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']."'";
$result_fetch_all_bill = mysqli_query($conn, $query_fetch_all_bill);
$arry_bill = array();
while ($row_ll = mysqli_fetch_assoc($result_fetch_all_bill)) {
    $arry_bill[] = $row_ll['lr_no'];
}

$result_diff = array_diff($all_lr_aaray, $arry_bill);
//print_r($result_diff);
$all_pending_lr_array_val = "'" . implode("', '", $result_diff) . "'";
$get_all_rest_lr_query = mysqli_query($conn, "SELECT * FROM `lr_mst` WHERE lr_no IN ($all_pending_lr_array_val) AND DATE(l_date)>='".$_SESSION['from_year']."' AND DATE(l_date)<='".$_SESSION['to_year']."'");

while($query_get_all_lr_query = mysqli_fetch_array($get_all_rest_lr_query)){
   //echo $query_get_all_lr_query['lr_no']."<br>";
   $lr_exp_ins = "INSERT INTO `temp_trip_profit`(`lr_no`, `income`, `expense`)
							VALUES ('".$query_get_all_lr_query['lr_no']."', '".$query_get_all_lr_query['freight']."','0' )";
	$lr_exp_ins_res = mysqli_query($conn, $lr_exp_ins);
}

$query_fetch_all_lr_from_income = "SELECT * FROM temp_trip_profit";
$result_fetch_all_lr_from_income = mysqli_query($conn, $query_fetch_all_lr_from_income);
while($row_from_income = mysqli_fetch_array($result_fetch_all_lr_from_income)) {
   //echo $row_from_income['lr_no']."<br>";
   $find_lhc_new     = mysqli_query($conn, "SELECT * FROM `lhc` WHERE `lr_no` = '".$row_from_income['lr_no']."' AND DATE(lhc_date)>='".$_SESSION['from_year']."' AND DATE(lhc_date)<='".$_SESSION['to_year']."'");
   $find_lhc_row_new = mysqli_fetch_assoc($find_lhc_new);
   // echo "LR - ".$row_from_income['lr_no']." -  LHC NO". $find_lhc_row_new['lhc_no']."<br>";

   $find_lr_lhc_new     = mysqli_query($conn, "SELECT * FROM `lhc` WHERE `lhc_no` = '".$find_lhc_row_new['lhc_no']."'AND DATE(lhc_date)>='".$_SESSION['from_year']."' AND DATE(lhc_date)<='".$_SESSION['to_year']."'");
   $all_lhc_table_lr = array();
   $lhc_count = mysqli_num_rows($find_lr_lhc_new);
   while($find_lr_lhc_row_new = mysqli_fetch_assoc($find_lr_lhc_new)){
      $all_lhc_table_lr[] =  $find_lr_lhc_row_new['lr_no'];
      // echo "LR - ".$row_from_income['lr_no']." -  LHC NO". $find_lhc_row_new['lhc_no']." -  Count". $lhc_count."<br>";
   }
   $all_lhc_table_lr_array_val = "'" . implode("', '", $all_lhc_table_lr) . "'";
   $sum_query_for_all_lhc_lr = "SELECT sum(income) as income FROM `temp_trip_profit` WHERE lr_no IN ($all_lhc_table_lr_array_val)";
   $result_sum_for_all_lhc_lr = mysqli_query($conn, $sum_query_for_all_lhc_lr);
   $fetch_sum_for_all_lhc_lr = mysqli_fetch_assoc($result_sum_for_all_lhc_lr);
   $total_lr_wise_income =$fetch_sum_for_all_lhc_lr['income'];
   $sum_query_for_all_lhc_lr_lr = "SELECT lr_no, sum(income) as income FROM `temp_trip_profit` WHERE lr_no  = '".$row_from_income['lr_no']."' group by lr_no";
   $result_sum_for_all_lhc_lr_lr = mysqli_query($conn, $sum_query_for_all_lhc_lr_lr);
   $fetch_sum_for_all_lhc_lr_lr = mysqli_fetch_assoc($result_sum_for_all_lhc_lr_lr);
   $total_lr_wise_income_lr = $fetch_sum_for_all_lhc_lr_lr['income'];
   $get_porp_percentage = ($total_lr_wise_income_lr*100)/$total_lr_wise_income;
   $lhc_asperpercentageamount = ($find_lhc_row_new['net_amnt']*$get_porp_percentage)/100;
   $sum_query_for_lr_expense = "SELECT SUM(amount) as total_lr_expense_amount FROM `lr_expenses` WHERE lr_no = '".$row_from_income['lr_no']."'";
   $result_sum_for_lr_exp = mysqli_query($conn, $sum_query_for_lr_expense);
   $fetch_sum_for_lr_exp = mysqli_fetch_assoc($result_sum_for_lr_exp);
   $total_expense_after_lr_expense_addition = $fetch_sum_for_lr_exp['total_lr_expense_amount'] + $lhc_asperpercentageamount;

   //echo "LR - ".$row_from_income['lr_no']." -  LHC NO - ". $find_lhc_row_new['lhc_no']." -  Count - ". $lhc_count." Income - ".$fetch_sum_for_all_lhc_lr['income']." Income - ".$fetch_sum_for_all_lhc_lr_lr['income']." Percentage of LR - ".$get_porp_percentage." LHC Expense Amt- ".$lhc_asperpercentageamount."Final Expense Amt - ".$total_expense_after_lr_expense_addition."<br>";

   $lr_exp_update = "update `temp_trip_profit` set expense = ".$total_expense_after_lr_expense_addition." where lr_no = '".$row_from_income['lr_no']."'";
	$lr_exp_ins_res = mysqli_query($conn, $lr_exp_update);
}

$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']."' AND DATE(bill_date)>='".$_SESSION['from_year']."' AND DATE(bill_date)<='".$_SESSION['to_year']."'");
      $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']."'AND DATE(bill_date)>='".$_SESSION['from_year']."' AND DATE(bill_date)<='".$_SESSION['to_year']."'");
      $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'] + $bill_row['other_charges'];
      }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;
      }
      
      /*EXPENSE*/
      $lhc     = mysqli_query($conn, "SELECT * FROM `lhc` WHERE `lr_no` = '".$sdatas['lr_no']."'AND DATE(lhc_date)>='".$_SESSION['from_year']."' AND DATE(lhc_date)<='".$_SESSION['to_year']."'");
      $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']."'AND DATE(lhc_date)>='".$_SESSION['from_year']."' AND DATE(lhc_date)<='".$_SESSION['to_year']."'");
      $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;
      }
      $query_fetch_all_lr_from_income_data = "SELECT * FROM temp_trip_profit where lr_no = '".$sdatas['lr_no']."'";
      $result_fetch_all_lr_from_income_data = mysqli_query($conn, $query_fetch_all_lr_from_income_data);
      $row_from_income_data = mysqli_fetch_array($result_fetch_all_lr_from_income_data);
      $profit           = $row_from_income_data['income'] - $row_from_income_data['expense'];

      /* END */
      //if($profit > 0){

      $total_income   += $row_from_income_data['income'];
      $total_expense  += $row_from_income_data['expense'];
      $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($row_from_income_data['income']).'</td>
            <td class="text-right">'.customFormatMoney($row_from_income_data['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();
?>