hz_crm_api/models/cygx/activity_attendance_detail.go

package cygx

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

// 报名
type CygxActivityAttendanceDetail struct {
	AttendanceId int    `orm:"column(attendance_id);pk;"description:"主键ID"`
	ActivityId   int    `description:"活动ID"`
	RealName     string `description:"姓名"`
	Mobile       string `description:"手机号"`
	CompanyName  string `description:"公司名称"`
	CompanyId    int    `description:"公司id 不在数据库的用户为0"`
	SellerName   string `description:"销售姓名"`
	//IsMeetingStr          string `description:"是否到会"`
	FirstMeetingTime      string    `description:"首次入会时间"`
	LastMeetingTime       string    `description:"最后退出时间"`
	Duration              string    `description:"参会时长"`
	MeetingTypeStr        string    `description:"参会方式"`
	MeetingAuthentication string    `description:"参会权鉴"`
	MeetingStatusStr      string    `description:"参会状态"`
	CreateTime            time.Time `description:"创建时间"`
	Position              string    `description:"职位"`
	IsMeetingStr          int       `description:"是否到会，1到会，0未到会"`
	UseridEntity          int       `description:"参会者身份. 1:主讲人, 2:主持人, 3:嘉宾, 4:普通参会者, 5:联席主讲人, 6:会议助理"`
	CrmCompanyMapStatusId int       `description:"转换后的对应状态信息：1=正式客户, 2=曾使用客户, 3=其他"`
	ActivityTime          string    `description:"活动时间"`
}

// 报名
type AttendanceDetail struct {
	ActivityId            int    `description:"活动ID"`
	RealName              string `description:"姓名"`
	Mobile                string `description:"手机号"`
	CompanyName           string `description:"公司名称"`
	CompanyId             int    `description:"公司ID"`
	SellerName            string `orm:"column(pseller_name);"description:"销售姓名"`
	IsMeeting             int    `description:"是否到会"`
	FirstMeetingTime      string `description:"首次入会时间"`
	LastMeetingTime       string `description:"最后退出时间"`
	Duration              string `description:"参会时长"`
	MeetingTypeStr        string `description:"参会方式"`
	MeetingAuthentication string `description:"参会权鉴"`
	MeetingStatusStr      string `description:"参会状态"`
	Position              string `description:"职位"`
	OutboundMobile        string `description:"外呼手机号"`
	CountryCode           string `description:"手机国家区号"`
	UseridEntity          int    `description:"参会者身份. 1:主讲人, 2:主持人, 3:嘉宾, 4:普通参会者, 5:联席主讲人, 6:会议助理"`
	Content               string `description:"备注信息"`
	ActivityTimeText      string `description:"活动时间带文字"`
	ActivityName          string `description:"活动名称"`
}

func AddAttendancDetail(items []*CygxActivityAttendanceDetail, activityId int, mobileStr string) (err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			fmt.Println(err)
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()
	//修改活动是否上传到会信息字段
	sql := `UPDATE cygx_activity SET  is_submit_meeting=1   WHERE activity_id=? `
	_, err = to.Raw(sql, activityId).Exec()
	if err != nil {
		return
	}
	//修改单个报名信息是否到会
	sql = `UPDATE cygx_activity_signup SET  is_meeting=0   WHERE activity_id=? `
	_, err = to.Raw(sql, activityId).Exec()
	if err != nil {
		return
	}
	sql = `UPDATE cygx_activity_signup SET  is_meeting=1   WHERE  activity_id=? AND outbound_mobile IN (` + mobileStr + `)`
	_, err = to.Raw(sql, activityId).Exec()
	if err != nil {
		return
	}
	sql = `UPDATE cygx_activity_signup SET  is_meeting=1   WHERE  activity_id=? AND mobile IN (` + mobileStr + `)`
	_, err = to.Raw(sql, activityId).Exec()
	if err != nil {
		return
	}
	//二次上传时删除原有数据
	sql = ` DELETE FROM cygx_activity_attendance_detail WHERE activity_id = ?`
	_, err = to.Raw(sql, activityId).Exec()
	if err != nil {
		return
	}
	//插入提交信息
	//for _, v := range items {
	//	_, err = to.Insert(v)
	//	if err != nil {
	//		return
	//	}
	//}

	_, err = o.InsertMulti(len(items), items)
	if err != nil {
		return err
	}
	return
}

func GetAttendanceDetailList(activityId int, sqlStr string) (item []*AttendanceDetail, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT s.id,s.activity_id,p.company_name,s.mobile,s.is_meeting,s.outbound_mobile,s.country_code,u.real_name,d.first_meeting_time,d.last_meeting_time,d.meeting_type_str,d.meeting_authentication,d.meeting_status_str,d.duration,d.userid_entity,
			GROUP_CONCAT( DISTINCT p.seller_name SEPARATOR '/' ) AS pseller_name
			FROM
			cygx_activity_signup AS s
			INNER JOIN wx_user AS u ON u.user_id = s.user_id
			INNER JOIN cygx_activity AS a ON a.activity_id = s.activity_id
			INNER JOIN company_product AS p ON p.company_id = s.company_id 
			LEFT JOIN cygx_activity_attendance_detail AS d ON d.mobile = s.outbound_mobile  AND s.activity_id = d.activity_id
			WHERE 1 = 1 
			AND s.fail_type = 0 
			AND s.is_cancel = 0
			AND d.userid_entity != 3
			AND s.activity_id = ?` + sqlStr + `  GROUP BY s.id  ORDER BY s.is_meeting DESC`
	_, err = o.Raw(sql, activityId).QueryRows(&item)
	return
}

func GetAttendanceDetailListGroup(activityId int, sqlStr string) (item []*AttendanceDetail, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT s.*,d.first_meeting_time,d.last_meeting_time,d.meeting_type_str,d.meeting_authentication,d.meeting_status_str,d.duration,d.userid_entity,d.userid_entity
			FROM
			cygx_activity_signup AS s
			LEFT JOIN cygx_activity AS a ON a.activity_id = s.activity_id
			LEFT JOIN cygx_activity_attendance_detail AS d ON ( d.mobile = s.outbound_mobile   OR d.mobile = s.mobile )  AND s.activity_id = d.activity_id
			WHERE 1 = 1 
			AND s.do_fail_type = 0 
			AND s.is_cancel = 0
			AND s.activity_id = ?` + sqlStr + `  ORDER BY s.is_meeting DESC`
	_, err = o.Raw(sql, activityId).QueryRows(&item)
	return
}

func GetAttendanceDetailListGroupByUser(mobile, sqlStr string) (item []*AttendanceDetail, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT s.id,s.activity_id,s.company_name,s.mobile,s.is_meeting,s.outbound_mobile,s.country_code,s.real_name,s.first_meeting_time,s.last_meeting_time,s.meeting_type_str,s.meeting_authentication,s.meeting_status_str,s.duration,s.userid_entity,s.userid_entity,a.activity_name,a.activity_time_text
			FROM
			cygx_activity_signup_detail AS s
			INNER JOIN cygx_activity AS a ON a.activity_id = s.activity_id
			WHERE 1 = 1 
			AND s.do_fail_type = 0 
			AND s.is_cancel = 0
			AND s.mobile = ?` + sqlStr + `  ORDER BY a.activity_time ASC `
	_, err = o.Raw(sql, mobile).QueryRows(&item)
	return
}

func GetAttendanceDetailList2(activityId int, sqlStr string) (item []*AttendanceDetail, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT d.activity_id,d.real_name,d.company_id,d.first_meeting_time,d.last_meeting_time,d.meeting_type_str,d.meeting_authentication,d.meeting_status_str,d.mobile,d.duration,d.company_name,d.position,d.userid_entity
			FROM
			cygx_activity_attendance_detail AS d
			WHERE 1 = 1 AND d.activity_id = ? AND is_meeting_str = 1 AND d.userid_entity != 3  ` + sqlStr + ` GROUP BY d.mobile`
	_, err = o.Raw(sql, activityId).QueryRows(&item)
	return
}

func GetAttendanceDetailList3(activityId int, sqlStr string) (item []*AttendanceDetail, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT d.activity_id,d.real_name,d.company_id,d.first_meeting_time,d.last_meeting_time,d.meeting_type_str,d.meeting_authentication,d.meeting_status_str,d.mobile,d.duration,d.company_name,d.position,d.userid_entity
			FROM
			cygx_activity_attendance_detail AS d
			WHERE 1 = 1 AND d.activity_id = ?  AND d.userid_entity != 3 AND d.crm_company_map_status_id != 1 ` + sqlStr + ` GROUP BY d.mobile`
	_, err = o.Raw(sql, activityId).QueryRows(&item)
	return
}

type AttendanceDetailList struct {
	List1 []*AttendanceDetail `description:"预约客户"`
	List2 []*AttendanceDetail `description:"未预约客户"`
	List3 []*AttendanceDetail `description:"潜在客户"`
}

type WxUserOutboundMobile struct {
	RealName       string `description:"姓名"`
	Mobile         string `description:"手机号"`
	OutboundMobile string `description:"外呼手机号"`
	CompanyId      int    `description:"公司ID"`
	CompanyName    string `description:"公司名称"`
	SellerName     string `description:"所属销售"`
	UserId         int    `description:"用户ID"`
}

func GetWxUserOutboundMobile(mobileStr string) (item []*WxUserOutboundMobile, err error) {
	o := orm.NewOrm()
	sql := `SELECT u.real_name,u.mobile,u.outbound_mobile,u.company_id,p.company_name ,GROUP_CONCAT( DISTINCT p.seller_name SEPARATOR '/' ) AS seller_name
			FROM wx_user as u 
			INNER JOIN company_product AS p ON p.company_id = u.company_id 
			WHERE outbound_mobile IN (` + mobileStr + `) GROUP BY u.user_id`
	_, err = o.Raw(sql).QueryRows(&item)
	return
}

func GetWxUserMobile(mobileStr string) (item []*WxUserOutboundMobile, err error) {
	o := orm.NewOrm()
	sql := `SELECT u.real_name,u.mobile,u.outbound_mobile,u.company_id,p.company_name ,GROUP_CONCAT( DISTINCT p.seller_name SEPARATOR '/' ) AS seller_name
			FROM wx_user as u 
			INNER JOIN company_product AS p ON p.company_id = u.company_id 
			WHERE mobile IN (` + mobileStr + `) GROUP BY u.user_id`
	_, err = o.Raw(sql).QueryRows(&item)
	return
}

type RoadshowData struct {
	Id                    int       `orm:"column(id);pk;"description:"主键ID"`
	Company               string    `description:"公司名称"`
	UserName              string    `description:"用户名"`
	UserPhone             string    `description:"手机号"`
	UserinfoUource        string    `description:"用户来源"`
	AuthInfo              string    `description:"用户参会鉴权"`
	GuestUsername         string    `description:"嘉宾名称, 多个用,隔开"`
	FirstWatchTime        string    `description:"首次观看时间"`
	LastWatchTime         string    `description:"最后观看时间"`
	RoadshowId            string    `description:"路演ID"`
	RoadshowTitle         string    `description:"路演标题"`
	RoadshowBeginTime     time.Time `description:"路演开始时间"`
	RoadshowEndTime       time.Time `description:"路演结束时间"`
	JoinTime              int       `description:"参与时长"`
	JoinType              int       `description:"参与方式, 1网络, 2电话"`
	DataType              int       `description:"数据类型: 1直播, 2回放"`
	Occupation            string    `description:"职位"`
	UseridEntity          int       `description:"参会者身份. 1:主讲人, 2:主持人, 3:嘉宾, 4:普通参会者, 5:联席主讲人, 6:会议助理"`
	CrmCompanyMapStatusId int       `description:"转换后的对应状态信息：1=正式客户, 2=曾使用客户, 3=其他"`
}

type RoadshowDataTitle struct {
	RoadshowTitle string `description:"路演标题"`
	ActivityTime  string `description:"活动时间"`
}

type RoadshowDataTitleList struct {
	List []*RoadshowDataTitle
}

type RoadshowDataTitleResp struct {
	KeyWord    string `description:"关键词"`
	ActivityId int    `description:"活动ID"`
}

func GetRoadshowDataTitleList(startTime, endTime, title string) (list []*RoadshowDataTitle, err error) {
	o := orm.NewOrmUsingDB("comein_data")
	sql := `SELECT roadshow_title, roadshow_begin_time as activity_time  FROM roadshow_data WHERE  roadshow_begin_time > ? AND  roadshow_end_time < ?   AND roadshow_title LIKE '%` + title + `%'  GROUP BY  roadshow_id `
	_, err = o.Raw(sql, startTime, endTime).QueryRows(&list)
	return
}

func GetRoadshowDataList(title, condition string) (list []*RoadshowData, err error) {
	o := orm.NewOrmUsingDB("comein_data")
	sql := `SELECT * FROM roadshow_data WHERE roadshow_title LIKE '%` + title + `%'` + condition
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// 客户参会记录
type CompanyMeetRep struct {
	ActivityId            int    `description:"活动Id"`
	RealName              string `description:"姓名"`
	Mobile                string `description:"手机号"`
	Email                 string `description:"邮箱"`
	ActivityName          string `description:"活动名称"`
	ChartPermissionName   string `description:"行业名称"`
	ActivityTypeName      string `description:"活动类型名称"`
	Label                 string `description:"标签"`
	ActivityTime          string `description:"活动时间"`
	FirstMeetingTime      string `description:"首次入会时间"`
	LastMeetingTime       string `description:"最后退出时间"`
	Duration              string `description:"参会时长"`
	MeetingTypeStr        string `description:"参会方式"`
	MeetingAuthentication string `description:"参会权鉴"`
	MeetingStatusStr      string `description:"参会状态"`
}

func GetCygxActivityAttendanceDetailByCompanyId(mobiles string) (list []*CompanyMeetRep, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
			a.*,
			s.mobile,
			s.real_name,
			d.first_meeting_time,
			d.last_meeting_time,
			d.duration,
			d.meeting_type_str,
			d.meeting_authentication,
			d.meeting_status_str
			FROM
			cygx_activity_signup AS s
			INNER  JOIN cygx_activity AS a ON a.activity_id = s.activity_id
			INNER JOIN cygx_activity_attendance_detail AS d ON d.activity_id = s.activity_id 
			INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id
		WHERE
			1 = 1 
			AND s.do_fail_type = 0 
			AND s.is_cancel = 0 
			AND s.is_meeting = 1
			AND s.mobile IN (` + mobiles + `)
			AND a.is_submit_meeting = 1
			AND t.activity_type = 1
		GROUP BY
			s.id 
		ORDER BY
			a.activity_time DESC `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

func GetCygxActivityOfflineMeetingDetailByCompanyId(mobiles string) (list []*CompanyMeetRep, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
			a.*,s.mobile,s.email,s.real_name
		FROM
		    cygx_activity_signup AS s
			INNER  JOIN cygx_activity AS a ON a.activity_id = s.activity_id
			INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id
		WHERE
			1 = 1 
			AND s.do_fail_type = 0 
			AND s.is_cancel = 0 
			AND s.is_meeting = 1
			AND s.mobile IN (` + mobiles + `)
			AND a.is_submit_meeting = 1
			AND t.activity_type = 0
		GROUP BY
			s.id
		ORDER BY
			a.activity_time DESC `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

func GetCompanyActivityCount(companyId int) (list []*CompanyMeetRep, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
			a.chart_permission_name,
			date_format( a.activity_time, '%Y-%m' ) AS activity_time
		FROM
			cygx_activity_attendance_detail AS r
			INNER JOIN cygx_activity AS a ON a.activity_id = r.activity_id 
		WHERE
			r.company_id = ? 
			AND is_meeting_str = 1 
		ORDER BY
			a.activity_time DESC`
	_, err = o.Raw(sql, companyId).QueryRows(&list)
	return
}

func GetCygxActivityOfflineMeetingCoount(companyId int) (list []*CompanyMeetRep, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
			a.chart_permission_name,
			date_format( a.activity_time, '%Y-%m' ) AS activity_time
		FROM
			cygx_activity_offline_meeting_detail  as r 
			INNER JOIN cygx_activity as a On a.activity_id = r.activity_id
		WHERE
			r.company_id =?  AND r.is_meeting = 1 ORDER BY a.activity_time DESC`
	_, err = o.Raw(sql, companyId).QueryRows(&list)
	return
}

// 列表
func GetCygxActivityAttendanceDetailList(condition string, pars []interface{}, startSize, pageSize int) (items []*CygxActivityAttendanceDetail, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT * FROM cygx_activity_attendance_detail as a WHERE 1= 1 `
	if condition != "" {
		sql += condition
	}
	sql += ` LIMIT ?,?  `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}