hz_crm_api/models/overseas_custom/custom.go

package overseas_custom

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"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:"创建时间"`
}

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 company_status,COUNT(1) AS total FROM (
				SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
				CASE b.status 
				WHEN '正式' THEN '正式'
				WHEN '永续' THEN '正式'
				WHEN '试用' THEN '试用'
				ELSE '关闭' END AS company_status,
				b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time
				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,
				CASE t.enabled
				WHEN 0 THEN '关闭'
				ELSE '试用' END AS company_status,
				t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time
				FROM %s.english_company AS t
				INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
				)AS m
		WHERE 1=1                       
`

	if condition != "" {
		sql += condition
	}

	sql += ` GROUP BY m.company_status `

	sql = fmt.Sprintf(sql, databaseName)

	_, err = o.Raw(sql, pars).QueryRows(&list)
	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
}