def main():
conn = get_connection().oracle_connection()
yesterday = fixed_params().yesterday
# day_weekbefore = fixed_params().day_weekbefore
sel_sql1 = '''select distinct dim_id from DIM_MARKET_ID'''
sel_sql2 = '''select dim_id, erdat, close_price
from DIM_MARKET_PRICE
where dim_id='%s' --617100 615600 622600
and erdat between '20200101'
and '20200423'
order by erdat'''
ins_sql = '''insert into DIM_MARKET_PRICE_FORMULA (dim_id_dr, erdat, calced_price, dim_id)values('%s', '%s', %s, '%s')'''
result1 = ('615300', '615400')
for i in range(0, len(result1)):
all_data = sql().sql_req(conn, result1[i], sql=sel_sql2)
if all_data:
all_results = time_list_new(all_data).result_cal()
sql().sql_req(conn, all_results[i], sql=ins_sql)
get_connection().close_connection(conn)
def main():
conn = get_connection().oracle_connection()
yesterday = fixed_params().yesterday
day_weekbefore = fixed_params().day_weekbefore
sel_sql1 = '''select distinct dim_id from DIM_MARKET_ID'''
sel_sql2 = '''select dim_id, erdat, close_price
from DIM_MARKET_PRICE
where dim_id='%s' --617100 615600 622600
and erdat between to_char(add_months(to_date('%s','yyyymmdd'),-12),'yyyymmdd')
and '%s'
order by erdat'''
ins_sql = '''insert into DIM_MARKET_PRICE_FORMULA (dim_id_dr, erdat, calced_price, dim_id)values('%s', '%s', %s, '%s')'''
result1 = sql().sql_req(conn, sql=sel_sql1)
for i in range(0, len(result1)):
all_data = sql().sql_req(conn, (result1[i][0], yesterday, yesterday),
sql=sel_sql2)
if all_data:
all_results = time_list_new(all_data).result_cal()
for i in range(0, len(all_results)):
if all_results[i][1] >= day_weekbefore:
sql().sql_req(conn, all_results[i], sql=ins_sql)
get_connection().close_connection(conn)
def main():
try:
# 定义today为当前日期 ny为年月
today = datetime.date.today().strftime('%Y-%m-%d')
ny = datetime.date.today().strftime('%Y-%m')
yeday = datetime.datetime.strptime(today, '%Y-%m-%d') + datetime.timedelta(days=-4)
yeday = yeday.strftime('%Y-%m-%d')
# 设定传递参数，依次为昨日，今日和年月
ccf_cookie = str(get_market_values().ccf_cookie())
ccfei_cookie = str(get_market_values().ccfei_cookie())
icis_cookie = get_market_values().icis_cookie()
param = [yeday, today, ny, ccf_cookie, ccfei_cookie, icis_cookie]
sqltuple = get_market_values().parse_concurrent(param)
# 定义连接，并插入数据
conn = get_connection().hana_connection()
ins_sql = '''insert into"COMMON"."XFM_MARKT" (ZB , ERDAT ,SPJ) values ('%s','%s',%s)'''
sql().sql_req(conn, tuple(sqltuple), sql=ins_sql)
get_connection().close_connection(conn)
except Exception as e:
print(e)
wechat_auto().send_mesg(0, 'person', 13)
def main():
# 定义hana连接
conn = get_connection().hana_connection()
today = fixed_params().today
yesterday = fixed_params().yesterday
# 原料库存
sel_sql1 = '''
select distinct bukrs,gsjc,erdat,kc,wljc,licha
from(select bukrs,
gsjc,--公司简称
'%s' as erdat,--日期
(kc+ykc)as kc,--库存
case when wllx='60018629'then 'MEG'
when wllx='60018628'then 'PTA'end as wljc,--物料简称
licha--批号描述
from(select m.wllx,
m.werks,
t.gsjc,
t.bukrs,
m.charg,
m.kc,--上月库存
ifnull(n.kc,0)as ykc,--当月库存
m.maktx,--物料描述
m.licha--批号描述
from(select distinct wllx,--物料类型
werks,--工厂
charg,--批号
year,--年
month,--月
kc,--库存
maktx,--物料描述
licha--批号描述
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.MM/HMXFMMM09"
where year=left(to_char(add_months('%s',-1),'yyyymm'),4)
and month=right(to_char(add_months('%s',-1),'yyyymm'),2)
and charg<>'FHCHUANG')m
left join
(select wllx,--物料类型
werks,--工厂
charg,--批号
sum(kc)as kc--库存
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.MM/HMXFMMM10"
where erdat between left('%s',6)||'01' and '%s'
group by wllx,--物料类型
werks,--工厂
charg--批号
)n
on m.wllx=n.wllx
and m.werks=n.werks
and m.charg=n.charg
left join
(select distinct gsjc,--公司简称
gc,--工厂
bukrs
from"COMMON"."XFMPM_CJLX"
union all
select distinct'集团总公司'as gsjc,
'HQM1'as gc,
'1000'as bukrs
from"COMMON"."XFMPM_CJLX")t
on m.werks=t.gc))where kc<>0
'''
del_sql1 = '''delete from"COMMON"."XFMMM_KC"where erdat='%s' '''
ins_sql1 = '''insert into"COMMON"."XFMMM_KC"values('%s','%s','%s',%s,'%s','%s')'''
# PTA,PX库存
sel_sql2 = '''
select to_char(add_days('%s',-1),'yyyymmdd') as erdat,'PTA'as lx,round(sum(zcl/1000),2)as kc from(
select budat,sum(stock_qty)as zcl
from"_SYS_BIC"."XFMGROUP.EDW.01_BASAL_LAYER.MM/MATDOC"
where werks='DSC1'
and bwart in('101','102','601','602')
and budat between '20191101'and to_char(add_days('%s',-1),'yyyymmdd')
group by budat
order by budat)
union all
select to_char(add_days('%s',-1),'yyyymmdd') as erdat,'PX'as lx,round(sum(zcl/1000),2)as kc from(
select budat,sum(stock_qty)as zcl
from"_SYS_BIC"."XFMGROUP.EDW.01_BASAL_LAYER.MM/MATDOC"
where werks='DSM1'
and right(matnr,8)='60053974'
and left(lgort,1)<>'C'
and budat <= to_char(add_days('%s',-1),'yyyymmdd')
group by budat
order by budat)
'''
ins_sql2 = '''insert into"COMMON"."XFMSC_CLKC"values('%s','%s',%s)'''
# 取PTA负荷
sel_sql3 = '''
select '%s'as erdat,instr_vad_val as fh
from"XFMMES30"."IF_INSTRDATA_T"
where instr_id='26200193'
and read_time in(select max(read_time)
from"XFMMES30"."IF_INSTRDATA_T"
where instr_id='26200193'
and to_char(read_time,'yyyymmdd')<='%s'
and instr_vad_val<>0)
'''
del_sql3 = '''delete from"COMMON"."XFM_PTA_FH"where erdat='%s' '''
ins_sql3 = '''insert into"COMMON"."XFM_PTA_FH"values(%s,%s)'''
# 生产管理报表大屏展示
sel_sql4 = '''
select '%s' as erdat,'%s' as crrq,'产量总览'as zb,'日计划'as ms,' 'as mx,zcl as sl1,rjh as sl2
from(
select round(sum(sl),2)as zcl,--总产量
sum(clrjh)as rjh--日计划
from(select distinct calday,xcxmc,clrjh,sum(sl)as sl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC05"
group by calday,xcxmc,clrjh)
where calday= '%s')
union all
select left('%s',6) as erdat,'%s' as crrq,'产量总览'as zb,'月计划'as ms,' 'as mx,zcl as sl1,yjh as sl2
from(
select round(sum(sl),2)as zcl,--总产量
sum(clyjh) as yjh--月计划
from(select distinct left(calday,6)as calmonth,xcxmc,clyjh,sum(sl)as sl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC05"
where calday between left('%s',6)||'01'and '%s'
group by left(calday,6),xcxmc,clyjh)
)
union all
select left('%s',4) as erdat,'%s' as crrq,'产量总览'as zb,'年计划'as ms,' 'as mx,zcl as sl1,njh as sl2
from(
select round(sum(sl),2)as zcl,--总产量
sum(clnjh) as njh--年计划
from(select distinct left(calday,4)as calyear,xcxmc,clnjh,sum(sl)as sl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC05"
where calday between left('%s',4)||'0101'and '%s'
group by left(calday,4),xcxmc,clnjh)
)
union all
select '%s' as erdat,'%s' as crrq,'公司间产量日对比'as zb,gsjc as ms,' 'as mx,cl as sl1,0 as sl2
from(
select gsjc,--公司简称
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by gsjc
)
union all
select left('%s',6)as erdat,'%s' as crrq,'公司间产量月对比'as zb,gsjc as ms,' 'as mx,cl as sl1,0 as sl2
from(
select gsjc,--公司简称
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by gsjc
)
union all
select '%s' as erdat,'%s' as crrq,'装置间产量日对比'as zb,xcxmc as ms,' 'as mx,cl as sl1,0 as sl2
from(
select xcxmc,--装置号
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by xcxmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置间产量月对比'as zb,xcxmc as ms,' 'as mx,cl as sl1,0 as sl2
from(
select xcxmc,--装置号
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc
)
union all
select '%s' as erdat,'%s' as crrq,'产品类别日占比'as zb,wplx as ms,' 'as mx,zb as sl1,0 as sl2
from(
select wplx,--物品类型
round(sum(sl)/zsl,3)as zb--占比
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s')
where bzrq='%s'
group by wplx,zsl
)
union all
select left('%s',6) as erdat,'%s' as crrq,'产品类别月占比'as zb,wplx as ms,' 'as mx,zb as sl1,0 as sl2
from(
select wplx,--物品类型
round(sum(sl)/zsl,3)as zb--占比
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s')
where bzrq between left('%s',6)||'01'and '%s'
group by wplx,zsl
)
-------------------------------------------------------------------------------------------------------------产量（总览）
union all
select '%s' as erdat,'%s' as crrq,'公司产量日占比'as zb,gsjc as ms,' 'as mx,zb as sl1,0 as sl2
from(
select gsjc,--公司简称
round(sum(sl)/zsl,3)as zb--占比
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s')
where bzrq='%s'
group by gsjc,zsl
)
union all
select left('%s',6)as erdat,'%s' as crrq,'公司产量月占比'as zb,gsjc as ms,' 'as mx,zb as sl1,0 as sl2
from(
select gsjc,--公司简称
round(sum(sl)/zsl,3)as zb--占比
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s')
where bzrq between left('%s',6)||'01'and '%s'
group by gsjc,zsl
)
union all
select '%s' as erdat,'%s' as crrq,'单公司产品日占比'as zb,gsjc as ms,wplx as mx,cl as sl1,0 as sl2
from(
select gsjc,
wplx,--公司简称
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by gsjc,wplx
)
union all
select left('%s',6) as erdat,'%s' as crrq,'单公司产品月占比'as zb,gsjc as ms,wplx as mx,cl as sl1,0 as sl2
from(
select gsjc,
wplx,--公司简称
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by gsjc,wplx
)
union all
select '%s' as erdat,'%s' as crrq,'公司日同环比'as zb,gsjc as ms,' 'as mx,hb as sl1,tb as sl2
from(
select a.gsjc,
case when ifnull(b.cl,0)=0 then 1
else round((a.cl-b.cl)/b.cl,3)end as hb,--环比
case when ifnull(c.cl,0)=0 then 1
else round((a.cl-c.cl)/c.cl,3)end as tb--同比
from(
select gsjc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by gsjc)a,
(
select gsjc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq=to_char(add_months('%s',-1),'yyyymmdd')
group by gsjc)b,
(select gsjc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq=to_char(add_years('%s',-1),'yyyymmdd')
group by gsjc)c
where a.gsjc=b.gsjc
and a.gsjc=c.gsjc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'公司月同环比'as zb,gsjc as ms,' 'as mx,hb as sl1,tb as sl2
from(
select a.gsjc,
case when ifnull(b.cl,0)=0 then 1
else round((a.cl-b.cl)/b.cl,3)end as hb,--环比
case when ifnull(c.cl,0)=0 then 1
else round((a.cl-c.cl)/c.cl,3)end as tb--同比
from(
select gsjc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by gsjc)a,
(
select gsjc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left(to_char(add_months('%s',-1),'yyyymmdd'),6)||'01'and to_char(add_months('%s',-1),'yyyymmdd')
group by gsjc)b,
(select gsjc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left(to_char(add_years('%s',-1),'yyyymmdd'),6)||'01'and to_char(add_years('%s',-1),'yyyymmdd')
group by gsjc)c
where a.gsjc=b.gsjc
and a.gsjc=c.gsjc
)
union all
select bzrq as erdat,'%s' as crrq,'单公司产量日趋势'as zb,gsjc as ms,' 'as mx,cl as sl1,dqfh as sl2
from(
select bzrq, --日期
a.gsjc,
round(sum(sl),2)as cl,--产量
round(dqfh,2)as dqfh--当前负荷
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"a,
(select gsjc,sum(dqfh)as dqfh from"COMMON"."XFMSC_DQFH"group by gsjc)b
where a.gsjc=b.gsjc
and bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,dqfh,a.gsjc
order by bzrq
)
union all
select bzrq as erdat,'%s' as crrq,'单公司产量月趋势'as zb,gsjc as ms,' 'as mx,cl as sl1,dqfh as sl2
from(
select left(bzrq,6)as bzrq, --月
a.gsjc,
round(sum(sl),2)as cl,--产量
round(dqfh,2)as dqfh--当前负荷
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"a,
(select gsjc,sum(dqfh)as dqfh from"COMMON"."XFMSC_DQFH"group by gsjc)b
where a.gsjc=b.gsjc
and bzrq between left(to_char(add_years('%s',-1),'yyyymmdd'),6)||'01'and '%s'
group by left(bzrq,6),dqfh,a.gsjc
)
-----------------------------------------------------------------------------------------------------------产量（公司间）
union all
select '%s' as erdat,'%s' as crrq,'装置产量日占比'as zb,xcxmc as ms,' 'as mx,zb as sl1,0 as sl2
from(
select xcxmc,--装置名称
round(sum(sl)/zsl,3)as zb
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s')
where bzrq='%s'
group by xcxmc,zsl
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置产量月占比'as zb,xcxmc as ms,' 'as mx,zb as sl1,0 as sl2
from(
select xcxmc,--装置名称
round(sum(sl)/zsl,3)as zb
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s')
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zsl
)
union all
select '%s' as erdat,'%s' as crrq,'单装置产量日占比'as zb,xcxmc as ms,wplx as mx,cl as sl1,0 as sl2
from(
select wplx,--物品类型
xcxmc,
round(sum(sl),2)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by wplx,xcxmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'单装置产量月占比'as zb,xcxmc as ms,wplx as mx,cl as sl1,0 as sl2
from(
select wplx,--物品类型
xcxmc,
round(sum(sl),2)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by wplx,xcxmc
)
union all
select '%s' as erdat,'%s' as crrq,'单装置日同环比'as zb,xcxmc as ms,' 'as mx,hb as sl1,tb as sl2
from(
select a.xcxmc,
case when ifnull(b.cl,0)=0 then 1
else round((a.cl-b.cl)/b.cl,3)end as hb,--环比
case when ifnull(c.cl,0)=0 then 1
else round((a.cl-c.cl)/c.cl,3)end as tb--同比
from(
select xcxmc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by xcxmc)a,
(
select xcxmc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq=to_char(add_months('%s',-1),'yyyymmdd')
group by xcxmc)b,
(select xcxmc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq=to_char(add_years('%s',-1),'yyyymmdd')
group by xcxmc)c
where a.xcxmc=b.xcxmc
and a.xcxmc=c.xcxmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'单装置月同环比'as zb,xcxmc as ms,' 'as mx,hb as sl1,tb as sl2
from(
select a.xcxmc,
case when ifnull(b.cl,0)=0 then 1
else round((a.cl-b.cl)/b.cl,3)end as hb,--环比
case when ifnull(c.cl,0)=0 then 1
else round((a.cl-c.cl)/c.cl,3)end as tb--同比
from(
select xcxmc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc)a,
(
select xcxmc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left(to_char(add_months('%s',-1),'yyyymmdd'),6)||'01'and to_char(add_months('%s',-1),'yyyymmdd')
group by xcxmc)b,
(select xcxmc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left(to_char(add_years('%s',-1),'yyyymmdd'),6)||'01'and to_char(add_years('%s',-1),'yyyymmdd')
group by xcxmc)c
where a.xcxmc=b.xcxmc
and a.xcxmc=c.xcxmc
)
union all
select bzrq as erdat,'%s' as crrq,'单装置产量日趋势'as zb,xcxmc as ms,' 'as mx,cl as sl1,dqfh as sl2
from(
select bzrq, --日期
a.xcxmc,
round(sum(sl),2)as cl,--产量
round(dqfh,2)as dqfh--当前负荷
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"a,
(select cxmc,sum(dqfh)as dqfh from"COMMON"."XFMSC_DQFH"group by cxmc)b
where a.xcxmc=b.cxmc
and bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,dqfh,a.xcxmc
order by bzrq
)
union all
select bzrq as erdat,'%s' as crrq,'单装置产量月趋势'as zb,xcxmc as ms,' 'as mx,cl as sl1,dqfh as sl2
from(
select left(bzrq,6)as bzrq, --月
a.xcxmc,
round(sum(sl),2)as cl,--产量
round(dqfh,2)as dqfh--当前负荷
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"a,
(select cxmc,sum(dqfh)as dqfh from"COMMON"."XFMSC_DQFH"group by cxmc)b
where a.xcxmc=b.cxmc
and bzrq between left(to_char(add_years('%s',-1),'yyyymmdd'),6)||'01'and '%s'
group by left(bzrq,6),dqfh,a.xcxmc
)
-----------------------------------------------------------------------------------------------------------产量（装置间）
union all
select '%s' as erdat,'%s' as crrq,'产品大类别日占比'as zb,wplx as ms,' 'as mx,zb as sl1,cl as sl2
from(
select wplx,--物品类型
round(sum(sl)/zsl,3)as zb,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s')
where bzrq='%s'
group by wplx,zsl
)
union all
select left('%s',6) as erdat,'%s' as crrq,'产品大类别月占比'as zb,wplx as ms,' 'as mx,zb as sl1,cl as sl2
from(
select wplx,--物品类型
round(sum(sl)/zsl,3)as zb,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s')
where bzrq between left('%s',6)||'01'and '%s'
group by wplx,zsl
)
union all
select '%s' as erdat,'%s' as crrq,'单产品装置日分布'as zb,wplx as ms,xcxmc as mx,cl as sl1,0 as sl2
from(
select wplx,--物品类型
xcxmc,
round(sum(sl),2)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by wplx,xcxmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'单产品装置月分布'as zb,wplx as ms,xcxmc as mx,cl as sl1,0 as sl2
from(
select wplx,--物品类型
xcxmc,
round(sum(sl),2)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by wplx,xcxmc
)
union all
select bzrq as erdat,'%s' as crrq,'单产品产量日趋势'as zb,wplx as ms,' 'as mx,cl as sl1,0 as sl2
from(
select bzrq, --日期
wplx,
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,wplx
order by bzrq
)
union all
select bzrq as erdat,'%s' as crrq,'单产品产量月趋势'as zb,wplx as ms,' 'as mx,cl as sl1,0 as sl2
from(
select left(bzrq,6)as bzrq, --月
wplx,
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6),wplx
)
union all
select '%s' as erdat,'%s' as crrq,'产品品种描述日占比'as zb,wplx as ms,wpmc as mx,zb as sl1,0 as sl2
from(
select a.wpmc,a.wplx,round(sum(sl/1000)/zsl,3)as zb
from"_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SC/HCXFMSC09"a,
(select wplx,sum(sl/1000)as zsl from"_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SC/HCXFMSC09"
where bzrq='%s'group by wplx)b
where a.wplx=b.wplx
and bzrq='%s'
group by wpmc,a.wplx,zsl
)
union all
select left('%s',6)as erdat,'%s' as crrq,'产品品种描述月占比'as zb,wplx as ms,wpmc as mx,zb as sl1,0 as sl2
from(
select a.wpmc,a.wplx,round(sum(sl/1000)/zsl,3)as zb
from"_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SC/HCXFMSC09"a,
(select wplx,sum(sl/1000)as zsl from"_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SC/HCXFMSC09"
where bzrq between left('%s',6)||'01'and '%s'group by wplx)b
where bzrq between left('%s',6)||'01'and '%s'
and a.wplx=b.wplx
group by wpmc,a.wplx,zsl
)
----------------------------------------------------------------------------------------------------------产量（产品大类）
union all
select '%s' as erdat,'%s' as crrq,'日优等率总览'as zb,wplx as ms,' 'as mx,ydl as sl1,khz as sl2
from(
select wplx,round(sum(ydsl)/sum(sl),4)as ydl,
case when wplx='POY'then poy
when wplx='FDY'then fdy
when wplx='DTY'then dty end as khz
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07",
(select round(avg(case when poyudjh=0 then null else poyudjh end)/100,3)as poy,
round(avg(case when fdyudjh=0 then null else fdyudjh end)/100,3)as fdy,
round(avg(case when dtyudjh=0 then null else dtyudjh end)/100,3)as dty
from"COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where bzrq='%s'
group by wplx,poy,dty,fdy
)
union all
select left('%s',6) as erdat,'%s' as crrq,'月优等率总览'as zb,wplx as ms,' 'as mx,ydl as sl1,khz as sl2
from(
select wplx,round(sum(ydsl)/sum(sl),4)as ydl,
case when wplx='POY'then poy
when wplx='FDY'then fdy
when wplx='DTY'then dty end as khz
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07",
(select round(avg(case when poyudjh=0 then null else poyudjh end)/100,3)as poy,
round(avg(case when fdyudjh=0 then null else fdyudjh end)/100,3)as fdy,
round(avg(case when dtyudjh=0 then null else dtyudjh end)/100,3)as dty
from"COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where bzrq between left('%s',6)||'01'and '%s'
group by wplx,poy,dty,fdy
)
union all
select left('%s',4) as erdat,'%s' as crrq,'年优等率总览'as zb,wplx as ms,' 'as mx,ydl as sl1,khz as sl2
from(
select wplx,round(sum(ydsl)/sum(sl),4)as ydl,
case when wplx='POY'then poy
when wplx='FDY'then fdy
when wplx='DTY'then dty end as khz
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07",
(select round(avg(case when poyudjh=0 then null else poyudjh end)/100,3)as poy,
round(avg(case when fdyudjh=0 then null else fdyudjh end)/100,3)as fdy,
round(avg(case when dtyudjh=0 then null else dtyudjh end)/100,3)as dty
from"COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where bzrq between left('%s',4)||'0101'and '%s'
group by wplx,poy,dty,fdy
)
union all
select '%s' as erdat,'%s' as crrq,'品种日产销率'as zb,wplx as ms,' 'as mx,zcxl as sl1,0 as sl2
from(
select b.wplx,round((a.zxl)/(b.zcl),4) as zcxl
from(select zeiar,sum(zsum/1000) as zxl
from "_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SD/HCXFMSD02"
where erdat='%s'
group by zeiar)a,
(select wplx,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq=to_char(add_days('%s',-1),'yyyymmdd')
group by wplx)b
where a.zeiar=b.wplx
)
union all
select left('%s',6) as erdat,'%s' as crrq,'品种月产销率'as zb,wplx as ms,' 'as mx,zcxl as sl1,0 as sl2
from(
select b.wplx,round((a.zxl)/(b.zcl),4) as zcxl
from(select zeiar,sum(zsum/1000) as zxl
from "_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SD/HCXFMSD02"
where erdat between left('%s',6)||'01'and '%s'
group by zeiar)a,
(select wplx,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by wplx)b
where a.zeiar=b.wplx
)
union all
select '%s' as erdat,'%s' as crrq,'品种日废丝'as zb,wplx as ms,' 'as mx,fs as sl1,fsl as sl2
from(
select wplx, --物品类型
sum(fs) as fs, --废丝量
round(sum(fs)/sum(sl),4) as fsl --废丝率
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by wplx
)
union all
select left('%s',6) as erdat,'%s' as crrq,'品种月废丝'as zb,wplx as ms,' 'as mx,fs as sl1,fsl as sl2
from(
select wplx, --物品类型
sum(fs) as fs, --废丝量
round(sum(fs)/sum(sl),4) as fsl --废丝率
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by wplx
)
union all
select '%s' as erdat,'%s' as crrq,'吨飘丝断头日趋势'as zb,bzrq as ms,' 'as mx,ps as sl1,dt as sl2
from(
select bzrq,
round(sum(ps)/sum(sl),4) as ps,--飘丝
round(sum(dt)/sum(sl),4)as dt--断头
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq
)
union all
select left('%s',6) as erdat,'%s' as crrq,'吨飘丝断头月趋势'as zb,bzrq as ms,' 'as mx,ps as sl1,dt as sl2
from(
select left(bzrq,6)as bzrq,
round(sum(ps)/sum(sl),4) as ps,--飘丝
round(sum(dt)/sum(sl),4)as dt--断头
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6)
)
union all
select '%s' as erdat,'%s' as crrq,'单品种日装置间优等率'as zb,wplx as ms,zzh as mx,ydl as sl1,khz as sl2
from(
select a.zzh,wplx,a.ydl,
case when wplx='POY'then poyudjh/100
when wplx='FDY'then fdyudjh/100
when wplx='DTY'then dtyudjh/100 end as khz
from (select xcxmc,zzh,wplx,round(sum(ydsl)/sum(sl),3) as ydl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by xcxmc,zzh,wplx)a,
(select zzmc,poyudjh,fdyudjh,dtyudjh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'单品种月装置间优等率'as zb,wplx as ms,zzh as mx,ydl as sl1,khz as sl2
from(
select a.zzh,wplx,a.ydl,
case when wplx='POY'then poyudjh/100
when wplx='FDY'then fdyudjh/100
when wplx='DTY'then dtyudjh/100 end as khz
from (select xcxmc,zzh,wplx,round(sum(ydsl)/sum(sl),3) as ydl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zzh,wplx)a,
(select zzmc,poyudjh,fdyudjh,dtyudjh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select '%s' as erdat,'%s' as crrq,'装置间优等率日分布'as zb,zzh as ms,' ' as mx,ydl as sl1,khz as sl2
from(
select a.zzh,a.ydl,pfdudjh/100 as khz
from (select xcxmc,zzh,round(sum(ydsl)/sum(sl),3) as ydl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by xcxmc,zzh)a,
(select zzmc,pfdudjh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置间优等率月分布'as zb,zzh as ms,' ' as mx,ydl as sl1,khz as sl2
from(
select a.zzh,a.ydl,pfdudjh/100 as khz
from (select xcxmc,zzh,round(sum(ydsl)/sum(sl),3) as ydl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zzh)a,
(select zzmc,pfdudjh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select bzrq as erdat,'%s' as crrq,'单装置优等率日趋势'as zb,zzh as ms,' ' as mx,ydl as sl1,khz as sl2
from(
select a.zzh,bzrq,a.ydl,pfdudjh/100 as khz
from (select bzrq,xcxmc,zzh,round(sum(ydsl)/sum(sl),3) as ydl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,xcxmc,zzh)a,
(select zzmc,pfdudjh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select bzrq as erdat,'%s' as crrq,'单装置优等率月趋势'as zb,zzh as ms,' ' as mx,ydl as sl1,khz as sl2
from(
select a.zzh,bzrq,a.ydl,pfdudjh/100 as khz
from (select left(bzrq,6)as bzrq,xcxmc,zzh,round(sum(ydsl)/sum(sl),3) as ydl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6),xcxmc,zzh)a,
(select zzmc,pfdudjh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select '%s' as erdat,'%s' as crrq,'单品种日装置间废丝率'as zb,wplx as ms,zzh as mx,fsl as sl1,khz as sl2
from(
select a.zzh,wplx,a.fsl,fsljh/100 as khz
from(select xcxmc,zzh,wplx,round(sum(fs)/sum(sl),3) as fsl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by xcxmc,zzh,wplx)a,
(select zzmc,fsljh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'单品种月装置间废丝率'as zb,wplx as ms,zzh as mx,fsl as sl1,khz as sl2
from(
select a.zzh,wplx,a.fsl,fsljh/100 as khz
from (select xcxmc,zzh,wplx,round(sum(fs)/sum(sl),3) as fsl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zzh,wplx)a,
(select zzmc,fsljh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select '%s' as erdat,'%s' as crrq,'装置间废丝率日分布'as zb,zzh as ms,' ' as mx,fsl as sl1,khz as sl2
from(
select a.zzh,a.fsl,fsljh/100 as khz
from(select xcxmc,zzh,round(sum(fs)/sum(sl),3) as fsl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by xcxmc,zzh)a,
(select zzmc,fsljh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置间废丝率月分布'as zb,zzh as ms,' ' as mx,fsl as sl1,khz as sl2
from(
select a.zzh,a.fsl,fsljh/100 as khz
from (select xcxmc,zzh,round(sum(fs)/sum(sl),3) as fsl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zzh)a,
(select zzmc,fsljh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select bzrq as erdat,'%s' as crrq,'单装置废丝率日趋势'as zb,zzh as ms,' ' as mx,fsl as sl1,khz as sl2
from(
select a.zzh,bzrq,a.fsl,fsljh/100 as khz
from (select bzrq,xcxmc,zzh,round(sum(fs)/sum(sl),3) as fsl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,xcxmc,zzh)a,
(select zzmc,fsljh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select bzrq as erdat,'%s' as crrq,'单装置废丝率月趋势'as zb,zzh as ms,' ' as mx,fsl as sl1,khz as sl2
from(
select a.zzh,bzrq,a.fsl,fsljh/100 as khz
from (select left(bzrq,6)as bzrq,xcxmc,zzh,round(sum(fs)/sum(sl),3) as fsl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6),xcxmc,zzh)a,
(select zzmc,fsljh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select '%s' as erdat,'%s' as crrq,'装置飘丝断头日分布'as zb,zzh as ms,' ' as mx,ps as sl1,dt as sl2
from(
select xcxmc,zzh,sum(ps)as ps,sum(dt)as dt
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by xcxmc,zzh
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置飘丝断头月分布'as zb,zzh as ms,' ' as mx,ps as sl1,dt as sl2
from(
select xcxmc,zzh,sum(ps)as ps,sum(dt)as dt
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zzh
)
union all
select '%s' as erdat,'%s' as crrq,'装置切败日分布'as zb,zzh as ms,' ' as mx,qb as sl1,0 as sl2
from(
select xcxmc,zzh,sum(zgqb)as qb
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by xcxmc,zzh
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置切败月分布'as zb,zzh as ms,' ' as mx,qb as sl1,0 as sl2
from(
select xcxmc,zzh,sum(zgqb)as qb
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zzh
)
union all
select bzrq as erdat,'%s' as crrq,'单装置断头日趋势'as zb,zzh as ms,' ' as mx,dt as sl1,0 as sl2
from(
select bzrq,xcxmc,zzh,sum(dt)as dt
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,xcxmc,zzh
)
union all
select bzrq as erdat,'%s' as crrq,'单装置断头月趋势'as zb,zzh as ms,' ' as mx,dt as sl1,0 as sl2
from(
select left(bzrq,6)as bzrq,xcxmc,zzh,sum(dt)as dt
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6),xcxmc,zzh
)
union all
select bzrq as erdat,'%s' as crrq,'单装置飘丝日趋势'as zb,zzh as ms,' ' as mx,ps as sl1,0 as sl2
from(
select bzrq,xcxmc,zzh,sum(ps)as ps
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,xcxmc,zzh
)
union all
select bzrq as erdat,'%s' as crrq,'单装置飘丝月趋势'as zb,zzh as ms,' ' as mx,ps as sl1,0 as sl2
from(
select left(bzrq,6)as bzrq,xcxmc,zzh,sum(ps)as ps
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6),xcxmc,zzh
)
union all
select bzrq as erdat,'%s' as crrq,'单装置切败日趋势'as zb,zzh as ms,' ' as mx,dt as sl1,0 as sl2
from(
select bzrq,xcxmc,zzh,sum(zgqb)as dt
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,xcxmc,zzh
)
union all
select bzrq as erdat,'%s' as crrq,'单装置切败月趋势'as zb,zzh as ms,' ' as mx,dt as sl1,0 as sl2
from(
select left(bzrq,6)as bzrq,xcxmc,zzh,sum(zgqb)as dt
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6),xcxmc,zzh
)
union all
select '%s' as erdat,'%s' as crrq,'品种产销率日分布'as zb,wplx as ms,' 'as mx,zcxl as sl1,hb as sl2
from(
select m.wplx,m.zcxl,round((m.zcxl-n.zcxl)/n.zcxl,3) as hb
from(select b.wplx,round((a.zxl)/(b.zcl),4) as zcxl
from(select zeiar,sum(zsum/1000) as zxl
from "_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SD/HCXFMSD02"
where erdat='%s'
group by zeiar)a,
(select wplx,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq=to_char(add_days('%s',-1),'yyyymmdd')
group by wplx)b
where a.zeiar=b.wplx)m,
(select b.wplx,round((a.zxl)/(b.zcl),4) as zcxl
from(select zeiar,sum(zsum/1000) as zxl
from "_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SD/HCXFMSD02"
where erdat=to_char(add_months('%s',-1),'yyyymmdd')
group by zeiar)a,
(select wplx,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq=to_char(add_days('%s',-2),'yyyymmdd')
group by wplx)b
where a.zeiar=b.wplx)n
where m.wplx=n.wplx
)
union all
select left('%s',6) as erdat,'%s' as crrq,'品种产销率月分布'as zb,wplx as ms,' 'as mx,zcxl as sl1,hb as sl2
from(
select m.wplx,m.zcxl,round((m.zcxl-n.zcxl)/n.zcxl,3) as hb
from(select b.wplx,round((a.zxl)/(b.zcl),4) as zcxl
from(select zeiar,sum(zsum/1000) as zxl
from "_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SD/HCXFMSD02"
where erdat between left('%s',6)||'01'and '%s'
group by zeiar)a,
(select wplx,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by wplx)b
where a.zeiar=b.wplx)m,
(select b.wplx,round((a.zxl)/(b.zcl),4) as zcxl
from(select zeiar,sum(zsum/1000) as zxl
from "_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SD/HCXFMSD02"
where erdat between left(to_char(add_months('%s',-1),'yyyymmdd'),6)||'01'and to_char(add_months('%s',-1),'yyyymmdd')
group by zeiar)a,
(select wplx,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left(to_char(add_months('%s',-1),'yyyymmdd'),6)||'01'and to_char(add_months('%s',-1),'yyyymmdd')
group by wplx)b
where a.zeiar=b.wplx)n
where m.wplx=n.wplx
)
union all
select '%s' as erdat,'%s' as crrq,'公司库存天数分布'as zb,ms as ms,' 'as mx,zkc as sl1,kcts as sl2
from(
select ms,--公司名称
zkc,--总库存
case when rjcl=0 then 0 else round(zkc/rjcl,2)end as kcts
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.MM/HMXFMMM20"
('PLACEHOLDER' = ('$$IP_ID$$','2'),'PLACEHOLDER' = ('$$IP_CALDAY$$','%s'))
)
union all
select '%s' as erdat,'%s' as crrq,'品种库存天数分布'as zb,ms as ms,' 'as mx,zkc as sl1,kcts as sl2
from(
select ms,--公司名称
zkc,--总库存
case when rjcl=0 then 0 else round(zkc/rjcl,2)end as kcts
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.MM/HMXFMMM20"
('PLACEHOLDER' = ('$$IP_ID$$','1'),'PLACEHOLDER' = ('$$IP_CALDAY$$','%s'))
)
union all
select '%s' as erdat,'%s' as crrq,'装置产销率日分布'as zb,zzh as ms,' 'as mx,cxl as sl1,zcl as sl2
from(
select b.zzh,round(a.zxl/b.zcl,4)as cxl,b.zcl
from(select sccj,sum(zsum/1000)as zxl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.SD/HMXFMSD35"
where erdat='%s'
group by sccj)a,
(select zzh,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq=to_char(add_days('%s',-1),'yyyymmdd')
group by zzh)b
where a.sccj=b.zzh
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置产销率月分布'as zb,zzh as ms,' 'as mx,cxl as sl1,zcl as sl2
from(
select b.zzh,round(a.zxl/b.zcl,4)as cxl,b.zcl
from(select sccj,sum(zsum/1000)as zxl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.SD/HMXFMSD35"
where erdat between left('%s',6)||'01'and '%s'
group by sccj)a,
(select zzh,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by zzh)b
where a.sccj=b.zzh
)
----------------------------------------------------------------------------------------------------------指标
union all
select left('20190930',6) as erdat,'%s' as crrq,'月公司间利润分布'as zb,gsmc as ms,' 'as mx,lr as sl1,0 as sl2
from(
select gsmc,round(sum(lr/10000),2)as lr
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth=left('20190930',6)
group by gsmc
)
union all
select left('20190930',6) as erdat,'%s' as crrq,'月毛利润及毛利率'as zb,' ' as ms,' 'as mx,lr as sl1,lrl as sl2
from(
select round(sum(lr/10000),2)as lr,round(sum(lr/10000)/sum(sr/10000),3)as lrl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth=left('20190930',6)
)
union all
select left('20190930',6) as erdat,'%s' as crrq,'年毛利润及毛利率'as zb,' ' as ms,' 'as mx,lr as sl1,lrl as sl2
from(
select round(sum(lr/10000),2)as lr,round(sum(lr/10000)/sum(sr/10000),3)as lrl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth between left('20190930',4)||'01'and left('20190930',6)
)
union all
select calmonth as erdat,'%s' as crrq,'公司毛利润趋势'as zb,gsmc as ms,' 'as mx,lr as sl1,lrl as sl2
from(
select gsmc,calmonth,round(sum(lr/10000),2)as lr,round(sum(lr/10000)/sum(sr/10000),3)as lrl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth between left('20190930',4)||'01'and left('20190930',6)
group by gsmc,calmonth
)
union all
select calmonth as erdat,'%s' as crrq,'品种毛利润趋势'as zb,wlmc as ms,' 'as mx,lr as sl1,lrl as sl2
from(
select wlmc,calmonth,round(sum(lr/10000),2)as lr,round(sum(lr/10000)/sum(sr/10000),3)as lrl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth between left('20190930',4)||'01'and left('20190930',6)
group by wlmc,calmonth
)
union all
select left('20190930',6) as erdat,'%s' as crrq,'品种利润分布'as zb,wlmc as ms,' 'as mx,lr as sl1,lrl as sl2
from(
select wlmc,round(sum(lr/10000),2)as lr,round(sum(lr/10000)/sum(sr/10000),3)as lrl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth=left('20190930',6)
group by wlmc
)
union all
select calmonth as erdat,'%s' as crrq,'毛利润趋势分布'as zb,' ' as ms,' 'as mx,lrl as sl1,lre as sl2
from(
select calmonth,round(sum(lr/10000)/sum(sr/10000),3)as lrl,round(sum(lr/10000),2)as lre
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth between left('20190930',4)||'01'and left('20190930',6)
group by calmonth
)
---------------------------------------------------------------------------------------------------------效益
----------------------------------------------------------------------------------------------------------设备
union all
select left('%s',6) as erdat,'%s' as crrq,'设备缺陷总览'as zb,ms as ms,' 'as mx,sl as sl1,wcsl as sl2
from(
select '当月情况'as ms,
ifnull(sum(case when calmonth=left('%s',6)then jsq end),0)as sl,
ifnull(sum(case when calmonth=left('%s',6)and APPROVESTATE='J'then jsq end),0)as wcsl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.PM/HMXFMPM06"
union all
select '当年情况'as ms,
ifnull(sum(case when calmonth between left('%s',4)||'01'and left('%s',6)then jsq end),0)as sl,
ifnull(sum(case when calmonth between left('%s',4)||'01'and left('%s',6)
and APPROVESTATE='J' then jsq end),0)as wcsl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.PM/HMXFMPM06"
)
union all
select calmonth as erdat,'%s' as crrq,'设备缺陷数目趋势'as zb,' ' as ms,' 'as mx,sl as sl1,0 as sl2
from(
select calmonth,
sum(jsq)as sl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.PM/HMXFMPM06"
where calmonth between left(add_years('%s',-1),6) and left('%s',6)
group by calmonth
)
union all
select left('%s',4) as erdat,'%s' as crrq,'当年设备缺陷类型总览'as zb,qxlx as ms,' 'as mx,sl as sl1,wcsl as sl2
from(
select qxlx,
ifnull(sum(case when calmonth between left('%s',4)||'01'and left('%s',6)then jsq end),0)as sl,
ifnull(sum(case when calmonth between left('%s',4)||'01'and left('%s',6)
and APPROVESTATE='J' then jsq end),0)as wcsl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.PM/HMXFMPM06"
where calmonth between left('%s',4)||'01' and left('%s',6)
group by qxlx
)
union all
select left('%s',4) as erdat,'%s' as crrq,'当年公司缺陷分布'as zb,gsjc as ms,' 'as mx,sl as sl1,0 as sl2
from(
select gsjc,
sum(jsq)as sl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.PM/HMXFMPM06"
where calmonth between left('%s',4)||'01' and left('%s',6)
group by gsjc
)
union all
select left('%s',4) as erdat,'%s' as crrq,'当年单公司缺陷类型分布'as zb,gsjc as ms,qxlx as mx,sl as sl1,0 as sl2
from(
select gsjc,
qxlx,
sum(jsq)as sl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.PM/HMXFMPM06"
where calmonth between left('%s',4)||'01' and left('%s',6)
group by gsjc,qxlx
)
----------------------------------------------------------------------------------------------------------设备缺陷台账
'''
del_sql4 = '''delete from"COMMON"."XFM_SC"where crrq='%s' '''
ins_sql4 = '''insert into"COMMON"."XFM_SC"values('%s','%s','%s','%s','%s',%s,%s)'''
# 取合同负盈亏
sel_sql5 = '''
select distinct n.vbeln,
n.bukrs_vf,
n.vkorg,
n.kondm,
n.kunnr,
n.name1,
n.zeiar,
n.matnr,
erdat,
kbetr*zxsl as zdtje,
zxsje as zhtje
from(select a.vkorg,a.matnr,a.kondm,a.datab,a.datbi,kbetr,kpein,kmein
from "_SYS_BIC"."XFMGROUP.EDW.01_BASAL_LAYER.SD/A801" a,
"_SYS_BIC"."XFMGROUP.EDW.01_BASAL_LAYER.SD/KONP" c
where a.kschl = 'ZC01'
and a.knumh = c.knumh
and to_char(add_days('%s',-1),'yyyymmdd') between a.datab and a.datbi) m,
(select vbeln,
vkorg,
kondm,
erdat,
bukrs_vf,
kunnr,
name1,
matnr,
zeiar,
sum(kzwi1) as zxsje,
sum(zsum) as zxsl
from(select a.vbeln, --订单号
a.vkorg,
b.kondm,
a.erdat, --订单日期
a.bukrs_vf, --公司代码
a.kunnr, --客户代码
c.name1, --客户名称
b.matnr, --物料编号
CASE WHEN e.zeiar = '' THEN 'QT'
ELSE e.zeiar end zeiar, --物料类型
b.KZWI1, --销售总价
CASE
WHEN b.zieme = 'TO' THEN
b.kwmeng * b.umziz * 1000
ELSE
b.kwmeng * b.umziz
END AS ZSUM -- 总销量
from "_SYS_BIC"."XFMGROUP.EDW.01_BASAL_LAYER.SD/VBAK" a,
"_SYS_BIC"."XFMGROUP.EDW.01_BASAL_LAYER.SD/VBAP" b,
"_SYS_BIC"."XFMGROUP.EDW.01_BASAL_LAYER.SD/KNA1" c,
"_SYS_BIC"."XFMGROUP.EDW.01_BASAL_LAYER.ZSJ.PUBLIC/MARA" e
where a.vbeln = b.vbeln
and a.kunnr = c.kunnr
and b.matnr = e.matnr
and a.auart = 'ZC05')
group by vbeln,vkorg,kondm,erdat,bukrs_vf,kunnr,name1,matnr,zeiar) n
where m.vkorg = n.vkorg
and m.matnr = n.matnr
and m.kondm = n.kondm
and erdat = to_char(add_days('%s',-1),'yyyymmdd')
'''
ins_sql5 = '''insert into"COMMON"."XFMSD_HTFYK"values('%s','%s','%s','%s','%s','%s','%s','%s','%s',%s,%s)'''
for item in ([sel_sql1, del_sql1, ins_sql1,
today], [sel_sql2, ins_sql2,
today], [sel_sql3, del_sql3, ins_sql3, yesterday],
[sel_sql4, del_sql4, ins_sql4,
yesterday], [sel_sql5, ins_sql5, today]):
if len(item) == 4:
res = sql().sql_req(conn, str(item[3]), sql=str(item[0]))
sql().sql_req(conn, str(item[3]), sql=str(item[1]))
sql().sql_req(conn, res, sql=str(item[2]))
else:
res = sql().sql_req(conn, str(item[2]), sql=str(item[0]))
sql().sql_req(conn, res, sql=str(item[1]))
get_connection().close_connection(conn)
def main():
# 定义sql1为 执行的查询数据语句
# 定义sql2为 执行的删除当日数据语句
# 定义sql3为 执行的插入当日数据语句
sql1 = '''
-------------------------------------------------------------------------------------------------------------产量（总览）
select '%s' as erdat,'%s' as crrq,'产量总览'as zb,'日计划'as ms,' 'as mx,zcl as sl1,rjh as sl2
from(
select round(sum(sl),2)as zcl,--总产量
sum(clrjh)as rjh--日计划
from(select distinct calday,xcxmc,clrjh,sum(sl)as sl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC05"
group by calday,xcxmc,clrjh)
where calday= '%s')
union all
select left('%s',6) as erdat,'%s' as crrq,'产量总览'as zb,'月计划'as ms,' 'as mx,zcl as sl1,yjh as sl2
from(
select round(sum(sl),2)as zcl,--总产量
sum(clyjh) as yjh--月计划
from(select distinct left(calday,6)as calmonth,xcxmc,clyjh,sum(sl)as sl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC05"
where calday between left('%s',6)||'01'and '%s'
group by left(calday,6),xcxmc,clyjh)
)
union all
select left('%s',4) as erdat,'%s' as crrq,'产量总览'as zb,'年计划'as ms,' 'as mx,zcl as sl1,njh as sl2
from(
select round(sum(sl),2)as zcl,--总产量
sum(clnjh) as njh--年计划
from(select distinct left(calday,4)as calyear,xcxmc,clnjh,sum(sl)as sl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC05"
where calday between left('%s',4)||'0101'and '%s'
group by left(calday,4),xcxmc,clnjh)
)
union all
select '%s' as erdat,'%s' as crrq,'公司间产量日对比'as zb,gsjc as ms,' 'as mx,cl as sl1,0 as sl2
from(
select gsjc,--公司简称
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by gsjc
)
union all
select left('%s',6)as erdat,'%s' as crrq,'公司间产量月对比'as zb,gsjc as ms,' 'as mx,cl as sl1,0 as sl2
from(
select gsjc,--公司简称
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by gsjc
)
union all
select '%s' as erdat,'%s' as crrq,'装置间产量日对比'as zb,xcxmc as ms,' 'as mx,cl as sl1,0 as sl2
from(
select xcxmc,--装置号
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by xcxmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置间产量月对比'as zb,xcxmc as ms,' 'as mx,cl as sl1,0 as sl2
from(
select xcxmc,--装置号
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc
)
union all
select '%s' as erdat,'%s' as crrq,'产品类别日占比'as zb,wplx as ms,' 'as mx,zb as sl1,0 as sl2
from(
select wplx,--物品类型
round(sum(sl)/zsl,3)as zb--占比
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s')
where bzrq='%s'
group by wplx,zsl
)
union all
select left('%s',6) as erdat,'%s' as crrq,'产品类别月占比'as zb,wplx as ms,' 'as mx,zb as sl1,0 as sl2
from(
select wplx,--物品类型
round(sum(sl)/zsl,3)as zb--占比
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s')
where bzrq between left('%s',6)||'01'and '%s'
group by wplx,zsl
)
-----------------------------------------------------------------------------------------------------------产量（公司间）
union all
select '%s' as erdat,'%s' as crrq,'公司产量日占比'as zb,gsjc as ms,' 'as mx,zb as sl1,0 as sl2
from(
select gsjc,--公司简称
round(sum(sl)/zsl,3)as zb--占比
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s')
where bzrq='%s'
group by gsjc,zsl
)
union all
select left('%s',6)as erdat,'%s' as crrq,'公司产量月占比'as zb,gsjc as ms,' 'as mx,zb as sl1,0 as sl2
from(
select gsjc,--公司简称
round(sum(sl)/zsl,3)as zb--占比
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s')
where bzrq between left('%s',6)||'01'and '%s'
group by gsjc,zsl
)
union all
select '%s' as erdat,'%s' as crrq,'单公司产品日占比'as zb,gsjc as ms,wplx as mx,cl as sl1,0 as sl2
from(
select gsjc,
wplx,--公司简称
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by gsjc,wplx
)
union all
select left('%s',6) as erdat,'%s' as crrq,'单公司产品月占比'as zb,gsjc as ms,wplx as mx,cl as sl1,0 as sl2
from(
select gsjc,
wplx,--公司简称
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by gsjc,wplx
)
union all
select '%s' as erdat,'%s' as crrq,'公司日同环比'as zb,gsjc as ms,' 'as mx,hb as sl1,tb as sl2
from(
select a.gsjc,
case when ifnull(b.cl,0)=0 then 1
else round((a.cl-b.cl)/b.cl,3)end as hb,--环比
case when ifnull(c.cl,0)=0 then 1
else round((a.cl-c.cl)/c.cl,3)end as tb--同比
from(
select gsjc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by gsjc)a,
(
select gsjc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq=to_char(add_months('%s',-1),'yyyymmdd')
group by gsjc)b,
(select gsjc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq=to_char(add_years('%s',-1),'yyyymmdd')
group by gsjc)c
where a.gsjc=b.gsjc
and a.gsjc=c.gsjc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'公司月同环比'as zb,gsjc as ms,' 'as mx,hb as sl1,tb as sl2
from(
select a.gsjc,
case when ifnull(b.cl,0)=0 then 1
else round((a.cl-b.cl)/b.cl,3)end as hb,--环比
case when ifnull(c.cl,0)=0 then 1
else round((a.cl-c.cl)/c.cl,3)end as tb--同比
from(
select gsjc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by gsjc)a,
(
select gsjc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left(to_char(add_months('%s',-1),'yyyymmdd'),6)||'01'and to_char(add_months('%s',-1),'yyyymmdd')
group by gsjc)b,
(select gsjc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left(to_char(add_years('%s',-1),'yyyymmdd'),6)||'01'and to_char(add_years('%s',-1),'yyyymmdd')
group by gsjc)c
where a.gsjc=b.gsjc
and a.gsjc=c.gsjc
)
union all
select bzrq as erdat,'%s' as crrq,'单公司产量日趋势'as zb,gsjc as ms,' 'as mx,cl as sl1,dqfh as sl2
from(
select bzrq, --日期
a.gsjc,
round(sum(sl),2)as cl,--产量
round(dqfh,2)as dqfh--当前负荷
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"a,
(select gsjc,sum(dqfh)as dqfh from"COMMON"."XFMSC_DQFH"group by gsjc)b
where a.gsjc=b.gsjc
and bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,dqfh,a.gsjc
order by bzrq
)
union all
select bzrq as erdat,'%s' as crrq,'单公司产量月趋势'as zb,gsjc as ms,' 'as mx,cl as sl1,dqfh as sl2
from(
select left(bzrq,6)as bzrq, --月
a.gsjc,
round(sum(sl),2)as cl,--产量
round(dqfh,2)as dqfh--当前负荷
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"a,
(select gsjc,sum(dqfh)as dqfh from"COMMON"."XFMSC_DQFH"group by gsjc)b
where a.gsjc=b.gsjc
and bzrq between left(to_char(add_years('%s',-1),'yyyymmdd'),6)||'01'and '%s'
group by left(bzrq,6),dqfh,a.gsjc
)
-----------------------------------------------------------------------------------------------------------产量（装置间）
union all
select '%s' as erdat,'%s' as crrq,'装置产量日占比'as zb,xcxmc as ms,' 'as mx,zb as sl1,0 as sl2
from(
select xcxmc,--装置名称
round(sum(sl)/zsl,3)as zb
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s')
where bzrq='%s'
group by xcxmc,zsl
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置产量月占比'as zb,xcxmc as ms,' 'as mx,zb as sl1,0 as sl2
from(
select xcxmc,--装置名称
round(sum(sl)/zsl,3)as zb
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s')
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zsl
)
union all
select '%s' as erdat,'%s' as crrq,'单装置产量日占比'as zb,xcxmc as ms,wplx as mx,cl as sl1,0 as sl2
from(
select wplx,--物品类型
xcxmc,
round(sum(sl),2)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by wplx,xcxmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'单装置产量月占比'as zb,xcxmc as ms,wplx as mx,cl as sl1,0 as sl2
from(
select wplx,--物品类型
xcxmc,
round(sum(sl),2)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by wplx,xcxmc
)
union all
select '%s' as erdat,'%s' as crrq,'单装置日同环比'as zb,xcxmc as ms,' 'as mx,hb as sl1,tb as sl2
from(
select a.xcxmc,
case when ifnull(b.cl,0)=0 then 1
else round((a.cl-b.cl)/b.cl,3)end as hb,--环比
case when ifnull(c.cl,0)=0 then 1
else round((a.cl-c.cl)/c.cl,3)end as tb--同比
from(
select xcxmc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by xcxmc)a,
(
select xcxmc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq=to_char(add_months('%s',-1),'yyyymmdd')
group by xcxmc)b,
(select xcxmc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq=to_char(add_years('%s',-1),'yyyymmdd')
group by xcxmc)c
where a.xcxmc=b.xcxmc
and a.xcxmc=c.xcxmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'单装置月同环比'as zb,xcxmc as ms,' 'as mx,hb as sl1,tb as sl2
from(
select a.xcxmc,
case when ifnull(b.cl,0)=0 then 1
else round((a.cl-b.cl)/b.cl,3)end as hb,--环比
case when ifnull(c.cl,0)=0 then 1
else round((a.cl-c.cl)/c.cl,3)end as tb--同比
from(
select xcxmc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc)a,
(
select xcxmc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left(to_char(add_months('%s',-1),'yyyymmdd'),6)||'01'and to_char(add_months('%s',-1),'yyyymmdd')
group by xcxmc)b,
(select xcxmc,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left(to_char(add_years('%s',-1),'yyyymmdd'),6)||'01'and to_char(add_years('%s',-1),'yyyymmdd')
group by xcxmc)c
where a.xcxmc=b.xcxmc
and a.xcxmc=c.xcxmc
)
union all
select bzrq as erdat,'%s' as crrq,'单装置产量日趋势'as zb,xcxmc as ms,' 'as mx,cl as sl1,dqfh as sl2
from(
select bzrq, --日期
a.xcxmc,
round(sum(sl),2)as cl,--产量
round(dqfh,2)as dqfh--当前负荷
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"a,
(select cxmc,sum(dqfh)as dqfh from"COMMON"."XFMSC_DQFH"group by cxmc)b
where a.xcxmc=b.cxmc
and bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,dqfh,a.xcxmc
order by bzrq
)
union all
select bzrq as erdat,'%s' as crrq,'单装置产量月趋势'as zb,xcxmc as ms,' 'as mx,cl as sl1,dqfh as sl2
from(
select left(bzrq,6)as bzrq, --月
a.xcxmc,
round(sum(sl),2)as cl,--产量
round(dqfh,2)as dqfh--当前负荷
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"a,
(select cxmc,sum(dqfh)as dqfh from"COMMON"."XFMSC_DQFH"group by cxmc)b
where a.xcxmc=b.cxmc
and bzrq between left(to_char(add_years('%s',-1),'yyyymmdd'),6)||'01'and '%s'
group by left(bzrq,6),dqfh,a.xcxmc
)
----------------------------------------------------------------------------------------------------------产量（产品大类）
union all
select '%s' as erdat,'%s' as crrq,'产品大类别日占比'as zb,wplx as ms,' 'as mx,zb as sl1,cl as sl2
from(
select wplx,--物品类型
round(sum(sl)/zsl,3)as zb,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s')
where bzrq='%s'
group by wplx,zsl
)
union all
select left('%s',6) as erdat,'%s' as crrq,'产品大类别月占比'as zb,wplx as ms,' 'as mx,zb as sl1,cl as sl2
from(
select wplx,--物品类型
round(sum(sl)/zsl,3)as zb,sum(sl)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01",
(select sum(sl)as zsl from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s')
where bzrq between left('%s',6)||'01'and '%s'
group by wplx,zsl
)
union all
select '%s' as erdat,'%s' as crrq,'单产品装置日分布'as zb,wplx as ms,xcxmc as mx,cl as sl1,0 as sl2
from(
select wplx,--物品类型
xcxmc,
round(sum(sl),2)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq='%s'
group by wplx,xcxmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'单产品装置月分布'as zb,wplx as ms,xcxmc as mx,cl as sl1,0 as sl2
from(
select wplx,--物品类型
xcxmc,
round(sum(sl),2)as cl
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between left('%s',6)||'01'and '%s'
group by wplx,xcxmc
)
union all
select bzrq as erdat,'%s' as crrq,'单产品产量日趋势'as zb,wplx as ms,' 'as mx,cl as sl1,0 as sl2
from(
select bzrq, --日期
wplx,
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,wplx
order by bzrq
)
union all
select bzrq as erdat,'%s' as crrq,'单产品产量月趋势'as zb,wplx as ms,' 'as mx,cl as sl1,0 as sl2
from(
select left(bzrq,6)as bzrq, --月
wplx,
round(sum(sl),2)as cl--产量
from"_SYS_BIC"."DASHBOARD_XFM.02_LOGICAL_LAYER.SC/HCXFMSC01"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6),wplx
)
union all
select '%s' as erdat,'%s' as crrq,'产品品种描述日占比'as zb,wplx as ms,wpmc as mx,zb as sl1,0 as sl2
from(
select a.wpmc,a.wplx,round(sum(sl/1000)/zsl,3)as zb
from"_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SC/HCXFMSC09"a,
(select wplx,sum(sl/1000)as zsl from"_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SC/HCXFMSC09"
where bzrq='%s'group by wplx)b
where a.wplx=b.wplx
and bzrq='%s'
group by wpmc,a.wplx,zsl
)
union all
select left('%s',6)as erdat,'%s' as crrq,'产品品种描述月占比'as zb,wplx as ms,wpmc as mx,zb as sl1,0 as sl2
from(
select a.wpmc,a.wplx,round(sum(sl/1000)/zsl,3)as zb
from"_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SC/HCXFMSC09"a,
(select wplx,sum(sl/1000)as zsl from"_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SC/HCXFMSC09"
where bzrq between left('%s',6)||'01'and '%s'group by wplx)b
where bzrq between left('%s',6)||'01'and '%s'
and a.wplx=b.wplx
group by wpmc,a.wplx,zsl
)
----------------------------------------------------------------------------------------------------------指标
union all
select '%s' as erdat,'%s' as crrq,'日优等率总览'as zb,wplx as ms,' 'as mx,ydl as sl1,khz as sl2
from(
select wplx,round(sum(ydsl)/sum(sl),4)as ydl,
case when wplx='POY'then poy
when wplx='FDY'then fdy
when wplx='DTY'then dty end as khz
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07",
(select round(avg(case when poyudjh=0 then null else poyudjh end)/100,3)as poy,
round(avg(case when fdyudjh=0 then null else fdyudjh end)/100,3)as fdy,
round(avg(case when dtyudjh=0 then null else dtyudjh end)/100,3)as dty
from"COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where bzrq='%s'
group by wplx,poy,dty,fdy
)
union all
select left('%s',6) as erdat,'%s' as crrq,'月优等率总览'as zb,wplx as ms,' 'as mx,ydl as sl1,khz as sl2
from(
select wplx,round(sum(ydsl)/sum(sl),4)as ydl,
case when wplx='POY'then poy
when wplx='FDY'then fdy
when wplx='DTY'then dty end as khz
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07",
(select round(avg(case when poyudjh=0 then null else poyudjh end)/100,3)as poy,
round(avg(case when fdyudjh=0 then null else fdyudjh end)/100,3)as fdy,
round(avg(case when dtyudjh=0 then null else dtyudjh end)/100,3)as dty
from"COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where bzrq between left('%s',6)||'01'and '%s'
group by wplx,poy,dty,fdy
)
union all
select left('%s',4) as erdat,'%s' as crrq,'年优等率总览'as zb,wplx as ms,' 'as mx,ydl as sl1,khz as sl2
from(
select wplx,round(sum(ydsl)/sum(sl),4)as ydl,
case when wplx='POY'then poy
when wplx='FDY'then fdy
when wplx='DTY'then dty end as khz
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07",
(select round(avg(case when poyudjh=0 then null else poyudjh end)/100,3)as poy,
round(avg(case when fdyudjh=0 then null else fdyudjh end)/100,3)as fdy,
round(avg(case when dtyudjh=0 then null else dtyudjh end)/100,3)as dty
from"COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where bzrq between left('%s',4)||'0101'and '%s'
group by wplx,poy,dty,fdy
)
union all
select '%s' as erdat,'%s' as crrq,'品种日产销率'as zb,wplx as ms,' 'as mx,zcxl as sl1,0 as sl2
from(
select b.wplx,round((a.zxl)/(b.zcl),4) as zcxl
from(select zeiar,sum(zsum/1000) as zxl
from "_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SD/HCXFMSD02"
where erdat='%s'
group by zeiar)a,
(select wplx,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq=to_char(add_days('%s',-1),'yyyymmdd')
group by wplx)b
where a.zeiar=b.wplx
)
union all
select left('%s',6) as erdat,'%s' as crrq,'品种月产销率'as zb,wplx as ms,' 'as mx,zcxl as sl1,0 as sl2
from(
select b.wplx,round((a.zxl)/(b.zcl),4) as zcxl
from(select zeiar,sum(zsum/1000) as zxl
from "_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SD/HCXFMSD02"
where erdat between left('%s',6)||'01'and '%s'
group by zeiar)a,
(select wplx,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by wplx)b
where a.zeiar=b.wplx
)
union all
select '%s' as erdat,'%s' as crrq,'品种日废丝'as zb,wplx as ms,' 'as mx,fs as sl1,fsl as sl2
from(
select wplx, --物品类型
sum(fs) as fs, --废丝量
round(sum(fs)/sum(sl),4) as fsl --废丝率
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by wplx
)
union all
select left('%s',6) as erdat,'%s' as crrq,'品种月废丝'as zb,wplx as ms,' 'as mx,fs as sl1,fsl as sl2
from(
select wplx, --物品类型
sum(fs) as fs, --废丝量
round(sum(fs)/sum(sl),4) as fsl --废丝率
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by wplx
)
union all
select '%s' as erdat,'%s' as crrq,'吨飘丝断头日趋势'as zb,bzrq as ms,' 'as mx,ps as sl1,dt as sl2
from(
select bzrq,
round(sum(ps)/sum(sl),4) as ps,--飘丝
round(sum(dt)/sum(sl),4)as dt--断头
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq
)
union all
select left('%s',6) as erdat,'%s' as crrq,'吨飘丝断头月趋势'as zb,bzrq as ms,' 'as mx,ps as sl1,dt as sl2
from(
select left(bzrq,6)as bzrq,
round(sum(ps)/sum(sl),4) as ps,--飘丝
round(sum(dt)/sum(sl),4)as dt--断头
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6)
)
union all
select '%s' as erdat,'%s' as crrq,'单品种日装置间优等率'as zb,wplx as ms,zzh as mx,ydl as sl1,khz as sl2
from(
select a.zzh,wplx,a.ydl,
case when wplx='POY'then poyudjh/100
when wplx='FDY'then fdyudjh/100
when wplx='DTY'then dtyudjh/100 end as khz
from (select xcxmc,zzh,wplx,round(sum(ydsl)/sum(sl),3) as ydl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by xcxmc,zzh,wplx)a,
(select zzmc,poyudjh,fdyudjh,dtyudjh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'单品种月装置间优等率'as zb,wplx as ms,zzh as mx,ydl as sl1,khz as sl2
from(
select a.zzh,wplx,a.ydl,
case when wplx='POY'then poyudjh/100
when wplx='FDY'then fdyudjh/100
when wplx='DTY'then dtyudjh/100 end as khz
from (select xcxmc,zzh,wplx,round(sum(ydsl)/sum(sl),3) as ydl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zzh,wplx)a,
(select zzmc,poyudjh,fdyudjh,dtyudjh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select '%s' as erdat,'%s' as crrq,'装置间优等率日分布'as zb,zzh as ms,' ' as mx,ydl as sl1,khz as sl2
from(
select a.zzh,a.ydl,pfdudjh/100 as khz
from (select xcxmc,zzh,round(sum(ydsl)/sum(sl),3) as ydl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by xcxmc,zzh)a,
(select zzmc,pfdudjh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置间优等率月分布'as zb,zzh as ms,' ' as mx,ydl as sl1,khz as sl2
from(
select a.zzh,a.ydl,pfdudjh/100 as khz
from (select xcxmc,zzh,round(sum(ydsl)/sum(sl),3) as ydl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zzh)a,
(select zzmc,pfdudjh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select bzrq as erdat,'%s' as crrq,'单装置优等率日趋势'as zb,zzh as ms,' ' as mx,ydl as sl1,khz as sl2
from(
select a.zzh,bzrq,a.ydl,pfdudjh/100 as khz
from (select bzrq,xcxmc,zzh,round(sum(ydsl)/sum(sl),3) as ydl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,xcxmc,zzh)a,
(select zzmc,pfdudjh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select bzrq as erdat,'%s' as crrq,'单装置优等率月趋势'as zb,zzh as ms,' ' as mx,ydl as sl1,khz as sl2
from(
select a.zzh,bzrq,a.ydl,pfdudjh/100 as khz
from (select left(bzrq,6)as bzrq,xcxmc,zzh,round(sum(ydsl)/sum(sl),3) as ydl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6),xcxmc,zzh)a,
(select zzmc,pfdudjh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select '%s' as erdat,'%s' as crrq,'单品种日装置间废丝率'as zb,wplx as ms,zzh as mx,fsl as sl1,khz as sl2
from(
select a.zzh,wplx,a.fsl,fsljh/100 as khz
from(select xcxmc,zzh,wplx,round(sum(fs)/sum(sl),3) as fsl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by xcxmc,zzh,wplx)a,
(select zzmc,fsljh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'单品种月装置间废丝率'as zb,wplx as ms,zzh as mx,fsl as sl1,khz as sl2
from(
select a.zzh,wplx,a.fsl,fsljh/100 as khz
from (select xcxmc,zzh,wplx,round(sum(fs)/sum(sl),3) as fsl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zzh,wplx)a,
(select zzmc,fsljh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select '%s' as erdat,'%s' as crrq,'装置间废丝率日分布'as zb,zzh as ms,' ' as mx,fsl as sl1,khz as sl2
from(
select a.zzh,a.fsl,fsljh/100 as khz
from(select xcxmc,zzh,round(sum(fs)/sum(sl),3) as fsl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by xcxmc,zzh)a,
(select zzmc,fsljh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置间废丝率月分布'as zb,zzh as ms,' ' as mx,fsl as sl1,khz as sl2
from(
select a.zzh,a.fsl,fsljh/100 as khz
from (select xcxmc,zzh,round(sum(fs)/sum(sl),3) as fsl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zzh)a,
(select zzmc,fsljh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select bzrq as erdat,'%s' as crrq,'单装置废丝率日趋势'as zb,zzh as ms,' ' as mx,fsl as sl1,khz as sl2
from(
select a.zzh,bzrq,a.fsl,fsljh/100 as khz
from (select bzrq,xcxmc,zzh,round(sum(fs)/sum(sl),3) as fsl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,xcxmc,zzh)a,
(select zzmc,fsljh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select bzrq as erdat,'%s' as crrq,'单装置废丝率月趋势'as zb,zzh as ms,' ' as mx,fsl as sl1,khz as sl2
from(
select a.zzh,bzrq,a.fsl,fsljh/100 as khz
from (select left(bzrq,6)as bzrq,xcxmc,zzh,round(sum(fs)/sum(sl),3) as fsl
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6),xcxmc,zzh)a,
(select zzmc,fsljh from "COMMON"."XFM_KHZB"
where khrq=left('%s',4))b
where a.xcxmc=b.zzmc
)
union all
select '%s' as erdat,'%s' as crrq,'装置飘丝断头日分布'as zb,zzh as ms,' ' as mx,ps as sl1,dt as sl2
from(
select xcxmc,zzh,sum(ps)as ps,sum(dt)as dt
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by xcxmc,zzh
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置飘丝断头月分布'as zb,zzh as ms,' ' as mx,ps as sl1,dt as sl2
from(
select xcxmc,zzh,sum(ps)as ps,sum(dt)as dt
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zzh
)
union all
select '%s' as erdat,'%s' as crrq,'装置切败日分布'as zb,zzh as ms,' ' as mx,qb as sl1,0 as sl2
from(
select xcxmc,zzh,sum(zgqb)as qb
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq='%s'
group by xcxmc,zzh
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置切败月分布'as zb,zzh as ms,' ' as mx,qb as sl1,0 as sl2
from(
select xcxmc,zzh,sum(zgqb)as qb
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by xcxmc,zzh
)
union all
select bzrq as erdat,'%s' as crrq,'单装置断头日趋势'as zb,zzh as ms,' ' as mx,dt as sl1,0 as sl2
from(
select bzrq,xcxmc,zzh,sum(dt)as dt
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,xcxmc,zzh
)
union all
select bzrq as erdat,'%s' as crrq,'单装置断头月趋势'as zb,zzh as ms,' ' as mx,dt as sl1,0 as sl2
from(
select left(bzrq,6)as bzrq,xcxmc,zzh,sum(dt)as dt
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6),xcxmc,zzh
)
union all
select bzrq as erdat,'%s' as crrq,'单装置飘丝日趋势'as zb,zzh as ms,' ' as mx,ps as sl1,0 as sl2
from(
select bzrq,xcxmc,zzh,sum(ps)as ps
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,xcxmc,zzh
)
union all
select bzrq as erdat,'%s' as crrq,'单装置飘丝月趋势'as zb,zzh as ms,' ' as mx,ps as sl1,0 as sl2
from(
select left(bzrq,6)as bzrq,xcxmc,zzh,sum(ps)as ps
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6),xcxmc,zzh
)
union all
select bzrq as erdat,'%s' as crrq,'单装置切败日趋势'as zb,zzh as ms,' ' as mx,dt as sl1,0 as sl2
from(
select bzrq,xcxmc,zzh,sum(zgqb)as dt
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_months('%s',-1),'yyyymmdd')and '%s'
group by bzrq,xcxmc,zzh
)
union all
select bzrq as erdat,'%s' as crrq,'单装置切败月趋势'as zb,zzh as ms,' ' as mx,dt as sl1,0 as sl2
from(
select left(bzrq,6)as bzrq,xcxmc,zzh,sum(zgqb)as dt
from"_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between to_char(add_years('%s',-1),'yyyymmdd')and '%s'
group by left(bzrq,6),xcxmc,zzh
)
union all
select '%s' as erdat,'%s' as crrq,'品种产销率日分布'as zb,wplx as ms,' 'as mx,zcxl as sl1,hb as sl2
from(
select m.wplx,m.zcxl,round((m.zcxl-n.zcxl)/n.zcxl,3) as hb
from(select b.wplx,round((a.zxl)/(b.zcl),4) as zcxl
from(select zeiar,sum(zsum/1000) as zxl
from "_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SD/HCXFMSD02"
where erdat='%s'
group by zeiar)a,
(select wplx,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq=to_char(add_days('%s',-1),'yyyymmdd')
group by wplx)b
where a.zeiar=b.wplx)m,
(select b.wplx,round((a.zxl)/(b.zcl),4) as zcxl
from(select zeiar,sum(zsum/1000) as zxl
from "_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SD/HCXFMSD02"
where erdat=to_char(add_months('%s',-1),'yyyymmdd')
group by zeiar)a,
(select wplx,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq=to_char(add_days('%s',-2),'yyyymmdd')
group by wplx)b
where a.zeiar=b.wplx)n
where m.wplx=n.wplx
)
union all
select left('%s',6) as erdat,'%s' as crrq,'品种产销率月分布'as zb,wplx as ms,' 'as mx,zcxl as sl1,hb as sl2
from(
select m.wplx,m.zcxl,round((m.zcxl-n.zcxl)/n.zcxl,3) as hb
from(select b.wplx,round((a.zxl)/(b.zcl),4) as zcxl
from(select zeiar,sum(zsum/1000) as zxl
from "_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SD/HCXFMSD02"
where erdat between left('%s',6)||'01'and '%s'
group by zeiar)a,
(select wplx,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by wplx)b
where a.zeiar=b.wplx)m,
(select b.wplx,round((a.zxl)/(b.zcl),4) as zcxl
from(select zeiar,sum(zsum/1000) as zxl
from "_SYS_BIC"."XFMGROUP.EDW.02_LOGICAL_LAYER.SD/HCXFMSD02"
where erdat between left(to_char(add_months('%s',-1),'yyyymmdd'),6)||'01'and to_char(add_months('%s',-1),'yyyymmdd')
group by zeiar)a,
(select wplx,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left(to_char(add_months('%s',-1),'yyyymmdd'),6)||'01'and to_char(add_months('%s',-1),'yyyymmdd')
group by wplx)b
where a.zeiar=b.wplx)n
where m.wplx=n.wplx
)
union all
select '%s' as erdat,'%s' as crrq,'公司库存天数分布'as zb,ms as ms,' 'as mx,zkc as sl1,kcts as sl2
from(
select ms,--公司名称
zkc,--总库存
case when rjcl=0 then 0 else round(zkc/rjcl,2)end as kcts
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.MM/HMXFMMM20"
('PLACEHOLDER' = ('$$IP_ID$$','2'),'PLACEHOLDER' = ('$$IP_CALDAY$$','%s'))
)
union all
select '%s' as erdat,'%s' as crrq,'品种库存天数分布'as zb,ms as ms,' 'as mx,zkc as sl1,kcts as sl2
from(
select ms,--公司名称
zkc,--总库存
case when rjcl=0 then 0 else round(zkc/rjcl,2)end as kcts
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.MM/HMXFMMM20"
('PLACEHOLDER' = ('$$IP_ID$$','1'),'PLACEHOLDER' = ('$$IP_CALDAY$$','%s'))
)
union all
select '%s' as erdat,'%s' as crrq,'装置产销率日分布'as zb,zzh as ms,' 'as mx,cxl as sl1,zcl as sl2
from(
select b.zzh,round(a.zxl/b.zcl,4)as cxl,b.zcl
from(select sccj,sum(zsum/1000)as zxl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.SD/HMXFMSD35"
where erdat='%s'
group by sccj)a,
(select zzh,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq=to_char(add_days('%s',-1),'yyyymmdd')
group by zzh)b
where a.sccj=b.zzh
)
union all
select left('%s',6) as erdat,'%s' as crrq,'装置产销率月分布'as zb,zzh as ms,' 'as mx,cxl as sl1,zcl as sl2
from(
select b.zzh,round(a.zxl/b.zcl,4)as cxl,b.zcl
from(select sccj,sum(zsum/1000)as zxl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.SD/HMXFMSD35"
where erdat between left('%s',6)||'01'and '%s'
group by sccj)a,
(select zzh,sum(sl)as zcl
from "_SYS_BIC"."DASHBOARD_XFM.03_DATAMART_LAYER.SC/HMXFMSC07"
where bzrq between left('%s',6)||'01'and '%s'
group by zzh)b
where a.sccj=b.zzh
)
----------------------------------------------------------------------------------------------------------效益
union all
select left('20190930',6) as erdat,'%s' as crrq,'月公司间利润分布'as zb,gsmc as ms,' 'as mx,lr as sl1,0 as sl2
from(
select gsmc,round(sum(lr/10000),2)as lr
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth=left('20190930',6)
group by gsmc
)
union all
select left('20190930',6) as erdat,'%s' as crrq,'月毛利润及毛利率'as zb,' ' as ms,' 'as mx,lr as sl1,lrl as sl2
from(
select round(sum(lr/10000),2)as lr,round(sum(lr/10000)/sum(sr/10000),3)as lrl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth=left('20190930',6)
)
union all
select left('20190930',6) as erdat,'%s' as crrq,'年毛利润及毛利率'as zb,' ' as ms,' 'as mx,lr as sl1,lrl as sl2
from(
select round(sum(lr/10000),2)as lr,round(sum(lr/10000)/sum(sr/10000),3)as lrl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth between left('20190930',4)||'01'and left('20190930',6)
)
union all
select calmonth as erdat,'%s' as crrq,'公司毛利润趋势'as zb,gsmc as ms,' 'as mx,lr as sl1,lrl as sl2
from(
select gsmc,calmonth,round(sum(lr/10000),2)as lr,round(sum(lr/10000)/sum(sr/10000),3)as lrl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth between left('20190930',4)||'01'and left('20190930',6)
group by gsmc,calmonth
)
union all
select calmonth as erdat,'%s' as crrq,'品种毛利润趋势'as zb,wlmc as ms,' 'as mx,lr as sl1,lrl as sl2
from(
select wlmc,calmonth,round(sum(lr/10000),2)as lr,round(sum(lr/10000)/sum(sr/10000),3)as lrl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth between left('20190930',4)||'01'and left('20190930',6)
group by wlmc,calmonth
)
union all
select left('20190930',6) as erdat,'%s' as crrq,'品种利润分布'as zb,wlmc as ms,' 'as mx,lr as sl1,lrl as sl2
from(
select wlmc,round(sum(lr/10000),2)as lr,round(sum(lr/10000)/sum(sr/10000),3)as lrl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth=left('20190930',6)
group by wlmc
)
union all
select calmonth as erdat,'%s' as crrq,'毛利润趋势分布'as zb,' ' as ms,' 'as mx,lrl as sl1,lre as sl2
from(
select calmonth,round(sum(lr/10000)/sum(sr/10000),3)as lrl,round(sum(lr/10000),2)as lre
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.FI/HMXFMFI04"
where calmonth between left('20190930',4)||'01'and left('20190930',6)
group by calmonth
)
----------------------------------------------------------------------------------------------------------设备
----------------------------------------------------------------------------------------------------------设备缺陷台账
union all
select left('%s',6) as erdat,'%s' as crrq,'设备缺陷总览'as zb,ms as ms,' 'as mx,sl as sl1,wcsl as sl2
from(
select '当月情况'as ms,
ifnull(sum(case when calmonth=left('%s',6)then jsq end),0)as sl,
ifnull(sum(case when calmonth=left('%s',6)and APPROVESTATE='J'then jsq end),0)as wcsl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.PM/HMXFMPM06"
union all
select '当年情况'as ms,
ifnull(sum(case when calmonth between left('%s',4)||'01'and left('%s',6)then jsq end),0)as sl,
ifnull(sum(case when calmonth between left('%s',4)||'01'and left('%s',6)
and APPROVESTATE='J' then jsq end),0)as wcsl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.PM/HMXFMPM06"
)
union all
select calmonth as erdat,'%s' as crrq,'设备缺陷数目趋势'as zb,' ' as ms,' 'as mx,sl as sl1,0 as sl2
from(
select calmonth,
sum(jsq)as sl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.PM/HMXFMPM06"
where calmonth between left(add_years('%s',-1),6) and left('%s',6)
group by calmonth
)
union all
select left('%s',4) as erdat,'%s' as crrq,'当年设备缺陷类型总览'as zb,qxlx as ms,' 'as mx,sl as sl1,wcsl as sl2
from(
select qxlx,
ifnull(sum(case when calmonth between left('%s',4)||'01'and left('%s',6)then jsq end),0)as sl,
ifnull(sum(case when calmonth between left('%s',4)||'01'and left('%s',6)
and APPROVESTATE='J' then jsq end),0)as wcsl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.PM/HMXFMPM06"
where calmonth between left('%s',4)||'01' and left('%s',6)
group by qxlx
)
union all
select left('%s',4) as erdat,'%s' as crrq,'当年公司缺陷分布'as zb,gsjc as ms,' 'as mx,sl as sl1,0 as sl2
from(
select gsjc,
sum(jsq)as sl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.PM/HMXFMPM06"
where calmonth between left('%s',4)||'01' and left('%s',6)
group by gsjc
)
union all
select left('%s',4) as erdat,'%s' as crrq,'当年单公司缺陷类型分布'as zb,gsjc as ms,qxlx as mx,sl as sl1,0 as sl2
from(
select gsjc,
qxlx,
sum(jsq)as sl
from"_SYS_BIC"."XFMGROUP.EDW.03_DATAMART_LAYER.PM/HMXFMPM06"
where calmonth between left('%s',4)||'01' and left('%s',6)
group by gsjc,qxlx
)
'''
sql2 = '''delete from"COMMON"."XFM_SC"where crrq=%s '''
sql3 = '''insert into "COMMON"."XFM_SC"(erdat,crrq,zb,ms,mx,sl1,sl2)values('%s','%s','%s','%s','%s',%s,%s)'''
rep = sql().sql_req(conn, fixed_params().yesterday, sql=sql1)
sql().sql_req(conn, fixed_params().yesterday, sql=sql2)
sql().sql_req(conn, rep, sql=sql3)
def main():
# w.wsd：获取历史序列数据
# w.wsi：获取分钟数据 data= w.wsi(品种代码,指标,开始时间,结束时间,可选参数)；
# w.wst：获取日内tick级别数据 data= w.wst(品种代码,指标,开始时间,结束时间,可选参数)
# w.wss：获历史截面数据 data= w.wss(品种代码,指标,可选参数)
# w.wsq：获取和订阅实时行情数据 data=w.wsq(品种代码,指标,可选参数,回调函数)
# w.wset：获取板块、指数等成分数据 data=w.wset(数据集名称,可选参数)
# w.weqs：获取条件选股结果 data= w.weqs(filtername,…)
# w.wpf：获取资产管理、组合管理数据 data=w.wpf(产品名，数据表名,可选参数)
# w.tlogon交易登录 data = w.tlogon(BrokerID, DepartmentID, LogonAccount, Password, AccountType,...)
w.start()
w.isconnected() # 即判断WindPy是否已经登陆成功
conn = get_connection().hana_connection()
# 定义执行语句与所取参数
# 自定义data存储所需行业数据 美原油期货/布伦特期货/石脑油:CFR日本/PX:CFR中国主港/MX:FOB韩国/醋酸:山东/乙烯:CFR东北亚/甲醇:华东地区
# MEG外盘/PTA外盘/PTA主力期货
# 存储所需PTA及EG各期数据 PTA主力收盘价/PTA一月/PTA三月/PTA五月/PTA七月/PTA九月/PTA十一月/EG一月/EG五月/EG六月/EG九月
data = [
[
'M0000005',
'S0031525',
'S5428960',
'S5432004',
'S5419003',
'S5439491',
'S5400549',
'S5470115',
'S0181381',
'M0000186',
'M0000188',
'TA.CZC',
'TA01M.CZC',
'TA03M.CZC',
'TA05M.CZC',
'TA07M.CZC',
'TA09M.CZC',
'TA11M.CZC',
'EG01M.DCE',
'EG05M.DCE',
'EG06M.DCE',
'EG09M.DCE',
'S0266311',
'M0066316',
'M0000185',
'S5446052',
'S0049499',
'S5400548',
'S5400550',
'S5140506',
'S0069597',
'S5419004',
'M0096870',
'M0331299',
'M0017138',
'M0017126',
'M0000138',
'M5567889',
'M0012303',
'M5528818',
'M5543249',
'M5206730',
'M5525755',
'M0001384',
'M0290205',
'G0000001',
'G1503455',
'G8405271',
'G0008071',
'G0006148',
'G0000027',
'G1500813',
'G1400631',
'G0006148',
'G0000027',
'G1500813',
'G1400631',
'G0006243',
'G0002323',
'G1500013',
'G1400007',
'G0006318',
'G0008060',
'G8405269',
'G8405314',
'G0002325',
'G1137704',
'G1109369',
'S0049499',
'S0049521',
'S5401631',
'S5404287',
'S5402198',
'S5404854',
'S0027307',
'S5401186',
'S0071220',
'S5439164',
'S5439171',
'S5439172',
'S0028007',
'S0106137',
'S0070115',
'S0070116',
'S0070117',
'S0070118',
'S0070130',
'S0070136',
'S0070138',
'S5319442',
'S5319443',
'S5319444',
'S5319446',
'S5319447',
'S5319448',
'S0049638',
'S0049656',
'S0049668',
'S0049707',
'S0049722',
'S0049641',
'S0049644',
'S0049647',
'S0049650',
'S0049653',
'S0049659',
'S0049662',
'S0049665',
'S0049671',
'S0049674',
'S0049677',
'S0049680',
'S0049683',
'S0049686',
'S0049689',
'S0049692',
'S0049710',
'S0049713',
'S0049716',
'S0049719',
'S0049725',
'S0049728',
'S0049731',
'S0049766',
'S0049772',
'S0049784',
'S0049790',
'S0049796',
'G1112986',
'G0007980',
'G0008073',
'G0006147',
'S5431567',
'S5440395',
'S5416997',
'M0017139',
'M0017140',
'M0017141',
'M0017142',
'M0017143',
'M0017144',
'M0017145',
'S5470327',
'S5422007',
'S5440460',
'S0182141',
'S5456800',
],
] # 27个行业数据 + 97个新增数据
sel_sql = '''
select ifnull(to_char(add_days(current_date,-3),'yyyy-mm-dd'),'1995-01-01')
from dummy
'''
del_sql = '''
delete from"COMMON"."XFM_MARKT"
where erdat='%s'
and zb in %s ''' % ('%s', str(tuple(data[0])))
ins_sql = '''
insert into"COMMON"."XFM_MARKT" (ZB , ERDAT ,SPJ)values ('%s','%s',%s)
'''
# 取表中最大日期后一天，与系统前一天日期
results = sql().sql_req(conn, sql=sel_sql)
table_mer = results[0][0]
yesterday = fixed_params().yesterday
yesterday_l = fixed_params().yesterday_l
sql().sql_req(conn, yesterday, sql=del_sql)
for j in range(0, len(data[0])): # 循环27个行业数据
print("\n\n-----第 %i 次通过edb来提取 %s 收盘价数据-----\n" % (j, str(data[0][j])))
edbdata = w.edb(str(data[0][j]), table_mer, yesterday_l,
"Fill=Previous")
if edbdata.ErrorCode != 0:
continue
print(edbdata)
for i in range(0, len(edbdata.Times)): # 循环所有日期数据
sqllist = []
sqllist.append(str(edbdata.Codes[0])) # 插入code数据
print(sqllist)
sqllist.append(edbdata.Times[i].strftime('%Y%m%d')) # 插入日期数据
for k in range(0, len(edbdata.Fields)): # 循环所有获取数据（这里只有收盘价）
sqllist.append(edbdata.Data[k][i]) # 插入收盘价数据
if sqllist[2] is None or np.isnan(
sqllist[2]): # 判断是否为NAN,是则进入下个日期循环
continue
sqltuple = tuple(sqllist)
print(sqltuple)
sql().sql_req(conn, sqltuple, sql=ins_sql)
get_connection().close_connection(conn)