<?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.AccountMapperEx">
    <resultMap extends="com.jsh.erp.datasource.mappers.AccountMapper.BaseResultMap" id="ResultListMap" type="com.jsh.erp.datasource.vo.AccountVo4List">
        <result column="thisMonthAmount" jdbcType="VARCHAR" property="thisMonthAmount" />
    </resultMap>

    <resultMap id="ResultInOutList" type="com.jsh.erp.datasource.vo.AccountVo4InOutList">
        <result column="Number" jdbcType="VARCHAR" property="number" />
        <result column="newType" jdbcType="VARCHAR" property="type" />
        <result column="fromType" jdbcType="VARCHAR" property="fromType" />
        <result column="supplier" jdbcType="VARCHAR" property="supplierName" />
        <result column="change_amount" jdbcType="DECIMAL" property="changeAmount" />
        <result column="oTime" jdbcType="VARCHAR" property="operTime" />
        <result column="AList" jdbcType="VARCHAR" property="aList" />
        <result column="AMList" jdbcType="VARCHAR" property="amList" />
    </resultMap>

    <select id="getAccountByParam" resultType="com.jsh.erp.datasource.entities.Account">
        select *
        FROM jsh_account
        where 1=1
        <if test="name != null and name !=''">
            <bind name="bindName" value="'%'+name+'%'"/>
            and name like #{bindName}
        </if>
        <if test="serialNo != null and serialNo !=''">
            <bind name="bindSerialNo" value="'%'+serialNo+'%'"/>
            and serial_no like #{bindSerialNo}
        </if>
        and ifnull(delete_flag,'0') !='1'
    </select>

    <select id="selectByConditionAccount" parameterType="com.jsh.erp.datasource.entities.AccountExample" resultMap="ResultListMap">
        select *
        FROM jsh_account
        where 1=1
        <if test="name != null">
            <bind name="bindName" value="'%'+name+'%'"/>
            and name like #{bindName}
        </if>
        <if test="serialNo != null and serialNo !=''">
            <bind name="bindSerialNo" value="'%'+serialNo+'%'"/>
            and serial_no like #{bindSerialNo}
        </if>
        <if test="remark != null">
            <bind name="bindRemark" value="'%'+remark+'%'"/>
            and remark like #{bindRemark}
        </if>
        and ifnull(delete_flag,'0') !='1'
        order by sort asc, id desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>

    <select id="countsByAccount" resultType="java.lang.Long">
        SELECT
        COUNT(id)
        FROM jsh_account
        WHERE 1=1
        <if test="name != null">
            <bind name="bindName" value="'%'+name+'%'"/>
            and name like #{bindName}
        </if>
        <if test="serialNo != null and serialNo !=''">
            <bind name="bindSerialNo" value="'%'+serialNo+'%'"/>
            and serial_no like #{bindSerialNo}
        </if>
        <if test="remark != null">
            <bind name="bindRemark" value="'%'+remark+'%'"/>
            and remark like #{bindRemark}
        </if>
        and ifnull(delete_flag,'0') !='1'
    </select>

    <select id="getAccountSum" resultType="java.math.BigDecimal">
        select ifnull(sum(dh.change_amount),0) from jsh_depot_head dh
        where 1=1 and dh.pay_type != '预付款'
        <if test="accountId != null">
            and dh.account_id = #{accountId}
        </if>
        <if test="beginTime != null">
            and dh.oper_time >= #{beginTime}
        </if>
        <if test="endTime != null">
            and dh.oper_time &lt;= #{endTime}
        </if>
        <if test="forceFlag">
            and (dh.status = '1' or dh.status = '2' or dh.status = '3')
        </if>
        and ifnull(dh.delete_flag,'0') !='1'
    </select>

    <select id="getAccountSumByHead" resultType="java.math.BigDecimal">
        select ifnull(sum(ah.change_amount),0) from jsh_account_head ah
        where 1=1
        <if test="accountId != null">
            and ah.account_id = #{accountId}
        </if>
        <if test="beginTime != null">
            and ah.bill_time >= #{beginTime}
        </if>
        <if test="endTime != null">
            and ah.bill_time &lt;= #{endTime}
        </if>
        <if test="forceFlag">
            and ah.status = '1'
        </if>
        and ifnull(ah.delete_flag,'0') !='1'
    </select>

    <select id="getAccountSumByDetail" resultType="java.math.BigDecimal">
        select ifnull(sum(ai.each_amount),0) from jsh_account_head ah
        left join jsh_account_item ai on ah.id = ai.header_id and ifnull(ai.delete_flag,'0') !='1'
        where 1=1
        <if test="accountId != null">
            and ai.account_id = #{accountId}
        </if>
        <if test="beginTime != null">
            and ah.bill_time >= #{beginTime}
        </if>
        <if test="endTime != null">
            and ah.bill_time &lt;= #{endTime}
        </if>
        <if test="forceFlag">
            and ah.status = '1'
        </if>
        and ifnull(ah.delete_flag,'0') !='1'
    </select>

    <select id="getManyAccountSum" resultType="com.jsh.erp.datasource.entities.DepotHead">
        select dh.account_id_list accountIdList, dh.account_money_list accountMoneyList from jsh_depot_head dh
        where 1=1
        <if test="accountId != null">
            <bind name="bindAccountId" value="'%'+accountId+'%'"/>
            and dh.account_id_list like #{bindAccountId}
        </if>
        <if test="beginTime != null">
            and dh.oper_time >= #{beginTime}
        </if>
        <if test="endTime != null">
            and dh.oper_time &lt;= #{endTime}
        </if>
        <if test="forceFlag">
            and (dh.status = '1' or dh.status = '2' or dh.status = '3')
        </if>
        and ifnull(dh.delete_flag,'0') !='1'
    </select>

    <select id="getAccountSumByParam" resultType="com.jsh.erp.datasource.entities.AccountVo4Sum">
        select a.id,
            (select ifnull(sum(dh.change_amount),0) from jsh_depot_head dh
            where 1=1 and dh.pay_type != '预付款'
            and dh.account_id = a.id
            <if test="beginTime != null">
                and dh.oper_time >= #{beginTime}
            </if>
            <if test="endTime != null">
                and dh.oper_time &lt;= #{endTime}
            </if>
            <if test="forceFlag">
                and (dh.status = '1' or dh.status = '2' or dh.status = '3')
            </if>
            and ifnull(dh.delete_flag,'0') !='1') accountSum,
            (select ifnull(sum(ah.change_amount),0) from jsh_account_head ah
            where 1=1 and ah.account_id = a.id
            <if test="beginTime != null">
                and ah.bill_time >= #{beginTime}
            </if>
            <if test="endTime != null">
                and ah.bill_time &lt;= #{endTime}
            </if>
            <if test="forceFlag">
                and ah.status = '1'
            </if>
            and ifnull(ah.delete_flag,'0') !='1') accountSumByHead,
            (select ifnull(sum(ai.each_amount),0) from jsh_account_head ah
            left join jsh_account_item ai on ah.id = ai.header_id and ifnull(ai.delete_flag,'0') !='1'
            where 1=1 and ai.account_id = a.id
            <if test="beginTime != null">
                and ah.bill_time >= #{beginTime}
            </if>
            <if test="endTime != null">
                and ah.bill_time &lt;= #{endTime}
            </if>
            <if test="forceFlag">
                and ah.status = '1'
            </if>
            and ifnull(ah.delete_flag,'0') !='1') accountSumByDetail
        from jsh_account a
        where 1=1
        <if test="name != null">
            <bind name="bindName" value="'%'+name+'%'"/>
            and a.name like #{bindName}
        </if>
        <if test="serialNo != null and serialNo !=''">
            <bind name="bindSerialNo" value="'%'+serialNo+'%'"/>
            and a.serial_no like #{bindSerialNo}
        </if>
        and ifnull(a.delete_flag,'0') !='1'
        order by a.sort asc, a.id desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>

    <select id="getManyAccountSumByParam" resultType="com.jsh.erp.datasource.entities.DepotHead">
        select dh.account_id_list accountIdList, dh.account_money_list accountMoneyList
        from jsh_depot_head dh
        where 1=1
        and dh.account_id_list !=''
        <if test="beginTime != null">
            and dh.oper_time >= #{beginTime}
        </if>
        <if test="endTime != null">
            and dh.oper_time &lt;= #{endTime}
        </if>
        <if test="forceFlag">
            and (dh.status = '1' or dh.status = '2' or dh.status = '3')
        </if>
        and ifnull(dh.delete_flag,'0') !='1'
    </select>

    <select id="findAccountInOutList" parameterType="com.jsh.erp.datasource.entities.AccountExample" resultMap="ResultInOutList">
        <!--主表出入库涉及的账户 -->
        select dh.number,concat(dh.sub_type,dh.type) as newType, 'bill' as fromType, s.supplier,dh.change_amount,date_format(dh.oper_time,'%Y-%m-%d %H:%i:%S') as oTime,'' as AList,'' as AMList
        from jsh_depot_head dh left join jsh_supplier s on dh.organ_id = s.id and ifnull(s.delete_flag,'0') !='1'
        where 1=1
        <if test="accountId != null">
            and dh.account_id=#{accountId}
        </if>
        <if test="number != null and number !=''">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and dh.number like #{bindNumber}
        </if>
        <if test="beginTime != null and beginTime !=''">
            and dh.oper_time >= #{beginTime}
        </if>
        <if test="endTime != null and endTime !=''">
            and dh.oper_time &lt;= #{endTime}
        </if>
        <if test="forceFlag">
            and (dh.status = '1' or dh.status = '2' or dh.status = '3')
        </if>
        and ifnull(dh.change_amount, 0)!=0
        and ifnull(dh.delete_flag,'0') !='1'
        <!--主表收入和支出涉及的账户 -->
        UNION ALL
        select ah.bill_no,ah.type as newType, 'financial' as fromType, s.supplier,ah.change_amount,date_format(ah.bill_time,'%Y-%m-%d %H:%i:%S') as oTime,'' as AList,'' as AMList
        from jsh_account_head ah left join jsh_supplier s on ah.organ_id=s.id and ifnull(s.delete_flag,'0') !='1'
        where ah.type!='转账'
        <if test="accountId != null">
            and ah.account_id=#{accountId}
        </if>
        <if test="number != null and number !=''">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and ah.bill_no like #{bindNumber}
        </if>
        <if test="beginTime != null and beginTime !=''">
            and ah.bill_time >= #{beginTime}
        </if>
        <if test="endTime != null and endTime !=''">
            and ah.bill_time &lt;= #{endTime}
        </if>
        <if test="forceFlag">
            and ah.status = '1'
        </if>
        and ifnull(ah.change_amount, 0)!=0
        and ifnull(ah.delete_flag,'0') !='1'
        <!--明细中涉及的账户（收款,付款,收预付款） -->
        UNION ALL
        select ah.bill_no,ah.type as newType, 'financial' as fromType, s.supplier,ai.each_amount change_amount,date_format(ah.bill_time,'%Y-%m-%d %H:%i:%S') as oTime,'' as AList,'' as AMList
        from jsh_account_head ah left join jsh_supplier s on ah.organ_id=s.id
        left join jsh_account_item ai on ai.header_id=ah.id and ifnull(ai.delete_flag,'0') !='1'
        where ah.type in ('收款','付款','收预付款')
        <if test="accountId != null">
            and ai.account_id=#{accountId}
        </if>
        <if test="number != null and number !=''">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and ah.bill_no like #{bindNumber}
        </if>
        <if test="beginTime != null and beginTime !=''">
            and ah.bill_time >= #{beginTime}
        </if>
        <if test="endTime != null and endTime !=''">
            and ah.bill_time &lt;= #{endTime}
        </if>
        <if test="forceFlag">
            and ah.status = '1'
        </if>
        and ifnull(ai.each_amount, 0)!=0
        and ifnull(ah.delete_flag,'0') !='1'
        <!--主表中转出的账户 -->
        UNION ALL
        select ah.bill_no,ah.type as newType, 'financial' as fromType, '' as sName,ah.change_amount,date_format(ah.bill_time,'%Y-%m-%d %H:%i:%S') as oTime,'' as AList,'' as AMList
        from jsh_account_head ah
        where ah.type='转账'
        <if test="accountId != null">
            and ah.account_id=#{accountId}
        </if>
        <if test="number != null and number !=''">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and ah.bill_no like #{bindNumber}
        </if>
        <if test="beginTime != null and beginTime !=''">
            and ah.bill_time >= #{beginTime}
        </if>
        <if test="endTime != null and endTime !=''">
            and ah.bill_time &lt;= #{endTime}
        </if>
        <if test="forceFlag">
            and ah.status = '1'
        </if>
        and ifnull(ah.change_amount, 0)!=0
        and ifnull(ah.delete_flag,'0') !='1'
        <!--明细中被转入的账户 -->
        UNION ALL
        select ah.bill_no,ah.type as newType, 'financial' as fromType, '' as sName,ai.each_amount change_amount,date_format(ah.bill_time,'%Y-%m-%d %H:%i:%S') as oTime,'' as AList,'' as AMList
        from jsh_account_head ah left join jsh_account_item ai on ai.header_id=ah.id and ifnull(ai.delete_flag,'0') !='1'
        where ah.type='转账'
        <if test="accountId != null">
            and ai.account_id=#{accountId}
        </if>
        <if test="number != null and number !=''">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and ah.bill_no like #{bindNumber}
        </if>
        <if test="beginTime != null and beginTime !=''">
            and ah.bill_time >= #{beginTime}
        </if>
        <if test="endTime != null and endTime !=''">
            and ah.bill_time &lt;= #{endTime}
        </if>
        <if test="forceFlag">
            and ah.status = '1'
        </if>
        and ifnull(ai.each_amount, 0)!=0
        and ifnull(ah.delete_flag,'0') !='1'
        <!--多账户的情况 -->
        UNION ALL
        select dh.number,concat(dh.sub_type,dh.type) as newType, 'bill' as fromType, s.supplier,dh.change_amount,date_format(dh.oper_time,'%Y-%m-%d %H:%i:%S') as oTime,
        dh.account_id_list as AList,dh.account_money_list as AMList
        from jsh_depot_head dh left join jsh_supplier s on dh.organ_id = s.id and ifnull(s.delete_flag,'0') !='1'
        where 1=1
        <if test="accountId != null">
            <bind name="bindAccountId" value="'%'+accountId+'%'"/>
            and dh.account_id_list like #{bindAccountId}
        </if>
        <if test="number != null and number !=''">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and dh.number like #{bindNumber}
        </if>
        <if test="beginTime != null and beginTime !=''">
            and dh.oper_time >= #{beginTime}
        </if>
        <if test="endTime != null and endTime !=''">
            and dh.oper_time &lt;= #{endTime}
        </if>
        <if test="forceFlag">
            and (dh.status = '1' or dh.status = '2' or dh.status = '3')
        </if>
        and ifnull(dh.change_amount, 0)!=0
        and ifnull(dh.delete_flag,'0') !='1'
        ORDER BY oTime desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>

    <select id="findAccountInOutListCount" resultType="java.lang.Integer">
        select sum(a) from
        (
            <!--主表出入库涉及的账户 -->
            select count(1) a
            from jsh_depot_head dh left join jsh_supplier s on dh.organ_id = s.id and ifnull(s.delete_flag,'0') !='1'
            where 1=1
            <if test="accountId != null">
                and dh.account_id=#{accountId}
            </if>
            <if test="number != null and number !=''">
                <bind name="bindNumber" value="'%'+number+'%'"/>
                and dh.number like #{bindNumber}
            </if>
            <if test="beginTime != null and beginTime !=''">
                and dh.oper_time >= #{beginTime}
            </if>
            <if test="endTime != null and endTime !=''">
                and dh.oper_time &lt;= #{endTime}
            </if>
            <if test="forceFlag">
                and (dh.status = '1' or dh.status = '2' or dh.status = '3')
            </if>
            and ifnull(dh.change_amount, 0)!=0
            and ifnull(dh.delete_flag,'0') !='1'
            <!--主表收入和支出涉及的账户 -->
            UNION ALL
            select count(1) a
            from jsh_account_head ah left join jsh_supplier s on ah.organ_id=s.id and ifnull(s.delete_flag,'0') !='1'
            where ah.type!='转账'
            <if test="accountId != null">
                and ah.account_id=#{accountId}
            </if>
            <if test="number != null and number !=''">
                <bind name="bindNumber" value="'%'+number+'%'"/>
                and ah.bill_no like #{bindNumber}
            </if>
            <if test="beginTime != null and beginTime !=''">
                and ah.bill_time >= #{beginTime}
            </if>
            <if test="endTime != null and endTime !=''">
                and ah.bill_time &lt;= #{endTime}
            </if>
            <if test="forceFlag">
                and ah.status = '1'
            </if>
            and ifnull(ah.change_amount, 0)!=0
            and ifnull(ah.delete_flag,'0') !='1'
            <!--明细中涉及的账户（收款,付款,收预付款） -->
            UNION ALL
            select count(1) a
            from jsh_account_head ah left join jsh_supplier s on ah.organ_id=s.id
            left join jsh_account_item ai on ai.header_id=ah.Id and ifnull(ai.delete_flag,'0') !='1'
            where ah.Type in ('收款','付款','收预付款')
            <if test="accountId != null">
                and ai.account_id=#{accountId}
            </if>
            <if test="number != null and number !=''">
                <bind name="bindNumber" value="'%'+number+'%'"/>
                and ah.bill_no like #{bindNumber}
            </if>
            <if test="beginTime != null and beginTime !=''">
                and ah.bill_time >= #{beginTime}
            </if>
            <if test="endTime != null and endTime !=''">
                and ah.bill_time &lt;= #{endTime}
            </if>
            <if test="forceFlag">
                and ah.status = '1'
            </if>
            and ifnull(ai.each_amount, 0)!=0
            and ifnull(ah.delete_flag,'0') !='1'
            <!--主表中转出的账户 -->
            UNION ALL
            select count(1) a
            from jsh_account_head ah
            where ah.type='转账'
            <if test="accountId != null">
                and ah.account_id=#{accountId}
            </if>
            <if test="number != null and number !=''">
                <bind name="bindNumber" value="'%'+number+'%'"/>
                and ah.bill_no like #{bindNumber}
            </if>
            <if test="beginTime != null and beginTime !=''">
                and ah.bill_time >= #{beginTime}
            </if>
            <if test="endTime != null and endTime !=''">
                and ah.bill_time &lt;= #{endTime}
            </if>
            <if test="forceFlag">
                and ah.status = '1'
            </if>
            and ifnull(ah.change_amount, 0)!=0
            and ifnull(ah.delete_flag,'0') !='1'
            <!--明细中被转入的账户 -->
            UNION ALL
            select count(1) a
            from jsh_account_head ah left join jsh_account_item ai on ai.header_id=ah.id and ifnull(ai.delete_flag,'0') !='1'
            where ah.type='转账'
            <if test="accountId != null">
                and ai.account_id=#{accountId}
            </if>
            <if test="number != null and number !=''">
                <bind name="bindNumber" value="'%'+number+'%'"/>
                and ah.bill_no like #{bindNumber}
            </if>
            <if test="beginTime != null and beginTime !=''">
                and ah.bill_time >= #{beginTime}
            </if>
            <if test="endTime != null and endTime !=''">
                and ah.bill_time &lt;= #{endTime}
            </if>
            <if test="forceFlag">
                and ah.status = '1'
            </if>
            and ifnull(ai.each_amount, 0)!=0
            and ifnull(ah.delete_flag,'0') !='1'
            <!--多账户的情况 -->
            UNION ALL
            select count(1) a
            from jsh_depot_head dh left join jsh_supplier s on dh.organ_id = s.id and ifnull(s.delete_flag,'0') !='1'
            where 1=1
            <if test="accountId != null">
                <bind name="bindAccountId" value="'%'+accountId+'%'"/>
                and dh.account_id_list like #{bindAccountId}
            </if>
            <if test="number != null and number !=''">
                <bind name="bindNumber" value="'%'+number+'%'"/>
                and dh.number like #{bindNumber}
            </if>
            <if test="beginTime != null and beginTime !=''">
                and dh.oper_time >= #{beginTime}
            </if>
            <if test="endTime != null and endTime !=''">
                and dh.oper_time &lt;= #{endTime}
            </if>
            <if test="forceFlag">
                and (dh.status = '1' or dh.status = '2' or dh.status = '3')
            </if>
            and ifnull(dh.change_amount, 0)!=0
            and ifnull(dh.delete_flag,'0') !='1'
        ) cc
    </select>

    <update id="batchDeleteAccountByIds">
        update jsh_account
        set delete_flag='1'
        where 1=1
        and id in (
        <foreach collection="ids" item="id" separator=",">
            #{id}
        </foreach>
        )
    </update>
</mapper>