hz_crm_api/models/statistic_report/report.go

package statistic_report

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

type CompanyReportRecord struct {
	CompanyReportRecordId int `orm:"column(company_report_record_id);pk"`
	CompanyId             int `description:"客户id"`
	CompanyProductId      int
	CompanyName           string    `description:"客户名称"`
	Status                string    `description:"客户状态,枚举值：试用,永续,冻结,流失,正式,潜在"`
	StartDate             string    `description:"开始日期"`
	EndDate               string    `description:"结束日期"`
	CompanyBelong         string    `description:"客户所属，ficc：ficc客户，public_offering：公募客户，partner：合作伙伴"`
	SellerId              int       `description:"所属销售id"`
	SellerName            string    `description:"所属销售名称"`
	CreditCode            string    `description:"社会统一信用码"`
	CreateDate            time.Time `description:"创建日期"`
	CreateTime            time.Time `description:"创建时间"`
	ModifyTime            time.Time `description:"修改时间"`
}

type CompanyReportRecordGroup struct {
	AdminId    int    `description:"所属销售id"`
	AdminName  string `description:"所属销售名称"`
	Num        int    `description:"汇总次数"`
	CompanyIds string `description:"客户id字符串"`
	TryStage   int    `description:"试用客户标签：1未分类、2  推进、3 跟踪、4 预备"`
}

// GetGroupCompanyReportRecordGroupList 获取销售分组数据
func GetGroupCompanyReportRecordGroupList(condition string, pars []interface{}) (list []*CompanyReportRecordGroup, err error) {
	o := orm.NewOrm()
	sql := ` SELECT seller_id as admin_id,seller_name admin_name,count(1) num, GROUP_CONCAT(DISTINCT company_id SEPARATOR ',') AS company_ids
				FROM  company_report_record 
				WHERE 1=1  
 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY seller_id`
	_, err = o.Raw(sql, pars).QueryRows(&list)

	return
}

// GetTryGroupCompanyReportRecordGroupList 获取销售试用分组数据
func GetTryGroupCompanyReportRecordGroupList(date time.Time, productId int) (list []*CompanyReportRecordGroup, err error) {
	o := orm.NewOrm()
	sql := ` SELECT c.seller_id as admin_id,c.seller_name as admin_name,count(1) num, b.try_stage, GROUP_CONCAT(DISTINCT c.company_id SEPARATOR ',') AS company_ids
				FROM  company_report_record c
				left JOIN (select * from company_product where product_id=? AND status = "试用") b on b.company_id= c.company_id 
				WHERE 1=1  
  and c.create_date = ?  and c.status = "试用" and c.product_id = ? GROUP BY c.seller_id, b.try_stage`
	_, err = o.Raw(sql, productId, date, productId).QueryRows(&list)

	return
}

type IncrementalCompanyListReq struct {
	PageSize     int `description:"每页数据条数"`
	CurrentIndex int `description:"当前页页码，从1开始"`
	SortParam    string `description:"排序字段参数，用来排序的字段， 枚举值：'viewTotal':总阅读次数 、 
'viewTime':阅读时间 、 'roadShowTotal':累计路演次数 、'expireDay'：到期时间 、 'createTime'：创建时间 、
'formalTime': 转正时间 、 'freezeTime'：冻结时间 、'lossTime'：流失时间 、
'deadline'：距离未完成的任务的截止日期的天数、sellerName:所属销售、shareSellerName:分配销售、status：客户状态"`
	SortType     string `description:"如何排序，是正序还是倒序，枚举值：asc 正序,desc 倒叙 "`
	CompanyIds   string `description:"标签，多个标签用、隔开；长度255"`
}

type UpdateLogDate struct {
	CompanyId  int    `description:"客户id"`
	CreateTime string `description:"创建时间"`
}

// GetLatestDateFromUpdateLog 获取试用和活跃用户的转试用时间
func GetLatestDateFromUpdateLog(condition string) (list []*UpdateLogDate, err error) {
	o := orm.NewOrm()
	sql := ` SELECT company_id,MAX(create_time) create_time FROM company_product_update_log WHERE source IN ('thaw','receive','apply_receive','add') `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY company_id`
	_, err = o.Raw(sql).QueryRows(&list)

	return
}