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 sub all_parts {
 786   $main::lxdebug->enter_sub();
 787
 788   my ($self, $myconfig, $form) = @_;
 789
 790   my $where = qq|1 = 1|;
 791   my (@values, $var, $flds, $group, $limit);
 792
 793   foreach my $item (qw(partnumber drawing microfiche ean pg.partsgroup)) {
 794     my $column = $item;
 795     $column =~ s/.*\.//;
 796     if ($form->{$column}) {
 797       $where .= qq| AND (${item} ILIKE ?)|;
 798       push(@values, '%' . $form->{$column} . '%');
 799     }
 800   }
 801
 802   # special case for description
 803   if ($form->{description}
 804       && !(   $form->{bought}  || $form->{sold} || $form->{onorder}
 805            || $form->{ordered} || $form->{rfq} || $form->{quoted})) {
 806     $where .= qq| AND (p.description ILIKE ?)|;
 807     push(@values, '%' . $form->{description} . '%');
 808   }
 809
 810   # special case for serialnumber
 811   if ($form->{l_serialnumber} && $form->{serialnumber}) {
 812     $where .= qq| AND (p.serialnumber ILIKE ?)|;
 813     push(@values, '%' . $form->{serialnumber} . '%');
 814   }
 815
 816   if ($form->{searchitems} eq 'part') {
 817     $where .= qq| AND (p.inventory_accno_id > 0) |;
 818   }
 819
 820   if ($form->{searchitems} eq 'assembly') {
 821     $form->{bought} = "";
 822     $where .= qq| AND p.assembly|;
 823   }
 824
 825   if ($form->{searchitems} eq 'service') {
 826     $where .= qq| AND (p.inventory_accno_id IS NULL) AND NOT (p.assembly = '1')|;
 827
 828     # irrelevant for services
 829     $form->{make} = $form->{model} = "";
 830   }
 831
 832   # items which were never bought, sold or on an order
 833   if ($form->{itemstatus} eq 'orphaned') {
 834     $form->{onhand}  = $form->{short}   = 0;
 835     $form->{bought}  = $form->{sold}    = 0;
 836     $form->{onorder} = $form->{ordered} = 0;
 837     $form->{rfq}     = $form->{quoted}  = 0;
 838
 839     $form->{transdatefrom} = $form->{transdateto} = "";
 840
 841     $where .=
 842       qq| AND (p.onhand = 0)
 843           AND p.id NOT IN
 844             (
 845               SELECT DISTINCT parts_id FROM invoice
 846               UNION
 847               SELECT DISTINCT parts_id FROM assembly
 848               UNION
 849               SELECT DISTINCT parts_id FROM orderitems
 850             )|;
 851   }
 852
 853   if ($form->{itemstatus} eq 'active') {
 854     $where .= qq| AND (p.obsolete = '0')|;
 855   } elsif ($form->{itemstatus} eq 'obsolete') {
 856     $where .= qq| AND (p.obsolete = '1')|;
 857     $form->{onhand} = $form->{short} = 0;
 858   } elsif ($form->{itemstatus} eq 'onhand') {
 859     $where .= qq| AND (p.onhand > 0)|;
 860   } elsif ($form->{itemstatus} eq 'short') {
 861     $where .= qq| AND (p.onhand < p.rop)|;
 862   }
 863
 864   my @subcolumns;
 865   foreach my $column (qw(make model)) {
 866     push @subcolumns, $column if $form->{$column};
 867   }
 868   if (@subcolumns) {
 869     $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE |;
 870     $where .= join " AND ", map { "($_ ILIKE ?)"; } @subcolumns;
 871     $where .= qq|)|;
 872     push @values, map { '%' . $form->{$_} . '%' } @subcolumns;
 873   }
 874
 875   if ($form->{l_soldtotal}) {
 876     $where .= qq| AND (p.id = i.parts_id) AND (i.qty >= 0)|;
 877     $group =
 878       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|;
 879   }
 880
 881   $limit = qq| LIMIT 100| if ($form->{top100});
 882
 883   # connect to database
 884   my $dbh = $form->dbconnect($myconfig);
 885
 886   my @sort_cols = qw(id partnumber description partsgroup bin priceupdate onhand
 887                      invnumber ordnumber quonumber name drawing microfiche
 888                      serialnumber soldtotal deliverydate);
 889
 890   my $sortorder = "partnumber";
 891   $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } @sort_cols));
 892   $sortorder .= " DESC" if ($form->{revers});
 893
 894   my $query = "";
 895
 896   if ($form->{l_soldtotal}) {
 897     $form->{soldtotal} = 'soldtotal';
 898     $query =
 899       qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, p.bin, p.sellprice, p.listprice,
 900            p.lastcost, p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal FROM parts
 901            p LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id), invoice i
 902            WHERE $where
 903            $group
 904            ORDER BY $sortorder
 905            $limit|;
 906   } else {
 907     $query =
 908       qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit,
 909            p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.weight,
 910            p.priceupdate, p.image, p.drawing, p.microfiche,
 911            pg.partsgroup
 912          FROM parts p
 913          LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
 914          WHERE $where
 915          $group
 916          ORDER BY $sortorder
 917          $limit|;
 918   }
 919
 920   my @all_values = @values;
 921
 922   # rebuild query for bought and sold items
 923   if (   $form->{bought}
 924       || $form->{sold}
 925       || $form->{onorder}
 926       || $form->{ordered}
 927       || $form->{rfq}
 928       || $form->{quoted}) {
 929     my $union = "";
 930     $query = "";
 931     @all_values = ();
 932
 933     if ($form->{bought} || $form->{sold}) {
 934
 935       my @invvalues = @values;
 936       my $invwhere = "$where";
 937       $invwhere .= qq| AND i.assemblyitem = '0'|;
 938
 939       if ($form->{transdatefrom}) {
 940         $invwhere .= qq| AND a.transdate >= ?|;
 941         push(@invvalues, $form->{transdatefrom});
 942       }
 943
 944       if ($form->{transdateto}) {
 945         $invwhere .= qq| AND a.transdate <= ?|;
 946         push(@invvalues, $form->{transdateto});
 947       }
 948
 949       if ($form->{description}) {
 950         $invwhere .= qq| AND i.description ILIKE ?|;
 951         push(@invvalues, '%' . $form->{description} . '%');
 952       }
 953
 954       $flds =
 955         qq|p.id, p.partnumber, i.description, i.serialnumber,
 956            i.qty AS onhand, i.unit, p.bin, i.sellprice,
 957            p.listprice, p.lastcost, p.rop, p.weight,
 958            p.priceupdate, p.image, p.drawing, p.microfiche,
 959            pg.partsgroup,
 960            a.invnumber, a.ordnumber, a.quonumber, i.trans_id,
 961            ct.name, i.deliverydate|;
 962
 963       if ($form->{bought}) {
 964         $query =
 965           qq|SELECT $flds, 'ir' AS module, '' AS type, 1 AS exchangerate
 966              FROM invoice i
 967              JOIN parts p ON (p.id = i.parts_id)
 968              JOIN ap a ON (a.id = i.trans_id)
 969              JOIN vendor ct ON (a.vendor_id = ct.id)
 970              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
 971              WHERE $invwhere|;
 972
 973         $union = qq| UNION |;
 974
 975         push(@all_values, @invvalues);
 976       }
 977
 978       if ($form->{sold}) {
 979         $query .=
 980           qq|$union
 981
 982              SELECT $flds, 'is' AS module, '' AS type, 1 As exchangerate
 983              FROM invoice i
 984              JOIN parts p ON (p.id = i.parts_id)
 985              JOIN ar a ON (a.id = i.trans_id)
 986              JOIN customer ct ON (a.customer_id = ct.id)
 987              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
 988              WHERE $invwhere|;
 989         $union = qq| UNION |;
 990
 991         push(@all_values, @invvalues);
 992       }
 993     }
 994
 995     if ($form->{onorder} || $form->{ordered}) {
 996       my @ordvalues = @values;
 997       my $ordwhere = $where . qq| AND o.quotation = '0'|;
 998
 999       if ($form->{transdatefrom}) {
1000         $ordwhere .= qq| AND o.transdate >= ?|;
1001         push(@ordvalues, $form->{transdatefrom});
1002       }
1003
1004       if ($form->{transdateto}) {
1005         $ordwhere .= qq| AND o.transdate <= ?|;
1006         push(@ordvalues, $form->{transdateto});
1007       }
1008
1009       if ($form->{description}) {
1010         $ordwhere .= qq| AND oi.description ILIKE ?|;
1011         push(@ordvalues, '%' . $form->{description} . '%');
1012       }
1013
1014       if ($form->{ordered}) {
1015         $query .=
1016           qq|$union
1017
1018              SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
1019                oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
1020                p.listprice, p.lastcost, p.rop, p.weight,
1021                p.priceupdate, p.image, p.drawing, p.microfiche,
1022                pg.partsgroup,
1023                '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
1024                ct.name, NULL AS deliverydate,
1025                'oe' AS module, 'sales_order' AS type,
1026                (SELECT buy FROM exchangerate ex
1027                 WHERE ex.curr = o.curr AND ex.transdate = o.transdate) AS exchangerate
1028              FROM orderitems oi
1029              JOIN parts p ON (oi.parts_id = p.id)
1030              JOIN oe o ON (oi.trans_id = o.id)
1031              JOIN customer ct ON (o.customer_id = ct.id)
1032              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1033              WHERE $ordwhere AND (o.customer_id > 0)|;
1034         $union = qq| UNION |;
1035
1036         push(@all_values, @ordvalues);
1037       }
1038
1039       if ($form->{onorder}) {
1040         $query .=
1041           qq|$union
1042
1043              SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
1044                oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
1045                p.listprice, p.lastcost, p.rop, p.weight,
1046                p.priceupdate, p.image, p.drawing, p.microfiche,
1047                pg.partsgroup,
1048                '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
1049                ct.name, NULL AS deliverydate,
1050                'oe' AS module, 'purchase_order' AS type,
1051                (SELECT sell FROM exchangerate ex
1052                WHERE ex.curr = o.curr AND (ex.transdate = o.transdate)) AS exchangerate
1053              FROM orderitems oi
1054              JOIN parts p ON (oi.parts_id = p.id)
1055              JOIN oe o ON (oi.trans_id = o.id)
1056              JOIN vendor ct ON (o.vendor_id = ct.id)
1057              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1058              WHERE $ordwhere AND (o.vendor_id > 0)|;
1059         $union = qq| UNION |;
1060
1061         push(@all_values, @ordvalues);
1062       }
1063
1064     }
1065
1066     if ($form->{rfq} || $form->{quoted}) {
1067       my $quowhere = $where . qq| AND o.quotation = '1'|;
1068       my @quovalues = @values;
1069
1070       if ($form->{transdatefrom}) {
1071         $quowhere .= qq| AND o.transdate >= ?|;
1072         push(@quovalues, $form->{transdatefrom});
1073       }
1074
1075       if ($form->{transdateto}) {
1076         $quowhere .= qq| AND o.transdate <= ?|;
1077         push(@quovalues, $form->{transdateto});
1078       }
1079
1080       if ($form->{description}) {
1081         $quowhere .= qq| AND oi.description ILIKE ?|;
1082         push(@quovalues, '%' . $form->{description} . '%');
1083       }
1084
1085       if ($form->{quoted}) {
1086         $query .=
1087           qq|$union
1088
1089              SELECT
1090                p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
1091                oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
1092                p.listprice, p.lastcost, p.rop, p.weight,
1093                p.priceupdate, p.image, p.drawing, p.microfiche,
1094                pg.partsgroup,
1095                '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
1096                ct.name, NULL AS deliverydate, 'oe' AS module, 'sales_quotation' AS type,
1097                (SELECT buy FROM exchangerate ex
1098                 WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate
1099              FROM orderitems oi
1100              JOIN parts p ON (oi.parts_id = p.id)
1101              JOIN oe o ON (oi.trans_id = o.id)
1102              JOIN customer ct ON (o.customer_id = ct.id)
1103              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1104              WHERE $quowhere
1105              AND o.customer_id > 0|;
1106         $union = qq| UNION |;
1107
1108         push(@all_values, @quovalues);
1109       }
1110
1111       if ($form->{rfq}) {
1112         $query .=
1113           qq|$union
1114
1115              SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
1116                oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
1117                p.listprice, p.lastcost, p.rop, p.weight,
1118                p.priceupdate, p.image, p.drawing, p.microfiche,
1119                pg.partsgroup,
1120                '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
1121                ct.name, NULL AS deliverydate,
1122                'oe' AS module, 'request_quotation' AS type,
1123                (SELECT sell FROM exchangerate ex
1124                WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate
1125              FROM orderitems oi
1126              JOIN parts p ON (oi.parts_id = p.id)
1127              JOIN oe o ON (oi.trans_id = o.id)
1128              JOIN vendor ct ON (o.vendor_id = ct.id)
1129              LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1130              WHERE $quowhere
1131              AND o.vendor_id > 0|;
1132
1133         push(@all_values, @quovalues);
1134       }
1135
1136     }
1137     $query .= qq| ORDER BY  | . $sortorder;
1138
1139   }
1140
1141   $form->{parts} = selectall_hashref_query($form, $dbh, $query, @all_values);
1142
1143   my @assemblies;
1144   # include individual items for assemblies
1145   if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
1146     $query =
1147       qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,
1148            p.unit, p.bin,
1149            p.sellprice, p.listprice, p.lastcost,
1150            p.rop, p.weight, p.priceupdate,
1151            p.image, p.drawing, p.microfiche
1152          FROM parts p, assembly a
1153          WHERE (p.id = a.parts_id) AND (a.id = ?)|;
1154     $sth = prepare_query($form, $dbh, $query);
1155
1156     foreach $item (@{ $form->{parts} }) {
1157       push(@assemblies, $item);
1158       do_statement($form, $sth, $query, conv_i($item->{id}));
1159
1160       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1161         $ref->{assemblyitem} = 1;
1162         push(@assemblies, $ref);
1163       }
1164       $sth->finish;
1165
1166       push(@assemblies, { id => $item->{id} });
1167
1168     }
1169
1170     # copy assemblies to $form->{parts}
1171     $form->{parts} = \@assemblies;
1172   }
1173
1174   $dbh->disconnect;
1175   $main::lxdebug->leave_sub();
1176 }
1177
1178 sub update_prices {
1179   $main::lxdebug->enter_sub();
1180
1181   my ($self, $myconfig, $form) = @_;
1182   my @where_values;
1183   my $where = '1 = 1';
1184   my $var;
1185
1186   my $group;
1187   my $limit;
1188
1189   my @where_values;
1190
1191   if ($item ne 'make') {
1192     foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) {
1193       my $column = $item;
1194       $column =~ s/.*\.//;
1195       next unless ($form->{$column});
1196       $where .= qq| AND $item ILIKE ?|;
1197       push(@where_values, '%' . $form->{$column} . '%');
1198     }
1199   }
1200
1201   # special case for description
1202   if ($form->{description}
1203       && !(   $form->{bought}  || $form->{sold} || $form->{onorder}
1204            || $form->{ordered} || $form->{rfq} || $form->{quoted})) {
1205     $where .= qq| AND (p.description ILIKE ?)|;
1206     push(@where_values, '%' . $form->{description} . '%');
1207   }
1208
1209   # special case for serialnumber
1210   if ($form->{l_serialnumber} && $form->{serialnumber}) {
1211     $where .= qq| AND serialnumber ILIKE ?|;
1212     push(@where_values, '%' . $form->{serialnumber} . '%');
1213   }
1214
1215
1216   # items which were never bought, sold or on an order
1217   if ($form->{itemstatus} eq 'orphaned') {
1218     $form->{onhand}  = $form->{short}   = 0;
1219     $form->{bought}  = $form->{sold}    = 0;
1220     $form->{onorder} = $form->{ordered} = 0;
1221     $form->{rfq}     = $form->{quoted}  = 0;
1222
1223     $form->{transdatefrom} = $form->{transdateto} = "";
1224
1225     $where .=
1226       qq| AND (p.onhand = 0)
1227           AND p.id NOT IN
1228             (
1229               SELECT DISTINCT parts_id FROM invoice
1230               UNION
1231               SELECT DISTINCT parts_id FROM assembly
1232               UNION
1233               SELECT DISTINCT parts_id FROM orderitems
1234             )|;
1235   }
1236
1237   if ($form->{itemstatus} eq 'active') {
1238     $where .= qq| AND p.obsolete = '0'|;
1239   }
1240
1241   if ($form->{itemstatus} eq 'obsolete') {
1242     $where .= qq| AND p.obsolete = '1'|;
1243     $form->{onhand} = $form->{short} = 0;
1244   }
1245
1246   if ($form->{itemstatus} eq 'onhand') {
1247     $where .= qq| AND p.onhand > 0|;
1248   }
1249
1250   if ($form->{itemstatus} eq 'short') {
1251     $where .= qq| AND p.onhand < p.rop|;
1252   }
1253
1254   foreach my $column (qw(make model)) {
1255     next unless ($form->{$colum});
1256     $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|;
1257     push(@where_values, '%' . $form->{$column} . '%');
1258   }
1259
1260   # connect to database
1261   my $dbh = $form->dbconnect_noauto($myconfig);
1262
1263   for my $column (qw(sellprice listprice)) {
1264     next if ($form->{$column} eq "");
1265
1266     my $value = $form->parse_amount($myconfig, $form->{$column});
1267     my $operator = '+';
1268
1269     if ($form->{"${column}_type"} eq "percent") {
1270       $value = ($value / 100) + 1;
1271       $operator = '*';
1272     }
1273
1274     $query =
1275       qq|UPDATE parts SET $column = $column $operator ?
1276          WHERE id IN
1277            (SELECT p.id
1278             FROM parts p
1279             LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1280             WHERE $where)|;
1281     do_query($from, $dbh, $query, $value, @where_values);
1282   }
1283
1284   my $q_add =
1285     qq|UPDATE prices SET price = price + ?
1286        WHERE parts_id IN
1287          (SELECT p.id
1288           FROM parts p
1289           LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1290           WHERE $where) AND (pricegroup_id = ?)|;
1291   my $sth_add = prepare_query($form, $dbh, $q_add);
1292
1293   my $q_multiply =
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_multiply = prepare_query($form, $dbh, $q_multiply);
1301
1302   for my $i (1 .. $form->{price_rows}) {
1303     next if ($form->{"price_$i"} eq "");
1304
1305     my $value = $form->parse_amount($myconfig, $form->{"price_$i"});
1306
1307     if ($form->{"pricegroup_type_$i"} eq "percent") {
1308       do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"}));
1309     } else {
1310       do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"}));
1311     }
1312   }
1313
1314   $sth_add->finish();
1315   $sth_multiply->finish();
1316
1317   my $rc= $dbh->commit;
1318   $dbh->disconnect;
1319
1320   $main::lxdebug->leave_sub();
1321
1322   return $rc;
1323 }
1324
1325 sub create_links {
1326   $main::lxdebug->enter_sub();
1327
1328   my ($self, $module, $myconfig, $form) = @_;
1329
1330   # connect to database
1331   my $dbh = $form->dbconnect($myconfig);
1332
1333   my @values = ('%' . $module . '%');
1334
1335   if ($form->{id}) {
1336     $query =
1337       qq|SELECT c.accno, c.description, c.link, c.id,
1338            p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1339          FROM chart c, parts p
1340          WHERE (c.link LIKE ?) AND (p.id = ?)
1341          ORDER BY c.accno|;
1342     push(@values, conv_i($form->{id}));
1343
1344   } else {
1345     $query =
1346       qq|SELECT c.accno, c.description, c.link, c.id,
1347            d.inventory_accno_id, d.income_accno_id, d.expense_accno_id
1348          FROM chart c, defaults d
1349          WHERE c.link LIKE ?
1350          ORDER BY c.accno|;
1351   }
1352
1353   my $sth = prepare_execute_query($form, $dbh, $query, @values);
1354   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1355     foreach my $key (split(/:/, $ref->{link})) {
1356       if ($key =~ /$module/) {
1357         if (   ($ref->{id} eq $ref->{inventory_accno_id})
1358             || ($ref->{id} eq $ref->{income_accno_id})
1359             || ($ref->{id} eq $ref->{expense_accno_id})) {
1360           push @{ $form->{"${module}_links"}{$key} },
1361             { accno       => $ref->{accno},
1362               description => $ref->{description},
1363               selected    => "selected" };
1364           $form->{"${key}_default"} = "$ref->{accno}--$ref->{description}";
1365             } else {
1366           push @{ $form->{"${module}_links"}{$key} },
1367             { accno       => $ref->{accno},
1368               description => $ref->{description},
1369               selected    => "" };
1370         }
1371       }
1372     }
1373   }
1374   $sth->finish;
1375
1376   # get buchungsgruppen
1377   $form->{BUCHUNGSGRUPPEN} = selectall_hashref_query($form, $dbh, qq|SELECT id, description FROM buchungsgruppen|);
1378
1379   # get payment terms
1380   $form->{payment_terms} = selectall_hashref_query($form, $dbh, qq|SELECT id, description FROM payment_terms ORDER BY sortkey|);
1381
1382   if (!$form->{id}) {
1383     ($form->{priceupdate}) = selectrow_query($form, $dbh, qq|SELECT current_date|);
1384   }
1385
1386   $dbh->disconnect;
1387   $main::lxdebug->leave_sub();
1388 }
1389
1390 # get partnumber, description, unit, sellprice and soldtotal with choice through $sortorder for Top100
1391 sub get_parts {
1392   $main::lxdebug->enter_sub();
1393
1394   my ($self, $myconfig, $form, $sortorder) = @_;
1395   my $dbh   = $form->dbconnect($myconfig);
1396   my $order = qq| p.partnumber|;
1397   my $where = qq|1 = 1|;
1398   my @values;
1399
1400   if ($sortorder eq "all") {
1401     $where .= qq| AND (partnumber ILIKE ?) AND (description ILIKE ?)|;
1402     push(@values, '%' . $form->{partnumber} . '%', '%' . $form->{description} . '%');
1403
1404   } elsif ($sortorder eq "partnumber") {
1405     $where .= qq| AND (partnumber ILIKE ?)|;
1406     push(@values, '%' . $form->{partnumber} . '%');
1407
1408   } elsif ($sortorder eq "description") {
1409     $where .= qq| AND (description ILIKE ?)|;
1410     push(@values, '%' . $form->{description} . '%');
1411     $order = "description";
1412
1413   }
1414
1415   my $query =
1416     qq|SELECT id, partnumber, description, unit, sellprice
1417        FROM parts
1418        WHERE $where ORDER BY $order|;
1419
1420   my $sth = prepare_execute_query($form, $dbh, $query, @values);
1421
1422   my $j = 0;
1423   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1424     if (($ref->{partnumber} eq "*") && ($ref->{description} eq "")) {
1425       next;
1426     }
1427
1428     $j++;
1429     $form->{"id_$j"}          = $ref->{id};
1430     $form->{"partnumber_$j"}  = $ref->{partnumber};
1431     $form->{"description_$j"} = $ref->{description};
1432     $form->{"unit_$j"}        = $ref->{unit};
1433     $form->{"sellprice_$j"}   = $ref->{sellprice};
1434     $form->{"soldtotal_$j"}   = get_soldtotal($dbh, $ref->{id});
1435   }    #while
1436   $form->{rows} = $j;
1437   $sth->finish;
1438   $dbh->disconnect;
1439
1440   $main::lxdebug->leave_sub();
1441
1442   return $self;
1443 }    #end get_parts()
1444
1445 # gets sum of sold part with part_id
1446 sub get_soldtotal {
1447   $main::lxdebug->enter_sub();
1448
1449   my ($dbh, $id) = @_;
1450
1451   my $query = qq|SELECT sum(qty) FROM invoice WHERE parts_id = ?|;
1452   my ($sum) = selectrow_query($form, $dbh, $query, conv_i($id));
1453   $sum ||= 0;
1454
1455   $main::lxdebug->leave_sub();
1456
1457   return $sum;
1458 }    #end get_soldtotal
1459
1460 sub retrieve_languages {
1461   $main::lxdebug->enter_sub();
1462
1463   my ($self, $myconfig, $form) = @_;
1464
1465   # connect to database
1466   my $dbh = $form->dbconnect($myconfig);
1467
1468   my @values;
1469   my $where;
1470
1471   if ($form->{language_values} ne "") {
1472     $query =
1473       qq|SELECT l.id, l.description, tr.translation, tr.longdescription
1474          FROM language l
1475          LEFT OUTER JOIN translation tr ON (tr.language_id = l.id) AND (tr.parts_id = ?)|;
1476     @values = (conv_i($form->{id}));
1477
1478   } else {
1479     $query = qq|SELECT id, description FROM language|;
1480   }
1481
1482   my $languages = selectall_hashref_query($form, $dbh, $query, @values);
1483
1484   $dbh->disconnect;
1485
1486   $main::lxdebug->leave_sub();
1487
1488   return $languages;
1489 }
1490
1491 sub follow_account_chain {
1492   $main::lxdebug->enter_sub(2);
1493
1494   my ($self, $form, $dbh, $transdate, $accno_id, $accno) = @_;
1495
1496   my @visited_accno_ids = ($accno_id);
1497
1498   my ($query, $sth);
1499
1500   $query =
1501     qq|SELECT c.new_chart_id, date($transdate) >= c.valid_from AS is_valid, | .
1502     qq|  cnew.accno | .
1503     qq|FROM chart c | .
1504     qq|LEFT JOIN chart cnew ON c.new_chart_id = cnew.id | .
1505     qq|WHERE (c.id = ?) AND NOT c.new_chart_id ISNULL AND (c.new_chart_id > 0)|;
1506   $sth = prepare_query($form, $dbh, $query);
1507
1508   while (1) {
1509     do_statement($form, $sth, $query, $accno_id);
1510     $ref = $sth->fetchrow_hashref();
1511     last unless ($ref && $ref->{"is_valid"} &&
1512                  !grep({ $_ == $ref->{"new_chart_id"} } @visited_accno_ids));
1513     $accno_id = $ref->{"new_chart_id"};
1514     $accno = $ref->{"accno"};
1515     push(@visited_accno_ids, $accno_id);
1516   }
1517
1518   $main::lxdebug->leave_sub(2);
1519
1520   return ($accno_id, $accno);
1521 }
1522
1523 sub retrieve_accounts {
1524   $main::lxdebug->enter_sub(2);
1525
1526   my ($self, $myconfig, $form, $parts_id, $index, $copy_accnos) = @_;
1527
1528   my ($query, $sth, $dbh);
1529
1530   $form->{"taxzone_id"} *= 1;
1531
1532   $dbh = $form->dbconnect($myconfig);
1533
1534   my $transdate = "";
1535   if ($form->{type} eq "invoice") {
1536     if (($form->{vc} eq "vendor") || !$form->{deliverydate}) {
1537       $transdate = $form->{invdate};
1538     } else {
1539       $transdate = $form->{deliverydate};
1540     }
1541   } elsif ($form->{type} eq "credit_note") {
1542     $transdate = $form->{invdate};
1543   } else {
1544     $transdate = $form->{transdate};
1545   }
1546
1547   if ($transdate eq "") {
1548     $transdate = "current_date";
1549   } else {
1550     $transdate = $dbh->quote($transdate);
1551   }
1552
1553   $query =
1554     qq|SELECT | .
1555     qq|  p.inventory_accno_id AS is_part, | .
1556     qq|  bg.inventory_accno_id, | .
1557     qq|  bg.income_accno_id_$form->{taxzone_id} AS income_accno_id, | .
1558     qq|  bg.expense_accno_id_$form->{taxzone_id} AS expense_accno_id, | .
1559     qq|  c1.accno AS inventory_accno, | .
1560     qq|  c2.accno AS income_accno, | .
1561     qq|  c3.accno AS expense_accno | .
1562     qq|FROM parts p | .
1563     qq|LEFT JOIN buchungsgruppen bg ON p.buchungsgruppen_id = bg.id | .
1564     qq|LEFT JOIN chart c1 ON bg.inventory_accno_id = c1.id | .
1565     qq|LEFT JOIN chart c2 ON bg.income_accno_id_$form->{taxzone_id} = c2.id | .
1566     qq|LEFT JOIN chart c3 ON bg.expense_accno_id_$form->{taxzone_id} = c3.id | .
1567     qq|WHERE p.id = ?|;
1568   my $ref = selectfirst_hashref_query($form, $dbh, $query, $parts_id);
1569
1570   if (!$ref) {
1571     $dbh->disconnect();
1572     return $main::lxdebug->leave_sub(2);
1573   }
1574
1575   $ref->{"inventory_accno_id"} = undef unless ($ref->{"is_part"});
1576
1577   my %accounts;
1578   foreach my $type (qw(inventory income expense)) {
1579     next unless ($ref->{"${type}_accno_id"});
1580     ($accounts{"${type}_accno_id"}, $accounts{"${type}_accno"}) =
1581       $self->follow_account_chain($form, $dbh, $transdate,
1582                                   $ref->{"${type}_accno_id"},
1583                                   $ref->{"${type}_accno"});
1584   }
1585
1586   map({ $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} }
1587       qw(inventory income expense));
1588
1589   my $inc_exp = $form->{"vc"} eq "customer" ? "income" : "expense";
1590   my $accno_id = $accounts{"${inc_exp}_accno_id"};
1591
1592   $query =
1593     qq|SELECT c.accno, t.taxdescription AS description, t.rate, t.taxnumber | .
1594     qq|FROM tax t | .
1595     qq|LEFT JOIN chart c ON c.id = t.chart_id | .
1596     qq|WHERE t.id IN | .
1597     qq|  (SELECT tk.tax_id | .
1598     qq|   FROM taxkeys tk | .
1599     qq|   WHERE tk.chart_id = ? AND startdate <= | . quote_db_date($transdate) .
1600     qq|   ORDER BY startdate DESC LIMIT 1) |;
1601   $ref = selectfirst_hashref_query($form, $dbh, $query, $accno_id);
1602
1603   unless ($ref) {
1604     $main::lxdebug->leave_sub(2);
1605     return;
1606   }
1607
1608   $form->{"taxaccounts_$index"} = $ref->{"accno"};
1609   if ($form->{"taxaccounts"} !~ /$ref->{accno}/) {
1610     $form->{"taxaccounts"} .= "$ref->{accno} ";
1611   }
1612   map({ $form->{"$ref->{accno}_${_}"} = $ref->{$_}; }
1613       qw(rate description taxnumber));
1614
1615 #   $main::lxdebug->message(0, "formvars: rate " . $form->{"$ref->{accno}_rate"} .
1616 #                           " description " . $form->{"$ref->{accno}_description"} .
1617 #                           " taxnumber " . $form->{"$ref->{accno}_taxnumber"} .
1618 #                           " || taxaccounts_$index " . $form->{"taxaccounts_$index"} .
1619 #                           " || taxaccounts " . $form->{"taxaccounts"});
1620
1621   $main::lxdebug->leave_sub(2);
1622 }
1623
1624 1;