public function get($outputInventoryId, $id = false) { $select = new Select($this->tableGateway->getTable()); $select->join('output_inventory', "output_inventory.id = " . $this->tableGateway->getTable() . ".output_inventory", array(), 'inner'); $select->where(array($this->tableGateway->getTable() . ".output_inventory" => $outputInventoryId)); if ($id) { $select->where(array($this->tableGateway->getTable() . ".id" => $id)); $resultSet = $this->tableGateway->selectWith($select); $result = $resultSet->current(); $product = $this->productTable->getName(false, $result->getProduct()); $result->setProduct(implode($product)); } else { $resultSet = $this->tableGateway->selectWith($select); $result = array(); foreach ($resultSet as $rows) { $product = $this->productTable->getName(false, $rows->getProduct()); $rows->setProduct(implode($product)); switch ($rows->getIva()) { case 1: $rows->setIvaAccumulated($rows->getCost() - $rows->getCost() / 1.16); $rows->setCost($rows->getCost() / 1.16); break; case 2: $rows->setIvaAccumulated($rows->getCost() * 0.16); break; case 3: $rows->setIvaAccumulated(0); break; } $result[] = $rows; } } return $result; }
public function fetchAll($paginate = true, $filter = array(), $orderBy = array()) { if ($paginate) { $select = new Select('service_language'); $select->join('lookup_status', 'lookup_status.status_id = service_language.status_id', array('status'), 'left'); /* Data filter code start here*/ if (count($filter) > 0) { $filter['language_name'] != "" ? $select->where("service_language.language_name LIKE '%" . $filter['language_name'] . "%'") : ""; $filter['status_id'] != "" ? $select->where("service_language.status_id = " . $filter['status_id']) : ""; } /* Data filter code end here*/ /* Data sorting code starts here */ if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') { switch ($orderBy['sort_field']) { case 'language': $select->order('service_language.language_name ' . $orderBy['sort_order']); break; case 'status': $select->order('lookup_status.status ' . $orderBy['sort_order']); break; } } /* Data sorting code ends here */ $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new ServiceLanguages()); $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } return $this->tableGateway->select(); }
/** * * @param int $days_threshold days threshold * @param int $ca_threshold turnover threshold * @param int $limit * @return type */ public function getCustomerGeo($days_threshold = 300, $ca_threshold = 1000, $min_accuracy = 6, $limit = 1000) { $akilia2db = $this->configuration['synchronizer']['db_akilia2']; $select = new Select(); $bcg = new \Zend\Db\Sql\TableIdentifier('base_customer_geo', $akilia2db); $bc = new \Zend\Db\Sql\TableIdentifier('base_customer', $akilia2db); $bs = new \Zend\Db\Sql\TableIdentifier('base_state', $akilia2db); $bco = new \Zend\Db\Sql\TableIdentifier('base_country', $akilia2db); $so = new \Zend\Db\Sql\TableIdentifier('sal_order', $akilia2db); $sol = new \Zend\Db\Sql\TableIdentifier('sal_order_line', $akilia2db); $select->from(["bc" => $bc], [])->join(['bcg' => $bcg], "bc.id = bcg.customer_id", [], Select::JOIN_LEFT)->join(['bs' => $bs], "bs.id = bc.state_id", [], Select::JOIN_LEFT)->join(['bco' => $bco], "bco.id = bc.country_id", [], Select::JOIN_LEFT)->join(['so' => $so], "bc.id = so.customer_id", [], Select::JOIN_INNER)->join(['sol' => $sol], "so.id = sol.order_id", [], Select::JOIN_INNER)->where('bc.flag_archived <> 1'); $columns = ['customer_id' => new Expression('bc.id'), 'name' => new Expression('bc.name'), 'street' => new Expression('bc.street'), 'street_2' => new Expression('bc.street_2'), 'street_number' => new Expression('bc.street_number'), 'state_reference' => new Expression('bs.reference'), 'state_name' => new Expression('bs.name'), 'zipcode' => new Expression('bc.zipcode'), 'city' => new Expression('bc.city'), 'country' => new Expression('bco.name'), 'accuracy' => new Expression('bcg.accuracy'), 'latitude' => new Expression('bcg.latitude'), 'longitude' => new Expression('bcg.longitude')]; $select->columns(array_merge($columns, ['total_net' => new Expression('sum(sol.price_total_net)')]), true); $select->group($columns); $select->having("sum(sol.price_total_net) > {$ca_threshold}"); $select->where(function (Where $where) use($min_accuracy) { //$where->greaterThan('so.date_order', '2012-12-31'); $where->notLike('bc.name', '%FINISHED%'); $where->nest->lessThan('accuracy', $min_accuracy)->or->isNull('accuracy')->unnest; }); $select->where(new Expression("(TO_DAYS(NOW()) - TO_DAYS(so.date_order)) < {$days_threshold}")); if ($limit > 0) { $select->limit($limit); } $store = $this->getStore($select); $data = $store->getData()->toArray(); return $data; }
/** * Get properties * * @param boolean $forceReload to initiliaze properties * * @return array */ public function getProperties($forceReload = false) { if ($this->getData('properties') == null or $forceReload) { $select = new Select(); $select->from('tab')->columns(array())->join('property', 'tab.id = property.tab_id', '*', Select::JOIN_INNER); if ($this->getDocumentId() !== null) { $select->join('document', 'document.document_type_id = tab.document_type_id', array(), Select::JOIN_INNER); $select->join('property_value', 'document.id = property_value.document_id AND property.id = property_value.property_id', array('value'), Select::JOIN_LEFT); $select->where(array('document.id' => $this->getDocumentId())); } if ($this->getTabId() != null) { $select->where(array('tab.id' => $this->getTabId())); } if ($this->getDocumentTypeId() != null) { $select->where(array('tab.document_type_id' => $this->getDocumentTypeId())); } $select->order('property.sort_order ASC'); $rows = $this->fetchAll($select); $properties = array(); foreach ($rows as $row) { $propertyModel = Model::fromArray((array) $row); if ($this->getDocumentId() !== null) { $propertyModel->setDocumentId($this->getDocumentId()); } $properties[] = $propertyModel; } $this->setData('properties', $properties); } return $this->getData('properties'); }
public function setParameters(Parameters $params) { $groupsPostsTable = Api::_()->getDbTable('Group\\DbTable\\GroupsPosts'); $groupsPostsTableName = $groupsPostsTable->initTableName()->getTable(); $groupsCategoriesTable = Api::_()->getDbTable('Group\\DbTable\\CategoriesGroups'); $groupsCategoriesTableName = $groupsCategoriesTable->initTableName()->getTable(); if ($params->group_id || $params->groupCategory) { $params->inGroup = true; } if ($params->inGroup) { $groupId = $params->group_id; $categoryId = $params->groupCategory; $this->where(function ($where) use($groupsPostsTableName, $groupsCategoriesTableName, $groupId, $categoryId) { $select = new Select($groupsPostsTableName); $select->columns(array('post_id')); if ($groupId) { $select->where(array('group_id' => $groupId)); } if ($categoryId) { $cateSelect = new Select($groupsCategoriesTableName); $cateSelect->columns(array('group_id')); $cateSelect->where(array('category_id' => $categoryId)); $select->where(function ($where) use($cateSelect) { $where->in('group_id', $cateSelect); return $where; }); } $where->in('id', $select); return $where; }); } return parent::setParameters($params); }
public function fetchAll($paginate = true, $filter = array(), $orderBy = array()) { if ($paginate) { $select = new Select('booking'); $select->columns(array('*', new Expression("service_provider.first_name as sp_first_name, service_provider.last_name as sp_last_name,\n\t\t\tinvoice.status_id AS invoice_status,payment_history.currency as currency,CASE invoice.status_id WHEN 0 THEN 'Unpaid' WHEN 1 THEN 'Paid' WHEN 2 THEN 'Partially Paid' END AS PaymentStatus"))); $select->join('booking_suggestion_history', 'booking_suggestion_history.booking_id = booking.id', array('booking_time', 'booking_status'), 'inner'); $select->join('users', 'users.id = booking.user_id', array('first_name', 'last_name'), 'left'); $select->join(array('service_provider' => 'users'), 'service_provider.id = booking.service_provider_id', array(), 'left'); $select->join('service_provider_service', 'service_provider_service.id = booking.service_provider_service_id', array('duration', 'price'), 'left'); $select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'left'); $select->join('invoice', 'invoice.id = booking.invoice_id', array('invoice_total', 'site_commision'), 'inner'); $select->join('invoice_details', 'invoice_details.invoice_id = invoice.id', array('sale_item_details'), 'inner'); $select->join('payment_history', 'payment_history.invoice_id = invoice.id', array(), 'inner'); $select->join('lookup_status', 'lookup_status.status_id = booking_suggestion_history.booking_status', array('status'), 'left'); $select->where('booking_suggestion_history.id = (SELECT id FROM booking_suggestion_history WHERE booking_id = booking.id ORDER BY id DESC LIMIT 1)'); count($filter) > 0 ? $select->where($filter) : ""; /* Data sorting code starts here */ if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') { switch ($orderBy['sort_field']) { case 'user': $select->order('users.first_name ' . $orderBy['sort_order']); break; case 'service_provider': $select->order('service_provider.first_name ' . $orderBy['sort_order']); break; case 'service': $select->order('service_category.category_name ' . $orderBy['sort_order']); break; case 'booked_date': $select->order('booking.booked_date ' . $orderBy['sort_order']); break; } } else { $select->order('booking.id desc'); } /* Data sorting code ends here */ //echo str_replace('"', '', $select->getSqlString()); exit; $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new Bookings()); $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } else { $select = $this->tableGateway->getSql()->select(); $select->columns(array('*', new Expression("service_provider.first_name as sp_first_name, service_provider.last_name as sp_last_name,\n\t\t\tinvoice.status_id AS invoice_status,CASE invoice.status_id WHEN 0 THEN 'Unpaid' WHEN 1 THEN 'Paid' WHEN 2 THEN 'Partially Paid' END AS PaymentStatus"))); $select->join('booking_suggestion_history', 'booking_suggestion_history.booking_id = booking.id', array('booking_time', 'booking_status'), 'inner'); $select->join('users', 'users.id = booking.user_id', array('first_name', 'last_name'), 'left'); $select->join(array('service_provider' => 'users'), 'service_provider.id = booking.service_provider_id', array(), 'left'); $select->join('service_provider_service', 'service_provider_service.id = booking.service_provider_service_id', array('duration', 'price'), 'left'); $select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'left'); $select->join('invoice', 'invoice.id = booking.invoice_id', array('invoice_total', 'site_commision', 'created_date'), 'inner'); $select->join('invoice_details', 'invoice_details.invoice_id = invoice.id', array('sale_item_details'), 'inner'); $select->join('payment_history', 'payment_history.invoice_id = invoice.id', array(), 'inner'); $select->join('lookup_status', 'lookup_status.status_id = booking_suggestion_history.booking_status', array('status'), 'left'); $select->where('booking_suggestion_history.id = (SELECT id FROM booking_suggestion_history WHERE booking_id = booking.id ORDER BY id DESC LIMIT 1)'); count($filter) > 0 ? $select->where($filter) : ""; return $this->tableGateway->selectwith($select); } }
/** * Sets the filter * @param \Zend\Db\Sql\Select $select * @param array $criteria * @return \Zend\Db\Sql\Select */ private function setFilter($select, $criteria) { $select->columns(array('*', 'role' => new \Zend\Db\Sql\Predicate\Expression('(SELECT `description` FROM `profile_role` WHERE `profile_role`.`id`=`profile`.`role_id` )'))); if (array_key_exists('role_id', $criteria) && $criteria['role_id'] != '') { $select->where('`profile`.`role_id` = ' . intval($criteria['role_id'])); } if (array_key_exists('sSearch', $criteria) && $criteria['sSearch'] != '') { $select->where(' ( `profile`.`first_name` LIKE "' . addslashes($criteria['sSearch']) . '%" OR `profile`.`last_name` LIKE "' . addslashes($criteria['sSearch']) . '%" ) '); } return $select; }
/** * Get config value * * @param string $source Source * @param string $locale Optional locale * * @return string value */ public function getValue($source, $locale = null) { $select = new Select(); $select->from('core_translate')->columns(array('src_id' => 'id', 'source'))->join('core_translate_locale', 'core_translate.id = core_translate_locale.core_translate_id', array('dst_id' => 'id', 'destination', 'locale'), Select::JOIN_INNER); if (!empty($source)) { $select->where(array('core_translate.source' => $source)); } if (!empty($locale)) { $select->where(array('core_translate_locale.locale' => $locale)); } return $this->fetchRow($select); }
public function buscarTodos($paginado = false, array $filtro = array()) { $sqlTotal = new Select(); $sqlTotal->from('despesas')->columns(['total' => new Expression('SUM(valor)')])->join('clientes', 'despesas.fk_cliente = clientes.id', []); $sqlTotalPendente = new Select(); $sqlTotalPendente->from('despesas')->columns(['totalPendente' => new Expression('SUM(valor)')])->join('clientes', 'despesas.fk_cliente = clientes.id', [])->where(['despesas.pagamento' => 'N']); $sqlTotalRecebido = new Select(); $sqlTotalRecebido->from('despesas')->columns(['totalRecebido' => new Expression('SUM(valor)')])->join('clientes', 'despesas.fk_cliente = clientes.id', [])->where(['despesas.pagamento' => 'S']); $sqlPrincipal = new Select(); $sqlPrincipal->from(['des' => 'despesas'])->columns(['id', 'fk_categoria', 'fk_subcategoria', 'fk_cliente', 'fk_conta', 'fk_cartao', 'descricao', 'valor', 'repetir', 'repetir_quando', 'repetir_ocorrencia', 'data_fatura', 'data_vencimento' => new Expression('DATE_FORMAT(data_vencimento, "%d/%m/%Y")'), 'pagamento' => new Expression('IF (pagamento = "S", "Efetivado", "Não Efetivado")'), 'pagamento_data' => new Expression('DATE_FORMAT(pagamento_data, "%d/%m/%Y")'), 'total' => new Expression('?', [$sqlTotal]), 'totalPendente' => new Expression('?', [$sqlTotalPendente]), 'totalRecebido' => new Expression('?', [$sqlTotalRecebido])])->join(['cli' => 'clientes'], 'des.fk_cliente = cli.id', ['cli_nome' => 'nome'])->join(['dca' => 'despesas_categorias'], 'des.fk_categoria = dca.id', ['dca_nome' => 'nome'])->join(['con' => 'contas'], 'des.fk_conta = con.id', ['con_nome' => 'nome'])->order('des.id Desc'); //Retorna as receitas do mês corrente. if (!isset($filtro['data']) && !isset($filtro['data_inicio']) && !isset($filtro['data_fim'])) { $parteSql = 'DATE_FORMAT(data_vencimento, "%Y-%m") = DATE_FORMAT(NOW(), "%Y-%m")'; $sqlTotalPendente->where($parteSql); $sqlTotalRecebido->where($parteSql); $sqlTotal->where($parteSql); $sqlPrincipal->where($parteSql); } //Filtros if (count($filtro) > 0) { $arrayFiltro = array_filter(['des.descricao' => isset($filtro['descricao']) ? filter_var($filtro['descricao'], FILTER_SANITIZE_STRING) . '%' : null, 'des.pagamento' => isset($filtro['pagamento']) ? filter_var($filtro['pagamento'], FILTER_SANITIZE_STRING) : null, 'dca.nome' => isset($filtro['categoria']) ? filter_var($filtro['categoria'], FILTER_SANITIZE_STRING) . '%' : null, 'con.nome' => isset($filtro['conta']) ? filter_var($filtro['conta'], FILTER_SANITIZE_STRING) . '%' : null, 'des.valor' => isset($filtro['valor']) ? filter_var($filtro['valor'], FILTER_SANITIZE_STRING) : null]); foreach ($arrayFiltro as $chave => $valor) { $sqlPrincipal->where->like($chave, $valor); } if (isset($filtro['data_inicio']) && !empty('data_inicio') && isset($filtro['data_fim']) && !empty($filtro['data_fim'])) { $dtInicio = filter_var($filtro['data_inicio'], FILTER_SANITIZE_NUMBER_INT); $dtFim = filter_var($filtro['data_fim'], FILTER_SANITIZE_NUMBER_INT); $dataInicio = implode('-', array_reverse(explode('-', $dtInicio))); $dataFim = implode('-', array_reverse(explode('-', $dtFim))); $sqlTotalPendente->where->between('des.data_vencimento', $dataInicio, $dataFim); $sqlTotal->where->between('des.data_vencimento', $dataInicio, $dataFim); $sqlTotalRecebido->where->between('des.data_vencimento', $dataInicio, $dataFim); $sqlPrincipal->where->between('des.data_vencimento', $dataInicio, $dataFim); } //Retorna as receitas de acordo com o filtro data if (isset($filtro['data']) && !empty($filtro['data']) && !isset($filtro['data_inicio']) && !isset($filtro['data_fim'])) { $data = filter_var($filtro['data'], FILTER_SANITIZE_NUMBER_INT); $parteSql = ['DATE_FORMAT(data_vencimento, "%Y-%m") = DATE_FORMAT(?, "%Y-%m")' => $data]; $sqlTotalPendente->where($parteSql); $sqlTotalRecebido->where($parteSql); $sqlTotal->where($parteSql); $sqlPrincipal->where($parteSql); } } if ($paginado) { $paginatorAdapter = new DbSelect($sqlPrincipal, $this->getAdapter()); $paginado = new Paginator($paginatorAdapter); return $paginado; } $dados = $this->selectWith($sqlPrincipal); return $dados; }
public function buscarTodos($paginado = false, array $filtro = array()) { $sqlTotal = new Select(); $sqlTotal->from('receitas')->columns(['total' => new Expression('SUM(receitas.valor)')])->join('clientes', 'receitas.fk_cliente = clientes.id', []); $sqlTotalPendente = new Select(); $sqlTotalPendente->from('receitas')->columns(['total_pendente' => new Expression('SUM(receitas.valor)')])->join('clientes', 'receitas.fk_cliente = clientes.id', [])->where(['receitas.pagamento' => 'N']); $sqlTotalRecebido = new Select(); $sqlTotalRecebido->from('receitas')->columns(['total' => new Expression('SUM(receitas.valor)')])->join('clientes', 'receitas.fk_cliente = clientes.id', [])->where(['receitas.pagamento' => 'S']); $sqlPrincipal = new Select(); $sqlPrincipal->from(array('rec' => 'receitas'))->columns(array('id', 'descricao', 'valor', 'pagamento' => new Expression('IF (pagamento = "S", "Efetivado", "Não Efetivado")'), 'pagamento_data' => new Expression("DATE_FORMAT(pagamento_data, '%d/%m/%Y')"), 'total' => new Expression('?', array($sqlTotal)), 'total_pendente' => new Expression('?', array($sqlTotalPendente)), 'total_recebido' => new Expression('?', array($sqlTotalRecebido))))->join(array('cli' => 'clientes'), 'rec.fk_cliente = cli.id', array('cli_nome' => 'nome'))->join(array('con' => 'contas'), 'rec.fk_conta = con.id', array('con_nome' => 'nome'))->join(array('cat' => 'receitas_categorias'), 'rec.fk_categoria = cat.id', array('cat_nome' => 'nome'))->order('rec.id Desc'); //Retorna as receitas do mês corrente. if (!isset($filtro['data']) && !isset($filtro['data_inicio']) && !isset($filtro['data_fim'])) { $parteSql = 'DATE_FORMAT(data_vencimento, "%Y-%m") = DATE_FORMAT(NOW(), "%Y-%m")'; $sqlTotalPendente->where($parteSql); $sqlTotalRecebido->where($parteSql); $sqlTotal->where($parteSql); $sqlPrincipal->where($parteSql); } //Outros filtros if (count($filtro) > 0) { $arrayFiltro = array('rec.descricao' => isset($filtro['descricao']) ? filter_var($filtro['descricao'], FILTER_SANITIZE_STRING) . '%' : null, 'cat.nome' => isset($filtro['categoria']) ? filter_var($filtro['categoria'], FILTER_SANITIZE_STRING) . '%' : null, 'con.nome' => isset($filtro['conta']) ? filter_var($filtro['conta'], FILTER_SANITIZE_STRING) . '%' : null, 'rec.pagamento' => isset($filtro['pagamento']) ? filter_var($filtro['pagamento'], FILTER_SANITIZE_STRING) : null, 'rec.valor' => isset($filtro['valor']) ? filter_var($filtro['valor'], FILTER_SANITIZE_STRING) : null); foreach (array_filter($arrayFiltro) as $chave => $valor) { $sqlPrincipal->where->like($chave, $valor); } if (isset($filtro['data_inicio']) && !empty('data_inicio') && isset($filtro['data_fim']) && !empty($filtro['data_fim'])) { $data_inicio = $this->converterData(filter_var($filtro['data_inicio'], FILTER_SANITIZE_NUMBER_INT)); $data_fim = $this->converterData(filter_var($filtro['data_fim'], FILTER_SANITIZE_NUMBER_INT)); $sqlTotalPendente->where->between('rec.data_vencimento', $data_inicio, $data_fim); $sqlTotal->where->between('rec.data_vencimento', $data_inicio, $data_fim); $sqlTotalRecebido->where->between('rec.data_vencimento', $data_inicio, $data_fim); $sqlPrincipal->where->between('rec.data_vencimento', $data_inicio, $data_fim); } //Retorna as receitas de acordo com o filtro data if (isset($filtro['data']) && !empty($filtro['data']) && !isset($filtro['data_inicio']) && !isset($filtro['data_fim'])) { $data = filter_var($filtro['data'], FILTER_SANITIZE_NUMBER_INT); $parteSql = ['DATE_FORMAT(data_vencimento, "%Y-%m") = DATE_FORMAT(?, "%Y-%m")' => $data]; $sqlTotalPendente->where($parteSql); $sqlTotalRecebido->where($parteSql); $sqlTotal->where($parteSql); $sqlPrincipal->where($parteSql); } } //Se os dados forem paginados, entra aqui. if ($paginado) { $paginatorAdapter = new DbSelect($sqlPrincipal, $this->getAdapter()); $paginado = new Paginator($paginatorAdapter); return $paginado; } $dados = $this->selectWith($sqlPrincipal); return $dados; }
public function fetchAll($userId, $cond = null) { $select = new Select('user_track'); if ($cond == null) { $select->join('packages', 'packages.package_id = user_track.usertrack_package_id', array('package_name')); $select->where(" usertrack_user_id = '{$userId}' and track_type = 'package' "); } else { $select->where(" usertrack_user_id = '{$userId}' and track_type = 'offer' and offer_status = '1' "); } $select->where(" usertrack_user_id = '{$userId}' "); $paginatorAdapter = new DbSelect($select, $this->adapter); $collection = new PackageCollection($paginatorAdapter); return $collection; }
public function fetchAll($id, $paginate = true, $filter = array(), $orderBy = array()) { if ($paginate) { $select = new Select('media'); //$select->columns(array('*', new Expression("user_feature_setting.email as email_status"))); $select->join('users', 'users.id = media.user_id', array('first_name', 'last_name'), 'inner'); $select->join('lookup_status', 'lookup_status.status_id = media.status_id', array('status'), 'left'); $select->where(array('user_id' => $id, 'media_type' => 1)); /* Data filter code start here*/ if (count($filter) > 0) { isset($filter['name']) && $filter['name'] != "" ? $select->where("CONCAT(users.first_name,' ',users.last_name) LIKE '%" . $filter['name'] . "%'") : ""; isset($filter['title']) && $filter['title'] != "" ? $select->where("media.media_title LIKE '%" . $filter['title'] . "%'") : ""; isset($filter['media_type']) && $filter['media_type'] != "" ? $select->where("media.media_type = " . $filter['media_type']) : ""; if (isset($filter['from_date']) && $filter['from_date'] != "" && isset($filter['to_date']) && $filter['to_date'] != "") { $select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') BETWEEN '" . $filter['from_date'] . "' AND '" . $filter['to_date'] . "'"); } else { if (isset($filter['from_date']) && !isset($filter['to_date']) && $filter['from_date'] != "") { $select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') = '" . $filter['from_date'] . "'"); } else { if (!isset($filter['from_date']) && isset($filter['to_date']) && $filter['to_date'] != "") { $select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') = '" . $filter['to_date'] . "'"); } } } isset($filter['status_id']) && $filter['status_id'] != "" ? $select->where("media.status_id = " . $filter['status_id']) : ""; } /* Data filter code end here*/ /* Data sorting code starts here */ if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') { switch ($orderBy['sort_field']) { case 'name': $select->order('users.first_name ' . $orderBy['sort_order']); break; case 'title': $select->order('media.media_title ' . $orderBy['sort_order']); break; case 'media_type': $select->order('media.media_type ' . $orderBy['sort_order']); break; case 'date': $select->order('media.created_date ' . $orderBy['sort_order']); break; case 'status': $select->order('lookup_status.status ' . $orderBy['sort_order']); break; } } /* Data sorting code ends here */ //echo str_replace('"', '', $select->getSqlString()); exit; $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new UsersMedia()); $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } else { $select = $this->tableGateway->getSql()->select(); return $this->tableGateway->selectwith($select); } }
/** * Sets the filter * @param \Zend\Db\Sql\Select $select * @param array $criteria * @return \Zend\Db\Sql\Select */ private function setFilter($select, $criteria) { if (is_numeric($this->partIVA)) { $select->where('`codFisc` IN (SELECT `codFisc` FROM `person_to_company` WHERE `or_partIVA` = "' . addslashes($this->partIVA) . '")'); } if ($this->codFisc != '') { $select->where(' ( `codFisc` IN (SELECT `or_codFisc` FROM `person_to_person` WHERE `codFisc` = "' . addslashes($this->codFisc) . '") OR `codFisc` IN (SELECT `codFisc` FROM `person_to_person` WHERE `or_codFisc` = "' . addslashes($this->codFisc) . '") ) '); } return $select; }
public function fetchPostsByFeeds($feeds, $offset = null, $limit = null) { $select = new Select(self::$_tableName); if (!is_null($offset)) { $select->offset($offset); } if (!is_null($limit)) { $select->limit($limit); } $select->join('directus_social_feeds', 'directus_social_feeds.id = directus_social_posts.feed', ['feed_type' => 'type'])->order('directus_social_posts.datetime DESC'); $select->where->equalTo('directus_social_posts.status', 1)->equalTo('directus_social_feeds.status', 1); $FeedWhere = new Where(); $SocialCache = new SocialCache(); foreach ($feeds as $feed) { // Run scrape if due $SocialCache->scrapeFeedIfDue($feed['name'], $feed['type']); $FeedWhere->or->nest->equalTo('directus_social_feeds.name', $feed['name'])->equalTo('directus_social_feeds.type', $feed['type'])->unnest; $select->where($FeedWhere); } $socialPosts = $this->selectWith($select); $socialPosts = $socialPosts->toArray(); // Unserialize cached feed entry API-responses foreach ($socialPosts as &$post) { $post['data'] = json_decode($post['data'], true); } return $socialPosts; }
protected function quickSearch() { if ($this->getQuickSearchQuery()) { $where = $this->getQuickSearchQuery()->getRawState('where'); $this->select->where($where); } }
/** * Simple, default implementation for find * * This will allow you to do queries for rows in the table, * where you provide field=>values for the where clause. * Only fields actually in the table can be included this way. * * You generally want to override this implementation with your own * However, this basic implementation will allow you to get up and * running quicker. * * @param array $fields Key value pairs to select on * @param string $order The default ordering to use for select * @param boolean $paginated If set to true, will return a paginator * @param int $limit */ public function find($fields = null, $order = null, $paginated = false, $limit = null) { $select = new Select($this->tableGateway->getTable()); if (count($fields)) { foreach ($fields as $key => $value) { if (isset($this->columns)) { if (in_array($key, $this->columns)) { $select->where([$key => $value]); } } else { $select->where([$key => $value]); } } } return $this->performSelect($select, $order, $paginated, $limit); }
public function indexAction() { $searchform = new AlbumSearchForm(); $searchform->get('submit')->setValue('Search'); $select = new Select(); $order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'id'; $order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_ASCENDING; $page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1; $select->order($order_by . ' ' . $order); $search_by = $this->params()->fromRoute('search_by') ? $this->params()->fromRoute('search_by') : ''; $where = new \Zend\Db\Sql\Where(); $formdata = array(); if (!empty($search_by)) { $formdata = (array) json_decode($search_by); if (!empty($formdata['artist'])) { $where->addPredicate(new \Zend\Db\Sql\Predicate\Like('artist', '%' . $formdata['artist'] . '%')); } if (!empty($formdata['title'])) { $where->addPredicate(new \Zend\Db\Sql\Predicate\Like('title', '%' . $formdata['title'] . '%')); } } if (!empty($where)) { $select->where($where); } $album = $this->getAlbumTable()->fetchAll($select); $totalRecord = $album->count(); $itemsPerPage = 2; $album->current(); $paginator = new Paginator(new paginatorIterator($album)); $paginator->setCurrentPageNumber($page)->setItemCountPerPage($itemsPerPage)->setPageRange(7); $searchform->setData($formdata); return new ViewModel(array('search_by' => $search_by, 'order_by' => $order_by, 'order' => $order, 'page' => $page, 'paginator' => $paginator, 'pageAction' => 'album', 'form' => $searchform, 'totalRecord' => $totalRecord)); }
public function fetchAll() { $select = new Select($this->tableGateway->getTable()); $select->where("id != 1"); $resultSet = $this->tableGateway->selectWith($select); return $resultSet; }
/** * Sets the filter * @param \Zend\Db\Sql\Select $select * @param array $criteria * @return \Zend\Db\Sql\Select */ private function setFilter($select, $criteria) { if (array_key_exists('profile_id', $criteria) && $criteria['profile_id'] != '') { $select->where('`profile_id` = ' . intval($criteria['profile_id'])); } return $select; }
public function find($params) { $sql = new Sql($this->getAdapter()); $select = new Select(); $select->from($params['from']); if (!empty($params['columns'])) { $select->columns($params['columns']); } foreach ($params['where'] as $where) { $select->where($where); } foreach ($params['joins'] as $join) { if (empty($join['columns'])) { $join['columns'] = Select::SQL_STAR; } if (empty($join['type'])) { $join['type'] = Select::JOIN_INNER; } $select->join($join['name'], $join['on'], $join['columns'], $join['type']); } $query = $sql->getSqlStringForSqlObject($select); $results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE); $data = $results->toArray(); if (empty($data)) { return false; } else { if (count($data) == 1) { return $data[0]; } else { return $data; } } }
/** * index Action * Receive the search params * Build the search query * Generate the search result as a list * @return type * Author: Tahmina Khatoon */ public function indexAction() { $searchform = new UserRolesSearchForm(); $searchform->get('submit')->setValue('Search'); $select = new Select(); $order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'id'; $order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_DESCENDING; $page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1; $item_per_page = $this->params()->fromRoute('item_per_page') ? (int) $this->params()->fromRoute('item_per_page') : 10; $page_range = $this->params()->fromRoute('page_range') ? (int) $this->params()->fromRoute('page_range') : 7; $select->order($order_by . ' ' . $order); $search_by = $this->params()->fromRoute('search_by') ? $this->params()->fromRoute('search_by') : ''; $where = new \Zend\Db\Sql\Where(); $formdata = array(); if (!empty($search_by)) { $formdata = (array) json_decode($search_by); if (!empty($formdata['user_id'])) { $where->addPredicate(new \Zend\Db\Sql\Predicate\Like('user_id', '%' . $formdata['user_id'] . '%')); } if (!empty($formdata['role_id'])) { $where->addPredicate(new \Zend\Db\Sql\Predicate\Like('role_id', '%' . $formdata['role_id'] . '%')); } } if (!empty($where)) { $select->where($where); } $paginator = $this->getUserRolesTable()->fetchAll($select, true); $paginator->setCurrentPageNumber($page)->setItemCountPerPage($item_per_page)->setPageRange($page_range); $totalRecord = $paginator->getTotalItemCount(); $currentPage = $paginator->getCurrentPageNumber(); $totalPage = $paginator->count(); $searchform->setData($formdata); $this->vm->setVariables(array('flashMessages' => $this->flashMessenger()->getMessages(), 'search_by' => $search_by, 'order_by' => $order_by, 'order' => $order, 'page' => $page, 'item_per_page' => $item_per_page, 'paginator' => $paginator, 'pageAction' => 'user_roles/index', 'form' => $searchform, 'totalRecord' => $totalRecord, 'currentPage' => $currentPage, 'totalPage' => $totalPage)); return $this->vm; }
public function getList($where = array(), $order = null, $offset = null, $limit = null) { if (empty($where['ProductFilterOption.productCategoryFilterOptionID'])) { $select = new Select(); $select->from(array('b' => 'Product')); $select->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName')); $select->where($where); $select->offset($offset); $select->limit($limit); $select->order($order); } else { $select = $this->getSelect(); $select->columns(array())->join(array('b' => 'Product'), 'ProductFilterOption.productID = b.productID')->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName'))->where($where)->offset($offset)->limit($limit)->group(array('ProductFilterOption.productID'))->having('count(ProductFilterOption.productID) > ' . (count($where['ProductFilterOption.productCategoryFilterOptionID']) - 1)); $select->order($order); } $paginator = $this->paginate($select); $paginator->setCurrentPageNumber(ceil($offset / $limit) + 1); //$paginator->setItemCountPerPage(1); $products = $paginator->getCurrentItems()->getArrayCopy(); $pages = $paginator->getPages(); $productsCount = $paginator->getTotalItemCount(); foreach ($products as $k => $v) { $products[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']); } return array('products' => $products, 'productsCount' => $productsCount, 'pages' => $pages); }
/** * * @param \Zend\Db\Sql\Select $query */ protected function initFilters($query) { $value = $this->getParamAdapter()->getValueOfFilter('idGuildes'); if ($value != null) { $query->where("idGuildes = '" . $value . "' "); } $value = $this->getParamAdapter()->getValueOfFilter('nom'); if ($value != null) { $query->where("nom like '%" . $value . "%' "); } $value = $this->getParamAdapter()->getValueOfFilter('serveur'); if ($value != null) { $query->where("serveur like '%" . $value . "%' "); } $value = $this->getParamAdapter()->getValueOfFilter('niveau'); if ($value != null) { $query->where("niveau like '%" . $value . "%' "); } $value = $this->getParamAdapter()->getValueOfFilter('miniature'); if ($value != null) { $query->where("miniature like '%" . $value . "%' "); } $value = $this->getParamAdapter()->getValueOfFilter('idFaction'); if ($value != null) { $query->where("idFaction = '" . $value . "' "); } }
public function getUsersAndAbove(bool $paginated, $name = '', $roles = []) { $select = new Select('account'); $where = new Where(); if ($roles) { $sub = $where->nest(); for ($i = 0; $i < count($roles); $i++) { $sub->equalTo('role', $roles[$i]); if ($i < count($roles) - 1) { $sub->or; } } $sub->unnest(); } else { $where->greaterThan('role', '0'); $where->lessThan('role', '32'); } if ($name) { $where->like('name', '%' . $name . '%'); } $select->where($where)->order('name ASC'); if ($paginated) { $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new Account()); $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); return new Paginator($paginatorAdapter); } return $this->tableGateway->select($select); }
public function fetchAll($sort = 'name', $order = 'ASC', $page = 1, $perPage = 15, $status = true) { $offset = $perPage * ($page - 1); switch ($sort) { case "id": $sort = "C_Id"; break; case "name": $sort = "C_Name"; break; case "lastinvoice": $sort = "C_LastInvoice"; break; default: $sort = "C_Name"; } $select = new Select(); $select->from($this->table); $select->columns(array('C_Id' => 'C_Id', 'C_Name' => 'C_Name', 'C_PaymentTerms' => 'C_PaymentTerms', 'C_Reference' => 'C_Reference', 'C_CRN' => 'C_CRN', 'C_Class' => 'C_Class', 'C_Status' => 'C_Status', 'C_Updated' => 'C_Updated'))->order($sort . ' ' . $order)->limit($perPage)->offset($offset); if ($status) { $select->where(array('C_Status' => 1)); } $resultSet = $this->selectWith($select); return $resultSet; }
public function getByProduct($product) { $select = new Select($this->tableGateway->getTable()); $select->where(array("product" => $product)); $rows = $this->tableGateway->selectWith($select); return $rows ? $rows : false; }
/** * * @param \Zend\Db\Sql\Select $query */ protected function initFilters($query) { $value = $this->getParamAdapter()->getValueOfFilter('idRaid'); if ($value != null) { $query->where("idRaid like '%" . $value . "%' "); } $value = $this->getParamAdapter()->getValueOfFilter('idEvenements'); if ($value != null) { $query->where("idEvenements = '" . $value . "' "); } $value = $this->getParamAdapter()->getValueOfFilter('date'); if ($value != null) { $query->where("date like '%" . $value . "%' "); } $value = $this->getParamAdapter()->getValueOfFilter('note'); if ($value != null) { $query->where("note like '%" . $value . "%' "); } $value = $this->getParamAdapter()->getValueOfFilter('valeur'); if ($value != null) { $query->where("valeur = '" . $value . "' "); } $value = $this->getParamAdapter()->getValueOfFilter('ajoutePar'); if ($value != null) { $query->where("ajoutePar like '%" . $value . "%' "); } $value = $this->getParamAdapter()->getValueOfFilter('majPar'); if ($value != null) { $query->where("majPar like '%" . $value . "%' "); } }
/** * * @param \Zend\Db\Sql\Select $query */ protected function initFilters($query) { $value = $this->getParamAdapter()->getValueOfFilter('idItem'); if ($value != null) { $query->where("idItem = '" . $value . "' "); } $value = $this->getParamAdapter()->getValueOfFilter('nom'); if ($value != null) { $query->where("nom like '%" . $value . "%' "); } $value = $this->getParamAdapter()->getValueOfFilter('ajouterPar'); if ($value != null) { $query->where("ajouterPar like '%" . $value . "%' "); } $value = $this->getParamAdapter()->getValueOfFilter('majPar'); if ($value != null) { $query->where("majPar like '%" . $value . "%' "); } $value = $this->getParamAdapter()->getValueOfFilter('idItemJeu'); if ($value != null) { $query->where("idIBnet like '%" . $value . "%' "); } $value = $this->getParamAdapter()->getValueOfFilter('couleur'); if ($value != null) { $query->where("couleur like '%" . $value . "%' "); } }
public function fetchAll($paginate = true, $usertype = 8) { if ($paginate) { switch ($usertype) { case 3: $select = new Select('users'); $select->join('lookup_status', 'lookup_status.status_id = users.status_id', array('status'), 'left'); $select->join('user_feature_setting', 'user_feature_setting.user_id = users.id', array(), 'left'); $select->where('users.user_type_id = 3 and user_feature_setting.newsletter in (1,4)'); break; case 4: $select = new Select('users'); $select->join('lookup_status', 'lookup_status.status_id = users.status_id', array('status'), 'left'); $select->join('user_feature_setting', 'user_feature_setting.user_id = users.id', array(), 'left'); $select->where('users.user_type_id = 4 and user_feature_setting.newsletter in (1,4)'); break; case 8: $select = new Select('newsletter_subscription'); $select->join('lookup_status', 'lookup_status.status_id = newsletter_subscription.status_id', array('status'), 'left'); break; default: $select = new Select('newsletter_subscription'); $select->join('lookup_status', 'lookup_status.status_id = newsletter_subscription.status_id', array('status'), 'left'); break; } $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new NewsletterSubscribers()); $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } return $this->tableGateway->select(); }
public function fetchAll($request, $paginated = false) { if ($paginated) { // create a new Select object for the table album $select = new Select('album'); $select->order($request['sort'] . " " . $request['order']); // Search if ($request['search']) { $x = $request['search'] . '%'; $where = new \Zend\Db\Sql\Where(); $where->like('title', $x); $where->or->like('artist', $x); $select->where($where); } // New result set based on the Album entity $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new Album()); // New pagination adapter object $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } $resultSet = $this->tableGateway->select(); return $resultSet; }