1 #=====================================================================
2 # SQL-Ledger Accounting
5 # Author: Dieter Simader
6 # Email: dsimader@sql-ledger.org
7 # Web: http://www.sql-ledger.org
11 # This program is free software; you can redistribute it and/or modify
12 # it under the terms of the GNU General Public License as published by
13 # the Free Software Foundation; either version 2 of the License, or
14 # (at your option) any later version.
16 # This program is distributed in the hope that it will be useful,
17 # but WITHOUT ANY WARRANTY; without even the implied warranty of
18 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 # GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License
21 # along with this program; if not, write to the Free Software
22 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
23 #======================================================================
25 # backend code for human resources and payroll
27 #======================================================================
33 my ($self, $myconfig, $form) = @_;
35 my $dbh = $form->dbconnect($myconfig);
43 $query = qq|SELECT e.*
45 WHERE e.id = $form->{id}|;
46 $sth = $dbh->prepare($query);
47 $sth->execute || $form->dberror($query);
49 $ref = $sth->fetchrow_hashref(NAME_lc);
51 # check if employee can be deleted, orphaned
52 $form->{status} = "orphaned" unless $ref->{login};
54 $form->{status} = 'orphaned'; # leave orphaned for now until payroll is done
56 $ref->{employeelogin} = $ref->{login};
58 map { $form->{$_} = $ref->{$_} } keys %$ref;
63 $form->{managerid} *= 1;
64 $query = qq|SELECT name
66 WHERE id = $form->{managerid}|;
67 ($form->{manager}) = $dbh->selectrow_array($query);
70 ######### disabled for now
71 if ($form->{deductions}) {
73 $query = qq|SELECT d.id, d.description, da.before, da.after, da.rate
74 FROM employeededuction da
75 JOIN deduction d ON (da.deduction_id = d.id)
76 WHERE da.employee_id = $form->{id}|;
77 $sth = $dbh->prepare($query);
78 $sth->execute || $form->dberror($query);
80 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
82 push @{ $form->{all_employeededuction} }, $ref;
87 $notid = qq|AND id != $form->{id}|;
93 $query = qq|SELECT id, name
99 $sth = $dbh->prepare($query);
100 $sth->execute || $form->dberror($query);
102 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
103 push @{ $form->{all_manager} }, $ref;
109 if ($form->{deductions}) {
110 $query = qq|SELECT id, description
113 $sth = $dbh->prepare($query);
114 $sth->execute || $form->dberror($query);
116 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
117 push @{ $form->{all_deduction} }, $ref;
129 my ($self, $myconfig, $form) = @_;
131 # connect to database
132 my $dbh = $form->dbconnect_noauto($myconfig);
138 $uid .= $form->{login};
140 $query = qq|INSERT INTO employee (name)
142 $dbh->do($query) || $form->dberror($query);
144 $query = qq|SELECT id FROM employee
145 WHERE name = '$uid'|;
146 $sth = $dbh->prepare($query);
147 $sth->execute || $form->dberror($query);
149 ($form->{id}) = $sth->fetchrow_array;
153 my ($null, $managerid) = split /--/, $form->{manager};
157 $form->{employeenumber} = $form->update_defaults($myconfig, "employeenumber", $dbh) if ! $form->{employeenumber};
159 $query = qq|UPDATE employee SET
160 employeenumber = |.$dbh->quote($form->{employeenumber}).qq|,
161 name = |.$dbh->quote($form->{name}).qq|,
162 address1 = |.$dbh->quote($form->{address1}).qq|,
163 address2 = |.$dbh->quote($form->{address2}).qq|,
164 city = |.$dbh->quote($form->{city}).qq|,
165 state = |.$dbh->quote($form->{state}).qq|,
166 zipcode = |.$dbh->quote($form->{zipcode}).qq|,
167 country = |.$dbh->quote($form->{country}).qq|,
168 workphone = '$form->{workphone}',
169 homephone = '$form->{homephone}',
170 startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|,
171 enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|,
172 notes = |.$dbh->quote($form->{notes}).qq|,
173 role = '$form->{role}',
174 sales = '$form->{sales}',
175 email = |.$dbh->quote($form->{email}).qq|,
176 ssn = '$form->{ssn}',
177 dob = |.$form->dbquote($form->{dob}, SQL_DATE).qq|,
178 iban = '$form->{iban}',
179 bic = '$form->{bic}',
180 managerid = $managerid
181 WHERE id = $form->{id}|;
182 $dbh->do($query) || $form->dberror($query);
185 if ($form->{selectdeduction}) {
186 # insert deduction and allowances for payroll
187 $query = qq|DELETE FROM employeededuction
188 WHERE employee_id = $form->{id}|;
189 $dbh->do($query) || $form->dberror($query);
191 $query = qq|INSERT INTO employeededuction (employee_id, deduction_id,
192 before, after, rate) VALUES ($form->{id},?,?,?,?)|;
193 my $sth = $dbh->prepare($query) || $form->dberror($query);
195 for ($i = 1; $i <= $form->{deduction_rows}; $i++) {
196 map { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(before after);
197 ($null, $deduction_id) = split /--/, $form->{"deduction_$i"};
199 $sth->execute($deduction_id, $form->{"before_$i"}, $form->{"after_$i"}, $form->{"rate_$i"} / 100) || $form->dberror($query);
211 sub delete_employee {
212 my ($self, $myconfig, $form) = @_;
214 # connect to database
215 my $dbh = $form->dbconnect_noauto($myconfig);
218 my $query = qq|DELETE FROM $form->{db}
219 WHERE id = $form->{id}|;
220 $dbh->do($query) || $form->dberror($query);
229 my ($self, $myconfig, $form) = @_;
231 # connect to database
232 my $dbh = $form->dbconnect($myconfig);
235 $form->{sort} = ($form->{sort}) ? $form->{sort} : "name";
237 my $sortorder = $form->sort_order(\@a);
241 if ($form->{startdate}) {
242 $where .= " AND e.startdate >= '$startdate'";
244 if ($form->{enddate}) {
245 $where .= " AND e.enddate >= '$enddate'";
248 $var = $form->like(lc $form->{name});
249 $where .= " AND lower(e.name) LIKE '$var'";
251 if ($form->{notes}) {
252 $var = $form->like(lc $form->{notes});
253 $where .= " AND lower(e.notes) LIKE '$var'";
255 if ($form->{status} eq 'sales') {
256 $where .= " AND e.sales = '1'";
258 if ($form->{status} eq 'orphaned') {
259 $where .= qq| AND e.login IS NULL|;
262 my $query = qq|SELECT e.*, m.name AS manager
264 LEFT JOIN employee m ON (m.id = e.managerid)
266 ORDER BY $sortorder|;
268 my $sth = $dbh->prepare($query);
269 $sth->execute || $form->dberror($query);
271 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
272 $ref->{address} = "";
273 map { $ref->{address} .= "$ref->{$_} "; } qw(address1 address2 city state zipcode country);
274 push @{ $form->{all_employee} }, $ref;
284 my ($self, $myconfig, $form) = @_;
286 my $dbh = $form->dbconnect($myconfig);
294 $query = qq|SELECT d.*,
295 c1.accno AS ap_accno,
296 c1.description AS ap_description,
297 c2.accno AS expense_accno,
298 c2.description AS expense_description
300 LEFT JOIN chart c1 ON (c1.id = d.ap_accno_id)
301 LEFT JOIN chart c2 ON (c2.id = d.expense_accno_id)
302 WHERE d.id = $form->{id}|;
303 $sth = $dbh->prepare($query);
304 $sth->execute || $form->dberror($query);
306 $ref = $sth->fetchrow_hashref(NAME_lc);
307 map { $form->{$_} = $ref->{$_} } keys %$ref;
312 $form->{status} = 'orphaned'; # for now
316 $query = qq|SELECT rate, amount, above, below
318 WHERE trans_id = $form->{id}
319 ORDER BY rate, amount|;
320 $sth = $dbh->prepare($query);
321 $sth->execute || $form->dberror($query);
323 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
324 push @{ $form->{deductionrate} }, $ref;
328 # get all for deductionbase
329 $query = qq|SELECT d.description, d.id, db.maximum
330 FROM deductionbase db
331 JOIN deduction d ON (d.id = db.deduction_id)
332 WHERE db.trans_id = $form->{id}|;
333 $sth = $dbh->prepare($query);
334 $sth->execute || $form->dberror($query);
336 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
337 push @{ $form->{deductionbase} }, $ref;
341 # get all for deductionafter
342 $query = qq|SELECT d.description, d.id
343 FROM deductionafter da
344 JOIN deduction d ON (d.id = da.deduction_id)
345 WHERE da.trans_id = $form->{id}|;
346 $sth = $dbh->prepare($query);
347 $sth->execute || $form->dberror($query);
349 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
350 push @{ $form->{deductionafter} }, $ref;
354 # build selection list for base and after
355 $query = qq|SELECT id, description
357 WHERE id != $form->{id}
361 # build selection list for base and after
362 $query = qq|SELECT id, description
367 $sth = $dbh->prepare($query);
368 $sth->execute || $form->dberror($query);
370 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
371 push @{ $form->{all_deduction} }, $ref;
376 my %category = ( ap => 'L',
379 foreach $item (keys %category) {
380 $query = qq|SELECT accno, description
382 WHERE charttype = 'A'
383 AND category = '$category{$item}'
385 $sth = $dbh->prepare($query);
386 $sth->execute || $form->dberror($query);
388 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
389 push @{ $form->{"${item}_accounts"} }, $ref;
401 my ($self, $myconfig, $form) = @_;
403 my $dbh = $form->dbconnect($myconfig);
405 my $query = qq|SELECT d.id, d.description, d.employeepays, d.employerpays,
406 c1.accno AS ap_accno, c2.accno AS expense_accno,
407 dr.rate, dr.amount, dr.above, dr.below
409 JOIN deductionrate dr ON (dr.trans_id = d.id)
410 LEFT JOIN chart c1 ON (d.ap_accno_id = c1.id)
411 LEFT JOIN chart c2 ON (d.expense_accno_id = c2.id)
413 my $sth = $dbh->prepare($query);
414 $sth->execute || $form->dberror($query);
416 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
417 push @{ $form->{all_deduction} }, $ref;
427 my ($self, $myconfig, $form) = @_;
429 # connect to database
430 my $dbh = $form->dbconnect_noauto($myconfig);
432 ($form->{ap_accno}) = split /--/, $form->{ap_accno};
433 ($form->{expense_accno}) = split /--/, $form->{expense_accno};
442 $uid .= $form->{login};
444 $query = qq|INSERT INTO deduction (description)
446 $dbh->do($query) || $form->dberror($query);
448 $query = qq|SELECT id FROM deduction
449 WHERE description = '$uid'|;
450 $sth = $dbh->prepare($query);
451 $sth->execute || $form->dberror($query);
453 ($form->{id}) = $sth->fetchrow_array;
458 map { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) } qw(employeepays employerpays);
460 $query = qq|UPDATE deduction SET
461 description = |.$dbh->quote($form->{description}).qq|,
463 (SELECT id FROM chart
464 WHERE accno = '$form->{ap_accno}'),
466 (SELECT id FROM chart
467 WHERE accno = '$form->{expense_accno}'),
468 employerpays = '$form->{employerpays}',
469 employeepays = '$form->{employeepays}',
470 fromage = |.$form->dbquote($form->{fromage}, SQL_INT).qq|,
471 toage = |.$form->dbquote($form->{toage}, SQL_INT).qq|
472 WHERE id = $form->{id}|;
473 $dbh->do($query) || $form->dberror($query);
476 $query = qq|DELETE FROM deductionrate
477 WHERE trans_id = $form->{id}|;
478 $dbh->do($query) || $form->dberror($query);
480 $query = qq|INSERT INTO deductionrate
481 (trans_id, rate, amount, above, below) VALUES (?,?,?,?,?)|;
482 $sth = $dbh->prepare($query) || $form->dberror($query);
484 for ($i = 1; $i <= $form->{rate_rows}; $i++) {
485 map { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(rate amount above below);
486 $form->{"rate_$i"} /= 100;
488 if ($form->{"rate_$i"} || $form->{"amount_$i"}) {
489 $sth->execute($form->{id}, $form->{"rate_$i"}, $form->{"amount_$i"}, $form->{"above_$i"}, $form->{"below_$i"}) || $form->dberror($query);
495 $query = qq|DELETE FROM deductionbase
496 WHERE trans_id = $form->{id}|;
497 $dbh->do($query) || $form->dberror($query);
499 $query = qq|INSERT INTO deductionbase
500 (trans_id, deduction_id, maximum) VALUES (?,?,?)|;
501 $sth = $dbh->prepare($query) || $form->dberror($query);
503 for ($i = 1; $i <= $form->{base_rows}; $i++) {
504 ($null, $deduction_id) = split /--/, $form->{"base_$i"};
505 $form->{"maximum_$i"} = $form->parse_amount($myconfig, $form->{"maximum_$i"});
507 $sth->execute($form->{id}, $deduction_id, $form->{"maximum_$i"}) || $form->dberror($query);
513 $query = qq|DELETE FROM deductionafter
514 WHERE trans_id = $form->{id}|;
515 $dbh->do($query) || $form->dberror($query);
517 $query = qq|INSERT INTO deductionafter
518 (trans_id, deduction_id) VALUES (?,?)|;
519 $sth = $dbh->prepare($query) || $form->dberror($query);
521 for ($i = 1; $i <= $form->{after_rows}; $i++) {
522 ($null, $deduction_id) = split /--/, $form->{"after_$i"};
524 $sth->execute($form->{id}, $deduction_id) || $form->dberror($query);
535 sub delete_deduction {
536 my ($self, $myconfig, $form) = @_;
538 # connect to database
539 my $dbh = $form->dbconnect_noauto($myconfig);
542 my $query = qq|DELETE FROM $form->{db}
543 WHERE id = $form->{id}|;
544 $dbh->do($query) || $form->dberror($query);
546 foreach $item (qw(rate base after)) {
547 $query = qq|DELETE FROM deduction$item
548 WHERE trans_id = $form->{id}|;
549 $dbh->do($query) || $form->dberror($query);