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'));
}
public function __construct(DatabaseManager $DB, AuthenticationManagementInterface $AuthenticationManager, Translator $Lang)
{
// $this->DB = $DB;
// $this->DB->connection()->enableQueryLog();
$this->Database = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Period AS p')->join('ACCT_Fiscal_Year AS f', 'f.id', '=', 'p.fiscal_year_id')->where('p.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->whereNull('p.deleted_at');
$this->visibleColumns = array('p.id AS acct_pm_id', 'p.month as acct_pm_month', 'p.start_date as acct_pm_start_date', 'p.end_date as acct_pm_end_date', 'p.is_closed as acct_pm_is_closed', 'f.year as acct_pm_year');
$this->orderBy = array(array('acct_pm_year', 'desc'), array('acct_pm_month', 'asc'));
}
public function __construct(DatabaseManager $DB, AuthenticationManagementInterface $AuthenticationManager)
{
// $this->DB = $DB;
// $this->DB->connection()->enableQueryLog();
$this->Database = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Fiscal_Year AS f')->where('f.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->whereNull('f.deleted_at');
$this->visibleColumns = array('f.id AS acct_cfy_id', 'f.year AS acct_cfy_year', 'f.start_date AS acct_cfy_start_date', 'f.end_date AS acct_cfy_end_date');
$this->orderBy = array(array('acct_cfy_id', 'asc'));
}
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'));
}
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';
}
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)
{
// $this->DB = $DB;
// $this->DB->connection()->enableQueryLog();
$this->Database = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Voucher_Type AS vt');
//->leftJoin('MODULE_Table1 AS t1p', 't1.id', '=', 't1p.parent_id')
//->join('MODULE_Table2 AS t2', 't2.id', '=', 't1.table2_id')
//->where('t.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())
//->whereNull('t.deleted_at');
$this->visibleColumns = array('vt.id AS acct_vtm_id', 'vt.name AS acct_vtm_name');
$this->orderBy = array(array('vt.id', 'asc'));
// $this->treeGrid = true;
// $this->parentColumn = 'parent_id';
// $this->leafColumn = 'is_leaf';
}
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)
{
$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'));
}
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'));
}
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'));
}
public function __construct(DatabaseManager $DB, AuthenticationManagementInterface $AuthenticationManager)
{
$this->Database = $DB->connection($AuthenticationManager->getCurrentUserOrganizationConnection())->table('ACCT_Journal_Entry AS je')->join('ACCT_Cost_Center AS cc', 'cc.id', '=', 'je.cost_center_id')->join('ACCT_Account AS c', 'c.id', '=', 'je.account_id')->where('c.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->where('cc.organization_id', '=', $AuthenticationManager->getCurrentUserOrganizationId())->whereNull('je.deleted_at');
$this->visibleColumns = array('je.id AS journal_entry_id', 'je.debit', 'je.credit', 'je.journal_voucher_id', 'cc.id AS cost_center_id', 'cc.key AS cost_center_key', 'cc.name AS cost_center_name', 'c.id AS account_id', 'c.key AS account_key', 'c.name AS account_name');
$this->orderBy = array(array('je.id', 'asc'));
}