summaryrefslogtreecommitdiff
path: root/LedgerSMB/PE.pm
blob: efe8f4ecc6e3e4ff0515d29bb0794dbff54d06ac (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) 2003
  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. # Project module
  31. # also used for partsgroups
  32. #
  33. #======================================================================
  34. package PE;
  35. sub projects {
  36. my ($self, $myconfig, $form) = @_;
  37. my $dbh = $form->{dbh};
  38. $form->{sort} = "projectnumber" unless $form->{sort};
  39. my @a = ($form->{sort});
  40. my %ordinal = ( projectnumber => 2,
  41. description => 3,
  42. startdate => 4,
  43. enddate => 5,
  44. );
  45. my $sortorder = $form->sort_order(\@a, \%ordinal);
  46. my $query;
  47. my $where = "WHERE 1=1";
  48. $query = qq|
  49. SELECT pr.*, c.name
  50. FROM project pr
  51. LEFT JOIN customer c ON (c.id = pr.customer_id)|;
  52. if ($form->{type} eq 'job') {
  53. $where .= qq| AND pr.id NOT IN (SELECT DISTINCT id
  54. FROM parts
  55. WHERE project_id > 0)|;
  56. }
  57. my $var;
  58. if ($form->{projectnumber} ne "") {
  59. $var = $dbh->quote($form->like(lc $form->{projectnumber}));
  60. $where .= " AND lower(pr.projectnumber) LIKE $var";
  61. }
  62. if ($form->{description} ne "") {
  63. $var = $dbh->quote($form->like(lc $form->{description}));
  64. $where .= " AND lower(pr.description) LIKE $var";
  65. }
  66. ($form->{startdatefrom}, $form->{startdateto})
  67. = $form->from_to(
  68. $form->{year}, $form->{month}, $form->{interval})
  69. if $form->{year} && $form->{month};
  70. if ($form->{startdatefrom}) {
  71. $where .= " AND (pr.startdate IS NULL OR pr.startdate >= ".
  72. $dbh->quote($form->{startdatefrom}).")";
  73. }
  74. if ($form->{startdateto}) {
  75. $where .= " AND (pr.startdate IS NULL OR pr.startdate <= ".
  76. $dbh->quote($form->{startdateto}).")";
  77. }
  78. if ($form->{status} eq 'orphaned') {
  79. $where .= qq| AND pr.id NOT IN (SELECT DISTINCT project_id
  80. FROM acc_trans
  81. WHERE project_id > 0
  82. UNION
  83. SELECT DISTINCT project_id
  84. FROM invoice
  85. WHERE project_id > 0
  86. UNION
  87. SELECT DISTINCT project_id
  88. FROM orderitems
  89. WHERE project_id > 0
  90. UNION
  91. SELECT DISTINCT project_id
  92. FROM jcitems
  93. WHERE project_id > 0)
  94. |;
  95. }
  96. if ($form->{status} eq 'active') {
  97. $where .= qq|
  98. AND (pr.enddate IS NULL
  99. OR pr.enddate >= current_date)|;
  100. }
  101. if ($form->{status} eq 'inactive') {
  102. $where .= qq| AND pr.enddate <= current_date|;
  103. }
  104. $query .= qq|
  105. $where
  106. ORDER BY $sortorder|;
  107. $sth = $dbh->prepare($query);
  108. $sth->execute || $form->dberror($query);
  109. my $i = 0;
  110. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  111. push @{ $form->{all_project} }, $ref;
  112. $i++;
  113. }
  114. $sth->finish;
  115. $dbh->commit;
  116. $i;
  117. }
  118. sub get_project {
  119. my ($self, $myconfig, $form) = @_;
  120. my $dbh = $form->{dbh};
  121. my $query;
  122. my $sth;
  123. my $ref;
  124. my $where;
  125. if ($form->{id}) {
  126. $query = qq|
  127. SELECT pr.*, c.name AS customer
  128. FROM project pr
  129. LEFT JOIN customer c ON (c.id = pr.customer_id)
  130. WHERE pr.id = ?|;
  131. $sth = $dbh->prepare($query);
  132. $sth->execute($form->{id}) || $form->dberror($query);
  133. $ref = $sth->fetchrow_hashref(NAME_lc);
  134. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  135. $sth->finish;
  136. # check if it is orphaned
  137. $query = qq|
  138. SELECT count(*)
  139. FROM acc_trans
  140. WHERE project_id = ?
  141. UNION
  142. SELECT count(*)
  143. FROM invoice
  144. WHERE project_id = ?
  145. UNION
  146. SELECT count(*)
  147. FROM orderitems
  148. WHERE project_id = ?
  149. UNION
  150. SELECT count(*)
  151. FROM jcitems
  152. WHERE project_id = ?|;
  153. $sth = $dbh->prepare($query);
  154. $sth->execute(
  155. $form->{id}, $form->{id}, $form->{id}, $form->{id}
  156. )|| $form->dberror($query);
  157. my $count;
  158. while (($count) = $sth->fetchrow_array) {
  159. $form->{orphaned} += $count;
  160. }
  161. $sth->finish;
  162. $form->{orphaned} = !$form->{orphaned};
  163. }
  164. PE->get_customer($myconfig, $form, $dbh);
  165. $form->run_custom_queries('project', 'SELECT');
  166. $dbh->commit;
  167. }
  168. sub save_project {
  169. my ($self, $myconfig, $form) = @_;
  170. my $dbh = $form->{dbh};
  171. $form->{customer_id} ||= undef;
  172. $form->{projectnumber}
  173. = $form->update_defaults($myconfig, "projectnumber", $dbh)
  174. unless $form->{projectnumber};
  175. my $enddate;
  176. my $startdate;
  177. $enddate = $form->{enddate} if $form->{enddate};
  178. $startdate = $form->{startdate} if $form->{startdate};
  179. if ($form->{id}) {
  180. $query = qq|
  181. UPDATE project
  182. SET projectnumber = ?,
  183. description = ?,
  184. startdate = ?,
  185. enddate = ?,
  186. customer_id = ?
  187. WHERE id = |.$dbh->quote($form->{id});
  188. } else {
  189. $query = qq|
  190. INSERT INTO project (projectnumber, description,
  191. startdate, enddate, customer_id)
  192. VALUES (?, ?, ?, ?, ?)|;
  193. }
  194. $sth = $dbh->prepare($query);
  195. $sth->execute(
  196. $form->{projectnumber}, $form->{description},
  197. $startdate, $enddate, $form->{customer_id}
  198. ) || $form->dberror($query);
  199. $form->run_custom_queries('project', 'UPDATE');
  200. $dbh->commit;
  201. }
  202. sub list_stock {
  203. my ($self, $myconfig, $form) = @_;
  204. my $dbh = $form->{dbh};
  205. my $var;
  206. my $where = "1 = 1";
  207. if ($form->{status} eq 'active') {
  208. $where = qq|
  209. (pr.enddate IS NULL OR pr.enddate >= current_date)
  210. AND pr.completed < pr.production|;
  211. }
  212. if ($form->{status} eq 'inactive') {
  213. $where = qq|pr.completed = pr.production|;
  214. }
  215. if ($form->{projectnumber}) {
  216. $var = $dbh->quote($form->like(lc $form->{projectnumber}));
  217. $where .= " AND lower(pr.projectnumber) LIKE $var";
  218. }
  219. if ($form->{description}) {
  220. $var = $dbh->quote($form->like(lc $form->{description}));
  221. $where .= " AND lower(pr.description) LIKE $var";
  222. }
  223. $form->{sort} = "projectnumber" unless $form->{sort};
  224. my @a = ($form->{sort});
  225. my %ordinal = ( projectnumber => 2, description => 3 );
  226. my $sortorder = $form->sort_order(\@a, \%ordinal);
  227. my $query = qq|
  228. SELECT pr.*, p.partnumber
  229. FROM project pr
  230. JOIN parts p ON (p.id = pr.parts_id)
  231. WHERE $where
  232. ORDER BY $sortorder|;
  233. $sth = $dbh->prepare($query);
  234. $sth->execute || $form->dberror($query);
  235. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  236. push @{ $form->{all_project} }, $ref;
  237. }
  238. $sth->finish;
  239. $query = qq|SELECT current_date|;
  240. ($form->{stockingdate}) = $dbh->selectrow_array($query)
  241. if !$form->{stockingdate};
  242. $dbh->commit;
  243. }
  244. sub jobs {
  245. my ($self, $myconfig, $form) = @_;
  246. my $dbh = $form->{dbh};
  247. $form->{sort} = "projectnumber" unless $form->{sort};
  248. my @a = ($form->{sort});
  249. my %ordinal = (projectnumber => 2, description => 3, startdate => 4);
  250. my $sortorder = $form->sort_order(\@a, \%ordinal);
  251. my $query = qq|
  252. SELECT pr.*, p.partnumber, p.onhand, c.name
  253. FROM project pr
  254. JOIN parts p ON (p.id = pr.parts_id)
  255. LEFT JOIN customer c ON (c.id = pr.customer_id)
  256. WHERE 1=1|;
  257. if ($form->{projectnumber} ne "") {
  258. $var = $dbh->quote($form->like(lc $form->{projectnumber}));
  259. $query .= " AND lower(pr.projectnumber) LIKE $var";
  260. }
  261. if ($form->{description} ne "") {
  262. $var = $dbh->quote($form->like(lc $form->{description}));
  263. $query .= " AND lower(pr.description) LIKE $var";
  264. }
  265. ($form->{startdatefrom}, $form->{startdateto})
  266. = $form->from_to($form->{year}, $form->{month},
  267. $form->{interval})
  268. if $form->{year} && $form->{month};
  269. if ($form->{startdatefrom}) {
  270. $query .= " AND pr.startdate >= ".
  271. $dbh->quote($form->{startdatefrom});
  272. }
  273. if ($form->{startdateto}) {
  274. $query .= " AND pr.startdate <= ".
  275. $dbh->quote($form->{startdateto});
  276. }
  277. if ($form->{status} eq 'active') {
  278. $query .= qq| AND NOT pr.production = pr.completed|;
  279. }
  280. if ($form->{status} eq 'inactive') {
  281. $query .= qq| AND pr.production = pr.completed|;
  282. }
  283. if ($form->{status} eq 'orphaned') {
  284. $query .= qq|
  285. AND pr.completed = 0
  286. AND (pr.id NOT IN
  287. (SELECT DISTINCT project_id
  288. FROM invoice
  289. WHERE project_id > 0
  290. UNION
  291. SELECT DISTINCT project_id
  292. FROM orderitems
  293. WHERE project_id > 0
  294. UNION
  295. SELECT DISTINCT project_id
  296. FROM jcitems
  297. WHERE project_id > 0)
  298. )|;
  299. }
  300. $query .= qq|
  301. ORDER BY $sortorder|;
  302. $sth = $dbh->prepare($query);
  303. $sth->execute || $form->dberror($query);
  304. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  305. push @{ $form->{all_project} }, $ref;
  306. }
  307. $sth->finish;
  308. $dbh->commit;
  309. }
  310. sub get_job {
  311. my ($self, $myconfig, $form) = @_;
  312. # connect to database
  313. my $dbh = $form->{dbh};
  314. my $query;
  315. my $sth;
  316. my $ref;
  317. if ($form->{id}) {
  318. $query = qq|
  319. SELECT value FROM defaults
  320. WHERE setting_key = 'weightunit'|;
  321. ($form->{weightunit}) = $dbh->selectrow_array($query);
  322. $query = qq|
  323. SELECT pr.*, p.partnumber,
  324. p.description AS partdescription, p.unit,
  325. p.listprice, p.sellprice, p.priceupdate,
  326. p.weight, p.notes, p.bin, p.partsgroup_id,
  327. ch.accno AS income_accno,
  328. ch.description AS income_description,
  329. pr.customer_id, c.name AS customer,
  330. pg.partsgroup
  331. FROM project pr
  332. LEFT JOIN parts p ON (p.id = pr.parts_id)
  333. LEFT JOIN chart ch ON (ch.id = p.income_accno_id)
  334. LEFT JOIN customer c ON (c.id = pr.customer_id)
  335. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  336. WHERE pr.id = |.$dbh->quote($form->{id});
  337. } else {
  338. $query = qq|
  339. SELECT value, current_date AS startdate FROM defaults
  340. WHERE setting_key = 'weightunit'|;
  341. }
  342. $sth = $dbh->prepare($query);
  343. $sth->execute || $form->dberror($query);
  344. $ref = $sth->fetchrow_hashref(NAME_lc);
  345. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  346. $sth->finish;
  347. if ($form->{id}) {
  348. # check if it is orphaned
  349. $query = qq|
  350. SELECT count(*)
  351. FROM invoice
  352. WHERE project_id = ?
  353. UNION
  354. SELECT count(*)
  355. FROM orderitems
  356. WHERE project_id = ?
  357. UNION
  358. SELECT count(*)
  359. FROM jcitems
  360. WHERE project_id = ?|;
  361. $sth = $dbh->prepare($query);
  362. $sth->execute(
  363. $form->{id}, $form->{id}, $form->{id}
  364. )|| $form->dberror($query);
  365. my $count;
  366. my $count;
  367. while (($count) = $sth->fetchrow_array) {
  368. $form->{orphaned} += $count;
  369. }
  370. $sth->finish;
  371. }
  372. $form->{orphaned} = !$form->{orphaned};
  373. $query = qq|
  374. SELECT accno, description, link
  375. FROM chart
  376. WHERE link LIKE ?
  377. ORDER BY accno|;
  378. $sth = $dbh->prepare($query);
  379. $sth->execute('%IC%') || $form->dberror($query);
  380. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  381. for (split /:/, $ref->{link}) {
  382. if (/IC/) {
  383. push @{ $form->{IC_links}{$_} },
  384. { accno => $ref->{accno},
  385. description => $ref->{description} };
  386. }
  387. }
  388. }
  389. $sth->finish;
  390. if ($form->{id}) {
  391. $query = qq|
  392. SELECT ch.accno
  393. FROM parts p
  394. JOIN partstax pt ON (pt.parts_id = p.id)
  395. JOIN chart ch ON (pt.chart_id = ch.id)
  396. WHERE p.id = ?|;
  397. $sth = $dbh->prepare($query);
  398. $sth->execute($form->{id}) || $form->dberror($query);
  399. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  400. $form->{amount}{$ref->{accno}} = $ref->{accno};
  401. }
  402. $sth->finish;
  403. }
  404. PE->get_customer($myconfig, $form, $dbh);
  405. $dbh->commit;
  406. }
  407. sub get_customer {
  408. my ($self, $myconfig, $form, $dbh) = @_;
  409. if (! $dbh) {
  410. $dbh = $form->{dbh};
  411. }
  412. my $query;
  413. my $sth;
  414. my $ref;
  415. if (! $form->{startdate}) {
  416. $query = qq|SELECT current_date|;
  417. ($form->{startdate}) = $dbh->selectrow_array($query);
  418. }
  419. my $where = qq|(startdate >= |.$dbh->quote($form->{startdate}).
  420. qq| OR startdate IS NULL OR enddate IS NULL)|;
  421. if ($form->{enddate}) {
  422. $where .= qq| AND (enddate >= |.$dbh->quote($form->{enddate}).
  423. qq| OR enddate IS NULL)|;
  424. } else {
  425. $where .=
  426. qq| AND (enddate >= current_date OR enddate IS NULL)|;
  427. }
  428. $query = qq|
  429. SELECT count(*)
  430. FROM customer
  431. WHERE $where|;
  432. my ($count) = $dbh->selectrow_array($query);
  433. if ($count < $myconfig->{vclimit}) {
  434. $query = qq|
  435. SELECT id, name
  436. FROM customer
  437. WHERE $where|;
  438. if ($form->{customer_id}) {
  439. $query .= qq|
  440. UNION
  441. SELECT id,name
  442. FROM customer
  443. WHERE id = |.
  444. $dbh->quote($form->{customer_id});
  445. }
  446. $query .= qq|
  447. ORDER BY name|;
  448. $sth = $dbh->prepare($query);
  449. $sth->execute || $form->dberror($query);
  450. @{ $form->{all_customer} } = ();
  451. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  452. push @{ $form->{all_customer} }, $ref;
  453. }
  454. $sth->finish;
  455. }
  456. }
  457. sub save_job {
  458. my ($self, $myconfig, $form) = @_;
  459. my $dbh = $form->{dbh};
  460. my ($income_accno) = split /--/, $form->{IC_income};
  461. my ($partsgroup, $partsgroup_id) = split /--/, $form->{partsgroup};
  462. if ($form->{id}) {
  463. $query = qq|
  464. SELECT id FROM project
  465. WHERE id = |.$dbh->quote($form->{id});
  466. ($form->{id}) = $dbh->selectrow_array($query);
  467. }
  468. if (!$form->{id}) {
  469. my $uid = localtime;
  470. $uid .= "$$";
  471. $query = qq|
  472. INSERT INTO project (projectnumber)
  473. VALUES ('$uid')|;
  474. $dbh->do($query) || $form->dberror($query);
  475. $query = qq|
  476. SELECT id FROM project
  477. WHERE projectnumber = '$uid'|;
  478. ($form->{id}) = $dbh->selectrow_array($query);
  479. }
  480. $form->{projectnumber}
  481. = $form->update_defaults($myconfig, "projectnumber", $dbh)
  482. unless $form->{projectnumber};
  483. $query = qq|
  484. UPDATE project
  485. SET projectnumber = ?,
  486. description = ?,
  487. startdate = ?,
  488. enddate = ?,
  489. parts_id = ?
  490. production = ?,
  491. customer_id = ?
  492. WHERE id = ?|;
  493. $sth = $dbh->prepare($query);
  494. $sth->execute(
  495. $form->{projectnumber}, $form->{description},
  496. $form->{startdate}, $form->{enddate}, $form->{id},
  497. $form->{production}, $form->{customer_id}, $form->{id}
  498. ) || $form->dberror($query);
  499. #### add/edit assembly
  500. $query = qq|SELECT id FROM parts WHERE id = |.$dbh->quote($form->{id});
  501. my ($id) = $dbh->selectrow_array($query);
  502. if (!$id) {
  503. $query = qq|
  504. INSERT INTO parts (id)
  505. VALUES (|.$dbh->quote($form->{id}).qq|)|;
  506. $dbh->do($query) || $form->dberror($query);
  507. }
  508. my $partnumber =
  509. ($form->{partnumber})
  510. ? $form->{partnumber}
  511. : $form->{projectnumber};
  512. $query = qq|
  513. UPDATE parts
  514. SET partnumber = ?,
  515. description = ?,
  516. priceupdate = ?,
  517. listprice = ?,
  518. sellprice = ?,
  519. weight = ?,
  520. bin = ?,
  521. unit = ?,
  522. notes = ?,
  523. income_accno_id = (SELECT id FROM chart
  524. WHERE accno = ?),
  525. partsgroup_id = ?,
  526. assembly = '1',
  527. obsolete = '1',
  528. project_id = ?
  529. WHERE id = ?|;
  530. $sth = $dbh->prepare($query);
  531. $sth->execute(
  532. $partnumber, $form->{partdescription},
  533. $form->{priceupdate},
  534. $form->parse_amount($myconfig, $form->{listprice}),
  535. $form->parse_amount($myconfig, $form->{sellprice}),
  536. $form->parse_amount($myconfig, $form->{weight}),
  537. $form->{bin}, $form->{unit}, $form->{notes},
  538. $income_accno,
  539. ($partsgroup_id) ? $partsgroup_id : undef,
  540. $form->{id}, $form->{id}
  541. ) || $form->dberror($query);
  542. $query = qq|DELETE FROM partstax WHERE parts_id = |.
  543. $dbh->qupte($form->{id});
  544. $dbh->do($query) || $form->dberror($query);
  545. $query = qq|
  546. INSERT INTO partstax (parts_id, chart_id)
  547. VALUES (?, (SELECT id FROM chart WHERE accno = ?))|;
  548. $sth = $dbh->prepare($query);
  549. for (split / /, $form->{taxaccounts}) {
  550. if ($form->{"IC_tax_$_"}) {
  551. $sth->execute($form->{id}, $_)
  552. || $form->dberror($query);
  553. }
  554. }
  555. $dbh->commit;
  556. }
  557. sub stock_assembly {
  558. my ($self, $myconfig, $form) = @_;
  559. my $dbh = $form->{dbh};
  560. my $ref;
  561. my $query = qq|SELECT * FROM project WHERE id = ?|;
  562. my $sth = $dbh->prepare($query) || $form->dberror($query);
  563. $query = qq|SELECT COUNT(*) FROM parts WHERE project_id = ?|;
  564. my $rvh = $dbh->prepare($query) || $form->dberror($query);
  565. if (! $form->{stockingdate}) {
  566. $query = qq|SELECT current_date|;
  567. ($form->{stockingdate}) = $dbh->selectrow_array($query);
  568. }
  569. $query = qq|SELECT * FROM parts WHERE id = ?|;
  570. my $pth = $dbh->prepare($query) || $form->dberror($query);
  571. $query = qq|
  572. SELECT j.*, p.lastcost FROM jcitems j
  573. JOIN parts p ON (p.id = j.parts_id)
  574. WHERE j.project_id = ?
  575. AND j.checkedin <= |.
  576. $dbh->quote($form->{stockingdate}).qq|
  577. ORDER BY parts_id|;
  578. my $jth = $dbh->prepare($query) || $form->dberror($query);
  579. $query = qq|
  580. INSERT INTO assembly (id, parts_id, qty, bom, adj)
  581. VALUES (?, ?, ?, '0', '0')|;
  582. my $ath = $dbh->prepare($query) || $form->dberror($query);
  583. my $i = 0;
  584. my $sold;
  585. my $ship;
  586. while (1) {
  587. $i++;
  588. last unless $form->{"id_$i"};
  589. $stock = $form->parse_amount($myconfig, $form->{"stock_$i"});
  590. if ($stock) {
  591. $sth->execute($form->{"id_$i"});
  592. $ref = $sth->fetchrow_hashref(NAME_lc);
  593. if ($stock >($ref->{production} - $ref->{completed})) {
  594. $stock = $ref->{production}
  595. - $ref->{completed};
  596. }
  597. if (($stock * -1) > $ref->{completed}) {
  598. $stock = $ref->{completed} * -1;
  599. }
  600. $pth->execute($form->{"id_$i"});
  601. $pref = $pth->fetchrow_hashref(NAME_lc);
  602. my %assembly = ();
  603. my $lastcost = 0;
  604. my $sellprice = 0;
  605. my $listprice = 0;
  606. $jth->execute($form->{"id_$i"});
  607. while ($jref = $jth->fetchrow_hashref(NAME_lc)) {
  608. $assembly{qty}{$jref->{parts_id}}
  609. += ($jref->{qty} - $jref->{allocated});
  610. $assembly{parts_id}{$jref->{parts_id}}
  611. = $jref->{parts_id};
  612. $assembly{jcitems}{$jref->{id}} = $jref->{id};
  613. $lastcost += $form->round_amount(
  614. $jref->{lastcost} * ($jref->{qty}
  615. - $jref->{allocated}),
  616. 2);
  617. $sellprice += $form->round_amount(
  618. $jref->{sellprice} * ($jref->{qty}
  619. - $jref->{allocated}),
  620. 2);
  621. $listprice += $form->round_amount(
  622. $jref->{listprice} * ($jref->{qty}
  623. - $jref->{allocated}),
  624. 2);
  625. }
  626. $jth->finish;
  627. $uid = localtime;
  628. $uid .= "$$";
  629. $query = qq|
  630. INSERT INTO parts (partnumber)
  631. VALUES ('$uid')|;
  632. $dbh->do($query) || $form->dberror($query);
  633. $query = qq|
  634. SELECT id
  635. FROM parts
  636. WHERE partnumber = '$uid'|;
  637. ($uid) = $dbh->selectrow_array($query);
  638. $lastcost = $form->round_amount($lastcost / $stock, 2);
  639. $sellprice =
  640. ($pref->{sellprice})
  641. ? $pref->{sellprice}
  642. : $form->round_amount($sellprice / $stock, 2);
  643. $listprice =
  644. ($pref->{listprice})
  645. ? $pref->{listprice}
  646. : $form->round_amount($listprice / $stock, 2);
  647. $rvh->execute($form->{"id_$i"});
  648. my ($rev) = $rvh->fetchrow_array;
  649. $rvh->finish;
  650. $query = qq|
  651. UPDATE parts
  652. SET partnumber = ?,
  653. description = ?,
  654. priceupdate = ?,
  655. unit = ?,
  656. listprice = ?,
  657. sellprice = ?,
  658. lastcost = ?,
  659. weight = ?,
  660. onhand = ?,
  661. notes = ?,
  662. assembly = '1',
  663. income_accno_id = ?,
  664. bin = ?,
  665. project_id = ?
  666. WHERE id = ?|;
  667. $sth = $dbh->prepare($query);
  668. $sth->execute(
  669. "$pref->{partnumber}-$rev",
  670. $pref->{partdescription},
  671. $form->{stockingdate}, $pref->{unit},
  672. $listprice, $sellprice, $lastcost,
  673. $pref->{weight}, $stock, $pref->{notes},
  674. $pref->{income_accno_id}, $pref->{bin},
  675. $form->{"id_$i"}, $uid
  676. )|| $form->dberror($query);
  677. $query = qq|
  678. INSERT INTO partstax (parts_id, chart_id)
  679. SELECT ?, chart_id FROM partstax
  680. WHERE parts_id = ?|;
  681. $sth = $dbh->prepare($query);
  682. $sth->execute($uid, $pref->{id})
  683. || $form->dberror($query);
  684. $pth->finish;
  685. for (keys %{$assembly{parts_id}}) {
  686. if ($assembly{qty}{$_}) {
  687. $ath->execute(
  688. $uid, $assembly{parts_id}{$_},
  689. $form->round_amount(
  690. $assembly{qty}{$_}
  691. / $stock,
  692. 4));
  693. $ath->finish;
  694. }
  695. }
  696. $form->update_balance(
  697. $dbh, "project", "completed",
  698. qq|id = $form->{"id_$i"}|, $stock);
  699. $query = qq|
  700. UPDATE jcitems
  701. SET allocated = qty
  702. WHERE allocated != qty
  703. AND checkedin <= ?
  704. AND project_id = ?|;
  705. $sth = $dbh->prepare($query);
  706. $sth->execute($form->{stockingdate}, $form->{"id_$i"})
  707. || $form->dberror($query);
  708. $sth->finish;
  709. }
  710. }
  711. my $rc = $dbh->commit;
  712. $rc;
  713. }
  714. sub delete_project {
  715. my ($self, $myconfig, $form) = @_;
  716. my $dbh = $form->{dbh};
  717. $query = qq|DELETE FROM project WHERE id = ?|;
  718. $sth = $dbh->prepare($query);
  719. $sth->execute($form->{id}) || $form->dberror($query);
  720. $query = qq|DELETE FROM translation
  721. WHERE trans_id = $form->{id}|;
  722. $sth = $dbh->prepare($query);
  723. $sth->execute($form->{id}) || $form->dberror($query);
  724. my $rc = $dbh->commit;
  725. $rc;
  726. }
  727. sub delete_partsgroup {
  728. my ($self, $myconfig, $form) = @_;
  729. my $dbh = $form->{dbh};
  730. $query = qq|DELETE FROM partsgroup WHERE id = ?|;
  731. $sth = $dbh->prepare($query);
  732. $sth->execute($form->{id}) || $form->dberror($query);
  733. $query = qq|DELETE FROM translation WHERE trans_id = ?|;
  734. $sth = $dbh->prepare($query);
  735. $sth->execute($form->{id}) || $form->dberror($query);
  736. my $rc = $dbh->commit;
  737. $rc;
  738. }
  739. sub delete_pricegroup {
  740. my ($self, $myconfig, $form) = @_;
  741. my $dbh = $form->{dbh};
  742. $query = qq|DELETE FROM pricegroup WHERE id = ?|;
  743. $sth = $dbh->prepare($query);
  744. $sth->execute($form->{id}) || $form->dberror($query);
  745. my $rc = $dbh->commit;
  746. $rc;
  747. }
  748. sub delete_job {
  749. my ($self, $myconfig, $form) = @_;
  750. my $dbh = $form->{dbh};
  751. my %audittrail = (
  752. tablename => 'project',
  753. reference => $form->{id},
  754. formname => $form->{type},
  755. action => 'deleted',
  756. id => $form->{id} );
  757. $form->audittrail($dbh, "", \%audittrail);
  758. my $query = qq|DELETE FROM project WHERE id = ?|;
  759. $sth = $dbh->prepare($query);
  760. $sth->execute($form->{id}) || $form->dberror($query);
  761. $query = qq|DELETE FROM translation WHERE trans_id = ?|;
  762. $sth = $dbh->prepare($query);
  763. $sth->execute($form->{id}) || $form->dberror($query);
  764. # delete all the assemblies
  765. $query = qq|
  766. DELETE FROM assembly a
  767. JOIN parts p ON (a.id = p.id)
  768. WHERE p.project_id = ?|;
  769. $sth = $dbh->prepare($query);
  770. $sth->execute($form->{id}) || $form->dberror($query);
  771. $query = qq|DELETE FROM parts WHERE project_id = ?|;
  772. $sth = $dbh->prepare($query);
  773. $sth->execute($form->{id}) || $form->dberror($query);
  774. my $rc = $dbh->commit;
  775. $rc;
  776. }
  777. sub partsgroups {
  778. my ($self, $myconfig, $form) = @_;
  779. my $var;
  780. my $dbh = $form->{dbh};
  781. $form->{sort} = "partsgroup" unless $form->{partsgroup};
  782. my @a = (partsgroup);
  783. my $sortorder = $form->sort_order(\@a);
  784. my $query = qq|SELECT g.* FROM partsgroup g|;
  785. my $where = "1 = 1";
  786. if ($form->{partsgroup} ne "") {
  787. $var = $dbh->quote($form->like(lc $form->{partsgroup}));
  788. $where .= " AND lower(partsgroup) LIKE '$var'";
  789. }
  790. $query .= qq| WHERE $where ORDER BY $sortorder|;
  791. if ($form->{status} eq 'orphaned') {
  792. $query = qq|
  793. SELECT g.*
  794. FROM partsgroup g
  795. LEFT JOIN parts p ON (p.partsgroup_id = g.id)
  796. WHERE $where
  797. EXCEPT
  798. SELECT g.*
  799. FROM partsgroup g
  800. JOIN parts p ON (p.partsgroup_id = g.id)
  801. WHERE $where
  802. ORDER BY $sortorder|;
  803. }
  804. $sth = $dbh->prepare($query);
  805. $sth->execute || $form->dberror($query);
  806. my $i = 0;
  807. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  808. push @{ $form->{item_list} }, $ref;
  809. $i++;
  810. }
  811. $sth->finish;
  812. $i;
  813. }
  814. sub save_partsgroup {
  815. my ($self, $myconfig, $form) = @_;
  816. my $dbh = $form->{dbh};
  817. if ($form->{id}) {
  818. $query = qq|
  819. UPDATE partsgroup
  820. SET partsgroup = |.
  821. $dbh->quote($form->{partsgroup}).qq|
  822. WHERE id = $form->{id}|;
  823. } else {
  824. $query = qq|
  825. INSERT INTO partsgroup (partsgroup)
  826. VALUES (|.$dbh->quote($form->{partsgroup}).qq|)|;
  827. }
  828. $dbh->do($query) || $form->dberror($query);
  829. $dbh->commit;
  830. }
  831. sub get_partsgroup {
  832. my ($self, $myconfig, $form) = @_;
  833. my $dbh = $form->{dbh};
  834. my $query = qq|SELECT * FROM partsgroup WHERE id = ?|;
  835. my $sth = $dbh->prepare($query);
  836. $sth->execute($form->{id}) || $form->dberror($query);
  837. my $ref = $sth->fetchrow_hashref(NAME_lc);
  838. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  839. $sth->finish;
  840. # check if it is orphaned
  841. $query = qq|SELECT count(*) FROM parts WHERE partsgroup_id = ?|;
  842. $sth = $dbh->prepare($query);
  843. $sth->execute($form->{id}) || $form->dberror($query);
  844. ($form->{orphaned}) = $sth->fetchrow_array;
  845. $form->{orphaned} = !$form->{orphaned};
  846. $sth->finish;
  847. $dbh->commit;
  848. }
  849. sub pricegroups {
  850. my ($self, $myconfig, $form) = @_;
  851. my $var;
  852. my $dbh = $form->{dbh};
  853. $form->{sort} = "pricegroup" unless $form->{sort};
  854. my @a = (pricegroup);
  855. my $sortorder = $form->sort_order(\@a);
  856. my $query = qq|SELECT g.* FROM pricegroup g|;
  857. my $where = "1 = 1";
  858. if ($form->{pricegroup} ne "") {
  859. $var = $dbh->quote($form->like(lc $form->{pricegroup}));
  860. $where .= " AND lower(pricegroup) LIKE $var";
  861. }
  862. $query .= qq|
  863. WHERE $where ORDER BY $sortorder|;
  864. if ($form->{status} eq 'orphaned') {
  865. $query = qq|
  866. SELECT g.*
  867. FROM pricegroup g
  868. WHERE $where
  869. AND g.id NOT IN (SELECT DISTINCT pricegroup_id
  870. FROM partscustomer
  871. WHERE pricegroup_id > 0)
  872. ORDER BY $sortorder|;
  873. }
  874. $sth = $dbh->prepare($query);
  875. $sth->execute || $form->dberror($query);
  876. my $i = 0;
  877. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  878. push @{ $form->{item_list} }, $ref;
  879. $i++;
  880. }
  881. $sth->finish;
  882. $dbh->commit;
  883. $i;
  884. }
  885. sub save_pricegroup {
  886. my ($self, $myconfig, $form) = @_;
  887. my $dbh = $form->{dbh};
  888. if ($form->{id}) {
  889. $query = qq|
  890. UPDATE pricegroup SET
  891. pricegroup = ?
  892. WHERE id = |.$dbh->quote($form->{id});
  893. } else {
  894. $query = qq|
  895. INSERT INTO pricegroup (pricegroup)
  896. VALUES (?)|;
  897. }
  898. $sth = $dbh->prepare($query);
  899. $sth->execute($form->{pricegroup}) || $form->dberror($query);
  900. $dbh->commit;
  901. }
  902. sub get_pricegroup {
  903. my ($self, $myconfig, $form) = @_;
  904. my $dbh = $form->{dbh};
  905. my $query = qq|SELECT * FROM pricegroup WHERE id = ?|;
  906. my $sth = $dbh->prepare($query);
  907. $sth->execute($form->{id}) || $form->dberror($query);
  908. my $ref = $sth->fetchrow_hashref(NAME_lc);
  909. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  910. $sth->finish;
  911. # check if it is orphaned
  912. $query = "SELECT count(*) FROM partscustomer WHERE pricegroup_id = ?";
  913. $sth = $dbh->prepare($query);
  914. $sth->execute($form->{id}) || $form->dberror($query);
  915. ($form->{orphaned}) = $sth->fetchrow_array;
  916. $form->{orphaned} = !$form->{orphaned};
  917. $sth->finish;
  918. $dbh->commit;
  919. }
  920. sub description_translations {
  921. my ($self, $myconfig, $form) = @_;
  922. my $dbh = $form->{dbh};
  923. my $where = "1 = 1";
  924. my $var;
  925. my $ref;
  926. for (qw(partnumber description)) {
  927. if ($form->{$_}) {
  928. $var = $dbh->quote($form->like(lc $form->{$_}));
  929. $where .= " AND lower(p.$_) LIKE $var";
  930. }
  931. }
  932. $where .= " AND p.obsolete = '0'";
  933. $where .= " AND p.id = ".$dbh->quote($form->{id}) if $form->{id};
  934. my %ordinal = ( 'partnumber' => 2, 'description' => 3 );
  935. my @a = qw(partnumber description);
  936. my $sortorder = $form->sort_order(\@a, \%ordinal);
  937. my $query = qq|
  938. SELECT l.description AS language,
  939. t.description AS translation, l.code
  940. FROM translation t
  941. JOIN language l ON (l.code = t.language_code)
  942. WHERE trans_id = ?
  943. ORDER BY 1|;
  944. my $tth = $dbh->prepare($query);
  945. $query = qq|
  946. SELECT p.id, p.partnumber, p.description
  947. FROM parts p
  948. WHERE $where
  949. ORDER BY $sortorder|;
  950. my $sth = $dbh->prepare($query);
  951. $sth->execute || $form->dberror($query);
  952. my $tra;
  953. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  954. push @{ $form->{translations} }, $ref;
  955. # get translations for description
  956. $tth->execute($ref->{id}) || $form->dberror;
  957. while ($tra = $tth->fetchrow_hashref(NAME_lc)) {
  958. $form->{trans_id} = $ref->{id};
  959. $tra->{id} = $ref->{id};
  960. push @{ $form->{translations} }, $tra;
  961. }
  962. $tth->finish;
  963. }
  964. $sth->finish;
  965. &get_language("", $dbh, $form) if $form->{id};
  966. $dbh->commit;
  967. }
  968. sub partsgroup_translations {
  969. my ($self, $myconfig, $form) = @_;
  970. my $dbh = $form->{dbh};
  971. my $where = "1 = 1";
  972. my $ref;
  973. my $var;
  974. if ($form->{description}) {
  975. $var = $dbh->quote($form->like(lc $form->{description}));
  976. $where .= " AND lower(p.partsgroup) LIKE $var";
  977. }
  978. $where .= " AND p.id = ".$dbh->quote($form->{id}) if $form->{id};
  979. my $query = qq|
  980. SELECT l.description AS language,
  981. t.description AS translation, l.code
  982. FROM translation t
  983. JOIN language l ON (l.code = t.language_code)
  984. WHERE trans_id = ?
  985. ORDER BY 1|;
  986. my $tth = $dbh->prepare($query);
  987. $form->sort_order();
  988. $query = qq|
  989. SELECT p.id, p.partsgroup AS description
  990. FROM partsgroup p
  991. WHERE $where
  992. ORDER BY 2 $form->{direction}|;
  993. my $sth = $dbh->prepare($query);
  994. $sth->execute || $form->dberror($query);
  995. my $tra;
  996. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  997. push @{ $form->{translations} }, $ref;
  998. # get translations for partsgroup
  999. $tth->execute($ref->{id}) || $form->dberror;
  1000. while ($tra = $tth->fetchrow_hashref(NAME_lc)) {
  1001. $form->{trans_id} = $ref->{id};
  1002. push @{ $form->{translations} }, $tra;
  1003. }
  1004. $tth->finish;
  1005. }
  1006. $sth->finish;
  1007. &get_language("", $dbh, $form) if $form->{id};
  1008. $dbh->commit;
  1009. }
  1010. sub project_translations {
  1011. my ($self, $myconfig, $form) = @_;
  1012. my $dbh = $form->{dbh};
  1013. my $where = "1 = 1";
  1014. my $var;
  1015. my $ref;
  1016. for (qw(projectnumber description)) {
  1017. if ($form->{$_}) {
  1018. $var = $dbh->quote($form->like(lc $form->{$_}));
  1019. $where .= " AND lower(p.$_) LIKE $var";
  1020. }
  1021. }
  1022. $where .= " AND p.id = ".$dbh->quote($form->{id}) if $form->{id};
  1023. my %ordinal = ( 'projectnumber' => 2, 'description' => 3 );
  1024. my @a = qw(projectnumber description);
  1025. my $sortorder = $form->sort_order(\@a, \%ordinal);
  1026. my $query = qq|
  1027. SELECT l.description AS language,
  1028. t.description AS translation, l.code
  1029. FROM translation t
  1030. JOIN language l ON (l.code = t.language_code)
  1031. WHERE trans_id = ?
  1032. ORDER BY 1|;
  1033. my $tth = $dbh->prepare($query);
  1034. $query = qq|
  1035. SELECT p.id, p.projectnumber, p.description
  1036. FROM project p
  1037. WHERE $where
  1038. ORDER BY $sortorder|;
  1039. my $sth = $dbh->prepare($query);
  1040. $sth->execute || $form->dberror($query);
  1041. my $tra;
  1042. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1043. push @{ $form->{translations} }, $ref;
  1044. # get translations for description
  1045. $tth->execute($ref->{id}) || $form->dberror;
  1046. while ($tra = $tth->fetchrow_hashref(NAME_lc)) {
  1047. $form->{trans_id} = $ref->{id};
  1048. $tra->{id} = $ref->{id};
  1049. push @{ $form->{translations} }, $tra;
  1050. }
  1051. $tth->finish;
  1052. }
  1053. $sth->finish;
  1054. &get_language("", $dbh, $form) if $form->{id};
  1055. $dbh->commit;
  1056. }
  1057. sub get_language {
  1058. my ($self, $dbh, $form) = @_;
  1059. my $query = qq|SELECT * FROM language ORDER BY 2|;
  1060. my $sth = $dbh->prepare($query);
  1061. $sth->execute || $form->dberror($query);
  1062. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  1063. push @{ $form->{all_language} }, $ref;
  1064. }
  1065. $sth->finish;
  1066. }
  1067. sub save_translation {
  1068. my ($self, $myconfig, $form) = @_;
  1069. my $dbh = $form->{dbh};
  1070. my $query = qq|DELETE FROM translation WHERE trans_id = ?|;
  1071. $sth = $dbh->prepare($query);
  1072. $sth->execute($form->{id})|| $form->dberror($query);
  1073. $query = qq|
  1074. INSERT INTO translation (trans_id, language_code, description)
  1075. VALUES (?, ?, ?)|;
  1076. my $sth = $dbh->prepare($query) || $form->dberror($query);
  1077. foreach my $i (1 .. $form->{translation_rows}) {
  1078. if ($form->{"language_code_$i"} ne "") {
  1079. $sth->execute($form->{id}, $form->{"language_code_$i"},
  1080. $form->{"translation_$i"});
  1081. $sth->finish;
  1082. }
  1083. }
  1084. $dbh->commit;
  1085. }
  1086. sub delete_translation {
  1087. my ($self, $myconfig, $form) = @_;
  1088. my $dbh = $form->{dbh};
  1089. my $query = qq|DELETE FROM translation WHERE trans_id = ?|;
  1090. $sth = $dbh->prepare($query);
  1091. $sth->execute($form->{id})|| $form->dberror($query);
  1092. $dbh->commit;
  1093. }
  1094. sub project_sales_order {
  1095. my ($self, $myconfig, $form) = @_;
  1096. # connect to database
  1097. my $dbh = $form->{dbh};
  1098. my $query = qq|SELECT current_date|;
  1099. my ($transdate) = $dbh->selectrow_array($query);
  1100. $form->all_years($myconfig, $dbh);
  1101. $form->all_projects($myconfig, $dbh, $transdate);
  1102. $form->all_employees($myconfig, $dbh, $transdate);
  1103. $dbh->commit;
  1104. }
  1105. sub get_jcitems {
  1106. my ($self, $myconfig, $form) = @_;
  1107. my $dbh = $form->{dbh};
  1108. my $null;
  1109. my $var;
  1110. my $where;
  1111. if ($form->{projectnumber}) {
  1112. ($null, $var) = split /--/, $form->{projectnumber};
  1113. $var = $dbh->quote($var);
  1114. $where .= " AND j.project_id = $var";
  1115. }
  1116. if ($form->{employee}) {
  1117. ($null, $var) = split /--/, $form->{employee};
  1118. $var = $dbh->quote($var);
  1119. $where .= " AND j.employee_id = $var";
  1120. }
  1121. ($form->{transdatefrom}, $form->{transdateto})
  1122. = $form->from_to(
  1123. $form->{year}, $form->{month}, $form->{interval})
  1124. if $form->{year} && $form->{month};
  1125. if ($form->{transdatefrom}) {
  1126. $where .= " AND j.checkedin >= ".
  1127. $dbh->quote($form->{transdatefrom});
  1128. }
  1129. if ($form->{transdateto}) {
  1130. $where .= " AND j.checkedout <= (date ".
  1131. $dbh->quote($form->{transdateto}) .
  1132. " + interval '1 days')";
  1133. }
  1134. my $query;
  1135. my $ref;
  1136. $query = qq|
  1137. SELECT j.id, j.description, j.qty - j.allocated AS qty,
  1138. j.sellprice, j.parts_id, pr.$form->{vc}_id,
  1139. j.project_id, j.checkedin::date AS transdate,
  1140. j.notes, c.name AS $form->{vc}, pr.projectnumber,
  1141. p.partnumber
  1142. FROM jcitems j
  1143. JOIN project pr ON (pr.id = j.project_id)
  1144. JOIN employees e ON (e.id = j.employee_id)
  1145. JOIN parts p ON (p.id = j.parts_id)
  1146. LEFT JOIN $form->{vc} c ON (c.id = pr.$form->{vc}_id)
  1147. WHERE pr.parts_id IS NULL
  1148. AND j.allocated != j.qty $where
  1149. ORDER BY pr.projectnumber, c.name, j.checkedin::date|;
  1150. if ($form->{summary}) {
  1151. $query =~ s/j\.description/p\.description/;
  1152. $query =~ s/c\.name,/c\.name, j\.parts_id, /;
  1153. }
  1154. $sth = $dbh->prepare($query);
  1155. $sth->execute || $form->dberror($query);
  1156. # tax accounts
  1157. $query = qq|
  1158. SELECT c.accno
  1159. FROM chart c
  1160. JOIN partstax pt ON (pt.chart_id = c.id)
  1161. WHERE pt.parts_id = ?|;
  1162. my $tth = $dbh->prepare($query) || $form->dberror($query);
  1163. my $ptref;
  1164. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1165. $tth->execute($ref->{parts_id});
  1166. $ref->{taxaccounts} = "";
  1167. while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
  1168. $ref->{taxaccounts} .= "$ptref->{accno} ";
  1169. }
  1170. $tth->finish;
  1171. chop $ref->{taxaccounts};
  1172. $ref->{amount} = $ref->{sellprice} * $ref->{qty};
  1173. push @{ $form->{jcitems} }, $ref;
  1174. }
  1175. $sth->finish;
  1176. $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
  1177. ($form->{currency}) = $dbh->selectrow_array($query);
  1178. $form->{currency} =~ s/:.*//;
  1179. $form->{defaultcurrency} = $form->{currency};
  1180. $query = qq|
  1181. SELECT c.accno, t.rate
  1182. FROM tax t
  1183. JOIN chart c ON (c.id = t.chart_id)|;
  1184. $sth = $dbh->prepare($query);
  1185. $sth->execute || $form->dberror($query);
  1186. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1187. $form->{taxaccounts} .= "$ref->{accno} ";
  1188. $form->{"$ref->{accno}_rate"} = $ref->{rate};
  1189. }
  1190. chop $form->{taxaccounts};
  1191. $sth->finish;
  1192. $dbh->commit;
  1193. }
  1194. sub allocate_projectitems {
  1195. my ($self, $myconfig, $form) = @_;
  1196. my $dbh = $form->{dbh};
  1197. for my $i (1 .. $form->{rowcount}) {
  1198. for (split / /, $form->{"jcitems_$i"}) {
  1199. my ($id, $qty) = split /:/, $_;
  1200. $form->update_balance(
  1201. $dbh, 'jcitems', 'allocated', "id = $id",
  1202. $qty);
  1203. }
  1204. }
  1205. $rc = $dbh->commit;
  1206. $rc;
  1207. }
  1208. 1;