/** * @param $year * @param $month * @return \Illuminate\View\View */ public function archive($year, $month) { $data = $this->getOverviewData(); $posts = $this->post->forPublic()->where($this->db->raw('MONTHNAME(publish_date)'), '=', $month)->where($this->db->raw('YEAR(publish_date)'), '=', $year)->orderBy('publish_date', 'DESC')->paginate($this->config->items_per_page); $data['posts'] = $this->dateFormat($posts); $data['headings'] = "All posts from <strong>" . $month . " " . $year . "</strong>"; return view('blogify.index', $data); }
public function __construct(DatabaseManager $DB, AuthenticationManagementInterface $AuthenticationManager, Translator $Lang) { // $this->DB = $DB; // $this->DB->connection()->enableQueryLog(); $this->Database = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Account AS a')->leftJoin('ACCT_Account AS ap', 'ap.id', '=', 'a.parent_account_id')->join('ACCT_Account_Type AS at', 'at.id', '=', 'a.account_type_id')->where('a.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->whereNull('a.deleted_at'); $this->visibleColumns = array('a.id AS acct_am_id', 'a.key as acct_am_key', 'a.name as acct_am_name', 'a.balance_type as acct_am_balance_type', 'a.is_group as acct_am_is_group', 'a.account_type_id as acct_am_account_type_id', 'at.name as acct_am_account_type', 'ap.id as acct_am_parent_account_id', 'ap.key as acct_am_parent_key', 'ap.name as acct_am_parent_account', $DB->raw('CASE a.balance_type WHEN "D" THEN "' . $Lang->get('decima-accounting::account-management.D') . '" ELSE "' . $Lang->get('decima-accounting::account-management.A') . '" END AS acct_am_balance_type_name'), $DB->raw('CASE a.is_group WHEN 1 THEN 0 ELSE 1 END AS acct_am_is_leaf')); $this->orderBy = array(array('acct_am_key', 'asc')); $this->treeGrid = true; $this->parentColumn = 'ap.id'; $this->leafColumn = 'acct_am_is_leaf'; }
/** * Check if given participants are * in a conversation * * @param $participants * @return mixed */ public function inConversation($participants) { list($num_user_in_conversation, $valuesEscaped) = $this->getEscapedValues($participants); // prepare question marks for the query $questionmarks = str_repeat("?,", $num_user_in_conversation - 1) . "?"; $query = $this->db->select($this->db->raw('select cu.conversation_id, cu.deleted_at from conversation_joined cu group by cu.conversation_id, cu.deleted_at having SUM(cu.participant_id in ( ' . $questionmarks . ' )) = ? and SUM(cu.participant_id not in ( ' . $questionmarks . ' )) = 0 '), $valuesEscaped); return $query; }
public function __construct(DatabaseManager $DB, AuthenticationManagementInterface $AuthenticationManager, Translator $Lang) { $this->DB = $DB; $this->AuthenticationManager = $AuthenticationManager; /* $this->Database = $DB->table('ACCT_Journal_Entry AS je') ->join('ACCT_Journal_Voucher AS jv', 'jv.id', '=', 'je.journal_voucher_id') ->rightJoin('ACCT_Account AS c', 'je.account_id', '=', 'c.id') ->join('ACCT_Account_Type AS at', 'at.id', '=', 'c.account_type_id') ->where(function($query) { $query->orWhere('jv.status', '=', 'B'); $query->orWhereNull('jv.status'); } ) ->where('c.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId()) ->whereIn('at.pl_bs_category', array('B', 'C')) ->whereNull('je.deleted_at') ->whereNull('jv.deleted_at'); */ $this->Database = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Journal_Entry AS je')->join('ACCT_Journal_Voucher AS jv', 'jv.id', '=', 'je.journal_voucher_id')->rightJoin('ACCT_Account AS c', 'je.account_id', '=', 'c.id')->join('ACCT_Account_Type AS at', 'at.id', '=', 'c.account_type_id')->where('jv.status', '=', 'B')->where('jv.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->where('c.is_group', '=', 0)->whereIn('at.pl_bs_category', array('B', 'C'))->whereNull('je.deleted_at')->whereNull('jv.deleted_at'); $this->Database2 = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Account AS c')->join('ACCT_Account_Type AS at', 'at.id', '=', 'c.account_type_id')->where('c.is_group', '=', 1)->where('c.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->whereIn('at.pl_bs_category', array('B', 'C'))->select(array($DB->raw('0 AS acct_pl_debit'), $DB->raw('0 AS acct_pl_credit'), 'c.id AS acct_pl_account_id', 'c.parent_account_id AS acct_pl_parent_account_id', 'c.key AS acct_pl_account_key', 'c.name AS acct_pl_account_name', 'c.is_group AS acct_pl_is_group', 'c.balance_type AS acct_pl_balance_type', $DB->raw('CASE at.pl_bs_category WHEN "B" THEN "' . $Lang->get('decima-accounting::profit-and-loss.income') . '" ELSE "' . $Lang->get('decima-accounting::profit-and-loss.expenses') . '" END AS acct_pl_pl_bs_category'), $DB->raw('0 AS acct_pl_balance'))); $this->visibleColumns = array($DB->raw('IFNULL(SUM(je.debit),0) AS acct_pl_debit'), $DB->raw('IFNULL(SUM(je.credit),0) AS acct_pl_credit'), 'c.id AS acct_pl_account_id', 'c.parent_account_id AS acct_pl_parent_account_id', 'c.key AS acct_pl_account_key', 'c.name AS acct_pl_account_name', 'c.is_group AS acct_pl_is_group', 'c.balance_type AS acct_pl_balance_type', $DB->raw('CASE at.pl_bs_category WHEN "B" THEN "' . $Lang->get('decima-accounting::profit-and-loss.income') . '" ELSE "' . $Lang->get('decima-accounting::profit-and-loss.expenses') . '" END AS acct_pl_pl_bs_category'), $DB->raw('0 AS acct_pl_balance')); $this->orderBy = array(array('acct_pl_account_key', 'asc')); }
/** * Adds selects to a query * * @param array $selects * * @return void */ public function filterQuery(&$selects) { if ($select = $this->getOption('select')) { $selects[] = $this->db->raw($select . ' AS ' . $this->db->getQueryGrammar()->wrap($this->getOption('column_name'))); } }
/** * Return the table to join. * * @return string|mixed */ public function tableToJoin() { $table = $this->table; if ($this->table !== $this->tableAlias) { $table = DB::raw("{$this->table} as {$this->tableAlias}"); } return $table; }
public function __construct(DatabaseManager $DB, AuthenticationManagementInterface $AuthenticationManager, AccountManagementInterface $AccountManager, Translator $Lang, Carbon $Carbon) { $this->DB = $DB; $this->AccountManager = $AccountManager; $this->AuthenticationManager = $AuthenticationManager; $this->Carbon = $Carbon; $this->Database = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Journal_Entry AS je')->join('ACCT_Account AS c', 'je.account_id', '=', 'c.id')->join('ACCT_Journal_Voucher AS jv', 'jv.id', '=', 'je.journal_voucher_id')->leftJoin('PURCH_Document_Type AS dt', 'dt.id', '=', 'jv.document_type_id')->leftJoin('PURCH_Supplier AS ps', 'ps.id', '=', 'jv.supplier_id')->leftJoin('SALE_Client AS cl', 'cl.id', '=', 'jv.client_id')->leftJoin('HR_Employee AS e', 'e.id', '=', 'jv.employee_id')->join('ACCT_Voucher_Type AS vt', 'vt.id', '=', 'jv.voucher_type_id')->where('jv.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->where('c.is_group', '=', 0)->where('jv.status', '=', 'B')->whereNull('je.deleted_at')->whereNull('jv.deleted_at')->select(array('jv.date', 'jv.number', 'jv.remark', 'jv.manual_reference', 'vt.name AS voucher_type_name', 'jv.document_date', 'jv.document_number', 'jv.document_amount', 'dt.name AS document_type_name', $DB->raw('CONCAT(e.names, \' \', e.surnames) AS employee_name'), 'e.tax_id as employee_tax_id', 'e.single_identity_document_number as employee_single_identity_document_number', 'ps.name as supplier_name', 'ps.registration_number AS supplier_registration_number', 'ps.tax_id as supplier_tax_id', 'ps.single_identity_document_number as supplier_single_identity_document_number', 'cl.name as client_name', 'cl.registration_number AS client_registration_number', 'cl.tax_id as client_tax_id', 'cl.single_identity_document_number as client_single_identity_document_number', 'c.key AS account_key', 'c.name AS account_name', 'je.debit', 'je.credit')); $this->orderBy = array(array('jv.document_date', 'asc')); }
/** * @param string $identifier * * @return \Illuminate\Database\Query\Builder */ public function queryUsersByIdentifier($identifier) { $first = $this->getEntities()->first(); $query = $this->database->table($first->table)->selectRaw($this->database->raw("'{$first->type}' AS type, id, `{$first->identifier}` AS identifier"))->where($first->identifier, '=', $identifier); foreach ($this->getEntities()->slice(1) as $entity) { $subQuery = $this->database->table($entity->table)->selectRaw($this->database->raw("'{$entity->type}' AS type, id, `{$entity->identifier}` AS identifier"))->where($entity->identifier, '=', $identifier); $query = $query->union($subQuery); } return $query; }
public function __construct(DatabaseManager $DB, AuthenticationManagementInterface $AuthenticationManager, Translator $Lang) { // $this->DB = $DB; // $this->DB->connection()->enableQueryLog(); $this->Database = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Cost_Center AS c')->leftJoin('ACCT_Cost_Center AS cp', 'cp.id', '=', 'c.parent_cc_id')->where('c.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->whereNull('c.deleted_at'); $this->visibleColumns = array('c.id AS acct_ccm_id', 'c.key as acct_ccm_key', 'c.name as acct_ccm_name', 'c.is_group as acct_ccm_is_group', 'cp.id as acct_ccm_parent_cc_id', 'cp.key as acct_ccm_parent_key', 'cp.name as acct_ccm_parent_cc', $DB->raw('CASE c.is_group WHEN 1 THEN 0 ELSE 1 END AS acct_ccm_is_leaf')); $this->orderBy = array(array('acct_ccm_key', 'asc')); $this->treeGrid = true; $this->parentColumn = 'cp.id'; $this->leafColumn = 'acct_ccm_is_leaf'; }
public function __construct(DatabaseManager $DB, AuthenticationManagementInterface $AuthenticationManager, Translator $Lang) { /* $this->Database = $DB->table('ACCT_Journal_Voucher AS jv') ->join('ACCT_Voucher_Type AS vt', 'vt.id', '=', 'jv.voucher_type_id') ->where('jv.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId()) ->whereNull('jv.deleted_at'); $this->visibleColumns = array('jv.id', 'jv.number', 'jv.date', 'jv.manual_reference', 'jv.remark', 'jv.is_editable', 'jv.status', 'vt.name', 'vt.lang_key', ); */ //$DB->connection()->enableQueryLog(); // $this->DB = $DB; // // $this->DB->connection()->enableQueryLog(); $this->Database = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Journal_Voucher AS jv')->leftJoin('ACCT_Journal_Entry AS je', 'je.journal_voucher_id', '=', 'jv.id')->leftJoin('PURCH_Document_Type AS dt', 'dt.id', '=', 'jv.document_type_id')->leftJoin('PURCH_Supplier AS ps', 'ps.id', '=', 'jv.supplier_id')->leftJoin('SALE_Client AS cl', 'cl.id', '=', 'jv.client_id')->leftJoin('HR_Employee AS e', 'e.id', '=', 'jv.employee_id')->join('ACCT_Voucher_Type AS vt', 'vt.id', '=', 'jv.voucher_type_id')->join('ACCT_Period AS p', 'p.id', '=', 'jv.period_id')->leftJoin('ACCT_Cost_Center AS cc', 'cc.id', '=', 'je.cost_center_id')->leftJoin('ACCT_Account AS c', 'c.id', '=', 'je.account_id')->where('jv.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->whereNull('je.deleted_at')->whereNull('jv.deleted_at'); $this->visibleColumns = array('jv.id AS voucher_id', 'jv.number', 'jv.date', 'jv.manual_reference', 'jv.remark', 'jv.is_editable', 'jv.status', 'jv.document_date', 'jv.document_number', 'jv.document_amount', 'je.debit AS debit_0', 'je.credit AS credit_0', 'dt.id AS document_type_id', 'dt.name AS document_type_label', 'ps.id AS supplier_id', 'ps.name AS supplier_label', 'cl.id AS client_id', 'cl.name AS client_label', 'e.id AS employee_id', $DB->raw('CONCAT(e.names, \' \', e.surnames) AS employee_label'), 'vt.id AS voucher_type_id', 'vt.name AS voucher_type', 'cc.id AS cost_center_id_0', 'cc.key AS cost_center_key_0', 'cc.name AS cost_center_name_0', 'c.id AS account_id_0', 'c.key AS account_key_0', 'c.name AS account_name_0', 'p.id AS period_id', $DB->raw('CONCAT("#", LPAD(jv.number, 4, 0), " - P", LPAD(month, 2, 0), " - ", DATE_FORMAT(jv.date, "' . $Lang->get('form.mysqlDateFormat') . '"), " - ", vt.name, " - ", IFNULL(jv.manual_reference,"' . $Lang->get('decima-accounting::journal-management.noRef') . '"), " - ", jv.remark) AS voucher_header')); //var_dump($DB->raw('CONCAT(jv.date, ' - ', jv.number, ' - ', vt.name) AS voucher_header'));die(); //var_dump($DB->raw('concat(jv.date, " - ", jv.number) AS voucher_header'));die(); $this->orderBy = array(array('voucher_header', 'desc')); }
/** * @param $filters * @param null $limit * * @return \Illuminate\Database\Eloquent\Collection|static[] */ public function all($filters, $limit = null) { $query = $this->post->select('*'); $from = "posts "; if (isset($filters['post_type']) && $filters['post_type'] != '') { $post_type = $filters['post_type']; $query->whereRaw("posts.metadata->>'type' = ?", [$post_type]); } $query->from($this->db->raw($from)); $query->orderBy('updated_at', 'DESC'); $query->published(); return $query->paginate(); }
public function __construct(DatabaseManager $DB, AuthenticationManagementInterface $AuthenticationManager, Translator $Lang) { $this->DB = $DB; $this->AuthenticationManager = $AuthenticationManager; //$this->DB->connection()->enableQueryLog(); /* $this->Database = $DB->table('ACCT_Journal_Entry AS je') ->join('ACCT_Journal_Voucher AS jv', 'jv.id', '=', 'je.journal_voucher_id') ->rightJoin('ACCT_Account AS c', 'je.account_id', '=', 'c.id') ->join('ACCT_Account_Type AS at', 'at.id', '=', 'c.account_type_id') ->where(function($query) { $query->orWhere('jv.status', '=', 'B'); $query->orWhereNull('jv.status'); } ) ->where('c.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId()) ->whereIn('at.pl_bs_category', array('D', 'E')) ->whereNull('je.deleted_at') ->whereNull('jv.deleted_at'); //->groupBy('c.id', 'c.parent_account_id', 'c.key', 'c.name', 'at.name'); $this->visibleColumns = array($DB->raw('IFNULL(SUM(je.debit),0) AS acct_bs_debit'), $DB->raw('IFNULL(SUM(je.credit),0) AS acct_bs_credit'), 'c.id AS acct_bs_account_id', 'c.parent_account_id AS acct_bs_parent_account_id','c.key AS acct_bs_account_key', 'c.name AS acct_bs_account_name', 'c.is_group AS acct_bs_is_group', 'c.balance_type AS acct_bs_balance_type', $DB->raw('CASE at.pl_bs_category WHEN "D" THEN "' . $Lang->get('decima-accounting::balance-sheet.assets') . '" ELSE "' . $Lang->get('decima-accounting::balance-sheet.liability') . '" END AS acct_bs_pl_bs_category'), //$DB->raw('CASE balance_type WHEN "D" THEN IFNULL(SUM(je.debit),0) - IFNULL(SUM(je.credit),0) ELSE IFNULL(SUM(je.credit),0) - IFNULL(SUM(je.debit),0) END AS acct_bs_balance'), $DB->raw('0 AS acct_bs_balance'), //'at.name AS acct_bs_account_type_name' //$DB->raw('CONCAT(" ", DATE_FORMAT(jv.date, "%c"), " - ", DATE_FORMAT(jv.date, "' . $Lang->get('form.mysqlDateFormat') . '"), " - #", jv.number, " - ", vt.name, " - ", IFNULL(jv.manual_reference,"No Ref."), " - ", jv.remark) AS voucher_header') //$DB->raw('count(*) AS voucher_header') ); */ $this->Database = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Journal_Entry AS je')->join('ACCT_Journal_Voucher AS jv', 'jv.id', '=', 'je.journal_voucher_id')->rightJoin('ACCT_Account AS c', 'je.account_id', '=', 'c.id')->join('ACCT_Account_Type AS at', 'at.id', '=', 'c.account_type_id')->where('jv.status', '=', 'B')->where('c.is_group', '=', 0)->where('jv.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->whereIn('at.pl_bs_category', array('D', 'E'))->whereNull('je.deleted_at')->whereNull('jv.deleted_at'); //->groupBy('c.id', 'c.parent_account_id', 'c.key', 'c.name', 'at.name'); $this->visibleColumns = array($DB->raw('IFNULL(SUM(je.debit),0) AS acct_bs_debit'), $DB->raw('IFNULL(SUM(je.credit),0) AS acct_bs_credit'), 'c.id AS acct_bs_account_id', 'c.parent_account_id AS acct_bs_parent_account_id', 'c.key AS acct_bs_account_key', 'c.name AS acct_bs_account_name', 'c.is_group AS acct_bs_is_group', 'c.balance_type AS acct_bs_balance_type', $DB->raw('CASE at.pl_bs_category WHEN "D" THEN "' . $Lang->get('decima-accounting::balance-sheet.assets') . '" ELSE "' . $Lang->get('decima-accounting::balance-sheet.liability') . '" END AS acct_bs_pl_bs_category'), $DB->raw('0 AS acct_bs_balance')); $this->Database2 = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Journal_Entry AS je')->join('ACCT_Journal_Voucher AS jv', 'jv.id', '=', 'je.journal_voucher_id')->rightJoin('ACCT_Account AS c', 'je.account_id', '=', 'c.id')->join('ACCT_Account_Type AS at', 'at.id', '=', 'c.account_type_id')->where(function ($query) { $query->orWhere('jv.status', '=', 'B'); $query->orWhereNull('jv.status'); })->where('jv.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->whereIn('at.pl_bs_category', array('B'))->whereNull('je.deleted_at')->whereNull('jv.deleted_at'); $this->Database4 = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Journal_Entry AS je')->join('ACCT_Journal_Voucher AS jv', 'jv.id', '=', 'je.journal_voucher_id')->rightJoin('ACCT_Account AS c', 'je.account_id', '=', 'c.id')->join('ACCT_Account_Type AS at', 'at.id', '=', 'c.account_type_id')->where(function ($query) { $query->orWhere('jv.status', '=', 'B'); $query->orWhereNull('jv.status'); })->where('jv.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->whereIn('at.pl_bs_category', array('C'))->whereNull('je.deleted_at')->whereNull('jv.deleted_at'); $this->Database3 = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Account AS c')->join('ACCT_Account_Type AS at', 'at.id', '=', 'c.account_type_id')->where('c.is_group', '=', 1)->where('c.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->whereIn('at.pl_bs_category', array('D', 'E'))->select(array($DB->raw('0 AS acct_bs_debit'), $DB->raw('0 AS acct_bs_credit'), 'c.id AS acct_bs_account_id', 'c.parent_account_id AS acct_bs_parent_account_id', 'c.key AS acct_bs_account_key', 'c.name AS acct_bs_account_name', 'c.is_group AS acct_bs_is_group', 'c.balance_type AS acct_bs_balance_type', $DB->raw('CASE at.pl_bs_category WHEN "D" THEN "' . $Lang->get('decima-accounting::balance-sheet.assets') . '" ELSE "' . $Lang->get('decima-accounting::balance-sheet.liability') . '" END AS acct_bs_pl_bs_category'), $DB->raw('0 AS acct_bs_balance'))); $this->orderBy = array(array('acct_bs_account_key', 'asc')); }
/** * Filters a relationship options query by a search term * * @param mixed $term * @param \Illuminate\Database\Query\Builder $query * @param array $selectedItems * @param \Frozennode\Administrator\Fields\Field $fieldObject * @param string $relatedKeyTable */ public function filterBySearchTerm($term, EloquentBuilder &$query, Field $fieldObject, array $selectedItems, $relatedKeyTable) { if ($term) { //set up the wheres foreach ($fieldObject->getOption('search_fields') as $search) { $query->where($this->db->raw($search), 'LIKE', '%' . $term . '%'); } //exclude the currently-selected items if there are any if (count($selectedItems)) { $query->whereNotIn($relatedKeyTable, $selectedItems); } //set up the limits $query->take($fieldObject->getOption('num_options') + count($selectedItems)); } }
/** * Get country all users * * @return Collection/null */ public function getUsersWithCountryContract() { $query = $this->user->select('name', 'id'); $countries = $this->auth->user()->country; $from = "users"; $from .= ",json_array_elements(users.country) r"; if (!is_null($countries)) { $query->whereRaw("trim(both '\"' from r::text) in (?)", $countries); } $query->from($this->db->raw($from)); $list = []; foreach ($query->get() as $v) { $list[$v->id] = $v->name; } return $list; }
/** * Return the admin categories view. * * @return \Illuminate\View\View */ public function showCategoriesTable(DatabaseManager $database) { $categories = Category::query()->leftJoin('items')->addSelect($database->raw('COUNT(items.item_id) as item_count'))->groupBy('categories.category_id'); $table = new AdminCategories($categories); $table->with('parent'); return view('mustard::admin.categories', ['table' => $table, 'categories' => $table->paginate()]); }
/** * get number Notifications * not read. * * @param $toId * @param $entity * @param Closure $filterScope * @return mixed */ public function countNotRead($toId, $entity, Closure $filterScope = null) { $query = $this->notification->wherePolymorphic($toId, $entity)->withNotRead()->select($this->db->raw('Count(*) as notRead')); $query = $this->applyFilter($filterScope, $query); return $query->count(); }
/** * @param array $followed * @return mixed */ public function countFollowing(array $followed) { return $this->follow->select($this->db->raw('Count(*) as numbers_followers'))->where('follower_type', $followed['follower_type'])->where('follower_id', $followed['follower_id'])->first(); }
public static function getRows($db, $filters = null, $page = 1, $sort = null) { //grab the model instance $model = $config->model; //update the config sort options $config->setSort($sort); $sort = $config->sort; //get things going by grouping the set $query = $model::group_by($model->table() . '.' . $model::$key); //set up initial array states for the selects //************* // the problem originated here // according to PostgreSQL if you want to select something you have to group your result by it (only if you using gruop by statement) // you can either get id and then get the rest of fields using id or you can put all columns of table in group_by statement // I chose the first approach // so i'm selecting just the id and then i will get the rest of row using id $selects = array(DB::raw($model->table() . '.' . $model::$key)); //************** //then we set the filters if ($filters && is_array($filters)) { foreach ($filters as $filter) { if (!($fieldObject = Field::get($filter['field'], $filter, $config))) { continue; } $fieldObject->filterQuery($query, $model); } } //determines if the sort should have the table prefixed to it $sortOnTable = true; //iterate over the columns to check if we need to join any values or add any extra columns foreach ($config->columns['columns'] as $field => $column) { //if this is a related column, we'll need to add some joins $column->filterQuery($query, $selects, $model); //if this is a related field or if (($column->isRelated || $column->select) && $column->field === $sort['field']) { $sortOnTable = false; } } //if the sort is on the model's table, prefix the table name to it if ($sortOnTable) { $sort['field'] = $model->table() . '.' . $sort['field']; } /** * We need to do our own pagination since there is a bug in the L3 paginator when using groupings :( * When L4 is released, this problem will go away and we'll be able to use the paginator again */ //first get the sql sans selects $sql = $query->table->grammar->select($query->table); //then we need to round out the inner select $sql = "SELECT {$model->table()}.{$model::$key} " . $sql; //then wrap the inner table and perform the count $sql = "SELECT COUNT({$model::$key}) AS aggregate FROM ({$sql}) AS agg"; //then perform the count query $results = $query->table->connection->query($sql, $query->table->bindings); $num_rows = $results[0]->aggregate; $page = (int) \Input::get('page', 1); $last = (int) ceil($num_rows / $config->rowsPerPage); //if the current page is greater than the last page, set the current page to the last page $page = $page > $last ? $last : $page; //now we need to limit and offset the rows in remembrance of our dear lost friend paginate() $query->take($config->rowsPerPage); $query->skip($config->rowsPerPage * ($page === 0 ? $page : $page - 1)); //order the set by the model table's id $query->order_by($sort['field'], $sort['direction']); //then retrieve the rows $rows = $query->distinct()->get($selects); //********* // I also added this // here i got the rest of fields // I know you probebly asking why i didn't just join the query with table and be done with that // But that way it whoud not match the rest of code and I didn't want to mess with the rest of code for ($i = 0; $i < count($rows); $i++) { $rows[$i] = $model::find($rows[$i]->key); } //********* $results = array(); //convert the resulting set into arrays foreach ($rows as $item) { //iterate over the included and related columns $onTableColumns = array_merge($config->columns['includedColumns'], $config->columns['relatedColumns']); $arr = array(); foreach ($onTableColumns as $field => $col) { //if this column is in our objects array, render the output with the given value if (isset($config->columns['columnObjects'][$field])) { $arr[$field] = $config->columns['columnObjects'][$field]->renderOutput($item->get_attribute($field)); } else { $arr[$field] = $item->get_attribute($field); } } //then grab the computed, unsortable columns foreach ($config->columns['computedColumns'] as $col) { $arr[$col] = $config->columns['columnObjects'][$col]->renderOutput($item->{$col}); } $results[] = $arr; } return array('page' => $page, 'last' => $last, 'total' => $num_rows, 'results' => $results); }
/** * Get conversation archived and not * of the given partecipant * * @param $conversation_id * @param $from * @return mixed */ public function conversationReadable($conversation_id, $from) { return $this->conversation->whereNotExists(function ($query) use($from, $conversation_id) { $query->select($this->db->raw($from))->from('deleted_conversations')->whereRaw('deleted_conversations.conversation_id = conversations.id')->where('deleted_conversations.participant_id', $from)->where('deleted_conversations.archived', '=', 1); })->find($conversation_id); }
/** * Get the count of presence of contract's metadatas * * @param $metadata * @return collection */ public function getMetadataQuality($metadata) { $from = "contracts "; $result = $this->contract->whereRaw(sprintf("contracts.metadata->>'%s'!=''", $metadata))->from($this->db->raw($from))->count(); return $result; }
/** * Get result of Full text search * @param $contract_id * @param $query * @return array */ public function fullTextSearch($contract_id, $query) { return $this->pages->select($this->db->raw("contract_id, page_no, ts_headline(text, plainto_tsquery('" . $query . "')) as text"))->whereRaw("to_tsvector(text) @@ plainto_tsquery('" . $query . "')")->orderBy('page_no', 'ASC')->where('contract_id', $contract_id)->get()->toArray(); }
public function __construct(DatabaseManager $DB, AuthenticationManagementInterface $AuthenticationManager, AccountManagementInterface $AccountManager, Translator $Lang, Carbon $Carbon) { $this->DB = $DB; $this->AccountManager = $AccountManager; $this->AuthenticationManager = $AuthenticationManager; $this->Carbon = $Carbon; $this->Database = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Journal_Entry AS je')->join('ACCT_Journal_Voucher AS jv', 'jv.id', '=', 'je.journal_voucher_id')->join('ACCT_Account AS c', 'je.account_id', '=', 'c.id')->where('jv.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->where('c.is_group', '=', 0)->whereNull('je.deleted_at')->whereNull('jv.deleted_at')->select(array($DB->raw('IFNULL(SUM(je.debit),0) AS acct_gl_debit'), $DB->raw('IFNULL(SUM(je.credit),0) AS acct_gl_credit'), 'c.id AS acct_gl_account_id', 'c.parent_account_id AS acct_gl_parent_account_id', 'c.key AS acct_gl_account_key', 'c.name AS acct_gl_account_name', 'c.is_group AS acct_gl_is_group', 'c.balance_type AS acct_gl_balance_type', $DB->raw('0 AS acct_gl_total_debit'), $DB->raw('0 AS acct_gl_total_credit'), $DB->raw('0 AS acct_gl_opening_balance'), $DB->raw('0 AS acct_gl_closing_balance'), $DB->raw('\'\' AS acct_gl_voucher_date'), $DB->raw('\'\' AS acct_gl_voucher_type'), $DB->raw('\'\' AS acct_gl_voucher_number'))); $this->Database2 = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Account AS c')->where('c.is_group', '=', 1)->where('c.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->select(array($DB->raw('0 AS acct_gl_debit'), $DB->raw('0 AS acct_gl_credit'), 'c.id AS acct_gl_account_id', 'c.parent_account_id AS acct_gl_parent_account_id', 'c.key AS acct_gl_account_key', 'c.name AS acct_gl_account_name', 'c.is_group AS acct_gl_is_group', 'c.balance_type AS acct_gl_balance_type', $DB->raw('0 AS acct_gl_total_debit'), $DB->raw('0 AS acct_gl_total_credit'), $DB->raw('0 AS acct_gl_opening_balance'), $DB->raw('0 AS acct_gl_closing_balance'), $DB->raw('\'\' AS acct_gl_voucher_date'), $DB->raw('\'\' AS acct_gl_voucher_type'), $DB->raw('\'\' AS acct_gl_voucher_number'))); $this->Database3 = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Journal_Entry AS je')->join('ACCT_Journal_Voucher AS jv', 'jv.id', '=', 'je.journal_voucher_id')->join('ACCT_Account AS c', 'je.account_id', '=', 'c.id')->where('jv.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->where('c.is_group', '=', 0)->whereNull('je.deleted_at')->whereNull('jv.deleted_at')->select(array($DB->raw('0 AS acct_gl_debit'), $DB->raw('0 AS acct_gl_credit'), 'c.id AS acct_gl_account_id', 'c.parent_account_id AS acct_gl_parent_account_id', 'c.key AS acct_gl_account_key', 'c.name AS acct_gl_account_name', 'c.is_group AS acct_gl_is_group', 'c.balance_type AS acct_gl_balance_type', $DB->raw('IFNULL(SUM(je.debit),0) AS acct_gl_total_credit'), $DB->raw('IFNULL(SUM(je.credit),0) AS acct_gl_total_credit'), $DB->raw('0 AS acct_gl_opening_balance'), $DB->raw('0 AS acct_gl_closing_balance'), $DB->raw('\'\' AS acct_gl_voucher_date'), $DB->raw('\'\' AS acct_gl_voucher_type'), $DB->raw('\'\' AS acct_gl_voucher_number'))); $this->Database4 = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Journal_Entry AS je')->join('ACCT_Journal_Voucher AS jv', 'jv.id', '=', 'je.journal_voucher_id')->join('ACCT_Voucher_Type AS vt', 'vt.id', '=', 'jv.voucher_type_id')->join('ACCT_Account AS c', 'je.account_id', '=', 'c.id')->where('jv.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->where('c.is_group', '=', 0)->whereNull('je.deleted_at')->whereNull('jv.deleted_at')->select(array($DB->raw('je.debit AS acct_gl_debit'), $DB->raw('je.credit AS acct_gl_credit'), 'c.id AS acct_gl_account_id', 'c.parent_account_id AS acct_gl_parent_account_id', $DB->raw('IFNULL(jv.manual_reference,"' . $Lang->get('decima-accounting::journal-management.noRef') . '") AS acct_gl_account_key'), 'jv.remark AS acct_gl_account_name', 'c.is_group AS acct_gl_is_group', 'c.balance_type AS acct_gl_balance_type', $DB->raw('0 AS acct_gl_total_debit'), $DB->raw('0 AS acct_gl_total_credit'), $DB->raw('0 AS acct_gl_opening_balance'), $DB->raw('0 AS acct_gl_closing_balance'), $DB->raw('DATE_FORMAT(jv.date, "' . $Lang->get('form.mysqlDateFormat') . '") AS acct_gl_voucher_date'), $DB->raw('vt.name AS acct_gl_voucher_type'), $DB->raw('jv.number AS acct_gl_voucher_number'))); $this->orderBy = array(array('acct_gl_account_key', 'asc')); }
public function __construct(DatabaseManager $DB, AuthenticationManagementInterface $AuthenticationManager, Translator $Lang, Carbon $Carbon) { $this->DB = $DB; $this->AuthenticationManager = $AuthenticationManager; $this->Carbon = $Carbon; $this->Database = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Journal_Entry AS je')->join('ACCT_Journal_Voucher AS jv', 'jv.id', '=', 'je.journal_voucher_id')->join('ACCT_Account AS c', 'je.account_id', '=', 'c.id')->where('jv.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->where('c.is_group', '=', 0)->whereNull('je.deleted_at')->whereNull('jv.deleted_at')->select(array($DB->raw('IFNULL(SUM(je.debit),0) AS acct_tb_debit'), $DB->raw('IFNULL(SUM(je.credit),0) AS acct_tb_credit'), 'c.id AS acct_tb_account_id', 'c.parent_account_id AS acct_tb_parent_account_id', 'c.key AS acct_tb_account_key', 'c.name AS acct_tb_account_name', 'c.is_group AS acct_tb_is_group', 'c.balance_type AS acct_tb_balance_type', $DB->raw('0 AS acct_tb_total_debit'), $DB->raw('0 AS acct_tb_total_credit'), $DB->raw('0 AS acct_tb_opening_balance'), $DB->raw('0 AS acct_tb_closing_balance'))); $this->Database2 = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Account AS c')->where('c.is_group', '=', 1)->where('c.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->select(array($DB->raw('0 AS acct_tb_debit'), $DB->raw('0 AS acct_tb_credit'), 'c.id AS acct_tb_account_id', 'c.parent_account_id AS acct_tb_parent_account_id', 'c.key AS acct_tb_account_key', 'c.name AS acct_tb_account_name', 'c.is_group AS acct_tb_is_group', 'c.balance_type AS acct_tb_balance_type', $DB->raw('0 AS acct_tb_total_debit'), $DB->raw('0 AS acct_tb_total_credit'), $DB->raw('0 AS acct_tb_opening_balance'), $DB->raw('0 AS acct_tb_closing_balance'))); $this->Database3 = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Journal_Entry AS je')->join('ACCT_Journal_Voucher AS jv', 'jv.id', '=', 'je.journal_voucher_id')->join('ACCT_Account AS c', 'je.account_id', '=', 'c.id')->where('jv.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->where('c.is_group', '=', 0)->whereNull('je.deleted_at')->whereNull('jv.deleted_at')->select(array($DB->raw('0 AS acct_tb_debit'), $DB->raw('0 AS acct_tb_credit'), 'c.id AS acct_tb_account_id', 'c.parent_account_id AS acct_tb_parent_account_id', 'c.key AS acct_tb_account_key', 'c.name AS acct_tb_account_name', 'c.is_group AS acct_tb_is_group', 'c.balance_type AS acct_tb_balance_type', $DB->raw('IFNULL(SUM(je.debit),0) acct_tb_total_debit'), $DB->raw('IFNULL(SUM(je.credit),0) AS acct_tb_total_credit'), $DB->raw('0 AS acct_tb_opening_balance'), $DB->raw('0 AS acct_tb_closing_balance'))); // $this->visibleColumns = array($DB->raw('IFNULL(SUM(je.debit),0) AS acct_tb_debit'), $DB->raw('IFNULL(SUM(je.credit),0) AS acct_tb_credit'), // 'c.id AS acct_tb_account_id', 'c.parent_account_id AS acct_tb_parent_account_id','c.key AS acct_tb_account_key', 'c.name AS acct_tb_account_name', 'c.is_group AS acct_tb_is_group', 'c.balance_type AS acct_tb_balance_type', // $DB->raw('0 AS acct_tb_total_debit'), $DB->raw('0 AS acct_tb_total_credit'), $DB->raw('0 AS acct_tb_opening_balance'), $DB->raw('0 AS acct_tb_closing_balance'), // ); $this->orderBy = array(array('acct_tb_account_key', 'asc')); }