package company

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

type ChartPermission struct {
	ChartPermissionId   int       `description:"权限id"`
	ChartPermissionName string    `description:"权限名称（旧）"`
	PermissionName      string    `description:"权限名称"`
	Sort                int       `description:"排序"`
	Enabled             int       `description:"是否可用"`
	CreatedTime         time.Time `description:"创建时间"`
	LastUpdatedTime     time.Time `description:"最后更新时间"`
	TeleconferenceSort  int       `description:"电话会排序"`
	Remark              string    `description:"备注"`
	ClassifyName        string    `description:"分类"`
	PermissionType      int       `description:"1主观，2客观"`
	Checked             bool      `description:"选中状态"`
	ParentId            int       `description:"父级权限id"`
	IsPublic            int       `description:"是否是公有权限1:公有权限，0私有权限"`
}

type PermissionSetItem struct {
	ChartPermissionId int    `description:"权限id"`
	PermissionName    string `description:"权限名称"`
	PermissionType    int    `description:"1主观，2客观"`
	ParentId          int    `description:"父级权限id"`
	IsPublic          int    `description:"是否是公有权限1:公有权限，0私有权限"`
	Checked           bool   `description:"选中状态"`

	Child []*PermissionSetItem `description:"具体的主客观-方便前端的排版用的"`
}

type PermissionSetItemType struct {
	PermissionName string `description:"权限名称"`
	Checked        bool   `description:"选中状态"`
	CheckedMinate  bool   `description:"不确定状态"`
	NoClicking     bool   `description:"是否禁止点击"`
	Items          []*PermissionLookItem
}

type PermissionSetList struct {
	ClassifyName string `description:"分类"`
	Items        []*PermissionSetItem
	CheckList    []int
}

type PermissionSetListType struct {
	ClassifyName string `description:"分类"`
	Items        []*PermissionSetItemType
	CheckList    []int
}

type PermissionSetResp struct {
	List []*PermissionSetList
	//ListUpgrade []*PermissionSetList     `description:"升级权限列表"`
	ListType []*PermissionSetListType `description:"主观客观列表"`
}

type ChartPermissionFirst struct {
	ChartPermissionId int    `description:"权限id"`
	ClassifyName      string `description:"分类"`
}

func GetPermissionSetItems(productId int, classifyName string) (items []*PermissionSetItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1 AND product_id=? AND classify_name=? AND parent_id>0 AND permission_type=0 ORDER BY sort ASC `
	_, err = o.Raw(sql, productId, classifyName).QueryRows(&items)
	return
}

func GetPermissionByProductId(productId int) (items []*PermissionSetItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1 AND product_id=? AND permission_type=0 ORDER BY sort ASC `
	_, err = o.Raw(sql, productId).QueryRows(&items)
	return
}
func GetPermissionSetItemsByType(productId int, classifyName string) (items []*PermissionSetItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1 AND product_id=? AND classify_name=? AND parent_id>0 AND permission_type!=2 ORDER BY sort ASC `
	_, err = o.Raw(sql, productId, classifyName).QueryRows(&items)
	return
}

func GetPermissionSetSandoItems(productId int, classifyName string) (items []*PermissionLookItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1 AND product_id=? AND classify_name=? AND parent_id>0 ORDER BY sort ASC `
	_, err = o.Raw(sql, productId, classifyName).QueryRows(&items)
	return
}

func GetPermissionSetItemsType(productId int, classifyName string) (items []*PermissionLookItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1 AND product_id=? AND classify_name=?  AND permission_type!=0 ORDER BY sort ASC `
	_, err = o.Raw(sql, productId, classifyName).QueryRows(&items)
	return
}

type CompanyReportPermission struct {
	CompanyReportPermissionId int64 `orm:"column(company_report_permission_id);pk"`
	CompanyId                 int
	ReportPermissionId        int
	CreatedTime               time.Time
	LastUpdatedTime           time.Time
	ChartPermissionId         int
	StartDate                 string    `description:"权限开始日期"`
	EndDate                   string    `description:"权限结束日期"`
	ProductId                 int       `description:"产品id"`
	ProductName               string    `description:"产品名称"`
	CompanyContractId         int       `description:"合同id"`
	Status                    string    `description:"'正式','试用','关闭'"`
	ModifyTime                time.Time `description:"修改时间"`
	IsUpgrade                 int       `description:"是否升级，1是，0否"`
	ExpensiveYx               int       `description:"权益研选: 0-3w; 1-5w ,2: 10W"`
}

// 新增客户权限
func AddCompanyReportPermission(item *CompanyReportPermission) (err error) {
	o := orm.NewOrm()
	lastId, err := o.Insert(item)
	item.CompanyReportPermissionId = lastId
	return
}

func GetCompanyReportPermissionCount(companyId, productId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS count FROM company_report_permission WHERE company_id=? AND product_id=? `
	err = o.Raw(sql, companyId, productId).QueryRow(&count)
	return
}

func DeleteCompanyReportPermission(companyId, productId int) (err error) {
	o := orm.NewOrm()
	sql := ` DELETE FROM company_report_permission WHERE company_id=? AND product_id=? `
	_, err = o.Raw(sql, companyId, productId).Exec()
	return
}

type PermissionLookResp struct {
	List    []*PermissionLookList
	ListRai []*PermissionLookList `description:"权益"`
}

type PermissionLookSandoResp struct {
	List     []*PermissionLookList
	ListType []*PermissionSetListType `description:"主观客观列表"`
}

type PermissionLookList struct {
	ClassifyName string `description:"分类"`
	Items        []*PermissionLookItem
	ItemsType    []*PermissionSetItemType `description:"主观客观列表"`
	CheckList    []int
	RaiMerge     int `description:"权益-用于前端判断展示形式：1-全部合并 2-全部拆分"`
}

type PermissionLookItem struct {
	ChartPermissionId  int                   `description:"权限id"`
	PermissionName     string                `description:"权限名称"`
	StartDate          string                `description:"权限开始日期"`
	EndDate            string                `description:"权限结束日期"`
	Status             string                `description:"'正式','试用','关闭'"`
	ExpireDay          string                `description:"到期天数"`
	ClassifyName       string                `description:"分类"`
	PermissionType     int                   `description:"1主观，2客观"`
	PermissionTypeName string                `description:"主观、客观"`
	Checked            bool                  `description:"选中状态"`
	Remark             string                `description:"备注"`
	IsMerge            bool                  `description:"是否合并行业, 给前端的标识, 暂时仅权益使用"`
	RaiBothHas         bool                  `description:"权益-是否主客观都有"`
	IsUpgrade          int                   `description:"是否升级，1是，0否"`
	ExpensiveYx        int                   `description:"权益研选: 0-3w; 1-5w ,2: 10W"`
	Points             float64               `description:"研选扣点包点数"`
	ParentId           int                   `description:"父级权限id"`
	IsPublic           int                   `description:"是否是公有权限1:公有权限，0私有权限"`
	Child              []*PermissionLookItem `description:"子权限"`
}

// todo 确认是否需要删除
func GetPermissionLookItems(productId int, classifyName string) (items []*PermissionLookItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1 AND product_id=? AND classify_name=? AND parent_id > 0 ORDER BY sort ASC `
	_, err = o.Raw(sql, productId, classifyName).QueryRows(&items)
	return
}

func GetPermissionLookItemsByProductId(productId int) (items []*PermissionLookItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1 AND product_id=? ORDER BY sort ASC `
	_, err = o.Raw(sql, productId).QueryRows(&items)
	return
}

func GetPermissionLookItemsByParentId(productId int, parentId int) (items []*PermissionLookItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1 AND product_id=? AND parent_id=? ORDER BY sort ASC `
	_, err = o.Raw(sql, productId, parentId).QueryRows(&items)
	return
}

func GetCompanyPermissionCheck(companyId, permissionId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS count FROM company_report_permission AS a WHERE a.company_id=? AND a.chart_permission_id=? `
	err = o.Raw(sql, companyId, permissionId).QueryRow(&count)
	return
}

// GetCompanyPermission 根据客户id和品种id获取对应的数据
func GetCompanyPermission(companyId, permissionId int) (item *CompanyReportPermission, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_report_permission AS a WHERE a.company_id=? AND a.chart_permission_id=? `
	err = o.Raw(sql, companyId, permissionId).QueryRow(&item)
	return
}

func GetCompanyPermissionCheckItem(companyId, permissionId int) (item *PermissionLookItem, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_report_permission AS a WHERE a.company_id=? AND a.chart_permission_id=? `
	err = o.Raw(sql, companyId, permissionId).QueryRow(&item)
	return
}

// 通过主观ID获取对应的客观ID
func GetPermissionIdById(permissionIds string) (allpermissionId string, err error) {
	sql := ` SELECT
				GROUP_CONCAT( DISTINCT chart_permission_id  SEPARATOR ',' ) AS permission 
			FROM
				chart_permission 
			WHERE
			    parent_id > 0 and 
				product_permission_name IN (
				SELECT
					product_permission_name 
				FROM
					chart_permission 
			WHERE
				chart_permission_id IN ( ` + permissionIds + ` )) `
	o := orm.NewOrm()
	err = o.Raw(sql).QueryRow(&allpermissionId)
	return
}

// 通过主观ID获取对应的客观ID
func GetCompanyPermissionIdById(companyId int) (allpermissionId string, err error) {
	sql := `SELECT
	GROUP_CONCAT( DISTINCT chart_permission_id SEPARATOR ',' ) AS permission 
FROM
	company_report_permission AS a 
WHERE
	a.company_id = ? 
	AND product_id = 2`
	o := orm.NewOrm()
	err = o.Raw(sql, companyId).QueryRow(&allpermissionId)
	return
}

type ContractPermissionList struct {
	ClassifyName string `description:"分类"`
	Items        []*PermissionLookItem
	CheckList    []int
	CheckAll     bool
}

func GetCompanyIdStr(permissionStr string) (company_id_str string, err error) {
	o := orm.NewOrm()
	sql := `SELECT GROUP_CONCAT(DISTINCT company_id SEPARATOR ',') AS company_id_str
          FROM company_report_permission AS a
		  WHERE a.chart_permission_id IN( ` + permissionStr + `) `
	err = o.Raw(sql).QueryRow(&company_id_str)
	return
}

type PermissionVarietyResp struct {
	List []*PermissionVarietyList
}

type PermissionVarietyItem struct {
	ChartPermissionId int    `description:"权限id"`
	ParentId          int    `description:"父级权限id"`
	ClassifyName      string `orm:"column(permission_name)" description:"权限名称"`
}

type PermissionVarietyList struct {
	ChartPermissionId int    `description:"父级id"`
	ClassifyName      string `description:"分类"`
	Items             []*PermissionVarietyItem
}

// todo 删除品种列表
func GetPermissionVarietyItems(productId int, classifyName string) (items []*PermissionVarietyItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1 AND product_id=? AND classify_name=?  AND parent_id > 0 GROUP BY permission_name ORDER BY sort ASC `
	_, err = o.Raw(sql, productId, classifyName).QueryRows(&items)
	return
}

func GetPermissionVarietyItemsByProductId(productId int) (items []*PermissionVarietyItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1 AND product_id=? and parent_id > 0 GROUP BY permission_name ORDER BY sort ASC `
	_, err = o.Raw(sql, productId).QueryRows(&items)
	return
}

func GetCompanyReportPermission(companyId, productId int) (items []*CompanyReportPermission, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? `
	_, err = o.Raw(sql, companyId, productId).QueryRows(&items)
	return
}

func GetCompanyReportPermissionByStatus(companyId, productId int, status string) (items []*CompanyReportPermission, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? AND status = ? `
	_, err = o.Raw(sql, companyId, productId, status).QueryRows(&items)
	return
}

type PermissionCompany struct {
	CompanyId int
}

//
//func GetCompanyListCompanyIdByPermissionIdStr(permissionStr string, permissionCount int) (items []*PermissionCompany, err error) {
//	o := orm.NewOrm()
//	sql := `
//		SELECT company_id, COUNT(company_id) ks
//		  FROM company_report_permission
//		 WHERE report_permission_id IN (` + permissionStr + `)
//
//		 GROUP BY company_id
//		HAVING company_id IN
//
//		 (SELECT company_id
//			FROM company_report_permission
//		   GROUP BY company_id
//		  HAVING COUNT(company_id) = ?)
//
//		AND COUNT(company_id) = ?
//`
//	_, err = o.Raw(sql, permissionCount, permissionCount).QueryRows(&items)
//	return
//}

func GetCompanyListCompanyIdByPermissionIdStr(permissionStr string) (company_id string, err error) {
	o := orm.NewOrm()
	sql := `
		SELECT 
		GROUP_CONCAT(DISTINCT company_id) AS company_id
		FROM company_report_permission
		WHERE report_permission_id IN (?)
		#GROUP  BY company_id
		`
	err = o.Raw(sql, permissionStr).QueryRow(&company_id)
	return
}

func GetPermissionNameByPermissionIdStr(permissionStr string) (chart_permission_name string, err error) {
	o := orm.NewOrm()
	sql := `SELECT 	GROUP_CONCAT(DISTINCT chart_permission_name SEPARATOR ',') AS chart_permission_name FROM chart_permission WHERE chart_permission_id IN (` + permissionStr + `)`
	err = o.Raw(sql).QueryRow(&chart_permission_name)
	return
}

func GetPermissionIdsByPermissionNameStr(chartPermissionName string) (chart_permission_id string, err error) {
	o := orm.NewOrm()
	sql := `SELECT  GROUP_CONCAT(DISTINCT chart_permission_id  SEPARATOR ',') AS chart_permission_id 	FROM chart_permission WHERE chart_permission_name IN(` + chartPermissionName + `)`
	err = o.Raw(sql).QueryRow(&chart_permission_id)
	return
}

// 新增试用权限
type PermissionAddTryOutReq struct {
	CompanyId         int    `description:"客户id"`
	ChartPermissionId string `description:"权限id,多个用英文逗号隔开"`
}

func FixPermissionStatus(companyId, productId int, startDate, endDate, status string) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_report_permission SET start_date=?,end_date=?,status=?,modify_time=NOW() WHERE company_id=? AND product_id=? AND status IS NULL `
	_, err = o.Raw(sql, startDate, endDate, status, companyId, productId).Exec()
	return
}

func GetPermissionLookItemsExt(productId string, classifyName string) (items []*PermissionLookItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1 AND product_id IN(` + productId + `) AND classify_name=? AND parent_id > 0 ORDER BY sort ASC `
	_, err = o.Raw(sql, classifyName).QueryRows(&items)
	return
}
func GetPermissionLookItemsExtByParentId(productId string, parentId int) (items []*PermissionLookItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1 AND product_id IN(` + productId + `) AND parent_id=? ORDER BY sort ASC `
	_, err = o.Raw(sql, parentId).QueryRows(&items)
	return
}

// 客户授权产品结构体（包含产品名称）
type CompanyReportPermissionAndName struct {
	CompanyReportPermissionId int `description:"客户授权产品id"`
	CompanyId                 int
	ReportPermissionId        int
	CreatedTime               time.Time
	LastUpdatedTime           time.Time
	ChartPermissionId         int
	StartDate                 string    `description:"权限开始日期"`
	EndDate                   string    `description:"权限结束日期"`
	ProductId                 int       `description:"产品id"`
	ProductName               string    `description:"产品名称"`
	CompanyContractId         int       `description:"合同id"`
	PermissionName            string    `description:"客户授权产品的名称"`
	ClassifyName              string    `description:"客户授权产品的分类名称"`
	Status                    string    `description:"'正式','试用','关闭'"`
	ModifyTime                time.Time `description:"修改时间"`
}

// 根据企业用户id字符串集合
func GetCompanyReportPermissionListByCompanyIds(companyIds string) (items []*CompanyReportPermissionAndName, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.*,b.permission_name,b.classify_name FROM company_report_permission a left join chart_permission b on a.chart_permission_id=b.chart_permission_id WHERE a.company_id in (` + companyIds + `)`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 根据企业用户id和产品id获取所有正式的权限
func GetCompanyReportPermissionListByProductId(companyId, productId int) (items []*CompanyReportPermissionAndName, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.*,b.permission_name,b.classify_name FROM company_report_permission a left join chart_permission b on a.chart_permission_id=b.chart_permission_id WHERE a.company_id = ? and a.product_id=? and a.status="正式"`
	_, err = o.Raw(sql, companyId, productId).QueryRows(&items)
	return
}

// GetChartPermissionListByIds 根据权限id集合获取所有的权限列表
func GetChartPermissionListByIds(chartPermissionIds []int) (list []*ChartPermission, err error) {
	o := orm.NewOrm()
	//o.Using("rddp")
	//sNums：=  make（[]，len（nums））
	idStrList := make([]string, len(chartPermissionIds))
	for key, id := range chartPermissionIds {
		idStrList[key] = fmt.Sprint(id)
	}
	idStr := strings.Join(idStrList, ",")
	sql := `SELECT * FROM chart_permission WHERE chart_permission_id in (` + idStr + `) `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// 根据企业用户id和产品id获取所有正式的权限
func GetCompanyProductReportPermissionList(companyId, productId int) (items []*CompanyReportPermissionAndName, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.*,b.permission_name,b.classify_name FROM company_report_permission a left join chart_permission b on a.chart_permission_id=b.chart_permission_id WHERE a.company_id = ? and a.product_id=? `
	_, err = o.Raw(sql, companyId, productId).QueryRows(&items)
	return
}

// GetChartPermissionListById 根据权限id获取产品权限详情
func GetChartPermissionListById(chartPermissionId int) (item *ChartPermission, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM chart_permission WHERE chart_permission_id =? `
	err = o.Raw(sql, chartPermissionId).QueryRow(&item)
	return
}

func GetPermissionLookItemsSandO(permissionIds string) (items []*PermissionLookItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1 AND chart_permission_id IN (` + permissionIds + `) ORDER BY sort ASC `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetPermissionLookItemsSandOByName(permissionName string) (items []*PermissionLookItem, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM chart_permission WHERE enabled=1  AND product_id = 2 AND remark IN (` + permissionName + `) AND parent_id > 0 ORDER BY sort ASC `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetParentIdFromGroup(gid int) (items *int, err error) {
	o := orm.NewOrm()
	sql := `SELECT parent_id FROM sys_group WHERE group_id=? `
	err = o.Raw(sql, gid).QueryRow(&items)
	return
}

func GetGroupIdsByParentId(gid int) (items []*string, err error) {
	o := orm.NewOrm()
	sql := `SELECT group_id FROM sys_group WHERE parent_id=? `
	_, err = o.Raw(sql, gid).QueryRows(&items)
	return
}

func GetGroupNamesById(gid int) (items *string, err error) {
	o := orm.NewOrm()
	sql := `SELECT group_name FROM sys_group WHERE group_id=? `
	err = o.Raw(sql, gid).QueryRow(&items)
	return
}

// GetChartPermissionFirst 获取排序后的权限分类
func GetChartPermissionFirst() (list []*ChartPermissionFirst, err error) {
	o := orm.NewOrm()
	sql := ` SELECT chart_permission_id, classify_name FROM chart_permission where enabled =1 and product_id=1 and parent_id=0 ORDER BY sort ASC `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetCompanyReportPermissionUpgrade 获取升级的权限类型
func GetCompanyReportPermissionUpgrade(companyId, productId int) (items []*CompanyReportPermission, err error) {
	o := orm.NewOrm()
	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
}

// GetCompanyPermissionNameCheck 通过名称判断客户是否开了某一类型的权限
func GetCompanyPermissionNameCheck(companyId, productId int, permissionName string) (count int, err error) {
	o := orm.NewOrm()
	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
}

// GetCompanyReportPermissionUpgradeByChartPermissionId 通过行业权限获取具备该行业升级权限的公司
func GetCompanyReportPermissionUpgradeByChartPermissionId(chartPermissionId int) (items []*CompanyReportPermission, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	company_id 
FROM
	company_report_permission AS p 
WHERE
	1 = 1 
	AND p.is_upgrade = 1 
	AND p.chart_permission_id = ? 
	AND p.STATUS = '正式' 
	OR (
		p.STATUS = '永续' 
	AND p.chart_permission_id = ? 
	)`
	_, err = o.Raw(sql, chartPermissionId, chartPermissionId).QueryRows(&items)
	return
}

func GetPermissionNameByPermissionIds(permissionIds []int) (names []string, err error) {
	if len(permissionIds) == 0 {
		return
	}
	o := orm.NewOrm()
	sql := `SELECT
				DISTINCT chart_permission_name
			FROM
				chart_permission
			WHERE
				chart_permission_id IN (` + utils.GetOrmInReplace(len(permissionIds)) + `)`
	_, err = o.Raw(sql, permissionIds).QueryRows(&names)
	return
}

func GetPermissionIdsByPermissionNames(names []string) (ids []int, err error) {
	if len(names) == 0 {
		return
	}
	o := orm.NewOrm()
	sql := `SELECT
				DISTINCT chart_permission_id
			FROM
				chart_permission
			WHERE
				chart_permission_name IN (` + utils.GetOrmInReplace(len(names)) + `) AND parent_id > 0 `
	_, err = o.Raw(sql, names).QueryRows(&ids)
	return
}

// GetCompanyIdsStrByReportPermissionIds 根据有效阅读权限IDs获取客户IDs
func GetCompanyIdsStrByReportPermissionIds(permissionIds []int) (companyIds []int, err error) {
	if len(permissionIds) == 0 {
		return
	}
	o := orm.NewOrm()
	sql := `SELECT
				DISTINCT company_id
			FROM
				company_report_permission
			WHERE
				report_permission_id IN (` + utils.GetOrmInReplace(len(permissionIds)) + `) AND (DATE(NOW()) BETWEEN start_date AND end_date) AND status IN ('试用', '正式', '永续')`
	_, err = o.Raw(sql, permissionIds).QueryRows(&companyIds)
	return
}

// GetCompanyIdsStrByContractPermissionIds 根据合同权限IDs获取客户IDs
func GetCompanyIdsStrByContractPermissionIds(permissionIds []int) (companyIds []int, err error) {
	if len(permissionIds) == 0 {
		return
	}
	o := orm.NewOrm()
	sql := `SELECT
				DISTINCT company_id
			FROM
				company_contract_permission
			WHERE
				chart_permission_id IN (` + utils.GetOrmInReplace(len(permissionIds)) + `) AND (DATE(NOW()) BETWEEN start_date AND end_date)`
	_, err = o.Raw(sql, permissionIds).QueryRows(&companyIds)
	return
}

// CompanyListPermissionIds 客户列表权限IDs
type CompanyListPermissionIds struct {
	CompanyId          int    `description:"客户ID"`
	ChartPermissionIds string `description:"权限IDs"`
}

// GetContractPermissionIdsByCompanyIds 根据客户ID获取合同权限IDs
func GetContractPermissionIdsByCompanyIds(companyIds []int) (items []*CompanyListPermissionIds, err error) {
	if len(companyIds) == 0 {
		return
	}
	o := orm.NewOrm()
	sql := `SELECT
				company_id,
				GROUP_CONCAT(
					DISTINCT chart_permission_id
				) AS chart_permission_ids
			FROM
				company_contract_permission
			WHERE
				(DATE(NOW()) BETWEEN start_date AND end_date) AND company_id IN (` + utils.GetOrmInReplace(len(companyIds)) + `)`
	sql += `GROUP BY company_id`
	_, err = o.Raw(sql, companyIds).QueryRows(&items)
	return
}

// GetReportPermissionIdsByCompanyIds 根据客户ID获取有效阅读权限IDs
func GetReportPermissionIdsByCompanyIds(companyIds []int) (items []*CompanyListPermissionIds, err error) {
	if len(companyIds) == 0 {
		return
	}
	o := orm.NewOrm()
	sql := `SELECT
				company_id,
				GROUP_CONCAT(
					DISTINCT chart_permission_id
				) AS chart_permission_ids
			FROM
				company_report_permission
			WHERE
				(DATE(NOW()) BETWEEN start_date AND end_date) AND status IN ('试用', '正式', '永续') AND company_id IN (` + utils.GetOrmInReplace(len(companyIds)) + `)`
	sql += `GROUP BY company_id`
	_, err = o.Raw(sql, companyIds).QueryRows(&items)
	return
}

// GetCompanyReportPermissionByCompanyIdAndProductId 获取权限类型
func GetCompanyReportPermissionByCompanyIdAndProductId(companyId, productId int) (items []*CompanyReportPermission, err error) {
	o := orm.NewOrm()
	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
}

// 列表
func GetCompanyReportPermissionList(condition string, pars []interface{}, startSize, pageSize int) (items []*CompanyReportPermission, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_report_permission  WHERE 1= 1 `
	if condition != "" {
		sql += condition
	}
	if startSize+pageSize > 0 {
		sql += ` LIMIT ?,?  `
		_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	} else {
		_, err = o.Raw(sql, pars).QueryRows(&items)
	}
	return
}