/**
* Получает всё дерево категорий определённого пользователя.
*/
public function loadUserTree()
{
$where = array_key_exists('categories_filtr', $_SESSION) ? $_SESSION['categories_filtr'] : null;
$forest = $this->db->select("SELECT c.*, c.cat_id AS ARRAY_KEY, c.cat_parent AS PARENT_KEY,\n sc.name FROM category c\n LEFT JOIN system_categories sc ON sc.id = c.system_category_id\n WHERE c.user_id = ? " . $where . " AND c.deleted_at IS NULL ORDER BY cat_name", Core::getInstance()->user->getId());
$this->tree = $forest;
$this->saveCache();
}
/**
* Загружает валюты. Сперва пробует загрузить их из файла, а потом, если не вышло, то из базы
* @example Пример части загружаемого массива
* <code>array(
* '2'=>array(
* 'name'=>'Доллар США',
* 'abbr'=>'$',
* 'charCode'=>'USD',
* 'value'=>'31.2424',
* 'dirrection'=>'up'
* )),
* </code>
*/
function loadCurrency()
{
if (!(include_once dirname(dirname(__FILE__)) . '/include/daily_currency.php')) {
$daily = null;
}
$daily = null;
if ($daily == null) {
$currency = $this->db->select("SELECT * FROM currency c WHERE cur_uses=1");
$daily = $this->db->select("SELECT\n currency_id,\n user_id,\n direction,\n currency_sum AS value,\n currency_date AS `date`\n FROM daily_currency\n WHERE\n currency_from = 1 AND\n currency_date =(SELECT MAX(currency_date)\n FROM daily_currency\n WHERE currency_from=1\n AND user_id=0)\n ORDER BY currency_id");
foreach ($currency as $v) {
$this->sys_list_currency[$v['cur_id']] = array('id' => $v['cur_id'], 'name' => $v['cur_name_value'], 'abbr' => $v['cur_name'], 'charCode' => $v['cur_char_code'], 'okv' => $v['cur_okv_id'], 'country' => $v['cur_country'], 'uses' => $v['cur_uses']);
foreach ($daily as $k => $val) {
if ($val['currency_id'] == $v['cur_id']) {
if ($val['user_id'] > 0) {
$this->sys_list_currency[$v['cur_id']]['value_user'] = $val['value'];
$this->sys_list_currency[$v['cur_id']]['date_user'] = $val['date'];
$this->sys_list_currency[$v['cur_id']]['direct_user'] = $val['direction'];
} else {
$this->sys_list_currency[$v['cur_id']]['value'] = $val['value'];
$this->sys_list_currency[$v['cur_id']]['date'] = $val['date'];
$this->sys_list_currency[$v['cur_id']]['direct'] = $val['direction'];
}
}
}
$this->sys_list_currency[1]['value'] = (double) 1.0;
$this->sys_list_currency[1]['date'] = '';
$this->sys_list_currency[1]['direct'] = 0;
}
// $sql = "SELECT cur_name_value AS name, cur_char_code AS `charCode`,
// cur_name AS abbr, currency_sum AS value, direction
// FROM currency
// LEFT JOIN daily_currency ON cur_id=currency_id
// WHERE currency_date=CURRENT_DATE OR cur_id=1";
// $daily = $this->db->select($sql);
// foreach ($daily as $val) {
// $this->sys_list_currency[$val['currency_id']] = array(
// 'id' => $val['currency_id'],
// 'name' => $daily['name'],
// 'abbr' => $daily['abbr'],
// 'charCode' => $daily['charCode'],
// 'value' => $daily['value'],
// 'direction' => $daily['direction'],
// );
// }
} else {
$this->sys_list_currency = $daily;
}
}
/**
* Получает список последних операций
* @param int $count
* @return array
*/
function getLastOperations($count = 10)
{
$operations = array();
$sql = "SELECT\n o.id,\n o.user_id,\n o.money,\n DATE_FORMAT(o.date,'%d.%m.%Y') as `date`,\n o.date AS dnat,\n o.cat_id,\n NULL as target_id,\n o.account_id,\n o.comment,\n o.transfer_account_id AS transfer,\n 0 AS virt,\n o.tags,\n o.transfer_amount AS moneydef,\n o.exchange_rate AS curs,\n o.type,\n created_at,\n updated_at\n FROM operation o\n WHERE\n o.user_id = " . $this->_user->getId() . "\n AND\n o.date > 0\n AND\n accepted=1\n AND\n deleted_at IS NULL\n UNION\n SELECT\n t.id,\n t.user_id,\n -t.money,\n DATE_FORMAT(t.date,'%d.%m.%Y'),\n t.date AS dnat,\n tt.category_id,\n t.target_id,\n tt.target_account_id,\n t.comment,\n '',\n 1 AS virt,\n t.tags,\n NULL,\n NULL,\n 4 as type,\n dt_create AS created_at,\n dt_update AS updated_at\n FROM target_bill t\n LEFT JOIN target tt\n ON\n t.target_id=tt.id\n WHERE\n t.user_id = " . $this->_user->getId() . "\n AND\n tt.done=0\n ORDER BY\n updated_at DESC\n LIMIT " . (int) $count;
$operations = $this->db->select($sql);
return $operations;
}
/**
* Сравнение расходов и доходов
*
* @param string mysqldate $start Дата начала периода в формате "YYYY-MM-DD"
* @param string mysqldate $end Дата окончания периода в формате "YYYY-MM-DD"
* @param int|string $accounts Ид счёта, или несколько счетов через запятую
* @param int $currencyId Ид валюты в которой ожидается результат
* @return array
*/
function getBars($start = '', $end = '', $accounts = 0, $currencyId = 0)
{
// Получаем все операции, за выбранный период
$sql = "SELECT\n ABS(sum(o.money)) AS money,\n cur.cur_id,\n DATE_FORMAT(`date`,'%Y-%m-01') as `datef`,\n o.`type`\n FROM operation o\n INNER JOIN accounts a\n ON a.account_id=o.account_id\n INNER JOIN category c\n ON c.cat_id = o.cat_id\n INNER JOIN currency cur\n ON cur.cur_id = a.account_currency_id\n WHERE\n o.user_id = ?\n AND `date` BETWEEN ? AND ?\n AND o.accepted=1\n AND o.deleted_at IS NULL\n AND a.deleted_at IS NULL\n AND c.deleted_at IS NULL\n AND a.account_id IN({$accounts})\n AND o.type IN (?a)\n GROUP BY o.`type`, cur_id, `datef`";
$result = $this->_db->select($sql, Core::getInstance()->user->getId(), $start, $end, array(Operation::TYPE_WASTE, Operation::TYPE_PROFIT));
$sort = array();
foreach ($result as $value) {
$money = new myMoney(abs($value['money']), $value['cur_id']);
$amount = $this->ex->convert($money, $currencyId)->getAmount();
// Доходы
if ($value['type'] == 1) {
if (isset($sort[$value['datef']]['in'])) {
$sort[$value['datef']]['in'] += $amount;
} else {
$sort[$value['datef']]['in'] = $amount;
}
// Расходы
} else {
if (isset($sort[$value['datef']]['was'])) {
$sort[$value['datef']]['was'] += $amount;
} else {
$sort[$value['datef']]['was'] = $amount;
}
}
$ts = strtotime($value['datef']);
// Если у нас период захватывает разные годы
if (date("Y", strtotime($start)) == date("Y", strtotime($end))) {
$sort[$value['datef']]['lab'] = $this->_fullMonth[date("n", $ts)];
// Иначе выводим просто месяц
} else {
$sort[$value['datef']]['lab'] = $this->_fullMonth[date("n", $ts)] . " " . date("Y", $ts);
}
}
return array($sort);
}
/**
* Возвращает массив тегов с количеством их повторов (если указано)
* @param bool $cloud Возвращать статистику для составления облака?
* @return array
*/
function getTags($cloud = true)
{
if ($cloud) {
$sql = "SELECT name, COUNT(name) as cnt\n FROM tags\n WHERE user_id = ?\n GROUP BY name ORDER BY COUNT(name) DESC";
return $this->db->select($sql, Core::getInstance()->user->getId());
} else {
$sql = "SELECT name\n FROM tags\n WHERE user_id = ?\n GROUP BY name ORDER BY name";
return $this->db->selectCol($sql, Core::getInstance()->user->getId());
}
}
/**
* Возвращает фин. цели
* @return void
*/
public function initUserTargets()
{
$this->user_targets = array();
// Ежели нет пользователя - всё это не нужно.
if (!$this->getId()) {
return;
}
$this->user_targets = array();
$this->user_targets['user_targets'] = array();
$userTargets = $this->db->select("\n SELECT\n t.id, t.category_id as category,\n t.title,\n t.amount,\n DATE_FORMAT(t.date_begin,'%d.%m.%Y') as start,\n DATE_FORMAT(t.date_end,'%d.%m.%Y') as end,\n t.percent_done,\n t.forecast_done,\n t.visible,\n t.photo,\n t.url,\n t.comment,\n t.target_account_id AS account,\n t.amount_done,\n t.close,\n t.done,\n (SELECT b.money FROM target_bill b WHERE b.target_id = t.id ORDER BY b.dt_create ASC LIMIT 1) AS money\n FROM target t\n WHERE t.user_id = ?\n ORDER BY t.date_end ASC\n LIMIT ?d,?d;", $this->getId(), 0, 20);
while (list(, $target) = each($userTargets)) {
$this->user_targets['user_targets'][$target['id']] = $target;
}
unset($userTargets);
if (!isset(Core::getInstance()->user)) {
Core::getInstance()->user = $this;
}
$target = new Targets_Model();
$popTargets = $target->getPopList();
$this->user_targets['pop_targets'] = $popTargets['list'];
}
public function parseTables(DbSimple_Mysql $db)
{
$prefix = $db->getPrefix();
foreach ($db->selectCol("SHOW TABLES LIKE ?", $prefix . '%') as $tablename) {
if (strlen($prefix) && strpos($tablename, $prefix) !== 0) {
continue;
}
// other prefix?
$name = substr($tablename, strlen($prefix));
$table = new Am_DbSync_Table($name);
foreach ($db->select("DESCRIBE ?#", $tablename) as $row) {
$table->addField(Am_DbSync_Field::createFromDb($row));
}
$indexes = array();
foreach ($db->select("SHOW INDEX FROM ?#", $tablename) as $row) {
$indexes[$row['Key_name']][] = $row;
}
foreach ($indexes as $indexRows) {
$table->addIndex(Am_DbSync_Index::createFromDb($indexRows));
}
$this->addTable($table);
}
}
/**
* Get table definition from database and return array like
* <example>
* Array
* (
* [cc_id] => stdClass Object
* (
* [field] => cc_id
* [type] => int(11)
* [null] => NO
* [key] => PRI
* [default] =>
* [extra] => auto_increment
* )
*
* [user_id] => stdClass Object
* (
* [field] => user_id
* [type] => int(11)
* [null] => NO
* [key] => UNI
* [default] =>
* [extra] =>
* )
* </example>
*
* @return array array of field defs objects
*/
function getFields($onlyFieldNames = false)
{
$res = array();
$class = $this->getName();
if (empty(self::$infoCache[$class])) {
foreach ($this->_db->select("SHOW FIELDS FROM {$this->_table}") as $f) {
$x = new stdClass();
foreach ($f as $k => $v) {
$k = strtolower($k);
$x->{$k} = $v;
}
$res[$f['Field']] = $x;
}
self::$infoCache[$class] = $res;
}
return $onlyFieldNames ? array_keys(self::$infoCache[$class]) : self::$infoCache[$class];
}
public function countClose()
{
$sql = "SELECT count(*) AS co\n FROM target\n WHERE close=1 AND done=1 AND user_id=?";
$result = $this->db->select($sql, Core::getInstance()->user->getId());
return $result[0]['co'];
}
function guessDbPrefix(DbSimple_Mysql $db, $database = null, $prefix = null)
{
$res = array();
foreach ($dbs = $db->selectCol("SHOW DATABASES") as $dbname) {
try {
$tables = $db->selectCol("SHOW TABLES FROM ?# LIKE '%{$this->guessTablePattern}'", $dbname);
} catch (Am_Exception_Db $e) {
continue;
}
if (is_array($tables)) {
foreach ($tables as $t) {
// check fields here
$info = $db->select("SHOW COLUMNS FROM `{$dbname}`.{$t}");
$infostr = "";
if (is_array($info)) {
foreach ($info as $k => $v) {
$infostr .= join(';', $v) . "\n";
}
}
$wrong = 0;
foreach ($this->guessFieldsPattern as $pat) {
if (!preg_match('|^' . $pat . '|m', $infostr)) {
$wrong++;
}
}
if ($wrong) {
continue;
}
$res[] = $dbname . '.' . substr($t, 0, -strlen($this->guessTablePattern));
}
}
}
return $res;
}
