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