Golang db.Query函數代碼示例

func (*GroupRegistrationsModel) Delete(id int) {
	// TODO: TT
	query := `with TT AS (
        DELETE
        FROM registrations
        WHERE registrations.id in (
            SELECT rs_gs.reg_id
            FROM regs_groupregs rs_gs
            WHERE rs_gs.groupreg_id = $1
            AND array_length(array(
                SELECT regs_groupregs.groupreg_id
                FROM regs_groupregs
                WHERE regs_groupregs.reg_id = rs_gs.reg_id
            ), 1) = 1
        ) returning registrations.id
    )
    DELETE FROM param_values WHERE param_values.reg_id in (
        SELECT rs_gs.reg_id
        FROM regs_groupregs rs_gs
        WHERE rs_gs.groupreg_id = $1
        AND array_length(array(
            SELECT regs_groupregs.groupreg_id
            FROM regs_groupregs
            WHERE regs_groupregs.reg_id = rs_gs.reg_id
        ), 1) = 1
    );`
	db.Query(query, []interface{}{id})

	query = `DELETE FROM group_registrations WHERE id = $1;`
	db.Query(query, []interface{}{id})
}

func (this *Entity) Select_(fields []string) []interface{} {
	query := "SELECT %s FROM %s"
	if len(this.wherePart) != 0 {
		where, params := this.GenerateWherePart(1)
		params = append(params, this.orderBy)
		query += " WHERE %s ORDER BY $" + strconv.Itoa(len(params))

		switch this.limit.(type) {
		case string:
			query += " LIMIT ALL"
			break
		case int:
			query += " LIMIT $" + strconv.Itoa(len(params))
			params = append(params, this.limit)
			break
		default:
			panic("Invalid type of limit")
		}

		params = append(params, this.offset)
		query += " OFFSET $" + strconv.Itoa(len(params)) + ";"

		return db.Query(fmt.Sprintf(query, strings.Join(fields, ", "), this.tableName, where), params)
	} else {
		query += " ORDER BY $1 LIMIT $2 OFFSET $3;"

		return db.Query(
			fmt.Sprintf(query, strings.Join(fields, ", "), this.tableName),
			[]interface{}{this.GetOrder(), this.GetLimit(), this.GetOffset()})
	}
}

func (*GroupsModel) Delete(id int) {
	query := `DELETE
        FROM persons
        WHERE persons.group_id = $1;`
	db.Query(query, []interface{}{id})

	query = `DELETE FROM groups WHERE id = $1;`
	db.Query(query, []interface{}{id})
}

func (*FacesModel) GetColModel(isAdmin bool, userId int) []map[string]interface{} {
	query := `SELECT array_to_string(
        array(SELECT users.id || ':' || users.login FROM users GROUP BY users.id ORDER BY users.id), ';') as name;`
	logins := db.Query(query, nil)[0].(map[string]interface{})["name"].(string)

	query = `SELECT array_to_string(
        array(
            SELECT f.id || ':' || f.id || '-' || array_to_string(
            array(
                SELECT param_values.value
                FROM param_values
                INNER JOIN registrations ON registrations.id = param_values.reg_id
                INNER JOIN faces ON faces.id = registrations.face_id
                INNER JOIN events ON events.id = registrations.event_id
                INNER JOIN params ON params.id = param_values.param_id
                WHERE param_values.param_id IN (5, 6, 7) AND events.id = 1 AND faces.id = f.id ORDER BY param_values.param_id
            ), ' ')
            FROM param_values
            INNER JOIN registrations as reg ON reg.id = param_values.reg_id
            INNER JOIN faces as f ON f.id = reg.face_id
            INNER JOIN events ON events.id = reg.event_id
            INNER JOIN params as p ON p.id = param_values.param_id
            INNER JOIN users ON users.id = f.user_id GROUP BY f.id ORDER BY f.id
        ), ';') as name;`

	faces := db.Query(query, nil)[0].(map[string]interface{})["name"].(string)

	return []map[string]interface{}{
		0: map[string]interface{}{
			"index":         "id",
			"name":          "id",
			"editable":      false,
			"formatter":     "select",
			"edittype":      "select",
			"stype":         "select",
			"search":        true,
			"editoptions":   map[string]string{"value": faces},
			"searchoptions": map[string]string{"value": ":Все;" + faces},
		},
		1: map[string]interface{}{
			"index":         "user_id",
			"name":          "user_id",
			"editable":      true,
			"formatter":     "select",
			"edittype":      "select",
			"stype":         "select",
			"search":        true,
			"editrules":     map[string]interface{}{"required": true},
			"editoptions":   map[string]string{"value": logins},
			"searchoptions": map[string]string{"value": ":Все;" + logins},
		},
	}
}

func (this *Handler) GroupsLoad() {
	userId, err := this.CheckSid()
	if err != nil {
		http.Error(this.Response, "Unauthorized", 400)
		return
	}

	limit, err := strconv.Atoi(this.Request.PostFormValue("rows"))
	if err != nil {
		http.Error(this.Response, err.Error(), 400)
		return
	}

	page, err := strconv.Atoi(this.Request.PostFormValue("page"))
	if err != nil {
		http.Error(this.Response, err.Error(), 400)
		return
	}

	sidx := this.Request.FormValue("sidx")
	start := limit*page - limit

	query := `SELECT groups.id, groups.name, groups.face_id
        FROM groups
        INNER JOIN persons ON persons.group_id = groups.id
        INNER JOIN faces ON faces.id = persons.face_id
        INNER JOIN users ON users.id = faces.user_id
        WHERE users.id = $1 ORDER BY $2 LIMIT $3 OFFSET $4;`
	rows := db.Query(query, []interface{}{userId, sidx, limit, start})

	query = `SELECT COUNT(*) FROM (SELECT groups.id FROM groups
        INNER JOIN persons ON persons.group_id = groups.id
        INNER JOIN faces ON faces.id = persons.face_id
        INNER JOIN users ON users.id = faces.user_id
        WHERE users.id = $1) as count;`
	count := int(db.Query(query, []interface{}{userId})[0].(map[string]interface{})["count"].(int))

	var totalPages int
	if count > 0 {
		totalPages = int(math.Ceil(float64(count) / float64(limit)))
	} else {
		totalPages = 0
	}

	result := make(map[string]interface{}, 2)
	result["rows"] = rows
	result["page"] = page
	result["total"] = totalPages
	result["records"] = count

	utils.SendJSReply(result, this.Response)
}

func (*GroupRegistrationsModel) GetColModel(isAdmin bool, userId int) []map[string]interface{} {
	query := `SELECT array_to_string(
        array(SELECT events.id || ':' || events.name FROM events GROUP BY events.id ORDER BY events.id), ';') as name;`
	events := db.Query(query, nil)[0].(map[string]interface{})["name"].(string)

	query = `SELECT array_to_string(
        array(SELECT groups.id || ':' || groups.name FROM groups GROUP BY groups.id ORDER BY groups), ';') as name;`
	groups := db.Query(query, nil)[0].(map[string]interface{})["name"].(string)

	return []map[string]interface{}{
		0: map[string]interface{}{
			"index":    "id",
			"name":     "id",
			"editable": false,
		},
		1: map[string]interface{}{
			"index":         "event_id",
			"name":          "event_id",
			"editable":      true,
			"formatter":     "select",
			"edittype":      "select",
			"stype":         "select",
			"search":        true,
			"editrules":     map[string]interface{}{"required": true},
			"editoptions":   map[string]string{"value": events},
			"searchoptions": map[string]string{"value": ":Все;" + events},
		},
		2: map[string]interface{}{
			"index":         "group_id",
			"name":          "group_id",
			"editable":      true,
			"formatter":     "select",
			"edittype":      "select",
			"stype":         "select",
			"search":        true,
			"editrules":     map[string]interface{}{"required": true},
			"editoptions":   map[string]string{"value": groups},
			"searchoptions": map[string]string{"value": ":Все;" + groups},
		},
		3: map[string]interface{}{
			"index":         "status",
			"name":          "status",
			"editable":      true,
			"editrules":     map[string]interface{}{"required": true},
			"formatter":     "checkbox",
			"formatoptions": map[string]interface{}{"disabled": true},
			"edittype":      "checkbox",
			"editoptions":   map[string]interface{}{"value": "true:false"},
		},
	}
}

func (*RegistrationsModel) Delete(id int) {
	query := `DELETE FROM param_values WHERE param_values.reg_id = $1;`
	db.Query(query, []interface{}{id})

	// query = `DELETE
	//     FROM faces
	//     WHERE faces.id in
	//     (SELECT registrations.face_id
	//         FROM registrations WHERE registrations.id = $1);`
	// db.Query(query, []interface{}{id})

	query = `DELETE FROM registrations WHERE id = $1;`
	db.Query(query, []interface{}{id})
}

func (this *Entity) Select(fields []string, filters map[string]interface{}) (result []interface{}) {
	if len(fields) == 0 {
		return nil
	}

	where, params, _ := this.Where(filters, 1)
	if where != "" {
		where = " WHERE " + where
	}
	query := `SELECT ` + strings.Join(fields, ", ") + ` FROM ` + this.GetTableName() + where
	query += ` ORDER BY ` + this.GetTableName() + "." + this.orderBy
	query += ` ` + this.sorting

	switch this.limit.(type) {
	case string:
		query += " LIMIT ALL"
		break
	case int:
		query += " LIMIT $" + strconv.Itoa(len(params))
		params = append(params, this.GetLimit())
		break
	default:
		panic("Invalid type of limit")
	}

	params = append(params, this.GetOffset())
	query += ` OFFSET $` + strconv.Itoa(len(params)) + `;`

	return db.Query(query, params)
}

func (this *FacesModel) Select(fields []string, filters map[string]interface{}) (result []interface{}) {
	if len(fields) == 0 {
		return nil
	}

	query := `SELECT `

	for _, field := range fields {
		switch field {
		case "id":
			query += "faces.id, "
			break
		case "user_id":
			query += "users.login, "
			break
		}
	}

	query = query[:len(query)-2]
	query += ` FROM faces INNER JOIN users ON users.id = faces.user_id`
	where, params, _ := this.Where(filters, 1)
	if where != "" {
		query += ` WHERE ` + where
	}
	query += ` ORDER BY faces.` + this.orderBy
	query += ` ` + this.GetSorting()
	params = append(params, this.GetLimit())
	query += ` LIMIT $` + strconv.Itoa(len(params))
	params = append(params, this.GetOffset())
	query += ` OFFSET $` + strconv.Itoa(len(params)) + `;`

	return db.Query(query, params)
}

func (this *Handler) GroupRegistrationsLoad() {
	userId, err := this.CheckSid()
	if err != nil {
		http.Error(this.Response, "Unauthorized", 400)
		return
	}

	limit, err := strconv.Atoi(this.Request.PostFormValue("rows"))
	if err != nil {
		http.Error(this.Response, err.Error(), 400)
		return
	}

	page, err := strconv.Atoi(this.Request.PostFormValue("page"))
	if err != nil {
		http.Error(this.Response, err.Error(), 400)
		return
	}

	sidx := this.Request.FormValue("sidx")
	start := limit*page - limit

	query := `SELECT group_registrations.id, group_registrations.event_id,
            group_registrations.group_id
        FROM group_registrations
        INNER JOIN events ON events.id = group_registrations.event_id
        INNER JOIN groups ON groups.id = group_registrations.group_id
        INNER JOIN persons ON persons.group_id = groups.id
        INNER JOIN faces ON faces.id = persons.face_id
        INNER JOIN users ON users.id = faces.user_id
        WHERE users.id = $1 AND events.team = $2 ORDER BY $3 LIMIT $4 OFFSET $5;`
	rows := db.Query(query, []interface{}{userId, true, sidx, limit, start})

	query = `SELECT COUNT(*) FROM (SELECT group_registrations.id
        FROM group_registrations
        INNER JOIN events ON events.id = group_registrations.event_id
        INNER JOIN groups ON groups.id = group_registrations.group_id
        INNER JOIN persons ON persons.group_id = groups.id
        INNER JOIN faces ON faces.id = persons.face_id
        INNER JOIN users ON users.id = faces.user_id
        WHERE users.id = $1 AND events.team = $2 GROUP BY group_registrations.id) as count;`

	var count int
	db.QueryRow(query, []interface{}{userId, true}).Scan(&count)

	var totalPages int
	if count > 0 {
		totalPages = int(math.Ceil(float64(count) / float64(limit)))
	} else {
		totalPages = 0
	}

	result := make(map[string]interface{}, 2)
	result["rows"] = rows
	result["page"] = page
	result["total"] = totalPages
	result["records"] = count

	utils.SendJSReply(result, this.Response)
}

func (this *BlankController) GetHistoryRequest() {
	userId, err := this.CheckSid()
	if err != nil {
		utils.SendJSReply(map[string]interface{}{"result": "Unauthorized"}, this.Response)
		return
	}

	data, err := utils.ParseJS(this.Request, this.Response)
	if err != nil {
		utils.SendJSReply(map[string]interface{}{"result": err.Error()}, this.Response)
		return
	}

	eventId, err := strconv.Atoi(data["event_id"].(string))
	if err != nil {
		utils.SendJSReply(map[string]interface{}{"result": err.Error()}, this.Response)
		return
	}

	query := `SELECT params.id as param_id, params.name as param_name,
            param_types.name as type, param_values.value, forms.id as form_id
        FROM events
        INNER JOIN events_forms ON events_forms.event_id = events.id
        INNER JOIN forms ON events_forms.form_id = forms.id
        INNER JOIN registrations ON events.id = registrations.event_id
        INNER JOIN faces ON faces.id = registrations.face_id
        INNER JOIN users ON users.id = faces.user_id
        INNER JOIN params ON params.form_id = forms.id
        INNER JOIN param_types ON param_types.id = params.param_type_id
        INNER JOIN param_values ON param_values.param_id = params.id
            AND param_values.reg_id = registrations.id
        WHERE users.id = $1 AND events.id = $2 AND forms.personal = true;`

	utils.SendJSReply(map[string]interface{}{"result": "ok", "data": db.Query(query, []interface{}{userId, eventId})}, this.Response)
}

//-----------------------------------------------------------------------------
func (this *GridController) GetEventTypesByEventId() {
	if !sessions.CheckSession(this.Response, this.Request) {
		http.Redirect(this.Response, this.Request, "/", http.StatusUnauthorized)

		return
	}

	if !this.isAdmin() {
		http.Redirect(this.Response, this.Request, "/", http.StatusForbidden)

		return
	}

	request, err := utils.ParseJS(this.Request, this.Response)
	if err != nil {
		utils.SendJSReply(map[string]interface{}{"result": err.Error()}, this.Response)

		return
	}

	eventId, err := strconv.Atoi(request["event_id"].(string))
	if err != nil {
		utils.SendJSReply(map[string]interface{}{"result": err.Error()}, this.Response)

		return
	}

	query := `SELECT event_types.id, event_types.name FROM events_types
        INNER JOIN events ON events.id = events_types.event_id
        INNER JOIN event_types ON event_types.id = events_types.type_id
        WHERE events.id = $1 ORDER BY event_types.id;`
	result := db.Query(query, []interface{}{eventId})

	utils.SendJSReply(map[string]interface{}{"result": "ok", "data": result}, this.Response)
}

func (this *BlankController) GetListHistoryEvents() {
	userId, err := this.CheckSid()
	if err != nil {
		utils.SendJSReply(map[string]interface{}{"result": "Unauthorized"}, this.Response)

		return
	}

	data, err := utils.ParseJS(this.Request, this.Response)
	if err != nil {
		utils.SendJSReply(map[string]interface{}{"result": err.Error()}, this.Response)

		return
	}

	ids := map[string]interface{}{"form_id": make([]interface{}, 0)}
	if data["form_ids"] == nil || len(data["form_ids"].([]interface{})) == 0 {
		utils.SendJSReply(map[string]interface{}{"result": "Нет данных о формах анкеты"}, this.Response)

		return
	}

	for _, v := range data["form_ids"].([]interface{}) {
		ids["form_id"] = append(ids["form_id"].([]interface{}), int(v.(float64)))
	}

	eventsForms := this.GetModel("events_forms")
	events := eventsForms.
		LoadWherePart(ids).
		SetCondition(models.OR).
		Select_([]string{"event_id"})

	if len(events) == 0 {
		utils.SendJSReply(map[string]interface{}{"result": "Нет данных"}, this.Response)

		return
	}

	query := `SELECT DISTINCT events.id, events.name FROM events
        INNER JOIN events_forms ON events_forms.event_id = events.id
        INNER JOIN forms ON events_forms.form_id = forms.id
        INNER JOIN registrations ON registrations.event_id = events.id
        INNER JOIN faces ON faces.id = registrations.face_id
        INNER JOIN users ON users.id = faces.user_id
        WHERE users.id=$1 AND events.id IN (`

	var i int
	params := []interface{}{userId}

	for i = 2; i < len(events); i++ {
		query += "$" + strconv.Itoa(i) + ", "
		params = append(params, int(events[i-2].(map[string]interface{})["event_id"].(int)))
	}

	query += "$" + strconv.Itoa(i) + ")"
	params = append(params, int(events[i-2].(map[string]interface{})["event_id"].(int)))

	utils.SendJSReply(map[string]interface{}{"result": "ok", "data": db.Query(query, params)}, this.Response)
}

func generateUsers(count int) {
	base := new(controllers.BaseController)
	date := time.Now().Format("2006-01-02T15:04:05Z00:00")

	type FullNames struct {
		firstNames, lastNames, patronymics []string
	}

	male := FullNames{
		firstNames:  readStringsFromFile(getResourcePath("first-name-male.txt")),
		lastNames:   readStringsFromFile(getResourcePath("last-name-male.txt")),
		patronymics: readStringsFromFile(getResourcePath("patronymic-male.txt")),
	}
	female := FullNames{
		firstNames:  readStringsFromFile(getResourcePath("first-name-female.txt")),
		lastNames:   readStringsFromFile(getResourcePath("last-name-female.txt")),
		patronymics: readStringsFromFile(getResourcePath("patronymic-female.txt")),
	}

	for i := 0; i < count; i++ {
		userName := "user" + strconv.Itoa(i+1)
		userEmail := userName + "@example.com"

		result, regId := base.RegistrationController().Register(userName, "password", userEmail, "user")
		if result != "ok" {
			log.Fatalln("[loadUsers]:", result)
		}

		query := `INSERT INTO param_values (param_id, value, date, reg_id)
            VALUES ($1, $2, $3, $4);`

		db.Exec(query, []interface{}{4, userEmail, date, regId})
		var fullNames FullNames
		if rand.Int()%2 == 1 {
			fullNames = male
		} else {
			fullNames = female
		}
		db.Exec(query, []interface{}{6, fullNames.firstNames[rand.Intn(len(fullNames.firstNames))], date, regId})
		db.Exec(query, []interface{}{5, fullNames.lastNames[rand.Intn(len(fullNames.lastNames))], date, regId})
		db.Exec(query, []interface{}{7, fullNames.patronymics[rand.Intn(len(fullNames.patronymics))], date, regId})

		query = `SELECT users.token FROM registrations
            INNER JOIN events ON registrations.event_id = events.id
            INNER JOIN faces ON faces.id = registrations.face_id
            INNER JOIN users ON users.id = faces.user_id
            WHERE events.id = $1 AND registrations.id = $2;`
		res := db.Query(query, []interface{}{1, regId})

		if len(res) == 0 {
			log.Fatalln("[loadUsers]:", "Data are not found")
		}

		token := res[0].(map[string]interface{})["token"].(string)
		base.RegistrationController().ConfirmUser(token)
	}
}

func (this *RegistrationController) InsertUserParams(userId, regId int, data []interface{}) (err error) {
	var paramValueIds []string

	date := time.Now().Format("2006-01-02T15:04:05Z00:00")

	for _, element := range data {
		paramId, err := strconv.Atoi(element.(map[string]interface{})["id"].(string))
		if err != nil {
			continue
		}

		if paramId == 1 || paramId == 2 || paramId == 3 {
			continue
		}

		query := `SELECT params.name, params.required, params.editable
            FROM params
            WHERE params.id = $1;`
		result := db.Query(query, []interface{}{paramId})

		name := result[0].(map[string]interface{})["name"].(string)
		required := result[0].(map[string]interface{})["required"].(bool)
		editable := result[0].(map[string]interface{})["editable"].(bool)
		value := element.(map[string]interface{})["value"].(string)

		if required && utils.MatchRegexp("^[ \t\v\r\n\f]{0,}$", value) {
			db.QueryDeleteByIds("param_vals", strings.Join(paramValueIds, ", "))
			db.QueryDeleteByIds("registrations", strconv.Itoa(regId))

			return errors.New("Заполните параметр '" + name + "'.")
		}

		if !editable {
			value = " "
		}

		var paramValId int
		err = this.GetModel("param_values").
			LoadModelData(map[string]interface{}{
				"param_id": paramId,
				"value":    value,
				"date":     date,
				"user_id":  userId,
				"reg_id":   regId}).
			QueryInsert("RETURNING id").
			Scan(&paramValId)
		if err, ok := err.(*pq.Error); ok {
			log.Println(err.Code.Name())
		}

		paramValueIds = append(paramValueIds, strconv.Itoa(paramValId))
	}

	return nil
}