1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Account reconciliation routines
33 #======================================================================
43 $main::lxdebug->enter_sub();
45 my ($self, $myconfig, $form) = @_;
47 my $dbh = SL::DB->client->dbh;
50 qq|SELECT accno, description | .
52 qq|WHERE link LIKE '%_paid%' AND category IN ('A', 'L') | .
55 $form->{PR} = selectall_hashref_query($form, $dbh, $query);
57 $main::lxdebug->leave_sub();
60 sub payment_transactions {
61 $main::lxdebug->enter_sub();
63 my ($self, $myconfig, $form) = @_;
65 # connect to database, turn AutoCommit off
66 my $dbh = SL::DB->client->dbh;
71 if ($form->{fromdate}) {
73 qq|SELECT sum(a.amount), | .
74 qq| (SELECT DISTINCT c2.category FROM chart c2 | .
75 qq| WHERE c2.accno = ?) AS category | .
76 qq|FROM acc_trans a | .
77 qq|JOIN chart c ON (c.id = a.chart_id) | .
78 qq|WHERE a.transdate < ? AND a.cleared = '1' AND c.accno = ?|;
79 @values = ($form->{accno}, conv_date($form->{fromdate}), $form->{accno});
83 qq|SELECT sum(a.amount), | .
84 qq| (SELECT DISTINCT c2.category FROM chart c2 | .
85 qq| WHERE c2.accno = ?) AS category | .
86 qq|FROM acc_trans a | .
87 qq|JOIN chart c ON (c.id = a.chart_id) | .
88 qq|WHERE a.cleared = '1' AND c.accno = ?|;
89 @values = ($form->{accno}, $form->{accno});
92 ($form->{beginningbalance}, $form->{category}) =
93 selectrow_query($form, $dbh, $query, @values);
97 qq|SELECT c.name, ac.source, ac.transdate, ac.cleared, | .
98 qq| ac.fx_transaction, ac.amount, a.id, | .
99 qq| ac.acc_trans_id AS oid | .
100 qq|FROM customer c, acc_trans ac, ar a, chart ch | .
101 qq|WHERE c.id = a.customer_id | .
102 qq| AND ac.cleared = '0' | .
103 qq| AND ac.trans_id = a.id | .
104 qq| AND ac.chart_id = ch.id | .
105 qq| AND ch.accno = ? |;
106 push(@values, $form->{accno});
108 if($form->{fromdate}) {
109 $query .= qq| AND ac.transdate >= ? |;
110 push(@values, conv_date($form->{fromdate}));
114 $query .= qq| AND ac.transdate <= ? |;
115 push(@values, conv_date($form->{todate}));
118 if($form->{additional_fromdate}) {
119 $query .= qq| AND ac.transdate >= ? |;
120 push(@values, conv_date($form->{additional_fromdate}));
123 if($form->{additional_todate}){
124 $query .= qq| AND ac.transdate <= ? |;
125 push(@values, conv_date($form->{additional_todate}));
128 if($form->{filter_amount}){
129 $query .= qq| AND ac.amount = ? |;
130 push(@values, conv_i($form->{filter_amount}));
136 qq|SELECT v.name, ac.source, ac.transdate, ac.cleared, | .
137 qq| ac.fx_transaction, ac.amount, a.id, | .
138 qq| ac.acc_trans_id AS oid | .
139 qq|FROM vendor v, acc_trans ac, ap a, chart ch | .
140 qq|WHERE v.id = a.vendor_id | .
141 qq| AND ac.cleared = '0' | .
142 qq| AND ac.trans_id = a.id | .
143 qq| AND ac.chart_id = ch.id | .
144 qq| AND ch.accno = ? |;
146 push(@values, $form->{accno});
148 if($form->{fromdate}) {
149 $query .= qq| AND ac.transdate >= ? |;
150 push(@values, conv_date($form->{fromdate}));
154 $query .= qq| AND ac.transdate <= ? |;
155 push(@values, conv_date($form->{todate}));
158 if($form->{additional_fromdate}) {
159 $query .= qq| AND ac.transdate >= ? |;
160 push(@values, conv_date($form->{additional_fromdate}));
163 if($form->{additional_todate}){
164 $query .= qq| AND ac.transdate <= ? |;
165 push(@values, conv_date($form->{additional_todate}));
168 if($form->{filter_amount}){
169 $query .= qq| AND ac.amount = ? |;
170 push(@values, conv_i($form->{filter_amount}));
176 qq|SELECT g.description, ac.source, ac.transdate, ac.cleared, | .
177 qq| ac.fx_transaction, ac.amount, g.id, | .
178 qq| ac.acc_trans_id AS oid | .
179 qq|FROM gl g, acc_trans ac, chart ch | .
180 qq|WHERE g.id = ac.trans_id | .
181 qq| AND ac.cleared = '0' | .
182 qq| AND ac.trans_id = g.id | .
183 qq| AND ac.chart_id = ch.id | .
184 qq| AND ch.accno = ? |;
186 push(@values, $form->{accno});
188 if($form->{fromdate}) {
189 $query .= qq| AND ac.transdate >= ? |;
190 push(@values, conv_date($form->{fromdate}));
194 $query .= qq| AND ac.transdate <= ? |;
195 push(@values, conv_date($form->{todate}));
198 if($form->{additional_fromdate}) {
199 $query .= qq| AND ac.transdate >= ? |;
200 push(@values, conv_date($form->{additional_fromdate}));
203 if($form->{additional_todate}){
204 $query .= qq| AND ac.transdate <= ? |;
205 push(@values, conv_date($form->{additional_todate}));
208 if($form->{filter_amount}){
209 $query .= qq| AND ac.amount = ? |;
210 push(@values, conv_i($form->{filter_amount}));
213 $query .= " ORDER BY 3,7,8";
215 $form->{PR} = selectall_hashref_query($form, $dbh, $query, @values);
217 $main::lxdebug->leave_sub();
221 $main::lxdebug->enter_sub();
223 my ($self, $myconfig, $form) = @_;
225 SL::DB->client->with_transaction(sub {
226 my $dbh = SL::DB->client->dbh;
231 for $i (1 .. $form->{rowcount}) {
232 if ($form->{"cleared_$i"}) {
234 qq|UPDATE acc_trans SET cleared = '1' | .
235 qq|WHERE acc_trans_id = ?|;
236 do_query($form, $dbh, $query, $form->{"oid_$i"});
238 # clear fx_transaction
239 if ($form->{"fxoid_$i"}) {
241 qq|UPDATE acc_trans SET cleared = '1' | .
242 qq|WHERE acc_trans_id = ?|;
243 do_query($form, $dbh, $query, $form->{"fxoid_$i"});
248 }) or do { die SL::DB->client->error };
250 $main::lxdebug->leave_sub();
253 sub get_statement_balance {
254 $main::lxdebug->enter_sub();
256 my ($self, $myconfig, $form) = @_;
258 # connect to database, turn AutoCommit off
259 my $dbh = SL::DB->client->dbh;
261 my ($query, @values);
263 $query = qq|SELECT sum(amount) FROM acc_trans where chart_id=45 AND cleared='1'|;
265 if($form->{fromdate}) {
266 $query .= qq| AND transdate >= ? |;
267 push(@values, conv_date($form->{fromdate}));
271 $query .= qq| AND transdate <= ? |;
272 push(@values, conv_date($form->{todate}));
275 ($form->{statement_balance}) = selectrow_query($form, $dbh, $query, @values);
277 $main::lxdebug->leave_sub();