Golang lessgo.GetMySQL函數代碼示例

func getScheduleChildByChildIdAndScheduleId(childId, scheduleId string) (map[string]string, error) {

	db := lessgo.GetMySQL()
	defer db.Close()

	sql := "select id,schedule_detail_id,child_id,create_user,wyclass_id,contract_id,is_free from schedule_detail_child where child_id=? and schedule_detail_id=? "
	lessgo.Log.Debug(sql)

	rows, err := db.Query(sql, childId, scheduleId)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return nil, err
	}

	var dataMap map[string]string

	if rows.Next() {
		dataMap, err = lessgo.GetDataMap(rows)
		if err != nil {
			lessgo.Log.Error(err.Error())
			return nil, err
		}
	}

	return dataMap, nil
}

func getFurtherScheduleIds(timeId, roomId, week, startTime, courseId string) ([]string, error) {

	sql := `
			select id from class_schedule_detail where time_id=? and room_id=? and week=? and start_time>=? and course_id=?`

	lessgo.Log.Debug(sql)

	db := lessgo.GetMySQL()
	defer db.Close()

	rows, err := db.Query(sql, timeId, roomId, week, startTime, courseId)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return nil, err
	}

	ids := []string{}

	for rows.Next() {
		id := ""
		err := commonlib.PutRecord(rows, &id)

		if err != nil {
			lessgo.Log.Error(err.Error())
			return nil, err
		}

		ids = append(ids, id)
	}

	return ids, nil
}

/*
select csd.id,class_id classId,teacher_id teacherId,assistant_id assistantId,course_id courseId,center_id centerId,time_id timeId,room_id roomId,day_date dayDate,week,capacity,start_time startTime,end_time endTime,status,cour.name courseName,r.name roomName
			from class_schedule_detail csd
			left join course cour on cour.cid=csd.course_id
			left join room r on r.rid = csd.room_id
			where csd.id=?
*/
func getScheduleDetailId(id string) (map[string]string, error) {

	sql := `
			select csd.id,csd.class_id classId,csd.teacher_id teacherId,csd.assistant_id assistantId,csd.course_id courseId,csd.center_id centerId,csd.time_id timeId,csd.room_id roomId,csd.day_date dayDate,csd.week,csd.capacity,csd.start_time startTime,csd.end_time endTime,csd.status,cour.name courseName,r.name roomName
			from class_schedule_detail csd
			left join course cour on cour.cid=csd.course_id
			left join room r on r.rid = csd.room_id
			where csd.id=?
	    	`

	lessgo.Log.Debug(sql)

	db := lessgo.GetMySQL()
	defer db.Close()

	rows, err := db.Query(sql, id)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return nil, err
	}

	var dataMap map[string]string

	if rows.Next() {
		dataMap, err = lessgo.GetDataMap(rows)
	}

	if err != nil {
		return nil, err
	}

	return dataMap, nil
}

func FindTimeSectionById(id string) (TimeSection, error) {

	db := lessgo.GetMySQL()
	defer db.Close()

	sql := "select id,center_id,start_time,end_time,lesson_no from time_section where id=? "

	lessgo.Log.Debug(sql)

	rows, err := db.Query(sql, id)

	if err != nil {
		lessgo.Log.Warn(err.Error())
		return TimeSection{}, err
	}

	timeSection := TimeSection{}

	if rows.Next() {
		err = commonlib.PutRecord(rows, &timeSection.Id, &timeSection.CenterId, &timeSection.StartTime, &timeSection.EndTime, &timeSection.LessonNo)

		if err != nil {
			lessgo.Log.Warn(err.Error())
			return TimeSection{}, err
		}
	}

	return timeSection, nil
}

func FindChildById(id string) (Child, error) {
	db := lessgo.GetMySQL()
	defer db.Close()

	sql := "select cid,name,card_id,pid,sex,birthday,hobby,center_id,avatar from child where cid=?"

	lessgo.Log.Debug(sql)

	rows, err := db.Query(sql, id)

	if err != nil {
		lessgo.Log.Warn(err.Error())
		return Child{}, err
	}

	child := Child{}

	if rows.Next() {
		err = commonlib.PutRecord(rows, &child.Cid, &child.Name, &child.CardId, &child.Pid, &child.Sex, &child.Birthday, &child.Hobby, &child.CenterId, &child.Avatar)

		if err != nil {
			lessgo.Log.Warn(err.Error())
			return Child{}, err
		}
	}

	return child, nil
}

//根據id獲取員工信息
func FindEmployeeById(id int) (Employee, error) {

	var employee Employee

	db := lessgo.GetMySQL()
	defer db.Close()

	sql := "select user_id,username,really_name,department_id,center_id from employee where user_id=?"

	rows, err := db.Query(sql, id)
	if err != nil {
		lessgo.Log.Error(err.Error())
		return employee, err
	}

	if rows.Next() {
		err := commonlib.PutRecord(rows, &employee.UserId, &employee.UserName, &employee.ReallyName, &employee.DepartmentId, &employee.CenterId)
		if err != nil {
			lessgo.Log.Error(err.Error())
			return employee, err
		}
	}

	return employee, nil
}

func InsertCourse(centerId, courseName, courseType string) (flag bool, msg string, err error) {

	db := lessgo.GetMySQL()
	defer db.Close()

	tx, err := db.Begin()

	if err != nil {
		lessgo.Log.Error(err.Error())
		return false, "", err
	}

	courseNameFlag, err := checkCourseNameExist(centerId, courseName)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return false, "", err
	}

	if !courseNameFlag {
		_, err = insertCourse(tx, centerId, courseName, courseType)
		if err != nil {
			lessgo.Log.Error(err.Error())
			return false, "", err
		}
	} else {
		return false, "課程名字已存在", nil
	}

	tx.Commit()

	return true, "", nil
}

func checkScheduleTempChildExist(childId, scheduleTempId string) (flag bool, err error) {

	db := lessgo.GetMySQL()
	defer db.Close()

	sql := " select count(1) from schedule_template_child where child_id=? and  schedule_template_id=? "

	lessgo.Log.Debug(sql)

	rows, err := db.Query(sql, childId, scheduleTempId)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return false, err
	}

	num := 0

	if rows.Next() {

		err = commonlib.PutRecord(rows, &num)

		if err != nil {
			lessgo.Log.Error(err.Error())
			return false, err
		}
	}

	if num > 0 {
		return true, nil
	}

	return false, nil
}

/*
select st.id,st.room_id,st.time_id,st.week,csd.start_time,csd.course_id
			from class_schedule_detail csd
			left join schedule_template st on csd.center_id=st.center_id and csd.room_id=st.room_id and csd.time_id=st.time_id and csd.week=st.week
			where csd.id=?
*/
func getScheduleTmpByScheduelDetailId(scheduleId string) (map[string]string, error) {

	sql := `
			select st.id,st.room_id,st.time_id,st.week,csd.start_time,csd.course_id
			from class_schedule_detail csd
			left join schedule_template st on csd.center_id=st.center_id and csd.room_id=st.room_id and csd.time_id=st.time_id and csd.week=st.week
			where csd.id=?`

	lessgo.Log.Debug(sql)

	db := lessgo.GetMySQL()
	defer db.Close()

	rows, err := db.Query(sql, scheduleId)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return nil, err
	}

	var dataMap map[string]string

	if rows.Next() {
		dataMap, err = lessgo.GetDataMap(rows)
	}

	if err != nil {
		return nil, err
	}

	return dataMap, nil
}

func FindLessonById(id string) (Lesson, error) {

	db := lessgo.GetMySQL()
	defer db.Close()

	sql := "select lid,course_id,caption,teacher_id,keystone,lesson_time,order_no,is_last from lesson where lid=? "

	lessgo.Log.Debug(sql)

	rows, err := db.Query(sql, id)

	if err != nil {
		lessgo.Log.Warn(err.Error())
		return Lesson{}, err
	}

	lesson := Lesson{}

	if rows.Next() {
		err = commonlib.PutRecord(rows, &lesson.Lid, &lesson.CourseId, &lesson.Caption, &lesson.TeacherId, &lesson.KeyStone, &lesson.LessonTime, &lesson.OrderNo, &lesson.IsLast)

		if err != nil {
			lessgo.Log.Warn(err.Error())
			return Lesson{}, err
		}
	}

	return lesson, nil
}

func getTimeSectionById(id string) (map[string]string, error) {

	sql := `select id,center_id,start_time,end_time,lesson_no from time_section where id=?`

	lessgo.Log.Debug(sql)

	db := lessgo.GetMySQL()
	defer db.Close()

	rows, err := db.Query(sql, id)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return nil, err
	}

	var dataMap map[string]string

	if rows.Next() {
		dataMap, err = lessgo.GetDataMap(rows)
	}

	if err != nil {
		return nil, err
	}

	return dataMap, nil
}

func FindClassById(id string) (WyClass, error) {
	db := lessgo.GetMySQL()
	defer db.Close()

	sql := "select wc.class_id,wc.assistant_id,wc.name,wc.course_id,wc.center_id,wc.child_num,wc.end_time,wc.deadline,wc.max_child_num,wc.teacher_id,c.is_probation from wyclass wc left join course c on wc.course_id=c.cid where wc.class_id=?"

	lessgo.Log.Debug(sql)

	rows, err := db.Query(sql, id)

	if err != nil {
		lessgo.Log.Warn(err.Error())
		return WyClass{}, err
	}

	class := WyClass{}

	if rows.Next() {
		err = commonlib.PutRecord(rows, &class.ClassId, &class.AssistantId, &class.Name, &class.CourseId, &class.CenterId, &class.ChildNum, &class.EndTime, &class.DeadLine, &class.MaxChildNum, &class.TeacherId, &class.IsProbation)

		if err != nil {
			lessgo.Log.Warn(err.Error())
			return WyClass{}, err
		}
	}

	return class, nil
}

/*
select class_id id,name,start_time,end_time,code,center_id,child_num from wyclass where class_id=?
*/
func getWyClassById(id string) (map[string]string, error) {

	sql := `
			select class_id id,name,start_time,end_time,code,center_id,child_num from wyclass where class_id=?
	    	`

	lessgo.Log.Debug(sql)

	db := lessgo.GetMySQL()
	defer db.Close()

	rows, err := db.Query(sql, id)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return nil, err
	}

	var dataMap map[string]string

	if rows.Next() {
		dataMap, err = lessgo.GetDataMap(rows)
	}

	if err != nil {
		return nil, err
	}

	return dataMap, nil
}

func getNewestFreeScheduleIdByChildId(childId string) (scheduleId, classId string, err error) {

	db := lessgo.GetMySQL()
	defer db.Close()

	sql := "select schedule_detail_id,wyclass_id from schedule_detail_child where child_id=? and is_free=1 order by id desc "
	lessgo.Log.Debug(sql)

	rows, err := db.Query(sql, childId)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return "", "", err
	}

	if rows.Next() {
		err = commonlib.PutRecord(rows, &scheduleId, &classId)
		if err != nil {
			lessgo.Log.Error(err.Error())
			return "", "", err
		}
	}

	return scheduleId, classId, nil
}

/*
select id,child_id,apply_time,contract_no,parent_id,price,employee_id,center_id,course_id,left_lesson_num,type,status,expire_date from contract where id=?
*/
func getContractIdByChildIdAndScheduleId(childId, scheduleId string) (contractId, isFree string, err error) {

	sql := `select contract_id,is_free from schedule_detail_child where child_id=? and schedule_detail_id=?`
	lessgo.Log.Debug(sql)

	db := lessgo.GetMySQL()
	defer db.Close()

	rows, err := db.Query(sql, childId, scheduleId)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return "0", "", err
	}

	if rows.Next() {
		err = commonlib.PutRecord(rows, &contractId, &isFree)
	}

	if err != nil {
		lessgo.Log.Error(err.Error())
		return "0", "", err
	}

	return contractId, isFree, nil
}