<?php 
require "commonincludefiles.php";
set_time_limit(0);
ignore_user_abort(true);
ini_set('max_execution_time', 0);

if($_SESSION["cCompany_id"]!="" && isset($_SESSION["cCompany_id"]))
{	

$result ="";
$phone1 = $_POST["phone1"];
$cust_id= $_REQUEST["customer_id"];
$query = " SELECT * FROM `lms_cust` INNER JOIN lms_customer INNER JOIN lms_usermaster WHERE lms_cust.customer_id = lms_customer.customer_id and lms_cust.user_id = lms_usermaster.user_id and lms_cust.company_id = '".$_SESSION['cCompany_id']."'";

   if ($phone1 != "")
	{
	   $query = $query." AND `mobno` LIKE '%".$phone1."%' OR `phoneNo` LIKE '%".$phone1."%' ";//  OR `customer_orgname` LIKE '%".$phone1."%'  ";
	}
	$query = $query." ORDER BY   status   desc , date1 desc , cust_id DESC ";

	    $q = dbQuery($query);
    	$total = dbRows($q);

	$i=0;
	$distictData = array("count" => $total, "rows1" => array());
	if	(($total) > 0) 
		   {
				$temparray = array();
				$cur_date=  Date('d/m/y',time());
				$last_date = date("Y-m-d",strtotime($date."- 5000 days"));
				
				
				while($row = dbArray($q))
				{
					$product_name1=0;
					if($row["status"]=='Completed') {
						
						if($last_date <  $row["date1"])
						{
							$temparray["cust_id"]  = $row["cust_id"];
							$temparray["customer_id"]  = $row["customer_id"];
							$temparray["customer_orgname"]   = $row["customer_orgname"];
							//$temparray["date1"]  = date("d-m-Y", strtotime($row["date1"]));
							$temparray["date1"]  = $row["date1"];
							$temparray["address"]   = $row["address"];
							$temparray["commu_person"]   = $row["commu_person"];
							$temparray["mobno"]   = $row["mobno"];
							$temparray["phoneNo"]   = $row["phoneNo"];					
							$temparray["problem_det"]   = $row["problem_det"];
							$temparray["completed_det"]   = $row["completed_det"];
							$temparray["status"]   = $row["status"];
							$temparray["pending_reason"] =$row["pending_reason"];
							$temparray["pending_work"] =$row["pending_work"];	
							$temparray["cp_mobno"]   = $row["cp_mobno"];				
							$temparray["user_id"]  = $row["user_id"];
							$temparray["user_name"]  = $row["user_name"];
							$temparray["assign_user_id"]  = $row["assign_user_id"];
							
							$temparray["support_sdate"]   = $row["support_sdate"];				
							$temparray["support_edate"]  = $row["support_edate"];
							$temparray["support_timefrom"]  = $row["support_timefrom"];
							$temparray["support_timeto"]  = $row["support_timeto"];
							$temparray["cpmail1"]  = $row["cpmail1"];
							$temparray["ticket"]  = $row["ticket"];
							$asign_qry= "SELECT `user_name` as asign_user_name FROM `lms_usermaster` WHERE `user_id` = '".$temparray["assign_user_id"]."'";
							$asign_res = dbQuery($asign_qry);
							$result_asign = dbArray($asign_res);
							$temparray["asign_user_name"]  = $result_asign["asign_user_name"];
							 //continue; 
						}
						else{
							continue;
						}
					}else
					{
						$temparray["cust_id"]  = $row["cust_id"];
						$temparray["customer_id"]  = $row["customer_id"];
						$temparray["customer_orgname"]   = $row["customer_orgname"];
						//$temparray["date1"]  = date("d-m-Y", strtotime($row["date1"]));
						$temparray["date1"]  = $row["date1"];
						$temparray["address"]   = $row["address"];
						$temparray["commu_person"]   = $row["commu_person"];
						$temparray["mobno"]   = $row["mobno"];
						$temparray["phoneNo"]   = $row["phoneNo"];					
						$temparray["problem_det"]   = $row["problem_det"];
						$temparray["completed_det"]   = $row["completed_det"];
						$temparray["status"]   = $row["status"];
						$temparray["pending_reason"] =$row["pending_reason"];
						$temparray["pending_work"] =$row["pending_work"];	
						$temparray["cp_mobno"]   = $row["cp_mobno"];				
						$temparray["user_id"]  = $row["user_id"];
						$temparray["user_name"]  = $row["user_name"];
						$temparray["assign_user_id"]  = $row["assign_user_id"];
						
						$temparray["support_sdate"]   = $row["support_sdate"];				
						$temparray["support_edate"]  = $row["support_edate"];
						$temparray["support_timefrom"]  = $row["support_timefrom"];
						$temparray["support_timeto"]  = $row["support_timeto"];
							$temparray["cpmail1"]  = $row["cpmail1"];
							$temparray["ticket"]  = $row["ticket"];
						
						$asign_qry= "SELECT `user_name` as asign_user_name FROM `lms_usermaster` WHERE `user_id` = '".$temparray["assign_user_id"]."'";
						$asign_res = dbQuery($asign_qry);
						$result_asign = dbArray($asign_res);
						$temparray["asign_user_name"]  = $result_asign["asign_user_name"];
					}
					$due_qry= "SELECT SUM(inv.dueamount) AS dueamount FROM  lms_customerinvoice inv where inv.status != 'Paid' AND inv.customer_id='".$temparray["customer_id"]."' group by inv.customer_id ";
					//echo $due_qry;
						
					$out = dbQuery($due_qry);
					$result = dbArray($out);
					
					$temparray["dueamount"]  = $result["dueamount"];
					
					$qry_billchk  = " SELECT * from lms_customerinvoice WHERE customer_id='".$temparray["customer_id"]."'";	
					//echo $qry_billchk;
					
					$out_billchk = dbQuery($qry_billchk);
					if(dbRows($out_billchk) > 0){
					
					/*$qry_product =  " SELECT 
										concat('Inv : ',inv.customerinvoice_no,'  ',DATE_FORMAT(inv.customerinvoice_date,'%d-%m-%Y'),'  ',cpm.product_name,' ( ',cpm.product_type,' )',' : ',FLOOR(invp.product_qty),' * ',invp.product_rate,' = ',FLOOR(invp.product_qty*invp.product_rate))  as product_name,inv.status,cpm.product_type
							FROM 
								lms_customerinvoice inv,
								lms_customerinvoiceproduct invp,
								lms_productmaster cpm
							where 
								
								invp.customerinvoice_id = inv.customerinvoice_id AND
								invp.product_id = cpm.product_id AND 
								inv.customer_id='".$temparray["customer_id"]."' ";
										
					$out_product = dbQuery($qry_product);
					if(dbRows($out_product) > 0){
						$product_name = "";
						while($result1 = dbArray($out_product)){
							if($result1['status'] != 'Paid' ||  $result1['product_type'] == 'AMC' ){
								$product_name[$temparray["customer_id"]][] = $result1['product_name'];
							}
							else{
								continue;
							}
						}
						$product_name1 = implode("\n", $product_name[$temparray["customer_id"]]);
						$temparray["amount_desc"]  = $product_name1;
					}*/
					$qry_product="SELECT 
						inv.customerinvoice_no,
						DATE_FORMAT(inv.customerinvoice_date,'%d-%m-%Y') as date,
						cpm.product_type,
						cpm.product_name,
						cpm.product_type,
						inv.status,
						invp.product_qty,
						invp.product_rate,
						floor(invp.product_qty*invp.product_rate) as due
					 FROM 
						lms_customerinvoice inv,
						lms_customerinvoiceproduct invp,
						lms_productmaster cpm
					where 
						invp.customerinvoice_id = inv.customerinvoice_id AND
						invp.product_id = cpm.product_id AND 
						inv.customer_id='".$temparray["customer_id"]."' and
						(cpm.product_type = 'AMC' or (cpm.product_type != 'AMC' && inv.status != 'Paid'))";
	
				$out_product = dbQuery($qry_product);
				$data = array();
					//echo "<pre>";
				if (dbRows($out_product)){
					$k=0;
					while($result1 = dbArray($out_product))
					{
						if($result1['product_type'] != 'AMC'){
							$data[$k] = $result1;	
							$data[$k]['total']=  $result1['product_qty']*$result1['product_rate'];
							$data[$k]['calulation']=  $result1['product_qty']." * ".$result1['product_rate']." = ".$data[$i]['total'];
						$k++;	
						}
						else
						{
							if(!(isset($data['AMC']))){
								$data['AMC'] = $result1;
								$data['AMC']['total']= $result1['product_qty']*$result1['product_rate'];
								//$data['AMC']['calulation']=  $data['AMC']['product_qty']." * ".$data['AMC']['product_rate']." = ".$data['AMC']['total'];
							}else{
								$data['AMC']['date'] =  $result1['date'];
								$data['AMC']['product_name'] =  $result1['product_name'];
								$data['AMC']['total'] = $data['AMC']['product_rate'] + $result1['product_rate'];
								//$data['AMC']['calulation']=  $data['AMC']['product_rate']." + ".$result1['product_rate']." = ".$data['AMC']['total'];
							}
						}
					}
					foreach($data as $d){
						$product_name1 .= $d['date']." - ".$d['product_name']." ( ".$d['product_type']." ) ".$d['calulation']."\n";
					}
				}else{
					$data=0;
					$product_name1 ="";
				}
				$temparray["amount_desc"]=$product_name1;
			}
			else{
				$temparray["amount_desc"]  = " Bill is not Avaible" ;
			}
			$distictData["rows1"][$i] = $temparray;   
			$i++;
			}
					
			
		}
		/*echo "sss".$cur_date."<br/>".$last_date;
		
		exit;*/
 	echo json_encode($distictData);	
}
?>