D-Net

package eu.dnetlib.organizations.repository.readonly;

import org.springframework.stereotype.Repository;

import eu.dnetlib.organizations.model.view.UserView;

@Repository

public interface UserViewRepository extends ReadOnlyRepository<UserView, String> {

}

package eu.dnetlib.organizations.model.view;

import java.io.Serializable;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.Id;

import javax.persistence.Table;

import org.hibernate.annotations.Type;

import org.hibernate.annotations.TypeDef;

import org.hibernate.annotations.TypeDefs;

import com.vladmihalcea.hibernate.type.array.StringArrayType;

@Entity

@Table(name = "users_view")

@TypeDefs({

		@TypeDef(name = "string-array", typeClass = StringArrayType.class)

})

public class UserView implements Serializable {

	/**

	 *

	 */

	private static final long serialVersionUID = -3308680880727895075L;

	@Id

	@Column(name = "email")

	private String email;

	@Column(name = "valid")

	private boolean valid;

	@Column(name = "role")

	private String role;

	@Type(type = "string-array")

	@Column(name = "countries", columnDefinition = "text[]")

	private String[] countries;

	public String getEmail() {

		return email;

	}

	public void setEmail(final String email) {

		this.email = email;

	}

	public boolean isValid() {

		return valid;

	}

	public void setValid(final boolean valid) {

		this.valid = valid;

	}

	public String getRole() {

		return role;

	}

	public void setRole(final String role) {

		this.role = role;

	}

	public String[] getCountries() {

		return countries;

	}

	public void setCountries(final String[] countries) {

		this.countries = countries;

	}

}

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.PostMapping;

import eu.dnetlib.organizations.model.view.UserView;

import eu.dnetlib.organizations.repository.readonly.UserViewRepository;

	@Autowired

	private UserViewRepository userViewRepository;

	@PostMapping(value = "/public_api/newUser")

	public Map<String, Integer> newUser(final @RequestParam String email) {

	@GetMapping("/api/users")

	public Iterable<UserView> users() {

		return userViewRepository.findAll();

	}

		return "/home";

DROP VIEW users_view;

CREATE VIEW	users_view AS SELECT

	u.email,

	u.valid,

	u.role,

	array_agg(uc.country) AS countries

FROM

	users u

	LEFT OUTER JOIN user_countries uc ON (u.email = uc.email)

GROUP BY u.email, u.valid, u.role

ORDER BY u.email;

<table class="table table-sm table-hover col-sm-12 col-md-10 col-lg-8">

	<thead class="thead-light">

		<tr class="d-flex">

			<th class="col-4">User</th>

			<th class="col-2 text-center">Valid</th>

			<th class="col-2 text-center">Role</th>

			<th class="col-3">Countries</th>

			<th class="col-1"></th>

		</tr>

	</thead>

	<tbody>

		<tr ng-repeat="u in users" class="d-flex">

			<td class="col-4">{{u.email}}</td>

			<td class="col-2 text-center">{{u.valid}}</td>

			<td class="col-2 text-center">{{u.role}}</td>

			<td class="col-3">{{u.countries}}</td>

			<td class="col-1 text-right"><button type="button" class="btn btn-sm  btn-outline-danger"><i class="fa fa-trash"></i></button></td>

		</tr>

	</tbody>

</table>

		.when('/users',                            { templateUrl: 'resources/html/users.html',        controller: 'usersCtrl' })

orgsModule.controller('usersCtrl', function ($scope, $http) {

	$scope.users = [];

	$scope.vocabularies = {};

	$http.get('api/vocabularies').then(function successCallback(res) {

		$scope.vocabularies = res.data;

		$http.get('api/users').then(function successCallback(res) {

			$scope.users = res.data;

		}, function errorCallback(res) {

			alert('ERROR: ' + res.data.error + ' (' + res.data.message + ')');

		});

	}, function errorCallback(res) {

		alert('ERROR: ' + res.data.error + ' (' + res.data.message + ')');

	});

});

		<nav class="navbar navbar-expand-lg navbar-dark bg-primary">

	<script src="resources/js/jquery-3.4.1.min.js"></script>

	<script src="resources/js/popper.min.js"></script>

		<nav class="navbar navbar-expand-lg navbar-dark bg-primary">

			<a class="navbar-brand" href="#">

				<img src="resources/images/openaire_logo_small.png" width="30" height="30" alt="OpenOrgs Database"> OpenOrgs Database

<!doctype html>

<html lang="en"	xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org" xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity4">

<head>

<!-- Required meta tags -->

<meta charset="utf-8">

<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

<meta http-equiv="Cache-Control" content="no-cache, no-store, must-revalidate">

<meta http-equiv="Pragma" content="no-cache">

<meta http-equiv="Expires" content="0">

<!-- Bootstrap CSS -->

<link rel="stylesheet" href="resources/css/bootstrap.min.css" />

<!-- Icons CSS -->

<link rel="stylesheet" href="resources/css/fontawesome-all.min.css">

<style type="text/css">

.table > tbody > tr > td {

     vertical-align: middle;

}

.card > .table {

	margin-bottom: 0 !important;

}

fieldset > legend {

	font-size: 1.2rem !important;  

}

</style>

<title>Organizations Database</title>

</head>

<body ng-app="orgs" sec:authorize="isAuthenticated()">

	<nav class="navbar navbar-expand-lg navbar-dark bg-primary">

		<a class="navbar-brand" href="#"> <img

			src="resources/images/openaire_logo_small.png" width="30" height="30" alt="OpenOrgs Database">

			OpenOrgs Database

		</a>

		<button class="navbar-toggler" type="button" data-toggle="collapse"

			data-target="#navbarSupportedContent">

			<span class="navbar-toggler-icon"></span>

		</button>

		<div class="collapse navbar-collapse w-100 order-1" id="navbarSupportedContent">

			<ul class="navbar-nav mr-auto">

				<li class="nav-item active"><a class="nav-link" href="#">Search</a></li>

				<li class="nav-item dropdown">

					<a class="nav-link dropdown-toggle" href="javascript:void(0)" data-toggle="dropdown">Browse</a>

					<div class="dropdown-menu">

						<a class="dropdown-item" href="#!/countries">by country</a> <a

							class="dropdown-item" href="#!/types">by type</a>

					</div>

				</li>

				<li class="nav-item"><a class="nav-link" href="#!/new">New</a></li>

			</ul>

		</div>

		<div class="navbar-collapse collapse w-100 order-2">

			<ul class="navbar-nav ml-auto">

				<li class="nav-item dropdown" sec:authorize="hasRole('ROLE_SUPERUSER')">

					<a class="nav-link dropdown-toggle" href="javascript:void(0)" data-toggle="dropdown"><i class="fa fa-cog"></i></a>

					<div class="dropdown-menu dropdown-menu-right">

						<a class="dropdown-item" href="#!/users">manage users</a>

					</div>

				</li>

				<li class="nav-item dropdown">

					<a class="nav-link dropdown-toggle" href="javascript:void(0)" data-toggle="dropdown"><i class="fa fa-user"></i></a>

					<div class="dropdown-menu dropdown-menu-right">

						<p class="px-4 pt-2 text-muted small">

							<b>Logged as:</b><br /><span sec:authentication="name"></span>

							<b>Role:</b><br /><span sec:authentication="principal.authorities"></span>

						</p>

						<div class="dropdown-divider"></div>

						<a class="dropdown-item" th:href="@{/logout}">Logout</a>

					</div>

				</li>

				<li class="nav-item"><a class="btn btn-outline-secondary" href="doc">API</a></li>

			</ul>

		</div>

	</nav>

	<div class="container-fluid small mt-4" ng-view></div>

	<script src="resources/js/jquery-3.4.1.min.js"></script>

	<script src="resources/js/popper.min.js"></script>

	<script src="resources/js/bootstrap.min.js"></script>

	<script src="resources/js/angular.min.js"></script>

	<script src="resources/js/angular-route.min.js"></script>

	<script src="resources/js/organizations.js"></script>

</body>

</html>