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
1179     # copy assemblies to $form->{parts}
1180     $form->{parts} = \@assemblies;
1181   }
1182
1183   $dbh->disconnect;
1184   $main::lxdebug->leave_sub();
1185 }
1186
1187 sub update_prices {
1188   $main::lxdebug->enter_sub();
1189
1190   my ($self, $myconfig, $form) = @_;
1191   my @where_values;
1192   my $where = '1 = 1';
1193   my $var;
1194
1195   my $group;
1196   my $limit;
1197
1198   my @where_values;
1199
1200   if ($item ne 'make') {
1201     foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) {
1202       my $column = $item;
1203       $column =~ s/.*\.//;
1204       next unless ($form->{$column});
1205       $where .= qq| AND $item ILIKE ?|;
1206       push(@where_values, '%' . $form->{$column} . '%');
1207     }
1208   }
1209
1210   # special case for description
1211   if ($form->{description}
1212       && !(   $form->{bought}  || $form->{sold} || $form->{onorder}
1213            || $form->{ordered} || $form->{rfq} || $form->{quoted})) {
1214     $where .= qq| AND (p.description ILIKE ?)|;
1215     push(@where_values, '%' . $form->{description} . '%');
1216   }
1217
1218   # special case for serialnumber
1219   if ($form->{l_serialnumber} && $form->{serialnumber}) {
1220     $where .= qq| AND serialnumber ILIKE ?|;
1221     push(@where_values, '%' . $form->{serialnumber} . '%');
1222   }
1223
1224
1225   # items which were never bought, sold or on an order
1226   if ($form->{itemstatus} eq 'orphaned') {
1227     $form->{onhand}  = $form->{short}   = 0;
1228     $form->{bought}  = $form->{sold}    = 0;
1229     $form->{onorder} = $form->{ordered} = 0;
1230     $form->{rfq}     = $form->{quoted}  = 0;
1231
1232     $form->{transdatefrom} = $form->{transdateto} = "";
1233
1234     $where .=
1235       qq| AND (p.onhand = 0)
1236           AND p.id NOT IN
1237             (
1238               SELECT DISTINCT parts_id FROM invoice
1239               UNION
1240               SELECT DISTINCT parts_id FROM assembly
1241               UNION
1242               SELECT DISTINCT parts_id FROM orderitems
1243             )|;
1244   }
1245
1246   if ($form->{itemstatus} eq 'active') {
1247     $where .= qq| AND p.obsolete = '0'|;
1248   }
1249
1250   if ($form->{itemstatus} eq 'obsolete') {
1251     $where .= qq| AND p.obsolete = '1'|;
1252     $form->{onhand} = $form->{short} = 0;
1253   }
1254
1255   if ($form->{itemstatus} eq 'onhand') {
1256     $where .= qq| AND p.onhand > 0|;
1257   }
1258
1259   if ($form->{itemstatus} eq 'short') {
1260     $where .= qq| AND p.onhand < p.rop|;
1261   }
1262
1263   foreach my $column (qw(make model)) {
1264     next unless ($form->{$colum});
1265     $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|;
1266     push(@where_values, '%' . $form->{$column} . '%');
1267   }
1268
1269   # connect to database
1270   my $dbh = $form->dbconnect_noauto($myconfig);
1271
1272   for my $column (qw(sellprice listprice)) {
1273     next if ($form->{$column} eq "");
1274
1275     my $value = $form->parse_amount($myconfig, $form->{$column});
1276     my $operator = '+';
1277
1278     if ($form->{"${column}_type"} eq "percent") {
1279       $value = ($value / 100) + 1;
1280       $operator = '*';
1281     }
1282
1283     $query =
1284       qq|UPDATE parts SET $column = $column $operator ?
1285          WHERE id IN
1286            (SELECT p.id
1287             FROM parts p
1288             LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1289             WHERE $where)|;
1290     do_query($from, $dbh, $query, $value, @where_values);
1291   }
1292
1293   my $q_add =
1294     qq|UPDATE prices SET price = price + ?
1295        WHERE parts_id IN
1296          (SELECT p.id
1297           FROM parts p
1298           LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1299           WHERE $where) AND (pricegroup_id = ?)|;
1300   my $sth_add = prepare_query($form, $dbh, $q_add);
1301
1302   my $q_multiply =
1303     qq|UPDATE prices SET price = price * ?
1304        WHERE parts_id IN
1305          (SELECT p.id
1306           FROM parts p
1307           LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1308           WHERE $where) AND (pricegroup_id = ?)|;
1309   my $sth_multiply = prepare_query($form, $dbh, $q_multiply);
1310
1311   for my $i (1 .. $form->{price_rows}) {
1312     next if ($form->{"price_$i"} eq "");
1313
1314     my $value = $form->parse_amount($myconfig, $form->{"price_$i"});
1315
1316     if ($form->{"pricegroup_type_$i"} eq "percent") {
1317       do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"}));
1318     } else {
1319       do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"}));
1320     }
1321   }
1322
1323   $sth_add->finish();
1324   $sth_multiply->finish();
1325
1326   my $rc= $dbh->commit;
1327   $dbh->disconnect;
1328
1329   $main::lxdebug->leave_sub();
1330
1331   return $rc;
1332 }
1333
1334 sub create_links {
1335   $main::lxdebug->enter_sub();
1336
1337   my ($self, $module, $myconfig, $form) = @_;
1338
1339   # connect to database
1340   my $dbh = $form->dbconnect($myconfig);
1341
1342   my @values = ('%' . $module . '%');
1343
1344   if ($form->{id}) {
1345     $query =
1346       qq|SELECT c.accno, c.description, c.link, c.id,
1347            p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1348          FROM chart c, parts p
1349          WHERE (c.link LIKE ?) AND (p.id = ?)
1350          ORDER BY c.accno|;
1351     push(@values, conv_i($form->{id}));
1352
1353   } else {
1354     $query =
1355       qq|SELECT c.accno, c.description, c.link, c.id,
1356            d.inventory_accno_id, d.income_accno_id, d.expense_accno_id
1357          FROM chart c, defaults d
1358          WHERE c.link LIKE ?
1359          ORDER BY c.accno|;
1360   }
1361
1362   my $sth = prepare_execute_query($form, $dbh, $query, @values);
1363   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1364     foreach my $key (split(/:/, $ref->{link})) {
1365       if ($key =~ /$module/) {
1366         if (   ($ref->{id} eq $ref->{inventory_accno_id})
1367             || ($ref->{id} eq $ref->{income_accno_id})
1368             || ($ref->{id} eq $ref->{expense_accno_id})) {
1369           push @{ $form->{"${module}_links"}{$key} },
1370             { accno       => $ref->{accno},
1371               description => $ref->{description},
1372               selected    => "selected" };
1373           $form->{"${key}_default"} = "$ref->{accno}--$ref->{description}";
1374             } else {
1375           push @{ $form->{"${module}_links"}{$key} },
1376             { accno       => $ref->{accno},
1377               description => $ref->{description},
1378               selected    => "" };
1379         }
1380       }
1381     }
1382   }
1383   $sth->finish;
1384
1385   # get buchungsgruppen
1386   $form->{BUCHUNGSGRUPPEN} = selectall_hashref_query($form, $dbh, qq|SELECT id, description FROM buchungsgruppen|);
1387
1388   # get payment terms
1389   $form->{payment_terms} = selectall_hashref_query($form, $dbh, qq|SELECT id, description FROM payment_terms ORDER BY sortkey|);
1390
1391   if (!$form->{id}) {
1392     ($form->{priceupdate}) = selectrow_query($form, $dbh, qq|SELECT current_date|);
1393   }
1394
1395   $dbh->disconnect;
1396   $main::lxdebug->leave_sub();
1397 }
1398
1399 # get partnumber, description, unit, sellprice and soldtotal with choice through $sortorder for Top100
1400 sub get_parts {
1401   $main::lxdebug->enter_sub();
1402
1403   my ($self, $myconfig, $form, $sortorder) = @_;
1404   my $dbh   = $form->dbconnect($myconfig);
1405   my $order = qq| p.partnumber|;
1406   my $where = qq|1 = 1|;
1407   my @values;
1408
1409   if ($sortorder eq "all") {
1410     $where .= qq| AND (partnumber ILIKE ?) AND (description ILIKE ?)|;
1411     push(@values, '%' . $form->{partnumber} . '%', '%' . $form->{description} . '%');
1412
1413   } elsif ($sortorder eq "partnumber") {
1414     $where .= qq| AND (partnumber ILIKE ?)|;
1415     push(@values, '%' . $form->{partnumber} . '%');
1416
1417   } elsif ($sortorder eq "description") {
1418     $where .= qq| AND (description ILIKE ?)|;
1419     push(@values, '%' . $form->{description} . '%');
1420     $order = "description";
1421
1422   }
1423
1424   my $query =
1425     qq|SELECT id, partnumber, description, unit, sellprice
1426        FROM parts
1427        WHERE $where ORDER BY $order|;
1428
1429   my $sth = prepare_execute_query($form, $dbh, $query, @values);
1430
1431   my $j = 0;
1432   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1433     if (($ref->{partnumber} eq "*") && ($ref->{description} eq "")) {
1434       next;
1435     }
1436
1437     $j++;
1438     $form->{"id_$j"}          = $ref->{id};
1439     $form->{"partnumber_$j"}  = $ref->{partnumber};
1440     $form->{"description_$j"} = $ref->{description};
1441     $form->{"unit_$j"}        = $ref->{unit};
1442     $form->{"sellprice_$j"}   = $ref->{sellprice};
1443     $form->{"soldtotal_$j"}   = get_soldtotal($dbh, $ref->{id});
1444   }    #while
1445   $form->{rows} = $j;
1446   $sth->finish;
1447   $dbh->disconnect;
1448
1449   $main::lxdebug->leave_sub();
1450
1451   return $self;
1452 }    #end get_parts()
1453
1454 # gets sum of sold part with part_id
1455 sub get_soldtotal {
1456   $main::lxdebug->enter_sub();
1457
1458   my ($dbh, $id) = @_;
1459
1460   my $query = qq|SELECT sum(qty) FROM invoice WHERE parts_id = ?|;
1461   my ($sum) = selectrow_query($form, $dbh, $query, conv_i($id));
1462   $sum ||= 0;
1463
1464   $main::lxdebug->leave_sub();
1465
1466   return $sum;
1467 }    #end get_soldtotal
1468
1469 sub retrieve_languages {
1470   $main::lxdebug->enter_sub();
1471
1472   my ($self, $myconfig, $form) = @_;
1473
1474   # connect to database
1475   my $dbh = $form->dbconnect($myconfig);
1476
1477   my @values;
1478   my $where;
1479
1480   if ($form->{language_values} ne "") {
1481     $query =
1482       qq|SELECT l.id, l.description, tr.translation, tr.longdescription
1483          FROM language l
1484          LEFT OUTER JOIN translation tr ON (tr.language_id = l.id) AND (tr.parts_id = ?)|;
1485     @values = (conv_i($form->{id}));
1486
1487   } else {
1488     $query = qq|SELECT id, description FROM language|;
1489   }
1490
1491   my $languages = selectall_hashref_query($form, $dbh, $query, @values);
1492
1493   $dbh->disconnect;
1494
1495   $main::lxdebug->leave_sub();
1496
1497   return $languages;
1498 }
1499
1500 sub follow_account_chain {
1501   $main::lxdebug->enter_sub(2);
1502
1503   my ($self, $form, $dbh, $transdate, $accno_id, $accno) = @_;
1504
1505   my @visited_accno_ids = ($accno_id);
1506
1507   my ($query, $sth);
1508
1509   $query =
1510     qq|SELECT c.new_chart_id, date($transdate) >= c.valid_from AS is_valid, | .
1511     qq|  cnew.accno | .
1512     qq|FROM chart c | .
1513     qq|LEFT JOIN chart cnew ON c.new_chart_id = cnew.id | .
1514     qq|WHERE (c.id = ?) AND NOT c.new_chart_id ISNULL AND (c.new_chart_id > 0)|;
1515   $sth = prepare_query($form, $dbh, $query);
1516
1517   while (1) {
1518     do_statement($form, $sth, $query, $accno_id);
1519     $ref = $sth->fetchrow_hashref();
1520     last unless ($ref && $ref->{"is_valid"} &&
1521                  !grep({ $_ == $ref->{"new_chart_id"} } @visited_accno_ids));
1522     $accno_id = $ref->{"new_chart_id"};
1523     $accno = $ref->{"accno"};
1524     push(@visited_accno_ids, $accno_id);
1525   }
1526
1527   $main::lxdebug->leave_sub(2);
1528
1529   return ($accno_id, $accno);
1530 }
1531
1532 sub retrieve_accounts {
1533   $main::lxdebug->enter_sub(2);
1534
1535   my ($self, $myconfig, $form, $parts_id, $index) = @_;
1536
1537   my ($query, $sth, $dbh);
1538
1539   $form->{"taxzone_id"} *= 1;
1540
1541   $dbh = $form->get_standard_dbh($myconfig);
1542
1543   my $transdate = "";
1544   if ($form->{type} eq "invoice") {
1545     if (($form->{vc} eq "vendor") || !$form->{deliverydate}) {
1546       $transdate = $form->{invdate};
1547     } else {
1548       $transdate = $form->{deliverydate};
1549     }
1550   } elsif (($form->{type} eq "credit_note") || ($form->{script} eq 'ir.pl')) {
1551     $transdate = $form->{invdate};
1552   } else {
1553     $transdate = $form->{transdate};
1554   }
1555
1556   if ($transdate eq "") {
1557     $transdate = "current_date";
1558   } else {
1559     $transdate = $dbh->quote($transdate);
1560   }
1561
1562   $query =
1563     qq|SELECT | .
1564     qq|  p.inventory_accno_id AS is_part, | .
1565     qq|  bg.inventory_accno_id, | .
1566     qq|  bg.income_accno_id_$form->{taxzone_id} AS income_accno_id, | .
1567     qq|  bg.expense_accno_id_$form->{taxzone_id} AS expense_accno_id, | .
1568     qq|  c1.accno AS inventory_accno, | .
1569     qq|  c2.accno AS income_accno, | .
1570     qq|  c3.accno AS expense_accno | .
1571     qq|FROM parts p | .
1572     qq|LEFT JOIN buchungsgruppen bg ON p.buchungsgruppen_id = bg.id | .
1573     qq|LEFT JOIN chart c1 ON bg.inventory_accno_id = c1.id | .
1574     qq|LEFT JOIN chart c2 ON bg.income_accno_id_$form->{taxzone_id} = c2.id | .
1575     qq|LEFT JOIN chart c3 ON bg.expense_accno_id_$form->{taxzone_id} = c3.id | .
1576     qq|WHERE p.id = ?|;
1577   my $ref = selectfirst_hashref_query($form, $dbh, $query, $parts_id);
1578
1579   return $main::lxdebug->leave_sub(2) if (!$ref);
1580
1581   $ref->{"inventory_accno_id"} = undef unless ($ref->{"is_part"});
1582
1583   my %accounts;
1584   foreach my $type (qw(inventory income expense)) {
1585     next unless ($ref->{"${type}_accno_id"});
1586     ($accounts{"${type}_accno_id"}, $accounts{"${type}_accno"}) =
1587       $self->follow_account_chain($form, $dbh, $transdate,
1588                                   $ref->{"${type}_accno_id"},
1589                                   $ref->{"${type}_accno"});
1590   }
1591
1592   map({ $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} }
1593       qw(inventory income expense));
1594
1595   my $inc_exp = $form->{"vc"} eq "customer" ? "income" : "expense";
1596   my $accno_id = $accounts{"${inc_exp}_accno_id"};
1597
1598   $query =
1599     qq|SELECT c.accno, t.taxdescription AS description, t.rate, t.taxnumber | .
1600     qq|FROM tax t | .
1601     qq|LEFT JOIN chart c ON c.id = t.chart_id | .
1602     qq|WHERE t.id IN | .
1603     qq|  (SELECT tk.tax_id | .
1604     qq|   FROM taxkeys tk | .
1605     qq|   WHERE tk.chart_id = ? AND startdate <= | . quote_db_date($transdate) .
1606     qq|   ORDER BY startdate DESC LIMIT 1) |;
1607   $ref = selectfirst_hashref_query($form, $dbh, $query, $accno_id);
1608
1609   unless ($ref) {
1610     $main::lxdebug->leave_sub(2);
1611     return;
1612   }
1613
1614   $form->{"taxaccounts_$index"} = $ref->{"accno"};
1615   if ($form->{"taxaccounts"} !~ /$ref->{accno}/) {
1616     $form->{"taxaccounts"} .= "$ref->{accno} ";
1617   }
1618   map({ $form->{"$ref->{accno}_${_}"} = $ref->{$_}; }
1619       qw(rate description taxnumber));
1620
1621 #   $main::lxdebug->message(0, "formvars: rate " . $form->{"$ref->{accno}_rate"} .
1622 #                           " description " . $form->{"$ref->{accno}_description"} .
1623 #                           " taxnumber " . $form->{"$ref->{accno}_taxnumber"} .
1624 #                           " || taxaccounts_$index " . $form->{"taxaccounts_$index"} .
1625 #                           " || taxaccounts " . $form->{"taxaccounts"});
1626
1627   $main::lxdebug->leave_sub(2);
1628 }
1629
1630 1;