public function fetchFeed($params = null)
{
$sql = new Sql($this->adapter);
$select = $sql->select()->from($this->table);
$params['orderColumn'] = 'id';
$params['orderDirection'] = 'DESC';
$tableSchemaArray = TableSchema::getSchemaArray($this->table);
$hasActiveColumn = $this->schemaHasActiveColumn($tableSchemaArray);
$params = $this->applyDefaultEntriesSelectParams($params);
$columns = ['id', 'identifier', 'action', 'table_name', 'row_id', 'user', 'datetime', 'type', 'data'];
$select->columns($columns);
// ->order('id DESC');
$select->where->nest->isNull('parent_id')->OR->equalTo('type', 'FILES')->unnest;
$select = $this->applyParamsToTableEntriesSelect($params, $select, $tableSchemaArray, $hasActiveColumn);
//die($this->dumpSql($select));
$rowset = $this->selectWith($select);
$rowset = $rowset->toArray();
$rowset = $this->convertDates($rowset, $tableSchemaArray);
// @TODO: Returns date in ISO 8601 Ex: 2016-06-06T17:18:20Z
// see: https://en.wikipedia.org/wiki/ISO_8601
// foreach ($rowset as &$row) {
// $row['datetime'] .= ' UTC';
// }
$countTotalWhere = new Where();
$countTotalWhere->isNull('parent_id')->OR->equalTo('type', 'FILES');
$activityTotal = $this->countTotal($countTotalWhere);
return ['total' => $activityTotal, 'rows' => $rowset];
}
public function fetchFeed($params = null)
{
$sql = new Sql($this->adapter);
$select = $sql->select()->from($this->table);
$params['orderColumn'] = 'id';
$params['orderDirection'] = 'DESC';
$tableSchemaArray = TableSchema::getSchemaArray($this->table);
$hasActiveColumn = $this->schemaHasActiveColumn($tableSchemaArray);
$params = $this->applyDefaultEntriesSelectParams($params);
$columns = array('id', 'identifier', 'action', 'table_name', 'row_id', 'user', 'datetime', 'type', 'data');
$select->columns($columns);
// ->order('id DESC');
$select->where->nest->isNull('parent_id')->OR->equalTo('type', 'FILES')->unnest;
$select = $this->applyParamsToTableEntriesSelect($params, $select, $tableSchemaArray, $hasActiveColumn);
//die($this->dumpSql($select));
$rowset = $this->selectWith($select);
$rowset = $rowset->toArray();
foreach ($rowset as &$row) {
$row['datetime'] .= ' UTC';
}
$countTotalWhere = new Where();
$countTotalWhere->isNull('parent_id')->OR->equalTo('type', 'FILES');
$activityTotal = $this->countTotal($countTotalWhere);
return array('total' => $activityTotal, 'rows' => $rowset);
}
/**
* @param null $parentId
* @param array $roles
* @return array
*/
public function getMenuList($parentId = null, array $roles)
{
$results = $this->select(function (Select $select) use($parentId, $roles) {
try {
$where = new Where();
if (empty($parentId)) {
$where->isNull('parentId');
} else {
$where->equalTo('parentId', $parentId);
}
if (!empty($roles)) {
$where->in('mp.roleId', $roles);
} else {
$where->expression(' 1 = ?', 0);
}
$select->join(array('mp' => 'tbl_menu_permission'), 'tbl_menu.menuId = mp.menuId', array('menuId'), Select::JOIN_INNER)->where($where)->quantifier(Select::QUANTIFIER_DISTINCT)->order(array('priority ASC'));
} catch (\Exception $ex) {
throw $ex;
}
});
$menus = array();
foreach ($results as $menu) {
$nav = array();
$nav['id'] = $menu->getMenuId();
$pages = $this->getMenuList($menu->getMenuId(), $roles);
$caret = '';
if (!empty($pages)) {
$nav['pages'] = $pages;
}
$icon = '';
if ($menu->getIcon()) {
$icon = '<span class="' . $menu->getIcon() . '"></span> ';
}
$nav['title'] = $menu->getDescription();
$nav['label'] = $icon . htmlspecialchars($menu->getTitle()) . $caret;
$nav['order'] = $menu->getPriority();
$nav['rel'] = array('divider' => $menu->getHasDivider());
if ($menu->getUrlType() == 'R') {
$nav['route'] = $menu->getUrls();
} else {
$nav['uri'] = $menu->getUrls();
}
$menus[] = $nav;
}
return $menus;
}
public function getListForSearch($iDisplayStart, $iDisplayLength, $filterParams, $sSearch, $sortCol = 0, $sortDir = 'DESC')
{
$where = new Where();
if ($filterParams["category"] > 0) {
$where->equalTo($this->getTable() . '.category_id', $filterParams["category"]);
}
if (FALSE !== strpos($filterParams["location"], '_')) {
$locationsArray = explode('_', $filterParams["location"]);
$locationId = $locationsArray[1];
$where->equalTo($this->getTable() . '.location_entity_id', $locationId);
}
switch ((int) $filterParams["runningOut"]) {
case AssetService::RUNNING_OUT_YES:
$where->expression($this->getTable() . '.quantity <= thresholds.threshold', []);
break;
case AssetService::RUNNING_OUT_NO:
$where->expression($this->getTable() . '.quantity > thresholds.threshold', []);
break;
case AssetService::RUNNING_OUT_NOT_SET:
$where->isNull(' thresholds.threshold');
break;
}
if (strlen($sSearch)) {
$where->nest->like('storages.name', "%" . $sSearch . "%")->or->like('categories.name', "%" . $sSearch . "%")->unnest;
}
$sortColumns = ['category_name', 'location_name', 'quantity', 'running_out', 'threshold'];
$result = $this->fetchAll(function (Select $select) use($filterParams, $sortColumns, $iDisplayStart, $iDisplayLength, $where, $sSearch, $sortCol, $sortDir) {
$select->join(['categories' => DbTables::TBL_ASSET_CATEGORIES], $this->getTable() . '.category_id = categories.id', ['category_name' => 'name'], Select::JOIN_INNER)->join(['storages' => DbTables::TBL_WM_STORAGE], $this->getTable() . '.location_entity_id = storages.id', ['location_name' => 'name'], Select::JOIN_INNER)->join(['thresholds' => DbTables::TBL_WM_THRESHOLD], $this->getTable() . '.category_id = thresholds.asset_category_id AND ' . $this->getTable() . '.location_entity_id = thresholds.storage_id', ['threshold' => 'threshold'], Select::JOIN_LEFT);
if ($where !== null) {
$select->where($where);
}
$select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
if ($iDisplayLength !== null && $iDisplayStart !== null) {
$select->limit((int) $iDisplayLength);
$select->offset((int) $iDisplayStart);
}
$select->order($sortColumns[$sortCol] . ' ' . $sortDir);
});
$return['result'] = $result;
$statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
$result2 = $statement->execute();
$row = $result2->current();
$return['count'] = $row['total'];
return $return;
}
public function detailAction()
{
$storeID = $this->queryData['storeID'];
$auctionStatus = $this->queryData['auctionStatus'];
$order = $this->queryData['order'];
$sort = $this->queryData['sort'];
$storeCategoryID = $this->queryData['storeCategoryID'];
$where = array('storeID' => $storeID);
$storeInfo = $this->storeModel->fetch($where);
$storeCategories = $this->storeCategoryModel->select(array('storeID' => $storeID))->toArray();
/*$storeRecommendProducts = $this->productModel->getProducts(array('Product.isStoreRecommend' => 1, 'Product.storeID' => $storeID, 'Product.auctionStatus' => array(1, 2)));
$storeRecommendProductsData = $storeRecommendProducts['data'];
foreach($storeRecommendProductsData as $k => $v){
$storeRecommendProductsData[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']);
}*/
if (!empty($order) && !empty($sort)) {
$order = 'Product.' . $order . ' ' . $sort;
}
$where = new Where();
$where->equalTo('Product.storeID', $storeID);
if (!empty($auctionStatus)) {
$where->equalTo('Product.auctionStatus', $auctionStatus);
} else {
$where->in('Product.auctionStatus', array(1, 2));
}
$where->isNull('Product.specialID');
if (!empty($storeCategoryID)) {
$where->and->nest()->or->equalTo('Product.firstStoreCategoryID', $storeCategoryID)->or->equalTo('Product.secondStoreCategoryID', $storeCategoryID)->or->equalTo('Product.thirdStoreCategoryID', $storeCategoryID);
}
$storeProducts = $this->productModel->getProducts($where, $this->pageNum, $this->limit, $order);
$storeProductsData = $storeProducts['data'];
foreach ($storeProductsData as $k => $v) {
$storeProductsData[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']);
}
$this->view->setVariables(array('storeInfo' => $storeInfo, 'storeProducts' => $storeProductsData, 'storeCategories' => $storeCategories, 'pages' => $storeProducts['pages'], 'auctionStatus' => $auctionStatus, 'order' => $this->queryData['order'], 'sort' => $sort, 'storeCategoryID' => $storeCategoryID));
return $this->view;
}
/**
* Permet d'afficher seulement les informations présentes sur l'écran de recherche Client (qui sont également les critères)
* @author Ophélie
* @param ServiceLocator $sm
* @param array $critere
* @since 1.0
* @return array
*/
public function getClientsFromForms($sm, $codeClient = '', $raisonSociale = null, $limit = 100)
{
$sql = new Sql($sm->get('Zend\\Db\\Adapter\\Adapter'));
$select = $sql->select();
$select->columns(array('id', 'societe' => new Expression("CONCAT(c.raison_sociale,' (',a.code_postal,' ',a.ville,', ',a.pays,')')")))->from(array('c' => 'client'))->join(array('a' => 'adresse'), 'a.ref_client = c.id', array(), $select::JOIN_LEFT)->order('raison_sociale ASC');
$where = new Where();
if ($codeClient !== '') {
if ($codeClient === null) {
//var_dump($codeClient);die();
$where->isNull('c.code_client')->and;
} else {
$where->equalTo('c.code_client', $codeClient)->and;
}
}
if ($raisonSociale !== null) {
$where->equalTo('c.raison_sociale', $raisonSociale)->and;
}
$where->and->nest()->equalTo('a.adresse_principale', 1)->or->isNull('a.adresse_principale')->unnest();
$select->where($where);
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();
if ($results->isQueryResult()) {
$resultSet = new ResultSet();
$resultSet->initialize($results);
return $resultSet->toArray();
}
return array();
}
/**
* @return int
*
* @author Tigran Petrosyan
*/
public function getNotChargedApartelReservationsCount()
{
$this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
$result = $this->fetchOne(function (Select $select) {
$select->join(['apartment_groups' => DbTables::TBL_APARTMENT_GROUPS], $this->getTable() . '.apartel_id = apartment_groups.id', []);
$select->join(['charges' => DbTables::TBL_CHARGE], new Expression($this->getTable() . '.id = charges.reservation_id AND charges.status=0'), [], Select::JOIN_LEFT);
$where = new Where();
$where->greaterThan($this->getTable() . '.apartel_id', 0);
$where->equalTo($this->getTable() . '.status', \DDD\Service\Booking::BOOKING_STATUS_BOOKED);
$where->isNull('charges.id');
$where->notEqualTo($this->getTable() . '.check_charged', 1);
$where->expression($this->getTable() . '.apartment_id_assigned NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
$where->greaterThanOrEqualTo($this->getTable() . '.date_from', date('Y-m-d'));
$where->expression('(' . $this->getTable() . '.is_refundable = 2 or ' . $this->getTable() . '.refundable_before_hours >= TIMESTAMPDIFF(HOUR,NOW(),date_from))', []);
$select->columns(['count' => new Expression('COUNT(*)')]);
$select->where($where);
});
return $result['count'];
}
/**
* @param null $parentId
* @param string $parentName
* @param string $type
* @return array
*/
public function getChildren($type = '', $parentId = null, $parentName = "")
{
$results = $this->select(function (Select $select) use($parentId, $type) {
if (empty($type)) {
$select->where(array('parentTypeId' => $parentId));
} else {
$where = new Where();
if (empty($parentId)) {
$where->isNull('parentTypeId');
} else {
$where->equalTo('parentTypeId', $parentId);
}
$where->in('baseType', array('B', $type));
$select->where($where);
}
});
$resultList = array();
foreach ($results as $accountType) {
$children = $this->getChildren($type, $accountType->getAccountTypeId(), $parentName);
if (!empty($children)) {
$accountType->setChildren($children);
}
$resultList[] = $accountType;
}
return $resultList;
}
public function getTaskListForSearch($authID, $iDisplayStart = null, $iDisplayLength = null, $filterParams = array(), $sortCol = 0, $sortDir = 'DESC', $taskManger)
{
$where = new Where();
foreach ($filterParams as $key => $row) {
if (!is_array($row)) {
$filterParams[$key] = trim($row);
}
}
if ($filterParams["title"] != '') {
$where->like($this->getTable() . '.title', '%' . $filterParams["title"] . '%');
}
if ($filterParams["status"] > 0) {
$statusArray = [$filterParams["status"]];
if ($filterParams["status"] == TaskService::STATUS_ALL_OPEN) {
$statusArray = [TaskService::STATUS_NEW, TaskService::STATUS_VIEWED, TaskService::STATUS_BLOCKED, TaskService::STATUS_STARTED];
}
$where->in($this->getTable() . '.task_status', $statusArray);
}
if ($filterParams["priority"] > 0) {
$where->equalTo($this->getTable() . '.priority', $filterParams["priority"]);
}
if ($filterParams["type"] > 0) {
$where->equalTo($this->getTable() . '.task_type', $filterParams["type"]);
}
if ($filterParams["creator_id"] > 0) {
$where->equalTo('task_creators.user_id', (int) $filterParams["creator_id"]);
}
if ($filterParams["responsible_id"] > 0) {
$where->equalTo('task_responsibles.user_id', (int) $filterParams["responsible_id"]);
}
if ($filterParams["responsible_id"] < 0) {
$where->isNull('task_responsibles.user_id');
}
if ($filterParams["verifier_id"] > 0) {
$where->equalTo('task_verifiers.user_id', (int) $filterParams["verifier_id"]);
}
if ($filterParams["helper_id"] > 0) {
$where->equalTo('task_helpers.user_id', (int) $filterParams["helper_id"]);
}
if ($filterParams["follower_id"] > 0) {
$where->equalTo('task_followers.user_id', (int) $filterParams["follower_id"]);
}
if ($filterParams["property_id"] > 0 && $filterParams['property']) {
$where->equalTo($this->getTable() . '.property_id', $filterParams["property_id"]);
}
if ($filterParams["team_id"]) {
$where->equalTo($this->getTable() . '.team_id', $filterParams["team_id"]);
}
if (isset($filterParams['tags']) && !empty($filterParams['tags'])) {
$where->in('task_tag.tag_id', explode(',', $filterParams['tags']));
}
if ($filterParams["end_date"] != '') {
$dates = explode(' - ', $filterParams["end_date"]);
$rangeStart = $dates[0];
$rangeEnd = $dates[1];
$where->lessThanOrEqualTo($this->getTable() . ".end_date", $rangeEnd);
$where->greaterThanOrEqualTo($this->getTable() . ".end_date", $rangeStart);
}
if ($filterParams["creation_date"] != '') {
$dates = explode(' - ', $filterParams["creation_date"]);
$rangeStart = $dates[0] . ' 00:00';
$rangeEnd = $dates[1] . ' 23:59';
$where->lessThanOrEqualTo($this->getTable() . ".creation_date", $rangeEnd);
$where->greaterThanOrEqualTo($this->getTable() . ".creation_date", $rangeStart);
}
if ($filterParams["done_date"] != '') {
$dates = explode(' - ', $filterParams["done_date"]);
$rangeStart = $dates[0];
$rangeEnd = $dates[1];
$where->lessThanOrEqualTo($this->getTable() . ".done_date", $rangeEnd);
$where->greaterThanOrEqualTo($this->getTable() . ".done_date", $rangeStart);
}
$buildingId = false;
if ($filterParams["building_id"] > 0 && $filterParams['building']) {
$buildingId = $filterParams["building_id"];
}
$sortColumns = array('priority', 'task_status', 'start_date', 'end_date', 'title', 'apartment_name', 'responsible_name', 'verifier_name', 'task_type');
$result = $this->fetchAll(function (Select $select) use($sortColumns, $iDisplayStart, $iDisplayLength, $where, $sortCol, $sortDir, $authID, $taskManger, $buildingId) {
$select->join(['task_types' => DbTables::TBL_TASK_TYPE], $this->getTable() . '.task_type = task_types.id', ['task_type_name' => 'name'], Select::JOIN_INNER)->join(['task_creators' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_creators.task_id and task_creators.type=' . TaskService::STAFF_CREATOR), [], Select::JOIN_LEFT)->join(['users_creators' => DbTables::TBL_BACKOFFICE_USERS], 'task_creators.user_id = users_creators.id', ['creator_id' => 'id', 'creator_name' => new Expression("CONCAT(users_creators.firstname, ' ', users_creators.lastname)")], Select::JOIN_LEFT)->join(['task_responsibles' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_responsibles.task_id and task_responsibles.type=' . TaskService::STAFF_RESPONSIBLE), [], Select::JOIN_LEFT)->join(['users_responsibles' => DbTables::TBL_BACKOFFICE_USERS], 'task_responsibles.user_id = users_responsibles.id', ['responsible_id' => 'id', 'responsible_name' => new Expression("CONCAT(users_responsibles.firstname, ' ', users_responsibles.lastname)")], Select::JOIN_LEFT)->join(['task_verifiers' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_verifiers.task_id and task_verifiers.type=' . TaskService::STAFF_VERIFIER), [], Select::JOIN_LEFT)->join(['users_verifiers' => DbTables::TBL_BACKOFFICE_USERS], 'task_verifiers.user_id = users_verifiers.id', ['verifier_id' => 'id', 'verifier_name' => new Expression("CONCAT(users_verifiers.firstname, ' ', users_verifiers.lastname)")], Select::JOIN_LEFT)->join(['task_helpers' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_helpers.task_id and task_helpers.type=' . TaskService::STAFF_HELPER), [], Select::JOIN_LEFT)->join(['users_helpers' => DbTables::TBL_BACKOFFICE_USERS], 'task_helpers.user_id = users_helpers.id', ['helper_id' => 'id', 'helper_name' => new Expression("CONCAT(users_helpers.firstname, ' ', users_helpers.lastname)")], Select::JOIN_LEFT)->join(['task_followers' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_followers.task_id and task_followers.type=' . TaskService::STAFF_FOLLOWER), [], Select::JOIN_LEFT)->join(['task_team_staff' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.team_id = task_team_staff.team_id and task_team_staff.type NOT IN (' . TeamService::STAFF_CREATOR . ', ' . TeamService::STAFF_DIRECTOR . ')'), [], Select::JOIN_LEFT)->join(['task_following_team_staff' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.following_team_id = task_following_team_staff.team_id and task_following_team_staff.type NOT IN (' . TeamService::STAFF_CREATOR . ', ' . TeamService::STAFF_DIRECTOR . ')'), [], Select::JOIN_LEFT)->join(['users_followers' => DbTables::TBL_BACKOFFICE_USERS], 'task_followers.user_id = users_followers.id', ['follower_id' => 'id', 'follower_name' => new Expression("CONCAT(users_followers.firstname, ' ', users_followers.lastname)")], Select::JOIN_LEFT)->join(['apartment1' => DbTables::TBL_APARTMENTS], $this->getTable() . '.property_id = apartment1.id', ['apartment_name' => 'name', 'apartment_unit_number' => 'unit_number'], Select::JOIN_LEFT)->join(['subtask' => DbTables::TBL_TASK_SUBTASK], 'subtask.task_id = ' . $this->getTable() . '.id', ['subtask_description' => new Expression('GROUP_CONCAT(subtask.description)')], Select::JOIN_LEFT)->join(['task_tag' => DbTables::TBL_TASK_TAG], new Expression($this->getTable() . '.id = task_tag.task_id'), [], Select::JOIN_LEFT);
if ($buildingId) {
$select->join(['apartment' => DbTables::TBL_APARTMENTS], new Expression($this->getTable() . '.property_id = apartment.id AND apartment.building_id = ' . $buildingId), []);
}
if (!$taskManger) {
$where->expression('(users_creators.id = ' . $authID . ' OR users_responsibles.id = ' . $authID . ' OR task_verifiers.user_id = ' . $authID . ' OR users_helpers.id = ' . $authID . ' OR users_followers.id = ' . $authID . ' OR task_team_staff.user_id = ' . $authID . ' OR task_following_team_staff.user_id = ' . $authID . ')', []);
}
if ($where !== null) {
$select->where($where);
}
$select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
if ($iDisplayLength !== null && $iDisplayStart !== null) {
$select->limit((int) $iDisplayLength);
$select->offset((int) $iDisplayStart);
}
$select->group($this->getTable() . '.id');
$select->order($sortColumns[$sortCol] . ' ' . $sortDir);
});
$statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
$result2 = $statement->execute();
$row = $result2->current();
$total = $row['total'];
return ['result' => $result, 'count' => $total];
}
