summaryrefslogtreecommitdiff
path: root/LedgerSMB/CT.pm
blob: 4d4b260e121f3c374c9c031f49c90b2758f018e1 (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) 2000
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors:
  23. #
  24. #
  25. #======================================================================
  26. #
  27. # This file has undergone whitespace cleanup.
  28. #
  29. #======================================================================
  30. #
  31. # backend code for customers and vendors
  32. #
  33. #======================================================================
  34. package CT;
  35. sub create_links {
  36. my ( $self, $myconfig, $form ) = @_;
  37. my $dbh = $form->{dbh};
  38. my $query;
  39. my $sth;
  40. my $ref;
  41. my $arap = ( $form->{db} eq 'customer' ) ? "ar" : "ap";
  42. my $ARAP = uc $arap;
  43. if ( $form->{id} ) {
  44. $query = qq|
  45. SELECT ct.*, b.description AS business, s.*,
  46. e.name AS employee,
  47. g.pricegroup AS pricegroup,
  48. l.description AS language, ct.curr
  49. FROM $form->{db} ct
  50. LEFT JOIN business b ON (ct.business_id = b.id)
  51. LEFT JOIN shipto s ON (ct.id = s.trans_id)
  52. LEFT JOIN employees e ON (ct.employee_id = e.id)
  53. LEFT JOIN pricegroup g ON (g.id = ct.pricegroup_id)
  54. LEFT JOIN language l ON (l.code = ct.language_code)
  55. WHERE ct.id = ?|;
  56. $sth = $dbh->prepare($query);
  57. $sth->execute( $form->{id} ) || $form->dberror($query);
  58. $ref = $sth->fetchrow_hashref(NAME_lc);
  59. for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
  60. $sth->finish;
  61. # check if it is orphaned
  62. $query = qq|
  63. SELECT a.id
  64. FROM $arap a
  65. JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
  66. WHERE ct.id = ?
  67. UNION
  68. SELECT a.id
  69. FROM oe a
  70. JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
  71. WHERE ct.id = ?|;
  72. $sth = $dbh->prepare($query);
  73. $sth->execute( $form->{id}, $form->{id} )
  74. || $form->dberror($query);
  75. unless ( $sth->fetchrow_array ) {
  76. $form->{status} = "orphaned";
  77. }
  78. $sth->finish;
  79. # get taxes for customer/vendor
  80. $query = qq|
  81. SELECT c.accno
  82. FROM chart c
  83. JOIN $form->{db}tax t ON (t.chart_id = c.id)
  84. WHERE t.$form->{db}_id = ?|;
  85. $sth = $dbh->prepare($query);
  86. $sth->execute( $form->{id} ) || $form->dberror($query);
  87. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  88. $form->{tax}{ $ref->{accno} }{taxable} = 1;
  89. }
  90. $sth->finish;
  91. }
  92. else {
  93. ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh);
  94. $query = qq|SELECT current_date|;
  95. ( $form->{startdate} ) = $dbh->selectrow_array($query);
  96. }
  97. # get tax labels
  98. $query = qq|
  99. SELECT DISTINCT c.accno, c.description
  100. FROM chart c
  101. JOIN tax t ON (t.chart_id = c.id)
  102. WHERE c.link LIKE ?
  103. ORDER BY c.accno|;
  104. $sth = $dbh->prepare($query);
  105. $sth->execute("%${ARAP}_tax%") || $form->dberror($query);
  106. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  107. $form->{taxaccounts} .= "$ref->{accno} ";
  108. $form->{tax}{ $ref->{accno} }{description} = $ref->{description};
  109. }
  110. $sth->finish;
  111. chop $form->{taxaccounts};
  112. # get business types ## needs fixing, this is bad (SELECT * ...) with order by 2. Yuck
  113. $query = qq|
  114. SELECT *
  115. FROM business
  116. ORDER BY 2|;
  117. $sth = $dbh->prepare($query);
  118. $sth->execute || $form->dberror($query);
  119. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  120. push @{ $form->{all_business} }, $ref;
  121. }
  122. $sth->finish;
  123. # employees/salespersons
  124. $form->all_employees( $myconfig, $dbh, undef,
  125. ( $form->{vc} eq 'customer' )
  126. ? 1
  127. : 0 );
  128. # get language ## needs fixing, this is bad (SELECT * ...) with order by 2. Yuck
  129. $query = qq|
  130. SELECT *
  131. FROM language
  132. ORDER BY 2|;
  133. $sth = $dbh->prepare($query);
  134. $sth->execute || $form->dberror($query);
  135. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  136. push @{ $form->{all_language} }, $ref;
  137. }
  138. $sth->finish;
  139. # get pricegroups ## needs fixing, this is bad (SELECT * ...) with order by 2. Yuck
  140. $query = qq|
  141. SELECT *
  142. FROM pricegroup
  143. ORDER BY 2|;
  144. $sth = $dbh->prepare($query);
  145. $sth->execute || $form->dberror($query);
  146. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  147. push @{ $form->{all_pricegroup} }, $ref;
  148. }
  149. $sth->finish;
  150. # get currencies
  151. $query = qq|
  152. SELECT value AS currencies
  153. FROM defaults
  154. WHERE setting_key = 'curr'|;
  155. ( $form->{currencies} ) = $dbh->selectrow_array($query);
  156. $dbh->commit;
  157. }
  158. sub _save_vc {
  159. ($form) = @_;
  160. my $dbh = $form->{dbh};
  161. my $updated = 0;
  162. if ($form->{vc} eq 'customer'){
  163. $form->{vc} = 'customer';
  164. $form->{entity_class} = 2;
  165. } else {
  166. $form->{vc} = 'vendor';
  167. $form->{entity_class} = 1;
  168. }
  169. if ( $form->{id} ) {
  170. $query = qq|
  171. DELETE FROM $form->{vc}tax
  172. WHERE entity_id =
  173. (select entity_id from $form->{vc}
  174. WHERE id = ?)|;
  175. $sth = $dbh->prepare($query);
  176. $sth->execute( $form->{id} ) || $form->dberror($query);
  177. $query = qq|
  178. SELECT id
  179. FROM $form->{vc}
  180. WHERE id = ?|;
  181. $sth = $dbh->prepare($query);
  182. $sth->execute( $form->{id} ) || $form->dberror($query);
  183. if ( $sth->fetchrow_array ) {
  184. $sth->finish;
  185. $query = qq|
  186. UPDATE $form->{vc}
  187. SET discount = ?
  188. taxincluded = ?
  189. creditlimit = ?
  190. terms = ?
  191. $form->{vc}number = ?
  192. cc = ?
  193. bcc = ?
  194. business_id = ?
  195. sic_code = ?
  196. language_code = ?
  197. pricegroup_id = ?
  198. curr = ?
  199. startdate = ?
  200. enddate = ?
  201. invoice_notes = ?
  202. bic = ?
  203. iban = ?
  204. WHERE id = ?|;
  205. $sth = $dbh->prepare($query);
  206. $sth->execute(
  207. $form->{discount}, $form->{taxincluded}, $form->{creditlimit},
  208. $form->{terms}, $form->{"$form->{vc}number"}, $form->{cc},
  209. $form->{bcc}, $form->{business_id}, $form->{sic_code},
  210. $form->{language_code}, $form->{pricegroup_id},
  211. $form->{curr}, $form->{startdate}, $form->{enddate},
  212. $form->{invoice_notes}, $form->{bic}, $form->{iban}, $form->{id}
  213. ) || $form->dberror(__FILE__.":".__LINE__.":$query");
  214. $updated = 1;
  215. }
  216. }
  217. if (!$updated){
  218. # Creating Entity
  219. $query = qq|INSERT INTO entity (name, entity_class) VALUES (?, ?)|;
  220. $sth = $dbh->prepare($query);
  221. $sth->execute($form->{name}, $form->{entity_class});
  222. $sth->finish;
  223. ($form->{entity_id}) =
  224. $dbh->selectrow_array("SELECT currval('entity_id_seq')");
  225. # Creating LOCATION
  226. $query = qq|
  227. INSERT INTO location
  228. (line_one, line_two, city_province, mail_code,
  229. country_id)
  230. VALUES
  231. (?, ?, ?, ?,
  232. (SELECT id FROM country
  233. WHERE short_name = ? OR name = ?))
  234. |;
  235. $sth = $dbh->prepare($query);
  236. $sth->execute($form->{address1}, $form->{address2},
  237. "$form->{city}, $form->{state}", $form->{zipcode},
  238. $form->{country}, $form->{country}
  239. ) || $form->dberror($query);
  240. ($form->{location_id}) =
  241. $dbh->selectrow_array("SELECT currval('location_id_seq')");
  242. #Creating company
  243. $query = qq|
  244. INSERT INTO company
  245. (entity_id, entity_class_id, legal_name,
  246. primary_location_id, tax_id)
  247. VALUES
  248. (?, ?, ?, ?, ?)
  249. |;
  250. $sth = $dbh->prepare($query) || $form->dberror($query);
  251. $sth->execute($form->{entity_id}, $form->{entity_class},
  252. $form->{name},
  253. $form->{location_id}, $form->{taxnumber});
  254. #Creating customer record
  255. $query = qq|
  256. INSERT INTO customer
  257. (entity_id, discount, taxincluded, creditlimit, terms,
  258. customernumber, cc, bcc, business_id, sic_code,
  259. language_code, pricegroup_id, curr, startdate,
  260. enddate, invoice_notes, bic, iban)
  261. VALUES (?, ?, ?, ?, ?,
  262. ?, ?, ?, ?, ?,
  263. ?, ?, ?, ?,
  264. ?, ?, ?, ?)|;
  265. $sth = $dbh->prepare($query);
  266. $sth->execute(
  267. $form->{entity_id}, $form->{discount}, $form->{taxincluded},
  268. $form->{creditlimit},
  269. $form->{terms}, $form->{"$form->{vc}number"}, $form->{cc},
  270. $form->{bcc}, $form->{business_id}, $form->{sic_code},
  271. $form->{language_code}, $form->{pricegroup_id}, $form->{curr},
  272. $form->{startdate} || undef, $form->{enddate} || undef,
  273. $form->{invoice_notes},
  274. $form->{bic}, $form->{iban}
  275. ) || $form->dberror($query);
  276. }
  277. }
  278. sub save_customer {
  279. my ( $self, $myconfig, $form ) = @_;
  280. # connect to database
  281. my $dbh = $form->{dbh};
  282. my $query;
  283. my $sth;
  284. my $null;
  285. # remove double spaces
  286. $form->{name} =~ s/ / /g;
  287. # remove double minus and minus at the end
  288. $form->{name} =~ s/--+/-/g;
  289. $form->{name} =~ s/-+$//;
  290. # assign value discount, terms, creditlimit
  291. $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
  292. $form->{discount} /= 100;
  293. $form->{terms} *= 1;
  294. $form->{taxincluded} *= 1;
  295. $form->{creditlimit} =
  296. $form->parse_amount( $myconfig, $form->{creditlimit} );
  297. if ( !$form->{creditlimit} ) {
  298. $form->{creditlimit} = 0;
  299. }
  300. &_save_vc($form);
  301. # save taxes
  302. foreach $item ( split / /, $form->{taxaccounts} ) {
  303. if ( $form->{"tax_$item"} ) {
  304. $query = qq|
  305. INSERT INTO customertax (customer_id, chart_id)
  306. VALUES (?, (SELECT id
  307. FROM chart
  308. WHERE accno = ?))|;
  309. $sth = $dbh->prepare($query);
  310. $sth->execute( $form->{id}, $item )
  311. || $form->dberror($query);
  312. }
  313. }
  314. # add shipto
  315. $form->add_shipto( $dbh, $form->{id} );
  316. $dbh->commit;
  317. }
  318. sub save_vendor {
  319. my ( $self, $myconfig, $form ) = @_;
  320. # connect to database
  321. my $dbh = $form->{dbh};
  322. my $query;
  323. my $sth;
  324. my $null;
  325. # remove double spaces
  326. $form->{name} =~ s/ / /g;
  327. # remove double minus and minus at the end
  328. $form->{name} =~ s/--+/-/g;
  329. $form->{name} =~ s/-+$//;
  330. $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
  331. $form->{discount} /= 100;
  332. $form->{terms} *= 1;
  333. $form->{taxincluded} *= 1;
  334. $form->{creditlimit} =
  335. $form->parse_amount( $myconfig, $form->{creditlimit} );
  336. &_save_vc($form);
  337. # save taxes
  338. foreach $item ( split / /, $form->{taxaccounts} ) {
  339. if ( $form->{"tax_$item"} ) {
  340. $query = qq|
  341. INSERT INTO vendortax (vendor_id, chart_id)
  342. VALUES (?, (SELECT id
  343. FROM chart
  344. WHERE accno = ?))|;
  345. $sth = $dbh->prepare($query);
  346. $sth->execute( $form->{id}, $item )
  347. || $form->dberror($query);
  348. }
  349. }
  350. # add shipto
  351. $form->add_shipto( $dbh, $form->{id} );
  352. $dbh->commit;
  353. }
  354. sub delete {
  355. my ( $self, $myconfig, $form ) = @_;
  356. # connect to database
  357. my $dbh = $form->{dbh};
  358. # delete customer/vendor
  359. my $query = qq|DELETE FROM $form->{db}
  360. WHERE id = ?|;
  361. $sth = $dbh->prepare($query);
  362. $sth->execute( $form->{id} ) || $form->dberror($query);
  363. $dbh->commit;
  364. }
  365. sub search {
  366. my ( $self, $myconfig, $form ) = @_;
  367. # connect to database
  368. my $dbh = $form->{dbh};
  369. my $where = "1 = 1";
  370. $form->{sort} = ( $form->{sort} ) ? $form->{sort} : "name";
  371. my @a = qw(name);
  372. my $sortorder = $form->sort_order( \@a );
  373. my $var;
  374. my $item;
  375. @a = ("$form->{db}number");
  376. push @a, qw(name contact city state zipcode country notes phone email);
  377. if ( $form->{employee} ) {
  378. $var = $form->like( lc $form->{employee} );
  379. $where .= " AND lower(e.name) LIKE '$var'";
  380. }
  381. foreach $item (@a) {
  382. if ( $form->{$item} ne "" ) {
  383. $var = $form->like( lc $form->{$item} );
  384. $where .= " AND lower(ct.$item) LIKE '$var'";
  385. }
  386. }
  387. if ( $form->{address} ne "" ) {
  388. $var = $dbh->quote( $form->like( lc $form->{address} ) );
  389. $where .=
  390. " AND (lower(ct.address1) ILIKE $var)";
  391. }
  392. if ( $form->{startdatefrom} ) {
  393. $where .=
  394. " AND ct.startdate >= " . $dbh->quote( $form->{startdatefrom} );
  395. }
  396. if ( $form->{startdateto} ) {
  397. $where .= " AND ct.startdate <= " . $dbh->quote( $form->{startdateto} );
  398. }
  399. if ( $form->{status} eq 'active' ) {
  400. $where .= " AND ct.enddate IS NULL";
  401. }
  402. if ( $form->{status} eq 'inactive' ) {
  403. $where .= " AND ct.enddate <= current_date";
  404. }
  405. if ( $form->{status} eq 'orphaned' ) {
  406. $where .= qq|
  407. AND ct.id NOT IN (SELECT o.$form->{db}_id
  408. FROM oe o, $form->{db} vc
  409. WHERE vc.id = o.$form->{db}_id)|;
  410. if ( $form->{db} =~ /(^customer$|^vendor$)/ ) {
  411. $where .= qq| AND ct.id NOT IN (SELECT a.entity_id
  412. FROM ar a, customer vc
  413. WHERE vc.entity_id = a.entity_id)|;
  414. }
  415. $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
  416. }
  417. my $query = qq|
  418. SELECT ct.*, b.description AS business,
  419. e.name AS employee, g.pricegroup,
  420. l.description AS language, m.name AS manager
  421. FROM $form->{db} ct
  422. LEFT JOIN business b ON (ct.business_id = b.id)
  423. LEFT JOIN employees e ON (ct.employee_id = e.id)
  424. LEFT JOIN employees m ON (m.id = e.managerid)
  425. LEFT JOIN pricegroup g ON (ct.pricegroup_id = g.id)
  426. LEFT JOIN language l ON (l.code = ct.language_code)
  427. WHERE $where|;
  428. # redo for invoices, orders and quotations
  429. if ( $form->{l_transnumber}
  430. || $form->{l_invnumber}
  431. || $form->{l_ordnumber}
  432. || $form->{l_quonumber} )
  433. {
  434. my ( $ar, $union, $module );
  435. $query = "";
  436. my $transwhere;
  437. my $openarap = "";
  438. my $openoe = "";
  439. if ( $form->{open} || $form->{closed} ) {
  440. unless ( $form->{open} && $form->{closed} ) {
  441. $openarap = " AND a.amount != a.paid"
  442. if $form->{open};
  443. $openarap = " AND a.amount = a.paid"
  444. if $form->{closed};
  445. $openoe = " AND o.closed = '0'"
  446. if $form->{open};
  447. $openoe = " AND o.closed = '1'"
  448. if $form->{closed};
  449. }
  450. }
  451. if ( $form->{l_transnumber} ) {
  452. $ar = ( $form->{db} eq 'customer' ) ? 'ar' : 'ap';
  453. $module = $ar;
  454. $transwhere = "";
  455. $transwhere .=
  456. " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
  457. if $form->{transdatefrom};
  458. $transwhere .=
  459. " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
  460. if $form->{transdateto};
  461. $query = qq|
  462. SELECT ct.*, b.description AS business,
  463. a.invnumber, a.ordnumber,
  464. a.quonumber,
  465. a.id AS invid, '$ar' AS module,
  466. 'invoice' AS formtype,
  467. (a.amount = a.paid) AS closed,
  468. a.amount,
  469. a.netamount, e.name AS employee,
  470. m.name AS manager
  471. FROM $form->{db} ct
  472. JOIN $ar a ON (a.$form->{db}_id = ct.id)
  473. LEFT JOIN business b ON (ct.business_id = b.id)
  474. LEFT JOIN employees e ON (a.employee_id = e.id)
  475. LEFT JOIN employees m ON (m.id = e.managerid)
  476. WHERE $where
  477. AND a.invoice = '0'
  478. $transwhere
  479. $openarap |;
  480. $union = qq| UNION |;
  481. }
  482. if ( $form->{l_invnumber} ) {
  483. $ar = ( $form->{db} eq 'customer' ) ? 'ar' : 'ap';
  484. $module = ( $ar eq 'ar' ) ? 'is' : 'ir';
  485. $transwhere = "";
  486. $transwhere .=
  487. " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
  488. if $form->{transdatefrom};
  489. $transwhere .=
  490. " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
  491. if $form->{transdateto};
  492. $query .= qq|
  493. $union
  494. SELECT ct.*, b.description AS business,
  495. a.invnumber, a.ordnumber, a.quonumber,
  496. a.id AS invid,
  497. '$module' AS module,
  498. 'invoice' AS formtype,
  499. (a.amount = a.paid) AS closed,
  500. a.amount, a.netamount,
  501. e.name AS employee, m.name AS manager
  502. FROM $form->{db} ct
  503. JOIN $ar a ON (a.$form->{db}_id = ct.id)
  504. LEFT JOIN business b ON (ct.business_id = b.id)
  505. LEFT JOIN employees e ON (a.employee_id = e.id)
  506. LEFT JOIN employees m ON (m.id = e.managerid)
  507. WHERE $where
  508. AND a.invoice = '1'
  509. $transwhere
  510. $openarap |;
  511. $union = qq| UNION|;
  512. }
  513. if ( $form->{l_ordnumber} ) {
  514. $transwhere = "";
  515. $transwhere .=
  516. " AND o.transdate >= " . $dbh->quote( $form->{transdatefrom} )
  517. if $form->{transdatefrom};
  518. $transwhere .=
  519. " AND o.transdate <= " . $dbh->quote( $form->{transdateto} )
  520. if $form->{transdateto};
  521. $query .= qq|
  522. $union
  523. SELECT ct.*, b.description AS business,
  524. ' ' AS invnumber, o.ordnumber,
  525. o.quonumber, o.id AS invid,
  526. 'oe' AS module, 'order' AS formtype,
  527. o.closed, o.amount, o.netamount,
  528. e.name AS employee, m.name AS manager
  529. FROM $form->{db} ct
  530. JOIN oe o ON (o.$form->{db}_id = ct.id)
  531. LEFT JOIN business b ON (ct.business_id = b.id)
  532. LEFT JOIN employees e ON (o.employee_id = e.id)
  533. LEFT JOIN employees m ON (m.id = e.managerid)
  534. WHERE $where
  535. AND o.quotation = '0'
  536. $transwhere
  537. $openoe |;
  538. $union = qq| UNION|;
  539. }
  540. if ( $form->{l_quonumber} ) {
  541. $transwhere = "";
  542. $transwhere .=
  543. " AND o.transdate >= " . $dbh->quote( $form->{transdatefrom} )
  544. if $form->{transdatefrom};
  545. $transwhere .=
  546. " AND o.transdate <= " . $dbh->quote( $form->{transdateto} )
  547. if $form->{transdateto};
  548. $query .= qq|
  549. $union
  550. SELECT ct.*, b.description AS business,
  551. ' ' AS invnumber, o.ordnumber,
  552. o.quonumber, o.id AS invid,
  553. 'oe' AS module,
  554. 'quotation' AS formtype,
  555. o.closed, o.amount, o.netamount,
  556. e.name AS employee, m.name AS manager
  557. FROM $form->{db} ct
  558. JOIN oe o ON (o.$form->{db}_id = ct.id)
  559. LEFT JOIN business b ON (ct.business_id = b.id)
  560. LEFT JOIN employees e ON (o.employee_id = e.id)
  561. LEFT JOIN employees m ON (m.id = e.managerid)
  562. WHERE $where
  563. AND o.quotation = '1'
  564. $transwhere
  565. $openoe |;
  566. }
  567. $sortorder .= ", invid";
  568. }
  569. $query .= qq| ORDER BY $sortorder|;
  570. my $sth = $dbh->prepare($query);
  571. $sth->execute || $form->dberror($query);
  572. # accounts
  573. $query = qq|
  574. SELECT c.accno
  575. FROM chart c
  576. JOIN $form->{db}tax t ON (t.chart_id = c.id)
  577. WHERE t.$form->{db}_id = ?|;
  578. my $tth = $dbh->prepare($query);
  579. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  580. $tth->execute( $ref->{id} );
  581. while ( ($item) = $tth->fetchrow_array ) {
  582. $ref->{taxaccount} .= "$item ";
  583. }
  584. $tth->finish;
  585. chop $ref->{taxaccount};
  586. $ref->{address} = "";
  587. for (qw(address1 address2 city state zipcode country)) {
  588. $ref->{address} .= "$ref->{$_} ";
  589. }
  590. push @{ $form->{CT} }, $ref;
  591. }
  592. $sth->finish;
  593. $dbh->commit;
  594. }
  595. sub get_history {
  596. my ( $self, $myconfig, $form ) = @_;
  597. # connect to database
  598. my $dbh = $form->{dbh};
  599. my $query;
  600. my $where = "1 = 1";
  601. $form->{sort} = "partnumber" unless $form->{sort};
  602. my $sortorder = $form->{sort};
  603. my %ordinal = ();
  604. my $var;
  605. my $table;
  606. # setup ASC or DESC
  607. $form->sort_order();
  608. if ( $form->{"$form->{db}number"} ne "" ) {
  609. $var = $dbh->( $form->like( lc $form->{"$form->{db}number"} ) );
  610. $where .= " AND lower(ct.$form->{db}number) LIKE $var";
  611. }
  612. if ( $form->{address} ne "" ) {
  613. $var = $dbh->quote( $form->like( lc $form->{address} ) );
  614. $where .= " AND lower(ct.address1) ILIKE $var";
  615. }
  616. for (qw(name contact email phone notes city state zipcode country)) {
  617. if ( $form->{$_} ne "" ) {
  618. $var = $dbh->quote( $form->like( lc $form->{$_} ) );
  619. $where .= " AND lower(ct.$_) LIKE $var";
  620. }
  621. }
  622. if ( $form->{employee} ne "" ) {
  623. $var = $form->like( lc $form->{employee} );
  624. $where .= " AND lower(e.name) LIKE '$var'";
  625. }
  626. $transwhere .=
  627. " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
  628. if $form->{transdatefrom};
  629. $transwhere .= " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
  630. if $form->{transdateto};
  631. if ( $form->{open} || $form->{closed} ) {
  632. unless ( $form->{open} && $form->{closed} ) {
  633. if ( $form->{type} eq 'invoice' ) {
  634. $where .= " AND a.amount != a.paid"
  635. if $form->{open};
  636. $where .= " AND a.amount = a.paid"
  637. if $form->{closed};
  638. }
  639. else {
  640. $where .= " AND a.closed = '0'"
  641. if $form->{open};
  642. $where .= " AND a.closed = '1'"
  643. if $form->{closed};
  644. }
  645. }
  646. }
  647. my $invnumber = 'invnumber';
  648. my $deldate = 'deliverydate';
  649. my $buysell;
  650. my $sellprice = "sellprice";
  651. if ( $form->{db} eq 'customer' ) {
  652. $buysell = "buy";
  653. if ( $form->{type} eq 'invoice' ) {
  654. $where .= qq|
  655. AND a.invoice = '1' AND i.assemblyitem = '0'|;
  656. $table = 'ar';
  657. $sellprice = "fxsellprice";
  658. }
  659. else {
  660. $table = 'oe';
  661. if ( $form->{type} eq 'order' ) {
  662. $invnumber = 'ordnumber';
  663. $where .= qq| AND a.quotation = '0'|;
  664. }
  665. else {
  666. $invnumber = 'quonumber';
  667. $where .= qq| AND a.quotation = '1'|;
  668. }
  669. $deldate = 'reqdate';
  670. }
  671. }
  672. if ( $form->{db} eq 'vendor' ) {
  673. $buysell = "sell";
  674. if ( $form->{type} eq 'invoice' ) {
  675. $where .= qq| AND a.invoice = '1' AND i.assemblyitem = '0'|;
  676. $table = 'ap';
  677. $sellprice = "fxsellprice";
  678. }
  679. else {
  680. $table = 'oe';
  681. if ( $form->{type} eq 'order' ) {
  682. $invnumber = 'ordnumber';
  683. $where .= qq| AND a.quotation = '0'|;
  684. }
  685. else {
  686. $invnumber = 'quonumber';
  687. $where .= qq| AND a.quotation = '1'|;
  688. }
  689. $deldate = 'reqdate';
  690. }
  691. }
  692. my $invjoin = qq| JOIN invoice i ON (i.trans_id = a.id)|;
  693. if ( $form->{type} eq 'order' ) {
  694. $invjoin = qq| JOIN orderitems i ON (i.trans_id = a.id)|;
  695. }
  696. if ( $form->{type} eq 'quotation' ) {
  697. $invjoin = qq| JOIN orderitems i ON (i.trans_id = a.id)|;
  698. $where .= qq| AND a.quotation = '1'|;
  699. }
  700. %ordinal = (
  701. partnumber => 9,
  702. description => 12,
  703. "$deldate" => 16,
  704. serialnumber => 17,
  705. projectnumber => 18
  706. );
  707. $sortorder =
  708. "2 $form->{direction}, 1, 11, $ordinal{$sortorder} $form->{direction}";
  709. $query = qq|
  710. SELECT ct.id AS ctid, ct.name, ct.address1,
  711. ct.address2, ct.city, ct.state,
  712. p.id AS pid, p.partnumber, a.id AS invid,
  713. a.$invnumber, a.curr, i.description,
  714. i.qty, i.$sellprice AS sellprice, i.discount,
  715. i.$deldate, i.serialnumber, pr.projectnumber,
  716. e.name AS employee, ct.zipcode, ct.country, i.unit,
  717. (SELECT $buysell
  718. FROM exchangerate ex
  719. WHERE a.curr = ex.curr
  720. AND a.transdate = ex.transdate) AS exchangerate
  721. FROM $form->{db} ct
  722. JOIN $table a ON (a.$form->{db}_id = ct.id)
  723. $invjoin
  724. JOIN parts p ON (p.id = i.parts_id)
  725. LEFT JOIN project pr ON (pr.id = i.project_id)
  726. LEFT JOIN employees e ON (e.id = a.employee_id)
  727. WHERE $where
  728. ORDER BY $sortorder|;
  729. my $sth = $dbh->prepare($query);
  730. $sth->execute || $form->dberror($query);
  731. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  732. $ref->{address} = "";
  733. $ref->{exchangerate} ||= 1;
  734. for (qw(address1 address2 city state zipcode country)) {
  735. $ref->{address} .= "$ref->{$_} ";
  736. }
  737. $ref->{id} = $ref->{ctid};
  738. push @{ $form->{CT} }, $ref;
  739. }
  740. $sth->finish;
  741. $dbh->commit;
  742. }
  743. sub pricelist {
  744. my ( $self, $myconfig, $form ) = @_;
  745. # connect to database
  746. my $dbh = $form->{dbh};
  747. my $query;
  748. if ( $form->{db} eq 'customer' ) {
  749. $query = qq|SELECT p.id, p.partnumber, p.description,
  750. p.sellprice, pg.partsgroup, p.partsgroup_id,
  751. m.pricebreak, m.sellprice,
  752. m.validfrom, m.validto, m.curr
  753. FROM partscustomer m
  754. JOIN parts p ON (p.id = m.parts_id)
  755. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  756. WHERE m.customer_id = ?
  757. ORDER BY partnumber|;
  758. }
  759. if ( $form->{db} eq 'vendor' ) {
  760. $query = qq|SELECT p.id, p.partnumber AS sku, p.description,
  761. pg.partsgroup, p.partsgroup_id,
  762. m.partnumber, m.leadtime, m.lastcost, m.curr
  763. FROM partsvendor m
  764. JOIN parts p ON (p.id = m.parts_id)
  765. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  766. WHERE m.vendor_id = ?
  767. ORDER BY p.partnumber|;
  768. }
  769. my $sth;
  770. my $ref;
  771. if ( $form->{id} ) {
  772. $sth = $dbh->prepare($query);
  773. $sth->execute( $form->{id} ) || $form->dberror($query);
  774. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  775. push @{ $form->{all_partspricelist} }, $ref;
  776. }
  777. $sth->finish;
  778. }
  779. $query = qq|SELECT value FROM defaults where setting_key = 'curr'|;
  780. ( $form->{currencies} ) = $dbh->selectrow_array($query);
  781. $query = qq|SELECT id, partsgroup
  782. FROM partsgroup
  783. ORDER BY partsgroup|;
  784. $sth = $dbh->prepare($query);
  785. $sth->execute || $self->dberror($query);
  786. $form->{all_partsgroup} = ();
  787. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  788. push @{ $form->{all_partsgroup} }, $ref;
  789. }
  790. $sth->finish;
  791. $dbh->commit;
  792. }
  793. sub save_pricelist {
  794. my ( $self, $myconfig, $form ) = @_;
  795. my $dbh = $form->{dbh};
  796. my $query = qq|
  797. DELETE FROM parts$form->{db}
  798. WHERE $form->{db}_id = ?}|;
  799. $sth = $dbh->prepare($query);
  800. $sth->execute( $form->{id} ) || $form->dberror($query);
  801. foreach $i ( 1 .. $form->{rowcount} ) {
  802. if ( $form->{"id_$i"} ) {
  803. if ( $form->{db} eq 'customer' ) {
  804. for (qw(pricebreak sellprice)) {
  805. $form->{"${_}_$i"} =
  806. $form->parse_amount( $myconfig, $form->{"${_}_$i"} );
  807. }
  808. $query = qq|
  809. INSERT INTO parts$form->{db}
  810. (parts_id, customer_id,
  811. pricebreak, sellprice,
  812. validfrom, validto, curr)
  813. VALUES (?, ?, ?, ?, ?, ?, ?)|;
  814. @queryargs = (
  815. $form->{"id_$i"}, $form->{id},
  816. $form->{"pricebreak_$i"}, $form->{"sellprice_$i"},
  817. $form->{"validfrom_$i"}, $form->{"validto_$i"},
  818. $form->{"curr_$i"}
  819. );
  820. }
  821. else {
  822. for (qw(leadtime lastcost)) {
  823. $form->{"${_}_$i"} =
  824. $form->parse_amount( $myconfig, $form->{"${_}_$i"} );
  825. }
  826. $query = qq|
  827. INSERT INTO parts$form->{db}
  828. (parts_id, vendor_id,
  829. partnumber, lastcost,
  830. leadtime, curr)
  831. VALUES (?, ?, ?, ?, ?, ?)|;
  832. @queryargs = (
  833. $form->{"id_$i"}, $form->{id},
  834. $form->{"partnumber_$i"}, $form->{"lastcost_$i"},
  835. $form->{"leadtime_$i"}, $form->{"curr_$i"}
  836. );
  837. }
  838. $sth = $dbh->prepare($query);
  839. $sth->execute(@queryargs) || $form->dberror($query);
  840. }
  841. }
  842. $_ = $dbh->commit;
  843. }
  844. sub retrieve_item {
  845. my ( $self, $myconfig, $form ) = @_;
  846. # connect to database
  847. my $dbh = $form->{dbh};
  848. my $i = $form->{rowcount};
  849. my $var;
  850. my $null;
  851. my $where = "WHERE p.obsolete = '0'";
  852. if ( $form->{db} eq 'vendor' ) {
  853. # parts, services, labor
  854. $where .= " AND p.assembly = '0'";
  855. }
  856. if ( $form->{db} eq 'customer' ) {
  857. # parts, assemblies, services
  858. $where .= " AND p.income_accno_id > 0";
  859. }
  860. if ( $form->{"partnumber_$i"} ne "" ) {
  861. $var = $dbh->quote( $form->like( lc $form->{"partnumber_$i"} ) );
  862. $where .= " AND lower(p.partnumber) LIKE $var";
  863. }
  864. if ( $form->{"description_$i"} ne "" ) {
  865. $var = $dbh->quote( $form->like( lc $form->{"description_$i"} ) );
  866. $where .= " AND lower(p.description) LIKE $var";
  867. }
  868. if ( $form->{"partsgroup_$i"} ne "" ) {
  869. ( $null, $var ) = split /--/, $form->{"partsgroup_$i"};
  870. $var = $dbh->quote($var);
  871. $where .= qq| AND p.partsgroup_id = $var|;
  872. }
  873. my $query = qq|
  874. SELECT p.id, p.partnumber, p.description, p.sellprice,
  875. p.lastcost, p.unit, pg.partsgroup, p.partsgroup_id
  876. FROM parts p
  877. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  878. $where
  879. ORDER BY partnumber|;
  880. my $sth = $dbh->prepare($query);
  881. $sth->execute || $form->dberror($query);
  882. my $ref;
  883. $form->{item_list} = ();
  884. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  885. push @{ $form->{item_list} }, $ref;
  886. }
  887. $sth->finish;
  888. $dbh->commit;
  889. }
  890. 1;