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 #======================================================================
40 $main::lxdebug->enter_sub();
42 my ($self, $myconfig, $form) = @_;
45 my $dbh = $form->dbconnect($myconfig);
47 my $sortorder = ($form->{sort}) ? $form->{sort} : "projectnumber";
49 my $query = qq|SELECT p.id, p.projectnumber, p.description
53 if ($form->{projectnumber}) {
54 my $projectnumber = $form->like(lc $form->{projectnumber});
55 $query .= " AND lower(projectnumber) LIKE '$projectnumber'";
57 if ($form->{projectdescription}) {
58 my $description = $form->like(lc $form->{projectdescription});
59 $query .= " AND lower(description) LIKE '$description'";
61 if ($form->{status} eq 'orphaned') {
62 $query .= " AND id NOT IN (SELECT p.id
63 FROM project p, acc_trans a
64 WHERE p.id = a.project_id)
65 AND id NOT IN (SELECT p.id
66 FROM project p, invoice i
67 WHERE p.id = i.project_id)
68 AND id NOT IN (SELECT p.id
69 FROM project p, orderitems o
70 WHERE p.id = o.project_id)";
76 $sth = $dbh->prepare($query);
77 $sth->execute || $form->dberror($query);
80 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
81 push @{ $form->{project_list} }, $ref;
88 $main::lxdebug->leave_sub();
95 $main::lxdebug->enter_sub();
97 my ($self, $myconfig, $form) = @_;
100 my $dbh = $form->dbconnect($myconfig);
102 my $query = qq|SELECT p.*
104 WHERE p.id = $form->{id}|;
105 my $sth = $dbh->prepare($query);
106 $sth->execute || $form->dberror($query);
108 my $ref = $sth->fetchrow_hashref(NAME_lc);
110 map { $form->{$_} = $ref->{$_} } keys %$ref;
114 # check if it is orphaned
115 $query = qq|SELECT count(*)
117 WHERE a.project_id = $form->{id}|;
118 $sth = $dbh->prepare($query);
119 $sth->execute || $form->dberror($query);
121 ($form->{orphaned}) = $sth->fetchrow_array;
122 $form->{orphaned} = !$form->{orphaned};
128 $main::lxdebug->leave_sub();
133 $main::lxdebug->enter_sub();
135 my ($self, $myconfig, $form) = @_;
137 # connect to database
138 my $dbh = $form->dbconnect($myconfig);
140 map { $form->{$_} =~ s/\'/\'\'/g } (projectnumber, description);
143 $query = qq|UPDATE project SET
144 projectnumber = '$form->{projectnumber}',
145 description = '$form->{description}'
146 WHERE id = $form->{id}|;
148 $query = qq|INSERT INTO project
149 (projectnumber, description)
150 VALUES ('$form->{projectnumber}', '$form->{description}')|;
152 $dbh->do($query) || $form->dberror($query);
156 $main::lxdebug->leave_sub();
161 $main::lxdebug->enter_sub();
163 my ($self, $myconfig, $form) = @_;
167 # connect to database
168 my $dbh = $form->dbconnect($myconfig);
170 my $sortorder = ($form->{sort}) ? $form->{sort} : "partsgroup";
172 my $query = qq|SELECT g.*
177 if ($form->{partsgroup}) {
178 $var = $form->like(lc $form->{partsgroup});
179 $where .= " AND lower(g.partsgroup) LIKE '$var'";
183 ORDER BY $sortorder|;
185 if ($form->{status} eq 'orphaned') {
186 $query = qq|SELECT g.*
188 LEFT JOIN parts p ON (p.partsgroup_id = g.id)
193 JOIN parts p ON (p.partsgroup_id = g.id)
195 ORDER BY $sortorder|;
198 $sth = $dbh->prepare($query);
199 $sth->execute || $form->dberror($query);
202 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
203 push @{ $form->{item_list} }, $ref;
210 $main::lxdebug->leave_sub();
216 sub save_partsgroup {
217 $main::lxdebug->enter_sub();
219 my ($self, $myconfig, $form) = @_;
221 # connect to database
222 my $dbh = $form->dbconnect($myconfig);
224 map { $form->{$_} =~ s/\'/\'\'/g } (partsgroup);
226 $form->{discount} /= 100;
229 $query = qq|UPDATE partsgroup SET
230 partsgroup = '$form->{partsgroup}'
231 WHERE id = $form->{id}|;
233 $query = qq|INSERT INTO partsgroup
235 VALUES ('$form->{partsgroup}')|;
237 $dbh->do($query) || $form->dberror($query);
241 $main::lxdebug->leave_sub();
246 $main::lxdebug->enter_sub();
248 my ($self, $myconfig, $form) = @_;
250 # connect to database
251 my $dbh = $form->dbconnect($myconfig);
253 my $query = qq|SELECT p.*
255 WHERE p.id = $form->{id}|;
256 my $sth = $dbh->prepare($query);
257 $sth->execute || $form->dberror($query);
259 my $ref = $sth->fetchrow_hashref(NAME_lc);
261 map { $form->{$_} = $ref->{$_} } keys %$ref;
265 # check if it is orphaned
266 $query = qq|SELECT count(*)
268 WHERE p.partsgroup_id = $form->{id}|;
269 $sth = $dbh->prepare($query);
270 $sth->execute || $form->dberror($query);
272 ($form->{orphaned}) = $sth->fetchrow_array;
273 $form->{orphaned} = !$form->{orphaned};
279 $main::lxdebug->leave_sub();
285 $main::lxdebug->enter_sub();
287 my ($self, $myconfig, $form) = @_;
289 # connect to database
290 my $dbh = $form->dbconnect($myconfig);
292 $query = qq|DELETE FROM $form->{type}
293 WHERE id = $form->{id}|;
294 $dbh->do($query) || $form->dberror($query);
298 $main::lxdebug->leave_sub();