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

	

$jobtype='';
// Get filters
$type_1        = $_GET['type_1'];
$job_card_no   = $_GET['job_card_no'];
$jobcard_from  = $_GET['jobcard_from'];
$jobcard_to    = $_GET['jobcard_to'];
$buyer_name    = $_GET['buyer_name'];
$jobtype       = $_GET['jobtype'];
$from_date     = $_GET['from_date'];
$to_date       = $_GET['to_date'];
$cur_date      = date("Y-m-d");

// ------------------------
// Build Media Sublist Filter
// ------------------------
$mediaFilters = [];

if ($from_date != "") {
    $mediaFilters[] = "DATE_FORMAT(b.media_date,'%Y-%m-%d') >= '$from_date'";
} else {
    $mediaFilters[] = "DATE_FORMAT(b.media_date,'%Y-%m-%d') = '$cur_date'";
}

if ($to_date != "") {
    $mediaFilters[] = "DATE_FORMAT(b.media_date,'%Y-%m-%d') <= '$to_date'";
} else {
    $mediaFilters[] = "DATE_FORMAT(b.media_date,'%Y-%m-%d') = '$cur_date'";
}

if ($job_card_no != "") {
    $mediaFilters[] = "b.jobcard_no = '$job_card_no'";
}

if ($jobcard_from != "" && $jobcard_to != "") {
    $mediaFilters[] = "b.jobcard_no BETWEEN '$jobcard_from' AND '$jobcard_to'";
}

if ($buyer_name != "") {
    $mediaFilters[] = "b.in_cust_name = '$buyer_name'";
}

if ($jobtype != "") {
    $mediaFilters[] = "job_types = '$jobtype'";
}

$mediaWhere = implode(" AND ", $mediaFilters);

// ------------------------
// Build Jobcard Sublist Filter
// ------------------------
$jobcardFilters = [];

if ($from_date != "") {
    $jobcardFilters[] = "DATE_FORMAT(a.entry_time,'%Y-%m-%d') >= '$from_date'";
} else {
    $jobcardFilters[] = "DATE_FORMAT(a.entry_time,'%Y-%m-%d') = '$cur_date'";
}

if ($to_date != "") {
    $jobcardFilters[] = "DATE_FORMAT(a.entry_time,'%Y-%m-%d') <= '$to_date'";
} else {
    $jobcardFilters[] = "DATE_FORMAT(a.entry_time,'%Y-%m-%d') = '$cur_date'";
}

if ($job_card_no != "") {
    $jobcardFilters[] = "a.jobcard_no = '$job_card_no'";
}

if ($jobcard_from != "" && $jobcard_to != "") {
    $jobcardFilters[] = "a.jobcard_no BETWEEN '$jobcard_from' AND '$jobcard_to'";
}

if ($buyer_name != "") {
    $jobcardFilters[] = "a.customer_name = '$buyer_name'";
}

if ($jobtype != "") {
    $jobcardFilters[] = "job_types = '$jobtype'";
}

$jobcardWhere = implode(" AND ", $jobcardFilters);


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 ($job_card_no != "") {
    $job_card_no1 = $job_card_no; // single jobcard
} elseif ($jobcard_from != "" && $jobcard_to != "") {
    $job_card_no1 = $jobcard_from . " to " . $jobcard_to; // range display
} elseif ($jobcard_from != "") {
    $job_card_no1 = "From " . $jobcard_from; // only from
} elseif ($jobcard_to != "") {
    $job_card_no1 = "Up to " . $jobcard_to; // only to
} else {
    $job_card_no1 = "ALL"; // no filter
}
if($buyer_name!=""){ $buyer_name11=$buyer_name;} else {$buyer_name11="ALL" ;}
if($jobtype!=""){ $jobtype1=$jobtype;} else {$jobtype1="ALL" ;}

$blog=array('','Media Pending Report','','');


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

$output .="\n";
$output .="\n"; 
$output .="From Date".",".$from_date21.","."To Date".",".$to_date21.","."Job Type".",".$jobtype1.","."Jobcard No".",".$job_card_no1.","."Buyer Name".",".$buyer_name11."\n"; 
$output .="\n"; 

 

 
     $output .='"SNo","Date","Job Type","Jobcard No","Buyer Name","Particulars","Order Qty","Plan Qty","Pending Qty",'."\n";
      $table_values_new=array(ItemGroupName);
	 $table_values=array(SNo,Date,JobType,JobcardNo,BuyerName,Particulars,OrderQty,PlanQty,PendingQty);
   
   
	 $sql = "
SELECT 
    a.entry_time AS media_date,
    a.particulars AS media_particular,
    a.customer_name AS in_cust_name,
    a.jobcard_no AS jobcard_no,
    a.qty AS order_qty,
    0 AS plan_qty,
    0 AS totalplanqty
FROM 
    jobcard_sublist AS a
LEFT JOIN 
    media_sub_list AS b ON a.particulars = b.media_particular
WHERE 
    $jobcardWhere AND b.jobcard_no IS NULL
GROUP BY 
    a.particulars, a.entry_time, a.customer_name, a.jobcard_no, a.qty

UNION

SELECT 
    b.media_date AS media_date,
    b.media_particular AS media_particular,
    b.in_cust_name AS in_cust_name,
    b.jobcard_no AS jobcard_no,
    b.order_qty AS order_qty,
    b.plan_qty AS plan_qty,
    SUM(b.plan_qty) AS totalplanqty
FROM 
    media_sub_list AS b
WHERE 
    $mediaWhere 
GROUP BY 
    b.media_date, b.media_particular, b.in_cust_name, b.jobcard_no, b.order_qty, b.plan_qty
";
	 


    $rs=mysql_query($sql);
	 $rscount=mysql_num_rows($rs);
	if($rscount!='')
	{
	$s_no=0;
   		while($rsdata=mysql_fetch_array($rs))
		{
			$media_date=$rsdata['media_date'];
			$media_particular=$rsdata['media_particular'];
			$customer_name=$rsdata['in_cust_name'];
			  $jobcard_no=$rsdata['jobcard_no'];
			 
			$order_qty=$rsdata['order_qty'];
			$plan_qty=$rsdata['plan_qty'];
			$totalplanqty =$rsdata['totalplanqty']; 
			$pending_qty=$order_qty-$plan_qty;
			  $newpendingqty = $order_qty - $rsdata['totalplanqty'];  
			
		//  if($pending_qty!=0){ 
			 $i=$i+1;
			 
			foreach ($table_values as $val) 
			{
				   
				        	
				if($val=='SNo'){$output.='"'.$i.'",';}
				if($val=='Date'){$output.='"'.date("d-m-Y",strtotime($media_date)).'",';}
				if($val=='JobType'){$output.='"'.$jobtype.'",';}
				if($val=='JobcardNo'){$output.='"'.$jobcard_no.'",';}
				if($val=='BuyerName'){$output.='"'.$customer_name.'",';}
				if($val=='Particulars'){$output.='"'.$media_particular.'",';}
				if($val=='OrderQty'){$output.='"'.number_format($order_qty,2).'",';}
				if($val=='PlanQty'){$output.='"'.number_format($totalplanqty,2).'",';}
				if($val=='PendingQty'){$output.='"'.number_format($pending_qty,2).'",';}
				
				   
				

if(($val!='SNo')&&($val!='Date')&&($val!='OB')&&($val!='JobType')&&($val!='JobcardNo')&&($val!='BuyerName')&&($val!='Particulars')&&($val!='OrderQty')&&($val!='PlanQty')&&($val!='PendingQty')){$output .='"'.$val.'",';}
} 
 $output .="\n";
		
 $sum_tot1+=$order_qty;
 $sum_tot2+=$totalplanqty;
 $sum_tot3+=$newpendingqty;
		//  }		    
 
}
 
$output .="".",".",".",".",".",".Total.",".$sum_tot1.",".$sum_tot2.",".$sum_tot3."\n\n";
}
$date=date('d-m-Y H:i:s');
$filename =  "Media Pending 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>