expend.go

package repository

import (
	"database/sql"
	"gin-vue-admin/models"
)

// QueryExpendCount 查询支出管理total
func QueryExpendCount(db *sql.DB, month string) (int64, error) {

	var total int64
	err := db.QueryRow(`select count(id) from c_outcharge where 
	delflag = 0 and DATE_FORMAT( happentime, '%Y-%m' ) = ?`, month).Scan(&total)
	return total, err
}

// QueryExpends 查询支出管理 数据列表
func QueryExpends(db *sql.DB, month string, page, pageSize int64) (interface{}, error) {

	rows, err := db.Query(`select id, DATE_FORMAT(happentime,'%Y/%m/%d'), _usage, ifnull(convert(money/1000,decimal(11,3)), 0) as money from c_outcharge where 
	delflag = 0 and DATE_FORMAT( happentime, '%Y-%m' ) = ?  order by id desc  limit ?,?`,
		month, page, pageSize)
	if err != nil {
		return nil, err
	}

	response := make([]models.Expend, 0)

	for rows.Next() {
		var t models.Expend
		err = rows.Scan(&t.ID, &t.HappenTime, &t.Usage, &t.Money)
		if err != nil {
			return nil, err
		}
		response = append(response, t)
	}

	return response, nil
}

func QueryExpendsSumMoney(db *sql.DB, month string) (interface{}, error) {

	var money float64
	err := db.QueryRow(`select ifnull(convert(sum(money)/1000,decimal(11,3)), 0) as money from c_outcharge where 
	delflag = 0 and DATE_FORMAT( happentime, '%Y-%m' ) = ?`, month).Scan(&money)
	if err != nil {
		return nil, err
	}

	return money, nil
}