<?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.sunxung.factoring.mapper.acceptancesheet.AcceptanceBasicInfoMapper">

    <resultMap type="AcceptanceBasicInfo" id="listVoResult">
        <result property="id" column="id" />
        <result property="moduleNumber" column="acceptance_number" />
        <result property="projectName" column="project_name" />
        <result property="customerName" column="customerName" />
        <result property="projectName" column="project_name" />
        <result property="supplierName" column="supplierName" />
        <result property="expectedSettlementDate" column="expected_settlement_date" />
        <result property="gmtModified" column="gmt_modified" />
        <result property="acceptanceStatus" column="acceptance_status"/>

<!--        <association property="acceptanceStatus" column="acceptance_status" resultMap="dictionaryResult"/>-->

    </resultMap>

    <resultMap type="com.sunxung.factoring.entity.project.vo.BusinessProcessingTaskVo" id="baseResultMap">
        <result property="id" column="id" />
        <result property="moduleNumber" column="acceptance_number" />
        <result property="businessNumber" column="business_number" />
        <result property="customerName" column="customerName" />
        <result property="platformName" column="platform_name"/>
        <result property="projectName" column="project_name" />
        <result property="businessName" column="business_name" />
        <result property="supplierName" column="supplierName" />
        <result property="expectedSettlementTime" column="expected_settlement_date" />
        <result property="gmtModified" column="gmt_modified" />
        <result property="statusStr"  column="acceptance_status"/>
        <association property="status" column="acceptance_status" resultMap="dictionaryResult" />
    </resultMap>

    <resultMap id="dictionaryResult" type="Dictionary">
        <result property="id" column="did" />
        <result property="name" column="dname" />
        <result property="code" column="dcode" />
    </resultMap>


    <select id="getListPage" parameterType="java.lang.Long" resultMap="listVoResult">
        select DISTINCT
            t1.id,
            t1.acceptance_number,
            t1.business_number,
            t4.corporate_name customerName,
            t5.project_name ,
            t6.platform_name tradingPlatformName,
            t2.corporate_name supplierName,
            t1.expected_settlement_date ,
            t1.acceptance_status,
            t1.gmt_modified ,
            t3.id did,
            t3.code dcode,
            t3.name dname
        from c_acceptance_basic_info t1
                 LEFT JOIN c_enterprise t2 on t1.c_supplier_info_id = t2.id
                 LEFT JOIN t_dictionary t3 ON t1.acceptance_status = t3.id
                 LEFT JOIN c_enterprise t4 on t1.c_project_core_enterprise_id = t4.id
                 LEFT JOIN c_project_information t5 on t1.business_number = t5.business_number
                 LEFT JOIN trading_platform t6 on t6.id = t1.trading_platform_id
        where t1.id = #{id}
        ORDER BY t1.gmt_modified DESC
    </select>

    <select id="findMyCompletedList" parameterType="com.sunxung.factoring.entity.project.vo.SearchBusinessProcessingTask" resultMap="baseResultMap">
        SELECT DISTINCT
        t2.id,
        t2.acceptance_number ,
        t2.business_number,
        t5.enterprise_name customerName,
        t3.business_name ,
        t4.enterprise_name supplierName,
        t2.expected_settlement_date ,
        t2.acceptance_status ,
        t2.gmt_modified ,
        t6.id did,
        t6.code dcode,
        t6.name dname
        FROM business_processing_log t1
                LEFT JOIN c_acceptance_basic_info t2 ON t1.busness_key = t2.id
                LEFT JOIN business_basic_info t3 ON t2.business_number = t3.business_number
                LEFT JOIN enterprise_customer_info t4 ON t2.supplier_id = t4.id
                LEFT JOIN enterprise_customer_info t5 ON t2.enterprise_customer_info_id = t5.id
                LEFT JOIN t_dictionary t6 ON t2.acceptance_status = t6.id
        <where>
            t1.business_type = 'AcceptanceSheet' and t2.id is not null
            AND t1.end_time IS NOT NULL
            <if test="userId != null">
                AND t1.assignee_id = #{userId}
            </if>
            <if test="fastSearch != null and fastSearch !=''">
                AND (t2.acceptance_number LIKE concat('%',#{fastSearch},'%') OR t2.supplier_id in (select id from enterprise_customer_info where enterprise_name like concat('%',#{fastSearch},'%') and supplier ="1" )
                OR t2.enterprise_customer_info_id in (select id from enterprise_customer_info where enterprise_name like concat('%',#{fastSearch},'%') and supplier ="0" ) OR t3.business_number like concat('%',#{fastSearch},'%')
                OR t3.business_name like concat('%',#{fastSearch},'%')  )
            </if>
        </where>
        GROUP BY t1.busness_key,t2.gmt_create
        ORDER BY t2.acceptance_status ASC,t2.gmt_modified DESC
    </select>

    <select id="findAllList" parameterType="com.sunxung.factoring.entity.project.vo.SearchBusinessProcessingTask" resultMap="baseResultMap">
        SELECT DISTINCT
        t2.id,
        t2.acceptance_number,
        t2.business_number,
        t7.platform_name,
        t8.project_name ,
        t5.corporate_name customerName,
        t4.corporate_name supplierName,
        t2.expected_settlement_date ,
        t2.acceptance_status ,
        t2.gmt_modified ,
        t6.id did,
        t6.code dcode,
        t6.name dname
        FROM business_processing_log t1
        LEFT JOIN c_acceptance_basic_info t2 ON t1.busness_key = t2.id
        LEFT JOIN c_enterprise t4 ON t2.c_supplier_info_id = t4.id
        LEFT JOIN c_enterprise t5 ON t2.c_project_core_enterprise_id = t5.id
        LEFT JOIN t_dictionary t6 ON t2.acceptance_status = t6.code
        LEFT JOIN trading_platform t7 on t7.id = t2.trading_platform_id
        LEFT JOIN c_project_information t8 on t2.business_number = t8.business_number
        <where>
            t1.business_type  like "AcceptanceSheet%" and t2.id is not null
            <if test="userIds != null and userIds.size()>0">
                AND t1.assignee_id in
                <foreach collection="userIds" index="index" item="item"  open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="fastSearch != null and fastSearch !=''">
                AND (     t8.project_name like CONCAT('%',#{fastSearch},'%')
                       or t5.corporate_name like CONCAT('%',#{fastSearch},'%')
                       or t4.corporate_name  like CONCAT('%',#{fastSearch},'%')
                    )
            </if>
            <if test="startDate != null">
                <![CDATA[ AND DATE_FORMAT(t2.expected_settlement_date, '%Y-%m-%d')>=  DATE_FORMAT(#{startDate}, '%Y-%m-%d') ]]>
            </if>
            <if test="endDate != null">
                <![CDATA[ AND DATE_FORMAT(t2.expected_settlement_date, '%Y-%m-%d')<=  DATE_FORMAT(#{endDate}, '%Y-%m-%d') ]]>
            </if>
            <if test="status != null and status !=''  ">
                AND t2.acceptance_status = #{status}
            </if>
            <if test="searchNumber !=null and searchNumber !='' ">
                AND t2.business_number like CONCAT('%',#{searchNumber},'%')
            </if>
        </where>
        GROUP BY t1.busness_key,t2.gmt_create
        ORDER BY t2.acceptance_status ASC,t2.gmt_modified DESC
    </select>
    <select id="findGoodsBySettlement" resultType="com.sunxung.factoring.entity.settlement.SettlementBasicInfo">
        SELECT DISTINCT
            t1.*
        FROM
            settlement_basic_info t1
                LEFT JOIN settlement_goods_info t2 ON t1.id = t2.settlement_basic_info_id
                LEFT JOIN acceptance_basic_info t3 ON t3.id = t2.acceptance_basic_info_id
        WHERE
            t3.is_settlement = 1
          AND t1.supplier_id = #{supplierId}
          AND t3.is_receivables = 0
          AND t1.business_number = #{businessNumber}
          AND t1.settlement_status IN (SELECT t4.id FROM t_dictionary t4 WHERE t4.code IN ('bill_sign','bill_need_sign'))
    </select>
    <select id="getCanBeBillGoodsInformation"
            resultType="com.sunxung.factoring.entity.acceptance.AcceptanceBasicInfo">
        select
               t1.*
        from
             c_acceptance_basic_info t1
        where
            t1.business_number = #{businessNumber}
        and t1.supplier_id = #{supplierId}
        and t1.is_settlement = false
        and t1.acceptance_status = #{status}

    </select>
    <select id="findAllListSupplier"
            resultType="com.sunxung.factoring.entity.project.vo.BusinessProcessingTaskVo" resultMap="baseResultMap">
        SELECT DISTINCT
        t2.id,
        t2.acceptance_number,
        t2.business_number,
        t5.enterprise_name customerName,
        t3.business_name ,
        t4.enterprise_name supplierName,
        t2.expected_settlement_date ,
        t2.acceptance_status ,
        t2.gmt_modified ,
        t6.id did,
        t6.code dcode,
        t6.name dname
        FROM business_processing_log t1
        LEFT JOIN c_acceptance_basic_info t2 ON t1.busness_key = t2.id
        LEFT JOIN business_basic_info t3 ON t2.business_number = t3.business_number
        LEFT JOIN enterprise_customer_info t4 ON t2.supplier_id = t4.id
        LEFT JOIN enterprise_customer_info t5 ON t2.enterprise_customer_info_id = t5.id
        LEFT JOIN t_dictionary t6 ON t2.acceptance_status = t6.id
        <where>
            t1.business_type = "AcceptanceSheet"
            and t2.supplier_id = #{supplierId}
            <if test="fastSearch != null and fastSearch !=''">
                AND (t2.acceptance_number LIKE concat('%',#{fastSearch},'%') OR t2.supplier_id in (select id from enterprise_customer_info where enterprise_name like concat('%',#{fastSearch},'%') and supplier ="1" )
                OR t2.enterprise_customer_info_id in (select id from enterprise_customer_info where enterprise_name like concat('%',#{fastSearch},'%') and supplier ="0" ) OR t3.business_number like concat('%',#{fastSearch},'%')
                OR t3.business_name like concat('%',#{fastSearch},'%')  )
            </if>
            <if test="startDate != null">
                <![CDATA[ AND DATE_FORMAT(t2.expected_settlement_date, '%Y-%m-%d')>=  DATE_FORMAT(#{startDate}, '%Y-%m-%d') ]]>
            </if>
            <if test="endDate != null">
                <![CDATA[ AND DATE_FORMAT(t2.expected_settlement_date, '%Y-%m-%d')<=  DATE_FORMAT(#{endDate}, '%Y-%m-%d') ]]>
            </if>
            <if test="statusId != null">
                AND t2.acceptance_status = #{statusId}
            </if>
        </where>
        GROUP BY t1.busness_key,t2.gmt_create
        ORDER BY t2.gmt_modified DESC
    </select>


    <select id="getSpecialOneById"
      resultType="com.sunxung.factoring.entity.acceptance.AcceptanceBasicInfo"
      parameterType="java.lang.Long">
          select  * from c_acceptance_basic_info where id = #{id}

    </select>
</mapper>
