hz_crm_api/models/cygx/cygx_user_record.go

package cygx

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

type CygxUserRecord struct {
	UserRecordId int       `orm:"column(user_record_id);pk"`
	OpenId       string    `description:"用户openid，最大长度：32"`
	UnionId      string    `description:"用户unionid，最大长度：64"`
	NickName     string    `descritpion:"用户昵称，最大长度：32"`
	Sex          int       `descritpion:"普通用户性别，1为男性，2为女性"`
	Province     string    `description:"普通用户个人资料填写的省份,最大长度：30"`
	City         string    `description:"普通用户个人资料填写的城市,最大长度：30"`
	Country      string    `description:"国家，如中国为CN,最大长度：30"`
	Headimgurl   string    `description:"用户第三方（微信）头像,最大长度：512"`
	CreateTime   time.Time `description:"创建时间，关系添加时间、用户授权时间"`
}

type OpenIdList struct {
	OpenId    string
	UserId    int
	CompanyId int `description:"公司ID"`
}

func GetCygxUserRecord() (items []*CygxUserRecord, err error) {
	sql := `SELECT union_id,open_id FROM cygx_user_record  `
	_, err = orm.NewOrm().Raw(sql).QueryRows(&items)
	return
}

// 获取所有有权限的用户的opid
func GetCygxUserRecordPower(chartPermissionIds string) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			cr.open_id,
			u.user_id,
			u.company_id,
			u.real_name,
			u.mobile 
		FROM
			company_report_permission AS p
			INNER JOIN wx_user AS u ON u.company_id = p.company_id
			INNER JOIN user_record AS r ON r.user_id = u.user_id
			INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id 
		WHERE
			p.chart_permission_id IN (` + chartPermissionIds + `)
			AND r.create_platform = 4 
			AND p.STATUS IN ('正式','试用','永续')   GROUP BY cr.open_id `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取所有有权限的用户的opid
func GetCygxUserRecordPowerOpenid(condition string, pars []interface{}) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			cr.open_id,
			u.user_id,
			u.company_id,
			u.real_name,
			u.mobile 
		FROM
			company_report_permission AS p
			INNER JOIN wx_user AS u ON u.company_id = p.company_id
			INNER JOIN user_record AS r ON r.user_id = u.user_id
			INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id 
		WHERE
 			1 =1
			AND r.create_platform = 4  ` + condition + ` GROUP BY cr.open_id `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 获取关注这个文章对应产业的用户的 openid
func GetCygxUserFllowOpenid(articleId int) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			cr.open_id,
			f.user_id 
		FROM
			cygx_xzs_choose_send AS s
			INNER JOIN cygx_industry_fllow AS f ON f.user_id = s.user_id
			INNER JOIN user_record AS r ON r.user_id = f.user_id
			INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id 
			INNER JOIN cygx_industrial_article_group_management as mg  ON mg.industrial_management_id = f.industrial_management_id
		WHERE
			r.create_platform = 4 
			AND mg.article_id  = ?`
	_, err = o.Raw(sql, articleId).QueryRows(&items)
	return
}

// 获取关注这个产业的用户的 openid
func GetCygxUserindustryFllowOpenid(industrialId int) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			cr.open_id,
			f.user_id 
		FROM
			cygx_xzs_choose_send AS s
			INNER JOIN cygx_industry_fllow AS f ON f.user_id = s.user_id
			INNER JOIN user_record AS r ON r.user_id = f.user_id
			INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id
		WHERE
			r.create_platform = 4 
			AND f.industrial_management_id  = ?`
	_, err = o.Raw(sql, industrialId).QueryRows(&items)
	return
}

// 获取关注这个文章对应作者的用户的 openid
func GetCygxUserFllowDepartmentOpenid(articleId int) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			cr.open_id,
			f.user_id 
		FROM
			cygx_article_department_follow AS f
			INNER JOIN user_record AS r ON r.user_id = f.user_id
			INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id
			INNER JOIN cygx_article AS a ON a.department_id = f.department_id 
		WHERE
			r.create_platform = 4 
			AND a.article_id = ?`
	_, err = o.Raw(sql, articleId).QueryRows(&items)
	return
}

// 获取拒绝接收推送的的用户的 openid
func GetCygxUserRefusetOpenid() (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			cr.open_id,
			s.user_id
			FROM
			cygx_xzs_choose_send AS s
			INNER JOIN user_record AS r ON r.user_id = s.user_id
			INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id
			WHERE
			r.create_platform = 4
			AND s.is_refuse = 1`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取选择策略推送的用户的openid openid
func GetCygxUserFllowCeLueOpenid(categoryId int) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			cr.open_id,
			f.user_id 
		FROM
			cygx_xzs_choose_send AS s
			INNER JOIN cygx_xzs_choose_category AS f ON f.user_id = s.user_id
			INNER JOIN user_record AS r ON r.user_id = f.user_id
			INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id 
		WHERE
			r.create_platform = 4
			AND s.is_refuse = 0
			AND s.is_subjective = 1
			AND s.is_objective = 1
			AND f.category_id = ?`
	_, err = o.Raw(sql, categoryId).QueryRows(&items)
	return
}

// 获取关注这个活动对应产业的用户的 openid
func GetCygxActiviyUserFllowOpenid(activityId int) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			cr.open_id,
			f.user_id 
		FROM
			cygx_xzs_choose_send AS s
			INNER JOIN cygx_industry_fllow AS f ON f.user_id = s.user_id
			INNER JOIN user_record AS r ON r.user_id = f.user_id
			INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id
			INNER JOIN cygx_industrial_activity_group_management AS mg ON mg.industrial_management_id = f.industrial_management_id 
		WHERE
			r.create_platform = 4 
			AND mg.activity_id = ?`
	_, err = o.Raw(sql, activityId).QueryRows(&items)
	return
}

// 根据销售规模，获取所有有权限的用户的opid
func GetCygxUserRecordPowerByScale(chartPermissionIds, scale string) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			cr.open_id,
			u.user_id,
			u.company_id,
			u.real_name,
			u.mobile 
		FROM
			company_report_permission AS p
			INNER JOIN wx_user AS u ON u.company_id = p.company_id
			INNER JOIN user_record AS r ON r.user_id = u.user_id
			INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id 
			INNER JOIN company_product AS cp ON cp.company_id = p.company_id
		WHERE
			p.chart_permission_id IN (` + chartPermissionIds + `)
			AND cp.scale IN (` + scale + `)
			AND r.create_platform = 4 
			AND p.STATUS IN ('正式','试用','永续')   GROUP BY  u.company_id `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetCygxUserRecordPowerByActivitySet 根据活动设置，获取所有有权限的用户的opid
func GetCygxUserRecordPowerByActivitySet(chartPermissionIds, condition string) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			cr.open_id,
			u.user_id,
			u.company_id,
			u.real_name,
			u.mobile 
		FROM
			company_report_permission AS p
			INNER JOIN wx_user AS u ON u.company_id = p.company_id
			INNER JOIN user_record AS r ON r.user_id = u.user_id
			INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id 
		WHERE
			p.chart_permission_id IN (` + chartPermissionIds + `) ` + condition + ` AND r.create_platform = 4 
			AND p.STATUS IN ('正式','试用','永续')   GROUP BY cr.open_id `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取关注了查研观向小助手公众号的所有公司ID
func GetCygxUserRecordCompany() (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			w.company_id 
		FROM
			cygx_user_record AS u
			INNER JOIN user_record AS r ON r.union_id = u.union_id
			INNER JOIN wx_user AS w ON w.user_id = r.user_id 
		WHERE
			1 = 1 
			AND r.create_platform = 4 
			AND w.company_id != 1 
		GROUP BY
			w.company_id `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

type CompanyReportPermissionItem struct {
	CompanyId         int
	ChartPermissionId int
	IsUpgrade         int    `description:"是否升级，1是，0否"`
	Remark            string `description:"备注，权限名称带有主观客观这种"`
	PermissionName    string `description:"权限名称"`
}

// GetCompanyReportPermissionItem 获取客户对应的权限
func GetCompanyReportPermissionItem(condition string, pars []interface{}) (items []*CompanyReportPermissionItem, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			r.company_id,
			r.chart_permission_id,
			r.is_upgrade,
			c.remark,
			c.permission_name 
		FROM
			company_report_permission AS r
			INNER JOIN chart_permission AS c ON r.chart_permission_id = c.chart_permission_id 
		WHERE
			1 = 1
			AND r.product_id = 2 
			AND r.STATUS = '正式' ` + condition
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}