services/models/verify/verify.go

package verify

import (
	"errors"
	"spaceofcheng/services/models"
	"spaceofcheng/services/models/model"
	"spaceofcheng/services/utils"
	"strconv"
	"strings"

	"github.com/go-xorm/xorm"
)

// VerifyDAO 当前数据库操作对象
type VerifyDAO struct {
	ctx *utils.Context
	db  *xorm.Session
}

// NewVerifyDAO New Inst
func NewVerifyDAO(ctx *utils.Context) *VerifyDAO {
	return &VerifyDAO{
		ctx: ctx,
		db:  ctx.DB,
	}
}

type MyCourse struct {
	model.TaCustomerCourseDetail `xorm:"extends"`
	CaseName                     string
	DetailName                   string
	LocationName                 string
	CustomerId                   string
}

func (m *VerifyDAO) GetCustomerCourseDetailById(id string) (*model.TaCustomerCourseDetail, error) {
	var customerCourseDerail []model.TaCustomerCourseDetail
	err := m.db.Where("customer_detail_id =?", id).Find(&customerCourseDerail)
	if err != nil {
		return nil, err
	}
	if len(customerCourseDerail) > 0 {
		return &customerCourseDerail[0], nil
	}
	return nil, nil
}

// GetCustomerCourseList 根据我的课程id获取我的课程详情
func (m *VerifyDAO) GetCustomerCourseList(customerCourseId, caseids string, page int, pageSize int) ([]MyCourse, error) {
	var courseList []MyCourse
	sql := `SELECT
	a.*,
	d.case_name,
	c.detail_name,
	f.location_name,
	b.customer_id
	FROM
	ta_customer_course_detail a
	INNER JOIN ta_customer_course b ON a.customer_course_id = b.customer_course_id
	INNER JOIN ta_course_detail c ON c.detail_id = a.detail_id
	INNER JOIN sys_case d ON d.case_id = a.case_id
	LEFT JOIN td_cms_image_location f on b.location_id=f.location_id
	WHERE DATE_FORMAT(a.start_date,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')
	AND a.customer_course_id = '` + customerCourseId + `'
	and b.case_id in ('` + strings.Replace(caseids, ",", "','", -1) + `')`
	sql = sql + " limit " + strconv.Itoa((page-1)*pageSize) + `, ` + strconv.Itoa(pageSize)
	err := m.db.Sql(sql).Find(&courseList)
	return courseList, err
}

// GetCustomerCourseListCount 根据我的课程id获取我的课程详情数量
func (m *VerifyDAO) GetCustomerCourseListCount(customerCourseId, caseids string) (int, error) {
	var courseList []MyCourse
	sql := `SELECT
	a.*,
	d.case_name,
	c.detail_name
	FROM
	ta_customer_course_detail a
	INNER JOIN ta_customer_course b ON a.customer_course_id = b.customer_course_id
	INNER JOIN ta_course_detail c ON c.detail_id = a.detail_id
	INNER JOIN sys_case d ON d.case_id = a.case_id
	WHERE DATE_FORMAT(a.start_date,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')
	AND a.customer_course_id = '` + customerCourseId + `'
	and b.case_id in ('` + strings.Replace(caseids, ",", "','", -1) + `')`
	err := m.db.Sql(sql).Find(&courseList)
	return len(courseList), err
}

// UpdateCustomerCourseDetail 更新我的课程详情
func (m *VerifyDAO) UpdateCustomerCourseDetail(customerCourse *model.TaCustomerCourseDetail) error {
	var cols = []string{
		"verify_status",
		"verify_date",
		"verify_user",
	}
	_, err := m.db.Cols(cols...).Where("customer_detail_id=?", customerCourse.CustomerDetailId).Update(customerCourse)
	return err
}

// GetCustomerCourseListByTel 根据客户电话获取我的课程详情
func (m *VerifyDAO) GetCustomerCourseListByTel(tel, caseids string, page, pageSize int) ([]MyCourse, error) {
	var courseList []MyCourse
	sql := `SELECT
	a.*,
	d.case_name,
	c.detail_name,
	f.location_name,
	b.customer_id
	FROM
	ta_customer_course_detail a
	INNER JOIN ta_customer_course b ON a.customer_course_id = b.customer_course_id
	INNER JOIN ta_course_detail c ON c.detail_id = a.detail_id
	INNER JOIN sys_case d ON d.case_id = a.case_id
	INNER JOIN ta_customer e ON b.customer_id = e.customer_id
	LEFT JOIN td_cms_image_location f on b.location_id=f.location_id
	WHERE DATE_FORMAT(a.start_date,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')
	AND e.phone = '` + tel + `'
	and b.case_id in ('` + strings.Replace(caseids, ",", "','", -1) + `')`
	sql = sql + " limit " + strconv.Itoa((page-1)*pageSize) + `, ` + strconv.Itoa(pageSize)

	err := m.db.Sql(sql).Find(&courseList)
	return courseList, err
}

// GetCustomerCourseListByTelCount 根据客户电话获取我的课程详情数量
func (m *VerifyDAO) GetCustomerCourseListByTelCount(tel, caseids string) (int, error) {
	var courseList []MyCourse
	sql := `SELECT
	a.*,
	d.case_name,
	c.detail_name
FROM
	ta_customer_course_detail a
	INNER JOIN ta_customer_course b ON a.customer_course_id = b.customer_course_id
	INNER JOIN ta_course_detail c ON c.detail_id = a.detail_id
	INNER JOIN sys_case d ON d.case_id = a.case_id
	INNER JOIN ta_customer e ON b.customer_id = e.customer_id
	WHERE DATE_FORMAT(a.start_date,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')
	AND e.phone = '` + tel + `'
	and b.case_id in ('` + strings.Replace(caseids, ",", "','", -1) + `')`
	err := m.db.Sql(sql).Find(&courseList)
	return len(courseList), err
}

// UpdateCustomerCard 更新客户体验卡
func (m *VerifyDAO) UpdateCustomerCard(customerCard *model.TaCustomerCard) error {
	var cols = []string{
		"verify_date",
		"verify_status",
		"status",
	}
	_, err := m.db.Cols(cols...).Where("customer_card_id=?", customerCard.CustomerCardId).Update(customerCard)
	return err
}

// UpdateCustomerCourse 更新我的课程
func (m *VerifyDAO) UpdateCustomerCourse(customerCourse *model.TaCustomerCourse) error {
	var cols = []string{
		"join_num",
		"is_done",
	}
	_, err := m.db.Cols(cols...).Where("customer_course_id=?", customerCourse.CustomerCourseId).Update(customerCourse)
	return err
}

// GetCustomerCardByDetailId 根据我的课程详情ID查询用户体验卡
func (m *VerifyDAO) GetCustomerCardByDetailId(detailId string) (*model.TaCustomerCard, error) {
	var customerCard []model.TaCustomerCard
	sql := `SELECT
	a.* 
FROM
	ta_customer_card a
	INNER JOIN ta_customer_course b ON a.customer_card_id = b.source_id 
	
	INNER JOIN ta_customer_course_detail c ON c.customer_course_id = b.customer_course_id
WHERE  b.course_obtaim_type = 'card'
and  c.customer_detail_id = '` + detailId + `'`
	err := m.db.Sql(sql).Find(&customerCard)
	return &customerCard[0], err

}

// GetCustomerCourseByDetailId 根据我的课程详情ID查询我的课程
func (m *VerifyDAO) GetCustomerCourseByDetailId(detailId string) (*model.TaCustomerCourse, error) {
	var customerCard []model.TaCustomerCourse
	sql := `SELECT
	a.* 
FROM
	ta_customer_course a
	INNER JOIN ta_customer_course_detail b ON a.customer_course_id = b.customer_course_id 
WHERE
	b.customer_detail_id = '` + detailId + `'`
	err := m.db.Sql(sql).Find(&customerCard)
	return &customerCard[0], err
}

// GetCustomerCourseIdByQrcode 根据QRcode
func (m *VerifyDAO) GetCustomerCourseIdByQrcode(qrcode string) (string, error) {
	var customerQrcode []model.TaCustomerCourseQrcode
	err := m.db.Where("customer_qrcode = ?", qrcode).And("status > ?", models.STATUS_DEL).Find(&customerQrcode)
	if err != nil {
		return "", err
	}
	if len(customerQrcode) > 0 {
		return customerQrcode[0].CustomerCourseId, nil
	}
	return "", errors.New("无效二维码")

}