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;
}
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 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;
}
/**
* 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);
}
}
/**
*
* @param type $value
* @param type $format
* @param type $limit
* @param type $offset
* @param type $category
* @param type $key
* @return array
*/
public function searchValue($value, $format = Format::FORMAT_TEXT, $limit = 30, $offset = 0, $category = "", $key = "")
{
// init
$adapter = $this->getAdapter();
$returnArray = array();
// where
$where = new \Zend\Db\Sql\Where();
$where->like('value', $value);
if ($format != "" && !is_null($format)) {
$where->like("format", $format);
}
$sql = new \Zend\Db\Sql\Sql($adapter, $this->_tableData);
$select = $sql->select();
$select->where($where)->limit($limit)->offset($offset);
$stmt = $sql->prepareStatementForSqlObject($select);
$resultSet = new ResultSet();
$resultSet->initialize($stmt->execute());
$keyId = $this->_tableId;
foreach ($resultSet as $row) {
$returnArray[$row->{$keyId}][$row->category][$row->key] = Format::formatValue($row->value, $row->format);
}
return $returnArray;
}
/**
* Update package assignments
*
* Sets a new package on existing assignments. Updated assignments have
* their status reset to "pending" and their options (force, schedule,
* post cmd) removed.
*
* @param integer $oldPackageId package to be replaced
* @param integer $newPackageId new package
* @param bool $deployPending Update assignments with status 'pending'
* @param bool $deployRunning Update assignments with status 'running'
* @param bool $deploySuccess Update assignments with status 'success'
* @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, $deployPending, $deployRunning, $deploySuccess, $deployError, $deployGroups)
{
if (!($deployPending or $deployRunning or $deploySuccess 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);
$where->equalTo('name', 'DOWNLOAD');
// Additional filters are only necessary if not all conditions are set
if (!($deployPending and $deployRunning and $deploySuccess 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 ($deployPending) {
$filters->isNull('tvalue')->and->notIn('hardware_id', $groups);
}
if ($deployRunning) {
$filters->equalTo('tvalue', \Model\Package\Assignment::RUNNING);
}
if ($deploySuccess) {
$filters->equalTo('tvalue', \Model\Package\Assignment::SUCCESS);
}
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($where);
// @codeCoverageIgnoreStart
if ($clientConfig->getAdapter()->getPlatform()->getName() == 'MySQL') {
// MySQL does not allow subquery on the same table for DELETE
// statements. Fetch result as a list instead.
$subquery = array_column($clientConfig->selectWith($subquery)->toArray(), 'hardware_id');
}
// @codeCoverageIgnoreEnd
if ($subquery) {
// $subquery is either an SQL statement or a non-empty array.
$delete = new \Zend\Db\Sql\Where();
$delete->equalTo('ivalue', $oldPackageId)->notEqualTo('name', 'DOWNLOAD_SWITCH')->in('hardware_id', $subquery)->like('name', 'DOWNLOAD_%');
$clientConfig->delete($delete);
}
// Update package ID and reset status
$clientConfig->update(array('ivalue' => $newPackageId, 'tvalue' => \Model\Package\Assignment::PENDING, 'comments' => $now), $where);
} catch (\Exception $e) {
throw new RuntimeException($e->getMessage(), (int) $e->getCode(), $e);
}
}
