lc_finance/server/dao/monthExpenses.go

package dao

import (
	"gorm.io/gorm"
	"server/global"
)

type MonthExpenses struct {
	global.GVA_MODEL
	DepartmentId     int           `json:"departmentId" form:"departmentId" gorm:"comment:部门id"`
	Department       Department    `json:"department" form:"department" gorm:"foreignKey:DepartmentId;references:id;"`
	Genre            int           `json:"genre" form:"genre" gorm:"comment:费用类型Id"`
	ExpensesGenre    ExpensesGenre `json:"expensesGenre" form:"expensesGenre" gorm:"foreignKey:Genre;references:id;"`
	TotalDeposit     float64       `json:"totalDeposit" form:"totalDeposit" gorm:"comment:总支入;type:double(10,2)"`
	TotalExpenditure float64       `json:"totalExpenditure" form:"totalExpenditure" gorm:"comment:总支出;type:double(10,2)"`
	SettlementMonth  string        `json:"settlementMonth" form:"settlementMonth" gorm:"comment:结算月份"`
}

func (MonthExpenses) TableName() string {
	return "month_expenses"
}

func QueryMonthExpenses(monthTime, yearTime string, genreId int) (monthExpenses []MonthExpenses, err error) {
	db := global.GVA_DB.Debug().Model(&MonthExpenses{}).Select("department_id, SUM(total_deposit) AS total_deposit, SUM(total_expenditure) AS total_expenditure")
	if monthTime != "" {
		db = db.Where("DATE_FORMAT(CONCAT(settlement_month, '-01'), '%Y-%m') = ?", monthTime)
	}
	if yearTime != "" {
		db = db.Where("DATE_FORMAT(CONCAT(settlement_month, '-01'), '%Y') = ?", yearTime)
	}
	if genreId != 0 {
		db = db.Where("genre = ?", genreId)
	}
	err = db.Group("department_id").Preload("ExpensesGenre").Preload("Department").Find(&monthExpenses).Error
	return monthExpenses, err
}

func CreateMonthExpenses() error {
	return global.GVA_DB.Transaction(func(tx *gorm.DB) error {
		tx = tx.Debug()
		txErr := tx.Session(&gorm.Session{AllowGlobalUpdate: true}).Unscoped().Delete(&MonthExpenses{}).Error
		if txErr != nil {
			return txErr
		}
		var fees []MonthExpenses
		txErr = tx.Raw(`SELECT DATE_FORMAT(fee_time, '%Y-%m') AS settlement_month,department_id, genre, SUM(deposit_amount) AS total_deposit, SUM(expenditure_amount) AS total_expenditure FROM  expenses GROUP BY DATE_FORMAT(fee_time, '%Y-%m'),department_id,genre`).Scan(&fees).Error
		if txErr != nil {
			return txErr
		}
		txErr = tx.Model(&MonthExpenses{}).Create(&fees).Error
		if txErr != nil {
			return txErr
		}
		return txErr
	})
}