<?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.chinaitop.depot.intelligent.basicdata.mapper.WareHouseBasicInfoMapper" >
    <select id="getStorehouseInfo" parameterType="java.util.Map" resultType="java.util.Map">
        select * from (
        SELECT
        row_number() over(partition by t2.storehouse_code ) rn,
        case substr(t2.storehouse_code,1,2) when 'YG' then t4.tank_type else t2.storehouse_type end as storehouse_type,
        t2.storehouse_code,
        t2.storehouse_name,
        case substr(t2.storehouse_code,1,2) when 'YG' then t4.tank_capacity else t2.design_capacity end as design_capacity,
        t2.house_doot_position,
        case substr(t2.storehouse_code,1,2) when 'YG' then t4.tank_status else t2.storehouse_state end as storehouse_state,
        t2.dutyStoreman,
        t1.lkmc,
        t1.pz,
        t1.hwxz,
        t1.dj,
        t1.kcsl,
        to_char(t1.rq,'yyyy-MM-dd HH24:mi:ss') AS rq,
        to_char(t1.rq,'yyyy-MM-dd HH24:mi:ss') AS rcrq,
        to_char(t1.cjsj,'yyyy-MM-dd HH24:mi:ss') AS cjsj,
        t1.UnitID
	    FROM lsreport_ts:data_kcgl_kcsw_default AS t1
	    LEFT JOIN depot:basic_storehouse t2 ON t1.UnitID = t2.org_id AND t1.ch = t2.storehouse_id AND del_flag = 1
	    LEFT JOIN depot:basic_tank t4 on t4.id=t2.storehouse_id
        <where>
            <if test="org_id != null">
                t2.org_id = #{org_id}
            </if>
            <if test="library_type != null">
                AND t2.library_type = #{library_type}
            </if>
            <if test="cfCdoe != null">
                AND t2.storehouse_code = #{cfCdoe}
            </if>
        </where>
      union all
    	SELECT
        row_number() over(partition by t2.storehouse_code ) rn,
        case substr(t2.storehouse_code,1,2) when 'YG' then t4.tank_type else t2.storehouse_type end as storehouse_type,
        t2.storehouse_code,
        t2.storehouse_name,
        case substr(t2.storehouse_code,1,2) when 'YG' then t4.tank_capacity else t2.design_capacity end as design_capacity,
        t2.house_doot_position,
        case substr(t2.storehouse_code,1,2) when 'YG' then t4.tank_status else t2.storehouse_state end as storehouse_state,
        t2.dutyStoreman,
        t3.org_name,
        t1.lypz,
        t1.lyxz,
        t1.lydj,
        t1.kcsl,
        to_char(t1.rksj,'yyyy-MM-dd HH24:mi:ss') AS rq,
        to_char(t1.rksj,'yyyy-MM-dd HH24:mi:ss') AS rcrq,
        to_char(t1.input_time,'yyyy-MM-dd HH24:mi:ss') AS cjsj,
        t1.org_id
	    FROM depot:storage_product_kcsl AS t1
	    LEFT JOIN depot:basic_storehouse t2 ON t1.org_id = t2.org_id AND t1.house_id = t2.storehouse_id AND t2.del_flag = 1
	    LEFT JOIN depot:org_info t3 ON t3.org_id = t2.org_id
	    LEFT JOIN depot:basic_tank t4 on t4.id=t2.storehouse_id
	    <where>
            <if test="org_id != null">
                t2.org_id = #{org_id}
            </if>
            <if test="library_type != null">
                AND t2.library_type = #{library_type}
            </if>
            <if test="cfCdoe != null">
                AND t2.storehouse_code = #{cfCdoe}
            </if>
        </where>
        ) a
        where a.rn = 1
    </select>

    <select id="getColumn1Max" parameterType="java.util.Map" resultType="java.util.Map">
        SELECT
        <trim suffixOverrides="," >
            <if test="column1 != null" >
                MAX(${column1}) column1,
            </if>
            <if test="column2 != null" >
                MAX(${column2}) column2,
            </if>
            <if test="column3 != null" >
                MAX(${column3}) column3,
            </if>
        </trim>
        FROM ${tableName}
        where ${column1} >= 10000
    </select>

    <select id="getEnum" parameterType="java.lang.String" resultType="java.lang.String">
        SELECT
	      enumName
        FROM
	      basic_enum
        WHERE
	      enumId = ${code} AND delFlag = 1
    </select>

    <select id="getKeeperName" parameterType="java.util.Map" resultType="java.lang.String">
        select b.keepername
        from basic_storehouse a 
        left join (
            select 
		        a1.house_id house_id, wm_concat(c1.real_name) keepername 
	    from basic_keeper_house a1 
        left join basic_keeper b1 on b1.id=a1.keeper_id
        left join user_info c1 on c1.user_id=b1.user_id
        group by a1.house_id
        ) b on b.house_id=a.storehouse_id
        where a.org_id=${unitid} and a.storehouse_code=#{storehouse_code,jdbcType=VARCHAR} and a.del_flag=1
    </select>
</mapper>