<?php
include('db_con.php');
include('function.php');
$cust_id = 0;
 if(isset($_POST['cust_id']))
	{
		$cust_id = $_POST['cust_id'];
		$acc_no = "104".$cust_id;
		
		$query = "
		SELECT * FROM customers WHERE cust_id = :cust_id
		";
		$statement = $connect->prepare($query);
		$statement->execute(
			array(
				':cust_id'	=>	$cust_id
			)
		);
		$result = $statement->fetchAll();
		foreach($result as $row)
		{
			$output['sm_id'] = $row['sm_id'];
			$output['cr_limit'] = $row['cr_limit'];
			$output['sale_target'] = $row['sale_target'];
			$due_days = $row['due_days'];

			
			
			
		}
		$sql = "select ifnull(due_day,0) from sale where cust_id = '".$cust_id."' order by dated desc LIMIT 1";
		$statement = $connect->prepare($sql);
		$statement->execute();
		$last_due_days = $statement->fetchColumn();
		if($last_due_days > 0)
		{
			$due_day = $last_due_days;
		}else
		{
			$due_day = $due_days;
		}
		$sql = "select ifnull(gst,'OFF') from sale where cust_id = '".$cust_id."' order by dated desc LIMIT 1";
		$statement = $connect->prepare($sql);
		$statement->execute();
		$gst = $statement->fetchColumn();
		$sql = "
    SELECT IFNULL(sum(d.debit-d.credit),0) from jv_detail d, jv m where m.jv_id=d.jv_id
    and d.acc_no = '".$acc_no."'
    ";
    $statement = $connect->prepare($sql);
            $statement->execute(); 
            $balance = $statement->fetchColumn();
         
          
   
		 	
            $output['balance'] = $balance;
            $output['gst'] = $gst;
 		$count = 0;
        $sr = 0;
        $tot_amount= 0;
        $gross_amount = 0;
        $amount = 0;
 
$dated= date('Y-m-d');
$gross_total = 0;
    
$acc_name = 'ALL';
      $statement = $connect->prepare("select m.cust_id,c.name from customers c,sale m where m.cust_id=c.cust_id and m.cust_id LIKE '%".$cust_id."%'  group by cust_id");
 $statement->execute();
 
 $result = $statement->fetchAll();   
      $detail_statement = $connect->prepare("
select m.s_id,c.name,m.inv_no,m.dated,m.description, m.due_day,m.due_dated,sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty)   + sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty)  * gst_pr / 100 -m.discount + m.freight as amount from sale m, sale_detail d,customers c where m.cust_id=c.cust_id and m.s_id = d.s_id AND m.inv_type = 'Credit' and m.cust_id LIKE '%".$cust_id."%' group by s_id,inv_no,dated,description,m.cust_id order by name
");
      $detail_statement->execute();
      $detail_result = $detail_statement->fetchAll();
      $payment_statement = $connect->prepare("
select sum(m.amount) from rec_voucher m where m.cust_id LIKE '%".$cust_id."%' GROUP BY CUST_ID
");
      $payment_statement->execute();
      $payment = $payment_statement->fetchColumn();
 
  
  $count = $count+1;
  
   
 
        foreach($detail_result as $detail_row)
 {
 
  $dated = date('Y-m-d');
  $due_dated = $detail_row['due_dated'];
 $date1_ts = strtotime($due_dated);
    $date2_ts = strtotime($dated);
    $diff = $date2_ts - $date1_ts;
    $diff = round($diff / 86400);
$net_total = 0;
$due_day = $detail_row['due_day'];
if($diff > 0){
//$days = $days - $due_day;
$amount = $detail_row["amount"];
$amount = round($amount);
 $tot_amount = $tot_amount + $amount;
 $gross_total = 0;
if($tot_amount > $payment)
{
 $gross_amount= $gross_amount + $amount;
   }
   
}
 }
$output['over_amount'] = $gross_amount;
$output['due_day'] = $due_day;
		echo json_encode($output);
	}
		
	
	
?>