2 files changed, 39 insertions, 11 deletions

diff --git a/src/main/java/DNPM/services/DefaultStudienService.java b/src/main/java/DNPM/services/DefaultStudienService.java
index 2994917..9f2ca05 100644
--- a/src/main/java/DNPM/services/DefaultStudienService.java
+++ b/src/main/java/DNPM/services/DefaultStudienService.java
@@ -17,31 +17,28 @@ public class DefaultStudienService implements StudienService {
 
     @Override
     public List<Studie> findAll() {
-        var sql = "SELECT property_catalogue_version.version_number, pcve.code, pcve.shortdesc, pcve.description FROM property_catalogue "
+        var sql = "SELECT property_catalogue_version.version_number, studie.studien_nummer, pcve.code, pcve.shortdesc, pcve.description FROM property_catalogue "
         + "JOIN property_catalogue_version ON property_catalogue.id = property_catalogue_version.datacatalog_id "
         + "JOIN property_catalogue_version_entry pcve ON property_catalogue_version.id = pcve.property_version_id "
-        + "JOIN studie ON studie.property_version_entry = pcve.id "
-        + "WHERE property_catalogue.name = 'OS.Studien' "
-        + "AND studie.aktiv;";
+        + "LEFT JOIN studie ON pcve.id = studie.property_version_entry "
+        + "WHERE property_catalogue.name = 'OS.Studien' AND s.aktiv;";
 
         return this.jdbcTemplate.query(sql, (resultSet, i) -> new Studie(
                 resultSet.getString(1),
                 resultSet.getString(2),
                 resultSet.getString(3),
+                resultSet.getString(4),
                 resultSet.getInt(0)
         ));
     }
 
     @Override
     public List<Studie> findByQuery(String query) {
-        var sql = "SELECT property_catalogue_version.version_number, pcve.code, pcve.shortdesc, pcve.description FROM property_catalogue "
+        var sql = "SELECT property_catalogue_version.version_number, studie.studien_nummer, pcve.code, pcve.shortdesc, pcve.description FROM property_catalogue "
                 + "JOIN property_catalogue_version ON property_catalogue.id = property_catalogue_version.datacatalog_id "
                 + "JOIN property_catalogue_version_entry pcve ON property_catalogue_version.id = pcve.property_version_id "
-                + "JOIN studie ON studie.property_version_entry = pcve.id "
-                + "WHERE property_catalogue.name = 'OS.Studien' "
-                + "AND studie.aktiv "
-                + "AND (pcve.shortdesc LIKE ? "
-                + "OR pcve.description LIKE ?);";
+                + "LEFT JOIN studie ON pcve.id = studie.property_version_entry "
+                + "WHERE property_catalogue.name = 'OS.Studien' AND studie.aktiv AND (pcve.shortdesc LIKE ? OR pcve.description LIKE ?);";
 
         var like = String.format("%%%s%%", query);
 
@@ -49,6 +46,7 @@ public class DefaultStudienService implements StudienService {
                 resultSet.getString(1),
                 resultSet.getString(2),
                 resultSet.getString(3),
+                resultSet.getString(4),
                 resultSet.getInt(0)
         ));
     }
diff --git a/src/main/java/DNPM/services/Studie.java b/src/main/java/DNPM/services/Studie.java
index d0d674a..9e487e1 100644
--- a/src/main/java/DNPM/services/Studie.java
+++ b/src/main/java/DNPM/services/Studie.java
@@ -2,12 +2,14 @@ package DNPM.services;
 
 public class Studie {
     private final String code;
+    private final String studiennummer;
     private final String shortDesc;
     private final String description;
     private final int version;
 
-    public Studie(final String code, final String shortDesc, final String description, final int version) {
+    public Studie(final String code, final String studiennummer, final String shortDesc, final String description, final int version) {
         this.code = code;
+        this.studiennummer = studiennummer;
         this.shortDesc = shortDesc;
         this.description = description;
         this.version = version;
@@ -17,6 +19,10 @@ public class Studie {
         return code;
     }
 
+    public String getStudiennummer() {
+        return studiennummer;
+    }
+
     public String getShortDesc() {
         return shortDesc;
     }
@@ -28,4 +34,28 @@ public class Studie {
     public int getVersion() {
         return version;
     }
+
+    public Type getType() {
+        if (this.hasNctNumber()) {
+            return Type.NCT;
+        } else if (this.hasEudraCtNumber()) {
+            return Type.EUDRA_CT;
+        } else {
+            return Type.UNKNOWN;
+        }
+    }
+
+    private boolean hasNctNumber() {
+        return null != studiennummer && studiennummer.toLowerCase().startsWith("nct");
+    }
+
+    private boolean hasEudraCtNumber() {
+        return null != studiennummer && studiennummer.matches("[0-9]{4}-[0-9]{6}-[0-9]{2}");
+    }
+
+    public enum Type {
+        NCT,
+        EUDRA_CT,
+        UNKNOWN
+    }
 }