X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCT.pm;h=b1c21ab224c342d3e368d90ad02435076bf886a7;hb=1410996dd10264247f118f1ac6f3a8daaf5e55e6;hp=6cd9b91db97afe7d38b8a1f16d3703f12e39f02c;hpb=ee82a6ce45291872bfb41d178352b61a9b9be957;p=kivitendo-erp.git diff --git a/SL/CT.pm b/SL/CT.pm index 6cd9b91db..b1c21ab22 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -155,8 +155,9 @@ sub populate_drop_down_boxes { # get shipto address $query = - qq|SELECT shipto_id, shiptoname, shiptodepartment_1 | . - qq|FROM shipto WHERE (trans_id = ?) AND (module = 'CT')|; + qq|SELECT shipto_id, shiptoname, shiptodepartment_1, shiptostreet, shiptocity + FROM shipto + WHERE (trans_id = ?) AND (module = 'CT')|; $form->{SHIPTO} = selectall_hashref_query($form, $dbh, $query, $form->{id}); # get contacts @@ -301,12 +302,12 @@ sub save_customer { qq|terms = ?, | . qq|business_id = ?, | . qq|taxnumber = ?, | . - qq|sic_code = ?, | . qq|language = ?, | . qq|account_number = ?, | . qq|bank_code = ?, | . qq|bank = ?, | . qq|obsolete = ?, | . + qq|direct_debit = ?, | . qq|ustid = ?, | . qq|username = ?, | . qq|salesman_id = ?, | . @@ -340,12 +341,12 @@ sub save_customer { 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->{direct_debit} ? 't' : 'f', $form->{ustid}, $form->{username}, conv_i($form->{salesman_id}), @@ -470,11 +471,7 @@ sub save_vendor { my $query; - if ( $form->{id} ) { - $query = qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'CT')|; - do_query($form, $dbh, $query, $form->{id}); - - } else { + if (!$form->{id}) { $query = qq|SELECT nextval('id')|; ($form->{id}) = selectrow_query($form, $dbh, $query); @@ -510,12 +507,12 @@ sub save_vendor { qq| creditlimit = ?, | . qq| business_id = ?, | . qq| taxnumber = ?, | . - qq| sic_code = ?, | . qq| language = ?, | . qq| account_number = ?, | . qq| bank_code = ?, | . qq| bank = ?, | . qq| obsolete = ?, | . + qq| direct_debit = ?, | . qq| ustid = ?, | . qq| payment_id = ?, | . qq| taxzone_id = ?, | . @@ -547,12 +544,12 @@ sub save_vendor { $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->{direct_debit} ? 't' : 'f', $form->{ustid}, conv_i($form->{payment_id}), conv_i($form->{taxzone_id}, 0), @@ -684,13 +681,17 @@ sub search { my @values; 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"; + map({ $_, 1 } qw(id customernumber vendornumber name contact phone fax email + taxnumber business invnumber ordnumber quonumber)); + $sortorder = $form->{sort} && $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name"; $form->{sort} = $sortorder; - $sortorder = "country,city,street" if ($sortorder eq "address"); + my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; + +if ($sortorder ne 'id') { + $sortorder = "lower($sortorder) ${sortdir}"; + } else { + $sortorder .= " ${sortdir}"; + } if ($form->{"${cv}number"}) { $where .= " AND ct.${cv}number ILIKE ?"; @@ -704,6 +705,24 @@ sub search { } } + if ($form->{cp_name}) { + $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))"; + push @values, '%' . $form->{cp_name} . '%'; + } + + if ($form->{addr_city}) { + $where .= " AND ((lower(ct.city) LIKE lower(?)) + OR + (ct.id IN ( + SELECT trans_id + FROM shipto + WHERE (module = 'CT') + AND (lower(shiptocity) LIKE lower(?)) + )) + )"; + push @values, ('%' . $form->{addr_city} . '%') x 2; + } + if ( $form->{status} eq 'orphaned' ) { $where .= qq| AND ct.id NOT IN | . @@ -742,7 +761,17 @@ sub search { $where .= qq| AND ($cvar_where)|; push @values, @cvar_values; } - + # Um nach Straße in der Berichtsmaske zu suchen ... jb 13.11.2008 + if ($form->{addr_street}) { + $where .= qq| AND (street ILIKE ?)|; + push @values, ('%' . $form->{addr_street} . '%'); + } + + # Um nach PLZ in der Berichtsmaske zu suchen ... jb 13.11.2008 + if ($form->{addr_zipcode}) { + $where .= qq| AND (zipcode ILIKE ?)|; + push @values, ($form->{addr_zipcode} . '%'); + } my $query = qq|SELECT ct.*, b.description AS business | . qq|FROM $cv ct | . @@ -808,8 +837,6 @@ sub search { $query .= qq| ORDER BY $sortorder|; $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(); } @@ -827,6 +854,14 @@ sub get_contact { map { $form->{$_} = $ref->{$_} } keys %$ref; + $query = qq|SELECT COUNT(cp_id) AS used FROM ( + SELECT cp_id FROM oe UNION + SELECT cp_id FROM ar UNION + SELECT cp_id FROM ap UNION + SELECT cp_id FROM delivery_orders + ) AS cpid WHERE cp_id = ? OR ? = 0|; + ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2); + $sth->finish; $dbh->disconnect; @@ -1003,4 +1038,21 @@ sub delete_shipto { $main::lxdebug->leave_sub(); } +sub delete_shipto { + $main::lxdebug->enter_sub(); + + my $self = shift; + my $shipto_id = shift; + + my $form = $main::form; + my %myconfig = %main::myconfig; + my $dbh = $form->get_standard_dbh(\%myconfig); + + do_query($form, $dbh, qq|UPDATE contacts SET cp_cv_id = NULL WHERE cp_id = ?|, $shipto_id); + + $dbh->commit(); + + $main::lxdebug->leave_sub(); +} + 1;