hz_crm_api/models/cygx/company_contract.go

package cygx

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

// 增量客户统计报表列表数据结构
type CompanyContractResp struct {
	CompanyContractId int     `description:"合同id"`
	ContractType      string  `description:"合同类型"`
	CompanyId         int     `description:"企业客户id"`
	CompanyName       string  `description:"企业客户名称"`
	ContractCode      string  `description:"合同编码"`
	StartDate         string  `description:"合同开始日期"`
	EndDate           string  `description:"合同结束日期"`
	Money             float64 `description:"合同金额"`
	CreateTime        string  `description:"合同创建时间"`
	ModifyTime        string  `description:"合同修改时间"`
	SellerId          int     `description:"归属销售id"`
	SellerName        string  `description:"归属销售名称"`
	IsAllocation      int     `description:"是否派点: 0-未派点; 1-已派点"`
	Source            string  `description:"合同来源，枚举值：上传附件、系统合同，默认上传附件"`
	FormalType        string  `description:"转正类型，枚举值: 标准、非标"`
	IsGray            bool    `description:"是否置灰"`
	ContractId        int     `description:"合同唯一id"`
	MoneyPoint        float64 `description:"派点金额"`
	PermissionName    string  `description:"权限名"`
}

// 增量客户统计报表返回类
type CompanyContractListResp struct {
	Paging *paging.PagingItem `description:"分页数据"`
	List   []*CompanyContractResp
}

// 获取存量客户未续约报表列表统计数据（根据合同来展示）
func GetCompanyContractCountJoinCompany(condition string, pars []interface{}) (total int, err error) {
	o := orm.NewOrm()
	sql := ` SELECT
	count(*) AS count 
FROM
	(
	SELECT
		COUNT(*) 
	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
		a.STATUS = 1 
		` + condition + `
	GROUP BY
	a.company_contract_id 
	) b`
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// 获取存量客户未续约报表列表数据（根据合同来展示）
func GetCompanyContractListJoinCompany(condition string, pars []interface{}, startSize, pageSize int) (items []*CompanyContractResp, 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 a.status = 1 `
	//
	//if condition != "" {
	//	sql += condition
	//}
	//sql += " order by a.start_date desc "
	//sql = `select *,count(*) count from (` + sql + `) b group by company_id  order by end_date asc,company_id desc limit ?,?`

	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
			a.STATUS = 1`
	sql += condition
	sql += `GROUP BY a.company_contract_id ORDER BY a.start_date DESC   , a.company_contract_id DESC  	LIMIT  ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}