gl/gl_budget.php

   1 <?php
   2
   3 $page_security = 10;
   4 $path_to_root="..";
   5 include($path_to_root . "/includes/session.inc");
   6
   7 add_js_file('budget.js');
   8
   9 page(_("Budget Entry"));
  10
  11 include($path_to_root . "/includes/ui.inc");
  12 include($path_to_root . "/gl/includes/gl_db.inc");
  13 include_once($path_to_root . "/includes/data_checks.inc");
  14
  15 check_db_has_gl_account_groups(_("There are no account groups defined. Please define at least one account group before entering accounts."));
  16
  17 //-------------------------------------------------------------------------------------
  18
  19 function exists_gl_budget($date_, $account, $dimension, $dimension2)
  20 {
  21         $sql = "SELECT account FROM ".TB_PREF."budget_trans WHERE account='$account' AND tran_date='$date_' AND
  22                 dimension_id=$dimension AND dimension2_id=$dimension2";
  23         $result = db_query($sql, "Cannot retreive a gl transaction");
  24
  25     return (db_num_rows($result) > 0);
  26 }
  27
  28 function add_update_gl_budget_trans($date_, $account, $dimension, $dimension2, $amount)
  29 {
  30         $date = date2sql($date_);
  31
  32         if (exists_gl_budget($date, $account, $dimension, $dimension2))
  33                 $sql = "UPDATE ".TB_PREF."budget_trans SET amount=$amount WHERE account='$account' AND
  34                         dimension_id=$dimension AND dimension2_id=$dimension2 AND tran_date='$date'";
  35         else
  36                 $sql = "INSERT INTO ".TB_PREF."budget_trans (tran_date,
  37                         account, dimension_id, dimension2_id, amount) VALUES ('$date',
  38                         '$account', $dimension, $dimension2, $amount)";
  39
  40         db_query($sql, "The GL budget transaction could not be saved");
  41 }
  42
  43 function delete_gl_budget_trans($date_, $account, $dimension, $dimension2)
  44 {
  45         $date = date2sql($date_);
  46
  47         $sql = "DELETE FROM ".TB_PREF."budget_trans WHERE account='$account' AND
  48                         dimension_id=$dimension AND dimension2_id=$dimension2 AND tran_date='$date'";
  49         db_query($sql, "The GL budget transaction could not be deleted");
  50 }
  51
  52 function get_only_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0)
  53 {
  54
  55         $from = date2sql($from_date);
  56         $to = date2sql($to_date);
  57
  58         $sql = "SELECT SUM(amount) FROM ".TB_PREF."budget_trans
  59                 WHERE account='$account' AND tran_date >= '$from' AND tran_date <= '$to'
  60                  AND dimension_id = $dimension AND dimension2_id = $dimension2";
  61         $result = db_query($sql,"No budget accounts were returned");
  62
  63         $row = db_fetch_row($result);
  64         return $row[0];
  65 }
  66
  67 //-------------------------------------------------------------------------------------
  68
  69 if (isset($_POST['add']) || isset($_POST['delete']))
  70 {
  71         begin_transaction();
  72
  73         for ($i = 0, $da = $_POST['begin']; date1_greater_date2($_POST['end'], $da); $i++)
  74         {
  75                 if (isset($_POST['add']))
  76                         add_update_gl_budget_trans($da, $_POST['account'], $_POST['dim1'], $_POST['dim2'], input_num('amount'.$i));
  77                 else
  78                         delete_gl_budget_trans($da, $_POST['account'], $_POST['dim1'], $_POST['dim2']);
  79                 $da = add_months($da, 1);
  80         }
  81         commit_transaction();
  82
  83         if (isset($_POST['add']))
  84                 display_notification_centered(_("The Budget has been saved."));
  85         else
  86                 display_notification_centered(_("The Budget has been deleted."));
  87
  88         //meta_forward($_SERVER['PHP_SELF']);
  89         $Ajax->activate('budget_tbl');
  90 }
  91 if (isset($_POST['submit']) || isset($_POST['update']))
  92         $Ajax->activate('budget_tbl');
  93
  94 //-------------------------------------------------------------------------------------
  95
  96 start_form();
  97
  98 if (db_has_gl_accounts())
  99 {
 100         $dim = get_company_pref('use_dimension');
 101         start_table($table_style2);
 102         fiscalyears_list_row(_("Fiscal Year:"), 'fyear', null);
 103         gl_all_accounts_list_row(_("Account Code:"), 'account', null);
 104         if (!isset($_POST['dim1']))
 105                 $_POST['dim1'] = 0;
 106         if (!isset($_POST['dim2']))
 107                 $_POST['dim2'] = 0;
 108     if ($dim == 2)
 109     {
 110                 dimensions_list_row(_("Dimension")." 1", 'dim1', $_POST['dim1'], true, null, false, 1);
 111                 dimensions_list_row(_("Dimension")." 2", 'dim2', $_POST['dim2'], true, null, false, 2);
 112         }
 113         else if ($dim == 1)
 114         {
 115                 dimensions_list_row(_("Dimension"), 'dim1', $_POST['dim1'], true, null, false, 1);
 116                 hidden('dim2', 0);
 117         }
 118         else
 119         {
 120                 hidden('dim1', 0);
 121                 hidden('dim2', 0);
 122         }
 123         submit_row('submit', _("Get"), true, '', '', true);
 124         end_table(1);
 125         div_start('budget_tbl');
 126         start_table($table_style2);
 127         $showdims = (($dim == 1 && $_POST['dim1'] == 0) ||
 128                 ($dim == 2 && $_POST['dim1'] == 0 && $_POST['dim2'] == 0));
 129         if ($showdims)
 130                 $th = array(_("Period"), _("Amount"), _("Dim. incl."), _("Last Year"));
 131         else
 132                 $th = array(_("Period"), _("Amount"), _("Last Year"));
 133         table_header($th);
 134         $year = $_POST['fyear'];
 135         if (get_post('update') == '') {
 136                 $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE id=$year";
 137
 138                 $result = db_query($sql, "could not get current fiscal year");
 139
 140                 $fyear = db_fetch($result);
 141                 $_POST['begin'] = sql2date($fyear['begin']);
 142                 $_POST['end'] = sql2date($fyear['end']);
 143         }
 144         hidden('begin');
 145         hidden('end');
 146         $total = $btotal = $ltotal = 0;
 147         for ($i = 0, $date_ = $_POST['begin']; date1_greater_date2($_POST['end'], $date_); $i++)
 148         {
 149                 start_row();
 150                 if (get_post('update') == '')
 151                         $_POST['amount'.$i] = number_format2(get_only_budget_trans_from_to(
 152                                 $date_, $date_, $_POST['account'], $_POST['dim1'], $_POST['dim2']), 0);
 153
 154                 label_cell($date_);
 155                 amount_cells(null, 'amount'.$i, null, 15, null, 0);
 156                 if ($showdims)
 157                 {
 158                         $d = get_budget_trans_from_to($date_, $date_, $_POST['account'], $_POST['dim1'], $_POST['dim2']);
 159                         label_cell(number_format2($d, 0), "nowrap align=right");
 160                         $btotal += $d;
 161                 }
 162                 $lamount = get_gl_trans_from_to(add_years($date_, -1), add_years(end_month($date_), -1), $_POST['account'], $_POST['dim1'], $_POST['dim2']);
 163                 $total += input_num('amount'.$i);
 164                 $ltotal += $lamount;
 165                 label_cell(number_format2($lamount, 0), "nowrap align=right");
 166                 $date_ = add_months($date_, 1);
 167                 end_row();
 168         }
 169         start_row();
 170         label_cell("<b>"._("Total")."</b>");
 171         label_cell(number_format2($total, 0), 'align=right style="font-weight:bold"', 'Total');
 172         if ($showdims)
 173                 label_cell("<b>".number_format2($btotal, 0)."</b>", "nowrap align=right");
 174         label_cell("<b>".number_format2($ltotal, 0)."</b>", "nowrap align=right");
 175         end_row();
 176         end_table(1);
 177         div_end();
 178         submit_center_first('update', _("Update"), '', null);
 179         submit('add', _("Save"), true, '', true);
 180         submit_center_last('delete', _("Delete"), '', true);
 181 }
 182 end_form();
 183
 184 end_page();
 185
 186 ?>