static function listTaxLines()
{
$sql = "SELECT account_tax_line.id, account_tax_form.name || ': ' || account_tax_line.line || ' - ' || account_tax_line.name AS name ";
$sql .= ' FROM account_tax_line';
$sql .= ' JOIN account_tax_form ON account_tax_line.account_tax_form_id = account_tax_form.id';
$sql .= ' ORDER BY account_tax_form.name,account_tax_line.sort';
$res = SQL::fetch_all($sql);
return $res;
// Specific Form?
/*
$list = array();
$rs = $this->query("select id,name from account_tax_line where form = '" . pg_escape_string($form) ."' order by sort");
foreach ($rs as $x) {
$list[$x[0]['id']] = $x[0]['name'];
}
return $list;
*/
}
// Base_Diff::note($this->Invoice, $this->_s->info);
}
$_ENV['invoice']['id'] = $this->Invoice['id'];
$this->Contact = new Contact($this->Invoice['contact_id']);
$this->ContactAddressList = SQL::fetch_mix('select id,address from contact_address where contact_id = ?', array($this->Invoice['contact_id']));
$this->InvoiceItemList = $this->Invoice->getInvoiceItems();
$this->InvoiceNoteList = $this->Invoice->getNotes();
$this->InvoiceFileList = $this->Invoice->getFiles();
$this->InvoiceHistoryList = $this->Invoice->getHistory();
$this->InvoiceTransactionList = $this->Invoice->getTransactions();
// Add Prev / Next Links
$this->jump_list = array();
if (!empty($this->Invoice['id'])) {
// Previous Ones
$s = sprintf('SELECT id FROM invoice where id < %d order by id desc limit 5', $this->Invoice['id']);
$r = SQL::fetch_all($s);
$r = array_reverse($r);
foreach ($r as $x) {
$this->jump_list[] = array('controller' => 'invoice', 'action' => 'view', 'id' => $x['id']);
}
// This One
$this->jump_list[] = array('controller' => 'invoice', 'action' => 'view', 'id' => $this->Invoice['id']);
// Next Ones
$s = sprintf('SELECT id FROM invoice where id > %d order by id asc limit 5', $this->Invoice['id']);
$r = SQL::fetch_all($s);
foreach ($r as $x) {
$this->jump_list[] = array('controller' => 'invoice', 'action' => 'view', 'id' => $x['id']);
}
}
// $this->_s->Invoice = $this->view->Invoice;
$_SESSION['invoice'] = $this->Invoice;
//
// if ($p->count() == 0) {
// $title[] = 'Contacts';
// } else {
// $a_id = $p->getItem(1)->name;
// $z_id = $p->getItem($p->getCurrentItemCount())->name;
//
// $title[] = sprintf('%s through %s',$a_id,$z_id);
// }
// $title[] = sprintf('Page %d of %d',$page->getCurrentPageNumber(),$page->count());
$_ENV['title'] = $title;
// $this->view->Page = $p;
/*
$rq = $this->getRequest();
// Automatic Query?
if ($a = $rq->getQuery('a')) {
}
//$ss->where('kind_id in (100,300)');
$sql->order(array('contact','company'));
$sql->limitPage($this->view->Paginator->page,$this->view->Paginator->limit);
// View!
$this->view->ContactList = $db->fetchAll($sql);
$this->view->title = array(
'Contacts',
'Page ' . $this->view->Paginator->page . ' of ' . $this->view->Paginator->pmax,
);
*/
$this->ContactList = SQL::fetch_all($sql, $arg);
Session::flash('fail', SQL::lastError());
$where = " (date>='{$this->date_alpha}' and date<='{$this->date_omega}') ";
$order = " date,kind, account_journal_id, amount asc ";
$this->dr_total = SQL::fetch_one("select sum(amount) from general_ledger where amount < 0 and {$where}");
$this->cr_total = SQL::fetch_one("select sum(amount) from general_ledger where amount > 0 and {$where}");
$this->Account = new Account(array('name' => 'General Ledger'));
} else {
// Show this specific Account
$_SESSION['account-id'] = $this->Account['id'];
$this->openBalance = $this->Account->balanceBefore($this->date_alpha);
$where = " (account_id = ? OR parent_id = ?) AND (date >= ? AND date <= ?) ";
$param = array($this->Account['id'], $this->Account['id'], $this->date_alpha, $this->date_omega);
$order = " date,kind desc,amount asc ";
//$this->AccountLedger = $data;
$this->dr_total = abs($this->Account->debitTotal($this->date_alpha, $this->date_omega));
$this->cr_total = abs($this->Account->creditTotal($this->date_alpha, $this->date_omega));
}
if (strlen($_GET['link'])) {
// $l = ImperiumBase::getObjectType($o)
$l = Base_Link::load($_GET['link']);
$link_to = Base_Link::getObjectType($l, 'id');
// Get Object Type ID
$link_id = $l->id;
if (!empty($link_to) && !empty($link_id)) {
$where .= sprintf(' and link_to = %d and link_id = %d ', $link_to, $link_id);
}
}
$sql = "SELECT * FROM general_ledger WHERE {$where} ORDER BY {$order}";
$res = SQL::fetch_all($sql, $param);
$this->LedgerEntryList = $res;
// ImperiumView::mruAdd($this->link(),'Ledger ' . $this->Account->name);
$_SESSION['return-path'] = '/account/ledger';
function getTransactions()
{
// Transaction
if (intval($this->id) == 0) {
return null;
}
$sql = 'SELECT al.id,al.account_id,al.amount,aj.id as account_journal_id,aj.date,aj.note,a.name as account_name ';
$sql .= ' from account_ledger al ';
$sql .= ' join account_journal aj on al.account_journal_id = aj.id ';
$sql .= ' join account a on al.account_id = a.id ';
// KIND needs to be A/R + Asset //
$sql .= ' WHERE ';
// $sql.= " a.kind = 'Asset: Accounts Receivable' AND ";
$sql .= sprintf(' al.link_to = %d AND al.link_id = %d', self::getObjectType($this), $this->id);
$sql .= ' ORDER BY aj.date ASC, al.amount DESC';
$res = SQL::fetch_all($sql);
return $res;
}
/**
Contact getChannelList()
*/
function getChannelList()
{
// Now Get Child Emails if Company?
$sql = 'SELECT * FROM contact_channel ';
// WHERE kind = 200 ';
$sql .= ' WHERE contact_id IN (SELECT id FROM contact WHERE id = ? OR parent_id = ? ) ';
$sql .= ' ORDER BY contact_id, name ';
// $res = $db->fetchAll($sql);
$res = SQL::fetch_all($sql, array($this->_data['id'], $this->_data['id']));
$list = array();
foreach ($res as $x) {
$list[] = new ContactChannel($x);
}
return $list;
}
<?php
/**
InvoiceController indexAction
*/
namespace Edoceo\Imperium;
use Edoceo\Radix\DB\SQL;
if (empty($_GET['size'])) {
$_GET['size'] = 50;
}
// Get Counts
$this->page_max = SQL::fetch_one('SELECT count(id) FROM invoice');
$this->page_cur = min(max(1, $_GET['page']), $this->page_max);
$_GET['size'] = min(max(20, $_GET['size']), 100);
$sql = 'SELECT invoice.*, contact.name as contact_name ';
$sql .= ' FROM invoice ';
$sql .= ' JOIN contact ON invoice.contact_id=contact.id ';
$sql .= ' ORDER BY invoice.date DESC, invoice.id DESC ';
$sql .= ' OFFSET ' . ($this->page_cur - 1) * $_GET['size'];
$sql .= ' LIMIT ' . $_GET['size'];
$this->list = SQL::fetch_all($sql);
$a_id = $this->list[0]['id'];
$z_id = $this->list[count($this->list) - 1]['id'];
$title = array();
$title[] = sprintf('Invoices %d through %d', $a_id, $z_id);
$title[] = sprintf('Page %d of %d', $this->page_cur, ceil($this->page_max / $_GET['size']));
$_ENV['title'] = $title;
/**
*/
namespace Edoceo\Imperium;
use Edoceo\Radix\DB\SQL;
if (empty($_GET['c'])) {
return 0;
}
$c = new Contact(intval($_GET['c']));
if (empty($c['id'])) {
Session::flash('fail', 'Contact not found');
// Radix::redirect('/contact');
}
$_ENV['contact'] = $c;
$this->Contact = $c;
// Why Pointing this way?
$this->Account = $c->getAccount();
$this->ContactList = array();
if (empty($c->parent_id)) {
// $this->ContactList = SQL::fetch_all("SELECT * FROM contact WHERE id != ? AND (parent_id = ? OR company = ?)",array($c->id,$c->id,$c->company));
$this->ContactList = SQL::fetch_all('SELECT * FROM contact WHERE id != ? AND parent_id = ?', array($c->id, $c->id));
}
$this->ContactAddressList = $c->getAddressList();
$this->ContactChannelList = $c->getChannelList();
$this->ContactNoteList = $c->getNotes();
$this->ContactFileList = $c->getFiles();
// @note what does order by star, status do? Join base_enum?
$this->WorkOrderList = SQL::fetch_all('SELECT workorder.*, contact.name AS contact_name FROM workorder JOIN contact ON workorder.contact_id = contact.id WHERE workorder.contact_id = ? ORDER BY workorder.date DESC, workorder.id DESC', array($c['id']));
$this->InvoiceList = SQL::fetch_all('SELECT * FROM invoice WHERE contact_id = ? ORDER BY date DESC, id DESC', array($c['id']));
$_ENV['title'] = array($this->Contact['kind'], sprintf('#%d:%s', $this->Contact['id'], $this->Contact['name']));
/**
ImperiumBase getNotes()
*/
function getNotes()
{
if (intval($this['id']) == 0) {
return null;
}
$sql = 'SELECT * FROM base_note WHERE link = ? ORDER BY name';
$arg = array($this->link());
$ret = SQL::fetch_all($sql, $arg);
return $ret;
}
/**
getWorkOrderItems()
*/
function getWorkOrderItems($where = null)
{
$sql = 'SELECT * FROM workorder_item WHERE workorder_id = ? ';
$sql .= ' ORDER BY status, date, kind, a_rate, a_quantity ';
$res = SQL::fetch_all($sql, array($this->_data['id']));
$ret = array();
foreach ($res as $x) {
$ret[] = new WorkOrderItem($x);
}
return $ret;
}
$arg[] = $q;
$sql .= ' OR contact_channel.data #op# ?';
$arg[] = $q;
$sql .= ' OR contact_meta.val #op# ?';
$arg[] = $q;
$sql .= ' ORDER BY contact.name';
if (preg_match('/[_%]/', $q)) {
$sql = str_replace('#op#', 'LIKE', $sql);
} elseif (preg_match('/[\\.\\*\\+\\?]/', $q)) {
$sql = str_replace('#op#', '~*', $sql);
} else {
$sql = str_replace('#op#', '=', $sql);
}
// Radix::dump($sql);
// Radix::dump($arg);
$res = SQL::fetch_all($sql, $arg);
// Radix::dump(SQL::lastError());
// Radix::dump($res);
foreach ($res as $rec) {
$idx++;
echo '<dt><a href="' . Radix::link('/contact/view?c=' . $rec['id']) . '">Contact: ' . $rec['name'] . '</a></dt>';
}
echo '</dl>';
if ($idx == 0) {
_draw_rebuild_prompt();
return 0;
}
$_ENV['title'] = array('Search', $q, $idx == 1 ? '1 result' : $idx . ' results');
/**
Draw the Rebuild Button
*/
continue;
}
$info = $_ENV['data'][$name];
if (count($info['list']) > 0) {
// echo "<div style='display: table-cell;'>";
echo '<div>';
// style='display: table-cell;'>";
echo sprintf('<h2>%d %s</h2>', count($info['list']), $name);
echo Radix::block($info['view'], array('list' => $info['list'], 'opts' => array('head' => true)));
echo '</div>';
// Radix::dump($info);
}
}
// Show the Events
$sql = 'SELECT contact_event.*, contact.name AS contact_name FROM contact_event';
$sql .= ' JOIN contact ON contact_event.contact_id = contact.id';
// $sql.= ' WHERE flag = 0';
$sql .= ' ORDER BY contact_event.xts DESC';
$sql .= ' LIMIT 20';
$res = SQL::fetch_all($sql);
foreach ($res as $rec) {
echo '<p>';
echo '<a href="' . Radix::link('/contact/view?c=' . $rec['contact_id']) . '">' . html($rec['contact_name']) . '</a>';
echo ' - ';
echo html($rec['name']);
echo ' - ';
echo html($rec['note']);
echo '</p>';
echo '<p>Due: ' . strftime('%Y-%m-%d %H:%M', $rec['xts']) . '</p>';
// Radix::dump($rec);
}
$_ENV['title'] = 'Dashboard: ' . date('Y-m-d');
$sql_w = 'SELECT workorder.*, b.name AS contact_name ';
$sql_w .= ' FROM workorder ';
$sql_w .= ' JOIN contact b ON workorder.contact_id=b.id ';
$sql_w .= ' JOIN base_enum ON workorder.kind = base_enum.name ';
$sql_w .= " WHERE workorder.status in ('Active','Pending') ";
$sql_w .= ' ORDER BY base_enum.sort, workorder.status, workorder.date desc, workorder.id DESC';
// Pending Work Order Items
$sql_woi = 'SELECT workorder.*, contact.name AS contact_name ';
$sql_woi .= ' FROM workorder ';
$sql_woi .= ' JOIN contact on workorder.contact_id = contact.id ';
$sql_woi .= ' JOIN workorder_item ON workorder.id = workorder_item.workorder_id ';
$sql_woi .= ' JOIN base_enum ON workorder.kind = base_enum.name ';
$sql_woi .= " WHERE workorder.status = 'Active' AND workorder_item.status = 'Pending' ";
$sql_woi .= ' ORDER BY base_enum.sort, workorder.status, workorder.date desc, workorder.id DESC';
$data = array('Pending Work Order Items' => array('css' => 'index_pack', 'list' => SQL::fetch_all($sql_woi), 'view' => 'workorder-list'), 'Active Work Orders' => array('css' => 'index_list', 'list' => SQL::fetch_all($sql_w), 'view' => 'workorder-list'), 'Active Invoices' => array('css' => 'index_list', 'list' => SQL::fetch_all("select invoice.*,b.name as contact_name from invoice join contact b on invoice.contact_id=b.id where ((invoice.paid_amount is null or invoice.paid_amount < invoice.bill_amount) and invoice.status in ('Active','Sent','Hawk')) order by invoice.date desc, invoice.id desc"), 'view' => 'invoice-list'));
/*
$this->paginate = array(
'WorkOrder' => array(
'conditions' => 'WorkOrder.status_id in (100,200)',
'limit'=>50,
'order' => array('WorkOrder.id'=>'desc','WorkOrder.date'=>'desc'),
'page'=>1,
'recursive'=>1,
),
'Invoice' => array(
'conditions' => '((Invoice.paid_amount is null or Invoice.paid_amount<Invoice.bill_amount) and Invoice.status_id in (100,200))',
'limit'=>50,
'order' => array('Invoice.date'=>'desc'),
'page'=>1,
'recursive'=>0,
