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);
}
}
/**
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;
}
/**
Delete a Key
@param $k Key
*/
function delMeta($k)
{
if (empty($this->_data['id'])) {
unset($this->_meta[$k]);
}
$sql = 'DELETE FROM contact_meta';
$sql .= ' WHERE contact_id = ? AND key = ?';
$arg = array($this->_data['id'], $k);
$res = SQL::query($sql, $arg);
return $res;
}
/**
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'];
}
/**
AppModel delete
Destroy this object and it's index
*/
function delete()
{
SQL::query("delete from {$this->_table} where id = {$this->_data['id']}");
}
/**
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}");
}
