package models

import "github.com/beego/beego/v2/client/orm"

func GetCountCompanyDetailByIdGroupTrip(companyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT
	COUNT( 1 ) AS count 
FROM
	company AS a
	INNER JOIN company_product AS b ON a.company_id = b.company_id
	INNER JOIN company_report_permission AS p ON p.company_id = a.company_id
	INNER JOIN chart_permission AS cp ON cp.chart_permission_id = p.chart_permission_id
	LEFT JOIN admin AS c ON b.seller_id = c.admin_id 
WHERE
	a.company_id = ? 
	AND b.product_id = 2 
	AND b.status = '正式' 
	AND p.is_upgrade =  1
	OR (
		a.company_id = ? 
	AND b.product_id = 2 
	AND b.status = '永续') `
	err = o.Raw(sql, companyId, companyId).QueryRow(&count)
	return
}

func GetCompanyDetailByIdGroupTrip(companyId int) (item *CompanyDetail, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT a.company_id,a.company_name,b.status,b.seller_id,b.seller_name,c.mobile ,b.is_suspend
            FROM company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			INNER JOIN company_report_permission AS p ON p.company_id = a.company_id
			INNER JOIN chart_permission AS cp ON cp.chart_permission_id = p.chart_permission_id
			LEFT JOIN admin AS c ON b.seller_id=c.admin_id
			WHERE a.company_id=? AND b.product_id = 2  
			ORDER BY b.product_id DESC  LIMIT 0,1  `
	err = o.Raw(sql, companyId).QueryRow(&item)
	return
}

func GetCompanyPermissionByUserTrip(companyId int) (permission string, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT
			GROUP_CONCAT( DISTINCT b.remark ORDER BY b.sort ASC SEPARATOR ',' ) AS permission 
		FROM
			company_report_permission AS a
			INNER JOIN chart_permission AS b ON a.chart_permission_id = b.chart_permission_id
			INNER JOIN company_product AS c ON a.company_id = c.company_id 
			AND a.product_id = c.product_id 
		WHERE
			a.company_id = ? 
			AND c.is_suspend = 0 
			AND b.cygx_auth = 1 
			AND a.is_upgrade = 1 
			AND c.STATUS IN ( '正式' ) 
			OR ( a.company_id = ? AND c.is_suspend = 0 AND b.cygx_auth = 1 AND c.STATUS IN ( '永续' ) ) 
			OR ( a.company_id = ? AND c.is_suspend = 0 AND b.cygx_auth = 1 AND b.chart_permission_name = '策略' AND c.STATUS IN ( '正式' ) )`
	err = o.Raw(sql, companyId, companyId, companyId).QueryRow(&permission)
	return
}

// 获取可查看升级权限的正式权限
func GetCompanyPermissionByUserZhengShiTrip(companyId int) (permission string, err error) {
	sql := ` SELECT GROUP_CONCAT(DISTINCT b.chart_permission_name  ORDER BY b.sort ASC  SEPARATOR ',') AS permission
			FROM company_report_permission AS a
			INNER JOIN chart_permission AS b ON a.chart_permission_id=b.chart_permission_id
			INNER JOIN company_product AS c ON a.company_id=c.company_id AND a.product_id=c.product_id
			WHERE  a.company_id=?
			AND c.is_suspend=0
            AND b.cygx_auth=1
			AND ( a.is_upgrade = 1 AND c.STATUS = '正式' OR c.STATUS = '永续' ) `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId).QueryRow(&permission)
	return
}

// GetCompanyReportPermissionUpgrade 获取升级的权限类型
func GetCompanyReportPermissionUpgrade(companyId, productId int) (items []*CompanyReportPermission, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT
	re.*
FROM
	company_report_permission  as re 
	INNER JOIN chart_permission as  c ON c.chart_permission_id = re.chart_permission_id
WHERE
	re.company_id = ? 
	AND re.product_id = ? 
	AND re.is_upgrade = 1 
	AND c.permission_type = 1
	AND STATUS = '正式' `
	_, err = o.Raw(sql, companyId, productId).QueryRows(&items)
	return
}

// GetChartPermissionByIds 主键获取权限
func GetChartPermissionByIds(permissionIds []string) (list []*ChartPermission, err error) {
	qb, _ := orm.NewQueryBuilder("mysql")
	// 构建查询对象
	qb.Select("*").From("chart_permission").
		Where("chart_permission_id").In(permissionIds...)
	// 导出 SQL 语句
	sql := qb.String()

	// 执行 SQL 语句
	o := orm.NewOrmUsingDB("weekly_report")
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetCompanyPermissionNameCheck 通过名称判断客户是否开了某一类型的权限
func GetCompanyPermissionNameCheck(companyId, productId int, permissionName string) (count int, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT
			COUNT( 1 ) AS count 
		FROM
			company_report_permission AS a 
			INNER JOIN chart_permission as  c  ON c.chart_permission_id = a.chart_permission_id
		WHERE
			a.company_id = ?
			AND a.STATUS = '正式'
			AND a.product_id = ? 
			AND c.chart_permission_name = ? `
	err = o.Raw(sql, companyId, productId, permissionName).QueryRow(&count)
	return
}

// GetCompanyReportPermissionByCompanyIdAndProductId 获取权限类型
func GetCompanyReportPermissionByCompanyIdAndProductId(companyId, productId int) (items []*CompanyReportPermission, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT
	re.*
FROM
	company_report_permission  as re 
	INNER JOIN chart_permission as  c ON c.chart_permission_id = re.chart_permission_id
WHERE
	re.company_id = ? 
	AND re.product_id = ? 
	AND STATUS = '正式' `
	_, err = o.Raw(sql, companyId, productId).QueryRows(&items)
	return
}

// GetCompanyReportPermissionByCompanyIdAndProductIdForInit 获取权限类型-初始化用，去掉正式的判断
func GetCompanyReportPermissionByCompanyIdAndProductIdForInit(companyId, productId int) (items []*CompanyReportPermission, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT
	re.*
FROM
	company_report_permission  as re 
	INNER JOIN chart_permission as  c ON c.chart_permission_id = re.chart_permission_id
WHERE
	re.company_id = ? 
	AND re.product_id = ? 
	AND STATUS NOT IN ('正式','永续') GROUP BY re.chart_permission_id `
	_, err = o.Raw(sql, companyId, productId).QueryRows(&items)
	return
}

// GetCompanyReportPermissionByCompanyIdAndProductIdForInit 获取权限类型-初始化total用，去掉权限的判断
func GetCompanyReportPermissionByCompanyIdAndProductIdForInit2(companyId, productId int) (items []*CompanyReportPermission, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT
	re.*
FROM
	company_report_permission  as re 
	INNER JOIN chart_permission as  c ON c.chart_permission_id = re.chart_permission_id
WHERE
	re.company_id = ? 
	AND re.product_id = ? 
	 `
	_, err = o.Raw(sql, companyId, productId).QueryRows(&items)
	return
}