// $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>';
