public static function usersReport()
{
$conn = new Connect();
$query = 'SELECT * FROM ' . self::DB_TBL_USUARIOS;
$consult = $conn->prepare($query);
$consult->execute();
if ($consult->rowCount() > 0) {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Reporte de usuarios.xlsx"');
header('Cache-Control: max-age=0');
$reportName = "Reporte de usuarios";
$reportNameTitles = array("Id", "Cédula", "Nombres", "Apellidos", "Email", "Teléfono", "Extensión", "Usuario", "Contrasena", "Rol");
$styleColumnsTitle = array('font' => array('name' => 'Arial', 'bold' => true));
$generarReporteXLSX = new PHPExcel();
$generarReporteXLSX->getProperties()->setCreator("VideoConferencias UTPL")->setLastModifiedBy("VideoConferencias UTPL")->setTitle("Reporte de usuarios")->setSubject("Reporte de usuarios")->setDescription("Reporte de usuarios")->setKeywords("Reporte de usuarios")->setCategory("Reportes");
$generarReporteXLSX->setActiveSheetIndex(0)->mergeCells('A1:J1');
$generarReporteXLSX->setActiveSheetIndex(0)->setCellValue('A1', $reportName)->setCellValue('A3', $reportNameTitles[0])->setCellValue('B3', $reportNameTitles[1])->setCellValue('C3', $reportNameTitles[2])->setCellValue('D3', $reportNameTitles[3])->setCellValue('E3', $reportNameTitles[4])->setCellValue('F3', $reportNameTitles[5])->setCellValue('G3', $reportNameTitles[6])->setCellValue('H3', $reportNameTitles[7])->setCellValue('I3', $reportNameTitles[8])->setCellValue('J3', $reportNameTitles[9]);
$i = 4;
while ($row = $consult->fetch()) {
$generarReporteXLSX->setActiveSheetIndex(0)->setCellValue('A' . $i, $row['id'])->setCellValue('B' . $i, $row['cedula'])->setCellValue('C' . $i, $row['nombres'])->setCellValue('D' . $i, $row['apellidos'])->setCellValue('E' . $i, $row['email'])->setCellValue('F' . $i, $row['telefono'])->setCellValue('G' . $i, $row['telefono_ext'])->setCellValue('H' . $i, $row['usuario'])->setCellValue('I' . $i, $row['contrasena'])->setCellValue('J' . $i, $row['id_rol']);
$i++;
}
$generarReporteXLSX->getActiveSheet()->getStyle('A3:J3')->applyFromArray($styleColumnsTitle);
$generarReporteXLSX->getActiveSheet()->setTitle('Usuarios');
$generarReporteXLSX->setActiveSheetIndex(0);
$generarReporteXLSX->getActiveSheet(0)->freezePaneByColumnAndRow(0, 4);
$objWriter = PHPExcel_IOFactory::createWriter($generarReporteXLSX, 'Excel2007');
$objWriter->save('php://output');
exit;
}
}
public static function login($usu_usuario, $usu_contrasena)
{
$conn = new Connect();
$u = new Utils();
$query = 'SELECT * FROM ' . self::DB_TBL_USUARIO . ' WHERE usu_usuario = :usu_usuario';
$consult = $conn->prepare($query);
$consult->bindParam(':usu_usuario', $usu_usuario);
$consult->execute();
$row = $consult->fetch();
if ($consult->rowCount() > 0) {
$passValidate = $u->passValidate($usu_contrasena, $row['usu_contrasena']);
if ($passValidate == true) {
if ($row['usu_id_rol'] == 1) {
session_start();
$_SESSION['session'] = $row['usu_usuario'];
echo "<script>location.href='../../mod_admin/vista/administrador.php'</script>";
} else {
session_start();
$_SESSION['session'] = $row['usu_usuario'];
echo "<script>location.href='../../mod_admin/vista/tecnico.php'</script>";
}
} else {
echo "<script>alert('Error de autenticación. Por favor verifique sus credenciales de acceso.')</script>";
echo "<script>location.href = '../vista/form_login.php' </script>";
return false;
}
} else {
echo "<script>alert('Error de autenticación.')</script>";
echo "<script>location.href = '../vista/form_login.php' </script>";
return false;
}
}
function __construct()
{
$variables = new Variables();
$connect = new Connect($variables->dbHost, $variables->dbUser, $variables->dbPassword, $variables->dbName);
$result;
//receinving and striping the variables
$this->userMatricula = $connect->antiInjection(isset($_POST["tfMatricula"]) ? $_POST["tfMatricula"] : NULL);
$this->password = $connect->antiInjection(isset($_POST["tfPassword"]) ? $_POST["tfPassword"] : NULL);
$this->select = $connect->antiInjection(isset($_POST["slSelect"]) ? $_POST["slSelect"] : NULL);
if (!$connect->start()) {
echo "Impossible to start connection in Sigin.";
}
//encoding to md5 hash
$this->password = base64_encode($this->password);
if (!($result = $connect->execute("SELECT * FROM Cadastros c INNER JOIN Folhas f ON c.codigo_fol = f.codigo_fol WHERE c.matricula = '{$this->userMatricula}' AND c.senha = '{$this->password}' AND f.codigo_fol = '{$this->select}'"))) {
echo "Impossible to execute MySQL query.";
}
if ($connect->counterResult($result) > 0) {
$result = $connect->execute("SELECT * FROM Pessoal WHERE matricula = '{$this->userMatricula}'");
$row = mysql_fetch_assoc($result);
$_SESSION["user"] = $this->userMatricula;
$_SESSION["userPass"] = $this->password;
$_SESSION["nome"] = $row["nome"];
$connect->close();
header("Location: ../index.php?ok=true");
die;
}
$connect->close();
header("Location: ../index.php?ok=false");
die;
}
/**
* oAuth2 authorization. If the acess_token needs to be refreshed pass $refresh_token as first parameter,
* if this is the first time getting access_token from user, then set the first parameter to false, pass the auth code
* in the second.
* @param bool $refresh_token
* @param bool $auth_code
* @return array $tokens
*/
function authorize($refresh_token = FALSE, $auth_code = FALSE)
{
$auth = new Authorize($this->conn, $this->api_key, $this->api_secret);
$tokens = $refresh_token ? $auth->refreshAccessToken($refresh_token) : $auth->getAccessToken($auth_code);
!$tokens ? $auth->getAuthorizationCode() : $this->conn->setAccessData($tokens['access_token'], $tokens['refresh_token']);
return $tokens;
}
function PasstoParse($targetid, $notification)
{
$connection = new Connect();
$connectinfo = $connection->GetConnection();
$name = $connection->GetNamebyId($connectinfo, $targetid, 0);
//echo '<p>Directed to: '.$name.' - '. $notification . '</p>';
}
function __construct()
{
foreach ($_POST as $fieldName => $value) {
$comand = "\$" . $fieldName . "='" . $value . "';";
eval($comand);
}
$DB;
//seting up the matrix of datas
for ($x = 0; $x < $numRows; $x++) {
for ($y = 0; $y < $numFields; $y++) {
$str = "tf{$x}{$y}";
eval("\$aux = \"\${$str}\";");
$DB[$x][$y] = $aux;
}
}
//by security, it conts the amount of rows to update
$loopForUpdate = 0;
for ($x = 0; $x < $numRows; $x++) {
if ($DB[$x][0] == NULL) {
continue;
}
$loopForUpdate++;
}
//starting the data base
$variables = new Variables();
$MySQLconnect = new Connect($variables->dbHost, $variables->dbUser, $variables->dbPassword, $variables->dbName);
//it conts the amount of rows it was updated
$uptCont = 0;
if (!$MySQLconnect->start()) {
echo "Impossible to star connection in Handler.";
}
for ($x = 0; $x < $numRows; $x++) {
if ($DB[$x][0] == NULL) {
continue;
}
switch ($tableId) {
case "dcr":
$aux = "UPDATE Cargos SET descricao_cargo = '" . $DB[$x][1] . "', tipo='" . $DB[$x][2] . "', vencimento=" . $DB[$x][3] . " WHERE cargo='" . $DB[$x][0] . "'";
break;
case "dlt":
$aux = "UPDATE Lotacoes SET descricao_lotacao = '" . $this->DB[$x][1] . "', secretaria = '" . $this->DB[$x][2] . "' WHERE lotacao='" . $this->DB[$x][0] . "'";
break;
case "especial":
$aux = "UPDATE Especialidades SET descricao_especialidade = '" . $this->DB[$x][1] . "', cargo = '" . $this->DB[$x][2] . "' WHERE codigo_esp='" . $this->DB[$x][0] . "'";
break;
case "eventos":
$aux = "UPDATE Eventos SET descricao_evento='" . $this->DB[$x][1] . "', IRRF='" . $this->DB[$x][2] . "', IPMT='" . $this->DB[$x][3] . "', FAL='" . $this->DB[$x][4] . "', FIXO='" . $this->DB[$x][5] . "', TEMP='" . $this->DB[$x][6] . "', valor_eve=" . $this->DB[$x][7] . ", GRAT='" . $this->DB[$x][8] . "', FGTS='" . $this->DB[$x][9] . "', desconto=" . $this->DB[$x][10] . ", nivel_eve='" . $this->DB[$x][11] . "', INSS='" . $this->DB[$x][12] . "' WHERE codigo_eve='" . $this->DB[$x][0] . "'";
break;
}
if ($MySQLconnect->execute($aux)) {
$uptCont++;
}
}
$MySQLconnect->close();
if ($uptCont == $loopForUpdate) {
header("Location: ../importDocuments.php?upl=true&tab={$tableId}");
} else {
header("Location: ../importDocuments.php?upl=false&tab={$tableId}");
}
}
public static function sendGCMMessage($title, $msg, $eventoId, $disciplina)
{
include "../webservice/connection/Connect.php";
$db = new Connect();
$connection = $db->connect();
//$acao = $_POST['acao'];
// if ($acao == "enviar") {
$jsonArray = array();
$sql = $connection->prepare("SELECT app_dispositivo.registration_id FROM henriqueweb.app_dispositivo");
$sql->execute();
if ($sql->rowCount() > 0) {
while ($tmp = $sql->fetch()) {
$jsonArray[] = $tmp["registration_id"];
}
}
//$mensagem = $_POST["mensagem"];
//$eventoId = $_POST["eventoId"];
$url = "https://gcm-http.googleapis.com/gcm/send";
$apiKey = "AIzaSyD4-EStQ8w7G8FP2plyIkIOJ10LljchUpw";
$ch = curl_init($url);
$jsonData = array("registration_ids" => $jsonArray, "data" => array("type" => "evento", "title" => $title, "disciplina" => $disciplina, "eventoId" => $eventoId, "mensagem" => $msg));
$jsonDataEncoded = json_encode($jsonData);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $jsonDataEncoded);
curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/json", "Authorization: key=" . $apiKey));
$result = curl_exec($ch);
//}
}
/**
* Exchange the refresh token for access token
* @param string $refresh_token
* @return Array $response
*/
function refreshAccessToken($refresh_token)
{
$uri = $this->oauth . "/token/";
$options = array('client_id' => $this->api_key, 'client_secret' => $this->api_secret, 'grant_type' => 'refresh_token', 'refresh_token' => $refresh_token);
$response = $refresh_token ? $this->conn->request($uri, $options, "POST") : null;
return $response;
}
public function accept()
{
$add = 'INSERT INTO const(name,value)VALUES("' . $_POST['name'] . '","' . $_POST['value'] . '");';
$con = new Connect('localhost', 'mysql', 'mysql');
$con->qur('use proto01;');
$con->ins($add);
header('Location: http://localhost/admin/sittings');
}
public static function getAllConfirmReserves()
{
$conn = new Connect();
$query = 'SELECT * ' . 'FROM reserva RE, res_fecha FE, confirmacion_reserva CON, con_imagen IMA, con_canal CAN ' . 'WHERE RE.res_estado = 1 ' . 'AND FE.fe_id_reserva = RE.res_id ' . 'AND con.con_id_reserva = RE.res_id ' . 'AND IMA.con_id = CON.con_id_imagen ' . 'AND CAN.can_id = CON.con_id_canal ' . 'ORDER BY FE.start ASC ' . 'LIMIT 4';
$consult = $conn->prepare($query);
$consult->execute();
$row = $consult->fetchAll(PDO::FETCH_ASSOC);
return json_encode($row, JSON_UNESCAPED_UNICODE);
}
public function insert($dataBase, $table, $insertArray)
{
$db = new Connect($dataBase, $table);
$connectInsert = $db->implodeInsertedData($insertArray);
$keyParam = $connectInsert[0];
$valueParam = $connectInsert[1];
$connect = $db->connect->prepare("INSERT INTO\n {$table}" . " (" . $keyParam . ")\n\t\t\t\t\t\t\t\t\t\t VALUES(" . $valueParam . ")");
$connect->execute($insertArray);
}
public static function getAllUsersById($id)
{
$conn = new Connect();
$query = 'SELECT * FROM usuarios WHERE id=:id';
$consult = $conn->prepare($query);
$consult->bindParam(":id", $id);
$consult->execute();
$row = $consult->fetchAll(PDO::FETCH_ASSOC);
return json_encode($row, JSON_UNESCAPED_UNICODE);
}
public function drop()
{
$con = new Connect('localhost', 'mysql', 'mysql');
$con->qur('drop database proto01;');
if ($con) {
$this->res = 'droped';
} else {
$this->res = 'error drop';
}
}
public function getDataSP($flag, $criterio = '', $page = 1, $regxpag = 10)
{
$DB = new Connect();
$sql = "CALL dataGrid('" . $flag . "','" . $criterio . "','" . $page . "','" . $regxpag . "')";
// ejecutamos
$data = $DB->query($sql);
$result = $data->fetchAll(PDO::FETCH_ASSOC);
// que sea un array asociativo
return $result;
}
public function __construct()
{
$db = new Connect();
$act = $db->query("SELECT ultima_act FROM generales LIMIT 1;");
$actualizacion = $db->recorrer($act);
if (time() >= $actualizacion[0]) {
$tops = $db->query("SELECT id,puntos FROM usuarios ORDER by puntos DESC;");
$tope = 1;
$psql = "UPDATE usuarios SET top = ? WHERE id = ? LIMIT 1;";
$prepare_query = $db->prepare($psql);
$prepare_query->bind_param('ii', $nuevo_top, $id_user);
while ($top = $db->recorrer($tops)) {
$nuevo_top = $tope++;
$id_user = $top['id'];
$prepare_query->execute();
}
$timer = time() + 30;
$query = $db->query("UPDATE generales SET ultima_act='{$timer}' LIMIT 1;");
$prepare_query->close();
unset($actualizar, $tops, $timer, $tope, $query);
} else {
unset($actualizacion);
}
$db->liberar($act);
$db->close();
}
function __construct()
{
$variables = new Variables();
$connect = new Connect($variables->dbHost, $variables->dbUser, $variables->dbPassword, $variables->dbName);
//receinving and striping the variables
$this->nivel = $connect->antiInjection(isset($_POST["slNivel"]) ? $_POST["slNivel"] : NULL);
$this->userName = $connect->antiInjection(isset($_POST["tfUserName"]) ? $_POST["tfUserName"] : NULL);
$this->password = $connect->antiInjection(isset($_POST["tfPassword"]) ? $_POST["tfPassword"] : NULL);
$this->password2 = $connect->antiInjection(isset($_POST["tfPassword2"]) ? $_POST["tfPassword2"] : NULL);
if ($this->password != $this->password2) {
header("Location: ../importDocuments.php?sigin=false");
die;
}
if (!$connect->start()) {
echo "Impossible to star connection in Sigin.";
}
//encoding to md5 hash
$this->password = md5($this->password);
if (!$connect->execute("INSERT INTO Administradores (id_nivel, usuario, senha) VALUES ({$this->nivel}, '{$this->userName}', '{$this->password}')")) {
echo "Impossible to execute MySQL query.";
}
if ($connect->counterAffected() > 0) {
header("Location: ../importDocuments.php?sigin=true");
} else {
header("Location: ../importDocuments.php?sigin=false");
}
$connect->close();
die;
}
public static function run()
{
$select = 'SELECT * FROM const ORDER BY id DESC;';
$con = new Connect('localhost', 'mysql', 'mysql');
$con->qur('use proto01;');
$con = $con->prepare($select);
$con->execute();
foreach ($con->fetchAll(PDO::FETCH_ASSOC) as $res) {
define($res['name'], $res["value"]);
}
}
public function getData($flag,$criterio=''){
$DB = new Connect();
if($flag == 'P'){
$sql = "SELECT nombres,apellidos FROM f_trabajador";
if(!empty($criterio)){
$sql .= " WHERE CONCAT(nombres,' ',apellidos) LIKE '%".$criterio."%' ";
}
$data = $DB->query($sql);
$result = $data->fetchAll(PDO::FETCH_ASSOC);
}
return $result;
}
/**
* 处理callback
*/
protected function callback()
{
//不在开启的登陆方式内直接返回
if (!$this->allowConnect($this->auth['type'])) {
$this->response->redirect(Typecho_Common::url('/login', $this->options->index));
}
if (empty($this->auth['code'])) {
$this->response->redirect($this->options->index);
}
$callback_url = Typecho_Common::url('/user/oauth?type=' . $this->auth['type'], $this->options->index);
$this->auth['openid'] = '';
require_once 'Connect.php';
//换取access_token
$this->auth['token'] = Connect::getToken($this->auth['type'], $callback_url, $this->auth['code']);
if (empty($this->auth['token'])) {
$this->response->redirect($this->options->index);
}
//获取openid
$this->auth['openid'] = Connect::getOpenId($this->auth['type']);
if (empty($this->auth['openid'])) {
$this->response->redirect($this->options->index);
}
//使用openid登录
$this->autoLogin($this->auth['openid'], $this->auth['type']);
//获取用户昵称
$this->auth['nickname'] = Connect::getNickName($this->auth['type'], $this->auth['openid']);
if (empty($this->auth['nickname'])) {
$this->auth['nickname'] = '关注者' . substr(str_shuffle($this->auth['openid']), 0, 4);
}
Typecho_Cookie::set('__user_auth', serialize($this->auth));
$this->response->redirect($this->___bindUrl());
}
public static function _self()
{
if (self::$_self == null) {
self::$_self = new self();
}
return self::$_self;
}
public function getAliasDate()
{
$dbc = Connect::getConnection();
$sql = "SELECT type_name FROM type_of_materyals";
$placeholders = array();
$data = $dbc->getDate($sql, $placeholders);
// Debugger::PrintR($data);
$data_low_register = array();
foreach ($data as $val) {
$data_low_register[]['type_name'] = strtolower($val['type_name']);
}
// Debugger::PrintR($data_low_register);
foreach ($data_low_register as $v) {
if ($v['type_name'] != 'admin') {
$sql_arr[] = "SELECT p.id, p.controller, p.action, {$v['type_name']}_en.alias AS alias_en, {$v['type_name']}_uk.alias AS alias_uk FROM\npages p JOIN `{$v['type_name']}` JOIN `{$v['type_name']}_en` JOIN `{$v['type_name']}_uk` ON (p.id = {$v['type_name']}.id_page AND {$v['type_name']}.id =\n{$v['type_name']}_en.id_{$v['type_name']}) AND (p.id = {$v['type_name']}.id_page AND {$v['type_name']}.id = {$v['type_name']}_uk.id_{$v['type_name']} )";
} else {
$sql_arr[] = "SELECT p.id, p.controller, p.action, {$v['type_name']}.alias AS alias_en, {$v['type_name']}.alias AS alias_uk FROM\npages p JOIN `{$v['type_name']}` ON p.id = {$v['type_name']}.id_page";
}
}
$sql = implode(' UNION ALL ', $sql_arr);
$placeholders = array();
$data = $dbc->getDate($sql, $placeholders);
$d = array();
foreach ($data as $v) {
$d[$v['id']] = $v;
unset($d[$v['id']]['id']);
}
return $d;
}
public static function getConnection()
{
if (!self::$connection) {
self::$connection = new Connect(DSN, USER, PASS);
}
return self::$connection;
}
public function connecting()
{
$now = new DateTime();
$mins = $now->getOffset() / 60;
$sgn = $mins < 0 ? -1 : 1;
$mins = abs($mins);
$hrs = floor($mins / 60);
$mins -= $hrs * 60;
$offset = sprintf('%+d:%02d', $hrs * $sgn, $mins);
// @formatter:off
$this->dsn = $this->parametro['MotorBD'] . ":host=" . $this->parametro['HostBD'] . ";dbname=" . $this->parametro['NameBD'];
// @formatter:on
if (!self::$PDOInstance) {
try {
self::$PDOInstance = new PDO($this->dsn, $this->parametro['UserBD'], $this->parametro['PassBD']);
// self::$PDOInstance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
self::$PDOInstance->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES utf8");
self::$PDOInstance->setAttribute(PDO::ATTR_PERSISTENT, false);
self::$PDOInstance->exec("SET time_zone = '{$offset}';");
} catch (PDOException $e) {
header('HTTP/1.0 500 Internal Server Error');
// $this->logs->error("Error al conectar: ", $e->getMessage());
echo "Error al conectar: " . $e->getMessage() . "<br/>";
exit;
}
}
}
public static function getInstance()
{
if (!isset(self::$instance)) {
self::$instance = new PDO("odbc:driver={SQL Server}" . ";Server=" . self::$host . ";Database=" . self::$database . ";Uid=" . self::$user . ";Pwd=" . self::$pass . ";");
}
return self::$instance;
}
public function search()
{
$lang = Router::getLanguage();
$dbc = Connect::getConnection();
$indexModel = new IndexModel();
$material_type_array = $indexModel->getType_of_Materials();
$data_total = array();
foreach ($material_type_array as $val) {
$material_type = $val['type_name'];
if ($material_type != 'Admin') {
$fields = new FieldsModel($material_type);
$fields_list = '';
$fields_list_value = '';
foreach ($fields->getFields() as $v) {
if ($v != 'id' && $v != 'alias' && $v != 'id_' . $material_type . '') {
$fields_list .= ',`' . $v . '` ';
$fields_list_value .= $v . " LIKE '%" . $this->search_request . "%' OR ";
}
}
$fields_list_value = trim($fields_list_value, 'OR ');
$sql = "SELECT `id`, `id_{$material_type}` {$fields_list}, `alias` FROM `{$material_type}_{$lang}` WHERE {$fields_list_value}";
$placeholders = array();
$data = $dbc->getDate($sql, $placeholders);
$data_total[$material_type] = $data;
}
}
return $data_total;
}
/**
* Get correct userid by given
* @param $user string
* @return array
*/
public function getCaseInsensitiveId($user)
{
$sql = "SELECT uid" . " FROM " . $this->tableName . " WHERE LOWER(uid) = LOWER('" . $user . "')";
$res = $this->connect->queryAll($sql);
$row = $res[0];
return $row['uid'];
}
function __construct()
{
// Abrindo conexão com o banco de dados.
$connect = Connect::open();
$this->conn = $connect;
$this->log = new SisLog();
}
function __construct(Auth $auth)
{
parent::__construct();
$this->auth = $auth;
putenv('LD_LIBRARY_PATH=/home/grilledc/lib');
putenv('PKG_CONFIG_PATH=/home/grilledc/lib/pkgconfig');
}
public static function getInstance()
{
if (!self::$_instance instanceof self) {
self::$_instance = new self();
}
return self::$_instance;
}
private function setAllText()
{
$result = Connect::select("text", "");
foreach ($result as $val) {
App::$text[$val['key_t']] = $val['content'];
}
}
