projekt kivitendo

#   top100

#

# simple filter strings (every one of those also has a column flag prefixed with 'l_' associated):

#

# column flags:

#

# exclusives:

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

# joining filters:

#   make model                               - makemodel

#   serialnumber transdatefrom transdateto   - invoice/orderitems

# binary flags:

#   l_linetotal l_subtotal                   - aggreg joins to display totals (complicated) - NOT IMPLEMENTED here, implementation at frontend

#   l_soldtotal                              - aggreg join to display total of sold quantity

#   onhand                                   - as above, but masking the simple itemstatus results (doh!)

#   l_serialnumber                           - belonges to serialnumber filter

#   l_deliverydate                           - displays deliverydate is sold etc. flags are active

#

#   l_soldtotal                              - aggreg join to display total of sold quantity

#   onhand                                   - as above, but masking the simple itemstatus results (doh!)

#   masking of onhand in bsooqr mode         - ToDO: fixme

  my %joins = (

    partsgroup => 'LEFT JOIN partsgroup pg ON p.partsgroup_id = pg.id',

    makemodel  => 'LEFT JOIN makemodel mm ON mm.parts_id = p.id',

       ) AS ioi ON ioi.parts_id = p.id|,

         SELECT id, transdate, 'ir' AS module, ordnumber, quonumber,         invnumber, FALSE AS quotation, NULL AS customer_id,         vendor_id, NULL AS deliverydate FROM ap UNION

         SELECT id, transdate, 'is' AS module, ordnumber, quonumber,         invnumber, FALSE AS quotation,         customer_id, NULL AS vendor_id,         deliverydate FROM ar UNION

         SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber,          quotation,         customer_id,         vendor_id, NULL AS deliverydate FROM oe

       ) AS apoe ON ioi.trans_id = apoe.id|,

    cv         =>

         ) AS cv ON cv.id = apoe.customer_id OR cv.id = apoe.vendor_id|,

  );

  my @join_order = qw(partsgroup makemodel invoice_oi apoe cv);

  foreach (@simple_filters, @makemodel_filters, @invoice_oi_filters) {

    next unless $form->{$_};

    $form->{"l_$_"} = '1'; # show the column

    push @bind_vars,    "%$form->{$_}%";

  }

  }

  for ($form->{itemstatus}) {

         SELECT DISTINCT parts_id FROM orderitems)'    if /orphaned/;

    push @where_tokens, 'p.onhand = 0'                 if /orphaned/;

    push @where_tokens, 'NOT p.obsolete'               if /active/;

    push @where_tokens, '    p.obsolete',              if /obsolete/;

    push @where_tokens, 'p.onhand > 0',                if /onhand/;

    push @where_tokens, 'p.onhand < p.rop',            if /short/;

  my @sort_cols = (@simple_filters, qw(id bin priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate));

  my $limit_clause = " LIMIT 100" if $form->{top100};

  #=== joins and complicated filters ========#

  my $bsooqr = $form->{bought}  || $form->{sold}

            || $form->{ordered} || $form->{onorder}

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

  my @bsooqr;

  push @select_tokens, @qsooqr_flags                                          if $bsooqr;

  push @select_tokens, @deliverydate_flags                                    if $bsooqr && $form->{l_deliverydate};

  #============= build query ================#

  my %table_prefix = (

     invnumber    => 'apoe.', partsgroup   => 'pg.',

     'SUM(ioi.qty) AS soldtotal' => ' ',

  );

  map { $table_prefix{$_} = 'ioi.' } qw(description serialnumber qty unit) if $joins_needed{invoice_oi};

  my $select_clause = join ', ',    map { ($table_prefix{$_} || "p.") . $_ } @select_tokens;

##  if ($form->{l_soldtotal}) {

##    $form->{soldtotal} = 'soldtotal';

##    $query =

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

##         WHERE $where

##         $group