X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCT.pm;h=606c46a4962b1953380a9aa59648f4598aaa7ea1;hb=4e953c795c21e894ffa9e72840d663bc05e01179;hp=96b6b9cb3866282cabdaf0ca737b5feb9dd85344;hpb=6c1536aad4882e22fe0be62f295749d436e0f49d;p=kivitendo-erp.git diff --git a/SL/CT.pm b/SL/CT.pm index 96b6b9cb3..606c46a49 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -44,6 +44,7 @@ use SL::CVar; use SL::DBUtils; use SL::FU; use SL::Notes; +use List::MoreUtils qw(true); sub get_tuple { $main::lxdebug->enter_sub(); @@ -123,7 +124,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 1 FROM makemodel mm WHERE mm.make = ?|; + $num_args++; + } + $query = qq|SELECT a.id | . qq|FROM $arap a | . @@ -133,8 +141,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; @@ -155,8 +165,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 +312,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 +353,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 +485,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 +521,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 +560,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 +699,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' && 1 >= true { $form->{$_} } qw(l_ordnumber l_quonumber l_invnumber)) { + $sortorder = "lower($sortorder) ${sortdir}"; + } else { + $sortorder .= " ${sortdir}"; + } if ($form->{"${cv}number"}) { $where .= " AND ct.${cv}number ILIKE ?"; @@ -704,6 +723,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 +780,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 +855,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(); } @@ -896,8 +941,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" @@ -905,6 +951,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|; @@ -1028,4 +1075,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;