<?
//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['fromdt'];
  $todt=$_REQUEST['todt'];
  $type=$_REQUEST['type'];
  $mfrom_date = $fromdt;
  $mto_date = $todt;
  $fromdt = implode('-', array_reverse(explode('-', $fromdt)));
  $todt = implode('-', array_reverse(explode('-', $todt)));
$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
$brnchnm= $brrow[1];
$filename ="purchreg_new_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\tPurchase Register for the period from ".$mfrom_date."to".$mto_date."\n\n\n";

 if ($type == 'All')
       $qry=mysqli_query($db,"select gname,invdate,sum(total),mba,comision,discount,mrgin,invno,pmode,pfchrgs,othr_chrgs,refno,refdt from brtransferin where invdate between '$fromdt' and '$todt' and brcode='$brcode' group by invno,gname");
  else
       $qry=mysqli_query($db,"select gname,invdate,sum(total),mba,comision,discount,mrgin,invno,pmode,pfchrgs,othr_chrgs,refno,refdt from brtransferin where invdate between '$fromdt' and '$todt' and brcode='$brcode' and gname = '$type' group by invno,gname");

$contents.= "\t GRS#\t Invoice No \t Inv.Date \t Cotton \t Poly\t Reeled Silk\t Spun Silk \t VIP \t Woollen \t Others \t MDA\t Commission \t Discount \t Margin \t PF Charges \t Other Charges \t Total Amount  \n";
$colspan=6;
  $mstr[]='';
  $i=1;
  $mgname = '.';
  $mtot_cot = 0;
  $mtot_pol = 0;
  $mtot_rel = 0;
  $mtot_spu = 0;
  $mtot_vip = 0;
  $mtot_woo = 0;
  $mtot_oth = 0;
  $mtot_total = 0;
  $mtot_mba= 0;
  $mtot_comision = 0;
  $mtot_discount = 0;
  $mtot_pfchrgs = 0;
  $mtot_othr_chrgs = 0;
  $mtot_taxamt = 0;
  $mtot_nettot = 0;
  $mtot_dbtrs = 0;
  $mgr_total = 0;
  $mgr_cot = 0;
  $mgr_pol = 0;
  $mgr_rel = 0;
  $mgr_spu = 0;
  $mgr_vip = 0;
  $mgr_woo = 0;
  $mgr_oth = 0;
  $mgr_mba= 0;
  $mgr_comision = 0;
  $mgr_discount = 0;
  $mgr_pfchrgs = 0;
  $mgr_othr_chrgs = 0;
  $mgr_taxamt = 0;
  $mgr_nettot = 0;
  $mgr_dbtrs = 0;

  $ctr=0;
  $mgr_dbtrs=0; $mgr_dbtrs1=0;
  $minvno = '';
  while($row=mysqli_fetch_array($qry))
    {
    if($minvno <>  $row[7] && $ctr <> 0)
    {
        $mtot_nettot = $mtot_cot+$mtot_pol+$mtot_rel+$mtot_spu+$mtot_vip+$mtot_woo+$mtot_oth-$mtot_mba-$mtot_comision-$mtot_discount+$mtot_mrgin+$mtot_pfchrgs+$mtot_othr_chrgs;
		$contents.= "\t".$minvno." \t". $mrefno." \t".$mrefdt."\t".number_format($mtot_cot,2,'.','')."\t".number_format($mtot_pol,2,'.','')." \t". number_format($mtot_rel,2,'.','')."\t". number_format($mtot_spu,2,'.','')."\t". number_format($mtot_vip,2,'.','')."\t". number_format($mtot_woo,2,'.','')."\t ".number_format($mtot_oth,2,'.','')."\t". number_format($mtot_mba,2,'.','')." \t".number_format($mtot_comision,2,'.','')."\t".number_format($mtot_discount,2,'.','')."\t".number_format($mtot_mrgin,2,'.','')."\t".number_format($mtot_pfchrgs,2,'.','')."\t".number_format($mtot_othr_chrgs,2,'.','')."\t".number_format($mtot_nettot,2,'.','')." \n"; 

        $mgr_cot +=  $mtot_cot;
        $mgr_pol +=  $mtot_pol;
        $mgr_rel +=  $mtot_rel;
        $mgr_spu +=  $mtot_spu;
        $mgr_vip +=  $mtot_vip;
        $mgr_woo +=  $mtot_woo;
        $mgr_oth +=  $mtot_oth;
        $mgr_mba+=  $mtot_mba;
        $mgr_comision +=  $mtot_comision;
        $mgr_discount +=  $mtot_discount;
        $mgr_pfchrgs +=  $mtot_pfchrgs;
        $mgr_othr_chrgs +=  $mtot_othr_chrgs;
        $mgr_mrgin +=  $mtot_mrgin;
        $mgr_nettot +=  $mtot_nettot;
        $mgr_dbtrs +=  $mtot_dbtrs;

        $mtot_cot = 0;
        $mtot_pol = 0;
        $mtot_rel = 0;
        $mtot_spu = 0;
        $mtot_vip = 0;
        $mtot_woo = 0;
        $mtot_oth = 0;
        $mtot_mba= 0;
        $mtot_comision = 0;
        $mtot_discount = 0;
        $mtot_pfchrgs = 0;
        $mtot_othr_chrgs = 0;
        $mtot_mrgin = 0;
        $mtot_nettot = 0;
        $mtot_dbtrs = 0;

    }
    $minvno = $row[7];
    ++$ctr;
    $mrefno = $row[11];
    $mrefdt = $row[1];
                $mrefdt=implode('-', array_reverse(explode('-', $mrefdt)));
    $date = $row[1];
    if($row[0]== 'COTTON')
      $mtot_cot += $row[2];
    else if ($row[0]== 'POLYSTER')
      $mtot_pol += $row[2];
    else if ($row[0]== 'REELED SILK')
      $mtot_rel += $row[2];
    else if ($row[0]== 'SPUNSILK')
      $mtot_spu += $row[2];
    else if ($row[0]== '0% TAXABLE VIP' || $row[0]== '5% TAXABLE VIP' || $row[0]== '14.5% TAXABLE VIP' || $row[0]== '1% TAXABLE VIP')
      $mtot_vip += $row[2];
    else if ($row[0]== 'WOOLEN')
      $mtot_woo += $row[2];
    else
      $mtot_oth += $row[2];

  //select gname,invdate,sum(total),sum(mba),sum(comision),sum(discount),sum(mrgin),invno,pmode,sum(pfchrgs),sum(othr_chrgs),refno,refdt from brtransferin
   //          0    1         2         3           4            5            6        7     8       9             10           11    12
    $mtot_mba+= $row[3];
    $mtot_comision =$row[4];
    $mtot_discount =$row[5];
    $mtot_mrgin+=$row[2]*($row[6]/100);
    $mtot_pfchrgs =$row[9];
    $mtot_othr_chrgs =$row[10];
    $i++;
  }
  $mgr_cot +=  $mtot_cot;
  $mgr_pol +=  $mtot_pol;
  $mgr_rel +=  $mtot_rel;
  $mgr_spu +=  $mtot_spu;
  $mgr_vip +=  $mtot_vip;
  $mgr_woo +=  $mtot_woo;
  $mgr_oth +=  $mtot_oth;
  $mgr_mba+=  $mtot_mba;
  $mgr_comision +=$mtot_comision;
  $mgr_discount +=$mtot_discount;
  $mgr_mrgin +=$mtot_mrgin;
  $mgr_pfchrgs +=$mtot_pfchrgs;
  $mgr_othr_chrgs +=$mtot_othr_chrgs;
  $mtot_nettot = $mtot_cot+$mtot_pol+$mtot_rel+$mtot_spu+$mtot_vip+$mtot_woo+$mtot_oth-$mtot_mba-$mtot_comision-$mtot_discount+$mtot_mrgin+$mtot_pfchrgs+$mtot_othr_chrgs;
  $contents.= "\t".$minvno." \t". $mrefno." \t".$mrefdt."\t".number_format($mtot_cot,2,'.','')."\t".number_format($mtot_pol,2,'.','')." \t". number_format($mtot_rel,2,'.','')."\t". number_format($mtot_spu,2,'.','')."\t". number_format($mtot_vip,2,'.','')."\t". number_format($mtot_woo,2,'.','')."\t ".number_format($mtot_oth,2,'.','')."\t". number_format($mtot_mba,2,'.','')." \t".number_format($mtot_comision,2,'.','')."\t".number_format($mtot_discount,2,'.','')."\t".number_format($mtot_mrgin,2,'.','')."\t".number_format($mtot_pfchrgs,2,'.','')."\t".number_format($mtot_othr_chrgs,2,'.','')."\t".number_format($mtot_nettot,2,'.','')." \n\n"; 
  
  $mgr_nettot +=  $mtot_nettot;
$contents.= "\t \t Grand Total \t\t".number_format($mgr_cot,2,'.','')."\t".number_format($mgr_pol,2,'.','')." \t". number_format($mgr_rel,2,'.','')."\t". number_format($mgr_spu,2,'.','')."\t". number_format($mgr_vip,2,'.','')."\t". number_format($mgr_woo,2,'.','')."\t ".number_format($mgr_oth,2,'.','')."\t". number_format($mgr_mba,2,'.','')." \t".number_format($mgr_comision,2,'.','')."\t".number_format($mgr_discount,2,'.','')."\t".number_format($mgr_mrgin,2,'.','')."\t".number_format($mgr_pfchrgs,2,'.','')."\t".number_format($mgr_othr_chrgs,2,'.','')."\t".number_format($mgr_nettot,2,'.','')." \n"; 

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