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:"路演活动状态"` } // 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 } // 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 }