<?php
	include "db_connection/db_connection.php";
	ini_set('max_execution_time', 5000);	
	
	if($_SESSION['admin_login_id'] == '')
	{
		echo "<script>window.location='index.php';</script>";
	}
	$flag = 0;
		
	if(isset($_GET['fdate']) && $_GET['fdate']!='' ){ $fdate = $_GET['fdate'];	}else{ $fdate = ''; }
	if(isset($_GET['todate']) && $_GET['todate']!='' ){ $todate = $_GET['todate'];	}else{ $todate = ''; }
	if(isset($_GET['yea_r']) && $_GET['yea_r']!='' ){ $yea_r = 'Year';	}else{ $yea_r = ''; }
	if(isset($_GET['mont_h']) && $_GET['mont_h']!='' ){ $mont_h = 'Month';	}else{ $mont_h = ''; }
	if(isset($_GET['da_y']) && $_GET['da_y']!='' ){ $da_y = 'Day';	}else{ $da_y = ''; }
	$design_date = "acc_rep_temp.entry_date"; 	
	$dat_str = ''; //THIS IS FOR QUERY STRING	
	if($fdate!='' && $todate!='')
	{
		$dat_str = "STR_TO_DATE(".$design_date.",'%d-%m-%Y') BETWEEN STR_TO_DATE('$fdate','%d/%m/%Y') AND STR_TO_DATE('$todate','%d/%m/%Y')"; 
		$flgg=1;	
		$lbl = $fdate." - ".$todate;		
	}
	else if($fdate=='' && $todate!='')
	{
		if($yea_r!='' && $mont_h!='' && $da_y!='')
		{ 
			$dat_str = "STR_TO_DATE(".$design_date.",'%d-%m-%Y') = STR_TO_DATE('$todate','%d/%m/%Y')"; 
			$flgg = 1;
			$lbl = $todate." - Day/Month/Year";				
		}		
		else if($yea_r!='' && $mont_h!='' && $da_y=='')
		{
		 	$vl = explode("/",$todate);
			$todat_vl = $vl[2]."-".$vl[1]."-".$vl[0];
		 	$dat_str = "DATE_FORMAT(STR_TO_DATE(".$design_date.",'%d-%m-%Y'),'%m-%Y') = DATE_FORMAT(STR_TO_DATE('$todate','%d/%m/%Y'),'%m-%Y')";  
			$flgg = 1;
			$lbl = $todate." - Month/Year";	
		}		
		else if($yea_r!='' && $mont_h=='' && $da_y=='')
		{ 
			$vl = explode("/",$todate);
			$todat_vl = ($vl[2]-1)."-12-31";
			$dat_str = "YEAR(STR_TO_DATE(".$design_date.",'%d-%m-%Y')) = YEAR(STR_TO_DATE('$todate','%d/%m/%Y'))"; 				
			$flgg = 1;
			$lbl = $todate." - Year";	
		}		
		else if($yea_r=='' && $mont_h=='' && $da_y=='')
		{ 
			$dat_str = "STR_TO_DATE(".$design_date.",'%d-%m-%Y') <= STR_TO_DATE('$todate','%d/%m/%Y')"; 				
			$flgg = 0;
			$lbl = $todate;
		}		
		else if($yea_r=='' && $mont_h!='' && $da_y=='')
		{ 
			$vl = explode("/",$todate);
			$todat_vl = $vl[2]."-".$vl[1]."-".$vl[0];
			$dat_str = "DATE_FORMAT(STR_TO_DATE(".$design_date.",'%d-%m-%Y'),'%m-%Y') = DATE_FORMAT(STR_TO_DATE('$todate','%d/%m/%Y'),'%m-%Y')";  
		 	$flgg = 1;
		 	$lbl = $todate." - Month";	
		}		
		else if($yea_r=='' && $mont_h=='' && $da_y!='')
		{ 
			$dat_str = "STR_TO_DATE(".$design_date.",'%d-%m-%Y') = STR_TO_DATE('$todate','%d/%m/%Y')"; 
			$flgg = 1;
			$lbl = $todate." - Day";
		}
		
		else if($yea_r!='' && $mont_h=='' && $da_y!='')
		{ 
			$dat_str = "STR_TO_DATE(".$design_date.",'%d-%m-%Y') = STR_TO_DATE('$todate','%d/%m/%Y')"; 
			$flgg = 1;
			$lbl = $todate." - Day/Year";	
		}
		
		else if($yea_r=='' && $mont_h!='' && $da_y!='')
		{ 
			$dat_str = "STR_TO_DATE(".$design_date.",'%d-%m-%Y') = STR_TO_DATE('$todate','%d/%m/%Y')"; 				
			$flgg = 1;
			$lbl = $todate." - Day/Month";	
		}
	}
	if($fdate!="" && $todate!="")
	{
		$part_temp = " AND STR_TO_DATE(acc_rep_temp.entry_date,'%d-%m-%Y') BETWEEN STR_TO_DATE('$fdate','%d/%m/%Y') AND STR_TO_DATE('$todate','%d/%m/%Y') ORDER BY STR_TO_DATE(bill_date,'%d-%m-%Y') ASC, bill_no ASC";
	}
	else if($fdate!="" && $todate=="")
	{
		$part_temp = " AND STR_TO_DATE(acc_rep_temp.entry_date,'%d-%m-%Y') BETWEEN STR_TO_DATE('$fdate','%d/%m/%Y') AND STR_TO_DATE('$cur_date','%Y-%m-%d') ORDER BY STR_TO_DATE(bill_date,'%d-%m-%Y') ASC, bill_no ASC";
	}
	else if($fdate=="" && $todate!="")
	{
		$part_temp = " AND ".$dat_str." ORDER BY STR_TO_DATE(bill_date,'%d-%m-%Y') ASC, bill_no ASC";
	}
	else
	{
		$part_temp = " ORDER BY STR_TO_DATE(bill_date,'%d-%m-%Y') ASC, bill_no ASC";
	}	
	$loop = 1;
	$data = '';
	$SLCT = "SELECT cmp_name,tin_no,acc_rep_temp.party_det_id,data_type,billing_det.billing_det_id,bill_amt,bill_tot_tax_amt,courier_amt,round_off,bill_no,bill_date,bill_tax FROM party_det,ac_party_det,acc_rep_temp,billing_det WHERE billing_det.billing_det_id = acc_rep_temp.billing_det_id AND party_det.party_det_id = ac_party_det.party_det_id AND acc_rep_temp.data_type='billing' AND acc_rep_temp.party_det_id = ac_party_det.party_det_id ".$part_temp;
	$QURY = mysqli_query($connect,$SLCT);		
	if(mysqli_num_rows($QURY)>0)
	{
		while($FTCH = mysqli_fetch_object($QURY))
		{		
			$pa_id = $FTCH->party_det_id;
				
			if($FTCH->round_off<0)
			{
				$bl_amt = $FTCH->bill_amt - $FTCH->bill_tot_tax_amt - $FTCH->courier_amt - $FTCH->round_off;
			}
			else
			{
				$bl_amt = $FTCH->bill_amt - $FTCH->bill_tot_tax_amt - $FTCH->courier_amt;				
			}
			
			$data 	   .= $loop."\t".$FTCH->cmp_name."\t".$FTCH->tin_no."\t"." "."\t".$FTCH->bill_no."\t".str_replace("-","/",$FTCH->bill_date)."\t".$bl_amt."\t".$FTCH->bill_tax."\t".$FTCH->bill_tot_tax_amt."\t"."F"."\n";  
			
			$loop++;
		}
	}
	
	$heading	    = "serial_no \t Name_of_buyer \t Buyer_TIN \t commodity_code \t Invoice_No \t Invoice_Date \t Sales_Value \t Tax_rate \t VAT_CST_paid \t Category \n";
	$result     	= $heading.$data;  
	
	$saving_file_name 	= "Sales Tax on ".$lbl;
	header("Content-type: application/octet-stream");
	header("Content-Disposition: attachment; filename=\"$saving_file_name.xls\"");
	header("Content-Transfer-Encoding: binary");
	if(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE'))
	{
		header('Cache-Control: public');
	}
	echo $result;
	exit;  
?>