hz_crm_api/models/cygx/activity_appointment.go

package cygx

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

type CygxActivityAppointment struct {
	Id          int       `orm:"column(id);pk"`
	ActivityId  int       `description:"活动ID"`
	UserId      int       `description:"用户ID"`
	AdminId     int       `description:"管理员ID"`
	CreateTime  time.Time `description:"创建时间"`
	Mobile      string    `description:"手机号"`
	Email       string    `description:"邮箱"`
	CompanyId   int       `description:"公司id"`
	CompanyName string    `description:"公司名称"`
	RealName    string    `description:"用户实际名称"`
	SellerName  string    `description:"所属销售"`
	Source      int       `description:"来源，1小程序，2后台添加"`
}

// 取消纪要预约
func CancelcygxActivityAppointment(item *CygxActivityAppointment) (lastId int64, err error) {
	o, err := orm.NewOrm().Begin()
	if err != nil {
		return
	}
	defer func() {
		fmt.Println(err)
		if err == nil {
			o.Commit()
		} else {
			o.Rollback()
		}
	}()
	//判断是否删除我的日程
	var countSingup int
	var countReminder int
	sql := `SELECT COUNT(1) AS count FROM cygx_activity_signup WHERE  is_cancel = 0  AND user_id=? AND activity_id=? `
	err = o.Raw(sql, item.UserId, item.ActivityId).QueryRow(&countSingup)
	sql = `SELECT COUNT(1) AS count FROM cygx_activity_meeting_reminder WHERE   user_id=? AND activity_id=? `
	err = o.Raw(sql, item.UserId, item.ActivityId).QueryRow(&countReminder)
	if err != nil {
		return
	}
	if countSingup == 0 && countReminder == 0 {
		sql = `DELETE  FROM cygx_my_schedule   WHERE user_id=?  AND activity_id=? `
		_, err = o.Raw(sql, item.UserId, item.ActivityId).Exec()
		if err != nil {
			return
		}
	}
	//删除预约的纪要
	sql = `DELETE  FROM cygx_activity_appointment   WHERE user_id=?  AND activity_id=? `
	_, err = o.Raw(sql, item.UserId, item.ActivityId).Exec()
	if err != nil {
		return
	}
	return
}

// 预约纪要的人数列表
func GetCygxAppointmentSummaryList(activityId int, sqlStr string) (item []*CygxAppointmentList, err error) {
	o := orm.NewOrm()
	sql := `SELECT s.id,s.activity_id,s.user_id,s.create_time,s.mobile,s.company_id ,u.real_name,c.company_name,a.activity_time,
			(SELECT p.seller_name from company_product as p WHERE p.company_id = u.company_id  AND  p.product_id = 2 )  AS seller_name,
			GROUP_CONCAT( DISTINCT p.seller_name SEPARATOR '/' ) AS pseller_name
			FROM
			cygx_activity_appointment AS s
			INNER JOIN wx_user AS u ON  u.user_id = s.user_id
			LEFT JOIN cygx_activity AS a ON a.activity_id = s.activity_id
			LEFT JOIN company_product AS p ON p.company_id = u.company_id 
			LEFT JOIN company AS c ON c.company_id = u.company_id 
			WHERE a.activity_id = ?  ` + sqlStr + `  GROUP BY s.user_id   ORDER BY s.create_time DESC `
	_, err = o.Raw(sql, activityId).QueryRows(&item)
	return
}

// 预约纪要的人数数量
func GetCygxAppointmentSummaryCount(activityId int, sqlStr string) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(*) FROM
			cygx_activity_appointment AS s
			INNER  JOIN wx_user AS u ON  u.user_id = s.user_id
			LEFT JOIN cygx_activity AS a ON a.activity_id = s.activity_id
			LEFT JOIN company_product AS p ON p.company_id = u.company_id 
			WHERE
			s.activity_id = ? ` + sqlStr + `
            GROUP BY s.user_id)`
	sql = `SELECT COUNT(*)  as count FROM (` + sql + ` a`
	err = o.Raw(sql, activityId).QueryRow(&count)
	return
}

// 通过ID获取预约纪要详情
func GetCygxAppointmentSummaryInfoById(id int) (item *CygxActivityAppointment, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_activity_appointment  WHERE id=?`
	err = o.Raw(sql, id).QueryRow(&item)
	return
}

// 获取某一用户的报名的数量
func GetUserCygxActivityAppointmentCount(uid, activityId int) (count int, err error) {
	sqlCount := `SELECT COUNT(1) AS count FROM cygx_activity_appointment  WHERE  user_id=?  AND   activity_id =? `
	o := orm.NewOrm()
	err = o.Raw(sqlCount, uid, activityId).QueryRow(&count)
	return
}

// 添加预约纪要
func AddCygxActivityAppointmentUser(items []*CygxActivityAppointment) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			fmt.Println(err)
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()
	for _, item := range items {
		var count int
		sql := `SELECT COUNT(1) AS count FROM cygx_my_schedule WHERE user_id=? AND activity_id=? `
		err = to.Raw(sql, item.UserId, item.ActivityId).QueryRow(&count)
		if err != nil {
			return
		}
		if count == 0 {
			itemMy := new(CygxMySchedule)
			itemMy.UserId = item.UserId
			itemMy.ActivityId = item.ActivityId
			itemMy.CreateTime = time.Now()
			itemMy.Mobile = item.Mobile
			itemMy.Email = item.Email
			itemMy.CompanyId = item.CompanyId
			itemMy.CompanyName = item.CompanyName
			_, err = to.Insert(itemMy)
			if err != nil {
				return
			}
		}
		sql = `SELECT COUNT(1) AS count FROM cygx_activity_appointment WHERE user_id=? AND activity_id=? `
		err = to.Raw(sql, item.UserId, item.ActivityId).QueryRow(&count)
		if err != nil {
			return
		}
		if count == 0 {
			_, err = to.Insert(item)
		}
	}
	return
}

type CygxAppointmentMobileList struct {
	ActivityId   int    `description:"活动ID"`
	Mobile       string `description:"手机号"`
	ActivityName string `description:"活动名称"`
}

// 通过活动ID获取预约纪要的人数列表
func GetCygxAppointmentSummaryListBySubjectId(subjectIds string) (item []*CygxAppointmentMobileList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			ap.mobile,
			ap.activity_id,
			a.activity_name 
		FROM
			cygx_activity_appointment AS ap
			INNER JOIN cygx_activity AS a ON a.activity_id = ap.activity_id
			INNER JOIN cygx_industrial_activity_group_subject AS sg ON sg.activity_id = a.activity_id 
		WHERE
			a.active_state = 3 
			AND a.chart_permission_id = 31 
			AND DATE_SUB( CURDATE(), INTERVAL 14 DAY ) <= date( a.activity_time ) 
			AND a.activity_type_id = 1 
			AND sg.industrial_subject_id IN (` + subjectIds + `) 
		GROUP BY
			ap.mobile,
			ap.activity_id `
	_, err = o.Raw(sql).QueryRows(&item)
	return
}

// 获取预约活动纪要的的用户的openID
func GetActivityAppointmentOpenIdList(activityId int) (items []*OpenIdList, err error) {
	var sql string
	if utils.RunMode == "release" {
		sql = `SELECT DISTINCT cr.open_id,u.user_id
			FROM
			cygx_activity_appointment AS m
			INNER JOIN user_record AS u ON u.bind_account = m.mobile 
			INNER JOIN cygx_user_record AS cr ON cr.union_id = u.union_id 
			WHERE m.activity_id = ? AND u.create_platform = 4 `
	} else {
		sql = `SELECT
			u.open_id,
			u.user_id 
		FROM
			cygx_activity_appointment AS s
			INNER JOIN wx_user AS wx ON wx.user_id = s.user_id
			INNER JOIN user_record AS u ON u.bind_account = wx.mobile
			INNER JOIN company_product AS p ON p.company_id = wx.company_id 
		WHERE
			s.activity_id = ? 
			AND u.create_platform = 1 
			AND p.STATUS IN ( '正式', '试用', '永续' ) 
		GROUP BY
			u.open_id`
	}
	_, err = orm.NewOrm().Raw(sql, activityId).QueryRows(&items)
	return
}