1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
33 #======================================================================
44 my %project_id_column_prefixes = ("ar" => "global",
47 "delivery_orders" => "global");
49 my @tables_with_project_id_cols = qw(acc_trans
57 delivery_order_items);
60 $main::lxdebug->enter_sub();
65 my $myconfig = \%main::myconfig;
66 my $form = $main::form;
68 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
70 my (@filters, @values);
72 foreach my $column (qw(projectnumber description)) {
73 if ($params{$column}) {
74 push @filters, "p.$column ILIKE ?";
75 push @values, '%' . $params{$column} . '%';
79 if ($params{status} eq 'orphaned') {
82 foreach my $table (@tables_with_project_id_cols) {
83 push @sub_filters, qq|SELECT DISTINCT $project_id_column_prefixes{$table}project_id FROM $table
84 WHERE NOT $project_id_column_prefixes{$table}project_id ISNULL|;
87 push @filters, "p.id NOT IN (" . join(" UNION ", @sub_filters) . ")";
90 if ($params{active} eq "active") {
91 push @filters, 'p.active';
93 } elsif ($params{active} eq "inactive") {
94 push @filters, 'NOT COALESCE(p.active, FALSE)';
97 if ($params{valid} eq "valid") {
98 push @filters, 'p.valid';
100 } elsif ($params{valid} eq "invalid") {
101 push @filters, 'NOT COALESCE(p.valid, FALSE)';
104 if ($params{customer}) {
105 push @filters, 'c.name ILIKE ?';
106 push @values, '%' . $params{customer} . '%';
110 push @filters, 'p.type ILIKE ?';
111 push @values, '%' . $params{type} . '%';
114 my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Projects',
115 'trans_id_field' => 'p.id',
119 push @filters, $cvar_where;
120 push @values, @cvar_values;
124 my $where = @filters ? 'WHERE ' . join(' AND ', map { "($_)" } @filters) : '';
126 my $sortorder = $params{sort} ? $params{sort} : "projectnumber";
127 $sortorder =~ s/[^a-z_]//g;
128 my $query = qq|SELECT p.id, p.projectnumber, p.description, p.active, p.valid, p.type,
131 LEFT JOIN customer c ON (p.customer_id = c.id)
133 ORDER BY $sortorder|;
135 $form->{project_list} = selectall_hashref_query($form, $dbh, $query, @values);
137 $main::lxdebug->leave_sub();
139 return scalar(@{ $form->{project_list} });
143 $main::lxdebug->enter_sub();
149 $main::lxdebug->leave_sub();
153 my $myconfig = \%main::myconfig;
154 my $form = $main::form;
156 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
158 my $project = selectfirst_hashref_query($form, $dbh, qq|SELECT * FROM project WHERE id = ?|, conv_i($params{id})) || { };
160 if ($params{orphaned}) {
161 # check if it is orphaned
162 my (@values, $query);
164 foreach my $table (@tables_with_project_id_cols) {
165 $query .= " + " if ($query);
166 $query .= qq|(SELECT COUNT(*) FROM $table
167 WHERE $project_id_column_prefixes{$table}project_id = ?) |;
168 push @values, conv_i($params{id});
171 $query = 'SELECT ' . $query;
173 ($project->{orphaned}) = selectrow_query($form, $dbh, $query, @values);
174 $project->{orphaned} = !$project->{orphaned};
177 $main::lxdebug->leave_sub();
183 $main::lxdebug->enter_sub();
188 my $myconfig = \%main::myconfig;
189 my $form = $main::form;
191 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
196 ($params{id}) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('id')|);
197 do_query($form, $dbh, qq|INSERT INTO project (id) VALUES (?)|, conv_i($params{id}));
204 SET projectnumber = ?, description = ?, active = ?, customer_id = ?, type = ?, valid = ?
208 @values = ($params{projectnumber}, $params{description}, $params{active} ? 't' : 'f', conv_i($params{customer_id}), $params{type}, $params{valid} ? 't' : 'f', conv_i($params{id}));
209 do_query($form, $dbh, $query, @values);
211 CVar->save_custom_variables('dbh' => $dbh,
212 'module' => 'Projects',
213 'trans_id' => $params{id},
214 'variables' => $form,
215 'always_valid' => 1);
219 $main::lxdebug->leave_sub();
225 $main::lxdebug->enter_sub();
230 Common::check_params(\%params, qw(id));
232 my $myconfig = \%main::myconfig;
233 my $form = $main::form;
235 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
237 do_query($form, $dbh, qq|DELETE FROM project WHERE id = ?|, conv_i($params{id}));
241 $main::lxdebug->leave_sub();