<?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.common.api.mapper.DynamicFormInstanceMapper">
    <select id="selectListByCalledId" resultType="com.yeejoin.amos.boot.module.common.api.dto.DynamicFormInstanceDto">
        SELECT v.sequence_nbr      sequenceNbr,
               v.form_column_id    formColumnId,
               v.instance_id       instanceId,
               v.group_code        groupCode,
               v.field_name        fieldName,
               v.field_code        fieldCode,
               v.field_value       fieldValue,
               v.field_value_label fieldValueLabel,
               v.rec_user_name     recUserName,
               v.rec_user_id       recUserId,
               v.rec_date          recDate,
               v.is_delete         isDelete,
               v.block             block,
               f.field_type        fieldType
        FROM cb_dynamic_form_instance v
                 LEFT JOIN cb_dynamic_form_column f ON f.sequence_nbr = v.form_column_id
        WHERE v.instance_id = #{id}
    </select>
    <select id="listAll" resultType="java.util.Map">
        select
        d.*
        from
        (
        select
        i.INSTANCE_ID instanceId,
        i.GROUP_CODE groupCode,
        <foreach collection="fieldCodes" item="value" index="key" separator=",">
            MAX(CASE WHEN i.FIELD_CODE = #{key} THEN i.FIELD_VALUE END) as ${key}
        </foreach>
        from
        cb_dynamic_form_instance i
        where i.GROUP_CODE = #{groupCode}
        and i.APP_KEY = #{appKey}
        and i.is_delete = 0
        GROUP by
        i.INSTANCE_ID)d
        <if test="params != null and  params.size() > 0">
            where
            1=1
            <foreach collection="params" index="key" item="value" separator="">
                <choose>
                    <when test="fieldCodes[key] == 'like' and value !=null and value !=''">
                        and d.${key} like concat('%',#{value},'%')
                    </when>
                    <when test="fieldCodes[key] == 'eq' and value !=null and value !=''">
                        and d.${key} = #{value}
                    </when>
                    <when test="fieldCodes[key] == 'ge' and value !=null and value !=''">
                        and d.${key} >= #{value}
                    </when>
                    <when test="fieldCodes[key] == 'le' and value !=null and value !=''">
                        and d.${key} <![CDATA[<=]]> #{value}
                    </when>
                </choose>
            </foreach>
        </if>
        order by instanceId desc
    </select>
    <select id="pageList" resultType="java.util.Map">
        select
        d.*
        from
        (
        select
        i.INSTANCE_ID instanceId,
        i.GROUP_CODE groupCode,
        <foreach collection="fieldCodes" item="value" index="key" separator=",">
            MAX(CASE WHEN i.FIELD_CODE = #{key} THEN i.FIELD_VALUE END) as ${key},
            IF(FIND_IN_SET(i.field_type,'radio,select,treeSelect'), MAX(CASE WHEN i.FIELD_CODE = #{key} THEN
            i.FIELD_VALUE_LABEL END), null) as ${key}Label
        </foreach>
        from
        cb_dynamic_form_instance i
        where
        i.GROUP_CODE = #{groupCode}
        and i.is_delete = 0
        <if test="appKey != null and appKey !=''">
            and i.APP_KEY = #{appKey}
        </if>
        <foreach collection="params" index="key" item="value" separator="">
            <if test="key != null and key == 'instanceIds' ">
                and find_in_set(i.instance_id, #{value}) > 0
            </if>
        </foreach>
        GROUP by
        i.INSTANCE_ID) d
        <if test="params != null and params.size() > 0">
            where
            1=1
            <foreach collection="params" index="key" item="value" separator="">
                <choose>
                    <when test="fieldCodes[key] == 'like' and value !=null and value !=''">
                        and d.${key} like concat('%',#{value},'%')
                    </when>
                    <when test="fieldCodes[key] == 'eq' and value !=null and value !=''">
                        and d.${key} = #{value}
                    </when>
                    <when test="fieldCodes[key] == 'ge' and value !=null and value !=''">
                        and d.${key} >= #{value}
                    </when>
                    <when test="fieldCodes[key] == 'le' and value !=null and value !=''">
                        and d.${key} <![CDATA[<=]]> #{value}
                    </when>
                </choose>
            </foreach>
        </if>
        order by instanceId desc
    </select>
    <select id="listOneDayDutyPerson" resultType="java.util.Map">
        select
        d.*,
        ps.shift_id as shiftId,
        ps.duty_date as dutyDate,
        ds.name as shiftName
        from
        (
        select
        i.INSTANCE_ID instanceId,
        i.GROUP_CODE groupCode,
        <foreach collection="fieldCodes" item="value" index="key" separator=",">
            MAX(CASE WHEN i.FIELD_CODE = #{key} THEN i.FIELD_VALUE END) as ${key}
        </foreach>
        from
        cb_dynamic_form_instance i
        where i.GROUP_CODE = #{groupCode}
        and i.APP_KEY = #{appKey}
        GROUP by
        i.INSTANCE_ID ) d,
        cb_duty_person_shift ps,
        cb_duty_shift ds
        where
        d.instanceId = ps.instance_id
        and ps.shift_id = ds.sequence_nbr
        and ps.duty_date = #{dutyDate}
        <if test="shiftId != null and shiftId != ''">
            and ps.shift_id = #{shiftId}
        </if>
        <if test="params != null and  params.size() > 0">
            <foreach collection="params" index="key" item="value" separator="">
                <choose>
                    <when test="fieldCodes[key] == 'like' and value !=null and value !=''">
                        and d.${key} like concat('%',#{value},'%')
                    </when>
                    <when test="fieldCodes[key] == 'eq' and value !=null and value !=''">
                        and d.${key} = #{value}
                    </when>
                </choose>
            </foreach>
        </if>
        order by instanceId desc
    </select>
    <select id="listOnDutyPerson" resultType="java.util.Map">
        select
        d.*,
        ps.shift_id as shiftId,
        ps.duty_date as dutyDate,
        ds.name as shiftName,
        '在岗' state
        from
        (
        select
        i.INSTANCE_ID instanceId,
        i.GROUP_CODE groupCode,
        <foreach collection="fieldCodes" item="value" index="key" separator=",">
            MAX(CASE WHEN i.FIELD_CODE = #{key} THEN i.FIELD_VALUE END) as ${key}
        </foreach>
        from
        cb_dynamic_form_instance i
        where i.GROUP_CODE = #{groupCode}
        GROUP by
        i.INSTANCE_ID ) d,
        cb_duty_person_shift ps,
        cb_duty_shift ds
        where
        d.instanceId = ps.instance_id
        and ps.shift_id = ds.sequence_nbr
        and ps.duty_date = #{dutyDate}
        <if test="shiftId != null and shiftId != ''">
            and ps.shift_id in (#{shiftId})
        </if>
        order by instanceId desc
    </select>
    <select id="getInstanceByCodeAndValue" resultType="com.yeejoin.amos.boot.module.common.api.entity.DynamicFormInstance">
        SELECT
            f.instance_id
        FROM
            `cb_dynamic_form_instance` f
        <where>
            <if test="code != null and code != ''">
                f.field_code = #{code}
            </if>
            <if test="value != null and value != ''">
                AND f.field_value = #{value}
            </if>
        </where>
    </select>


    <!--不存在值班数据则不查找 修改sql 方法去除 by kongfm 2021-09-14-->
    <select id="pageListNew" resultType="java.util.Map">
        select
        d.*
        from
        (
        select
        i.INSTANCE_ID instanceId,
        i.GROUP_CODE groupCode,
        <foreach collection="fieldCodes" item="value" index="key" separator=",">
            MAX(CASE WHEN i.FIELD_CODE = #{key} THEN i.FIELD_VALUE END) as ${key},
            IF(FIND_IN_SET(i.field_type,'radio,select,treeSelect'), MAX(CASE WHEN i.FIELD_CODE = #{key} THEN
            i.FIELD_VALUE_LABEL END), null) as ${key}Label
        </foreach>
        from
        cb_dynamic_form_instance i
        where
        i.GROUP_CODE = #{groupCode}
        and i.is_delete = 0
        <if test="appKey != null and appKey !=''">
            and i.APP_KEY = #{appKey}
        </if>
        <foreach collection="params" index="key" item="value" separator="">
            <if test="key != null and key == 'instanceIds' ">
                and find_in_set(i.instance_id, #{value}) > 0
            </if>
        </foreach>
        GROUP by
        i.INSTANCE_ID) d
        <if test="params != null and params.size() > 0">
            where
            d.instanceId in (
            select tt.instance_id from cb_duty_person_shift tt where tt.duty_date >= #{stratTime}
            and tt.duty_date <![CDATA[<=]]>  #{endTime}
            )
            <foreach collection="params" index="key" item="value" separator="">
                <choose>
                    <when test="fieldCodes[key] == 'like' and value !=null and value !=''">
                        and d.${key} like concat('%',#{value},'%')
                    </when>
                    <when test="fieldCodes[key] == 'eq' and value !=null and value !=''">
                        and d.${key} = #{value}
                    </when>
                    <when test="fieldCodes[key] == 'ge' and value !=null and value !=''">
                        and d.${key} >= #{value}
                    </when>
                    <when test="fieldCodes[key] == 'le' and value !=null and value !=''">
                        and d.${key} <![CDATA[<=]]> #{value}
                    </when>
                </choose>
            </foreach>
        </if>
        order by instanceId desc
    </select>

</mapper>
