summaryrefslogtreecommitdiff
path: root/LedgerSMB/HR.pm
diff options
context:
space:
mode:
Diffstat (limited to 'LedgerSMB/HR.pm')
-rwxr-xr-xLedgerSMB/HR.pm655
1 files changed, 172 insertions, 483 deletions
diff --git a/LedgerSMB/HR.pm b/LedgerSMB/HR.pm
index ee5a2c32..91c1a060 100755
--- a/LedgerSMB/HR.pm
+++ b/LedgerSMB/HR.pm
@@ -35,539 +35,228 @@ package HR;
sub get_employee {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->dbconnect($myconfig);
- my $query;
- my $sth;
- my $ref;
- my $notid = "";
+ my $query;
+ my $sth;
+ my $ref;
+ my $notid = "";
- if ($form->{id}) {
- $query = qq|SELECT e.*
- FROM employee e
- WHERE e.id = $form->{id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ if ($form->{id}) {
+ $query = qq|SELECT e.* FROM employee e WHERE e.id = ?|;
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
- $ref = $sth->fetchrow_hashref(NAME_lc);
+ $ref = $sth->fetchrow_hashref(NAME_lc);
- # check if employee can be deleted, orphaned
- $form->{status} = "orphaned" unless $ref->{login};
+ # check if employee can be deleted, orphaned
+ $form->{status} = "orphaned" unless $ref->{login};
-$form->{status} = 'orphaned'; # leave orphaned for now until payroll is done
- $ref->{employeelogin} = $ref->{login};
- delete $ref->{login};
- for (keys %$ref) { $form->{$_} = $ref->{$_} }
+ $ref->{employeelogin} = $ref->{login};
+ delete $ref->{login};
+ for (keys %$ref) { $form->{$_} = $ref->{$_} }
- $sth->finish;
+ $sth->finish;
- # get manager
- $form->{managerid} *= 1;
- $query = qq|SELECT name
- FROM employee
- WHERE id = $form->{managerid}|;
- ($form->{manager}) = $dbh->selectrow_array($query);
+ # get manager
+ $form->{managerid} *= 1;
+ $query = qq|SELECT name FROM employee WHERE id = ?|;
+
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{managerid});
+ ($form->{manager}) = $sth->fetchrow_array($query);
-######### disabled for now
-if ($form->{deductions}) {
- # get allowances
- $query = qq|SELECT d.id, d.description, da.before, da.after, da.rate
- FROM employeededuction da
- JOIN deduction d ON (da.deduction_id = d.id)
- WHERE da.employee_id = $form->{id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- $ref->{rate} *= 100;
- push @{ $form->{all_employeededuction} }, $ref;
- }
- $sth->finish;
-}
-
- $notid = qq|AND id != $form->{id}|;
+ $notid = qq|AND id != |.$dbh->quote($form->{id});
- } else {
+ } else {
- $query = qq|SELECT current_date|;
- ($form->{startdate}) = $dbh->selectrow_array($query);
+ $query = qq|SELECT current_date|;
+ ($form->{startdate}) = $dbh->selectrow_array($query);
- }
+ }
- # get managers
- $query = qq|SELECT id, name
- FROM employee
- WHERE sales = '1'
- AND role = 'manager'
- $notid
- ORDER BY 2|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{all_manager} }, $ref;
- }
- $sth->finish;
-
-
- # get deductions
-if ($form->{deductions}) {
- $query = qq|SELECT id, description
- FROM deduction
- ORDER BY 2|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{all_deduction} }, $ref;
- }
- $sth->finish;
-}
+ # get managers
+ $query = qq|
+ SELECT id, name
+ FROM employee
+ WHERE sales = '1'
+ AND role = 'manager'
+ $notid
+ ORDER BY 2|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{all_manager} }, $ref;
+ }
+ $sth->finish;
- $dbh->disconnect;
+
+ $dbh->commit;
}
sub save_employee {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect_noauto($myconfig);
- my $query;
- my $sth;
+ my $dbh = $form->{dbh};
+ my $query;
+ my $sth;
- if (! $form->{id}) {
- my $uid = localtime;
- $uid .= "$$";
+ if (! $form->{id}) {
+ my $uid = localtime;
+ $uid .= "$$";
- $query = qq|INSERT INTO employee (name)
- VALUES ('$uid')|;
- $dbh->do($query) || $form->dberror($query);
+ $query = qq|INSERT INTO employee (name) VALUES ('$uid')|;
+ $dbh->do($query) || $form->dberror($query);
- $query = qq|SELECT id FROM employee
- WHERE name = '$uid'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- ($form->{id}) = $sth->fetchrow_array;
- $sth->finish;
- }
-
- my ($null, $managerid) = split /--/, $form->{manager};
- $managerid *= 1;
- $form->{sales} *= 1;
-
- $form->{employeenumber} = $form->update_defaults($myconfig, "employeenumber", $dbh) if ! $form->{employeenumber};
-
- $query = qq|UPDATE employee SET
- employeenumber = |.$dbh->quote($form->{employeenumber}).qq|,
- name = |.$dbh->quote($form->{name}).qq|,
- address1 = |.$dbh->quote($form->{address1}).qq|,
- address2 = |.$dbh->quote($form->{address2}).qq|,
- city = |.$dbh->quote($form->{city}).qq|,
- state = |.$dbh->quote($form->{state}).qq|,
- zipcode = |.$dbh->quote($form->{zipcode}).qq|,
- country = |.$dbh->quote($form->{country}).qq|,
- workphone = '$form->{workphone}',
- homephone = '$form->{homephone}',
- startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|,
- enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|,
- notes = |.$dbh->quote($form->{notes}).qq|,
- role = '$form->{role}',
- sales = '$form->{sales}',
- email = |.$dbh->quote($form->{email}).qq|,
- ssn = '$form->{ssn}',
- dob = |.$form->dbquote($form->{dob}, SQL_DATE).qq|,
- iban = '$form->{iban}',
- bic = '$form->{bic}',
- managerid = $managerid
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
-# for now
-if ($form->{selectdeduction}) {
- # insert deduction and allowances for payroll
- $query = qq|DELETE FROM employeededuction
- WHERE employee_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- $query = qq|INSERT INTO employeededuction (employee_id, deduction_id,
- before, after, rate) VALUES ($form->{id},?,?,?,?)|;
- my $sth = $dbh->prepare($query) || $form->dberror($query);
-
- for ($i = 1; $i <= $form->{deduction_rows}; $i++) {
- for (qw(before after)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) }
- ($null, $deduction_id) = split /--/, $form->{"deduction_$i"};
- if ($deduction_id) {
- $sth->execute($deduction_id, $form->{"before_$i"}, $form->{"after_$i"}, $form->{"rate_$i"} / 100) || $form->dberror($query);
- }
- }
- $sth->finish;
-}
-
- $dbh->commit;
- $dbh->disconnect;
+ $query = qq|SELECT id FROM employee WHERE name = '$uid'|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ ($form->{id}) = $sth->fetchrow_array;
+ $sth->finish;
+ }
+
+ my ($null, $managerid) = split /--/, $form->{manager};
+ $managerid *= 1;
+ $form->{sales} *= 1;
+
+ $form->{employeenumber} = $form->update_defaults(
+ $myconfig, "employeenumber", $dbh)
+ if ! $form->{employeenumber};
+
+ $query = qq|
+ UPDATE employee
+ SET employeenumber = ?,
+ name = ?,
+ address1 = ?,
+ address2 = ?,
+ city = ?,
+ state = ?,
+ zipcode = ?,
+ country = ?,
+ workphone = ?,
+ homephone = ?,
+ startdate = ?,
+ enddate = ?,
+ notes = ?,
+ role = ?,
+ sales = ?,
+ email = ?,
+ ssn = ?,
+ dob = ?,
+ iban = ?,
+ bic = ?,
+ managerid = ?
+ WHERE id = ?|;
+ $sth = $dbh->prepare($query);
+ $form->db_prepare_vars('startdate', 'enddate');
+ $sth->execute(
+ $form->{employeenumber}, $form->{name}, $form->{address1},
+ $form->{address2}, $form->{city}, $form->{state},
+ $form->{zipcode}, $form->{country}, $form->{workphone},
+ $form->{homephone}, $form->{startdate}, $form->{enddate},
+ $form->{notes}, $form->{role}, $form->{sales}, $form->{email},
+ $form->{ssn}, $form->{dob}, $form->{iban}, $form->{bic},
+ $managerid, $form->{id}
+ ) || $form->dberror($query);
+
+
+ $dbh->commit;
}
sub delete_employee {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect_noauto($myconfig);
+ # connect to database
+ my $dbh = $form->dbconnect_noauto($myconfig);
- # delete employee
- my $query = qq|DELETE FROM $form->{db}
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ # delete employee
+
+ my $query = qq|
+ DELETE FROM employee
+ WHERE id = |.$dbh->quote($form->{id});
+ $dbh->do($query) || $form->dberror($query);
- $dbh->commit;
- $dbh->disconnect;
+ $dbh->commit;
}
sub employees {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
+ # connect to database
+ my $dbh = $form->{dbh};
- my $where = "1 = 1";
- $form->{sort} = ($form->{sort}) ? $form->{sort} : "name";
- my @a = qw(name);
- my $sortorder = $form->sort_order(\@a);
+ my $where = "1 = 1";
+ $form->{sort} = ($form->{sort}) ? $form->{sort} : "name";
+ my @a = qw(name);
+ my $sortorder = $form->sort_order(\@a);
- my $var;
+ my $var;
- if ($form->{startdatefrom}) {
- $where .= " AND e.startdate >= '$form->{startdatefrom}'";
- }
- if ($form->{startdateto}) {
- $where .= " AND e.startddate <= '$form->{startdateto}'";
- }
- if ($form->{name} ne "") {
- $var = $form->like(lc $form->{name});
- $where .= " AND lower(e.name) LIKE '$var'";
- }
- if ($form->{notes} ne "") {
- $var = $form->like(lc $form->{notes});
- $where .= " AND lower(e.notes) LIKE '$var'";
- }
- if ($form->{sales} eq 'Y') {
- $where .= " AND e.sales = '1'";
- }
- if ($form->{status} eq 'orphaned') {
- $where .= qq| AND e.login IS NULL|;
- }
- if ($form->{status} eq 'active') {
- $where .= qq| AND e.enddate IS NULL|;
- }
- if ($form->{status} eq 'inactive') {
- $where .= qq| AND e.enddate <= current_date|;
- }
-
- my $query = qq|SELECT e.*, m.name AS manager
- FROM employee e
- LEFT JOIN employee m ON (m.id = e.managerid)
- WHERE $where
+ if ($form->{startdatefrom}) {
+ $where .= " AND e.startdate >= ".
+ $dbh->quote($form->{startdatefrom});
+ }
+ if ($form->{startdateto}) {
+ $where .= " AND e.startddate <= ".
+ $dbh->quote($form->{startdateto});
+ }
+ if ($form->{name} ne "") {
+ $var = $dbh->quote($form->like(lc $form->{name}));
+ $where .= " AND lower(e.name) LIKE $var";
+ }
+ if ($form->{notes} ne "") {
+ $var = $dbh->quote($form->like(lc $form->{notes}));
+ $where .= " AND lower(e.notes) LIKE $var";
+ }
+ if ($form->{sales} eq 'Y') {
+ $where .= " AND e.sales = '1'";
+ }
+ if ($form->{status} eq 'orphaned') {
+ $where .= qq| AND e.login IS NULL|;
+ }
+ if ($form->{status} eq 'active') {
+ $where .= qq| AND e.enddate IS NULL|;
+ }
+ if ($form->{status} eq 'inactive') {
+ $where .= qq| AND e.enddate <= current_date|;
+ }
+
+ my $query = qq|
+ SELECT e.*, m.name AS manager
+ FROM employee e
+ LEFT JOIN employee m ON (m.id = e.managerid)
+ WHERE $where
ORDER BY $sortorder|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $ref->{address} = "";
- for (qw(address1 address2 city state zipcode country)) { $ref->{address} .= "$ref->{$_} " }
- push @{ $form->{all_employee} }, $ref;
- }
-
- $sth->finish;
- $dbh->disconnect;
-
-}
-
-
-sub get_deduction {
- my ($self, $myconfig, $form) = @_;
-
- my $dbh = $form->dbconnect($myconfig);
- my $query;
- my $sth;
- my $ref;
- my $item;
- my $i;
-
- if ($form->{id}) {
- $query = qq|SELECT d.*,
- c1.accno AS ap_accno,
- c1.description AS ap_description,
- c2.accno AS expense_accno,
- c2.description AS expense_description
- FROM deduction d
- LEFT JOIN chart c1 ON (c1.id = d.ap_accno_id)
- LEFT JOIN chart c2 ON (c2.id = d.expense_accno_id)
- WHERE d.id = $form->{id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
- for (keys %$ref) { $form->{$_} = $ref->{$_} }
-
- $sth->finish;
-
- # check if orphaned
-$form->{status} = 'orphaned'; # for now
-
-
- # get the rates
- $query = qq|SELECT rate, amount, above, below
- FROM deductionrate
- WHERE trans_id = $form->{id}
- ORDER BY rate, amount|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{deductionrate} }, $ref;
- }
- $sth->finish;
-
- # get all for deductionbase
- $query = qq|SELECT d.description, d.id, db.maximum
- FROM deductionbase db
- JOIN deduction d ON (d.id = db.deduction_id)
- WHERE db.trans_id = $form->{id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{deductionbase} }, $ref;
- }
- $sth->finish;
-
- # get all for deductionafter
- $query = qq|SELECT d.description, d.id
- FROM deductionafter da
- JOIN deduction d ON (d.id = da.deduction_id)
- WHERE da.trans_id = $form->{id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{deductionafter} }, $ref;
- }
- $sth->finish;
-
- # build selection list for base and after
- $query = qq|SELECT id, description
- FROM deduction
- WHERE id != $form->{id}
- ORDER BY 2|;
-
- } else {
- # build selection list for base and after
- $query = qq|SELECT id, description
- FROM deduction
- ORDER BY 2|;
- }
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{all_deduction} }, $ref;
- }
- $sth->finish;
-
-
- my %category = ( ap => 'L',
- expense => 'E' );
-
- foreach $item (keys %category) {
- $query = qq|SELECT accno, description
- FROM chart
- WHERE charttype = 'A'
- AND category = '$category{$item}'
- ORDER BY accno|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{"${item}_accounts"} }, $ref;
- }
- $sth->finish;
- }
-
-
- $dbh->disconnect;
-
-}
-
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
-sub deductions {
- my ($self, $myconfig, $form) = @_;
+ while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ $ref->{address} = "";
+ for (qw(address1 address2 city state zipcode country)) {
+ $ref->{address} .= "$ref->{$_} ";
+ }
+ push @{ $form->{all_employee} }, $ref;
+ }
- my $dbh = $form->dbconnect($myconfig);
-
- my $query = qq|SELECT d.id, d.description, d.employeepays, d.employerpays,
- c1.accno AS ap_accno, c2.accno AS expense_accno,
- dr.rate, dr.amount, dr.above, dr.below
- FROM deduction d
- JOIN deductionrate dr ON (dr.trans_id = d.id)
- LEFT JOIN chart c1 ON (d.ap_accno_id = c1.id)
- LEFT JOIN chart c2 ON (d.expense_accno_id = c2.id)
- ORDER BY 2, 7, 8|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{all_deduction} }, $ref;
- }
-
- $sth->finish;
- $dbh->disconnect;
+ $sth->finish;
+ $dbh->commit;
}
-sub save_deduction {
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect_noauto($myconfig);
-
- ($form->{ap_accno}) = split /--/, $form->{ap_accno};
- ($form->{expense_accno}) = split /--/, $form->{expense_accno};
-
- my $null;
- my $deduction_id;
- my $query;
- my $sth;
-
- if (! $form->{id}) {
- my $uid = localtime;
- $uid .= "$$";
-
- $query = qq|INSERT INTO deduction (description)
- VALUES ('$uid')|;
- $dbh->do($query) || $form->dberror($query);
-
- $query = qq|SELECT id FROM deduction
- WHERE description = '$uid'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- ($form->{id}) = $sth->fetchrow_array;
- $sth->finish;
- }
-
-
- for (qw(employeepays employerpays)) { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) }
-
- $query = qq|UPDATE deduction SET
- description = |.$dbh->quote($form->{description}).qq|,
- ap_accno_id =
- (SELECT id FROM chart
- WHERE accno = '$form->{ap_accno}'),
- expense_accno_id =
- (SELECT id FROM chart
- WHERE accno = '$form->{expense_accno}'),
- employerpays = '$form->{employerpays}',
- employeepays = '$form->{employeepays}',
- fromage = |.$form->dbquote($form->{fromage}, SQL_INT).qq|,
- toage = |.$form->dbquote($form->{toage}, SQL_INT).qq|
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
-
- $query = qq|DELETE FROM deductionrate
- WHERE trans_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- $query = qq|INSERT INTO deductionrate
- (trans_id, rate, amount, above, below) VALUES (?,?,?,?,?)|;
- $sth = $dbh->prepare($query) || $form->dberror($query);
-
- for ($i = 1; $i <= $form->{rate_rows}; $i++) {
- for (qw(rate amount above below)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) }
- $form->{"rate_$i"} /= 100;
-
- if ($form->{"rate_$i"} || $form->{"amount_$i"}) {
- $sth->execute($form->{id}, $form->{"rate_$i"}, $form->{"amount_$i"}, $form->{"above_$i"}, $form->{"below_$i"}) || $form->dberror($query);
- }
- }
- $sth->finish;
-
-
- $query = qq|DELETE FROM deductionbase
- WHERE trans_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- $query = qq|INSERT INTO deductionbase
- (trans_id, deduction_id, maximum) VALUES (?,?,?)|;
- $sth = $dbh->prepare($query) || $form->dberror($query);
-
- for ($i = 1; $i <= $form->{base_rows}; $i++) {
- ($null, $deduction_id) = split /--/, $form->{"base_$i"};
- $form->{"maximum_$i"} = $form->parse_amount($myconfig, $form->{"maximum_$i"});
- if ($deduction_id) {
- $sth->execute($form->{id}, $deduction_id, $form->{"maximum_$i"}) || $form->dberror($query);
- }
- }
- $sth->finish;
-
-
- $query = qq|DELETE FROM deductionafter
- WHERE trans_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- $query = qq|INSERT INTO deductionafter
- (trans_id, deduction_id) VALUES (?,?)|;
- $sth = $dbh->prepare($query) || $form->dberror($query);
-
- for ($i = 1; $i <= $form->{after_rows}; $i++) {
- ($null, $deduction_id) = split /--/, $form->{"after_$i"};
- if ($deduction_id) {
- $sth->execute($form->{id}, $deduction_id) || $form->dberror($query);
- }
- }
- $sth->finish;
-
- $dbh->commit;
- $dbh->disconnect;
-
-}
-
-
-sub delete_deduction {
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect_noauto($myconfig);
-
- # delete deduction
- my $query = qq|DELETE FROM $form->{db}
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- foreach $item (qw(rate base after)) {
- $query = qq|DELETE FROM deduction$item
- WHERE trans_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
- }
-
- $dbh->commit;
- $dbh->disconnect;
-
-}
-
1;
generated by cgit v1.2.3 (git 2.46.0) at 2024-11-23 03:31:28 +0000