project_group/langjiu-project
2
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
master
langjiu-project/FineReport/销售主题/看板/首页.sql

1961 lines
71 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

-- ======================================
-- 报表上新
-- ======================================
SELECT
op1.entry_id AS "报表id",
op2.displayname AS "报表名称",
TO_CHAR(op1.create_time,'YYYY-MM-DD') AS "上新时间"
FROM
sdi_fine.input_fr_report_list op1
LEFT JOIN sdi_fine.fine_authority_object op2 ON op1.entry_id = op2.id
WHERE
report_status = '0'
-- AND STRING_TO_ARRAY(op1.role_list,',') && STRING_TO_ARRAY('${fine_role}',',')
AND op1.create_time >= CURRENT_TIMESTAMP - INTERVAL '7 days'
ORDER BY
op1.create_time DESC
-- ======================================
-- 收藏目录表
-- ======================================
-- 收藏目录表
SELECT
IF(LENGTH(LEFT(op3.path,POSITION('/' IN op3.path) - 1)) > 0,LEFT(op3.path,POSITION('/' IN op3.path) - 1),"其他") AS `所属根目录名称`,
op3.path AS `报表全路径`,
op1.id,
op1.entryId AS `报表ID`,
op1.userId AS `用户ID`,
op2.realName AS `姓名`,
op2.userName AS `用户名`,
op3.displayName AS `报表名称`
FROM
fine_favorite_entry op1
JOIN fine_user op2 ON op1.userId = op2.id
JOIN fine_authority_object op3 ON op1.entryId = op3.id
WHERE
op2.userName = '${fine_username}'
-- 展示终端类型:此处需要再讨论一下PC及移动端定义
AND op3.deviceType = 1
-- 平台类型:此处只展示链接/FR报表/BI报表
AND op3.expandType IN (5,102,201)
-- AND IFNULL(LEFT(op3.path,POSITION('/' IN op3.path) - 1),"其他") = '${sDefaultFile}'
-- ======================================
-- 最近浏览
-- ======================================
-- 近10天近30个目录
SELECT
op1.tname AS `报表全路径`,
op2.id AS `报表ID`,
SUBSTRING_INDEX(op2.displayName, '/', -1) AS `模版名称`,
-- FROM_UNIXTIME(MAX(op1.time) / 1000,'%Y-%m-%d %H:%i:%s') AS `访问时间`
FROM_UNIXTIME(MAX(op1.time) / 1000,'%Y-%m-%d') AS `访问时间`
FROM
logdb.fine_record_execute op1
LEFT JOIN finedb.fine_authority_object op2 ON op1.tname = op2.path
WHERE
op1.username = (SELECT CONCAT(realName,'(',userName,')') AS username FROM finedb.fine_user WHERE userName = '${fine_username}')
AND op1.time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 10 DAY)) * 1000
GROUP BY
op2.displayName,
op2.id
ORDER BY
op1.time DESC
LIMIT 30
-- ======================================
-- 合同量
-- ======================================
select
'当期' AS time_type
,sum(ct_amt)/10000 AS amt
from dm.v_ag_sal_contract a
-- 权限控制
where exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
OR per_code = a.region_code -- 大区
OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
AND ct_status = '已终审'
${IF(len(sStart_date) > 0,"AND ct_year ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 年
-- ${IF(sPeriod=1,"AND ct_year ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 季
-- ${IF(sPeriod=2,"AND to_char(signed_date,'yyyy-MM-dd') >='"+ sStart_date +"'","")}
-- ${IF(sPeriod=2,"AND to_char(signed_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 月
-- ${IF(sPeriod=3,"AND to_char(signed_date,'yyyy-MM-dd') >='"+ sStart_date +"'","")}
--- ${IF(sPeriod=3,"AND to_char(signed_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 日
-- ${IF(sPeriod=4,"AND to_char(signed_date,'yyyy-MM-dd') >='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(signed_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
UNION ALL
select
'同期' AS time_type
,sum(ct_amt)/10000 AS amt
from dm.v_ag_sal_contract a
-- 权限控制
where exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
OR per_code = a.region_code -- 大区
OR per_code = a.office_code -- 办事处
OR per_code = a.city_unit_code -- 城市单元
)
)
AND ct_status = '已终审'
${IF(len(sStart_date) > 0,"AND ct_year = '" + left(yeardelta(sStart_date,-1),4) + "'","")}
-- 去年
-- ${IF(sPeriod=1,"AND ct_year = '" + left(yeardelta(sStart_date,-1),4) + "'","")}
-- 去年同季
-- ${IF(sPeriod=2,"AND to_char(signed_date,'yyyy-MM-dd') >='"+ yeardelta(sStart_date,-1) +"'","")}
-- ${IF(sPeriod=2,"AND to_char(signed_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
-- 去年同月
-- ${IF(sPeriod=3,"AND to_char(signed_date,'yyyy-MM-dd') >='"+ yeardelta(sStart_date,-1) +"'","")}
-- ${IF(sPeriod=3,"AND to_char(signed_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
-- 去年同日
-- ${IF(sPeriod=4,"AND to_char(signed_date,'yyyy-MM-dd') >='"+ yeardelta(sStart_date,-1) +"'","")}
-- ${IF(sPeriod=4,"AND to_char(signed_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
UNION ALL
select
'当期回款' time_type
,sum(amt_bu) AS amt
from dm.v_sal_indicator_bu a
where (indi_type='回款' OR indi_type='电商订单')
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
${IF(len(sStart_date) > 0,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 年
-- ${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 季
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') >='"+ sStart_date +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 月
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') >='"+ sStart_date +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 日
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') >='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- ======================================
-- 回款
-- ======================================
select
'当期' time_type
,sum(amt_bu) AS amt
from dm.v_sal_indicator_bu a
where (indi_type='回款' OR indi_type='电商订单')
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
-- 季
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 月
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 日
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
UNION ALL
select
'同期' time_type
,sum(amt_bu) AS amt
from dm.v_sal_indicator_bu a
where (indi_type='回款' OR indi_type='电商订单')
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 去年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') = '" + left(yeardelta(sStart_date,-1),4) + "'","")}
-- ${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
-- 去年同季
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(YEARDELTA(sStart_date,-1),4),roundup(month(YEARDELTA(sStart_date,-1))/3)) +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
-- 去年同月
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
-- 去年同日
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ yeardelta(sStart_date,-1) +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
UNION
select
'目标' time_type,
sum(amt_bu) AS amt
from dm.v_sal_indicator_bu a
where ${switch(sPeriod
,1,"indi_type='年度目标_事业部_回款'"
,2,"indi_type='季度目标_事业部_回款'"
,3,"indi_type='月度目标_事业部_回款'"
,4,"indi_type='不存在'")}
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 季
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 月
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 日
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- ======================================
-- 经销商出货
-- ======================================
select
'当期' time_type
,sum(amt_bu) AS amt
from dm.v_sal_indicator_bu a
where (indi_type='出货' OR indi_type='电商订单')
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
-- 季
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 月
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 日
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
UNION ALL
select
'同期' time_type
,sum(amt_bu) AS amt
from dm.v_sal_indicator_bu a
where (indi_type='出货' OR indi_type='电商订单')
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 去年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') = '" + left(yeardelta(sStart_date,-1),4) + "'","")}
-- ${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
-- 去年同季
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(YEARDELTA(sStart_date,-1),4),roundup(month(YEARDELTA(sStart_date,-1))/3)) +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
-- 去年同月
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
-- 去年同日
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ yeardelta(sStart_date,-1) +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
UNION
select
'目标' time_type
,sum(amt_bu) AS amt
from dm.v_sal_indicator_bu a
where ${switch(sPeriod
,1,"indi_type='年度目标_事业部_出货'"
,2,"indi_type='季度目标_事业部_出货'"
,3,"indi_type='月度目标_事业部_出货'"
,4,"indi_type='不存在'")}
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 季
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 月
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 日
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- ======================================
-- 发货量
-- ======================================
select
'当期' time_type
,sum(amt_bu) AS amt
from dm.v_sal_indicator_bu a
where (indi_type='发货' OR indi_type='费用发货')
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
-- 季
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 月
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 日
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
UNION ALL
select
'同期' time_type
,sum(amt_bu) AS amt
from dm.v_sal_indicator_bu a
where (indi_type='发货' OR indi_type='费用发货')
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 去年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') = '" + left(yeardelta(sStart_date,-1),4) + "'","")}
-- ${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
-- 去年同季
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(YEARDELTA(sStart_date,-1),4),roundup(month(YEARDELTA(sStart_date,-1))/3)) +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
-- 去年同月
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
-- 去年同日
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ yeardelta(sStart_date,-1) +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
UNION
select
'目标' time_type
,sum(amt_bu) AS amt
from dm.v_sal_indicator_bu a
where ${switch(sPeriod
,1,"indi_type IN('年度目标_事业部_费用发货','年度目标_事业部_销售发货')"
,2,"indi_type IN('季度目标_事业部_费用发货','季度目标_事业部_销售发货')"
,3,"indi_type IN('月度目标_事业部_费用发货','月度目标_事业部_销售发货')"
,4,"indi_type IN('不存在')")}
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 季
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 月
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 日
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- ======================================
-- 经销商库存
-- ======================================
-- 要取时点值
with base_data AS
(
select
indi_type
,stat_date
,sum(amt_bu) AS amt
from dm.v_sal_indicator_bu a
where (indi_type='实物库存'OR indi_type='在途库存'OR indi_type='融资未填仓'
OR indi_type='已填仓未下单'OR indi_type='未下单往来余额' OR indi_type='费用余额' OR indi_type='未发货')
AND EXISTS (
SELECT
1
FROM
dm.dm_bi_user_permisson
WHERE
bi_user = '${fine_username}' -- 替换为fine_username
AND per_model IN ('all','sal')
AND (
is_all = 1
OR per_code = a.bu_code
)
)
GROUP BY
indi_type,stat_date
)
SELECT
SUM(amt) AS amt -- 库存总量
,SUM(CASE WHEN indi_type = '在途库存' THEN amt ELSE 0 END) AS onway_amt -- 在途库存
,SUM(CASE WHEN indi_type = '未发货' THEN amt ELSE 0 END) AS not_ship_amt -- 未发货帐余
,SUM(CASE WHEN indi_type = '融资未填仓' THEN amt ELSE 0 END) AS not_fill_amt -- 融资未填仓
FROM base_data
WHERE 1=1
-- 年
-- ${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- ${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') = '"+ FORMAT(ENDOFMONTH(sStart_date),'yyyy-MM-dd') +"'","")}
-- 季
-- ${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 月
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
${IF(len(sStart_date) > 0,"AND to_char(stat_date,'yyyy-MM-dd') = '" + FORMAT(DATEINMONTH(sStart_date,1),'yyyy-MM-dd') + "'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 日
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- ======================================
-- 财务资金状况
-- ======================================
-- 资金计划与执行
select
'当期' AS time_type
,'收入' AS fee_type -- 收支方向 收入/支出
,SUM(CASE WHEN trade_direction = '收入' THEN amt ELSE 0 END) / 10000 AS amt
from dm.dm_fim_fud_indicator a
where corp_code not in ('0101','0103','0102')
AND trade_direction = '收入'
AND indi_type = '执行'
AND plan_item_name NOT IN ('内部调拨收入','取得贷款')
AND owner_bank_account <> '22195101040012008'
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_mon,'yyyy') +"'","")}
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy-MM') <= '"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
-- 月
${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') = '"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') <= '"+ FORMAT(sStart_date_fin,'yyyy-MM-dd') +"'","")}
-- 日
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >= '"+ FORMAT(sStart_date_fin,'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date_fin,'yyyy-MM-dd') +"'","")}
UNION ALL
select
'当期' AS time_type
,'支出' AS fee_type -- 收支方向 收入/支出
,SUM(CASE WHEN trade_direction = '支出' THEN amt ELSE 0 END) / 10000 AS amt
from dm.dm_fim_fud_indicator a
where corp_code not in ('0101','0103','0102')
AND trade_direction = '支出'
AND indi_type = '执行'
AND plan_item_name NOT IN ('内部调拨支出','偿还贷款')
AND owner_bank_account <> '22195101040012008'
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_mon,'yyyy') +"'","")}
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy-MM') <='"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
-- 月
${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date_fin,'yyyy-MM-dd') +"'","")}
-- 日
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >='"+ FORMAT(sStart_date_fin,'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(sStart_date_fin,'yyyy-MM-dd') +"'","")}
UNION ALL
-- 月初资金余额要取上月的月初的，年初要取上年末12月份的
select
'当期' AS time_type
,'余额' AS fee_type
,sum(amt) / 10000 AS AMT
from dm.dm_fim_fud_balance a
where corp_code not in ('0101','0103','0102')
-- AND owner_bank_account NOT IN ('20351059900100000512291'
-- ,'22195101040012008'
-- ,'信用证保证金'
-- ,'22195162750000012'
-- ,'22195162650000017'
-- ,'22195162350000014'
-- ,'22195162050000015'
-- ,'951018033000447470'
-- ,'22195162950000025')
AND a.is_usable = 1
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
-- ${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_mon,'yyyy') +"'","")}
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
-- 月
${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(MONTHDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
-- 日
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >='"+ FORMAT(MONTHDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(MONTHDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
UNION ALL
-- 资金计划与执行
select
'上期' AS time_type
,'收入' AS fee_type -- 收支方向 收入/支出
,SUM(CASE WHEN trade_direction = '收入' THEN amt ELSE 0 END) / 10000 AS amt
from dm.dm_fim_fud_indicator a
where corp_code not in ('0101','0103','0102')
AND trade_direction = '收入'
AND indi_type = '执行'
AND plan_item_name NOT IN ('内部调拨收入','取得贷款')
AND owner_bank_account <> '22195101040012008'
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 上年
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(YEARDELTA(sStart_mon,-1),'yyyy') +"'","")}
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy-MM') <='"+ FORMAT(yeardelta(sStart_mon,-1),'yyyy-MM') +"'","")}
-- 上月
${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') = '"+ FORMAT(MONTHDELTA(sStart_mon,-1),'yyyy-MM') +"'","")}
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(MONTHDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
-- 昨日
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >= '"+ FORMAT(DATEDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(DATEDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
UNION ALL
select
'上期' AS time_type
,'支出' AS fee_type -- 收支方向 收入/支出
,SUM(CASE WHEN trade_direction = '支出' THEN amt ELSE 0 END) / 10000 AS amt
from dm.dm_fim_fud_indicator a
where corp_code not in ('0101','0103','0102')
AND trade_direction = '支出'
AND indi_type = '执行'
AND plan_item_name NOT IN ('内部调拨支出','偿还贷款')
AND owner_bank_account <> '22195101040012008'
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 上年
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(YEARDELTA(sStart_mon,-1),'yyyy') +"'","")}
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy-MM') <='"+ FORMAT(yeardelta(sStart_mon,-1),'yyyy-MM') +"'","")}
-- 上月
${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') = '"+ FORMAT(MONTHDELTA(sStart_mon,-1),'yyyy-MM') +"'","")}
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(MONTHDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
-- 昨日
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >= '"+ FORMAT(DATEDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(DATEDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
UNION ALL
-- 月初资金余额要取上月的月初的，年初要取上年末12月份的
select
'上期' AS time_type
,'余额' AS fee_type
,sum(amt) / 10000 AS AMT
from dm.dm_fim_fud_balance a
where corp_code not in ('0101','0103','0102')
-- AND owner_bank_account NOT IN ('20351059900100000512291'
-- ,'22195101040012008'
-- ,'信用证保证金'
-- ,'22195162750000012'
-- ,'22195162650000017'
-- ,'22195162350000014'
-- ,'22195162050000015'
-- ,'951018033000447470'
-- ,'22195162950000025')
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
AND a.is_usable = 1
-- 上年
-- ${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(YEARDELTA(sStart_mon,-1),'yyyy') +"'","")}
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(YEARDELTA(MONTHDELTA(sStart_mon,-1),-1),'yyyy-MM') +"'","")}
-- 上月
${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(MONTHDELTA(sStart_mon,-1),'yyyy-MM') +"'","")}
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(MONTHDELTA(sStart_date_fin,-2),'yyyy-MM-dd') +"'","")}
-- 昨日
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >= '"+ FORMAT(DATEDELTA(MONTHDELTA(sStart_date_fin,-1),-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(DATEDELTA(MONTHDELTA(sStart_date_fin,-1),-1),'yyyy-MM-dd') +"'","")}
-- ======================================
-- 财务资金月度
-- ======================================
SELECT
t.stat_date AS stat_date
,to_char(t.stat_date,'MM') as month
,CASE WHEN t.fee_type = '收入' THEN amt ELSE 0 END AS revenue_fee -- 收入
,CASE WHEN t.fee_type = '支出' THEN amt ELSE 0 END AS expend_fee -- 支出
,CASE WHEN t.fee_type = '余额' THEN amt ELSE 0 END AS balance_fee -- 余额
FROM (SELECT stat_date
, '收入' AS fee_type -- 收支方向 收入/支出
, SUM(CASE WHEN trade_direction = '收入' THEN amt ELSE 0 END) / 10000 AS amt
FROM dm.dm_fim_fud_indicator a
WHERE corp_code NOT IN ('0101', '0103', '0102')
AND trade_direction = '收入'
AND indi_type = '执行'
AND plan_item_name NOT IN ('内部调拨收入', '取得贷款')
AND owner_bank_account <> '22195101040012008'
-- 权限控制
AND exists(select 1
FROM
dm.dm_bi_user_permisson
WHERE
bi_user = '${fine_username}'
AND per_model IN ('all','sal')
AND (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
${IF(len(sStart_mon) > 0,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_mon,'yyyy') +"'","")}
-- 年
-- ${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date_fin,'yyyy') +"'","")}
-- 月
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') = '"+ FORMAT(sStart_date_fin,'yyyy-MM') +"'","")}
-- 日
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >= '"+ sStart_date_fin +"'","")}
-- ${IF(sPeriod_fin=4,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ sStart_date_fin +"'","")}
GROUP BY stat_date
UNION ALL
SELECT stat_date
, '支出' AS fee_type -- 收支方向 收入/支出
, SUM(CASE WHEN trade_direction = '支出' THEN amt ELSE 0 END) / 10000 AS amt
FROM dm.dm_fim_fud_indicator a
WHERE corp_code NOT IN ('0101', '0103', '0102')
AND trade_direction = '支出'
AND indi_type = '执行'
AND plan_item_name NOT IN ('内部调拨支出', '偿还贷款')
AND owner_bank_account <> '22195101040012008'
-- 权限控制
AND exists(select 1
FROM
dm.dm_bi_user_permisson
WHERE
bi_user = '${fine_username}'
AND per_model IN ('all','sal')
AND (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
${IF(len(sStart_mon) > 0,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_mon,'yyyy') +"'","")}
-- 年
-- ${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date_fin,'yyyy') +"'","")}
-- 月
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date_fin,'yyyy-MM') +"'","")}
-- 日
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >='"+ sStart_date_fin +"'","")}
-- ${IF(sPeriod_fin=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sStart_date_fin +"'","")}
GROUP BY stat_date
UNION ALL
-- 月初资金余额要取上月的月初的，年初要取上年末12月份的
SELECT
stat_date AS stat_date
, '余额' AS fee_type
,SUM(amt) / 10000 AS AMT
FROM dm.dm_fim_fud_balance a
WHERE corp_code NOT IN ('0101', '0103', '0102')
-- AND owner_bank_account NOT IN
-- ('20351059900100000512291', '22195101040012008', '信用证保证金', '22195162750000012', '22195162650000017',
-- '22195162350000014', '22195162050000015', '951018033000447470','22195162950000025')
AND a.is_usable = 1
-- 权限控制
AND EXISTS (SELECT 1
FROM dm.dm_bi_user_permisson
WHERE bi_user = '${fine_username}'
AND per_model IN ('all', 'fim')
AND (
is_all = 1
OR per_code = a.corp_code -- 事业部
))
${IF(len(sStart_mon) > 0,"AND to_char(a.stat_date,'yyyy') ='"+ FORMAT(sStart_mon,'yyyy') +"'","")}
GROUP BY stat_date
)t
${IF(len(sStart_mon) > 0,"where to_char(t.stat_date,'yyyy-MM') <= '"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
ORDER BY to_char(t.stat_date,'MM')
-- ======================================
-- 财务
-- ======================================
WITH amt_base AS
(
-- 映射日报科目对照关系
SELECT
stat_date -- 统计日期
,plan_item_name
,trade_direction
,SUM(amt) AS amt -- 成品酒销售回款
FROM dm.dm_fim_fud_indicator fp
where fp.trade_direction IN('收入','支出')
and fp.indi_type='执行'
AND fp.plan_item_name NOT IN ('内部调拨支出','内部调拨收入')
and EXISTS (
SELECT
1
FROM
dm.dm_bi_user_permisson
WHERE
bi_user = '${fine_username}'
AND per_model IN ('all','fim')
AND (
is_all = 1
OR per_code = fp.corp_code
)
)
${IF(len(sCorp)=0," ","AND fp.corp_code in ('"+sCorp+"')")}
AND stat_date >= date '2023-01-01'
GROUP BY stat_date,plan_item_name,trade_direction
UNION ALL
select
date(concat_ws(',',account_year,account_month,'1')) as stat_date
,'其他收入（含未确认收入）' as plan_item_name
,'收入' AS trade_direction
,losses_amt AS amt
from sdi_hzg.exchange_gains_losses
where 1=1
${IF(len(sCorp)=0," ","AND corp_code in ('"+sCorp+"')")}
)
SELECT
plan_item_name
,trade_direction as fee_class
,SUM(amt) / 10000 AS amt -- 本日
FROM amt_base
WHERE 1=1
AND to_char(stat_date,'yyyy-MM-dd') = '${sEnd_date}'
GROUP BY plan_item_name,trade_direction
-- 期初余额
-- 年
${IF(sPeriod_fin=1,"","/*")}
--本月期初
with md as (
select
bank_account,
max(report_date) as max_report_date
from dwi.dwi_fim_bank_account_balance
where report_date<date_trunc('year',date '${sEnd_date}')
group by bank_account
),xj as (
select
a.corp_code,
a.bank_account,
sum(a.current_balance) as current_balance,
a.currency
from dwi.dwi_fim_bank_account_balance a
where exists (
select 1 from md
where a.bank_account=md.bank_account and a.report_date=md.max_report_date
)
and a.account_state='开户'
group by a.corp_code, a.currency ,a.bank_account
) , rto as ( -- 汇率表
select decode(currency_name,'加拿大元','加元',currency_name) as currency_name,
conversion_price_boc/100 as rate,
date_trunc('day',exchange_publish_time) as rto_date,
row_number() over (partition by currency_name,
date_trunc('day',exchange_publish_time)
order by exchange_publish_time desc) as ridx
from sdi_xdata.boc_exchange_rate
where date_trunc('day',exchange_publish_time)=date_trunc('year',date '${sEnd_date}')-1
)
select
sum(current_balance*nvl(rto.rate,1))/10000 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,0 AS limit_amt -- 受限资金
from xj re
inner join dwr.dim_corp c on re.corp_code=c.corp_code and c.corp_group='股份'
left join rto on re.currency=rto.currency_name and rto.ridx=1
left join dwr.dim_bank_account dba on decode(length(re.bank_account), 17,
substring(re.bank_account, 3),
re.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND if((dba.category_name in ('专用户','保证金户') and re.bank_account!='22195901040000147' ) or re.bank_account='信用证保证金',1,0) = 0
UNION ALL
select
0 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,sum(current_balance*nvl(rto.rate,1))/10000 AS limit_amt -- 受限资金
from xj re
inner join dwr.dim_corp c on re.corp_code=c.corp_code and c.corp_group='股份'
left join rto on re.currency=rto.currency_name and rto.ridx=1
left join dwr.dim_bank_account dba on decode(length(re.bank_account), 17,
substring(re.bank_account, 3),
re.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND if((dba.category_name in ('专用户','保证金户') and re.bank_account!='22195901040000147' ) or re.bank_account='信用证保证金',1,0) = 1
${IF(sPeriod_fin=1,"","*/")}
-- 月
${IF(sPeriod_fin=2,"","/*")}
--本月期初
with md as (
select
bank_account,
max(report_date) as max_report_date
from dwi.dwi_fim_bank_account_balance
where report_date<date_trunc('month',date '${sEnd_date}')
group by bank_account
),xj as (
select
a.corp_code,
a.bank_account,
sum(a.current_balance) as current_balance,
a.currency
from dwi.dwi_fim_bank_account_balance a
where exists (
select 1 from md
where a.bank_account=md.bank_account and a.report_date=md.max_report_date
)
and a.account_state='开户'
group by a.corp_code, a.currency ,a.bank_account
) , rto as ( -- 汇率表
select decode(currency_name,'加拿大元','加元',currency_name) as currency_name,
conversion_price_boc/100 as rate,
date_trunc('day',exchange_publish_time) as rto_date,
row_number() over (partition by currency_name,
date_trunc('day',exchange_publish_time)
order by exchange_publish_time desc) as ridx
from sdi_xdata.boc_exchange_rate
where date_trunc('day',exchange_publish_time)=date_trunc('month', date '${sEnd_date}')-1
)
select
0 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,sum(current_balance*nvl(rto.rate,1))/10000 AS limit_amt -- 受限资金
from xj re
inner join dwr.dim_corp c on re.corp_code=c.corp_code and c.corp_group='股份'
left join rto on re.currency=rto.currency_name and rto.ridx=1
left join dwr.dim_bank_account dba on decode(length(re.bank_account), 17,
substring(re.bank_account, 3),
re.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND if((dba.category_name in ('专用户','保证金户') and re.bank_account!='22195901040000147' ) or re.bank_account='信用证保证金',1,0) = 1
UNION ALL
select
0 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,sum(current_balance*nvl(rto.rate,1))/10000 AS hk_amt -- 香港公司账户资金
,0 AS limit_amt -- 受限资金
from xj re
inner join dwr.dim_corp c on re.corp_code=c.corp_code and c.corp_group='股份'
left join rto on re.currency=rto.currency_name and rto.ridx=1
left join dwr.dim_bank_account dba on decode(length(re.bank_account), 17,
substring(re.bank_account, 3),
re.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND re.corp_code = '0223'
UNION ALL
SELECT
0 as free_amt -- 其中：可用存款
, sum(balance) / 10000 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,0 AS limit_amt -- 受限资金
from dwr.ag_fim_fud_draft_balance a
where stat_date=date_trunc('month',add_months(date'${sEnd_date}',-1))
${IF(len(sCorp)=0," ","AND a.corp_code in ('"+sCorp+"')")}
and exists(
select 1 from dwr.dim_corp cp
where a.corp_code=cp.corp_code
and cp.corp_group='股份'
)
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
${IF(sPeriod_fin=2,"","*/")}
-- 日
${IF(sPeriod_fin=3,"","/*")}
with tpj as (
select
a.corp_code,
'收入' as trade_direction,
draftcode,
a.owner_bank_account,
sum(a.amt) as amt
from sdi_oth.fim_draft_balance a
group by a.corp_code,
draftcode,
owner_bank_account
union all
select
a.corp_code,
trade_direction,
a.draftcode,
a.owner_bank_account,
amt
from dwr.fact_fim_draft_flow a
where a.trade_time>=date'2024-08-01'
and a.trade_time<date'${FORMAT(sEnd_date,"yyyy-MM-dd")}'
and nvl(a.draft_state,'x') not in ('未签收','背书中','登记中','已作废','承兑中','出票中','贴现中','质押中','收款中','被拒付','拒绝签收')
) ,pj as ( -- 票据余额
select
a.corp_code,
sum(decode(trade_direction,'支出',-1*amt,amt)) as balance
from tpj a
inner join dwr.dim_corp c on a.corp_code =c.corp_code and c.corp_group='股份'
group by a.corp_code
having sum(decode(trade_direction,'支出',-1*amt,amt))>0
), md as (
select
bank_account,
max(report_date) as max_report_date
from dwi.dwi_fim_bank_account_balance
where report_date<date'${FORMAT(sEnd_date,"yyyy-MM-dd")}'
group by bank_account
),re as ( -- 汇总票据和银行余额
select
a.corp_code,
a.current_balance,
a.currency,
'现金' as amt_type,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0) as is_limit--受限资金金额
from dwi.dwi_fim_bank_account_balance a
inner join dwr.dim_corp c on a.corp_code=c.corp_code and c.corp_group='股份'
left join dwr.dim_bank_account dba on decode(length(a.bank_account), 17,
substring(a.bank_account, 3),
a.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
where exists (
select 1 from md
where a.bank_account=md.bank_account and a.report_date=md.max_report_date
)
and a.account_state='开户'
union all
select
corp_code,
sum(balance) as current_balance,
'人民币' as currency,
'票据' as amt_type,
0 as is_limit
from pj
group by corp_code),
rto as ( -- 汇率表
select decode(currency_name,'加拿大元','加元',currency_name) as currency_name,
conversion_price_boc/100 as rate,
date_trunc('day',exchange_publish_time) as rto_date,
row_number() over (partition by currency_name,
date_trunc('day',exchange_publish_time)
order by exchange_publish_time desc) as ridx
from sdi_xdata.boc_exchange_rate
where date_trunc('day',exchange_publish_time)=date'${FORMAT(sEnd_date,"yyyy-MM-dd")}'
)
select
current_balance*nvl(rto.rate,1)/10000 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,0 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND is_limit = 0
AND amt_type = '现金'
AND corp_code <> '0223'
UNION ALL
select
0 as free_amt -- 其中：可用存款
, current_balance*nvl(rto.rate,1)/10000 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,0 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND amt_type = '票据'
UNION ALL
select
0 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,current_balance*nvl(rto.rate,1)/10000 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND is_limit = 1
AND amt_type = '现金'
UNION ALL
select
0 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,current_balance*nvl(rto.rate,1)/10000 AS hk_amt -- 香港公司账户资金
,0 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND is_limit = 0
AND amt_type = '现金'
AND corp_code = '0223'
${IF(sPeriod_fin=3,"","*/")}
-- 期末
-- 年
${IF(sPeriod_fin=1,"","/*")}
with tpj as (
select
a.corp_code,
'收入' as trade_direction,
draftcode,
a.owner_bank_account,
sum(a.amt) as amt
from sdi_oth.fim_draft_balance a
group by a.corp_code,
draftcode,
owner_bank_account
union all
select
a.corp_code,
trade_direction,
a.draftcode,
a.owner_bank_account,
amt
from dwr.fact_fim_draft_flow a
where a.trade_time>=date'2024-08-01'
and a.trade_time< date'${sEnd_date}' + interval '1 day'
and nvl(a.draft_state,'x') not in ('未签收','背书中','登记中','已作废','承兑中','出票中','贴现中','质押中','收款中','被拒付','拒绝签收')
) ,pj as ( -- 票据余额
select
a.corp_code,
sum(decode(trade_direction,'支出',-1*amt,amt)) as balance
from tpj a
inner join dwr.dim_corp c on a.corp_code =c.corp_code and c.corp_group='股份'
group by a.corp_code
having sum(decode(trade_direction,'支出',-1*amt,amt))>0
), md as (
select
bank_account,
max(report_date) as max_report_date
from dwi.dwi_fim_bank_account_balance
where report_date< date'${sEnd_date}' + interval '1 day'
group by bank_account
),xj as (
select
a.corp_code,
a.bank_account,
sum(a.current_balance) as amt,
a.currency
from dwi.dwi_fim_bank_account_balance a
where exists (
select 1 from md
where a.bank_account=md.bank_account and a.report_date=md.max_report_date
)
and a.account_state='开户'
group by a.corp_code, a.currency ,a.bank_account
union all
select corp_code,
owner_bank_account as bank_account,
sum(decode(trade_direction,'收入',amt,-1*amt)) as amt,
'人民币' as currency
from dm.dm_fim_fud_indicator
where amt_type='现金'
and stat_date=date'${sEnd_date}'
group by corp_code,owner_bank_account
) ,re as ( -- 汇总票据和银行余额
select
a.corp_code,
sum(a.amt) as current_balance,
a.currency,
'现金' as amt_type,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0) as is_limit--受限资金金额
from xj a
inner join dwr.dim_corp c on a.corp_code=c.corp_code and c.corp_group='股份'
left join dwr.dim_bank_account dba on decode(length(a.bank_account), 17,
substring(a.bank_account, 3),
a.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
group by a.corp_code,
a.currency,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0)
union all
select
corp_code,
sum(balance) as current_balance,
'人民币' as currency,
'票据' as amt_type,
0 as is_limit
from pj
group by corp_code),
rto as ( -- 汇率表
select decode(currency_name,'加拿大元','加元',currency_name) as currency_name,
conversion_price_boc/100 as rate,
date_trunc('day',exchange_publish_time) as rto_date,
row_number() over (partition by currency_name,
date_trunc('day',exchange_publish_time)
order by exchange_publish_time desc) as ridx
from sdi_xdata.boc_exchange_rate
where date_trunc('day',exchange_publish_time)=date'${sEnd_date}'
)
SELECT 0 AS free_amt -- 其中：可用存款
, 0 AS bank_amt -- 银行承兑汇票
, current_balance*nvl(rto.rate,1)/10000 AS hk_amt -- 香港公司账户资金
, 0 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND corp_code = '0223'
AND amt_type = '现金'
UNION ALL
SELECT 0 AS free_amt -- 其中：可用存款
, 0 AS bank_amt -- 银行承兑汇票
, 0 AS hk_amt -- 香港公司账户资金
, current_balance*nvl(rto.rate,1)/10000 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND is_limit = 1
AND amt_type = '现金'
UNION ALL
SELECT
0 AS free_amt -- 其中：可用存款
, sum(balance)/10000 AS bank_amt -- 银行承兑汇票
, 0 AS hk_amt -- 香港公司账户资金
, 0 AS limit_amt -- 受限资金
from dwr.ag_fim_fud_draft_balance a
where stat_date=date_trunc('month',date '${sEnd_date}')
${IF(len(sCorp)=0," ","AND a.corp_code in ('"+sCorp+"')")}
and exists(
select 1 from dwr.dim_corp cp
where a.corp_code=cp.corp_code
and cp.corp_group='股份'
)
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
${IF(sPeriod_fin=1,"","*/")}
-- 月
${IF(sPeriod_fin=2,"","/*")}
with tpj as (
select
a.corp_code,
'收入' as trade_direction,
draftcode,
a.owner_bank_account,
sum(a.amt) as amt
from sdi_oth.fim_draft_balance a
group by a.corp_code,
draftcode,
owner_bank_account
union all
select
a.corp_code,
trade_direction,
a.draftcode,
a.owner_bank_account,
amt
from dwr.fact_fim_draft_flow a
where a.trade_time>=date'2024-08-01'
and a.trade_time< date'${sEnd_date}' + interval '1 day'
and nvl(a.draft_state,'x') not in ('未签收','背书中','登记中','已作废','承兑中','出票中','贴现中','质押中','收款中','被拒付','拒绝签收')
) ,pj as ( -- 票据余额
select
a.corp_code,
sum(decode(trade_direction,'支出',-1*amt,amt)) as balance
from tpj a
inner join dwr.dim_corp c on a.corp_code =c.corp_code and c.corp_group='股份'
group by a.corp_code
having sum(decode(trade_direction,'支出',-1*amt,amt))>0
), md as (
select
bank_account,
max(report_date) as max_report_date
from dwi.dwi_fim_bank_account_balance
where report_date< date'${sEnd_date}' + interval '1 day'
group by bank_account
),xj as (
select
a.corp_code,
a.bank_account,
sum(a.current_balance) as amt,
a.currency
from dwi.dwi_fim_bank_account_balance a
where exists (
select 1 from md
where a.bank_account=md.bank_account and a.report_date=md.max_report_date
)
and a.account_state='开户'
group by a.corp_code, a.currency ,a.bank_account
union all
select corp_code,
owner_bank_account as bank_account,
sum(decode(trade_direction,'收入',amt,-1*amt)) as amt,
'人民币' as currency
from dm.dm_fim_fud_indicator
where amt_type='现金'
and stat_date=date'${sEnd_date}'
group by corp_code,owner_bank_account
) ,re as ( -- 汇总票据和银行余额
select
a.corp_code,
sum(a.amt) as current_balance,
a.currency,
'现金' as amt_type,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0) as is_limit--受限资金金额
from xj a
inner join dwr.dim_corp c on a.corp_code=c.corp_code and c.corp_group='股份'
left join dwr.dim_bank_account dba on decode(length(a.bank_account), 17,
substring(a.bank_account, 3),
a.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
group by a.corp_code,
a.currency,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0)
union all
select
corp_code,
sum(balance) as current_balance,
'人民币' as currency,
'票据' as amt_type,
0 as is_limit
from pj
group by corp_code),
rto as ( -- 汇率表
select decode(currency_name,'加拿大元','加元',currency_name) as currency_name,
conversion_price_boc/100 as rate,
date_trunc('day',exchange_publish_time) as rto_date,
row_number() over (partition by currency_name,
date_trunc('day',exchange_publish_time)
order by exchange_publish_time desc) as ridx
from sdi_xdata.boc_exchange_rate
where date_trunc('day',exchange_publish_time)=date'${sEnd_date}'
)
SELECT 0 AS free_amt -- 其中：可用存款
, 0 AS bank_amt -- 银行承兑汇票
, current_balance*nvl(rto.rate,1)/10000 AS hk_amt -- 香港公司账户资金
, 0 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND corp_code = '0223'
AND amt_type = '现金'
UNION ALL
SELECT 0 AS free_amt -- 其中：可用存款
, 0 AS bank_amt -- 银行承兑汇票
, 0 AS hk_amt -- 香港公司账户资金
, current_balance*nvl(rto.rate,1)/10000 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND is_limit = 1
AND amt_type = '现金'
UNION ALL
SELECT
0 AS free_amt -- 其中：可用存款
, sum(balance)/10000 AS bank_amt -- 银行承兑汇票
, 0 AS hk_amt -- 香港公司账户资金
, 0 AS limit_amt -- 受限资金
from dwr.ag_fim_fud_draft_balance a
where stat_date=date_trunc('month',date '${sEnd_date}')
and exists(
select 1 from dwr.dim_corp cp
where a.corp_code=cp.corp_code
and cp.corp_group='股份'
)
${IF(len(sCorp)=0," ","AND a.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
${IF(sPeriod_fin=2,"","*/")}
-- 日
${IF(sPeriod_fin=3,"","/*")}
with tpj as (
select
a.corp_code,
'收入' as trade_direction,
draftcode,
a.owner_bank_account,
sum(a.amt) as amt
from sdi_oth.fim_draft_balance a
group by a.corp_code,
draftcode,
owner_bank_account
union all
select
a.corp_code,
trade_direction,
a.draftcode,
a.owner_bank_account,
amt
from dwr.fact_fim_draft_flow a
where a.trade_time>=date'2024-08-01'
and a.trade_time< date'${sEnd_date}' + interval '1 day'
and nvl(a.draft_state,'x') not in ('未签收','背书中','登记中','已作废','承兑中','出票中','贴现中','质押中','收款中','被拒付','拒绝签收')
) ,pj as ( -- 票据余额
select
a.corp_code,
sum(decode(trade_direction,'支出',-1*amt,amt)) as balance
from tpj a
inner join dwr.dim_corp c on a.corp_code =c.corp_code and c.corp_group='股份'
group by a.corp_code
having sum(decode(trade_direction,'支出',-1*amt,amt))>0
), md as (
select
bank_account,
max(report_date) as max_report_date
from dwi.dwi_fim_bank_account_balance
where report_date< date'${sEnd_date}' + interval '1 day'
group by bank_account
),xj as (
select
a.corp_code,
a.bank_account,
sum(a.current_balance) as amt,
a.currency
from dwi.dwi_fim_bank_account_balance a
where exists (
select 1 from md
where a.bank_account=md.bank_account and a.report_date=md.max_report_date
)
and a.account_state='开户'
group by a.corp_code, a.currency ,a.bank_account
union all
select corp_code,
owner_bank_account as bank_account,
sum(decode(trade_direction,'收入',amt,-1*amt)) as amt,
'人民币' as currency
from dm.dm_fim_fud_indicator
where amt_type='现金'
and stat_date=date'${sEnd_date}'
group by corp_code,owner_bank_account
) ,re as ( -- 汇总票据和银行余额
select
a.corp_code,
sum(a.amt) as current_balance,
a.currency,
'现金' as amt_type,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0) as is_limit--受限资金金额
from xj a
inner join dwr.dim_corp c on a.corp_code=c.corp_code and c.corp_group='股份'
left join dwr.dim_bank_account dba on decode(length(a.bank_account), 17,
substring(a.bank_account, 3),
a.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
group by a.corp_code,
a.currency,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0)
union all
select
corp_code,
sum(balance) as current_balance,
'人民币' as currency,
'票据' as amt_type,
0 as is_limit
from pj
group by corp_code),
rto as ( -- 汇率表
select decode(currency_name,'加拿大元','加元',currency_name) as currency_name,
conversion_price_boc/100 as rate,
date_trunc('day',exchange_publish_time) as rto_date,
row_number() over (partition by currency_name,
date_trunc('day',exchange_publish_time)
order by exchange_publish_time desc) as ridx
from sdi_xdata.boc_exchange_rate
where date_trunc('day',exchange_publish_time)=date'${sEnd_date}'
)
SELECT 0 AS free_amt -- 其中：可用存款
, 0 AS bank_amt -- 银行承兑汇票
, current_balance*nvl(rto.rate,1)/10000 AS hk_amt -- 香港公司账户资金
, 0 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND corp_code = '0223'
AND amt_type = '现金'
UNION ALL
SELECT 0 AS free_amt -- 其中：可用存款
, 0 AS bank_amt -- 银行承兑汇票
, 0 AS hk_amt -- 香港公司账户资金
, current_balance*nvl(rto.rate,1)/10000 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND is_limit = 1
AND amt_type = '现金'
UNION ALL
SELECT
0 AS free_amt -- 其中：可用存款
, sum(balance)/10000 AS bank_amt -- 银行承兑汇票
, 0 AS hk_amt -- 香港公司账户资金
, 0 AS limit_amt -- 受限资金
from dwr.ag_fim_fud_draft_balance a
where stat_date=date_trunc('month',date '${sEnd_date}')
and exists(
select 1 from dwr.dim_corp cp
where a.corp_code=cp.corp_code
and cp.corp_group='股份'
)
${IF(len(sCorp)=0," ","AND a.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
${IF(sPeriod_fin=3,"","*/")}
Reference in New Issue View Git Blame Copy Permalink