package models

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

//存量客户数据表
type StackCompanyStatistic struct {
	StatisticId  int       `orm:"column(statistic_id);pk"`
	Type         string    `description:"数据类型，取值范围：新签客户，续约客户，未续约客户"`
	CompanyId    int       `description:"企业客户id"`
	CompanyName  string    `description:"企业客户名称"`
	ProductId    int       `description:"客户产品id"`
	ProductName  string    `description:"客户产品名称"`
	ContractNum  int       `description:"第几份合同，默认是：1"`
	ContractId   int       `description:"合同id，默认是：0"`
	SellerId     int       `description:"所属销售id"`
	SellerName   string    `description:"所属销售名称"`
	GroupId      int       `description:"所属销售分组id"`
	DepartmentId int       `description:"所属销售部门id"`
	Date         string    `description:"记录日期"`
	StartDate    string    `description:"服务起始时间"`
	EndDate      string    `description:"服务截止时间"`
	RegionType   string    `description:"所属区域，国内，海外"`
	CreateTime   time.Time `description:"记录添加时间"`
}

//添加存量客户记录
func AddStackCompanyStatistic(item *StackCompanyStatistic) (err error) {
	o := orm.NewOrm()
	_, err = o.Insert(item)
	return
}

//获取某天的存量客户统计数据数量
func GetStackCompanyCount(dayStr string) (count int, err error) {
	o := orm.NewOrm()
	//产品权限
	sql := `SELECT count(1) count FROM stack_company_statistic WHERE date = ?`

	err = o.Raw(sql, dayStr).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"`
	GroupId           int     `description:"归属销售分组id"`
	DepartmentId      int     `description:"所属销售部门id"`
	SellerName        string  `description:"归属销售名称"`
	ProductStatus     string  `description:"产品状态"`
	Count             int     `description:"合同数量"`
}

//新签客户数（存量客户）
func GetStackCompanyListV1() (total int64, items []*WillExpireCompanyList, err error) {
	o := orm.NewOrm()
	//产品权限
	sql := `SELECT *,count(1) count FROM (
SELECT a.*,b.region_type,c.seller_id,c.seller_name,c.company_name,c.group_id,c.department_id,c.status as product_status 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
order by a.end_date desc ) d 
GROUP BY company_id,product_id `

	total, err = o.Raw(sql).QueryRows(&items)
	return
}

//增量客户统计报表列表数据结构
type IncrementalList struct {
	CompanyOperationRecordId int     `description:"操作记录id"`
	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"`
	GroupId                  int     `description:"归属分组id"`
	DepartmentId             int     `description:"归属部门id"`
	SellerName               string  `description:"归属销售名称"`
	ExpireDay                string  `description:"剩余可用天数"`
}

//未续约客户数（存量客户）
func GetNotRenewalCompanyTotalV1(dateTime string) (total int64, items []*IncrementalList, err error) {
	o := orm.NewOrm()
	//产品权限
	sql := `SELECT *  FROM
	( SELECT a.id,a.company_id,a.company_name,c.seller_id,c.seller_name,c.group_id,c.department_id,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 c.STATUS NOT IN ( "永续", "正式" )  AND a.create_time <= ? 
		AND a.operation = 'try_out' 
	ORDER BY create_time ASC 
	) f 
GROUP BY company_id, product_id 
ORDER BY create_time DESC , company_id DESC  `

	total, err = o.Raw(sql, dateTime).QueryRows(&items)
	return
}

// GetIncrementalCompanyListByOperationRecord 获取未续约客户报表列表数据（根据新增客户时间来展示）
func GetIncrementalCompanyListByOperationRecord(condition string, pars []interface{}) (items []*IncrementalList, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.id company_operation_record_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 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 = `select * from  (` + sql + ` order by create_time asc) f group by company_id,product_id order by create_time desc,company_id desc `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// GetTodayStackCompanyList 获取当天存量客户报表列表数据（新签客户，根据合同来展示）
func GetTodayStackCompanyList(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,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`
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// GetTodayStackCompanyListV2 获取当天存量客户报表列表数据（续约客户，根据合同来展示）
func GetTodayStackCompanyListV2(condition string, pars []interface{}) (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`

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