X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/ca12e8dfc4773aabc13818a0398e06e44028e2bc..06211d74ea81c20cd935a9f35df45a6f76590692:/SL/CT.pm diff --git a/SL/CT.pm b/SL/CT.pm index 933ec15c9..79169b5d2 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -123,7 +123,14 @@ sub get_tuple { } # check if it is orphaned - my $arap = ( $form->{db} eq 'customer' ) ? "ar" : "ap"; + my $arap = ( $form->{db} eq 'customer' ) ? "ar" : "ap"; + my $num_args = 2; + my $makemodel = ''; + if ($form->{db} eq 'vendor') { + $makemodel = qq| UNION SELECT mm.make FROM makemodel mm WHERE mm.make = ?|; + $num_args++; + } + $query = qq|SELECT a.id | . qq|FROM $arap a | . @@ -133,8 +140,10 @@ sub get_tuple { 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}); + qq|WHERE ct.id = ?| + . $makemodel; + my ($dummy) = selectrow_query($form, $dbh, $query, (conv_i($form->{id})) x $num_args); + $form->{status} = "orphaned" unless ($dummy); $dbh->disconnect; @@ -302,11 +311,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|iban = ?, | . + qq|bic = ?, | . qq|obsolete = ?, | . qq|direct_debit = ?, | . qq|ustid = ?, | . @@ -342,11 +352,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->{iban}, + $form->{bic}, $form->{obsolete} ? 't' : 'f', $form->{direct_debit} ? 't' : 'f', $form->{ustid}, @@ -509,11 +520,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| iban = ?, | . + qq| bic = ?, | . qq| obsolete = ?, | . qq| direct_debit = ?, | . qq| ustid = ?, | . @@ -547,11 +559,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->{iban}, + $form->{bic}, $form->{obsolete} ? 't' : 'f', $form->{direct_debit} ? 't' : 'f', $form->{ustid}, @@ -685,15 +698,13 @@ sub search { my @values; my %allowed_sort_columns = - map({ $_, 1 } qw(id customernumber vendornumber name address contact phone fax email - taxnumber sic_code business invnumber ordnumber quonumber)); + 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; my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; - if ($sortorder eq "address") { - $sortorder = "lower(country) ${sortdir}, lower(city) ${sortdir}, lower(street) ${sortdir}"; - } elsif ($sortorder ne 'id') { +if ($sortorder ne 'id') { $sortorder = "lower($sortorder) ${sortdir}"; } else { $sortorder .= " ${sortdir}"; @@ -711,6 +722,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 | . @@ -750,6 +779,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 | . @@ -815,8 +854,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(); } @@ -903,8 +940,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" @@ -912,6 +950,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|; @@ -1035,4 +1074,37 @@ sub delete_shipto { $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;