package io.teiler.server.persistence.repositories;

import io.teiler.server.persistence.entities.DebtEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.persistence.*;
import java.util.List;

/**
 * Provides database-related operations for debts.
 *
 * @author dthoma
 */
@Repository
public class DebtRepository {

    static final String STATEMENT_SQLMAP = "GET_DEBT_QUERY";

    @Autowired
    private EntityManager entityManager;

    /**
     * Returns a {@link List} of {@link DebtEntity} in the Group with the given Id.
     *
     * @param groupId Id of the Group
     * @return {@link List} of {@link DebtEntity}
     */
    public List<DebtEntity> get(String groupId) {
        Query query = entityManager.createNativeQuery(
                "SELECT\n" +
                        "    person.id AS person,\n" +
                        "    transactions.credit - profiteers.debt AS balance\n" +
                        "FROM person\n" +
                        "LEFT JOIN (\n" +
                        "    SELECT \n" +
                        "        transaction.payer,\n" +
                        "        SUM(profiteer.share) AS credit\n" +
                        "    FROM transaction\n" +
                        "    LEFT JOIN profiteer ON profiteer.transaction=transaction.id\n" +
                        "    LEFT JOIN person ON person.id=profiteer.person\n" +
                        "    WHERE person.\"group\"='1'\n" +
                        "    GROUP BY transaction.payer\n" +
                        ") AS transactions ON transactions.payer=person.id\n" +
                        "LEFT JOIN (\n" +
                        "    SELECT \n" +
                        "        profiteer.person,\n" +
                        "        SUM(profiteer.share) AS debt\n" +
                        "    FROM profiteer\n" +
                        "    LEFT JOIN person ON person.id=profiteer.person\n" +
                        "    WHERE person.\"group\"='1'\n" +
                        "    GROUP BY profiteer.person\n" +
                        ") AS profiteers ON profiteers.person=person.id", STATEMENT_SQLMAP);

        return query.getResultList();
    }
}

@@ -767,54 +767,20 @@
     
-Query query = entityManager.createNativeQuery(
+String sql =
 %0A   
@@ -799,30 +799,20 @@
  %22SELECT
-%5Cn
 %22 +%0A
-        
         
@@ -840,38 +840,28 @@
 d AS person,
-%5Cn
 %22 +%0A
-        
             
@@ -917,18 +917,16 @@
  balance
-%5Cn
 %22 +%0A    
@@ -937,24 +937,16 @@
         
- 
-       
 %22FROM pe
@@ -945,26 +945,24 @@
 %22FROM person
-%5Cn
 %22 +%0A        
@@ -965,32 +965,24 @@
             
-   
-     
 %22LEFT JOIN (
@@ -973,34 +973,32 @@
     %22LEFT JOIN (
-%5Cn
 %22 +%0A            
@@ -997,32 +997,24 @@
             
-    
-    
 %22    SELECT 
@@ -1005,34 +1005,32 @@
     %22    SELECT 
-%5Cn
 %22 +%0A            
@@ -1029,32 +1029,24 @@
             
-      
-  
 %22        tra
@@ -1060,30 +1060,20 @@
 n.payer,
-%5Cn
 %22 +%0A
-        
         
@@ -1119,30 +1119,20 @@
 S credit
-%5Cn
 %22 +%0A
-        
         
@@ -1160,30 +1160,20 @@
 nsaction
-%5Cn
 %22 +%0A
-        
         
@@ -1240,26 +1240,24 @@
 ansaction.id
-%5Cn
 %22 +%0A        
@@ -1260,32 +1260,24 @@
             
- 
-       
 %22    LEFT JO
@@ -1307,34 +1307,32 @@
 profiteer.person
-%5Cn
 %22 +%0A            
@@ -1331,32 +1331,24 @@
             
-   
-     
 %22    WHERE p
@@ -1355,37 +1355,34 @@
 erson.%5C%22group%5C%22=
-'1'%5Cn
+?1
 %22 +%0A            
@@ -1381,32 +1381,24 @@
             
- 
-       
 %22    GROUP B
@@ -1416,30 +1416,20 @@
 on.payer
-%5Cn
 %22 +%0A
-        
         
@@ -1486,18 +1486,16 @@
 erson.id
-%5Cn
 %22 +%0A    
@@ -1502,32 +1502,24 @@
             
-    
-    
 %22LEFT JOIN (
@@ -1518,18 +1518,16 @@
 T JOIN (
-%5Cn
 %22 +%0A    
@@ -1534,32 +1534,24 @@
             
-      
-  
 %22    SELECT 
@@ -1550,30 +1550,20 @@
  SELECT 
-%5Cn
 %22 +%0A
-        
         
@@ -1596,30 +1596,20 @@
 .person,
-%5Cn
 %22 +%0A
-        
         
@@ -1653,18 +1653,16 @@
  AS debt
-%5Cn
 %22 +%0A    
@@ -1669,32 +1669,24 @@
             
-        
 %22    FROM pr
@@ -1692,18 +1692,16 @@
 rofiteer
-%5Cn
 %22 +%0A    
@@ -1708,32 +1708,24 @@
             
- 
-       
 %22    LEFT JO
@@ -1755,34 +1755,32 @@
 profiteer.person
-%5Cn
 %22 +%0A            
@@ -1779,32 +1779,24 @@
             
-   
-     
 %22    WHERE p
@@ -1815,13 +1815,10 @@
 p%5C%22=
-'1'%5Cn
+?1
 %22 +%0A
@@ -1829,32 +1829,24 @@
             
-  
-      
 %22    GROUP B
@@ -1867,22 +1867,12 @@
 rson
-%5Cn
 %22 +%0A
-        
     
@@ -1935,26 +1935,126 @@
 .id%22
-, STATEMENT_SQLMAP
+;%0A%0A        Query query = entityManager.createNativeQuery(sql, STATEMENT_SQLMAP);%0A        query.setParameter(1, groupId
 );%0A%0A

add query parameter