- if (!$main::auth->assert('sales_all_edit', 1)) {
- # only show own invoices
- $where .= " AND a.employee_id = (select id from employee where login= ?)";
- push (@values, $::myconfig{login});
- } else {
- if ($form->{employee_id}) {
- $where .= " AND a.employee_id = ?";
- push @values, conv_i($form->{employee_id});
- }
- if ($form->{salesman_id}) {
- $where .= " AND a.salesman_id = ?";
- push @values, conv_i($form->{salesman_id});
- }
- };
+ if ($form->{parts_partnumber}) {
+ $where .= <<SQL;
+ AND EXISTS (
+ SELECT invoice.trans_id
+ FROM invoice
+ LEFT JOIN parts ON (invoice.parts_id = parts.id)
+ WHERE (invoice.trans_id = a.id)
+ AND (parts.partnumber ILIKE ?)
+ LIMIT 1
+ )
+SQL
+ push @values, like($form->{parts_partnumber});
+ }
+
+ if ($form->{parts_description}) {
+ $where .= <<SQL;
+ AND EXISTS (
+ SELECT invoice.trans_id
+ FROM invoice
+ WHERE (invoice.trans_id = a.id)
+ AND (invoice.description ILIKE ?)
+ LIMIT 1
+ )
+SQL
+ push @values, like($form->{parts_description});
+ }
+
+ if ($form->{show_not_mailed}) {
+ $where .= <<SQL;
+ AND NOT EXISTS (
+ SELECT rl.to_id
+ FROM record_links rl
+ WHERE (rl.from_id = a.id)
+ AND (rl.to_table = 'email_journal')
+ LIMIT 1
+ )
+SQL
+ }
+
+ if ($form->{show_marked_as_closed}) {
+ $query .= '
+ LEFT JOIN (
+ SELECT SUM(acc_trans.amount) AS amount, trans_id
+ FROM acc_trans
+ LEFT JOIN chart ON chart.id = chart_id
+ WHERE chart.link ILIKE ?
+ GROUP BY trans_id
+ ) AS paid_difference ON (paid_difference.trans_id = a.id)
+ ';
+ unshift @values, '%AR_paid%';
+ $where .= ' AND COALESCE(paid_difference.amount, 0) + a.paid != 0';
+ }
+
+ my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
+ 'trans_id_field' => 'c.id',
+ 'filter' => $form,
+ );
+ if ($cvar_where) {
+ $where .= qq| AND ($cvar_where)|;
+ push @values, @cvar_values;
+ }
projects / mfinanz.git / blobdiff