<?php
include_once("../classes/class.MySQL.php");
$db	=new MySQL();
$action = $_GET['act'];

if($action == "listall"){

}elseif($action == 'list'){
	
	$fromdate 	= date('Y-m-d',strtotime($_GET['fdate']));
	$todate 	= date('Y-m-d',strtotime($_GET['tdate']));
	$userid		= $_GET['userid'];
	
	if($_GET['fdate'] == ''){
		$fromdate = date('Y-m-d');
	}
	if($_GET['tdate'] ==''){
		$todate	  = date('Y-m-d');
	}
	
	
	
	$creditDetaild = $db->fetchQuery("SELECT sum(credit_amount) as totalCredit, totalPaid, B.name,B.phone,B.email,id  FROM credit_given A 
		LEFT JOIN user_details B ON A.user_id = B.id
		LEFT JOIN (SELECT sum(paid_amount) as totalPaid, user_id, date_time FROM credit_payment WHERE DATE(date_time) between '$fromdate' and '$todate' GROUP BY user_id) E ON A.user_id=E.user_id 
		WHERE DATE(A.date_time) between '$fromdate' and '$todate' GROUP BY A.user_id");
		
	/*	echo "SELECT sum(credit_amount) as totalCredit, totalPaid, B.name,B.phone,B.email,id  FROM credit_given A 
		LEFT JOIN user_details B ON A.user_id = B.id
		LEFT JOIN (SELECT sum(paid_amount) as totalPaid, user_id, date_time FROM credit_payment WHERE DATE(date_time) between '$fromdate' and '$todate') E ON A.user_id=E.user_id 
		WHERE DATE(A.date_time) between '$fromdate' and '$todate' GROUP BY A.user_id";*/
		
		
/*		echo "SELECT sum(credit_amount) as totalCredit, totalPaid, B.name,B.phone,B.email,id  FROM credit_given A 
		LEFT JOIN user_details B ON A.user_id = B.id
		LEFT JOIN (SELECT sum(paid_amount) as totalPaid, user_id, date_time FROM credit_payment D WHERE DATE(date_time) between '$fromdate' and '$todate') E ON A.user_id=E.user_id 
		WHERE DATE(A.date_time) between '$fromdate' and '$todate' GROUP BY A.user_id";	*/							  
										  
	echo '{"creditDetails":'.json_encode($creditDetaild).'}';
	
	
	
	
	
}elseif($action == "listbyuser"){

	$fromdate 	= date('Y-m-d',strtotime($_GET['fdate']));
	$todate 	= date('Y-m-d',strtotime($_GET['tdate']));
	$userid		= $_GET['userid'];
	
	if($_GET['fdate'] == ''){
		$fromdate = date('Y-m-d');
	}
	if($_GET['tdate'] ==''){
		$todate	  = date('Y-m-d');
	}
	
	
	
	/*$creditDetaild = $db->fetchQuery("SELECT sum(credit_amount) as totalCredit, totalPaid, B.name,B.phone,B.email,id,A.bill_number,DATE(A.date_time) as date   FROM credit_given A 
		LEFT JOIN user_details B ON A.user_id = B.id
		LEFT JOIN (SELECT sum(paid_amount) as totalPaid, user_id, date_time FROM credit_payment WHERE DATE(date_time) between '$fromdate' and '$todate' and user_id='$userid' ) E ON A.user_id=E.user_id 
		WHERE DATE(A.date_time) between '$fromdate' and '$todate' and A.user_id = '$userid' GROUP BY DATE(A.date_time) ");*/
							
										  
/*		$creditDetaild = $db->fetchQuery("select  creditamount,paidamount from user_details as B
											LEFT JOIN (SELECT sum(credit_amount) as creditamount, user_id, date_time FROM credit_given where DATE(date_time) between '$fromdate' and '$todate' group by user_id,Date(date_time) ) E ON B.id=E.user_id
											LEFT JOIN (SELECT sum(paid_amount) as paidamount, user_id, date_time FROM credit_payment where DATE(date_time) between '$fromdate' and '$todate' group by user_id,Date(date_time) ) C ON B.id=C.user_id
											WHERE B.id = '$userid' group by DATE(C.date_time)
											");	
				*/
							  
/*					echo "select  creditamount,paidamount from user_details as B
											LEFT JOIN (SELECT sum(credit_amount) as creditamount, user_id, date_time FROM credit_given where DATE(date_time) between '$fromdate' and '$todate' group by user_id,Date(date_time) ) E ON B.id=E.user_id
											LEFT JOIN (SELECT sum(paid_amount) as paidamount, user_id, date_time FROM credit_payment where DATE(date_time) between '$fromdate' and '$todate' group by user_id,Date(date_time) ) C ON B.id=C.user_id
											WHERE B.id = '$userid' group by DATE(C.date_time)
											";			  
										  */
	$creditDetaild	= $db->fetchQuery("SELECT *,DATE(date_time) as date,user_id as id,
										sum(CASE WHEN is_credit = 0 THEN paid_amount END) as totalPaid,
										sum(CASE WHEN is_credit = 1 THEN paid_amount END) as totalCredit
										FROM credit_details WHERE DATE(date_time) between '$fromdate' and '$todate' and user_id='$userid' GROUP BY DATE(date_time)");								  
	  
										  
	echo '{"creditDetails":'.json_encode($creditDetaild).'}';
	
}elseif($action == "expandDateBase"){

	



}elseif($action == "insert"){




	$vars['name']	 = $_POST['name'];
	$vars['phone']	 = $_POST['phone'];
	$vars['email']	 = $_POST['email'];
	$vars['address'] = $_POST['address'];
	$vars['added_by_admin'] =1;
	
	
	$where['phone']    		= $_POST['phone'];
	$count = $db->CountRows('user_details',$where);
		
		if($count==0)
		{
			$db->Insert($vars,'user_details');
			$vars['id'] = $db->LastInsertID();
		}
	
	echo json_encode($vars);
		




}elseif($action == "update"){




	$data['name']	 = $_POST['name'];
	$data['phone']	 = $_POST['phone'];
	$data['email']	 = $_POST['email'];
	$data['address'] = $_POST['address'];
	$where['id']	 = $_POST['userid'];
	$db->Update('user_details',$data,$where);
	
	
	
	
}elseif($action == "listCustomers"){



	$where['added_by_admin'] = 1;
	$where['is_blocked'] = 0;
	$users = $db->fetchAll('user_details',$where);
	echo '{"users":'.json_encode($users).'}';



}elseif($action == "delete"){



	$where['id'] = $_GET['userid'];
	$data['is_blocked'] = 1;
	$users = $db->Update('user_details',$data,$where);
	//echo '{"users":'.json_encode($users).'}';
	
	
	
}elseif($action == "userdropdown"){


	
//	$q = $_GET['q'];
	$users = $db->fetchQuery("SELECT name,name as label ,phone,id FROM user_details");
	
	echo json_encode($users);




}elseif($action == "getUserBalance"){
	
	
	
	
	$userid	= $_GET['userid'];
	
	$creditDetaild = $db->ExecuteSQL("SELECT sum(credit_amount) as totalCredit, totalPaid, B.name,B.phone,B.email,id,A.bill_number,DATE(A.date_time) as date   FROM credit_given A 
		LEFT JOIN user_details B ON A.user_id = B.id
		LEFT JOIN (SELECT sum(paid_amount) as totalPaid, user_id, date_time FROM credit_payment GROUP BY user_id) E ON A.user_id=E.user_id
		WHERE A.user_id = '$userid' ");
		
	echo json_encode($creditDetaild);




}elseif($action == 'payBalance'){




	$data['user_id']	= $_POST['user_id'];
	$data['paid_amount']= $_POST['paidAmount'];
	//$data['collected_by'] = $_POST['supplier'];
	$data['date_time'] = date("Y-m-d H:i:s");
	
	$db->Insert($data,'credit_payment');
	
	
}elseif($action == "expandBillBase"){


	$date 	= date('Y-m-d',strtotime($_GET['date']));
	$userid		= $_GET['userid'];
	

	
	
	$creditDetaild	= $db->fetchQuery("SELECT *,DATE(date_time) as date,user_id as id,bill_number, sum(credit_amount) as bill_amount
										FROM credit_given WHERE DATE(date_time) = '$date' and user_id='$userid' GROUP BY bill_number");								  
	  
										  
	echo '{"creditDetails":'.json_encode($creditDetaild).'}';


}


?>