예제 #1
0
파일: Team.php 프로젝트: arbi/MyCode
 public function getTeamListDetails($offset, $limit, $sortCol, $sortDir, $like, $deactivatedIncluded = '1')
 {
     $oldEntityPrototype = $this->resultSetPrototype->getArrayObjectPrototype();
     $this->resultSetPrototype->setArrayObjectPrototype(new PeopleTeamsTableRow());
     $result = $this->fetchAll(function (Select $select) use($offset, $limit, $sortCol, $sortDir, $like, $deactivatedIncluded) {
         $sortColumns = ['is_disable', 'name', 'description', 'size', 'usage_department', 'usage_notifiable', 'usage_frontier', 'usage_security', 'usage_taskable', 'usage_procurement', 'usage_hiring', 'usage_storage'];
         $select->columns(['id' => 'id', 'is_active' => 'is_disable', 'name' => 'name', 'description' => 'description', 'usage_department' => 'usage_department', 'usage_notifiable' => 'usage_notifiable', 'usage_frontier' => 'usage_frontier', 'usage_security' => 'usage_security', 'usage_taskable' => 'usage_taskable', 'usage_procurement' => 'usage_procurement', 'usage_hiring' => 'usage_hiring', 'usage_storage' => 'usage_storage'])->join(['creator' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.id = creator.team_id AND creator.type =' . TeamService::STAFF_CREATOR), [])->join(['staff' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.id = staff.team_id AND staff.type !=' . TeamService::STAFF_CREATOR . ' AND staff.type !=' . TeamService::STAFF_DIRECTOR . ' AND staff.user_id !=' . UserService::ANY_TEAM_MEMBER_USER_ID), ['size' => new Expression('COUNT(distinct(staff.user_id))')], Select::JOIN_LEFT);
         switch ($deactivatedIncluded) {
             case '2':
                 $select->where->equalTo($this->getTable() . '.is_disable', 1);
                 // only inactive ones
                 break;
             case '1':
                 $select->where->equalTo($this->getTable() . '.is_disable', 0);
                 // only active ones
                 break;
             case '0':
                 // all
                 break;
         }
         if ($like !== '') {
             $nestedWhere = new Predicate();
             $nestedWhere->like($this->getTable() . '.description', '%' . $like . '%');
             $nestedWhere->or;
             $nestedWhere->like($this->getTable() . '.name', '%' . $like . '%');
             $select->where->andPredicate($nestedWhere);
         }
         $select->group($this->getTable() . '.id')->order($sortColumns[$sortCol] . ' ' . $sortDir)->offset((int) $offset)->limit((int) $limit);
     });
     $this->resultSetPrototype->setArrayObjectPrototype($oldEntityPrototype);
     return $result;
 }
예제 #2
0
파일: Forum.php 프로젝트: galaco/chatter
 private function getThreadCounts()
 {
     $select = $this->getSelect('thread')->group('forum_id')->columns(['forum_id', 'count_for_forum' => new Expression('COUNT(forum_id)')]);
     $predicate = new Predicate(null, Predicate::OP_AND);
     $predicate->equalTo('deleted', "0");
     $select->where($predicate);
     return $select;
 }
예제 #3
0
 /**
  * Begin nesting predicates
  *
  * @return Predicate
  */
 public function nest()
 {
     $predicateSet = new Predicate();
     $predicateSet->setUnnest($this);
     $this->addPredicate($predicateSet, $this->nextPredicateCombineOperator ?: $this->defaultCombination);
     $this->nextPredicateCombineOperator = null;
     return $predicateSet;
 }
 /**
  * Get all empty transactions
  *
  * @param integer $limit
  * @return array
  */
 public function getEmptyTransactions($limit)
 {
     $predicate = new Predicate();
     $select = $this->select();
     $select->from(['a' => 'payment_transaction_list'])->columns(['id', 'slug'])->join(['b' => 'payment_transaction_item'], 'a.id = b.transaction_id', [], 'left')->where([$predicate->isNull('b.transaction_id')])->group('a.id')->limit($limit);
     $statement = $this->prepareStatementForSqlObject($select);
     $resultSet = new ResultSet();
     $resultSet->initialize($statement->execute());
     return $resultSet->toArray();
 }
예제 #5
0
파일: User.php 프로젝트: galaco/chatter
 public function getSettings($userId)
 {
     $predicate = new Predicate(null, Predicate::OP_AND);
     $predicate->equalTo('user_id', $userId);
     $select = new Select();
     $select->from($this->tableName);
     $select->where($predicate);
     $result = $this->select($select);
     return $result->toArray();
 }
예제 #6
0
 public function getEmailContent($type)
 {
     $predicate = new Predicate(null, Predicate::OP_AND);
     $predicate->equalTo('type', $type);
     $select = new Select();
     $select->from($this->tableName);
     $select->where($predicate);
     $result = $this->select($select);
     return $result->toArray();
 }
예제 #7
0
 public function getProductsById(array $productIds = array())
 {
     $wheres = array();
     foreach ($productIds as $productId) {
         $predicate = new Predicate\Predicate();
         $wheres[] = $predicate->equalTo('product_id', $productId);
     }
     $select = $this->getSelect()->where($wheres, Predicate\PredicateSet::OP_OR);
     return $this->selectManyModels($select);
 }
예제 #8
0
파일: Post.php 프로젝트: galaco/chatter
 public function updatePost($postId, $content)
 {
     $entity = ['content' => $content, 'last_updated' => date('Y/m/d H:i:s')];
     $predicate = new Predicate(null, Predicate::OP_AND);
     $predicate->equalTo('id', $postId);
     try {
         $result = parent::update($entity, $predicate, $this->tableName, null);
     } catch (Exception $e) {
         return false;
     }
     return true;
 }
예제 #9
0
 public function constructWhereFromFilterParams($filterParams, $download = false)
 {
     /* @var $auth \Library\Authentication\BackofficeAuthenticationService */
     $auth = $this->getServiceLocator()->get('library_backoffice_auth');
     $where = new Where();
     $table = DbTables::TBL_BOOKINGS;
     if (isset($filterParams["res_number"]) && $filterParams["res_number"] != '') {
         $where->like($table . '.res_number', '%' . $filterParams["res_number"] . '%');
     }
     if (isset($filterParams["rooms_count"])) {
         $apartmentTableName = $download ? DbTables::TBL_APARTMENTS : 'apartments';
         $roomsCount = $filterParams["rooms_count"] - 1;
         if ($roomsCount >= 0) {
             if ($roomsCount == 2) {
                 $where->greaterThanOrEqualTo($apartmentTableName . '.bedroom_count', $roomsCount);
             } else {
                 $where->equalTo($apartmentTableName . '.bedroom_count', $roomsCount);
             }
         }
     }
     if (isset($filterParams["status"]) && $filterParams["status"]) {
         if ($filterParams["status"] == Constants::NOT_BOOKED_STATUS) {
             $where->notEqualTo($table . '.status', Booking::BOOKING_STATUS_BOOKED);
         } else {
             $where->equalTo($table . '.status', $filterParams["status"]);
         }
     }
     if (isset($filterParams["arrival_status"]) && $filterParams['arrival_status'] != -1) {
         $where->equalTo($table . '.arrival_status', $filterParams["arrival_status"]);
     }
     if (isset($filterParams["overbooking_status"]) && $filterParams["overbooking_status"] != -1) {
         $where->equalTo($table . '.overbooking_status', $filterParams["overbooking_status"]);
     }
     if (isset($filterParams["apartel_id"]) && $filterParams["apartel_id"] != -2) {
         $where->equalTo($table . '.apartel_id', $filterParams["apartel_id"]);
     }
     if (isset($filterParams["product_id"]) && isset($filterParams["product"]) && $filterParams["product_id"] != '' && $filterParams["product"] != '') {
         $where->equalTo($table . '.apartment_id_origin', $filterParams["product_id"]);
     }
     if (isset($filterParams["assigned_product_id"]) && isset($filterParams["assigned_product"]) && $filterParams["assigned_product_id"] != '' && $filterParams["assigned_product"] != '') {
         $where->equalTo($table . '.apartment_id_assigned', $filterParams["assigned_product_id"]);
     }
     if (isset($filterParams["booking_date"]) && $filterParams["booking_date"] != '') {
         $dates = explode(' - ', $filterParams["booking_date"]);
         $startDate = $dates[0];
         $endDate = $dates[1];
         $where->expression('DATE(' . $table . '.timestamp) >= \'' . $startDate . '\'', []);
         $where->expression('DATE(' . $table . '.timestamp) <= \'' . $endDate . '\'', []);
     }
     if (isset($filterParams["arrival_date"]) && $filterParams["arrival_date"] != '') {
         $dates = explode(' - ', $filterParams["arrival_date"]);
         $startDate = $dates[0];
         $endDate = $dates[1];
         $where->lessThanOrEqualTo($table . ".date_from", $endDate);
         $where->greaterThanOrEqualTo($table . ".date_from", $startDate);
     }
     if (isset($filterParams["departure_date"]) && $filterParams["departure_date"] != '') {
         $dates = explode(' - ', $filterParams["departure_date"]);
         $startDate = $dates[0];
         $endDate = $dates[1];
         $where->lessThanOrEqualTo($table . ".date_to", $endDate);
         $where->greaterThanOrEqualTo($table . ".date_to", $startDate);
     }
     if (isset($filterParams["guest_first_name"]) && $filterParams["guest_first_name"] != '') {
         $where->like($table . '.guest_first_name', '%' . $filterParams["guest_first_name"] . '%');
     }
     if (isset($filterParams["guest_last_name"]) && $filterParams["guest_last_name"] != '') {
         $where->like($table . '.guest_last_name', '%' . $filterParams["guest_last_name"] . '%');
     }
     if (isset($filterParams["guest_phone"]) && $filterParams["guest_phone"] != '') {
         $nestedWhere = new Predicate();
         $nestedWhere->like($table . '.guest_phone', '%' . $filterParams["guest_phone"] . '%');
         $nestedWhere->OR;
         $nestedWhere->like($table . '.guest_travel_phone', '%' . $filterParams["guest_phone"] . '%');
         $where->addPredicate($nestedWhere);
     }
     if (isset($filterParams["guest_email"]) && $filterParams["guest_email"] != '') {
         $where->like($table . '.guest_email', '%' . $filterParams["guest_email"] . '%');
     }
     if (isset($filterParams["guest_secondary_email"]) && $filterParams["guest_secondary_email"] != '') {
         $where->like($table . '.secondary_email', '%' . $filterParams["guest_secondary_email"] . '%');
     }
     if (isset($filterParams["guest_country_id"]) && $filterParams["guest_country_id"] != '' && $filterParams["guest_country"] != '') {
         $where->equalTo($table . '.guest_country_id', $filterParams["guest_country_id"]);
     }
     if (isset($filterParams["apt_location_id"]) && $filterParams["apt_location_id"] != '') {
         $nestedWhere = new Predicate();
         $nestedWhere->equalTo($table . '.acc_country_id', $filterParams["apt_location_id"]);
         $nestedWhere->OR;
         $nestedWhere->equalTo($table . '.acc_city_id', $filterParams["apt_location_id"]);
         $where->addPredicate($nestedWhere);
     }
     if (isset($filterParams["partner_id"]) && $filterParams["partner_id"] != '0') {
         $where->equalTo($table . '.partner_id', $filterParams["partner_id"]);
     }
     if (isset($filterParams["partner_reference"]) && $filterParams["partner_reference"] != '') {
         $where->like($table . '.partner_ref', '%' . $filterParams["partner_reference"] . '%');
     }
     if (isset($filterParams["payment_model"]) && $filterParams["payment_model"] != '0') {
         $where->equalTo($table . '.model', $filterParams["payment_model"]);
     }
     if (!$auth->hasRole(Roles::ROLE_DEVELOPMENT_TESTING)) {
         $where->expression($table . '.apartment_id_assigned NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
     }
     if (isset($filterParams["no_collection"]) && $filterParams["no_collection"] > 1) {
         $where->equalTo($table . '.no_collection', $filterParams["no_collection"] == 2 ? 1 : 0);
     }
     if (isset($filterParams["channel_res_id"]) && $filterParams["channel_res_id"]) {
         $where->equalTo($table . '.channel_res_id', $filterParams["channel_res_id"]);
     }
     return $where;
 }
 /**
  * Delete not active empty connections
  *
  * @return boolean|string
  */
 public function deleteNotActiveEmptyConnections()
 {
     try {
         $this->adapter->getDriver()->getConnection()->beginTransaction();
         $predicate = new Predicate();
         $delete = $this->delete()->from('membership_level_connection')->where(['active' => self::MEMBERSHIP_LEVEL_CONNECTION_NOT_ACTIVE, $predicate->isNull('membership_id')]);
         $statement = $this->prepareStatementForSqlObject($delete);
         $statement->execute();
         $this->adapter->getDriver()->getConnection()->commit();
     } catch (Exception $e) {
         $this->adapter->getDriver()->getConnection()->rollback();
         ApplicationErrorLogger::log($e);
         return $e->getMessage();
     }
     return true;
 }
예제 #11
0
파일: Details.php 프로젝트: arbi/MyCode
 public function getTranslationListForSearch($filterParams = array())
 {
     $where = new Where();
     if ((int) $filterParams["id_translation"] > 0) {
         $where->equalTo($this->getTable() . '.id', $filterParams["id_translation"]);
     } else {
         if ($filterParams["srch_txt"] != '') {
             $pred = new Predicate();
             $pred->like($this->getTable() . '.name', '%' . strip_tags(trim($filterParams["srch_txt"])) . '%')->or->like($this->getTable() . '.information_text_html_clean', '%' . strip_tags(trim($filterParams["srch_txt"])) . '%');
             $where->addPredicate($pred);
         }
     }
     $columns = ['id' => 'id', 'tx_2' => 'information_text', 'name' => 'name', 'name_en' => 'name'];
     $sortColumns = ['id', 'information_text', 'name_en'];
     $result = $this->fetchAll(function (Select $select) use($columns, $sortColumns, $where) {
         $select->columns($columns);
         $select->join(DbTables::TBL_COUNTRIES, $this->getTable() . '.id = ' . DbTables::TBL_COUNTRIES . '.detail_id', array('country' => 'id'), 'left');
         $select->join(DbTables::TBL_PROVINCES, $this->getTable() . '.id = ' . DbTables::TBL_PROVINCES . '.detail_id', array('provinces' => 'id'), 'left');
         $select->join(DbTables::TBL_CITIES, $this->getTable() . '.id = ' . DbTables::TBL_CITIES . '.detail_id', array('city' => 'id'), 'left');
         $select->join(DbTables::TBL_POI, $this->getTable() . '.id = ' . DbTables::TBL_POI . '.detail_id', array('poi' => 'id'), 'left');
         if ($where !== null) {
             $select->where($where);
         }
         $select->group($this->getTable() . '.id');
     });
     $count = $this->getCount($where);
     return ['result' => $result, 'count' => $count];
 }
<?php

require_once 'vendor/autoload.php';
use Zend\Db\Sql\Predicate\Predicate;
use Zend\Db\Sql\Sql;
$adapter = (require_once 'adapter.php');
/** @var \Zend\Db\Adapter\Adapter $adapter ['sqlite'] */
$sql = new Sql($adapter['sqlite']);
$wherePredicate = new Predicate();
$wherePredicate->equalTo('Email', '*****@*****.**')->equalTo('LastName', 'Setter')->isNotNull('FirstName');
$update = $sql->update();
$update->table('Customer')->set(['FirstName' => 'Matthew', 'LastName' => 'Setter', 'Email' => '*****@*****.**', 'Country' => 'Australia'])->where($wherePredicate);
$statement = $sql->prepareStatementForSqlObject($update);
$results = $statement->execute();
echo SqlFormatter::format($statement->getSql());
 /**
  * Delete node
  *
  * @param integer $leftKey
  * @param integer $rightKey
  * @param array $filter
  * @param boolean $useTransaction
  * @return boolean|string
  */
 public function deleteNode($leftKey, $rightKey, array $filter = [], $useTransaction = true)
 {
     try {
         if ($useTransaction) {
             $this->tableGateway->getAdapter()->getDriver()->getConnection()->beginTransaction();
         }
         $predicate = new Predicate();
         $this->tableGateway->delete([$predicate->greaterThanOrEqualTo($this->left, $leftKey), $predicate->lessThanOrEqualTo($this->right, $rightKey)] + $filter);
         $predicate = new Predicate();
         $this->tableGateway->update([$this->left => new Expression('IF(' . $this->left . ' > ?, ' . $this->left . ' - (? - ? + 1), ' . $this->left . ')', [$leftKey, $rightKey, $leftKey]), $this->right => new Expression($this->right . ' - (? - ? + 1)', [$rightKey, $leftKey])], [$predicate->greaterThan($this->right, $rightKey)] + $filter);
         if ($useTransaction) {
             $this->tableGateway->getAdapter()->getDriver()->getConnection()->commit();
         }
     } catch (Exception $e) {
         if ($useTransaction) {
             $this->tableGateway->getAdapter()->getDriver()->getConnection()->rollback();
         }
         ErrorLogger::log($e);
         return $e->getMessage();
     }
     return true;
 }
    "SELECT c.FirstName, c.LastName, c.Email, c.Country, i.InvoiceId, i.InvoiceDate, i.Total
    FROM Customer c
    INNER JOIN Invoice i ON (i.CustomerId = c.CustomerId)
    WHERE (i.Total BETWEEN :invoiceMin and :invoiceMax)
    AND c.Country = :customerCountry
    LIMIT 20", $queryValues
);*/
$sqliteFullNameExpression = new Expression('c.FirstName || " " || c.LastName');
$whereLastNameExpression = new Expression('lower(LastName) IN (?)', ['stevens', 'brooks', 'harris']);
$sql = new Sql($adapter['sqlite']);
/** @var Zend\Db\Sql\Select $select */
$select = $sql->select();
$select->from(['c' => 'Customer'])->columns(['FirstName', 'LastName', 'Customer Full Name' => $sqliteFullNameExpression, 'Email', 'Country'])->join(['i' => 'Invoice'], 'i.CustomerId = c.CustomerId', ['InvoiceId', 'InvoiceDate', 'Total']);
$predicate = new Predicate();
$predicate->between('i.Total', $queryValues[':invoiceMin'], $queryValues[':invoiceMax'])->equalTo('c.Country', $queryValues[':customerCountry']);
$predicate2 = new Predicate();
$predicate2->between('i.Total', $queryValues[':invoiceMin'], $queryValues[':invoiceMax'])->equalTo('c.Country', 'Chile');
$select->where($predicate);
$select2 = $sql->select();
$select2 = $select2->from(['c' => 'Customer'])->columns(['FirstName', 'LastName', 'Customer Full Name' => $sqliteFullNameExpression, 'Email', 'Country'])->join(['i' => 'Invoice'], 'i.CustomerId = c.CustomerId', ['InvoiceId', 'InvoiceDate', 'Total']);
$select2->where($predicate2);
$select->limit(20)->order(["Total DESC", "LastName ASC"]);
$select2->combine($select);
$statement = $sql->prepareStatementForSqlObject($select2);
echo SqlFormatter::format($statement->getSql());
exit;
/** @var \Zend\Db\Adapter\Driver\Pdo\Result $results */
$results = $statement->execute();
if ($results->count()) {
    foreach ($results as $result) {
        $headers = array_keys($result);
예제 #15
0
파일: Booking.php 프로젝트: arbi/MyCode
 public function getReservationForAccOnDate($apartment_id, $start_date, $end_date)
 {
     return $this->fetchAll(function (Select $select) use($apartment_id, $start_date, $end_date) {
         $select->columns(['date_from', 'date_to']);
         $where = new Where();
         $where->equalTo('apartment_id_assigned', $apartment_id)->equalTo('status', 1);
         $fromPredicate = new Predicate();
         $fromPredicate->greaterThanOrEqualTo('date_from', $start_date)->lessThanOrEqualTo('date_from', $end_date);
         $toPredicate = new Predicate();
         $toPredicate->greaterThanOrEqualTo('date_to', $start_date)->lessThanOrEqualTo('date_to', $end_date);
         $fromToPredicate = new PredicateSet([$fromPredicate, $toPredicate], PredicateSet::COMBINED_BY_OR);
         $where->addPredicate($fromToPredicate);
         $select->where($where);
     });
 }
예제 #16
0
파일: Where.php 프로젝트: bradley-holt/zf2
 /**
  * Get SQL string for statement
  * 
  * @param  null|PlatformInterface $platform If null, defaults to Sql92
  * @return string
  */
 public function getSqlString(PlatformInterface $platform = null)
 {
     $platform = $platform ?: new Sql92();
     $parts = parent::getWhereParts();
     $wherePart = '';
     foreach ($parts as $part) {
         if (is_string($part)) {
             $wherePart .= $part;
         } elseif (is_array($part)) {
             $values = $part[1];
             $types = isset($part[2]) ? $part[2] : array();
             foreach ($values as $index => $value) {
                 if (isset($types[$index]) && $types[$index] == self::TYPE_IDENTIFIER) {
                     $values[$index] = $platform->quoteIdentifierInFragment($value);
                 } elseif (isset($types[$index]) && $types[$index] == self::TYPE_VALUE) {
                     $values[$index] = $platform->quoteValue($value);
                 }
             }
             $wherePart .= vsprintf($part[0], $values);
         }
     }
     return sprintf($this->specification, $wherePart);
 }
<?php

require_once 'vendor/autoload.php';
use Zend\Db\Sql\Predicate\Predicate;
use Zend\Db\Sql\Sql;
$adapter = (require_once 'adapter.php');
/** @var \Zend\Db\Adapter\Adapter $adapter ['sqlite'] */
$sql = new Sql($adapter['sqlite']);
$wherePredicate = new Predicate();
$wherePredicate->equalTo('Email', '*****@*****.**')->equalTo('LastName', 'Setter')->isNotNull('FirstName');
$delete = $sql->delete();
$delete->from('Customer')->where($wherePredicate);
$statement = $sql->prepareStatementForSqlObject($delete);
$results = $statement->execute();
echo SqlFormatter::format($statement->getSql());
예제 #18
0
파일: Charge.php 프로젝트: arbi/MyCode
 /**
  * @param int $reservationId
  *
  * @return \Traversable|array
  */
 public function getFirstAccTaxCharge($reservationId)
 {
     $this->resultSetPrototype->setArrayObjectPrototype(new \DDD\Domain\Booking\ForCancelCharge());
     $result = $this->fetchAll(function (Select $select) use($reservationId) {
         $select->join(['addons' => DbTables::TBL_BOOKING_ADDONS], $this->getTable() . '.addons_type = addons.id', ['addon' => 'name', 'location_tax' => 'location_join', 'cxl_apply' => 'cxl_apply'], Select::JOIN_LEFT);
         $select->where->equalTo($this->getTable() . '.reservation_id', $reservationId);
         $select->where->equalTo($this->table . '.status', ChargeService::CHARGE_STATUS_NORMAL);
         $accOrTaxWhere = new Predicate();
         $accOrTaxWhere->in($this->table . '.addons_type', [BookingAddon::ADDON_TYPE_ACC, BookingAddon::ADDON_TYPE_PARKING, BookingAddon::ADDON_TYPE_CLEANING_FEE, BookingAddon::ADDON_TYPE_DISCOUNT, BookingAddon::ADDON_TYPE_COMPENSATION, BookingAddon::ADDON_TYPE_EXTRA_PERSON]);
         // acc or parking
         $accOrTaxWhere->OR;
         $accOrTaxWhere->notEqualTo('addons.location_join', '');
         // taxes
         $select->where->addPredicate($accOrTaxWhere);
         $select->order('id ASC');
     });
     return $result;
 }
예제 #19
0
    public function testCanNestPredicates()
    {
        $predicate = new Predicate();
        $predicate->isNull('foo.bar')
                  ->nest()
                  ->isNotNull('bar.baz')
                  ->and
                  ->equalTo('baz.bat', 'foo')
                  ->unnest();
        $parts = $predicate->getExpressionData();

        $this->assertEquals(7, count($parts));

        $this->assertContains('%1$s IS NULL', $parts[0]);
        $this->assertContains(array('foo.bar'), $parts[0]);

        $this->assertEquals(' AND ', $parts[1]);

        $this->assertEquals('(', $parts[2]);

        $this->assertContains('%1$s IS NOT NULL', $parts[3]);
        $this->assertContains(array('bar.baz'), $parts[3]);

        $this->assertEquals(' AND ', $parts[4]);

        $this->assertContains('%s = %s', $parts[5]);
        $this->assertContains(array('baz.bat', 'foo'), $parts[5]);
        
        $this->assertEquals(')', $parts[6]);
    }
예제 #20
0
파일: Booking.php 프로젝트: arbi/MyCode
 /**
  * @param array $data
  * @return array
  */
 public function getPayToPartnerReservations(array $data)
 {
     /**
      * @var \DDD\Dao\Booking\Booking $bookingDao
      */
     $bookingDao = $this->getServiceLocator()->get('dao_booking_booking');
     $bookingDao->setEntity(new \DDD\Domain\Booking\BookingTableRow());
     $where = new Where();
     if (isset($data['res_number'])) {
         $where->equalTo(DbTables::TBL_BOOKINGS . '.res_number', $data['res_number']);
     } else {
         $partnerId = $data['partner'];
         $dateFrom = $data['date_from'];
         $dateTo = $data['date_to'];
         $dist = $data['dist'];
         $where->equalTo(DbTables::TBL_BOOKINGS . '.partner_id', $partnerId);
         $where->greaterThanOrEqualTo(DbTables::TBL_BOOKINGS . '.date_to', date('Y-m-d', strtotime($dateFrom)));
         $where->lessThanOrEqualTo(DbTables::TBL_BOOKINGS . '.date_to', date('Y-m-d', strtotime($dateTo)));
         $where->greaterThan(DbTables::TBL_BOOKINGS . '.partner_balance', 0);
         $where->equalTo(DbTables::TBL_BOOKINGS . '.partner_settled', 0);
         $where->equalTo(DbTables::TBL_BOOKINGS . '.payment_settled', 1);
         $where->expression(DbTables::TBL_BOOKINGS . '.apartment_id_assigned not in (?, ?)', [Constants::TEST_APARTMENT_1, Constants::TEST_APARTMENT_2]);
         if (is_array($dist) && count($dist)) {
             $apartmentIdList = [];
             $apartelIdList = [];
             $fiscalList = [];
             foreach ($dist as $distItem) {
                 list($entityType, $entityId) = explode('_', $distItem);
                 // 1 for apartment, 2 for apartel (as a convention)
                 if ($entityType == Distribution::TYPE_APARTMENT) {
                     array_push($apartmentIdList, $entityId);
                 } elseif ($entityType == Distribution::TYPE_APARTEL) {
                     array_push($apartelIdList, $entityId);
                 } else {
                     array_push($fiscalList, $entityId);
                 }
             }
             if (!count($apartmentIdList) && !count($apartelIdList) && !count($fiscalList)) {
                 throw new \RuntimeException('Entity not selected.');
             }
             $wrapperPredicate = new Predicate();
             $checkAddOr = false;
             // apartment
             if (count($apartmentIdList)) {
                 $predicate = new Predicate();
                 $predicate->in(DbTables::TBL_BOOKINGS . '.apartment_id_origin', $apartmentIdList)->lessThan(DbTables::TBL_BOOKINGS . '.apartel_id', 1);
                 $wrapperPredicate->addPredicate($predicate);
                 $checkAddOr = true;
             }
             // apartel
             if (count($apartelIdList)) {
                 if ($checkAddOr) {
                     $wrapperPredicate->or;
                 }
                 $checkAddOr = true;
                 $wrapperPredicate->in(DbTables::TBL_BOOKINGS . '.apartel_id', $apartelIdList);
             }
             // fiscal
             if (count($fiscalList)) {
                 if ($checkAddOr) {
                     $wrapperPredicate->or;
                 }
                 $wrapperPredicate->in('fiscal.id', $fiscalList);
             }
             $where->addPredicate($wrapperPredicate);
         }
     }
     $reservations = $bookingDao->getPayToPartnerReservationsByFilter($where);
     $reservationList = [];
     if ($reservations->count()) {
         foreach ($reservations as $reservation) {
             array_push($reservationList, ['id' => $reservation->getReservationId(), 'res_number' => $reservation->getReservationNumber(), 'status' => $reservation->getStatus(), 'apartel_id' => $reservation->getApartelId(), 'booking_date' => $reservation->getBookingDate(), 'departure_date' => $reservation->getDepartureDate(), 'apartment_id' => $reservation->getApartmentId(), 'apartment_name' => $reservation->getApartmentName(), 'partner_id' => $reservation->getPartnerId(), 'partner_name' => $reservation->getPartnerName(), 'guest_balance' => $reservation->getGuestBalance(), 'partner_balance' => $reservation->getPartnerBalance(), 'symbol' => $reservation->getSymbol(), 'currency_id' => $reservation->getCurrencyId()]);
         }
     }
     return $reservationList;
 }
<?php

require_once 'vendor/autoload.php';
require_once 'output-results.php';
use Zend\Db\Sql\Predicate\Expression;
use Zend\Db\Sql\Predicate\Predicate;
use Zend\Db\Sql\Sql;
$adapter = (require_once 'adapter.php');
$queryValues = [':invoiceMin' => 1, ':invoiceMax' => 20, ':countryOne' => 'USA', ':countryTwo' => 'Germany'];
/** @var \Zend\Db\Adapter\Driver\StatementInterface $statement */
$sql = new Sql($adapter['sqlite']);
$countryPredicate = new Predicate();
$countryPredicate->equalTo('c.Country', 'USA')->or->equalTo('c.Country', 'Germany');
$invoicePredicate = new Predicate();
$invoicePredicate->between('i.Total', $queryValues[':invoiceMin'], $queryValues[':invoiceMax']);
$predicate = new Predicate();
//$predicate->addPredicate($invoicePredicate)->addPredicate($countryPredicate);
$predicate->addPredicates([$invoicePredicate]);
$predicate->andPredicate($countryPredicate);
$sqliteFullNameExpression = new Expression('c.FirstName || " " || c.LastName');
$whereLastNameExpression = new Expression('LastName IN (?)', [['Stevens', 'Brooks', 'Harris']]);
/** @var Zend\Db\Sql\Select $select */
$select = $sql->select();
$select->from(['c' => 'Customer'])->columns(['FirstName', 'LastName', "Full Name" => $sqliteFullNameExpression, 'Email', 'Country'])->join(['i' => 'Invoice'], 'i.CustomerId = c.CustomerId', ['InvoiceId', 'InvoiceDate', 'Total'])->where($predicate)->where($whereLastNameExpression)->order(['c.Country DESC', 'i.Total DESC', 'c.LastName'])->limit(30)->offset(1);
/** @var \Zend\Db\Adapter\Driver\Pdo\Result $results */
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();
renderResults($results, $statement);
예제 #22
0
파일: Kendo.php 프로젝트: moln/gzfextra
 /**
  * @param array $fieldMap
  *
  * @return Predicate
  */
 public function filter($fieldMap = array())
 {
     if (empty($this->filter['filters'])) {
         return array();
     }
     $where = new Predicate();
     $where->addPredicate($this->parseFilters($this->filter, $fieldMap));
     return $where;
 }
예제 #23
0
 /**
  * @testdox Unit test: Test literal() is chainable, returns proper values, and is backwards compatible with 2.0.*
  */
 public function testLiteral()
 {
     $predicate = new Predicate();
     // is chainable
     $this->assertSame($predicate, $predicate->literal('foo = bar'));
     // with parameter
     $this->assertEquals(array(array('foo = bar', array(), array())), $predicate->getExpressionData());
     // test literal() is backwards-compatible, and works with with parameters
     $predicate = new Predicate();
     $predicate->expression('foo = ?', 'bar');
     // with parameter
     $this->assertEquals(array(array('foo = %s', array('bar'), array(Expression::TYPE_VALUE))), $predicate->getExpressionData());
     // test literal() is backwards-compatible, and works with with parameters, even 0 which tests as false
     $predicate = new Predicate();
     $predicate->expression('foo = ?', 0);
     // with parameter
     $this->assertEquals(array(array('foo = %s', array(0), array(Expression::TYPE_VALUE))), $predicate->getExpressionData());
 }
예제 #24
0
파일: Where.php 프로젝트: Andyyang1981/pi
 /**
  * Create predicate object
  *
  * @param  string|array $predicate
  * @param  string $combination
  *
  * @return Predicate\Predicate
  */
 public function create($predicate, $combination = null)
 {
     $combination = $combination ? strtoupper($combination) : null;
     //$predicates = $this->canonize($predicate);
     //$result = new Predicate\Predicate($predicates, $combination);
     $result = new Predicate\Predicate();
     $result->addPredicates($predicate, $combination);
     return $result;
 }
예제 #25
0
파일: Team.php 프로젝트: arbi/MyCode
 /**
  * Returns true if user is this team's director or one of team's managers
  * @param $teamId
  * @param $userId
  * @return bool
  */
 public function isTeamManagerOrDirector($teamId, $userId)
 {
     $where = new Where();
     $nestedWhere = new Predicate();
     $where->equalTo('team_id', $teamId)->equalTo('user_id', $userId);
     $nestedWhere->equalTo('type', self::STAFF_MANAGER)->or->equalTo('type', self::STAFF_DIRECTOR);
     $where->addPredicate($nestedWhere);
     $result = $this->getTeamStaffDao()->fetchOne($where, ['id']);
     if (!$result) {
         return false;
     }
     return true;
 }
예제 #26
0
 /**
  * Prepare filter parameters
  *
  * @param   array|string $binds A bind variable array
  * @param   \Zend\Db\Sql\Predicate\PredicateInterface $predicate
  * @return  Predicate
  */
 public function prepareFilterParams($binds = [], $predicate = null)
 {
     if (null === $predicate) {
         $predicate = new Predicate();
     }
     $fields = $this->getRepository()->fields;
     if (is_array($binds)) {
         foreach ($binds as $v) {
             if (!is_array($v) || empty($v['predicate'])) {
                 continue;
             }
             if (isset($v['nesting']) && (Enums\PredicateType::NEST === $v['nesting'] || Enums\PredicateType::UNNEST === $v['nesting'])) {
                 /* @see \Zend\Db\Sql\Predicate\Predicate::nest
                  * @see \Zend\Db\Sql\Predicate\Predicate::unnest */
                 $predicate = $predicate->{$v['nesting']}();
             }
             if (isset($v['combine']) && (Predicate::OP_OR === $v['combine'] || strtolower(Predicate::OP_OR) === $v['combine'])) {
                 $predicate->or;
             }
             switch ($v['predicate']) {
                 case Enums\PredicateType::BETWEEN:
                 case Enums\PredicateType::NOT_BETWEEN:
                     if (empty($v['identifier']) || !isset($v['minValue']) || !isset($v['maxValue']) || !isset($fields[$v['identifier']])) {
                         continue;
                     }
                     /* @see \Zend\Db\Sql\Predicate\Predicate::between
                      * @see \Zend\Db\Sql\Predicate\Predicate::notBetween */
                     $predicate->{$v['predicate']}($v['identifier'], "'" . $this->filter($v['minValue'], true) . "'", "'" . $this->filter($v['maxValue'], 86399) . "'");
                     break;
                 case Enums\PredicateType::EQUAL_TO:
                 case Enums\PredicateType::NOT_EQUAL_TO:
                 case Enums\PredicateType::GREATER_THAN:
                 case Enums\PredicateType::LESS_THAN:
                 case Enums\PredicateType::GREATER_THAN_OR_EQUAL_TO:
                 case Enums\PredicateType::LESS_THAN_OR_EQUAL_TO:
                 case Enums\PredicateType::EQ:
                 case Enums\PredicateType::NEQ:
                 case Enums\PredicateType::GT:
                 case Enums\PredicateType::LT:
                 case Enums\PredicateType::GTE:
                 case Enums\PredicateType::LTE:
                     if (!isset($v['left']) || !isset($v['right'])) {
                         continue;
                     }
                     if (empty($v['leftType']) || Predicate::TYPE_VALUE !== $v['leftType']) {
                         $v['leftType'] = Predicate::TYPE_IDENTIFIER;
                     }
                     if (empty($v['rightType']) || Predicate::TYPE_IDENTIFIER !== $v['rightType']) {
                         $v['rightType'] = Predicate::TYPE_VALUE;
                     }
                     if ($v['leftType'] == $v['rightType']) {
                         $v['leftType'] = Predicate::TYPE_IDENTIFIER;
                         $v['rightType'] = Predicate::TYPE_VALUE;
                     }
                     if (Predicate::TYPE_IDENTIFIER !== $v['leftType']) {
                         $v['left'] = "'" . $this->filter($v['left'], true) . "'";
                     } elseif (!isset($fields[$v['left']])) {
                         continue;
                     }
                     if (Predicate::TYPE_IDENTIFIER !== $v['rightType']) {
                         $v['right'] = "'" . $this->filter($v['right'], true) . "'";
                     } elseif (!isset($fields[$v['right']])) {
                         continue;
                     }
                     /* @see \Zend\Db\Sql\Predicate\Predicate::equalTo
                      * @see \Zend\Db\Sql\Predicate\Predicate::notEqualTo
                      * @see \Zend\Db\Sql\Predicate\Predicate::lessThan
                      * @see \Zend\Db\Sql\Predicate\Predicate::greaterThan
                      * @see \Zend\Db\Sql\Predicate\Predicate::lessThanOrEqualTo
                      * @see \Zend\Db\Sql\Predicate\Predicate::greaterThanOrEqualTo */
                     $predicate->{$v['predicate']}($v['left'], $v['right'], $v['leftType'], $v['rightType']);
                     break;
                 case Enums\PredicateType::EXPR:
                 case Enums\PredicateType::EXPRESSION:
                     if (empty($v['expression'])) {
                         continue;
                     }
                     if (isset($v['parameters'])) {
                         if (!is_array($v['parameters'])) {
                             $v['parameters'] = [$v['parameters']];
                         }
                         foreach ($v['parameters'] as $key => &$value) {
                             if (!is_scalar($value)) {
                                 unset($v['parameters'][$key]);
                             } elseif (!isset($fields[$value])) {
                                 $value = "'" . str_replace('%', '%%', $this->filter($value)) . "'";
                             }
                         }
                         unset($value);
                         $v['parameters'] = array_values($v['parameters']);
                     }
                     $v['expression'] = str_replace(['&lt;', '&gt;'], ['<', '>'], $this->filter($v['expression']));
                     $predicate->expression($v['expression'], @$v['parameters']);
                     break;
                 case Enums\PredicateType::IN:
                 case Enums\PredicateType::NIN:
                 case Enums\PredicateType::NOT_IN:
                     if (empty($v['identifier']) || empty($v['valueSet']) || !is_array($v['valueSet'])) {
                         continue;
                     }
                     if (is_array($v['identifier'])) {
                         foreach ($v['identifier'] as $key => $value) {
                             if (!isset($fields[$value])) {
                                 unset($v['identifier'][$key]);
                             }
                         }
                         if (empty($v['identifier'])) {
                             continue;
                         }
                         $v['identifier'] = array_values($v['identifier']);
                     } elseif (!isset($fields[$v['identifier']])) {
                         continue;
                     }
                     foreach ($v['valueSet'] as &$value) {
                         $value = "'" . $this->filter($value) . "'";
                     }
                     unset($value);
                     /* @see \Zend\Db\Sql\Predicate\Predicate::in
                      * @see \Zend\Db\Sql\Predicate\Predicate::notIn */
                     $predicate->{$v['predicate']}($v['identifier'], $v['valueSet']);
                     break;
                 case Enums\PredicateType::IS_NOT_NULL:
                 case Enums\PredicateType::IS_NULL:
                     if (empty($v['identifier']) || !isset($fields[$v['identifier']])) {
                         continue;
                     }
                     /* @see \Zend\Db\Sql\Predicate\Predicate::isNull
                      * @see \Zend\Db\Sql\Predicate\Predicate::isNotNull */
                     $predicate->{$v['predicate']}($v['identifier']);
                     break;
                 case Enums\PredicateType::LIKE:
                 case Enums\PredicateType::NOT_LIKE:
                     if (empty($v['identifier']) || empty($v[$v['predicate']]) || !isset($fields[$v['identifier']]) || !is_string($v[$v['predicate']])) {
                         continue;
                     }
                     $v[$v['predicate']] = "'%" . str_replace('%', '%%', $this->filter($v[$v['predicate']])) . "%'";
                     /* @see \Zend\Db\Sql\Predicate\Predicate::like
                      * @see \Zend\Db\Sql\Predicate\Predicate::notLike */
                     $predicate->{$v['predicate']}($v['identifier'], $v[$v['predicate']]);
                     break;
                 case Predicate::TYPE_LITERAL:
                     if (empty($v['literal']) || !is_string($v['literal'])) {
                         continue;
                     }
                     $predicate->literal(str_replace(['&lt;', '&gt;', '&#39;', '&#039;'], ['<', '>', "'", "'"], $this->filter($v['literal'])));
                     break;
                 default:
             }
         }
     } elseif (is_string($binds)) {
         $predicateSet = new Predicate();
         $searchable = $this->getConfig()->get('app.minSearchChars') <= strlen($binds);
         $binds = $this->filter($binds);
         $equalValue = "'" . $binds . "'";
         $likeValue = "'%" . str_replace('%', '%%', $binds) . "%'";
         $count = 0;
         foreach ($fields as $k => $v) {
             if ((Types\Type::STRING_TYPE === $v['type'] || Types\Type::TEXT_TYPE === $v['type']) && ($searchable || ($isChar = isset($v['options']) && isset($v['options']['fixed'])))) {
                 $predicateSet->or;
                 isset($isChar) && $isChar ? $predicateSet->equalTo($k, $equalValue) : $predicateSet->like($k, $likeValue);
                 if (CHAOS_MAX_QUERY <= ++$count) {
                     break;
                 }
             }
         }
         if (0 !== count($predicateSet)) {
             $predicate->predicate($predicateSet);
         }
     }
     return $predicate;
 }