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;
}
