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:"修改时间"`
}

// GetUnusualRenewCompanyList 获取销售未续约数据
func GetUnusualRenewCompanyList(startDate, endDate time.Time, productId int) (list []*CompanyRenewalRecord, 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 = 1 
	AND a.product_id = ?
GROUP BY
	a.seller_id;
`
	_, err = o.Raw(sql, startDate, endDate, productId).QueryRows(&list)

	return
}