/** * Получает всё дерево категорий определённого пользователя. */ 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; }