package company import ( "fmt" "github.com/beego/beego/v2/client/orm" "github.com/rdlucklib/rdluck_tools/paging" "hongze/hz_crm_api/utils" "strconv" "strings" "time" ) type Company struct { CompanyId int `orm:"column(company_id);pk"` CompanyName string `description:"客户名称"` CompanyType int `orm:"column(type)"` CreditCode string `description:"社会统一信用码"` CompanyCode string `description:"客户编码"` Sort int `description:"优先级"` IsFeeCustomer int `description:"是否付费用户"` Country string `description:"国家编码"` Province string `description:"省"` City string `description:"市"` Address string `description:"详细地址"` Enabled int `description:"用户状态"` CreatedTime time.Time `description:"创建时间"` LastUpdatedTime time.Time `description:"最后一次阅读时间"` Seller string `description:"销售员"` SellsId int `description:"销售员id"` CompanyBelong string `description:"客户所属，ficc：ficc客户，public_offering：公募客户，partner：合作伙伴"` StartDate string `description:"合同开始日期"` EndDate string `description:"合同结束日期"` LastType int `description:"原客户标签"` IsVip int `description:"0:普通用户,1:大客户"` FirstStartDate string `description:"首次设置为试用客户开始时间"` FirstEndDate string `description:"首次设置为试用客户结束时间"` DateType int `description:"设置流失类型，1：1个月，2：2个月，3：3个月"` Remark string `description:"备注信息"` RegionType string `description:"地区类型，国内，国外"` OpenCompanyCode string `description:"开放给第三方的客户编码，不让第三方定位我们的客户信息"` Nation string `description:"所属国家"` IsHide int `description:"是否隐藏:0:不隐藏,1:隐藏"` OverseasStatus string `description:"海外客户状态:'正式','试用','关闭'"` ResetBtn int `description:"转正式和重置按钮:同步过来默认为0:显示转正式为1:显示重置为2"` } // 新增客户 func AddCompany(item *Company) (lastId int64, err error) { o := orm.NewOrm() lastId, err = o.Insert(item) return } // 根据企业id获取企业信息 func GetCompanyById(companyId int) (items *Company, err error) { o := orm.NewOrm() sql := ` SELECT * FROM company where company_id= ? ` err = o.Raw(sql, companyId).QueryRow(&items) return } type CompanySearchItem struct { CompanyId int `orm:"column(company_id);pk"` CompanyName string `description:"客户名称"` CreditCode string `description:"社会统一信用码"` CompanyCode string `description:"客户编码"` StartDate string `description:"合同开始日期"` EndDate string `description:"合同结束日期"` LoseReason string `description:"流失原因"` LossTime string `description:"流失时间"` Status string `description:"客户状态：'试用','永续','冻结','流失','正式' "` CompanyType string `description:"客户类型：ficc/权益"` ApproveStatus string `description:"审批状态：'审批中','通过','驳回' 审批状态为空时，表示没有审批申请"` SellerName string `description:"销售：吉根龙/颖丹"` SellerIds string `description:"销售ID集合，包含ficc和权益的销售id" json:"-"` Duration string `description:"时长"` FreezeTime string `description:"冻结时间"` GroupId int `description:"分组id"` GroupIds string `json:"-" description:"分组id集合，包含ficc和权益的小组id"` DepartmentId int `description:"部门id"` IsSuspend int `description:"是否暂停:1:暂停,0:启用 "` ProductId int `description:"产品id"` ProductIds string `json:"-";description:"分组id集合，包含ficc和权益的小组id"` IsMoveShow int `description:"按钮显示控制：1:显示，移动按钮，0：不显示移动按钮"` SellerId int `description:"销售id"` BtnItem *ButtonPermission FormalTime string `description:"转正时间"` FreezeStartDate string `description:"冻结开始日期"` FreezeEndDate string `description:"冻结结束日期"` RegionType string `description:"地区类型，国内，国外"` ShareSeller string `description:"共享销售员"` ShareSellerId int `description:"共享销售员id"` ShareSellerIds string `description:"共享销售员id"` IsShare int `description:"0:非共享用户,1:共享客户"` IsScrounge int `description:"是否白嫖 0不是 1是"` Nation string `description:"所属国家"` } type CompanySearchListResp struct { Paging *paging.PagingItem List []*CompanySearchItem } func GetCompanySearchList(condition string, pars []interface{}, limitParam ...int) (items []*CompanySearchItem, err error) { o := orm.NewOrm() sql := `SELECT DISTINCT a.company_id,a.company_name,a.credit_code,a.company_code,a.region_type,b.share_seller_id,b.share_seller,b.is_share, GROUP_CONCAT(b.status ORDER BY b.product_id ASC SEPARATOR '/') AS status, GROUP_CONCAT(DISTINCT b.seller_name ORDER BY b.product_id ASC SEPARATOR '/' ) AS seller_name, GROUP_CONCAT(DISTINCT b.seller_id ORDER BY b.product_id ASC SEPARATOR '/') AS seller_ids, GROUP_CONCAT(DISTINCT b.group_id ORDER BY b.product_id ASC SEPARATOR '/' ) AS group_ids, GROUP_CONCAT(DISTINCT b.product_id ORDER BY b.product_id ASC SEPARATOR '/' ) AS product_ids, b.freeze_time,b.loss_time,a.lose_reason,b.is_suspend,b.seller_id,b.product_id,b.group_id,b.formal_time,b.freeze_time, GROUP_CONCAT(b.start_date ORDER BY b.product_id ASC SEPARATOR '/') AS start_date, GROUP_CONCAT(b.end_date ORDER BY b.product_id ASC SEPARATOR '/') AS end_date, GROUP_CONCAT(DISTINCT b.product_name ORDER BY b.product_id ASC SEPARATOR '/') AS company_type, GROUP_CONCAT(DISTINCT b.approve_status ORDER BY b.product_id ASC SEPARATOR '/') AS approve_status, GROUP_CONCAT(DISTINCT b.freeze_start_date ORDER BY b.product_id ASC SEPARATOR '/') AS freeze_start_date, GROUP_CONCAT(DISTINCT b.freeze_end_date ORDER BY b.product_id ASC SEPARATOR '/') AS freeze_end_date, GROUP_CONCAT(DISTINCT b.share_seller_id ORDER BY b.product_id ASC SEPARATOR ',') AS share_seller_ids FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 ` if condition != "" { sql += condition } sql += ` GROUP BY a.company_id ORDER BY a.last_updated_time DESC LIMIT ?,? ` _, err = o.Raw(sql, pars, limitParam).QueryRows(&items) return } func GetCompanySearchCount(condition string, pars []interface{}) (count int, err error) { o := orm.NewOrm() sql := `SELECT COUNT(1) AS count FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 ` if condition != "" { sql += condition } sql += ` GROUP BY a.company_id ` qsql := `SELECT COUNT(1) AS count FROM (` + sql + `) AS t` err = o.Raw(qsql, pars).QueryRow(&count) return } type CompanyItem struct { CompanyId int `orm:"column(company_id);pk"` CompanyName string `description:"客户名称"` CreditCode string `description:"社会统一信用码"` CompanyCode string `description:"客户编码"` StartDate string `description:"合同开始日期"` EndDate string `description:"合同结束日期"` LoseReason string `description:"流失原因"` RenewalReason string `description:"续约说明"` FreezeReason string `description:"冻结理由"` LossTime string `description:"流失时间"` Status string `description:"客户状态：'试用','永续','冻结','流失','正式' "` StatusStr string `description:"客户状态：'试用','永续','冻结','流失','正式' 如果是共享客户会加上 '(共享)'" ` CompanyType string `description:"客户类型：ficc/权益"` ApproveStatus string `description:"审批状态：'待审批','已审批','驳回' 审批状态为空时，表示没有审批申请"` SellerName string `description:"销售：吉根龙/颖丹"` SellerId int `description:"销售ID"` SellerIds string `description:"销售ID集合，包含ficc和权益的销售id"` ShareSeller string `description:"共享销售员"` ShareSellerId int `description:"共享销售员id"` ShareSellerIds string `description:"共享销售员ids"` PackageTypes string `description:"套餐类型集合，包含ficc和权益的套餐类型" json:"-"` FiccPackageType int `description:"ficc的套餐类型"` ExpireDay string `description:"到期天数"` FreezeTime string `description:"冻结时间"` GroupId int `description:"分组id"` GroupIds string `description:"分组id集合，包含ficc和权益的小组id" json:"-"` DepartmentId int `description:"部门id"` IndustryName string `description:"所属行业"` IsSuspend int `description:"是否暂停:1:暂停,0:启用 "` CreatedTime string `description:"创建时间"` Source string `description:"客户来源"` Province string `description:"省"` City string `description:"市"` Address string `description:"详细地址"` Reasons string `description:"新增理由"` FreezeStartDate string `description:"冻结开始日期"` FreezeEndDate string `description:"冻结结束日期"` FreezeExpireDays int `description:"冻结到期天数"` BtnItem *ButtonPermission ProductId int `json:"-"` ProductIds string `description:"产品id集合，包含ficc和权益的产品id" json:"-"` FormalTime string `description:"转正时间"` IsShared bool `description:"是否共享客户"` RegionType string `description:"区域：国内，海外"` FiccLastViewTime string `description:"ficc最近一次阅读时间"` RaiLastViewTime string `description:"权益最近一次阅读时间"` ViewTotals string `description:"阅读次数集合，包含ficc和权益的阅读次数" json:"-"` LastViewTimes string `description:"最近一次阅读时间集合，包含ficc和权益的最近一次阅读时间" json:"-"` FiccView int `description:"Ficc报告阅读次数" json:"-"` RaiView int `description:"权益报告阅读次数" json:"-"` RoadShowTotal int `description:"累计路演次数"` TodoStatuss string `description:"任务状态"` TryStageStr string `description:"试用客户子标签"` TryStageSlice []*TryStageSliceItem `description:"试用状态相关"` AllViewTotal int `description:"总阅读数"` Deadline string `description:"未完成的todo任务的截止日期，截止目前还剩余的天数"` TodoEndTimeStr string `description:"未完成的todo任务的截止日期拼接格式"` TodoEndTime time.Time `description:"未完成的todo任务的截止日期"` TryOutDayTotals string `description:"试用天数集合，包含ficc和权益的试用天数" json:"-"` FiccTryOutDay int `description:"Ficc试用天数" json:"-"` RaiTryOutDay int `description:"权益试用天数" json:"-"` WeekViewActive int `description:"周阅读活跃: 0-七日内无阅读; 1-活跃"` IsShare int `description:"0:非共享用户,1:共享客户"` LastServiceTime string `description:"最后服务时间"` ServiceTimes int `description:"服务次数"` CloseReason string `description:"关闭原因"` CloseTime string `description:"关闭时间"` Nation string `description:"所属国家"` IsUserMaker int `description:"近四周之内是否包含决策人互动过 ，0否，1是"` LatestServiceRecord time.Time `description:"最近沟通时间"` FirstDate time.Time `description:"首次服务时间"` } type CompanyListItem struct { CompanyId int `orm:"column(company_id);pk"` CompanyName string `description:"客户名称"` CreditCode string `description:"社会统一信用码"` CompanyCode string `description:"客户编码"` StartDate string `description:"合同开始日期"` EndDate string `description:"合同结束日期"` LoseReason string `description:"流失原因"` RenewalReason string `description:"续约说明"` FreezeReason string `description:"冻结理由"` LossTime string `description:"流失时间"` Status string `description:"客户状态：'试用','永续','冻结','流失','正式' "` CompanyType string `description:"客户类型：ficc/权益"` ApproveStatus string `description:"审批状态：'待审批','已审批','驳回' 审批状态为空时，表示没有审批申请"` SellerName string `description:"销售：吉根龙/颖丹"` SellerId int `description:"销售ID"` SellerIds string `description:"销售ID集合，包含ficc和权益的销售id"` FiccPackageType int `description:"ficc的套餐类型"` ExpireDay string `description:"到期天数"` FreezeTime string `description:"冻结时间"` GroupId int `description:"分组id"` GroupIds string `description:"分组id集合，包含ficc和权益的小组id" json:"-"` DepartmentId int `description:"部门id"` IndustryName string `description:"所属行业"` IsSuspend int `description:"是否暂停:1:暂停,0:启用 "` CreatedTime string `description:"创建时间"` Source string `description:"客户来源"` Province string `description:"省"` City string `description:"市"` Address string `description:"详细地址"` Reasons string `description:"新增理由"` FreezeStartDate string `description:"冻结开始日期"` FreezeEndDate string `description:"冻结结束日期"` FreezeExpireDays int `description:"冻结到期天数"` BtnItem *ButtonPermission ProductId int `json:"-"` FormalTime string `description:"转正时间"` IsShared bool `description:"是否共享客户"` RegionType string `description:"区域：国内，海外"` FiccView int `description:"Ficc报告阅读次数"` RaiView int `description:"权益报告阅读次数"` FiccLastViewTime string `description:"ficc最近一次阅读时间"` RaiLastViewTime string `description:"权益最近一次阅读时间"` RoadShowTotal int `description:"累计路演次数"` TodoStatus bool `description:"是否存在进行中任务，true：存在待处理的任务"` CanConfirm bool `description:"是否允许完成任务，true：允许完成任务"` HiddenConfirm bool `description:"是否隐藏完成任务按钮，true：隐藏完成任务按钮"` HiddenCreate bool `description:"是否隐藏新增/编辑按钮，true：隐藏新增/编辑按钮"` TodoButtonColor string `description:"任务按钮颜色：red，green，gray"` TryStageSlice []*TryStageSliceItem `description:"试用状态相关"` AllViewTotal int `description:"总阅读数"` Deadline string `description:"未完成的todo任务的截止日期，截止目前还剩余的天数"` FiccTryOutDay int `description:"Ficc总试用天数"` RaiTryOutDay int `description:"权益总试用天数"` WeekViewActive int `description:"周阅读活跃: 0-七日内无阅读; 1-活跃"` IsShare int `description:"0:非共享用户,1:共享客户"` ShareSeller string `description:"共享销售员"` ShareSellerId int `description:"共享销售员id"` ShareSellerIds string `description:"共享销售员ids"` LastServiceTime string `description:"最后服务时间"` ServiceTimes int `description:"服务次数"` CloseReason string `description:"关闭客户原因"` CloseTime string `description:"关闭客户时间"` IsScrounge int `description:"是否白嫖 0不是 1是"` IsUserMaker int `description:"近四周之内是否包含决策人互动过 ，0否，1是"` } type TryStageSliceItem struct { ProductId int `description:"产品ID"` TryStage int `description:"试用客户子标签1未分类、2 推进、3 跟踪、4 预备"` HasPermission bool `description:"是否有权限设置标签"` } type CompanyListResp struct { Paging *paging.PagingItem List []*CompanyListItem StatusCount []*CompanyStatus TryStageCount []*CompanyTryStageCount AddCompanyBtn bool IsShareGroup bool `description:"是否咨询组销售"` Status int `description:"是否咨询组销售"` } func GetCompanyListCount(condition string, pars []interface{}) (count int, err error) { o := orm.NewOrm() sql := `SELECT COUNT(DISTINCT b.company_id) AS count FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&count) return } func GetCompanyList(condition, status, sortStr string, pars []interface{}, startSize, pageSize int) (items []*CompanyItem, err error) { o := orm.NewOrm() // 七天前的时间-用于查询周阅读活跃, 此处的需求是 // FICC客户、共享客户: 以FICC最后一次阅读时间作为参考, 七日内有阅读量则为活跃 // 权益客户: 以权益最后一次阅读时间作为参考 t := time.Now().Local().AddDate(0, 0, -7).Format(utils.FormatDate) sql := `SELECT a.company_id,a.company_name,a.credit_code,a.company_code,a.created_time,a.province,a.city,a.address,a.region_type,b.group_id,b.road_show_total,b.is_share,b.is_user_maker, CASE WHEN GROUP_CONCAT(b.status) LIKE "%永续%" THEN 1 WHEN GROUP_CONCAT(b.status) LIKE "%正式%" THEN 2 WHEN GROUP_CONCAT(b.status) LIKE "%试用%" THEN 3 WHEN GROUP_CONCAT(b.status) LIKE "%冻结%" THEN 4 WHEN GROUP_CONCAT(b.status) LIKE "%流失%" THEN 5 ELSE 10 END as sort_status, CASE WHEN GROUP_CONCAT(b.try_stage) LIKE "%2%" THEN 1 WHEN GROUP_CONCAT(b.try_stage) LIKE "%3%" THEN 2 WHEN GROUP_CONCAT(b.try_stage) LIKE "%4%" THEN 3 WHEN GROUP_CONCAT(b.try_stage) LIKE "%1%" THEN 4 ELSE 10 END as sort_try_stage, CASE WHEN GROUP_CONCAT(b.todo_status) LIKE "%未完成%" AND GROUP_CONCAT(b.todo_end_time) is not null THEN GROUP_CONCAT(b.todo_end_time) ELSE "9999-01-01" END as sort_todo_end_time, GROUP_CONCAT(CASE WHEN b.is_share = 1 THEN CONCAT(b.status, '(共享)') ELSE b.status END ORDER BY b.product_id ASC SEPARATOR '/') AS status_str, GROUP_CONCAT(b.status ORDER BY b.product_id ASC SEPARATOR '/') AS status, GROUP_CONCAT(DISTINCT b.seller_name ORDER BY b.product_id ASC SEPARATOR '/') AS seller_name, GROUP_CONCAT(DISTINCT b.seller_id ORDER BY b.product_id ASC SEPARATOR '/') AS seller_ids, GROUP_CONCAT(DISTINCT b.package_type ORDER BY b.product_id ASC SEPARATOR '/') AS package_types, GROUP_CONCAT(DISTINCT b.industry_name ORDER BY b.product_id ASC SEPARATOR '/') AS industry_name, GROUP_CONCAT(DISTINCT b.product_name ORDER BY b.product_id ASC SEPARATOR '/') AS company_type, GROUP_CONCAT(DISTINCT b.product_id ORDER BY b.product_id ASC SEPARATOR '/') AS product_ids, GROUP_CONCAT(DISTINCT b.view_total ORDER BY b.product_id ASC SEPARATOR '/') AS view_totals, GROUP_CONCAT(DISTINCT b.last_view_time ORDER BY b.product_id ASC SEPARATOR '/') AS last_view_times, GROUP_CONCAT(b.start_date ORDER BY b.product_id ASC SEPARATOR '/') AS start_date, GROUP_CONCAT(b.end_date ORDER BY b.product_id ASC SEPARATOR '/') AS end_date, GROUP_CONCAT(DISTINCT b.loss_time ORDER BY b.product_id ASC SEPARATOR '/') AS loss_time, GROUP_CONCAT(DISTINCT b.lose_reason ORDER BY b.product_id ASC SEPARATOR '/') AS lose_reason, GROUP_CONCAT(DISTINCT b.close_time ORDER BY b.product_id ASC SEPARATOR '/') AS close_time, GROUP_CONCAT(DISTINCT b.close_reason ORDER BY b.product_id ASC SEPARATOR '/') AS close_reason, GROUP_CONCAT(DISTINCT b.source ORDER BY b.product_id ASC SEPARATOR '/') AS source, GROUP_CONCAT(DISTINCT b.reasons ORDER BY b.product_id ASC SEPARATOR '/') AS reasons, GROUP_CONCAT(DISTINCT b.approve_status ORDER BY b.product_id ASC SEPARATOR '/') AS approve_status, GROUP_CONCAT(b.try_stage ORDER BY b.product_id ASC SEPARATOR '/') AS try_stage_str, GROUP_CONCAT(DISTINCT b.group_id ORDER BY b.product_id ASC SEPARATOR '/') AS group_ids, GROUP_CONCAT(DISTINCT b.todo_status ORDER BY b.product_id ASC SEPARATOR '/') AS todo_statuss, GROUP_CONCAT(DISTINCT b.todo_end_time ORDER BY b.product_id ASC SEPARATOR '/') AS todo_end_time_str, GROUP_CONCAT(DISTINCT b.try_out_day_total ORDER BY b.product_id ASC SEPARATOR '/') AS try_out_day_totals, b.is_suspend, b.freeze_start_date, b.freeze_end_date, b.seller_id, b.product_id, b.formal_time, b.freeze_time,b.freeze_reason,b.renewal_reason, b.view_total, b.last_view_time, GROUP_CONCAT(DISTINCT b.share_seller ORDER BY b.product_id ASC SEPARATOR '/') AS share_seller, GROUP_CONCAT(DISTINCT b.share_seller_id ORDER BY b.product_id ASC SEPARATOR ',') AS share_seller_ids, b.share_seller_id , MAX(b.last_view_time) as max_last_view_time, MIN(b.last_view_time) as min_last_view_time, MAX(b.end_date) as max_end_date, MIN(b.end_date) as min_end_date, SUM(b.view_total) as all_view_total, SUM(b.try_out_day_total) as all_try_out_day_total, IF(LEFT(GROUP_CONCAT(DISTINCT b.last_view_time ORDER BY b.product_id), 19) >= "` + t + `", 1, 0) AS week_view_active, a.nation, c.latest_service_record, d.first_date FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id LEFT JOIN ( SELECT company_id, MAX(create_time) AS latest_service_record FROM company_service_record GROUP BY company_id ) AS c ON a.company_id = c.company_id LEFT JOIN ( SELECT company_id,MIN(start_date) AS first_date FROM company_contract WHERE status=1 GROUP BY company_id ) AS d ON a.company_id = d.company_id WHERE a.enabled=1 ` if condition != "" { sql += condition } //sortStr := `` //如果没有传入排序字段，那么按照状态排序 if sortStr == "" { if status == "全部" || status == utils.COMPANY_STATUS_TRY_OUT { //试用 sortStr = " ORDER BY a.created_time DESC " } else if status == utils.COMPANY_STATUS_FORMAL { //正式 // 正式客户默认按照周阅读活跃ASC, 同为不活跃则按照转正时间DESC sortStr = " ORDER BY week_view_active ASC, b.formal_time DESC" //sortStr = " ORDER BY b.formal_time DESC " } else if status == utils.COMPANY_STATUS_FREEZE { //冻结 sortStr = " ORDER BY b.freeze_time DESC " } else if status == utils.COMPANY_STATUS_LOSE { //流失 sortStr = " ORDER BY b.loss_time DESC " } else if status == utils.COMPANY_STATUS_CLOSE { //关闭 sortStr = " ORDER BY b.close_time DESC " } else { sortStr = " ORDER BY a.created_time DESC " } } sql += ` GROUP BY a.company_id ` + sortStr + ` LIMIT ?,? ` _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items) return } // GetCountCygxArticleHistoryRecordByCompanyName 查研观向阅读次数 func GetCountCygxArticleHistoryRecordByCompanyName(companyName string) (count int, err error) { o := orm.NewOrm() sql := `SELECT count(1) total FROM cygx_article_history_record WHERE company_name=? ` _ = o.Raw(sql, companyName).QueryRow(&count) return } // GetCountUserViewHistoryByCompanyName 晨报阅读次数 func GetCountUserViewHistoryByCompanyName(companyName string) (count int, err error) { o := orm.NewOrm() sql := `SELECT count(1) total FROM user_view_history WHERE company_name=? ` _ = o.Raw(sql, companyName).QueryRow(&count) return } // GetCountReportViewRecordByCompanyName 日评阅读次数 func GetCountReportViewRecordByCompanyName(companyName string) (count int, err error) { o := orm.NewOrm() sql := `SELECT count(1) total FROM report_view_record WHERE company_name=? ` _ = o.Raw(sql, companyName).QueryRow(&count) return } // GetCountAdvisoryUserChartArticleRecordByCompanyName 每日资讯阅读次数 func GetCountAdvisoryUserChartArticleRecordByCompanyName(companyName string) (count int, err error) { o := orm.NewOrm() sql := `SELECT count(1) total FROM advisory_user_chart_article_record WHERE company_name=? ` _ = o.Raw(sql, companyName).QueryRow(&count) return } type CompanyStatus struct { Status string `description:"客户状态：'试用','永续','冻结','流失','正式','试用-活跃','试用-非活跃' "` Count int `description:"数量 "` } type CompanyTryStageCount struct { TryStageStr string `description:"试用客户子标签：未分类、推进、跟踪、预备"` TryStage int `description:"试用客户子标签：1未分类、2 推进、3 跟踪、4 预备"` Count int `description:"数量 "` } func GetCompanyStatusCount(condition string, pars []interface{}) (items []*CompanyStatus, err error) { o := orm.NewOrm() sql := ` SELECT b.status,COUNT(DISTINCT a.company_id) AS count FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 AND b.status IN('正式','试用','冻结','永续','关闭') ` if condition != "" { sql += condition } //sql += `GROUP BY b.status ` //_, err = o.Raw(sql, pars).QueryRows(&items) sql += ` GROUP BY b.status ` _, err = o.Raw(sql, pars).QueryRows(&items) return } // GetCompanyTryStageCount 获取试用客户标签的总数 func GetCompanyTryStageCount(condition string, pars []interface{}) (items []*CompanyTryStageCount, err error) { o := orm.NewOrm() sql := ` SELECT b.try_stage,COUNT(DISTINCT a.company_id) AS count FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 AND b.status = "试用"` if condition != "" { sql += condition } //sql += `GROUP BY b.status ` //_, err = o.Raw(sql, pars).QueryRows(&items) sql += ` GROUP BY b.try_stage ` _, err = o.Raw(sql, pars).QueryRows(&items) return } // GetCompanyTryOutStatusCount 获取试用状态的客户数 func GetCompanyTryOutStatusCount(condition string, pars []interface{}) (item *CompanyStatus, err error) { o := orm.NewOrm() sql := ` SELECT b.status,COUNT(DISTINCT a.company_id) AS count FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 AND b.status = "试用" ` if condition != "" { sql += condition } //sql += `GROUP BY b.status ` //_, err = o.Raw(sql, pars).QueryRows(&items) err = o.Raw(sql, pars).QueryRow(&item) return } func GetCompanyStatusLoseCount(condition string, pars []interface{}) (items *CompanyStatus, err error) { o := orm.NewOrm() sql := ` SELECT b.status,COUNT(DISTINCT a.company_id) AS count FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 AND b.status='流失' ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&items) return } func GetCompanyStatusFreezeCount(condition string, pars []interface{}) (items *CompanyStatus, err error) { o := orm.NewOrm() sql := ` SELECT b.status,COUNT(DISTINCT a.company_id) AS count FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 AND b.status='冻结' ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&items) return } func GetCompanyStatusForverCount(condition string, pars []interface{}) (items *CompanyStatus, err error) { o := orm.NewOrm() sql := ` SELECT b.status,COUNT(DISTINCT a.company_id) AS count FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 AND b.status='永续' ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&items) return } // 新增客户请求参数 type CompanyAddReq struct { CompanyName string `description:"客户名称"` CreditCode string `description:"社会统一信用码"` CompanyCode string `description:"客户编码"` Province string `description:"省"` City string `description:"市"` Status string `description:"客户状态，'试用','永续','冻结','流失','正式','潜在'"` CompanyType string `description:"客户类型，ficc/权益"` IndustryId int `description:"所属行业id"` Source string `description:"来源"` SellsId int `description:"销售员id"` Reasons string `description:"新增理由，备注"` PermissionIds string `description:"权限id，多个用英文逗号隔开"` RegionType string `description:"地区类型，国内，海外"` UserId int `description:"联系人ID"` Scale string `description:"管理规模，空不填，1：:50亿以下，2：50~100亿，3：100亿以上。"` Nation string `description:"所属国家"` } // 新增客户请求参数 type CompanyAddResp struct { CompanyId int `description:"客户ID"` } func GetCompanyCode() (companyCode string, err error) { var num int o := orm.NewOrm() sql := `SELECT COUNT(1) AS num FROM company WHERE created_time >=? ` err = o.Raw(sql, time.Now().Format(utils.FormatDate)).QueryRow(&num) if err != nil { return } fmt.Println("num", num) companyCode = "KH" + time.Now().Format("20060102") + fmt.Sprintf("%03d", num) return } type CompanyDetail struct { CompanyId int `orm:"column(company_id);pk"` CompanyName string `description:"客户名称"` CreditCode string `description:"社会统一信用码"` CompanyCode string `description:"客户编码"` Province string `description:"省"` City string `description:"市"` Address string `description:"详细地址"` RegionType string `description:"地区类型，1：国内，2：国外"` OpenCompanyCode string `description:"开放给第三方的客户编码，不让第三方定位我们的客户信息"` IsScrounge int `description:"是否白嫖 0不是 1是"` Nation string `description:"所属国家"` } type CompanyDetailResp struct { Item *CompanyDetail `description:"客户信息"` FiccItem *CompanyProductDetail `description:"Ficc客户产品详情"` RaiItem *CompanyProductDetail `description:"权益客户产品详情"` CreateAuth int `description:"创建人：1：ficc,2:权益"` ProductName string `description:"部门名称：ficc/权益/admin"` IsButtonShow int `description:"1:显示编辑和删除按钮，0:不显示"` IsContractButtonShow bool `description:"true:显示联系人操作按钮，新增、编辑、导入和删除按钮，false:不显示"` IsUserYanXuanButtonShow bool `description:"true:显示联系人开通研选试用按钮，false:不显示"` BtnItem *ButtonDetailPermission } // 客户权限列表 type CompanyPermissionResp struct { FiccPermissionList []*PermissionLookList `description:"Ficc权限列表"` PermissionList []*PermissionLookList `description:"权益权限列表"` } func GetCompanyDetailById(companyId int) (item *CompanyDetail, err error) { o := orm.NewOrm() sql := `SELECT * FROM company WHERE company_id =? ` err = o.Raw(sql, companyId).QueryRow(&item) return } func GetCompanyCountByCompanyName(companyName string) (count int, err error) { o := orm.NewOrm() sql := ` SELECT COUNT(1) AS COUNT FROM company AS a WHERE a.company_name=? ` err = o.Raw(sql, companyName).QueryRow(&count) return } func GetCompanyCountByCreditCode(creditCode string) (count int, err error) { o := orm.NewOrm() sql := ` SELECT COUNT(1) AS COUNT FROM company AS a WHERE a.credit_code=? ` err = o.Raw(sql, creditCode).QueryRow(&count) return } func GetCompanyCountByCompanyNameAndCompanyId(companyId int, companyName string) (count int, err error) { o := orm.NewOrm() sql := ` SELECT COUNT(1) AS COUNT FROM company AS a WHERE a.company_id<>? AND a.company_name=? ` err = o.Raw(sql, companyId, companyName).QueryRow(&count) return } func GetCompanyCountByCreditCodeAndCompanyId(companyId int, creditCode string) (count int, err error) { o := orm.NewOrm() sql := ` SELECT COUNT(1) AS COUNT FROM company AS a WHERE a.company_id<>? AND a.credit_code=? ` err = o.Raw(sql, companyId, creditCode).QueryRow(&count) return } // GetCompanyByCreditCode 根据社会信用码获取客户信息 func GetCompanyByCreditCode(creditCode string) (companyInfo *Company, err error) { o := orm.NewOrm() sql := ` SELECT * FROM company AS a WHERE a.credit_code=? ` err = o.Raw(sql, creditCode).QueryRow(&companyInfo) return } // GetCompanyDetailByCreditCode 根据社会信用码获取客户详情接口 func GetCompanyDetailByCreditCode(creditCode string) (companyDetail *CompanyDetail, err error) { o := orm.NewOrm() sql := ` SELECT * FROM company AS a WHERE a.credit_code=? ` err = o.Raw(sql, creditCode).QueryRow(&companyDetail) return } func GetCompanyTryOutCount(status string, sellerId int) (count int, err error) { o := orm.NewOrm() sql := ` SELECT COUNT(DISTINCT a.company_id) AS count FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE b.status=? AND b.seller_id=? ` err = o.Raw(sql, status, sellerId).QueryRow(&count) return } // 获取权益销售开通的非研选试用客户数量 func GetRaiCompanyTryOutCountNoResearch(sellerId int) (count int, err error) { o := orm.NewOrm() sql := `SELECT COUNT( DISTINCT a.company_id ) AS count FROM company AS a INNER JOIN company_product AS b ON a.company_id = b.company_id INNER JOIN company_report_permission AS p ON p.company_id = a.company_id WHERE b.status = '试用' AND p.status = '试用' AND b.seller_id = ? AND p.chart_permission_id NOT IN(31,52) ` err = o.Raw(sql, sellerId).QueryRow(&count) return } type CompanySearchResp struct { List []*CompanyItem } func GetCompanyBySearch(keyWord string) (item []*CompanyItem, err error) { o := orm.NewOrm() var sql string if keyWord == "##" { sql = ` SELECT * FROM company ORDER BY RAND() LIMIT 5 ` } else { sql = `SELECT * FROM company WHERE company_name LIKE '%` + keyWord + `%' LIMIT 50` } _, err = o.Raw(sql).QueryRows(&item) return } // 编辑客户请求参数 type CompanyEditReq struct { CompanyId int `description:"客户id"` CompanyName string `description:"客户名称"` CreditCode string `description:"社会统一信用码"` Province string `description:"省"` City string `description:"市"` Nation string `description:"所属国家"` Products []*CompanyProductEditReq `description:"产品信息"` } // 编辑客户信息请求参数 type CompanyInfoEditReq struct { CompanyId int `description:"客户id"` CompanyName string `description:"客户名称"` CreditCode string `description:"社会统一信用码"` Province string `description:"省"` City string `description:"市"` IndustryId int `description:"所属行业id"` IndustryName string `description:"所属行业id"` Source string `description:"来源"` } type CompanyProductEditReq struct { CompanyProductId int `description:"客户产品id"` Status string `description:"客户状态，试用,永续"` CompanyType string `description:"客户类型，ficc/权益"` IndustryId int `description:"所属行业id"` Source string `description:"来源"` SellsId int `description:"销售员id"` Reasons string `description:"新增理由"` PermissionIds string `description:"权限id，多个用英文逗号隔开"` Scale string `description:"管理规模，空不填，1：:50亿以下，2：50~100亿，3：100亿以上。"` } type CompanyFuzzySearch struct { CompanyId int `orm:"column(company_id);pk"` CompanyName string `description:"客户名称"` CreditCode string `description:"社会统一信用码"` CompanyCode string `description:"客户编码"` } // 新增客户请求参数 type CompanyFuzzySearchReq struct { List []*CompanyFuzzySearch } func GetCompanyFuzzySearch(keyWord string) (items []*CompanyFuzzySearch, err error) { o := orm.NewOrm() sql := `SELECT * FROM company WHERE company_name LIKE '%` + keyWord + `%' ` _, err = o.Raw(sql).QueryRows(&items) return } // 修改客户 func ModifyCompany(item *Company) (err error) { o := orm.NewOrm() sql := `UPDATE company SET company_name = ?, credit_code = ?, address = ?, last_updated_time = NOW(), province = ?, city = ?, nation=? WHERE company_id =?` _, err = o.Raw(sql, item.CompanyName, item.CreditCode, item.Address, item.Province, item.City, item.Nation, item.CompanyId).Exec() return } // 更新客户基础信息 func (company *Company) Update(cols []string) (err error) { o := orm.NewOrm() _, err = o.Update(company, cols...) return } type DeletePptReq struct { CompanyId int `description:"客户id"` } func DeleteCompanyById(companyId int) (err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { _ = to.Rollback() } else { _ = to.Commit() } }() sql := `DELETE FROM company_product WHERE company_id=? ` _, err = to.Raw(sql, companyId).Exec() if err != nil { return } sql = `DELETE FROM company_report_permission WHERE company_id=? ` _, err = to.Raw(sql, companyId).Exec() if err != nil { return } //联系人表 sql = `DELETE FROM wx_user WHERE company_id=? ` _, err = to.Raw(sql, companyId).Exec() if err != nil { return } //联系人 与 销售 关系表 sql = `DELETE FROM user_seller_relation WHERE company_id=? ` _, err = to.Raw(sql, companyId).Exec() if err != nil { return } //sql = `DELETE FROM company_operation_record WHERE company_id=? ` //_, err = to.Raw(sql, companyId).Exec() //if err != nil { // return //} sql = `DELETE FROM company_contract WHERE company_id=? ` _, err = to.Raw(sql, companyId).Exec() if err != nil { return } sql = `DELETE FROM company_contract_permission WHERE company_id=? ` _, err = to.Raw(sql, companyId).Exec() if err != nil { return } sql = `DELETE FROM company WHERE company_id=? ` _, err = to.Raw(sql, companyId).Exec() return } type CompanyReceiveItem struct { CompanyId int `orm:"column(company_id);pk"` CompanyName string `description:"客户名称"` StartDate string `description:"合同开始日期"` EndDate string `description:"合同结束日期"` Status string `description:"客户状态：'试用','永续','冻结','流失','正式' "` ProductName string `description:"客户类型：ficc/权益"` SellerName string `description:"销售：吉根龙/颖丹"` CreatedTime string `description:"创建时间"` ExpireDay string `description:"到期天数"` IndustryName string `description:"行业"` } type CompanyReceiveListResp struct { Paging *paging.PagingItem List []*CompanyReceiveItem } func GetCompanyReceiveListCount(condition string, pars []interface{}, productId, receiveProductId int) (count int, err error) { o := orm.NewOrm() sql := `SELECT COUNT(DISTINCT a.company_id ) AS COUNT FROM company AS a LEFT JOIN wx_user AS b ON a.company_id=b.company_id LEFT JOIN company_product AS c ON a.company_id=c.company_id WHERE a.enabled=1 AND c.product_id=? AND c.company_id NOT IN (SELECT company_id FROM company_product WHERE product_id=?) ` if condition != "" { sql += condition } err = o.Raw(sql, receiveProductId, productId, pars).QueryRow(&count) return } func GetCompanyReceiveList(condition string, pars []interface{}, productId, receiveProductId, startSize, pageSize int) (items []*CompanyReceiveItem, err error) { o := orm.NewOrm() sql := `SELECT c.company_id,a.company_name,c.product_name,c.industry_name,c.seller_name,c.status,c.start_date,c.end_date,a.created_time FROM company AS a LEFT JOIN wx_user AS b ON a.company_id=b.company_id LEFT JOIN company_product AS c ON a.company_id=c.company_id WHERE a.enabled=1 AND c.product_id=? AND c.company_id NOT IN (SELECT company_id FROM company_product WHERE product_id=?) ` if condition != "" { sql += condition } sql += ` GROUP BY a.company_id ORDER BY a.last_updated_time DESC LIMIT ?,? ` _, err = o.Raw(sql, receiveProductId, productId, pars, startSize, pageSize).QueryRows(&items) return } // 编辑客户请求参数 type CompanyReceiveReq struct { CompanyId int `description:"客户id"` Status string `description:"客户状态，试用,永续"` IndustryId int `description:"所属行业id"` Source string `description:"来源"` SellsId int `description:"销售员id,流失状态下直接领取，请传 0"` Reasons string `description:"新增理由"` PermissionIds string `description:"权限id，多个用英文逗号隔开"` CompanyType string `description:"客户类型"` } // 移动销售 type MoveSellerReq struct { CompanyId int `description:"客户id"` SellsId int `description:"销售员id"` CompanyType string `description:"客户类型"` } // 修改试用客户标签 type UpdateTryStageReq struct { CompanyId int `description:"客户id"` ProductId int `description:"产品ID"` TryStage int `description:"试用客户子标签：1未分类、2 推进、3 跟踪、4 预备"` } // MoveSeller 移动客户到另外的销售（销售领取客户时使用该接口） func MoveSeller(companyId, productId, sellerId, groupId, departmentId int, sellerName string) (items []*CompanyReportPermission, startDateTime, endDateTime time.Time, err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { _ = to.Rollback() } else { _ = to.Commit() } }() startDateTime = time.Now() endDateTime = time.Now().AddDate(0, 2, 0) startDate := startDateTime.Format(utils.FormatDate) endDate := endDateTime.Format(utils.FormatDate) //更改 客户状态为 试用 sql := `UPDATE company SET start_date=?,end_date=?,last_updated_time=NOW() WHERE company_id=? ` _, err = to.Raw(sql, startDate, endDate, companyId).Exec() if err != nil { return } //更改 客户产品 状态为 试用 //重置tryout时间，上次修改未续约说明时间 sql = `UPDATE company_product SET status='试用',try_out_time=NULL,last_description_time=NULL,is_suspend=0,start_date=?,end_date=?,seller_id=?,seller_name=?,group_id=?,department_id=?,modify_time=NOW(),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 = to.Raw(sql, startDate, endDate, sellerId, sellerName, groupId, departmentId, companyId, productId).Exec() if err != nil { return } //修改原有的 客户产品权限 的服务时间 items = make([]*CompanyReportPermission, 0) sql = `SELECT * FROM company_report_permission WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, companyId, productId).QueryRows(&items) for _, pv := range items { cpLog := new(CompanyPermissionLog) cpLog.CompanyId = companyId cpLog.ChartPermissionId = pv.ChartPermissionId cpLog.CreateTime = time.Now() cpLog.SysUserId = sellerId cpLog.SysUserName = sellerName cpLog.StartDate = pv.StartDate cpLog.EndDate = pv.EndDate cpLog.ProductId = productId cpLog.ProductName = pv.ProductName go AddCompanyPermissionLog(cpLog) } sql = `UPDATE company_report_permission SET status='试用',start_date=?,end_date=?,modify_time=NOW() WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, startDate, endDate, companyId, productId).Exec() //修改 该联系人 与 当前销售的关系 sql = `UPDATE user_seller_relation SET seller_id=?,seller = ? ,modify_time=NOW() WHERE company_id = ? AND product_id=?` _, err = to.Raw(sql, sellerId, sellerName, companyId, productId).Exec() //如果是权益客户，则把对应的主观客观一起开通 if productId == 2 { //获取公司现有的权限，并通过现有的权限找出对应的主观客观权限 oldPermissionId, tmpErr := GetCompanyPermissionIdById(companyId) if tmpErr != nil { err = tmpErr return } newPermissionId, tmpErr := GetPermissionIdById(oldPermissionId) if tmpErr != nil { err = tmpErr return } permissionArr := strings.Split(newPermissionId, ",") for _, v := range permissionArr { permissionId, tmpErr := strconv.Atoi(v) if tmpErr != nil { err = tmpErr return } //判断这个权限是否存在，如果不存在则新增 count, tmpErr := GetCompanyPermissionCheck(companyId, permissionId) if tmpErr != nil { err = tmpErr return } if count == 0 { permission := new(CompanyReportPermission) permission.CompanyId = companyId permission.ReportPermissionId = permissionId permission.CreatedTime = time.Now() permission.LastUpdatedTime = time.Now() permission.ChartPermissionId = permissionId permission.StartDate = startDate permission.EndDate = endDate permission.ProductId = productId permission.ProductName = "权益" permission.ModifyTime = time.Now() permission.Status = "试用" err = AddCompanyReportPermission(permission) if err != nil { return } items = append(items, permission) } } } // 将历史的任务给标记删除掉 sql = `UPDATE company_todo SET is_delete=1,modify_time=NOW() WHERE company_id=? AND product_id=? AND is_delete = 0 ` _, err = to.Raw(sql, companyId, productId).Exec() if err != nil { return } return } func GetExportCompanyIds(condition string, pars []interface{}) (company_id string, err error) { o := orm.NewOrm() sql := `SELECT GROUP_CONCAT(DISTINCT b.company_id SEPARATOR ',') AS company_id FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id LEFT JOIN wx_user AS c ON a.company_id=c.company_id WHERE 1=1 and a.enabled=1 ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&company_id) return } type ExportUser struct { RealName string Mobile string Email string Position string IsMaker int DepartmentName string CompanyId int IsSubscribe int `description:"是否关注了公众号(弘则研究)"` } func GetExportUser(companyId string) (items []*ExportUser, err error) { o := orm.NewOrm() sql := `SELECT a.real_name,a.mobile,a.email,a.position,a.is_maker,department_name,a.company_id,b.subscribe AS is_subscribe FROM wx_user AS a LEFT JOIN (SELECT * FROM user_record WHERE create_platform = 1 GROUP BY user_id) AS b ON a.user_id = b.user_id WHERE a.company_id<>1 AND a.company_id IN(` + companyId + `) GROUP BY a.user_id ORDER BY a.company_id ASC ` _, err = o.Raw(sql).QueryRows(&items) return } type ExportCompanyProduct struct { CompanyName string ProductId int ProductName string IndustryName string SellerName string Status string StartDate time.Time EndDate time.Time CreateTime string Source string CreditCode string RegionType string LossTime string IsSuspend int } func GetExportCompanyProduct(companyId int) (items []*ExportCompanyProduct, err error) { o := orm.NewOrm() sql := ` SELECT a.company_name,b.product_id,b.product_name,b.industry_name,b.seller_name,b.status,b.start_date,b.end_date,b.create_time, b.source,a.credit_code,a.region_type,b.loss_time, b.is_suspend FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.company_id=? ORDER BY b.product_id ASC ` _, err = o.Raw(sql, companyId).QueryRows(&items) return } func GetExportPermissionName(companyId, productId int) (chart_permission_name string, err error) { o := orm.NewOrm() sql := ` SELECT GROUP_CONCAT(DISTINCT c.permission_name ORDER BY sort ASC SEPARATOR '/') AS chart_permission_name FROM company_product AS a INNER JOIN company_report_permission AS b ON a.company_id=b.company_id AND a.product_id=b.product_id INNER JOIN chart_permission AS c ON b.chart_permission_id=c.chart_permission_id AND b.product_id=c.product_id WHERE a.company_id=? AND a.product_id=? ` err = o.Raw(sql, companyId, productId).QueryRow(&chart_permission_name) return } // 移动销售 type SuspendReq struct { CompanyId int `description:"客户id"` } // MoveCompanySeller 移动客户销售 func MoveCompanySeller(companyId, productId, sellerId, groupId, departmentId int, sellerName string) (err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { _ = to.Rollback() } else { _ = to.Commit() } }() if sellerName != "" { sql := `UPDATE company_product SET seller_id=?,seller_name=?,group_id=?,department_id=?,modify_time=NOW(),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 = to.Raw(sql, sellerId, sellerName, groupId, departmentId, companyId, productId).Exec() if err != nil { return } } else { sql := `UPDATE company_product SET seller_id=?,group_id=?,department_id=?,modify_time=NOW(),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 = to.Raw(sql, sellerId, groupId, departmentId, 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 = to.Raw(sql, companyId, productId).Exec() if err != nil { return } return } // UpdateCompanyTryStage 更新试用客户标签 func UpdateCompanyTryStage(companyId, productId, tryStage int) (err error) { o := orm.NewOrm() sql := `UPDATE company_product SET try_stage=? WHERE company_id=? AND product_id=? and status="试用"` _, err = o.Raw(sql, tryStage, companyId, productId).Exec() return } func CheckCompanyProductByName(companyName string, productId int) (item *CompanyProductDetail, err error) { o := orm.NewOrm() sql := ` SELECT * FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.company_name=? AND b.product_id=? ` err = o.Raw(sql, companyName, productId).QueryRow(&item) return } func CheckCompanyProductById(companyId, productId int) (item *CompanyProductDetail, err error) { o := orm.NewOrm() sql := ` SELECT * 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=? ` err = o.Raw(sql, companyId, productId).QueryRow(&item) return } func CheckCompanyProductByCreditCode(creditCode string, productId int) (item *CompanyProductDetail, err error) { o := orm.NewOrm() sql := ` SELECT * FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.credit_code=? AND b.product_id=? ` err = o.Raw(sql, creditCode, productId).QueryRow(&item) return } // 新增客户请求参数 type CheckCompanyInfoResp struct { Status int `description:"检测状态:1:跳转到领取页面，0：不跳转，继续操作"` CompanyId int `description:"客户id"` ProductName string `description:"产品名称"` } type CheckCompanyInfoRepeatResp struct { Status int `description:"检测状态:1:跳转到领取页面，0：不跳转，继续操作"` RepeatStatus int `description:"检测状态:0:无重复 1:信用码重复 2:公司名重复"` CompanyId int `description:"客户id"` ProductName string `description:"产品名称"` } type ButtonPermission struct { BtnView bool `description:"查看按钮权限:true显示，false不显示"` BtnEdit bool `description:"编辑按钮权限:true显示，false不显示"` BtnDelete bool `description:"删除按钮权限:true显示，false不显示"` BtnSuspend bool `description:"暂停/启用按钮权限:true显示，false不显示"` BtnDelay bool `description:"申请延期按钮权限:true显示，false不显示"` BtnTurnPositive bool `description:"申请转正按钮权限:true显示，false不显示"` BtnUpdate bool `description:"服务更新按钮权限:true显示，false不显示"` BtnAddAgreement bool `description:"补充协议按钮权限:true显示，false不显示"` BtnThaw bool `description:"申请解冻按钮权限:true显示，false不显示"` BtnApplyReceive bool `description:"申请领取按钮权限:true显示，false不显示"` BtnReceive bool `description:"领取客户按钮权限:true显示，false不显示"` BtnModifySeller bool `description:"修改销售按钮权限：true显示，false不显示"` BtnReceiveOther bool `description:"领取其他部门客户按钮权限:true显示，false不显示"` BtnFreeze bool `description:"冻结按钮权限:true显示，false不显示"` BtnTryOut bool `description:"增开试用:true显示，false不显示"` BtnDetail bool `description:"详情按钮:true显示，false不显示"` BtnRemarkEdit bool `description:"编辑备注按钮:true显示，false不显示"` BtnRemarkView bool `description:"查看备注按钮:true显示，false不显示"` BtnRemarkViewHistory bool `description:"查看历史备注按钮:true显示，false不显示"` BtnShare bool `description:"设置共享按钮:true显示，false不显示"` BtnServiceRecord bool `description:"查看服务记录按钮:true显示，false不显示"` BtnClose bool `description:"关闭客户按钮:true显示，false不显示"` BtnLoss bool `description:"转流失按钮:true显示，false不显示"` IsResearchShow bool `description:"研选详情展示控制:true显示，false不显示"` } type ButtonSearchPermission struct { BtnView bool `description:"查看按钮权限:true显示，false不显示"` BtnUpdate bool `description:"服务更新按钮权限:true显示，false不显示"` BtnEdit bool `description:"编辑按钮权限:true显示，false不显示"` BtnDelete bool `description:"删除按钮权限:true显示，false不显示"` BtnSuspend bool `description:"暂停/启用按钮权限:true显示，false不显示"` BtnDelay bool `description:"申请延期按钮权限:true显示，false不显示"` BtnTurnPositive bool `description:"申请转正按钮权限:true显示，false不显示"` BtnThaw bool `description:"申请解冻按钮权限:true显示，false不显示"` BtnApplyReceive bool `description:"申请领取按钮权限:true显示，false不显示"` BtnReceive bool `description:"领取客户按钮权限:true显示，false不显示"` BtnModifySeller bool `description:"修改销售按钮权限：true显示，false不显示"` BtnReceiveOther bool `description:"领取其他部门客户按钮权限:true显示，false不显示"` } type ButtonDetailPermission struct { BtnEdit bool `description:"编辑按钮权限:true显示，false不显示"` BtnDelete bool `description:"删除按钮权限:true显示，false不显示"` BtnHistoryList bool `description:"历史签约按钮权限:true显示，false不显示"` BaseInfoEdit bool `description:"客户基础信息编辑操作:true可操作，false不可操作"` FiccEdit bool `description:"ficc类型，编辑按钮权限:true显示，false不显示"` FiccDelete bool `description:"ficc类型，删除按钮权限:true显示，false不显示"` RaiEdit bool `description:"权益类型，编辑按钮权限:true显示，false不显示"` RaiDelete bool `description:"权益类型，删除按钮权限:true显示，false不显示"` } func GetCompanyListExport(condition string, pars []interface{}) (items []*CompanyItem, err error) { o := orm.NewOrm() sql := `SELECT a.company_id,a.company_name,a.credit_code,a.company_code,a.created_time,a.address, GROUP_CONCAT(b.status ORDER BY b.product_id ASC SEPARATOR '/') AS status, GROUP_CONCAT(DISTINCT b.seller_name ORDER BY b.product_id ASC SEPARATOR '/') AS seller_name, GROUP_CONCAT(DISTINCT b.industry_name ORDER BY b.product_id ASC SEPARATOR '/') AS industry_name, GROUP_CONCAT(DISTINCT b.product_name ORDER BY b.product_id ASC SEPARATOR '/') AS company_type, GROUP_CONCAT(b.start_date ORDER BY b.product_id ASC SEPARATOR '/') AS start_date, GROUP_CONCAT(b.end_date ORDER BY b.product_id ASC SEPARATOR '/') AS end_date, GROUP_CONCAT(DISTINCT b.loss_time ORDER BY b.product_id ASC SEPARATOR '/') AS loss_time, GROUP_CONCAT(DISTINCT b.lose_reason ORDER BY b.product_id ASC SEPARATOR '/') AS lose_reason, GROUP_CONCAT(DISTINCT b.source ORDER BY b.product_id ASC SEPARATOR '/') AS source, GROUP_CONCAT(DISTINCT b.reasons ORDER BY b.product_id ASC SEPARATOR '/') AS reasons, GROUP_CONCAT(DISTINCT b.approve_status ORDER BY b.product_id ASC SEPARATOR '/') AS approve_status, GROUP_CONCAT(DISTINCT b.group_id ORDER BY b.product_id ASC SEPARATOR '/') AS group_id, b.is_suspend, b.freeze_start_date, b.freeze_end_date, b.seller_id, b.product_id FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 AND a.company_id<>1 ` if condition != "" { sql += condition } sql += ` GROUP BY a.company_id ORDER BY a.last_updated_time DESC ` _, err = o.Raw(sql, pars).QueryRows(&items) return } // 冻结客户请求参数 type CompanyFreezeReq struct { CompanyId int `description:"客户id"` CompanyType string `description:"客户类型：ficc/权益"` Remark string `description:"备注"` } // 冻结客户 func CompanyFreeze(companyId, productId int, freezeReason string) (err error) { o := orm.NewOrm() to, err := o.Begin() if err != nil { return } defer func() { if err != nil { _ = to.Rollback() } else { _ = to.Commit() } }() 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=?,freeze_reason=?,try_stage=1 WHERE company_id=? AND product_id=? ` _, err = to.Raw(sql, freezeStartDate, freezeEndDate, freezeStartDate, freezeEndDate, freezeReason, companyId, productId).Exec() if err != nil { return err } //修改产品权限状态为关闭 sql = `UPDATE company_report_permission SET status='关闭',modify_time=NOW() WHERE company_id= ? AND product_id = ? ` _, err = to.Raw(sql, companyId, productId).Exec() if err != nil { return err } //修改客户为关闭 sql = `UPDATE company SET type=3,last_updated_time=NOW(),start_date=?,end_date=? WHERE company_id=? ` _, err = to.Raw(sql, freezeStartDate, freezeEndDate, companyId).Exec() if err != nil { return err } return } func ModifyCompanyGroupId(sellerId, groupId int) (err error) { o := orm.NewOrm() sql := `UPDATE company_product SET group_id=? WHERE seller_id=? ` _, err = o.Raw(sql, groupId, sellerId).Exec() return } func ModifyCompanyDepartmentId(sellerId, departmentId int) (err error) { o := orm.NewOrm() sql := `UPDATE company_product SET department_id=? WHERE seller_id=? ` _, err = o.Raw(sql, departmentId, sellerId).Exec() return } // CompanyTryOutReasonReq 编辑客户未续约情况说明请求参数 type CompanyTryOutReasonReq struct { CompanyId int `description:"客户id"` ProductId int `description:"客户产品id：ficc：1；权益：2"` Remark string `description:"备注"` } // RenewalReasonReq 新增客户未续约情况说明请求参数 type RenewalReasonReq struct { CompanyId int `description:"客户id"` ProductId int `description:"客户产品id：ficc：1；权益：2"` Remark string `description:"未续约理由"` Renew int `description:"是否勾选不再跟进 1:勾选,0:未勾选"` RenewalTodo string `description:"待办事项说明"` } // TryOutReq 新增客户未续约情况说明请求参数 type TryOutReq struct { CompanyId int `description:"客户id"` ProductId int `description:"客户产品id：ficc：1；权益：2"` OperationId int `description:"operation record id"` Remark string `description:"未续约理由"` Renew int `description:"是否勾选不再跟进 1:勾选,0:未勾选"` RenewalTodo string `description:"待办事项说明"` } func GetFiccReportListByMobile(mobiles string) (items []*ViewReportList, err error) { dataName := "" sql := `` if utils.RunMode == "debug" { dataName = "test_v2_hongze_rddp" } else { dataName = "hongze_rddp" } ficcSql := `SELECT rr.research_report_name, rr.type AS report_type, 'ficc' AS txt_type, '--' AS match_type_name, '--' AS stop_time, uvh.created_time AS created_time FROM user_view_history uvh LEFT JOIN research_report rr ON rr.research_report_id = uvh.research_report_id WHERE uvh.mobile in (` + mobiles + `) UNION ALL SELECT r.title AS research_report_name, 'rddp' AS report_type, 'ficc' AS txt_type, r.classify_name_first AS match_type_name, '--' AS stop_time, rvr.create_time AS created_time FROM %s.report_view_record rvr LEFT JOIN %s.report r ON r.id = rvr.report_id WHERE rvr.mobile in (` + mobiles + `) UNION ALL SELECT cha.permission_name AS research_report_name, 'advisory' AS report_type, 'ficc' AS txt_type, cha.classify_name AS match_type_name, '--' AS stop_time, auc.create_time AS created_time FROM advisory_user_chart_article_record auc LEFT JOIN chart_permission cha ON cha.chart_permission_id = auc.chart_permission_id WHERE auc.mobile in (` + mobiles + `)` sql = ` SELECT * FROM ( ` + ficcSql + ` )AS t ORDER BY t.created_time DESC` o := orm.NewOrm() sql = fmt.Sprintf(sql, dataName, dataName) _, err = o.Raw(sql).QueryRows(&items) return } func GetRightsReportListByMobile(mobiles string) (items []*ViewReportList, err error) { rightsSql := `SELECT art.title AS research_report_name, 'cygx' AS report_type, 'rights' AS txt_type, art.match_type_name, h.stop_time, h.create_time AS created_time FROM cygx_article_history_record_newpv h LEFT JOIN cygx_article art ON art.article_id = h.article_id WHERE h.mobile in (` + mobiles + `) AND h.company_id != 16 ` sql := ` SELECT * FROM ( ` + rightsSql + ` )AS t ORDER BY t.created_time DESC` o := orm.NewOrm() _, err = o.Raw(sql).QueryRows(&items) return } func GetFiccReportListByEmail(emails string) (items []*ViewReportList, err error) { dataName := "" sql := `` if utils.RunMode == "debug" { dataName = "test_v2_hongze_rddp" } else { dataName = "hongze_rddp" } ficcSql := `SELECT rr.research_report_name, rr.type AS report_type, 'ficc' AS txt_type, '--' AS match_type_name, '--' AS stop_time, uvh.created_time AS created_time FROM user_view_history uvh INNER JOIN research_report rr ON rr.research_report_id = uvh.research_report_id WHERE uvh.email in (` + emails + `) UNION ALL SELECT r.title AS research_report_name, 'rddp' AS report_type, 'ficc' AS txt_type, r.classify_name_first AS match_type_name, '--' AS stop_time, rvr.create_time AS created_time FROM %s.report_view_record rvr INNER JOIN %s.report r ON r.id = rvr.report_id WHERE rvr.email in (` + emails + `) UNION ALL SELECT cha.permission_name AS research_report_name, 'advisory' AS report_type, 'ficc' AS txt_type, cha.classify_name AS match_type_name, '--' AS stop_time, auc.create_time AS created_time FROM advisory_user_chart_article_record auc INNER JOIN chart_permission cha ON cha.chart_permission_id = auc.chart_permission_id WHERE auc.email in (` + emails + `) ` sql = ` SELECT * FROM ( ` + ficcSql + ` )AS t ORDER BY t.created_time DESC` o := orm.NewOrm() sql = fmt.Sprintf(sql, dataName, dataName) _, err = o.Raw(sql).QueryRows(&items) return } func GetRightsReportListByEmail(emails string) (items []*ViewReportList, err error) { rightsSql := `SELECT art.title AS research_report_name, 'cygx' AS report_type, 'rights' AS txt_type, art.match_type_name, h.stop_time, h.create_time AS created_time FROM cygx_article_history_record_newpv h INNER JOIN cygx_article art ON art.article_id = h.article_id WHERE h.email in (` + emails + `) AND h.company_id != 16 ` sql := ` SELECT * FROM ( ` + rightsSql + ` )AS t ORDER BY t.created_time DESC` o := orm.NewOrm() _, err = o.Raw(sql).QueryRows(&items) return } // ModifyCompanyProductLastViewData 修改客户产品的总共阅读次数以及最近阅读时间 func ModifyCompanyProductLastViewData(companyId, productId, viewTotal int, lastViewTime string) (err error) { o := orm.NewOrm() sql := `UPDATE company_product SET view_total=?,last_view_time=? WHERE company_id=? AND product_id=? ` _, err = o.Raw(sql, viewTotal, lastViewTime, companyId, productId).Exec() if err != nil { return err } return } // RemarkReq 新增备注请求 type RemarkReq struct { CompanyId int `description:"客户id"` Remark string `description:"备注"` } // RemarkDelReq 删除备注请求 type RemarkDelReq struct { RemarkId int `description:"备注ID"` } // RemarkResp 备注说明 type RemarkResp struct { RemarkId int `description:"备注ID"` Remark string `description:"未续约说明"` ProductName string `description:"客户产品：ficc：权益："` CreateTime string `description:"添加时间"` ButtonDel bool `description:"删除权限"` } type RoadShowTotalSlice struct { Total int `description:"总路演数"` CompanyId int `description:"用户id"` } // 根据用户id字符串获取企业用户的路演总次数 func GetRoadShowTotal(companyIds, date, endTime string) (items []*RoadShowTotalSlice, err error) { //如果companyIds是空串，那么直接返回 if companyIds == "" { return } o := orm.NewOrm() sql := `SELECT COUNT(1) total, t.company_id FROM ( SELECT a.rs_calendar_id, a.company_id FROM rs_calendar AS a INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id = b.rs_calendar_id WHERE a.company_id in (` + companyIds + `) AND a.activity_type = '路演' AND b.status = 2 AND b.end_date <= ? AND b.end_time < ? ) AS t GROUP BY company_id` _, err = o.Raw(sql, date, endTime).QueryRows(&items) return } type RoadShowCalendar struct { RsCalendarId int `orm:"column(rs_calendar_id);pk"` SysUserId int `description:"创建人id"` SysUserRealName string `description:"创建人名称"` ActivityType string `description:"活动类型"` RoadshowType string `description:"路演形式"` RoadshowPlatform string `description:"路演平台"` CompanyId int `description:"客户id"` CompanyName string `description:"客户名称"` Province string `description:"省"` ProvinceCode string `description:"省编码"` City string `description:"市"` CityCode string `description:"市编码"` Theme string `description:"会议主题"` CooperationName string `description:"合作方名称"` Title string `description:"展示在日历的标题"` Source int8 `description:"来源，0：自系统，1：上海方的"` CreateTime time.Time ModifyTime time.Time ActivityCategory string `description:"活动类别"` IsSynced int `description:"是否与上海同步 0:未同步 1:已同步"` EndDate time.Time } func GetRoadShowList(companyId, englishCompany int) (items []*RoadShowCalendar, err error) { o := orm.NewOrm() sql := `SELECT a.*,b.end_date FROM rs_calendar AS a INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id = b.rs_calendar_id WHERE a.source = 0 AND a.company_id=? AND b.status = 2 AND a.english_company=? AND NOW()> DATE_FORMAT( CONCAT( end_date, " ", end_time ), '%Y-%m-%d %H:%i:%S' ) GROUP BY rs_calendar_id ORDER BY create_time DESC ` _, err = o.Raw(sql, companyId, englishCompany).QueryRows(&items) return } type RoadShowListResp struct { SysUserRealName string `description:"对接销售"` RoadshowType string `description:"路演形式"` RoadshowPlatform string `description:"路演平台：线上时为平台，线下时为省市"` RoadShowTime string `description:"路演时间"` Researchers string `description:"研究员"` } func GetRoadShowResearchers(calendarId int) (items []*string, err error) { o := orm.NewOrm() sql := `SELECT researcher_name FROM rs_calendar_researcher WHERE rs_calendar_id=? AND status=2 AND NOW()> DATE_FORMAT( CONCAT( end_date, " ", end_time ), '%Y-%m-%d %H:%i:%S' ) ` _, err = o.Raw(sql, calendarId).QueryRows(&items) return } type CompanyNameAndId struct { CompanyId int `description:"公司id"` CompanyName string `description:"公司名称"` } type CompanyNameAndIdListResp struct { List []*CompanyNameAndId } // GetCompanyIdListByproductName 获取不同种类不同状态下的公司ID func GetCompanyIdListByproductName(condition string, pars []interface{}) (items []*CompanyNameAndId, err error) { o := orm.NewOrm() sql := `SELECT a.company_id,a.company_name FROM company AS a INNER JOIN company_product AS b ON a.company_id = b.company_id WHERE a.enabled = 1 ` if condition != `` { sql += condition } _, err = o.Raw(sql, pars).QueryRows(&items) return } // CompanyTryOutDayListResp 客户累计试用天数 type CompanyTryOutDayListResp struct { Paging *paging.PagingItem List []*CompanyProductTryOutUpdateLog } // 共享客户 type CompanyShareReq struct { CompanyId int `description:"客户id"` IsShare int `description:"0取消共享 1共享"` } func GetShareCompanyListCount(condition string, pars []interface{}) (count int, err error) { o := orm.NewOrm() sql := `SELECT COUNT(DISTINCT b.company_id) AS count FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&count) return } func GetShareCompanyList(condition, sortStr string, pars []interface{}, startSize, pageSize int) (items []*CompanyItem, err error) { o := orm.NewOrm() // 七天前的时间-用于查询周阅读活跃, 此处的需求是 // FICC客户、共享客户: 以FICC最后一次阅读时间作为参考, 七日内有阅读量则为活跃 // 权益客户: 以权益最后一次阅读时间作为参考 t := time.Now().Local().AddDate(0, 0, -7).Format(utils.FormatDate) sql := `SELECT a.company_id,a.company_name,a.credit_code,a.company_code,a.created_time,a.province,a.city, a.address,a.region_type,b.group_id,b.road_show_total,b.is_share,b.share_seller_id,b.share_seller,b.is_user_maker, CASE WHEN GROUP_CONCAT(b.status) LIKE "%永续%" THEN 1 WHEN GROUP_CONCAT(b.status) LIKE "%正式%" THEN 2 WHEN GROUP_CONCAT(b.status) LIKE "%试用%" THEN 3 WHEN GROUP_CONCAT(b.status) LIKE "%冻结%" THEN 4 WHEN GROUP_CONCAT(b.status) LIKE "%流失%" THEN 5 ELSE 10 END as sort_status, CASE WHEN GROUP_CONCAT(b.try_stage) LIKE "%2%" THEN 1 WHEN GROUP_CONCAT(b.try_stage) LIKE "%3%" THEN 2 WHEN GROUP_CONCAT(b.try_stage) LIKE "%4%" THEN 3 WHEN GROUP_CONCAT(b.try_stage) LIKE "%1%" THEN 4 ELSE 10 END as sort_try_stage, CASE WHEN GROUP_CONCAT(b.todo_status) LIKE "%未完成%" AND GROUP_CONCAT(b.todo_end_time) is not null THEN GROUP_CONCAT(b.todo_end_time) ELSE "9999-01-01" END as sort_todo_end_time, GROUP_CONCAT(CASE WHEN b.is_share = 1 THEN CONCAT(b.status, '(共享)') ELSE b.status END ORDER BY b.product_id ASC SEPARATOR '/') AS status, GROUP_CONCAT(DISTINCT b.seller_name ORDER BY b.product_id ASC SEPARATOR '/') AS seller_name, GROUP_CONCAT(DISTINCT b.seller_id ORDER BY b.product_id ASC SEPARATOR '/') AS seller_ids, GROUP_CONCAT(DISTINCT b.package_type ORDER BY b.product_id ASC SEPARATOR '/') AS package_types, GROUP_CONCAT(DISTINCT b.industry_name ORDER BY b.product_id ASC SEPARATOR '/') AS industry_name, GROUP_CONCAT(DISTINCT b.product_name ORDER BY b.product_id ASC SEPARATOR '/') AS company_type, GROUP_CONCAT(DISTINCT b.product_id ORDER BY b.product_id ASC SEPARATOR '/') AS product_ids, GROUP_CONCAT(DISTINCT b.view_total ORDER BY b.product_id ASC SEPARATOR '/') AS view_totals, GROUP_CONCAT(DISTINCT b.last_view_time ORDER BY b.product_id ASC SEPARATOR '/') AS last_view_times, GROUP_CONCAT(b.start_date ORDER BY b.product_id ASC SEPARATOR '/') AS start_date, GROUP_CONCAT(b.end_date ORDER BY b.product_id ASC SEPARATOR '/') AS end_date, GROUP_CONCAT(DISTINCT b.loss_time ORDER BY b.product_id ASC SEPARATOR '/') AS loss_time, GROUP_CONCAT(DISTINCT b.lose_reason ORDER BY b.product_id ASC SEPARATOR '/') AS lose_reason, GROUP_CONCAT(DISTINCT b.source ORDER BY b.product_id ASC SEPARATOR '/') AS source, GROUP_CONCAT(DISTINCT b.reasons ORDER BY b.product_id ASC SEPARATOR '/') AS reasons, GROUP_CONCAT(DISTINCT b.approve_status ORDER BY b.product_id ASC SEPARATOR '/') AS approve_status, GROUP_CONCAT(b.try_stage ORDER BY b.product_id ASC SEPARATOR '/') AS try_stage_str, GROUP_CONCAT(DISTINCT b.group_id ORDER BY b.product_id ASC SEPARATOR '/') AS group_ids, GROUP_CONCAT(DISTINCT b.todo_status ORDER BY b.product_id ASC SEPARATOR '/') AS todo_statuss, GROUP_CONCAT(DISTINCT b.todo_end_time ORDER BY b.product_id ASC SEPARATOR '/') AS todo_end_time_str, GROUP_CONCAT(DISTINCT b.try_out_day_total ORDER BY b.product_id ASC SEPARATOR '/') AS try_out_day_totals, b.is_suspend, b.freeze_start_date, b.freeze_end_date, b.seller_id, b.product_id, b.formal_time, b.freeze_time,b.freeze_reason,b.renewal_reason, b.view_total, b.last_view_time, GROUP_CONCAT(DISTINCT b.share_seller ORDER BY b.product_id ASC SEPARATOR '/') AS share_seller, GROUP_CONCAT(DISTINCT b.share_seller_id ORDER BY b.product_id ASC SEPARATOR ',') AS share_seller_ids, b.share_seller_id , MAX(b.last_view_time) as max_last_view_time, MIN(b.last_view_time) as min_last_view_time, MAX(b.end_date) as max_end_date, MIN(b.end_date) as min_end_date, SUM(b.view_total) as all_view_total, SUM(b.try_out_day_total) as all_try_out_day_total, IF(LEFT(GROUP_CONCAT(DISTINCT b.last_view_time ORDER BY b.product_id), 19) >= "` + t + `", 1, 0) AS week_view_active, (SELECT MAX(create_time) FROM company_service_record WHERE company_id = a.company_id) AS last_service_time, (SELECT COUNT(company_service_record_id) FROM company_service_record WHERE company_id = a.company_id) AS service_times FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 ` if condition != "" { sql += condition } //sortStr := `` //如果没有传入排序字段，那么按照状态排序 if sortStr == "" { sortStr = " ORDER BY a.created_time DESC " } sql += ` GROUP BY a.company_id ` + sortStr + ` LIMIT ?,? ` _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items) return } // 分配共享销售 type MoveShareSellerReq struct { CompanyId int `description:"客户id"` SellsId int `description:"销售员id"` } // ExportUserCompanyStatus 联系人导出客户状态 type ExportUserCompanyStatus struct { CompanyId int `description:"客户ID"` CompanyName string `description:"客户名称"` CompanyStatus string `description:"客户状态: FICC状态/权益状态"` } // GetExportUserCompanyStatus 获取联系人导出客户状态 func GetExportUserCompanyStatus(condition string, pars []interface{}) (items []*ExportUserCompanyStatus, err error) { o := orm.NewOrm() sql := `SELECT a.company_id, a.company_name, GROUP_CONCAT(b.status SEPARATOR '/') AS company_status FROM company AS a INNER JOIN company_product AS b ON a.company_id = b.company_id WHERE 1 = 1 ` if condition != "" { sql += condition } sql += ` GROUP BY a.company_id ` _, err = o.Raw(sql, pars).QueryRows(&items) return } func GetExportUserByProductId(companyId string, productId int) (items []*ExportUser, err error) { o := orm.NewOrm() sql := `SELECT a.real_name,a.mobile,a.email,a.position,a.is_maker,department_name,a.company_id,b.subscribe AS is_subscribe FROM wx_user AS a LEFT JOIN (SELECT * FROM user_record WHERE create_platform = 1 GROUP BY user_id) AS b ON a.user_id = b.user_id LEFT JOIN user_seller_relation AS c ON a.user_id = c.user_id WHERE a.company_id<>1 AND a.company_id IN(` + companyId + `) AND c.product_id = ? GROUP BY a.user_id ORDER BY a.company_id ASC ` _, err = o.Raw(sql, productId).QueryRows(&items) return } // CloseCompanyReq 关闭客户请求体 type CloseCompanyReq struct { CompanyId int `description:"客户id"` Remark string `description:"备注"` } // CloseCompany 关闭客户 func CloseCompany(companyId, productId int, reason string) (err error) { o := orm.NewOrm() tx, e := o.Begin() if e != nil { err = fmt.Errorf("orm begin err: %s", e.Error()) return } defer func() { if err != nil { _ = tx.Rollback() return } _ = tx.Commit() }() // 修改产品状态 sql := `UPDATE company_product SET status = '关闭', is_formal = 0, close_reason = ?, close_time = NOW(), modify_time=NOW(), 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 = ?` _, e = tx.Raw(sql, reason, companyId, productId).Exec() if e != nil { err = fmt.Errorf("update company product err: %s", e.Error()) return } // 修改产品权限 sql = `UPDATE company_report_permission SET status='关闭', modify_time=NOW() WHERE company_id = ? AND product_id = ?` _, e = tx.Raw(sql, companyId, productId).Exec() if e != nil { err = fmt.Errorf("update company report permission err: %s", e.Error()) return } // 将历史的任务给标记删除掉 sql = `UPDATE company_todo SET is_delete = 1, modify_time=NOW() WHERE company_id = ? AND product_id = ? AND is_delete = 0` _, e = tx.Raw(sql, companyId, productId).Exec() if e != nil { err = fmt.Errorf("update company todo err: %s", e.Error()) return } return } // Close2LossReq 关闭转流失请求体 type Close2LossReq struct { CompanyId int `description:"客户id"` } // Close2Loss 关闭转流失 func Close2Loss(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, close_reason = '', close_time = null WHERE company_id = ? AND product_id = ?` _, err = o.Raw(sql, companyId, productId).Exec() return } // CompanyMarkReq 标记请求 type CompanyMarkReq struct { CompanyServiceRecordId int `description:"服务记录ID"` Status int `description:"0取消标记 1标记"` } // GetCompanyListByCondition 获取客户列表 func GetCompanyListByCondition(condition string, pars []interface{}, fieldArr []string, orderRule string) (items []*Company, err error) { o := orm.NewOrm() fields := strings.Join(fieldArr, ",") if len(fieldArr) == 0 { fields = `*` } order := `ORDER BY created_time DESC` if orderRule != "" { order = ` ORDER BY ` + orderRule } sql := fmt.Sprintf(`SELECT %s FROM company WHERE 1=1 %s %s`, fields, condition, order) _, err = o.Raw(sql, pars).QueryRows(&items) return } func GetOverseasCompanys() (items []*Company, err error) { o := orm.NewOrm() sql := ` SELECT * FROM company where region_type= '海外' ` _, err = o.Raw(sql).QueryRows(&items) return }