hongze_cygx/models/company/company_product.go

package company

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

type CompanyProduct struct {
	CompanyProductId    int       `orm:"column(company_product_id);pk" description:"客户产品id"`
	CompanyId           int       `description:"客户id"`
	ProductId           int       `description:"产品id"`
	ProductName         string    `description:"产品名称"`
	CompanyName         string    `description:"客户名称"`
	Source              string    `description:"来源"`
	Reasons             string    `description:"新增理由"`
	Status              string    `description:"客户状态"`
	IndustryId          int       `description:"行业id"`
	IndustryName        string    `description:"行业名称"`
	SellerId            int       `description:"销售id"`
	SellerName          string    `description:"销售名称"`
	GroupId             int       `description:"销售分组id"`
	ShareGroupId        int       `description:"共享销售分组id"`
	DepartmentId        int       `description:"销售部门id"`
	IsSuspend           int       `description:"1:暂停,0:启用"`
	SuspendTime         time.Time `description:"暂停启用时间"`
	TryOutTime          time.Time `description:"正式转试用时间"`
	RenewalReason       string    `description:"正式转试用后的续约情况说明"`
	RenewalTodo         string    `description:"未续约说明中的待办事项说明"`
	LastDescriptionTime time.Time `description:"上次添加说明时间"`
	RenewalIntention    int       `description:"是否勾选无续约意向,1:确认,0:未确认"`
	ApproveStatus       string    `description:"审批状态:'审批中','通过','驳回'"`
	FreezeTime          time.Time `description:"冻结时间"`
	FreezeReason        time.Time `description:"冻结理由"`
	Remark              string    `description:"备注信息"`
	CreateTime          time.Time `description:"创建时间"`
	ModifyTime          time.Time `description:"修改时间"`
	StartDate           string    `description:"开始日期"`
	EndDate             string    `description:"结束日期"`
	ContractEndDate     time.Time `description:"合同结束日期"`
	LoseReason          string    `description:"流失原因"`
	LossTime            time.Time `description:"流失时间"`
	CompanyType         string    `description:"客户类型"`
	OpenCode            string    `description:"开放给第三方的编码，不让第三方定位我们的客户信息"`
	Scale               string    `description:"管理规模，空不填，1：:50亿以下，2：50~100亿，3：100亿以上。"`
	ViewTotal           int       `description:"总阅读次数"`
	RoadShowTotal       int       `description:"累计路演次数"`
	LastViewTime        time.Time `description:"最后一次阅读时间"`
	PackageType         int       `description:"套餐类型"`
	IsFormal            int       `description:"是否已经转正式，0是没有转正式，1是已经转过正式"`
	TodoStatus          string    `description:"任务处理状态；枚举值：'无任务','未完成','已完成'"`
	TodoCreateTime      time.Time `description:"任务创建时间"`
	TodoApproveTime     time.Time `description:"任务审批时间"`
	TryStage            int       `description:"试用客户子标签：1未分类、2  推进、3 跟踪、4 预备"`
	TryOutDayTotal      int       `description:"客户总试用天数"`
	CloseReason         string    `description:"关闭原因"`
	CloseTime           time.Time `description:"关闭时间"`
	OverseasLabel       int       `description:"海外客户试用子标签：1未分类、2  推进、3 跟踪、4 预备、"`
	IsOverseas          int       `description:"是否显示在海外客户0:显示,1:不显示"`
	ShareSeller         string    `description:"共享销售员"`
	ShareSellerId       int       `description:"共享销售员id"`
}

func GetCompanyProductByCompanyIdAndProductId(companyId, productId int) (item *CompanyProduct, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT b.* FROM company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE a.company_id=? AND b.product_id=? LIMIT 1 `
	err = o.Raw(sql, companyId, productId).QueryRow(&item)
	return
}

func GetCompanyProductCount(companyId, productId int) (count int, err error) {
	sql := ` SELECT COUNT(1) AS count FROM  company_product WHERE company_id = ? AND product_id = ? `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId, productId).QueryRow(&count)
	return
}

func GetCompanyProductDetailByCompanyId(companyId, productId int) (item *CompanyProduct, err error) {
	sql := ` SELECT * FROM company_product WHERE company_id = ? AND product_id = ?; `
	o := orm.NewOrmUsingDB("weekly_report")
	err = o.Raw(sql, companyId, productId).QueryRow(&item)
	return
}

func GetCompanyProductAaiServeCount(companyId, groupId int) (count int, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT COUNT(1) AS count FROM  company_product WHERE  company_id = ?  AND product_id = 2 AND  (group_id = ? OR  share_group_id = ?) `
	err = o.Raw(sql, companyId, groupId, groupId).QueryRow(&count)
	return
}

// 获取列表
func GetCompanyProductAndCompanyListByCondition(condition string, pars []interface{}) (items []*CompanyProduct, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT
			p.company_id,
			p.seller_id,
			p.seller_name,
			p.start_date,
			p.end_date,
			p.share_seller_id,
			p.share_seller,
			p.group_id,
			p.share_group_id,
			p.status,
			c.company_name
		FROM
			company_product AS p
			INNER JOIN company AS c ON c.company_id = p.company_id 
		WHERE
			1 = 1  `
	if condition != "" {
		sql += condition
	}
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

type CompanyProductUser struct {
	CompanyId   int    `description:"客户id"`
	ProductId   int    `description:"产品id"`
	CompanyName string `description:"客户名称"`
	Status      string `description:"客户状态"`
	SellerId    int    `description:"销售id"`
	SellerName  string `description:"销售名称"`
	Mobile      string `description:"手机号"`
	UserId      int
	RealName    string `description:"用户实际名称"`
	Email       string `description:"邮箱"`
}

// 获取销售信息列表
func GetCompanyProductListByMobiles(mobiles []string) (items []*CompanyProductUser, err error) {
	lenArr := len(mobiles)
	if lenArr == 0 {
		return
	}
	o := orm.NewOrmUsingDB("weekly_report")
	sql := ` SELECT
			u.mobile,
			u.user_id,
			u.email,
			u.real_name,
			u.company_id,
			p.seller_name,
			p.status,
			c.company_name
		FROM
			wx_user as u   
			INNER JOIN company AS c ON c.company_id = u.company_id 
			LEFT  JOIN company_product AS p ON p.company_id = u.company_id 
		WHERE u.mobile IN (` + utils.GetOrmInReplace(lenArr) + `)  AND  p.product_id = 2 `
	_, err = o.Raw(sql, mobiles).QueryRows(&items)
	return
}

// 获取销售信息
func GetCompanyProductListByUserId(userId int) (item *CompanyProductUser, err error) {
	o := orm.NewOrmUsingDB("weekly_report")
	sql := `SELECT
			u.mobile,
			u.user_id,
			u.email,
			u.real_name,
			u.company_id,
			p.seller_name,
			p.status,
			c.company_name
		FROM
			wx_user as u   
			INNER JOIN company AS c ON c.company_id = u.company_id 
			INNER JOIN company_product AS p ON p.company_id = u.company_id 
		WHERE u.user_id = ?  AND  p.product_id = 2 `
	err = o.Raw(sql, userId).QueryRow(&item)
	return
}

// 获取权益客户下的正式、试用、永续客户。
func GetRaiCompanyProductCompanyids() (items []*CompanyProduct, err error) {
	sql := ` SELECT  company_id  FROM  company_product WHERE  product_id = 2 AND status IN  ('正式','试用','永续') AND  company_id > 1  `
	o := orm.NewOrmUsingDB("weekly_report")
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 更新权益客户下近四周之内是否包含决策人(是否有KP互动过)
func UpdateRaiCompanyProductIsUserMakerByCompanyids(companyIds []int) (err error) {
	lenArr := len(companyIds)
	if lenArr == 0 {
		return
	}
	o, err := orm.NewOrmUsingDB("weekly_report").Begin()
	if err != nil {
		return
	}
	defer func() {
		fmt.Println(err)
		if err == nil {
			o.Commit()
		} else {
			o.Rollback()
		}
	}()
	sql := `UPDATE company_product SET is_user_maker = -1 WHERE  product_id=2  AND  company_id > 1  `
	_, err = o.Raw(sql).Exec()
	if err != nil {
		return
	}

	sql = `UPDATE company_product SET is_user_maker = 1 WHERE  product_id=2   AND status IN  ('正式','试用','永续') AND  company_id > 1   AND  company_id IN (` + utils.GetOrmInReplace(lenArr) + `) `
	_, err = o.Raw(sql, companyIds).Exec()

	return
}