/**
* Returns list of tables.
*/
public function getTables()
{
$tables = array();
foreach ($this->connection->query('SELECT * FROM cat') as $row) {
if ($row[1] === 'TABLE' || $row[1] === 'VIEW') {
$tables[] = array('name' => $row[0], 'view' => $row[1] === 'VIEW');
}
}
return $tables;
}
/**
* @inheritdoc
*/
protected function setUp()
{
$this->schemas = (require __DIR__ . '/db/schemas.php');
$this->fixtures = (require __DIR__ . '/db/fixtures.php');
$this->connection = Manager::getConnection(['driver' => 'pdo_sqlite']);
foreach ($this->schemas as $table => $definition) {
$this->connection->query('CREATE TABLE ' . $table . ' (' . join(', ', $definition) . ')');
}
foreach ($this->fixtures as $table => $fixtures) {
foreach ($fixtures as $data) {
$this->connection->insert($table, $data);
}
}
}
public function load(Connection $connection)
{
if (!$connection->isConnected) {
$connection->Connect();
}
if (!$connection->query($this->medicationQuery)) {
return false;
} else {
$result = null;
$medication = null;
while ($result = $connection->getObject()) {
$medication = new Medication();
$medication->setCommonDose($result->commonDose);
$medication->setGenericName($result->genericName);
$medication->setRoute($result->route);
$medication->setUnit($result->unit);
$medication->setMedicationID($result->medicationId);
$medication->setActive($result->active);
$medication->setConfirmed($result->confirmed);
$medication->setConfirmedBy($result->confirmedBy);
array_push($this->medicationList, $medication);
}
}
return true;
}
public function testGetAffectedRowsDelete()
{
$this->createTable();
$statement = $this->connection->query("INSERT INTO cat (name, colour) VALUES (?, ?)", array('Nennek', 'black'));
$statement = $this->connection->query("DELETE FROM cat WHERE id = ?", array(1));
$this->assertEquals(1, $this->connection->getAffectedRows());
}
function __construct()
{
if (!isset($_SESSION['username'])) {
if (isset($_POST['login'])) {
$connection = new Connection();
$this->username = $connection->validate_string($_POST['username']);
$password = $connection->validate_string($_POST['password']);
$query = "\n\t\t\t\tSELECT p_password, p_barcode\n\t\t\t\tFROM people\n\t\t\t\tWHERE p_username = '******';";
$connection->query($query);
if ($connection->result_size() == 1) {
$row = $connection->fetch_row();
if ($row[0] == $password && $password != "") {
$_SESSION['username'] = $this->username;
$_SESSION['barcode'] = $row[1];
$_SESSION['user'] = true;
$_SESSION['admin'] = true;
} else {
$this->loginError();
exit(0);
}
} else {
$this->loginError();
exit(0);
}
} else {
$this->printLoginScreen();
exit(0);
}
}
$this->username = $_SESSION['username'];
$this->barcode = $_SESSION['barcode'];
$this->user = $_SESSION['user'];
$this->admin = $_SESSION['admin'];
}
function getUsers()
{
$db = new Connection(DB_HOST, DB_USER, DB_PASS, DB_NAME);
$result = $db->query('SELECT name FROM users');
while ($row = mysql_fetch_assoc($result)) {
echo $row['name'] . '<br>';
}
}
public function save()
{
$oCon = new Connection();
if ($this->iPostID == 0) {
$sSQL = "INSERT INTO tbposts (PostContent, TopicID, MemberID) VALUES (\n '" . $this->sPostContent . "',\n '" . $this->iTopicID . "',\n '" . $this->iMemberID . "')";
$bResult = $oCon->query($sSQL);
if ($bResult == true) {
$this->iPostID = $oCon->getInsertID();
} else {
die($sSQL . " did not run");
}
} else {
$sSQL = 'UPDATE tbposts SET Active = ' . $this->iActive . ' WHERE PostID=' . $this->iPostID;
$bResult = $oCon->query($sSQL);
}
$oCon->close();
}
public function remove($id_mensaje)
{
$db = new Connection();
$dateLeaving = date("m.d.y");
$query = "UPDATE MENSAJE SET fecha_baja = {$dateLeaving} where id_mensaje = {$id_mensaje}";
$results = $db->query($query) or die('Error dando de baja el mensaje: ' . mysqli_error($this->db));
$db->close();
}
/**
* @return Result
*/
private function query($args)
{
$res = $this->connection->query($args);
foreach ($this->setups as $setup) {
call_user_func_array([$res, array_shift($setup)], $setup);
}
return $res;
}
/**
* @param null $sql
*
* @return bool
*/
public function exists($sql = null)
{
if (null == $sql) {
$sql = $this->prepareExists();
}
$result = $this->_connection->query($sql);
return $result->one() != null;
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
/* Not for multi-column foreign keys */
$keys = array();
foreach ($this->connection->query("\n\t\t\tSELECT\n\t\t\t\ttc.constraint_name AS name,\n\t\t\t\tkcu.column_name AS local,\n\t\t\t\tccu.table_name AS table,\n\t\t\t\tccu.column_name AS foreign\n\t\t\tFROM\n\t\t\t\tinformation_schema.table_constraints AS tc\n\t\t\t\tJOIN information_schema.key_column_usage AS kcu USING(constraint_catalog, constraint_schema, constraint_name)\n\t\t\t\tJOIN information_schema.constraint_column_usage AS ccu USING(constraint_catalog, constraint_schema, constraint_name)\n\t\t\tWHERE\n\t\t\t\tconstraint_type = 'FOREIGN KEY'\n\t\t\t\tAND\n\t\t\t\ttc.table_name = {$this->connection->quote($table)}\n\t\t\tORDER BY\n\t\t\t\tkcu.ordinal_position\n\t\t") as $row) {
$keys[] = (array) $row;
}
return $keys;
}
public function load($iGenreID)
{
$oCon = new Connection();
$sSQL = "SELECT GenreID, GenreName, DisplayOrder \n\t\t\tFROM tbgenres \n\t\t\tWHERE GenreID = " . $iGenreID;
$oResultSet = $oCon->query($sSQL);
$aRow = $oCon->fetchArray($oResultSet);
$this->iGenreID = $aRow['GenreID'];
$this->sGenreName = $aRow['GenreName'];
$this->iDisplayOrder = $aRow['DisplayOrder'];
$sSQL = "SELECT RecordID\n\t\t\tFROM tbrecords \n\t\t\tWHERE GenreID = " . $iGenreID;
$oResultSet = $oCon->query($sSQL);
while ($aRow = $oCon->fetchArray($oResultSet)) {
$iRecordID = $aRow["RecordID"];
$oRecord = new Record();
$oRecord->load($iRecordID);
$this->aRecords[] = $oRecord;
}
$oCon->close();
}
/**
* @param string $table
* @param mixed $hash
* @param Context\Query $context
* @return \Riverline\DynamoDB\Collection
*/
public function query($table, $hash, Context\Query $context = null)
{
$collection = new Collection();
do {
try {
$items = $this->connection->query($table, $hash, $context);
$collection->merge($items);
if ($items->more()) {
$context = $items->getNextContext();
} else {
// End
break;
}
} catch (\Riverline\DynamoDB\Exception\ProvisionedThroughputExceededException $e) {
// Continue
}
} while (true);
return $collection;
}
public function save()
{
$oCon = new Connection();
if ($this->iCategoryID == 0) {
$sSQL = 'INSERT INTO tbcategory (CategoryName, CategoryDesc)
VALUES(
"' . $this->sCategoryName . '",
"' . $this->sCategoryDesc . '")';
$bResult = $oCon->query($sSQL);
if ($bResult == true) {
$this->iCategoryID = $oCon->getInsertID();
} else {
die($sSQL . " did not run");
}
} else {
$sSQL = 'UPDATE tbcategory SET Active = ' . $this->iActive . ' WHERE CategoryID=' . $this->iCategoryID;
$bResult = $oCon->query($sSQL);
}
$oCon->close();
}
function selectAll()
{
$claseConexion = new Connection();
$resultado = $claseConexion->query("\n SELECT Id_horario, Hora_ini, Hora_fin, Dia, Id_materia, Id_aula\n FROM horario");
$listaHorario = array();
while ($row = $resultado->fetch(PDO::FETCH_ASSOC)) {
$objResultado = $this->rowToDto($row);
$listaHorario[] = $objResultado;
}
return $listaHorario;
}
public function save()
{
$connection = new Connection();
$sSQL = "INSERT INTO tblike(UserID, RecipeID)\n\t\t\t VALUES ('" . $connection->escape($this->iUserID) . "','" . $connection->escape($this->iRecipeID) . "')";
$bSuccess = $connection->query($sSQL);
if ($bSuccess == true) {
$this->iLikeID = $connection->get_insert_id();
} else {
die($sSQL . " fails!");
}
}
function selectAll()
{
$claseConexion = new Connection();
$resultado = $claseConexion->query("\n SELECT idCategoria,nombre, color \n FROM tblcategorias;");
$listaCategorias = array();
while ($row = $resultado->fetch(PDO::FETCH_ASSOC)) {
$objResultado = $this->rowToDto($row);
$listaCategorias[] = $objResultado;
}
return $listaCategorias;
}
function selectAll()
{
$claseConexion = new Connection();
$resultado = $claseConexion->query("\n SELECT idHorario, idMateria, dia, hrInicio, hrTermina, idAula\n FROM tblhorarios");
$listaHorarios = array();
while ($row = $resultado->fetch(PDO::FETCH_ASSOC)) {
$objResultado = $this->rowToDto($row);
$listaHorarios[] = $objResultado;
}
return $listaHorarios;
}
function selectAll()
{
$claseConexion = new Connection();
$resultado = $claseConexion->query("CALL sp_tblNotas_SelectAll();");
$listaNotas = array();
while ($row = $resultado->fetch(PDO::FETCH_ASSOC)) {
$objResultado = $this->rowToDto($row);
$listaNotas[] = $objResultado;
}
return $listaNotas;
}
public function save()
{
$oCon = new Connection();
if ($this->iCustomerID == 0) {
$sSQL = "INSERT INTO tbcustomer (Name, Address, Phone, Email, Password) \n\t\t\t\tVALUES ('" . $this->sName . "', '" . $this->sAddress . "', '" . $this->sPhone . "', '" . $this->sEmail . "', '" . $this->sPassword . "')";
$bResult = $oCon->query($sSQL);
if ($bResult == true) {
$this->iCustomerID = $oCon->getInsertID();
} else {
die($sSQL . " Did not run");
}
} else {
$sSQL = "UPDATE tbcustomer \n\t\t\t\t\tSET Name = '" . $this->sName . "',\n\t\t\t\t\t \tAddress = '" . $this->sAddress . "',\n\t\t\t\t\t \tPhone = '" . $this->sPhone . "',\n\t\t\t\t\t \tEmail = '" . $this->sEmail . "',\n\t\t\t\t\t \tPassword = '******'\n\t\t\t\t\t \tWHERE CustomerID = " . $this->iCustomerID;
$bResult = $oCon->query($sSQL);
if ($bResult == false) {
die($sSQL . " Did not run");
}
}
$oCon->close();
}
function selectAll()
{
$claseConexion = new Connection();
$resultado = $claseConexion->query("\n select m.nombremateria,a.numaula,h.dia,h.hora from tblmaterias as m inner join tblhorarios as h on m.idmateria=h.idmateria inner join tblaulas as a on h.idaula=a.idaula");
$listaHorarios = array();
while ($row = $resultado->fetch(PDO::FETCH_ASSOC)) {
$objResultado = $this->rowToDto($row);
$listaHorarios[] = $objResultado;
}
return $listaHorarios;
}
public static function add($firstName, $lastName, $birthDate)
{
$conn = new Connection();
$query = "INSERT INTO person (FirstName, LastName, BirthDate) VALUES('%s', '%s', '%s');";
$query = sprintf($query, $firstName, $lastName, $birthDate);
$result = $conn->query($query);
if ($result) {
return mysql_insert_id();
}
return null;
}
function selectAll()
{
$claseConexion = new Connection();
$resultado = $claseConexion->query("\n SELECT Id_aula, Nro_aula\n FROM aula");
$listaPersonas = array();
while ($row = $resultado->fetch(PDO::FETCH_ASSOC)) {
$objResultado = $this->rowToDto($row);
$listaPersonas[] = $objResultado;
}
return $listaPersonas;
}
function selectAll()
{
$claseConexion = new Connection();
$resultado = $claseConexion->query("\n SELECT nID_Registro, nID_Materia, sDia, sHora_Inicio, sHora_Termina, nID_Aula\n FROM tblRegistros");
$listaPersonas = array();
while ($row = $resultado->fetch(PDO::FETCH_ASSOC)) {
$objResultado = $this->rowToDto($row);
$listaPersonas[] = $objResultado;
}
return $listaPersonas;
}
function selectAll()
{
$claseConexion = new Connection();
$resultado = $claseConexion->query("\n SELECT id, nombres, apellidos, edad\n FROM tblPersona");
$listaPersonas = array();
while ($row = $resultado->fetch(PDO::FETCH_ASSOC)) {
$objResultado = $this->rowToDto($row);
$listaPersonas[] = $objResultado;
}
return $listaPersonas;
}
public function save()
{
$oCon = new Connection();
if ($this->iMemberID == 0) {
$sSQL = "INSERT INTO tbmember\n (MemberName, MemberPassword, MemberEmail) VALUES ('" . $this->sMemberName . "',\n '" . $this->sMemberPassword . "',\n '" . $this->sMemberEmail . "')";
$bResult = $oCon->query($sSQL);
if ($bResult == true) {
$this->iMemberID = $oCon->getInsertID();
} else {
die($sSQL . " did not run");
}
} else {
$sSQL = "UPDATE tbmember SET MemberName = '" . $this->sMemberName . "'WHERE MemberID = " . $this->iMemberID;
$bResult = $oCon->query($sSQL);
if (bResult == false) {
die($sSQL . " did not run");
}
}
$oCon->close();
}
function selectNotasArchivadas()
{
$claseConexion = new Connection();
$resultado = $claseConexion->query("\n SELECT id, fecha, nota, estado, idCategoria, titulo\n FROM tblnota WHERE estado = 0");
$listaHorarios = array();
while ($row = $resultado->fetch(PDO::FETCH_ASSOC)) {
$objResultado = $this->rowToDto($row);
$listaHorarios[] = $objResultado;
}
return $listaHorarios;
}
public function save()
{
$connection = new Connection();
if ($this->iRecipeID == 0) {
$sSQL = "INSERT INTO tbrecipe(Title, AuthorNotes, Ingredients, Directions, ImagePath, UserID, RecipeTypeID)\n VALUES ('" . $connection->escape($this->sTitle) . "','" . $connection->escape($this->sAuthorNotes) . "','" . $connection->escape($this->sIngredients) . "','" . $connection->escape($this->sDirections) . "','" . $connection->escape($this->sImagePath) . "','" . $connection->escape($this->iUserID) . "','" . $connection->escape($this->iRecipeTypeID) . "')";
$bSuccess = $connection->query($sSQL);
if ($bSuccess == true) {
$this->iRecipeID = $connection->get_insert_id();
} else {
die($sSQL . " fails!");
}
} else {
// update instead
$sSQL = "UPDATE tbrecipe\n SET Title = '" . $connection->escape($this->sTitle) . "',AuthorNotes ='" . $connection->escape($this->sAuthorNotes) . "',Ingredients='" . $connection->escape($this->sIngredients) . "',Directions='" . $connection->escape($this->sDirections) . "',ImagePath='" . $connection->escape($this->sImagePath) . "',UserID='" . $connection->escape($this->iUserID) . "', RecipeTypeID='" . $connection->escape($this->iRecipeTypeID) . "'\n WHERE RecipeID=" . $this->iRecipeID;
$bSuccess = $connection->query($sSQL);
if ($bSuccess == false) {
die($sSQL . " fails!");
}
}
}
public function save()
{
$connection = new Connection();
if ($this->iProductID == 0) {
$sSQL = "INSERT INTO tbproduct(ProductName, Description, Price, Size, Ingredients, StockLevel, ImagePath)\n VALUES ('" . $connection->escape($this->sProductName) . "','" . $connection->escape($this->sDescription) . "','" . $connection->escape($this->fPrice) . "','" . $connection->escape($this->sSize) . "','" . $connection->escape($this->sIngredients) . "','" . $connection->escape($this->iStockLevel) . "','" . $connection->escape($this->sImagePath) . "')";
$bSuccess = $connection->query($sSQL);
if ($bSuccess == true) {
$this->iProductID = $connection->get_insert_id();
} else {
die($sSQL . " fails!");
}
} else {
//update instead
$sSQL = "UPDATE tbproduct\n SET ProductName = '" . $connection->escape($this->sProductName) . "',Description ='" . $connection->escape($this->sDescription) . "',Price='" . $connection->escape($this->fPrice) . "',Size='" . $connection->escape($this->sSize) . "',Ingredients='" . $connection->escape($this->sIngredients) . "',StockLevel='" . $connection->escape($this->iStockLevel) . "', ImagePath='" . $connection->escape($this->sImagePath) . "'\n WHERE ProductID=" . $this->iProductID;
$bSuccess = $connection->query($sSQL);
if ($bSuccess == false) {
die($sSQL . " fails!");
}
}
}
function selectAll()
{
$claseConexion = new Connection();
$resultado = $claseConexion->query("\n SELECT nID_Materia, sCodigo, sNombre, sSemestre, nCosto\n FROM tblMaterias");
$listaPersonas = array();
while ($row = $resultado->fetch(PDO::FETCH_ASSOC)) {
$objResultado = $this->rowToDto($row);
$listaPersonas[] = $objResultado;
}
return $listaPersonas;
}
