hz_crm_api/models/company/company_product.go

package company

import (
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/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"`
	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:"关闭时间"`
}

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

type CompanyProductDetail 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:"结束日期"`
	LoseReason       string    `description:"流失原因"`
	LossTime         time.Time `description:"流失时间"`
	OpenCode         string    `description:"开放给第三方的编码，不让第三方定位我们的客户信息"`
	Scale            string    `description:"管理规模，空不填，1：:50亿以下，2：50~100亿，3：100亿以上。"`
	SpecialSurplus   string    `description:"专项调研剩余次数"`
	Points           string    `description:"公司研选服务剩余点数"`
	PermissionList   []*PermissionLookList
}

func GetCompanyProductsByCompanyId(companyId int) (items []*CompanyProductDetail, err error) {
	sql := `SELECT * FROM company_product WHERE company_id=? ORDER BY create_time ASC `
	o := orm.NewOrm()
	_, err = o.Raw(sql, companyId).QueryRows(&items)
	return
}

func GetCompanyProductById(companyProductId int) (item *CompanyProduct, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_product WHERE company_product_id=? `
	err = o.Raw(sql, companyProductId).QueryRow(&item)
	return
}

func GetCompanyProductByCompanyIdAndProductId(companyId, productId int) (item *CompanyProduct, err error) {
	o := orm.NewOrm()
	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 ModifyCompanyProduct(item *CompanyProduct) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE  company_product
			SET
			  company_name = ?,
			  source = ?,
			  reasons = ?,
			  status = ?,
			  industry_id = ?,
			  industry_name = ?,
			  seller_id = ?,
			  seller_name = ?,
			  group_id = ?,
			  department_id = ?,
			  start_date = ?,
			  end_date = ?,
			  scale = ?,
			  modify_time = NOW()
			WHERE company_product_id = ? `
	_, err = o.Raw(sql, item.CompanyName, item.Source, item.Reasons, item.Status, item.IndustryId, item.IndustryName, item.SellerId,
		item.SellerName, item.GroupId, item.DepartmentId, item.StartDate, item.EndDate, item.Scale, item.CompanyProductId).Exec()
	return
}

// 更新客户产品信息
func (companyProduct *CompanyProduct) Update(cols []string) (err error) {
	o := orm.NewOrm()
	_, err = o.Update(companyProduct, cols...)
	return
}

func DeleteCompanyProductById(companyId, productId, companyProductId int) (companyProductList []*CompanyProduct, companyReportPermissionList []*CompanyReportPermission, err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()
	searchSql := `select * FROM company_product WHERE company_product_id=? `
	_, err = to.Raw(searchSql, companyProductId).QueryRows(&companyProductList)

	sql := `DELETE FROM company_product WHERE company_product_id=? `
	_, err = to.Raw(sql, companyProductId).Exec()
	if err != nil {
		return
	}

	searchSql = `select * FROM company_report_permission WHERE company_id=? AND product_id=? `
	_, err = to.Raw(searchSql, companyId, productId).QueryRows(&companyReportPermissionList)

	sql = `DELETE FROM company_report_permission WHERE company_id=? AND product_id=? `
	_, err = to.Raw(sql, companyId, productId).Exec()
	return
}

func SuspendCompanyProductById(companyId, productId, suspend int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_product SET is_suspend=?,suspend_time=NOW(),modify_time=NOW() WHERE company_id=? AND product_id=? `
	_, err = o.Raw(sql, suspend, companyId, productId).Exec()
	return
}

func GetAllCompanyProducts() (items []*CompanyProductDetail, err error) {
	sql := `SELECT * FROM company_product `
	o := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

type CompanyProductTotalSlice struct {
	Total     int `description:"总产品数"`
	CompanyId int `description:"用户id"`
}

// 根据用户id字符串获取企业用户的产品数
func GetCountProductByCompanyIds(companyIds string) (items []*CompanyProductTotalSlice, err error) {
	//如果companyIds是空串，那么直接返回
	if companyIds == "" {
		return
	}
	o := orm.NewOrm()
	sql := `SELECT count(1) total,company_id FROM company_product WHERE company_id in (` + companyIds + `) group by company_id`
	_, err = o.Raw(sql).QueryRows(&items)
	return
	//return items2,err
}

// TryOutCompanyList 正式转试用的客户
type TryOutCompanyList 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:"客户名称"`
	CreditCode          string    `description:"社会统一信用码"`
	Status              string    `description:"客户状态"`
	TryOutTime          time.Time `description:"正式转试用时间"`
	RenewalReason       string    `description:"正式转试用后的续约情况说明"`
	RenewalTodo         string    `description:"未续约说明中的待办事项说明"`
	LastDescriptionTime string    `description:"上次添加说明时间"`
	RenewalIntention    int       `description:"是否勾选无续约意向,1:确认,0:未确认"`
	CreateTime          time.Time `description:"创建时间"`
	ModifyTime          time.Time `description:"修改时间"`
	StartDate           string    `description:"开始日期"`
	EndDate             string    `description:"结束日期"`
	ExpireDays          int       `description:"到期日期"`
}

// GetTryOutCompanyCount
// 获取今日待办消息数量
func GetTryOutCompanyCount(condition string, pars []interface{}) (total int, err error) {
	o := orm.NewOrm()
	sql := ` select count(*) count from company_product a 
join company b on a.company_id=b.company_id where 1=1  and a.status="试用" `
	sql += condition
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// GetTryOutCompanyList
// 获取今日待办消息
func GetTryOutCompanyList(condition string, pars []interface{}, startSize, pageSize int) (items []*TryOutCompanyList, err error) {
	o := orm.NewOrm()
	sql := ` select a.company_product_id,a.company_id,a.product_id,a.product_name,a.status,b.credit_code,b.company_name,a.try_out_time,a.renewal_reason,a.renewal_todo,a.last_description_time,a.create_time,a.modify_time,a.start_date,a.end_date from company_product a 
join company b on a.company_id=b.company_id where 1=1  and a.status="试用" `
	sql += condition

	sql += `  LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// EditTryOutCompanyReason 编辑续约情况说明
func EditTryOutCompanyReason(companyProduct *CompanyProduct, reason string, renewalTodo string) (err error) {
	o := orm.NewOrm()
	updateCols := make([]string, 0)
	companyProduct.RenewalReason = reason
	companyProduct.RenewalTodo = renewalTodo
	companyProduct.ModifyTime = time.Now()
	companyProduct.LastDescriptionTime = time.Now()
	updateCols = append(updateCols, "RenewalReason", "RenewalTodo", "ModifyTime", "LastDescriptionTime", "RenewalIntention")
	_, err = o.Update(companyProduct, updateCols...)
	return
}

// CompanyRenewalReason 未续约说明
type CompanyRenewalReason struct {
	CompanyRenewalReasonId int       `orm:"column(company_renewal_reason_id);pk" description:"未续约说明id"`
	CompanyId              int       `description:"客户id"`
	ProductId              int       `description:"产品id"`
	ProductName            string    `description:"产品名称"`
	CompanyName            string    `description:"客户名称"`
	TryOutTime             time.Time `description:"正式转试用时间"`
	RenewalReason          string    `description:"正式转试用后的续约情况说明"`
	RenewalTodo            string    `description:"未续约说明中的待办事项说明"`
	LastDescriptionTime    time.Time `description:"上次添加说明时间"`
	EndDays                time.Time `description:"到期时间"`
	ExpirationTimeExceeded int       `description:"超出到期时间"`
	RenewalIntention       int       `description:"是否勾选无续约意向,1:确认,0:未确认"`
	CreateTime             time.Time `description:"创建时间"`
}

// AddRenewalReason 新增续约情况说明
func AddRenewalReason(renewalReason *CompanyRenewalReason) (err error) {
	o := orm.NewOrm()
	_, err = o.Insert(renewalReason)
	return
}

// GetLastDescriptionTime 查询上次添加未续约说明时间
func GetLastDescriptionTime() (items []*TryOutCompanyList, err error) {
	o := orm.NewOrm()
	sql := "SELECT * FROM company_product ORDER BY last_description_time desc limit 1"
	err = o.Raw(sql).QueryRow(&items)
	return
}

// UpdateLastDescriptionTime 更新最后一次修改未续约说明时间
func UpdateLastDescriptionTime(companyProduct *CompanyProduct) (err error) {
	o := orm.NewOrm()
	updateCols := make([]string, 0)
	companyProduct.LastDescriptionTime = time.Now()
	updateCols = append(updateCols, "LastDescriptionTime")

	_, err = o.Update(companyProduct, updateCols...)
	return
}

// GetTryOutTimeFromOperationRecord 查询转试用时间
func GetTryOutTimeFromOperationRecord(companyId int) (item *string, err error) {
	o := orm.NewOrm()
	sql := "SELECT create_time FROM company_operation_record WHERE company_id=? AND operation='try_out' ORDER BY create_time DESC "
	err = o.Raw(sql, companyId).QueryRow(&item)
	return
}

// GetCompanyProductsByCompanyIds 根据客户id集合字符串获取所有客户产品列表
func GetCompanyProductsByCompanyIds(companyIds, condition string, pars []interface{}) (items []*CompanyProduct, err error) {
	if companyIds == `` {
		return
	}
	sql := `SELECT * FROM company_product WHERE company_id in (` + companyIds + `) `
	sql += condition
	sql += ` ORDER BY create_time ASC `
	o := orm.NewOrm()
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 更新最后一次编辑时间
func UpdateCompanyProductModifyTime(companyId, productId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_product SET modify_time=NOW() WHERE company_id=? AND product_id=? `
	_, err = o.Raw(sql, companyId, productId).Exec()
	return
}

// CompanyRemark 备注
type CompanyProductRemark struct {
	CompanyProductRemakrId int       `orm:"column(company_product_remark_id);pk" description:"备注id"`
	CompanyId              int       `description:"客户id"`
	ProductId              int       `description:"产品id"`
	ProductName            string    `description:"产品名称"`
	Remark                 string    `description:"备注"`
	CreateTime             time.Time `description:"创建时间"`
	Mark                   int       `description:"是否标记 0不是 1是"`
}

func AddCompanyRemark(remark *CompanyProductRemark) (err error) {
	o := orm.NewOrm()
	_, err = o.Insert(remark)
	return
}

// GetRemarkList 获取备注列表
func GetRemarkList(CompanyId, ProductId string) (items []*CompanyProductRemark, err error) {
	o := orm.NewOrm()
	sql := "SELECT * FROM company_product_remark WHERE company_id=? AND product_id=? ORDER BY create_time DESC"
	_, err = o.Raw(sql, CompanyId, ProductId).QueryRows(&items)
	return
}

// GetRemarkList 获取备注列表
func GetRemarkListByCompanyId(CompanyId string) (items []*CompanyProductRemark, err error) {
	o := orm.NewOrm()
	sql := "SELECT * FROM company_product_remark WHERE company_id=? ORDER BY create_time DESC"
	_, err = o.Raw(sql, CompanyId).QueryRows(&items)
	return
}

// 更新备注标记
func UpdateCompanyProductRemark(remarkId, mark int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_product_remark SET mark=? WHERE company_product_remark_id=? `
	_, err = o.Raw(sql, mark, remarkId).Exec()
	return
}

// 冻结客户
//func FreezeCompany(productId, companyId, adminId int, remark, realName string) (msg, errMsg string){
//	time.Sleep(time.Second)
//	productName := ""
//	if productId == 1 {
//		productName = "ficc"
//	} else if productId == 2 {
//		productName = "权益"
//	} else {
//		productName = "佣金客户"
//	}
//
//	cp, err := GetCompanyProductByCompanyIdAndProductId(companyId, productId)
//	if err != nil {
//		msg = "查询该客户产品异常！"
//		errMsg = "查询该客户产品异常,err:" + err.Error()
//		return
//	}
//	if cp.Status != utils.COMPANY_STATUS_TRY_OUT {
//		msg = "客户状态为：" + cp.Status + ";不可冻结"
//		errMsg = "客户状态为：" + cp.Status + ";不可冻结"
//		return
//	}
//	//查询客户的产品权限
//	companyReportPermissionList, err := GetCompanyReportPermission(companyId, productId)
//	if err != nil {
//		msg = "查询客户的产品权限异常"
//		errMsg = "查询客户的产品权限异常:" + err.Error()
//		return
//	}
//	err = CompanyFreeze(companyId, productId, remark)
//	if err != nil {
//		msg = "冻结失败！"
//		errMsg = "冻结失败，Err:" + err.Error()
//		return
//	}
//	//新增操作记录
//	{
//		approveContent := remark
//		remark := "冻结客户"
//		operation := "freeze"
//		services.AddCompanyOperationRecord(companyId, cp.SellerId, adminId, productId, adminId, cp.CompanyName,
//			productName, "超级管理员", remark, operation, approveContent, realName, "", "冻结")
//
//		//新增产品权限冻结操作日志
//		for _, companyReportPermission := range companyReportPermissionList {
//			record := company_report_permission_log.CompanyReportPermissionLog{
//				CompanyReportPermissionId: companyReportPermission.CompanyReportPermissionId,
//				CompanyId:                 companyReportPermission.CompanyId,
//				ProductId:                 productId,
//				ProductName:               companyReportPermission.ProductName,
//				SysUserId:                 adminId,
//				SysUserName:               "超级管理员",
//				Remark:                    "冻结客户",
//				Operation:                 "freeze",
//				Status:                    companyReportPermission.Status,
//				CreateTime:                time.Now(),
//			}
//			_, err = company_report_permission_log.AddCompanyReportPermissionLog(&record)
//		}
//	}
//	if err != nil {
//		msg = "编辑失败"
//		errMsg = "编辑失败，Err:" + err.Error()
//	}
//	return
//}

// SellerTryCompanyProductIds 销售对应的客户ID
type SellerTryCompanyProductIds struct {
	CompanyIds string `description:"客户ids"`
	SellerId   int    `description:"销售ID"`
}

// GetTryCompanyIdsBySellerIds 获取销售对应的试用客户ID
func GetTryCompanyIdsBySellerIds(productId int, sellerIds string) (list []*SellerTryCompanyProductIds, err error) {
	o := orm.NewOrm()
	sql := ` SELECT seller_id, GROUP_CONCAT(DISTINCT company_id SEPARATOR ',') AS company_ids  
				FROM  company_product
				WHERE product_id=?  and status = "试用" and seller_id in ` + sellerIds + ` GROUP BY seller_id 
 `
	_, err = o.Raw(sql, productId).QueryRows(&list)

	return
}

// GetCompanyProductsByCompanyIdsAndProductId 根据客户id集合字符串以及品种id获取所有客户产品列表
func GetCompanyProductsByCompanyIdsAndProductId(companyIds []int, productId int) (items []*CompanyProduct, err error) {
	lenCompanyId := len(companyIds)
	if lenCompanyId == 0 {
		return
	}
	sql := `SELECT b.* FROM company a left join company_product b on a.company_id=b.company_id WHERE a.company_id in (` + utils.GetOrmInReplace(lenCompanyId) + `) AND b.product_id = ? `
	o := orm.NewOrm()
	_, err = o.Raw(sql, companyIds, productId).QueryRows(&items)
	return
}

// GetCompanyProductByCompanyIdAndSellerId 根据客户id和所属销售id获取客户产品信息
func GetCompanyProductByCompanyIdAndSellerId(companyId, sellerId int) (item *CompanyProduct, err error) {
	o := orm.NewOrm()
	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.seller_id=? LIMIT 1 `
	err = o.Raw(sql, companyId, sellerId).QueryRow(&item)
	return
}

// GetCompanyProductsByCompanyIds 根据客户id集合字符串获取所有客户产品列表
func GetCompanyProductsBySellerId(sellerId string) (items []*CompanyProduct, err error) {
	sql := `SELECT * FROM company_product WHERE seller_id in (` + sellerId + `) `
	o := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetCompanyProductsByCondition 获取客户产品列表
func GetCompanyProductsByCondition(condition string, pars []interface{}) (list []*CompanyProduct, err error) {
	sql := `SELECT
				b.*
			FROM
				company AS a
			JOIN company_product AS b ON a.company_id = b.company_id
			WHERE
				1 = 1 `
	if condition != `` {
		sql += condition
	}
	_, err = orm.NewOrm().Raw(sql, pars).QueryRows(&list)
	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
}

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

// 根据状态获取客户列表
func GetCompanyProductListByStatus(status string, productId int) (items []*CompanyProduct, err error) {
	sql := ` SELECT * FROM company_product WHERE status = ? AND product_id = ?; `
	o := orm.NewOrm()
	_, err = o.Raw(sql, status, productId).QueryRows(&items)
	return
}

// 根据规模取客户列表
func GetCompanyProductListByScale(status string, productId int) (items []*CompanyProduct, err error) {
	sql := ` SELECT * FROM company_product WHERE scale IN (?)  AND product_id = ?; `
	o := orm.NewOrm()
	_, err = o.Raw(sql, status, productId).QueryRows(&items)
	return
}