<select id="getWeeklyData"
resultType="com.huawei.it.tgmes.tmas.weeklyplanaccuracy.entity.bo.BaseWeeklyPlanAccuracy">
With temp1 As (
Select distinct to_char(current_timestamp, 'yyyyMMdd') As batchNo,
to_char(trunc(wwi.plan_online_time,'IW'),'yyyy-MM-dd') As week,
to_char(trunc(wwi.plan_online_time,'IW'),'yyyyMMdd') As weekjoin,
wwi.plan_online_time As planOnLineTime,
wwi.workorder_name As workOrderName,
wwi.state_Type As stateType,
case
when wwi.manufacture_mode ='ODM' then 'ODM'
when substr(wwi.mfg_site_name,1,2)='华为' then '自制'
when wwi.manufacture_mode='EMS' then 'EMS' end As manufactureMode,
wwi.item_code As itemCode,
wwi.work_serction As workSerction,
wwi.work_serction As prodItemType,
wwi.prod_model_code As prodModelCode,
wwi.release_time As releaseTime,
wwi.factory_code As factoryCode,
bfi.factory_name As factoryName,
wwi.workorder_qty As planQty,
bpi.prodLineCode,
bpi.prodLineCnName,
bpi.prodLineEnName,
bpi.prodSeriesCode,
bpi.prodSeriesCnName,
coalesce(wpc.prod_convert_name, wpc.prod_series_en_name) As prodSeriesEnName
From (
Select distinct wf.plan_online_time,wf.workorder_name,wf.state_Type,wf.item_code,wf.work_serction,
wf.release_time,wf.factory_code,wf.workorder_qty,wf.prod_model_code,wp.manufacture_mode,wp.mfg_site_name
From T_WAVE_WORKORDER_INFO wf
inner join T_EBC_PROD_INSTR wp
on wf.workorder_name = wp.prod_instr_no
and wp.manufacture_mode is not null
and wp.manufacture_mode <![CDATA[<>]]> ''
and wp.manufacture_mode <![CDATA[<>]]> 'NA'
Where wf.state_Type In (3, 12, 4)
And (substring (wf.workorder_name,3,1) = 'Z' or substring (wf.workorder_name,3,1) = '1')
<![CDATA[
And wf.plan_online_time >= TO_TIMESTAMP(#{startTime,jdbcType=VARCHAR}, 'yyyy-mm-dd hh24:mi:ss')
And wf.plan_online_time <= TO_TIMESTAMP(#{endTime,jdbcType=VARCHAR}, 'yyyy-mm-dd hh24:mi:ss')
]]>
) wwi
Left Join T_BASE_WORKORDER_PROD_CONVERT wpc
On wpc.prod_model_code = wwi.prod_model_code
Left Join (
Select prod_line_code As prodLineCode,
prod_line_cn_name As prodLineCnName,
prod_line_en_name As prodLineEnName,
prod_series_code As prodSeriesCode,
prod_series_cn_name As prodSeriesCnName,
prod_series_en_name As prodSeriesEnName,
prod_model_code As prodmodelcode
From T_BASE_PROD_CATALOG
Group By prod_line_code,
prod_line_cn_name,
prod_line_en_name,
prod_family_code,
prod_family_cn_name,
prod_family_en_name,
prod_series_code,
prod_series_cn_name,
prod_series_en_name,
prod_model_code,
prod_model_cn_name,
prod_model_en_name) bpi On
wwi.prod_model_code = bpi.prodmodelcode
Left Join t_Base_Factory_Info bfi On
wwi.factory_code = bfi.factory_code),
temp2 As (
Select batchNo,
week,
weekjoin,
sum(planQty) As workOrderQty,
prodItemType,
factoryCode,
factoryName,
prodSeriesEnName,
prodLineCode,
prodLineCnName,
prodLineEnName,
prodSeriesCode,
prodSeriesCnName,
manufactureMode
From temp1
Group By (batchNo, week, weekjoin, prodItemType, factoryCode, factoryName, prodSeriesEnName,
prodLineCode, prodLineCnName, prodLineEnName, prodSeriesCode, prodSeriesCnName,
manufactureMode)
),
temp3 As (
Select SUBSTR(T.tmas_version, 6, 13) As weekjoin,
t.tmas_version,
t.factory_code,
t.prod_series_en_name,
t.produce_type_cn,
sum(t.qty) As systemLockQty
From t_base_two_week_lock t
Where t.item = '系统互锁量'
Group By t.factory_code, t.prod_series_en_name, t.produce_type_cn, t.tmas_version
)
Select t2.*, t3.systemLockQty
From temp2 t2
Join temp3 t3 On t2.weekjoin = t3.weekjoin And t2.prodItemType = t3.produce_type_cn And
t2.factorycode = t3.factory_code And t2.prodSeriesEnName = t3.prod_series_en_name
</select>
<select id="getByFactoryHeaderList" resultType="com.huawei.it.tgmes.tmas.basedata.entity.vo.KeyValue">
With weeks As (
With daytimes As (
Select daytime::date
From generate_series(
(Select #{condition.startweekTimeStr}::date),/*--查询开始日期(可根据需求调整)*/
(Select #{condition.endweekTimeStr}::date),/*--查询结束日期(可根据需求调整)*/
'1 day'<!--间隔(可根据需求调整)-->
) s(daytime) <!--获取连续日期-->
)
Select (Select ts.daytime - (Case
When
extract(Dow From ts.daytime) = 0
Then 7
Else
extract(Dow From ts.daytime)
End - 1 || 'day')::interval)
week1
From daytimes ts
Group By week1
Order By week1 Asc <!--获取日期对应的周一-->
)
<choose>
<when test="condition.dimensionStr eq 1">
select to_char(s.week1,'yyyy-mm-dd') as "key",
to_char(s.week1,'yyyy-mm-dd') as "value"
from weeks s group by "key"
order by "key" asc <!--周-->
</when>
<when test="condition.dimensionStr eq 2">
select to_char(s.week1,'yyyy-mm') as "key",
to_char(s.week1,'yyyy-mm') as "value"
from weeks s group by "key"
order by "key" asc <!--月-->
</when>
<when test="condition.dimensionStr eq 3">
select to_char(s.week1,'yyyy')||'_Q'||(select quarters from (
select extract (quarter from s.week1) quarters)t
) as "key" ,
to_char(s.week1,'yyyy')||'_Q'||(select quarters from (
select extract (quarter from s.week1) quarters)t
) as "value"
from weeks s group by "key"
order by "key" asc
</when>
<when test="condition.dimensionStr eq 4">
select to_char(s.week1,'yyyy') as "key",to_char(s.week1,'yyyy') as "value" from weeks s group by "key"
order by "key" asc <!--年-->
</when>
</choose>
</select>分析这串 代码
最新发布