/**
* {@inheritdoc}
* @codeCoverageIgnore
*/
public function setSchema($prune = false)
{
// Reimplementation to provide a view instead of previous table
$logger = $this->_serviceLocator->get('Library\\Logger');
$database = $this->_serviceLocator->get('Database\\Nada');
if (in_array('download_enable', $database->getTableNames())) {
// Use value of "fileid" column instead of obsolete "id" for package assignments
$logger->info('Transforming package assignment IDs...');
$where = new \Zend\Db\Sql\Where();
$this->_serviceLocator->get('Database\\Table\\ClientConfig')->update(array('ivalue' => new \Zend\Db\Sql\Expression(sprintf('(SELECT CAST(fileid AS %s) FROM download_enable WHERE id = ivalue)', $database->getNativeDatatype(Column::TYPE_INTEGER, 32, true)))), $where->notEqualTo('name', 'DOWNLOAD_SWITCH')->like('name', 'DOWNLOAD%'));
$logger->info('done.');
$logger->info("Dropping table 'download_enable'...");
$database->dropTable('download_enable');
$logger->info('done.');
}
if (!in_array('download_enable', $database->getViewNames())) {
$logger->info("Creating view 'download_enable'");
$typeText = $database->getNativeDatatype(Column::TYPE_VARCHAR, 255, true);
$typeInt = $database->getNativeDatatype(Column::TYPE_INTEGER, 32, true);
$null = 'CAST(NULL AS %s)';
$sql = $this->_serviceLocator->get('Database\\Table\\Packages')->getSql();
$select = $sql->select();
$select->columns(array('id' => 'fileid', 'fileid' => 'fileid', 'info_loc' => new Literal("(SELECT tvalue FROM config WHERE name = 'BRAINTACLE_DEFAULT_INFOFILE_LOCATION')"), 'pack_loc' => new Literal("(SELECT tvalue FROM config WHERE name = 'BRAINTACLE_DEFAULT_DOWNLOAD_LOCATION')"), 'cert_path' => new Literal(sprintf($null, $typeText)), 'cert_file' => new Literal(sprintf($null, $typeText)), 'server_id' => new Literal(sprintf($null, $typeInt))), false);
$database->createView('download_enable', $sql->buildSqlString($select));
$logger->info('done.');
}
}
/**
* index Action
* Receive the search params
* Build the search query
* Generate the search result as a list
* @return type
* Author: Tahmina Khatoon
*/
public function indexAction()
{
$searchform = new UserRolesSearchForm();
$searchform->get('submit')->setValue('Search');
$select = new Select();
$order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'id';
$order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_DESCENDING;
$page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1;
$item_per_page = $this->params()->fromRoute('item_per_page') ? (int) $this->params()->fromRoute('item_per_page') : 10;
$page_range = $this->params()->fromRoute('page_range') ? (int) $this->params()->fromRoute('page_range') : 7;
$select->order($order_by . ' ' . $order);
$search_by = $this->params()->fromRoute('search_by') ? $this->params()->fromRoute('search_by') : '';
$where = new \Zend\Db\Sql\Where();
$formdata = array();
if (!empty($search_by)) {
$formdata = (array) json_decode($search_by);
if (!empty($formdata['user_id'])) {
$where->addPredicate(new \Zend\Db\Sql\Predicate\Like('user_id', '%' . $formdata['user_id'] . '%'));
}
if (!empty($formdata['role_id'])) {
$where->addPredicate(new \Zend\Db\Sql\Predicate\Like('role_id', '%' . $formdata['role_id'] . '%'));
}
}
if (!empty($where)) {
$select->where($where);
}
$paginator = $this->getUserRolesTable()->fetchAll($select, true);
$paginator->setCurrentPageNumber($page)->setItemCountPerPage($item_per_page)->setPageRange($page_range);
$totalRecord = $paginator->getTotalItemCount();
$currentPage = $paginator->getCurrentPageNumber();
$totalPage = $paginator->count();
$searchform->setData($formdata);
$this->vm->setVariables(array('flashMessages' => $this->flashMessenger()->getMessages(), 'search_by' => $search_by, 'order_by' => $order_by, 'order' => $order, 'page' => $page, 'item_per_page' => $item_per_page, 'paginator' => $paginator, 'pageAction' => 'user_roles/index', 'form' => $searchform, 'totalRecord' => $totalRecord, 'currentPage' => $currentPage, 'totalPage' => $totalPage));
return $this->vm;
}
public function indexAction()
{
$searchform = new AlbumSearchForm();
$searchform->get('submit')->setValue('Search');
$select = new Select();
$order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'id';
$order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_ASCENDING;
$page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1;
$select->order($order_by . ' ' . $order);
$search_by = $this->params()->fromRoute('search_by') ? $this->params()->fromRoute('search_by') : '';
$where = new \Zend\Db\Sql\Where();
$formdata = array();
if (!empty($search_by)) {
$formdata = (array) json_decode($search_by);
if (!empty($formdata['artist'])) {
$where->addPredicate(new \Zend\Db\Sql\Predicate\Like('artist', '%' . $formdata['artist'] . '%'));
}
if (!empty($formdata['title'])) {
$where->addPredicate(new \Zend\Db\Sql\Predicate\Like('title', '%' . $formdata['title'] . '%'));
}
}
if (!empty($where)) {
$select->where($where);
}
$album = $this->getAlbumTable()->fetchAll($select);
$totalRecord = $album->count();
$itemsPerPage = 2;
$album->current();
$paginator = new Paginator(new paginatorIterator($album));
$paginator->setCurrentPageNumber($page)->setItemCountPerPage($itemsPerPage)->setPageRange(7);
$searchform->setData($formdata);
return new ViewModel(array('search_by' => $search_by, 'order_by' => $order_by, 'order' => $order, 'page' => $page, 'paginator' => $paginator, 'pageAction' => 'album', 'form' => $searchform, 'totalRecord' => $totalRecord));
}
/**
* Retourne les particpants d'un raid.
* @param type $iIdRaid
*/
function getParticipantRaid($iIdRaid)
{
$sql = new \Zend\Db\Sql\Sql($this->getAdapter());
$oQuery = $sql->select();
$oQuery->from(array('rp' => 'raid_personnage'))->join(array('r' => 'raids'), 'r.idRaid=rp.idRaid', array(), \Zend\Db\Sql\Select::JOIN_INNER)->join(array('p' => 'personnages'), 'p.idPersonnage=rp.idPersonnage', array('personnage_nom' => 'nom', 'personnage_royaume' => 'royaume', 'ilvl'), \Zend\Db\Sql\Select::JOIN_INNER)->join(array('c' => 'classes'), 'c.idClasses=p.idClasses', array('classe_nom' => 'nom', 'classe_couleur' => 'couleur'), \Zend\Db\Sql\Select::JOIN_INNER)->join(array('rac' => 'race'), 'rac.idRace=p.idRace', array('race_nom' => 'nom'), \Zend\Db\Sql\Select::JOIN_INNER);
$where = new \Zend\Db\Sql\Where();
$where->equalTo("rp.idRaid", $iIdRaid);
$oQuery->where($where);
$oQuery->order('p.nom');
$aAllParticpantTmp = $this->fetchAllArray($oQuery);
foreach ($aAllParticpantTmp as $aValue) {
$aValue['roster'] = 0;
$aValue['apply'] = null;
$aValue['abs'] = false;
$aAllParticpant[$aValue['idPersonnage']] = $aValue;
}
$oQuery->join(array('rhp' => 'roster_has_personnage'), 'rhp.idRoster = r.idRosterTmp AND rhp.idPersonnage = rp.idPersonnage', array('apply' => 'isApply'), \Zend\Db\Sql\Select::JOIN_INNER);
$aMembreRosterTmp = $this->fetchAllArray($oQuery);
foreach ($aMembreRosterTmp as $aValue) {
$aMembreRoster[$aValue['idPersonnage']] = $aValue;
}
if (isset($aMembreRoster)) {
$aParticipantRoster = array_intersect_key($aAllParticpant, $aMembreRoster);
foreach ($aParticipantRoster as $key => $value) {
$aAllParticpant[$key]['roster'] = 1;
$aAllParticpant[$key]['apply'] = $aMembreRoster[$key]['apply'];
$aAllParticpant[$key]['abs'] = false;
}
}
return $aAllParticpant;
}
public function fetchAll($request, $paginated = false)
{
if ($paginated) {
// create a new Select object for the table album
$select = new Select('album');
$select->order($request['sort'] . " " . $request['order']);
// Search
if ($request['search']) {
$x = $request['search'] . '%';
$where = new \Zend\Db\Sql\Where();
$where->like('title', $x);
$where->or->like('artist', $x);
$select->where($where);
}
// New result set based on the Album entity
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Album());
// New pagination adapter object
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
}
$resultSet = $this->tableGateway->select();
return $resultSet;
}
/**
* Gets the 5 last Matches for User by ID
* @param type $id
* @return MatchList
*/
public function getMatchList($id)
{
$sql = new Sql($this->dbAdapter);
//Creating WHERE OR Statement!!
$where = new \Zend\Db\Sql\Where();
$where->nest()->equalTo('tblmatch.User1', $id)->or->equalTo('tblmatch.User2', $id)->unnest();
//Inserting Statement into Select with ORDER BY 'Date' DESC and TOP 5
$select = $sql->select('tblmatch')->where($where)->order('Date DESC')->limit(5);
$stmt = $sql->prepareStatementForSqlObject($select);
$result = $stmt->execute();
if ($result instanceof ResultInterface && $result->isQueryResult() && $result->getAffectedRows()) {
$array = array();
$match = new Match();
$match->user1Name = $this->getUsernameByID($result->current()['User1']);
$match->user2Name = $this->getUsernameByID($result->current()['User2']);
$match->exchangeArray($result->current());
array_push($array, $match);
// Minus 1 Because we already pushed 1 MatchObject into the Array
for ($count = $result->count() - 1; $count > 0; $count--) {
$newResult = $result->next();
$match = new Match();
$match->user1Name = $this->getUsernameByID($newResult['User1']);
$match->user2Name = $this->getUsernameByID($newResult['User2']);
$match->exchangeArray($newResult);
array_push($array, $match);
}
//Add all Matches to the MatchList
$matchList = new MatchList();
$matchList->addMatchesFromTable($array);
return $matchList;
}
return new MatchList();
}
public function getHistory($sTableName, $row_id)
{
$oWhere = new \Zend\Db\Sql\Where();
$oWhere->equalTo('table_name', $sTableName);
$oWhere->equalTo('row_id', $row_id);
$rowset = $this->select(function (Select $select) use($oWhere) {
$select->where($oWhere);
});
return $rowset->toArray();
}
public function search($profile_name)
{
$sql = new Sql($this->tableGateway->adapter);
$where = new \Zend\Db\Sql\Where();
$select = $sql->select();
$select->from('users');
$where->like('profile_name', '%' . $profile_name . '%');
$select->where($where);
$select->order('profile_name');
$select->limit(15);
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();
return $results;
}
public function addCategory($parentCategoryId = null, $categoryId, $siteId = 1)
{
$where = new \Zend\Db\Sql\Where();
$where->equalTo('website_id', $siteId)->equalTo('category_id', $categoryId);
if (null === $parentCategoryId) {
$where->isNull('parent_category_id');
} else {
$where->equalTo('parent_category_id', $parentCategoryId);
}
$table = 'catalog_category_website';
$row = array('category_id' => $categoryId, 'parent_category_id' => $parentCategoryId, 'website_id' => $siteId);
$select = $this->getSelect($table)->where($where);
$result = $this->selectOne($select);
if (false === $result) {
$this->insert($row, $table);
}
}
public function adminAction()
{
$request = $this->params()->fromQuery();
if ($request['search']) {
$search = $request['search'];
}
$prequest = $this->getRequest();
if ($prequest->isPost()) {
$search = $prequest->getPost('search');
}
if (!$request['page']) {
$page = 1;
} else {
$page = $request['page'];
}
$username = $this->params()->fromRoute('emp', 0);
//Get Records from table
$limit = 5;
//
$adapter = $this->getDbAdapter();
$sql = new Sql($adapter);
$select = $sql->select('timetable');
//table name = timetable
if ($search) {
$where = new \Zend\Db\Sql\Where();
$where->like('username', $search . "%");
$select->where($where);
}
if ($username) {
$select->where(array('username' => $username));
}
$selectString = $sql->getSqlStringForSqlObject($select);
$result = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
$count = count($result);
$select->limit($limit);
$select->offset(($page - 1) * $limit);
$selectString = $sql->getSqlStringForSqlObject($select);
$result = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
//Pagination
$pages = ceil($count / $limit);
$paginator = array('page' => $page, 'pages' => $pages);
$form = new EmployeeForm();
//Return results
return array('result' => $result, 'paginator' => $paginator, 'form' => $form, 'search' => $search, 'username' => $username);
}
public function countPhieuNhapByMaPhieuNhap($array = array())
{
$ma_phieu_nhap = $array['ma_phieu_nhap'];
$adapter = $this->tableGateway->adapter;
$sql = new Sql($adapter);
// select
$sqlSelect = $sql->select();
$sqlSelect->from(array('t1' => 'phieu_nhap'));
$sqlSelect->columns(array(new Expression('COUNT(*) as num')));
$where = new \Zend\Db\Sql\Where();
$where->like('ma_phieu_nhap', '%' . $ma_phieu_nhap . '%');
$sqlSelect->where($where);
$statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect);
$resultSets = $statement->execute();
if ($resultSets->current()) {
return $resultSets->current();
}
return false;
}
public function fetchAll($adStatus = null, $searchString = null)
{
# use a closure to manipulate the Select object
/*
$active = 1;
$resultSet = $this->tableGateway->select(
function (Select $select) use ($active) {
//$select->columns(array('id', 'name', 'type'));
$select->order('id DESC');
//$select->where(array('active'=> $active));
});
*/
// return $resultSet;
$adapter = $this->tableGateway->getAdapter();
$sql = new Sql($adapter);
$select = $sql->select();
$select->from('advertisement_logs');
$select->columns(array('id', 'user_id', 'add_id', 'date', 'status', 'totalClick' => new \Zend\Db\Sql\Expression('SUM(advertisement_logs.click_count)'), 'totalView' => new \Zend\Db\Sql\Expression('SUM(advertisement_logs.view_count)')));
$select->join('player', 'advertisement_logs.user_id = player.id ', array('name', 'email'), $type = self::JOIN_LEFT);
//$select->join('player', 'advertisement_logs.user_id = player.id ' , array('name' ,'email'),$type = self::JOIN_LEFT);
//$select->where(array('date'=> $active));
if ($searchString != null) {
$predicate = new \Zend\Db\Sql\Where();
$str_arr = explode(" - ", $searchString);
$startDate = date("Y-m-d", strtotime($str_arr[0]));
$endDate = date("Y-m-d", strtotime($str_arr[1]));
$select->where($predicate->greaterThanOrEqualTo('date', $startDate));
$select->where($predicate->lessThanOrEqualTo('date', $endDate));
//02/03/2015 - 02/18/2015
}
if ($adStatus != null) {
$select->where(array('status' => $adStatus));
}
$select->order('date DESC');
$select->group('add_id');
$statement = $sql->prepareStatementForSqlObject($select);
$rowset = $statement->execute();
$resultSet = new ResultSet();
$resultSet->initialize($rowset);
return $resultSet;
}
/**
* {@inheritDoc}
*/
public function loadStream($sourceFQCN, $sourceId, $version = null)
{
$tableGateway = $this->getTablegateway($sourceFQCN);
$sql = $tableGateway->getSql();
$where = new \Zend\Db\Sql\Where();
$where->equalTo('sourceId', $sourceId);
if (!is_null($version)) {
$where->AND->greaterThanOrEqualTo('sourceVersion', $version);
}
$select = $sql->select()->where($where)->order('sourceVersion');
$eventsData = $tableGateway->selectWith($select);
$events = array();
foreach ($eventsData as $eventData) {
$eventClass = $eventData->eventClass;
$payload = $this->getSerializer()->deserialize($eventData->payload, 'array', 'json');
$event = new $eventClass($payload, $eventData->eventId, (int) $eventData->timestamp, (double) $eventData->eventVersion);
$event->setSourceVersion((int) $eventData->sourceVersion);
$event->setSourceId($sourceId);
$events[] = $event;
}
return $events;
}
public function masterindexAction()
{
$this->layout('layout/apotravinyadmin');
$getuser = $this->forward()->dispatch('Admin\\Controller\\Index', array('action' => 'getuser'));
$this->layout()->getuser = $getuser;
// notlogin
if (!$getuser) {
$this->redirect()->toUrl(WEBPATH);
}
// //search
// $searchform = new SearchFromSlider();
// $searchform->get ( 'submit' )->setValue ( 'Search' );
// $this->layout()->formSearch = $searchform;
$select = new Select();
$order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'id';
$order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_DESCENDING;
$page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1;
$search_by = $this->params()->fromRoute('search_by') ? $this->params()->fromRoute('search_by') : '';
$select->order($order_by . ' ' . $order);
$where = new \Zend\Db\Sql\Where();
$formdata = array();
if (!empty($search_by)) {
$formdata = (array) json_decode($search_by);
if (!empty($formdata['descriptionkey'])) {
$where->addPredicate(new \Zend\Db\Sql\Predicate\Like('descriptionkey', '%' . $formdata['descriptionkey'] . '%'));
}
if (!empty($formdata['title'])) {
$where->addPredicate(new \Zend\Db\Sql\Predicate\Like('title', '%' . $formdata['title'] . '%'));
}
}
if (!empty($where)) {
$select->where($where);
}
$ContactpTable = $this->getSliderTable();
$allRecord = $ContactpTable->countAll();
$pageNull = new PageNull($allRecord);
$itemsPerPage = 20;
$pageRange = 5;
$page = $this->params()->fromRoute('page', 1);
$offset = ($page - 1) * $itemsPerPage;
$paginator = new Paginator($pageNull);
$paginator->setCurrentPageNumber($page);
$paginator->setItemCountPerPage($itemsPerPage);
$paginator->setPageRange($pageRange);
$listpr = $ContactpTable->getList($offset, $itemsPerPage);
$this->layout()->listNews = $listpr;
$this->layout()->paginator = $paginator;
$this->layout()->allRecord = $allRecord;
$this->layout()->offset = $offset;
$this->layout()->itemsPerPage = $itemsPerPage;
$all_catalogue = $this->getSliderTable()->fetchAll();
$this->layout()->catalogue = $all_catalogue;
}
public function fetchAllByIdentity($identity, $identitykey = 'email')
{
$adapter = $this->adapter;
$select = new Select();
$select->from($this->table);
$select->join('user_roles', 'user_roles.user_id = users.id', array('role_id' => 'role_id'), 'inner');
$select->join('roles', 'user_roles.role_id = roles.id', array('role_name' => 'name', 'role_label' => 'label', 'role_weight' => 'weight'), 'inner');
$where = new \Zend\Db\Sql\Where();
$where->addPredicate(new \Zend\Db\Sql\Predicate\Expression("(users." . $identitykey . " = '" . $identity . "')"));
if (!empty($where)) {
$select->where($where);
}
// echo "<pre>";
// echo $select->getSqlString();
// die();
$sql = new Sql($adapter);
$statement = $sql->getSqlStringForSqlObject($select);
$resultSet = $adapter->query($statement, $adapter::QUERY_MODE_EXECUTE);
$resultSet->buffer();
return $resultSet;
}
/**
* @param array $where ['equalTo' => ['type', 'single'], 'in' => ['id', [1, 2]], ]
* @param null $limit
* @param int $offset
* @param string $order
* @return null|\Zend\Db\ResultSet\ResultSetInterface
*/
public function getItemsWhere($where = [], $limit = null, $offset = 0, $order = 'id asc')
{
$select = $this->getSelect()->from($this->getTable());
if ($where) {
$whereObj = new \Zend\Db\Sql\Where();
foreach ($where as $key => $params) {
if (in_array($key, $this->fields)) {
if (is_array($params)) {
if ($params) {
$whereObj->in($key, $params);
} else {
// в случае когда передано $service->getItemsWhere(['id => []]);
return [];
}
} else {
$whereObj->equalTo($key, $params);
}
} else {
$allowedMethods = ['equalTo', 'notEqualTo', 'lessThan', 'greaterThan', 'lessThanOrEqualTo', 'greaterThanOrEqualTo', 'like', 'notLike', 'expression', 'literal', 'isNull', 'isNotNull', 'in', 'notIn', 'between'];
if (!in_array($key, $allowedMethods)) {
throw new \Exception("method '" . $key . "' not allowed in where.");
}
call_user_func_array([$whereObj, $key], $params);
}
}
$select->where($whereObj);
}
if ($limit !== null) {
$select->limit($limit);
}
if ($offset) {
$select->offset($offset);
}
$select->order($order);
return $this->tableGateway->selectWith($select);
}
/**
* Retourne le select query deja configuré par l'adapter et le nom de la table pour le frontend
*
* @return Zend\Db\Sql\Select
*/
public function getBaseQueryFrontend($iIdRoster)
{
try {
$sql = new \Zend\Db\Sql\Sql($this->getAdapter());
$query = $sql->select();
$query->from($this->table)->columns(array('*'));
$where = new \Zend\Db\Sql\Where();
$where->equalTo("idRosterTmp", $iIdRoster);
$query->where($where);
$query->order('date DESC');
return $query;
} catch (\Exception $exc) {
throw new DatabaseException(4000, 4, $this->_getServiceLocator(), $iIdRoster, $exc);
}
}
public function findNextInOrder($ordering, array $where = array(), $entity_name = null, $primary_key_field = null)
{
try {
$predicate = new \Zend\Db\Sql\Where();
$where[] = $predicate->lessThan('entity_ordering', $ordering);
$select = $this->getFetchSelect($where, array('ordering ' . $this->ordering), array(), null, $entity_name, $primary_key_field);
$select->limit(1);
$entity = $this->select($select)->current();
if ($entity) {
return $entity;
}
} catch (\Exception $e) {
}
return false;
}
/**
* Retourne le predicate pour la gestiond es palliers
* @param mixed $mRoster id ou nom du pallier
* @return \Zend\Db\Sql\Where
* @throws \Commun\Exception\LogException
*/
public function getPredicate($mRoster)
{
try {
if (is_string($mRoster)) {
$aPalliers = $this->getPallierPourNomRoster($mRoster);
} else {
$aPalliers = $this->getPallierPourIdRoster($mRoster);
}
if (!$aPalliers) {
$msg = sprintf($this->_getServTranslator()->translate("Aucun palier définit pour le roster [ %s ]."), $mRoster);
throw new \Commun\Exception\LogException($msg, 499, $this->_getServiceLocator(), null, $mRoster);
}
$predicateGlobal = new \Zend\Db\Sql\Where();
$predicatePallierGlobal = new \Zend\Db\Sql\Where();
foreach ($aPalliers as $key => $aPallier) {
$predicatePallier = new \Zend\Db\Sql\Where();
$predicatePallier->NEST->equalTo("r.idMode", $aPallier['idModeDifficulte'])->AND->equalTo("r.idZoneTmp", $aPallier['idZone'])->AND->equalTo("r.idRosterTmp", $aPallier['idRoster'])->UNNEST;
if ($key == 1) {
$predicatePallierGlobal->addPredicate($predicatePallier, 'OR');
} else {
$predicatePallierGlobal->addPredicate($predicatePallier);
}
}
$predicateGlobal->NEST->addPredicate($predicatePallierGlobal)->UNNEST;
return $predicatePallierGlobal;
} catch (\Exception $exc) {
throw new DatabaseException(10000, 4, $this->_getServiceLocator(), $mRoster, $exc);
}
}
/**
* Update package assignments
*
* Sets a new package on existing assignments. Updated assignments have
* their status reset to "not notified" and their options (force, schedule,
* post cmd) removed.
*
* @param integer $oldPackageId package to be replaced
* @param integer $newPackageId new package
* @param bool $deployNonnotified Update assignments with status 'not notified'
* @param bool $deploySuccess Update assignments with status 'success'
* @param bool $deployNotified Update assignments with status 'notified'
* @param bool $deployError Update assignments with status 'error'
* @param bool $deployGroups Update assignments for groups
* @throws RuntimeException if an error occurs
*/
public function updateAssignments($oldPackageId, $newPackageId, $deployNonnotified, $deploySuccess, $deployNotified, $deployError, $deployGroups)
{
if (!($deployNonnotified or $deploySuccess or $deployNotified or $deployError or $deployGroups)) {
return;
// nothing to do
}
$clientConfig = $this->_serviceManager->get('Database\\Table\\ClientConfig');
$groupInfo = $this->_serviceManager->get('Database\\Table\\GroupInfo');
$where = new \Zend\Db\Sql\Where();
$where->equalTo('ivalue', $oldPackageId);
// Additional filters are only necessary if not all conditions are set
if (!($deployNonnotified and $deploySuccess and $deployNotified and $deployError and $deployGroups)) {
$groups = $groupInfo->getSql()->select()->columns(array('hardware_id'));
$filters = new \Zend\Db\Sql\Where(null, \Zend\Db\Sql\Where::COMBINED_BY_OR);
if ($deployNonnotified) {
$filters->isNull('tvalue')->and->notIn('hardware_id', $groups);
}
if ($deploySuccess) {
$filters->equalTo('tvalue', \Model\Package\Assignment::SUCCESS);
}
if ($deployNotified) {
$filters->equalTo('tvalue', \Model\Package\Assignment::NOTIFIED);
}
if ($deployError) {
$filters->like('tvalue', \Model\Package\Assignment::ERROR_PREFIX . '%');
}
if ($deployGroups) {
$filters->in('hardware_id', $groups);
}
$where->addPredicate($filters);
}
$now = $this->_serviceManager->get('Library\\Now')->format(\Model\Package\Assignment::DATEFORMAT);
try {
// Remove DOWNLOAD_* options from updated assignments
$subquery = $clientConfig->getSql()->select()->columns(array('hardware_id'))->where(array('name' => 'DOWNLOAD', $where));
$delete = new \Zend\Db\Sql\Where();
$delete->equalTo('ivalue', $oldPackageId)->in('hardware_id', $subquery)->notEqualTo('name', 'DOWNLOAD_SWITCH')->like('name', 'DOWNLOAD_%');
$clientConfig->delete($delete);
// Update package ID and reset status
$clientConfig->update(array('ivalue' => $newPackageId, 'tvalue' => \Model\Package\Assignment::NOT_NOTIFIED, 'comments' => $now), array('name' => 'DOWNLOAD', $where));
} catch (\Exception $e) {
throw new RuntimeException($e->getMessage(), (int) $e->getCode(), $e);
}
}
/**
* Retourne les loots du raid.
* @param type $iIdRaid
*/
public function getLootRaid($iIdRaid)
{
$oQuery = $this->getQueryBaseLoot();
$oQuery->order('item_date');
// $oQuery->join(array('rhp' => 'roster_has_personnage'), 'rhp.idRoster = r.idRosterTmp AND rhp.idPersonnage = ipr.idPersonnage', array(), \Zend\Db\Sql\Select::JOIN_INNER);
$predicate = new \Zend\Db\Sql\Where();
$predicate->equalTo("r.idRaid", $iIdRaid);
$oQuery->where($predicate);
$aLoots = $this->fetchAllArray($oQuery);
foreach ($aLoots as $key => $loot) {
switch ($loot['valeur']) {
//spé
case 0:
$aLoots[$key]['spe'] = 'spé 1';
break;
//spé
//spé
case 1:
$aLoots[$key]['spe'] = 'spé 2';
break;
//spé
//spé
case 2:
$aLoots[$key]['spe'] = 'spé 3';
break;
//spé
//spé
case 3:
$aLoots[$key]['spe'] = 'spé 4';
break;
default:
$aLoots[$key]['spe'] = 'spé 1';
break;
}
}
return $aLoots;
}
})->via('GET', 'POST', 'PUT');
$app->map("/{$v}/tables/:table/rows/bulk/?", function ($table) use($acl, $ZendDb, $params, $requestPayload, $app) {
$rows = array_key_exists('rows', $requestPayload) ? $requestPayload['rows'] : false;
if (!is_array($rows) || count($rows) <= 0) {
throw new Exception(__t('rows_no_specified'));
}
$TableGateway = new TableGateway($acl, $table, $ZendDb);
$primaryKeyFieldName = $TableGateway->primaryKeyFieldName;
$rowIds = [];
foreach ($rows as $row) {
if (!array_key_exists($primaryKeyFieldName, $row)) {
throw new Exception(__t('row_without_primary_key_field'));
}
array_push($rowIds, $row[$primaryKeyFieldName]);
}
$where = new \Zend\Db\Sql\Where();
if ($app->request()->isDelete()) {
$TableGateway->delete($where->in($primaryKeyFieldName, $rowIds));
} else {
foreach ($rows as $row) {
$TableGateway->updateCollection($row);
}
}
$entries = $TableGateway->getEntries($params);
JsonView::render($entries);
})->via('POST', 'PATCH', 'PUT', 'DELETE');
$app->get("/{$v}/tables/:table/typeahead/?", function ($table, $query = null) use($ZendDb, $acl, $params, $app) {
$Table = new TableGateway($acl, $table, $ZendDb);
if (!isset($params['columns'])) {
$params['columns'] = '';
}
/**
* Easily bind role with resources
* @return type
* Author: Tahmina Khatoon
*/
public function bindRolesAction()
{
$request = $this->getRequest();
if ($request->isPost()) {
$confirm = '';
$formdata = $request->getPost();
if (!empty($formdata['role_id']) && !empty($formdata['resource_id']) && md5($formdata['role_id'] * $formdata['resource_id']) == $formdata['token']) {
$existdata = $this->getRoleResourcesTable()->getRoleResourcesByRoleIdAndResourceId($formdata['role_id'], $formdata['resource_id']);
$resourceDetails = $this->getResourcesTable()->getResources($formdata['resource_id']);
$resource_name = explode('/', $resourceDetails->name);
$group = array('index', 'search');
$formDataAll = array();
if (empty($resource_name[1]) || in_array($resource_name[1], $group)) {
$select = new Select();
$where = new \Zend\Db\Sql\Where();
$where->in('name', array($resource_name[0], $resource_name[0] . '/index', $resource_name[0] . '/search'));
$select->where($where);
$main_resources = $this->getResourcesTable()->fetchAll($select);
$total = $main_resources->count();
if ($total > 0) {
foreach ($main_resources as $row) {
$role_resources = new RoleResources();
$formDataAll[] = array('role_id' => $formdata['role_id'], 'resource_id' => $row->id);
}
}
} else {
$formDataAll[] = array('role_id' => $formdata['role_id'], 'resource_id' => $formdata['resource_id']);
}
if (!empty($formdata['status'])) {
foreach ($formDataAll as $data) {
$role_resources = new RoleResources();
$role_resources->exchangeArray($data);
$confirm = $this->getRoleResourcesTable()->saveRoleResources($role_resources);
}
} else {
foreach ($formDataAll as $data) {
$existdata = $this->getRoleResourcesTable()->getRoleResourcesByRoleIdAndResourceId($data['role_id'], $data['resource_id']);
if (!empty($existdata)) {
$id = $existdata->id;
$confirm = $this->getRoleResourcesTable()->deleteRoleResources($id);
}
}
}
}
$response = new Response();
$response->getHeaders()->addHeaderLine('Content-Type: application/json');
$response->setContent(Json::encode($confirm));
$response->setStatusCode(200);
return $response;
} else {
$select = new Select();
$order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'name';
$order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_ASCENDING;
$select->order($order_by . ' ' . $order);
$resources = $this->getResourcesTable()->fetchAll($select);
$roles = $this->getRolesTable()->fetchAll();
$primaryResources = $this->formetArray($this->getResourcesTable()->getPrimaryResources(), 'id');
$roleResources = array();
if (!empty($roles)) {
foreach ($roles as $role) {
$roleResources[$role->id] = $this->formetArray($this->getRoleResourcesTable()->getResourcesBasedOnRole($role->id), 'resource_id');
}
}
$this->vm->setVariables(array('resources' => $resources, 'roles' => $roles, 'roleResources' => $roleResources, 'primaryResources' => $primaryResources, 'order_by' => $order_by, 'order' => $order, 'pageAction' => 'resources/bind_roles'));
}
return $this->vm;
}
<?php
/** @var $adapter Zend\Db\Adapter\Adapter */
$adapter = (include file_exists('bootstrap.php') ? 'bootstrap.php' : 'bootstrap.dist.php');
refresh_data($adapter);
$where = new Zend\Db\Sql\Where();
$where->equalTo('id', 1)->OR->equalTo('id', 2);
$where->OR->NEST->like('name', 'Ralph%')->OR->greaterThanOrEqualTo('age', 30)->AND->lessThanOrEqualTo('age', 50)->UNNEST->literal('foo = ?', 'bar');
$target = <<<EOS
SELECT "foo".* FROM "foo" WHERE "id" = '1' OR "id" = '2' OR ("name" LIKE 'Ralph%' OR "age" >= '30' AND "age" <= '50') AND foo = 'bar'
EOS;
$select = new Zend\Db\Sql\Select('foo');
$select->where($where);
assert_example_works($target == $select->getSqlString());
private function dropdownMenus($id = 0)
{
$options = array();
if (empty($id)) {
$select = new Select();
$where = new \Zend\Db\Sql\Where();
$where->notEqualTo('id', (int) $id);
$select->where($where);
$options = $this->getMenusTable()->dropdownMenus($select);
} else {
$options = $this->getMenusTable()->dropdownMenus();
}
return $options;
}
protected function saveNewELOToDatabase($newELO, $userID)
{
$sql = new Sql($this->dbAdapter);
$where = new \Zend\Db\Sql\Where();
$where->nest()->equalTo('tbluser.id', $userID);
$update = $sql->update('tbluser')->set(array('ELO' => $newELO))->where($where);
$stmt = $sql->prepareStatementForSqlObject($update);
$stmt->execute();
}
public function index10Action()
{
$subObj = new Select("building");
$subObj->columns(array("cost"))->where(new Pre\Expression("city = ?", array("can tho")));
$stringSql = $this->createSqlString($subObj);
$selectObj = new Select("building");
$where = new \Zend\Db\Sql\Where();
$where->literal(sprintf("cost > ANY (%s)", $stringSql))->notEqualTo("city", "can tho");
$selectObj->columns(array("id", "name", "address", "cost"))->where($where);
$this->showResult($selectObj);
return false;
}
public function getOpponentID($matchID, $userID)
{
$sql = new Sql($this->dbAdapter);
$where = new \Zend\Db\Sql\Where();
$where->nest()->equalTo('tblmatch.matchID', $matchID)->unnest();
$select = $sql->select('tblmatch')->where($where);
$stmt = $sql->prepareStatementForSqlObject($select);
$result = $stmt->execute();
$result = $result->current();
if ($result["User1"] == $userID) {
return $result["User2"];
} else {
return $result["User1"];
}
}
public function login($name, $password)
{
$where = new \Zend\Db\Sql\Where();
$where->nest()->equalTo('name', $name)->or->equalTo('email', $name)->unnest()->and->equalTo('password', $password);
$select = $this->tableGateway->getSql()->select()->where($where)->limit(1);
$result = $this->tableGateway->getSql()->prepareStatementForSqlObject($select)->execute();
$rs = new ResultSet();
$rs->initialize($result);
return $rs->toArray();
}
public function getUserTreeMenus($role_id, $parent_id = 0)
{
$adapter = $this->adapter;
$result = array();
$select = new Select();
$select->from($this->table);
// $select->columns(array(new Expression('DISTINCT(menus.id) as id'), 'name', 'type', 'url', 'target'));
$select->join('resources', 'resources.id = menus.resource_id', array('resource_name' => 'name'), 'left');
$select->join('role_resources', 'menus.resource_id = role_resources.resource_id', array('role_id' => 'role_id'), 'left');
$where = new \Zend\Db\Sql\Where();
if ($role_id != 1) {
$where->addPredicate(new \Zend\Db\Sql\Predicate\Expression("(role_resources.role_id = {$role_id} OR menus.type = 1)"));
}
$where->equalTo('parent_id', $parent_id);
$where->equalTo('status', 1);
if (!empty($where)) {
$select->where($where);
}
$select->order('order' . ' ' . 'ASC');
$sql = new Sql($adapter);
$statement = $sql->getSqlStringForSqlObject($select);
//echo $statement;
// die();
$resultSet = $adapter->query($statement, $adapter::QUERY_MODE_EXECUTE);
$resultSet->buffer();
if (!empty($resultSet)) {
$exist_ids = array();
foreach ($resultSet as $key => $data) {
if (!in_array($data->id, $exist_ids)) {
$exist_ids[] = $data->id;
$result[$key]['data'] = $data;
$result[$key]['child'] = $this->getUserTreeMenus($role_id, $data->id);
}
}
}
return $result;
}
