hongze_cygx/models/user.go

package models

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"hongze/hongze_cygx/utils"
	"time"
)

type UserDetail struct {
	Headimgurl          string `description:"用户头像，最后一个数值代表正方形头像大小（有0、46、64、96、132数值可选，0代表640*640正方形头像），用户没有头像时该项为空"`
	Mobile              string `description:"手机号码"`
	Email               string `description:"邮箱"`
	NickName            string `description:"用户昵称"`
	RealName            string `description:"用户实际名称"`
	CompanyName         string `description:"公司名称"`
	PermissionName      string `description:"拥有权限分类,多个用英文逗号分隔"`
	HasPermission       int    `description:"1:无该行业权限，不存在权益客户下,2：潜在客户，未提交过申请，3：潜在客户,已提交过申请"`
	SellerMobile        string `description:"销售手机号"`
	SellerName          string `description:"销售名称"`
	Note                string `json:"-" description:"申请提交时，公司名称"`
	CountryCode         string `description:"区号"`
	OutboundMobile      string `description:"外呼手机号"`
	OutboundCountryCode string `description:"外呼手机号区号"`
	ConNum              int    `description:"收藏"`
	HistoryNum          int    `description:"足迹"`
	ScheduleNum         int    `description:"活动日程"`
}

func GetUserDetailByUserId(userId int) (item *UserDetail, err error) {
	o := orm.NewOrm()
	sql := `SELECT *,
	( SELECT SUM(count) AS count FROM (
			SELECT COUNT(1) AS count FROM cygx_article_collect AS a 
			INNER JOIN cygx_article as art ON art.article_id = a.article_id 
			WHERE a.user_id=? AND art.publish_status = 1  
			UNION ALL
			SELECT COUNT(1) AS count FROM cygx_yanxuan_special_collect AS a
			INNER JOIN cygx_yanxuan_special as b ON b.id = a.yanxuan_special_id 
			WHERE a.user_id=? AND b.status = 3) AS c  ) AS con_num,
	(SELECT	COUNT( 1 ) AS count FROM
		( SELECT count(*) FROM cygx_article_history_record AS a WHERE a.user_id = ?  GROUP BY a.article_id ) b ) AS history_num ,
	( SELECT COUNT( 1 ) AS count FROM	cygx_my_schedule AS a INNER JOIN cygx_activity AS art ON art.activity_id = a.activity_id WHERE
		a.user_id = ? AND art.publish_status = 1 AND art.active_state != 3 ) AS schedule_num
	FROM wx_user WHERE user_id = ? `
	err = o.Raw(sql, userId, userId, userId, userId, userId).QueryRow(&item)
	return
}

type UserPermission struct {
	CompanyName         string `description:"公司名称"`
	ChartPermissionName string `description:"权限"`
}

type LoginReq struct {
	LoginType     int    `description:"登录方式:1:微信手机，2:邮箱，3：自定义手机登录"`
	Mobile        string `description:"手机号"`
	Email         string `description:"邮箱"`
	VCode         string `description:"验证码"`
	CountryCode   string `description:"区号"`
	ShareUserCode string `description:"分享人的分享码"`
}

func PcBindMobile(unionId, mobile string, userId, loginType int) (wxUserId int, err error) {
	//loginType  登录方式:1:手机，2:邮箱
	sql := ``
	if loginType == 1 {
		sql = `SELECT * FROM wx_user WHERE mobile = ? `
	} else {
		sql = "SELECT * FROM wx_user WHERE email = ? "
	}
	user := new(WxUser)
	o := orm.NewOrm()
	err = o.Raw(sql, mobile).QueryRow(&user)

	if err != nil && err.Error() != utils.ErrNoRow() {
		return
	}

	if user == nil || (err != nil && err.Error() == utils.ErrNoRow()) {
		msql := ``
		if loginType == 1 {
			msql = "UPDATE wx_user SET mobile = ?,bind_account = ? WHERE union_id = ? "
		} else {
			msql = "UPDATE wx_user SET email = ?,bind_account = ? WHERE union_id = ?  "
		}
		_, err = o.Raw(msql, mobile, mobile, unionId).Exec()
		wxUserId = userId
	} else {
		if user.UnionId == "" {
			sql = `SELECT * FROM wx_user WHERE union_id = ? `
			userInfo := new(WxUser)
			o := orm.NewOrm()
			err = o.Raw(sql, unionId).QueryRow(&userInfo)
			if err != nil {
				return
			}
			var maxRegisterTime time.Time
			if user.RegisterTime.Before(userInfo.RegisterTime) {
				maxRegisterTime = user.RegisterTime
			} else {
				maxRegisterTime = userInfo.RegisterTime
			}
			wxUserId = user.UserId
			dsql := ` DELETE FROM wx_user WHERE union_id = ? `
			_, err = o.Raw(dsql, unionId).Exec()
			if err != nil {
				return wxUserId, err
			}
			msql := ` UPDATE wx_user SET union_id=?,register_time=?,province=?,city=?,country=?,headimgurl=?,unionid=?,sex=?  WHERE user_id = ?  `
			_, err = o.Raw(msql, unionId, maxRegisterTime, userInfo.Province, userInfo.City, userInfo.Country, userInfo.Headimgurl, unionId, userInfo.Sex, user.UserId).Exec()
			wxUserId = user.UserId
		} else {
			sql = `SELECT * FROM wx_user WHERE user_id = ? `
			userInfo := new(WxUser)
			o := orm.NewOrm()
			err = o.Raw(sql, userId).QueryRow(&userInfo)
			if err != nil && err.Error() != utils.ErrNoRow() {
				return
			}
			if user.UserId != userId {
				dsql := ` DELETE FROM wx_user WHERE user_id = ? `
				_, err = o.Raw(dsql, userId).Exec()
				if err != nil {
					return user.UserId, err
				}
			}
			msql := ` UPDATE wx_user SET union_id=?,province=?,city=?,country=?,headimgurl=?,unionid=?,sex=?  WHERE user_id = ?  `
			_, err = o.Raw(msql, unionId, userInfo.Province, userInfo.City, userInfo.Country, userInfo.Headimgurl, unionId, userInfo.Sex, user.UserId).Exec()
			wxUserId = userId
		}
	}
	return
}

type LoginResp struct {
	UserId        int    `description:"用户id"`
	Authorization string `description:"Token"`
	Headimgurl    string `description:"用户头像"`
	Mobile        string `description:"手机号"`
	Email         string `description:"邮箱"`
	CompanyName   string `description:"客户名称"`
	Status        string `description:"状态"`
	EndDate       string `description:"到期日期"`
	ProductName   string `description:"客户类型名称"`
	IsPotential   bool   `description:"是否是潜在"`
}

type CheckStatusResp struct {
	IsBind         bool   `description:"true:需要绑定手机号或邮箱,false:不需要绑定手机号或邮箱"`
	IsAuth         bool   `description:"true:需要授权,false:不需要授权"`
	PermissionName string `description:"拥有权限分类,多个用英文逗号分隔"`
}

func GetArticleUserCollectCount(userId int) (count int, err error) {
	sql := `SELECT SUM(count) AS count FROM (
			SELECT COUNT(1) AS count FROM cygx_article_collect AS a 
			INNER JOIN cygx_article as art ON art.article_id = a.article_id 
			WHERE a.user_id=? AND art.publish_status = 1  
			UNION ALL
			SELECT COUNT(1) AS count FROM cygx_yanxuan_special_collect AS a
			INNER JOIN cygx_yanxuan_special as b ON b.id = a.yanxuan_special_id 
			WHERE a.user_id=? AND b.status = 3) AS c 
			 `
	err = orm.NewOrm().Raw(sql, userId, userId).QueryRow(&count)
	return
}

func GetArticleUserCollectList(startSize, pageSize, userId int) (items []*ArticleReportBillboardResp, err error) {
	sql := `SELECT 
		a.article_id,
		art.category_id,
		'' AS title,
		'' AS publish_date,
		'' AS nick_name,
		0 AS article_type_id,
		'' AS article_type_name,
		0 AS is_special,
		'' AS special_tags,
		0 AS pv,
		0 AS collect_num,
		0 AS my_collect_num,
		0 AS special_type,
		a.create_time AS create_time 
			FROM cygx_article_collect AS a 
			INNER JOIN cygx_article as art ON art.article_id = a.article_id
			WHERE a.user_id=? 
			UNION ALL
	SELECT
		a.id AS article_id,
		0 AS category_id,
		a.title AS title,
		date_format( a.publish_time, '%Y-%m-%d' ) AS publish_date,
		b.nick_name AS nick_name,
		-1 AS article_type_id,
		'' AS article_type_name,
		1 AS is_special,
		a.tags AS special_tags,
		( SELECT count( 1 ) FROM cygx_yanxuan_special_record AS h WHERE h.yanxuan_special_id = a.id ) AS pv,
		( SELECT count( 1 ) FROM cygx_yanxuan_special_collect AS ac  INNER JOIN wx_user as u ON  u.user_id = ac.user_id  WHERE ac.yanxuan_special_id = a.id  ) AS collect_num,
		( SELECT count( 1 ) FROM cygx_yanxuan_special_collect AS ac WHERE ac.yanxuan_special_id = a.id  AND user_id = ? ) AS my_collect_num,
		a.type AS special_type,
		c.create_time AS create_time 
	FROM
	cygx_yanxuan_special AS a
	JOIN cygx_yanxuan_special_author AS b ON a.user_id = b.user_id
	JOIN cygx_yanxuan_special_collect AS c ON a.id = c.yanxuan_special_id
	WHERE
	1 = 1  AND a.status = 3	AND c.user_id=? 		
	ORDER BY create_time DESC 
LIMIT ?,? `
	_, err = orm.NewOrm().Raw(sql, userId, userId,userId, startSize, pageSize).QueryRows(&items)
	return
}

type ArticleCollectListResp struct {
	List   []*ArticleCollectList
	Paging *paging.PagingItem
}

func GetArticleUserInterviewApplyCount(userId int) (count int, err error) {
	sql := `SELECT COUNT(1) AS count FROM cygx_interview_apply AS a WHERE a.user_id=? `
	err = orm.NewOrm().Raw(sql, userId).QueryRow(&count)
	return
}

func GetArticleUserInterviewApplyList(startSize, pageSize, userId int) (items []*ArticleInterviewApplyList, err error) {
	sql := `SELECT a.* FROM cygx_interview_apply AS a
			WHERE a.user_id=? 
           ORDER BY a.status ASC LIMIT ?,? `
	_, err = orm.NewOrm().Raw(sql, userId, startSize, pageSize).QueryRows(&items)
	return
}

type ArticleInterviewApplyListResp struct {
	List   []*ArticleInterviewApplyList
	Paging *paging.PagingItem
}

func GetArticleUserBrowseHistoryCount(userId int, endDate string) (count int, err error) {
	sql := `SELECT COUNT( 1 ) as count
			FROM
			( SELECT count(*) FROM cygx_article_history_record AS a WHERE a.user_id = ? AND a.create_time >= ? GROUP BY a.article_id ) b  `
	err = orm.NewOrm().Raw(sql, userId, endDate).QueryRow(&count)
	return
}

func GetArticleUserBrowseHistoryList(startSize, pageSize, userId int, endDate string) (items []*ArticleReportBillboardResp, err error) {
	sql := `SELECT a.* ,	MAX( a.id ) AS mid   FROM cygx_article_history_record AS a
			WHERE a.user_id=? AND a.create_time>=?  GROUP BY a.article_id
           ORDER BY mid  DESC LIMIT ?,? `
	_, err = orm.NewOrm().Raw(sql, userId, endDate, startSize, pageSize).QueryRows(&items)
	return
}

type ArticleBrowseHistoryListResp struct {
	List   []*ArticleInterviewApplyList
	Paging *paging.PagingItem
}

type ApplyTryReq struct {
	BusinessCardUrl string `description:"名片地址"`
	RealName        string `description:"姓名"`
	CompanyName     string `description:"公司名称"`
	ApplyMethod     int    `description:"1:已付费客户申请试用，2:非客户申请试用,3:非客户申请试用(ficc下，不需要进行数据校验)"`
	TryType         string `description:"提交类型，Article：文章、Activity：活动、MicroAudio:微路演音频、MicroVideo:微路演视频、Researchsummary:本周研究汇总、Minutessummary:上周纪要汇总、ReportSelection:报告精选、ProductInterior:产品内测"`
	DetailId        int    `description:"详情ID"`
}

type CountryCode struct {
	IsNeedAddCountryCode bool `description:"是否需要填写区号:需要填写,false:不需要填写"`
}

type OutboundMobile struct {
	IsNeedAddOutboundMobile bool `description:"是否需要填写外呼手机号:需要填写,false:不需要填写"`
}

type CountryCodeItem struct {
	CountryCode string `description:"区号"`
}

func AddCountryCode(CountryCode string, user *WxUserItem) (err error) {
	o := orm.NewOrm()
	if user.OutboundCountryCode == "" {
		sql := `UPDATE wx_user SET country_code=?,outbound_mobile=?,outbound_country_code=?  WHERE user_id=? `
		_, err = o.Raw(sql, CountryCode, user.OutboundMobile, user.OutboundCountryCode, user.UserId).Exec()
	} else {
		sql := `UPDATE wx_user SET country_code=? WHERE user_id=? `
		_, err = o.Raw(sql, CountryCode, user.UserId).Exec()
	}
	return
}

// 修改外呼手机号
type OutboundMobileItem struct {
	OutboundMobile      string `description:"外呼手机号"`
	OutboundCountryCode string `description:"外呼手机号区号"`
	ActivityId          int    `description:"活动ID"`
}

func AddOutboundMobile(item *OutboundMobileItem, userId int) (err error) {
	o, err := orm.NewOrm().Begin()
	if err != nil {
		return
	}
	defer func() {
		fmt.Println(err)
		if err == nil {
			o.Commit()
		} else {
			o.Rollback()
		}
	}()
	sql := `UPDATE wx_user SET outbound_mobile=? ,outbound_country_code = ?, is_msg_outbound_mobile= 1 WHERE user_id=? `
	_, err = o.Raw(sql, item.OutboundMobile, item.OutboundCountryCode, userId).Exec()
	if err != nil {
		return
	}
	if item.ActivityId > 0 {
		sql = `UPDATE cygx_activity_signup SET outbound_mobile=? ,country_code = ?  WHERE user_id=? AND  activity_id = ?`
		_, err = o.Raw(sql, item.OutboundMobile, item.OutboundCountryCode, userId, item.ActivityId).Exec()
	}
	return
}

// 用户绑定手机号时同时绑定外呼手机号
func BindUserOutboundMobile(mobile, countryCode string, userId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE wx_user SET outbound_mobile=? ,outbound_country_code = ?, country_code= ? WHERE user_id=? `
	_, err = o.Raw(sql, mobile, countryCode, countryCode, userId).Exec()
	return
}

// 已经绑定手机号，没有绑定外呼手机号的的用户，预约外呼的时候，将外呼手机号同步成手机号
func BindUserOutboundMobileByMobile(mobile, countryCode string, userId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE wx_user SET outbound_mobile=? ,outbound_country_code = ? WHERE user_id=? `
	_, err = o.Raw(sql, mobile, countryCode, userId).Exec()
	return
}

// 将手机号为11位的用户，区号默认设置为86
func ChangeUserOutboundMobileByMobile(userId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE wx_user SET country_code = 86 WHERE user_id=? `
	_, err = o.Raw(sql, userId).Exec()
	return
}

type UserWhiteList struct {
	Mobile      string `description:"手机号码"`
	RealName    string `description:"用户实际名称"`
	CompanyName string `description:"公司名称"`
	Permission  string `description:"拥有权限分类,多个用英文逗号分隔"`
	CountryCode string `description:"区号"`
	SellerName  string `description:"销售姓名"`
	CreatedTime time.Time
	Status      string `description:"客户状态'试用','永续','冻结','流失','正式','潜在'"`
}

type UserWhiteListRep struct {
	List []*UserWhiteList
}

// 获取正式试用用户白名单
func GetFormalUserWhiteList(fieldStr, condition string) (items []*UserWhiteList, err error) {
	sql := `SELECT ` + fieldStr + `
		(SELECT cp.seller_name FROM company_product  AS cp WHERE cp.company_id = u.company_id ORDER BY cp.product_id DESC  LIMIT 0,1 ) as seller_name,
		GROUP_CONCAT( DISTINCT b.chart_permission_name SEPARATOR '/' ) AS permission
		FROM wx_user AS u
		INNER JOIN company as c ON c.company_id = u.company_id
		INNER JOIN company_report_permission AS p ON p.company_id = u.company_id
		INNER JOIN chart_permission AS b ON b.chart_permission_id=p.chart_permission_id
		INNER JOIN company_product AS cp ON cp.company_id = u.company_id
		WHERE 1= 1
		AND cp.product_id = 2
		AND b.product_id = 2
		AND u.company_id >1 ` + condition + `
		GROUP BY u.user_id`
	_, err = orm.NewOrm().Raw(sql).QueryRows(&items)
	return
}

// 获取永续用户白名单
func GetSustainableUserWhiteList(fieldStr, condition string) (items []*UserWhiteList, err error) {
	sql := `SELECT ` + fieldStr + `
			(SELECT cp.seller_name FROM company_product  AS cp WHERE cp.company_id = u.company_id ORDER BY cp.product_id DESC  LIMIT 0,1 ) as seller_name,
			(SELECT
				GROUP_CONCAT( DISTINCT b.chart_permission_name SEPARATOR '/' ) 
			FROM
				company_report_permission AS p
				INNER JOIN chart_permission AS b ON b.chart_permission_id = p.chart_permission_id 
			WHERE
				p.company_id = u.company_id 
				AND p.status IN ( '永续' ) 
				AND p.product_id = 2 
			) AS permission 
			FROM wx_user AS u
			INNER JOIN company AS c ON c.company_id = u.company_id
			INNER JOIN company_report_permission AS p ON p.company_id = u.company_id
			INNER JOIN chart_permission AS b ON b.chart_permission_id = p.chart_permission_id
			INNER JOIN company_product AS cp ON cp.company_id = u.company_id 
			WHERE 1 = 1
			AND u.company_id > 1  ` + condition + `
			GROUP BY u.user_id `
	_, err = orm.NewOrm().Raw(sql).QueryRows(&items)
	return
}

// 权益用户
type GetSendEmailAllUserWithRAIRep struct {
	CompanyName  string `description:"客户名称"`
	CreditCode   string `description:"社会统一信用码"`
	ProductName  string `description:"客户类型"`
	IndustryName string `description:"行业"`
	RealName     string `description:"姓名"`
	Mobile       string `description:"手机号"`
	Status       string `description:"手机号"`
	StartDate    string `description:"合同开始日期"`
	EndDate      string `description:"合同结束日期"`
	CreatedTime  string `description:"创建时间"`
	Permission   string `description:"权限"`
}

func GetSendEmailAllUserWithRAI() (items []*GetSendEmailAllUserWithRAIRep, err error) {
	sql := `SELECT
			c.company_name,
			c.credit_code,
			cp.product_name,
			cp.industry_name,
			a.real_name,
			a.mobile,
			cp.status,
			cp.start_date,
			cp.end_date,
			c.created_time,
			GROUP_CONCAT( DISTINCT b.chart_permission_name SEPARATOR '/' ) AS permission 
		FROM
			company AS c
			INNER JOIN company_report_permission AS p ON p.company_id = c.company_id
			INNER JOIN chart_permission AS b ON b.chart_permission_id = p.chart_permission_id
			INNER JOIN company_product AS cp ON cp.company_id = c.company_id
			INNER JOIN admin AS a ON a.admin_id = cp.seller_id 
		WHERE
			1 = 1 
			AND cp.product_id = 2 
			AND b.product_id = 2 
			AND cp.status IN ( '正式', '试用' ) 
		GROUP BY
			c.company_id 
		ORDER BY
			c.company_id DESC`
	_, err = orm.NewOrm().Raw(sql).QueryRows(&items)
	return
}

// 权益用户
type GetSendEmailAllUserWithCompanyRep struct {
	RealName    string `description:"姓名"`
	Mobile      string `description:"手机号"`
	CountryCode string `description:"国家号"`
	Email       string `description:"邮箱"`
	CompanyName string `description:"客户名称"`
	CreditCode  string `description:"社会统一信用码"`
	IsMaker     string `description:"是否是决策人，1是，0否"`
}

func GetSendEmailAllUserWithCompany() (items []*GetSendEmailAllUserWithCompanyRep, err error) {
	sql := `SELECT
			u.real_name,
			u.mobile,
			u.country_code,
			u.email,
			c.company_name,
			u.is_maker 
		FROM
			no_use AS n
			INNER JOIN company AS c ON c.company_name = n.company_name
			INNER JOIN company_report_permission AS p ON p.company_id = c.company_id
			INNER JOIN chart_permission AS b ON b.chart_permission_id = p.chart_permission_id
			INNER JOIN company_product AS cp ON cp.company_id = c.company_id
			INNER JOIN wx_user AS u ON u.company_id = c.company_id 
		GROUP BY
			u.user_id 
		ORDER BY
			c.company_id ASC`
	_, err = orm.NewOrm().Raw(sql).QueryRows(&items)
	return
}

type Headimgurl struct {
	Headimgurl string `description:"用户头像"`
}
type UserEmail struct {
	Email string `description:"邮箱号"`
}

// 更改用户手机号
func UpdateUserHeadimgurl(headimgurl string, userId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE wx_user SET headimgurl = ? WHERE user_id=? `
	_, err = o.Raw(sql, headimgurl, userId).Exec()
	return
}

// 更改用户邮箱号
func UpdateUserEmail(email string, userId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE wx_user SET email = ? WHERE user_id=? `
	_, err = o.Raw(sql, email, userId).Exec()
	return
}

// 更新用户标签
func UpdateUserLabel(userLabel string, userId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE wx_user SET user_label = ? WHERE user_id=? `
	_, err = o.Raw(sql, userLabel, userId).Exec()
	return
}

// 更新用户互动量
func UpdateUserInteractionNum(interactionNum, userId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE wx_user SET interaction_num = ? WHERE user_id=? `
	_, err = o.Raw(sql, interactionNum, userId).Exec()
	return
}

// UserPermissionAuthInfo 用户通用权限信息
type UserPermissionAuthInfo struct {
	HasPermission int    `description:"是否有权限：1-有权限; 2-无权限; 3-潜在客户未提交申请; 4-潜在客户已提交申请 5-仅有FICC权限"`
	SellerMobile  string `description:"销售手机号"`
	SellerName    string `description:"销售名称"`
	OperationMode string `description:"操作方式：Apply-立即申请; Call-拨号"`
	PopupMsg      string `description:"权限弹窗信息"`
}

func GetUserMicroRoadshowCollectList(userId int) (items []*CygxArticleCollect, err error) {
	sql := `SELECT a.* FROM cygx_article_collect AS a 
			WHERE a.user_id=?  
			AND a.article_id =0 
           ORDER BY a.create_time DESC `
	_, err = orm.NewOrm().Raw(sql, userId).QueryRows(&items)
	return
}

func GetUserMicroRoadshowCollectcount(userId int) (count int, err error) {
	sqlCount := `SELECT  COUNT(1) AS count FROM cygx_article_collect AS a 
			WHERE a.user_id=?  
			AND a.article_id =0 
           ORDER BY a.create_time DESC `
	o := orm.NewOrm()
	err = o.Raw(sqlCount, userId).QueryRow(&count)
	return
}