<?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.jxiop.api.mapper.HouseholdPvMapper">

    <select id="selectExportList" resultType="com.yeejoin.amos.boot.module.jxiop.api.dto.HouseholdPvExport">
        SELECT
        hp.sequence_nbr as id,
        hp.platform_company_id AS platformCompanyId,
        hp.number,
        hp.`name`,
        hp.sex,
        hp.ID_number AS idNumber,
        hp.expiry_time AS expiryTime,
        hp.issuing_authority AS issuingAuthority,
        hp.residence_address AS residenceAddress,
        hp.phone,
        hp.address,
        hp.postcode,
        hp.service_address AS serviceAddress,
        hp.district_id AS districtId,
        CASE
        hp.is_permanently
        WHEN 1 THEN
        '是' ELSE '否'
        END AS isPermanently,
        hpc.number AS contactNumber,
        hpc.period,
        hpc.description ,
        hpc.generation_number as generationNumber ,
        hpl.install_address as installAddress,
        hpl.install_date as installDate,
        hpl.estimated_power as estimatedPower,
        hpl.monolithic_power as monolithicPower,
        hpl.actual_capacity as actualCapacity,
        hpl.station_type as stationType,
        hpl.module_type as moduleType,
        hpl.install_count as installCount,
        hpl.inverter_serial_number as inverterSerialNumber,
        hpi.description as installDescription,
        hpi.platform_company_id AS installStation

        FROM
        household_pv hp
        LEFT JOIN household_pv_contact hpc on hp.sequence_nbr = hpc.household_pv_id
        LEFT JOIN  household_pv_lease hpl  on  hp.sequence_nbr = hpl.household_pv_id
        LEFT JOIN  household_pv_install hpi  on  hp.sequence_nbr = hpi.household_pv_id
        <where>
            <if test="dto.name != null and dto.name != ''">
                and hp.name like concat('%',#{dto.name},'%')
            </if>
            <if test="dto.bizOrgCode != null and dto.bizOrgCode != ''">
                and hp.biz_org_code like concat('%',#{dto.bizOrgCode},'%')
            </if>
            <if test="dto.level != null and dto.level != ''">
                and hp.level = #{dto.level}
            </if>
            <if test="dto.residenceAddress != null and dto.residenceAddress != ''">
                and hp.residence_address like concat('%',#{residenceAddress},'%')
            </if>
            <if test="dto.number != null and dto.number != ''">
                and hp.number like concat('%',#{dto.number},'%')
            </if>
            <if test="ids != null and ids.size > 0">
                and hp.sequence_nbr in
                <foreach collection="ids" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
    <select id="selectEquipList" resultType="com.yeejoin.amos.boot.module.jxiop.api.dto.HouseholdPvDeviceDto">
        SELECT * from household_pv_device hpd WHERE hpd.household_pv_id = #{id}
    </select>
    <select id="selectPageMessage" resultType="com.yeejoin.amos.boot.module.jxiop.api.dto.HouseholdPvDto">
        select  hp.*,hpl.install_address as installAddress from  household_pv hp
        LEFT JOIN  household_pv_lease hpl  on  hp.sequence_nbr = hpl.household_pv_id
        <where>
            <if test="dto.name != null and dto.name != ''">
                and hp.name like concat('%',#{dto.name},'%')
            </if>
            <if test="dto.installAddress != null and dto.installAddress != ''">
                and hpl.install_address like concat('%',#{dto.installAddress},'%')
            </if>
            <if test="dto.number != null and dto.number != ''">
                and hp.number like concat('%',#{dto.number},'%')
            </if>
            <if test="dto.level != null and dto.level != ''">
                and hp.level = #{dto.level}
            </if>
            <if test="dto.bizOrgCode != null and dto.bizOrgCode != ''">
                and hp.biz_org_code like concat('%',#{dto.bizOrgCode},'%')
            </if>
        </where>
        order by  hp.rec_date desc
    </select>

    <select id="selectImg" resultType="java.lang.String">
        SELECT
            homepage_photo AS img
        FROM
            household_pv
        WHERE
            sequence_nbr = #{id}
    </select>
    <select id="selectExportEquipMessage"
            resultType="com.yeejoin.amos.boot.module.jxiop.api.dto.EquipMessageExport">
        SELECT
        (@i:= @i+1) as numSort,
        hp.`name`,
        hp.ID_number AS idNumber,
        hpd.type,
        hpd.name as equipName,
        hpd.brand,
        hpd.SN_code as snCode,
        hpd.SN_code as description
        FROM
        (SELECT @i:=0) as i,
        household_pv hp
        left join household_pv_device hpd  on hp.sequence_nbr = hpd.household_pv_id
        <where>
            <if test="dto.name != null and dto.name != ''">
                and hp.name like concat('%',#{dto.name},'%')
            </if>
            <if test="dto.bizOrgCode != null and dto.bizOrgCode != ''">
                and hp.biz_org_code like concat('%',#{dto.bizOrgCode},'%')
            </if>
            <if test="dto.level != null and dto.level != ''">
                and hp.level = #{dto.level}
            </if>
            <if test="dto.number != null and dto.number != ''">
                and hp.number like concat('%',#{dto.number},'%')
            </if>
            <if test="ids != null and ids.size > 0">
                and hp.sequence_nbr in
                <foreach collection="ids" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>


</mapper>
