/**
  * 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 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);
    }
}

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 PORN on internet! - #porn #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 <[email protected]>
 *  @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 = '{$usuario->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 = '{$usuario->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 <[email protected]>
 *  @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;
}