summaryrefslogtreecommitdiff
path: root/LedgerSMB/JC.pm
blob: 09fe9b4f583f9df7cc1c260fa4c6addcc6d9e1ab (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) 2005
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors:
  23. #
  24. #======================================================================
  25. #
  26. # This file has undergone whitespace cleanup.
  27. #
  28. #======================================================================
  29. #
  30. # Job Costing
  31. #
  32. #======================================================================
  33. package JC;
  34. use LedgerSMB::IS;
  35. use LedgerSMB::PriceMatrix;
  36. use LedgerSMB::Sysconfig;
  37. sub get_jcitems {
  38. my ($self, $myconfig, $form) = @_;
  39. # connect to database
  40. my $dbh = $form->{dbh};
  41. my $query = qq|SELECT current_date|;
  42. ($form->{transdate}) = $dbh->selectrow_array($query);
  43. ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
  44. my $dateformat = $myconfig->{dateformat};
  45. $dateformat =~ s/yy/yyyy/;
  46. $dateformat =~ s/yyyyyy/yyyy/;
  47. if ($form->{id}) {
  48. # retrieve timecard/storescard
  49. $query = qq|
  50. SELECT j.*, to_char(j.checkedin, 'HH24:MI:SS')
  51. AS checkedina,
  52. to_char(j.checkedout, 'HH24:MI:SS')
  53. AS checkedouta,
  54. to_char(j.checkedin, ?) AS transdate,
  55. e.name AS employee, p.partnumber,
  56. pr.projectnumber,
  57. pr.description AS projectdescription,
  58. pr.production, pr.completed,
  59. pr.parts_id AS project
  60. FROM jcitems j
  61. JOIN employee e ON (e.id = j.employee_id)
  62. JOIN parts p ON (p.id = j.parts_id)
  63. JOIN project pr ON (pr.id = j.project_id)
  64. WHERE j.id = ?|;
  65. $sth = $dbh->prepare($query);
  66. $sth->execute($dateformat, $form->{id})
  67. || $form->dberror($query);
  68. $ref = $sth->fetchrow_hashref(NAME_lc);
  69. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  70. $sth->finish;
  71. $form->{project} = ($form->{project}) ? "job" : "project";
  72. for (qw(checkedin checkedout)) {
  73. $form->{$_} = $form->{"${_}a"};
  74. delete $form->{"${_}a"};
  75. }
  76. $query = qq|
  77. SELECT s.printed, s.spoolfile, s.formname
  78. FROM status s
  79. WHERE s.formname = ?
  80. AND s.trans_id = ?|;
  81. $sth = $dbh->prepare($query);
  82. $sth->execute($form->{type}, $form->{id})
  83. || $form->dberror($query);
  84. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  85. $form->{printed} .= "$ref->{formname} "
  86. if $ref->{printed};
  87. $form->{queued} .=
  88. "$ref->{formname} $ref->{spoolfile} "
  89. if $ref->{spoolfile};
  90. }
  91. $sth->finish;
  92. for (qw(printed queued)) { $form->{$_} =~ s/ +$//g }
  93. }
  94. JC->jcitems_links($myconfig, $form, $dbh);
  95. # get language codes
  96. $query = qq|SELECT * FROM language ORDER BY 2|;
  97. $sth = $dbh->prepare($query);
  98. $sth->execute || $form->dberror($query);
  99. $form->{all_language} = ();
  100. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  101. push @{ $form->{all_language} }, $ref;
  102. }
  103. $sth->finish;
  104. $dbh->commit;
  105. }
  106. sub jcitems_links {
  107. my ($self, $myconfig, $form, $dbh) = @_;
  108. my $disconnect = 0;
  109. if (! $dbh) {
  110. $dbh = $form->{dbh};
  111. }
  112. my $query;
  113. if ($form->{project_id}) {
  114. $form->{orphaned} = 1;
  115. $query = qq|SELECT parts_id FROM project WHERE id = ?|;
  116. my $sth = $dbh->prepare($query);
  117. $sth->execute($form->{project_id});
  118. if ($sth->fetchrow_array) {
  119. $form->{project} = 'job';
  120. $query = qq|
  121. SELECT id
  122. FROM project
  123. WHERE parts_id > 0
  124. AND production > completed
  125. AND id = $form->{project_id}|;
  126. my $sth = $dbh->prepare($query);
  127. $sth->execute($form->{project_id});
  128. ($form->{orphaned}) = $sth->fetchrow_array();
  129. $sth->finish;
  130. } else {
  131. $form->{project} = 'project';
  132. }
  133. $sth->finish;
  134. }
  135. JC->jcparts($myconfig, $form, $dbh);
  136. $form->all_employees($myconfig, $dbh, $form->{transdate});
  137. my $where;
  138. if ($form->{transdate}) {
  139. $where .= qq|
  140. AND (enddate IS NULL
  141. OR enddate >= |.
  142. $dbh->quote($form->{transdate}).qq|)
  143. AND (startdate <= |.
  144. $dbh->quote($form->{transdate}).qq|
  145. OR startdate IS NULL)|;
  146. }
  147. if ($form->{project} eq 'job') {
  148. $query = qq|
  149. SELECT pr.*
  150. FROM project pr
  151. WHERE pr.parts_id > 0
  152. AND pr.production > pr.completed
  153. $where|;
  154. } elsif ($form->{project} eq 'project') {
  155. $query = qq|
  156. SELECT pr.*
  157. FROM project pr
  158. WHERE pr.parts_id IS NULL
  159. $where|;
  160. } else {
  161. $query = qq|
  162. SELECT pr.*
  163. FROM project pr
  164. WHERE 1=1
  165. $where
  166. EXCEPT
  167. SELECT pr.*
  168. FROM project pr
  169. WHERE pr.parts_id > 0
  170. AND pr.production = pr.completed|;
  171. }
  172. if ($form->{project_id}) {
  173. $query .= qq|
  174. UNION
  175. SELECT *
  176. FROM project
  177. WHERE id = |.$dbh->quote($form->{project_id});
  178. }
  179. $query .= qq|
  180. ORDER BY projectnumber|;
  181. $sth = $dbh->prepare($query);
  182. $sth->execute || $form->dberror($query);
  183. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  184. push @{ $form->{all_project} }, $ref;
  185. }
  186. $sth->finish;
  187. }
  188. sub jcparts {
  189. my ($self, $myconfig, $form, $dbh) = @_;
  190. my ($null, $project_id) = split /--/, $form->{projectnumber};
  191. $project_id = $dbh->quote($project_id);
  192. my $query = qq|SELECT customer_id FROM project WHERE id = $project_id|;
  193. my ($customer_id) = $dbh->selectrow_array($query);
  194. $customer_id = $dbh->quote($customer_id);;
  195. my $where;
  196. if ($form->{project} eq 'job') {
  197. $where = " AND p.income_accno_id IS NULL";
  198. if ($form->{type} eq 'storescard') {
  199. $where = " AND p.inventory_accno_id > 0
  200. AND p.income_accno_id > 0";
  201. }
  202. $query = qq|
  203. SELECT p.id, p.partnumber, p.description,
  204. p.sellprice,
  205. p.unit, t.description AS translation
  206. FROM parts p
  207. LEFT JOIN translation t
  208. ON (t.trans_id = p.id
  209. AND t.language_code
  210. = |.$dbh->quote($form->{language_code}).qq|)
  211. WHERE p.obsolete = '0'
  212. $where|;
  213. } elsif ($form->{project} eq 'project') {
  214. $where = " AND p.inventory_accno_id IS NULL";
  215. if ($form->{type} eq 'storescard') {
  216. $where = " AND p.inventory_accno_id > 0";
  217. }
  218. $query = qq|
  219. SELECT p.id, p.partnumber, p.description,
  220. p.sellprice, p.unit,
  221. t.description AS translation
  222. FROM parts p
  223. LEFT JOIN translation t
  224. ON (t.trans_id = p.id
  225. AND t.language_code
  226. = |.$dbh->quote($form->{language_code}).qq|)
  227. WHERE p.obsolete = '0'
  228. AND p.assembly = '0' $where|;
  229. } else {
  230. $query = qq|
  231. SELECT p.id, p.partnumber, p.description,
  232. p.sellprice, p.unit,
  233. t.description AS translation
  234. FROM parts p
  235. LEFT JOIN translation t
  236. ON (t.trans_id = p.id
  237. AND t.language_code
  238. = |.$dbh->quote($form->{language_code}).qq|)
  239. WHERE p.obsolete = '0'
  240. AND p.income_accno_id IS NULL
  241. UNION
  242. SELECT p.id, p.partnumber, p.description,
  243. p.sellprice, p.unit,
  244. t.description AS translation
  245. FROM parts p
  246. LEFT JOIN translation t
  247. ON (t.trans_id = p.id
  248. AND t.language_code
  249. = |.$dbh->quote($form->{language_code}).qq|)
  250. WHERE p.obsolete = '0'
  251. AND p.assembly = '0'
  252. AND p.inventory_accno_id IS NULL|;
  253. }
  254. $query .= qq|
  255. ORDER BY 2|;
  256. my $sth = $dbh->prepare($query);
  257. $sth->execute || $form->dberror($query);
  258. my $pmh = PriceMatrix::price_matrix_query($dbh, $form);
  259. IS::exchangerate_defaults($dbh, $form);
  260. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  261. $ref->{description} = $ref->{translation}
  262. if $ref->{translation};
  263. PriceMatrix::price_matrix(
  264. $pmh, $ref, $form->{transdate}, 4, $form, $myconfig);
  265. push @{ $form->{all_parts} }, $ref;
  266. }
  267. $sth->finish;
  268. }
  269. sub delete_timecard {
  270. my ($self, $myconfig, $form) = @_;
  271. # connect to database
  272. my $dbh = $form->{dbh};
  273. my %audittrail = (
  274. tablename => 'jcitems',
  275. reference => $form->{id},
  276. formname => $form->{type},
  277. action => 'deleted',
  278. id => $form->{id} );
  279. $form->audittrail($dbh, "", \%audittrail);
  280. my $query = qq|DELETE FROM jcitems WHERE id = ?|;
  281. my $sth = $dbh->prepare($query);
  282. $sth->execute($form->{id})|| $form->dberror($query);
  283. # delete spool files
  284. $query = qq|
  285. SELECT spoolfile FROM status
  286. WHERE formname = ?
  287. AND trans_id = ?
  288. AND spoolfile IS NOT NULL|;
  289. my $sth = $dbh->prepare($query);
  290. $sth->execute($form->{type}, $form->{id}) || $form->dberror($query);
  291. my $spoolfile;
  292. my @spoolfiles = ();
  293. while (($spoolfile) = $sth->fetchrow_array) {
  294. push @spoolfiles, $spoolfile;
  295. }
  296. $sth->finish;
  297. # delete status entries
  298. $query = qq|
  299. DELETE
  300. FROM status
  301. WHERE formname = ?
  302. AND trans_id = ?|;
  303. my $sth = $dbh->prepare($query);
  304. $sth->execute($form->{type}, $form->{id}) || $form->dberror($query);
  305. my $rc = $dbh->commit;
  306. if ($rc) {
  307. foreach $spoolfile (@spoolfiles) {
  308. unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile"
  309. if $spoolfile;
  310. }
  311. }
  312. $dbh->{commit};
  313. $rc;
  314. }
  315. sub jcitems {
  316. my ($self, $myconfig, $form) = @_;
  317. # connect to database
  318. my $dbh = $form->{dbh};
  319. my $query;
  320. my $where = "1 = 1";
  321. my $null;
  322. my $var;
  323. if ($form->{projectnumber}) {
  324. ($null, $var) = split /--/, $form->{projectnumber};
  325. $var = $dbh->quote($var);
  326. $where .= " AND j.project_id = $var";
  327. $query = qq|SELECT parts_id FROM project WHERE id = $var|;
  328. my ($job) = $dbh->selectrow_array($query);
  329. $form->{project} = ($job) ? "job" : "project";
  330. }
  331. if ($form->{partnumber}) {
  332. ($null, $var) = split /--/, $form->{partnumber};
  333. $var = $dbh->quote($var);
  334. $where .= " AND j.parts_id = $var";
  335. $query = qq|
  336. SELECT inventory_accno_id
  337. FROM parts
  338. WHERE id = $var|;
  339. my ($job) = $dbh->selectrow_array($query);
  340. $form->{project} = ($job) ? "job" : "project";
  341. }
  342. if ($form->{employee}) {
  343. ($null, $var) = split /--/, $form->{employee};
  344. $var = $dbh->quote($var);
  345. $where .= " AND j.employee_id = $var";
  346. }
  347. if ($form->{open} || $form->{closed}) {
  348. unless ($form->{open} && $form->{closed}) {
  349. $where .= " AND j.qty != j.allocated" if $form->{open};
  350. $where .= " AND j.qty = j.allocated"
  351. if $form->{closed};
  352. }
  353. }
  354. ($form->{startdatefrom}, $form->{startdateto})
  355. = $form->from_to(
  356. $form->{year}, $form->{month}, $form->{interval})
  357. if $form->{year} && $form->{month};
  358. $where .= " AND j.checkedin >= ".$dbh->quote($form->{startdatefrom})
  359. if $form->{startdatefrom};
  360. $where .= " AND j.checkedout < date ".
  361. $dbh->quote($form->{startdateto})." + 1"
  362. if $form->{startdateto};
  363. my %ordinal = (
  364. id => 1,
  365. description => 2,
  366. transdate => 7,
  367. partnumber => 9,
  368. projectnumber => 10,
  369. projectdescription => 11,
  370. );
  371. my @a = (transdate, projectnumber);
  372. my $sortorder = $form->sort_order(\@a, \%ordinal);
  373. my $dateformat = $myconfig->{dateformat};
  374. $dateformat =~ s/yy$/yyyy/;
  375. $dateformat =~ s/yyyyyy/yyyy/;
  376. if ($form->{project} eq 'job') {
  377. if ($form->{type} eq 'timecard') {
  378. $where .= "
  379. AND pr.parts_id > 0
  380. AND p.income_accno_id IS NULL";
  381. }
  382. if ($form->{type} eq 'storescard') {
  383. $where .= "
  384. AND pr.parts_id > 0
  385. AND p.income_accno_id > 0";
  386. }
  387. }
  388. if ($form->{project} eq 'project') {
  389. $where .= " AND pr.parts_id IS NULL";
  390. }
  391. $query = qq|
  392. SELECT j.id, j.description, j.qty, j.allocated,
  393. to_char(j.checkedin, 'HH24:MI') AS checkedin,
  394. to_char(j.checkedout, 'HH24:MI') AS checkedout,
  395. to_char(j.checkedin, 'yyyymmdd') AS transdate,
  396. to_char(j.checkedin, ?) AS transdatea,
  397. to_char(j.checkedin, 'D') AS weekday,
  398. p.partnumber,
  399. pr.projectnumber, pr.description AS projectdescription,
  400. e.employeenumber, e.name AS employee,
  401. to_char(j.checkedin, 'WW') AS workweek, pr.parts_id,
  402. j.sellprice
  403. FROM jcitems j
  404. JOIN parts p ON (p.id = j.parts_id)
  405. JOIN project pr ON (pr.id = j.project_id)
  406. JOIN employee e ON (e.id = j.employee_id)
  407. WHERE $where
  408. ORDER BY employee, employeenumber, $sortorder|;
  409. $sth = $dbh->prepare($query);
  410. $sth->execute($dateformat) || $form->dberror($query);
  411. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  412. $ref->{project} = ($ref->{parts_id}) ? "job" : "project";
  413. $ref->{transdate} = $ref->{transdatea};
  414. delete $ref->{transdatea};
  415. push @{ $form->{transactions} }, $ref;
  416. }
  417. $sth->finish;
  418. $dbh->commit;
  419. }
  420. sub save {
  421. my ($self, $myconfig, $form) = @_;
  422. my $dbh = $form->{dbh};
  423. my $query;
  424. my $sth;
  425. my ($null, $project_id) = split /--/, $form->{projectnumber};
  426. if ($form->{id}) {
  427. # check if it was a job
  428. $query = qq|
  429. SELECT pr.parts_id, pr.production - pr.completed
  430. FROM project pr
  431. JOIN jcitems j ON (j.project_id = pr.id)
  432. WHERE j.id = ?|;
  433. $sth = $dbh->prepare($query);
  434. $sth->execute($form->{id});
  435. my ($job_id, $qty) = $sth->fetchrow_array();
  436. $sth->finish;
  437. if ($job_id && $qty == 0) {
  438. return -1;
  439. }
  440. # check if new one belongs to a job
  441. if ($project_id) {
  442. $query = qq|
  443. SELECT pr.parts_id,
  444. pr.production - pr.completed
  445. FROM project pr
  446. WHERE pr.id = ?|;
  447. $sth = $dbh->prepare($query);
  448. $sth->execute($project_id);
  449. my ($job_id, $qty) = $sth->fetchrow_array();
  450. if ($job_id && $qty == 0) {
  451. $dbh->disconnect;
  452. return -2;
  453. }
  454. }
  455. } else {
  456. my $uid = localtime;
  457. $uid .= "$$";
  458. $query = qq|INSERT INTO jcitems (description) VALUES ('$uid')|;
  459. $dbh->do($query) || $form->dberror($query);
  460. $query = qq|SELECT id FROM jcitems WHERE description = '$uid'|;
  461. ($form->{id}) = $dbh->selectrow_array($query);
  462. }
  463. for (qw(inhour inmin insec outhour outmin outsec)) {
  464. $form->{$_} = substr("00$form->{$_}", -2);
  465. }
  466. for (qw(qty sellprice allocated)) {
  467. $form->{$_} = $form->parse_amount($myconfig, $form->{$_});
  468. }
  469. my $checkedin = "$form->{inhour}$form->{inmin}$form->{insec}";
  470. my $checkedout = "$form->{outhour}$form->{outmin}$form->{outsec}";
  471. my $outdate = $form->{transdate};
  472. if ($checkedout < $checkedin) {
  473. $outdate = $form->add_date(
  474. $myconfig, $form->{transdate}, 1, 'days');
  475. }
  476. ($null, $form->{employee_id}) = split /--/, $form->{employee};
  477. unless ($form->{employee_id}) {
  478. ($form->{employee}, $form->{employee_id})
  479. = $form->get_employee($dbh);
  480. }
  481. my $parts_id;
  482. ($null, $parts_id) = split /--/, $form->{partnumber};
  483. $query = qq|
  484. UPDATE jcitems
  485. SET project_id = ?,
  486. parts_id = ?,
  487. description = ?,
  488. qty = ?,
  489. allocated = ?,
  490. sellprice = ?,
  491. fxsellprice = ?,
  492. serialnumber = ?,
  493. checkedin = ?::timestamp,
  494. checkedout = ?::timestamp,
  495. employee_id = ?,
  496. notes = ?
  497. WHERE id = ?|;
  498. $sth = $dbh->prepare($query);
  499. $sth->execute(
  500. $project_id, $parts_id, $form->{description}, $form->{qty},
  501. $form->{allocated}, $form->{sellprice}, $form->{sellprice},
  502. $form->{serialnumber},
  503. "$form->{transdate} $form->{inhour}:$form->{inmin}:".
  504. $form->{insec},
  505. "$outdate $form->{outhour}:$form->{outmin}:$form->{outsec}",
  506. $form->{employee_id}, $form->{notes}, $form->{id}
  507. ) || $form->dberror($query);
  508. # save printed, queued
  509. $form->save_status($dbh);
  510. my %audittrail = (
  511. tablename => 'jcitems',
  512. reference => $form->{id},
  513. formname => $form->{type},
  514. action => 'saved',
  515. id => $form->{id} );
  516. $form->audittrail($dbh, "", \%audittrail);
  517. my $rc = $dbh->commit;
  518. $rc;
  519. }
  520. 1;