public function findCourses($free, $disability, $child_care, $lat, $lng) { $sql = new Sql($this->tableGateway->getAdapter()); $select = $sql->select(); $select->from(array("co" => "course"))->join(array("cc" => "course_centre"), "co.id = cc.course_id")->join(array("ce" => "centre"), "ce.id = cc.centre_id", array("centre_id" => "id", "centreName" => "name", "location" => new Expression("AsWKT(location)"), "post_code", "address", "buses", "tube", "accebility", "accebility_condition", "other_information")); //especificar las columnas que queremos en el resultados de las consultas. $select->columns(array("id", "courseName" => "name", "class_type", "levels", "who_join", "how_join", "when_join", "how_long", "cost_free", "cost_condition", "times", "documentation_required", "contact_phone", "contact_email", "contact_person", "child_care", "child_condition", "organization_id", "other_information")); $where = new Where(); //TODO terminar de poner prefijos a las tablas if (isset($free)) { if ($free == 'Yes') { $where->in("co.cost_free", array("y", "c")); } else { if ($free == 'No') { $where->equalTo("co.cost_free", "n"); } } } if (isset($disability)) { if ($disability == 'Yes') { $where->in("ce.accebility", array("y", "c")); } else { if ($disability == 'No') { $where->equalTo("ce.accebility", "n"); } } } if (isset($child_care)) { if ($child_care == 'Yes') { $where->in("co.child_care", array("y", "c")); } else { if ($child_care == 'No') { $where->equalTo("co.child_care", "n"); } } } //hacemos busquedas en 3km a la redonda, se puede convertir en un parametro $R = 6371; //radio de la tierra en km if (isset($lat) && isset($lng)) { $maxLat = $lat + rad2deg(1.5 / $R); $minLat = $lat - rad2deg(1.5 / $R); $maxLng = $lng + rad2deg(1.5 / $R / cos(deg2rad($lat))); $minLng = $lng - rad2deg(1.5 / $R / cos(deg2rad($lat))); $where->between(new Expression("X(ce.location)"), $minLat, $maxLat); $where->between(new Expression("y(ce.location)"), $minLng, $maxLng); } //TODO terminar con el resto de los filtros de busqueda $select->where($where); $statement = $sql->prepareStatementForSqlObject($select); $rowset = $statement->execute(); return $rowset; }
/** * Creates a new square manager object. * * Preloads all available squares from the database. * * @param SquareTable $squareTable * @param SquareMetaTable $squareMetaTable * @param string $locale */ public function __construct(SquareTable $squareTable, SquareMetaTable $squareMetaTable, $locale) { $this->squareTable = $squareTable; $this->squareMetaTable = $squareMetaTable; $select = $squareTable->getSql()->select(); $select->order('priority ASC'); $resultSet = $squareTable->selectWith($select); $this->squares = SquareFactory::fromResultSet($resultSet); /* Load square meta data */ if ($this->squares) { $sids = array(); foreach ($this->squares as $square) { $sids[] = $square->need('sid'); } reset($this->squares); $metaWhere = new Where(); $metaWhere->in('sid', $sids); $metaWhere->and; $metaWhereNested = $metaWhere->nest(); $metaWhereNested->isNull('locale'); $metaWhereNested->or; $metaWhereNested->equalTo('locale', $locale); $metaWhereNested->unnest(); $metaSelect = $this->squareMetaTable->getSql()->select(); $metaSelect->where($metaWhere); $metaSelect->order('locale ASC'); $metaResultSet = $this->squareMetaTable->selectWith($metaSelect); SquareFactory::fromMetaResultSet($this->squares, $metaResultSet); /* Prepare active squares */ $this->activeSquares = $this->getAllVisible(); } }
public function getTranslationListForSearch($filterParams = []) { $where = new Where(); $hasPageQuery = false; if ((int) $filterParams["id_translation"] > 0) { $where->equalTo($this->getTable() . '.id', $filterParams["id_translation"]); } else { if ($filterParams["srch_txt"] != '') { $where->like($this->getTable() . '.en_html_clean', '%' . strip_tags(trim($filterParams["srch_txt"])) . '%'); } if ($filterParams['category'] == 1 && isset($filterParams['un_type'][0])) { $pages = explode(',', $filterParams['un_type'][0]); $where->in('pr.page_id', $pages); } if ($filterParams["description"] != '') { $where->like($this->getTable() . '.description', '%' . strip_tags(trim($filterParams["description"])) . '%'); } } $columns = array('id' => 'id', 'content' => 'en', 'description' => 'description', 'page_name' => new Expression("GROUP_CONCAT(p.name SEPARATOR ', ')")); $sortColumns = ['id', 'en']; $result = $this->fetchAll(function (Select $select) use($columns, $sortColumns, $where) { $select->columns($columns); $select->join(['pr' => DbTables::TBL_UN_TEXTLINE_PAGE_REL], $this->getTable() . '.id = pr.textline_id', []); $select->join(['p' => DbTables::TBL_PAGES], 'p.id = pr.page_id', []); if ($where !== null) { $select->where($where); } $select->group($this->getTable() . '.id'); $select->quantifier(new Expression('SQL_CALC_FOUND_ROWS')); }); $statement = $this->adapter->query('SELECT FOUND_ROWS() as count'); $result2 = $statement->execute(); $row = $result2->current(); return ['result' => $result, 'count' => $row['count']]; }
/** * @param array $typeList * @param bool|true $onlyActive * @return \DDD\Domain\Warehouse\Category\Category[] */ public function getCategoriesByTypeList($typeList = [], $onlyActive = true, $selectedId = 0, $returnArray = false) { $entity = $this->getEntity(); $this->setEntity(new \DDD\Domain\Warehouse\Category\Category()); if ($returnArray) { $this->setEntity(new \ArrayObject()); } $result = $this->fetchAll(function (Select $select) use($typeList, $onlyActive, $selectedId) { $select->columns(['id', 'name', 'type' => 'type_id', 'inactive']); $nestedWhere = new Where(); $where = new Where(); if (!empty($typeList)) { $nestedWhere->in('type_id', $typeList); } if ($onlyActive) { $nestedWhere->equalTo('inactive', AssetsCategoryService::CATEGORY_STATUS_ACTIVE); } if ($selectedId) { $where->equalTo('id', $selectedId)->orPredicate($nestedWhere); } else { $where = $nestedWhere; } $select->where($where)->order('type_id'); }); $this->setEntity($entity); return $result; }
/** * Construct Where object from query parameters * * @param array $filterParams * @param bool $testApartments * @return Where */ public function constructWhereFromFilterParams($filterParams, $testApartments = true) { $auth = $this->getServiceLocator()->get('library_backoffice_auth'); $hasDevTestRole = $auth->hasRole(Roles::ROLE_DEVELOPMENT_TESTING); $where = new Where(); $table = DbTables::TBL_APARTMENTS; $productStatusGroups = Objects::getProductStatusGroups(); if (!$testApartments || !$hasDevTestRole) { $where->expression($table . '.id NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []); } if (isset($filterParams["status"]) && $filterParams["status"] != '0') { $statusGroup = $productStatusGroups[$filterParams["status"]]; $where->in($table . ".status", $statusGroup); } if (isset($filterParams["building_id"]) && $filterParams["building_id"] != '0') { $where->expression($table . '.id IN (SELECT `apartment_id` FROM ' . DbTables::TBL_APARTMENT_GROUP_ITEMS . ' JOIN ' . DbTables::TBL_APARTMENT_GROUPS . ' ON `apartment_group_id` = ' . DbTables::TBL_APARTMENT_GROUPS . '.id WHERE ' . DbTables::TBL_APARTMENT_GROUPS . '.id = ' . $filterParams['building_id'] . ' ) ', []); } if (isset($filterParams["address"]) && $filterParams["address"] != '') { $addressQuery = $filterParams["address"]; $nestedWhere = new Where(); $nestedWhere->like($table . '.name', '%' . $addressQuery . '%')->or->like('det1.name', '%' . $addressQuery . '%')->or->like('det2.name', '%' . $addressQuery . '%')->or->like($table . '.address', '%' . $addressQuery . '%')->or->like($table . '.unit_number', '%' . $addressQuery . '%')->or->like($table . '.postal_code', '%' . $addressQuery . '%'); $where->addPredicate($nestedWhere); } if (isset($filterParams['createdDate']) && $filterParams['createdDate'] !== '') { $createdDate = explode(' - ', $filterParams['createdDate']); $where->between($table . '.create_date', $createdDate['0'], $createdDate['1']); } return $where; }
/** * @param $backofficeUserId int * @return bool */ public function unassignUserFromTasks($backofficeUserId) { /** * @var UserManager $backofficeUsersDao */ $userManagerDao = $this->getServiceLocator()->get('dao_user_user_manager'); $taskStaffDao = $this->getServiceLocator()->get('dao_task_staff'); $userInfo = $userManagerDao->fetchOne(['id' => $backofficeUserId]); $where = new Where(); $where->in('type', [TaskService::STAFF_HELPER, TaskService::STAFF_FOLLOWER])->equalTo('user_id', $backofficeUserId); $taskStaffDao->deleteWhere($where); $taskStaffs = $taskStaffDao->getStaffTasks($backofficeUserId); $managerId = $userInfo->getManager_id(); $oneMonthBeforeNow = strtotime('-1 month'); if ($taskStaffs->count()) { foreach ($taskStaffs as $taskStaff) { if ($taskStaff->getType() == TaskService::STAFF_VERIFIER) { if (strtotime($taskStaff->getStartDate()) >= $oneMonthBeforeNow) { $taskStaffDao->save(['user_id' => $managerId], ['id' => $taskStaff->getIId()]); } else { $taskStaffDao->save(['user_id' => UserService::AUTO_VERIFY_USER_ID], ['id' => $taskStaff->getIId()]); } } if ($taskStaff->getType() == TaskService::STAFF_RESPONSIBLE) { if ($taskStaff->getTaskStatus() != TaskService::STATUS_DONE && $taskStaff->getTaskStatus() != TaskService::STATUS_VERIFIED) { $taskStaffDao->deleteWhere(['user_id' => $backofficeUserId, 'id' => $taskStaff->getIId()]); } } } } return true; }
/** * @param $ids * @param $limitAttempt * @return int */ public function incrementAttempts($ids, $limitAttempt) { if ($limitAttempt) { $sql = "IF(attempts >= {$limitAttempt} , {$limitAttempt}, attempts + 1)"; } else { $sql = "attempts + 1"; } $where = new Where(); $where->in('id', $ids); return $this->update(['attempts' => new Expression($sql)], $where); }
public function getMostRecentListing($id = null) { $subSelect = new Select(); $subSelect->columns(array('mostRecent' => new Expression('MAX(`listings_id`)'))); $subSelect->from(ListingsTable::$tableName); $where = new Where(); $where->in('listings_id', $subSelect); $select = new Select(); $select->where($where)->from(ListingsTable::$tableName); return $this->selectWith($select)->current(); }
/** * @param array $roles * @return \Zend\Db\ResultSet\ResultSet */ public function getRouteData(array $roles) { $results = $this->select(function (Select $select) use($roles) { $where = new Where(); if (!empty($roles)) { $where->in('rp.roleId', $roles); } else { $where->expression(' 1 = ?', 0); } $select->join(array('rp' => 'tbl_route_permission'), 'tbl_route.routeId = rp.routeId', array('routeId'), Select::JOIN_INNER)->where($where)->quantifier(Select::QUANTIFIER_DISTINCT); }); return $results; }
public function getLatestListing() { $adapter = $this->getAdapter(); $platform = $adapter->getPlatform(); $quoteId = $platform->quoteIdentifier($this->listingsId); $select = new Select(); $select->from($this->tableName); $expression = new Expression(sprintf('MAX(%s)', $quoteId)); $subSelect = new Select(); $subSelect->from($this->tableName)->columns(array($expression)); $where = new Where(); $where->in($this->listingsId, $subSelect); $select->where($where); return $this->selectWith($select)->current(); }
/** * Gets the entity meta select predicate. * * @param string $id * @param array $eids * @return Select */ protected function getByMetaSelect($id, array $eids) { $where = new Where(); $where->in($id, $eids); $where->and; $nestedWhere = $where->nest(); $nestedWhere->equalTo('locale', $this->locale); $nestedWhere->or; $nestedWhere->isNull('locale'); $nestedWhere->unnest(); $metaSelect = $this->entityMetaTable->getSql()->select(); $metaSelect->where($where); $metaSelect->order('locale ASC'); return $metaSelect; }
public function getLatestTitle() { $adapter = $this->getAdapter(); $platform = $adapter->getPlatform(); $quoteId = $platform->quoteIdentifier($this->primaryKey); $select = new Select(); $select->from(self::$tableName); $expression = new Expression(sprintf('MAX(%s)', $quoteId)); $subSelect = new Select(); $subSelect->from(self::$tableName)->columns(array($expression)); $where = new Where(); $where->in($this->primaryKey, $subSelect); $select->where($where); //echo $select->getSqlString($platform); return $this->selectWith($select)->current(); }
/** * Garbage Collection * Only delete sessions that have expired. * * @param int $maxlifetime * @return true */ public function gc($maxlifetime) { $platform = $this->tableGateway->getAdapter()->getPlatform(); $where = new Where(); $where->lessThan($this->options->getModifiedColumn(), new Expression('(' . time() . ' - ' . $platform->quoteIdentifier($this->options->getLifetimeColumn()) . ')')); $rows = $this->tableGateway->select($where); $ids = []; /* @var \UthandoSessionManager\Model\Session $row */ foreach ($rows as $row) { $ids[] = $row->{$this->options->getIdColumn()}; } if (count($ids) > 0) { $where = new Where(); $result = (bool) $this->tableGateway->delete($where->in($this->options->getIdColumn(), $ids)); } else { $result = false; } return $result; }
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; }
/** * @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; }
/** * @param $groupId * @return array */ public function deactivateApartel($groupId) { /** * @var \Library\ActionLogger\Logger $actionLogger */ $actionLogger = $this->getServiceLocator()->get('ActionLogger'); try { /** * @var \DDD\Dao\Apartel\General $apartelGeneralDao */ $apartelGeneralDao = $this->getServiceLocator()->get('dao_apartel_general'); // get apartel data $apartelData = $apartelGeneralDao->fetchOne(['apartment_group_id' => $groupId], ['id']); if ($apartelData) { /** * @var \DDD\Dao\ApartmentGroup\ApartmentGroup $groupsManagementDao * @var \DDD\Dao\Apartel\Inventory $apartelInventoryDao * @var \DDD\Dao\Apartel\OTADistribution $apartelOtaDistributionDao * @var \DDD\Dao\Apartel\Rate $apartelRateDao * @var \DDD\Dao\Apartel\RelTypeApartment $apartelTypesRelDao * @var \DDD\Dao\Apartel\Type $apartelTypeDao * @var \DDD\Dao\Apartel\Details $apartelDetailsDao * @var \DDD\Dao\Textline\Group $apartmentGroupTextlineDao */ $groupsManagementDao = $this->getServiceLocator()->get('dao_apartment_group_apartment_group'); $apartelInventoryDao = $this->getServiceLocator()->get('dao_apartel_inventory'); $apartelOtaDistributionDao = $this->getServiceLocator()->get('dao_apartel_ota_distribution'); $apartelRateDao = $this->getServiceLocator()->get('dao_apartel_rate'); $apartelTypesRelDao = $this->getServiceLocator()->get('dao_apartel_rel_type_apartment'); $apartelTypeDao = $this->getServiceLocator()->get('dao_apartel_type'); $apartelDetailsDao = $this->getServiceLocator()->get('dao_apartel_details'); $apartmentGroupTextlineDao = $this->getServiceLocator()->get('dao_textline_group'); $apartelImagesPath = DirectoryStructure::FS_GINOSI_ROOT . DirectoryStructure::FS_IMAGES_ROOT . DirectoryStructure::FS_IMAGES_APARTEL_BG_IMAGE . $apartelData->getId(); // remove apartel images if (is_writable($apartelImagesPath)) { FileUtils::deleteDir($apartelImagesPath); } // delete apartel inventory $apartelInventoryDao->delete(['apartel_id' => $apartelData->getId()]); // delete apartel ota distribution data $apartelOtaDistributionDao->delete(['apartel_id' => $apartelData->getId()]); // delete apartel rates $apartelRateDao->delete(['apartel_id' => $apartelData->getId()]); $apartelTypeRels = $apartelTypeDao->fetchAll(['apartel_id' => $apartelData->getId()], ['id']); // delete apartel type rel to apartments foreach ($apartelTypeRels as $apartelTypeRel) { $apartelTypesRelDao->delete(['apartel_type_id' => $apartelTypeRel->getId()]); } // delete apartel types $apartelTypeDao->delete(['apartel_id' => $apartelData->getId()]); // get apartel textlines id's $apartelTextiles = $apartelDetailsDao->fetchOne(['apartel_id' => $apartelData->getId()], ['content_textline_id', 'moto_textline_id', 'meta_description_textline_id']); // delete apartel details $apartelDetailsDao->delete(['apartel_id' => $apartelData->getId()]); $apartelTextlineWhere = new Where(); $apartelTextlineWhere->in('id', [$apartelTextiles->getContentTextlineId(), $apartelTextiles->getMotoTextlineId(), $apartelTextiles->getMetaDescriptionTextlineId()]); // delete apartel textlines $apartmentGroupTextlineDao->delete($apartelTextlineWhere); // delete apartel general data $apartelGeneralDao->delete(['id' => $apartelData->getId()]); // save apartel usage $groupsManagementDao->save(['usage_apartel' => 0], ['id' => $groupId]); } $actionLogger->save(ActionLogger::MODULE_APARTMENT_GROUPS, $groupId, ActionLogger::ACTION_APARTMENT_GROUPS_USAGE, 'Delete Apartel'); return ['status' => 'success', 'msg' => TextConstants::SUCCESS_DEACTIVATE]; } catch (\Exception $e) { $this->gr2logException($e, "Apartel wasn't deactivated", ['apartment_group_id' => $groupId]); return ['status' => 'error', 'msg' => TextConstants::SERVER_ERROR]; } }
/** * @param array $params * @return Where * @throws \Exception */ private function constructWhereForTransactionSearch($params) { $where = new Where(); $transactionTable = DbTables::TBL_EXPENSE_TRANSACTIONS; if (empty($params['transactionIdList'])) { if (empty($params['poId'])) { throw new \Exception('Purchase order id is invalid.'); } $where->equalTo("{$transactionTable}.expense_id", $params['poId']); if (!empty($params['transactionId'])) { $where->equalTo("{$transactionTable}.id", $params['transactionId']); } if (!empty($params['accountFrom'])) { $where->equalTo("{$transactionTable}.money_account_id", $params['accountFrom']); } if (!empty($params['accountTo'])) { $where->equalTo("{$transactionTable}.account_to_id", $params['accountTo']); } if (!empty($params['transactionDate'])) { $where->equalTo("{$transactionTable}.transaction_date", date('Y-m-d', strtotime($params['transactionDate']))); } if (!empty($params['creationDate'])) { $where->expression("date({$transactionTable}.creation_date) = ?", [date('Y-m-d', strtotime($params['creationDate']))]); } if (!empty($params['amount'])) { $where->expression("abs({$transactionTable}.amount) = ?", $params['amount']); } } else { $where->in("{$transactionTable}.id", $params['transactionIdList']); } return $where; }
/** * @param $fromDate * @param $toDate * @param int $currency * @param bool|false $paginated * @param string $filter * @param string $orderBy * @param string $order * @param array $skipTypes * @return \Zend\Db\ResultSet\ResultSet|Paginator */ public function getVouchersByDate($fromDate, $toDate, $currency = 0, $skipTypes = array(), $paginated = false, $filter = '', $orderBy = 'voucherNo', $order = 'ASC') { if ($paginated) { $select = new Select($this->table); $where = new Where(); $where->in('status', array('A', 'C', 'F'))->AND->between('approvedDate', $fromDate, $toDate)->AND->literal("concat_ws(' ',requester, description, voucherNo, accountType, amount, voucherDate) LIKE ?", '%' . $filter . '%'); if (!empty($skipTypes)) { $where->notIn('accountTypeId', $skipTypes); } if ($currency > 0) { $where->equalTo('currencyId', $currency); } $select->where($where); $select->order($orderBy . ' ' . $order); return $this->paginateWith($select); } $results = $this->select(function (Select $select) use($fromDate, $toDate) { $where = new Where(); $where->in('status', array('A', 'C', 'F'))->AND->between('approvedDate', $fromDate, $toDate); $select->where($where)->order('voucherNo asc'); }); return $results; }
public function fetchAllByIdTipoEmpleado($ids) { $sql = new Sql($this->adapter); $select = $sql->select(); $select->from($this->table)->join('tab_tipo_empleado', 'tab_tipo_empleado.id = tab_plantilla.id_tipo_empleado', array('tipo_empleado' => 'tipo'))->join('tab_unidad_negocio', 'tab_unidad_negocio.id = tab_tipo_empleado.id_unidad', array('id_unidad_negocio' => 'id', 'unidad_negocio' => 'nombre')); $where = new Where(); $where->in('tab_plantilla.id_tipo_empleado', $ids); $select->where($where); $statement = $sql->prepareStatementForSqlObject($select); $resultSet = $statement->execute(); $entities = array(); foreach ($resultSet as $row) { $entity = new Entity\Plantilla(array('id' => $row["id"], 'id_tipo_empleado' => $row["id_tipo_empleado"], 'tipo_empleado' => $row["tipo_empleado"], 'nombre' => $row["nombre"], 'descripcion' => $row["descripcion"], 'periodo' => $row["periodo"], 'id_unidad_negocio' => $row["id_unidad_negocio"], 'unidad_negocio' => $row["unidad_negocio"])); $entities[] = $entity->toArray(); } return $entities; }
/** * @param $categoryData * @param $categoryId * @return bool */ public function saveCategory($categoryData, $categoryId = 0) { /** * @var \DDD\Dao\Warehouse\Category $categoryDao * @var SKU $skuDao */ $categoryDao = $this->getServiceLocator()->get('dao_warehouse_category'); $skuDao = $this->getServiceLocator()->get('dao_warehouse_sku'); $auth = $this->getServiceLocator()->get('library_backoffice_auth'); $userId = $auth->getIdentity()->id; try { $categoryDao->beginTransaction(); if (isset($categoryData['sku_names']) && count($categoryData['sku_names'])) { foreach ($categoryData['sku_names'] as $sku) { $sku = trim($sku); if (empty($sku)) { continue; } if ($skuDao->fetchOne(['name' => trim($sku)], ['id'])) { throw new \RuntimeException(TextConstants::UNIQUE_SKU . '. SKU# ' . $sku); } } } $params = ['name' => $categoryData['name'], 'type_id' => $categoryData['type'], 'creator_id' => $userId]; if ($categoryId) { $categoryDao->save($params, ['id' => $categoryId]); } else { $categoryId = $categoryDao->save($params); } if (isset($categoryData['sku_names']) && count($categoryData['sku_names'])) { foreach ($categoryData['sku_names'] as $sku) { if (empty($sku)) { continue; } $skuDao->save(['name' => trim($sku), 'asset_category_id' => $categoryId]); } } if (isset($categoryData['aliases']) && count($categoryData['aliases'])) { /** @var \DDD\Dao\Warehouse\Alias $assetCategoryAliasesDao */ $assetCategoryAliasesDao = $this->getServiceLocator()->get('dao_warehouse_alias'); $existingAliases = $this->getAliases($categoryId); foreach ($categoryData['aliases'] as $submittedAliasId => $submittedAliasName) { if (empty(trim($submittedAliasName))) { continue; } if (array_key_exists($submittedAliasId, $existingAliases)) { if (trim($submittedAliasName) == $existingAliases[$submittedAliasId]) { continue; } $assetCategoryAliasesDao->save(['name' => $submittedAliasName], ['id' => $submittedAliasId]); unset($existingAliases[$submittedAliasId]); } else { $assetCategoryAliasesDao->save(['name' => $submittedAliasName, 'asset_category_id' => $categoryId]); } } if (count($existingAliases)) { $deleteWhere = new Where(); $deleteWhere->in('id', array_keys($existingAliases)); $assetCategoryAliasesDao->delete($deleteWhere); } } $categoryDao->commitTransaction(); } catch (\Exception $e) { $categoryDao->rollbackTransaction(); return $e; } return $categoryId; }
public function conciergeSave($data, $id, $global) { /** * @var BackofficeAuthenticationService $auth * @var \DDD\Dao\ApartmentGroup\ApartmentGroupItems $apartmentGroupItemsDao * @var \Library\ActionLogger\Logger $actionLogger */ $actionLogger = $this->getServiceLocator()->get('ActionLogger'); $accGroupsManagementDao = $this->getConcierge(); $insert_id = $id; if (!$global) { $auth = $this->getServiceLocator()->get('library_backoffice_auth'); $conciergeData = $accGroupsManagementDao->getRowById($id); if ($conciergeData->getGroupManagerId() == $auth->getIdentity()->id) { $global = true; } } if (!isset($data['check_users']) || !(int) $data['check_users']) { $data['concierge_email'] = null; } if ($global) { $accGroupData = array('name' => $data['name'], 'timezone' => $data['timezone'], 'group_manager_id' => isset($data['group_manager_id']) ? $data['group_manager_id'] : 0, 'usage_concierge_dashboard' => isset($data['check_users']) && (int) $data['check_users'] > 0 ? 1 : 0, 'usage_cost_center' => isset($data['usage_cost_center']) && (int) $data['usage_cost_center'] > 0 ? 1 : 0, 'usage_building' => isset($data['usage_building']) && (int) $data['usage_building'] > 0 ? 1 : 0, 'usage_apartel' => isset($data['usage_apartel']) && (int) $data['usage_apartel'] > 0 ? 1 : 0, 'usage_performance_group' => isset($data['usage_performance_group']) && (int) $data['usage_performance_group'] > 0 ? 1 : 0, 'email' => !empty($data['concierge_email']) ? $data['concierge_email'] : null, 'country_id' => !empty($data['country_id']) ? $data['country_id'] : null); $buildingDetailsDao = $this->getServiceLocator()->get('dao_apartment_group_building_details'); $accommodationsDao = $this->getAccommodationsDao(); if ($id > 0) { $apartmentGroupCurrentState = $accGroupsManagementDao->getRowById($id); // check for change Apartment Group name if ($apartmentGroupCurrentState->getName() != $data['name']) { $actionLogger->save(ActionLogger::MODULE_APARTMENT_GROUPS, $id, ActionLogger::ACTION_APARTMENT_GROUPS_NAME, 'The Apartment Group name change from "' . $apartmentGroupCurrentState->getName() . '" to "' . $data['name'] . '"'); } // check for change Usage - Cost Center if ($apartmentGroupCurrentState->getCostCenter() != $data['usage_cost_center']) { $value = $data['usage_cost_center'] ? 'is set as' : 'is set as non'; $actionLogger->save(ActionLogger::MODULE_APARTMENT_GROUPS, $id, ActionLogger::ACTION_APARTMENT_GROUPS_USAGE, 'Group\'s usage ' . $value . ' Cost Center'); } // check for change Usage - Concierge Dashboard if ($apartmentGroupCurrentState->getIsArrivalsDashboard() != $data['check_users']) { $value = $data['check_users'] ? 'is set as' : 'is set as non'; $actionLogger->save(ActionLogger::MODULE_APARTMENT_GROUPS, $id, ActionLogger::ACTION_APARTMENT_GROUPS_USAGE, 'Group\'s usage ' . $value . ' Concierge Dashboard'); } // check for change Usage - Building if ($apartmentGroupCurrentState->isBuilding() != $data['usage_building']) { $value = $data['usage_building'] ? 'is set as' : 'is set as non'; $actionLogger->save(ActionLogger::MODULE_APARTMENT_GROUPS, $id, ActionLogger::ACTION_APARTMENT_GROUPS_USAGE, 'Group\'s usage ' . $value . ' Building'); } // check for change Usage - Apartel if ($apartmentGroupCurrentState->getIsApartel() != $data['usage_apartel']) { $value = $data['usage_apartel'] ? 'is set as' : 'is set as non'; $actionLogger->save(ActionLogger::MODULE_APARTMENT_GROUPS, $id, ActionLogger::ACTION_APARTMENT_GROUPS_USAGE, 'Group\'s usage ' . $value . ' Apartel'); } if (!$apartmentGroupCurrentState->getIsApartel() && $data['usage_apartel']) { /** * @var \DDD\Service\ApartmentGroup\Usages\Apartel $apartelUsageService */ $apartelUsageService = $this->getServiceLocator()->get('service_apartment_group_usages_apartel'); $apartelUsageService->save(['group_id' => $id]); } // check for change Usage - Performance if ($apartmentGroupCurrentState->getIsPerformanceGroup() != $data['usage_performance_group']) { $value = $data['usage_performance_group'] ? 'is set as' : 'is set as non'; $actionLogger->save(ActionLogger::MODULE_APARTMENT_GROUPS, $id, ActionLogger::ACTION_APARTMENT_GROUPS_USAGE, 'Group\'s usage ' . $value . ' Performance'); } $apartmentGroupItemsDao = $this->getServiceLocator()->get('dao_apartment_group_apartment_group_items'); $apartmentsCurrentList = $apartmentGroupItemsDao->getApartmentGroupItems($id); if (isset($data['accommodations']) && is_array($data['accommodations'])) { $newApartmentsList = $data['accommodations']; } else { $newApartmentsList = []; } // check for removing Apartments from group foreach ($apartmentsCurrentList as $currentApartment) { if (($key = array_search($currentApartment->getApartmentId(), $newApartmentsList)) === FALSE) { $accommodationsDao->save(['building_id' => 0], ['id' => $currentApartment->getApartmentId()]); $actionLogger->save(ActionLogger::MODULE_APARTMENT_GROUPS, $id, ActionLogger::ACTION_APARTMENT_GROUPS_APARTMENT_LIST, 'Apartment ' . $currentApartment->getApartmentName() . ' (id: ' . $currentApartment->getApartmentId() . ') removed from group'); } else { unset($newApartmentsList[$key]); } } // check for adding Apartments to group if (!empty($newApartmentsList)) { $apartmentGeneralService = $this->getServiceLocator()->get('service_apartment_general'); foreach ($newApartmentsList as $addedApartmentId) { $apartmentGeneral = $apartmentGeneralService->getApartmentGeneral($addedApartmentId); $actionLogger->save(ActionLogger::MODULE_APARTMENT_GROUPS, $id, ActionLogger::ACTION_APARTMENT_GROUPS_APARTMENT_LIST, 'Apartment ' . $apartmentGeneral['name'] . ' (id: ' . $addedApartmentId . ') added to group'); } } $accGroupsManagementDao->save($accGroupData, ['id' => (int) $id]); } else { $insert_id = $accGroupsManagementDao->save($accGroupData); } if (isset($data['usage_building']) && $data['usage_building']) { if ($buildingDetailsDao->fetchOne(['apartment_group_id' => (int) $insert_id])) { $buildingDetailsDao->save($buildingDetailsData, ['apartment_group_id' => (int) $insert_id]); } else { $buildingDetailsDao->save(array_merge($buildingDetailsData, ['apartment_group_id' => (int) $insert_id])); } } $accGroupsAccommodationDao = $this->getConciergeAccommodation(); $accGroupsAccommodationDao->deleteWhere(['apartment_group_id' => (int) $insert_id]); $buildingFacilityItemsDao = $this->getServiceLocator()->get('dao_building_facility_items'); if (isset($data['usage_building']) && $data['usage_building'] == 0) { if ($accommodationsDao->fetchOne(['building_id' => (int) $insert_id])) { $accommodationsDao->save(['building_id' => 0], ['building_id' => (int) $insert_id]); } $buildingFacilityItemsDao->deleteWhere(['building_id' => $insert_id]); } if (isset($data['usage_building']) && $data['usage_building'] == 1) { $buildingFacilityItemsDao->deleteWhere(['building_id' => $insert_id]); if (isset($data['facilities'])) { foreach ($data['facilities'] as $facilityId => $isSet) { if ($isSet) { $buildingFacilityItemsDao->save(['facility_id' => $facilityId, 'building_id' => $insert_id]); } } } } if (isset($data['accommodations']) && !empty($data['accommodations'])) { foreach ($data['accommodations'] as $row) { $accGroupsAccommodationDao->save(['apartment_group_id' => (int) $insert_id, 'apartment_id' => (int) $row]); } //update all accommmodations for this group and set building_id to this group id if (isset($data['usage_building']) && $data['usage_building']) { $where = new Where(); $where->in('id', $data['accommodations']); $accommodationsDao->update(['building_id' => (int) $insert_id], $where); } } } else { if ($id > 0) { $accGroupsManagementDao->setEntity(new \DDD\Domain\ApartmentGroup\ApartmentGroup()); } } return $insert_id; }
public function updateMoneyTransactions($toBeCombinedMoneyAccountTransactionIds, $combinedMoneyTransactionId) { $where = new Where(); $where->in($this->getTable() . '.money_transaction_id', $toBeCombinedMoneyAccountTransactionIds); $this->save(['money_transaction_id' => $combinedMoneyTransactionId], $where); }
/** * @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; }
/** * @param array $filterParams * @return Where */ protected function constructWhereFromFilterParams($filterParams) { $where = new Where(); if (!empty($filterParams['apartment_groups'])) { $where->equalTo('apartment_groups_items.apartment_group_id', $filterParams['apartment_groups']); } if (!empty($filterParams['tags'])) { $where->in('review_category_rel.apartment_review_category_id', explode(',', $filterParams['tags'][0])); } if (!empty($filterParams['arrival_date_range'])) { $dateArray = explode(' - ', $filterParams['arrival_date_range']); if (isset($dateArray[1])) { $dateFrom = $dateArray[0]; $dateTo = $dateArray[1]; $where->expression('DATE(reservations.date_from) >= DATE("' . $dateFrom . '") AND ' . 'DATE(reservations.date_from) <= DATE("' . $dateTo . '")', []); } } if (!empty($filterParams['departure_date_range'])) { $dateArray = explode(' - ', $filterParams['departure_date_range']); if (isset($dateArray[1])) { $dateFrom = $dateArray[0]; $dateTo = $dateArray[1]; $where->expression('DATE(reservations.date_to) >= DATE("' . $dateFrom . '") AND ' . 'DATE(reservations.date_to) <= DATE("' . $dateTo . '")', []); } } if (!empty($filterParams['stay_length_from'])) { $where->expression('DATEDIFF(reservations.date_to, reservations.date_from) >= ' . $filterParams['stay_length_from'], []); } if (!empty($filterParams['stay_length_to'])) { $where->expression('DATEDIFF(reservations.date_to, reservations.date_from) <= ' . $filterParams['stay_length_to'], []); } if (!empty($filterParams['score_filter']) && is_array($filterParams['score_filter']) && count($filterParams['score_filter']) != 5) { $where->in(DbTables::TBL_PRODUCT_REVIEWS . '.score', $filterParams['score_filter']); } return $where; }
/** * * @param array $filterParams * @param bool $testApartments * @return Where */ public function constructWhereFromFilterParams($filterParams, $securityLevels = []) { /* @var $auth \Library\Authentication\BackofficeAuthenticationService */ $auth = $this->getServiceLocator()->get('library_backoffice_auth'); $hasDevTestRole = $auth->hasRole(Roles::ROLE_DEVELOPMENT_TESTING); $documentsTable = DbTables::TBL_DOCUMENTS; $where = new Where(); if (!$hasDevTestRole) { $where->expression('apartment.id NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []); } if (isset($filterParams["validation-range"]) && $filterParams["validation-range"] != '') { $tempDatesArray = explode(' - ', $filterParams['validation-range']); $validFrom = $tempDatesArray[0]; $validTo = $tempDatesArray[1]; $where->expression('DATE(' . $documentsTable . '.valid_from) >= DATE("' . $validFrom . '") ' . 'AND DATE(' . $documentsTable . '.valid_to) <= DATE("' . $validTo . '") ', []); } if (isset($filterParams['createdDate']) && $filterParams['createdDate'] !== '') { $createdDate = explode(' - ', $filterParams['createdDate']); $where->between($documentsTable . '.created_date', $createdDate['0'] . ' 00:00:00', $createdDate['1'] . ' 23:59:59'); } if (!empty($filterParams['supplier_id']) && $filterParams['supplier_id'] != '78') { $where->equalTo($documentsTable . '.supplier_id', $filterParams['supplier_id']); } if (!empty($filterParams['document_type'])) { $where->equalTo($documentsTable . '.type_id', $filterParams['document_type']); } if (isset($filterParams['legal_entity_id']) && $filterParams['legal_entity_id'] != 0) { $where->equalTo($documentsTable . '.legal_entity_id', $filterParams['legal_entity_id']); } if (isset($filterParams['signatory_id']) && $filterParams['signatory_id'] != 0) { $where->equalTo($documentsTable . '.signatory_id', $filterParams['signatory_id']); } if (!empty($filterParams['author_id'])) { $where->equalTo($documentsTable . '.created_by', $filterParams['author_id']); } if (!empty($filterParams['account_number'])) { $where->like($documentsTable . '.account_number', '%' . $filterParams['account_number'] . '%'); } if (!empty($filterParams['entity_id'])) { $where->equalTo($documentsTable . '.entity_id', $filterParams['entity_id']); } if (!empty($filterParams['entity_type'])) { $where->equalTo($documentsTable . '.entity_type', $filterParams['entity_type']); } if (!empty($filterParams['account_holder'])) { $where->like($documentsTable . '.account_holder', '%' . $filterParams['account_holder'] . '%'); } if (!empty($filterParams['has_attachment'])) { switch ($filterParams['has_attachment']) { case 1: $where->isNotNull($documentsTable . '.attachment')->notEqualTo($documentsTable . '.attachment', ''); break; case 2: $where->NEST->isNull($documentsTable . '.attachment')->OR->equalTo($documentsTable . '.attachment', '')->UNNEST; break; } } if (isset($filterParams['has_url']) && !empty($filterParams['has_url'])) { switch ($filterParams['has_url']) { case 1: $where->notEqualTo($documentsTable . '.url', ''); break; case 2: $where->equalTo($documentsTable . '.url', ''); break; } } $hasSecurityAccess = $auth->hasRole(Roles::ROLE_DOCUMENTS_MANAGEMENT_GLOBAL); if (isset($securityLevels[0]) && !$hasSecurityAccess) { $where->in($documentsTable . '.security_level', $securityLevels); } return $where; }
/** * Returns SQL WHERE string created for the specified key fields * * @param mixed $mId primary key value * @return Where|string */ protected function getPrimaryWhere($mId) { $oWhere = new Where(); $sField = $this->sTableName . '.' . $this->sPrimaryKey; if (is_array($mId)) { $oWhere->in($sField, $mId); } else { $oWhere->equalTo($sField, $mId); } return $oWhere; }
public function fetchAllInIdPlantillas($ids) { $sql = new Sql($this->adapter); $select = $sql->select(); $select->from($this->table)->join('cat_estado_reporte', 'cat_estado_reporte.id = tab_reporte.id_estado', array('estado' => 'nombre')); $where = new Where(); $where->in('tab_reporte.id_plantilla', $ids); $select->where($where); $statement = $sql->prepareStatementForSqlObject($select); $resultSet = $statement->execute(); $entities = array(); foreach ($resultSet as $row) { $entity = new Entity\Reporte(array('id' => $row["id"], 'id_plantilla' => $row["id_plantilla"], 'nombre' => $row["nombre"], 'descripcion' => $row["descripcion"], 'periodo' => $row["periodo"], 'id_estado' => $row["id_estado"], 'estado' => $row["estado"], 'comentarios' => $row["comentarios"])); $entities[] = $entity->toArray(); } return $entities; }
public function fetchAllByIdRolesUsuario($ids) { $sql = new Sql($this->adapter); $select = $sql->select(); $select->from($this->table); $where = new Where(); $where->in('tab_usuario.id_rol_usuario', $ids); $select->where($where); $statement = $sql->prepareStatementForSqlObject($select); $resultSet = $statement->execute(); $entities = array(); foreach ($resultSet as $row) { $entity = new Entity\Usuario(array('id' => $row["id"], 'id_rol_usuario' => $row["id_rol_usuario"], 'nombre' => $row["nombre"])); $entities[] = $entity->toArray(); } return $entities; }
/** * @param array $moneyAccountTransactionIds */ public function deleteByIds($moneyAccountTransactionIds) { $where = new Where(); $where->in($this->getTable() . '.id', $moneyAccountTransactionIds); $this->delete($where); }
public function getBookingForReservationConfirmationMail($id) { $result = $this->fetchAll(function (Select $select) use($id) { $where = new Where(); $where->in($this->getTable() . '.id', $id); $select->columns(['id', 'apartment_id_assigned', 'res_number', 'date_from', 'date_to', 'guest_language_iso', 'guest_first_name', 'guest_last_name', 'guest_email', 'secondary_email', 'overbooking_status', 'guest_arrival_time' => 'guest_arrival_time', 'pax' => 'man_count', 'occupancy' => 'occupancy', 'price', 'booker_price', 'penalty', 'penalty_val', 'apartment_currency_code', 'currency_rate', 'refundable_before_hours', 'guest_currency_code', 'acc_city_id', 'acc_country_id', 'guest_phone', 'model', 'review_page_hash', 'rate_name', 'is_refundable', 'partner_id', 'guest_balance', 'rate_capacity' => 'man_count', 'guest_address'])->join(['apartments' => DbTables::TBL_APARTMENTS], $this->getTable() . '.apartment_id_assigned = apartments.id', ['acc_name' => 'name', 'apartment_assigned_postal_code' => 'postal_code', 'apartment_assigned_address' => 'address'], Select::JOIN_INNER)->join(['apartment_description' => DbTables::TBL_PRODUCT_DESCRIPTIONS], 'apartments.id = apartment_description.apartment_id', ['check_in', 'check_out'], Select::JOIN_INNER)->join(['apartment_detail' => DbTables::TBL_APARTMENTS_DETAILS], 'apartments.id = apartment_detail.apartment_id', ['cleaning_fee'], Select::JOIN_INNER)->join(['city' => DbTables::TBL_CITIES], $this->getTable() . '.acc_city_id = city.id', [], Select::JOIN_LEFT)->join(['location_details' => DbTables::TBL_LOCATION_DETAILS], 'city.detail_id = location_details.id', ['city_tot' => 'tot', 'city_tot_type' => 'tot_type', 'tot_included' => 'tot_included', 'city_vat' => 'vat', 'city_vat_type' => 'vat_type', 'vat_included' => 'vat_included', 'city_tax' => 'city_tax', 'city_tax_type' => 'city_tax_type', 'city_tax_included' => 'city_tax_included', 'city_sales_tax' => 'sales_tax', 'city_sales_tax_type' => 'sales_tax_type', 'sales_tax_included' => 'sales_tax_included', 'apartment_city_thumb' => 'thumbnail'], Select::JOIN_LEFT)->join(['country' => DbTables::TBL_COUNTRIES], $this->getTable() . '.acc_country_id = country.id', ['country_phone_apartment' => 'contact_phone'], Select::JOIN_LEFT)->join(['country2' => DbTables::TBL_COUNTRIES], $this->getTable() . '.guest_country_id = country2.id', ['country_phone_guest' => 'contact_phone'], Select::JOIN_LEFT)->join(['country_currency' => DbTables::TBL_CURRENCY], 'country.currency_id = country_currency.id', ['country_currency' => 'code'], Select::JOIN_LEFT)->join(['log' => DbTables::TBL_ACTION_LOGS], new Expression($this->getTable() . '.id = log.identity_id AND log.module_id = ' . Logger::MODULE_BOOKING . ' AND log.action_id = ' . Logger::ACTION_COMMENT . ' AND log.user_id = ' . UserService::USER_GUEST), ['remarks' => 'value'], Select::JOIN_LEFT)->join(['partner' => DbTables::TBL_BOOKING_PARTNERS], $this->getTable() . '.partner_id = partner.gid', ['emailing_enabled' => 'customer_email', 'partner_name'], Select::JOIN_INNER)->where($where)->order('date_from'); }); return $result; }