hz_crm_api/models/roadshow/rs_calendar_relation.go

package roadshow

import (
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/models/system"
	"hongze/hz_crm_api/utils"
	"strconv"
	"time"
)

// RsCalendarRelation 自系统路演与第三方路演关系表
type RsCalendarRelation struct {
	RelationId       int       `orm:"column(relation_id);pk" description:"关系id"`
	CalendarType     int8      `description:"日历类型；1：路演；2：事项"`
	SelfCalendarId   int       `description:"系统内部的日历id，可以是研究员与路演活动的关系id，也可以是事项id"`
	ThirdCalendarId  int       `description:"第三方路演id"`
	UserId           int       `description:"关系id"`
	UserPhone        string    `description:"创建人手机号"`
	UserName         string    `description:"创建人昵称"`
	ProjectName      string    `description:"活动名称"`
	ProjectId        int       `description:"活动id"`
	CustomerId       int       `description:"客户id"`
	CustomerName     string    `description:"客户名称"`
	CustomerSocial   string    `description:"客户社会信用码"`
	ProjectType      int       `description:"活动类型：1=沙龙，2=路演，3=专家需求，4=研究需求，5=电话，6=面谈，7=专题需求，8=线下沙龙，9=公司调研"`
	ProjectFormType  int       `description:"服务形式：1=沙龙，2=路演，3=专家需求，4=研究需求，5=电话，6=面谈，7=专题需求"`
	Room             int       `description:"会议室id"`
	StartTime        int       `description:"开始时间戳"`
	EndTime          int       `description:"结束时间戳"`
	Content          string    `description:"活动内容"`
	FeedExpert       string    `description:"邀请的专家"`
	Title            string    `description:"日历显示的标题"`
	ResearcherMobile string    `description:"研究员+协同人员手机号（多个使用逗号拼接）"`
	ModifyTime       time.Time `description:"更新时间"`
	CreateTime       time.Time `description:"关系建立时间"`
}

func GetRelationByPars(condition string, pars []interface{}) (items *RsCalendarRelation, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM rs_calendar_relation WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&items)
	return
}

func GetPhoneFromResearcher(calendarResearcherId int) (items *string, err error) {
	o := orm.NewOrm()
	sql := `SELECT mobile FROM admin AS a INNER JOIN rs_calendar_researcher AS b ON a.admin_id=b.researcher_id WHERE b.rs_calendar_researcher_id=?`
	err = o.Raw(sql, calendarResearcherId).QueryRow(&items)
	return
}

func GetPhoneFromRsCalendarById(rsCalendarId int) (item *string, err error) {
	o := orm.NewOrm()
	sql := `SELECT mobile FROM admin AS a INNER JOIN rs_calendar AS b ON a.admin_id=b.sys_user_id WHERE b.rs_calendar_id=? `
	err = o.Raw(sql, rsCalendarId).QueryRow(&item)
	return
}

// GetRsCalendarRelationListByThirdIds 根据第三方id集合获取所有的关系列表
func GetRsCalendarRelationListByThirdIds(thirdCalendarIds []int) (items []*RsCalendarRelation, err error) {
	if len(thirdCalendarIds) <= 0 {
		return
	}
	thirdCalendarIdStr := utils.Implode(thirdCalendarIds)
	o := orm.NewOrm()
	sql := `SELECT * FROM rs_calendar_relation WHERE third_calendar_id in (` + thirdCalendarIdStr + `) `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// AddRsCalendarRelation 添加自系统路演与第三方路演关系
func AddRsCalendarRelation(item *RsCalendarRelation) (lastId int64, err error) {
	o := orm.NewOrm()
	lastId, err = o.Insert(item)
	return
}

// DeleteRsCalendarRelation 删除关联表
func DeleteRsCalendarRelation(relationIdd int) (err error) {
	sql := `DELETE FROM rs_calendar_relation WHERE relation_id=? `
	o := orm.NewOrm()
	_, err = o.Raw(sql, relationIdd).Exec()
	return
}

// SyncRsCalendarRelation 同步自系统路演与第三方路演关系
func SyncRsCalendarRelation(thirdUserCalendar UserCalendar, createUser system.AdminItem, researcherList []system.AdminItem) (err error) {
	currentStartTimer := time.Unix(int64(thirdUserCalendar.StartTime), 0)
	currentEndTimer := time.Unix(int64(thirdUserCalendar.EndTime), 0)

	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	//路演活动表入库
	rsCalendar := &RsCalendar{
		SysUserId:        createUser.AdminId,
		SysUserRealName:  createUser.RealName,
		ActivityType:     "路演",
		RoadshowType:     "",
		RoadshowPlatform: "",
		CompanyId:        0,
		CompanyName:      thirdUserCalendar.CustomerName,
		Province:         "",
		ProvinceCode:     "",
		City:             "",
		CityCode:         "",
		Theme:            "",
		CooperationName:  "",
		Title:            thirdUserCalendar.Title,
		Source:           1, //来源，0：自系统，1：上海方的
		CreateTime:       time.Now(),
		ModifyTime:       time.Now(),
		ActivityCategory: "",
		IsSynced:         1,
	}
	rsCalendarId, err := to.Insert(rsCalendar)
	if err != nil {
		return
	}
	rsCalendar.RsCalendarId = int(rsCalendarId)

	// 路演研究员入库
	rsCalendarResearcherList := make([]*RsCalendarResearcher, 0)
	for _, researcheInfo := range researcherList {
		rsCalendarResearcher := &RsCalendarResearcher{
			RsCalendarResearcherId: 0,
			RsCalendarId:           rsCalendar.RsCalendarId,
			ResearcherId:           researcheInfo.AdminId,
			ResearcherName:         researcheInfo.RealName,
			StartDate:              currentStartTimer.Format(utils.FormatDate),
			EndDate:                currentEndTimer.Format(utils.FormatDate),
			StartTime:              currentStartTimer.Format(utils.FormatTime),
			EndTime:                currentEndTimer.Format(utils.FormatTime),
			StartWeek:              utils.EnWeekToCnWeek(currentStartTimer.Weekday().String()),
			EndWeek:                utils.EnWeekToCnWeek(currentEndTimer.Weekday().String()),
			CreateTime:             time.Now(),
			ModifyTime:             time.Now(),
			Status:                 2, //1:待接受,2:已接受,3:已拒绝,4:已删除,5:已撤回,6:已结束
			RefuseReason:           "",
			//RefuseTime:             time.Time{},
			DeleteReason: "",
			IsSynced:     1,
		}
		rsCalendarResearcherId, tmpErr := to.Insert(rsCalendarResearcher)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		rsCalendarResearcher.RsCalendarResearcherId = int(rsCalendarResearcherId)
		rsCalendarResearcherList = append(rsCalendarResearcherList, rsCalendarResearcher)
	}

	selfCalendarId := 0
	if len(rsCalendarResearcherList) > 0 {
		selfCalendarId = rsCalendarResearcherList[0].RsCalendarResearcherId
	}
	//关系入库
	customerId, _ := strconv.Atoi(thirdUserCalendar.CustomerId)
	rsCalendarRelation := &RsCalendarRelation{
		//RelationId:       0,
		CalendarType:     1,              //日历类型；1：路演；2：事项
		SelfCalendarId:   selfCalendarId, //研究员与路演关系id；
		ThirdCalendarId:  thirdUserCalendar.ID,
		UserId:           thirdUserCalendar.UserId,
		UserPhone:        thirdUserCalendar.UserPhone,
		UserName:         thirdUserCalendar.UserName,
		ProjectName:      thirdUserCalendar.ProjectName,
		ProjectId:        thirdUserCalendar.ProjectId,
		CustomerId:       customerId,
		CustomerName:     thirdUserCalendar.CustomerName,
		CustomerSocial:   thirdUserCalendar.CustomerSocial,
		ProjectType:      thirdUserCalendar.ProjectType,
		ProjectFormType:  thirdUserCalendar.ProjectType,
		Room:             thirdUserCalendar.Room,
		StartTime:        thirdUserCalendar.StartTime,
		EndTime:          thirdUserCalendar.EndTime,
		Content:          thirdUserCalendar.Content,
		FeedExpert:       thirdUserCalendar.FeedExpert,
		Title:            thirdUserCalendar.Title,
		ResearcherMobile: thirdUserCalendar.ResearcherMobile,
		ModifyTime:       time.Now(),
		CreateTime:       time.Now(),
	}
	rsCalendarRelationId, err := to.Insert(rsCalendarRelation)
	if err != nil {
		return
	}
	rsCalendarRelation.RelationId = int(rsCalendarRelationId)

	return
}

// UpdateSyncRsCalendarRelation 同步自系统路演与第三方路演关系
func UpdateSyncRsCalendarRelation(thirdUserCalendar UserCalendar, rsCalendar *RsCalendar, rsCalendarRelation *RsCalendarRelation, updateRsCalendarResearcherList []*RsCalendarResearcher, delResearcherIdList []int, addResearcherList []system.AdminItem) (err error) {
	currentStartTimer := time.Unix(int64(thirdUserCalendar.StartTime), 0)
	currentEndTimer := time.Unix(int64(thirdUserCalendar.EndTime), 0)

	//新增研究员
	//删除研究员
	//更新研究员
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	// 路演活动表修改
	rsCalendar.Title = thirdUserCalendar.Title
	rsCalendar.ModifyTime = time.Now()
	_, err = to.Update(rsCalendar, "Title", "ModifyTime")
	if err != nil {
		return
	}

	// 删除路演研究员
	if len(delResearcherIdList) > 0 {
		delResearcherIdStr := utils.Implode(delResearcherIdList)
		sql := `DELETE FROM rs_calendar_researcher WHERE rs_calendar_researcher_id in (` + delResearcherIdStr + `) `
		_, tmpErr := to.Raw(sql).Exec()
		if tmpErr != nil {
			err = tmpErr
			return
		}
	}

	// 修改路演研究员
	for _, rsCalendarResearcher := range updateRsCalendarResearcherList {
		rsCalendarResearcher.StartDate = currentStartTimer.Format(utils.FormatDate)
		rsCalendarResearcher.EndDate = currentEndTimer.Format(utils.FormatDate)
		rsCalendarResearcher.StartTime = currentStartTimer.Format(utils.FormatTime)
		rsCalendarResearcher.EndTime = currentEndTimer.Format(utils.FormatTime)
		rsCalendarResearcher.StartWeek = utils.EnWeekToCnWeek(currentStartTimer.Weekday().String())
		rsCalendarResearcher.EndWeek = utils.EnWeekToCnWeek(currentEndTimer.Weekday().String())
		rsCalendarResearcher.Status = 2 // 已接受
		rsCalendarResearcher.ModifyTime = time.Now()
		rsCalendarResearcher.IsSynced = 1
		_, tmpErr := to.Update(rsCalendarResearcher, "StartDate", "EndDate", "StartTime", "EndTime", "StartWeek", "EndWeek", "Status", "ModifyTime", "IsSynced")
		if tmpErr != nil {
			err = tmpErr
			return
		}
	}

	// 路演研究员入库
	rsCalendarResearcherList := make([]*RsCalendarResearcher, 0)
	for _, researcheInfo := range addResearcherList {
		rsCalendarResearcher := &RsCalendarResearcher{
			RsCalendarResearcherId: 0,
			RsCalendarId:           rsCalendar.RsCalendarId,
			ResearcherId:           researcheInfo.AdminId,
			ResearcherName:         researcheInfo.RealName,
			StartDate:              currentStartTimer.Format(utils.FormatDate),
			EndDate:                currentEndTimer.Format(utils.FormatDate),
			StartTime:              currentStartTimer.Format(utils.FormatTime),
			EndTime:                currentEndTimer.Format(utils.FormatTime),
			StartWeek:              utils.EnWeekToCnWeek(currentStartTimer.Weekday().String()),
			EndWeek:                utils.EnWeekToCnWeek(currentEndTimer.Weekday().String()),
			CreateTime:             time.Now(),
			ModifyTime:             time.Now(),
			Status:                 2, //1:待接受,2:已接受,3:已拒绝,4:已删除,5:已撤回,6:已结束
			RefuseReason:           "",
			//RefuseTime:             time.Time{},
			DeleteReason: "",
			IsSynced:     1,
		}
		rsCalendarResearcherId, tmpErr := to.Insert(rsCalendarResearcher)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		rsCalendarResearcher.RsCalendarResearcherId = int(rsCalendarResearcherId)
		rsCalendarResearcherList = append(rsCalendarResearcherList, rsCalendarResearcher)
	}

	// 关系表变更
	selfCalendarId := rsCalendarRelation.SelfCalendarId
	if len(updateRsCalendarResearcherList) > 0 {
		selfCalendarId = updateRsCalendarResearcherList[0].RsCalendarResearcherId //更新的研究员关系表id
	} else if len(rsCalendarResearcherList) > 0 {
		selfCalendarId = rsCalendarResearcherList[0].RsCalendarResearcherId //新增的研究员关系表id
	}

	//关系入库
	customerId, _ := strconv.Atoi(thirdUserCalendar.CustomerId)
	rsCalendarRelation.SelfCalendarId = selfCalendarId
	rsCalendarRelation.UserId = thirdUserCalendar.UserId
	rsCalendarRelation.UserPhone = thirdUserCalendar.UserPhone
	rsCalendarRelation.UserName = thirdUserCalendar.UserName
	rsCalendarRelation.ProjectName = thirdUserCalendar.ProjectName
	rsCalendarRelation.ProjectId = thirdUserCalendar.ProjectId
	rsCalendarRelation.CustomerId = customerId
	rsCalendarRelation.CustomerName = thirdUserCalendar.CustomerName
	rsCalendarRelation.CustomerSocial = thirdUserCalendar.CustomerSocial
	rsCalendarRelation.ProjectType = thirdUserCalendar.ProjectType
	rsCalendarRelation.ProjectFormType = thirdUserCalendar.ProjectType
	rsCalendarRelation.Room = thirdUserCalendar.Room
	rsCalendarRelation.StartTime = thirdUserCalendar.StartTime
	rsCalendarRelation.EndTime = thirdUserCalendar.EndTime
	rsCalendarRelation.Content = thirdUserCalendar.Content
	rsCalendarRelation.FeedExpert = thirdUserCalendar.FeedExpert
	rsCalendarRelation.Title = thirdUserCalendar.Title
	rsCalendarRelation.ResearcherMobile = thirdUserCalendar.ResearcherMobile
	rsCalendarRelation.ModifyTime = time.Now()
	_, err = to.Update(rsCalendarRelation, "SelfCalendarId", "UserId", "UserPhone", "UserName", "ProjectName", "ProjectId", "CustomerId", "CustomerName", "CustomerSocial", "ProjectType", "ProjectFormType", "Room", "StartTime", "EndTime", "Content", "FeedExpert", "Title", "ResearcherMobile", "ModifyTime")
	return
}

// UpdateRsCalendarRelation 更新活动关系信息
func UpdateRsCalendarRelation(where, updateParams map[string]interface{}) error {
	o := orm.NewOrm()
	ptrStructOrTableName := "rs_calendar"
	qs := o.QueryTable(ptrStructOrTableName)
	for expr, exprV := range where {
		qs = qs.Filter(expr, exprV)
	}
	_, err := qs.Update(updateParams)
	return err
}

// RsCalendarResearcherRelationInfo
type RsCalendarResearcherRelationInfo struct {
	RsCalendarResearcherId int    `orm:"column(rs_calendar_researcher_id);pk"`
	CalendarType           int8   `description:"日历类型；1：路演；2：事项"`
	ThirdCalendarId        int    `description:"第三方路演id"`
	RsCalendarId           int    `description:"日历活动id"`
	ResearcherId           int    `description:"研究员id"`
	ResearcherName         string `description:"研究员名称"`
	StartDate              string `description:"开始日期"`
	EndDate                string `description:"结束日期"`
	StartTime              string `description:"开始时间"`
	EndTime                string `description:"结束时间"`
	StartWeek              string `description:"开始日期对应周"`
	EndWeek                string `description:"结束日期对应周"`
	CreateTime             time.Time
	ModifyTime             time.Time
	Status                 int       `description:"状态:1:待接受,2:已接受,3:已拒绝,4:已删除,5:已撤回,6:已结束"`
	RefuseReason           string    `description:"拒绝理由"`
	RefuseTime             time.Time `description:"拒绝时间"`
	DeleteReason           string    `description:"删除理由"`
	DeleteTime             time.Time `description:"删除时间"`
	ApproveTime            time.Time `description:"接受时间"`
	IsSynced               int       `description:"是否与上海同步 0:未同步 1:已同步"`
	ResearcherSort         int       `description:"研究员新增排序"`
}

// GetRsCalendarResearcherInfoIByResearcherIdAndDate 根据研究员和开始/结束日期获取上海的活动路演
func GetRsCalendarResearcherInfoIByResearcherIdAndDate(researcherId int, startDate, endDate string) (items []*RsCalendarResearcherRelationInfo, err error) {
	o := orm.NewOrm()
	//	sql := `SELECT a.*,c.third_calendar_id,c.calendar_type FROM rs_calendar_researcher a join rs_calendar b on a.rs_calendar_id=b.rs_calendar_id
	//join rs_calendar_relation c on a.rs_calendar_researcher_id=c.self_calendar_id
	//WHERE b.source=1 and a.status=2 and c.calendar_type=1 and a.researcher_id=? and start_date>=? and end_date <= ? `

	//杭州创建的路演活动，如果上海被删除了，那么也要同步删除杭州的（所以相对于上面的逻辑，下面移除了来源的where条件）
	sql := `SELECT a.*,c.third_calendar_id,c.calendar_type FROM rs_calendar_researcher a 
join rs_calendar b on a.rs_calendar_id=b.rs_calendar_id
join rs_calendar_relation c on a.rs_calendar_researcher_id=c.self_calendar_id
WHERE a.status=2 and c.calendar_type=1 and a.researcher_id=? and start_date>=? and end_date <= ? `
	_, err = o.Raw(sql, researcherId, startDate, endDate).QueryRows(&items)
	return
}