/** * Загружает валюты. Сперва пробует загрузить их из файла, а потом, если не вышло, то из базы * @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); }