<?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.zjrs.ggfw.portal.entity.VRsWbBjListMapper">

    <sql id="wlList_where">
        <if test="dto.slbh != null and dto.slbh != ''">
            and a.slbh = #{dto.slbh}
        </if>
        <if test="dto.sxmc != null and dto.sxmc != ''">
            and a.sxmc = #{dto.sxmc}
        </if>
        <if test="dto.bjrqc != null and dto.bjrqc != ''">
            and a.bjsj &gt; #{dto.bjrqc} ||' 00:00:00'
        </if>
        <if test="dto.bjrqz != null and dto.bjrqz != ''">
            and a.bjsj &lt; #{dto.bjrqz} ||' 23:59:59'
        </if>
        <if test="dto.sqrqq != null and dto.sqrqq != ''">
            and a.sqsj &gt; #{dto.sqrqq} ||' 00:00:00'
        </if>
    </sql>

    <select id="getWlList"  resultType="com.zjrs.ggfw.portal.dto.WlListResultDTO">
        select * from (select c.SLLSH,c.SLBH,(select sxmc from V_RS_SX_CONFIG bb where a.sbsx = bb.sxbm) SXMC,
        a.SQSJ,c.JBSJ BJSJ,a.SJLY,b.orderstatus
        from rs_wb_bj a,RS_SLXX_EMS_ORDER b,rs_slxx c
        where a.sllsh = b.sllsh and b.sllsh = c.sllsh and c.CUSTORDERID is null
        and a.yhid = #{dto.yhid}  and b.orderstatus = #{dto.zfzt}
        union all
        select * from (SELECT SLLSH,SLBH,YJSSXMC||'('||(select listagg(b.SXMC, '、') WITHIN GROUP(ORDER BY JBSJ)||')'
        from V_RS_SX_CONFIG b
        where instr(SBSX, b.SXBM) > 0) SXMC,SQSJ,JBSJ BJSJ,SJLY,orderstatus
        FROM (select a.CUSTORDERID,b.RS_WB_BJ_LSH,
        '4408' YJSSXMC,
        '01' SJLY,
        (SELECT orderstatus FROM RS_SLXX_EMS_ORDER aa WHERE aa.CUSTORDERID = a.CUSTORDERID) orderstatus,
        (LISTAGG(b.SBSX,'、')  within group (order by a.JBSJ)) SBSX,
        SUBSTR((LISTAGG(a.JBSJ,'、')  within group (order by a.JBSJ)),0,19) JBSJ,
        SUBSTR((LISTAGG(b.SQSJ,'、')  within group (order by a.JBSJ)),0,19) SQSJ,
        null SLBH,
        null SLLSH
        from RS_SLXX a,RS_WB_BJ_SUB b  WHERE a.SLLSH = b.SLLSH
        and b.yhid = #{dto.yhid}
        AND a.CUSTORDERID IS NOT NULL GROUP BY a.CUSTORDERID,b.RS_WB_BJ_LSH,b.SQSJ)) q
        where q.orderstatus = #{dto.zfzt}
        ) a where 1=1
        <include refid="wlList_where" />
        order by a.SQSJ desc
    </select>
    <select id="getWlListGr"  resultType="com.zjrs.ggfw.portal.dto.WlListResultDTO">
        select * from (select c.SLLSH,c.SLBH,(select sxmc from V_RS_SX_CONFIG bb where a.sbsx = bb.sxbm) SXMC,
        a.SQSJ,c.JBSJ BJSJ,a.SJLY,b.orderstatus
        from rs_wb_bj a,RS_SLXX_EMS_ORDER b,rs_slxx c
        where a.sllsh = b.sllsh and b.sllsh = c.sllsh and c.CUSTORDERID is null
        and a.yhid = #{dto.yhid}  and b.orderstatus = #{dto.zfzt}
        union all
        select * from (SELECT SLLSH,SLBH,YJSSXMC||'('||(select listagg(b.SXMC, '、') WITHIN GROUP(ORDER BY JBSJ)||')'
        from V_RS_SX_CONFIG b
        where instr(SBSX, b.SXBM) > 0) SXMC,SQSJ,JBSJ BJSJ,SJLY,orderstatus
        FROM (select a.CUSTORDERID,b.RS_WB_BJ_LSH,
        (SELECT SXMC FROM  RS_WB_BJ cc,V_RS_SX_CONFIG dd WHERE cc.RS_WB_BJ_LSH = b.RS_WB_BJ_LSH AND dd.SXBM = cc.SBSX) YJSSXMC,
        (SELECT SJLY FROM V_RS_WB_BJ aa WHERE aa.RS_WB_BJ_LSH = b.RS_WB_BJ_LSH) SJLY,
        (SELECT orderstatus FROM RS_SLXX_EMS_ORDER aa WHERE aa.CUSTORDERID = a.CUSTORDERID) orderstatus,
        (LISTAGG(b.SBSX,'、')  within group (order by a.JBSJ)) SBSX,
        SUBSTR((LISTAGG(a.JBSJ,'、')  within group (order by a.JBSJ)),0,19) JBSJ,
        SUBSTR((LISTAGG(b.SQSJ,'、')  within group (order by a.JBSJ)),0,19) SQSJ,
        null SLBH,
        null SLLSH
        from RS_SLXX a,RS_WB_BJ_SUB b  WHERE a.SLLSH = b.SLLSH
        and b.yhid = #{dto.yhid}
        AND a.CUSTORDERID IS NOT NULL GROUP BY a.CUSTORDERID,b.RS_WB_BJ_LSH,b.SQSJ)) q
        where q.orderstatus = #{dto.zfzt}
        ) a where 1=1
        <include refid="wlList_where" />
        order by a.SQSJ desc
    </select>

    <select id="getbjAddress" resultType="com.zjrs.ggfw.portal.dto.BjDTO">
        select a.tcustProvince,a.tcustCity,a.tcustCounty
        from V_RS_WB_bj a,V_RS_WB_BJ_LIST b
        where a.sllsh = b.sllsh and a.sllsh = #{sllsh}
    </select>

    <select id="getBjListDw" parameterType="com.zjrs.ggfw.portal.dto.SxblQueryDTO" resultType="com.zjrs.ggfw.portal.po.VRsWbBjList">
        SELECT A.*,B.LQR as "lqr",B.LQRQ as "lqrq",B.BZ as "bz",
        C.ORDERSTATUS as "orderstatus",C.EXPRESSNBR as "expressnbr"
        FROM V_RS_WB_BJ_LIST A LEFT JOIN
        RS_SLXX_JGLQ B ON A.SLLSH = B.SLLSH
        LEFT JOIN RS_SLXX_EMS_ORDER C ON A.SLLSH = C.SLLSH
        WHERE A.YHID = #{dto.yhid}
        <if test="dto.slbh != null and dto.slbh != ''">
            and A.slbh LIKE '%'||#{dto.slbh}||'%'
        </if>
        <if test="dto.sxmc != null and dto.sxmc != ''">
            and A.sxmc LIKE '%'||#{dto.sxmc}||'%'
        </if>
        <if test="dto.sqrqq != null and dto.sqrqq != ''">
            and A.sqsj &gt;= #{dto.sqrqq} ||' 00:00:00'
        </if>
        <if test="dto.sqrqz != null and dto.sqrqz != ''">
            and A.sqsj &lt;= #{dto.sqrqz} ||' 23:59:59'
        </if>
        order by A.zhxgsj DESC
    </select>
</mapper>
