hz_crm_api/models/roadshow/calendar.go

package roadshow

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"hongze/hz_crm_api/models/company"
	"hongze/hz_crm_api/models/system"
	"hongze/hz_crm_api/utils"
	"time"
)

type AddActivityReq struct {
	ActivityType     string `description:"活动类型"`
	RoadshowType     string `description:"路演形式"`
	RoadshowPlatform string `description:"路演平台"`
	CompanyId        int    `description:"客户id"`
	CompanyName      string `description:"客户名称"`
	Province         string `description:"省"`
	ProvinceCode     string `description:"省编码"`
	City             string `description:"市"`
	CityCode         string `description:"市编码"`
	Theme            string `description:"会议主题"`
	CooperationName  string `description:"合作方名称"`
	ActivityCategory string `description:"活动类别"`
	ResearcherList   []*CalendarResearcher
	EnglishCompany   int `description:"是否为英文客户"`
}

type CalendarResearcher struct {
	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:"结束日期对应周"`
}

type RsCalendar struct {
	RsCalendarId     int    `orm:"column(rs_calendar_id);pk"`
	SysUserId        int    `description:"创建人id"`
	SysUserRealName  string `description:"创建人名称"`
	ActivityType     string `description:"活动类型"`
	RoadshowType     string `description:"路演形式"`
	RoadshowPlatform string `description:"路演平台"`
	CompanyId        int    `description:"客户id"`
	CompanyName      string `description:"客户名称"`
	Province         string `description:"省"`
	ProvinceCode     string `description:"省编码"`
	City             string `description:"市"`
	CityCode         string `description:"市编码"`
	Theme            string `description:"会议主题"`
	CooperationName  string `description:"合作方名称"`
	Title            string `description:"展示在日历的标题"`
	Source           int8   `description:"来源，0：自系统，1：上海方的"`
	CreateTime       time.Time
	ModifyTime       time.Time
	ActivityCategory string `description:"活动类别"`
	IsSynced         int    `description:"是否与上海同步 0:未同步 1:已同步"`
	UnionCode        string `description:"公开会议联合编码"`
	EnglishCompany   int    `description:"是否为英文客户: 0-否; 1-是"`
}

type RsCalendarResearcher struct {
	RsCalendarResearcherId int    `orm:"column(rs_calendar_researcher_id);pk"`
	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:"研究员新增排序"`
	UnionCode              string    `description:"公开会议联合编码"`
}

func GetRsCalendarById(rsCalendarId int) (item *RsCalendar, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM rs_calendar WHERE rs_calendar_id=? `
	err = o.Raw(sql, rsCalendarId).QueryRow(&item)
	return
}

func GetRsCalendarResearcherById(rsCalendarResearcherId int) (item *RsCalendarResearcher, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM rs_calendar_researcher WHERE rs_calendar_researcher_id=? `
	err = o.Raw(sql, rsCalendarResearcherId).QueryRow(&item)
	return
}

// Update 更新路演用户信息
func (item *RsCalendarResearcher) Update(cols []string) (err error) {
	o := orm.NewOrm()
	_, err = o.Update(item, cols...)
	return
}

func GetRsCalendarResearcherListById(rsCalendarId int) (item []*RsCalendarResearcher, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM rs_calendar_researcher WHERE rs_calendar_id=? `
	_, err = o.Raw(sql, rsCalendarId).QueryRows(&item)
	return
}

func GetRsCalendarResearcherListByCalendarResearcherId(rsCalendarResearcherId int) (item []*RsCalendarResearcher, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM rs_calendar_researcher WHERE rs_calendar_researcher_id=? `
	_, err = o.Raw(sql, rsCalendarResearcherId).QueryRows(&item)
	return
}

// 添加RsCalendar
func AddRsCalendar(item *RsCalendar) (lastId int64, err error) {
	o := orm.NewOrm()
	lastId, err = o.Insert(item)
	return
}

// 添加RsCalendarResearcher
func AddRsCalendarResearcher(item *RsCalendarResearcher) (lastId int64, err error) {
	o := orm.NewOrm()
	lastId, err = o.Insert(item)
	return
}

// 添加RsCalendarResearcher
func AddRsCalendarResearcherFromSH(item *RsCalendarResearcher) (bool bool, id int64, err error) {
	o := orm.NewOrm()
	bool, id, err = o.ReadOrCreate(item, "rs_calendar_id")
	return
}

type Researcher struct {
	AdminId      int    `description:"研究员id"`
	RealName     string `description:"研究员名称"`
	GroupId      int    `description:"分组id"`
	GroupName    string `description:"分组名称"`
	RoleTypeCode string `description:"角色编码"`
}

type ResearcherIds struct {
	AdminIds string `description:"研究员id"`
}

type ResearcherGroup struct {
	GroupId        int    `description:"分组id"`
	GroupName      string `description:"分组名称"`
	ResearcherList []*Researcher
}

func GetResearcherGroup() (list []*ResearcherGroup, err error) {
	o := orm.NewOrm()
	sql := ` SELECT group_id,group_name FROM admin AS a
			WHERE a.role_type_code IN('researcher','ficc_researcher','ficc_admin')
			AND a.enabled=1
			AND a.group_id>0
			AND a.group_name<>'无'
			GROUP BY a.group_id 
            ORDER BY a.group_id ASC `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

func GetResearcher() (list []*Researcher, 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 a.real_name<>'于菲' `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

func GetChoiceResearcher(adminIds string) (list []*Researcher, err error) {
	o := orm.NewOrm()
	sql := ` SELECT *  FROM	admin  WHERE	admin_id IN ( ` + adminIds + `	); `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetResearcherV2 获取研究员列表（冻结的也要）
func GetResearcherV2() (list []*Researcher, 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
}

// GetSellerGroup 获取销售分组
func GetSellerGroup() (list []*ResearcherGroup, err error) {
	o := orm.NewOrm()
	sql := ` SELECT group_id,group_name FROM admin AS a
			WHERE a.role_type_code IN('ficc_seller','ficc_group')
			AND a.enabled=1
			AND a.group_id>0
			AND a.group_name<>'无'
			GROUP BY a.group_id 
            ORDER BY a.group_id ASC `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetSellerList 获取销售列表（冻结的也要）
func GetSellerList(roleTypeCode string, groupIds string) (list []*Researcher, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM admin AS a
			WHERE a.role_type_code IN ` + roleTypeCode + `
			AND a.enabled=1 `
	if groupIds != "" {
		sql += "and group_id in " + groupIds
	}
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetRaiSellerList 获取权益销售列表（冻结的也要）
func GetRaiSellerList(roleTypeCode string, groupIds string) (list []*Researcher, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM admin AS a
			WHERE a.role_type_code IN ` + roleTypeCode + `
			AND a.enabled=1 `
	if groupIds != "" {
		sql += "and group_id in " + groupIds
	}
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

type CalendarListView struct {
	RsCalendarId           int    `orm:"column(rs_calendar_id);pk"`
	SysUserId              int    `description:"创建人id"`
	SysUserRealName        string `description:"创建人名称"`
	ActivityType           string `description:"活动类型"`
	RoadshowType           string `description:"路演形式"`
	RoadshowPlatform       string `description:"路演平台"`
	CompanyId              int    `description:"客户id"`
	CompanyName            string `description:"客户名称"`
	RsCalendarResearcherId int    `description:"活动研究员id"`
	ResearcherId           string `description:"研究员id"`
	ResearcherName         string `description:"研究员名称"`
	StartDate              string `description:"开始日期"`
	EndDate                string `description:"结束日期"`
	StartTime              string `description:"开始时间"`
	EndTime                string `description:"结束时间"`
	StartWeek              string `description:"开始日期对应周"`
	EndWeek                string `description:"结束日期对应周"`
	CreateTime             string
	ModifyTime             string
	Status                 int    `description:"状态:1:待接受,2:已接受,3:已拒绝,4:已删除,5:已撤回,6:已结束"`
	RefuseReason           string `description:"拒绝理由"`
	RefuseTime             string `description:"拒绝时间"`
	DeleteReason           string `description:"删除原因"`
	Province               string `description:"省"`
	ProvinceCode           string `description:"省编码"`
	City                   string `description:"市"`
	CityCode               string `description:"市编码"`
	Theme                  string `description:"会议主题"`
	CooperationName        string `description:"合作方名称"`
	ActivityCategory       string `description:"活动类别"`
	Source                 int    `description:"来源，0：自系统，1：上海方的"`
	Title                  string `description:"日历展示标题"`
	CompanyStatus          string `description:"新增客户状态"`
	UnionCode              string `description:"公开会议联合编码"`
	EnglishCompany         int    `description:"是否为英文客户: 0-否; 1-是"`
	EnglishCountry         string `description:"英文客户-国家"`
	EnglishViewTotal       int    `description:"英文客户-累计点击量"`
}

type CalendarListResp struct {
	Paging *paging.PagingItem
	List   []*CalendarListView
}

func GetCalendarListCount(condition string, pars []interface{}, calendarType int) (count int, err error) {
	o := orm.NewOrm()
	if calendarType == 3 || calendarType == 4 {
		sql := `SELECT COUNT(1) AS count FROM(SELECT COUNT(1) AS count
				FROM  rs_calendar AS a
				INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
				WHERE 1=1  `
		if condition != "" {
			sql += condition
		}
		sql += ` GROUP BY a.rs_calendar_id ) AS t `
		err = o.Raw(sql, pars).QueryRow(&count)
	} else {
		sql := `SELECT COUNT(1) AS count FROM  rs_calendar AS a
		INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
		WHERE 1=1 `
		if condition != "" {
			sql += condition
		}
		err = o.Raw(sql, pars).QueryRow(&count)
	}
	return
}

func GetCalendarList(condition string, pars []interface{}, startSize, pageSize, calendarType int) (list []*CalendarListView, err error) {
	o := orm.NewOrm()
	if calendarType == 3 || calendarType == 4 {
		sql := ` SELECT a.rs_calendar_id,a.activity_type,a.roadshow_type,a.activity_category,a.roadshow_platform,b.create_time,
				b.modify_time,GROUP_CONCAT(b.researcher_id ORDER BY researcher_sort ASC) AS researcher_id,GROUP_CONCAT(b.researcher_name ORDER BY researcher_sort ASC) AS researcher_name,
				b.rs_calendar_researcher_id,b.start_date,
				b.end_date,b.start_time,b.end_time,b.start_week,b.end_week,b.status,b.refuse_reason,b.refuse_time,
                b.delete_reason,a.sys_user_real_name,a.city,a.province,a.company_name,a.company_id,
                a.cooperation_name,a.theme,a.activity_category,a.english_company
				FROM  rs_calendar AS a
				INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
				WHERE 1=1
 `
		if condition != "" {
			sql += condition
		}
		if calendarType == 1 {
			sql += ` GROUP BY a.rs_calendar_id
				 ORDER BY b.create_time ASC LIMIT ?,? `
		} else {
			sql += ` GROUP BY a.rs_calendar_id
				 ORDER BY b.create_time DESC LIMIT ?,? `
		}

		_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	} else {
		sql := `SELECT a.rs_calendar_id,a.activity_type,a.roadshow_type,a.activity_category,a.roadshow_platform,b.create_time,
				b.modify_time,b.researcher_id,b.researcher_name,
				b.rs_calendar_researcher_id,b.start_date,
				b.end_date,b.start_time,b.end_time,b.start_week,b.end_week,b.status,b.refuse_reason,b.refuse_time,
                b.delete_reason,a.sys_user_real_name,a.city,a.province,a.company_name,a.company_id,a.cooperation_name,a.theme,a.activity_category,a.english_company
        FROM  rs_calendar AS a
		INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
		WHERE 1=1 `
		if condition != "" {
			sql += condition
		}
		if calendarType == 1 {
			sql += ` ORDER BY b.create_time ASC LIMIT ?,? `
		} else {
			sql += ` ORDER BY b.create_time DESC LIMIT ?,? `
		}
		_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	}

	return
}

type AcceptReq struct {
	RsCalendarId           int `description:"日历活动id"`
	RsCalendarResearcherId int `description:"活动研究员id"`
}

type DeleteReq struct {
	RsCalendarId           int    `description:"日历活动id"`
	RsCalendarResearcherId int    `description:"活动研究员id"`
	DeleteReason           string `description:"删除原因"`
}

type RefuseReq struct {
	RsCalendarId           int    `description:"日历活动id"`
	RsCalendarResearcherId int    `description:"活动研究员id"`
	RefuseReason           string `description:"拒绝原因"`
}

// 更新
func UpdateCalendarResearcher(where, updateParams map[string]interface{}) error {
	o := orm.NewOrm()
	ptrStructOrTableName := "rs_calendar_researcher"
	qs := o.QueryTable(ptrStructOrTableName)
	for expr, exprV := range where {
		qs = qs.Filter(expr, exprV)
	}
	_, err := qs.Update(updateParams)
	return err
}

// 更新
func UpdateCalendarResearcherFromSH(item *RsCalendarResearcher) error {
	o := orm.NewOrm()
	_, err := o.Update(item, "")
	return err
}

type AddMattersReq struct {
	StartDate     string `description:"开始日期"`
	EndDate       string `description:"结束日期"`
	StartTime     string `description:"开始时间"`
	EndTime       string `description:"结束时间"`
	StartWeek     string `description:"开始日期周"`
	EndWeek       string `description:"结束日期周"`
	MatterContent string `description:"事项内容"`
}

type RsMatters struct {
	RsMattersId     int       `orm:"column(rs_matters_id);pk"`
	SysUserId       int       `description:"添加事项人id"`
	SysUserRealName string    `description:"创建人姓名"`
	StartDate       string    `description:"开始日期"`
	EndDate         string    `description:"结束日期"`
	StartTime       string    `description:"开始时间"`
	EndTime         string    `description:"结束时间"`
	StartWeek       string    `description:"开始日期周"`
	EndWeek         string    `description:"结束日期周"`
	MatterContent   string    `description:"事项内容"`
	Status          int8      `description:"状态:1:进行中,6:已结束"`
	CreateTime      time.Time `description:"创建时间"`
	ModifyTime      time.Time `description:"修改时间"`
	IsSynced        int       `description:"是否与上海同步 0:未同步 1:已同步"`
	EditReason      string    `description:"修改原因"`
	IsSeller        int       `description:"是否为销售事项: 0-否; 1-是"`
}

// 添加RsCalendarResearcher
func AddRsMatters(item *RsMatters) (id int64, err error) {
	o := orm.NewOrm()
	id, err = o.Insert(item)
	return
}

type UpdateMattersReq struct {
	RsMattersId   int    `orm:"column(rs_matters_id);pk"`
	StartDate     string `description:"开始日期"`
	EndDate       string `description:"结束日期"`
	StartTime     string `description:"开始时间"`
	EndTime       string `description:"结束时间"`
	StartWeek     string `description:"开始日期周"`
	EndWeek       string `description:"结束日期周"`
	MatterContent string `description:"事项内容"`
	EditReason    string `description:"修改原因"`
}

// 更新
func UpdateRsMatters(where, updateParams map[string]interface{}) error {
	o := orm.NewOrm()
	ptrStructOrTableName := "rs_matters"
	qs := o.QueryTable(ptrStructOrTableName)
	for expr, exprV := range where {
		qs = qs.Filter(expr, exprV)
	}
	_, err := qs.Update(updateParams)
	return err
}

type DeleteMattersReq struct {
	RsMattersId int `description:"事项id"`
}

// 删除事项
func DeleteRsMatters(rsMattersId int) (err error) {
	o := orm.NewOrm()
	sql := ` DELETE FROM rs_matters WHERE rs_matters_id=? `
	_, err = o.Raw(sql, rsMattersId).Exec()
	return err
}

type RsMattersView struct {
	RsMattersId     int            `orm:"column(rs_matters_id);pk"`
	SysUserId       int            `description:"添加事项人id"`
	SysUserRealName string         `description:"创建人姓名"`
	StartDate       string         `description:"开始日期"`
	EndDate         string         `description:"结束日期"`
	StartTime       string         `description:"开始时间"`
	EndTime         string         `description:"结束时间"`
	StartWeek       string         `description:"开始日期周"`
	EndWeek         string         `description:"结束日期周"`
	MatterContent   string         `description:"事项内容"`
	Status          int8           `description:"状态:1:进行中,6:已结束"`
	CreateTime      string         `description:"创建时间"`
	ModifyTime      string         `description:"修改时间"`
	IsSynced        int            `description:"是否与上海同步 0:未同步 1:已同步"`
	EditReason      string         `description:"修改原因"`
	ButtonAuth      RsMatterButton `description:"按钮权限"`
}

// RsMatterButton
type RsMatterButton struct {
	EditDisabled   bool `description:"禁用编辑"`
	RemoveDisabled bool `description:"禁用删除"`
}

func GetCalendarDetailList(condition string, pars []interface{}) (list []*CalendarListView, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.*,b.*,c.status AS company_status FROM  rs_calendar AS a
		INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
        LEFT JOIN company_product AS c ON a.company_id=c.company_id AND c.product_id=1
		WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY a.create_time DESC `
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

func GetRsMattersList(startDate, endDate string, researcherId int) (list []*RsMatters, err error) {
	o := orm.NewOrm()
	//sql := `SELECT * FROM  rs_matters AS a WHERE a.start_date>=? AND a.end_date<=? AND sys_user_id=? `
	sql := `SELECT * FROM  rs_matters AS a WHERE sys_user_id=? `
	sql += ` ORDER BY a.create_time DESC `
	//_, err = o.Raw(sql, startDate, endDate, researcherId).QueryRows(&list)
	_, err = o.Raw(sql, researcherId).QueryRows(&list)
	return
}

type CalendarDetailResp struct {
	CalendarList  []*CalendarListView
	RsMattersList []*RsMatters
}

type EditActivityReq struct {
	EditType               int    `description:"修改方式: 1:修改,2:修改重提"`
	RsCalendarId           int    `description:"路演活动id"`
	RsCalendarResearcherId int    `description:"活动研究员id"`
	ActivityType           string `description:"活动类型"`
	RoadshowType           string `description:"路演形式"`
	RoadshowPlatform       string `description:"路演平台"`
	CompanyId              int    `description:"客户id"`
	CompanyName            string `description:"客户名称"`
	Province               string `description:"省"`
	ProvinceCode           string `description:"省编码"`
	City                   string `description:"市"`
	CityCode               string `description:"市编码"`
	Theme                  string `description:"会议主题"`
	CooperationName        string `description:"合作方名称"`
	ActivityCategory       string `description:"活动类别"`
	ResearcherList         []*CalendarResearcher
	EnglishCompany         int `description:"是否为英文客户"`
}

// 更新活动信息
func UpdateRsCalendar(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
}

func GetMattersListCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS count FROM  rs_matters AS a
		WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

func GetMattersList(condition string, pars []interface{}, pageLimit ...int) (list []*RsMattersView, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM  rs_matters AS a
		WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY a.create_time DESC LIMIT ?,? `
	_, err = o.Raw(sql, pars, pageLimit).QueryRows(&list)
	return
}

type MattersListResp struct {
	Paging *paging.PagingItem
	List   []*RsMattersView
}

// 删除
func DeleteCalendar(rsCalendarId, rsCalendarResearcherId int) (err error) {
	o := orm.NewOrm()
	sql := ` DELETE FROM rs_calendar_researcher WHERE rs_calendar_id=? `
	_, err = o.Raw(sql, rsCalendarId).Exec()
	if err != nil {
		return err
	}
	//var count int
	//sql = ` SELECT COUNT(1) AS count FROM rs_calendar_researcher WHERE rs_calendar_id=? `
	//err = o.Raw(sql, rsCalendarId).QueryRow(&count)
	//if err != nil && err.Error() != utils.ErrNoRow() {
	//	return err
	//}
	//if count <= 0 {
	//	sql := ` DELETE FROM rs_calendar WHERE rs_calendar_id=? `
	//	_, err = o.Raw(sql, rsCalendarId).Exec()
	//	if err != nil {
	//		return err
	//	}
	//}
	sql = ` DELETE FROM rs_calendar WHERE rs_calendar_id=? `
	_, err = o.Raw(sql, rsCalendarId).Exec()
	return err
}

// 删除
func DeleteRsCalendarResearcher(rsCalendarId int) (err error) {
	o := orm.NewOrm()
	sql := ` DELETE FROM rs_calendar_researcher WHERE rs_calendar_id=? `
	_, err = o.Raw(sql, rsCalendarId).Exec()
	return err
}

func CheckMattersCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS count FROM  rs_matters AS a
		WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

func CheckCalendarResearcherCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS count FROM  rs_calendar_researcher AS a
		WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}
func GetResearcherFromAdmin(condition string, pars []interface{}) (lists []*system.Admin, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM admin WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	_, err = o.Raw(sql, pars).QueryRows(&lists)
	return
}

func GetCreditCodeFromCompany(companyId int) (creditCode string, err error) {
	o := orm.NewOrm()
	companyItem := company.Company{CompanyId: companyId}
	err = o.Read(&companyItem)
	if err != nil {
		return "", err
	}
	creditCode = companyItem.CreditCode
	return
}

type SHCalendar struct {
	CalendarId      int    `description:"日历id"`
	UserPhone       string `description:"创建人手机号"`
	ResearcherPhone string `description:"研究员手机号"`
	IndustryName    string `description:"行业名称"`
	CreditCode      string `description:"社会信用码"`
	content         string `description:"日历内容"`
	StartTime       string `description:"开始时间"`
	EndTime         string `description:"结束时间"`
}

func GetRsCalendarResearcherByRsCalendarIdAndResearcherId(rsCalendarId, researcherId int) (item *RsCalendarResearcher, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM rs_calendar_researcher WHERE rs_calendar_id=? AND researcher_id=? `
	err = o.Raw(sql, rsCalendarId, researcherId).QueryRow(&item)
	return
}

// GetRsCalendarResearcherListByRsCalendarId 根据路演id获取路演研究员列表
func GetRsCalendarResearcherListByRsCalendarId(rsCalendarId int) (items []*RsCalendarResearcher, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM rs_calendar_researcher WHERE rs_calendar_id=? `
	_, err = o.Raw(sql, rsCalendarId).QueryRows(&items)
	return
}

func GetMattersById(rsMatters int) (item *RsMatters, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM  rs_matters
		WHERE rs_matters_id=? `
	err = o.Raw(sql, rsMatters).QueryRow(&item)
	return
}

func GetRsCalendarResearcherExist(rsCalendarResearcherId, rsCalendarId, researcherId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS count FROM rs_calendar_researcher WHERE rs_calendar_researcher_id=? AND rs_calendar_id=? AND researcher_id=? `
	err = o.Raw(sql, rsCalendarResearcherId, rsCalendarId, researcherId).QueryRow(&count)
	return
}

func GetRsCalendarResearcherByCalendarId(rsCalendarId int) (item []*RsCalendarResearcher, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM rs_calendar_researcher WHERE rs_calendar_id=? `
	_, err = o.Raw(sql, rsCalendarId).QueryRows(&item)
	return
}

type PublicMeetingDetailResp struct {
	Group []*PublicMeetingGroup
}

type PublicMeetingGroup struct {
	UnionCode    string
	MinTime      string
	MaxTime      string
	CalendarList []*CalendarListView
}

func GetPublicCalendarDetailList(condition string, pars []interface{}) (list []*CalendarListView, err error) {
	o := orm.NewOrm()
	sql := `SELECT a.*,b.*,c.status AS company_status FROM  rs_calendar AS a
		INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
        LEFT JOIN company_product AS c ON a.company_id=c.company_id AND c.product_id=1
        INNER JOIN admin AS d ON b.researcher_id=d.admin_id
		WHERE 1=1 AND d.enabled=1 `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY a.create_time DESC `
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

type AdminInfo struct {
	AdminId  int
	RealName string
}

func GetOverseaCustomCalendarSellerList() (list []*AdminInfo, err error) {
	o := orm.NewOrm()
	sql1 := `SELECT a.sys_user_id FROM  rs_calendar AS a
    INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
        LEFT JOIN company_product AS c ON a.company_id=c.company_id AND c.product_id=1
        LEFT JOIN company AS d ON c.company_id=d.company_id 
		where d.region_type= "海外" and  a.english_company= 0 AND a.source = 0  AND b.status=2 AND a.sys_user_id != 0 `

	sql2 := `SELECT aa.sys_user_id FROM  rs_calendar AS aa
    INNER JOIN rs_calendar_researcher AS bb ON aa.rs_calendar_id=bb.rs_calendar_id
		where aa.english_company= 1 AND aa.source = 0  AND bb.status=2 AND aa.sys_user_id != 0`

	sql := `SELECT n.admin_id,n.real_name FROM (` + sql1 + ` UNION ALL ` + sql2
	sql += `		) AS m 
 JOIN admin n on m.sys_user_id=n.admin_id
WHERE 1=1 Group by sys_user_id order by sys_user_id asc`
	_, err = o.Raw(sql).QueryRows(&list)

	return
}

func GetOverseaCustomCalendarResearcherList() (list []*AdminInfo, err error) {
	o := orm.NewOrm()
	sql1 := `SELECT b.researcher_id FROM  rs_calendar AS a
    INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
        LEFT JOIN company_product AS c ON a.company_id=c.company_id AND c.product_id=1
        LEFT JOIN company AS d ON c.company_id=d.company_id 
		where d.region_type= "海外" and  a.english_company= 0 AND a.source = 0  AND b.status=2 AND b.researcher_id != 0`

	sql2 := `SELECT bb.researcher_id FROM  rs_calendar AS aa
    INNER JOIN rs_calendar_researcher AS bb ON aa.rs_calendar_id=bb.rs_calendar_id
		where aa.english_company= 1 AND aa.source = 0  AND bb.status=2 AND bb.researcher_id != 0 `

	sql := `SELECT n.admin_id,n.real_name FROM (` + sql1 + ` UNION ALL ` + sql2
	sql += `		) AS m 
JOIN admin n on m.researcher_id=n.admin_id
WHERE 1=1 Group by researcher_id order by researcher_id asc`
	_, err = o.Raw(sql).QueryRows(&list)

	return
}

type OverseaCustomRecordInfoResp struct {
	Paging *paging.PagingItem
	List   []*OverseaCustomRecordInfo
}

type OverseaCustomRecordInfo struct {
	RsCalendarId     int
	RoadshowType     string
	RoadshowPlatform string
	SellerName       string
	ResearcherName   string
	CompanyName      string
	CompanyStatus    string
	StartDate        string
}

func GetOverseaCustomCalendarList(sellerId, researcherId, startDate, endDate, companyStatus string, startSize, pageSize int) (total int, list []*OverseaCustomRecordInfo, err error) {
	var databaseName string
	if utils.RunMode == "debug" {
		databaseName = "test_v2_hongze_rddp"
	} else {
		databaseName = "hongze_rddp"
	}
	o := orm.NewOrm()

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

	sql1 := `SELECT d.company_name,c.status company_status,b.start_date,a.rs_calendar_id,a.roadshow_type,a.roadshow_platform,a.sys_user_real_name seller_name,b.researcher_name as base_researcher_name FROM  rs_calendar AS a
    INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
        LEFT JOIN company_product AS c ON a.company_id=c.company_id AND c.product_id=1
        LEFT JOIN company AS d ON c.company_id=d.company_id 
		where c.is_overseas = 0 and  a.english_company= 0 AND a.source = 0  AND b.status=2 AND b.researcher_id != 0`

	if sellerId != "" {
		sql1 += fmt.Sprintf(` AND a.sys_user_id in (%s) `, sellerId)
	}
	if startDate != "" {
		sql1 += ` AND b.start_date >= ? `
		pars = append(pars, startDate)
	}
	if endDate != "" {
		sql1 += ` AND b.start_date <= ? `
		pars = append(pars, endDate)
	}
	if companyStatus != "" {
		sql1 += ` AND c.status = ? `
		pars = append(pars, companyStatus)
	}

	sql2 := fmt.Sprintf(`SELECT cc.company_name,cc.overseas_status company_status,bb.start_date,aa.rs_calendar_id,aa.roadshow_type,aa.roadshow_platform,aa.sys_user_real_name seller_name,bb.researcher_name as base_researcher_name FROM rs_calendar AS aa
    INNER JOIN rs_calendar_researcher 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 bb.status=2 AND bb.researcher_id != 0 `, databaseName)

	if sellerId != "" {
		sql2 += fmt.Sprintf(` AND aa.sys_user_id in (%s) `, sellerId)
	}

	if startDate != "" {
		sql2 += ` AND bb.start_date >= ?  `
		pars = append(pars, startDate)
	}
	if endDate != "" {
		sql2 += ` AND bb.start_date <= ?  `
		pars = append(pars, endDate)
	}
	if companyStatus != "" {
		sql2 += ` AND cc.overseas_status = ? `
		pars = append(pars, companyStatus)
	}
	rsCalendarIdList := make([]int, 0)
	if researcherId != `` {
		rsCalendarIdSql := fmt.Sprintf("select rs_calendar_id from rs_calendar_researcher where researcher_id in (%s) group by rs_calendar_id", researcherId)
		_, err = o.Raw(rsCalendarIdSql).QueryRows(&rsCalendarIdList)
		if err != nil {
			return
		}
	}
	rsCalendarIdNum := len(rsCalendarIdList)

	// 汇总数据
	totalSql := `SELECT COUNT(1) FROM (SELECT rs_calendar_id FROM (` + sql1 + ` UNION ALL ` + sql2
	totalSql += ` ) AS m  WHERE 1=1 `

	if rsCalendarIdNum > 0 {
		totalSql += fmt.Sprintf(` AND rs_calendar_id IN (` + utils.GetOrmInReplace(rsCalendarIdNum) + `)`)
		pars = append(pars, rsCalendarIdList)
	}

	totalSql += ` Group by rs_calendar_id) fff`
	err = o.Raw(totalSql, pars).QueryRow(&total)
	if err != nil {
		return
	}

	// 列表数据
	sql := `SELECT m.*,GROUP_CONCAT(DISTINCT base_researcher_name ORDER BY rs_calendar_id ASC SEPARATOR '，') AS researcher_name  FROM (` + sql1 + ` UNION ALL ` + sql2
	sql += ` ) AS m  WHERE 1=1 `

	if rsCalendarIdNum > 0 {
		sql += fmt.Sprintf(` AND rs_calendar_id IN (` + utils.GetOrmInReplace(rsCalendarIdNum) + `)`)
	}

	sql += ` Group by rs_calendar_id order by start_date desc `
	sql += ` LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)

	return
}