Details
diff --git a/account/src/main/resources/com/ning/billing/account/ddl.sql b/account/src/main/resources/com/ning/billing/account/ddl.sql
index 42cf2ef..acca205 100644
--- a/account/src/main/resources/com/ning/billing/account/ddl.sql
+++ b/account/src/main/resources/com/ning/billing/account/ddl.sql
@@ -2,12 +2,12 @@ DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
id char(36) NOT NULL,
external_key varchar(128) NULL,
- email varchar(50) DEFAULT NULL,
+ email varchar(50) NOT NULL,
name varchar(100) NOT NULL,
first_name_length int NOT NULL,
phone varchar(13) DEFAULT NULL,
- currency char(3) NOT NULL,
- billing_cycle_day int NOT NULL,
+ currency char(3) DEFAULT NULL,
+ billing_cycle_day int DEFAULT NULL,
payment_provider_name varchar(20) DEFAULT NULL,
PRIMARY KEY(id)
) ENGINE=innodb;
diff --git a/api/src/main/java/com/ning/billing/invoice/api/Invoice.java b/api/src/main/java/com/ning/billing/invoice/api/Invoice.java
index 4c7d6fd..f69b934 100644
--- a/api/src/main/java/com/ning/billing/invoice/api/Invoice.java
+++ b/api/src/main/java/com/ning/billing/invoice/api/Invoice.java
@@ -48,4 +48,6 @@ public interface Invoice extends Entity {
BigDecimal getTotalAmount();
BigDecimal getAmountOutstanding();
+
+ boolean isDueForPayment(DateTime targetDate, int numberOfDays);
}
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 c09bb25..1186456 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
@@ -51,8 +51,39 @@ public class DefaultInvoiceDao implements InvoiceDao {
}
@Override
+ public List<Invoice> get() {
+ return invoiceDao.inTransaction(new Transaction<List<Invoice>, InvoiceSqlDao>() {
+ @Override
+ public List<Invoice> inTransaction(InvoiceSqlDao invoiceDao, TransactionStatus status) throws Exception {
+ List<Invoice> invoices = invoiceDao.get();
+
+ InvoiceItemSqlDao invoiceItemDao = invoiceDao.become(InvoiceItemSqlDao.class);
+ for (Invoice invoice : invoices) {
+ List<InvoiceItem> invoiceItems = invoiceItemDao.getInvoiceItemsByInvoice(invoice.getId().toString());
+ invoice.add(invoiceItems);
+ }
+
+ return invoices;
+ }
+ });
+ }
+
+ @Override
public Invoice getById(final String invoiceId) {
- return invoiceDao.getById(invoiceId);
+ return invoiceDao.inTransaction(new Transaction<Invoice, InvoiceSqlDao>() {
+ @Override
+ public Invoice inTransaction(InvoiceSqlDao invoiceDao, TransactionStatus status) throws Exception {
+ Invoice invoice = invoiceDao.getById(invoiceId);
+
+ if (invoice != null) {
+ InvoiceItemSqlDao invoiceItemDao = invoiceDao.become(InvoiceItemSqlDao.class);
+ List<InvoiceItem> invoiceItems = invoiceItemDao.getInvoiceItemsByInvoice(invoiceId);
+ invoice.add(invoiceItems);
+ }
+
+ return invoice;
+ }
+ });
}
@Override
@@ -83,8 +114,21 @@ public class DefaultInvoiceDao implements InvoiceDao {
}
@Override
- public List<Invoice> getInvoicesBySubscription(String subscriptionId) {
- return invoiceDao.getInvoicesBySubscription(subscriptionId);
+ public List<Invoice> getInvoicesBySubscription(final String subscriptionId) {
+ return invoiceDao.inTransaction(new Transaction<List<Invoice>, InvoiceSqlDao>() {
+ @Override
+ public List<Invoice> inTransaction(InvoiceSqlDao invoiceDao, TransactionStatus status) throws Exception {
+ List<Invoice> invoices = invoiceDao.getInvoicesBySubscription(subscriptionId);
+
+ InvoiceItemSqlDao invoiceItemDao = invoiceDao.become(InvoiceItemSqlDao.class);
+ for (Invoice invoice : invoices) {
+ List<InvoiceItem> invoiceItems = invoiceItemDao.getInvoiceItemsByInvoice(invoice.getId().toString());
+ invoice.add(invoiceItems);
+ }
+
+ return invoices;
+ }
+ });
}
@Override
diff --git a/invoice/src/main/java/com/ning/billing/invoice/dao/InvoiceDao.java b/invoice/src/main/java/com/ning/billing/invoice/dao/InvoiceDao.java
index 477be07..b59e4ed 100644
--- a/invoice/src/main/java/com/ning/billing/invoice/dao/InvoiceDao.java
+++ b/invoice/src/main/java/com/ning/billing/invoice/dao/InvoiceDao.java
@@ -27,6 +27,8 @@ public interface InvoiceDao {
Invoice getById(final String id);
+ List<Invoice> get();
+
List<Invoice> getInvoicesByAccount(final String accountId);
List<Invoice> getInvoicesBySubscription(final String subscriptionId);
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 67e94bb..aa0051a 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
@@ -91,8 +91,8 @@ public interface InvoiceSqlDao extends EntityDao<Invoice>, Transactional<Invoice
q.bind("targetDate", invoice.getTargetDate().toDate());
q.bind("amountPaid", invoice.getAmountPaid());
q.bind("amountOutstanding", invoice.getAmountOutstanding());
- DateTime invoiceDate = invoice.getLastPaymentAttempt();
- q.bind("lastPaymentAttempt", invoiceDate == null ? null : invoiceDate.toDate());
+ DateTime last_payment_date = invoice.getLastPaymentAttempt();
+ q.bind("lastPaymentAttempt", last_payment_date == null ? null : last_payment_date.toDate());
q.bind("currency", invoice.getCurrency().toString());
}
};
@@ -108,6 +108,9 @@ public interface InvoiceSqlDao extends EntityDao<Invoice>, Transactional<Invoice
DateTime invoiceDate = new DateTime(result.getTimestamp("invoice_date"));
DateTime targetDate = new DateTime(result.getTimestamp("target_date"));
BigDecimal amountPaid = result.getBigDecimal("amount_paid");
+ if (amountPaid == null) {
+ amountPaid = BigDecimal.ZERO;
+ }
Timestamp lastPaymentAttemptTimeStamp = result.getTimestamp("last_payment_attempt");
DateTime lastPaymentAttempt = lastPaymentAttemptTimeStamp == null ? null : new DateTime(lastPaymentAttemptTimeStamp);
Currency currency = Currency.valueOf(result.getString("currency"));
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 0c956e4..797f587 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
@@ -123,5 +123,14 @@ public class DefaultInvoice implements Invoice {
public BigDecimal getAmountOutstanding() {
return getTotalAmount().subtract(getAmountPaid());
}
+
+ @Override
+ public boolean isDueForPayment(final DateTime targetDate, final int numberOfDays) {
+ if (lastPaymentAttempt == null) {
+ return true;
+ }
+
+ return lastPaymentAttempt.plusDays(numberOfDays).isBefore(targetDate);
+ }
}
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 9414228..0323040 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
@@ -1,20 +1,31 @@
group InvoiceDao;
+get() ::= <<
+ SELECT i.id, i.account_id, i.invoice_date, i.target_date, i.currency, SUM(ii.amount) AS amount,
+ SUM(ip.amount) AS amount_paid, MAX(ip.payment_date) AS last_payment_attempt
+ FROM invoices i
+ LEFT JOIN invoice_payments ip ON ip.invoice_id = i.id
+ LEFT JOIN invoice_items ii ON ii.invoice_id = i.id
+ GROUP BY i.id, i.account_id, i.invoice_date, i.target_date, i.currency
+ ORDER BY i.invoice_date ASC;
+>>
+
getInvoicesByAccount() ::= <<
- SELECT i.id, i.account_id, i.invoice_date, i.target_date, i.currency,
+ SELECT i.id, i.account_id, i.invoice_date, i.target_date, i.currency, SUM(ii.amount) AS amount,
SUM(ip.amount) AS amount_paid, MAX(ip.payment_date) AS last_payment_attempt
FROM invoices i
LEFT JOIN invoice_payments ip ON ip.invoice_id = i.id
+ LEFT JOIN invoice_items ii ON ii.invoice_id = i.id
WHERE i.account_id = :accountId
GROUP BY i.id, i.account_id, i.invoice_date, i.target_date, i.currency
ORDER BY i.invoice_date ASC;
>>
getInvoicesBySubscription() ::= <<
- SELECT i.id, i.account_id, i.invoice_date, i.target_date, i.currency,
+ SELECT i.id, i.account_id, i.invoice_date, i.target_date, i.currency, SUM(ii.amount) AS amount,
SUM(ip.amount) AS amount_paid, MAX(ip.payment_date) AS last_payment_attempt
FROM invoices i
- INNER JOIN invoice_items ii ON i.id = ii.invoice_id
+ LEFT JOIN invoice_items ii ON i.id = ii.invoice_id
LEFT JOIN invoice_payments ip ON ip.invoice_id = i.id
WHERE ii.subscription_id = :subscriptionId
GROUP BY i.id, i.account_id, i.invoice_date, i.target_date, i.currency;
@@ -23,13 +34,20 @@ getInvoicesBySubscription() ::= <<
getInvoicesForPayment() ::= <<
SELECT i.id
FROM invoices i
- GROUP BY i.id;
+ LEFT JOIN invoice_payment_summary ips ON ips.invoice_id = i.id
+ LEFT JOIN invoice_item_summary iis ON iis.invoice_id = i.id
+ WHERE ((ips.last_payment_date IS NULL) OR (DATEDIFF(:targetDate, ips.last_payment_date) >= :numberOfDays))
+ AND ((ips.total_paid IS NULL) OR (iis.total_amount >= ips.total_paid))
+ AND ((iis.total_amount IS NOT NULL) AND (iis.total_amount > 0))
+ GROUP BY i.id, i.account_id, i.invoice_date, i.target_date, i.currency;
>>
getById() ::= <<
- SELECT i.id, i.account_id, i.invoice_date, i.target_date, SUM(ii.amount) AS amount, i.currency
+ SELECT i.id, i.account_id, i.invoice_date, i.target_date, i.currency, SUM(ii.amount) AS amount,
+ SUM(ip.amount) AS amount_paid, MAX(ip.payment_date) AS last_payment_attempt
FROM invoices i
- INNER JOIN invoice_items ii ON i.id = ii.invoice_id
+ LEFT JOIN invoice_items ii ON i.id = ii.invoice_id
+ LEFT JOIN invoice_payments ip ON ip.invoice_id = i.id
WHERE i.id = :id
GROUP BY i.id, i.account_id, i.invoice_date, i.target_date, i.currency;
>>
diff --git a/invoice/src/main/resources/com/ning/billing/invoice/ddl.sql b/invoice/src/main/resources/com/ning/billing/invoice/ddl.sql
index f6f2c41..a3ff37d 100644
--- a/invoice/src/main/resources/com/ning/billing/invoice/ddl.sql
+++ b/invoice/src/main/resources/com/ning/billing/invoice/ddl.sql
@@ -35,4 +35,16 @@ CREATE TABLE invoice_payments (
currency char(3),
PRIMARY KEY(invoice_id, payment_id)
) ENGINE=innodb;
-CREATE UNIQUE INDEX invoice_payments_unique ON invoice_payments(invoice_id, payment_id);
\ No newline at end of file
+CREATE UNIQUE INDEX invoice_payments_unique ON invoice_payments(invoice_id, payment_id);
+
+DROP VIEW IF EXISTS invoice_payment_summary;
+CREATE VIEW invoice_payment_summary AS
+SELECT invoice_id, SUM(amount) AS total_paid, MAX(payment_date) AS last_payment_date
+FROM invoice_payments
+GROUP BY invoice_id;
+
+DROP VIEW IF EXISTS invoice_item_summary;
+CREATE VIEW invoice_item_summary AS
+SELECT invoice_id, SUM(amount) AS total_amount
+FROM invoice_items
+GROUP BY invoice_id;
\ No newline at end of file
diff --git a/invoice/src/test/java/com/ning/billing/invoice/dao/InvoiceDaoTests.java b/invoice/src/test/java/com/ning/billing/invoice/dao/InvoiceDaoTests.java
index 42c5d47..0666ada 100644
--- a/invoice/src/test/java/com/ning/billing/invoice/dao/InvoiceDaoTests.java
+++ b/invoice/src/test/java/com/ning/billing/invoice/dao/InvoiceDaoTests.java
@@ -17,9 +17,11 @@
package com.ning.billing.invoice.dao;
import java.math.BigDecimal;
+import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import org.joda.time.DateTime;
+import org.joda.time.Days;
import org.testng.annotations.Test;
import com.ning.billing.catalog.api.Currency;
import com.ning.billing.invoice.api.Invoice;
@@ -70,8 +72,8 @@ public class InvoiceDaoTests extends InvoiceDaoTestBase {
Invoice savedInvoice = invoiceDao.getById(invoiceId.toString());
assertNotNull(savedInvoice);
- assertEquals(savedInvoice.getTotalAmount(), new BigDecimal("21.00"));
- assertEquals(savedInvoice.getAmountOutstanding(), new BigDecimal("21.00"));
+ assertEquals(savedInvoice.getTotalAmount().compareTo(new BigDecimal("21.00")), 0);
+ assertEquals(savedInvoice.getAmountOutstanding().compareTo(new BigDecimal("21.00")), 0);
assertEquals(savedInvoice.getAmountPaid(), BigDecimal.ZERO);
assertEquals(savedInvoice.getItems().size(), 1);
@@ -81,10 +83,10 @@ public class InvoiceDaoTests extends InvoiceDaoTestBase {
Invoice retrievedInvoice = invoiceDao.getById(invoiceId.toString());
assertNotNull(retrievedInvoice);
- assertEquals(retrievedInvoice.getTotalAmount(), new BigDecimal("21.00"));
- assertEquals(retrievedInvoice.getAmountOutstanding(), new BigDecimal("10.00"));
- assertEquals(retrievedInvoice.getAmountPaid(), new BigDecimal("11.00"));
assertEquals(retrievedInvoice.getItems().size(), 1);
+ assertEquals(retrievedInvoice.getTotalAmount().compareTo(new BigDecimal("21.00")), 0);
+ assertEquals(retrievedInvoice.getAmountOutstanding().compareTo(new BigDecimal("10.00")), 0);
+ assertEquals(retrievedInvoice.getAmountPaid().compareTo(new BigDecimal("11.00")), 0);
}
@Test
@@ -148,15 +150,10 @@ public class InvoiceDaoTests extends InvoiceDaoTestBase {
List<UUID> invoices;
DateTime notionalDate = new DateTime();
- // determine the number of existing invoices available for payment (to avoid side effects from other tests)
- invoices = invoiceDao.getInvoicesForPayment(notionalDate.toDate(), NUMBER_OF_DAY_BETWEEN_RETRIES);
- int existingInvoiceCount = invoices.size();
-
// create a new invoice with one item
UUID accountId = UUID.randomUUID();
DateTime targetDate = new DateTime(2011, 10, 6, 0, 0, 0, 0);
Invoice invoice = new DefaultInvoice(accountId, targetDate, Currency.USD);
- invoiceDao.save(invoice);
UUID invoiceId = invoice.getId();
UUID subscriptionId = UUID.randomUUID();
@@ -165,50 +162,76 @@ public class InvoiceDaoTests extends InvoiceDaoTestBase {
BigDecimal amount = rate.multiply(new BigDecimal("3.0"));
DefaultInvoiceItem item = new DefaultInvoiceItem(invoiceId, subscriptionId, targetDate, endDate, "test", amount, rate, Currency.USD);
- invoiceItemDao.save(item);
+ invoice.add(item);
+ invoiceDao.save(invoice);
// ensure that the number of invoices for payment has increased by 1
+ int count;
invoices = invoiceDao.getInvoicesForPayment(notionalDate.toDate(), NUMBER_OF_DAY_BETWEEN_RETRIES);
- assertEquals(invoices.size(), existingInvoiceCount + 1);
+ List<Invoice> invoicesDue = getInvoicesDueForPaymentAttempt(invoiceDao.get(), notionalDate);
+ count = invoicesDue.size();
+ assertEquals(invoices.size(), count);
- // attempt a payment; ensure that the number of invoices for payment has decreased by 1 (no retries for eight days)
+ // attempt a payment; ensure that the number of invoices for payment has decreased by 1
+ // (no retries for NUMBER_OF_DAYS_BETWEEN_RETRIES days)
invoiceDao.notifyFailedPayment(invoice.getId().toString(), UUID.randomUUID().toString(), notionalDate.toDate());
invoices = invoiceDao.getInvoicesForPayment(notionalDate.toDate(), NUMBER_OF_DAY_BETWEEN_RETRIES);
- assertEquals(invoices.size(), existingInvoiceCount);
+ count = getInvoicesDueForPaymentAttempt(invoiceDao.get(), notionalDate).size();
+ assertEquals(invoices.size(), count);
- // advance clock by 8 days; ensure that number of invoices for payment has increased by 1 (retry)
+ // advance clock by NUMBER_OF_DAYS_BETWEEN_RETRIES days
+ // ensure that number of invoices for payment has increased by 1 (retry)
notionalDate = notionalDate.plusDays(NUMBER_OF_DAY_BETWEEN_RETRIES);
invoices = invoiceDao.getInvoicesForPayment(notionalDate.toDate(), NUMBER_OF_DAY_BETWEEN_RETRIES);
- assertEquals(invoices.size(), existingInvoiceCount + 1);
+ count = getInvoicesDueForPaymentAttempt(invoiceDao.get(), notionalDate).size();
+ assertEquals(invoices.size(), count);
// post successful partial payment; ensure that number of invoices for payment has decreased by 1
invoiceDao.notifySuccessfulPayment(invoiceId.toString(), new BigDecimal("22.0000"), Currency.USD.toString(), UUID.randomUUID().toString(), notionalDate.toDate());
invoices = invoiceDao.getInvoicesForPayment(notionalDate.toDate(), NUMBER_OF_DAY_BETWEEN_RETRIES);
- assertEquals(invoices.size(), existingInvoiceCount);
+ count = getInvoicesDueForPaymentAttempt(invoiceDao.get(), notionalDate).size();
+ assertEquals(invoices.size(), count);
// get invoice; verify amount paid is correct
invoice = invoiceDao.getById(invoiceId.toString());
assertEquals(invoice.getAmountPaid().compareTo(new BigDecimal("22.0")), 0);
- // advance clock eight days; ensure that number of invoices for payment has increased by 1 (retry)
+ // advance clock NUMBER_OF_DAYS_BETWEEN_RETRIES days
+ // ensure that number of invoices for payment has increased by 1 (retry)
notionalDate = notionalDate.plusDays(NUMBER_OF_DAY_BETWEEN_RETRIES);
invoices = invoiceDao.getInvoicesForPayment(notionalDate.toDate(), NUMBER_OF_DAY_BETWEEN_RETRIES);
- assertEquals(invoices.size(), existingInvoiceCount + 1);
+ count = getInvoicesDueForPaymentAttempt(invoiceDao.get(), notionalDate).size();
+ assertEquals(invoices.size(), count);
// post completed payment; ensure that the number of invoices for payment has decreased by 1
invoiceDao.notifySuccessfulPayment(invoiceId.toString(), new BigDecimal("5.0000"), Currency.USD.toString(), UUID.randomUUID().toString(), notionalDate.toDate());
invoices = invoiceDao.getInvoicesForPayment(notionalDate.toDate(), NUMBER_OF_DAY_BETWEEN_RETRIES);
- assertEquals(invoices.size(), existingInvoiceCount);
+ count = getInvoicesDueForPaymentAttempt(invoiceDao.get(), notionalDate).size();
+ assertEquals(invoices.size(), count);
// get invoice; verify amount paid is correct
invoice = invoiceDao.getById(invoiceId.toString());
+ count = getInvoicesDueForPaymentAttempt(invoiceDao.get(), notionalDate).size();
assertEquals(invoice.getAmountPaid().compareTo(new BigDecimal("27.0")), 0);
- // advance clock by 8 days; ensure that the number of invoices for payment hasn't changed
+ // advance clock by NUMBER_OF_DAYS_BETWEEN_RETRIES days
+ // ensure that the number of invoices for payment hasn't changed
notionalDate = notionalDate.plusDays(NUMBER_OF_DAY_BETWEEN_RETRIES);
invoices = invoiceDao.getInvoicesForPayment(notionalDate.toDate(), NUMBER_OF_DAY_BETWEEN_RETRIES);
- assertEquals(invoices.size(), existingInvoiceCount);
+ count = getInvoicesDueForPaymentAttempt(invoiceDao.get(), notionalDate).size();
+ assertEquals(invoices.size(), count);
+ }
+
+ private List<Invoice> getInvoicesDueForPaymentAttempt(List<Invoice> invoices, DateTime date) {
+ List<Invoice> invoicesDue= new ArrayList<Invoice>();
+
+ for (Invoice invoice : invoices) {
+ if (invoice.isDueForPayment(date, NUMBER_OF_DAY_BETWEEN_RETRIES)) {
+ invoicesDue.add(invoice);
+ }
+ }
+ return invoicesDue;
}
@Test
