jx-print/dao/sim_dao.go

package dao

import (
	"git.rosy.net.cn/baseapi/utils"
	"git.rosy.net.cn/jx-print/model"
	"github.com/jmoiron/sqlx"
	"time"
)

type FlowSum struct {
	Flow     float64 `json:"flow" db:"flow"`
	IccID    string  `json:"icc_id" db:"icc_id"`
	FlowUnit string  `json:"flow_unit" db:"flow_unit"`
}

func GetSimFlowExpend(db *sqlx.DB, iccid string, createdAtBegin, createdAtEnd time.Time) (simFlowExpnd []*model.SimFlowExpend, err error) {

	return simFlowExpnd, err
}

func GetSimFlowExpendSum(db *sqlx.DB, iccid string, createdAtBegin, createdAtEnd time.Time) (simFlowExpnd *FlowSum, err error) {
	var (
		simFlowExpnds []*FlowSum
	)
	sql := `
		SELECT SUM(
				IF(flow_unit = 'KB', flow,
					IF(flow_unit = 'MB', ROUND(flow * 1024),
						IF(flow_unit = 'GB', ROUND(flow * 1024 * 1024), 0)
					)
				)
			) flow, icc_id, 'KB' flow_unit
		  FROM sim_flow_expend
		 WHERE 1 = 1
	`
	sqlParams := []interface{}{}
	if iccid != "" {
		sql += " AND icc_id = ?"
		sqlParams = append(sqlParams, iccid)
	}
	if !utils.IsTimeZero(createdAtBegin) {
		sql += " AND created_at > ?"
		sqlParams = append(sqlParams, createdAtBegin)
	}
	if !utils.IsTimeZero(createdAtEnd) {
		sql += " AND created_at < ?"
		sqlParams = append(sqlParams, createdAtEnd)
	}
	sql += " GROUP BY 2, 3"
	if err = db.Select(&simFlowExpnds, sql, sqlParams...); err == nil && len(simFlowExpnds) > 0 {
		return simFlowExpnds[0], err
	}
	return simFlowExpnd, err
}

func GetSimFlowIncomeSum(db *sqlx.DB, iccid string, createdAtBegin, createdAtEnd time.Time) (simFlowIncome *FlowSum, err error) {
	var (
		simFlowIncomes []*FlowSum
	)
	sql := `
		SELECT SUM(
				IF(flow_unit = 'KB', flow,
					IF(flow_unit = 'MB', ROUND(flow * 1024),
						IF(flow_unit = 'GB', ROUND(flow * 1024 *	 1024), 0)
					)
				)
			) flow, icc_id, 'KB' flow_unit
		  FROM sim_flow_income
		 WHERE 1 = 1
	`
	sqlParams := []interface{}{}
	if iccid != "" {
		sql += " AND icc_id = ?"
		sqlParams = append(sqlParams, iccid)
	}
	if !utils.IsTimeZero(createdAtBegin) {
		sql += " AND created_at > ?"
		sqlParams = append(sqlParams, createdAtBegin)
	}
	if !utils.IsTimeZero(createdAtEnd) {
		sql += " AND created_at < ?"
		sqlParams = append(sqlParams, createdAtEnd)
	}
	sql += " GROUP BY 2, 3"
	if err = db.Select(&simFlowIncomes, sql, sqlParams...); err == nil && len(simFlowIncomes) > 0 {
		return simFlowIncomes[0], err
	}
	return simFlowIncome, err
}

type GetChargeInfoResult struct {
	model.PayOrder
	PrintNo   string  `json:"print_no" db:"print_no"`
	PrintName string  `json:"print_name" db:"print_name"`
	Flow      float64 `json:"flow"`
	FlowUnit  string  `json:"flow_unit" db:"flow_unit"`
}

func GetChargeInfo(db *sqlx.DB, appID int, printNo string, iccID, beginDate, endDate string, offset, pageSize int) (page *model.PagedInfo, err error) {
	var (
		results            []*GetChargeInfoResult
		count              int
		beginTime, endTime time.Time
	)
	if beginDate == "" {
		beginTime = utils.Time2Date(time.Now().AddDate(0, 0, -7))
	} else {
		beginTime = utils.Str2Time(beginDate)
	}
	if endDate == "" {
		endTime = utils.Str2Time(utils.Time2Str(utils.Time2Date(time.Now())) + " 23:59:59")
	} else {
		endTime = utils.Str2Time(endDate)
	}
	sqlCount := `
		SELECT COUNT(*) count
	`
	sql := `
		SELECT a.*, d.flow, d.flow_unit, c.print_no, c.name print_name
	`
	sqlOhter := `
		  FROM pay_order a 
          JOIN apps b ON b.user_id = a.user_id AND b.id = ? AND b.deleted_at = ?
     	  JOIN printer c ON c.icc_id = a.thing_id AND c.deleted_at = ? 
     	  JOIN sim_flow_income d ON d.order_id = a.order_id AND d.income_type = ?
		 WHERE 1 = 1 
	`
	sqlParams := []interface{}{appID, utils.DefaultTimeValue, utils.DefaultTimeValue, model.FlowIncomeTypeUser}
	if printNo != "" {
		sqlOhter += " AND c.print_no = ?"
		sqlParams = append(sqlParams, printNo)
	}
	if iccID != "" {
		sqlOhter += " AND a.thing_id = ?"
		sqlParams = append(sqlParams, iccID)
	}
	if beginTime != utils.ZeroTimeValue {
		sqlOhter += " AND a.created_at > ?"
		sqlParams = append(sqlParams, beginTime)
	}
	if endTime != utils.ZeroTimeValue {
		sqlOhter += " AND a.created_at < ?"
		sqlParams = append(sqlParams, endTime)
	}
	//查总数
	row := db.DB.QueryRow(sqlCount+sqlOhter, sqlParams...)
	if err = row.Scan(&count); err != nil {
		return page, err
	}
	sqlOhter += " LIMIT ? OFFSET ?"
	sqlParams = append(sqlParams, pageSize, offset)
	if err = db.Select(&results, sql+sqlOhter, sqlParams...); err == nil {
		return &model.PagedInfo{
			TotalCount: count,
			Data:       results,
		}, err
	}
	return page, err
}