<?
session_start();
include_once('opendb.php');
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
 print "<script> alert('Session timed out'); parent.document.location='inde.php'; </script>";
 return;
}
include_once('testtot.php');

			
    
	$fromdt=$_REQUEST['dfrom'];
	$todt=$_REQUEST['dto'];
	$filename ="salerpt_grws.xls";
	$tbl='sales_summ'.$brcode.$user;
	mysqli_query($db,"delete from $tbl");
	mysqli_query($db,"create table $tbl(gname varchar(50),total double(14,2),mba double(14,2),strbtnml double(14,2),strbtspl double(14,2),redct double(14,2),befrtx double(14,2),taxamt double(14,2),nettot double(14,2),dbttot double(14,2))");
	$qry=mysqli_query($db,"select gname,invdate,sum(total),sum(mba),sum(strbtnml),sum(strbtspl),sum(redct),sum(befrtx),sum(taxamt),sum(nettot),invno,pmode from prod_invoice where invdate between '$fromdt' and '$todt' and brcode='$brcode' group by gname,invdate,invno");
 
     
	//print "<table class='report' border='1'>";
	$mfrom_date = $fromdt;
	$mto_date = $todt;
	$contents="\t \t Sales Summary for the period from ".$mfrom_date. " to " .$mto_date." \n";
	echo $contents;
	$contents="BillNo \t Amount \t MDA \t State Rebate Normal \t State Rebate Spl \t Reduction \t Net Cash \t Tax \t Total Amount \t Debtors\n\n";
	echo $contents;
	
	$mstr[]='';
	$i=1;
	$mgname = '.';
	$mtot_total = 0;
	$mtot_mba= 0;
	$mtot_strbtnml = 0;
	$mtot_strbtspl = 0;
	$mtot_redct = 0;
	$mtot_befrtx = 0;
	$mtot_taxamt = 0;
	$mtot_nettot = 0;

	$mgr_total = 0;
	$mgr_mba= 0;
	$mgr_strbtnml = 0;
	$mgr_strbtspl = 0;
	$mgr_redct = 0;
	$mgr_befrtx = 0;
	$mgr_taxamt = 0;
	$mgr_nettot = 0;
	$ctr=0;
 $mgr_dbtrs=0; $mgr_dbtrs1=0;
	while($row=mysqli_fetch_array($qry))
  	{  
		if($mgname <> $row[0])
		{
			if($mgname <> '.')
			{	
			
			$contents=$mgname." Total \t".number_format($mgr_total,2)."\t".number_format($mgr_mba,2)."\t".number_format($mgr_strbtnml,2)."\t".number_format($mgr_strbtspl,2)."\t".number_format($mgr_redct,2)."\t".number_format($mgr_befrtx,2)."\t".number_format($mgr_taxamt,2)."\t"
			.number_format($mgr_nettot,2)."\t".number_format($mgr_dbtrs, 2)."\n";
			echo $contents;
				
	
mysqli_query($db,"insert into $tbl (gname,total,mba,strbtnml,strbtspl,redct,befrtx,taxamt,nettot,dbttot) values ('$mgname',$mgr_total,$mgr_mba,$mgr_strbtnml,$mgr_strbtspl,$mgr_redct,$mgr_befrtx,$mgr_taxamt,$mgr_nettot,$mgr_dbtrs)") or die(mysqli_error($db));			//mysqli_query($db,"insert into $tbl (gname,total,mba,strbtnml,strbtspl,redct,befrtx,taxamt,nettot) values ('$mgname',$mgr_total,$mgr_mba,$mgr_strbtnml,$mgr_strbtspl,$mgr_redct,$mgr_befrtx,$mgr_taxamt,$mgr_nettot)") or die(mysqli_error($db));
				$mgr_total = 0;
				$mgr_mba= 0;
				$mgr_strbtnml = 0;
				$mgr_strbtspl = 0;
				$mgr_redct = 0;
				$mgr_befrtx = 0;
				$mgr_taxamt = 0;
				$mgr_nettot = 0;

$mgr_dbtrs=0;			}
			$contents=$row[0]."\n";
			echo $contents;
			
			$mgname = $row[0];
		}

		$date = $row[1];

if($row[11]=='CREDIT')
{
$dbtrs=$row[9];
$cshvl=0;
//$mgr_dbtrs+=$row[9];
}
else
{
$dbtrs=0;
$cshvl=$row[9];		
$mtot_nettot += $row[9];

}
$mgr_dbtrs+=$dbtrs;
$mgr_dbtrs1+=$dbtrs;
		$contents="'".$row[10]." \t".number_format($row[2],2)."\t".number_format($row[3],2)."\t".number_format($row[4],2)."\t".number_format($row[5],2)."\t".number_format($row[6],2)."\t".$row[7]."\t".number_format($row[8],2)."\t"
		.number_format($cshvl,2)."\t".number_format($dbtrs,2)."\n";
		echo $contents;
		//print "<tr><td>".$row[10]."</td><td align='right'>$row[2]</td><td align='right'>$row[3]</td><td align='right'>$row[4]</td><td align='right'>$row[5]</td><td align='right'>$row[6]</td><td align='right'>$row[7]</td><td align='right'>$row[8]</td><td align='right'>$cshvl</td><td align='right'>$dbtrs</td></tr>";

		$mgr_total += $row[2];
		$mgr_mba += $row[3];
		$mgr_strbtnml += $row[4];
		$mgr_strbtspl += $row[5];
		$mgr_redct += $row[6];
		$mgr_befrtx += $row[7];
		$mgr_taxamt += $row[8];
		$mgr_nettot += $cshvl;


//$mgr_dbtrs+=$dbtrs;		
		$mtot_total += $row[2];
		$mtot_mba += $row[3];
		$mtot_strbtnml += $row[4];
		$mtot_strbtspl += $row[5];
		$mtot_redct += $row[6];
		$mtot_befrtx += $row[7];
		$mtot_taxamt += $row[8];

		$i++;
	}		
$contents=$mgname." Total \t".number_format($mgr_total,2)."\t".number_format($mgr_mba,2)."\t".number_format($mgr_strbtnml,2)."\t".number_format($mgr_strbtspl,2)."\t".number_format($mgr_redct,2)."\t".number_format($mgr_befrtx,2)."\t".number_format($mgr_taxamt,2)."\t".number_format($mgr_nettot,2)."\t".number_format($mgr_dbtrs,2)."\n\n";
echo $contents;	
//print "<tr><td><b>$mgname Total</td><td align='right'>".$mgr_total."</td><td align='right'>".$mgr_mba."</td><td align='right'>".$mgr_strbtnml."</td><td align='right'>".$mgr_strbtspl."</td><td align='right'>".$mgr_redct."</td><td align='right'>".$mgr_befrtx."</td><td align='right'>".$mgr_taxamt."</td><td align='right'>".$mgr_nettot."</td><td align='right'>".$mgr_dbtrs."</td></tr>";


	
mysqli_query($db,"insert into $tbl (gname,total,mba,strbtnml,strbtspl,redct,befrtx,taxamt,nettot,dbttot) values ('$mgname',$mgr_total,$mgr_mba,$mgr_strbtnml,$mgr_strbtspl,$mgr_redct,$mgr_befrtx,$mgr_taxamt,$mgr_nettot,$mgr_dbtrs)") or die(mysqli_error($db));


//	print "<tr><td><b>Grand Total</td><td align='right'><b>".number_format($mtot_total, 2, '.', '')."</td><td align='right'><b>".number_format($mtot_mba, 2, '.', '')."</td><td align='right'><b>".number_format($mtot_strbtnml, 2, '.', '')."</td><td align='right'><b>".number_format($mtot_strbtspl, 2, '.', '')."</td><td align='right'><b>".number_format($mtot_redct, 2, '.', '')."</td><td align='right'><b>".number_format($mtot_befrtx, 2, '.', '')."</td><td align='right'><b>".number_format($mtot_taxamt, 2, '.', '')."</td><td align='right'><b>".number_format($mtot_nettot, 2, '.', '')."</td></tr>";
	$contents="Grand Total \t".number_format($mtot_total,2)."\t".number_format($mtot_mba,2)."\t".number_format($mtot_strbtnml,2)."\t".number_format($mtot_strbtspl,2).
	"\t".number_format($mtot_redct,2)."\t".number_format($mtot_befrtx,2)."\t".number_format($mtot_taxamt,2)."\t".number_format($mtot_nettot,2).
	"\t".number_format($mgr_dbtrs1,2)."\n";
	echo $contents;	
	//print "<tr><td><b>Grand Total</td><td align='right'><b>".$mtot_total."</td><td align='right'><b>".$mtot_mba."</td><td align='right'><b>".$mtot_strbtnml."</td><td align='right'><b>".$mtot_strbtspl."</td><td align='right'><b>".$mtot_redct."</td><td align='right'><b>".$mtot_befrtx."</td><td align='right'><b>".$mtot_taxamt."</td><td align='right'><b>".$mtot_nettot."</td><td align='right'><b>".$mgr_dbtrs1."</td></tr>";
	//print "</table>";


$contents = strip_tags($contents);
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
//print $contents;
?>
