/** 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'));