<?php
date_default_timezone_set("Asia/Kolkata");
 error_reporting(0);
ob_start();
session_start();
 
	include("../../include/db.php");

 $job_card_no=$_GET['job_card_no'];
 $invoice_no=$_GET['invoice_no'];
 $billing_type=$_GET['billing_type'];
 // echo $billing_type."karthi";
 $acc_year=$_GET['acc_year'];
 $from_date=$_GET['from_date'];
 $to_date=$_GET['to_date'];

 // echo $to_date;
 $cur_date=date("Y-m-d");
if($from_date!=""){ $from_date1= "bill_date>='$from_date'"; }else{$from_date1="bill_date='$current_date'"; }
 if($to_date!=""){ $to_date1 = "bill_date<='$to_date'";}else{$to_date1='';}
 if($cus_name!=""){ $cus_name1= "cust_name='$cus_name'"; }else{$cus_name1=''; }
 if($billing_type!=""){ $billing_type1= "billing_type='$billing_type'"; }else{$billing_type1=''; }
if($job_card_no!=""){ $job_card_no1= "job_card_no='$job_card_no'"; }else{$job_card_no1=''; }
 if($invoice_no!=""){ $invoice_no1= "invoice_no='$invoice_no'"; }else{$invoice_no1=''; }

$all_value10 = $from_date1."@".$to_date1."@".$cus_name1."@".$billing_type1."@".$job_card_no1."@".$invoice_no1;
$all_array10 = explode('@',$all_value10);

foreach($all_array10 as $value10)
{ 
if($value10!='')
{
$get_query102 .= $value10." AND ";
}
}  
// echo $get_query102;
if($from_date!=""){ $from_date2= $from_date; }else{$from_date2=$cur_date; }
if($to_date!=""){ $to_date2 = $to_date;}else{$to_date2=$cur_date;}
if($billing_type4!=""){ $billing_type3=$billing_type4;} else {$billing_type3="ALL" ;} 
if($job_card_no!=""){ $job_card_no1=$job_card_no;} else {$job_card_no1="ALL" ;}



$blog=array('','Billing Report','','');


foreach($blog as $icon)
{
$output		.= '"'.$icon.'",';
}

$output .="\n";
$output .="\n"; 
$output .="From Date".",".$from_date2.","."To Date".",".$to_date2.","."Job CardNo".",".$billing_type3.","."Job CardNo".",".$job_card_no1."\n"; 
$output .="\n"; 

 

 
     $output .='"SNo","Date","Jobcard No","Invoice No","Customer Name","Job Created","Printed Qty","Tot Value"'."\n";
      $table_values_new=array(ItemGroupName);
	 $table_values=array(SNo,Date,JobcardNo,InvoiceNo,CustomerName,JobCreated,PrintedQty,TotValue);
   function get_freight_charges_list($jobcard_no)
  {
   $sql="select frieght_charges as frieght from printer_production_main where job_card_no='$jobcard_no' union select frieght_charges as frieght from fusing_production_main where job_card_no='$jobcard_no'";
 $rs1=mysql_query($sql);
 while($rsdata=mysql_fetch_array($rs1))		
		{
		$frieght_charges=$rsdata['frieght'];
		}
 return $frieght_charges;
 } 

function get_billing_tot_value($job_card_no)
{
     		$sql2="select sum(jobcard_values) as jobcard_values from  jobcard_sublist where jobcard_no='$job_card_no'"; 
		    $rs2=mysql_query($sql2);
			while($rsdata2=mysql_fetch_array($rs2))
			{
	   			$jobcard_values2=$rsdata2['jobcard_values'];
  			}
  		return $jobcard_values2;
}
function get_tax_type_list($job_card_no)
  {
  $sql="select tax_type as tax_type from printer_production_main where job_card_no='$job_card_no' union select tax_type as tax_type from fusing_production_main where job_card_no='$job_card_no'";
 $rs1=mysql_query($sql);
 while($rsdata=mysql_fetch_array($rs1))		
		{
		$tax_type=$rsdata['tax_type'];
		}
 return $tax_type;
 } 
 
 
function  get_total_billing($invoice_no,$job_card_no,$billing_type,$acc_year)
  {
 $sql_fusing_sub="select * from fusing_jobcard_sub where invoice_no='$invoice_no'  and billing_type='$billing_type' and acc_year='$acc_year' ";
 $rs_fusing_sub=mysql_query($sql_fusing_sub);
 $count_fusing=mysql_num_rows($rs_fusing_sub);
if($count_fusing!=0)
{
  while($rsdata_fusing_sub=mysql_fetch_array($rs_fusing_sub))
  {
  $jobcard_no22=$rsdata_fusing_sub['jobcard_no'];
  $sql="select sum(jobcard_values) as jobcard_values,sum(qty) as qty from jobcard_sublist where jobcard_no='$jobcard_no22'";
   $sno=0;
	 $rs=mysql_query($sql);
	 $rscount=mysql_num_rows($rs);
	   $count_sub+=$rscount;
   		while($rsdata=mysql_fetch_array($rs))		
		{
		$jobcard_values+=$rsdata['jobcard_values'];
		}
	}
}
			else
			{  
			$sql="select sum(jobcard_values) as jobcard_values,sum(qty) as qty from jobcard_sublist where jobcard_no='$job_card_no'";
   $sno=0;
	 $rs=mysql_query($sql);
	 $rscount=mysql_num_rows($rs);
	   $count_sub=$rscount;
   		while($rsdata=mysql_fetch_array($rs))		
		{
	    $sno=$sno+1;
		$jobcard_values+=$rsdata['jobcard_values'];
		} 
}
return $jobcard_values;
}



function  get_qty_billing($invoice_no,$job_card_no,$billing_type,$acc_year)
{
 $sql_fusing_sub="select * from fusing_jobcard_sub where invoice_no='$invoice_no' and billing_type='$billing_type' and acc_year='$acc_year' ";
 $rs_fusing_sub=mysql_query($sql_fusing_sub);
 $count_fusing=mysql_num_rows($rs_fusing_sub);
if($count_fusing!=0)
{
  while($rsdata_fusing_sub=mysql_fetch_array($rs_fusing_sub))
  {
  $jobcard_nos=$rsdata_fusing_sub['jobcard_no'];
  $sql="select sum(qty) as qty from jobcard_sublist where jobcard_no='$jobcard_nos'";
   $sno=0;
	 $rs=mysql_query($sql);
	 $rscount=mysql_num_rows($rs);
	   $count_sub+=$rscount;
   		while($rsdata=mysql_fetch_array($rs))		
		{
	  $qty_billing+=$rsdata['qty'];
		}
	}
}
else
	{  
	 $sql="select sum(qty) as qty from jobcard_sublist where jobcard_no='$job_card_no'";
	 $sno=0;
	 $rs=mysql_query($sql);
	 $rscount=mysql_num_rows($rs);
	   $count_sub=$rscount;
   		while($rsdata=mysql_fetch_array($rs))		
		{
	    $qty_billing+=$rsdata['qty'];
		} 
}
return $qty_billing;
}



function get_account_year($job_card_no)
  {
 $sql_fusing_sub="select * from fusing_jobcard_sub where jobcard_no='$job_card_no'";
 $rs1=mysql_query($sql_fusing_sub);
 while($rsdata=mysql_fetch_array($rs1))		
		{
		$acc_year=$rsdata['acc_year'];
		}
 return $acc_year;
 } 
   
 // $sql="select * from printer_production_main where printer_status='1' and  bill_date>='$from_date' and bill_date<='$to_date'  union select * from fusing_production_main where   bill_date>='$from_date' and bill_date<='$to_date'  group by invoice_no,billing_type";



	$sql="select * from printer_production_main where $get_query102 printer_status='1' union select * from fusing_production_main where $get_query102 printer_status='1'  group by invoice_no,billing_type order by bill_date";
	// echo "select * from printer_production_main where $get_query102 printer_status='1' union select * from fusing_production_main where $get_query102 printer_status='1'  group by invoice_no,billing_type";
    $rs=mysql_query($sql);
	$rscount1=mysql_num_rows($rs);
	$s_no=0;
	if($rscount1!='')
	{
   		while($rsdata=mysql_fetch_array($rs))
  			{
   				 $s_no=$s_no+1;
				 $from=$from+1;
                 $job_card_no=$rsdata['job_card_no'];
				$bill_date=$rsdata['bill_date'];
	   			$cust_name=$rsdata['cust_name'];
	   			$job_created=$rsdata['job_created'];
				$cust_po=$rsdata['cust_po'];
	   			$style_no=$rsdata['style_no'];
	   			$style_name=$rsdata['style_name'];
				$combo=$rsdata['combo'];
	   			$printed_qty=$rsdata['printed_qty'];
				$billed_status=$rsdata['billed_status'];
			    $refer=$rsdata['refer'];
                $invoice_no=$rsdata['invoice_no'];
			 	$billing_types=$rsdata['billing_type'];
				$tax_type=$rsdata['tax_type'];
				$payment_terms=$rsdata['payment_terms'];
				$acc_year=get_account_year($job_card_no);
				$tot_values=get_billing_tot_value($job_card_no);
				$get_freight=get_freight_charges_list($job_card_no);
				$total_value_job=$tot_values+$get_freight;
				$tax_type_list=get_tax_type_list($job_card_no);
				$total_amount=get_total_billing($invoice_no,$job_card_no,$billing_types,$acc_year);
				$qty_billings=get_qty_billing($invoice_no,$job_card_no,$billing_types,$acc_year);
				$billing_type=$rsdata['billing_type'];
	
 if($tax_type_list=="VAT")
   { 
 	 $a=(($total_amount+$get_freight)*((70/100)*(5/100)));
	 $c=number_format($a,2); 
	}
				
 if($tax_type_list=="CST") 
 { 
  $a=(($total_amount+$get_freight)*((70/100)*(2/100)));
  $c=number_format($a,2); 
 }
		   
		   
 $round_tots=number_format(($total+$frieght_charges+$a),2);
 $b=explode(".",$round_tots);
 $d =$b[1];
 if($b[1]>=50)
 {
 $ss="0.".$b[1];
 $plus=1-$ss;
 }
			
if(($b[1]<=49)&&($b[1]!=0)) { $minus="- 0.".$b[1]; } 

if($billing_type!="Estimate")
{
	$overall_amount = round(($total_amount+$get_freight)+$a);
}
else
{
	$overall_amount = round($total_amount);
}








		  
			 $sno=$sno+1;
			foreach ($table_values as $val) 
			{
				// SNo,Date,JobcardNo,InvoiceNo,CustomerName,JobCreated,PrintedQty,TotValue
				
				if($val=='SNo'){$output.='"'.$sno.'",';}
				if($val=='Date'){$output.='"'.date("d-m-Y",strtotime($bill_date)).'",';}
				if($val=='JobcardNo'){$output.='"'.$job_card_no.'",';}
				if($val=='InvoiceNo'){$output.='"'.$invoice_no.'",';}
				if($val=='CustomerName'){$output.='"'.$cust_name.'",';}
				if($val=='JobCreated'){$output.='"'.$job_created.'",';}
				if($val=='PrintedQty'){$output.='"'.number_format($qty_billings,3).'",';}
				if($val=='TotValue'){$output.='"'.number_format($overall_amount,2).'",';}
				
				
				// $totalss+=$printed_qty;
				// $totalss_qtys+=$total_amount;

if(($val!='SNo')&&($val!='Date')&&($val!='JobcardNo')&&($val!='InvoiceNo')&&($val!='CustomerName')&&($val!='JobCreated')&&($val!='PrintedQty')&&($val!='TotValue')){$output .='"'.$val.'",';}
} 
 $output .="\n";
		
					
				
	$printed_qty=intval($qty_billings);	
	$over_all_total=floatval($overall_amount);			 
 $totalss+=$printed_qty;
  $totalss_qtys+=$over_all_total;
  // echo $totalss_qtys;
}
 
$output .="".",".",".",".",".",".Total.",".round($totalss).",".round($totalss_qtys)."\n\n";
}
$date=date('d-m-Y H:i:s');
$filename =  "Billing Report". $date.".csv";
header('Content-type: application/xls');
header('Content-Disposition: attachment; filename='.$filename);
echo $output;



exit;
	


?>
<style>
br{
	
	mso-data-placement:same-cell;
	}
</style>