作者:arb
项目:MyCod
public function getTranslationListForSearch($filterParams = array())
{
$columns = array('id' => 'id', 'type' => 'entity_type', 'content' => 'en', 'entity_name' => new Expression("\n CASE\n WHEN (apartel.id IS NOT NULL) THEN apartel_group.name\n WHEN (building.id IS NOT NULL) THEN building.name\n WHEN (apartment.id IS NOT NULL) THEN apartment.name\n WHEN (office.id IS NOT NULL) THEN office.name\n WHEN (parking.id IS NOT NULL) THEN parking.name\n WHEN (building_section.id IS NOT NULL) THEN CONCAT(building_for_section.name, ' ', building_section.name)\n END\n "));
$sortColumns = ['id'];
$result = $this->fetchAll(function (Select $select) use($columns, $sortColumns, $filterParams) {
$select->columns($columns);
$select->join(['apartment' => DbTables::TBL_APARTMENTS], new Expression($this->getTable() . '.entity_id = apartment.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$APARTMENT_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT)->join(['apartel' => DbTables::TBL_APARTELS], new Expression($this->getTable() . '.entity_id = apartel.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$APARTEL_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT)->join(['apartel_group' => DbTables::TBL_APARTMENT_GROUPS], 'apartel.apartment_group_id = apartel_group.id', [], $select::JOIN_LEFT)->join(['building' => DbTables::TBL_APARTMENT_GROUPS], new Expression($this->getTable() . '.entity_id = building.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$BUILDING_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT)->join(['building_section' => DbTables::TBL_BUILDING_SECTIONS], new Expression($this->getTable() . '.entity_id = building_section.id AND ' . $this->getTable() . '.entity_type =' . Translation::PRODUCT_TEXTLINE_TYPE_BUILDING_SECTION_APARTMENT_ENTRY), [], $select::JOIN_LEFT)->join(['building_for_section' => DbTables::TBL_APARTMENT_GROUPS], 'building_section.building_id = building_for_section.id', [], $select::JOIN_LEFT)->join(['office' => DbTables::TBL_OFFICES], new Expression($this->getTable() . '.entity_id = office.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$OFFICE_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT)->join(['parking' => DbTables::TBL_PARKING_LOTS], new Expression($this->getTable() . '.entity_id = parking.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$PARKING_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT);
$where = new Where();
$textSearch = '%' . strip_tags(trim($filterParams["srch_txt"])) . '%';
if ((int) $filterParams["id_translation"] > 0) {
$where->equalTo($this->getTable() . '.entity_id', $filterParams["id_translation"]);
} else {
if ($filterParams["srch_txt"] != '') {
$where->NEST->like('apartment.name', $textSearch)->or->NEST->like('apartel_group.name', $textSearch)->or->NEST->like('building.name', $textSearch)->or->NEST->like('office.name', $textSearch)->or->like('parking.name', $textSearch)->or->like($this->getTable() . '.en_html_clean', $textSearch)->UNNEST;
}
}
if ((int) $filterParams['product_type']) {
$where->equalTo($this->getTable() . '.type', (int) $filterParams['product_type']);
}
$select->where($where);
$select->group($this->getTable() . '.id');
$select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
});
$statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
$resultCount = $statement->execute();
$row = $resultCount->current();
$total = $row['total'];
return ['result' => $result, 'total' => $total];
}
作者:shitikovkiril
项目:zend-shop.co
public function removeSerie($userId, $serieId)
{
$where = new Where();
$where->equalTo('user_id', $userId);
$where->equalTo('sid', $serieId);
return parent::delete($where, $this->tableName);
}
作者:arb
项目:MyCod
/**
* @param int $apartmentId
* @param string $startDate
* @param string $endDate
*
* @return array
*/
public function getMonthlyCost($apartmentId, $startDate, $endDate)
{
$expenses = $this->fetchAll(function (Select $select) use($apartmentId, $startDate, $endDate) {
$nestedWhere = new Where();
$nestedWhere->lessThanOrEqualTo('item.period_from', $endDate)->or->greaterThanOrEqualTo('item.period_to', $startDate);
$select->columns(['amount' => 'amount'])->join(['item' => DbTables::TBL_EXPENSE_ITEM], $this->getTable() . '.expense_item_id = item.id', ['period_from', 'period_to', 'date_created', 'is_refund'], Select::JOIN_LEFT)->join(['expense' => DbTables::TBL_EXPENSES], 'item.expense_id = expense.id', [], Select::JOIN_LEFT)->where->equalTo('expense.status', Ticket::STATUS_GRANTED)->equalTo($this->getTable() . '.cost_center_id', $apartmentId)->equalTo($this->getTable() . '.cost_center_type', ExpenseCosts::TYPE_APARTMENT)->addPredicate($nestedWhere);
});
$result = [];
foreach ($expenses as $expense) {
$monthStart = date("n", strtotime($expense['period_from']));
$yearStart = date("Y", strtotime($expense['period_from']));
$monthEnd = date("n", strtotime($expense['period_to']));
$yearEnd = date("Y", strtotime($expense['period_to']));
$startInMonths = $monthStart + $yearStart * 12;
$endInMonths = $monthEnd + $yearEnd * 12;
$sign = $expense['is_refund'] ? -1 : 1;
$expensePerMonth = $sign * $expense['amount'] / ($endInMonths - $startInMonths + 1);
for ($iterator = $startInMonths; $iterator <= $endInMonths; $iterator++) {
$tempMonth = $iterator % 12;
$tempMonth = $tempMonth ? $tempMonth : 12;
$currentYear = ($iterator - $tempMonth) / 12;
$currentMonth = date('M', mktime(0, 0, 0, $tempMonth, 1));
$key = $currentMonth . '_' . $currentYear;
if (isset($result[$key])) {
$result[$key] += $expensePerMonth;
} else {
$result[$key] = $expensePerMonth;
}
}
}
return $result;
}
作者:arb
项目:MyCod
public function getBlackList($data)
{
$result = $this->fetchAll(function (Select $select) use($data) {
$select->join(['reservation' => DbTables::TBL_BOOKINGS], $this->getTable() . '.reservation_id = reservation.id', ['res_number'], $select::JOIN_LEFT);
$where = new Where();
$where->equalTo('hash', '');
if (!empty($data['email'])) {
$where->or->equalTo('hash', $data['email']);
}
if (!empty($data['fullNamePhone'])) {
$where->or->equalTo('hash', $data['fullNamePhone']);
}
if (!empty($data['fullNameAddress'])) {
$where->or->equalTo('hash', $data['fullNameAddress']);
}
if (!empty($data['fullName'])) {
$where->or->equalTo('hash', $data['fullName']);
}
if (!empty($data['phone'])) {
$where->or->equalTo('hash', $data['phone']);
}
$select->where($where);
});
return $result;
}
作者:Bertie170
项目:ep3-b
/**
* 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();
}
}
作者:hyrmedi
项目:directu
public function fetchFeed($params = null)
{
$sql = new Sql($this->adapter);
$select = $sql->select()->from($this->table);
$params['orderColumn'] = 'id';
$params['orderDirection'] = 'DESC';
$tableSchemaArray = TableSchema::getSchemaArray($this->table);
$hasActiveColumn = $this->schemaHasActiveColumn($tableSchemaArray);
$params = $this->applyDefaultEntriesSelectParams($params);
$columns = array('id', 'identifier', 'action', 'table_name', 'row_id', 'user', 'datetime', 'type', 'data');
$select->columns($columns);
// ->order('id DESC');
$select->where->nest->isNull('parent_id')->OR->equalTo('type', 'FILES')->unnest;
$select = $this->applyParamsToTableEntriesSelect($params, $select, $tableSchemaArray, $hasActiveColumn);
//die($this->dumpSql($select));
$rowset = $this->selectWith($select);
$rowset = $rowset->toArray();
foreach ($rowset as &$row) {
$row['datetime'] .= ' UTC';
}
$countTotalWhere = new Where();
$countTotalWhere->isNull('parent_id')->OR->equalTo('type', 'FILES');
$activityTotal = $this->countTotal($countTotalWhere);
return array('total' => $activityTotal, 'rows' => $rowset);
}
作者:arb
项目:MyCod
public function fillInventory($userId, $startDate, $officeId)
{
/** @var \DDD\Dao\User\Schedule\Inventory $inventoryDao */
$inventoryDao = $this->getServiceLocator()->get('dao_user_schedule_inventory');
$deleteWhere = new Where();
$deleteWhere->equalTo('user_id', $userId)->greaterThanOrEqualTo('date', date('Y-m-d', strtotime($startDate)))->notEqualTo('is_changed', 1);
$inventoryDao->delete($deleteWhere);
$scheduleData = $this->getUserSchedule($userId);
$scheduleArr = [];
if ($scheduleData) {
foreach ($scheduleData as $daySchedule) {
$scheduleArr[$daySchedule->getDay()] = $daySchedule;
}
}
// Fill up to 3 months ahead
$endDate = date('Y-m-d', strtotime('+92 days'));
$dateIterator = date('Y-m-d', strtotime($startDate));
$insertArray = [];
while ($dateIterator < $endDate) {
/** @var \DDD\Domain\User\Schedule\Schedule $daySchedule */
if (count($scheduleArr)) {
for ($scheduleIndex = 1; $scheduleIndex <= count($scheduleArr); $scheduleIndex++) {
$daySchedule = $scheduleArr[$scheduleIndex];
array_push($insertArray, ['user_id' => $userId, 'office_id' => $officeId, 'date' => $dateIterator, 'availability' => $daySchedule->isActive(), 'time_from1' => $daySchedule->isActive() ? $daySchedule->getTimeFrom1() : '', 'time_to1' => $daySchedule->isActive() ? $daySchedule->getTimeTo1() : '', 'time_from2' => $daySchedule->isActive() ? $daySchedule->getTimeFrom2() : '', 'time_to2' => $daySchedule->isActive() ? $daySchedule->getTimeTo2() : '']);
$dateIterator = date('Y-m-d', strtotime($dateIterator . '+1 days'));
}
// The case when user has no schedule scheme. Fill the inventory with 0 availability
} else {
array_push($insertArray, ['user_id' => $userId, 'office_id' => $officeId, 'date' => $dateIterator, 'availability' => 0, 'time_from1' => '', 'time_to1' => '', 'time_from2' => '', 'time_to2' => '']);
$dateIterator = date('Y-m-d', strtotime($dateIterator . '+1 days'));
}
}
$inventoryDao->multiInsert($insertArray, true);
}
作者:arb
项目:MyCod
/**
* @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;
}
作者:gstearmi
项目:EshopVegeTabl
public function search($str)
{
$select = new Select('video');
$spec = new Where();
$spec->like('title', '% ' . $str . ' %');
$select->where($spec);
$select->where->OR->like('title', $str . ' %');
$select->where->OR->like('title', '% ' . $str);
//$select->where(array('title'=>$str));
//$select->where(array('catelog'=>$title,'description'=>$description));
$select->order('id ASC');
/*
// create a new result set based on the Album entity
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Search());
// create a new pagination adapter object
$paginatorAdapter = new DbSelect(
// our configured select object
$select,
// the adapter to run it against
$this->tableGateway->getAdapter(),
// the result set to hydrate
$resultSetPrototype
);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
*/
return $this->tableGateway->selectWith($select);
}
作者:YounessTaye
项目:directu
public function fetchFeed($params = null)
{
$sql = new Sql($this->adapter);
$select = $sql->select()->from($this->table);
$params['orderColumn'] = 'id';
$params['orderDirection'] = 'DESC';
$tableSchemaArray = TableSchema::getSchemaArray($this->table);
$hasActiveColumn = $this->schemaHasActiveColumn($tableSchemaArray);
$params = $this->applyDefaultEntriesSelectParams($params);
$columns = ['id', 'identifier', 'action', 'table_name', 'row_id', 'user', 'datetime', 'type', 'data'];
$select->columns($columns);
// ->order('id DESC');
$select->where->nest->isNull('parent_id')->OR->equalTo('type', 'FILES')->unnest;
$select = $this->applyParamsToTableEntriesSelect($params, $select, $tableSchemaArray, $hasActiveColumn);
//die($this->dumpSql($select));
$rowset = $this->selectWith($select);
$rowset = $rowset->toArray();
$rowset = $this->convertDates($rowset, $tableSchemaArray);
// @TODO: Returns date in ISO 8601 Ex: 2016-06-06T17:18:20Z
// see: https://en.wikipedia.org/wiki/ISO_8601
// foreach ($rowset as &$row) {
// $row['datetime'] .= ' UTC';
// }
$countTotalWhere = new Where();
$countTotalWhere->isNull('parent_id')->OR->equalTo('type', 'FILES');
$activityTotal = $this->countTotal($countTotalWhere);
return ['total' => $activityTotal, 'rows' => $rowset];
}
作者:biialabor
项目:budocu.co
/**
*
* @param string $search
* @return mixed
*/
public function getDocumento($search = '')
{
$select = $this->sql->select()->from(array('t1' => 'bdc_documento'))->join(array('t2' => 'bdc_tipo_documento'), 't1.tipd_id=t2.tipd_id', array('*'));
$where = new Where();
$where->equalTo('doc_numero', $search);
$select->where($where);
return $this->fetchRow($select);
}
作者:nclundste
项目:SpeckContac
public function findByContactId($id)
{
$sql = new Select();
$sql->from(array('cc' => 'contact_companies'))->join(array('c' => 'contact_company'), 'c.company_id = cc.company_id');
$where = new Where();
$where->equalTo('cc.contact_id', $id);
return $this->select($sql->where($where));
}
作者:arb
项目:MyCod
public function getSkuIdByName($sku)
{
return $this->fetchOne(function (Select $select) use($sku) {
$where = new Where();
$where->equalTo('name', $sku);
$select->columns(['id', 'asset_category_id'])->where($where);
});
}
作者:artpopls
项目:learningZf
public function getChatMesByGetter($getter)
{
$where = new Where();
$where->equalTo('getter', $getter);
$where->equalTo('isread', 1);
$rowset = $this->tableGateway->select($where);
return iterator_to_array($rowset);
}
作者:vrkansagar
项目:PhlyPee
public function fetchUserTimeline($user)
{
$select = $this->getSql()->select();
$where = new Where();
$where->equalTo('username', $user);
$select->where($where)->order('timestamp DESC');
return new Paginator(new DbSelect($select, $this->adapter, $this->resultSetPrototype));
}
作者:arb
项目:MyCod
/**
* @param $transactionId
* @return array|\ArrayObject|null
*/
public function getTransferByTransaction($transactionId)
{
return $this->fetchOne(function (Select $select) use($transactionId) {
$where = new Where();
$where->equalTo('money_transaction_id_1', $transactionId)->or->equalTo('money_transaction_id_2', $transactionId);
$select->where($where)->columns(['id', 'money_transaction_id_1', 'money_transaction_id_2', 'amount_from', 'amount_to'])->join(['ta1' => DbTables::TBL_TRANSACTION_ACCOUNTS], new Expression($this->getTable() . '.account_id_from = ta1.id AND ta1.type = ' . Account::TYPE_MONEY_ACCOUNT), [], Select::JOIN_LEFT)->join(['ma1' => DbTables::TBL_MONEY_ACCOUNT], 'ta1.holder_id = ma1.id', ['money_account_id_from' => 'id', 'account_currency_from' => 'currency_id'], Select::JOIN_LEFT)->join(['ta2' => DbTables::TBL_TRANSACTION_ACCOUNTS], new Expression($this->getTable() . '.account_id_to = ta2.id AND ta2.type = ' . Account::TYPE_MONEY_ACCOUNT), [], Select::JOIN_LEFT)->join(['ma2' => DbTables::TBL_MONEY_ACCOUNT], 'ta2.holder_id = ma2.id', ['money_account_id_to' => 'id', 'account_currency_to' => 'currency_id'], Select::JOIN_LEFT);
});
}
作者:Mendi
项目:ep3-b
/**
* Gets all events within the specified datetime interval.
*
* Events are ordered by start date and time.
*
* @param \DateTime $dateTimeStart
* @param \DateTime $dateTimeEnd
* @param int $limit
* @param int $offset
* @param boolean $loadMeta
* @return array
*/
public function getInRange(\DateTime $dateTimeStart, \DateTime $dateTimeEnd, $limit = null, $offset = null, $loadMeta = true)
{
$where = new Where();
$where->greaterThan('datetime_end', $dateTimeStart->format('Y-m-d H:i:s'));
$where->and;
$where->lessThan('datetime_start', $dateTimeEnd->format('Y-m-d H:i:s'));
return $this->getBy($where, 'datetime_start ASC', $limit, $offset, $loadMeta);
}
作者:OSCype
项目:zskcs-skladzi
public function fetchAll_user($user)
{
$user_new = $user;
$where = new Where();
$where->like('id_uzytkownika', $user_new);
$resultSet = $this->tableGateway->select(array($where));
return $resultSet;
}
作者:arb
项目:MyCod
/**
* @param $taskId
* @return \DDD\Domain\Task\Staff[]
*/
public function getTaskStaff($taskId)
{
return $this->fetchAll(function (Select $select) use($taskId) {
$where = new Where();
$where->equalTo('task_id', $taskId)->notEqualTo('type', TaskService::STAFF_CREATOR);
$select->join(['users' => DbTables::TBL_BACKOFFICE_USERS], $this->getTable() . '.user_id = users.id', ['id', 'name' => new Expression("CONCAT(users.firstname, ' ', users.lastname)"), 'avatar'])->where($where)->order('type', 'ASC');
});
}
作者:arb
项目:MyCod
/**
* @param array $accList
* @param string $date
*
* @return \DDD\Domain\ApartmentGroup\ConciergeView[]|\ArrayObject
*/
public function getCheckoutByDay($accList, $date)
{
$columns = array('id', 'ki_page_hash', 'guest_first_name', 'guest_last_name', 'res_number', 'pax' => 'man_count', 'occupancy', 'date_to', 'guest_email', 'arrival_status', 'guest_balance', 'housekeeping_comment' => new Expression("(\n SELECT\n GROUP_CONCAT(\n CONCAT('<blockquote class=\"comment-blockquote\">', '<p>', value, '</p><footer>', users.firstname, ' ', users.lastname, ', ', `timestamp`, ' (Amsterdam Time)', '</footer></blockquote>') SEPARATOR ''\n )\n FROM " . DbTables::TBL_ACTION_LOGS . "\n LEFT JOIN " . DbTables::TBL_BACKOFFICE_USERS . " AS users ON users.id = " . DbTables::TBL_ACTION_LOGS . ".user_id\n WHERE module_id = " . Logger::MODULE_BOOKING . " AND identity_id = " . $this->getTable() . ".`id` AND action_id = " . Logger::ACTION_HOUSEKEEPING_COMMENT . "\n )"), 'ki_page_status' => 'ki_page_status', 'provide_cc_page_status');
return $this->fetchAll(function (Select $select) use($accList, $date, $columns) {
$where = new Where();
$where->equalTo($this->getTable() . '.date_to', $date)->equalTo($this->getTable() . '.status', Booking::BOOKING_STATUS_BOOKED)->notEqualTo($this->getTable() . '.arrival_status', ReservationTicketService::BOOKING_ARRIVAL_STATUS_NO_SHOW)->notEqualTo($this->getTable() . '.overbooking_status', BookingTicket::OVERBOOKING_STATUS_OVERBOOKED)->in($this->getTable() . '.apartment_id_assigned', $accList);
$select->columns($columns)->join(['apartment' => DbTables::TBL_APARTMENTS], $this->getTable() . '.apartment_id_assigned = apartment.id', ['unitNumber' => 'unit_number', 'acc_name' => 'name'], Select::JOIN_LEFT)->join(['charge' => DbTables::TBL_CHARGE], new Expression($this->getTable() . '.id = charge.reservation_id AND charge.addons_type = 6 AND charge.status = 0'), ['parking' => 'id'], Select::JOIN_LEFT)->join(['tasks' => DbTables::TBL_TASK], new Expression($this->getTable() . '.id = tasks.res_id AND tasks.task_type = ' . Task::TYPE_KEYFOB . ' AND tasks.task_status != ' . Task::STATUS_VERIFIED), ['key_task' => 'id'], Select::JOIN_LEFT)->where($where)->order([$this->getTable() . '.arrival_status ASC', $this->getTable() . '.guest_last_name ASC'])->group($this->getTable() . '.id');
});
}