/** * Взять все заметки * * @param integer $from_id ИД пользователя чьи заметки * @param array $to_login Кому заметка (массив с логинами) * @param string $error Возвращает сообщение об ошибке * @return array данные выборки */ function GetNotes($from_id, $to_login = false, &$error) { $DB = new DB(); if (!$from_id) { return false; } if (empty($to_login)) { $rows = $DB->rows("SELECT * FROM notes(?)", $from_id); } elseif (is_array($to_login)) { $rows = $DB->rows("SELECT * FROM notes_get(?, ?a)", $from_id, $to_login); } else { $rows = $DB->row("SELECT * FROM notes_get(?i, ?i)", $from_id, $to_login); } return $rows; }
/** * Взять информацию по найденным результатам. * * @return array */ public function getRecords($order_by = null) { if ($this->matches && $this->active_search) { $plproxy = new DB('plproxy'); $uid = get_uid(); $sql = "SELECT * FROM get_search_messages({$uid}, ARRAY[" . implode(', ', $this->matches) . '])'; if ($order_by) { $sql .= " ORDER BY {$order_by}"; } elseif ($this->_sortby && (($desc = $this->_sort == SPH_SORT_ATTR_DESC) || $this->_sort == SPH_SORT_ATTR_ASC)) { $sql .= " ORDER BY {$this->_sortby}" . ($desc ? ' DESC' : ''); } return $plproxy->rows($sql); } return; }
public function getModAdvices($type = false, $filter = false, $page = 0) { list($type_sql, $order) = $this->getTypeSQL($type); if ($filter) { $filter_sql = ' AND ' . $this->getFilterSQL($filter); } if ($page < 0) { $page = 0; } $limit = (int) self::COUNT_PAGE; $offset = self::COUNT_PAGE * $page; $sql = "SELECT pa.*, e.login as e_login, e.role as e_role, e.photo as e_photo, e.uname as e_uname, e.usurname as e_usurname, e.is_pro as e_is_pro, e.is_team as e_is_team, e.is_pro_test as e_is_pro_test,\n f.login as f_login, f.role as f_role, f.photo as f_photo, f.uname as f_uname, f.usurname as f_usurname, f.is_pro as f_is_pro, f.is_team as f_is_team, f.is_pro_test as f_is_pro_test,\n file_docs_contract.original_name as fname_docs_contract, file_docs_contract.path as path_docs_contract, file_docs_contract.fname as fname_contract, \n file_docs_tz.original_name as fname_docs_tz, file_docs_tz.path as path_docs_tz, file_docs_tz.fname as fname_tz, \n file_docs_result.original_name as fname_docs_result, file_docs_result.path as path_docs_result, file_docs_result.fname as fname_result\n FROM paid_advices pa\n INNER JOIN users e ON e.uid = pa.user_from AND e.is_banned = B'0'\n INNER JOIN users f ON f.uid = pa.user_to AND f.is_banned = B'0'\n LEFT JOIN file_advices as file_docs_contract ON file_docs_contract.id = pa.docs_contract\n LEFT JOIN file_advices as file_docs_tz ON file_docs_tz.id = pa.docs_tz\n LEFT JOIN file_advices as file_docs_result ON file_docs_result.id = pa.docs_result_file\n WHERE {$type_sql} {$filter_sql}\n ORDER BY {$order} \n LIMIT {$limit} OFFSET {$offset}"; $res = $this->_db->rows($sql); return $res; }
public function changepw() { $res = DB::query("SELECT * FROM users WHERE id = " . DB::esc($this->id) . " AND password = '******'"); if (DB::rows() > 0) { $q = "UPDATE users SET password = '******' WHERE id = " . DB::esc($this->id); $res2 = DB::query($q); if (DB::rows() > 0) { return true; } else { return false; } } else { return false; } }
/** * Берем таблицы, последний анализ, которых был ранее чем analyze_min_age секунд назад * или количество изменненных строк > analyze_changed_factor * кол-во строк. * Сначала идут по времени последнего анализа. * Вопрос в том, что если сработал обычный автовакуум, то в autovacuum_log статистика не поменяется, * т.е. не будет известно сколько строк в реальности изменилось с последнего анализа. * Решение: не принимать во внимание, т.е. просто соблюдаем условия выше. Может случиться "лишний" анализ, не беда. */ public function analyze($db_alias, $mode = self::MODE_ANALYZE) { $cfg = $this->_config[$mode]; $log = new log($cfg['log_file'], 'w'); // !!! SERVER добавить (для альфы/беты). $DB = new DB($db_alias); $log->linePrefix = $this->_config['log_line_prefix']; $log->writeln('Получаем список таблиц'); $last_av_col = 'COALESCE(maintenance.max(' . ($mode == self::MODE_ANALYZE ? 'pt.last_analyze, pt.last_autoanalyze, ' : '') . "pt.last_vacuum, pt.last_autovacuum), 'epoch')"; $sql = "\n WITH w_stat as (\n SELECT ts.*, pt.*, ts.relid IS NULL as _is_new, {$last_av_col} as _last_av\n FROM pg_stat_user_tables pt\n LEFT JOIN\n maintenance.table_stat ts\n ON ts.relid = pt.relid\n WHERE pt.schemaname = 'public'\n AND pt.n_live_tup + pt.n_dead_tup > 0\n )\n (\n (SELECT *, 0 as ord, 0.00 as ord2 FROM w_stat WHERE (_is_new OR _last_av + interval '?i seconds' <= now()))\n UNION ALL\n (SELECT *, 1, tup_factor FROM w_stat WHERE tup_factor >= ?f)\n )\n ORDER BY ord, ord2 DESC, _last_av\n "; $tbls = $DB->rows($sql, $cfg['min_age'], $cfg['changed_factor']); $acnt = 0; $log->writeln(count($tbls) . ' таблиц'); foreach ($tbls as $t) { if ($log->getTotalTime(null) >= $cfg['max_duration']) { $log->writeln('Время истекло.'); break; } $DB->query(($mode == self::MODE_VACUUM ? 'VACUUM ' : 'ANALYZE') . " VERBOSE {$t['relname']}"); $cmpls[(int) ($t['_is_new'] == 't')][] = $t['relid']; $log->writeln(pg_last_notice(DB::$connections[$db_alias])); ++$acnt; } // Обновляем статистику. if ($cmpls[0]) { $cols = $this->_mt_cols; unset($cols[0], $cols[1]); $lcols = implode(',', $cols); $rcols = 'pt.' . implode(', pt.', $cols); $sql = "UPDATE maintenance.table_stat ts SET ({$lcols}) = ({$rcols}) FROM pg_stat_user_tables pt WHERE pt.relid IN (?l) AND ts.relid = pt.relid"; $DB->query($sql, $cmpls[0]); } if ($cmpls[1]) { $cols = implode(',', $this->_mt_cols); $sql = "INSERT INTO maintenance.table_stat ({$cols}) SELECT {$cols} FROM pg_stat_user_tables WHERE relid IN (?l)"; $DB->query($sql, $cmpls[1]); } // Удаляем лишние таблицы, типа catalog_positions2 (создаются/удаляются автоматом) $DB->query('DELETE FROM maintenance.table_stat ts WHERE NOT EXISTS (SELECT 1 FROM pg_stat_user_tables WHERE relid = ts.relid)'); $log->writeln("Обработано {$acnt} таблиц"); }
public function getTopQueries($type, $limit = 1000, &$se_info = array()) { $limit = " LIMIT {$limit}"; $types = array('users', 'projects'); $index_type = 2; if (in_array($type, array('users', 'projects'))) { $index_type = intval(array_search($type, $types)); } $sql = "SELECT s.query, rate as weight \n FROM search_kwords_top s\n WHERE index_type = {$index_type}\n ORDER BY weight DESC {$limit}"; $res = $this->_db->rows($sql); if (!$res) { $res = array(); } $se_info = $res; $out = array(); foreach ($res as $row) { $out[] = $row['query']; } $time = time() . time(); $out = implode($time, $out); $out = iconv('CP1251', 'UTF8', $out); $out = explode($time, $out); return $out; }
/** * Обработка данных, загруженных из 1С */ public static function processInvoiceData() { $db = new DB('master'); $sql = 'SELECT rr.*, s.id sbr_id, s.emp_id, (u.uid = s.emp_id)::int is_emp FROM pskb_invoice_raw rr INNER JOIN users u ON u.login = rr.login INNER JOIN pskb_lc lc ON lc.lc_id = rr.lc_id INNER JOIN sbr s ON s.id = lc.sbr_id WHERE rr.status = 0 ORDER BY id LIMIT 50'; $rows = $db->rows($sql); foreach ($rows as $row) { $err = ''; $params = array('status' => 1, 'err' => ''); $db->update('pskb_invoice_raw', array('status' => 99), 'lc_id = ? AND login = ? AND status != ? AND actnum = ? AND invnum = ?', $row['lc_id'], $row['login'], 1, $row['actnum'], $row['invnum']); if (!self::addInvoice($row, $err)) { $params = array('status' => 2); } if ($err) { $params['err'] = $err; } $db->update('pskb_invoice_raw', $params, 'lc_id = ? AND login = ? AND status != ? AND actnum = ? AND invnum = ?', $row['lc_id'], $row['login'], 1, $row['actnum'], $row['invnum']); } }
/** * Получить всех кто у юзера в избарнном * * @param string $whom логин или uid * @param string $error Возвращает сообщение об ошибке * @param bool $bIsLogin является ли $whom логином * @return array */ function teamsFavorites($whom, &$error, $bIsLogin = false) { // Я рекомендую. $DB = new DB(); if ($whom === NULL) { $whom = get_uid(false); } if ($bIsLogin) { $users = new users(); $whom = $users->GetUid($err, $whom); } $users = $DB->rows("SELECT * FROM teams_get_users(?i)", $whom); return $users; }
/** * Получаем блоки (элементы) ленты по пользователю * Получаем единый массив данных, необходимых для вывода элементов ленты. Разделением между топиком сообщества и работой портфолио * может служить, например, член массива .portfolio_id, у сообществ он NULL. В запросе pf.post_date может быть NULL, так как * поле portfolio.post_date заведено совсем недавно. * * @param integer $user_id id пользователя * @param integer $my_team_checked истина, если стоит галка "Моя команда" или "Рекоммендованые мной". * @param integer $all_profs_checked истина, если стоит галка "Все разделы". * @param mixed $prof_groups строка идентификаторов групп профессий, разделенных запятыми. * @param mixed $communes строка идентификаторов сообществ, разделенных запятыми. * @param integer $offset SQL OFFSET * @param string $limit SQL LIMIT * @param integer &$count=-1 количество работ, если пользователь определил в настройках разделы или стоит галка "Все разделы". Количество тем сообществ, считается отдельно * @param mixed $blog_groups строка идентификаторов разделов блогов, разделенных запятыми. * * @return array массив тем в случае успеха, 0 в случае неудачи */ function GetLentaItems($user_id, $my_team_checked = 0, $all_profs_checked = 0, $prof_groups = NULL, $communes = NULL, $offset = 0, $limit = 'ALL', &$count = -1, $blog_groups = NULL) { global $DB; if ($my_team_checked) { $DBProxy = new DB('plproxy'); $sql = "SELECT uid FROM teams_get(?i);"; $quids_team = $DBProxy->rows($sql, $user_id); $uids_team = array(); $uids_team[] = 0; if ($quids_team) { foreach ($quids_team as $uid_team) { $uids_team[] = $uid_team['uid']; } } } $sql = "\n SELECT \n li.*,\n u.is_banned::int as user_is_banned,\n u.is_pro as user_is_pro,\n u.is_profi AS user_is_profi,\n u.is_team as user_is_team,\n u.is_pro_test as user_is_pro_test,\n u.role as user_role,\n u.login as user_login,\n u.photo as user_photo,\n u.usurname as user_usurname,\n u.uname as user_uname,\n u.reg_date, u.is_chuck, u.is_verify\n FROM\n (" . (!$all_profs_checked && !$prof_groups ? '' : "\n SELECT\n 1 as item_type,\n pf.user_id as user_id, \n pf.post_date as post_time,\n NULL as id,\n 'PF-' || pf.id as key,\n NULL::integer as parent_id,\n NULL::integer as theme_id,\n NULL as msgtext,\n NULL as title,\n NULL::integer as deleted_id,\n NULL::integer as modified_id,\n NULL as created_time,\n NULL as deleted_time,\n NULL as modified_time,\n NULL as file_exists,\n NULL::integer as commune_id,\n NULL as a_count,\n NULL as is_blocked,\n NULL as last_activity,\n NULL::integer as commune_group_id,\n NULL as commune_group_name,\n NULL as commune_name,\n NULL::integer as commune_author_id,\n NULL as member_warn_count,\n NULL::integer as member_id,\n NULL as member_is_banned,\n NULL as member_is_admin,\n NULL as last_viewed_time,\n NULL as modified_login,\n NULL as modified_usurname,\n NULL as modified_uname,\n NULL as modified_by_commune_admin,\n pf.id as portfolio_id,\n pf.name as name,\n pf.link as link,\n pf.descr as descr,\n pf.pict as pict,\n pf.prev_pict as prev_pict,\n pf.prof_id as prof_id,\n p.name as prof_name,\n p.id as prof_id, \n NULL as question,\n NULL::boolean as poll_closed,\n\t\t\t\t\t NULL::boolean as poll_multiple,\n NULL::bigint as poll_votes,\n NULL::boolean as close_comments,\n NULL as is_private,\n NULL::smallint as current_count,\nNULL as dfl_title,\nNULL as dfl_description,\nNULL as dfl_type,\n0 as dfl_jury_id,\nNULL as dfl_image,\n0 as dfl_type_id,\nNULL as yt_link,\n0 as count_comments,\n0 as status_comments,\n/*pf.moderator_status,*/\npfb.admin AS work_is_blocked\n FROM\n (\n SELECT DISTINCT COALESCE(m.main_prof, p.id) as id\n FROM prof_group pg\n INNER JOIN\n professions p\n ON p.prof_group = pg.id\n LEFT JOIN\n mirrored_professions m\n ON m.mirror_prof = p.id\n " . (!$prof_groups ? '' : " WHERE pg.id IN ({$prof_groups})") . "\n ) as px\n INNER JOIN\n portf_choise pc\n ON pc.prof_id = px.id\n AND pc.user_id <> {$user_id}\n INNER JOIN\n professions p\n ON p.id = COALESCE(pc.prof_origin, pc.prof_id)\n INNER JOIN\n portfolio pf\n ON pf.prof_id = pc.prof_id\n AND pf.user_id = pc.user_id\n AND pf.post_date > now() - '1 month'::interval\n /*AND (pf.moderator_status != 0 OR pf.moderator_status IS NULL)*/\n " . (!$my_team_checked ? '' : " \n AND pf.user_id IN (?l)\n ") . "LEFT JOIN\n portfolio_blocked AS pfb\n ON pfb.src_id = pf.id" . ($communes || $blog_groups ? " UNION ALL" : '') . "\n ") . (!$communes ? '' : "\n SELECT \n 2 as item_type,\n ms.user_id as user_id,\n ms.created_time as post_time,\n ms.id as id,\n 'CM-' || ms.id as key,\n ms.parent_id as parent_id,\n ms.theme_id as theme_id,\n ms.msgtext as msgtext,\n ms.title as title,\n ms.deleted_id as deleted_id,\n ms.modified_id as modified_id,\n ms.created_time as created_time,\n ms.deleted_time as deleted_time,\n ms.modified_time as modified_time,\n ms.cnt_files as file_exists,\n t.commune_id as commune_id,\n t.a_count as a_count,\n (CASE WHEN ctb.blocked_time IS NOT NULL THEN ctb.blocked_time\n WHEN t.blocked_time IS NOT NULL THEN t.blocked_time\n WHEN ms.deleted_time IS NOT NULL THEN ms.deleted_time\n ELSE NULL END) as is_blocked,\n t.last_activity as last_activity,\n cg.id as commune_group_id,\n cg.name as commune_group_name,\n cm.name as commune_name,\n cm.author_id as commune_author_id,\n m.warn_count as member_warn_count,\n m.id as member_id,\n m.is_banned::int as member_is_banned,\n m.is_admin::int as member_is_admin,\n um.last_viewed_time as last_viewed_time,\n umm.login as modified_login,\n umm.usurname as modified_usurname,\n umm.uname as modified_uname,\n (am.user_id IS NOT NULL)::int as modified_by_commune_admin,\n NULL as portfolio_id,\n NULL as name,\n NULL as link,\n NULL as descr,\n NULL as pict,\n NULL as prev_pict,\n NULL as prof_id,\n NULL as prof_name,\n NULL as prof_id, \n \t\t\t\t cp.question as question,\n \t\t\t\t cp.closed as poll_closed,\n\t\t\t\t\t cp.multiple as poll_multiple,\n \t\t\t\t cv._cnt as poll_votes,\n t.close_comments as closed_comments,\n t.is_private as is_private,\n um.current_count as current_count,\nNULL as dfl_title,\nNULL as dfl_description,\nNULL as dfl_type,\n0 as dfl_jury_id,\nNULL as dfl_image,\n0 as dfl_type_id,\nms.youtube_link as yt_link,\nt.a_count-1 as count_comments,\num.current_count as status_comments,\n/*ms.moderator_status, */\nNULL AS work_is_blocked\n FROM commune_themes t\n INNER JOIN\n commune cm\n ON cm.id = t.commune_id\n INNER JOIN\n commune_groups cg\n ON cg.id = cm.group_id\n INNER JOIN\n commune_messages ms\n ON ms.theme_id = t.id\n AND ms.parent_id IS NULL\n AND ms.created_time > now() - '1 month'::interval\n /*AND (ms.moderator_status != 0 OR ms.moderator_status IS NULL)*/\n LEFT JOIN\n commune_members m\n ON m.user_id = ms.user_id\n AND m.commune_id = t.commune_id\n LEFT JOIN\n users umm\n ON umm.uid = ms.modified_id\n LEFT JOIN\n commune_members am\n ON am.user_id = umm.uid\n AND am.commune_id = cm.id\n AND am.is_admin = true\n\t\t\tLEFT JOIN\n\t\t\t commune_poll cp\n\t\t\t ON cp.theme_id = ms.theme_id\n LEFT JOIN\n commune_theme_blocked ctb\n ON ctb.theme_id = t.id\n\t\t\tLEFT JOIN\n\t\t\t (SELECT theme_id, COUNT(answer_id) AS _cnt FROM commune_poll_votes WHERE user_id = {$user_id} GROUP BY theme_id) cv\n\t\t\t ON cv.theme_id = ms.theme_id\n LEFT JOIN\n commune_users_messages um\n ON um.message_id = ms.id\n AND um.user_id = {$user_id}\n WHERE t.commune_id IN ({$communes})\n " . ($blog_groups ? " UNION ALL" : '')) . (!$blog_groups ? '' : "\nSELECT \n 4 as item_type,\n bm.fromuser_id as user_id,\n bm.post_time as post_time,\n bm.id as id,\n 'BL-' || bm.id as key,\n NULL as parent_id,\n b.thread_id as theme_id,\n bm.msgtext as msgtext,\n bm.title as title,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n bg.id as commune_group_id,\n bg.t_name as commune_group_name,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n bp.question as question,\n bp.closed as poll_closed,\n\t\t\t\t\tbp.multiple as poll_multiple,\n bv._cnt as poll_votes,\n b.close_comments as closed_comments,\n NULL,\n w.status AS current_count,\nNULL,\nNULL,\nNULL,\nNULL,\nNULL,\nNULL,\nbm.yt_link as yt_link,\n(b.messages_cnt-1) as count_comments ,\nw.status as status_comments,\n/*bm.moderator_status, */\nNULL AS work_is_blocked \nFROM blogs_themes b \nINNER JOIN blogs_msgs_" . date('Y') . " bm\n ON bm.thread_id = b.thread_id\n AND bm.reply_to IS NULL\n AND (b.is_private='f' OR bm.fromuser_id={$user_id})\n AND bm.post_time > now() - '1 month'::interval\n /*AND (bm.moderator_status != 0 OR bm.moderator_status IS NULL)*/\nLEFT JOIN blogs_blocked ON blogs_blocked.thread_id = b.thread_id\nINNER JOIN blogs_groups bg\n ON bg.id = b.id_gr\nLEFT JOIN blogs_poll bp\n ON bp.thread_id = bm.thread_id\nLEFT JOIN (SELECT thread_id, COUNT(answer_id) AS _cnt FROM blogs_poll_votes WHERE user_id = {$user_id} GROUP BY thread_id) bv\n ON bv.thread_id = bm.thread_id\nLEFT JOIN (SELECT * FROM blogs_themes_watch WHERE user_id = '{$user_id}') AS w ON (theme_id=b.thread_id)\n\n \nWHERE b.id_gr IN ({$blog_groups}) AND bm.deleted IS NULL AND blogs_blocked.thread_id IS NULL\n\n " . (date('n') < 2 ? " \n \n UNION ALL\n \n SELECT \n 4 as item_type,\n bm.fromuser_id as user_id,\n bm.post_time as post_time,\n bm.id as id,\n 'BL-' || bm.id as key,\n NULL as parent_id,\n b.thread_id as theme_id,\n bm.msgtext as msgtext,\n bm.title as title,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n bg.id as commune_group_id,\n bg.t_name as commune_group_name,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n bp.question as question,\n bp.closed as poll_closed,\n\t\t\t\t\tbp.multiple as poll_multiple,\n bv._cnt as poll_votes,\n b.close_comments as closed_comments,\n NULL,\n w.status AS current_count,\nNULL,\nNULL,\nNULL,\nNULL,\nNULL,\nNULL,\nbm.yt_link as yt_link,\n(b.messages_cnt-1) as count_comments ,\nw.status as status_comments,\n/*bm.moderator_status, */\nNULL AS work_is_blocked \nFROM blogs_themes b \nINNER JOIN blogs_msgs_" . (date('Y') - 1) . " bm\n ON bm.thread_id = b.thread_id\n AND bm.reply_to IS NULL\n AND (b.is_private='f' OR bm.fromuser_id={$user_id})\n AND bm.post_time > now() - '1 month'::interval\n /*AND (bm.moderator_status != 0 OR bm.moderator_status IS NULL)*/\nLEFT JOIN blogs_blocked ON blogs_blocked.thread_id = b.thread_id\nINNER JOIN blogs_groups bg\n ON bg.id = b.id_gr\nLEFT JOIN blogs_poll bp\n ON bp.thread_id = bm.thread_id\nLEFT JOIN (SELECT thread_id, COUNT(answer_id) AS _cnt FROM blogs_poll_votes WHERE user_id = {$user_id} GROUP BY thread_id) bv\n ON bv.thread_id = bm.thread_id\nLEFT JOIN (SELECT * FROM blogs_themes_watch WHERE user_id = '{$user_id}') AS w ON (theme_id=b.thread_id)\n\n \nWHERE b.id_gr IN ({$blog_groups}) AND bm.deleted IS NULL AND blogs_blocked.thread_id IS NULL\n\n " : '')) . "\n\n ) AS li\n INNER JOIN\n users u\n ON u.uid = li.user_id\n AND u.is_banned = '0'\n \n\n ORDER BY li.post_time DESC" . ($all_profs_checked || $prof_groups ? ', li.portfolio_id DESC' : '') . "\n LIMIT {$limit} OFFSET {$offset}\n "; $res = $DB->rows($sql, $uids_team); if ($res) { foreach ($res as $row) { $ret[$row['key']] = $row; if ($row['item_type'] == 2) { $ids2[] = $row['id']; } if ($row['item_type'] == 4) { $ids4[] = $row['id']; } // if($row['id']) $ids[] = $row['id']; } if ($ids2) { //$sql = "SELECT file.*, commune_attach.cid, commune_attach.small FROM commune_attach JOIN file_commune as file ON file.id = commune_attach.fid WHERE commune_attach.cid IN (".implode(", ", $ids2).")"; //$res2 = $DB->rows($sql); $res2 = CFile::selectFilesBySrc(commune::FILE_TABLE, $ids2); foreach ($res2 as $row) { $ret['CM-' . $row['src_id']]['attach'][] = $row; } } if ($ids4) { $sql = 'SELECT * FROM file_blogs WHERE src_id IN (?l)'; $res2 = $DB->rows($sql, $ids4); foreach ($res2 as $row) { $ret['BL-' . $row['src_id']]['attach'][] = $row; } } $count = 0; if ($all_profs_checked || $prof_groups || $communes || $blog_groups) { $sql = "SELECT SUM(items.count) as count FROM (" . (!$all_profs_checked && !$prof_groups ? '' : "\n SELECT\n COUNT(pf.id) as count\n FROM (\n SELECT DISTINCT COALESCE(m.main_prof, p.id) as id\n FROM prof_group pg\n INNER JOIN professions p ON p.prof_group = pg.id\n LEFT JOIN mirrored_professions m ON m.mirror_prof = p.id\n " . (!$prof_groups ? '' : " WHERE pg.id IN ({$prof_groups})") . "\n ) as px\n INNER JOIN portf_choise pc\n ON pc.prof_id = px.id\n AND pc.user_id <> {$user_id}\n INNER JOIN professions p\n ON p.id = COALESCE(pc.prof_origin, pc.prof_id)\n INNER JOIN portfolio pf\n ON pf.prof_id = pc.prof_id\n AND pf.user_id = pc.user_id\n AND pf.post_date > now() - '1 month'::interval" . (!$my_team_checked ? '' : " \n AND pf.user_id IN (" . implode(',', $uids_team) . ")\n ") . ($communes || $blog_groups ? " UNION ALL" : '')) . (!$communes ? '' : "\n SELECT \n COUNT(ms.id) as count\n FROM commune_themes t\n INNER JOIN commune_messages ms \n ON ms.theme_id = t.id\n AND ms.parent_id IS NULL\n AND ms.created_time > now() - '1 month'::interval\n INNER JOIN users u\n ON u.uid = ms.user_id\n AND u.is_banned = '0'\n WHERE t.commune_id IN ({$communes})\n " . ($blog_groups ? " UNION ALL" : '')) . (!$blog_groups ? '' : "\n SELECT COUNT(1) as count\n FROM blogs_themes b \n WHERE b.id_gr IN ({$blog_groups}) AND b.deleted IS NULL AND b.is_blocked = false\n AND (b.is_private='f' OR b.fromuser_id={$user_id})\n AND b.post_time > now() - '1 month'::interval\n ") . ") as items"; /* $sql = " SELECT COUNT(pf.id) as count FROM prof_group pg INNER JOIN professions p ON p.prof_group = pg.id INNER JOIN portf_choise pc ON pc.prof_id = p.id AND pc.user_id <> {$user_id} INNER JOIN portfolio pf ON pf.prof_id = pc.prof_id AND pf.user_id = pc.user_id INNER JOIN freelancer f ON f.uid = pf.user_id AND f.is_banned = '0'". ( !$my_team_checked ? '' : " INNER JOIN teams tm ON tm.target_id = f.uid AND tm.user_id = {$user_id}" ). " WHERE pf.post_date > now() - '1 month'::interval " . ( !$prof_groups ? '' : " AND pg.id IN ({$prof_groups})" ); */ $memBuff = new memBuff(); $count_arr = $memBuff->getSql($error, $sql, 120); if (!$error) { $count = $count_arr[0]['count']; } } return $ret; } return 0; }
/** * Обрабатывает POST переменную action и возвращает null или данные. * * @return string */ public function processRequest() { $expire = 1; //3600; $action = __paramInit('string', '', 'action'); switch ($action) { case 'getdays': $days = array(1 => iconv('WINDOWS-1251', 'UTF-8//IGNORE', '1 день')); $max = __paramInit('integer', '', 'max', 1); $all = array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 14, 21, 30, 45, 60, 90); if ($max > 1) { $days = array(); foreach ($all as $day) { if ($day >= $max) { break; } $days[$day] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $day . ending($day, ' день', ' дня', ' дней')); } } $days = array(array('parentId' => '0'), $days); return json_encode($days); break; case 'gettucategories': $expire = 300; //3600; $membuf = new memBuff(); $memkey = 'b-combo-gettucategories'; $result = $membuf->get($memkey); if (!$result) { /* * Получаем из базы иерархию категорий для * типовой услуги */ $DB = new DB('master'); $sql = 'SELECT g.id AS gid, g.title AS gname, g.ucnt AS gucnt, p.id AS pid, p.title AS name, p.ucnt AS pucnt FROM tservices_categories AS g INNER JOIN tservices_categories AS p ON p.parent_id = g.id ORDER BY g.ucnt DESC, p.ucnt DESC --gid, pid --g.n_order, p.n_order'; $rows = $DB->rows($sql); $result = array(); if (count($rows)) { foreach ($rows as $k => $i) { if ($result[$i['gid']] === null) { $result[$i['gid']] = array('0' => array(iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['gname']), $i['gucnt'])); if ($i['pid'] !== null) { $result[$i['gid']][$i['pid']] = array(iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['name']), $i['pucnt']); } else { $result[$i['gid']] = array(iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['gname']), $i['gucnt']); } } elseif (is_array($result[$i['gid']])) { $result[$i['gid']][$i['pid']] = array(iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['name']), $i['pucnt']); } } } //print_r($result); //exit; $result = json_encode($result); $membuf->add($memkey, $result, $expire); } return $result; break; case 'getlettergrouplist': require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/letters.php'; $lettergroups = letters::getGroups($_POST['word'], (int) $_POST['limit']); $result = array(); $n = 0; foreach ($lettergroups as $k => $i) { $result[$n]['uid'] = $i['id']; $result[$n]['uname'] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['title']); $result[$n]['usurname'] = ''; $result[$n]['login'] = ''; $result[$n]['photo'] = ''; $result[$n]['path'] = ''; $result[$n]['isContacts'] = ''; ++$n; } $list = array(); $list['list'] = $result; $list['counters']['moreContacts'] = 0; $list['counters']['moreUsers'] = 0; $list['dav'] = WDCPREFIX; return json_encode($list); break; case 'getlettergroupinfo': require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/letters.php'; $group = letters::getGroup($_POST['uid']); if ($group) { $data = array('record' => array('uid' => $group['id'], 'uname' => iconv('WINDOWS-1251', 'UTF-8//IGNORE', $group['title']), 'usurname' => '', 'login' => '', 'photo' => '', 'path' => '', 'isContacts' => ''), 'found' => 1, 'dav' => WDCPREFIX); } return json_encode($data); break; case 'getletterdocinfo': require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/letters.php'; $doc = letters::getDocument($_POST['uid']); if ($doc) { $data = array('record' => array('uid' => $doc['id'], 'uname' => iconv('WINDOWS-1251', 'UTF-8//IGNORE', $doc['id'] . ' ' . htmlspecialchars($doc['group_title']) . ' - ' . htmlspecialchars($doc['title'])), 'usurname' => '', 'login' => '', 'photo' => '', 'path' => '', 'isContacts' => ''), 'found' => 1, 'dav' => WDCPREFIX); } return json_encode($data); break; case 'getletterdoclist': require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/letters.php'; $letterdocs = letters::getDocuments($_POST['word'], (int) $_POST['limit']); $result = array(); $n = 0; foreach ($letterdocs as $k => $i) { $result[$n]['uid'] = $i['id']; $result[$n]['uname'] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['id'] . ' ' . htmlspecialchars($i['group_title']) . ' - ' . htmlspecialchars($i['title'])); $result[$n]['usurname'] = ''; $result[$n]['login'] = ''; $result[$n]['photo'] = ''; $result[$n]['path'] = ''; $result[$n]['isContacts'] = ''; ++$n; } $list = array(); $list['list'] = $result; $list['counters']['moreContacts'] = 0; $list['counters']['moreUsers'] = 0; $list['dav'] = WDCPREFIX; return json_encode($list); break; case 'getletterdocsearch': require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/letters.php'; $letterdocs = letters::getSearchDocuments($_POST['word'], (int) $_POST['limit']); $result = array(); $n = 0; foreach ($letterdocs as $k => $i) { $result[$n]['uid'] = $i['id']; $result[$n]['uname'] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['id'] . ' ' . htmlspecialchars($i['group_title']) . ' - ' . htmlspecialchars($i['title'])); $result[$n]['usurname'] = ''; $result[$n]['login'] = ''; $result[$n]['photo'] = ''; $result[$n]['path'] = ''; $result[$n]['isContacts'] = ''; ++$n; } $list = array(); $list['list'] = $result; $list['counters']['moreContacts'] = 0; $list['counters']['moreUsers'] = 0; $list['dav'] = WDCPREFIX; return json_encode($list); break; case 'getdate': return date('Y-m-d'); case 'getprofgroups': $membuf = new memBuff(); $memkey = 'b-combo-getprofgroups'; $result = $membuf->get($memkey); if (!$result) { $rows = professions::GetAllGroupsLite(); $result = array(); foreach ($rows as $k => $i) { $result[$i['id']] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['name']); } $membuf->add($memkey, $result, $expire); } return json_encode($result); case 'getprofessionsandgroups': $membuf = new memBuff(); $memkey = 'b-combo-getprofandgroups'; $result = $membuf->get($memkey); if (!$result) { $rows = professions::GetProfessionsAndGroup(); $result = array(); foreach ($rows as $k => $i) { if ($result[$i['gid']] === null) { $result[$i['gid']] = array($i['gid'] => iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['gname'])); if ($i['id'] !== null) { $result[$i['gid']][$i['id']] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['name']); } else { $result[$i['gid']] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['gname']); } } elseif (is_array($result[$i['gid']])) { $result[$i['gid']][$i['id']] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['name']); } } $membuf->add($memkey, $result, $expire); } return json_encode($result); case 'getprofessions': $n = __paramInit('integer', '', 'id'); if ($n !== false) { $membuf = new memBuff(); $memkey = "b-combo-getprofbygroup{$n}"; $result = $membuf->get($memkey); if (!$result) { $rows = professions::GetProfs($n); $result = array(0 => iconv('WINDOWS-1251', 'UTF-8//IGNORE', 'Все специальности')); foreach ($rows as $k => $i) { $result[$i['id']] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['name']); } $membuf->add($memkey, $result, $expire); } $data = array(array('parentId' => "{$n}"), $result); return json_encode($data); } case 'get_pro_types': $membuf = new memBuff(); $memkey = 'b-combo-get_pro_type'; $result = $membuf->get($memkey); if (!$result) { $rows = op_codes::getCodes(array(47, 48, 49, 50, 51)); $result = array(); foreach ($rows as $k => $i) { $result[$i['id']] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['op_name']); } $result[76] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', 'На несколько недель'); $membuf->add($memkey, $result, $expire); } return json_encode($result); case 'getcountries': case 'getrelevantcountries': $membuf = new memBuff(); $memkey = 'b-combo-getcountriesr'; $result = $membuf->get($memkey); if (!$result) { $rows = country::GetCountriesByCountUser(); $result = array(); foreach ($rows as $k => $i) { $result[$i['id']] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i['name']); } $membuf->add($memkey, $result, $expire); } return json_encode($result); case 'getcities': $n = __paramInit('integer', '', 'id'); if ($n !== false) { $membuf = new memBuff(); $memkey = "b-combo-getcitybycountry{$n}"; $result = $membuf->get($memkey); if (!$result) { $rows = city::GetCities($n); $result = array('0' => iconv('WINDOWS-1251', 'UTF-8//IGNORE', 'Все города')); if (is_array($rows)) { foreach ($rows as $k => $i) { $result[$k] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i); } } $membuf->add($memkey, $result, $expire); } $data = array(array('parentId' => "{$n}"), $result); return json_encode($data); } case 'getcitiesbyid': $n = __paramInit('integer', '', 'id'); if ($n !== false) { $membuf = new memBuff(); $memkey = "b-combo-getcitybycountry{$n}"; $result = $membuf->get($memkey); if (!$result) { $rows = city::GetCities($n); $result = array('0' => iconv('WINDOWS-1251', 'UTF-8//IGNORE', 'Все города')); foreach ($rows as $k => $i) { $result[$k] = iconv('WINDOWS-1251', 'UTF-8//IGNORE', $i); } $membuf->add($memkey, $result, $expire); } return json_encode($result); } case 'getuserlistbysbr': case 'getuserlistold': case 'getuserlist': return $this->getUsersList($_POST['word'], (int) $_POST['limit'], (int) $_POST['userType'], (int) $_POST['scope']); case 'get_user_info': return json_encode(users::GetUserShortInfo((int) $_POST['uid'])); case 'getusersandcompanies': return $this->getUsersAndCompany($_POST['word'], (int) $_POST['limit'], (int) $_POST['userType'], (int) $_POST['scope'], false); case 'get_user_or_company_info': return $this->getUserOrCompanyRecord(); case 'getsms': require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/registration.php'; $registration = new registration(); $registration->listenerAction(__paramInit('string', null, 'action')); } return false; }
$db2 = new DB(); $db->query("SELECT * FROM pw_dyn_dreamvote WHERE house = 'scotland'"); if ($db->rows() > 0) { print "There are rows in pw_dyn_dreamvote with house = 'scotland'.\n"; print "This script assumes that there are no votes from policies on\n"; print "Scottish Parliament divisions yet.\n"; exit(-1); } $db->query("SELECT * FROM pw_dyn_dreamvote ORDER BY division_date, division_number"); while ($row = $db->fetch_row_assoc()) { $dream_id = $row['dream_id']; $division_date = $row['division_date']; $division_number = $row['division_number']; $possibly_wrong_house = $row['house']; $db2->query("SELECT * FROM pw_division WHERE\n division_date = '{$division_date}' AND\n division_number = '{$division_number}' AND\n house != 'scotland'"); $nrows = $db2->rows(); if ($nrows == 0) { print "### MISSING: {$nrows} house {$possibly_wrong_house}, dream_id {$dream_id}, date {$division_date}, division_number {$division_number}\n"; } elseif ($nrows == 1) { $row2 = $db2->fetch_row_assoc(); $correct_house = $row2['house']; if ($correct_house != $possibly_wrong_house) { print "{$nrows} house {$possibly_wrong_house}, dream_id {$dream_id}, date {$division_date}, division_number {$division_number}\n"; print " Should correct house to: {$correct_house}\n"; if ($repair) { print " Repairing... "; $db2->query("UPDATE pw_dyn_dreamvote SET house = '{$correct_house}' WHERE\n division_date = '{$division_date}' AND\n division_number = {$division_number} AND\n dream_id = {$dream_id}"); print "done\n"; } } } else {
/** * Получить контакты из автоматической папки пользователя. * * @param string $sUid UID пользователя. * @param string $sFolderId папка, для которой получаем контакты * @param string $sSearch подстрока для поиска в логине пользователя * * @return array */ public function pmAutoFolderGetContacts($sUid = '', $sFolderId = '', $sSearch = '') { $DBproxy = new DB(); if ($sSearch) { $sQuery = $DBproxy->parse('SELECT * FROM messages_search_pm_folder(?, ?, ?)', $sUid, $sFolderId, $sSearch); } else { $sQuery = $DBproxy->parse('SELECT * FROM messages_contacts_pm_folder(?, ?)', $sUid, $sFolderId); } return $DBproxy->rows($sQuery); }
/** * Возвращает общее количество переходов, на страницы пользователей, с платных мест на главной (первое, второе и последние места) * и с платных мест в общем каталоге (первое место) за указанный период. * @param string $from_date начало периода в формате postgresql (пользователь должен быть PRO весь этот день) * @param string $to_date конец периода в формате postgresql (пользователь должен быть PRO весь этот день) * @return array массив с данными или NULL, если данных нет или произошла ошибка */ function GetFromPSummary($from_date, $to_date) { $ret = NULL; $sDB = new DB('stat'); $sql = "SELECT pos_id, -- 1, 2, -1: на главной (первое, второе и последние места соот.). 0 -- первое место в общем каталоге.\n SUM(by_e) as by_e,\n SUM(by_f) as by_f,\n SUM(by_u) as by_u,\n SUM(by_e+by_f+by_u) as by_a\n FROM stat_from_p\n WHERE _date >= ? AND _date < ?\n GROUP BY pos_id"; $res = $sDB->rows($sql, $from_date, $to_date); if (!$sDB->error) { foreach ($res as $row) { $ret[$row['pos_id']] = $row; } } return $ret; }
/** * Начисление неначисленных подарков). */ public function failedGifts() { $db = new DB('master'); $sql = 'SELECT * FROM __tmp_failed_gifts WHERE processed = 0 order by billing_id, op_id'; $res = $db->rows($sql); $admin_uid = 103; // $admin_uid = 1; foreach ($res as $row) { $op_code = $row['op_code']; $payment_sys = $row['payment_sys']; $trs_sum = $row['trs_sum']; $gid = $row['uid']; $dep_id = $row['billing_id']; $is_emp = $row['is_emp']; // акция с Webmoney --------------------- if ($op_code == 12) { if ($payment_sys == 10 || $payment_sys == 2) { // WMR if (!$is_emp) { // подарок фрилансеру if ($trs_sum >= 2000 && $trs_sum < 5000) { require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/payed.php'; $payed = new payed(); $bill_id = $gift_id = 0; $tr_id = $this->start_transaction($admin_uid); $payed->GiftOrderedTarif($bill_id, $gift_id, $gid, $admin_uid, $tr_id, '1', 'Аккаунт PRO в подарок при пополнении счета с помощью WebMoney', 91); } elseif ($trs_sum >= 5000) { require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/firstpage.php'; $bill_id = $gift_id = 0; $tr_id = $this->start_transaction($admin_uid); $payed = new firstpage(); $payed->GiftOrdered($bill_id, $gift_id, $gid, $admin_uid, $tr_id, '1 week', 93, 'Первая страница в подарок при пополнении счета с помощью WebMoney', 'Первая страница в подарок при пополнении счета с помощью WebMoney'); } } else { // подарок работодателю if ($trs_sum >= 1000 && $trs_sum < 5000) { require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/payed.php'; $payed = new payed(); $bill_id = $gift_id = 0; $tr_id = $this->start_transaction($admin_uid); $payed->GiftOrderedTarif($bill_id, $gift_id, $gid, $admin_uid, $tr_id, '1', 'Аккаунт PRO в подарок при пополнении счета с помощью WebMoney', 92); } elseif ($trs_sum >= 5000) { $bill_id = $gift_id = 0; $tr_id = $this->start_transaction($admin_uid); $error = $this->Gift($bill_id, $gift_id, $tr_id, 93, $admin_uid, $gid, 'Платный проект в подарок при пополнении счета с помощью WebMoney', ''); if ($error === 0) { // Добавляем подарочное бабло на бонусный счет. $this->depositBonusEx($dep_id, 85, 'Начисление денег на платный проект в подарок', '', 40); } } } } else { // WMZ - пока нет } } //акция банк/сбер if ($op_code == 12 && ($payment_sys == 4 || $payment_sys == 5)) { $_opstr = $payment_sys == 5 ? 'через квитанцию Сбербанка' : 'через безналичный расчет'; if (!$is_emp) { // подарок фрилансеру if ($trs_sum >= 2000 && $trs_sum < 5000) { require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/payed.php'; $_opcode = $payment_sys == 5 ? 95 : 99; $payed = new payed(); $bill_id = $gift_id = 0; $tr_id = $this->start_transaction($admin_uid); $payed->GiftOrderedTarif($bill_id, $gift_id, $gid, $admin_uid, $tr_id, '1', "Аккаунт PRO в подарок при пополнении счета {$_opstr}", $_opcode); } elseif ($trs_sum >= 5000) { require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/firstpage.php'; $_opcode = $payment_sys == 5 ? 97 : 101; $bill_id = $gift_id = 0; $tr_id = $this->start_transaction($admin_uid); $payed = new firstpage(); $payed->GiftOrdered($bill_id, $gift_id, $gid, $admin_uid, $tr_id, '1 week', $_opcode, "Первая страница в подарок при пополнении счета {$_opstr}", "Первая страница в подарок при пополнении счета {$_opstr}"); } } else { // подарок работодателю if ($trs_sum >= 1000 && $trs_sum < 5000) { require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/payed.php'; $_opcode = $payment_sys == 5 ? 96 : 100; $payed = new payed(); $bill_id = $gift_id = 0; $tr_id = $this->start_transaction($admin_uid); $payed->GiftOrderedTarif($bill_id, $gift_id, $gid, $admin_uid, $tr_id, '1', "Аккаунт PRO в подарок при пополнении счета {$_opstr}", $_opcode); } elseif ($trs_sum >= 5000) { $bill_id = $gift_id = 0; $tr_id = $this->start_transaction($admin_uid); $_opcode = $payment_sys == 5 ? 97 : 101; $error = $this->Gift($bill_id, $gift_id, $tr_id, $_opcode, $admin_uid, $gid, "Платный проект в подарок при пополнении счета {$_opstr}", "Платный проект в подарок при пополнении счета {$_opstr}"); if ($error === 0) { // Добавляем подарочное бабло на бонусный счет. $this->depositBonusEx($dep_id, 85, 'Начисление денег на платный проект в подарок', '', 40); } } } } var_dump($row['op_id']); $db->update('__tmp_failed_gifts', array('processed' => 1), 'op_id = ?', $row['op_id']); } }
<?php if (!defined('IS_SITE_ADMIN')) { header('Location: /404.php'); exit; } require_once $_SERVER['DOCUMENT_ROOT'] . "/classes/payed.php"; require_once $_SERVER['DOCUMENT_ROOT'] . "/classes/account.php"; $DB = new DB('master'); $sql = "SELECT * FROM stat_login ORDER BY date DESC"; $stats = $DB->rows($sql); $monthName = array(1 => "Январь", 2 => "Февраль", 3 => "Март", 4 => "Апрель", 5 => "Май", 6 => "Июнь", 7 => "Июль", 8 => "Август", 9 => "Сентябрь", 10 => "Октябрь", 11 => "Ноябрь", 12 => "Декабрь"); ?> <table cellpadding="0" cellspacing="0" border="0" width="100%"> <tr> <td align="left"><strong>Статистика</strong></td> <td align="right"><a href="/siteadmin/stats/charts.php">График</a></td> </tr> </table> <br><br> <?php $mVerify = true; require_once "top_menu.php"; ?> <br><br> <?php $action = trim($_GET['action']);
print "\n"; print "ERROR\n"; print "Example: php one_day.php start\n"; exit; } require_once '../classes/config.php'; require_once '../classes/DB.php'; $DB = new DB('master'); $setting = array('from' => '2011-10-12 23:00', 'to' => '2011-10-13 12:00', 'day' => '1 day'); switch ($argv[1]) { case 'start': $csv = ''; print "\n"; print "Processing...\n\n"; $sql = "SELECT oc.op_name, o.billing_id, u.login FROM orders o\n INNER JOIN users u ON u.uid = o.from_id \n INNER JOIN op_codes oc ON oc.id = o.tarif\n WHERE from_date + to_date >= '{$setting['from']}' AND from_date <= '{$setting['to']}' AND payed = 't'\n \n UNION ALL\n \n SELECT oc.op_name, o.billing_id, u.login FROM users_first_page o\n INNER JOIN users u ON u.uid = o.user_id \n INNER JOIN op_codes oc ON oc.id = o.tarif\n WHERE from_date + to_date >= '{$setting['from']}' AND from_date <= '{$setting['to']}' AND payed = 't'\n \n UNION ALL\n \n SELECT oc.op_name, o.billing_id, u.login FROM projects o\n INNER JOIN users u ON u.uid = o.user_id \n INNER JOIN account_operations ao ON ao.id = o.billing_id\n INNER JOIN op_codes oc ON oc.id = ao.op_code\n WHERE top_to >= '{$setting['from']}' AND top_from <= '{$setting['to']}'"; $result = $DB->rows($sql); if ($result) { foreach ($result as $row) { $csv .= implode(';', $row) . "\n"; } } $update = "UPDATE orders SET to_date = to_date+'{$setting['day']}'::interval WHERE from_date + to_date >= '{$setting['from']}' AND from_date <= '{$setting['to']}' AND payed = 't';\n UPDATE users_first_page SET to_date = to_date+'{$setting['day']}'::interval WHERE from_date + to_date >= '{$setting['from']}' AND from_date <= '{$setting['to']}' AND payed = 't';\n UPDATE projects SET top_to = top_to + '{$setting['day']}' WHERE top_to >= '{$setting['from']}' AND top_from <= '{$setting['to']}';"; $error = $DB->query($update); if ($error != null) { file_put_contents(LOG_FILE, $csv); echo "\nDONE\n"; } else { echo "\nERROR :: {$error}\n"; } break; default:
/** * Уведомление Заказчикам о том, что надо зарезервировать деньги через сутки и трое суток после создания сделки. */ public function activateAccountNotice() { $DB = new DB('master'); $host = $GLOBALS['host']; $this->subject = 'Последний день для активации аккаунта'; $message = "<p>Пожалуйста, активируйте ваш аккаунт %NAME_LOGIN% в течении суток.<p>\n <p>Для активации достаточно перейти по указанной ссылке или скопировать ее в адресную строку браузера:</p>\n <p><a href='%LINK%' target='_blank'>%LINK%</a></p>\n <p>При возникновении проблем с активацией аккаунта рекомендуем вам <a href='https://feedback.fl.ru/' target='_blank'>ознакомиться с инструкцией</a> или <a href='https://feedback.fl.ru/' target='_blank'>написать нам</a>. Мы обязательно вам поможем.</p>\n <p>Информацию о резервировании и проведении сделок, а также ответы на все интересующие вопросы вы можете найти в нашем <a href='https://feedback.fl.ru/' target='_blank'>сообществе поддержки</a>.</p>\n "; $hours = 48; $time_limit = $hours + 24; $query = "SELECT u.email, u.login, u.uname, u.usurname, usk.key AS ukey, u.uid, ac.code\n FROM users AS u\n LEFT JOIN users_subscribe_keys AS usk ON usk.uid = u.uid\n LEFT JOIN activate_code AS ac ON ac.user_id = u.uid\n WHERE active = false \n AND NOW() - last_time > '{$hours} hours'::interval\n AND NOW() - last_time < '{$time_limit} hours'::interval;"; if ($_GET['debug'] == 1 && $_GET['activate'] == 1) { $query = $DB->parse('SELECT u.email, u.login, u.uname, u.usurname, usk.key AS ukey, u.uid, ac.code FROM users AS u LEFT JOIN users_subscribe_keys AS usk ON usk.uid = u.uid LEFT JOIN activate_code AS ac ON ac.user_id = u.uid WHERE u.login = ? LIMIT 1', $_GET['login']); } $users = $DB->rows($query); $this->message = $this->GetHtml(false, $message, array('header' => 'noname', 'footer' => 'feedback_default'), array('target_footer' => true)); $this->recipient = ''; $massId = $this->send('text/html'); require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/users.php'; $i = 0; $cnt = 0; $this->recipient = array(); foreach ($users as $row) { if ($row['email'] == '') { continue; } if (strlen($row['ukey']) == 0) { $row['ukey'] = users::writeUnsubscribeKey($row['uid'], true); } $link = $host . "/registration/activate.php?code={$row['code']}"; $name = trim($row['uname'] . ' ' . $row['usurname']); $name_login = ($name ? $name . ', ' : '') . $row['login']; $this->recipient[] = array('email' => $row['email'], 'extra' => array('USER_LOGIN' => $row['login'], 'UNSUBSCRIBE_KEY' => $row['ukey'], 'NAME_LOGIN' => $name_login, 'LINK' => $link)); if (++$i >= 30000) { $this->bind($massId); $this->recipient = array(); $i = 0; } ++$cnt; } $this->bind($massId); }
<?php $rpath = '../../'; require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/stdf.php'; $syear = $_GET['y']; $smonth = $_GET['m']; $DB = new DB('master'); $data = array(); $days = date('t', mktime(0, 0, 0, $smonth, 1, $syear)); for ($n = 1; $n <= $days; ++$n) { $data[$n] = 0; } $sql = 'SELECT u_pro, extract(day from date) as day FROM stat_data WHERE date>=? AND date<=?'; $q = $DB->rows($sql, $syear . '-' . $smonth . '-01', $syear . '-' . $smonth . '-' . $days); $is_null = 1; if ($q) { foreach ($q as $s) { if ($s['u_pro'] != 0) { $is_null = 0; } $data[$s['day']] = $s['u_pro']; } } // Standard inclusions include 'pChart/pData.class'; include 'pChart/pChart.class'; // Dataset definition $DataSet = new pData(); $DataSet->AddPoint($data, 'S1'); $DataSet->AddPoint(array_keys($data), 'S2'); $DataSet->AddSerie('S1');
$mail = new smtp(); $mail->subject = $eSubject; // заголовок письма $mail->message = $eMessage; // текст письма $mail->recipient = ''; // свойство 'получатель' оставляем пустым $spamid = $mail->send('text/html'); if (!$spamid) { die('Failed!'); } // с этого момента рассылка создана, но еще никому не отправлена! $mail->recipient = array(); $i = 0; //Отправить сообщения while ($rows = $M->rows($sql, $i)) { $pm_users = array(); foreach ($rows as $row) { unset($csv_users[$row['fromuser_id']]); if (strlen($row['ukey']) == 0) { $row['ukey'] = users::writeUnsubscribeKey($row['uid']); } if (is_email($row['email'])) { $mail->recipient[] = array('email' => $row['email'], 'extra' => array('first_name' => $row['uname'], 'last_name' => $row['usurname'], 'UNSUBSCRIBE_KEY' => $row['ukey'])); $mail->bind($spamid); $mail->recipient = array(); ++$cnt; } $pm_users[] = $row['uid']; ++$i; }
$iYear = InGet('y'); } } else { //echo $idMonth.'<br>'; //echo date('t',mktime(0,0,0, intval($idMonth), 1, intval($idYear))); $date_from = $idYear . '-' . $idMonth . '-1'; $date_to = $idYear . '-' . $idMonth . '-' . date('t', mktime(0, 0, 0, intval($idMonth), 1, intval($idYear))); $iMonth = $idMonth; $iYear = $idYear; } if ($bYear) { $sql = "SELECT country_name, COUNT(*) as cnt, country as country_id FROM users LEFT JOIN country ON users.country = country.id WHERE reg_date >= '" . $date_from . "' AND reg_date < '" . $date_to . "' GROUP BY country_name, country ORDER BY cnt DESC LIMIT 10"; } else { $sql = "SELECT country_name, COUNT(*) as cnt, country as country_id FROM users LEFT JOIN country ON users.country = country.id WHERE reg_date >= '" . $date_from . "' AND reg_date < '" . $date_to . "'::date+'1day'::interval GROUP BY country_name, country ORDER BY cnt DESC LIMIT 10"; } $countr = $DB->rows($sql); if (empty($countr)) { $countr[0]['country_name'] = ''; $countr[0]['cnt'] = ''; $countr[0]['country_id'] = '-1'; } $iMaxDays = $iMax = $bYear ? 12 : date('t', mktime(0, 0, 0, $iMonth, 1, $iYear)); //Вычисление максимального количества дней\месяцев в текущем месяце\годе $iFMperPX = !$bYear ? 30 : 30 * 10; //масштаб for ($i = 1; $i <= count($countr); ++$i) { for ($j = 0; $j <= $iMaxDays; ++$j) { $graphValues[$i][$j] = 0; } } $imgHeight = 0;
<?php echo htmlspecialchars($_GET['uname']); ?> [<?php echo htmlspecialchars($_GET['login']); ?> ]</strong> <br/><br/> <table width="100%" cellpadding="5" cellspacing="5"> <tr bgcolor="#eeeeee"> <td><strong>E-mail</strong></td> <td><strong>Дата</strong></td> </tr> <?php $sql = "SELECT * FROM users_change_emails_log WHERE uid=?i ORDER BY date desc"; $res = $DB->rows($sql, $_GET['uid']); if ($res) { foreach ($res as $log) { ?> <tr> <td><?php echo $log['email']; ?> </td> <td><?php echo $log['date']; ?> </td> </tr> <?php }
/** * Возвращает список папок. * * @return array */ function GetAll() { $DB = new DB(); return $DB->rows("SELECT * FROM messages_folders(?i)", $this->from_id); }
$stat = new DB("stat"); $_update = "UPDATE users_visits SET is_emp = CASE user_id\n{WHEN_MACROS}\nELSE is_emp END"; $_update2 = "UPDATE users_visits_daily SET is_emp = CASE user_id\n{WHEN_MACROS}\nELSE is_emp END"; /* WHEN THEN TRUE WHEN 238021 THEN FALSE * */ $count = 0; while (true) { $col = $stat->col($select); if (!is_array($col) || !$col) { echo "col is not array<br>"; break; } $uids = join(',', $col); $rows = $master->rows("SELECT uid, role FROM users WHERE uid IN ({$uids}) "); if (!is_array($rows) || !$rows) { $offset += $n; $select = "SELECT DISTINCT user_id FROM users_visits WHERE is_emp IS NULL OFFSET {$offset} LIMIT {$n}"; continue; } $when = array(); foreach ($rows as $row) { $bool = $row['role'][0] == 1 ? 'TRUE' : 'FALSE'; $when[] = "WHEN {$row['uid']} THEN {$bool}"; $count++; } if (count($when) == 0) { echo "users with uids ({$uids}) not fount in table users<br>"; break; }
$konk[$i] = account::GetStatOP($konkCodes, $fdate, $tdate); $upproj[$i] = account::GetStatOP(array(7), $fdate, $tdate); $transf[$i] = account::GetStatOP(array(23), $fdate, $tdate); $testbuypro[$i] = account::GetStatTestBuyPro($fdate, $tdate); $bonuses[$i] = account::GetStatBonuses($fdate, $tdate); // Статистика по регистрациям и привязке мобильных телефонов $regs = stats::getRegStats($fdate, $tdate); ?> <table border="1" cellspacing="2" cellpadding="2" class="brd-tbl"> <tr> <td width=200><strong>Проекты:</strong></td> <td> <?php $sql = "SELECT COUNT(*) as cnt FROM projects WHERE kind != 9 AND post_date >= ? AND post_date - '1 day'::interval < ?"; $s_project = $DB->rows($sql, $fdate, $tdate); ?> <?php echo $s_project[0]['cnt']; ?> </td> </tr> <tr> <td width=200><strong>Проекты (заблокированно):</strong></td> <td> <?php $sql = "SELECT COUNT(*) AS cnt FROM projects p INNER JOIN projects_blocked pb ON pb.project_id=p.id WHERE p.post_date >= ? AND p.post_date - '1 day'::interval < ?"; $s_bproject = $DB->rows($sql, $fdate, $tdate); ?> <?php echo $s_bproject[0]['cnt'];
/** * Возвращает заблокированые личные сообщения * * @param array $filter фильтр * @param type $last_id последний полученный ID * @param type $limit количество записей * @return array */ function getBlockedMessages($filter = array(), $last_id = 2147483647, $limit = 10) { $DB = new DB('plproxy'); $aFilter = array(); if (is_array($filter) && count($filter)) { foreach ($filter as $sKey => $sVal) { $aFilter[] = array($sKey, $sVal); } } $sQuery = 'SELECT m.* FROM messages_moder_deleted(?a, ?i, ?i) m ORDER BY m.id DESC'; $aReturn = $DB->rows($sQuery, $aFilter, $last_id, $limit); if ($aReturn) { $this->_getContentMessagesEx($aReturn); } return $aReturn; }
/** * Возвращает список жалоб о спаме, удовлетворяющих условиям выборки * * Внутренняя функция * * @param int $count возвращает количество записей удовлтворяющих условиям выборки * @param int $page номер текущей страницы * @param string order тип сортировки * @param int $direction порядок сортировки: 0 - по убыванию, не 0 - по возрастанию * @param bool $unlimited опционально. установить в true если нужно получить все записи (без постраничного вывода) * @return array */ function _getSpam(&$count, $page = 1, $order = 'general', $direction = 0, $unlimited = false) { $DB = new DB(); // plproxy $aFilter = array(); $this->aSQL = array(); $offset = $this->items_pp * ($page - 1); // строим запрос $this->_setSpamOrderBy($order, $direction); if (is_array($this->filter) && count($this->filter)) { foreach ($this->filter as $sKey => $sVal) { $aFilter[] = array($sKey, $sVal); } } $sQuery = 'SELECT * FROM messages_spam_get_list(?a) ' . ' ORDER BY ' . implode(', ', $this->aSQL['order_by']) . (!$unlimited ? ' LIMIT ' . $this->items_pp . ' OFFSET ' . $offset : ''); $aSpam = $DB->rows($sQuery, $aFilter); if ($DB->error || !$aSpam) { return array(); } require_once $_SERVER['DOCUMENT_ROOT'] . '/classes/messages.php'; // прикрепленные файлы messages::getMessagesAttaches($aSpam, 'msg_id'); $sQuery = 'SELECT messages_spam_get_count(?a)'; $count = $DB->val($sQuery, $aFilter); return $aSpam; }
function GetTags(&$count, $limit, $offset = 0) { $DB = new DB('master'); $sql = "SELECT COUNT(*) FROM (SELECT DISTINCT tag_id FROM corp_tags) as t"; $count = $DB->val($sql); $sql = "SELECT t.name, COUNT(*) as count, t.id \n FROM corp_tags c \n INNER JOIN tags t ON c.tag_id = t.id \n INNER JOIN corporative_blog b ON b.id = c.corp_id AND b.date_deleted IS NULL\n GROUP BY t.name, t.id ORDER BY count DESC, name "; /*$sql = "SELECT tags.name, COUNT(*) as count, tags.id FROM corp_tags LEFT JOIN tags ON corp_tags.tag_id=tags.id GROUP BY tags.name, tags.id ORDER BY count DESC, name "; //LIMIT $limit OFFSET $offset"; */ return $DB->rows($sql); }
<?php $rpath = "../../"; require_once $_SERVER['DOCUMENT_ROOT'] . "/classes/stdf.php"; $syear = $_GET['y']; $smonth = $_GET['m']; $DB = new DB('master'); $data = array(); $days = date('t', mktime(0, 0, 0, $smonth, 1, $syear)); for ($n = 1; $n <= $days; $n++) { $data[$n] = 0; } $sql = "SELECT l_emp, extract(day from date) as day FROM stat_data WHERE date>=? AND date<=?"; $q = $DB->rows($sql, $syear . "-" . $smonth . "-01", $syear . "-" . $smonth . "-" . $days); $is_null = 1; if ($q) { foreach ($q as $s) { if ($s['l_emp'] != 0) { $is_null = 0; } $data[$s['day']] = $s['l_emp']; } } // Standard inclusions include "pChart/pData.class"; include "pChart/pChart.class"; // Dataset definition $DataSet = new pData(); $DataSet->AddPoint($data, "S1"); $DataSet->AddPoint(array_keys($data), "S2"); $DataSet->AddSerie("S1");
private function run($sql, $data) { try { $sth = $this->db->prepare($sql); foreach ($data as $key => &$value) { $sth->bindValue($key, $value); } // Exec prepareing $sth->execute(); $result = $sth->fetchAll(PDO::FETCH_ASSOC); self::$rows = count($result); return $result; } catch (exception $e) { // Keep error into log file $log_id = $this->set_log($e); $msg = array('error' => $e->getMessage(), 'id' => $log_id); return $msg; } }
for ($i = 2006; $i <= date('Y'); ++$i) { for ($j = 1; $j <= 12; ++$j) { $aData[$i][$j]['data'] = 0; $aData[$i][$j]['date_m'] = 0; $aData[$i][$j]['date_y'] = 0; } } $DB = new DB('master'); for ($i = 2006, $Y = date('Y'); $i <= $Y; ++$i) { $date_from = $i . '-01-01'; $date_to = $i + 1 . '-01-01'; $sql = "SELECT SUM(trs_sum) as ammount, to_char(op_date,'MM') as _day FROM\n\t\t\taccount_operations WHERE op_date >= ? AND op_date < ? GROUP BY to_char(op_date,'MM') ORDER BY to_char(op_date,'MM')"; if ($i < $Y) { $aTemp = $DB->cache(0)->rows($sql, $date_from, $date_to); } else { $aTemp = $DB->rows($sql, $date_from, $date_to); } $aTemp = $DB->rows($sql, $date_from, $date_to); for ($j = 0; $j < count($aTemp); ++$j) { $iMonth = intval($aTemp[$j]['_day']); $aData[$i][$iMonth]['data'] = true; $aData[$i][$iMonth]['date_m'] = $aTemp[$j]['_day']; $aData[$i][$iMonth]['date_y'] = $i; } } ?> <script type="text/javascript"> var cur_y = '<?php echo date('Y'); ?>