lc_finance/server/dao/projectFee.go

package dao

import (
	"github.com/shopspring/decimal"
	"gorm.io/gorm"
	"server/dao/system"
	"server/global"
)

type ProjectFee struct {
	global.GVA_MODEL
	Reimburser        int             `json:"reimburser" form:"reimburser" gorm:"comment:报销人"`
	SysUser           system.SysUser  `json:"user" form:"user" gorm:"foreignKey:Reimburser;references:id;"`
	DepartmentId      int             `json:"departmentId" form:"departmentId" gorm:"comment:部门id"`
	Department        Department      `json:"department" form:"department" gorm:"foreignKey:DepartmentId;references:id;"`
	ExpenditureAmount float64         `json:"expenditureAmount" form:"expenditureAmount" gorm:"comment:支出金额;type:double(10,2)"`
	DepositAmount     float64         `json:"depositAmount" form:"depositAmount" gorm:"comment:支入金额;type:double(10,2)"`
	FeeTime           string          `json:"feeTime" form:"feeTime" gorm:"comment:时间"`
	Genre             int             `json:"genre" form:"genre" gorm:"comment:费用类型id"`
	ProjectFeeGenre   ProjectFeeGenre `json:"projectFeeGenre" form:"projectFeeGenre" gorm:"foreignKey:Genre;references:id;"`
	ProjectCode       string          `json:"projectCode" form:"projectCode" gorm:"comment:项目编号"`
	Project           Project         `json:"project" form:"project" gorm:"foreignKey:ProjectCode;references:code;"`
	ExpenseDetail     string          `json:"expenseDetail" form:"expenseDetail" gorm:"comment:费用明细;type:varchar(400)"`
	Remarks           string          `json:"remarks" form:"remarks" gorm:"comment:备注"`
	ThenBalance       float64         `json:"thenBalance" form:"thenBalance" gorm:"comment:当时余额;type:double(10,2)"`
}

func (ProjectFee) TableName() string {
	return "project_fee"
}

func QueryProjectFeeList(limit, offset, genre, name int, code, dayTime, monthTime, yearTime string) (projectFee []ProjectFee, total int64, err error) {
	// 创建db
	db := global.GVA_DB.Model(&ProjectFee{})
	// 如果有条件搜索 下方会自动创建搜索语句
	if genre != 0 {
		db = db.Where("genre = ?", genre)
	}
	if name != 0 {
		db = db.Where("reimburser = ?", name)
	}
	if code != "" {
		db = db.Where("project_code = ?", code)
	}
	if dayTime != "" {
		db = db.Where("DATE_FORMAT(fee_time, '%Y-%m-%d') = ?", dayTime)
	}
	if monthTime != "" {
		db = db.Where("DATE_FORMAT(fee_time, '%Y-%m') = ?", monthTime)
	}
	if yearTime != "" {
		db = db.Where("DATE_FORMAT(fee_time, '%Y') = ?", yearTime)
	}
	err = db.Count(&total).Error
	if err != nil {
		return
	}
	err = db.Order("id desc").Limit(limit).Offset(offset).Preload("SysUser").Preload("Department").Preload("Project").Preload("ProjectFeeGenre").Find(&projectFee).Error
	return projectFee, total, err
}

func QueryProjectFeeByGenre(genre int) (projectFee []ProjectFee, err error) {
	err = global.GVA_DB.Model(&ProjectFee{}).Where("genre = ?", genre).Find(&projectFee).Error
	return projectFee, err
}

type Reserve struct {
	ReserveAmount     float64 `json:"reserveAmount"`
	ExpenditureAmount float64 `json:"expenditureAmount"`
	Balance           float64 `json:"balance"`
}

func QueryReserveBalance() (reserve Reserve, err error) {
	err = global.GVA_DB.Transaction(func(tx *gorm.DB) error {
		txErr := tx.Model(&ProjectFee{}).Select("SUM(expenditure_amount) as expenditure_amount").Where("genre = ?", 7).First(&reserve).Error
		if txErr != nil {
			return txErr
		}
		txErr = tx.Model(&Expenses{}).Select("SUM(expenditure_amount) as reserve_amount").Where("genre = ?", 10).First(&reserve).Error
		if txErr != nil {
			return txErr
		}
		return txErr
	})
	// 使用decimal 防止精度不准
	exponent := decimal.NewFromFloatWithExponent(reserve.ReserveAmount-reserve.ExpenditureAmount, -2)
	reserve.Balance = exponent.InexactFloat64()
	return reserve, err
}

func (e ProjectFee) CreateProjectFee() error {
	return global.GVA_DB.Create(&e).Error
}

func (e ProjectFee) UpdateProjectFee() error {
	return global.GVA_DB.Model(&ProjectFee{}).Select("*").Omit("created_at").Where("id = ?", e.ID).Updates(&e).Error
}

func (e ProjectFee) DeleteProjectFee() error {
	return global.GVA_DB.Unscoped().Where("id = ?", e.ID).Delete(&e).Error
}