hz_crm_api/models/wechat_send_msg.go

package models

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

type OpenIdList struct {
	OpenId string
	UserId int
}

func GetOpenIdList() (items []*OpenIdList, err error) {
	//openIdstr := WxUsersGet()
	//sql:=` SELECT DISTINCT open_id FROM wx_user AS wu
	//      INNER JOIN company AS c ON c.company_id = wu.company_id
	//      INNER JOIN company_product AS d ON c.company_id=d.company_id
	//      WHERE wu.open_id IS NOT NULL AND  d.status IN('正式','试用','永续') `
	sql := `SELECT DISTINCT ur.open_id,wu.user_id FROM wx_user AS wu 
          INNER JOIN company AS c ON c.company_id = wu.company_id 
          INNER JOIN company_product AS d ON c.company_id=d.company_id
		INNER join user_record  as ur on wu.user_id=ur.user_id
          WHERE ur.open_id != "" AND ur.subscribe=1 and ur.create_platform=1 AND  d.status IN('正式','试用','永续') `
	//if openIdstr != "" {
	//	sql += ` AND ur.open_id in (` + openIdstr + `) `
	//}
	_, err = orm.NewOrm().Raw(sql).QueryRows(&items)
	return
}

func GetOpenIdListByMobile(mobile string) (items []*OpenIdList, err error) {
	//openIdstr := WxUsersGet()
	sql := `SELECT DISTINCT ur.open_id,wu.user_id FROM wx_user AS wu 
          INNER JOIN company AS c ON c.company_id = wu.company_id 
          INNER join user_record  as ur on wu.user_id=ur.user_id
          WHERE ur.open_id != "" AND ur.subscribe=1 and ur.create_platform=1 AND wu.mobile=? `
	//if openIdstr != "" {
	//	sql += ` AND ur.open_id in (` + openIdstr + `) `
	//}
	_, err = orm.NewOrm().Raw(sql, mobile).QueryRows(&items)
	return
}

//// 获取预约活动的用户的openID
//func GetActivityOpenIdList(activityId int) (items []*OpenIdList, err error) {
//	sql := `SELECT DISTINCT cr.open_id,u.user_id
//			FROM
//			cygx_my_schedule AS m
//			INNER JOIN user_record AS u ON u.bind_account = m.mobile
//			INNER JOIN cygx_user_record AS cr ON cr.union_id = u.union_id
//			WHERE m.activity_id = ? AND u.create_platform = 4 `
//	_, err = orm.NewOrm().Raw(sql, activityId).QueryRows(&items)
//	return
//}

// 获取预约活动的用户的openID测试环境
//func GetActivityOpenIdListByDeBug(activityId int) (items []*OpenIdList, err error) {
//	sql := `SELECT
//			u.open_id,
//			u.user_id
//		FROM
//			cygx_my_schedule AS s
//			INNER JOIN wx_user AS wx ON wx.user_id = s.user_id
//			INNER JOIN user_record AS u ON u.bind_account = wx.mobile
//			INNER JOIN company_product AS p ON p.company_id = wx.company_id
//		WHERE
//			s.activity_id = ?
//			AND u.create_platform = 1
//			AND p.STATUS IN ( '正式', '试用', '永续' )
//		GROUP BY
//			u.open_id`
//	_, err = orm.NewOrm().Raw(sql, activityId).QueryRows(&items)
//	return
//}

//// 获取预约活动的用户的openID
//func GetActivitySpecialOpenIdList() (items []*OpenIdList, err error) {
//	sql := `SELECT DISTINCT cr.open_id,u.user_id
//			FROM
//			cygx_user_follow_special AS m
//			INNER JOIN user_record AS u ON u.bind_account = m.mobile
//			INNER JOIN cygx_user_record AS cr ON cr.union_id = u.union_id
//			WHERE  u.create_platform = 4 `
//	_, err = orm.NewOrm().Raw(sql).QueryRows(&items)
//	return
//}

// 获取预约活动的用户的openID测试环境
//func GetActivitySpecialOpenIdListByDeBug() (items []*OpenIdList, err error) {
//	sql := `SELECT
//			u.open_id,
//			u.user_id
//		FROM
//			cygx_user_follow_special AS s
//			INNER JOIN wx_user AS wx ON wx.user_id = s.user_id
//			INNER JOIN user_record AS u ON u.bind_account = wx.mobile
//			INNER JOIN company_product AS p ON p.company_id = wx.company_id
//		WHERE
//			u.create_platform = 1
//			AND p.STATUS IN ( '正式', '试用', '永续' )
//		GROUP BY
//			u.open_id`
//	_, err = orm.NewOrm().Raw(sql).QueryRows(&items)
//	return
//}

// 获取关注作者的用户的openID
//func GetFollowDepartmentOpenIdList(departmentId int) (items []*OpenIdList, err error) {
//	sql := `SELECT cr.open_id,u.user_id
//			FROM
//				cygx_article_department_follow AS f
//				INNER JOIN user_record AS u ON u.bind_account = f.mobile
//				INNER JOIN wx_user AS wx ON wx.user_id = f.user_id
//				INNER JOIN company_product AS p ON p.company_id = wx.company_id
//				INNER JOIN cygx_user_record AS cr ON cr.union_id = u.union_id
//			WHERE
//				f.department_id = ?
//				AND u.create_platform = 4
//				AND f.type = 1
//				AND p.status IN ('正式','试用','永续')
//			GROUP BY
//				cr.open_id `
//	_, err = orm.NewOrm().Raw(sql, departmentId).QueryRows(&items)
//	return
//}

// 获取关注作者的用户的openID测试环境
//func GetFollowDepartmentOpenIdListByDeBug(departmentId int) (items []*OpenIdList, err error) {
//	sql := `SELECT
//			u.open_id,
//			u.user_id
//		FROM
//			cygx_article_department_follow AS f
//			INNER JOIN wx_user AS wx ON wx.user_id = f.user_id
//			INNER JOIN user_record AS u ON u.bind_account = wx.mobile
//			INNER JOIN company_product AS p ON p.company_id = wx.company_id
//		WHERE
//			f.department_id = ?
//			AND u.create_platform = 1
//			AND f.type = 1
//			AND p.STATUS IN ( '正式', '试用', '永续' )
//		GROUP BY
//			u.open_id`
//	_, err = orm.NewOrm().Raw(sql, departmentId).QueryRows(&items)
//	return
//}

// 获取关注产业的用户的openID
func GetFollowindustrialOpenIdList(industrialManagementId int) (items []*OpenIdList, err error) {
	sql := `SELECT cr.open_id,u.user_id
			FROM
			cygx_industry_fllow AS f
			INNER JOIN wx_user AS wx ON wx.user_id = f.user_id
			INNER JOIN user_record AS u ON u.bind_account = wx.mobile 
			INNER JOIN cygx_user_record AS cr ON cr.union_id = u.union_id 
			WHERE
			f.industrial_management_id = ? 
			AND u.create_platform = 4 
			AND f.type = 1 GROUP BY cr.open_id `
	_, err = orm.NewOrm().Raw(sql, industrialManagementId).QueryRows(&items)
	return
}

// 获取关注产业的用户的openID 测试环境
//func GetFollowindustrialOpenIdListByDeBug(industrialManagementId int) (items []*OpenIdList, err error) {
//	sql := `SELECT
//			u.open_id,
//			u.user_id
//		FROM
//			cygx_industry_fllow AS f
//			INNER JOIN wx_user AS wx ON wx.user_id = f.user_id
//			INNER JOIN user_record AS u ON u.bind_account = wx.mobile
//		WHERE
//			f.industrial_management_id = ?
//			AND u.create_platform = 1
//			AND u.bind_account != ""
//			AND f.type = 1
//		GROUP BY
//			u.open_id`
//	_, err = orm.NewOrm().Raw(sql, industrialManagementId).QueryRows(&items)
//	return
//}

// GetUserOpenidListByUserIds 根据用户id字符串集合来获取他的openid列表集合
func GetUserOpenidListByUserIds(userIdStr []string) (list []*OpenIdList, err error) {
	if len(userIdStr) <= 0 {
		return
	}
	sql := `SELECT open_id,u.user_id FROM user_record WHERE user_id in (` + strings.Join(userIdStr, ",") + `) and create_platform = 1`
	_, err = orm.NewOrm().Raw(sql).QueryRows(&list)
	return
}

func GetAdminOpenIdByMobile(mobile string) (items []*OpenIdList, err error) {
	sql := `SELECT DISTINCT ur.open_id,wu.user_id FROM wx_user AS wu 
          INNER JOIN company AS c ON c.company_id = wu.company_id 
          INNER join user_record  as ur on wu.user_id=ur.user_id
          WHERE ur.open_id != "" and ur.create_platform=1 AND wu.mobile=? `
	_, err = orm.NewOrm().Raw(sql, mobile).QueryRows(&items)
	return
}

// 根据手机号获取用户的openid查研观向小助手专用
func GetUserRecordListByMobile(platform int, bindAccount string) (items []*OpenIdList, err error) {
	var sql string
	if utils.RunMode == "release" {
		sql = `SELECT cr.open_id FROM user_record  as u 
			INNER JOIN cygx_user_record AS cr ON cr.union_id = u.union_id 
			WHERE create_platform=? AND bind_account IN (` + bindAccount + `)`
	} else {
		platform = 1
		sql = `SELECT open_id FROM	user_record  WHERE create_platform =? AND bind_account IN (` + bindAccount + `)`
	}
	_, err = orm.NewOrm().Raw(sql, platform).QueryRows(&items)
	return
}

// 获取单个用户openid
func GetCompanyDetailByIdGroup(platform int, bindAccount string) (item *OpenIdList, err error) {
	o := orm.NewOrm()
	var sql string
	sql = `SELECT cr.open_id,wu.user_id FROM user_record  as u 
			INNER JOIN cygx_user_record AS cr ON cr.union_id = u.union_id 
			INNER JOIN wx_user AS wu ON wu.mobile = u.bind_account 
			WHERE create_platform=? AND u.bind_account = ?`
	err = o.Raw(sql, platform, bindAccount).QueryRow(&item)
	return
}

// 获取小助手所有的用户的openid
func GetCygxUserAllOpneid() (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			cr.open_id,
			r.user_id 
		FROM
			user_record AS r
			INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id 
		WHERE
			r.create_platform = 4`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetOpenIdArr() (items []string, err error) {
	sql := ` SELECT DISTINCT ur.open_id FROM wx_user AS wu 
          INNER JOIN company AS c ON c.company_id = wu.company_id 
          INNER JOIN company_product AS d ON c.company_id=d.company_id
		INNER JOIN user_record  AS ur ON wu.user_id=ur.user_id
          WHERE ur.open_id != "" AND ur.subscribe=1 AND ur.create_platform=1 AND  d.status IN('正式','试用','永续')
         ORDER BY FIELD(c.company_id, 16) desc, ur.user_record_id asc`
	_, err = orm.NewOrm().Raw(sql).QueryRows(&items)
	return
}

// 获取预约活动的用户的openID测试环境
func GetActivitySpecialOpenIdListMobile(condition string, pars []interface{}) (items []*OpenIdList, err error) {
	sql := `SELECT
			cr.open_id,
			u.user_id
		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
			 r.create_platform = 4  AND p.STATUS IN ('正式','试用','永续')  ` + condition + ` GROUP BY cr.open_id`
	_, err = orm.NewOrm().Raw(sql, pars).QueryRows(&items)
	return
}

// 获取所有关注了该产业用户的openid
func GetCygxUserIndustryFllowOpneid(IndustrialManagementId int) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			cr.open_id,
			r.user_id 
		FROM
			user_record AS r
			INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id 
			INNER join cygx_industry_fllow  as cf on cf.user_id = r.user_id 
		WHERE
			r.create_platform = 4
			AND cf.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,
	r.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 user_record AS r
	INNER JOIN cygx_user_record AS cr 
WHERE
	agm.activity_id = ? 
	AND cr.union_id = r.union_id 
	AND r.create_platform = 4 
	AND r.user_id = f.user_id;`
	_, err = o.Raw(sql, activityId).QueryRows(&items)
	return
}

// 获取所有关注了该产业永续客户的openid
func GetCygxForeverUserIndustryFllowOpneidByActivityIds(activityId int) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `			
SELECT
	cr.open_id,
	r.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 user_record AS r
	INNER JOIN cygx_user_record AS cr 
	INNER JOIN wx_user AS wx ON wx.user_id = r.user_id 
	INNER JOIN company_product AS p ON p.company_id = wx.company_id 
WHERE
	agm.activity_id = ? 
	AND cr.union_id = r.union_id 
	AND r.create_platform = 4 
	AND r.user_id = f.user_id
	AND p.status = "永续";`
	_, err = o.Raw(sql, activityId).QueryRows(&items)
	return
}

// 获取所有关注了该产业试用客户的openid
func GetCygxTryOutUserIndustryFllowOpneidByActivityIds(activityId int) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `			
SELECT
	cr.open_id,
	r.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 user_record AS r
	INNER JOIN cygx_user_record AS cr 
	INNER JOIN wx_user AS wx ON wx.user_id = r.user_id 
	INNER JOIN company_product AS p ON p.company_id = wx.company_id 
WHERE
	agm.activity_id = ? 
	AND cr.union_id = r.union_id 
	AND r.create_platform = 4 
	AND r.user_id = f.user_id
	AND p.status = "试用";`
	_, err = o.Raw(sql, activityId).QueryRows(&items)
	return
}

// 获取所有关注了该产业用户的companyIds
func GetCygxIndustryFollowCompanyIdsByActivityId(activityId int) (item *string, err error) {
	o := orm.NewOrm()
	sql := `			
SELECT
	GROUP_CONCAT( DISTINCT f.company_id SEPARATOR ',' ) AS company_ids
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 user_record AS r
	INNER JOIN cygx_user_record AS cr 
WHERE
	agm.activity_id = ? 
	AND r.create_platform = 4 
	AND r.user_id = f.user_id;`
	err = o.Raw(sql, activityId).QueryRow(&item)
	return
}

// 获取所有关注了该产业用户的openidBy公司id
func GetCygxUserIndustryFllowOpneidByActivityIdAndCompanyIds(activityId int, companyIds string) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `			
SELECT
	cr.open_id,
	r.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 user_record AS r
	INNER JOIN cygx_user_record AS cr 
WHERE
	agm.activity_id = ? 
	AND cr.union_id = r.union_id 
	AND r.create_platform = 4 
	AND r.user_id = f.user_id
	AND f.company_id IN (` + companyIds + `);`
	_, err = o.Raw(sql, activityId).QueryRows(&items)
	return
}

// 获取所有互动过的用户id
func GetCygxInteractiveUserByActivityId(activityId int) (items []*int, err error) {
	o := orm.NewOrm()
	sql := `			
SELECT user_id FROM cygx_activity_signup WHERE activity_id =? AND fail_type = 0
UNION ALL
SELECT user_id FROM cygx_activity_appointment WHERE activity_id = ?
UNION ALL
SELECT user_id FROM cygx_activity_meeting_reminder WHERE activity_id = ?
UNION ALL
SELECT user_id FROM cygx_activity_help_ask  WHERE activity_id = ? `
	_, err = o.Raw(sql, activityId, activityId, activityId, activityId).QueryRows(&items)
	return
}

// 获取所有用户的openid
func GetCygxUserOpneidByUserIds(userIds string) (items []*OpenIdList, err error) {
	o := orm.NewOrm()
	sql := `			
SELECT
	cr.open_id,
	r.user_id 
FROM
	user_record AS r
	INNER JOIN cygx_user_record AS cr 
WHERE
	cr.union_id = r.union_id 
	AND r.create_platform = 4 
	AND r.user_id IN (` + userIds + `);`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetOpenIdArrByClassifyNameSecond(classifyNameSecond string) (items []string, err error) {
	sql := ` SELECT DISTINCT ur.open_id FROM wx_user AS wu 
			INNER JOIN company AS c ON c.company_id = wu.company_id 
			INNER JOIN company_product AS d ON c.company_id=d.company_id
			INNER JOIN user_record  AS ur ON wu.user_id=ur.user_id
			INNER JOIN company_report_permission AS e ON d.company_id=e.company_id
			INNER JOIN chart_permission AS f ON e.chart_permission_id=f.chart_permission_id
			INNER JOIN chart_permission_search_key_word_mapping AS g ON f.chart_permission_id=g.chart_permission_id
			WHERE ur.open_id != "" AND ur.subscribe=1 AND ur.create_platform=1 AND  d.status IN('正式','试用','永续') AND  e.status IN('正式','试用','永续') 
			AND g.from='rddp'
			AND g.key_word=?
			ORDER BY FIELD(c.company_id, 16) DESC, ur.user_record_id ASC  `
	_, err = orm.NewOrm().Raw(sql, classifyNameSecond).QueryRows(&items)
	return
}

func GetOpenIdArrByChartPermissionIds(chartPermissionIds string) (items []string, err error) {
	sql := ` SELECT DISTINCT ur.open_id FROM wx_user AS wu 
INNER JOIN company AS c ON c.company_id = wu.company_id 
INNER JOIN company_product AS d ON c.company_id=d.company_id
INNER JOIN user_record  AS ur ON wu.user_id=ur.user_id
INNER JOIN company_report_permission AS e ON d.company_id=e.company_id
INNER JOIN chart_permission AS f ON e.chart_permission_id=f.chart_permission_id
WHERE ur.open_id != "" AND ur.subscribe=1 AND ur.create_platform=1 AND  d.status IN('正式','试用','永续')
AND f.chart_permission_id IN(` + chartPermissionIds + `)
ORDER BY FIELD(c.company_id, 16) DESC, ur.user_record_id ASC  `
	_, err = orm.NewOrm().Raw(sql).QueryRows(&items)
	return
}

func GetOpenIdArrByVarietyTag(varietyTagId int) (items []string, err error) {
	sql := ` SELECT DISTINCT ur.open_id FROM wx_user AS wu 
INNER JOIN company AS c ON c.company_id = wu.company_id 
INNER JOIN company_product AS d ON c.company_id=d.company_id
INNER JOIN user_record  AS ur ON wu.user_id=ur.user_id
INNER JOIN company_report_permission AS e ON d.company_id=e.company_id
INNER JOIN chart_permission AS f ON e.chart_permission_id=f.chart_permission_id
INNER JOIN variety_tag AS g ON f.chart_permission_id=g.chart_permission_id
WHERE ur.open_id != "" AND ur.subscribe=1 AND ur.create_platform=1 AND  d.status IN('正式','试用','永续')
AND g.variety_tag_id=?
ORDER BY FIELD(c.company_id, 16) DESC, ur.user_record_id ASC  `
	_, err = orm.NewOrm().Raw(sql, varietyTagId).QueryRows(&items)
	return
}