hongze_task/models/company.go

package models

import (
	"github.com/beego/beego/v2/client/orm"
	"time"
)

type Company struct {
	CompanyId       int       `orm:"column(company_id);pk"`
	CompanyName     string    `description:"客户名称"`
	CreditCode      string    `description:"社会统一信用码"`
	CompanyCode     string    `description:"客户编码"`
	Sort            int       `description:"优先级"`
	IsFeeCustomer   int       `description:"是否付费用户"`
	Country         string    `description:"国家编码"`
	ProvinceId      int       `description:"省id"`
	CityId          int       `description:"市id"`
	Address         string    `description:"详细地址"`
	CompanyType     int       `orm:"column(type)" description:"客户标签,1：付费客户，2：试用客户，3：流失客户，4：潜在客户"`
	Enabled         int       `description:"用户状态"`
	CreatedTime     time.Time `description:"创建时间"`
	LastUpdatedTime time.Time `description:"最后一次阅读时间"`
	Seller          string    `description:"销售员"`
	SellsId         int       `description:"销售员id"`
	CompanyBelong   string    `description:"客户所属，ficc：ficc客户，public_offering：公募客户，partner：合作伙伴"`
	StartDate       string    `description:"合同开始日期"`
	EndDate         string    `description:"合同结束日期"`
	LoseReason      string    `description:"流失原因"`
	LastType        int       `description:"原客户标签"`
	IsVip           int       `description:"0:普通用户,1:大客户"`
	LossTime        time.Time `description:"流失时间"`
	FirstStartDate  string    `description:"首次设置为试用客户开始时间"`
	FirstEndDate    string    `description:"首次设置为试用客户结束时间"`
	DateType        int       `description:"设置流失类型，1：1个月，2：2个月，3：3个月"`
}

func GetCompanyByName(companyName string) (item *Company, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company WHERE company_name=? `
	err = o.Raw(sql, companyName).QueryRow(&item)
	return
}

func GetCompanyById(companyId int) (item *Company, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company WHERE company_id=? `
	err = o.Raw(sql, companyId).QueryRow(&item)
	return
}

func GetCompanyCountByCreditCode(CreditCode string) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS count FROM company WHERE credit_code=? `
	err = o.Raw(sql, CreditCode).QueryRow(&count)
	return
}

func AddCompany(item *Company) (newId int64, err error) {
	o := orm.NewOrm()
	newId, err = o.Insert(item)
	return
}

func GetCompany() (items []*Company, err error) {
	sql := `SELECT * FROM company WHERE type =1 `
	_, err = orm.NewOrm().Raw(sql).QueryRows(&items)
	return
}

// 待冻结客户
type CompanyNeedFreeze struct {
	CompanyId        int
	CompanyProductId int
	ProductId        int
	CompanyName      string
	ProductName      string
	Status           string
}

func GetCompanyNeedFreeze(endDate string) (items []*CompanyNeedFreeze, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.company_id,b.company_product_id,b.product_id,a.company_name,b.product_name,b.status
            FROM  company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE b.status='试用'
			AND b.end_date<=? `
	_, err = o.Raw(sql, endDate).QueryRows(&items)
	return
}

func GetCompanyNeedLoss(endDate string) (items []*CompanyNeedFreeze, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.company_id,b.company_product_id,b.product_id,a.company_name,b.product_name,b.status
            FROM  company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE b.status='冻结'
			AND b.freeze_end_date<=?  AND b.init_status != '永续'  ` // 权益的永续客户冻结不转流失
	_, err = o.Raw(sql, endDate).QueryRows(&items)
	return
}

// 正式
func GetCompanyNeedTryOut(endDate string) (items []*CompanyNeedFreeze, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.company_id,b.company_product_id,b.product_id,a.company_name,b.product_name,b.status
            FROM  company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE b.status='正式'
			AND b.end_date<=?  AND b.init_status != '永续'  ` //权益的永续客户正式不转试用
	_, err = o.Raw(sql, endDate).QueryRows(&items)
	return
}

type CompanyIndustry struct {
	IndustryId   int    `description:"行业id"`
	IndustryName string `description:"行业名称"`
	ParentId     int    `description:"父级id"`
	Classify     string `description:"分类"`
}

func GetCompanyIndustryByName(industryName string) (items *CompanyIndustry, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM company_industry WHERE industry_name=? `
	err = o.Raw(sql, industryName).QueryRow(&items)
	return
}

func ModifyCompanyCreditCode(companyId int, creditCode string) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company SET credit_code=? WHERE company_id=? AND credit_code='' `
	_, err = o.Raw(sql, creditCode, companyId).Exec()
	return
}

func ModifyCompanyIndustry(industryId, companyId int, industryName string) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE company_product SET industry_id=?,industry_name=? WHERE company_id=? AND product_id=1 AND industry_id=0 `
	_, err = o.Raw(sql, industryId, industryName, companyId).Exec()
	return
}

// 权益 正式->永续
func GetCompanyNeedTryOutXClassRai(endDate string) (items []*CompanyNeedFreeze, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.company_id,b.company_product_id,b.product_id,a.company_name,b.product_name,b.status
            FROM  company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE b.status='正式'
			AND b.end_date<=?  AND b.init_status = '永续'  ` //权益的永续客户正式不转试用
	_, err = o.Raw(sql, endDate).QueryRows(&items)
	return
}

// 权益 永续->冻结
func GetCompanyNeedFreezeXClassRai(endDate string) (items []*CompanyNeedFreeze, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.company_id,b.company_product_id,b.product_id,a.company_name,b.product_name,b.status
            FROM  company AS a
			INNER JOIN company_product AS b ON a.company_id=b.company_id
			WHERE b.status='永续'
			AND b.end_date<=?   AND b.init_status = '永续'  `
	_, err = o.Raw(sql, endDate).QueryRows(&items)
	return
}