Nachschlag zu commit: 066fbb38fb409c5801b70fc7d43f4e9190d295ff Entsprechend schon...

[mfinanz.git] / SL / IC.pm

diff --git a/SL/IC.pm b/SL/IC.pm
index f1840d8cbd7b1260ba76695b99618e5e6f2c5eb1..82b43cd4c1c6d703f456b8d3dcce4f22873677cb 100644 (file)
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -1,4 +1,4 @@
-  #=====================================================================
+#=====================================================================

 # LX-Office ERP
 # Copyright (C) 2004
 # Based on SQL-Ledger Version 2.1.9
 # LX-Office ERP
 # Copyright (C) 2004
 # Based on SQL-Ledger Version 2.1.9


@@ -40,6 +40,7 @@ use YAML;
 
 use SL::CVar;
 use SL::DBUtils;
 
 use SL::CVar;
 use SL::DBUtils;

+use SL::TransNumber;

 
 use strict;
 
 
 use strict;
 


@@ -176,7 +177,7 @@ sub get_part {
 
       my $i = 1;
 
 
       my $i = 1;
 

-      while (($form->{"make_$i"}, $form->{"model_$i"}, $form->{"old_lastcost_$i"}, 
+      while (($form->{"make_$i"}, $form->{"model_$i"}, $form->{"old_lastcost_$i"},

                 $form->{"lastcost_$i"}, $form->{"lastupdate_$i"}, $form->{"sortorder_$i"}) = $sth->fetchrow_array)
       {
         $i++;
                 $form->{"lastcost_$i"}, $form->{"lastupdate_$i"}, $form->{"sortorder_$i"}) = $sth->fetchrow_array)
       {
         $i++;


@@ -287,7 +288,7 @@ sub save {
   my ($self, $myconfig, $form) = @_;
   my @values;
   # connect to database, turn off AutoCommit
   my ($self, $myconfig, $form) = @_;
   my @values;
   # connect to database, turn off AutoCommit

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

 
   # save the part
   # make up a unique handle and store in partnumber field
 
   # save the part
   # make up a unique handle and store in partnumber field


@@ -311,6 +312,11 @@ sub save {
   my $priceupdate = ', priceupdate = current_date';
 
   if ($form->{id}) {
   my $priceupdate = ', priceupdate = current_date';
 
   if ($form->{id}) {

+    my $trans_number = SL::TransNumber->new(type => $form->{item}, dbh => $dbh, number => $form->{partnumber}, id => $form->{id});
+    if (!$trans_number->is_unique) {
+      $::lxdebug->leave_sub;
+      return 3;
+    }

 
     # get old price
     $query = qq|SELECT sellprice, weight FROM parts WHERE id = ?|;
 
     # get old price
     $query = qq|SELECT sellprice, weight FROM parts WHERE id = ?|;


@@ -346,23 +352,19 @@ sub save {
     $priceupdate        = '' if (all { $previous_values->{$_} == $form->{$_} } qw(sellprice lastcost listprice));
 
   } else {
     $priceupdate        = '' if (all { $previous_values->{$_} == $form->{$_} } qw(sellprice lastcost listprice));
 
   } else {

-    my ($count) = selectrow_query($form, $dbh, qq|SELECT COUNT(*) FROM parts WHERE partnumber = ?|, $form->{partnumber});
-    if ($count) {
-      $main::lxdebug->leave_sub();
+    my $trans_number = SL::TransNumber->new(type => $form->{item}, dbh => $dbh, number => $form->{partnumber}, save => 1);
+
+    if ($form->{partnumber} && !$trans_number->is_unique) {
+      $::lxdebug->leave_sub;

       return 3;
     }
 
       return 3;
     }
 

+    $form->{partnumber} ||= $trans_number->create_unique;
+

     ($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);
     ($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);

-    do_query($form, $dbh, qq|INSERT INTO parts (id, partnumber, unit) VALUES (?, '', '')|, $form->{id});
+    do_query($form, $dbh, qq|INSERT INTO parts (id, partnumber, unit) VALUES (?, ?, ?)|, $form->{id}, $form->{partnumber}, $form->{unit});

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

-    if ($form->{partnumber} eq "" && $form->{"item"} eq "service") {
-      $form->{partnumber} = $form->update_defaults($myconfig, "servicenumber");
-    }
-    if ($form->{partnumber} eq "" && $form->{"item"} ne "service") {
-      $form->{partnumber} = $form->update_defaults($myconfig, "articlenumber");
-    }
-

   }
   my $partsgroup_id = 0;
 
   }
   my $partsgroup_id = 0;
 


@@ -502,9 +504,13 @@ sub save {
       if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
         #hli
         $value = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
       if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
         #hli
         $value = $form->parse_amount($myconfig, $form->{"lastcost_$i"});

-        if ($value == $form->{"old_lastcost_$i"}) 
+        if ($value == $form->parse_amount($myconfig, $form->{"old_lastcost_$i"}))

         {
         {

-            $lastupdate = $dbh->quote($form->{"lastupdate_$i"});
+            if ($form->{"lastupdate_$i"} eq "") {
+                $lastupdate = 'now()';
+            } else {
+                $lastupdate = $dbh->quote($form->{"lastupdate_$i"});
+            }

         } else {
             $lastupdate = 'now()';
         }
         } else {
             $lastupdate = 'now()';
         }


@@ -579,14 +585,14 @@ sub save {
     }
   }
 
     }
   }
 

-  CVar->save_custom_variables('dbh'       => $dbh,
-                              'module'    => 'IC',
-                              'trans_id'  => $form->{id},
-                              'variables' => $form);
+  CVar->save_custom_variables(dbh           => $dbh,
+                              module        => 'IC',
+                              trans_id      => $form->{id},
+                              variables     => $form,
+                              save_validity => 1);

 
   # commit
   my $rc = $dbh->commit;
 
   # commit
   my $rc = $dbh->commit;

-  $dbh->disconnect;

 
   $main::lxdebug->leave_sub();
 
 
   $main::lxdebug->leave_sub();
 


@@ -664,7 +670,7 @@ sub delete {
 
   my %columns = ( "assembly" => "id", "parts" => "id" );
 
 
   my %columns = ( "assembly" => "id", "parts" => "id" );
 

-  for my $table (qw(prices partstax makemodel inventory assembly license translation parts)) {
+  for my $table (qw(prices partstax makemodel inventory assembly translation parts)) {

     my $column = defined($columns{$table}) ? $columns{$table} : "parts_id";
     do_query($form, $dbh, qq|DELETE FROM $table WHERE $column = ?|, @values);
   }
     my $column = defined($columns{$table}) ? $columns{$table} : "parts_id";
     do_query($form, $dbh, qq|DELETE FROM $table WHERE $column = ?|, @values);
   }


@@ -701,6 +707,12 @@ sub assembly_item {
     push(@values, conv_i($form->{id}));
   }
 
     push(@values, conv_i($form->{id}));
   }
 

+  # Search for part ID overrides all other criteria.
+  if ($form->{"id_${i}"}) {
+    $where  = qq|p.id = ?|;
+    @values = ($form->{"id_${i}"});
+  }
+

   if ($form->{partnumber}) {
     $where .= qq| ORDER BY p.partnumber|;
   } else {
   if ($form->{partnumber}) {
     $where .= qq| ORDER BY p.partnumber|;
   } else {


@@ -785,7 +797,7 @@ sub all_parts {
   my @makemodel_filters    = qw(make model);
   my @invoice_oi_filters   = qw(serialnumber soldtotal);
   my @apoe_filters         = qw(transdate);
   my @makemodel_filters    = qw(make model);
   my @invoice_oi_filters   = qw(serialnumber soldtotal);
   my @apoe_filters         = qw(transdate);

-  my @like_filters         = (@simple_filters, @makemodel_filters, @invoice_oi_filters);
+  my @like_filters         = (@simple_filters, @invoice_oi_filters);

   my @all_columns          = (@simple_filters, @makemodel_filters, @apoe_filters, qw(serialnumber));
   my @simple_l_switches    = (@all_columns, qw(listprice sellprice lastcost priceupdate weight unit bin rop image));
   my @oe_flags             = qw(bought sold onorder ordered rfq quoted);
   my @all_columns          = (@simple_filters, @makemodel_filters, @apoe_filters, qw(serialnumber));
   my @simple_l_switches    = (@all_columns, qw(listprice sellprice lastcost priceupdate weight unit bin rop image));
   my @oe_flags             = qw(bought sold onorder ordered rfq quoted);


@@ -820,8 +832,9 @@ sub all_parts {
            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|,
            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',

   );
   );

-  my @join_order = qw(partsgroup makemodel invoice_oi apoe cv pfac);
+  my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac);

 
   my %table_prefix = (
      deliverydate => 'apoe.', serialnumber => 'ioi.',
 
   my %table_prefix = (
      deliverydate => 'apoe.', serialnumber => 'ioi.',


@@ -899,6 +912,12 @@ sub all_parts {
     }
   }
 
     }
   }
 

+  if ($form->{"partsgroup_id"}) {
+    $form->{"l_partsgroup"} = '1'; # show the column
+    push @where_tokens, "pg.id = ?";
+    push @bind_vars, $form->{"partsgroup_id"};
+  }
+

   foreach (@like_filters) {
     next unless $form->{$_};
     $form->{"l_$_"} = '1'; # show the column
   foreach (@like_filters) {
     next unless $form->{$_};
     $form->{"l_$_"} = '1'; # show the column


@@ -938,6 +957,19 @@ sub all_parts {
         WHERE (a_lc.id = p.id)) AS lastcost|;
   $table_prefix{$q_assembly_lastcost} = ' ';
 
         WHERE (a_lc.id = p.id)) AS lastcost|;
   $table_prefix{$q_assembly_lastcost} = ' ';
 

+  # special case makemodel search
+  # 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.
+  if ($form->{make}) {
+    push @where_tokens, 'mv.name ILIKE ?';
+    push @bind_vars, "%$form->{make}%";
+  }
+  if ($form->{model}) {
+    push @where_tokens, 'mm.model ILIKE ?';
+    push @bind_vars, "%$form->{model}%";
+  }
+

   # special case: sorting by partnumber
   # since partnumbers are expected to be prefixed integers, a special sorting is implemented sorting first lexically by prefix and then by suffix.
   # and yes, that expression is designed to hold that array of regexes only once, so the map is kinda messy, sorry about that.
   # special case: sorting by partnumber
   # since partnumbers are expected to be prefixed integers, a special sorting is implemented sorting first lexically by prefix and then by suffix.
   # and yes, that expression is designed to hold that array of regexes only once, so the map is kinda messy, sorry about that.


@@ -972,6 +1004,7 @@ sub all_parts {
   $joins_needed{partsgroup}  = 1;
   $joins_needed{pfac}        = 1;
   $joins_needed{makemodel}   = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters;
   $joins_needed{partsgroup}  = 1;
   $joins_needed{pfac}        = 1;
   $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{cv}   || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
   $joins_needed{invoice_oi}  = 1 if $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
   $joins_needed{cv}          = 1 if $bsooqr;
   $joins_needed{apoe}        = 1 if $joins_needed{cv}   || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
   $joins_needed{invoice_oi}  = 1 if $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;


@@ -1467,8 +1500,8 @@ sub follow_account_chain {
 
   $form->{ACCOUNT_CHAIN_BY_ID} ||= {
     map { $_->{id} => $_ }
 
   $form->{ACCOUNT_CHAIN_BY_ID} ||= {
     map { $_->{id} => $_ }

-      selectall_hashref_query($form, $dbh, <<SQL) };
-    SELECT c.id, c.new_chart_id, ${transdate} >= c.valid_from AS is_valid, cnew.accno
+      selectall_hashref_query($form, $dbh, <<SQL, $transdate) };
+    SELECT c.id, c.new_chart_id, date(?) >= c.valid_from AS is_valid, cnew.accno

     FROM chart c
     LEFT JOIN chart cnew ON c.new_chart_id = cnew.id
     WHERE NOT c.new_chart_id IS NULL AND (c.new_chart_id > 0)
     FROM chart c
     LEFT JOIN chart cnew ON c.new_chart_id = cnew.id
     WHERE NOT c.new_chart_id IS NULL AND (c.new_chart_id > 0)


@@ -1516,9 +1549,9 @@ sub retrieve_accounts {
   }
 
   if ($transdate eq "") {
   }
 
   if ($transdate eq "") {

-    $transdate = "current_date";
+    $transdate = DateTime->today_local->to_lxoffice;

   } else {
   } else {

-    $transdate = 'date(' . $dbh->quote($transdate) . ')';
+    $transdate = $dbh->quote($transdate);

   }
   #/transdate
   my $inc_exp = $form->{"vc"} eq "customer" ? "income_accno_id" : "expense_accno_id";
   }
   #/transdate
   my $inc_exp = $form->{"vc"} eq "customer" ? "income_accno_id" : "expense_accno_id";


@@ -1551,7 +1584,7 @@ SQL
     WHERE t.id IN
       (SELECT tk.tax_id
        FROM taxkeys tk
     WHERE t.id IN
       (SELECT tk.tax_id
        FROM taxkeys tk

-       WHERE tk.chart_id = ? AND startdate <= ${transdate}
+       WHERE tk.chart_id = ? AND startdate <= ?

        ORDER BY startdate DESC LIMIT 1)
 SQL
 
        ORDER BY startdate DESC LIMIT 1)
 SQL
 


@@ -1569,7 +1602,7 @@ SQL
 
     $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} for qw(inventory income expense);
 
 
     $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} for qw(inventory income expense);
 

-    $sth_tax->execute($accounts{$inc_exp});
+    $sth_tax->execute($accounts{$inc_exp}, quote_db_date($transdate));

     $ref = $sth_tax->fetchrow_hashref or next;
 
     $form->{"taxaccounts_$index"} = $ref->{"accno"};
     $ref = $sth_tax->fetchrow_hashref or next;
 
     $form->{"taxaccounts_$index"} = $ref->{"accno"};


@@ -1643,7 +1676,7 @@ sub prepare_parts_for_printing {
   }
 
   my $placeholders = join ', ', ('?') x scalar(@part_ids);
   }
 
   my $placeholders = join ', ', ('?') x scalar(@part_ids);

-  my $query        = qq|SELECT mm.parts_id, mm.model, v.name AS make
+  my $query        = qq|SELECT mm.parts_id, mm.model, mm.lastcost, v.name AS make

                         FROM makemodel mm
                         LEFT JOIN vendor v ON (mm.make = v.id)
                         WHERE mm.parts_id IN ($placeholders)|;
                         FROM makemodel mm
                         LEFT JOIN vendor v ON (mm.make = v.id)
                         WHERE mm.parts_id IN ($placeholders)|;