X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FPE.pm;h=5a23fb749c9254b2a5a6f118804a862e206d18c0;hb=99a4c7b96e309e52db255c18f17467e86eeb284f;hp=7e69fd70775b1ef03305c93883a1586415bf37ad;hpb=4dbb09950c9f5596646537c12d991c99086fe7c1;p=kivitendo-erp.git

diff --git a/SL/PE.pm b/SL/PE.pm
index 7e69fd707..5a23fb749 100644
--- a/SL/PE.pm
+++ b/SL/PE.pm
@@ -35,12 +35,13 @@
 
 package PE;
 
+use Data::Dumper;
 
 sub projects {
   $main::lxdebug->enter_sub();
 
   my ($self, $myconfig, $form) = @_;
-  
+
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
@@ -84,13 +85,12 @@ sub projects {
 
   $sth->finish;
   $dbh->disconnect;
-  
+
   $main::lxdebug->leave_sub();
 
   return $i;
 }
 
-
 sub get_project {
   $main::lxdebug->enter_sub();
 
@@ -98,7 +98,7 @@ sub get_project {
 
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
-  
+
   my $query = qq|SELECT p.*
                  FROM project p
 	         WHERE p.id = $form->{id}|;
@@ -106,7 +106,7 @@ sub get_project {
   $sth->execute || $form->dberror($query);
 
   my $ref = $sth->fetchrow_hashref(NAME_lc);
-  
+
   map { $form->{$_} = $ref->{$_} } keys %$ref;
 
   $sth->finish;
@@ -120,24 +120,23 @@ sub get_project {
 
   ($form->{orphaned}) = $sth->fetchrow_array;
   $form->{orphaned} = !$form->{orphaned};
-       
+
   $sth->finish;
-  
+
   $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);
-  
-  map { $form->{$_} =~ s/\'/\'\'/g } (projectnumber, description);
+
+  map { $form->{$_} =~ s/\'/\'\'/g } qw(projectnumber description);
 
   if ($form->{id}) {
     $query = qq|UPDATE project SET
@@ -150,20 +149,19 @@ sub save_project {
                 VALUES ('$form->{projectnumber}', '$form->{description}')|;
   }
   $dbh->do($query) || $form->dberror($query);
-  
+
   $dbh->disconnect;
 
   $main::lxdebug->leave_sub();
 }
 
-
 sub partsgroups {
   $main::lxdebug->enter_sub();
 
   my ($self, $myconfig, $form) = @_;
-  
+
   my $var;
-  
+
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
@@ -173,7 +171,7 @@ sub partsgroups {
                  FROM partsgroup g|;
 
   my $where = "1 = 1";
-  
+
   if ($form->{partsgroup}) {
     $var = $form->like(lc $form->{partsgroup});
     $where .= " AND lower(g.partsgroup) LIKE '$var'";
@@ -181,7 +179,7 @@ sub partsgroups {
   $query .= qq|
                WHERE $where
 	       ORDER BY $sortorder|;
-  
+
   if ($form->{status} eq 'orphaned') {
     $query = qq|SELECT g.*
                 FROM partsgroup g
@@ -206,23 +204,21 @@ sub partsgroups {
 
   $sth->finish;
   $dbh->disconnect;
-  
+
   $main::lxdebug->leave_sub();
 
   return $i;
 }
 
-
 sub save_partsgroup {
   $main::lxdebug->enter_sub();
 
   my ($self, $myconfig, $form) = @_;
-  
+
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
-  
-  map { $form->{$_} =~ s/\'/\'\'/g } (partsgroup);
 
+  map { $form->{$_} =~ s/\'/\'\'/g } qw(partsgroup);
   $form->{discount} /= 100;
 
   if ($form->{id}) {
@@ -235,13 +231,12 @@ sub save_partsgroup {
                 VALUES ('$form->{partsgroup}')|;
   }
   $dbh->do($query) || $form->dberror($query);
-  
+
   $dbh->disconnect;
 
   $main::lxdebug->leave_sub();
 }
 
-
 sub get_partsgroup {
   $main::lxdebug->enter_sub();
 
@@ -249,7 +244,7 @@ sub get_partsgroup {
 
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
-  
+
   my $query = qq|SELECT p.*
                  FROM partsgroup p
 	         WHERE p.id = $form->{id}|;
@@ -257,7 +252,7 @@ sub get_partsgroup {
   $sth->execute || $form->dberror($query);
 
   my $ref = $sth->fetchrow_hashref(NAME_lc);
- 
+
   map { $form->{$_} = $ref->{$_} } keys %$ref;
 
   $sth->finish;
@@ -271,34 +266,157 @@ sub get_partsgroup {
 
   ($form->{orphaned}) = $sth->fetchrow_array;
   $form->{orphaned} = !$form->{orphaned};
-       
+
   $sth->finish;
-  
+
   $dbh->disconnect;
 
   $main::lxdebug->leave_sub();
 }
 
-
-
 sub delete_tuple {
   $main::lxdebug->enter_sub();
 
   my ($self, $myconfig, $form) = @_;
-  
+
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
-  
+
   $query = qq|DELETE FROM $form->{type}
 	      WHERE id = $form->{id}|;
   $dbh->do($query) || $form->dberror($query);
-  
+
+  $dbh->disconnect;
+
+  $main::lxdebug->leave_sub();
+}
+
+##########################
+# get pricegroups from database
+#
+sub pricegroups {
+  $main::lxdebug->enter_sub();
+
+  my ($self, $myconfig, $form) = @_;
+
+  my $var;
+
+  # connect to database
+  my $dbh = $form->dbconnect($myconfig);
+
+  my $sortorder = ($form->{sort}) ? $form->{sort} : "pricegroup";
+
+  my $query = qq|SELECT g.id, g.pricegroup
+                 FROM pricegroup g|;
+
+  my $where = "1 = 1";
+
+  if ($form->{pricegroup}) {
+    $var = $form->like(lc $form->{pricegroup});
+    $where .= " AND lower(g.pricegroup) LIKE '$var'";
+  }
+  $query .= qq|
+               WHERE $where
+	       ORDER BY $sortorder|;
+
+  if ($form->{status} eq 'orphaned') {
+    $query = qq|SELECT pg.*
+                FROM pricegroup pg
+                LEFT JOIN prices p ON (p.pricegroup_id = pg.id)
+		WHERE $where
+                EXCEPT
+                SELECT pg.*
+	        FROM pricegroup pg
+	        JOIN prices p ON (p.pricegroup_id = pg.id)
+	        WHERE $where
+		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;
+
+  $main::lxdebug->leave_sub();
+
+  return $i;
+}
+########################
+# save pricegruop to database
+#
+sub save_pricegroup {
+  $main::lxdebug->enter_sub();
+
+  my ($self, $myconfig, $form) = @_;
+
+  # connect to database
+  my $dbh = $form->dbconnect($myconfig);
+
+  map { $form->{$_} =~ s/\'/\'\'/g } qw(pricegroup);
+
+  $form->{discount} /= 100;
+
+  if ($form->{id}) {
+    $query = qq|UPDATE pricegroup SET
+                pricegroup = '$form->{pricegroup}'
+		WHERE id = $form->{id}|;
+  } else {
+    $query = qq|INSERT INTO pricegroup
+                (pricegroup)
+                VALUES ('$form->{pricegroup}')|;
+  }
+  $dbh->do($query) || $form->dberror($query);
+
   $dbh->disconnect;
 
   $main::lxdebug->leave_sub();
 }
+############################
+# get one pricegroup from database
+#
+sub get_pricegroup {
+  $main::lxdebug->enter_sub();
+
+  my ($self, $myconfig, $form) = @_;
+
+  # connect to database
+  my $dbh = $form->dbconnect($myconfig);
 
+  my $query = qq|SELECT p.id, p.pricegroup
+                 FROM pricegroup p
+	         WHERE p.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 prices p
+	      WHERE p.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;
+
+  $main::lxdebug->leave_sub();
+}
 
 1;