/**
* Assign values to the database columns
* @param $column Name of the database column
* @param $value Value for the database column
*/
public function setRowData($column, $value)
{
// get the current connection
$conn = TTransaction::get();
// store just scalar values (string, integer, ...)
if (is_scalar($value)) {
// if is a string
if (is_string($value) and !empty($value)) {
// fill an array indexed by the column names
$this->columnValues[$column] = $conn->quote($value);
} else {
if (is_bool($value)) {
// fill an array indexed by the column names
$this->columnValues[$column] = $value ? 'TRUE' : 'FALSE';
} else {
if ($value !== '') {
// fill an array indexed by the column names
$this->columnValues[$column] = $value;
} else {
// if the value is NULL
$this->columnValues[$column] = "NULL";
}
}
}
}
}
public function __construct()
{
parent::__construct();
try {
// connection info
$db = array();
$db['host'] = '';
$db['port'] = '';
$db['name'] = 'app/database/samples.db';
$db['user'] = '';
$db['pass'] = '';
$db['type'] = 'sqlite';
TTransaction::open(NULL, $db);
// open transaction
$conn = TTransaction::get();
// get PDO connection
// make query
$result = $conn->query('SELECT id, name from customer order by id');
// iterate results
foreach ($result as $row) {
print $row['id'] . '-';
print $row['name'] . "<br>\n";
}
TTransaction::close();
// close transaction
} catch (Exception $e) {
new TMessage('error', $e->getMessage());
}
}
public function getSocial()
{
//RECUPERA CONEXAO BANCO DE DADOS
TTransaction::open('my_bd_site');
//TABELA exposition_gallery
$criteria = new TCriteria();
$criteria->setProperty('order', 'nome ASC');
// instancia a instrução de SELECT
$sql = new TSqlSelect();
$sql->addColumn('*');
$sql->setEntity('social');
// atribui o critério passado como parâmetro
$sql->setCriteria($criteria);
// obtém transação ativa
if ($conn = TTransaction::get()) {
// registra mensagem de log
TTransaction::log($sql->getInstruction());
// executa a consulta no banco de dados
$result = $conn->Query($sql->getInstruction());
$this->results = array();
if ($result) {
// percorre os resultados da consulta, retornando um objeto
while ($row = $result->fetchObject()) {
// armazena no array $this->results;
$this->results[] = $row;
}
}
}
TTransaction::close();
return $this->results;
}
public static function getFirst()
{
$conn = TTransaction::get();
// get PDO connection
// run query
$result = $conn->query('SELECT min(id) as min from category');
// show results
foreach ($result as $row) {
return $row['min'];
}
}
public function saveSale($client_insert, $amount_insert)
{
try {
$conn = TTransaction::get();
// get PDO connection
// run query
$date = date('Y-m-d');
$result = $conn->query('INSERT INTO system_sales (date, client, amount) VALUES (' . $date . ', ' . $client_insert . ', ' . $amount_insert . ')');
} catch (Exception $e) {
new TMessage('error', $e->getMessage());
}
}
public static function getManuByRequisicao(Manu $manu)
{
TTransaction::open('my_config');
if ($conn = TTransaction::get()) {
$sql = "SELECT M.*,U.nome_usuario FROM manutencoes M\r\n INNER JOIN usuarios U ON\r\n (U.id_usuario = M.responsavel_id)\r\n WHERE req_manutencao_id = ?";
$sth = $conn->prepare($sql);
$sth->execute(array($manu->getReqManutencaoId()));
$result = $sth->fetchALL(PDO::FETCH_OBJ);
return $result;
TTransaction::close();
}
}
public function getDataItem($id = NULL)
{
$conn = TTransaction::get();
// get PDO connection
// run query
$result = $conn->query('SELECT product, quantity, price from system_stock where id = ' . $id . ' order by id');
foreach ($result as $key) {
$this->product = $key['product'];
$this->quantity = $key['quantity'];
$this->price = $key['price'];
}
}
public static function concluiEmp($id)
{
TTransaction::open('my_config');
if ($conn = TTransaction::get()) {
$sql = "UPDATE itens_emprestimos SET entregue = 1\r\n WHERE id_item = ?";
$sth = $conn->prepare($sql);
$sth->execute(array($id));
TTransaction::close();
} else {
echo 'Sem conexão com banco!';
}
}
function CancelarStatus($id)
{
TTransaction::open('my_config');
if ($conn = TTransaction::get()) {
$sql = 'UPDATE req_manutencao SET estado_id = 1
WHERE id_requisicao = ?';
$sth = $conn->prepare($sql);
$sth->execute(array($id));
TTransaction::close();
} else {
echo 'Sem conexão com banco!';
}
}
/**
* Returns the SELECT statement as an string according to the database driver
*/
public function getInstruction()
{
$conn = TTransaction::get();
$driver = $conn->getAttribute(PDO::ATTR_DRIVER_NAME);
if (in_array($driver, array('mssql', 'dblib', 'sqlsrv'))) {
return $this->getSqlServerInstruction();
}
if (in_array($driver, array('oci', 'oci8'))) {
return $this->getOracleInstruction();
} else {
return $this->getStandardInstruction();
}
}
static function ConcluiCrlChave(CrlChave $ch)
{
TTransaction::open('my_config');
if ($conn = TTransaction::get()) {
$sql = "UPDATE laboratorios SET chave_laboratorio=0\r\n WHERE id_laboratorio = ?";
$sth = $conn->prepare($sql);
$sth->execute(array($ch->getLaboratorioId()));
$sql = "UPDATE ctrl_chaves SET dt_final_controle=?\r\n WHERE laboratorio_id = ?";
$sth = $conn->prepare($sql);
$sth->execute(array($ch->getDtFinalControle(), $ch->getLaboratorioId()));
TTransaction::close();
} else {
echo 'Sem conexão com banco!';
}
}
public function __construct()
{
parent::__construct();
try {
TTransaction::open('samples');
$sql = 'SELECT name, address FROM customer WHERE name like :name';
$conn = TTransaction::get();
$result = $conn->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$result->execute(array(':name' => 'And%'));
var_dump($result->fetchAll());
TTransaction::close();
} catch (Exception $e) {
new TMessage('error', $e->getMessage());
}
}
function count(TCriteria $criteria)
{
$sql = new TSqlSelect();
$sql->addColumn(' count(*) ');
$sql->setEntity(constant($this->class . '::TABLENAME'));
$sql->setCriteria($criteria);
if ($conn = TTransaction::get()) {
TTransaction::log($sql->getInstruction());
$result = $conn->query($sql->getInstruction());
if ($result) {
$row = $result->fetch();
}
return $row[0];
} else {
throw new Exception('Não há transação ativa!');
}
}
public static function InsertPermissoes($id, $arrayPost, $qtd_modulos_permissoes)
{
TTransaction::open('my_config');
if ($conn = TTransaction::get()) {
$sql = "DELETE FROM modulos_permissoes_usuarios\r\n WHERE usuario_id = ?";
$sth = $conn->prepare($sql);
$sth->execute(array($id));
for ($i = 0; $i < $qtd_modulos_permissoes; $i++) {
if ($arrayPost[$i] != 0) {
$sql = "INSERT INTO modulos_permissoes_usuarios (modulo_permissao_id,\r\n usuario_id) VALUE (?,?)";
$sth = $conn->prepare($sql);
$sth->execute(array($arrayPost[$i], $id));
}
}
TTransaction::close();
} else {
echo 'Sem conexão com banco!';
}
}
public function __construct()
{
parent::__construct();
try {
TTransaction::open('samples');
// open transaction
$conn = TTransaction::get();
// get PDO connection
// run query
$result = $conn->query('SELECT id, name from customer order by id');
// show results
foreach ($result as $row) {
print $row['id'] . '-';
print $row['name'] . "<br>\n";
}
TTransaction::close();
// close transaction
} catch (Exception $e) {
new TMessage('error', $e->getMessage());
}
}
public function __construct()
{
parent::__construct();
try {
TTransaction::open('samples');
// abre uma transação
$conn = TTransaction::get();
// obtém a conexão
// realiza a consulta
$result = $conn->query('SELECT id, name from customer order by id');
// exibe os resultados
foreach ($result as $row) {
print $row['id'] . '-';
print $row['name'] . "<br>\n";
}
TTransaction::close();
// fecha a transação.
} catch (Exception $e) {
new TMessage('error', $e->getMessage());
}
}
public function onGenerate($param)
{
try {
$this->form->validate();
$data = $this->form->getData();
if (strtoupper(substr($data->select, 0, 6)) !== 'SELECT') {
throw new Exception(_t('Invalid command'));
}
// creates a DataGrid
$datagrid = new BootstrapDatagridWrapper(new TDataGrid());
$datagrid->style = 'width: 100%';
$datagrid->setHeight(320);
TTransaction::open($data->database);
$conn = TTransaction::get();
$result = $conn->query($data->select);
$row = $result->fetch();
foreach ($row as $key => $value) {
if (is_string($key)) {
$col = new TDataGridColumn($key, $key, 'left');
$datagrid->addColumn($col);
}
}
// create the datagrid model
$datagrid->createModel();
$datagrid->addItem((object) $row);
$i = 1;
while ($row = $result->fetch() and $i <= 1000) {
$datagrid->addItem((object) $row);
$i++;
}
$panel = new TPanelGroup(_t('Results'));
$panel->add($datagrid);
$panel->addFooter(_t('^1 records shown', "<b>{$i}</b>"));
$this->container->addRow()->addCell($panel);
TTransaction::close();
} catch (Exception $e) {
new TMessage('error', $e->getMessage());
}
}
public static function autenticar(Login $login)
{
TTransaction::open('my_config');
$sessao = new TSessao(true);
if ($conn = TTransaction::get()) {
$sql = 'SELECT * FROM usuarios
WHERE login_usuario like ? AND senha_usuario like ?';
$sth = $conn->prepare($sql);
$sth->execute(array($login->getUsuario(), $login->getSenha()));
$result = $sth->fetch(PDO::FETCH_OBJ);
if ($result) {
include 'app.functions/buscaPermissoes.php';
$result->permissoes = buscaPermissoes($result->id_usuario);
$sessao->addVar('usuario', $result);
header('location:index.php');
} else {
$sessao->addVar('msg', 1);
header('location:index.php');
}
TTransaction::close();
} else {
echo 'Sem conexão com banco!';
}
}
/**
* Método count()
* Conta o número de ocorrencias que satisfazem o critério de seleção
*
* @access public
* @param $criteria Criteria de Selecção
* @throws Exception Não há transação ativa
* @return Número de Ocorrencias
*/
function count(TCriteria $criteria)
{
$sql = new TSqlSelect();
$sql->addColumn(' count(*) ');
$sql->addEntity($this->entity[0]);
$sql->setCriteria($criteria);
//RECUPERA CONEXAO BANCO DE DADOS
TTransaction::open('my_bd_site');
if ($conn = TTransaction::get()) {
$result = $conn->query($sql->getInstruction());
if ($result) {
$row = $result->fetch();
}
TTransaction::close();
return $row[0];
} else {
throw new Exception('Não há transação ativa!');
}
}
/**
* method onPDF()
* executed whenever the user clicks at the pdf button
*/
function onPDF($param)
{
try {
TTransaction::open('permission');
// open transaction
$conn = TTransaction::get();
// get PDO connection
// run query
$result = $conn->query('SELECT id, name from system_user order by id');
// get the form data into an active record Customer
$widths = array(60, 125, 125, 150, 70);
$tr = new TTableWriterPDF($widths);
// create the document styles
$tr->addStyle('title', 'Arial', '12', 'B', '#000000', '#CDCDCD');
$tr->addStyle('par', 'Arial', '10', '', '#000000', '#CFCFCF');
$tr->addStyle('impar', 'Arial', '10', '', '#000000', '#FFFFFF');
$tr->addStyle('header', 'Times', '16', 'B', '#000000', '#CDCDCD');
$tr->addStyle('footer', 'Times', '12', 'B', '#000000', '#CDCDCD');
// add a header row
$tr->addRow();
$tr->addCell('Employees', 'center', 'header', 5);
// add titles row
$tr->addRow();
$tr->addCell('DNI', 'center', 'title');
$tr->addCell('Name', 'center', 'title');
$tr->addCell('Address', 'center', 'title');
$tr->addCell('Email', 'center', 'title');
$tr->addCell('Phone', 'center', 'title');
// controls the background filling
$colour = FALSE;
foreach ($result as $row) {
$style = $colour ? 'par' : 'impar';
$tr->addRow();
$tr->addCell('53321548G', 'left', $style);
$tr->addCell('Jordi Aguilà Cortés', 'left', $style);
$tr->addCell('Pere Massallach 21 1 2', 'left', $style);
$tr->addCell('*****@*****.**', 'left', $style);
$tr->addCell('617186879', 'left', $style);
$colour = !$colour;
}
// footer row
$tr->addRow();
$tr->addCell(date('l jS \\of F Y h:i:s A'), 'center', 'footer', 5);
$tr->Footer('This document contains information about employees of the company.');
if (!file_exists("app/output/employees_" . date("Ymd") . ".pdf") or is_writable("app/output/employees_" . date("Ymd") . ".pdf")) {
$tr->save("app/output/employees_" . date("Ymd") . ".pdf");
} else {
throw new Exception(_t('Permission denied') . ': ' . "app/output/employees_" . date("Ymd") . ".pdf");
}
parent::openFile("app/output/employees_" . date("Ymd") . ".pdf");
// shows the success message
new TMessage('info', 'Report generated. Please, enable popups in the browser (just in the web).');
TTransaction::close();
// close transaction
} catch (Exception $e) {
// shows the exception error message
new TMessage('error', '<b>Error</b> ' . $e->getMessage());
}
}
/**
* Return the amount of objects that satisfy a given criteria
* @param $criteria An TCriteria object, specifiyng the filters
* @return An Integer containing the amount of objects that satisfy the criteria
*/
public function count(TCriteria $criteria = NULL)
{
if (!$criteria) {
$criteria = isset($this->criteria) ? $this->criteria : new TCriteria();
}
// creates a SELECT statement
$sql = new TSqlSelect();
$sql->addColumn('count(*)');
$sql->setEntity($this->getEntity());
// assign the criteria to the SELECT statement
$sql->setCriteria($criteria);
// get the connection of the active transaction
if ($conn = TTransaction::get()) {
// register the operation in the LOG file
TTransaction::log($sql->getInstruction());
// executes the SELECT statement
$result = $conn->Query($sql->getInstruction());
if ($result) {
$row = $result->fetch();
}
// returns the result
return $row[0];
} else {
// if there's no active transaction opened
throw new Exception(TAdiantiCoreTranslator::translate('No active transactions') . ': ' . __METHOD__ . ' ' . $this->getEntity());
}
}
private function getLast()
{
// inicia transação
if ($conn = TTransaction::get()) {
// instancia instrução de SELECT
$sql = new TSqlSelect();
$sql->addColumn('max(ID) as ID');
$sql->setEntity($this->getEntity());
// cria log e executa instrução SQL
TTransaction::log($sql->getInstruction());
$result = $conn->Query($sql->getInstruction());
// retorna os dados do banco
$row = $result->fetch();
return $row[0];
} else {
// se não tiver transação, retorna uma exceção
throw new Exception('Não há transação ativa!!');
}
}
public function getLast()
{
if ($conn = TTransaction::get()) {
// cria instrução SQL
$sql = new TSqlSelect();
$sql->addColumn('max(codigo) as codigo');
$sql->addEntity($this->getEntity());
$result = $conn->query($sql->getInstruction());
$row = $result->fetch();
return $row[0];
} else {
throw new Exception('Não há transação ativa');
}
}
function count(TCriteria $criteria)
{
// instancia instrução de SELECT
$sql = new TSqlSelect();
$sql->addColumn('count(*)');
$sql->setEntity(constant($this->class . '::TABLENAME'));
// atribui o critério passado como parâmetro
$sql->setCriteria($criteria);
// obtém transação ativa
if ($conn = TTransaction::get()) {
// registra mensagem de log
TTransaction::log($sql->getInstruction());
// executa instrução de SELECT
$result = $conn->Query($sql->getInstruction());
if ($result) {
$row = $result->fetch();
}
// retorna o resultado
return $row[0];
} else {
// se não tiver transação, retorna uma exceção
throw new Exception('Não há transação ativa!!');
}
}
/**
* Returns the LAST Object ID from database
* @return An Integer containing the LAST Object ID from database
* @exception Exception if there's no active transaction opened
*/
private function getLast()
{
$pk = $this->getPrimaryKey();
// get the connection of the active transaction
if ($conn = TTransaction::get()) {
// instancia instrução de SELECT
$sql = new TSqlSelect();
$sql->addColumn("max({$pk}) as {$pk}");
$sql->setEntity($this->getEntity());
// register the operation in the LOG file
TTransaction::log($sql->getInstruction());
$result = $conn->Query($sql->getInstruction());
// retorna os dados do banco
$row = $result->fetch();
return $row[0];
} else {
// if there's no active transaction opened
throw new Exception(TAdiantiCoreTranslator::translate('No active transactions') . ': ' . __METHOD__ . ' ' . $this->getEntity());
}
}
public static function VerificaNumeroLab($numero)
{
TTransaction::open('my_config');
if ($conn = TTransaction::get()) {
$sql = "SELECT count(numero_laboratorio) FROM laboratorios\r\n WHERE numero_laboratorio = ? AND deleted = 0";
$sth = $conn->prepare($sql);
$sth->execute(array($numero));
return $sth->fetch();
TTransaction::close();
} else {
echo 'Sem conexão com banco!';
}
}
private function criaConexao()
{
\TTransaction::open();
return \TTransaction::get();
}
public static function delete($id)
{
TTransaction::open('my_config');
if ($conn = TTransaction::get()) {
$sql = "DELETE FROM usuarios\r\n WHERE id_usuario = ?";
$sth = $conn->prepare($sql);
$sth->execute(array($id));
TTransaction::close();
} else {
echo 'Sem conexão com banco!';
}
}
// obtém a conexão ativa
$conn = TTransaction::get();
// executa a instrução SQL
$result = $conn->Query($sql->getInstruction());
// escreve mensagem de LOG
TTransaction::log($sql->getInstruction());
// define a estratégia de LOG
TTransaction::setLogger(new TLoggerXML('/tmp/arquivo.xml'));
// escreve mensagem de LOG
TTransaction::log("Inserindo registro Albert Einstein");
// cria uma instrução de INSERT
$sql = new TSqlInsert();
// define o nome da entidade
$sql->setEntity('famosos');
// atribui o valor de cada coluna
$sql->setRowData('codigo', 10);
$sql->setRowData('nome', 'Albert Einstein');
// obtém a conexão ativa
$conn = TTransaction::get();
// executa a instrução SQL
$result = $conn->Query($sql->getInstruction());
// escreve mensagem de LOG
TTransaction::log($sql->getInstruction());
// fecha a transação, aplicando todas as operações
TTransaction::close();
} catch (Exception $e) {
// exibe a mensagem de erro
echo $e->getMessage();
// desfaz operações realizadas durante a transação
TTransaction::rollback();
}
public function getNaoUsados()
{
$conn = TTransaction::get();
$result = $conn->query("SELECT id FROM ticket WHERE id NOT IN (SELECT ticket_id FROM requisito_desenvolvimento)");
foreach ($result as $row) {
$retorno[] = $row[0];
}
if (!$retorno) {
$action = new TAction(array('TicketForm', 'onEdit'));
new TMessage('info', 'Cadastre um ticket antes de Gerar DR', $action);
// success message
}
return $retorno;
}
