<?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.muchinfo.mtp3interface.mapper.IWroutinapplyMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.Wroutinapply">
        <id column="APPLYID" property="applyid" />
        <result column="APPLYTYPE" property="applytype" />
        <result column="WRSTANDARDID" property="wrstandardid" />
        <result column="WRSTANDARDCODE" property="wrstandardcode" />
        <result column="DELIVERYGOODSID" property="deliverygoodsid" />
        <result column="WAREHOUSEID" property="warehouseid" />
        <result column="USERID" property="userid" />
        <result column="ACCOUNTID" property="accountid" />
        <result column="MOBILE" property="mobile" />
        <result column="APPOINTMENTDATE" property="appointmentdate" />
        <result column="APPOINTMENTMODEL" property="appointmentmodel" />
        <result column="COUNTRYID" property="countryid" />
        <result column="PROVINCEID" property="provinceid" />
        <result column="DISTRICTID" property="districtid" />
        <result column="CITYID" property="cityid" />
        <result column="ADDRESS" property="address" />
        <result column="CONTACTNAME" property="contactname" />
        <result column="CONTACTNUM" property="contactnum" />
        <result column="APPOINTMENTREMARK" property="appointmentremark" />
        <result column="APPLYSTATUS" property="applystatus" />
        <result column="APPLYTIME" property="applytime" />
        <result column="CREATORID" property="creatorid" />
        <result column="CREATETIME" property="createtime" />
        <result column="FIRSTAUDITID" property="firstauditid" />
        <result column="FIRSTAUDITTIME" property="firstaudittime" />
        <result column="FIRSTREMARK" property="firstremark" />
        <result column="SECONDAUDITID" property="secondauditid" />
        <result column="SECONDAUDITTIME" property="secondaudittime" />
        <result column="SECONDREMARK" property="secondremark" />
        <result column="APPLYSTATUS2" property="applystatus2" />
        <result column="FIRSTAUDITID2" property="firstauditid2" />
        <result column="FIRSTAUDITTIME2" property="firstaudittime2" />
        <result column="FIRSTREMARK2" property="firstremark2" />
        <result column="SECONDAUDITID2" property="secondauditid2" />
        <result column="SECONDAUDITTIME2" property="secondaudittime2" />
        <result column="SECONDREMARK2" property="secondremark2" />
        <result column="TRADEDATE" property="tradedate" />
        <result column="INQTY" property="inqty" />
        <result column="INREALQTY" property="inrealqty" />
        <result column="INREALQTY2" property="inrealqty2" />
        <result column="CLIENTTICKET" property="clientticket" />
        <result column="WRFACTORTYPEID" property="wrfactortypeid" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        APPLYID, APPLYTYPE, WRSTANDARDID, WRSTANDARDCODE, DELIVERYGOODSID, WAREHOUSEID, USERID, ACCOUNTID, MOBILE, APPOINTMENTDATE, APPOINTMENTMODEL, COUNTRYID, PROVINCEID, DISTRICTID, CITYID, ADDRESS, CONTACTNAME, CONTACTNUM, APPOINTMENTREMARK, APPLYSTATUS, APPLYTIME, CREATORID, CREATETIME, FIRSTAUDITID, FIRSTAUDITTIME, FIRSTREMARK, SECONDAUDITID, SECONDAUDITTIME, SECONDREMARK, APPLYSTATUS2, FIRSTAUDITID2, FIRSTAUDITTIME2, FIRSTREMARK2, SECONDAUDITID2, SECONDAUDITTIME2, SECONDREMARK2, TRADEDATE, INQTY, INREALQTY, INREALQTY2, CLIENTTICKET, WRFACTORTYPEID
    </sql>

    <select id="queryWROutInApplyPage" resultType="com.muchinfo.mtp3pojos.vo.warehouse.WarehouseQueryResult">
        select w.*,
        u.customername username,
        decode(w.applytype, 1, di1.dgfactoryitemvalue, 3, di3.dgfactoryitemvalue, h.warehousename) warehousename,
        s.wrstandardname wrstandardname,
        e.enumdicname,
        wl.ladingbillid,
        a.typevalues,
        b.typevalues2
        from WROUTINAPPLY w
        left join WROutInFactoryDetail wofd on w.applyid = wofd.applyid and wofd.typeid = 1
        left join DGFactoryItem di1 on wofd.typevalue = di1.dgfactoryitemid and wofd.typeid = di1.dgfactoryitemtypeid and w.applytype = 1
        left join DGFactoryItem di3 on wofd.typevalue2 = di3.dgfactoryitemid and wofd.typeid = di3.dgfactoryitemtypeid and w.applytype = 3
        left join userinfo u on w.userid = u.userid
        left join WRStandard s on w.wrstandardid = s.wrstandardid
        left join WarehouseInfo h on h.autoid = w.warehouseid
        left join enumdicitem e on s.unitid = e.enumitemname and e.enumdiccode = 'goodsunit'
        left join wrladingbill wl on w.applyid = wl.applyid
        left join
        (select t.applyid, listagg(t.dgfactoryitemvalue ,', ') WITHIN GROUP(ORDER BY t.typevalue) typevalues
        from(
        select distinct wd.*,
        dt.itemtypename,
        di.dgfactoryitemvalue
        from WROutInFactoryDetail wd
        left join DGFactoryItemType dt on wd.typeid = dt.dgfactoryitemtypeid
        left join DGFactoryItem di on dt.dgfactoryitemtypeid = di.dgfactoryitemtypeid and wd.typevalue = di.dgfactoryitemid
        where wd.typeid != 1
        and wd.ItemTypeMode = 1) t
        group by t.applyid) a on a.applyid = w.applyid

        left join
        (select t1.applyid, listagg(t1.dgfactoryitemvalue ,', ') WITHIN GROUP(ORDER BY t1.typevalue2) typevalues2
        from(
        select distinct wd.*,
        dt.itemtypename,
        di.dgfactoryitemvalue
        from WROutInFactoryDetail wd
        left join DGFactoryItemType dt on wd.typeid = dt.dgfactoryitemtypeid
        left join DGFactoryItem di on dt.dgfactoryitemtypeid = di.dgfactoryitemtypeid and wd.typevalue2 = di.dgfactoryitemid
        where wd.typeid != 1
        and wd.ItemTypeMode = 1) t1
        group by t1.applyid) b on b.applyid = w.applyid
        <where>
            <if test="vo.mobile != null and vo.mobile != ''">
                and (w.mobile like '%'||#{vo.mobile,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.wrstandardcode != null and vo.wrstandardcode != ''">
                and ((s.wrstandardname like '%'||#{vo.wrstandardcode,jdbcType=VARCHAR}||'%')
                or(w.wrstandardcode like '%'||#{vo.wrstandardcode,jdbcType=VARCHAR}||'%'))
            </if>
            <if test="vo.appointmentdatestr != null and vo.appointmentdatestr != ''">
                and w.appointmentdate = to_date(#{vo.appointmentdatestr},'yyyyMMdd')
            </if>
            <if test="vo.applystatus != null">
                and w.applystatus2 = #{vo.applystatus}
            </if>
            <if test="vo.applytype != null and vo.applytype != ''">
                and w.applytype = #{vo.applytype}
            </if>
            <if test="vo.applytimestr != null and vo.applytimestr != ''">
                and w.applytime &gt;= to_date(#{vo.applytimestr}||',00:00:00','yyyyMMdd,hh24:mi:ss')
                and w.applytime &lt;= to_date(#{vo.applytimestr}||',23:59:59','yyyyMMdd,hh24:mi:ss')
            </if>
            <if test="vo.username != null and vo.username != ''">
                and ((w.userid like '%'||#{vo.username,jdbcType=VARCHAR}||'%')
                or(u.customername like '%'||#{vo.username,jdbcType=VARCHAR}||'%'))
            </if>
            <if test="vo.applyid != null and vo.applyid != ''">
                and (w.applyid like '%'||#{vo.applyid,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.userid != null">
                and w.userid = #{vo.userid}
            </if>
        </where>
        order by w.createtime desc
    </select>

    <select id="loadWRCategoryGoodsTree" resultType="com.muchinfo.mtp3pojos.entity.Wrcategory">
        select * from WRCategory t
        where t.categoryid != 0
        <if test="categoryid != null and categoryid != 0">
            start with t.categoryid =#{categoryid}
            connect by prior t.categoryid =  t.parentcategoryid
        </if>
        order by t.orderindex
    </select>

    <select id="queryUserList" resultType="com.muchinfo.mtp3pojos.vo.common.OrganSelectResult">
        select distinct u.userid,u.accountname
        from Wrposition w,useraccount u
        where w.wruserid = u.userid
    </select>

    <select id="querywslist" resultType="com.muchinfo.mtp3pojos.vo.warehouse.WslistQueryResult">
        select distinct wt.wrstandardid,ws.wrstandardcode,ws.wrstandardname
        from WRFactorType wt
                 left join WRStandard ws on wt.wrstandardid = ws.wrstandardid
                 left join WRHoldLB wl on wt.wrfactortypeid = wl.wrfactortypeid
        where wl.userid = #{userid}
    </select>

    <select id="querywhlist" resultType="com.muchinfo.mtp3pojos.vo.warehouse.WhlistQueryResult">
        select distinct wt.warehouseid autoid, wh.warehousename
        from WRFactorType wt
                 left join WRStandard ws on wt.wrstandardid = ws.wrstandardid
                 left join WarehouseInfo wh on wt.warehouseid = wh.autoid
                 left join WRHoldLB wl on wt.wrfactortypeid = wl.wrfactortypeid
        where wl.userid = #{userid}
          and ws.wrstandardid = #{wrstandardid}
    </select>

    <select id="queryWRHoldLB" resultType="com.muchinfo.mtp3pojos.vo.warehouse.WrHoldLBResult">
        with tmp as(
        select t.deliverygoodsid,
        listagg(t.itemtypename, ',') within group(order by t.orderindex) as itemtypename
        from dgfactoryitemtype t where t.itemtypemode!=3
        group by t.deliverygoodsid
        )
        select wf.wrstandardid,wh.autoid, w.*,
        e.enumdicname unitname,
        dg.deliverygoodsname,
        wf.wrfactortypename,
        wf.optioncompare,
        wh.warehousename warehousename,
        ws.wrstandardname wrstandardname,
        tmp.itemtypename
        from wrholdlb w
        left join wrfactortype wf on wf.wrfactortypeid = w.wrfactortypeid
        left join wrstandard ws on ws.wrstandardid = wf.wrstandardid
        left join WarehouseInfo wh on wh.autoid = wf.warehouseid
        left join DeliveryGoods dg on ws.deliverygoodsid = dg.deliverygoodsid
        left join enumdicitem e on ws.unitid = e.enumitemname and e.enumdiccode = 'goodsunit'
        left join tmp on ws.deliverygoodsid = tmp.deliverygoodsid
        <where>
            <if test="vo.warehouseid != null and vo.warehouseid != ''">
                and wf.warehouseid = #{vo.warehouseid}
            </if>
            <if test="vo.wrstandardid != null and vo.wrstandardid != ''">
                and wf.wrstandardid = #{vo.wrstandardid}
            </if>
            <if test="vo.userid != null and vo.userid != ''">
                and w.userid = #{vo.userid}
            </if>
            <if test="vo.accountid != null and vo.accountid != ''">
                and w.accountid = #{vo.accountid}
            </if>
        </where>
        order by ws.wrstandardid desc, wf.wrfactortypeid desc
    </select>

    <select id="receiptquery" resultType="com.muchinfo.mtp3pojos.vo.warehouse.WarehouseReceiptResult">
        select w.*, o.accountname as areaname
        from WareHouseinfo w
        left join UserAccount o on w.areauserid = o.UserID
        <where>
            <if test="vo.areaname != null and vo.areaname != ''">
                and o.accountname like '%'||#{vo.areaname}||'%'
            </if>
            <if test="vo.warehousecode != null and vo.warehousecode != ''">
                and w.warehousecode like '%'||#{vo.warehousecode}||'%'
            </if>
            <if test="vo.areauserid != null and vo.areauserid != ''">
                and w.areauserid = #{vo.areauserid}
            </if>
            <if test="vo.starttime != null and vo.starttime !='' ">
                <![CDATA[and w.createtime >= to_date(#{vo.starttime}||' 00:00:00', 'yyyy-mm-dd hh24:mi:ss')]]>
            </if>
            <if test="vo.endtime != null and vo.endtime !='' ">
                <![CDATA[and w.createtime <= to_date(#{vo.endtime}||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss')]]>
            </if>
        </where>
        order by decode(w.warehousestatus, '3',1,'1',2,'4',3,'2',4),w.autoid desc
    </select>

    <select id="queryWRStandardSet" resultType="com.muchinfo.mtp3pojos.vo.warehouse.WarehouseVarietyResult">
        select ws.*,
        ws.minivalue/power(10,ws.minivaluedp) minivalue2,
        wc.categoryname,
        pwc.categoryname parencategoryname,
        decode(null, pwc.categoryname  ,'', pwc.categoryname|| ' > ')   ||    decode(null,  wc.categoryname  ,'',  wc.categoryname|| ' > ')  ||
        dg.deliverygoodsname  as fullname
        from WRStandard ws
        left join DeliveryGoods dg on ws.deliverygoodsid = dg.deliverygoodsid
        left join WRCategory wc on dg.categoryid = wc.categoryid
        left join WRCategory pwc on wc.parentcategoryid = pwc.categoryid
        <where>
            <if test="vo.deliverygoodsid != null">
                and ws.deliverygoodsid = #{deliverygoodsid}
            </if>
            <if test="vo.wrstandardid != null">
                and ws.wrstandardid = #{vo.wrstandardid}
            </if>
            <if test="vo.wrstandardname != null and vo.wrstandardname !=''">
                and (ws.wrstandardcode like '%'||#{vo.wrstandardname,jdbcType=VARCHAR}||'%'
                or ws.wrstandardname like '%'||#{vo.wrstandardname,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.isvalid != null">
                and ws.isvalid = #{vo.isvalid}
            </if>
        </where>
        order by ws.updatetime desc, ws.wrstandardcode
    </select>

    <select id="queryDGFactoryItemList" resultType="com.muchinfo.mtp3pojos.entity.Dgfactoryitem">
        select t.*
        <if test="wrstandardid!=null and wrstandardid!=''">
            ,w.wrstandardid
        </if>
        from DGFactoryItem t
        <if test="wrstandardid!=null and wrstandardid!=''">
            left join WRStandardFactoryItem w on t.dgfactoryitemid = w.dgfactoryitemid
            and t.dgfactoryitemtypeid = w.dgfactoryitemtypeid and w.wrstandardid=#{wrstandardid }
        </if>
        where 1=1
        <if test="deliverygoodsid!=null and deliverygoodsid!=''">
            and t.dgfactoryitemtypeid in(
            select t.dgfactoryitemtypeid from DGFACTORYITEMTYPE t where t.deliverygoodsid = #{deliverygoodsid}
            and t.itemtypemode = 3 and t.isvalid=1)
        </if>
        and t.deliverygoodsid=0
        and t.isvalid = 1 order by t.orderindex
    </select>
    <select id="queryexpress" resultType="com.muchinfo.mtp3pojos.vo.warehouse.WarehouseExpressResult">
        select w.ordertype,w.outinmodel,w.shippingmodel,w.expressnum,w.expresscompany,w.vehiclenum,
        w.contactname,w.contactnum,w.outindate,w.createtime,w.updatetime,w.remark,
        u.customername username,
        s.wrstandardcode wrstandardcode,
        s.wrstandardname wrstandardname,
        a.applytime applytime,
        a.applyid applyid
        from wroutinapply a
        left join wroutinexpress w on w.applyid = a.applyid
        left join wrstandard s on a.wrstandardid = s.wrstandardid
        left join userinfo u on a.userid = u.userid
        <where>
            <if test="vo.expressnum != null and vo.expressnum != ''">
                and (w.expressnum like '%'||#{vo.expressnum,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.shippingmodel != null">
                and w.shippingmodel = #{vo.shippingmodel}
            </if>
            <if test="vo.contactname != null and vo.contactname != ''">
                and (w.contactname like '%'||#{vo.contactname,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.contactnum != null and vo.contactnum != ''">
                and (w.contactnum like '%'||#{vo.contactnum,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.applyid != null and vo.applyid != ''">
                and (w.applyid like '%'||#{vo.applyid,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.applytimestr != null and vo.applytimestr != ''">
                and a.applytime &gt;= to_date(#{vo.applytimestr}||',00:00:00','yyyyMMdd,hh24:mi:ss')
                and a.applytime &lt;= to_date(#{vo.applytimestr}||',23:59:59','yyyyMMdd,hh24:mi:ss')
            </if>
            <if test="vo.expresscompany != null and vo.expresscompany != ''">
                and (w.expresscompany like '%'||#{vo.expresscompany,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.username != null and vo.username != ''">
                and ((a.userid like '%'||#{vo.username,jdbcType=VARCHAR}||'%')
                or(u.customername like '%'||#{vo.username,jdbcType=VARCHAR}||'%'))
            </if>
            <if test="vo.outinmodel != null">
                and w.outinmodel = #{vo.outinmodel}
            </if>
            <if test="ordertype == 1">
                and a.applytype in (1,3)
            </if>
            <if test="ordertype == 2">
                and a.applytype in (2,4)
            </if>
            and a.ApplyStatus != 8 and a.ApplyStatus2 != 8
        </where>
        order by a.applytime desc
    </select>
</mapper>
