hz_crm_api/models/company/company_user.go

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-已关注"`
	IsSubscribeMfyx          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:"是否移动过"`
	OutboundMobile           string    `description:"外呼手机号"`
	OutboundCountryCode      string    `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,  a.mfyx_subscribe AS is_subscribe_mfyx , 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
}

//<template slot-scope="scope">{{scope.row.ReportType=='day'?'晨报':scope.row.ReportType=='week'?'周报':scope.row.ReportType=='twoweek'?'双周报':scope.row.ReportType=='month'?'月报':scope.row.ReportType=='rddp'?'日度点评':scope.row.ReportType=='cygx'?'查研观向':scope.row.ReportType=='advisory'?'每日商品聚焦':''}}</template>

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
}