protected function _init($itemClass, $keyField, $tableName = '')
{
parent::_init($itemClass, $keyField);
$this->_table = $tableName;
$this->_select = new Select();
$this->_select->from(array('main_table' => $tableName));
}
/**
* @param string $entityName
*/
public function __construct($entityName, Config $config, Select $select = null)
{
$this->entityName = $entityName;
$this->config = $config;
if ($select === null) {
$this->select = new Select();
$this->select->from($this->config->getTable($this->entityName));
} else {
$this->select = $select;
}
}
/**
* Méthode get : récupère un ou plusieurs pays
*
* @param string $code
* @return array
* @throws \Exception
*/
public function getPays($code = "", $returnObject = false)
{
$select = new Select();
$where = null;
$result = [];
try {
$select->from('pays');
if ($code) {
$where = $this->constructWhereFromCode($code);
$select->where($where);
}
$rowset = $this->tableGateway->selectWith($select);
if ($rowset->count() > 1) {
for ($i = 0; ($row = $rowset->current()) && $i < 30; $rowset->next(), $i++) {
$result[] = $row->toArray();
}
} else {
if ($rowset->count() > 0) {
if ($returnObject) {
$result[] = $rowset->current();
} else {
$result[] = $rowset->current()->toArray();
}
}
}
if (empty($result)) {
throw new \Exception("Nous n'avons pas trouve le pays possedant ce code, alpha2 ou alpha3 egal {$code}");
}
} catch (\Exception $e) {
$result['error'] = $e->getMessage();
}
return $result;
}
public function getBlogList()
{
$select = new Select();
$select->from($this->table);
$select->order('date DESC');
return new \Zend\Paginator\Paginator(new \Zend\Paginator\Adapter\DbSelect($select, $this->adapter, $this->resultSetPrototype));
}
/**
* Data provider for testGetSqlString
*
* @return array
*/
public function dataProvider()
{
$select0 = new Select();
$select0->from(array('x' => 'foo'));
$expectedSql0 = 'SELECT "x".* FROM "foo" "x"';
return array(array($select0, $expectedSql0));
}
public function fetchByIdCategoryWithLimit($idCategory, $limit)
{
$select = new Select();
$select->from('post')->where('id_category = ' . (int) $idCategory)->limit((int) $limit);
$resultSet = $this->tableGateway->select($select);
return $resultSet;
}
/**
* Get properties
*
* @param boolean $forceReload to initiliaze properties
*
* @return array
*/
public function getProperties($forceReload = false)
{
if ($this->getData('properties') == null or $forceReload) {
$select = new Select();
$select->from('tab')->columns(array())->join('property', 'tab.id = property.tab_id', '*', Select::JOIN_INNER);
if ($this->getDocumentId() !== null) {
$select->join('document', 'document.document_type_id = tab.document_type_id', array(), Select::JOIN_INNER);
$select->join('property_value', 'document.id = property_value.document_id AND property.id = property_value.property_id', array('value'), Select::JOIN_LEFT);
$select->where(array('document.id' => $this->getDocumentId()));
}
if ($this->getTabId() != null) {
$select->where(array('tab.id' => $this->getTabId()));
}
if ($this->getDocumentTypeId() != null) {
$select->where(array('tab.document_type_id' => $this->getDocumentTypeId()));
}
$select->order('property.sort_order ASC');
$rows = $this->fetchAll($select);
$properties = array();
foreach ($rows as $row) {
$propertyModel = Model::fromArray((array) $row);
if ($this->getDocumentId() !== null) {
$propertyModel->setDocumentId($this->getDocumentId());
}
$properties[] = $propertyModel;
}
$this->setData('properties', $properties);
}
return $this->getData('properties');
}
/**
*
* @param int $days_threshold days threshold
* @param int $ca_threshold turnover threshold
* @param int $limit
* @return type
*/
public function getCustomerGeo($days_threshold = 300, $ca_threshold = 1000, $min_accuracy = 6, $limit = 1000)
{
$akilia2db = $this->configuration['synchronizer']['db_akilia2'];
$select = new Select();
$bcg = new \Zend\Db\Sql\TableIdentifier('base_customer_geo', $akilia2db);
$bc = new \Zend\Db\Sql\TableIdentifier('base_customer', $akilia2db);
$bs = new \Zend\Db\Sql\TableIdentifier('base_state', $akilia2db);
$bco = new \Zend\Db\Sql\TableIdentifier('base_country', $akilia2db);
$so = new \Zend\Db\Sql\TableIdentifier('sal_order', $akilia2db);
$sol = new \Zend\Db\Sql\TableIdentifier('sal_order_line', $akilia2db);
$select->from(["bc" => $bc], [])->join(['bcg' => $bcg], "bc.id = bcg.customer_id", [], Select::JOIN_LEFT)->join(['bs' => $bs], "bs.id = bc.state_id", [], Select::JOIN_LEFT)->join(['bco' => $bco], "bco.id = bc.country_id", [], Select::JOIN_LEFT)->join(['so' => $so], "bc.id = so.customer_id", [], Select::JOIN_INNER)->join(['sol' => $sol], "so.id = sol.order_id", [], Select::JOIN_INNER)->where('bc.flag_archived <> 1');
$columns = ['customer_id' => new Expression('bc.id'), 'name' => new Expression('bc.name'), 'street' => new Expression('bc.street'), 'street_2' => new Expression('bc.street_2'), 'street_number' => new Expression('bc.street_number'), 'state_reference' => new Expression('bs.reference'), 'state_name' => new Expression('bs.name'), 'zipcode' => new Expression('bc.zipcode'), 'city' => new Expression('bc.city'), 'country' => new Expression('bco.name'), 'accuracy' => new Expression('bcg.accuracy'), 'latitude' => new Expression('bcg.latitude'), 'longitude' => new Expression('bcg.longitude')];
$select->columns(array_merge($columns, ['total_net' => new Expression('sum(sol.price_total_net)')]), true);
$select->group($columns);
$select->having("sum(sol.price_total_net) > {$ca_threshold}");
$select->where(function (Where $where) use($min_accuracy) {
//$where->greaterThan('so.date_order', '2012-12-31');
$where->notLike('bc.name', '%FINISHED%');
$where->nest->lessThan('accuracy', $min_accuracy)->or->isNull('accuracy')->unnest;
});
$select->where(new Expression("(TO_DAYS(NOW()) - TO_DAYS(so.date_order)) < {$days_threshold}"));
if ($limit > 0) {
$select->limit($limit);
}
$store = $this->getStore($select);
$data = $store->getData()->toArray();
return $data;
}
public function dataProvider()
{
$select0 = new Select();
$select0->from('foo')->columns(array('bar', 'baz'))->order('bar')->limit(5)->offset(10);
$expectedPrepareSql0 = 'SELECT [bar], [baz] FROM ( SELECT [foo].[bar] AS [bar], [foo].[baz] AS [baz], ROW_NUMBER() OVER (ORDER BY [bar] ASC) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ?+1 AND ?+?';
$expectedParams0 = array('offset' => 10, 'limit' => 5, 'offsetForSum' => 10);
$expectedSql0 = 'SELECT [bar], [baz] FROM ( SELECT [foo].[bar] AS [bar], [foo].[baz] AS [baz], ROW_NUMBER() OVER (ORDER BY [bar] ASC) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN 10+1 AND 5+10';
$expectedFormatParamCount0 = 3;
$select1 = new Select();
$select1->from('foo')->columns(array('bar', 'bam' => 'baz'))->limit(5)->offset(10);
$expectedPrepareSql1 = 'SELECT [bar], [bam] FROM ( SELECT [foo].[bar] AS [bar], [foo].[baz] AS [bam], ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ?+1 AND ?+?';
$expectedParams1 = array('offset' => 10, 'limit' => 5, 'offsetForSum' => 10);
$expectedSql1 = 'SELECT [bar], [bam] FROM ( SELECT [foo].[bar] AS [bar], [foo].[baz] AS [bam], ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN 10+1 AND 5+10';
$expectedFormatParamCount1 = 3;
$select2 = new Select();
$select2->from('foo')->order('bar')->limit(5)->offset(10);
$expectedPrepareSql2 = 'SELECT * FROM ( SELECT [foo].*, ROW_NUMBER() OVER (ORDER BY [bar] ASC) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ?+1 AND ?+?';
$expectedParams2 = array('offset' => 10, 'limit' => 5, 'offsetForSum' => 10);
$expectedSql2 = 'SELECT * FROM ( SELECT [foo].*, ROW_NUMBER() OVER (ORDER BY [bar] ASC) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN 10+1 AND 5+10';
$expectedFormatParamCount2 = 3;
$select3 = new Select();
$select3->from('foo');
$expectedPrepareSql3 = 'SELECT [foo].* FROM [foo]';
$expectedParams3 = array();
$expectedSql3 = 'SELECT [foo].* FROM [foo]';
$expectedFormatParamCount3 = 0;
return array(array($select0, $expectedPrepareSql0, $expectedParams0, $expectedSql0, $expectedFormatParamCount0), array($select1, $expectedPrepareSql1, $expectedParams1, $expectedSql1, $expectedFormatParamCount1), array($select2, $expectedPrepareSql2, $expectedParams2, $expectedSql2, $expectedFormatParamCount2), array($select3, $expectedPrepareSql3, $expectedParams3, $expectedSql3, $expectedFormatParamCount3));
}
public function getList($where = array(), $order = null, $offset = null, $limit = null)
{
if (empty($where['ProductFilterOption.productCategoryFilterOptionID'])) {
$select = new Select();
$select->from(array('b' => 'Product'));
$select->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName'));
$select->where($where);
$select->offset($offset);
$select->limit($limit);
$select->order($order);
} else {
$select = $this->getSelect();
$select->columns(array())->join(array('b' => 'Product'), 'ProductFilterOption.productID = b.productID')->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName'))->where($where)->offset($offset)->limit($limit)->group(array('ProductFilterOption.productID'))->having('count(ProductFilterOption.productID) > ' . (count($where['ProductFilterOption.productCategoryFilterOptionID']) - 1));
$select->order($order);
}
$paginator = $this->paginate($select);
$paginator->setCurrentPageNumber(ceil($offset / $limit) + 1);
//$paginator->setItemCountPerPage(1);
$products = $paginator->getCurrentItems()->getArrayCopy();
$pages = $paginator->getPages();
$productsCount = $paginator->getTotalItemCount();
foreach ($products as $k => $v) {
$products[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']);
}
return array('products' => $products, 'productsCount' => $productsCount, 'pages' => $pages);
}
/**
* {@inheritDoc}
*/
public function getRoles()
{
/* @var $tableGateway \Zend\Db\TableGateway\TableGateway */
$tableGateway = $this->serviceLocator->get('BjyAuthorize\\Service\\RoleDbTableGateway');
$sql = new Select();
$sql->from($this->tableName);
/* @var $roles Role[] */
$roles = array();
$indexedRows = array();
$rowset = $tableGateway->selectWith($sql);
// Pass 1: collect all rows and index them by PK
foreach ($rowset as $row) {
$indexedRows[$row[$this->identifierFieldName]] = $row;
}
// Pass 2: build a role for each indexed row
foreach ($indexedRows as $row) {
$parentRoleId = isset($row[$this->parentRoleFieldName]) ? $indexedRows[$row[$this->parentRoleFieldName]][$this->roleIdFieldName] : null;
$roleId = $row[$this->roleIdFieldName];
$roles[$roleId] = new Role($roleId, $parentRoleId);
}
// Pass 3: Re-inject parent objects to preserve hierarchy
foreach ($roles as $role) {
$parentRoleObj = $role->getParent();
if ($parentRoleObj && ($parentRoleId = $parentRoleObj->getRoleId())) {
$role->setParent($roles[$parentRoleId]);
}
}
return array_values($roles);
}
public function find($params)
{
$sql = new Sql($this->getAdapter());
$select = new Select();
$select->from($params['from']);
if (!empty($params['columns'])) {
$select->columns($params['columns']);
}
foreach ($params['where'] as $where) {
$select->where($where);
}
foreach ($params['joins'] as $join) {
if (empty($join['columns'])) {
$join['columns'] = Select::SQL_STAR;
}
if (empty($join['type'])) {
$join['type'] = Select::JOIN_INNER;
}
$select->join($join['name'], $join['on'], $join['columns'], $join['type']);
}
$query = $sql->getSqlStringForSqlObject($select);
$results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE);
$data = $results->toArray();
if (empty($data)) {
return false;
} else {
if (count($data) == 1) {
return $data[0];
} else {
return $data;
}
}
}
public function fetchAll($paginated = false, $sort = 'name', $order = 'ASC')
{
switch ($sort) {
case "id":
$sort = "C_Id";
break;
case "name":
$sort = "C_Name";
break;
case "lastinvoice":
$sort = "C_LastInvoice";
break;
default:
$sort = "C_Name";
}
$select = new Select();
$select->from($this->table);
$subquery = "(SELECT I_BaseDate FROM Invoice WHERE Invoice.I_ClientId = Client.C_Id ORDER BY I_Id DESC LIMIT 1)";
$select->columns(array('C_Id' => 'C_Id', 'C_Name' => 'C_Name', 'C_PaymentTerms' => 'C_PaymentTerms', 'C_Reference' => 'C_Reference', 'C_CRN' => 'C_CRN', 'C_Class' => 'C_Class', 'C_Updated' => 'C_Updated', 'C_LastInvoice' => new Expression($subquery)))->order($sort . ' ' . $order);
if ($paginated) {
return new Paginator(new DbSelect($select, $this->adapter, $this->resultSetPrototype));
}
$resultSet = $this->select($select);
return $resultSet;
}
public function getBuilder()
{
if (empty($this->parsed['SELECT'])) {
throw new \InvalidArgumentException('Queries other than SELECT are not supported yet');
}
$sql = new Select();
$sql->columns($this->getBaseExprs($this->parsed['SELECT']));
foreach ($this->parsed['FROM'] as $table) {
if (!$table['ref_type']) {
$sql->from(array($table['alias']['name'] => $table['table']));
continue;
}
if ('JOIN' == $table['join_type']) {
$table['join_type'] = 'INNER';
}
$sql->join(array($table['alias']['name'] => $table['table']), join(' ', $this->getBaseExprs($table['ref_clause'])), array(), $table['join_type']);
}
if (isset($this->parsed['GROUP'])) {
$sql->group($this->getBaseExprs($this->parsed['GROUP']));
}
if (isset($this->parsed['WHERE'])) {
$sql->where(join(' ', $this->getBaseExprs($this->parsed['WHERE'])));
}
unset($this->parsed['WHERE'], $this->parsed['GROUP'], $this->parsed['FROM'], $this->parsed['WHERE']);
var_dump($this->parsed);
#die('Stopped at ' . __FILE__ . ' on line ' . __LINE__);
return $sql;
}
public function buscarUm($id)
{
$id = (int) $id;
$select = new Select();
$select->from('clientes')->columns(['id', 'nome', 'email', 'criado', 'modificado'])->where(['id' => $id]);
$dados = $this->selectWith($select);
return $dados->current();
}
public function findByContactId($id)
{
$sql = new Select();
$sql->from(array('cc' => 'contact_companies'))->join(array('c' => 'contact_company'), 'c.company_id = cc.company_id');
$where = new Where();
$where->equalTo('cc.contact_id', $id);
return $this->select($sql->where($where));
}
public function fetchMoi($id)
{
$select = new Select();
$select->from(array('m' => 'message'))->join(array('l' => 'livre_d_or'), 'm.id_livre = l.id_livre')->join(array('u' => 'utilisateur'), 'm.id_utilisateur = u.id_utilisateur', array('posteur' => 'login_utilisateur'));
$select->where('m.id_livre = "' . $id . '"');
$resultSet = $this->tableGateway->selectWith($select);
return $resultSet;
}
public function fetchDrafts()
{
$select = new Select();
$select->from($this->table);
$select->columns(array('I_Id' => 'I_Id', 'I_Number' => 'I_Number', 'I_BaseDate' => 'I_BaseDate', 'I_Vat' => 'I_Vat', 'I_DueDays' => 'I_DueDays', 'I_SentDate' => 'I_SentDate', 'I_PayedDate' => 'I_PayedDate', 'I_ClientId' => 'I_ClientId', 'I_ClientReference' => 'I_ClientReference', 'I_OwnReference' => 'I_OwnReference', 'I_AddressId' => 'I_AddressId', 'I_Text' => 'I_Text', 'I_Updated' => 'I_Updated'))->where(array(new \Zend\Db\Sql\Predicate\IsNull('I_Number')))->join('Client', 'Invoice.I_ClientId = Client.C_Id');
$resultSet = $this->selectWith($select);
return $resultSet;
}
public function commandeEnCours($commande_id)
{
$id = (int) $commande_id;
$select = new Select();
$select->from('commandeproduit')->where(array('COMMANDE_ID' => $id, 'COMMANDEPRODUIT_ACTIF' => 1));
$resultSet = $this->tableGateway->selectwith($select);
return $resultSet;
}
public function getAllTva()
{
$select = new Select();
$select->from('tva')->order('TVA_NOM')->where('TVA_ACTIF = 1');
$resultSet = $this->tableGateway->selectwith($select);
$resultSet->buffer();
return $resultSet;
}
public function getNewsList()
{
$select = new Select();
$select->columns(['content' => 'en', 'title' => 'en_title', 'url' => 'en_title', 'date']);
$select->from($this->table);
$select->order('date DESC');
return new \Zend\Paginator\Paginator(new \Zend\Paginator\Adapter\DbSelect($select, $this->adapter, $this->resultSetPrototype));
}
public function getAllMenu()
{
$select = new Select();
$select->from('menu')->order('MENU_NOM')->where('MENU_ACTIF = 1');
$resultSet = $this->tableGateway->selectWith($select);
$resultSet->buffer();
return $resultSet;
}
public function findByIdAndUser($addressId, $userId)
{
$where = new Where();
$where->equalTo('ua.user_id', $userId)->equalTo('ua.address_id', $addressId);
$sql = new Select();
$sql->from(array('ua' => 'user_addresses'))->join(array('a' => 'address'), 'ua.address_id = a.address_id')->where($where);
return $this->select($sql)->current();
}
public function getAllParametre()
{
$select = new Select();
$select->from('PARAMETRE')->order('PARAMETRE_NOM');
$resultSet = $this->tableGateway->selectwith($select);
$resultSet->buffer();
return $resultSet;
}
public function findByContactId($id)
{
$sql = new Select();
$sql->from('contact_phone');
$where = new Where();
$where->equalTo('contact_id', $id);
return $this->select($sql->where($where));
}
public function findByContactId($id)
{
$sql = new Select();
$sql->from(array('ca' => 'contact_addresses'))->join(array('a' => 'address'), 'a.address_id = ca.address_id');
$where = new Where();
$where->equalTo('ca.contact_id', $id);
return $this->select($sql->where($where));
}
public function buscarUm($id)
{
$id = (int) $id;
$select = new Select();
$select->from('contas')->columns(array('id', 'fk_tipo', 'fk_cliente', 'nome', 'saldo' => new Expression("FORMAT(saldo, 2, 'de_DE')"), 'criado', 'modificado'))->where(array('id' => $id));
$dados = $this->selectWith($select);
return $dados->current();
}
public function fetchAll()
{
$select = new Select();
$select->from(array('u' => 'users'))->join(array('a' => 'album'), 'a.id_user = u.id');
$select->where('u.user_name = "' . $this->identity . '"');
$resultSet = $this->tableGateway->selectWith($select);
return $resultSet;
}
public function findByItemId($itemId)
{
$select = new Select();
$select->from('cart_item_index');
$where = new Where();
$where->equalTo('item_id', $itemId);
$resultSet = $this->selectMany($select->where($where));
return $resultSet;
}
public function getAllTypeproduit()
{
$select = new Select();
// $where = new Where();
$select->from('TYPEPRODUIT')->join(array('t' => 'TVA'), 'TYPEPRODUIT.TVA_ID = t.TVA_ID')->columns(array('TVA_NOM'))->where(array('TYPEPRODUIT.TVA_ID' => 'TVA.TVA_NOM'));
$resultSet = $this->tableGateway->selectwith($select);
$resultSet->buffer();
return $resultSet;
}
