<?php 
error_reporting(0);
include_once "session.php";

extract($_REQUEST);
$act=$action;


switch($act)
{	

	case 'rpt_pur_payment_pending':	
	
		$rslt_html='';
		
		$cond_company='';
		if(isset($srch_sup_company) && !empty($srch_sup_company)){
			$cond_company= " and t1.company_id ='".$srch_sup_company."' ";
		}
		
		$cond_supplier='';
		if(isset($srch_sup_supplier) && !empty($srch_sup_supplier)){
			$cond_supplier= " and t1.sup_id ='".$srch_sup_supplier."' ";
		}

		$sql_query ="  select t1.company_id, t3.company_name, t1.sup_id, t2.sup_name, t2.sup_payment_type, sum(t1.tot_pur_amount) as tot_pur_amt, sum(t1.final_pur_amt) as final_pur_amt from t_purchase_entry_main t1 inner join m_supplier t2 on t1.sup_id = t2.sup_id  inner join m_company t3 on t1.company_id = t3.company_id  where 1=1 ".$cond_company."  ".$cond_supplier." group by  t1.sup_id, t1.company_id ";
		
		$rslt_data = $db->get_rsltset($sql_query);
		
		if(!empty($rslt_data)){	
			$rslt_html .='<table class="table table-bordered table-hover" id="rslt_tbl">
						<thead>
							<tr>		
								<th>Company Name</th>
								<th>Supplier Name</th>
								<th>Total Purchase Amt</th>
								<th>Already Paid Amt</th>
								<th>Due Amt</th>
							</tr>
						</thead> 	<tbody>';
			
			foreach($rslt_data as $r){
				
				$tot_purchase_amt=0;
				if($r['sup_payment_type'] == "1"){
					$tot_purchase_amt = $r['final_pur_amt'];
				}
				else{
					$tot_purchase_amt = $r['tot_pur_amt'];
				}
				
				//already paid amount
				$tot_already_paid_amt=0; $tot_due_amt=0;
				$get_tot_paid_amt =$db->get_a_line(" SELECT sum(`amount_recv`) as tot_paid_amt FROM `t_suppliers_payments` WHERE 1=1 and `sup_id`='".$r['sup_id']."' and `company_id`='".$r['company_id']."' and `IsActive`=1 ");
				
				if(isset($get_tot_paid_amt['tot_paid_amt']) && !empty($get_tot_paid_amt['tot_paid_amt']) && $get_tot_paid_amt['tot_paid_amt'] !=null){
					$tot_already_paid_amt = $get_tot_paid_amt['tot_paid_amt'];		
				}
				
				$tot_due_amt = (float)$tot_purchase_amt -  (float)$tot_already_paid_amt;
				
				
				$rslt_html .= '<tr>
							<td>'.$r['company_name'].'</td>
							<td>'.$r['sup_name'].'</td>
							<td>'.$tot_purchase_amt.'</td>	
							<td>'.$tot_already_paid_amt.'</td>	
							<td>'.$tot_due_amt.'</td>	
							</tr> ';	
			}
			
			//footer column
			$rslt_html .= '</tbody> 
			<tfoot> 
			<tr>
			<td><b>Total</b></td> 	
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			</tr> 
			</tfoot> </table>';
		}
		else{
			$rslt_html=' No Data';	
		}
				
		echo json_encode(array("rslt"=>"1", "reports_rslt"=>$rslt_html)); //success	
		
	break;	
	
	case 'rpt_sales_report':
	
$rslt_html='';
		
		$cond_company='';
		if(isset($srch_sup_company) && !empty($srch_sup_company)){
			$cond_company= " and t1.company_id ='".$srch_sup_company."' ";
		}

		$str_ed = "select * from m_financial_year where IsActive = '1'";
		$res_ed = $db->get_a_line($str_ed);

		
		
		$sdate='';
		if(isset($startDate) && !empty($startDate)){
			$startDate = "01-".$startDate;
			$startformatDate = date('Y-m-d',strtotime($startDate));
			$endDate  = "01-".$endDate;		
			$lastday = date('Y-m-t',strtotime($endDate));

			$cond_sdate= " and t1.s_bill_date >= '".$startformatDate."' and t1.s_bill_date <= '".$lastday."' ";
		}else{
			$sdate=date_create($res_ed['f_start_date']);
			$edate=date_create($res_ed['f_end_date']);
			$startDate = date_format($sdate,"Y-m-d");
			$endDate = date_format($edate,"Y-m-d");			
			$cond_sdate= " and t1.s_bill_date >= '".$startDate."' and t1.s_bill_date <= '".$endDate."' ";
		}

		
			$sql_query="select t1.*,DATE_FORMAT(t1.s_bill_date,'%d-%b-%Y') AS disp_bill_date, t2.tot_paid_amt, t2.due_amt, t3.company_name, t4.cus_name,t4.cus_gstno from t_sales_entry_main t1 
left join t_sales_payment t2 on t1.s_bill_no = t2.s_bill_no
inner join m_company t3 on t1.company_id=t3.company_id
inner join m_customer t4 on t1.cus_id = t4.cus_id
where 1=1 and t1.IsActive <>2 ".$cond_company." ".$cond_sdate." order by t1.s_bill_date desc, actu_bill_no desc ";

		//echo $sql_query; exit;
	
	
		
		$rslt_data = $db->get_rsltset($sql_query);
		
		if(!empty($rslt_data)){	
			$rslt_html .='<table class="table table-bordered table-hover" id="rslt_tbl">
						<thead>
							<tr>	
								<th>Sno</th>		
								<th>Company Name</th>						
								<th>Customer Name</th>								
								<th>GSTNo/UIN</th>
								<th>Inv No</th>
								<th>Inv Date</th>
								<th>Sarees</th>
								<th>Amount</th>
								<th>Dis (%)</th>
								<th>After Dis</th>
								<th>GST(2.5 %)</th>
								<th>SGST (2.5 %)</th>
								<th>IGST (5 %)</th>
								<th>Taxable </br> Amount</th>								
								<th>Final </br> Amount</th> 
							</tr>
						</thead> 	<tbody>';
			$sno = 1;
			$totalsare = $after_discount_amt = $total_sale_amount=0;
			foreach($rslt_data as $r){
				$totalsare = $totalsare + $totalsaree['totalsaree'];
				
				 
					$total_sale_amount = $r['total_sale_amount'];
				 
					$after_discount_amt = $r['after_discount_amt'];
				 
				
				//already paid amount
				$tot_already_paid_amt=0; $tot_due_amt=0;
				$totalsaree =$db->get_a_line(" SELECT count(`s_bill_det_id`) as totalsaree FROM `t_sales_entry_details` WHERE 1=1 and `s_bill_no`='".$r['s_bill_no']."'  and `IsActive`=1 ");
				
				 
				
				//$tot_due_amt = (float)$tot_purchase_amt -  (float)$tot_already_paid_amt;
				
				
				$rslt_html .= '<tr>
							<td>'.$sno++.'</td>
							<td>'.$r['company_name'].'</td>
							<td>'.$r['cus_name'].'</td>
							<td style="color:green; font-size:12px">'.$r['cus_gstno'].'</td>
							<td>'.$r['actu_bill_no'].'</td>
							<td>'.date('d/m/Y',strtotime($r['s_bill_date'])).'</td>							
							<td>'.$totalsaree['totalsaree'].'</td>
							<td>'.$total_sale_amount.'</td>
							<td>'.$r['discount_per'].'</td>	
							<td>'.$r['after_discount_amt'].'</td>	
							<td>'.$r['cgst_total'].'</td>	
							<td>'.$r['sgst_total'].'</td>	
							<td>'.$r['igst_total'].'</td>	
							<td>'.$r['tot_tax_amt'].'</td>	
							<td>'.$r['final_sale_amt'].'</td>	
							</tr> ';	
			}
			
			//footer column
			$rslt_html .= '</tbody> 
			<tfoot> 
			<tr>
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td><b>Total</b></td> 			
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			</tr> 
			</tfoot> </table>';
		}
		else{
			$rslt_html=' <tbody class="employee-grid-error"><tr><th colspan="10">No data found in the server</th></tr></tbody>';	
		}
				
		echo json_encode(array("rslt"=>"1", "reports_rslt"=>$rslt_html)); //success		
	break;
	
		case 'rpt_purchase_report':
	
$rslt_html='';
		
		$cond_company='';
		if(isset($srch_sup_company) && !empty($srch_sup_company)){
			$cond_company= " and a.company_id ='".$srch_sup_company."' ";
		}
		$str_ed = "select * from m_financial_year where IsActive = '1'";
		$res_ed = $db->get_a_line($str_ed);

		$sdate='';
		if(isset($startDate) && !empty($startDate)){
			$startDate = "01-".$startDate;
			$startformatDate = date('Y-m-d',strtotime($startDate));
			$endDate  = "01-".$endDate;		
			$lastday = date('Y-m-t',strtotime($endDate));
		   
		   	$cond_sdate= " and a.p_invoice_date >= '".$startformatDate."' and a.p_invoice_date <= '".$lastday."' ";
		}else{
		   $sdate=date_create($res_ed['f_start_date']);
			$edate=date_create($res_ed['f_end_date']);
			$startDate = date_format($sdate,"Y-m-d");
			$endDate = date_format($edate,"Y-m-d");		
			$cond_sdate= " and a.p_invoice_date >= '".$startDate."' and a.p_invoice_date <= '".$endDate."' ";
		}
 
		
		   /*   $sql_query="select t1.*,DATE_FORMAT(t1.p_invoice_date,'%d-%b-%Y') AS disp_bill_date,t4.sup_name,t4.sup_gstno from 
			t_purchase_entry_main t1   
inner join m_supplier t4 on t4.sup_id = t1.sup_id 
where 1=1 and t1.IsActive <>2 order by t1.p_invoice_no ".$cond_company." ".$cond_sdate." ";

*/

	$sql_query="	SELECT a.*,DATE_FORMAT(a.p_invoice_date,'%d-%b-%Y') AS disp_bill_date,t4.sup_name,t4.sup_gstno,concat(date_format(yr.f_start_date,'%Y'),' - ',date_format(yr.f_end_date,'%Y')) as financalyear, t3.company_name  FROM `t_purchase_entry_main` a
inner join m_supplier t4 on t4.sup_id = a.sup_id 
inner join m_company t3 on a.company_id=t3.company_id
inner join m_financial_year yr on yr.financial_id = a.financial_id
WHERE  a.IsActive <>2  ".$cond_company." ".$cond_sdate." order by a.p_invoice_date desc, actu_inv_no desc ";
	
//	echo $sql_query;
		
		$rslt_data = $db->get_rsltset($sql_query);
		
		if(!empty($rslt_data)){	
			$rslt_html .='<table class="table table-bordered table-hover" id="rslt_tbl">
						<thead>
							<tr>	
								<th>Sno</th>		
								<th>Company Name</th>					
								<th>Supplier Name</th>						
								<th>Financial Year</th>
								
								<th>Inv No</th>
								<th>Inv Date</th>
								<th>Sarees No</th>
								<th>Amount</th>
								<th>Dis (%)</th>
								<th>After Dis</th>
								<th>GST(2.5 %)</th>
								<th>SGST (2.5 %)</th>
								<th>IGST (5 %)</th>
								<th>Taxable </br> Amount</th>								
								<th>Final </br> Amount</th> 
							</tr>
						</thead> 	<tbody>';
			$sno = 1;
			$totalsare = $after_discount_amt = $total_sale_amount=0;
			foreach($rslt_data as $r){
				$totalsare = $totalsare + $totalsaree['totalsaree'];
				
				 
					$total_sale_amount = $r['tot_pur_amount'];
				 
					$after_discount_amt = $r['after_discount_amt'];
				 
				
				//already paid amount
				$tot_already_paid_amt=0; $tot_due_amt=0;
				$totalsaree =$db->get_a_line(" SELECT count(`p_invoice_det_id`) as totalsaree FROM `t_purchase_entry_details` WHERE 1=1 and `p_invoice_no`='".$r['p_invoice_no']."'  and `IsActive`=1 ");
				
			 
				
				//$tot_due_amt = (float)$tot_purchase_amt -  (float)$tot_already_paid_amt;
				
				
				$rslt_html .= '<tr>
							<td>'.$sno++.'</td>
							<td>'.$r['company_name'].'</td>
							<td>'.$r['sup_name'].'</br><span>'.$r['sup_gstno'].'</span></td>
							<td>'.$r['financalyear'].'</td>
							
							<td>'.$r['actu_inv_no'].'</td>
							<td>'.date('d/m/Y',strtotime($r['p_invoice_date'])).'</td>							
							<td>'.$totalsaree['totalsaree'].'</td>
							<td>'.$total_sale_amount.'</td>
							<td>'.$r['discount_per'].'</td>	
							<td>'.$r['after_discount_amt'].'</td>	
							<td>'.$r['cgst_total'].'</td>	
							<td>'.$r['sgst_total'].'</td>	
							<td>'.$r['igst_total'].'</td>	
							<td>'.$r['tot_tax_amt'].'</td>	
							<td>'.$r['final_pur_amt'].'</td>	
							</tr> ';	
			}
			
			//footer column
			$rslt_html .= '</tbody> 
			<tfoot> 
			<tr>
			<td>&nbsp;</td> 
			
			<td>&nbsp;</td> 	
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td><b>Total</b></td> 			
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td>&nbsp;</td> 
			<td	&nbsp;</td> 
			</tr> 
			</tfoot> </table>';
		}
		else{
			$rslt_html=' <tbody class="employee-grid-error"><tr><th colspan="10">No data found in the server</th></tr></tbody>';	
		}
				
		echo json_encode(array("rslt"=>"1", "reports_rslt"=>$rslt_html)); //success		
	break;
}

?>