X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/fcbb613d03379b265cb0df7ce0b553ee630d52f3..d331a3d77f0b24a301412799d2e9cc5fa1bf27ba:/SL/CT.pm

diff --git a/SL/CT.pm b/SL/CT.pm
index 93c3fcea2..37550e4af 100644
--- a/SL/CT.pm
+++ b/SL/CT.pm
@@ -68,11 +68,12 @@ sub get_tuple {
   my $ref = $sth->fetchrow_hashref("NAME_lc");
 
   map { $form->{$_} = $ref->{$_} } keys %$ref;
+  $sth->finish;
 
-  # remove any trailing whitespace
-  $form->{curr} =~ s/\s*$//;
+  #get name of currency instead of id:
+  $query = qq|SELECT curr FROM currencies WHERE id=?|;
+  ($form->{curr}) = selectrow_query($form, $dbh, $query, conv_i($form->{curr}));
 
-  $sth->finish;
   if ( $form->{salesman_id} ) {
     my $query =
       qq|SELECT ct.name AS salesman | .
@@ -319,7 +320,7 @@ sub save_customer {
     qq|user_password = ?, | .
     qq|c_vendor_id = ?, | .
     qq|klass = ?, | .
-    qq|curr = ?, | .
+    qq|curr = (SELECT id FROM currencies WHERE curr = ?), | .
     qq|taxincluded_checked = ? | .
     qq|WHERE id = ?|;
   my @values = (
@@ -362,7 +363,7 @@ sub save_customer {
     $form->{user_password},
     $form->{c_vendor_id},
     conv_i($form->{klass}),
-    substr($form->{currency}, 0, 3),
+    $form->{currency},
     $form->{taxincluded_checked} ne '' ? $form->{taxincluded_checked} : undef,
     $form->{id}
     );
@@ -471,7 +472,7 @@ sub save_vendor {
     qq|  username = ?, | .
     qq|  user_password = ?, | .
     qq|  v_customer_id = ?, | .
-    qq|  curr = ? | .
+    qq|  curr = (SELECT id FROM currencies WHERE curr = ?) | .
     qq|WHERE id = ?|;
   my @values = (
     $form->{vendornumber},
@@ -511,7 +512,7 @@ sub save_vendor {
     $form->{username},
     $form->{user_password},
     $form->{v_customer_id},
-    substr($form->{currency}, 0, 3),
+    $form->{currency},
     $form->{id}
     );
   do_query($form, $dbh, $query, @values);
@@ -551,8 +552,19 @@ sub _save_contact {
 
   my @columns = qw(cp_title cp_givenname cp_name cp_email cp_phone1 cp_phone2 cp_abteilung cp_fax
                    cp_mobile1 cp_mobile2 cp_satphone cp_satfax cp_project cp_privatphone cp_privatemail cp_birthday cp_gender
-                   cp_street cp_zipcode cp_city);
-  my @values  = map { $_ eq 'cp_gender' ? ($form->{$_} eq 'f' ? 'f' : 'm') : $form->{$_} } @columns;
+                   cp_street cp_zipcode cp_city cp_position);
+  my @values  = map(
+    {
+      if ( $_ eq 'cp_gender' ) {
+        $form->{$_} eq 'f' ? 'f' : 'm';
+      } elsif ( $_ eq 'cp_birthday' && $form->{cp_birthday} eq '' ) {
+        undef;
+      } else {
+        $form->{$_};
+      }
+    }
+    @columns
+  );
 
   my ($query, $cp_id);
   if ($form->{cp_id}) {
@@ -603,13 +615,37 @@ sub search {
   my $where = "1 = 1";
   my @values;
 
-  my %allowed_sort_columns =
-    map { $_, 1 } qw(
-      id customernumber vendornumber name contact phone fax email street
-      taxnumber business invnumber ordnumber quonumber zipcode city
+  my %allowed_sort_columns = (
+      "id" => "id",
+      "customernumber" => "customernumber",
+      "vendornumber" => "vendornumber",
+      "name" => "ct.name",
+      "contact" => "contact",
+      "phone" => "phone",
+      "fax" => "fax",
+      "email" => "email",
+      "street" => "street",
+      "taxnumber" => "taxnumber",
+      "business" => "business",
+      "invnumber" => "invnumber",
+      "ordnumber" => "ordnumber",
+      "quonumber" => "quonumber",
+      "zipcode" => "zipcode",
+      "city" => "city",
+      "country" => "country",
+      "salesman" => "e.name"
     );
-  my $sortorder    = $form->{sort} && $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
-  $form->{sort} = $sortorder;
+
+  $form->{sort} ||= "name";
+  my $sortorder;
+  if ( $join_records ) {
+    # in UNION case order by hash key, e.g. salesman
+    # the UNION created an implicit select around the result
+    $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
+  } else {
+    # in not UNION case order by hash value, e.g. e.name
+    $sortorder = $allowed_sort_columns{$form->{sort}} ?  $allowed_sort_columns{$form->{sort}} : "ct.name";
+  }
   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
 
   if ($sortorder !~ /(business|id)/ && !$join_records) {
@@ -648,6 +684,19 @@ sub search {
     push @values, ('%' . $form->{addr_city} . '%') x 2;
   }
 
+  if ($form->{addr_country}) {
+    $where .= " AND ((lower(ct.country) LIKE lower(?))
+                     OR
+                     (ct.id IN (
+                        SELECT trans_id
+                        FROM shipto
+                        WHERE (module = 'CT')
+                          AND (lower(shiptocountry) LIKE lower(?))
+                      ))
+                     )";
+    push @values, ('%' . $form->{addr_country} . '%') x 2;
+  }
+
   if ( $form->{status} eq 'orphaned' ) {
     $where .=
       qq| AND ct.id NOT IN | .
@@ -705,10 +754,11 @@ sub search {
   }
 
   my $query =
-    qq|SELECT ct.*, b.description AS business | .
+    qq|SELECT ct.*, b.description AS business, e.name as salesman | .
     (qq|, NULL AS invnumber, NULL AS ordnumber, NULL AS quonumber, NULL AS invid, NULL AS module, NULL AS formtype, NULL AS closed | x!! $join_records) .
     qq|FROM $cv ct | .
     qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
+    qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
     qq|WHERE $where|;
 
   my @saved_values = @values;
@@ -722,13 +772,14 @@ sub search {
       push(@values, @saved_values);
       $query .=
         qq| UNION | .
-        qq|SELECT ct.*, b.description AS business, | .
+        qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
         qq|  a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
         qq|  '$module' AS module, 'invoice' AS formtype, | .
         qq|  (a.amount = a.paid) AS closed | .
         qq|FROM $cv ct | .
         qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
+        qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
         qq|WHERE $where AND (a.invoice = '1')|;
     }
 
@@ -736,12 +787,13 @@ sub search {
       push(@values, @saved_values);
       $query .=
         qq| UNION | .
-        qq|SELECT ct.*, b.description AS business,| .
+        qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
         qq|  ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
         qq|  'oe' AS module, 'order' AS formtype, o.closed | .
         qq|FROM $cv ct | .
         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
+        qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
         qq|WHERE $where AND (o.quotation = '0')|;
     }
 
@@ -749,12 +801,13 @@ sub search {
       push(@values, @saved_values);
       $query .=
         qq| UNION | .
-        qq|SELECT ct.*, b.description AS business, | .
+        qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
         qq|  ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
         qq|  'oe' AS module, 'quotation' AS formtype, o.closed | .
         qq|FROM $cv ct | .
         qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
+        qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
         qq|WHERE $where AND (o.quotation = '1')|;
     }
   }
@@ -1112,7 +1165,7 @@ sub search_contacts {
     'vcnumber'  => 'vcnumber, cp_name, cp_givenname',
     );
 
-  my %sortcols  = map { $_ => 1 } qw(cp_name cp_givenname cp_phone1 cp_phone2 cp_mobile1 cp_email cp_street cp_zipcode cp_city vcname vcnumber);
+  my %sortcols  = map { $_ => 1 } qw(cp_name cp_givenname cp_phone1 cp_phone2 cp_mobile1 cp_email cp_street cp_zipcode cp_city cp_position vcname vcnumber);
 
   my $order_by  = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
   $::form->{sort} = $order_by;