projekt kivitendo

use Data::Dumper;

##########################

# get pricegroups from database

#

sub pricegroups {

  $main::lxdebug->enter_sub();

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

  my $var;

  # connect to database

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

  my $sortorder = ($form->{sort}) ? $form->{sort} : "pricegroup";

  my $query = qq|SELECT g.id, g.pricegroup

                 FROM pricegroup g|;

  my $where = "1 = 1";

  if ($form->{pricegroup}) {

    $var = $form->like(lc $form->{pricegroup});

    $where .= " AND lower(g.pricegroup) LIKE '$var'";

  }

  $query .= qq|

               WHERE $where

	       ORDER BY $sortorder|;

  if ($form->{status} eq 'orphaned') {

    $query = qq|SELECT pg.*

                FROM pricegroup pg

                LEFT JOIN prices p ON (p.pricegroup_id = pg.id)

		WHERE $where

                EXCEPT

                SELECT pg.*

	        FROM pricegroup pg

	        JOIN prices p ON (p.pricegroup_id = pg.id)

	        WHERE $where

		ORDER BY $sortorder|;

  }

print STDERR "asdfasdf-$query\n";

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

  $sth->execute || $form->dberror($query);

  my $i = 0;

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

    push @{ $form->{item_list} }, $ref;

    $i++;

  }

  $sth->finish;

  $dbh->disconnect;

  $main::lxdebug->leave_sub();

  return $i;

}

########################

# save pricegruop to database

#

sub save_pricegroup {

  $main::lxdebug->enter_sub();

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

  # connect to database

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

  map { $form->{$_} =~ s/\'/\'\'/g } (pricegroup);

  $form->{discount} /= 100;

  if ($form->{id}) {

    $query = qq|UPDATE pricegroup SET

                pricegroup = '$form->{pricegroup}'

		WHERE id = $form->{id}|;

  } else {

    $query = qq|INSERT INTO pricegroup

                (pricegroup)

                VALUES ('$form->{pricegroup}')|;

  }

  $dbh->do($query) || $form->dberror($query);

  $dbh->disconnect;

  $main::lxdebug->leave_sub();

}

############################

# get one pricegroup from database

#

sub get_pricegroup {

  $main::lxdebug->enter_sub();

print STDERR "PE.pm-get_pricegroup\n";

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

  # connect to database

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

  my $query = qq|SELECT p.id, p.pricegroup

                 FROM pricegroup p

	         WHERE p.id = $form->{id}|;

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

  $sth->execute || $form->dberror($query);

  my $ref = $sth->fetchrow_hashref(NAME_lc);

  map { $form->{$_} = $ref->{$_} } keys %$ref;

  $sth->finish;

  # check if it is orphaned

  $query = qq|SELECT count(*)

              FROM prices p

	      WHERE p.pricegroup_id = $form->{id}|;

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

  $sth->execute || $form->dberror($query);

  ($form->{orphaned}) = $sth->fetchrow_array;

  $form->{orphaned} = !$form->{orphaned};

  $sth->finish;

  $dbh->disconnect;

#print (STDERR "   ", Dumper($form));

  $main::lxdebug->leave_sub();

}