projekt kivitendo

sub retrieve_projects {

  $main::lxdebug->enter_sub();

  my ($self, $myconfig, $form, $order_by, $order_dir) = @_;

  my $dbh = $form->dbconnect($myconfig);

  my (@filter_values, $filter);

  if ($form->{"projectnumber"}) {

    $filter .= qq| AND (projectnumber ILIKE ?)|;

    push(@filter_values, '%' . $form->{"projectnumber"} . '%');

  }

  if ($form->{"description"}) {

    $filter .= qq| AND (description ILIKE ?)|;

    push(@filter_values, '%' . $form->{"description"} . '%');

  }

  substr($filter, 1, 3) = "WHERE" if ($filter);

  $order_by =~ s/[^a-zA-Z_]//g;

  $order_dir = $order_dir ? "ASC" : "DESC";

  my $query =

    qq|SELECT id, projectnumber, description | .

    qq|FROM project $filter | .

    qq|ORDER BY $order_by $order_dir|;

  my $sth = $dbh->prepare($query);

  $sth->execute(@filter_values) || $form->dberror($query . " (" . join(", ", @filter_values) . ")");

  my $projects = [];

  while (my $ref = $sth->fetchrow_hashref()) {

    push(@{$projects}, $ref);

  }

  $sth->finish();

  $dbh->disconnect();

  $main::lxdebug->leave_sub();

  return $projects;

}

sub retrieve_employees {

  $main::lxdebug->enter_sub();

  my ($self, $myconfig, $form, $order_by, $order_dir) = @_;

  my $dbh = $form->dbconnect($myconfig);

  my (@filter_values, $filter);

  if ($form->{"name"}) {

    $filter .= qq| AND (name ILIKE ?)|;

    push(@filter_values, '%' . $form->{"name"} . '%');

  }

  substr($filter, 1, 3) = "WHERE" if ($filter);

  $order_by =~ s/[^a-zA-Z_]//g;

  $order_dir = $order_dir ? "ASC" : "DESC";

  my $query =

    qq|SELECT id, name | .

    qq|FROM employee $filter | .

    qq|ORDER BY $order_by $order_dir|;

  my $sth = $dbh->prepare($query);

  $sth->execute(@filter_values) || $form->dberror($query . " (" . join(", ", @filter_values) . ")");

  my $employees = [];

  while (my $ref = $sth->fetchrow_hashref()) {

    push(@{$employees}, $ref);

  }

  $sth->finish();

  $dbh->disconnect();

  $main::lxdebug->leave_sub();

  return $employees;

}