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