use SL::Common;
use SL::CVar;
use SL::DBUtils;
+use SL::DB::Default;
use SL::FU;
use SL::Notes;
use SL::TransNumber;
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 name AS curr FROM currencies WHERE id=?|;
+ ($form->{curr}) = selectrow_query($form, $dbh, $query, conv_i($form->{currency_id}));
- $sth->finish;
if ( $form->{salesman_id} ) {
my $query =
qq|SELECT ct.name AS salesman | .
$query = qq|SELECT nextval('id')|;
($form->{id}) = selectrow_query($form, $dbh, $query);
- $query = qq|INSERT INTO customer (id, name) VALUES (?, '')|;
+ $query = qq|INSERT INTO customer (id, name, currency_id) VALUES (?, '', (SELECT currency_id FROM defaults))|;
do_query($form, $dbh, $query, $form->{id});
}
qq|user_password = ?, | .
qq|c_vendor_id = ?, | .
qq|klass = ?, | .
- qq|curr = ?, | .
- qq|taxincluded_checked = ? | .
+ qq|currency_id = (SELECT id FROM currencies WHERE name = ?), | .
+ qq|taxincluded_checked = ?, | .
+ qq|delivery_term_id = ? | .
qq|WHERE id = ?|;
my @values = (
$form->{customernumber},
$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,
+ conv_i($form->{delivery_term_id}),
$form->{id}
);
do_query( $form, $dbh, $query, @values );
$query = qq|SELECT nextval('id')|;
($form->{id}) = selectrow_query($form, $dbh, $query);
- $query = qq|INSERT INTO vendor (id, name) VALUES (?, '')|;
+ $query = qq|INSERT INTO vendor (id, name, currency_id) VALUES (?, '', (SELECT currency_id FROM defaults))|;
do_query($form, $dbh, $query, $form->{id});
my $vendornumber = SL::TransNumber->new(type => 'vendor',
qq| username = ?, | .
qq| user_password = ?, | .
qq| v_customer_id = ?, | .
- qq| curr = ? | .
+ qq| currency_id = (SELECT id FROM currencies WHERE name = ?), | .
+ qq| delivery_term_id = ? | .
qq|WHERE id = ?|;
my @values = (
$form->{vendornumber},
$form->{username},
$form->{user_password},
$form->{v_customer_id},
- substr($form->{currency}, 0, 3),
+ $form->{currency},
+ conv_i($form->{delivery_term_id}),
$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);
+ cp_street cp_zipcode cp_city cp_position);
my @values = map(
{
if ( $_ eq 'cp_gender' ) {
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" => "ct.id",
+ "customernumber" => "ct.customernumber",
+ "vendornumber" => "ct.vendornumber",
+ "name" => "ct.name",
+ "contact" => "ct.contact",
+ "phone" => "ct.phone",
+ "fax" => "ct.fax",
+ "email" => "ct.email",
+ "street" => "ct.street",
+ "taxnumber" => "ct.taxnumber",
+ "business" => "ct.business",
+ "invnumber" => "ct.invnumber",
+ "ordnumber" => "ct.ordnumber",
+ "quonumber" => "ct.quonumber",
+ "zipcode" => "ct.zipcode",
+ "city" => "ct.city",
+ "country" => "ct.country",
+ "discount" => "ct.discount",
+ "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) {
+ if ($sortorder !~ /(business|id|discount)/ && !$join_records) {
$sortorder = "lower($sortorder) ${sortdir}";
} else {
$sortorder .= " ${sortdir}";
$where .= " AND ((lower(ct.city) LIKE lower(?))
OR
(ct.id IN (
- SELECT trans_id
- FROM shipto
- WHERE (module = 'CT')
- AND (lower(shiptocity) LIKE lower(?))
+ SELECT sc.trans_id
+ FROM shipto sc
+ WHERE (sc.module = 'CT')
+ AND (lower(sc.shiptocity) LIKE lower(?))
))
)";
push @values, ('%' . $form->{addr_city} . '%') x 2;
}
+ if ($form->{addr_country}) {
+ $where .= " AND ((lower(ct.country) LIKE lower(?))
+ OR
+ (ct.id IN (
+ SELECT so.trans_id
+ FROM shipto so
+ WHERE (so.module = 'CT')
+ AND (lower(so.shiptocountry) LIKE lower(?))
+ ))
+ )";
+ push @values, ('%' . $form->{addr_country} . '%') x 2;
+ }
+
if ( $form->{status} eq 'orphaned' ) {
$where .=
qq| AND ct.id NOT IN | .
}
if ($form->{obsolete} eq "Y") {
- $where .= qq| AND obsolete|;
+ $where .= qq| AND ct.obsolete|;
} elsif ($form->{obsolete} eq "N") {
- $where .= qq| AND NOT obsolete|;
+ $where .= qq| AND NOT ct.obsolete|;
}
if ($form->{business_id}) {
- $where .= qq| AND (business_id = ?)|;
+ $where .= qq| AND (ct.business_id = ?)|;
push(@values, conv_i($form->{business_id}));
}
# Nur Kunden finden, bei denen ich selber der Verkäufer bin
# Gilt nicht für Lieferanten
if ($cv eq 'customer' && !$main::auth->assert('customer_vendor_all_edit', 1)) {
- $where .= qq| AND ct.salesman_id = (select id from employee where login= ?)|;
+ $where .= qq| AND ct.salesman_id = (select em.id from employee em where em.login = ?)|;
push(@values, $form->{login});
}
}
if ($form->{addr_street}) {
- $where .= qq| AND (street ILIKE ?)|;
+ $where .= qq| AND (ct.street ILIKE ?)|;
push @values, '%' . $form->{addr_street} . '%';
}
if ($form->{addr_zipcode}) {
- $where .= qq| AND (zipcode ILIKE ?)|;
+ $where .= qq| AND (ct.zipcode ILIKE ?)|;
push @values, $form->{addr_zipcode} . '%';
}
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')|;
}
}
my ($self, $myconfig, $form) = @_;
my $locale = $main::locale;
+ my $defaults = SL::DB::Default->get;
+ $form->error($::locale->text('No print templates have been created for this client yet. Please do so in the client configuration.')) if !$defaults->templates;
+ $form->{templates} = $defaults->templates;
+
$form->{formname} = 'sales_quotation';
$form->{type} = 'sales_quotation';
$form->{format} = 'excel';
$form->{notes} =~ s/^\s+//g;
- $form->{templates} = $myconfig->{templates};
-
delete $form->{printer_command};
$form->get_employee_info($myconfig);
my %params = @_;
my $dbh = $params{dbh} || $::form->get_standard_dbh;
- my $vc = $params{db} eq 'customer' ? 'customer' : 'vendor';
my %sortspecs = (
'cp_name' => 'cp_name, cp_givenname',
'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 / kivitendo-erp.git / blobdiff