<?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.max maxTemp, td.min minTemp, 
    	td.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.time >= #{startDate,jdbcType=TIMESTAMP}
    </if>
    <if test="endDate != null and endDate != ''">
        and #{endDate,jdbcType=TIMESTAMP} >= td.time
    </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>
		    <if test="endDate != null and endDate != ''">
		        and #{endDate,jdbcType=TIMESTAMP} >= tq.v_value_time
		    </if>
		</where>
		GROUP BY tq.v_cf_code
	) tq on tq.v_cf_code = bs.storehouse_code
    
	<!-- LEFT JOIN t_qtdevinfo_value tq on tq.v_cf_code = bs.storehouse_code and tq.org_id = bs.org_id
	<if test="startDate != null and startDate != ''">
          and tq.v_value_time >= #{startDate,jdbcType=TIMESTAMP}
    </if>
    <if test="endDate != null and endDate != ''">
        and #{endDate,jdbcType=TIMESTAMP} >= tq.v_value_time
    </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 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
	) 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>
  
  <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.time, td.inTemp, td.inH, td.outTemp, 
    	td.outH, td.max maxTemp, td.min minTemp, td.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
	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.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>
  
  <select id="getSubmitReportInfo" resultType="java.util.Map" parameterType="map">
  	SELECT bs.*, max(CASE tdl.layerNumber WHEN 1 THEN tdl.lAvg ELSE '' END) firstFloor,
		max(CASE tdl.layerNumber WHEN 2 THEN tdl.lAvg ELSE '' END) secondFloor,
		max(CASE tdl.layerNumber WHEN 3 THEN tdl.lAvg ELSE '' END) threeFloor,
		max(CASE tdl.layerNumber WHEN 4 THEN tdl.lAvg ELSE '' END) fourFloor FROM (
	    SELECT bs.storehouse_code storeHouse, bs.storehouse_type storehouseType, bs.keeping_way keepingWay, 
	    	bs.storehouse_name storehouseName, td.inTemp, td.inH, td.max maxTemp, td.min minTemp, 
	    	td.avg avgTemp, sq.water,DATE_FORMAT(sq.input_time, '%Y-%m') inputTime,sq.harvest_time harvestTime
		FROM basic_storehouse bs
		LEFT JOIN storage_qualitycheck sq on bs.storehouse_id = sq.house_id and sq.org_id = bs.org_id
		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 DATE_FORMAT(td.time, '%Y-%m-%d') = #{startDate}
	    </if>
	    <!-- LEFT JOIN t_testdata_layer tdl ON tdl.lHouse = bs.storehouse_code
	    <if test="orgId!=null">
	    	and tdl.org_id = ${orgId}   
		</if>
		<if test="startDate != null and startDate != ''">
	          and DATE_FORMAT(tdl.ltime, '%Y-%m-%d') = #{startDate}
	    </if> -->
		<where>
			bs.del_flag = 1
			<if test="orgId!=null">
	            and bs.org_id = ${orgId}   
	        </if>
		</where>
		GROUP BY bs.storehouse_code
	) bs LEFT JOIN t_testdata_layer tdl ON tdl.lHouse = bs.storeHouse 
	<if test="orgId!=null">
    	and tdl.org_id = ${orgId}   
	</if>
	<if test="startDate != null and startDate != ''">
          and DATE_FORMAT(tdl.ltime, '%Y-%m-%d') = #{startDate}
    </if>
	GROUP BY bs.storeHouse
  </select>
  
</mapper>