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