hz_crm_api/models/stack_company_statistic.go

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"`
	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 GetStackCompanyCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	//o.Using("rddp")
	//产品权限

	sql := `SELECT count(1) count from stack_company_statistic a 
join company c on a.company_id=c.company_id WHERE 1 = 1 `

	if condition != "" {
		sql += condition
	}

	sql = `SELECT count(1) count from ( ` + sql + ` group by a.company_id) b `
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// 获取客户产品数量
func GetStackCompanyProductCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	//o.Using("rddp")
	//产品权限

	sql := `SELECT statistic_id from stack_company_statistic a 
join company c on a.company_id=c.company_id WHERE 1 = 1 `

	if condition != "" {
		sql += condition
	}
	sql = `SELECT count(1) count from ( ` + sql + ` group by a.company_id,a.product_id) b `
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// GetNotRenewalStackCompanyProductCount 获取未续约客户产品数量
func GetNotRenewalStackCompanyProductCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	//o.Using("rddp")
	//产品权限

	sql := `SELECT statistic_id from stack_company_statistic a 
left join company_product b on a.company_id=b.company_id and a.product_id=b.product_id
join company c on a.company_id=c.company_id WHERE 1 = 1 `

	if condition != "" {
		sql += condition
	}
	sql = `SELECT count(1) count from ( ` + sql + ` group by a.company_id,a.product_id) b `
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// 获取收入统计报表列表数据（根据合同来展示）
type StackCompanyStatisticList struct {
	StatisticId         int       `orm:"column(statistic_id);pk"`
	Type                string    `description:"数据类型，取值范围：新签客户，续约客户，未续约客户"`
	Status              string    `description:"当前状态"`
	CompanyId           int       `description:"企业客户id"`
	CompanyName         string    `description:"企业客户名称"`
	ProductId           int       `description:"客户产品id"`
	ProductName         string    `description:"客户产品名称"`
	ContractNum         int       `description:"第几份合同，默认是：1"`
	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:"记录添加时间"`
	CreateTimeStr       string    `description:"记录添加时间，字符串形式"`
	ExpireDay           string    `description:"剩余可用天数"`
	Count               int       `json:"-"`
	RenewalReason       string    `description:"未续约说明"`
	RenewalTodo         string    `description:"未续约说明中的待办事项说明"`
	PackageDifference   string    `description:"和上一份合同的区别"`
	AscribeContent      string    `description:"归因标签说明"`
	IsShowNoRenewedNote bool      `description:"是否展示未续约备注按钮"`
	Content             string    `description:"归因内容说明"`
}

func GetStackCompanyList(condition, orderBy string, pars []interface{}, startSize, pageSize int) (items []*StackCompanyStatisticList, err error) {
	o := orm.NewOrm()
	/*sql := `SELECT *,count(1) count from stack_company_statistic WHERE 1 = 1 `

	if condition != "" {
		sql += condition
	}
	sql += " group by company_id order by end_date asc LIMIT ?,? "*/

	//sql := `SELECT a.*,b.renewal_reason,b.status from stack_company_statistic a
	//left join company_product b on a.company_id=b.company_id and a.product_id=b.product_id
	//join company c on a.company_id=c.company_id  WHERE 1 = 1 `

	sql := `SELECT a.statistic_id,a.type,a.company_id,c.company_name,a.product_id,a.product_name,a.contract_id,a.contract_num,a.seller_id,a.seller_name,a.group_id,a.department_id,a.date,a.start_date,a.end_date,a.region_type,a.create_time,b.renewal_reason,b.renewal_todo,b.status from stack_company_statistic a
	left join company_product b on a.company_id=b.company_id and a.product_id=b.product_id
	join company c on a.company_id=c.company_id  WHERE 1 = 1 `
	if condition != "" {
		sql += condition
	}
	if orderBy == "" {
		orderBy = ` end_date asc`
	}
	sql = "select *,count(1) count from (" + sql + " order by a.end_date asc) c group by company_id,product_id order by " + orderBy + ",company_id desc LIMIT ?,? "
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// GetStackCompanyListFromReportRecord 查找存量客户数据FromReportRecord
func GetStackCompanyListFromReportRecord(condition, orderBy string, pars []interface{}, startSize, pageSize int) (items []*StackCompanyStatisticList, err error) {
	o := orm.NewOrm()

	sql := `SELECT a.company_report_record_id,a.status,a.company_id,c.company_name,a.product_id,a.product_name,a.contract_id,a.contract_num,a.seller_id,a.seller_name,a.group_id,a.department_id,a.date,a.start_date,a.end_date,a.region_type,a.create_time,b.renewal_reason,b.status from company_report_record a
	left join company_product b on a.company_id=b.company_id and a.product_id=b.product_id
	join company c on a.company_id=c.company_id  WHERE 1 = 1 `
	if condition != "" {
		sql += condition
	}
	if orderBy == "" {
		orderBy = ` end_date asc`
	}
	sql = "select *,count(1) count from (" + sql + " order by a.end_date asc) c group by company_id,product_id order by " + orderBy + ",company_id desc LIMIT ?,? "
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// 根据企业客户编号数据集、记录编号数据集获取不在里面的数据列表
func GetStackCompanyListByCompanyIds(companyIds, statisticIds, condition string, pars []interface{}) (items []*StackCompanyStatisticList, err error) {
	o := orm.NewOrm()
	sql := `SELECT * from stack_company_statistic WHERE 1 = 1 AND company_id in (` + companyIds + `) 
AND statistic_id not in (` + statisticIds + `) `

	if condition != "" {
		sql += condition
	}
	sql += " order by end_date desc "
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}