summaryrefslogtreecommitdiff
path: root/LedgerSMB/HR.pm
blob: c9869e1511e1b4ca0d8de85787a77674cf4a2f8a (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} )
  45. || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );
  46. $ref = $sth->fetchrow_hashref(NAME_lc);
  47. # check if employee can be deleted, orphaned
  48. $form->{status} = "orphaned" unless $ref->{login};
  49. $ref->{employeelogin} = $ref->{login};
  50. delete $ref->{login};
  51. for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
  52. $sth->finish;
  53. # get manager
  54. $form->{managerid} *= 1;
  55. $sth = $dbh->prepare("SELECT name FROM employees WHERE id = ?");
  56. $sth->execute( $form->{managerid} );
  57. ( $form->{manager} ) = $sth->fetchrow_array;
  58. $notid = qq|AND id != | . $dbh->quote( $form->{id} );
  59. }
  60. else {
  61. ( $form->{startdate} ) = $dbh->selectrow_array("SELECT current_date");
  62. }
  63. # get managers
  64. $query = qq|
  65. SELECT id, name
  66. FROM employees
  67. WHERE sales = '1'
  68. AND role = 'manager'
  69. $notid
  70. ORDER BY 2|;
  71. $sth = $dbh->prepare($query);
  72. $sth->execute || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );
  73. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  74. push @{ $form->{all_manager} }, $ref;
  75. }
  76. $sth->finish;
  77. $dbh->commit;
  78. }
  79. sub save_employee {
  80. my ( $self, $myconfig, $form ) = @_;
  81. my $dbh = $form->{dbh};
  82. my $query;
  83. my $sth;
  84. if ( !$form->{id} ) {
  85. my $uid = localtime;
  86. $uid .= "$$";
  87. $query = qq|INSERT INTO employees (name) VALUES ('$uid')|;
  88. $dbh->do($query)
  89. || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );
  90. $query = qq|SELECT id FROM employees WHERE name = '$uid'|;
  91. $sth = $dbh->prepare($query);
  92. $sth->execute
  93. || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );
  94. ( $form->{id} ) = $sth->fetchrow_array;
  95. $sth->finish;
  96. }
  97. my ( $null, $managerid ) = split /--/, $form->{manager};
  98. $managerid *= 1;
  99. $form->{sales} *= 1;
  100. $form->{employeenumber} =
  101. $form->update_defaults( $myconfig, "employeenumber", $dbh )
  102. if !$form->{employeenumber};
  103. $query = qq|
  104. UPDATE employees
  105. SET employeenumber = ?,
  106. name = ?,
  107. address1 = ?,
  108. address2 = ?,
  109. city = ?,
  110. state = ?,
  111. zipcode = ?,
  112. country = ?,
  113. workphone = ?,
  114. homephone = ?,
  115. startdate = ?,
  116. enddate = ?,
  117. notes = ?,
  118. role = ?,
  119. sales = ?,
  120. email = ?,
  121. ssn = ?,
  122. dob = ?,
  123. iban = ?,
  124. bic = ?,
  125. managerid = ?
  126. WHERE id = ?|;
  127. $sth = $dbh->prepare($query);
  128. $form->{dob} ||= undef;
  129. $form->{startdate} ||= undef;
  130. $form->{enddate} ||= undef;
  131. $sth->execute(
  132. $form->{employeenumber}, $form->{name}, $form->{address1},
  133. $form->{address2}, $form->{city}, $form->{state},
  134. $form->{zipcode}, $form->{country}, $form->{workphone},
  135. $form->{homephone}, $form->{startdate}, $form->{enddate},
  136. $form->{notes}, $form->{role}, $form->{sales},
  137. $form->{email}, $form->{ssn}, $form->{dob},
  138. $form->{iban}, $form->{bic}, $managerid,
  139. $form->{id}
  140. ) || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );
  141. $dbh->commit;
  142. }
  143. sub delete_employee {
  144. my ( $self, $myconfig, $form ) = @_;
  145. # connect to database
  146. my $dbh = $form->{dbh};
  147. # delete employee
  148. my $query = qq|
  149. DELETE FROM employees
  150. WHERE id = | . $dbh->quote( $form->{id} );
  151. $dbh->do($query)
  152. || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );
  153. $dbh->commit;
  154. }
  155. sub employees {
  156. my ( $self, $myconfig, $form ) = @_;
  157. # connect to database
  158. my $dbh = $form->{dbh};
  159. my $where = "1 = 1";
  160. $form->{sort} = ( $form->{sort} ) ? $form->{sort} : "name";
  161. my @a = qw(name);
  162. my $sortorder = $form->sort_order( \@a );
  163. my $var;
  164. if ( $form->{startdatefrom} ) {
  165. $where .=
  166. " AND e.startdate >= " . $dbh->quote( $form->{startdatefrom} );
  167. }
  168. if ( $form->{startdateto} ) {
  169. $where .= " AND e.startddate <= " . $dbh->quote( $form->{startdateto} );
  170. }
  171. if ( $form->{name} ne "" ) {
  172. $var = $dbh->quote( $form->like( lc $form->{name} ) );
  173. $where .= " AND lower(e.name) LIKE $var";
  174. }
  175. if ( $form->{notes} ne "" ) {
  176. $var = $dbh->quote( $form->like( lc $form->{notes} ) );
  177. $where .= " AND lower(e.notes) LIKE $var";
  178. }
  179. if ( $form->{sales} eq 'Y' ) {
  180. $where .= " AND e.sales = '1'";
  181. }
  182. if ( $form->{status} eq 'orphaned' ) {
  183. $where .= qq| AND e.login IS NULL|;
  184. }
  185. if ( $form->{status} eq 'active' ) {
  186. $where .= qq| AND e.enddate IS NULL|;
  187. }
  188. if ( $form->{status} eq 'inactive' ) {
  189. $where .= qq| AND e.enddate <= current_date|;
  190. }
  191. my $query = qq|
  192. SELECT e.*, m.name AS manager
  193. FROM employees e
  194. LEFT JOIN employees m ON (m.id = e.managerid)
  195. WHERE $where
  196. ORDER BY $sortorder|;
  197. my $sth = $dbh->prepare($query);
  198. $sth->execute || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );
  199. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  200. $ref->{address} = "";
  201. for (qw(address1 address2 city state zipcode country)) {
  202. $ref->{address} .= "$ref->{$_} ";
  203. }
  204. push @{ $form->{all_employee} }, $ref;
  205. }
  206. $sth->finish;
  207. $dbh->commit;
  208. }
  209. 1;