hz_crm_api/models/roadshow/report_record.go

package roadshow

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/utils"
	"strings"
	"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:"路演活动状态"`
	PermissionName             string    `description:"开通品种"`
	InteractionNum             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:"线上路演次数"`
	RoadShowNum  int    `description:"路演总次数"`
	SalonNum     int    `description:"沙龙路演次数"`
	AskTryOutNum int    `description:"提交过问答的路演次数"`
	AskFormalNum int    `description:"提交过问答的路演次数"`
	AskNum       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
}

// GetGroupResearcherRecordListAsk 获取研究员分组数据()
func GetGroupResearcherRecordListAsk(condition string, pars []interface{}) (list []*RsReportRecordGroup, err error) {
	o := orm.NewOrm()
	sql := `SELECT 
				a.researcher_id AS admin_id,
				a.researcher_group_id AS group_id,
				a.researcher_name admin_name,
				count( 1 ) num  
				FROM  rs_report_record a 
				inner join rs_calendar_researcher AS c ON a.rs_calendar_id=c.rs_calendar_id AND a.rs_calendar_researcher_id=c.rs_calendar_researcher_id
				WHERE 1=1 and a.rs_calendar_researcher_status = 2 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY 	a.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:"合作方名称"`
	CompanyIndustry            string    `description:"客户行业"`
	CompanyClassify            string    `description:"客户分类"`
	QuestionStatus             int       `description:"问答状态：0-未填写；1-已填写"`
	QuestionMsgStatus          int       `description:"问答模板消息：0-未发送；1-已发送"`
	CompanyType                string    `description:"客户类型:FICC/权益"`
	EnglishCompany             int       `description:"是否为英文客户: 0-否; 1-是"`
}

// 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,b.english_company,c.company_industry,c.company_classify,c.question_status,c.question_msg_status
				FROM  rs_report_record a 
				join rs_calendar b on a.rs_calendar_id=b.rs_calendar_id
				inner join rs_calendar_researcher AS c ON a.rs_calendar_id=c.rs_calendar_id AND a.rs_calendar_researcher_id=c.rs_calendar_researcher_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
}

// GetOverseasGroupSellerRecordList 获取销售分组数据
func _GetOverseasGroupSellerRecordList(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
}

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

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

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

func GetOverseasGroupResearcherRecordList(startDate, endDate, companyStatus string) (list []*RsReportRecordGroup, err error) {
	var databaseName string
	if utils.RunMode == "debug" {
		databaseName = "test_v2_hongze_rddp"
	} else {
		databaseName = "hongze_rddp"
	}
	companyStatusList := make([]string, 0)
	if companyStatus != "" {
		companyStatusList = strings.Split(companyStatus, ",")
	}
	lenCompanyStatusList := len(companyStatusList)

	o := orm.NewOrm()

	pars := make([]interface{}, 0)

	sql1 := `SELECT a.rs_calendar_id,b.researcher_id as admin_id,b.researcher_group_id as group_id,b.researcher_name AS admin_name FROM  rs_calendar AS a
    INNER JOIN rs_report_record  AS b ON a.rs_calendar_id=b.rs_calendar_id
         JOIN company_product AS c ON a.company_id=c.company_id AND c.product_id=1
         JOIN company AS d ON c.company_id=d.company_id 
				INNER JOIN overseas_custom_seller AS f ON c.seller_id=f.seller_id
		where c.is_overseas = 0 and  a.english_company= 0 AND a.source = 0  AND b.rs_calendar_researcher_status=2 AND b.researcher_id != 0 `

	if startDate != "" {
		sql1 += ` AND b.start_date >= ? `
		pars = append(pars, startDate)
	}
	if endDate != "" {
		sql1 += ` AND b.end_date <= ? `
		pars = append(pars, endDate)
	}
	if lenCompanyStatusList > 0 {
		sql1 += ` AND d.overseas_status in (` + utils.GetOrmInReplace(lenCompanyStatusList) + ") "
		pars = append(pars, companyStatusList)
	}
	//sql1 += ` GROUP BY admin_id `

	sql2 := fmt.Sprintf(`SELECT aa.rs_calendar_id,bb.researcher_id as admin_id,bb.researcher_group_id as group_id,bb.researcher_name AS admin_name FROM rs_calendar AS aa
    INNER JOIN rs_report_record  AS bb ON aa.rs_calendar_id=bb.rs_calendar_id
    INNER JOIN %s.english_company AS cc ON aa.company_id=cc.company_id
		where aa.english_company= 1 AND aa.source = 0 AND cc.is_deleted=0  AND bb.rs_calendar_researcher_status=2 AND bb.researcher_id != 0 `, databaseName)

	if startDate != "" {
		sql2 += ` AND bb.start_date >= ?  `
		pars = append(pars, startDate)
	}
	if endDate != "" {
		sql2 += ` AND bb.end_date <= ?  `
		pars = append(pars, endDate)
	}
	if lenCompanyStatusList > 0 {
		sql2 += ` AND cc.overseas_status in (` + utils.GetOrmInReplace(lenCompanyStatusList) + ") "
		pars = append(pars, companyStatusList)
	}
	//sql2 += ` GROUP BY admin_id `

	// 列表数据
	sql := `SELECT m.*,count(1) as num  FROM (` + sql1 + ` UNION ALL ` + sql2
	sql += ` ) AS m  WHERE 1=1 `

	sql += ` Group by admin_id `
	_, err = o.Raw(sql, pars).QueryRows(&list)

	return
}

func GetOverseasGroupSellerRecordList(startDate, endDate, companyStatus string) (list []*RsReportRecordGroup, err error) {
	var databaseName string
	if utils.RunMode == "debug" {
		databaseName = "test_v2_hongze_rddp"
	} else {
		databaseName = "hongze_rddp"
	}
	companyStatusList := make([]string, 0)
	if companyStatus != "" {
		companyStatusList = strings.Split(companyStatus, ",")
	}
	lenCompanyStatusList := len(companyStatusList)

	o := orm.NewOrm()

	pars := make([]interface{}, 0)

	sql1 := `SELECT COUNT(DISTINCT a.rs_calendar_id) AS num,b.seller_id as admin_id,b.seller_group_id as group_id,b.seller_name AS admin_name FROM  rs_calendar AS a
    INNER JOIN rs_report_record  AS b ON a.rs_calendar_id=b.rs_calendar_id
         JOIN company_product AS c ON a.company_id=c.company_id AND c.product_id=1
         JOIN company AS d ON c.company_id=d.company_id 
		 INNER JOIN overseas_custom_seller AS f ON c.seller_id=f.seller_id
		where c.is_overseas = 0 and  a.english_company= 0 AND a.source = 0  AND b.rs_calendar_researcher_status=2 AND b.researcher_id != 0`

	if startDate != "" {
		sql1 += ` AND b.start_date >= ? `
		pars = append(pars, startDate)
	}
	if endDate != "" {
		sql1 += ` AND b.end_date <= ? `
		pars = append(pars, endDate)
	}
	if lenCompanyStatusList > 0 {
		sql1 += ` AND d.overseas_status in (` + utils.GetOrmInReplace(lenCompanyStatusList) + ") "
		pars = append(pars, companyStatusList)
	}
	sql1 += ` GROUP BY admin_id `

	sql2 := fmt.Sprintf(`SELECT COUNT(DISTINCT aa.rs_calendar_id) AS num,bb.seller_id as admin_id,bb.seller_group_id as group_id,bb.seller_name AS admin_name FROM rs_calendar AS aa
    INNER JOIN rs_report_record  AS bb ON aa.rs_calendar_id=bb.rs_calendar_id
    INNER JOIN %s.english_company AS cc ON aa.company_id=cc.company_id
		where aa.english_company= 1 AND aa.source = 0 AND cc.is_deleted=0  AND bb.rs_calendar_researcher_status=2 AND bb.researcher_id != 0 `, databaseName)

	if startDate != "" {
		sql2 += ` AND bb.start_date >= ?  `
		pars = append(pars, startDate)
	}
	if endDate != "" {
		sql2 += ` AND bb.end_date <= ?  `
		pars = append(pars, endDate)
	}
	if lenCompanyStatusList > 0 {
		sql2 += ` AND cc.overseas_status in (` + utils.GetOrmInReplace(lenCompanyStatusList) + ") "
		pars = append(pars, companyStatusList)
	}
	sql2 += ` GROUP BY admin_id `

	// 列表数据
	sql := `SELECT m.*,SUM(m.num) AS num  FROM (` + sql1 + ` UNION ALL ` + sql2
	sql += ` ) AS m  WHERE 1=1 `

	sql += ` Group by admin_id `
	_, err = o.Raw(sql, pars).QueryRows(&list)

	return
}

// 根据ID获取详情
func GetRsReportRecordDetailByRsReportRecordId(rsReportRecordId int) (item *RsReportRecord, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM rs_report_record WHERE rs_report_record_id=? `
	err = o.Raw(sql, rsReportRecordId).QueryRow(&item)
	return
}

// UpdateRsReportRecordPermissionMulti 批量修改
func UpdateRsReportRecordPermissionMulti(items []*RsReportRecord) (err error) {
	o, err := orm.NewOrm().Begin()
	if err != nil {
		return
	}
	defer func() {
		if err == nil {
			o.Commit()
		} else {
			o.Rollback()
		}
	}()

	//批量修改
	p, err := o.Raw("UPDATE rs_report_record SET permission_name = ?  WHERE rs_report_record_id = ?").Prepare()
	if err != nil {
		return
	}
	defer func() {
		_ = p.Close() // 别忘记关闭 statement
	}()

	for _, v := range items {
		_, err = p.Exec(v.PermissionName, v.RsReportRecordId)
		if err != nil {
			return
		}
	}
	return
}

// UpdateRsReportRecordInteractionNumnMulti 批量修改互动数量
func UpdateRsReportRecordInteractionNumnMulti(items []*RsReportRecord) (err error) {
	o, err := orm.NewOrm().Begin()
	if err != nil {
		return
	}
	defer func() {
		if err == nil {
			o.Commit()
		} else {
			o.Rollback()
		}
	}()

	//批量修改
	p, err := o.Raw("UPDATE rs_report_record SET interaction_num = ?  WHERE rs_report_record_id = ?").Prepare()
	if err != nil {
		return
	}
	defer func() {
		_ = p.Close() // 别忘记关闭 statement
	}()

	for _, v := range items {
		_, err = p.Exec(v.InteractionNum, v.RsReportRecordId)
		if err != nil {
			return
		}
	}
	return
}

func GetReportResearcherExport(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,c.company_industry,c.company_classify,c.question_status,c.question_msg_status,b.english_company
				FROM  rs_report_record a 
				join rs_calendar b on a.rs_calendar_id=b.rs_calendar_id
				inner join rs_calendar_researcher AS c ON a.rs_calendar_id=c.rs_calendar_id AND a.rs_calendar_researcher_id=c.rs_calendar_researcher_id
				WHERE 1=1 and a.rs_calendar_researcher_status = 2 
 `
	if condition != "" {
		sql += condition
	}
	sql += ` order BY a.start_date DESC,a.start_time DESC`
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}