X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCT.pm;h=97f57c72c5510a684ef82521d4af00c77f1d2bfc;hb=7ff0d2ab41abd45fa1d45e6401a3f33c1ebddecd;hp=de10623f2c10f0ca6d4a1ad0d7c58ed987a02a37;hpb=0aa294a49c13a756e43fddb3ae294666a8d8bfca;p=kivitendo-erp.git diff --git a/SL/CT.pm b/SL/CT.pm index de10623f2..97f57c72c 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -551,8 +551,19 @@ sub _save_contact { my @columns = qw(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 cp_gender - cp_street cp_zipcode cp_city); - my @values = map { $_ eq 'cp_gender' ? ($form->{$_} eq 'f' ? 'f' : 'm') : $form->{$_} } @columns; + cp_street cp_zipcode cp_city cp_position); + my @values = map( + { + if ( $_ eq 'cp_gender' ) { + $form->{$_} eq 'f' ? 'f' : 'm'; + } elsif ( $_ eq 'cp_birthday' && $form->{cp_birthday} eq '' ) { + undef; + } else { + $form->{$_}; + } + } + @columns + ); my ($query, $cp_id); if ($form->{cp_id}) { @@ -598,20 +609,45 @@ sub search { my $dbh = $form->dbconnect($myconfig); my $cv = $form->{db} eq "customer" ? "customer" : "vendor"; + my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}; my $where = "1 = 1"; my @values; - my %allowed_sort_columns = - map { $_, 1 } qw( - id customernumber vendornumber name contact phone fax email street - taxnumber business invnumber ordnumber quonumber zipcode city + my %allowed_sort_columns = ( + "id" => "id", + "customernumber" => "customernumber", + "vendornumber" => "vendornumber", + "name" => "ct.name", + "contact" => "contact", + "phone" => "phone", + "fax" => "fax", + "email" => "email", + "street" => "street", + "taxnumber" => "taxnumber", + "business" => "business", + "invnumber" => "invnumber", + "ordnumber" => "ordnumber", + "quonumber" => "quonumber", + "zipcode" => "zipcode", + "city" => "city", + "country" => "country", + "salesman" => "e.name" ); - my $sortorder = $form->{sort} && $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name"; - $form->{sort} = $sortorder; + + $form->{sort} ||= "name"; + my $sortorder; + if ( $join_records ) { + # in UNION case order by hash key, e.g. salesman + # the UNION created an implicit select around the result + $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name"; + } else { + # in not UNION case order by hash value, e.g. e.name + $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name"; + } my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; - if ($sortorder !~ /(business|id)/ && 1 >= scalar grep { $form->{$_} } qw(l_ordnumber l_quonumber l_invnumber )) { + if ($sortorder !~ /(business|id)/ && !$join_records) { $sortorder = "lower($sortorder) ${sortdir}"; } else { $sortorder .= " ${sortdir}"; @@ -647,6 +683,19 @@ sub search { push @values, ('%' . $form->{addr_city} . '%') x 2; } + if ($form->{addr_country}) { + $where .= " AND ((lower(ct.country) LIKE lower(?)) + OR + (ct.id IN ( + SELECT trans_id + FROM shipto + WHERE (module = 'CT') + AND (lower(shiptocountry) LIKE lower(?)) + )) + )"; + push @values, ('%' . $form->{addr_country} . '%') x 2; + } + if ( $form->{status} eq 'orphaned' ) { $where .= qq| AND ct.id NOT IN | . @@ -704,63 +753,60 @@ sub search { } my $query = - qq|SELECT ct.*, b.description AS business | . + qq|SELECT ct.*, b.description AS business, e.name as salesman | . + (qq|, NULL AS invnumber, NULL AS ordnumber, NULL AS quonumber, NULL AS invid, NULL AS module, NULL AS formtype, NULL AS closed | x!! $join_records) . qq|FROM $cv ct | . qq|LEFT JOIN business b ON (ct.business_id = b.id) | . + qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . qq|WHERE $where|; my @saved_values = @values; # redo for invoices, orders and quotations - if ($form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) { - my ($ar, $union, $module); - $query = ""; + if ($join_records) { + my $union = "UNION"; if ($form->{l_invnumber}) { my $ar = $cv eq 'customer' ? 'ar' : 'ap'; my $module = $ar eq 'ar' ? 'is' : 'ir'; - - $query = - qq|SELECT ct.*, b.description AS business, | . + push(@values, @saved_values); + $query .= + qq| UNION | . + qq|SELECT ct.*, b.description AS business, e.name as salesman, | . 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|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . qq|WHERE $where AND (a.invoice = '1')|; - - $union = qq|UNION|; } if ( $form->{l_ordnumber} ) { - if ($union eq "UNION") { - push(@values, @saved_values); - } + push(@values, @saved_values); $query .= - qq| $union | . - qq|SELECT ct.*, b.description AS business,| . + qq| UNION | . + qq|SELECT ct.*, b.description AS business, e.name as salesman, | . 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|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . qq|WHERE $where AND (o.quotation = '0')|; - - $union = qq|UNION|; } if ( $form->{l_quonumber} ) { - if ($union eq "UNION") { - push(@values, @saved_values); - } + push(@values, @saved_values); $query .= - qq| $union | . - qq|SELECT ct.*, b.description AS business, | . + qq| UNION | . + qq|SELECT ct.*, b.description AS business, e.name as salesman, | . 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|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . qq|WHERE $where AND (o.quotation = '1')|; } } @@ -1118,7 +1164,7 @@ sub search_contacts { 'vcnumber' => 'vcnumber, cp_name, cp_givenname', ); - my %sortcols = map { $_ => 1 } qw(cp_name cp_givenname cp_phone1 cp_phone2 cp_mobile1 cp_email cp_street cp_zipcode cp_city vcname vcnumber); + my %sortcols = map { $_ => 1 } qw(cp_name cp_givenname cp_phone1 cp_phone2 cp_mobile1 cp_email cp_street cp_zipcode cp_city cp_position vcname vcnumber); my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name'; $::form->{sort} = $order_by;