public function findAlbumByTest()
{
$sql = new \Zend\Db\Sql\Sql($this->tableGateway->getAdapter());
$select = $sql->select();
$select->from(array('a' => 'album'))->join(array('t' => 'tests'), 't.test = a.title');
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();
return $results;
}
/**
* @param string $email
* @return \NekoPHP\Modules\User\Models\User
*/
public static function getByEmail($email)
{
$db = parent::createZendDb();
$sql = new \Zend\Db\Sql\Sql($db);
$query = $sql->select()->from(parent::getTable())->columns(['id'])->where(['email' => $email]);
$stmt = $sql->prepareStatementForSqlObject($query);
$results = $stmt->execute();
if ($results->count() != 1) {
return null;
}
$row = $results->next();
return new self($row['id']);
}
/**
* Zend db query, access
*
* @param Zend\Db\Sql\Select
* @return statement result object
*/
public function query($select)
{
$adapter = $this->getAdapter();
$zendSql = new Zend\Db\Sql\Sql($adapter);
if (isTraining()) {
di('log')->sql($select->getSqlString($adapter->getPlatform()));
}
$this->error = null;
try {
$statement = $zendSql->prepareStatementForSqlObject($select);
$results = $statement->execute();
} catch (Exception $e) {
$this->error = $e;
return false;
}
return $results;
}
/**
* @return bool
*/
public function update()
{
$id = $this->getIdentifier();
$db = $this->createZendDb();
$sql = new \Zend\Db\Sql\Sql($db);
$query = $sql->update()->table(self::getTable())->set($this->fields)->where([$id['column'] => $id['value']]);
try {
$stmt = $sql->prepareStatementForSqlObject($query);
$result = $stmt->execute();
} catch (\Exception $e) {
$this->exception = $e;
return false;
}
return true;
}
/**
* @param $postId int
*
* @return void
*/
public function delete($postId)
{
$sql = new \Zend\Db\Sql\Sql($this->adapter);
$delete = $sql->delete()->from('news')->where(array('id' => $postId));
$statement = $sql->prepareStatementForSqlObject($delete);
$statement->execute();
}
/**
* Return clients matching criteria
*
* @param array $properties Properties to be returned. If empty or null, return all properties.
* @param string $order Property to sort by
* @param string $direction One of [asc|desc]
* @param string|array $filter Name or array of names of a pre-defined filter routine
* @param string|array $search Search parameter(s) passed to the filter. May be case sensitive depending on DBMS.
* @param string|array $operators Comparison operator(s)
* @param bool|array $invert Invert query results (return clients not matching criteria)
* @param bool $addSearchColumns Add columns with search criteria (default), otherwise only columns from $properties
* @param bool $distinct Force distinct results.
* @param bool $query Perform query and return result set (default), return \Zend\Db\Sql\Select object otherwise.
* @return \Zend\Db\ResultSet\AbstractResultSet|\Zend\Db\Sql\Select Query result or Query object
* @throws \InvalidArgumentException if a filter or order column is invalid
* @throws \LogicException if $invertResult is not supported by a filter or type of custom field is not supported
*/
public function getClients($properties = null, $order = null, $direction = 'asc', $filter = null, $search = null, $operators = null, $invert = null, $addSearchColumns = true, $distinct = false, $query = true)
{
$clients = $this->_serviceLocator->get('Database\\Table\\Clients');
$map = $clients->getHydrator()->getExtractorMap();
$fromClients = array();
if (empty($properties)) {
$properties = array_keys($map);
// Select all properties
}
foreach ($properties as $property) {
if (isset($map[$property])) {
$fromClients[] = $map[$property];
} elseif (preg_match('/^Windows\\.(.*)/', $property, $matches)) {
$column = $this->_serviceLocator->get('Database\\Table\\WindowsInstallations')->getHydrator()->extractName($matches[1]);
$fromWindows["windows_{$column}"] = $column;
}
// Ignore other properties. They might get added by a filter.
}
// add PK if not already selected
if (!in_array('id', $fromClients)) {
$fromClients[] = 'id';
}
// Set up Select object manually instead of pulling it from a table
// gateway because the base table might be changed later.
$sql = new \Zend\Db\Sql\Sql($this->_serviceLocator->get('Db'));
$select = $sql->select();
$select->from('clients');
$select->columns($fromClients);
if ($distinct) {
$select->quantifier('DISTINCT');
}
if (isset($fromWindows)) {
// Use left join because there might be no matching row in the 'windows_installations' table.
$select->join('windows_installations', 'windows_installations.client_id = clients.id', $fromWindows, Select::JOIN_LEFT);
}
// apply filters
if (!is_array($filter)) {
// convert to array if necessary
$filter = array($filter);
$search = array($search);
$operators = array($operators);
$invert = array($invert);
}
foreach ($filter as $index => $type) {
$arg = $search[$index];
$operator = $operators[$index];
$matchExact = $operator == 'eq';
$invertResult = $invert[$index];
switch ($type) {
case '':
break;
// No filter requested
// No filter requested
case 'Id':
if ($invertResult) {
throw new \LogicException("invertResult cannot be used on Id filter");
}
$select->where(array('clients.id' => $arg));
break;
case 'AssetTag':
case 'BiosDate':
case 'BiosVersion':
case 'CpuType':
case 'DnsServer':
case 'DefaultGateway':
case 'Manufacturer':
case 'Name':
case 'OsName':
case 'OsVersionNumber':
case 'OsVersionString':
case 'OsComment':
case 'ProductName':
case 'Serial':
case 'UserAgent':
case 'UserName':
$select = $this->_filterByString($select, 'Client', $type, $arg, $matchExact, $invertResult, $addSearchColumns);
break;
case 'CpuClock':
case 'CpuCores':
case 'PhysicalMemory':
case 'SwapMemory':
$select = $this->_filterByOrdinal($select, 'Client', $type, $arg, $operator, $invertResult, $addSearchColumns);
break;
case 'InventoryDate':
case 'LastContactDate':
$select = $this->_filterByDate($select, 'Client', $type, $arg, $operator, $invertResult, $addSearchColumns);
break;
case 'PackagePending':
case 'PackageRunning':
case 'PackageSuccess':
case 'PackageError':
if ($invertResult) {
throw new \LogicException("invertResult cannot be used on {$type} filter");
}
$select = $this->_filterByPackage($select, $type, $arg, $addSearchColumns);
break;
case 'Software':
if ($invertResult) {
throw new \LogicException("invertResult cannot be used on Software filter");
}
$select->quantifier('DISTINCT')->join('softwares', 'softwares.hardware_id = clients.id', $addSearchColumns ? array('software_version' => 'version') : array())->where(array('softwares.name' => $arg));
break;
case 'MemberOf':
if ($invertResult) {
throw new \LogicException("invertResult cannot be used on MemberOf filter");
}
// $arg is expected to be a \Model\Group\Group object.
$arg->update();
$select->join('groups_cache', 'groups_cache.hardware_id = clients.id', $addSearchColumns ? array('static') : array())->where(array('groups_cache.group_id' => $arg['Id'], new Predicate\In('groups_cache.static', array(Client::MEMBERSHIP_AUTOMATIC, Client::MEMBERSHIP_ALWAYS))));
break;
case 'ExcludedFrom':
if ($invertResult) {
throw new \LogicException("invertResult cannot be used on ExcludedFrom filter");
}
// $arg is expected to be a \Model\Group\Group object.
$arg->update();
$select->join('groups_cache', 'groups_cache.hardware_id = clients.id', array())->where(array('groups_cache.group_id' => $arg['Id'], 'groups_cache.static' => \Model\Client\Client::MEMBERSHIP_NEVER));
break;
case 'Filesystem.Size':
case 'Filesystem.FreeSpace':
// Generic integer filter
list($model, $property) = explode('.', $type);
$select = $this->_filterByOrdinal($select, $model, $property, $arg, $operator, $invertResult, $addSearchColumns);
break;
default:
if (preg_match('#^CustomFields\\.(.*)#', $type, $matches)) {
$property = $matches[1];
$fieldType = $this->_serviceLocator->get('Model\\Client\\CustomFieldManager')->getFields()[$property];
switch ($fieldType) {
case 'text':
case 'clob':
$select = $this->_filterByString($select, 'CustomFields', $property, $arg, $matchExact, $invertResult, $addSearchColumns);
break;
case 'integer':
case 'float':
$select = $this->_filterByOrdinal($select, 'CustomFields', $property, $arg, $operator, $invertResult, $addSearchColumns);
break;
case 'date':
$select = $this->_filterByDate($select, 'CustomFields', $property, $arg, $operator, $invertResult, $addSearchColumns);
break;
default:
throw new \LogicException('Unsupported type: ' . $fieldType);
}
} elseif (preg_match('/^Registry\\.(.+)/', $type, $matches)) {
$property = $matches[1];
$select = $this->_filterByString($select, 'Registry', $property, $arg, $matchExact, $invertResult, $addSearchColumns);
} elseif (preg_match('/^([a-zA-Z]+)\\.([a-zA-Z]+)$/', $type, $matches)) {
// apply a generic string filter.
$select = $this->_filterByString($select, $matches[1], $matches[2], $arg, $matchExact, $invertResult, $addSearchColumns);
} else {
// Filter must be of the form 'Model.Property'.
throw new \InvalidArgumentException('Invalid filter: ' . $type);
}
}
}
if ($order) {
if (isset($map[$order])) {
$order = "clients.{$map[$order]}";
} elseif ($order == 'Membership') {
$order = 'groups_cache.static';
} elseif (preg_match('/^CustomFields\\.(.+)/', $order, $matches)) {
$order = 'customfields_' . $this->_serviceLocator->get('Model\\Client\\CustomFieldManager')->getColumnMap()[$matches[1]];
} elseif (preg_match('/^Windows\\.(.+)/', $order, $matches)) {
$hydrator = $this->_serviceLocator->get('Database\\Table\\WindowsInstallations')->getHydrator();
$order = 'windows_' . $hydrator->extractName($matches[1]);
} elseif (preg_match('/^Registry\\./', $order)) {
$order = 'registry_content';
} elseif (preg_match('/^([a-zA-Z]+)\\.([a-zA-Z]+)$/', $order, $matches)) {
$model = $matches[1];
$property = $matches[2];
// Assume column alias 'model_column'
$tableGateway = $this->_serviceLocator->get('Model\\Client\\ItemManager')->getTable($model);
$column = $tableGateway->getHydrator()->extractName($property);
$order = strtolower("{$model}_{$column}");
} else {
throw new \InvalidArgumentException('Invalid order: ' . $order);
}
$select->order(array($order => $direction));
}
/*
* Try to optimize the query by removing unnecessary JOINs. The query
* can be rewritten if all of the following conditions are met:
* - There is exactly 1 joined table.
* - The tables are joined by an inner join.
* - The only column from the 'clients' table is 'id'.
* In that case, clients.id can be replaced by the 'hardware_id' or
* 'client_id' column from the joined table.
*/
$joinedTables = $select->getRawState(Select::JOINS)->getJoins();
$clientColumns = $select->getRawState(Select::COLUMNS);
if (count($joinedTables) == 1 and $clientColumns == array('id')) {
$joinedTable = $joinedTables[0];
if ($joinedTable['type'] == Select::JOIN_INNER) {
$joinColumn = strpos($joinedTable['on'], 'client_id') === false ? 'hardware_id' : 'client_id';
// Add column with "id" alias
$columns = $joinedTable['columns'];
$columns['id'] = $joinColumn;
// Replace columns and table
$select->from($joinedTable['name']);
$select->columns($columns);
$select->reset(Select::JOINS);
// Replace possible clients.id in ORDER BY clause
$orderSpec = array();
foreach ($select->getRawState(Select::ORDER) as $column => $direction) {
if ($column == 'clients.id') {
$column = $joinColumn;
}
$orderSpec[$column] = $direction;
}
$select->reset(Select::ORDER);
$select->order($orderSpec);
}
}
if ($query) {
$resultSet = clone $clients->getResultSetPrototype();
$resultSet->initialize($sql->prepareStatementForSqlObject($select)->execute());
return $resultSet;
} else {
return $select;
}
}
public function ajaxArrayAction()
{
$table = new TableExample\ArrayAdapter();
$sql = new \Zend\Db\Sql\Sql($this->getDbAdapter());
$stmt = $sql->prepareStatementForSqlObject($this->getSource());
$res = $stmt->execute();
$resultSet = new \Zend\Db\ResultSet\ResultSet();
$resultSet->initialize($res);
$source = $resultSet->toArray();
$table->setAdapter($this->getDbAdapter())->setSource($source)->setParamAdapter($this->getRequest()->getPost());
return $this->htmlResponse($table->render());
}
/**
* Product list
*
* @return \ZfcDatagrid\Datagrid
*/
public function getDatagrid()
{
$eavProduct = new \Product\Model\EavProduct($this->tableGateway);
$records = array();
$result = array();
$customAttributes = array();
$grid = $this->getGrid();
$grid->setId('productGrid');
$dbAdapter = $this->adapter;
$select = new Select();
$select->from(array('p' => 'product'));
// execute the query
$sql = new \Zend\Db\Sql\Sql($this->adapter);
$stmt = $sql->prepareStatementForSqlObject($select);
$results = $stmt->execute();
// execute the main query
$records = $this->tableGateway->select($select);
// load the attributes from the preferences
$columnsAttributesIdx = $this->settings->getValueByParameter('product', 'attributes');
if (!empty($columnsAttributesIdx)) {
// get from the database the custom attributes set as product preferences ($columnsAttributesIdx)
$selectedAttributes = $this->attributes->findbyIdx(json_decode($columnsAttributesIdx, true));
$selectedAttributes->buffer();
// Get the selected product attribute values ONLY
$attributes = $eavProduct->loadAttributes($results, $selectedAttributes);
$attributesValues = $attributes->toArray();
// loop the selected product attribute records
foreach ($attributesValues as $recordId => $attributeValue) {
// loop the record selected values
foreach ($attributeValue as $id => $value) {
// get the attribute information
$theAttribute = $eavProduct->getAttribute($id);
// create a temporary array of data to merge with the main datagrid array
$customAttributes[$recordId][$theAttribute->getName()] = $value;
// Create a custom column on the grid
$col = new Column\Select($theAttribute->getName());
$col->setLabel(_($theAttribute->getLabel()));
$grid->addColumn($col);
}
}
// Merge the temporary array with the main datagrid array
foreach ($records as $record) {
$result[] = array_merge($record->getArrayCopy(), $customAttributes[$record->getId()]);
}
}
$RecordsPerPage = $this->settings->getValueByParameter('product', 'recordsperpage');
$grid->setDefaultItemsPerPage($RecordsPerPage);
$grid->setDataSource($result);
$colId = new Column\Select('id');
$colId->setLabel('Id');
$colId->setIdentity();
$grid->addColumn($colId);
$colType = new Type\DateTime('Y-m-d H:i:s', \IntlDateFormatter::SHORT, \IntlDateFormatter::SHORT);
$colType->setSourceTimezone('Europe/Rome');
$colType->setOutputTimezone('UTC');
$colType->setLocale('it_IT');
$col = new Column\Select('createdat');
$col->setType($colType);
$col->setLabel(_('Created At'));
$col->setWidth(15);
$grid->addColumn($col);
// Add actions to the grid
$showaction = new Column\Action\Button();
$showaction->setAttribute('href', "/admin/product/edit/" . $showaction->getColumnValuePlaceholder(new Column\Select('id')));
$showaction->setAttribute('class', 'btn btn-xs btn-success');
$showaction->setLabel(_('edit'));
$delaction = new Column\Action\Button();
$delaction->setAttribute('href', '/admin/product/delete/' . $delaction->getRowIdPlaceholder());
$delaction->setAttribute('onclick', "return confirm('Are you sure?')");
$delaction->setAttribute('class', 'btn btn-xs btn-danger');
$delaction->setLabel(_('delete'));
$col = new Column\Action();
$col->addAction($showaction);
$col->addAction($delaction);
$grid->addColumn($col);
$grid->setToolbarTemplate('');
return $grid;
}
