package cygx

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

type CygxTagHistory struct {
	Id               int `orm:"column(id);pk"`
	TagId            int
	UserId           int
	CreateTime       time.Time
	Mobile           string    `description:"手机号"`
	Email            string    `description:"邮箱"`
	CompanyId        int       `description:"公司id"`
	CompanyName      string    `description:"公司名称"`
	ModifyTime       time.Time `description:"修改时间"`
	RealName         string    `description:"用户实际名称"`
	SellerName       string    `description:"所属销售"`
	RegisterPlatform int       `description:"来源 1小程序，2：网页"`
}

// 列表
func GetCygxTagHistoryList(condition string, pars []interface{}) (items []*CygxTagHistory, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT * FROM cygx_tag_history as art WHERE 1= 1 `
	if condition != "" {
		sql += condition
	}
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

//// 获取数量
//func GetCygxTagHistoryCount(condition string, pars []interface{}) (count int, err error) {
//	sqlCount := ` SELECT COUNT(1) AS count  FROM cygx_tag_history as art WHERE 1= 1  `
//	if condition != "" {
//		sqlCount += condition
//	}
//	o := orm.NewOrmUsingDB("hz_cygx")
//	err = o.Raw(sqlCount, pars).QueryRow(&count)
//	return
//}

// 获取数量
func GetCygxTagHistoryCountUv(tagId int) (count int, err error) {
	sqlCount := ` SELECT
			count(*) AS uv 
		FROM
			( SELECT count(*) FROM cygx_tag_history WHERE 1 = 1 AND tag_id = ? GROUP BY user_id ) b `

	o := orm.NewOrmUsingDB("hz_cygx")
	err = o.Raw(sqlCount, tagId).QueryRow(&count)
	return
}

// 获取数量
func GetCygxTagHistoryCountPv(tagId int) (count int, err error) {
	sqlCount := ` SELECT COUNT(1) AS count  FROM cygx_tag_history as art WHERE 1= 1  AND tag_id = ? `
	o := orm.NewOrmUsingDB("hz_cygx")
	err = o.Raw(sqlCount, tagId).QueryRow(&count)
	return
}

// 获取数量
func GetCygxTagHistoryCountByUserId(userId int) (count int, err error) {
	sqlCount := ` SELECT COUNT(1) AS count  FROM cygx_tag_history as a 
	INNER JOIN cygx_tag AS b ON a.tag_id = b.tag_id 
	WHERE a.user_id=? ORDER BY a.create_time  `
	o := orm.NewOrmUsingDB("hz_cygx")
	err = o.Raw(sqlCount, userId).QueryRow(&count)
	return
}

// 获取数量
func GetCygxTagHistoryByUserId(userId, startSize, pageSize int) (list []*UserInteraction, err error) {
	sqlCount := ` SELECT b.*,a.create_time FROM cygx_tag_history as a INNER JOIN cygx_tag AS b ON a.tag_id = b.tag_id 
WHERE a.user_id=? ORDER BY a.create_time DESC LIMIT ?,? `
	o := orm.NewOrmUsingDB("hz_cygx")
	_, err = o.Raw(sqlCount, userId, startSize, pageSize).QueryRows(&list)
	return
}

// 获取数量
func GetCygxTagHistoryCountByCompanyId(companyId int, condition string) (count int, err error) {
	sqlCount := ` SELECT COUNT(1) AS count  FROM (SELECT b.* FROM cygx_tag_history as r 
	INNER JOIN cygx_tag AS b ON r.tag_id = b.tag_id  `
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` WHERE r.company_id=? ) AS t `
	o := orm.NewOrmUsingDB("hz_cygx")
	err = o.Raw(sqlCount, companyId).QueryRow(&count)
	return
}

// 获取数量
func GetCygxTagHistoryCountByCompanyIdWeekly(companyId int, condition string) (count int, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	databaseName := utils.GetWeeklyDatabase()
	sqlCount := ` SELECT COUNT(1) AS count  FROM (SELECT b.* FROM cygx_tag_history as r 
	INNER JOIN cygx_tag AS b ON r.tag_id = b.tag_id
	INNER JOIN ` + databaseName + `.user_seller_relation AS us ON us.user_id = r.user_id   AND us.product_id = 2 
`
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` WHERE r.company_id=? ) AS t `

	err = o.Raw(sqlCount, companyId).QueryRow(&count)
	return
}

// 获取数量
func GetCygxTagHistoryByCompanyId(companyId, startSize, pageSize int, condition string) (list []*UserInteraction, err error) {
	sqlCount := ` SELECT r.real_name,r.mobile, r.create_time AS create_time,
b.tag_id,b.tag_name,b.article_types,b.activity_types,b.industries,b.subject_names   
FROM cygx_tag_history as r 
	INNER JOIN cygx_tag AS b ON r.tag_id = b.tag_id `
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` 
	WHERE r.company_id=? ORDER BY  create_time DESC LIMIT ?,? `
	o := orm.NewOrmUsingDB("hz_cygx")
	_, err = o.Raw(sqlCount, companyId, startSize, pageSize).QueryRows(&list)
	return
}

// 获取数量
func GetCygxTagHistoryByCompanyIdWeekly(companyId, startSize, pageSize int, condition string) (list []*UserInteraction, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	databaseName := utils.GetWeeklyDatabase()
	sqlCount := ` SELECT r.real_name,a.mobile, r.create_time AS create_time,
			b.tag_id,b.tag_name,b.article_types,b.activity_types,b.industries,b.subject_names   
			FROM cygx_tag_history as r
				INNER JOIN cygx_tag AS b ON r.tag_id = b.tag_id 
			INNER JOIN ` + databaseName + `.user_seller_relation AS us ON us.user_id = r.user_id   AND us.product_id = 2 `
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` 
	WHERE r.company_id=? ORDER BY  create_time DESC LIMIT ?,? `

	_, err = o.Raw(sqlCount, companyId, startSize, pageSize).QueryRows(&list)
	return
}

// 获取数量
func GetCygxTagHistoryCountByCompanyIds(companyIds, condition string) (count int, err error) {
	sqlCount := ` SELECT a.real_name,a.mobile,b.*,ci.tag_num,ui.tag_num AS ui_tag_num,a.company_name  FROM cygx_tag_history as a  
	INNER JOIN cygx_tag AS b ON a.tag_id = b.tag_id
	LEFT JOIN cygx_company_interaction_num AS ci ON ci.company_id = a.company_id 
	LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = a.user_id WHERE 1=1 `
	if companyIds != "" {
		sqlCount += `AND a.company_id IN ( ` + companyIds + `) `
	}
	if condition != "" {
		sqlCount += condition
	}
	sql := `SELECT COUNT(1) AS count  FROM (` + sqlCount + `)  AS t`
	o := orm.NewOrmUsingDB("hz_cygx")
	err = o.Raw(sql).QueryRow(&count)
	return
}

// 获取数量
func GetCygxTagHistoryByCompanyIds(companyIds, condition string, startSize, pageSize int) (list []*UserInteraction, err error) {
	sqlCount := ` SELECT a.real_name,a.mobile,ci.tag_num,ui.tag_num AS ui_tag_num,a.company_name, a.create_time AS create_time,
b.tag_id,b.tag_name,b.article_types,b.activity_types,b.industries,b.subject_names
FROM cygx_tag_history as a 
INNER JOIN cygx_tag AS b ON a.tag_id = b.tag_id
LEFT JOIN cygx_company_interaction_num AS ci ON ci.company_id = a.company_id 
LEFT JOIN cygx_user_interaction_num AS ui ON ui.user_id = a.user_id WHERE 1=1 `
	if companyIds != "" {
		sqlCount += `AND a.company_id IN ( ` + companyIds + `) `
	}
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += `  ORDER BY  create_time DESC LIMIT ?,? `
	o := orm.NewOrmUsingDB("hz_cygx")
	_, err = o.Raw(sqlCount, startSize, pageSize).QueryRows(&list)
	return
}

// 获取数量
func GetCygxTagHistoryCountByCompanyIdCondition(condition string) (count int, err error) {
	sqlCount := ` SELECT COUNT(1) AS count  FROM cygx_tag_history as a  
	INNER JOIN cygx_tag AS b ON a.tag_id = b.tag_id  WHERE 1=1`
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += `GROUP BY a.user_id,a.tag_id   `
	sql := `SELECT COUNT(1) AS count  FROM (` + sqlCount + `)  AS t`
	o := orm.NewOrm()
	err = o.Raw(sql).QueryRow(&count)
	return
}

// 获取列表
func GetCygxTagHistoryListBycondition(condition string, startSize, pageSize int) (list []*UserInteraction, err error) {
	sql := ` SELECT r.real_name,r.mobile,r.company_name, r.create_time AS create_time,
				b.tag_id,b.tag_name,b.article_types,b.activity_types,b.industries,b.subject_names
				FROM cygx_tag_history as r 
				INNER JOIN cygx_tag AS b ON r.tag_id = b.tag_id
				WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += `  ORDER BY  create_time DESC LIMIT ?,? `
	o := orm.NewOrmUsingDB("hz_cygx")
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&list)
	return
}

// 获取列表
func GetCygxTagHistoryListByconditionWeekly(condition string, startSize, pageSize int) (list []*UserInteraction, err error) {
	databaseName := utils.GetWeeklyDatabase()
	sql := ` SELECT r.real_name,r.mobile,r.company_name, r.create_time AS create_time,	r.company_name,r.company_id,
				b.tag_id,b.tag_name,b.article_types,b.activity_types,b.industries,b.subject_names
				FROM cygx_tag_history as r 
				INNER JOIN cygx_tag AS b ON r.tag_id = b.tag_id
				INNER JOIN %s.user_seller_relation AS us ON us.user_id = r.user_id   AND us.product_id = 2 
				WHERE 1=1 `
	sql = fmt.Sprintf(sql, databaseName)
	if condition != "" {
		sql += condition
	}
	sql += `  ORDER BY  create_time DESC LIMIT ?,? `
	o := orm.NewOrmUsingDB("hz_cygx")
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&list)
	return
}

// 获取数量
func GetCygxTagHistoryCount(condition string) (count int, err error) {
	sql := ` SELECT COUNT(1) AS count  FROM cygx_tag_history as r 
			INNER JOIN cygx_tag AS b ON r.tag_id = b.tag_id  WHERE 1 = 1 `
	if condition != "" {
		sql += condition
	}
	o := orm.NewOrmUsingDB("hz_cygx")
	err = o.Raw(sql).QueryRow(&count)
	return
}

// 获取数量
func GetCygxTagHistoryCountWeekly(condition string) (count int, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	databaseName := utils.GetWeeklyDatabase()
	sql := ` SELECT COUNT(1) AS count  FROM cygx_tag_history as r 
				INNER JOIN cygx_tag AS b ON r.tag_id = b.tag_id
				INNER JOIN %s.user_seller_relation AS us ON us.user_id = r.user_id   AND us.product_id = 2 
		WHERE 1 = 1 `
	sql = fmt.Sprintf(sql, databaseName)
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql).QueryRow(&count)
	return
}
