kpt_xdmy/apiserver/service/home.go

package service

import (
	"fmt"
	"kpt.xdmy/apiserver/model"
	"kpt.xdmy/apiserver/model/home"
	"kpt.xdmy/pkg/util"
	"time"
)

func (s *Service) OverallIndex() (*home.OverallIndexResp, error) {
	resp := new(home.OverallIndexResp)
	monthBudgetList := make([]*home.MonthBudget, 0)
	err := s.d.DB.Raw(`SELECT
(
		sum( monthBudget )*(
			DATEDIFF(
				NOW(),
				CONCAT( YEAR ( NOW()), '-01-01' )) / DATEDIFF(
				CONCAT( YEAR ( NOW()), '-12-31' ),
			CONCAT( YEAR ( NOW()), '-01-01' )) 
		)) monthBudget,
	feeType 
FROM
	month_budget 
WHERE
	budgetMonth LIKE concat( '%', date_format( now(), '%Y' ), '%' ) 
GROUP BY
	feeType `).Find(&monthBudgetList).Error
	if err != nil {
		return resp, err
	}

	thisYearActualList := make([]*home.ThisYearActual, 0)
	err = s.d.DB.Raw(`select FYLX feeType,sum(HSL) sumPrice from feequery  where Date like  concat( '%',YEAR(NOW()),'%')   and FYLX in ('水费','燃动费','电费')  group by FYLX

UNION ALL

SELECT
  '维修费' feeType ,IFNULL(SUM( pu.sumPrice - pu.quitNumber * pu.price),0) sumPrice
FROM
  partuse pu
  INNER JOIN bigpartuse bpu
    ON pu.bigId = bpu.id	 
WHERE  bpu.useType <> 3  
  AND (
    pu.useTypeV = '维修'
    OR pu.useTypeV = '保养'
  ) AND (SELECT iscal FROM department d WHERE d.id=bpu.departmentId)= 0 AND  bpu.receiveTime BETWEEN CONCAT(YEAR(NOW()),'-01-01') AND CONCAT(YEAR(NOW()),'-12-31')
  AND (bpu.pastureid = (SELECT id FROM pasture WHERE name = '现代牧业') OR '现代牧业' = '现代牧业') and bpu.sterilisation = 0`).Find(&thisYearActualList).Error
	if err != nil {
		return resp, err
	}

	lastYearActualList := make([]*home.ThisYearActual, 0)
	err = s.d.DB.Raw(`select FYLX feeType,sum(HSL) sumPrice from feequery  where Date like  concat( '%',YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)),'%')  
                                                     and FYLX in ('水费','燃动费','电费')  group by FYLX

UNION ALL

SELECT
  '维修费' feeType ,IFNULL(SUM( pu.sumPrice - pu.quitNumber * pu.price),0) sumPrice
FROM
  partuse pu
  INNER JOIN bigpartuse bpu
    ON pu.bigId = bpu.id	 
WHERE  bpu.useType <> 3  
  AND (
    pu.useTypeV = '维修'
    OR pu.useTypeV = '保养'
  ) AND (SELECT iscal FROM department d WHERE d.id=bpu.departmentId)= 0 AND  bpu.receiveTime BETWEEN CONCAT(YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)),'-01-01') AND CONCAT(YEAR(NOW()),'-12-31')
  AND (bpu.pastureid = (SELECT id FROM pasture WHERE name = '现代牧业') OR '现代牧业' = '现代牧业') and bpu.sterilisation = 0`).Find(&lastYearActualList).Error
	if err != nil {
		return resp, err
	}

	feedSum := new(model.FeedSum)
	err = s.d.DB.Raw(` select sum(cowNum) cowNum  from feed_sum `).First(feedSum).Error
	if err != nil {
		return resp, err
	}

	var repair = new(home.Overall)
	var water = new(home.Overall)
	var electricity = new(home.Overall)
	var ignition = new(home.Overall)

	for _, monthBudget := range monthBudgetList {
		if monthBudget.FeeType == "维修费" {
			repair.Budget = util.Round(monthBudget.MonthBudget/1000000, 2)
		} else if monthBudget.FeeType == "电费" {
			electricity.Budget = util.Round(monthBudget.MonthBudget/1000000, 2)
		} else if monthBudget.FeeType == "水费" {
			water.Budget = util.Round(monthBudget.MonthBudget/1000000, 2)
		} else if monthBudget.FeeType == "燃动费" {
			ignition.Budget = util.Round(monthBudget.MonthBudget/1000000, 2)
		}
	}
	cowNum := float64(feedSum.CowNum)
	for _, item := range thisYearActualList {
		if item.FeeType == "维修费" {
			repair.Actual = util.Round(item.SumPrice/1000000, 2)
			repair.SingleCow = util.Round(item.SumPrice/cowNum, 2)
		} else if item.FeeType == "电费" {
			electricity.Actual = util.Round(item.SumPrice/1000000, 2)
			electricity.SingleCow = util.Round(item.SumPrice/cowNum, 2)
		} else if item.FeeType == "水费" {
			water.Actual = util.Round(item.SumPrice/1000000, 2)
			water.SingleCow = util.Round(item.SumPrice/cowNum, 2)
		} else if item.FeeType == "燃动费" {
			ignition.Actual = util.Round(item.SumPrice/1000000, 2)
			ignition.SingleCow = util.Round(item.SumPrice/cowNum, 2)
		}
	}
	for _, item := range lastYearActualList {
		if item.FeeType == "维修费" {
			repair.LastActual = util.Round(item.SumPrice/1000000, 2)
		} else if item.FeeType == "电费" {
			electricity.LastActual = util.Round(item.SumPrice/1000000, 2)
		} else if item.FeeType == "水费" {
			water.LastActual = util.Round(item.SumPrice/1000000, 2)
		} else if item.FeeType == "燃动费" {
			ignition.LastActual = util.Round(item.SumPrice/1000000, 2)
		}
	}

	resp.Repair = repair
	resp.Electricity = electricity
	resp.Water = water
	resp.Ignition = ignition
	return resp, nil
}

func (s *Service) CentralIndex() (*home.CentralIndexResp, error) {
	resp := new(home.CentralIndexResp)

	pastureList := make([]*model.Pasture, 0)
	err := s.d.DB.Select("center").Where(" center is not null or center != '' ").Group("center").Order("center_id").Find(&pastureList).Error
	if err != nil {
		return resp, err
	}

	centralIndexList := make([]*home.CentralIndex, 0)
	for _, pasture := range pastureList {
		centralIndexList = append(centralIndexList, &home.CentralIndex{
			Central: pasture.Center,
		})
	}

	monthBudgetList := make([]*home.MonthBudget, 0)
	err = s.d.DB.Raw(`SELECT
	(
		sum( monthBudget )*(
			DATEDIFF(
				NOW(),
				CONCAT( YEAR ( NOW()), '-01-01' )) / DATEDIFF(
				CONCAT( YEAR ( NOW()), '-12-31' ),
			CONCAT( YEAR ( NOW()), '-01-01' )) 
		)) monthBudget,
	p.center 
FROM
	month_budget mb
	JOIN pasture p ON p.id = mb.pastureId 
WHERE
	budgetMonth LIKE concat( '%', date_format( now(), '%Y' ), '%' ) 
	AND feeType IN ( '维修费', '电费', '水费', '燃动费', '柴油费', '生物质颗粒', '汽油用量', '煤炭用量' ) 
GROUP BY
	center `).Find(&monthBudgetList).Error
	if err != nil {
		return resp, err
	}

	thisYearActualList := make([]*home.ThisYearActual, 0)
	err = s.d.DB.Raw(`select  sum(t.sumPrice) sumPrice,p.center  from (
	select pastureid,FYLX feeType,sum(HSL) sumPrice from feequery  where Date like  concat( '%',YEAR(NOW()),'%')   and FYLX in ('水费','燃动费','电费','柴油费')  group by FYLX,pastureid 
UNION ALL
SELECT
  bpu.pastureid,'维修费' feeType ,IFNULL(SUM( pu.sumPrice - pu.quitNumber * pu.price),0) sumPrice
FROM
  partuse pu
  INNER JOIN bigpartuse bpu
    ON pu.bigId = bpu.id	 
WHERE  bpu.useType <> 3  
  AND (
    pu.useTypeV = '维修'
    OR pu.useTypeV = '保养'
  ) AND (SELECT iscal FROM department d WHERE d.id=bpu.departmentId)= 0 AND  bpu.receiveTime BETWEEN CONCAT(YEAR(NOW()),'-01-01') AND CONCAT(YEAR(NOW()),'-12-31')
  AND (bpu.pastureid = (SELECT id FROM pasture WHERE name = '现代牧业') OR '现代牧业' = '现代牧业') and bpu.sterilisation = 0  group by bpu.pastureId)  t  
    join pasture p on p.id = t.pastureId   GROUP BY p.center`).Find(&thisYearActualList).Error
	if err != nil {
		return resp, err
	}

	nowStr := time.Now().Format("2006-01-02")
	now, _ := time.Parse("2006-01-02", nowStr)
	// 获取当年的一月一号
	year := now.Year()
	start := time.Date(year, 1, 1, 0, 0, 0, 0, time.UTC)
	fmt.Println(start)
	end := time.Date(year, 12, 31, 0, 0, 0, 0, time.UTC)
	fmt.Println(end)
	real := now.Sub(start)
	sum := end.Sub(start)
	sumDay := sum.Hours() / 24
	realDay := real.Hours() / 24
	fmt.Println(realDay, sumDay)

	for _, central := range centralIndexList {
		for _, monthBudget := range monthBudgetList {
			if monthBudget.Center == central.Central {
				central.Budget = util.Round(monthBudget.MonthBudget, 2)
				break
			}
		}
		for _, item := range thisYearActualList {
			if item.Center == central.Central {
				central.Actual = util.Round(item.SumPrice, 2)
				break
			}
		}
		if central.Budget > 0 && central.Actual > 0 {
			central.Complete = util.Round(1+(central.Budget/sumDay*realDay-(central.Actual))/central.Budget*100, 2)
			central.Budget = util.Round(central.Budget/1000000, 2)
			central.Actual = util.Round(central.Actual/1000000, 2)
		}
	}
	resp.Central = append(resp.Central, centralIndexList...)
	return resp, nil
}

func (s *Service) GetExpense(pastureName string) (*home.GetExpenseResp, error) {
	resp := new(home.GetExpenseResp)
	pasture := new(model.Pasture)
	if pastureName != "现代牧业" && pastureName != "" {
		err := s.d.DB.Where(" name = ? ", pastureName).First(&pasture).Error
		if err != nil {
			return resp, err
		}
	}

	monthBudgetList := make([]*home.MonthBudget, 0)
	err := s.d.DB.Debug().Raw(`SELECT
(
		sum( monthBudget )*(
			DATEDIFF(
				NOW(),
				CONCAT( YEAR ( NOW()), '-01-01' )) / DATEDIFF(
				CONCAT( YEAR ( NOW()), '-12-31' ),
			CONCAT( YEAR ( NOW()), '-01-01' )) 
		)) monthBudget,
	feeType ,p.center
FROM
	month_budget mb join pasture p on p.id = mb.pastureId
WHERE
	budgetMonth LIKE concat( '%', date_format( now(), '%Y' ), '%' )  and ( ? = 0  or pastureId = ?)
GROUP BY
	feeType,p.center `, pasture.ID, pasture.ID).Find(&monthBudgetList).Error
	if err != nil {
		return resp, err
	}

	thisYearActualList := make([]*home.ThisYearActual, 0)
	err = s.d.DB.Debug().Raw(`select FYLX feeType,sum(HSL) sumPrice,p.center from feequery  fq  join pasture p on p.id = fq.pastureId   
  where Date like  concat( '%',YEAR(NOW()),'%')   and ( ? = 0  or pastureId = ?)
                                                     and FYLX in ('水费','燃动费','电费')  group by FYLX,p.center

UNION ALL

SELECT
  '维修费' feeType ,IFNULL(SUM( pu.sumPrice - pu.quitNumber * pu.price),0) sumPrice,p.center
FROM
  partuse pu
  INNER JOIN bigpartuse bpu
    ON pu.bigId = bpu.id	
	join pasture p on p.id = bpu.pastureId
WHERE  bpu.useType <> 3  
  AND (
    pu.useTypeV = '维修'
    OR pu.useTypeV = '保养'
  ) AND (SELECT iscal FROM department d WHERE d.id=bpu.departmentId)= 0 AND  bpu.receiveTime BETWEEN CONCAT(YEAR(NOW()),'-01-01') 
      AND CONCAT(YEAR(NOW()),'-12-31')
  AND  ( ? = 0  or pastureId = ?) and bpu.sterilisation = 0 group by p.center`, pasture.ID, pasture.ID, pasture.ID, pasture.ID).Find(&thisYearActualList).Error
	if err != nil {
		return resp, err
	}

	lastYearActualList := make([]*home.ThisYearActual, 0)
	err = s.d.DB.Debug().Raw(`select FYLX feeType,sum(HSL) sumPrice,p.center from feequery  fq  join pasture p on p.id = fq.pastureId  where Date like  concat( '%',YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)),'%')  
                                                     and FYLX in ('水费','燃动费','电费')   and ( ? = 0  or pastureId = ?)  group by FYLX,p.center

UNION ALL

SELECT
  '维修费' feeType ,IFNULL(SUM( pu.sumPrice - pu.quitNumber * pu.price),0) sumPrice,p.center
FROM
  partuse pu
  INNER JOIN bigpartuse bpu
    ON pu.bigId = bpu.id	
	join pasture p on p.id = bpu.pastureId
WHERE  bpu.useType <> 3  
  AND (
    pu.useTypeV = '维修'
    OR pu.useTypeV = '保养'
  ) AND (SELECT iscal FROM department d WHERE d.id=bpu.departmentId)= 0 AND  bpu.receiveTime BETWEEN CONCAT(YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)),'-01-01') AND CONCAT(YEAR(NOW()),'-12-31')
  AND  ( ? = 0  or pastureId = ?)  and bpu.sterilisation = 0 group by p.center`, pasture.ID, pasture.ID, pasture.ID, pasture.ID).Find(&lastYearActualList).Error
	if err != nil {
		return resp, err
	}

	feedSum := new(model.FeedSum)
	err = s.d.DB.Raw(` select sum(cowNum) cowNum  from feed_sum `).First(feedSum).Error
	if err != nil {
		return resp, err
	}

	pastureList := make([]*model.Pasture, 0)
	err = s.d.DB.Select("center").Where(" center is not null or center != '' ").Group("center").Order("center_id").Find(&pastureList).Error
	if err != nil {
		return resp, err
	}

	var repairList = make([]*home.Overall, 0)
	var waterList = make([]*home.Overall, 0)
	var electricityList = make([]*home.Overall, 0)
	var ignitionList = make([]*home.Overall, 0)
	for _, item := range pastureList {
		repairList = append(repairList, &home.Overall{Center: item.Center})
		waterList = append(waterList, &home.Overall{Center: item.Center})
		electricityList = append(electricityList, &home.Overall{Center: item.Center})
		ignitionList = append(ignitionList, &home.Overall{Center: item.Center})
	}

	for _, monthBudget := range monthBudgetList {
		if monthBudget.FeeType == "维修费" {
			for _, repair := range repairList {
				if repair.Center == monthBudget.Center {
					repair.Budget = util.Round(monthBudget.MonthBudget/1000000, 2)
					break
				}
			}
		} else if monthBudget.FeeType == "电费" {
			for _, electricity := range electricityList {
				if electricity.Center == monthBudget.Center {
					electricity.Budget = util.Round(monthBudget.MonthBudget/1000000, 2)
					break
				}
			}
		} else if monthBudget.FeeType == "水费" {
			for _, water := range waterList {
				if water.Center == monthBudget.Center {
					water.Budget = util.Round(monthBudget.MonthBudget/1000000, 2)
					break
				}
			}
		} else if monthBudget.FeeType == "燃动费" {
			for _, ignition := range ignitionList {
				if ignition.Center == monthBudget.Center {
					ignition.Budget = util.Round(monthBudget.MonthBudget/1000000, 2)
					break
				}
			}
		}
	}
	cowNum := float64(feedSum.CowNum)
	for _, item := range thisYearActualList {
		if item.FeeType == "维修费" {
			for _, repair := range repairList {
				if repair.Center == item.Center {
					repair.Actual = util.Round(item.SumPrice/1000000, 2)
					repair.SingleCow = util.Round(item.SumPrice/cowNum, 2)
					break
				}
			}
		} else if item.FeeType == "电费" {
			//electricity.Actual = util.Round(item.SumPrice, 2)
			//electricity.Actual = util.Round(electricity.Actual/cowNum, 2)
			for _, electricity := range electricityList {
				if electricity.Center == item.Center {
					electricity.Actual = util.Round(item.SumPrice/1000000, 2)
					electricity.SingleCow = util.Round(item.SumPrice/cowNum, 2)
					break
				}
			}
		} else if item.FeeType == "水费" {
			//water.Actual = util.Round(item.SumPrice, 2)
			//water.Actual = util.Round(water.Actual/cowNum, 2)
			for _, water := range waterList {
				if water.Center == item.Center {
					water.Actual = util.Round(item.SumPrice/1000000, 2)
					water.SingleCow = util.Round(item.SumPrice/cowNum, 2)
					break
				}
			}
		} else if item.FeeType == "燃动费" {
			//ignition.Actual = util.Round(item.SumPrice, 2)
			//ignition.Actual = util.Round(ignition.Actual/cowNum, 2)
			for _, ignition := range ignitionList {
				if ignition.Center == item.Center {
					//ignition.Actual = util.Round(item.SumPrice, 2)
					ignition.SingleCow = util.Round(item.SumPrice/cowNum, 2)
					ignition.Actual = util.Round(item.SumPrice/1000000, 2)
					break
				}
			}
		}
	}

	for _, item := range lastYearActualList {
		if item.FeeType == "维修费" {
			for _, repair := range repairList {
				if repair.Center == item.Center {
					repair.LastActual = util.Round(item.SumPrice/1000000, 2)
					break
				}
			}
			//repair.LastActual = util.Round(item.SumPrice, 2)
		} else if item.FeeType == "电费" {
			//electricity.LastActual = util.Round(item.SumPrice, 2)
			for _, electricity := range electricityList {
				if electricity.Center == item.Center {
					electricity.LastActual = util.Round(item.SumPrice/1000000, 2)
					break
				}
			}
		} else if item.FeeType == "水费" {
			//water.LastActual = util.Round(item.SumPrice, 2)
			for _, water := range waterList {
				if water.Center == item.Center {
					water.LastActual = util.Round(item.SumPrice/1000000, 2)
					break
				}
			}
		} else if item.FeeType == "燃动费" {
			//ignition.LastActual = util.Round(item.SumPrice, 2)
			for _, ignition := range ignitionList {
				if ignition.Center == item.Center {
					ignition.LastActual = util.Round(item.SumPrice/1000000, 2)
					break
				}
			}
		}
	}

	resp.Repair = append(resp.Repair, repairList...)
	resp.Electricity = append(resp.Electricity, electricityList...)
	resp.Water = append(resp.Water, waterList...)
	resp.Ignition = append(resp.Ignition, ignitionList...)
	return resp, nil
}

func (s *Service) GetPastureIndex(pastureName string) (*home.GetPastureIndexResp, error) {
	resp := new(home.GetPastureIndexResp)

	monthBudget := new(home.MonthBudget)
	err := s.d.DB.Raw(`SELECT
		sum( monthBudget ) monthBudget
FROM
	month_budget mb
	JOIN pasture p ON p.id = mb.pastureId 
WHERE
	budgetMonth LIKE concat( '%', date_format( now(), '%Y' ), '%' ) 
	AND feeType IN ( '维修费', '电费', '水费', '燃动费', '柴油费', '生物质颗粒', '汽油用量', '煤炭用量' )  and p.name = ? `, pastureName).Find(&monthBudget).Error
	if err != nil {
		return resp, err
	}

	thisYearActual := new(home.ThisYearActual)
	err = s.d.DB.Raw(`select  sum(t.sumPrice) sumPrice from (
	select pastureid,FYLX feeType,sum(HSL) sumPrice from feequery  fq join pasture p on p.id = fq.pastureId where Date like  concat( '%',YEAR(NOW()),'%')  
	                                                               and FYLX in ('水费','燃动费','电费','柴油费')  and p.name = ?
UNION ALL
SELECT
  bpu.pastureid,'维修费' feeType ,IFNULL(SUM( pu.sumPrice - pu.quitNumber * pu.price),0) sumPrice
FROM
  partuse pu
  INNER JOIN bigpartuse bpu
    ON pu.bigId = bpu.id
 join pasture p on p.id = bpu.pastureId
WHERE  bpu.useType <> 3  
  AND (
    pu.useTypeV = '维修'
    OR pu.useTypeV = '保养'
  ) AND (SELECT iscal FROM department d WHERE d.id=bpu.departmentId)= 0 AND  bpu.receiveTime BETWEEN CONCAT(YEAR(NOW()),'-01-01') AND CONCAT(YEAR(NOW()),'-12-31')
  AND p.name = ?  and bpu.sterilisation = 0  group by bpu.pastureId)  t  
    join pasture p on p.id = t.pastureId   GROUP BY p.center`, pastureName, pastureName).Find(&thisYearActual).Error
	if err != nil {
		return resp, err
	}

	nowStr := time.Now().Format("2006-01-02")
	now, _ := time.Parse("2006-01-02", nowStr)
	// 获取当年的一月一号
	year := now.Year()
	start := time.Date(year, 1, 1, 0, 0, 0, 0, time.UTC)
	fmt.Println(start)
	end := time.Date(year, 12, 31, 0, 0, 0, 0, time.UTC)
	fmt.Println(end)
	real := now.Sub(start)
	sum := end.Sub(start)
	sumDay := sum.Hours() / 24
	realDay := real.Hours() / 24
	fmt.Println(realDay, sumDay)
	resp.Budget = monthBudget.MonthBudget
	resp.Actual = thisYearActual.SumPrice
	resp.Complete = util.Round((1+(resp.Budget/sumDay*realDay-(resp.Actual))/resp.Budget)*100, 2)
	resp.Budget = util.Round(resp.Budget/1000000, 2)
	resp.Actual = util.Round(resp.Actual/1000000, 2)
	//resp.Complete = util.Round(1+(resp.Budget/sumDay*realDay-(resp.Actual/10000))/(resp.Budget/sumDay*realDay), 2)

	return resp, nil
}