hongze_task/models/overseas_custom/custom.go

package overseas_custom

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"hongze/hongze_task/utils"
	"time"
)

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 RsCompany struct {
	CompanyId int
	Total     int
}

// EnglishCompany 英文客户
type EnglishCompany struct {
	CompanyId      int       `orm:"column(company_id);pk" description:"英文客户ID"`
	CompanyName    string    `description:"客户名称"`
	CountryCode    string    `description:"国家Code"`
	Country        string    `description:"国家"`
	SellerId       int       `description:"销售ID"`
	SellerName     string    `description:"销售姓名"`
	ViewTotal      int       `description:"累计点击量/阅读量"`
	IsDeleted      int       `description:"删除状态：0-正常；1-已删除"`
	CreateTime     time.Time `description:"创建时间"`
	ModifyTime     time.Time `description:"更新时间"`
	Enabled        int       `description:"0-禁用; 1-启用; 2-部分禁用"`
	Status         int       `description:"1:正式,2:临时,3:终止"`
	Nation         string    `description:"所属国家"`
	IsHide         int       `description:"是否隐藏:0:不隐藏,1:隐藏"`
	OverseasStatus string    `description:"海外客户状态:'正式','试用','关闭'"`
	OverseasLabel  int       `description:"海外客户试用子标签：1未分类、2  推进、3 跟踪、4 预备、"`
	RoadShowTotal  int       `description:"累计路演次数"`
	LastViewTime   string    `description:"最后一次阅读时间"`
}

// 获取客户路演数据
func (obj *Custom) GetRsCompanyTotal() (list []*RsCompany, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.company_id,COUNT(1) AS total
				FROM rs_calendar AS a
				INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id = b.rs_calendar_id 
				WHERE
				a.source = 0 
				AND b.status = 2 
				AND a.english_company=1
				GROUP BY a.company_id`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// 获取所有英文客户
func (obj *Custom) GetEnglishCompanyAll() (list []*EnglishCompany, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM english_company WHERE is_deleted = 0 `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

func (obj *Custom) UpdateEnglishCompanyRoadShowTotal(companyId, total int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` UPDATE english_company SET road_show_total=? WHERE company_id=? `
	_, err = o.Raw(sql, total, companyId).Exec()
	return
}

// 获取客户路演数据
func (obj *Custom) GetEnglishCompanyLastViewTime() (list []*EnglishCompany, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT b.company_id,MAX(a.create_time) AS last_view_time
			FROM  english_report_email_pv AS a
			INNER JOIN english_report_email AS b ON a.email_id=b.id
			GROUP BY b.company_id `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

func (obj *Custom) UpdateEnglishCompanyLastViewTime(companyId int, lastViewTime string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` UPDATE english_company SET last_view_time=? WHERE company_id=? `
	_, err = o.Raw(sql, lastViewTime, companyId).Exec()
	return
}

type OverseasCompany struct {
}

// 获取客户路演数据
func (obj *Custom) GetOverseasCompany() (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,
		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.product_id=1 AND 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,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 '关闭'
				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 AND t.is_deleted=0 
	)AS m WHERE 1=1
	AND m.company_status<>m.overseas_status  `

	sql = fmt.Sprintf(sql, databaseName)
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

func (obj *Custom) UpdateEnglishCompanyOverseasStatus(companyId int, companyStatus string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` UPDATE english_company SET overseas_status=? WHERE company_id=? AND overseas_status<>'正式' `
	_, err = o.Raw(sql, companyStatus, companyId).Exec()
	return
}

func (obj *Custom) UpdateCompanyOverseasStatus(companyId int, companyStatus string) (err error) {
	o := orm.NewOrm()
	sql := ` UPDATE company SET overseas_status=? WHERE company_id=? AND overseas_status<>'正式' `
	_, err = o.Raw(sql, companyStatus, companyId).Exec()
	return
}