function detect_bad_utf8($mysqli, $flag = false)
{
$sql = "select max(id) as total from sc_post ";
$row = MySQL\Helper::fetchRow($mysqli, $sql);
$total = $row["total"];
$pageSize = 50;
$pages = ceil($total / $pageSize);
$count = 0;
while ($count <= $pages) {
$start = $count * $pageSize + 1;
$end = $start + ($pageSize - 1);
$sql = " select pseudo_id,title,description from sc_post where (id <= {end}) and (id >= {start} ) ";
$sql = str_replace(array("{end}", "{start}"), array(0 => $end, 1 => $start), $sql);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
printf("processing row between %d and %d \n", $start, $end);
foreach ($rows as $row) {
$description = $row['description'];
if (!Util::isUtf8($description)) {
printf("Bad utf-8 for item - %s \n", $row['pseudo_id']);
if ($flag) {
$clean_description = Util::filterBadUtf8($description);
$clean_title = Util::filterBadUtf8($row['title']);
update_clean_utf8($mysqli, $row['pseudo_id'], $clean_title, $clean_description);
}
}
}
sleep(1);
$count++;
}
}
static function getPaged($start, $direction, $limit, $filters)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($start, "integer");
settype($limit, "integer");
$direction = $mysqli->real_escape_string($direction);
$sql = " select a.*,l.name as user_name from sc_comment a,sc_login l ";
$q = new MySQL\Query($mysqli);
$q->setAlias("com\\indigloo\\sc\\model\\Comment", "a");
//raw condition
$q->addCondition("l.id = a.login_id");
$q->filter($filters);
$sql .= $q->get();
$sql .= $q->getPagination($start, $direction, "a.id", $limit);
if (Config::getInstance()->is_debug()) {
Logger::getInstance()->debug("sql => {$sql} \n");
}
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
//reverse rows for 'before' direction
if ($direction == 'before') {
$results = array_reverse($rows);
return $results;
}
return $rows;
}
static function currentSessions()
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = " select l.id as login_id, l.name, s.updated_on from sc_php_session s, sc_login l ";
$sql .= "where l.session_id = s.session_id and s.updated_on > (now() - interval 1 day) ";
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
return $rows;
}
static function getTmpPSData($postId)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($postId, "integer");
$sql = " select * from sc_tmp_ps where post_id = %d ";
$sql = sprintf($sql, $postId);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
return $rows;
}
static function getLatest($limit)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($limit, "integer");
$sql = " select * from sc_login order by id desc limit %d ";
$sql = sprintf($sql, $limit);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
return $rows;
}
function process_groups($mysqli)
{
//process groups
$sql = " select post_id from sc_site_tracker where group_flag = 0 order by id desc limit 50";
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
$groupDao = new \com\indigloo\sc\dao\Group();
foreach ($rows as $row) {
$postId = $row["post_id"];
$groupDao->process($postId);
}
}
static function getFollowers($loginId, $limit)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($loginId, "integer");
settype($limit, "integer");
$sql = " select u.name, u.login_id, u.photo_url from sc_denorm_user u, ";
$sql .= " sc_follow s where u.login_id = s.follower_id and s.following_id = %d limit %d ";
$sql = sprintf($sql, $loginId, $limit);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
return $rows;
}
static function getPaged($start, $direction, $limit)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($start, "integer");
settype($limit, "integer");
$direction = $mysqli->real_escape_string($direction);
$sql = " select f.* from sc_feedback f ";
$q = new MySQL\Query($mysqli);
$sql .= $q->getPagination($start, $direction, "f.id", $limit);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
//reverse rows for 'before' direction
if ($direction == 'before') {
$results = array_reverse($rows);
return $results;
}
return $rows;
}
function run_diagnostic($mysqli, $flag = false)
{
$sql = "select max(id) as total from sc_post ";
$row = MySQL\Helper::fetchRow($mysqli, $sql);
$total = $row["total"];
$pageSize = 50;
$pages = ceil($total / $pageSize);
$count = 0;
while ($count <= $pages) {
$start = $count * $pageSize + 1;
$end = $start + ($pageSize - 1);
$sql = " select pseudo_id,created_on,images_json from sc_post where (id <= {end}) and (id >= {start} ) ";
$sql = str_replace(array("{end}", "{start}"), array(0 => $end, 1 => $start), $sql);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
foreach ($rows as $row) {
$images = json_decode($row["images_json"]);
foreach ($images as $image) {
detect_bad($row["pseudo_id"], $row["created_on"], $image);
}
}
$count++;
}
}
function load_in_redis($mysqli)
{
$sql = "select max(id) as total from sc_activity ";
$row = MySQL\Helper::fetchRow($mysqli, $sql);
$total = $row["total"];
$pageSize = 50;
$pages = ceil($total / $pageSize);
$count = 0;
$activityDao = new \com\indigloo\sc\dao\Activity();
while ($count <= $pages) {
$start = $count * $pageSize + 1;
$end = $start + ($pageSize - 1);
$sql = " select * from sc_activity where (id <= {end}) and (id >= {start} ) ";
$sql = str_replace(array("{end}", "{start}"), array(0 => $end, 1 => $start), $sql);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
foreach ($rows as $row) {
$activityDao->pushToRedis($row);
}
printf("processed rows between %s and %s \n", $start, $end);
flush();
sleep(1);
$count++;
}
}
static function getPagedItems($start, $direction, $limit, $filters)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($start, "integer");
settype($limit, "integer");
$direction = $mysqli->real_escape_string($direction);
$sql = " select login.name as user_name, post.*, li.item_id, li.id as sort_id " . " from sc_list_item li " . " left join sc_post post on li.item_id = post.id " . " left join sc_login login on login.id = post.login_id ";
$q = new MySQL\Query($mysqli);
$q->setAlias("com\\indigloo\\sc\\model\\ListItem", "li");
$q->filter($filters);
$condition = $q->get();
$sql .= $condition;
$sql .= $q->getPagination($start, $direction, "li.id", $limit);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
//reverse rows for 'before' direction
if ($direction == 'before') {
$results = array_reverse($rows);
return $results;
}
return $rows;
}
function process_activities($mysqli)
{
/*
* process activities data
* @imp activities should be brought in the order that they have happened
* so sort on id ASC
*/
$sql = " select * from sc_activity where op_bit = 0 order by id limit 50";
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
$activityDao = new \com\indigloo\sc\dao\Activity();
foreach ($rows as $row) {
try {
$sql2 = " update sc_activity set op_bit = 1 where id = " . $row["id"];
$feed = $activityDao->pushToRedis($row);
$activityDao->sendMail($row, $feed);
//flip the op_bit for this activity
MySQL\Helper::executeSQL($mysqli, $sql2);
} catch (\Exception $ex) {
Logger::getInstance()->error($ex->getMessage());
}
}
}
static function getPagedOnCategory($start, $direction, $limit, $code)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
$code = $mysqli->real_escape_string($code);
$direction = $mysqli->real_escape_string($direction);
settype($start, "integer");
settype($limit, "integer");
$sql = " select q.*,l.name as user_name from sc_post q,sc_login l ";
$codeCondition = sprintf("cat_code = '%s'", $code);
$q = new MySQL\Query($mysqli);
$q->addCondition("l.id = q.login_id");
$q->addCondition($codeCondition);
$sql .= $q->get();
$sql .= $q->getPagination($start, $direction, "q.id", $limit);
if (Config::getInstance()->is_debug()) {
Logger::getInstance()->debug("sql => {$sql} \n");
}
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
//reverse rows for 'before' direction
if ($direction == 'before') {
$results = array_reverse($rows);
return $results;
}
return $rows;
}
function getQuorum($index, $token, $hits, $offset, $limit)
{
if (Util::tryEmpty($token)) {
return array();
}
Util::isEmpty("index", $index);
$sql = sprintf("select id from %s where match('", $index);
$sql .= '\\"' . $token . '\\"\\/' . $hits . "')";
$sql .= sprintf(" limit %d,%d ", $offset, $limit);
$rows = MySQL\Helper::fetchRows($this->connx, $sql);
$ids = array();
foreach ($rows as $row) {
array_push($ids, $row["id"]);
}
return $ids;
}
static function getPaged($start, $direction, $limit, $filters)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($start, "integer");
settype($limit, "integer");
$direction = $mysqli->real_escape_string($direction);
$sql = " select u.* from sc_denorm_user u ";
$q = new MySQL\Query($mysqli);
$q->setAlias("com\\indigloo\\sc\\model\\User", "u");
$q->filter($filters);
$condition = $q->get();
$sql .= $condition;
$sql .= $q->getPagination($start, $direction, "u.id", $limit);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
//reverse rows for 'before' direction
if ($direction == 'before') {
$results = array_reverse($rows);
return $results;
}
return $rows;
}
static function getLikeOnItemId($itemId)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($itemId, "integer");
//@todo change UI if num_likes > 10
$sql = " select l.id as login_id, l.name as user_name ";
$sql .= " from sc_bookmark b , sc_login l ";
$sql .= " where b.subject_id = l.id and b.verb = 1 and b.object_id = %d limit 10 ";
$sql = sprintf($sql, $itemId);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
return $rows;
}
static function uizmembersAsMap($key)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = "select seo_key as id, name as name from sc_ui_zset ";
$sql .= " where set_key = '%s' order by ui_order";
$sql = sprintf($sql, $key);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
return $rows;
}
$lname = empty($row["last_name"]) ? "_EMPTY_" : $row["last_name"];
$email = $row["email"];
$buffer = sprintf("%s,%s,%s \n", $fname, $lname, $email);
fwrite($fhandle, $buffer);
}
// + start
$mysqli = \com\indigloo\mysql\Connection::getInstance()->getHandle();
$ufile = "3mik-users.csv";
$fhandle = fopen($ufile, "w");
fwrite($fhandle, "first name,last name,email \n");
$sql = "select count(id) as total from sc_denorm_user ";
$row = MySQL\Helper::fetchRow($mysqli, $sql);
$total = $row["total"];
$pageSize = 50;
$pages = ceil($total / $pageSize);
$count = 0;
while ($count <= $pages) {
$start = $count * $pageSize + 1;
$end = $start + ($pageSize - 1);
$sql = " select * from sc_denorm_user where (id <= {end}) and (id >= {start} ) ";
$sql = str_replace(array("{end}", "{start}"), array(0 => $end, 1 => $start), $sql);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
printf("processing user rows between %d and %d \n", $start, $end);
foreach ($rows as $row) {
write_csv_row($fhandle, $row);
}
$count++;
}
//close resources
$mysqli->close();
fclose($fhandle);
function post_to_activity($mysqli)
{
$sql = "select max(id) as total from sc_post";
$row = MySQL\Helper::fetchRow($mysqli, $sql);
$total = $row["total"];
$pageSize = 50;
$pages = ceil($total / $pageSize);
$count = 0;
$userDao = new \com\indigloo\sc\dao\User();
$activityDao = new \com\indigloo\sc\dao\Activity();
while ($count <= $pages) {
$start = $count * $pageSize + 1;
$end = $start + ($pageSize - 1);
$sql = " select * from sc_post where (id <= {end}) and (id >= {start} ) ";
$sql = str_replace(array("{end}", "{start}"), array(0 => $end, 1 => $start), $sql);
$rows = MySQL\Helper::fetchRows($mysqli, $sql);
foreach ($rows as $row) {
$subjectId = $row['login_id'];
$ownerId = $row['login_id'];
$postId = $row['id'];
$objectId = PseudoId::encode($postId);
$userDBRow = $userDao->getOnLoginId($subjectId);
$subject = $userDBRow['name'];
$object = $row['title'];
$object = Util::filterBadUtf8($object);
$verb = \com\indigloo\sc\Constants::POST_VERB;
$activityDao->addRow($ownerId, $subjectId, $objectId, $subject, $object, $verb);
}
flush();
sleep(1);
$count++;
}
}
