mh_goadmin_server/app/admin/models/analyse.go

package models

import (
	"fmt"
	"github.com/codinl/go-logger"
	"github.com/xuri/excelize/v2"
	orm "go-admin/common/global"
	"time"
)

//go:generate goqueryset -in analyse.go

type Statistic struct {
	Model

	Count uint32 `json:"count"`             // 计数
	Type  uint32 `json:"type" gorm:"index"` // 类型:1-

}

const (
	UserRenewalLogActionPop           = 1 // 弹窗
	UserRenewalLogActionEnterActivity = 2 // 进入活动
	UserRenewalLogActionPickGold      = 3 // 领取黄金会员
	UserRenewalLogActionPickPlatinum  = 4 // 领取白金会员
	UserRenewalLogActionPickBlackGold = 5 // 领取黑金会员
	UserRenewalLogActionUseGold       = 6 // 使用黄金会员
	UserRenewalLogActionUsePlatinum   = 7 // 使用白金会员
	UserRenewalLogActionUseBlackGold  = 8 // 使用黑金会员
	UserRenewalLogSendMessage         = 9 // 发送短信
)

// gen:qs
type UserRenewalLog struct {
	Model

	Uid          uint32    `json:"uid"`
	Action       uint32    `json:"action"`
	MemberExpire time.Time `json:"member_expire"`
	Type         uint32    `json:"type"` // 类型:1-续费 2-升级
	RenewalNum   uint32    `json:"renewal_num"`
	Serial       uint32    `json:"serial"`
	// user_renewal_log
}

func (l *UserRenewalLog) Add() error {
	err := orm.Eloquent.Create(l).Error
	if err != nil {
		logger.Error("user log add err:", err)
		return err
	}
	return nil
}

func GetActionUserRenewalLog(uid, action uint32) (UserRenewalLog, error) {
	var userRenewalLog UserRenewalLog
	err := orm.Eloquent.Table("user_renewal_log").Where("uid=?", uid).Where("action").
		Order("id DESC").Limit(1).Find(&userRenewalLog).Error
	//err := NewUserRenewalLogQuerySet(DB).UidEq(uid).ActionEq(action).OrderDescByID().Limit(1).One(&userRenewalLog)
	if err != nil && err != RecordNotFound {
		logger.Error("user renewal log err:", err)
		return userRenewalLog, err
	}
	return userRenewalLog, nil
}

func GetActionUserRenewalLogMulti(uids []uint32, action uint32) ([]UserRenewalLog, error) {
	var userRenewalLogs []UserRenewalLog
	err := orm.Eloquent.Table("user_renewal_log").Where("uid IN (?)", uids).Where("action=?", action).
		Order("id DESC").Limit(1).Find(&userRenewalLogs).Error
	//err := NewUserRenewalLogQuerySet(DB).UidEq(uid).ActionEq(action).OrderDescByID().Limit(1).One(&userRenewalLog)
	if err != nil && err != RecordNotFound {
		logger.Error("user renewal log err:", err)
		return userRenewalLogs, err
	}
	return userRenewalLogs, nil
}

// gen:qs
type UserMemberRecord struct {
	Model
	Uid                uint32    `json:"uid" gorm:"index"`
	BeforeMemberLevel  uint32    `json:"before_member_level"`
	AfterMemberLevel   uint32    `json:"after_member_level"`
	BeforeMemberExpire time.Time `json:"before_member_expire"`
	AfterMemberExpire  time.Time `json:"after_member_expire"`
	InviteUid          uint32    `json:"invite_uid" gorm:"index"`
	DeductionDays      uint32    `json:"deduction_days"`      // 抵扣天数
	DeductionFee       uint32    `json:"deduction_fee"`       // 抵扣金额
	CouponId           uint32    `json:"coupon_id"`           // 优惠券id
	Serial             uint32    `json:"serial" gorm:"index"` //  日志
	Type               uint32    `json:"type" gorm:"index"`   // 1-开通黄金 2-开通白金 3-开通黑金 4-续费黄金 5-续费白金 6-续费黑金  7-升级白金 8-升级黑金
	OpenMemberLevel    uint32    `json:"open_member_level"`
	OpenMemberTime     time.Time `json:"open_member_time"`
	RegisterTime       time.Time `json:"register_time" gorm:"-"`
	UserInfo           *UserInfo `json:"user_info" gorm:"-"`
	MemberLevel        uint32    `json:"member_level" gorm:"-"`
	MemberExpire       time.Time `json:"member_expire" gorm:"-"` // 会员到期时间
	// user_member_record
}

type UserMemberRecordReq struct {
	Uid                uint32    `json:"uid"`
	OpenMemberLevel    uint32    `json:"open_member_level"`    // 开通等级
	RenewalMemberLevel uint32    `json:"renewal_member_level"` // 续费等级
	MemberLevel        uint32    `json:"member_level"`         // 当前等级
	RenewalStartTime   time.Time `json:"renewal_start_time"`   // 续费开始时间
	RenewalEndTime     time.Time `json:"renewal_end_time"`     // 续费结束时间
	OpenStartTime      time.Time `json:"open_start_time"`      // 开通开始时间
	OpenEndTime        time.Time `json:"open_end_time"`        // 开通结束时间
	PageIndex          int       `json:"pageIndex"`
	PageSize           int       `json:"pageSize"`
}

func (m *UserMemberRecordReq) List() ([]UserMemberRecord, int64, error) {
	m.PageIndex -= 1
	if m.PageIndex < 0 {
		m.PageIndex = 0
	}
	if m.PageSize == 0 {
		m.PageSize = 10
	}
	var records []UserMemberRecord
	qs := orm.Eloquent.Table("user_member_record")
	if m.Uid != 0 {
		qs = qs.Where("uid=?", m.Uid)
	}
	if m.OpenMemberLevel != 0 {
		if m.OpenMemberLevel == 1000 {
			qs = qs.Where("type IN (1,2,3)")
		} else {
			qs = qs.Where("type=?", m.OpenMemberLevel)
		}
	}
	if m.RenewalMemberLevel != 0 {
		if m.RenewalMemberLevel == 1001 {
			qs = qs.Where("type IN (4,5,6)")
		} else {
			qs = qs.Where("type=?", m.RenewalMemberLevel)
			//qs = qs.Where("type=?", TypeToRenewalMemberLevel(m.RenewalMemberLevel))
		}
	}

	if m.MemberLevel != 0 {
		var users []UserInfo
		err := orm.Eloquent.Table("user").Where("member_level=?", m.MemberLevel).Find(&users).Error
		if err != nil {
			logger.Error("user info err:", err)
		}
		ids := make([]uint32, 0, len(users))
		for i, _ := range users {
			ids = append(ids, users[i].Uid)
		}
		if len(ids) > 0 {
			qs = qs.Where("uid IN (?)", ids)
		}
	}

	if !m.RenewalStartTime.IsZero() {
		qs = qs.Where("created_at > ?", m.RenewalStartTime)
	}
	if !m.RenewalEndTime.IsZero() {
		qs = qs.Where("created_at < ?", m.RenewalEndTime)
	}
	if !m.OpenStartTime.IsZero() {
		qs = qs.Where("open_member_time > ?", m.OpenStartTime)
	}
	if !m.OpenEndTime.IsZero() {
		qs = qs.Where("open_member_time < ?", m.OpenEndTime)
	}
	var count int64
	err := qs.Count(&count).Error
	if err != nil {
		logger.Error("count error:", err)
		return records, count, err
	}

	err = qs.Order("id DESC").Offset(m.PageIndex * m.PageSize).Limit(m.PageSize).Find(&records).Error
	if err != nil && err != RecordNotFound {
		logger.Error("record error:", err)
		return records, count, err
	}
	UserMemberRecordListSetUser(records)

	return records, count, nil
}

func (m *UserMemberRecordReq) Export() (string, error) {

	var records []UserMemberRecord
	qs := orm.Eloquent.Table("user_member_record")
	if m.Uid != 0 {
		qs = qs.Where("uid=?", m.Uid)
	}
	if m.OpenMemberLevel != 0 {
		qs = qs.Where("open_member_level=?", m.OpenMemberLevel)
	}
	if m.RenewalMemberLevel != 0 {
		qs = qs.Where("after_member_level=?", m.RenewalMemberLevel)
	}
	if m.MemberLevel != 0 {
		var users []UserInfo
		err := orm.Eloquent.Table("user").Where("member_level=?", m.MemberLevel).Find(&users).Error
		if err != nil {
			logger.Error("user info err:", err)
		}
		ids := make([]uint32, 0, len(users))
		for i, _ := range users {
			ids = append(ids, users[i].Uid)
		}
		if len(ids) > 0 {
			qs = qs.Where("uid IN (?)", ids)
		}
	}

	if !m.RenewalStartTime.IsZero() {
		qs = qs.Where("created_at > ?", m.RenewalStartTime)
	}
	if !m.RenewalEndTime.IsZero() {
		qs = qs.Where("created_at < ?", m.RenewalEndTime)
	}
	if !m.OpenStartTime.IsZero() {
		qs = qs.Where("open_member_time > ?", m.OpenStartTime)
	}
	if !m.OpenEndTime.IsZero() {
		qs = qs.Where("open_member_time < ?", m.OpenEndTime)
	}

	err := qs.Order("id DESC").Find(&records).Error
	if err != nil && err != RecordNotFound {
		logger.Error("record error:", err)
		return "", err
	}
	UserMemberRecordListSetUser(records)

	file := excelize.NewFile()
	streamWriter, err := file.NewStreamWriter("Sheet1")
	if err != nil {
		fmt.Println(err)
	}

	//url := "http://39.108.188.218:8000/img/export/"
	//f, err := os.OpenFile(fmt.Sprintf("/www/server/images/export/")+fileName, os.O_CREATE|os.O_TRUNC|os.O_APPEND|os.O_RDWR, 0644)

	fileName := "用户会员记录" + time.Now().Format("2006-01-02 15:04:05") + ".xlsx"

	//title := []interface{}{"时间", "新增会员数", "会员过期数", "净增会员数", "累计会员数"}
	title := []interface{}{"用户ID", "注册时间", "开通类型", "开通时间", "续费类型", "续费时间", "当前类型", "会员到期时间"}
	cell, _ := excelize.CoordinatesToCellName(1, 1)
	if err = streamWriter.SetRow(cell, title); err != nil {
		fmt.Println(err)
	}
	var row []interface{}
	for rowId := 0; rowId < len(records); rowId++ {
		row = []interface{}{records[rowId].Uid, records[rowId].RegisterTime.Format("2006.01.02"),
			MemberLevelToString(records[rowId].OpenMemberLevel), records[rowId].OpenMemberTime.Format("2006.01.02"),
			records[rowId].TypeToString(), records[rowId].CreatedAt.Format("2006.01.02"), MemberLevelToString(records[rowId].MemberLevel), records[rowId].MemberExpire.Format("2006.01.02")}
		cell, _ := excelize.CoordinatesToCellName(1, rowId+2)
		if err := streamWriter.SetRow(cell, row); err != nil {
			fmt.Println(err)
		}
	}
	if err := streamWriter.Flush(); err != nil {
		fmt.Println(err)
	}
	//if err := file.SaveAs("/www/server/images/export/Book1.xlsx"); err != nil {
	if err := file.SaveAs("/www/server/images/export/" + fileName); err != nil {
		fmt.Println(err)
	}

	return ExportUrl + fileName, nil
}

func UserMemberRecordListSetUser(list []UserMemberRecord) {
	ids := make([]uint32, 0)
	for i, _ := range list {
		ids = append(ids, list[i].Uid)
	}
	infoMap, err := GetUserInfoMap(ids)
	if err != nil {
		logger.Error("get user info map err:", err)
		return
	}

	for i, _ := range list {
		v, ok := infoMap[list[i].Uid]
		if ok {
			//list[i].UserInfo = &v
			list[i].RegisterTime = v.CreatedAt
			list[i].MemberLevel = v.MemberLevel
			list[i].MemberExpire = *v.MemberExpire
		}
	}
}

func (m *UserMemberRecord) TypeToString() string {
	//// 1-开通黄金 2-开通白金 3-开通黑金 4-续费黄金 5-续费白金 6-续费黑金  7-升级白金 8-升级黑金
	switch m.Type {
	case 1:
		return "开通黄金"
	case 2:
		return "开通白金"
	case 3:
		return "开通黑金"
	case 4:
		return "续费黄金"
	case 5:
		return "续费白金"
	case 6:
		return "续费黑金"
	case 7:
		return "升级白金"
	case 8:
		return "升级黑金"
	}

	return ""
}
func TypeToRenewalMemberLevel(level uint32) uint32 {
	//// 1-开通黄金 2-开通白金 3-开通黑金 4-续费黄金 5-续费白金 6-续费黑金  7-升级白金 8-升级黑金
	switch level {
	case 1:
		return 4
	case 2:
		return 5
	case 3:
		return 6
	}

	return 0
}