lsa_YOS/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.sumqty*t1.price) 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
 inner join (
SELECT  t1.sa_orderitemsid,t1.siteid,
        SUM(ifnull(t1.qty,0)) sumqty
FROM st_stockbill_items t1
         inner join st_stockbill t2 on t1.siteid = t2.siteid and t1.st_stockbillid = t2.st_stockbillid
         inner join sa_orderitems t4 on t1.sa_orderitemsid=t4.sa_orderitemsid and t1.siteid = t4.siteid
WHERE t2.STATUS ='审核' and t2.rb=1 and $where1$ group by t1.sa_orderitemsid,t1.siteid) t5 on t5.sa_orderitemsid=t1.sa_orderitemsid and t5.siteid=t1.siteid

WHERE YEAR (t2.createdate)=$year$ AND t1.siteid=$siteid$) a
GROUP BY point,sa_saleareaid,type
    )
        , hrtarget as (
select t1.sa_saleareaid,
    t1.areaname,
    t1.hrid,
    t1.name,
    t1.position,
    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)  as y1a,
    ifnull(m1.amount,0)+ ifnull(m2.amount,0)+ifnull(m3.amount,0)  as s1a,
    ifnull(m4.amount,0)+ ifnull(m5.amount,0)+ifnull(m6.amount,0)  as s2a,
    ifnull(m7.amount,0)+ ifnull(m8.amount,0)+ifnull(m9.amount,0)  as s3a,
    ifnull(m10.amount,0)+ ifnull(m11.amount,0)+ifnull(m12.amount,0) as s4a,
    ifnull(m1.amount,0) as m1a,
    ifnull(m2.amount,0) as m2a,
    ifnull(m3.amount,0) as m3a,
    ifnull(m4.amount,0) as m4a,
    ifnull(m5.amount,0) as m5a,
    ifnull(m6.amount,0) as m6a,
    ifnull(m7.amount,0) as m7a,
    ifnull(m8.amount,0) as m8a,
    ifnull(m9.amount,0) as m9a,
    ifnull(m10.amount,0) as m10a,
    ifnull(m11.amount,0) as m11a,
    ifnull(m12.amount,0) 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/10000,2) y1a,
                ROUND(s1a/10000,2) s1a,
                ROUND(s2a/10000,2) s2a,
                ROUND(s3a/10000,2) s3a,
                ROUND(s4a/10000,2) s4a,
                ROUND(m1a/10000,2) m1a,
                ROUND(m2a/10000,2) m2a,
                ROUND(m3a/10000,2) m3a,
                ROUND(m4a/10000,2) m4a,
                ROUND(m5a/10000,2) m5a,
                ROUND(m6a/10000,2) m6a,
                ROUND(m7a/10000,2) m7a,
                ROUND(m8a/10000,2) m8a,
                ROUND(m9a/10000,2) m9a,
                ROUND(m10a/10000,2) m10a,
                ROUND(m11a/10000,2) m11a,
                ROUND(m12a/10000,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,
                ROUND(sum(y1a)/10000,2) y1a,
                ROUND(sum(s1a)/10000,2) s1a,
                ROUND(sum(s2a)/10000,2) s2a,
                ROUND(sum(s3a)/10000,2) s3a,
                ROUND(sum(s4a)/10000,2) s4a,
                ROUND(sum(m1a)/10000,2) m1a,
                ROUND(sum(m2a)/10000,2) m2a,
                ROUND(sum(m3a)/10000,2) m3a,
                ROUND(sum(m4a)/10000,2) m4a,
                ROUND(sum(m5a)/10000,2) m5a,
                ROUND(sum(m6a)/10000,2) m6a,
                ROUND(sum(m7a)/10000,2) m7a,
                ROUND(sum(m8a)/10000,2) m8a,
                ROUND(sum(m9a)/10000,2) m9a,
                ROUND(sum(m10a)/10000,2) m10a,
                ROUND(sum(m11a)/10000,2) m11a,
                ROUND(sum(m12a)/10000,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 areafullname, case when t.type = '区域' then 0 else 1 end