lc_finance/server/dao/expenses.go

package dao

import (
	"server/dao/system"
	"server/global"
)

type Expenses 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"`
	ExpensesGenre     ExpensesGenre  `json:"expensesGenre" form:"expensesGenre" gorm:"foreignKey:Genre;references:id;"`
	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)"`
}

type ExpensesResult struct {
	Reimburser           int            `json:"reimburser" form:"reimburser" gorm:"comment:报销人"`
	SysUser              system.SysUser `json:"user" form:"user" gorm:"foreignKey:Reimburser;references:id;"`
	ExpenditureAmountSum float64
	DepositAmountSum     float64
	DepartmentId         int        `json:"departmentId" form:"departmentId" gorm:"comment:部门id"`
	Department           Department `json:"department" form:"department" gorm:"foreignKey:DepartmentId;references:id;"`
	Expenses             []Expenses `json:"expenses" form:"expenses" gorm:"-"`
}

func (Expenses) TableName() string {
	return "expenses"
}

func QueryExpensesList(limit, offset, genre, name int, dayTime, monthTime, yearTime string) (expenses []Expenses, total int64, err error) {
	// 创建db
	db := global.GVA_DB.Model(&Expenses{})
	// 如果有条件搜索 下方会自动创建搜索语句
	if genre != 0 {
		db = db.Where("genre = ?", genre)
	}
	if name != 0 {
		db = db.Where("reimburser = ?", name)
	}
	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("fee_time desc,created_at desc").Limit(limit).Offset(offset).Preload("SysUser").Preload("Department").Preload("ExpensesGenre").Find(&expenses).Error
	return expenses, total, err
}

// QueryExpensesSumByPeople 查询
func QueryExpensesSumByPeople(limit, offset, genre, name int, dayTime, monthTime, yearTime string) (results []ExpensesResult, total int64, err error) {
	db := global.GVA_DB.Model(&Expenses{}).Select("reimburser,SUM(expenditure_amount) as expenditure_amount_sum,SUM(deposit_amount) as deposit_amount_sum,department_id")
	// 如果有条件搜索 下方会自动创建搜索语句
	if genre != 0 {
		db = db.Where("genre = ?", genre)
	}
	if name != 0 {
		db = db.Where("reimburser = ?", name)
	}
	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.Group("reimburser,department_id").Count(&total).Error
	if err != nil {
		return
	}
	err = db.Limit(limit).Offset(offset).Preload("SysUser").Preload("Department").Scan(&results).Error

	return
}

func QuerySurplus() (interface{}, error) {
	type Result struct {
		FeeTime              string
		ExpenditureAmountSum float64
		DepositAmountSum     float64
		Surplus              float64
	}

	var results []Result
	db := global.GVA_DB.Model(&Expenses{}).Select("DATE_FORMAT(fee_time, '%Y-%m') AS fee_time, SUM(expenditure_amount) AS expenditure_amount_sum,SUM(deposit_amount) AS deposit_amount_sum,(SUM(deposit_amount) - SUM(expenditure_amount)) AS surplus")

	err := db.Group("DATE_FORMAT(fee_time, '%Y-%m')").Scan(&results).Error
	return results, err
}

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

func QueryExpensesByProjectId(projectId int) (expenses []Expenses, err error) {
	err = global.GVA_DB.Model(&Expenses{}).Where("project_id = ?", projectId).Find(&expenses).Error
	return expenses, err
}

func QueryExpensesById(id int) (expenses Expenses, err error) {
	err = global.GVA_DB.Where("id =?", id).Preload("SysUser").Preload("Department").Preload("ExpensesGenre").First(&expenses).Error
	return expenses, err
}

func (e Expenses) CreateExpenses() error {
	return global.GVA_DB.Create(&e).Error
}

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

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