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