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,