killbill-uncached

diff --git a/invoice/src/main/java/com/ning/billing/invoice/dao/DefaultInvoiceDao.java b/invoice/src/main/java/com/ning/billing/invoice/dao/DefaultInvoiceDao.java
index 573b215..99c2204 100644
--- a/invoice/src/main/java/com/ning/billing/invoice/dao/DefaultInvoiceDao.java
+++ b/invoice/src/main/java/com/ning/billing/invoice/dao/DefaultInvoiceDao.java
@@ -18,6 +18,8 @@ package com.ning.billing.invoice.dao;
 
 import java.math.BigDecimal;
 import java.util.ArrayList;
+import java.util.Collection;
+import java.util.LinkedList;
 import java.util.List;
 import java.util.UUID;
 
@@ -26,6 +28,9 @@ import org.skife.jdbi.v2.IDBI;
 import org.skife.jdbi.v2.Transaction;
 import org.skife.jdbi.v2.TransactionStatus;
 
+import com.google.common.base.Function;
+import com.google.common.base.Predicate;
+import com.google.common.collect.Collections2;
 import com.google.inject.Inject;
 import com.ning.billing.ErrorCode;
 import com.ning.billing.catalog.api.Currency;
@@ -72,9 +77,7 @@ public class DefaultInvoiceDao implements InvoiceDao {
             @Override
             public List<Invoice> inTransaction(final InvoiceSqlDao invoiceDao, final TransactionStatus status) throws Exception {
                 final List<Invoice> invoices = invoiceDao.getInvoicesByAccount(accountId.toString());
-
                 populateChildren(invoices, invoiceDao);
-
                 return invoices;
             }
         });
@@ -85,11 +88,7 @@ public class DefaultInvoiceDao implements InvoiceDao {
         return invoiceSqlDao.inTransaction(new Transaction<List<Invoice>, InvoiceSqlDao>() {
             @Override
             public List<Invoice> inTransaction(final InvoiceSqlDao invoiceDao, final TransactionStatus status) throws Exception {
-                final List<Invoice> invoices = invoiceDao.getAllInvoicesByAccount(accountId.toString());
-
-                populateChildren(invoices, invoiceDao);
-
-                return invoices;
+                return getAllInvoicesByAccountFromTransaction(accountId, invoiceDao);
             }
         });
     }
@@ -205,9 +204,28 @@ public class DefaultInvoiceDao implements InvoiceDao {
 
     @Override
     public BigDecimal getAccountBalance(final UUID accountId) {
-        return invoiceSqlDao.getAccountBalance(accountId.toString());
+
+        return invoiceSqlDao.inTransaction(new Transaction<BigDecimal, InvoiceSqlDao>() {
+            @Override
+            public BigDecimal inTransaction(final InvoiceSqlDao transactional, final TransactionStatus status) throws Exception {
+
+                BigDecimal accountBalance = BigDecimal.ZERO;
+                List<Invoice> invoices = getAllInvoicesByAccountFromTransaction(accountId, transactional);
+                List<InvoiceItem> creditBalanceItems = new LinkedList<InvoiceItem>();
+                for (Invoice cur : invoices) {
+                    accountBalance = accountBalance.add(cur.getBalance());
+                    creditBalanceItems.addAll(cur.getInvoiceItems(CreditBalanceAdjInvoiceItem.class));
+                }
+                BigDecimal cbaTotal = BigDecimal.ZERO;
+                for (InvoiceItem cur : creditBalanceItems) {
+                    cbaTotal = cbaTotal.add(cur.getAmount());
+                }
+                return accountBalance.add(cbaTotal.negate());
+            }
+        });
     }
 
+
     @Override
     public void notifyOfPaymentAttempt(final InvoicePayment invoicePayment, final CallContext context) {
         invoicePaymentSqlDao.inTransaction(new Transaction<Void, InvoicePaymentSqlDao>() {
@@ -230,11 +248,16 @@ public class DefaultInvoiceDao implements InvoiceDao {
         return invoiceSqlDao.inTransaction(new Transaction<List<Invoice>, InvoiceSqlDao>() {
             @Override
             public List<Invoice> inTransaction(final InvoiceSqlDao invoiceDao, final TransactionStatus status) throws Exception {
-                final List<Invoice> invoices = invoiceSqlDao.getUnpaidInvoicesByAccountId(accountId.toString(), upToDate.toDate());
-
-                populateChildren(invoices, invoiceDao);
 
-                return invoices;
+                List<Invoice> invoices = getAllInvoicesByAccountFromTransaction(accountId, invoiceDao);
+                Collection<Invoice> unpaidInvoices = Collections2.filter(invoices, new Predicate<Invoice>() {
+                    @Override
+                    public boolean apply(Invoice in) {
+                        // STEPH do we really want to exclude migration invoice
+                        return (!in.isMigrationInvoice() && in.getBalance().compareTo(BigDecimal.ZERO) >= 1);
+                    }
+                });
+                return new ArrayList<Invoice>(unpaidInvoices);
             }
         });
     }
@@ -351,6 +374,13 @@ public class DefaultInvoiceDao implements InvoiceDao {
         getInvoicePaymentsWithinTransaction(invoices, invoiceSqlDao);
     }
 
+    private List<Invoice> getAllInvoicesByAccountFromTransaction(final UUID accountId, final InvoiceSqlDao transactional) {
+        final List<Invoice> invoices = transactional.getAllInvoicesByAccount(accountId.toString());
+        populateChildren(invoices, transactional);
+        return invoices;
+    }
+
+
     private void getInvoiceItemsWithinTransaction(final List<Invoice> invoices, final InvoiceSqlDao invoiceDao) {
         for (final Invoice invoice : invoices) {
             getInvoiceItemsWithinTransaction(invoice, invoiceDao);

diff --git a/invoice/src/main/java/com/ning/billing/invoice/dao/InvoicePaymentSqlDao.java b/invoice/src/main/java/com/ning/billing/invoice/dao/InvoicePaymentSqlDao.java
index 8b12f8d..ab817b5 100644
--- a/invoice/src/main/java/com/ning/billing/invoice/dao/InvoicePaymentSqlDao.java
+++ b/invoice/src/main/java/com/ning/billing/invoice/dao/InvoicePaymentSqlDao.java
@@ -63,9 +63,11 @@ public interface InvoicePaymentSqlDao extends EntitySqlDao<InvoicePayment>, Tran
     @SqlQuery
     public InvoicePayment getByPaymentAttemptId(@Bind("paymentAttempt") final String paymentAttemptId);
 
+    @Override
     @SqlQuery
     public List<InvoicePayment> get();
 
+    @Override
     @SqlUpdate
     public void create(@InvoicePaymentBinder final InvoicePayment invoicePayment,
                        @CallContextBinder final CallContext context);

diff --git a/invoice/src/main/java/com/ning/billing/invoice/dao/InvoiceSqlDao.java b/invoice/src/main/java/com/ning/billing/invoice/dao/InvoiceSqlDao.java
index 21fe8d5..df254de 100644
--- a/invoice/src/main/java/com/ning/billing/invoice/dao/InvoiceSqlDao.java
+++ b/invoice/src/main/java/com/ning/billing/invoice/dao/InvoiceSqlDao.java
@@ -77,13 +77,6 @@ public interface InvoiceSqlDao extends EntitySqlDao<Invoice>, AuditSqlDao, Trans
     @RegisterMapper(UuidMapper.class)
     UUID getInvoiceIdByPaymentAttemptId(@Bind("paymentAttemptId") final String paymentAttemptId);
 
-    @SqlQuery
-    @RegisterMapper(BalanceMapper.class)
-    BigDecimal getAccountBalance(@Bind("accountId") final String accountId);
-
-    @SqlQuery
-    List<Invoice> getUnpaidInvoicesByAccountId(@Bind("accountId") final String accountId,
-                                               @Bind("upToDate") final Date upToDate);
 
     @BindingAnnotation(InvoiceBinder.InvoiceBinderFactory.class)
     @Retention(RetentionPolicy.RUNTIME)

diff --git a/invoice/src/main/java/com/ning/billing/invoice/model/DefaultInvoice.java b/invoice/src/main/java/com/ning/billing/invoice/model/DefaultInvoice.java
index c636ada..7c2bae1 100644
--- a/invoice/src/main/java/com/ning/billing/invoice/model/DefaultInvoice.java
+++ b/invoice/src/main/java/com/ning/billing/invoice/model/DefaultInvoice.java
@@ -185,6 +185,7 @@ public class DefaultInvoice extends EntityBase implements Invoice {
     @Override
     public BigDecimal getBalance() {
         // credits offset payments
+        // STEPH fix that
         return getAmountCharged().subtract(getAmountPaid().subtract(getAmountCredited()));
     }
 

diff --git a/invoice/src/main/resources/com/ning/billing/invoice/dao/InvoiceSqlDao.sql.stg b/invoice/src/main/resources/com/ning/billing/invoice/dao/InvoiceSqlDao.sql.stg
index 8c8a3a2..90e66bc 100644
--- a/invoice/src/main/resources/com/ning/billing/invoice/dao/InvoiceSqlDao.sql.stg
+++ b/invoice/src/main/resources/com/ning/billing/invoice/dao/InvoiceSqlDao.sql.stg
@@ -41,17 +41,9 @@ getInvoicesByAccountAfterDate() ::= <<
 getInvoicesBySubscription() ::= <<
   SELECT i.record_id as invoice_number, <invoiceFields("i.")>
   FROM invoices i
-  LEFT JOIN recurring_invoice_items rii ON i.id = rii.invoice_id
+  LEFT JOIN invoice_items rii ON i.id = rii.invoice_id
   WHERE rii.subscription_id = :subscriptionId AND migrated = 'FALSE'
   GROUP BY i.record_id, <invoiceFields("i.")>
-
-  UNION ALL
-
-  SELECT i.record_id as invoice_number, <invoiceFields("i.")>
-  FROM invoices i
-  LEFT JOIN fixed_invoice_items fii ON i.id = fii.invoice_id
-  WHERE fii.subscription_id = :subscriptionId AND migrated = 'FALSE'
-  GROUP BY i.record_id, <invoiceFields("i.")>;
 >>
 
 getById() ::= <<
@@ -60,16 +52,6 @@ getById() ::= <<
   WHERE id = :id;
 >>
 
-getAccountBalance() ::= <<
-  SELECT SUM(iis.amount_invoiced) AS amount_invoiced,
-         SUM(ips.total_paid) AS amount_paid
-  FROM invoices i
-  LEFT JOIN invoice_payment_summary ips ON i.id = ips.invoice_id
-  LEFT JOIN invoice_item_summary iis ON i.id = iis.invoice_id
-  WHERE i.account_id = :accountId
-  GROUP BY i.account_id;
->>
-
 create() ::= <<
   INSERT INTO invoices(<invoiceFields()>)
   VALUES (:id, :accountId, :invoiceDate, :targetDate, :currency, :migrated, :userName, :createdDate);
@@ -82,17 +64,6 @@ getInvoiceIdByPaymentAttemptId() ::= <<
      AND ip.payment_attempt_id = :paymentAttemptId
 >>
 
-getUnpaidInvoicesByAccountId() ::= <<
-  SELECT record_id as invoice_number, <invoiceFields("i.")>
-  FROM invoices i
-  LEFT JOIN invoice_payment_summary ips ON i.id = ips.invoice_id
-  LEFT JOIN invoice_item_summary iis ON i.id = iis.invoice_id
-  WHERE i.account_id = :accountId AND NOT (i.target_date > :upToDate) AND migrated = 'FALSE'
-  GROUP BY i.id, i.account_id, i.invoice_date, i.target_date, i.currency
-  HAVING (SUM(iis.amount_invoiced) > SUM(ips.total_paid)) OR (SUM(ips.total_paid) IS NULL)
-  AND SUM(iis.amount_invoiced) > 0
-  ORDER BY i.target_date ASC;
->>
 
 getRecordId() ::= <<
     SELECT record_id

diff --git a/invoice/src/test/java/com/ning/billing/invoice/api/migration/TestDefaultInvoiceMigrationApi.java b/invoice/src/test/java/com/ning/billing/invoice/api/migration/TestDefaultInvoiceMigrationApi.java
index c44a63a..e0d25dc 100644
--- a/invoice/src/test/java/com/ning/billing/invoice/api/migration/TestDefaultInvoiceMigrationApi.java
+++ b/invoice/src/test/java/com/ning/billing/invoice/api/migration/TestDefaultInvoiceMigrationApi.java
@@ -16,21 +16,24 @@
 
 package com.ning.billing.invoice.api.migration;
 
-import static org.testng.Assert.assertNotNull;
-
-import java.io.IOException;
 import java.math.BigDecimal;
-import java.net.URL;
 import java.util.Collection;
 import java.util.List;
 import java.util.UUID;
 
 import org.joda.time.DateTime;
+import org.skife.jdbi.v2.Handle;
+import org.skife.jdbi.v2.IDBI;
+import org.skife.jdbi.v2.TransactionCallback;
+import org.skife.jdbi.v2.TransactionStatus;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.testng.Assert;
 import org.testng.annotations.AfterClass;
+import org.testng.annotations.AfterSuite;
 import org.testng.annotations.BeforeClass;
+import org.testng.annotations.BeforeSuite;
+import org.testng.annotations.BeforeTest;
 import org.testng.annotations.Guice;
 import org.testng.annotations.Test;
 
@@ -111,14 +114,32 @@ public class TestDefaultInvoiceMigrationApi extends InvoicingTestBase {
     private UUID migrationInvoiceId;
     private UUID regularInvoiceId;
 
+    private IDBI dbi;
+
+
     private static final BigDecimal MIGRATION_INVOICE_AMOUNT = new BigDecimal("100.00");
     private static final Currency MIGRATION_INVOICE_CURRENCY = Currency.USD;
 
     private final Clock clock = new ClockMock();
 
+    //@BeforeTest(groups = {"slow"})
+    public void cleanup() {
+        if (dbi !=  null) {
+            dbi.inTransaction(new TransactionCallback<Void>() {
+                @Override
+                public Void inTransaction(final Handle h, final TransactionStatus status)
+                throws Exception {
+                    h.execute("truncate table invoices");
+                    h.execute("truncate table invoice_items");
+                    h.execute("truncate table invoice_payments");
+                    return null;
+                }
+            });
+        }
+    }
 
 
-    @BeforeClass(groups = {"slow"})
+    @BeforeSuite(groups = {"slow"})
     public void setup() throws Exception {
         log.info("Starting set up");
         accountId = UUID.randomUUID();
@@ -133,7 +154,8 @@ public class TestDefaultInvoiceMigrationApi extends InvoicingTestBase {
 
         helper.initDb(invoiceDdl);
         helper.initDb(utilDdl);
-
+        dbi = helper.getDBI();
+        cleanup();
         busService.getBus().start();
 
         ((ZombieControl) billingApi).addResult("setChargedThroughDate", BrainDeadProxyFactory.ZOMBIE_VOID);
@@ -142,7 +164,7 @@ public class TestDefaultInvoiceMigrationApi extends InvoicingTestBase {
 
     }
 
-    @AfterClass(groups = {"slow"})
+    @AfterSuite(groups = {"slow"})
     public void tearDown() {
         try {
             ((DefaultBusService) busService).stopBus();