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