<?php
// error_reporting(E_ALL);
// ini_set('display_errors', '1');
	session_start();
	require "_config.php";

	
	require('fpdf/fpdf.php');
	
	
	class PDF extends FPDF{
		
		function Header(){
			$this->SetFont('Arial','',10);
			 if ($this->PageNo() == 1 ) {
				 #Company Name
				$this->SetY(18);
				$this->SetX(0);
				$this->SetFont('Arial','B',16);
				$this->Cell(0,6,'SARAYUENGINEERING',0,1,'C');
			 }
		}
		
		function Body($data){
			$this->Ln(10);
			$fontSize=10;
			$cellHeight= 10;
			$this->SetFont('Arial','B',$fontSize);
			
			$this->Cell(15,$cellHeight,"SL NO","TRL",0,'C');
			$this->Cell(85,$cellHeight,"MATERIAL PARTICULARS","TR",0,'C');
			$this->Cell(62,$cellHeight,"OPENING","TRB",0,'C');
			$this->Cell(62,$cellHeight,"PURCHASE","TRB",0,'C');
			$this->Cell(62,$cellHeight,"TOTAL","TRB",0,'C');
			$this->Cell(62,$cellHeight,"CONSUMPTION","TRB",0,'C');
			$this->Cell(62,$cellHeight,"CLOSING","TRB",1,'C');
			
			
			$this->Cell(15,$cellHeight,"","RBL",0,'C');
			$this->Cell(85,$cellHeight,"","RB",0,'C');
			$this->Cell(31,$cellHeight,"QTY","TRB",0,'C');
			$this->Cell(31,$cellHeight,"VALUE","TRB",0,'C');
			$this->Cell(31,$cellHeight,"QTY","TRB",0,'C');
			$this->Cell(31,$cellHeight,"VALUE","TRB",0,'C');
			$this->Cell(31,$cellHeight,"QTY","TRB",0,'C');
			$this->Cell(31,$cellHeight,"VALUE","TRB",0,'C');
			$this->Cell(31,$cellHeight,"QTY","TRB",0,'C');
			$this->Cell(31,$cellHeight,"VALUE","TRB",0,'C');
			$this->Cell(31,$cellHeight,"QTY","TRB",0,'C');
			$this->Cell(31,$cellHeight,"VALUE","TRB",1,'C');
			
			$total = [0,0,0,0,0];
			$qty = [0,0,0,0,0];
			
			$this->SetFont('Arial','',$fontSize);
			for($i=0;$i<count($data["rows"]);$i++){
				$row = $data["rows"][$i];
				$ledger = $data["ledger"][$row["pid"]];
				$this->Cell(15,$cellHeight,$i+1,"TRBL",0,'C');
				$this->Cell(85,$cellHeight,$row["cname"],"TRB",0,'L');
				
				$this->Cell(31,$cellHeight,$ledger["opn"]!=0?$ledger["opn"]:"","TRB",0,'R');
				$this->Cell(31,$cellHeight,$ledger["opn_val"]!=0?$ledger["opn_val"]:"","TRB",0,'R');
				
				$qty[0]+=$ledger["opn"];
				$total[0]+=$ledger["opn_val"];
				
				$this->Cell(31,$cellHeight,$ledger["pur"]!=0?$ledger["pur"]:"","TRB",0,'R');
				$this->Cell(31,$cellHeight,$ledger["pur_val"]!=0?$ledger["pur_val"]:"","TRB",0,'R');
				
				$qty[1]+=$ledger["pur"];
				$total[1]+=$ledger["pur_val"];
				
				$this->Cell(31,$cellHeight,$ledger["opn"]+$ledger["pur"]!=0?$ledger["opn"]+$ledger["pur"]:"","TRB",0,'R');
				$this->Cell(31,$cellHeight,$ledger["opn_val"]+$ledger["pur_val"]!=0?$ledger["opn_val"]+$ledger["pur_val"]:"","TRB",0,'R');
				
				$qty[2]+=$ledger["pur"]+$ledger["opn"];
				$total[2]+=$ledger["opn_val"]+$ledger["pur_val"];
				
				$qty[3]+=$ledger["sal"];
				$this->Cell(31,$cellHeight,$ledger["sal"]!=0?$ledger["sal"]:"","TRB",0,'R');
				$this->Cell(31,$cellHeight,$ledger["sal_val"]!=0?$ledger["sal_val"]:"","TRB",0,'R');
				
				$total[3]+=$ledger["sal_val"];
				
				$this->Cell(31,$cellHeight,$ledger["cls"]!=0?$ledger["cls"]:"","TRB",0,'R');
				$this->Cell(31,$cellHeight,$ledger["cls_val"]!=0?$ledger["cls_val"]:"","TRB",1,'R');
				
				$qty[4]+=$ledger["cls"];
				$total[4]+=$ledger["cls_val"];
				
				
			}
			$this->SetFont('Arial','B',$fontSize);
			$this->Cell(15,$cellHeight,"","TRBL",0,'C');
			$this->Cell(85,$cellHeight,"TOTAL","TRB",0,'C');
			$this->Cell(31,$cellHeight,format($qty[0]),"TRB",0,'R');
			$this->Cell(31,$cellHeight,format($total[0]),"TRB",0,'R');
			$this->Cell(31,$cellHeight,format($qty[1]),"TRB",0,'R');
			$this->Cell(31,$cellHeight,format($total[1]),"TRB",0,'R');
			$this->Cell(31,$cellHeight,format($qty[2]),"TRB",0,'R');
			$this->Cell(31,$cellHeight,format($total[2]),"TRB",0,'R');
			$this->Cell(31,$cellHeight,format($qty[3]),"TRB",0,'R');
			$this->Cell(31,$cellHeight,format($total[3]),"TRB",0,'R');
			$this->Cell(31,$cellHeight,format($qty[4]),"TRB",0,'R');
			$this->Cell(31,$cellHeight,format($total[4]),"TRB",1,'R');
		}
	}
	  
  
	//Create object of inherited class
	$pdf=new PDF("L","mm",array(279.4,431.8)); 
   
	$type = urldecode($_GET["type"]);
	$sql="select p.cid,p.pid,c.cname as cname from tbl_products p inner join tbl_category c on p.cid=c.cid WHERE ctype='{$type}' group by p.cid order by cname";
	
			
	$data["rows"] = resultSet($con,$sql);
	$fdate = $_GET["fdate"];
	$tdate = $_GET["tdate"];
	
	$data["ledger"] = [];
	foreach($data["rows"] as $row){
		
		$opn = single($con,"select sum(dr)-sum(cr) as opn,sum(if(ttype='PURCHASE',total,0)) as total_dr,sum(if(ttype='SALES',total,0)) as total_cr from `{$_SESSION["fyear"]}_tbl_transactions` t inner join tbl_products p on t.pid=p.pid where tdate<'{$fdate}' and  cid='{$row["cid"]}'");
		
		
		//$opn_rate = single($con,"select round(sum(rate)/count(rate),2) as rate from tbl_transactions t inner join tbl_products p on t.pid=p.pid where tdate<'{$fdate}' and  cid='{$row["cid"]}' and ttype='PURCHASE'");
		 
		 
		$pur = single($con,"select sum(dr) as opn,sum(total) as total from `{$_SESSION["fyear"]}_tbl_transactions` t inner join tbl_products p on t.pid=p.pid where tdate>='{$fdate}' and tdate<='{$tdate}' and  cid='{$row["cid"]}' and ttype='PURCHASE'");
		
	
		
		$sal = single($con,"select sum(cr) as opn,sum(total) as total from `{$_SESSION["fyear"]}_tbl_transactions` t inner join tbl_products p on t.pid=p.pid where tdate>='{$fdate}' and tdate<='{$tdate}' and  cid='{$row["cid"]}' and ttype='SALES'");
		 
		$cls = single($con,"select sum(dr)-sum(cr) as opn,sum(if(ttype='PURCHASE',total,0)) as total_dr,sum(if(ttype='SALES',total,0)) as total_cr from `{$_SESSION["fyear"]}_tbl_transactions` t inner join tbl_products p on t.pid=p.pid where tdate<='{$tdate}' and  cid='{$row["cid"]}'");
		
		//$cls_rate = single($con,"select round(sum(rate)/count(rate),2) as rate from tbl_transactions t inner join tbl_products p on t.pid=p.pid where tdate<='{$tdate}' and  cid='{$row["cid"]}' and ttype='PURCHASE'");
		 
		
		$data["ledger"][$row["pid"]]=[
			"opn"=>round($opn["opn"],2),
			"opn_val"=>round($opn["total_dr"]-$opn["total_cr"],2),
			"pur"=>round($pur["opn"],2),
			"pur_val"=>round($pur["total"],2),
			"sal"=>round($sal["opn"],2),
			"sal_val"=>round($sal["total"],2),
			"cls"=>round($cls["opn"],2),
			"cls_val"=>round($cls["total_dr"]-$cls["total_cr"],2),
		];
	}
	
	//Call this function for special value {nb} (total number of pages) 
	$pdf->AliasNbPages();
	$pdf->AddPage();
	$pdf->Body($data);
	$pdf->SetTitle('SARAYUENGINEERING'); 
	$pdf->SetFont('Arial','',15);
	$pdf->Output(); 
?>