SL/IC.pm

   1 #=====================================================================
   2 # LX-Office ERP
   3 # Copyright (C) 2004
   4 # Based on SQL-Ledger Version 2.1.9
   5 # Web http://www.lx-office.org
   6 #
   7 #=====================================================================
   8 # SQL-Ledger Accounting
   9 # Copyright (C) 2001
  10 #
  11 #  Author: Dieter Simader
  12 #   Email: dsimader@sql-ledger.org
  13 #     Web: http://www.sql-ledger.org
  14 #
  15 #  Contributors:
  16 #
  17 # This program is free software; you can redistribute it and/or modify
  18 # it under the terms of the GNU General Public License as published by
  19 # the Free Software Foundation; either version 2 of the License, or
  20 # (at your option) any later version.
  21 #
  22 # This program is distributed in the hope that it will be useful,
  23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  25 # GNU General Public License for more details.
  26 # You should have received a copy of the GNU General Public License
  27 # along with this program; if not, write to the Free Software
  28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
  29 #======================================================================
  30 #
  31 # Inventory Control backend
  32 #
  33 #======================================================================
  34
  35 package IC;
  36 use Data::Dumper;
  37 use SL::DBUtils;
  38
  39 sub get_part {
  40   $main::lxdebug->enter_sub();
  41
  42   my ($self, $myconfig, $form) = @_;
  43
  44   # connect to db
  45   my $dbh = $form->dbconnect($myconfig);
  46
  47   my $sth;
  48
  49   my $query =
  50     qq|SELECT p.*,
  51          c1.accno AS inventory_accno,
  52          c2.accno AS income_accno,
  53          c3.accno AS expense_accno,
  54          pg.partsgroup
  55        FROM parts p
  56        LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
  57        LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
  58        LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
  59        LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  60        WHERE p.id = ? |;
  61   my $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
  62
  63   # copy to $form variables
  64   map { $form->{$_} = $ref->{$_} } (keys %{$ref});
  65
  66   my %oid = ('Pg'     => 'a.oid',
  67              'Oracle' => 'a.rowid');
  68
  69   # part or service item
  70   $form->{item} = ($form->{inventory_accno}) ? 'part' : 'service';
  71   if ($form->{assembly}) {
  72     $form->{item} = 'assembly';
  73
  74     # retrieve assembly items
  75     $query =
  76       qq|SELECT p.id, p.partnumber, p.description,
  77            p.sellprice, p.weight, a.qty, a.bom, p.unit,
  78            pg.partsgroup
  79          FROM parts p
  80          JOIN assembly a ON (a.parts_id = p.id)
  81          LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  82          WHERE (a.id = ?)
  83          ORDER BY $oid{$myconfig->{dbdriver}}|;
  84     $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
  85
  86     $form->{assembly_rows} = 0;
  87     while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  88       $form->{assembly_rows}++;
  89       foreach my $key (keys %{$ref}) {
  90         $form->{"${key}_$form->{assembly_rows}"} = $ref->{$key};
  91       }
  92     }
  93     $sth->finish;
  94
  95   }
  96
  97   # setup accno hash for <option checked> {amount} is used in create_links
  98   $form->{amount}{IC}         = $form->{inventory_accno};
  99   $form->{amount}{IC_income}  = $form->{income_accno};
 100   $form->{amount}{IC_sale}    = $form->{income_accno};
 101   $form->{amount}{IC_expense} = $form->{expense_accno};
 102   $form->{amount}{IC_cogs}    = $form->{expense_accno};
 103
 104   my @pricegroups          = ();
 105   my @pricegroups_not_used = ();
 106
 107   # get prices
 108   $query =
 109     qq|SELECT p.parts_id, p.pricegroup_id, p.price,
 110          (SELECT pg.pricegroup
 111           FROM pricegroup pg
 112           WHERE pg.id = p.pricegroup_id) AS pricegroup
 113        FROM prices p
 114        WHERE (parts_id = ?)
 115        ORDER BY pricegroup|;
 116   $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
 117
 118   #for pricegroups
 119   my $i = 1;
 120   while (($form->{"klass_$i"}, $form->{"pricegroup_id_$i"},
 121           $form->{"price_$i"}, $form->{"pricegroup_$i"})
 122          = $sth->fetchrow_array()) {
 123     $form->{"price_$i"} = $form->round_amount($form->{"price_$i"}, 5);
 124     $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
 125     push @pricegroups, $form->{"pricegroup_id_$i"};
 126     $i++;
 127   }
 128
 129   $sth->finish;
 130
 131   # get pricegroups
 132   $query = qq|SELECT id, pricegroup FROM pricegroup|;
 133   $form->{PRICEGROUPS} = selectall_hashref_query($form, $dbh, $query);
 134
 135   #find not used pricegroups
 136   while ($tmp = pop(@{ $form->{PRICEGROUPS} })) {
 137     my $in_use = 0;
 138     foreach my $item (@pricegroups) {
 139       if ($item eq $tmp->{id}) {
 140         $in_use = 1;
 141         last;
 142       }
 143     }
 144     push(@pricegroups_not_used, $tmp) unless ($in_use);
 145   }
 146
 147   # if not used pricegroups are avaible
 148   if (@pricegroups_not_used) {
 149
 150     foreach $name (@pricegroups_not_used) {
 151       $form->{"klass_$i"} = "$name->{id}";
 152       $form->{"price_$i"} = $form->round_amount($form->{sellprice}, 5);
 153       $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
 154       $form->{"pricegroup_id_$i"} = "$name->{id}";
 155       $form->{"pricegroup_$i"}    = "$name->{pricegroup}";
 156       $i++;
 157     }
 158   }
 159
 160   #correct rows
 161   $form->{price_rows} = $i - 1;
 162
 163   unless ($form->{item} eq 'service') {
 164
 165     # get makes
 166     if ($form->{makemodel}) {
 167       $query = qq|SELECT m.make, m.model FROM makemodel m | .
 168                qq|WHERE m.parts_id = ?|;
 169       @values = ($form->{id});
 170       $sth = $dbh->prepare($query);
 171       $sth->execute(@values) || $form->dberror("$query (" . join(', ', @values) . ")");
 172
 173       my $i = 1;
 174       while (($form->{"make_$i"}, $form->{"model_$i"}) = $sth->fetchrow_array)
 175       {
 176         $i++;
 177       }
 178       $sth->finish;
 179       $form->{makemodel_rows} = $i - 1;
 180
 181     }
 182   }
 183
 184   # get translations
 185   $form->{language_values} = "";
 186   $query = qq|SELECT language_id, translation FROM translation WHERE parts_id = ?|;
 187   my $trq = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
 188   while ($tr = $trq->fetchrow_hashref(NAME_lc)) {
 189     $form->{language_values} .= "---+++---".$tr->{language_id}."--++--".$tr->{translation};
 190   }
 191   $trq->finish;
 192
 193   # now get accno for taxes
 194   $query =
 195     qq|SELECT c.accno
 196        FROM chart c, partstax pt
 197        WHERE (pt.chart_id = c.id) AND (pt.parts_id = ?)|;
 198   $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
 199   while (($key) = $sth->fetchrow_array) {
 200     $form->{amount}{$key} = $key;
 201   }
 202
 203   $sth->finish;
 204
 205   # is it an orphan
 206   $query =
 207     qq|SELECT i.parts_id
 208        FROM invoice i
 209        WHERE (i.parts_id = ?)
 210
 211        UNION
 212
 213        SELECT o.parts_id
 214        FROM orderitems o
 215        WHERE (o.parts_id = ?)
 216
 217        UNION
 218
 219        SELECT a.parts_id
 220        FROM assembly a
 221        WHERE (a.parts_id = ?)|;
 222   @values = (conv_i($form->{id}), conv_i($form->{id}), conv_i($form->{id}));
 223   ($form->{orphaned}) = selectrow_query($form, $dbh, $query, @values);
 224   $form->{orphaned} = !$form->{orphaned};
 225
 226   $form->{"unit_changeable"} = 1;
 227   foreach my $table (qw(invoice assembly orderitems inventory license)) {
 228     $query = qq|SELECT COUNT(*) FROM $table WHERE parts_id = ?|;
 229     my ($count) = selectrow_query($form, $dbh, $query, conv_i($form->{"id"}));
 230
 231     if ($count) {
 232       $form->{"unit_changeable"} = 0;
 233       last;
 234     }
 235   }
 236
 237   $dbh->disconnect;
 238
 239   $main::lxdebug->leave_sub();
 240 }
 241
 242 sub get_pricegroups {
 243   $main::lxdebug->enter_sub();
 244
 245   my ($self, $myconfig, $form) = @_;
 246
 247   my $dbh = $form->dbconnect($myconfig);
 248
 249   # get pricegroups
 250   my $query = qq|SELECT id, pricegroup FROM pricegroup|;
 251   my $pricegroups = selectall_hashref_query($form, $dbh, $query);
 252
 253   my $i = 1;
 254   foreach $pg (@{ $pricegroups }) {
 255     $form->{"klass_$i"} = "$pg->{id}";
 256     $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
 257     $form->{"pricegroup_id_$i"} = "$pg->{id}";
 258     $form->{"pricegroup_$i"}    = "$pg->{pricegroup}";
 259     $i++;
 260   }
 261
 262   #correct rows
 263   $form->{price_rows} = $i - 1;
 264
 265   $dbh->disconnect;
 266
 267   $main::lxdebug->leave_sub();
 268 }
 269
 270 sub retrieve_buchungsgruppen {
 271   $main::lxdebug->enter_sub();
 272
 273   my ($self, $myconfig, $form) = @_;
 274
 275   my ($query, $sth);
 276
 277   my $dbh = $form->dbconnect($myconfig);
 278
 279   # get buchungsgruppen
 280   $query = qq|SELECT id, description FROM buchungsgruppen ORDER BY sortkey|;
 281   $form->{BUCHUNGSGRUPPEN} = selectall_hashref_query($form, $dbh, $query);
 282
 283   $main::lxdebug->leave_sub();
 284 }
 285
 286 sub save {
 287   $main::lxdebug->enter_sub();
 288
 289   my ($self, $myconfig, $form) = @_;
 290   my @values;
 291   # connect to database, turn off AutoCommit
 292   my $dbh = $form->dbconnect_noauto($myconfig);
 293
 294   # save the part
 295   # make up a unique handle and store in partnumber field
 296   # then retrieve the record based on the unique handle to get the id
 297   # replace the partnumber field with the actual variable
 298   # add records for makemodel
 299
 300   # if there is a $form->{id} then replace the old entry
 301   # delete all makemodel entries and add the new ones
 302
 303   # undo amount formatting
 304   map { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) }
 305     qw(rop weight listprice sellprice gv lastcost stock);
 306
 307   my $makemodel = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0;
 308
 309   $form->{assembly} = ($form->{item} eq 'assembly') ? 1 : 0;
 310
 311   my ($query, $sth);
 312
 313   if ($form->{id}) {
 314
 315     # get old price
 316     $query = qq|SELECT sellprice, weight FROM parts WHERE id = ?|;
 317     my ($sellprice, $weight) = selectrow_query($form, $dbh, $query, conv_i($form->{id}));
 318
 319     # if item is part of an assembly adjust all assemblies
 320     $query = qq|SELECT id, qty FROM assembly WHERE parts_id = ?|;
 321     $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
 322     while (my ($id, $qty) = $sth->fetchrow_array) {
 323       &update_assembly($dbh, $form, $id, $qty, $sellprice * 1, $weight * 1);
 324     }
 325     $sth->finish;
 326
 327     if ($form->{item} ne 'service') {
 328       # delete makemodel records
 329       do_query($form, $dbh, qq|DELETE FROM makemodel WHERE parts_id = ?|, conv_i($form->{id}));
 330     }
 331
 332     if ($form->{item} eq 'assembly') {
 333       if ($form->{onhand} != 0) {
 334         &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand} * -1);
 335       }
 336
 337       # delete assembly records
 338       do_query($form, $dbh, qq|DELETE FROM assembly WHERE id = ?|, conv_i($form->{id}));
 339
 340       $form->{onhand} += $form->{stock};
 341     }
 342
 343     # delete tax records
 344     do_query($form, $dbh, qq|DELETE FROM partstax WHERE parts_id = ?|, conv_i($form->{id}));
 345
 346     # delete translations
 347     do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
 348
 349   } else {
 350     my ($count) = selectrow_query($form, $dbh, qq|SELECT COUNT(*) FROM parts WHERE partnumber = ?|, $form->{partnumber});
 351     if ($count) {
 352       $main::lxdebug->leave_sub();
 353       return 3;
 354     }
 355
 356     ($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);
 357     do_query($form, $dbh, qq|INSERT INTO parts (id, partnumber) VALUES (?, '')|, $form->{id});
 358
 359     $form->{orphaned} = 1;
 360     $form->{onhand} = $form->{stock} if $form->{item} eq 'assembly';
 361     if ($form->{partnumber} eq "" && $form->{"item"} eq "service") {
 362       $form->{partnumber} = $form->update_defaults($myconfig, "servicenumber");
 363     }
 364     if ($form->{partnumber} eq "" && $form->{"item"} ne "service") {
 365       $form->{partnumber} = $form->update_defaults($myconfig, "articlenumber");
 366     }
 367
 368   }
 369   my $partsgroup_id = 0;
 370
 371   if ($form->{partsgroup}) {
 372     ($partsgroup, $partsgroup_id) = split(/--/, $form->{partsgroup});
 373   }
 374
 375   my ($subq_inventory, $subq_expense, $subq_income);
 376   if ($form->{"item"} eq "part") {
 377     $subq_inventory =
 378       qq|(SELECT bg.inventory_accno_id
 379           FROM buchungsgruppen bg
 380           WHERE bg.id = | . conv_i($form->{"buchungsgruppen_id"}, 'NULL') . qq|)|;
 381   } else {
 382     $subq_inventory = "NULL";
 383   }
 384
 385   if ($form->{"item"} ne "assembly") {
 386     $subq_expense =
 387       qq|(SELECT bg.expense_accno_id_0
 388           FROM buchungsgruppen bg
 389           WHERE bg.id = | . conv_i($form->{"buchungsgruppen_id"}, 'NULL') . qq|)|;
 390   } else {
 391     $subq_expense = "NULL";
 392   }
 393
 394   $query =
 395     qq|UPDATE parts SET
 396          partnumber = ?,
 397          description = ?,
 398          makemodel = ?,
 399          alternate = 'f',
 400          assembly = ?,
 401          listprice = ?,
 402          sellprice = ?,
 403          lastcost = ?,
 404          weight = ?,
 405          priceupdate = ?,
 406          unit = ?,
 407          notes = ?,
 408          formel = ?,
 409          rop = ?,
 410          bin = ?,
 411          buchungsgruppen_id = ?,
 412          payment_id = ?,
 413          inventory_accno_id = $subq_inventory,
 414          income_accno_id = (SELECT bg.income_accno_id_0 FROM buchungsgruppen bg WHERE bg.id = ?),
 415          expense_accno_id = $subq_expense,
 416          obsolete = ?,
 417          image = ?,
 418          drawing = ?,
 419          shop = ?,
 420          ve = ?,
 421          gv = ?,
 422          ean = ?,
 423          not_discountable = ?,
 424          microfiche = ?,
 425          partsgroup_id = ?
 426        WHERE id = ?|;
 427   @values = ($form->{partnumber},
 428              $form->{description},
 429              $makemodel ? 't' : 'f',
 430              $form->{assembly} ? 't' : 'f',
 431              $form->{listprice},
 432              $form->{sellprice},
 433              $form->{lastcost},
 434              $form->{weight},
 435              conv_date($form->{priceupdate}),
 436              $form->{unit},
 437              $form->{notes},
 438              $form->{formel},
 439              $form->{rop},
 440              $form->{bin},
 441              conv_i($form->{buchungsgruppen_id}),
 442              conv_i($form->{payment_id}),
 443              conv_i($form->{buchungsgruppen_id}),
 444              $form->{obsolete} ? 't' : 'f',
 445              $form->{image},
 446              $form->{drawing},
 447              $form->{shop} ? 't' : 'f',
 448              conv_i($form->{ve}),
 449              conv_i($form->{gv}),
 450              $form->{ean},
 451              $form->{not_discountable} ? 't' : 'f',
 452              $form->{microfiche},
 453              conv_i($partsgroup_id),
 454              conv_i($form->{id})
 455   );
 456   do_query($form, $dbh, $query, @values);
 457
 458   # delete translation records
 459   do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
 460
 461   if ($form->{language_values} ne "") {
 462     foreach $item (split(/---\+\+\+---/, $form->{language_values})) {
 463       my ($language_id, $translation, $longdescription) = split(/--\+\+--/, $item);
 464       if ($translation ne "") {
 465         $query = qq|INSERT into translation (parts_id, language_id, translation, longdescription)
 466                     VALUES ( ?, ?, ?, ? )|;
 467         @values = (conv_i($form->{id}), conv_i($language_id), $translation, $longdescription);
 468         do_query($form, $dbh, $query, @values);
 469       }
 470     }
 471   }
 472
 473   # delete price records
 474   do_query($form, $dbh, qq|DELETE FROM prices WHERE parts_id = ?|, conv_i($form->{id}));
 475
 476   # insert price records only if different to sellprice
 477   for my $i (1 .. $form->{price_rows}) {
 478     if ($form->{"price_$i"} eq "0") {
 479       $form->{"price_$i"} = $form->{sellprice};
 480     }
 481     if (
 482         (   $form->{"price_$i"}
 483          || $form->{"klass_$i"}
 484          || $form->{"pricegroup_id_$i"})
 485         and $form->{"price_$i"} != $form->{sellprice}
 486       ) {
 487       #$klass = $form->parse_amount($myconfig, $form->{"klass_$i"});
 488       $price = $form->parse_amount($myconfig, $form->{"price_$i"});
 489       $pricegroup_id =
 490         $form->parse_amount($myconfig, $form->{"pricegroup_id_$i"});
 491       $query = qq|INSERT INTO prices (parts_id, pricegroup_id, price) | .
 492                qq|VALUES(?, ?, ?)|;
 493       @values = (conv_i($form->{id}), conv_i($pricegroup_id), $price);
 494       do_query($form, $dbh, $query, @values);
 495     }
 496   }
 497
 498   # insert makemodel records
 499   unless ($form->{item} eq 'service') {
 500     for my $i (1 .. $form->{makemodel_rows}) {
 501       if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
 502         map { $form->{"${_}_$i"} =~ s/\'/\'\'/g } qw(make model);
 503
 504         $query = qq|INSERT INTO makemodel (parts_id, make, model) | .
 505                              qq|VALUES (?, ?, ?)|;
 506                     @values = (conv_i($form->{id}), $form->{"make_$i"}, $form->{"model_$i"});
 507         do_query($form, $dbh, $query, @values);
 508       }
 509     }
 510   }
 511
 512   # insert taxes
 513   foreach $item (split(/ /, $form->{taxaccounts})) {
 514     if ($form->{"IC_tax_$item"}) {
 515       $query =
 516         qq|INSERT INTO partstax (parts_id, chart_id)
 517            VALUES (?, (SELECT id FROM chart WHERE accno = ?))|;
 518                         @values = (conv_i($form->{id}), $item);
 519       do_query($form, $dbh, $query, @values);
 520     }
 521   }
 522
 523   # add assembly records
 524   if ($form->{item} eq 'assembly') {
 525
 526     for my $i (1 .. $form->{assembly_rows}) {
 527       $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
 528
 529       if ($form->{"qty_$i"} != 0) {
 530         $form->{"bom_$i"} *= 1;
 531         $query = qq|INSERT INTO assembly (id, parts_id, qty, bom) | .
 532                              qq|VALUES (?, ?, ?, ?)|;
 533                     @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}), conv_i($form->{"qty_$i"}), $form->{"bom_$i"} ? 't' : 'f');
 534         do_query($form, $dbh, $query, @values);
 535       }
 536     }
 537
 538     # adjust onhand for the parts
 539     if ($form->{onhand} != 0) {
 540       &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand});
 541     }
 542
 543     @a = localtime;
 544     $a[5] += 1900;
 545     $a[4]++;
 546     my $shippingdate = "$a[5]-$a[4]-$a[3]";
 547
 548     $form->get_employee($dbh);
 549
 550     # add inventory record
 551     $query =
 552       qq|INSERT INTO inventory (warehouse_id, parts_id, qty, shippingdate, employee_id)
 553          VALUES (0, ?, ?, '$shippingdate', ?)|;
 554     @values = (conv_i($form->{id}), $form->{stock}, conv_i($form->{employee_id}));
 555     do_query($form, $dbh, $query, @values);
 556
 557   }
 558
 559   #set expense_accno=inventory_accno if they are different => bilanz
 560   $vendor_accno =
 561     ($form->{expense_accno} != $form->{inventory_accno})
 562     ? $form->{inventory_accno}
 563     : $form->{expense_accno};
 564
 565   # get tax rates and description
 566   $accno_id =
 567     ($form->{vc} eq "customer") ? $form->{income_accno} : $vendor_accno;
 568   $query =
 569     qq|SELECT c.accno, c.description, t.rate, t.taxnumber
 570        FROM chart c, tax t
 571        WHERE (c.id = t.chart_id) AND (t.taxkey IN (SELECT taxkey_id FROM chart where accno = ?))
 572        ORDER BY c.accno|;
 573   $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
 574
 575   $form->{taxaccount} = "";
 576   while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
 577     $form->{taxaccount} .= "$ptr->{accno} ";
 578     if (!($form->{taxaccount2} =~ /$ptr->{accno}/)) {
 579       $form->{"$ptr->{accno}_rate"}        = $ptr->{rate};
 580       $form->{"$ptr->{accno}_description"} = $ptr->{description};
 581       $form->{"$ptr->{accno}_taxnumber"}   = $ptr->{taxnumber};
 582       $form->{taxaccount2} .= " $ptr->{accno} ";
 583     }
 584   }
 585
 586   # commit
 587   my $rc = $dbh->commit;
 588   $dbh->disconnect;
 589
 590   $main::lxdebug->leave_sub();
 591
 592   return $rc;
 593 }
 594
 595 sub update_assembly {
 596   $main::lxdebug->enter_sub();
 597
 598   my ($dbh, $form, $id, $qty, $sellprice, $weight) = @_;
 599
 600   my $query = qq|SELECT id, qty FROM assembly WHERE parts_id = ?|;
 601   my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
 602
 603   while (my ($pid, $aqty) = $sth->fetchrow_array) {
 604     &update_assembly($dbh, $form, $pid, $aqty * $qty, $sellprice, $weight);
 605   }
 606   $sth->finish;
 607
 608   $query =
 609     qq|UPDATE parts SET sellprice = sellprice + ?, weight = weight + ?
 610        WHERE id = ?|;
 611   @values = ($qty * ($form->{sellprice} - $sellprice),
 612              $qty * ($form->{weight} - $weight), conv_i($id));
 613   do_query($form, $dbh, $query, @values);
 614
 615   $main::lxdebug->leave_sub();
 616 }
 617
 618 sub retrieve_assemblies {
 619   $main::lxdebug->enter_sub();
 620
 621   my ($self, $myconfig, $form) = @_;
 622
 623   # connect to database
 624   my $dbh = $form->dbconnect($myconfig);
 625
 626   my $where = qq|NOT p.obsolete|;
 627   my @values;
 628
 629   if ($form->{partnumber}) {
 630     $where .= qq| AND (p.partnumber ILIKE ?)|;
 631     push(@values, '%' . $form->{partnumber} . '%');
 632   }
 633
 634   if ($form->{description}) {
 635     $where .= qq| AND (p.description ILIKE ?)|;
 636     push(@values, '%' . $form->{description} . '%');
 637   }
 638
 639   # retrieve assembly items
 640   my $query =
 641     qq|SELECT p.id, p.partnumber, p.description,
 642          p.bin, p.onhand, p.rop,
 643          (SELECT sum(p2.inventory_accno_id)
 644           FROM parts p2, assembly a
 645           WHERE (p2.id = a.parts_id) AND (a.id = p.id)) AS inventory
 646        FROM parts p
 647        WHERE NOT p.obsolete AND p.assembly $where|;
 648
 649   $form->{assembly_items} = selectall_hashref_query($form, $dbh, $query, @values);
 650
 651   $dbh->disconnect;
 652
 653   $main::lxdebug->leave_sub();
 654 }
 655
 656 sub restock_assemblies {
 657   $main::lxdebug->enter_sub();
 658
 659   my ($self, $myconfig, $form) = @_;
 660
 661   # connect to database
 662   my $dbh = $form->dbconnect_noauto($myconfig);
 663
 664   for my $i (1 .. $form->{rowcount}) {
 665
 666     $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
 667
 668     if ($form->{"qty_$i"} != 0) {
 669       &adjust_inventory($dbh, $form, $form->{"id_$i"}, $form->{"qty_$i"});
 670     }
 671
 672   }
 673
 674   my $rc = $dbh->commit;
 675   $dbh->disconnect;
 676
 677   $main::lxdebug->leave_sub();
 678
 679   return $rc;
 680 }
 681
 682 sub adjust_inventory {
 683   $main::lxdebug->enter_sub();
 684
 685   my ($dbh, $form, $id, $qty) = @_;
 686
 687   my $query =
 688     qq|SELECT p.id, p.inventory_accno_id, p.assembly, a.qty
 689        FROM parts p, assembly a
 690        WHERE (a.parts_id = p.id) AND (a.id = ?)|;
 691   my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
 692
 693   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 694
 695     my $allocate = $qty * $ref->{qty};
 696
 697     # is it a service item, then loop
 698     $ref->{inventory_accno_id} *= 1;
 699     next if (($ref->{inventory_accno_id} == 0) && !$ref->{assembly});
 700
 701     # adjust parts onhand
 702     $form->update_balance($dbh, "parts", "onhand",
 703                           qq|id = $ref->{id}|,
 704                           $allocate * -1);
 705   }
 706
 707   $sth->finish;
 708
 709   # update assembly
 710   my $rc = $form->update_balance($dbh, "parts", "onhand", qq|id = ?|, $qty, $id);
 711
 712   $main::lxdebug->leave_sub();
 713
 714   return $rc;
 715 }
 716
 717 sub delete {
 718   $main::lxdebug->enter_sub();
 719
 720   my ($self, $myconfig, $form) = @_;
 721   my @values = (conv_i($form->{id}));
 722   # connect to database, turn off AutoCommit
 723   my $dbh = $form->dbconnect_noauto($myconfig);
 724
 725   my %columns = ( "assembly" => "id", "alternate" => "id", "parts" => "id" );
 726
 727   for my $table (qw(prices partstax makemodel inventory assembly parts)) {
 728     my $column = defined($columns{$table}) ? $columns{$table} : "parts_id";
 729     do_query($form, $dbh, qq|DELETE FROM $table WHERE $column = ?|, @values);
 730   }
 731
 732   # commit
 733   my $rc = $dbh->commit;
 734   $dbh->disconnect;
 735
 736   $main::lxdebug->leave_sub();
 737
 738   return $rc;
 739 }
 740
 741 sub assembly_item {
 742   $main::lxdebug->enter_sub();
 743
 744   my ($self, $myconfig, $form) = @_;
 745
 746   my $i = $form->{assembly_rows};
 747   my $var;
 748   my $where = qq|1 = 1|;
 749   my @values;
 750
 751   my %columns = ("partnumber" => "p", "description" => "p", "partsgroup" => "pg");
 752
 753   while (my ($column, $table) = each(%columns)) {
 754     next unless ($form->{"${column}_$i"});
 755     $where .= qq| AND ${table}.${column} ILIKE ?|;
 756     push(@values, '%' . $form->{"${column}_$i"} . '%');
 757   }
 758
 759   if ($form->{id}) {
 760     $where .= qq| AND NOT (p.id = ?)|;
 761     push(@values, conv_i($form->{id}));
 762   }
 763
 764   if ($partnumber) {
 765     $where .= qq| ORDER BY p.partnumber|;
 766   } else {
 767     $where .= qq| ORDER BY p.description|;
 768   }
 769
 770   # connect to database
 771   my $dbh = $form->dbconnect($myconfig);
 772
 773   my $query =
 774     qq|SELECT p.id, p.partnumber, p.description, p.sellprice, p.weight, p.onhand, p.unit, pg.partsgroup
 775        FROM parts p
 776        LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
 777        WHERE $where|;
 778   $form->{item_list} = selectall_hashref_query($form, $dbh, $query, @values);
 779
 780   $dbh->disconnect;
 781
 782   $main::lxdebug->leave_sub();
 783 }
 784
 785 #
 786 # Report for Wares.
 787 # Warning, deep magic ahead.
 788 # This function gets all parts from the database according to the filters specified
 789 #
 790 # filter strings:
 791 #   partnumber ean description partsgroup serialnumber make model drawing microfiche
 792 #   transdatefrom transdateto sort
 793 #
 794 # exclusives:
 795 #   itemstatus  = active | onhand | short | obsolete | orphaned
 796 #   searchitems = part | assembly | service
 797 #
 798 # column flags:
 799 #   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
 800 #
 801 # binary flags:
 802 #   bought sold onorder ordered rfq quoted onhand short
 803 #   l_serialnumber l_linetotal l_subtotal l_soldtotal l_deliverydate
 804 #   revers top100
 805 #
 806 sub all_parts {
 807   $main::lxdebug->enter_sub();
 808
 809   my ($self, $myconfig, $form) = @_;
 810
 811   my $where = qq|1 = 1|;
 812   my (@values, $var, $flds, $group, $limit);
 813
 814   foreach my $item (qw(partnumber drawing microfiche ean pg.partsgroup)) {
 815     my $column = $item;
 816     $column =~ s/.*\.//; # get rid of table prefixes
 817     if ($form->{$column}) {
 818       $where .= qq| AND ($item ILIKE ?)|;
 819       push(@values, "%$form->{$column}%");
 820     }
 821   }
 822
 823   # special case for description
 824   if ($form->{description}
 825       && !(   $form->{bought}  || $form->{sold} || $form->{onorder}
 826            || $form->{ordered} || $form->{rfq} || $form->{quoted})) {
 827     $where .= qq| AND (p.description ILIKE ?)|;
 828     push(@values, "%$form->{description}%");
 829   }
 830
 831   # special case for serialnumber
 832   if ($form->{l_serialnumber} && $form->{serialnumber}) {
 833     $where .= qq| AND (serialnumber ILIKE ?)|;
 834     push(@values, "%$form->{serialnumber}%");
 835   }
 836
 837   if ($form->{searchitems} eq 'part') {
 838     $where .= qq| AND (p.inventory_accno_id > 0) |;
 839   }
 840
 841   if ($form->{searchitems} eq 'assembly') {
 842     $form->{bought} = "";
 843     $where .= qq| AND p.assembly|;
 844   }
 845
 846   if ($form->{searchitems} eq 'service') {
 847     $where .= qq| AND (p.inventory_accno_id IS NULL) AND NOT (p.assembly = '1')|;
 848     # irrelevant for services
 849     map { $form->{$_} = '' } qw(make model);
 850   }
 851
 852   # items which were never bought, sold or on an order
 853   if ($form->{itemstatus} eq 'orphaned') {
 854     map { $form->{$_} = 0  } qw(onhand short bought sold onorder ordered rfq quoted);
 855     map { $form->{$_} = '' } qw(transdatefrom transdateto);
 856
 857     $where .=
 858       qq| AND (p.onhand = 0)
 859           AND p.id NOT IN
 860             (
 861               SELECT DISTINCT parts_id FROM invoice
 862               UNION
 863               SELECT DISTINCT parts_id FROM assembly
 864               UNION
 865               SELECT DISTINCT parts_id FROM orderitems
 866             )|;
 867   }
 868
 869   my %status2condition = (
 870     active   => " AND (p.obsolete = '0')",
 871     obsolete => " AND (p.obsolete = '1')",
 872     onhand   => " AND (p.onhand > 0)",
 873     short    => " AND (p.onhand < p.rop)",
 874   );
 875   $where .= $status2condition{$form->{itemstatus}};
 876
 877   $form->{onhand} = $form->{short} = 0 if ($form->{itemstatus} eq 'obsolete');
 878
 879   my @subcolumns;
 880   foreach my $column (qw(make model)) {
 881     push @subcolumns, $column if $form->{$column};
 882   }
 883   if (@subcolumns) {
 884     $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE | . (join " AND ", map { "($_ ILIKE ?)"; } @subcolumns) . ")";
 885     push @values, map { '%' . $form->{$_} . '%' } @subcolumns;
 886   }
 887
 888   if ($form->{l_soldtotal}) {
 889     $where .= qq| AND (p.id = i.parts_id) AND (i.qty >= 0)|;
 890     $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|;
 891   }
 892
 893   $limit = qq| LIMIT 100| if ($form->{top100});
 894
 895   # connect to database
 896   my $dbh = $form->dbconnect($myconfig);
 897
 898   my @sort_cols = qw(id partnumber description partsgroup bin priceupdate onhand
 899                      invnumber ordnumber quonumber name drawing microfiche
 900                      serialnumber soldtotal deliverydate);
 901
 902   my $sortorder = "partnumber";
 903   $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } @sort_cols));
 904   $sortorder .= " DESC" if ($form->{revers});
 905
 906   my $query = "";
 907
 908   if ($form->{l_soldtotal}) {
 909     $form->{soldtotal} = 'soldtotal';
 910     $query =
 911       qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit,
 912            p.bin, p.sellprice, p.listprice, p.lastcost,
 913            p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal
 914          FROM parts p
 915          LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id), invoice i
 916          WHERE $where
 917          $group
 918          ORDER BY $sortorder $limit|;
 919   } else {
 920     $query =
 921       qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit,
 922            p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.weight,
 923            p.priceupdate, p.image, p.drawing, p.microfiche,
 924            pg.partsgroup
 925          FROM parts p
 926          LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
 927          WHERE $where
 928          $group
 929          ORDER BY $sortorder $limit|;
 930   }
 931
 932   my @all_values = @values;
 933
 934   # rebuild query for bought and sold items
 935   if (   $form->{bought}
 936       || $form->{sold}
 937       || $form->{onorder}
 938       || $form->{ordered}
 939       || $form->{rfq}
 940       || $form->{quoted}) {
 941     my $union = "";
 942     $query = "";
 943     @all_values = ();
 944
 945     if ($form->{bought} || $form->{sold}) {
 946
 947       my @invvalues = @values;
 948       my $invwhere = "$where";
 949       $invwhere .= qq| AND i.assemblyitem = '0'|;
 950
 951       if ($form->{transdatefrom}) {
 952         $invwhere .= qq| AND a.transdate >= ?|;
 953         push(@invvalues, $form->{transdatefrom});
 954       }
 955
 956       if ($form->{transdateto}) {
 957         $invwhere .= qq| AND a.transdate <= ?|;
 958         push(@invvalues, $form->{transdateto});
 959       }
 960
 961       if ($form->{description}) {
 962         $invwhere .= qq| AND i.description ILIKE ?|;
 963         push(@invvalues, '%' . $form->{description} . '%');
 964       }
 965
 966       $flds =
 967         qq|p.id, p.partnumber, i.description, i.serialnumber,
 968            i.qty AS onhand, i.unit, p.bin, i.sellprice,
 969            p.listprice, p.lastcost, p.rop, p.weight,
 970            p.priceupdate, p.image, p.drawing, p.microfiche,
 971            pg.partsgroup,
 972            a.invnumber, a.ordnumber, a.quonumber, i.trans_id,
 973            ct.name, i.deliverydate|;
 974
 975       if ($form->{bought}) {
 976         $query =
 977           qq|SELECT $flds, 'ir' AS module, '' AS type, 1 AS exchangerate
 978              FROM invoice i
 979              JOIN parts p ON (p.id = i.parts_id)
 980              JOIN ap a ON (a.id = i.trans_id)
 981              JOIN vendor ct ON (a.vendor_id = ct.id)
 982              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
 983              WHERE $invwhere|;
 984
 985         $union = qq| UNION |;
 986
 987         push(@all_values, @invvalues);
 988       }
 989
 990       if ($form->{sold}) {
 991         $query .=
 992           qq|$union
 993
 994              SELECT $flds, 'is' AS module, '' AS type, 1 As exchangerate
 995              FROM invoice i
 996              JOIN parts p ON (p.id = i.parts_id)
 997              JOIN ar a ON (a.id = i.trans_id)
 998              JOIN customer ct ON (a.customer_id = ct.id)
 999              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1000              WHERE $invwhere|;
1001         $union = qq| UNION |;
1002
1003         push(@all_values, @invvalues);
1004       }
1005     }
1006
1007     if ($form->{onorder} || $form->{ordered}) {
1008       my @ordvalues = @values;
1009       my $ordwhere = $where . qq| AND o.quotation = '0'|;
1010
1011       if ($form->{transdatefrom}) {
1012         $ordwhere .= qq| AND o.transdate >= ?|;
1013         push(@ordvalues, $form->{transdatefrom});
1014       }
1015
1016       if ($form->{transdateto}) {
1017         $ordwhere .= qq| AND o.transdate <= ?|;
1018         push(@ordvalues, $form->{transdateto});
1019       }
1020
1021       if ($form->{description}) {
1022         $ordwhere .= qq| AND oi.description ILIKE ?|;
1023         push(@ordvalues, '%' . $form->{description} . '%');
1024       }
1025
1026       if ($form->{ordered}) {
1027         $query .=
1028           qq|$union
1029
1030              SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
1031                oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
1032                p.listprice, p.lastcost, p.rop, p.weight,
1033                p.priceupdate, p.image, p.drawing, p.microfiche,
1034                pg.partsgroup,
1035                '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
1036                ct.name, NULL AS deliverydate,
1037                'oe' AS module, 'sales_order' AS type,
1038                (SELECT buy FROM exchangerate ex
1039                 WHERE ex.curr = o.curr AND ex.transdate = o.transdate) AS exchangerate
1040              FROM orderitems oi
1041              JOIN parts p ON (oi.parts_id = p.id)
1042              JOIN oe o ON (oi.trans_id = o.id)
1043              JOIN customer ct ON (o.customer_id = ct.id)
1044              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1045              WHERE $ordwhere AND (o.customer_id > 0)|;
1046         $union = qq| UNION |;
1047
1048         push(@all_values, @ordvalues);
1049       }
1050
1051       if ($form->{onorder}) {
1052         $query .=
1053           qq|$union
1054
1055              SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
1056                oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
1057                p.listprice, p.lastcost, p.rop, p.weight,
1058                p.priceupdate, p.image, p.drawing, p.microfiche,
1059                pg.partsgroup,
1060                '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
1061                ct.name, NULL AS deliverydate,
1062                'oe' AS module, 'purchase_order' AS type,
1063                (SELECT sell FROM exchangerate ex
1064                WHERE ex.curr = o.curr AND (ex.transdate = o.transdate)) AS exchangerate
1065              FROM orderitems oi
1066              JOIN parts p ON (oi.parts_id = p.id)
1067              JOIN oe o ON (oi.trans_id = o.id)
1068              JOIN vendor ct ON (o.vendor_id = ct.id)
1069              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1070              WHERE $ordwhere AND (o.vendor_id > 0)|;
1071         $union = qq| UNION |;
1072
1073         push(@all_values, @ordvalues);
1074       }
1075
1076     }
1077
1078     if ($form->{rfq} || $form->{quoted}) {
1079       my $quowhere = $where . qq| AND o.quotation = '1'|;
1080       my @quovalues = @values;
1081
1082       if ($form->{transdatefrom}) {
1083         $quowhere .= qq| AND o.transdate >= ?|;
1084         push(@quovalues, $form->{transdatefrom});
1085       }
1086
1087       if ($form->{transdateto}) {
1088         $quowhere .= qq| AND o.transdate <= ?|;
1089         push(@quovalues, $form->{transdateto});
1090       }
1091
1092       if ($form->{description}) {
1093         $quowhere .= qq| AND oi.description ILIKE ?|;
1094         push(@quovalues, '%' . $form->{description} . '%');
1095       }
1096
1097       if ($form->{quoted}) {
1098         $query .=
1099           qq|$union
1100
1101              SELECT
1102                p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
1103                oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
1104                p.listprice, p.lastcost, p.rop, p.weight,
1105                p.priceupdate, p.image, p.drawing, p.microfiche,
1106                pg.partsgroup,
1107                '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
1108                ct.name, NULL AS deliverydate, 'oe' AS module, 'sales_quotation' AS type,
1109                (SELECT buy FROM exchangerate ex
1110                 WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate
1111              FROM orderitems oi
1112              JOIN parts p ON (oi.parts_id = p.id)
1113              JOIN oe o ON (oi.trans_id = o.id)
1114              JOIN customer ct ON (o.customer_id = ct.id)
1115              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1116              WHERE $quowhere
1117              AND o.customer_id > 0|;
1118         $union = qq| UNION |;
1119
1120         push(@all_values, @quovalues);
1121       }
1122
1123       if ($form->{rfq}) {
1124         $query .=
1125           qq|$union
1126
1127              SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
1128                oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
1129                p.listprice, p.lastcost, p.rop, p.weight,
1130                p.priceupdate, p.image, p.drawing, p.microfiche,
1131                pg.partsgroup,
1132                '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
1133                ct.name, NULL AS deliverydate,
1134                'oe' AS module, 'request_quotation' AS type,
1135                (SELECT sell FROM exchangerate ex
1136                WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate
1137              FROM orderitems oi
1138              JOIN parts p ON (oi.parts_id = p.id)
1139              JOIN oe o ON (oi.trans_id = o.id)
1140              JOIN vendor ct ON (o.vendor_id = ct.id)
1141              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1142              WHERE $quowhere
1143              AND o.vendor_id > 0|;
1144
1145         push(@all_values, @quovalues);
1146       }
1147
1148     }
1149     $query .= qq| ORDER BY  | . $sortorder;
1150
1151   }
1152
1153   $form->{parts} = selectall_hashref_query($form, $dbh, $query, @all_values);
1154
1155   my @assemblies;
1156   # include individual items for assemblies
1157   if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
1158     $query =
1159       qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,
1160            p.unit, p.bin,
1161            p.sellprice, p.listprice, p.lastcost,
1162            p.rop, p.weight, p.priceupdate,
1163            p.image, p.drawing, p.microfiche
1164          FROM parts p, assembly a
1165          WHERE (p.id = a.parts_id) AND (a.id = ?)|;
1166     $sth = prepare_query($form, $dbh, $query);
1167
1168     foreach $item (@{ $form->{parts} }) {
1169       push(@assemblies, $item);
1170       do_statement($form, $sth, $query, conv_i($item->{id}));
1171
1172       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1173         $ref->{assemblyitem} = 1;
1174         push(@assemblies, $ref);
1175       }
1176       $sth->finish;
1177
1178       push(@assemblies, { id => $item->{id} });
1179
1180     }
1181
1182     # copy assemblies to $form->{parts}
1183     $form->{parts} = \@assemblies;
1184   }
1185
1186   $dbh->disconnect;
1187   $main::lxdebug->leave_sub();
1188 }
1189
1190 sub update_prices {
1191   $main::lxdebug->enter_sub();
1192
1193   my ($self, $myconfig, $form) = @_;
1194   my @where_values;
1195   my $where = '1 = 1';
1196   my $var;
1197
1198   my $group;
1199   my $limit;
1200
1201   my @where_values;
1202
1203   if ($item ne 'make') {
1204     foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) {
1205       my $column = $item;
1206       $column =~ s/.*\.//;
1207       next unless ($form->{$column});
1208       $where .= qq| AND $item ILIKE ?|;
1209       push(@where_values, '%' . $form->{$column} . '%');
1210     }
1211   }
1212
1213   # special case for description
1214   if ($form->{description}
1215       && !(   $form->{bought}  || $form->{sold} || $form->{onorder}
1216            || $form->{ordered} || $form->{rfq} || $form->{quoted})) {
1217     $where .= qq| AND (p.description ILIKE ?)|;
1218     push(@where_values, '%' . $form->{description} . '%');
1219   }
1220
1221   # special case for serialnumber
1222   if ($form->{l_serialnumber} && $form->{serialnumber}) {
1223     $where .= qq| AND serialnumber ILIKE ?|;
1224     push(@where_values, '%' . $form->{serialnumber} . '%');
1225   }
1226
1227
1228   # items which were never bought, sold or on an order
1229   if ($form->{itemstatus} eq 'orphaned') {
1230     $form->{onhand}  = $form->{short}   = 0;
1231     $form->{bought}  = $form->{sold}    = 0;
1232     $form->{onorder} = $form->{ordered} = 0;
1233     $form->{rfq}     = $form->{quoted}  = 0;
1234
1235     $form->{transdatefrom} = $form->{transdateto} = "";
1236
1237     $where .=
1238       qq| AND (p.onhand = 0)
1239           AND p.id NOT IN
1240             (
1241               SELECT DISTINCT parts_id FROM invoice
1242               UNION
1243               SELECT DISTINCT parts_id FROM assembly
1244               UNION
1245               SELECT DISTINCT parts_id FROM orderitems
1246             )|;
1247   }
1248
1249   if ($form->{itemstatus} eq 'active') {
1250     $where .= qq| AND p.obsolete = '0'|;
1251   }
1252
1253   if ($form->{itemstatus} eq 'obsolete') {
1254     $where .= qq| AND p.obsolete = '1'|;
1255     $form->{onhand} = $form->{short} = 0;
1256   }
1257
1258   if ($form->{itemstatus} eq 'onhand') {
1259     $where .= qq| AND p.onhand > 0|;
1260   }
1261
1262   if ($form->{itemstatus} eq 'short') {
1263     $where .= qq| AND p.onhand < p.rop|;
1264   }
1265
1266   foreach my $column (qw(make model)) {
1267     next unless ($form->{$colum});
1268     $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|;
1269     push(@where_values, '%' . $form->{$column} . '%');
1270   }
1271
1272   # connect to database
1273   my $dbh = $form->dbconnect_noauto($myconfig);
1274
1275   for my $column (qw(sellprice listprice)) {
1276     next if ($form->{$column} eq "");
1277
1278     my $value = $form->parse_amount($myconfig, $form->{$column});
1279     my $operator = '+';
1280
1281     if ($form->{"${column}_type"} eq "percent") {
1282       $value = ($value / 100) + 1;
1283       $operator = '*';
1284     }
1285
1286     $query =
1287       qq|UPDATE parts SET $column = $column $operator ?
1288          WHERE id IN
1289            (SELECT p.id
1290             FROM parts p
1291             LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1292             WHERE $where)|;
1293     do_query($from, $dbh, $query, $value, @where_values);
1294   }
1295
1296   my $q_add =
1297     qq|UPDATE prices SET price = price + ?
1298        WHERE parts_id IN
1299          (SELECT p.id
1300           FROM parts p
1301           LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1302           WHERE $where) AND (pricegroup_id = ?)|;
1303   my $sth_add = prepare_query($form, $dbh, $q_add);
1304
1305   my $q_multiply =
1306     qq|UPDATE prices SET price = price * ?
1307        WHERE parts_id IN
1308          (SELECT p.id
1309           FROM parts p
1310           LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1311           WHERE $where) AND (pricegroup_id = ?)|;
1312   my $sth_multiply = prepare_query($form, $dbh, $q_multiply);
1313
1314   for my $i (1 .. $form->{price_rows}) {
1315     next if ($form->{"price_$i"} eq "");
1316
1317     my $value = $form->parse_amount($myconfig, $form->{"price_$i"});
1318
1319     if ($form->{"pricegroup_type_$i"} eq "percent") {
1320       do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"}));
1321     } else {
1322       do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"}));
1323     }
1324   }
1325
1326   $sth_add->finish();
1327   $sth_multiply->finish();
1328
1329   my $rc= $dbh->commit;
1330   $dbh->disconnect;
1331
1332   $main::lxdebug->leave_sub();
1333
1334   return $rc;
1335 }
1336
1337 sub create_links {
1338   $main::lxdebug->enter_sub();
1339
1340   my ($self, $module, $myconfig, $form) = @_;
1341
1342   # connect to database
1343   my $dbh = $form->dbconnect($myconfig);
1344
1345   my @values = ('%' . $module . '%');
1346
1347   if ($form->{id}) {
1348     $query =
1349       qq|SELECT c.accno, c.description, c.link, c.id,
1350            p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1351          FROM chart c, parts p
1352          WHERE (c.link LIKE ?) AND (p.id = ?)
1353          ORDER BY c.accno|;
1354     push(@values, conv_i($form->{id}));
1355
1356   } else {
1357     $query =
1358       qq|SELECT c.accno, c.description, c.link, c.id,
1359            d.inventory_accno_id, d.income_accno_id, d.expense_accno_id
1360          FROM chart c, defaults d
1361          WHERE c.link LIKE ?
1362          ORDER BY c.accno|;
1363   }
1364
1365   my $sth = prepare_execute_query($form, $dbh, $query, @values);
1366   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1367     foreach my $key (split(/:/, $ref->{link})) {
1368       if ($key =~ /$module/) {
1369         if (   ($ref->{id} eq $ref->{inventory_accno_id})
1370             || ($ref->{id} eq $ref->{income_accno_id})
1371             || ($ref->{id} eq $ref->{expense_accno_id})) {
1372           push @{ $form->{"${module}_links"}{$key} },
1373             { accno       => $ref->{accno},
1374               description => $ref->{description},
1375               selected    => "selected" };
1376           $form->{"${key}_default"} = "$ref->{accno}--$ref->{description}";
1377             } else {
1378           push @{ $form->{"${module}_links"}{$key} },
1379             { accno       => $ref->{accno},
1380               description => $ref->{description},
1381               selected    => "" };
1382         }
1383       }
1384     }
1385   }
1386   $sth->finish;
1387
1388   # get buchungsgruppen
1389   $form->{BUCHUNGSGRUPPEN} = selectall_hashref_query($form, $dbh, qq|SELECT id, description FROM buchungsgruppen|);
1390
1391   # get payment terms
1392   $form->{payment_terms} = selectall_hashref_query($form, $dbh, qq|SELECT id, description FROM payment_terms ORDER BY sortkey|);
1393
1394   if (!$form->{id}) {
1395     ($form->{priceupdate}) = selectrow_query($form, $dbh, qq|SELECT current_date|);
1396   }
1397
1398   $dbh->disconnect;
1399   $main::lxdebug->leave_sub();
1400 }
1401
1402 # get partnumber, description, unit, sellprice and soldtotal with choice through $sortorder for Top100
1403 sub get_parts {
1404   $main::lxdebug->enter_sub();
1405
1406   my ($self, $myconfig, $form, $sortorder) = @_;
1407   my $dbh   = $form->dbconnect($myconfig);
1408   my $order = qq| p.partnumber|;
1409   my $where = qq|1 = 1|;
1410   my @values;
1411
1412   if ($sortorder eq "all") {
1413     $where .= qq| AND (partnumber ILIKE ?) AND (description ILIKE ?)|;
1414     push(@values, '%' . $form->{partnumber} . '%', '%' . $form->{description} . '%');
1415
1416   } elsif ($sortorder eq "partnumber") {
1417     $where .= qq| AND (partnumber ILIKE ?)|;
1418     push(@values, '%' . $form->{partnumber} . '%');
1419
1420   } elsif ($sortorder eq "description") {
1421     $where .= qq| AND (description ILIKE ?)|;
1422     push(@values, '%' . $form->{description} . '%');
1423     $order = "description";
1424
1425   }
1426
1427   my $query =
1428     qq|SELECT id, partnumber, description, unit, sellprice
1429        FROM parts
1430        WHERE $where ORDER BY $order|;
1431
1432   my $sth = prepare_execute_query($form, $dbh, $query, @values);
1433
1434   my $j = 0;
1435   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1436     if (($ref->{partnumber} eq "*") && ($ref->{description} eq "")) {
1437       next;
1438     }
1439
1440     $j++;
1441     $form->{"id_$j"}          = $ref->{id};
1442     $form->{"partnumber_$j"}  = $ref->{partnumber};
1443     $form->{"description_$j"} = $ref->{description};
1444     $form->{"unit_$j"}        = $ref->{unit};
1445     $form->{"sellprice_$j"}   = $ref->{sellprice};
1446     $form->{"soldtotal_$j"}   = get_soldtotal($dbh, $ref->{id});
1447   }    #while
1448   $form->{rows} = $j;
1449   $sth->finish;
1450   $dbh->disconnect;
1451
1452   $main::lxdebug->leave_sub();
1453
1454   return $self;
1455 }    #end get_parts()
1456
1457 # gets sum of sold part with part_id
1458 sub get_soldtotal {
1459   $main::lxdebug->enter_sub();
1460
1461   my ($dbh, $id) = @_;
1462
1463   my $query = qq|SELECT sum(qty) FROM invoice WHERE parts_id = ?|;
1464   my ($sum) = selectrow_query($form, $dbh, $query, conv_i($id));
1465   $sum ||= 0;
1466
1467   $main::lxdebug->leave_sub();
1468
1469   return $sum;
1470 }    #end get_soldtotal
1471
1472 sub retrieve_languages {
1473   $main::lxdebug->enter_sub();
1474
1475   my ($self, $myconfig, $form) = @_;
1476
1477   # connect to database
1478   my $dbh = $form->dbconnect($myconfig);
1479
1480   my @values;
1481   my $where;
1482
1483   if ($form->{language_values} ne "") {
1484     $query =
1485       qq|SELECT l.id, l.description, tr.translation, tr.longdescription
1486          FROM language l
1487          LEFT OUTER JOIN translation tr ON (tr.language_id = l.id) AND (tr.parts_id = ?)|;
1488     @values = (conv_i($form->{id}));
1489
1490   } else {
1491     $query = qq|SELECT id, description FROM language|;
1492   }
1493
1494   my $languages = selectall_hashref_query($form, $dbh, $query, @values);
1495
1496   $dbh->disconnect;
1497
1498   $main::lxdebug->leave_sub();
1499
1500   return $languages;
1501 }
1502
1503 sub follow_account_chain {
1504   $main::lxdebug->enter_sub(2);
1505
1506   my ($self, $form, $dbh, $transdate, $accno_id, $accno) = @_;
1507
1508   my @visited_accno_ids = ($accno_id);
1509
1510   my ($query, $sth);
1511
1512   $query =
1513     qq|SELECT c.new_chart_id, date($transdate) >= c.valid_from AS is_valid, | .
1514     qq|  cnew.accno | .
1515     qq|FROM chart c | .
1516     qq|LEFT JOIN chart cnew ON c.new_chart_id = cnew.id | .
1517     qq|WHERE (c.id = ?) AND NOT c.new_chart_id ISNULL AND (c.new_chart_id > 0)|;
1518   $sth = prepare_query($form, $dbh, $query);
1519
1520   while (1) {
1521     do_statement($form, $sth, $query, $accno_id);
1522     $ref = $sth->fetchrow_hashref();
1523     last unless ($ref && $ref->{"is_valid"} &&
1524                  !grep({ $_ == $ref->{"new_chart_id"} } @visited_accno_ids));
1525     $accno_id = $ref->{"new_chart_id"};
1526     $accno = $ref->{"accno"};
1527     push(@visited_accno_ids, $accno_id);
1528   }
1529
1530   $main::lxdebug->leave_sub(2);
1531
1532   return ($accno_id, $accno);
1533 }
1534
1535 sub retrieve_accounts {
1536   $main::lxdebug->enter_sub(2);
1537
1538   my ($self, $myconfig, $form, $parts_id, $index) = @_;
1539
1540   my ($query, $sth, $dbh);
1541
1542   $form->{"taxzone_id"} *= 1;
1543
1544   $dbh = $form->dbconnect($myconfig);
1545
1546   my $transdate = "";
1547   if ($form->{type} eq "invoice") {
1548     if (($form->{vc} eq "vendor") || !$form->{deliverydate}) {
1549       $transdate = $form->{invdate};
1550     } else {
1551       $transdate = $form->{deliverydate};
1552     }
1553   } elsif ($form->{type} eq "credit_note") {
1554     $transdate = $form->{invdate};
1555   } else {
1556     $transdate = $form->{transdate};
1557   }
1558
1559   if ($transdate eq "") {
1560     $transdate = "current_date";
1561   } else {
1562     $transdate = $dbh->quote($transdate);
1563   }
1564
1565   $query =
1566     qq|SELECT | .
1567     qq|  p.inventory_accno_id AS is_part, | .
1568     qq|  bg.inventory_accno_id, | .
1569     qq|  bg.income_accno_id_$form->{taxzone_id} AS income_accno_id, | .
1570     qq|  bg.expense_accno_id_$form->{taxzone_id} AS expense_accno_id, | .
1571     qq|  c1.accno AS inventory_accno, | .
1572     qq|  c2.accno AS income_accno, | .
1573     qq|  c3.accno AS expense_accno | .
1574     qq|FROM parts p | .
1575     qq|LEFT JOIN buchungsgruppen bg ON p.buchungsgruppen_id = bg.id | .
1576     qq|LEFT JOIN chart c1 ON bg.inventory_accno_id = c1.id | .
1577     qq|LEFT JOIN chart c2 ON bg.income_accno_id_$form->{taxzone_id} = c2.id | .
1578     qq|LEFT JOIN chart c3 ON bg.expense_accno_id_$form->{taxzone_id} = c3.id | .
1579     qq|WHERE p.id = ?|;
1580   my $ref = selectfirst_hashref_query($form, $dbh, $query, $parts_id);
1581
1582   if (!$ref) {
1583     $dbh->disconnect();
1584     return $main::lxdebug->leave_sub(2);
1585   }
1586
1587   $ref->{"inventory_accno_id"} = undef unless ($ref->{"is_part"});
1588
1589   my %accounts;
1590   foreach my $type (qw(inventory income expense)) {
1591     next unless ($ref->{"${type}_accno_id"});
1592     ($accounts{"${type}_accno_id"}, $accounts{"${type}_accno"}) =
1593       $self->follow_account_chain($form, $dbh, $transdate,
1594                                   $ref->{"${type}_accno_id"},
1595                                   $ref->{"${type}_accno"});
1596   }
1597
1598   map({ $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} }
1599       qw(inventory income expense));
1600
1601   my $inc_exp = $form->{"vc"} eq "customer" ? "income" : "expense";
1602   my $accno_id = $accounts{"${inc_exp}_accno_id"};
1603
1604   $query =
1605     qq|SELECT c.accno, t.taxdescription AS description, t.rate, t.taxnumber | .
1606     qq|FROM tax t | .
1607     qq|LEFT JOIN chart c ON c.id = t.chart_id | .
1608     qq|WHERE t.id IN | .
1609     qq|  (SELECT tk.tax_id | .
1610     qq|   FROM taxkeys tk | .
1611     qq|   WHERE tk.chart_id = ? AND startdate <= | . quote_db_date($transdate) .
1612     qq|   ORDER BY startdate DESC LIMIT 1) |;
1613   $ref = selectfirst_hashref_query($form, $dbh, $query, $accno_id);
1614
1615   unless ($ref) {
1616     $main::lxdebug->leave_sub(2);
1617     return;
1618   }
1619
1620   $form->{"taxaccounts_$index"} = $ref->{"accno"};
1621   if ($form->{"taxaccounts"} !~ /$ref->{accno}/) {
1622     $form->{"taxaccounts"} .= "$ref->{accno} ";
1623   }
1624   map({ $form->{"$ref->{accno}_${_}"} = $ref->{$_}; }
1625       qw(rate description taxnumber));
1626
1627 #   $main::lxdebug->message(0, "formvars: rate " . $form->{"$ref->{accno}_rate"} .
1628 #                           " description " . $form->{"$ref->{accno}_description"} .
1629 #                           " taxnumber " . $form->{"$ref->{accno}_taxnumber"} .
1630 #                           " || taxaccounts_$index " . $form->{"taxaccounts_$index"} .
1631 #                           " || taxaccounts " . $form->{"taxaccounts"});
1632
1633   $main::lxdebug->leave_sub(2);
1634 }
1635
1636 1;