hz_crm_api/models/overseas_custom/custom.go

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"`
}

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{}, companyStatus string) (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`
	if companyStatus == "正式" {
		sql += ` AND b.status IN('正式','永续') `
	} else if companyStatus == "试用" {
		sql += ` AND b.status='试用' `
	} else if companyStatus == "关闭" {
		sql += ` AND b.status IN('冻结','流失','关闭') `
	}
	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 `
	if companyStatus == "试用" {
		sql += ` AND t.enabled IN(1,2)`
	} else if companyStatus == "禁用" {
		sql += ` AND t.enabled = 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{}, companyStatus, 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
				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`
	if companyStatus == "正式" {
		sql += ` AND b.status IN('正式','永续') `
	} else if companyStatus == "试用" {
		sql += ` AND b.status='试用' `
	} else if companyStatus == "关闭" {
		sql += ` AND b.status IN('冻结','流失','关闭') `
	}
	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 `
	if companyStatus == "试用" {
		sql += ` AND t.enabled IN(1,2)`
	} else if companyStatus == "禁用" {
		sql += ` AND t.enabled = 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 `
	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
}