<?php
error_reporting(E_ALL);
date_default_timezone_set("Asia/Kolkata");
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'];
$acc_year = $_GET['acc_year'];
$bill_date = $_GET['bill_date'];

 //echo "select * from jobcard_main where jobcard_no='$job_card_no' ";
    $sql="select * from jobcard_main where jobcard_no='$job_card_no' ";
	$rs=mysql_query($sql);
	$sno=0;
	$from=0;
   		while($rsdata=mysql_fetch_array($rs))		
		{
   				$sno=$sno+1;
				$from=$from+1;
           	    $jobcard_no1=$rsdata['jobcard_no'];
           	    //echo $jobcard_no1;
			    $createdate=$rsdata['createdate'];
			    $print_date=$rsdata['print_date'];
	   		    $job_created=$rsdata['job_created'];
			    $cust_name=$rsdata['cust_name'];
	   		    $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'];
		    	$job_type=$rsdata['job_type'];
				$amount=$rsdata['amount'];
				$cust_po_no=$rsdata['cust_po_no'];
				$qty =$rsdata['qty'];
				$jobcard_values=$rsdata['jobcard_values'];
				
			    $estimate_type = get_estimate_type($jobcard_no1);
				$invoice_no = get_invoice_no($jobcard_no1);
				$dates=get_created_dates($jobcard_no1);
				list($estimate_type,$remark,$desc) = get_estimate_type11($jobcard_no1);
				} 
				
	$sql_user="select * from fusing_jobcard_sub where invoice_no='$invoice_no' and billing_type='$_GET[billing_type]' and acc_year='$_GET[acc_year]'";
	$rs_user=mysql_query($sql_user);
	$rscount_fuse=mysql_num_rows($rs_user);
	if($rscount_fuse!=0)
	{
	while($rsdata_user=mysql_fetch_object($rs_user))
	{ 
    $jobcard_nos=$rsdata_user->jobcard_no;
    echo  $jobcard_nos.'01';
	$inward_dc_no = get_inward_dc_no($jobcard_nos);
	}
	
	}
	else
	{
	$jobcard_no=$_GET['job_card_no'];
    $inward_dc_no = get_inward_dc_no($jobcard_no);		
	}
  echo $inward_dc_no.'testinhg';
     $sql="select * from party_creation where party_name='$cust_name' and delete_status!='1' order by party_name ASC ";
	 $rs1=mysql_query($sql);
   		while($rsdata=mysql_fetch_array($rs1))		
		{
           	    $party_no=$rsdata['party_no'];
			    $party_name1=$rsdata['party_name'];
			    $mobile_no=$rsdata['mobile_no'];
	   		    $phone_no=$rsdata['phone_no'];
			    $address=$rsdata['address'];
	   		    $city=$rsdata['city'];
	   			$cst_no=$rsdata['cst_no'];
	   			$tin_no=$rsdata['tin_no'];
				$gst_no=$rsdata['gst_no'];
}

$output = '';

$blog=array('','','Billing Individual Report','','');
foreach($blog as $icon)
{
$output		.= '"'.$icon.'",';
}
$output .="\n";
$output .="\n"; 
if ($billing_type == 'Billing') {
    $output .="Bill Date".",".$bill_date.","."Billing Type".",".$billing_type.","."Job No".",".$job_card_no."\n";
}elseif ($billing_type == 'Re Production Mistake') {
    $output .="Re Production Mistake Date".",".$bill_date.","."Billing Type".",".$billing_type.","."Job CardNo".",".$job_card_no."\n";
}else{
    $output .="Estimate Date".",".$bill_date.","."Billing Type".",".$billing_type.","."Job CardNo".",".$job_card_no."\n"; 
    }
$output .="\n"; 

$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);

if ($rs_fusing_sub) {
    if ($billing_type == 'Billing') {
        $output .= "SNo,DC No,Description Of Print,HSN Code,Qty,Rate,Amount\n";
    }else{
        $output .= "SNo,Joncard No,Description Of Print,Qty,Rate,Amount\n";
    }
    
    $frieght_charges=get_freight_charges($job_card_no);
    $tax_type=get_tax_type($job_card_no);
    
    while ($rsdata_fusing_sub = mysql_fetch_array($rs_fusing_sub)) {
        $jobcard_no = $rsdata_fusing_sub['jobcard_no'];
        $sql_jobcard_sublist = "SELECT * FROM jobcard_sublist WHERE jobcard_no='$jobcard_no'";
        
        $rs_jobcard_sublist = mysql_query($sql_jobcard_sublist);

        if ($rs_jobcard_sublist) {
            $s_no = 0;

            while ($rsdata = mysql_fetch_array($rs_jobcard_sublist)) {
                $s_no++;
                $amount = $rsdata['amount'];
                $conformed_amt = $rsdata['conformed_amt'];
                $qty = $rsdata['qty'];
                $jobcard_values = $rsdata['jobcard_values'];
                $particulars = $rsdata['particulars'];

                // Get DC No
                $dc_nos=get_dc_no($jobcard_no,$particulars);
                $dc_no=explode(',',$dc_nos);
                $dc_number=array();
                    foreach ($dc_no as $key_dc) {
                
                      $dc_number[]=$key_dc;
                    }
                     $get_dc_number=implode('',$dc_number);
                		$delivery_no =$rsdata['delivery_no'];
                    $get_hsn_codes=mysql_fetch_array(mysql_query("select hsn_code from fusing_production_main where job_card_no='$jobcard_no'"));
                    
                    $get_hsn_code=mysql_fetch_array(mysql_query("select hsn_code from printer_production_main where job_card_no='$jobcard_no'"));
                    if($get_hsn_codes['hsn_code']!=''){
                         $hsn_code=$get_hsn_codes['hsn_code']; 
                    }else{
                         $hsn_code=$get_hsn_code['hsn_code'];}

                if ($billing_type == 'Billing') {
                    $output .= "$s_no,$get_dc_number,$particulars,$hsn_code,$qty," . number_format($conformed_amt,3) . "," . $jobcard_values . "\n";
                } else {
                    $output .= "$s_no,$jobcard_no,$particulars,$qty," . number_format($conformed_amt,3) . "," . $jobcard_values . "\n";
                }
    		
        	$printed_qty=intval($qty);	
        	$over_all_total=floatval($conformed_amt);
            $totalss+=$printed_qty;
            $totalss_qtys+=$over_all_total;
            $total1+=$jobcard_values;
            }
        }
    } 
    if ($billing_type == 'Billing') {
        $output .="".",".",".",".Total.",".round($totalss).",".round($totalss_qtys).",".round($total1)."\n";
    } else {
        $output .="".",".",".Total.",".round($totalss).",".round($totalss_qtys).",".round($total1)."\n";
    }

$output .="\n";
$output .="\n"; 

$total = $total1;
$tax_amount = 0;
$cgst = 0;
$sgst = 0;
$igst = 0;
$utgst = 0;

// Calculate tax amounts based on tax type
if ($tax_type == 'GST 18%') {
    $cgst = $sgst = ($total + $frieght_charges) * (9/100);
    $tax_amount = $cgst + $sgst;
    $cgst_percent = 'CGST 9%';
    $sgst_percent = 'SGST 9%';
} elseif ($tax_type == 'GST 5%') {
    $cgst = $sgst = ($total + $frieght_charges) * (2.5/100);
    $tax_amount = $cgst + $sgst;
    $cgst_percent = 'CGST 2.5%';
    $sgst_percent = 'SGST 2.5%';
} elseif ($tax_type == 'GST 12%') {
    $cgst = $sgst = ($total + $frieght_charges) * (6/100);
    $tax_amount = $cgst + $sgst;
    $cgst_percent = 'CGST 6%';
    $sgst_percent = 'SGST 6%';
} elseif ($tax_type == 'IGST') {
    $igst = ($total + $frieght_charges) * (5/100);
    $tax_amount = $igst;
    $igst_percent = 'IGST 5%';
} elseif ($tax_type == 'IGST 18%') {
    $igst = ($total + $frieght_charges) * (18/100);
    $tax_amount = $igst;
    $igst_percent = 'IGST 18%';
} elseif ($tax_type == 'UTGST') {
    $utgst = ($total + $frieght_charges) * (18/100);
    $tax_amount = $utgst;
    $utgst_percent = 'UTGST 18%';
}
//$output01= array('AMOUNT :', number_format($total, 2));
// Write tax rows
if ($cgst_percent != '') {
   // $output02= array($cgst_percent.' :', number_format($cgst, 2));
    $output02= $cgst_percent;
    $output002= $cgst;
}
if ($sgst_percent != '') {
   // $output03= array($sgst_percent.' :', number_format($sgst, 2));
    $output03= $sgst_percent;
    $output003= $sgst;
}
if ($igst_percent != '') {
    //$output02= array($igst_percent.' :', number_format($igst, 2));
    $output02= $igst_percent;
    $output002= $igst;
}
if ($utgst_percent != '') {
  //  $output03= array($utgst_percent.' :', number_format($utgst, 2));
  $output03= $utgst_percent;
  $output003= $utgst;
}

// Calculate and write round off amount
$round_off = round($tax_amount + $total + $frieght_charges) - ($tax_amount + $total + $frieght_charges);
//$output04= array('ROUND OFF :', number_format($round_off, 2));

// Calculate and write grand total
$grand_total = $tax_amount + $total + $frieght_charges + $round_off;
//$output05= array('GRAND TOTAL :', number_format($grand_total, 2));

// Appending HTML table data to $output variable
if ($billing_type == 'Billing') {
$output .= "STYLE No,: $style_no".",".",".",".","."AMOUNT :".",".$total."\n";
$output .= "COMBO,: $combo".",".",".",".",".$output02.",".$output002."\n";
$output .= "DESIGN NAME,: $style_name".",".",".",".",".$output03.",".$output003."\n";
$output .= "PARTY DC No,: $inward_dc_no".",".",".","."ROUND OFF :".",".$round_off."\n";
$output .= "Po No,: $cust_po_no".",".",".",".","."GRAND TOTAL :".",".$grand_total."\n";
}else {
$output .= "STYLE No,: $style_no\n";
$output .= "COMBO,: $combo\n";
$output .= "DESIGN NAME,: $style_name\n";
$output .= "PARTY DC No,: $inward_dc_no\n";
}

$output .="\n";

    $date=date('d-m-Y H:i:s');
    $filename =  "Billing Individual Report". $date.".csv";
    header('Content-type: application/xls');
    header('Content-Disposition: attachment; filename='.$filename);
    echo $output;
} else {
    echo "Error in fetching data.";
}

// if ($billing_type == 'Billing') {
// $output = fopen('php://output', 'w');

// // Initialize variables
// $tax_amount = 0;
// $cgst = 0;
// $sgst = 0;
// $igst = 0;
// $utgst = 0;

// // Calculate tax amounts based on tax type
// if ($tax_type == 'GST 18%') {
//     $cgst = $sgst = ($total + $frieght_charges) * (9/100);
//     $tax_amount = $cgst + $sgst;
//     $cgst_percent = 'CGST 9%';
//     $sgst_percent = 'SGST 9%';
// } elseif ($tax_type == 'GST 5%') {
//     $cgst = $sgst = ($total + $frieght_charges) * (2.5/100);
//     $tax_amount = $cgst + $sgst;
//     $cgst_percent = 'CGST 2.5%';
//     $sgst_percent = 'SGST 2.5%';
// } elseif ($tax_type == 'GST 12%') {
//     $cgst = $sgst = ($total + $frieght_charges) * (6/100);
//     $tax_amount = $cgst + $sgst;
//     $cgst_percent = 'CGST 6%';
//     $sgst_percent = 'SGST 6%';
// } elseif ($tax_type == 'IGST') {
//     $igst = ($total + $frieght_charges) * (5/100);
//     $tax_amount = $igst;
//     $igst_percent = 'IGST 5%';
// } elseif ($tax_type == 'IGST 18%') {
//     $igst = ($total + $frieght_charges) * (18/100);
//     $tax_amount = $igst;
//     $igst_percent = 'IGST 18%';
// } elseif ($tax_type == 'UTGST') {
//     $utgst = ($total + $frieght_charges) * (18/100);
//     $tax_amount = $utgst;
//     $utgst_percent = 'UTGST 18%';
// }
// fputcsv($output, array('AMOUNT', '', number_format($total, 2)));
// // Write tax rows
// if ($cgst_percent != '') {
//     fputcsv($output, array($cgst_percent, '', number_format($cgst, 2)));
// }
// if ($sgst_percent != '') {
//     fputcsv($output, array($sgst_percent, '', number_format($sgst, 2)));
// }
// if ($igst_percent != '') {
//     fputcsv($output, array($igst_percent, '', number_format($igst, 2)));
// }
// if ($utgst_percent != '') {
//     fputcsv($output, array($utgst_percent, '', number_format($utgst, 2)));
// }

// // Calculate and write round off amount
// $round_off = round($tax_amount + $total + $frieght_charges) - ($tax_amount + $total + $frieght_charges);
// fputcsv($output, array('ROUND OFF', '', number_format($round_off, 2)));

// // Calculate and write grand total
// $grand_total = $tax_amount + $total + $frieght_charges + $round_off;
// fputcsv($output, array('GRAND TOTAL', '', number_format($grand_total, 2)));

// // Close the file pointer
// fclose($output);
// }
exit;

function get_dc_no($jobcard_no, $particulars) {
    $sql_user= "select GROUP_CONCAT(delivery_no) as delivery_no  from delivery_challan_sub where job_no='$jobcard_no' and particulars='$particulars' union select delivery_no from sticker_delivery_challan_sub where job_no='$jobcard_no' and particulars='$particulars' ";
	$rs_user=mysql_query($sql_user);
	while($rsdata_user=mysql_fetch_object($rs_user))
	{ 
	$delivery_no=$rsdata_user->delivery_no;
	}
	return $delivery_no;
}

function get_estimate_type($jobcard_no1)
  {
	  $sql_user="select billing_type as billing_type from fusing_production_main where job_card_no='$jobcard_no1' and billing_type!=''  union select billing_type as billing_type from printer_production_main where job_card_no='$jobcard_no1' and billing_type!=''";
	$rs_user=mysql_query($sql_user);
	while($rsdata_user=mysql_fetch_object($rs_user))
	{ 
	$billing_type=$rsdata_user->billing_type;
	}
	return $billing_type;
 } 
 
 function get_invoice_no($jobcard_no1)
  {
	$sql_user="select invoice_no as invoice_no from fusing_production_main where job_card_no='$jobcard_no1' and invoice_no!='' union select invoice_no as invoice_no from printer_production_main where job_card_no='$jobcard_no1' and invoice_no!=''";
	$rs_user=mysql_query($sql_user);
	while($rsdata_user=mysql_fetch_object($rs_user))
	{ 
	$invoice_no=$rsdata_user->invoice_no;
	}
	return $invoice_no;
 } 
 
 function get_created_dates($jobcard_no1)
  {
	$sql_user="select bill_date as bill_date from fusing_production_main where job_card_no='$jobcard_no1' and invoice_no!='' union select bill_date as bill_date from printer_production_main where job_card_no='$jobcard_no1' and invoice_no!=''";
	$rs_user=mysql_query($sql_user);
	while($rsdata_user=mysql_fetch_object($rs_user))
	{ 
	$bill_date=$rsdata_user->bill_date;
	}
	return $bill_date;
 } 
 
 function get_estimate_type11($jobcard_no1)
  {
	  $sql_user="select billing_type as billing_type,remarks as remarks,description as description from fusing_production_main where job_card_no='$jobcard_no1' and billing_type!=''  union select billing_type as billing_type,remarks as remarks,description as description from printer_production_main where job_card_no='$jobcard_no1' and billing_type!=''";
	$rs_user=mysql_query($sql_user);
	while($rsdata_user=mysql_fetch_object($rs_user))
	{ 
	$billing_type=$rsdata_user->billing_type;
	$remarks=$rsdata_user->remarks;
	$description=$rsdata_user->description;
	}
	return array($billing_type,$remarks,$description);
 } 
 
 function get_inward_dc_no($jobcard_nos)
  {
    $sql_user1="select customer_dc_no from inward_main where jobcard_no='$jobcard_nos' ";
 	$rs_user11=mysql_query($sql_user1);
	while($rsdata_user11=mysql_fetch_object($rs_user11))
	{ 
	 $customer_dc_no=$rsdata_user11->customer_dc_no.",";
	}
 return $customer_dc_no;
 }
 
 function get_tax_type($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_freight_charges($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;
 } 
?>
