CUCU2/src/custom/restcontroller/webmanage/sale/salestarget/SQL/人员目标统计-订单.sql

with sa_salestarget as
         (
              select  0+CAST(sum(amount) AS CHAR) amount,point,sa_saleareaid,type from (SELECT if(t1.isclose,t1.deliedqty*t1.price,amount) amount, MONTH (t2.createdate) point,ifnull(t4.sa_saleareaid,0) sa_saleareaid,'月' type
FROM sa_orderitems t1
    LEFT JOIN sa_order t2 ON t1.sa_orderid=t2.sa_orderid AND t1.siteid=t2.siteid
		left join sys_enterprise_tradefield t3 on t2.sys_enterpriseid=t3.sys_enterpriseid and t2.siteid=t3.siteid
		left join sa_salearea t4 on t4.sa_saleareaid=t3.sa_saleareaid and t4.siteid=t3.siteid
WHERE YEAR (t2.createdate)=$year$ AND t1.siteid=$siteid$ AND $where1$) a
GROUP BY  point,sa_saleareaid,type
    )
        , hrtarget as (
select t1.sa_saleareaid,
    t1.areaname,
    ROUND((ifnull(m1.amount,0)+ ifnull(m2.amount,0)+ifnull(m3.amount,0)+ ifnull(m4.amount,0)+ ifnull(m5.amount,0)+  ifnull(m6.amount,0)+ ifnull(m7.amount,0)+ ifnull(m8.amount,0)+  ifnull(m9.amount,0) + ifnull(m10.amount,0)+ ifnull(m11.amount,0)+  ifnull(m12.amount,0))/10000,2)  as y1a,
    ROUND((ifnull(m1.amount,0)+ ifnull(m2.amount,0)+ifnull(m3.amount,0))/10000,2)  as s1a,
    ROUND((ifnull(m4.amount,0)+ ifnull(m5.amount,0)+ifnull(m6.amount,0))/10000,2)  as s2a,
    ROUND((ifnull(m7.amount,0)+ ifnull(m8.amount,0)+ifnull(m9.amount,0))/10000,2)  as s3a,
    ROUND((ifnull(m10.amount,0)+ ifnull(m11.amount,0)+ifnull(m12.amount,0))/10000,2) as s4a,
    ROUND(ifnull(m1.amount,0)/10000,2) as m1a,
    ROUND(ifnull(m2.amount,0)/10000,2) as m2a,
    ROUND(ifnull(m3.amount,0)/10000,2) as m3a,
    ROUND(ifnull(m4.amount,0)/10000,2)as m4a,
    ROUND(ifnull(m5.amount,0)/10000,2) as m5a,
    ROUND(ifnull(m6.amount,0)/10000,2) as m6a,
    ROUND(ifnull(m7.amount,0)/10000,2) as m7a,
    ROUND(ifnull(m8.amount,0)/10000,2) as m8a,
    ROUND(ifnull(m9.amount,0)/10000,2) as m9a,
    ROUND(ifnull(m10.amount,0)/10000,2) as m10a,
    ROUND(ifnull(m11.amount,0)/10000,2) as m11a,
    ROUND(ifnull(m12.amount,0)/10000,2) as m12a
from sa_salestargethr t1
    left join sa_salestarget m1 on t1.sa_saleareaid = m1.sa_saleareaid and m1.type = '月' and m1.point = 1
    left join sa_salestarget m2 on t1.sa_saleareaid = m2.sa_saleareaid and m2.type = '月' and m2.point = 2
    left join sa_salestarget m3 on t1.sa_saleareaid = m3.sa_saleareaid and m3.type = '月' and m3.point = 3
    left join sa_salestarget m4 on t1.sa_saleareaid = m4.sa_saleareaid and m4.type = '月' and m4.point = 4
    left join sa_salestarget m5 on t1.sa_saleareaid = m5.sa_saleareaid and m5.type = '月' and m5.point = 5
    left join sa_salestarget m6 on t1.sa_saleareaid = m6.sa_saleareaid and m6.type = '月' and m6.point = 6
    left join sa_salestarget m7 on t1.sa_saleareaid = m7.sa_saleareaid and m7.type = '月' and m7.point = 7
    left join sa_salestarget m8 on t1.sa_saleareaid = m8.sa_saleareaid and m8.type = '月' and m8.point = 8
    left join sa_salestarget m9 on t1.sa_saleareaid = m9.sa_saleareaid and m9.type = '月' and m9.point = 9
    left join sa_salestarget m10 on t1.sa_saleareaid = m10.sa_saleareaid and m10.type = '月' and m10.point = 10
    left join sa_salestarget m11 on t1.sa_saleareaid = m11.sa_saleareaid and m11.type = '月' and m11.point = 11
    left join sa_salestarget m12 on t1.sa_saleareaid = m12.sa_saleareaid and m12.type = '月' and m12.point = 12
where  t1.sa_salestargetbillid = $sa_salestargetbillid$
    )
select *
from (
         select '区域' as type,
                t1.areaname,
								t1.sa_saleareaid,
								t2.parentid,
                t2.areafullname,
                1       level,
                CAST(ROUND(y1a,2) AS DECIMAL(16,2)) y1a,
                CAST(ROUND(s1a,2) AS DECIMAL(16,2))  s1a,
                CAST(ROUND(s2a,2) AS DECIMAL(16,2)) s2a,
                CAST(ROUND(s3a,2) AS DECIMAL(16,2))  s3a,
                CAST(ROUND(s4a,2) AS DECIMAL(16,2))  s4a,
                CAST(ROUND(m1a,2) AS DECIMAL(16,2))  m1a,
                CAST(ROUND(m2a,2) AS DECIMAL(16,2))  m2a,
                CAST(ROUND(m3a,2) AS DECIMAL(16,2))  m3a,
                CAST(ROUND(m4a,2) AS DECIMAL(16,2))   m4a,
                CAST(ROUND(m5a,2) AS DECIMAL(16,2))  m5a,
                CAST(ROUND(m6a,2) AS DECIMAL(16,2))   m6a,
                CAST(ROUND(m7a,2) AS DECIMAL(16,2))   m7a,
                CAST(ROUND(m8a,2) AS DECIMAL(16,2))  m8a,
                CAST(ROUND(m9a,2) AS DECIMAL(16,2))  m9a,
                CAST(ROUND(m10a,2) AS DECIMAL(16,2))  m10a,
                CAST(ROUND(m11a,2) AS DECIMAL(16,2))  m11a,
                CAST(ROUND(m12a,2) AS DECIMAL(16,2))  m12a
         from hrtarget t1
                  INNER JOIN sa_salearea t2 ON t2.sa_saleareaid = t1.sa_saleareaid
			  union all
         select '区域汇总' as  type,
                t6.areaname,
								t6.sa_saleareaid,
								t6.parentid,
                t6.areafullname,
                0        level,
                CAST(ROUND(sum(y1a),2)  AS DECIMAL(16,2)) y1a,
                CAST(ROUND(sum(s1a),2)  AS DECIMAL(16,2)) s1a,
                CAST(ROUND(sum(s2a),2)  AS DECIMAL(16,2)) s2a,
                CAST(ROUND(sum(s3a),2)  AS DECIMAL(16,2)) s3a,
                CAST(ROUND(sum(s4a),2)  AS DECIMAL(16,2)) s4a,
                CAST(ROUND(sum(m1a),2)  AS DECIMAL(16,2)) m1a,
                CAST(ROUND(sum(m2a),2)  AS DECIMAL(16,2)) m2a,
                CAST(ROUND(sum(m3a),2) AS DECIMAL(16,2)) m3a,
                CAST(ROUND(sum(m4a),2)  AS DECIMAL(16,2)) m4a,
                CAST(ROUND(sum(m5a),2)  AS DECIMAL(16,2)) m5a,
                CAST(ROUND(sum(m6a),2)  AS DECIMAL(16,2)) m6a,
                CAST(ROUND(sum(m7a),2)  AS DECIMAL(16,2)) m7a,
                CAST(ROUND(sum(m8a),2)  AS DECIMAL(16,2)) m8a,
                CAST(ROUND(sum(m9a),2)  AS DECIMAL(16,2)) m9a,
                CAST(ROUND(sum(m10a),2)  AS DECIMAL(16,2)) m10a,
                CAST(ROUND(sum(m11a),2)  AS DECIMAL(16,2)) m11a,
                CAST(ROUND(sum(m12a),2)  AS DECIMAL(16,2)) m12a
         from hrtarget t1
                left join sa_salearea t5 on t1.sa_saleareaid = t5.sa_saleareaid
								 left join sa_salearea t6 on t5.parentid = t6.sa_saleareaid and t5.siteid = t6.siteid
         group by  t6.sa_saleareaid,  t6.areafullname, t6.areaname) t
where  $where$
order by  case when t.type = '区域汇总' then 0 else 1 end