function ActivateScheduledVideos() { $DB = GetDB(); $args = ParseCommandLine(); $queries = array(); if (!isset($args['sort']) || empty($args['sort'])) { $args['sort'] = 'RAND()'; } if (!isset($args['sort-direction']) || empty($args['sort-direction'])) { $args['sort-direction'] = SQL::SORT_ASC; } if (isset($args['amount'])) { $sb = new SQL_SelectBuilder('tbx_video'); $sb->AddSelectField('`video_id`'); $sb->AddSelectField('`tags`'); $sb->AddWhere('tbx_video.status', SQL::EQUALS, STATUS_SCHEDULED); $sb->AddOrder($args['sort'], $args['sort-direction']); $sb->SetLimit($args['amount']); $queries[] = $DB->Prepare($sb->Generate(), $sb->Binds()); } else { if (isset($args['amount-per-sponsor'])) { $result = $DB->Query('SELECT `sponsor_id` FROM `tbx_sponsor`'); while ($sponsor = $DB->NextRow($result)) { $sb = new SQL_SelectBuilder('tbx_video'); $sb->AddSelectField('`video_id`'); $sb->AddSelectField('`tags`'); $sb->AddWhere('tbx_sponsor.sponsor_id', SQL::EQUALS, $sponsor['sponsor_id']); $sb->AddWhere('tbx_video.status', SQL::EQUALS, STATUS_SCHEDULED); $sb->AddOrder($args['sort'], $args['sort-direction']); $sb->SetLimit($args['amount-per-sponsor']); $queries[] = $DB->Prepare($sb->Generate(), $sb->Binds()); } $DB->Free($result); } else { if (isset($args['amount-per-category'])) { $result = $DB->Query('SELECT `category_id` FROM `tbx_category`'); while ($category = $DB->NextRow($result)) { $sb = new SQL_SelectBuilder('tbx_video'); $sb->AddSelectField('`video_id`'); $sb->AddSelectField('`tags`'); $sb->AddWhere('tbx_video.category_id', SQL::EQUALS, $category['category_id']); $sb->AddWhere('tbx_video.status', SQL::EQUALS, STATUS_SCHEDULED); $sb->AddOrder($args['sort'], $args['sort-direction']); $sb->SetLimit($args['amount-per-category']); $queries[] = $DB->Prepare($sb->Generate(), $sb->Binds()); } $DB->Free($result); } else { throw new BaseException('One of --amount, --amount-per-sponsor or --amount-per-category must be specified'); } } } foreach ($queries as $query) { $result = $DB->Query($query); while ($video = $DB->NextRow($result)) { $DB->Update('UPDATE `tbx_video` SET `status`=?,`date_added`=? WHERE `video_id`=?', array(STATUS_ACTIVE, Database_MySQL::Now(), $video['video_id'])); Tags::AddToFrequency($video['tags']); } $DB->Free($result); } UpdateCategoryStats(); }
function tbxGenericSearch() { $DB = GetDB(); $schema = GetDBSchema(); $_REQUEST['per_page'] = isset($_REQUEST['per_page']) && $_REQUEST['per_page'] > 0 ? $_REQUEST['per_page'] : 20; $_REQUEST['page'] = isset($_REQUEST['page']) && $_REQUEST['page'] > 0 ? $_REQUEST['page'] : 1; // Sanity checking $table = Request::GetSafe('table'); $xtable = $schema->el('//table[name="' . $table . '"]'); if (empty($xtable)) { throw new BaseException('The supplied database table does not exist', $table); } // Get custom and merge tables $custom_table = $xtable->custom->val(); $merge_tables = empty($custom_table) ? array() : array($custom_table); foreach ($xtable->xpath('./merge') as $xmerge) { $merge_tables[] = $xmerge->val(); } // Start building the SQL query $s = new SQL_SelectBuilder($table); // Fulltext searches if (isset($_REQUEST['text_search']) && !String::IsEmpty($_REQUEST['text_search'])) { $columns = array(); foreach ($xtable->xpath('.//fulltext[1]/column') as $xcolumn) { $columns[] = $table . '.' . $xcolumn->val(); } $s->AddFulltextWhere($columns, $_REQUEST['text_search_type'], $_REQUEST['text_search']); if ($_REQUEST['text_search_type'] == SQL::FULLTEXT) { $_REQUEST['sort_field'] = array(); } } // Standard search fields for ($i = 0; $i < count($_REQUEST['search_field']); $i++) { $s->AddWhere($_REQUEST['search_field'][$i], $_REQUEST['search_operator'][$i], $_REQUEST['search_term'][$i], $_REQUEST['search_connector'][$i], true); } // Sort fields for ($i = 0; $i < count($_REQUEST['sort_field']); $i++) { $s->AddOrder($_REQUEST['sort_field'][$i], $_REQUEST['sort_direction'][$i]); } $primary_key = $xtable->columns->primaryKey->val(); $result = $DB->QueryWithPagination($s->Generate(), $s->Binds(), $_REQUEST['page'], $_REQUEST['per_page'], $primary_key); if ($result['handle']) { $global_item_include_file = File::Sanitize('cp-' . $xtable->naming->type . '-search-item-global.php', 'php'); $item_include_file = File::Sanitize('cp-' . $xtable->naming->type . '-search-item.php', 'php'); if (!is_file("includes/{$item_include_file}")) { throw new BaseException('The required include file could not be found', $item_include_file); } ob_start(); if (is_file("includes/{$global_item_include_file}")) { include $global_item_include_file; } while ($original = $DB->NextRow($result['handle'])) { foreach ($merge_tables as $merge_table) { $row = $DB->Row('SELECT * FROM # WHERE #=?', array($merge_table, $primary_key, $original[$primary_key])); if (is_array($row)) { $original = array_merge($row, $original); } } $item = String::HtmlSpecialChars($original); include $item_include_file; } $result['html'] = ob_get_clean(); $DB->Free($result['handle']); unset($result['handle']); } JSON::Success($result); }
private function Query($query) { $DB = GetDB(); $xnaming = $this->xtable->naming; $table = $this->xtable->name->val(); $primary_key = $this->xtable->el('.//primaryKey')->val(); $search = $this->search_form; $s = new SQL_SelectBuilder($table); switch ($this->type) { case self::$TYPE_MATCHING: // Fulltext searches if (isset($search['text_search']) && !String::IsEmpty($search['text_search'])) { $columns = array(); foreach ($this->xtable->xpath('.//fulltext/column') as $xcolumn) { $columns[] = $table . '.' . $xcolumn->name; } $s->AddFulltextWhere($columns, $search['text_search_type'], $search['text_search']); } for ($i = 0; $i < count($search['search_field']); $i++) { $s->AddWhere($search['search_field'][$i], $search['search_operator'][$i], $search['search_term'][$i], $search['search_connector'][$i], true); } break; case self::$TYPE_SELECTED: $s->AddWhere("{$table}.{$primary_key}", SQL::IN, $search['search_term']); break; } switch ($query) { case 'amount': return $DB->QueryCount($s->Generate(), $s->Binds(), $primary_key); case 'handle': return $DB->Query($s->Generate(), $s->Binds()); case 'ids': $ids = array(); $result = $DB->Query($s->Generate(), $s->Binds()); while ($row = $DB->NextRow($result)) { $ids[] = $row[$primary_key]; } $DB->Free($result); return $ids; } }