summaryrefslogtreecommitdiff
path: root/LedgerSMB/HR.pm
blob: 6b6b6effe4c410d698fa270977b78e6488ffb468 (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 employees 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 employees 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 employees (name) VALUES ('$uid')|;
  86. $dbh->do($query) || $form->dberror(__FILE__.':'.__LINE__.':'.$query);
  87. $query = qq|SELECT id FROM employees 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 employees
  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->{dob} ||= undef;
  125. $form->{startdate} ||= undef;
  126. $form->{enddate} ||= undef;
  127. $sth->execute(
  128. $form->{employeenumber}, $form->{name}, $form->{address1},
  129. $form->{address2}, $form->{city}, $form->{state},
  130. $form->{zipcode}, $form->{country}, $form->{workphone},
  131. $form->{homephone}, $form->{startdate}, $form->{enddate},
  132. $form->{notes}, $form->{role}, $form->{sales}, $form->{email},
  133. $form->{ssn}, $form->{dob}, $form->{iban}, $form->{bic},
  134. $managerid, $form->{id}
  135. ) || $form->dberror(__FILE__.':'.__LINE__.':'.$query);
  136. $dbh->commit;
  137. }
  138. sub delete_employee {
  139. my ($self, $myconfig, $form) = @_;
  140. # connect to database
  141. my $dbh = $form->{dbh};
  142. # delete employee
  143. my $query = qq|
  144. DELETE FROM employees
  145. WHERE id = |.$dbh->quote($form->{id});
  146. $dbh->do($query) || $form->dberror(__FILE__.':'.__LINE__.':'.$query);
  147. $dbh->commit;
  148. }
  149. sub employees {
  150. my ($self, $myconfig, $form) = @_;
  151. # connect to database
  152. my $dbh = $form->{dbh};
  153. my $where = "1 = 1";
  154. $form->{sort} = ($form->{sort}) ? $form->{sort} : "name";
  155. my @a = qw(name);
  156. my $sortorder = $form->sort_order(\@a);
  157. my $var;
  158. if ($form->{startdatefrom}) {
  159. $where .= " AND e.startdate >= ".
  160. $dbh->quote($form->{startdatefrom});
  161. }
  162. if ($form->{startdateto}) {
  163. $where .= " AND e.startddate <= ".
  164. $dbh->quote($form->{startdateto});
  165. }
  166. if ($form->{name} ne "") {
  167. $var = $dbh->quote($form->like(lc $form->{name}));
  168. $where .= " AND lower(e.name) LIKE $var";
  169. }
  170. if ($form->{notes} ne "") {
  171. $var = $dbh->quote($form->like(lc $form->{notes}));
  172. $where .= " AND lower(e.notes) LIKE $var";
  173. }
  174. if ($form->{sales} eq 'Y') {
  175. $where .= " AND e.sales = '1'";
  176. }
  177. if ($form->{status} eq 'orphaned') {
  178. $where .= qq| AND e.login IS NULL|;
  179. }
  180. if ($form->{status} eq 'active') {
  181. $where .= qq| AND e.enddate IS NULL|;
  182. }
  183. if ($form->{status} eq 'inactive') {
  184. $where .= qq| AND e.enddate <= current_date|;
  185. }
  186. my $query = qq|
  187. SELECT e.*, m.name AS manager
  188. FROM employees e
  189. LEFT JOIN employees m ON (m.id = e.managerid)
  190. WHERE $where
  191. ORDER BY $sortorder|;
  192. my $sth = $dbh->prepare($query);
  193. $sth->execute || $form->dberror(__FILE__.':'.__LINE__.':'.$query);
  194. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  195. $ref->{address} = "";
  196. for (qw(address1 address2 city state zipcode country)) {
  197. $ref->{address} .= "$ref->{$_} ";
  198. }
  199. push @{ $form->{all_employee} }, $ref;
  200. }
  201. $sth->finish;
  202. $dbh->commit;
  203. }
  204. 1;