summaryrefslogtreecommitdiff
path: root/LedgerSMB/HR.pm
blob: ee5a2c3219d6c4e9db61436e6811f8b3b51586c3 (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->dbconnect($myconfig);
  37. my $query;
  38. my $sth;
  39. my $ref;
  40. my $notid = "";
  41. if ($form->{id}) {
  42. $query = qq|SELECT e.*
  43. FROM employee e
  44. WHERE e.id = $form->{id}|;
  45. $sth = $dbh->prepare($query);
  46. $sth->execute || $form->dberror($query);
  47. $ref = $sth->fetchrow_hashref(NAME_lc);
  48. # check if employee can be deleted, orphaned
  49. $form->{status} = "orphaned" unless $ref->{login};
  50. $form->{status} = 'orphaned'; # leave orphaned for now until payroll is done
  51. $ref->{employeelogin} = $ref->{login};
  52. delete $ref->{login};
  53. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  54. $sth->finish;
  55. # get manager
  56. $form->{managerid} *= 1;
  57. $query = qq|SELECT name
  58. FROM employee
  59. WHERE id = $form->{managerid}|;
  60. ($form->{manager}) = $dbh->selectrow_array($query);
  61. ######### disabled for now
  62. if ($form->{deductions}) {
  63. # get allowances
  64. $query = qq|SELECT d.id, d.description, da.before, da.after, da.rate
  65. FROM employeededuction da
  66. JOIN deduction d ON (da.deduction_id = d.id)
  67. WHERE da.employee_id = $form->{id}|;
  68. $sth = $dbh->prepare($query);
  69. $sth->execute || $form->dberror($query);
  70. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  71. $ref->{rate} *= 100;
  72. push @{ $form->{all_employeededuction} }, $ref;
  73. }
  74. $sth->finish;
  75. }
  76. $notid = qq|AND id != $form->{id}|;
  77. } else {
  78. $query = qq|SELECT current_date|;
  79. ($form->{startdate}) = $dbh->selectrow_array($query);
  80. }
  81. # get managers
  82. $query = qq|SELECT id, name
  83. FROM employee
  84. WHERE sales = '1'
  85. AND role = 'manager'
  86. $notid
  87. ORDER BY 2|;
  88. $sth = $dbh->prepare($query);
  89. $sth->execute || $form->dberror($query);
  90. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  91. push @{ $form->{all_manager} }, $ref;
  92. }
  93. $sth->finish;
  94. # get deductions
  95. if ($form->{deductions}) {
  96. $query = qq|SELECT id, description
  97. FROM deduction
  98. ORDER BY 2|;
  99. $sth = $dbh->prepare($query);
  100. $sth->execute || $form->dberror($query);
  101. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  102. push @{ $form->{all_deduction} }, $ref;
  103. }
  104. $sth->finish;
  105. }
  106. $dbh->disconnect;
  107. }
  108. sub save_employee {
  109. my ($self, $myconfig, $form) = @_;
  110. # connect to database
  111. my $dbh = $form->dbconnect_noauto($myconfig);
  112. my $query;
  113. my $sth;
  114. if (! $form->{id}) {
  115. my $uid = localtime;
  116. $uid .= "$$";
  117. $query = qq|INSERT INTO employee (name)
  118. VALUES ('$uid')|;
  119. $dbh->do($query) || $form->dberror($query);
  120. $query = qq|SELECT id FROM employee
  121. WHERE name = '$uid'|;
  122. $sth = $dbh->prepare($query);
  123. $sth->execute || $form->dberror($query);
  124. ($form->{id}) = $sth->fetchrow_array;
  125. $sth->finish;
  126. }
  127. my ($null, $managerid) = split /--/, $form->{manager};
  128. $managerid *= 1;
  129. $form->{sales} *= 1;
  130. $form->{employeenumber} = $form->update_defaults($myconfig, "employeenumber", $dbh) if ! $form->{employeenumber};
  131. $query = qq|UPDATE employee SET
  132. employeenumber = |.$dbh->quote($form->{employeenumber}).qq|,
  133. name = |.$dbh->quote($form->{name}).qq|,
  134. address1 = |.$dbh->quote($form->{address1}).qq|,
  135. address2 = |.$dbh->quote($form->{address2}).qq|,
  136. city = |.$dbh->quote($form->{city}).qq|,
  137. state = |.$dbh->quote($form->{state}).qq|,
  138. zipcode = |.$dbh->quote($form->{zipcode}).qq|,
  139. country = |.$dbh->quote($form->{country}).qq|,
  140. workphone = '$form->{workphone}',
  141. homephone = '$form->{homephone}',
  142. startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|,
  143. enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|,
  144. notes = |.$dbh->quote($form->{notes}).qq|,
  145. role = '$form->{role}',
  146. sales = '$form->{sales}',
  147. email = |.$dbh->quote($form->{email}).qq|,
  148. ssn = '$form->{ssn}',
  149. dob = |.$form->dbquote($form->{dob}, SQL_DATE).qq|,
  150. iban = '$form->{iban}',
  151. bic = '$form->{bic}',
  152. managerid = $managerid
  153. WHERE id = $form->{id}|;
  154. $dbh->do($query) || $form->dberror($query);
  155. # for now
  156. if ($form->{selectdeduction}) {
  157. # insert deduction and allowances for payroll
  158. $query = qq|DELETE FROM employeededuction
  159. WHERE employee_id = $form->{id}|;
  160. $dbh->do($query) || $form->dberror($query);
  161. $query = qq|INSERT INTO employeededuction (employee_id, deduction_id,
  162. before, after, rate) VALUES ($form->{id},?,?,?,?)|;
  163. my $sth = $dbh->prepare($query) || $form->dberror($query);
  164. for ($i = 1; $i <= $form->{deduction_rows}; $i++) {
  165. for (qw(before after)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) }
  166. ($null, $deduction_id) = split /--/, $form->{"deduction_$i"};
  167. if ($deduction_id) {
  168. $sth->execute($deduction_id, $form->{"before_$i"}, $form->{"after_$i"}, $form->{"rate_$i"} / 100) || $form->dberror($query);
  169. }
  170. }
  171. $sth->finish;
  172. }
  173. $dbh->commit;
  174. $dbh->disconnect;
  175. }
  176. sub delete_employee {
  177. my ($self, $myconfig, $form) = @_;
  178. # connect to database
  179. my $dbh = $form->dbconnect_noauto($myconfig);
  180. # delete employee
  181. my $query = qq|DELETE FROM $form->{db}
  182. WHERE id = $form->{id}|;
  183. $dbh->do($query) || $form->dberror($query);
  184. $dbh->commit;
  185. $dbh->disconnect;
  186. }
  187. sub employees {
  188. my ($self, $myconfig, $form) = @_;
  189. # connect to database
  190. my $dbh = $form->dbconnect($myconfig);
  191. my $where = "1 = 1";
  192. $form->{sort} = ($form->{sort}) ? $form->{sort} : "name";
  193. my @a = qw(name);
  194. my $sortorder = $form->sort_order(\@a);
  195. my $var;
  196. if ($form->{startdatefrom}) {
  197. $where .= " AND e.startdate >= '$form->{startdatefrom}'";
  198. }
  199. if ($form->{startdateto}) {
  200. $where .= " AND e.startddate <= '$form->{startdateto}'";
  201. }
  202. if ($form->{name} ne "") {
  203. $var = $form->like(lc $form->{name});
  204. $where .= " AND lower(e.name) LIKE '$var'";
  205. }
  206. if ($form->{notes} ne "") {
  207. $var = $form->like(lc $form->{notes});
  208. $where .= " AND lower(e.notes) LIKE '$var'";
  209. }
  210. if ($form->{sales} eq 'Y') {
  211. $where .= " AND e.sales = '1'";
  212. }
  213. if ($form->{status} eq 'orphaned') {
  214. $where .= qq| AND e.login IS NULL|;
  215. }
  216. if ($form->{status} eq 'active') {
  217. $where .= qq| AND e.enddate IS NULL|;
  218. }
  219. if ($form->{status} eq 'inactive') {
  220. $where .= qq| AND e.enddate <= current_date|;
  221. }
  222. my $query = qq|SELECT e.*, m.name AS manager
  223. FROM employee e
  224. LEFT JOIN employee m ON (m.id = e.managerid)
  225. WHERE $where
  226. ORDER BY $sortorder|;
  227. my $sth = $dbh->prepare($query);
  228. $sth->execute || $form->dberror($query);
  229. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  230. $ref->{address} = "";
  231. for (qw(address1 address2 city state zipcode country)) { $ref->{address} .= "$ref->{$_} " }
  232. push @{ $form->{all_employee} }, $ref;
  233. }
  234. $sth->finish;
  235. $dbh->disconnect;
  236. }
  237. sub get_deduction {
  238. my ($self, $myconfig, $form) = @_;
  239. my $dbh = $form->dbconnect($myconfig);
  240. my $query;
  241. my $sth;
  242. my $ref;
  243. my $item;
  244. my $i;
  245. if ($form->{id}) {
  246. $query = qq|SELECT d.*,
  247. c1.accno AS ap_accno,
  248. c1.description AS ap_description,
  249. c2.accno AS expense_accno,
  250. c2.description AS expense_description
  251. FROM deduction d
  252. LEFT JOIN chart c1 ON (c1.id = d.ap_accno_id)
  253. LEFT JOIN chart c2 ON (c2.id = d.expense_accno_id)
  254. WHERE d.id = $form->{id}|;
  255. $sth = $dbh->prepare($query);
  256. $sth->execute || $form->dberror($query);
  257. $ref = $sth->fetchrow_hashref(NAME_lc);
  258. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  259. $sth->finish;
  260. # check if orphaned
  261. $form->{status} = 'orphaned'; # for now
  262. # get the rates
  263. $query = qq|SELECT rate, amount, above, below
  264. FROM deductionrate
  265. WHERE trans_id = $form->{id}
  266. ORDER BY rate, amount|;
  267. $sth = $dbh->prepare($query);
  268. $sth->execute || $form->dberror($query);
  269. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  270. push @{ $form->{deductionrate} }, $ref;
  271. }
  272. $sth->finish;
  273. # get all for deductionbase
  274. $query = qq|SELECT d.description, d.id, db.maximum
  275. FROM deductionbase db
  276. JOIN deduction d ON (d.id = db.deduction_id)
  277. WHERE db.trans_id = $form->{id}|;
  278. $sth = $dbh->prepare($query);
  279. $sth->execute || $form->dberror($query);
  280. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  281. push @{ $form->{deductionbase} }, $ref;
  282. }
  283. $sth->finish;
  284. # get all for deductionafter
  285. $query = qq|SELECT d.description, d.id
  286. FROM deductionafter da
  287. JOIN deduction d ON (d.id = da.deduction_id)
  288. WHERE da.trans_id = $form->{id}|;
  289. $sth = $dbh->prepare($query);
  290. $sth->execute || $form->dberror($query);
  291. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  292. push @{ $form->{deductionafter} }, $ref;
  293. }
  294. $sth->finish;
  295. # build selection list for base and after
  296. $query = qq|SELECT id, description
  297. FROM deduction
  298. WHERE id != $form->{id}
  299. ORDER BY 2|;
  300. } else {
  301. # build selection list for base and after
  302. $query = qq|SELECT id, description
  303. FROM deduction
  304. ORDER BY 2|;
  305. }
  306. $sth = $dbh->prepare($query);
  307. $sth->execute || $form->dberror($query);
  308. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  309. push @{ $form->{all_deduction} }, $ref;
  310. }
  311. $sth->finish;
  312. my %category = ( ap => 'L',
  313. expense => 'E' );
  314. foreach $item (keys %category) {
  315. $query = qq|SELECT accno, description
  316. FROM chart
  317. WHERE charttype = 'A'
  318. AND category = '$category{$item}'
  319. ORDER BY accno|;
  320. $sth = $dbh->prepare($query);
  321. $sth->execute || $form->dberror($query);
  322. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  323. push @{ $form->{"${item}_accounts"} }, $ref;
  324. }
  325. $sth->finish;
  326. }
  327. $dbh->disconnect;
  328. }
  329. sub deductions {
  330. my ($self, $myconfig, $form) = @_;
  331. my $dbh = $form->dbconnect($myconfig);
  332. my $query = qq|SELECT d.id, d.description, d.employeepays, d.employerpays,
  333. c1.accno AS ap_accno, c2.accno AS expense_accno,
  334. dr.rate, dr.amount, dr.above, dr.below
  335. FROM deduction d
  336. JOIN deductionrate dr ON (dr.trans_id = d.id)
  337. LEFT JOIN chart c1 ON (d.ap_accno_id = c1.id)
  338. LEFT JOIN chart c2 ON (d.expense_accno_id = c2.id)
  339. ORDER BY 2, 7, 8|;
  340. my $sth = $dbh->prepare($query);
  341. $sth->execute || $form->dberror($query);
  342. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  343. push @{ $form->{all_deduction} }, $ref;
  344. }
  345. $sth->finish;
  346. $dbh->disconnect;
  347. }
  348. sub save_deduction {
  349. my ($self, $myconfig, $form) = @_;
  350. # connect to database
  351. my $dbh = $form->dbconnect_noauto($myconfig);
  352. ($form->{ap_accno}) = split /--/, $form->{ap_accno};
  353. ($form->{expense_accno}) = split /--/, $form->{expense_accno};
  354. my $null;
  355. my $deduction_id;
  356. my $query;
  357. my $sth;
  358. if (! $form->{id}) {
  359. my $uid = localtime;
  360. $uid .= "$$";
  361. $query = qq|INSERT INTO deduction (description)
  362. VALUES ('$uid')|;
  363. $dbh->do($query) || $form->dberror($query);
  364. $query = qq|SELECT id FROM deduction
  365. WHERE description = '$uid'|;
  366. $sth = $dbh->prepare($query);
  367. $sth->execute || $form->dberror($query);
  368. ($form->{id}) = $sth->fetchrow_array;
  369. $sth->finish;
  370. }
  371. for (qw(employeepays employerpays)) { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) }
  372. $query = qq|UPDATE deduction SET
  373. description = |.$dbh->quote($form->{description}).qq|,
  374. ap_accno_id =
  375. (SELECT id FROM chart
  376. WHERE accno = '$form->{ap_accno}'),
  377. expense_accno_id =
  378. (SELECT id FROM chart
  379. WHERE accno = '$form->{expense_accno}'),
  380. employerpays = '$form->{employerpays}',
  381. employeepays = '$form->{employeepays}',
  382. fromage = |.$form->dbquote($form->{fromage}, SQL_INT).qq|,
  383. toage = |.$form->dbquote($form->{toage}, SQL_INT).qq|
  384. WHERE id = $form->{id}|;
  385. $dbh->do($query) || $form->dberror($query);
  386. $query = qq|DELETE FROM deductionrate
  387. WHERE trans_id = $form->{id}|;
  388. $dbh->do($query) || $form->dberror($query);
  389. $query = qq|INSERT INTO deductionrate
  390. (trans_id, rate, amount, above, below) VALUES (?,?,?,?,?)|;
  391. $sth = $dbh->prepare($query) || $form->dberror($query);
  392. for ($i = 1; $i <= $form->{rate_rows}; $i++) {
  393. for (qw(rate amount above below)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) }
  394. $form->{"rate_$i"} /= 100;
  395. if ($form->{"rate_$i"} || $form->{"amount_$i"}) {
  396. $sth->execute($form->{id}, $form->{"rate_$i"}, $form->{"amount_$i"}, $form->{"above_$i"}, $form->{"below_$i"}) || $form->dberror($query);
  397. }
  398. }
  399. $sth->finish;
  400. $query = qq|DELETE FROM deductionbase
  401. WHERE trans_id = $form->{id}|;
  402. $dbh->do($query) || $form->dberror($query);
  403. $query = qq|INSERT INTO deductionbase
  404. (trans_id, deduction_id, maximum) VALUES (?,?,?)|;
  405. $sth = $dbh->prepare($query) || $form->dberror($query);
  406. for ($i = 1; $i <= $form->{base_rows}; $i++) {
  407. ($null, $deduction_id) = split /--/, $form->{"base_$i"};
  408. $form->{"maximum_$i"} = $form->parse_amount($myconfig, $form->{"maximum_$i"});
  409. if ($deduction_id) {
  410. $sth->execute($form->{id}, $deduction_id, $form->{"maximum_$i"}) || $form->dberror($query);
  411. }
  412. }
  413. $sth->finish;
  414. $query = qq|DELETE FROM deductionafter
  415. WHERE trans_id = $form->{id}|;
  416. $dbh->do($query) || $form->dberror($query);
  417. $query = qq|INSERT INTO deductionafter
  418. (trans_id, deduction_id) VALUES (?,?)|;
  419. $sth = $dbh->prepare($query) || $form->dberror($query);
  420. for ($i = 1; $i <= $form->{after_rows}; $i++) {
  421. ($null, $deduction_id) = split /--/, $form->{"after_$i"};
  422. if ($deduction_id) {
  423. $sth->execute($form->{id}, $deduction_id) || $form->dberror($query);
  424. }
  425. }
  426. $sth->finish;
  427. $dbh->commit;
  428. $dbh->disconnect;
  429. }
  430. sub delete_deduction {
  431. my ($self, $myconfig, $form) = @_;
  432. # connect to database
  433. my $dbh = $form->dbconnect_noauto($myconfig);
  434. # delete deduction
  435. my $query = qq|DELETE FROM $form->{db}
  436. WHERE id = $form->{id}|;
  437. $dbh->do($query) || $form->dberror($query);
  438. foreach $item (qw(rate base after)) {
  439. $query = qq|DELETE FROM deduction$item
  440. WHERE trans_id = $form->{id}|;
  441. $dbh->do($query) || $form->dberror($query);
  442. }
  443. $dbh->commit;
  444. $dbh->disconnect;
  445. }
  446. 1;