gistfile1.txt

<?php
$param = [];
if(Input::get('doc_type') == 1 || Input::get('doc_type') == 2 || Input::get('doc_type') == 3 || Input::get('doc_type') == 4){
	$type = Input::get('doc_type');
	$q = "and doc_type_id = ?";
	$param[] = $type;
}
else{
	$type = '';
	$q = "";
}
		
if(Input::get('doc_no_from')){
	$doc_no_from = Input::get('doc_no_from');
	$q1 = "tbl.seq >= ? ";
	$param[] = $doc_no_from;
}
else{
	$doc_no_from = 0;
	$q1 = "";
}

if(Input::get('doc_no_to')){
	$doc_no_to = Input::get('doc_no_to');
	$param[] = $doc_no_to;
	if($q1 == ""){
		$q1 = "tbl.seq <= ?";
	}
	else{
		$q1 = " and tbl.seq <= ?";
	}
}
else{
	$doc_no_to = 0;
	$q1 = "";
}


		$headers = DB::select("
								
								SELECT * from 
								(
									SELECT id as seq from sequence_numbers
								)as tbl
								LEFT JOIN
								(
								SELECT j.doc_type_id, j.doc_type, partners.name,CAST(j.doc_no as UNSIGNED) as doc_no, j.date, j.partner_id, j.amount, j.gov, j.vat, j.nv, j.zr, j.nt, j.ewt
																	FROM (
																	(SELECT receipt_app_attachments.doc_type_id as doc_type_id, 'RCT' as doc_type,
																	       CAST(receipt_app_attachments.doc_no as UNSIGNED)  as doc_no, receipt_app_attachments.attachment_date as date, postings.partner_id as partner_id,
																				 receipt_app_attachments.amount as amount, 
																			   SUM(IF(posting_sales.sales_type='gov',posting_sales.amount,NULL)) AS gov,
																		     SUM(IF(posting_sales.sales_type='vat',posting_sales.amount,NULL)) AS vat,
																				 SUM(IF(posting_sales.sales_type='nv',posting_sales.amount,NULL)) AS nv,
																	       SUM(IF(posting_sales.sales_type='zr',posting_sales.amount,NULL)) AS zr,
																	       SUM(IF(posting_sales.sales_type='nt',posting_sales.amount,NULL)) AS nt, 
																				 sum(posting_sales.ewt) as ewt
																	from app_headers 
																	left outer join receipt_app_attachments on app_headers.id = receipt_app_attachments.ref_header_id
																	left join postings on receipt_app_attachments.id = postings.attachment_no
																	left join posting_sales on postings.id = posting_sales.posting_id
																	where app_headers.status in ('Approved','Printed')
																	and postings.doc_type = 'ReceiptApp' 
																	and postings.status = 'Posted' and postings.posting_type = 'Sales'
																	GROUP BY receipt_app_attachments.doc_no)
																	UNION ALL
																	(SELECT bill_attachments.doc_type_id  as doc_type_id, 'Bill' as doc_type,
																	       CAST(bill_attachments.doc_no as UNSIGNED) as doc_no, bill_attachments.attachment_date as date, postings.partner_id as partner_id,
																				 bill_attachments.amount as amount, 
																			   SUM(IF(posting_sales.sales_type='gov',posting_sales.amount,NULL)) AS gov,
																		     SUM(IF(posting_sales.sales_type='vat',posting_sales.amount,NULL)) AS vat,
																				 SUM(IF(posting_sales.sales_type='nv',posting_sales.amount,NULL)) AS nv,
																	       SUM(IF(posting_sales.sales_type='zr',posting_sales.amount,NULL)) AS zr,
																	       SUM(IF(posting_sales.sales_type='nt',posting_sales.amount,NULL)) AS nt, 
																				 sum(posting_sales.ewt) as ewt
																	from bill_headers 
																	left outer join bill_attachments on bill_headers.id = bill_attachments.ref_header_id
																	left join postings on bill_attachments.id = postings.attachment_no
																	left join posting_sales on postings.id = posting_sales.posting_id
																	where bill_headers.status in ('Approved','Printed')
																	and postings.doc_type = 'Bill' and postings.status = 'Posted' and postings.posting_type = 'Sales'
																	GROUP BY bill_attachments.doc_no)
																	UNION ALL
																	(
																		select '4' as doc_type_id, 'JV' as doc_type, postings.doc_no as doc_no, postings.post_date as date, postings.partner_id as partner_id,
																	jv_headers.dbtotal as amount,
																	SUM(IF(posting_sales.sales_type='gov',posting_sales.amount,NULL)) AS gov,
																		     SUM(IF(posting_sales.sales_type='vat',posting_sales.amount,NULL)) AS vat,
																				 SUM(IF(posting_sales.sales_type='nv',posting_sales.amount,NULL)) AS nv,
																	       SUM(IF(posting_sales.sales_type='zr',posting_sales.amount,NULL)) AS zr,
																	       SUM(IF(posting_sales.sales_type='nt',posting_sales.amount,NULL)) AS nt, 
																				 sum(posting_sales.ewt) as ewt
																	  from jv_headers
																	LEFT JOIN  postings on jv_headers.id = postings.doc_no
																	left join posting_sales on postings.id = posting_sales.posting_id
																	where postings.posting_type = 'Sales' and postings.doc_type = 'JV' and postings.status = 'Posted' and jv_headers.status in('Approved','Printed')
																	)
																	UNION ALL
																	(
																	select '4' as doc_type_id, 'Memo' as doc_type, postings.doc_no as doc_no, postings.post_date as date, postings.partner_id as partner_id,
																	memo_app_headers.total as amount,
																	SUM(IF(posting_sales.sales_type='gov',posting_sales.amount,NULL)) AS gov,
																		     SUM(IF(posting_sales.sales_type='vat',posting_sales.amount,NULL)) AS vat,
																				 SUM(IF(posting_sales.sales_type='nv',posting_sales.amount,NULL)) AS nv,
																	       SUM(IF(posting_sales.sales_type='zr',posting_sales.amount,NULL)) AS zr,
																	       SUM(IF(posting_sales.sales_type='nt',posting_sales.amount,NULL)) AS nt, 
																				 sum(posting_sales.ewt) as ewt
																	  from memo_app_headers
																	LEFT JOIN  postings on memo_app_headers.id = postings.doc_no
																	left join posting_sales on postings.id = posting_sales.posting_id
																	where postings.posting_type = 'Sales' and postings.doc_type in ('DMA','CMA') and postings.status = 'Posted' and memo_app_headers.status in('Approved','Printed')
																	)
																	) as j
																	LEFT JOIN partners on j.partner_id = partners.id
																	where j.doc_no IS NOT NULL ".$q."
																	ORDER BY j.doc_no asc
								) as tbl1 on tbl.seq = tbl1.doc_no
								where ".$q1."
								ORDER BY tbl.seq asc 
							",$param);