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 }