<?xml version="1
.0" encoding="UTF-
8" ?>
<!--
~ /*
~ * Copyright
(c
) Huawei Technolog
ies Co
., Ltd
. 2020-2020
. All rights reserved
.
~ */
-->
<!DOCTYPE mapper PUBLIC "-//mybatis
.org//DTD Mapper 3
.0//EN" "http
://mybatis
.org/dtd/mybatis-3-mapper
.dtd" >
<mapper namespace="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.dao
.IPfcResgroupPromiseDao">
<sql id ="hasEmptySearch">
<if test=" queryFactoryFlag != null and queryFactoryFlag eq 'Y'
.toString
() and hasEmptyFlag != null and
(hasEmptyFlag eq 'Y'
.toString
() or hasEmptyFlag eq 'N'
.toString
() ) ">
<if test=" hasEmptyFlag eq 'Y'
.toString
() " >
and exists
</if>
<if test="hasEmptyFlag eq 'N'
.toString
() ">
and not exists
</if>
(select 1
from t_pfc_forecast_resgroup_bucket pfrbc
where
<choose>
<when test="functionType != null and functionType eq '2'
.toString
()" >
(pfrbc
.high_end_auto_
line is null or pfrbc
.standard_auto_
line is null or pfrbc
.low_end_auto_
line is null
)
</when>
<otherwise>
(pfrbc
.factory_qty is null or pfrbc
.factory_
line_qty is null
)
</otherwise>
</choose>
and pfrbc
.operation_flag = '1'
and pfrbc
.pfr_id = pfr
.pfr_id
)
</if>
<if test=" queryFactoryFlag != null and queryFactoryFlag eq 'N'
.toString
() and hasEmptyFlag != null and
(hasEmptyFlag eq 'Y'
.toString
() or hasEmptyFlag eq 'N'
.toString
() ) ">
<if test=" hasEmptyFlag eq 'Y'
.toString
() " >
and exists
</if>
<if test=" hasEmptyFlag eq 'N'
.toString
() ">
and not exists
</if>
(select 1
from t_pfc_forecast_resgroup_bucket pfrbc
where
(pfrbc
.ie_qty is null or pfrbc
.ie_
line_qty is null
)
and pfrbc
.operation_flag in
('1','2'
)
and pfrbc
.pfr_id = pfr
.pfr_id
)
</if>
</sql>
<sql id ="planIDSearch">
and pfr
.plan_id in
<choose>
<when test=" planIDList != null and planIDList
.size > 0 " >
<foreach collection="planIDList" item="planID" separator="," index="index" open="
(" close="
)">
#{planID,jdbcType=VARCHAR}
</foreach>
</when>
<otherwise>
(select max
(t
.plan_id
) from T_PFC_FORECAST_RESGROUP t
where t
.promise_type = '0'
)
</otherwise>
</choose>
</sql>
<sql id ="commonF
ieldSearch">
<if test=" supplyCenterCodeList != null and supplyCenterCodeList
.size > 0 ">
and pfr
.supply_center_code in
<foreach collection="supplyCenterCodeList" item="supplyCenterCode" separator="," index="index" open="
(" close="
)">
#{supplyCenterCode,jdbcType=VARCHAR}
</foreach>
</if>
<if test=" prod
LineCodeList != null and prod
LineCodeList
.size > 0 ">
and pfr
.prod_
line_code in
<foreach collection="prod
LineCodeList" item="prod
LineCode" separator="," index="index" open="
(" close="
)">
#{prod
LineCode,jdbcType=VARCHAR}
</foreach>
</if>
<if test=" prodAreaCodeList != null and prodAreaCodeList
.size > 0 ">
and pfr
.prod_area_code in
<foreach collection="prodAreaCodeList" item="prodAreaCode" separator="," index="index" open="
(" close="
)">
#{prodAreaCode,jdbcType=VARCHAR}
</foreach>
</if>
<if test=" prodFamilyCodeList != null and prodFamilyCodeList
.size > 0 ">
and pfr
.prod_family_code in
<foreach collection="prodFamilyCodeList" item="prodFamilyCode" separator="," index="index" open="
(" close="
)">
#{prodFamilyCode,jdbcType=VARCHAR}
</foreach>
</if>
<if test=" prodSer
iesCodeList != null and prodSer
iesCodeList
.size > 0 ">
and pfr
.prod_ser
ies_code in
<foreach collection="prodSer
iesCodeList" item="prodSer
iesCode" separator="," index="index" open="
(" close="
)">
#{prodSer
iesCode,jdbcType=VARCHAR}
</foreach>
</if>
<if test=" prodResgroupList != null and prodResgroupList
.size > 0 ">
and pfr
.prod_resgroup in
<foreach collection="prodResgroupList" item="prodResgroup" separator="," index="index" open="
(" close="
)">
#{prodResgroup,jdbcType=VARCHAR}
</foreach>
</if>
<if test="factoryCodeList != null and factoryCodeList
.size > 0">
and pfr
.factory_code in
<foreach collection="factoryCodeList" item="factoryCode" separator="," index="index" open="
(" close="
)">
#{factoryCode,jdbcType=VARCHAR}
</foreach>
</if>
<if test="siteCodeList != null and siteCodeList
.size > 0">
and pfr
.site_code in
<foreach collection="siteCodeList" item="siteCode" separator="," index="index" open="
(" close="
)">
#{siteCode,jdbcType=VARCHAR}
</foreach>
</if>
<if test="locationNameList != null and locationNameList
.size > 0">
and pfr
.location_name in
<foreach collection="locationNameList" item="locationName" separator="," index="index" open="
(" close="
)">
#{locationName,jdbcType=VARCHAR}
</foreach>
</if>
<if test="mfgTypeList != null and mfgTypeList
.size > 0">
and pfr
.mfg_type in
<foreach collection="mfgTypeList" item="mfgType" separator="," index="index" open="
(" close="
)">
#{mfgType,jdbcType=VARCHAR}
</foreach>
</if>
<if test="supplyModeList != null and supplyModeList
.size > 0">
and pfr
.supply_mode in
<foreach collection="supplyModeList" item="supplyMode" separator="," index="index" open="
(" close="
)">
#{supplyMode,jdbcType=VARCHAR}
</foreach>
</if>
<if test="produceTypeCodeList != null and produceTypeCodeList
.size > 0">
and pfr
.produce_type_code in
<foreach collection="produceTypeCodeList" item="produceTypeCode" separator="," index="index" open="
(" close="
)">
#{produceTypeCode,jdbcType=VARCHAR}
</foreach>
</if>
</sql>
<sql id= "condition">
and pfr
.promise_type = '0'
<if test=" factoryVisibleFlagStr != null and factoryVisibleFlagStr eq 'Y'
.toString
() ">
and pfr
.factory_visible_flag = 'Y'
</if>
<include refid="hasEmptySearch"/>
<include refid="commonF
ieldSearch"/>
<include refid="planIDSearch"/>
</sql>
<select id="getPfcResgroupListByCondition"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.bo
.PfcForecastResgroup">
select
pfr_id id,
plan_id,
supply_center_id,
supply_center_code,
supply_center_name_cn,
supply_center_name_en,
prod_
line_code,
prod_
line_cn_name,
prod_
line_en_name,
prod_area_code,
prod_area_cn_name,
prod_area_en_name,
prod_family_code,
prod_family_cn_name,
prod_family_en_name,
prod_ser
ies_code,
prod_ser
ies_cn_name,
prod_ser
ies_en_name,
prod_resgroup,
prod_resgroup_desc,
process_plan_useraccount,
process_plan_username,
mfg_type,
plan_operation_enty_name,
produce_type_code produce_type,
produce_type_cn,
produce_type_en,
factory_code,
factory_name,
overseas_ckd_flag,
remark,
created_by,
creation_date,
last_updated_by,
last_updated_date,
vendor_code,
location_code,
site_code,
location_name
from T_PFC_FORECAST_RESGROUP pfr
<where>
<include refid="condition"/>
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="FactoryInfo
:pfr
.factory_code"></addProgram>
]]>
<if test="permissionFlag == true">
and exists
(
select 1 from t_product_type_rel_v r where r
.prod_
line_code = pfr
.prod_
line_code
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="ProductTypeInfo
:r
.product_type"></addProgram>
]]>
)
</if>
</where>
</select>
<select id="getPfcResgroupBucketList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.bo
.PfcForecastResgroupBucket">
select
pfrb
.pfrb_id as id,
pfrb
.pfr_id as pfrID,
pfrb
.bucket,
pfrb
.bucket_type,
pfrb
.system_qty,
pfrb
.system_
line_qty,
pfrb
.promise_qty,
pfrb
.promise_
line_qty,
pfrb
.promise_qty_account,
pfrb
.factory_qty,
pfrb
.factory_
line_qty,
pfrb
.ie_qty,
pfrb
.ie_
line_qty,
pfrb
.high_end_auto_
line,
pfrb
.standard_auto_
line,
pfrb
.low_end_auto_
line,
pfrb
.remark,
pfrb
.created_by,
pfrb
.creation_date,
pfrb
.last_updated_by,
pfrb
.last_updated_date
from T_PFC_FORECAST_RESGROUP pfr
left join T_PFC_FORECAST_RESGROUP_BUCKET pfrb on pfrb
.pfr_id = pfr
.pfr_id
<where>
<include refid="condition"/>
<if test=" pfrIDList!= null and pfrIDList
.size > 0">
and pfrb
.pfr_id in
<foreach collection="pfrIDList" item="pfrID" open="
(" separator=","
close="
)">
#{pfrID,jdbcType=VARCHAR}
</foreach>
</if>
<choose>
<when test=" queryFactoryFlag != null and queryFactoryFlag eq 'Y'
.toString
() " >
and pfrb
.operation_flag = '1'
</when>
<when test=" queryFactoryFlag != null and queryFactoryFlag eq 'N'
.toString
() " >
and pfrb
.operation_flag in
('1','2'
)
</when>
<otherwise>
and 1 = 2
</otherwise>
</choose>
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="FactoryInfo
:pfr
.factory_code"></addProgram>
]]>
</where>
</select>
<select id="getPfcResgroupBucketSumList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.PfcForecastResgroupBucketSum">
select pfrb
.bucket,
sum
(coalesce
(pfrb
.high_end_auto_
line, 0
) + coalesce
(pfrb
.standard_auto_
line, 0
) + coalesce
(pfrb
.low_end_auto_
line, 0
)) as sumNum
from T_PFC_FORECAST_RESGROUP pfr
left join T_PFC_FORECAST_RESGROUP_BUCKET pfrb on pfrb
.pfr_id = pfr
.pfr_id
<where>
<include refid="condition"/>
<if test=" pfrIDList!= null and pfrIDList
.size > 0">
and pfrb
.pfr_id in
<foreach collection="pfrIDList" item="pfrID" open="
(" separator=","
close="
)">
#{pfrID,jdbcType=VARCHAR}
</foreach>
</if>
<choose>
<when test=" queryFactoryFlag != null and queryFactoryFlag eq 'Y'
.toString
() " >
and pfrb
.operation_flag = '1'
</when>
<when test=" queryFactoryFlag != null and queryFactoryFlag eq 'N'
.toString
() " >
and pfrb
.operation_flag in
('1','2'
)
</when>
<otherwise>
and 1 = 2
</otherwise>
</choose>
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="FactoryInfo
:pfr
.factory_code"></addProgram>
]]>
</where>
group by pfrb
.bucket
</select>
<select id="getBucketSumListForProdSer
ies"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.PfcForecastResgroupBucketSum">
select pfr
.prod_ser
ies_en_name,
pfrb
.bucket,
sum
(coalesce
(pfrb
.high_end_auto_
line, 0
) + coalesce
(pfrb
.standard_auto_
line, 0
) + coalesce
(pfrb
.low_end_auto_
line, 0
)) as sumNum
from T_PFC_FORECAST_RESGROUP pfr
left join T_PFC_FORECAST_RESGROUP_BUCKET pfrb on pfrb
.pfr_id = pfr
.pfr_id
<where>
<include refid="condition"/>
<if test=" pfrIDList!= null and pfrIDList
.size > 0">
and pfrb
.pfr_id in
<foreach collection="pfrIDList" item="pfrID" open="
(" separator=","
close="
)">
#{pfrID,jdbcType=VARCHAR}
</foreach>
</if>
<choose>
<when test=" queryFactoryFlag != null and queryFactoryFlag eq 'Y'
.toString
() " >
and pfrb
.operation_flag = '1'
</when>
<when test=" queryFactoryFlag != null and queryFactoryFlag eq 'N'
.toString
() " >
and pfrb
.operation_flag in
('1','2'
)
</when>
<otherwise>
and 1 = 2
</otherwise>
</choose>
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="FactoryInfo
:pfr
.factory_code"></addProgram>
]]>
</where>
group by pfr
.prod_ser
ies_en_name, pfrb
.bucket
</select>
<update id="updateFactoryPromiseList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.UpdatePfcForecastResgroupBucket">
<foreach collection="updatePfcForecastResgroupBuckets" item="bucket">
<if test=" bucket
.typeCode != null">
update t_pfc_forecast_resgroup_bucket pfrb
set
<if test=" bucket
.typeCode eq 'FACTORY_QTY'
.toString
() ">
factory_qty = #{bucket
.factoryQty,jdbcType=INTEGER},
</if>
<if test=" bucket
.typeCode eq 'FACTORY_
LINE_QTY'
.toString
() ">
factory_
line_qty = #{bucket
.factory
LineQty,jdbcType=DOUBLE},
</if>
<if test=" bucket
.typeCode eq 'HIGH_END_AUTO_
LINE'
.toString
() ">
high_end_auto_
line = #{bucket
.highEndAuto
Line,jdbcType=DOUBLE},
</if>
<if test=" bucket
.typeCode eq 'STANDARD_AUTO_
LINE'
.toString
() ">
standard_auto_
line = #{bucket
.standardAuto
Line,jdbcType=DOUBLE},
</if>
<if test=" bucket
.typeCode eq 'LOW_END_AUTO_
LINE'
.toString
() ">
low_end_auto_
line = #{bucket
.lowEndAuto
Line,jdbcType=DOUBLE},
</if>
<if test=" bucket
.typeCode eq 'FACTORY_QTY'
.toString
() || bucket
.typeCode eq 'FACTORY_
LINE_QTY'
.toString
() ">
factory_last_updated_by = #{bucket
.lastUpdatedBy,jdbcType=VARCHAR},
factory_last_updated_date = cast
(#{bucket
.lastUpdatedDate} as timestamp
),
</if>
last_updated_by = #{bucket
.lastUpdatedBy,jdbcType=VARCHAR},
last_updated_date = cast
(#{bucket
.lastUpdatedDate} as timestamp
)
where
<choose>
<when test="authFactoryCodeList != null and authFactoryCodeList
.size > 0" >
exists
(select 1 from t_pfc_forecast_resgroup pfr
where pfr
.pfr_id = pfrb
.pfr_id
and pfr
.factory_visible_flag = 'Y'
and pfr
.factory_code in
<foreach collection="authFactoryCodeList" item="authFactoryCode" separator="," index="index" open="
(" close="
)">
#{authFactoryCode,jdbcType=VARCHAR}
</foreach>
)
</when>
<otherwise>
1 = 2
</otherwise>
</choose>
and pfrb
.operation_flag = '1'
and pfrb
.pfrb_id = #{bucket
.id,jdbcType=VARCHAR};
</if>
</foreach>
</update>
<delete id="deleteForecastResgroupTempbyUser"
parameterType="java
.lang
.String" >
delete from t_pfc_forecast_resgroup_temp t
where t
.last_updated_by = #{lastUpdatedBy,jdbcType=VARCHAR};
delete from t_pfc_forecast_resgroup_bk_tp t
where t
.last_updated_by = #{lastUpdatedBy,jdbcType=VARCHAR};
</delete>
<insert id="batchInsertForecastResgroupTemp"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.bo
.PfcForecastResgroup">
<if test=" pfcForecastResgroupList != null and pfcForecastResgroupList
.size > 0 ">
<foreach collection="pfcForecastResgroupList" item="pfcForecastResgroup">
insert into T_PFC_FORECAST_RESGROUP_TEMP
(
PFR_ID,
PLAN_ID,
SUPPLY_CENTER_NAME_CN,
PROD_
LINE_CN_NAME,
PROD_AREA_CN_NAME,
PROD_FAMILY_CN_NAME,
PROD_SER
IES_CN_NAME,
PROD_RESGROUP_DESC,
PROCESS_PLAN_USERACCOUNT,
MFG_TYPE,
PLAN_OPERATION_ENTY_NAME,
PRODUCE_TYPE_CN,
OVERSEAS_CKD_FLAG,
LAST_UPDATED_BY,
LAST_UPDATED_DATE,
SITE_CODE,
LOCATION_NAME
)
values
(
#{pfcForecastResgroup
.id,jdbcType=VARCHAR},
#{pfcForecastResgroup
.planID,jdbcType=VARCHAR},
#{pfcForecastResgroup
.supplyCenterNameCn,jdbcType=VARCHAR},
#{pfcForecastResgroup
.prod
LineCnName,jdbcType=VARCHAR},
#{pfcForecastResgroup
.prodAreaCnName,jdbcType=VARCHAR},
#{pfcForecastResgroup
.prodFamilyCnName,jdbcType=VARCHAR},
#{pfcForecastResgroup
.prodSer
iesCnName,jdbcType=VARCHAR},
#{pfcForecastResgroup
.prodResgroupDesc,jdbcType=VARCHAR},
#{pfcForecastResgroup
.processPlanUseraccount,jdbcType=VARCHAR},
#{pfcForecastResgroup
.mfgType,jdbcType=VARCHAR},
#{pfcForecastResgroup
.planOperationEntyName,jdbcType=VARCHAR},
#{pfcForecastResgroup
.produceTypeCn,jdbcType=VARCHAR},
#{pfcForecastResgroup
.overseasCkdFlag,jdbcType=VARCHAR},
#{pfcForecastResgroup
.lastUpdatedBy,jdbcType=VARCHAR},
cast
(#{pfcForecastResgroup
.lastUpdatedDate} as timestamp
),
#{pfcForecastResgroup
.siteCode,jdbcType=VARCHAR},
#{pfcForecastResgroup
.locationName,jdbcType=VARCHAR}
);
<foreach collection="pfcForecastResgroup
.pfcForecastResgroupBucketList" item="bucket">
insert into T_PFC_FORECAST_RESGROUP_BK_TP
(
PFR_ID,
BUCKET,
BUCKET_TYPE,
FACTORY_QTY,
FACTORY_
LINE_QTY,
IE_QTY,
IE_
LINE_QTY,
LAST_UPDATED_BY,
LAST_UPDATED_DATE
)
values
(
#{bucket
.pfrID,jdbcType=VARCHAR},
#{bucket
.bucket,jdbcType=VARCHAR},
#{bucket
.bucketType,jdbcType=VARCHAR},
#{bucket
.factoryQty,jdbcType=INTEGER},
#{bucket
.factory
LineQty},
#{bucket
.ieQty,jdbcType=INTEGER},
#{bucket
.ieLineQty},
#{bucket
.lastUpdatedBy,jdbcType=VARCHAR},
cast
(#{bucket
.lastUpdatedDate} as timestamp
));
</foreach>
</foreach>
</if>
</insert>
<select id="getImportForecastResgroupExist"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.bo
.PfcForecastResgroup"
resultType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.bo
.PfcForecastResgroup">
select pfr
.plan_id as planID,
pfr
.supply_center_name_cn,
pfr
.mfg_type,
pfr
.plan_operation_enty_name,
pfr
.prod_resgroup_desc,
pfr
.produce_type_cn,
pfr
.factory_code,
pfr
.location_name,
pfr
.pfr_id id,
pfr
.factory_visible_flag
from t_pfc_forecast_resgroup pfr
where pfr
.promise_type = '0'
and
(pfr
.plan_id,
pfr
.supply_center_name_cn,
pfr
.mfg_type,
pfr
.plan_operation_enty_name,
pfr
.prod_resgroup_desc,
pfr
.produce_type_cn,
pfr
.location_name
) in
<foreach collection="itemList" item="item" separator="," index="index" open="
(" close="
)">
(#{item
.planID},
#{item
.supplyCenterNameCn},
#{item
.mfgType},
#{item
.planOperationEntyName},
#{item
.prodResgroupDesc},
#{item
.produceTypeCn},
#{item
.locationName}
)
</foreach>
</select>
<update id="updateFactoryPromiseListByIdBuketNew"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.bo
.PfcForecastResgroupBucket">
update t_pfc_forecast_resgroup_bucket b
set factory_qty = COALESCE
(cast
(#{item
.factoryQty} as Integer
), b
.ie_qty
),
factory_
line_qty = COALESCE
(#{item
.factory
LineQty,jdbcType=DOUBLE}, b
.ie_
line_qty
),
last_updated_by = #{item
.lastUpdatedBy,jdbcType=VARCHAR},
last_updated_date = cast
(#{item
.lastUpdatedDate,jdbcType=TIMESTAMP} as timestamp
),
ie_last_updated_by = #{item
.lastUpdatedBy,jdbcType=VARCHAR},
ie_last_updated_date = cast
(#{item
.lastUpdatedDate,jdbcType=TIMESTAMP} as timestamp
)
where b
.pfr_id = #{item
.insertId,jdbcType=VARCHAR}
and to_char
(b
.BUCKET, 'yy-mm-dd'
) = to_char
(cast
(#{item
.bucket,jdbcType=TIMESTAMP} as timestamp
), 'yy-mm-dd'
)
and b
.operation_flag = '1';
</update>
<update id="updateTerminalAuto
LinePromiseListById"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.bo
.PfcForecastResgroupBucket">
update t_pfc_forecast_resgroup_bucket b
set
<if test="item
.highEndAuto
Line != null">
high_end_auto_
line = #{item
.highEndAuto
Line,jdbcType=DOUBLE},
</if>
<if test="item
.standardAuto
Line != null">
standard_auto_
line = #{item
.standardAuto
Line,jdbcType=DOUBLE},
</if>
<if test="item
.lowEndAuto
Line != null">
low_end_auto_
line = #{item
.lowEndAuto
Line,jdbcType=DOUBLE},
</if>
last_updated_by = #{item
.lastUpdatedBy,jdbcType=VARCHAR},
last_updated_date = cast
(#{item
.lastUpdatedDate,jdbcType=TIMESTAMP} as timestamp
)
where b
.pfr_id = #{item
.insertId,jdbcType=VARCHAR}
and to_char
(b
.BUCKET, 'yy-mm-dd'
) = to_char
(cast
(#{item
.bucket,jdbcType=TIMESTAMP} as timestamp
), 'yy-mm-dd'
)
and b
.operation_flag = '1';
</update>
<update id="update
IEPromiseListFromSelectTemp"
parameterType="java
.util
.List">
<foreach collection="tempList" item="item">
update t_pfc_forecast_resgroup_bucket b
set
ie_qty = coalesce
(cast
(#{item
.ie_qty} as Integer
),b
.ie_qty
),
ie_
line_qty = coalesce
(#{item
.ie_
line_qty,jdbcType=DOUBLE},b
.ie_
line_qty
),
last_updated_by = #{item
.last_updated_by},
last_updated_date =cast
(#{item
.last_updated_date} as timestamp
),
ie_last_updated_by = #{item
.ie_last_updated_by},
ie_last_updated_date = cast
(#{item
.ie_last_updated_date} as timestamp
)
where b
.pfrb_id = #{item
.pfrb_id};
</foreach>
</update>
<select id="getPlanIDList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.basedata
.entity
.vo
.Key
Value">
select pfr
.plan_id as key,
pfr
.plan_id as
value
from T_PFC_FORECAST_RESGROUP pfr
where pfr
.promise_type = '0'
group by pfr
.plan_id
order by pfr
.plan_id desc
</select>
<select id="getMaxPlanID"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="java
.lang
.String">
select max
(pfr
.plan_id
)
from T_PFC_FORECAST_RESGROUP pfr
where pfr
.promise_type = '0'
</select>
<select id="getThisMonthPlanIDList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition" resultType="string">
select o
.plan_id from
( select pfr
.plan_id,
(regexp_matches
(pfr
.plan_id, '[0-9]+'
))[1] str
from T_PFC_FORECAST_RESGROUP pfr
where pfr
.promise_type = '0'
) o where str between cast
(#{beginDateStr,jdbcType=VARCHAR} as varchar
) and cast
(#{endDateStr,jdbcType=VARCHAR} as varchar
)
group by o
.plan_id
</select>
<select id="getPfcResgroupDateList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.basedata
.entity
.vo
.Key
Value">
select 'f'||to_char
(pfrb
.bucket, 'yyyymmdd'
) as key,
to_char
(pfrb
.bucket, 'yyyy/mm/dd'
)||
(case when pfrb
.bucket_type = 'WEEK' then '
(周
)' else '
(日
)' end
) as
value
from T_PFC_FORECAST_RESGROUP_BUCKET pfrb, T_PFC_FORECAST_RESGROUP pfr
where
pfr
.plan_id in
<foreach collection="planIDList" item="planID" separator="," index="index" open="
(" close="
)">
#{planID,jdbcType=VARCHAR}
</foreach>
and pfrb
.PFR_ID = pfr
.PFR_ID
and pfrb
.bucket is not null
and pfrb
.bucket_type is not null
and pfr
.promise_type = '0'
<choose>
<when test=" queryFactoryFlag != null and queryFactoryFlag eq 'Y'
.toString
() " >
and pfrb
.operation_flag = '1'
</when>
<when test=" queryFactoryFlag != null and queryFactoryFlag eq 'N'
.toString
() " >
and pfrb
.operation_flag in
('1','2'
)
</when>
<otherwise>
and 1 = 2
</otherwise>
</choose>
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="FactoryInfo
:pfr
.factory_code"></addProgram>
]]>
group by pfrb
.bucket,pfrb
.bucket_type
order by pfrb
.bucket asc
</select>
<select id="getSupplyCenterList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.basedata
.entity
.vo
.Key
Value">
select pfr
.supply_center_code as key,
pfr
.supply_center_name_cn as
value
from T_PFC_FORECAST_RESGROUP pfr
<where>
<include refid="condition"/>
</where>
group by pfr
.supply_center_code,pfr
.supply_center_name_cn
</select>
<select id="getSupplyCenterList2"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.basedata
.entity
.vo
.Key
Value">
select pfr
.supply_center_code as key,
pfr
.supply_center_name_cn as
value
from T_PFC_FORECAST_RESGROUP pfr
where 1=1
<choose>
<when test=" planIDList != null and planIDList
.size > 0 " >
and pfr
.plan_id in
<foreach collection="planIDList" item="planID" separator="," index="index" open="
(" close="
)">
#{planID,jdbcType=VARCHAR}
</foreach>
</when>
</choose>
group by pfr
.supply_center_code,pfr
.supply_center_name_cn
</select>
<select id="getMfgTypeList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.basedata
.entity
.vo
.Key
Value">
select pfr
.mfg_type as key,
pfr
.mfg_type as
value
from T_PFC_FORECAST_RESGROUP pfr
<where>
<include refid="condition"/>
</where>
group by pfr
.mfg_type
</select>
<select id="getProduceTypeList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.basedata
.entity
.vo
.Key
Value">
select pfr
.produce_type_code as key,
pfr
.produce_type_cn as
value
from T_PFC_FORECAST_RESGROUP pfr
<where>
<include refid="condition"/>
</where>
group by pfr
.produce_type_code,pfr
.produce_type_cn
</select>
<select id="getProd
LineList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.basedata
.entity
.vo
.Key
Value">
select pfr
.prod_
line_code as key,
pfr
.prod_
line_cn_name as
value
from T_PFC_FORECAST_RESGROUP pfr
<where>
<include refid="condition"/>
<if test="permissionFlag == true">
and exists
(
select 1 from t_product_type_rel_v r where r
.prod_
line_code = pfr
.prod_
line_code
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="ProductTypeInfo
:r
.product_type"></addProgram>
]]>
)
</if>
</where>
group by pfr
.prod_
line_code,pfr
.prod_
line_cn_name
</select>
<select id="getProdAreaList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.basedata
.entity
.vo
.Key
Value">
select pfr
.prod_area_code as key,
pfr
.prod_area_cn_name as
value
from T_PFC_FORECAST_RESGROUP pfr
<where>
<include refid="condition"/>
<if test="permissionFlag == true">
and exists
(
select 1 from t_product_type_rel_v r where r
.prod_
line_code = pfr
.prod_
line_code
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="ProductTypeInfo
:r
.product_type"></addProgram>
]]>
)
</if>
</where>
group by pfr
.prod_area_code,pfr
.prod_area_cn_name
</select>
<select id="getProdFamilyList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.basedata
.entity
.vo
.Key
Value">
select pfr
.prod_family_code as key,
pfr
.prod_family_cn_name as
value
from T_PFC_FORECAST_RESGROUP pfr
<where>
<include refid="condition"/>
<if test="permissionFlag == true">
and exists
(
select 1 from t_product_type_rel_v r where r
.prod_
line_code = pfr
.prod_
line_code
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="ProductTypeInfo
:r
.product_type"></addProgram>
]]>
)
</if>
</where>
group by pfr
.prod_family_code,pfr
.prod_family_cn_name
</select>
<select id="getProdSer
iesList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.basedata
.entity
.vo
.Key
Value">
select pfr
.prod_ser
ies_code as key,
pfr
.prod_ser
ies_en_name as
value
from T_PFC_FORECAST_RESGROUP pfr
<where>
<include refid="condition"/>
<if test="permissionFlag == true">
and exists
(
select 1 from t_product_type_rel_v r where r
.prod_
line_code = pfr
.prod_
line_code
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="ProductTypeInfo
:r
.product_type"></addProgram>
]]>
)
</if>
</where>
group by pfr
.prod_ser
ies_code,pfr
.prod_ser
ies_en_name
</select>
<select id="getProdResgroupList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.basedata
.entity
.vo
.Key
Value">
select pfr
.prod_resgroup as key,
pfr
.prod_resgroup_desc as
value
from T_PFC_FORECAST_RESGROUP pfr
<where>
<include refid="condition"/>
</where>
group by pfr
.prod_resgroup,pfr
.prod_resgroup_desc
</select>
<select id="getFactoryList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.basedata
.entity
.vo
.Key
Value">
select pfr
.factory_code as key,
pfr
.factory_name as
value
from t_pfc_forecast_resgroup pfr
<where>
<include refid="condition"/>
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="FactoryInfo
:pfr
.factory_code"></addProgram>
]]>
</where>
group by pfr
.factory_code,pfr
.factory_name
</select>
<select id="getLocationList"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.QueryPfcResgroupPromiseCondition"
resultType="com
.huawei
.it
.tgmes
.tmas
.basedata
.entity
.vo
.Key
Value">
select pfr
.site_code as key,
pfr
.location_name as
value
from t_pfc_forecast_resgroup pfr
<where>
<include refid="condition"/>
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="FactoryInfo
:pfr
.factory_code"></addProgram>
]]>
</where>
group by pfr
.site_code,pfr
.location_name
</select>
<select id="getForecastPlanDates"
parameterType="map" resultType="java
.lang
.String">
select FN_GET_FORECAST_DATES
(#{planID,jdbcType=VARCHAR},#{operationFlag,jdbcType=VARCHAR},#{bucketType,jdbcType=VARCHAR}
)
</select>
<update id="calcMinFactory
LineQty1">
UPDATE T_PFC_FORECAST_RESGROUP_BUCKET
SET
FACTORY_
LINE_QTY = HUMAN_
LINE_QTY
WHERE HUMAN_
LINE_QTY IS NOT NULL
AND PFR_ID = any
(#{pfrIdArray}
)
</update>
<select id="getPfcResgroupBucketById"
resultType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.bo
.PfcForecastResgroup">
select
pfrb
.pfrb_id as id,
pfrb
.pfr_id as pfrID,
pfr
.plan_id
from T_PFC_FORECAST_RESGROUP pfr
left join T_PFC_FORECAST_RESGROUP_BUCKET pfrb
on pfrb
.pfr_id = pfr
.pfr_id
where pfrb
.pfrb_id = #{pfrbId,jdbcType=VARCHAR}
<if test="flag">
and pfrb
.operation_flag = '1'
</if>
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="FactoryInfo
:pfr
.factory_code"></addProgram>
]]>
</select>
<select id="getFactoryQtyData" resultType="com
.huawei
.it
.tgmes
.tmas
.ebc
.entity
.bo
.TiPfcForecastResgroup">
select t
.plan_id,
t
.prod_
line_cn_name,
t
.prod_area_cn_name,
t
.prod_family_cn_name,
t
.prod_ser
ies_cn_name,
t
.prod_resgroup_desc,
t
.produce_type_cn,
t
.overseas_ckd_flag,
r
.bucket,
r
.bucket_type,
r
.promise_qty,
r
.factory_qty,
r
.promise_
line_qty,
r
.factory_
line_qty,
t
.factory_code,
t
.factory_name,
r
.created_by,
r
.creation_date,
r
.last_updated_by,
r
.last_updated_date
from T_PFC_FORECAST_RESGROUP t
left join T_PFC_FORECAST_RESGROUP_BUCKET r
on t
.pfr_id = r
.pfr_id
where t
.promise_type = '0'
and t
.factory_visible_flag = 'Y'
and r
.operation_flag = '1'
and t
.plan_id = #{maxPlanId}
and t
.factory_code in
<foreach collection="factoryList" item="factoryList" open="
(" separator="," close="
)">
#{factoryList
.valueInfo,jdbcType=VARCHAR}
</foreach>
</select>
<select id="getLastPlanId"
parameterType="string" resultType="string">
select max
(plan_id
)
from T_PFC_FORECAST_RESGROUP
where plan_id <![CDATA[ < ]]> #{planId};
</select>
<select id="getForecastHeadInfo"
parameterType="string" resultType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.PfcForecastResgroupAuto
LineVO">
select h
.pfr_id,
h
.plan_id,
h
.supply_center_name_cn,
h
.plan_operation_enty_name,
h
.mfg_type,
h
.produce_type_code,
h
.site_code,
h
.prod_resgroup
from t_pfc_forecast_resgroup h
where plan_id = #{planId}
and promise_type = '0'
and exists
(
select 1 from t_product_type_rel_v r where r
.prod_
line_code = h
.prod_
line_code
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="ProductTypeInfo
:r
.product_type"></addProgram>
]]>
)
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="FactoryInfo
:h
.factory_code"></addProgram>
]]>
</select>
<select id="getForecastAuto
LineInfo"
parameterType="string" resultType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.PfcForecastResgroupAuto
LineVO">
select h
.plan_id,
h
.supply_center_name_cn,
h
.plan_operation_enty_name,
h
.mfg_type,
h
.produce_type_code,
h
.site_code,
h
.prod_resgroup,
b
.bucket,
b
.high_end_auto_
line,
b
.low_end_auto_
line,
b
.standard_auto_
line
from t_pfc_forecast_resgroup h
inner join t_pfc_forecast_resgroup_bucket b
on b
.pfr_id = h
.pfr_id
where plan_id = #{planId}
and b
.operation_flag = '1'
and exists
(
select 1 from t_product_type_rel_v r where r
.prod_
line_code = h
.prod_
line_code
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="ProductTypeInfo
:r
.product_type"></addProgram>
]]>
)
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="FactoryInfo
:h
.factory_code"></addProgram>
]]>
</select>
<update id="updateForecastAuto
Line"
parameterType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.vo
.PfcForecastResgroupAuto
LineVO">
update t_pfc_forecast_resgroup_bucket
set high_end_auto_
line = #{vo
.highEndAuto
Line},
standard_auto_
line = #{vo
.standardAuto
Line},
low_end_auto_
line = #{vo
.lowEndAuto
Line},
last_updated_by = #{vo
.lastUpdatedBy},
last_updated_date = #{vo
.lastUpdatedDate}
where bucket = #{vo
.bucket}
and pfr_id = #{vo
.pfrId}
</update>
<select id="getProd
LineAreaFamilySer
iesById" resultType="com
.huawei
.it
.tgmes
.tmas
.prodforecast
.entity
.bo
.PfcForecastResgroup">
select prod_
line_code,
prod_area_code,
prod_family_code,
prod_ser
ies_code
from t_pfc_forecast_resgroup h
<where>
<if test="pfrIdArray != null and pfrIdArray
.length > 0">
h
.pfr_id = any
(#{pfrIdArray}
)
</if>
</where>
</select>
<select id="getPfrIdById" resultType="String">
select
pfr_id
from t_pfc_forecast_resgroup_bucket h
<where>
<if test="idArray != null and idArray
.length > 0">
h
.pfrb_id = any
(#{idArray}
)
</if>
</where>
</select>
<select id="calcPermissionPfrIdByPlanId" resultType="string">
select pfr
.pfr_id from T_PFC_FORECAST_RESGROUP pfr where plan_id = #{planID}
and exists
(
select 1 from t_product_type_rel_v r where r
.prod_
line_code = pfr
.prod_
line_code
<![CDATA[
<addProgram prepend ="and" dimensions2F
ields="ProductTypeInfo
:r
.product_type"></addProgram>
]]>
)
</select>
</mapper>
结合这个分析是
不是13周前的日期是天维度，13周之后是周维度， /**
* 功能类型：1 【1-3系统月工厂承诺】； 2 【未来1-3个月终端工厂承诺】
*/
private String functionType;
扫一扫
247
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
