<?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.PeasantHouseholdMapper">
<select id="selectPeasantHouseholdList" resultType="com.yeejoin.amos.boot.module.hygf.api.entity.PeasantHousehold">

SELECT 	DISTINCT	hygf_peasant_household.sequence_nbr stationId,
    hygf_peasant_household.*
 FROM  hygf_peasant_household LEFT JOIN hygf_household_contract ON hygf_household_contract.peasant_household_id=hygf_peasant_household.sequence_nbr where
hygf_household_contract.`status`='双方已签署'
and hygf_peasant_household.developer_id=#{developerId}
and hygf_peasant_household.regional_companies_seq=#{regionalCompaniesSeq}
and hygf_peasant_household.survey_or_not=3 and  hygf_peasant_household.sequence_nbr not in
(select hygf_document_station.station_id  from hygf_preparation_money LEFT JOIN hygf_document_station
on hygf_document_station.preparation_money_id=hygf_preparation_money.sequence_nbr  where hygf_preparation_money.delivery_state !='已作废'
    <if test="ids!=null and ids !=''">
        and hygf_document_station.preparation_money_id !=#{ids}
    </if>

and hygf_document_station.station_id is not null
)
    <if test="peasantHouseholdNo!=null and peasantHouseholdNo !=''">
        and hygf_peasant_household.peasant_household_no like concat(concat("%",#{peasantHouseholdNo}),"%")
    </if>
    <if test="ownersName!=null and ownersName !='' ">
        and hygf_peasant_household.owners_name like concat(concat("%",#{ownersName}),"%")
    </if>

</select>

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

        SELECT *,
        hygf_peasant_household.sequence_nbr stationId
        FROM  hygf_peasant_household
        where
        hygf_peasant_household.construction_state='备货完成'
        and hygf_peasant_household.developer_id=#{developerId}
        and hygf_peasant_household.regional_companies_seq=#{regionalCompaniesSeq}
        and hygf_peasant_household.sequence_nbr not in (
        select hygf_work_order_power_station.peasant_household_id   from hygf_work_order_power_station
        where 1=1
        <if test="ids!=null and ids !=''">
           and hygf_work_order_power_station.work_order_id !=#{ids}
        </if>
        )
        <if test="peasantHouseholdNo!=null and peasantHouseholdNo !=''">
            and hygf_peasant_household.peasant_household_no like concat(concat("%",#{peasantHouseholdNo}),"%")
        </if>
        <if test="ownersName!=null and ownersName !='' ">
            and hygf_peasant_household.owners_name like concat(concat("%",#{ownersName}),"%")
        </if>

    </select>
    <select id="queryForPage" resultType="com.yeejoin.amos.boot.module.hygf.api.entity.PeasantHousehold">
        SELECT *
        FROM hygf_peasant_household
        WHERE IS_CERTIFIED = #{isCertified}
        <if test="ownersName!=null and ownersName !=''">
            and owners_name like concat ('%',#{ownersName},'%')
        </if>
        <if test="developerId!=null and developerId !=''">
            and developer_id = #{developerId}
        </if>
        <if test="developerCode!=null and developerCode !=''">
            and developer_code = #{developerCode}
        </if>
        ORDER BY rec_date DESC
    </select>
    <select id="selectUserIsHouse" resultType="java.util.Map">
        SELECT
            role.*
        FROM
            privilege_user_org_role role
                INNER JOIN privilege_agency_user `user` ON `user`.USER_ID = role.USER_ID
                AND role.ROLE_SEQ NOT IN ( 1702551322030534658 )
        WHERE
            `user`.MOBILE = #{telephone}
    </select>

</mapper>
