hongze_task/models/free_viewer.go

package models

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

type FreeViewerDetails struct {
	UserId         int
	RealName       string
	Mobile         string
	CompanyName    string
	LastTime       string
	Email          string
	ApplyRecordId  int
}

func GetFreeViewerDetails(startTime, endTime string) (items []*FreeViewerDetails, err error) {
	sql := `SELECT
	a.user_id,
	a.real_name,
	a.mobile,
	a.email,
    y.apply_record_id,
    IF(a.company_id > 1,b.company_name,a.note) AS company_name,
	IF(y.apply_record_id > 0,y.create_time, a.created_time) as last_time
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 or bp.company_product_id is null)
	AND ( a.mobile IS NOT NULL ) 
	AND ( a.mobile <> '' ) 
	AND ( c.create_platform <> 4 OR c.create_platform IS NULL )
    AND (y.status = "潜在用户" or y.status is null or y.status = "权益用户" or y.status = "流失" )
	AND ((y.apply_record_id > 0 and y.create_time > ? and y.create_time <= ?) OR (y.apply_record_id is null AND a.created_time > ? and a.created_time <= ?) )
  GROUP BY a.user_id ORDER BY last_time asc
	`
	_, err = orm.NewOrm().Raw(sql, startTime, endTime, startTime, endTime).QueryRows(&items)
	return
}

// DealWxUser 处理用户标记状态
func DealWxUsers(userIds string) (err error) {
	sql := `update wx_user set is_deal = 1 where user_id in (`+userIds+`) and is_deal=0`
	_, err = orm.NewOrm().Raw(sql).Exec()
	return
}

// DealFiccApply 处理用户申请标记状态
func DealFiccApply(applyRecordIds string, dealTime time.Time) (err error) {
	sql := `update yb_apply_record set op_status = 1, deal_time=?  where apply_record_id in (`+applyRecordIds+`) and op_status=0`
	_, err = orm.NewOrm().Raw(sql, dealTime).Exec()
	return
}