gl/gl_budget.php

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