hz_crm_api/models/statistic_report/rai_data_summary.go

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 {
	IsServerSeller                   bool   `description:"是否属于服务组销售"`
	SellerId                         string `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"`
	ShareSellerIdInit int    `description:"共享销售员"`
	ShareSellerIdLast int    `description:"合同到期之前最后共享销售员ID"`
	SysRealName       string `description:"操作者名称"`
	Operation         string `description:"操作"`
	RaiContractType   string `description:"权益合同类型：枚举值：'新签合同','续约合同','补充协议'"`
	InheritEndDate    string `description:"所继承上一份合同的结束日期，权益自定义续约合同统计使用"`
	DueEndDate        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.due_end_date,
			   a.company_contract_id,
			   a.money,
			   a.company_id,
			   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.company_id,
			   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 {
	CompanyId              int     `description:"企业客户ID"`
	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列占位符"` //配合前端渲染使用，没有啥实际意义
}