<?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.jsh.erp.datasource.mappers.DepotHeadMapperEx">

    <resultMap extends="com.jsh.erp.datasource.mappers.DepotHeadMapper.BaseResultMap" id="ResultMapEx" type="com.jsh.erp.datasource.vo.DepotHeadVo4List">
        <result column="depotHeadType" jdbcType="VARCHAR" property="depotHeadType" />
        <result column="OrganName" jdbcType="VARCHAR" property="organName" />
        <result column="userName" jdbcType="VARCHAR" property="userName" />
        <result column="AccountName" jdbcType="VARCHAR" property="accountName" />
        <result column="contacts" jdbcType="VARCHAR" property="contacts" />
        <result column="telephone" jdbcType="VARCHAR" property="telephone" />
        <result column="address" jdbcType="VARCHAR" property="address" />
    </resultMap>

    <resultMap id="ResultWithInfoExMap" type="com.jsh.erp.datasource.vo.DepotHeadVo4InDetail">
        <result column="Number" jdbcType="VARCHAR" property="Number" />
        <result column="bar_code" jdbcType="VARCHAR" property="barCode" />
        <result column="MName" jdbcType="VARCHAR" property="MName" />
        <result column="Model" jdbcType="VARCHAR" property="Model" />
        <result column="standard" jdbcType="VARCHAR" property="standard" />
        <result column="unit_price" jdbcType="DECIMAL" property="UnitPrice" />
        <result column="oper_number" jdbcType="DECIMAL" property="OperNumber" />
        <result column="all_price" jdbcType="DECIMAL" property="AllPrice" />
        <result column="tax_rate" jdbcType="DECIMAL" property="taxRate" />
        <result column="tax_money" jdbcType="DECIMAL" property="taxMoney" />
        <result column="tax_last_money" jdbcType="DECIMAL" property="taxLastMoney" />
        <result column="SName" jdbcType="VARCHAR" property="SName" />
        <result column="DName" jdbcType="VARCHAR" property="DName" />
        <result column="OperTime" jdbcType="VARCHAR" property="OperTime" />
        <result column="NewType" jdbcType="VARCHAR" property="NewType" />
    </resultMap>

    <resultMap id="ResultInOutMaterialCount" type="com.jsh.erp.datasource.vo.DepotHeadVo4InOutMCount">
        <result column="MaterialId" jdbcType="BIGINT" property="MaterialId" />
        <result column="bar_code" jdbcType="VARCHAR" property="barCode" />
        <result column="mName" jdbcType="VARCHAR" property="mName" />
        <result column="Model" jdbcType="VARCHAR" property="Model" />
        <result column="standard" jdbcType="VARCHAR" property="standard" />
        <result column="categoryName" jdbcType="VARCHAR" property="categoryName" />
        <result column="materialUnit" jdbcType="VARCHAR" property="materialUnit" />
        <result column="numSum" jdbcType="DECIMAL" property="numSum" />
        <result column="priceSum" jdbcType="DECIMAL" property="priceSum" />
    </resultMap>

    <resultMap id="ResultStatementAccount" type="com.jsh.erp.datasource.vo.DepotHeadVo4StatementAccount">
        <result column="number" jdbcType="VARCHAR" property="number" />
        <result column="type" jdbcType="VARCHAR" property="type" />
        <result column="discount_last_money" jdbcType="DECIMAL" property="discountLastMoney" />
        <result column="other_money" jdbcType="DECIMAL" property="otherMoney" />
        <result column="change_amount" jdbcType="DECIMAL" property="changeAmount" />
        <result column="supplierName" jdbcType="VARCHAR" property="supplierName" />
        <result column="oTime" jdbcType="VARCHAR" property="oTime" />
    </resultMap>

    <select id="selectByConditionDepotHead" parameterType="com.jsh.erp.datasource.entities.DepotHeadExample" resultMap="ResultMapEx">
        select distinct dh.*, s.supplier OrganName, u.username userName, a.name AccountName
        from jsh_depot_head dh
        left join jsh_depot_item di on dh.id = di.header_id and ifnull(di.delete_flag,'0') !='1'
        left join jsh_supplier s on dh.organ_id=s.id and ifnull(s.delete_Flag,'0') !='1'
        left join jsh_user u on dh.creator=u.id and ifnull(u.Status,'0') !='1'
        left join jsh_account a on dh.account_id=a.id and ifnull(a.delete_Flag,'0') !='1'
        left join jsh_material m on di.material_id = m.id and ifnull(m.delete_flag,'0') !='1'
        left join jsh_material_extend me on di.material_extend_id = me.id and ifnull(me.delete_flag,'0') !='1'
        where 1=1
        <if test="type != null">
            and dh.type=#{type}
        </if>
        <if test="subType != null">
            and dh.sub_type=#{subType}
        </if>
        <if test="statusArray != null and statusArray !=''">
            and dh.status in (
            <foreach collection="statusArray" item="status" separator=",">
                #{status}
            </foreach>
            )
        </if>
        <if test="purchaseStatusArray != null and purchaseStatusArray !=''">
            and dh.purchase_status in (
            <foreach collection="purchaseStatusArray" item="purchaseStatus" separator=",">
                #{purchaseStatus}
            </foreach>
            )
        </if>
        <if test="number != null">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and dh.number like #{bindNumber}
        </if>
        <if test="linkNumber != null">
            <bind name="bindLinkNumber" value="'%'+linkNumber+'%'"/>
            and dh.link_number like #{bindLinkNumber}
        </if>
        <if test="beginTime != null">
            and dh.oper_time >= #{beginTime}
        </if>
        <if test="endTime != null">
            and dh.oper_time &lt;= #{endTime}
        </if>
        <if test="materialParam != null and materialParam !=''">
            <bind name="bindKey" value="'%'+materialParam+'%'"/>
            and (me.bar_code like #{bindKey} or m.name like #{bindKey} or m.standard like #{bindKey} or m.model like #{bindKey})
        </if>
        <if test="organId != null">
            and dh.organ_id=#{organId}
        </if>
        <if test="organArray != null and organArray !=''">
            and dh.organ_id in (
            <foreach collection="organArray" item="organId" separator=",">
                #{organId}
            </foreach>
            )
        </if>
        <if test="creator != null">
            and dh.creator=#{creator}
        </if>
        <if test="depotId != null">
            and di.depot_id=#{depotId}
        </if>
        <if test="depotArray != null and depotArray !=''">
            and di.depot_id in (
            <foreach collection="depotArray" item="depotId" separator=",">
                #{depotId}
            </foreach>
            )
        </if>
        <if test="creatorArray != null">
            and dh.creator in (
            <foreach collection="creatorArray" item="creator" separator=",">
                #{creator}
            </foreach>
            )
        </if>
        <if test="accountId != null">
            <bind name="bindAccountId" value="'%'+accountId+'%'"/>
            and (dh.account_id=#{accountId} or dh.account_id_list like #{bindAccountId})
        </if>
        <if test="remark != null and remark !=''">
            <bind name="bindRemark" value="'%'+remark+'%'"/>
            and (dh.remark like #{bindRemark} or di.remark like #{bindRemark})
        </if>
        and ifnull(dh.delete_flag,'0') !='1'
        order by dh.id desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>
    <select id="countsByDepotHead" resultType="java.lang.Long">
        SELECT
        COUNT(1) from
        (select distinct dh.* FROM jsh_depot_head dh
        left join jsh_depot_item di on dh.Id = di.header_id and ifnull(di.delete_flag,'0') !='1'
        left join jsh_material m on di.material_id = m.Id and ifnull(m.delete_Flag,'0') !='1'
        left join jsh_material_extend me on di.material_extend_id = me.id and ifnull(me.delete_flag,'0') !='1'
        WHERE 1=1
        <if test="type != null">
            and dh.type=#{type}
        </if>
        <if test="subType != null">
            and dh.sub_type=#{subType}
        </if>
        <if test="statusArray != null and statusArray !=''">
            and dh.status in (
            <foreach collection="statusArray" item="status" separator=",">
                #{status}
            </foreach>
            )
        </if>
        <if test="purchaseStatusArray != null and purchaseStatusArray !=''">
            and dh.purchase_status in (
            <foreach collection="purchaseStatusArray" item="purchaseStatus" separator=",">
                #{purchaseStatus}
            </foreach>
            )
        </if>
        <if test="number != null">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and dh.number like #{bindNumber}
        </if>
        <if test="linkNumber != null">
            <bind name="bindLinkNumber" value="'%'+linkNumber+'%'"/>
            and dh.link_number like #{bindLinkNumber}
        </if>
        <if test="beginTime != null">
            and dh.oper_time >= #{beginTime}
        </if>
        <if test="endTime != null">
            and dh.oper_time &lt;= #{endTime}
        </if>
        <if test="materialParam != null and materialParam !=''">
            <bind name="bindKey" value="'%'+materialParam+'%'"/>
            and (me.bar_code like #{bindKey} or m.name like #{bindKey} or m.standard like #{bindKey} or m.model like #{bindKey})
        </if>
        <if test="organId != null">
            and dh.organ_id=#{organId}
        </if>
        <if test="organArray != null and organArray !=''">
            and dh.organ_id in (
            <foreach collection="organArray" item="organId" separator=",">
                #{organId}
            </foreach>
            )
        </if>
        <if test="creator != null">
            and dh.creator=#{creator}
        </if>
        <if test="depotId != null">
            and di.depot_id=#{depotId}
        </if>
        <if test="depotArray != null and depotArray !=''">
            and di.depot_id in (
            <foreach collection="depotArray" item="depotId" separator=",">
                #{depotId}
            </foreach>
            )
        </if>
        <if test="creatorArray != null">
            and dh.creator in (
            <foreach collection="creatorArray" item="creator" separator=",">
                #{creator}
            </foreach>
            )
        </if>
        <if test="accountId != null">
            <bind name="bindAccountId" value="'%'+accountId+'%'"/>
            and (dh.account_id=#{accountId} or dh.account_id_list like #{bindAccountId})
        </if>
        <if test="remark != null and remark !=''">
            <bind name="bindRemark" value="'%'+remark+'%'"/>
            and (dh.remark like #{bindRemark} or di.remark like #{bindRemark})
        </if>
        and ifnull(dh.delete_Flag,'0') !='1') tb
    </select>

    <select id="findMaterialsListByHeaderId" resultType="java.lang.String">
        select group_concat(concat(jsh_material.name,' ',ifnull(jsh_material.standard,''),' ',ifnull(jsh_material.model,''))) as mName
        from jsh_depot_item
        left join jsh_material on jsh_depot_item.material_id = jsh_material.Id and ifnull(jsh_material.delete_Flag,'0') !='1'
        where jsh_depot_item.header_id = #{id}
        and ifnull(jsh_depot_item.delete_flag,'0') !='1'
    </select>

    <select id="getMaterialCountByHeaderId" resultType="java.math.BigDecimal">
        select sum(oper_number) from jsh_depot_item
        where header_id = #{id}
        and ifnull(delete_flag,'0') !='1'
    </select>

    <select id="findInOutDetail" parameterType="com.jsh.erp.datasource.entities.DepotItemExample" resultMap="ResultWithInfoExMap">
        select dh.number,me.bar_code, m.`name` MName,m.model,m.standard,di.unit_price,di.material_unit as mUnit,
        di.oper_number,di.all_price, ifnull(di.tax_rate,0) tax_rate, ifnull(di.tax_money,0) tax_money, ifnull(di.tax_last_money,0) tax_last_money,
        s.supplier SName,d.dName DName, date_format(dh.oper_time, '%Y-%m-%d') OperTime, concat(dh.sub_type,dh.type) as NewType,
        concat_ws('  ',dh.remark,di.remark) as newRemark
        from jsh_depot_head dh
        left join jsh_depot_item di on di.header_id=dh.id and ifnull(di.delete_flag,'0') !='1'
        left join jsh_material m on m.id=di.material_id and ifnull(m.delete_flag,'0') !='1'
        left join jsh_material_extend me on me.id=di.material_extend_id and ifnull(me.delete_Flag,'0') !='1'
        left join jsh_supplier s on s.id=dh.organ_id and ifnull(s.delete_Flag,'0') !='1'
        left join (select id,name as dName,delete_Flag from jsh_depot ) d on d.id=di.depot_id and ifnull(d.delete_Flag,'0') !='1'
        where dh.oper_time >=#{beginTime} and dh.oper_time &lt;=#{endTime}
        <if test="oId != null">
            and dh.organ_id = #{oId}
        </if>
        <if test="depotList.size()>0">
            and di.depot_id in
            <foreach collection="depotList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="type != null">
            and dh.type=#{type}
        </if>
        <if test="creatorArray != null">
            and dh.creator in (
            <foreach collection="creatorArray" item="creator" separator=",">
                #{creator}
            </foreach>
            )
        </if>
        <if test="organArray != null and organArray !=''">
            and (dh.organ_id in (
            <foreach collection="organArray" item="organId" separator=",">
                #{organId}
            </foreach>
            ) or dh.sub_type='采购退货' or dh.sub_type='零售')
        </if>
        <if test="number != null and number !=''">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and dh.number like #{bindNumber}
        </if>
        <if test="materialParam != null and materialParam !=''">
            <bind name="bindKey" value="'%'+materialParam+'%'"/>
            and (me.bar_code like #{bindKey} or m.name like #{bindKey} or m.standard like #{bindKey} or m.model like #{bindKey})
        </if>
        <if test="remark != null and remark !=''">
            <bind name="bindRemark" value="'%'+remark+'%'"/>
            and (dh.remark like #{bindRemark} or di.remark like #{bindRemark})
        </if>
        and dh.sub_type!='调拨'
        and ifnull(dh.delete_flag,'0') !='1'
        ORDER BY oper_time DESC,number desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>

    <select id="findInOutDetailCount" resultType="java.lang.Integer">
        select count(1)
        from jsh_depot_head dh
        left join jsh_depot_item di on di.header_id=dh.id and ifnull(di.delete_flag,'0') !='1'
        left join jsh_material m on m.id=di.material_id and ifnull(m.delete_Flag,'0') !='1'
        left join jsh_material_extend me on me.id=di.material_extend_id and ifnull(me.delete_Flag,'0') !='1'
        left join jsh_supplier s on s.id=dh.organ_id and ifnull(s.delete_Flag,'0') !='1'
        left join (select id,name as dName,delete_Flag from jsh_depot) d on d.id=di.depot_id and ifnull(d.delete_Flag,'0') !='1'
        where dh.oper_time >=#{beginTime} and dh.oper_time &lt;=#{endTime}
        <if test="oId != null">
            and dh.organ_id = #{oId}
        </if>
        <if test="depotList.size()>0">
            and di.depot_id in
            <foreach collection="depotList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="type != null">
            and dh.type=#{type}
        </if>
        <if test="creatorArray != null">
            and dh.creator in (
            <foreach collection="creatorArray" item="creator" separator=",">
                #{creator}
            </foreach>
            )
        </if>
        <if test="organArray != null and organArray !=''">
            and (dh.organ_id in (
            <foreach collection="organArray" item="organId" separator=",">
                #{organId}
            </foreach>
            ) or dh.sub_type='采购退货' or dh.sub_type='零售')
        </if>
        <if test="number != null and number !=''">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and dh.number like #{bindNumber}
        </if>
        <if test="materialParam != null and materialParam !=''">
            <bind name="bindKey" value="'%'+materialParam+'%'"/>
            and (me.bar_code like #{bindKey} or m.name like #{bindKey} or m.standard like #{bindKey} or m.model like #{bindKey})
        </if>
        <if test="remark != null and remark !=''">
            <bind name="bindRemark" value="'%'+remark+'%'"/>
            and (dh.remark like #{bindRemark} or di.remark like #{bindRemark})
        </if>
        and dh.sub_type!='调拨'
        and ifnull(dh.delete_flag,'0') !='1'
        ORDER BY oper_time DESC,number desc
    </select>

    <select id="findInOutMaterialCount" parameterType="com.jsh.erp.datasource.entities.DepotItemExample" resultMap="ResultInOutMaterialCount">
        select di.material_id, me.bar_code, m.name mName,m.Model,m.standard,mc.name categoryName,concat_ws('', m.unit, u.basic_unit) materialUnit,
        sum(di.basic_number) numSum,
        sum(di.all_price) priceSum
        from jsh_depot_head dh
        left join jsh_depot_item di on dh.id=di.header_id and ifnull(di.delete_flag,'0') !='1'
        left join jsh_material m on m.id=di.material_id and ifnull(m.delete_flag,'0') !='1'
        left join jsh_unit u on m.unit_id=u.id and ifnull(u.delete_Flag,'0') !='1'
        left join jsh_material_extend me on me.material_id=m.id and ifnull(me.delete_Flag,'0') !='1'
        left join jsh_material_category mc on m.category_id=mc.Id and ifnull(mc.delete_flag,'0') !='1'
        where me.default_flag=1
        and (dh.type=#{type} or dh.sub_type='调拨') and dh.oper_time >=#{beginTime} and dh.oper_time &lt;=#{endTime}
        <if test="oId != null">
            and dh.organ_id = #{oId}
        </if>
        <if test="creatorArray != null">
            and dh.creator in (
            <foreach collection="creatorArray" item="creator" separator=",">
                #{creator}
            </foreach>
            )
        </if>
        <if test="organArray != null and organArray !=''">
            and (dh.organ_id in (
            <foreach collection="organArray" item="organId" separator=",">
                #{organId}
            </foreach>
            ) or dh.sub_type='采购退货' or dh.sub_type='零售')
        </if>
        <if test="depotList.size()>0">
            <if test="type == '入库'">
                and ((
                dh.sub_type!='调拨' and di.depot_id in
                <foreach collection="depotList" item="item" index="index" separator="," open="(" close=")">
                    #{item}
                </foreach>
                ) or (
                dh.sub_type='调拨' and di.another_depot_id in
                <foreach collection="depotList" item="item" index="index" separator="," open="(" close=")">
                    #{item}
                </foreach>
                ))
            </if>
            <if test="type == '出库'">
                and di.depot_id in
                <foreach collection="depotList" item="item" index="index" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
        </if>
        <if test="materialParam != null and materialParam !=''">
            <bind name="bindKey" value="'%'+materialParam+'%'"/>
            and (me.bar_code like #{bindKey} or m.name like #{bindKey} or m.standard like #{bindKey} or m.model like #{bindKey})
        </if>
        and ifnull(dh.delete_flag,'0') !='1'
        group by di.material_id
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>

    <select id="findInOutMaterialCountTotal" resultType="java.lang.Integer">
        select count(1) from
        (select di.material_id from jsh_depot_head dh
        left join jsh_depot_item di on dh.id=di.header_id  and ifnull(di.delete_flag,'0') !='1'
        left join jsh_material m on m.id=di.material_id and ifnull(m.delete_flag,'0') !='1'
        left join jsh_material_extend me on me.material_id=m.id and ifnull(me.delete_flag,'0') !='1'
        where me.default_flag=1
        and (dh.type=#{type} or dh.sub_type='调拨') and dh.oper_time >=#{beginTime} and dh.oper_time &lt;=#{endTime}
        <if test="oId != null">
            and dh.organ_id = #{oId}
        </if>
        <if test="creatorArray != null">
            and dh.creator in (
            <foreach collection="creatorArray" item="creator" separator=",">
                #{creator}
            </foreach>
            )
        </if>
        <if test="organArray != null and organArray !=''">
            and (dh.organ_id in (
            <foreach collection="organArray" item="organId" separator=",">
                #{organId}
            </foreach>
            ) or dh.sub_type='采购退货' or dh.sub_type='零售')
        </if>
        <if test="depotList.size()>0">
            <if test="type == '入库'">
                 and ((
                 dh.sub_type!='调拨' and di.depot_id in
                <foreach collection="depotList" item="item" index="index" separator="," open="(" close=")">
                    #{item}
                </foreach>
                 ) or (
                 dh.sub_type='调拨' and di.another_depot_id in
                <foreach collection="depotList" item="item" index="index" separator="," open="(" close=")">
                    #{item}
                </foreach>
                 ))
            </if>
            <if test="type == '出库'">
                and di.depot_id in
                <foreach collection="depotList" item="item" index="index" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
        </if>
        <if test="materialParam != null and materialParam !=''">
            <bind name="bindKey" value="'%'+materialParam+'%'"/>
            and (me.bar_code like #{bindKey} or m.name like #{bindKey} or m.standard like #{bindKey} or m.model like #{bindKey})
        </if>
        and ifnull(dh.delete_flag,'0') !='1'
        group by di.material_id) a
    </select>

    <select id="findAllocationDetail" parameterType="com.jsh.erp.datasource.entities.DepotItemExample" resultMap="ResultWithInfoExMap">
        select dh.number,me.bar_code, m.`name` MName,m.model,m.standard,di.unit_price,di.material_unit as mUnit,di.oper_number,di.all_price,SName, d.dName DName,
        date_format(dh.oper_time, '%Y-%m-%d') OperTime, concat(dh.sub_type,dh.type) as NewType, concat_ws('  ',dh.remark,di.remark) as newRemark
        from jsh_depot_head dh
        left join jsh_depot_item di on di.header_id=dh.id and ifnull(di.delete_flag,'0') !='1'
        left join jsh_material m on m.id=di.material_id and ifnull(m.delete_flag,'0') !='1'
        left join jsh_material_extend me on me.id=di.material_extend_id and ifnull(me.delete_Flag,'0') !='1'
        left join (select id,name as dName,delete_Flag from jsh_depot ) d on d.id=di.depot_id and ifnull(d.delete_Flag,'0') !='1'
        <!-- 调出仓库名查询 -->
        left join (select id as aid,name as SName,delete_Flag as adelete_Flag from jsh_depot ) ddd on ddd.aid=di.another_depot_id and ifnull(ddd.adelete_Flag,'0') !='1'
        where dh.oper_time >=#{beginTime} and dh.oper_time &lt;=#{endTime}
        <if test="depotFList.size()>0">
            and di.depot_id in
            <foreach collection="depotFList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="depotList.size()>0">
            and di.another_depot_id in
            <foreach collection="depotList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="subType != null">
            and dh.sub_type=#{subType}
        </if>
        <if test="number != null and number !=''">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and dh.number like #{bindNumber}
        </if>
        <if test="creatorArray != null">
            and dh.creator in (
            <foreach collection="creatorArray" item="creator" separator=",">
                #{creator}
            </foreach>
            )
        </if>
        <if test="materialParam != null and materialParam !=''">
            <bind name="bindKey" value="'%'+materialParam+'%'"/>
            and (me.bar_code like #{bindKey} or m.name like #{bindKey} or m.standard like #{bindKey} or m.model like #{bindKey} or dh.remark like #{bindKey})
        </if>
        <if test="remark != null and remark !=''">
            <bind name="bindRemark" value="'%'+remark+'%'"/>
            and (dh.remark like #{bindRemark} or di.remark like #{bindRemark})
        </if>
        and ifnull(dh.delete_flag,'0') !='1'
        ORDER BY oper_time DESC,number desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>

    <select id="findAllocationDetailCount" resultType="java.lang.Integer">
        select count(1)
        from jsh_depot_head dh
        left join jsh_depot_item di on di.header_id=dh.id and ifnull(di.delete_flag,'0') !='1'
        left join jsh_material m on m.id=di.material_id and ifnull(m.delete_Flag,'0') !='1'
        left join jsh_material_extend me on me.id=di.material_extend_id and ifnull(me.delete_Flag,'0') !='1'
        left join (select id,name as dName,delete_Flag from jsh_depot) d on d.id=di.depot_id and ifnull(d.delete_Flag,'0') !='1'
        where dh.oper_time >=#{beginTime} and dh.oper_time &lt;=#{endTime}
        <if test="depotFList.size()>0">
            and di.depot_id in
            <foreach collection="depotFList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="depotList.size()>0">
            and di.another_depot_id in
            <foreach collection="depotList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="subType != null">
            and dh.sub_type=#{subType}
        </if>
        <if test="number != null and number !=''">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and dh.number like #{bindNumber}
        </if>
        <if test="creatorArray != null">
            and dh.creator in (
            <foreach collection="creatorArray" item="creator" separator=",">
                #{creator}
            </foreach>
            )
        </if>
        <if test="materialParam != null and materialParam !=''">
            <bind name="bindKey" value="'%'+materialParam+'%'"/>
            and (me.bar_code like #{bindKey} or m.name like #{bindKey} or m.standard like #{bindKey} or m.model like #{bindKey} or dh.remark like #{bindKey})
        </if>
        <if test="remark != null and remark !=''">
            <bind name="bindRemark" value="'%'+remark+'%'"/>
            and (dh.remark like #{bindRemark} or di.remark like #{bindRemark})
        </if>
        and ifnull(dh.delete_flag,'0') !='1'
        ORDER BY oper_time DESC,number desc
    </select>

    <select id="getStatementAccount" resultType="com.jsh.erp.datasource.vo.DepotHeadVo4StatementAccount">
        select * from
        (select s.id, s.supplier, s.contacts, s.telephone, s.phone_num, s.email,
            (case when s.type='供应商' then ifnull(s.begin_need_pay,0) else ifnull(s.begin_need_get,0) end) begin_need,
            (select
            ifnull(sum(dh.discount_last_money),0)+ifnull(sum(dh.other_money),0)-ifnull(sum(dh.deposit),0)-abs(ifnull(sum(dh.change_amount),0))
            from jsh_depot_head dh
            where dh.organ_id=s.id
            and dh.type=#{type} and dh.sub_type=#{subType}
            and dh.oper_time &lt;= #{beginTime}
            and ifnull(dh.delete_flag,'0') !='1') preDebtMoney,
            (select abs(ifnull(sum(ai.each_amount),0)) from jsh_account_item ai
            left join jsh_depot_head dh on ai.bill_id=dh.id and ifnull(dh.delete_flag,'0') !='1'
            where dh.organ_id=s.id
            and dh.oper_time &lt;= #{beginTime}
            and ifnull(ai.delete_flag,'0') !='1') preBackMoney,
            (select
            ifnull(sum(dh.discount_last_money),0)+ifnull(sum(dh.other_money),0)-ifnull(sum(dh.deposit),0)-abs(ifnull(sum(dh.change_amount),0))
            from jsh_depot_head dh
            where dh.organ_id=s.id
            and dh.type=#{type} and dh.sub_type=#{subType}
            and dh.oper_time>#{beginTime} and dh.oper_time &lt;= #{endTime}
            and ifnull(dh.delete_flag,'0') !='1') debtMoney,
            (select abs(ifnull(sum(ai.each_amount),0)) from jsh_account_item ai
            left join jsh_depot_head dh on ai.bill_id=dh.id and ifnull(dh.delete_flag,'0') !='1'
            where dh.organ_id=s.id
            and dh.oper_time>#{beginTime} and dh.oper_time &lt;= #{endTime}
            and ifnull(ai.delete_flag,'0') !='1') backMoney
        from jsh_supplier s
        where s.enabled=1
        and s.type=#{supplierType}
        <if test="organId != null">
            and s.id = #{organId}
        </if>
        <if test="organArray != null and organArray !=''">
            and s.id in (
            <foreach collection="organArray" item="organId" separator=",">
                #{organId}
            </foreach>
            )
        </if>
        and ifnull(s.delete_flag,'0') !='1') tb
        where begin_need+preDebtMoney-preBackMoney+debtMoney-backMoney>0
        order by begin_need+preDebtMoney-preBackMoney+debtMoney-backMoney desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>

    <select id="getStatementAccountCount" resultType="java.lang.Integer">
        select count(1) from
        (select s.id, s.supplier, s.contacts, s.telephone, s.phone_num, s.email,
        (case when s.type='供应商' then ifnull(s.begin_need_pay,0) else ifnull(s.begin_need_get,0) end) begin_need,
        (select
        ifnull(sum(dh.discount_last_money),0)+ifnull(sum(dh.other_money),0)-ifnull(sum(dh.deposit),0)-abs(ifnull(sum(dh.change_amount),0))
        from jsh_depot_head dh
        where dh.organ_id=s.id
        and dh.type=#{type} and dh.sub_type=#{subType}
        and dh.oper_time &lt;= #{beginTime}
        and ifnull(dh.delete_flag,'0') !='1') preDebtMoney,
        (select abs(ifnull(sum(ai.each_amount),0)) from jsh_account_item ai
        left join jsh_depot_head dh on ai.bill_id=dh.id and ifnull(dh.delete_flag,'0') !='1'
        where dh.organ_id=s.id
        and dh.oper_time &lt;= #{beginTime}
        and ifnull(ai.delete_flag,'0') !='1') preBackMoney,
        (select
        ifnull(sum(dh.discount_last_money),0)+ifnull(sum(dh.other_money),0)-ifnull(sum(dh.deposit),0)-abs(ifnull(sum(dh.change_amount),0))
        from jsh_depot_head dh
        where dh.organ_id=s.id
        and dh.type=#{type} and dh.sub_type=#{subType}
        and dh.oper_time>#{beginTime} and dh.oper_time &lt;= #{endTime}
        and ifnull(dh.delete_flag,'0') !='1') debtMoney,
        (select abs(ifnull(sum(ai.each_amount),0)) from jsh_account_item ai
        left join jsh_depot_head dh on ai.bill_id=dh.id and ifnull(dh.delete_flag,'0') !='1'
        where dh.organ_id=s.id
        and dh.oper_time>#{beginTime} and dh.oper_time &lt;= #{endTime}
        and ifnull(ai.delete_flag,'0') !='1') backMoney
        from jsh_supplier s
        where s.enabled=1
        and s.type=#{supplierType}
        <if test="organId != null">
            and s.id = #{organId}
        </if>
        <if test="organArray != null and organArray !=''">
            and s.id in (
            <foreach collection="organArray" item="organId" separator=",">
                #{organId}
            </foreach>
            )
        </if>
        and ifnull(s.delete_flag,'0') !='1') tb
        where begin_need+preDebtMoney-preBackMoney+debtMoney-backMoney>0
    </select>

    <select id="getStatementAccountTotalPay" resultType="com.jsh.erp.datasource.vo.DepotHeadVo4StatementAccount">
        select sum(begin_need) begin_need, sum(preDebtMoney) preDebtMoney, sum(preBackMoney) preBackMoney,
               sum(debtMoney) debtMoney, sum(backMoney) backMoney from
        (select s.id,
        (case when s.type='供应商' then ifnull(s.begin_need_pay,0) else ifnull(s.begin_need_get,0) end) begin_need,
        (select
        ifnull(sum(dh.discount_last_money),0)+ifnull(sum(dh.other_money),0)-ifnull(sum(dh.deposit),0)-abs(ifnull(sum(dh.change_amount),0))
        from jsh_depot_head dh
        where dh.organ_id=s.id
        and dh.type=#{type} and dh.sub_type=#{subType}
        and dh.oper_time &lt;= #{beginTime}
        and ifnull(dh.delete_flag,'0') !='1') preDebtMoney,
        (select abs(ifnull(sum(ai.each_amount),0)) from jsh_account_item ai
        left join jsh_depot_head dh on ai.bill_id=dh.id and ifnull(dh.delete_flag,'0') !='1'
        where dh.organ_id=s.id
        and dh.oper_time &lt;= #{beginTime}
        and ifnull(ai.delete_flag,'0') !='1') preBackMoney,
        (select
        ifnull(sum(dh.discount_last_money),0)+ifnull(sum(dh.other_money),0)-ifnull(sum(dh.deposit),0)-abs(ifnull(sum(dh.change_amount),0))
        from jsh_depot_head dh
        where dh.organ_id=s.id
        and dh.type=#{type} and dh.sub_type=#{subType}
        and dh.oper_time>#{beginTime} and dh.oper_time &lt;= #{endTime}
        and ifnull(dh.delete_flag,'0') !='1') debtMoney,
        (select abs(ifnull(sum(ai.each_amount),0)) from jsh_account_item ai
        left join jsh_depot_head dh on ai.bill_id=dh.id and ifnull(dh.delete_flag,'0') !='1'
        where dh.organ_id=s.id
        and dh.oper_time>#{beginTime} and dh.oper_time &lt;= #{endTime}
        and ifnull(ai.delete_flag,'0') !='1') backMoney
        from jsh_supplier s
        where s.enabled=1
        and s.type=#{supplierType}
        <if test="organId != null">
            and s.id = #{organId}
        </if>
        <if test="organArray != null and organArray !=''">
            and s.id in (
            <foreach collection="organArray" item="organId" separator=",">
                #{organId}
            </foreach>
            )
        </if>
        and ifnull(s.delete_flag,'0') !='1') tb
        where begin_need+preDebtMoney-preBackMoney+debtMoney-backMoney>0
    </select>

    <select id="findAllMoney" resultType="java.math.BigDecimal">
        select ifnull(sum(${modeName}),0) as allMoney from jsh_depot_head where type=#{type} and sub_type = #{subType}
        and organ_id =#{supplierId} and oper_time &lt;=#{endTime}
        and ifnull(delete_flag,'0') !='1'
    </select>

    <select id="findAllOtherMoney" resultType="java.math.BigDecimal">
        select ifnull(sum(other_money),0) as allOtherMoney from jsh_depot_head where type=#{type} and sub_type = #{subType}
        and organ_id =#{supplierId} and oper_time &lt;=#{endTime}
        and ifnull(delete_flag,'0') !='1'
    </select>

    <select id="findDepositMoney" resultType="java.math.BigDecimal">
        select ifnull(sum(deposit),0) as allDepositMoney from jsh_depot_head where type=#{type} and sub_type = #{subType}
        and organ_id =#{supplierId} and oper_time &lt;=#{endTime}
        and ifnull(delete_flag,'0') !='1'
    </select>

    <select id="getDetailByNumber" parameterType="com.jsh.erp.datasource.entities.DepotHeadExample" resultMap="ResultMapEx">
        select dh.*, concat(dh.sub_type,dh.type) as depotHeadType, a.name AccountName,
               s.supplier OrganName, s.contacts, s.telephone, s.address
        from jsh_depot_head dh
        left join jsh_supplier s on dh.organ_id=s.id and ifnull(s.delete_Flag,'0') !='1'
        left join jsh_account a on dh.account_id=a.id and ifnull(a.delete_Flag,'0') !='1'
        where 1=1
        <if test="number != null">
            and dh.number=#{number}
        </if>
        and ifnull(dh.delete_flag,'0') !='1'
    </select>

    <update id="batchDeleteDepotHeadByIds">
        update jsh_depot_head
        set delete_flag='1'
        where 1=1
        and id in (
        <foreach collection="ids" item="id" separator=",">
            #{id}
        </foreach>
        )
    </update>
    <select id="getDepotHeadListByAccountIds" resultMap="com.jsh.erp.datasource.mappers.DepotHeadMapper.BaseResultMap">
        select
        <include refid="com.jsh.erp.datasource.mappers.DepotHeadMapper.Base_Column_List" />
        from jsh_depot_head
        where 1=1
        and account_id in (
        <foreach collection="accountIds" item="accountId" separator=",">
            #{accountId}
        </foreach>
        )
        and ifnull(delete_flag,'0') !='1'
    </select>
    <select id="getDepotHeadListByOrganIds" resultMap="com.jsh.erp.datasource.mappers.DepotHeadMapper.BaseResultMap">
        select
        <include refid="com.jsh.erp.datasource.mappers.DepotHeadMapper.Base_Column_List" />
        from jsh_depot_head
        where 1=1
        and organ_id in (
        <foreach collection="organIds" item="organId" separator=",">
            #{organId}
        </foreach>
        )
        and ifnull(delete_flag,'0') !='1'
    </select>
    <select id="getDepotHeadListByCreator" resultMap="com.jsh.erp.datasource.mappers.DepotHeadMapper.BaseResultMap">
        select
        <include refid="com.jsh.erp.datasource.mappers.DepotHeadMapper.Base_Column_List" />
        from jsh_depot_head
        where 1=1
        and creator in (
        <foreach collection="creatorArray" item="creator" separator=",">
            #{creator}
        </foreach>
        )
        and ifnull(delete_flag,'0') !='1'
    </select>

    <select id="getBuyAndSaleBasicStatistics" resultType="java.math.BigDecimal">
        SELECT
        ifnull(sum(discount_last_money),0)
        FROM jsh_depot_head
        WHERE 1=1
        <if test="type != null">
            and type=#{type}
        </if>
        <if test="subType != null">
            and sub_type=#{subType}
        </if>
        <if test="hasSupplier == 1">
            and organ_id is not null
        </if>
        <if test="hasSupplier == 0">
            and organ_id is null
        </if>
        <if test="beginTime != null">
            and oper_time >= #{beginTime}
        </if>
        <if test="endTime != null">
            and oper_time &lt;= #{endTime}
        </if>
        <if test="creatorArray != null">
            and creator in (
            <foreach collection="creatorArray" item="creator" separator=",">
                #{creator}
            </foreach>
            )
        </if>
        and ifnull(delete_flag,'0') !='1'
    </select>

    <select id="getBuyAndSaleRetailStatistics" resultType="java.math.BigDecimal">
        SELECT
        ifnull(sum(total_price),0)
        FROM jsh_depot_head
        WHERE 1=1
        <if test="type != null">
            and type=#{type}
        </if>
        <if test="subType != null">
            and sub_type=#{subType}
        </if>
        <if test="beginTime != null">
            and oper_time >= #{beginTime}
        </if>
        <if test="endTime != null">
            and oper_time &lt;= #{endTime}
        </if>
        <if test="creatorArray != null">
            and creator in (
            <foreach collection="creatorArray" item="creator" separator=",">
                #{creator}
            </foreach>
            )
        </if>
        and ifnull(delete_flag,'0') !='1'
    </select>

    <select id="debtList" parameterType="com.jsh.erp.datasource.entities.DepotHeadExample" resultMap="ResultMapEx">
        select distinct dh.*, s.supplier OrganName, u.username userName
        from jsh_depot_head dh
        left join jsh_supplier s on dh.organ_id=s.id and ifnull(s.delete_Flag,'0') !='1'
        left join jsh_user u on dh.creator=u.id and ifnull(u.Status,'0') !='1'
        left join jsh_depot_item di on dh.id = di.header_id and ifnull(di.delete_flag,'0') !='1'
        left join jsh_material m on di.material_id = m.id and ifnull(m.delete_flag,'0') !='1'
        where 1=1
        <if test="organId != null">
            and dh.organ_id = #{organId}
        </if>
        <if test="type != null">
            and dh.type=#{type}
        </if>
        <if test="subType != null">
            and dh.sub_type=#{subType}
        </if>
        <if test="status != null">
            and dh.status =#{status}
        </if>
        <if test="number != null">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and dh.number like #{bindNumber}
        </if>
        <if test="beginTime != null">
            and dh.oper_time >= #{beginTime}
        </if>
        <if test="endTime != null">
            and dh.oper_time &lt;= #{endTime}
        </if>
        <if test="materialParam != null and materialParam !=''">
            <bind name="bindKey" value="'%'+materialParam+'%'"/>
            and (m.name like #{bindKey} or m.standard like #{bindKey} or m.model like #{bindKey})
        </if>
        <if test="depotArray != null and depotArray !=''">
            and di.depot_id in (
            <foreach collection="depotArray" item="depotId" separator=",">
                #{depotId}
            </foreach>
            )
        </if>
        <if test="creatorArray != null">
            and dh.creator in (
            <foreach collection="creatorArray" item="creator" separator=",">
                #{creator}
            </foreach>
            )
        </if>
        and (abs(dh.change_amount)+(select ifnull(sum(abs(ai.each_amount)),0) from jsh_account_item ai where ai.bill_id=dh.id and ifnull(ai.delete_flag,'0') !='1'))
        &lt; (dh.discount_last_money + dh.other_money - ifnull(dh.deposit,0))
        and ifnull(dh.delete_flag,'0') !='1'
        order by dh.oper_time desc
    </select>

    <select id="getFinishDepositByNumber" resultType="java.math.BigDecimal">
        select ifnull(sum(dh.deposit),0)
        from jsh_depot_head dh
        where dh.link_number=#{number}
        and ifnull(dh.delete_flag,'0') !='1'
    </select>

    <select id="getFinishDepositByNumberExceptCurrent" resultType="java.math.BigDecimal">
        select ifnull(sum(dh.deposit),0)
        from jsh_depot_head dh
        where dh.link_number=#{linkNumber}
        and dh.number!=#{number}
        and ifnull(dh.delete_flag,'0') !='1'
    </select>
</mapper>