<?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.manage.dao.TContractorBasicInfoDao">
    <!-- 开启二级缓存 -->
    <!-- <cache type="org.mybatis.caches.ehcache.LoggingEhcache"/> -->
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.testor.module.contractor.manage.model.domain.TContractorBasicInfo">
                <id column="id" property="id"/>
                <id column="process_id" property="processId"/>
                <id column="process_status" property="processStatus"/>
    </resultMap>
    <select id="pageList" resultType="com.testor.module.contractor.manage.model.dto.ContractorInfoDTO"
            databaseId="mysql">
        SELECT
            tcbi.id,
            tci.name as contractorName,
            tcl.biz_nature_id,
            GROUP_CONCAT( tcp.NAME ) as projectNames,
            tci.province,
            tci.city,
            tci.addr,
            tci.reg_date,
            tci.valid_period,
            tcbi.process_status
        FROM
            t_contractor_basic_info AS tcbi
                LEFT JOIN t_contractor_ledger_project AS tclp ON tcbi.id = tclp.biz_id
                LEFT JOIN t_contractor_ledger AS tcl ON tcl.id = tclp.contractor_id
                LEFT JOIN t_contractor_info AS tci ON tci.id = tcl.info_id
                LEFT JOIN t_contractor_project AS tcp ON tcp.id = tclp.project_id
        WHERE
            tcbi.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 tcbi.status = '0'
          <if test="param.contractorName != null and param.contractorName != ''">
                AND tci.name like concat('%',#{param.contractorName},'%')
            </if>
            <if test="param.validityStartDate != null and param.validityEndDate != null">
                AND date_format(tci.valid_period,'%Y-%m-%d') &gt;= date_format(#{param.validityStartDate},'%Y-%m-%d')
                AND date_format(tci.valid_period,'%Y-%m-%d') &lt;= date_format(#{param.validityEndDate},'%Y-%m-%d')
            </if>
        GROUP BY
            tcbi.id,
            tcl.id,
            tcbi.process_status
        order by tcbi.update_date desc
    </select>
    <select id="pageList" resultType="com.testor.module.contractor.manage.model.dto.ContractorInfoDTO"
            databaseId="kingbase">
        SELECT
        tcbi.id,
        tci.name as contractorName,
        tcl.biz_nature_id,
        GROUP_CONCAT( tcp.NAME ) as projectNames,
        tci.province,
        tci.city,
        tci.addr,
        tci.reg_date,
        tci.valid_period,
        tcbi.process_status,
        tcl.type type
        FROM
        t_contractor_basic_info AS tcbi
        LEFT JOIN t_contractor_ledger_project AS tclp ON tcbi.id = tclp.biz_id
        LEFT JOIN t_contractor_ledger AS tcl ON tcl.id = tclp.contractor_id
        LEFT JOIN t_contractor_info AS tci ON tci.id = tcl.info_id
        LEFT JOIN t_contractor_project AS tcp ON tcp.id = tclp.project_id
        <!--WHERE
        tcbi.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})-->
        WHERE EXISTS (
        SELECT 1
        FROM unnest(string_to_array(tcl.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 tcbi.status = '0'
        <if test="param.contractorName != null and param.contractorName != ''">
            AND tci.name like concat('%',#{param.contractorName},'%')
        </if>
        <if test="param.validityStartDate != null and param.validityEndDate != null">
            AND to_char(tci.valid_period,'yyyy-mm-dd') &gt;= to_char(#{param.validityStartDate}::DATE,'yyyy-mm-dd')
            AND to_char(tci.valid_period,'yyyy-mm-dd') &lt;= to_char(#{param.validityEndDate}::DATE,'yyyy-mm-dd')
        </if>
        <if test="param.type != null and param.type != ''">
            AND tcl.type =#{param.type}
        </if>
        GROUP BY
        tcbi.id,
        tcl.id,
        tci.name,
        tcl.biz_nature_id,
        tci.province,
        tci.city,
        tci.addr,
        tci.reg_date,
        tci.valid_period,
        tcbi.process_status
        order by tcbi.update_date desc
    </select>
</mapper>