kpt_xdmy/doc/eqzhibiao/getChooseMonthWX.sql

SELECT 
sum(tem.asave) asave  ,
sum(tem.monthBudget) monthBudget  ,
sum(tem.monthLimit) monthLimit  ,
sum(tem.qmonthBudget) qmonthBudget  ,
sum(tem.sumPrice) sumPrice  ,
avg(tem.oneCowAsave) oneCowAsave  ,
avg(tem.oneCowBudget) oneCowBudget  ,
avg(tem.oneCowLimit) oneCowLimit  ,
avg(tem.oneCowQmonthBudget) oneCowQmonthBudget  ,
avg(tem.oneCowSumPrice) oneCowSumPrice  ,
tem.ftype  ftype  ,
tem.pastureId  pastureId  ,
tem.realReason  realReason  ,
tem.sort  sort  ,
 case
        when pp.name is null then
        case when pp.center  is null then '集团'
        else if(pp.region is null,pp.center,pp.region)
        end
        else pp.name 
    end pastureName
 from
(SELECT
 '现代牧业' pastureName,
 20 sort,
'维修费（万元）' ftype,
ROUND(sum(qq.monthBudget),2) monthBudget,  #预算
ROUND(sum(qq.monthLimit),2)  monthLimit,	 #内控
ROUND(sum(qq.sumPrice)/10000,2)  sumPrice,    #迄今实际
ROUND( sum(qq.monthLimit)/qq.sumDay*qq.realDay,2)    qmonthBudget, #迄今内控
ROUND( sum(qq.monthLimit)/qq.sumDay*qq.realDay-(sum(qq.sumPrice)/10000),2) asave, #实际节约
ROUND(1+(( sum(qq.monthLimit)/qq.sumDay*qq.realDay-(sum(qq.sumPrice)/10000))/(sum(qq.monthLimit)/qq.sumDay*qq.realDay)),2) realReason,#实际达成率
IFNULL(ROUND(sum(qq.monthBudget)/sum(qq.cowSumBudgetMonth)*10000,2),0)  oneCowBudget, #单头牛预算
IFNULL(ROUND(sum(qq.monthLimit)/sum(qq.cowSumBudgetMonth)*10000,2),0) oneCowLimit,	#单头牛内控
IFNULL(ROUND((sum(qq.monthLimit)/qq.sumDay*qq.realDay)/(sum(qq.cowSumBudgetMonth)/qq.sumDay*qq.realDay)*10000,2),0) oneCowQmonthBudget,	#单头牛迄今内控
IFNULL(ROUND(sum(qq.sumPrice)/(sum(qq.cowSum)/qq.sumDay*qq.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际
IFNULL(ROUND((sum(qq.monthLimit)/qq.sumDay*qq.realDay-(sum(qq.sumPrice)))/(sum(qq.cowSum)/qq.sumDay*qq.realDay),2),0) oneCowAsave,#单头牛迄今实际节约
18 pastureId
FROM (SELECT IFNULL((SELECT SUM(monthBudget)monthBudget FROM `month_budget`  WHERE concat(budgetMonth,'-','01') >= tem.startTime   
AND concat(budgetMonth,'-','01') < tem.endTime  AND feeType='维修费' AND (SELECT iscal FROM department d WHERE d.id=month_budget.`deptId`)= 0),0)monthBudget,
IFNULL((SELECT SUM(monthLimit)monthLimit FROM `month_budget`  WHERE concat(budgetMonth,'-','01') >= tem.startTime   
AND concat(budgetMonth,'-','01') < tem.endTime AND feeType='维修费' AND (SELECT iscal FROM department d WHERE d.id=month_budget.`deptId`)= 0 ),0)monthLimit,
IFNULL((SELECT SUM(cowSumBudgetMonth)cowSumBudgetMonth FROM `month_budget`  WHERE concat(budgetMonth,'-','01') >= tem.startTime   
AND concat(budgetMonth,'-','01') < tem.endTime AND feeType='维修费' AND (SELECT iscal FROM department d WHERE d.id=month_budget.`deptId`)= 0 ),0)cowSumBudgetMonth,
IFNULL((SELECT sum(fs.`cowNum`) FROM  feed_sum fs INNER JOIN pasture p ON p.id = fs.pastureId WHERE  fs.`feedDay` >= tem.startTime  and fs.`feedDay` <= tem.endTime),0) cowSum,
tem.startTime,
tem.endTime,
#DATEDIFF(DATE_ADD(LAST_DAY(tem.endTime),INTERVAL 1 DAY),tem.startTime) sumDay,#当前总天数
#DATEDIFF(tem.endTime,tem.startTime) realDay,#到当前选择的天数
DATEDIFF(LAST_DAY(tem.endTime),CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))+1 sumDay,
if (DATEDIFF(tem.endTime,CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))=0,DATEDIFF(LAST_DAY(tem.endTime),CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))+1,DATEDIFF(tem.endTime,CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))) realDay,
tem.sumPrice
 FROM(SELECT
  ROUND(IFNULL(SUM( pu.`sumPrice` - pu.`quitNumber` * pu.`price`),0),2) sumPrice,
   CONCAT(DATE_FORMAT(CONCAT(LEFT(?,7),'-01'),'%Y-%m'),'-01') startTime,
	IF(RIGHT(?,2)='00',CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01'),DATE_ADD(?,INTERVAL 1 DAY)) endTime
FROM
  partuse pu
  INNER JOIN bigpartuse bpu
    ON pu.`bigId` = bpu.`id`	 
WHERE  bpu.`useType` <> 3 AND bpu.`receiveTime` >= CONCAT(DATE_FORMAT(CONCAT(LEFT(?,7),'-01'),'%Y-%m'),'-01')
AND bpu.`receiveTime`  < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01')
  AND (
    pu.`useTypeV` = '维修'
    OR pu.`useTypeV` = '保养'
  ) AND (SELECT iscal FROM department d WHERE d.id=bpu.`departmentId`)= 0
  GROUP BY LEFT(bpu.`receiveTime`,7) ) tem) qq
  UNION ALL 
  SELECT 
   p.name pastureName,
 p.sort,
'维修费（万元）' ftype,
ROUND(qq.monthBudget,2) monthBudget,  #预算
ROUND(qq.monthLimit,2)  monthLimit,	 #内控
ROUND(qq.sumPrice/10000,2)  sumPrice,    #迄今实际
ROUND( qq.monthLimit/qq.sumDay*qq.realDay,2)    qmonthBudget, #迄今内控
ROUND( qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice/10000),2) asave, #实际节约
ROUND(1+(( qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice/10000))/(qq.monthLimit/qq.sumDay*qq.realDay)),2) realReason,#实际达成率
IFNULL(ROUND(qq.monthBudget/qq.cowSumBudgetMonth*10000,2),0)  oneCowBudget, #单头牛预算
IFNULL(ROUND(qq.monthLimit/qq.cowSumBudgetMonth*10000,2),0) oneCowLimit,	#单头牛内控
IFNULL(ROUND((qq.monthLimit/qq.sumDay*qq.realDay)/(qq.cowSumBudgetMonth/qq.sumDay*qq.realDay)*10000,2),0) oneCowQmonthBudget,	#单头牛迄今内控
IFNULL(ROUND(qq.sumPrice/(qq.cowSum/qq.sumDay*qq.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际
IFNULL(ROUND((qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice))/(qq.cowSum/qq.sumDay*qq.realDay),2),0) oneCowAsave,#单头牛迄今实际节约
 qq.pastureId
FROM 
(SELECT  tem.pastureId,IFNULL((SELECT SUM(monthBudget)monthBudget FROM `month_budget`  WHERE concat(budgetMonth,'-','01') >= tem.startTime   
AND concat(budgetMonth,'-','01') < tem.endTime AND pastureId = tem.pastureId  AND feeType='维修费' AND pastureId = tem.pastureId AND (SELECT iscal FROM department d WHERE d.id=month_budget.`deptId`)= 0),0)monthBudget,
IFNULL((SELECT SUM(monthLimit)monthLimit FROM `month_budget`  WHERE concat(budgetMonth,'-','01') >= tem.startTime   
AND concat(budgetMonth,'-','01') < tem.endTime AND feeType='维修费' AND pastureId = tem.pastureId AND (SELECT iscal FROM department d WHERE d.id=month_budget.`deptId`)= 0 ),0)monthLimit,
IFNULL((SELECT SUM(cowSumBudgetMonth)cowSumBudgetMonth FROM `month_budget`  WHERE concat(budgetMonth,'-','01') >= tem.startTime   
AND concat(budgetMonth,'-','01') < tem.endTime AND feeType='维修费' AND pastureId = tem.pastureId AND (SELECT iscal FROM department d WHERE d.id=month_budget.`deptId`)= 0 ),0)cowSumBudgetMonth,
IFNULL((SELECT sum(fs.`cowNum`) FROM  feed_sum fs INNER JOIN pasture p ON p.id = fs.pastureId WHERE p.id= tem.pastureId and fs.`feedDay` >= tem.startTime  and fs.`feedDay` <= tem.endTime),0) cowSum,
tem.startTime,
tem.endTime,
#DATEDIFF(DATE_ADD(LAST_DAY(tem.endTime),INTERVAL 1 DAY),tem.startTime) sumDay,#当前总天数
#DATEDIFF(tem.endTime,tem.startTime) realDay,#到当前选择的天数
DATEDIFF(LAST_DAY(tem.endTime),CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))+1 sumDay,
if (DATEDIFF(tem.endTime,CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))=0,DATEDIFF(LAST_DAY(tem.endTime),CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))+1,DATEDIFF(tem.endTime,CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))) realDay,
tem.sumPrice
 FROM(SELECT 
 bpu.pastureId,
  ROUND(IFNULL(SUM( pu.`sumPrice` - pu.`quitNumber` * pu.`price`),0),2) sumPrice,
     CONCAT(DATE_FORMAT(CONCAT(LEFT(?,7),'-01'),'%Y-%m'),'-01') startTime,
	IF(RIGHT(?,2)='00',CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01'),DATE_ADD(?,INTERVAL 1 DAY)) endTime
FROM
  partuse pu
  INNER JOIN bigpartuse bpu
    ON pu.`bigId` = bpu.`id`	 
WHERE  bpu.`useType` <> 3 AND bpu.`receiveTime` >= CONCAT(DATE_FORMAT(CONCAT(LEFT(?,7),'-01'),'%Y-%m'),'-01')
AND bpu.`receiveTime`  < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01')
  AND (
    pu.`useTypeV` = '维修'
    OR pu.`useTypeV` = '保养'
  ) 
	AND (SELECT iscal FROM department d WHERE d.id=bpu.`departmentId`)= 0
	AND  (bpu.pastureId = ? OR ? in ('18',''))
AND (bpu.departmentId = ? or ? = '')  
  GROUP BY bpu.pastureId ) tem) qq
  INNER JOIN pasture p
  ON p.id = qq.pastureId
)tem 
left join 
pasture  pp on pp.id  = tem.pastureId
where pp.id <>18
group by pp.center,pp.region,pp.name  with rollup