/SL/CT.pm - Diff - projekt kivitendo - wissen

« Zurück | Weiter »

Revision 93a4e424

Von Moritz Bunkus vor mehr als 17 Jahren hinzugefügt

ID 93a4e424476aae2551b2d070133cd42d5d2f3d52
Vorgänger 191a0e0d
Nachfolger e3bd5746

CT.pm auf die Verwendung von parametrisierten Queries zur Vermeidung von SQL injection umgestellt. Dabei Restfunktionalität von customertax und vendortax entfernt. Mehr Datenbankhilfsfunktionen hinzugefügt.

     sub get_tuple {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       my ( $self, $myconfig, $form ) = @_;
       my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
       my $dbh   = $form->dbconnect($myconfig);
       my $query = qq|SELECT ct.*, b.id AS business, cp.*
                      FROM $form->{db} ct
     		 LEFT JOIN business b on ct.business_id = b.id
                      LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
     		 WHERE ct.id = $form->{id}  order by cp.cp_id limit 1|;
       my $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       my $query =
         qq|SELECT ct.*, b.id AS business, cp.* | .
         qq|FROM $cv ct | .
         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
         qq|LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id) | .
         qq|WHERE (ct.id = ?) | .
         qq|ORDER BY cp.cp_id LIMIT 1|;
       my $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
       my $ref = $sth->fetchrow_hashref(NAME_lc);
       map { $form->{$_} = $ref->{$_} } keys %$ref;
       $sth->finish;
       if ($form->{salesman_id}) {
         my $query = qq|SELECT ct.name AS salesman
                       FROM $form->{db} ct
                       WHERE ct.id = $form->{salesman_id}|;
         my $sth = $dbh->prepare($query);
         $sth->execute || $form->dberror($query);
         my ($ref) = $sth->fetchrow_array();
         $form->{salesman} = $ref;
         $sth->finish;
       if ( $form->{salesman_id} ) {
         my $query =
           qq|SELECT ct.name AS salesman | .
           qq|FROM $cv ct | .
           qq|WHERE ct.id = ?|;
         ($form->{salesman}) =
           selectrow_query($form, $dbh, $query, $form->{salesman_id});
+      }
       # check if it is orphaned
       my $arap = ($form->{db} eq 'customer') ? "ar" : "ap";
       $query = qq|SELECT a.id
                   FROM $arap a
     	      JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
     	      WHERE ct.id = $form->{id}
     	    UNION
     	      SELECT a.id
     	      FROM oe a
     	      JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
     	      WHERE ct.id = $form->{id}|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       unless ($sth->fetchrow_array) {
         $form->{status} = "orphaned";
+      }
       $sth->finish;
       # get tax labels
       $query = qq|SELECT c.accno, c.description
                   FROM chart c
     	      JOIN tax t ON (t.chart_id = c.id)
     	      WHERE c.link LIKE '%CT_tax%'
     	      ORDER BY c.accno|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         $form->{taxaccounts} .= "$ref->{accno} ";
         $form->{tax}{ $ref->{accno} }{description} = $ref->{description};
+      }
       $sth->finish;
       chop $form->{taxaccounts};
       # get taxes for customer/vendor
       $query = qq|SELECT c.accno
                   FROM chart c
     	      JOIN $form->{db}tax t ON (t.chart_id = c.id)
     	      WHERE t.$form->{db}_id = $form->{id}|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         $form->{tax}{ $ref->{accno} }{taxable} = 1;
+      }
       $sth->finish;
       # get business types
       $query = qq|SELECT id, description
                   FROM business
     	      ORDER BY 1|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{all_business} }, $ref;
+      }
       $sth->finish;
       my $arap = ( $form->{db} eq 'customer' ) ? "ar" : "ap";
       $query =
         qq|SELECT a.id | .
         qq|FROM $arap a | .
         qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
         qq|WHERE ct.id = ? | .
         qq|UNION | .
         qq|SELECT a.id | .
         qq|FROM oe a | .
         qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
         qq|WHERE ct.id = ?|;
       my ($dummy) = selectrow_query($form, $dbh, $query, $form->{id}, $form->{id});
       $form->{status} = "orphaned" unless ($dummy);
       # get tax zones
       $query = qq|SELECT id, description
                   FROM tax_zones|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       $dbh->disconnect;
       $main::lxdebug->leave_sub();
+    }
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{TAXZONE} }, $ref;
+      }
       $sth->finish;
     sub populate_drop_down_boxes {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form, $provided_dbh) = @_;
       # get shipto address
       $query = qq|SELECT shipto_id, shiptoname, shiptodepartment_1
                   FROM shipto WHERE trans_id=$form->{id}|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect($myconfig);
       # get business types
       $query = qq|SELECT id, description FROM business ORDER BY id|;
       $form->{all_business} = selectall_hashref_query($form, $dbh, $query);
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{SHIPTO} }, $ref;
+      }
       $sth->finish;
       # get tax zones
       $query = qq|SELECT id, description FROM tax_zones|;
       $form->{TAXZONE} = selectall_hashref_query($form, $dbh, $query);
       # get shipto address
       $query =
         qq|SELECT shipto_id, shiptoname, shiptodepartment_1 | .
         qq|FROM shipto WHERE (trans_id = ?) AND (module = 'CT')|;
       $form->{SHIPTO} = selectall_hashref_query($form, $dbh, $query, $form->{id});
       # get contacts
       $query = qq|SELECT cp_id, cp_name
                   FROM contacts WHERE cp_cv_id=$form->{id}|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{CONTACTS} }, $ref;
+      }
       $sth->finish;
       $query  = qq|SELECT cp_id, cp_name FROM contacts WHERE cp_cv_id = ?|;
       $form->{CONTACTS} = selectall_hashref_query($form, $dbh, $query, $form->{id});
       # get languages
       $query = qq|SELECT id, description
                   FROM language
     	      ORDER BY 1|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{languages} }, $ref;
+      }
       $sth->finish;
       $query = qq|SELECT id, description FROM language ORDER BY id|;
       $form->{languages} = selectall_hashref_query($form, $dbh, $query);
       # get payment terms
       $query = qq|SELECT id, description
                   FROM payment_terms
                   ORDER BY sortkey|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{payment_terms} }, $ref;
+      }
       $sth->finish;
       $query = qq|SELECT id, description FROM payment_terms ORDER BY sortkey|;
       $form->{payment_terms} = selectall_hashref_query($form, $dbh, $query);
       $dbh->disconnect;
       $dbh->disconnect() unless ($provided_dbh);
       $main::lxdebug->leave_sub();
+    }
     ## LINET
     sub query_titles_and_greetings {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       my (%tmp,  $ref);
       my ( $self, $myconfig, $form ) = @_;
       my ( %tmp,  $ref );
       my $dbh = $form->dbconnect($myconfig);
       $query =
         "SELECT DISTINCT(c.cp_greeting) FROM contacts c WHERE c.cp_greeting LIKE '%'";
       $sth = $dbh->prepare($query);
       $sth->execute() || $form->dberror($query);
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         next unless ($ref->{cp_greeting} =~ /[a-zA-Z]/);
         $tmp{ $ref->{cp_greeting} } = 1;
+      }
       $sth->finish();
       @{ $form->{GREETINGS} } = sort(keys(%tmp));
       %tmp = ();
         qq|SELECT DISTINCT(cp_greeting) | .
         qq|FROM contacts | .
         qq|WHERE cp_greeting ~ '[a-zA-Z]' | .
         qq|ORDER BY cp_greeting|;
       $form->{GREETINGS} = [ selectall_array_query($form, $dbh, $query) ];
       $query =
         "SELECT greeting FROM customer UNION select greeting FROM vendor";
       $sth = $dbh->prepare($query);
       $sth->execute() || $form->dberror($query);
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         next unless ($ref->{greeting} =~ /[a-zA-Z]/);
         $tmp{ $ref->{greeting} } = 1;
+      }
       $sth->finish();
       @{ $form->{COMPANY_GREETINGS} } = sort(keys(%tmp));
       %tmp = ();
         qq|SELECT DISTINCT(greeting) | .
         qq|FROM customer | .
         qq|WHERE greeting ~ '[a-zA-Z]' | .
         qq|UNION | .
         qq|SELECT DISTINCT(greeting) | .
         qq|FROM vendor | .
         qq|WHERE greeting ~ '[a-zA-Z]' | .
         qq|ORDER BY greeting|;
       my %tmp;
       map({ $tmp{$_} = 1; } selectall_array_query($form, $dbh, $query));
       $form->{COMPANY_GREETINGS} = [ sort(keys(%tmp)) ];
       $query =
         "SELECT DISTINCT(c.cp_title) FROM contacts c WHERE c.cp_title LIKE '%'";
       $sth = $dbh->prepare($query);
       $sth->execute() || $form->dberror($query);
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         next unless ($ref->{cp_title} =~ /[a-zA-Z]/);
         $tmp{ $ref->{cp_title} } = 1;
+      }
       $sth->finish();
       @{ $form->{TITLES} } = sort(keys(%tmp));
       %tmp = ();
         qq|SELECT DISTINCT(cp_title) | .
         qq|FROM contacts | .
         qq|WHERE cp_title ~ '[a-zA-Z]'|;
       $form->{TITLES} = [ selectall_array_query($form, $dbh, $query) ];
       $query =
         "SELECT DISTINCT(c.cp_abteilung) FROM contacts c WHERE c.cp_abteilung LIKE '%'";
       $sth = $dbh->prepare($query);
       $sth->execute() || $form->dberror($query);
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         $tmp{ $ref->{cp_abteilung} } = 1;
+      }
       $sth->finish();
       @{ $form->{DEPARTMENT} } = sort(keys(%tmp));
         qq|SELECT DISTINCT(cp_abteilung) | .
         qq|FROM contacts | .
         qq|WHERE cp_abteilung ~ '[a-zA-Z]'|;
       $form->{DEPARTMENT} = [ selectall_array_query($form, $dbh, $query) ];
       $dbh->disconnect();
       $main::lxdebug->leave_sub();
+    }
     ## /LINET
     sub taxaccounts {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       my $dbh = $form->dbconnect($myconfig);
       # get tax labels
       my $query = qq|SELECT accno, description
                      FROM chart c, tax t
     		 WHERE c.link LIKE '%CT_tax%'
     	         AND c.id = t.chart_id
     		 ORDER BY accno|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       my $ref = ();
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         $form->{taxaccounts} .= "$ref->{accno} ";
         $form->{tax}{ $ref->{accno} }{description} = $ref->{description};
+      }
       $sth->finish;
       chop $form->{taxaccounts};
       # this is just for the selection for type of business
       $query = qq|SELECT id, description
                   FROM business|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{all_business} }, $ref;
+      }
       $sth->finish;
       # get languages
       $query = qq|SELECT id, description
                   FROM language
     	      ORDER BY 1|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{languages} }, $ref;
+      }
       $sth->finish;
       # get payment terms
       $query = qq|SELECT id, description
                   FROM payment_terms
                   ORDER BY sortkey|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{payment_terms} }, $ref;
+      }
       $sth->finish;
       # get taxkeys and description
       $query = qq|SELECT id, description
                   FROM tax_zones|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{TAXZONE} }, $ref;
+      }
       $sth->finish;
       $dbh->disconnect;
       $main::lxdebug->leave_sub();
+    }
     sub save_customer {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       my ( $self, $myconfig, $form ) = @_;
       # set pricegroup to default
       if ($form->{klass}) { }
       else { $form->{klass} = 0; }
       $form->{klass} = 0 unless ($form->{klass});
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
     ##LINET
       map({
           $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
             if ($form->{"selected_cp_${_}"});
       } qw(title greeting abteilung));
       my $dbh = $form->dbconnect_noauto($myconfig);
       map( {
         $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
         if ( $form->{"selected_cp_${_}"} );
            } qw(title greeting abteilung) );
       $form->{"greeting"} = $form->{"selected_company_greeting"}
             if ($form->{"selected_company_greeting"});
+      #
       # escape '
       map { $form->{$_} =~ s/\'/\'\'/g }
         qw(customernumber name street zipcode city country homepage contact notes cp_title cp_greeting language pricegroup);
     ##/LINET
       if ( $form->{"selected_company_greeting"} );
       # assign value discount, terms, creditlimit
       $form->{discount} = $form->parse_amount($myconfig, $form->{discount});
       $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
       $form->{discount} /= 100;
       $form->{terms}       *= 1;
       $form->{taxincluded} *= 1;
       $form->{obsolete}    *= 1;
       $form->{business}    *= 1;
       $form->{salesman_id} *= 1;
       $form->{payment_id} *= 1;
       $form->{taxzone_id} *= 1;
       $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
       my ($query, $sth, $f_id);
       $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
       if ($form->{id}) {
       my ( $query, $sth, $f_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 "")) {
       if ( $form->{id} ) {
         $query = qq|SELECT id FROM customer WHERE customernumber = ?|;
         ($f_id) = selectrow_query($form, $dbh, $query, $form->{customernumber});
         if (($f_id ne $form->{id}) && ($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);
     #     $query = qq|DELETE FROM shipto
     #                 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});
             $form->update_business($myconfig, $form->{business}, $dbh);
+        }
         if (!$form->{customernumber}) {
           $form->{customernumber} =
             $form->update_defaults($myconfig, "customernumber");
             $form->update_defaults($myconfig, "customernumber", $dbh);
+        }
         $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 "") {
         $query  = qq|SELECT c.id FROM customer c WHERE c.customernumber = ?|;
         ($f_id) = selectrow_query($form, $dbh, $query, $form->{customernumber});
         if ($f_id ne "") {
           $main::lxdebug->leave_sub();
           return 3;
+        }
         $query = qq|INSERT INTO customer (name)
                     VALUES ('$uid')|;
         $dbh->do($query) || $form->dberror($query);
         $query = qq|SELECT c.id FROM customer c
                     WHERE c.name = '$uid'|;
         $sth = $dbh->prepare($query);
         $sth->execute || $form->dberror($query);
         ($form->{id}) = $sth->fetchrow_array;
         $sth->finish;
+      }
       $query = qq|UPDATE customer SET
                   customernumber = '$form->{customernumber}',
     	      name = '$form->{name}',
                   greeting = '$form->{greeting}',
                   department_1 = '$form->{department_1}',
                   department_2 = '$form->{department_2}',
     	      street = '$form->{street}',
     	      zipcode = '$form->{zipcode}',
     	      city = '$form->{city}',
     	      country = '$form->{country}',
     	      homepage = '$form->{homepage}',
     	      contact = '$form->{contact}',
     	      phone = '$form->{phone}',
     	      fax = '$form->{fax}',
     	      email = '$form->{email}',
     	      cc = '$form->{cc}',
     	      bcc = '$form->{bcc}',
     	      notes = '$form->{notes}',
     	      discount = $form->{discount},
     	      creditlimit = $form->{creditlimit},
     	      terms = $form->{terms},
     	      taxincluded = '$form->{taxincluded}',
     	      business_id = $form->{business},
     	      taxnumber = '$form->{taxnumber}',
     	      sic_code = '$form->{sic}',
                   language = '$form->{language}',
                   account_number = '$form->{account_number}',
                   bank_code = '$form->{bank_code}',
                   bank = '$form->{bank}',
                   obsolete = '$form->{obsolete}',
                   ustid = '$form->{ustid}',
                   username = '$form->{username}',
                   salesman_id = '$form->{salesman_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|,
                   c_vendor_id = '$form->{c_vendor_id}',
                   klass = '$form->{klass}'
     	      WHERE id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
       if ($form->{cp_id}) {
         $query = qq|UPDATE contacts SET
     		cp_greeting = '$form->{cp_greeting}',
     		cp_title = '$form->{cp_title}',
     		cp_givenname = '$form->{cp_givenname}',
     		cp_name = '$form->{cp_name}',
     		cp_email = '$form->{cp_email}',
     		cp_phone1 = '$form->{cp_phone1}',
     		cp_phone2 = '$form->{cp_phone2}',
                     cp_abteilung = | . $dbh->quote($form->{cp_abteilung}) . qq|,
                     cp_fax = | . $dbh->quote($form->{cp_fax}) . qq|,
                     cp_mobile1 = | . $dbh->quote($form->{cp_mobile1}) . qq|,
                     cp_mobile2 = | . $dbh->quote($form->{cp_mobile2}) . qq|,
                     cp_satphone = | . $dbh->quote($form->{cp_satphone}) . qq|,
                     cp_satfax = | . $dbh->quote($form->{cp_satfax}) . qq|,
                     cp_project = | . $dbh->quote($form->{cp_project}) . qq|,
                     cp_privatphone = | . $dbh->quote($form->{cp_privatphone}) . qq|,
                     cp_privatemail = | . $dbh->quote($form->{cp_privatemail}) . qq|,
                     cp_birthday = | . $dbh->quote($form->{cp_birthday}) . qq|
             	WHERE cp_id = $form->{cp_id}|;
       } elsif ($form->{cp_name} || $form->{cp_givenname}) {
         $query = qq|SELECT nextval('id')|;
         ($form->{id}) = selectrow_query($form, $dbh, $query);
         $query = qq|INSERT INTO customer (id, name) VALUES (?, '')|;
         do_query($form, $dbh, $query, $form->{id});
+      }
       $query = qq|UPDATE customer SET | .
         qq|customernumber = ?, | .
         qq|name = ?, | .
         qq|greeting = ?, | .
         qq|department_1 = ?, | .
         qq|department_2 = ?, | .
         qq|street = ?, | .
         qq|zipcode = ?, | .
         qq|city = ?, | .
         qq|country = ?, | .
         qq|homepage = ?, | .
         qq|contact = ?, | .
         qq|phone = ?, | .
         qq|fax = ?, | .
         qq|email = ?, | .
         qq|cc = ?, | .
         qq|bcc = ?, | .
         qq|notes = ?, | .
         qq|discount = ?, | .
         qq|creditlimit = ?, | .
         qq|terms = ?, | .
         qq|business_id = ?, | .
         qq|taxnumber = ?, | .
         qq|sic_code = ?, | .
         qq|language = ?, | .
         qq|account_number = ?, | .
         qq|bank_code = ?, | .
         qq|bank = ?, | .
         qq|obsolete = ?, | .
         qq|ustid = ?, | .
         qq|username = ?, | .
         qq|salesman_id = ?, | .
         qq|language_id = ?, | .
         qq|payment_id = ?, | .
         qq|taxzone_id = ?, | .
         qq|user_password = ?, | .
         qq|c_vendor_id = ?, | .
         qq|klass = ? | .
         qq|WHERE id = ?|;
       my @values = (
         $form->{customernumber},
         $form->{name},
         $form->{greeting},
         $form->{department_1},
         $form->{department_2},
         $form->{street},
         $form->{zipcode},
         $form->{city},
         $form->{country},
         $form->{homepage},
         $form->{contact},
         $form->{phone},
         $form->{fax},
         $form->{email},
         $form->{cc},
         $form->{bcc},
         $form->{notes},
         $form->{discount},
         $form->{creditlimit},
         conv_i($form->{terms}),
         conv_i($form->{business}),
         $form->{taxnumber},
         $form->{sic},
         $form->{language},
         $form->{account_number},
         $form->{bank_code},
         $form->{bank},
         $form->{obsolete} ? 't' : 'f',
         $form->{ustid},
         $form->{username},
         conv_i($form->{salesman_id}),
         conv_i($form->{language_id}),
         conv_i($form->{payment_id}),
         conv_i($form->{taxzone_id}),
         $form->{user_password},
         $form->{c_vendor_id},
         conv_i($form->{klass}),
         $form->{id}
         );
       do_query( $form, $dbh, $query, @values );
       $query = undef;
       if ( $form->{cp_id} ) {
         $query = qq|UPDATE contacts SET | .
           qq|cp_greeting = ?, | .
           qq|cp_title = ?,  | .
           qq|cp_givenname = ?, | .
           qq|cp_name = ?, | .
           qq|cp_email = ?, | .
           qq|cp_phone1 = ?, | .
           qq|cp_phone2 = ?, | .
           qq|cp_abteilung = ?, | .
           qq|cp_fax = ?, | .
           qq|cp_mobile1 = ?, | .
           qq|cp_mobile2 = ?, | .
           qq|cp_satphone = ?, | .
           qq|cp_satfax = ?, | .
           qq|cp_project = ?, | .
           qq|cp_privatphone = ?, | .
           qq|cp_privatemail = ?, | .
           qq|cp_birthday = ? | .
           qq|WHERE cp_id = ?|;
         @values = (
           $form->{cp_greeting},
           $form->{cp_title},
           $form->{cp_givenname},
           $form->{cp_name},
           $form->{cp_email},
           $form->{cp_phone1},
           $form->{cp_phone2},
           $form->{cp_abteilung},
           $form->{cp_fax},
           $form->{cp_mobile1},
           $form->{cp_mobile2},
           $form->{cp_satphone},
           $form->{cp_satfax},
           $form->{cp_project},
           $form->{cp_privatphone},
           $form->{cp_privatemail},
           $form->{cp_birthday},
           $form->{cp_id}
           );
       } elsif ( $form->{cp_name} || $form->{cp_givenname} ) {
         $query =
           qq|INSERT INTO contacts ( cp_cv_id, cp_greeting, 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)
     		  VALUES ($form->{id}, '$form->{cp_greeting}','$form->{cp_title}','$form->{cp_givenname}','$form->{cp_name}','$form->{cp_email}','$form->{cp_phone1}','$form->{cp_phone2}', '$form->{cp_abteilung}', | . $dbh->quote($form->{cp_fax}) . qq|,| . $dbh->quote($form->{cp_mobile1}) . qq|,| . $dbh->quote($form->{cp_mobile2}) . qq|,| . $dbh->quote($form->{cp_satphone}) . qq|,| . $dbh->quote($form->{cp_satfax}) . qq|,| . $dbh->quote($form->{cp_project}) . qq|,| . $dbh->quote($form->{cp_privatphone}) . qq|,| . $dbh->quote($form->{cp_privatemail}) . qq|,| . $dbh->quote($form->{cp_birthday}) . qq|)|;
+      }
       $dbh->do($query) || $form->dberror($query);
       # save taxes
       foreach $item (split / /, $form->{taxaccounts}) {
         if ($form->{"tax_$item"}) {
           $query = qq|INSERT INTO customertax (customer_id, chart_id)
     		  VALUES ($form->{id}, (SELECT c.id
     				        FROM chart c
     				        WHERE c.accno = '$item'))|;
           $dbh->do($query) || $form->dberror($query);
+        }
+      }
           qq|INSERT INTO contacts ( cp_cv_id, cp_greeting, cp_title, cp_givenname,  | .
           qq|  cp_name, cp_email, cp_phone1, cp_phone2, cp_abteilung, cp_fax, cp_mobile1, | .
           qq|  cp_mobile2, cp_satphone, cp_satfax, cp_project, cp_privatphone, cp_privatemail, | .
           qq|  cp_birthday) | .
           qq|VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
         @values = (
           $form->{id},
           $form->{cp_greeting},
           $form->{cp_title},
           $form->{cp_givenname},
           $form->{cp_name},
           $form->{cp_email},
           $form->{cp_phone1},
           $form->{cp_phone2},
           $form->{cp_abteilung},
           $form->{cp_fax},
           $form->{cp_mobile1},
           $form->{cp_mobile2},
           $form->{cp_satphone},
           $form->{cp_satfax},
           $form->{cp_project},
           $form->{cp_privatphone},
           $form->{cp_privatemail},
           $form->{cp_birthday}
           );
+      }
       do_query( $form, $dbh, $query, @values ) if ($query);
       # add shipto
       $form->add_shipto($dbh, $form->{id}, "CT");
       $form->add_shipto( $dbh, $form->{id}, "CT" );
       $rc = $dbh->disconnect;
       $rc = $dbh->commit();
       $dbh->disconnect();
       $main::lxdebug->leave_sub();
       return $rc;
-...
     sub save_vendor {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       my ( $self, $myconfig, $form ) = @_;
       $form->{taxzone_id} *= 1;
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
     ##LINET
       map({
           $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
             if ($form->{"selected_cp_${_}"});
       } qw(title greeting abteilung));
       my $dbh = $form->dbconnect_noauto($myconfig);
       map( {
         $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
         if ( $form->{"selected_cp_${_}"} );
            } qw(title greeting abteilung) );
       $form->{"greeting"} = $form->{"selected_company_greeting"}
             if ($form->{"selected_company_greeting"});
       # escape '
       map { $form->{$_} =~ s/\'/\'\'/g }
         qw(vendornumber name street zipcode city country homepage contact notes cp_title cp_greeting language);
     ##/LINET
       $form->{discount} = $form->parse_amount($myconfig, $form->{discount});
       if ( $form->{"selected_company_greeting"} );
       $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
       $form->{discount} /= 100;
       $form->{terms}       *= 1;
       $form->{taxincluded} *= 1;
       $form->{obsolete}    *= 1;
       $form->{business}    *= 1;
       $form->{payment_id}    *= 1;
       $form->{taxzone_id}    *= 1;
       $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
       $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} );
       my $query;
       if ($form->{id}) {
         $query = qq|DELETE FROM vendortax
                     WHERE vendor_id = $form->{id}|;
         $dbh->do($query) || $form->dberror($query);
       if ( $form->{id} ) {
         $query = qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'CT')|;
         do_query($form, $dbh, $query, $form->{id});
         $query = qq|DELETE FROM shipto
                     WHERE trans_id = $form->{id} AND module = 'CT'|;
         $dbh->do($query) || $form->dberror($query);
       } else {
         my $uid = time;
         $uid .= $form->{login};
         my $uid = rand() . time;
         $uid .= $form->{login};
         $uid = substr($uid, 2, 75);
         $query = qq|INSERT INTO vendor (name)
                     VALUES ('$uid')|;
         $dbh->do($query) || $form->dberror($query);
         $query = qq|SELECT v.id FROM vendor v
                     WHERE v.name = '$uid'|;
         $sth = $dbh->prepare($query);
         $sth->execute || $form->dberror($query);
         ($form->{id}) = $sth->fetchrow_array;
         $sth->finish;
         if (!$form->{vendornumber}) {
           $form->{vendornumber} =
             $form->update_defaults($myconfig, "vendornumber");
+        }
         $query = qq|SELECT nextval('id')|;
         ($form->{id}) = selectrow_query($form, $dbh, $query);
+      }
         $query = qq|INSERT INTO vendor (id, name) VALUES (?, '')|;
         do_query($form, $dbh, $query, $form->{id});
     ##LINET
       $query = qq|UPDATE vendor SET
                   vendornumber = '$form->{vendornumber}',
     	      name = '$form->{name}',
                   greeting = '$form->{greeting}',
                   department_1 = '$form->{department_1}',
                   department_2 = '$form->{department_2}',
     	      street = '$form->{street}',
     	      zipcode = '$form->{zipcode}',
     	      city = '$form->{city}',
     	      country = '$form->{country}',
     	      homepage = '$form->{homepage}',
     	      contact = '$form->{contact}',
     	      phone = '$form->{phone}',
     	      fax = '$form->{fax}',
     	      email = '$form->{email}',
     	      cc = '$form->{cc}',
     	      bcc = '$form->{bcc}',
     	      notes = '$form->{notes}',
     	      terms = $form->{terms},
     	      discount = $form->{discount},
     	      creditlimit = $form->{creditlimit},
     	      taxincluded = '$form->{taxincluded}',
     	      gifi_accno = '$form->{gifi_accno}',
     	      business_id = $form->{business},
     	      taxnumber = '$form->{taxnumber}',
     	      sic_code = '$form->{sic}',
                   language = '$form->{language}',
                   account_number = '$form->{account_number}',
                   bank_code = '$form->{bank_code}',
                   bank = '$form->{bank}',
                   obsolete = '$form->{obsolete}',
                   ustid = '$form->{ustid}',
                   payment_id = '$form->{payment_id}',
                   taxzone_id = '$form->{taxzone_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}'
     	      WHERE id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
       if ($form->{cp_id}) {
         $query = qq|UPDATE contacts SET
     		cp_greeting = '$form->{cp_greeting}',
     		cp_title = '$form->{cp_title}',
     		cp_givenname = '$form->{cp_givenname}',
     		cp_name = '$form->{cp_name}',
     		cp_email = '$form->{cp_email}',
     		cp_phone1 = '$form->{cp_phone1}',
     		cp_phone2 = '$form->{cp_phone2}'
     		WHERE cp_id = $form->{cp_id}|;
       } elsif ($form->{cp_name} || $form->{cp_givenname}) {
         $query =
           qq|INSERT INTO contacts ( cp_cv_id, cp_greeting, cp_title, cp_givenname, cp_name, cp_email, cp_phone1, cp_phone2)
     		  VALUES ($form->{id}, '$form->{cp_greeting}','$form->{cp_title}','$form->{cp_givenname}','$form->{cp_name}','$form->{cp_email}','$form->{cp_phone1}','$form->{cp_phone2}')|;
+      }
       $dbh->do($query) || $form->dberror($query);
       # save taxes
       foreach $item (split / /, $form->{taxaccounts}) {
         if ($form->{"tax_$item"}) {
           $query = qq|INSERT INTO vendortax (vendor_id, chart_id)
     		  VALUES ($form->{id}, (SELECT c.id
     				        FROM chart c
     				        WHERE c.accno = '$item'))|;
           $dbh->do($query) || $form->dberror($query);
         if ( !$form->{vendornumber} ) {
           $form->{vendornumber} = $form->update_defaults( $myconfig, "vendornumber", $dbh );
+        }
+      }
       $query =
         qq|UPDATE vendor SET | .
         qq|  vendornumber = ?, | .
         qq|  name = ?, | .
         qq|  greeting = ?, | .
         qq|  department_1 = ?, | .
         qq|  department_2 = ?, | .
         qq|  street = ?, | .
         qq|  zipcode = ?, | .
         qq|  city = ?, | .
         qq|  country = ?, | .
         qq|  homepage = ?, | .
         qq|  contact = ?, | .
         qq|  phone = ?, | .
         qq|  fax = ?, | .
         qq|  email = ?, | .
         qq|  cc = ?, | .
         qq|  bcc = ?, | .
         qq|  notes = ?, | .
         qq|  terms = ?, | .
         qq|  discount = ?, | .
         qq|  creditlimit = ?, | .
         qq|  business_id = ?, | .
         qq|  taxnumber = ?, | .
         qq|  sic_code = ?, | .
         qq|  language = ?, | .
         qq|  account_number = ?, | .
         qq|  bank_code = ?, | .
         qq|  bank = ?, | .
         qq|  obsolete = ?, | .
         qq|  ustid = ?, | .
         qq|  payment_id = ?, | .
         qq|  taxzone_id = ?, | .
         qq|  language_id = ?, | .
         qq|  username = ?, | .
         qq|  user_password = ?, | .
         qq|  v_customer_id = ? | .
         qq|WHERE id = ?|;
       @values = (
         $form->{vendornumber},
         $form->{name},
         $form->{greeting},
         $form->{department_1},
         $form->{department_2},
         $form->{street},
         $form->{zipcode},
         $form->{city},
         $form->{country},
         $form->{homepage},
         $form->{contact},
         $form->{phone},
         $form->{fax},
         $form->{email},
         $form->{cc},
         $form->{bcc},
         $form->{notes},
         conv_i($form->{terms}),
         $form->{discount},
         $form->{creditlimit},
         conv_i($form->{business}),
         $form->{taxnumber},
         $form->{sic},
         $form->{language},
         $form->{account_number},
         $form->{bank_code},
         $form->{bank},
         $form->{obsolete} ? 't' : 'f',
         $form->{ustid},
         conv_i($form->{payment_id}),
         conv_i($form->{taxzone_id}),
         conv_i( $form->{language_id}),
         $form->{username},
         $form->{user_password},
         conv_i($form->{v_customer_id}),
         $form->{id}
         );
       do_query($form, $dbh, $query, @values);
       $query = undef;
       if ( $form->{cp_id} ) {
         $query =
           qq|UPDATE contacts SET | .
           qq|  cp_greeting = ?, | .
           qq|  cp_title = ?, | .
           qq|  cp_givenname = ?, | .
           qq|  cp_name = ?, | .
           qq|  cp_email = ?, | .
           qq|  cp_phone1 = ?, | .
           qq|  cp_phone2 = ? | .
           qq|WHERE cp_id = ?|;
         @values = (
           $form->{cp_greeting},
           $form->{cp_title},
           $form->{cp_givenname},
           $form->{cp_name},
           $form->{cp_email},
           $form->{cp_phone1},
           $form->{cp_phone2},
           $form->{cp_id});
       } elsif ( $form->{cp_name} || $form->{cp_givenname} ) {
         $query =
           qq|INSERT INTO contacts ( cp_cv_id, cp_greeting, cp_title, cp_givenname, cp_name, cp_email, cp_phone1, cp_phone2) | .
           qq|VALUES (?, ?, ?, ?, ?, ?, ?, ?)|;
         @values = (
           conv_i($form->{id}),
           $form->{cp_greeting},
           $form->{cp_title},
           $form->{cp_givenname},
           $form->{cp_name},
           $form->{cp_email},
           $form->{cp_phone1},
           $form->{cp_phone2});
+      }
       do_query($form, $dbh, $query, @values) if ($query);
       # add shipto
       $form->add_shipto($dbh, $form->{id}, "CT");
       $form->add_shipto( $dbh, $form->{id}, "CT" );
       $rc = $dbh->disconnect;
       $rc = $dbh->commit();
       $dbh->disconnect();
       $main::lxdebug->leave_sub();
       return $rc;
-...
     sub delete {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       my ( $self, $myconfig, $form ) = @_;
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       # delete vendor
       my $query = qq|DELETE FROM $form->{db}
     	         WHERE id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
       my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
       my $query = qq|DELETE FROM $cv WHERE id = ?|;
       do_query($form, $dbh, $query, $form->{id});
       $dbh->disconnect;
-...
     sub search {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       my ( $self, $myconfig, $form ) = @_;
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
       my $where = "1 = 1";
       $form->{sort} = "name" unless ($form->{sort});
       if ($form->{sort} eq "address") {
         $form->{sort} = "country,city,street";
+      }
       my @values;
       if ($form->{"$form->{db}number"}) {
         my $companynumber = $form->like(lc $form->{"$form->{db}number"});
         $where .= " AND lower(ct.$form->{db}number) LIKE '$companynumber'";
+      }
       if ($form->{name}) {
         my $name = $form->like(lc $form->{name});
         $where .= " AND lower(ct.name) LIKE '$name'";
+      }
       if ($form->{contact}) {
         my $contact = $form->like(lc $form->{contact});
         $where .= " AND lower(ct.contact) LIKE '$contact'";
       my %allowed_sort_columns =
         map({ $_, 1 } qw(id customernumber name address contact phone fax email
                          taxnumber sic_code business invnumber ordnumber quonumber));
       $sortorder =
         $form->{sort} && $allowed_sort_columns{$form->{sort}} ?
         $form->{sort} : "name";
       $sortorder = "country,city,street" if ($sortorder eq "address");
       if ($form->{"${cv}number"}) {
         $where .= " AND ct.${cv}number ILIKE ?";
         push(@values, '%' . $form->{"${cv}number"} . '%');
+      }
       if ($form->{email}) {
         my $email = $form->like(lc $form->{email});
         $where .= " AND lower(ct.email) LIKE '$email'";
       foreach my $key (qw(name contact email)) {
         if ($form->{$key}) {
           $where .= " AND ct.$key ILIKE ?";
           push(@values, '%' . $form->{$key} . '%');
+        }
+      }
       if ($form->{status} eq 'orphaned') {
         $where .= qq| AND ct.id NOT IN (SELECT o.$form->{db}_id
                                         FROM oe o, $form->{db} cv
     		 	            WHERE cv.id = o.$form->{db}_id)|;
         if ($form->{db} eq 'customer') {
           $where .= qq| AND ct.id NOT IN (SELECT a.customer_id
                                           FROM ar a, customer cv
     				      WHERE cv.id = a.customer_id)|;
       if ( $form->{status} eq 'orphaned' ) {
         $where .=
           qq| AND ct.id NOT IN | .
           qq|   (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
         if ($cv eq 'customer') {
           $where .=
             qq| AND ct.id NOT IN | .
             qq| (SELECT a.customer_id FROM ar a, customer cv | .
             qq|  WHERE cv.id = a.customer_id)|;
+        }
         if ($form->{db} eq 'vendor') {
           $where .= qq| AND ct.id NOT IN (SELECT a.vendor_id
                                           FROM ap a, vendor cv
     				      WHERE cv.id = a.vendor_id)|;
         if ($cv eq 'vendor') {
           $where .=
             qq| AND ct.id NOT IN | .
             qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
             qq|  WHERE cv.id = a.vendor_id)|;
+        }
         $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
+      }
       my $query = qq|SELECT ct.*, b.description AS business
                      FROM $form->{db} ct
     	      LEFT JOIN business b ON (ct.business_id = b.id)
                      WHERE $where|;
       my $query =
         qq|SELECT ct.*, b.description AS business | .
         qq|FROM $cv ct | .
         qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
         qq|WHERE $where|;
       # redo for invoices, orders and quotations
       if ($form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) {
         my ($ar, $union, $module);
         $query = "";
         if ($form->{l_invnumber}) {
           $ar     = ($form->{db} eq 'customer') ? 'ar' : 'ap';
           $module = ($ar         eq 'ar')       ? 'is' : 'ir';
           $query = qq|SELECT ct.*, b.description AS business,
                       a.invnumber, a.ordnumber, a.quonumber, a.id AS invid,
     		  '$module' AS module, 'invoice' AS formtype,
     		  (a.amount = a.paid) AS closed
     		  FROM $form->{db} ct
     		JOIN $ar a ON (a.$form->{db}_id = ct.id)
     	        LEFT JOIN business b ON (ct.business_id = b.id)
     		  WHERE $where
     		  AND a.invoice = '1'|;
           $union = qq|
                   UNION|;
           my $ar = $cv eq 'customer' ? 'ar' : 'ap';
           my $module = $ar eq 'ar' ? 'is' : 'ir';
           $query =
             qq|SELECT ct.*, b.description AS business, | .
             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|WHERE $where AND (a.invoice = '1')|;
           $union = qq|UNION|;
+        }
         if ($form->{l_ordnumber}) {
           $query .= qq|$union
                       SELECT ct.*, b.description AS business,
     		  ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid,
     		  'oe' AS module, 'order' AS formtype,
     		  o.closed
     		  FROM $form->{db} ct
     		JOIN oe o ON (o.$form->{db}_id = ct.id)
     	        LEFT JOIN business b ON (ct.business_id = b.id)
     		  WHERE $where
     		  AND o.quotation = '0'|;
           $union = qq|
                   UNION|;
         if ( $form->{l_ordnumber} ) {
           $query .=
             qq| $union | .
             qq|SELECT ct.*, b.description AS business,| .
             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|WHERE $where AND (o.quotation = '0')|;
           $union = qq|UNION|;
+        }
         if ($form->{l_quonumber}) {
           $query .= qq|$union
                       SELECT ct.*, b.description AS business,
     		  ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid,
     		  'oe' AS module, 'quotation' AS formtype,
     		  o.closed
     		  FROM $form->{db} ct
     		JOIN oe o ON (o.$form->{db}_id = ct.id)
     	        LEFT JOIN business b ON (ct.business_id = b.id)
     		  WHERE $where
     		  AND o.quotation = '1'|;
         if ( $form->{l_quonumber} ) {
           $query .=
             qq| $union | .
             qq|SELECT ct.*, b.description AS business, | .
             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|WHERE $where AND (o.quotation = '1')|;
+        }
+      }
       $query .= qq|
     		 ORDER BY $form->{sort}|;
       $query .= qq| ORDER BY $sortorder|;
       my $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
     ##LINET
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
         $ref->{address} = "";
         map { $ref->{address} .= "$ref->{$_} "; } qw(street zipcode city country);
         push @{ $form->{CT} }, $ref;
+      }
     ##/LINET
       $sth->finish;
       $dbh->disconnect;
       $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
       map({ my $ref = $_; $ref->{address} = join(" ", map({ $ref->{$_} } qw(street zipcode city country))); }
           @{ $form->{CT} });
       $main::lxdebug->leave_sub();
+    }
-...
     sub get_contact {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       my ( $self, $myconfig, $form ) = @_;
       my $dbh   = $form->dbconnect($myconfig);
       my $query = qq|SELECT c.*
                      FROM contacts c
     		 WHERE c.cp_id = $form->{cp_id}  order by c.cp_id limit 1|;
       my $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       my $query =
         qq|SELECT * FROM contacts c | .
         qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
       my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
       my $ref = $sth->fetchrow_hashref(NAME_lc);
       map { $form->{$_} = $ref->{$_} } keys %$ref;
-...
       $main::lxdebug->leave_sub();
+    }
     sub get_shipto {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       my ( $self, $myconfig, $form ) = @_;
       my $dbh   = $form->dbconnect($myconfig);
       my $query = qq|SELECT s.*
                      FROM shipto s
     		 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);
       my $query = qq|SELECT * FROM shipto WHERE shipto_id = ?|;
       my $sth = prepare_execute_query($form, $dbh, $query, $form->{shipto_id});
       my $ref = $sth->fetchrow_hashref(NAME_lc);
-...
     sub get_delivery {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       my $dbh   = $form->dbconnect($myconfig);
       $tabelle = ($form->{db} eq "vendor") ? "ap" : "ar";
       my ( $self, $myconfig, $form ) = @_;
       my $dbh = $form->dbconnect($myconfig);
       $where = " WHERE 1=1 ";
       if ($form->{shipto_id} && $tabelle eq "ar") {
         $where .= "AND $tabelle.shipto_id=$form->{shipto_id} ";
       my $arap = $form->{db} eq "vendor" ? "ap" : "ar";
       my $db = $form->{db} eq "customer" ? "customer" : "vendor";
       my $where = " WHERE 1=1 ";
       my @values;
       if ($form->{shipto_id} && ($arap eq "ar")) {
         $where .= "AND ${arap}.shipto_id = ?";
         push(@values, $form->{shipto_id});
       } else {
     	$where .="AND $tabelle.$form->{db}_id=$form->{id} ";
         $where .= "AND ${arap}.${db}_id = ?";
         push(@values, $form->{id});
+      }
       if ($form->{from}) {
         $where .= "AND $tabelle.transdate >= '$form->{from}' ";
         $where .= "AND ${arap}.transdate >= ?";
         push(@values, conv_date($form->{from}));
+      }
       if ($form->{to}) {
         $where .= "AND $tabelle.transdate <= '$form->{to}' ";
+      }
       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);
         $where .= "AND ${arap}.transdate <= ?";
         push(@values, conv_date($form->{to}));
+      }
       my $query =
         qq|SELECT s.shiptoname, i.qty, | .
         qq|  ${arap}.transdate, ${arap}.invnumber, ${arap}.ordnumber, | .
         qq|  invoice.description, invoice.unit | .
         qq|FROM $arap | .
         qq|LEFT JOIN shipto s ON | .
         ($arap eq "ar"
          ? qq|(ar.shipto_id = s.shipto_id) |
          : qq|(ap.id = s.trans_id) |) .
         qq|LEFT JOIN invoice i ON (${arap}.id = i.trans_id) | .
         qq|LEFT join parts p ON (p.id = i.parts_id) | .
         $where .
         qq|ORDER BY ${arap}.transdate DESC LIMIT 15|;
       $form->{DELIVERY} = selectall_hashref_query($form, $dbh, $query, @values);
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{DELIVERY} }, $ref;
+      }
       $sth->finish;
       $dbh->disconnect;
       $main::lxdebug->leave_sub();
+    }
 ;

Auch abrufbar als: Unified diff

Projekt

Allgemein

Profil

projekt kivitendo

Revision 93a4e424

Von Moritz Bunkus vor mehr als 17 Jahren hinzugefügt