hongze_task/models/company_product.go

package models

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

type CompanyOperationRecord struct {
	Id              int       `orm:"column(id);pk"`
	CompanyId       int       `description:"客户id"`
	CompanyName     string    `description:"客户名称"`
	SysUserId       int       `description:"操作者id"`
	SysRealName     string    `description:"操作者名称"`
	Remark          string    `description:"备注"`
	Operation       string    `description:"操作"`
	CreateTime      time.Time `description:"操作时间"`
	ProductId       int       `description:"产品id"`
	ProductName     string    `description:"产品名称"`
	ApproveUserId   int       `description:"审批人id"`
	ApproveRealName string    `description:"审批人姓名"`
	ApproveContent  string    `description:"审批人内容"`
	ApproveRemark   string    `description:"审批人内容"`
	Status          string    `description:"状态"`
}

// 新增操作记录
func AddCompanyOperationRecord(item *CompanyOperationRecord) (lastId int64, err error) {
	o := orm.NewOrm()
	lastId, err = o.Insert(item)
	return
}

func CompanyFreeze(companyId, productId int) (companyReportPermissionList []*CompanyReportPermission, err error) {
	o := orm.NewOrm()
	freezeStartDate := time.Now().Format(utils.FormatDate)
	freezeEndDate := time.Now().AddDate(0, 3, 0).Format(utils.FormatDate)

	//FICC客户冻结期为六个月
	if productId == 1 {
		freezeEndDate = time.Now().AddDate(0, 6, 0).Format(utils.FormatDate)
	}

	// 权益客户 冻结期 改为两个月
	if productId == 2 {
		freezeEndDate = time.Now().AddDate(0, 2, 0).Format(utils.FormatDate)
	}

	//客户产品 状态 变更
	sql := `UPDATE company_product SET status='冻结',is_formal=0,is_suspend=0,freeze_time=NOW(),modify_time=NOW(),start_date=?,end_date=?,freeze_start_date=?,freeze_end_date=?,try_stage=1 WHERE company_id=? AND product_id=? `
	_, err = o.Raw(sql, freezeStartDate, freezeEndDate, freezeStartDate, freezeEndDate, companyId, productId).Exec()
	if err != nil {
		return
	}

	//客户产品权限 状态 变更
	//获取需要变更的 客户产品权限
	oldPermissionEndDate := time.Now().AddDate(0, 0, -1).Format(utils.FormatDate)
	sql = `SELECT *
          FROM company_report_permission
			WHERE status='试用' AND end_date<=? AND company_id=? AND product_id=? `
	total, err := o.Raw(sql, oldPermissionEndDate, companyId, productId).QueryRows(&companyReportPermissionList)
	if err != nil {
		return
	}

	if total > 0 {
		sql = `UPDATE company_report_permission SET status='关闭',modify_time=NOW() 
			WHERE status='试用' AND end_date<=? AND company_id=? AND product_id=? `
		_, err = o.Raw(sql, oldPermissionEndDate, companyId, productId).Exec()
		if err != nil {
			return
		}
	}

	//客户状态变更
	sql = `UPDATE company SET type=3,last_updated_time=NOW(),start_date=?,end_date=? WHERE company_id=? `
	_, err = o.Raw(sql, freezeStartDate, freezeEndDate, companyId).Exec()
	return
}

func CompanyFreezeXClassRai(companyId, productId int) (companyReportPermissionList []*CompanyReportPermission, err error) {
	o := orm.NewOrm()
	freezeStartDate := time.Now().Format(utils.FormatDate)
	freezeEndDate := time.Now().AddDate(0, 3, 0).Format(utils.FormatDate)

	//FICC客户冻结期由三个月改为两个月
	//if productId == 1 {
	//	freezeEndDate = time.Now().AddDate(0, 2, 0).Format(utils.FormatDate)
	//}

	// 权益客户 冻结期 改为两个月
	if productId == 2 {
		freezeEndDate = time.Now().AddDate(0, 2, 0).Format(utils.FormatDate)
	}

	//客户产品 状态 变更
	sql := `UPDATE company_product SET status='冻结',is_formal=0,is_suspend=0,freeze_time=NOW(),modify_time=NOW(),start_date=?,end_date=?,freeze_start_date=?,freeze_end_date=?,try_stage=1 WHERE company_id=? AND product_id=? `
	_, err = o.Raw(sql, freezeStartDate, freezeEndDate, freezeStartDate, freezeEndDate, companyId, productId).Exec()
	if err != nil {
		return
	}

	//客户产品权限 状态 变更
	//获取需要变更的 客户产品权限
	oldPermissionEndDate := time.Now().AddDate(0, 0, -1).Format(utils.FormatDate)
	sql = `SELECT *
          FROM company_report_permission
			WHERE status='永续' AND end_date<=? AND company_id=? AND product_id=? `
	total, err := o.Raw(sql, oldPermissionEndDate, companyId, productId).QueryRows(&companyReportPermissionList)
	if err != nil {
		return
	}

	if total > 0 {
		sql = `UPDATE company_report_permission SET status='关闭',modify_time=NOW() 
			WHERE status='永续' AND end_date<=? AND company_id=? AND product_id=? `
		_, err = o.Raw(sql, oldPermissionEndDate, companyId, productId).Exec()
		if err != nil {
			return
		}
	}

	//客户状态变更
	sql = `UPDATE company SET type=3,last_updated_time=NOW(),start_date=?,end_date=? WHERE company_id=? `
	_, err = o.Raw(sql, freezeStartDate, freezeEndDate, companyId).Exec()
	return
}

func CompanyLoss(companyId, productId int) (err error) {
	o := orm.NewOrm()
	//客户产品状态变更
	sql := `UPDATE company_product SET status='流失',is_formal=0,loss_time=NOW(),modify_time=NOW(),lose_reason='冻结到期系统自动流失',try_stage=1,todo_status="无任务",todo_create_time=null,todo_approve_time=null,todo_modify_time=null,todo_end_time=null WHERE company_id=? AND product_id=? `
	_, err = o.Raw(sql, companyId, productId).Exec()
	if err != nil {
		return
	}

	// 将历史的任务给标记删除掉
	sql = `UPDATE company_todo SET is_delete=1,modify_time=NOW() WHERE company_id=? AND product_id=? AND is_delete = 0 `
	_, err = o.Raw(sql, companyId, productId).Exec()
	if err != nil {
		return
	}

	//客户状态变更
	sql = `UPDATE company SET type=3,last_updated_time=NOW() WHERE company_id=? `
	_, err = o.Raw(sql, companyId).Exec()
	if err != nil {
		return
	}
	return
}

// 正式转试用
func CompanyTryOut(companyId, productId int) (companyReportPermissionList []*CompanyReportPermission, err error) {
	o := orm.NewOrm()
	startDate := time.Now().Format(utils.FormatDate)
	endDate := time.Now().AddDate(0, 2, 0).Format(utils.FormatDate)

	//客户产品 状态 变更
	sql := `UPDATE company_product SET status='试用',start_date=?,end_date=?,modify_time=NOW(),try_out_time=NOW(),renewal_reason="",package_type=0,try_stage=1 WHERE company_id=? AND product_id=? `
	_, err = o.Raw(sql, startDate, endDate, companyId, productId).Exec()
	if err != nil {
		return
	}

	//客户产品权限 状态 变更
	//获取需要变更的 客户产品权限
	oldPermissionEndDate := time.Now().AddDate(0, 0, -1).Format(utils.FormatDate)
	sql = `SELECT *
          FROM company_report_permission
			WHERE status='正式' AND end_date<=? AND company_id=? AND product_id=? `
	total, err := o.Raw(sql, oldPermissionEndDate, companyId, productId).QueryRows(&companyReportPermissionList)
	if err != nil {
		return
	}
	if total > 0 {
		sql = `UPDATE company_report_permission SET status='试用',start_date=?,end_date=?,modify_time=NOW() 
			WHERE status='正式' AND end_date<=? AND company_id=? AND product_id=? `
		_, err = o.Raw(sql, startDate, endDate, oldPermissionEndDate, companyId, productId).Exec()
		if err != nil {
			return
		}
	}

	//客户状态变更
	sql = `UPDATE company SET type=2,last_updated_time=NOW(),start_date=?,end_date=? WHERE company_id=? `
	_, err = o.Raw(sql, startDate, endDate, companyId).Exec()
	return
}

// 权益正式转X类试用(永续)
func CompanyTryOutXClassRai(companyId, productId int) (companyReportPermissionList []*CompanyReportPermission, err error) {
	o := orm.NewOrm()
	startDate := time.Now().Format(utils.FormatDate)
	endDate := time.Now().AddDate(0, 3, 0).Format(utils.FormatDate)

	//客户产品 状态 变更
	sql := `UPDATE company_product SET status='永续',start_date=?,end_date=?,modify_time=NOW(),try_out_time=NOW(),renewal_reason="",package_type=0,try_stage=1 WHERE company_id=? AND product_id=? `
	_, err = o.Raw(sql, startDate, endDate, companyId, productId).Exec()
	if err != nil {
		return
	}

	//客户产品权限 状态 变更
	//获取需要变更的 客户产品权限
	oldPermissionEndDate := time.Now().AddDate(0, 0, -1).Format(utils.FormatDate)
	sql = `SELECT *
          FROM company_report_permission
			WHERE status='正式' AND end_date<=? AND company_id=? AND product_id=? `
	total, err := o.Raw(sql, oldPermissionEndDate, companyId, productId).QueryRows(&companyReportPermissionList)
	if err != nil {
		return
	}
	if total > 0 {
		sql = `UPDATE company_report_permission SET status='永续',start_date=?,end_date=?,modify_time=NOW() 
			WHERE status='正式' AND end_date<=? AND company_id=? AND product_id=? `
		_, err = o.Raw(sql, startDate, endDate, oldPermissionEndDate, companyId, productId).Exec()
		if err != nil {
			return
		}
	}

	//客户状态变更
	sql = `UPDATE company SET type=5,last_updated_time=NOW(),start_date=?,end_date=? WHERE company_id=? `
	_, err = o.Raw(sql, startDate, endDate, companyId).Exec()
	return
}

func GetCompanyOldDataSync() (items []*Company, err error) {
	sql := `SELECT * FROM company WHERE company_id NOT IN(
			SELECT company_id FROM company_product
			)
			AND company_id<>1 `
	o := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

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"`
	DepartmentId     int       `description:"销售部门id"`
	IsSuspend        int       `description:"1:暂停,0:启用"`
	SuspendTime      time.Time `description:"暂停启用时间"`
	ApproveStatus    string    `description:"审批状态:'审批中','通过','驳回'"`
	FreezeTime       time.Time `description:"冻结时间"`
	Remark           string    `description:"备注信息"`
	CreateTime       time.Time `description:"创建时间"`
	ModifyTime       time.Time `description:"修改时间"`
	StartDate        string    `description:"开始日期"`
	EndDate          string    `description:"结束日期"`
	ContractEndDate  string    `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:"套餐类型，0：无，1：大套餐，2：小套餐"`
	IsFormal         int       `description:"是否已经转正式，0是没有转正式，1是已经转过正式"`
	TodoStatus       string    `description:"任务处理状态；枚举值：'无任务','未完成','已完成'"`
	TodoCreateTime   time.Time `description:"任务创建时间"`
	TodoApproveTime  time.Time `description:"任务审批时间"`
	TryStage         int       `description:"试用客户子标签：1未分类、2  推进、3 跟踪、4 预备"`
}

// GetCompanyProduct 获取产品详情
func GetCompanyProduct(companyId int, productId int) (companyProduct *CompanyProduct, err error) {
	o := orm.NewOrm()
	sql := `SELECT *
				FROM company_product WHERE company_id= ? AND product_id = ? `
	err = o.Raw(sql, companyId, productId).QueryRow(&companyProduct)
	return
}

// 新增客户产品
func AddCompanyProduct(item *CompanyProduct) (newId int64, err error) {
	o := orm.NewOrm()
	newId, err = o.Insert(item)
	return
}

type Sellers struct {
	AdminId      int    `description:"销售id"`
	RealName     string `description:"销售姓名"`
	Email        string `description:"销售邮箱"`
	OpenId       string `description:"销售openid"`
	Mobile       string `description:"销售电话"`
	RoleTypeCode string `description:"角色编码"`
}

func GetSellers() (items []*Sellers, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.real_name,a.email,c.open_id,a.mobile,b.mobile,a.role_type_code,a.admin_id
           FROM admin AS a
			LEFT JOIN wx_user AS b ON a.mobile=b.mobile
			LEFT JOIN user_record AS c ON b.user_id=c.user_id
			WHERE role_type_code IN('ficc_seller','ficc_group','ficc_team','rai_seller','rai_group')
			and (
				(a.email != "") or ( c.open_id<>'' and c.create_platform=1)
			)`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetSellersOpenId() (items []*Sellers, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.real_name,a.email,a.open_id,a.mobile,a.role_type_code,a.admin_id
           FROM admin AS a
			WHERE role_type_code IN('ficc_seller','ficc_group','ficc_team','rai_seller','rai_group')
			and (
				(a.email != "") or ( a.open_id<>'')
			)`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetRemindCompany(sellerId int, endDate string) (items []*CompanyProduct, err error) {
	o := orm.NewOrm()
	sql := ` SELECT b.end_date,b.contract_end_date,a.company_id,a.company_name,b.status,b.seller_id,b.seller_name FROM
	company a
	INNER JOIN company_product AS b ON a.company_id = b.company_id 
WHERE
	(b.seller_id = ? OR b.share_seller_id =?)
	AND (
		( b.contract_end_date = ? AND b.status = "正式" )  OR (b.end_date = ? AND b.status = "试用")
  ) ORDER BY b.status DESC `
	_, err = o.Raw(sql, sellerId, sellerId, endDate, endDate).QueryRows(&items)
	return
}

// GetAllCompanyProduct 获取所有客户产品列表（永续、正式、试用、冻结）
func GetAllCompanyProduct() (items []*CompanyProduct, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * from company_product WHERE status in ("永续","正式","试用","冻结") `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// AddCompanyProductTryOutDayTotal 将客户产品的试用天数+1
func AddCompanyProductTryOutDayTotal(companyId, productId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE  company_product
			SET
			  try_out_day_total = try_out_day_total+1
			WHERE company_id = ? AND product_id = ?`
	_, err = o.Raw(sql, companyId, productId).Exec()
	return
}

type CompanyProductItem 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"`
	DepartmentId     int       `description:"销售部门id"`
	IsSuspend        int       `description:"1:暂停,0:启用"`
	SuspendTime      time.Time `description:"暂停启用时间"`
	ApproveStatus    string    `description:"审批状态:'审批中','通过','驳回'"`
	FreezeTime       time.Time `description:"冻结时间"`
	Remark           string    `description:"备注信息"`
	CreateTime       time.Time `description:"创建时间"`
	ModifyTime       time.Time `description:"修改时间"`
	StartDate        string    `description:"开始日期"`
	EndDate          string    `description:"结束日期"`
	ContractEndDate  string    `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:"套餐类型，0：无，1：大套餐，2：小套餐"`
	IsFormal         int       `description:"是否已经转正式，0是没有转正式，1是已经转过正式"`
	TodoStatus       string    `description:"任务处理状态；枚举值：'无任务','未完成','已完成'"`
	TodoCreateTime   time.Time `description:"任务创建时间"`
	TodoApproveTime  time.Time `description:"任务审批时间"`
	TryStage         int       `description:"试用客户子标签：1未分类、2  推进、3 跟踪、4 预备"`
	IsShare          int       `description:"0:非共享用户,1:共享客户"`
	ShareSeller      string    `description:"共享销售员"`
	ShareSellerId    int       `description:"共享销售员id"`
}

// GetCompanyProductItemListByCompanyIdList
// @Description: 根据客户ID列表获取客户产品列表
// @author: Roc
// @datetime 2023-12-07 11:06:58
// @param companyIdList []int
// @param productId int
// @return items []*CompanyProductItem
// @return err error
func GetCompanyProductItemListByCompanyIdList(companyIdList []int, productId int) (items []*CompanyProductItem, err error) {
	num := len(companyIdList)
	if num <= 0 {
		return
	}
	o := orm.NewOrm()
	sql := `SELECT a.* FROM company_product as a 
         JOIN company b on a.company_id=b.company_id
         WHERE a.company_id in (` + utils.GetOrmInReplace(num) + `) AND a.product_id = ? `
	_, err = o.Raw(sql, companyIdList, productId).QueryRows(&items)

	return
}

// GetCompanyProductItemByCompanyId
// @Description: 根据客户ID获取客户产品列表
// @author: Roc
// @datetime 2023-12-07 11:06:58
// @param companyIdList []int
// @param productId int
// @return items []*CompanyProductItem
// @return err error
func GetCompanyProductItemByCompanyId(companyId int, productId int) (item *CompanyProductItem, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.* FROM company_product as a 
         JOIN company b on a.company_id=b.company_id
         WHERE a.company_id = ? AND a.product_id = ? `
	err = o.Raw(sql, companyId, productId).QueryRow(&item)

	return
}