func getChronoRecursive(tx *sqlx.Tx, chrono *ChronologyTreeStruct) error {
var err error = nil
// load translations
tr := []model.Chronology_tr{}
err = tx.Select(&tr, "SELECT * FROM chronology_tr WHERE chronology_id = "+strconv.Itoa(chrono.Id))
if err != nil {
return err
}
chrono.Name = model.MapSqlTranslations(tr, "Lang_isocode", "Name")
chrono.Description = model.MapSqlTranslations(tr, "Lang_isocode", "Description")
// get the childs of this chronology from the db
childs, err := chrono.Chronology.Childs(tx)
if err != nil {
return err
}
// recurse
chrono.Content = make([]ChronologyTreeStruct, len(childs))
for i, child := range childs {
chrono.Content[i].Chronology = child
err = getChronoRecursive(tx, &chrono.Content[i])
if err != nil {
return err
}
}
return nil
}
func getWmLayers(params *LayersParams, viewUnpublished bool, tx *sqlx.Tx) (layers []*model.LayerFullInfos, err error) {
layers = []*model.LayerFullInfos{}
q := "SELECT m.id, m.type, m.start_date, m.end_date, m.min_scale, m.max_scale, ST_AsGeoJSON(m.geographical_extent_geom) as geographical_extent_geom, m.published, m.created_at, m.creator_user_id, u.firstname || ' ' || u.lastname as author FROM map_layer m LEFT JOIN \"user\" u ON m.creator_user_id = u.id WHERE m.id > 0"
if params.Author > 0 {
q += " AND u.id = :author"
}
if params.Published || !viewUnpublished {
q += " AND m.published = 't'"
}
if params.Type != "" {
q += " AND m.type= :type"
}
if params.Bounding_box != "" {
q += " AND (ST_Contains(ST_GeomFromGeoJSON(:bounding_box), m.geographical_extent_geom::::geometry) OR ST_Contains(m.geographical_extent_geom::::geometry, ST_GeomFromGeoJSON(:bounding_box)) OR ST_Overlaps(ST_GeomFromGeoJSON(:bounding_box), m.geographical_extent_geom::::geometry))"
}
if params.Check_dates {
q += " AND m.start_date >= :start_date AND m.end_date <= :end_date"
}
in := model.IntJoin(params.Ids, false)
if in != "" {
q += " AND m.id IN (" + in + ")"
}
nstmt, err := tx.PrepareNamed(q)
if err != nil {
log.Println(err)
_ = tx.Rollback()
return
}
err = nstmt.Select(&layers, params)
for _, layer := range layers {
tr := []model.Map_layer_tr{}
err = tx.Select(&tr, "SELECT * FROM map_layer_tr WHERE map_layer_id = "+strconv.Itoa(layer.Id))
if err != nil {
log.Println(err)
_ = tx.Rollback()
return
}
layer.Uniq_code = layer.Type + strconv.Itoa(layer.Id)
layer.Name = model.MapSqlTranslations(tr, "Lang_isocode", "Name")
layer.Attribution = model.MapSqlTranslations(tr, "Lang_isocode", "Attribution")
layer.Copyright = model.MapSqlTranslations(tr, "Lang_isocode", "Copyright")
layer.Description = model.MapSqlTranslations(tr, "Lang_isocode", "Description")
}
return
}
func getCharacRecursive(tx *sqlx.Tx, charac *CharacTreeStruct, project_id int) error {
var err error = nil
// load translations
tr := []model.Charac_tr{}
err = tx.Select(&tr, "SELECT * FROM charac_tr WHERE charac_id = "+strconv.Itoa(charac.Id))
if err != nil {
return err
}
charac.Name = model.MapSqlTranslations(tr, "Lang_isocode", "Name")
charac.Description = model.MapSqlTranslations(tr, "Lang_isocode", "Description")
// check if enabled in project
if project_id > 0 {
hiddenCount := 0
tx.Get(&hiddenCount, "SELECT count(*) FROM project_hidden_characs WHERE project_id = "+strconv.Itoa(project_id)+" AND charac_id = "+strconv.Itoa(charac.Id))
if hiddenCount > 0 {
charac.Hidden = true
log.Println("found hidden : ", charac.Id)
}
}
// get the childs of this charac from the db
childs, err := charac.Charac.Childs(tx)
if err != nil {
return err
}
// recurse
charac.Content = make([]CharacTreeStruct, len(childs))
for i, child := range childs {
charac.Content[i].Charac = child
err = getCharacRecursive(tx, &charac.Content[i], project_id)
if err != nil {
return err
}
}
return nil
}
// CharacsRoots write all root characs in all languages
func CharacsRoots(w http.ResponseWriter, r *http.Request, proute routes.Proute) {
type row struct {
model.Charac_root
model.Charac
Name map[string]string `json:"name"`
Description map[string]string `json:"description"`
HiddensCount int `json:"hiddens_count"`
//UsersInGroup []model.User `json:"users_in_group" ignore:"true"` // read-only, used to display users of the group
//Author model.User `json:"author" ignore:"true"` // read-only, used to display users of the group
}
params := proute.Params.(*CharacRootsParams)
characs := []*row{}
// transaction begin...
tx, err := db.DB.Beginx()
if err != nil {
log.Println(err)
userSqlError(w, err)
return
}
// if Project_id is specified, verify that we are the owner of the project
if params.Project_id != 0 {
// get the user
_user, ok := proute.Session.Get("user")
if !ok {
log.Println("CharacsUpdate: can't get user in session...", _user)
_ = tx.Rollback()
return
}
user, ok := _user.(model.User)
if !ok {
log.Println("CharacsUpdate: can't cast user...", _user)
_ = tx.Rollback()
return
}
// check if the user is the owner of the project
count := 0
tx.Get(&count, "SELECT count(*) FROM project WHERE id = "+strconv.Itoa(params.Project_id)+" AND user_id = "+strconv.Itoa(user.Id))
if count != 1 {
log.Println("CharacSetHiddens: user is not the owner...", user, params.Project_id)
_ = tx.Rollback()
return
}
}
// load all roots
err = db.DB.Select(&characs, "SELECT charac_root.* FROM charac_root LEFT JOIN charac on charac_root.root_charac_id = charac.id ORDER BY charac.order")
if err != nil {
log.Println(err)
userSqlError(w, err)
_ = tx.Rollback()
return
}
// load all root characs
for _, charac := range characs {
charac.Charac.Id = charac.Charac_root.Root_charac_id
err = charac.Charac.Get(tx)
if err != nil {
log.Println(err)
_ = tx.Rollback()
userSqlError(w, err)
return
}
// load translations
tr := []model.Charac_tr{}
err = tx.Select(&tr, "SELECT * FROM charac_tr WHERE charac_id = "+strconv.Itoa(charac.Charac.Id))
if err != nil {
log.Println(err)
userSqlError(w, err)
_ = tx.Rollback()
return
}
charac.Name = model.MapSqlTranslations(tr, "Lang_isocode", "Name")
charac.Description = model.MapSqlTranslations(tr, "Lang_isocode", "Description")
// load custom modified characs
// get the author user
/* err = charac.Author.Get(tx)
charac.Author.Password = ""
if err != nil {
userSqlError(w, err)
_ = tx.Rollback()
return
}
*/
if params.Project_id > 0 {
hidden_count := 0
err = tx.Get(&hidden_count, `WITH RECURSIVE subcharac(id, parent_id, charac_id, project_id) AS (
SELECT id, parent_id, phc.charac_id, phc.project_id
FROM charac LEFT JOIN project_hidden_characs phc ON phc.charac_id = charac.id WHERE id = $1
UNION ALL
SELECT c2.id, c2.parent_id, phc2.charac_id, phc2.project_id
FROM subcharac AS sc, charac AS c2 LEFT JOIN project_hidden_characs phc2 ON phc2.charac_id = c2.id
WHERE c2.parent_id = sc.id
)
SELECT count(*) FROM subcharac WHERE project_id=$2`, charac.Id, params.Project_id)
if err != nil {
log.Println(err)
userSqlError(w, err)
_ = tx.Rollback()
return
}
charac.HiddensCount = hidden_count
}
}
// commit...
err = tx.Commit()
if err != nil {
log.Println("commit failed")
userSqlError(w, err)
return
}
j, err := json.Marshal(characs)
w.Write(j)
}
// ChronologiesRoots write all root chronologies in all languages
func ChronologiesRoots(w http.ResponseWriter, r *http.Request, proute routes.Proute) {
type row struct {
model.Chronology_root
model.Chronology
Name map[string]string `json:"name"`
Description map[string]string `json:"description"`
//UsersInGroup []model.User `json:"users_in_group" ignore:"true"` // read-only, used to display users of the group
Author model.User `json:"author" ignore:"true"` // read-only, used to display users of the group
}
// get the params
params := proute.Params.(*ChronologiesRootsParams)
// get the user logged
_user, _ := proute.Session.Get("user")
user := _user.(model.User)
chronologies := []*row{}
returnedChronologies := []*row{}
// transaction begin...
tx, err := db.DB.Beginx()
if err != nil {
userSqlError(w, err)
return
}
// load all roots yes condition is always true
q := "SELECT *,ST_AsGeoJSON(geom) as geom FROM chronology_root WHERE 1 = 1"
if params.Bounding_box != "" {
q += " AND (ST_Contains(ST_GeomFromGeoJSON(:bounding_box), geom::::geometry) OR ST_Contains(geom::::geometry, ST_GeomFromGeoJSON(:bounding_box)) OR ST_Overlaps(ST_GeomFromGeoJSON(:bounding_box), geom::::geometry))"
}
viewUnpublished, err := user.HavePermissions(tx, "manage all databases")
if err != nil {
userSqlError(w, err)
return
}
if params.Active || !viewUnpublished {
q += " AND active = 't'"
}
stmt, err := db.DB.PrepareNamed(q)
if err != nil {
userSqlError(w, err)
_ = tx.Rollback()
return
}
err = stmt.Select(&chronologies, params)
if err != nil {
userSqlError(w, err)
_ = tx.Rollback()
return
}
// load all root chronologies
for _, chrono := range chronologies {
chrono.Chronology.Id = chrono.Chronology_root.Root_chronology_id
err = chrono.Chronology.Get(tx)
if err != nil {
userSqlError(w, err)
_ = tx.Rollback()
return
}
// load translations
tr := []model.Chronology_tr{}
err = tx.Select(&tr, "SELECT * FROM chronology_tr WHERE chronology_id = "+strconv.Itoa(chrono.Chronology.Id))
if err != nil {
userSqlError(w, err)
_ = tx.Rollback()
return
}
chrono.Name = model.MapSqlTranslations(tr, "Lang_isocode", "Name")
chrono.Description = model.MapSqlTranslations(tr, "Lang_isocode", "Description")
// get the author user
chrono.Author.Id = chrono.Chronology_root.Author_user_id
err = chrono.Author.Get(tx)
chrono.Author.Password = ""
if err != nil {
userSqlError(w, err)
_ = tx.Rollback()
return
}
// check if chronology is in requested date bounds
if params.Check_dates {
if chrono.Start_date >= params.Start_date && chrono.End_date <= params.End_date {
returnedChronologies = append(returnedChronologies, chrono)
}
} else {
returnedChronologies = append(returnedChronologies, chrono)
}
}
// commit...
err = tx.Commit()
if err != nil {
log.Println("commit failed")
_ = tx.Rollback()
return
}
j, _ := json.Marshal(returnedChronologies)
w.Write(j)
}
// DatabaseList returns the list of databases
func DatabaseList(w http.ResponseWriter, r *http.Request, proute routes.Proute) {
params := proute.Params.(*DatabaseListParams)
tx, err := db.DB.Beginx()
if err != nil {
userSqlError(w, err)
return
}
_user, _ := proute.Session.Get("user")
user := _user.(model.User)
q := "SELECT d.*, ST_AsGeoJSON(d.geographical_extent_geom) as geographical_extent_geom, l.name as license, (SELECT count(*) from site WHERE database_id = d.id) AS number_of_sites, (SELECT number_of_lines FROM import WHERE database_id = d.id ORDER BY id DESC LIMIT 1) AS number_of_lines, u.firstname || ' ' || u.lastname as author FROM \"database\" d LEFT JOIN \"user\" u ON d.owner = u.id LEFT JOIN license l ON l.id = d.license_id WHERE 1 = 1"
type dbInfos struct {
model.Database
Author string `json:"author"`
Description map[string]string `json:"description"`
Geographical_limit map[string]string `json:"geographical_limit"`
Bibliography map[string]string `json:"bibliography"`
Context_description map[string]string `json:"context_description"`
Source_description map[string]string `json:"source_description"`
Source_relation map[string]string `json:"source_relation"`
Copyright map[string]string `json:"copyright"`
Subject map[string]string `json:"subject"`
Number_of_lines int `json:"number_of_lines"`
Number_of_sites int `json:"number_of_sites"`
License string `json:"license"`
Contexts []string `json:"context"`
Countries []struct {
Id int `json:"id"`
Name string `json:"name"`
} `json:"countries"`
Continents []struct {
Id int `json:"id"`
Name string `json:"name"`
} `json:"continents"`
Authors []struct {
Id int `json:"id"`
Fullname string `json:"fullname"`
} `json:"authors"`
}
if params.Bounding_box != "" {
q += " AND (ST_Contains(ST_GeomFromGeoJSON(:bounding_box), geographical_extent_geom::::geometry) OR ST_Contains(geographical_extent_geom::::geometry, ST_GeomFromGeoJSON(:bounding_box)) OR ST_Overlaps(ST_GeomFromGeoJSON(:bounding_box), geographical_extent_geom::::geometry))"
}
if params.Check_dates {
q += " AND ((d.start_date = " + strconv.Itoa(math.MinInt32) + " OR d.start_date >= :start_date) AND (d.end_date = " + strconv.Itoa(math.MaxInt32) + " OR d.end_date <= :end_date))"
}
viewUnpublished, err := user.HavePermissions(tx, "manage all databases")
if err != nil {
userSqlError(w, err)
return
}
if !viewUnpublished {
q += " AND published = 't' OR d.owner = " + strconv.Itoa(user.Id)
}
q += " ORDER BY d.Id DESC"
// fmt.Println(q)
databases := []dbInfos{}
nstmt, err := tx.PrepareNamed(q)
if err != nil {
err = errors.New("rest.databases::DatabaseList : (infos) " + err.Error())
log.Println(err)
userSqlError(w, err)
tx.Rollback()
return
}
err = nstmt.Select(&databases, params)
if err != nil {
err = errors.New("rest.databases::DatabaseList : (infos) " + err.Error())
log.Println(err)
userSqlError(w, err)
tx.Rollback()
return
}
returnedDatabases := []dbInfos{}
for _, database := range databases {
// Authors
astmt, err2 := tx.PrepareNamed("SELECT id, firstname || ' ' || lastname as fullname FROM \"user\" u LEFT JOIN database__authors da ON u.id = da.user_id WHERE da.database_id = :id")
if err2 != nil {
err = errors.New("rest.databases::DatabaseList (authors) : " + err2.Error())
log.Println(err)
userSqlError(w, err)
tx.Rollback()
return
}
err = astmt.Select(&database.Authors, database)
if err != nil {
err = errors.New("rest.databases::DatabaseList (authors) : " + err.Error())
log.Println(err)
userSqlError(w, err)
tx.Rollback()
return
}
// Contexts
cstmt, err3 := tx.PrepareNamed("SELECT context FROM database_context WHERE database_id = :id")
if err3 != nil {
err = errors.New("rest.databases::DatabaseList (contexts) : " + err3.Error())
log.Println(err)
userSqlError(w, err)
tx.Rollback()
return
}
err = cstmt.Select(&database.Contexts, database)
// Countries
if database.Geographical_extent == "country" {
coustmt, err4 := tx.Preparex("SELECT ctr.name FROM country_tr ctr LEFT JOIN country c ON ctr.country_geonameid = c.geonameid LEFT JOIN database__country dc ON c.geonameid = dc.country_geonameid WHERE dc.database_id = $1 AND ctr.lang_isocode = $2")
if err4 != nil {
err = errors.New("rest.databases::DatabaseList (countries) : " + err4.Error())
log.Println(err)
userSqlError(w, err)
tx.Rollback()
return
}
err = coustmt.Select(&database.Countries, database.Id, user.First_lang_isocode)
}
// Continents
if database.Geographical_extent == "continent" {
constmt, err5 := tx.Preparex("SELECT ctr.name FROM continent_tr ctr LEFT JOIN continent c ON ctr.continent_geonameid = c.geonameid LEFT JOIN database__continent dc ON c.geonameid = dc.continent_geonameid WHERE dc.database_id = $1 AND ctr.lang_isocode = $2")
if err5 != nil {
err = errors.New("rest.databases::DatabaseList : (continents) " + err5.Error())
log.Println(err)
userSqlError(w, err)
tx.Rollback()
return
}
err = constmt.Select(&database.Continents, database.Id, user.First_lang_isocode)
}
tr := []model.Database_tr{}
err = tx.Select(&tr, "SELECT * FROM database_tr WHERE database_id = "+strconv.Itoa(database.Id))
if err != nil {
log.Println(err)
_ = tx.Rollback()
userSqlError(w, err)
return
}
database.Description = model.MapSqlTranslations(tr, "Lang_isocode", "Description")
database.Geographical_limit = model.MapSqlTranslations(tr, "Lang_isocode", "Geographical_limit")
database.Bibliography = model.MapSqlTranslations(tr, "Lang_isocode", "Bibliography")
database.Context_description = model.MapSqlTranslations(tr, "Lang_isocode", "Context_description")
database.Source_description = model.MapSqlTranslations(tr, "Lang_isocode", "Source_description")
database.Source_relation = model.MapSqlTranslations(tr, "Lang_isocode", "Source_relation")
database.Copyright = model.MapSqlTranslations(tr, "Lang_isocode", "Copyright")
database.Subject = model.MapSqlTranslations(tr, "Lang_isocode", "Subject")
returnedDatabases = append(returnedDatabases, database)
}
if err != nil {
log.Println(err)
tx.Rollback()
userSqlError(w, err)
return
}
err = tx.Commit()
if err != nil {
log.Println(err)
userSqlError(w, err)
return
}
l, _ := json.Marshal(returnedDatabases)
w.Header().Set("Content-Type", "application/json")
w.Write(l)
}
