summaryrefslogtreecommitdiff
path: root/LedgerSMB/JC.pm
blob: c9addc5170db23abaa0f23378e2cfa8538b2a8fc (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} .= "$ref->{formname} $ref->{spoolfile} "
  88. if $ref->{spoolfile};
  89. }
  90. $sth->finish;
  91. for (qw(printed queued)) { $form->{$_} =~ s/ +$//g }
  92. }
  93. JC->jcitems_links( $myconfig, $form, $dbh );
  94. # get language codes
  95. $query = qq|SELECT * FROM language ORDER BY 2|;
  96. $sth = $dbh->prepare($query);
  97. $sth->execute || $form->dberror($query);
  98. $form->{all_language} = ();
  99. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  100. push @{ $form->{all_language} }, $ref;
  101. }
  102. $sth->finish;
  103. $dbh->commit;
  104. }
  105. sub jcitems_links {
  106. my ( $self, $myconfig, $form, $dbh ) = @_;
  107. my $disconnect = 0;
  108. if ( !$dbh ) {
  109. $dbh = $form->{dbh};
  110. }
  111. my $query;
  112. if ( $form->{project_id} ) {
  113. $form->{orphaned} = 1;
  114. $query = qq|SELECT parts_id FROM project WHERE id = ?|;
  115. my $sth = $dbh->prepare($query);
  116. $sth->execute( $form->{project_id} );
  117. if ( $sth->fetchrow_array ) {
  118. $form->{project} = 'job';
  119. $query = qq|
  120. SELECT id
  121. FROM project
  122. WHERE parts_id > 0
  123. AND production > completed
  124. AND id = $form->{project_id}|;
  125. my $sth = $dbh->prepare($query);
  126. $sth->execute( $form->{project_id} );
  127. ( $form->{orphaned} ) = $sth->fetchrow_array();
  128. $sth->finish;
  129. }
  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 >= | . $dbh->quote( $form->{transdate} ) . qq|)
  142. AND (startdate <= | . $dbh->quote( $form->{transdate} ) . qq|
  143. OR startdate IS NULL)|;
  144. }
  145. if ( $form->{project} eq 'job' ) {
  146. $query = qq|
  147. SELECT pr.*
  148. FROM project pr
  149. WHERE pr.parts_id > 0
  150. AND pr.production > pr.completed
  151. $where|;
  152. }
  153. elsif ( $form->{project} eq 'project' ) {
  154. $query = qq|
  155. SELECT pr.*
  156. FROM project pr
  157. WHERE pr.parts_id IS NULL
  158. $where|;
  159. }
  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. }
  214. elsif ( $form->{project} eq 'project' ) {
  215. $where = " AND p.inventory_accno_id IS NULL";
  216. if ( $form->{type} eq 'storescard' ) {
  217. $where = " AND p.inventory_accno_id > 0";
  218. }
  219. $query = qq|
  220. SELECT p.id, p.partnumber, p.description,
  221. p.sellprice, p.unit,
  222. t.description AS translation
  223. FROM parts p
  224. LEFT JOIN translation t
  225. ON (t.trans_id = p.id
  226. AND t.language_code
  227. = | . $dbh->quote( $form->{language_code} ) . qq|)
  228. WHERE p.obsolete = '0'
  229. AND p.assembly = '0' $where|;
  230. }
  231. else {
  232. $query = qq|
  233. SELECT p.id, p.partnumber, p.description,
  234. p.sellprice, p.unit,
  235. t.description AS translation
  236. FROM parts p
  237. LEFT JOIN translation t
  238. ON (t.trans_id = p.id
  239. AND t.language_code
  240. = | . $dbh->quote( $form->{language_code} ) . qq|)
  241. WHERE p.obsolete = '0'
  242. AND p.income_accno_id IS NULL
  243. UNION
  244. SELECT p.id, p.partnumber, p.description,
  245. p.sellprice, p.unit,
  246. t.description AS translation
  247. FROM parts p
  248. LEFT JOIN translation t
  249. ON (t.trans_id = p.id
  250. AND t.language_code
  251. = | . $dbh->quote( $form->{language_code} ) . qq|)
  252. WHERE p.obsolete = '0'
  253. AND p.assembly = '0'
  254. AND p.inventory_accno_id IS NULL|;
  255. }
  256. $query .= qq|
  257. ORDER BY 2|;
  258. my $sth = $dbh->prepare($query);
  259. $sth->execute || $form->dberror($query);
  260. my $pmh = PriceMatrix::price_matrix_query( $dbh, $form );
  261. IS::exchangerate_defaults( $dbh, $form );
  262. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  263. $ref->{description} = $ref->{translation}
  264. if $ref->{translation};
  265. PriceMatrix::price_matrix( $pmh, $ref, $form->{transdate}, 4, $form,
  266. $myconfig );
  267. push @{ $form->{all_parts} }, $ref;
  268. }
  269. $sth->finish;
  270. }
  271. sub delete_timecard {
  272. my ( $self, $myconfig, $form ) = @_;
  273. # connect to database
  274. my $dbh = $form->{dbh};
  275. my %audittrail = (
  276. tablename => 'jcitems',
  277. reference => $form->{id},
  278. formname => $form->{type},
  279. action => 'deleted',
  280. id => $form->{id}
  281. );
  282. $form->audittrail( $dbh, "", \%audittrail );
  283. my $query = qq|DELETE FROM jcitems WHERE id = ?|;
  284. my $sth = $dbh->prepare($query);
  285. $sth->execute( $form->{id} ) || $form->dberror($query);
  286. # delete spool files
  287. $query = qq|
  288. SELECT spoolfile FROM status
  289. WHERE formname = ?
  290. AND trans_id = ?
  291. AND spoolfile IS NOT NULL|;
  292. my $sth = $dbh->prepare($query);
  293. $sth->execute( $form->{type}, $form->{id} ) || $form->dberror($query);
  294. my $spoolfile;
  295. my @spoolfiles = ();
  296. while ( ($spoolfile) = $sth->fetchrow_array ) {
  297. push @spoolfiles, $spoolfile;
  298. }
  299. $sth->finish;
  300. # delete status entries
  301. $query = qq|
  302. DELETE
  303. FROM status
  304. WHERE formname = ?
  305. AND trans_id = ?|;
  306. my $sth = $dbh->prepare($query);
  307. $sth->execute( $form->{type}, $form->{id} ) || $form->dberror($query);
  308. my $rc = $dbh->commit;
  309. if ($rc) {
  310. foreach $spoolfile (@spoolfiles) {
  311. unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile"
  312. if $spoolfile;
  313. }
  314. }
  315. $dbh->{commit};
  316. $rc;
  317. }
  318. sub jcitems {
  319. my ( $self, $myconfig, $form ) = @_;
  320. # connect to database
  321. my $dbh = $form->{dbh};
  322. my $query;
  323. my $where = "1 = 1";
  324. my $null;
  325. my $var;
  326. if ( $form->{projectnumber} ) {
  327. ( $null, $var ) = split /--/, $form->{projectnumber};
  328. $var = $dbh->quote($var);
  329. $where .= " AND j.project_id = $var";
  330. $query = qq|SELECT parts_id FROM project WHERE id = $var|;
  331. my ($job) = $dbh->selectrow_array($query);
  332. $form->{project} = ($job) ? "job" : "project";
  333. }
  334. if ( $form->{partnumber} ) {
  335. ( $null, $var ) = split /--/, $form->{partnumber};
  336. $var = $dbh->quote($var);
  337. $where .= " AND j.parts_id = $var";
  338. $query = qq|
  339. SELECT inventory_accno_id
  340. FROM parts
  341. WHERE id = $var|;
  342. my ($job) = $dbh->selectrow_array($query);
  343. $form->{project} = ($job) ? "job" : "project";
  344. }
  345. if ( $form->{employee} ) {
  346. ( $null, $var ) = split /--/, $form->{employee};
  347. $var = $dbh->quote($var);
  348. $where .= " AND j.employee_id = $var";
  349. }
  350. if ( $form->{open} || $form->{closed} ) {
  351. unless ( $form->{open} && $form->{closed} ) {
  352. $where .= " AND j.qty != j.allocated" if $form->{open};
  353. $where .= " AND j.qty = j.allocated"
  354. if $form->{closed};
  355. }
  356. }
  357. ( $form->{startdatefrom}, $form->{startdateto} ) =
  358. $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
  359. if $form->{year} && $form->{month};
  360. $where .= " AND j.checkedin >= " . $dbh->quote( $form->{startdatefrom} )
  361. if $form->{startdatefrom};
  362. $where .=
  363. " AND j.checkedout < date " . $dbh->quote( $form->{startdateto} ) . " + 1"
  364. if $form->{startdateto};
  365. my %ordinal = (
  366. id => 1,
  367. description => 2,
  368. transdate => 7,
  369. partnumber => 9,
  370. projectnumber => 10,
  371. projectdescription => 11,
  372. );
  373. my @a = ( transdate, projectnumber );
  374. my $sortorder = $form->sort_order( \@a, \%ordinal );
  375. my $dateformat = $myconfig->{dateformat};
  376. $dateformat =~ s/yy$/yyyy/;
  377. $dateformat =~ s/yyyyyy/yyyy/;
  378. if ( $form->{project} eq 'job' ) {
  379. if ( $form->{type} eq 'timecard' ) {
  380. $where .= "
  381. AND pr.parts_id > 0
  382. AND p.income_accno_id IS NULL";
  383. }
  384. if ( $form->{type} eq 'storescard' ) {
  385. $where .= "
  386. AND pr.parts_id > 0
  387. AND p.income_accno_id > 0";
  388. }
  389. }
  390. if ( $form->{project} eq 'project' ) {
  391. $where .= " AND pr.parts_id IS NULL";
  392. }
  393. $query = qq|
  394. SELECT j.id, j.description, j.qty, j.allocated,
  395. to_char(j.checkedin, 'HH24:MI') AS checkedin,
  396. to_char(j.checkedout, 'HH24:MI') AS checkedout,
  397. to_char(j.checkedin, 'yyyymmdd') AS transdate,
  398. to_char(j.checkedin, ?) AS transdatea,
  399. to_char(j.checkedin, 'D') AS weekday,
  400. p.partnumber,
  401. pr.projectnumber, pr.description AS projectdescription,
  402. e.employeenumber, e.name AS employee,
  403. to_char(j.checkedin, 'WW') AS workweek, pr.parts_id,
  404. j.sellprice
  405. FROM jcitems j
  406. JOIN parts p ON (p.id = j.parts_id)
  407. JOIN project pr ON (pr.id = j.project_id)
  408. JOIN employee e ON (e.id = j.employee_id)
  409. WHERE $where
  410. ORDER BY employee, employeenumber, $sortorder|;
  411. $sth = $dbh->prepare($query);
  412. $sth->execute($dateformat) || $form->dberror($query);
  413. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  414. $ref->{project} = ( $ref->{parts_id} ) ? "job" : "project";
  415. $ref->{transdate} = $ref->{transdatea};
  416. delete $ref->{transdatea};
  417. push @{ $form->{transactions} }, $ref;
  418. }
  419. $sth->finish;
  420. $dbh->commit;
  421. }
  422. sub save {
  423. my ( $self, $myconfig, $form ) = @_;
  424. my $dbh = $form->{dbh};
  425. my $query;
  426. my $sth;
  427. my ( $null, $project_id ) = split /--/, $form->{projectnumber};
  428. if ( $form->{id} ) {
  429. # check if it was a job
  430. $query = qq|
  431. SELECT pr.parts_id, pr.production - pr.completed
  432. FROM project pr
  433. JOIN jcitems j ON (j.project_id = pr.id)
  434. WHERE j.id = ?|;
  435. $sth = $dbh->prepare($query);
  436. $sth->execute( $form->{id} );
  437. my ( $job_id, $qty ) = $sth->fetchrow_array();
  438. $sth->finish;
  439. if ( $job_id && $qty == 0 ) {
  440. return -1;
  441. }
  442. # check if new one belongs to a job
  443. if ($project_id) {
  444. $query = qq|
  445. SELECT pr.parts_id,
  446. pr.production - pr.completed
  447. FROM project pr
  448. WHERE pr.id = ?|;
  449. $sth = $dbh->prepare($query);
  450. $sth->execute($project_id);
  451. my ( $job_id, $qty ) = $sth->fetchrow_array();
  452. if ( $job_id && $qty == 0 ) {
  453. $dbh->disconnect;
  454. return -2;
  455. }
  456. }
  457. }
  458. else {
  459. my $uid = localtime;
  460. $uid .= "$$";
  461. $query = qq|INSERT INTO jcitems (description) VALUES ('$uid')|;
  462. $dbh->do($query) || $form->dberror($query);
  463. $query = qq|SELECT id FROM jcitems WHERE description = '$uid'|;
  464. ( $form->{id} ) = $dbh->selectrow_array($query);
  465. }
  466. for (qw(inhour inmin insec outhour outmin outsec)) {
  467. $form->{$_} = substr( "00$form->{$_}", -2 );
  468. }
  469. for (qw(qty sellprice allocated)) {
  470. $form->{$_} = $form->parse_amount( $myconfig, $form->{$_} );
  471. }
  472. my $checkedin = "$form->{inhour}$form->{inmin}$form->{insec}";
  473. my $checkedout = "$form->{outhour}$form->{outmin}$form->{outsec}";
  474. my $outdate = $form->{transdate};
  475. if ( $checkedout < $checkedin ) {
  476. $outdate = $form->add_date( $myconfig, $form->{transdate}, 1, 'days' );
  477. }
  478. ( $null, $form->{employee_id} ) = split /--/, $form->{employee};
  479. unless ( $form->{employee_id} ) {
  480. ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh);
  481. }
  482. my $parts_id;
  483. ( $null, $parts_id ) = split /--/, $form->{partnumber};
  484. $query = qq|
  485. UPDATE jcitems
  486. SET project_id = ?,
  487. parts_id = ?,
  488. description = ?,
  489. qty = ?,
  490. allocated = ?,
  491. sellprice = ?,
  492. fxsellprice = ?,
  493. serialnumber = ?,
  494. checkedin = ?::timestamp,
  495. checkedout = ?::timestamp,
  496. employee_id = ?,
  497. notes = ?
  498. WHERE id = ?|;
  499. $sth = $dbh->prepare($query);
  500. $sth->execute(
  501. $project_id,
  502. $parts_id,
  503. $form->{description},
  504. $form->{qty},
  505. $form->{allocated},
  506. $form->{sellprice},
  507. $form->{sellprice},
  508. $form->{serialnumber},
  509. "$form->{transdate} $form->{inhour}:$form->{inmin}:" . $form->{insec},
  510. "$outdate $form->{outhour}:$form->{outmin}:$form->{outsec}",
  511. $form->{employee_id},
  512. $form->{notes},
  513. $form->{id}
  514. ) || $form->dberror($query);
  515. # save printed, queued
  516. $form->save_status($dbh);
  517. my %audittrail = (
  518. tablename => 'jcitems',
  519. reference => $form->{id},
  520. formname => $form->{type},
  521. action => 'saved',
  522. id => $form->{id}
  523. );
  524. $form->audittrail( $dbh, "", \%audittrail );
  525. my $rc = $dbh->commit;
  526. $rc;
  527. }
  528. 1;