<?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.yeejoin.amos.boot.module.hygf.api.mapper.AcceptanceMapper">


    <select id="page" resultType="com.yeejoin.amos.boot.module.hygf.api.dto.AcceptanceDto">
        SELECT
            bga.sequence_nbr AS sequenceNbr,
            ph.peasant_household_no AS powerStationCode,
            ph.developer_name as serviceAgent,
            ph.owners_name AS ownersName,
            ph.project_address_name AS projectAddressName,
            bga.acceptance_rec_date AS acceptanceRecDate,

            CASE WHEN bga.acceptance_status  = '1' THEN '待提交验收'
            WHEN bga.acceptance_status  = '2' THEN '待投融验收'
            WHEN bga.acceptance_status  = '3' THEN '投融验收不通过'
            WHEN bga.acceptance_status  = '4' THEN '待法务验收'
            WHEN bga.acceptance_status  = '5' THEN '法务验收不通过'
            WHEN bga.acceptance_status  = '6' THEN '待工程验收'
            WHEN bga.acceptance_status  = '7' THEN '工程验收不通过'
            WHEN bga.acceptance_status  = '8' THEN '待线下验收'
            WHEN bga.acceptance_status  = '9' THEN '线下验收不通过'
            WHEN bga.acceptance_status  = '11' THEN '是否线下验收'

            ELSE '验收完成' END AS acceptanceStatus,

            bga.work_order_id workOrderId,
            bga.work_order_power_station_id workOrderPowerStationId,
            bga.peasant_household_id peasantHouseholdId,
            bga.acceptance_rec_date acceptanceRecDate,
            bga.instance_id instanceId,
            bga.next_executor_ids nextExecutorIds,
            bga.next_node_key  nextNodeKey,

        (select  hygf_acceptance_check_item.sequence_nbr from hygf_acceptance_check_item where hygf_acceptance_check_item.work_order_power_station_id=bga.work_order_power_station_id LIMIT 1) acceptanceCheckItemId,

            hygf_work_order.region_company_id   regionCompanyId,
            ph.regional_companies_code AS regionalCompaniesCode,
            ph.regional_companies_name AS regionalCompaniesName,
            ifnull((
                SELECT count(1) FROM hygf_acceptance_rectification_order aro WHERE aro.peasant_household_id = bga.peasant_household_id
            ), 0) AS rectificationOrderCount,
            (
               SELECT
                CASE WHEN aro.rectification_status  = '1' THEN '待整改'
                WHEN aro.rectification_status  = '2' THEN '整改未完成'
                WHEN aro.rectification_status  = '3' THEN '整改已完成'
                ELSE '' END AS rectificationStatus
               FROM hygf_acceptance_rectification_order aro WHERE aro.peasant_household_id = bga.peasant_household_id ORDER BY rec_date DESC LIMIT 1
            ) AS rectificationStatus
        FROM
            hygf_basic_grid_acceptance bga
                LEFT JOIN hygf_peasant_household ph ON ph.sequence_nbr = bga.peasant_household_id
              LEFT JOIN hygf_work_order  ON hygf_work_order.sequence_nbr = bga.work_order_id
        <where>
            bga.is_delete = 0
            AND bga.acceptance_status IS NOT NULL AND bga.acceptance_status != ''
            <if test="param != null">
                <if test="param.sequenceNbr != null and param.sequenceNbr != ''">
                    AND bga.sequence_nbr = #{param.sequenceNbr}
                </if>
                <if test="param.projectAddressName != null and param.projectAddressName != ''">
                    AND ph.project_address_name LIKE CONCAT('%', #{param.projectAddressName}, '%')
                </if>
                <if test="param.serviceAgent != null and param.serviceAgent != ''">
                    AND ph.developer_name LIKE CONCAT('%', #{param.serviceAgent}, '%')
                </if>
                <if test="param.powerStationCode != null and param.powerStationCode != ''">
                    AND ph.peasant_household_no LIKE CONCAT('%', #{param.powerStationCode}, '%')
                </if>
                <if test="param.acceptanceStatus != null and param.acceptanceStatus != ''">
                    AND bga.acceptance_status = #{param.acceptanceStatus}
                </if>
                <if test="param.startSubmitDate != null and param.startSubmitDate != ''">
                    AND bga.acceptance_rec_date &gt;= #{param.startSubmitDate}
                </if>
                <if test="param.endSubmitDate != null and param.endSubmitDate != ''">
                    AND bga.acceptance_rec_date &lt;= #{param.endSubmitDate}
                </if>

                <if test="param.amosDealerId!=null and param.amosDealerId!=''">
                    and hygf_work_order.amos_dealer_id = #{param.amosDealerId}
                </if>
                <if test="param.regionCompanyId!=null and param.regionCompanyId!=''">
                    and hygf_work_order.region_company_id =#{param.regionCompanyId}
                </if>


            </if>
        </where>
        ORDER BY
         bga.acceptance_rec_date DESC,bga.rec_date desc
    </select>
</mapper>
