|
#=====================================================================
|
|
# LX-Office ERP
|
|
# Copyright (C) 2004
|
|
# Based on SQL-Ledger Version 2.1.9
|
|
# Web http://www.lx-office.org
|
|
#
|
|
#=====================================================================
|
|
# SQL-Ledger Accounting
|
|
# Copyright (C) 1998-2002
|
|
#
|
|
# Author: Dieter Simader
|
|
# Email: dsimader@sql-ledger.org
|
|
# Web: http://www.sql-ledger.org
|
|
#
|
|
# Contributors:
|
|
#
|
|
# This program is free software; you can redistribute it and/or modify
|
|
# it under the terms of the GNU General Public License as published by
|
|
# the Free Software Foundation; either version 2 of the License, or
|
|
# (at your option) any later version.
|
|
#
|
|
# This program is distributed in the hope that it will be useful,
|
|
# but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
# GNU General Public License for more details.
|
|
# You should have received a copy of the GNU General Public License
|
|
# along with this program; if not, write to the Free Software
|
|
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
|
|
#======================================================================
|
|
#
|
|
# Project module
|
|
#
|
|
#======================================================================
|
|
|
|
package Projects;
|
|
|
|
use Data::Dumper;
|
|
|
|
use SL::DBUtils;
|
|
use SL::CVar;
|
|
|
|
my %project_id_column_prefixes = ("ar" => "global",
|
|
"ap" => "global",
|
|
"oe" => "global",
|
|
"delivery_orders" => "global");
|
|
|
|
my @tables_with_project_id_cols = qw(acc_trans
|
|
invoice
|
|
orderitems
|
|
rmaitems
|
|
ar
|
|
ap
|
|
oe
|
|
delivery_orders
|
|
delivery_order_items);
|
|
|
|
sub search_projects {
|
|
$main::lxdebug->enter_sub();
|
|
|
|
my $self = shift;
|
|
my %params = @_;
|
|
|
|
my $myconfig = \%main::myconfig;
|
|
my $form = $main::form;
|
|
|
|
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
|
|
|
my (@filters, @values);
|
|
|
|
foreach my $column (qw(projectnumber description)) {
|
|
if ($params{$column}) {
|
|
push @filters, "p.$column ILIKE ?";
|
|
push @values, '%' . $params{$column} . '%';
|
|
}
|
|
}
|
|
|
|
if ($params{status} eq 'orphaned') {
|
|
my @sub_filters;
|
|
|
|
foreach my $table (@tables_with_project_id_cols) {
|
|
push @sub_filters, qq|SELECT DISTINCT $project_id_column_prefixes{$table}project_id FROM $table
|
|
WHERE NOT $project_id_column_prefixes{$table}project_id ISNULL|;
|
|
}
|
|
|
|
push @filters, "p.id NOT IN (" . join(" UNION ", @sub_filters) . ")";
|
|
}
|
|
|
|
if ($params{active} eq "active") {
|
|
push @filters, 'p.active';
|
|
|
|
} elsif ($params{active} eq "inactive") {
|
|
push @filters, 'NOT COALESCE(p.active, FALSE)';
|
|
}
|
|
|
|
my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Projects',
|
|
'trans_id_field' => 'p.id',
|
|
'filter' => $form);
|
|
|
|
if ($cvar_where) {
|
|
push @filters, $cvar_where;
|
|
push @values, @cvar_values;
|
|
}
|
|
|
|
|
|
my $where = 'WHERE ' . join(' AND ', map { "($_)" } @filters) if (scalar @filters);
|
|
|
|
my $sortorder = $params{sort} ? $params{sort} : "projectnumber";
|
|
$sortorder =~ s/[^a-z_]//g;
|
|
my $query = qq|SELECT p.id, p.projectnumber, p.description, p.active
|
|
FROM project p
|
|
$where
|
|
ORDER BY $sortorder|;
|
|
|
|
$form->{project_list} = selectall_hashref_query($form, $dbh, $query, @values);
|
|
|
|
$main::lxdebug->leave_sub();
|
|
|
|
return scalar(@{ $form->{project_list} });
|
|
}
|
|
|
|
sub get_project {
|
|
$main::lxdebug->enter_sub();
|
|
|
|
my $self = shift;
|
|
my %params = @_;
|
|
|
|
if (!$params{id}) {
|
|
$main::lxdebug->leave_sub();
|
|
return { };
|
|
}
|
|
|
|
my $myconfig = \%main::myconfig;
|
|
my $form = $main::form;
|
|
|
|
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
|
|
|
my $project = selectfirst_hashref_query($form, $dbh, qq|SELECT * FROM project WHERE id = ?|, conv_i($params{id})) || { };
|
|
|
|
if ($params{orphaned}) {
|
|
# check if it is orphaned
|
|
my (@values, $query);
|
|
|
|
foreach my $table (@tables_with_project_id_cols) {
|
|
$query .= " + " if ($query);
|
|
$query .= qq|(SELECT COUNT(*) FROM $table
|
|
WHERE $project_id_column_prefixes{$table}project_id = ?) |;
|
|
push @values, conv_i($params{id});
|
|
}
|
|
|
|
$query = 'SELECT ' . $query;
|
|
|
|
($project->{orphaned}) = selectrow_query($form, $dbh, $query, @values);
|
|
$project->{orphaned} = !$project->{orphaned};
|
|
}
|
|
|
|
$main::lxdebug->leave_sub();
|
|
|
|
return $project;
|
|
}
|
|
|
|
sub save_project {
|
|
$main::lxdebug->enter_sub();
|
|
|
|
my $self = shift;
|
|
my %params = @_;
|
|
|
|
my $myconfig = \%main::myconfig;
|
|
my $form = $main::form;
|
|
|
|
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
|
|
|
my @values;
|
|
|
|
if (!$params{id}) {
|
|
($params{id}) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('id')|);
|
|
do_query($form, $dbh, qq|INSERT INTO project (id) VALUES (?)|, conv_i($params{id}));
|
|
|
|
$params{active} = 1;
|
|
}
|
|
|
|
$query = qq|UPDATE project SET projectnumber = ?, description = ?, active = ?
|
|
WHERE id = ?|;
|
|
|
|
@values = ($params{projectnumber}, $params{description}, $params{active} ? 't' : 'f', conv_i($params{id}));
|
|
do_query($form, $dbh, $query, @values);
|
|
|
|
CVar->save_custom_variables('dbh' => $dbh,
|
|
'module' => 'Projects',
|
|
'trans_id' => $params{id},
|
|
'variables' => $form);
|
|
|
|
$dbh->commit();
|
|
|
|
$main::lxdebug->leave_sub();
|
|
|
|
return $params{id};
|
|
}
|
|
|
|
sub delete_project {
|
|
$main::lxdebug->enter_sub();
|
|
|
|
my $self = shift;
|
|
my %params = @_;
|
|
|
|
Common::check_params(\%params, qw(id));
|
|
|
|
my $myconfig = \%main::myconfig;
|
|
my $form = $main::form;
|
|
|
|
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
|
|
|
do_query($form, $dbh, qq|DELETE FROM project WHERE id = ?|, conv_i($params{id}));
|
|
|
|
$dbh->commit();
|
|
|
|
$main::lxdebug->leave_sub();
|
|
}
|
|
|
|
1;
|
|
|