1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354 |
- 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状态/权益状态"`
- }
|