function contests_cache($job, $step)
{
$fw = \Base::instance();
$new = "{$fw['installerCFG.db5.dbname']}`.`{$fw['installerCFG.db5.prefix']}";
$limit = 50;
$tracking = new DB\SQL\Mapper($fw->db5, $fw->get('installerCFG.db5.prefix') . 'convert');
$tracking->load(['id = ?', $step['id']]);
if ($step['success'] == 0) {
$total = $fw->db5->exec("SELECT COUNT(*) as found FROM `{$new}contests`;")[0]['found'];
$fw->db5->exec("UPDATE `{$new}convert`SET `success` = 1, `total` = :total WHERE `id` = :id ", [':total' => $total, ':id' => $step['id']]);
}
$dataIn = $fw->db5->exec("SELECT \r\n\t\t\t\t\t\t\t\tCon.conid,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT S.sid,',',S.title,',',U.uid,',',U.nickname ORDER BY charname ASC SEPARATOR '||') AS storyblock,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT Chara.charid,',',Chara.charname ORDER BY charname ASC SEPARATOR '||') AS characterblock,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT C.cid,',',C.category ORDER BY category ASC SEPARATOR '||' ) as categoryblock,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT T.tid,',',T.label,',',TG.description ORDER BY TG.order,TG.tgid,T.label ASC SEPARATOR '||') AS tagblock\r\n\t\t\t\t\t\t\t\t\tFROM \r\n\t\t\t\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\t\t\t\tSELECT Con1.conid\r\n\t\t\t\t\t\t\t\t\t\t\tFROM `{$new}contests`Con1\r\n\t\t\t\t\t\t\t\t\t\t\tWHERE Con1.cache_tags IS NULL\r\n\t\t\t\t\t\t\t\t\t\t\tLIMIT 0,{$limit}\r\n\t\t\t\t\t\t\t\t\t) AS Con\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}contest_relations`rC ON ( rC.conid = Con.conid )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}stories`S ON ( S.sid = rC.relid and rC.type='ST' )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}stories_authors`rSA ON ( rSA.sid = S.sid )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}users`U ON ( U.uid = rSA.aid )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}tags`T ON ( T.tid = rC.relid AND rC.type = 'T' )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}tag_groups`TG ON ( TG.tgid = T.tgid )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}characters`Chara ON ( Chara.charid = rC.relid AND rC.type = 'CH' )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}categories`C ON ( C.cid = rC.relid AND rC.type = 'CA' )\r\n\t\t\t\t\t\t\t\t\tGROUP BY Con.conid;");
if (0 < ($count = sizeof($dataIn))) {
foreach ($dataIn as $item) {
$fw->db5->exec("UPDATE `{$new}contests` SET \r\n\t\t\t\t\t\t`cache_stories`\t\t= :storyblock,\r\n\t\t\t\t\t\t`cache_tags`\t\t= :tagblock,\r\n\t\t\t\t\t\t`cache_characters`\t= :characterblock,\r\n\t\t\t\t\t\t`cache_categories`\t= :categoryblock\r\n\t\t\t\t\tWHERE conid = {$item['conid']} ;", [':storyblock' => json_encode(upgradetools::cleanResult($item['storyblock'])), ':tagblock' => json_encode(upgradetools::cleanResult($item['tagblock'])), ':characterblock' => json_encode(upgradetools::cleanResult($item['characterblock'])), ':categoryblock' => json_encode(upgradetools::cleanResult($item['categoryblock']))]);
}
$tracking->items = $tracking->items + $count;
$tracking->save();
}
if ($count == 0 or $count < $limit) {
// There was either nothing to be done, or there are no elements left for the next run
$tracking->success = 2;
$tracking->save();
}
}
function series_cache($job, $step)
{
$fw = \Base::instance();
$new = "{$fw['installerCFG.db5.dbname']}`.`{$fw['installerCFG.db5.prefix']}";
$limit = 20;
$tracking = new DB\SQL\Mapper($fw->db5, $fw->get('installerCFG.db5.prefix') . 'convert');
$tracking->load(['id = ?', $step['id']]);
if ($step['success'] == 0) {
$total = $fw->db5->exec("SELECT COUNT(*) as found FROM `{$new}series`;")[0]['found'];
$fw->db5->exec("UPDATE `{$new}convert`SET `success` = 1, `total` = :total WHERE `id` = :id ", [':total' => $total, ':id' => $step['id']]);
}
$dataIn = $fw->db5->exec("SELECT \r\n\t\t\t\t\t\t\t\tSERIES.seriesid, \r\n\t\t\t\t\t\t\t\tSERIES.tagblock, \r\n\t\t\t\t\t\t\t\tSERIES.characterblock, \r\n\t\t\t\t\t\t\t\tSERIES.authorblock, \r\n\t\t\t\t\t\t\t\tSERIES.categoryblock, \r\n\t\t\t\t\t\t\t\tCONCAT(rating,'||',max_rating_id) as max_rating\r\n\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\tSELECT \r\n\t\t\t\t\t\t\tSer.seriesid,\r\n\t\t\t\t\t\t\tMAX(Ra.rid) as max_rating_id,\r\n\t\t\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT U.uid,',',U.nickname ORDER BY nickname ASC SEPARATOR '||' ) as authorblock,\r\n\t\t\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT Chara.charid,',',Chara.charname ORDER BY charname ASC SEPARATOR '||') AS characterblock,\r\n\t\t\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT C.cid,',',C.category ORDER BY category ASC SEPARATOR '||' ) as categoryblock,\r\n\t\t\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT T.tid,',',T.label,',',TG.description ORDER BY TG.order,TG.tgid,T.label ASC SEPARATOR '||') AS tagblock\r\n\t\t\t\t\t\t\t\t\tFROM \r\n\t\t\t\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\t\t\t\tSELECT Ser1.seriesid\r\n\t\t\t\t\t\t\t\t\t\t\tFROM `{$new}series`Ser1\r\n\t\t\t\t\t\t\t\t\t\t\tWHERE Ser1.cache_authors IS NULL\r\n\t\t\t\t\t\t\t\t\t\t\tLIMIT 0,{$limit}\r\n\t\t\t\t\t\t\t\t\t) AS Ser\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}series_stories`TrS ON ( Ser.seriesid = TrS.seriesid )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}stories`S ON ( TrS.sid = S.sid )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}ratings`Ra ON ( Ra.rid = S.ratingid )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}stories_tags`rST ON ( rST.sid = S.sid )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}tags`T ON ( T.tid = rST.tid AND rST.character = 0 )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}tag_groups`TG ON ( TG.tgid = T.tgid )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}characters`Chara ON ( Chara.charid = rST.tid AND rST.character = 1 )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}stories_categories`rSC ON ( rSC.sid = S.sid )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}categories`C ON ( rSC.cid = C.cid )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}stories_authors`rSA ON ( rSA.sid = S.sid )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}users` U ON ( rSA.aid = U.uid )\r\n\t\t\t\t\t\t\t\t\tGROUP BY Ser.seriesid\r\n\t\t\t\t\t\t\t) AS SERIES\r\n\t\t\t\t\t\t\tLEFT JOIN `{$new}ratings`R ON (R.rid = max_rating_id);");
if (0 < ($count = sizeof($dataIn))) {
foreach ($dataIn as $item) {
$fw->db5->exec("UPDATE `{$new}series` SET \r\n\t\t\t\t\t\t`cache_authors`\t\t= :authorblock,\r\n\t\t\t\t\t\t`cache_tags`\t\t= :tagblock,\r\n\t\t\t\t\t\t`cache_characters`\t= :characterblock,\r\n\t\t\t\t\t\t`cache_categories`\t= :categoryblock,\r\n\t\t\t\t\t\t`max_rating`\t\t= :max_rating\r\n\t\t\t\t\tWHERE seriesid = {$item['seriesid']} ;", [':authorblock' => json_encode(upgradetools::cleanResult($item['authorblock'])), ':tagblock' => json_encode(upgradetools::cleanResult($item['tagblock'])), ':characterblock' => json_encode(upgradetools::cleanResult($item['characterblock'])), ':categoryblock' => json_encode(upgradetools::cleanResult($item['categoryblock'])), ':max_rating' => json_encode(explode(",", $item['max_rating']))]);
}
$tracking->items = $tracking->items + $count;
$tracking->save();
}
if ($count == 0 or $count < $limit) {
// There was either nothing to be done, or there are no elements left for the next run
$tracking->success = 2;
$tracking->save();
}
}
public function _log($typeID, $id = array(), $text = "", $data = array())
{
//save($id=array("contentID"=>"","commentID"=>"","meetingID"=>"","companyID"=>"","answerID"=>""),$text="",$userID="") {
$timer = new timer();
$f3 = \base::instance();
$user = $f3->get("user");
$userID = $user['ID'];
//test_array($id);
$values = array('typeID' => $typeID, 'userID' => $userID, 'contentID' => isset($id["contentID"]) ? $id["contentID"] : null, 'commentID' => isset($id["commentID"]) ? $id["commentID"] : null, 'meetingID' => isset($id["meetingID"]) ? $id["meetingID"] : null, 'companyID' => isset($id["companyID"]) ? $id["companyID"] : null, 'optionID' => isset($id["optionID"]) ? $id["optionID"] : null, 'fileID' => isset($id["fileID"]) ? $id["fileID"] : null, 'text' => $text, 'data' => json_encode($data), 'sessionID' => session_id());
//test_array($values);
if ($values['fileID'] != null && $values['contentID'] == null) {
$values['contentID'] = $this->lookup($values['fileID'], 'mp_content_files')->contentID;
}
if ($values['optionID'] != null && $values['contentID'] == null) {
$values['contentID'] = $this->lookup($values['optionID'], 'mp_content_poll_answers')->contentID;
}
if ($values['commentID'] != null && $values['contentID'] == null) {
$values['contentID'] = $this->lookup($values['commentID'], 'mp_content_comments')->contentID;
}
if ($values['contentID'] != null && $values['meetingID'] == null) {
$values['meetingID'] = $this->lookup($values['contentID'], 'mp_content')->meetingID;
}
if ($values['meetingID'] != null && $values['companyID'] == null) {
$values['companyID'] = $this->lookup($values['meetingID'], 'mp_meetings')->companyID;
}
$art = new \DB\SQL\Mapper($this->f3->get("DB"), 'mp_logs');
foreach ($values as $key => $value) {
if (isset($art->{$key}) && $key != "ID") {
$art->{$key} = $value;
}
}
$art->save();
$timer->_stop(__NAMESPACE__, __CLASS__, __FUNCTION__, func_get_args());
return $values;
}
function Set($f3)
{
if (!\Check::confirm('POST.password')) {
$this->flash('Konfirmasi Password Tidak Cocok');
$f3->reroute($f3->get('URI'));
}
$post = $f3->get('POST');
$db_host = $post['DB_HOST'];
$db_name = $post['DB_NAME'];
$db_user = $post['DB_USER'];
$db_pass = $post['DB_PASS'];
$dsn = "mysql:host={$db_host};port=3306;dbname={$db_name}";
$db = new \DB\SQL($dsn, $db_user, $db_pass);
try {
$db->begin();
$db->exec(explode(';', $f3->read('installation/install.sql')));
$user = new \DB\SQL\Mapper($db, 'user');
$user->username = $post['username'];
$user->password = \Bcrypt::instance()->hash($post['password']);
$user->type = 1;
$user->save();
$key = bin2hex(openssl_random_pseudo_bytes(32));
$data = "[globals]\nDEBUG=0\nAUTOLOAD=\"controller/;model/\"\nUI=\"view/\"\nAPP_KEY=\"{$key}\"\nDB_SET=\"{$dsn}\"\nDB_USER=\"{$db_user}\"\nDB_PASS=\"{$db_pass}\"";
$f3->write('config/config.ini', $data);
$f3->write('config/route.ini', $f3->read('installation/route.ini'));
$db->commit();
$this->flash('Success... Silahkan Hapus Folder Installation', 'success');
} catch (Exception $e) {
$db->rollback();
$this->flash($e->getMessage());
$f3->reroute('/');
}
$f3->reroute('/');
}
public function userSession($uid)
{
$session = new \DB\SQL\Mapper($this->db, $this->prefix . "sessions");
$session->load(array('session=?', $_SESSION['session_id']));
$session->user = $uid;
$session->save();
}
public function cacheCategories($catID = 0)
{
// clear stats of affected categories
$sql = "UPDATE `tbl_categories` SET `stats` = NULL";
if ($catID > 0) {
$sql .= " WHERE `cid` = {$catID};";
}
$this->exec($sql);
$categories = new \DB\SQL\Mapper($this->db, $this->prefix . 'categories');
$sql = "SELECT C.cid, C.category, COUNT(DISTINCT S.sid) as counted, \r\n\t\t\t\t\tGROUP_CONCAT(DISTINCT C1.category SEPARATOR '||' ) as sub_categories, \r\n\t\t\t\t\tGROUP_CONCAT(DISTINCT C1.stats SEPARATOR '||' ) as sub_stats\r\n\t\t\tFROM `tbl_categories`C \r\n\t\t\t\tINNER JOIN (SELECT leveldown FROM `tbl_categories` WHERE `stats` = '' ORDER BY leveldown DESC LIMIT 0,1) c2 ON ( C.leveldown = c2.leveldown )\r\n\t\t\t\tLEFT JOIN `tbl_stories_categories`SC ON ( C.cid = SC.cid )\r\n\t\t\t\tLEFT JOIN `tbl_stories`S ON ( S.sid = SC.sid )\r\n\t\t\t\tLEFT JOIN `tbl_categories`C1 ON ( C.cid = C1.parent_cid )\r\n\t\t\tGROUP BY C.cid";
do {
$items = $this->exec($sql);
$change = FALSE;
foreach ($items as $item) {
if ($item['sub_categories'] == NULL) {
$sub = NULL;
} else {
$sub_categories = explode("||", $item['sub_categories']);
$sub_stats = explode("||", $item['sub_stats']);
$sub_stats = array_map("json_decode", $sub_stats);
foreach ($sub_categories as $key => $value) {
$item['counted'] += $sub_stats[$key]->count;
$sub[$value] = $sub_stats[$key]->count;
}
}
$stats = json_encode(["count" => (int) $item['counted'], "cid" => $item['cid'], "sub" => $sub]);
unset($sub);
$categories->load(array('cid=?', $item['cid']));
$categories->stats = $stats;
$categories->save();
$change = $change ?: $categories->changed();
}
} while ($change != FALSE);
}
public function update($table, $data, $where)
{
$handle = new \DB\SQL\Mapper($this->db, str_replace("tbl_", $this->prefix, $table));
$handle->load($where);
foreach ($data as $key => $value) {
$handle->{$key} = $value;
}
$handle->save();
unset($handle);
}
public function save()
{
$v = new DB\SQL\Mapper(Events::$db, 'venues');
if ($this->id) {
$v->load('id=' . $this->id);
}
$v->name = $this->name;
$v->address = $this->address;
$v->postcode = $this->postcode;
$v->info = $this->info;
$v->save();
}
function edit($f3)
{
$video = new DB\SQL\Mapper($f3->get('db'), 'videos');
$video->load(array('id=?', $f3->get('PARAMS.videoID')));
if ($f3->get('POST.delete') == '1') {
$video->erase();
} else {
$video->startTime = $f3->get('POST.startTime');
$video->endTime = $f3->get('POST.endTime');
$video->save();
}
$f3->reroute('@channelList');
}
private function ipIsAlreadyRegistered($ip, $gaddress)
{
$faucet = new DB\SQL\Mapper($this->db, 'faucet');
$faucet->load(array('user_ip=?', $ip));
if ($faucet->user_ip == null) {
$faucet->load(array('gaddress=?', $gaddress));
if ($faucet->gaddress == null) {
$faucet->user_ip = $ip;
$faucet->gaddress = $gaddress;
$faucet->save();
return false;
}
}
return true;
}
function textblocks_copy($job, $step)
{
// Copy the existing blocks
$fw = \Base::instance();
$new = "{$fw['installerCFG.db5.dbname']}`.`{$fw['installerCFG.db5.prefix']}";
$old = "{$fw['installerCFG.db3.dbname']}`.`{$fw['installerCFG.db3.prefix']}fanfiction_";
$i = 0;
$newdata = new \DB\SQL\Mapper($fw->db5, $fw['installerCFG.db5.prefix'] . "textblocks");
$dataIn = $fw->db3->exec("SELECT message_id as id, message_name as label, message_title as title, message_text as content, 1 as as_page FROM `{$old}messages`;");
foreach ($dataIn as $data) {
$i++;
$newdata->copyfrom($data);
$newdata->save();
$newdata->reset();
}
$fw->db5->exec("UPDATE `{$new}convert`SET `success` = 2, `items` = :items WHERE `id` = :id ", [':items' => $i, ':id' => $step['id']]);
}
function order()
{
$return = "";
$domain = $this->f3->get("domain");
//$ID = (isset($_GET['ID'])) ? $_GET['ID'] : "";
$f3 = \Base::instance();
$list = $_POST['id'];
$a = new \DB\SQL\Mapper($f3->get("DB"), "dir_categories");
$sort = array();
foreach ($list as $id => $parentId) {
$a->load("ID='{$id}'");
$a->parentID = $parentId;
if (!$a->dry()) {
$a->save();
}
$a->reset();
}
test_array($sort);
return $GLOBALS["output"]['data'] = $return;
}
static function save($ID, $values)
{
$timer = new timer();
$f3 = \base::instance();
$user = $f3->get("user");
// test_array($values);
$IDorig = $ID;
$changes = array();
$art = new \DB\SQL\Mapper($f3->get("DB"), "dir_pages");
$art->load("ID='{$ID}'");
// test_array(array($art->ID,$ID));
//test_array($this->get("14"));
foreach ($values as $key => $value) {
//$value = $f3->scrub($value,$f3->get("TAGS"));
if (isset($art->{$key})) {
$art->{$key} = $value;
}
}
$art->save();
$ID = $art->ID ? $art->ID : $art->_id;
$timer->_stop(__NAMESPACE__, __CLASS__, __FUNCTION__, func_get_args());
return $ID;
}
public function newsletter()
{
$f3 = \Base::instance();
$db = $f3->get('DB.instance');
// Getting info about request, $org = organisation, $nId = newsletter id.
$org = $f3->get('PARAMS.org');
$nId = (int) $f3->get('PARAMS.nId');
$nmap = new \DB\SQL\Mapper($db, 'newsletters');
$omap = new \DB\SQL\Mapper($db, 'organisations');
if ($omap->load(array('id=? OR cname=?', $org, $org))) {
// Organisation exists
if ($nmap->load(array('id=? AND orgId=?', $nId, $omap->id))) {
$nmap->views++;
$nmap->save();
// Render
$f3->set('v_org', $omap);
$f3->set('v_let', $nmap);
$this->_render('public/newsletter.html');
} else {
/*
* Newsletter does not exists
*
* Render a page that says so, and lists other newsletters made by the same company.
*/
$f3->set('v_org', $nmap);
$lastNews = $nmap->find(array('orgId=?', $omap->id));
$f3->set('v_lastNews', $lastNews);
$this->_render('public/newsletter_notfound.html');
}
} else {
/*
* Organisation does not exists, error page
*/
$this->_render('public/organisation_notfound.html');
}
}
function various_poll_votes($job, $step)
{
$fw = \Base::instance();
$new = "{$fw['installerCFG.db5.dbname']}`.`{$fw['installerCFG.db5.prefix']}";
$old = "{$fw['installerCFG.db3.dbname']}`.`{$fw['installerCFG.db3.prefix']}fanfiction_";
$limit = 50;
$tracking = new DB\SQL\Mapper($fw->db5, $fw->get('installerCFG.db5.prefix') . 'convert');
$tracking->load(['id = ?', $step['id']]);
if ($step['success'] == 0) {
try {
$total = $fw->db3->exec("SELECT COUNT(*) as found FROM `{$old}poll_votes`;")[0]['found'];
$fw->db5->exec("UPDATE `{$new}convert`SET `success` = 1, `total` = :total WHERE `id` = :id ", [':total' => $total, ':id' => $step['id']]);
} catch (PDOException $e) {
// There's no source table, so we just finish with 0 entries
$tracking->items = -1;
$tracking->success = 2;
$tracking->save();
return TRUE;
// escape plan
}
}
$dataIn = $fw->db3->exec("SELECT `vote_id`, `vote_user`, `vote_opt`, `vote_poll`\r\n\t\t\t\t\t\t\t\tFROM `{$old}poll_votes` \r\n\t\t\t\t\t\t\t\tORDER BY `vote_id` ASC LIMIT {$step['items']},{$limit};");
if (0 < ($count = sizeof($dataIn))) {
foreach ($dataIn as $data) {
$values[] = "( '{$data['vote_id']}', \r\n\t\t\t\t\t\t\t'{$data['vote_user']}',\r\n\t\t\t\t\t\t\t'{$data['vote_opt']}',\r\n\t\t\t\t\t\t\t'{$data['vote_poll']}' )";
}
$fw->db5->exec("INSERT INTO `{$new}poll_votes` (`vote_id`, `poll_id`, `uid`, `option`) VALUES " . implode(", ", $values) . "; ");
$count = $fw->db5->count();
$tracking->items = $tracking->items + $count;
$tracking->save();
}
if ($count == 0 or $count < $limit) {
// There was either nothing to be done, or there are no elements left for the next run
$tracking->success = 2;
$tracking->save();
}
}
function chapters_copy($job, $step)
{
// Chapters copy is a 1-pass module, doing the entire chapter relocation
$fw = \Base::instance();
$new = "{$fw['installerCFG.db5.dbname']}`.`{$fw['installerCFG.db5.prefix']}";
$old = "{$fw['installerCFG.db3.dbname']}`.`{$fw['installerCFG.db3.prefix']}fanfiction_";
$limit = 100;
$report = [];
$source = $fw->get('installerCFG.data.store');
// "files" or "mysql"
$target = $fw->get('installerCFG.chapters');
// "filebase" or "database"
// Initialize
if ($step['success'] == 0) {
// drop an existing chapter DB file
if (file_exists(realpath('..') . '/data/chapters.sq3')) {
unlink(realpath('..') . '/data/chapters.sq3');
}
// if we need the filebase storage, initialize it now
if ($target = "filebase") {
$fw->dbsqlite = new DB\SQL('sqlite:' . realpath('..') . '/data/chapters.sq3');
$fw->dbsqlite->begin();
$fw->dbsqlite->exec("DROP TABLE IF EXISTS 'chapters'");
$fw->dbsqlite->exec("CREATE TABLE IF NOT EXISTS 'chapters' ('chapid' INTEGER PRIMARY KEY NOT NULL, 'sid' INTEGER, 'chaptertext' BLOB);");
// inorder might be obsolete
//$fw->dbsqlite->exec ( "CREATE TABLE IF NOT EXISTS 'chapters' ('chapid' INTEGER PRIMARY KEY NOT NULL, 'sid' INTEGER, 'inorder' INTEGER,'chaptertext' BLOB);" );
$fw->dbsqlite->commit();
unset($fw->dbsqlite);
} else {
}
// Count total chapters and take note
$total = $fw->db3->exec("SELECT COUNT(*) as found FROM `{$old}chapters`;")[0]['found'];
$fw->db5->exec("UPDATE `{$new}convert`SET `success` = 1, `total` = :total WHERE `id` = :id ", [':total' => $total, ':id' => $step['id']]);
}
$dataIn = $fw->db3->exec("SELECT COUNT(reviewid) as reviewsNew, Ch.uid as folder, Ch.chapid as chapter, Ch.*\r\n\t\t\t\t\t\t\t\tFROM `{$old}chapters`Ch \r\n\t\t\t\t\t\t\t\tLEFT JOIN `{$old}reviews`R ON ( Ch.chapid = R.chapid AND R.type='ST' )\r\n\t\t\t\t\t\t\t\tGROUP BY Ch.chapid\r\n\t\t\t\t\t\t\t\tORDER BY chapid ASC LIMIT {$step['items']},{$limit};");
if (0 < ($count = sizeof($dataIn))) {
$newchapter = new DB\SQL\Mapper($fw->db5, $fw->get('installerCFG.db5.prefix') . 'chapters');
if ($target = "filebase") {
$fw->dbsqlite = new DB\SQL('sqlite:' . realpath('..') . '/data/chapters.sq3');
$newchapterText = new DB\SQL\Mapper($fw->dbsqlite, 'chapters');
}
$tracking = new DB\SQL\Mapper($fw->db5, $fw->get('installerCFG.db5.prefix') . 'convert');
$tracking->load(['id = ?', $step['id']]);
foreach ($dataIn as $chapterIn) {
// Get chapter text, from file or DB
if ($source == "files") {
$s = upgradetools::getChapterFile($chapterIn);
if ($s[0]) {
$chaptertext = mb_convert_encoding($s[1], "UTF-8", mb_detect_encoding($s[1], 'UTF-8, ISO-8859-1'));
} else {
// report error
}
} elseif ($source == "mysql") {
$chaptertext = $chapterIn['storytext'];
}
if ($target == "filebase") {
// No text in Database
$newchapter->chaptertext = NULL;
// Store data in the filebase storage
$newchapterText->chapid = $chapterIn['chapter'];
$newchapterText->sid = $chapterIn['sid'];
//$newchapterText->inorder = $chapterIn['inorder'];
$newchapterText->chaptertext = $chaptertext;
$newchapterText->save();
$newchapterText->reset();
} else {
$newchapter->chaptertext = $chaptertext;
}
$newchapter->chapid = $chapterIn['chapter'];
$newchapter->sid = $chapterIn['sid'];
$newchapter->title = $chapterIn['title'];
$newchapter->inorder = $chapterIn['inorder'];
$newchapter->notes = $chapterIn['notes'];
$newchapter->endnotes = $chapterIn['endnotes'];
$newchapter->validated = $chapterIn['validated'];
$newchapter->wordcount = count(preg_split("/\\p{L}[\\p{L}\\p{Mn}\\p{Pd}'\\x{2019}]{1,}/u", $chaptertext));
$newchapter->rating = $chapterIn['rating'];
$newchapter->reviews = $chapterIn['reviewsNew'];
$newchapter->count = $chapterIn['count'];
$newchapter->save();
$newchapter->reset();
$tracking->items = $tracking->items + 1;
$tracking->save();
}
//
}
if ($count == 0 or $count < $limit) {
// There was either nothing to be done, or there are no elements left for the next run
$tracking->success = 2;
$tracking->save();
}
}
private function runTestSuite($db)
{
$schema = new \DB\SQL\Schema($db);
$schema->dropTable($this->tname);
// create table
$table = $schema->createTable($this->tname);
$table = $table->build();
$result = $schema->getTables();
$this->test->expect(in_array($this->tname, $result), $this->getTestDesc('create default table'));
unset($result);
$this->test->expect($table instanceof \DB\SQL\TableModifier, $this->getTestDesc('$table->build() returns TableModifier'));
// drop table
$table->drop();
$this->test->expect(in_array($this->tname, $schema->getTables()) == false, $this->getTestDesc('drop table'));
unset($table);
// create table with columns
$table = $schema->createTable($this->tname);
$table->addColumn('title')->type($schema::DT_VARCHAR128);
$table->addColumn('number')->type($schema::DT_INT4);
$table = $table->build();
$r1 = $schema->getTables();
$r2 = $table->getCols();
$this->test->expect(in_array($this->tname, $r1) && in_array('id', $r2) && in_array('title', $r2) && in_array('number', $r2), $this->getTestDesc('create new table with additional columns'));
unset($r1, $r2);
// testing all datatypes
foreach (array_keys($schema->dataTypes) as $index => $field) {
// testing column type
$table->addColumn('column_' . $index)->type($field);
$table->build();
$r1 = $table->getCols();
$this->test->expect(in_array('column_' . $index, $r1), $this->getTestDesc('adding column [' . $field . '], nullable'));
}
unset($r1);
// adding some testing data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->column_7 = 'hello world';
$mapper->save();
$mapper->reset();
$result = $mapper->findone(array('column_7 = ?', 'hello world'))->cast();
unset($mapper);
$this->test->expect($result['column_7'] == 'hello world', $this->getTestDesc('mapping dummy data'));
// default value text, not nullable
$table->addColumn('text_default_not_null')->type($schema::DT_VARCHAR128)->nullable(false)->defaults('foo bar');
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(in_array('text_default_not_null', array_keys($r1)) && $r1['text_default_not_null']['default'] == 'foo bar' && $r1['text_default_not_null']['nullable'] == false, $this->getTestDesc('adding column [VARCHAR128], not nullable with default value'));
unset($r1);
// some testing dummy data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->column_7 = 'tanduay';
$mapper->save();
$mapper->reset();
$result = $mapper->findone(array('column_7 = ?', 'tanduay'))->cast();
$this->test->expect($result['column_7'] == 'tanduay' && $result['text_default_not_null'] == 'foo bar', $this->getTestDesc('mapping dummy data'));
unset($mapper, $result);
// default value numeric, not nullable
$table->addColumn('int_default_not_null')->type($schema::DT_INT4)->nullable(false)->defaults(123);
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(in_array('int_default_not_null', array_keys($r1)) && $r1['int_default_not_null']['default'] == 123 && $r1['int_default_not_null']['nullable'] == false, $this->getTestDesc('adding column [INT4], not nullable with default value'));
unset($r1);
// adding testing data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->column_7 = 'test3';
$mapper->save();
$mapper->reset();
$r1 = $mapper->findone(array('column_7 = ?', 'test3'))->cast();
$this->test->expect($r1['column_7'] == 'test3' && $r1['int_default_not_null'] == 123, $this->getTestDesc('mapping dummy data'));
unset($mapper, $r1);
// default value text, nullable
$table->addColumn('text_default_nullable')->type($schema::DT_VARCHAR128)->defaults('foo bar');
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(in_array('text_default_nullable', array_keys($r1)) && $r1['text_default_nullable']['default'] == 'foo bar', $this->getTestDesc('adding column [VARCHAR128], nullable with default value'));
unset($r1);
// adding some dummy data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->column_7 = 'test4';
$mapper->save();
$mapper->reset();
$mapper->column_7 = 'test5';
$mapper->text_default_nullable = null;
$mapper->save();
$mapper->reset();
$result = $mapper->find(array('column_7 = ? OR column_7 = ?', 'test4', 'test5'));
foreach ($result as &$r) {
$r = $r->cast();
}
$this->test->expect(array_key_exists(0, $result) && array_key_exists(1, $result) && $result[0]['column_7'] == 'test4' && $result[0]['text_default_nullable'] == 'foo bar' && $result[1]['column_7'] == 'test5' && $result[1]['text_default_nullable'] === null, $this->getTestDesc('mapping dummy data'));
unset($mapper, $result);
// default value numeric, nullable
$table->addColumn('int_default_nullable')->type($schema::DT_INT4)->defaults(123);
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(in_array('int_default_nullable', array_keys($r1)) == true && $r1['int_default_nullable']['default'] == 123, $this->getTestDesc('adding column [INT4], nullable with default value'));
unset($r1);
// adding dummy data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->column_7 = 'test6';
$mapper->save();
$mapper->reset();
$mapper->column_7 = 'test7';
$mapper->int_default_nullable = null;
$mapper->save();
$mapper->reset();
$result = $mapper->find(array('column_7 = ? OR column_7 = ?', 'test6', 'test7'));
foreach ($result as &$r) {
$r = $r->cast();
}
$this->test->expect(array_key_exists(0, $result) && array_key_exists(1, $result) && $result[0]['column_7'] == 'test6' && $result[0]['int_default_nullable'] === 123 && $result[1]['column_7'] == 'test7' && $result[1]['int_default_nullable'] === null, $this->getTestDesc('mapping dummy data'));
unset($mapper, $result);
// current timestamp
$table->addColumn('stamp')->type($schema::DT_TIMESTAMP)->nullable(false)->defaults($schema::DF_CURRENT_TIMESTAMP);
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(in_array('stamp', array_keys($r1)) && $r1['stamp']['default'] == $schema::DF_CURRENT_TIMESTAMP, $this->getTestDesc('adding column [TIMESTAMP], not nullable with current_timestamp default value'));
unset($r1);
// datetime nullable
$table->addColumn('datetime')->type_datetime()->nullable(true);
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(in_array('datetime', array_keys($r1)) && $r1['datetime']['nullable'] == true, $this->getTestDesc('adding column [DATETIME], nullable, no default'));
unset($r1);
// adding dummy data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->column_7 = 'test_datetime';
$mapper->datetime = NULL;
$mapper->save();
$mapper->reset();
$result = $mapper->find(array('column_7 = ?', 'test_datetime'));
foreach ($result as &$r) {
$r = $r->cast();
}
$this->test->expect(array_key_exists(0, $result) && $result[0]['column_7'] == 'test_datetime' && $result[0]['datetime'] === null, $this->getTestDesc('mapping dummy data'));
unset($mapper, $result);
// rename column
$table->renameColumn('text_default_not_null', 'title123');
$table->build();
$r1 = $table->getCols();
$this->test->expect(in_array('title123', $r1) && !in_array('text_default_not_null', $r1), $this->getTestDesc('renaming column'));
unset($r1);
// adding dummy data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->title123 = 'test8';
$mapper->save();
$mapper->reset();
$result = $mapper->findone(array('title123 = ?', 'test8'));
$this->test->expect(!$result->dry(), $this->getTestDesc('mapping dummy data'));
$table->renameColumn('title123', 'text_default_not_null');
$table->build();
unset($result, $mapper);
// remove column
$table->dropColumn('column_1');
$table->build();
$r1 = $table->getCols();
$this->test->expect(!in_array('column_1', $r1), $this->getTestDesc('removing column'));
unset($r1);
// rename table
$schema->dropTable('test123');
$table->rename('test123');
$result = $schema->getTables();
$this->test->expect(in_array('test123', $result) && !in_array($this->tname, $result), $this->getTestDesc('renaming table'));
$table->rename($this->tname);
unset($result);
// check record count
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$this->test->expect(count($mapper->find()) == 9, $this->getTestDesc('check record count'));
unset($mapper);
// adding composite primary keys
$table->addColumn('version')->type($schema::DT_INT4)->nullable(false)->defaults(1);
$table->primary(array('id', 'version'));
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(!empty($r1) && isset($r1['version']) && $r1['id']['pkey'] == true && $r1['version']['pkey'] == true, $this->getTestDesc('adding composite primary-keys'));
unset($r1);
// check record count
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$this->test->expect(count($mapper->find()) == 9, $this->getTestDesc('check record count'));
unset($mapper);
// drop table
$schema->dropTable($this->tname);
$this->test->expect(!in_array($this->tname, $schema->getTables()), $this->getTestDesc('drop table'));
// adding composite primary keys
$table = $schema->createTable($this->tname);
$table->addColumn('version')->type($schema::DT_INT4)->defaults(1)->nullable(false);
$table->primary(array('id', 'version'));
$table = $table->build();
$r1 = $table->getCols(true);
$this->test->expect(!empty($r1) && $r1['id']['pkey'] == true && $r1['version']['pkey'] == true, $this->getTestDesc('creating new table with composite key'));
$this->test->expect(!empty($r1) && $r1['version']['default'] == '1', $this->getTestDesc('default value on composite primary key'));
unset($r1);
// more fields to composite primary key table
$table->addColumn('title')->type($schema::DT_VARCHAR256);
$table->addColumn('title2')->type($schema::DT_TEXT);
$table->addColumn('title_notnull')->type($schema::DT_VARCHAR128)->nullable(false)->defaults("foo");
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(array_key_exists('title', $r1) && array_key_exists('title_notnull', $r1) && $r1['id']['pkey'] == true && $r1['version']['pkey'] == true, $this->getTestDesc('adding more fields to composite pk table'));
unset($r1);
// testing primary keys with inserted data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->title = 'test1';
$mapper->save();
$mapper->reset();
$mapper->id = 1;
$mapper->title = 'nullable';
$mapper->version = 2;
$mapper->save();
$mapper->reset();
$mapper->title = 'test3';
$mapper->title2 = 'foobar';
$mapper->title_notnull = 'bar';
$mapper->save();
$result = array_map(array($mapper, 'cast'), $mapper->find());
$cpk_expected = array(0 => array('id' => 1, 'version' => 1, 'title' => 'test1', 'title2' => NULL, 'title_notnull' => 'foo'), 1 => array('id' => 1, 'version' => 2, 'title' => 'nullable', 'title2' => NULL, 'title_notnull' => 'foo'), 2 => array('id' => 2, 'version' => 1, 'title' => 'test3', 'title2' => 'foobar', 'title_notnull' => 'bar'));
foreach ($result as &$r) {
ksort($r);
}
foreach ($cpk_expected as &$r) {
ksort($r);
}
$this->test->expect(json_encode($result) == json_encode($cpk_expected), $this->getTestDesc('adding items with composite primary-keys'));
$schema->dropTable($this->tname);
// indexes
$table = $schema->createTable($this->tname);
$table->addColumn('rawtest', array('type' => $schema::DT_VARCHAR256, 'default' => 'foo'));
$table->addColumn('text')->type($schema::DT_TEXT);
$table->addColumn('foo')->type($schema::DT_VARCHAR128)->index();
$table = $table->build();
$r1 = $table->getCols(true);
$this->test->expect(isset($r1['rawtest']) && ($r1['rawtest']['default'] = 'foo'), $this->getTestDesc('adding column with options array'));
$indexes = $table->listIndex();
$this->test->expect(isset($indexes[$table->name . '___foo']), $this->getTestDesc('column index on table creation'));
$table->addColumn('bar')->type($schema::DT_VARCHAR128)->index(true);
$table->addColumn('baz')->type($schema::DT_VARCHAR128);
$table->addIndex(array('foo', 'baz'));
$table->build();
$indexes = $table->listIndex();
$this->test->expect(isset($indexes[$table->name . '___bar']), $this->getTestDesc('column index on table alteration'));
$this->test->expect(isset($indexes[$table->name . '___bar']) && $indexes[$table->name . '___bar']['unique'] == true, $this->getTestDesc('unique index'));
$this->test->expect(isset($indexes[$table->name . '___foo__baz']), $this->getTestDesc('index on combined columns'));
if ($this->current_engine == 'sqlite') {
$table->dropColumn('rawtest');
$table->build();
$indexes = $table->listIndex();
$this->test->expect(isset($indexes[$table->name . '___foo__baz']) && isset($indexes[$table->name . '___bar']) && $indexes[$table->name . '___bar']['unique'], $this->getTestDesc('preserve indexes after table rebuild'));
}
$table->dropIndex($table->name . '___bar');
$table->build();
$indexes = $table->listIndex();
$this->test->expect(!array_key_exists($table->name . '___bar', $indexes), $this->getTestDesc('drop index'));
// update column
$table->updateColumn('bar', $schema::DT_TEXT);
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(array_key_exists('bar', $r1) && $r1['bar']['type'] == 'text', $this->getTestDesc('update column'));
// create table with text not nullable column
$table2 = $schema->createTable($this->tname . '_notnulltext');
$table2->addColumn('desc')->type($schema::DT_TEXT)->nullable(false);
$table2 = $table2->build();
$r1 = $schema->getTables();
$r2 = $table2->getCols(true);
$this->test->expect(in_array($this->tname . '_notnulltext', $r1) && array_key_exists('desc', $r2) && $r2['desc']['nullable'] == false, $this->getTestDesc('create new table with not nullable text column'));
$table2->drop();
}
function edit($f3)
{
$db = $f3->get('db');
$channel = new DB\SQL\Mapper($db, 'channels');
$channel->load(array('id=?', $f3->get('PARAMS.channelID')));
// Delete Channel
if ($f3->get('POST.delete') == '1') {
$channel->erase();
$db->exec('DELETE FROM videos WHERE channel=?', $f3->get('PARAMS.channelID'));
$db->exec('DELETE FROM schedule WHERE channel=?', $f3->get('PARAMS.channelID'));
$f3->reroute('@channelList');
// Update Name
} else {
$channel->name = $f3->get('POST.name');
$channel->live = $f3->get('POST.live');
$channel->save();
$f3->reroute('@channelList');
}
}
public function save()
{
global $f3;
$e = new DB\SQL\Mapper(Events::$db, 'events');
$get_id = true;
if ($this->id) {
$e->load(array('id=?', $this->id));
$get_id = false;
}
$e->id = $this->id;
$e->title = $this->title;
$e->startdt = $this->startdt->format("Y-m-d H:i");
if ($this->enddt) {
$e->enddt = $this->enddt->format("Y-m-d H:i");
} else {
$e->enddt = NULL;
}
$e->location_text = $this->location->dbname();
$e->blurb = $this->blurb;
$e->url = $this->url;
$e->cost = $this->cost;
if ($this->film) {
$e->type = "film";
}
$e->host = $this->host;
if (!$this->slug) {
$setslug = true;
} else {
$e->slug = $this->slug;
}
$e->email = $this->email;
$e->state = $this->state;
$e->save();
if ($get_id && $setslug) {
$e->slug = $this->generateSlug();
$e->save();
}
}
$row = array('id' => $id, 'randomNumber' => $db->exec('SELECT randomNumber FROM World WHERE id = ?', $id, 0, false));
$rnu = mt_rand(1, 10000);
$row['randomNumber'] = $rnu;
$db->exec('UPDATE World SET randomNumber = :ranNum WHERE id = :id', array(':ranNum' => $rnu, ':id' => $id), 0, false);
$result[] = $row;
}
header("Content-type: application/json");
echo json_encode($result);
});
$f3->route(array('GET /update-orm', 'GET /update-orm/@queries'), function ($f3, $params) {
/** @var Base $f3 */
$queries = 1;
if (isset($params['queries'])) {
$queries = (int) $params['queries'];
$queries = $queries < 1 ? 1 : ($queries > 500 ? 500 : $queries);
}
$dbc = $f3->get('DBS');
$db = new \DB\SQL($dbc[0], $dbc[1], $dbc[2], array(\PDO::ATTR_PERSISTENT => TRUE));
$world = new \DB\SQL\Mapper($db, 'World');
$result = array();
for ($i = 0; $i < $queries; $i++) {
$id = mt_rand(1, 10000);
$world->load(array('id = ?', $id));
$world->randomNumber = mt_rand(1, 10000);
$world->save();
$result[] = $world->cast();
}
header("Content-type: application/json");
echo json_encode($result);
});
$f3->run();
static function save($ID, $values)
{
$timer = new timer();
$f3 = \base::instance();
$user = $f3->get("user");
// test_array($values);
$IDorig = $ID;
$changes = array();
$art = new \DB\SQL\Mapper($f3->get("DB"), "dir_items");
$art->load("ID='{$ID}'");
// test_array(array($art->ID,$ID));
//test_array($this->get("14"));
foreach ($values as $key => $value) {
$value = $f3->scrub($value, $f3->get("TAGS"));
if (isset($art->{$key})) {
$art->{$key} = $value;
}
}
$art->save();
$ID = $art->ID ? $art->ID : $art->_id;
if (isset($values['photos'])) {
$art = new \DB\SQL\Mapper($f3->get("DB"), "dir_items_photos");
foreach ($values['photos'] as $item) {
$art->load("ID='{$item['ID']}'");
$art->itemID = $ID;
$art->photo = $item['photo'];
$art->save();
$art->reset();
}
}
if (isset($values['categoryID'])) {
$f3->get("DB")->exec("DELETE FROM dir_item_category WHERE itemID = '{$ID}';");
if (count($values['categoryID'])) {
foreach ((array) $values['categoryID'] as $item) {
$f3->get("DB")->exec("INSERT INTO dir_item_category (itemID, catID) VALUES ('{$ID}','{$item}');");
}
}
}
$timer->_stop(__NAMESPACE__, __CLASS__, __FUNCTION__, func_get_args());
return $ID;
}
public function saveShout($id, array $data)
{
$shout = new \DB\SQL\Mapper($this->db, $this->prefix . 'shoutbox');
$shout->load(array('id=?', $id));
$shout->copyfrom(["message" => $data['message']]);
$i = $shout->changed("message");
$shout->save();
return $i;
}
function recommendations_cache($job, $step)
{
$fw = \Base::instance();
$new = "{$fw['installerCFG.db5.dbname']}`.`{$fw['installerCFG.db5.prefix']}";
$limit = 50;
$tracking = new DB\SQL\Mapper($fw->db5, $fw->get('installerCFG.db5.prefix') . 'convert');
$tracking->load(['id = ?', $step['id']]);
if ($step['success'] == 0) {
$total = $fw->db5->exec("SELECT COUNT(*) as found FROM `{$new}recommendations`;")[0]['found'];
$fw->db5->exec("UPDATE `{$new}convert`SET `success` = 1, `total` = :total WHERE `id` = :id ", [':total' => $total, ':id' => $step['id']]);
}
$dataIn = $fw->db5->exec("SELECT SELECT_OUTER.recid,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT tid,',',tag,',',description ORDER BY `order`,tgid,tag ASC SEPARATOR '||') AS tagblock,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT charid,',',charname ORDER BY charname ASC SEPARATOR '||') AS characterblock,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT cid,',',category ORDER BY category ASC SEPARATOR '||' ) as categoryblock,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT ratingid,',',rating_name,',',rating_image SEPARATOR '||' ) as rating\r\n\t\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\t\t\tSELECT R.recid,\r\n\t\t\t\t\t\t\t\t\t\tR.ratingid, Ra.rating as rating_name, IF(Ra.rating_image,Ra.rating_image,'') as rating_image,\r\n\t\t\t\t\t\t\t\t\t\tCat.cid, Cat.category,\r\n\t\t\t\t\t\t\t\t\t\tTG.description,TG.order,TG.tgid,T.label as tag,T.tid,\r\n\t\t\t\t\t\t\t\t\t\tCh.charid, Ch.charname\r\n\t\t\t\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\t\t\t\t\tSELECT R1.*\r\n\t\t\t\t\t\t\t\t\t\t\tFROM `{$new}recommendations` R1\r\n\t\t\t\t\t\t\t\t\t\t\tWHERE R1.cache_tags IS NULL\r\n\t\t\t\t\t\t\t\t\t\t\tLIMIT 0,{$limit}\r\n\t\t\t\t\t\t\t\t\t\t) AS R\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}ratings` Ra ON ( Ra.rid = R.ratingid )\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}recommendation_relations`rRT ON ( rRT.recid = R.recid )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}tags` T ON ( T.tid = rRT.relid AND rRT.type='T' )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}tag_groups` TG ON ( TG.tgid = T.tgid )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}characters` Ch ON ( Ch.charid = rRT.relid AND rRT.type = 'CH' )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}categories` Cat ON ( Cat.cid = rRT.relid AND rRT.type = 'CA' )\r\n\t\t\t\t\t\t\t\t)AS SELECT_OUTER\r\n\t\t\t\t\t\t\t\tGROUP BY recid ORDER BY recid ASC;");
if (0 < ($count = sizeof($dataIn))) {
foreach ($dataIn as $item) {
$fw->db5->exec("UPDATE `{$new}recommendations` SET \r\n\t\t\t\t\t`cache_tags`\t\t= :tagblock,\r\n\t\t\t\t\t`cache_characters`\t= :characterblock,\r\n\t\t\t\t\t`cache_categories`\t= :categoryblock,\r\n\t\t\t\t\t`cache_rating`\t\t= :rating\r\n\t\t\t\tWHERE recid = {$item['recid']} ;", [':tagblock' => json_encode(upgradetools::cleanResult($item['tagblock'])), ':characterblock' => json_encode(upgradetools::cleanResult($item['characterblock'])), ':categoryblock' => json_encode(upgradetools::cleanResult($item['categoryblock'])), ':rating' => json_encode(explode(",", $item['rating']))]);
}
$tracking->items = $tracking->items + $count;
$tracking->save();
}
if ($count == 0 or $count < $limit) {
// There was either nothing to be done, or there are no elements left for the next run
$tracking->success = 2;
$tracking->save();
}
}
function feedback_news($job, $step)
{
$fw = \Base::instance();
$new = "{$fw['installerCFG.db5.dbname']}`.`{$fw['installerCFG.db5.prefix']}";
$old = "{$fw['installerCFG.db3.dbname']}`.`{$fw['installerCFG.db3.prefix']}fanfiction_";
$limit = 500;
$i = 0;
//$newdata = new \DB\SQL\Mapper( $fw->db5, $fw['installerCFG.db5.prefix']."stories" );
if ($step['success'] == 0) {
$total = $fw->db3->exec("SELECT COUNT(*) as found FROM `{$old}comments`;")[0]['found'];
$fw->db5->exec("UPDATE `{$new}convert`SET `success` = 1, `total` = :total WHERE `id` = :id ", [':total' => $total, ':id' => $step['id']]);
}
$dataIn = $fw->db3->exec("SELECT C.nid, C.uid, C.comment, C.time FROM `{$old}comments`C LIMIT {$step['items']},{$limit};");
$tracking = new DB\SQL\Mapper($fw->db5, $fw->get('installerCFG.db5.prefix') . 'convert');
$tracking->load(['id = ?', $step['id']]);
if (0 < ($count = sizeof($dataIn))) {
/* this is slower, but requires no quoting. will keep the code in case need arises
foreach($dataIn as $data)
{
$i++;
$newdata->copyfrom($data);
$newdata->save();
$newdata->reset();
$tracking->items = $tracking->items+1;
$tracking->save();
}*/
// build the insert values
foreach ($dataIn as $data) {
$values[] = "( '{$data['nid']}', '{$data['uid']}', {$fw->db5->quote($data['comment'])}, '{$data['time']}', 'C' )";
}
$fw->db5->exec("INSERT INTO `{$new}feedback` ( `reference`, `writer_uid`, `text`, `datetime`, `type` ) VALUES " . implode(", ", $values) . "; ");
$count = $fw->db5->count();
$tracking->items = $tracking->items + $count;
$tracking->save();
}
if ($count == 0 or $count < $limit) {
// There was either nothing to be done, or there are no elements left for the next run
$tracking->success = 2;
$tracking->save();
}
}
} elseif ($lifetime == '1m') {
$lifetime = date('Y-m-d H:i:s', strtotime('+1 month'));
} elseif ($lifetime == '1y') {
$lifetime = date('Y-m-d H:i:s', strtotime('+1 year'));
}
// check max size
$size = strlen($f3->get('POST.cryptdown'));
$max_size = $f3->get('max_paste_size') * 1000;
if ($size > $max_size) {
$f3->error(400, 'Your document (' . $size / 1000 . 'kb) exceeds the maximum size of ' . $max_size / 1000 . 'kb');
}
$mapper->data = $f3->get('POST.cryptdown');
$mapper->crdate = date('Y-m-d H:i:s');
$mapper->lifetime = $lifetime;
$mapper->uuid = $f3->hash($f3->SALT . time());
$mapper->save();
if ($f3->get('AJAX')) {
$f3->status(200);
echo json_encode(array('pasteURI' => $f3->SCHEME . '://' . $f3->HOST . $f3->BASE . '/view/' . $mapper->uuid, 'pasteID' => $mapper->uuid));
exit;
}
$f3->reroute('/view/' . $mapper->uuid);
} else {
if ($f3->get('AJAX')) {
$f3->error(400, 'No message body send');
} else {
$f3->reroute('/');
}
}
});
// setup the database
$message .= "\nemail: " . $user_profile->email;
$message .= "\n\n\nPop up the champaign!";
$sent = $smtp->send($message, TRUE);
$mylog = $smtp->log();
}
$user->email = $username;
if (!empty($user_profile->displayName)) {
$user->name = $user_profile->displayName;
}
if (!empty($user_profile->firstName)) {
$user->first = $user_profile->firstName;
}
if (!empty($user_profile->photoURL)) {
$user->image = $user_profile->photoURL;
}
$user->save();
$f3->set('SESSION.name', $user->name);
$f3->set('SESSION.id', $user->id);
$f3->set('SESSION.first', $user->first);
$f3->set('SESSION.image', $user->image);
$f3->set('SESSION.memo', $user->memo);
$f3->set('SESSION.email', $user->email);
$f3->set('SESSION.role', $user->role);
$f3->set('SESSION.created', $user->created);
$f3->set('SESSION.last_modified', $user->last_modified);
$f3->set('SESSION.logged_in', 'ok');
$f3->reroute('/');
} catch (Exception $e) {
echo "Argl! we got an error: " . $e->getMessage();
echo " Error code: " . $e->getCode();
}
$response['status'] = 404;
$response['message'] = 'Email is not registered';
echo json_encode($response);
return;
} else {
if ($user->password == $password) {
if (!empty($latitude) && !empty($longitude) && !empty($locationText)) {
$locations = new DB\SQL\Mapper($db, 'location');
$location = $locations->findone(array('user_name = ? and latitude = ? and longitude = ?', $username, $latitude, $longitude));
if (empty($location)) {
$location = new DB\SQL\Mapper($db, 'location');
$location->user_name = $user->user_name;
$location->latitude = $latitude;
$location->longitude = $longitude;
$location->location = $locationText;
$location->save();
}
}
$user->online_status = date('Y-m-d H:i:s');
$user->last_active = date('Y-m-d H:i:s');
$user->save();
$response['status'] = 200;
$response['message'] = 'Successfully logged in user';
$response['data'] = $user->cast();
$locations = new DB\SQL\Mapper($db, 'location');
$location = $locations->findone(['user_name = ?', $user->user_name], ['order' => 'location_id DESC']);
if (!empty($location)) {
$response['data']['location'] = $location->cast();
}
$educations = new DB\SQL\Mapper($db, 'education');
$education = $educations->findone(['username = ?', $user->user_name]);
function users_favourites($job, $step)
{
$fw = \Base::instance();
$new = "{$fw['installerCFG.db5.dbname']}`.`{$fw['installerCFG.db5.prefix']}";
$old = "{$fw['installerCFG.db3.dbname']}`.`{$fw['installerCFG.db3.prefix']}fanfiction_";
$limit = 500;
$i = 0;
if ($step['success'] == 0) {
$total = $fw->db3->exec("SELECT COUNT(*) as found FROM `{$old}favorites` WHERE uid > 0;")[0]['found'];
$fw->db5->exec("UPDATE `{$new}convert`SET `success` = 1, `total` = :total WHERE `id` = :id ", [':total' => $total, ':id' => $step['id']]);
}
$dataIn = $fw->db3->exec("SELECT `uid`, `item`, `type`, `comments`\r\n\t\t\t\t\t\t\t\tFROM `{$old}favorites`\r\n\t\t\t\t\t\t\t\tORDER BY uid, item, type ASC LIMIT {$step['items']},{$limit};");
$tracking = new DB\SQL\Mapper($fw->db5, $fw->get('installerCFG.db5.prefix') . 'convert');
$tracking->load(['id = ?', $step['id']]);
if (0 < ($count = sizeof($dataIn))) {
foreach ($dataIn as $data) {
$values[] = "( '{$data['uid']}',\r\n\t\t\t\t\t\t\t'{$data['item']}', \r\n\t\t\t\t\t\t\t'{$data['type']}', \r\n\t\t\t\t\t\t\t{$fw->db5->quote($data['comments'])} )";
}
$fw->db5->exec("INSERT INTO `{$new}user_favourites` ( `uid`, `item`, `type`, `comments` ) VALUES " . implode(", ", $values) . "; ");
$count = $fw->db5->count();
$tracking->items = $tracking->items + $count;
$tracking->save();
}
if ($count == 0 or $count < $limit) {
// There was either nothing to be done, or there are no elements left for the next run
$tracking->success = 2;
$tracking->save();
}
}
