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 }