<? include("../../include/dbconnect.php"); 

mysql_query("truncate salesexcel ") or die(mysql_error());

$f_date = substr($from_date,6,4).'-'.substr($from_date,3,2).'-'.substr($from_date,0,2);
$t_date = substr($to_date,6,4).'-'.substr($to_date,3,2).'-'.substr($to_date,0,2);
	$sno=1;
$invresult= mysql_query("select distinct invno from salesreg  where invdate >='$f_date' and invdate <='$t_date'  and compcode='$compcode' and drawing_no = 'TOTAL' order by invno,invdate,record ")or die(mysql_error());
while($invrow = mysql_fetch_row($invresult))
{

	$presult = mysql_query("select 	invno, invdate, drawing_no, bcode, value, packing_percent, packing_amt, bed, bed_amt, cess, cess_amt, hcess, hcess_amt, vat_percent, vat_amount, freight, round_off, gtotal, taxtype from salesreg where invdate >='$f_date' and invdate <='$t_date'  and compcode='$compcode' and invno = '$invrow[0]' and drawing_no = 'TOTAL' order by invno,drawing_no,record ")or die(mysql_error());
	$prow = mysql_fetch_row($presult);

		$invno=$prow[0];
		$invdate= substr($prow[1],8,2).'/'.substr($prow[1],5,2).'/'.substr($prow[1],0,4); 
		$drawing_no=$prow[2]; 
		$bcode=$prow[3]; 
		$incomename_amount=$prow[4]; 
		$packing_percent=$prow[5]; 
		$packing_charges_amount=$prow[6]; 
		$bed=$prow[7]; 
		$eduty_amount=$prow[8]; 
		$cess=$prow[9]; 
		$edcess_amount=$prow[10]; 
		$hcess=$prow[11]; 
		$hcess_amount=$prow[12]; 
		$vat_percent=$prow[13]; 
		$taxname_amount=$prow[14]; 
		$freight=$prow[15]; 
		//$round_off_amount=$prow[16]; 
		$total=$prow[17]; 
		$taxtype=$prow[18];
		$fright_amount = 0;
		$export_amount =0;
		$labour_amount = 0;
		$temp1_amount = 0;
		$temp2_amount = 0;
		$bresult = mysql_query("select name from buy where bcode = '$bcode' ") or die(mysql_error());
		$brow = mysql_fetch_row($bresult);
		$customer_name = $brow[0];
		$final = $incomename_amount + $packing_charges_amount + $eduty_amount + $hcess_amount + $taxname_amount;
		$int_final = intval(round($final),0);
		$round_off_amount = $int_final - $final;
		
	//	if($taxtype == 'VAT')
	//	{
			if($vat_percent == '5.00')
			{
				$incomename = 'Sales VAT @ 5%';
				$taxname = 'Output VAT @5%';
			}
			elseif($vat_percent == '0.00')
			{
				$incomename = '';
				$taxname = '';
			}
			if($vat_percent == '14.50')
			{
				$incomename = 'Sales VAT @ 14.5%';
				$taxname = 'Output VAT @14.5%';
			}
			elseif($vat_percent == '0.00')
			{
				$incomename = '';
				$taxname = '';
			}					
		//}
		//if($taxtype == 'CFORM')
		//{
			if($vat_percent == '2.00')
			{
				$incomename = 'Sales CST 2%';
				$taxname = 'Tax - CST 2%';
			}		
		//}
		if($packing_percent == '1.5')
		{
			$packing_charges = 'Packing Charges - Collection 1.5%';	
		}
		if($packing_percent == '2.00')
		{
			$packing_charges = 'Packing Charges - Collection 2%';	
		}
		if($packing_percent == '3.00')
		{
			$packing_charges = 'Packing Charges - Collection 3%';	
		}
		if($packing_percent == '0.00')
		{
			$packing_charges = 'Packing Charges - Collection 1.5%';	;	
		}	
		if($bed == 12.00)
		{	
			$eduty = 'Basic Excise Duty 12%';	
		}
		elseif($bed == 10.00)
		{
		 	$eduty = 'Basic Excise Duty 10%';	
		}
		if($bed == 12.50)
		{
			$eduty = 'Basic Excise Duty 12.50%';			
		}
		$edcess ='Edu. Cess 2%';				
		$hcess = 'Hr.Edu.Cess 1%';	
		$round_off = 'Rounded Off';
		$fright = 'Fright Charges';
		$labour = 'Labour Charges';
		$export = 'Export Sales';
		$temp1 ='';
		$temp2='';
		$narration = 'Towards Sales inv '.$invno;
		
		$query = "insert into salesexcel(sno, invno, invdate, customer_name, incomename, packing_charges, eduty, edcess, hcess, taxname, round_off, frieght, labour, export, temp1, temp2, incomename_amount, packing_charges_amount, eduty_amount, edcess_amount, hcess_amount, taxname_amount, round_off_amount, fright_amount, labour_amount, export_amount, temp1_amount, temp2_amount,total, narration) values
				('$sno', '$invno', '$invdate', '$customer_name', '$incomename', '$packing_charges', '$eduty', '$edcess', '$hcess', '$taxname', '$round_off', '$frieght', '$labour', '$export', '$temp1', '$temp2', '$incomename_amount', '$packing_charges_amount', '$eduty_amount', '$edcess_amount', '$hcess_amount', '$taxname_amount', '$round_off_amount', '$fright_amount', '$labour_amount', '$export_amount', '$temp1_amount', '$temp2_amount','$total', '$narration')";		
		mysql_query($query) or die(mysql_error());
		$sno++;

}										

		$invno=$prow[0];
		$invdate= substr($prow[1],8,2).'/'.substr($prow[1],5,2).'/'.substr($prow[1],0,4); 
		$drawing_no=$prow[2]; 
		$bcode=$prow[3]; 
		$incomename_amount=0; 
		$packing_percent=0; 
		$packing_charges_amount=0; 
		$bed=0; 
		$eduty_amount=0; 
		$cess=0; 
		$edcess_amount=0; 
		$hcess=0; 
		$hcess_amount=0; 
		$vat_percent=0; 
		$taxname_amount=0; 
		$freight=0; 
		//$round_off_amount=$prow[16]; 
		$total=0; 
		$taxtype=0;
		$fright_amount = 0;
		$export_amount =0;
		$labour_amount = 0;
		$temp1_amount = 0;
		$temp2_amount = 0;
		$packing_charges = 'Packing Charges - Collection 1.5%';	
		$taxname = 'Output VAT @5%';

// LABOUR INVOICES

$invresult= mysql_query("select distinct invno from labourinv  where invdate >='$f_date' and invdate <='$t_date'  and compcode='$compcode' and drawing_no = 'TOTAL' order by invno,invdate,record ")or die(mysql_error());
while($invrow = mysql_fetch_row($invresult))
{

	$presult = mysql_query("select 	invno, invdate, drawing_no, bcode, value, packing_percent, packing_amt,  round_off, gtotal from labourinv where invdate >='$f_date' and invdate <='$t_date'  and compcode='$compcode' and invno = '$invrow[0]' and drawing_no = 'TOTAL' order by invno,drawing_no,record ")or die(mysql_error());
	$prow = mysql_fetch_row($presult);

		$invno=$prow[0];
		$invdate= substr($prow[1],8,2).'/'.substr($prow[1],5,2).'/'.substr($prow[1],0,4); 
		$drawing_no=$prow[2]; 
		$bcode=$prow[3]; 
		$incomename_amount=$prow[4]; 
		$packing_percent=$prow[5]; 
		$packing_charges_amount=$prow[6]; 
		$total = $prow[8];

		$narration = 'Towards Labour inv '.$invno;
	
		$fright_amount = 0;
		$export_amount =0;
		$labour_amount = 0;
		$temp1_amount = 0;
		$temp2_amount = 0;
		$bresult = mysql_query("select name from buy where bcode = '$bcode' ") or die(mysql_error());
		$brow = mysql_fetch_row($bresult);
		$customer_name = $brow[0];
		$final = $incomename_amount + $packing_charges_amount + $eduty_amount + $hcess_amount + $taxname_amount;
		$int_final = intval(round($final),0);
		$round_off_amount = $int_final - $final;
		

		$incomename = 'Labour Charges - Income';
		$taxname = '';


		if($packing_percent == '1.5')
		{
			$packing_charges = 'Packing Charges - Collection 1.5%';	
		}
		if($packing_percent == '2.00')
		{
			$packing_charges = 'Packing Charges - Collection 2%';	
		}
		if($packing_percent == '3.00')
		{
			$packing_charges = 'Packing Charges - Collection 3%';	
		}
		if($packing_percent == '0.00')
		{
			$packing_charges = '';	
		}		
		$eduty = 'Basic Excise Duty 12%';	
		$edcess ='Edu. Cess 2%';				
		$hcess = 'Hr.Edu.Cess 1%';	
		$round_off = 'Rounded Off';
		$fright = 'Fright Charges';
		$labour = 'Labour Charges - Income';
		$export = 'Export Sales';
		$temp1 ='';
		$temp2='';
		$narration = 'Towards Sales inv '.$invno;
		
		$query = "insert into salesexcel(sno, invno, invdate, customer_name, incomename, packing_charges, eduty, edcess, hcess, taxname, round_off, frieght, labour, export, temp1, temp2, incomename_amount, packing_charges_amount, eduty_amount, edcess_amount, hcess_amount, taxname_amount, round_off_amount, fright_amount, labour_amount, export_amount, temp1_amount, temp2_amount,total, narration) values
				('$sno', '$invno', '$invdate', '$customer_name', '$incomename', '$packing_charges', '$eduty', '$edcess', '$hcess', '$taxname', '$round_off', '$frieght', '$labour', '$export', '$temp1', '$temp2', '$incomename_amount', '$packing_charges_amount', '$eduty_amount', '$edcess_amount', '$hcess_amount', '$taxname_amount', '$round_off_amount', '$fright_amount', '$labour_amount', '$export_amount', '$temp1_amount', '$temp2_amount','$total', '$narration')";		
		mysql_query($query) or die(mysql_error());
		$sno++;

}										

?>
<h3><font face="Arial, Helvetica, sans-serif">Invoice Details and Labour Invoices Uploaded into Excel file.   Please click the below link to download the file.</font></h3>
<a href = "annexure1_excel.php"><font size="+1">Download Excel File</font></a>