hz_crm_api/models/yb_research_signup_statistics.go

package models

import (
	"github.com/beego/beego/v2/client/orm"
	"time"
)

// YbResearchSignupStatistics 调研报名统计结构体
type YbResearchSignupStatistics struct {
	YbResearchSignupStatisticsId int       // 主键ID
	UserId                       int       // 分享人ID
	RealName                     string    // 分享人姓名
	Mobile                       string    // 分享人手机号
	CompanyName                  string    // 公司名称
	BannerId                     int       // 活动ID
	CreateTime                   time.Time // 创建时间
	Amount                       float64   // 付款金额
	CustomName                   string    // 报名人姓名
	CustomMobile                 string    // 报名人手机号
	CustomCompanyName            string    // 报名人公司名称
}

type YbResearchSignupStatisticsListItem struct {
	YbResearchSignupStatisticsId int     // 主键ID
	BannerId                     int     // 活动ID
	Amount                       float64 // 付款金额
	Count                        int     // 报名人数
	Remark                       string  // 备注-活动名称
	StartDate                    string  // 活动开始时间
	EndDate                      string  // 活动结束时间
	Enable                       int     // 1:有效，0:禁用
}

func GetYbResearchSignupStatisticsItems() (items []*YbResearchSignupStatisticsListItem, err error) {
	sql := ` SELECT
	a.*,
	b.start_date,
	b.end_date,
	b.remark,
	b.enable,
	COUNT( 1 ) AS count 
FROM
	yb_research_signup_statistics AS a
	INNER JOIN banner AS b ON a.banner_id = b.id 
WHERE
	1 = 1 
GROUP BY
	a.banner_id 
ORDER BY
	start_date DESC `
	o := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

type YbResearchSignupStatisticsResp struct {
	OngoingList []*YbResearchSignupStatisticsListItem
	OverList    []*YbResearchSignupStatisticsListItem
}

type YbResearchSignupStatisticsItem struct {
	YbResearchSignupStatisticsId int     // 主键ID
	UserId                       int     // 分享人ID
	RealName                     string  // 分享人姓名
	Mobile                       string  // 分享人手机号
	CompanyName                  string  // 公司名称
	BannerId                     int     // 活动ID
	CreateTime                   string  // 创建时间
	Amount                       float64 // 付款金额
	CustomName                   string  // 报名人姓名
	CustomMobile                 string  // 报名人手机号
	CustomCompanyName            string  // 报名人公司名称
	Count                        int     // 报名人数
}

type YbResearchSignupStatisticsItemsResp struct {
	List     []*YbResearchSignupStatisticsItem
	Total    int
	HasPayed PayItem
	NoPay    PayItem
}
type PayItem struct {
	Name       string
	Count      int
	Amount     float64
	Percentage int
}

func GetYbResearchSignupStatisticsItemsById(bannerId int) (items []*YbResearchSignupStatisticsItem, err error) {
	sql := ` SELECT
	*,count(1) as count
FROM
	yb_research_signup_statistics 
WHERE
	1 = 1 and banner_id = ?
GROUP BY
	mobile 
ORDER BY
	count DESC `
	o := orm.NewOrm()
	_, err = o.Raw(sql, bannerId).QueryRows(&items)
	return
}

func GetYbResearchSignupStatisticsItemsByMobile(mobile string, bannerId int) (items []*YbResearchSignupStatisticsItem, err error) {
	sql := ` SELECT
	*
FROM
	yb_research_signup_statistics 
WHERE
	1 = 1 and mobile = ? and banner_id = ? 
ORDER BY
	create_time DESC `
	o := orm.NewOrm()
	_, err = o.Raw(sql, mobile, bannerId).QueryRows(&items)
	return
}

// UpdateYbResearchSignupStatistics 更新付款金额
func UpdateYbResearchSignupStatisticsAmountById(amount float64, id int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE yb_research_signup_statistics SET amount=? WHERE yb_research_signup_statistics_id=?`
	_, err = o.Raw(sql, amount, id).Exec()
	return
}

func GetYbResearchSignupStatisticsAmount(bannerId int) (items []*YbResearchSignupStatisticsListItem, err error) {
	sql := ` SELECT
	a.*
FROM
	yb_research_signup_statistics AS a
	INNER JOIN banner AS b ON a.banner_id = b.id 
WHERE
	1 = 1 AND a.banner_id = ? `
	o := orm.NewOrm()
	_, err = o.Raw(sql, bannerId).QueryRows(&items)
	return
}