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