hz_crm_api/models/company/company_contract_merge.go

package company

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

type CompanyContractMergeResp struct {
	CompanyContractMergeId 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:"权限类目名称"`
	PackageDifference      string    `description:"和上一份合同的区别"`
}

type CompanyContractMergeDetailResp struct {
	CompanyContractMergeId 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:"权限类目名称"`
	PackageDifference      string    `description:"和上一份合同的区别"`
	PermissionName         string    `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 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 + `  ) f`
	err = o.Raw(sql, pars).QueryRow(&total)
	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 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 + ` ) f`
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

type GetCompanyContractMergeDetailResp struct {
	Detail *CompanyContractMergeDetailResp
}

// GetIncrementalNewCompanyProductMergeCount 获取增量客户产品报表列表统计数据（根据合同来展示）
func GetIncrementalNewCompanyProductPermissionCount(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 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
		 JOIN company_contract_permission d ON d.company_contract_id = a.company_contract_id
		 WHERE 1 = 1  `

	if condition != "" {
		sql += condition
	}
	sql = `select count(1) count from (` + sql + ` GROUP BY d.company_contract_id,d.permission_name  ) f`
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// GetIncrementalRenewalCompanyProductMergeSumMoney 对于签约的合同金额进行求和运算
func GetIncrementalRenewalCompanyProductMergeSumMoney(condition string, pars []interface{}) (total float64, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			SUM( a.money ) AS total 
		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
	}
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// 权益客户统计，续约率搜索自然年的年份，返回类
type SearchYearListResp struct {
	List []int
}