<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.pingan.ela.behavior.mapper.ElaTraceMapper"> <sql id="select_column"> e.id_ela id, e.order_no orderNo, e.agent_no agentNo, e.product_name productName, e.product_code productCode, c.ins_name insName, substring(c.ins_name FROM char_length(c.ins_name) FOR char_length(c.ins_name)) insLastChar, c.ins_sex insSex, c.ins_age insAge, c.ins_birthday insBirthday, c.ins_mobile insMobile, e.document_status documentStatus, e.response_time responseTime, e.source source, c.openid openid, e.money money, e.created_by createdBy, e.created_date createdDate, e.updated_by updatedBy, e.updated_date updatedDate, count(1) over() total </sql> <select id="queryElaTraceInfoList" resultType="com.pingan.ela.behavior.dto.ElaTraceInfo"> with r as (select e.agent_no, e.product_name, e.id_customer, e.response_time, max(e.updated_date) upd from ela_trace_info e <if test='insMobile != null or insName != null or (groupNo != null and groupNo != "A")'> ,ela_trace_customer_info c </if> where e.agent_no = #{agentNo} and e.id_customer is not null <if test='insMobile != null or insName != null or (groupNo != null and groupNo != "A")'> and e.id_customer = c.id_customer </if> <if test="documentStatus != null"> and e.document_status = #{documentStatus} </if> <if test="insName != null"> and c.ins_name <choose> <when test="insName == ''"> is null </when> <otherwise> = #{insName} </otherwise> </choose> </if> <if test="insMobile != null"> and c.ins_mobile = #{insMobile} </if> <choose> <when test='groupNo == "X"'> and not exists (select null from customer_groups cg where cg.openid = c.openid) </when> <when test='groupNo != null and groupNo != "A"'> and exists (select null from customer_groups cg where cg.openid = c.openid and cg.group_infotype_no =#{groupNo}) </when> </choose> group by e.agent_no, e.product_name, e.id_customer, e.response_time order by <choose> <when test="orderBy == 1"> (select c.ins_name from ela_trace_customer_info c where c.id_customer = e.id_customer) collate "zh_CN" nulls first </when> <when test="orderBy == 2"> e.response_time desc nulls first, upd desc </when> <otherwise> upd desc </otherwise> </choose> limit #{pageSize} offset #{pageSize} * (#{currentPage} - 1)) select <include refid="select_column"/> from ela_trace_info e,r, ela_trace_customer_info c where c.id_customer = e.id_customer and e.agent_no = r.agent_no and e.id_customer = r.id_customer and e.product_name = r.product_name and e.updated_date = r.upd order by <choose> <when test="orderBy == 1"> c.ins_name collate "zh_CN" nulls first </when> <when test="orderBy == 2"> e.response_time desc nulls first, r.upd desc </when> <otherwise> r.upd desc </otherwise> </choose>; </select> <select id="queryElaTraceInfoDetail" resultType="com.pingan.ela.behavior.dto.ElaTraceInfo"> select <include refid="select_column"/> from ela_trace_info e, ela_trace_customer_info c where e.id_customer = c.id_customer and c.ins_mobile = #{insMobile} and e.agent_no = #{agentNo} <if test="openid != null"> and c.openid = #{openid} </if> <if test="isLastWeek == 1"> and e.updated_date > now() - interval '7 day' </if> order by e.updated_date desc; </select> <select id="queryNotRespondingCount" resultType="long"> SELECT count(1) FROM ela_trace_info WHERE agent_no = #{agentNo} AND document_status = '6' AND response_time IS NULL; </select> <update id="upsertElaTraceCustomerInfo" parameterType="com.pingan.ela.behavior.dto.ElaTraceInfo" useGeneratedKeys="true" keyProperty="customerId"> INSERT INTO ela_trace_customer_info ( ins_name, ins_sex, ins_age, ins_birthday, ins_mobile, openid ) VALUES ( <choose> <when test="insName != null"> #{insName}, </when> <otherwise> '', </otherwise> </choose> <choose> <when test="insSex != null"> #{insSex}, </when> <otherwise> '', </otherwise> </choose> #{insAge}, #{insBirthday}, <choose> <when test="insMobile != null"> #{insMobile}, </when> <otherwise> '', </otherwise> </choose> <choose> <when test="openid != null"> #{openid} </when> <otherwise> '' </otherwise> </choose>) on conflict(openid, ins_mobile, ins_name, ins_sex) do update set <if test="insAge != null"> ins_age = #{insAge}, </if> <if test="insBirthday != null"> ins_birthday = #{insBirthday}, </if> updated_by = user, updated_date = now(); </update> <insert id="addElaTraceInfo" parameterType="com.pingan.ela.behavior.dto.ElaTraceInfo"> insert into ela_trace_info ( order_no, agent_no, product_name, product_code, document_status, response_time, source, money, id_customer, policyNo, insidtype, insidnumber, beinsidtype, beinsidnumber ) values ( #{orderNo}, #{agentNo}, #{productName}, #{productCode}, #{documentStatus}, #{responseTime}, #{source}, #{money}, #{customerId}, #{policyNo}, #{insidtype}, #{insidnumber}, #{beinsidtype}, #{beinsidnumber} ); </insert> <update id="updateResponseTime" parameterType="com.pingan.ela.behavior.dto.ElaTraceInfo"> UPDATE ela_trace_info SET response_time = #{responseTime}, updated_by = user, updated_date = now() WHERE id_ela = #{id}; </update> <select id="queryReadAndShareCount" resultType="com.pingan.ela.behavior.dto.ElaTraceInfo"> with m as(select sit.item_no,sit.item_name from sales_item_table sit where sit.parent_item_no='0501' order by sit.item_no desc), n as(select cti.openid,cti.info_type_no,cti.share_type,cti.created_date from customer_trace_info cti where cti.openid=#{openid}) select item_no "groupNo",item_name "groupName",coalesce(read_total,0) "readTotal",coalesce(share_total,0) "shareTotal" from( select m.item_no, m.item_name, (select count(1) from n where n.info_type_no = m.item_no <if test="isLastWeek == 0"> and n.created_date > CURRENT_DATE - INTERVAL '2 month' </if> group by n.openid, n.info_type_no ) "read_total", (select count(1) from n where n.info_type_no = m.item_no <![CDATA[ and coalesce(n.share_type, '') <> '' ]]> <if test="isLastWeek == 0"> and n.created_date > CURRENT_DATE - INTERVAL '2 month' </if> group by n.openid, n.info_type_no ) "share_total" from m ) t; </select> <select id="queryCustomerGroups" resultType="com.pingan.ela.behavior.dto.ElaTraceInfo"> SELECT t2.group_name groupName, t1.created_date createdDate, t1.group_infotype_no groupNo FROM customer_groups t1, customer_groups_dict t2 WHERE t1.openid = #{openid} AND t1.group_infotype_no = t2.info_type_no ORDER BY t1.created_date DESC; </select> <sql id="old_select_column"> e.id_ela id, e.order_no orderNo, e.agent_no agentNo, e.product_name productName, e.product_code productCode, e.ins_name insName, substring(e.ins_name FROM char_length(e.ins_name) FOR char_length(e.ins_name)) insLastChar, e.ins_sex insSex, e.ins_age insAge, e.ins_mobile insMobile, e.document_status documentStatus, e.response_time responseTime, e.source source, e.openid openid, e.money money, e.created_by createdBy, e.created_date createdDate, e.updated_by updatedBy, e.updated_date updatedDate, count(1) over() total </sql> <select id="queryOldElaTraceInfoList" resultType="com.pingan.ela.behavior.dto.ElaTraceInfo"> with r as (select agent_no, product_name, ins_name, ins_mobile, response_time, openid, max(updated_date) upd from ela_trace_info eti where agent_no = #{agentNo} <if test="documentStatus != null"> and document_status = #{documentStatus} </if> <if test="insName != null"> and ins_name <choose> <when test="insName == ''"> is null </when> <otherwise> = #{insName} </otherwise> </choose> </if> and ins_mobile != '暂无' <if test="insMobile != null"> and ins_mobile = #{insMobile} </if> <choose> <when test='groupNo == "X"'> and not exists (select null from customer_groups cg where cg.openid = eti.openid) </when> <when test='groupNo != null and groupNo != "A"'> and exists (select null from customer_groups cg where cg.openid = eti.openid and cg.group_infotype_no =#{groupNo}) </when> </choose> group by agent_no, product_name, ins_name, ins_mobile, response_time, openid order by <choose> <when test="orderBy == 1"> ins_name collate "zh_CN" nulls first </when> <when test="orderBy == 2"> response_time desc nulls first, upd desc </when> <otherwise> upd desc </otherwise> </choose> limit #{pageSize} offset #{pageSize} * (#{currentPage} - 1)) select <include refid="old_select_column"/> from ela_trace_info e, r where e.agent_no = r.agent_no and e.product_name = r.product_name and (e.ins_mobile = r.ins_mobile or e.openid = r.openid) and e.updated_date = r.upd order by <choose> <when test="orderBy == 1"> e.ins_name collate "zh_CN" nulls first </when> <when test="orderBy == 2"> e.response_time desc nulls first, r.upd desc </when> <otherwise> r.upd desc </otherwise> </choose>; </select> <select id="queryOldElaTraceInfoDetail" resultType="com.pingan.ela.behavior.dto.ElaTraceInfo"> select <include refid="old_select_column"/> from ela_trace_info e where agent_no = #{agentNo} and ins_mobile = #{insMobile} <if test="openid != null"> and openid = #{openid} </if> <if test="isLastWeek == 1"> and updated_date > now() - interval '7 day' </if> order by updated_date desc; </select> <insert id="addOldElaTraceInfo" parameterType="com.pingan.ela.behavior.dto.ElaTraceInfo"> insert into ela_trace_info ( order_no, agent_no, product_name, product_code, <if test="insName != ''"> ins_name, </if> ins_sex, ins_age, ins_mobile, document_status, response_time, source <if test="openid != ''"> ,openid </if> ,money ) values ( #{orderNo}, #{agentNo}, #{productName}, #{productCode}, <if test="insName != ''"> #{insName}, </if> #{insSex}, #{insAge}, #{insMobile}, #{documentStatus}, #{responseTime}, #{source} <if test="openid != ''"> ,#{openid} </if> ,#{money} ); </insert> </mapper>
sql的一种配置新方法
最新推荐文章于 2021-11-16 11:13:01 发布