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 }