InvoiceSqlDao.sql.stg

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, 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, 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_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;
>>

getInvoicesForPayment() ::= <<
  SELECT i.id
  FROM invoices i
  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, 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_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;
>>

create() ::= <<
  INSERT INTO invoices(id, account_id, invoice_date, target_date, currency)
  VALUES (:id, :accountId, :invoiceDate, :targetDate, :currency);
>>

update() ::= <<
  UPDATE invoices
  SET account_id = :accountId, invoice_date = :invoiceDate, target_date = :targetDate, currency = :currency
  WHERE id = :id;
>>

notifySuccessfulPayment() ::= <<
  INSERT INTO invoice_payments(invoice_id, payment_id, payment_date, amount, currency)
  VALUES(:invoiceId, :paymentId, :paymentDate, :amount, :currency);
>>

notifyFailedPayment() ::= <<
  INSERT INTO invoice_payments(invoice_id, payment_id, payment_date)
  VALUES(:invoiceId, :paymentId, :paymentAttemptDate);
>>

test() ::= <<
  SELECT 1
  FROM invoices;
>>
;