hz_crm_api/models/roadshow/public_meeting.go

package roadshow

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

//公开会议总览

type PublicMeetingView struct {
	RsCalendarId int    `description:"日历活动id"`
	MinTime      string `description:"活动最小时间"`
	MaxTime      string `description:"活动最大时间"`
}

//获取公开会议
func GetPublicMeetingList(endDate string) (list []*PublicMeetingView, err error) {
	sql := ` SELECT a.rs_calendar_id,min(DATE_FORMAT(CONCAT(b.start_date," ",b.start_time),'%Y-%m-%d %H:%i:%S')) AS min_time,max(DATE_FORMAT(CONCAT(b.end_date," ",b.end_time),'%Y-%m-%d %H:%i:%S')) AS max_time
			FROM rs_calendar AS a
			INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
			WHERE a.source=0 AND activity_type='公开会议'
			AND b.end_date>=?
			group by a.rs_calendar_id  `
	o := orm.NewOrm()
	_, err = o.Raw(sql, endDate).QueryRows(&list)
	return
}

func ModifyCalendarUnionCode(unionCode string, rsCalendarId int) (err error) {
	sql := ` UPDATE rs_calendar SET union_code=? WHERE rs_calendar_id=? `
	o := orm.NewOrm()
	_, err = o.Raw(sql, unionCode, rsCalendarId).Exec()
	if err != nil {
		return err
	}
	sql = ` UPDATE rs_calendar_researcher SET union_code=? WHERE rs_calendar_id=? `
	_, err = o.Raw(sql, unionCode, rsCalendarId).Exec()
	if err != nil {
		return err
	}
	return
}

func GetCoincideMeeting(startTime, endTime string) (list []*PublicMeetingView, err error) {
	sql := ` SELECT * FROM (
			SELECT a.rs_calendar_id,MIN(DATE_FORMAT(CONCAT(b.start_date," ",b.start_time),'%Y-%m-%d %H:%i:%S')) AS min_time,MAX(DATE_FORMAT(CONCAT(b.end_date," ",b.end_time),'%Y-%m-%d %H:%i:%S')) AS max_time
						FROM rs_calendar AS a
						INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
						WHERE a.source=0 AND activity_type='公开会议'
						GROUP BY a.rs_calendar_id
			) AS t
			WHERE (t.min_time<=? AND t.max_time>=?) OR (?>=t.min_time AND ?<=t.max_time) or 
(t.min_time >= ? AND t.max_time <=?) `
	o := orm.NewOrm()
	_, err = o.Raw(sql, startTime, startTime, endTime, endTime, startTime, endTime).QueryRows(&list)
	return
}

func GetCoincideMeetingMinAndMaxTime(startTime, endTime string) (min_time, max_time string, err error) {
	sql := ` select min(t.min_time) as min_time,max(t.max_time) as max_time from (
			SELECT a.rs_calendar_id,MIN(DATE_FORMAT(CONCAT(b.start_date," ",b.start_time),'%Y-%m-%d %H:%i:%S')) AS min_time,MAX(DATE_FORMAT(CONCAT(b.end_date," ",b.end_time),'%Y-%m-%d %H:%i:%S')) AS max_time
						FROM rs_calendar AS a
						INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
						WHERE a.source=0 AND activity_type='公开会议'
						GROUP BY a.rs_calendar_id
			)as t
			where (t.min_time<=? AND t.max_time>=?) OR (?>=t.min_time AND ?<=t.max_time) `
	o := orm.NewOrm()
	err = o.Raw(sql, startTime, startTime, endTime, endTime).QueryRow(&min_time, &max_time)
	return
}

func ClearCalendarUnionCode(nowDate string) (err error) {
	o := orm.NewOrm()
	var rs_calendar_id string
	sql := `SELECT group_concat(distinct a.rs_calendar_id) AS rs_calendar_id FROM rs_calendar AS a
			INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
			WHERE a.source=0 AND activity_type='公开会议'
			AND b.end_date>=? `
	err = o.Raw(sql, nowDate).QueryRow(&rs_calendar_id)

	sql = ` UPDATE rs_calendar SET union_code='' WHERE rs_calendar_id IN(` + rs_calendar_id + `) `

	_, err = o.Raw(sql).Exec()
	if err != nil {
		return err
	}
	sql = ` UPDATE rs_calendar_researcher SET union_code='' WHERE rs_calendar_id IN(` + rs_calendar_id + `) `
	_, err = o.Raw(sql).Exec()
	if err != nil {
		return err
	}
	return
}

/*
sql := ` SELECT b.* FROM rs_calendar AS a
			INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
			WHERE a.source=0 AND activity_type='公开会议'
			AND b.end_date>=?
			ORDER BY a.rs_calendar_id ASC  `
*/

func GetExistMeetingCode(startTime, endTime string) (union_code string, err error) {
	sql := ` SELECT union_code FROM rs_calendar_researcher AS a
			WHERE DATE_FORMAT(CONCAT(a.start_date," ",a.start_time),'%Y-%m-%d %H:%i:%S')>=?
			AND DATE_FORMAT(CONCAT(a.end_date," ",a.end_time),'%Y-%m-%d %H:%i:%S')<=?
			group by a.union_code
			LIMIT 1 `
	o := orm.NewOrm()
	err = o.Raw(sql, startTime, endTime).QueryRow(&union_code)
	return
}