X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=sql-ledger%2FSL%2FPE.pm;h=f0850a7cf2d46663c563ab60e7a67f9b19736382;hp=dec04bb4fb154fac3b7f820e288245bfadad7d9e;hb=32306b5f8ffe4ce594409aa6e89626740b225a39;hpb=52072fcd26f2faf57923f598c358e7f47c4e2643 diff --git a/sql-ledger/SL/PE.pm b/sql-ledger/SL/PE.pm index dec04bb4f..f0850a7cf 100644 --- a/sql-ledger/SL/PE.pm +++ b/sql-ledger/SL/PE.pm @@ -1,6 +1,6 @@ #===================================================================== # SQL-Ledger Accounting -# Copyright (C) 1998-2002 +# Copyright (C) 2003 # # Author: Dieter Simader # Email: dsimader@sql-ledger.org @@ -36,7 +36,11 @@ sub projects { # connect to database my $dbh = $form->dbconnect($myconfig); - my $sortorder = ($form->{sort}) ? $form->{sort} : "projectnumber"; + $form->{sort} = "projectnumber" unless $form->{sort}; + my @a = ($form->{sort}); + my %ordinal = ( projectnumber => 2, + description => 3 ); + my $sortorder = $form->sort_order(\@a, \%ordinal); my $query = qq|SELECT id, projectnumber, description FROM project @@ -103,14 +107,24 @@ sub get_project { # check if it is orphaned $query = qq|SELECT count(*) FROM acc_trans - WHERE project_id = $form->{id}|; + WHERE project_id = $form->{id} + UNION + SELECT count(*) + FROM invoice + WHERE project_id = $form->{id} + UNION + SELECT count(*) + FROM orderitems + WHERE project_id = $form->{id} + |; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - ($form->{orphaned}) = $sth->fetchrow_array; - $form->{orphaned} = !$form->{orphaned}; - + while (my ($count) = $sth->fetchrow_array) { + $form->{orphaned} += $count; + } $sth->finish; + $form->{orphaned} = !$form->{orphaned}; $dbh->disconnect; @@ -123,17 +137,17 @@ sub save_project { # connect to database my $dbh = $form->dbconnect($myconfig); - map { $form->{$_} =~ s/'/''/g } (projectnumber, description); - if ($form->{id}) { $query = qq|UPDATE project SET - projectnumber = '$form->{projectnumber}', - description = '$form->{description}' + projectnumber = |.$dbh->quote($form->{projectnumber}).qq|, + description = |.$dbh->quote($form->{description}).qq| WHERE id = $form->{id}|; } else { $query = qq|INSERT INTO project (projectnumber, description) - VALUES ('$form->{projectnumber}', '$form->{description}')|; + VALUES (| + .$dbh->quote($form->{projectnumber}).qq|, | + .$dbh->quote($form->{description}).qq|)|; } $dbh->do($query) || $form->dberror($query); @@ -150,7 +164,9 @@ sub partsgroups { # connect to database my $dbh = $form->dbconnect($myconfig); - my $sortorder = ($form->{sort}) ? $form->{sort} : "partsgroup"; + $form->{sort} = "partsgroup" unless $form->{partsgroup}; + my @a = (partsgroup); + my $sortorder = $form->sort_order(\@a); my $query = qq|SELECT g.* FROM partsgroup g|; @@ -201,17 +217,14 @@ sub save_partsgroup { # connect to database my $dbh = $form->dbconnect($myconfig); - map { $form->{$_} =~ s/'/''/g } (partsgroup); - - if ($form->{id}) { $query = qq|UPDATE partsgroup SET - partsgroup = '$form->{partsgroup}' + partsgroup = |.$dbh->quote($form->{partsgroup}).qq| WHERE id = $form->{id}|; } else { $query = qq|INSERT INTO partsgroup (partsgroup) - VALUES ('$form->{partsgroup}')|; + VALUES (|.$dbh->quote($form->{partsgroup}).qq|)|; } $dbh->do($query) || $form->dberror($query); @@ -255,22 +268,372 @@ sub get_partsgroup { } +sub pricegroups { + my ($self, $myconfig, $form) = @_; + + my $var; + + # connect to database + my $dbh = $form->dbconnect($myconfig); -sub delete_tuple { + $form->{sort} = "pricegroup" unless $form->{sort}; + my @a = (pricegroup); + my $sortorder = $form->sort_order(\@a); + + my $query = qq|SELECT g.* + FROM pricegroup g|; + + my $where = "1 = 1"; + + if ($form->{pricegroup}) { + $var = $form->like(lc $form->{pricegroup}); + $where .= " AND lower(pricegroup) LIKE '$var'"; + } + $query .= qq| + WHERE $where + ORDER BY $sortorder|; + + if ($form->{status} eq 'orphaned') { + $query = qq|SELECT g.* + FROM pricegroup g + WHERE $where + AND g.id NOT IN (SELECT DISTINCT pricegroup_id + FROM partscustomer) + ORDER BY $sortorder|; + } + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $i = 0; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{item_list} }, $ref; + $i++; + } + + $sth->finish; + $dbh->disconnect; + + $i; + +} + + +sub save_pricegroup { my ($self, $myconfig, $form) = @_; # connect to database my $dbh = $form->dbconnect($myconfig); + if ($form->{id}) { + $query = qq|UPDATE pricegroup SET + pricegroup = |.$dbh->quote($form->{pricegroup}).qq| + WHERE id = $form->{id}|; + } else { + $query = qq|INSERT INTO pricegroup + (pricegroup) + VALUES (|.$dbh->quote($form->{pricegroup}).qq|)|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + +} + + +sub get_pricegroup { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT * + FROM pricegroup + WHERE id = $form->{id}|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $ref = $sth->fetchrow_hashref(NAME_lc); + + map { $form->{$_} = $ref->{$_} } keys %$ref; + + $sth->finish; + + # check if it is orphaned + $query = qq|SELECT count(*) + FROM partscustomer + WHERE pricegroup_id = $form->{id}|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + ($form->{orphaned}) = $sth->fetchrow_array; + $form->{orphaned} = !$form->{orphaned}; + + $sth->finish; + + $dbh->disconnect; + +} + + +sub delete_tuple { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + $query = qq|DELETE FROM $form->{type} WHERE id = $form->{id}|; $dbh->do($query) || $form->dberror($query); + if ($form->{type} !~ /pricegroup/) { + $query = qq|DELETE FROM translation + WHERE trans_id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + } + + $dbh->commit; + $dbh->disconnect; + +} + + +sub description_translations { + my ($self, $myconfig, $form) = @_; + + my $where = "1 = 1\n"; + my $var; + my $ref; + + map { $where .= "AND lower(p.$_) LIKE '".$form->like(lc $form->{$_})."'\n" if $form->{$_} } qw(partnumber description); + + $where .= " AND p.obsolete = '0'"; + $where .= " AND p.id = $form->{id}" if $form->{id}; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my %ordinal = ( 'partnumber' => 2, + 'description' => 3 + ); + + my @a = qw(partnumber description); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + my $query = qq|SELECT l.description AS language, t.description AS translation, + l.code + FROM translation t + JOIN language l ON (l.code = t.language_code) + WHERE trans_id = ? + ORDER BY 1|; + my $tth = $dbh->prepare($query); + + $query = qq|SELECT p.id, p.partnumber, p.description + FROM parts p + WHERE $where + ORDER BY $sortorder|; + + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $tra; + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{translations} }, $ref; + + # get translations for description + $tth->execute($ref->{id}) || $form->dberror; + + while ($tra = $tth->fetchrow_hashref(NAME_lc)) { + $form->{trans_id} = $ref->{id}; + $tra->{id} = $ref->{id}; + push @{ $form->{translations} }, $tra; + } + + } + $sth->finish; + + &get_language("", $dbh, $form) if $form->{id}; + + $dbh->disconnect; + +} + + +sub partsgroup_translations { + my ($self, $myconfig, $form) = @_; + + my $where = "1 = 1\n"; + my $ref; + + if ($form->{description}) { + $where .= "AND lower(p.partsgroup) LIKE '".$form->like(lc $form->{description})."'"; + } + $where .= " AND p.id = $form->{id}" if $form->{id}; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT l.description AS language, t.description AS translation, + l.code + FROM translation t + JOIN language l ON (l.code = t.language_code) + WHERE trans_id = ? + ORDER BY 1|; + my $tth = $dbh->prepare($query); + + $form->sort_order(); + + $query = qq|SELECT p.id, p.partsgroup AS description + FROM partsgroup p + WHERE $where + ORDER BY 2 $form->{direction}|; + + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $tra; + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{translations} }, $ref; + + # get translations for partsgroup + $tth->execute($ref->{id}) || $form->dberror; + + while ($tra = $tth->fetchrow_hashref(NAME_lc)) { + $form->{trans_id} = $ref->{id}; + push @{ $form->{translations} }, $tra; + } + + } + $sth->finish; + + &get_language("", $dbh, $form) if $form->{id}; + + $dbh->disconnect; + +} + + +sub project_translations { + my ($self, $myconfig, $form) = @_; + + my $where = "1 = 1\n"; + my $var; + my $ref; + + map { $where .= "AND lower(p.$_) LIKE '".$form->like(lc $form->{$_})."'\n" if $form->{$_} } qw(projectnumber description); + + $where .= " AND p.id = $form->{id}" if $form->{id}; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my %ordinal = ( 'projectnumber' => 2, + 'description' => 3 + ); + + my @a = qw(projectnumber description); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + my $query = qq|SELECT l.description AS language, t.description AS translation, + l.code + FROM translation t + JOIN language l ON (l.code = t.language_code) + WHERE trans_id = ? + ORDER BY 1|; + my $tth = $dbh->prepare($query); + + $query = qq|SELECT p.id, p.projectnumber, p.description + FROM project p + WHERE $where + ORDER BY $sortorder|; + + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $tra; + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{translations} }, $ref; + + # get translations for description + $tth->execute($ref->{id}) || $form->dberror; + + while ($tra = $tth->fetchrow_hashref(NAME_lc)) { + $form->{trans_id} = $ref->{id}; + $tra->{id} = $ref->{id}; + push @{ $form->{translations} }, $tra; + } + + } + $sth->finish; + + &get_language("", $dbh, $form) if $form->{id}; + + $dbh->disconnect; + +} + + +sub get_language { + my ($self, $dbh, $form) = @_; + + # get language + my $query = qq|SELECT * + FROM language + ORDER BY 2|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_language} }, $ref; + } + $sth->finish; + +} + + +sub save_translation { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + + my $query = qq|DELETE FROM translation + WHERE trans_id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $query = qq|INSERT INTO translation (trans_id, language_code, description) + VALUES ($form->{id}, ?, ?)|; + my $sth = $dbh->prepare($query) || $form->dberror($query); + + foreach my $i (1 .. $form->{translation_rows}) { + if ($form->{"language_code_$i"}) { + $sth->execute($form->{"language_code_$i"}, $form->{"translation_$i"}); + $sth->finish; + } + } + $dbh->commit; $dbh->disconnect; } +sub delete_translation { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $query = qq|DELETE FROM translation + WHERE trans_id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + +} + 1;