hz_crm_api/models/company/company_renewal_record.go

package company

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

// CompanyRenewalRecord
// @Description: 客户续约状态记录表
type CompanyRenewalRecord struct {
	Id              int       `orm:"column(id);pk"`
	CompanyId       int       `description:"客户id"`
	ProductId       int       `description:"产品id"`
	Source          int       `description:"类型，枚举值，1：续约异常客户；2：续约正常客户；3：超时续约客户"`
	SellerId        int       `description:"销售id"`
	SellerName      string    `description:"销售名称"`
	ShareSellerId   int       `description:"共享销售id"`
	ShareSellerName string    `description:"共享销售名称"`
	CreateTime      time.Time `description:"创建时间"`
	ModifyTime      time.Time `description:"修改时间"`
}

type UnusualRenewCompanyGroup struct {
	SellerId      int `description:"所属销售id"`
	ShareSellerId int `description:"所属共享销售id"`
	//AdminName string `description:"所属销售名称"`
	Num        int    `description:"汇总次数"`
	CompanyIds string `description:"客户id字符串"`
}

// GetUnusualRenewCompanyList 获取销售未续约数据
func GetUnusualRenewCompanyList(startDate, endDate time.Time, productId int) (list []*CompanyRenewalRecord, err error) {
	o := orm.NewOrm()
	sql := `	SELECT
	*
	FROM
	company_renewal_record 
WHERE
	modify_time BETWEEN ? 
	AND ? 
    AND source = 1 
	AND product_id = ?;
`
	_, err = o.Raw(sql, startDate, endDate, productId).QueryRows(&list)

	return
}

// GetUnusualRenewCompanyByShareSellerList 获取销售未续约数据
func GetUnusualRenewCompanyByShareSellerList(startDate, endDate time.Time, productId, source int) (list []*UnusualRenewCompanyGroup, err error) {
	o := orm.NewOrm()
	sql := `	SELECT
	a.seller_id,a.share_seller_id,
	count(
	DISTINCT ( a.company_id )) num,
	GROUP_CONCAT( DISTINCT a.company_id SEPARATOR ',' ) AS company_ids 
	FROM
	company_renewal_record as a
WHERE
	a.modify_time BETWEEN ? 
	AND ? 
    AND a.source = ? 
	AND a.product_id = ?
GROUP BY
	a.share_seller_id;
`
	_, err = o.Raw(sql, startDate, endDate, source, productId).QueryRows(&list)

	return
}

// Add
// @Description: 添加续约用户记录
// @author: Roc
// @datetime 2023-12-07 14:16:37
// @param item *CompanyRenewalRecord
// @return err error
func (item *CompanyRenewalRecord) Add() (err error) {
	o := orm.NewOrm()
	lastId, err := o.Insert(item)
	if err != nil {
		return
	}
	item.Id = int(lastId)

	return
}

type GetUnusualRenewListGroupMonth struct {
	Date       string
	CompanyNum int
}

type GetUnusualRenewListGroupMonthResp struct {
	List []*GetUnusualRenewListGroupMonth
}

// GetUnusualRenewList 获取未续约公司列表
func GetUnusualRenewList(startDate, endDate time.Time, productId, source int, sellerIds string) (list []*CompanyRenewalRecord, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	* 
FROM
	company_renewal_record 
WHERE
	source = ? 
	AND modify_time BETWEEN ? 
	AND ? 
	AND product_id = ?
	AND (share_seller_id in (` + sellerIds + `)
	OR seller_id in (` + sellerIds + `))

`
	_, err = o.Raw(sql, source, startDate, endDate, productId).QueryRows(&list)

	return
}

func (item *CompanyRenewalRecord) InsertMulti(items []*CompanyRenewalRecord) (err error) {
	o := orm.NewOrm()
	_, err = o.InsertMulti(len(items), items)
	return
}