hz_crm_api/models/contract/contract.go

package contract

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

// 合同
type Contract struct {
	ContractId           int       `orm:"column(contract_id);pk"`
	ContractCode         string    `description:"合同编号,长度32位"`
	SellerId             int       `description:"所属销售id"`
	SellerName           string    `description:"所属销售名称"`
	ProductId            int       `description:"产品id，1：ficc；2：权益"`
	ContractBusinessType string    `description:"合同业务类型，枚举值：'业务合同','代付合同'"`
	ContractType         string    `description:"合同类型，枚举值：'新签合同','续约合同','补充协议'"`
	Status               string    `description:"合同状态，枚举值：'待提交','待审批','已撤回','已审批','已驳回','已作废','已解约'"`
	StartDate            time.Time `description:"合同开始日期"`
	EndDate              time.Time `description:"合同结束日期"`
	OriginalPrice        float64   `description:"合同原金额，优惠前的金额"`
	Price                float64   `description:"实际金额，优惠后的金额"`
	PayRemark            string    `description:"付款方式说明,长度255位"`
	PayChannel           string    `description:"付款渠道,长度255位"`
	CompanyName          string    `description:"客户名称，甲方名称,长度32位"`
	CreditCode           string    `description:"社会统一信用代码,长度64位"`
	ProvinceId           int       `description:"省级id"`
	Province             string    `description:"省级名称,长度16位"`
	CityId               int       `description:"市级id"`
	City                 string    `description:"市级名称,长度32位"`
	Address              string    `description:"详细地址"`
	Fax                  string    `description:"传真,长度32位"`
	Phone                string    `description:"电话,长度32位"`
	Postcode             string    `description:"邮编,长度16位"`
	Remark               string    `description:"补充内容,长度255位"`
	SellerRemark         string    `description:"销售备注,长度255位"`
	ModifyContent        string    `description:"修改内容"`
	ApprovalRemark       string    `description:"审核备注"`
	FileUrl              string    `description:"合同文件地址"`
	CheckBackFileUrl     string    `description:"签回合同文件地址"`
	RescindFileUrl       string    `description:"解约合同文件地址"`
	TemplateId           int       `description:"模板id"`
	SourceId             int       `description:"来源合同id，默认是0；如果是通过其他合同复制过来的，那么就是原合同的id"`
	IsDelete             int       `description:"是否已经删除，0：未删除，1：已删除" json:"-"`
	ApproveTime          time.Time `description:"审批时间"`
	InvalidTime          time.Time `description:"作废时间"`
	CheckBackFileTime    time.Time `description:"合同签回时间"`
	RescindTime          time.Time `description:"解约时间"`
	ModifyTime           time.Time `description:"合同最近一次修改时间"`
	CreateTime           time.Time `description:"合同添加时间"`
}

// 更新合同基础信息
func (contract *Contract) Update(cols []string) (err error) {
	o := orm.NewOrm()
	_, err = o.Update(contract, cols...)
	return
}

// 根据合同id获取合同信息
func GetContractById(contractId int) (contractInfo *Contract, err error) {
	o := orm.NewOrm()
	sql := `select * from contract where contract_id = ? `
	err = o.Raw(sql, contractId).QueryRow(&contractInfo)
	return
}

// GetContractByCode 根据合同编号获取合同信息
func GetContractByCode(contractCode string) (contractInfo *Contract, err error) {
	o := orm.NewOrm()
	sql := `select * from contract where contract_code = ? `
	err = o.Raw(sql, contractCode).QueryRow(&contractInfo)
	return
}

// 合同详情信息（包含服务信息等）
type ContractDetail struct {
	ContractId           int       `description:"合同唯一id"`
	ContractCode         string    `description:"合同编号,长度32位"`
	SellerId             int       `description:"所属销售id"`
	SellerName           string    `description:"所属销售名称"`
	ProductId            int       `description:"产品id，1：ficc；2：权益"`
	ContractBusinessType string    `description:"合同业务类型，枚举值：'业务合同','代付合同'"`
	ContractType         string    `description:"合同类型，枚举值：'新签合同','续约合同','补充协议'"`
	Status               string    `description:"合同状态，枚举值：'待提交','待审批','已撤回','已审批','已驳回','已作废','已解约'"`
	StartDate            time.Time `description:"合同开始日期"`
	EndDate              time.Time `description:"合同结束日期"`
	OriginalPrice        float64   `description:"合同原金额，优惠前的金额"`
	Price                float64   `description:"实际金额，优惠后的金额"`
	PayRemark            string    `description:"付款方式说明,长度255位"`
	PayChannel           string    `description:"付款渠道,长度255位"`
	CompanyName          string    `description:"客户名称，甲方名称,长度32位"`
	CreditCode           string    `description:"社会统一信用代码,长度64位"`
	ProvinceId           int       `description:"省级id"`
	Province             string    `description:"省级名称,长度16位"`
	CityId               int       `description:"市级id"`
	City                 string    `description:"市级名称,长度32位"`
	Address              string    `description:"详细地址"`
	Fax                  string    `description:"传真,长度32位"`
	Phone                string    `description:"电话,长度32位"`
	Postcode             string    `description:"邮编,长度16位"`
	Remark               string    `description:"补充内容,长度255位"`
	SellerRemark         string    `description:"销售备注,长度255位"`
	ModifyContent        string    `description:"修改内容"`
	ApprovalRemark       string    `description:"审核备注"`
	FileUrl              string    `description:"合同文件地址"`
	CheckBackFileUrl     string    `description:"签回合同文件地址"`
	RescindFileUrl       string    `description:"解约合同文件地址"`
	TemplateId           int       `description:"模板id"`
	SourceId             int       `description:"来源合同id，默认是0；如果是通过其他合同复制过来的，那么就是原合同的id"`
	IsDelete             int       `json:"-" description:"是否已经删除，0：未删除，1：已删除"`
	ApproveTime          time.Time `description:"审批时间"`
	InvalidTime          time.Time `description:"作废时间"`
	CheckBackFileTime    time.Time `description:"合同签回时间"`
	RescindTime          time.Time `description:"解约时间"`
	ModifyTime           time.Time `description:"合同最近一次修改时间"`
	CreateTime           time.Time `description:"合同添加时间"`

	StartDateStr               string `description:"合同起始时间（字符串形式）"`
	EndDateStr                 string `description:"合同结束时间（字符串形式）"`
	ApproveTimeStr             string `description:"审批时间（字符串形式）"`
	InvalidTimeStr             string `description:"作废时间（字符串形式）"`
	CheckBackFileTimeStr       string `description:"合同签回时间（字符串形式）"`
	RescindTimeStr             string `description:"解约时间（字符串形式）"`
	ModifyTimeStr              string `description:"最近一次更新时间（字符串形式）"`
	CreateTimeStr              string `description:"合同添加时间（字符串形式）"`
	Service                    []*ContractServiceAndDetail
	RelationContractDetailList []*ContractDetail     `description:"关联合同详情"`
	PermissionLookList         []*PermissionLookList `description:"合同里面的权限列表"`
}

type PermissionLookList struct {
	ClassifyName string `description:"分类"`
	Items        []*PermissionLookItem
}
type PermissionLookItem struct {
	ChartPermissionId int    `description:"权限id"`
	PermissionName    string `description:"权限名称"`
	ClassifyName      string `description:"分类"`
}

// 根据合同id获取合同详情信息
func GetContractDetailById(contractId int) (contractInfo *ContractDetail, err error) {
	o := orm.NewOrm()
	sql := `select * from contract where contract_id = ? `
	err = o.Raw(sql, contractId).QueryRow(&contractInfo)
	return
}

// 合同添加
func AddContract(contractInfo *Contract, contractServiceAndDetailList []*ContractServiceAndDetail, relationId int) (newContract *Contract, err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	//合同数据入库
	contractId, err := to.Insert(contractInfo)
	if err != nil {
		return
	}
	contractInfo.ContractId = int(contractId)
	for i := 0; i < len(contractServiceAndDetailList); i++ {
		//合同服务数据入库
		tmpContractService := contractServiceAndDetailList[i]
		contractService := &ContractService{
			ContractId:        int(contractId),
			ProductId:         contractInfo.ProductId,
			ServiceTemplateId: tmpContractService.ServiceTemplateId,
			Title:             tmpContractService.Title,
			Value:             tmpContractService.Value,
			TableValue:        tmpContractService.TableValue,
			HasDetail:         tmpContractService.HasDetail,
			ChartPermissionId: tmpContractService.ChartPermissionId,
			CreateTime:        time.Now(),
		}
		contractServiceId, serviceErr := to.Insert(contractService)
		if serviceErr != nil {
			err = serviceErr
			return
		}
		contractService.ContractServiceId = int(contractServiceId)

		//合同服务详情入库
		for j := 0; j < len(tmpContractService.DetailList); j++ {
			contractServiceDetail := tmpContractService.DetailList[j]
			//合同服务编号
			contractServiceDetail.ContractServiceId = contractService.ContractServiceId
			contractServiceDetail.ContractId = int(contractId)
			contractServiceDetail.ServiceTemplateId = contractService.ServiceTemplateId

			//合同服务详情入库
			contractServiceDetailId, detailErr := to.Insert(contractServiceDetail)
			if detailErr != nil {
				err = detailErr
				return
			}
			contractServiceDetail.Id = int(contractServiceDetailId)
			tmpContractService.DetailList[j] = contractServiceDetail
		}
	}

	//新增 业务合同 与 代付合同 的 关联关系
	if relationId > 0 {
		sql := ``
		if contractInfo.ContractBusinessType == "代付合同" {
			sql = `INSERT INTO contract_relation ( contract_id,payment_on_behalf_contract_id ) VALUES ( ` + fmt.Sprint(relationId) + `,` + fmt.Sprint(contractId) + ` )`
		} else {
			sql = `INSERT INTO contract_relation ( contract_id,payment_on_behalf_contract_id ) VALUES ( ` + fmt.Sprint(contractId) + `,` + fmt.Sprint(relationId) + ` )`
		}
		_, err = to.Raw(sql).Exec()
		if err != nil {
			return
		}
	}

	newContract = contractInfo
	return
}

type ContractList struct {
	ContractId           int       `description:"合同唯一id"`
	ContractCode         string    `description:"合同编号,长度32位"`
	SellerId             int       `description:"所属销售id"`
	SellerName           string    `description:"所属销售名称"`
	ProductId            int       `description:"产品id，1：ficc；2：权益"`
	ContractBusinessType string    `description:"合同业务类型，枚举值：'业务合同','代付合同'"`
	ContractType         string    `description:"合同类型，枚举值：'新签合同','续约合同','补充协议'"`
	Status               string    `description:"合同状态，枚举值：'待提交','待审批','已撤回','已审批','已驳回','已作废','已签回','已解约'"`
	StartDate            time.Time `json:"-" description:"合同开始日期"`
	EndDate              time.Time `json:"-" description:"合同结束日期"`
	OriginalPrice        float64   `description:"合同原金额，优惠前的金额"`
	Price                float64   `description:"实际金额，优惠后的金额"`
	PayRemark            string    `description:"付款方式说明,长度255位"`
	PayChannel           string    `description:"付款渠道,长度255位"`
	CompanyName          string    `description:"客户名称，甲方名称,长度32位"`
	CreditCode           string    `description:"社会统一信用代码,长度64位"`
	ProvinceId           int       `description:"省级id"`
	Province             string    `description:"省级名称,长度16位"`
	CityId               int       `description:"市级id"`
	City                 string    `description:"市级名称,长度32位"`
	Address              string    `description:"详细地址"`
	Fax                  string    `description:"传真,长度32位"`
	Phone                string    `description:"电话,长度32位"`
	Postcode             string    `description:"邮编,长度16位"`
	Remark               string    `json:"-" description:"补充内容,长度255位"`
	ApprovalRemark       string    `description:"审核备注"`
	ModifyContent        string    `description:"修改内容"`
	FileUrl              string    `description:"合同文件地址"`
	CheckBackFileUrl     string    `description:"签回合同文件地址"`
	RescindFileUrl       string    `description:"解约合同文件地址"`
	TemplateId           int       `description:"模板id"`
	SourceId             int       `description:"来源合同id，默认是0；如果是通过其他合同复制过来的，那么就是原合同的id"`
	IsDelete             int       `json:"-" description:"是否已经删除，0：未删除，1：已删除"`
	ModifyTime           time.Time `json:"-" description:"合同最近一次修改时间"`
	CreateTime           time.Time `json:"-" description:"合同添加时间"`
	ApproveTime          time.Time `description:"审批时间"`
	InvalidTime          time.Time `description:"作废时间"`
	CheckBackFileTime    time.Time `description:"合同签回时间"`
	RescindTime          time.Time `description:"解约时间"`

	StartDateStr         string `description:"合同起始时间"`
	EndDateStr           string `description:"合同结束时间"`
	ApproveTimeStr       string `description:"审批时间（字符串形式）"`
	InvalidTimeStr       string `description:"作废时间（字符串形式）"`
	CheckBackFileTimeStr string `description:"合同签回时间（字符串形式）"`
	RescindTimeStr       string `description:"解约时间（字符串形式）"`
	ModifyTimeStr        string `description:"最近一次更新时间（字符串形式）"`
	CreateTimeStr        string `description:"合同添加时间（字符串形式）"`
	Service              []*ContractServiceAndDetail
	OpButton             ContractOpButton `description:"按钮操作权限"`

	RelationContractList []*RelationContractList `description:"关联合同信息"`
}

// ContractOpButton 合同操作权限
type ContractOpButton struct {
	Cancel     bool `description:"是否具有撤回操作权限，true才允许撤回操作"`
	UpdateFile bool `description:"是否具有更新合同附件权限，true才允许更新合同附件操作"`
}

// GetContractListCount 获取合同列表数据数量
func GetContractListCount(condition, joinStr string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := `select count(a.contract_id) AS COUNT from contract a ` + joinStr
	sql += ` where a.is_delete = 0 `
	sql += condition
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// GetContractList 获取合同列表数据
func GetContractList(condition, joinStr string, pars []interface{}, startSize, pageSize int) (list []*ContractList, err error) {
	o := orm.NewOrm()
	sql := "select a.* from contract a " + joinStr
	sql += ` where a.is_delete = 0 `
	sql += condition
	sql += ` order by modify_time desc LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}

// ContractCompanyNameList 合同中客户名称数据结构体
type ContractCompanyNameList struct {
	CompanyName string `description:"客户名称"`
}

// GetContractCompanyNameList 获取合同中客户名称列表数据
func GetContractCompanyNameList(condition string, pars []interface{}) (list []*ContractCompanyNameList, err error) {
	o := orm.NewOrm()
	sql := `select a.company_name from contract a  where a.is_delete = 0 and price>paid_price`
	sql += condition
	sql += ` group by company_name order by modify_time desc`
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

// 修改合同
func EditContract(contractInfo *Contract, updateDetailCol []string, contractServiceAndDetailList []*ContractServiceAndDetail, relationId int) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	contractId := contractInfo.ContractId
	//合同数据入库
	_, err = to.Update(contractInfo, updateDetailCol...)
	if err != nil {
		return
	}

	//删除合同的原始服务信息
	sql := `delete from contract_service where contract_id = ?`
	_, err = to.Raw(sql, contractId).Exec()
	//删除合同的原始服务详情信息
	sql = `delete from contract_service_detail where contract_id = ?`
	_, err = to.Raw(sql, contractId).Exec()

	for i := 0; i < len(contractServiceAndDetailList); i++ {
		//合同服务数据入库
		tmpContractService := contractServiceAndDetailList[i]
		contractService := &ContractService{
			ContractId:        int(contractId),
			Title:             tmpContractService.Title,
			ProductId:         contractInfo.ProductId,
			ServiceTemplateId: tmpContractService.ServiceTemplateId,
			ChartPermissionId: tmpContractService.ChartPermissionId,
			Value:             tmpContractService.Value,
			TableValue:        tmpContractService.TableValue,
			HasDetail:         tmpContractService.HasDetail,
			CreateTime:        time.Now(),
		}
		contractServiceId, serviceErr := to.Insert(contractService)
		if serviceErr != nil {
			err = serviceErr
			return
		}
		contractService.ContractServiceId = int(contractServiceId)

		//合同服务详情入库
		for j := 0; j < len(tmpContractService.DetailList); j++ {
			contractServiceDetail := tmpContractService.DetailList[j]
			//合同服务编号
			contractServiceDetail.ContractServiceId = contractService.ContractServiceId
			contractServiceDetail.ContractId = contractService.ContractId
			contractServiceDetail.ServiceTemplateId = contractService.ServiceTemplateId

			//合同服务详情入库
			contractServiceDetailId, detailErr := to.Insert(contractServiceDetail)
			if detailErr != nil {
				err = detailErr
				return
			}
			contractServiceDetail.Id = int(contractServiceDetailId)
			tmpContractService.DetailList[j] = contractServiceDetail
		}
	}

	//新增 业务合同 与 代付合同 的 关联关系
	if relationId > 0 {
		sql = ``
		if contractInfo.ContractBusinessType == "代付合同" {
			//删除 业务合同 与 代付合同 的 关联关系
			sql = `delete from contract_relation where payment_on_behalf_contract_id = ?`
			_, err = to.Raw(sql, contractId).Exec()
			sql = `INSERT INTO contract_relation ( contract_id,payment_on_behalf_contract_id ) VALUES ( ` + fmt.Sprint(relationId) + `,` + fmt.Sprint(contractId) + ` )`
		} else {
			//删除 业务合同 与 代付合同 的 关联关系
			sql = `delete from contract_relation where contract_id = ?`
			_, err = to.Raw(sql, contractId).Exec()
			sql = `INSERT INTO contract_relation ( contract_id,payment_on_behalf_contract_id ) VALUES ( ` + fmt.Sprint(contractId) + `,` + fmt.Sprint(relationId) + ` )`
		}
		_, err = to.Raw(sql).Exec()
		if err != nil {
			return
		}
	}

	return
}

// AddContractPdf 添加生成后的合同地址
func AddContractPdf(contractId int, pdfUrl string) (err error) {
	o := orm.NewOrm()

	//删除合同的原始服务信息
	sql := `update contract set file_url=? where contract_id = ?`
	_, err = o.Raw(sql, pdfUrl, contractId).Exec()
	return
}

// DeleteContract 删除合同
func DeleteContract(contractInfo *Contract) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	contractInfo.IsDelete = 1
	contractInfo.ModifyTime = time.Now()
	//合同数据入库
	_, err = to.Update(contractInfo)
	if err != nil {
		return
	}
	return
}

// InvalidContract 作废合同
func InvalidContract(contractInfo *Contract) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	contractInfo.Status = "已作废"
	contractInfo.ModifyTime = time.Now()
	contractInfo.InvalidTime = time.Now()
	//合同数据入库
	_, err = to.Update(contractInfo, "Status", "ModifyTime", "InvalidTime")
	if err != nil {
		return
	}
	return
}

// GetCompanyContractCode 生成合同编号
func GetCompanyContractCode(productId int, contractBusinessType string) (companyCode string, err error) {
	var num int
	o := orm.NewOrm()
	today := utils.GetToday(utils.FormatDate)
	sql := `SELECT COUNT(1) AS num FROM contract where create_time>=?`
	err = o.Raw(sql, today).QueryRow(&num)
	if err != nil {
		return
	}
	companyType := ""
	switch productId {
	case 1:
		companyType = "FICC"
	case 2:
		companyType = "EQ"
	}

	perStr := `HZ`
	switch contractBusinessType {
	case "业务合同":
		perStr = `HZ`
	case "代付合同":
		perStr = `DC`
	}
	companyCode = perStr + companyType + time.Now().Format("20060102") + fmt.Sprintf("%03d", num)
	return
}

// GetSearchContractList 获取合同列表数据
func GetSearchContractList(productId, sellerId int, keyword, creditCode string) (list []*ContractList, err error) {
	o := orm.NewOrm()
	sql := `select a.* from contract a left join company_contract b on a.contract_code=b.contract_code
where a.status='已签回' AND a.contract_business_type="业务合同" AND is_delete = 0  AND a.product_id=? AND a.end_date>now()
AND (b.contract_code is null or b.status = "已审批") and seller_id=?`

	//if companyIdStr != "" {
	//	sql += ` AND b.company_id not in ( ` + companyIdStr + `) `
	//}
	if keyword != "" {
		sql += ` AND  a.company_name like "%` + keyword + `%" `
	}
	if creditCode != "" {
		sql += ` AND  a.credit_code = "` + creditCode + `" `
	}

	sql += ` order by a.end_date asc `
	_, err = o.Raw(sql, productId, sellerId).QueryRows(&list)
	return
}

// UpdateBusinessContractPaidPrice 更新合同已支付金额
func UpdateBusinessContractPaidPrice(contractId int, updatePriceStr string) (err error) {
	o := orm.NewOrm()

	//删除合同的原始服务信息
	sql := `update contract set paid_price=paid_price` + updatePriceStr + ` where contract_id = ?`
	_, err = o.Raw(sql, contractId).Exec()
	return
}

// GetSearchListBySeal 获取用印所需合同列表
func GetSearchListBySeal(condition string, pars []interface{}) (list []*ContractList, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM contract WHERE 1=1 AND is_delete = 0 AND status = "已审批" `
	sql += condition
	sql += ` ORDER BY modify_time DESC`
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

// GetContractListByContractIds 根据审批单IDs获取审批节点列表
func GetContractListByContractIds(contractIds string) (contractList []*ContractList, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM contract WHERE contract_id IN (` + contractIds + `)`
	_, err = o.Raw(sql).QueryRows(&contractList)
	return
}

// GetLatestContractListByProductId 获取最新的续约合同ids
func GetLatestContractListByProductId(productId int) (contractIds []string, err error) {
	o := orm.NewOrm()
	sql := `SELECT company_contract_id FROM company_contract WHERE product_id=? AND contract_type="续约合同" GROUP BY create_time DESC,company_id `
	_, err = o.Raw(sql, productId).QueryRows(&contractIds)
	return
}

// GetContractListByProductId 根据产品获取合同列表
func GetContractListByProductId(productId int) (list []*ContractList, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM contract WHERE is_delete = 0 AND product_id = ? ORDER BY create_time ASC`
	_, err = o.Raw(sql, productId).QueryRows(&list)
	return
}

// GetContractListByCompanyName 根据公司名称获取合同
func GetContractListByCompanyName(companyName string) (item *ContractList, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM contract WHERE is_delete = 0 AND company_name = ? ORDER BY create_time DESC`
	err = o.Raw(sql, companyName).QueryRow(&item)
	return
}

type RenewCompanyGroup struct {
	SellerId int `description:"所属销售id"`
	//AdminName string `description:"所属销售名称"`
	Num        int    `description:"汇总次数"`
	CompanyIds string `description:"客户id字符串"`
}

// GetExpiresCompanyGroupList 获取销售到期数据
func GetExpiresCompanyGroupList(startDate, endDate time.Time, productId int) (list []*RenewCompanyGroup, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	a.seller_id,
	count(
	DISTINCT ( a.company_id )) num,
	GROUP_CONCAT( DISTINCT a.company_id SEPARATOR ',' ) AS company_ids 
FROM
	company_product as a 
join company_contract as b on a.company_id=b.company_id and a.product_id=b.product_id
WHERE
	a.product_id=?
AND	b.product_id=?
AND
	b.end_date >= ?
	AND b.end_date < ? 
AND b.status = 1
GROUP BY
	a.seller_id
`
	_, err = o.Raw(sql, productId, productId, startDate, endDate).QueryRows(&list)

	return
}

// GetCountFormalCompany
// @Description: 获取某个时间点前的合同客户数量
// @author: Roc
// @datetime 2023-12-05 09:36:28
// @param endDate time.Time
// @param productId int
// @return total int
// @return err error
func GetCountFormalCompany(endDate time.Time, productId int) (total int, err error) {
	o := orm.NewOrm()
	sql := `select count(1) total from (
    SELECT
	a.seller_id
FROM
	company_product as a 
join company_contract as b on a.company_id=b.company_id and a.product_id=b.product_id
WHERE
	a.product_id=?
AND	b.product_id=?
	AND b.end_date < ? 
AND b.status = 1
GROUP BY
	a.company_id
) z
`
	err = o.Raw(sql, productId, productId, endDate).QueryRow(&total)

	return
}