package company import ( "fmt" "github.com/beego/beego/v2/client/orm" "github.com/rdlucklib/rdluck_tools/paging" "hongze/hz_eta_api/utils" "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"` ShareSeller string `description:"共享销售员"` ShareSellerId 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:大客户"` IsShare 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:"开放给第三方的客户编码，不让第三方定位我们的客户信息"` } // 新增客户 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"` IsShare int `description:"0:非共享用户,1:共享客户"` } 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,a.share_seller_id,a.share_seller,a.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.company_type 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 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:"客户状态：'试用','永续','冻结','流失','正式' "` 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"` 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:"服务次数"` } 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"` LastServiceTime string `description:"最后服务时间"` ServiceTimes int `description:"服务次数"` } 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 } 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,a.is_share, 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(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.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, 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 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 == "" { 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 { 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亿以上。"` } // 新增客户请求参数 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:"开放给第三方的客户编码，不让第三方定位我们的客户信息"` IsShare int `description:"0:非共享用户,1:共享客户"` ShareSeller string `description:"共享销售员"` ShareSellerId int `description:"共享销售员id"` } // 客户权限列表 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 } 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:"市"` 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 = ? WHERE company_id =?` _, err = o.Raw(sql, item.CompanyName, item.CreditCode, item.Address, item.Province, item.City, 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 预备"` } type ExportUser struct { RealName string Mobile string Email string Position string IsMaker int DepartmentName string CompanyId int IsSubscribe int `description:"是否关注了公众号(弘则研究)"` } 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 } // 移动销售 type SuspendReq struct { CompanyId int `description:"客户id"` } // 新增客户请求参数 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不显示"` BtnShare bool `description:"设置共享按钮:true显示，false不显示"` BtnServiceRecord 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, 2, 0).Format(utils.FormatDate) } // 权益也调整为 两个月的冻结期 if productId == 2 { freezeEndDate = time.Now().AddDate(0, 2, 0).Format(utils.FormatDate) } //修改用户产品状态 sql := `UPDATE company_product SET status='冻结',is_formal=0,is_suspend=0,freeze_time=NOW(),modify_time=NOW(),start_date=?,end_date=?,freeze_start_date=?,freeze_end_date=?,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:"备注"` } // RemarkResp 备注说明 type RemarkResp struct { Remark string `description:"未续约说明"` ProductName string `description:"客户产品：ficc：权益："` CreateTime string `description:"添加时间"` } type RoadShowTotalSlice struct { Total int `description:"总路演数"` CompanyId int `description:"用户id"` } 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 } type RoadShowListResp struct { SysUserRealName string `description:"对接销售"` RoadshowType string `description:"路演形式"` RoadshowPlatform string `description:"路演平台：线上时为平台，线下时为省市"` RoadShowTime string `description:"路演时间"` Researchers string `description:"研究员"` } type CompanyNameAndId struct { CompanyId int `description:"公司id"` CompanyName string `description:"公司名称"` } type CompanyNameAndIdListResp struct { List []*CompanyNameAndId } // CompanyTryOutDayListResp 客户累计试用天数 type CompanyTryOutDayListResp struct { Paging *paging.PagingItem List []*CompanyProductTryOutUpdateLog } // 共享客户 type CompanyShareReq struct { CompanyId int `description:"客户id"` IsShare int `description:"0取消共享 1共享"` } // 分配共享销售 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状态/权益状态"` }