/**
* Initializes invoice data.
* This method is equivalent to the old getInvoice()
*/
protected function _initData()
{
$invoices = new SimpleInvoices_Db_Table_Invoices();
$invoiceItems = new SimpleInvoices_Db_Table_InvoiceItems();
$payments = new SimpleInvoices_Db_Table_Payment();
$this->_data = $invoices->getInvoice($this->_id);
if (!is_array($this->_data)) {
require_once 'SimpleInvoices/Exception.php';
throw new SimpleInvoices_Exception('Invalid invoice identifier.');
}
// I unset the ID as I don't want it to be present in inserts or updates.
unset($this->_data['id']);
$this->_outData = array();
$this->_outData['calc_date'] = date('Y-m-d', strtotime($this->_data['date']));
$this->_outData['date'] = siLocal::date($this->_data['date']);
$this->_outData['total'] = $invoiceItems->getInvoiceTotal($this->_id);
$this->_outData['gross'] = $invoiceItems->getGrossForInvoice($this->_id);
$this->_outData['paid'] = $payments->getPaidAmountForInvoice($this->_id);
$this->_outData['owing'] = $this->_outData['total'] - $this->_outData['paid'];
if (isset($this->_data['inv_status'])) {
// This seems to be a thing of the past.
// I think we could delete the whole "if".
$this->_outData['status'] = $this->_data['inv_status'];
} else {
$this->_outData['status'] = '';
}
#invoice total tax
$result = $invoiceItems->getTotals($this->_id);
//$invoice['total'] = number_format($result['total'],2);
$this->_outData['total_tax'] = $result['total_tax'];
$this->_outData['tax_grouped'] = taxesGroupedForInvoice($id);
}
public function select($id, $domain_id = '')
{
global $logger;
if (!empty($domain_id)) {
$this->domain_id = $domain_id;
}
$sql = "SELECT \n i.*,\n\t\t i.date as date_original, \n (SELECT CONCAT(p.pref_inv_wording,' ',i.index_id)) as index_name,\n p.pref_inv_wording AS preference,\n p.status\n FROM \n " . TB_PREFIX . "invoices i LEFT JOIN \n\t\t\t\t\t" . TB_PREFIX . "preferences p \n\t\t\t\t\t\tON (i.preference_id = p.pref_id AND i.domain_id = p.domain_id)\n WHERE \n i.domain_id = :domain_id \n AND \n\t\t\t\t\ti.id = :id";
$sth = dbQuery($sql, ':id', $id, ':domain_id', $this->domain_id);
$invoice = $sth->fetch();
$invoice['calc_date'] = date('Y-m-d', strtotime($invoice['date']));
$invoice['date'] = siLocal::date($invoice['date']);
$invoice['total'] = getInvoiceTotal($invoice['id'], $domain_id);
$invoice['gross'] = $this->getInvoiceGross($invoice['id'], $this->domain_id);
$invoice['paid'] = calc_invoice_paid($invoice['id'], $domain_id);
$invoice['owing'] = $invoice['total'] - $invoice['paid'];
$invoice['invoice_items'] = $this->getInvoiceItems($id, $this->domain_id);
#invoice total tax
$sql2 = "SELECT SUM(tax_amount) AS total_tax, SUM(total) AS total FROM " . TB_PREFIX . "invoice_items WHERE invoice_id = :id AND domain_id = :domain_id";
$sth2 = dbQuery($sql2, ':id', $id, ':domain_id', $this->domain_id);
$result2 = $sth2->fetch();
//$invoice['total'] = number_format($result['total'],2);
$invoice['total_tax'] = $result2['total_tax'];
$invoice['tax_grouped'] = taxesGroupedForInvoice($id);
return $invoice;
}
/**
* Initialize payment data.
* replaces method getPayment($id)
*/
protected function _initData()
{
$tbl_prefix = SimpleInvoices_Db_Table_Abstract::getTablePrefix();
$select = new Zend_Db_Select($this->_db);
$select->from($tbl_prefix . 'payment');
$select->joinInner($tbl_prefix . 'invoices', $tbl_prefix . "payment.ac_inv_id = " . $tbl_prefix . "invoices.id", NULL);
$select->joinInner($tbl_prefix . 'customers', $tbl_prefix . "invoices.customer_id = " . $tbl_prefix . "customers.id", array('customer_id' => $tbl_prefix . "customers.id", 'customer' => $tbl_prefix . "customers.name"));
$select->joinInner($tbl_prefix . 'biller', $tbl_prefix . "invoices.biller_id = " . $tbl_prefix . "biller.id", array('biller_id' => $tbl_prefix . "biller.id", 'biller' => $tbl_prefix . "biller.name"));
$select->where($tbl_prefix . 'payment.id=?', $this->_id);
$this->_data = $this->_db->fetchRow($select);
$this->_data['date'] = siLocal::date($payment['ac_date']);
}
public static function select($id)
{
global $logger;
global $db;
global $auth_session;
$sql = "SELECT \n i.*,\n\t\t i.date as date_original, \n (SELECT CONCAT(p.pref_inv_wording,' ',i.index_id)) as index_name,\n p.pref_inv_wording AS preference,\n p.status\n FROM \n " . TB_PREFIX . "invoices i, \n " . TB_PREFIX . "preferences p \n WHERE \n i.domain_id = :domain_id \n and\n i.preference_id = p.pref_id\n and \n i.id = :id";
$sth = $db->query($sql, ':id', $id, ':domain_id', $auth_session->domain_id);
$invoice = $sth->fetch();
$invoice['calc_date'] = date('Y-m-d', strtotime($invoice['date']));
$invoice['date'] = siLocal::date($invoice['date']);
$invoice['total'] = getInvoiceTotal($invoice['id']);
$invoice['gross'] = invoice::getInvoiceGross($invoice['id']);
$invoice['paid'] = calc_invoice_paid($invoice['id']);
$invoice['owing'] = $invoice['total'] - $invoice['paid'];
$invoice['invoice_items'] = invoice::getInvoiceItems($id);
#invoice total tax
$sql2 = "SELECT SUM(tax_amount) AS total_tax, SUM(total) AS total FROM " . TB_PREFIX . "invoice_items WHERE invoice_id = :id";
$sth2 = dbQuery($sql2, ':id', $id) or die(htmlsafe(end($dbh->errorInfo())));
$result2 = $sth2->fetch();
//$invoice['total'] = number_format($result['total'],2);
$invoice['total_tax'] = $result2['total_tax'];
$invoice['tax_grouped'] = taxesGroupedForInvoice($id);
return $invoice;
}
// Alternatively: The Owing column can have the link on the amount instead of the payment icon code here
if ($row['status'] && $row['owing'] > 0) {
// Real Invoice Has Owing - Process payment
$xml .= "<!--6 Payment --><a title='" . $LANG['process_payment_for'] . " " . $row['preference'] . " " . $row['index_id'] . "' class='index_table' href='index.php?module=payments&view=process&id=" . $row['id'] . "&op=pay_selected_invoice'><img src='images/common/money_dollar.png' class='action' /></a>";
} elseif ($row['status']) {
// Real Invoice Payment Details if not Owing (get different color payment icon)
$xml .= "<!--6 Payment --><a title='" . $LANG['process_payment_for'] . " " . $row['preference'] . " " . $row['index_id'] . "' class='index_table' href='index.php?module=payments&view=details&id=" . $row['id'] . "&action=view'><img src='images/common/money_dollar.png' class='action' /></a>";
} else {
// Draft Invoice Just Image to occupy space till blank or greyed out icon becomes available
$xml .= "<!--6 Payment --><img src='images/common/money_dollar.png' class='action' />";
}
$xml .= "<!--7 Email --><a title='" . $LANG['email'] . " " . $row['preference'] . " " . $row['index_id'] . "' class='index_table' href='index.php?module=invoices&view=email&stage=1&id=" . $row['id'] . "'><img src='images/common/mail-message-new.png' class='action' /></a>\n\t\t\t]]>\n\t\t\t\t</cell>";
$xml .= "<cell><![CDATA[" . $row['index_name'] . "]]></cell>";
$xml .= "<cell><![CDATA[" . $row['biller'] . "]]></cell>";
$xml .= "<cell><![CDATA[" . $row['customer'] . "]]></cell>";
$xml .= "<cell><![CDATA[" . siLocal::date($row['date']) . "]]></cell>";
$xml .= "<cell><![CDATA[" . siLocal::number($row['invoice_total']) . "]]></cell>";
if ($row['status']) {
$xml .= "<cell><![CDATA[" . siLocal::number($row['owing']) . "]]></cell>";
$xml .= "<cell><![CDATA[" . $row['aging'] . "]]></cell>";
} else {
$xml .= "<cell><![CDATA[ ]]></cell>";
$xml .= "<cell><![CDATA[ ]]></cell>";
}
$xml .= "<cell><![CDATA[" . $row['preference'] . "]]></cell>";
$xml .= "</row>";
}
$xml .= "</rows>";
echo $xml;
?>
function getCustomerInvoices($id, $domain_id = '')
{
global $config;
$domain_id = domain_id::get($domain_id);
// tested for MySQL
$sql = "SELECT\t\n\t\tiv.id, \n\t\tiv.index_id, \n\t\tiv.date, \n\t\tiv.type_id, \n\t\t(SELECT SUM( COALESCE(ii.total, 0)) FROM " . TB_PREFIX . "invoice_items ii WHERE ii.invoice_id = iv.id AND ii.domain_id = iv.domain_id) AS invd,\n\t\t(SELECT SUM( COALESCE(ap.ac_amount, 0)) FROM " . TB_PREFIX . "payment ap WHERE ap.ac_inv_id = iv.id AND ap.domain_id = iv.domain_id) AS pmt,\n\t\t(SELECT COALESCE(invd, 0)) As total, \n\t\t(SELECT COALESCE(pmt, 0)) As paid, \n\t\t(select (total - paid)) as owing,\n\t\tpr.status,\n\t\tpr.pref_inv_wording\n\tFROM \n\t\t" . TB_PREFIX . "invoices iv\n\t\tLEFT JOIN " . TB_PREFIX . "preferences pr ON (pr.pref_id = iv.preference_id AND pr.domain_id = iv.domain_id)\n\tWHERE \n\t\tiv.customer_id = :id\n\tAND iv.domain_id = :domain_id\n\tORDER BY \n\t\tiv.id DESC;";
$sth = dbQuery($sql, ':id', $id, ':domain_id', $domain_id);
$invoices = null;
while ($invoice = $sth->fetch()) {
$invoice['calc_date'] = date('Y-m-d', strtotime($invoice['date']));
$invoice['date'] = siLocal::date($invoice['date']);
$invoices[] = $invoice;
}
return $invoices;
}
function getCustomerInvoices($id)
{
global $dbh;
global $config;
global $auth_session;
// tested for MySQL
$sql = "SELECT\t\n\t\ti.id, \n\t\ti.date, \n\t\ti.type_id, \n\t\t(SELECT sum( COALESCE(ii.total, 0)) FROM " . TB_PREFIX . "invoice_items ii where ii.invoice_id = i.id) As invd,\n\t\t(SELECT sum( COALESCE(ap.ac_amount, 0)) FROM " . TB_PREFIX . "payment ap where ap.ac_inv_id = i.id) As pmt,\n\t\t(SELECT COALESCE(invd, 0)) As total, \n\t\t(SELECT COALESCE(pmt, 0)) As paid, \n\t\t(select (total - paid)) as owing \n\tFROM \n\t\t" . TB_PREFIX . "invoices i \n\tWHERE \n\t\ti.customer_id = :id\n\t\tand\n\t\ti.domain_id = :domain_id\n\tORDER BY \n\t\ti.id DESC;";
$sth = dbQuery($sql, ':id', $id, ':domain_id', $auth_session->domain_id) or die(htmlsafe(end($dbh->errorInfo())));
$invoices = null;
while ($invoice = $sth->fetch()) {
$invoice['calc_date'] = date('Y-m-d', strtotime($invoice['date']));
$invoice['date'] = siLocal::date($invoice['date']);
$invoices[] = $invoice;
}
return $invoices;
}