hz_crm_api/models/wechat_send_msg.go

package models

import (
	"github.com/beego/beego/v2/client/orm"
	"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
}

// 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 GetCygxUserAllOpneid() (items []*OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
			cr.open_id,
			cr.cygx_user_id  as  user_id
		FROM  cygx_user_record AS cr `
	_, 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
}

type MobileUserIdList struct {
	Mobile string
	UserId int
}

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

// 获取所有关注了该产业用户的openid
func GetCygxUserIndustryFllowOpneid(IndustrialManagementId int) (items []*OpenIdList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
			cr.open_id,
			cr.cygx_user_id  as user_id
		FROM
			cygx_industry_fllow AS cf
			INNER JOIN cygx_user_record AS cr ON cr.cygx_user_id = cf.user_id
		WHERE
			1=1
			AND cf.industrial_management_id = ?`
	_, err = o.Raw(sql, IndustrialManagementId).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
}

// 获取所有关注了该产业用户的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.NewOrmUsingDB("hz_cygx")
	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
}