
use sakti;
DROP PROCEDURE IF EXISTS `sakti`.`getDetailBarangTkdn`;

DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `sakti`.`getDetailBarangTkdn`(`PHEADER` INT, `PJENIS` INT) RETURNS longtext CHARSET latin1
    DETERMINISTIC
BEGIN
	DECLARE VTEXT LONGTEXT;

SET @VTEXT2 = (
	SELECT
		GROUP_CONCAT(CONCAT(
			IF(s.KODE_BARANG IS NULL,"",s.KODE_BARANG), "|"
			, s.HARGA_SATUAN, "|"
			, s.JUMLAH_BARANG, "|"
			, s.KODE_UAKPB, "|"
			, IF(s.KET_TKDN IS NULL,'-',s.KET_TKDN), "|"
			, REPLACE(ROUND(s2.NILAI_TKDN, 2), '.', ','), "|"
			, s2.JENIS_TKDN
		) SEPARATOR "\r\n")
		
	FROM sakti.sakti_barang_detail s
	, sakti.sakti_barang_detail s2
	WHERE s2.ID = s.REF_ID_ASAL AND s.HEADER = PHEADER 
	AND s.REF_ID_ASAL > 0);

	SELECT
	CONCAT(
		GROUP_CONCAT(CONCAT(
			IF(s.KODE_BARANG IS NULL,"",s.KODE_BARANG), "|"
			, s.HARGA_SATUAN, "|"
			, s.JUMLAH_BARANG, "|"
			, s.KODE_UAKPB, "|"
			, s.KET_TKDN, "|"
			, REPLACE(ROUND(s.NILAI_TKDN, 2), '.', ','), "|"
			, s.JENIS_TKDN
		) SEPARATOR "\r\n")
	, IF(@VTEXT2 IS NULL,'',CONCAT("\r\n",@VTEXT2))
	)INTO VTEXT
	FROM sakti.sakti_barang_detail s
	WHERE s.HEADER = PHEADER 
	AND (s.JENIS_TKDN > 0 OR s.NILAI_TKDN > 0);
	

	RETURN VTEXT;
END//
DELIMITER ;