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 # Batch printing module backend routines
33 #======================================================================
39 $main::lxdebug->enter_sub();
41 my ($self, $myconfig, $form) = @_;
44 my $dbh = $form->dbconnect($myconfig);
46 my %arap = ( invoice => 'ar',
49 purchase_order => 'oe',
50 sales_quotation => 'oe',
51 request_quotation => 'oe',
56 $query = qq|SELECT count(*)
57 FROM (SELECT DISTINCT ON (vc.id) vc.id
58 FROM $form->{vc} vc, $arap{$form->{type}} a, status s
59 WHERE a.$form->{vc}_id = vc.id
61 AND s.formname = '$form->{type}'
62 AND s.spoolfile IS NOT NULL) AS total|;
64 my $sth = $dbh->prepare($query);
65 $sth->execute || $form->dberror($query);
66 my ($count) = $sth->fetchrow_array;
69 # build selection list
70 if ($count < $myconfig->{vclimit}) {
71 $query = qq|SELECT DISTINCT ON (vc.id) vc.id, vc.name
72 FROM $form->{vc} vc, $arap{$form->{type}} a, status s
73 WHERE a.$form->{vc}_id = vc.id
75 AND s.formname = '$form->{type}'
76 AND s.spoolfile IS NOT NULL|;
78 $sth = $dbh->prepare($query);
79 $sth->execute || $form->dberror($query);
81 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
82 push @{ $form->{"all_$form->{vc}"} }, $ref;
88 $main::lxdebug->leave_sub();
93 sub payment_accounts {
94 $main::lxdebug->enter_sub();
96 my ($self, $myconfig, $form) = @_;
99 my $dbh = $form->dbconnect($myconfig);
101 my $query = qq|SELECT DISTINCT ON (s.chart_id) c.accno, c.description
102 FROM status s, chart c
103 WHERE s.chart_id = c.id
104 AND s.formname = '$form->{type}'|;
105 my $sth = $dbh->prepare($query);
106 $sth->execute || $form->dberror($query);
108 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
109 push @{ $form->{accounts} }, $ref;
115 $main::lxdebug->leave_sub();
120 $main::lxdebug->enter_sub();
122 my ($self, $myconfig, $form) = @_;
124 # connect to database
125 my $dbh = $form->dbconnect($myconfig);
128 my $invnumber = "invnumber";
130 if ($form->{type} eq 'check' || $form->{type} eq 'receipt') {
132 $arap = ($form->{type} eq 'check') ? "ap" : "ar";
133 my ($accno) = split /--/, $form->{account};
135 $query = qq|SELECT a.id, s.spoolfile, vc.name, ac.transdate, a.invnumber,
136 a.invoice, '$arap' AS module
137 FROM status s, chart c, $form->{vc} vc, $arap a, acc_trans ac
138 WHERE s.formname = '$form->{type}'
139 AND s.chart_id = c.id
140 AND c.accno = '$accno'
141 AND s.trans_id = a.id
142 AND a.$form->{vc}_id = vc.id
143 AND ac.trans_id = s.trans_id
144 AND ac.chart_id = c.id
145 AND NOT ac.fx_transaction|;
149 my $invoice = "a.invoice";
151 if ($form->{type} =~ /_(order|quotation)$/) {
152 $invnumber = "ordnumber";
157 $query = qq|SELECT a.id, a.$invnumber AS invnumber, a.ordnumber,
158 a.quonumber, a.transdate, $invoice AS invoice,
159 '$arap' AS module, vc.name, s.spoolfile
160 FROM $arap a, $form->{vc} vc, status s
161 WHERE s.trans_id = a.id
162 AND s.spoolfile IS NOT NULL
163 AND s.formname = '$form->{type}'
164 AND a.$form->{vc}_id = vc.id|;
167 if ($form->{"$form->{vc}_id"}) {
168 $query .= qq| AND a.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
170 if ($form->{$form->{vc}}) {
171 my $name = $form->like(lc $form->{$form->{vc}});
172 $query .= " AND lower(vc.name) LIKE '$name'";
175 if ($form->{invnumber}) {
176 my $number = $form->like(lc $form->{invnumber});
177 $query .= " AND lower(a.invnumber) LIKE '$number'";
179 if ($form->{ordnumber}) {
180 my $ordnumber = $form->like(lc $form->{ordnumber});
181 $query .= " AND lower(a.ordnumber) LIKE '$ordnumber'";
183 if ($form->{quonumber}) {
184 my $quonumber = $form->like(lc $form->{quonumber});
185 $query .= " AND lower(a.quonumber) LIKE '$quonumber'";
188 # $query .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
189 # $query .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
191 my @a = (transdate, $invnumber, name);
192 my $sortorder = join ', ', $form->sort_columns(@a);
193 $sortorder = $form->{sort} unless $sortorder;
195 $query .= " ORDER by $sortorder";
198 my $sth = $dbh->prepare($query);
199 $sth->execute || $form->dberror($query);
201 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
202 push @{ $form->{SPOOL} }, $ref;
208 $main::lxdebug->leave_sub();
213 $main::lxdebug->enter_sub();
215 my ($self, $myconfig, $form, $spool) = @_;
217 # connect to database, turn AutoCommit off
218 my $dbh = $form->dbconnect_noauto($myconfig);
222 if ($form->{type} =~ /(check|receipt)/) {
223 $query = qq|DELETE FROM status
224 WHERE spoolfile = ?|;
226 $query = qq|UPDATE status SET
229 WHERE spoolfile = ?|;
231 my $sth = $dbh->prepare($query) || $form->dberror($query);
233 foreach my $i (1 .. $form->{rowcount}) {
234 if ($form->{"checked_$i"}) {
235 $sth->execute($form->{"spoolfile_$i"}) || $form->dberror($query);
241 my $rc = $dbh->commit;
245 foreach my $i (1 .. $form->{rowcount}) {
246 $_ = qq|$spool/$form->{"spoolfile_$i"}|;
247 if ($form->{"checked_$i"}) {
253 $main::lxdebug->leave_sub();
260 $main::lxdebug->enter_sub();
262 my ($self, $myconfig, $form, $spool) = @_;
264 # connect to database
265 my $dbh = $form->dbconnect($myconfig);
267 my $query = qq|UPDATE status SET
269 WHERE formname = '$form->{type}'
271 my $sth = $dbh->prepare($query) || $form->dberror($query);
273 foreach my $i (1 .. $form->{rowcount}) {
274 if ($form->{"checked_$i"}) {
275 open(OUT, $form->{OUT}) or $form->error("$form->{OUT} : $!");
277 $spoolfile = qq|$spool/$form->{"spoolfile_$i"}|;
279 # send file to printer
280 open(IN, $spoolfile) or $form->error("$spoolfile : $!");
288 $sth->execute($form->{"spoolfile_$i"}) || $form->dberror($query);
296 $main::lxdebug->leave_sub();