1 #=====================================================================
2 # SQL-Ledger Accounting
3 # Copyright (C) 1998-2002
5 # Author: Dieter Simader
6 # Email: dsimader@sql-ledger.org
7 # Web: http://www.sql-ledger.org
11 # This program is free software; you can redistribute it and/or modify
12 # it under the terms of the GNU General Public License as published by
13 # the Free Software Foundation; either version 2 of the License, or
14 # (at your option) any later version.
16 # This program is distributed in the hope that it will be useful,
17 # but WITHOUT ANY WARRANTY; without even the implied warranty of
18 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 # GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License
21 # along with this program; if not, write to the Free Software
22 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
23 #======================================================================
26 # also used for partsgroups
28 #======================================================================
34 my ($self, $myconfig, $form) = @_;
37 my $dbh = $form->dbconnect($myconfig);
39 my $sortorder = ($form->{sort}) ? $form->{sort} : "projectnumber";
41 my $query = qq|SELECT id, projectnumber, description
45 if ($form->{projectnumber}) {
46 my $projectnumber = $form->like(lc $form->{projectnumber});
47 $query .= " AND lower(projectnumber) LIKE '$projectnumber'";
49 if ($form->{projectdescription}) {
50 my $description = $form->like(lc $form->{projectdescription});
51 $query .= " AND lower(description) LIKE '$description'";
53 if ($form->{status} eq 'orphaned') {
54 $query .= " AND id NOT IN (SELECT p.id
55 FROM project p, acc_trans a
56 WHERE p.id = a.project_id)
57 AND id NOT IN (SELECT p.id
58 FROM project p, invoice i
59 WHERE p.id = i.project_id)
60 AND id NOT IN (SELECT p.id
61 FROM project p, orderitems o
62 WHERE p.id = o.project_id)";
68 $sth = $dbh->prepare($query);
69 $sth->execute || $form->dberror($query);
72 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
73 push @{ $form->{project_list} }, $ref;
86 my ($self, $myconfig, $form) = @_;
89 my $dbh = $form->dbconnect($myconfig);
91 my $query = qq|SELECT *
93 WHERE id = $form->{id}|;
94 my $sth = $dbh->prepare($query);
95 $sth->execute || $form->dberror($query);
97 my $ref = $sth->fetchrow_hashref(NAME_lc);
99 map { $form->{$_} = $ref->{$_} } keys %$ref;
103 # check if it is orphaned
104 $query = qq|SELECT count(*)
106 WHERE project_id = $form->{id}|;
107 $sth = $dbh->prepare($query);
108 $sth->execute || $form->dberror($query);
110 ($form->{orphaned}) = $sth->fetchrow_array;
111 $form->{orphaned} = !$form->{orphaned};
121 my ($self, $myconfig, $form) = @_;
123 # connect to database
124 my $dbh = $form->dbconnect($myconfig);
126 map { $form->{$_} =~ s/'/''/g } (projectnumber, description);
129 $query = qq|UPDATE project SET
130 projectnumber = '$form->{projectnumber}',
131 description = '$form->{description}'
132 WHERE id = $form->{id}|;
134 $query = qq|INSERT INTO project
135 (projectnumber, description)
136 VALUES ('$form->{projectnumber}', '$form->{description}')|;
138 $dbh->do($query) || $form->dberror($query);
146 my ($self, $myconfig, $form) = @_;
150 # connect to database
151 my $dbh = $form->dbconnect($myconfig);
153 my $sortorder = ($form->{sort}) ? $form->{sort} : "partsgroup";
155 my $query = qq|SELECT g.*
160 if ($form->{partsgroup}) {
161 $var = $form->like(lc $form->{partsgroup});
162 $where .= " AND lower(partsgroup) LIKE '$var'";
166 ORDER BY $sortorder|;
168 if ($form->{status} eq 'orphaned') {
169 $query = qq|SELECT g.*
171 LEFT JOIN parts p ON (p.partsgroup_id = g.id)
176 JOIN parts p ON (p.partsgroup_id = g.id)
178 ORDER BY $sortorder|;
181 $sth = $dbh->prepare($query);
182 $sth->execute || $form->dberror($query);
185 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
186 push @{ $form->{item_list} }, $ref;
198 sub save_partsgroup {
199 my ($self, $myconfig, $form) = @_;
201 # connect to database
202 my $dbh = $form->dbconnect($myconfig);
204 map { $form->{$_} =~ s/'/''/g } (partsgroup);
208 $query = qq|UPDATE partsgroup SET
209 partsgroup = '$form->{partsgroup}'
210 WHERE id = $form->{id}|;
212 $query = qq|INSERT INTO partsgroup
214 VALUES ('$form->{partsgroup}')|;
216 $dbh->do($query) || $form->dberror($query);
224 my ($self, $myconfig, $form) = @_;
226 # connect to database
227 my $dbh = $form->dbconnect($myconfig);
229 my $query = qq|SELECT *
231 WHERE id = $form->{id}|;
232 my $sth = $dbh->prepare($query);
233 $sth->execute || $form->dberror($query);
235 my $ref = $sth->fetchrow_hashref(NAME_lc);
237 map { $form->{$_} = $ref->{$_} } keys %$ref;
241 # check if it is orphaned
242 $query = qq|SELECT count(*)
244 WHERE partsgroup_id = $form->{id}|;
245 $sth = $dbh->prepare($query);
246 $sth->execute || $form->dberror($query);
248 ($form->{orphaned}) = $sth->fetchrow_array;
249 $form->{orphaned} = !$form->{orphaned};
260 my ($self, $myconfig, $form) = @_;
262 # connect to database
263 my $dbh = $form->dbconnect($myconfig);
265 $query = qq|DELETE FROM $form->{type}
266 WHERE id = $form->{id}|;
267 $dbh->do($query) || $form->dberror($query);