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';
14 include($path_to_root . "/includes/session.inc");
16 add_js_file('budget.js');
18 page(_($help_context = "Budget Entry"));
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");
24 check_db_has_gl_account_groups(_("There are no account groups defined. Please define at least one account group before entering accounts."));
26 //-------------------------------------------------------------------------------------
28 function exists_gl_budget($date_, $account, $dimension, $dimension2)
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");
35 return (db_num_rows($result) > 0);
38 function add_update_gl_budget_trans($date_, $account, $dimension, $dimension2, $amount)
40 $date = date2sql($date_);
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'";
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).", '')";
54 db_query($sql, "The GL budget transaction could not be saved");
57 function delete_gl_budget_trans($date_, $account, $dimension, $dimension2)
59 $date = date2sql($date_);
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");
68 function get_only_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0)
71 $from = date2sql($from_date);
72 $to = date2sql($to_date);
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");
81 $row = db_fetch_row($result);
85 //-------------------------------------------------------------------------------------
87 if (isset($_POST['add']) || isset($_POST['delete']))
91 for ($i = 0, $da = $_POST['begin']; date1_greater_date2($_POST['end'], $da); $i++)
93 if (isset($_POST['add']))
94 add_update_gl_budget_trans($da, $_POST['account'], $_POST['dim1'], $_POST['dim2'], input_num('amount'.$i));
96 delete_gl_budget_trans($da, $_POST['account'], $_POST['dim1'], $_POST['dim2']);
97 $da = add_months($da, 1);
101 if (isset($_POST['add']))
102 display_notification_centered(_("The Budget has been saved."));
104 display_notification_centered(_("The Budget has been deleted."));
106 //meta_forward($_SERVER['PHP_SELF']);
107 $Ajax->activate('budget_tbl');
109 if (isset($_POST['submit']) || isset($_POST['update']))
110 $Ajax->activate('budget_tbl');
112 //-------------------------------------------------------------------------------------
116 if (db_has_gl_accounts())
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']))
124 if (!isset($_POST['dim2']))
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);
133 dimensions_list_row(_("Dimension"), 'dim1', $_POST['dim1'], true, null, false, 1);
141 submit_row('submit', _("Get"), true, '', '', true);
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));
148 $th = array(_("Period"), _("Amount"), _("Dim. incl."), _("Last Year"));
150 $th = array(_("Period"), _("Amount"), _("Last Year"));
152 $year = $_POST['fyear'];
153 if (get_post('update') == '') {
154 $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE id=".db_escape($year);
156 $result = db_query($sql, "could not get current fiscal year");
158 $fyear = db_fetch($result);
159 $_POST['begin'] = sql2date($fyear['begin']);
160 $_POST['end'] = sql2date($fyear['end']);
164 $total = $btotal = $ltotal = 0;
165 for ($i = 0, $date_ = $_POST['begin']; date1_greater_date2($_POST['end'], $date_); $i++)
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);
173 amount_cells(null, 'amount'.$i, null, 15, null, 0);
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");
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);
183 label_cell(number_format2($lamount, 0), "nowrap align=right");
184 $date_ = add_months($date_, 1);
188 label_cell("<b>"._("Total")."</b>");
189 label_cell(number_format2($total, 0), 'align=right style="font-weight:bold"', 'Total');
191 label_cell("<b>".number_format2($btotal, 0)."</b>", "nowrap align=right");
192 label_cell("<b>".number_format2($ltotal, 0)."</b>", "nowrap align=right");
196 submit_center_first('update', _("Update"), '', null);
197 submit('add', _("Save"), true, '', 'default');
198 submit_center_last('delete', _("Delete"), '', true);