<?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.shinsoft.sto.mapper.ConfigMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.shinsoft.sto.model.Config">
        <result column="ID" property="id"/>
        <result column="PROCESS_INSTANCE_ID" property="processInstanceId"/>
        <result column="PROCESS_INSTANCE_STATUS" property="processInstanceStatus"/>
        <result column="ADD_ID" property="addId"/>
        <result column="ADD_CODE" property="addCode"/>
        <result column="ADD_NAME" property="addName"/>
        <result column="ADD_DEPT_ID" property="addDeptId"/>
        <result column="ADD_DEPT_CODE" property="addDeptCode"/>
        <result column="ADD_DEPT_NAME" property="addDeptName"/>
        <result column="ADD_TIME" property="addTime"/>
        <result column="MODIFY_ID" property="modifyId"/>
        <result column="MODIFY_CODE" property="modifyCode"/>
        <result column="MODIFY_NAME" property="modifyName"/>
        <result column="MODIFY_TIME" property="modifyTime"/>
        <result column="CANCEL_ID" property="cancelId"/>
        <result column="CANCEL_CODE" property="cancelCode"/>
        <result column="CANCEL_NAME" property="cancelName"/>
        <result column="CANCEL_TIME" property="cancelTime"/>
        <result column="CANCEL_FLAG" property="cancelFlag"/>
        <result column="BELONG_ID" property="belongId"/>
        <result column="COMP_CD" property="compCd"/>
        <result column="DEPT_CD" property="deptCd"/>
        <result column="STORE_CD" property="storeCd"/>
        <result column="STORE_NM" property="storeNm"/>
        <result column="AREA_CD" property="areaCd"/>
        <result column="STACK_CD" property="stackCd"/>
        <result column="STORE_TYPE" property="storeType"/>
        <result column="ACTIVE_STATUS" property="activeStatus"/>
        <result column="REMARKS" property="remarks"/>
        <result column="ATTR1" property="attr1"/>
        <result column="ATTR2" property="attr2"/>
        <result column="ATTR3" property="attr3"/>
        <result column="ATTR4" property="attr4"/>
        <result column="ATTR5" property="attr5"/>
        <result column="ATTR6" property="attr6"/>
        <result column="ATTR7" property="attr7"/>
        <result column="ATTR8" property="attr8"/>
        <result column="ATTR9" property="attr9"/>
        <result column="ATTR10" property="attr10"/>
        <result column="F_ID" property="fId"/>
    </resultMap>

    <select id="selectAll" resultType="com.shinsoft.sto.model.Config">
        SELECT CON1.STACK_CD stackCd,
               CON2.AREA_CD areaCd,
                CON1.id id,
                CON1.f_id fId,
                CON2.id id2,
                CON3.id id1,
               CON3.STORE_CD storeCd,
               CON3.STORE_NM storeNm,
               CON1.REMARKS remarks,
               CON1.ACTIVE_STATUS activeStatus,
               CON1.STORE_TYPE storeType,
               CON1.CANCEL_FLAG cancelFlag,
        CON1.ADD_ID        addId,
        CON1.ADD_NAME      addName,
        CON1.ADD_TIME      addTime


        FROM STO_CONFIG CON1
                 INNER JOIN STO_CONFIG CON2 ON CON1.F_ID = CON2.ID
                 INNER JOIN STO_CONFIG CON3 ON CON2.F_ID = CON3.ID
        where
            CON1.cancel_flag = '0'
            <if test="activeStatus != null and activeStatus !='' ">
                and CON1.active_status = #{activeStatus}
            </if>
            <if test="storeType != null and storeType !='' ">
                and CON1.store_type = #{storeType}
            </if>
            <if test="areaCd != null and areaCd !='' ">
                and CON2.area_cd like concat(concat('%',#{areaCd}),'%')
            </if>
            <if test="stackCd != null and stackCd !='' ">
                and CON1.stack_cd like concat(concat('%',#{stackCd}),'%')
            </if>
            <if test="storeNm != null and storeNm !='' ">
                and CON3.store_nm like concat(concat('%',#{storeNm}),'%')
            </if>
        order by CON1.ADD_TIME desc

    </select>
    <select id="selectAllFj" resultType="com.shinsoft.sto.model.Config">
        SELECT CON1.STACK_CD stackCd,
               CON2.AREA_CD areaCd,
                CON1.id id,
                CON1.f_id fId,
                CON2.id id2,
                CON3.id id1,
               CON1.STORE_NM storeNm,
               CON1.REMARKS remarks,
               CON1.ACTIVE_STATUS activeStatus,
               CON1.STORE_TYPE storeType,
               CON1.CANCEL_FLAG cancelFlag,
               CON1.ADD_ID addId


        FROM STO_CONFIG CON1
                 INNER JOIN STO_CONFIG CON2 ON CON1.F_ID = CON2.ID
                 INNER JOIN STO_CONFIG CON3 ON CON2.F_ID = CON3.ID
        where
            CON1.cancel_flag = '0'
            <if test="activeStatus != null and activeStatus !='' ">
                and CON1.active_status = #{activeStatus}
            </if>

            <if test="fId2 != null and fId2 !='' ">
                and CON2.f_id = #{fId2}
            </if>
            <if test="fId3 != null and fId3 !='' ">
                and CON1.f_id = #{fId3}
            </if>
            <if test="storeType != null and storeType !='' ">
                and CON1.store_type = #{storeType}
            </if>
            <if test="storeNm != null and storeNm !='' ">
                and CON3.store_nm like concat(concat('%',#{storeNm}),'%')

            </if>
        order by CON1.ADD_TIME desc

    </select>
    <select id="selectAllQH" resultType="com.shinsoft.sto.model.Config">
        select distinct con2.id      id,
                        con1.id      fid,
                        con2.AREA_CD areaCd

        from sto_config con2
                 inner join sto_config con1 on con2.F_ID = con1.ID
        where CON2.cancel_flag = '0'
          and CON2.ACTIVE_STATUS = '0'
          and con2.f_id = #{fId}
        <if test="areaCd != null and areaCd !=''">
            and con2.AREA_CD like concat(concat('%',#{areaCd}),'%')
        </if>

        order by CON2.ADD_TIME desc

    </select>
    <select id="selectAllKFItem" resultType="com.shinsoft.sto.model.Config">

        SELECT distinct CON.id,
        CON.STORE_CD storeCd,
        CON.STORE_NM storeNm,
        CON.STORE_TYPE storeType ,
        CON.ADD_TIME
        FROM STO_CONFIG CON
        INNER JOIN STO_ONHAND HA ON CON.STORE_CD = HA.STORE_CD
        WHERE CON.STO_CONFIG_LEVEL = '1'
        AND CON.CANCEL_FLAG = '0'
        AND CON.ACTIVE_STATUS = '0'
        AND HA.CANCEL_FLAG = '0'

        <if test="itemCd != null and itemCd !=''">
            and  HA.ITEM_CD = #{itemCd}
        </if>
        <if test="storeType != null and storeType !=''">
            and  CON.STORE_TYPE = #{storeType}
        </if>
        <if test="storeNm != null and storeNm !=''">
            and  CON.STORE_NM like concat(concat('%',#{storeNm}),'%')
        </if>
        order by CON.ADD_TIME desc
    </select>

    <select id="selectAllKF" resultType="com.shinsoft.sto.model.Config">
        select distinct
        id ,
        STORE_CD storeCd,
        STORE_NM storeNm,
        STORE_TYPE storeType ,
        ADD_TIME
        from STO_CONFIG
        where cancel_flag = '0'
        and ACTIVE_STATUS = '0'
        <if test="storeType != null and storeType !=''">
            and  STORE_TYPE = #{storeType}
        </if>
        and STO_CONFIG_LEVEL = '1'
        <if test="storeNm != null and storeNm !=''">
            and  STORE_NM like concat(concat('%',#{storeNm}),'%')
        </if>
        order by ADD_TIME desc

    </select>

    <select id="selectAllDWH" resultType="com.shinsoft.sto.model.Config">
        select id,
               STACK_CD stackCd
        from sto_config
        where cancel_flag = '0'
          and ACTIVE_STATUS = '0'
          and STO_CONFIG_LEVEL = '3'
          and f_id = #{fId}
        <if test="stackCd != null and stackCd !=''">
            and  STACK_CD like concat(concat('%',#{stackCd}),'%')
        </if>
        order by ADD_TIME desc

    </select>

    <select id="selectAllById" resultType="com.shinsoft.sto.model.Config">
        SELECT CON1.STACK_CD stackCd,
               CON2.AREA_CD areaCd,
               CON1.id id,
               CON1.f_id fId,
               CON2.id id2,
               CON3.id id1,
               CON1.STORE_NM storeNm,
               CON1.REMARKS remarks,
               CON1.ACTIVE_STATUS activeStatus,
               CON1.STORE_TYPE storeType,
               CON1.CANCEL_FLAG cancelFlag,
               CON1.ADD_ID addId


        FROM STO_CONFIG CON1
                 INNER JOIN STO_CONFIG CON2 ON CON1.F_ID = CON2.ID
                 INNER JOIN STO_CONFIG CON3 ON CON2.F_ID = CON3.ID
        where
            CON1.cancel_flag = '0'
          and CON1.id = #{id}
        order by CON1.ADD_TIME desc

    </select>

    <select id="getMax" resultType="string">
        select max(SUBSTR(STORE_CD, -5)) code from STO_CONFIG

    </select>

</mapper>
