1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
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 #======================================================================
32 # also used for partsgroups
34 #======================================================================
43 $main::lxdebug->enter_sub();
45 my ($self, $myconfig, $form) = @_;
48 my $dbh = $form->dbconnect($myconfig);
52 foreach my $column (qw(projectnumber description)) {
53 if ($form->{$column}) {
54 $where .= qq|AND $column ILIKE ? |;
55 push(@values, '%' . $form->{$column} . '%');
59 if ($form->{status} eq 'orphaned') {
60 my %col_prefix = ("ar" => "global", "ap" => "global", "oe" => "global");
63 $where .= qq|AND id NOT IN (|;
64 foreach my $table (qw(acc_trans invoice orderitems rmaitems ar ap oe)) {
65 $where .= "UNION " unless ($first);
68 qq|SELECT DISTINCT $col_prefix{$table}project_id FROM $table | .
69 qq|WHERE NOT $col_prefix{$table}project_id ISNULL |;
74 if ($form->{active} eq "active") {
75 $where .= qq|AND active |;
76 } elsif ($form->{active} eq "inactive") {
77 $where .= qq|AND NOT active |;
80 substr($where, 0, 4) = "WHERE " if ($where);
82 my $sortorder = $form->{sort} ? $form->{sort} : "projectnumber";
83 $sortorder =~ s/[^a-z_]//g;
85 qq|SELECT id, projectnumber, description, active | .
88 qq|ORDER BY $sortorder|;
90 $main::lxdebug->message(1, $query);
92 $form->{project_list} =
93 selectall_hashref_query($form, $dbh, $query, @values);
96 $main::lxdebug->leave_sub();
98 return scalar(@{ $form->{project_list} });
102 $main::lxdebug->enter_sub();
104 my ($self, $myconfig, $form) = @_;
106 # connect to database
107 my $dbh = $form->dbconnect($myconfig);
110 qq|SELECT * FROM project | .
112 my @values = ($form->{id});
113 my $sth = $dbh->prepare($query);
114 $sth->execute(@values) || $form->dberror($query);
116 my $ref = $sth->fetchrow_hashref(NAME_lc);
118 map { $form->{$_} = $ref->{$_} } keys %$ref;
122 # check if it is orphaned
123 my %col_prefix = ("ar" => "global", "ap" => "global", "oe" => "global");
125 $query = qq|SELECT |;
127 foreach my $table (qw(acc_trans invoice orderitems rmaitems ar ap oe)) {
128 $query .= " + " unless ($first);
131 qq|(SELECT COUNT(*) FROM $table | .
132 qq| WHERE $col_prefix{$table}project_id = ?) |;
133 push(@values, $form->{id});
136 ($form->{orphaned}) = selectrow_query($form, $dbh, $query, @values);
137 $form->{orphaned} = !$form->{orphaned};
141 $main::lxdebug->leave_sub();
145 $main::lxdebug->enter_sub();
147 my ($self, $myconfig, $form) = @_;
149 # connect to database
150 my $dbh = $form->dbconnect($myconfig);
152 my @values = ($form->{projectnumber}, $form->{description});
156 qq|UPDATE project SET projectnumber = ?, description = ?, active = ? | .
158 push(@values, ($form->{active} ? 't' : 'f'), $form->{id});
161 qq|INSERT INTO project (projectnumber, description, active) | .
162 qq|VALUES (?, ?, 't')|;
164 do_query($form, $dbh, $query, @values);
168 $main::lxdebug->leave_sub();
172 $main::lxdebug->enter_sub();
174 my ($self, $myconfig, $form) = @_;
176 # connect to database
177 my $dbh = $form->dbconnect($myconfig);
179 my ($where, @values);
181 if ($form->{partsgroup}) {
182 $where .= qq| AND partsgroup ILIKE ?|;
183 push(@values, '%' . $form->{partsgroup} . '%');
186 if ($form->{status} eq 'orphaned') {
188 qq| AND id NOT IN | .
189 qq| (SELECT DISTINCT partsgroup_id FROM parts | .
190 qq| WHERE NOT partsgroup_id ISNULL) |;
193 substr($where, 0, 4) = "WHERE " if ($where);
195 my $sortorder = $form->{sort} ? $form->{sort} : "partsgroup";
196 $sortorder =~ s/[^a-z_]//g;
199 qq|SELECT id, partsgroup FROM partsgroup | .
201 qq|ORDER BY $sortorder|;
203 $form->{item_list} = selectall_hashref_query($form, $dbh, $query, @values);
207 $main::lxdebug->leave_sub();
209 return scalar(@{ $form->{item_list} });
212 sub save_partsgroup {
213 $main::lxdebug->enter_sub();
215 my ($self, $myconfig, $form) = @_;
217 # connect to database
218 my $dbh = $form->dbconnect($myconfig);
220 $form->{discount} /= 100;
222 my @values = ($form->{partsgroup});
225 $query = qq|UPDATE partsgroup SET partsgroup = ? WHERE id = ?|;
226 push(@values, $form->{id});
228 $query = qq|INSERT INTO partsgroup (partsgroup) VALUES (?)|;
230 do_query($form, $dbh, $query, @values);
234 $main::lxdebug->leave_sub();
238 $main::lxdebug->enter_sub();
240 my ($self, $myconfig, $form) = @_;
242 # connect to database
243 my $dbh = $form->dbconnect($myconfig);
247 qq|(SELECT COUNT(*) FROM parts WHERE partsgroup_id = ?) = 0 AS orphaned | .
248 qq|FROM partsgroup pg | .
250 my $sth = prepare_execute_query($form, $dbh, $query, $form->{id},
252 my $ref = $sth->fetchrow_hashref(NAME_lc);
254 map({ $form->{$_} = $ref->{$_} } keys(%{$ref}));
259 $main::lxdebug->leave_sub();
263 $main::lxdebug->enter_sub();
265 my ($self, $myconfig, $form) = @_;
267 # connect to database
268 my $dbh = $form->dbconnect($myconfig);
271 $form->{type} eq "project" ? "project" :
272 $form->{type} eq "pricegroup" ? "pricegroup" :
275 $query = qq|DELETE FROM $table WHERE id = ?|;
276 do_query($form, $dbh, $query, $form->{id});
280 $main::lxdebug->leave_sub();
283 ##########################
284 # get pricegroups from database
287 $main::lxdebug->enter_sub();
289 my ($self, $myconfig, $form) = @_;
291 # connect to database
292 my $dbh = $form->dbconnect($myconfig);
294 my ($where, @values);
296 if ($form->{pricegroup}) {
297 $where .= qq| AND pricegroup ILIKE ?|;
298 push(@values, '%' . $form->{pricegroup} . '%');
301 if ($form->{status} eq 'orphaned') {
304 $where .= qq| AND id NOT IN (|;
305 foreach my $table (qw(invoice orderitems prices rmaitems)) {
306 $where .= "UNION " unless ($first);
309 qq|SELECT DISTINCT pricegroup_id FROM $table | .
310 qq|WHERE NOT pricegroup_id ISNULL |;
315 substr($where, 0, 4) = "WHERE " if ($where);
317 my $sortorder = $form->{sort} ? $form->{sort} : "pricegroup";
318 $sortorder =~ s/[^a-z_]//g;
321 qq|SELECT id, pricegroup FROM pricegroup | .
323 qq|ORDER BY $sortorder|;
325 $form->{item_list} = selectall_hashref_query($form, $dbh, $query, @values);
329 $main::lxdebug->leave_sub();
331 return scalar(@{ $form->{item_list} });
334 ########################
335 # save pricegruop to database
337 sub save_pricegroup {
338 $main::lxdebug->enter_sub();
340 my ($self, $myconfig, $form) = @_;
342 # connect to database
343 my $dbh = $form->dbconnect($myconfig);
346 $form->{discount} /= 100;
348 my @values = ($form->{pricegroup});
351 $query = qq|UPDATE pricegroup SET pricegroup = ? WHERE id = ? |;
352 push(@values, $form->{id});
354 $query = qq|INSERT INTO pricegroup (pricegroup) VALUES (?)|;
356 do_query($form, $dbh, $query, @values);
360 $main::lxdebug->leave_sub();
363 ############################
364 # get one pricegroup from database
367 $main::lxdebug->enter_sub();
369 my ($self, $myconfig, $form) = @_;
371 # connect to database
372 my $dbh = $form->dbconnect($myconfig);
374 my $query = qq|SELECT id, pricegroup FROM pricegroup WHERE id = ?|;
375 my $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
376 my $ref = $sth->fetchrow_hashref(NAME_lc);
378 map({ $form->{$_} = $ref->{$_} } keys(%{$ref}));
385 $query = qq|SELECT |;
386 foreach my $table (qw(invoice orderitems prices rmaitems)) {
387 $query .= " + " unless ($first);
389 $query .= qq|(SELECT COUNT(*) FROM $table WHERE pricegroup_id = ?) |;
390 push(@values, $form->{id});
393 ($form->{orphaned}) = selectrow_query($form, $dbh, $query, @values);
394 $form->{orphaned} = !$form->{orphaned};
398 $main::lxdebug->leave_sub();