public function getConcertsByFilter($params)
{
$adapter = $this->tableGateway->getAdapter();
$sql = new Sql($adapter);
$select = $sql->select();
$select->from(array('c' => 'concerts'), array('place', 'address', 'city', 'date_concert', 'cost', 'id_artist'))->join(array('a' => 'artists'), 'c.id_artist = a.id', array('name', 'image', 'tags'));
// join table with alias
if (count($params) == 5) {
$select->where(array('city' => $params['city'], 'tags' => $params['tags'], 'cost' => $params['cost']))->where->between('date_concert', $params['date_start'], $params['date_end']);
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();
return $results->current();
} else {
foreach ($params as $key => $value) {
if ($key == 'date_start') {
$select->where('date_concert >= ' . $value);
} elseif ($key == 'date_end') {
$select->where('date_concert <= ' . $value);
} else {
$select->where(array($key => $value));
}
}
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();
return $results;
}
}
/**
* Returns the total number of rows in the result set.
*
* @return int
*/
public function count()
{
if ($this->rowCount !== null) {
return $this->rowCount;
}
$select = clone $this->select;
$select->reset(Select::COLUMNS);
$select->reset(Select::LIMIT);
$select->reset(Select::OFFSET);
$select->reset(Select::ORDER);
$select->reset(Select::GROUP);
// get join information, clear, and repopulate without columns
$joins = $select->getRawState(Select::JOINS);
$select->reset(Select::JOINS);
foreach ($joins as $join) {
$select->join($join['name'], $join['on'], array(), $join['type']);
}
$select->columns(array('c' => new Expression('COUNT(1)')));
$statement = $this->sql->prepareStatementForSqlObject($select);
$result = $statement->execute();
$row = $result->current();
$this->rowCount = $row['c'];
return $this->rowCount;
}
public function getPerTime($where_params = null, $is_super_admin = 0)
{
$obj_list = array();
$sql = new Sql($this->adapter);
$select = $sql->select();
$select->from('publisherHourlyBidsBidsPerTime');
if (!empty($where_params['DateCreatedGreater'])) {
$select->where($select->where->greaterThanOrEqualTo('DateCreated', $where_params['DateCreatedGreater']));
}
if (!empty($where_params['DateCreatedLower'])) {
$select->where($select->where->lessThanOrEqualTo('DateCreated', $where_params['DateCreatedLower']));
}
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();
foreach ($results as $obj) {
if (!$is_super_admin) {
array_walk($obj, function ($item, $key) use(&$obj) {
if (array_search($key, $this->adminFields) !== FALSE) {
$obj[$key] = FALSE;
}
});
$obj = array_filter($obj, function ($value) {
return $value !== FALSE;
});
}
$obj['MDYH'] = $this->re_normalize_time($obj['MDYH']);
$obj_list[] = $obj;
}
return $obj_list;
}
public function getUserRoles($where = array(), $columns = array(), $orderBy = '', $paging = false)
{
try {
$sql = new Sql($this->getAdapter());
$select = $sql->select()->from(array('sa' => $this->table));
if (count($where) > 0) {
$select->where($where);
}
$select->where($where);
if (count($columns) > 0) {
$select->columns($columns);
}
if (!empty($orderBy)) {
$select->order($orderBy);
}
if ($paging) {
$dbAdapter = new DbSelect($select, $this->getAdapter());
$paginator = new Paginator($dbAdapter);
return $paginator;
} else {
$statement = $sql->prepareStatementForSqlObject($select);
$clients = $this->resultSetPrototype->initialize($statement->execute())->toArray();
return $clients;
}
} catch (\Exception $e) {
throw new \Exception($e->getPrevious()->getMessage());
}
}
public function save(\Api\Entity\Post $post)
{
$hydrator = $this->getHydrator();
$action = null;
$postData = array('title' => $post->getTitle(), 'description' => $post->getDescription());
if ($post->getId()) {
$action = new Update('posts');
$action->set($postData);
$action->where(array('id = ?' => $post->getId()));
} else {
$postData['author_id'] = $post->getAuthorId();
$action = new Insert('posts');
$action->values($postData);
}
$sql = new Sql($this->getAdaptor());
$statement = $sql->prepareStatementForSqlObject($action);
$result = $statement->execute();
if ($result instanceof ResultInterface) {
if ($pk = $result->getGeneratedValue()) {
$post->setId($pk);
}
return $this->getPost($post->getId());
}
throw new \Exception('something went wrong.Please try again later');
}
/**
* Saves a user to a database
*
* @param array $user
*/
public function saveUser(array $user)
{
$sql = new Sql($this->dbAdapter);
$insert = $sql->insert('users');
$insert->columns(array_keys($user))->values($user);
$sql->prepareStatementForSqlObject($insert)->execute();
}
public function indexAction()
{
date_default_timezone_set('Asia/Dubai');
$auth = new AuthenticationService();
$container = new Container('username');
$sm = $this->getServiceLocator();
$dba = $sm->get($container->adapter);
$secsql = "SELECT * FROM section";
$secstatement = $dba->query($secsql, array(5));
$section = new ResultSet();
$section->initialize($secstatement);
$teasql = "SELECT * FROM teacher";
$teastatement = $dba->query($teasql, array(5));
$teacher = new ResultSet();
$teacher->initialize($teastatement);
if ($auth->hasIdentity() && $container->type == 0) {
if ($this->getRequest()->getPost('submit-update')) {
$count = (int) $this->getRequest()->getPost('rcount');
for ($i = 1; $i <= $count; $i++) {
$id = $this->getRequest()->getPost('rid' . $i);
$att = $this->getRequest()->getPost('rattendance' . $i);
$com = $this->getRequest()->getPost('rcomment' . $i);
$stup = $this->getRequest()->getPost('status-update' . $i);
if ($stup != 0) {
$data = array('counted' => $att, 'comment' => $com, 'Abs_value' => $stup);
$sql = new Sql($dba);
$update = $sql->update();
$update->table('attendance');
$update->set($data);
$update->where(array('Att_id' => $id));
$statement = $sql->prepareStatementForSqlObject($update);
$statement->execute();
} else {
$id = $this->getRequest()->getPost('rid' . $i);
$sql = new Sql($dba);
$delete = $sql->delete('attendance')->where(array('Att_id' => $id));
$statement = $sql->prepareStatementForSqlObject($delete);
$statement->execute();
}
}
$sday = $this->getRequest()->getPost('sdate-filter');
$eday = $this->getRequest()->getPost('edate-filter');
$syour_date = date("Y-m-d", strtotime($sday));
$eyour_date = date("Y-m-d", strtotime($eday));
return new ViewModel(array('attendance' => $this->getRepport($syour_date, $eyour_date, $this->getRequest()->getPost('stid-filter'), $this->getRequest()->getPost('tea-filter'), $this->getRequest()->getPost('sec-filter'), $this->getRequest()->getPost('p-filter'), $this->getRequest()->getPost('status-filter')), 'sections' => $section, 'teachers' => $teacher, 'message' => "Attendance report updated", 'sday' => $sday, 'eday' => $eday, 'stid' => $this->getRequest()->getPost('stid-filter'), 'tid' => $this->getRequest()->getPost('tea-filter'), 'secid' => $this->getRequest()->getPost('sec-filter'), 'pid' => $this->getRequest()->getPost('p-filter'), 'status' => $this->getRequest()->getPost('status-filter'), 'filter' => "Attendance report for " . $this->getRequest()->getPost('stid-filter') . " from " . date("l jS \\of F Y ", strtotime($sday)) . " to " . date("l jS \\of F Y ", strtotime($eday))));
} else {
if ($this->getRequest()->getPost('submit-date')) {
$sday = $this->getRequest()->getPost('sdate-filter');
$eday = $this->getRequest()->getPost('edate-filter');
$syour_date = date("Y-m-d", strtotime($sday));
$eyour_date = date("Y-m-d", strtotime($eday));
return new ViewModel(array('attendance' => $this->getRepport($syour_date, $eyour_date, $this->getRequest()->getPost('stid-filter'), $this->getRequest()->getPost('tea-filter'), $this->getRequest()->getPost('sec-filter'), $this->getRequest()->getPost('p-filter'), $this->getRequest()->getPost('status-filter')), 'sections' => $section, 'teachers' => $teacher, 'message' => "", 'sday' => $sday, 'eday' => $eday, 'stid' => $this->getRequest()->getPost('stid-filter'), 'tid' => $this->getRequest()->getPost('tea-filter'), 'secid' => $this->getRequest()->getPost('sec-filter'), 'pid' => $this->getRequest()->getPost('p-filter'), 'status' => $this->getRequest()->getPost('status-filter'), 'filter' => "Attendance report for " . $this->getRequest()->getPost('stid-filter') . " from " . date("l jS \\of F Y ", strtotime($sday)) . " to " . date("l jS \\of F Y ", strtotime($eday))));
} else {
return new ViewModel(array('attendance' => $this->getRepport(date('Y-m-d'), date('Y-m-d'), "", 0, 0, 0, 0), 'sections' => $section, 'teachers' => $teacher, 'message' => "", 'sday' => date('m/d/Y'), 'eday' => date('m/d/Y'), 'stid' => "", 'tid' => 0, 'secid' => 0, 'pid' => 0, 'status' => 0, 'filter' => "Attendance report for " . date("l jS \\of F Y ", strtotime(date('m/d/Y')))));
}
}
} else {
return $this->redirect()->toRoute('login', array('controller' => 'index', 'action' => 'login'));
}
}
/**
* This function returns the user by ID
* @param int $userId
* @return array
*/
public function getUser($userId)
{
$sql = new Sql($this->tableGateway->getAdapter());
$select = $sql->select()->from(array('u' => 'user'))->join(array('r' => 'role'), 'r.id = u.role_id', array('*'))->where(array('u.id' => $userId))->order('u.id');
$stmt = $sql->prepareStatementForSqlObject($select);
$results = $stmt->execute();
return $results;
}
public function getRole($roleId)
{
$sql = new Sql($this->tableGateway->getAdapter());
$select = $sql->select()->from(array('r' => 'role'))->where(array('r.id' => $roleId))->order('r.id');
$stmt = $sql->prepareStatementForSqlObject($select);
$results = $stmt->execute();
return $results;
}
/**
* Removes this record from the database
*/
protected function delete()
{
if ($this->getId()) {
$sql = new Sql(Database::getConnection(), $this->tablename);
$delete = $sql->delete()->where(['id' => $this->getId()]);
$sql->prepareStatementForSqlObject($delete)->execute();
}
}
/**
* Function for getting role permissions
*
* @author Kaushal Kishore <*****@*****.**>
* @return unknown
*/
public function getRolePermissions()
{
$sql = new Sql($this->getAdapter());
$select = $sql->select()->from(array('t1' => 'role'))->columns(array('role_name'))->join(array('t2' => $this->table), 't1.rid = t2.role_id', array(), 'left')->join(array('t3' => 'permission'), 't3.id = t2.permission_id', array('permission_name'), 'left')->join(array('t4' => 'resource'), 't4.id = t3.resource_id', array('resource_name'), 'left')->where('t3.permission_name is not null and t4.resource_name is not null')->order('t1.rid');
$statement = $sql->prepareStatementForSqlObject($select);
$result = $this->resultSetPrototype->initialize($statement->execute())->toArray();
return $result;
}
public function findActiveDeals()
{
$sql = new \Zend\Db\Sql\Sql($this->getAdapter());
$select = $sql->select()->from($this->tableName)->join('product', 'deal.product=product.productId')->where('DATE(startDate) <= DATE(NOW())')->where('DATE(endDate) >= DATE(NOW())')->where('stock > 0');
$stmt = $sql->prepareStatementForSqlObject($select);
$results = $stmt->execute();
return $this->hydrate($results);
}
public function cleanPriorForgotRequests($userId)
{
$sql = new Sql($this->getDbAdapter(), $this->tableName);
$delete = $sql->delete();
$delete->where->equalTo($this->userField, $userId);
$statement = $sql->prepareStatementForSqlObject($delete);
$statement->execute();
return true;
}
public function count($where = [])
{
$db = $this->getAdapter();
$sql = new Sql\Sql($db);
$select = $sql->select()->from($this->getTable())->columns(array('count' => new Sql\Expression('COUNT(*)')))->where($where);
$statement = $sql->prepareStatementForSqlObject($select);
$rowset = $statement->execute();
return $rowset->current()['count'];
}
public function delete(PostInterface $postObject)
{
$action = new Delete($postObject);
$action->where(array('id=?' => $postObject->getId()));
$sql = new Sql($this->Adapter);
$stmt = $sql->prepareStatementForSqlObject($action);
$result = $stmt->execute();
return (bool) $result->getAffectedRows();
}
public function getAllCmsItemsOfFolder($id)
{
$sql = new Sql($this->getAdapter());
$select = $sql->select();
$select->from('cms_item')->where(array('fk_cms_folder' => $id));
$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();
return $result;
}
/**
* Deletes a record.
* @param $id
* @throws Exception if no records are deleted
* @return $this
*/
public function delete($id)
{
$adapter = $this->getServiceLocator()->get('Zend\\Db\\Adapter\\Adapter');
$sql = new Sql($adapter);
$delete = $sql->delete('room')->where(array('id' => $id));
$res = $sql->prepareStatementForSqlObject($delete)->execute();
if ($res->getAffectedRows() < 1) {
throw new \Exeption('Room not deleted: DB affected rows count=0.');
}
}
public function getTotal($filter = null)
{
// Эта инструкция не работает, так как к KeyTableGateway подключен AbstractRowGateway как фича и он требует, чтобы
// в результате запроса было поле первичного ключа, а там только поле count(*) as count.
//return $this->legalFormTable->getTotal($filter);
$sql = new Sql($this->adapter);
$select = $sql->select()->from("legal_form")->columns([count => new Expression("count(*)")]);
$totalSet = $sql->prepareStatementForSqlObject($select)->execute();
return (int) $totalSet->current()[count];
}
/**
* @return array|PostInterface[]
*/
public function findAll()
{
$sql = new Sql($this->dbAdapter);
$select = $sql->select('posts');
$stmt = $sql->prepareStatementForSqlObject($select);
$result = $stmt->execute();
// return $result;
\Zend\Debug\Debug::dump($result);
die;
}
/**
* action index
* @return \Zend\View\Model\ViewModel
*/
public function indexAction()
{
/**
* função anônima para var_dump estilizado
*/
$myVarDump = function ($nome_linha = "Nome da Linha", $data = null, $caracter = ' - ') {
echo str_repeat($caracter, 100) . '<br/>' . ucwords($nome_linha) . '<pre><br/>';
var_dump($data);
echo '</pre>' . str_repeat($caracter, 100) . '<br/><br/>';
};
/**
* Uso de cache
*/
if (!$this->cache()->hasItem('nome')) {
$myVarDump("Registro de Cache Agora", $this->cache()->setItem('nome', 'igor'));
} else {
$myVarDump("Cache Existente", $this->cache()->getItem('nome'));
}
/**
* conexão com banco
*/
/* $adapter = new Adaptador(array( // alias use Zend\Db\Adapter\Adapter as Adaptador
'driver' => 'Pdo_Mysql',
'database' => 'agenda_contatos',
'username' => 'tutorialzf2',
'password' => '!nF&rn0d3d4nt&'
));*/
$adapter = $this->getServiceLocator()->get('AdapterDb');
/**
* obter nome do schema do nosso banco
*/
$myVarDump("Nome Schema", $adapter->getCurrentSchema());
/**
* contar quantidade de elementos da nossa tabela
*/
$myVarDump("Quantidade elementos tabela contatos", $adapter->query("SELECT * FROM `contatos`")->execute()->count());
/**
* montar objeto sql e executar
*/
$sql = new Sql($adapter);
// use Zend\Db\Sql\Sql
$select = $sql->select()->from('contatos');
$statement = $sql->prepareStatementForSqlObject($select);
$resultsSql = $statement->execute();
$myVarDump("Objet Sql com Select executado", $resultsSql);
/**
* motar objeto resultset com objeto sql e mostrar resultado em array
*/
$resultSet = new \Zend\Db\ResultSet\ResultSet();
// nao necessita do use devido a sintaxe iniciando em \
$resultSet->initialize($resultsSql);
$myVarDump("Resultado do Objeto Sql para Array ", $resultSet->toArray());
die;
}
public function saveHuntClubsHasPlayers($data)
{
$adapter = $this->tableGateway->getAdapter();
$sql = new Sql($adapter);
$insert = $sql->insert('hunt_clubs_has_players');
$insert->values($data);
$statement = $sql->prepareStatementForSqlObject($insert);
$results = $statement->execute();
$id = $adapter->getDriver()->getLastGeneratedValue();
return $id;
}
public function getComments($where = null)
{
$sql = new Sql($this->tableGateway->adapter);
$select = $sql->select();
$select->from('comments');
$select->where($where);
$select->order('id DESC');
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();
return $results;
}
public function fetchGrid($data = array())
{
$sql = new Sql($this->tableGateway->getAdapter());
$select = $sql->select()->from(array('tag' => 'tag'))->columns(array('*'));
if (isset($data['name']) && !empty($data['name'])) {
$select->where->literal('LOWER(tag.name) LIKE "%' . strtolower($data['name']) . '%"');
}
$select->order(array($data['order'] . ' ' . strtoupper($data['sort'])));
$statement = $sql->prepareStatementForSqlObject($select);
return iterator_to_array($statement->execute());
}
public function deleteById($cartItemId)
{
$sql = new Sql($this->getDbAdapter(), $this->tableName);
$where = new Where();
$where->equalTo($this->itemIdField, $cartItemId);
$delete = $sql->delete();
$delete->where($where);
$statement = $sql->prepareStatementForSqlObject($delete);
$result = $statement->execute();
return $result->getAffectedRows();
}
public function findReportRows()
{
$sql = new Sql($this->tableGateway->getAdapter());
$select = $sql->select();
$select->from(array("prod" => "products"))->join(array("adv" => "adverts"), "prod.id = adv.prod_id")->join(array("issue" => "issues"), "prod.id = issue.prod_id", array("jan" => new Expression("sum(CASE WHEN month(issue.issue) = 1 THEN truncate(adv.adcost,2) END)"), "feb" => new Expression("sum(CASE WHEN month(issue.issue) = 2 THEN truncate(adv.adcost,2) END)"), "mar" => new Expression("sum(CASE WHEN month(issue.issue) = 3 THEN truncate(adv.adcost,2) END)"), "apr" => new Expression("sum(CASE WHEN month(issue.issue) = 4 THEN truncate(adv.adcost,2) END)"), "may" => new Expression("sum(CASE WHEN month(issue.issue) = 5 THEN truncate(adv.adcost,2) END)"), "jun" => new Expression("sum(CASE WHEN month(issue.issue) = 6 THEN truncate(adv.adcost,2) END)"), "jul" => new Expression("sum(CASE WHEN month(issue.issue) = 7 THEN truncate(adv.adcost,2) END)"), "aug" => new Expression("sum(CASE WHEN month(issue.issue) = 8 THEN truncate(adv.adcost,2) END)"), "sep" => new Expression("sum(CASE WHEN month(issue.issue) = 9 THEN truncate(adv.adcost,2) END)"), "oct" => new Expression("sum(CASE WHEN month(issue.issue) = 10 THEN truncate(adv.adcost,2) END)"), "nov" => new Expression("sum(CASE WHEN month(issue.issue) = 11 THEN truncate(adv.adcost,2) END)"), "decem" => new Expression("sum(CASE WHEN month(issue.issue) = 12 THEN truncate(adv.adcost,2) END)")));
//especificar las columnas que queremos en el resultados de las consultas.
$select->columns(array("productName" => "name"));
$select->group('prod.id');
$statement = $sql->prepareStatementForSqlObject($select);
$rowset = $statement->execute();
return $rowset;
}
/**
* Returns the total number of rows in the result set.
*
* @return int
*/
public function count()
{
if ($this->rowCount !== null) {
return $this->rowCount;
}
$select = $this->getSelectCount();
$statement = $this->sql->prepareStatementForSqlObject($select);
$result = $statement->execute();
$row = $result->current();
$this->rowCount = (int) $row[self::ROW_COUNT_COLUMN_NAME];
return $this->rowCount;
}
/**
* Retrieves the login data for the mailbox which is used by the app
* @return AppMail
* @throws InvalidArgumentException if the mailbox doesn't exist
*/
private function getAppMailData()
{
$sql = new Sql($this->dbAdapter);
$select = $sql->select('app_mail');
$select->where(['name = ?' => 'app']);
$stmt = $sql->prepareStatementForSqlObject($select);
$result = $stmt->execute();
if ($result instanceof ResultInterface && $result->isQueryResult() && $result->getAffectedRows()) {
return $this->hydrator->hydrate($result->current(), $this->appMailPrototype);
}
throw new \InvalidArgumentException("Mail-Account with given name: app not found.");
}
public function getRolesAndResources($roleId, $resourceId)
{
$sql = new Sql($this->getDbAdapter());
$select = $sql->select();
$select->from($this->tables['user_roles']);
$select->columns([]);
$select->join($this->tables['role_resources'], sprintf('%s.role_id=%s.role_id', $this->tables['user_roles'], $this->tables['role_resources']));
$select->where(['user_id' => $roleId]);
$select->where(['resource' => $resourceId]);
$statement = $sql->prepareStatementForSqlObject($select);
return $statement->execute();
}
/**
* Delete
*
* @param Closure $where
* @return int
*/
public function delete($where)
{
$delete = $this->sql->delete();
if ($where instanceof \Closure) {
$where($delete);
} else {
$delete->where($where);
}
$statement = $this->sql->prepareStatementForSqlObject($delete);
$result = $statement->execute();
return $result->getAffectedRows();
}
public function saveConsumables($data)
{
$dbAdapter = $this->getServiceLocator()->get('Zend\\Db\\Adapter\\Adapter');
$sql = new Sql($dbAdapter);
$insert = $sql->insert('weapon_consumables_has_players');
$insert->values($data);
$statement = $sql->prepareStatementForSqlObject($insert);
$results = $statement->execute();
$id = $dbAdapter->getDriver()->getLastGeneratedValue();
//print 'I am here !' . $id; exit();
return $id;
}
