<?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.dk.mdm.mapper.sys.BarcodeItemMapper">

    <!-- 通用设置 -->
    <!-- 通用查询列 -->
    <sql id="Base_Column_List">
        item_code, item_name_i18n, kind_codes, format_kind, def_format, sample_text, display_no, flg_valid, remarks
    </sql>

    <sql id="Base_Column_List_Join">
        stbi.item_code, stbi.item_name_i18n,
        sys.f_get_name_i18n(stbi.item_name_i18n,#{i18n}) as item_name,
        stbi.kind_codes, stbi.format_kind, stbi.def_format, stbi.sample_text,
        stbi.display_no, stbi.flg_valid, stbi.remarks
    </sql>

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.dk.mdm.model.pojo.sys.BarcodeItem">
        <id column="item_code" property="itemCode"/>
                <result column="item_name_i18n" property="itemNameI18n" typeHandler="JsonTypeHandler"/>
                <result column="kind_codes" property="kindCodes"/>
                <result column="format_kind" property="formatKind"/>
                <result column="def_format" property="defFormat" typeHandler="JsonTypeHandler"/>
                <result column="sample_text" property="sampleText"/>
                <result column="display_no" property="displayNo"/>
                <result column="flg_valid" property="flgValid"/>
                <result column="remarks" property="remarks"/>
    </resultMap>

    <resultMap id="BaseResultMapResponse" type="com.dk.mdm.model.response.sys.BarcodeItemResponse">
        <id column="item_code" property="itemCode"/>
        <id column="item_name" property="itemName"/>
        <result column="item_name_i18n" property="itemNameI18n" typeHandler="JsonTypeHandler"/>
        <result column="kind_codes" property="kindCodes"/>
        <result column="format_kind" property="formatKind"/>
        <result column="def_format" property="defFormat" typeHandler="JsonTypeHandler"/>
        <result column="sample_text" property="sampleText"/>
        <result column="display_no" property="displayNo"/>
        <result column="flg_valid" property="flgValid"/>
        <result column="remarks" property="remarks"/>
    </resultMap>


    <!-- 通用条件列 -->
    <sql id="Condition">
        <where>
            <if test="itemNameI18n != null and itemNameI18n != ''">
                AND stbi.item_name_i18n = #{itemNameI18n}
            </if>
            <if test="kindCodes != null and kindCodes != ''">
                AND ((#{kindCodes} = ANY (stbi.kind_codes)) OR (stbi.kind_codes IS NULL))
            </if>
            <if test="formatKind != null and formatKind != ''">
                AND stbi.format_kind = #{formatKind}
            </if>
            <if test="defFormat != null and defFormat != ''">
                AND stbi.def_format = #{defFormat}
            </if>
            <if test="sampleText != null and sampleText != ''">
                AND stbi.sample_text = #{sampleText}
            </if>
            <if test="displayNo != null">
                AND stbi.display_no = #{displayNo}
            </if>
            <if test="flgValid != null">
                AND stbi.flg_valid = #{flgValid}
            </if>
        </where>
    </sql>

    <sql id="idsForeach">
        <!-- 根据主键itemCode批量操作 -->
        WHERE item_code in
        <foreach collection="ids" index="index" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </sql>

    <!-- 查询表t_barcode_item，（条件查询+分页）列表 -->
    <select id="selectByCond" resultMap="BaseResultMapResponse">
        SELECT
        <include refid="Base_Column_List_Join"/>
        FROM sys.t_barcode_item  stbi
        <include refid="Condition"/>
        order by stbi.display_no
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>

    <!-- 查询表t_barcode_item，（条件查询）个数 -->
    <select id="countByCond" resultType="Long">
        SELECT
        count(1)
        FROM sys.t_barcode_item stbi
        <include refid="Condition"/>
    </select>
    
    <select id="selectByKind" resultType="Map">
        SELECT sys.f_get_name_i18n(stbi.item_name_i18n,  #{i18n}) as            "itemName",
               stbi.item_code                                                       "itemCode",
               sys.f_get_name_i18n(tdk2.kind_name_i18n,  #{i18n})               "formatKindName",
               stbi.sample_text                                                     "sampleText",
               stbi.flg_valid                                                       "flgValid",
               coalesce(mtbs2.FIX_LEN, text(stbi.def_format ->> 'FIX_LEN'))         "fixLen",
               coalesce(mtbs2.CUT_TYPE, text(stbi.def_format ->> 'CUT_TYPE'))       "cutType",
               coalesce(mtbs2.PAD_STR, text(stbi.def_format ->> 'PAD_STR'))         "padStr",
               coalesce(mtbs2.PAD_TYPE, text(stbi.def_format ->> 'PAD_TYPE'))       "padType",
               coalesce(mtbs2.DATE_FORMAT, text(stbi.def_format ->> 'DATE_FORMAT')) "dateFormat"
        FROM sys.t_data_kind tdk
                 left join sys.t_barcode_item stbi on ((tdk.kind_code = ANY (stbi.kind_codes)) OR (stbi.kind_codes IS NULL))
                 left join sys.t_data_kind tdk2 on stbi.format_kind = tdk2.kind_code
                 left join (
            select text(t.json ->> 'code')                    code,
                   bool(t.json ->> 'flgValid')                flgValid,
                   text(t.json -> 'format' ->> 'FIX_LEN')     FIX_LEN,
                   text(t.json -> 'format' ->> 'PAD_STR')     PAD_STR,
                   text(t.json -> 'format' ->> 'CUT_TYPE')    CUT_TYPE,
                   text(t.json -> 'format' ->> 'PAD_TYPE')    PAD_TYPE,
                   text(t.json -> 'format' ->> 'DATE_FORMAT') DATE_FORMAT
            from (
                     SELECT jsonb_array_elements(mtbs.set_items) json
                     FROM mst.t_barcode_set mtbs
                     WHERE mtbs.set_kind = #{setKind}
                       and mtbs.fty_id = #{ftyId}
                 ) t
        ) mtbs2
                           on stbi.item_code = mtbs2.code
        where tdk.kind_code = #{setKind}
          and ((#{setKind} = ANY (stbi.kind_codes)) OR (stbi.kind_codes IS NULL))
          and tdk.flg_valid
        order by stbi.display_no
    </select>

</mapper>
