Mapper.java
查询
批量查询
List<PreferProbeItem> queryProbe(String userId);
List<RiskVO> queryRiskVO(String riskId);
List<RiskVO> queryCloseDProject();
List<PreferProbeItem> queryProbeList(@Param("condition") Condition condition, @Param("topic") String topic);
List<PreferProbeItem> queryUserProbes(@Param("userId") String userId, @Param("topicValue") String topicValue,
@Param("probeType") String probeType);
List<ProbeInfo> getProbeInfo(ApiProbeParam apiParam);
List<Trending> queryTrending(@Param("probeId") String probeId, @Param("startTime") Date startTime,
@Param("endTime") Date endTime, @Param("regionOrg") String regionOrg, @Param("repOffice") String repOffice);
List<WarningDetails> queryTaskLink(@Param("list") List<WarningDetailsParam> params);
List<RiskVO> queryRiskVOs(@Param("list") List<String> commonNo);
List<String> queryFilterRiskIds(List<String> commonNoList);
List<WarningDetailsParam> queryPlinkTask(@Param("repofficeCode") String repofficeCode);
其他查询
Integer queryRiskIdS(String commonNo);
int queryCloseTaskCount(@Param("dateMonth") String dateMonth);
String queryThreshold(@Param("threshold") String threshold);
ProbeCriterion queryProbeCriterion(@Param("regionOrg") String regionOrg, @Param("repOffice") String repOffice,
@Param("probeId") String probeId);
String queryTopic(@Param("probeId") String probeId);
增加
批量插入
void insertStateDeploy(List<StateDeploy> stateDeploys);
void insertRiskIds(List<String> RiskIds);
void insertPerformanceDeploy(List<KpiDeploy> kpiDeploys);
其他插入
void insertRiskVO(RiskVO riskVO);
void insert(@Param("warningDetails") WarningDetails warningDetails);
void insertPlinkTask(@Param("warningDetailsParam") WarningDetailsParam warningDetailsParam);
修改
批量更新
void updateStateDeploys(List<StateDeploy> stateDeploys);
其他更新
void updateCloseTime(String commonNo);
void updateRiskVO(RiskVO riskVO);
删除
批量删除
void patchDelete(List<Long> ids);
其他删除
void deletePerformanceDeploy(KpiDeploy KpiDeploy);
Mapper.xml
查询
批量查询
<select id="queryProbe" resultType="com.-.it.regioc.bean.config.prefer.PreferProbeItem">
select rel.probe_id probeId
, all_probe.topic topic
, all_probe.stage stage
, all_probe.name apiName
, rel.rank rank
, all_probe.type probeType
from dm_scm_rel_ioc_user_probe rel
inner join
dm_scm_dim_ioc_all_probe all_probe on rel.probe_id = all_probe.probe_id
where rel.user_id = #{userId}
and rank != o
order by stage, rank asc
</select>
<select id="queryRiskVO" parameterType="String" resultType="com.-.it.regioc.bean.result.RiskVO">
select risk_id riskId,
plink_task_id plinkTaskId,
close_time closeTime
from dm_scm_rel_ioc_risk_task
where close_time >= #{riskId}
</select>
<select id="queryCloseDProject" resultType="com.-.it.regioc.bean.result.RiskVO">
select risk_id as riskId, plink_task_id as plinkTaskId, close_time as closeTime
from dm_scm_rel_ioc_risk_task
where close_time is not null
</select>
<select id="queryProbeList" parameterType="com.-.it.regioc.bean.Condition"
resultType="com.-.it.regioc.bean.config.prefer.PreferProbeItem">
SELECT rel.probe_id probeId,
all_probe.topic topic,
all_probe.stage stage,
all_probe.NAME probeName,
rel.RANK rank
FROM dm_scm_rel_ioc_user_probe rel,
dm_scm_dim_ioc_all_probe all_probe
WHERE all_probe.probe_id = rel.probe_id
and rel.user_id = #{condition.userId}
AND all_probe.topic = #{topic}
UNION ALL
SELECT all_probe.probe_id probeId,
all_probe.topic topic,
all_probe.stage stage,
all_probe.NAME probeName,
0 rank
FROM dm_scm_dim_ioc_all_probe all_probe
WHERE not exists (SELECT probe_id
FROM dm_scm_rel_ioc_user_probe rel
WHERE rel.user_id = #{condition.userId}
AND all_probe.topic = #{topic})
ORDER BY stage, RANK ASC
</select>
<select id="queryUserProbes" resultType="com.-.it.regioc.bean.config.prefer.PreferProbeItem">
SELECT rel.user_id userId,
rel.probe_id probeId,
all_probe.topic topic,
all_probe.stage stage,
all_probe.NAME probeName,
rel.RANK RANK
FROM dm_scm_rel_ioc_user_probe rel
JOIN dm_scm_dim_ioc_all_probe all_probe ON rel.probe_id = all_probe.probe_id
WHERE rel.user_id = #{userId}
AND all_probe.topic = #{topicValue}
AND all_probe.type = #{probeType}
</select>
<select id="getProbeInfo" parameterType="com.-.it.regioc.bean.apiparam.ApiProbeParam"
resultType="com.-.it.regioc.bean.result.ProbeInfo">
select base_val baseVal, target_val targetVal, threshold_val thresholdVal,probe_id probeId
from dm_scm_rel_ioc_country_probe
where 1=1
<if test="regionCnName != null and regionCnName != ''">
and region_org_id = #{regionCnName}
</if>
<if test="repoffceCnName != null and repoffceCnName != ''">
and office_id = #{repoffceCnName}
</if>
<if test="probeId != null and probeId != ''">
and probe_id = #{probeId}
</if>
</select>
<select id="queryTrending" resultType="com.-.it.regioc.bean.model.Trending">
(
select base_val baseVal,update_time updateTime
from dm_scm_rel_ioc_country_probe dsricp
where update_time > #{startTime} and #{endTime} > update_time
and probe_id = #{probeId}
<if test="regionOrg == null or regionOrg ==''">
and region_org_id = 'ALL'
</if>
<if test="regionOrg !=null and regionOrg !=''">
and region_org_id = #{regionOrg}
<if test="repOffice == null or repOffice == ''">
and office_id = 'ALL'
</if>
<if test="repOffice !=null and repOffice != ''">
and office_id = #{repOffice}
</if>
</if>
order by update_time desc
)
union all
(
select base_val baseVal,update_time updateTime
from dm_scm_rel_ioc_country_probe dsricp
where #{startTime} > update_time
and probe_id = #{probeId}
<if test="regionOrg == null or regionOrg ==''">
and region_org_id = 'ALL'
</if>
<if test="regionOrg !=null and regionOrg !=''">
and region_org_id = #{regionOrg}
<if test="repOffice == null or repOffice == ''">
and office_id = 'ALL'
</if>
<if test="repOffice !=null and repOffice != ''">
and office_id = #{repOffice}
</if>
</if>
order by update_time desc limit 1
)
</select>
<select id="queryTaskLink" parameterType="java.util.List"
resultType="com.-.it.regioc.bean.result.WarningDetails">
select probe_id probeId,project_id projectId,plink_task_id pLinkTaskId from dm_scm_rel_ioc_task where 1 = 1
and probe_id in
<foreach collection="list" item="param" open="(" separator="," close=")">
#{param.probeId}
</foreach>
and project_id in
<foreach collection="list" item="param" open="(" separator="," close=")">
#{param.projectId}
</foreach>
</select>
<select id="queryRiskVOs" parameterType="java.util.List" resultType="com.-.it.regioc.bean.result.RiskVO">
select risk_id riskId,
plink_task_id plinkTaskId,
close_time closeTime
from dm_scm_rel_ioc_risk_task
where
risk_id in
<foreach collection="list" item="commonNo" separator="," open="(" close=")">
#{commonNo}
</foreach>
</select>
<select id="queryFilterRiskIds" parameterType="java.util.List" resultType="String">
select risk_id
from dm_scm_rel_ioc_risk_task
where
risk_id in
<foreach collection="list" item="commonNo" separator="," open="(" close=")">
#{commonNo}
</foreach>
AND close_time is not null
and NOW()- close_time <![CDATA[ < ]]> interval '7 day'
</select>
<select id="queryPlinkTask" resultType="com.-.it.regioc.bean.model.WarningDetailsParam">
select a.repoffice_code as repofficeCode,
a.pLink_project_id as pLinkProjectId,
a.repoffice_name as repofficeName,
a.plink_group_id as pLinkGroupId,
a.topic
from dm_scm_rel_ioc_plink_task a
where a.repoffice_code = #{repofficeCode}
</select>
其他查询
<select id="queryRiskIdS" parameterType="String" resultType="Integer">
select count(1)
from dm_scm_rel_ioc_risk_task
where risk_id = #{commonNo}
</select>
<select id="queryCloseTaskCount" resultType="java.lang.Integer">
select count(1) as total
from dm_scm_rel_ioc_risk_task
where close_time like concat('', #{dateMonth}, '%')
</select>
<select id="queryThreshold" resultType="string">
select param_value
from dm_scm_common_config
where param_type = #{threshold}
</select>
<select id="queryProbeCriterion" resultType="com.-.it.regioc.bean.config.porbecriterion.ProbeCriterion">
select probe_id probeId,
threshold_val thresholdVal,
base_val baseVal,
target_val targetVal
from dm_scm_rel_ioc_country_probe rel
where probe_id = #{probeId}
<if test="regionOrg != null and regionOrg != ''">
and region_org_id = #{regionOrg}
</if>
<if test="regionOrg == null and regionOrg == ''">
and region_org_id = 'total'
</if>
<if test="repOffice != null and repOffice != ''">
and office_id = #{repOffice}
</if>
<if test="repOffice == null or repOffice == ''">
and office_id = 'total'
</if>
order by update_time desc limit 1
</select>
<select id="queryTopic" resultType="string">
select topic
from dm_scm_dim_ioc_all_probe a
where a.probe_id = #{probeId}
</select>
增加
批量插入
<insert id="insertStateDeploy">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
insert into DM_SCM_REL_IOC_USER_PROBE(USER_ID ,PROBE_ID ,RANK)
values(#{item.userId}, #{item.probeId},#{item.rank})
</foreach>
</insert>
<insert id="insertRiskIds">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
insert into dm_scm_rel_ioc_risk_task(risk_id)
values(#{item})
</foreach>
</insert>
<insert id="insertPerformanceDeploy">
insert into DM_SCM_REL_IOC_USER_KPI(USER_ID ,KPI_ID ,RANK) values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.userId}, #{item.kpiId},(#{index}+1))
</foreach>
</insert>
其他插入
<insert id="insertRiskVO">
insert into dm_scm_rel_ioc_risk_task(risk_id, plink_task_id,close_time)
values (#{riskId}, #{plinkTaskId},now())
</insert>
<insert id="insert" parameterType="com.-.it.regioc.bean.result.WarningDetails">
insert into dm_scm_rel_ioc_task
(probe_id,
project_id,
plink_task_id)
values (#{warningDetails.probeId,jdbcType=VARCHAR},
#{warningDetails.projectId,jdbcType=VARCHAR},
#{warningDetails.pLinkTaskId,jdbcType=VARCHAR})
</insert>
<insert id="insertPlinkTask" parameterType="com.-.it.regioc.bean.model.WarningDetailsParam">
insert into dm_scm_rel_ioc_plink_task
(repoffice_code,
repoffice_name,
plink_project_id,
plink_group_id,
topic,
update_time)
values (#{warningDetailsParam.repofficeCode,jdbcType=VARCHAR},
#{warningDetailsParam.repofficeName,jdbcType=VARCHAR},
#{warningDetailsParam.plinkProjectId,jdbcType=VARCHAR},
#{warningDetailsParam.plinkGroupId,jdbcType=VARCHAR},
#{warningDetailsParam.topic,jdbcType=VARCHAR},
#{warningDetailsParam.updateTime,jdbcType=TIMESTAMP})
</insert>
修改
批量更新
<update id="updateStateDeploys" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update dm_scm_rel_ioc_user_probe
set rank = #{item.rank}
where
user_id = #{item.userId}
and
probe_id = #{item.probeId}
</foreach>
</update>
其他更新
<update id="updateCloseTime" parameterType="string">
UPDATE dm_scm_rel_ioc_risk_task
SET close_time = NOW()
WHERE risk_id = #{commonNo}
</update>
<update id="updateRiskVO" parameterType="com.-.it.regioc.bean.result.RiskVO">
UPDATE dm_scm_rel_ioc_risk_task
SET plink_task_id = #{plinkTaskId}
WHERE risk_id = #{riskId}
</update>
删除
批量删除
<!--void patchDelete(List<Long> ids);
delete from t_product where id in (1,2,3)
-->
<delete id="patchDelete" >
delete from t_product where id in
<foreach collection="list" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</delete>
其他删除
<delete id="deletePerformanceDeploy" parameterType="com.-.it.regioc.bean.vueparam.KpiDeploy">
delete
from DM_SCM_REL_IOC_USER_KPI
where USER_ID = #{userId}
</delete>
