<?
//include_once('includes/top.php');
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;
}

  $fromdt=$_REQUEST['df'];
  $todt=$_REQUEST['dt'];
  $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))");
  
 
$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
$brnchnm= $brrow[1];
$filename ="stax_salrtn_excel.xls";
$contents="\t\t\t\t\tKERALA KHADI & VILLAGE INDUSTRIES BOARD \n";
$contents.= "\t\t\t\t\t\tCENTER NAME : ".$brnchnm." \n";
$contents.= "\t\t\t\t\tSales Return Register for the period from ".$fromdt."to".$todt."\n";

$fromdt=implode('-', array_reverse(explode('-', $fromdt)));
  $todt=implode('-', array_reverse(explode('-', $todt)));

$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,tax,customer from salesretn where invdate between '$fromdt' and '$todt' and brcode='$brcode' group by tax,invdate,invno");

$contents.= "\t BillNo\t Customer \t Amount \t MDA \t State Rebate Normal \t State Rebate Spl \t Reduction \t Net Cash \t Tax \t Total Amount \n";
$colspan=6;
  $mstr[]='';
  $i=1;
  $mtax = '.';
  $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($mtax <> $row[12])
    {
      if($mtax <> '.')
      {
		  

		  $contents.= "\t".$mtax."% Tax Total\t\t".$mgr_total." \t".$mgr_mba."\t".$mgr_strbtnml."\t".$mgr_strbtspl." \t".$mgr_redct."\t".$mgr_befrtx."\t".$mgr_taxamt."\t".$mgr_nettot."\n"; 
        
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));              $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;     
}
	
    $date = $row[1];

  $dbtrs=0;
  $cshvl=$row[9];
  $mtot_nettot += $row[9];
  $mgr_dbtrs+=$dbtrs;
  $mgr_dbtrs1+=$dbtrs;

	  $contents.= "\t".$row[10]."\t".$row[13]." \t".$row[2]."\t". $row[3]."\t". $row[4]."\t".$row[5]."\t". $row[6]."\t". $row[7]."\t".$row[8]."\t".$cshvl."\n"; 
    $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;
    $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.= "\t".$mtax." Tax Total \t\t".$mgr_total." \t". $mgr_taxamt."\t". $mgr_mba."\t".$mgr_strbtnml." \t". $mgr_strbtspl."\t". $mgr_redct."\t".$mgr_befrtx."\t". $mgr_taxamt."\t".$mgr_nettot."\n"; 
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));
 
  $contents.= "\t<b>Grand Total \t\t".$mtot_total." \t". $mtot_mba."\t". $mtot_strbtnml."\t".$mtot_strbtspl." \t". $mtot_redct."\t". $mtot_befrtx."\t".$mtot_taxamt."\t". $mtot_nettot."\n"; 
}
$contents.= "\t".$mtax." Tax Total (".$mgname1.")\t".$mgr_befrtx." \t". $mgr_taxamt."\t". $mgr_nettot."\n"; 
$contents = strip_tags($contents);
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
print $contents;
//header('location:stockverblnk.php');
?>