--****************************************************
--*　出力日付の取得
--*　(日付)
--****************************************************
[lselectRecords4]
SELECT DT_DERIVERY_PRODUCT
FROM T_DERIVERY_PRODUCT
WHERE DT_DERIVERY_PRODUCT >= TO_DATE('&8', 'yyyy/mm/dd')
AND DT_DERIVERY_PRODUCT <= TO_DATE('&9', 'yyyy/mm/dd')
and FLG_DELETE = 0
GROUP BY DT_DERIVERY_PRODUCT
ORDER BY DT_DERIVERY_PRODUCT


--*************************************************************************************************************
--*　製品受入データの指定された日数分のデータを取得
--*　(日付・含水重量(Aｸﾞﾚｰﾄﾞ,Bｸﾞﾚｰﾄﾞ,Cｸﾞﾚｰﾄﾞ)・水分(Aｸﾞﾚｰﾄﾞ,Bｸﾞﾚｰﾄﾞ,Cｸﾞﾚｰﾄﾞ)・風乾重量(Aｸﾞﾚｰﾄﾞ,Bｸﾞﾚｰﾄﾞ,Cｸﾞﾚｰﾄﾞ)
--*　　・原材料費(Aｸﾞﾚｰﾄﾞ,Bｸﾞﾚｰﾄﾞ,Cｸﾞﾚｰﾄﾞ)・生産高(Aｸﾞﾚｰﾄﾞ,Bｸﾞﾚｰﾄﾞ,Cｸﾞﾚｰﾄﾞ)・製品運送費(Aｸﾞﾚｰﾄﾞ,Bｸﾞﾚｰﾄﾞ,Cｸﾞﾚｰﾄﾞ) ･経費(Aｸﾞﾚｰﾄﾞ,Bｸﾞﾚｰﾄﾞ,Cｸﾞﾚｰﾄﾞ) 
--**************************************************************************************************************
[lselectRecords]
SELECT
	 TDP.DT_DERIVERY_PRODUCT
	,TDP.DTS_YEAR
	,TDP.DTS_MONTH
	,TDP.DTS_DAY
	,SUM(CASE WHEN TDP.CD_GREAD = 1 THEN TDP.WEIGHT_MOIST ELSE 0 END) AGANSUI
	,SUM(CASE WHEN TDP.CD_GREAD = 2 THEN TDP.WEIGHT_MOIST ELSE 0 END) BGANSUI
	,SUM(CASE WHEN TDP.CD_GREAD = 3 THEN TDP.WEIGHT_MOIST ELSE 0 END) CGANSUI
	,A.SUIBUN ASUIBUN
	,B.SUIBUN BSUIBUN
	,C.SUIBUN CSUIBUN
	,SUM(CASE WHEN TDP.CD_GREAD = 1 THEN TDP.WEIGHT_DRY ELSE 0 END) AFUUKAN
	,SUM(CASE WHEN TDP.CD_GREAD = 2 THEN TDP.WEIGHT_DRY ELSE 0 END) BFUUKAN
	,SUM(CASE WHEN TDP.CD_GREAD = 3 THEN TDP.WEIGHT_DRY ELSE 0 END) CFUUKAN
	,SUM(CASE WHEN TDP.CD_GREAD = 1 THEN (TDP.WEIGHT_DRY * MP.UNIT_PRICE_PRODUCT) ELSE 0 END) ASEISANNDAKA
	,SUM(CASE WHEN TDP.CD_GREAD = 2 THEN (TDP.WEIGHT_DRY * MP.UNIT_PRICE_PRODUCT) ELSE 0 END) BSEISANNDAKA
	,SUM(CASE WHEN TDP.CD_GREAD = 3 THEN (TDP.WEIGHT_DRY * MP.UNIT_PRICE_PRODUCT) ELSE 0 END) CSEISANNDAKA
	,TSW.MONEY_WP_A AGENZAIRYOU
	,TSW.MONEY_WP_B BGENZAIRYOU
	,TSW.MONEY_WP_C CGENZAIRYOU
	,SUM(CASE WHEN TDP.CD_GREAD = 1 THEN (TDP.WEIGHT_DRY * MD.PRICE_FREIGHT_PRODUCT) ELSE 0 END) A_UNSOUHI
	,SUM(CASE WHEN TDP.CD_GREAD = 2 THEN (TDP.WEIGHT_DRY * MD.PRICE_FREIGHT_PRODUCT) ELSE 0 END) B_UNSOUHI
	,SUM(CASE WHEN TDP.CD_GREAD = 3 THEN (TDP.WEIGHT_DRY * MD.PRICE_FREIGHT_PRODUCT) ELSE 0 END) C_UNSOUHI
	,TM.A A_KEIHI
	,TM.B B_KEIHI
	,TM.C C_KEIHI
FROM t_derivery_product tdp
LEFT OUTER JOIN (
	SELECT
		 CD_PRODUCT
		,CD_GREAD
		,UNIT_PRICE_PRODUCT
	FROM M_PRODUCT
	WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
	AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
) mp ON (TDP.CD_PRODUCT = MP.CD_PRODUCT AND TDP.CD_GREAD = MP.CD_GREAD)
LEFT OUTER JOIN (
	SELECT
		 CD_DERIVERY
		,CD_CUSTOMER
		,PRICE_FREIGHT_PRODUCT
	FROM M_DERIVERY
	WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
	AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
) md ON (TDP.CD_DERIVERY = MD.CD_DERIVERY AND TDP.CD_CUSTOMER = MD.CD_CUSTOMER)
LEFT OUTER JOIN (
	SELECT
		 DT_DERIVERY_PRODUCT
		,round((100 - (sum(WEIGHT_DRY) / 0.01 * 9 / 10 / sum(WEIGHT_MOIST))), 1) SUIBUN
	FROM T_DERIVERY_PRODUCT
	WHERE DT_DERIVERY_PRODUCT >= TO_DATE('&1', 'yyyy/mm/dd')
	AND DT_DERIVERY_PRODUCT <= TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
	AND CD_GREAD = 1
	GROUP BY DT_DERIVERY_PRODUCT
) a ON (TDP.DT_DERIVERY_PRODUCT = A.DT_DERIVERY_PRODUCT)
LEFT OUTER JOIN (
	SELECT
		 DT_DERIVERY_PRODUCT
		,round((100 - (sum(WEIGHT_DRY) / 0.01 * 9 / 10 / sum(WEIGHT_MOIST))), 1) SUIBUN
	FROM T_DERIVERY_PRODUCT
	WHERE DT_DERIVERY_PRODUCT >= TO_DATE('&1', 'yyyy/mm/dd')
	AND DT_DERIVERY_PRODUCT <= TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
	AND CD_GREAD = 2
	GROUP BY DT_DERIVERY_PRODUCT 
) b ON (TDP.DT_DERIVERY_PRODUCT = B.DT_DERIVERY_PRODUCT)
LEFT OUTER JOIN (
	SELECT
		 DT_DERIVERY_PRODUCT
		,round((100 - (sum(WEIGHT_DRY) / 0.01 * 9 / 10 / sum(WEIGHT_MOIST))), 1) SUIBUN
	FROM T_DERIVERY_PRODUCT
	WHERE DT_DERIVERY_PRODUCT >= TO_DATE('&1', 'yyyy/mm/dd')
	AND DT_DERIVERY_PRODUCT <= TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
	AND CD_GREAD = 3
	GROUP BY DT_DERIVERY_PRODUCT
) c ON (TDP.DT_DERIVERY_PRODUCT = C.DT_DERIVERY_PRODUCT)
LEFT OUTER JOIN (
	SELECT
		 DT_MCP
		,sum(CASE WHEN CD_GREAD = 1 THEN EXPENSES ELSE 0 END) A
		,sum(CASE WHEN CD_GREAD = 2 THEN EXPENSES ELSE 0 END) B
		,sum(CASE WHEN CD_GREAD = 3 THEN EXPENSES ELSE 0 END) C
	from T_MCP
	WHERE DT_MCP >= TO_DATE('&1', 'yyyy/mm/dd')
	AND DT_MCP <= TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
	GROUP BY DT_MCP
) tm ON (TDP.DT_DERIVERY_PRODUCT = TM.DT_MCP)
LEFT OUTER JOIN (
	SELECT
		 DTS_YEAR
		,DTS_MONTH
		,DTS_DAY
		,SUM(CASE WHEN CD_GRADE_PRODUCT = 1 THEN MONEY_WP ELSE 0 END) MONEY_WP_A
		,SUM(CASE WHEN CD_GRADE_PRODUCT = 2 THEN MONEY_WP ELSE 0 END) MONEY_WP_B
		,SUM(CASE WHEN CD_GRADE_PRODUCT = 3 THEN MONEY_WP ELSE 0 END) MONEY_WP_C
	FROM T_SHIP_WP
	GROUP BY DTS_YEAR, DTS_MONTH, DTS_DAY
) tsw ON (TDP.DTS_YEAR = TSW.DTS_YEAR AND TDP.DTS_MONTH = TSW.DTS_MONTH AND TDP.DTS_DAY = TSW.DTS_DAY)
WHERE TDP.DT_DERIVERY_PRODUCT = TO_DATE('&1', 'yyyy/mm/dd')
AND TDP.FLG_DELETE = 0
GROUP BY TDP.DT_DERIVERY_PRODUCT , TDP.DTS_YEAR , TDP.DTS_MONTH , TDP.DTS_DAY , A.SUIBUN , B.SUIBUN , C.SUIBUN , TM.A, TM.B, TM.C , TSW.MONEY_WP_A, TSW.MONEY_WP_B, TSW.MONEY_WP_C


--************************************************************
--*　1日分の担当者名と
--*　担当者別の生産ロット(パレット)の開始番号と終了番号を取得
--************************************************************
[lselectRecords2]
SELECT
	 TDP.DT_DERIVERY_PRODUCT
	,NAME_STAFF
	,ROTTO.MINROTTO
	,ROTTO.MAXROTTO
FROM t_derivery_product tdp
LEFT OUTER JOIN (
	SELECT
		 DT_DERIVERY_PRODUCT
		,CD_STAFF
		,MIN(NO_PALLET) MINROTTO
		,MAX(NO_PALLET) MAXROTTO
	FROM T_DERIVERY_PRODUCT
	WHERE FLG_DELETE = 0
	GROUP BY DT_DERIVERY_PRODUCT , CD_STAFF
) rotto ON (TDP.CD_STAFF = ROTTO.CD_STAFF AND TDP.DT_DERIVERY_PRODUCT = ROTTO.DT_DERIVERY_PRODUCT)
LEFT OUTER JOIN m_staff mstf ON (TDP.CD_STAFF = MSTF.CD_STAFF)
WHERE TDP.DT_DERIVERY_PRODUCT = TO_DATE('&1', 'yyyy/mm/dd')
GROUP BY TDP.DT_DERIVERY_PRODUCT , NAME_STAFF , ROTTO.MINROTTO , ROTTO.MAXROTTO
ORDER BY ROTTO.MINROTTO


--****************************************************
--*　1日分の製品受け入れデータを取得
--*　(得意先名・時刻・含水重量・重量・水分率・特記)
--****************************************************
[lselectRecords3]
SELECT
	 DT_DERIVERY_PRODUCT
	,TDP.CD_GREAD
	,NAME_DERIVERY
	,TM_FINISHED
	,WEIGHT_MOIST
	,WEIGHT_DRY
	,coalesce(RATIO_MOIST, 0) RATIO_MOIST
	,NOTE
	,NOTE_KEIKOU
	,NOTE_KYOUZATSU
	,NOTE_ARUMI
	,NOTE_SHIROBOTSU
	,TDP.NO_PALLET
	,NAME_PRODUCT
	,NAME_STAFF
FROM m_staff ms, t_derivery_product tdp
LEFT OUTER JOIN m_customer mc ON (TDP.CD_CUSTOMER = MC.CD_CUSTOMER)
LEFT OUTER JOIN m_derivery md ON (TDP.CD_DERIVERY = MD.CD_DERIVERY)
LEFT OUTER JOIN m_product mp ON (TDP.CD_PRODUCT = MP.CD_PRODUCT)
WHERE TDP.FLG_DELETE = 0
AND TDP.DT_DERIVERY_PRODUCT = TO_DATE('&1', 'yyyy/mm/dd')
AND MC.DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
AND MC.DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
AND MC.FLG_DELETE = 0
AND MD.DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
AND MD.DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
AND MD.FLG_DELETE = 0
AND MP.DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
AND MP.DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
AND MP.FLG_DELETE = 0
AND TDP.CD_STAFF = MS.CD_STAFF
ORDER BY NO_PALLET