function convert_tables($db_config, $rules_config, $table_name)
{
// connect db
$mysql = new MeekroDB($db_config['host'], $db_config['user'], $db_config['pass'], $db_config['db'], $db_config['port'], $db_config['enc']);
// convert list
$table_list = array();
if ($table_name != '') {
$table_list = array($table_name);
} else {
$table_list = $mysql->tableList();
}
foreach ($table_list as $t) {
if ($rules_config[$t] == NULL) {
continue;
// no convert rule
}
// load convert config
$rule_list = $rules_config[$t];
// load old data
$rows = $mysql->query("SELECT * FROM %b", $t);
foreach ($rows as $r) {
// for each convert config
foreach ($rule_list as $rule) {
$tablename = $rule['table'];
$convertor = $rule['func'];
// convert data
$newrow = $convertor($mysql, $r);
if (empty($newrow)) {
continue;
// convert error
}
$values = '';
$keys = array_keys($newrow);
foreach ($keys as $k) {
$values .= ' ' . $k . "='" . mysql_escape_string($newrow[$k]) . "',";
}
if (strlen($values) > 0) {
$values = substr($values, 0, -1);
}
// generate insert sql
$sql = 'INSERT INTO ' . $tablename . ' SET' . $values . ';';
echo $sql . "\n";
}
}
}
}
$pageCount = 0;
} else {
$pageCount = (int) ($countValue / $number);
if ($countValue % $number != 0) {
$pageCount = $pageCount + 1;
}
$totalNumber = intval($countValue);
}
//echo $count."<br />";
if (isset($_REQUEST["page"]) && $_REQUEST["page"] > $pageCount) {
echo json_encode(array("result" => null, "status" => false, "error" => "请求页数超过总页数"));
exit;
}
if (isset($_REQUEST["sort"]) && $_REQUEST["sort"] == 1) {
$queryParamater = $queryParamater . " ORDER BY A.header, A.words";
} else {
$queryParamater = $queryParamater . " ORDER BY A.words, A.header, A.count DESC";
}
if (isset($_REQUEST["page"]) && $_REQUEST["page"] != 0) {
$pageNum = intval($_REQUEST["page"]);
$queryParamater = $queryParamater . " LIMIT " . $pageNum * $number . "," . $number;
} else {
$pageNum = 0;
$queryParamater = $queryParamater . " LIMIT 0," . $number;
}
$queryString = $queryString . $queryParamater;
//echo $queryString."<br />";
$results = $mdb->query($queryString, $artistid, $artistid, $handle);
$songs = formatSongsResult($results);
$finalResult = array("songs" => $songs, "page" => $pageNum, "total" => $pageCount, "totalNumber" => $totalNumber);
formatResult($finalResult);
// $queryParamater = $queryParamater." ORDER BY A.count DESC, A.order";
}
// if (isset($_REQUEST["page"]) && $_REQUEST["page"] != 0){
// $pageNum = intval($_REQUEST["page"]);
// $queryParamater = $queryParamater." LIMIT ".($pageNum * $number).",".$number;
// } else {
// $pageNum = 0;
// $queryParamater = $queryParamater." LIMIT 0,".$number;
// }
$queryString = $queryString . $queryParamater;
// echo $queryString."<br />";
$pageNum = @intval($_REQUEST["page"]);
if ($use_cache && ($pageNum + 1) * $number <= 500) {
$redis_key .= isset($_REQUEST['type']) ? '#' . $_REQUEST["type"] : '';
if (!$redis->is_exist($redis_key)) {
$result = $mdb->query($queryString . " LIMIT 0,500");
$redis->set($redis_key, $result, 1800);
}
$pageNum = @intval($_REQUEST["page"]);
$result = $redis->get($redis_key, $pageNum * $number, ($pageNum + 1) * $number - 1);
} else {
if (isset($_REQUEST["page"]) && $_REQUEST["page"] != 0) {
$pageNum = intval($_REQUEST["page"]);
$pageParamater = " LIMIT " . $pageNum * $number . "," . $number;
} else {
$pageNum = 0;
$pageParamater = " LIMIT 0," . $number;
}
$queryString = $queryString . $pageParamater;
$result = $mdb->query($queryString);
}
$mdb = new MeekroDB();
$mdb->insert('cars', $_POST);
});
$_PUT = $app->request->params();
// PUT route
$app->put('/cars/:id', function ($id) use($_PUT) {
$columns = array('id', 'brand', 'model', 'year', 'cost');
// Get a list of cars
$mdb = new MeekroDB();
$params = array();
foreach ($columns as $column) {
if (isset($_PUT[$column])) {
$params[] = "`" . $column . "` = '" . $_PUT[$column] . "'";
}
}
$query = "UPDATE cars SET " . implode(',', $params) . " WHERE id=" . $id;
echo json_encode($mdb->query($query));
});
// DELETE route
$app->delete('/cars/:id', function ($id) {
$query = 'DELETE FROM cars WHERE id=' . $id;
$mdb = new MeekroDB();
echo json_encode($mdb->query($query));
});
/**
* Step 4: Run the Slim application
*
* This method should be called last. This executes the Slim application
* and returns the HTTP response to the HTTP client.
*/
$app->run();
<?php
define('ROOT_PATH', getcwd() . '/');
define('SHARED_PATH', ROOT_PATH . '../Shared/');
define('VENDOR_PATH', SHARED_PATH . './vendor/');
require SHARED_PATH . 'config.inc.php';
require VENDOR_PATH . 'autoload.php';
require VENDOR_PATH . 'sergeytsalkov/meekrodb/db.class.php';
use Psr\Http\Message\ServerRequestInterface as Request;
use Psr\Http\Message\ResponseInterface as Response;
use Slim\Http\Stream;
$endpointDatabase = new MeekroDB(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME, DB_PORT, DB_CHARSET);
$endpointDatabase->param_char = "##";
$endpointConfiguration = $endpointDatabase->query("SELECT * FROM EndpointConfiguration");
$endpointEntities = $endpointDatabase->query("SELECT Id, Name, TableName, SelectFields, DATE_FORMAT(DeltaStartDateTimeUtc, '%Y-%m-%dT%TZ') AS DeltaStartDateTimeUtc, JsonFields FROM EndpointEntity");
$app = new \Slim\App(['settings' => ['displayErrorDetails' => true]]);
// Register Endpoint Metadata
$app->get('/Endpoint', function (Request $request, Response $response, $args) use($endpointConfiguration, $endpointEntities, $endpointDatabase) {
foreach ($endpointEntities as $id => $entity) {
$row = $endpointDatabase->queryFirstRow("SELECT DATE_FORMAT(MAX(LastChangeDateTimeUtc), '%Y-%m-%dT%TZ') AS LastChangeDateTimeUtc, COUNT(*) AS Count FROM " . $entity["TableName"]);
$endpointEntities[$id]["LastChangeDateTimeUtc"] = $row["LastChangeDateTimeUtc"];
$endpointEntities[$id]["Count"] = $row["Count"];
}
return $response->withJson(array("CurrentDateTimeUtc" => gmdate("Y-m-d\\TH:i:s\\Z"), "Configuration" => $endpointConfiguration, "Entities" => $endpointEntities));
});
// Register all Endpoints for Table Enumeration & Indexer
foreach ($endpointEntities as $id => $entity) {
$app->get('/' . $entity["Name"], function (Request $request, Response $response) use($endpointDatabase, $entity) {
$fields = preg_replace("(date:([^\$^,]+))", "DATE_FORMAT(\\1,'%Y-%m-%dT%TZ') AS \\1", $entity["SelectFields"]);
$jsonFields = explode(",", $entity["JsonFields"]);
$queryBase = "SELECT " . $fields . " FROM " . $entity["TableName"] . " tbl ";
function archive_lmt_db($uname, $passw, $yrfrom, $yrto)
{
$yrfrom = intval($yrfrom);
$yrto = intval($yrto);
//Reconnect with the new username/password with more privileges
$adminDB = new MeekroDB(NULL, $uname, $passw);
//create new db
//probably triggers error if already exists
$adminDB->query("CREATE DATABASE `lmt-{$yrfrom}` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci");
$adminDB->useDB('lmt-' . $yrfrom);
//copy db info to new db
//This can be done dynamically using SHOW TABLES but this is easier.
$tables = array('guts', 'individuals', 'map', 'pages', 'schools', 'teams');
foreach ($tables as $table) {
$adminDB->query("CREATE TABLE `lmt-{$yrfrom}`.`{$table}` LIKE `lmt`.`{$table}`");
$adminDB->query("INSERT `lmt-{$yrfrom}`.`{$table}` SELECT * FROM `lmt`.`{$table}`");
}
//truncate necessary fields in LMT db, since it's already archived.
$tables_truncate = array('guts', 'individuals', 'schools', 'teams');
foreach ($tables_truncate as $table) {
$adminDB->query("TRUNCATE TABLE `lmt`.`{$table}`");
}
}
$database->throw_exception_on_error = true;
$database->error_handler = false;
// Full page source of http://wiki.eurofurence.org/doku.php?id=ef22:it:mobileapp:coninfo
$wikiText = file_get_contents($fileName);
$regexParseContent = "/<WRAP[^>]*>PARSE_START<\\/WRAP>(.*)<WRAP[^>]*>PARSE_END<\\/WRAP>/si";
$regexGroup = "/====([^=]+)====(.+?)((?=====)|\$)/siu";
$regexEntry = "/===([^=]+)===.+?<WRAP box[^>]*>(.+?)<\\/WRAP>([^\\<]*<WRAP lo[^>]*>([^\\<]+)<\\/WRAP>){0,1}/si";
$regexLinks = "/ \\* \\[\\[([^\\|]+)\\|([^\\]]+)\\]\\]/si";
preg_match($regexParseContent, $wikiText, $matches);
$wikiTextToParse = trim($matches[1]);
preg_match_all($regexGroup, $wikiTextToParse, $groupMatches);
$position = 0;
$groupIds = array();
try {
$database->startTransaction();
$database->query("DELETE FROM Info");
$database->query("DELETE FROM InfoGroup");
$database->query("UPDATE EndpointEntity SET DeltaStartDateTimeUtc = utc_timestamp() WHERE TableName = 'Info';");
$database->query("UPDATE EndpointEntity SET DeltaStartDateTimeUtc = utc_timestamp() WHERE TableName = 'InfoGroup';");
foreach ($groupMatches[1] as $id => $group) {
$groupId = GUID();
$parts = explode("|", trim($group), 2);
Log::info(sprintf("Importing Group %s", trim($parts[0])));
$database->insert("InfoGroup", array("Id" => $groupId, "LastChangeDateTimeUtc" => $database->sqleval("utc_timestamp()"), "IsDeleted" => "0", "Name" => trim($parts[0]), "Description" => trim($parts[1]), "Position" => $position));
$position++;
preg_match_all($regexEntry, $groupMatches[2][$id], $entryMatches);
$epos = 0;
foreach ($entryMatches[1] as $entryId => $entry) {
Log::info(sprintf(" Importing Entry %s", trim($entry)));
$links = [];
$images = [];
$mdb = new MeekroDB(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_TABLE_NAME, DB_PORT, DB_CHARSET);
$queryString = "SELECT B.mid, B.serial_id, B.name, B.singer, B.path, B.original_track, B.sound_track, B.start_volume_1, B.start_volume_2, B.lyric, B.prelude, B.match, C.name AS effect, D.name AS version \r\nFROM media_recommand A \r\nLEFT JOIN media B ON A.rmid = B.mid \r\nLEFT JOIN media_effect C ON B.effect = C.id \r\nLEFT JOIN media_version D ON B.version=D.id";
$queryParamater = " WHERE B.enabled=1";
if (!isset($_REQUEST["mid"])) {
echo json_encode(array("result" => null, "status" => false, "error" => "需提交歌曲编号(mid)"));
exit;
}
$queryParamater = $queryParamater . " AND A.mid=%d";
//Check the black list option is open or not
$rb = $mdb->queryFirstRow("SELECT value FROM config_resource WHERE name = 'filter_black'");
if ($rb["value"] == 1) {
$queryParamater = $queryParamater . " AND B.black = 0";
}
$queryString = $queryString . $queryParamater;
// echo $queryString."<br />";
$results = $mdb->query($queryString, $_REQUEST["mid"]);
$songs = formatSongsResult($results);
//When ther is no recommend, add songs from this singer.
if (count($songs) < 5) {
$add_result = $mdb->query("SELECT A.mid, A.serial_id, A.name, A.singer, A.path, A.original_track, A.sound_track, A.start_volume_1, A.start_volume_2, A.lyric, A.prelude,A.match, B.name AS effect, C.name AS version \r\n FROM media A \r\n LEFT JOIN media_effect B ON A.effect = B.id \r\n LEFT JOIN media_version C ON A.version=C.id\r\n LEFT JOIN media D ON A.mid <> D.mid AND A.artist_sid_1=D.artist_sid_1 OR A.artist_sid_1=D.artist_sid_2\r\n WHERE D.mid=%d LIMIT 0,10", $_REQUEST["mid"]);
foreach ($add_result as $row) {
$song = formatSongResult($row);
array_push($songs, $song);
if (count($songs) == 5) {
break;
}
}
}
//when the singer get no enough songs, add random songs in the database
if (count($songs) < 5) {
$add_result = $mdb->query("SELECT A.mid, A.serial_id, A.name, A.singer, A.path, A.original_track, A.sound_track, A.start_volume_1, A.start_volume_2, A.lyric, A.prelude,A.match, B.name AS effect, C.name AS version \r\n FROM media A \r\n LEFT JOIN media_effect B ON A.effect = B.id \r\n LEFT JOIN media_version C ON A.version=C.id \r\n WHERE A.enabled=1 AND RAND()<=0.00009 limit 0,10");
if ($countValue == 0) {
$pageCount = 0;
} else {
$pageCount = (int) ($countValue / $number);
if ($countValue % $number != 0) {
$pageCount = $pageCount + 1;
}
$totalNumber = intval($countValue);
}
//echo $count."<br />";
if (isset($_REQUEST["page"]) && $_REQUEST["page"] > $pageCount) {
echo json_encode(array("result" => null, "status" => false, "error" => "请求页数超过总页数"));
exit;
}
if (isset($_REQUEST["page"]) && $_REQUEST["page"] != 0) {
$pageNum = intval($_REQUEST["page"]);
$queryParamater = $queryParamater . " LIMIT " . $pageNum * $number . "," . $number;
} else {
$pageNum = 0;
$queryParamater = $queryParamater . " LIMIT 0," . $number;
}
$queryString = $queryString . $queryParamater;
//echo $queryString."<br />";
$results = $mdb->query($queryString, $type);
$lists = array();
foreach ($results as $row) {
$list = array("lid" => $row['lid'], "title" => $row['title'], "image" => $row['image'], "type" => $row['type'], "count" => $row['count'], "special" => $row['special']);
array_push($lists, $list);
}
$finalResult = array("songlists" => $lists, "page" => $pageNum, "total" => $pageCount, "totalNumber" => $totalNumber);
formatResult($finalResult);
<?php
if (!defined("CHECK_ON")) {
exit;
}
require_once '../tools/db.php';
require_once '../tools/main.php';
header("Content-type: text/html; charset=utf-8");
$mdb = new MeekroDB(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_TABLE_NAME, DB_PORT, DB_CHARSET);
$ret_str = "";
$check_list = array();
if ($check_list) {
foreach ($check_list as $key => $check_table) {
$ret = $mdb->query("SHOW COLUMNS FROM {$check_table['table_name']}");
if ($ret) {
$field_arr = array();
foreach ($ret as $key => $value) {
array_push($field_arr, $value['Field']);
}
foreach ($check_table['check_field'] as $key => $value) {
if (!in_array($value, $field_arr)) {
$ret_str .= "数据表{$check_table['table_name']}缺少字段：{$value}" . PHP_EOL;
}
}
}
}
}
return $ret_str;
$queries[0] = "\nCREATE TABLE IF NOT EXISTS `activated_plugins` (\n `plugin_xml_path` varchar(255) NOT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n";
$queries[1] = "\nCREATE TABLE IF NOT EXISTS `articles` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `title` text NOT NULL,\n `content` mediumtext NOT NULL,\n `tags` text NOT NULL,\n `authorID` int(11) NOT NULL,\n `date` date NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;\n";
$queries[2] = "\nCREATE TABLE IF NOT EXISTS `comments` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `content` text NOT NULL,\n `articleID` int(10) unsigned NOT NULL,\n `authorID` int(10) unsigned NOT NULL,\n `timestamp` int(11) NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;\n";
$queries[3] = "\nCREATE TABLE IF NOT EXISTS `notifications` (\n `id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n `title` text NOT NULL,\n `text` text NOT NULL,\n `image` text,\n `time` int(11) NOT NULL,\n `userID` int(11) unsigned NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=65 ;\n";
$queries[4] = "\nCREATE TABLE IF NOT EXISTS `pages` (\n `id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n `title` mediumtext NOT NULL,\n `content` text NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;\n";
$queries[5] = "\nCREATE TABLE IF NOT EXISTS `plugin_data` (\n `access_key` text NOT NULL,\n `setting` text NOT NULL,\n `setting_type` varchar(255) NOT NULL,\n `setting_value` text NOT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n";
$queries[6] = "\nCREATE TABLE IF NOT EXISTS `sessions` (\n `userID` int(11) NOT NULL,\n `sessionID` text NOT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n";
$queries[7] = "\nCREATE TABLE IF NOT EXISTS `settings` (\n `setting` tinytext NOT NULL,\n `value` tinytext NOT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n";
$queries[8] = "\nCREATE TABLE IF NOT EXISTS `storage` (\n `key` text NOT NULL,\n `var` text NOT NULL,\n `is_object` varchar(255) NOT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n";
$queries[9] = "\nCREATE TABLE IF NOT EXISTS `style_data` (\n `style_main_class` text NOT NULL,\n `setting` text NOT NULL,\n `setting_type` varchar(255) NOT NULL,\n `setting_value` text NOT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n";
$queries[10] = "\nCREATE TABLE IF NOT EXISTS `users` (\n `id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n `name` varchar(50) NOT NULL,\n `password` text NOT NULL,\n `email` varchar(80) NOT NULL,\n `rank` tinyint(1) NOT NULL,\n `activated` tinyint(1) NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;\n";
$queries[11] = "\nCREATE TABLE IF NOT EXISTS `user_activations` (\n `userID` int(11) NOT NULL,\n `code` text NOT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n";
$creds = $_SESSION['mysql_creds'];
$db = new MeekroDB($creds['host'], $creds['username'], $creds['password'], $creds['database']);
foreach ($queries as $query) {
$db->query($query);
}
unset($_SESSION['mysql_creds']);
$db->insert("settings", array("setting" => "website_url", "value" => $_POST['website_url']));
$db->insert("settings", array("setting" => "website_name", "value" => $_POST['website_name']));
$db->insert("settings", array("setting" => "website_style", "value" => "monk"));
$db->insert("settings", array("setting" => "website_email", "value" => $_POST['admin_email']));
$db->insert("settings", array("setting" => "website_lang", "value" => "en_US"));
$db->insert("settings", array("setting" => "show_archive", "value" => "true"));
$conf = new ConfigFile();
$config = array();
$config['db'] = array();
$config['security'] = array();
$config['db']['name'] = $creds['database'];
$config['db']['username'] = $creds['username'];
$config['db']['password'] = $creds['password'];
Log::info(" Touching LastChangeDateTimeUtc of Id=" . $result["Id"]);
} else {
Log::info(" No changes on Id=" . $result["Id"]);
}
if (!$isModified) {
return null;
}
return $result;
}
$news = json_decode(file_get_contents('http://6al.de/efsched/getconnews'));
Log::info("Importing ConNews");
try {
$database->startTransaction();
foreach ($news as $entry) {
Log::info(sprintf("Id: %s, Type: %s -> %s", $entry->id, $entry->news->type, $entry->news->title));
$dbItem = @$database->query("SELECT * FROM Announcement WHERE ExternalId=%s", "connews:" . $entry->id)[0];
if ($dbItem) {
$dbItem["ValidFromDateTimeUtc"] = new DateTime($dbItem["ValidFromDateTimeUtc"]);
$dbItem["ValidUntilDateTimeUtc"] = new DateTime($dbItem["ValidUntilDateTimeUtc"]);
}
if ($entry->news->type == "new" || $entry->news->type == "reschedule") {
$entry->news->valid_until = $entry->date + 60 * 60 * 48;
}
$sourceItem = array("ExternalId" => "connews:" . $entry->id, "ValidFrom" => DateTime::createFromFormat('U', $entry->date), "ValidUntil" => DateTime::createFromFormat('U', $entry->news->valid_until), "Area" => ucwords($entry->news->type), "Author" => isset($entry->news->department) ? ucwords($entry->news->department) : "Eurofurence", "Title" => $entry->news->title, "Content" => strip_tags($parsedown->text($entry->news->message)));
$patchedItem = patch($sourceItem, $dbItem, array("ExternalId" => "ExternalId", "ValidFrom" => "ValidFromDateTimeUtc", "ValidUntil" => "ValidUntilDateTimeUtc", "Area" => "Area", "Author" => "Author", "Title" => "Title", "Content" => "Content"));
if ($patchedItem) {
if (!$dbItem) {
$database->insert("Announcement", $patchedItem);
} else {
$database->update("Announcement", $patchedItem, "Id=%s", $dbItem["Id"]);
}
Log::info(" Touching LastChangeDateTimeUtc of Id=" . $result["Id"]);
} else {
//Log::info( " No changes on Id=".$result["Id"]);
}
if (!$isModified) {
return null;
}
return $result;
}
try {
$database->startTransaction();
/* Import Conference Tracks */
Log::info("Importing Conference Tracks");
$importConferenceTracks = Enumerable::from($eventQuery->select('$v["conference_track"]')->distinct()->toList());
$importConferenceRooms = Enumerable::from($eventQuery->select('$v["conference_room"]')->distinct()->toList());
$dbConferenceTracks = Enumerable::from($database->query("SELECT * FROM EventConferenceTrack"));
$importConferenceTracks->each(function ($iItem) use($dbConferenceTracks, $database) {
$dbItem = $dbConferenceTracks->where(function ($a) use($iItem) {
return $a["Name"] == $iItem;
})->singleOrDefault();
$patchedItem = patch(array("Name" => $iItem), $dbItem, array("Name" => "Name"));
if ($patchedItem) {
if (!$dbItem) {
$database->insert("EventConferenceTrack", $patchedItem);
} else {
$database->update("EventConferenceTrack", $patchedItem, "Id=%s", $dbItem["Id"]);
}
}
});
$dbConferenceTracks->where(function ($a) use($importConferenceTracks) {
return $a["IsDeleted"] == 0 && $importConferenceTracks->where(function ($b) use($a) {
}
$countResult = $mdb->queryFirstRow("SELECT COUNT(*) AS count FROM songlist_detail WHERE lid=%d", intval($_REQUEST["lid"]));
$totalNumber = 0;
$countValue = $countResult['count'];
if ($countValue == 0) {
$pageCount = 0;
} else {
$pageCount = (int) ($countValue / $number);
if ($countValue % $number != 0) {
$pageCount = $pageCount + 1;
}
$totalNumber = intval($countValue);
}
//echo $count."<br />";
if (isset($_REQUEST["page"]) && $_REQUEST["page"] > $pageCount) {
echo json_encode(array("result" => null, "status" => false, "error" => "请求页数超过总页数"));
exit;
}
if (isset($_REQUEST["page"]) && $_REQUEST["page"] != 0) {
$pageNum = intval($_REQUEST["page"]);
$queryParamater = $queryParamater . " LIMIT " . $pageNum * $number . "," . $number;
} else {
$pageNum = 0;
$queryParamater = $queryParamater . " LIMIT 0," . $number;
}
$queryString = $queryString . $queryParamater;
//echo $queryString."<br />";
$results = $mdb->query($queryString, intval($_REQUEST["lid"]));
$songs = formatSongsResult($results);
$finalResult = array("songs" => $songs, "page" => $pageNum, "total" => $pageCount, "totalNumber" => $totalNumber);
formatResult($finalResult);
$database->error_handler = false;
$zipArchiveLocation = $fileName;
$zipContentsQueryable = getZipContentsAsQueryable($zipArchiveLocation);
$csvEntry = $zipContentsQueryable->where(function ($v) {
return endsWith($v["name"], "csv");
})->single();
$csvData = getZipContentOfFile($zipArchiveLocation, $csvEntry['name']);
while (ord($csvData[0]) > 127) {
$csvData = substr($csvData, 1);
}
$csvParser = new parseCSV();
$csvParser->delimiter = ";";
$csvContentsQueryable = from($csvParser->parse_string(utf8_decode($csvData)));
try {
$database->startTransaction();
$existingRecordsQueryable = from($database->query("SELECT * FROM dealer"));
$oldRecords = $existingRecordsQueryable->where('$v["IsDeleted"] == 0')->where(function ($row) use($csvContentsQueryable) {
return $csvContentsQueryable->all('$v["Reg No."] != ' . $row['RegistrationNumber']);
})->toArray();
Log::info(sprintf("Soft deleting old records on '%s'", 'dealer'));
from($oldRecords)->each(function ($row) use($database) {
Log::info(sprintf("Deleting %s", $row['RegistrationNumber']));
$database->update("dealer", array("LastChangeDateTimeUtc" => $database->sqleval("utc_timestamp()"), "IsDeleted" => 1), "Id=%s", $row["Id"]);
});
Log::info(sprintf("Inserting / updating records on '%s'", 'dealer'));
from($csvContentsQueryable)->each(function ($record) use($database, $existingRecordsQueryable, $zipArchiveLocation) {
$row = array("LastChangeDateTimeUtc" => $database->sqleval("utc_timestamp()"), "IsDeleted" => "0", "RegistrationNumber" => $record["Reg No."], "AttendeeNickname" => $record["Nick"], "DisplayName" => $record["Display Name"], "ShortDescription" => $record["Short Description"], "AboutTheArtistText" => $record["About the Artist"], "AboutTheArtText" => $record["About the Art"], "WebsiteUri" => $record["Website Reg"], "ArtPreviewCaption" => $record["Art Preview Caption"]);
$zipContentsQueryable = getZipContentsAsQueryable($zipArchiveLocation);
// Artist Thumbnail
$artistThumbnailImageEntry = from($zipContentsQueryable)->where(function ($v) use($row) {
return strpos($v["name"], sprintf("thumbnail_%s.", $row['RegistrationNumber'])) !== false;
}
if (isset($_REQUEST["sort"]) && $_REQUEST["sort"] == 1) {
$use_cache = false;
$queryParamater = $queryParamater . " ORDER BY header, words";
} else {
//$queryParamater = $queryParamater." ORDER BY words, count DESC, header";
$queryParamater = $queryParamater . " ORDER BY count DESC";
}
if (isset($_REQUEST["page"]) && $_REQUEST["page"] != 0) {
$pageNum = intval($_REQUEST["page"]);
$pageParamater = $queryParamater . " LIMIT " . $pageNum * $number . "," . $number;
} else {
$pageNum = 0;
$pageParamater = $queryParamater . " LIMIT 0," . $number;
}
//$queryString = $queryString.$pageParamater;
// echo $queryString."<br />";
// $pageNum = @intval($_REQUEST["page"]);
if ($use_cache && ($pageNum + 1) * $number <= 500) {
if (!$redis->is_exist($redis_key)) {
$result = $mdb->query($queryString . $queryParamater . " LIMIT 0,500");
$redis->set($redis_key, $result, 1800);
}
$results = $redis->get($redis_key, $pageNum * $number, ($pageNum + 1) * $number - 1);
} else {
$queryString = $queryString . $pageParamater;
$results = $mdb->query($queryString, $handle);
}
$songs = formatMusicsResult($results);
$finalResult = array("songs" => $songs, "page" => $pageNum, "total" => $pageCount, "totalNumber" => $totalNumber);
formatResult($finalResult);
}
} else {
$pageNum = 0;
$pageParamater = " LIMIT 0," . $number;
}
if ($type == "hot") {
//return hot board(dynamic)
$hotQueryString = "SELECT B.mid, B.serial_id, B.name, B.singer, B.path, B.original_track, B.sound_track, B.start_volume_1, B.start_volume_2, B.lyric, B.prelude,B.match, D.name AS effect, C.name AS version, B.artist_sid_1 AS singer_id \r\n FROM media B \r\n LEFT JOIN media_effect D ON B.effect = D.id \r\n LEFT JOIN media_version C ON B.version = C.id\r\n WHERE B.enabled=1";
if ($black) {
$hotQueryString = $hotQueryString . " AND B.black = 0";
}
$hotQueryString .= $hot_filter . " ORDER BY B.count DESC";
if ($use_cache && ($pageNum + 1) * $number <= 200) {
//使用缓存
if (!$redis->is_exist($redis_key)) {
$results = $mdb->query($hotQueryString . " LIMIT 0,200");
$redis->set($redis_key, $results, 1800);
}
$pageNum = @intval($_REQUEST["page"]);
$results = $redis->get($redis_key, $pageNum * $number, ($pageNum + 1) * $number - 1);
} else {
$results = $mdb->query($hotQueryString . $pageParamater, $handle);
}
} else {
if ($use_cache && ($pageNum + 1) * $number <= 500) {
// $key = md5($queryString.$queryParamater.$type);
if (!$redis->is_exist($redis_key)) {
$results = $mdb->query($queryString . $queryParamater . " LIMIT 0,500", $type);
$redis->set($redis_key, $results, 1800);
}
$pageNum = @intval($_REQUEST["page"]);
if (!isset($result['matches'])) {
include_once '_list.php';
exit;
// $finalResult = array(
// "songs" => array(),
// "page" => 0,
// "total" => 0,
// "totalNumber" => 0
// );
// formatResult($finalResult);
// exit;
}
$countValue = $result['total'];
if ($countValue == 0) {
$pageCount = 0;
} else {
$pageCount = (int) ($countValue / $number);
if ($countValue % $number != 0) {
$pageCount = $pageCount + 1;
}
$totalNumber = intval($countValue);
}
$mid_list = array();
foreach ($result['matches'] as $match) {
array_push($mid_list, $match['id']);
}
$queryString = "SELECT A.mid, A.serial_id, A.name, A.singer, A.path, A.original_track, A.sound_track, A.start_volume_1, A.start_volume_2, A.lyric, A.prelude, A.match, B.name AS effect, C.name AS version, A.artist_sid_1 AS singer_id \r\nFROM media A \r\nLEFT JOIN media_effect B ON A.effect = B.id \r\nLEFT JOIN media_version C ON A.version=C.id WHERE A.mid IN (" . implode(',', $mid_list) . ") ORDER BY FIELD(mid," . implode(',', $mid_list) . ")";
$results = $mdb->query($queryString);
$songs = formatSongsResult($results);
$finalResult = array("songs" => $songs, "page" => $pageNum, "total" => $pageCount, "totalNumber" => $totalNumber);
formatResult($finalResult);
$rb = $mdb->queryFirstRow("SELECT value FROM config_resource WHERE name = 'filter_black'");
if ($rb["value"] == 1) {
$queryParamater .= $ip ? " AND A.black = 0" : " AND B.black = 0";
}
if (isset($_REQUEST["page"]) && $_REQUEST["page"] != 0) {
$pageNum = intval($_REQUEST["page"]);
$pageParamater = " LIMIT " . $pageNum * $number . "," . $number;
} else {
$pageNum = 0;
$pageParamater = " LIMIT 0," . $number;
}
if ($ip) {
$queryString = "SELECT A.mid, A.serial_id, A.name, A.singer, A.path, A.original_track, A.sound_track, A.start_volume_1, A.start_volume_2, A.lyric, A.prelude, A.match, B.name AS effect, C.name AS version \r\n\tFROM media A \r\n\tLEFT JOIN media_effect B ON A.effect = B.id \r\n\tLEFT JOIN media_version C ON A.version=C.id \r\n\tLEFT JOIN publicsong_list D ON D.mid=A.mid\r\n\tLEFT JOIN publicsong_ip E ON E.type=D.type\r\n\tWHERE A.enabled = 1 AND E.ip=%s";
$sql = "SELECT count(*) as count FROM media A \r\n\tLEFT JOIN publicsong_list B ON A.mid=B.mid\r\n\tLEFT JOIN publicsong_ip C ON C.type=B.type\r\n\tWHERE A.enabled=1 AND C.ip=%s ";
$countResult = $mdb->queryFirstRow($sql, $ip);
$results = $mdb->query($queryString . $queryParamater . $pageParamater, $ip);
}
if (!$ip || !$results) {
$queryString = "SELECT B.mid, B.serial_id, B.name, B.singer, B.path, B.original_track, B.sound_track, B.start_volume_1, B.start_volume_2, B.lyric, B.prelude, B.match, C.name AS effect, D.name AS version \r\n\tFROM media_list A \r\n\tLEFT JOIN media B ON A.mid = B.mid \r\n\tLEFT JOIN media_effect C ON B.effect = C.id \r\n\tLEFT JOIN media_version D ON B.version=D.id \r\n\tWHERE A.type='ad'";
$countResult = $mdb->queryFirstRow("SELECT COUNT(*) as count FROM media_list LEFT JOIN media ON media_list.mid = media.mid WHERE media_list.type = 'ad'" . $queryParamater);
$results = $mdb->query($queryString . $queryParamater . $pageParamater);
}
$totalNumber = 0;
$countValue = $countResult['count'];
if ($countValue == 0) {
$pageCount = 0;
} else {
$pageCount = (int) ($countValue / $number);
if ($countValue % $number != 0) {
$pageCount = $pageCount + 1;
}
