services/models/card/record.go

package card

import (
	"spaceofcheng/services/models/model"
	"spaceofcheng/services/utils"
	"strconv"

	"github.com/go-xorm/xorm"
)

// RecordDAO 当前数据库操作对象
type RecordDAO struct {
	ctx *utils.Context
	db  *xorm.Session
}

// NewRecordDAO New Inst
func NewRecordDAO(ctx *utils.Context) *RecordDAO {
	return &RecordDAO{
		ctx: ctx,
		db:  ctx.DB,
	}
}

// RecordInfo 卡
type RecordInfo struct {
	model.TaCouponGiveRecord `xorm:"extends"`
	CustomerName             string
	Phone                    string
	RecommendName            string
}

// getRecordList 根据条件查询赠送记录
func (c *RecordDAO) GetRecordList(startDate string, endDate string, person string, page int, pageSize int) ([]RecordInfo, error) {
	var info []RecordInfo

	sql := "select tc.customer_name,tc.phone,tc.recommend_name,tgr.* from ta_coupon_give_record tgr LEFT JOIN ta_customer tc on tgr.to_id = tc.customer_id"

	if startDate != "" || endDate != "" || person != "" {
		sql = sql + ` WHERE`
	}

	// 传入了开始时间的时候
	if startDate != "" {
		sql = sql + ` DATE_FORMAT(tgr.create_date,'%Y-%m-%d') >= DATE_FORMAT('` + startDate + `','%Y-%m-%d')`
	}
	// 传入了结束时间的时候
	if endDate != "" {
		// 传入了开始时间的时候,添加 and
		if startDate != "" {
			sql = sql + ` and`
		}
		sql = sql + ` DATE_FORMAT(tgr.create_date,'%Y-%m-%d') <= DATE_FORMAT('` + endDate + `','%Y-%m-%d')`
	}

	if person != "" {
		// 传入了 开始时间 或者 结束时间 的时候,添加 and
		if startDate != "" || endDate != "" {
			sql = sql + ` and`
		}
		sql = sql + ` tc.recommend_name like '%` + person + `%'`
	}

	sql = sql + ` ORDER BY tgr.create_date desc limit ` + strconv.Itoa((page-1)*pageSize) + `, ` + strconv.Itoa(pageSize)

	err := c.db.SQL(sql).Find(&info)
	if err != nil {
		return nil, err
	}

	return info, err

}

// GetRecordCount 获取count
func (c *RecordDAO) GetRecordCount(startDate string, endDate string, person string) (int, error) {
	var info []RecordInfo

	sql := "select tc.customer_name,tc.phone,tc.recommend_name,tgr.* from ta_coupon_give_record tgr LEFT JOIN ta_customer tc on tgr.to_id = tc.customer_id"

	if startDate != "" || endDate != "" || person != "" {
		sql = sql + ` WHERE`
	}

	// 传入了开始时间的时候
	if startDate != "" {
		sql = sql + ` DATE_FORMAT(tgr.create_date,'%Y-%m-%d') >= DATE_FORMAT('` + startDate + `','%Y-%m-%d')`
	}
	// 传入了结束时间的时候
	if endDate != "" {
		// 传入了开始时间的时候,添加 and
		if startDate != "" {
			sql = sql + ` and`
		}
		sql = sql + ` DATE_FORMAT(tgr.create_date,'%Y-%m-%d') <= DATE_FORMAT('` + endDate + `','%Y-%m-%d')`
	}

	if person != "" {
		// 传入了 开始时间 或者 结束时间 的时候,添加 and
		if startDate != "" || endDate != "" {
			sql = sql + ` and`
		}
		sql = sql + ` tc.recommend_name like '%` + person + `%'`
	}

	err := c.db.SQL(sql).Find(&info)
	if err != nil {
		return 0, err
	}

	return len(info), err

}