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 t1.amount, MONTH (t1.period) point,ifnull(t4.sa_saleareaid,0) sa_saleareaid,'月' type
FROM sa_cashbill t1
		left join sys_enterprise_tradefield t3 on t1.sys_enterpriseid=t3.sys_enterpriseid and t1.siteid=t3.siteid
		left join sa_salearea t4 on t4.sa_saleareaid=t3.sa_saleareaid and t4.siteid=t3.siteid
WHERE YEAR (t1.period)=$year$ AND t1.siteid=$siteid$ AND t1.status in('审核') and t1.type=1 and ($where1$)) 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