Revision 57289
Added by Michele Artini over 4 years ago
| modules/dnet-orgs-database-application/trunk/src/main/java/eu/dnetlib/organizations/repository/TypeRepository.java | ||
|---|---|---|
| 1 |
package eu.dnetlib.organizations.repository; |
|
| 2 |
|
|
| 3 |
import org.springframework.data.jpa.repository.JpaRepository; |
|
| 4 |
|
|
| 5 |
import eu.dnetlib.organizations.model.Type; |
|
| 6 |
import eu.dnetlib.organizations.model.TypePK; |
|
| 7 |
|
|
| 8 |
public interface TypeRepository extends JpaRepository<Type, TypePK> {
|
|
| 9 |
|
|
| 10 |
void deleteByOrgId(String orgId); |
|
| 11 |
} |
|
| modules/dnet-orgs-database-application/trunk/src/main/java/eu/dnetlib/organizations/model/TypePK.java | ||
|---|---|---|
| 1 |
package eu.dnetlib.organizations.model; |
|
| 2 |
|
|
| 3 |
import java.io.Serializable; |
|
| 4 |
import java.util.Objects; |
|
| 5 |
|
|
| 6 |
public class TypePK implements Serializable {
|
|
| 7 |
|
|
| 8 |
/** |
|
| 9 |
* |
|
| 10 |
*/ |
|
| 11 |
private static final long serialVersionUID = 7320404773442386747L; |
|
| 12 |
|
|
| 13 |
private String orgId; |
|
| 14 |
|
|
| 15 |
private String type; |
|
| 16 |
|
|
| 17 |
public String getOrgId() {
|
|
| 18 |
return orgId; |
|
| 19 |
} |
|
| 20 |
|
|
| 21 |
public void setOrgId(final String orgId) {
|
|
| 22 |
this.orgId = orgId; |
|
| 23 |
} |
|
| 24 |
|
|
| 25 |
public String getType() {
|
|
| 26 |
return type; |
|
| 27 |
} |
|
| 28 |
|
|
| 29 |
public void setType(final String type) {
|
|
| 30 |
this.type = type; |
|
| 31 |
} |
|
| 32 |
|
|
| 33 |
@Override |
|
| 34 |
public int hashCode() {
|
|
| 35 |
return Objects.hash(orgId, type); |
|
| 36 |
} |
|
| 37 |
|
|
| 38 |
@Override |
|
| 39 |
public boolean equals(final Object obj) {
|
|
| 40 |
if (this == obj) { return true; }
|
|
| 41 |
if (obj == null) { return false; }
|
|
| 42 |
if (!(obj instanceof TypePK)) { return false; }
|
|
| 43 |
final TypePK other = (TypePK) obj; |
|
| 44 |
return Objects.equals(orgId, other.orgId) && Objects.equals(type, other.type); |
|
| 45 |
} |
|
| 46 |
|
|
| 47 |
@Override |
|
| 48 |
public String toString() {
|
|
| 49 |
return String.format("TypePK [orgId=%s, type=%s]", orgId, type);
|
|
| 50 |
} |
|
| 51 |
} |
|
| modules/dnet-orgs-database-application/trunk/src/main/java/eu/dnetlib/organizations/model/Type.java | ||
|---|---|---|
| 1 |
package eu.dnetlib.organizations.model; |
|
| 2 |
|
|
| 3 |
import java.io.Serializable; |
|
| 4 |
|
|
| 5 |
import javax.persistence.Column; |
|
| 6 |
import javax.persistence.Entity; |
|
| 7 |
import javax.persistence.Id; |
|
| 8 |
import javax.persistence.IdClass; |
|
| 9 |
import javax.persistence.Table; |
|
| 10 |
|
|
| 11 |
@Entity |
|
| 12 |
@Table(name = "types") |
|
| 13 |
@IdClass(TypePK.class) |
|
| 14 |
public class Type implements Serializable {
|
|
| 15 |
|
|
| 16 |
/** |
|
| 17 |
* |
|
| 18 |
*/ |
|
| 19 |
private static final long serialVersionUID = -3471104919002595082L; |
|
| 20 |
|
|
| 21 |
@Id |
|
| 22 |
@Column(name = "id") |
|
| 23 |
private String orgId; |
|
| 24 |
|
|
| 25 |
@Id |
|
| 26 |
@Column(name = "type") |
|
| 27 |
private String type; |
|
| 28 |
|
|
| 29 |
public Type() {}
|
|
| 30 |
|
|
| 31 |
public Type(final String orgId, final String type) {
|
|
| 32 |
this.orgId = orgId; |
|
| 33 |
this.type = type; |
|
| 34 |
} |
|
| 35 |
|
|
| 36 |
public String getOrgId() {
|
|
| 37 |
return orgId; |
|
| 38 |
} |
|
| 39 |
|
|
| 40 |
public void setOrgId(final String orgId) {
|
|
| 41 |
this.orgId = orgId; |
|
| 42 |
} |
|
| 43 |
|
|
| 44 |
public String getType() {
|
|
| 45 |
return type; |
|
| 46 |
} |
|
| 47 |
|
|
| 48 |
public void setType(final String type) {
|
|
| 49 |
this.type = type; |
|
| 50 |
} |
|
| 51 |
|
|
| 52 |
} |
|
| modules/dnet-orgs-database-application/trunk/src/main/java/eu/dnetlib/organizations/repository/readonly/OrganizationSimpleViewRepository.java | ||
|---|---|---|
| 31 | 31 |
Page<OrganizationSimpleView> findByCountry(String country, Pageable pageable); |
| 32 | 32 |
|
| 33 | 33 |
// BROWSE BY ORG TYPE |
| 34 |
@Query(value = "select type as value, count(*) as count from types group by type order by count desc", nativeQuery = true)
|
|
| 34 |
@Query(value = "select type as value, count(*) as count from organizations group by type order by count desc", nativeQuery = true)
|
|
| 35 | 35 |
List<BrowseEntry> browseTypes(); |
| 36 | 36 |
|
| 37 | 37 |
// BROWSE BY ORG TYPE FOR USER |
| 38 |
@Query(value = "select t.type as value, count(t.type) as count " |
|
| 39 |
+ "from user_countries uc " |
|
| 40 |
+ "left outer join organizations o on (uc.country = o.country) " |
|
| 41 |
+ "left outer join types t on (o.id = t.id) " |
|
| 42 |
+ "where uc.email=?1 and t.type is not null " |
|
| 43 |
+ "group by t.type " |
|
| 38 |
@Query(value = "select o.type as value, count(o.type) as count " |
|
| 39 |
+ "from organizations o " |
|
| 40 |
+ "left outer join user_countries uc on (uc.country = o.country) " |
|
| 41 |
+ "where uc.email=?1 " |
|
| 42 |
+ "group by o.type " |
|
| 44 | 43 |
+ "order by count desc;", nativeQuery = true) |
| 45 | 44 |
List<BrowseEntry> browseTypesForUser(String email); |
| 46 | 45 |
|
| 47 |
@Query(value = "select * from organizations_simple_view where ?1 = any(types)", countQuery = "select count(*) from types where type = ?1", nativeQuery = true) |
|
| 48 | 46 |
Page<OrganizationSimpleView> findByType(String type, Pageable pageable); |
| 49 | 47 |
|
| 50 |
@Query(value = "select o.* from organizations_simple_view o left outer join user_countries uc on (uc.country = o.country) where uc.email = ?2 and ?1 = any(o.types)", countQuery = "select count(*) from types t left outer join organizations o on (t.id = o .id) left outer join user_countries uc on (uc.country = o .country) where t.type = ?1 and uc.email = ?2", nativeQuery = true)
|
|
| 48 |
@Query(value = "select o.* from organizations_simple_view o left outer join user_countries uc on (uc.country = o.country) where uc.email = ?2 and o.type = ?1", nativeQuery = true)
|
|
| 51 | 49 |
Page<OrganizationSimpleView> findByTypeForUser(String type, String name, Pageable pageable); |
| 52 | 50 |
|
| 53 | 51 |
} |
| modules/dnet-orgs-database-application/trunk/src/main/java/eu/dnetlib/organizations/model/Organization.java | ||
|---|---|---|
| 23 | 23 |
@Column(name = "name") |
| 24 | 24 |
private String name; |
| 25 | 25 |
|
| 26 |
@Column(name = "type") |
|
| 27 |
private String type; |
|
| 28 |
|
|
| 26 | 29 |
@Column(name = "lat") |
| 27 | 30 |
private Double lat; |
| 28 | 31 |
|
| ... | ... | |
| 37 | 40 |
|
| 38 | 41 |
public Organization() {}
|
| 39 | 42 |
|
| 40 |
public Organization(final String id, final String name, final Double lat, final Double lng, final String city, final String country) {
|
|
| 43 |
public Organization(final String id, final String name, final String type, final Double lat, final Double lng, final String city, final String country) {
|
|
| 41 | 44 |
this.id = id; |
| 42 | 45 |
this.name = name; |
| 46 |
this.type = type; |
|
| 43 | 47 |
this.lat = lat; |
| 44 | 48 |
this.lng = lng; |
| 45 | 49 |
this.city = city; |
| ... | ... | |
| 62 | 66 |
this.name = name; |
| 63 | 67 |
} |
| 64 | 68 |
|
| 69 |
public String getType() {
|
|
| 70 |
return type; |
|
| 71 |
} |
|
| 72 |
|
|
| 73 |
public void setType(final String type) {
|
|
| 74 |
this.type = type; |
|
| 75 |
} |
|
| 76 |
|
|
| 65 | 77 |
public Double getLat() {
|
| 66 | 78 |
return lat; |
| 67 | 79 |
} |
| modules/dnet-orgs-database-application/trunk/src/main/java/eu/dnetlib/organizations/model/view/OrganizationView.java | ||
|---|---|---|
| 36 | 36 |
@Column(name = "name") |
| 37 | 37 |
private String name; |
| 38 | 38 |
|
| 39 |
@Column(name = "type") |
|
| 40 |
private String type; |
|
| 41 |
|
|
| 39 | 42 |
@Column(name = "lat") |
| 40 | 43 |
private Double lat; |
| 41 | 44 |
|
| ... | ... | |
| 73 | 76 |
private Set<String> acronyms; |
| 74 | 77 |
|
| 75 | 78 |
@Type(type = "jsonb") |
| 76 |
@Column(name = "types", columnDefinition = "jsonb") |
|
| 77 |
private Set<String> types; |
|
| 78 |
|
|
| 79 |
@Type(type = "jsonb") |
|
| 80 | 79 |
@Column(name = "urls", columnDefinition = "jsonb") |
| 81 | 80 |
private Set<String> urls; |
| 82 | 81 |
|
| ... | ... | |
| 96 | 95 |
this.name = name; |
| 97 | 96 |
} |
| 98 | 97 |
|
| 98 |
public String getType() {
|
|
| 99 |
return type; |
|
| 100 |
} |
|
| 101 |
|
|
| 102 |
public void setType(final String type) {
|
|
| 103 |
this.type = type; |
|
| 104 |
} |
|
| 105 |
|
|
| 99 | 106 |
public Double getLat() {
|
| 100 | 107 |
return lat; |
| 101 | 108 |
} |
| ... | ... | |
| 184 | 191 |
this.acronyms = acronyms; |
| 185 | 192 |
} |
| 186 | 193 |
|
| 187 |
public Set<String> getTypes() {
|
|
| 188 |
return types; |
|
| 189 |
} |
|
| 190 |
|
|
| 191 |
public void setTypes(final Set<String> types) {
|
|
| 192 |
this.types = types; |
|
| 193 |
} |
|
| 194 |
|
|
| 195 | 194 |
public Set<String> getUrls() {
|
| 196 | 195 |
return urls; |
| 197 | 196 |
} |
| modules/dnet-orgs-database-application/trunk/src/main/java/eu/dnetlib/organizations/model/view/OrganizationSimpleView.java | ||
|---|---|---|
| 32 | 32 |
@Column(name = "name") |
| 33 | 33 |
private String name; |
| 34 | 34 |
|
| 35 |
@Column(name = "type") |
|
| 36 |
private String type; |
|
| 37 |
|
|
| 35 | 38 |
@Column(name = "city") |
| 36 | 39 |
private String city; |
| 37 | 40 |
|
| ... | ... | |
| 42 | 45 |
@Column(name = "acronyms", columnDefinition = "text[]") |
| 43 | 46 |
private String[] acronyms; |
| 44 | 47 |
|
| 45 |
@Type(type = "string-array") |
|
| 46 |
@Column(name = "types", columnDefinition = "text[]") |
|
| 47 |
private String[] types; |
|
| 48 |
|
|
| 49 | 48 |
public String getId() {
|
| 50 | 49 |
return id; |
| 51 | 50 |
} |
| ... | ... | |
| 62 | 61 |
this.name = name; |
| 63 | 62 |
} |
| 64 | 63 |
|
| 64 |
public String getType() {
|
|
| 65 |
return type; |
|
| 66 |
} |
|
| 67 |
|
|
| 68 |
public void setType(final String type) {
|
|
| 69 |
this.type = type; |
|
| 70 |
} |
|
| 71 |
|
|
| 65 | 72 |
public String getCity() {
|
| 66 | 73 |
return city; |
| 67 | 74 |
} |
| ... | ... | |
| 86 | 93 |
this.acronyms = acronyms; |
| 87 | 94 |
} |
| 88 | 95 |
|
| 89 |
public String[] getTypes() {
|
|
| 90 |
return types; |
|
| 91 |
} |
|
| 92 |
|
|
| 93 |
public void setTypes(final String[] types) {
|
|
| 94 |
this.types = types; |
|
| 95 |
} |
|
| 96 |
|
|
| 97 | 96 |
} |
| modules/dnet-orgs-database-application/trunk/src/main/java/eu/dnetlib/organizations/utils/DatabaseUtils.java | ||
|---|---|---|
| 17 | 17 |
import eu.dnetlib.organizations.model.OtherIdentifier; |
| 18 | 18 |
import eu.dnetlib.organizations.model.OtherName; |
| 19 | 19 |
import eu.dnetlib.organizations.model.Relationship; |
| 20 |
import eu.dnetlib.organizations.model.Type; |
|
| 21 | 20 |
import eu.dnetlib.organizations.model.Url; |
| 22 | 21 |
import eu.dnetlib.organizations.model.view.OrganizationView; |
| 23 | 22 |
import eu.dnetlib.organizations.repository.AcronymRepository; |
| ... | ... | |
| 25 | 24 |
import eu.dnetlib.organizations.repository.OtherIdentifierRepository; |
| 26 | 25 |
import eu.dnetlib.organizations.repository.OtherNameRepository; |
| 27 | 26 |
import eu.dnetlib.organizations.repository.RelationshipRepository; |
| 28 |
import eu.dnetlib.organizations.repository.TypeRepository; |
|
| 29 | 27 |
import eu.dnetlib.organizations.repository.UrlRepository; |
| 30 | 28 |
|
| 31 | 29 |
@Component |
| ... | ... | |
| 40 | 38 |
@Autowired |
| 41 | 39 |
private OtherNameRepository otherNameRepository; |
| 42 | 40 |
@Autowired |
| 43 |
private TypeRepository typeRepository; |
|
| 44 |
@Autowired |
|
| 45 | 41 |
private UrlRepository urlRepository; |
| 46 | 42 |
@Autowired |
| 47 | 43 |
private RelationshipRepository relationshipRepository; |
| ... | ... | |
| 63 | 59 |
acronymRepository.deleteByOrgId(orgId); |
| 64 | 60 |
otherNameRepository.deleteByOrgId(orgId); |
| 65 | 61 |
otherIdentifierRepository.deleteByOrgId(orgId); |
| 66 |
typeRepository.deleteByOrgId(orgId); |
|
| 67 | 62 |
urlRepository.deleteByOrgId(orgId); |
| 68 | 63 |
} |
| 69 | 64 |
|
| 70 |
organizationRepository.save(new Organization(orgId, org.getName(), org.getLat(), org.getLng(), org.getCity(), org.getCountry())); |
|
| 65 |
organizationRepository.save(new Organization(orgId, org.getName(), org.getType(), org.getLat(), org.getLng(), org.getCity(), org.getCountry()));
|
|
| 71 | 66 |
org.getAcronyms().forEach(s -> acronymRepository.save(new Acronym(orgId, s))); |
| 72 | 67 |
org.getOtherNames().forEach(n -> otherNameRepository.save(new OtherName(orgId, n.getName(), n.getLang()))); |
| 73 | 68 |
org.getOtherIdentifiers().forEach(id -> otherIdentifierRepository.save(new OtherIdentifier(orgId, id.getId(), id.getType()))); |
| 74 |
org.getTypes().forEach(t -> typeRepository.save(new Type(orgId, t))); |
|
| 75 | 69 |
org.getUrls().forEach(u -> urlRepository.save(new Url(orgId, u))); |
| 76 | 70 |
|
| 77 | 71 |
if (update) {
|
| modules/dnet-orgs-database-application/trunk/src/main/resources/sql/import_grid_ac.sql | ||
|---|---|---|
| 90 | 90 |
COPY grid_aliases (grid_id,alias) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/aliases.csv' CSV HEADER; |
| 91 | 91 |
|
| 92 | 92 |
|
| 93 |
INSERT INTO organizations(id, name, lat, lng, city, country, created_by, modified_by) (SELECT 'openorgs____::'||md5(o.grid_id), o.name, a.lat, a.lng, a.city, a.country_code, 'import:grid.ac', 'import:grid.ac' FROM grid_institutes o LEFT OUTER JOIN grid_addresses a ON (o.grid_id=a.grid_id)) ON CONFLICT DO NOTHING;
|
|
| 93 |
INSERT INTO organizations(id, name, type, lat, lng, city, country, created_by, modified_by) (SELECT 'openorgs____::'||md5(o.grid_id), o.name, COALESCE(t.type, 'Other'), a.lat, a.lng, a.city, a.country_code, 'import:grid.ac', 'import:grid.ac' FROM grid_institutes o LEFT OUTER JOIN grid_addresses a ON (o.grid_id=a.grid_id) LEFT OUTER JOIN grid_types t ON (o.grid_id=t.grid_id)) ON CONFLICT DO NOTHING;
|
|
| 94 | 94 |
INSERT INTO other_ids (id, otherid, type) (SELECT 'openorgs____::'||md5(grid_id), grid_id, 'grid.ac' FROM grid_institutes ) ON CONFLICT DO NOTHING; |
| 95 | 95 |
INSERT INTO other_ids (id, otherid, type) (SELECT 'openorgs____::'||md5(grid_id), external_id, external_id_type FROM grid_external_ids ) ON CONFLICT DO NOTHING; |
| 96 | 96 |
INSERT INTO other_names (id, lang, name) (SELECT 'openorgs____::'||md5(grid_id), 'en', name FROM grid_institutes ) ON CONFLICT DO NOTHING; |
| ... | ... | |
| 98 | 98 |
INSERT INTO other_names (id, lang, name) (SELECT 'openorgs____::'||md5(grid_id), 'UNKNOWN', alias FROM grid_aliases ) ON CONFLICT DO NOTHING; |
| 99 | 99 |
INSERT INTO acronyms (id, acronym) (SELECT 'openorgs____::'||md5(grid_id), acronym FROM grid_acronyms ) ON CONFLICT DO NOTHING; |
| 100 | 100 |
INSERT INTO relationships(id1, reltype, id2) (SELECT 'openorgs____::'||md5(grid_id), relationship_type, 'openorgs____::'||md5(related_grid_id) FROM grid_relationships) ON CONFLICT DO NOTHING; |
| 101 |
INSERT INTO types (id, type) (SELECT 'openorgs____::'||md5(grid_id), type FROM grid_types ) ON CONFLICT DO NOTHING; |
|
| 102 | 101 |
INSERT INTO urls (id, url) (SELECT 'openorgs____::'||md5(grid_id), link FROM grid_links ) ON CONFLICT DO NOTHING; |
| 103 | 102 |
|
| modules/dnet-orgs-database-application/trunk/src/main/resources/sql/schema.sql | ||
|---|---|---|
| 1 | 1 |
DROP VIEW organizations_view; |
| 2 | 2 |
DROP VIEW organizations_simple_view; |
| 3 | 3 |
|
| 4 |
|
|
| 5 |
DROP TABLE IF EXISTS user_roles; |
|
| 6 |
DROP TABLE IF EXISTS user_countries; |
|
| 7 |
DROP TABLE IF EXISTS users; |
|
| 8 | 4 |
DROP TABLE IF EXISTS other_ids; |
| 9 | 5 |
DROP TABLE IF EXISTS other_names; |
| 10 | 6 |
DROP TABLE IF EXISTS acronyms; |
| 11 | 7 |
DROP TABLE IF EXISTS relationships; |
| 12 |
DROP TABLE IF EXISTS types; |
|
| 13 | 8 |
DROP TABLE IF EXISTS urls; |
| 14 | 9 |
DROP TABLE IF EXISTS openaire_simrels; |
| 15 | 10 |
DROP TABLE IF EXISTS organizations; |
| 16 | 11 |
DROP TABLE IF EXISTS org_types; |
| 12 |
|
|
| 13 |
DROP TABLE IF EXISTS user_countries; |
|
| 14 |
DROP TABLE IF EXISTS users; |
|
| 15 |
|
|
| 16 |
DROP TABLE IF EXISTS user_roles; |
|
| 17 |
DROP TABLE IF EXISTS countries; |
|
| 17 | 18 |
DROP TABLE IF EXISTS id_types; |
| 18 | 19 |
DROP TABLE IF EXISTS languages; |
| 19 |
DROP TABLE IF EXISTS countries; |
|
| 20 | 20 |
|
| 21 | 21 |
|
| 22 |
|
|
| 22 | 23 |
CREATE TABLE org_types (val text PRIMARY KEY); |
| 23 | 24 |
INSERT INTO org_types VALUES ('Archive'), ('Company'), ('Education'), ('Facility'), ('Government'), ('Healthcare'), ('Nonprofit'), ('Other');
|
| 24 | 25 |
|
| ... | ... | |
| 63 | 64 |
CREATE TABLE organizations ( |
| 64 | 65 |
id text PRIMARY KEY, |
| 65 | 66 |
name text, |
| 67 |
type text NOT NULL default 'Other' REFERENCES org_types(val), |
|
| 66 | 68 |
lat double precision, |
| 67 | 69 |
lng double precision, |
| 68 | 70 |
city text, |
| ... | ... | |
| 100 | 102 |
PRIMARY KEY (id1, reltype, id2) |
| 101 | 103 |
); |
| 102 | 104 |
|
| 103 |
CREATE TABLE types ( |
|
| 104 |
id text REFERENCES organizations(id), |
|
| 105 |
type text REFERENCES org_types(val), |
|
| 106 |
PRIMARY KEY (id, type) |
|
| 107 |
); |
|
| 108 |
|
|
| 109 | 105 |
CREATE TABLE urls ( |
| 110 | 106 |
id text REFERENCES organizations(id), |
| 111 | 107 |
url text, |
| ... | ... | |
| 128 | 124 |
CREATE VIEW organizations_view AS SELECT |
| 129 | 125 |
org.id, |
| 130 | 126 |
org.name, |
| 127 |
org.type, |
|
| 131 | 128 |
org.lat, |
| 132 | 129 |
org.lng, |
| 133 | 130 |
org.city, |
| ... | ... | |
| 139 | 136 |
COALESCE(jsonb_agg(DISTINCT jsonb_build_object('id', oid.otherid, 'type', oid.type)) FILTER (WHERE oid.otherid IS NOT NULL), '[]') AS other_ids,
|
| 140 | 137 |
COALESCE(jsonb_agg(DISTINCT jsonb_build_object('name', n.name, 'lang', n.lang)) FILTER (WHERE n.name IS NOT NULL), '[]') AS other_names,
|
| 141 | 138 |
COALESCE(jsonb_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL), '[]') AS acronyms, |
| 142 |
COALESCE(jsonb_agg(DISTINCT t.type) FILTER (WHERE t.type IS NOT NULL), '[]') AS types, |
|
| 143 | 139 |
COALESCE(jsonb_agg(DISTINCT u.url) FILTER (WHERE u.url IS NOT NULL), '[]') AS urls |
| 144 | 140 |
FROM |
| 145 | 141 |
organizations org |
| 146 | 142 |
LEFT OUTER JOIN other_ids oid ON (org.id = oid.id) |
| 147 | 143 |
LEFT OUTER JOIN other_names n ON (org.id = n.id) |
| 148 | 144 |
LEFT OUTER JOIN acronyms a ON (org.id = a.id) |
| 149 |
LEFT OUTER JOIN types t ON (org.id = t.id) |
|
| 150 | 145 |
LEFT OUTER JOIN urls u ON (org.id = u.id) |
| 151 | 146 |
GROUP BY |
| 152 | 147 |
org.id, |
| 153 | 148 |
org.name, |
| 149 |
org.type, |
|
| 154 | 150 |
org.lat, |
| 155 | 151 |
org.lng, |
| 156 | 152 |
org.city, |
| ... | ... | |
| 163 | 159 |
CREATE VIEW organizations_simple_view AS SELECT |
| 164 | 160 |
org.id, |
| 165 | 161 |
org.name, |
| 162 |
org.type, |
|
| 166 | 163 |
org.city, |
| 167 | 164 |
org.country, |
| 168 |
array_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL) AS acronyms, |
|
| 169 |
array_agg(DISTINCT t.type) FILTER (WHERE t.type IS NOT NULL) AS types |
|
| 165 |
array_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL) AS acronyms |
|
| 170 | 166 |
FROM |
| 171 | 167 |
organizations org |
| 172 | 168 |
LEFT OUTER JOIN acronyms a ON (org.id = a.id) |
| 173 |
LEFT OUTER JOIN types t ON (org.id = t.id) |
|
| 174 | 169 |
GROUP BY |
| 175 | 170 |
org.id, |
| 176 | 171 |
org.name, |
| 172 |
org.type, |
|
| 177 | 173 |
org.city, |
| 178 | 174 |
org.country; |
| 179 | 175 |
|
| modules/dnet-orgs-database-application/trunk/src/main/resources/static/resources/html/edit.html | ||
|---|---|---|
| 23 | 23 |
<div class="card-body"> |
| 24 | 24 |
<form> |
| 25 | 25 |
<fieldset> |
| 26 |
<legend>Organization name</legend>
|
|
| 26 |
<legend>Official name and type</legend>
|
|
| 27 | 27 |
|
| 28 | 28 |
<div class="form-group"> |
| 29 | 29 |
<div class="input-group input-group-sm"> |
| ... | ... | |
| 31 | 31 |
<div class="input-group-text">name</div> |
| 32 | 32 |
</div> |
| 33 | 33 |
<input type="text" class="form-control" placeholder="name" ng-model="org.name" /> |
| 34 |
<div class="input-group-append input-group-prepend"> |
|
| 35 |
<div class="input-group-text">type</div> |
|
| 36 |
</div> |
|
| 37 |
<select class="custom-select" ng-model="org.type"> |
|
| 38 |
<option disabled="disabled" value=''>type...</option> |
|
| 39 |
<option ng-repeat="t in vocabularies.orgTypes">{{t}}</option>
|
|
| 40 |
</select> |
|
| 34 | 41 |
</div> |
| 35 | 42 |
</div> |
| 36 | 43 |
</fieldset> |
| ... | ... | |
| 63 | 70 |
</fieldset> |
| 64 | 71 |
|
| 65 | 72 |
<fieldset class="mt-4"> |
| 66 |
<legend>Organization type</legend> |
|
| 67 |
<div class="form-group row"> |
|
| 68 |
<div class="col-lg-6"> |
|
| 69 |
<div class="card"> |
|
| 70 |
<table class="table table-sm"> |
|
| 71 |
<tbody> |
|
| 72 |
<tr ng-repeat="t in org.types"> |
|
| 73 |
<td>{{t}}</td>
|
|
| 74 |
<td class="text-right"> |
|
| 75 |
<button type="button" class="btn btn-sm btn-outline-danger" ng-click="org.types.splice($index, 1)">delete</button> |
|
| 76 |
</td> |
|
| 77 |
</tr> |
|
| 78 |
</tbody> |
|
| 79 |
</table> |
|
| 80 |
<div class="card-footer"> |
|
| 81 |
<div class="input-group input-group-sm" ng-init="newType=''"> |
|
| 82 |
<select class="custom-select" ng-model="newType"> |
|
| 83 |
<option disabled="disabled" value=''>new type...</option> |
|
| 84 |
<option ng-repeat="t in vocabularies.orgTypes" ng-disabled="org.types.indexOf(t) !== -1">{{t}}</option>
|
|
| 85 |
</select> |
|
| 86 |
<div class="input-group-append"> |
|
| 87 |
<button type="button" class="btn btn-outline-success" ng-click="org.types.push(newType); newType=''" ng-disabled="!newType">add</button> |
|
| 88 |
</div> |
|
| 89 |
</div> |
|
| 90 |
</div> |
|
| 91 |
</div> |
|
| 92 |
</div> |
|
| 93 |
</div> |
|
| 94 |
</fieldset> |
|
| 95 |
|
|
| 96 |
|
|
| 97 |
<fieldset class="mt-4"> |
|
| 98 | 73 |
<legend>Other names and identifiers</legend> |
| 99 | 74 |
|
| 100 | 75 |
<div class="form-group row"> |
Also available in: Unified diff
type