<?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.statisticalReport.mapper.StatisticalReportMapper">
  
 <select id="getSummaryReportInfo" resultType="java.util.Map" parameterType="map">
    <!-- SELECT bs.storehouse_code storeHouse, td.inTemp, td.inH, td.outTemp, td.outH, td.t_max maxTemp, td.t_min minTemp, 
    	td.t_avg avgTemp, IFNULL(tq.co2avg,0) AS "co2avg", IFNULL(tq.ph3avg,0) AS "ph3avg", IFNULL(tq.o2avg,0) AS "o2avg",
		ccmax.iValue maxvalues, ccmin.iValue minvalues, bs.dutyStoreman, bs.remark
	FROM basic_storehouse bs
	LEFT JOIN t_testdata td on td.storehouse = bs.storehouse_code and td.iLq_yq = '0' and td.org_id = bs.org_id
	<if test="startDate != null and startDate != ''">
          and td.t_time >= #{startDate,jdbcType=TIMESTAMP}
    </if>
    LEFT JOIN(
		SELECT MAX(CASE tq.v_dev_kind_code WHEN "6964" THEN tq.cpo_avg ELSE 0 END ) AS "co2avg",
  		MAX(CASE tq.v_dev_kind_code WHEN "6965" THEN tq.cpo_avg ELSE 0 END ) AS "ph3avg",
  		MAX(CASE tq.v_dev_kind_code WHEN "6962" THEN tq.cpo_avg ELSE 0 END ) AS "o2avg",tq.v_cf_code
		FROM t_qtdevinfo_value tq
		<where>
			<if test="orgId!=null">
	            and tq.org_id = ${orgId}   
	        </if>
	        <if test="startDate != null and startDate != ''">
		          and tq.v_value_time >= #{startDate,jdbcType=TIMESTAMP}
		    </if>
		</where>
		GROUP BY tq.v_cf_code
	) tq on tq.v_cf_code = bs.storehouse_code
	LEFT JOIN (
		SELECT DISTINCT ti.vcfcode AS vCfCode, MAX(td.i_value) AS iValue, td.v_update_time AS vUpdateTime
	  	FROM  t_ccinfo ti
		INNER JOIN t_ccdata td ON ti.vdevcode = td.v_cc_code AND td.org_id = ti.org_id
		<where>
	        <if test="startDate != null and startDate != ''">
	           and td.v_update_time >= #{startDate,jdbcType=TIMESTAMP}
		    </if>
		</where>
		GROUP BY ti.vcfcode
	) ccmax ON td.storehouse = ccmax.vCfCode
	LEFT JOIN (
		SELECT DISTINCT ti.vcfcode AS vCfCode, min(td.i_value) AS iValue, td.v_update_time AS vUpdateTime
	  	FROM  t_ccinfo ti
		INNER JOIN t_ccdata td ON ti.vdevcode = td.v_cc_code  AND td.org_id = ti.org_id
		<where>
	        <if test="startDate != null and startDate != ''">
	           and td.v_update_time >= #{startDate,jdbcType=TIMESTAMP}
		    </if>
		    <if test="endDate != null and endDate != ''">
		        and #{endDate,jdbcType=TIMESTAMP} >= td.v_update_time
		    </if>
		</where>
		GROUP BY ti.vcfcode
	) ccmin ON td.storehouse = ccmin.vCfCode
	<where>
		bs.del_flag = 1
		<if test="orgId!=null">
            and bs.org_id = ${orgId}   
        </if>
	</where>
	GROUP BY bs.storehouse_code -->
	
	SELECT bs.storehouse_code storeHouse, td.inTemp, td.inH, td.outTemp, td.outH, td.t_max maxTemp, 
		td.t_min minTemp, td.t_avg avgTemp, bs.dutyStoreman, bs.remark
	FROM basic_storehouse bs
	LEFT JOIN (
		  select a.* from t_testdata a inner join (
		    select storehouse,max(t_time) t_time from t_testdata where org_id = 49 and iLq_yq = '0' 
		    <if test="startDate != null and startDate != ''">
		          and to_date(t_time, 'yyyy-mm-dd') = #{startDate,jdbcType=TIMESTAMP}
		    </if>
		    GROUP BY storehouse
		  ) b on a.storehouse = b.storehouse and a.t_time = b.t_time 
		  <if test="orgId!=null">
	    	 and a.org_id = ${orgId}   
	  	  </if>
		) td on td.storehouse = bs.storehouse_code
	 and td.t_time >= #{startDate,jdbcType=TIMESTAMP}
    where bs.del_flag = 1 
    <if test="orgId!=null">
    	 and bs.org_id = ${orgId}   
  	</if>
	
  </select>
  
  <select id="getDetailsReportInfo" resultType="java.util.Map" parameterType="map">
    <!-- SELECT bs.storehouse_code storeHouse, bs.storehouse_type storehouseType, bs.keeping_way keepingWay, 
    	bs.storehouse_name storehouseName, td.t_time, td.inTemp, td.inH, td.outTemp, 
    	td.outH, td.t_max maxTemp, td.t_min minTemp, td.t_avg avgTemp, tq.v_value_time valueTime,
    	MAX(CASE v_dev_kind_code WHEN "6964" THEN cpo_avg ELSE 0 END ) AS "co2avg",
  		MAX(CASE v_dev_kind_code WHEN "6965" THEN cpo_avg ELSE 0 END ) AS "ph3avg",
  		MAX(CASE v_dev_kind_code WHEN "6962" THEN cpo_avg ELSE 0 END ) AS "o2avg",
		ccmax.iValue maxvalues, ccmin.iValue minvalues, ccavg.iValue avgvalues, ccavg.vUpdateTime,
		bs.dutyStoreman, bs.remark, sq.water, sq.location
	FROM basic_storehouse bs
	LEFT JOIN storage_qualitycheck sq on bs.storehouse_code = sq.house_id AND sq.org_id = bs.org_id and sq.type = 0
	LEFT JOIN t_testdata td on bs.storehouse_code = td.storehouse and td.iLq_yq = '0' AND td.org_id = bs.org_id
	<if test="startDate != null and startDate != ''">
          and DATE_FORMAT(td.t_time, '%Y-%m-%d') = #{startDate}
    </if>
	LEFT JOIN t_qtdevinfo_value tq on bs.storehouse_code = tq.v_cf_code AND tq.org_id = bs.org_id
	<if test="startDate != null and startDate != ''">
          and DATE_FORMAT(tq.v_value_time, '%Y-%m-%d') = #{startDate}
    </if>
	LEFT JOIN (
		SELECT DISTINCT ti.vcfcode AS vCfCode, MAX(td.i_value) AS iValue, td.v_update_time AS vUpdateTime
	  	FROM  t_ccinfo ti
		INNER JOIN t_ccdata td ON ti.vdevcode = td.v_cc_code
		<where>
	        <if test="startDate != null and startDate != ''">
	           and DATE_FORMAT(td.v_update_time, '%Y-%m-%d') = #{startDate}
		    </if>
		</where>
		GROUP BY ti.vcfcode
	) ccmax ON bs.storehouse_code = ccmax.vCfCode
	LEFT JOIN (
		SELECT DISTINCT ti.vcfcode AS vCfCode, min(td.i_value) AS iValue, td.v_update_time AS vUpdateTime
	  	FROM  t_ccinfo ti
		INNER JOIN t_ccdata td ON ti.vdevcode = td.v_cc_code
		<where>
	        <if test="startDate != null and startDate != ''">
	           and DATE_FORMAT(td.v_update_time, '%Y-%m-%d') = #{startDate}
		    </if>
		</where>
		GROUP BY ti.vcfcode
	) ccmin ON bs.storehouse_code = ccmin.vCfCode
	LEFT JOIN (
		SELECT DISTINCT ti.vcfcode AS vCfCode, avg(td.i_value) AS iValue,
			td.v_update_time AS vUpdateTime
		FROM t_ccinfo ti
		INNER JOIN t_ccdata td ON ti.vdevcode = td.v_cc_code
		<where>
	        <if test="startDate != null and startDate != ''">
	           and DATE_FORMAT(td.v_update_time, '%Y-%m-%d') = #{startDate}
		    </if>
		</where>
		GROUP BY ti.vcfcode
	) ccavg ON bs.storehouse_code = ccavg.vCfCode
	<where>
		<if test="orgId!=null">
            and bs.org_id = ${orgId}   
        </if>
        <if test="storehouseCode != null and storehouseCode != ''">
           and bs.storehouse_code = #{storehouseCode}
	    </if>
	</where>
	GROUP BY bs.storehouse_code -->
	
	SELECT bs.storehouse_code storehouse, bs.storehouse_type storehousetype, bs.keeping_way keepingway, 
    	bs.storehouse_name storehousename, td.t_time, td.intemp, td.inh, td.outtemp, 
    	td.outh, td.t_max maxtemp, td.t_min mintemp, td.t_avg avgtemp, bs.remark, sq.water, sq.location
	FROM basic_storehouse bs
	LEFT JOIN storage_qualitycheck sq on bs.storehouse_id = sq.house_id AND sq.org_id = bs.org_id and sq.type = 0
	LEFT join(
	  select a.* from t_testdata a inner join (
		    select storehouse,max(t_time) t_time from t_testdata where iLq_yq = '0' 
		    <if test="orgId!=null">
	            and org_id = ${orgId}   
	        </if>
		    <if test="startDate != null and startDate != ''">
			    and to_date(t_time, 'yyyy-mm-dd') = #{startDate}
		    </if>
		    GROUP BY storehouse
		  ) b on a.storehouse = b.storehouse and a.t_time = b.t_time and a.org_id = 49
	) td on bs.storehouse_code = td.storehouse
	<where>
		<if test="orgId!=null">
            and bs.org_id = ${orgId}   
        </if>
        <if test="storehouseCode != null and storehouseCode != ''">
           and bs.storehouse_code = #{storehouseCode}
	    </if>
	</where>
  </select>
  
  <select id="getSubmitReportInfo" resultType="java.util.Map" parameterType="map">
  	SELECT bs.*, tdl1.lAvg firstfloor, tdl2.lAvg secondfloor, tdl3.lAvg threefloor, tdl4.lAvg fourfloor FROM (
	    SELECT bs.storehouse_code storehouse, bs.storehouse_type storehousetype, bs.keeping_way keepingway, 
	    	bs.storehouse_name storehousename, td.intemp, td.inh, td.t_max maxtemp, td.t_min mintemp, 
	    	td.t_avg avgtemp,sq.grain_annual harvesttime
	    	<!-- , sq.water -->
		FROM basic_storehouse bs
		LEFT JOIN (
			select a.grain_annual,a.house_id from business_store_ware_detail a
			INNER JOIN (
			    select max(t.id) id from business_store_ware_detail t
			    where t.type= 'notice' 
			    <if test="orgId!=null">
			    	  and t.org_id = ${orgId}   
				</if>
				group by t.house_id
			) b on a.id = b.id 
		) sq on bs.storehouse_id = sq.house_id
		LEFT JOIN (
		  select a.* from t_testdata a inner join (
		    select storehouse,max(t_time) t_time from t_testdata 
		    where iLq_yq = '0'
		    <if test="orgId!=null">
		    	  and org_id = ${orgId}   
			</if> 
			<if test="startDate != null and startDate != ''">
		          and to_date(t_time, 'yyyy-mm-dd') = #{startDate}
		    </if>  
		    GROUP BY storehouse
		  ) b on a.storehouse = b.storehouse and a.t_time = b.t_time 
		  <if test="orgId!=null">
		    	  and a.org_id = ${orgId}   
		  </if>
		) td on td.storehouse = bs.storehouse_code
        where bs.del_flag = 1 
        <if test="orgId!=null">
		    	  and bs.org_id = ${orgId}   
		</if>  
	) bs 
	LEFT join (
	      select a.lHouse,a.lAvg from t_testdata_layer a inner join (
		    select lHouse,max(ltime) ltime from t_testdata_layer 
		    where 
		    <if test="orgId!=null">
		    	  org_id = ${orgId}   
			</if>
			<if test="startDate != null and startDate != ''">
		          and to_date(ltime, 'yyyy-mm-dd') = #{startDate}
		    </if> 
		    GROUP BY lHouse
		  ) b on a.lHouse = b.lHouse and a.ltime = b.ltime and layerNumber = 1 
		  <if test="orgId!=null">
		    	 and a.org_id = ${orgId}   
		  </if> 
	) tdl1 ON tdl1.lHouse = bs.storeHouse
	LEFT join (
	      select a.lHouse,a.lAvg from t_testdata_layer a inner join (
		    select lHouse,max(ltime) ltime from t_testdata_layer 
		    where 
		    <if test="orgId!=null">
		    	  org_id = ${orgId}   
			</if>
			<if test="startDate != null and startDate != ''">
		          and to_date(ltime, 'yyyy-mm-dd') = #{startDate}
		    </if> 
		    GROUP BY lHouse
		  ) b on a.lHouse = b.lHouse and a.ltime = b.ltime and layerNumber = 2 
		  <if test="orgId!=null">
		    	 and a.org_id = ${orgId}   
		  </if>
	) tdl2 ON tdl2.lHouse = bs.storeHouse
	LEFT join (
	      select a.lHouse,a.lAvg from t_testdata_layer a inner join (
		    select lHouse,max(ltime) ltime from t_testdata_layer 
		    where 
		    <if test="orgId!=null">
		    	  org_id = ${orgId}   
			</if>
			<if test="startDate != null and startDate != ''">
		          and to_date(ltime, 'yyyy-mm-dd') = #{startDate}
		    </if> 
		    GROUP BY lHouse
		  ) b on a.lHouse = b.lHouse and a.ltime = b.ltime and layerNumber = 3
		  <if test="orgId!=null">
		    	 and a.org_id = ${orgId}   
		  </if>
	) tdl3 ON tdl3.lHouse = bs.storeHouse
	LEFT join (
	      select a.lHouse,a.lAvg from t_testdata_layer a inner join (
		    select lHouse,max(ltime) ltime from t_testdata_layer 
		    where 
		    <if test="orgId!=null">
		    	  org_id = ${orgId}   
			</if>
			<if test="startDate != null and startDate != ''">
		          and to_date(ltime, 'yyyy-mm-dd') = #{startDate}
		    </if> 
		    GROUP BY lHouse
		  ) b on a.lHouse = b.lHouse and a.ltime = b.ltime and layerNumber = 4
		  <if test="orgId!=null">
		    	 and a.org_id = ${orgId}   
		  </if>
	) tdl4 ON tdl4.lHouse = bs.storeHouse
  </select>
  
</mapper>