123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197 |
- package models
- import (
- "github.com/beego/beego/v2/client/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"`
- ContractId int `description:"合同id,默认是:0"`
- 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 {
- CompanyOperationRecordId int `description:"操作记录id"`
- 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
- }
- // GetIncrementalCompanyListByOperationRecord 获取未续约客户报表列表数据(根据新增客户时间来展示)
- func GetIncrementalCompanyListByOperationRecord(condition string, pars []interface{}) (items []*IncrementalList, err error) {
- o := orm.NewOrm()
- sql := `SELECT a.id company_operation_record_id,a.company_id,b.company_name,c.seller_id,c.seller_name,
- a.product_id,a.product_name,a.create_time,b.region_type,c.renewal_reason 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 `
- if condition != "" {
- sql += condition
- }
- sql = `select * from (` + sql + ` order by create_time asc) f group by company_id,product_id order by create_time desc,company_id desc `
- _, err = o.Raw(sql, pars).QueryRows(&items)
- return
- }
- // GetTodayStackCompanyList 获取当天存量客户报表列表数据(新签客户,根据合同来展示)
- func GetTodayStackCompanyList(condition string, pars []interface{}) (items []*IncrementalList, err error) {
- o := orm.NewOrm()
- sql := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name,c.renewal_reason FROM company_contract a
- JOIN company b ON a.company_id = b.company_id
- JOIN company_product c ON a.company_id = c.company_id and a.product_id=c.product_id WHERE a.status = 1 `
- if condition != "" {
- sql += condition
- }
- sql += " order by a.start_date desc "
- sql = `select * from (` + sql + `) b order by start_date desc,company_id desc`
- _, err = o.Raw(sql, pars).QueryRows(&items)
- return
- }
- // GetTodayStackCompanyListV2 获取当天存量客户报表列表数据(续约客户,根据合同来展示)
- func GetTodayStackCompanyListV2(condition string, pars []interface{}) (items []*IncrementalList, err error) {
- o := orm.NewOrm()
- sql1 := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name,c.renewal_reason FROM company_contract a
- JOIN company b ON a.company_id = b.company_id
- JOIN company_product c ON a.company_id = c.company_id and a.product_id=c.product_id WHERE a.status = 1 and a.product_id=1 `
- if condition != "" {
- sql1 += condition
- }
- sql1 += ` and a.company_id not in (select company_id from company_contract where contract_type="新签合同" and status=1 and product_id=1 and end_date > ?) `
- sql2 := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name,c.renewal_reason FROM company_contract a
- JOIN company b ON a.company_id = b.company_id
- JOIN company_product c ON a.company_id = c.company_id and a.product_id=c.product_id WHERE a.status = 1 and a.product_id=2 `
- if condition != "" {
- sql2 += condition
- }
- sql2 += ` and a.company_id not in (select company_id from company_contract where contract_type="新签合同" and status=1 and product_id=2 and end_date > ?) `
- sql := `select * from (` + sql1 + ` union (` + sql2 + `) ` + `) f order by start_date desc`
- sql = `select * from (` + sql + `) b order by start_date desc,company_id desc`
- pars = append(pars, pars...)
- _, err = o.Raw(sql, pars).QueryRows(&items)
- return
- }
|