/**
* @param string $table
* @param array $data
*
* @return bool|int
*/
protected final function insert($table, array $data)
{
if (empty($data)) {
return false;
}
$keys = array_keys($data);
$sql = 'INSERT INTO ' . $table . ' (' . implode(', ', $keys) . ') VALUES (:' . implode(', :', $keys) . ');';
$query = $this->pdo->prepare($sql);
foreach ($data as $key => $value) {
$query->bindValue(':' . $key, $data[$key]);
}
$query->execute();
$insert = $query->rowCount();
if ($insert) {
return (int) $this->pdo->lastInsertId();
}
return false;
}
/**
* execute query
*
* @param \Aura\SqlQuery\AbstractQuery|\Aura\SqlQuery\Common\SelectInterface|\Aura\SqlQuery\Common\ValuesInterface $queryObject
* @return array
* @throws \Exception
*/
public function query($queryObject)
{
$query = $queryObject->__toString();
$sth = self::$_connection->prepare($query);
if (!$sth) {
$err = self::$_connection->errorInfo();
throw new \Exception('DB Error: ' . $err[0] . ' - ' . $err[2]);
}
$values = $queryObject->getBindValues();
foreach ($values as $key => $val) {
$sth->bindParam(':' . $key, $val);
unset($val);
}
$res = $sth->execute();
if (!$res) {
$err = $sth->errorInfo();
throw new \Exception('DB Error: ' . $err[0] . ' - ' . $err[2]);
}
return $sth;
}
public function modifyWebDispatcher($di)
{
$dispatcher = $di->get('aura/web-kernel:dispatcher');
$dispatcher->setObject('hello', function () use($di) {
$response = $di->get('aura/web-kernel:response');
$response->content->set('Hello World!');
});
$dispatcher->setObject('about', function () use($di) {
$view_factory = new \Aura\View\ViewFactory();
$view = $view_factory->newInstance();
$layout_registry = $view->getLayoutRegistry();
$layout_registry->set('default', './../src/templates/default.layout.php');
$view_registry = $view->getViewRegistry();
$view_registry->set('about', './../src/templates/about.php');
$view->setView('about');
$view->setLayout('default');
$response = $di->get('aura/web-kernel:response');
$response->content->set($view());
});
$dispatcher->setObject('data-view-sales', function () use($di) {
$view_factory = new \Aura\View\ViewFactory();
$view = $view_factory->newInstance();
$pdo = new ExtendedPdo('sqlite:./../db/database.sqlite');
$extended_pdo = new ExtendedPdo($pdo);
$stm = '
SELECT t.TrackId, sum(il.unitprice) as "TotalSales", t.Name as "TrackName", g.Name as Genre, a.Title as "AlbumTitle", at.Name as "ArtistName"
from InvoiceLine il
inner join track t on (t.TrackId = il.TrackId)
INNER JOIN genre g on (g.GenreId = t.GenreId)
inner join album a on (a.AlbumId = t.AlbumId)
INNER JOIN artist at on (at.ArtistId = a.ArtistId)
WHERE g.Name like :genre
group by t.TrackId
HAVING at.Name = :artist_name
order by sum(il.UnitPrice) desc, t.Name asc
';
$bind = array('genre' => 'TV%', 'artist_name' => 'Lost');
$sth = $pdo->prepare($stm);
$sth->execute($bind);
$layout_registry = $view->getLayoutRegistry();
$layout_registry->set('default', './../src/templates/sales.layout.php');
$view_registry = $view->getViewRegistry();
$view_registry->set('sales-data', './../src/templates/data/sales/view.php');
$view->setView('sales-data');
$view->setLayout('default');
// the "sub" template
$view_registry->set('_result', './../src/templates/data/sales/result.php');
$view->setData(['results' => $pdo->fetchObjects($stm, $bind, '\\DatabaseObjects\\Entity\\SalesData')]);
$response = $di->get('aura/web-kernel:response');
$response->content->set($view());
});
}
