package models import ( "github.com/beego/beego/v2/client/orm" "hongze/hongze_mfyx/utils" "time" ) type WxUser struct { UserId int `orm:"column(user_id);pk"` OpenId string `description:"open_id"` UnionId string `description:"union_id"` Subscribe string `description:"是否关注"` CompanyId int `description:"客户id"` NickName string `description:"用户昵称"` RealName string `description:"用户实际名称"` UserCode string `description:"用户编码"` Mobile string `description:"手机号码"` BindAccount string `description:"绑定时的账号"` WxCode string `description:"微信号"` Profession string `description:"职业"` Email string `description:"邮箱"` Telephone string `description:"座机"` Sex int `description:"普通用户性别，1为男性，2为女性"` Province string `description:"普通用户个人资料填写的省份"` City string `description:"普通用户个人资料填写的城市"` Country string `description:"国家，如中国为CN"` SubscribeTime int `description:"关注时间"` Remark string `description:"备注"` Headimgurl string `description:"用户头像，最后一个数值代表正方形头像大小（有0、46、64、96、132数值可选，0代表640*640正方形头像），用户没有头像时该项为空"` Privilege string `description:"用户特权信息，json数组，如微信沃卡用户为（chinaunicom）"` Unionid string `description:"用户统一标识。针对一个微信开放平台帐号下的应用，同一用户的unionid是唯一的。"` FirstLogin int `description:"是否第一次登陆"` Enabled int `description:"是否可用"` CreatedTime time.Time `description:"创建时间"` LastUpdatedTime time.Time `description:"最新一次修改时间"` Seller string `description:"销售员"` Note string `description:"客户备份信息"` IsNote int `description:"是否备注过信息"` FromType string `description:"report' COMMENT 'report:研报，teleconference:电话会"` ApplyMethod int `description:"0:未申请，1:已付费客户申请试用，2:非客户申请试用"` RegisterTime time.Time `description:"注册时间"` RegisterPlatform int `description:"注册平台,1:微信端,2:PC网页端"` IsFreeLogin bool `description:"是否免登陆,true：免登陆,false:非免登陆"` LoginTime time.Time `description:"最近一次登录时间"` SessionKey string `description:"微信小程序会话密钥"` IsRegister int `description:"是否注册:1:已注册,0:未注册"` Source int `description:"绑定来源,1:微信端,2:pc网页端，3:查研观向小程序,4:每日咨询"` CountryCode string `description:"区号"` OutboundMobile string `description:"外呼手机号"` OutboundCountryCode string `description:"外呼手机号区号"` TripartiteCode string `description:"第三方给过来的用户编码，判断用户是否存在"` } // 添加用户信息 func AddWxUser(item *WxUser) (lastId int64, err error) { o := orm.NewOrmUsingDB("weekly_report") lastId, err = o.Insert(item) return } type WxUserItem struct { UserId int `description:"用户id"` OpenId string `description:"open_id"` UnionId string `description:"union_id"` CompanyId int `description:"客户id"` NickName string `description:"用户昵称"` RealName string `description:"用户实际名称"` Mobile string `description:"手机号码"` BindAccount string `description:"绑定时的账号"` Email string `description:"邮箱"` Headimgurl string `description:"用户头像，最后一个数值代表正方形头像大小（有0、46、64、96、132数值可选，0代表640*640正方形头像），用户没有头像时该项为空"` ApplyMethod int `description:"0:未申请，1:已付费客户申请试用，2:非客户申请试用"` FirstLogin int `description:"是否第一次登陆"` IsFreeLogin int `description:"是否免登陆,true：免登陆,false:非免登陆"` LoginTime time.Time `description:"登录时间"` CreatedTime time.Time `description:"创建时间"` LastUpdatedTime time.Time `description:"最近一次修改时间"` SessionKey string `description:"微信小程序会话密钥"` CompanyName string `description:"公司名称"` IsRegister int `description:"是否注册:1:已注册,0:未注册"` CountryCode string `description:"手机国家区号"` OutboundMobile string `description:"外呼手机号"` OutboundCountryCode string `description:"外呼手机号区号"` IsMsgOutboundMobile int `description:"是否弹窗过绑定外呼手机号区号"` IsMaker int `description:"是否是决策人"` Position string `description:"职务"` InviteCompany string `description:"邀请机构 ，LUODING:络町"` Source int } func GetWxUserItemByUnionid(unionid string) (item *WxUserItem, err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `SELECT * FROM wx_user WHERE union_id=? ` err = o.Raw(sql, unionid).QueryRow(&item) return } // 根据用户ID获取相关信息 func GetWxUserItemByUserId(userId int) (item *WxUserItem, err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `SELECT a.*,b.company_name FROM wx_user AS a LEFT JOIN company AS b on a.company_id=b.company_id WHERE a.user_id=? ` err = o.Raw(sql, userId).QueryRow(&item) return } type WxLoginResp struct { Authorization string UserId int FirstLogin int Headimgurl string `description:"用户头像"` Mobile string `description:"手机号"` Email string `description:"邮箱"` CompanyName string `description:"客户名称"` Status string `description:"状态"` EndDate string `description:"到期日期"` ProductName string `description:"客户类型名称"` } type WxGetUserInfoReq struct { RawData string `description:"rawData"` Signature string `description:"signature"` EncryptedData string `description:"encryptedData"` Iv string `description:"iv"` } // 修改用户会话key func ModifyWxUserSessionKey(sessionKey string, userId int) (err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `UPDATE wx_user SET session_key=? WHERE user_id=? ` _, err = o.Raw(sql, sessionKey, userId).Exec() return } // 添加用户信息 func ModifyWxUserInfo(unionId, nickName, province, city, country, avatar string, gender, userId int) (err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `UPDATE wx_user SET union_id=?,unionid=?,nick_name=?,sex=?,province=?,city=?,country=?,headimgurl=? WHERE user_id=? ` _, err = o.Raw(sql, unionId, unionId, nickName, gender, province, city, country, avatar, userId).Exec() return } // 修改用户会话key func DeleteWxUserByUserId(userId int) (err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `DELETE FROM wx_user WHERE user_id=? ` _, err = o.Raw(sql, userId).Exec() return } type WxGetUserInfoResp struct { //UsersId int `orm:"column(id);pk"` //Mobile string `description:"手机号"` //NickName string `description:"昵称"` //Gender int `description:"用户性别 1:男性,2:女性，0:未知(默认)"` //CreateTime time.Time `description:"注册时间"` //ModifyTime time.Time `description:"修改时间"` //AvatarUrl string `description:"头像地址"` //City string `description:"城市"` //Province string `description:"省"` //Country string `description:"国家"` //Language string `description:"语言"` //Appid string `description:"Appid"` //Timestamp int64 `description:"时间戳"` Authorization string `description:"登陆凭证，后续接口调用时，带在请求头里面Key:Authorization"` } type WxGetPhoneNumberReq struct { EncryptedData string `description:"encryptedData"` Iv string `description:"iv"` } func ModifyUsersMobile(usersId int, phoneNumber string) (err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `UPDATE wx_user SET mobile=? WHERE user_id=? ` _, err = o.Raw(sql, phoneNumber, usersId).Exec() return } type WxGetPhoneNumberResp struct { PhoneNumber string `description:"用户绑定的手机号（国外手机号会有区号）"` PurePhoneNumber string `description:"没有区号的手机号"` CountryCode string `description:"区号"` } // 根据用户手机号获取相关信息 func GetWxUserItemByMobile(mobile string) (item *WxUserItem, err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `SELECT a.*,b.company_name FROM wx_user AS a LEFT JOIN company AS b on a.company_id=b.company_id WHERE a.mobile= '` + mobile + `' ORDER BY a.company_id DESC LIMIT 1 ` err = o.Raw(sql).QueryRow(&item) return } // 根据用户手机号获取相关信息 func GetWxUserAouthByMobile(mobile string) (item *WxUserItem, err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `SELECT a.*, b.company_name FROM wx_user AS a LEFT JOIN company AS b ON a.company_id = b.company_id WHERE a.mobile = ? ORDER BY a.company_id DESC LIMIT 1` err = o.Raw(sql, mobile).QueryRow(&item) return } func GetWxUserItemByEmail(email string) (item *WxUserItem, err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `SELECT * FROM wx_user WHERE email=? ` err = o.Raw(sql, email).QueryRow(&item) return } func ModifyReportLastViewTime(uid int) (err error) { o := orm.NewOrmUsingDB("weekly_report") sql := ` UPDATE wx_user SET report_last_view_time=NOW() WHERE user_id=? ` _, err = o.Raw(sql, uid).Exec() return } // 变更联系人是否已注册状态 func ModifyWxUserRegisterStatus(userId int) (err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `UPDATE wx_user SET is_register=?,source=3,register_time=NOW() WHERE user_id = ? ` _, err = o.Raw(sql, 1, userId).Exec() return } // 修改用户是否绑定外呼手机号弹窗 func ModifyWxUserIsMsgOutboundMobile(userId int) (err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `UPDATE wx_user SET is_msg_outbound_mobile=1 WHERE user_id=? ` _, err = o.Raw(sql, userId).Exec() return } // 列表 func GetUserListAll() (items []*WxUserItem, err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `SELECT * FROM wx_user WHERE mobile <>'' AND outbound_mobile = ''` _, err = o.Raw(sql).QueryRows(&items) return } // 修改手机号区号 8位号码+852，9位号码+886,10位号码+1,11位及以上号码+86 func UPdateUserCountryCode(item *WxUserItem) (err error) { o := orm.NewOrmUsingDB("weekly_report") if item.CountryCode == "" && len(item.Mobile) >= 11 { sql := ` UPDATE wx_user SET outbound_mobile= ? , outbound_country_code=86 , country_code=86 WHERE user_id = ?` _, err = o.Raw(sql, item.Mobile, item.UserId).Exec() } else if item.CountryCode == "" && len(item.Mobile) == 8 { sql := ` UPDATE wx_user SET outbound_mobile= ? , outbound_country_code=852 , country_code=852 WHERE user_id = ?` _, err = o.Raw(sql, item.Mobile, item.UserId).Exec() } else if item.CountryCode == "" && len(item.Mobile) == 9 { sql := ` UPDATE wx_user SET outbound_mobile= ? , outbound_country_code=886 , country_code=886 WHERE user_id = ?` _, err = o.Raw(sql, item.Mobile, item.UserId).Exec() } else if item.CountryCode == "" && len(item.Mobile) == 10 { sql := ` UPDATE wx_user SET outbound_mobile= ? , outbound_country_code=1 , country_code=1 WHERE user_id = ?` _, err = o.Raw(sql, item.Mobile, item.UserId).Exec() } else { sql := ` UPDATE wx_user SET outbound_mobile= ? , outbound_country_code=? WHERE user_id = ?` _, err = o.Raw(sql, item.Mobile, item.CountryCode, item.UserId).Exec() } return } // 判断公司下用户名称是否存在 func GetUserCountByName(companyId int, name string) (count int, err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `SELECT COUNT(1) AS count FROM wx_user WHERE company_id = ? AND real_name = ?` err = o.Raw(sql, companyId, name).QueryRow(&count) return } // 判断这个用户是否被设置消息提醒 func GetUserRemind(uid int) (count int, err error) { o := orm.NewOrm() sql := `SELECT COUNT(1) AS count FROM cygx_user_remind WHERE user_id = ? ` err = o.Raw(sql, uid).QueryRow(&count) return } // 判断公司下用户名称是否存在 func GetUserCountByThirdName(companyId int, name string) (count int, err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `SELECT COUNT(1) AS count FROM wx_user WHERE company_id = ? AND tripartite_code = ?` err = o.Raw(sql, companyId, name).QueryRow(&count) return } // 获取公司下一共有多少用户 func GetUserCountByCompanyId(companyId int) (count int, err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `SELECT COUNT(1) AS count FROM wx_user WHERE company_id = ? ` err = o.Raw(sql, companyId).QueryRow(&count) return } func UpdateUserMobile(uid int, mobile string) (err error) { o := orm.NewOrmUsingDB("weekly_report") sql := ` UPDATE wx_user SET mobile=? WHERE user_id=? ` _, err = o.Raw(sql, mobile, uid).Exec() return } // 获取公司下用户详情详情详情 func GetUserByName(companyId int, name string) (item *WxUser, err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `SELECT * FROM wx_user WHERE company_id = ? AND real_name = ? ` err = o.Raw(sql, companyId, name).QueryRow(&item) return } // 获取公司下用户详情详情详情 func GetUserByThirdName(companyId int, name string) (item *WxUser, err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `SELECT * FROM wx_user WHERE company_id = ? AND tripartite_code = ? ` err = o.Raw(sql, companyId, name).QueryRow(&item) return } // 获取所有注册的权益用户 //func GetUserRegisterList() (items []*WxUser, err error) { // o := orm.NewOrmUsingDB("weekly_report") // sql := `SELECT // u.user_id, // u.company_id, // u.mobile, // u.email, // u.real_name, // u.is_register, // u.is_maker, // u.register_time // FROM // wx_user AS u // INNER JOIN company AS c ON c.company_id = u.company_id // INNER JOIN company_product AS cp ON cp.company_id = c.company_id // WHERE // u.company_id IN ( // SELECT // a.company_id // FROM // company AS a // INNER JOIN company_product AS b ON a.company_id = b.company_id // WHERE // a.enabled = 1 // AND b.STATUS IN ( '正式', '试用', '冻结' ) // AND cp.product_id = 2 // ) // AND cp.product_id = 2 // GROUP BY // u.user_id ` // _, err = o.Raw(sql).QueryRows(&items) // //AND u.register_time IS NOT NULL OR u.report_last_view_time <>'' 统计阅读时间与注册时间不为空的用户 // return //} // 获取所有注册的权益用户 func GetUserRegisterList() (items []*WxUser, err error) { o := orm.NewOrm() sql := `SELECT * FROM cygx_page_history_record WHERE user_id > 0 AND company_id > 0 AND create_time > DATE_SUB( DATE( NOW()), INTERVAL 1 DAY ) OR mobile = 18767183922 GROUP BY user_id ` _, err = o.Raw(sql).QueryRows(&items) return } type CygxCompanyUser struct { Labels string `description:"标签，用英文,隔开"` } type UserLabel struct { Label string `description:"标签 多个用 , 隔开"` } // 阅读记录 type ArticlePvCountResp struct { ArticleId int `description:"文章ID"` Pv int `description:"Pv"` } func GetArticlePvCount(mobile, email, dateTime string) (item []*ArticlePvCountResp, err error) { o := orm.NewOrm() sql := `SELECT article_id, COUNT(article_id) as pv FROM cygx_article_history_record_newpv as h WHERE mobile = ? AND email = ? AND create_time >= ? GROUP BY article_id ` _, err = o.Raw(sql, mobile, email, dateTime).QueryRows(&item) return } //产业名称 type ArticleIndustryNameResp struct { ArticleId int `description:"文章ID"` IndustryName string `description:"产业名称"` SubjectName string `description:"标的名称"` } func GetArticleGroupyIndustryName(articleIds string) (item []*ArticleIndustryNameResp, err error) { o := orm.NewOrm() sql := `SELECT art.article_id, ( SELECT GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' ) FROM cygx_industrial_subject AS s WHERE s.industrial_subject_id IN ( SELECT industrial_subject_id FROM cygx_industrial_article_group_subject AS sg WHERE sg.article_id = art.article_id ) ) AS subject_name, ( SELECT GROUP_CONCAT( DISTINCT man.industry_name SEPARATOR '/' ) FROM cygx_industrial_management AS man WHERE man.industrial_management_id IN ( SELECT industrial_management_id FROM cygx_industrial_article_group_management AS man_g WHERE man_g.article_id = art.article_id ) ) AS industry_name FROM cygx_article_history_record_newpv AS r INNER JOIN cygx_article AS art ON art.article_id = r.article_id LEFT JOIN cygx_report_mapping AS re ON re.category_id = art.category_id_two LEFT JOIN cygx_industrial_article_group_management AS man ON man.article_id = art.article_id WHERE art.article_id IN ( ` + articleIds + `) GROUP BY art.article_id ` _, err = o.Raw(sql).QueryRows(&item) return } func GetCygxArticleCollectId(uid int, dateTime string) (articleIds string, err error) { sql := `SELECT GROUP_CONCAT( DISTINCT article_id SEPARATOR ',' ) AS permission FROM cygx_article_collect WHERE user_id = ? AND create_time >= ? ` o := orm.NewOrm() err = o.Raw(sql, uid, dateTime).QueryRow(&articleIds) return } // 活动标签记录 type ActivityLabelCountResp struct { Label string `description:"标签"` Pv int `description:"Pv"` } func GetActivitySignCount(mobile, dateTime string) (item []*ActivityLabelCountResp, err error) { o := orm.NewOrm() sql := `SELECT label , COUNT( label ) AS pv FROM cygx_activity WHERE activity_id IN ( SELECT activity_id FROM cygx_activity_signup WHERE mobile = ? ) AND label != '' AND activity_time >= ? AND is_submit_meeting = 1 GROUP BY label` _, err = o.Raw(sql, mobile, dateTime).QueryRows(&item) return } type CygxUserInteractionNumDetail struct { UserId int64 `orm:"column(user_id);pk"` Mobile string `description:"手机号"` Email string `description:"邮箱"` CompanyId int `description:"公司id"` CompanyName string `description:"公司名称"` RealName string `description:"姓名"` CreatedTime string `description:"创建时间"` IsMaker int `description:"是否决策人，1:是，0:否"` IsRegister bool `description:"是否注册,true:已注册，false:未注册"` Status string `description:"客户状态"` RegisterTime string `description:"注册时间"` SellerName string `description:"销售名称"` InteractionNum int `description:"互动量"` Labels string `description:"标签，用英文,隔开"` ActivityLabel string `description:"活动标签，用英文,隔开"` IsShowSee bool `description:"是否展示查看"` IsShowSeeNum int `description:"是否展示查看"` HistoryNum int `description:"报告阅读"` CountNum int `description:"报告收藏"` ChartCountNum int `description:"图表收藏"` IndustryFllowNum int `description:"产业关注"` DepartmentFollowNum int `description:"作者关注"` KeyWordNum int `description:"搜索关键词"` OnLineNum int `description:"线上互动活动"` OfficeNum int `description:"线下互动活动"` TripNum int `description:"专项产业调研数量"` RoadshowVideoNum int `description:"产业视频播放量"` ActivityVideoNum int `description:"活动视频播放量"` ActivityVoiceNum int `description:"活动音频播放量"` TagNum int `description:"标签点击次数"` PackageType int `description:"套餐类型，0：无，1：大套餐，2：小套餐"` TryStage int `description:"试用客户子标签：0全部、1未分类、2 推进、3 跟踪、4 预备"` } // GetWxUserListByUserIds 根据用户ID集合获取用户 func GetWxUserListByUserIds(userIds string) (list []*WxUserItem, err error) { o := orm.NewOrmUsingDB("weekly_report") sql := ` SELECT u.*, c.company_name FROM wx_user AS u INNER JOIN company AS c ON c.company_id = u.company_id WHERE user_id IN (` + userIds + `) ` _, err = o.Raw(sql).QueryRows(&list) return } // GetWxUserByMobiles 根据用户手机号获取用户详情 func GetWxUserByMobiles(mobiles []string) (items []*WxUser, err error) { lenmobiles := len(mobiles) if lenmobiles == 0 { return } sql := `SELECT* FROM wx_user WHERE mobile in (` + utils.GetOrmInReplace(lenmobiles) + `) ` o := orm.NewOrmUsingDB("weekly_report") _, err = o.Raw(sql, mobiles).QueryRows(&items) return } // GetWxUserByMobiles 根据用户手机号获取用户详情 func GetWxUserByOutboundMobiles(mobiles []string) (items []*WxUser, err error) { lenmobiles := len(mobiles) if lenmobiles == 0 { return } sql := `SELECT* FROM wx_user WHERE outbound_mobile in (` + utils.GetOrmInReplace(lenmobiles) + `) ` o := orm.NewOrmUsingDB("weekly_report") _, err = o.Raw(sql, mobiles).QueryRows(&items) return } func UserSubscribe(subscribeTime string, userId int) (err error) { o := orm.NewOrmUsingDB("weekly_report") sql := `UPDATE wx_user SET cygx_subscribe=1,cygx_subscribe_time=? WHERE user_id = ? ` _, err = o.Raw(sql, subscribeTime, userId).Exec() return } func GetWxUserOutboundMobiles(mobiles []string) (item []*WxUserOutboundMobile, err error) { lenmobiles := len(mobiles) if lenmobiles == 0 { return } o := orm.NewOrmUsingDB("weekly_report") sql := `SELECT u.real_name,u.mobile,u.outbound_mobile,u.company_id,p.company_name ,GROUP_CONCAT( DISTINCT p.seller_name SEPARATOR '/' ) AS seller_name FROM wx_user as u INNER JOIN company_product AS p ON p.company_id = u.company_id WHERE outbound_mobile IN(` + utils.GetOrmInReplace(lenmobiles) + `) OR mobile IN (` + utils.GetOrmInReplace(lenmobiles) + `) GROUP BY u.user_id` _, err = o.Raw(sql, mobiles, mobiles).QueryRows(&item) return } // GetWxUserListByUserIdsArr 根据用户ID集合获取用户 func GetWxUserListByUserIdsArr(userIds []int) (list []*WxUserItem, err error) { lenArr := len(userIds) if lenArr == 0 { return } o := orm.NewOrmUsingDB("weekly_report") sql := ` SELECT u.*, c.company_name FROM wx_user AS u INNER JOIN company AS c ON c.company_id = u.company_id WHERE user_id IN (` + utils.GetOrmInReplace(lenArr) + `) ` _, err = o.Raw(sql, userIds).QueryRows(&list) return }