hz_crm_api/models/cygx/cygx_user_record.go

package cygx

import (
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/utils"
	"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"`
	Mobile    string `description:"手机号"`
}

type OpenIdMobileList struct {
	OpenId string
	UserId int
	Mobile string `description:"手机号"`
}

func GetCygxUserRecord() (items []*CygxUserRecord, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT union_id,open_id FROM cygx_user_record  `
	_, err = o.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)

	listMobile, err := GetCygxUserRecordPowerMobileList(chartPermissionIds)
	if err != nil {
		return
	}
	var mobileArr []string
	for _, v := range listMobile {
		if v.Mobile != "" {
			mobileArr = append(mobileArr, v.Mobile)
		}
	}
	mobileLen := len(mobileArr)
	if mobileLen == 0 {
		return
	}
	openIdList, err := GetUserRecordListByMobileArr(mobileArr)
	if err != nil {
		return
	}
	items = openIdList
	return
}

// 获取所有有权限的用户的opid
func GetCygxUserRecordPowerMobileList(chartPermissionIds string) (items []*OpenIdMobileList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			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
		WHERE
			p.chart_permission_id IN (` + chartPermissionIds + `)
			AND r.create_platform = 4 
			AND p.STATUS IN ('正式','试用','永续')   GROUP BY u.user_id `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取关注这个文章对应产业的用户的 openid
func GetCygxUserFllowOpenid(articleId int) (items []*OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
			cr.open_id,
			cr.cygx_user_id  as 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 cygx_user_record AS cr ON cr.cygx_user_id = s.user_id 
			INNER JOIN cygx_industrial_article_group_management as mg  ON mg.industrial_management_id = f.industrial_management_id
		WHERE 1=1 
			AND mg.article_id  = ?`
	_, err = o.Raw(sql, articleId).QueryRows(&items)
	return
}

// 获取关注这个产业的用户的 openid
func GetCygxUserindustryFllowOpenid(industrialId int) (items []*OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
			cr.open_id,
			cr.cygx_user_id  as 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 cygx_user_record AS cr ON cr.cygx_user_id = s.user_id 
		WHERE
			 1=1  
			AND f.industrial_management_id  = ?`
	_, err = o.Raw(sql, industrialId).QueryRows(&items)
	return
}

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

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

// 获取选择策略推送的用户的openid openid
func GetCygxUserFllowCeLueOpenid(categoryId int) (items []*OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
			cr.open_id,
			cr.cygx_user_id AS 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 cygx_user_record AS cr ON cr.cygx_user_id = s.user_id 
		WHERE
			1 = 1 
			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.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
				cr.open_id,
				cr.cygx_user_id AS 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 cygx_user_record AS cr  ON cr.cygx_user_id = s.user_id 
			INNER JOIN cygx_industrial_activity_group_management AS mg ON mg.industrial_management_id = f.industrial_management_id 
		WHERE
			1=1 
			AND mg.activity_id = ?  AND mg.source = 1 `
	_, 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, itemsYx []*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)

	listMobile, err := GetCygxUserRecordPowerByActivitySetListMobile(chartPermissionIds, condition)
	if err != nil {
		return
	}
	var mobileArr []string
	for _, v := range listMobile {
		if v.Mobile != "" {
			mobileArr = append(mobileArr, v.Mobile)
		}
	}
	mobileLen := len(mobileArr)
	if mobileLen == 0 {
		return
	}
	openIdList, err := GetUserRecordListByMobileArr(mobileArr)
	if err != nil {
		return
	}
	items = openIdList

	openIdListYx, err := GetMfyxUserRecordListByMobileArr(mobileArr)
	if err != nil {
		return
	}
	itemsYx = openIdListYx
	return
}

// GetCygxUserRecordPowerByActivitySetListMobile 根据活动设置，获取所有有权限的用户的手机号
func GetCygxUserRecordPowerByActivitySetListMobile(chartPermissionIds, condition string) (items []*OpenIdMobileList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			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
		WHERE
			p.chart_permission_id IN (` + chartPermissionIds + `) ` + condition + ` 
			AND p.STATUS IN ('正式','试用','永续')   GROUP BY u.user_id `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取关注了查研观向小助手公众号的所有公司ID
func GetCygxUserRecordCompany() (items []*OpenIdList, err error) {
	listUser, err := GetCygxUserRecordCompanyUserId()
	if err != nil {
		return
	}
	var userIds []int
	for _, v := range listUser {
		userIds = append(userIds, v.UserId)
	}
	lenarr := len(userIds)
	if lenarr == 0 {
		return
	}

	var condition string
	var pars []interface{}
	condition = ` AND w.user_id  IN (` + utils.GetOrmInReplace(lenarr) + `)`
	pars = append(pars, userIds)

	o := orm.NewOrm()
	sql := `SELECT
			w.company_id 
		FROM
			wx_user AS w
		WHERE
			1 = 1` + condition + `GROUP BY	 w.company_id `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 获取关注了查研观向小助手公众号的所有用户ID
func GetCygxUserRecordCompanyUserId() (items []*OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
			u.open_id,
			u.cygx_user_id AS user_id 
		FROM
			cygx_user_record AS u 
		WHERE
			1 = 1 
			AND subscribe = 1 
		GROUP BY
			u.cygx_user_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
}

// 根据手机号获取用户的openid
func GetUserRecordListByMobileArr(bindAccount []string) (items []*OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	lenarr := len(bindAccount)
	if lenarr == 0 {
		return
	}
	var condition string
	var pars []interface{}
	condition = ` AND u.cygx_bind_account IN (` + utils.GetOrmInReplace(lenarr) + `)`
	pars = append(pars, bindAccount)
	sql := `SELECT
			u.open_id,
			u.cygx_user_id AS user_id 
		FROM
			cygx_user_record AS u 
		WHERE
			1 = 1  ` + condition
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 获取单个用户openid
func GetUserRecordOpenidByMobile(bindAccount string) (item *OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	var sql string
	sql = `SELECT cr.open_id,cr.cygx_user_id as  user_id
			FROM cygx_user_record AS cr 
			WHERE cr.cygx_bind_account = ?`
	err = o.Raw(sql, bindAccount).QueryRow(&item)
	return
}

// 获取预约活动的用户的openID
func GetActivityOpenIdList(activityId int) (items []*OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT DISTINCT cr.open_id,cr.cygx_user_id as  user_id
			FROM
			cygx_my_schedule AS m
			INNER JOIN cygx_user_record AS cr ON cr.cygx_bind_account = m.mobile 
			WHERE m.activity_id = ?  `
	_, err = o.Raw(sql, activityId).QueryRows(&items)
	return
}

// 获取预约活动的用户的openID
func GetActivitySpecialOpenIdList() (items []*OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT DISTINCT cr.open_id,cr.cygx_user_id as  user_id
			FROM
			cygx_user_follow_special AS m
			INNER JOIN cygx_user_record AS cr ON cr.cygx_bind_account = m.mobile  `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取关注作者的用户的openID
func GetFollowDepartmentOpenIdList(departmentId int) (items []*OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT  DISTINCT cr.open_id,cr.cygx_user_id as  user_id
			FROM
				cygx_article_department_follow AS f
				INNER JOIN cygx_user_record AS cr  ON cr.cygx_bind_account = f.mobile   
			WHERE
				f.department_id = ? `
	_, err = o.Raw(sql, departmentId).QueryRows(&items)
	return
}

// 获取关注产业的用户的openID
func GetFollowindustrialOpenIdList(industrialManagementId int) (items []*OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT DISTINCT cr.open_id,cr.cygx_user_id as  user_id
			FROM
			cygx_industry_fllow AS f
			INNER JOIN cygx_user_record AS cr  ON cr.cygx_bind_account = f.mobile   
			WHERE
			f.industrial_management_id = ? `
	_, err = o.Raw(sql, industrialManagementId).QueryRows(&items)
	return
}

// 获取所有关注了该产业用户的openid
func GetCygxUserIndustryFllowOpneidByActivityIds(activityId int) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			cr.open_id,
			cr.cygx_bind_account as mobile,
			cr.cygx_user_id AS user_id 
		FROM
			cygx_industrial_activity_group_management AS agm
			INNER JOIN cygx_industry_fllow AS f ON f.industrial_management_id = agm.industrial_management_id
			INNER JOIN cygx_user_record AS cr ON cr.cygx_user_id = f.user_id 
		WHERE
			agm.activity_id = ? `
	_, err = o.Raw(sql, activityId).QueryRows(&items)
	return
}

// 获取预约活动的用户的openID
func GetMfyxActivityOpenIdList(activityId int) (items []*OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT DISTINCT cr.open_id,cr.cygx_user_id as  user_id
			FROM
			cygx_my_schedule AS m
			INNER JOIN cygx_mfyx_gzh_user_record AS cr ON cr.cygx_bind_account = m.mobile 
			WHERE m.activity_id = ?  `
	_, err = o.Raw(sql, activityId).QueryRows(&items)
	return
}

// 根据手机号获取用户的openid
func GetMfyxUserRecordListByMobileArr(bindAccount []string) (items []*OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	lenarr := len(bindAccount)
	if lenarr == 0 {
		return
	}
	var condition string
	var pars []interface{}
	condition = ` AND u.cygx_bind_account IN (` + utils.GetOrmInReplace(lenarr) + `)`
	pars = append(pars, bindAccount)
	sql := `SELECT
			u.open_id,
			u.cygx_user_id AS user_id 
		FROM
			cygx_mfyx_gzh_user_record AS u 
		WHERE
			1 = 1  ` + condition
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 获取单个用户openid
func GetMfyxUserRecordOpenidByMobile(bindAccount string) (item *OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	var sql string
	sql = `SELECT cr.open_id,cr.cygx_user_id as  user_id
			FROM cygx_mfyx_gzh_user_record AS cr 
			WHERE cr.cygx_bind_account = ?`
	err = o.Raw(sql, bindAccount).QueryRow(&item)
	return
}