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