Example #1
0
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
}
Example #2
0
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
}
Example #3
0
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
}
Example #4
0
// 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)
}
Example #5
0
// 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)
}
Example #6
0
// 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)
}