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
}