hz_crm_api/models/user_seller_relation.go

package models

import (
	"github.com/beego/beego/v2/client/orm"
	"time"
)

type UserSellerRelation struct {
	RelationId int64     `orm:"column(relation_id);pk"`
	UserId     int       `description:"用户id"`
	CompanyId  int       `description:"企业用户id"`
	SellerId   int       `description:"销售id"`
	Seller     string    `description:"销售员名称"`
	ProductId  int       `description:"产品id"`
	Mobile     string    `description:"手机号"`
	Email      string    `description:"邮箱"`
	ModifyTime time.Time `description:"修改时间"`
	CreateTime time.Time `description:"创建时间"`
}

// 添加销售员与员工的关系
func AddUserSellerRelation(userId int64, companyId, sellerId, productId int, seller, mobile, email string) (lastId int64, err error) {
	o := orm.NewOrm()
	relation := UserSellerRelation{
		UserId:     int(userId),
		SellerId:   sellerId,
		CompanyId:  companyId,
		Seller:     seller,
		ProductId:  productId,
		Mobile:     mobile,
		Email:      email,
		CreateTime: time.Now(),
		ModifyTime: time.Now(),
	}
	lastId, err = o.Insert(&relation)
	return
}

// 根据企业用户id修改所属销售
func UpdateUserSellerRelationByCompanyId(companyId, productId, sellerId int, seller string) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE user_seller_relation SET seller_id=?,seller = ? ,modify_time=NOW() WHERE company_id = ? AND product_id=?`
	_, err = o.Raw(sql, sellerId, seller, companyId, productId).Exec()
	return
}

// 删除销售员与员工的关系
func DeleteUserSellerRelation(userId, sellerId int) (err error) {
	o := orm.NewOrm()
	sql := ` DELETE FROM user_seller_relation WHERE user_id=? and seller_id = ?`
	_, err = o.Raw(sql, userId, sellerId).Exec()
	return
}

// 根据产品id删除销售员与员工的关系
func DeleteUserSellerRelationByProductId(userId, productId int) (err error) {
	o := orm.NewOrm()
	sql := ` DELETE FROM user_seller_relation WHERE user_id=? and product_id = ?`
	_, err = o.Raw(sql, userId, productId).Exec()
	return
}

// 根据联系人id删除所有销售员与该联系人的关系
func DeleteUserSellerRelationByUserId(userId int) (err error) {
	o := orm.NewOrm()
	sql := ` DELETE FROM user_seller_relation WHERE user_id=? `
	_, err = o.Raw(sql, userId).Exec()
	return
}

// 根据用户id和销售员id获取关系
func GetUserSellerRelation(userId int64, sellerId int) (item *UserSellerRelation, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM user_seller_relation WHERE user_id = ? and seller_id= ?`
	err = o.Raw(sql, userId, sellerId).QueryRow(&item)
	return
}

// 获取用户的销售员（产品）的关系数量
func GetUserSellerRelationCount(userId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(*) AS count
			FROM user_seller_relation WHERE user_id = ? `
	err = o.Raw(sql, userId).QueryRow(&count)
	return
}

// 通过用户id获取用户的销售员（产品）的关系
func GetUserSellerRelationList(userId int) (list []*UserSellerRelation, err error) {
	o := orm.NewOrm()
	sql := `SELECT *
			FROM user_seller_relation WHERE user_id = ? `
	_, err = o.Raw(sql, userId).QueryRows(&list)
	return
}

// 用户与销售员的关系数量切片
type UserSellerRelationSlice struct {
	Total      int    `description:"总阅读数"`
	UserId     int    `description:"用户id"`
	ProductIds string `description:"所属权限id"`
}

// 根据用户id字符串，获取用户与销售员的关系数量
func GetUserSellerRelationCountByUserIds(userIds string) (items []*UserSellerRelationSlice, err error) {
	o := orm.NewOrm()
	sql := `select *,COUNT(1) AS total,GROUP_CONCAT( DISTINCT product_id SEPARATOR ',' ) AS product_ids from (SELECT user_id, product_id
			FROM user_seller_relation WHERE user_id in (` + userIds + `)  group by user_id,product_id) b group by user_id`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 根据用户产品权限来获取名片数
func GetCompanyUserBusinessCardCountByProductId(companyId, productId int) (count int, err error) {
	o := orm.NewOrm()
	sql := ` SELECT COUNT(1) AS count FROM user_seller_relation a 
left join wx_user b on a.user_id=b.user_id WHERE  a.company_id =? and a.product_id=? AND b.business_card_url<>'' `
	err = o.Raw(sql, companyId, productId).QueryRow(&count)
	return
}

// GetCompanyUserSellerRelationByProductId 根据企业id和产品id获取所有用户
func GetCompanyUserSellerRelationByProductId(companyId, productId int) (items []*UserSellerRelation, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM user_seller_relation WHERE company_id = ? AND product_id=?`
	_, err = o.Raw(sql, companyId, productId).QueryRows(&items)
	return
}

// 根据企业id和产品id获取所有不是该产品id的所有用户
func GetNotCompanyUserSellerRelationByProductId(companyId, productId int) (items []*UserSellerRelation, err error) {
	o := orm.NewOrm()
	sql := `SELECT user_id FROM user_seller_relation WHERE company_id = ? AND product_id != ?`
	_, err = o.Raw(sql, companyId, productId).QueryRows(&items)
	return
}

// 根据产品id删除某个客户下 销售员与员工的关系
func DelCompanyUserSellerRelationByProductId(companyId, productId int) (err error) {
	o := orm.NewOrm()
	sql := ` DELETE FROM user_seller_relation WHERE company_id = ? AND product_id = ? `
	_, err = o.Raw(sql, companyId, productId).Exec()
	return
}

// 获取联系人分组信息
type UserSellerRelationGroup struct {
	RelationId     int64     `orm:"column(relation_id);pk"`
	UserId         int       `description:"用户id"`
	CompanyId      int       `description:"企业用户id"`
	SellerId       int       `description:"销售id"`
	Seller         string    `description:"销售员名称"`
	SellerRealName string    `description:"销售员名称"`
	Status         string    `description:"产品权限状态"`
	ProductId      int       `description:"产品id"`
	Mobile         string    `description:"手机号"`
	Email          string    `description:"邮箱"`
	ModifyTime     time.Time `description:"修改时间"`
	CreateTime     time.Time `description:"创建时间"`
}

func GetUserGroupSellerByUserId(userId int) (item *UserSellerRelationGroup, err error) {
	o := orm.NewOrm()
	sql := ` SELECT a.*,GROUP_CONCAT(DISTINCT b.real_name SEPARATOR '/') AS seller_real_name,GROUP_CONCAT(DISTINCT c.status SEPARATOR '/') AS status
			FROM user_seller_relation AS a
			LEFT  JOIN admin AS b ON a.seller_id=b.admin_id
			LEFT  JOIN company_product AS c ON a.company_id=c.company_id
			WHERE a.user_id=? GROUP BY a.company_id`
	err = o.Raw(sql, userId).QueryRow(&item)
	return
}

// 根据联系人id获所有的分组信息
func GetUserGroupSellersByUserId(userId int) (list []*UserSellerRelationGroup, err error) {
	o := orm.NewOrm()
	sql := ` SELECT a.*,b.real_name AS seller_real_name,c.status  
			FROM user_seller_relation AS a
			LEFT  JOIN admin AS b ON a.seller_id=b.admin_id
			LEFT  JOIN company_product AS c ON a.company_id=c.company_id
			WHERE a.user_id=? `
	_, err = o.Raw(sql, userId).QueryRows(&list)
	return
}