package company import ( "fmt" "github.com/beego/beego/v2/client/orm" "github.com/rdlucklib/rdluck_tools/paging" "hongze/hz_crm_api/utils" "time" ) // 新增客户请求参数 type AddUserReq struct { RealName string `description:"姓名"` Sex int `description:"用户性别，1为男性，2为女性"` Source string `description:"来源,新增客户时添加：add_custom;领取客户时添加：pick_custom;普通领取联系人时添加：add_user"` RegionType string `description:"区域，枚举值：国内、海外"` CountryCode string `description:"区号，86、852、886等"` MobileOne string `description:"手机号1"` MobileTwo string `description:"手机号2"` Email string `description:"邮箱"` Position string `description:"职位"` IsMaker int `description:"是否决策人，1:是，0:否"` BusinessCardUrl string `description:"名片地址"` CompanyId int `description:"客户id"` DepartmentName string `description:"联系人部门"` CompanyName string `description:"客户名称（公司名称）"` } type CompanyUser struct { UserId int64 `orm:"column(user_id);pk"` CountryCode string `description:"区号，86、852、886等"` Mobile string `description:"手机号"` Email string `description:"邮箱"` CompanyId int `description:"公司id"` CompanyName string `description:"公司名称"` RealName string `description:"姓名"` CreatedTime string `description:"创建时间"` MobileTwo string `description:"备用手机号"` BusinessCardUrl string `description:"名片地址"` IsMaker int `description:"是否决策人，1:是，0:否"` Position string `description:"职位"` Sex int `description:"普通用户性别，1为男性，2为女性"` IsRegister bool `description:"是否注册,true:已注册，false:未注册"` OpenId string `json:"-"` DepartmentName string `description:"联系人部门"` SellerRealName string `description:"销售"` Status string `description:"客户状态"` HasMove bool `description:"true:可移动，false:显示知道了"` ViewTotal int `description:"累计阅读次数"` LastViewTime time.Time `json:"-" description:"最后一次阅读时间"` LastViewTimeStr string `description:"最后一次阅读时间"` ApplyMethod int `description:"0:未申请，1:已付费客户申请试用，2:非客户申请试用"` RegisterTime string `description:"注册时间"` RegisterPlatform int `description:"注册平台,1:微信端,2:网页端"` SellerId int `description:"销售id"` SellerName string `description:"销售名称"` GroupId int `description:"所属销售分组id"` DepartmentId int `description:"所属销售分组id"` IsShared bool `description:"是否共享联系人"` ProductIds string `description:"商品权限集合，用英文,隔开"` IsChartPermissionSetting int `description:"是否设置过图表权限, 0-未设置，1-已设置，2-无需设置"` YbProductViewTotal int `description:"研报分产品阅读统计"` IsSubscribe int `description:"是否关注了微信公众号: 0-未关注; 1-已关注"` IsFollow int `description:"是否特别关注: 0-未关注; 1-已关注"` IsSubscribeHzyj int `description:"是否关注了弘则研究微信公众号: 0-未关注; 1-已关注"` IsSubscribeCygx int `description:"是否关注了查研观向微信公众号: 0-未关注; 1-已关注"` FiccViewTotal int `description:"ficc报告的阅读次数" json:"-"` FiccLastViewTime time.Time `description:"ficc报告最近一次阅读时间" json:"-"` RaiViewTotal int `description:"权益报告的阅读次数" json:"-"` RaiLastViewTime time.Time `description:"权益报告的最近一次阅读时间" json:"-"` NotRead bool `description:"是否七天内未阅读"` MfyxStartDate string `description:"买方研选权限开始日期"` MfyxEndDate string `description:"买方研选权限结束日期"` MfyxStatus string `description:"买方研选状态'试用','未开通'"` MfyxInteractionNum int `description:"互动量"` MfyxIsBinding bool `description:"买方研选是否绑定"` MfyxBindingTime string `description:"买方研选绑定时间"` HaveMoveButton bool `description:"是否移动过"` } type CompanyUserListResp struct { Paging *paging.PagingItem List []*CompanyUser } // 联系人数量总数返回数据结构体 type CompanyUserTotal struct { Total int `description:"联系人总数"` } func GetCompanyUserListCount(condition string, pars []interface{}, companyId int) (count int, err error) { o := orm.NewOrm() sql := ` SELECT COUNT(1) AS count FROM wx_user AS a WHERE company_id=? ` if condition != "" { sql += condition } err = o.Raw(sql, companyId, pars).QueryRow(&count) return } func GetCompanyUserList(condition string, pars []interface{}, companyId, startSize, pageSize int) (items []*CompanyUser, err error) { o := orm.NewOrm() sql := ` SELECT a.*,b.company_name, (SELECT count(1) FROM user_view_history AS uvh WHERE uvh.user_id=a.user_id GROUP BY a.user_id) AS view_total, (SELECT max(uvh.created_time) FROM user_view_history AS uvh WHERE uvh.user_id=a.user_id GROUP BY a.user_id) AS last_view_time FROM wx_user AS a INNER JOIN company AS b ON a.company_id=b.company_id WHERE a.company_id=? ` if condition != "" { sql += condition } sql += `ORDER BY a.last_updated_time DESC LIMIT ?,? ` _, err = o.Raw(sql, companyId, pars, startSize, pageSize).QueryRows(&items) return } // 获取该用户下联系人数量（跟销售绑定，2021年03月09日16:35:11） func GetCompanyUserListCountV2(condition string, pars []interface{}, companyId int) (count int, err error) { o := orm.NewOrm() tmpSql := `SELECT a.*, c.subscribe AS is_subscribe FROM wx_user AS a LEFT JOIN user_seller_relation AS b ON a.user_id = b.user_id LEFT JOIN (SELECT * FROM user_record WHERE create_platform = 1 GROUP BY user_id) AS c ON a.user_id = c.user_id WHERE a.company_id = ? ` if condition != "" { tmpSql += condition } sql := `SELECT COUNT(1) AS count FROM (` + tmpSql + ` group by a.user_id) AS c ` err = o.Raw(sql, companyId, pars).QueryRow(&count) return } // 获取该用户下联系人切片列表（跟销售绑定，2021年03月09日16:35:11） func GetCompanyUserListV2(condition string, pars []interface{}, companyId, startSize, pageSize int) (items []*CompanyUser, err error) { o := orm.NewOrm() sql := `SELECT a.*, a.cygx_subscribe as is_subscribe_cygx, c.subscribe AS is_subscribe_hzyj FROM wx_user AS a LEFT JOIN user_seller_relation AS b ON a.user_id = b.user_id LEFT JOIN (SELECT * FROM user_record WHERE create_platform = 1 GROUP BY user_id) AS c ON a.user_id = c.user_id WHERE a.company_id = ? ` if condition != "" { sql += condition } sql += ` group by a.user_id ORDER BY CASE WHEN is_follow = 1 AND (report_last_view_time < NOW() - INTERVAL 7 DAY or report_last_view_time is NULL ) THEN 0 ELSE 1 END,a.report_last_view_time desc,a.last_updated_time DESC LIMIT ?,? ` //sql += ` group by a.user_id ORDER BY CASE WHEN is_follow = 1 AND (report_last_view_time < NOW() - INTERVAL 7 DAY or report_last_view_time is NULL ) THEN 0 ELSE 1 END,a.is_register desc,a.report_last_view_time ASC,a.last_updated_time ASC LIMIT ?,? ` _, err = o.Raw(sql, companyId, pars, startSize, pageSize).QueryRows(&items) return } // GetCompanyUserListById 获取该用户下联系人切片列表 func GetCompanyUserListById(companyId int) (items []*CompanyUser, err error) { o := orm.NewOrm() sql := ` SELECT a.* FROM wx_user AS a WHERE a.company_id=? ` _, err = o.Raw(sql, companyId).QueryRows(&items) return } // 删除客户请求参数 type DeleteUserReq struct { UserId int `orm:"column(user_id);pk"` } func DeleteCompanyUser(userId int) (err error) { o := orm.NewOrm() sql := ` DELETE FROM wx_user WHERE user_id=? ` _, err = o.Raw(sql, userId).Exec() return } // 新增客户请求参数 type EditUserReq struct { UserId int `orm:"column(user_id);pk"` RealName string `description:"姓名"` Sex int `description:"用户性别，1为男性，2为女性"` CountryCode string `description:"区号，86、852、886等"` MobileOne string `description:"手机号1"` MobileTwo string `description:"手机号2"` Email string `description:"邮箱"` Position string `description:"职位"` IsMaker int `description:"是否决策人，1:是，0:否"` BusinessCardUrl string `description:"名片地址"` CompanyId int `description:"客户id"` DepartmentName string `description:"联系人部门"` } func EditCompanyUser(item *EditUserReq) (err error) { o := orm.NewOrm() sql := ` UPDATE wx_user SET real_name=?, mobile= ?, email = ?, sex = ?, last_updated_time = NOW(), position = ?, is_maker = ?, business_card_url = ?, mobile_two= ?, department_name=?, company_id=?,country_code=? WHERE user_id = ? ` _, err = o.Raw(sql, item.RealName, item.MobileOne, item.Email, item.Sex, item.Position, item.IsMaker, item.BusinessCardUrl, item.MobileTwo, item.DepartmentName, item.CompanyId, item.CountryCode, item.UserId).Exec() return } // 修改联系人的联系方式 func EditCompanyUserContact(userId int64, mobileOne, mobileTwo, email, businessCardUrl string) (err error) { o := orm.NewOrm() sql := ` UPDATE wx_user SET mobile= ?, mobile_two= ?, email = ?, business_card_url = ?, last_updated_time = NOW() WHERE user_id = ? ` _, err = o.Raw(sql, mobileOne, mobileTwo, email, businessCardUrl, userId).Exec() return } func GetCompanyIdByKeyWord(keyWord string) (company_id string, err error) { o := orm.NewOrm() sql := ` SELECT GROUP_CONCAT(DISTINCT company_id) AS company_id FROM wx_user WHERE mobile LIKE '%` + keyWord + `%' OR email LIKE '%` + keyWord + `%'` err = o.Raw(sql).QueryRow(&company_id) return } func GetCompanyIdByKeyWordAll(keyWord string) (company_id string, err error) { o := orm.NewOrm() sql := ` SELECT GROUP_CONCAT(DISTINCT company_id) AS company_id FROM wx_user WHERE mobile LIKE '%` + keyWord + `%' OR email LIKE '%` + keyWord + `%' ` err = o.Raw(sql).QueryRow(&company_id) return } func GetCompanyUserCount(companyId int) (count int, err error) { o := orm.NewOrm() sql := ` SELECT COUNT(1) AS count FROM wx_user WHERE company_id =? ` err = o.Raw(sql, companyId).QueryRow(&count) return } func CheckCompanyUserCountByMobileTwo(mobileTwo string) (item *CompanyUser, err error) { o := orm.NewOrm() sql := ` SELECT a.*,b.company_name,GROUP_CONCAT(DISTINCT d.real_name SEPARATOR '/') AS seller_real_name,GROUP_CONCAT(DISTINCT c.status SEPARATOR '/') AS status FROM wx_user AS a LEFT JOIN company AS b ON a.company_id=b.company_id LEFT JOIN company_product AS c ON b.company_id=c.company_id LEFT JOIN admin AS d ON c.seller_id=d.admin_id WHERE a.mobile_two =? GROUP BY a.company_id ` err = o.Raw(sql, mobileTwo).QueryRow(&item) return } func CheckCompanyUserCountByEmail(email string) (item *CompanyUser, err error) { o := orm.NewOrm() sql := ` SELECT a.*,b.company_name,GROUP_CONCAT(DISTINCT d.real_name SEPARATOR '/') AS seller_real_name,GROUP_CONCAT(DISTINCT c.status SEPARATOR '/') AS status FROM wx_user AS a LEFT JOIN company AS b ON a.company_id=b.company_id LEFT JOIN company_product AS c ON b.company_id=c.company_id LEFT JOIN admin AS d ON c.seller_id=d.admin_id WHERE a.email =? GROUP BY a.company_id ` err = o.Raw(sql, email).QueryRow(&item) return } func GetUserCountByMobile(mobile string) (item *CompanyUser, err error) { o := orm.NewOrm() sql := ` SELECT a.*,b.company_name,GROUP_CONCAT(DISTINCT d.real_name SEPARATOR '/') AS seller_real_name,GROUP_CONCAT(DISTINCT c.status SEPARATOR '/') AS status FROM wx_user AS a LEFT JOIN company AS b ON a.company_id=b.company_id LEFT JOIN company_product AS c ON b.company_id=c.company_id LEFT JOIN admin AS d ON c.seller_id=d.admin_id WHERE a.mobile=? GROUP BY a.company_id ` err = o.Raw(sql, mobile).QueryRow(&item) return } // type ViewReportList struct { ResearchReportName string `description:"报告标题"` ReportType string `description:"报告类型 'day 晨报'、'week 周报'、'twoweek 双周报'、'month 月报'、'rddp 日度点评'、'cygx 查研观向'、'advisory 每日商品聚焦'"` CreatedTime string `description:"创建时间"` TxtType string `description:"类型 ficc:ficc 、 rights：权益"` MatchTypeName string `description:"匹配类型"` StopTime string `description:"停留时间"` } type ViewReportListResp struct { Total int `description:"数量"` List []*ViewReportList } func ModifyCompanyUserCompanyId(userId, companyId int) (err error) { o := orm.NewOrm() sql := ` UPDATE wx_user SET company_id=? WHERE user_id=? ` _, err = o.Raw(sql, companyId, userId).Exec() return } func GetCompanyUserBusinessCardCount(companyId int) (count int, err error) { o := orm.NewOrm() sql := ` SELECT COUNT(1) AS count FROM wx_user WHERE company_id =? AND business_card_url<>'' ` err = o.Raw(sql, companyId).QueryRow(&count) return } // 标记用户 func DealCompanyUser(userId int) (err error) { o := orm.NewOrm() sql := ` UPDATE wx_user SET is_deal=1 WHERE user_id=? ` _, err = o.Raw(sql, userId).Exec() return } type CompanyActivityUser struct { UserId int64 Mobile string `description:"手机号"` Email string `description:"邮箱"` CompanyId int `description:"公司id"` CompanyName string `description:"公司名称"` RealName string `description:"姓名"` } // GetNoPotentialCompanyUserList 获取不是潜在客户的联系人列表 func GetNoPotentialCompanyUserList(condition string, pars []interface{}) (items []*CompanyActivityUser, err error) { o := orm.NewOrm() sql := ` SELECT a.user_id,a.mobile,a.email,a.company_id,a.real_name,b.company_name FROM wx_user AS a INNER JOIN company AS b ON a.company_id=b.company_id WHERE a.company_id>1 ` if condition != "" { sql += condition } sql += `ORDER BY a.last_updated_time DESC ` _, err = o.Raw(sql, pars).QueryRows(&items) return } // GetFiccCompanyUserByUserIds 批量查询用户的购买ficc的产品详情 func GetFiccCompanyUserByUserIds(userIds string) (list []*CompanyUser, err error) { o := orm.NewOrm() sql := ` SELECT a.user_id, a.real_name, if(b.company_id=1,"",b.company_name) as company_name, c.status, b.company_id, c.seller_name FROM wx_user AS a LEFT JOIN company AS b ON a.company_id=b.company_id LEFT JOIN company_product AS c ON a.company_id=c.company_id and c.product_id=1 WHERE a.user_id in (` + userIds + `)` _, err = o.Raw(sql).QueryRows(&list) return } // UserImportMatchResp 联系人名单导入匹配响应体 type UserImportMatchResp struct { List []*UserImportMatch `description:"名单列表"` Code string `description:"下载名单的code"` } // UserImportMatch 联系人名单导入匹配 type UserImportMatch struct { UserName string `description:"姓名"` CountryCode string `description:"国际(区号)"` Mobile string `description:"手机号"` CompanyName string `description:"导入的客户名称"` SysCompanyName string `description:"系统匹配的客户名称"` FiccStatus string `descripiton:"FICC状态"` FiccSeller string `description:"FICC销售"` RaiStatus string `description:"权益状态"` RaiSeller string `description:"权益销售"` } // GetCompanyUsersByCondition 获取联系人列表 func GetCompanyUsersByCondition(condition string, pars []interface{}) (list []*CompanyUser, err error) { sql := `SELECT * FROM wx_user WHERE 1 = 1 ` if condition != `` { sql += condition } _, err = orm.NewOrm().Raw(sql, pars).QueryRows(&list) return } type ViewReportListV2 struct { Id int `description:"阅读记录id"` UserId int `description:"用户id"` Mobile string `description:"手机号"` Email string `description:"邮箱"` RealName string `description:"真实姓名"` CompanyName string `description:"客户名称"` ResearchReportName string `description:"报告标题"` ReportType int8 `description:"来源：1：rddp的报告；2：weekly_report的PHP报告；3：weekly_report商品的报告（应该是作废了）；4：察研观向的报告""` CreatedTime string `description:"创建时间"` TxtType string `description:"类型 ficc:ficc 、 rights：权益"` MatchTypeName string `description:"匹配类型"` StopTime int `description:"停留时间"` ReportId int `description:"报告id"` ReportChapterId int `description:"报告章节id"` } func GetViewReportListByDate(startDate, endDate string) (items []*ViewReportListV2, err error) { dataName := "" sql := `` if utils.RunMode == "debug" { dataName = "test_v2_hongze_rddp" } else { dataName = "hongze_rddp" } ficcSql := `SELECT uvh.user_id,uvh.mobile,uvh.email,uvh.view_history_id AS id,uvh.real_name,uvh.company_name, uvh.research_report_id as report_id, uvh.research_report_type_id as report_chapter_id, rr.research_report_name, "2" AS report_type, 'ficc' AS txt_type, '--' AS match_type_name, '0' AS stop_time, uvh.created_time AS created_time FROM user_view_history uvh LEFT JOIN research_report rr ON rr.research_report_id = uvh.research_report_id WHERE uvh.created_time >= ? AND uvh.created_time< ? UNION ALL SELECT rvr.user_id,rvr.mobile,rvr.email,rvr.id,rvr.real_name,rvr.company_name, rvr.report_id, rvr.report_chapter_id, r.title AS research_report_name, '1' AS report_type, 'ficc' AS txt_type, r.classify_name_first AS match_type_name, '0' AS stop_time, rvr.create_time AS created_time FROM %s.report_view_record rvr LEFT JOIN %s.report r ON r.id = rvr.report_id WHERE rvr.create_time >= ? AND rvr.create_time< ? UNION ALL SELECT auc.user_id,auc.mobile,auc.email,auc.id,auc.real_name,auc.company_name, auc.chart_permission_id as report_id, '0' AS report_chapter_id, cha.permission_name AS research_report_name, '3' AS report_type, 'ficc' AS txt_type, cha.classify_name AS match_type_name, '0' AS stop_time, auc.create_time AS created_time FROM advisory_user_chart_article_record auc LEFT JOIN chart_permission cha ON cha.chart_permission_id = auc.chart_permission_id WHERE auc.create_time >= ? AND auc.create_time< ?` rightsSql := `SELECT h.user_id,h.mobile,h.email,h.id,h.company_name,'' AS real_name, h.article_id AS report_id, '0' AS report_chapter_id, art.title AS research_report_name, '4' AS report_type, 'rights' AS txt_type, art.match_type_name, h.stop_time, h.create_time AS created_time FROM cygx_article_history_record_newpv h INNER JOIN cygx_article art ON art.article_id = h.article_id WHERE h.create_time >= ? AND h.create_time< ? ` sql = ` SELECT * FROM ( ` + ficcSql + " UNION ALL " + rightsSql + ` )AS t ORDER BY t.created_time asc` o := orm.NewOrm() sql = fmt.Sprintf(sql, dataName, dataName) _, err = o.Raw(sql, startDate, endDate, startDate, endDate, startDate, endDate, startDate, endDate).QueryRows(&items) return } // CompanyViewResp // @Description: 客户报告阅读统计 type CompanyViewResp struct { UserId int `description:"联系人ID"` UserName string `description:"联系人姓名"` Mobile string `description:"手机号"` Email string `description:"邮箱地址"` ViewTotal int `description:"累计点击量"` LastViewTime string `description:"创建时间"` } // CompanyViewPageListResp 客户-点击量分页列表响应体 type CompanyViewPageListResp struct { List []*CompanyViewResp Paging *paging.PagingItem `description:"分页数据"` } func GetCompanyUserListByReportCount(companyId int) (count int, err error) { o := orm.NewOrm() sql := ` SELECT COUNT(1) AS count FROM wx_user WHERE company_id =? AND ficc_view_total>0 ` err = o.Raw(sql, companyId).QueryRow(&count) return } // GetCompanyUserListByReport // @Description: 根据报告阅读情况获取联系人列表 // @author: Roc // @datetime 2024-02-01 16:07:59 // @param companyId int // @param startSize int // @param pageSize int // @param order string // @return items []*CompanyUser // @return err error func GetCompanyUserListByReport(companyId, startSize, pageSize int, order string) (items []*CompanyUser, err error) { o := orm.NewOrm() sql := `SELECT * FROM wx_user WHERE company_id = ? AND ficc_view_total>0 ` if order != "" { sql += order } else { sql += ` ORDER BY create_time DESC` } sql += ` LIMIT ?,?` _, err = o.Raw(sql, companyId, startSize, pageSize).QueryRows(&items) return }