package statistic_report import ( "github.com/beego/beego/v2/client/orm" "github.com/rdlucklib/rdluck_tools/paging" "hongze/hz_crm_api/models/company" ) // 时间区间数据结构 type DateInterval struct { StartDate string `description:"合同开始日期"` EndDate string `description:"合同结束日期"` } type RaiDataSummaryListResp struct { List []*RaiDataSummaryResp } type RaiDataSummaryResp struct { DataType string `description:"时间区间"` DataList []*RaiDataSummaryDetail } type RaiDataSummaryDetail struct { SellerId int `description:"销售id"` SellerName string `description:"销售名称"` AddTrialCount string `description:"新增试用"` NewContractData string `description:"新签合同（金额/数量)"` ExpiredContractData string `description:"到期合同（金额/数量)"` RenewedContractData string `description:"续约合同（金额/数量)"` RenewalRateData string `description:"续约率（金额/数量)"` ConfirmedNoRenewalContractData string `description:"确认不续约合同（金额/数量)"` ConfirmNonRenewalRateData string `description:"确认不续约率（金额/数量)"` SignedClientCount string `description:"签约客户数量"` AverageRevenueCount string `description:"客单价"` InvoiceAmountCount string `description:"开票金额"` PaymentReceivedCount string `description:"到款金额"` UnpaidRatioCount string `description:"未到款比例"` NewCustomerInvoicingCount string `description:"新客开票"` NewCustomerPaymentsReceivedCount string `description:"新客到款"` } // 增量客户统计报表列表数据结构 type IncrementalList struct { CompanyContractId int `description:"合同id"` ContractType string `description:"合同类型"` CompanyId int `description:"企业客户id"` CompanyName string `description:"企业客户名称"` ProductId int `description:"产品id"` ProductName string `description:"产品名称"` ProductStatus 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"` SellerName string `description:"归属销售名称"` SellerNameLast string `description:"合同到期之前最后所属归属销售名称"` ShareSeller string `description:"合同到期之前最后所属共享销售员"` ShareSellerLast string `description:"共享销售员"` ExpireDay string `description:"剩余可用天数"` PermissionList []*company.CompanyReportPermission `description:"产品权限"` Count int `json:"-" description:"合同数"` RenewalReason string `description:"未续约说明"` RenewalTodo string `description:"未续约说明中的待办事项说明"` PackageDifference string `description:"和上一份合同的区别"` AscribeContent string `description:"归因标签说明"` IsShowNoRenewedNote bool `description:"是否展示未续约备注按钮"` Content string `description:"归因内容说明"` PermissionName string `description:"权限名"` PermissionNameExport string `description:"权限名导出时使用"` PermissionNameStatus string `description:"权限状态"` CompanyProductStatus string `description:"客户状态"` //CompanyContractIdGroup string `description:"表company_contract合并的 company_contract_id"` IsUserMaker int `description:"近四周之内是否包含决策人互动过 ，0否，1是"` SellerNameInit string `description:"权益初始化销售"` SellerIdInit int `description:"权益初始化销售ID"` SellerIdLast int `description:"合同到期之前最后所属销售ID"` ShareSellerInit string `description:"共享销售员"` SysRealName string `description:"操作者名称"` Operation string `description:"操作"` RaiContractType string `description:"权益合同类型：枚举值：'新签合同','续约合同','补充协议'"` InheritEndDate string `description:"所继承上一份合同的结束日期，权益自定义续约合同统计使用"` } // GetRaiDataSummaryList 获取增量客户报表列表数据（根据合同来展示） func GetRaiDataSummaryList(condition string, pars []interface{}) (items []*IncrementalList, err error) { o := orm.NewOrm() sql := `SELECT a.start_date, a.end_date, a.company_contract_id, a.money, a.rai_contract_type, a.seller_id_init, a.seller_name_init, a.share_seller_init, a.share_seller_id_init, a.seller_id_last, a.seller_name_last, a.share_seller_last, a.share_seller_id_last 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 1 = 1 ` if condition != "" { sql += condition } sql += ` group by a.company_contract_id order by a.start_date desc,a.company_id desc ` _, err = o.Raw(sql, pars).QueryRows(&items) return } // GetRaiDataSummaryList 获取增量客户报表列表数据（根据合同来展示） func GetRaiDataSummaryInheritList(condition string, pars []interface{}) (items []*IncrementalList, err error) { o := orm.NewOrm() sql := `SELECT a.start_date, a.end_date, a.inherit_end_date, a.company_contract_id, a.money, a.rai_contract_type, a.seller_id_init, a.seller_name_init, a.share_seller_init, a.share_seller_id_init, a.seller_id_last, a.seller_name_last, a.share_seller_last, a.share_seller_id_last 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 1 = 1 ` if condition != "" { sql += condition } sql += ` group by a.company_contract_id order by a.start_date desc,a.company_id desc ` _, err = o.Raw(sql, pars).QueryRows(&items) return } type RaiDataSummaryPopupTypeResp struct { CompanyNum int `description:"企业客户数量"` CompanyMultiple int `description:"户有多份合同的企业客户数量"` Paging *paging.PagingItem `description:"分页数据"` List []*RaiDataSummaryDetailResp `description:"列表"` } // 增量客户统计报表列表数据结构 type RaiDataSummaryDetailResp struct { CompanyName string `description:"企业客户名称"` SellerName string `description:"归属销售名称"` AddType string `description:"新增类型"` CreateTime string `description:"创建时间"` StartDate string `description:"合同开始日期"` EndDate string `description:"合同结束日期"` Money float64 `description:"合同金额"` PermissionName string `description:"权限名"` RenewedContractMoney string `description:"续约金额"` ExpiredContractMoney string `description:"到期金额"` RenewalRate string `description:"续约率"` RenewedContractCompany int `description:"续约客户数"` RenewedContract int `description:"续约合同数"` NewContractCompany int `description:"新签客户数"` NewContract int `description:"新签合同数"` ContractMoney string `description:"签约总金额"` ContractNum int `description:"签约客户数"` ContractCode string `description:"合同编号"` InvoicedAmount float64 ` description:"开票金额"` PaymentAmount float64 ` description:"到款金额"` NotReceivedtAmount float64 ` description:"未到账金额"` TbaleNameAText string ` description:"表格A列占位符"` //配合前端渲染使用，没有啥实际意义 }