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 := "******" + 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(¶mValId)
if err, ok := err.(*pq.Error); ok {
log.Println(err.Code.Name())
}
paramValueIds = append(paramValueIds, strconv.Itoa(paramValId))
}
return nil
}
func (*RegsGroupRegsModel) GetColModel(isAdmin bool, userId int) []map[string]interface{} {
query := `SELECT array_to_string(
array(SELECT group_registrations.id || ':' || group_registrations.id FROM group_registrations GROUP BY group_registrations.id ORDER BY group_registrations.id), ';') as name;`
groupRegs := db.Query(query, nil)[0].(map[string]interface{})["name"].(string)
query = `SELECT array_to_string(
array(SELECT registrations.id || ':' || registrations.id FROM registrations GROUP BY registrations.id ORDER BY registrations.id), ';') as name;`
regs := 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": "groupreg_id",
"name": "groupreg_id",
"editable": true,
"formatter": "select",
"edittype": "select",
"stype": "select",
"search": true,
"editrules": map[string]interface{}{"required": true},
"editoptions": map[string]string{"value": groupRegs},
"searchoptions": map[string]string{"value": ":Все;" + groupRegs},
},
2: map[string]interface{}{
"index": "reg_id",
"name": "reg_id",
"editable": true,
"formatter": "select",
"edittype": "select",
"stype": "select",
"search": true,
"editrules": map[string]interface{}{"required": true},
"editoptions": map[string]string{"value": regs},
"searchoptions": map[string]string{"value": ":Все;" + regs},
},
}
}
func (*EventsTypesModel) 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 event_types.id || ':' || event_types.name FROM event_types GROUP BY event_types.id ORDER BY event_types.id), ';') as name;`
types := 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": "type_id",
"name": "type_id",
"editable": true,
"formatter": "select",
"edittype": "select",
"stype": "select",
"search": true,
"editrules": map[string]interface{}{"required": true},
"editoptions": map[string]string{"value": types},
"searchoptions": map[string]string{"value": ":Все;" + types},
},
}
}
//-----------------------------------------------------------------------------
func (this *BlankController) EditParams() {
userId, err := this.CheckSid()
if err != nil {
http.Redirect(this.Response, this.Request, "/", http.StatusUnauthorized)
return
}
request, err := utils.ParseJS(this.Request, this.Response)
if err != nil {
utils.SendJSReply(map[string]interface{}{"result": err.Error()}, this.Response)
return
}
date := time.Now().Format("2006-01-02T15:04:05Z00:00")
for _, v := range request["data"].([]interface{}) {
paramValId, err := strconv.Atoi(v.(map[string]interface{})["param_val_id"].(string))
if err != nil {
utils.SendJSReply(map[string]interface{}{"result": err.Error()}, this.Response)
return
}
query := `SELECT params.name, params.required, params.editable
FROM params
INNER JOIN param_values ON param_values.param_id = params.id
WHERE param_values.id = $1;`
result := db.Query(query, []interface{}{paramValId})
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 := v.(map[string]interface{})["value"].(string)
if required && utils.MatchRegexp("^[ \t\v\r\n\f]{0,}$", value) {
utils.SendJSReply(map[string]interface{}{"result": "Заполните параметр '" + name + "'"}, this.Response)
return
}
if !this.isAdmin() && !editable {
continue
}
if value == "" {
value = " "
}
params := map[string]interface{}{"value": value, "date": date, "user_id": userId}
where := map[string]interface{}{"id": paramValId}
this.GetModel("param_values").Update(this.isAdmin(), userId, params, where)
}
utils.SendJSReply(map[string]interface{}{"result": "Изменения сохранены"}, this.Response)
}
func (this *ParamValuesModel) 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 += "param_values.id, "
break
case "param_id":
query += "forms.name || ': ' || params.name as name, "
break
case "reg_id":
query += "registrations.id || ' - ' || events.name, "
break
case "value":
query += "param_values.value, "
break
case "date":
query += "param_values.date, "
break
case "user_id":
query += "users.login, "
break
}
}
query = query[:len(query)-2]
query += ` FROM registrations
INNER JOIN param_values ON param_values.reg_id = registrations.id
INNER JOIN params ON params.id = param_values.param_id
INNER JOIN forms ON forms.id = params.form_id
INNER JOIN users ON users.id = param_values.user_id
INNER JOIN events ON events.id = registrations.event_id`
where, params, _ := this.Where(filters, 1)
if where != "" {
where = " WHERE " + where
}
query += ` ORDER BY param_values.` + 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 *Blank) GetEmptyBlank(eventId int) []interface{} {
query := `SELECT forms.id as form_id, forms.name as form_name,
params.id as param_id, params.name as param_name, params.required,
params.editable, param_types.name as type, events.name as event_name,
events.id as event_id
FROM events_forms
INNER JOIN events ON events.id = events_forms.event_id
INNER JOIN forms ON forms.id = events_forms.form_id
INNER JOIN params ON forms.id = params.form_id
INNER JOIN param_types ON param_types.id = params.param_type_id
WHERE events.id = $1 AND forms.personal = $2 ORDER BY forms.id, params.id;`
return db.Query(query, []interface{}{eventId, this.personal})
}
func (this *UserController) SendEmailWellcomeToProfile() {
if !this.isAdmin() {
http.Redirect(this.Response, this.Request, "/", http.StatusForbidden)
return
}
request, err := utils.ParseJS(this.Request, this.Response)
if utils.HandleErr("[UserController::SendEmailWellcomeToProfile]: ", err, this.Response) {
utils.SendJSReply(map[string]interface{}{"result": err.Error()}, this.Response)
return
}
userId, err := strconv.Atoi(request["user_id"].(string))
if err != nil {
utils.SendJSReply(map[string]interface{}{"result": err.Error()}, this.Response)
return
}
query := `SELECT param_values.value
FROM param_values
INNER JOIN registrations ON registrations.id = param_values.reg_id
INNER JOIN params ON params.id = param_values.param_id
INNER JOIN events ON events.id = registrations.event_id
INNER JOIN faces ON faces.id = registrations.face_id
INNER JOIN users ON users.id = faces.user_id
WHERE params.id in (4, 5, 6, 7) AND users.id = $1 ORDER BY params.id;`
data := db.Query(query, []interface{}{userId})
if len(data) < 4 {
utils.SendJSReply(map[string]interface{}{"result": "Нет регистрационных данных пользователя."}, this.Response)
return
}
to := data[1].(map[string]interface{})["value"].(string) + " "
to += data[2].(map[string]interface{})["value"].(string) + " "
to += data[3].(map[string]interface{})["value"].(string)
email := data[0].(map[string]interface{})["value"].(string)
token := utils.GetRandSeq(HASH_SIZE)
if !mailer.SendEmailWellcomeToProfile(to, email, token) {
utils.SendJSReply(map[string]interface{}{"result": "Проверьте правильность email."}, this.Response)
return
}
params := map[string]interface{}{"token": token, "enabled": true}
where := map[string]interface{}{"id": userId}
this.GetModel("users").Update(this.isAdmin(), userId, params, where)
utils.SendJSReply(map[string]interface{}{"result": "Письмо отправлено"}, this.Response)
}
//-----------------------------------------------------------------------------
func (this *BlankController) GetEditHistoryData() {
data, err := utils.ParseJS(this.Request, this.Response)
if err != nil {
utils.SendJSReply(map[string]interface{}{"result": err.Error()}, this.Response)
return
}
regId, err := strconv.Atoi(data["reg_id"].(string))
if err != nil {
utils.SendJSReply(map[string]interface{}{"result": err.Error()}, this.Response)
return
}
formType := data["personal"].(string)
if formType != "true" && formType != "false" {
utils.SendJSReply(map[string]interface{}{"result": "Invalid form type"}, this.Response)
return
}
query := `SELECT params.id as param_id, forms.id as form_id, p.date as edit_date,
array_to_string(ARRAY(
SELECT param_values.value
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 registrations.id = param_values.reg_id
WHERE (params.id in (5, 6, 7) AND events.id = 1) and users.id = p.user_id
), ' ') as login
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 as p ON p.param_id = params.id
AND p.reg_id = registrations.id
WHERE registrations.id = $1 AND forms.personal = $2;`
utils.SendJSReply(map[string]interface{}{"result": "ok", "data": db.Query(query, []interface{}{regId, formType})}, this.Response)
}
func (this *ParamsModel) 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 += "params.id, "
break
case "name":
query += "params.name as param_name, "
break
case "param_type_id":
query += "param_types.name as type_name, "
break
case "form_id":
query += "forms.name as form_name, "
break
case "identifier":
query += "params.identifier, "
break
case "required":
query += "params.required, "
break
}
}
query = query[:len(query)-2]
query += ` FROM params
INNER JOIN param_types ON param_types.id = params.param_type_id
INNER JOIN forms ON forms.id = params.form_id`
where, params, _ := this.Where(filters, 1)
if where != "" {
where = " WHERE " + where
}
query += ` ORDER BY params.` + 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 *PersonsModel) 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 += "persons.id, "
break
case "group_id":
query += "groups.name as group_name, "
break
case "status":
query += "persons.status, "
break
case "face_id":
query += "array_to_string(array_agg(param_values.value), ' ') as face_name, "
break
}
}
query = query[:len(query)-2]
query += ` 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
INNER JOIN persons ON persons.face_id = faces.id
INNER JOIN groups ON groups.id = persons.group_id`
where, params, _ := this.Where(filters, 1)
if where != "" {
query += ` WHERE ` + where + ` AND params.id in (5, 6, 7) AND events.id = 1 GROUP BY persons.id, groups.id`
} else {
query += ` WHERE params.id in (5, 6, 7) AND events.id = 1 GROUP BY persons.id, groups.id`
}
query += ` ORDER BY persons.` + 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 *Entity) _select(fields []string, whereCond string, whereParam []interface{}) []interface{} {
if len(fields) == 0 {
return nil
}
query := fmt.Sprintf("SELECT %s FROM %s", strings.Join(fields, ", "), this.GetTableName())
var params []interface{}
if whereCond != "" {
query += " WHERE " + whereCond
params = append(params, whereParam...)
}
params = append(params, this.GetTableName()+"."+this.GetOrder()+" "+this.GetSorting())
query += " ORDER BY $" + strconv.Itoa(len(params))
if l, f := this.GetLimit().(int); f {
params = append(params, l)
query += " LIMIT $" + strconv.Itoa(len(params))
}
params = append(params, this.GetOffset())
query += " OFFSET $" + strconv.Itoa(len(params))
return db.Query(query, params)
}
func (this *GroupRegistrationsModel) 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 += "group_registrations.id, "
break
case "event_id":
query += "events.name as event_name, "
break
case "group_id":
query += "groups.name as group_name, "
break
case "status":
query += "group_registrations.status, "
break
}
}
query = query[:len(query)-2]
query += ` FROM group_registrations
INNER JOIN events ON events.id = group_registrations.event_id
INNER JOIN groups ON groups.id = group_registrations.group_id`
where, params, _ := this.Where(filters, 1)
if where != "" {
where = " WHERE " + where
}
query += ` ORDER BY group_registrations.` + 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 loadUsers() {
base := new(controllers.BaseController)
date := time.Now().Format("2006-01-02T15:04:05Z00:00")
for i := 0; i < USER_COUNT; i++ {
rand.Seed(int64(i))
userName := "******" + strconv.Itoa(i)
userEmail := userName + "@mail.ru"
result, regId := base.RegistrationController().Register(userName, "secret"+strconv.Itoa(i), userEmail, "user")
if result != "ok" {
utils.HandleErr("[loadUsers]: "+result, nil, nil)
continue
}
query := `INSERT INTO param_values (param_id, value, date, user_id, reg_id)
VALUES (4, $1, $2, NULL, $3);`
db.Exec(query, []interface{}{userEmail, date, regId})
for k := 5; k < 8; k++ {
query := `INSERT INTO param_values (param_id, value, date, user_id, reg_id)
VALUES (` + strconv.Itoa(k) + `, '` + userName + `', $1, NULL, $2);`
db.Exec(query, []interface{}{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 {
utils.HandleErr("[loadUsers]: ", errors.New("Data are not faund."), nil)
continue
}
token := res[0].(map[string]interface{})["token"].(string)
base.RegistrationController().ConfirmUser(token)
}
}
func (this *EventsTypesModel) 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 += "events_types.id, "
break
case "event_id":
query += "events.name as event_name, "
break
case "type_id":
query += "event_types.name as type_name, "
break
}
}
query = query[:len(query)-2]
query += ` 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, params, _ := this.Where(filters, 1)
if where != "" {
where = " WHERE " + where
}
query += where
query += ` ORDER BY events_types.` + 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 loadAdmin() {
base := new(controllers.BaseController)
date := time.Now().Format("2006-01-02T15:04:05Z00:00")
result, regId := base.RegistrationController().Register("admin", "password", mailer.Admin_.Email, "admin")
if result != "ok" {
utils.HandleErr("[LoadAdmin]: "+result, nil, nil)
return
}
query := `INSERT INTO param_values (param_id, value, date, user_id, reg_id)
VALUES (4, $1, $2, NULL, $3);`
db.Exec(query, []interface{}{mailer.Admin_.Email, date, regId})
for k := 5; k < 8; k++ {
query := `INSERT INTO param_values (param_id, value, date, user_id, reg_id)
VALUES (` + strconv.Itoa(k) + `, 'admin', $1, NULL, $2);`
db.Exec(query, []interface{}{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 {
utils.HandleErr("[LoadAdmin]: ", errors.New("Data are not faund."), nil)
return
}
token := res[0].(map[string]interface{})["token"].(string)
base.RegistrationController().ConfirmUser(token)
}
func (this *UserController) ConfirmOrRejectPersonRequest() {
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
}
regId, err := strconv.Atoi(request["reg_id"].(string))
if err != nil {
utils.SendJSReply(map[string]interface{}{"result": err.Error()}, this.Response)
return
}
query := `SELECT param_values.value, users.id as user_id
FROM param_values
INNER JOIN registrations ON registrations.id = param_values.reg_id
INNER JOIN params ON params.id = param_values.param_id
INNER JOIN events ON events.id = registrations.event_id
INNER JOIN faces ON faces.id = registrations.face_id
INNER JOIN users ON users.id = faces.user_id
WHERE params.id in (4, 5, 6, 7) AND users.id in (
SELECT users.id FROM registrations INNER JOIN events ON events.id = registrations.event_id
INNER JOIN faces ON faces.id = registrations.face_id
INNER JOIN users ON users.id = faces.user_id
WHERE registrations.id = $1
) ORDER BY params.id;`
data := db.Query(query, []interface{}{regId})
if len(data) < 2 {
utils.SendJSReply(
map[string]interface{}{"result": "Нет регистрационных данных пользователя"},
this.Response)
return
}
email := data[0].(map[string]interface{})["value"].(string)
to := data[1].(map[string]interface{})["value"].(string)
to += " " + data[2].(map[string]interface{})["value"].(string)
to += " " + data[3].(map[string]interface{})["value"].(string)
event := db.Query(
"SELECT name FROM events WHERE id=$1;",
[]interface{}{eventId})[0].(map[string]interface{})["name"].(string)
if request["confirm"].(bool) {
if eventId == 1 {
utils.SendJSReply(map[string]interface{}{"result": "Эту заявку нельзя подтвердить письмом"}, this.Response)
} else {
if mailer.SendEmailToConfirmRejectPersonRequest(to, email, event, true) {
utils.SendJSReply(map[string]interface{}{"result": "Письмо с подтверждением заявки отправлено"}, this.Response)
} else {
utils.SendJSReply(map[string]interface{}{"result": "Ошибка. Письмо с подтверждением заявки не отправлено"}, this.Response)
}
}
} else {
if eventId == 1 {
utils.SendJSReply(map[string]interface{}{"result": "Эту заявку нельзя отклонить письмом"}, this.Response)
} else {
query := `DELETE FROM param_values WHERE param_values.reg_id = $1;`
db.Query(query, []interface{}{regId})
query = `DELETE FROM registrations WHERE id = $1;`
db.Query(query, []interface{}{regId})
if mailer.SendEmailToConfirmRejectPersonRequest(to, email, event, false) {
utils.SendJSReply(map[string]interface{}{"result": "Письмо с отклонением заявки отправлено"}, this.Response)
} else {
utils.SendJSReply(map[string]interface{}{"result": "Ошибка. Письмо с отклонением заявки не отправлено"}, this.Response)
}
}
}
}