lc_finance/server/dao/projectMonthFee.go

package dao

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

type ProjectMonthFee struct {
	global.GVA_MODEL
	Genre            int             `json:"genre" form:"genre" gorm:"comment:费用类型Id"`
	ProjectFeeGenre  ProjectFeeGenre `json:"projectFeeGenre" form:"projectFeeGenre" 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 (ProjectMonthFee) TableName() string {
	return "project_month_fee"
}

func QueryProjectMonthFee(monthTime, yearTime string) (monthFees []ProjectMonthFee, err error) {
	db := global.GVA_DB.Model(&ProjectMonthFee{}).Debug().Select("genre, 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)
	}
	err = db.Group("genre").Preload("ProjectFeeGenre").Find(&monthFees).Error
	return monthFees, err
}

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