/** * Get the rows data to be shown in the grid. * * @param integer $limit * Number of rows to be shown into the grid * @param integer $offset * Start position * @param string $orderBy * Column name to order by. * @param array $sordvisibleColumns * Sorting order * @param array $filters * An array of filters, example: array(array('field'=>'column index/name 1','op'=>'operator','data'=>'searched string column 1'), array('field'=>'column index/name 2','op'=>'operator','data'=>'searched string column 2')) * The 'field' key will contain the 'index' column property if is set, otherwise the 'name' column property. * The 'op' key will contain one of the following operators: '=', '<', '>', '<=', '>=', '<>', '!=','like', 'not like', 'is in', 'is not in'. * when the 'operator' is 'like' the 'data' already contains the '%' character in the appropiate position. * The 'data' key will contain the string searched by the user. * @return array * An array of array, each array will have the data of a row. * Example: array(array("column1" => "1-1", "column2" => "1-2"), array("column1" => "2-1", "column2" => "2-2")) */ public function getRows($limit, $offset, $orderBy = null, $sord = null, array $filters = array(), $nodeId = null, $nodeLevel = null, $exporting) { $orderByRaw = null; if (!is_null($orderBy) || !is_null($sord)) { $found = false; $pos = strpos($orderBy, 'desc'); if ($pos !== false) { $found = true; } else { $pos = strpos($orderBy, 'asc'); if ($pos !== false) { $found = true; } } if ($found) { $orderBy = rtrim($orderBy); if (substr($orderBy, -1) == ',') { $orderBy = substr($orderBy, 0, -1); } else { $orderBy .= " {$sord}"; } $orderByRaw = $orderBy; } else { $this->orderBy = array(array($orderBy, $sord)); } } if ($limit == 0) { $limit = 1; } if (empty($orderByRaw)) { $orderByRaw = array(); foreach ($this->orderBy as $orderBy) { array_push($orderByRaw, implode(' ', $orderBy)); } $orderByRaw = implode(',', $orderByRaw); } $accountChildrenIds = array(); $query3 = $this->Database3->whereNested(function ($query) use($filters, &$accountChildrenIds) { foreach ($filters as $filter) { if ($filter['field'] == 'je.account_id') { $accountChildrenIds = $this->AccountManager->getAccountChildrenIds($filter['data']); array_push($accountChildrenIds, $filter['data']); $query->whereIn($filter['field'], $accountChildrenIds); continue; } if ($filter['op'] == 'is in') { $query->whereIn($filter['field'], explode(',', $filter['data'])); continue; } if ($filter['op'] == 'is not in') { $query->whereNotIn($filter['field'], explode(',', $filter['data'])); continue; } if ($filter['field'] == 'jv.date' && $filter['op'] == '<=') { continue; } if ($filter['field'] == 'auxiliary') { continue; } if ($filter['field'] == 'jv.date' && $filter['op'] == '>=') { $query->where($filter['field'], '<', $filter['data']); $Date = new Carbon('first day of January ' . $this->Carbon->createFromFormat('Y-m-d', $filter['data'])->year); $query->where($filter['field'], '>=', $Date->format('Y-m-d')); } else { $query->where($filter['field'], $filter['op'], $filter['data']); } } })->groupBy('c.id', 'c.parent_account_id', 'c.key', 'c.name', 'c.is_group', 'c.balance_type'); // var_dump($accountChildrenIds);die(); // var_dump($query3->toSql(), $query3->getBindings());die(); $query = $this->Database->whereNested(function ($query) use($filters, &$auxiliary, $accountChildrenIds) { foreach ($filters as $filter) { if ($filter['field'] == 'je.account_id') { $query->whereIn($filter['field'], $accountChildrenIds); continue; } if ($filter['op'] == 'is in') { $query->whereIn($filter['field'], explode(',', $filter['data'])); continue; } if ($filter['op'] == 'is not in') { $query->whereNotIn($filter['field'], explode(',', $filter['data'])); continue; } if ($filter['field'] == 'auxiliary') { $auxiliary = $filter['data']; continue; } $query->where($filter['field'], $filter['op'], $filter['data']); } })->union($this->Database2)->union($query3)->groupBy('c.id', 'c.parent_account_id', 'c.key', 'c.name', 'c.is_group', 'c.balance_type'); // var_dump($query->get());die(); // var_dump($query->toSql(), $query->getBindings());die(); $querySql = $query->toSql(); $groupBy = "acct_gl_voucher_date, acct_gl_voucher_type, acct_gl_voucher_number, acct_gl_opening_balance, acct_gl_closing_balance, acct_gl_account_id, acct_gl_parent_account_id, acct_gl_account_key, acct_gl_account_name, acct_gl_is_group, acct_gl_balance_type"; $select = "SUM(acct_gl_debit) AS acct_gl_debit, SUM(acct_gl_credit) AS acct_gl_credit, SUM(acct_gl_total_debit) AS acct_gl_total_debit, SUM(acct_gl_total_credit) AS acct_gl_total_credit, {$groupBy}"; // var_dump("SELECT $select FROM ($querySql) AS A GROUP BY $groupBy ORDER BY $orderByRaw"); $rows = $this->DB->connection($this->AuthenticationManager->getCurrentUserOrganizationConnection())->select("SELECT {$select} FROM ({$querySql}) AS A GROUP BY {$groupBy} ORDER BY {$orderByRaw}", $query->getBindings()); if (!is_array($rows)) { $rows = $rows->toArray(); } foreach ($rows as &$row) { $row = (array) $row; } if (!empty($auxiliary)) { $detailRows = $this->Database4->whereNested(function ($query) use($filters, $accountChildrenIds) { foreach ($filters as $filter) { if ($filter['field'] == 'je.account_id') { $query->whereIn($filter['field'], $accountChildrenIds); continue; } if ($filter['op'] == 'is in') { $query->whereIn($filter['field'], explode(',', $filter['data'])); continue; } if ($filter['op'] == 'is not in') { $query->whereNotIn($filter['field'], explode(',', $filter['data'])); continue; } if ($filter['field'] == 'auxiliary') { continue; } $query->where($filter['field'], $filter['op'], $filter['data']); } })->orderByRaw('jv.date')->get(); if (!is_array($detailRows)) { $detailRows = $detailRows->toArray(); } } // var_dump($rows);die(); // var_dump($detailRows);die(); $headerRows = account_balance_update($rows, 'acct_gl_'); // var_dump($headerRows);die(); $newRows = array(); if (empty($auxiliary)) { if (count($accountChildrenIds) > 0) { foreach ($headerRows as $headerRowKey => $headerRow) { if (in_array($headerRow['acct_gl_account_id'], $accountChildrenIds)) { array_push($newRows, $headerRow); } } return $newRows; } else { return $headerRows; } } foreach ($headerRows as $headerRowKey => $headerRow) { if (count($accountChildrenIds) > 0) { if (in_array($headerRow['acct_gl_account_id'], $accountChildrenIds)) { array_push($newRows, $headerRow); } } else { array_push($newRows, $headerRow); } if (empty($headerRow['acct_gl_is_group'])) { $count = 0; foreach ($detailRows as $keyDetailRow => $detailRow) { if ($detailRow->acct_gl_account_id == $headerRow['acct_gl_account_id']) { $count++; if ($count == 1) { $detailRow->acct_gl_opening_balance = $headerRow['acct_gl_opening_balance']; } else { $detailRow->acct_gl_opening_balance = $closingBalance; } if ($detailRow->acct_gl_balance_type == 'D') { $detailRow->acct_gl_closing_balance = $detailRow->acct_gl_opening_balance + $detailRow->acct_gl_debit - $detailRow->acct_gl_credit; } else { $detailRow->acct_gl_closing_balance = $detailRow->acct_gl_opening_balance - $detailRow->acct_gl_debit + $detailRow->acct_gl_credit; } $closingBalance = $detailRow->acct_gl_closing_balance; array_push($newRows, (array) $detailRow); } } } } return $newRows; }
/** * Get the rows data to be shown in the grid. * * @param integer $limit * Number of rows to be shown into the grid * @param integer $offset * Start position * @param string $orderBy * Column name to order by. * @param array $sordvisibleColumns * Sorting order * @param array $filters * An array of filters, example: array(array('field'=>'column index/name 1','op'=>'operator','data'=>'searched string column 1'), array('field'=>'column index/name 2','op'=>'operator','data'=>'searched string column 2')) * The 'field' key will contain the 'index' column property if is set, otherwise the 'name' column property. * The 'op' key will contain one of the following operators: '=', '<', '>', '<=', '>=', '<>', '!=','like', 'not like', 'is in', 'is not in'. * when the 'operator' is 'like' the 'data' already contains the '%' character in the appropiate position. * The 'data' key will contain the string searched by the user. * @return array * An array of array, each array will have the data of a row. * Example: array(array("column1" => "1-1", "column2" => "1-2"), array("column1" => "2-1", "column2" => "2-2")) */ public function getRows($limit, $offset, $orderBy = null, $sord = null, array $filters = array(), $nodeId = null, $nodeLevel = null, $exporting) { $orderByRaw = null; if (!is_null($orderBy) || !is_null($sord)) { $found = false; $pos = strpos($orderBy, 'desc'); if ($pos !== false) { $found = true; } else { $pos = strpos($orderBy, 'asc'); if ($pos !== false) { $found = true; } } if ($found) { $orderBy = rtrim($orderBy); if (substr($orderBy, -1) == ',') { $orderBy = substr($orderBy, 0, -1); } else { $orderBy .= " {$sord}"; } $orderByRaw = $orderBy; } else { $this->orderBy = array(array($orderBy, $sord)); } } if ($limit == 0) { $limit = 1; } if (empty($orderByRaw)) { $orderByRaw = array(); foreach ($this->orderBy as $orderBy) { array_push($orderByRaw, implode(' ', $orderBy)); } $orderByRaw = implode(',', $orderByRaw); } $query = $this->Database->whereNested(function ($query) use($filters) { foreach ($filters as $filter) { if ($filter['op'] == 'is in') { $query->whereIn($filter['field'], explode(',', $filter['data'])); continue; } if ($filter['op'] == 'is not in') { $query->whereNotIn($filter['field'], explode(',', $filter['data'])); continue; } $query->where($filter['field'], $filter['op'], $filter['data']); } })->orderByRaw($orderByRaw)->union($this->Database3)->groupBy('c.id', 'c.parent_account_id', 'c.key', 'c.name', 'c.is_group', 'c.balance_type')->select($this->visibleColumns); $querySql = $query->toSql(); $rows = $this->DB->connection($this->AuthenticationManager->getCurrentUserOrganizationConnection())->select($querySql . ' ORDER BY ' . $orderByRaw, $query->getBindings()); $Income = $this->Database2->whereNested(function ($query) use($filters) { foreach ($filters as $filter) { if ($filter['op'] == 'is in') { $query->whereIn($filter['field'], explode(',', $filter['data'])); continue; } if ($filter['op'] == 'is not in') { $query->whereNotIn($filter['field'], explode(',', $filter['data'])); continue; } $query->where($filter['field'], $filter['op'], $filter['data']); } })->select(array($this->DB->raw('IFNULL(SUM(je.credit),0) - IFNULL(SUM(je.debit),0) AS result')))->first(); $Expenses = $this->Database4->whereNested(function ($query) use($filters) { foreach ($filters as $filter) { if ($filter['op'] == 'is in') { $query->whereIn($filter['field'], explode(',', $filter['data'])); continue; } if ($filter['op'] == 'is not in') { $query->whereNotIn($filter['field'], explode(',', $filter['data'])); continue; } $query->where($filter['field'], $filter['op'], $filter['data']); } })->select(array($this->DB->raw('IFNULL(SUM(je.debit),0) - IFNULL(SUM(je.credit),0) AS result')))->first(); if (!is_array($rows)) { $rows = $rows->toArray(); } foreach ($rows as &$row) { $row = (array) $row; } $newRows = account_balance_update($rows, 'acct_bs_', false, $Income->result - $Expenses->result); return $newRows; }
/** * Get the rows data to be shown in the grid. * * @param integer $limit * Number of rows to be shown into the grid * @param integer $offset * Start position * @param string $orderBy * Column name to order by. * @param array $sordvisibleColumns * Sorting order * @param array $filters * An array of filters, example: array(array('field'=>'column index/name 1','op'=>'operator','data'=>'searched string column 1'), array('field'=>'column index/name 2','op'=>'operator','data'=>'searched string column 2')) * The 'field' key will contain the 'index' column property if is set, otherwise the 'name' column property. * The 'op' key will contain one of the following operators: '=', '<', '>', '<=', '>=', '<>', '!=','like', 'not like', 'is in', 'is not in'. * when the 'operator' is 'like' the 'data' already contains the '%' character in the appropiate position. * The 'data' key will contain the string searched by the user. * @return array * An array of array, each array will have the data of a row. * Example: array(array("column1" => "1-1", "column2" => "1-2"), array("column1" => "2-1", "column2" => "2-2")) */ public function getRows($limit, $offset, $orderBy = null, $sord = null, array $filters = array(), $nodeId = null, $nodeLevel = null, $exporting) { $orderByRaw = null; if (!is_null($orderBy) || !is_null($sord)) { $found = false; $pos = strpos($orderBy, 'desc'); if ($pos !== false) { $found = true; } else { $pos = strpos($orderBy, 'asc'); if ($pos !== false) { $found = true; } } if ($found) { $orderBy = rtrim($orderBy); if (substr($orderBy, -1) == ',') { $orderBy = substr($orderBy, 0, -1); } else { $orderBy .= " {$sord}"; } $orderByRaw = $orderBy; } else { $this->orderBy = array(array($orderBy, $sord)); } } if ($limit == 0) { $limit = 1; } if (empty($orderByRaw)) { $orderByRaw = array(); foreach ($this->orderBy as $orderBy) { array_push($orderByRaw, implode(' ', $orderBy)); } $orderByRaw = implode(',', $orderByRaw); } $query2 = $this->Database3->whereNested(function ($query) use($filters) { foreach ($filters as $filter) { if ($filter['op'] == 'is in') { $query->whereIn($filter['field'], explode(',', $filter['data'])); continue; } if ($filter['op'] == 'is not in') { $query->whereNotIn($filter['field'], explode(',', $filter['data'])); continue; } if ($filter['field'] == 'jv.date' && $filter['op'] == '<=') { continue; } if ($filter['field'] == 'auxiliary') { continue; } if ($filter['field'] == 'jv.date' && $filter['op'] == '>=') { $query->where($filter['field'], '<', $filter['data']); $Date = new Carbon('first day of January ' . $this->Carbon->createFromFormat('Y-m-d', $filter['data'])->year); $query->where($filter['field'], '>=', $Date->format('Y-m-d')); } else { $query->where($filter['field'], $filter['op'], $filter['data']); } } })->groupBy('c.id', 'c.parent_account_id', 'c.key', 'c.name', 'c.is_group', 'c.balance_type'); $query = $this->Database->whereNested(function ($query) use($filters) { foreach ($filters as $filter) { if ($filter['op'] == 'is in') { $query->whereIn($filter['field'], explode(',', $filter['data'])); continue; } if ($filter['op'] == 'is not in') { $query->whereNotIn($filter['field'], explode(',', $filter['data'])); continue; } $query->where($filter['field'], $filter['op'], $filter['data']); } })->union($this->Database2)->union($query2)->groupBy('c.id', 'c.parent_account_id', 'c.key', 'c.name', 'c.is_group', 'c.balance_type'); $querySql = $query->toSql(); $groupBy = "acct_tb_opening_balance, acct_tb_closing_balance, acct_tb_account_id, acct_tb_parent_account_id, acct_tb_account_key, acct_tb_account_name, acct_tb_is_group, acct_tb_balance_type"; $select = "SUM(acct_tb_debit) AS acct_tb_debit, SUM(acct_tb_credit) AS acct_tb_credit, SUM(acct_tb_total_debit) AS acct_tb_total_debit, SUM(acct_tb_total_credit) AS acct_tb_total_credit, {$groupBy}"; $rows = $this->DB->connection($this->AuthenticationManager->getCurrentUserOrganizationConnection())->select("SELECT {$select} FROM ({$querySql}) AS A GROUP BY {$groupBy} ORDER BY {$orderByRaw}", $query->getBindings()); if (!is_array($rows)) { $rows = $rows->toArray(); } foreach ($rows as &$row) { $row = (array) $row; } $newRows = account_balance_update($rows, 'acct_tb_'); return $newRows; }