X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FPE.pm;h=c6de663e1cf90aea79760172b0fb6b091abd2e32;hb=832dce93297e0577b2f7acf6be3612adca7334d9;hp=d04c57683a0104f6468dd9a6b074acc39ad8d6f4;hpb=dbaa476367ecd993631b294344d992483461c78a;p=kivitendo-erp.git

diff --git a/SL/PE.pm b/SL/PE.pm
index d04c57683..c6de663e1 100644
--- a/SL/PE.pm
+++ b/SL/PE.pm
@@ -28,8 +28,7 @@
 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
 #======================================================================
 #
-# Project module
-# also used for partsgroups
+# Partsgroups and pricegroups
 #
 #======================================================================
 
@@ -38,133 +37,9 @@ package PE;
 use Data::Dumper;
 
 use SL::DBUtils;
+use SL::DB;
 
-sub projects {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $myconfig, $form) = @_;
-
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
-
-  my ($where, @values);
-
-  foreach my $column (qw(projectnumber description)) {
-    if ($form->{$column}) {
-      $where .= qq|AND $column ILIKE ? |;
-      push(@values, '%' . $form->{$column} . '%');
-    }
-  }
-
-  if ($form->{status} eq 'orphaned') {
-    my %col_prefix = ("ar" => "global", "ap" => "global", "oe" => "global");
-    my $first = 1;
-
-    $where .= qq|AND id NOT IN (|;
-    foreach my $table (qw(acc_trans invoice orderitems rmaitems ar ap oe)) {
-      $where .= "UNION " unless ($first);
-      $first = 0;
-      $where .=
-        qq|SELECT DISTINCT $col_prefix{$table}project_id FROM $table | .
-        qq|WHERE NOT $col_prefix{$table}project_id ISNULL |;
-    }
-    $where .= qq|) |;
-  }
-
-  if ($form->{active} eq "active") {
-    $where .= qq|AND active |;
-  } elsif ($form->{active} eq "inactive") {
-    $where .= qq|AND NOT active |;
-  }
-
-  substr($where, 0, 4) = "WHERE " if ($where);
-
-  my $sortorder = $form->{sort} ? $form->{sort} : "projectnumber";
-  $sortorder =~ s/[^a-z_]//g;
-  my $query =
-    qq|SELECT id, projectnumber, description, active | .
-    qq|FROM project | .
-    $where .
-    qq|ORDER BY $sortorder|;
-
-  $form->{project_list} =
-    selectall_hashref_query($form, $dbh, $query, @values);
-  $dbh->disconnect;
-
-  $main::lxdebug->leave_sub();
-
-  return scalar(@{ $form->{project_list} });
-}
-
-sub get_project {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $myconfig, $form) = @_;
-
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
-
-  my $query =
-    qq|SELECT * FROM project | .
-    qq|WHERE id = ?|;
-	my @values = ($form->{id});
-  my $sth = $dbh->prepare($query);
-  $sth->execute(@values) || $form->dberror($query);
-
-  my $ref = $sth->fetchrow_hashref(NAME_lc);
-
-  map { $form->{$_} = $ref->{$_} } keys %$ref;
-
-  $sth->finish;
-
-  # check if it is orphaned
-  my %col_prefix = ("ar" => "global", "ap" => "global", "oe" => "global");
-  @values = ();
-  $query = qq|SELECT |;
-  my $first = 1;
-  foreach my $table (qw(acc_trans invoice orderitems rmaitems ar ap oe)) {
-    $query .= " + " unless ($first);
-    $first = 0;
-    $query .=
-      qq|(SELECT COUNT(*) FROM $table | .
-      qq| WHERE $col_prefix{$table}project_id = ?) |;
-    push(@values, $form->{id});
-  }
-
-  ($form->{orphaned}) = selectrow_query($form, $dbh, $query, @values);
-  $form->{orphaned} = !$form->{orphaned};
-
-  $dbh->disconnect;
-
-  $main::lxdebug->leave_sub();
-}
-
-sub save_project {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $myconfig, $form) = @_;
-
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
-
-  my @values = ($form->{projectnumber}, $form->{description});
-
-  if ($form->{id}) {
-    $query =
-      qq|UPDATE project SET projectnumber = ?, description = ?, active = ? | .
-      qq|WHERE id = ?|;
-    push(@values, ($form->{active} ? 't' : 'f'), $form->{id});
-  } else {
-    $query =
-      qq|INSERT INTO project (projectnumber, description, active) | .
-      qq|VALUES (?, ?, 't')|;
-  }
-  do_query($form, $dbh, $query, @values);
-
-  $dbh->disconnect;
-
-  $main::lxdebug->leave_sub();
-}
+use strict;
 
 sub partsgroups {
   $main::lxdebug->enter_sub();
@@ -172,19 +47,22 @@ sub partsgroups {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my ($where, @values);
 
   if ($form->{partsgroup}) {
     $where .= qq| AND partsgroup ILIKE ?|;
-    push(@values, '%' . $form->{partsgroup} . '%');
+    push(@values, like($form->{partsgroup}));
   }
 
   if ($form->{status} eq 'orphaned') {
     $where .=
       qq| AND id NOT IN | .
       qq|  (SELECT DISTINCT partsgroup_id FROM parts | .
+      qq|   WHERE NOT partsgroup_id ISNULL | .
+      qq| UNION | .
+      qq|   SELECT DISTINCT partsgroup_id FROM custom_variable_config_partsgroups | .
       qq|   WHERE NOT partsgroup_id ISNULL) |;
   }
 
@@ -200,8 +78,6 @@ sub partsgroups {
 
   $form->{item_list} = selectall_hashref_query($form, $dbh, $query, @values);
 
-  $dbh->disconnect;
-
   $main::lxdebug->leave_sub();
 
   return scalar(@{ $form->{item_list} });
@@ -213,22 +89,21 @@ sub save_partsgroup {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   $form->{discount} /= 100;
 
   my @values = ($form->{partsgroup});
+  my $query;
 
   if ($form->{id}) {
     $query = qq|UPDATE partsgroup SET partsgroup = ? WHERE id = ?|;
-		push(@values, $form->{id});
+    push(@values, $form->{id});
   } else {
     $query = qq|INSERT INTO partsgroup (partsgroup) VALUES (?)|;
   }
   do_query($form, $dbh, $query, @values);
 
-  $dbh->disconnect;
-
   $main::lxdebug->leave_sub();
 }
 
@@ -238,7 +113,7 @@ sub get_partsgroup {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my $query =
     qq|SELECT pg.*, | .
@@ -247,12 +122,16 @@ sub get_partsgroup {
     qq|WHERE pg.id = ?|;
   my $sth = prepare_execute_query($form, $dbh, $query, $form->{id},
                                   $form->{id});
-  my $ref = $sth->fetchrow_hashref(NAME_lc);
+  my $ref = $sth->fetchrow_hashref("NAME_lc");
 
   map({ $form->{$_} = $ref->{$_} } keys(%{$ref}));
   $sth->finish;
 
-  $dbh->disconnect;
+  # also not orphaned if partsgroup is selected for a cvar filter
+  if ($form->{orphaned}) {
+    my $cvar_count = scalar( @{ SL::DB::PartsGroup->new(id => $form->{id})->custom_variable_configs } );
+    $form->{orphaned} = !$cvar_count;
+  }
 
   $main::lxdebug->leave_sub();
 }
@@ -263,17 +142,15 @@ sub delete_tuple {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
-
-  my $table =
-    $form->{type} eq "project" ? "project" :
-    $form->{type} eq "pricegroup" ? "pricegroup" :
-    "partsgroup";
+  SL::DB->client->with_transaction(sub {
+    my $dbh = SL::DB->client->dbh;
 
-  $query = qq|DELETE FROM $table WHERE id = ?|;
-  do_query($form, $dbh, $query, $form->{id});
+    my $table = $form->{type} eq "pricegroup" ? "pricegroup" : "partsgroup";
 
-  $dbh->disconnect;
+    my $query = qq|DELETE FROM $table WHERE id = ?|;
+    do_query($form, $dbh, $query, $form->{id});
+    1;
+  }) or do { die SL::DB->client->error };
 
   $main::lxdebug->leave_sub();
 }
@@ -287,20 +164,20 @@ sub pricegroups {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my ($where, @values);
 
   if ($form->{pricegroup}) {
     $where .= qq| AND pricegroup ILIKE ?|;
-    push(@values, '%' . $form->{pricegroup} . '%');
+    push(@values, like($form->{pricegroup}));
   }
 
   if ($form->{status} eq 'orphaned') {
     my $first = 1;
 
     $where .= qq| AND id NOT IN (|;
-    foreach my $table (qw(invoice orderitems prices rmaitems)) {
+    foreach my $table (qw(invoice orderitems prices)) {
       $where .= "UNION " unless ($first);
       $first = 0;
       $where .=
@@ -322,8 +199,6 @@ sub pricegroups {
 
   $form->{item_list} = selectall_hashref_query($form, $dbh, $query, @values);
 
-  $dbh->disconnect;
-
   $main::lxdebug->leave_sub();
 
   return scalar(@{ $form->{item_list} });
@@ -337,23 +212,23 @@ sub save_pricegroup {
 
   my ($self, $myconfig, $form) = @_;
 
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
-  my $query;
+  SL::DB->client->with_transaction(sub {
+    my $dbh = SL::DB->client->dbh;
+    my $query;
 
-  $form->{discount} /= 100;
+    $form->{discount} /= 100;
 
-  my @values = ($form->{pricegroup});
+    my @values = ($form->{pricegroup});
 
-  if ($form->{id}) {
-    $query = qq|UPDATE pricegroup SET pricegroup = ? WHERE id = ? |;
-		push(@values, $form->{id});
-  } else {
-    $query = qq|INSERT INTO pricegroup (pricegroup) VALUES (?)|;
-  }
-  do_query($form, $dbh, $query, @values);
-
-  $dbh->disconnect;
+    if ($form->{id}) {
+      $query = qq|UPDATE pricegroup SET pricegroup = ? WHERE id = ? |;
+      push(@values, $form->{id});
+    } else {
+      $query = qq|INSERT INTO pricegroup (pricegroup) VALUES (?)|;
+    }
+    do_query($form, $dbh, $query, @values);
+    1;
+  }) or do { die SL::DB->client->error };
 
   $main::lxdebug->leave_sub();
 }
@@ -367,11 +242,11 @@ sub get_pricegroup {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my $query = qq|SELECT id, pricegroup FROM pricegroup WHERE id = ?|;
   my $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
-  my $ref = $sth->fetchrow_hashref(NAME_lc);
+  my $ref = $sth->fetchrow_hashref("NAME_lc");
 
   map({ $form->{$_} = $ref->{$_} } keys(%{$ref}));
 
@@ -381,7 +256,7 @@ sub get_pricegroup {
 
   my @values = ();
   $query = qq|SELECT |;
-  foreach my $table (qw(invoice orderitems prices rmaitems)) {
+  foreach my $table (qw(invoice orderitems prices)) {
     $query .= " + " unless ($first);
     $first = 0;
     $query .= qq|(SELECT COUNT(*) FROM $table WHERE pricegroup_id = ?) |;
@@ -391,8 +266,6 @@ sub get_pricegroup {
   ($form->{orphaned}) = selectrow_query($form, $dbh, $query, @values);
   $form->{orphaned} = !$form->{orphaned};
 
-  $dbh->disconnect;
-
   $main::lxdebug->leave_sub();
 }