hz_crm_api/models/yb/apply_record.go

package yb

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

type ApplyRecord struct {
	ApplyRecordId   int       `orm:"column(apply_record_id);pk" description:"申请记录ID"`
	UserId          int       `description:"微信用户ID"`
	BusinessCardUrl string    `description:"名片"`
	RealName        string    `description:"用户姓名"`
	CompanyName     string    `description:"公司名称"`
	Mobile          string    `description:"手机号"`
	Email           string    `description:"邮箱"`
	Permission      string    `description:"关注品种/申请权限，多个权限用英文,隔开"`
	FiccSellerId    int       `description:"ficc销售id，没有开通ficc权限的话，那么所属销售id为0"`
	SellerName      string    `description:"销售姓名，多个产品销售用/隔开"`
	Status          string    `description:"用户状态"`
	CompanyIdPay    int       `description:"已付费客户公司id"`
	CompanyNamePay  string    `description:"已付费客户公司名称"`
	OpStatus        int       `description:"操作状态，0:未处理,1:已处理"`
	DealTime        time.Time `description:"处理时间"`
	SysUserId       int       `description:"处理人id"`
	CreateTime      time.Time `description:"创建时间"`
	IsMove          int       `description:"是否已移动"`
	Source          int       `description:"申请来源：1-我的 2-活动 3-图库"`
	MarkGroup       string    `description:"标记分组"`
}

func (applyRecord *ApplyRecord) TableName() string {
	return "yb_apply_record"
}

type ApplyList struct {
	ApplyRecordId   int       `orm:"column(apply_record_id);pk" description:"申请记录ID"`
	UserId          int       `description:"微信用户ID"`
	BusinessCardUrl string    `description:"名片"`
	RealName        string    `description:"用户姓名"`
	CompanyId       int       `description:"现有用户公司ID/潜在用户此ID=1"`
	CompanyName     string    `description:"现有用户公司名称"`
	UserCompanyName string    `description:"潜在客户自填公司名称"`
	Mobile          string    `description:"手机号"`
	Email           string    `description:"邮箱"`
	Permission      string    `description:"关注品种/申请权限，多个权限用英文,隔开"`
	SellerName      string    `description:"销售姓名"`
	Status          string    `description:"用户状态"`
	OpStatus        int       `description:"操作状态"`
	IsMove          int       `description:"是否已移动"`
	Source          int       `description:"申请来源 1-我的 2-活动 3-图库"`
	SourceAgent     int       `description:"申请入口来源，1：小程序，2：pc"`
	FromPage        string    `description:"申请来源具体页面"`
	CreateTime      time.Time `description:"申请时间"`
}

type ApplyListV2 struct {
	UserId           int       `description:"微信用户ID"`
	BusinessCardUrl  string    `description:"名片"`
	RealName         string    `description:"用户姓名"`
	CompanyId        int       `description:"现有用户公司ID/潜在用户此ID=1"`
	CompanyName      string    `description:"现有用户公司名称"`
	Mobile           string    `description:"手机号"`
	Email            string    `description:"邮箱"`
	Permission       string    `description:"关注品种/申请权限，多个权限用英文,隔开"`
	SellerName       string    `description:"现销售姓名"`
	SellerId         int       `description:"现销售ID"`
	Status           string    `description:"用户状态"`
	OpStatus         int       `description:"操作状态"`
	IsMove           int       `description:"是否已移动"`
	Source           int       `description:"申请来源 1-我的 2-活动 3-图库"`
	SourceAgent      int       `description:"申请入口来源，1：小程序，2：pc"`
	FromPage         string    `description:"申请来源具体页面`
	ApplyStatus      string    `description: 申请状态：已申请，未申请`
	LastTime         time.Time `description:"最新提交时间"`
	LastTimeStr      string    `description:"最新提交时间"`
	ViewTotal        int       `description:"累计阅读次数"`
	ApplyTotal       int       `description:"累计提交次数"`
	LastViewTimeStr  string    `description:"最近一次阅读时间"`
	LastViewTime     time.Time `description:"最近一次阅读时间"`
	ApplyRecordId    int       `description:"申请记录ID"`
	OriginSellerName string    `description:"原销售姓名"`
	RegisterSource   int       `description:"注册来源"`
	SourceStr        string    `description:"来源"`
	DelBtn           bool      `description:"是否展示删除按钮，true展示，false隐藏"`
	MarkGroup        string    `description:"标记分组"`
}

// GetApplyRecordList 获取申请记录列表
func GetApplyRecordList(condition string, pars []interface{}, startSize, pageSize int) (total int, list []*ApplyList, err error) {
	o := orm.NewOrm()
	fields := "a.apply_record_id, a.user_id, a.real_name, a.business_card_url, a.company_name AS user_company_name," +
		" a.mobile, a.email, a.permission, a.seller_name, a.op_status, a.create_time, a.status, a.is_move, a.source, a.source_agent, a.from_page," +
		" b.company_id, b.company_name"
	sql := "SELECT " + fields + " FROM yb_apply_record AS a LEFT JOIN company_product AS b ON a.company_id_pay = b.company_id AND b.product_id = 1 WHERE 1=1"
	sql += condition
	sql += ` ORDER BY a.create_time desc`

	totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z `
	err = o.Raw(totalSql, pars).QueryRow(&total)
	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}

// Update 更新申请记录
func (applyRecord *ApplyRecord) Update(cols []string) (err error) {
	o := orm.NewOrm()
	_, err = o.Update(applyRecord, cols...)
	return
}

// GetApplyRecordById 获取申请记录
func GetApplyRecordById(applyRecordId int) (item *ApplyRecord, err error) {
	o := orm.NewOrm()
	sql := "select * from yb_apply_record where apply_record_id = ?"
	err = o.Raw(sql, applyRecordId).QueryRow(&item)
	return
}

// DealUnDealFiccApplyByIds 批量处理用户申请标记状态
func DealUnDealFiccApplyByIds(userId, sysUserId int, dealTime time.Time) (err error) {
	sql := `update yb_apply_record set op_status=1, is_move=1, sys_user_id=?, deal_time=?  where user_id=? and (op_status = 0 or is_move=0)`
	_, err = orm.NewOrm().Raw(sql, sysUserId, dealTime, userId).Exec()
	return
}

// DealUserApplyRecords 标记处理用户所有待处理申请记录
func DealUserApplyRecords(userId, sysUserId int, dealTime time.Time) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE yb_apply_record SET op_status=1, sys_user_id=?, deal_time=? WHERE op_status=0 AND user_id=?`
	_, err = o.Raw(sql, sysUserId, dealTime, userId).Exec()
	return
}

// GetApplyRecordListV2 获取用户申请记录列表
func GetApplyRecordListV2(condition string, pars []interface{}, startTime string, startSize, pageSize int) (list []*ApplyListV2, err error) {
	o := orm.NewOrm()
	sql := `
SELECT
	a.user_id,
	a.real_name,
	a.mobile,
	a.email,
	a.company_id,
    IF(a.company_id > 1,b.company_name,a.note) AS company_name,
    #(SELECT count(1) FROM yb_apply_record AS ya WHERE ya.user_id=a.user_id GROUP BY a.user_id) AS apply_total,
	a.report_last_view_time AS last_view_time,
	a.source as register_source,
	#a.created_time as register_time,
	y.apply_record_id,
	IF(y.apply_record_id > 0,y.create_time, a.created_time) as last_time,
	y.seller_name as origin_seller_name,
	IF(y.apply_record_id > 0,y.status, "潜在用户") as status,
	IF(y.apply_record_id > 0,"已申请", "未申请") as apply_status,
	y.source_agent,
    IF(y.apply_record_id > 0,y.op_status, a.is_deal) as op_status,
	y.permission,
	y.is_move,
	y.source,
	y.from_page,
	IF(y.mark_group = '' OR y.mark_group IS NULL OR y.apply_record_id = 0 OR y.apply_record_id IS NULL , a.mark_group, y.mark_group) AS mark_group,
    y.company_name as user_company_name
    #bp.seller_id,
	#bp.seller_name
FROM
	wx_user AS a
	LEFT JOIN company AS b ON a.company_id = b.company_id
    LEFT JOIN (SELECT * from company_product where product_id = 1) AS bp ON a.company_id = bp.company_id
	LEFT JOIN (SELECT user_record_id, create_platform, user_id from user_record) AS c ON a.user_id = c.user_id 
	LEFT JOIN (SELECT * from yb_apply_record where apply_record_id in (SELECT max(apply_record_id) from yb_apply_record GROUP BY user_id)) as y on a.user_id = y.user_id
WHERE
	b.enabled = 1 
	AND ( y.apply_record_id > 0 or a.company_id=1 )
	AND ( a.mobile IS NOT NULL || a.email IS NOT NULL ) 
	AND ( a.mobile <> '' OR a.email <> '' ) 
	AND ( c.create_platform in (1,3,6,9) OR (c.create_platform IS NULL and a.register_platform!=7) )
    AND ((y.apply_record_id > 0 and y.create_time > ?) OR (y.apply_record_id is null AND a.created_time > ?) )
	AND ( y.source > 0 OR y.source IS NULL)
`
	sql += condition
	sql += ` GROUP BY a.user_id ORDER BY last_time desc`

	sql += ` LIMIT ?,?`
	//pars = append(pars, startTime)
	//pars = append(pars, startTime)

	_, err = o.Raw(sql, startTime, startTime, pars, startSize, pageSize).QueryRows(&list)
	return
}

func GetApplyRecordTotal(condition string, pars []interface{}, startTime string) (total int, err error) {
	o := orm.NewOrm()
	sql := `
SELECT
	a.user_id
FROM
	wx_user AS a
	LEFT JOIN company AS b ON a.company_id = b.company_id
    LEFT JOIN (SELECT * from company_product where product_id = 1) AS bp ON a.company_id = bp.company_id
	LEFT JOIN (SELECT user_record_id, create_platform, user_id from user_record) AS c ON a.user_id = c.user_id 
	LEFT JOIN (SELECT * from yb_apply_record where apply_record_id in (SELECT max(apply_record_id) from yb_apply_record GROUP BY user_id)) as y on a.user_id = y.user_id
WHERE
	b.enabled = 1
	AND ( y.apply_record_id > 0 or a.company_id=1)
	AND ( a.mobile IS NOT NULL || a.email IS NOT NULL ) 
	AND ( a.mobile <> '' OR a.email <> '' ) 
	AND ( c.create_platform in (1,3,6,9) OR (c.create_platform IS NULL and a.register_platform!=7) )
    AND ((y.apply_record_id > 0 and y.create_time > ?) OR (y.apply_record_id is null AND a.created_time > ?) )
	AND ( y.source > 0 OR y.source IS NULL)
`
	pars = append(pars, startTime)
	pars = append(pars, startTime)
	sql += condition
	sql += ` GROUP BY a.user_id`

	totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z `
	err = o.Raw(totalSql, pars).QueryRow(&total)
	return
}

// GetApplyRecordListV2Export 用户申请记录列表导出
func GetApplyRecordListV2Export(condition string, pars []interface{}, startTime string) (list []*ApplyListV2, err error) {
	o := orm.NewOrm()
	sql := `
SELECT
	a.user_id,
	a.real_name,
	a.mobile,
	a.email,
	a.company_id,
	IF(a.company_id > 1,b.company_name,a.note) AS company_name,
  # IF( b.type IN ( 1, 2 ), 1, 0 ) AS is_fee_customer,
	# (SELECT count(1) FROM yb_apply_record AS ya WHERE ya.user_id=a.user_id GROUP BY a.user_id) AS apply_total,
	a.report_last_view_time AS last_view_time,
	a.source as register_source,
	#a.created_time as register_time,
	y.apply_record_id,
	IF(y.apply_record_id > 0,y.create_time, a.created_time) as last_time,
	y.seller_name as origin_seller_name,
	IF(y.apply_record_id > 0,y.status, "潜在用户") as status,
	IF(y.apply_record_id > 0,"已申请", "未申请") as apply_status,
	y.source_agent,
	y.op_status,
	y.permission,
	y.is_move,
	y.source,
	y.from_page,
    y.company_name as user_company_name
    #bp.seller_id,
	#bp.seller_name
FROM
	wx_user AS a
	LEFT JOIN company AS b ON a.company_id = b.company_id
    LEFT JOIN (SELECT * from company_product where product_id = 1) AS bp ON a.company_id = bp.company_id
	LEFT JOIN (SELECT user_record_id, create_platform, user_id from user_record) AS c ON a.user_id = c.user_id 
	LEFT JOIN (SELECT * from yb_apply_record where apply_record_id in (SELECT max(apply_record_id) from yb_apply_record GROUP BY user_id)) as y on a.user_id = y.user_id
WHERE
	b.enabled = 1
	AND ( y.apply_record_id > 0 or a.company_id=1)
	AND ( a.mobile IS NOT NULL || a.email IS NOT NULL ) 
	AND ( a.mobile <> '' OR a.email <> '' ) 
	AND ( c.create_platform in (1,3,6,9) OR (c.create_platform IS NULL and a.register_platform!=7) )
    AND ((y.apply_record_id > 0 and y.create_time > ?) OR (y.apply_record_id is null AND a.created_time > ?) )
	AND ( y.source > 0 OR y.source IS NULL)
`
	sql += condition
	sql += ` GROUP BY a.user_id ORDER BY last_time desc`

	//pars = append(pars, startTime)
	//pars = append(pars, startTime)

	_, err = o.Raw(sql, startTime, startTime, pars).QueryRows(&list)
	return
}

type UserApplyTotal struct {
	UserId int
	Total  int
}

// GetTotalByUserIds 获取用户的申请数
func GetTotalByUserIds(userIdsStr string) (list []*UserApplyTotal, err error) {
	o := orm.NewOrm()
	sql := "SELECT count(*) as total, user_id FROM yb_apply_record  WHERE user_id in (" + userIdsStr + ") GROUP BY user_id"
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// DelApplyRecordByUserId 根据用户id删除该用户的所有申请
func DelApplyRecordByUserId(userId int64) (err error) {
	o := orm.NewOrm()
	sql := ` DELETE FROM yb_apply_record WHERE user_id=? `
	_, err = o.Raw(sql, userId).Exec()
	return
}