hz_crm_api/models/statistic_report.go

package models

import (
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/models/company"
)

// 获取即将过期的客户数量（根据客户来展示）
func GetWillExpireCompanyListCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.*,b.region_type,c.seller_id,c.seller_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 = "SELECT COUNT(1) AS count FROM ( SELECT * from (" + sql + " order by a.end_date desc ) d GROUP BY d.company_id,d.product_id ) f ;"
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

func GetWillExpireCompanyListCountV2(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.company_id,a.product_id,a.seller_id,a.seller_name,b.region_type FROM company_product a 
		 JOIN company b ON a.company_id = b.company_id WHERE a.status = "正式"`

	if condition != "" {
		sql += condition
	}
	sql = "SELECT COUNT(1) AS count FROM ( SELECT * from (" + sql + " order by a.end_date desc ) d GROUP BY d.company_id ) f ;"
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// 待签约用户列表
type WillExpireCompanyList 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    []*company.CompanyReportPermissionAndName `description:"产品权限"`
}

// 获取即将过期的客户数量（根据合同来展示）
func GetWillExpireContactListCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.*,b.region_type,c.seller_id,c.seller_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 = "SELECT COUNT(1) AS count FROM (" + sql + " order by a.end_date desc ) d ;"
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}
func GetWillExpireContactListCountV2(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.company_id,a.product_id,a.seller_id,a.seller_name,b.region_type FROM company_product a 
		 JOIN company b ON a.company_id = b.company_id  WHERE a.status = "正式"`

	if condition != "" {
		sql += condition
	}
	sql = "SELECT COUNT(1) AS count FROM (" + sql + " GROUP BY a.company_id,product_id ) f ;"
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// 获取即将过期的客户列表（根据合同来展示）
func GetWillExpireCompanyList(condition string, pars []interface{}, startSize, pageSize int) (items []*WillExpireCompanyList, err error) {
	o := orm.NewOrm()
	//o.Using("rddp")
	//产品权限

	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 = sql + " order by a.end_date asc,a.company_id desc LIMIT ?,? "
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}
func GetWillExpireCompanyListV2(condition string, pars []interface{}, startSize, pageSize int) (items []*WillExpireCompanyList, err error) {
	o := orm.NewOrm()
	//o.Using("rddp")
	//产品权限

	sql := `SELECT a.company_id,a.product_id,a.seller_id,a.seller_name,a.product_name,a.start_date,a.end_date,b.region_type,b.company_name FROM company_product a 
		 JOIN company b ON a.company_id = b.company_id   WHERE a.status = "正式"`

	if condition != "" {
		sql += condition
	}
	sql = sql + " order by a.end_date asc,a.company_id desc LIMIT ?,? "
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// GetContractStatListCount 获取合同统计数据列表数量（根据客户来展示）
func GetContractStatListCount(condition string, pars []interface{}) (count, countContract int, money float64, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.company_contract_id,a.company_id,a.product_id,b.region_type,c.seller_id,c.seller_name,sum(a.money) money,count(1) count_contract 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 = "SELECT COUNT(1) AS count,sum(money) money,sum(count_contract) count_contract FROM (" + sql + "  group by a.company_id  order by a.end_date desc ) d "
	err = o.Raw(sql, pars).QueryRow(&count, &money, &countContract)
	return
}

// ContractStatList 合同数据报表结构体
type ContractStatList struct {
	ContractId        int     `description:"合同id"`
	CompanyContractId int     `description:"客户使用合同id"`
	CompanyId         int     `description:"企业客户id"`
	CompanyName       string  `description:"企业客户名称"`
	ProductId         int     `description:"产品id"`
	ProductName       string  `description:"产品名称"`
	Count             int     `description:"有效合同数"`
	SumMoney          float64 `description:"有效合同总金额"`
	Status            string  `description:"合同审批状态，0:待审批，1:已审批；默认：1"`
	RegionType        string  `description:"企业客户所属区域；可选范围：国内，海外"`
	SellerId          int     `description:"归属销售id"`
	SellerName        string  `description:"归属销售名称"`
	Source            string  `description:"合同来源，枚举值：上传附件、系统合同"`
	CreateTime        string  `description:"新增合同时间"`
}

// GetContractStatList 获取合同统计数据列表（根据客户来展示）
func GetContractStatList(condition string, pars []interface{}, startSize, pageSize int) (items []*ContractStatList, err error) {
	o := orm.NewOrm()
	//o.Using("rddp")
	//产品权限

	sql := `SELECT a.company_contract_id,a.company_id,a.product_id,a.product_name,a.end_date,b.region_type,c.seller_id,c.seller_name,b.company_name,count(1) count,sum(money) sum_money 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 += " GROUP BY company_id order by end_date asc,a.company_id desc  LIMIT ?,? "
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// GetContractStatListCountBySource 根据合同来源获取合同统计数据列表数量（根据合同来展示）
func GetContractStatListCountBySource(source, condition, condition2 string, pars1, pars2 []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql1 := `SELECT a.company_contract_id 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 and a.source="上传附件" `
	if condition != "" {
		sql1 += condition
	}

	sql2 := `SELECT a.contract_id,a.seller_id,a.seller_name,a.company_name,a.product_id FROM contract a join admin b on a.seller_id=b.admin_id WHERE a.is_delete = 0 and a.contract_business_type="业务合同" and a.status="已签回" `
	if condition2 != "" {
		sql2 += condition2
	}

	sql := ``
	pars := make([]interface{}, 0)
	switch source {
	case "上传附件":
		sql = sql1
		pars = pars1
	case "系统合同":
		sql = sql2
		pars = pars2
	default:
		sql = `select * from (` + sql1 + ` union (` + sql2 + `)  ` + `) f `
		pars = append(pars, pars1, pars2)
	}

	sql = `select count(1) count from (` + sql + `) g`
	//
	//sql := `SELECT a.company_contract_id 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 = "SELECT COUNT(1) AS count FROM (" + sql + "  order by a.end_date desc ) d "
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// GetContractStatListBySource 获取合同统计数据列表（根据客户来展示）
func GetContractStatListBySource(source, condition, condition2 string, pars1, pars2 []interface{}, startSize, pageSize int) (items []*ContractStatList, err error) {
	o := orm.NewOrm()
	//o.Using("rddp")
	//产品权限
	//sql := `SELECT a.company_contract_id,a.company_id,a.product_id,a.product_name,a.end_date,a.source,a.create_time,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 end_date asc,a.company_id desc  LIMIT ?,? "
	sql1 := `SELECT 0 contract_id,a.company_contract_id,a.company_id,a.product_id,a.product_name,a.end_date,a.source,a.create_time,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 and a.source="上传附件" `
	if condition != "" {
		sql1 += condition
	}

	sql2 := `SELECT a.contract_id,0 company_contract_id,0 company_id,a.product_id,"" product_name,a.end_date,"系统合同" source,a.check_back_file_time create_time,"国内" region_type,a.seller_id,a.seller_name,a.company_name  FROM contract a join admin b on a.seller_id=b.admin_id WHERE a.is_delete = 0 and a.contract_business_type="业务合同" and a.status="已签回" `
	if condition2 != "" {
		sql2 += condition2
	}

	sql := ``
	pars := make([]interface{}, 0)
	switch source {
	case "上传附件":
		sql = sql1
		pars = pars1
	case "系统合同":
		sql = sql2
		pars = pars2
	default:
		sql = `select * from (` + sql1 + ` union (` + sql2 + `)  ` + `) f `
		pars = append(pars, pars1, pars2)
	}
	//sql += " order by end_date asc,company_name desc  LIMIT ?,? "
	sql += " order by create_time desc,company_name desc  LIMIT ?,? "

	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// 企业客户的合同数据报表
type CompanyContractStatList struct {
	CompanyContractId int                         `description:"客户合同id"`
	ContractType      string                      `description:"合同类型：枚举值：'新签合同','续约合同','补充协议'"`
	ProductId         int                         `description:"产品id"`
	ProductName       string                      `description:"产品名称"`
	CompanyId         int                         `description:"客户id"`
	ContractCode      string                      `description:"合同编码"`
	StartDate         string                      `description:"合同开始时间"`
	EndDate           string                      `description:"合同结束时间"`
	Money             float64                     `description:"合同金额"`
	PayMethod         string                      `description:"支付方式"`
	PayChannel        string                      `description:"支付渠道"`
	PermissionList    []CompanyContractPermission `description:"合同中的权限列表"`
}

// 客户合同产品权限数据结构
type CompanyContractPermission struct {
	ClassifyName   string `description:"权限分类名称"`
	PermissionList []*company.CompanyContractPermissionName
}

// 企业客户的获取合同统计数据列表（根据合同来展示）
func GetCompanyContractStatList(condition string, pars []interface{}) (items []*CompanyContractStatList, err error) {
	o := orm.NewOrm()
	//o.Using("rddp")
	//产品权限

	sql := `SELECT * FROM company_contract a WHERE a.status = 1 `

	if condition != "" {
		sql += condition
	}
	sql += "order by end_date asc,company_id desc"
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 收入统计报表合计数据（根据合同来展示）
func GetIncomeListCount(condition string, pars []interface{}) (count int, money float64, err error) {
	o := orm.NewOrm()
	/*sql := `SELECT a.*,b.region_type,c.seller_id,c.seller_name FROM company_contract a
		LEFT JOIN company b ON a.company_id = b.company_id
		LEFT 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 = "SELECT COUNT(1) AS count FROM (" + sql + " order by a.end_date desc ) d GROUP BY d.company_id,d.product_id; "*/

	sql := `SELECT COUNT(1) AS count,SUM(money) money 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
	}
	err = o.Raw(sql, pars).QueryRow(&count, &money)
	return
}

// 收入统计报表列表数据结构
type IncomeList 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    []CompanyContractPermission `description:"客户的产品权限列表"`
}

// 客户合同产品权限数据结构
type CompanyReportPermission struct {
	ClassifyName   string `description:"权限分类名称"`
	PermissionList []*company.CompanyContractPermissionName
}

// 获取收入统计报表列表数据（根据合同来展示）
func GetIncomeList(condition string, pars []interface{}, startSize, pageSize int) (items []*IncomeList, err error) {
	o := orm.NewOrm()
	//o.Using("rddp")
	//产品权限

	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,a.company_id desc LIMIT ?,? "
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&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:"产品名称"`
	ProductStatus        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:"归属销售名称"`
	ShareSeller          string                             `description:"共享销售员"`
	ExpireDay            string                             `description:"剩余可用天数"`
	PermissionList       []*company.CompanyReportPermission `description:"产品权限"`
	Count                int                                `json:"-" description:"合同数"`
	RenewalReason        string                             `description:"未续约说明"`
	RenewalTodo          string                             `description:"未续约说明中的待办事项说明"`
	PackageDifference    string                             `description:"和上一份合同的区别"`
	AscribeContent       string                             `description:"归因标签说明"`
	IsShowNoRenewedNote  bool                               `description:"是否展示未续约备注按钮"`
	Content              string                             `description:"归因内容说明"`
	PermissionName       string                             `description:"权限名"`
	PermissionNameExport string                             `description:"权限名导出时使用"`
	PermissionNameStatus string                             `description:"权限状态"`
	CompanyProductStatus string                             `description:"客户状态"`
	//CompanyContractIdGroup string                             `description:"表company_contract合并的 company_contract_id"`
	IsUserMaker     int    `description:"近四周之内是否包含决策人互动过 ，0否，1是"`
	SellerNameInit  string `description:"权益初始化销售"`
	SellerIdInit    int    `description:"权益初始化销售ID"`
	ShareSellerInit string `description:"共享销售员"`
}

// GetIncrementalNewCompanyCount 获取增量客户报表列表统计数据（根据合同来展示）
func GetIncrementalNewCompanyCount(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 a.status = 1 `

	if condition != "" {
		sql += condition
	}
	//sql += " order by a.start_date desc "
	sql = `select count(1) count from (` + sql + ` group by company_id) f`
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// GetIncrementalNewCompanyProductCount 获取增量客户产品报表列表统计数据（根据合同来展示）
func GetIncrementalNewCompanyProductCount(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 a.status = 1 `

	if condition != "" {
		sql += condition
	}
	//sql += " order by a.start_date desc "
	sql = `select count(1) count from (` + sql + ` group by company_id,product_id) f`
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// GetIncrementalNewCompanyList 获取增量客户报表列表数据（根据合同来展示）
func GetIncrementalNewCompanyList(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 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,product_id  order by start_date desc,company_id desc limit ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

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

	sql1 := `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 and a.product_id=1 `
	if condition != "" {
		sql1 += condition
	}
	sql1 += ` and a.company_id not in (select company_id from company_contract where contract_type="新签合同" and status=1 and product_id=1 and end_date>?) `

	sql2 := `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 and a.product_id=2 `
	if condition != "" {
		sql2 += condition
	}
	sql2 += ` and a.company_id not in (select company_id from company_contract where contract_type="新签合同" and status=1 and product_id=2 and end_date>?) `

	sql := sql1 + ` union (` + sql2 + `)  `
	sql = `select * from (` + sql + `) f group by company_id`
	//sql += " order by a.start_date desc "
	sql = `select count(1) count from (` + sql + `) g`

	pars = append(pars, pars...)
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

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

	sql1 := `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  and a.product_id=1 `
	if condition != "" {
		sql1 += condition
	}
	sql1 += ` and a.company_id not in (select company_id from company_contract where contract_type="新签合同" and status=1 and product_id=1 and end_date > ?) `

	sql2 := `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 and a.product_id=2 `
	if condition != "" {
		sql2 += condition
	}
	sql2 += ` and a.company_id not in (select company_id from company_contract where contract_type="新签合同" and status=1 and product_id=2 and end_date > ?) `

	sql := `select * from (` + sql1 + ` union (` + sql2 + `)  ` + `) f group by company_id,product_id`

	//sql := sql1 + ` union (` + sql2 + `) `

	//sql += " order by a.start_date desc "
	sql = `select count(1) count from (` + sql + `) g`

	pars = append(pars, pars...)
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

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

	sql1 := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name,c.renewal_reason 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 and a.product_id=1 `
	if condition != "" {
		sql1 += condition
	}
	sql1 += ` and a.company_id not in (select company_id from company_contract where contract_type="新签合同" and status=1 and product_id=1 and end_date > ?) `

	sql2 := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name,c.renewal_reason 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 and a.product_id=2 `
	if condition != "" {
		sql2 += condition
	}
	sql2 += ` and a.company_id not in (select company_id from company_contract where contract_type="新签合同" and status=1 and product_id=2 and end_date > ?) `

	//sql := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name,c.renewal_reason 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 := `select * from (` + sql1 + ` union (` + sql2 + `)  ` + `) f order by start_date desc`

	sql += "  "
	sql = `select *,count(*) count from (` + sql + `) b group by company_id,product_id  order by start_date desc,company_id desc limit ?,?`

	pars = append(pars, pars...)
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// 获取今日存量客户报表列表统计数据（根据合同来展示）
func GetTodayStackCompanyProductCount(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 a.status = 1 `

	if condition != "" {
		sql += condition
	}
	//sql += " order by a.start_date desc "
	sql = `select count(1) count from (` + sql + ` ) f`
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// GetTodayStackCompanyList 获取当天存量客户报表列表数据（根据合同来展示）
func GetTodayStackCompanyList(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 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 * from (` + sql + `) b  order by start_date desc,company_id desc limit ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// GetTodayStackCompanyProductCountV2 获取今日存量客户报表列表统计数据（续约客户数，根据合同来展示）
func GetTodayStackCompanyProductCountV2(condition string, pars []interface{}) (total int, err error) {
	o := orm.NewOrm()

	sql1 := `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 and a.product_id=1 `
	if condition != "" {
		sql1 += condition
	}
	sql1 += ` and a.company_id not in (select company_id from company_contract where contract_type="新签合同" and status=1 and product_id=1 and end_date>?) `

	sql2 := `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 and a.product_id=2 `
	if condition != "" {
		sql2 += condition
	}
	sql2 += ` and a.company_id not in (select company_id from company_contract where contract_type="新签合同" and status=1 and product_id=2 and end_date>?) `

	sql := sql1 + ` union (` + sql2 + `)  `

	sql = `select * from (` + sql + `) f group by company_id`
	//sql += " order by a.start_date desc "
	sql = `select count(1) count from (` + sql + `) g`

	pars = append(pars, pars...)
	err = o.Raw(sql, pars).QueryRow(&total)

	return
}

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

	sql1 := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name,c.renewal_reason 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 and a.product_id=1 `
	if condition != "" {
		sql1 += condition
	}
	sql1 += ` and a.company_id not in (select company_id from company_contract where contract_type="新签合同" and status=1 and product_id=1 and end_date > ?) `

	sql2 := `SELECT a.*,b.region_type,c.seller_id,c.seller_name,b.company_name,c.renewal_reason 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 and a.product_id=2 `
	if condition != "" {
		sql2 += condition
	}
	sql2 += ` and a.company_id not in (select company_id from company_contract where contract_type="新签合同" and status=1 and product_id=2 and end_date > ?) `

	sql := `select * from (` + sql1 + ` union (` + sql2 + `)  ` + `) f order by start_date desc`
	sql = `select * from (` + sql + `) b order by start_date desc,company_id desc limit ?,?`

	pars = append(pars, pars...)
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// 获取其他增量客户报表列表数据（根据合同来展示）
func GetOtherIncrementalNewCompanyList(companyIds, companyContractIds, condition string, pars []interface{}) (items []*IncrementalList, 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
AND a.company_id in (` + companyIds + `) AND company_contract_id not in (` + companyContractIds + `) `

	if condition != "" {
		sql += condition
	}
	sql += " order by a.start_date desc "
	sql = `select *,count(*) count from (` + sql + `) b group by company_id `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 获取存量客户未续约报表列表统计数据（根据合同来展示）
func GetIncrementalNotRenewalCompanyTotalCount(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 a.status = 1 `

	if condition != "" {
		sql += condition
	}
	//sql += " order by a.start_date desc "
	sql = `select count(1) count from (` + sql + ` group by company_id) f`
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// 获取存量客户未续约报表列表数据（根据合同来展示）
func GetIncrementalNotRenewalCompanyList(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 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 ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// 获取存量客户未续约报表列表数据（根据合同来展示）
func GetOtherIncrementalNotRenewalCompanyList(companyIds, companyContractIds, condition string, pars []interface{}) (items []*IncrementalList, 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
AND a.company_id in (` + companyIds + `) AND company_contract_id not in (` + companyContractIds + `) `

	if condition != "" {
		sql += condition
	}
	sql += " order by a.start_date desc "
	sql = `select *,count(*) count from (` + sql + `) b group by company_id `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 获取试用客户报表列表统计数据（根据新增客户时间来展示）
func GetIncrementalCompanyCountByOperationRecord(condition string, pars []interface{}) (total int, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.id,a.company_id,b.company_name,c.seller_id,c.seller_name,
a.product_id,a.product_name,b.region_type,c.renewal_reason FROM company_operation_record a
		RIGHT JOIN company b ON a.company_id = b.company_id 
		 JOIN company_product c ON b.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(1) count from (` + sql + ` group by company_id ) f `
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// 获取试用客户报表列表统计数据（根据新增客户和对应的产品时间来展示）
func GetIncrementalCompanyProductCountByOperationRecord(condition string, pars []interface{}) (total int, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.id,a.company_id,b.company_name,c.seller_id,c.seller_name,
a.product_id,a.product_name,b.region_type,c.renewal_reason FROM company_operation_record a
		RIGHT JOIN company b ON a.company_id = b.company_id 
		 JOIN company_product c ON b.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(1) count from (` + sql + ` group by company_id,product_id ) f `
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// 获取试用客户报表列表数据（根据新增客户时间来展示）（2022年02月09日14:47:45废弃）
func GetIncrementalCompanyListByOperationRecordOld(condition string, pars []interface{}, startSize, pageSize int) (items []*IncrementalList, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.id,a.company_id,b.company_name,c.seller_id,c.seller_name,
a.product_id,a.product_name,a.create_time,b.region_type,c.renewal_reason,c.status FROM company_operation_record a
		RIGHT JOIN company b ON a.company_id = b.company_id 
		 JOIN company_product c ON b.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 * from  (` + sql + ` order by create_time asc) f group by company_id,product_id order by create_time desc,company_id desc limit ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// GetIncrementalCompanyListByOperationRecord 获取试用客户报表列表数据（根据新增客户时间来展示）
func GetIncrementalCompanyListByOperationRecord(condition string, pars []interface{}, startSize, pageSize int) (items []*IncrementalList, err error) {
	o := orm.NewOrm()
	//	sql := `SELECT a.id,a.company_id,b.company_name,c.seller_id,c.seller_name,
	//a.product_id,a.product_name,a.create_time,b.region_type,c.renewal_reason,c.status FROM company_operation_record a
	//		RIGHT JOIN company b ON a.company_id = b.company_id
	//		 JOIN company_product c ON b.company_id = c.company_id and a.product_id=c.product_id WHERE 1 = 1 `

	//查询出最大id
	sql1 := `SELECT max(id) id FROM company_operation_record a
		RIGHT JOIN company b ON a.company_id = b.company_id
		JOIN company_product c ON b.company_id = c.company_id 
		AND a.product_id = c.product_id 
	WHERE 1 = 1 `
	if condition != "" {
		sql1 += condition
	}
	sql1 += ` GROUP BY a.company_id, a.product_id `

	//查询真正的数据
	sql := `SELECT a.id, a.company_id, a.sys_real_name as  seller_name_init , b.company_name, c.seller_id, c.seller_name, a.product_id, a.product_name, a.create_time, b.region_type, c.renewal_reason, c.renewal_todo, c.status FROM company_operation_record a
		RIGHT JOIN company b ON a.company_id = b.company_id
		JOIN company_product c ON b.company_id = c.company_id 
		AND a.product_id = c.product_id
where a.id in (` + sql1 + `) order by  create_time DESC,company_id DESC limit ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// 根据操作记录获取其他增量客户报表列表数据（根据新增客户时间来展示）
func GetOtherIncrementalCompanyListByOperationRecord(companyIds, condition string, pars []interface{}) (items []*IncrementalList, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.id,a.company_id,b.company_name,c.seller_id,c.seller_name,
a.product_id,a.product_name,a.create_time,b.region_type FROM company_operation_record a
		RIGHT JOIN company b ON a.company_id = b.company_id 
		 JOIN company_product c ON b.company_id = c.company_id and a.product_id=c.product_id WHERE 1 = 1 AND a.company_id in (` + companyIds + `) `

	if condition != "" {
		sql += condition
	}
	//sql += " order by a.start_date desc "
	sql = `select * from  (` + sql + ` order by create_time asc) f group by company_id`
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 获取当前客户报表列表统计数据（正式/试用）
func GetCurrCompanyCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT b.company_id from company a 
 join company_product b on a.company_id=b.company_id WHERE 1 = 1 `

	if condition != "" {
		sql += condition
	}

	sql = `SELECT count(*) count from (` + sql + ` group by company_id) d`
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// GetMoreRenewalReason 获取更多未续约说明列表
func GetMoreRenewalReason(CompanyId, ProductId string) (items []*company.CompanyRenewalReason, err error) {
	o := orm.NewOrm()
	sql := "SELECT * FROM company_renewal_reason WHERE company_id=? AND product_id=? ORDER BY create_time DESC"
	_, err = o.Raw(sql, CompanyId, ProductId).QueryRows(&items)
	return
}

// GetMoreRenewalReason 获取更多未续约说明列表15.9.1
func GetMoreRenewalReasoninit() (items []*company.CompanyRenewalReason, err error) {
	o := orm.NewOrm()
	sql := "SELECT * FROM company_renewal_reason WHERE  product_id=2 ORDER BY create_time DESC"
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetLastContractMoney 获取上一份权益合同的金额
func GetLastContractMoney(CompanyIds string) (items []*IncrementalList, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_contract WHERE company_id IN (` + CompanyIds + `) AND product_id=2 AND status=1 ORDER BY create_time DESC `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

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

	sql := `SELECT a.*,b.region_type,c.seller_id,a.seller_name_init as seller_name ,a.share_seller_init as share_seller ,b.company_name,c.renewal_reason 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 += ` group by a.company_contract_id  order by a.start_date desc,a.company_id desc limit ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// GetIncrementalNewCompanyList 获取到期合同列表数据（根据合同来展示）
func GetIncrementalCompanyMergeListEnd(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,c.status AS product_status 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 += ` group by a.company_contract_id  order by a.end_date desc,a.company_id desc limit ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

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

	//查询真正的数据
	sql := `SELECT a.company_contract_id,a.contract_type ,a.company_product_id ,a.contract_code ,a.pay_method ,a.pay_channel ,a.package_difference ,a.company_id, a.start_date, a.end_date, a.money, b.company_name, c.seller_id, c.seller_name, a.product_id, a.product_name, a.create_time, b.region_type, c.renewal_reason, c.renewal_todo, c.status FROM company_contract a
		RIGHT JOIN company b ON a.company_id = b.company_id
		JOIN company_product c ON b.company_id = c.company_id 
		AND a.product_id = c.product_id  where 1=1  `
	//where a.id in (` + sql1 + `) order by  create_time DESC,company_id DESC limit ?,?`
	//	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)

	if condition != "" {
		sql += condition
	}
	sql += ` group by a.company_contract_id  order by end_date desc,company_id desc limit ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// GetIncrementalCompanyListByOperationRecordMerge 未续约合同
func GetIncrementalCompanyListByOperationRecordMerge879() (items []*IncrementalList, err error) {
	o := orm.NewOrm()
	//查询真正的数据
	sql := `SELECT
	a.company_contract_id,
	a.contract_type,
	a.company_product_id,
	a.contract_code,
	a.pay_method,
	a.pay_channel,
	a.package_difference,
	a.company_id,
	a.start_date,
	a.end_date,
	a.money,
	b.company_name,
	c.seller_id,
	c.seller_name,
	a.product_id,
	a.product_name,
	a.create_time,
	b.region_type,
	c.renewal_reason,
	c.renewal_todo,
	c.STATUS 
FROM
	company_contract a
	RIGHT JOIN company b ON a.company_id = b.company_id
	JOIN company_product c ON b.company_id = c.company_id 
	AND a.product_id = c.product_id 
WHERE
	1 = 1 
	AND c.product_id = 2
	AND a.STATUS = 1 
	AND a.end_date >= '2017-09-05'
	AND a.end_date <= '2022-12-31'
	AND c.STATUS NOT IN ( "永续", "正式", "关闭" ) 
	AND a.company_contract_id NOT IN (SELECT company_contract_id FROM company_contract_no_renewed_ascribe ) 
GROUP BY
	a.company_contract_id 
ORDER BY
	end_date DESC,
	company_id DESC  limit	 10
`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetIncrementalNewCompanyList 获取增量客户报表列表数据（根据合同来展示）
func GetIncrementalCompanyPermissionList(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, c.status as  company_product_status, d.permission_name as permission_name_export 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 
		 JOIN company_contract_permission d ON d.company_contract_id = a.company_contract_id
		 and a.product_id=c.product_id   WHERE 1 = 1 `

	if condition != "" {
		sql += condition
	}
	sql += ` group by d.company_contract_id,d.permission_name   order by start_date desc,company_id desc limit ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

type CompanyContractPermissionNameGroupCountResp struct {
	Total          int    `description:"数量"`
	PermissionName string `description:"权限名"`
}

// GetCompanyContractPermissionNameGroupCount 获取增量客户产品报表列表统计数据（根据合同来展示）
func GetCompanyContractPermissionNameGroupCount(condition string, pars []interface{}) (items []*CompanyContractPermissionNameGroupCountResp, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			COUNT( DISTINCT d.permission_name, d.company_contract_id ) total,
			d.permission_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 `
	//AND d.permission_name IN ( '医药', '消费', '科技', '智造', '策略','研选订阅','研选扣点包' ) `

	if condition != "" {
		sql += condition
	}
	sql += `GROUP BY d.permission_name `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}