X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/ff159a4d47b9a2d10744dcfc23da2c63605c8a32..eeb5375ee7727c956cc357cc8f90b19d1bfe80b9:/SL/IC.pm

diff --git a/SL/IC.pm b/SL/IC.pm
index 2a9460745..d657e160b 100644
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -131,7 +131,7 @@ sub assembly_item {
 #   l_warehouse  l_bin
 #
 # exclusives:
-#   itemstatus  = active | onhand | short | obsolete | orphaned
+#   itemstatus  = active | onhand | short | order_locked | obsolete | orphaned
 #   searchitems = part | assembly | service
 #
 # joining filters:
@@ -178,7 +178,7 @@ sub all_parts {
   $form->{parts}     = +{ };
   $form->{soldtotal} = undef if $form->{l_soldtotal}; # security fix. top100 insists on putting strings in there...
 
-  my @simple_filters       = qw(partnumber ean description partsgroup microfiche drawing onhand);
+  my @simple_filters       = qw(partnumber ean description partsgroup microfiche drawing onhand notes);
   my @project_filters      = qw(projectnumber projectdescription);
   my @makemodel_filters    = qw(make model);
   my @invoice_oi_filters   = qw(serialnumber soldtotal);
@@ -186,7 +186,7 @@ sub all_parts {
   my @like_filters         = (@simple_filters, @invoice_oi_filters);
   my @all_columns          = (@simple_filters, @makemodel_filters, @apoe_filters, @project_filters, qw(serialnumber));
   my @simple_l_switches    = (@all_columns, qw(notes listprice sellprice lastcost priceupdate weight unit rop image shop insertdate));
-  my %no_simple_l_switches = (warehouse => 'wh.description as warehouse', bin => 'bin.description as bin');
+  my %no_simple_l_switches = (warehouse => 'wh.description as warehouse', bin => 'bin.description as bin',  price_factor_description => 'pfac.description as price_factor_description');
   my @oe_flags             = qw(bought sold onorder ordered rfq quoted);
   my @qsooqr_flags         = qw(invnumber ordnumber quonumber trans_id name module qty);
   my @deliverydate_flags   = qw(deliverydate);
@@ -201,7 +201,13 @@ sub all_parts {
 
   my %joins = (
     partsgroup => 'LEFT JOIN partsgroup pg      ON (pg.id       = p.partsgroup_id)',
-    makemodel  => 'LEFT JOIN makemodel mm       ON (mm.parts_id = p.id)',
+    makemodel  => "LEFT JOIN LATERAL (
+                      SELECT string_agg(mv.vendornumber || ' ' || mv.name, ', ') AS make,
+                             string_agg(mm.model, ', ')                          AS model
+                        FROM makemodel mm
+                             LEFT JOIN vendor mv ON (mv.id = mm.make)
+                      WHERE  mm.parts_id = p.id
+                   ) mm ON TRUE",
     pfac       => 'LEFT JOIN price_factors pfac ON (pfac.id     = p.price_factor_id)',
     invoice_oi =>
       q|LEFT JOIN (
@@ -210,21 +216,20 @@ sub all_parts {
        ) AS ioi ON ioi.parts_id = p.id|,
     apoe       =>
       q|LEFT JOIN (
-         SELECT id, transdate, 'ir' AS module, ordnumber, quonumber,         invnumber, FALSE AS quotation, NULL AS customer_id,         vendor_id,    NULL AS deliverydate, globalproject_id, 'invoice'    AS ioi FROM ap UNION
-         SELECT id, transdate, 'is' AS module, ordnumber, quonumber,         invnumber, FALSE AS quotation,         customer_id, NULL AS vendor_id,            deliverydate, globalproject_id, 'invoice'    AS ioi FROM ar UNION
-         SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber,          quotation,         customer_id,         vendor_id, reqdate AS deliverydate, globalproject_id, 'orderitems' AS ioi FROM oe
+         SELECT id, transdate, 'ir' AS module, ordnumber, quonumber,         invnumber, 'purchase_invoice' AS record_type, NULL AS customer_id,         vendor_id,    NULL AS deliverydate, globalproject_id, 'invoice'    AS ioi FROM ap UNION
+         SELECT id, transdate, 'is' AS module, ordnumber, quonumber,         invnumber, 'sales_invoice'    AS record_type,         customer_id, NULL AS vendor_id,            deliverydate, globalproject_id, 'invoice'    AS ioi FROM ar UNION
+         SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber,                       record_type::text,   customer_id,         vendor_id, reqdate AS deliverydate, globalproject_id, 'orderitems' AS ioi FROM oe
        ) AS apoe ON ((ioi.trans_id = apoe.id) AND (ioi.ioi = apoe.ioi))|,
     cv         =>
       q|LEFT JOIN (
            SELECT id, name, 'customer' AS cv FROM customer UNION
            SELECT id, name, 'vendor'   AS cv FROM vendor
          ) AS cv ON cv.id = apoe.customer_id OR cv.id = apoe.vendor_id|,
-    mv         => 'LEFT JOIN vendor AS mv ON mv.id = mm.make',
     project    => 'LEFT JOIN project AS pj ON pj.id = COALESCE(ioi.project_id, apoe.globalproject_id)',
     warehouse  => 'LEFT JOIN warehouse AS wh ON wh.id = p.warehouse_id',
     bin        => 'LEFT JOIN bin ON bin.id = p.bin_id',
   );
-  my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac project warehouse bin);
+  my @join_order = qw(partsgroup makemodel invoice_oi apoe cv pfac project warehouse bin);
 
   my %table_prefix = (
      deliverydate => 'apoe.', serialnumber => 'ioi.',
@@ -239,7 +244,7 @@ sub all_parts {
      description  => 'p.',
      qty          => 'ioi.',
      serialnumber => 'ioi.',
-     quotation    => 'apoe.',
+     record_type  => 'apoe.',
      cv           => 'cv.',
      "ioi.id"     => ' ',
      "ioi.ioi"    => ' ',
@@ -391,6 +396,7 @@ sub all_parts {
     push @where_tokens, '    p.obsolete',              if /obsolete/;
     push @where_tokens, 'p.onhand > 0',                if /onhand/;
     push @where_tokens, 'p.onhand < p.rop',            if /short/;
+    push @where_tokens, 'p.order_locked',              if /order_locked/;
   }
 
   my $q_assembly_lastcost =
@@ -405,8 +411,10 @@ sub all_parts {
   # all_parts is based upon the assumption that every parameter is named like the column it represents
   # unfortunately make would have to match vendor.name which is already taken for vendor.name in bsooqr mode.
   # fortunately makemodel doesn't need to be displayed later, so adding a special clause to where_token is sufficient.
+  # the lateral join mm already creates a string consisting of vendornumbers and vendornames in mm.make
+  # and the models in mm.make
   if ($form->{make}) {
-    push @where_tokens, 'mv.name ILIKE ?';
+    push @where_tokens, 'mm.make ILIKE ?';
     push @bind_vars, like($form->{make});
   }
   if ($form->{model}) {
@@ -434,22 +442,21 @@ sub all_parts {
   my $bsooqr        = any { $form->{$_} } @oe_flags;
   my @bsooqr_tokens = ();
 
-  push @select_tokens, @qsooqr_flags, 'quotation', 'cv', 'ioi.id', 'ioi.ioi'  if $bsooqr;
+  push @select_tokens, @qsooqr_flags, 'record_type', 'cv', 'ioi.id', 'ioi.ioi'  if $bsooqr;
   push @select_tokens, @deliverydate_flags                                    if $bsooqr && $form->{l_deliverydate};
   push @select_tokens, $q_assembly_lastcost                                   if $form->{l_assembly} && $form->{l_lastcost};
   push @bsooqr_tokens, q|module = 'ir' AND NOT ioi.assemblyitem|              if $form->{bought};
   push @bsooqr_tokens, q|module = 'is' AND NOT ioi.assemblyitem|              if $form->{sold};
-  push @bsooqr_tokens, q|module = 'oe' AND NOT quotation AND cv = 'customer'| if $form->{ordered};
-  push @bsooqr_tokens, q|module = 'oe' AND NOT quotation AND cv = 'vendor'|   if $form->{onorder};
-  push @bsooqr_tokens, q|module = 'oe' AND     quotation AND cv = 'customer'| if $form->{quoted};
-  push @bsooqr_tokens, q|module = 'oe' AND     quotation AND cv = 'vendor'|   if $form->{rfq};
+  push @bsooqr_tokens, q|module = 'oe' AND record_type = 'sales_order'|       if $form->{ordered};
+  push @bsooqr_tokens, q|module = 'oe' AND record_type = 'purchase_order'|    if $form->{onorder};
+  push @bsooqr_tokens, q|module = 'oe' AND record_type = 'sales_quotation'|   if $form->{quoted};
+  push @bsooqr_tokens, q|module = 'oe' AND record_type = 'request_quotation'| if $form->{rfq};
   push @where_tokens, join ' OR ', map { "($_)" } @bsooqr_tokens              if $bsooqr;
 
   $joins_needed{partsgroup}  = 1;
   $joins_needed{pfac}        = 1;
   $joins_needed{project}     = 1 if grep { $form->{$_} || $form->{"l_$_"} } @project_filters;
   $joins_needed{makemodel}   = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters;
-  $joins_needed{mv}          = 1 if $joins_needed{makemodel};
   $joins_needed{cv}          = 1 if $bsooqr;
   $joins_needed{apoe}        = 1 if $joins_needed{project} || $joins_needed{cv}   || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
   $joins_needed{invoice_oi}  = 1 if $joins_needed{project} || $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
@@ -805,14 +812,18 @@ SQL
     }
 
     $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} for qw(inventory income expense);
+    $form->{"${_}_accno_id_$index"} = $accounts{"${_}_accno_id"} for qw(inventory expense); # only for purchase_invoice
 
     $sth_tax->execute($accounts{$inc_exp}, quote_db_date($transdate)) || $::form->dberror($query_tax);
-    $ref = $sth_tax->fetchrow_hashref or next;
+    my $tax_ref;
+    $tax_ref = $sth_tax->fetchrow_hashref or next;
+
+    $form->{"expense_accno_tax_id_$index"} = $tax_ref->{tax_id}; # only for purchase_invoice
 
-    $form->{"taxaccounts_$index"} = $ref->{"accno"};
-    $form->{"taxaccounts"} .= "$ref->{accno} "if $form->{"taxaccounts"} !~ /$ref->{accno}/;
+    $form->{"taxaccounts_$index"} = $tax_ref->{"accno"};
+    $form->{"taxaccounts"} .= "$tax_ref->{accno} "if $form->{"taxaccounts"} !~ /$tax_ref->{accno}/;
 
-    $form->{"$ref->{accno}_${_}"} = $ref->{$_} for qw(rate description taxnumber tax_id);
+    $form->{"$tax_ref->{accno}_${_}"} = $tax_ref->{$_} for qw(rate description taxnumber tax_id);
   }
 
   $sth_tax->finish;
@@ -880,7 +891,10 @@ sub prepare_parts_for_printing {
   }
 
   my $placeholders = join ', ', ('?') x scalar(@part_ids);
-  my $query        = qq|SELECT mm.parts_id, mm.model, mm.lastcost, v.name AS make
+  my $query        = qq|SELECT mm.parts_id, mm.model,
+                        mm.part_description AS mm_part_description,
+                        mm.part_longdescription AS mm_part_longdescription,
+                        mm.lastcost, v.name AS make
                         FROM makemodel mm
                         LEFT JOIN vendor v ON (mm.make = v.id)
                         WHERE mm.parts_id IN ($placeholders)|;
@@ -896,10 +910,33 @@ sub prepare_parts_for_printing {
 
   $sth->finish();
 
+
+  $query            = qq|SELECT bm.parts_id,
+                         bm.model                AS business_model,
+                         bm.part_description     AS bm_part_description,
+                         bm.part_longdescription AS bm_part_longdescription,
+                         b.description           AS business_make
+                         FROM business_models bm
+                         LEFT JOIN business b ON (bm.business_id = b.id)
+                         WHERE bm.parts_id IN ($placeholders)|;
+
+  my %businessmodel = ();
+
+  $sth              = prepare_execute_query($form, $dbh, $query, @part_ids);
+
+  while (my $ref = $sth->fetchrow_hashref()) {
+    $businessmodel{$ref->{parts_id}} ||= [];
+    push @{ $businessmodel{$ref->{parts_id}} }, $ref;
+  }
+
+  $sth->finish();
+
   $query           = qq|SELECT
                         cp.parts_id,
-                        cp.customer_partnumber AS customer_model,
-                        c.name                 AS customer_make
+                        cp.customer_partnumber  AS customer_model,
+                        cp.part_description     AS cm_part_description,
+                        cp.part_longdescription AS cm_part_longdescription,
+                        c.name                  AS customer_make
                         FROM part_customer_prices cp
                         LEFT JOIN customer c ON (cp.customer_id = c.id)
                         WHERE cp.parts_id IN ($placeholders)|;
@@ -915,7 +952,7 @@ sub prepare_parts_for_printing {
 
   $sth->finish();
 
-  my @columns = qw(ean image microfiche drawing);
+  my @columns = qw(ean image microfiche drawing tariff_code);
 
   $query      = qq|SELECT id, | . join(', ', @columns) . qq|
                    FROM parts
@@ -924,7 +961,7 @@ sub prepare_parts_for_printing {
   my %data    = selectall_as_map($form, $dbh, $query, 'id', \@columns, @part_ids);
 
   my %template_arrays;
-  map { $template_arrays{$_} = [] } (qw(make model customer_make customer_model), @columns);
+  map { $template_arrays{$_} = [] } (qw(make model mm_part_description mm_part_longdescription business_make business_model bm_part_description bm_part_longdescription customer_make customer_model cm_part_description cm_part_longdescription), @columns);
 
   foreach my $i (1 .. $rowcount) {
     my $id = $form->{"${prefix}${i}"};
@@ -937,19 +974,34 @@ sub prepare_parts_for_printing {
 
     push @{ $template_arrays{make} },  [];
     push @{ $template_arrays{model} }, [];
+    push @{ $template_arrays{mm_part_description} },     [];
+    push @{ $template_arrays{mm_part_longdescription} }, [];
 
     if ($makemodel{$id}) {
       foreach my $ref (@{ $makemodel{$id} }) {
-        map { push @{ $template_arrays{$_}->[-1] }, $ref->{$_} } qw(make model);
+        map { push @{ $template_arrays{$_}->[-1] }, $ref->{$_} } qw(make model mm_part_description mm_part_longdescription);
+      }
+    }
+
+    push @{ $template_arrays{business_make} },           [];
+    push @{ $template_arrays{business_model} },          [];
+    push @{ $template_arrays{bm_part_description} },     [];
+    push @{ $template_arrays{bm_part_longdescription} }, [];
+
+    if ($businessmodel{$id}) {
+      foreach my $ref (@{ $businessmodel{$id} }) {
+        map { push @{ $template_arrays{$_}->[-1] }, $ref->{$_} } qw(business_make business_model bm_part_description bm_part_longdescription);
       }
     }
 
     push @{ $template_arrays{customer_make} },  [];
     push @{ $template_arrays{customer_model} }, [];
+    push @{ $template_arrays{cm_part_description} },     [];
+    push @{ $template_arrays{cm_part_longdescription} }, [];
 
     if ($customermodel{$id}) {
       foreach my $ref (@{ $customermodel{$id} }) {
-        push @{ $template_arrays{$_}->[-1] }, $ref->{$_} for qw(customer_make customer_model);
+        push @{ $template_arrays{$_}->[-1] }, $ref->{$_} for qw(customer_make customer_model cm_part_description cm_part_longdescription);
       }
     }