123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412 |
- 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
- }
|