X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCT.pm;h=f2745fb02644e0788ec20ba6d9ec72cae7bc0474;hb=41b8891d96b1ea8b22b06e9a14eb20eb96cc0300;hp=d22017d4fbbe9a799b4d5b4c750815666ec7b014;hpb=245b032241e70470b1d392b5fe055a2dfd6333c6;p=kivitendo-erp.git diff --git a/SL/CT.pm b/SL/CT.pm index d22017d4f..f2745fb02 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,14 @@ sub save_customer { qq|terms = ?, | . qq|business_id = ?, | . qq|taxnumber = ?, | . - qq|sic_code = ?, | . qq|language = ?, | . qq|account_number = ?, | . qq|bank_code = ?, | . qq|bank = ?, | . + qq|iban = ?, | . + qq|bic = ?, | . qq|obsolete = ?, | . + qq|direct_debit = ?, | . qq|ustid = ?, | . qq|username = ?, | . qq|salesman_id = ?, | . @@ -340,12 +343,14 @@ 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->{iban}, + $form->{bic}, $form->{obsolete} ? 't' : 'f', + $form->{direct_debit} ? 't' : 'f', $form->{ustid}, $form->{username}, conv_i($form->{salesman_id}), @@ -470,11 +475,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 +511,14 @@ sub save_vendor { qq| creditlimit = ?, | . qq| business_id = ?, | . qq| taxnumber = ?, | . - qq| sic_code = ?, | . qq| language = ?, | . qq| account_number = ?, | . qq| bank_code = ?, | . qq| bank = ?, | . + qq| iban = ?, | . + qq| bic = ?, | . qq| obsolete = ?, | . + qq| direct_debit = ?, | . qq| ustid = ?, | . qq| payment_id = ?, | . qq| taxzone_id = ?, | . @@ -547,12 +550,14 @@ sub save_vendor { $form->{creditlimit}, conv_i($form->{business}), $form->{taxnumber}, - $form->{sic}, $form->{language}, $form->{account_number}, $form->{bank_code}, $form->{bank}, + $form->{iban}, + $form->{bic}, $form->{obsolete} ? 't' : 'f', + $form->{direct_debit} ? 't' : 'f', $form->{ustid}, conv_i($form->{payment_id}), conv_i($form->{taxzone_id}, 0), @@ -684,13 +689,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 +713,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 | . @@ -743,6 +770,16 @@ sub search { push @values, @cvar_values; } + if ($form->{addr_street}) { + $where .= qq| AND (street ILIKE ?)|; + push @values, '%' . $form->{addr_street} . '%'; + } + + 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 +845,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 +862,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; @@ -888,8 +931,9 @@ sub get_delivery { } my $query = qq|SELECT s.shiptoname, i.qty, | . - qq| ${arap}.transdate, ${arap}.invnumber, ${arap}.ordnumber, | . - qq| i.description, i.unit, i.sellprice | . + qq| ${arap}.id, ${arap}.transdate, ${arap}.invnumber, ${arap}.ordnumber, | . + qq| i.description, i.unit, i.sellprice, | . + qq| oe.id AS oe_id | . qq|FROM $arap | . qq|LEFT JOIN shipto s ON | . ($arap eq "ar" @@ -897,6 +941,7 @@ sub get_delivery { : 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) | . + qq|LEFT JOIN oe ON (oe.ordnumber = ${arap}.ordnumber AND NOT ${arap}.ordnumber = '') | . $where . qq|ORDER BY ${arap}.transdate DESC LIMIT 15|; @@ -986,4 +1031,71 @@ sub _delete_selected_notes { $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 shipto SET trans_id = NULL WHERE shipto_id = ?|, $shipto_id); + + $dbh->commit(); + + $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(); +} + +sub get_bank_info { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(vc id)); + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor'; + my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id}); + my $placeholders = ('?') x scalar @ids; + my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic + FROM ${table} + WHERE id IN (${placeholders})|; + + my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids); + + if (ref $params{id} eq 'ARRAY') { + $result = { map { $_->{id} => $_ } @{ $result } }; + } else { + $result = $result->[0] || { 'id' => $params{id} }; + } + + $main::lxdebug->leave_sub(); + + return $result; +} + 1;