<?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.testor.module.contractor.ledger.dao.TContractorLedgerDao">
    <!-- 开启二级缓存 -->
    <!-- <cache type="org.mybatis.caches.ehcache.LoggingEhcache"/> -->
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.testor.module.contractor.ledger.model.domain.TContractorLedger">
                <id column="id" property="id"/>
                <id column="name" property="name"/>
                <id column="credit_code" property="creditCode"/>
                <id column="type_id" property="typeId"/>
                <id column="type" property="type"/>
                <id column="biz_nature_id" property="bizNatureId"/>
                <id column="biz_nature" property="bizNature"/>
                <id column="reg_capital" property="regCapital"/>
                <id column="province" property="province"/>
                <id column="city" property="city"/>
                <id column="addr" property="addr"/>
                <id column="legal_person" property="legalPerson"/>
                <id column="tel" property="tel"/>
                <id column="reg_date" property="regDate"/>
                <id column="valid_period" property="validPeriod"/>
                <id column="biz_scope" property="bizScope"/>
                <id column="safety_official" property="safetyOfficial"/>
                <id column="safety_official_tel" property="safetyOfficialTel"/>
                <id column="tech_leader" property="techLeader"/>
                <id column="tech_leader_tel" property="techLeaderTel"/>
                <id column="org_id" property="orgId"/>
                <id column="safety_prod_license" property="safetyProdLicense"/>
                <id column="safety_prod_agreement" property="safetyProdAgreement"/>
                <id column="license" property="license"/>
                <id column="accident_free_record" property="accidentFreeRecord"/>
                <id column="man_policies" property="manPolicies"/>
    </resultMap>
    <select id="pageList" resultType="com.testor.module.contractor.ledger.model.domain.TContractorLedger">
        <!--select t.* from t_contractor_ledger t left join t_contractor_info tci on t.info_id=tci.id
        where t.org_id IN (select org.org_id from t_sys_org org where org.parent_ids like
        concat('%',#{param.orgId},'%') or org.org_id = #{param.orgId})
          and t.status = '0'-->

        SELECT t.*
        FROM t_contractor_ledger t
        LEFT JOIN t_contractor_info tci ON t.info_id = tci.id
        WHERE EXISTS (
        SELECT 1
        FROM unnest(string_to_array(t.org_id, ',')) AS org(id)
        JOIN t_sys_org o ON org.id = o.org_id
        WHERE
        o.parent_ids LIKE concat('%',#{param.orgId},'%')
        OR o.org_id = #{param.orgId}
        )
        AND t.status = '0'
        <if test="param.name != null and param.name != ''">
            AND tci.name like concat('%',#{param.name},'%')
        </if>
        <if test="param.validPeriodStart != null and param.validPeriodStart != '' and param.validPeriodEnd != null and param.validPeriodEnd != ''">
            AND date_format(tci.valid_period,'%Y-%m-%d') &gt;= date_format(#{param.validPeriodStart},'%Y-%m-%d') AND
            date_format(tci.valid_period,'%Y-%m-%d') &lt;= date_format(#{param.validPeriodEnd},'%Y-%m-%d')
        </if>
        order by t.update_date desc
    </select>
    <select id="findByContractorName"
            resultType="com.testor.module.contractor.ledger.model.domain.TContractorLedger">

    </select>
    <select id="findContractorLedgerNames" resultType="java.lang.String">
        SELECT DISTINCT
            a.name
        FROM
            t_contractor_ledger t
                LEFT JOIN t_contractor_info a ON a.id = t.info_id
        where t.org_id IN ( select org.org_id from t_sys_org org where org.parent_ids like  concat('%',#{param.orgId},'%') or org.org_id = #{param.orgId} )
              and t.status = '0'
    </select>

    <select id="orgIdDistinct" resultType="java.lang.String">
        SELECT string_agg(DISTINCT org_id, ',') AS concatenated_org_ids
        FROM (
                 SELECT unnest(string_to_array(org_id, ',')) AS org_id
                 FROM t_contractor_ledger
                 WHERE org_id like  concat('%',#{orgId},'%')
             ) AS expanded_ids
    </select>


</mapper>