hongze_task/models/stack_company_statistic.go

package models

import (
	"rdluck_tools/orm"
	"time"
)

//存量客户数据表
type StackCompanyStatistic struct {
	StatisticId  int       `orm:"column(statistic_id);pk"`
	Type         string    `description:"数据类型，取值范围：新签客户，续约客户，未续约客户"`
	CompanyId    int       `description:"企业客户id"`
	CompanyName  string    `description:"企业客户名称"`
	ProductId    int       `description:"客户产品id"`
	ProductName  string    `description:"客户产品名称"`
	ContractNum  int       `description:"第几份合同，默认是：1"`
	SellerId     int       `description:"所属销售id"`
	SellerName   string    `description:"所属销售名称"`
	GroupId      int       `description:"所属销售分组id"`
	DepartmentId int       `description:"所属销售部门id"`
	Date         string    `description:"记录日期"`
	StartDate    string    `description:"服务起始时间"`
	EndDate      string    `description:"服务截止时间"`
	RegionType   string    `description:"所属区域，国内，海外"`
	CreateTime   time.Time `description:"记录添加时间"`
}

//添加存量客户记录
func AddStackCompanyStatistic(item *StackCompanyStatistic) (err error) {
	o := orm.NewOrm()
	_, err = o.Insert(item)
	return
}

//获取某天的存量客户统计数据数量
func GetStackCompanyCount(dayStr string) (count int, err error) {
	o := orm.NewOrm()
	//产品权限
	sql := `SELECT count(1) count FROM stack_company_statistic WHERE date = ?`

	err = o.Raw(sql, dayStr).QueryRow(&count)
	return
}

//待签约用户列表
type WillExpireCompanyList struct {
	CompanyContractId int     `description:"合同id"`
	ContractType      string  `description:"合同类型"`
	CompanyId         int     `description:"企业客户id"`
	CompanyName       string  `description:"企业客户名称"`
	ProductId         int     `description:"产品id"`
	ProductName       string  `description:"产品名称"`
	CompanyProductId  int     `description:"客户购买产品授权id"`
	ContractCode      string  `description:"合同编码"`
	StartDate         string  `description:"合同开始日期"`
	EndDate           string  `description:"合同结束日期"`
	Money             float64 `description:"合同金额"`
	PayMethod         string  `description:"付款方式"`
	PayChannel        string  `description:"付款渠道"`
	ImgUrl            string  `description:"合同图片"`
	CreateTime        string  `description:"合同创建时间"`
	ModifyTime        string  `description:"合同修改时间"`
	Status            string  `description:"合同审批状态，0:待审批，1:已审批；默认：1"`
	RegionType        string  `description:"企业客户所属区域；可选范围：国内，海外"`
	SellerId          int     `description:"归属销售id"`
	GroupId           int     `description:"归属销售分组id"`
	DepartmentId      int     `description:"所属销售部门id"`
	SellerName        string  `description:"归属销售名称"`
	ProductStatus     string  `description:"产品状态"`
	Count             int     `description:"合同数量"`
}

//新签客户数（存量客户）
func GetStackCompanyListV1() (total int64, items []*WillExpireCompanyList, err error) {
	o := orm.NewOrm()
	//产品权限
	sql := `SELECT *,count(1) count FROM (
SELECT a.*,b.region_type,c.seller_id,c.seller_name,c.company_name,c.group_id,c.department_id,c.status as product_status FROM company_contract a
		LEFT JOIN company b ON a.company_id = b.company_id
		LEFT JOIN company_product c ON a.company_id = c.company_id and a.product_id=c.product_id  where a.status = 1
order by a.end_date desc ) d 
GROUP BY company_id,product_id `

	total, err = o.Raw(sql).QueryRows(&items)
	return
}

//增量客户统计报表列表数据结构
type IncrementalList struct {
	CompanyContractId int     `description:"合同id"`
	ContractType      string  `description:"合同类型"`
	CompanyId         int     `description:"企业客户id"`
	CompanyName       string  `description:"企业客户名称"`
	ProductId         int     `description:"产品id"`
	ProductName       string  `description:"产品名称"`
	CompanyProductId  int     `description:"客户购买产品授权id"`
	ContractCode      string  `description:"合同编码"`
	StartDate         string  `description:"合同开始日期"`
	EndDate           string  `description:"合同结束日期"`
	Money             float64 `description:"合同金额"`
	PayMethod         string  `description:"付款方式"`
	PayChannel        string  `description:"付款渠道"`
	ImgUrl            string  `description:"合同图片"`
	CreateTime        string  `description:"合同创建时间"`
	ModifyTime        string  `description:"合同修改时间"`
	Status            string  `description:"合同审批状态，0:待审批，1:已审批；默认：1"`
	RegionType        string  `description:"企业客户所属区域；可选范围：国内，海外"`
	SellerId          int     `description:"归属销售id"`
	GroupId           int     `description:"归属分组id"`
	DepartmentId      int     `description:"归属部门id"`
	SellerName        string  `description:"归属销售名称"`
	ExpireDay         string  `description:"剩余可用天数"`
}

//未续约客户数（存量客户）
func GetNotRenewalCompanyTotalV1(dateTime string) (total int64, items []*IncrementalList, err error) {
	o := orm.NewOrm()
	//产品权限
	sql := `SELECT *  FROM
	( SELECT a.id,a.company_id,a.company_name,c.seller_id,c.seller_name,c.group_id,c.department_id,a.product_id,a.product_name,a.create_time,b.region_type 
	FROM company_operation_record a
		RIGHT JOIN company b ON a.company_id = b.company_id
		JOIN company_product c ON b.company_id = c.company_id AND a.product_id = c.product_id 
	WHERE
		1 = 1  AND c.STATUS NOT IN ( "永续", "正式" )  AND a.create_time <= ? 
		AND a.operation = 'try_out' 
	ORDER BY create_time ASC 
	) f 
GROUP BY company_id, product_id 
ORDER BY create_time DESC , company_id DESC  `

	total, err = o.Raw(sql, dateTime).QueryRows(&items)
	return
}