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