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

   <select id="selectWorkOrderList"  resultType="com.yeejoin.amos.boot.module.hygf.api.entity.WorkOrder" >

     SELECT
	 hygf_work_order.*,
	 (SELECT count(*) from  hygf_work_order_power_station where hygf_work_order_power_station.work_order_id = hygf_work_order.sequence_nbr ) peasantHouseholdNum,

       (SELECT count(*) from  hygf_work_order_power_station where hygf_work_order_power_station.work_order_id = hygf_work_order.sequence_nbr

       and hygf_work_order_power_station.power_station_construction_status in ("待登记","未通过","已完工","待审核")

       ) peasantHouseholdSgNum

       FROM  hygf_work_order
       WHERE hygf_work_order.is_delete=0
       <if test="dto.workOrderNum!=null and dto.workOrderNum!=''">
           and hygf_work_order.work_order_num like concat(concat("%",#{dto.workOrderNum}),"%")
       </if>
       <if test="dto.projectRegionManager!=null and dto.projectRegionManager!=''">
           and hygf_work_order.project_region_manager like concat(concat("%",#{dto.projectRegionManager}),"%")
       </if>
       <if test="dto.workOrderStatus!=null and dto.workOrderStatus!=''">
           and hygf_work_order.work_order_status=#{dto.workOrderStatus}
       </if>
       <if test="dto.amosDealerId!=null and dto.amosDealerId!=''">
           and hygf_work_order.amos_dealer_id = #{dto.amosDealerId}
       </if>
       <if test="dto.regionCompanyId!=null and dto.regionCompanyId!=''">
           and hygf_work_order.region_company_id =#{dto.regionCompanyId}
       </if>
       ORDER BY
       FIELD(hygf_work_order.work_order_status, '待施工', '施工中', '待登记', '登记中', '待审核', '审核中', '未通过', '已完工'),

       hygf_work_order.rec_date DESC

   </select>

    <select id="queryForwgpage"  resultType="com.yeejoin.amos.boot.module.hygf.api.dto.WorkOrderPage" >

        select
        hygf_peasant_household.sequence_nbr sequenceNbr,
        hygf_peasant_household.peasant_household_no peasantHouseholdNo,
        hygf_work_order.work_order_num workOrderNum,
        hygf_peasant_household.owners_name ownersName,
        hygf_work_order.project_region_manager projectRegionManager,
        hygf_work_order.construction_region_manager constructionRegionManager,
        hygf_power_station_engineering_info.completion_date completionDate ,
        hygf_peasant_household.project_address_name projectAddressName,
        hygf_work_order_power_station.power_station_construction_status powerStationConstructionStatus,
        hygf_work_order_power_station.sequence_nbr  workOrderPowerStationId
        from   hygf_work_order_power_station LEFT join hygf_work_order
        on hygf_work_order.sequence_nbr=hygf_work_order_power_station.work_order_id
        LEFT JOIN hygf_peasant_household on  hygf_peasant_household.sequence_nbr=hygf_work_order_power_station.peasant_household_id
        LEFT JOIN hygf_power_station_engineering_info on hygf_power_station_engineering_info.work_order_power_station_id=hygf_work_order_power_station.sequence_nbr

    <where>

        <if test="dto.workOrderId!=null and dto.workOrderId!=''">
            and hygf_work_order.sequence_nbr = #{dto.workOrderId}
        </if>
        <if test=' dto.type!=null and dto.type == "1"'>
            and  hygf_work_order_power_station.power_station_construction_status in ("待登记","未通过","已完工","待审核")
        </if>

        <if test=' dto.type !=null and dto.type == "2"'>
            and  hygf_work_order_power_station.power_station_construction_status in ("未通过","已完工","待审核")
        </if>

        <if test="dto.projectAddressName!=null and dto.projectAddressName!=''">
            and hygf_peasant_household.project_address_name like concat(concat("%",#{dto.projectAddressName}),"%")
        </if>

        <if test="dto.peasantHouseholdNo!=null and dto.peasantHouseholdNo!=''">
            and hygf_peasant_household.peasant_household_no like concat(concat("%",#{dto.peasantHouseholdNo}),"%")
        </if>

        <if test="dto.ownersName!=null and dto.ownersName!=''">
            and hygf_peasant_household.owners_name like concat(concat("%",#{dto.ownersName}),"%")
        </if>

        <if test="dto.workOrderNum!=null and dto.workOrderNum!=''">
            and hygf_work_order.work_order_num like concat(concat("%",#{dto.workOrderNum}),"%")
        </if>

        <if test="dto.projectRegionManager!=null and dto.projectRegionManager!=''">
            and hygf_work_order.project_region_manager like concat(concat("%",#{dto.projectRegionManager}),"%")
        </if>
        <if test="dto.powerStationConstructionStatus!=null and dto.powerStationConstructionStatus!=''">
            and hygf_work_order_power_station.power_station_construction_status=#{dto.powerStationConstructionStatus}
        </if>

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

        FIELD(hygf_work_order_power_station.power_station_construction_status, '待施工', '施工中', '待登记', '登记中', '待审核', '审核中', '未通过', '已完工'),

        hygf_work_order_power_station.rec_date DESC
    </where>

    </select>

</mapper>
