--**************************************************************************
--*　売上データの出力範囲を取得
--*　
--**************************************************************************
[lselectRecords]
select *
from (
select mc.CD_CUSTOMER,mc.name_customer
FROM T_SALE ts ,M_customer mc
where ts.DT_SALE >= '&2'
and ts.DT_SALE <= '&3'
and (('&4' = '') or (ts.CD_CUSTOMER >= TO_NUMBER('&4')))
and (('&5' = '') or (ts.CD_CUSTOMER <= TO_NUMBER('&5')))
and (('&6' = '') or (ts.CD_DERIVERY >= TO_NUMBER('&6')))
and (('&7' = '') or (ts.CD_DERIVERY >= TO_NUMBER('&7')))
and (('&8' = '') or (ts.CD_GREAD >= TO_NUMBER('&8')))
and ts.FLG_DELETE = 0
and ts.CD_CUSTOMER = mc.CD_CUSTOMER

--入金データ
union
select mc.CD_CUSTOMER,mc.name_customer
from T_DEPOSIT td ,M_customer mc
where td.DT_DEPOSIT >= '&2'
and td.DT_DEPOSIT <= '&3'
and (('&4' = '') or (td.CD_CUSTOMER >= TO_NUMBER('&4')))
and (('&5' = '') or (td.CD_CUSTOMER <= TO_NUMBER('&5')))
and (('&6' = '') or (td.CD_DERIVERY >= TO_NUMBER('&6')))
and (('&7' = '') or (td.CD_DERIVERY >= TO_NUMBER('&7')))
and td.DIV_DELETE = 0
and td.CD_CUSTOMER = mc.CD_CUSTOMER
group by mc.CD_CUSTOMER , mc.name_customer) x
order by CD_CUSTOMER , name_customer collate "ja_JP.utf8" asc


--**************************************************************************
--*　売上データの出力範囲を取得
--*　(グラフ表示用得意先の降順)
--**************************************************************************
[lselectRecords4]
select mc.CD_CUSTOMER,mc.name_customer
FROM T_SALE ts ,M_customer mc
where ts.DT_SALE >= '&2'
and ts.DT_SALE <= '&3'
and (('&4' = '') or (ts.CD_CUSTOMER >= TO_NUMBER('&4')))
and (('&5' = '') or (ts.CD_CUSTOMER <= TO_NUMBER('&5')))
and (('&6' = '') or (ts.CD_DERIVERY >= TO_NUMBER('&6')))
and (('&7' = '') or (ts.CD_DERIVERY >= TO_NUMBER('&7')))
and (('&8' = '') or (ts.CD_GREAD >= TO_NUMBER('&8')))
and ts.FLG_DELETE = 0
and ts.CD_CUSTOMER = mc.CD_CUSTOMER

--入金データ
union
select mc.CD_CUSTOMER,mc.name_customer
from T_DEPOSIT td ,M_customer mc
where td.DT_DEPOSIT >= '&2'
and td.DT_DEPOSIT <= '&3'
and (('&4' = '') or (td.CD_CUSTOMER >= TO_NUMBER('&4')))
and (('&5' = '') or (td.CD_CUSTOMER <= TO_NUMBER('&5')))
and (('&6' = '') or (td.CD_DERIVERY >= TO_NUMBER('&6')))
and (('&7' = '') or (td.CD_DERIVERY >= TO_NUMBER('&7')))
and td.DIV_DELETE = 0
and td.CD_CUSTOMER = mc.CD_CUSTOMER
group by mc.CD_CUSTOMER , mc.name_customer
order by CD_CUSTOMER desc


--**************************************************************************
--*　売上データの出力範囲を取得
--*　(グラフ表示用得意先別納品先の降順)
--**************************************************************************
[lselectRecords5]
select mc.CD_CUSTOMER,mc.name_customer,MD.CD_DERIVERY,MD.NAME_DERIVERY
FROM T_SALE ts ,M_customer mc ,M_DERIVERY MD
where ts.DT_SALE >= '&2'
and ts.DT_SALE <= '&3'
and (('&9' = '') or (ts.CD_CUSTOMER = TO_NUMBER('&9')))
and ts.FLG_DELETE = 0
and ts.CD_CUSTOMER = mc.CD_CUSTOMER
and ts.CD_DERIVERY = MD.CD_DERIVERY

--入金データ
union
select mc.CD_CUSTOMER,mc.name_customer,MD.CD_DERIVERY,MD.NAME_DERIVERY
from T_DEPOSIT td ,M_customer mc ,M_DERIVERY MD
where td.DT_DEPOSIT >= '&2'
and td.DT_DEPOSIT <= '&3'
and (('&9' = '') or (td.CD_CUSTOMER = TO_NUMBER('&9')))
and td.DIV_DELETE = 0
and td.CD_CUSTOMER = mc.CD_CUSTOMER
and td.CD_DERIVERY = MD.CD_DERIVERY
group by mc.CD_CUSTOMER , mc.name_customer , MD.CD_DERIVERY , MD.NAME_DERIVERY
order by CD_DERIVERY desc


--**************************************************************************
--*　全得意先の売上データを取得
--*　
--**************************************************************************
[lselectRecords_TotalCustomer]
-- 得意先別の売上計（当月分）
SELECT
	 round((CASE WHEN SUM(TS.WEIGHT_SALE01) = 0 THEN SUM(TS.MONEY_SALE01) ELSE SUM(TS.MONEY_SALE01) / SUM(TS.WEIGHT_SALE01) END::text) ::numeric, 1) UNIT_PRICE_SALE -- 単価
	,trunc((SUM(TS.WEIGHT_SALE01) / 1000) + 0.5) WEIGHT_SALE-- 年別の数量
	,trunc((SUM(TS.MONEY_SALE01) / 1000) + 0.5) MONEY_SALE -- 年別の売上金額
	,trunc((SUM(TS.TAX01) / 1000) + 0.5) TAX -- 年別の消費税
	,trunc((SUM(TD.MONEY_DEPOSIT01) / 1000) + 0.5) MONEY_DEPOSIT -- 年別の入金額
	,trunc((SUM(TS.MONEY_ADJUST01 + TS.MONEY_FREIGHT01 - TD.MONEY_DEPOSIT_ADJ01) / 1000) + 0.5) ADJUST -- 調整額は売上調整額と伝票の運賃と入金調整額とする。年別の伝票調整額 + 年別の運賃 - 年別の入金調整額
FROM m_customer mc
-- 得意先別納品先ごとのデータ
LEFT OUTER JOIN (
	SELECT
		 TS01.CD_CUSTOMER CD_CUSTOMER -- 得意先別納品先別の売上データ
		,sum(TS01.WEIGHT_SALE) WEIGHT_SALE01 -- 年別の数量
		,sum(TS01.MONEY_SALE) MONEY_SALE01 -- 年別の売上金額
		,sum(TS01.TAX) TAX01 -- 年別の消費税
		,sum(TS01.MONEY_ADJUST) MONEY_ADJUST01 -- 年別の伝票調整額
		,sum(TS01.MONEY_FREIGHT) MONEY_FREIGHT01 -- 年別の運賃
	FROM T_SALE TS01
	WHERE TS01.DT_SALE >= '&2'
	AND TS01.DT_SALE <= '&3'
	AND TS01.FLG_DELETE = 0
	AND (('&8' = '') OR (TS01.CD_GREAD = ('&8'::text) ::numeric))
	group by TS01.CD_CUSTOMER 
) ts ON (MC.CD_CUSTOMER = TS.CD_CUSTOMER)
LEFT OUTER JOIN (
	SELECT
		 TD02.CD_CUSTOMER CD_CUSTOMER
		,SUM(TD02.MONEY_DEPOSIT01) MONEY_DEPOSIT01
		,SUM(TD02.MONEY_DEPOSIT_ADJ01) MONEY_DEPOSIT_ADJ01 -- 年別の売上調整額
	FROM (
		-- 得意先別納品先別の入金データ
		SELECT
			 TD01.CD_CUSTOMER CD_CUSTOMER
			,CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END MONEY_DEPOSIT01 -- 年別の売上入金
			,CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END MONEY_DEPOSIT_ADJ01 -- 年別の売上調整額
		FROM T_DEPOSIT TD01
		where TD01.DT_DEPOSIT >= '&2'
		AND TD01.DT_DEPOSIT <= '&3'
		AND TD01.DIV_DELETE = 0
	) TD02
    GROUP BY TD02.CD_CUSTOMER
) td ON (MC.CD_CUSTOMER = TD.CD_CUSTOMER)
WHERE MC.DT_START = (
	SELECT MAX(MC01.DT_START)
	FROM M_CUSTOMER MC01
	WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER)
AND (('&4' = '') OR (MC.CD_CUSTOMER >= ('&4'::text) ::numeric))
AND (('&5' = '') OR (MC.CD_CUSTOMER <= ('&5'::text) ::numeric))


--**************************************************************************
--*　得意先別の売上データを取得
--*　(得意先別売上数量、金額取得用）
--**************************************************************************
[lselectRecords_customer_saledata]
SELECT
	 MC.CD_CUSTOMER
	,MC.NAME_CUSTOMER
	,trunc((SUM(TS.WEIGHT_SALE01) / 1000) + 0.5) WEIGHT_SALE --年別数量
	,trunc((SUM(TS.MONEY_SALE01) / 1000) + 0.5) MONEY_SALE --年別高
FROM M_CUSTOMER MC,(
	SELECT
		 TS01.CD_CUSTOMER CD_CUSTOMER
		,sum(TS01.WEIGHT_SALE) WEIGHT_SALE01
		,sum(TS01.MONEY_SALE) MONEY_SALE01
	FROM T_SALE TS01
	WHERE DT_SALE >= '&2'
	and DT_SALE <= '&3'
	AND TS01.FLG_DELETE = 0
	AND (('&8' = '') OR (TS01.CD_GREAD = ('&8'::text) ::numeric))
	group by TS01.CD_CUSTOMER
	order by TS01.CD_CUSTOMER
) TS
WHERE (('&4' = '') OR (MC.CD_CUSTOMER >= ('&4'::text) ::numeric))
AND (('&5' = '') OR (MC.CD_CUSTOMER <= ('&5'::text) ::numeric))
AND MC.CD_CUSTOMER = TS.CD_CUSTOMER
AND MC.DT_START = (
	SELECT MAX(MC01.DT_START)
	FROM M_CUSTOMER MC01
	WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER)
GROUP BY MC.CD_CUSTOMER, MC.NAME_CUSTOMER
ORDER BY MC.CD_CUSTOMER desc , MC.NAME_CUSTOMER desc


--**************************************************************************
--*　得意先別の売上データを取得
--*　
--**************************************************************************
[lselectRecords_TotalDerivery]
SELECT
	 round((CASE WHEN SUM(TS.WEIGHT_SALE01) = 0 THEN SUM(TS.MONEY_SALE01) ELSE SUM(TS.MONEY_SALE01) / SUM(TS.WEIGHT_SALE01) END::text) ::numeric, 1) UNIT_PRICE_SALE -- 単価
	,trunc((SUM(TS.WEIGHT_SALE01) / 1000) + 0.5) WEIGHT_SALE -- 年別の数量
	,trunc((SUM(TS.MONEY_SALE01) / 1000) + 0.5) MONEY_SALE -- 年別の売上金額
	,trunc((SUM(TS.TAX01) / 1000) + 0.5) TAX -- 年別の消費税
	,trunc((SUM(TD.MONEY_DEPOSIT01) / 1000) + 0.5) MONEY_DEPOSIT -- 年別の入金額
	,trunc((SUM(TS.MONEY_ADJUST01 + TS.MONEY_FREIGHT01 - TD.MONEY_DEPOSIT_ADJ01) / 1000) + 0.5) ADJUST -- 調整額は売上調整額と伝票の運賃と入金調整額とする。年別の伝票調整額 + 年別の運賃 - 年別の入金調整額
FROM m_customer mc
LEFT OUTER JOIN (
	-- 得意先別納品先別の売上データ
	SELECT
		 TS02.CD_CUSTOMER CD_CUSTOMER
		,SUM(TS02.WEIGHT_SALE01) WEIGHT_SALE01 -- 月別の数量
		,SUM(TS02.MONEY_SALE01) MONEY_SALE01 -- 月別の売上金額
		,SUM(TS02.TAX01) TAX01 -- 月別の消費税
		,SUM(TS02.MONEY_ADJUST01) MONEY_ADJUST01 -- 月別の伝票調整額
		,SUM(TS02.MONEY_FREIGHT01) MONEY_FREIGHT01 -- 月別の運賃
	FROM(
		-- 得意先別納品先ごとのデータ
		SELECT
			 TS01.CD_CUSTOMER CD_CUSTOMER -- 得意先別納品先別の売上データ
			,sum(TS01.WEIGHT_SALE) WEIGHT_SALE01 -- 月別の数量
			,sum(TS01.MONEY_SALE) MONEY_SALE01 -- 月別の売上金額
			,sum(TS01.TAX) TAX01 -- 月別の消費税
			,sum(TS01.MONEY_ADJUST) MONEY_ADJUST01 -- 月別の伝票調整額
			,sum(TS01.MONEY_FREIGHT) MONEY_FREIGHT01 -- 月別の運賃
		FROM T_SALE TS01
		WHERE TS01.DT_SALE >= '&2'
		AND TS01.DT_SALE <= '&3'
		AND TS01.FLG_DELETE = 0
		AND (('&8' = '') OR (TS01.CD_GREAD = ('&8'::text) ::numeric))
		GROUP BY TS01.CD_CUSTOMER 
    ) TS02
    GROUP BY TS02.CD_CUSTOMER
) ts ON (MC.CD_CUSTOMER = TS.CD_CUSTOMER)
LEFT OUTER JOIN (
	SELECT
		 TD02.CD_CUSTOMER CD_CUSTOMER
		,SUM(TD02.MONEY_DEPOSIT01) MONEY_DEPOSIT01 -- 年別の売上入金
		,SUM(TD02.MONEY_DEPOSIT_ADJ01) MONEY_DEPOSIT_ADJ01 -- 年別の売上調整額
	FROM (
		SELECT
			 TD01.CD_CUSTOMER CD_CUSTOMER -- 得意先別納品先別の入金データ
			,CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END MONEY_DEPOSIT01 -- 年別の売上入金
			,CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END MONEY_DEPOSIT_ADJ01 -- 年別の売上調整額
		FROM T_DEPOSIT TD01
		WHERE TD01.DT_DEPOSIT >= '&2'
		AND TD01.DT_DEPOSIT <= '&3'
		AND TD01.DIV_DELETE = 0
	) TD02
	GROUP BY TD02.CD_CUSTOMER
) td ON (MC.CD_CUSTOMER = TD.CD_CUSTOMER)
WHERE MC.DT_START = (
	SELECT MAX(MC01.DT_START)
	FROM M_CUSTOMER MC01
	WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER)
AND (('&9' = '') OR (MC.CD_CUSTOMER = ('&9'::text) ::numeric))


--**************************************************************************
--*　得意先、納品先別の売上データを取得
--*　納品先別の売上量、売上高の取得
--**************************************************************************
[lselectRecords_derivery_saledata]
 -- 単価
SELECT
	 MC.CD_CUSTOMER
	,MD.CD_DERIVERY
	,MC.NAME_CUSTOMER
	,MD.NAME_DERIVERY
	,trunc((SUM(TS.WEIGHT_SALE01) / 1000) + 0.5) WEIGHT_SALE -- 月別の数量
	,trunc((SUM(TS.MONEY_SALE01) / 1000) + 0.5) MONEY_SALE -- 月別の売上金額
FROM M_CUSTOMER MC,M_DERIVERY MD,(
	SELECT
		 TS01.CD_CUSTOMER
		,TS01.CD_DERIVERY
		,sum(TS01.WEIGHT_SALE) WEIGHT_SALE01
		,sum(TS01.MONEY_SALE) MONEY_SALE01
	FROM T_SALE TS01
	WHERE TS01.DT_SALE >= '&2'
	AND TS01.DT_SALE <= '&3'
	and TS01.FLG_DELETE = 0
	AND (('&8' = '') OR (TS01.CD_GREAD = ('&8'::text) ::numeric))
	GROUP BY TS01.CD_CUSTOMER, TS01.CD_DERIVERY
) TS
WHERE MC.CD_CUSTOMER = MD.CD_CUSTOMER
AND MD.CD_CUSTOMER = TS.CD_CUSTOMER
AND MD.CD_DERIVERY = TS.CD_DERIVERY
AND MC.DT_START = (
	SELECT MAX(MC01.DT_START)
	FROM M_CUSTOMER MC01
	WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER)
AND MD.DT_START = (
	SELECT MAX(MD01.DT_START)
	FROM M_DERIVERY MD01
	WHERE MD01.CD_DERIVERY = MD.CD_DERIVERY)
AND (('&9' = '') OR (MC.CD_CUSTOMER = ('&9'::text) ::numeric))
GROUP BY MC.CD_CUSTOMER, MD.CD_DERIVERY, MC.NAME_CUSTOMER, MD.NAME_DERIVERY
ORDER BY MC.CD_CUSTOMER desc , MD.CD_DERIVERY desc , MC.NAME_CUSTOMER desc , MD.NAME_DERIVERY desc


--**************************************************************************
--*　納品先別データの取得
--*　
--**************************************************************************
[lselectRecords_derivery_data]
SELECT
	 MC.CD_CUSTOMER
	,MC.NAME_CUSTOMER
	,MD.CD_DERIVERY
	,MD.NAME_DERIVERY
	,round((CASE WHEN SUM(TS.WEIGHT_SALE01) = 0 THEN SUM(TS.MONEY_SALE01) ELSE SUM(TS.MONEY_SALE01) / SUM(TS.WEIGHT_SALE01) END::text) ::numeric, 1) UNIT_PRICE_SALE  -- 単価
	,trunc((SUM(TS.WEIGHT_SALE01) / 1000) + 0.5) WEIGHT_SALE -- 年別の数量
	,trunc((SUM(TS.MONEY_SALE01) / 1000) + 0.5) MONEY_SALE -- 年別の売上金額
	,trunc((SUM(TS.TAX01) / 1000) + 0.5) TAX -- 年別の消費税
FROM m_customer mc
LEFT OUTER JOIN m_derivery md ON (MC.CD_CUSTOMER = MD.CD_CUSTOMER)
LEFT OUTER JOIN (
	SELECT
		 TS02.CD_CUSTOMER CD_CUSTOMER
		,TS02.CD_DERIVERY CD_DERIVERY -- 得意先別納品先別の売上データ
		,sum(TS02.WEIGHT_SALE) WEIGHT_SALE01 -- 年別の数量
		,sum(TS02.MONEY_SALE) MONEY_SALE01 -- 年別の売上金額
		,sum(TS02.TAX) TAX01 -- 年別の消費税
		,sum(TS02.MONEY_ADJUST) MONEY_ADJUST01 -- 年別の伝票調整額
		,sum(TS02.MONEY_FREIGHT) MONEY_FREIGHT01 -- 年別の運賃
	FROM(
		SELECT
			 TS01.CD_CUSTOMER
			,TS01.CD_DERIVERY
			,sum(TS01.WEIGHT_SALE) WEIGHT_SALE
			,sum(TS01.MONEY_SALE) MONEY_SALE
			,sum(TS01.TAX) TAX
			,sum(TS01.MONEY_ADJUST) MONEY_ADJUST
			,sum(TS01.MONEY_FREIGHT) MONEY_FREIGHT
		FROM T_SALE TS01
		WHERE DT_SALE >= '&2'
		and DT_SALE <= '&3'
		AND TS01.FLG_DELETE = 0
		AND (('&8' = '') OR (TS01.CD_GREAD = ('&8'::text) ::numeric))
		group by CD_CUSTOMER , TS01.CD_DERIVERY
		order by CD_CUSTOMER , TS01.CD_DERIVERY
    ) TS02
	GROUP BY TS02.CD_CUSTOMER, TS02.CD_DERIVERY 
) ts ON (MD.CD_CUSTOMER = TS.CD_CUSTOMER AND MD.CD_DERIVERY = TS.CD_DERIVERY)
WHERE MC.DT_START = (
	SELECT MAX(MC01.DT_START)
	FROM M_CUSTOMER MC01
	WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER)
AND MD.DT_START = (
	SELECT MAX(MD01.DT_START)
	FROM M_DERIVERY MD01
	WHERE MD01.CD_DERIVERY = MD.CD_DERIVERY)
AND (('&9' = '') OR (MC.CD_CUSTOMER = ('&9'::text) ::numeric))
and (('&10' = '') OR (TS.CD_DERIVERY = ('&10'::text) ::numeric))
GROUP BY MC.CD_CUSTOMER, MD.CD_DERIVERY, MC.NAME_CUSTOMER, MD.NAME_DERIVERY
ORDER BY MC.CD_CUSTOMER, MD.CD_DERIVERY, MC.NAME_CUSTOMER, MD.NAME_DERIVERY


--**************************************************************************
--*　納品先別製品データ件数の取得
--*　
--**************************************************************************
[lselectRecords_derivery_product_data_cnt]
SELECT
	 MC.CD_CUSTOMER
	,MC.NAME_CUSTOMER
	,MD.CD_DERIVERY
	,MD.NAME_DERIVERY
	,MP.CD_PRODUCT
	,MP.NAME_PRODUCT
FROM m_customer mc
LEFT OUTER JOIN m_derivery md ON (MC.CD_CUSTOMER = MD.CD_CUSTOMER)
LEFT OUTER JOIN t_sale ts ON (MD.CD_CUSTOMER = TS.CD_CUSTOMER AND MD.CD_DERIVERY = TS.CD_DERIVERY)
LEFT OUTER JOIN m_product mp ON (TS.CD_PRODUCT = MP.CD_PRODUCT)
WHERE TS.DT_SALE >= '&2'
and TS.DT_SALE <= '&3'
AND TS.FLG_DELETE = 0
AND (('&8' = '') OR (TS.CD_GREAD = ('&8'::text) ::numeric))
AND MC.DT_START = (
	SELECT MAX(MC01.DT_START)
	FROM M_CUSTOMER MC01
	WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER)
AND MD.DT_START = (
	SELECT MAX(MD01.DT_START)
	FROM M_DERIVERY MD01
	WHERE MD01.CD_DERIVERY = MD.CD_DERIVERY)
AND MP.DT_START = (
	SELECT MAX(MP01.DT_START)
	FROM M_PRODUCT MP01
	WHERE MP01.CD_PRODUCT = MP.CD_PRODUCT)
AND (('&9' = '') OR (MC.CD_CUSTOMER = ('&9'::text) ::numeric))
and (('&10' = '') OR (TS.CD_DERIVERY = ('&10'::text) ::numeric))
GROUP BY MC.CD_CUSTOMER, MD.CD_DERIVERY, MC.NAME_CUSTOMER, MD.NAME_DERIVERY , MP.CD_PRODUCT , MP.NAME_PRODUCT
ORDER BY MC.CD_CUSTOMER, MD.CD_DERIVERY, MC.NAME_CUSTOMER, MD.NAME_DERIVERY , MP.CD_PRODUCT , MP.NAME_PRODUCT


--**************************************************************************
--*　納品先別製品一覧の取得
--*　
--**************************************************************************
[lselectRecords_derivery_product_data]
SELECT
	 MC.CD_CUSTOMER
	,MC.NAME_CUSTOMER
	,MD.CD_DERIVERY
	,MD.NAME_DERIVERY
	,MP.CD_PRODUCT
	,MP.NAME_PRODUCT
	,round((CASE WHEN SUM(TS.WEIGHT_SALE01) = 0 THEN SUM(TS.MONEY_SALE01) ELSE SUM(TS.MONEY_SALE01) / SUM(TS.WEIGHT_SALE01) END::text) ::numeric, 1) UNIT_PRICE_SALE -- 単価
	,trunc((SUM(TS.WEIGHT_SALE01) / 1000) + 0.5) WEIGHT_SALE -- 年別の数量
	,trunc((SUM(TS.MONEY_SALE01) / 1000) + 0.5) MONEY_SALE -- 年別の売上金額
	,trunc((SUM(TS.TAX01) / 1000) + 0.5) TAX -- 年別の消費税
FROM m_customer mc
LEFT OUTER JOIN m_derivery md ON (MC.CD_CUSTOMER = MD.CD_CUSTOMER)
LEFT OUTER JOIN (
	SELECT
		 TS02.CD_CUSTOMER CD_CUSTOMER
		,TS02.CD_DERIVERY CD_DERIVERY
		,TS02.CD_PRODUCT CD_PRODUCT -- 得意先別納品先別の売上データ
		,sum(TS02.WEIGHT_SALE) WEIGHT_SALE01 -- 年別の数量
		,sum(TS02.MONEY_SALE) MONEY_SALE01 -- 年別の売上金額
		,sum(TS02.TAX) TAX01 -- 年別の消費税
		,sum(TS02.MONEY_ADJUST) MONEY_ADJUST01 -- 年別の伝票調整額
		,sum(TS02.MONEY_FREIGHT) MONEY_FREIGHT01 -- 年別の運賃
	FROM (
		SELECT
			 TS01.CD_CUSTOMER
			,TS01.CD_DERIVERY
			,TS01.CD_PRODUCT
			,sum(TS01.WEIGHT_SALE) WEIGHT_SALE
			,sum(TS01.MONEY_SALE) MONEY_SALE
			,sum(TS01.TAX) TAX
			,sum(TS01.MONEY_ADJUST) MONEY_ADJUST
			,sum(TS01.MONEY_FREIGHT) MONEY_FREIGHT
		FROM T_SALE TS01
		WHERE DT_SALE >= '&2'
		and DT_SALE <= '&3'
		AND TS01.FLG_DELETE = 0
		AND (('&8' = '') OR (TS01.CD_GREAD = ('&8'::text) ::numeric))
		group by CD_CUSTOMER , TS01.CD_DERIVERY , TS01.CD_PRODUCT
		order by CD_CUSTOMER , TS01.CD_DERIVERY , TS01.CD_PRODUCT
    ) TS02
	GROUP BY TS02.CD_CUSTOMER, TS02.CD_DERIVERY , TS02.CD_PRODUCT
) ts ON (MD.CD_CUSTOMER = TS.CD_CUSTOMER AND MD.CD_DERIVERY = TS.CD_DERIVERY)
LEFT OUTER JOIN m_product mp ON (TS.CD_PRODUCT = MP.CD_PRODUCT)
WHERE MC.DT_START = (
	SELECT MAX(MC01.DT_START)
	FROM M_CUSTOMER MC01
	WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER)
AND MD.DT_START = (
	SELECT MAX(MD01.DT_START)
	FROM M_DERIVERY MD01
	WHERE MD01.CD_DERIVERY = MD.CD_DERIVERY)
AND MP.DT_START = (
	SELECT MAX(MP01.DT_START)
	FROM M_PRODUCT MP01
	WHERE MP01.CD_PRODUCT = MP.CD_PRODUCT)
AND (('&9' = '') OR (MC.CD_CUSTOMER = ('&9'::text) ::numeric))
and (('&10' = '') OR (TS.CD_DERIVERY = ('&10'::text) ::numeric))
and (('&11' = '') OR (TS.CD_PRODUCT = ('&11'::text) ::numeric))
GROUP BY MC.CD_CUSTOMER, MD.CD_DERIVERY, MC.NAME_CUSTOMER, MD.NAME_DERIVERY , MP.CD_PRODUCT , MP.NAME_PRODUCT
ORDER BY MC.CD_CUSTOMER, MD.CD_DERIVERY, MC.NAME_CUSTOMER, MD.NAME_DERIVERY , MP.CD_PRODUCT , MP.NAME_PRODUCT


--**************************************************************************
--*　前月までの差引残高を取得
--*　
--**************************************************************************
[lselectRecords_zenzan]
SELECT
	 PF.CD_CUSTOMER
	,SUM(PF.WEIGHT_SALE) WEIGHT_SALE
	,SUM(PF.MONEY_SALE) MONEY_SALE
	,SUM(PF.TAX) TAX
	,SUM(PF.MONEY_ADJUST) MONEY_ADJUST
	,SUM(PF.MONEY_FREIGHT) MONEY_FREIGHT
	,SUM(PF.MONEY_DEPOSIT) MONEY_DEPOSIT
	,SUM(PF.MONEY_ADJUST + PF.MONEY_FREIGHT - PF.MONEY_DEPOSIT_ADJUST) MONEY_DEPOSIT_ADJUST
FROM(
	select
		 TS.CD_CUSTOMER
		,sum(TS.UNIT_PRICE_SALE) UNIT_PRICE_SALE
		,sum(TS.WEIGHT_SALE) WEIGHT_SALE
		,sum(TS.MONEY_SALE) MONEY_SALE
		,sum(TS.TAX) TAX
		,coalesce(SUM(TS.MONEY_ADJUST), 0) MONEY_ADJUST
		,coalesce(sum(TS.MONEY_FREIGHT), 0) MONEY_FREIGHT
		,0 MONEY_DEPOSIT
		,0 MONEY_DEPOSIT_ADJUST
	from T_SALE TS
	where TS.DT_SALE < '&2'
	and TS.FLG_DELETE = 0
	and (('&9' = '') OR (TS.CD_CUSTOMER = ('&9'::text) ::numeric))
	and (('&8' = '') OR (TS.CD_GREAD = ('&8'::text) ::numeric))
	GROUP BY TS.CD_CUSTOMER

	UNION
	select
		 TD.CD_CUSTOMER
		,0 UNIT_PRICE_SALE
		,0 WEIGHT_SALE
		,0 MONEY_SALE
		,0 TAX
		,0 MONEY_ADJUST
		,0 MONEY_FREIGHT
		,SUM(CASE WHEN TD.DIV_DEPOSIT = 0 THEN TD.MONEY_DEPOSIT ELSE 0 END) MONEY_DEPOSIT
		,SUM(CASE WHEN TD.DIV_DEPOSIT = 1 THEN TD.MONEY_DEPOSIT ELSE 0 END) MONEY_DEPOSIT_ADJST
	FROM T_DEPOSIT TD
	WHERE TD.DT_DEPOSIT < '&2'
	and (('&9' = '') OR (TD.CD_CUSTOMER = ('&9'::text) ::numeric))
	AND DIV_DELETE = 0
	GROUP BY TD.CD_CUSTOMER
) PF
GROUP BY PF.CD_CUSTOMER 


--**************************************************************************
--*　得意先、納品先別データ一覧の取得*　
--**************************************************************************
[lselectRecords2]
select
	 mc.CD_CUSTOMER
	,mc.NAME_CUSTOMER
	,md.CD_DERIVERY
	,md.NAME_DERIVERY
FROM m_customer mc
LEFT OUTER JOIN m_derivery md ON (mc.CD_CUSTOMER = md.CD_CUSTOMER)
LEFT OUTER JOIN t_sale ts ON (md.CD_CUSTOMER = ts.CD_CUSTOMER AND md.CD_DERIVERY = ts.CD_DERIVERY)
WHERE ts.DT_SALE >= '&2'
and ts.DT_SALE <= '&3'
and (('&9' = '') or (ts.CD_CUSTOMER = TO_NUMBER('&9')))
and (('&8' = '') or (ts.CD_GREAD = TO_NUMBER('&8')))
and ts.FLG_DELETE = 0
AND mc.DT_START = (
	SELECT MAX(MC01.DT_START)
	FROM M_CUSTOMER MC01
	WHERE MC01.CD_CUSTOMER = mc.CD_CUSTOMER)
AND md.DT_START = (
	SELECT MAX(MD01.DT_START)
	FROM M_DERIVERY MD01
	WHERE MD01.CD_DERIVERY = md.CD_DERIVERY)
group by mc.CD_CUSTOMER , mc.NAME_CUSTOMER,	md.CD_DERIVERY, md.NAME_DERIVERY