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:"和上一份合同的区别"`
}

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
}