<?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.partner.dao.TPartnerStaffDao">
    <!-- 开启二级缓存 -->
    <!-- <cache type="org.mybatis.caches.ehcache.LoggingEhcache"/> -->
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.testor.module.partner.model.domain.TPartnerStaff">
        <id column="id" property="id"/>
        <id column="org_id" property="orgId"/>
        <id column="partner_id" property="partnerId"/>
        <id column="name" property="name"/>
        <id column="sex" property="sex"/>
        <id column="work_type" property="workType"/>
        <id column="idcard" property="idcard"/>
        <id column="phone" property="phone"/>
        <id column="certificate_status" property="certificateStatus"/>
        <id column="age" property="age"/>
        <id column="tenant_id" property="tenantId"/>
    </resultMap>

    <sql id="select_base_column">
        ps
        .
        id
        ,ps.org_id orgId, ps.partner_id partnerId, ps.name, ps.sex, ps.work_type workType,
        ps.idcard, ps.phone, ps.certificate_status certificateStatus, ps.age,
         ps.tenant_id as tenantId, ps.status, ps.remarks, ps.create_by createBy, ps.create_date createDate,
        ps.update_by as updateBy, ps.update_date updateDate
    </sql>

    <sql id="common_where_if">
        <if test="query.id!=null and query.id!=''">
            and ps.id=#{query.id}
        </if>
        <if test="query.orgId!=null and query.orgId!=''">
            and ps.org_id=#{query.orgId}
        </if>
        <if test="query.partnerId!='' and query.partnerId!=null">
            and ps.partner_id=#{query.partnerId}
        </if>
        <if test="query.name!='' and query.name!=null">
            and ps.name like concat('%',#{query.name},'%')
        </if>
        <if test="query.sex!='' and query.sex!=null">
            and ps.sex=#{query.sex}
        </if>
        <if test="query.workType!='' and query.workType!=null">
            and ps.work_type=#{query.workType}
        </if>
        <if test="query.idcard!='' and query.idcard!=null">
            and ps.idcard=#{query.idcard}
        </if>
        <if test="query.phone!='' and query.phone!=null">
            and ps.phone=#{query.phone}
        </if>
        <if test="query.certificateStatus!='' and query.certificateStatus!=null">
            and ps.certificate_status=#{query.certificateStatus}
        </if>
    </sql>
    <select id="getOrganizationPersonnel" resultType="com.testor.module.partner.model.vo.OrganizationPersonnelVO">
        select staff.name as warehouseEntryExitOperation,
               staff.id   as userId,
               org.name   as orgName
        from t_partner_org org
                 LEFT JOIN t_partner_staff staff ON org.id = staff.partner_id
        where staff.status = 0
          and staff.org_id in
              (select org.org_id from t_sys_org org where org.parent_ids like '%,${orgId}%' or org.org_id = '${orgId}')
    </select>

    <select id="findPartnerStaffExportData" resultType="com.testor.module.partner.model.excel.PartnerStaffReport">
        SELECT (@i:=@i+1) AS serialNumber,tps.id,
        tso.org_name,
        tso.org_code,
        tpo.name AS partner_name,
        tpo.credit_code,
        tps.name AS staff_name,
        tps.idcard,
        tps.phone,
        tps.work_type,
        tpc.name AS certificate_name,
        tpc.code AS certificate_code,
        tpc.valid_end_date AS certificate_valid_end_date,
        tpc.remarks AS certificate_remarks
        FROM
        t_partner_staff AS tps
        LEFT JOIN t_partner_org AS tpo ON tps.partner_id = tpo.id
        LEFT JOIN t_partner_certificate AS tpc ON tpc.use_type = 2 and tpc.status = 0 and tps.id = tpc.biz_id
        LEFT JOIN t_sys_org AS tso ON tps.org_id=tso.org_id,(SELECT @i:=0) AS i
        <where>
            tps.status = '0'
            <if test="orgId != null and orgId != '' ">
                and tso.org_id=#{orgId}
            </if>
            <if test="query.partnerName!=null and query.partnerName!=''">
                AND tpo.name like CONCAT('%',#{query.partnerName},'%')
            </if>
            <if test="query.name !=null and query.name!=''">
                AND tps.name like CONCAT('%',#{query.name},'%')
            </if>
            <if test="query.trainingStatus != null and query.trainingStatus != '' ">
                AND tps.training_status = #{query.trainingStatus}
            </if>
        </where>
    </select>

    <select id="listByPage" resultType="com.testor.module.partner.model.vo.PartnerStaffDetailVO"
            parameterType="com.testor.module.partner.model.dto.TPartnerStaffParam">
        SELECT tpo.name AS partner_name,
        tps.name,
        tps.training_status,
        CASE
        WHEN tps.sex='1' THEN '男'
        ELSE '女' END AS sex,
        tps.work_type,
        tps.sys_user_id as userId,
        tps.phone,
        tps.certificate_status,
        tps.age,
        tps.id,
        '2' as isStaff,
        tps.sys_user_id as userId
        FROM
        t_partner_staff AS tps
        LEFT JOIN t_partner_org AS tpo
        ON tps.partner_id = tpo.id
        <where>
            tps.status = #{query.status}
            <if test="query.condOrgIdList!=null">
                AND tps.org_id IN
                <foreach collection="query.condOrgIdList" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

            <if test="query.ids != null">
                AND tps.partner_id IN
                <foreach collection="query.ids" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="query.partnerName!=null and query.partnerName!=''">
                AND tpo.name like CONCAT('%',#{query.partnerName},'%')
            </if>
            <if test="query.name !=null and query.name!=''">
                AND tps.name like CONCAT('%',#{query.name},'%')
            </if>
            <if test="query.trainingStatus != null and query.trainingStatus != '' ">
                AND tps.training_status = #{query.trainingStatus}
            </if>
            <!--            <if test="query.certificateStatus != null ">-->
            <!--                AND tps.certificate_status = #{query.certificateStatus}-->
            <!--            </if>-->
        </where>
        ORDER BY tps.create_date DESC
    </select>

    <select id="getPartnerStaffDetail" resultType="com.testor.module.partner.model.vo.PartnerStaffDetailVO"
            parameterType="string">
        SELECT tpo.name         AS partner_name,
               tps.name,
               tps.training_status,
               tps.sex AS sex,
               tps.work_type,
               tps.phone,
               tps.certificate_status,
               tps.age,
               tps.id,
               tpo.id           AS partner_id,
               tsu.login_name,
               tps.idcard,
               tps.sys_user_id as userId,
               role.role_name
        FROM t_partner_staff AS tps
                 LEFT JOIN t_partner_org AS tpo
                           ON tps.partner_id = tpo.id
                 LEFT JOIN t_sys_user AS tsu
                           ON tps.sys_user_id = tsu.user_id
                 LEFT JOIN t_sys_user_role AS userRole
                           ON tsu.user_id = userRole.user_id
                 LEFT JOIN t_sys_role AS role
                           ON userRole.role_id = role.role_id
        WHERE tps.id = #{id}
    </select>
</mapper>