<?php 
include("../config.php");

$lr_exp_delete = "DELETE FROM `lr_expenses`";
$lr_exp_delete_res 			= mysqli_query($conn,$lr_exp_delete);

$payment_query = "SELECT * FROM `payment`";
$payment_res = mysqli_query($conn, $payment_query);

while ($payment_row = mysqli_fetch_assoc($payment_res)) {
	$amount = $payment_row['amount'];


   $lr_nos = $payment_row['lr_no']; 
   $lr_no_array = explode(',', $lr_nos); 
   $lr_no_list = "'" . implode("', '", $lr_no_array) . "'"; 

   $query = ("SELECT case when billing.lr_no IS NULL then lr_mst.lr_no else billing.lr_no end as lr_no, 
   	         case when billing.total_amnt IS NULL then lr_mst.freight else billing.total_amnt end as total_amnt, 
   	         case when billing.lr_amount IS NULL then lr_mst.freight else billing.lr_amount end as lr_amount, 
   	         case when billing.invoice_no IS NULL then lr_mst.lr_no else billing.invoice_no end as invoice_no, 
   	         case when billing.other_charges IS NULL then 0 else billing.other_charges end as other_charges
   	         FROM lr_mst LEFT OUTER JOIN billing ON lr_mst.lr_no = billing.lr_no 
   	         where lr_mst.lr_no IN ($lr_no_list)");
   $result = mysqli_query($conn, $query);
   
   $lr_final_amount_val = 0;
   $lr_row = array();
   $lr_no_val = array();
   while ($row = mysqli_fetch_assoc($result)) {
     $sum_query = "SELECT SUM(amount) as total_other_amount FROM `billing_details` WHERE bill_no = '".$row['invoice_no']."'";
	  $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;
   }

   foreach ($lr_row as $index => $lr_value) {
      $lr_no_escaped = $lr_no_val[$index];
	  if ($lr_final_amount_val != 0) {
	     $percentage = ($lr_value / $lr_final_amount_val) * 100;
		  $insert_amount_val = ($amount*$percentage)/100;
	  } else {
	  }
	  $lr_exp_ins = "INSERT INTO `lr_expenses`(`voucher_no`, `flag`, `amount`, `lr_no`, `company_id`, `branch_id`, 
	  	            `created_id`, `created_datetime`, `updated_id`, `updated_datetime`)
	          	   VALUES ('".$voucher_no."', 'B', '".$insert_amount_val."', '".$lr_no_escaped."', '".$company_id."',
	          	   '".$branch_id."', '".$created_id."', '".$created_datetime."', '".$updated_id."', '".$updated_datetime."')";
      $lr_exp_ins_res = mysqli_query($conn, $lr_exp_ins);
   }
}
?>