projekt kivitendo

#

# Report for Wares.

# Warning, deep magic ahead.

# This function gets all parts from the database according to the filters specified

#

# filter strings:

#   partnumber ean description partsgroup serialnumber make model drawing microfiche

#   transdatefrom transdateto sort

#

# exclusives:

#   itemstatus  = active | onhand | short | obsolete | orphaned

#   searchitems = part | assembly | service

#

# column flags:

#   l_partnumber l_description l_listprice l_sellprice l_lastcost l_priceupdate l_weight l_unit l_bin l_rop l_image l_drawing l_microfiche l_partsgroup 

#

# binary flags:

#   bought sold onorder ordered rfq quoted onhand short

#   l_serialnumber l_linetotal l_subtotal l_soldtotal l_deliverydate

#   revers top100

#

    $column =~ s/.*\.//; # get rid of table prefixes

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

      push(@values, "%$form->{$column}%");

    push(@values, "%$form->{description}%");

    $where .= qq| AND (serialnumber ILIKE ?)|;

    push(@values, "%$form->{serialnumber}%");

    map { $form->{$_} = '' } qw(make model);

    map { $form->{$_} = 0  } qw(onhand short bought sold onorder ordered rfq quoted);

    map { $form->{$_} = '' } qw(transdatefrom transdateto);

  my %status2condition = (

    active   => " AND (p.obsolete = '0')",

    obsolete => " AND (p.obsolete = '1')",

    onhand   => " AND (p.onhand > 0)",

    short    => " AND (p.onhand < p.rop)",

  );

  $where .= $status2condition{$form->{itemstatus}};

  $form->{onhand} = $form->{short} = 0 if ($form->{itemstatus} eq 'obsolete');

    $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE | . (join " AND ", map { "($_ ILIKE ?)"; } @subcolumns) . ")";

    $group = qq| GROUP BY p.id, p.partnumber, p.description, p.onhand, p.unit, p.bin, p.sellprice, p.listprice, p.lastcost, p.priceupdate, pg.partsgroup|;

      qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, 

           p.bin, p.sellprice, p.listprice, p.lastcost, 

           p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal 

         FROM parts p 

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

         WHERE $where

         $group

         ORDER BY $sortorder $limit|;

         ORDER BY $sortorder $limit|;

use strict;

#use warnings;

# global imports

my $form     = $main::form;

my $locale   = $main::locale;

my %myconfig = %main::myconfig;

my $lxdebug  = $main::lxdebug;

  my ($button1, $button2, $onhand, $makemodel, $serialnumber, $l_serialnumber, $toplevel, $bought);

  my $jsscript = "";

  my ($onhand, $makemodel, $serialnumber, $l_serialnumber, $toplevel, $bought);

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

  foreach my $key (keys %$form) {

  my $j       = $main::j;

  my $lastndx = $main::lastndx;

  my ($totop100);

         <th class=listheading nowrap>| . $locale->text('Part Number') . qq|</th>

         <th class=listheading nowrap>| . $locale->text('Part Description') . qq|</th>

  my ($partnumber, $description, $unit, $sellprice, $soldtotal);

    for (my $i = 0; $i < $form->{ndxs_counter}; $i++) {

    for (my $i = 1; ($i < $form->{ndxs_counter} + 1); $i++) {

  my $lastndx = $main::lastndx;

  my $totop100 = "";

      # ToDO: does this really make sense?

      $main::partnumber  = $form->{"totop100_partnumber_$i"};

      $main::description = $form->{"totop100_description_$i"};

      $main::unit        = $form->{"totop100_unit_$i"};

      $main::sellprice   = $form->{"totop100_sellprice_$i"};

      $main::soldtotal   = $form->{"totop100_soldtotal_$i"};

      my $index = $form->{ndx};

      $form->{"totop100_partnumber_$form->{ndxs_counter}"} = $form->{"new_partnumber_$index"};

      $form->{"totop100_description_$form->{ndxs_counter}"} = $form->{"new_description_$index"};

      $form->{"totop100_unit_$form->{ndxs_counter}"} = $form->{"new_unit_$index"};

      $form->{"totop100_sellprice_$form->{ndxs_counter}"} = $form->{"new_sellprice_$index"};

      $form->{"totop100_soldtotal_$form->{ndxs_counter}"} = $form->{"new_soldtotal_$index"};

  my ($revers, $lastsort, $callback, $option, $description, $sameitem,

      $partnumber, $unit, $sellprice, $soldtotal, $totop100, $onhand, $align);

  my (@column_index, %column_header, %column_data);

  my ($totalsellprice, $totallastcost, $totallistprice, $subtotalonhand, $subtotalsellprice, $subtotallastcost, $subtotallistprice);

  $totop100 = "";

  my @columns = $form->sort_columns(

  foreach my $item (@columns) {

  my $colspan = $#column_index + 1;

  foreach my $ref (@{ $form->{parts} }) {

    for (my $i = 1; ($i < $form->{ndxs_counter} + 1); $i++) {

  foreach my $ref (@{ $form->{parts} }) {

    my $i = 0;

#

# Report for Wares.

# Warning, deep magic ahead.

# This function parses the requested details, sanity checks them, and converts them into a format thats usable for IC->all_parts

#

# flags coming from the form:

# hardcoded:

#  searchitems=part revers=0 lastsort=''

#

# filter:

# partnumber ean description partsgroup serialnumber make model drawing microfiche

# transdatefrom transdateto

#

# radio:

#  itemstatus = active | onhand | short | obsolete | orphaned

#  action     = continue | top100

#

# checkboxes:

#  bought sold onorder ordered rfq quoted

#  l_partnumber l_description l_serialnumber l_unit l_listprice l_sellprice l_lastcost

#  l_linetotal l_priceupdate l_bin l_rop l_weight l_image l_drawing l_microfiche

#  l_partsgroup l_subtotal l_soldtotal l_deliverydate

#

# hiddens: 

#  nextsub login password revers lastsort sort ndxs_counter

#

  my ($revers, $lastsort, $description);

  my (@column_index, %column_header, %column_data, @columns, @options, @callbacks);

  my ($totalsellprice, $totallastcost, $totallistprice, $subtotalonhand, $subtotalsellprice, $subtotallastcost, $subtotallistprice);

  my ($colspan, $sameitem, $onhand, $align);

  # sorting and direction of sorting

  # ToDO: change this to the simpler field+direction method

      $form->{revers} = 1 - $form->{revers};

      $form->{revers} = 0;

  # special case if we have a serialnumber limit search

  # serialnumbers are only given in invoices and orders, 

  # so they can only pop up in bought, sold, rfq, and quoted stuff

  $form->{no_sn_joins} = 'Y' if (   !$form->{bought} && !$form->{sold} 

                                 && !$form->{rfq}    && !$form->{quoted} 

                                 && ($form->{l_serialnumber} || $form->{serialnumber}));

  # special case for any checkbox of bought | sold | onorder | ordered | rfq | quoted.

  # if any of these are ticked the behavior changes slightly for lastcost

  # since all those are aggregation checks for the legder tables this is an internal switch

  # refered to as ledgerchecks

  $form->{ledgerchecks} = 'Y' if (   $form->{bought} || $form->{sold} || $form->{onorder}

                                  || $form->{ordered} || $form->{rfq} || $form->{quoted});

  # if something should be aktivated if something else is active, enter it here

  my %dependencies = (

    onhand       => [ qw(l_onhand) ],

    short        => [ qw(l_onhand) ],

    onorder      => [ qw(l_ordnumber) ],

    ordered      => [ qw(l_ordnumber) ],

    rfq          => [ qw(l_quonumber) ],

    quoted       => [ qw(l_quonumber) ],

    bought       => [ qw(l_invnumber) ],

    sold         => [ qw(l_invnumber) ],

    ledgerchecks => [ qw(l_name) ],

    serialnumber => [ qw(l_serialnumber) ],

    no_sn_joins  => [ qw(bought sold) ],

  );

  # these strings get displayed at the top of the results to indicate the user which switches were used

  my %optiontexts = (

    active        => $locale->text('Active'),

    obsolete      => $locale->text('Obsolete'),

    orphaned      => $locale->text('Orphaned'),

    onhand        => $locale->text('On Hand'),

    short         => $locale->text('Short'),

    onorder       => $locale->text('On Order'),

    ordered       => $locale->text('Ordered'),

    rfq           => $locale->text('RFQ'),

    quoted        => $locale->text('Quoted'),

    bought        => $locale->text('Bought'),

    sold          => $locale->text('Sold'),

    transdatefrom => $locale->text('From')       . " " . $locale->date(\%myconfig, $form->{transdatefrom}, 1),

    transdateto   => $locale->text('To (time)')  . " " . $locale->date(\%myconfig, $form->{transdateto}, 1),

    partnumber    => $locale->text('Part Number')      . ": '$form->{partnumber}'",

    partsgroup    => $locale->text('Group')            . ": '$form->{partsgroup}'",

    serialnumber  => $locale->text('Serial Number')    . ": '$form->{serialnumber}'",

    description   => $locale->text('Part Description') . ": '$form->{description}'",

    make          => $locale->text('Make')             . ": '$form->{make}'",

    model         => $locale->text('Model')            . ": '$form->{model}'",

    drawing       => $locale->text('Drawing')          . ": '$form->{drawing}'",

    microfiche    => $locale->text('Microfiche')       . ": '$form->{microfiche}'",

    l_soldtotal   => $locale->text('soldtotal'),

  );

  # this local subfunction generates a callback token from the input key.

  # easy to join into a callback later

  sub callback_token { 

    map { /\w+$/; return "&$&=$form->{$&}" } @_;

  my @itemstatus_keys = qw(active obsolete orphaned onhand short);

  my @callback_keys   = qw(onorder ordered rfq quoted bought sold partnumber partsgroup serialnumber description make model 

                           drawing microfiche l_soldtotal l_deliverydate transdatefrom transdateto);

  my $callback           = "$form->{script}?action=generate_report";

  map { $callback .= "&$_=" . $form->escape($form->{$_}) } qw(login password searchitems itemstatus bom l_linetotal title);

  # calculate dependencies

  for (@itemstatus_keys, @callback_keys) { 

    next if ($form->{itemstatus} ne $_ && !$form->{$_});

    map { $form->{$_} = 'Y' } @{ $dependencies{$_} } if $dependencies{$_};

  # generate callback and optionstrings

  for my  $key (@itemstatus_keys, @callback_keys) { 

    next if ($form->{itemstatus} ne $key && !$form->{$key});

    push @options, $optiontexts{$key};

    push @callbacks, callback_token($key) if grep { $_ eq $key } @callback_keys;;

  my $option    = $locale->text('Options') . ': ' . join(', ', grep $_, @options) . '<br>';

  $callback .= join '', grep $_, @callbacks;

  $lxdebug->message(0, $callback);

  IC->all_parts(\%myconfig, \%$form);

  # special case for lastcost

  $form->{l_lastcost} = "" if $form->{ledgerchecks};

  if ($form->{description}) {

    $description = $form->{description};

    $description =~ s/\n/<br>/g;

    qw(partnumber description partsgroup bin onhand rop unit listprice linetotallistprice sellprice linetotalsellprice 

       lastcost linetotallastcost priceupdate weight image drawing microfiche invnumber ordnumber quonumber name serialnumber soldtotal deliverydate)

  foreach my $item (@columns) {

  foreach my $ref (@{ $form->{parts} }) {

    my $i = 0;

  # imports

  my %column_data       = $main::column_data;

  my @column_index      = $main::column_index;

  my $subtotalonhand    = $main::subtotalonhand;

  my $totalsellprice    = $main::totalsellprice;

  my $totallastcost     = $main::totallastcost;

  my $totallistprice    = $main::totallistprice;

  my $subtotalsellprice = $main::subtotalsellprice;

  my $subtotallastcost  = $main::subtotallastcost;

  my $subtotallistprice = $main::subtotallistprice;

  my $item = $form->{item};

  foreach my $key (keys %{ $form->{IC_links} }) {

    foreach my $ref (@{ $form->{IC_links}{$key} }) {

    foreach my $i (1 .. $form->{assembly_rows}) {

  my ($payment, $rows, $notes, $description, $ean, $buchungsgruppe, $partsgroup, $group, $tax, $lastcost, $eur, $linkaccounts, $weight, $n, $rop, $bin, $vegv);

  my ($notdiscountableok, $notdiscountable);

  my ($formel, $imagelinks, $obsolete, $shopok, $shop);

  foreach my $item (qw(partnumber description unit notes)) {

  foreach my $item (@{ $form->{payment_terms} }) {

  foreach my $item (split / /, $form->{taxaccounts}) {

    foreach my $item (@{ $form->{BUCHUNGSGRUPPEN} }) {

  foreach my $item (qw(IC IC_income IC_expense)) {

  foreach my $item (split / /, $form->{taxaccounts}) {

  my ($jsscript, $button1);

  for my $i (1 .. $numrows) {

    $form->{"make_$i"}  =~ s/\"/"/g;

    $form->{"model_$i"} =~ s/\"/"/g;

  my (@column_index, %column_data, %column_header);

  my ($nochange, $callback, $previousform, $linetotal, $href);

  my $deliverydate = $main::deliverydate; # ToDO: cjeck if this indeed comes from global context

    foreach my $key (sort keys %$form) {

  for my $i (1 .. $numrows) {

    my $i = $form->{assembly_rows};

      my $rows = scalar @{ $form->{item_list} };

  my ($parts_id, %newform, $previousform, $amount, $callback);

  $lxdebug->message($LXDebug::DEBUG1, "ic.pl: sellprice in save = $form->{sellprice}\n");

  if (IC->save(\%myconfig, \%$form) == 3) {

  my $i;

    foreach my $item (split /&/, $previousform) {

      my ($key, $value) = split /=/, $item, 2;

      $lxdebug->message($LXDebug::DEBUG1, qq|sellprice_$i in previousform 2 = | . $form->{"sellprice_$i"} . qq|\n|);

      foreach my $item (qw(description rate taxnumber)) {

        my $index = $form->{"taxaccounts_$i"} . "_$item";

    foreach my $key (keys %$form) {

      my $value = $form->escape($form->{$key}, 1);

  $lxdebug->message($LXDebug::DEBUG1, qq|ic.pl: sellprice_$i nach sub save = | . $form->{"sellprice_$i"} . qq|\n|);

  my $rc = IC->delete(\%myconfig, \%$form);

  for my $i (1 .. $numrows) {

  my $onload = $main::onload;