package models

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

type CompanyDetail struct {
	CompanyId   int    `orm:"column(company_id);pk"`
	CompanyName string `description:"客户名称"`
	Status      string `description:"客户状态"`
	SellerId    int    `description:"销售id"`
	SellerName  string `description:"销售名称"`
	Mobile      string `description:"销售手机号"`
	ProductId   int    `description:"1,FICC,2权益"`
	IsSuspend   int    `description:"1:暂停,0:启用"`
}

func GetCompanyDetailById(companyId int) (item *CompanyDetail, err error) {
	//sql := ` SELECT a.company_id,a.company_name,b.status,b.seller_id,b.seller_name,c.mobile
	//        FROM company AS a
	//		INNER JOIN company_product AS b ON a.company_id=b.company_id
	//		LEFT JOIN admin AS c ON b.seller_id=c.admin_id
	//		WHERE a.company_id=? AND  b.product_id=2 `

	sql := ` SELECT a.company_id,a.company_name,b.status,b.seller_id,b.seller_name,c.mobile,p.product_id
            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 cp.cygx_auth=1
			OR (a.company_id = ? AND  cp.permission_name = '策略' ) ORDER BY  b.product_id  DESC LIMIT 1` // 兼容永续状态
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId, companyId).QueryRow(&item)
	return
}

func GetCompanyDetailByIdGroup(companyId int) (item *CompanyDetail, err error) {
	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 
			OR (a.company_id = ? AND  cp.permission_name = '策略' ) 
			ORDER BY b.product_id DESC  LIMIT 0,1 `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId, companyId).QueryRow(&item)
	return
}

func GetCountCompanyDetailByIdGroup(companyId int) (count int, err error) {
	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    OR (a.company_id = ? AND  cp.permission_name = '策略' )   `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId, companyId).QueryRow(&count)
	return
}

//原有权限叫校验，下方更改与2021-5.18
//func GetCompanyPermission(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 a.product_id=2
//            AND b.show_type=1
//			AND c.status IN('正式','试用','永续') `
//	o := orm.NewOrm()
//	err = o.Raw(sql, companyId).QueryRow(&permission)
//	return
//}

func GetCompanyPermission(companyId int) (permission string, err error) {
	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 c.status IN('正式','试用','永续')
			AND a.status IN('正式','试用','永续') `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId).QueryRow(&permission)
	return
}

func GetCompanyPermissionName(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 c.status IN('正式','试用','永续')
			AND a.status IN('正式','试用','永续') `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId).QueryRow(&permission)
	return
}

func GetCompanyPermissionByUser(companyId int) (permission string, err error) {
	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 c.status IN('正式','试用','永续')
			AND a.status IN('正式','试用','永续') `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId).QueryRow(&permission)
	return
}

type PermissionItem struct {
	ChartPermissionId int    `description:"权限id"`
	PermissionName    string `description:"权限名称"`
	IsUpgrade         int    `description:"是否升级，1是，0否"`
	Remark            string `description:"备注区分主观客观"`
}

func GetCompanyPermissionList(companyId int) (items []*PermissionItem, err error) {
	sql := ` SELECT b.chart_permission_id,b.permission_name,b.remark,a.is_upgrade
			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 c.status IN('正式','试用','永续')
			AND a.status IN('正式','试用','永续')   ORDER BY b.sort ASC `
	o := orm.NewOrmUsingDB("weekly_report")
	_, err = o.Raw(sql, companyId).QueryRows(&items)
	return
}

// 获取用户权限不限制状态
func GetCompanyPermissionByUserNoStatus(companyId int) (permission string, err error) {
	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`
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId).QueryRow(&permission)
	return
}

// 获取正式权限
func GetCompanyPermissionByUserZhengShi(companyId int) (permission string, err error) {
	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.status IN('正式') `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId).QueryRow(&permission)
	return
}

func GetCompanyPermissionByName(name string) (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
			INNER JOIN company AS cm ON cm.company_id=c.company_id 
			WHERE  cm.company_name=?
			AND b.product_id = 2 `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, name).QueryRow(&permission)
	return
}

func GetCompanyDetailAllById(companyId int) (item *CompanyDetail, err error) {
	sql := ` SELECT a.company_id,a.company_name,b.status,b.seller_id,b.seller_name,c.mobile 
            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 cp.cygx_auth=1
			OR (a.company_id = ? AND  cp.permission_name = '策略' )` // 兼容永续状态
	//WHERE a.company_id=?  AND b.status IN('正式','试用','永续')`
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId, companyId).QueryRow(&item)
	return
}

// 获取对应销售以及销售的手机号
func GetSellerDetailAllByCompanyId(companyId int) (item *CompanyDetail, err error) {
	sql := ` SELECT c.mobile ,c.real_name as seller_name
			FROM
			company AS a
			INNER JOIN company_product AS b ON a.company_id = b.company_id
			INNER JOIN admin AS c ON b.seller_id = c.admin_id 
			WHERE
			b.product_id = 2 
			AND a.company_id = ? LIMIT 1`
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId).QueryRow(&item)
	return
}

// 获取FICC与权益销售
func GetCompanySellerName(companyId int) (sellerName string, err error) {
	sql := ` SELECT
			GROUP_CONCAT( DISTINCT p.seller_name SEPARATOR '/' ) AS sellerName 
		FROM
			company_product AS p
		WHERE
			p.company_id = ? `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId).QueryRow(&sellerName)
	return
}

// 获取权益销售
func GetCompanySellerNameRai(companyId int) (sellerName string, err error) {
	sql := ` SELECT
			seller_name  AS sellerName 
		FROM
			company_product AS p
		WHERE
			p.company_id = ?  AND  product_id = 2 `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId).QueryRow(&sellerName)
	return
}

type CompanyProductStatus struct {
	IsSuspend int `description:"1:暂停,0:启用"`
}

type CompanyProductDetial struct {
	IsSuspend int    `description:"1:暂停,0:启用"`
	Scale     string `description:"管理规模，空不填，1：:50亿以下，2：50~100亿，3：100亿以上。多个用, 隔开"`
}

func GetCompanyProductDetail(companyId, productId int) (item *CompanyProductDetial, err error) {
	sql := ` SELECT * FROM company_product WHERE company_id = ? AND product_id = ?; `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId, productId).QueryRow(&item)
	return
}

// 获取公司详情详情
func GetCompanyByName(companyName string) (item *Company, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT *  FROM company WHERE company_name = ? LIMIT 1`
	err = o.Raw(sql, companyName).QueryRow(&item)
	return
}

// 获取公司详情详情
func GetCompanyById(companyId int) (item *Company, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT *  FROM company WHERE company_id = ? LIMIT 1`
	err = o.Raw(sql, companyId).QueryRow(&item)
	return
}

// 获取公司详情详情
func GetCompanyByThirdName(tripartiteCompanyCode string) (item *Company, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT *  FROM company WHERE tripartite_company_code = ? LIMIT 1`
	err = o.Raw(sql, tripartiteCompanyCode).QueryRow(&item)
	return
}

// 更新机构互动量
func UpdateComapanyInteractionNum(interactionNum, interactionNumSeller, companyId int) (err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `UPDATE company SET interaction_num = ? ,  interaction_num_seller = ?  WHERE company_id=? `
	_, err = o.Raw(sql, interactionNum, interactionNumSeller, companyId).Exec()
	return
}

// 获取有机构互动量的公司
func GetCompanyInteractionNumList() (items []*Company, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT *  FROM company  WHERE  interaction_num > 0  `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetCompanyProductCount(companyId, productId int) (count int, err error) {
	sql := ` SELECT COUNT(1) AS count FROM  company_product WHERE company_id = ? AND product_id = ?  AND  company_id  > 1  `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId, productId).QueryRow(&count)
	return
}