hz_crm_api/models/statistic_report/rai_data_summary.go

package statistic_report

import (
	"github.com/beego/beego/v2/client/orm"
	"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 {
	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:"权益合同类型：枚举值：'新签合同','续约合同','补充协议'"`
}

// GetRaiDataSummaryList 获取增量客户报表列表数据（根据合同来展示）
func GetRaiDataSummaryList(condition string, pars []interface{}) (items []*IncrementalList, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.start_date,
			   a.end_date,
			   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
}