hz_crm_api/models/roadshow/report_record.go

package roadshow

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

type RsReportRecord struct {
	RsReportRecordId           int       `orm:"column(rs_report_record_id);pk"`
	CompanyId                  int       `description:"客户id"`
	CompanyName                string    `description:"客户名称"`
	CreditCode                 string    `description:"社会统一信用码"`
	CompanyStatus              string    `description:"客户状态:'试用','永续','冻结','流失','正式','潜在'"`
	ResearcherId               int       `description:"研究员id"`
	ResearcherName             string    `description:"研究员名称"`
	ResearcherGroupId          int       `description:"研究员分组id"`
	ResearcherGroupName        string    `description:"研究员分组名称"`
	SellerId                   int       `description:"所属销售id"`
	SellerName                 string    `description:"所属销售名称"`
	SellerGroupId              int       `description:"销售分组id"`
	SellerGroupName            string    `description:"销售分组名称"`
	RsCalendarId               int       `description:"路演活动id"`
	RsCalendarResearcherId     int       `description:"路演研究员id"`
	StartDate                  string    `description:"开始日期"`
	EndDate                    string    `description:"结束日期"`
	StartTime                  string    `description:"开始时间"`
	EndTime                    string    `description:"结束时间"`
	StartWeek                  string    `description:"开始日期对应周"`
	EndWeek                    string    `description:"结束日期对应周"`
	CreateTime                 time.Time `description:"记录创建时间"`
	RsCalendarResearcherStatus int       `description:"路演活动状态"`
}

// RsReportRecordResp 总统计信息
type RsReportRecordResp struct {
	List                  []GroupReportRecord `description:"系统用户分组信息"`
	RsReportRecordNumList []RsReportRecordNum `description:"统计次数"`
}

// GroupReportRecord 分组统计信息
type GroupReportRecord struct {
	Item                  []AdminReportRecord `description:"系统用户信息"`
	Name                  string              `description:"分组名"`
	GruopId               int                 `description:"分组id"`
	RsReportRecordNumList []RsReportRecordNum `description:"统计次数"`
}

// AdminReportRecord 系统用户统计信息
type AdminReportRecord struct {
	Name                  string              `description:"系统用户名"`
	AdminId               int                 `description:"系统用户id"`
	RsReportRecordNumList []RsReportRecordNum `description:"统计次数"`
}

// RsReportRecordNum 系统用户统计信息
type RsReportRecordNum struct {
	TryOutNum  int    `description:"试用路演次数"`
	FormalNum  int    `description:"正式路演次数"`
	MeetingNum int    `description:"会议次数"`
	OnlineNum  int    `description:"线上路演次数"`
	OfflineNum int    `description:"线上路演次数"`
	StartDate  string `description:"开始日期"`
	EndDate    string `description:"结束日期"`
	IsLook     bool   `description:"是否可以查看详情"`
}

func DeleteRsReportRecord(rsCalendarId, rsCalendarResearcherId int) (err error) {
	o := orm.NewOrm()
	sql := ` DELETE FROM rs_report_record WHERE rs_calendar_id=? `
	_, err = o.Raw(sql, rsCalendarId).Exec()
	return
}

type RsReportRecordGroup struct {
	AdminId   int    `description:"系统用户id"`
	GroupId   int    `description:"系统用户分组id"`
	AdminName string `description:"系统用户名称"`
	Num       int    `description:"汇总次数"`
}

// GetGroupResearcherRecordList 获取研究员分组数据
func GetGroupResearcherRecordList(condition string, pars []interface{}) (list []*RsReportRecordGroup, err error) {
	o := orm.NewOrm()
	sql := ` SELECT researcher_id as admin_id,researcher_group_id as group_id,researcher_name admin_name,count(1) num
				FROM  rs_report_record 
				WHERE 1=1 and rs_calendar_researcher_status = 2 
 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY researcher_id`
	_, err = o.Raw(sql, pars).QueryRows(&list)

	return
}

// GetGroupSellerRecordList 获取销售分组数据
func GetGroupSellerRecordList(condition string, pars []interface{}) (list []*RsReportRecordGroup, err error) {
	o := orm.NewOrm()
	sql := ` SELECT seller_id as admin_id,seller_group_id as group_id,seller_name admin_name,count(1) num
				FROM  rs_report_record 
				WHERE 1=1 and rs_calendar_researcher_status = 2 
 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY seller_id`
	_, err = o.Raw(sql, pars).QueryRows(&list)

	return
}

// RsReportRecordList 路演统计详情返回数据
type RsReportRecordList struct {
	RsReportRecordId           int       `orm:"column(rs_report_record_id);pk"`
	CompanyId                  int       `description:"客户id"`
	CompanyName                string    `description:"客户名称"`
	CreditCode                 string    `description:"社会统一信用码"`
	CompanyStatus              string    `description:"客户状态:'试用','永续','冻结','流失','正式','潜在'"`
	ResearcherId               int       `description:"研究员id"`
	ResearcherName             string    `description:"研究员名称"`
	SellerId                   int       `description:"所属销售id"`
	SellerName                 string    `description:"所属销售名称"`
	RsCalendarId               int       `description:"路演活动id"`
	RsCalendarResearcherId     int       `description:"路演研究员id"`
	StartDate                  string    `description:"开始日期"`
	EndDate                    string    `description:"结束日期"`
	StartTime                  string    `description:"开始时间"`
	EndTime                    string    `description:"结束时间"`
	StartWeek                  string    `description:"开始日期对应周"`
	EndWeek                    string    `description:"结束日期对应周"`
	CreateTime                 time.Time `description:"记录创建时间"`
	RsCalendarResearcherStatus int       `description:"路演活动状态"`
	ActivityType               string    `description:"活动类型"`
	RoadshowType               string    `description:"路演形式"`
	RoadshowPlatform           string    `description:"路演平台"`
	Theme                      string    `description:"会议主题"`
	Province                   string    `description:"省"`
	City                       string    `description:"市"`
	CooperationName            string    `description:"合作方名称"`
}

// GetRsReportRecordList 获取路演统计详情返回数据
func GetRsReportRecordList(condition string, pars []interface{}) (list []*RsReportRecordList, err error) {
	o := orm.NewOrm()
	sql := ` SELECT a.*,b.theme,b.roadshow_platform,b.province,b.city,b.cooperation_name
				FROM  rs_report_record a 
				join rs_calendar b on a.rs_calendar_id=b.rs_calendar_id
				WHERE 1=1 and a.rs_calendar_researcher_status = 2 
 `
	if condition != "" {
		sql += condition
	}
	sql += ` order BY a.start_date asc,a.start_time asc`
	_, err = o.Raw(sql, pars).QueryRows(&list)

	return
}

type BusinessTripResp struct {
	GroupList []*BusinessTripGroup
	BaseDate  string `description:"用于查询上下两周的开始日期"`
}

type BusinessTripGroup struct {
	GroupName      string
	GroupId        int
	ResearcherList []*BusinessTripResearcher
}

type BusinessTripResearcher struct {
	AdminId          int    `description:"研究员id"`
	RealName         string `description:"研究员名称"`
	GroupId          int    `description:"分组id"`
	GroupName        string `description:"分组名称"`
	RoleTypeCode     string `description:"角色编码"`
	Province         string `description:"省"`
	City             string `description:"市"`
	BusinessTripList []*BusinessTrip
}

type BusinessTrip struct {
	WeekDate string `description:"开始日期"`
	City     string `description:"城市"`
	Week     string `description:"周"`
	WeekType string `description:"当前周:current,下一周:next"`
}

// GetResearcherV2 获取研究员列表（冻结的也要）
func GetBusinessTripResearcher() (list []*BusinessTripResearcher, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM admin AS a
			WHERE a.role_type_code IN('researcher','rai_researcher','ficc_researcher','ficc_admin')
			AND a.enabled=1 and admin_id !=92 `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

type BusinessTripResearcherList struct {
	Province       string `description:"省"`
	City           string `description:"市"`
	StartDate      string `description:"路演开始日期"`
	EndDate        string `description:"路演结束日期"`
	ResearcherId   int    `description:"研究员id"`
	ResearcherName string `description:"研究员姓名"`
	StartDateTime  string `description:"研究员姓名"`
}

func GetBusinessTripList(adminId int, startDate, endDate string) (list []*BusinessTripResearcherList, err error) {
	o := orm.NewOrm()
	sql := ` SELECT a.province,a.city,b.start_date,b.end_date,b.researcher_id,b.researcher_name,CONCAT(b.start_date,' ',b.start_time) AS start_date_time
			FROM  rs_calendar AS a
			INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
			INNER JOIN admin AS c ON b.researcher_id=c.admin_id
			WHERE a.roadshow_type='线下'
			AND b.status=2
			AND a.city<>c.city
			AND b.start_date>=?
			AND b.start_date<=? `
	if adminId > 0 {
		sql += ` AND b.researcher_id=? `
		//sql += ` GROUP BY b.researcher_id,b.start_date `
		sql += ` ORDER BY b.researcher_id ASC,start_date_time ASC `
		_, err = o.Raw(sql, startDate, endDate, adminId).QueryRows(&list)
	} else {
		//sql += ` GROUP BY b.researcher_id,b.start_date `
		sql += ` ORDER BY b.researcher_id ASC,start_date_time ASC `
		_, err = o.Raw(sql, startDate, endDate).QueryRows(&list)
	}
	return
}