services/models/card/card.go

package card

import (
	"errors"
	"fmt"
	"spaceofcheng/services/models"
	"spaceofcheng/services/models/model"
	"spaceofcheng/services/utils"
	"strconv"
	"strings"
	"time"

	"github.com/go-xorm/xorm"
	"github.com/yl10/kit/guid"
)

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

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

// CardInfo 卡
type CardInfo struct {
	model.TaCouponCard `xorm:"extends"`
	Images             []model.TaExperienceCardImage
	Targets            []model.TaCouponCardTarget
	Share              *model.TaExperienceCardShare
	ChannelId          string
}

// GetCardList 获取卡列表
func (m *CardDAO) GetCardList(filters []string, limit []int) ([]model.TaCouponCard, int64, error) {
	var cds []model.TaCouponCard

	filterString := ""
	if len(filters) > 0 {
		filterString = strings.Join(filters, " and ")
		filterString += " and "
	}

	sql := `select * from ta_coupon_card where %s status > ` + strconv.Itoa(models.STATUS_DEL) + ` order by create_date desc`

	total, err := utils.NewPageNaviEngine(m.ctx).GetPageList(&cds, fmt.Sprintf(sql, filterString), limit)
	if err != nil {
		return nil, 0, err
	}

	return cds, total, nil
}

// GetCardByID 获取卡明细
func (m *CardDAO) GetCardByID(cardid string) (*CardInfo, error) {
	var card []CardInfo
	sql := `select * from ta_coupon_card where card_id=? and status>` + strconv.Itoa(models.STATUS_DEL)

	err := m.db.Sql(sql, cardid).Find(&card)
	if err != nil {
		return nil, err
	}
	if len(card) == 0 {
		return nil, nil
	}
	imgs, err := m.GetCardImgsByCardID(cardid)
	if err != nil {
		return nil, err
	}
	card[0].Images = imgs
	targets, err := m.GetCardTargetByCardID(cardid)
	if err != nil {
		return nil, err
	}
	card[0].Targets = targets
	share, err := m.GetCardShareByCardID(cardid)
	if err != nil {
		return nil, err
	}
	card[0].Share = share
	channel, err := m.GetCardChannel(cardid)
	if err != nil {
		return nil, err
	}
	if channel != nil {
		card[0].ChannelId = channel.ChannelId
	}
	return &card[0], err
}

// AddCard 新增卡信息
func (m *CardDAO) AddCard(card *model.TaCouponCard) error {
	if card.CardId == "" {
		card.CardId = guid.NewGUIDString()
	}

	userRaw := m.ctx.Get("user")
	if userRaw != nil {
		user := userRaw.(model.SysUser)
		card.CreateUser = user.UserId
	}

	card.Status = models.STATUS_NORMAL
	card.CreateDate = time.Now().Local()

	if _, err := m.db.Insert(card); err != nil {
		return err
	}

	return nil
}

// GetCardInfoByID 获取卡信息
func (m *CardDAO) GetCardInfoByID(cardid string) (*model.TaCouponCard, error) {
	var card = model.TaCouponCard{}
	_, err := m.db.Where("card_id=?", cardid).Get(&card)
	return &card, err
}

// UpdateCard 更新体验卡
func (m *CardDAO) UpdateCard(card *model.TaCouponCard, cols []string) error {
	if card.CardId == "" {
		return errors.New("无卡信息")
	}

	if _, err := m.db.Cols(cols...).Where("card_id=?", card.CardId).Update(card); err != nil {
		return err
	}

	return nil
}

// DeleteCard 删除卡
func (m *CardDAO) DeleteCard(id string) error {
	if id == "" {
		return errors.New("无卡信息")
	}

	card := model.TaCouponCard{
		CardId: id,
		Status: models.STATUS_DEL,
	}

	if err := m.UpdateCard(&card, []string{"status"}); err != nil {
		return err
	}

	return nil
}

// SaveCustomerCard 保存用户卡
func (m *CardDAO) SaveCustomerCard(card *model.TaCustomerCard) error {
	if card.CustomerCardId == "" {
		card.CustomerCardId = guid.NewGUIDString()
	}

	card.Status = models.STATUS_NORMAL
	card.ReceiveDate = time.Now().Local()
	card.VerifyStatus = models.VERIFY_USEABLE

	if _, err := m.db.Insert(card); err != nil {
		return err
	}

	return nil
}

// SaveCustomerCards 保存用户卡
func (m *CardDAO) SaveCustomerCards(cards []model.TaCustomerCard) error {
	for i := range cards {
		if cards[i].CustomerCardId == "" {
			cards[i].CustomerCardId = guid.NewGUIDString()
		}

		cards[i].Status = models.STATUS_USED
		cards[i].ReceiveDate = time.Now()
		cards[i].VerifyStatus = models.VERIFY_USEABLE
	}

	if _, err := m.db.Insert(cards); err != nil {
		return err
	}

	return nil
}

// CheckCustCard 检查客户是否已经有了该卡券
// 如果用户有了卡券, 但是用过了，或者卡券过期了，仍然可以再次拥有
func (m *CardDAO) CheckCustCard(custID, CardID string, startDate, endDate time.Time) ([]model.TaCustomerCard, error) {
	var cps []model.TaCustomerCard
	query := `
		SELECT
			*
		FROM
			ta_customer_card t
		WHERE
			t.card_id = ?
		AND t.customer_id = ?
		AND t.status = ?
		AND (
			(t.start_date BETWEEN ? AND ?)
			OR (t.end_date BETWEEN ? AND ?)
			OR (
				t.start_date <= ?
				AND t.end_date >= ?
			)
		)
	`

	if err := m.db.SQL(
		query,
		CardID,
		models.STATUS_NORMAL,
		custID,
		startDate,
		endDate,
		startDate,
		endDate,
		startDate,
		endDate,
	).Find(&cps); err != nil {
		return nil, err
	}

	return cps, nil
}

// 案场可用卡信息
type CaseUsableCard struct {
	model.TaCouponCard `xorm:"extends"`
	CustomerCard       []model.TaCustomerCard
	Share              *model.TaExperienceCardShare
}

// GetCaseUsableCard 获取案场可用卡信息
func (m *CardDAO) GetCaseUsableCard(caseid, userid string, page, pageSize int) ([]CaseUsableCard, error) {
	var cards []CaseUsableCard
	sql := `select * from ta_coupon_card where case_id=? and status=? and DATE_FORMAT(end_date, '%Y-%m-%d')>=DATE_FORMAT(NOW(), '%Y-%m-%d') and send_type=? order by create_date desc limit ` + strconv.Itoa((page-1)*pageSize) + `, ` + strconv.Itoa(pageSize)
	err := m.db.Sql(sql, caseid, models.STATUS_NORMAL, models.GIVE_TYPE_CASE).Find(&cards)
	for inx, card := range cards {
		if userid != "" {
			cCards, err := m.GetCustomerCardByCardAndUser(card.CardId, userid)
			if err != nil {
				return nil, err
			}
			cards[inx].CustomerCard = cCards
		} else {
			// 主管
			num, err := m.GetCustomerCardVerifyNum(card.CardId)
			if err != nil {
				return nil, err
			}
			cards[inx].UsedCount = num
		}
		share, err := m.GetCardShareByCardID(card.CardId)
		if err != nil {
			return nil, err
		}
		cards[inx].Share = share
	}
	return cards, err
}

// GetCustomerCardVerifyNum 获取卡核销数量
func (m *CardDAO) GetCustomerCardVerifyNum(cardid string) (int, error) {
	var customerCards []model.TaCustomerCard
	err := m.db.Where("card_id=?", cardid).And("verify_status<>?", models.VERIFY_USEABLE).Find(&customerCards)
	return len(customerCards), err
}

// GetCaseUsableCardCount 获取案场可用卡数量
func (m *CardDAO) GetCaseUsableCardCount(caseid, userid string) (int, error) {
	var Coupons []CaseUsableCard
	sql := `select * from ta_coupon_card where case_id=? and status=? and DATE_FORMAT(end_date, '%Y-%m-%d')>=DATE_FORMAT(NOW(), '%Y-%m-%d') and send_type=?`
	err := m.db.Sql(sql, caseid, models.STATUS_NORMAL, models.GIVE_TYPE_CASE).Find(&Coupons)
	return len(Coupons), err
}

// GetCustomerCardByCardAndUser 根据卡与用户获取领取的用户信息
func (m *CardDAO) GetCustomerCardByCardAndUser(cardid, userid string) ([]model.TaCustomerCard, error) {
	var customerCards []model.TaCustomerCard
	err := m.db.Where("card_id=?", cardid).And("sales_id=?", userid).And("status>?", models.STATUS_DEL).Find(&customerCards)
	return customerCards, err
}

// 案场可用卡信息
type CardDetailWithSales struct {
	model.TaCouponCard `xorm:"extends"`
	CustomerCard       []SalesWithCustomerCardNum
}

// SalesWithCustomerCardNum
type SalesWithCustomerCardNum struct {
	RealName   string
	SalesId    string
	ReceiveNum int
	UsedNum    int
}

// GetCardDetail 获取卡详情
func (m *CardDAO) GetCardDetail(cardid string) (*CardDetailWithSales, error) {
	var card = new(CardDetailWithSales)
	sql := `select * from ta_coupon_card where card_id=? and status>?`
	_, err := m.db.Sql(sql, cardid, models.STATUS_DEL).Get(card)
	if err != nil {
		return nil, err
	}
	if card == nil || card.CardId == "" {
		return nil, errors.New("无卡信息")
	}
	num, err := m.GetCustomerCardVerifyNum(card.CardId)
	if err != nil {
		return nil, err
	}
	card.UsedCount = num
	ccards, err := m.GetCustomerCardByCardWithRealName(cardid)
	if err != nil {
		return nil, err
	}
	card.CustomerCard = ccards
	return card, nil
}

// GetSalesCardByCard 根据卡获取领取的用户信息
func (m *CardDAO) GetSalesCardByCard(cardid string) ([]model.TaCustomerCard, error) {
	var customerCards []model.TaCustomerCard
	err := m.db.Where("card_id=?", cardid).And("status>?", models.STATUS_DEL).Find(&customerCards)
	return customerCards, err
}

// GetCustomerCardByCardWithRealName 根据卡获取领取的用户信息
func (m *CardDAO) GetCustomerCardByCardWithRealName(cardid string) ([]SalesWithCustomerCardNum, error) {
	var customerCards []SalesWithCustomerCardNum
	sql := `SELECT
			b.real_name,
			a.sales_id,
			count(1) AS receive_num,
			sum(
				CASE
				WHEN a.verify_status = 'useable' THEN
					0
				ELSE
					1
				END
			) AS used_num
		FROM
			ta_customer_card a
		INNER JOIN sys_user b ON a.sales_id = b.user_id
		WHERE
			a.card_id = ?
		AND a. STATUS > ?
		GROUP BY
			b.real_name,
			a.sales_id`
	err := m.db.Sql(sql, cardid, models.STATUS_DEL).Find(&customerCards)
	return customerCards, err
}

// GetCustomerCardByCard 根据卡获取领取的用户信息
func (m *CardDAO) GetCustomerCardByCard(cardid string) ([]model.TaCustomerCard, error) {
	var customerCards []model.TaCustomerCard
	err := m.db.Where("card_id=?", cardid).And("status>?", models.STATUS_DEL).Find(&customerCards)
	return customerCards, err
}

// GetCustomerCardByUser 根据用户获取发送的卡信息
func (m *CardDAO) GetCustomerCardByUser(caseid, userid string) ([]model.TaCustomerCard, error) {
	var customerCards []model.TaCustomerCard
	err := m.db.Where("case_id=?", caseid).And("sales_id=?", userid).And("status>?", models.STATUS_DEL).Find(&customerCards)
	return customerCards, err
}

// GetCardWithCustomer 获取体验卡明细
func (m *CardDAO) GetCardWithCustomer(cardid string) (*CaseUsableCard, error) {
	var card = new(CaseUsableCard)
	sql := `select * from ta_card where coupin_id=?`
	_, err := m.db.Sql(sql, cardid).Get(&card)
	if err != nil {
		return nil, err
	}
	if card == nil || card.CardId == "" {
		return nil, errors.New("无体验卡信息")
	}
	var customerCopons []model.TaCustomerCard
	sql = `select * from ta_customer_card where card_id = ? and status>?`
	err = m.db.Sql(sql, card, models.STATUS_DEL).Find(&customerCopons)
	if err != nil {
		return nil, err
	}
	card.CustomerCard = customerCopons
	return card, nil
}

// CustomerCardWithShare 我的卡券关联分享信息
type CustomerCardWithShare struct {
	model.TaCustomerCard `xorm:"extends"`
	CustomerCourseId     string
	CourseId             string
	CourseNum            int
	JoinNum              int
	CourseName           string
	Share                *model.TaExperienceCardShare
}

// GetCardByCustomer 获取我的体验卡
func (m *CardDAO) GetCardByCustomer(orgid, customerid string) ([]CustomerCardWithShare, error) {
	var cards []CustomerCardWithShare
	sql := `select a.*,b.customer_course_id,b.course_id,b.course_num,b.join_num,b.course_name from ta_customer_card a left join ta_customer_course b on a.customer_card_id = b.source_id where a.status>? and a.customer_id=? and a.org_id=?`
	err := m.db.Sql(sql, models.STATUS_DEL, customerid, orgid).Find(&cards)
	if err != nil {
		return nil, err
	}
	for inx, card := range cards {
		share, err := m.GetCardShareByCardID(card.CardId)
		if err != nil {
			utils.LogError("获取体验卡失败: " + err.Error())
			return nil, errors.New("获取体验卡失败")
		}
		cards[inx].Share = share
	}
	return cards, err
}

// GetCustomerCardByID 获取我的体验卡详情
func (m *CardDAO) GetCustomerCardByID(id string) (*CustomerCardWithShare, error) {
	if id == "" {
		return nil, errors.New("无体验卡信息")
	}
	var customerCard = new(CustomerCardWithShare)
	sql := `select * from ta_customer_card where status>? and customer_card_id=?`
	_, err := m.db.Sql(sql, models.STATUS_DEL, id).Get(customerCard)

	share, err := m.GetCardShareByCardID(customerCard.CardId)
	if err != nil {
		utils.LogError("获取体验卡失败: " + err.Error())
		return nil, errors.New("获取体验卡失败")
	}
	customerCard.Share = share
	return customerCard, err
}

// CheckBySerialCode 根据批次码判断
func (m *CardDAO) CheckBySerialCode(cardid, code string) (bool, error) {
	var customerCards []model.TaCustomerCard
	err := m.db.Where("card_id=?", cardid).And("serial_code=?", code).Find(&customerCards)
	if err != nil {
		return false, err
	}
	if len(customerCards) == 0 {
		return true, nil
	}
	return false, nil
}

// CheckByCustomer 判断用户是否已领取
func (m *CardDAO) CheckByCustomer(cardid, customerid string) (bool, error) {
	var customerCards []model.TaCustomerCard
	err := m.db.Where("card_id=?", cardid).And("customer_id=?", customerid).Find(&customerCards)
	if err != nil {
		return false, err
	}
	if len(customerCards) == 0 {
		return true, nil
	}
	return false, nil
}

// GetCustomerCardByCustomerAndSaleCount 获取用户的领取记录
func (m *CardDAO) GetCustomerCardByCustomerAndSaleCount(customerid, saleid string) (int, error) {
	var customerCard []model.TaCustomerCard
	err := m.db.Where("customer_id=?", customerid).And("sales_id=?", saleid).Find(&customerCard)
	return len(customerCard), err
}

// GetCustomerCardByCustomerAndSale 获取用户的领取记录
func (m *CardDAO) GetCustomerCardByCustomerAndSale(customerid, saleid string, page, pageSize int) ([]model.TaCustomerCard, error) {
	var customerCard []model.TaCustomerCard
	err := m.db.Where("customer_id=?", customerid).And("sales_id=?", saleid).OrderBy("status asc,receive_date desc").Limit(pageSize, (page-1)*pageSize).Find(&customerCard)
	return customerCard, err
}

// GetCustomerNoCardByCustomer 获取我的卡
func (m *CardDAO) GetCustomerNoCardByCustomer(customerid, orgid string) ([]model.TaCustomerCard, error) {
	var cards []model.TaCustomerCard
	sql := `select * from ta_customer_card where status>? and verify_date is null and customer_id=? and org_id=?`
	err := m.db.Sql(sql, models.STATUS_DEL, customerid, orgid).Find(&cards)
	if err != nil {
		return nil, err
	}
	return cards, nil
}

// GetCardChannel 获取卡对应渠道
func (m *CardDAO) GetCardChannel(cardid string) (*model.TaCardCouponChannel, error) {
	var channels []model.TaCardCouponChannel
	err := m.db.Where("card_coupon_type=?", models.COURSE_COUPON_CARD).And("card_coupon_id=?", cardid).And("status=?", models.STATUS_NORMAL).Find(&channels)
	if err != nil {
		return nil, err
	}
	if len(channels) > 0 {
		return &channels[0], nil
	}
	return nil, nil
}

// DelCardChannel 删除卡对应渠道
func (m *CardDAO) DelCardChannel(cardid string) error {
	sql := `delete from ta_card_coupon_channel where card_coupon_id=? and card_coupon_type=?`
	_, err := m.db.Exec(sql, cardid, models.COURSE_COUPON_CARD)
	return err
}

// SaveCardChannel 保存卡对应渠道
func (m *CardDAO) SaveCardChannel(channelid, cardid string) error {
	var cardChannel = model.TaCardCouponChannel{
		CardCouponChannelId: utils.GetGUID(),
		CardCouponId:        cardid,
		ChannelId:           channelid,
		CardCouponType:      models.COURSE_COUPON_CARD,
		Status:              models.STATUS_NORMAL,
	}
	_, err := m.db.Insert(&cardChannel)
	return err
}

func (m *CardDAO) GetSysCardList(caseid string) ([]model.TaCouponCard, error) {
	var card []model.TaCouponCard
	sql := `SELECT
	* 
FROM
	ta_coupon_card 
WHERE
	send_type = '` + models.GIVE_TYPE_SYSTEM + `'
	and case_id = '` + caseid + `'
	and  status > ` + strconv.Itoa(models.STATUS_DEL)
	err := m.db.Sql(sql).Find(&card)
	return card, err

}