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  (t5.qty*t1.price) amount, MONTH (t6.checkdate) 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
        inner join st_stockbill_items t5 ON t5.sa_orderitemsid = t1.sa_orderitemsid AND t5.siteid = t1.siteid
        inner join st_stockbill t6 ON t6.st_stockbillid = t5.st_stockbillid AND t6.siteid = t5.siteid
WHERE YEAR (t6.checkdate)=$year$ AND t1.siteid=$siteid$ and t6.rb=1) a
GROUP BY point,sa_saleareaid,type
    )
        , hrtarget as (
select t1.sa_saleareaid,
    t1.areaname,
    t1.hrid,
    t1.name,
    t1.position,
     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,$num$)  as y1a,
    ROUND((ifnull(m1.amount,0)+ ifnull(m2.amount,0)+ifnull(m3.amount,0))/10000,$num$)  as s1a,
    ROUND((ifnull(m4.amount,0)+ ifnull(m5.amount,0)+ifnull(m6.amount,0))/10000,$num$)  as s2a,
    ROUND((ifnull(m7.amount,0)+ ifnull(m8.amount,0)+ifnull(m9.amount,0))/10000,$num$)  as s3a,
    ROUND((ifnull(m10.amount,0)+ ifnull(m11.amount,0)+ifnull(m12.amount,0))/10000,$num$) as s4a,
    ROUND(ifnull(m1.amount,0)/10000,$num$) as m1a,
    ROUND(ifnull(m2.amount,0)/10000,$num$) as m2a,
    ROUND(ifnull(m3.amount,0)/10000,$num$) as m3a,
    ROUND(ifnull(m4.amount,0)/10000,$num$)as m4a,
    ROUND(ifnull(m5.amount,0)/10000,$num$) as m5a,
    ROUND(ifnull(m6.amount,0)/10000,$num$) as m6a,
    ROUND(ifnull(m7.amount,0)/10000,$num$) as m7a,
    ROUND(ifnull(m8.amount,0)/10000,$num$) as m8a,
    ROUND(ifnull(m9.amount,0)/10000,$num$) as m9a,
    ROUND(ifnull(m10.amount,0)/10000,$num$) as m10a,
    ROUND(ifnull(m11.amount,0)/10000,$num$) as m11a,
    ROUND(ifnull(m12.amount,0)/10000,$num$) 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,
                 ROUND(y1a,$num$)  y1a,
                 ROUND(s1a,$num$)   s1a,
                 ROUND(s2a,$num$)  s2a,
                 ROUND(s3a,$num$)  s3a,
                 ROUND(s4a,$num$)   s4a,
                 ROUND(m1a,$num$)   m1a,
                 ROUND(m2a,$num$)   m2a,
                 ROUND(m3a,$num$)   m3a,
                 ROUND(m4a,$num$)    m4a,
                 ROUND(m5a,$num$)   m5a,
                 ROUND(m6a,$num$)    m6a,
                 ROUND(m7a,$num$)    m7a,
                 ROUND(m8a,$num$)   m8a,
                 ROUND(m9a,$num$)   m9a,
                 ROUND(m10a,$num$)   m10a,
                 ROUND(m11a,$num$)   m11a,
                 ROUND(m12a,$num$)  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,
                ROUND(sum(y1a),$num$)   y1a,
                ROUND(sum(s1a),$num$)  s1a,
                ROUND(sum(s2a),$num$)   s2a,
                ROUND(sum(s3a),$num$)   s3a,
                ROUND(sum(s4a),$num$)   s4a,
                ROUND(sum(m1a),$num$)   m1a,
                ROUND(sum(m2a),$num$)  m2a,
                ROUND(sum(m3a),$num$)   m3a,
                ROUND(sum(m4a),$num$)   m4a,
                ROUND(sum(m5a),$num$)   m5a,
                ROUND(sum(m6a),$num$)   m6a,
                ROUND(sum(m7a),$num$)   m7a,
                ROUND(sum(m8a),$num$)   m8a,
                ROUND(sum(m9a),$num$)   m9a,
                ROUND(sum(m10a),$num$)   m10a,
                ROUND(sum(m11a),$num$)   m11a,
                ROUND(sum(m12a),$num$)   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