123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396 |
- package overseas_custom
- import (
- "fmt"
- "github.com/beego/beego/v2/client/orm"
- "github.com/rdlucklib/rdluck_tools/paging"
- "hongze/hz_crm_api/utils"
- )
- type Custom struct {
- CompanyId int `description:"客户id"`
- CompanyName string `description:"客户名称"`
- Nation string `description:"国家"`
- SellerId int `description:"销售id"`
- SellerName string `description:"销售名称"`
- CompanyStatus string `description:"状态"`
- ViewTotal int `description:"累计点击量"`
- RoadShowTotal int `description:"路演数量"`
- LastViewTime string `description:"最近阅读时间"`
- CreateTime string `description:"创建时间"`
- IsHide int `description:"是否隐藏:0:不隐藏,1:隐藏"`
- OverseasStatus string `description:"海外客户状态:'正式','试用','关闭'"`
- Source int `description:"来源:1,英文客户,2:客户列表"`
- OverseasLabel int `description:"海外客户试用子标签:1未分类、2 推进、3 跟踪、4 预备、"`
- ResetBtn int `description:"转正式和重置按钮:同步过来默认为0:显示转正式为1:显示重置为2"`
- Status string `description:"客户状态"`
- }
- type CustomTotal struct {
- CompanyStatus string `description:"状态"`
- Total int `description:"总数"`
- }
- func (obj *Custom) GetCustomTotal(condition string, pars []interface{}) (list []*CustomTotal, err error) {
- o := orm.NewOrm()
- sql := ``
- var databaseName string
- if utils.RunMode == "debug" {
- databaseName = "test_v2_hongze_rddp"
- } else {
- databaseName = "hongze_rddp"
- }
- sql = `SELECT overseas_status AS company_status,COUNT(1) AS total,is_hide FROM (
- SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
- b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status,b.overseas_label
- FROM company AS a
- INNER JOIN company_product AS b ON a.company_id=b.company_id
- INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
- WHERE b.is_overseas = 0
- UNION ALL
- SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
- t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,t.overseas_label
- FROM %s.english_company AS t
- INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
- WHERE 1=1 AND t.is_deleted=0
- )AS m
- WHERE 1=1
- `
- if condition != "" {
- sql += condition
- }
- sql += ` GROUP BY m.overseas_status `
- sql = fmt.Sprintf(sql, databaseName)
- _, err = o.Raw(sql, pars).QueryRows(&list)
- return
- }
- //
- //func (obj *Custom) GetCustomOverseasStatusTotal(condition string, pars []interface{}) (total int, err error) {
- // o := orm.NewOrm()
- // sql := ``
- // var databaseName string
- // if utils.RunMode == "debug" {
- // databaseName = "test_v2_hongze_rddp"
- // } else {
- // databaseName = "hongze_rddp"
- // }
- //
- // sql = `SELECT COUNT(1) AS total FROM (
- // SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
- // b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status,b.overseas_label
- // FROM company AS a
- // INNER JOIN company_product AS b ON a.company_id=b.company_id
- // INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
- // WHERE b.is_overseas = 0
- // UNION ALL
- // SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
- // t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,t.overseas_label
- // FROM %s.english_company AS t
- // INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
- // WHERE 1=1 AND t.is_deleted=0
- // )AS m
- // WHERE 1=1
- // AND m.is_hide=0
- //AND m. overseas_status='正式' `
- //
- // if condition != "" {
- // sql += condition
- // }
- //
- // sql += ` GROUP BY m.overseas_status `
- //
- // sql = fmt.Sprintf(sql, databaseName)
- //
- // err = o.Raw(sql, pars).QueryRow(&total)
- // return
- //}
- // GetCompanyIdsByKeyword 关键词获取客户IDs
- func (obj *Custom) GetCompanyIdsByKeyword(keyword string) (companyIds []int, err error) {
- o := orm.NewOrm()
- sql := ` SELECT DISTINCT b.company_id FROM wx_user AS a
- INNER JOIN company AS b ON a.company_id=b.company_id
- WHERE 1=1 AND (a.email LIKE ? OR a.mobile LIKE ? OR b.company_name LIKE ? OR b.credit_code LIKE ? ) `
- _, err = o.Raw(sql, keyword, keyword, keyword, keyword).QueryRows(&companyIds)
- return
- }
- func (obj *Custom) GetCustomListCount(condition string, pars []interface{}) (count int, err error) {
- o := orm.NewOrm()
- sql := ``
- var databaseName string
- if utils.RunMode == "debug" {
- databaseName = "test_v2_hongze_rddp"
- } else {
- databaseName = "hongze_rddp"
- }
- sql = `SELECT COUNT(1) AS count FROM (
- SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
- b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status,b.overseas_label
- FROM company AS a
- INNER JOIN company_product AS b ON a.company_id=b.company_id
- INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
- WHERE b.is_overseas = 0`
- sql += `
- UNION ALL
- SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
- t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,t.overseas_label
- FROM %s.english_company AS t
- INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
- WHERE 1=1 AND t.is_deleted=0 `
- sql += ` )AS m
- WHERE 1=1 `
- if condition != "" {
- sql += condition
- }
- sql = fmt.Sprintf(sql, databaseName)
- err = o.Raw(sql, pars).QueryRow(&count)
- return
- }
- func (obj *Custom) GetCustomList(condition string, pars []interface{}, sortField string, startSize, pageSize, sortDesc int) (list []*Custom, err error) {
- o := orm.NewOrm()
- sql := ``
- var databaseName string
- if utils.RunMode == "debug" {
- databaseName = "test_v2_hongze_rddp"
- } else {
- databaseName = "hongze_rddp"
- }
- sql = `SELECT * FROM (
- SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
- b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status,
- 2 AS source,b.overseas_label,a.reset_btn,b.status
- FROM company AS a
- INNER JOIN company_product AS b ON a.company_id=b.company_id
- INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
- WHERE b.product_id=1 AND b.is_overseas = 0`
- sql += `
- UNION ALL
- SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
- t.view_total,t.road_show_total,t.create_time,t.last_view_time,t.is_hide,t.overseas_status,
- 1 AS source,t.overseas_label,t.reset_btn,CASE t.enabled WHEN 0 THEN '关闭' WHEN 1 THEN '试用' WHEN 2 THEN '试用' END status
- FROM %s.english_company AS t
- INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
- WHERE 1=1 AND t.is_deleted=0 `
- sql += ` )AS m WHERE 1=1 `
- if condition != "" {
- sql += condition
- }
- var sortFieldStr, sortDescStr string
- if sortField != "" {
- if sortField == "ViewTotal" {
- sortFieldStr = "view_total"
- } else if sortField == "RoadShowTotal" {
- sortFieldStr = "road_show_total"
- } else if sortField == "CreateTime" {
- sortFieldStr = "create_time"
- } else if sortField == "LastViewTime" {
- sortFieldStr = "last_view_time"
- }
- }
- if sortDesc == 1 {
- sortDescStr = "DESC"
- } else {
- sortDescStr = "ASC"
- }
- sql += ` ORDER BY %s %s,m.create_time DESC`
- sql += ` LIMIT ?,? `
- sql = fmt.Sprintf(sql, databaseName, sortFieldStr, sortDescStr)
- _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
- return
- }
- type CustomListResp struct {
- Paging *paging.PagingItem
- List []*Custom
- }
- type CustomHideReq struct {
- CompanyId int `description:"客户ID"`
- }
- func (obj *Custom) GetCustomByCompanyId(companyId int) (item *Custom, err error) {
- o := orm.NewOrm()
- sql := ``
- var databaseName string
- if utils.RunMode == "debug" {
- databaseName = "test_v2_hongze_rddp"
- } else {
- databaseName = "hongze_rddp"
- }
- //if companyId > utils.EnCompanyIdStep {
- // companyId = companyId - utils.EnCompanyIdStep
- //}
- sql = `SELECT * FROM (
- SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
- b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status,a.reset_btn,
- CASE b.status
- WHEN '正式' THEN '正式'
- WHEN '永续' THEN '正式'
- WHEN '试用' THEN '试用'
- ELSE '关闭' END AS company_status
- FROM company AS a
- INNER JOIN company_product AS b ON a.company_id=b.company_id
- INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
- WHERE b.is_overseas = 0`
- sql += ` UNION ALL
- SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
- t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,t.reset_btn,
- CASE t.enabled
- WHEN 0 THEN '关闭'
- ELSE '试用' END AS company_status
- FROM %s.english_company AS t
- INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
- WHERE 1=1 `
- sql += ` )AS m WHERE 1=1 `
- sql += ` AND m.company_id=? `
- sql = fmt.Sprintf(sql, databaseName)
- err = o.Raw(sql, companyId).QueryRow(&item)
- return
- }
- type OverseasLabelTotal struct {
- OverseasLabel int `description:"海外客户试用子标签:1未分类、2 推进、3 跟踪、4 预备、"`
- Total int `description:"总数"`
- }
- func (obj *Custom) GetCustomOverseasLabelTotal(condition string, pars []interface{}) (list []*OverseasLabelTotal, err error) {
- o := orm.NewOrm()
- sql := ``
- var databaseName string
- if utils.RunMode == "debug" {
- databaseName = "test_v2_hongze_rddp"
- } else {
- databaseName = "hongze_rddp"
- }
- sql = `SELECT overseas_label,COUNT(1) AS total FROM (
- SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
- b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status,b.overseas_label
- FROM company AS a
- INNER JOIN company_product AS b ON a.company_id=b.company_id
- INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
- WHERE b.is_overseas = 0
- UNION ALL
- SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
- t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,t.overseas_label
- FROM %s.english_company AS t
- INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
- WHERE 1=1 AND t.is_deleted=0
- )AS m
- WHERE 1=1
- `
- if condition != "" {
- sql += condition
- }
- sql += ` GROUP BY m.overseas_label `
- sql = fmt.Sprintf(sql, databaseName)
- _, err = o.Raw(sql, pars).QueryRows(&list)
- return
- }
- type CustomOverseasLabelSetReq struct {
- CompanyId int `description:"客户ID"`
- OverseasLabel int `description:"海外客户试用子标签:1未分类、2 推进、3 跟踪、4 预备、"`
- }
- // 更新客户产品信息
- func (obj *Custom) UpdateCompanyProduct(updateParams, whereParam map[string]interface{}) (err error) {
- to := orm.NewOrm()
- ptrStructOrTableName := "company_product"
- qs := to.QueryTable(ptrStructOrTableName)
- for expr, exprV := range whereParam {
- qs = qs.Filter(expr, exprV)
- }
- _, err = qs.Update(updateParams)
- return
- }
- // 更新客户产品信息
- func (obj *Custom) UpdateCompany(updateParams, whereParam map[string]interface{}) (err error) {
- to := orm.NewOrm()
- ptrStructOrTableName := "company"
- qs := to.QueryTable(ptrStructOrTableName)
- for expr, exprV := range whereParam {
- qs = qs.Filter(expr, exprV)
- }
- _, err = qs.Update(updateParams)
- return
- }
- // GetEnCompanyIdsByKeyword 关键词获取英文客户IDs
- func GetEnCompanyIdsByKeyword(keyword string) (companyIds []int, err error) {
- o := orm.NewOrmUsingDB("rddp")
- sql := ` SELECT (a.company_id+10000000) AS company_id
- FROM english_company AS a
- LEFT JOIN english_report_email AS b ON a.company_id=b.company_id
- WHERE a.is_deleted=0
- AND ( a.company_name LIKE ? OR a.country_code LIKE ? OR b.email LIKE ? OR b.mobile LIKE ?)
- GROUP BY a.company_id `
- _, err = o.Raw(sql, keyword, keyword, keyword, keyword).QueryRows(&companyIds)
- return
- }
- func GetOverseasCustomList(condition string, pars []interface{}) (list []*Custom, err error) {
- o := orm.NewOrm()
- sql := ``
- var databaseName string
- if utils.RunMode == "debug" {
- databaseName = "test_v2_hongze_rddp"
- } else {
- databaseName = "hongze_rddp"
- }
- sql = `SELECT * FROM (
- SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
- b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status,
- 2 AS source,b.overseas_label,a.reset_btn
- FROM company AS a
- INNER JOIN company_product AS b ON a.company_id=b.company_id
- INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
- WHERE b.product_id=1 AND b.is_overseas = 0`
- sql += `
- UNION ALL
- SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
- t.view_total,t.road_show_total,t.create_time,t.last_view_time,t.is_hide,t.overseas_status,
- 1 AS source,t.overseas_label,t.reset_btn
- FROM %s.english_company AS t
- INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
- WHERE 1=1 AND t.is_deleted=0 `
- sql += ` )AS m WHERE 1=1 `
- if condition != "" {
- sql += condition
- }
- sql = fmt.Sprintf(sql, databaseName)
- _, err = o.Raw(sql, pars).QueryRows(&list)
- return
- }
|