<?
//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'];
  
  $type=$_REQUEST['type'];
   $fromdt=implode('-', array_reverse(explode('-', $fromdt)));
  $todt=implode('-', array_reverse(explode('-', $todt)));
  if ($type == 'All')
    $qry=mysqli_query($db,"select gname,sum(befrtx),sum(taxamt),sum(nettot),invno,group_concat(item),invdate,sum(total),tax from prod_invoice where invdate between '$fromdt' and '$todt' and brcode='$brcode' group by gname,invdate,invno");
  else
    $qry=mysqli_query($db,"select gname,sum(befrtx),sum(taxamt),sum(nettot),invno,group_concat(item),invdate,sum(total),tax from prod_invoice where invdate between '$fromdt' and '$todt' and brcode='$brcode' and gname = '$type' group by tax,invdate,invno");



  //$qry=mysqli_query($db,"select gname,sum(befrtx),sum(taxamt),sum(nettot),invno,group_concat(item),invdate,sum(total),tax from prod_invoice where invdate between '$fromdt' and '$todt' and brcode='$brcode' and gname = '$type' group by gname,invdate,invno");

  $mfrom_date = implode('-', array_reverse(explode('-', $fromdt)));
  $mto_date = implode('-', array_reverse(explode('-', $todt)));
$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
$brnchnm= $brrow[1];
$filename ="stax_salesday_exl.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\tMONTHLY VAT REGISTER FOR THE PERIOD FROM ".$mfrom_date."to".$mto_date."\n";

$contents.= "\t Bill No\t Date \t Descripton \t Sales \t VAT Collected \t Total Amount\t Item Details\n";
$colspan=6;
  $mstr[]='';
  $i=1;
  $mgname1 = '.';
  $mtot_befrtx = 0;
  $mtot_taxamt = 0;
  $mtot_nettot = 0;
  $mgr_befrtx = 0;
  $mgr_taxamt = 0;
  $mgr_nettot = 0;
  $ctr=0;
    $ctr_flg = 0;
  $mbill_to = "";
    $minv_date = ".";
    $mgrp_befrtx = 0;
    $mgrp_taxamt = 0;
    $mgrp_nettot = 0;
  while($row=mysqli_fetch_array($qry))
    {
        if($ctr_flg++==0)
            $mbill_frm = $row[4];
    if($mgname1 <> $row[0])
    {
      if($mgname1 <> '.')
      {
                $mdate = implode('-', array_reverse(explode('-', $minv_date)));
                $mgrp_befrtx += $mtot_befrtx;
                $mgrp_taxamt += $mtot_taxamt;
                $mgrp_nettot += $mtot_nettot;
                
				$contents.= "\t<b>".$mbill_frm ."-". $mbill_to."\t".$mdate." \t".$mgname1."\t". number_format($mtot_befrtx,2,'.',',')."\t".number_format($mtot_taxamt,2,'.',',')."\t".number_format($mtot_nettot,2,'.',',')."\t<b>".$mdesc."\n"; 
        $mtot_befrtx = 0;
        $mtot_taxamt = 0;
        $mtot_nettot = 0;
                $mbill_frm = $row[4];
                $mgr_befrtx += $mgrp_befrtx;
                $mgr_taxamt += $mgrp_taxamt;
                $mgr_nettot += $mgrp_nettot;
				
				$contents.= "\t<b>".$mgname1 ."Total\t\t\t".number_format($mgrp_befrtx,2,'.',',')." \t".number_format($mgrp_taxamt,2,'.',',')."\t".number_format($mtot_nettot,2,'.',',')."\t<b>".$mdesc."\n"; 
                $mgrp_befrtx = 0;
                $mgrp_taxamt = 0;
                $mgrp_nettot = 0;
                //print "<tr><td><b>$mgname1 Total</td><td></td><td><b></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><td><b>$mdesc</td></tr>";
                $minv_date = $row[6];
            }
      $mgname1 = $row[0];
    }

if($minv_date <> $row[6])
    {
      if($minv_date <> '.')
      {
                $mdate = implode('-', array_reverse(explode('-', $minv_date)));
                $mgrp_befrtx += $mtot_befrtx;
                $mgrp_taxamt += $mtot_taxamt;
                $mgrp_nettot += $mtot_nettot;


		$contents.= "\t<b>".$mbill_frm ."-". $mbill_to."\t".$mdate." \t".$mgname1."\t". number_format($mtot_befrtx,2,'.',',')."\t".number_format($mtot_taxamt,2,'.',',')."\t".number_format($mtot_nettot,2,'.',',')."\t<b>".$mdesc."\n"; 
        $mtot_befrtx = 0;
        $mtot_taxamt = 0;
        $mtot_nettot = 0;
                $mbill_frm = $row[4];
            }
      $minv_date = $row[6];
    }
        if($row[8]>0)
            $mtot_befrtx += $row[1];
        else
            $mtot_befrtx += $row[7];
        $mtot_taxamt += $row[2];
        $mtot_nettot += $row[3];
        $mdesc = $row[5];
        $mbill_to = $row[4];
        $minv_date = $row[6];
    $i++;
  }
    $mgrp_befrtx += $mtot_befrtx;
    $mgrp_taxamt += $mtot_taxamt;
    $mgrp_nettot += $mtot_nettot;
    $mgr_befrtx += $mgrp_befrtx;
    $mgr_taxamt += $mgrp_taxamt;
    $mgr_nettot += $mgrp_nettot;
    $mdate = implode('-', array_reverse(explode('-', $minv_date)));
   
	$contents.= "\t<b>".$mbill_frm ."-". $mbill_to."\t".$mdate." \t".$mgname1."\t". number_format($mtot_befrtx,2,'.',',')."\t".number_format($mtot_taxamt,2,'.',',')."\t".number_format($mtot_nettot,2,'.',',')."\t<b>".$mdesc."\n"; 
	$contents.= "\t<b>".$mgname1 ."Total\t\t\t".number_format($mgrp_befrtx,2,'.',',')." \t".number_format($mgrp_taxamt,2,'.',',')."\t".number_format($mgrp_nettot,2,'.',',')."\t<b>".$mdesc."\n"; 

    $contents.= "\t<b>Total\t\t\t".number_format($mgr_befrtx,2,'.',',')." \t".number_format($mgr_taxamt,2,'.',',')."\t".number_format($mgr_nettot,2,'.',',')."\t<b>".$mdesc."\n"; 

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