/**
* 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;
}
/**
* @dataProvider findProvider
*/
public function testFind($criteria, $order, $direction, $clearBlacklist, $expectedOrder, $expectedResult)
{
if ($clearBlacklist) {
static::$serviceManager->get('Database\\Table\\DuplicateMacAddresses')->delete(true);
static::$serviceManager->get('Database\\Table\\DuplicateSerials')->delete(true);
static::$serviceManager->get('Database\\Table\\DuplicateAssetTags')->delete(true);
}
$ordercolumns = array('Id' => 'clients.id', 'Name' => 'clients.name', 'NetworkInterface.MacAddress' => 'networkinterface_macaddr');
$sql = new \Zend\Db\Sql\Sql(static::$serviceManager->get('Db'), 'clients');
$select = $sql->select()->columns(array('id', 'name', 'lastcome', 'ssn', 'assettag'))->order(array($ordercolumns[$order] => $direction));
$clientManager = $this->createMock('Model\\Client\\ClientManager');
$clientManager->method('getClients')->with(array('Id', 'Name', 'LastContactDate', 'Serial', 'AssetTag'), $order, $direction, null, null, null, null, false, false, false)->willReturn($select);
$clients = $this->getMockBuilder('Database\\Table\\Clients')->disableOriginalConstructor()->setMethods(array('getSql', 'selectWith'))->getMock();
$clients->method('getSql')->willReturn($sql);
$clients->method('selectWith')->with($this->callback(function ($select) use($expectedOrder) {
return $select->getRawState($select::ORDER) == $expectedOrder;
}))->willReturnCallback(function ($select) use($sql) {
// Build simple result set to bypass hydrator
$resultSet = new \Zend\Db\ResultSet\ResultSet();
$resultSet->initialize($sql->prepareStatementForSqlObject($select)->execute());
return $resultSet;
});
$duplicates = $this->_getModel(array('Database\\Table\\Clients' => $clients, 'Model\\Client\\ClientManager' => $clientManager));
$resultSet = $duplicates->find($criteria, $order, $direction);
$this->assertInstanceOf('Zend\\Db\\ResultSet\\AbstractResultSet', $resultSet);
$this->assertEquals($expectedResult, $resultSet->toArray());
}
public function getPerson($where)
{
$sql = new \Zend\Db\Sql\Sql($this->getAdapter());
$select = $sql->select();
$select->from(array('p' => 'person'));
$select->join(array('g' => 'group'), 'g.id = p.primaryGroupId', 'name', 'left');
return $select;
}
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;
}
/**
*/
private function _processDdl()
{
// this will turn off buffering,
// so we see outout right away
while (ob_get_level() > 0) {
ob_end_flush();
}
/*
*
* $table = new \Zend\Db\Sql\Ddl\CreateTable ( 'keyper_bar' );
*
* $table->addColumn ( new \Zend\Db\Sql\Ddl\Column\Integer ( 'id' ) );
* $table->addColumn ( new \Zend\Db\Sql\Ddl\Column\Varchar ( 'name', 200 ) );
* $table->addConstraint ( new \Zend\Db\Sql\Ddl\Constraint\PrimaryKey ( 'id' ) );
*
* $sql = new \Zend\Db\Sql\Sql ( $this->adapter );
*
* $this->adapter->query ( $sql->getSqlStringForSqlObject ( $table ), \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE );
*/
echo "<div>";
try {
require_once DOC_ROOT . "/config/schema/ddl.php";
foreach ($schema as $key => $value) {
$this->validateTable($key);
$this->createTable($key, $prefix);
// create table
$this->createColumn("id", "integer", 11, true);
if (count($value['associate'])) {
foreach ($value['associate'] as $key_3 => $value_3) {
$this->table->addConstraint(new \Zend\Db\Sql\Ddl\Constraint\ForeignKey($value_3, $value_3, $prefix . $key_3, "id", "cascade", "cascade"));
}
}
// create columns
foreach ($value['columns'] as $key_2 => $value_2) {
$col = $this->validateColumn($value_2[0]);
$data_type = $this->validateDataType($value_2[1]['data_type'][0]);
$data_type_param = $data_type == "varchar" ? 200 : $value_2[1]['data_type'][1];
$this->createColumn($col, $data_type, $data_type_param, false);
}
// execute now
$sql = new \Zend\Db\Sql\Sql($this->adapter);
echo $sql->getSqlStringForSqlObject($this->table);
echo "<br>";
$this->adapter->query($sql->getSqlStringForSqlObject($this->table), \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
// create physical file now
$this->generateClassFile($key, $value);
}
} catch (\Exception $e) {
echo "<span class=''>{$e->getMessage()}</span>";
}
echo "</div>";
}
/**
* @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']);
}
public function search($nome)
{
// preparar objeto SQL
$adapter = $this->tableGateway->getAdapter();
$sql = new \Zend\Db\Sql\Sql($adapter);
// montagem do select com where, like e limit para tabela contatos
$select = (new \Zend\Db\Sql\Select('contatos'))->limit(8);
$select->columns(array('id', 'nome'))->where->like('nome', "%{$nome}%");
// executar select
$statement = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($statement, $adapter::QUERY_MODE_EXECUTE);
return $results;
}
/**
*
* @param unknown $schema
*/
private function dropDb()
{
$metadata = new \Zend\Db\Metadata\Metadata($this->adapter);
// get the table names
$tableNames = $metadata->getTableNames();
$this->adapter->query('SET foreign_key_checks = 0')->execute();
foreach ($tableNames as $tableName) {
$sql = new \Zend\Db\Sql\Sql($this->adapter);
$drop = new \Zend\Db\Sql\Ddl\DropTable($tableName);
@$this->adapter->query($sql->getSqlStringForSqlObject($drop), \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
}
$this->adapter->query('SET foreign_key_checks = 1')->execute();
}
public function getEmailByCredential($credential)
{
$adapter = $this->adapter;
$sql = new \Zend\Db\Sql\Sql($adapter);
$select = $sql->select();
$select->from('user');
$select->where(sprintf('username = "******" OR email = "%s"', $credential, $credential));
//$select->where(function (\Zend\Db\Sql\Where $where) { $where->greaterThanOrEqualTo('ts.cfp_closed', date('Y-m-d H:i:s'));});
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
if ($results->count() != 1) {
return false;
}
return $results->current()->email;
}
public function indexAction()
{
//默认Action
//test db library
$dbObj = new Db_Table('test', 'user');
$res = $dbObj->getCount('id=3');
var_dump($res);
$field = array();
$whereArr = array('order' => 'id', 'limit' => '3');
$res = $dbObj->getAll($field, $whereArr);
var_dump($res);
echo "</br>";
//test smarty library
$this->getView()->assign("contentaa", "Hello Worldssss");
//test zendDb library
$adapter = Yaf_Registry::get('db');
//use adapter
// $qi = function ($name) use ($adapter) {
// return $adapter->platform->quoteIdentifier($name);
// };
// $fp = function ($name) use ($adapter) {
// return $adapter->driver->formatParameterName($name);
// };
// $sql = 'select * from ' . $qi('usertest') . ' where' . $qi('user_id') . ' <' . $fp('user_id');
// $statement = $adapter->query($sql);
// $results = $statement->execute(array(
// 'user_id' => 5
// ));
// //$row = $results->current();
// $resultSet = new Zend\Db\ResultSet\ResultSet;
// $resultSet->initialize($results);
// var_dump($results->toArray());
//use sql
$sql = new Zend\Db\Sql\Sql($adapter);
$select = $sql->select();
$select->from('usertest');
$select->where(array('user_id <3'));
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
// var_dump($results);
var_dump($results->toArray());
echo "</br>";
// var_dump($results);
// $resultSet = new Zend\Db\ResultSet\ResultSet;
// $resultSet->initialize($results);
}
/**
* 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;
}
/**
* Retourne une liste de personnage correspondant au roles et au roster.
* @param type $idRole
* @param type $idRoster
* @return type
*/
function getListePersonnage($idRole = null, $idRoster)
{
$sql = new \Zend\Db\Sql\Sql($this->getAdapter());
$oQuery = $sql->select();
$oQuery->columns(array('isApply', 'idRoster'))->from(array('rhp' => 'roster_has_personnage'));
$oQuery->join(array('p' => 'personnages'), 'rhp.idPersonnage = p.idPersonnage', array('nom', 'idPersonnage', 'royaume', 'ilvl'), \Zend\Db\Sql\Select::JOIN_INNER);
$oQuery->join(array('c' => 'classes'), 'c.idClasses = p.idClasses', array('classe' => 'nom', 'idClasses', 'couleur'), \Zend\Db\Sql\Select::JOIN_INNER);
$oQuery->join(array('r' => 'role'), 'rhp.idRole = r.idRole', array('role' => 'nom', 'idRole'), \Zend\Db\Sql\Select::JOIN_INNER);
$oQuery->join(array('g' => 'guildes'), 'g.idGuildes = p.idGuildes', array('guilde' => 'nom', 'idGuildes'), \Zend\Db\Sql\Select::JOIN_LEFT);
if ($idRole) {
$oQuery->where("rhp.idRole= '{$idRole} '");
}
$oQuery->where("rhp.idRoster = '{$idRoster} '");
$oQuery->order('nom');
// $this->debug($oQuery);
$aReturn = $this->fetchAllArray($oQuery);
return $aReturn;
}
/**
* Set group members based on query
*
* If $type is \Model\Client\Client::MEMBERSHIP_AUTOMATIC,
* the DynamicMembersSql property will be set to the resulting query. For
* other values, the query will be executed and $type is stored as manual
* membership/exclusion on the results.
*
* The query arguments are passed to \Model\Client\ClientManager::getClients().
*
* @param integer $type Membership type
* @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
* @param string|array $operator Comparision operator
* @param bool|array $invert Invert query results (return all clients NOT matching criteria)
* @throws \LogicException if the query does not yield exactly 1 column (internal validation, should never fail)
*/
public function setMembersFromQuery($type, $filter, $search, $operator, $invert)
{
$id = $this['Id'];
$members = $this->serviceLocator->get('Model\\Client\\ClientManager')->getClients(array('Id'), null, null, $filter, $search, $operator, $invert, false, true, $type != \Model\Client\Client::MEMBERSHIP_AUTOMATIC);
if ($type == \Model\Client\Client::MEMBERSHIP_AUTOMATIC) {
$numCols = count($members->getRawState(\Zend\Db\Sql\Select::COLUMNS));
foreach ($members->getRawState(\Zend\Db\Sql\Select::JOINS) as $join) {
$numCols += count($join['columns']);
}
if ($numCols != 1) {
throw new \LogicException('Expected 1 column, got ' . $numCols);
}
$sql = new \Zend\Db\Sql\Sql($this->serviceLocator->get('Db'));
$query = $sql->buildSqlString($members);
$this->serviceLocator->get('Database\\Table\\GroupInfo')->update(array('request' => $query), array('hardware_id' => $id));
$this->offsetSet('DynamicMembersSql', $query);
$this->update(true);
// Force cache update, effectively validating query
} else {
// Wait until lock can be obtained
while (!$this->lock()) {
sleep(1);
}
// Get list of existing memberships
$existingMemberships = array();
$groupMemberships = $this->serviceLocator->get('Database\\Table\\GroupMemberships');
$select = $groupMemberships->getSql()->select();
$select->columns(array('hardware_id', 'static'))->where(array('group_id' => $id));
foreach ($groupMemberships->selectWith($select) as $membership) {
$existingMemberships[$membership['hardware_id']] = $membership['static'];
}
// Insert/update membership entries
$connection = $groupMemberships->getAdapter()->getDriver()->getConnection();
$connection->beginTransaction();
try {
foreach ($members as $member) {
$member = $member['Id'];
if (isset($existingMemberships[$member])) {
// Update only memberships of a different type
if ($existingMemberships[$member] != $type) {
$groupMemberships->update(array('static' => $type), array('group_id' => $id, 'hardware_id' => $member));
}
} else {
$groupMemberships->insert(array('group_id' => $id, 'hardware_id' => $member, 'static' => $type));
}
}
$connection->commit();
} catch (\Exception $exception) {
$connection->rollBack();
$this->unlock();
throw $exception;
}
$this->unlock();
}
}
/**
* Retourne une liste des roster avec les paramêtres passé.
* Les paramatres par defaut sont:
* $aParam = array(
* 'rech' => $aParam["rech"],
* 'champs_mode' => array(
* 'idMode',
* 'nom'
* ),
* 'limit' => 20
* );
*
* @param type $aParam
* @return array
*/
function getAutoComplete($aParam)
{
if (!isset($aParam)) {
$aParam = array('rech' => $aParam["rech"], 'champs_mode' => array('idMode', 'nom'), 'limit' => 20);
}
if (!isset($aParam['champs_mode'])) {
$aParam['champs_mode'] = array('idMode', 'nom');
}
$sql = new \Zend\Db\Sql\Sql($this->getAdapter());
$oQuery = $sql->select();
$oQuery->columns($aParam['champs_mode'])->from(array('m' => 'mode_difficulte'));
if (isset($aParam['rech'])) {
$oQuery->where("m.nom like '%" . strtolower($aParam['rech']) . "%' ");
}
if (isset($aParam['limit'])) {
$oQuery->limit($aParam['limit']);
}
$oQuery->order(array('nom'));
//$this->debug($oQuery);
$aReturn = $this->fetchAllArray($oQuery);
return $aReturn;
}
/**
* @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 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;
}
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());
}
/**
* 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;
}
}
/**
* 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);
}
}
/**
* 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;
}
/**
* Retourne une liste de personnage avec les paramêtres passé.
* Les paramatres par defaut sont:
* $aParam = array(
* 'rech'=>'%',
* 'champs_personnage' => array(
* 'idPersonnage',
* 'nom',
* 'royaume',
* ),
* 'classe' => true,
* 'guilde' => true
* 'limit' => 20
* );
*
* @param type $aParam
* @return array
*/
function getAutoComplete($aParam)
{
if (!isset($aParam)) {
$aParam = array('rech' => '%', 'champs_personnage' => array('idPersonnage', 'nom', 'royaume'), 'classe' => true, 'guilde' => true, 'limit' => 20);
}
if (!isset($aParam['champs_personnage'])) {
$aParam['champs_personnage'] = array('idPersonnage', 'nom', 'royaume');
}
$sql = new \Zend\Db\Sql\Sql($this->getAdapter());
$oQuery = $sql->select();
$oQuery->columns($aParam['champs_personnage'])->from(array('p' => 'personnages'));
if (isset($aParam['classe']) && $aParam['classe']) {
$oQuery->join(array('c' => 'classes'), 'c.idClasses = p.idClasses', array('classe' => 'nom', 'c_classeId' => 'idClasses', 'couleur'), \Zend\Db\Sql\Select::JOIN_INNER);
}
// $query->join(array('r' => 'race'), 'r.idRace = p.idRace', array('race' => 'nom',
// 'r_raceId' => 'idRace'), \Zend\Db\Sql\Select::JOIN_INNER)
// ->join(array('f' => 'faction'), 'f.idFaction = p.idFaction', array('faction' => 'nom',
// 'r_factionId' => 'idFaction'), \Zend\Db\Sql\Select::JOIN_INNER)
if (isset($aParam['guilde']) && $aParam['guilde']) {
$oQuery->join(array('g' => 'guildes'), 'g.idGuildes = p.idGuildes', array('guilde' => 'nom', 'g_guildeId' => 'idGuildes'), \Zend\Db\Sql\Select::JOIN_LEFT);
}
if (isset($aParam['rech'])) {
$oQuery->where("p.nom like '%" . strtolower($aParam['rech']) . "%' ");
}
if (isset($aParam['limit'])) {
$oQuery->limit($aParam['limit']);
}
$oQuery->order(array('nom'));
//$this->debug($oQuery);
$aReturn = $this->fetchAllArray($oQuery);
return $aReturn;
}
/**
* Retourne les palliers renseigné pour le roster ayant l'identififant passé en paramètre.
* @param int $iIdRoster
* @return array
*/
public function getPallierStats($iIdRoster)
{
try {
$sql = new \Zend\Db\Sql\Sql($this->getAdapter());
$oQuery = $sql->select();
$oQuery->from(array('pa' => 'pallierAfficher'))->join(array('z' => 'zone'), 'z.idZone=pa.idZone', array('idZone', 'zone' => 'nom'), \Zend\Db\Sql\Select::JOIN_INNER)->join(array('m' => 'mode_difficulte'), 'm.idMode=pa.idModeDifficulte', array('idMode', 'mode' => 'nom'), \Zend\Db\Sql\Select::JOIN_INNER)->order('zone', 'idModeDifficulte')->where->equalTo('idRoster', $iIdRoster);
$aTabPallier = $this->fetchAllArray($oQuery);
return $aTabPallier;
} catch (\Exception $exc) {
throw new DatabaseException(10000, 4, $this->_getServiceLocator(), $iIdRoster, $exc);
}
}
/**
* Retourne le nombre total de loot du roster.(hors pallier)
* @param int $iIdRoster
*/
function getNbTotalLootRosterPallier($iIdRoster, $iSpe)
{
try {
$sql = new \Zend\Db\Sql\Sql($this->getAdapter());
$oQueryPallier = $sql->select();
$oQuery = $sql->select();
try {
$oQueryPallier->from(array('pa' => 'pallierAfficher'))->order('idModeDifficulte')->where->equalTo("idRoster", $iIdRoster);
$aPallierAfficheRoster = $this->fetchAllArray($oQueryPallier);
} catch (\Exception $exc) {
throw new DatabaseException(10000, 4, $this->_getServiceLocator(), $iIdRoster, $exc);
}
$oQuery->columns(array('totalItemPallier' => new Expression('COUNT(*)')))->from(array('ipr' => 'item_personnage_raid'))->join(array('r' => 'raids'), 'r.idRaid=ipr.idRaid', array(), \Zend\Db\Sql\Select::JOIN_INNER)->join(array('z' => 'zone'), 'z.idZone=r.idZoneTmp', array(), \Zend\Db\Sql\Select::JOIN_INNER)->join(array('m' => 'mode_difficulte'), 'm.idMode=r.idMode', array(), \Zend\Db\Sql\Select::JOIN_INNER)->join(array('ro' => 'roster'), 'ro.idRoster=r.idRosterTmp', array(), \Zend\Db\Sql\Select::JOIN_INNER)->join(array('i' => 'items'), 'ipr.idItem=i.idItem', array(), \Zend\Db\Sql\Select::JOIN_INNER)->join(array('b' => 'bosses'), 'ipr.idBosses=b.idBosses', array(), \Zend\Db\Sql\Select::JOIN_INNER)->join(array('p' => 'personnages'), 'p.idPersonnage=ipr.idPersonnage', array(), \Zend\Db\Sql\Select::JOIN_INNER);
foreach ($aPallierAfficheRoster as $pallierAffiche) {
$oQuery->where->NEST()->equalTo('r.idZoneTmp', $pallierAffiche['idZone'])->AND->equalTo('r.idMode', $pallierAffiche['idModeDifficulte'])->UNNEST()->OR;
}
$oQuery->group('ro.idRoster');
return $this->fetchAllArray($oQuery)[0]['totalItemPallier'];
} catch (\Exception $exc) {
throw new DatabaseException(4000, 4, $this->_getServiceLocator(), $iIdRoster, $exc);
}
}