kpt_xdmy/doc/mbudget/getChooseEqMonthBudgetListSumAll.sql

update apisql 
set sqlstr =
"select
	'现代牧业' pastureName,
	20 sort,
	#RIGHT(rr.wmonth,2) num,
  '设备指标（万元）' ftype,
	ROUND(sum(rr.monthBudget), 2) monthBudget,
	#预算
	ROUND(sum(rr.monthLimit), 2) monthLimit,
	#内控
	ROUND(sum(rr.sumPrice), 2) sumPrice,
	#迄今实际

	ROUND( sum(rr.monthLimit)/ rr.sumDay * rr.realDay, 2) qmonthBudget,
	#迄今内控
	ROUND( sum(rr.monthLimit)/ rr.sumDay * rr.realDay-(sum(rr.sumPrice)), 2) asave,
	#实际节约
	ifnull(ROUND(1 +(( sum(rr.monthLimit)/ rr.sumDay * rr.realDay-(sum(rr.sumPrice)))/(sum(rr.monthLimit)/ rr.sumDay * rr.realDay)), 2), 0) realReason,
	#实际达成率
	IFNULL(ROUND(sum(rr.monthBudget)/ sum(rr.cowSumBudgetMonth), 2), 0) oneCowBudget,
	#单头牛预算
	-- IFNULL(ROUND(sum(rr.monthLimit)/sum(rr.cowSumBudgetMonth),2),0) oneCowLimit,	#单头牛内控
	sum(rr.oneCowLimit) oneCowLimit,
	-- IFNULL(ROUND((sum(rr.monthLimit)/rr.sumDay*rr.realDay)/(sum(rr.cowSumBudgetMonth)/rr.sumDay*rr.realDay),2),0) oneCowQmonthBudget,
	sum(rr.oneCowQmonthBudget) oneCowQmonthBudget,
	#单头牛迄今内控
	sum(rr.oneCowSumPrice) oneCowSumPrice,
	-- IFNULL(ROUND(sum(rr.sumPrice)/(sum(rr.cowSum)/rr.sumDay*rr.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际
	-- IFNULL(ROUND((sum(rr.monthLimit)/rr.sumDay*rr.realDay-(sum(rr.sumPrice)))/(sum(rr.cowSum)/rr.sumDay*rr.realDay),2),0) oneCowAsave,
	sum(rr.oneCowAsave)oneCowAsave,
	18 pastureId
from
	(
-- lag11
	select
		'维修费（万元）' 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.sumDay,
		qq.realDay,
		qq.cowSumBudgetMonth,
		qq.cowSum
	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(monthBudget)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,
			#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(DATE_ADD(?, interval -1 day), '%Y-%m'), '-01')
					and bpu.`receiveTime` < CONCAT(DATE_FORMAT(DATE_ADD(DATE_ADD(?, interval -1 day), interval 1 year), '%Y-%m'), '-01')
						and (
    pu.`useTypeV` = '维修'
							or pu.`useTypeV` = '保养'
  )
						and (
						select
							iscal
						from
							department d
						where
							d.id = bpu.`departmentId`)= 0
   ) tem) qq
union all
-- lag12
	select
		'电费（万元）' 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.cowSumBudgetMonth / qq.sumDay * qq.realDay), 2), 0) oneCowSumPrice,
		#单头牛的迄今实际
		IFNULL(ROUND((qq.monthLimit / qq.sumDay * qq.realDay-(qq.sumPrice))/(qq.cowSumBudgetMonth / qq.sumDay * qq.realDay), 2), 0) oneCowAsave,
		#单头牛迄今实际节约
		qq.sumDay,
		qq.realDay,
		qq.cowSum,
		qq.cowSumBudgetMonth
	from
		(
		select
			IFNULL((select SUM(monthBudget)monthBudget from `month_budget` where concat(budgetMonth, '-', '01') >= tem.startTime   
and concat(budgetMonth, '-', '01') < tem.endTime and feeType = '电费'), 0)monthBudget,
			IFNULL((select SUM(monthLimit)monthLimit from `month_budget` where concat(budgetMonth, '-', '01') >= tem.startTime   
and concat(budgetMonth, '-', '01') < tem.endTime and feeType = '电费' ), 0)monthLimit,
			IFNULL((select SUM(monthBudget)cowSumBudgetMonth from `month_budget` where concat(budgetMonth, '-', '01') >= tem.startTime   
and concat(budgetMonth, '-', '01') < tem.endTime and feeType = '饲养头数月预算' ), 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,
			#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(e.elecConsumption * e.`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
				electricity e
			inner join `measure` m
    on
				m.id = e.measureId
			where
				m.meterType = '电表'
				and m.useType = '2级表'
				and e.date >= CONCAT(DATE_FORMAT(CONCAT(left(?, 7), '-01'), '%Y-%m'), '-01')
					and e.date < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(left(?, 7), '-01'), interval 1 month), '%Y-%m'), '-01')
  
   ) tem) qq
union all
-- lag13
	select
		'水费（万元）' 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.cowSumBudgetMonth / qq.sumDay * qq.realDay), 2), 0) oneCowSumPrice,
		#单头牛的迄今实际
		IFNULL(ROUND((qq.monthLimit / qq.sumDay * qq.realDay-(qq.sumPrice))/(qq.cowSumBudgetMonth / qq.sumDay * qq.realDay), 2), 0) oneCowAsave,
		#单头牛迄今实际节约
		qq.sumDay,
		qq.realDay,
		qq.cowSum,
		qq.cowSumBudgetMonth
	from
		(
		select
			IFNULL((select SUM(monthBudget)monthBudget from `month_budget` where concat(budgetMonth, '-', '01') >= tem.startTime   
and concat(budgetMonth, '-', '01') < tem.endTime and feeType = '水费'), 0)monthBudget,
			IFNULL((select SUM(monthLimit)monthLimit from `month_budget` where concat(budgetMonth, '-', '01') >= tem.startTime   
and concat(budgetMonth, '-', '01') < tem.endTime and feeType = '水费' ), 0)monthLimit,
			IFNULL((select SUM(monthBudget)cowSumBudgetMonth from `month_budget` where concat(budgetMonth, '-', '01') >= tem.startTime   
and concat(budgetMonth, '-', '01') < tem.endTime and feeType = '饲养头数月预算' ), 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,
			#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(e.waterConsumption * e.`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
				water e
			inner join `measure` m
    on
				m.id = e.measureId
			where
				m.meterType = '水表'
				and m.useType = '2级表'
				and e.date >= CONCAT(DATE_FORMAT(CONCAT(left(?, 7), '-01'), '%Y-%m'), '-01')
					and e.date < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(left(?, 7), '-01'), interval 1 month), '%Y-%m'), '-01')
  
  ) tem) qq
union all
-- lag14
	select
		'燃动费（万元）' 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.cowSumBudgetMonth / qq.sumDay * qq.realDay), 2), 0) oneCowSumPrice,
		#单头牛的迄今实际
		IFNULL(ROUND((qq.monthLimit / qq.sumDay * qq.realDay-(qq.sumPrice))/(qq.cowSumBudgetMonth / qq.sumDay * qq.realDay), 2), 0) oneCowAsave,
		#单头牛迄今实际节约
		qq.sumDay,
		qq.realDay,
		qq.cowSum,
		qq.cowSumBudgetMonth
	from
		(
		select
			IFNULL((select SUM(monthBudget)monthBudget from `month_budget` where concat(budgetMonth, '-', '01') >= tem.startTime   
and concat(budgetMonth, '-', '01') < tem.endTime and feeType = '燃动费'), 0)monthBudget,
			IFNULL((select SUM(monthLimit)monthLimit from `month_budget` where concat(budgetMonth, '-', '01') >= tem.startTime   
and concat(budgetMonth, '-', '01') < tem.endTime and feeType = '燃动费' ), 0)monthLimit,
			IFNULL((select SUM(monthBudget)cowSumBudgetMonth from `month_budget` where concat(budgetMonth, '-', '01') >= tem.startTime   
and concat(budgetMonth, '-', '01') < tem.endTime and feeType = '饲养头数月预算' ), 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,
			#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(d.oilAmount * d.`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
				`diesel` d
			where
				d.selTime >= CONCAT(DATE_FORMAT(CONCAT(left(?, 7), '-01'), '%Y-%m'), '-01')
					and d.selTime < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(left(?, 7), '-01'), interval 1 month), '%Y-%m'), '-01')
  
 ) tem) qq) rr
union all 
  -- lag21
   select
	p.name,
	p.sort,
	'设备指标（万元）' ftype,
	ROUND(sum(rr.monthBudget), 2) monthBudget,
	#预算
	ROUND(sum(rr.monthLimit), 2) monthLimit,
	#内控
	ROUND(sum(rr.sumPrice), 2) sumPrice,
	#迄今实际

	ROUND( sum(rr.monthLimit)/ rr.sumDay * rr.realDay, 2) qmonthBudget,
	#迄今内控
	ROUND( sum(rr.monthLimit)/ rr.sumDay * rr.realDay-(sum(rr.sumPrice)), 2) asave,
	#实际节约
	ifnull(ROUND(1 +(( sum(rr.monthLimit)/ rr.sumDay * rr.realDay-(sum(rr.sumPrice)))/(sum(rr.monthLimit)/ rr.sumDay * rr.realDay)), 2), 0) realReason,
	#实际达成率
	IFNULL(ROUND(sum(rr.monthBudget)/ sum(rr.cowSumBudgetMonth), 2), 0) oneCowBudget,
	#单头牛预算
	IFNULL(ROUND(sum(rr.monthLimit)/ sum(rr.cowSumBudgetMonth), 2), 0) oneCowLimit,
	#单头牛内控
	IFNULL(ROUND((sum(rr.monthLimit)/ rr.sumDay * rr.realDay)/(sum(rr.cowSumBudgetMonth)/ rr.sumDay * rr.realDay), 2), 0) oneCowQmonthBudget,
	#单头牛迄今内控
	IFNULL(ROUND(sum(rr.sumPrice)/(sum(rr.cowSum)/ rr.sumDay * rr.realDay), 2), 0) oneCowSumPrice,
	#单头牛的迄今实际
	IFNULL(ROUND((sum(rr.monthLimit)/ rr.sumDay * rr.realDay-(sum(rr.sumPrice)))/(sum(rr.cowSum)/ rr.sumDay * rr.realDay), 2), 0) oneCowAsave,
	pastureId
from
	(
	select
		'维修费（万元）' 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.sumDay,
		qq.realDay,
		qq.cowSumBudgetMonth,
		qq.cowSum,
		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(monthBudget)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,
			#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
union all
-- lag22
	select
		'电费（万元）' 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.cowSumBudgetMonth / qq.sumDay * qq.realDay), 2), 0) oneCowSumPrice,
		#单头牛的迄今实际
		IFNULL(ROUND((qq.monthLimit / qq.sumDay * qq.realDay-(qq.sumPrice))/(qq.cowSumBudgetMonth / qq.sumDay * qq.realDay), 2), 0) oneCowAsave,
		#单头牛迄今实际节约
		qq.sumDay,
		qq.realDay,
		qq.cowSumBudgetMonth,
		qq.cowSum,
		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 feeType = '电费' and pastureId = tem.pastureId), 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 ), 0)monthLimit,
			IFNULL((select SUM(monthBudget)cowSumBudgetMonth from `month_budget` where concat(budgetMonth, '-', '01') >= tem.startTime   
and concat(budgetMonth, '-', '01') < tem.endTime and feeType = '饲养头数月预算' and pastureId = tem.pastureId ), 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,
			#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
				m.pastureId,
				ROUND(IFNULL(SUM(e.elecConsumption * e.`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
				electricity e
			inner join `measure` m
    on
				m.id = e.measureId
			where
				m.meterType = '电表'
				and m.useType = '2级表'
				and e.date >= CONCAT(DATE_FORMAT(CONCAT(left(?, 7), '-01'), '%Y-%m'), '-01')
					and e.date < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(left(?, 7), '-01'), interval 1 month), '%Y-%m'), '-01')
						and (m.pastureId = ?
							or ? in ('18', ''))
							and (m.departmentId = ?
								or ? = '')
						group by
							m.pastureId ) tem) qq
union all
-- lag23
	select
		'水费（万元）' 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.cowSumBudgetMonth / qq.sumDay * qq.realDay), 2), 0) oneCowSumPrice,
		#单头牛的迄今实际
		IFNULL(ROUND((qq.monthLimit / qq.sumDay * qq.realDay-(qq.sumPrice))/(qq.cowSumBudgetMonth / qq.sumDay * qq.realDay), 2), 0) oneCowAsave,
		#单头牛迄今实际节约
		qq.sumDay,
		qq.realDay,
		qq.cowSumBudgetMonth,
		qq.cowSum,
		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 feeType = '水费' and pastureId = tem.pastureId), 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 ), 0)monthLimit,
			IFNULL((select SUM(monthBudget)cowSumBudgetMonth from `month_budget` where concat(budgetMonth, '-', '01') >= tem.startTime   
and concat(budgetMonth, '-', '01') < tem.endTime and feeType = '饲养头数月预算' and pastureId = tem.pastureId ), 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,
			#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
				m.pastureId,
				ROUND(IFNULL(SUM(e.waterConsumption * e.`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
				water e
			inner join `measure` m
    on
				m.id = e.measureId
			where
				m.meterType = '水表'
				and m.useType = '2级表'
				and e.date >= CONCAT(DATE_FORMAT(CONCAT(left(?, 7), '-01'), '%Y-%m'), '-01')
					and e.date < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(left(?, 7), '-01'), interval 1 month), '%Y-%m'), '-01')
						and (m.pastureId = ?
							or ? in ('18', ''))
							and (m.departmentId = ?
								or ? = '')
						group by
							m.pastureId ) tem) qq
union all
-- lag24
	select
		'燃动费（万元）' 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.cowSumBudgetMonth / qq.sumDay * qq.realDay), 2), 0) oneCowSumPrice,
		#单头牛的迄今实际
		IFNULL(ROUND((qq.monthLimit / qq.sumDay * qq.realDay-(qq.sumPrice))/(qq.cowSumBudgetMonth / qq.sumDay * qq.realDay), 2), 0) oneCowAsave,
		#单头牛迄今实际节约
		qq.sumDay,
		qq.realDay,
		qq.cowSumBudgetMonth,
		qq.cowSum,
		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 feeType = '燃动费' and pastureId = tem.pastureId), 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 ), 0)monthLimit,
			IFNULL((select SUM(monthBudget)cowSumBudgetMonth from `month_budget` where concat(budgetMonth, '-', '01') >= tem.startTime   
and concat(budgetMonth, '-', '01') < tem.endTime and feeType = '饲养头数月预算' and pastureId = tem.pastureId ), 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,
			#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
				d.pastureId,
				ROUND(IFNULL(SUM(d.oilAmount * d.`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
				`diesel` d
			where
				d.selTime >= CONCAT(DATE_FORMAT(CONCAT(left(?, 7), '-01'), '%Y-%m'), '-01')
					and d.selTime < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(left(?, 7), '-01'), interval 1 month), '%Y-%m'), '-01')
						and (d.pastureId = ?
							or ? in ('18', ''))
							and (d.deptId = ?
								or ? = '')
						group by
							d.pastureId ) tem) qq
  
  ) rr
inner join pasture p
  on
	p.id = rr.pastureId
group by
	rr.pastureId
order by
	sort"
where sqlname ='getChooseEqMonthBudgetListSumAll'