stack_company_statistic.go 9.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197
  1. package models
  2. import (
  3. "github.com/beego/beego/v2/client/orm"
  4. "time"
  5. )
  6. //存量客户数据表
  7. type StackCompanyStatistic struct {
  8. StatisticId int `orm:"column(statistic_id);pk"`
  9. Type string `description:"数据类型,取值范围:新签客户,续约客户,未续约客户"`
  10. CompanyId int `description:"企业客户id"`
  11. CompanyName string `description:"企业客户名称"`
  12. ProductId int `description:"客户产品id"`
  13. ProductName string `description:"客户产品名称"`
  14. ContractNum int `description:"第几份合同,默认是:1"`
  15. ContractId int `description:"合同id,默认是:0"`
  16. SellerId int `description:"所属销售id"`
  17. SellerName string `description:"所属销售名称"`
  18. GroupId int `description:"所属销售分组id"`
  19. DepartmentId int `description:"所属销售部门id"`
  20. Date string `description:"记录日期"`
  21. StartDate string `description:"服务起始时间"`
  22. EndDate string `description:"服务截止时间"`
  23. RegionType string `description:"所属区域,国内,海外"`
  24. CreateTime time.Time `description:"记录添加时间"`
  25. }
  26. //添加存量客户记录
  27. func AddStackCompanyStatistic(item *StackCompanyStatistic) (err error) {
  28. o := orm.NewOrm()
  29. _, err = o.Insert(item)
  30. return
  31. }
  32. //获取某天的存量客户统计数据数量
  33. func GetStackCompanyCount(dayStr string) (count int, err error) {
  34. o := orm.NewOrm()
  35. //产品权限
  36. sql := `SELECT count(1) count FROM stack_company_statistic WHERE date = ?`
  37. err = o.Raw(sql, dayStr).QueryRow(&count)
  38. return
  39. }
  40. //待签约用户列表
  41. type WillExpireCompanyList struct {
  42. CompanyContractId int `description:"合同id"`
  43. ContractType string `description:"合同类型"`
  44. CompanyId int `description:"企业客户id"`
  45. CompanyName string `description:"企业客户名称"`
  46. ProductId int `description:"产品id"`
  47. ProductName string `description:"产品名称"`
  48. CompanyProductId int `description:"客户购买产品授权id"`
  49. ContractCode string `description:"合同编码"`
  50. StartDate string `description:"合同开始日期"`
  51. EndDate string `description:"合同结束日期"`
  52. Money float64 `description:"合同金额"`
  53. PayMethod string `description:"付款方式"`
  54. PayChannel string `description:"付款渠道"`
  55. ImgUrl string `description:"合同图片"`
  56. CreateTime string `description:"合同创建时间"`
  57. ModifyTime string `description:"合同修改时间"`
  58. Status string `description:"合同审批状态,0:待审批,1:已审批;默认:1"`
  59. RegionType string `description:"企业客户所属区域;可选范围:国内,海外"`
  60. SellerId int `description:"归属销售id"`
  61. GroupId int `description:"归属销售分组id"`
  62. DepartmentId int `description:"所属销售部门id"`
  63. SellerName string `description:"归属销售名称"`
  64. ProductStatus string `description:"产品状态"`
  65. Count int `description:"合同数量"`
  66. }
  67. //新签客户数(存量客户)
  68. func GetStackCompanyListV1() (total int64, items []*WillExpireCompanyList, err error) {
  69. o := orm.NewOrm()
  70. //产品权限
  71. sql := `SELECT *,count(1) count FROM (
  72. 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
  73. LEFT JOIN company b ON a.company_id = b.company_id
  74. LEFT JOIN company_product c ON a.company_id = c.company_id and a.product_id=c.product_id where a.status = 1
  75. order by a.end_date desc ) d
  76. GROUP BY company_id,product_id `
  77. total, err = o.Raw(sql).QueryRows(&items)
  78. return
  79. }
  80. //增量客户统计报表列表数据结构
  81. type IncrementalList struct {
  82. CompanyOperationRecordId int `description:"操作记录id"`
  83. CompanyContractId int `description:"合同id"`
  84. ContractType string `description:"合同类型"`
  85. CompanyId int `description:"企业客户id"`
  86. CompanyName string `description:"企业客户名称"`
  87. ProductId int `description:"产品id"`
  88. ProductName string `description:"产品名称"`
  89. CompanyProductId int `description:"客户购买产品授权id"`
  90. ContractCode string `description:"合同编码"`
  91. StartDate string `description:"合同开始日期"`
  92. EndDate string `description:"合同结束日期"`
  93. Money float64 `description:"合同金额"`
  94. PayMethod string `description:"付款方式"`
  95. PayChannel string `description:"付款渠道"`
  96. ImgUrl string `description:"合同图片"`
  97. CreateTime string `description:"合同创建时间"`
  98. ModifyTime string `description:"合同修改时间"`
  99. Status string `description:"合同审批状态,0:待审批,1:已审批;默认:1"`
  100. RegionType string `description:"企业客户所属区域;可选范围:国内,海外"`
  101. SellerId int `description:"归属销售id"`
  102. GroupId int `description:"归属分组id"`
  103. DepartmentId int `description:"归属部门id"`
  104. SellerName string `description:"归属销售名称"`
  105. ExpireDay string `description:"剩余可用天数"`
  106. }
  107. //未续约客户数(存量客户)
  108. func GetNotRenewalCompanyTotalV1(dateTime string) (total int64, items []*IncrementalList, err error) {
  109. o := orm.NewOrm()
  110. //产品权限
  111. sql := `SELECT * FROM
  112. ( 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
  113. FROM company_operation_record a
  114. RIGHT JOIN company b ON a.company_id = b.company_id
  115. JOIN company_product c ON b.company_id = c.company_id AND a.product_id = c.product_id
  116. WHERE
  117. 1 = 1 AND c.STATUS NOT IN ( "永续", "正式" ) AND a.create_time <= ?
  118. AND a.operation = 'try_out'
  119. ORDER BY create_time ASC
  120. ) f
  121. GROUP BY company_id, product_id
  122. ORDER BY create_time DESC , company_id DESC `
  123. total, err = o.Raw(sql, dateTime).QueryRows(&items)
  124. return
  125. }
  126. // GetIncrementalCompanyListByOperationRecord 获取未续约客户报表列表数据(根据新增客户时间来展示)
  127. func GetIncrementalCompanyListByOperationRecord(condition string, pars []interface{}) (items []*IncrementalList, err error) {
  128. o := orm.NewOrm()
  129. sql := `SELECT a.id company_operation_record_id,a.company_id,b.company_name,c.seller_id,c.seller_name,
  130. a.product_id,a.product_name,a.create_time,b.region_type,c.renewal_reason FROM company_operation_record a
  131. RIGHT JOIN company b ON a.company_id = b.company_id
  132. JOIN company_product c ON b.company_id = c.company_id and a.product_id=c.product_id WHERE 1 = 1 `
  133. if condition != "" {
  134. sql += condition
  135. }
  136. sql = `select * from (` + sql + ` order by create_time asc) f group by company_id,product_id order by create_time desc,company_id desc `
  137. _, err = o.Raw(sql, pars).QueryRows(&items)
  138. return
  139. }
  140. // GetTodayStackCompanyList 获取当天存量客户报表列表数据(新签客户,根据合同来展示)
  141. func GetTodayStackCompanyList(condition string, pars []interface{}) (items []*IncrementalList, err error) {
  142. o := orm.NewOrm()
  143. sql := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name,c.renewal_reason FROM company_contract a
  144. JOIN company b ON a.company_id = b.company_id
  145. JOIN company_product c ON a.company_id = c.company_id and a.product_id=c.product_id WHERE a.status = 1 `
  146. if condition != "" {
  147. sql += condition
  148. }
  149. sql += " order by a.start_date desc "
  150. sql = `select * from (` + sql + `) b order by start_date desc,company_id desc`
  151. _, err = o.Raw(sql, pars).QueryRows(&items)
  152. return
  153. }
  154. // GetTodayStackCompanyListV2 获取当天存量客户报表列表数据(续约客户,根据合同来展示)
  155. func GetTodayStackCompanyListV2(condition string, pars []interface{}) (items []*IncrementalList, err error) {
  156. o := orm.NewOrm()
  157. sql1 := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name,c.renewal_reason FROM company_contract a
  158. JOIN company b ON a.company_id = b.company_id
  159. 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 `
  160. if condition != "" {
  161. sql1 += condition
  162. }
  163. 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 > ?) `
  164. sql2 := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name,c.renewal_reason FROM company_contract a
  165. JOIN company b ON a.company_id = b.company_id
  166. 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 `
  167. if condition != "" {
  168. sql2 += condition
  169. }
  170. 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 > ?) `
  171. sql := `select * from (` + sql1 + ` union (` + sql2 + `) ` + `) f order by start_date desc`
  172. sql = `select * from (` + sql + `) b order by start_date desc,company_id desc`
  173. pars = append(pars, pars...)
  174. _, err = o.Raw(sql, pars).QueryRows(&items)
  175. return
  176. }