예제 #1
0
 public function deleteRemovedRows($owner, array $arrId)
 {
     $where = new Where();
     $where->equalTo('IR_InvoiceId', $owner);
     if (!empty($arrId)) {
         $where->notIn('IR_Id', $arrId);
     }
     $this->delete($where);
 }
예제 #2
0
 public function deleteRemovedPostAddresses($owner, array $arrId)
 {
     $where = new Where();
     $where->equalTo('PA_Owner', $owner);
     if (!empty($arrId)) {
         $where->notIn('PA_Id', $arrId);
     }
     $this->delete($where);
 }
예제 #3
0
 public function deleteRemovedPhoneNumbers($owner, array $arrId)
 {
     $where = new Where();
     $where->equalTo('PN_Owner', $owner);
     if (!empty($arrId)) {
         $where->notIn('PN_Id', $arrId);
     }
     $this->delete($where);
 }
예제 #4
0
 public function delNotExistCategory($names = array(), $storeID)
 {
     if (empty($names)) {
         return false;
     }
     $where = new Where();
     $where->notIn('storeCategoryName', $names);
     $where->equalTo('storeID', $storeID);
     return $this->delete($where);
 }
 /**
  * @param array $currency
  * @return \Zend\Db\ResultSet\ResultSet
  */
 public function getNewOpeningData(array $currency)
 {
     $tableGateway = new TableGateway('vw_account_voucher', $this->adapter);
     $results = $tableGateway->select(function (Select $select) use($currency) {
         $where = new Where();
         if (empty($currency)) {
             $where->equalTo('status', 'A');
         } else {
             $where->notIn('currency', $currency)->AND->equalTo('status', 'A');
         }
         $select->columns(array('type', 'currency', 'amount' => new Expression('SUM(amount)')))->where($where)->group(array('type', 'currency'))->order('currency asc');
     });
     return $results;
 }
예제 #6
0
파일: General.php 프로젝트: arbi/MyCode
 /**
  * @param $countryId
  * @param int $selectedId
  * @return \Zend\Db\ResultSet\ResultSet
  */
 public function getApartmentsForCountryForSelect($countryId = false, $selectedId = 0)
 {
     $prototype = $this->resultSetPrototype->getArrayObjectPrototype();
     $this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
     $result = $this->fetchAll(function (Select $select) use($countryId, $selectedId) {
         $where = new Where();
         $nestedWhere = new Where();
         $nestedWhere->notIn('id', [Constants::TEST_APARTMENT_1, Constants::TEST_APARTMENT_2])->AND->notEqualTo('status', AccommodationService::APARTMENT_STATUS_DISABLED);
         if ($selectedId) {
             $where->NEST->equalTo($this->getTable() . '.id', $selectedId)->orPredicate($nestedWhere)->UNNEST;
         } else {
             $where->addPredicate($nestedWhere);
         }
         $select->columns(['id', 'name']);
         if ($countryId) {
             $where->equalTo('country_id', $countryId);
         }
         $select->where($where);
     });
     $this->resultSetPrototype->setArrayObjectPrototype($prototype);
     return $result;
 }
 public function deleteItemsNotInArray($id, $itemsIds)
 {
     $where = new Where();
     $where->equalTo('order_id', $id);
     $where->notIn('id', $itemsIds);
     $this->tableGatewayItems->delete($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;
 }
예제 #9
0
파일: Task.php 프로젝트: arbi/MyCode
 /**
  * @param int $resId
  * @param int $start
  * @param int $length
  * @param array $order
  * @param array $search
  * @param int $status
  * @return \DDD\Domain\Task\Task[]
  */
 public function getTasksOnReservationForDatatable($resId, $start, $length, $order, $search, $status)
 {
     $result = $this->fetchAll(function (Select $select) use($resId, $start, $length, $order, $search, $status) {
         $like = $search['value'];
         $where = new Where();
         if ($status == 1) {
             $where->notIn('task_status', [TaskService::STATUS_CANCEL, TaskService::STATUS_VERIFIED, TaskService::STATUS_DONE]);
         } else {
             if ($status == 2) {
                 $where->in('task_status', [TaskService::STATUS_CANCEL, TaskService::STATUS_VERIFIED, TaskService::STATUS_DONE]);
             }
         }
         $where->equalTo('reservations.id', $resId);
         $columns = ['priority', 'title', 'task_status', 'start_date', 'end_date', 'id', 'task_type'];
         $orderColumns = ['priority', 'task_status', 'start_date', 'end_date', 'title', 'task_type', 'creator_name', 'responsible_name'];
         $nestedWhere = new Where();
         $nestedWhere->like('title', '%' . $like . '%')->or->like($this->getTable() . '.start_date', '%' . $like . '%')->or->like($this->getTable() . '.end_date', '%' . $like . '%')->or->like('creator_users.firstname', '%' . $like . '%')->or->like('creator_users.lastname', '%' . $like . '%');
         $where->addPredicate($nestedWhere);
         $orderList = [];
         foreach ($order as $entity) {
             $orderList[] = $orderColumns[$entity['column']] . ' ' . $entity['dir'];
         }
         $select->columns($columns)->join(['reservations' => DbTables::TBL_BOOKINGS], $this->getTable() . '.res_id = reservations.id', [], Select::JOIN_INNER)->join(['task_types' => DbTables::TBL_TASK_TYPE], $this->getTable() . '.task_type = task_types.id', ['task_type_name' => 'name'], Select::JOIN_INNER)->join(['creators' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = creators.task_id AND creators.type = ' . TaskService::STAFF_CREATOR), [], Select::JOIN_INNER)->join(['creator_users' => DbTables::TBL_BACKOFFICE_USERS], 'creators.user_id = creator_users.id', ['creator_id' => 'id', 'creator_name' => new Expression('CONCAT(creator_users.firstname, " ", creator_users.lastname)')], Select::JOIN_INNER)->join(['responsibles' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = responsibles.task_id AND responsibles.type = ' . TaskService::STAFF_RESPONSIBLE), [], Select::JOIN_LEFT)->join(['responsible_users' => DbTables::TBL_BACKOFFICE_USERS], 'responsibles.user_id = responsible_users.id', ['responsible_id' => 'id', 'responsible_name' => new Expression('CONCAT(responsible_users.firstname, " ", responsible_users.lastname)')], Select::JOIN_LEFT)->where($where)->order($orderList)->group($this->getTable() . '.id')->offset((int) $start)->limit((int) $length)->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
     });
     $statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
     $result2 = $statement->execute();
     $row = $result2->current();
     $total = $row['total'];
     return ['result' => $result, 'total' => $total];
 }