summaryrefslogtreecommitdiff
path: root/LedgerSMB/OE.pm
blob: f6a1f6be06e0d4cc5af438a9b6d38b97a92b2fed (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) 2001
  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. # Order entry module
  31. # Quotation
  32. #
  33. #======================================================================
  34. package OE;
  35. use LedgerSMB::Tax;
  36. use LedgerSMB::Sysconfig;
  37. sub transactions {
  38. my ( $self, $myconfig, $form ) = @_;
  39. # connect to database
  40. my $dbh = $form->{dbh};
  41. my $query;
  42. my $null;
  43. my $var;
  44. my $ordnumber = 'ordnumber';
  45. my $quotation = '0';
  46. my $department;
  47. my $rate = ( $form->{vc} eq 'customer' ) ? 'buy' : 'sell';
  48. ( $form->{transdatefrom}, $form->{transdateto} ) =
  49. $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
  50. if $form->{year} && $form->{month};
  51. if ( $form->{type} =~ /_quotation$/ ) {
  52. $quotation = '1';
  53. $ordnumber = 'quonumber';
  54. }
  55. my $number = $form->like( lc $form->{$ordnumber} );
  56. my $name = $form->like( lc $form->{ $form->{vc} } );
  57. my @dptargs = ();
  58. for (qw(department employee)) {
  59. if ( $form->{$_} ) {
  60. ( $null, $var ) = split /--/, $form->{$_};
  61. $department .= " AND o.${_}_id = ?";
  62. push @dptargs, $var;
  63. }
  64. }
  65. if ( $form->{vc} ne 'customer' ) { # Sanitize $form->{vc}
  66. $form->{vc} = 'vendor';
  67. }
  68. my $query = qq|
  69. SELECT o.id, o.ordnumber, o.transdate, o.reqdate,
  70. o.amount, ct.name, o.netamount, o.$form->{vc}_id,
  71. ex.$rate AS exchangerate, o.closed, o.quonumber,
  72. o.shippingpoint, o.shipvia, e.name AS employee,
  73. m.name AS manager, o.curr, o.ponumber
  74. FROM oe o
  75. JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)
  76. LEFT JOIN employee e ON (o.employee_id = e.id)
  77. LEFT JOIN employee m ON (e.managerid = m.id)
  78. LEFT JOIN exchangerate ex
  79. ON (ex.curr = o.curr AND ex.transdate = o.transdate)
  80. WHERE o.quotation = ?
  81. $department|;
  82. my @queryargs = @dptargs;
  83. unshift @queryargs, $quotation;
  84. my %ordinal = (
  85. id => 1,
  86. ordnumber => 2,
  87. transdate => 3,
  88. reqdate => 4,
  89. name => 6,
  90. quonumber => 11,
  91. shipvia => 13,
  92. employee => 14,
  93. manager => 15,
  94. curr => 16,
  95. ponumber => 17
  96. );
  97. my @a = ( transdate, $ordnumber, name );
  98. push @a, "employee" if $form->{l_employee};
  99. if ( $form->{type} !~ /(ship|receive)_order/ ) {
  100. push @a, "manager" if $form->{l_manager};
  101. }
  102. my $sortorder = $form->sort_order( \@a, \%ordinal );
  103. # build query if type eq (ship|receive)_order
  104. if ( $form->{type} =~ /(ship|receive)_order/ ) {
  105. my ( $warehouse, $warehouse_id ) = split /--/, $form->{warehouse};
  106. $query = qq|
  107. SELECT DISTINCT o.id, o.ordnumber, o.transdate,
  108. o.reqdate, o.amount, ct.name, o.netamount,
  109. o.$form->{vc}_id, ex.$rate AS exchangerate,
  110. o.closed, o.quonumber, o.shippingpoint,
  111. o.shipvia, e.name AS employee, o.curr,
  112. o.ponumber
  113. FROM oe o
  114. JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)
  115. JOIN orderitems oi ON (oi.trans_id = o.id)
  116. JOIN parts p ON (p.id = oi.parts_id)|;
  117. if ( $warehouse_id && $form->{type} eq 'ship_order' ) {
  118. $query .= qq|
  119. JOIN inventory i ON (oi.parts_id = i.parts_id)
  120. |;
  121. }
  122. $query .= qq|
  123. LEFT JOIN employee e ON (o.employee_id = e.id)
  124. LEFT JOIN exchangerate ex
  125. ON (ex.curr = o.curr
  126. AND ex.transdate = o.transdate)
  127. WHERE o.quotation = '0'
  128. AND (p.inventory_accno_id > 0 OR p.assembly = '1')
  129. AND oi.qty != oi.ship
  130. $department|;
  131. @queryargs = @dptargs; #reset @queryargs
  132. if ( $warehouse_id && $form->{type} eq 'ship_order' ) {
  133. $query .= qq|
  134. AND i.warehouse_id = ?
  135. AND (
  136. SELECT SUM(i.qty)
  137. FROM inventory i
  138. WHERE oi.parts_id = i.parts_id
  139. AND i.warehouse_id = ?
  140. ) > 0|;
  141. push( @queryargs, $warehouse_id, $warehouse_id );
  142. }
  143. }
  144. if ( $form->{"$form->{vc}_id"} ) {
  145. $query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
  146. }
  147. elsif ( $form->{ $form->{vc} } ne "" ) {
  148. $query .= " AND lower(ct.name) LIKE ?";
  149. push @queryargs, $name;
  150. }
  151. if ( $form->{$ordnumber} ne "" ) {
  152. $ordnumber = ($ordnumber eq 'ordnumber') ? 'ordnumber' : 'quonumber';
  153. $query .= " AND lower($ordnumber) LIKE ?";
  154. push @queryargs, $number;
  155. $form->{open} = 1;
  156. $form->{closed} = 1;
  157. }
  158. if ( $form->{ponumber} ne "" ) {
  159. $query .= " AND lower(ponumber) LIKE ?";
  160. push @queryargs, $form->{ponumber};
  161. }
  162. if ( !$form->{open} && !$form->{closed} ) {
  163. $query .= " AND o.id = 0";
  164. }
  165. elsif ( !( $form->{open} && $form->{closed} ) ) {
  166. $query .=
  167. ( $form->{open} ) ? " AND o.closed = '0'" : " AND o.closed = '1'";
  168. }
  169. if ( $form->{shipvia} ne "" ) {
  170. $var = $form->like( lc $form->{shipvia} );
  171. $query .= " AND lower(o.shipvia) LIKE ?";
  172. push @queryargs, $var;
  173. }
  174. if ( $form->{description} ne "" ) {
  175. $var = $form->like( lc $form->{description} );
  176. $query .= " AND o.id IN (SELECT DISTINCT trans_id
  177. FROM orderitems
  178. WHERE lower(description) LIKE '$var')";
  179. push @queryargs, $var;
  180. }
  181. if ( $form->{transdatefrom} ) {
  182. $query .= " AND o.transdate >= ?";
  183. push @queryargs, $form->{transdatefrom};
  184. }
  185. if ( $form->{transdateto} ) {
  186. $query .= " AND o.transdate <= ?";
  187. push @queryargs, $form->{transdateto};
  188. }
  189. $query .= " ORDER by $sortorder";
  190. my $sth = $dbh->prepare($query);
  191. $sth->execute(@queryargs) || $form->dberror($query);
  192. my %oid = ();
  193. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  194. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  195. $ref->{exchangerate} = 1 unless $ref->{exchangerate};
  196. if ( $ref->{id} != $oid{id}{ $ref->{id} } ) {
  197. push @{ $form->{OE} }, $ref;
  198. $oid{vc}{ $ref->{curr} }{ $ref->{"$form->{vc}_id"} }++;
  199. }
  200. $oid{id}{ $ref->{id} } = $ref->{id};
  201. }
  202. $sth->finish;
  203. $dbh->commit;
  204. if ( $form->{type} =~ /^consolidate_/ ) {
  205. @a = ();
  206. foreach $ref ( @{ $form->{OE} } ) {
  207. push @a, $ref
  208. if $oid{vc}{ $ref->{curr} }{ $ref->{"$form->{vc}_id"} } > 1;
  209. }
  210. @{ $form->{OE} } = @a;
  211. }
  212. }
  213. sub save {
  214. my ( $self, $myconfig, $form ) = @_;
  215. $form->db_prepare_vars(
  216. "quonumber", "transdate", "vendor_id", "customer_id",
  217. "reqdate", "taxincluded", "shippingpoint", "shipvia",
  218. "currency", "department_id", "employee_id", "language_code",
  219. "ponumber", "terms"
  220. );
  221. # connect to database, turn off autocommit
  222. my $dbh = $form->{dbh};
  223. my @queryargs;
  224. my $quotation;
  225. my $ordnumber;
  226. my $numberfld;
  227. $form->{vc} = ( $form->{vc} eq 'customer' ) ? 'customer' : 'vendor';
  228. if ( $form->{type} =~ /_order$/ ) {
  229. $quotation = "0";
  230. $ordnumber = "ordnumber";
  231. $numberfld =
  232. ( $form->{vc} eq 'customer' )
  233. ? "sonumber"
  234. : "ponumber";
  235. }
  236. else {
  237. $quotation = "1";
  238. $ordnumber = "quonumber";
  239. $numberfld =
  240. ( $form->{vc} eq 'customer' )
  241. ? "sqnumber"
  242. : "rfqnumber";
  243. }
  244. $form->{"$ordnumber"} =
  245. $form->update_defaults( $myconfig, $numberfld, $dbh )
  246. unless $form->{ordnumber};
  247. my $query;
  248. my $sth;
  249. my $null;
  250. my $exchangerate = 0;
  251. ( $null, $form->{employee_id} ) = split /--/, $form->{employee};
  252. if ( !$form->{employee_id} ) {
  253. ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh);
  254. $form->{employee} = "$form->{employee}--$form->{employee_id}";
  255. }
  256. my $ml = ( $form->{type} eq 'sales_order' ) ? 1 : -1;
  257. $query = qq|
  258. SELECT p.assembly, p.project_id
  259. FROM parts p WHERE p.id = ?|;
  260. my $pth = $dbh->prepare($query) || $form->dberror($query);
  261. if ( $form->{id} ) {
  262. $query = qq|SELECT id FROM oe WHERE id = $form->{id}|;
  263. if ( $dbh->selectrow_array($query) ) {
  264. &adj_onhand( $dbh, $form, $ml )
  265. if $form->{type} =~ /_order$/;
  266. $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
  267. $sth = $dbh->prepare($query);
  268. $sth->execute( $form->{id} ) || $form->dberror($query);
  269. $query = qq|DELETE FROM shipto WHERE trans_id = ?|;
  270. $sth = $dbh->prepare($query);
  271. $sth->execute( $form->{id} ) || $form->dberror($query);
  272. }
  273. else { # id is not in the database
  274. delete $form->{id};
  275. }
  276. }
  277. my $did_insert = 0;
  278. if ( !$form->{id} ) {
  279. $query = qq|SELECT nextval('id')|;
  280. $sth = $dbh->prepare($query);
  281. $sth->execute || $form->dberror($query);
  282. ( $form->{id} ) = $sth->fetchrow_array;
  283. $sth->finish;
  284. my $uid = localtime;
  285. $uid .= "$$";
  286. if ( !$form->{reqdate} ) {
  287. $form->{reqdate} = undef;
  288. }
  289. if ( !$form->{transdate} ) {
  290. $form->{transdate} = "now";
  291. }
  292. if ( ( $form->{closed} ne 't' ) and ( $form->{closed} ne "1" ) ) {
  293. $form->{closed} = 'f';
  294. }
  295. # $form->{id} is safe because it is only pulled *from* the db.
  296. $query = qq|
  297. INSERT INTO oe
  298. (id, ordnumber, quonumber, transdate, vendor_id,
  299. customer_id, reqdate, shippingpoint, shipvia,
  300. notes, intnotes, curr, closed, department_id,
  301. employee_id, language_code, ponumber, terms,
  302. quotation)
  303. VALUES
  304. ($form->{id}, ?, ?, ?, ?,
  305. ?, ?, ?, ?,
  306. ?, ?, ?, ?, ?,
  307. ?, ?, ?, ?, ?)|;
  308. @queryargs = (
  309. $form->{ordnumber}, $form->{quonumber},
  310. $form->{transdate}, $form->{vendor_id},
  311. $form->{customer_id}, $form->{reqdate},
  312. $form->{shippingpoint}, $form->{shipvia},
  313. $form->{notes}, $form->{intnotes},
  314. $form->{currency}, $form->{closed},
  315. $form->{department_id}, $form->{employee_id},
  316. $form->{language_code}, $form->{ponumber},
  317. $form->{terms}, $quotation
  318. );
  319. $sth = $dbh->prepare($query);
  320. $sth->execute(@queryargs) || $form->dberror($query);
  321. $sth->finish;
  322. @queries = $form->run_custom_queries( 'oe', 'INSERT' );
  323. }
  324. my $amount;
  325. my $linetotal;
  326. my $discount;
  327. my $project_id;
  328. my $taxrate;
  329. my $taxamount;
  330. my $fxsellprice;
  331. my %taxbase;
  332. my @taxaccounts;
  333. my %taxaccounts;
  334. my $netamount = 0;
  335. my $rowcount = $form->{rowcount};
  336. for my $i ( 1 .. $rowcount ) {
  337. $form->{"ship_$i"} = 0 unless $form->{"ship_$i"};
  338. $form->db_prepare_vars( "orderitems_id_$i", "id_$i", "description_$i",
  339. "project_id_$i" );
  340. for (qw(qty ship)) {
  341. $form->{"${_}_$i"} =
  342. $form->parse_amount( $myconfig, $form->{"${_}_$i"} );
  343. }
  344. $form->{"discount_$i"} =
  345. $form->parse_amount( $myconfig, $form->{"discount_$i"} ) / 100;
  346. $form->{"sellprice_$i"} =
  347. $form->parse_amount( $myconfig, $form->{"sellprice_$i"} );
  348. if ( $form->{"qty_$i"} ) {
  349. $pth->execute( $form->{"id_$i"} );
  350. $ref = $pth->fetchrow_hashref(NAME_lc);
  351. for ( keys %$ref ) { $form->{"${_}_$i"} = $ref->{$_} }
  352. $pth->finish;
  353. $fxsellprice = $form->{"sellprice_$i"};
  354. my ($dec) = ( $form->{"sellprice_$i"} =~ /\.(\d+)/ );
  355. $dec = length $dec;
  356. my $decimalplaces = ( $dec > 2 ) ? $dec : 2;
  357. $discount =
  358. $form->round_amount(
  359. $form->{"sellprice_$i"} * $form->{"discount_$i"},
  360. $decimalplaces );
  361. $form->{"sellprice_$i"} =
  362. $form->round_amount( $form->{"sellprice_$i"} - $discount,
  363. $decimalplaces );
  364. $linetotal =
  365. $form->round_amount( $form->{"sellprice_$i"} * $form->{"qty_$i"},
  366. 2 );
  367. @taxaccounts = Tax::init_taxes( $form, $form->{"taxaccounts_$i"},
  368. $form->{taxaccounts} );
  369. if ( $form->{taxincluded} ) {
  370. $taxamount =
  371. Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 1 );
  372. $form->{"sellprice_$i"} =
  373. Tax::extract_taxes( \@taxaccounts, $form,
  374. $form->{"sellprice_$i"} );
  375. $taxbase =
  376. Tax::extract_taxes( \@taxaccounts, $form, $linetotal );
  377. }
  378. else {
  379. $taxamount =
  380. Tax::apply_taxes( \@taxaccounts, $form, $linetotal );
  381. $taxbase = $linetotal;
  382. }
  383. if ( @taxaccounts && $form->round_amount( $taxamount, 2 ) == 0 ) {
  384. if ( $form->{taxincluded} ) {
  385. foreach $item (@taxaccounts) {
  386. $taxamount = $form->round_amount( $item->value, 2 );
  387. $taxaccounts{ $item->account } += $taxamount;
  388. $taxdiff += $taxamount;
  389. $taxbase{ $item->account } += $taxbase;
  390. }
  391. $taxaccounts{ $taxaccounts[0]->account } += $taxdiff;
  392. }
  393. else {
  394. foreach $item (@taxaccounts) {
  395. $taxaccounts{ $item->account } += $item->value;
  396. $taxbase{ $item->account } += $taxbase;
  397. }
  398. }
  399. }
  400. else {
  401. foreach $item (@taxaccounts) {
  402. $taxaccounts{ $item->account } += $item->value;
  403. $taxbase{ $item->account } += $taxbase;
  404. }
  405. }
  406. $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"};
  407. if ( $form->{"projectnumber_$i"} ne "" ) {
  408. ( $null, $project_id ) = split /--/,
  409. $form->{"projectnumber_$i"};
  410. }
  411. $project_id = $form->{"project_id_$i"}
  412. if $form->{"project_id_$i"};
  413. if ( !$form->{"reqdate_$i"} ) {
  414. $form->{"reqdate_$i"} = undef;
  415. }
  416. @queryargs = ();
  417. # save detail record in orderitems table
  418. $query = qq|INSERT INTO orderitems (|;
  419. $query .= qq|
  420. trans_id, parts_id, description, qty, sellprice,
  421. discount, unit, reqdate, project_id, ship,
  422. serialnumber, notes)
  423. VALUES (|;
  424. $query .= qq| ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
  425. $sth = $dbh->prepare($query);
  426. push( @queryargs,
  427. $form->{id}, $form->{"id_$i"},
  428. $form->{"description_$i"}, $form->{"qty_$i"},
  429. $fxsellprice, $form->{"discount_$i"},
  430. $form->{"unit_$i"}, $form->{"reqdate_$i"},
  431. $project_id, $form->{"ship_$i"},
  432. $form->{"serialnumber_$i"}, $form->{"notes_$i"} );
  433. $sth->execute(@queryargs) || $form->dberror($query);
  434. $form->{"sellprice_$i"} = $fxsellprice;
  435. }
  436. $form->{"discount_$i"} *= 100;
  437. }
  438. # set values which could be empty
  439. for (qw(vendor_id customer_id taxincluded closed quotation)) {
  440. $form->{$_} *= 1;
  441. }
  442. # add up the tax
  443. my $tax = 0;
  444. for ( keys %taxaccounts ) { $tax += $taxaccounts{$_} }
  445. $amount = $form->round_amount( $netamount + $tax, 2 );
  446. $netamount = $form->round_amount( $netamount, 2 );
  447. if ( $form->{currency} eq $form->{defaultcurrency} ) {
  448. $form->{exchangerate} = 1;
  449. }
  450. else {
  451. $exchangerate =
  452. $form->check_exchangerate( $myconfig, $form->{currency},
  453. $form->{transdate},
  454. ( $form->{vc} eq 'customer' ) ? 'buy' : 'sell' );
  455. }
  456. $form->{exchangerate} =
  457. ($exchangerate)
  458. ? $exchangerate
  459. : $form->parse_amount( $myconfig, $form->{exchangerate} );
  460. ( $null, $form->{department_id} ) = split( /--/, $form->{department} );
  461. for (qw(department_id terms)) { $form->{$_} *= 1 }
  462. if ($did_insert) {
  463. $query = qq|
  464. UPDATE oe SET
  465. amount = ?,
  466. netamount = ?,
  467. taxincluded = ?
  468. WHERE id = ?|;
  469. @queryargs = ( $amount, $netamount, $form->{taxincluded}, $form->{id} );
  470. }
  471. else {
  472. # save OE record
  473. $query = qq|
  474. UPDATE oe set
  475. ordnumber = ?,
  476. quonumber = ?,
  477. transdate = ?,
  478. vendor_id = ?,
  479. customer_id = ?,
  480. amount = ?,
  481. netamount = ?,
  482. reqdate = ?,
  483. taxincluded = ?,
  484. shippingpoint = ?,
  485. shipvia = ?,
  486. notes = ?,
  487. intnotes = ?,
  488. curr = ?,
  489. closed = ?,
  490. quotation = ?,
  491. department_id = ?,
  492. employee_id = ?,
  493. language_code = ?,
  494. ponumber = ?,
  495. terms = ?
  496. WHERE id = ?|;
  497. if ( !$form->{reqdate} ) {
  498. $form->{reqdate} = undef;
  499. }
  500. @queryargs = (
  501. $form->{ordnumber}, $form->{quonumber},
  502. $form->{transdate}, $form->{vendor_id},
  503. $form->{customer_id}, $amount,
  504. $netamount, $form->{reqdate},
  505. $form->{taxincluded}, $form->{shippingpoint},
  506. $form->{shipvia}, $form->{notes},
  507. $form->{intnotes}, $form->{currency},
  508. $form->{closed}, $quotation,
  509. $form->{department_id}, $form->{employee_id},
  510. $form->{language_code}, $form->{ponumber},
  511. $form->{terms}, $form->{id}
  512. );
  513. }
  514. $sth = $dbh->prepare($query);
  515. $sth->execute(@queryargs) || $form->dberror($query);
  516. if ( !$did_insert ) {
  517. @queries = $form->run_custom_queries( 'oe', 'UPDATE' );
  518. }
  519. $form->{ordtotal} = $amount;
  520. # add shipto
  521. $form->{name} = $form->{ $form->{vc} };
  522. $form->{name} =~ s/--$form->{"$form->{vc}_id"}//;
  523. $form->add_shipto( $dbh, $form->{id} );
  524. # save printed, emailed, queued
  525. $form->save_status($dbh);
  526. if ( ( $form->{currency} ne $form->{defaultcurrency} ) && !$exchangerate ) {
  527. if ( $form->{vc} eq 'customer' ) {
  528. $form->update_exchangerate( $dbh, $form->{currency},
  529. $form->{transdate}, $form->{exchangerate}, 0 );
  530. }
  531. if ( $form->{vc} eq 'vendor' ) {
  532. $form->update_exchangerate( $dbh, $form->{currency},
  533. $form->{transdate}, 0, $form->{exchangerate} );
  534. }
  535. }
  536. if ( $form->{type} =~ /_order$/ ) {
  537. # adjust onhand
  538. &adj_onhand( $dbh, $form, $ml * -1 );
  539. &adj_inventory( $dbh, $myconfig, $form );
  540. }
  541. my %audittrail = (
  542. tablename => 'oe',
  543. reference => ( $form->{type} =~ /_order$/ )
  544. ? $form->{ordnumber}
  545. : $form->{quonumber},
  546. formname => $form->{type},
  547. action => 'saved',
  548. id => $form->{id}
  549. );
  550. $form->audittrail( $dbh, "", \%audittrail );
  551. $form->save_recurring( $dbh, $myconfig );
  552. my $rc = $dbh->commit;
  553. $rc;
  554. }
  555. sub delete {
  556. my ( $self, $myconfig, $form ) = @_;
  557. # connect to database
  558. my $dbh = $form->{dbh};
  559. # delete spool files
  560. my $query = qq|
  561. SELECT spoolfile FROM status
  562. WHERE trans_id = ?
  563. AND spoolfile IS NOT NULL|;
  564. $sth = $dbh->prepare($query);
  565. $sth->execute( $form->{id} ) || $form->dberror($query);
  566. my $spoolfile;
  567. my @spoolfiles = ();
  568. while ( ($spoolfile) = $sth->fetchrow_array ) {
  569. push @spoolfiles, $spoolfile;
  570. }
  571. $sth->finish;
  572. $query = qq|
  573. SELECT o.parts_id, o.ship, p.inventory_accno_id, p.assembly
  574. FROM orderitems o
  575. JOIN parts p ON (p.id = o.parts_id)
  576. WHERE trans_id = ?|;
  577. $sth = $dbh->prepare($query);
  578. $sth->execute( $form->{id} ) || $form->dberror($query);
  579. if ( $form->{type} =~ /_order$/ ) {
  580. $ml = ( $form->{type} eq 'purchase_order' ) ? -1 : 1;
  581. while ( my ( $id, $ship, $inv, $assembly ) = $sth->fetchrow_array ) {
  582. $form->update_balance( $dbh, "parts", "onhand", "id = $id",
  583. $ship * $ml )
  584. if ( $inv || $assembly );
  585. }
  586. }
  587. $sth->finish;
  588. # delete inventory
  589. $query = qq|DELETE FROM inventory WHERE trans_id = ?|;
  590. $sth = $dbh->prepare($query);
  591. $sth->execute( $form->{id} ) || $form->dberror($query);
  592. $sth->finish;
  593. # delete status entries
  594. $query = qq|DELETE FROM status WHERE trans_id = ?|;
  595. $sth = $dbh->prepare($query);
  596. $sth->execute( $form->{id} ) || $form->dberror($query);
  597. $sth->finish;
  598. # delete OE record
  599. $query = qq|DELETE FROM oe WHERE id = ?|;
  600. $sth = $dbh->prepare($query);
  601. $sth->execute( $form->{id} ) || $form->dberror($query);
  602. $sth->finish;
  603. # delete individual entries
  604. $query = qq|DELETE FROM orderitems WHERE trans_id = ?|;
  605. $sth->finish;
  606. $query = qq|DELETE FROM shipto WHERE trans_id = ?|;
  607. $sth = $dbh->prepare($query);
  608. $sth->execute( $form->{id} ) || $form->dberror($query);
  609. $sth->finish;
  610. my %audittrail = (
  611. tablename => 'oe',
  612. reference => ( $form->{type} =~ /_order$/ )
  613. ? $form->{ordnumber}
  614. : $form->{quonumber},
  615. formname => $form->{type},
  616. action => 'deleted',
  617. id => $form->{id}
  618. );
  619. $form->audittrail( $dbh, "", \%audittrail );
  620. my $rc = $dbh->commit;
  621. if ($rc) {
  622. foreach $spoolfile (@spoolfiles) {
  623. unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile;
  624. }
  625. }
  626. $rc;
  627. }
  628. sub retrieve {
  629. use LedgerSMB::PriceMatrix;
  630. my ( $self, $myconfig, $form ) = @_;
  631. # connect to database
  632. my $dbh = $form->{dbh};
  633. my $query;
  634. my $sth;
  635. my $var;
  636. my $ref;
  637. $query = qq|
  638. SELECT value, current_date FROM defaults
  639. WHERE setting_key = 'curr'|;
  640. ( $form->{currencies}, $form->{transdate} ) = $dbh->selectrow_array($query);
  641. if ( $form->{id} ) {
  642. # retrieve order
  643. $query = qq|
  644. SELECT o.ordnumber, o.transdate, o.reqdate, o.terms,
  645. o.taxincluded, o.shippingpoint, o.shipvia,
  646. o.notes, o.intnotes, o.curr AS currency,
  647. e.name AS employee, o.employee_id,
  648. o.$form->{vc}_id, vc.name AS $form->{vc},
  649. o.amount AS invtotal, o.closed, o.reqdate,
  650. o.quonumber, o.department_id,
  651. d.description AS department, o.language_code,
  652. o.ponumber
  653. FROM oe o
  654. JOIN $form->{vc} vc ON (o.$form->{vc}_id = vc.id)
  655. LEFT JOIN employee e ON (o.employee_id = e.id)
  656. LEFT JOIN department d ON (o.department_id = d.id)
  657. WHERE o.id = ?|;
  658. $sth = $dbh->prepare($query);
  659. $sth->execute( $form->{id} ) || $form->dberror($query);
  660. $ref = $sth->fetchrow_hashref(NAME_lc);
  661. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  662. for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
  663. $sth->finish;
  664. $query = qq|SELECT * FROM shipto WHERE trans_id = ?|;
  665. $sth = $dbh->prepare($query);
  666. $sth->execute( $form->{id} ) || $form->dberror($query);
  667. $ref = $sth->fetchrow_hashref(NAME_lc);
  668. for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
  669. $sth->finish;
  670. # get printed, emailed and queued
  671. $query = qq|
  672. SELECT s.printed, s.emailed, s.spoolfile, s.formname
  673. FROM status s
  674. WHERE s.trans_id = ?|;
  675. $sth = $dbh->prepare($query);
  676. $sth->execute( $form->{id} ) || $form->dberror($query);
  677. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  678. $form->{printed} .= "$ref->{formname} "
  679. if $ref->{printed};
  680. $form->{emailed} .= "$ref->{formname} "
  681. if $ref->{emailed};
  682. $form->{queued} .= "$ref->{formname} $ref->{spoolfile} "
  683. if $ref->{spoolfile};
  684. }
  685. $sth->finish;
  686. for (qw(printed emailed queued)) { $form->{$_} =~ s/ +$//g }
  687. # retrieve individual items
  688. $query = qq|
  689. SELECT o.id AS orderitems_id, p.partnumber, p.assembly,
  690. o.description, o.qty, o.sellprice,
  691. o.parts_id AS id, o.unit, o.discount, p.bin,
  692. o.reqdate, o.project_id, o.ship, o.serialnumber,
  693. o.notes, pr.projectnumber, pg.partsgroup,
  694. p.partsgroup_id, p.partnumber AS sku,
  695. p.listprice, p.lastcost, p.weight, p.onhand,
  696. p.inventory_accno_id, p.income_accno_id,
  697. p.expense_accno_id, t.description
  698. AS partsgrouptranslation
  699. FROM orderitems o
  700. JOIN parts p ON (o.parts_id = p.id)
  701. LEFT JOIN project pr ON (o.project_id = pr.id)
  702. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  703. LEFT JOIN translation t
  704. ON (t.trans_id = p.partsgroup_id
  705. AND t.language_code = ?)
  706. WHERE o.trans_id = ?
  707. ORDER BY o.id|;
  708. $sth = $dbh->prepare($query);
  709. $sth->execute( $form->{language_code}, $form->{id} )
  710. || $form->dberror($query);
  711. # foreign exchange rates
  712. &exchangerate_defaults( $dbh, $form );
  713. # query for price matrix
  714. my $pmh = PriceMatrix::price_matrix_query( $dbh, $form );
  715. # taxes
  716. $query = qq|
  717. SELECT c.accno FROM chart c
  718. JOIN partstax pt ON (pt.chart_id = c.id)
  719. WHERE pt.parts_id = ?|;
  720. my $tth = $dbh->prepare($query) || $form->dberror($query);
  721. my $taxrate;
  722. my $ptref;
  723. my $sellprice;
  724. my $listprice;
  725. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  726. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  727. ($decimalplaces) = ( $ref->{sellprice} =~ /\.(\d+)/ );
  728. $decimalplaces = length $decimalplaces;
  729. $decimalplaces = ( $decimalplaces > 2 ) ? $decimalplaces : 2;
  730. $tth->execute( $ref->{id} );
  731. $ref->{taxaccounts} = "";
  732. $taxrate = 0;
  733. while ( $ptref = $tth->fetchrow_hashref(NAME_lc) ) {
  734. $ref->{taxaccounts} .= "$ptref->{accno} ";
  735. $taxrate += $form->{"$ptref->{accno}_rate"};
  736. }
  737. $tth->finish;
  738. chop $ref->{taxaccounts};
  739. # preserve price
  740. $sellprice = $ref->{sellprice};
  741. # multiply by exchangerate
  742. $ref->{sellprice} =
  743. $form->round_amount(
  744. $ref->{sellprice} * $form->{ $form->{currency} },
  745. $decimalplaces );
  746. for (qw(listprice lastcost)) {
  747. $ref->{$_} =
  748. $form->round_amount(
  749. $ref->{$_} / $form->{ $form->{currency} },
  750. $decimalplaces );
  751. }
  752. # partnumber and price matrix
  753. PriceMatrix::price_matrix( $pmh, $ref, $form->{transdate},
  754. $decimalplaces, $form, $myconfig );
  755. $ref->{sellprice} = $sellprice;
  756. $ref->{partsgroup} = $ref->{partsgrouptranslation}
  757. if $ref->{partsgrouptranslation};
  758. push @{ $form->{form_details} }, $ref;
  759. }
  760. $sth->finish;
  761. # get recurring transaction
  762. $form->get_recurring;
  763. @queries = $form->run_custom_queries( 'oe', 'SELECT' );
  764. }
  765. else {
  766. # get last name used
  767. $form->lastname_used( $myconfig, $dbh, $form->{vc} )
  768. unless $form->{"$form->{vc}_id"};
  769. delete $form->{notes};
  770. }
  771. $dbh->commit;
  772. }
  773. sub exchangerate_defaults {
  774. my ( $dbh2, $form ) = @_;
  775. $dbh = $form->{dbh};
  776. my $var;
  777. my $buysell = ( $form->{vc} eq "customer" ) ? "buy" : "sell";
  778. # get default currencies
  779. my $query = qq|
  780. SELECT substr(value,1,3), value FROM defaults
  781. WHERE setting_key = 'curr'|;
  782. ( $form->{defaultcurrency}, $form->{currencies} ) =
  783. $dbh->selectrow_array($query);
  784. $query = qq|
  785. SELECT $buysell
  786. FROM exchangerate
  787. WHERE curr = ?
  788. AND transdate = ?|;
  789. my $eth1 = $dbh->prepare($query) || $form->dberror($query);
  790. $query = qq~
  791. SELECT max(transdate || ' ' || $buysell || ' ' || curr)
  792. FROM exchangerate
  793. WHERE curr = ?~;
  794. my $eth2 = $dbh->prepare($query) || $form->dberror($query);
  795. # get exchange rates for transdate or max
  796. foreach $var ( split /:/, substr( $form->{currencies}, 4 ) ) {
  797. $eth1->execute( $var, $form->{transdate} );
  798. my @exchangelist;
  799. @exchangelist = $eth1->fetchrow_array;
  800. $form->db_parse_numeric(sth=>$eth1, arrayref=>\@exchangelist);
  801. $form->{$var} = shift @array;
  802. if ( !$form->{$var} ) {
  803. $eth2->execute($var);
  804. @exchangelist = $eth2->fetchrow_array;
  805. $form->db_parse_numeric(sth=>$eth2, arrayref=>\@exchangelist);
  806. ( $form->{$var} ) = @exchangelist;
  807. ( $null, $form->{$var} ) = split / /, $form->{$var};
  808. $form->{$var} = 1 unless $form->{$var};
  809. $eth2->finish;
  810. }
  811. $eth1->finish;
  812. }
  813. $form->{ $form->{currency} } = $form->{exchangerate}
  814. if $form->{exchangerate};
  815. $form->{ $form->{currency} } ||= 1;
  816. $form->{ $form->{defaultcurrency} } = 1;
  817. }
  818. sub order_details {
  819. use LedgerSMB::CP;
  820. my ( $self, $myconfig, $form ) = @_;
  821. # connect to database
  822. my $dbh = $form->{dbh};
  823. my $query;
  824. my $sth;
  825. my $item;
  826. my $i;
  827. my @sortlist = ();
  828. my $projectnumber;
  829. my $projectdescription;
  830. my $projectnumber_id;
  831. my $translation;
  832. my $partsgroup;
  833. my @queryargs;
  834. my @taxaccounts;
  835. my %taxaccounts; # I don't think this works.
  836. my $tax;
  837. my $taxrate;
  838. my $taxamount;
  839. my %translations;
  840. my $language_code = $form->{dbh}->quote( $form->{language_code} );
  841. $query = qq|
  842. SELECT p.description, t.description
  843. FROM project p
  844. LEFT JOIN translation t ON (t.trans_id = p.id AND
  845. t.language_code = $language_code)
  846. WHERE id = ?|;
  847. my $prh = $dbh->prepare($query) || $form->dberror($query);
  848. $query = qq|
  849. SELECT inventory_accno_id, income_accno_id,
  850. expense_accno_id, assembly FROM parts
  851. WHERE id = ?|;
  852. my $pth = $dbh->prepare($query) || $form->dberror($query);
  853. my $sortby;
  854. # sort items by project and partsgroup
  855. for $i ( 1 .. $form->{rowcount} ) {
  856. if ( $form->{"id_$i"} ) {
  857. # account numbers
  858. $pth->execute( $form->{"id_$i"} );
  859. $ref = $pth->fetchrow_hashref(NAME_lc);
  860. for ( keys %$ref ) { $form->{"${_}_$i"} = $ref->{$_} }
  861. $pth->finish;
  862. $projectnumber_id = 0;
  863. $projectnumber = "";
  864. $form->{partsgroup} = "";
  865. $form->{projectnumber} = "";
  866. if ( $form->{groupprojectnumber}
  867. || $form->{grouppartsgroup} )
  868. {
  869. $inventory_accno_id =
  870. ( $form->{"inventory_accno_id_$i"} || $form->{"assembly_$i"} )
  871. ? "1"
  872. : "";
  873. if ( $form->{groupprojectnumber} ) {
  874. ( $projectnumber, $projectnumber_id ) =
  875. split /--/, $form->{"projectnumber_$i"};
  876. }
  877. if ( $form->{grouppartsgroup} ) {
  878. ( $form->{partsgroup} ) = split /--/,
  879. $form->{"partsgroup_$i"};
  880. }
  881. if ( $projectnumber_id
  882. && $form->{groupprojectnumber} )
  883. {
  884. if ( $translation{$projectnumber_id} ) {
  885. $form->{projectnumber} =
  886. $translation{$projectnumber_id};
  887. }
  888. else {
  889. # get project description
  890. $prh->execute($projectnumber_id);
  891. ( $projectdescription, $translation ) =
  892. $prh->fetchrow_array;
  893. $prh->finish;
  894. $form->{projectnumber} =
  895. ($translation)
  896. ? "$projectnumber, \n" . "$translation"
  897. : "$projectnumber, \n" . "$projectdescription";
  898. $translation{$projectnumber_id} =
  899. $form->{projectnumber};
  900. }
  901. }
  902. if ( $form->{grouppartsgroup}
  903. && $form->{partsgroup} )
  904. {
  905. $form->{projectnumber} .= " / "
  906. if $projectnumber_id;
  907. $form->{projectnumber} .= $form->{partsgroup};
  908. }
  909. $form->format_string(projectnumber);
  910. }
  911. $sortby = qq|$projectnumber$form->{partsgroup}|;
  912. if ( $form->{sortby} ne 'runningnumber' ) {
  913. for (qw(partnumber description bin)) {
  914. $sortby .= $form->{"${_}_$i"}
  915. if $form->{sortby} eq $_;
  916. }
  917. }
  918. push @sortlist,
  919. [
  920. $i,
  921. "$projectnumber$form->{partsgroup}" . "$inventory_accno_id",
  922. $form->{projectnumber},
  923. $projectnumber_id,
  924. $form->{partsgroup},
  925. $sortby
  926. ];
  927. }
  928. }
  929. delete $form->{projectnumber};
  930. # sort the whole thing by project and group
  931. @sortlist = sort { $a->[5] cmp $b->[5] } @sortlist;
  932. # if there is a warehouse limit picking
  933. if ( $form->{warehouse_id} && $form->{formname} =~ /(pick|packing)_list/ ) {
  934. # run query to check for inventory
  935. $query = qq|
  936. SELECT sum(qty) AS qty FROM inventory
  937. WHERE parts_id = ? AND warehouse_id = ?|;
  938. $sth = $dbh->prepare($query) || $form->dberror($query);
  939. for $i ( 1 .. $form->{rowcount} ) {
  940. $sth->execute( $form->{"id_$i"}, $form->{warehouse_id} )
  941. || $form->dberror;
  942. my @qtylist = $sth->fetchrow_array;
  943. $form->db_parse_numeric(sth=>$sth, arrayref=>\@qtylist);
  944. ($qty) = @qtylist; $sth->fetchrow_array;
  945. $sth->finish;
  946. $form->{"qty_$i"} = 0 if $qty == 0;
  947. if ( $form->parse_amount( $myconfig, $form->{"ship_$i"} ) > $qty ) {
  948. $form->{"ship_$i"} = $form->format_amount( $myconfig, $qty );
  949. }
  950. }
  951. }
  952. my $runningnumber = 1;
  953. my $sameitem = "";
  954. my $subtotal;
  955. my $k = scalar @sortlist;
  956. my $j = 0;
  957. foreach $item (@sortlist) {
  958. $i = $item->[0];
  959. $j++;
  960. if ( $form->{groupprojectnumber} || $form->{grouppartsgroup} ) {
  961. if ( $item->[1] ne $sameitem ) {
  962. $sameitem = $item->[1];
  963. $ok = 0;
  964. if ( $form->{groupprojectnumber} ) {
  965. $ok = $form->{"projectnumber_$i"};
  966. }
  967. if ( $form->{grouppartsgroup} ) {
  968. $ok = $form->{"partsgroup_$i"}
  969. unless $ok;
  970. }
  971. if ($ok) {
  972. if ( $form->{"inventory_accno_id_$i"}
  973. || $form->{"assembly_$i"} )
  974. {
  975. push( @{ $form->{part} }, "" );
  976. push( @{ $form->{service} }, NULL );
  977. }
  978. else {
  979. push( @{ $form->{part} }, NULL );
  980. push( @{ $form->{service} }, "" );
  981. }
  982. push( @{ $form->{description} }, $item->[2] );
  983. for (
  984. qw(taxrates runningnumber
  985. number sku qty ship unit bin
  986. serialnumber requiredate
  987. projectnumber sellprice
  988. listprice netprice discount
  989. discountrate linetotal weight
  990. itemnotes)
  991. )
  992. {
  993. push( @{ $form->{$_} }, "" );
  994. }
  995. push( @{ $form->{lineitems} }, { amount => 0, tax => 0 } );
  996. }
  997. }
  998. }
  999. $form->{"qty_$i"} = $form->parse_amount( $myconfig, $form->{"qty_$i"} );
  1000. $form->{"ship_$i"} =
  1001. $form->parse_amount( $myconfig, $form->{"ship_$i"} );
  1002. if ( $form->{"qty_$i"} ) {
  1003. $form->{totalqty} += $form->{"qty_$i"};
  1004. $form->{totalship} += $form->{"ship_$i"};
  1005. $form->{totalweight} +=
  1006. ( $form->{"weight_$i"} * $form->{"qty_$i"} );
  1007. $form->{totalweightship} +=
  1008. ( $form->{"weight_$i"} * $form->{"ship_$i"} );
  1009. # add number, description and qty to $form->{number}
  1010. push( @{ $form->{runningnumber} }, $runningnumber++ );
  1011. push( @{ $form->{number} }, qq|$form->{"partnumber_$i"}| );
  1012. push( @{ $form->{sku} }, qq|$form->{"sku_$i"}| );
  1013. push( @{ $form->{description} }, qq|$form->{"description_$i"}| );
  1014. push( @{ $form->{itemnotes} }, $form->{"notes_$i"} );
  1015. push(
  1016. @{ $form->{qty} },
  1017. $form->format_amount( $myconfig, $form->{"qty_$i"} )
  1018. );
  1019. push(
  1020. @{ $form->{ship} },
  1021. $form->format_amount( $myconfig, $form->{"ship_$i"} )
  1022. );
  1023. push( @{ $form->{unit} }, qq|$form->{"unit_$i"}| );
  1024. push( @{ $form->{bin} }, qq|$form->{"bin_$i"}| );
  1025. push( @{ $form->{serialnumber} }, qq|$form->{"serialnumber_$i"}| );
  1026. push( @{ $form->{requiredate} }, qq|$form->{"reqdate_$i"}| );
  1027. push( @{ $form->{projectnumber} },
  1028. qq|$form->{"projectnumber_$i"}| );
  1029. push( @{ $form->{sellprice} }, $form->{"sellprice_$i"} );
  1030. push( @{ $form->{listprice} }, $form->{"listprice_$i"} );
  1031. push(
  1032. @{ $form->{weight} },
  1033. $form->format_amount(
  1034. $myconfig, $form->{"weight_$i"} * $form->{"ship_$i"}
  1035. )
  1036. );
  1037. my $sellprice =
  1038. $form->parse_amount( $myconfig, $form->{"sellprice_$i"} );
  1039. my ($dec) = ( $sellprice =~ /\.(\d+)/ );
  1040. $dec = length $dec;
  1041. my $decimalplaces = ( $dec > 2 ) ? $dec : 2;
  1042. my $discount = $form->round_amount(
  1043. $sellprice *
  1044. $form->parse_amount( $myconfig, $form->{"discount_$i"} ) /
  1045. 100,
  1046. $decimalplaces
  1047. );
  1048. # keep a netprice as well, (sellprice - discount)
  1049. $form->{"netprice_$i"} = $sellprice - $discount;
  1050. my $linetotal =
  1051. $form->round_amount( $form->{"qty_$i"} * $form->{"netprice_$i"},
  1052. 2 );
  1053. if ( $form->{"inventory_accno_id_$i"}
  1054. || $form->{"assembly_$i"} )
  1055. {
  1056. push( @{ $form->{part} }, $form->{"sku_$i"} );
  1057. push( @{ $form->{service} }, NULL );
  1058. $form->{totalparts} += $linetotal;
  1059. }
  1060. else {
  1061. push( @{ $form->{service} }, $form->{"sku_$i"} );
  1062. push( @{ $form->{part} }, NULL );
  1063. $form->{totalservices} += $linetotal;
  1064. }
  1065. push(
  1066. @{ $form->{netprice} },
  1067. ( $form->{"netprice_$i"} )
  1068. ? $form->format_amount( $myconfig, $form->{"netprice_$i"},
  1069. $decimalplaces )
  1070. : " "
  1071. );
  1072. $discount =
  1073. ($discount)
  1074. ? $form->format_amount( $myconfig, $discount * -1,
  1075. $decimalplaces )
  1076. : " ";
  1077. push( @{ $form->{discount} }, $discount );
  1078. push(
  1079. @{ $form->{discountrate} },
  1080. $form->format_amount( $myconfig, $form->{"discount_$i"} )
  1081. );
  1082. $form->{ordtotal} += $linetotal;
  1083. # this is for the subtotals for grouping
  1084. $subtotal += $linetotal;
  1085. $form->{"linetotal_$i"} =
  1086. $form->format_amount( $myconfig, $linetotal, 2 );
  1087. push( @{ $form->{linetotal} }, $form->{"linetotal_$i"} );
  1088. @taxaccounts = Tax::init_taxes( $form, $form->{"taxaccounts_$i"} );
  1089. my $ml = 1;
  1090. my @taxrates = ();
  1091. $tax = 0;
  1092. $taxamount =
  1093. Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 1 );
  1094. $taxbase = Tax::extract_taxes( \@taxaccounts, $form, $linetotal );
  1095. foreach $item (@taxaccounts) {
  1096. push @taxrates, Math::BigFloat->new(100) * $item->rate;
  1097. if ( $form->{taxincluded} ) {
  1098. $taxaccounts{ $item->account } += $item->value;
  1099. $taxbase{ $item->account } += $taxbase;
  1100. }
  1101. else {
  1102. Tax::apply_taxes( \@taxaccounts, $form, $linetotal );
  1103. $taxbase{ $item->account } += $linetotal;
  1104. $taxaccounts{ $item->account } += $item->value;
  1105. }
  1106. }
  1107. if ( $form->{taxincluded} ) {
  1108. $tax +=
  1109. Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 1 );
  1110. }
  1111. else {
  1112. $tax +=
  1113. Tax::calculate_taxes( \@taxaccounts, $form, $linetotal, 0 );
  1114. }
  1115. push(
  1116. @{ $form->{lineitems} },
  1117. {
  1118. amount => $linetotal,
  1119. tax => $form->round_amount( $tax, 2 )
  1120. }
  1121. );
  1122. push( @{ $form->{taxrates} },
  1123. join ' ', sort { $a <=> $b } @taxrates );
  1124. if ( $form->{"assembly_$i"} ) {
  1125. $form->{stagger} = -1;
  1126. &assembly_details( $myconfig, $form, $dbh, $form->{"id_$i"},
  1127. $oid{ $myconfig->{dbdriver} },
  1128. $form->{"qty_$i"} );
  1129. }
  1130. }
  1131. # add subtotal
  1132. if ( $form->{groupprojectnumber} || $form->{grouppartsgroup} ) {
  1133. if ($subtotal) {
  1134. if ( $j < $k ) {
  1135. # look at next item
  1136. if ( $sortlist[$j]->[1] ne $sameitem ) {
  1137. if ( $form->{"inventory_accno_id_$i"}
  1138. || $form->{"assembly_$i"} )
  1139. {
  1140. push( @{ $form->{part} }, "" );
  1141. push( @{ $form->{service} }, NULL );
  1142. }
  1143. else {
  1144. push( @{ $form->{service} }, "" );
  1145. push( @{ $form->{part} }, NULL );
  1146. }
  1147. for (
  1148. qw(
  1149. taxrates runningnumber
  1150. number sku qty ship unit
  1151. bin serialnumber
  1152. requiredate
  1153. projectnumber sellprice
  1154. listprice netprice
  1155. discount discountrate
  1156. weight itemnotes)
  1157. )
  1158. {
  1159. push( @{ $form->{$_} }, "" );
  1160. }
  1161. push(
  1162. @{ $form->{description} },
  1163. $form->{groupsubtotaldescription}
  1164. );
  1165. push(
  1166. @{ $form->{lineitems} },
  1167. {
  1168. amount => 0,
  1169. tax => 0
  1170. }
  1171. );
  1172. if ( $form->{groupsubtotaldescription} ne "" ) {
  1173. push(
  1174. @{ $form->{linetotal} },
  1175. $form->format_amount( $myconfig, $subtotal, 2 )
  1176. );
  1177. }
  1178. else {
  1179. push( @{ $form->{linetotal} }, "" );
  1180. }
  1181. $subtotal = 0;
  1182. }
  1183. }
  1184. else {
  1185. # got last item
  1186. if ( $form->{groupsubtotaldescription} ne "" ) {
  1187. if ( $form->{"inventory_accno_id_$i"}
  1188. || $form->{"assembly_$i"} )
  1189. {
  1190. push( @{ $form->{part} }, "" );
  1191. push( @{ $form->{service} }, NULL );
  1192. }
  1193. else {
  1194. push( @{ $form->{service} }, "" );
  1195. push( @{ $form->{part} }, NULL );
  1196. }
  1197. for (
  1198. qw(
  1199. taxrates runningnumber
  1200. number sku qty ship unit
  1201. bin serialnumber
  1202. requiredate
  1203. projectnumber sellprice
  1204. listprice netprice
  1205. discount discountrate
  1206. weight itemnotes)
  1207. )
  1208. {
  1209. push( @{ $form->{$_} }, "" );
  1210. }
  1211. push(
  1212. @{ $form->{description} },
  1213. $form->{groupsubtotaldescription}
  1214. );
  1215. push(
  1216. @{ $form->{linetotal} },
  1217. $form->format_amount( $myconfig, $subtotal, 2 )
  1218. );
  1219. push(
  1220. @{ $form->{lineitems} },
  1221. {
  1222. amount => 0,
  1223. tax => 0
  1224. }
  1225. );
  1226. }
  1227. }
  1228. }
  1229. }
  1230. }
  1231. $tax = 0;
  1232. foreach $item ( sort keys %taxaccounts ) {
  1233. if ( $form->round_amount( $taxaccounts{$item}, 2 ) ) {
  1234. $tax += $taxamount = $form->round_amount( $taxaccounts{$item}, 2 );
  1235. push(
  1236. @{ $form->{taxbaseinclusive} },
  1237. $form->{"${item}_taxbaseinclusive"} =
  1238. $form->round_amount( $taxbase{$item} + $tax, 2 )
  1239. );
  1240. push(
  1241. @{ $form->{taxbase} },
  1242. $form->{"${item}_taxbase"} =
  1243. $form->format_amount( $myconfig, $taxbase{$item}, 2 )
  1244. );
  1245. push(
  1246. @{ $form->{tax} },
  1247. $form->{"${item}_tax"} =
  1248. $form->format_amount( $myconfig, $taxamount, 2 )
  1249. );
  1250. push( @{ $form->{taxdescription} },
  1251. $form->{"${item}_description"} );
  1252. $form->{"${item}_taxrate"} =
  1253. $form->format_amount( $myconfig, $form->{"${item}_rate"} * 100 );
  1254. push( @{ $form->{taxrate} }, $form->{"${item}_taxrate"} );
  1255. push( @{ $form->{taxnumber} }, $form->{"${item}_taxnumber"} );
  1256. }
  1257. }
  1258. # adjust taxes for lineitems
  1259. my $total = 0;
  1260. for ( @{ $form->{lineitems} } ) {
  1261. $total += $_->{tax};
  1262. }
  1263. if ( $form->round_amount( $total, 2 ) != $form->round_amount( $tax, 2 ) ) {
  1264. # get largest amount
  1265. for ( reverse sort { $a->{tax} <=> $b->{tax} } @{ $form->{lineitems} } )
  1266. {
  1267. $_->{tax} -= $total - $tax;
  1268. last;
  1269. }
  1270. }
  1271. $i = 1;
  1272. for ( @{ $form->{lineitems} } ) {
  1273. push(
  1274. @{ $form->{linetax} },
  1275. $form->format_amount( $myconfig, $_->{tax}, 2, "" )
  1276. );
  1277. }
  1278. for (qw(totalparts totalservices)) {
  1279. $form->{$_} = $form->format_amount( $myconfig, $form->{$_}, 2 );
  1280. }
  1281. for (qw(totalqty totalship totalweight)) {
  1282. $form->{$_} = $form->format_amount( $myconfig, $form->{$_} );
  1283. }
  1284. $form->{subtotal} = $form->format_amount( $myconfig, $form->{ordtotal}, 2 );
  1285. $form->{ordtotal} =
  1286. ( $form->{taxincluded} )
  1287. ? $form->{ordtotal}
  1288. : $form->{ordtotal} + $tax;
  1289. my $c;
  1290. if ( $form->{language_code} ne "" ) {
  1291. $c = new CP $form->{language_code};
  1292. }
  1293. else {
  1294. $c = new CP $myconfig->{countrycode};
  1295. }
  1296. $c->init;
  1297. my $whole;
  1298. ( $whole, $form->{decimal} ) = split /\./, $form->{ordtotal};
  1299. $form->{decimal} .= "00";
  1300. $form->{decimal} = substr( $form->{decimal}, 0, 2 );
  1301. $form->{text_decimal} = $c->num2text( $form->{decimal} * 1 );
  1302. $form->{text_amount} = $c->num2text($whole);
  1303. $form->{integer_amount} = $form->format_amount( $myconfig, $whole );
  1304. # format amounts
  1305. $form->{quototal} = $form->{ordtotal} =
  1306. $form->format_amount( $myconfig, $form->{ordtotal}, 2 );
  1307. $form->format_string(qw(text_amount text_decimal));
  1308. $query = qq|
  1309. SELECT value FROM defaults
  1310. WHERE setting_key = 'weightunit'|;
  1311. ( $form->{weightunit} ) = $dbh->selectrow_array($query);
  1312. $dbh->commit;
  1313. }
  1314. sub assembly_details {
  1315. my ( $myconfig, $form, $dbh, $id, $oid, $qty ) = @_;
  1316. my $sm = "";
  1317. my $spacer;
  1318. $form->{stagger}++;
  1319. if ( $form->{format} eq 'html' ) {
  1320. $spacer = "&nbsp;" x ( 3 * ( $form->{stagger} - 1 ) )
  1321. if $form->{stagger} > 1;
  1322. }
  1323. if ( $form->{format} =~ /(postscript|pdf)/ ) {
  1324. if ( $form->{stagger} > 1 ) {
  1325. $spacer = ( $form->{stagger} - 1 ) * 3;
  1326. $spacer = '\rule{' . $spacer . 'mm}{0mm}';
  1327. }
  1328. }
  1329. # get parts and push them onto the stack
  1330. my $sortorder = "";
  1331. if ( $form->{grouppartsgroup} ) {
  1332. $sortorder = qq|ORDER BY pg.partsgroup, a.id|;
  1333. }
  1334. else {
  1335. $sortorder = qq|ORDER BY a.id|;
  1336. }
  1337. my $where =
  1338. ( $form->{formname} eq 'work_order' )
  1339. ? "1 = 1"
  1340. : "a.bom = '1'";
  1341. my $query = qq|
  1342. SELECT p.partnumber, p.description, p.unit, a.qty,
  1343. pg.partsgroup, p.partnumber AS sku, p.assembly, p.id,
  1344. p.bin
  1345. FROM assembly a
  1346. JOIN parts p ON (a.parts_id = p.id)
  1347. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  1348. WHERE $where
  1349. AND a.id = ?
  1350. $sortorder|;
  1351. my $sth = $dbh->prepare($query);
  1352. $sth->execute($id) || $form->dberror($query);
  1353. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1354. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1355. for (qw(partnumber description partsgroup)) {
  1356. $form->{"a_$_"} = $ref->{$_};
  1357. $form->format_string("a_$_");
  1358. }
  1359. if ( $form->{grouppartsgroup} && $ref->{partsgroup} ne $sm ) {
  1360. for (
  1361. qw(
  1362. taxrates number sku unit qty runningnumber ship
  1363. bin serialnumber requiredate projectnumber
  1364. sellprice listprice netprice discount
  1365. discountrate linetotal weight itemnotes)
  1366. )
  1367. {
  1368. push( @{ $form->{$_} }, "" );
  1369. }
  1370. $sm = ( $form->{"a_partsgroup"} ) ? $form->{"a_partsgroup"} : "";
  1371. push( @{ $form->{description} }, "$spacer$sm" );
  1372. push( @{ $form->{lineitems} }, { amount => 0, tax => 0 } );
  1373. }
  1374. if ( $form->{stagger} ) {
  1375. push(
  1376. @{ $form->{description} },
  1377. qq|$spacer$form->{"a_partnumber"}, |
  1378. . qq|$form->{"a_description"}|
  1379. );
  1380. for (
  1381. qw(
  1382. taxrates number sku runningnumber ship
  1383. serialnumber requiredate projectnumber
  1384. sellprice listprice netprice discount
  1385. discountrate linetotal weight itemnotes)
  1386. )
  1387. {
  1388. push( @{ $form->{$_} }, "" );
  1389. }
  1390. }
  1391. else {
  1392. push( @{ $form->{description} }, qq|$form->{"a_description"}| );
  1393. push( @{ $form->{sku} }, $form->{"a_partnumber"} );
  1394. push( @{ $form->{number} }, $form->{"a_partnumber"} );
  1395. for (
  1396. qw(
  1397. taxrates runningnumber ship serialnumber
  1398. requiredate projectnumber sellprice listprice
  1399. netprice discount discountrate linetotal weight
  1400. itemnotes)
  1401. )
  1402. {
  1403. push( @{ $form->{$_} }, "" );
  1404. }
  1405. }
  1406. push( @{ $form->{lineitems} }, { amount => 0, tax => 0 } );
  1407. push(
  1408. @{ $form->{qty} },
  1409. $form->format_amount( $myconfig, $ref->{qty} * $qty )
  1410. );
  1411. for (qw(unit bin)) {
  1412. $form->{"a_$_"} = $ref->{$_};
  1413. $form->format_string("a_$_");
  1414. push( @{ $form->{$_} }, $form->{"a_$_"} );
  1415. }
  1416. if ( $ref->{assembly} && $form->{formname} eq 'work_order' ) {
  1417. &assembly_details( $myconfig, $form, $dbh, $ref->{id}, $oid,
  1418. $ref->{qty} * $qty );
  1419. }
  1420. }
  1421. $sth->finish;
  1422. $form->{stagger}--;
  1423. }
  1424. sub project_description {
  1425. my ( $self, $dbh, $id ) = @_;
  1426. my $query = qq|
  1427. SELECT description
  1428. FROM project
  1429. WHERE id = $id|;
  1430. ($_) = $dbh->selectrow_array($query);
  1431. $_;
  1432. }
  1433. sub get_warehouses {
  1434. my ( $self, $myconfig, $form ) = @_;
  1435. my $dbh = $form->{dbh};
  1436. # setup warehouses
  1437. my $query = qq|
  1438. SELECT id, description
  1439. FROM warehouse
  1440. ORDER BY 2|;
  1441. my $sth = $dbh->prepare($query);
  1442. $sth->execute || $form->dberror($query);
  1443. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1444. push @{ $form->{all_warehouse} }, $ref;
  1445. }
  1446. $sth->finish;
  1447. }
  1448. sub save_inventory {
  1449. my ( $self, $myconfig, $form ) = @_;
  1450. my ( $null, $warehouse_id ) = split /--/, $form->{warehouse};
  1451. $warehouse_id *= 1;
  1452. my $ml = ( $form->{type} eq 'ship_order' ) ? -1 : 1;
  1453. my $dbh = $form->{dbh};
  1454. my $sth;
  1455. my $wth;
  1456. my $serialnumber;
  1457. my $ship;
  1458. my ( $null, $employee_id ) = split /--/, $form->{employee};
  1459. ( $null, $employee_id ) = $form->get_employee($dbh) if !$employee_id;
  1460. $query = qq|
  1461. SELECT serialnumber, ship
  1462. FROM orderitems
  1463. WHERE trans_id = ?
  1464. AND id = ?
  1465. FOR UPDATE|;
  1466. $sth = $dbh->prepare($query) || $form->dberror($query);
  1467. $query = qq|
  1468. SELECT sum(qty)
  1469. FROM inventory
  1470. WHERE parts_id = ?
  1471. AND warehouse_id = ?|;
  1472. $wth = $dbh->prepare($query) || $form->dberror($query);
  1473. for my $i ( 1 .. $form->{rowcount} ) {
  1474. $ship =
  1475. ( abs( $form->{"ship_$i"} ) > abs( $form->{"qty_$i"} ) )
  1476. ? $form->{"qty_$i"}
  1477. : $form->{"ship_$i"};
  1478. if ( $warehouse_id && $form->{type} eq 'ship_order' ) {
  1479. $wth->execute( $form->{"id_$i"}, $warehouse_id )
  1480. || $form->dberror;
  1481. @qtylist = $wth->fetchrow_array;
  1482. $form->db_parse_numeric(sth=>$wth, arrayref=>\@qtylist);
  1483. ($qty) = @qtylist;
  1484. $wth->finish;
  1485. if ( $ship > $qty ) {
  1486. $ship = $qty;
  1487. }
  1488. }
  1489. if ($ship) {
  1490. if ( !$form->{shippingdate} ) {
  1491. $form->{shippingdate} = undef;
  1492. }
  1493. $ship *= $ml;
  1494. $query = qq|
  1495. INSERT INTO inventory
  1496. (parts_id, warehouse_id, qty, trans_id,
  1497. orderitems_id, shippingdate,
  1498. employee_id)
  1499. VALUES
  1500. (?, ?, ?, ?, ?, ?, ?)|;
  1501. $sth2 = $dbh->prepare($query);
  1502. $sth2->execute( $form->{"id_$i"}, $warehouse_id, $ship,
  1503. $form->{"id"}, $form->{"orderitems_id_$i"},
  1504. $form->{shippingdate}, $employee_id )
  1505. || $form->dberror($query);
  1506. $sth2->finish;
  1507. # add serialnumber, ship to orderitems
  1508. $sth->execute( $form->{id}, $form->{"orderitems_id_$i"} )
  1509. || $form->dberror;
  1510. ( $serialnumber, $ship ) = $sth->fetchrow_array;
  1511. $sth->finish;
  1512. $serialnumber .= " " if $serialnumber;
  1513. $serialnumber .= qq|$form->{"serialnumber_$i"}|;
  1514. $ship += $form->{"ship_$i"};
  1515. $query = qq|
  1516. UPDATE orderitems SET
  1517. serialnumber = ?,
  1518. ship = ?,
  1519. reqdate = ?
  1520. WHERE trans_id = ?
  1521. AND id = ?|;
  1522. $sth2 = $dbh->prepare($query);
  1523. $sth2->execute( $serialnumber, $ship, $form->{shippingdate},
  1524. $form->{id}, $form->{"orderitems_id_$i"} )
  1525. || $form->dberror($query);
  1526. $sth2->finish;
  1527. # update order with ship via
  1528. $query = qq|
  1529. UPDATE oe SET
  1530. shippingpoint = ?,
  1531. shipvia = ?
  1532. WHERE id = ?|;
  1533. $sth2 = $dbh->prepare($query);
  1534. $sth2->execute( $form->{shippingpoint},
  1535. $form->{shipvia}, $form->{id} )
  1536. || $form->dberror($query);
  1537. $sth2->finish;
  1538. # update onhand for parts
  1539. $form->update_balance(
  1540. $dbh, "parts", "onhand",
  1541. qq|id = $form->{"id_$i"}|,
  1542. $form->{"ship_$i"} * $ml
  1543. );
  1544. }
  1545. }
  1546. my $rc = $dbh->commit;
  1547. $rc;
  1548. }
  1549. sub adj_onhand {
  1550. my ( $dbh, $form, $ml ) = @_;
  1551. my $query = qq|
  1552. SELECT oi.parts_id, oi.ship, p.inventory_accno_id, p.assembly
  1553. FROM orderitems oi
  1554. JOIN parts p ON (p.id = oi.parts_id)
  1555. WHERE oi.trans_id = ?|;
  1556. my $sth = $dbh->prepare($query);
  1557. $sth->execute( $form->{id} ) || $form->dberror($query);
  1558. $query = qq|
  1559. SELECT sum(p.inventory_accno_id), p.assembly
  1560. FROM parts p
  1561. JOIN assembly a ON (a.parts_id = p.id)
  1562. WHERE a.id = ?
  1563. GROUP BY p.assembly|;
  1564. my $ath = $dbh->prepare($query) || $form->dberror($query);
  1565. my $ref;
  1566. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1567. if ( $ref->{inventory_accno_id} || $ref->{assembly} ) {
  1568. # do not update if assembly consists of all services
  1569. if ( $ref->{assembly} ) {
  1570. $ath->execute( $ref->{parts_id} )
  1571. || $form->dberror($query);
  1572. my ( $inv, $assembly ) = $ath->fetchrow_array;
  1573. $ath->finish;
  1574. next unless ( $inv || $assembly );
  1575. }
  1576. # adjust onhand in parts table
  1577. $form->update_balance(
  1578. $dbh, "parts", "onhand",
  1579. qq|id = $ref->{parts_id}|,
  1580. $ref->{ship} * $ml
  1581. );
  1582. }
  1583. }
  1584. $sth->finish;
  1585. }
  1586. sub adj_inventory {
  1587. my ( $dbh, $myconfig, $form ) = @_;
  1588. # increase/reduce qty in inventory table
  1589. my $query = qq|
  1590. SELECT oi.id, oi.parts_id, oi.ship
  1591. FROM orderitems oi
  1592. WHERE oi.trans_id = ?|;
  1593. my $sth = $dbh->prepare($query);
  1594. $sth->execute( $form->{id} ) || $form->dberror($query);
  1595. my $id = $dbh->quote( $form->{id} );
  1596. $query = qq|
  1597. SELECT qty,
  1598. (SELECT SUM(qty) FROM inventory
  1599. WHERE trans_id = $id
  1600. AND orderitems_id = ?) AS total
  1601. FROM inventory
  1602. WHERE trans_id = $id
  1603. AND orderitems_id = ?|;
  1604. my $ith = $dbh->prepare($query) || $form->dberror($query);
  1605. my $qty;
  1606. my $ml = ( $form->{type} =~ /(ship|sales)_order/ ) ? -1 : 1;
  1607. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1608. $ith->execute( $ref->{id}, $ref->{id} ) || $form->dberror($query);
  1609. my $ship = $ref->{ship};
  1610. while ( my $inv = $ith->fetchrow_hashref(NAME_lc) ) {
  1611. if ( ( $qty = ( ( $inv->{total} * $ml ) - $ship ) ) >= 0 ) {
  1612. $qty = $inv->{qty} * $ml
  1613. if ( $qty > ( $inv->{qty} * $ml ) );
  1614. $form->update_balance(
  1615. $dbh, "inventory", "qty",
  1616. qq|$oid{$myconfig->{dbdriver}} | . qq|= $inv->{oid}|,
  1617. $qty * -1 * $ml
  1618. );
  1619. $ship -= $qty;
  1620. }
  1621. }
  1622. $ith->finish;
  1623. }
  1624. $sth->finish;
  1625. # delete inventory entries if qty = 0
  1626. $query = qq|
  1627. DELETE FROM inventory
  1628. WHERE trans_id = ?
  1629. AND qty = 0|;
  1630. $sth = $dbh->prepare($query);
  1631. $sth->execute( $form->{id} ) || $form->dberror($query);
  1632. }
  1633. sub get_inventory {
  1634. my ( $self, $myconfig, $form ) = @_;
  1635. my $where;
  1636. my $query;
  1637. my $null;
  1638. my $fromwarehouse_id;
  1639. my $towarehouse_id;
  1640. my $var;
  1641. my $dbh = $form->{dbh};
  1642. if ( $form->{partnumber} ne "" ) {
  1643. $var = $dbh->quote( $form->like( lc $form->{partnumber} ) );
  1644. $where .= "
  1645. AND lower(p.partnumber) LIKE '$var'";
  1646. }
  1647. if ( $form->{description} ne "" ) {
  1648. $var = $dbh->quote( $form->like( lc $form->{description} ) );
  1649. $where .= "
  1650. AND lower(p.description) LIKE '$var'";
  1651. }
  1652. if ( $form->{partsgroup} ne "" ) {
  1653. ( $null, $var ) = split /--/, $form->{partsgroup};
  1654. $var = $dbh->quote($var);
  1655. $where .= "
  1656. AND pg.id = $var";
  1657. }
  1658. ( $null, $fromwarehouse_id ) = split /--/, $form->{fromwarehouse};
  1659. $fromwarehouse_id = $dbh->quote($fromwarehouse_id);
  1660. ( $null, $towarehouse_id ) = split /--/, $form->{towarehouse};
  1661. $towarehouse_id = $dbh->quote($towarehouse_id);
  1662. my %ordinal = (
  1663. partnumber => 2,
  1664. description => 3,
  1665. partsgroup => 5,
  1666. warehouse => 6,
  1667. );
  1668. my @a = ( partnumber, warehouse );
  1669. my $sortorder = $form->sort_order( \@a, \%ordinal );
  1670. if ($fromwarehouse_id) {
  1671. if ($towarehouse_id) {
  1672. $where .= "
  1673. AND NOT i.warehouse_id = $towarehouse_id";
  1674. }
  1675. $query = qq|
  1676. SELECT p.id, p.partnumber, p.description,
  1677. sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty,
  1678. pg.partsgroup, w.description AS warehouse,
  1679. i.warehouse_id
  1680. FROM inventory i
  1681. JOIN parts p ON (p.id = i.parts_id)
  1682. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  1683. JOIN warehouse w ON (w.id = i.warehouse_id)
  1684. WHERE i.warehouse_id = $fromwarehouse_id
  1685. $where
  1686. GROUP BY p.id, p.partnumber, p.description,
  1687. pg.partsgroup, w.description, i.warehouse_id
  1688. ORDER BY $sortorder|;
  1689. }
  1690. else {
  1691. if ($towarehouse_id) {
  1692. $query = qq|
  1693. SELECT p.id, p.partnumber, p.description,
  1694. p.onhand,
  1695. (SELECT SUM(qty)
  1696. FROM inventory i
  1697. WHERE i.parts_id = p.id) AS qty,
  1698. pg.partsgroup, '' AS warehouse,
  1699. 0 AS warehouse_id
  1700. FROM parts p
  1701. LEFT JOIN partsgroup pg
  1702. ON (p.partsgroup_id = pg.id)
  1703. WHERE p.onhand > 0
  1704. $where
  1705. UNION|;
  1706. }
  1707. $query .= qq|
  1708. SELECT p.id, p.partnumber, p.description,
  1709. sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty,
  1710. pg.partsgroup, w.description AS warehouse,
  1711. i.warehouse_id
  1712. FROM inventory i
  1713. JOIN parts p ON (p.id = i.parts_id)
  1714. LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
  1715. JOIN warehouse w ON (w.id = i.warehouse_id)
  1716. WHERE i.warehouse_id != $towarehouse_id
  1717. $where
  1718. GROUP BY p.id, p.partnumber, p.description,
  1719. pg.partsgroup, w.description, i.warehouse_id
  1720. ORDER BY $sortorder|;
  1721. }
  1722. my $sth = $dbh->prepare($query);
  1723. $sth->execute || $form->dberror($query);
  1724. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1725. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1726. $ref->{qty} = $ref->{onhand} - $ref->{qty};
  1727. push @{ $form->{all_inventory} }, $ref if $ref->{qty} > 0;
  1728. }
  1729. $sth->finish;
  1730. $dbh->commit;
  1731. }
  1732. sub transfer {
  1733. my ( $self, $myconfig, $form ) = @_;
  1734. my $dbh = $form->{dbh};
  1735. ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh);
  1736. my @a = localtime;
  1737. $a[5] += 1900;
  1738. $a[4]++;
  1739. $a[4] = substr( "0$a[4]", -2 );
  1740. $a[3] = substr( "0$a[3]", -2 );
  1741. $shippingdate = "$a[5]$a[4]$a[3]";
  1742. my %total = ();
  1743. my $query = qq|
  1744. INSERT INTO inventory
  1745. (warehouse_id, parts_id, qty, shippingdate, employee_id)
  1746. VALUES (?, ?, ?, '$shippingdate', $form->{employee_id})|;
  1747. $sth = $dbh->prepare($query) || $form->dberror($query);
  1748. my $qty;
  1749. for my $i ( 1 .. $form->{rowcount} ) {
  1750. $qty = $form->parse_amount( $myconfig, $form->{"transfer_$i"} );
  1751. $qty = $form->{"qty_$i"} if ( $qty > $form->{"qty_$i"} );
  1752. if ( $qty > 0 ) {
  1753. # to warehouse
  1754. if ( $form->{warehouse_id} ) {
  1755. $sth->execute( $form->{warehouse_id}, $form->{"id_$i"}, $qty,
  1756. $shippingdate, $form->{employee_id} )
  1757. || $form->dberror;
  1758. $sth->finish;
  1759. }
  1760. # from warehouse
  1761. if ( $form->{"warehouse_id_$i"} ) {
  1762. $sth->execute( $form->{"warehouse_id_$i"},
  1763. $form->{"id_$i"}, $qty * -1 )
  1764. || $form->dberror;
  1765. $sth->finish;
  1766. }
  1767. }
  1768. }
  1769. my $rc = $dbh->commit;
  1770. $rc;
  1771. }
  1772. sub get_soparts {
  1773. my ( $self, $myconfig, $form ) = @_;
  1774. # connect to database
  1775. my $dbh = $form->{dbh};
  1776. my $id;
  1777. my $ref;
  1778. # store required items from selected sales orders
  1779. my $query = qq|
  1780. SELECT p.id, oi.qty - oi.ship AS required, p.assembly
  1781. FROM orderitems oi
  1782. JOIN parts p ON (p.id = oi.parts_id)
  1783. WHERE oi.trans_id = ?|;
  1784. my $sth = $dbh->prepare($query) || $form->dberror($query);
  1785. for ( my $i = 1 ; $i <= $form->{rowcount} ; $i++ ) {
  1786. if ( $form->{"ndx_$i"} ) {
  1787. $sth->execute( $form->{"ndx_$i"} );
  1788. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1789. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1790. &add_items_required( "", $dbh, $form, $ref->{id},
  1791. $ref->{required}, $ref->{assembly} );
  1792. }
  1793. $sth->finish;
  1794. }
  1795. }
  1796. $query = qq|SELECT current_date|;
  1797. ( $form->{transdate} ) = $dbh->selectrow_array($query);
  1798. # foreign exchange rates
  1799. &exchangerate_defaults( $dbh, $form );
  1800. $dbh->commit;
  1801. }
  1802. sub add_items_required {
  1803. my ( $self, $dbh, $form, $parts_id, $required, $assembly ) = @_;
  1804. my $query;
  1805. my $sth;
  1806. my $ref;
  1807. if ($assembly) {
  1808. $query = qq|
  1809. SELECT p.id, a.qty, p.assembly
  1810. FROM assembly a
  1811. JOIN parts p ON (p.id = a.parts_id)
  1812. WHERE a.id = ?|;
  1813. $sth = $dbh->prepare($query);
  1814. $sth->execute || $form->dberror($query);
  1815. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1816. $form->db_parse_numeric(sth=> $sth, hashref=> $ref);
  1817. &add_items_required( "", $dbh, $form, $ref->{id},
  1818. $required * $ref->{qty},
  1819. $ref->{assembly} );
  1820. }
  1821. $sth->finish;
  1822. }
  1823. else {
  1824. $query = qq|
  1825. SELECT partnumber, description, lastcost
  1826. FROM parts
  1827. WHERE id = ?|;
  1828. $sth = $dbh->prepare($query);
  1829. $sth->execute($parts_id) || $form->dberror($query);
  1830. $ref = $sth->fetchrow_hashref(NAME_lc);
  1831. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1832. for ( keys %$ref ) {
  1833. $form->{orderitems}{$parts_id}{$_} = $ref->{$_};
  1834. }
  1835. $sth->finish;
  1836. $form->{orderitems}{$parts_id}{required} += $required;
  1837. $query = qq|
  1838. SELECT pv.partnumber, pv.leadtime, pv.lastcost, pv.curr,
  1839. pv.vendor_id, v.name
  1840. FROM partsvendor pv
  1841. JOIN vendor v ON (v.id = pv.vendor_id)
  1842. WHERE pv.parts_id = ?|;
  1843. $sth = $dbh->prepare($query) || $form->dberror($query);
  1844. # get cost and vendor
  1845. $sth->execute($parts_id);
  1846. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1847. for ( keys %$ref ) {
  1848. $form->{orderitems}{$parts_id}{partsvendor}{ $ref->{vendor_id} }
  1849. {$_} = $ref->{$_};
  1850. }
  1851. }
  1852. $sth->finish;
  1853. }
  1854. }
  1855. sub generate_orders {
  1856. my ( $self, $myconfig, $form ) = @_;
  1857. my $i;
  1858. my %a;
  1859. my $query;
  1860. my $sth;
  1861. for ( $i = 1 ; $i <= $form->{rowcount} ; $i++ ) {
  1862. for (qw(qty lastcost)) {
  1863. $form->{"${_}_$i"} =
  1864. $form->parse_amount( $myconfig, $form->{"${_}_$i"} );
  1865. }
  1866. if ( $form->{"qty_$i"} ) {
  1867. ( $vendor, $vendor_id ) =
  1868. split /--/, $form->{"vendor_$i"};
  1869. if ($vendor_id) {
  1870. $a{$vendor_id}{ $form->{"id_$i"} }{qty} += $form->{"qty_$i"};
  1871. for (qw(curr lastcost)) {
  1872. $a{$vendor_id}{ $form->{"id_$i"} }{$_} = $form->{"${_}_$i"};
  1873. }
  1874. }
  1875. }
  1876. }
  1877. # connect to database
  1878. my $dbh = $form->{dbh};
  1879. # foreign exchange rates
  1880. &exchangerate_defaults( $dbh, $form );
  1881. my $amount;
  1882. my $netamount;
  1883. my $curr = "";
  1884. my %tax;
  1885. my $taxincluded = 0;
  1886. my $vendor_id;
  1887. my $description;
  1888. my $unit;
  1889. my $sellprice;
  1890. foreach $vendor_id ( keys %a ) {
  1891. %tax = ();
  1892. $query = qq|
  1893. SELECT v.curr, v.taxincluded, t.rate, c.accno
  1894. FROM vendor v
  1895. LEFT JOIN vendortax vt ON (v.id = vt.vendor_id)
  1896. LEFT JOIN tax t ON (t.chart_id = vt.chart_id)
  1897. LEFT JOIN chart c ON (c.id = t.chart_id)
  1898. WHERE v.id = ?|;
  1899. $sth = $dbh->prepare($query);
  1900. $sth->execute($vendor_id) || $form->dberror($query);
  1901. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1902. $form->db_parse_numeric(sth=>$sth, hashref=> $ref);
  1903. $curr = $ref->{curr};
  1904. $taxincluded = $ref->{taxincluded};
  1905. $tax{ $ref->{accno} } = $ref->{rate};
  1906. }
  1907. $sth->finish;
  1908. $curr ||= $form->{defaultcurrency};
  1909. $taxincluded *= 1;
  1910. my $uid = localtime;
  1911. $uid .= "$$";
  1912. # TODO: Make this function insert as much as possible
  1913. $query = qq|
  1914. INSERT INTO oe (ordnumber)
  1915. VALUES ('$uid')|;
  1916. $dbh->do($query) || $form->dberror($query);
  1917. $query = qq|SELECT id FROM oe WHERE ordnumber = '$uid'|;
  1918. $sth = $dbh->prepare($query);
  1919. $sth->execute || $form->dberror($query);
  1920. my ($id) = $sth->fetchrow_array;
  1921. $sth->finish;
  1922. $amount = 0;
  1923. $netamount = 0;
  1924. foreach my $parts_id ( keys %{ $a{$vendor_id} } ) {
  1925. if ( ( $form->{$curr} * $form->{ $a{$vendor_id}{$parts_id}{curr} } )
  1926. > 0 )
  1927. {
  1928. $sellprice =
  1929. $a{$vendor_id}{$parts_id}{lastcost} / $form->{$curr} *
  1930. $form->{ $a{$vendor_id}{$parts_id}{curr} };
  1931. }
  1932. else {
  1933. $sellprice = $a{$vendor_id}{$parts_id}{lastcost};
  1934. }
  1935. $sellprice = $form->round_amount( $sellprice, 2 );
  1936. my $linetotal =
  1937. $form->round_amount( $sellprice * $a{$vendor_id}{$parts_id}{qty},
  1938. 2 );
  1939. $query = qq|
  1940. SELECT p.description, p.unit, c.accno
  1941. FROM parts p
  1942. LEFT JOIN partstax pt ON (p.id = pt.parts_id)
  1943. LEFT JOIN chart c ON (c.id = pt.chart_id)
  1944. WHERE p.id = ?|;
  1945. $sth = $dbh->prepare($query);
  1946. $sth->execute($parts_id) || $form->dberror($query);
  1947. my $rate = 0;
  1948. my $taxes = '';
  1949. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1950. $description = $ref->{description};
  1951. $unit = $ref->{unit};
  1952. $rate += $tax{ $ref->{accno} };
  1953. $taxes .= "$ref->{accno} ";
  1954. }
  1955. $sth->finish;
  1956. chop $taxes;
  1957. my @taxaccounts = Tax::init_taxes( $form, $taxes );
  1958. $netamount += $linetotal;
  1959. if ($taxincluded) {
  1960. $amount += $linetotal;
  1961. }
  1962. else {
  1963. $amount +=
  1964. $form->round_amount(
  1965. Tax::apply_taxes( \@taxaccounts, $form, $linetotal ), 2 );
  1966. }
  1967. $query = qq|
  1968. INSERT INTO orderitems
  1969. (trans_id, parts_id, description,
  1970. qty, ship, sellprice, unit)
  1971. VALUES
  1972. (?, ?, ?, ?, 0, ?, ?)|;
  1973. $sth = $dbh->prepare($query);
  1974. $sth->execute( $id, $parts_id, $description,
  1975. $a{vendor_id}{parts_id}{qty},
  1976. $sellprice, $unit )
  1977. || $form->dberror($query);
  1978. }
  1979. my $ordnumber = $form->update_defaults( $myconfig, 'ponumber' );
  1980. my $null;
  1981. my $employee_id;
  1982. my $department_id;
  1983. ( $null, $employee_id ) = $form->get_employee($dbh);
  1984. ( $null, $department_id ) = split /--/, $form->{department};
  1985. $department_id *= 1;
  1986. $query = qq|
  1987. UPDATE oe SET
  1988. ordnumber = ?,
  1989. transdate = current_date,
  1990. vendor_id = ?,
  1991. customer_id = 0,
  1992. amount = ?,
  1993. netamount = ?,
  1994. taxincluded = ?,
  1995. curr = ?,
  1996. employee_id = ?,
  1997. department_id = ?,
  1998. ponumber = ?
  1999. WHERE id = ?|;
  2000. $sth = $dbh->prepare($query);
  2001. $sth->execute(
  2002. $ordnumber, $vendor_id, $amount,
  2003. $netamount, $taxincluded, $curr,
  2004. $employee_id, $department_id, $form->{ponumber},
  2005. $id
  2006. ) || $form->dberror($query);
  2007. }
  2008. my $rc = $dbh->commit;
  2009. $rc;
  2010. }
  2011. sub consolidate_orders {
  2012. my ( $self, $myconfig, $form ) = @_;
  2013. # connect to database
  2014. my $dbh = $form->{dbh};
  2015. my $i;
  2016. my $id;
  2017. my $ref;
  2018. my %oe = ();
  2019. my $query = qq|SELECT * FROM oe WHERE id = ?|;
  2020. my $sth = $dbh->prepare($query) || $form->dberror($query);
  2021. for ( $i = 1 ; $i <= $form->{rowcount} ; $i++ ) {
  2022. # retrieve order
  2023. if ( $form->{"ndx_$i"} ) {
  2024. $sth->execute( $form->{"ndx_$i"} );
  2025. $ref = $sth->fetchrow_hashref(NAME_lc);
  2026. $ref->{ndx} = $i;
  2027. $oe{oe}{ $ref->{curr} }{ $ref->{id} } = $ref;
  2028. $oe{vc}{ $ref->{curr} }{ $ref->{"$form->{vc}_id"} }++;
  2029. $sth->finish;
  2030. }
  2031. }
  2032. $query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
  2033. $sth = $dbh->prepare($query) || $form->dberror($query);
  2034. foreach $curr ( keys %{ $oe{oe} } ) {
  2035. foreach $id (
  2036. sort { $oe{oe}{$curr}{$a}->{ndx} <=> $oe{oe}{$curr}{$b}->{ndx} }
  2037. keys %{ $oe{oe}{$curr} }
  2038. )
  2039. {
  2040. # retrieve order
  2041. $vc_id = $oe{oe}{$curr}{$id}->{"$form->{vc}_id"};
  2042. if ( $oe{vc}{ $oe{oe}{$curr}{$id}->{curr} }{$vc_id} > 1 ) {
  2043. push @{ $oe{orders}{$curr}{$vc_id} }, $id;
  2044. $sth->execute($id);
  2045. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  2046. push @{ $oe{orderitems}{$curr}{$id} }, $ref;
  2047. }
  2048. $sth->finish;
  2049. }
  2050. }
  2051. }
  2052. my $ordnumber = $form->{ordnumber};
  2053. my $numberfld = ( $form->{vc} eq 'customer' ) ? 'sonumber' : 'ponumber';
  2054. my ( $department, $department_id ) = $form->{department};
  2055. $department_id *= 1;
  2056. my $uid = localtime;
  2057. $uid .= "$$";
  2058. my @orderitems = ();
  2059. foreach $curr ( keys %{ $oe{orders} } ) {
  2060. foreach $vc_id ( sort { $a <=> $b } keys %{ $oe{orders}{$curr} } ) {
  2061. # the orders
  2062. @orderitems = ();
  2063. $form->{customer_id} = $form->{vendor_id} = 0;
  2064. $form->{"$form->{vc}_id"} = $vc_id;
  2065. $amount = 0;
  2066. $netamount = 0;
  2067. foreach $id ( @{ $oe{orders}{$curr}{$vc_id} } ) {
  2068. # header
  2069. $ref = $oe{oe}{$curr}{$id};
  2070. $amount += $ref->{amount};
  2071. $netamount += $ref->{netamount};
  2072. $id = $dbh->quote($id);
  2073. foreach $item ( @{ $oe{orderitems}{$curr}{$id} } ) {
  2074. push @orderitems, $item;
  2075. }
  2076. # close order
  2077. $query = qq|
  2078. UPDATE oe SET
  2079. closed = '1'
  2080. WHERE id = $id|;
  2081. $dbh->do($query) || $form->dberror($query);
  2082. # reset shipped
  2083. $query = qq|
  2084. UPDATE orderitems SET
  2085. ship = 0
  2086. WHERE trans_id = $id|;
  2087. $dbh->do($query) || $form->dberror($query);
  2088. }
  2089. $ordnumber ||=
  2090. $form->update_defaults( $myconfig, $numberfld, $dbh );
  2091. #fixme: Change this
  2092. $query = qq|
  2093. INSERT INTO oe (ordnumber) VALUES ('$uid')|;
  2094. $dbh->do($query) || $form->dberror($query);
  2095. $query = qq|
  2096. SELECT id
  2097. FROM oe
  2098. WHERE ordnumber = '$uid'|;
  2099. ($id) = $dbh->selectrow_array($query);
  2100. $ref->{employee_id} *= 1;
  2101. $query = qq|
  2102. UPDATE oe SET
  2103. ordnumber = | . $dbh->quote($ordnumber) . qq|,
  2104. transdate = current_date,
  2105. vendor_id = $form->{vendor_id},
  2106. customer_id = $form->{customer_id},
  2107. amount = $amount,
  2108. netamount = $netamount,
  2109. reqdate = | . $form->dbquote( $ref->{reqdate}, SQL_DATE ) . qq|,
  2110. taxincluded = '$ref->{taxincluded}',
  2111. shippingpoint = | . $dbh->quote( $ref->{shippingpoint} ) . qq|,
  2112. notes = | . $dbh->quote( $ref->{notes} ) . qq|,
  2113. curr = '$curr',
  2114. employee_id = $ref->{employee_id},
  2115. intnotes = | . $dbh->quote( $ref->{intnotes} ) . qq|,
  2116. shipvia = | . $dbh->quote( $ref->{shipvia} ) . qq|,
  2117. language_code = '$ref->{language_code}',
  2118. ponumber = | . $dbh->quote( $form->{ponumber} ) . qq|,
  2119. department_id = $department_id
  2120. WHERE id = $id|;
  2121. $sth = $dbh->prepare($query);
  2122. $sth->execute() || $form->dberror($query);
  2123. # add items
  2124. foreach $item (@orderitems) {
  2125. for (
  2126. qw(
  2127. qty sellprice discount project_id ship)
  2128. )
  2129. {
  2130. $item->{$_} *= 1;
  2131. }
  2132. $query = qq|
  2133. INSERT INTO orderitems
  2134. (trans_id, parts_id, description,
  2135. qty, sellprice, discount, unit, reqdate,
  2136. project_id, ship, serialnumber, notes)
  2137. VALUES
  2138. (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
  2139. $sth = $dbh->prepare($query);
  2140. $sth->execute(
  2141. $id, $item->{parts_id},
  2142. $item->{description}, $item->{qty},
  2143. $item->{sellprice}, $item->{discount},
  2144. $item->{unit}, $form->{reqdate},
  2145. $item->{project_id}, $item->{ship},
  2146. $item->{serialnumber}, $item->{notes}
  2147. ) || $form->dberror($query);
  2148. }
  2149. }
  2150. }
  2151. $rc = $dbh->commit;
  2152. $rc;
  2153. }
  2154. 1;