<?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.th.demo.mapping.ware.StoreDailyMapper" >
    <resultMap id="BaseResultMap" type="com.th.demo.model.ware.StoreDaily" >
        <id column="id" property="id" jdbcType="VARCHAR" />
        <id column="cust_id" property="custId" jdbcType="VARCHAR" />
        <id column="invoice_compony" property="invoiceCompony" jdbcType="VARCHAR" />
        <id column="material_id" property="materialId" jdbcType="VARCHAR" />
        <id column="ord_no" property="ordNo" jdbcType="VARCHAR" />
        <id column="in_count" property="inCount" jdbcType="DOUBLE" />
        <id column="in_weight" property="inWeight" jdbcType="DOUBLE" />
        <id column="record_date" property="recordDate" jdbcType="VARCHAR" />

        <id column="hous_count" property="housCount" jdbcType="DOUBLE" />
        <id column="hous_weight" property="housWeight" jdbcType="DOUBLE" />
        <id column="out_count" property="outCount" jdbcType="DOUBLE" />
        <id column="out_weight" property="outWeight" jdbcType="DOUBLE" />
        <id column="rm_count" property="rmCount" jdbcType="DOUBLE" />
        <id column="rm_weight" property="rmWeight" jdbcType="DOUBLE" />
    </resultMap>
    <select id="query" resultMap="BaseResultMap" parameterType="java.lang.String" >
        select inrm.*,
        hous.hous_count,
        hous.hous_weight,
        outt.out_count,
        outt.out_weight
        from (
        select
        b.name as cust_id
        ,c.name as material_id
        ,left(a.ord_no,10) ord_no
        ,a.in_count
        ,a.in_weight
        ,a.rm_count
        ,a.rm_weight

        from (
        select
        ifnull(cust_id1,cust_id2) cust_id
        ,ifnull(invoice_compony1,invoice_compony2) invoice_compony
        ,ifnull(material_id1,material_id2) material_id
        ,left(ifnull(ord_no1,ord_no2),10) ord_no
        ,in_count1 as in_count
        ,in_weight1 as in_weight
        ,in_count2 as rm_count
        ,in_weight2 as rm_weight
        ,record_date1 as record_date
        from
        (
        select * from (
        (select t.id as id1
        ,t.cust_id as cust_id1
        ,t.invoice_compony as invoice_compony1
        ,t.material_id as material_id1
        ,left(t.ord_no,10) as ord_no1
        ,t.in_count as in_count1
        ,t.in_weight as in_weight1
        ,t.record_date as record_date1
        from (select left(t.ord_no,10)  as ord_no ,
                            sum(t.in_count) as in_count,
                                sum(t.in_weight) as in_weight,
                                max(t.invoice_compony) as invoice_compony,
                                max(t.cust_id) as cust_id,
                                max(t.material_id) as material_id,
                                max(t.record_date) as record_date,
                                max(t.id) as id
                from t_store_daily t
                where t.record_date = #{param4,jdbcType=VARCHAR}
                group by left(t.ord_no,10)) t ) a
        LEFT JOIN
        (select t.id as id2
        ,t.cust_id as cust_id2
        ,t.invoice_compony as invoice_compony2
        ,t.material_id as material_id2
        ,left(t.ord_no,10) as ord_no2
        ,t.in_count as in_count2
        ,t.in_weight as in_weight2
        ,t.record_date as record_date2
        from (select left(t.ord_no,10)  as ord_no ,
		    sum(t.in_count) as in_count,
                                sum(t.in_weight) as in_weight,
                                max(t.invoice_compony) as invoice_compony,
                                max(t.cust_id) as cust_id,
                                max(t.material_id) as material_id,
                                max(t.record_date) as record_date,
                                max(t.id) as id
                from t_store_daily t
                where t.record_date = #{param1,jdbcType=VARCHAR}
                group by left(t.ord_no,10)) t ) b
        on left(a.ord_no1,10) = left(b.ord_no2,10)
        )

        union
        select * from (
        (select
        t.id as id1
        ,t.cust_id as cust_id1
        ,t.invoice_compony as invoice_compony1
        ,t.material_id as material_id1
        ,left(t.ord_no,10) as ord_no1
        ,t.in_count as in_count1
        ,t.in_weight as in_weight1
        ,t.record_date as record_date1
        from (select left(t.ord_no,10)  as ord_no ,
		          sum(t.in_count) as in_count,
                                sum(t.in_weight) as in_weight,
                                max(t.invoice_compony) as invoice_compony,
                                max(t.cust_id) as cust_id,
                                max(t.material_id) as material_id,
                                max(t.record_date) as record_date,
                                max(t.id) as id
                from t_store_daily t
                where t.record_date = #{param4,jdbcType=VARCHAR}
                group by left(t.ord_no,10)) t) a
        right JOIN
        (select
        t.id as id2
        ,t.cust_id as cust_id2
        ,t.invoice_compony as invoice_compony2
        ,t.material_id as material_id2
        ,left(t.ord_no,10) as ord_no2
        ,t.in_count as in_count2
        ,t.in_weight as in_weight2
        ,t.record_date as record_date2
        from (select left(t.ord_no,10)  as ord_no ,
		        sum(t.in_count) as in_count,
                            sum(t.in_weight) as in_weight,
                            max(t.invoice_compony) as invoice_compony,
                            max(t.cust_id) as cust_id,
                            max(t.material_id) as material_id,
                            max(t.record_date) as record_date,
                                max(t.id) as id
            from t_store_daily t
            where t.record_date = #{param1,jdbcType=VARCHAR}
            group by left(t.ord_no,10)) t ) b
        on left(a.ord_no1,10) = left(b.ord_no2,10)
        )
        )	 tt
        ) a
        ,t_maint_customer b
        ,t_maint_material c
        where a.cust_id = b.id
        and a.material_id = c.id
        ) inrm left join
        (
        SELECT left(b.ord_no,10) ord_no,sum(a.count) as hous_count,sum(a.weight) as hous_weight
        from t_ware_in_record a,t_ware_store b
        where a.plate_no = b.plate_no
        and a.add_time > STR_TO_DATE(concat(#{param4,jdbcType=VARCHAR},'083000'), '%Y%m%d%H%i%s')
        and a.add_time &lt; STR_TO_DATE(concat(#{param1,jdbcType=VARCHAR},'083000'), '%Y%m%d%H%i%s')
        and a.record_type = '1'
        and b.cancel_flag = '0'
        group by left(b.ord_no,10)
        ) hous on left(inrm.ord_no,10) = left(hous.ord_no,10)
        LEFT JOIN
        (
        SELECT left(b.ord_no,10) ord_no,sum(a.count) as out_count,sum(a.weight) as out_weight
        from t_ware_out_record a,(select max(t.plate_no) as plate_no,left(max(t.ord_no),10) as ord_no from t_ware_store t where t.cancel_flag = '0' group by t.plate_no) b
        where a.plate_no = b.plate_no
        and a.add_time > STR_TO_DATE(concat(#{param4,jdbcType=VARCHAR},'083000'), '%Y%m%d%H%i%s')
        and a.add_time &lt; STR_TO_DATE(concat(#{param1,jdbcType=VARCHAR},'083000'), '%Y%m%d%H%i%s')
        and a.record_type = '1'
        and ifnull(a.back_flag,'0') &lt;>  '1'
        group by left(b.ord_no,10)
        ) outt on left(inrm.ord_no,10) = left(outt.ord_no,10)
    </select>
</mapper>