// $ale->link_id = $Invoice->id; $at->AccountLedgerEntryList[] = $ale; // Next Line Accounts Receivable $ale = new AccountLedgerEntry(); $at->AccountLedgerEntryList[] = $ale; $_SESSION['account-transaction'] = $at; $_SESSION['return-path'] = sprintf('/account/ledger?id=%d', $this->Account['id']); $this->redirect('/account/transaction'); } if (empty($this->Account['id'])) { // Show General Ledger (All Accounts!) $this->openBalance = 0; $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']);
/** Sum of Transactions */ function getTransactionSum() { $id = intval($this->_data['id']); if ($id <= 0) { return null; } $sql = "SELECT abs(sum(al.amount)) 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 al.link_to=" . self::getObjectType($this) . " and al.link_id={$id}"; // If Posting & Paying do this $sql .= ' AND amount < 0 '; // Elseif CASH basis don't use AND amount... $ret = SQL::fetch_one($sql); return $ret; }
/** @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; }
<?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;
/** */ 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; }
/** 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}"); }
<?php /** WorkorderController 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 workorder'); $this->page_cur = min(max(1, $_GET['page']), $this->page_max); $_GET['size'] = min(max(20, $_GET['size']), 100); $sql = 'SELECT workorder.*, contact.name as contact_name '; $sql .= ' FROM workorder '; $sql .= ' JOIN contact ON workorder.contact_id=contact.id '; $sql .= ' ORDER BY workorder.date DESC, workorder.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('Work Orders %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;
$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>';