summaryrefslogtreecommitdiff
path: root/LedgerSMB/HR.pm
blob: 0b08b13f69d943b68b594009dabd5387a61e1c1e (plain)
  1. #=====================================================================
  2. # LedgerSMB
  3. # Small Medium Business Accounting software
  4. # http://www.ledgersmb.org/
  5. #
  6. # Copyright (C) 2006
  7. # This work contains copyrighted information from a number of sources all used
  8. # with permission.
  9. #
  10. # This file contains source code included with or based on SQL-Ledger which
  11. # is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
  12. # under the GNU General Public License version 2 or, at your option, any later
  13. # version. For a full list including contact information of contributors,
  14. # maintainers, and copyright holders, see the CONTRIBUTORS file.
  15. #
  16. # Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
  17. # Copyright (C) 2003
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors:
  23. #
  24. #======================================================================
  25. #
  26. # This file has NOT undergone whitespace cleanup.
  27. #
  28. #======================================================================
  29. #
  30. # backend code for human resources and payroll
  31. #
  32. #======================================================================
  33. package HR;
  34. sub get_employee {
  35. my ($self, $myconfig, $form) = @_;
  36. my $dbh = $form->{dbh};
  37. my $query;
  38. my $sth;
  39. my $ref;
  40. my $notid = "";
  41. if ($form->{id}) {
  42. $query = qq|SELECT e.* FROM employee e WHERE e.id = ?|;
  43. $sth = $dbh->prepare($query);
  44. $sth->execute($form->{id}) || $form->dberror(__FILE__.':'.__LINE__.':'.$query);
  45. $ref = $sth->fetchrow_hashref(NAME_lc);
  46. # check if employee can be deleted, orphaned
  47. $form->{status} = "orphaned" unless $ref->{login};
  48. $ref->{employeelogin} = $ref->{login};
  49. delete $ref->{login};
  50. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  51. $sth->finish;
  52. # get manager
  53. $form->{managerid} *= 1;
  54. $sth = $dbh->prepare("SELECT name FROM employee WHERE id = ?");
  55. $sth->execute($form->{managerid});
  56. ($form->{manager}) = $sth->fetchrow_array;
  57. $notid = qq|AND id != |.$dbh->quote($form->{id});
  58. } else {
  59. ($form->{startdate}) = $dbh->selectrow_array("SELECT current_date");
  60. }
  61. # get managers
  62. $query = qq|
  63. SELECT id, name
  64. FROM employee
  65. WHERE sales = '1'
  66. AND role = 'manager'
  67. $notid
  68. ORDER BY 2|;
  69. $sth = $dbh->prepare($query);
  70. $sth->execute || $form->dberror(__FILE__.':'.__LINE__.':'.$query);
  71. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  72. push @{ $form->{all_manager} }, $ref;
  73. }
  74. $sth->finish;
  75. $dbh->commit;
  76. }
  77. sub save_employee {
  78. my ($self, $myconfig, $form) = @_;
  79. my $dbh = $form->{dbh};
  80. my $query;
  81. my $sth;
  82. if (! $form->{id}) {
  83. my $uid = localtime;
  84. $uid .= "$$";
  85. $query = qq|INSERT INTO employee (name) VALUES ('$uid')|;
  86. $dbh->do($query) || $form->dberror(__FILE__.':'.__LINE__.':'.$query);
  87. $query = qq|SELECT id FROM employee WHERE name = '$uid'|;
  88. $sth = $dbh->prepare($query);
  89. $sth->execute || $form->dberror(__FILE__.':'.__LINE__.':'.$query);
  90. ($form->{id}) = $sth->fetchrow_array;
  91. $sth->finish;
  92. }
  93. my ($null, $managerid) = split /--/, $form->{manager};
  94. $managerid *= 1;
  95. $form->{sales} *= 1;
  96. $form->{employeenumber} = $form->update_defaults(
  97. $myconfig, "employeenumber", $dbh)
  98. if ! $form->{employeenumber};
  99. $query = qq|
  100. UPDATE employee
  101. SET employeenumber = ?,
  102. name = ?,
  103. address1 = ?,
  104. address2 = ?,
  105. city = ?,
  106. state = ?,
  107. zipcode = ?,
  108. country = ?,
  109. workphone = ?,
  110. homephone = ?,
  111. startdate = ?,
  112. enddate = ?,
  113. notes = ?,
  114. role = ?,
  115. sales = ?,
  116. email = ?,
  117. ssn = ?,
  118. dob = ?,
  119. iban = ?,
  120. bic = ?,
  121. managerid = ?
  122. WHERE id = ?|;
  123. $sth = $dbh->prepare($query);
  124. $form->db_prepare_vars('dob', 'startdate', 'enddate');
  125. $sth->execute(
  126. $form->{employeenumber}, $form->{name}, $form->{address1},
  127. $form->{address2}, $form->{city}, $form->{state},
  128. $form->{zipcode}, $form->{country}, $form->{workphone},
  129. $form->{homephone}, $form->{startdate}, $form->{enddate},
  130. $form->{notes}, $form->{role}, $form->{sales}, $form->{email},
  131. $form->{ssn}, $form->{dob}, $form->{iban}, $form->{bic},
  132. $managerid, $form->{id}
  133. ) || $form->dberror(__FILE__.':'.__LINE__.':'.$query);
  134. $dbh->commit;
  135. }
  136. sub delete_employee {
  137. my ($self, $myconfig, $form) = @_;
  138. # connect to database
  139. my $dbh = $form->{dbh};
  140. # delete employee
  141. my $query = qq|
  142. DELETE FROM employee
  143. WHERE id = |.$dbh->quote($form->{id});
  144. $dbh->do($query) || $form->dberror(__FILE__.':'.__LINE__.':'.$query);
  145. $dbh->commit;
  146. }
  147. sub employees {
  148. my ($self, $myconfig, $form) = @_;
  149. # connect to database
  150. my $dbh = $form->{dbh};
  151. my $where = "1 = 1";
  152. $form->{sort} = ($form->{sort}) ? $form->{sort} : "name";
  153. my @a = qw(name);
  154. my $sortorder = $form->sort_order(\@a);
  155. my $var;
  156. if ($form->{startdatefrom}) {
  157. $where .= " AND e.startdate >= ".
  158. $dbh->quote($form->{startdatefrom});
  159. }
  160. if ($form->{startdateto}) {
  161. $where .= " AND e.startddate <= ".
  162. $dbh->quote($form->{startdateto});
  163. }
  164. if ($form->{name} ne "") {
  165. $var = $dbh->quote($form->like(lc $form->{name}));
  166. $where .= " AND lower(e.name) LIKE $var";
  167. }
  168. if ($form->{notes} ne "") {
  169. $var = $dbh->quote($form->like(lc $form->{notes}));
  170. $where .= " AND lower(e.notes) LIKE $var";
  171. }
  172. if ($form->{sales} eq 'Y') {
  173. $where .= " AND e.sales = '1'";
  174. }
  175. if ($form->{status} eq 'orphaned') {
  176. $where .= qq| AND e.login IS NULL|;
  177. }
  178. if ($form->{status} eq 'active') {
  179. $where .= qq| AND e.enddate IS NULL|;
  180. }
  181. if ($form->{status} eq 'inactive') {
  182. $where .= qq| AND e.enddate <= current_date|;
  183. }
  184. my $query = qq|
  185. SELECT e.*, m.name AS manager
  186. FROM employee e
  187. LEFT JOIN employee m ON (m.id = e.managerid)
  188. WHERE $where
  189. ORDER BY $sortorder|;
  190. my $sth = $dbh->prepare($query);
  191. $sth->execute || $form->dberror(__FILE__.':'.__LINE__.':'.$query);
  192. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  193. $ref->{address} = "";
  194. for (qw(address1 address2 city state zipcode country)) {
  195. $ref->{address} .= "$ref->{$_} ";
  196. }
  197. push @{ $form->{all_employee} }, $ref;
  198. }
  199. $sth->finish;
  200. $dbh->commit;
  201. }
  202. 1;