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