<?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.AccountHeadMapperEx">

    <resultMap extends="com.jsh.erp.datasource.mappers.AccountHeadMapper.BaseResultMap" id="ResultMapEx" type="com.jsh.erp.datasource.entities.AccountHeadVo4ListEx">
        <result column="OrganName" jdbcType="VARCHAR" property="organName" />
        <result column="HandsPersonName" jdbcType="VARCHAR" property="handsPersonName" />
        <result column="userName" jdbcType="VARCHAR" property="userName" />
        <result column="AccountName" jdbcType="VARCHAR" property="accountName" />
    </resultMap>

    <select id="selectByConditionAccountHead" parameterType="com.jsh.erp.datasource.entities.AccountHeadExample" resultMap="ResultMapEx">
        select ah.*, s.supplier OrganName, p.Name HandsPersonName, u.username userName, a.Name AccountName
        from jsh_account_head ah
        left join jsh_account_item ai on ah.id=ai.header_id and ifnull(ai.delete_flag,'0') !='1'
        left join jsh_depot_head dh on dh.id=ai.bill_id and ifnull(dh.delete_flag,'0') !='1'
        left join jsh_supplier s on ah.organ_id=s.id and ifnull(s.delete_Flag,'0') !='1'
        left join jsh_user u on ah.creator=u.id and ifnull(u.Status,'0') !='1'
        left join jsh_person p on ah.hands_person_id=p.id and ifnull(p.delete_Flag,'0') !='1'
        left join jsh_account a on ah.account_id=a.id and ifnull(a.delete_Flag,'0') !='1'
        where 1=1
        <if test="billNo != null">
            <bind name="bindBillNo" value="'%'+billNo+'%'"/>
            and ah.bill_no like #{bindBillNo}
        </if>
        <if test="type != null">
            and ah.type=#{type}
        </if>
        <if test="beginTime != null">
            and ah.bill_time &gt;= #{beginTime}
        </if>
        <if test="endTime != null">
            and ah.bill_time &lt;= #{endTime}
        </if>
        <if test="organId != null">
            and ah.organ_id=#{organId}
        </if>
        <if test="handsPersonId != null">
            and ah.hands_person_id=#{handsPersonId}
        </if>
        <if test="creator != null">
            and ah.creator=#{creator}
        </if>
        <if test="creatorArray != null">
            and ah.creator in (
            <foreach collection="creatorArray" item="creator" separator=",">
                #{creator}
            </foreach>
            )
        </if>
        <if test="accountId != null">
            and ah.account_id=#{accountId}
        </if>
        <if test="status != null and status != ''">
            and ah.status=#{status}
        </if>
        <if test="remark != null and remark !=''">
            <bind name="bindRemark" value="'%'+remark+'%'"/>
            and ah.remark like #{bindRemark}
        </if>
        <if test="number != null and number !=''">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and dh.number like #{bindNumber}
        </if>
        and ifnull(ah.delete_flag,'0') !='1'
        order by ah.id desc
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>


    <select id="countsByAccountHead" resultType="java.lang.Long">
        SELECT
        COUNT(ah.id)
        FROM jsh_account_head ah
        left join jsh_account_item ai on ah.id=ai.header_id and ifnull(ai.delete_flag,'0') !='1'
        left join jsh_depot_head dh on dh.id=ai.bill_id and ifnull(dh.delete_flag,'0') !='1'
        WHERE 1=1
        <if test="billNo != null">
            <bind name="bindBillNo" value="'%'+billNo+'%'"/>
            and ah.bill_no like #{bindBillNo}
        </if>
        <if test="type != null">
            and ah.type=#{type}
        </if>
        <if test="beginTime != null">
            and ah.bill_time &gt;= #{beginTime}
        </if>
        <if test="endTime != null">
            and ah.bill_time &lt;= #{endTime}
        </if>
        <if test="organId != null">
            and ah.organ_id=#{organId}
        </if>
        <if test="handsPersonId != null">
            and ah.hands_person_id=#{handsPersonId}
        </if>
        <if test="creator != null">
            and ah.creator=#{creator}
        </if>
        <if test="creatorArray != null">
            and ah.creator in (
            <foreach collection="creatorArray" item="creator" separator=",">
                #{creator}
            </foreach>
            )
        </if>
        <if test="accountId != null">
            and ah.account_id=#{accountId}
        </if>
        <if test="status != null and status != ''">
            and ah.status=#{status}
        </if>
        <if test="remark != null and remark !=''">
            <bind name="bindRemark" value="'%'+remark+'%'"/>
            and ah.remark like #{bindRemark}
        </if>
        <if test="number != null and number !=''">
            <bind name="bindNumber" value="'%'+number+'%'"/>
            and dh.number like #{bindNumber}
        </if>
        and ifnull(ah.delete_flag,'0') !='1'
    </select>

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

    <select id="getDetailByNumber" parameterType="com.jsh.erp.datasource.entities.AccountHeadExample" resultMap="ResultMapEx">
        select ah.*, s.supplier OrganName, p.Name HandsPersonName, a.Name AccountName
        from jsh_account_head ah
        left join jsh_supplier s on ah.organ_id=s.id and ifnull(s.delete_Flag,'0') !='1'
        left join jsh_person p on ah.hands_person_id=p.id and ifnull(p.delete_Flag,'0') !='1'
        left join jsh_account a on ah.account_id=a.id and ifnull(a.delete_Flag,'0') !='1'
        where 1=1
        <if test="billNo != null">
            and ah.bill_no = #{billNo}
        </if>
        and ifnull(ah.delete_flag,'0') !='1'
    </select>
    <update id="batchDeleteAccountHeadByIds">
        update jsh_account_head
        set delete_flag='1'
        where 1=1
        and id in (
        <foreach collection="ids" item="id" separator=",">
            #{id}
        </foreach>
        )
    </update>
    <select id="getAccountHeadListByAccountIds"  resultMap="com.jsh.erp.datasource.mappers.AccountHeadMapper.BaseResultMap">
        select
        <include refid="com.jsh.erp.datasource.mappers.AccountHeadMapper.Base_Column_List" />
        from jsh_account_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="getAccountHeadListByOrganIds"  resultMap="com.jsh.erp.datasource.mappers.AccountHeadMapper.BaseResultMap">
        select
        <include refid="com.jsh.erp.datasource.mappers.AccountHeadMapper.Base_Column_List" />
        from jsh_account_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="getAccountHeadListByHandsPersonIds"  resultMap="com.jsh.erp.datasource.mappers.AccountHeadMapper.BaseResultMap">
        select
        <include refid="com.jsh.erp.datasource.mappers.AccountHeadMapper.Base_Column_List" />
        from jsh_account_head
        where 1=1
        and hands_person_id in (
        <foreach collection="handsPersonIds" item="handsPersonId" separator=",">
            #{handsPersonId}
        </foreach>
        )
        and ifnull(delete_flag,'0') !='1'
    </select>

    <select id="getFinancialBillNoByBillIdList" resultType="com.jsh.erp.datasource.entities.AccountItem">
        select ai.bill_id 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="idList.size()>0">
            and ai.bill_id in
            <foreach collection="idList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        and ifnull(ah.delete_flag,'0') !='1'
    </select>

    <select id="getFinancialBillNoByBillId" resultType="com.jsh.erp.datasource.entities.AccountHead">
        select ah.bill_no 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 ai.bill_id=#{billId}
        and ifnull(ah.delete_flag,'0') !='1'
    </select>
</mapper>