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