<?php
	//AGEING SQL PART
	IF($selected_cutOffPayment == true){
		if($statementby == 'statement_year'){
			
			   "sqlAgeing 3: " . $sqlAgeing = " SELECT * FROM ( 
											(select  bills.bill_amount- sum( payments.amount_paid ) as total_outstanding , bills.*,  payments.created_at as payment_date, properties.unit_no, tenants.tenant_name 
											from payments 
											inner join bills on payments.bill_id = bills.id 
											inner join tenants on bills.tenant_id =  tenants.id 
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
											where bills.tenant_id =$tenant_id and payments.created_at <= '$cutOffDate' and  YEAR(bills.issue_date) = '$statement_year' and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  ) 
											UNION  
											(select  (bills.bill_amount- sum( payments.amount_paid )) +  sum( payments.amount_paid ) as total_outstanding , bills.*,payments.created_at as payment_date, properties.unit_no, tenants.tenant_name  from payments 
											inner join bills on payments.bill_id = bills.id 
											inner join tenants on bills.tenant_id =  tenants.id  
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
												where bills.tenant_id = $tenant_id and payments.created_at > '$cutOffDate' and YEAR(bills.issue_date) = '$statement_year' and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  )  
											UNION
												(select sum(bills.bill_amount) as total_outstanding, bills.*,bills.created_at as payment_date ,properties.unit_no, tenants.tenant_name 
													from bills 
													inner join tenants on bills.tenant_id =  tenants.id  
													inner join properties on tenants.property_id = properties.id
													inner join locations on properties.location_id = locations.id
													where YEAR(bills.issue_date) = '$statement_year'
													and bills.id not in (select payments.bill_id from payments inner join bills on payments.bill_id = bills.id WHERE bills.tenant_id = $tenant_id )
													and tenant_id = $tenant_id  
													and bills.bill_type_id = 1
													group by issue_date having total_outstanding <> 0 and bills.issue_date < CURRENT_DATE)
			  
			  ) results group by issue_date ORDER BY issue_date ASC";
		}
		
		else if($statementby == 'statement_monthyear'){
			
			  "sqlAgeing 4: " . $sqlAgeing = " SELECT * FROM ( 
											(select  bills.bill_amount- sum( payments.amount_paid ) as total_outstanding , bills.*,payments.created_at as payment_date, properties.unit_no, tenants.tenant_name 
											from payments 
											inner join bills on payments.bill_id = bills.id 
											inner join tenants on bills.tenant_id =  tenants.id  
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
											where bills.tenant_id =$tenant_id and payments.created_at < '$cutOffDate' and  bills.issue_date = '$firstDay' and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  ) 
											UNION  
											(select  (bills.bill_amount- sum( payments.amount_paid )) +  sum( payments.amount_paid ) as total_outstanding , bills.*,payments.created_at as payment_date, properties.unit_no, tenants.tenant_name 
											from payments 
											inner join bills on payments.bill_id = bills.id 
											inner join tenants on bills.tenant_id =  tenants.id  
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
												where bills.tenant_id = $tenant_id and payments.created_at >= '$cutOffDate' and bills.issue_date = '$firstDay' and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  )  
											UNION
												(select sum(bills.bill_amount) as total_outstanding, bills.* , bills.created_at as payment_date, properties.unit_no, tenants.tenant_name 
													from bills 
													inner join tenants on bills.tenant_id =  tenants.id  
													inner join properties on tenants.property_id = properties.id
													inner join locations on properties.location_id = locations.id
													where bills.issue_date = '$firstDay'
													and bills.id not in (select payments.bill_id from payments inner join bills on payments.bill_id = bills.id WHERE bills.tenant_id = $tenant_id )
													and tenant_id = $tenant_id  
													and bills.bill_type_id = 1
													group by issue_date having total_outstanding <> 0 and bills.issue_date < CURRENT_DATE)
			  
			  ) results group by issue_date ORDER BY issue_date ASC";
		}
		
		else if($statementby == 'statement_ChooseDate'){
			
			    "sqlAgeing 5: " . $sqlAgeing = " SELECT * FROM ( 
											(select  bills.bill_amount- sum( payments.amount_paid ) as total_outstanding , 
											bills.*,
											payments.created_at as payment_date, 
											properties.unit_no, 
											tenants.tenant_name 
											from payments 
											inner join bills on payments.bill_id = bills.id 
											inner join tenants on bills.tenant_id =  tenants.id 
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
											where bills.tenant_id =$tenant_id and payments.created_at < '$date_cutOFF' and bills.issue_date <= '$cutOffDate' and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  ) 
											UNION  
											(select  (bills.bill_amount- sum( payments.amount_paid )) +  sum( payments.amount_paid ) as total_outstanding ,
											bills.*,
											payments.created_at as payment_date, 
											properties.unit_no, 
											tenants.tenant_name 
											from payments 
											inner join bills on payments.bill_id = bills.id 
											inner join tenants on bills.tenant_id =  tenants.id 
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
												where bills.tenant_id = $tenant_id and payments.created_at >= '$date_cutOFF' and bills.issue_date <= '$cutOffDate' and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  )  
											UNION
												(select sum(bills.bill_amount) as total_outstanding, 
												bills.* , 
												bills.created_at as payment_date,																	
												properties.unit_no, 
												tenants.tenant_name 
													from bills 
													inner join tenants on bills.tenant_id =  tenants.id  
													inner join properties on tenants.property_id = properties.id
													inner join locations on properties.location_id = locations.id
													where bills.issue_date  <= '$cutOffDate'
													and bills.id not in (select payments.bill_id from payments inner join bills on payments.bill_id = bills.id WHERE bills.tenant_id = $tenant_id )
													and  tenants.id  = $tenant_id  
													and bills.bill_type_id = 1
													group by issue_date having total_outstanding <> 0 and bills.issue_date < CURRENT_DATE)
			  
			  ) results group by issue_date ORDER BY issue_date ASC";
		}
		else if($statementby == 'statement_date'){
			 
			    $sqlAgeing = " SELECT * FROM ( 
											(select  bills.bill_amount- sum( payments.amount_paid ) as total_outstanding , bills.*,payments.created_at as payment_date, properties.unit_no, tenants.tenant_name 
											from payments 
											inner join bills on payments.bill_id = bills.id 
											inner join tenants on bills.tenant_id =  tenants.id  
											
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
											where bills.tenant_id =$tenant_id  and (payments.created_at < '$date_cutOFF') and  (bills.issue_date >= '$dateFrom' and bills.issue_date < '$date_cutOFF')  and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  ) 
											UNION  
											(select  (bills.bill_amount- sum( payments.amount_paid )) +  sum( payments.amount_paid ) as total_outstanding , bills.*,payments.created_at as payment_date, properties.unit_no, tenants.tenant_name 
											from payments 
											inner join bills on payments.bill_id = bills.id 
											inner join tenants on bills.tenant_id =  tenants.id  
											
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
												where bills.tenant_id = $tenant_id and   (payments.created_at < '$dateFrom' and payments.created_at >= '$date_cutOFF') and  (bills.issue_date >= '$dateFrom' and bills.issue_date < '$date_cutOFF')  and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  )  
											UNION
												(select sum(bills.bill_amount) as total_outstanding, bills.* , bills.created_at as payment_date, properties.unit_no, tenants.tenant_name 
													from bills 
													inner join tenants on bills.tenant_id =  tenants.id  
													
													inner join properties on tenants.property_id = properties.id
													inner join locations on properties.location_id = locations.id
													where  (bills.issue_date >= '$dateFrom' and bills.issue_date < '$date_cutOFF') 
													and bills.id not in (select payments.bill_id from payments inner join bills on payments.bill_id = bills.id WHERE bills.tenant_id = $tenant_id )
													and tenant_id = $tenant_id  
													and bills.bill_type_id = 1
													group by issue_date having total_outstanding <> 0 and bills.issue_date < CURRENT_DATE)
			  
			  ) results group by issue_date ORDER BY issue_date ASC";
		}
	}
	else{	
		
		 "sqlAgeing 7: " . $sqlAgeing = "select sum(amount_due) as total_outstanding, bills.* from bills 
							where issue_date <= '$cutOffDate' and (lower(payment_status) = 'unpaid' or lower(payment_status) = 'partial') 
							and tenant_id = $tenant_id and bills.bill_type_id = 1
							group by issue_date ";
		//if select month year	
		if($statementby == 'statement_year'){
			  "sqlAgeing 8: " . $sqlAgeing = " SELECT * FROM ( 
												(select  bills.bill_amount- sum( payments.amount_paid ) as total_outstanding , 
												bills.*,
												payments.created_at as payment_date, 
												properties.unit_no, 
												tenants.tenant_name 
												from payments inner join bills on payments.bill_id = bills.id 
												inner join tenants on bills.tenant_id =  tenants.id
												inner join properties on tenants.property_id = properties.id
												inner join locations on properties.location_id = locations.id
												where bills.tenant_id =$tenant_id  and  YEAR(bills.issue_date) = '$statement_year' and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  ) 
											UNION 
												(select sum(bills.bill_amount) as total_outstanding, 
												bills.* , 
												bills.created_at as payment_date, 
												properties.unit_no, 
												tenants.tenant_name 
												from bills 
												inner join tenants on bills.tenant_id =  tenants.id 
												inner join properties on tenants.property_id = properties.id
												inner join locations on properties.location_id = locations.id
												where YEAR(bills.issue_date) = '$statement_year'
												and bills.id not in (select payments.bill_id from payments inner join bills on payments.bill_id = bills.id WHERE bills.tenant_id = $tenant_id )
												and tenant_id = $tenant_id  
												and bills.bill_type_id = 1
												group by issue_date having total_outstanding <> 0 and bills.issue_date < CURRENT_DATE)
			  
											) results group by issue_date ORDER BY issue_date ASC";
		}
		else if($statementby == 'statement_monthyear'){
			   "sqlAgeing 9: " . $sqlAgeing = " SELECT * FROM ( 
											(select  bills.bill_amount- sum( payments.amount_paid ) as total_outstanding , bills.*,payments.created_at as payment_date, properties.unit_no, tenants.tenant_name 
											from payments 
											inner join bills on payments.bill_id = bills.id 
											inner join tenants on bills.tenant_id =  tenants.id  
											
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
											where bills.tenant_id =$tenant_id and  bills.issue_date = '$firstDay' and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  ) 
											UNION
												(select sum(bills.bill_amount) as total_outstanding, bills.* , bills.created_at as  payment_date,  properties.unit_no, tenants.tenant_name 
													from bills 
													inner join tenants on bills.tenant_id =  tenants.id  
													
													inner join properties on tenants.property_id = properties.id
													inner join locations on properties.location_id = locations.id
													where bills.issue_date = '$firstDay'
													and bills.id not in (select payments.bill_id from payments inner join bills on payments.bill_id = bills.id WHERE bills.tenant_id = $tenant_id )
													and tenant_id = $tenant_id  
													and bills.bill_type_id = 1
													group by issue_date having total_outstanding <> 0 and bills.issue_date < CURRENT_DATE)
			  
			  ) results group by issue_date ORDER BY issue_date ASC";
		}
		else if($statementby == 'statement_ChooseDate'){
		 	 "sqlAgeing 10: " . $sqlAgeing = " SELECT * FROM ( 
											(select  bills.bill_amount- sum( payments.amount_paid ) as total_outstanding , bills.*,payments.created_at as payment_date, properties.unit_no, tenants.tenant_name 
											from payments 
											inner join bills on payments.bill_id = bills.id 
											inner join tenants on bills.tenant_id =  tenants.id
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
											where bills.tenant_id =$tenant_id  and bills.issue_date <= '$cutOffDate' and payments.created_at <= '$cutOffDate' and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  ) 
											UNION  
												(select sum(bills.bill_amount) as total_outstanding, bills.* , bills.created_at as  payment_date,  properties.unit_no, tenants.tenant_name 
													from bills 
													inner join tenants on bills.tenant_id =  tenants.id  
													
													inner join properties on tenants.property_id = properties.id
													inner join locations on properties.location_id = locations.id
													where bills.issue_date  <= '$cutOffDate'
													and bills.id not in (select payments.bill_id from payments inner join bills on payments.bill_id = bills.id WHERE bills.tenant_id = $tenant_id )
													and tenant_id = $tenant_id  
													and bills.bill_type_id = 1
													group by issue_date having total_outstanding <> 0 and bills.issue_date < CURRENT_DATE)
			  ) results group by issue_date ORDER BY issue_date ASC";
		}
		else if($statementby == 'statement_date'){
			   "sqlAgeing 11: " . $sqlAgeing = " SELECT * FROM ( 
											(select  bills.bill_amount- sum( payments.amount_paid ) as total_outstanding , bills.*,payments.created_at as  payment_date, properties.unit_no, tenants.tenant_name  from payments 
											inner join bills on payments.bill_id = bills.id 
											inner join tenants on bills.tenant_id =  tenants.id  
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
											where bills.tenant_id =$tenant_id   and  (bills.issue_date >= '$dateFrom' and bills.issue_date < '$date_cutOFF')  and payments.created_at < '$date_cutOFF' and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  ) 
											UNION  
											(select  (bills.bill_amount- sum( payments.amount_paid )) +  sum( payments.amount_paid ) as total_outstanding , bills.*,payments.created_at as payment_date, properties.unit_no, tenants.tenant_name  
											from payments inner join bills on payments.bill_id = bills.id 
											inner join tenants on bills.tenant_id =  tenants.id  
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
												where bills.tenant_id = $tenant_id and  (bills.issue_date >= '$dateFrom' and bills.issue_date < '$date_cutOFF') and payments.created_at > '$date_cutOFF' and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  )  
											UNION
												(select sum(bills.bill_amount) as total_outstanding, bills.* ,bills.created_at as payment_date,  properties.unit_no, tenants.tenant_name 
													from bills 
													inner join tenants on bills.tenant_id =  tenants.id  
													inner join properties on tenants.property_id = properties.id
													inner join locations on properties.location_id = locations.id
													where  (bills.issue_date >= '$dateFrom' and bills.issue_date < '$date_cutOFF') 
													and bills.id not in (select payments.bill_id from payments inner join bills on payments.bill_id = bills.id WHERE bills.tenant_id = $tenant_id )
													and tenant_id = $tenant_id  
													and bills.bill_type_id = 1
													group by issue_date having total_outstanding <> 0 and bills.issue_date < CURRENT_DATE)
			  ) results group by issue_date ORDER BY issue_date ASC";
		}
		else if($statementby == ''){
			$sqlAgeing = " SELECT * FROM ( 
							(select  bills.bill_amount- sum( payments.amount_paid ) as total_outstanding , bills.*,payments.created_at as payment_date, properties.unit_no, tenants.tenant_name 
							from payments 
							inner join bills on payments.bill_id = bills.id 
							inner join tenants on bills.tenant_id =  tenants.id  
							
							inner join properties on tenants.property_id = properties.id
							inner join locations on properties.location_id = locations.id
							where bills.tenant_id =$tenant_id  and bills.issue_date <= '$cutOffDate' and bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0  ) 
							UNION  
								(select sum(bills.bill_amount) as total_outstanding, bills.* , bills.created_at as  payment_date,  properties.unit_no, tenants.tenant_name 
									from bills 
									inner join tenants on bills.tenant_id =  tenants.id  
									
									inner join properties on tenants.property_id = properties.id
									inner join locations on properties.location_id = locations.id
									where bills.issue_date  < '$cutOffDate'
									and bills.id not in (select payments.bill_id from payments inner join bills on payments.bill_id = bills.id WHERE bills.tenant_id = $tenant_id )
									and tenant_id = $tenant_id  
									and bills.bill_type_id = 1
									group by issue_date having total_outstanding <> 0 and bills.issue_date < CURRENT_DATE)
							) results group by issue_date ORDER BY issue_date ASC";
		}
	}


?>