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