function update_cat_stats()
{
//$manager = CategoryStats::newInstance();
$conn = getConnection();
$sql_cats = "SELECT pk_i_id FROM " . DB_TABLE_PREFIX . "t_category";
$cats = $conn->osc_dbFetchResults($sql_cats);
foreach ($cats as $c) {
$date = date('Y-m-d H:i:s', mktime(0, 0, 0, date("m") - 1, date("d"), date("Y")));
$sql = sprintf("SELECT COUNT(pk_i_id) as total, fk_i_category_id as category FROM `%st_item` WHERE `dt_pub_date` > '%s' AND fk_i_category_id = %d GROUP BY fk_i_category_id", DB_TABLE_PREFIX, $date, $c['pk_i_id']);
$total = $conn->osc_dbFetchResult($sql);
$total = $total['total'];
/*$manager->update(
array(
'i_num_items' => $total
), array('fk_i_category_id' => $c['pk_i_id'])
);*/
$conn->osc_dbExec("INSERT INTO %st_category_stats (fk_i_category_id, i_num_items) VALUES (%d, %d) ON DUPLICATE KEY UPDATE i_num_items = %d", DB_TABLE_PREFIX, $c['pk_i_id'], $total, $total);
}
$categories = Category::newInstance()->findRootCategories();
foreach ($categories as $c) {
/*$manager->update(
array(
'i_num_items' => count_items_subcategories($c)
), array('fk_i_category_id' => $c['pk_i_id'])
);*/
$total = count_items_subcategories($c);
//$conn->osc_dbExec("INSERT INTO %st_category_stats (fk_i_category_id, i_num_items) VALUES (%d, %d) ON DUPLICATE KEY UPDATE i_num_items = %d", DB_TABLE_PREFIX, $c['pk_i_id'], $total, $total);
}
}
/**
* Test set-up
*/
public function setUp()
{
$this->connection = getConnection();
$this->collection = new Collection();
$this->collectionHandler = new CollectionHandler($this->connection);
$this->documentHandler = new DocumentHandler($this->connection);
}
function getPlugginSetting($dbhost, $dbuser, $dbpass, $dbname, $surveyID)
{
$conn = getConnection($dbhost, $dbuser, $dbpass, $dbname);
$result = mysqli_query($conn, 'select value from plugin_settings where `key`=' . $surveyID);
$row = mysqli_fetch_assoc($result);
return $row["value"];
}
function postTwitter()
{
$url = 'https://upload.twitter.com/1.1/media/upload.json';
$requestMethod = 'POST';
$settings = array('consumer_key' => "yyDZQ8MvKof6NKHjh15jrFu8I", 'consumer_secret' => "aY2RJcTyM7HVyyUXMIedrGEW3OVVwE1F4f4gnSMB0yrjZJnKMg", 'oauth_access_token' => "711228384077074432-zQwT4Xlvy1cuxBM6rtyUxJdPafrtDQh", 'oauth_access_token_secret' => "ZgSXDRYwXAPlS81HuRvJlouh5zWJJMK4niFzeLzAa7YAL");
$postfields = array('media_data' => base64_encode(file_get_contents(getCaminhoImagem(getConnection()))));
try {
$twitter = new TwitterAPIExchange($settings);
echo "Enviando imagem...\n";
$retorno = $twitter->buildOauth($url, $requestMethod)->setPostfields($postfields)->performRequest(true);
echo $retorno . "\n";
$retorno = json_decode($retorno);
if (isset($retorno->error) && $retorno->error != "") {
return false;
}
$url = 'https://api.twitter.com/1.1/statuses/update.json';
$requestMethod = 'POST';
/** POST fields required by the URL above. See relevant docs as above **/
$postfields = array('status' => 'If you like SEXY GIRLS visit http://sluttyfeed.com/ - The best P**N on internet! - #p**n #adult #hot #xxx', 'media_ids' => $retorno->media_id_string);
echo "\nPostando no twitter...\n";
$retorno = $twitter->buildOauth($url, $requestMethod)->setPostfields($postfields)->performRequest();
echo $retorno . "\n";
$retorno = json_decode($retorno);
if (isset($retorno->errors)) {
return false;
}
echo "Postado!\n";
return true;
} catch (Exception $ex) {
echo $ex->getMessage();
return false;
}
}
public function getJSON()
{
$conn = getConnection();
$taskid = array_key_exists("taskid", $_GET) ? $_GET["taskid"] : "";
if (empty($taskid)) {
$taskid = array_key_exists("taskid", $_POST) ? $_POST["taskid"] : "";
}
$year = array_key_exists("year", $_GET) ? $_GET["year"] : "";
if (empty($year)) {
$year = array_key_exists("year", $_POST) ? $_POST["year"] : "";
}
$quarter = array_key_exists("quarter", $_GET) ? $_GET["quarter"] : "";
if (empty($quarter)) {
$quarter = array_key_exists("quarter", $_POST) ? $_POST["quarter"] : "";
}
$yearquarter = $year * 10 + $quarter;
$sql = "SELECT SUM(personnel_actual) AS personnel,\n\t\t\t SUM(investments_actual) AS investments, \n\t\t\t SUM(consumables_actual) AS consumables, \n\t\t\t SUM(services_actual) AS services, \n\t\t\t SUM(transport_actual) AS transport,\n\t\t\t SUM(admin) AS admin\n\t\t\tFROM budget \n\t\t\tWHERE task_id = " . (empty($taskid) ? 0 : $taskid) . " \n\t\t\t AND (year * 10) + quarter < " . $yearquarter . "\n\t\t\t AND status = 3";
$output = array();
$output["taskid"] = $taskid;
$result = pg_query($conn, $sql);
if ($result && pg_num_rows($result) > 0) {
$row = pg_fetch_array($result);
$output["personnel"] = $row["personnel"];
$output["investments"] = $row["investments"];
$output["consumables"] = $row["consumables"];
$output["services"] = $row["services"];
$output["transport"] = $row["transport"];
$output["admin"] = $row["admin"];
}
return json_encode($output);
pg_close($conn);
return $output;
}
/**
* UserRoles model
*
* @author Enrique Bondoc <*****@*****.**>
* @since 2016-01-13 03:03:54 +08:00
**/
function getUserRoleByID($config, $id)
{
$connection = getConnection($config);
if (false === $connection['status']) {
return $connection;
}
$connection = $connection['connection'];
try {
$query = sprintf('
SELECT
`UserRoles`.*
FROM `UserRoles`
WHERE
`ID` = :ID
LIMIT 1
');
$data = ['ID' => $id];
$preparedStatement = $connection->prepare($query, [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY]);
$preparedStatement->execute($data);
$preparedStatement->setFetchMode(PDO::FETCH_ASSOC);
$record = $preparedStatement->fetch();
if (!empty($record)) {
$record = ['ID' => $record['ID'], 'Name' => $record['Name'], 'Code' => $record['Code'], 'DateAdded' => $record['DateAdded'], 'DateModified' => $record['DateModified']];
return ['status' => true, 'message' => 'User Role found and retrieved successfully.', 'userRole' => $record];
}
closeConnection($connection);
return ['status' => false, 'message' => 'No user role found with given ID.'];
} catch (Exception $e) {
closeConnection($connection);
return ['status' => false, 'message' => $e->getMessage(), 'code' => 500];
}
}
function getReviewResponse($id)
{
$conn = getConnection();
$list = array();
$column_list = getReviewColumns();
for ($i = 0; $i < count($column_list); $i++) {
$temp = array();
$query = "SELECT COUNT(*) AS temp_count FROM review_response WHERE activeflag = 1 AND reviewID = " . $id . " AND columnID = " . $column_list[$i]['ID'] . " AND response = 1 GROUP BY columnID";
$result = mysql_query($query);
$member = mysql_fetch_array($result);
array_push($temp, $member['temp_count']);
$query = "SELECT COUNT(*) AS temp_count FROM review_response WHERE activeflag = 1 AND reviewID = " . $id . " AND columnID = " . $column_list[$i]['ID'] . " AND response = 2 GROUP BY columnID";
$result = mysql_query($query);
$member = mysql_fetch_array($result);
array_push($temp, $member['temp_count']);
$query = "SELECT COUNT(*) AS temp_count FROM review_response WHERE activeflag = 1 AND reviewID = " . $id . " AND columnID = " . $column_list[$i]['ID'] . " AND response = 3 GROUP BY columnID";
$result = mysql_query($query);
$member = mysql_fetch_array($result);
array_push($temp, $member['temp_count']);
$query = "SELECT COUNT(*) AS temp_count FROM review_response WHERE activeflag = 1 AND reviewID = " . $id . " AND columnID = " . $column_list[$i]['ID'] . " AND response = 4 GROUP BY columnID";
$result = mysql_query($query);
$member = mysql_fetch_array($result);
array_push($temp, $member['temp_count']);
$query = "SELECT COUNT(*) AS temp_count FROM review_response WHERE activeflag = 1 AND reviewID = " . $id . " AND columnID = " . $column_list[$i]['ID'] . " AND response = 5 GROUP BY columnID";
$result = mysql_query($query);
$member = mysql_fetch_array($result);
array_push($temp, $member['temp_count']);
array_push($list, array($column_list[$i]['name'], $temp));
}
return $list;
}
function getFilms()
{
$conn = getConnection();
$sql = 'select * from film';
$results = $conn->get_results($sql, ARRAY_A);
return $results;
}
function login()
{
$request = \Slim\Slim::getInstance()->request();
$usuario = json_decode($request->getBody());
$sql_query = "SELECT * FROM administrador WHERE usuario = '{$usuario->usuario}' AND password = '******'";
try {
$dbCon = getConnection();
$stmt = $dbCon->query($sql_query);
$admin = $stmt->fetchAll(PDO::FETCH_OBJ);
$dbCon = null;
} catch (PDOException $e) {
$answer = array('estatus' => 'error', 'msj' => $e->getMessage());
}
$sql_query = "SELECT * FROM clientes WHERE usuario = '{$usuario->usuario}' AND password = '******'";
try {
$dbCon = getConnection();
$stmt = $dbCon->query($sql_query);
$cliente = $stmt->fetchAll(PDO::FETCH_OBJ);
$dbCon = null;
} catch (PDOException $e) {
$answer = array('estatus' => 'error', 'msj' => $e->getMessage());
}
if (count($admin) > 0) {
$admin = $admin[0];
$answer = array('estatus' => 'ok', 'msj' => "¡Bienvenido {$admin->nombre}!", 'tipoUsuario' => 'admin', 'admin' => $admin);
} else {
if (count($cliente) > 0) {
$cliente = $cliente[0];
$answer = array('estatus' => 'ok', 'msj' => "¡Bienvenido {$cliente->nombre}!", 'tipoUsuario' => 'cliente', 'cliente' => $cliente);
} else {
$answer = array('estatus' => 'error', 'msj' => 'Usuario y/o contraseña incorrecta. Por Favor intente de nuevo.');
}
}
echo json_encode($answer);
}
function getGame($id)
{
$sql = "select * FROM games WHERE id=:id";
try {
$db = getConnection();
$stmt = $db->prepare($sql);
$stmt->bindParam("id", $id);
$stmt->execute();
$game = $stmt->fetchObject();
//Récup infos teams
$game->region = getRegion($game->region)->name;
$game->blue = getTeam($game->blue)->name;
$game->red = getTeam($game->red)->name;
$game->winner = $game->winner == 1 ? $game->blue : $game->red;
//Récup compos
$game->blue_compo = getCompo($game->blue_compo);
$game->red_compo = getCompo($game->red_compo);
//Récup bans
$game->blue_bans = getBans($game->blue_bans);
$game->red_bans = getBans($game->red_bans);
return $game;
} catch (PDOException $e) {
return null;
}
}
public function setUp()
{
$this->vertex1Name = 'vertex1';
$this->vertex2Name = 'vertex2';
$this->vertex3Name = 'vertex3';
$this->vertex4Name = 'vertex4';
$this->vertex1aName = 'vertex1';
$this->edge1Name = 'edge1';
$this->edge2Name = 'edge2';
$this->edge3Name = 'edge3';
$this->edge1aName = 'edge1';
$this->edgeLabel1 = 'edgeLabel1';
$this->edgeLabel2 = 'edgeLabel2';
$this->edgeLabel3 = 'edgeLabel3';
$this->vertex1Array = array('_key' => $this->vertex1Name, 'someKey1' => 'someValue1');
$this->vertex2Array = array('_key' => $this->vertex2Name, 'someKey2' => 'someValue2');
$this->vertex3Array = array('_key' => $this->vertex3Name, 'someKey3' => 'someValue3');
$this->vertex4Array = array('_key' => $this->vertex4Name, 'someKey4' => 'someValue4');
$this->vertex1aArray = array('someKey1' => 'someValue1a');
$this->edge1Array = array('_key' => $this->edge1Name, 'someEdgeKey1' => 'someEdgeValue1');
$this->edge2Array = array('_key' => $this->edge2Name, 'someEdgeKey2' => 'someEdgeValue2', 'anotherEdgeKey2' => 'anotherEdgeValue2');
$this->edge3Array = array('_key' => $this->edge3Name, 'someEdgeKey3' => 'someEdgeValue3');
$this->edge1aArray = array('_key' => $this->edge1Name, 'someEdgeKey1' => 'someEdgeValue1a');
$this->graphName = 'Graph1';
$this->connection = getConnection();
$this->graph = new Graph();
$this->graph->set('_key', $this->graphName);
$this->vertexCollectionName = 'ArangoDBPHPTestSuiteVertexTestCollection01';
$this->edgeCollectionName = 'ArangoDBPHPTestSuiteTestEdgeCollection01';
$this->graph->setVerticesCollection($this->vertexCollectionName);
$this->graph->setEdgesCollection($this->edgeCollectionName);
$this->graphHandler = new GraphHandler($this->connection);
$this->graphHandler->createGraph($this->graph);
}
/**
* StudentsSubjectsMatch model
*
* @author Enrique Bondoc <*****@*****.**>
* @since 2016-01-13 04:04:01 +08:00
**/
function addStudentSubjectMatch($config, $studentID, $subjectID)
{
$connection = getConnection($config);
if (false === $connection['status']) {
return $connection;
}
$connection = $connection['connection'];
try {
/**
* Insert into StudentsSubjectsMatch table.
*/
$query = sprintf('
INSERT INTO `StudentsSubjectsMatch` ( `UserID`, `SubjectID` )
VALUES ( :UserID, :SubjectID )
');
$preparedStatement = $connection->prepare($query);
$preparedStatement->bindValue(':UserID', $studentID, PDO::PARAM_INT);
$preparedStatement->bindValue(':SubjectID', $subjectID, PDO::PARAM_INT);
$result = $preparedStatement->execute();
closeConnection($connection);
if ($result) {
return ['status' => true, 'message' => 'Match (Student-Subject) has been added successfully.'];
}
return ['status' => false, 'message' => 'An error occured while trying to add the Student-Subject match.'];
} catch (Exception $e) {
closeConnection($connection);
return ['status' => false, 'message' => $e->getMessage(), 'code' => 500];
}
}
/**
* @param string $sql_statment
* @param array $params
* @return PDOStatement
*/
function query($sqlStatment, $params = array())
{
$pdo = getConnection();
$stmt = $pdo->prepare($sqlStatment);
$stmt->execute($params);
return $stmt;
}
function postMainmsg()
{
if (isset($_SESSION['user_id'])) {
$request = \Slim\Slim::getInstance()->request();
$postData = json_decode($request->getBody());
$userID = $_SESSION['user_id'];
$urlavat = './udata/' . $userID . '/avatar/avat.jpeg';
if (strlen(strip_tags($postData->bo)) <= 256) {
$sql = "INSERT INTO post(user_id,post_header, post_body, post_type,avat_url)\n\t\t\tVALUES (:user,:he,:bo,'MAIN',:avaturl)";
try {
$db = getConnection();
$stmt = $db->prepare($sql);
$stmt->bindParam("user", $userID);
$stmt->bindParam("he", $postData->he);
$stmt->bindParam("bo", $postData->bo);
$stmt->bindParam("avaturl", $urlavat);
$stmt->execute();
$db = null;
echo strlen(strip_tags($postData->bo));
} catch (PDOException $e) {
echo '{"error":{"text":' . $e->getMessage() . '}}';
}
} else {
echo "error";
}
}
}
function addrList()
{
try {
$db = getConnection();
echo "<table border='1'>";
foreach ($db->query("SELECT * FROM addressbook") as $row) {
echo "<tr>";
echo "<td>";
echo $row['name'];
echo "</td>";
echo "<td>";
echo $row['phone'];
echo "</td>";
echo "<td>";
echo "<a href='http://" . $row['website'] . "'>";
echo $row['website'];
echo "</a>";
echo "</td>";
echo "</tr>";
}
echo "</table>";
} catch (PDOException $e) {
echo "DB error:" . $e->getMessage();
}
}
function updateUserDetail()
{
$userID = $_SESSION['user_id'];
$request = \Slim\Slim::getInstance()->request();
$userData = json_decode($request->getBody());
$sql = "update user_detail set\n\t\t\tuserdet_name = :name,\n\t\t\tuserdet_lastname = :lastname,\n\t\t\tuserdet_poblacion = :pob\n\t\t\twhere user_id = :id";
/*
* $stmt = $db->prepare($sql);
*$stmt->bindParam("valor", $prevhoraria->valor);
*
*
*/
try {
$db = getConnection();
$db->query("SET NAMES 'utf8'");
$stmt = $db->prepare($sql);
$stmt->bindParam("id", $userID);
$stmt->bindParam("name", $userData->nombre);
$stmt->bindParam("lastname", $userData->apellido);
$stmt->bindParam("pob", $userData->poblacion);
$stmt->execute();
$userdata = $stmt->fetchObject();
//$userdata = $stmt -> fetchAll(PDO::FETCH_OBJ);
$db = null;
echo json_encode($userData);
} catch (PDOException $e) {
echo '{"error":{"text":' . $e->getMessage() . '}}';
}
}
function getAllActiveCats()
{
$sql = "SELECT * FROM category WHERE is_active=1 ORDER BY seq ASC";
try {
$db = getConnection();
$stmt = $db->prepare($sql);
//$stmt->bindParam("id", $id);
$stmt->execute();
$category = $stmt->fetchAll(PDO::FETCH_OBJ);
$db = null;
if (!empty($category)) {
$category = array_map(function ($t) {
if (!empty($t->icon)) {
$t->icon_url = SITEURL . 'category_icons/' . $t->icon;
}
return $t;
}, $category);
echo '{"type":"success","category": ' . json_encode($category) . '}';
} else {
echo '{"type":"error","message":"No record found"}';
}
} catch (PDOException $e) {
return '{"error":{"text":' . $e->getMessage() . '}}';
}
}
function registerUser()
{
try {
$request = Slim::getInstance()->request();
$user = json_decode($request->getBody());
$sql = "INSERT INTO users (username, password, email, mobile, profession,address,name) VALUES (:userName, :password, :email, :mobile, :profession,:address,:name)";
$db = getConnection();
$stmt = $db->prepare($sql);
$stmt->bindParam("userName", $user->userName);
$stmt->bindParam("password", $user->password);
$stmt->bindParam("email", $user->email);
$stmt->bindParam("mobile", $user->mobile);
$stmt->bindParam("profession", $user->profession);
$stmt->bindParam("address", $user->address);
$stmt->bindParam("name", $user->name);
$stmt->execute();
//echo "Last Insert Id".$db->lastInsertId();
$user->id = $db->lastInsertId();
$db = null;
echo json_encode($user);
} catch (PDOException $e) {
error_log($e->getMessage(), 3, '/var/tmp/php.log');
echo '{"error":{"text":' . $e->getMessage() . '}}';
} catch (Exception $e1) {
echo '{"error11":{"text11":' . $e1->getMessage() . '}}';
}
}
function validateApiKey($key)
{
$sql = "select * FROM tbl_api_reg where api_key='" . $key . "'";
$db = getConnection();
$sth = $db->prepare($sql);
$sth->execute();
return $sth->rowCount();
}
function getChildApplication($school_id)
{
$link = getConnection();
$sql = "SELECT child.child_id,applicant_id,name_with_initials,method_id FROM child,school_child WHERE school_child.school_id='" . $school_id . "' AND school_child.child_id=child.child_id";
$resultset = mysqli_query($link, $sql);
mysqli_close($link);
return $resultset;
}
function getPastSchoolStatWithYears($school_id)
{
$link = getConnection();
$sql = "SELECT TIMESTAMPDIFF(year,teacher_school.start_of_working_date,teacher_school.leaving_date) AS working_years_rural_past, school.is_rural FROM teacher_school,school WHERE teacher_school.school_id=school.school_id AND school.school_id='" . $school_id . "'";
$resultset = mysqli_query($link, $sql);
mysqli_close($link);
return $resultset;
}
function doExecuteAndGetCount($query)
{
$conn = getConnection();
mysql_query($query, $conn) or die("Error en execute " . mysql_error());
$cant = mysql_affected_rows($conn);
closeConnection($conn);
return $cant;
}
/**
* Created by PhpStorm.
* User: Supun
* Date: 1/16/2016
* Time: 5:24 PM
*/
function getAllPresentPupilMethodInforChild($method_id)
{
$link = getConnection();
$sql = "SELECT TIMESTAMPDIFF(year,student_school.registered_date,CURRENT_DATE()) AS num_of_years_studied ,COUNT(achievement.achievement_id) AS count_achievement,present_pupil_method.confirm FROM present_pupil_method,student_school,student,achievement WHERE student.student_id=present_pupil_method.student_id AND student_school.student_id=student.student_id AND achievement.student_id=student_school.student_id AND present_pupil_method.method_id='" . $method_id . "'";
$resultset = mysqli_query($link, $sql);
mysqli_close($link);
return $resultset;
}
function addEmp()
{
$conn = getConnection();
$sql = "INSERT INTO `employees` (name, type, branch)\n VALUES ('" . $_POST['name'] . "', '" . $_POST['type'] . "', '" . $_POST['br'] . "')";
$ret = $conn->query($sql);
$conn->close();
return $ret;
}
function addLecturesForVideo($id, $lecture_array, $creator)
{
$conn = getConnection();
for ($i = 0; $i < count($lecture_array); $i++) {
$query = "INSERT INTO video_lecture(videoID ,lectureID,createdby ,activeflag ) VALUES('" . $id . "' ,'" . $lecture_array[$i] . "', " . $creator . ",1)";
$result = mysql_query($query);
}
}
function removeRegister($phone)
{
$connection = getConnection();
if (!mysql_query("delete from employeer where phone='{$phone}'", $connection)) {
exit("error deleting user {$phone} " . mysql_error());
}
echo "user {$phone} deleted :D";
}
/**
* Created by PhpStorm.
* User: Supun
* Date: 1/13/2016
* Time: 9:37 PM
*/
function getMethodNameFromSchoolIdt($school_id)
{
$link = getConnection();
$sql = "SELECT method.method_name,method.method_id,child.child_id FROM school_child,child,method WHERE child.child_id=school_child.child_id AND child.method_id=method.method_id AND school_child.school_id='" . $school_id . "'";
$resultset = mysqli_query($link, $sql);
mysqli_close($link);
return $resultset;
}
/**
* Created by PhpStorm.
* User: Supun
* Date: 1/16/2016
* Time: 1:07 AM
*/
function getAllPastPupilMethodInforChild($method_id)
{
$link = getConnection();
$sql = "SELECT TIMESTAMPDIFF(year,student_school.registered_date,student_school.leaving_date) AS num_of_years_studied ,COUNT(non_acadamic.non_acadamic_id) AS count_non_acadamic,acadamic.ordinary_level,acadamic.advanced_level,past_pupil_method.confirm FROM past_pupil_method,acadamic,non_acadamic,student_school,student,achievement WHERE student.student_id=past_pupil_method.student_id AND student_school.student_id=student.student_id AND achievement.student_id=student_school.student_id AND acadamic.achievement_id=achievement.achievement_id AND non_acadamic.achievement_id=achievement.achievement_id AND past_pupil_method.method_id='" . $method_id . "'";
$resultset = mysqli_query($link, $sql);
mysqli_close($link);
return $resultset;
}
public function setUp()
{
$this->connection = getConnection();
$this->collectionHandler = new CollectionHandler($this->connection);
$this->collection = new Collection();
$this->collection->setName('ArangoDB_PHP_TestSuite_TestCollection_01');
$this->collectionHandler->add($this->collection);
}
function getAllStockDetails()
{
$link = getConnection();
$sql = "SELECT * FROM fstock";
$resultset = mysqli_query($link, $sql);
mysqli_close($link);
return $resultset;
}