USTVA Ist-Soll Testskript für Steuerzone Inland überarbeitet

[kivitendo-erp.git] / SL / CT.pm

diff --git a/SL/CT.pm b/SL/CT.pm
index c61f5a4..8a3ff85 100644 (file)
--- a/SL/CT.pm
+++ b/SL/CT.pm
@@ -37,7 +37,7 @@
 
 package CT;
 use Data::Dumper;
-
+use SL::DBUtils;
 
 sub get_tuple {
   $main::lxdebug->enter_sub();
@@ -146,7 +146,7 @@ sub get_tuple {
 
 
   # get shipto address
-  $query = qq|SELECT id, shiptoname
+  $query = qq|SELECT shipto_id, shiptoname, shiptodepartment_1
               FROM shipto WHERE trans_id=$form->{id}|;
   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
@@ -182,10 +182,10 @@ sub get_tuple {
   }
   $sth->finish;
 
-  # get languages
+  # get payment terms
   $query = qq|SELECT id, description
               FROM payment_terms
-             ORDER BY 1|;
+              ORDER BY sortkey|;
   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
@@ -315,7 +315,7 @@ sub taxaccounts {
   # get payment terms
   $query = qq|SELECT id, description
               FROM payment_terms
-             ORDER BY 1|;
+              ORDER BY sortkey|;
   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
@@ -373,14 +373,25 @@ sub save_customer {
   $form->{obsolete}    *= 1;
   $form->{business}    *= 1;
   $form->{salesman_id} *= 1;
-  $form->{language_id} *= 1;
   $form->{payment_id} *= 1;
   $form->{taxzone_id} *= 1;
   $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
 
-  my ($query, $sth);
+  my ($query, $sth, $f_id);
 
   if ($form->{id}) {
+
+    $query = qq|SELECT id FROM customer
+                WHERE customernumber = '$form->{customernumber}'|;
+    $sth = $dbh->prepare($query);
+    $sth->execute || $form->dberror($query);
+    (${f_id}) = $sth->fetchrow_array;
+    $sth->finish;
+    if ((${f_id} ne $form->{id}) and (${f_id} ne "")) {
+
+      $main::lxdebug->leave_sub();
+      return 3;
+    }
     $query = qq|DELETE FROM customertax
                 WHERE customer_id = $form->{id}|;
     $dbh->do($query) || $form->dberror($query);
@@ -389,11 +400,31 @@ sub save_customer {
 #                 WHERE trans_id = $form->{id} AND module = 'CT'|;
 #     $dbh->do($query) || $form->dberror($query);
   } else {
+
     my $uid = rand() . time;
 
     $uid .= $form->{login};
 
     $uid = substr($uid, 2, 75);
+    if (!$form->{customernumber} && $form->{business}) {
+      $form->{customernumber} =
+        $form->update_business($myconfig, $form->{business});
+    }
+    if (!$form->{customernumber}) {
+      $form->{customernumber} =
+        $form->update_defaults($myconfig, "customernumber");
+    }
+
+    $query = qq|SELECT c.id FROM customer c
+                WHERE c.customernumber = '$form->{customernumber}'|;
+    $sth = $dbh->prepare($query);
+    $sth->execute || $form->dberror($query);
+    (${f_id}) = $sth->fetchrow_array;
+    $sth->finish;
+    if (${f_id} ne "") {
+      $main::lxdebug->leave_sub();
+      return 3;
+    }
 
     $query = qq|INSERT INTO customer (name)
                 VALUES ('$uid')|;
@@ -406,17 +437,7 @@ sub save_customer {
 
     ($form->{id}) = $sth->fetchrow_array;
     $sth->finish;
-    if (!$form->{customernumber} && $form->{business}) {
-      $form->{customernumber} =
-        $form->update_business($myconfig, $form->{business});
-    }
-    if (!$form->{customernumber}) {
-      $form->{customernumber} =
-        $form->update_defaults($myconfig, "customernumber");
-    }
-
   }
-
   $query = qq|UPDATE customer SET
               customernumber = '$form->{customernumber}',
              name = '$form->{name}',
@@ -450,7 +471,7 @@ sub save_customer {
               ustid = '$form->{ustid}',
               username = '$form->{username}',
               salesman_id = '$form->{salesman_id}',
-              language_id = '$form->{language_id}',
+              language_id = | . conv_i($form->{language_id}, "NULL") . qq|,
               payment_id = '$form->{payment_id}',
               taxzone_id = '$form->{taxzone_id}',
               user_password = | . $dbh->quote($form->{user_password}) . qq|,
@@ -496,7 +517,6 @@ sub save_customer {
       $dbh->do($query) || $form->dberror($query);
     }
   }
-  print(STDERR "SHIPTO_ID $form->{shipto_id}\n");
   # add shipto
   $form->add_shipto($dbh, $form->{id}, "CT");
 
@@ -531,7 +551,6 @@ sub save_vendor {
   $form->{obsolete}    *= 1;
   $form->{business}    *= 1;
   $form->{payment_id}    *= 1;
-  $form->{language_id}    *= 1;
   $form->{taxzone_id}    *= 1;
   $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
 
@@ -604,7 +623,7 @@ sub save_vendor {
               ustid = '$form->{ustid}',
               payment_id = '$form->{payment_id}',
               taxzone_id = '$form->{taxzone_id}',
-              language_id = '$form->{language_id}',
+              language_id = | . conv_i($form->{language_id}, "NULL") . qq|,
               username = '$form->{username}',
               user_password = '$form->{user_password}',
               v_customer_id = '$form->{v_customer_id}'
@@ -640,7 +659,7 @@ sub save_vendor {
   }
 
   # add shipto
-  $form->add_shipto($dbh, $form->{id});
+  $form->add_shipto($dbh, $form->{id}, "CT");
 
   $rc = $dbh->disconnect;
 
@@ -676,6 +695,9 @@ sub search {
 
   my $where = "1 = 1";
   $form->{sort} = "name" unless ($form->{sort});
+  if ($form->{sort} eq "address") {
+    $form->{sort} = "country,city,street";
+  }
 
   if ($form->{"$form->{db}number"}) {
     my $companynumber = $form->like(lc $form->{"$form->{db}number"});
@@ -819,7 +841,8 @@ sub get_shipto {
   my $dbh   = $form->dbconnect($myconfig);
   my $query = qq|SELECT s.*
                  FROM shipto s
-                WHERE s.id = $form->{shipto_id}  order by s.id limit 1|;
+                WHERE s.shipto_id = $form->{shipto_id}|;
+                #WHERE s.shipto_id = $form->{shipto_id}  order by s.shipto_id limit 1|;
   my $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
@@ -838,18 +861,23 @@ sub get_delivery {
 
   my ($self, $myconfig, $form) = @_;
   my $dbh   = $form->dbconnect($myconfig);
+  $tabelle = ($form->{db} eq "vendor") ? "ap" : "ar";
+
   $where = " WHERE 1=1 ";
-  if ($form->{shipto_id}) {
-    $where .= "AND ar.shipto_id=$form->{shipto_id} ";
+  if ($form->{shipto_id} && $tabelle eq "ar") {
+    $where .= "AND $tabelle.shipto_id=$form->{shipto_id} ";
+  } else {
+       $where .="AND $tabelle.$form->{db}_id=$form->{id} ";
   }
   if ($form->{from}) {
-    $where .= "AND ar.transdate >= '$form->{from}' ";
+    $where .= "AND $tabelle.transdate >= '$form->{from}' ";
   }
   if ($form->{to}) {
-    $where .= "AND ar.transdate <= '$form->{to}' ";
+    $where .= "AND $tabelle.transdate <= '$form->{to}' ";
   }
-
-  my $query = qq|select shiptoname, adr_code, ar.transdate, ar.invnumber, ar.ordnumber, invoice.description, qty, invoice.unit FROM ar LEFT join shipto ON (ar.shipto_id=shipto.id) LEFT join invoice on (ar.id=invoice.trans_id) LEFT join parts ON (parts.id=invoice.parts_id) LEFT join adr ON (parts.adr_id=adr.id) $where ORDER BY ar.transdate DESC LIMIT 15|;
+  my $query = qq|select shiptoname, $tabelle.transdate, $tabelle.invnumber, $tabelle.ordnumber, invoice.description, qty, invoice.unit FROM $tabelle LEFT JOIN shipto ON |;
+  $query .= ($tabelle eq "ar") ? qq|($tabelle.shipto_id=shipto.shipto_id) |:qq|($tabelle.id=shipto.trans_id) |;
+  $query .=qq|LEFT join invoice on ($tabelle.id=invoice.trans_id) LEFT join parts ON (parts.id=invoice.parts_id) $where ORDER BY $tabelle.transdate DESC LIMIT 15|;
   my $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
@@ -863,35 +891,5 @@ sub get_delivery {
   $main::lxdebug->leave_sub();
 }
 
-sub adr {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $myconfig, $form) = @_;
-  my $dbh   = $form->dbconnect($myconfig);
-  $where = " WHERE 1=1 ";
-  if ($form->{from}) {
-    $where .= "AND ar.transdate >= '$form->{from}' ";
-  }
-  if ($form->{to}) {
-    $where .= "AND ar.transdate <= '$form->{to}' ";
-  }
-  if ($form->{year}) {
-    $where = " WHERE ar.transdate >= '$form->{year}-01-01' AND ar.transdate <= '$form->{year}-12-31' ";
-  }
-
-  my $query = qq|select adr_code, adr_description, sum(base_qty), parts.unit from ar LEFT join invoice on (ar.id=invoice.trans_id) LEFT join parts ON (invoice.parts_id=parts.id) LEFT join adr ON (adr.id=parts.adr_id) $where GROUP BY adr_code,adr_description,parts.unit|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-
-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-    push @{ $form->{ADR} }, $ref;
-  }
-  $sth->finish;
-  $dbh->disconnect;
-
-  $main::lxdebug->leave_sub();
-}
-
 1;