Source for file assets.db.php
Documentation is available at assets.db.php
* database functions for assets book
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
* You should have received a copy of the GNU General Public License along
* with this program; if not, write to the Free Software Foundation, Inc.,
* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
* @author m2mtech <tech@m2m.at>
* @copyright 2007 m2m server software gmbh
* @license http://www.gnu.org/licenses/gpl.html GNU General Public License Version 2
* @version $Id: assets.db.php 130 2007-08-17 14:24:44Z m2mtech $
* @link http://www.ea-geier.at/
* standard client db functions
require_once('code/clients.db.php');
require_once('code/numbers.inc.php');
require_once('code/date.inc.php');
$opt['decimal'] = $db->conf['numNumberDecimal'];
$vars = array('txtName', 'txtVendor', 'numAmount', 'numLifeTime', 'txtDeprMethod');
foreach ($vars as $var) if (isset ($data[$var])) switch ($var) {
$what[$var] = makeDot($data[$var], $opt['decimal']);
default: $what[$var] = $data[$var];
$what['firstYear'] = $data['year'];
foreach ($data['lines'] as $key => $year) {
$depr[$year] = array('d' => makeDot($data['numDepreciation' . $key]), 'v' => makeDot($data['numValue' . $key]));
$what['lastYear'] = $lastYear;
$what['purchaseDate'] = dateFormat($data, 'YYYY-MM-DD');
$what['lastModified'] = $db->now();
$what['who'] = $db->user['mail'];
$table = $db->table('assets');
if (isset ($data['numID'])) { // old item
if (!$db->update($what, $table, array('id' => $id))) return false;
if (!$db->insert($what, $table)) return false;
if (!$id = $db->lastID()) return false;
return $data['numID'] = $id;
* @param integer clientID
$table = $db->table('assets');
if (!$data = $db->selectOne('*', $table, array('id' => $item))) return false;
* get number of assets items
* @param integer clientID
* @return integer number of items
$table = $db->table('assets');
$what = 'count(id) as c, sum(numAmount) as numAmount';
$where = ''; // prepare where clause manually
if (isset ($filters['txtName'])) {
$where = "txtName like '%" . $db->escape($filters['txtName'], true) . "%'";
unset ($filters['txtName']);
if ($filters) // other filters available
foreach ($filters as $key => $value) {
$where .= $next . $db->escapeKey($key) . ' = ' . $db->escape($value);
if (!$dummy = $db->selectOne($what, $table, $where)) return false;
if (!$dummy['c']) return false;
$data['count'] = $dummy['c'];
$data['numAmount'] = $dummy['numAmount'];
$data['numDepreciation'] = 0;
$wYear = $db->escape($year);
$where .= $next . 'firstYear <= ' . $wYear . ' and lastYear >= ' . $wYear;
if ($dummy = $db->select($what, $table, $where))
foreach ($dummy as $item) {
foreach ($depr as $yearKey => $var) {
if ($year != $yearKey) continue;
$data['numDepreciation'] += $var['d'];
$data['numValue'] += $var['v'];
* load multiple assets book item
* @param integer clientID
* @param integer number of items
* @param integer number page
* @param string sorting string
function dbLoadAssetsItems(&$db, $client, $num = 10, $page = 1, $sort = false, $where = false) {
$table = $db->table('assets');
if (!$where) $where = '';
if (isset ($where['txtName'])) { // we have to prepare it ourselves
$where = "txtName like '%" . $db->escape($where['txtName'], true) . "%'";
unset ($filters['txtName']);
if ($filters) { // other filters available
foreach ($filters as $key => $value)
$where .= ' and ' . $db->escapeKey($key) . ' = ' . $db->escape($value);
if (!$sort) $sort = 'id asc';
if ($page == - 1) $offset = - 1;
else $offset = $num * ($page - 1);
if (!$data = $db->select('*', $table, $where, $sort, $num, $offset))
* prepare the sql data for output
* @param array configuration data
* @param array assets item
static $separator = false;
if (!$decimal) $decimal = $conf['numNumberDecimal'];
if (!$separator) switch ($conf['txtNumberSeperator']) {
if (!$date) $date = $conf['txtDateFormat'];
// prepare depreciation data
if (isset ($data['data'])) {
foreach ($depr as $year => $var) {
$data['lines'][$i] = $year;
$data['numDepreciation' . $i] = $var['d'];
$data['numValue' . $i] = $var['v'];
$numVars[] = 'numDepreciation' . $i;
$numVars[] = 'numValue' . $i;
$numVars[] = 'numAmount';
if (isset ($data['numDepreciation'])) $numVars[] = 'numDepreciation';
if (isset ($data['numValue'])) $numVars[] = 'numValue';
foreach ($numVars as $var)
$data[$var] = makeNum($data[$var], $separator, $decimal);
* @param integer clientID
* @return array templates
$table = $db->table('assets');
if (!$where) $where = '';
if (isset ($where['txtName'])) { // we have to prepare it ourselves
$where = "txtName like '%" . $db->escape($where['txtName'], true) . "%'";
unset ($filters['txtName']);
if ($filters) { // other filters available
foreach ($filters as $key => $value)
$where .= ' and ' . $db->escapeKey($key) . ' = ' . $db->escape($value);
if (!$dummy = $db->selectOne('min(firstYear) as first, max(lastYear) as last', $table, $where))
$first = (integer) $dummy['first'];
$last = (integer) $dummy['last'];
for ($i = $first; $i <= $last; $i++ ) {
$date = array('day' => '31', 'month' => '12', 'year' => $i);
$years[$i] = dateFormat($date, $db->conf['txtDateFormat']);
|