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