static function getUserCounters($loginId)
{
settype($loginId, "integer");
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = "select * from sc_user_counter where login_id = %d ";
$sql = sprintf($sql, $loginId);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
static function getOnLoginId($loginId)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
settype($loginId, "integer");
$sql = " select * from sc_user where login_id = %d ";
$sql = sprintf($sql, $loginId);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
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 delete($id)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = "delete from sc_feedback where id = ?";
$stmt = $mysqli->prepare($sql);
if ($stmt) {
$stmt->bind_param("i", $id);
$stmt->execute();
$stmt->close();
} else {
MySQL\Error::handle($mysqli);
}
}
static function getOnTwitterId($twitterId)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
//sanitize input
$twitterId = $mysqli->real_escape_string($twitterId);
if (strlen($twitterId) > 64) {
trigger_error("Twitter id is longer than 64 chars", E_USER_ERROR);
}
$sql = " select * from sc_twitter where twitter_id = '%s' ";
$sql = sprintf($sql, $twitterId);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
static function capture($emails, $message)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = " insert into sc_email_capture(email,message,created_on) values ";
//sanitize message
$message = $mysqli->real_escape_string($message);
$isize = sizeof($emails);
if ($isize == 0) {
return;
}
for ($index = 0; $index < $isize; $index++) {
//last one?
$suffix = $index == $isize - 1 ? "" : ",";
$email = $emails[$index];
//sanitize email
$email = $mysqli->real_escape_string($email);
$sql .= sprintf(" ('%s','%s',now())%s ", $email, $message, $suffix);
}
MySQL\Helper::executeSQL($mysqli, $sql);
}
static function getPagedUserGroups($start, $direction, $limit, $filters)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = "select ug.* from sc_user_group ug";
//sanitize input
settype($start, "integer");
settype($limit, "integer");
$direction = $mysqli->real_escape_string($direction);
$q = new MySQL\Query($mysqli);
$q->setAlias("com\\indigloo\\sc\\model\\Group", "ug");
$q->filter($filters);
$sql .= $q->get();
$sql .= $q->getPagination($start, $direction, "ug.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 add($mediaVO)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$mediaId = NULL;
$sql = " insert into sc_media(bucket,original_name, stored_name, ";
$sql .= " size,mime, original_height, original_width,created_on,store,thumbnail,thumbnail_name) ";
$sql .= " values(?,?,?,?,?,?,?,now(),?,?,?) ";
$stmt = $mysqli->prepare($sql);
if ($stmt) {
$stmt->bind_param("sssisiisss", $mediaVO->bucket, $mediaVO->originalName, $mediaVO->storeName, $mediaVO->size, $mediaVO->mime, $mediaVO->height, $mediaVO->width, $mediaVO->store, $mediaVO->thumbnail, $mediaVO->thumbnailName);
$stmt->execute();
if ($mysqli->affected_rows != 1) {
MySQL\Error::handle($stmt);
}
$stmt->close();
} else {
MySQL\Error::handle($mysqli);
}
$mediaId = MySQL\Connection::getInstance()->getLastInsertId();
return $mediaId;
}
set_exception_handler("offline_exception_handler");
//no buffer for command line.
ob_end_flush();
function write_csv_row($fhandle, $row)
{
if (empty($row["email"])) {
return;
}
$fname = empty($row["first_name"]) ? "_EMPTY_" : $row["first_name"];
$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);
static function updateIp($sessionId, $loginId, $remoteIp)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = " update sc_login set ip_address = ?, session_id = ? , updated_on = now() ";
$sql .= " where id = ? ";
$stmt = $mysqli->prepare($sql);
if ($stmt) {
$stmt->bind_param("ssi", $remoteIp, $sessionId, $loginId);
$stmt->execute();
if ($mysqli->affected_rows != 1) {
MySQL\Error::handle($stmt);
}
$stmt->close();
} else {
MySQL\Error::handle($mysqli);
}
}
function end()
{
//do not create a new instance
$mysql = \com\indigloo\mysql\Connection::getInstance(false);
if (!is_null($mysql)) {
$mysql->closeHandle();
}
if (Config::getInstance()->is_debug()) {
Logger::getInstance()->debug('web >> end >> hash is:: ' . spl_object_hash(self::$instance));
}
}
$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());
}
}
}
//this script is locked via site-worker.sh shell script
$mysqli = MySQL\Connection::getInstance()->getHandle();
process_sites($mysqli);
sleep(1);
process_groups($mysqli);
sleep(1);
process_mail_queue($mysqli);
sleep(1);
$session_backend = Config::getInstance()->get_value("session.backend");
$session_backend = empty($session_backend) ? "default" : strtolower($session_backend);
if (strcmp($session_backend, "mysql") == 0) {
remove_mysql_sessions();
}
sleep(1);
//control mails via sendgrid.mail.mode settings
process_activities($mysqli);
//release resources
static function glget($key)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = "select t_value from sc_glob_table where t_key = '%s'";
$sql = sprintf($sql, $key);
$row = MySQL\Helper::fetchRow($mysqli, $sql);
return $row;
}
static function updateTracker($postId, $version)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$stmt = $mysqli->prepare("CALL UPDATE_SITE_TRACKER(?,?)");
$stmt->bind_param("ii", $postId, $version);
$flag = $stmt->execute();
if (!$flag) {
MySQL\Error::handle($stmt);
}
$stmt->close();
}
static function delete($loginId, $listId)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = " delete from sc_list where id = ? and login_id = ?";
$stmt = $mysqli->prepare($sql);
if ($stmt) {
$stmt->bind_param("ii", $listId, $loginId);
$stmt->execute();
$stmt->close();
} else {
MySQL\Error::handle($mysqli);
}
}
static function remove($subjectId, $objectId, $verb)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = "delete from sc_bookmark where subject_id = ? and object_id = ? and verb = ? ";
$stmt = $mysqli->prepare($sql);
if ($stmt) {
$stmt->bind_param("iii", $subjectId, $objectId, $verb);
$stmt->execute();
$stmt->close();
} else {
MySQL\Error::handle($mysqli);
}
}
static function changePassword($tableName, $loginId, $email, $password)
{
if (empty($tableName)) {
trigger_error("User Table name is not supplied", E_USER_ERROR);
exit(1);
}
Util::isEmpty('Email', $email);
Util::isEmpty('Password', $password);
$mysqli = MySQL\Connection::getInstance()->getHandle();
// get random salt
$salt = substr(md5(uniqid(rand(), true)), 0, 8);
$password = trim($password);
$message = $password . $salt;
//create SHA-1 digest from email and password
// we store this digest in table
$digest = sha1($message);
$sql = " update {table} set updated_on=now(), salt=?, password=? where email = ? and login_id = ?";
$sql = str_replace("{table}", $tableName, $sql);
$stmt = $mysqli->prepare($sql);
if ($stmt) {
$stmt->bind_param("sssi", $salt, $digest, $email, $loginId);
$stmt->execute();
$stmt->close();
} else {
MySQL\Error::handle($mysqli);
}
}
static function set_tu_bit($userId, $value)
{
$mysqli = MySQL\Connection::getInstance()->getHandle();
$sql = "update sc_denorm_user set updated_on = now(), tu_bit = ? where id = ?";
$stmt = $mysqli->prepare($sql);
if ($stmt) {
$stmt->bind_param("ii", $value, $userId);
$stmt->execute();
if ($mysqli->affected_rows != 1) {
MySQL\Error::handle($stmt);
}
$stmt->close();
} else {
MySQL\Error::handle($mysqli);
}
}
