+
+ $query =
+ qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
+ qq| o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
+ qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
+ qq| o.transaction_description, | .
+ qq| o.marge_total, o.marge_percent, | .
+ qq| o.itime::DATE AS insertdate, | .
+ qq| ex.$rate AS exchangerate, | .
+ qq| pt.description AS payment_terms, | .
+ qq| pr.projectnumber AS globalprojectnumber, | .
+ qq| e.name AS employee, s.name AS salesman, | .
+ qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
+ qq| tz.description AS taxzone | .
+ $periodic_invoices_columns .
+ qq| , o.order_probability, o.expected_billing_date, (o.netamount * o.order_probability / 100) AS expected_netamount | .
+ qq|FROM oe o | .
+ qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
+ qq|LEFT JOIN contacts cp ON (o.cp_id = cp.cp_id) | .
+ qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
+ qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
+ qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
+ qq| AND ex.transdate = o.transdate) | .
+ qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
+ qq|LEFT JOIN payment_terms pt ON (pt.id = o.payment_id)| .
+ qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
+ qq|$periodic_invoices_joins | .
+ qq|WHERE (o.quotation = ?) |;
+ push(@values, $quotation);
+
+ my ($null, $split_department_id) = split /--/, $form->{department};
+ my $department_id = $form->{department_id} || $split_department_id;
+ if ($department_id) {
+ $query .= qq| AND o.department_id = ?|;
+ push(@values, $department_id);
+ }
+
+ if ($form->{"project_id"}) {
+ $query .=
+ qq|AND ((globalproject_id = ?) OR EXISTS | .
+ qq| (SELECT * FROM orderitems oi | .
+ qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
+ push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
+ }
+
+ if ($form->{"projectnumber"}) {
+ $query .= <<SQL;
+ AND ((pr.projectnumber ILIKE ?) OR EXISTS (
+ SELECT * FROM orderitems oi
+ LEFT JOIN project proi ON proi.id = oi.project_id
+ WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
+ ))
+SQL
+ push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
+ }
+
+ if ($form->{"business_id"}) {
+ $query .= " AND ct.business_id = ?";
+ push(@values, $form->{"business_id"});
+ }
+
+ if ($form->{"${vc}_id"}) {
+ $query .= " AND o.${vc}_id = ?";
+ push(@values, $form->{"${vc}_id"});
+
+ } elsif ($form->{$vc}) {
+ $query .= " AND ct.name ILIKE ?";
+ push(@values, '%' . $form->{$vc} . '%');
+ }
+
+ if ($form->{"cp_name"}) {
+ $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
+ push(@values, ('%' . $form->{"cp_name"} . '%')x2);
+ }
+
+ if (!$main::auth->assert('sales_all_edit', 1)) {
+ $query .= " AND o.employee_id = (select id from employee where login= ?)";
+ push @values, $::myconfig{login};
+ }
+ if ($form->{employee_id}) {
+ $query .= " AND o.employee_id = ?";
+ push @values, conv_i($form->{employee_id});
+ }
+
+ if ($form->{salesman_id}) {
+ $query .= " AND o.salesman_id = ?";
+ push @values, conv_i($form->{salesman_id});
+ }
+