/**
* Загружает валюты. Сперва пробует загрузить их из файла, а потом, если не вышло, то из базы
* @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;
}
}
function getDb()
{
if (!$this->_db) {
$dsn = array();
$dsn['scheme'] = 'mysql';
$dsn['path'] = $this->getConfig('db');
if ($this->getConfig('other_db') == "1") {
$dsn = array_merge($dsn, array('host' => $this->getConfig('host'), 'user' => $this->getConfig('user'), 'pass' => $this->getConfig('pass')));
} else {
$appOptions = $this->getDi()->getParameters();
$dbConfig = $appOptions['db']['mysql'];
$dsn = array_merge($dsn, array('host' => $dbConfig['host'], 'user' => $dbConfig['user'], 'pass' => $dbConfig['pass']));
if (isset($dbConfig['port']) && $dbConfig['port']) {
$dsn['port'] = $dbConfig['port'];
}
}
if ($dsn['host'] && strpos($dsn['host'], ':') !== false && preg_match('/\\:(\\d+)$/', $dsn['host'])) {
list($dsn['host'], $dsn['port']) = explode(':', $dsn['host']);
}
$this->_db = Am_Db::connect($dsn, true);
$this->_db->setErrorHandler(array($this, 'dbErrorHandler'));
$this->_db->setIdentPrefix($this->getConfig('prefix'));
$this->_db->query("USE ?#", $dsn['path']);
$this->_db->query("SET NAMES utf8");
$this->_db->query("SET SESSION sql_mode=''");
}
if ($this->sqlDebug) {
if (!empty($this->getDi()->db->_logger)) {
$this->_db->setLogger($this->getDi()->db->_logger);
}
}
return $this->_db;
}
/**
* возвращает массив. 1ый элемент - сериализуемая строка. 2ой айди валюты по умолчанию
* @return array mixed
*/
function getCur()
{
$sql = "SELECT user_currency_list AS li, user_currency_default AS def FROM users WHERE id = ?";
$li = $this->db->query($sql, $this->getId());
$a = $li[0];
return $a;
}
function getDb()
{
if (!$this->_db) {
$dsn = array();
$dsn['scheme'] = 'mysql';
$dsn['path'] = $this->getConfig('db');
if ($this->getConfig('other_db') == "1") {
$dsn = array_merge($dsn, array('host' => $this->getConfig('host'), 'user' => $this->getConfig('user'), 'pass' => $this->getConfig('pass')));
} else {
$appOptions = $this->getDi()->getParameters();
$dbConfig = $appOptions['db']['mysql'];
$dsn = array_merge($dsn, array('host' => $dbConfig['host'], 'user' => $dbConfig['user'], 'pass' => $dbConfig['pass']));
}
$this->_db = Am_Db::connect($dsn, true);
$this->_db->setErrorHandler(array($this, 'dbErrorHandler'));
$this->_db->setIdentPrefix($this->getConfig('prefix'));
$this->_db->query("USE ?#", $dsn['path']);
}
if ($this->sqlDebug) {
if (!empty($this->getDi()->db->_logger)) {
$this->_db->setLogger($this->getDi()->db->_logger);
}
}
return $this->_db;
}
public function exportXml(XMLWriter $xml, $options = array())
{
$xml->startElement('table_data');
$xml->writeAttribute('name', $this->getTable()->getName(true));
$q = $this->query();
while ($row = $this->db->fetchRow($q)) {
$this->getTable()->createRecord($row)->exportXml($xml, $options);
}
$xml->endElement();
}
/**
* Возвращает массив тегов с количеством их повторов (если указано)
* @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());
}
}
/**
* Удаляет все операции по указанной категории
*
* @param oldUser $user
* @param int $catId
*/
function deleteOperationsByCategory(oldUser $user, $catId)
{
$sql = "SELECT id FROM operation WHERE user_id = ? AND cat_id = ?";
$operations = $this->db->selectCol($sql, $user->getId(), $catId);
if (count($operations) > 0) {
$this->db->query("BEGIN;");
foreach ($operations as $opId) {
$this->deleteOperation($opId);
}
$this->db->query("COMMIT;");
}
return (int) count($operations);
}
/**
* Сравнение расходов за периоды, Сравнение доходов за периоды
*
* @param int $type Тип операции. 0 - Расход, 1 - Доход
* @param string mysqldate $date1 Дата начала первого периода в формате "YYYY-MM-DD"
* @param string mysql date $date2 Дата окончания первого периода в формате "YYYY-MM-DD"
* @param string mysql date $date3 Дата начала второго периода в формате "YYYY-MM-DD"
* @param string mysql date $date4 Дата окончания второго периода в формате "YYYY-MM-DD"
* @param int | string $accounts Счёт в виде числа, или несколько чисел разделённых запятой
* @param int $currencyId Ид валюты в которой выводить результат
* @return array
*/
function CompareForPeriods($type, $date1 = '', $date2 = '', $date3 = '', $date4 = '', $accounts = '', $currencyId = 0)
{
$queryString = "SELECT\n c.cat_name,\n c.cat_id,\n a.account_currency_id as cur_id,\n sum(abs(op.money)) as su,\n p.per\n FROM operation op\n INNER JOIN accounts a\n ON a.account_id=op.account_id\n INNER JOIN category c\n ON c.cat_id=op.cat_id\n INNER JOIN (\n SELECT ? AS begin_date, ? AS end_date, 1 AS per\n UNION ALL\n SELECT ? AS begin_date, ? AS end_date, 2 AS per\n ) p\n ON op.date between p.begin_date AND p.end_date\n WHERE\n op.`type`= ?\n AND op.user_id= ? AND op.accepted=1\n AND op.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 GROUP BY c.cat_id, a.account_currency_id, p.per";
$rows = $this->_db->query($queryString, $date1, $date2, $date3, $date4, $type, $this->_user->getId());
$result = array();
foreach ($rows as $value) {
$money = new myMoney(abs($value['su']), $value['cur_id']);
$tempId = $value['per'] . $value['cat_id'];
$result[$tempId] = $value;
unset($result[$tempId]['cur_id']);
unset($result[$tempId]['su']);
if (isset($result[$tempId]['su'])) {
$result[$tempId]['su'] += $this->ex->convert($money, $currencyId)->getAmount();
} else {
$result[$tempId]['su'] = $this->ex->convert($money, $currencyId)->getAmount();
}
}
return array($result);
}
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 write($id, $data)
{
$row = array('id' => $id, 'modified' => Am_Di::getInstance()->time, 'lifetime' => $this->getLifetime(), 'user_id' => null, 'data' => $data);
$this->db->query("REPLACE INTO ?_session SET ?a", $row);
return true;
}
/**
* TearDown
*/
protected final function tearDown()
{
$this->_end();
$this->db->query("ROLLBACK");
}
/**
* Возвращает операцию по финансовой цели из базы данных
* @param $target_id int
* @return array mixed
*/
public function getTargetOperation($target_id)
{
return $this->db->selectRow("SELECT tb.id, tb.user_id, tb.money, t.category_id as cat_id, '' as transfer,\n DATE_FORMAT(tb.date,'%d.%m.%Y') as date, t.id as tr_id, tb.bill_id, '' as drain, tb.comment as comment,\n t.title as title, t.close\n FROM `target_bill` tb\n LEFT JOIN target t on tb.target_id = t.id\n WHERE tb.id = ? AND tb.`user_id` = ?", $target_id, Core::getInstance()->user->getId());
}
function deleteRecord($ip)
{
$this->db->query("DELETE FROM ?_failed_login\n WHERE ip=? AND login_type=?", $ip, $this->loginType);
}
public function subscribe($spamer)
{
$sql = "UPDATE users SET getNotify=? WHERE id=? ;";
return $this->db->query($sql, $spamer, $this->user_id);
}
/**
* Возвращает количество операций по ID категории
*
* @param oldUser $user Ссылка на объект пользователя
* @param int $categoryId Ид категории которую нужно удалить
* @return int Количество операций по категории пользователя
*/
function getCountOperationByCategory(oldUser $user, $categoryId = 0)
{
$sql = "SELECT COUNT(id) FROM operation WHERE cat_id IN (\n SELECT cat_id FROM category WHERE user_id=? AND (cat_id=? OR cat_parent=?))";
return (int) $this->db->selectCell($sql, $user->getId(), $categoryId, $categoryId);
}
