projekt kivitendo

sub get_pricegroups {

  $main::lxdebug->enter_sub();

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

  my $dbh = $form->get_standard_dbh;

  # get pricegroups

  my $query = qq|SELECT id, pricegroup FROM pricegroup ORDER BY lower(pricegroup)|;

  my $pricegroups = selectall_hashref_query($form, $dbh, $query);

  my $i = 1;

  foreach my $pg (@{ $pricegroups }) {

    $form->{"price_$i"}         = $form->format_amount($myconfig, $form->{"price_$i"}, -2);

    $form->{"pricegroup_id_$i"} = "$pg->{id}";

    $form->{"pricegroup_$i"}    = "$pg->{pricegroup}";

    $i++;

  }

  #correct rows

  $form->{price_rows} = $i - 1;

  $main::lxdebug->leave_sub();

  return $pricegroups;

}

sub _create_filter_for_priceupdate {

  $main::lxdebug->enter_sub();

  my $self     = shift;

  my $myconfig = \%main::myconfig;

  my $form     = $main::form;

  my @where_values;

  my $where = '1 = 1';

  foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) {

    my $column = $item;

    $column =~ s/.*\.//;

    next unless ($form->{$column});

    $where .= qq| AND $item ILIKE ?|;

    push(@where_values, like($form->{$column}));

  }

  foreach my $item (qw(description serialnumber)) {

    next unless ($form->{$item});

    $where .= qq| AND (${item} ILIKE ?)|;

    push(@where_values, like($form->{$item}));

  }

  # items which were never bought, sold or on an order

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

    $where .=

      qq| AND (p.onhand = 0)

          AND p.id NOT IN

            (

              SELECT DISTINCT parts_id FROM invoice

              UNION

              SELECT DISTINCT parts_id FROM assembly

              UNION

              SELECT DISTINCT parts_id FROM orderitems

            )|;

  } elsif ($form->{itemstatus} eq 'active') {

    $where .= qq| AND p.obsolete = '0'|;

  } elsif ($form->{itemstatus} eq 'obsolete') {

    $where .= qq| AND p.obsolete = '1'|;

  } elsif ($form->{itemstatus} eq 'onhand') {

    $where .= qq| AND p.onhand > 0|;

  } elsif ($form->{itemstatus} eq 'short') {

    $where .= qq| AND p.onhand < p.rop|;

  }

  foreach my $column (qw(make model)) {

    next unless ($form->{$column});

    $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|;

    push(@where_values, like($form->{$column}));

  }

  $main::lxdebug->leave_sub();

  return ($where, @where_values);

}

sub get_num_matches_for_priceupdate {

  $main::lxdebug->enter_sub();

  my $self     = shift;

  my $myconfig = \%main::myconfig;

  my $form     = $main::form;

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

  my ($where, @where_values) = $self->_create_filter_for_priceupdate();

  my $num_updated = 0;

  my $query;

  for my $column (qw(sellprice listprice)) {

    next if ($form->{$column} eq "");

    $query =

      qq|SELECT COUNT(*)

         FROM parts

         WHERE id IN

           (SELECT p.id

            FROM parts p

            LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)

            WHERE $where)|;

    my ($result)  = selectfirst_array_query($form, $dbh, $query, @where_values);

    $num_updated += $result if (0 <= $result);

  }

  $query =

    qq|SELECT COUNT(*)

       FROM prices

       WHERE parts_id IN

         (SELECT p.id

          FROM parts p

          LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)

          WHERE $where) AND (pricegroup_id = ?)|;

  my $sth = prepare_query($form, $dbh, $query);

  for my $i (1 .. $form->{price_rows}) {

    next if ($form->{"price_$i"} eq "");

    my ($result)  = do_statement($form, $sth, $query, @where_values, conv_i($form->{"pricegroup_id_$i"}));

    $num_updated += $result if (0 <= $result);

  }

  $sth->finish();

  $main::lxdebug->leave_sub();

  return $num_updated;

}

sub update_prices {

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

  $main::lxdebug->enter_sub();

  my $num_updated = SL::DB->client->with_transaction(\&_update_prices, $self, $myconfig, $form);

  $main::lxdebug->leave_sub();

  return $num_updated;

}

sub _update_prices {

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

  my ($where, @where_values) = $self->_create_filter_for_priceupdate();

  my $num_updated = 0;

  # connect to database

  my $dbh = SL::DB->client->dbh;

  for my $column (qw(sellprice listprice)) {

    next if ($form->{$column} eq "");

    my $value = $form->parse_amount($myconfig, $form->{$column});

    my $operator = '+';

    if ($form->{"${column}_type"} eq "percent") {

      $value = ($value / 100) + 1;

      $operator = '*';

    }

    my $query =

      qq|UPDATE parts SET $column = $column $operator ?

         WHERE id IN

           (SELECT p.id

            FROM parts p

            LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)

            WHERE $where)|;

    my $result    = do_query($form, $dbh, $query, $value, @where_values);

    $num_updated += $result if (0 <= $result);

  }

  my $q_add =

    qq|UPDATE prices SET price = price + ?

       WHERE parts_id IN

         (SELECT p.id

          FROM parts p

          LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)

          WHERE $where) AND (pricegroup_id = ?)|;

  my $sth_add = prepare_query($form, $dbh, $q_add);

  my $q_multiply =

    qq|UPDATE prices SET price = price * ?

       WHERE parts_id IN

         (SELECT p.id

          FROM parts p

          LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)

          WHERE $where) AND (pricegroup_id = ?)|;

  my $sth_multiply = prepare_query($form, $dbh, $q_multiply);

  for my $i (1 .. $form->{price_rows}) {

    next if ($form->{"price_$i"} eq "");

    my $value = $form->parse_amount($myconfig, $form->{"price_$i"});

    my $result;

    if ($form->{"pricegroup_type_$i"} eq "percent") {

      $result = do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"}));

    } else {

      $result = do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"}));

    }

    $num_updated += $result if (0 <= $result);

  }

  $sth_add->finish();

  $sth_multiply->finish();

  return $num_updated;

}