hz_crm_api/models/company/company_contract_merge.go

package company

import (
	//"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"time"
)

type CompanyContractMerge struct {
	CompanyContractId      int       `orm:"column(company_contract_merge_id);pk" description:"主键ID"`
	ContractType           string    `description:"合同类型：枚举值：'新签合同','续约合同','补充协议'"`
	ProductId              int       `description:"产品id"`
	ProductName            string    `description:"产品名称"`
	CompanyId              int       `description:"客户id"`
	CompanyProductId       int       `description:"客户产品id"`
	StartDate              string    `description:"合同开始时间"`
	EndDate                string    `description:"合同结束时间"`
	Money                  float64   `description:"合同金额"`
	CreateTime             time.Time `description:"合同创建时间"`
	ModifyTime             time.Time `description:"合同修改时间"`
	CompanyContractIdGroup string    `description:"表company_contract合并的 company_contract_id"`
	ChartPermissionName    string    `description:"权限类目名称"`
}

type CompanyContractMergeResp struct {
	CompanyContractId      int       `orm:"column(company_contract_merge_id);pk" description:"主键ID"`
	ContractType           string    `description:"合同类型：枚举值：'新签合同','续约合同','补充协议'"`
	ProductId              int       `description:"产品id"`
	ProductName            string    `description:"产品名称"`
	CompanyId              int       `description:"客户id"`
	CompanyProductId       int       `description:"客户产品id"`
	StartDate              time.Time `description:"合同开始时间"`
	EndDate                time.Time `description:"合同结束时间"`
	Money                  float64   `description:"合同金额"`
	CreateTime             time.Time `description:"合同创建时间"`
	ModifyTime             time.Time `description:"合同修改时间"`
	CompanyContractIdGroup string    `description:"表company_contract合并的 company_contract_id"`
	ChartPermissionName    string    `description:"权限类目名称"`
}

// MultiAddCompanyContractMerge 批量添加合同合并之后的数据
func MultiAddCompanyContractMerge(items []*CompanyContractMerge) (err error) {
	if len(items) == 0 {
		return
	}
	o := orm.NewOrm()
	_, err = o.InsertMulti(len(items), items)
	return
}

// 增量客户统计报表列表数据结构
type IncrementalList struct {
	CompanyContractId   int                        `description:"合同id"`
	ContractType        string                     `description:"合同类型"`
	CompanyId           int                        `description:"企业客户id"`
	CompanyName         string                     `description:"企业客户名称"`
	ProductId           int                        `description:"产品id"`
	ProductName         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:"归属销售名称"`
	ExpireDay           string                     `description:"剩余可用天数"`
	PermissionList      []*CompanyReportPermission `description:"产品权限"`
	Count               int                        `json:"-" description:"合同数"`
	RenewalReason       string                     `description:"未续约说明"`
	RenewalTodo         string                     `description:"未续约说明中的待办事项说明"`
	PackageDifference   string                     `description:"和上一份合同的区别"`
	AscribeContent      string                     `description:"归因标签说明"`
	IsShowNoRenewedNote bool                       `description:"是否展示未续约备注按钮"`
	Content             string                     `description:"归因内容说明"`
}

// 增量客户统计报表返回类
type IncrementalCompanyListResp struct {
	List                   []*IncrementalList
	Paging                 *paging.PagingItem `description:"分页数据"`
	TrialTotal             int                `description:"新增试用客户数"`
	NewCompanyTotal        int                `description:"新签客户数"`
	RenewalCompanyTotal    int                `description:"续约客户数"`
	NotRenewalCompanyTotal int                `description:"未续约客户数"`
	NotRenewalTryOut       int                `description:"未续约客户（试用）数"`
	NotRenewalNotTryOut    int                `description:"未续约客户（非试用）数"`
}

// GetIncrementalNewCompanyProductMergeCount 获取增量客户产品报表列表统计数据（根据合同来展示）
func GetIncrementalNewCompanyProductMergeCount(condition string, pars []interface{}) (total int, err error) {
	o := orm.NewOrm()

	sql := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name FROM company_contract_merge 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 = `select count(1) count from (` + sql + ` group by company_id,product_id) f`
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// GetIncrementalNewCompanyList 获取增量客户报表列表数据（根据合同来展示）
func GetIncrementalNewCompanyMergeList(condition string, pars []interface{}, startSize, pageSize int) (items []*IncrementalList, err error) {
	o := orm.NewOrm()

	sql := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name,c.renewal_reason FROM company_contract_merge 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 += " order by a.start_date desc "
	sql = `select *,count(*) count from (` + sql + `) b group by company_id,product_id  order by start_date desc,company_id desc limit ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// GetIncrementalRenewalCompanyProductMergeCount 续约合同数量
func GetIncrementalRenewalCompanyProductMergeCount(condition string, pars []interface{}) (total int, err error) {
	o := orm.NewOrm()

	sql := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name FROM company_contract_merge 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 = `select count(1) count from (` + sql + ` group by company_id,product_id) f`
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// GetIncrementalRenewalCompanyMergeList续约合同列表
func GetIncrementalRenewalCompanyMergeList(condition string, pars []interface{}, startSize, pageSize int) (items []*IncrementalList, err error) {
	o := orm.NewOrm()

	sql := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name,c.renewal_reason FROM company_contract_merge 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 += " order by a.start_date desc "
	sql = `select *,count(*) count from (` + sql + `) b group by company_id,product_id  order by start_date desc,company_id desc limit ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}