/**
Delete Ledger & Journal Entries
*/
function delete()
{
$arg = array(intval($this->_data['id']));
SQL::query('DELETE FROM account_ledger where account_journal_id = ?', $arg);
SQL::query('DELETE FROM account_journal where id = ?', $arg);
return true;
}
static function update()
{
foreach (self::$obj_list as $tab => $tab_spec) {
// Add ft (Full Text) column
$sql = 'DELETE FROM full_text WHERE link_to = ?';
$arg = array($tab);
SQL::query($sql, $arg);
// Update Desired Columns & Records to that Field
$buf = array();
foreach ($tab_spec['cols'] as $col) {
$buf[] = " coalesce({$col}::text,'') ";
}
$sql = "INSERT INTO full_text ";
$sql .= " SELECT '{$tab}',id, ";
switch ($tab) {
case 'contact':
$sql .= " kind || ': ' || CASE WHEN kind = 'Person' THEN contact ELSE company END, ";
break;
default:
$sql .= " '" . $tab_spec['name'] . " #' || id,";
}
$sql .= ' (' . implode(" || ' ' || ", $buf) . '), ';
$sql .= 'to_tsvector(' . implode(" || ' ' || ", $buf) . ')';
$sql .= " FROM {$tab}";
// echo "$sql\n";
SQL::query($sql);
}
}
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;
*/
}
App::load_config();
// Zend Locale
// $locale = new Zend_Locale('en');
// Zend_Locale::setDefault('en_US');
// Zend_Registry::set('Zend_Locale', new Zend_Locale('en_US'));
// Date Options
// Zend_Date::setOptions(array('extend_month' => false,'format_type'=>'iso'));
// Zend Database
//$x = $_ENV['database'];
//unset($x['adapter']);
//$x = Zend_Db::factory($_ENV['database']['adapter'],$x);
//$x->setFetchMode(Zend_Db::FETCH_OBJ);
//// set client_encoding='utf-8';
//Zend_Registry::set('db',$x);
//Zend_Db_Table_Abstract::setDefaultAdapter($x);
SQL::init("pgsql:host={$_ENV['database']['hostname']};dbname={$_ENV['database']['database']}", $_ENV['database']['username'], $_ENV['database']['password']);
// App::$db = new radix_db_sql();
/**
Internal Hax0r Functions
*/
function html($x)
{
return htmlspecialchars($x, ENT_QUOTES, 'UTF-8', false);
}
// @deprecated
function img($img, $alt = null)
{
$src = !empty($_SERVER['HTTPS']) ? 'https:' : 'http:';
$src .= '//gcdn.org/' . ltrim($img, '/');
if (empty($alt)) {
$alt = strtok(basename($img), '.');
<?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;
<?php
/**
WorkorderController init
Sets the ACL for this Controller
*/
namespace Edoceo\Imperium;
use Edoceo\Radix\DB\SQL;
//$acl = Zend_Registry::get('acl');
//if ($acl->has('workorder') == false) {
// $acl->add( new Zend_Acl_Resource('workorder') );
//}
//$acl->allow('user','workorder');
// parent::init();
$sql = 'SELECT name AS id,name FROM base_enum WHERE link = ? ORDER BY sort';
$this->StatusList = SQL::fetch_mix($sql, array('invoice-status'));
$this->ItemStatusList = SQL::fetch_mix($sql, array('invoice-item-status'));
$this->UnitList = Base_Unit::getList();
*/
namespace Edoceo\Imperium;
use Edoceo\Radix;
use Edoceo\Radix\Session;
use Edoceo\Radix\DB\SQL;
switch (strtolower($_POST['a'])) {
case 'sign in':
if (!acl::may('/auth/sign-in', 'POST')) {
Session::flash('fail', 'Access Denied');
Radix::redirect('/auth/sign-in');
}
$sql = 'SELECT * FROM auth_user WHERE username = ? ';
$sql .= ' AND (password = ? OR password = ? )';
$arg = array(strtolower($_POST['username']), $_POST['password'], sha1($_POST['username'] . $_POST['username']));
$res = SQL::fetch_row($sql, $arg);
if (empty($res)) {
// @todo Random Sleep
Session::flash('fail', 'Invalid username or password');
Radix::redirect();
}
// Radix::dump($res);
$_SESSION['uid'] = $res['id'];
acl::permit('/index');
acl::permit('/dashboard');
acl::permit('/search');
acl::permit('/block*');
acl::permit('/email*');
acl::permit('/file*');
acl::permit('/note*');
acl::permit('/account*');
// 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;
$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';
/**
@param $k Key
@param $v Value
*/
function setMeta($k, $v)
{
if (empty($this->_data['id'])) {
// Save for the future
$this->_meta[$k] = $v;
return;
}
$sql = 'SELECT id FROM contact_meta';
$sql .= ' WHERE contact_id = ? AND key = ?';
$arg = array($this->_data['id'], $k);
$chk = SQL::fetch_one($sql, $arg);
if ($chk) {
$sql = 'UPDATE contact_meta SET val = ? WHERE contact_id = ? AND key = ?';
$arg = array($v, $this->_data['id'], $k);
} else {
$sql = 'INSERT INTO contact_meta (contact_id, key, val) VALUES (?, ?, ?)';
$arg = array($this->_data['id'], $k, $v);
}
return $res;
}
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);
}
static function getList()
{
$sql = 'select id,name from base_unit order by name';
return SQL::fetch_mix($sql);
}
$list = array(0 => '- None -');
$AccountTaxLineList = AccountTaxFormLine::listTaxLines();
foreach ($AccountTaxLineList as $x) {
$list[$x['id']] = $x['name'];
}
// Radix::dump($AccountTaxLineList);
// $list += $AccountTaxLineList;
echo "<tr><td class='b r'>Tax Line:</td><td colspan='3'>" . Form::select('account_tax_line_id', $this->Account->account_tax_line_id, $list) . "</td>";
/*
// Asset Details
echo "<tr><td class='b r'>Opening Balance:</td><td>" . $imperiumForm->input('Account.code',am($opts,array('class'=>'tb','size'=>8))) . "</td></tr>";
*/
// Kind Bank Account Details
echo "<tr><td class='b r'>Transit:</td><td colspan='2'>" . Form::text('bank_routing', $this->Account->bank_routing) . '</td></tr>';
echo "<tr><td class='b r'>Account:</td><td colspan='2'>" . Form::text('bank_account', $this->Account->bank_account) . '</td></tr>';
echo '</table>';
/*
echo $imperiumForm->checkbox('Income Statement');
echo $imperiumForm->checkbox('Equity Statement');
echo $imperiumForm->checkbox('Balance Sheet');
echo $imperiumForm->checkbox('Cash Flow');
*/
echo '<div class="cmd">';
echo Form::hidden('id', $this->Account['id']);
echo '<input name="a" type="submit" value="Save">';
echo '<input name="a" type="submit" value="Delete">';
echo '</div>';
echo '</form>';
// Show Transaction Count
$res = SQL::fetch_one('SELECT count(id) FROM account_ledger WHERE account_id = ?', array($this->Account['id']));
echo '<p>' . $res . ' total transactions in this account</p>';
$_ENV['mode'] = 'view';
if ($_FILES['file']['error'] == 0) {
$this->Account = new Account($_POST['account_id']);
$_ENV['title'] = array('Account', 'Reconcile', $this->Account['full_name'], 'Preview');
// Read File
$arg = array('kind' => $_POST['format'], 'file' => $_FILES['file']['tmp_name'], 'account_id' => $_POST['upload_id']);
$this->JournalEntryList = Account_Reconcile::parse($arg);
} else {
Session::flash('fail', 'Failed to Upload');
}
// @todo If the Target Account is Asset then Other Side Only (and vice-versa)
$sql = 'SELECT id,full_name ';
$sql .= 'FROM account ';
// $sql.= "WHERE kind like 'Expense%' ";
$sql .= 'ORDER BY full_code ASC, code ASC';
$this->AccountPairList = SQL::fetch_mix($sql);
$_SESSION['reconcile_upload_id'] = $_POST['upload_id'];
$_SESSION['reconcile_offset_id'] = $_POST['offset_id'];
$_ENV['upload_account_id'] = $_SESSION['reconcile_upload_id'];
$_ENV['offset_account_id'] = $_POST['offset_id'];
break;
case 'save':
// Save the Uploaded Transactions
$_ENV['upload_account_id'] = $_SESSION['reconcile_upload_id'];
Radix::dump($_POST);
return 0;
$c = ceil(count($_POST) / 4);
for ($i = 1; $i <= $c; $i++) {
// Skip Entries Missing Date (or the last of the count)
if (empty($_POST[sprintf('je%ddate', $i)])) {
continue;
$_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,
/**
*/
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']));
/**
Update Balance
Updates the Invoice Balance after it's been saved
*/
private function _updateBalance()
{
$id = intval($this->_data['id']);
$sql = 'UPDATE invoice SET';
$sql .= ' sub_total = ( SELECT SUM ( quantity * rate * (1 + tax_rate)) FROM invoice_item WHERE invoice_id = ?)';
$sql .= ', tax_total = ( SELECT SUM ( quantity * rate * tax_rate) FROM invoice_item WHERE invoice_id = ?)';
$sql .= ', bill_amount = ( SELECT SUM ( quantity * rate * (1 + tax_rate)) FROM invoice_item WHERE invoice_id = ?) ';
// $sql.= ', paid_amount =
$sql .= ' WHERE id = ? ';
SQL::query($sql, array($id, $id, $id, $id));
// die(SQL::lastError());
// $r = array();
// $r['sub_total'] = floatval($d->fetchOne("select sum( quantity * rate ) as sub_total from invoice_item where invoice_id={$id}"));
// $r['tax_total'] = floatval($d->fetchOne("select sum( quantity * rate * tax_rate) as tax_total from invoice_item where invoice_id={$id}"));
// $sql.= ' sub_total = ?, ';
// $arg[] = floatval(SQL::fetch_one("select sum( quantity * rate * tax_rate) as tax_total from invoice_item where invoice_id={$id}"));
// // $r['bill_amount'] = $r['sub_total'] + $r['tax_total'];
// $sql.= ' bill_amount = ?, ';
// $arg[] = $r['sub_total'] + $r['tax_total'];
// // $r['paid_amount'] = $this->getTransactionSum();
// $sql.= ' paid_amount = ? ';
// $arg[] = $this->getTransactionSum();
// @todo Force Marking as Paid Amount Full?
// if ($this->status == 'Paid') {
// $r['paid_amount'] = $r['bill_amount'];
// }
// $w = array('id = ?'=>$this->id);
// $t = new Zend_Db_Table(array('name'=>'invoice'));
// $t->update($r,$w);
// @todo Save to Object Data?
// $this->bill_amount = $r['bill_amount'];
// $this->paid_amount = $r['paid_amount'];
// $this->sub_total = $r['sub_total'];
// $this->tax_total = $r['tax_total'];
}
$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
*/
/**
Work Order Update Balance
@todo handle totals differently for Subscription vs One-Time Work Orders
*/
private function _updateBalance()
{
$id = $this->_data['id'];
$sql = 'update workorder set ';
$sql .= 'bill_amount = (';
$sql .= "select sum(a_quantity * a_rate) from workorder_item ";
$sql .= " where workorder_id={$id} and status = 'Billed' ) ";
$sql .= ',';
$sql .= 'open_amount = (';
$sql .= 'select sum(a_quantity * a_rate) from workorder_item ';
$sql .= " where workorder_id = {$id} and status in ('Active','Complete') ";
$sql .= ") where id={$id}";
SQL::query($sql);
$this->bill_amount = SQL::fetch_one("SELECT bill_amount FROM workorder WHERE id = {$id}");
$this->open_amount = SQL::fetch_one("SELECT open_amount FROM workorder WHERE id = {$id}");
}
/**
getObjectType
@param $o is the Object, ObjectName or ObjectInteger
*/
static function getObjectType($o, $r = null)
{
$arg = array();
$sql = 'SELECT * FROM base_object ';
// Convert Object to String to use String Comp below
if (is_object($o)) {
$o = strtolower(get_class($o));
}
if (intval($o) > 0) {
$sql .= ' WHERE id = ?';
$arg[] = intval($o);
if (empty($r)) {
$r = 'name';
}
} elseif (is_string($o)) {
$o = strtolower($o);
$sql .= ' WHERE stub = ? OR path = ? OR link = ? ';
$arg[] = $o;
$arg[] = $o;
$arg[] = $o;
if (empty($r)) {
$r = 'id';
}
}
// Find and Return Value
$ot = SQL::fetch_row($sql);
if ($ot) {
switch ($r) {
case 'id':
return $ot->id;
case 'link':
return $ot->link;
case 'name':
return $ot->name;
case 'path':
return $ot->path;
case 'stub':
return $ot->stub;
default:
return $ot;
}
}
//throw new Exception('Cannot Handle Object Type ' . get_class($o) . '/' . $r . '[' . $sql->assemble() . ']');
return null;
}
// 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());
/**
*/
static function parse($opt)
{
$ret = array();
// Read the Line in the Format
switch ($opt['kind']) {
case 'csvwfb':
// Wells Fargo CSV Format
$ret = self::_parseWellsFargo($opt['file']);
break;
case 'paypal':
// 0 = Date
// 4 = Note
// 6 = Gross
// 7 = Fee
// 8 = Net
// 11 = Transaction ID
// Zend_Debug::dump($_FILES['file']);
$fh = fopen($_FILES['file']['tmp_name'], 'r');
while ($csv = fgetcsv($fh, 4096)) {
// Ledger Entry for Paypal Deposit (Gross)
if (count($csv) < 11) {
continue;
}
// Skip first Row if Header
if ($csv[0] == 'Date') {
continue;
}
// Only Process Completed Transactions
if ($csv[5] == 'Pending') {
continue;
}
// Only Transactions with Fees Count
if (empty($csv[7]) && empty($csv[8])) {
continue;
}
$le = new stdClass();
$le->date = $csv[0];
$le->note = $csv[4] . ' #' . $csv[11];
$le->account_id = null;
switch (trim($csv[4])) {
case 'Payment Received':
case 'eBay Payment Received':
case 'Shopping Cart Payment Received':
// Ledger Entry for Paypal Fee
$le->account_id = 111;
$le->note = 'Fee for Transaction #' . $csv[11] . '';
$le->amount = $le->dr = floatval(preg_replace('/[^\\d\\.\\-]/', null, $csv[7]));
$this->view->JournalEntryList[] = $le;
// Ledger Entry for Paypal Deposit
$le = new stdClass();
$le->date = $csv[0];
$le->account_id = 8;
$le->note = $csv[4] . ' #' . $csv[11];
$le->amount = $le->cr = floatval(preg_replace('/[^\\d\\.\\-]/', null, $csv[6]));
break;
// Money Leaves PayPal to Expense
// Money Leaves PayPal to Expense
case 'Payment Sent':
case 'Express Checkout Payment Sent':
case 'Shopping Cart Payment Sent':
case 'Web Accept Payment Sent':
case 'eBay Payment Sent':
// Debit to Checking
$le->amount = floatval(preg_replace('/[^\\d\\.\\-]/', null, $csv[6]));
//if (floatval($le->amount) < 0) {
$le->dr = abs($le->amount);
$le->account_id = 26;
break;
case 'Add Funds from a Bank Account':
// Happens before Update to ...
// Happens before Update to ...
case 'Order':
// Requested Money From Us, Paid on *Sent
// Requested Money From Us, Paid on *Sent
case 'Pending Balance Payment':
continue 2;
// Ignore
break;
case 'Refund':
// Debit to Checking
$le->amount = floatval(preg_replace('/[^\\d\\.\\-]/', null, $csv[6]));
$le->cr = abs($le->amount);
$le->account_id = 26;
break;
case 'Update to Add Funds from a Bank Account':
// Money Into Paypal from Bank
// Debit to Checking
$le->amount = floatval(preg_replace('/[^\\d\\.\\-]/', null, $csv[6]));
$le->cr = abs($le->amount);
$le->account_id = 1;
break;
case 'Withdraw Funds to a Bank Account':
// Debit to Checking
$le->amount = floatval(preg_replace('/[^\\d\\.\\-]/', null, $csv[6]));
//if (floatval($le->amount) < 0) {
$le->dr = abs($le->amount);
$le->account_id = 1;
break;
default:
die($csv[4]);
}
$this->view->JournalEntryList[] = $le;
}
//Zend_Debug::dump($this->view->JournalEntryList);
//exit(0);
break;
case 'qfx':
// Quicken 2004 Web Connect
//echo "<pre>".htmlspecialchars($buf)."</pre>";
if (!preg_match('/^OFXHEADER:100/', $bf->data)) {
trigger_error('Not a valid QFX file', E_USER_ERROR);
}
if (preg_match_all("/^<STMTTRN>\n<TRNTYPE>(CHECK|CREDIT|DEBIT|DEP|DIRECTDEBIT|FEE|POS)\n<DTPOSTED>(\\d{8})\n<TRNAMT>([\\d\\-\\.]+)\n<FITID>(\\d+)\n<NAME>(.+)<\\/STMTTRN>\n/m", $bf->data, $m)) {
$c_entries = count($m[0]);
$trn_types = $m[1];
$trn_dates = $m[2];
$trn_amnts = $m[3];
$trn_fitid = $m[4];
$trn_names = $m[5];
// echo "<pre>".print_r($trn_names,true)."</pre>";
for ($i = 0; $i < $c_entries; $i++) {
$je = new stdClass();
$je->id = null;
$je->ok = false;
$je->index = $i;
$je->date = substr($trn_dates[$i], 4, 2) . '/' . substr($trn_dates[$i], 6, 2) . '/' . substr($trn_dates[$i], 0, 4);
$je->amount = $trn_amnts[$i];
$je->note = $trn_names[$i];
$je->offset_account_id = null;
$this->view->JournalEntryList[] = $je;
}
}
case 'square':
$ret = self::_parseSquare($opt['file']);
break;
}
// Now Spin Each List Item and Discover Existing Journal Entry?
$c = count($ret);
for ($i = 0; $i < $c; $i++) {
// Old
// $s = $d->select();
// $s->from('general_ledger',array('account_journal_id','date','amount'));
// $s->where(" (date <= ?::timestamp + '5 days'::interval) AND (date >= ? ::timestamp - '5 days'::interval) ",$ret[$i]->date);
// $s->where(' account_id = ?',$opt['account_id']);
// $s->where(' abs(amount) = ?',abs($ret[$i]->abs));
// $ret[$i]->id = $d->fetchOne($s);
// New
$sql = 'SELECT account_journal_id, date, amount FROM general_ledger';
$sql .= " WHERE (date <= ?::timestamp + '5 days'::interval) AND (date >= ? ::timestamp - '5 days'::interval)";
$sql .= ' AND account_id = ?';
$sql .= ' AND abs(amount) = ?';
$ret[$i]->id = SQL::fetch_one($sql, array($ret[$i]->date, $ret[$i]->date, $opt['account_id'], abs($ret[$i]->abs)));
// $sql = 'select a.id,a.date,b.amount';
// $sql.= ' from account_journal a join account_ledger b on a.id=b.account_journal_id ';
// $sql.= ' where ';
// $sql.= " (date<='{$je->date}'::timestamp+'5 days'::interval and date>='{$je->date}'::timestamp-'5 days'::interval) ";
// $sql.= " and abs(b.amount)='{$je->abs}' ";
// $sql.= ' b.account_id=' . $acct_id and abs(b.amount)='".abs($entry->amount)."' ";
// echo "$sql\n";
// echo $s->assemble();
}
uasort($ret, array(self, '_sortCallback'));
return $ret;
}
<?php
/**
*/
namespace Edoceo\Imperium;
use Edoceo\Radix\DB\SQL;
$sql = 'SELECT name AS id,name FROM base_enum WHERE link = ? ORDER BY sort';
$this->KindList = SQL::fetch_mix($sql, array('contact-kind'));
$this->StatusList = SQL::fetch_mix($sql, array('contact-status'));
// function createAction()
// {
// $_ENV['title'] = array('Contact','Create');
// $P = new Contact(intval($_GET['parent']));
// if ($P->id) {
// $this->view->Contact['parent_id'] = $P['id'];
// $this->view->Contact['company'] = $P['company'];
// $this->view->Contact['phone'] = $P['phone'];
// $this->view->Contact['url'] = $P['url'];
// }
// $this->render('view');
// }
//
<?php
/**
WorkorderController init
Sets the ACL for this Controller
*/
namespace Edoceo\Imperium;
use Edoceo\Radix\DB\SQL;
//$acl = Zend_Registry::get('acl');
//if ($acl->has('workorder') == false) {
// $acl->add( new Zend_Acl_Resource('workorder') );
//}
//$acl->allow('user','workorder');
// parent::init();
$sql = 'SELECT name AS id,name FROM base_enum WHERE link = ? ORDER BY sort';
$this->KindList = SQL::fetch_mix($sql, array('workorder-kind'));
$this->StatusList = SQL::fetch_mix($sql, array('workorder-status'));
$this->ItemStatusList = SQL::fetch_mix($sql, array('workorder-item-status'));
