1 #=====================================================================
2 # SQL-Ledger Accounting
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 $form->{sort} = "projectnumber" unless $form->{sort};
40 my @a = ($form->{sort});
41 my %ordinal = ( projectnumber => 2,
43 my $sortorder = $form->sort_order(\@a, \%ordinal);
45 my $query = qq|SELECT id, projectnumber, description
49 if ($form->{projectnumber}) {
50 my $projectnumber = $form->like(lc $form->{projectnumber});
51 $query .= " AND lower(projectnumber) LIKE '$projectnumber'";
53 if ($form->{projectdescription}) {
54 my $description = $form->like(lc $form->{projectdescription});
55 $query .= " AND lower(description) LIKE '$description'";
57 if ($form->{status} eq 'orphaned') {
58 $query .= " AND id NOT IN (SELECT p.id
59 FROM project p, acc_trans a
60 WHERE p.id = a.project_id)
61 AND id NOT IN (SELECT p.id
62 FROM project p, invoice i
63 WHERE p.id = i.project_id)
64 AND id NOT IN (SELECT p.id
65 FROM project p, orderitems o
66 WHERE p.id = o.project_id)";
72 $sth = $dbh->prepare($query);
73 $sth->execute || $form->dberror($query);
76 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
77 push @{ $form->{project_list} }, $ref;
90 my ($self, $myconfig, $form) = @_;
93 my $dbh = $form->dbconnect($myconfig);
95 my $query = qq|SELECT *
97 WHERE id = $form->{id}|;
98 my $sth = $dbh->prepare($query);
99 $sth->execute || $form->dberror($query);
101 my $ref = $sth->fetchrow_hashref(NAME_lc);
103 map { $form->{$_} = $ref->{$_} } keys %$ref;
107 # check if it is orphaned
108 $query = qq|SELECT count(*)
110 WHERE project_id = $form->{id}
114 WHERE project_id = $form->{id}
118 WHERE project_id = $form->{id}
120 $sth = $dbh->prepare($query);
121 $sth->execute || $form->dberror($query);
123 while (my ($count) = $sth->fetchrow_array) {
124 $form->{orphaned} += $count;
127 $form->{orphaned} = !$form->{orphaned};
135 my ($self, $myconfig, $form) = @_;
137 # connect to database
138 my $dbh = $form->dbconnect($myconfig);
141 $query = qq|UPDATE project SET
142 projectnumber = |.$dbh->quote($form->{projectnumber}).qq|,
143 description = |.$dbh->quote($form->{description}).qq|
144 WHERE id = $form->{id}|;
146 $query = qq|INSERT INTO project
147 (projectnumber, description)
149 .$dbh->quote($form->{projectnumber}).qq|, |
150 .$dbh->quote($form->{description}).qq|)|;
152 $dbh->do($query) || $form->dberror($query);
160 my ($self, $myconfig, $form) = @_;
164 # connect to database
165 my $dbh = $form->dbconnect($myconfig);
167 $form->{sort} = "partsgroup" unless $form->{partsgroup};
168 my @a = (partsgroup);
169 my $sortorder = $form->sort_order(\@a);
171 my $query = qq|SELECT g.*
176 if ($form->{partsgroup}) {
177 $var = $form->like(lc $form->{partsgroup});
178 $where .= " AND lower(partsgroup) LIKE '$var'";
182 ORDER BY $sortorder|;
184 if ($form->{status} eq 'orphaned') {
185 $query = qq|SELECT g.*
187 LEFT JOIN parts p ON (p.partsgroup_id = g.id)
192 JOIN parts p ON (p.partsgroup_id = g.id)
194 ORDER BY $sortorder|;
197 $sth = $dbh->prepare($query);
198 $sth->execute || $form->dberror($query);
201 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
202 push @{ $form->{item_list} }, $ref;
214 sub save_partsgroup {
215 my ($self, $myconfig, $form) = @_;
217 # connect to database
218 my $dbh = $form->dbconnect($myconfig);
221 $query = qq|UPDATE partsgroup SET
222 partsgroup = |.$dbh->quote($form->{partsgroup}).qq|
223 WHERE id = $form->{id}|;
225 $query = qq|INSERT INTO partsgroup
227 VALUES (|.$dbh->quote($form->{partsgroup}).qq|)|;
229 $dbh->do($query) || $form->dberror($query);
237 my ($self, $myconfig, $form) = @_;
239 # connect to database
240 my $dbh = $form->dbconnect($myconfig);
242 my $query = qq|SELECT *
244 WHERE id = $form->{id}|;
245 my $sth = $dbh->prepare($query);
246 $sth->execute || $form->dberror($query);
248 my $ref = $sth->fetchrow_hashref(NAME_lc);
250 map { $form->{$_} = $ref->{$_} } keys %$ref;
254 # check if it is orphaned
255 $query = qq|SELECT count(*)
257 WHERE partsgroup_id = $form->{id}|;
258 $sth = $dbh->prepare($query);
259 $sth->execute || $form->dberror($query);
261 ($form->{orphaned}) = $sth->fetchrow_array;
262 $form->{orphaned} = !$form->{orphaned};
272 my ($self, $myconfig, $form) = @_;
276 # connect to database
277 my $dbh = $form->dbconnect($myconfig);
279 $form->{sort} = "pricegroup" unless $form->{sort};
280 my @a = (pricegroup);
281 my $sortorder = $form->sort_order(\@a);
283 my $query = qq|SELECT g.*
288 if ($form->{pricegroup}) {
289 $var = $form->like(lc $form->{pricegroup});
290 $where .= " AND lower(pricegroup) LIKE '$var'";
294 ORDER BY $sortorder|;
296 if ($form->{status} eq 'orphaned') {
297 $query = qq|SELECT g.*
300 AND g.id NOT IN (SELECT DISTINCT pricegroup_id
302 ORDER BY $sortorder|;
305 $sth = $dbh->prepare($query);
306 $sth->execute || $form->dberror($query);
309 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
310 push @{ $form->{item_list} }, $ref;
322 sub save_pricegroup {
323 my ($self, $myconfig, $form) = @_;
325 # connect to database
326 my $dbh = $form->dbconnect($myconfig);
329 $query = qq|UPDATE pricegroup SET
330 pricegroup = |.$dbh->quote($form->{pricegroup}).qq|
331 WHERE id = $form->{id}|;
333 $query = qq|INSERT INTO pricegroup
335 VALUES (|.$dbh->quote($form->{pricegroup}).qq|)|;
337 $dbh->do($query) || $form->dberror($query);
345 my ($self, $myconfig, $form) = @_;
347 # connect to database
348 my $dbh = $form->dbconnect($myconfig);
350 my $query = qq|SELECT *
352 WHERE id = $form->{id}|;
353 my $sth = $dbh->prepare($query);
354 $sth->execute || $form->dberror($query);
356 my $ref = $sth->fetchrow_hashref(NAME_lc);
358 map { $form->{$_} = $ref->{$_} } keys %$ref;
362 # check if it is orphaned
363 $query = qq|SELECT count(*)
365 WHERE pricegroup_id = $form->{id}|;
366 $sth = $dbh->prepare($query);
367 $sth->execute || $form->dberror($query);
369 ($form->{orphaned}) = $sth->fetchrow_array;
370 $form->{orphaned} = !$form->{orphaned};
380 my ($self, $myconfig, $form) = @_;
382 # connect to database
383 my $dbh = $form->dbconnect_noauto($myconfig);
385 $query = qq|DELETE FROM $form->{type}
386 WHERE id = $form->{id}|;
387 $dbh->do($query) || $form->dberror($query);
389 if ($form->{type} !~ /pricegroup/) {
390 $query = qq|DELETE FROM translation
391 WHERE trans_id = $form->{id}|;
392 $dbh->do($query) || $form->dberror($query);
401 sub description_translations {
402 my ($self, $myconfig, $form) = @_;
404 my $where = "1 = 1\n";
408 map { $where .= "AND lower(p.$_) LIKE '".$form->like(lc $form->{$_})."'\n" if $form->{$_} } qw(partnumber description);
410 $where .= " AND p.obsolete = '0'";
411 $where .= " AND p.id = $form->{id}" if $form->{id};
413 # connect to database
414 my $dbh = $form->dbconnect($myconfig);
416 my %ordinal = ( 'partnumber' => 2,
420 my @a = qw(partnumber description);
421 my $sortorder = $form->sort_order(\@a, \%ordinal);
423 my $query = qq|SELECT l.description AS language, t.description AS translation,
426 JOIN language l ON (l.code = t.language_code)
429 my $tth = $dbh->prepare($query);
431 $query = qq|SELECT p.id, p.partnumber, p.description
434 ORDER BY $sortorder|;
436 my $sth = $dbh->prepare($query);
437 $sth->execute || $form->dberror($query);
441 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
442 push @{ $form->{translations} }, $ref;
444 # get translations for description
445 $tth->execute($ref->{id}) || $form->dberror;
447 while ($tra = $tth->fetchrow_hashref(NAME_lc)) {
448 $form->{trans_id} = $ref->{id};
449 $tra->{id} = $ref->{id};
450 push @{ $form->{translations} }, $tra;
456 &get_language("", $dbh, $form) if $form->{id};
463 sub partsgroup_translations {
464 my ($self, $myconfig, $form) = @_;
466 my $where = "1 = 1\n";
469 if ($form->{description}) {
470 $where .= "AND lower(p.partsgroup) LIKE '".$form->like(lc $form->{description})."'";
472 $where .= " AND p.id = $form->{id}" if $form->{id};
474 # connect to database
475 my $dbh = $form->dbconnect($myconfig);
477 my $query = qq|SELECT l.description AS language, t.description AS translation,
480 JOIN language l ON (l.code = t.language_code)
483 my $tth = $dbh->prepare($query);
487 $query = qq|SELECT p.id, p.partsgroup AS description
490 ORDER BY 2 $form->{direction}|;
492 my $sth = $dbh->prepare($query);
493 $sth->execute || $form->dberror($query);
497 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
498 push @{ $form->{translations} }, $ref;
500 # get translations for partsgroup
501 $tth->execute($ref->{id}) || $form->dberror;
503 while ($tra = $tth->fetchrow_hashref(NAME_lc)) {
504 $form->{trans_id} = $ref->{id};
505 push @{ $form->{translations} }, $tra;
511 &get_language("", $dbh, $form) if $form->{id};
518 sub project_translations {
519 my ($self, $myconfig, $form) = @_;
521 my $where = "1 = 1\n";
525 map { $where .= "AND lower(p.$_) LIKE '".$form->like(lc $form->{$_})."'\n" if $form->{$_} } qw(projectnumber description);
527 $where .= " AND p.id = $form->{id}" if $form->{id};
529 # connect to database
530 my $dbh = $form->dbconnect($myconfig);
532 my %ordinal = ( 'projectnumber' => 2,
536 my @a = qw(projectnumber description);
537 my $sortorder = $form->sort_order(\@a, \%ordinal);
539 my $query = qq|SELECT l.description AS language, t.description AS translation,
542 JOIN language l ON (l.code = t.language_code)
545 my $tth = $dbh->prepare($query);
547 $query = qq|SELECT p.id, p.projectnumber, p.description
550 ORDER BY $sortorder|;
552 my $sth = $dbh->prepare($query);
553 $sth->execute || $form->dberror($query);
557 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
558 push @{ $form->{translations} }, $ref;
560 # get translations for description
561 $tth->execute($ref->{id}) || $form->dberror;
563 while ($tra = $tth->fetchrow_hashref(NAME_lc)) {
564 $form->{trans_id} = $ref->{id};
565 $tra->{id} = $ref->{id};
566 push @{ $form->{translations} }, $tra;
572 &get_language("", $dbh, $form) if $form->{id};
580 my ($self, $dbh, $form) = @_;
583 my $query = qq|SELECT *
586 my $sth = $dbh->prepare($query);
587 $sth->execute || $form->dberror($query);
589 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
590 push @{ $form->{all_language} }, $ref;
597 sub save_translation {
598 my ($self, $myconfig, $form) = @_;
600 # connect to database
601 my $dbh = $form->dbconnect_noauto($myconfig);
603 my $query = qq|DELETE FROM translation
604 WHERE trans_id = $form->{id}|;
605 $dbh->do($query) || $form->dberror($query);
607 $query = qq|INSERT INTO translation (trans_id, language_code, description)
608 VALUES ($form->{id}, ?, ?)|;
609 my $sth = $dbh->prepare($query) || $form->dberror($query);
611 foreach my $i (1 .. $form->{translation_rows}) {
612 if ($form->{"language_code_$i"}) {
613 $sth->execute($form->{"language_code_$i"}, $form->{"translation_$i"});
623 sub delete_translation {
624 my ($self, $myconfig, $form) = @_;
626 # connect to database
627 my $dbh = $form->dbconnect($myconfig);
629 $query = qq|DELETE FROM translation
630 WHERE trans_id = $form->{id}|;
631 $dbh->do($query) || $form->dberror($query);