1#===================================================================== 2# SQL-Ledger ERP 3# Copyright (C) 2006 4# 5# Author: DWS Systems Inc. 6# Web: http://www.sql-ledger.com 7# 8#====================================================================== 9# 10# backend code for human resources and payroll 11# 12#====================================================================== 13 14package HR; 15 16 17sub get_employee { 18 my ($self, $myconfig, $form, $nolock) = @_; 19 20 my $dbh = $form->dbconnect($myconfig); 21 22 $form->remove_locks($myconfig, $dbh, 'hr') unless $nolock; 23 24 my $query; 25 my $sth; 26 my $ref; 27 my $notid = ""; 28 my $rne; 29 30 my @df = qw(closedto revtrans company address tel fax businessnumber precision referenceurl lock_%); 31 my %defaults = $form->get_defaults($dbh, \@df); 32 for (keys %defaults) { $form->{$_} = $defaults{$_} } 33 34 %defaults = $form->get_defaults($dbh, \@{['printer_%']}); 35 36 my $label; 37 my $command; 38 for (keys %defaults) { 39 if ($_ =~ /printer_/) { 40 ($label, $command) = split /=/, $defaults{$_}; 41 $form->{"${label}_printer"} = $command; 42 } 43 } 44 45 if ($form->{id} *= 1) { 46 $query = qq|SELECT e.*, 47 ad.id AS addressid, ad.address1, ad.address2, ad.city, 48 ad.state, ad.zipcode, ad.country, 49 bk.name AS bankname, bk.iban, bk.bic, 50 bk.membernumber, bk.clearingnumber, 51 ad1.address1 AS bankaddress1, 52 ad1.address2 AS bankaddress2, 53 ad1.city AS bankcity, 54 ad1.state AS bankstate, 55 ad1.zipcode AS bankzipcode, 56 ad1.country AS bankcountry, 57 c1.accno AS ap, c1.description AS ap_description, 58 tr1.description AS ap_translation, 59 c2.accno AS payment, c2.description AS payment_description, 60 tr2.description AS payment_translation, 61 pm.description AS paymentmethod, 62 r.description AS acsrole 63 FROM employee e 64 JOIN address ad ON (e.id = ad.trans_id) 65 LEFT JOIN acsrole r ON (r.id = e.acsrole_id) 66 LEFT JOIN bank bk ON (bk.id = e.id) 67 LEFT JOIN address ad1 ON (bk.address_id = ad1.id) 68 LEFT JOIN chart c1 ON (c1.id = e.apid) 69 LEFT JOIN chart c2 ON (c2.id = e.paymentid) 70 LEFT JOIN translation tr1 ON (tr1.trans_id = c1.id AND tr1.language_code = '$myconfig->{countrycode}') 71 LEFT JOIN translation tr2 ON (tr2.trans_id = c2.id AND tr2.language_code = '$myconfig->{countrycode}') 72 LEFT JOIN paymentmethod pm ON (pm.id = e.paymentmethod_id) 73 WHERE e.id = $form->{id}|; 74 $sth = $dbh->prepare($query); 75 $sth->execute || $form->dberror($query); 76 77 $ref = $sth->fetchrow_hashref(NAME_lc); 78 $ref->{employeelogin} = $ref->{login}; 79 for (qw(ap payment)) { $ref->{"${_}_description"} = $ref->{"${_}_translation"} if $ref->{"${_}_translation"} } 80 for (qw(login ap_translation payment_translation)) { delete $ref->{$_} } 81 for (keys %$ref) { $form->{$_} = $ref->{$_} } 82 83 $sth->finish; 84 85 # check if employee can be deleted, orphaned 86 $form->{status} = ""; 87 $query = qq|SELECT count(*) FROM ap 88 WHERE vendor_id = $form->{id}|; 89 if (! $dbh->selectrow_array($query)) { 90 $form->{status} = "orphaned"; 91 } 92 93 if (! $form->{status}) { 94 $query = qq|SELECT count(*) FROM jcitems 95 WHERE employee_id = $form->{id}|; 96 if (! $dbh->selectrow_array($query)) { 97 $form->{status} = "orphaned"; 98 } 99 } 100 101 $query = qq|SELECT * 102 FROM payrate 103 WHERE trans_id = $form->{id} 104 ORDER BY id|; 105 $sth = $dbh->prepare($query); 106 $sth->execute || $form->dberror($query); 107 108 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 109 push @{ $form->{all_payrate} }, $ref; 110 } 111 $sth->finish; 112 113 # get wage links 114 $query = qq|SELECT w.* 115 FROM employeewage ew 116 JOIN wage w ON (ew.wage_id = w.id) 117 WHERE ew.employee_id = $form->{id} 118 ORDER BY ew.id|; 119 $sth = $dbh->prepare($query); 120 $sth->execute || $form->dberror($query); 121 122 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 123 push @{ $form->{all_employeewage} }, $ref; 124 } 125 $sth->finish; 126 127 # get deductions 128 $query = qq|SELECT d.id, d.description, ed.exempt, ed.maximum, 129 ed.id AS edid 130 FROM employeededuction ed 131 JOIN deduction d ON (ed.deduction_id = d.id) 132 WHERE ed.employee_id = $form->{id} 133 ORDER BY ed.id|; 134 $sth = $dbh->prepare($query); 135 $sth->execute || $form->dberror($query); 136 137 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 138 push @{ $form->{all_employeededuction} }, $ref; 139 } 140 $sth->finish; 141 142 $query = qq|SELECT d.id, da.trans_id, da.withholding, da.percent 143 FROM deduct da 144 JOIN deduction d ON (da.deduction_id = d.id)|; 145 $sth = $dbh->prepare($query); 146 $sth->execute || $form->dberror($query); 147 148 my %deduct; 149 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 150 $ref->{percent} ||= 1; 151 push @{ $form->{deduct}{$ref->{trans_id}} }, $ref; 152 $deduct{$ref->{id}} = 1; 153 } 154 $sth->finish; 155 156 # reorder to calculate dependencies 157 for $ref (@{ $form->{all_employeededuction} }) { 158 if ($deduct{$ref->{id}}) { 159 push @{$deduct{a}}, $ref; 160 } else { 161 push @{$deduct{b}}, $ref; 162 } 163 } 164 @{ $form->{all_employeededuction} } = (); 165 push @{ $form->{all_employeededuction} }, @{$deduct{a}}; 166 push @{ $form->{all_employeededuction} }, @{$deduct{b}}; 167 168 $notid = qq|AND id != $form->{id}|; 169 170 # retrieve totals 171 my $cd; 172 if ($form->{transdate}) { 173 $cd = substr($form->datetonum($myconfig, $form->{transdate}),0,4); 174 } else { 175 $query = qq|SELECT to_char(current_date, 'YYYY')|; 176 ($cd) = $dbh->selectrow_array($query); 177 } 178 179 %deduct = (); 180 if ($form->{trans_id}) { 181 $query = qq|SELECT * 182 FROM pay_trans 183 WHERE trans_id = $form->{trans_id}|; 184 my $tth = $dbh->prepare($query); 185 $tth->execute; 186 while ($ref = $tth->fetchrow_hashref(NAME_lc)) { 187 $deduct{$ref->{id}} = $ref->{amount}; 188 } 189 $tth->finish; 190 } 191 192 $query = qq|SELECT SUM(pt.amount) 193 FROM pay_trans pt 194 JOIN ap a ON (a.id = pt.trans_id) 195 WHERE a.vendor_id = $form->{id} 196 AND a.transdate >= '${cd}0101' 197 AND a.transdate <= '${cd}1231' 198 AND pt.id = ?|; 199 $sth = $dbh->prepare($query); 200 201 for $ref (@{ $form->{all_employeededuction} }) { 202 $sth->execute($ref->{id}); 203 ($form->{total}{$ref->{id}}) = $sth->fetchrow_array; 204 $form->{total}{$ref->{id}} -= $deduct{$ref->{id}} if $deduct{$ref->{id}}; 205 $form->{total}{$ref->{id}} *= -1; 206 $sth->finish; 207 } 208 209 $form->all_references($dbh); 210 211 $form->create_lock($myconfig, $dbh, $form->{id}, 'hr') unless $nolock; 212 213 } else { 214 215 $form->{startdate} = $form->current_date($myconfig); 216 217 } 218 219 HR->isadmin($myconfig, $form, $dbh); 220 221 # get wages 222 $query = qq|SELECT * 223 FROM wage 224 ORDER BY 2|; 225 $sth = $dbh->prepare($query); 226 $sth->execute || $form->dberror($query); 227 228 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 229 push @{ $form->{all_wage} }, $ref; 230 } 231 $sth->finish; 232 233 234 # get deductions 235 $query = qq|SELECT * 236 FROM deduction 237 ORDER BY 2|; 238 $sth = $dbh->prepare($query); 239 $sth->execute || $form->dberror($query); 240 241 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 242 push @{ $form->{all_deduction} }, $ref; 243 # for payroll 244 $form->{payrolldeduction}{$ref->{id}} = $ref; 245 } 246 $sth->finish; 247 248 # get deductionrates 249 $query = qq|SELECT * 250 FROM deductionrate 251 ORDER BY trans_id, rn|; 252 $sth = $dbh->prepare($query); 253 $sth->execute || $form->dberror($query); 254 255 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 256 push @{ $form->{all_deductionrate} }, $ref; 257 } 258 $sth->finish; 259 260 my %ae = ( ap => { category => 'L', link => qq| AND c.link = 'AP' | }, 261 payment => { category => 'A', link => qq| AND c.link LIKE '%AP_paid%' | } ); 262 for (qw(ap payment)) { 263 $query = qq|SELECT c.accno, c.description, 264 l.description AS translation 265 FROM chart c 266 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 267 WHERE c.charttype = 'A' 268 AND c.category = '$ae{$_}{category}' 269 $ae{$_}{link} 270 AND c.closed = '0' 271 ORDER BY c.accno|; 272 $sth = $dbh->prepare($query); 273 $sth->execute || $form->dberror($query); 274 275 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 276 $ref->{description} = $ref->{translation} if $ref->{translation}; 277 push @{ $form->{"${_}_accounts"} }, $ref; 278 } 279 $sth->finish; 280 } 281 282 # get paymentmethod 283 $query = qq|SELECT * 284 FROM paymentmethod 285 ORDER BY rn|; 286 $sth = $dbh->prepare($query); 287 $sth->execute || $form->dberror($query); 288 289 @{ $form->{"all_paymentmethod"} } = (); 290 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 291 push @{ $form->{"all_paymentmethod"} }, $ref; 292 } 293 $sth->finish; 294 295 $dbh->disconnect; 296 297} 298 299 300sub isadmin { 301 my ($self, $myconfig, $form, $dbh) = @_; 302 303 my $disconnect = ($dbh) ? 0 : 1;; 304 305 # connect to database 306 $dbh = $form->dbconnect($myconfig) unless $dbh; 307 308 $form->{id} *= 1; 309 my $rne; 310 311 my $query = qq|SELECT a.rn 312 FROM acsrole a 313 JOIN employee e ON (e.acsrole_id = a.id) 314 WHERE e.id = $form->{id}|; 315 ($rne) = $dbh->selectrow_array($query); 316 317 my $login = $form->{login}; 318 $login =~ s/\@.*//; 319 $query = qq|SELECT a.rn 320 FROM acsrole a 321 JOIN employee e ON (e.acsrole_id = a.id) 322 WHERE e.login = '$login'|; 323 my ($rnl) = $dbh->selectrow_array($query); 324 325 $rnl *= 1; 326 if ($rnl) { 327 $form->{admin} = ($rne) ? $rnl < $rne : 1; 328 } 329 $form->{admin} = 1 if $login eq 'admin'; 330 331 # get acsrole 332 $query = qq|SELECT id, description 333 FROM acsrole 334 WHERE rn > $rnl 335 ORDER BY rn|; 336 $sth = $dbh->prepare($query); 337 $sth->execute || $form->dberror($query); 338 339 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 340 push @{ $form->{all_acsrole} }, $ref; 341 } 342 $sth->finish; 343 344 $dbh->disconnect if $disconnect; 345 346} 347 348 349sub acsrole { 350 my ($self, $myconfig, $form) = @_; 351 352 my $dbh = $form->dbconnect($myconfig); 353 354 my $id; 355 (undef, $id) = split /--/, $form->{acsrole}; 356 $id *= 1; 357 358 my $query = qq|SELECT acs 359 FROM acsrole 360 WHERE id = $id|; 361 ($_) = $dbh->selectrow_array($query); 362 363 $dbh->disconnect; 364 365 $_; 366 367} 368 369 370sub save_employee { 371 my ($self, $myconfig, $form) = @_; 372 373 # connect to database 374 my $dbh = $form->dbconnect_noauto($myconfig); 375 my $query; 376 my $sth; 377 378 $form->remove_locks($myconfig, $dbh, 'hr'); 379 380 my $bank_address_id; 381 my $employeelogin; 382 my $sales; 383 my $acsrole_id; 384 my $rn; 385 386 for (qw(paymentmethod acsrole)) { (undef, $form->{"${_}_id"}) = split /--/, $form->{$_} } 387 388 if ($form->{id} *= 1) { 389 $query = qq|SELECT e.login, e.sales, e.acsrole_id, a.rn 390 FROM employee e 391 LEFT JOIN acsrole a ON (e.acsrole_id = a.id) 392 WHERE e.id = $form->{id}|; 393 394 ($employeelogin, $sales, $acsrole_id, $rn) = $dbh->selectrow_array($query); 395 396 my $login = $form->{login}; 397 $login =~ s/@.*//; 398 399 $query = qq|SELECT a.rn 400 FROM acsrole a 401 JOIN employee e ON (e.acsrole_id = a.id) 402 WHERE e.login = '$login'|; 403 my ($loginrn) = $dbh->selectrow_array($query); 404 405 unless ($form->{admin}) { 406 if ($rn <= $loginrn) { 407 $form->{acsrole_id} *= 1; 408 $form->{sales} = $sales; 409 $form->{acsrole} = qq|--$acsrole_id|; 410 $form->{nochange} = 1; 411 } 412 } 413 414 if ($employeelogin) { 415 $query = qq|UPDATE report SET 416 login = '$form->{employeelogin}' 417 WHERE login = '$employeelogin'|; 418 $dbh->do($query) || $form->dberror($query); 419 } 420 421 $query = qq|SELECT address_id 422 FROM bank 423 WHERE id = $form->{id}|; 424 ($bank_address_id) = $dbh->selectrow_array($query); 425 426 $query = qq|DELETE FROM bank 427 WHERE id = $form->{id}|; 428 $dbh->do($query) || $form->dberror($query); 429 430 $bank_address_id *= 1; 431 $query = qq|DELETE FROM address 432 WHERE trans_id = $bank_address_id|; 433 $dbh->do($query) || $form->dberror($query); 434 435 $form->{addressid} *= 1; 436 $query = qq|DELETE FROM address 437 WHERE id = $form->{addressid}|; 438 $dbh->do($query) || $form->dberror($query); 439 440 $query = qq|DELETE FROM payrate 441 WHERE trans_id = $form->{id}|; 442 $dbh->do($query) || $form->dberror($query); 443 444 } else { 445 my $uid = localtime; 446 $uid .= $$; 447 448 $query = qq|INSERT INTO employee (name) 449 VALUES ('$uid')|; 450 $dbh->do($query) || $form->dberror($query); 451 452 $query = qq|SELECT id FROM employee 453 WHERE name = '$uid'|; 454 $sth = $dbh->prepare($query); 455 $sth->execute || $form->dberror($query); 456 457 ($form->{id}) = $sth->fetchrow_array; 458 $sth->finish; 459 } 460 461 $form->{employeenumber} = $form->update_defaults($myconfig, "employeenumber", $dbh) if ! $form->{employeenumber}; 462 463 for (qw(ap payment)) { ($form->{$_}) = split /--/, $form->{$_} } 464 $form->{acsrole_id} ||= 'NULL'; 465 $form->{sales} *= 1; 466 467 $employeelogin = ($form->{employeelogin}) ? $dbh->quote($form->{employeelogin}) : 'NULL'; 468 469 $query = qq|UPDATE employee SET 470 employeenumber = |.$dbh->quote($form->{employeenumber}).qq|, 471 name = |.$dbh->quote($form->{name}).qq|, 472 workphone = '$form->{workphone}', 473 workfax = '$form->{workfax}', 474 workmobile = '$form->{workmobile}', 475 homephone = '$form->{homephone}', 476 homemobile = '$form->{homemobile}', 477 startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|, 478 enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|, 479 notes = |.$dbh->quote($form->{notes}).qq|, 480 sales = '$form->{sales}', 481 login = $employeelogin, 482 email = |.$dbh->quote($form->{email}).qq|, 483 ssn = '$form->{ssn}', 484 dob = |.$form->dbquote($form->{dob}, SQL_DATE).qq|, 485 payperiod = |.$form->dbquote($form->{payperiod}, SQL_INT).qq|, 486 apid = (SELECT id FROM chart WHERE accno = '$form->{ap}'), 487 paymentid = (SELECT id FROM chart WHERE accno = '$form->{payment}'), 488 paymentmethod_id = |.$dbh->quote($form->{paymentmethod_id}).qq|, 489 acsrole_id = $form->{acsrole_id}, 490 acs = '$form->{acs}' 491 WHERE id = $form->{id}|; 492 $dbh->do($query) || $form->dberror($query); 493 494 # add address 495 my $id; 496 my $var; 497 498 if ($form->{addressid}) { 499 $id = "id, "; 500 $var = "$form->{addressid}, "; 501 } 502 503 $query = qq|INSERT INTO address ($id trans_id, address1, address2, 504 city, state, zipcode, country) VALUES ($var 505 $form->{id}, 506 |.$dbh->quote($form->{address1}).qq|, 507 |.$dbh->quote($form->{address2}).qq|, 508 |.$dbh->quote($form->{city}).qq|, 509 |.$dbh->quote($form->{state}).qq|, 510 |.$dbh->quote($form->{zipcode}).qq|, 511 |.$dbh->quote($form->{country}).qq|)|; 512 $dbh->do($query) || $form->dberror($query); 513 514 my $ok; 515 516 for (qw(iban bic membernumber clearingnumber)) { 517 if ($form->{$_}) { 518 $ok = 1; 519 last; 520 } 521 } 522 523 if (!$ok) { 524 for (qw(name address1 address2 city state zipcode country)) { 525 if ($form->{"bank$_"}) { 526 $ok = 1; 527 last; 528 } 529 } 530 } 531 532 if ($ok) { 533 if ($bank_address_id) { 534 $query = qq|INSERT INTO bank (id, name, iban, bic, membernumber, 535 clearingnumber, address_id) 536 VALUES ($form->{id}, | 537 .$dbh->quote(uc $form->{bankname}).qq|,| 538 .$dbh->quote($form->{iban}).qq|,| 539 .$dbh->quote($form->{bic}).qq|,| 540 .$dbh->quote($form->{membernumber}).qq|,| 541 .$dbh->quote($form->{clearingnumber}).qq|, 542 $bank_address_id 543 )|; 544 } else { 545 $query = qq|INSERT INTO bank (id, name, iban, bic, membernumber, 546 clearingnumber) 547 VALUES ($form->{id}, | 548 .$dbh->quote(uc $form->{bankname}).qq|,| 549 .$dbh->quote($form->{iban}).qq|,| 550 .$dbh->quote($form->{bic}).qq|,| 551 .$dbh->quote($form->{membernumber}).qq|,| 552 .$dbh->quote($form->{clearingnumber}).qq| 553 )|; 554 } 555 $dbh->do($query) || $form->dberror($query); 556 557 $query = qq|SELECT address_id 558 FROM bank 559 WHERE id = $form->{id}|; 560 ($bank_address_id) = $dbh->selectrow_array($query); 561 562 } 563 564 $ok = 0; 565 for (qw(address1 address2 city state zipcode country)) { 566 if ($form->{"bank$_"}) { 567 $ok = 1; 568 last; 569 } 570 } 571 572 if ($ok) { 573 if ($bank_address_id) { 574 $query = qq|INSERT INTO address (id, trans_id, address1, address2, 575 city, state, zipcode, country) VALUES ( 576 $bank_address_id, $bank_address_id, 577 |.$dbh->quote(uc $form->{bankaddress1}).qq|, 578 |.$dbh->quote(uc $form->{bankaddress2}).qq|, 579 |.$dbh->quote(uc $form->{bankcity}).qq|, 580 |.$dbh->quote(uc $form->{bankstate}).qq|, 581 |.$dbh->quote(uc $form->{bankzipcode}).qq|, 582 |.$dbh->quote(uc $form->{bankcountry}).qq|)|; 583 $dbh->do($query) || $form->dberror($query); 584 585 } else { 586 $query = qq|INSERT INTO bank (id, name) 587 VALUES ($form->{id}, 588 |.$dbh->quote(uc $form->{bankname}).qq|)|; 589 $dbh->do($query) || $form->dberror($query); 590 591 $query = qq|SELECT address_id 592 FROM bank 593 WHERE id = $form->{id}|; 594 ($bank_address_id) = $dbh->selectrow_array($query); 595 596 $query = qq|INSERT INTO address (id, trans_id, address1, address2, 597 city, state, zipcode, country) VALUES ( 598 $bank_address_id, $bank_address_id, 599 |.$dbh->quote(uc $form->{bankaddress1}).qq|, 600 |.$dbh->quote(uc $form->{bankaddress2}).qq|, 601 |.$dbh->quote(uc $form->{bankcity}).qq|, 602 |.$dbh->quote(uc $form->{bankstate}).qq|, 603 |.$dbh->quote(uc $form->{bankzipcode}).qq|, 604 |.$dbh->quote(uc $form->{bankcountry}).qq|)|; 605 $dbh->do($query) || $form->dberror($query); 606 } 607 } 608 609 610 # insert wage, deduction and exempt for payroll 611 $query = qq|DELETE FROM employeewage 612 WHERE employee_id = $form->{id}|; 613 $dbh->do($query) || $form->dberror($query); 614 615 $query = qq|INSERT INTO employeewage (employee_id, id, wage_id) VALUES 616 ($form->{id},?,?)|; 617 my $sth = $dbh->prepare($query) || $form->dberror($query); 618 619 for ($i = 1; $i <= $form->{wage_rows}; $i++) { 620 (undef, $wage_id) = split /--/, $form->{"wage_$i"}; 621 if ($wage_id) { 622 $sth->execute($i,$wage_id) || $form->dberror($query); 623 } 624 } 625 $sth->finish; 626 627 $query = qq|DELETE FROM employeededuction 628 WHERE employee_id = $form->{id}|; 629 $dbh->do($query) || $form->dberror($query); 630 631 $query = qq|INSERT INTO employeededuction (employee_id, id, deduction_id, 632 exempt, maximum) VALUES ($form->{id},?,?,?,?)|; 633 my $sth = $dbh->prepare($query) || $form->dberror($query); 634 635 for ($i = 1; $i <= $form->{deduction_rows}; $i++) { 636 for (qw(exempt maximum)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } 637 (undef, $deduction_id) = split /--/, $form->{"deduction_$i"}; 638 if ($deduction_id) { 639 $sth->execute($i, $deduction_id, $form->{"exempt_$i"}, $form->{"maximum_$i"}) || $form->dberror($query); 640 } 641 } 642 $sth->finish; 643 644 $query = qq|INSERT INTO payrate (trans_id, id, rate, above) 645 VALUES ($form->{id},?,?,?)|; 646 $sth = $dbh->prepare($query) || $form->dberror($query); 647 648 for ($i = 1; $i <= $form->{payrate_rows}; $i++) { 649 for (qw(rate above)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } 650 $form->{"above_$i"} = $form->parse_amount($myconfig, $form->{"above_$i"}); 651 if ($form->{"rate_$i"}) { 652 $sth->execute($i, $form->{"rate_$i"}, $form->{"above_$i"}) || $form->dberror($query); 653 } 654 } 655 $sth->finish; 656 657 my %audittrail = ( tablename => 'employee', 658 reference => $form->{name}, 659 formname => '', 660 action => 'saved', 661 id => $form->{id} 662 ); 663 664 $form->audittrail($dbh, "", \%audittrail); 665 666 $form->save_reference($dbh, 'employee'); 667 668 my $rc = $dbh->commit; 669 $dbh->disconnect; 670 671 $rc; 672 673} 674 675 676sub delete_employee { 677 my ($self, $myconfig, $form) = @_; 678 679 # connect to database 680 my $dbh = $form->dbconnect_noauto($myconfig); 681 682 my $query; 683 684 for (qw(id db)) { $form->{$_} =~ s/;//g } 685 686 $query = qq|SELECT address_id 687 FROM bank 688 WHERE id = $form->{id}|; 689 my ($bank_address_id) = $dbh->selectrow_array($query); 690 691 $bank_address_id *= 1; 692 $query = qq|DELETE FROM address 693 WHERE trans_id = $bank_address_id|; 694 $dbh->do($query) || $form->dberror($query); 695 696 $query = qq|SELECT login 697 FROM employee 698 WHERE id = $form->{id}|; 699 my ($login) = $dbh->selectrow_array($query); 700 701 if ($login) { 702 $query = qq|UPDATE report 703 SET login = '' 704 WHERE login = '$login'|; 705 $dbh->do($query) || $form->dberror($query); 706 } 707 708 # delete employee 709 $query = qq|DELETE FROM $form->{db} 710 WHERE id = $form->{id}|; 711 $dbh->do($query) || $form->dberror($query); 712 713 $query = qq|DELETE FROM address 714 WHERE trans_id = $form->{id}|; 715 $dbh->do($query) || $form->dberror($query); 716 717 $form->delete_references($dbh); 718 719 $form->remove_locks($myconfig, $dbh, 'hr'); 720 721 my %audittrail = ( tablename => 'employee', 722 reference => $login, 723 formname => '', 724 action => 'deleted', 725 id => $form->{id} 726 ); 727 728 $form->audittrail($dbh, "", \%audittrail); 729 730 my $rc = $dbh->commit; 731 $dbh->disconnect; 732 733 $rc; 734 735} 736 737 738sub employees { 739 my ($self, $myconfig, $form) = @_; 740 741 # connect to database 742 my $dbh = $form->dbconnect($myconfig); 743 744 my %defaults = $form->get_defaults($dbh, \@{['company', 'precision']}); 745 for (keys %defaults) { $form->{$_} = $defaults{$_} } 746 747 my $where = "1 = 1"; 748 749 $form->{sort} ||= "name"; 750 751 my $var; 752 753 for (qw(name employeenumber notes)) { 754 if ($form->{$_} ne "") { 755 $var = $form->like(lc $form->{$_}); 756 $where .= " AND lower(e.$_) LIKE '$var'"; 757 } 758 } 759 if ($form->{employeelogin} ne "") { 760 $var = $form->like(lc $form->{employeelogin}); 761 $where .= " AND lower(e.login) LIKE '$var'"; 762 } 763 if ($form->{startdatefrom}) { 764 $where .= " AND e.startdate >= '$form->{startdatefrom}'"; 765 } 766 if ($form->{startdateto}) { 767 $where .= " AND e.startdate <= '$form->{startdateto}'"; 768 } 769 if ($form->{status} eq 'sales') { 770 $where .= " AND e.sales = '1'"; 771 } 772 if ($form->{status} eq 'orphaned') { 773 $where .= qq| AND e.login IS NULL 774 AND NOT e.id IN 775 (SELECT DISTINCT employee_id 776 FROM jcitems)|; 777 } 778 if ($form->{status} eq 'active') { 779 $where .= qq| AND e.enddate IS NULL|; 780 } 781 if ($form->{status} eq 'inactive') { 782 $where .= qq| AND e.enddate <= current_date|; 783 } 784 if ($form->{acsrole}) { 785 (undef, $var) = split /--/, $form->{acsrole}; 786 $where .= qq| AND r.id = $var|; 787 } 788 789 my $payrolljoin = qq|LEFT JOIN employeewage ew ON (e.id = ew.employee_id)|; 790 791 if ($form->{status} eq 'payroll') { 792 $payrolljoin = qq|JOIN employeewage ew ON (e.id = ew.employee_id)|; 793 } 794 795 $query = qq|SELECT DISTINCT e.*, 796 ad.address1, ad.address2, ad.city, ad.state, 797 ad.zipcode, ad.country, 798 bk.iban, bk.bic, 799 r.description AS acsrole, 800 ew.employee_id AS payroll 801 FROM employee e 802 LEFT JOIN address ad ON (ad.trans_id = e.id) 803 LEFT JOIN bank bk ON (bk.id = e.id) 804 LEFT JOIN acsrole r ON (r.id = e.acsrole_id) 805 $payrolljoin 806 WHERE $where|; 807 808 my @sf = qw(name); 809 my %ordinal = $form->ordinal_order($dbh, $query); 810 $query .= qq| ORDER BY | .$form->sort_order(\@sf, \%ordinal); 811 812 my $sth = $dbh->prepare($query); 813 $sth->execute || $form->dberror($query); 814 815 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { 816 $ref->{address} = ""; 817 for (qw(address1 address2 city state zipcode country)) { $ref->{address} .= "$ref->{$_} " } 818 push @{ $form->{all_employee} }, $ref; 819 } 820 $sth->finish; 821 822 $query = qq|SELECT c.accno, c.description, l.description AS translation 823 FROM chart c 824 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 825 WHERE c.link = 'AP' 826 ORDER BY c.accno|; 827 $sth = $dbh->prepare($query); 828 $sth->execute || $form->dberror($query); 829 830 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 831 $ref->{description} = $ref->{translation} if $ref->{translation}; 832 push @{ $form->{all_ap} }, $ref; 833 } 834 $sth->finish; 835 836 $query = qq|SELECT c.accno, c.description, l.description AS translation 837 FROM chart c 838 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 839 WHERE c.link LIKE '%AP_paid%' 840 ORDER BY c.accno|; 841 $sth = $dbh->prepare($query); 842 $sth->execute || $form->dberror($query); 843 844 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 845 $ref->{description} = $ref->{translation} if $ref->{translation}; 846 push @{ $form->{all_payment} }, $ref; 847 } 848 $sth->finish; 849 850 $query = qq|SELECT * 851 FROM paymentmethod 852 ORDER BY rn|; 853 $sth = $dbh->prepare($query); 854 $sth->execute || $form->dberror($query); 855 856 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 857 push @{ $form->{all_paymentmethod} }, $ref; 858 } 859 $sth->finish; 860 861 $form->get_peripherals($dbh); 862 863 $form->all_languages($myconfig, $dbh); 864 865 $form->all_projects($myconfig, $dbh); 866 867 $form->all_departments($myconfig, $dbh, 'vendor'); 868 869 $dbh->disconnect; 870 871} 872 873 874sub payroll_links { 875 my ($self, $myconfig, $form) = @_; 876 877 # connect to database 878 my $dbh = $form->dbconnect($myconfig); 879 880 $form->remove_locks($myconfig, $dbh); 881 882 my @var; 883 884 my @df = qw(closedto revtrans company precision namesbynumber referenceurl); 885 my %defaults = $form->get_defaults($dbh, \@df); 886 for (keys %defaults) { $form->{$_} = $defaults{$_} } 887 888 my $sortorder = "name"; 889 $sortorder = "employeenumber" if $form->{namesbynumber}; 890 891 my $query = qq|SELECT * 892 FROM employee 893 WHERE id IN (SELECT employee_id FROM employeewage) 894 AND enddate IS NULL 895 ORDER BY $sortorder|; 896 my $sth = $dbh->prepare($query); 897 $sth->execute || $form->dberror($query); 898 899 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { 900 push @{ $form->{all_employee} }, $ref; 901 } 902 $sth->finish; 903 904 $form->{datepaid} = $form->{transdate} = $form->current_date($myconfig); 905 906 if ($form->{id} *= 1) { 907 $query = qq|SELECT a.*, e.name AS employee, e.payperiod, 908 d.description AS department, 909 c1.accno AS ap_accno, c1.description AS ap_accno_description, 910 t1.description AS ap_accno_translation, 911 c2.accno AS payment_accno, c2.description AS payment_accno_description, 912 t2.description AS payment_accno_translation, 913 pm.description AS paymentmethod, e.paymentmethod_id 914 FROM ap a 915 JOIN employee e ON (e.id = a.vendor_id) 916 LEFT JOIN department d ON (d.id = a.department_id) 917 LEFT JOIN chart c1 ON (c1.id = e.apid) 918 LEFT JOIN translation t1 ON (t1.trans_id = c1.id AND t1.language_code = '$myconfig->{countrycode}') 919 LEFT JOIN chart c2 ON (c2.id = e.paymentid) 920 LEFT JOIN translation t2 ON (t2.trans_id = c2.id AND t2.language_code = '$myconfig->{countrycode}') 921 LEFT JOIN paymentmethod pm ON (pm.id = e.paymentmethod_id) 922 WHERE a.id = $form->{id}|; 923 $sth = $dbh->prepare($query); 924 $sth->execute || $form->dberror($query); 925 $ref = $sth->fetchrow_hashref(NAME_lc); 926 delete $ref->{id}; 927 928 # projectnumber 929 $query = qq~SELECT p.projectnumber || '--' || p.id 930 FROM project p 931 JOIN acc_trans a ON (a.project_id = p.id) 932 WHERE a.trans_id = $form->{id}~; 933 ($form->{project}) = $dbh->selectrow_array($query); 934 935 for (qw(ap payment)) { 936 $ref->{$_} = qq|$ref->{"${_}_accno"}--$ref->{"${_}_accno_description"}|; 937 $ref->{$_} = qq|$ref->{"${_}_accno"}--$ref->{"${_}_accno_description"}| if $ref->{"${_}_accno_translation"}; 938 } 939 940 for (qw(paymentmethod department)) { 941 $ref->{$_} = qq|$ref->{$_}--$ref->{"${_}_id"}|; 942 } 943 944 $ref->{employee} = qq|$ref->{employee}--$ref->{vendor_id}|; 945 946 for (keys %$ref) { $form->{$_} = $ref->{$_} } 947 $sth->finish; 948 949 # ap 950 $query = qq|SELECT c.accno, c.description, t.description AS translation 951 FROM acc_trans ac 952 JOIN chart c ON (c.id = ac.chart_id) 953 LEFT JOIN translation t ON (t.trans_id = c.id AND t.language_code = '$myconfig->{countrycode}') 954 WHERE c.link = 'AP' 955 AND ac.trans_id = $form->{id}|; 956 (@var) = $dbh->selectrow_array($query); 957 if (@var) { 958 $form->{ap} = qq|$var[0]--$var[1]|; 959 $form->{ap} = qq|$var[0]--$var[2]| if $var[2]; 960 } 961 962 # payment 963 $query = qq|SELECT ac.source, ac.memo, c.accno, c.description, 964 t.description AS translation, 965 pm.id AS pmid, pm.description AS paymentmethod 966 FROM acc_trans ac 967 JOIN chart c ON (c.id = ac.chart_id) 968 LEFT JOIN translation t ON (t.trans_id = c.id AND t.language_code = '$myconfig->{countrycode}') 969 JOIN payment p ON (p.trans_id = ac.trans_id and p.id = ac.id) 970 LEFT JOIN paymentmethod pm ON (pm.id = p.paymentmethod_id) 971 WHERE c.link LIKE '%AP_paid%' 972 AND ac.trans_id = $form->{id}|; 973 (@var) = $dbh->selectrow_array($query); 974 if (@var) { 975 $form->{source} = $var[0]; 976 $form->{memo} = $var[1]; 977 $form->{payment} = qq|$var[2]--$var[3]|; 978 $form->{payment} = qq|$var[2]--$var[4]| if $var[4]; 979 $form->{paymentmethod} = qq|$var[6]--$var[5]|; 980 } 981 982 my %wage; 983 984 # all wages for employee 985 $query = qq|SELECT w.* 986 FROM wage w 987 JOIN employeewage ew ON (ew.wage_id = w.id) 988 WHERE ew.employee_id = $form->{vendor_id} 989 ORDER BY ew.id|; 990 $sth = $dbh->prepare($query); 991 $sth->execute || $form->dberror($query); 992 993 $i = 0; 994 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 995 $i++; 996 $form->{"wage_$i"} = $ref->{description}; 997 $form->{"wage_id_$i"} = $ref->{id}; 998 $form->{"amount_$i"} = $ref->{amount}; 999 $wage{$ref->{id}} = $i; 1000 } 1001 $sth->finish; 1002 $form->{wage_rows} = $i; 1003 1004 # wages and deductions 1005 $query = qq|SELECT pt.*, w.description AS wage, d.description AS deduction 1006 FROM pay_trans pt 1007 LEFT JOIN wage w ON (w.id = pt.id) 1008 LEFT JOIN deduction d ON (d.id = pt.id) 1009 WHERE pt.trans_id = $form->{id}|; 1010 $sth = $dbh->prepare($query); 1011 $sth->execute || $form->dberror($query); 1012 1013 my $i = 0; 1014 my $j = 0; 1015 1016 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1017 if ($ref->{wage}) { 1018 for (qw(qty amount)) { $form->{"${_}_$wage{$ref->{id}}"} = $ref->{$_} } 1019 } 1020 if ($ref->{deduction}) { 1021 $j++; 1022 $form->{"deduction_$j"} = $ref->{deduction}; 1023 $form->{"deduction_id_$j"} = $ref->{id}; 1024 } 1025 } 1026 $sth->finish; 1027 $form->{deduction_rows} = $j; 1028 1029 # payrates 1030 $query = qq|SELECT * 1031 FROM payrate 1032 WHERE trans_id = $form->{vendor_id} 1033 ORDER BY id|; 1034 $sth = $dbh->prepare($query); 1035 $sth->execute || $form->dberror($query); 1036 1037 $i = 0; 1038 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1039 $i++; 1040 for (qw(rate above)) { $form->{"${_}_$i"} = $ref->{$_} } 1041 } 1042 $sth->finish; 1043 $form->{payrate_rows} = $i; 1044 1045 $form->all_references($dbh); 1046 1047 $form->create_lock($myconfig, $dbh, $form->{id}, 'hr'); 1048 1049 } 1050 1051 $query = qq|SELECT c.accno, c.description, l.description AS translation 1052 FROM chart c 1053 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 1054 WHERE c.link = 'AP' 1055 AND c.closed = '0' 1056 ORDER BY c.accno|; 1057 $sth = $dbh->prepare($query); 1058 $sth->execute || $form->dberror($query); 1059 1060 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1061 $ref->{description} = $ref->{translation} if $ref->{translation}; 1062 push @{ $form->{all_ap} }, $ref; 1063 } 1064 $sth->finish; 1065 1066 $query = qq|SELECT c.accno, c.description, l.description AS translation 1067 FROM chart c 1068 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 1069 WHERE c.link LIKE '%AP_paid%' 1070 AND c.closed = '0' 1071 ORDER BY c.accno|; 1072 $sth = $dbh->prepare($query); 1073 $sth->execute || $form->dberror($query); 1074 1075 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1076 $ref->{description} = $ref->{translation} if $ref->{translation}; 1077 push @{ $form->{all_payment} }, $ref; 1078 } 1079 $sth->finish; 1080 1081 $query = qq|SELECT * 1082 FROM paymentmethod 1083 ORDER BY rn|; 1084 $sth = $dbh->prepare($query); 1085 $sth->execute || $form->dberror($query); 1086 1087 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1088 push @{ $form->{all_paymentmethod} }, $ref; 1089 } 1090 $sth->finish; 1091 1092 $form->get_peripherals($dbh); 1093 1094 $form->all_languages($myconfig, $dbh); 1095 1096 $form->all_projects($myconfig, $dbh); 1097 1098 $form->all_departments($myconfig, $dbh, 'vendor'); 1099 1100 $dbh->disconnect; 1101 1102} 1103 1104 1105sub search_payroll { 1106 my ($self, $myconfig, $form) = @_; 1107 1108 # connect to database 1109 my $dbh = $form->dbconnect($myconfig); 1110 1111 my %defaults = $form->get_defaults($dbh, \@{['namesbynumber']}); 1112 1113 my $sortorder = "name"; 1114 if ($defaults{namesbynumber}) { 1115 $sortorder = "employeenumber"; 1116 } 1117 1118 my $query = qq|SELECT id, name, employeenumber 1119 FROM employee 1120 WHERE id IN (SELECT employee_id FROM employeewage) 1121 ORDER BY $sortorder|; 1122 my $sth = $dbh->prepare($query); 1123 $sth->execute || $form->dberror($query); 1124 1125 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { 1126 push @{ $form->{all_employee} }, $ref; 1127 } 1128 $sth->finish; 1129 1130 $query = qq|SELECT * 1131 FROM paymentmethod 1132 ORDER BY rn|; 1133 $sth = $dbh->prepare($query); 1134 $sth->execute || $form->dberror($query); 1135 1136 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1137 push @{ $form->{all_paymentmethod} }, $ref; 1138 } 1139 $sth->finish; 1140 1141 $form->all_projects($myconfig, $dbh); 1142 1143 $form->all_departments($myconfig, $dbh, 'vendor'); 1144 1145 $dbh->disconnect; 1146 1147} 1148 1149 1150 1151sub payroll_transactions { 1152 my ($self, $myconfig, $form) = @_; 1153 1154 my $dbh = $form->dbconnect($myconfig); 1155 1156 my %defaults = $form->get_defaults($dbh, \@{['company', 'precision']}); 1157 for (keys %defaults) { $form->{$_} = $defaults{$_} } 1158 1159 my $where = "1 = 1"; 1160 my $acwhere = "1 = 1"; 1161 1162 $form->{sort} ||= "employee"; 1163 1164 my $id; 1165 1166 $form->report_level($myconfig, $dbh); 1167 1168 if ($form->{employee}) { 1169 (undef, $id) = split /--/, $form->{employee}; 1170 $where .= qq| 1171 AND v.id = $id|; 1172 } 1173 1174 if ($form->{department}) { 1175 (undef, $id) = split /--/, $form->{department}; 1176 $where .= qq| 1177 AND a.department_id = $id|; 1178 } 1179 if ($form->{paymentmethod}) { 1180 (undef, $id) = split /--/, $form->{paymentmethod}; 1181 $where .= qq| 1182 AND a.paymentmethod_id = $id|; 1183 } 1184 1185 unless ($form->{transdatefrom} || $form->{transdateto}) { 1186 ($form->{transdatefrom}, $form->{transdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; 1187 } 1188 1189 $where .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom}; 1190 $where .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto}; 1191 1192 my $query = qq|SELECT DISTINCT v.name AS employee, 1193 a.transdate, a.invnumber, a.vendor_id, a.id, a.amount, a.paid 1194 FROM pay_trans pt 1195 JOIN ap a ON (a.id = pt.trans_id) 1196 JOIN vendor v ON (v.id = a.vendor_id) 1197 WHERE $where|; 1198 1199 my @sf = qw(employee); 1200 my %ordinal = $form->ordinal_order($dbh, $query); 1201 $query .= qq| ORDER BY | .$form->sort_order(\@sf, \%ordinal); 1202 1203 my $sth = $dbh->prepare($query); 1204 $sth->execute || $form->dberror($query); 1205 1206 # project ? 1207 (undef, $id) = split /--/, $form->{projectnumber}; 1208 $query = qq|SELECT trans_id 1209 FROM acc_trans 1210 WHERE project_id = $id 1211 AND trans_id = ?|; 1212 my $pth = $dbh->prepare($query); 1213 1214 # gl 1215 $query = qq|SELECT reference 1216 FROM gl 1217 WHERE id = ?|; 1218 my $gth = $dbh->prepare($query); 1219 1220 # wages / deductions 1221 $query = qq|SELECT * 1222 FROM pay_trans 1223 WHERE trans_id = ?|; 1224 my $wth = $dbh->prepare($query); 1225 1226 my $ok; 1227 my $ref; 1228 1229 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1230 $ok = 0; 1231 $wth->execute($ref->{id}); 1232 1233 while ($dref = $wth->fetchrow_hashref(NAME_lc)) { 1234 $ref->{glid} = $dref->{glid}; 1235 if ($ref->{$dref->{id}} = $form->round_amount($dref->{amount} * $dref->{qty}, $form->{precision})) { 1236 $form->{$dref->{id}} = 1; 1237 $ok = 1; 1238 } 1239 } 1240 $wth->finish; 1241 1242 if ($ref->{glid}) { 1243 $gth->execute($ref->{glid}); 1244 ($ref->{reference}) = $gth->fetchrow_array; 1245 $gth->finish; 1246 } 1247 1248 if ($form->{projectnumber}) { 1249 $pth->execute($ref->{id}); 1250 $ok = $pth->fetchrow_array; 1251 $pth->finish; 1252 } 1253 1254 push @{ $form->{transactions} }, $ref if $ok; 1255 1256 } 1257 $sth->finish; 1258 1259 for (qw(wage deduction)) { 1260 $query = qq|SELECT id, description 1261 FROM $_ 1262 ORDER BY 2|; 1263 $sth = $dbh->prepare($query); 1264 $sth->execute || $form->dberror($query); 1265 1266 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1267 push @{ $form->{"all_$_"} }, $ref if $form->{$ref->{id}}; 1268 } 1269 $sth->finish; 1270 } 1271 1272 $dbh->disconnect; 1273 1274} 1275 1276 1277sub payslip_details { 1278 my ($self, $myconfig, $form) = @_; 1279 1280 my $dbh = $form->dbconnect($myconfig); 1281 1282 my $query; 1283 my $sth; 1284 my $ref; 1285 my $id; 1286 1287 my %defaults = $form->get_defaults($dbh, \@{['company', 'address', 'tel', 'fax', 'companyemail', 'companywebsite', 'businessnumber', 'precision', 'referenceurl']}); 1288 for (keys %defaults) { $form->{$_} = $defaults{$_} } 1289 1290 %defaults = $form->get_defaults($dbh, \@{['printer_%']}); 1291 1292 (undef, $id) = split /--/, $form->{employee}; 1293 1294 if ($id *= 1) { 1295 $query = qq|SELECT e.*, 1296 ad.address1, ad.address2, ad.city, 1297 ad.state, ad.zipcode, ad.country, 1298 bk.name AS employeebankname, bk.iban AS employeebankiban, 1299 bk.bic AS employeebankbic, 1300 bk.membernumber AS employeebankmembernumber, 1301 bk.clearingnumber AS employeebankclearingnumber, 1302 ad1.address1 AS employeebankaddress1, 1303 ad1.address2 AS employeebankaddress2, 1304 ad1.city AS employeebankcity, 1305 ad1.state AS employeebankstate, 1306 ad1.zipcode AS employeebankzipcode, 1307 ad1.country AS employeebankcountry 1308 FROM employee e 1309 JOIN address ad ON (e.id = ad.trans_id) 1310 1311 LEFT JOIN bank bk ON (bk.id = e.id) 1312 LEFT JOIN address ad1 ON (bk.address_id = ad1.id) 1313 1314 WHERE e.id = $id|; 1315 1316 $sth = $dbh->prepare($query); 1317 $sth->execute || $form->dberror($query); 1318 1319 $ref = $sth->fetchrow_hashref(NAME_lc); 1320 $ref->{employeelogin} = $ref->{login}; 1321 delete $ref->{login}; 1322 for (keys %$ref) { $form->{$_} = $ref->{$_} } 1323 1324 $sth->finish; 1325 } 1326 1327 (undef, $id) = split /--/, $form->{paymentmethod}; 1328 if ($id *= 1) { 1329 $query = qq|SELECT fee, roundchange 1330 FROM paymentmethod 1331 WHERE id = $id|; 1332 1333 $sth = $dbh->prepare($query); 1334 $sth->execute || $form->dberror($query); 1335 1336 $ref = $sth->fetchrow_hashref(NAME_lc); 1337 for (keys %$ref) { $form->{$_} = $ref->{$_} } 1338 1339 $sth->finish; 1340 } 1341 1342 ($id) = split /--/, $form->{payment}; 1343 1344 if ($id *= 1) { 1345 $query = qq|SELECT 1346 c2.accno AS payment, c2.description AS payment_description, 1347 tr2.description AS payment_translation, 1348 bk.name AS bankname, bk.iban, bk.bic, bk.dcn, bk.rvc, 1349 bk.membernumber, bk.clearingnumber, 1350 ad1.address1 AS bankaddress1, 1351 ad1.address2 AS bankaddress2, 1352 ad1.city AS bankcity, 1353 ad1.state AS bankstate, 1354 ad1.zipcode AS bankzipcode, 1355 ad1.country AS bankcountry 1356 FROM chart c2 1357 LEFT JOIN bank bk ON (bk.id = c2.id) 1358 LEFT JOIN address ad1 ON (bk.id = ad1.trans_id) 1359 LEFT JOIN translation tr2 ON (tr2.trans_id = c2.id AND tr2.language_code = '$myconfig->{countrycode}') 1360 WHERE c2.accno = '$id'|; 1361 1362 $sth = $dbh->prepare($query); 1363 $sth->execute || $form->dberror($query); 1364 1365 $ref = $sth->fetchrow_hashref(NAME_lc); 1366 for (keys %$ref) { $form->{$_} = $ref->{$_} } 1367 1368 $sth->finish; 1369 } 1370 1371 1372 ($id) = split /--/, $form->{ap}; 1373 1374 if ($id *= 1) { 1375 $query = qq|SELECT 1376 c1.accno AS ap, c1.description AS ap_description, 1377 tr1.description AS ap_translation 1378 FROM chart c1 1379 LEFT JOIN translation tr1 ON (tr1.trans_id = c1.id AND tr1.language_code = '$myconfig->{countrycode}') 1380 WHERE c1.accno = '$id'|; 1381 1382 $sth = $dbh->prepare($query); 1383 $sth->execute || $form->dberror($query); 1384 1385 $ref = $sth->fetchrow_hashref(NAME_lc); 1386 for (keys %$ref) { $form->{$_} = $ref->{$_} } 1387 1388 $sth->finish; 1389 } 1390 1391 $dbh->disconnect; 1392 1393} 1394 1395 1396sub post_transaction { 1397 my ($self, $myconfig, $form) = @_; 1398 1399 my $dbh = $form->dbconnect_noauto($myconfig); 1400 my $ap = new Form; 1401 my $gl = new Form; 1402 1403 my %defaults = $form->get_defaults($dbh, \@{['expense_accno_id']}); 1404 1405 my $query; 1406 my $sth; 1407 my $ref; 1408 my $user_id; 1409 1410 $query = qq|SELECT * 1411 FROM wage 1412 WHERE id = ?|; 1413 my $wth = $dbh->prepare($query) || $form->dberror($query); 1414 1415 $query = qq|SELECT * 1416 FROM deduction 1417 WHERE id = ?|; 1418 my $dth = $dbh->prepare($query) || $form->dberror($query); 1419 1420 $query = qq|INSERT INTO pay_trans (trans_id, id, qty, amount) 1421 VALUES (?,?,?,?)|; 1422 my $pth = $dbh->prepare($query) || $form->dberror($query); 1423 1424 $query = qq|SELECT curr 1425 FROM curr 1426 ORDER BY rn|; 1427 ($ap->{currency}) = $dbh->selectrow_array($query); 1428 1429 $query = qq|SELECT accno 1430 FROM chart 1431 WHERE id = ?|; 1432 my $cth = $dbh->prepare($query) || $form->dberror($query); 1433 1434 my ($employee, $employee_id) = split /--/, $form->{employee}; 1435 1436 $cth->execute($defaults{expense_accno_id}); 1437 my ($expense_accno) = $cth->fetchrow_array; 1438 $cth->finish; 1439 1440 $query = qq|SELECT id 1441 FROM vendor 1442 WHERE id = $employee_id|; 1443 1444 if (! $dbh->selectrow_array($query)) { 1445 $query = qq|SELECT * 1446 FROM employee 1447 WHERE id = $employee_id|; 1448 $sth = $dbh->prepare($query); 1449 $sth->execute || $form->dberror($query); 1450 1451 $ref = $sth->fetchrow_hashref(NAME_lc); 1452 1453 (undef, $user_id) = $form->get_employee($dbh); 1454 my $vendornumber = $form->update_defaults($myconfig, 'vendornumber'); 1455 1456 $query = qq|INSERT INTO vendor (id, name, phone, 1457 fax, email, notes, 1458 vendornumber, employee_id, curr, 1459 startdate, enddate, arap_accno_id, 1460 payment_accno_id, paymentmethod_id) 1461 VALUES ( 1462 $employee_id, | 1463 .$dbh->quote($employee).qq|, '$ref->{workphone}', 1464 '$ref->{workfax}', '$ref->{email}', '$ref->{notes}', | 1465 .$dbh->quote($vendornumber).qq|, $user_id, '$ap->{currency}',| 1466 .$form->dbquote($ref->{startdate}, SQL_DATE).qq|, | 1467 .$form->dbquote($ref->{enddate}, SQL_DATE).qq|, | 1468 .$dbh->quote($ref->{apid}).qq|, | 1469 .$dbh->quote($ref->{paymentid}).qq|, | 1470 .$dbh->quote($ref->{paymentmethod_id}).qq|)|; 1471 $dbh->do($query) || $form->dberror($query); 1472 1473 $query = qq|INSERT INTO contact (trans_id, typeofcontact, 1474 phone, fax, mobile, email) 1475 VALUES ($employee_id, 'person', 1476 '$ref->{workphone}', '$ref->{workfax}', '$ref->{workmobile}', 1477 '$ref->{email}')|; 1478 $dbh->do($query) || $form->dberror($query); 1479 1480 $sth->finish; 1481 } 1482 1483 if ($form->{id} *= 1) { 1484 $query = qq|SELECT pt.glid, g.reference 1485 FROM pay_trans pt 1486 JOIN ap a ON (a.id = pt.trans_id) 1487 JOIN gl g ON (g.id = pt.glid) 1488 WHERE pt.trans_id = $form->{id}|; 1489 ($gl->{id}, $gl->{reference}) = $dbh->selectrow_array($query); 1490 1491 $query = qq|DELETE FROM pay_trans 1492 WHERE trans_id = $form->{id}|; 1493 $dbh->do($query) || $form->dberror($query); 1494 } 1495 1496 delete $form->{invnumber} if $form->{postasnew}; 1497 for (qw(login id invnumber department transdate description language_code)) { $ap->{$_} = $form->{$_} } 1498 1499 $ap->{vendor_id} = $employee_id; 1500 $ap->{defaultcurrency} = $ap->{currency}; 1501 $ap->{vc} = 'vendor'; 1502 $ap->{duedate} = $form->{transdate}; 1503 $ap->{AP} = $form->{ap}; 1504 1505 my $i = 0; 1506 my $j; 1507 my $employerpays; 1508 my $amount; 1509 1510 for $j (1 .. $form->{wage_rows}) { 1511 1512 if ($form->{"wage_id_$j"}) { 1513 1514 $wth->execute($form->{"wage_id_$j"}); 1515 $ref = $wth->fetchrow_hashref(NAME_lc); 1516 1517 # accno 1518 $cth->execute($ref->{chart_id}); 1519 ($accno) = $cth->fetchrow_array; 1520 $cth->finish; 1521 1522 if ($ref->{defer}) { 1523 # deferred wages 1524 $cth->execute($ref->{defer}); 1525 ($defer) = $cth->fetchrow_array; 1526 $cth->finish; 1527 1528 if ($form->{"pay_$j"}) { 1529 push @employerpays, { employeraccno => $defer, employeeaccno => $accno, description => $form->{"wage_$j"}, amount => $form->{"pay_$j"} }; 1530 } 1531 1532 } else { 1533 1534 $i++; 1535 1536 # acc_trans amount 1537 $ap->{"amount_$i"} = $form->{"pay_$j"}; 1538 $ap->{"AP_amount_$i"} = $accno || $expense_accno; 1539 $ap->{"description_$i"} = $form->{"wage_$j"}; 1540 $ap->{"projectnumber_$i"} = $form->{project}; 1541 } 1542 $wth->finish; 1543 1544 } 1545 } 1546 1547 for $j (1 .. $form->{deduction_rows}) { 1548 $dth->execute($form->{"deduction_id_$j"}); 1549 $ref = $dth->fetchrow_hashref(NAME_lc); 1550 1551 # employee 1552 if ($ref->{employeepays}) { 1553 # accno 1554 $cth->execute($ref->{employee_accno_id}); 1555 ($accno) = $cth->fetchrow_array; 1556 $cth->finish; 1557 1558 $accno ||= $expense_accno; 1559 $i++; 1560 1561 # acc_trans amount 1562 $ap->{"amount_$i"} = $form->{"deduct_$j"}; 1563 $ap->{"AP_amount_$i"} = $accno; 1564 $ap->{"description_$i"} = $form->{"deduction_$j"}; 1565 $ap->{"projectnumber_$i"} = $form->{project}; 1566 } 1567 1568 # employer 1569 if ($ref->{employerpays}) { 1570 $cth->execute($ref->{employer_accno_id}); 1571 ($employeraccno) = $cth->fetchrow_array; 1572 $cth->finish; 1573 1574 $employeraccno ||= $form->{ap}; 1575 1576 $amount = $form->parse_amount($myconfig, $form->{"deduct_$j"}) * $ref->{employerpays}; 1577 1578 if ($amount) { 1579 push @employerpays, { employeraccno => $employeraccno, employeeaccno => $accno, description => $form->{"deduction_$j"}, amount => $form->format_amount($myconfig, $amount, $form->{precision}) }; 1580 } 1581 1582 } 1583 1584 $dth->finish; 1585 } 1586 $ap->{rowcount} = $i; 1587 1588 # payment 1589 $ap->{paidaccounts} = 1; 1590 $ap->{"datepaid_1"} = $form->{datepaid}; 1591 $ap->{"paid_1"} = $form->{paid}; 1592 $ap->{"AP_paid_1"} = $form->{payment}; 1593 $ap->{"source_1"} = $form->{source}; 1594 $ap->{"memo_1"} = $form->{memo}; 1595 $ap->{"paymentmethod_1"} = $form->{paymentmethod}; 1596 1597 for $i (1 .. $form->{reference_rows}) { 1598 for $item (qw(description id archive_id filename confidential)) { 1599 $ap->{"reference${item}_$i"} = $form->{"reference${item}_$i"}; 1600 $gl->{"reference${item}_$i"} = $form->{"reference${item}_$i"}; 1601 } 1602 } 1603 $ap->{reference_rows} = $form->{reference_rows}; 1604 $gl->{reference_rows} = $form->{reference_rows}; 1605 1606 AA->post_transaction($myconfig, $ap, $dbh); 1607 1608 # pay_trans entries 1609 for $j (1 .. $form->{wage_rows}) { 1610 for (qw(qty amount)) { $form->{"${_}_$j"} = $form->parse_amount($myconfig, $form->{"${_}_$j"}) } 1611 1612 if ($form->round_amount($form->{"qty_$j"} * $form->{"amount_$j"}, 10)) { 1613 $pth->execute($ap->{id}, $form->{"wage_id_$j"}, $form->{"qty_$j"}, $form->{"amount_$j"}); 1614 $pth->finish; 1615 } 1616 } 1617 1618 for $j (1 .. $form->{deduction_rows}) { 1619 if ($form->{"deduct_$j"} = $form->parse_amount($myconfig, $form->{"deduct_$j"})) { 1620 $pth->execute($ap->{id}, $form->{"deduction_id_$j"}, 1, $form->{"deduct_$j"}); 1621 $pth->finish; 1622 } 1623 } 1624 1625 $gl->{login} = $form->{login}; 1626 1627 # employer pays 1628 if (@employerpays) { 1629 $gl->{transdate} = $form->{transdate}; 1630 $gl->{department} = $form->{department}; 1631 $gl->{currency} = $ap->{currency}; 1632 $gl->{defaultcurrency} = $ap->{currency}; 1633 $gl->{description} = $employee; 1634 $gl->{notes} = qq|$form->{description}|; 1635 1636 $i = 1; 1637 for (@employerpays) { 1638 1639 $amount = $form->parse_amount($myconfig, $_->{amount}); 1640 1641 if ($amount) { 1642 $gl->{"accno_$i"} = $_->{employeeaccno} || $form->{expense}; 1643 $gl->{"memo_$i"} = $_->{description}; 1644 $gl->{"projectnumber_$i"} = $form->{projectnumber}; 1645 1646 if ($amount < 0) { 1647 $gl->{"debit_$i"} = $form->format_amount($myconfig, $amount, $form->{precision}); 1648 } else { 1649 $gl->{"credit_$i"} = $form->format_amount($myconfig, $amount * -1, $form->{precision}); 1650 } 1651 $i++; 1652 1653 $gl->{"accno_$i"} = $_->{employeraccno} || $form->{ap}; 1654 $gl->{"memo_$i"} = $_->{description}; 1655 $gl->{"projectnumber_$i"} = $form->{projectnumber}; 1656 1657 if ($amount < 0) { 1658 $gl->{"credit_$i"} = $form->format_amount($myconfig, $amount, $form->{precision}); 1659 } else { 1660 $gl->{"debit_$i"} = $form->format_amount($myconfig, $amount * -1, $form->{precision}); 1661 } 1662 1663 $i++; 1664 } 1665 } 1666 $gl->{rowcount} = $i; 1667 1668 GL->post_transaction($myconfig, $gl, $dbh); 1669 1670 $query = qq|UPDATE pay_trans SET 1671 glid = $gl->{id} 1672 WHERE trans_id = $ap->{id}|; 1673 $dbh->do($query) || $form->dberror($query); 1674 1675 } 1676 1677 my $rc = $dbh->commit; 1678 1679 $dbh->disconnect; 1680 1681 $rc; 1682 1683} 1684 1685 1686sub get_deduction { 1687 my ($self, $myconfig, $form) = @_; 1688 1689 my $dbh = $form->dbconnect($myconfig); 1690 my $query; 1691 my $sth; 1692 my $ref; 1693 my $item; 1694 my $i; 1695 1696 $form->remove_locks($myconfig, $dbh, 'hr'); 1697 1698 my %defaults = $form->get_defaults($dbh, \@{['precision', 'company']}); 1699 for (keys %defaults) { $form->{$_} = $defaults{$_} } 1700 1701 if ($form->{id} *= 1) { 1702 $query = qq|SELECT d.*, 1703 c1.accno AS employee_accno, 1704 c1.description AS employee_accno_description, 1705 l1.description AS employee_accno_translation, 1706 c2.accno AS employer_accno, 1707 c2.description AS employer_accno_description, 1708 l2.description AS employer_accno_translation, 1709 b.description AS basedesc 1710 FROM deduction d 1711 LEFT JOIN chart c1 ON (c1.id = d.employee_accno_id) 1712 LEFT JOIN translation l1 ON (l1.trans_id = c1.id AND l1.language_code = '$myconfig->{countrycode}') 1713 LEFT JOIN chart c2 ON (c2.id = d.employer_accno_id) 1714 LEFT JOIN translation l2 ON (l2.trans_id = c2.id AND l2.language_code = '$myconfig->{countrycode}') 1715 LEFT JOIN deduction b ON (d.basedon = b.id) 1716 WHERE d.id = $form->{id}|; 1717 $sth = $dbh->prepare($query); 1718 $sth->execute || $form->dberror($query); 1719 1720 $ref = $sth->fetchrow_hashref(NAME_lc); 1721 for (qw(employee employer)) { $ref->{"${_}_accno_description"} = $ref->{"${_}_translation"} if $ref->{"${_}_translation"} } 1722 for (keys %$ref) { $form->{$_} = $ref->{$_} } 1723 1724 $sth->finish; 1725 1726 # check if orphaned 1727 $form->{status} = ""; 1728 $query = qq|SELECT count(*) FROM employeededuction 1729 WHERE deduction_id = $form->{id}|; 1730 if (! $dbh->selectrow_array($query)) { 1731 $form->{status} = 'orphaned'; 1732 } 1733 1734 # get the rates 1735 $query = qq|SELECT * 1736 FROM deductionrate 1737 WHERE trans_id = $form->{id} 1738 ORDER BY rn|; 1739 $sth = $dbh->prepare($query); 1740 $sth->execute || $form->dberror($query); 1741 1742 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1743 push @{ $form->{deductionrate} }, $ref; 1744 } 1745 $sth->finish; 1746 1747 $query = qq|SELECT d.id, d.description, da.withholding, da.percent 1748 FROM deduction d 1749 JOIN deduct da ON (da.deduction_id = d.id) 1750 WHERE da.trans_id = $form->{id}|; 1751 $sth = $dbh->prepare($query); 1752 $sth->execute || $form->dberror($query); 1753 1754 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1755 push @{ $form->{deduct} }, $ref; 1756 } 1757 $sth->finish; 1758 1759 # build selection list for basedon 1760 $query = qq|SELECT id, description 1761 FROM deduction 1762 WHERE id != $form->{id} 1763 ORDER BY 2|; 1764 1765 $form->create_lock($myconfig, $dbh, $form->{id}, 'hr'); 1766 1767 } else { 1768 # build selection list for basedon 1769 $query = qq|SELECT id, description 1770 FROM deduction 1771 ORDER BY 2|; 1772 } 1773 1774 $sth = $dbh->prepare($query); 1775 $sth->execute || $form->dberror($query); 1776 1777 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1778 push @{ $form->{all_deduction} }, $ref; 1779 } 1780 $sth->finish; 1781 1782 $query = qq|SELECT c.accno, c.description, 1783 l.description AS translation 1784 FROM chart c 1785 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 1786 WHERE c.charttype = 'A' 1787 AND c.link LIKE '%AP_amount%' 1788 ORDER BY c.accno|; 1789 $sth = $dbh->prepare($query); 1790 $sth->execute || $form->dberror($query); 1791 1792 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 1793 $ref->{description} = $ref->{translation} if $ref->{translation}; 1794 push @{ $form->{accounts} }, $ref; 1795 } 1796 $sth->finish; 1797 1798 $dbh->disconnect; 1799 1800} 1801 1802 1803sub deductions { 1804 my ($self, $myconfig, $form) = @_; 1805 1806 my $dbh = $form->dbconnect($myconfig); 1807 1808 my %defaults = $form->get_defaults($dbh, \@{['precision', 'company']}); 1809 for (keys %defaults) { $form->{$_} = $defaults{$_} } 1810 1811 my $query = qq|SELECT d.id, d.description, d.employeepays, d.employerpays, 1812 b.description AS basedon, 1813 c1.accno AS employee_accno, 1814 c2.accno AS employer_accno, 1815 dr.* 1816 FROM deduction d 1817 LEFT JOIN deduction b ON (b.id = d.basedon) 1818 LEFT JOIN deductionrate dr ON (dr.trans_id = d.id) 1819 LEFT JOIN chart c1 ON (d.employee_accno_id = c1.id) 1820 LEFT JOIN chart c2 ON (d.employer_accno_id = c2.id) 1821 ORDER BY 2, dr.rn|; 1822 my $sth = $dbh->prepare($query); 1823 $sth->execute || $form->dberror($query); 1824 1825 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { 1826 push @{ $form->{all_deduction} }, $ref; 1827 } 1828 1829 $sth->finish; 1830 $dbh->disconnect; 1831 1832} 1833 1834 1835sub save_deduction { 1836 my ($self, $myconfig, $form) = @_; 1837 1838 # connect to database 1839 my $dbh = $form->dbconnect_noauto($myconfig); 1840 1841 $form->{id} *= 1; 1842 1843 my $deduction_id; 1844 my $query; 1845 my $sth; 1846 1847 (undef, $form->{basedon}) = split /--/, $form->{basedon}; 1848 1849 if (! $form->{id}) { 1850 my $uid = localtime; 1851 $uid .= $$; 1852 1853 $query = qq|INSERT INTO deduction (description) 1854 VALUES ('$uid')|; 1855 $dbh->do($query) || $form->dberror($query); 1856 1857 $query = qq|SELECT id FROM deduction 1858 WHERE description = '$uid'|; 1859 $sth = $dbh->prepare($query); 1860 $sth->execute || $form->dberror($query); 1861 1862 ($form->{id}) = $sth->fetchrow_array; 1863 $sth->finish; 1864 } 1865 1866 1867 for (qw(employee employer)) { 1868 ($form->{"${_}_accno"}) = split /--/, $form->{"${_}_accno"}; 1869 $form->{"${_}pays"} = $form->parse_amount($myconfig, $form->{"${_}pays"}); 1870 } 1871 1872 $query = qq|UPDATE deduction SET 1873 description = |.$dbh->quote($form->{description}).qq|, 1874 employee_accno_id = 1875 (SELECT id FROM chart 1876 WHERE accno = '$form->{employee_accno}'), 1877 employer_accno_id = 1878 (SELECT id FROM chart 1879 WHERE accno = '$form->{employer_accno}'), 1880 employerpays = '$form->{employerpays}', 1881 employeepays = '$form->{employeepays}', 1882 fromage = |.$form->dbquote($form->{fromage}, SQL_INT).qq|, 1883 toage = |.$form->dbquote($form->{toage}, SQL_INT).qq|, 1884 basedon = |.$dbh->quote($form->{basedon}).qq|, 1885 agedob = |.$dbh->quote($form->{agedob}).qq| 1886 WHERE id = $form->{id}|; 1887 $dbh->do($query) || $form->dberror($query); 1888 1889 1890 $query = qq|DELETE FROM deductionrate 1891 WHERE trans_id = $form->{id}|; 1892 $dbh->do($query) || $form->dberror($query); 1893 1894 $query = qq|INSERT INTO deductionrate 1895 (rn, trans_id, rate, amount, above, below) VALUES (?,?,?,?,?,?)|; 1896 $sth = $dbh->prepare($query) || $form->dberror($query); 1897 1898 for ($i = 1; $i <= $form->{rate_rows}; $i++) { 1899 for (qw(rate amount above below)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } 1900 $form->{"rate_$i"} /= 100; 1901 1902 if ($form->{"rate_$i"} + $form->{"amount_$i"} + $form->{"above_$i"} + $form->{"below_$i"}) { 1903 $sth->execute($i, $form->{id}, $form->{"rate_$i"}, $form->{"amount_$i"}, $form->{"above_$i"}, $form->{"below_$i"}) || $form->dberror($query); 1904 } 1905 } 1906 $sth->finish; 1907 1908 $query = qq|DELETE FROM deduct 1909 WHERE trans_id = $form->{id}|; 1910 $dbh->do($query) || $form->dberror($query); 1911 1912 $query = qq|INSERT INTO deduct 1913 (trans_id, deduction_id, withholding, percent) VALUES (?,?,?,?)|; 1914 $sth = $dbh->prepare($query) || $form->dberror($query); 1915 1916 for ($i = 1; $i <= $form->{deduct_rows}; $i++) { 1917 (undef, $deduction_id) = split /--/, $form->{"deduct_$i"}; 1918 if ($deduction_id) { 1919 $form->{"percent_$i"} = $form->parse_amount($myconfig, $form->{"percent_$i"}); 1920 $form->{"percent_$i"} /= 100; 1921 1922 $sth->execute($form->{id}, $deduction_id, $form->{"withholding_$i"}, $form->{"percent_$i"}) || $form->dberror($query); 1923 } 1924 } 1925 $sth->finish; 1926 1927 $form->remove_locks($myconfig, $dbh, 'hr'); 1928 1929 $dbh->commit; 1930 $dbh->disconnect; 1931 1932} 1933 1934 1935sub delete_deduction { 1936 my ($self, $myconfig, $form) = @_; 1937 1938 # connect to database 1939 my $dbh = $form->dbconnect_noauto($myconfig); 1940 1941 $form->{id} *= 1; 1942 1943 # delete deduction 1944 my $query = qq|DELETE FROM deduction 1945 WHERE id = $form->{id}|; 1946 $dbh->do($query) || $form->dberror($query); 1947 1948 $query = qq|DELETE FROM deductionrate 1949 WHERE trans_id = $form->{id}|; 1950 $dbh->do($query) || $form->dberror($query); 1951 1952 $query = qq|DELETE FROM deduct 1953 WHERE trans_id = $form->{id}|; 1954 $dbh->do($query) || $form->dberror($query); 1955 1956 $form->remove_locks($myconfig, $dbh, 'hr'); 1957 1958 $dbh->commit; 1959 $dbh->disconnect; 1960 1961} 1962 1963 1964sub get_wage { 1965 my ($self, $myconfig, $form) = @_; 1966 1967 my $dbh = $form->dbconnect($myconfig); 1968 my $query; 1969 my $sth; 1970 my $ref; 1971 1972 $form->remove_locks($myconfig, $dbh, 'hr'); 1973 1974 my %defaults = $form->get_defaults($dbh, \@{['precision', 'company']}); 1975 for (keys %defaults) { $form->{$_} = $defaults{$_} } 1976 1977 if ($form->{id} *= 1) { 1978 $query = qq|SELECT w.*, 1979 c.accno, c.description AS accno_description, 1980 l.description AS accno_translation, 1981 c1.accno AS defer, c1.description AS defer_description, 1982 l1.description AS defer_translation 1983 FROM wage w 1984 LEFT JOIN chart c ON (c.id = w.chart_id) 1985 LEFT JOIN chart c1 ON (c1.id = w.defer) 1986 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 1987 LEFT JOIN translation l1 ON (l1.trans_id = c1.id AND l1.language_code = '$myconfig->{countrycode}') 1988 WHERE w.id = $form->{id}|; 1989 $sth = $dbh->prepare($query); 1990 $sth->execute || $form->dberror($query); 1991 1992 $ref = $sth->fetchrow_hashref(NAME_lc); 1993 $ref->{"accno_description"} = $ref->{"accno_translation"} if $ref->{"accno_translation"}; 1994 $ref->{"defer_description"} = $ref->{"defer_translation"} if $ref->{"defer_translation"}; 1995 for (keys %$ref) { $form->{$_} = $ref->{$_} } 1996 1997 $sth->finish; 1998 1999 # check if orphaned 2000 $form->{status} = ""; 2001 $query = qq|SELECT count(*) FROM employeewage 2002 WHERE wage_id = $form->{id}|; 2003 if (! $dbh->selectrow_array($query)) { 2004 $form->{status} = 'orphaned'; 2005 } 2006 2007 $form->create_lock($myconfig, $dbh, $form->{id}, 'hr'); 2008 2009 } 2010 2011 $query = qq|SELECT c.accno, c.description, 2012 l.description AS translation 2013 FROM chart c 2014 LEFT JOIN translation l ON (l.trans_id = c.id AND l.language_code = '$myconfig->{countrycode}') 2015 WHERE c.charttype = 'A' 2016 AND c.link LIKE '%AP_amount%' 2017 ORDER BY c.accno|; 2018 $sth = $dbh->prepare($query); 2019 $sth->execute || $form->dberror($query); 2020 2021 while ($ref = $sth->fetchrow_hashref(NAME_lc)) { 2022 $ref->{description} = $ref->{translation} if $ref->{translation}; 2023 push @{ $form->{accounts} }, $ref; 2024 } 2025 $sth->finish; 2026 2027 $dbh->disconnect; 2028 2029} 2030 2031 2032sub wages { 2033 my ($self, $myconfig, $form) = @_; 2034 2035 my $dbh = $form->dbconnect($myconfig); 2036 2037 my %defaults = $form->get_defaults($dbh, \@{['precision', 'company']}); 2038 for (keys %defaults) { $form->{$_} = $defaults{$_} } 2039 2040 my $query = qq|SELECT w.*, 2041 c.accno, c1.accno AS defer 2042 FROM wage w 2043 JOIN chart c ON (w.chart_id = c.id) 2044 LEFT JOIN chart c1 ON (c1.id = w.defer) 2045 ORDER BY 2|; 2046 my $sth = $dbh->prepare($query); 2047 $sth->execute || $form->dberror($query); 2048 2049 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { 2050 push @{ $form->{all_wage} }, $ref; 2051 } 2052 2053 $sth->finish; 2054 $dbh->disconnect; 2055 2056} 2057 2058 2059sub save_wage { 2060 my ($self, $myconfig, $form) = @_; 2061 2062 # connect to database 2063 my $dbh = $form->dbconnect_noauto($myconfig); 2064 2065 my $query; 2066 my $sth; 2067 2068 if (! $form->{id}) { 2069 my $uid = localtime; 2070 $uid .= $$; 2071 2072 $query = qq|INSERT INTO wage (description) 2073 VALUES ('$uid')|; 2074 $dbh->do($query) || $form->dberror($query); 2075 2076 $query = qq|SELECT id FROM wage 2077 WHERE description = '$uid'|; 2078 $sth = $dbh->prepare($query); 2079 $sth->execute || $form->dberror($query); 2080 2081 ($form->{id}) = $sth->fetchrow_array; 2082 $sth->finish; 2083 } 2084 2085 $form->{exempt} *= 1; 2086 $form->{amount} = $form->parse_amount($myconfig, $form->{amount}); 2087 ($form->{accno}) = split /--/, $form->{accno}; 2088 ($form->{defer}) = split /--/, $form->{defer}; 2089 2090 $query = qq|UPDATE wage SET 2091 description = |.$dbh->quote($form->{description}).qq|, 2092 defer = 2093 (SELECT id FROM chart 2094 WHERE accno = '$form->{defer}'), 2095 exempt = '$form->{exempt}', 2096 chart_id = 2097 (SELECT id FROM chart 2098 WHERE accno = '$form->{accno}'), 2099 amount = $form->{amount} 2100 WHERE id = $form->{id}|; 2101 $dbh->do($query) || $form->dberror($query); 2102 2103 $form->remove_locks($myconfig, $dbh, 'hr'); 2104 2105 $dbh->commit; 2106 $dbh->disconnect; 2107 2108} 2109 2110 2111sub delete_wage { 2112 my ($self, $myconfig, $form) = @_; 2113 2114 # connect to database 2115 my $dbh = $form->dbconnect_noauto($myconfig); 2116 2117 $form->{id} *= 1; 2118 2119 # delete deduction 2120 my $query = qq|DELETE FROM wage 2121 WHERE id = $form->{id}|; 2122 $dbh->do($query) || $form->dberror($query); 2123 2124 $form->remove_locks($myconfig, $dbh, 'hr'); 2125 2126 $dbh->commit; 2127 $dbh->disconnect; 2128 2129} 2130 2131 21321; 2133 2134