DELIMITER $$


DROP PROCEDURE IF EXISTS `spaccreport15_per_lokasi`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spaccreport15_per_lokasi`(pwarehouse_type VARCHAR(10), pwarehouse VARCHAR(30), plocation VARCHAR(30),pstock VARCHAR(30),pdate_from DATE,pdate_end DATE)
BEGIN
	SELECT  m7.ftcompany_name,n1.ftitem_code,n1.ftpart_no,
		CASE WHEN TRIM(IFNULL(n1.ftpart_no,'') != '') THEN CONCAT(n1.ftitem_name,' PART NO: ',IFNULL(n1.ftpart_no,'')) ELSE n1.ftitem_name END AS ftitem_name,		
		n2.ftlocation_code,n2.ftlocation_name,n5.fnwarehouse_type, m6.fttrans_status_name,
		IFNULL(m4.fnsaldo,0) AS fnsaldo_awal,
		IFNULL(m5.fnmasuk,0) AS fnmasuk,
		IFNULL(m5.fnkeluar,0) AS fnkeluar,
		IFNULL(m4.fnsaldo,0)+IFNULL(m5.fnmasuk,0)-IFNULL(m5.fnkeluar,0) AS fnsaldo_akhir,
		CASE WHEN (n5.fnwarehouse_type != 5) THEN b.fcbuying_price ELSE 0 END AS fcbuying_price, 
		CASE WHEN (n5.fnwarehouse_type != 5) THEN ((IFNULL(m4.fnsaldo,0)+IFNULL(m5.fnmasuk,0)-IFNULL(m5.fnkeluar,0)) * b.fcbuying_price) ELSE 0 END AS fctotal_price
	FROM tlstock_hdr n1 
	INNER JOIN tlstock_det n3 ON n1.ftitem_code=n3.ftitem_code 
	INNER JOIN tllocation n2 ON n3.ftlocation_code=n2.ftlocation_code
	INNER JOIN tlwarehouse n5 ON n5.ftwarehouse_code = n2.ftwarehouse_code
	-- INNER JOIN tlbuying_price b ON n1.ftitem_code = b.ftitem_code AND ftcurrency_code = 'IDR'
	INNER JOIN (	
			SELECT ftitem_code, SUM(fnqty) AS fnqty, fcnet_price AS fcbuying_price 
			FROM txstockfifo WHERE fnstatus <> 9
			GROUP BY ftitem_code, fcnet_price
		) b ON n1.ftitem_code = b.ftitem_code 
		
	LEFT JOIN (	
		
		SELECT m.ftitem_code,m.ftwarehouse_code,m.ftlocation_code,SUM(m.fnmasuk) AS fnmasuk,SUM(m.fnkeluar) AS fnkeluar,SUM(m.fnmasuk)-SUM(m.fnkeluar) AS fnsaldo
		FROM (
			SELECT b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,SUM(fnqty_base) AS fnmasuk,0 AS fnkeluar
				FROM txinnitial_stock_hdr a,txinnitial_stock_det b
				WHERE a.ftstock_in_no=b.ftstock_in_no 					
					AND a.fnstatus<>3 AND a.fnstatus<>9
					GROUP BY b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
			UNION ALL 
			
			SELECT b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,SUM(fnqty_base) AS fnmasuk,0 AS fnkeluar
			FROM txstock_in_hdr a,txstock_in_det b
			WHERE a.ftstock_in_no=b.ftstock_in_no 
				AND a.fnstatus<>3 AND a.fnstatus<>9 AND a.ftstock_type_code='inv' AND 
				a.fdtrans_date<pdate_from
			GROUP BY b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
			
			UNION ALL
			SELECT b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,0 AS fnmasuk,SUM(fnqty_base)  AS fnkeluar
			FROM txstock_out_hdr a,txstock_out_det b
			WHERE a.ftstock_out_no=b.ftstock_out_no 
				AND a.fnstatus<>3 AND a.fnstatus<>9  AND
				a.fdtrans_date<pdate_from
			GROUP BY b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code			
			
			UNION ALL
			SELECT b.ftitem_code,b.ftwarehouse_from AS ftwarehouse_code,b.ftlocation_from AS ftlocation_code,0 AS fnmasuk,SUM(fnqty_base) AS fnkeluar
			FROM txstock_mutation_hdr a,txstock_mutation_det b
			WHERE a.ftstock_mutation_no=b.ftstock_mutation_no 
				AND a.fnstatus<>3 AND a.fnstatus<>9 AND
				a.fdtrans_date<pdate_from
			GROUP BY  b.ftitem_code,b.ftwarehouse_from,b.ftlocation_from 
			
			UNION ALL
			SELECT b.ftitem_code,b.ftwarehouse_to AS ftwarehouse_code,b.ftlocation_to AS ftlocation_code,SUM(fnqty_base)  AS fnmasuk,0 AS fnkeluar
			FROM txstock_mutation_hdr a,txstock_mutation_det b
			WHERE a.ftstock_mutation_no=b.ftstock_mutation_no 
				AND a.fnstatus<>3 AND a.fnstatus<>9 AND
				a.fdtrans_date<pdate_from
			GROUP BY b.ftitem_code,b.ftwarehouse_to,b.ftlocation_to
			
			UNION ALL
			SELECT b.ftitem_code,b.ftwarehouse_to AS ftwarehouse_code,b.ftlocation_to AS ftlocation_code,SUM(fnqty_base)  AS fnmasuk,0 AS fnkeluar
			FROM txstock_mutation_hdr_bbtobj a,txstock_mutation_det_bbtobj b
			WHERE a.ftstock_mutation_no=b.ftstock_mutation_no
				AND a.fnstatus<>3 AND a.fnstatus<>9 AND
				a.fdtrans_date<pdate_from
			GROUP BY b.ftitem_code,b.ftwarehouse_to,b.ftlocation_to
			
			UNION ALL
			SELECT b.ftitem_code,b.ftwarehouse_to AS ftwarehouse_code,b.ftlocation_to AS ftlocation_code,SUM(fnqty_base)  AS fnmasuk,0 AS fnkeluar
			FROM txstock_mutation_hdr_sptobj a,txstock_mutation_det_sptobj b
			WHERE a.ftstock_mutation_no=b.ftstock_mutation_no
				AND a.fnstatus<>3 AND a.fnstatus<>9 AND
				a.fdtrans_date<pdate_from
			GROUP BY b.ftitem_code,b.ftwarehouse_to,b.ftlocation_to
				
			UNION ALL
			SELECT b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,
				CASE WHEN SUM(fnqty_base)>0 THEN SUM(fnqty_base) ELSE 0 END AS fnmasuk,
				CASE WHEN SUM(fnqty_base)<0 THEN SUM(fnqty_base)* -1 ELSE 0 END AS fnkeluar
			FROM txstock_adjustment_hdr a,txstock_adjustment_det b
			WHERE a.ftstock_adjustment_no=b.ftstock_adjustment_no AND
				a.fdtrans_date<pdate_from  AND a.fnstatus=2
			GROUP BY  b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
		) m
		GROUP BY m.ftitem_code,m.ftwarehouse_code,m.ftlocation_code
	) m4
	ON n1.ftitem_code=m4.ftitem_code AND n2.ftlocation_code=m4.ftlocation_code
	LEFT JOIN (
				
		SELECT m.ftitem_code,m.ftwarehouse_code,m.ftlocation_code,SUM(m.fnmasuk) AS fnmasuk,SUM(m.fnkeluar) AS fnkeluar,SUM(m.fnmasuk)-SUM(m.fnkeluar) AS fnsaldo
		FROM (
			
			SELECT b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,SUM(fnqty_base) AS fnmasuk,0 AS fnkeluar
			FROM txstock_in_hdr a,txstock_in_det b
			WHERE a.ftstock_in_no=b.ftstock_in_no 
				AND a.fnstatus<>3 AND a.fnstatus<>9 AND a.ftstock_type_code='inv' AND
				a.fdtrans_date>=pdate_from AND a.fdtrans_date<=pdate_end 
			GROUP BY b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
			
			UNION ALL
			SELECT b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,0 AS fnmasuk,SUM(fnqty_base)  AS fnkeluar
			FROM txstock_out_hdr a,txstock_out_det b
			WHERE a.ftstock_out_no=b.ftstock_out_no 
				AND a.fnstatus<>3 AND a.fnstatus<>9 AND 	
				a.fdtrans_date>=pdate_from AND a.fdtrans_date<=pdate_end
			GROUP BY b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
			
			
			UNION ALL
			SELECT b.ftitem_code,b.ftwarehouse_from AS ftwarehouse_code,b.ftlocation_from AS ftlocation_code,0 AS fnmasuk,SUM(fnqty_base) AS fnkeluar
			FROM txstock_mutation_hdr a,txstock_mutation_det b
			WHERE a.ftstock_mutation_no=b.ftstock_mutation_no 
				AND a.fnstatus<>3 AND a.fnstatus<>9 AND 	
				a.fdtrans_date>=pdate_from AND a.fdtrans_date<=pdate_end
			GROUP BY  b.ftitem_code,b.ftwarehouse_from,b.ftlocation_from 
			
			UNION ALL
			SELECT b.ftitem_code,b.ftwarehouse_to AS ftwarehouse_code,b.ftlocation_to AS ftlocation_code,SUM(fnqty_base)  AS fnmasuk,0 AS fnkeluar
			FROM txstock_mutation_hdr a,txstock_mutation_det b
			WHERE a.ftstock_mutation_no=b.ftstock_mutation_no 
				AND a.fnstatus<>3 AND a.fnstatus<>9 AND 	
				a.fdtrans_date>=pdate_from AND a.fdtrans_date<=pdate_end
			GROUP BY b.ftitem_code,b.ftwarehouse_to,b.ftlocation_to
			
			UNION ALL
			SELECT b.ftitem_code,b.ftwarehouse_to AS ftwarehouse_code,b.ftlocation_to AS ftlocation_code,SUM(fnqty_base)  AS fnmasuk,0 AS fnkeluar
			FROM txstock_mutation_hdr_bbtobj a,txstock_mutation_det_bbtobj b
			WHERE a.ftstock_mutation_no=b.ftstock_mutation_no
				AND a.fnstatus<>3 AND a.fnstatus<>9 AND
				a.fdtrans_date>=pdate_from AND a.fdtrans_date<=pdate_end
			GROUP BY b.ftitem_code,b.ftwarehouse_to,b.ftlocation_to
			
			UNION ALL
			SELECT b.ftitem_code,b.ftwarehouse_to AS ftwarehouse_code,b.ftlocation_to AS ftlocation_code,SUM(fnqty_base)  AS fnmasuk,0 AS fnkeluar
			FROM txstock_mutation_hdr_sptobj a,txstock_mutation_det_sptobj b
			WHERE a.ftstock_mutation_no=b.ftstock_mutation_no
				AND a.fnstatus<>3 AND a.fnstatus<>9 AND
				a.fdtrans_date>=pdate_from AND a.fdtrans_date<=pdate_end
			GROUP BY b.ftitem_code,b.ftwarehouse_to,b.ftlocation_to
			
			UNION ALL
			SELECT b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,	
				CASE WHEN SUM(fnqty_base)>0 THEN SUM(fnqty_base) ELSE 0 END AS fnmasuk,
				CASE WHEN SUM(fnqty_base)<0 THEN SUM(fnqty_base)* -1 ELSE 0 END AS fnkeluar
			FROM txstock_adjustment_hdr a,txstock_adjustment_det b
			WHERE a.ftstock_adjustment_no=b.ftstock_adjustment_no AND 	
				a.fdtrans_date>=pdate_from AND a.fdtrans_date<=pdate_end  AND a.fnstatus=2
			GROUP BY  b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
		) m
		GROUP BY m.ftitem_code,m.ftwarehouse_code,m.ftlocation_code
	) m5
	ON n1.ftitem_code=m5.ftitem_code   AND n2.ftlocation_code=m5.ftlocation_code 
	LEFT JOIN tstransaction_status m6 ON fntrans_group_code = 55 AND n5.fnwarehouse_type = m6.fntrans_status_code AND m6.fnactive_status = 1 
	CROSS JOIN tscompany_info m7 
	WHERE n1.ftstock_category_code='inv'
		AND CASE WHEN (pwarehouse_type='SELURUH') THEN 1=1 ELSE n5.fnwarehouse_type = pwarehouse_type END
		AND CASE WHEN (pwarehouse='SELURUH') THEN 1=1 ELSE n2.ftwarehouse_code = pwarehouse END
		AND CASE WHEN (plocation='SELURUH') THEN 1=1 ELSE n2.ftlocation_code = plocation END
		AND CASE WHEN (pstock='SELURUH') THEN 1=1 ELSE n1.ftitem_code = pstock END
	GROUP BY m7.ftcompany_name,n1.ftitem_code,n1.ftitem_name,n1.ftpart_no,
		n2.ftlocation_code,n2.ftlocation_name
	HAVING fnsaldo_awal <> 0 OR fnsaldo_akhir <> 0 OR fnmasuk <> 0 OR fnkeluar <> 0;	
	
    END$$

DELIMITER ;