KissCount/www/database.php

328 lines
7.9 KiB
PHP
Executable File

<?php
/*
Copyright 2010-2012 Grégory Soutadé
This file is part of KissCount.
KissCount 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 3 of the License, or
(at your option) any later version.
KissCount 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 KissCount. If not, see <http://www.gnu.org/licenses/>.
*/
$BDD_FILE = '/var/nfs/kc.bdd';
try {
$db = new SQLite3($BDD_FILE, SQLITE3_OPEN_READONLY);
}
catch(Exception $e)
{
die('Unable to load BDD');
}
function GetUsers()
{
global $db;
$res = array();
$result = $db->query("SELECT name FROM user ORDER BY name");
while ($row = $result->fetchArray())
array_push($res, $row['name']);
return $res;
}
function IsUserValid($user, $password)
{
global $db;
$result = $db->query("SELECT id FROM user WHERE name='" . $db->escapeString($user) . "' AND password='" . sha1($db->escapeString($password)) . "'");
return $result->fetchArray();
}
function ExtractStyle(&$category)
{
$s = "background-color:" . $category["backcolor"] . ";";
$s .= "color:" . $category["forecolor"] . ";";
if ($category["font"] != "")
{
$params = explode(";", $category["font"]);
// size ; xx ; 93 italic ; 92 bold ; face name
$s .= "font-size:" . $params[0] . "px;";
if ($params[2] == "93")
$s .= "font-style:italic;";
if ($params[3] == "92")
$s .= "font-weight:bold;";
$s .= "font-family:" . $params[4] . ";";
}
$category["style"] = $s;
}
function LoadUser($name)
{
global $db;
$user = new User();
$name = $db->escapeString($name);
$result = $db->query("SELECT * FROM user WHERE name='$name'");
if (!($row = $result->fetchArray()))
return NULL;
$user->id = $row["id"];
$result = $db->query("SELECT * FROM account WHERE user='$user->id' ORDER BY default_account DESC, hidden, blocked, virtual, name ASC");
$user->accounts = array();
while ($row = $result->fetchArray())
array_push($user->accounts, $row);
// Shared accounts
$result = $db->query("SELECT * FROM account WHERE id IN (SELECT account FROM shared_account WHERE user='$user->id') ORDER BY default_account DESC, hidden, blocked, virtual, name ASC");
while ($row = $result->fetchArray())
array_push($user->accounts, $row);
$result = $db->query("SELECT * FROM category WHERE user='$user->id' ORDER by name");
$user->categories = array();
while ($row = $result->fetchArray())
{
ExtractStyle($row);
array_push($user->categories, $row);
}
$result = $db->query("SELECT * FROM preference WHERE user='$user->id' ORDER by name");
$user->preferences = array();
$user->preferences["operation_order"] = "ASC";
while ($row = $result->fetchArray())
array_push($user->preferences, $row);
return $user;
}
function LoadCategory(&$user, $id)
{
global $db;
$result = $db->query("SELECT * FROM category WHERE id='$id'");
if ($row = $result->fetchArray())
{
ExtractStyle($row);
array_push($user->categories, $row);
return $row;
}
return "";
}
function LoadTag(&$user, $id)
{
global $db;
$result = $db->query("SELECT * FROM tag WHERE id='$id'");
if ($row = $result->fetchArray())
{
array_push($user->tags, $row);
return $row;
}
return "";
}
function GetAccountAmount($id, $month, $year)
{
global $db;
$req = "SELECT amount FROM account_amount WHERE account='$id' AND month='$month' AND year='$year'";
$result = $db->query($req);
if ($row = $result->fetchArray())
return $row["amount"];
return 0;
}
function array_insert($array,$pos,$val)
{
$array2 = array_splice($array,$pos);
$array[] = $val;
$array = array_merge($array,$array2);
return $array;
}
function LoadMonth($user, $month, $year)
{
global $db;
$res = array();
if (!isset($user->accounts[0])) return;
$req = "SELECT * FROM operation WHERE (account IN('" . $user->accounts[0]["id"] ;
foreach($user->accounts as $i => $account)
{
$req .= "', '" . $account["id"];
}
$req .= "')";
$req .= " OR user='$user->id')";
$req .= " AND year='$year' AND month='$month'";
$req .= " ORDER BY fix_cost DESC, year, month ASC, day ";
$req .= $user->preferences["operation_order"];
$result = $db->query($req);
// Pack operations and their sub operations
while ($row = $result->fetchArray())
{
$inserted = 0;
foreach($res as $i => $value)
{
if ($value["parent"] == $row["id"])
{
$res = array_insert($res, $i, $row);
$inserted = 1;
break;
}
if ($row["parent"] == $value["id"])
{
$res = array_insert($res, $i+1, $row);
$inserted = 1;
break;
}
}
// Append
if ($inserted == 0)
{
$res = array_insert($res, $i+1, $row);
}
}
return $res;
}
function MetaPositiveAmount($id)
{
global $db;
$req = "SELECT SUM(amount) as amount FROM operation WHERE amount > 0 AND parent='$id'";
$result = $db->query($req);
if ($row = $result->fetchArray())
return $row["amount"];
else
return 0.0;
}
function GetAllOperations($user, &$last_year, &$last_month)
{
$res;
global $db;
if (!isset($user->accounts[0])) return $res;
$req = "SELECT DISTINCT year FROM account_amount WHERE account IN('" . $user->accounts[0]["id"] ;
foreach($user->accounts as $i => $account)
{
$req .= "', '" . $account["id"];
}
$req .= "')";
$req2 = "SELECT DISTINCT year FROM operation WHERE account IN('" .$user->accounts[0]["id"] ;
foreach($user->accounts as $i => $account)
{
$req2 .= "', '" . $account["id"];
}
$req2 .= "')";
$req2 .= " OR user='" . $user->id . "'";
$req2 .= " ORDER BY year ASC";
$reqUnion = $req . " UNION " . $req2;
$result = $db->query($reqUnion);
while ($row = $result->fetchArray())
{
$last_year = $year = $row["year"];
$req = "SELECT DISTINCT month FROM account_amount WHERE account IN('" . $user->accounts[0]["id"] ;
foreach($user->accounts as $i => $account)
{
$req .= "', '" . $account["id"];
}
$req .= "')";
$req .= " AND year='" . $year . "'";
$req2 = "SELECT DISTINCT month FROM operation WHERE (account IN('" . $user->accounts[0]["id"] ;
foreach($user->accounts as $i => $account)
{
$req2 .= "', '" . $account["id"];
}
$req2 .= "')";
$req2 .= " OR user='" . $user->id . "')";
$req2 .= " AND year='" . $year . "'";
$req2 .= " ORDER BY month ASC";
$reqUnion = $req . " UNION " . $req2;
$result2 = $db->query($reqUnion);
while ($row = $result2->fetchArray())
{
if (!isset($res[$year])) $res[$year] = array();
array_push($res[$year], $row["month"]);
$last_month = $row["month"];
}
}
return $res;
}
function GetSubOperations($parent)
{
$res = "[";
global $db;
$req = "SELECT id FROM operation WHERE parent=\"" . $parent . "\"";
$result = $db->query($req);
while ($row = $result->fetchArray())
$res .= $row["id"] . ", ";
if (strlen($res) > 1)
$res = substr($res, 0, strlen($res)-2);
return $res . "]";
}
?>