my $ref = $sth->fetchrow_hashref("NAME_lc");
map { $form->{$_} = $ref->{$_} } keys %$ref;
+ $sth->finish;
- # remove any trailing whitespace
- $form->{curr} =~ s/\s*$//;
+ #get name of currency instead of id:
+ $query = qq|SELECT curr FROM currencies WHERE id=?|;
+ ($form->{curr}) = selectrow_query($form, $dbh, $query, conv_i($form->{curr}));
- $sth->finish;
if ( $form->{salesman_id} ) {
my $query =
qq|SELECT ct.name AS salesman | .
qq|user_password = ?, | .
qq|c_vendor_id = ?, | .
qq|klass = ?, | .
- qq|curr = ?, | .
+ qq|curr = (SELECT id FROM currencies WHERE curr = ?), | .
qq|taxincluded_checked = ? | .
qq|WHERE id = ?|;
my @values = (
$form->{user_password},
$form->{c_vendor_id},
conv_i($form->{klass}),
- substr($form->{currency}, 0, 3),
+ $form->{currency},
$form->{taxincluded_checked} ne '' ? $form->{taxincluded_checked} : undef,
$form->{id}
);
qq| username = ?, | .
qq| user_password = ?, | .
qq| v_customer_id = ?, | .
- qq| curr = ? | .
+ qq| curr = (SELECT id FROM currencies WHERE curr = ?) | .
qq|WHERE id = ?|;
my @values = (
$form->{vendornumber},
$form->{username},
$form->{user_password},
$form->{v_customer_id},
- substr($form->{currency}, 0, 3),
+ $form->{currency},
$form->{id}
);
do_query($form, $dbh, $query, @values);
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}) {
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)/ && !$join_records) {
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 | .
}
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;
push(@values, @saved_values);
$query .=
qq| UNION | .
- qq|SELECT ct.*, b.description AS business, | .
+ 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')|;
}
push(@values, @saved_values);
$query .=
qq| UNION | .
- qq|SELECT ct.*, b.description AS business,| .
+ 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')|;
}
push(@values, @saved_values);
$query .=
qq| UNION | .
- qq|SELECT ct.*, b.description AS business, | .
+ 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')|;
}
}
'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;
projects / mfinanz.git / blobdiff