X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=gl%2Finquiry%2Fgl_trial_balance.php;h=0ba2443fa876f9535a0492ded41bd34295ff9085;hb=73ab8fd6613a3632544a8d84819f0335a15dc219;hp=e94bd3c3db6775b157abe4860069139d8665f3aa;hpb=818719f38b8327cdca616d58b13913dbd174d96a;p=fa-stable.git diff --git a/gl/inquiry/gl_trial_balance.php b/gl/inquiry/gl_trial_balance.php index e94bd3c3..0ba2443f 100644 --- a/gl/inquiry/gl_trial_balance.php +++ b/gl/inquiry/gl_trial_balance.php @@ -1,30 +1,34 @@ . + See the License here . ***********************************************************************/ -$page_security = 8; +$page_security = 'SA_GLANALYTIC'; $path_to_root="../.."; include_once($path_to_root . "/includes/session.inc"); -include_once($path_to_root . "/includes/date_functions.inc"); include_once($path_to_root . "/includes/ui.inc"); +include_once($path_to_root . "/includes/date_functions.inc"); +include_once($path_to_root . "/admin/db/fiscalyears_db.inc"); include_once($path_to_root . "/includes/data_checks.inc"); include_once($path_to_root . "/gl/includes/gl_db.inc"); $js = ""; -if ($use_date_picker) +if (user_use_date_picker()) $js = get_js_date_picker(); -page(_("Trial Balance"), false, false, "", $js); +page(_($help_context = "Trial Balance"), false, false, "", $js); + +$k = 0; +$pdeb = $pcre = $cdeb = $ccre = $tdeb = $tcre = $pbal = $cbal = $tbal = 0; //---------------------------------------------------------------------------------------------------- // Ajax updates @@ -37,91 +41,77 @@ if (get_post('Show')) function gl_inquiry_controls() { + $dim = get_company_pref('use_dimension'); start_form(); - start_table("class='tablestyle_noborder'"); + start_table(TABLESTYLE_NOBORDER); - date_cells(_("From:"), 'TransFromDate', '', null, -30); + $date = today(); + if (!isset($_POST['TransToDate'])) + $_POST['TransToDate'] = end_month($date); + if (!isset($_POST['TransFromDate'])) + $_POST['TransFromDate'] = add_days(end_month($date), -user_transaction_days()); + date_cells(_("From:"), 'TransFromDate'); date_cells(_("To:"), 'TransToDate'); + if ($dim >= 1) + dimensions_list_cells(_("Dimension")." 1:", 'Dimension', null, true, " ", false, 1); + if ($dim > 1) + dimensions_list_cells(_("Dimension")." 2:", 'Dimension2', null, true, " ", false, 2); check_cells(_("No zero values"), 'NoZero', null); check_cells(_("Only balances"), 'Balance', null); - submit_cells('Show',_("Show"),'','', true); + submit_cells('Show',_("Show"),'','', 'default'); end_table(); end_form(); } //---------------------------------------------------------------------------------------------------- -function get_balance($account, $from, $to, $from_incl=true, $to_incl=true) +function display_trial_balance($type, $typename) { - $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) AS debit, SUM(IF(amount < 0, -amount, 0)) AS credit, SUM(amount) AS balance - FROM ".TB_PREF."gl_trans,".TB_PREF."chart_master,".TB_PREF."chart_types, ".TB_PREF."chart_class - WHERE ".TB_PREF."gl_trans.account=".TB_PREF."chart_master.account_code AND ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id - AND ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid AND"; - - if ($account != null) - $sql .= " account='$account' AND"; - $from_date = date2sql($from); - if ($from_incl) - $sql .= " tran_date >= '$from_date' AND"; - else - $sql .= " tran_date > IF(".TB_PREF."chart_class.balance_sheet=1, '0000-00-00', '$from_date') AND"; - $to_date = date2sql($to); - if ($to_incl) - $sql .= " tran_date <= '$to_date' "; - else - $sql .= " tran_date < '$to_date' "; - - $result = db_query($sql,"No general ledger accounts were returned"); - - return db_fetch($result); -} + global $path_to_root, $SysPrefs, + $k, $pdeb, $pcre, $cdeb, $ccre, $tdeb, $tcre, $pbal, $cbal, $tbal; -//---------------------------------------------------------------------------------------------------- - -function display_trial_balance() -{ - global $table_style, $path_to_root; - - div_start('balance_tbl'); - start_table($table_style); - $tableheader = " - " . _("Account") . " - " . _("Account Name") . " - " . _("Brought Forward") . " - " . _("This Period") . " - " . _("Balance") . " - - " . _("Debit") . " - " . _("Credit") . " - " . _("Debit") . " - " . _("Credit") . " - " . _("Debit") . " - " . _("Credit") . " - "; - - echo $tableheader; + $printtitle = 0; //Flag for printing type name $k = 0; - $accounts = get_gl_accounts(); - $pdeb = $pcre = $cdeb = $ccre = $tdeb = $tcre = $pbal = $cbal = $tbal = 0; - $begin = begin_fiscalyear(); + //Get Accounts directly under this group/type + $accounts = get_gl_accounts(null, null, $type); + + $begin = get_fiscalyear_begin_for_date($_POST['TransFromDate']); if (date1_greater_date2($begin, $_POST['TransFromDate'])) $begin = $_POST['TransFromDate']; $begin = add_days($begin, -1); - + while ($account = db_fetch($accounts)) { - $prev = get_balance($account["account_code"], $begin, $_POST['TransFromDate'], false, false); - $curr = get_balance($account["account_code"], $_POST['TransFromDate'], $_POST['TransToDate'], true, true); - $tot = get_balance($account["account_code"], $begin, $_POST['TransToDate'], false, true); + //Print Type Title if it has atleast one non-zero account + if (!$printtitle) + { + start_row("class='inquirybg' style='font-weight:bold'"); + label_cell(_("Group")." - ".$type ." - ".$typename, "colspan=8"); + end_row(); + $printtitle = 1; + } + + // FA doesn't really clear the closed year, therefore the brought forward balance includes all the transactions from the past, even though the balance is null. + // If we want to remove the balanced part for the past years, this option removes the common part from from the prev and tot figures. + if (@$SysPrefs->clear_trial_balance_opening) + { + $open = get_balance($account["account_code"], $_POST['Dimension'], $_POST['Dimension2'], $begin, $begin, false, true); + $offset = min($open['debit'], $open['credit']); + } else + $offset = 0; + + $prev = get_balance($account["account_code"], $_POST['Dimension'], $_POST['Dimension2'], $begin, $_POST['TransFromDate'], false, false); + $curr = get_balance($account["account_code"], $_POST['Dimension'], $_POST['Dimension2'], $_POST['TransFromDate'], $_POST['TransToDate'], true, true); + $tot = get_balance($account["account_code"], $_POST['Dimension'], $_POST['Dimension2'], $begin, $_POST['TransToDate'], false, true); if (check_value("NoZero") && !$prev['balance'] && !$curr['balance'] && !$tot['balance']) continue; alt_table_row_color($k); - $url = "" . $account["account_code"] . ""; + $url = "" . $account["account_code"] . ""; label_cell($url); label_cell($account["account_name"]); @@ -130,16 +120,16 @@ function display_trial_balance() display_debit_or_credit_cells($prev['balance']); display_debit_or_credit_cells($curr['balance']); display_debit_or_credit_cells($tot['balance']); - + } else { - amount_cell($prev['debit']); - amount_cell($prev['credit']); + amount_cell($prev['debit']-$offset); + amount_cell($prev['credit']-$offset); amount_cell($curr['debit']); amount_cell($curr['credit']); - amount_cell($tot['debit']); - amount_cell($tot['credit']); + amount_cell($tot['debit']-$offset); + amount_cell($tot['credit']-$offset); $pdeb += $prev['debit']; $pcre += $prev['credit']; $cdeb += $curr['debit']; @@ -153,9 +143,77 @@ function display_trial_balance() end_row(); } - //$prev = get_balance(null, $begin, $_POST['TransFromDate'], false, false); - //$curr = get_balance(null, $_POST['TransFromDate'], $_POST['TransToDate'], true, true); - //$tot = get_balance(null, $begin, $_POST['TransToDate'], false, true); + //Get Account groups/types under this group/type + $result = get_account_types(false, false, $type); + while ($accounttype=db_fetch($result)) + { + //Print Type Title if has sub types and not previously printed + if (!$printtitle) + { + start_row("class='inquirybg' style='font-weight:bold'"); + label_cell(_("Group")." - ".$type ." - ".$typename, "colspan=8"); + end_row(); + $printtitle = 1; + + } + display_trial_balance($accounttype["id"], $accounttype["name"].' ('.$typename.')'); + } +} + +//---------------------------------------------------------------------------------------------------- + +gl_inquiry_controls(); + +if (isset($_POST['TransFromDate'])) +{ + $row = get_current_fiscalyear(); + if (date1_greater_date2($_POST['TransFromDate'], sql2date($row['end']))) + { + display_error(_("The from date cannot be bigger than the fiscal year end.")); + set_focus('TransFromDate'); + return; + } +} +div_start('balance_tbl'); +if (!isset($_POST['Dimension'])) + $_POST['Dimension'] = 0; +if (!isset($_POST['Dimension2'])) + $_POST['Dimension2'] = 0; +start_table(TABLESTYLE); +$tableheader = " + " . _("Account") . " + " . _("Account Name") . " + " . _("Brought Forward") . " + " . _("This Period") . " + " . _("Balance") . " + + " . _("Debit") . " + " . _("Credit") . " + " . _("Debit") . " + " . _("Credit") . " + " . _("Debit") . " + " . _("Credit") . " + "; + +echo $tableheader; + +//display_trial_balance(); + +$classresult = get_account_classes(false); +while ($class = db_fetch($classresult)) +{ + start_row("class='inquirybg' style='font-weight:bold'"); + label_cell(_("Class")." - ".$class['cid'] ." - ".$class['class_name'], "colspan=8"); + end_row(); + + //Get Account groups/types under this group/type with no parents + $typeresult = get_account_types(false, $class['cid'], -1); + while ($accounttype=db_fetch($typeresult)) + { + display_trial_balance($accounttype["id"], $accounttype["name"]); + } +} + if (!check_value('Balance')) { start_row("class='inquirybg' style='font-weight:bold'"); @@ -176,18 +234,11 @@ function display_trial_balance() end_row(); end_table(1); + if (($pbal = round2($pbal, user_price_dec())) != 0 && $_POST['Dimension'] == 0 && $_POST['Dimension2'] == 0) + display_warning(_("The Opening Balance is not in balance, probably due to a non closed Previous Fiscalyear.")); div_end(); -} - -//---------------------------------------------------------------------------------------------------- - -gl_inquiry_controls(); - -display_trial_balance(); //---------------------------------------------------------------------------------------------------- end_page(); -?> -