summaryrefslogtreecommitdiff
path: root/LedgerSMB/CT.pm
blob: 43236c4b54ffa52c1e896a264d23ceb2c0c0aa55 (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 employee 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_customer {
  159. my ( $self, $myconfig, $form ) = @_;
  160. # connect to database
  161. my $dbh = $form->{dbh};
  162. my $query;
  163. my $sth;
  164. my $null;
  165. $form->{customernumber} =
  166. $form->update_defaults( $myconfig, "customernumber", $dbh )
  167. if !$form->{customernumber};
  168. # remove double spaces
  169. $form->{name} =~ s/ / /g;
  170. # remove double minus and minus at the end
  171. $form->{name} =~ s/--+/-/g;
  172. $form->{name} =~ s/-+$//;
  173. # assign value discount, terms, creditlimit
  174. $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
  175. $form->{discount} /= 100;
  176. $form->{terms} *= 1;
  177. $form->{taxincluded} *= 1;
  178. $form->{creditlimit} =
  179. $form->parse_amount( $myconfig, $form->{creditlimit} );
  180. if ( !$form->{creditlimit} ) {
  181. $form->{creditlimit} = 0;
  182. }
  183. if ( $form->{id} ) {
  184. $query = qq|
  185. DELETE FROM customertax
  186. WHERE customer_id = ?|;
  187. $sth = $dbh->prepare($query);
  188. $sth->execute( $form->{id} ) || $form->dberror($query);
  189. $query = qq|
  190. DELETE FROM shipto
  191. WHERE trans_id = ?|;
  192. $sth = $dbh->prepare($query);
  193. $sth->execute( $form->{id} ) || $form->dberror($query);
  194. $query = qq|
  195. SELECT id
  196. FROM customer
  197. WHERE id = ?|;
  198. $sth = $dbh->prepare($query);
  199. $sth->execute( $form->{id} ) || $form->dberror($query);
  200. if ( !$sth->fetchrow_array ) {
  201. $query = qq|
  202. INSERT INTO customer (id)
  203. VALUES (?)|;
  204. $sth = $dbh->prepare($query);
  205. $sth->execute( $form->{id} ) || $form->dberror($query);
  206. }
  207. # retrieve enddate
  208. if ( $form->{type} && $form->{enddate} ) {
  209. my $now;
  210. $query = qq|
  211. SELECT enddate, current_date AS now
  212. FROM customer|;
  213. ( $form->{enddate}, $now ) = $dbh->selectrow_array($query);
  214. $form->{enddate} = $now if $form->{enddate} lt $now;
  215. }
  216. }
  217. else {
  218. my $uid = localtime;
  219. $uid .= "$$";
  220. $query = qq|INSERT INTO customer (name)
  221. VALUES ('$uid')|;
  222. $dbh->do($query) || $form->dberror($query);
  223. $query = qq|SELECT id
  224. FROM customer
  225. WHERE name = '$uid'|;
  226. ( $form->{id} ) = $dbh->selectrow_array($query);
  227. }
  228. my $employee_id;
  229. ( $null, $employee_id ) = split /--/, $form->{employee};
  230. $employee_id *= 1;
  231. my $pricegroup_id;
  232. ( $null, $pricegroup_id ) = split /--/, $form->{pricegroup};
  233. $pricegroup_id *= 1;
  234. my $business_id;
  235. ( $null, $business_id ) = split /--/, $form->{business};
  236. $business_id *= 1;
  237. my $language_code;
  238. ( $null, $language_code ) = split /--/, $form->{language};
  239. $query = qq|
  240. UPDATE customer
  241. SET customernumber = ?,
  242. name = ?,
  243. address1 = ?,
  244. address2 = ?,
  245. city = ?,
  246. state = ?,
  247. zipcode = ?,
  248. country = ?,
  249. contact = ?,
  250. phone = ?,
  251. fax = ?,
  252. email = ?,
  253. cc = ?,
  254. bcc = ?,
  255. notes = ?,
  256. discount = ?,
  257. creditlimit = ?,
  258. terms = ?,
  259. taxincluded = ?,
  260. business_id = ?,
  261. taxnumber = ?,
  262. sic_code = ?,
  263. iban = ?,
  264. bic = ?,
  265. employee_id = ?,
  266. pricegroup_id = ?,
  267. language_code = ?,
  268. curr = ?,
  269. startdate = ?,
  270. enddate = ?
  271. WHERE id = ?|;
  272. $sth = $dbh->prepare($query);
  273. if ( !$form->{startdate} ) {
  274. undef $form->{startdate};
  275. }
  276. if ( !$form->{enddate} ) {
  277. undef $form->{enddate};
  278. }
  279. $sth->execute(
  280. $form->{customernumber}, $form->{name}, $form->{address1},
  281. $form->{address2}, $form->{city}, $form->{state},
  282. $form->{zipcode}, $form->{country}, $form->{contact},
  283. $form->{phone}, $form->{fax}, $form->{email},
  284. $form->{cc}, $form->{bcc}, $form->{notes},
  285. $form->{discount}, $form->{creditlimit}, $form->{terms},
  286. $form->{taxincluded}, $business_id, $form->{taxnumber},
  287. $form->{sic_code}, $form->{iban}, $form->{bic},
  288. $employee_id, $pricegroup_id, $language_code,
  289. $form->{curr}, $form->{startdate}, $form->{enddate},
  290. $form->{id}
  291. ) || $form->dberror($query);
  292. # save taxes
  293. foreach $item ( split / /, $form->{taxaccounts} ) {
  294. if ( $form->{"tax_$item"} ) {
  295. $query = qq|
  296. INSERT INTO customertax (customer_id, chart_id)
  297. VALUES (?, (SELECT id
  298. FROM chart
  299. WHERE accno = ?))|;
  300. $sth = $dbh->prepare($query);
  301. $sth->execute( $form->{id}, $item )
  302. || $form->dberror($query);
  303. }
  304. }
  305. # add shipto
  306. $form->add_shipto( $dbh, $form->{id} );
  307. $dbh->commit;
  308. }
  309. sub save_vendor {
  310. my ( $self, $myconfig, $form ) = @_;
  311. # connect to database
  312. my $dbh = $form->{dbh};
  313. $form->{vendornumber} =
  314. $form->update_defaults( $myconfig, "vendornumber", $dbh )
  315. if !$form->{vendornumber};
  316. my $query;
  317. my $sth;
  318. my $null;
  319. # remove double spaces
  320. $form->{name} =~ s/ / /g;
  321. # remove double minus and minus at the end
  322. $form->{name} =~ s/--+/-/g;
  323. $form->{name} =~ s/-+$//;
  324. $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
  325. $form->{discount} /= 100;
  326. $form->{terms} *= 1;
  327. $form->{taxincluded} *= 1;
  328. $form->{creditlimit} =
  329. $form->parse_amount( $myconfig, $form->{creditlimit} );
  330. if ( $form->{id} ) {
  331. $query = qq|DELETE FROM vendortax
  332. WHERE vendor_id = ?|;
  333. $sth = $dbh->prepare($query);
  334. $sth->execute( $form->{id} ) || $form->dberror($query);
  335. $query = qq|DELETE FROM shipto
  336. WHERE trans_id = ?|;
  337. $sth = $dbh->prepare($query);
  338. $sth->execute( $form->{id} ) || $form->dberror($query);
  339. $query = qq|SELECT id
  340. FROM vendor
  341. WHERE id = ?|;
  342. $sth = $dbh->prepare($query);
  343. $sth->execute( $form->{id} ) || $form->dberror($query);
  344. if ( !$sth->fetchrow_array ) {
  345. $query = qq|INSERT INTO vendor (id)
  346. VALUES (?)|;
  347. $sth = $dbh->prepare($query);
  348. $sth->execute( $form->{id} ) || $form->dberror($query);
  349. }
  350. # retrieve enddate
  351. if ( $form->{type} && $form->{enddate} ) {
  352. my $now;
  353. $query = qq|SELECT enddate, current_date AS now FROM vendor|;
  354. ( $form->{enddate}, $now ) = $dbh->selectrow_array($query);
  355. $form->{enddate} = $now if $form->{enddate} lt $now;
  356. }
  357. }
  358. else {
  359. my $uid = localtime;
  360. $uid .= "$$";
  361. $query = qq|INSERT INTO vendor (name)
  362. VALUES ('$uid')|;
  363. $dbh->do($query) || $form->dberror($query);
  364. $query = qq|SELECT id
  365. FROM vendor
  366. WHERE name = '$uid'|;
  367. ( $form->{id} ) = $dbh->selectrow_array($query);
  368. }
  369. my $employee_id;
  370. ( $null, $employee_id ) = split /--/, $form->{employee};
  371. $employee_id *= 1;
  372. my $pricegroup_id;
  373. ( $null, $pricegroup_id ) = split /--/, $form->{pricegroup};
  374. $pricegroup_id *= 1;
  375. my $business_id;
  376. ( $null, $business_id ) = split /--/, $form->{business};
  377. $business_id *= 1;
  378. my $language_code;
  379. ( $null, $language_code ) = split /--/, $form->{language};
  380. $form->{startdate} = undef unless $form->{startdate};
  381. $form->{enddate} = undef unless $form->{enddate};
  382. $query = qq|
  383. UPDATE vendor
  384. SET vendornumber = ?,
  385. name = ?,
  386. address1 = ?,
  387. address2 = ?,
  388. city = ?,
  389. state = ?,
  390. zipcode = ?,
  391. country = ?,
  392. contact = ?,
  393. phone = ?,
  394. fax = ?,
  395. email = ?,
  396. cc = ?,
  397. bcc = ?,
  398. notes = ?,
  399. discount = ?,
  400. creditlimit = ?,
  401. terms = ?,
  402. taxincluded = ?,
  403. gifi_accno = ?,
  404. business_id = ?,
  405. taxnumber = ?,
  406. sic_code = ?,
  407. iban = ?,
  408. bic = ?,
  409. employee_id = ?,
  410. language_code = ?,
  411. pricegroup_id = ?,
  412. curr = ?,
  413. startdate = ?,
  414. enddate = ?
  415. WHERE id = ?|;
  416. $sth = $dbh->prepare($query);
  417. $sth->execute(
  418. $form->{vendornumber}, $form->{name}, $form->{address1},
  419. $form->{address2}, $form->{city}, $form->{state},
  420. $form->{zipcode}, $form->{country}, $form->{contact},
  421. $form->{phone}, $form->{fax}, $form->{email},
  422. $form->{cc}, $form->{bcc}, $form->{notes},
  423. $form->{discount}, $form->{creditlimit}, $form->{terms},
  424. $form->{taxincluded}, $form->{gifi_accno}, $business_id,
  425. $form->{taxnumber}, $form->{sic_code}, $form->{iban},
  426. $form->{bic}, $employee_id, $language_code,
  427. $pricegroup_id, $form->{curr}, $form->{startdate},
  428. $form->{enddate}, $form->{id}
  429. ) || $form->dberror($query);
  430. # save taxes
  431. foreach $item ( split / /, $form->{taxaccounts} ) {
  432. if ( $form->{"tax_$item"} ) {
  433. $query = qq|
  434. INSERT INTO vendortax (vendor_id, chart_id)
  435. VALUES (?, (SELECT id
  436. FROM chart
  437. WHERE accno = ?))|;
  438. $sth = $dbh->prepare($query);
  439. $sth->execute( $form->{id}, $item )
  440. || $form->dberror($query);
  441. }
  442. }
  443. # add shipto
  444. $form->add_shipto( $dbh, $form->{id} );
  445. $dbh->commit;
  446. }
  447. sub delete {
  448. my ( $self, $myconfig, $form ) = @_;
  449. # connect to database
  450. my $dbh = $form->{dbh};
  451. # delete customer/vendor
  452. my $query = qq|DELETE FROM $form->{db}
  453. WHERE id = ?|;
  454. $sth = $dbh->prepare($query);
  455. $sth->execute( $form->{id} ) || $form->dberror($query);
  456. $dbh->commit;
  457. }
  458. sub search {
  459. my ( $self, $myconfig, $form ) = @_;
  460. # connect to database
  461. my $dbh = $form->{dbh};
  462. my $where = "1 = 1";
  463. $form->{sort} = ( $form->{sort} ) ? $form->{sort} : "name";
  464. my @a = qw(name);
  465. my $sortorder = $form->sort_order( \@a );
  466. my $var;
  467. my $item;
  468. @a = ("$form->{db}number");
  469. push @a, qw(name contact city state zipcode country notes phone email);
  470. if ( $form->{employee} ) {
  471. $var = $dbh->quote($form->like(lc $form->{employee}));
  472. $where .= " AND lower(e.name) LIKE $var";
  473. }
  474. foreach $item (@a) {
  475. if ( $form->{$item} ne "" ) {
  476. $var = $dbh->quote($form->like( lc $form->{$item}) );
  477. $where .= " AND lower(ct.$item) LIKE $var";
  478. }
  479. }
  480. if ( $form->{address} ne "" ) {
  481. $var = $dbh->quote( $form->like( lc $form->{address} ) );
  482. $where .=
  483. " AND (lower(ct.address1) ILIKE $var)";
  484. }
  485. if ( $form->{startdatefrom} ) {
  486. $where .=
  487. " AND ct.startdate >= " . $dbh->quote( $form->{startdatefrom} );
  488. }
  489. if ( $form->{startdateto} ) {
  490. $where .= " AND ct.startdate <= " . $dbh->quote( $form->{startdateto} );
  491. }
  492. if ( $form->{status} eq 'active' ) {
  493. $where .= " AND ct.enddate IS NULL";
  494. }
  495. if ( $form->{status} eq 'inactive' ) {
  496. $where .= " AND ct.enddate <= current_date";
  497. }
  498. if ( $form->{status} eq 'orphaned' ) {
  499. $where .= qq|
  500. AND ct.id NOT IN (SELECT o.$form->{db}_id
  501. FROM oe o, $form->{db} vc
  502. WHERE vc.id = o.$form->{db}_id)|;
  503. if ( $form->{db} eq 'customer' ) {
  504. $where .= qq| AND ct.id NOT IN (SELECT a.customer_id
  505. FROM ar a, customer vc
  506. WHERE vc.id = a.customer_id)|;
  507. }
  508. if ( $form->{db} eq 'vendor' ) {
  509. $where .= qq| AND ct.id NOT IN (SELECT a.vendor_id
  510. FROM ap a, vendor vc
  511. WHERE vc.id = a.vendor_id)|;
  512. }
  513. $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
  514. }
  515. my $query = qq|
  516. SELECT ct.*, b.description AS business,
  517. e.name AS employee, g.pricegroup,
  518. l.description AS language, m.name AS manager
  519. FROM $form->{db} ct
  520. LEFT JOIN business b ON (ct.business_id = b.id)
  521. LEFT JOIN employee e ON (ct.employee_id = e.id)
  522. LEFT JOIN employee m ON (m.id = e.managerid)
  523. LEFT JOIN pricegroup g ON (ct.pricegroup_id = g.id)
  524. LEFT JOIN language l ON (l.code = ct.language_code)
  525. WHERE $where|;
  526. # redo for invoices, orders and quotations
  527. if ( $form->{l_transnumber}
  528. || $form->{l_invnumber}
  529. || $form->{l_ordnumber}
  530. || $form->{l_quonumber} )
  531. {
  532. my ( $ar, $union, $module );
  533. $query = "";
  534. my $transwhere;
  535. my $openarap = "";
  536. my $openoe = "";
  537. if ( $form->{open} || $form->{closed} ) {
  538. unless ( $form->{open} && $form->{closed} ) {
  539. $openarap = " AND a.amount != a.paid"
  540. if $form->{open};
  541. $openarap = " AND a.amount = a.paid"
  542. if $form->{closed};
  543. $openoe = " AND o.closed = '0'"
  544. if $form->{open};
  545. $openoe = " AND o.closed = '1'"
  546. if $form->{closed};
  547. }
  548. }
  549. if ( $form->{l_transnumber} ) {
  550. $ar = ( $form->{db} eq 'customer' ) ? 'ar' : 'ap';
  551. $module = $ar;
  552. $transwhere = "";
  553. $transwhere .=
  554. " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
  555. if $form->{transdatefrom};
  556. $transwhere .=
  557. " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
  558. if $form->{transdateto};
  559. $query = qq|
  560. SELECT ct.*, b.description AS business,
  561. a.invnumber, a.ordnumber,
  562. a.quonumber,
  563. a.id AS invid, '$ar' AS module,
  564. 'invoice' AS formtype,
  565. (a.amount = a.paid) AS closed,
  566. a.amount,
  567. a.netamount, e.name AS employee,
  568. m.name AS manager
  569. FROM $form->{db} ct
  570. JOIN $ar a ON (a.$form->{db}_id = ct.id)
  571. LEFT JOIN business b ON (ct.business_id = b.id)
  572. LEFT JOIN employee e ON (a.employee_id = e.id)
  573. LEFT JOIN employee m ON (m.id = e.managerid)
  574. WHERE $where
  575. AND a.invoice = '0'
  576. $transwhere
  577. $openarap |;
  578. $union = qq| UNION |;
  579. }
  580. if ( $form->{l_invnumber} ) {
  581. $ar = ( $form->{db} eq 'customer' ) ? 'ar' : 'ap';
  582. $module = ( $ar eq 'ar' ) ? 'is' : 'ir';
  583. $transwhere = "";
  584. $transwhere .=
  585. " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
  586. if $form->{transdatefrom};
  587. $transwhere .=
  588. " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
  589. if $form->{transdateto};
  590. $query .= qq|
  591. $union
  592. SELECT ct.*, b.description AS business,
  593. a.invnumber, a.ordnumber, a.quonumber,
  594. a.id AS invid,
  595. '$module' AS module,
  596. 'invoice' AS formtype,
  597. (a.amount = a.paid) AS closed,
  598. a.amount, a.netamount,
  599. e.name AS employee, m.name AS manager
  600. FROM $form->{db} ct
  601. JOIN $ar a ON (a.$form->{db}_id = ct.id)
  602. LEFT JOIN business b ON (ct.business_id = b.id)
  603. LEFT JOIN employee e ON (a.employee_id = e.id)
  604. LEFT JOIN employee m ON (m.id = e.managerid)
  605. WHERE $where
  606. AND a.invoice = '1'
  607. $transwhere
  608. $openarap |;
  609. $union = qq| UNION|;
  610. }
  611. if ( $form->{l_ordnumber} ) {
  612. $transwhere = "";
  613. $transwhere .=
  614. " AND o.transdate >= " . $dbh->quote( $form->{transdatefrom} )
  615. if $form->{transdatefrom};
  616. $transwhere .=
  617. " AND o.transdate <= " . $dbh->quote( $form->{transdateto} )
  618. if $form->{transdateto};
  619. $query .= qq|
  620. $union
  621. SELECT ct.*, b.description AS business,
  622. ' ' AS invnumber, o.ordnumber,
  623. o.quonumber, o.id AS invid,
  624. 'oe' AS module, 'order' AS formtype,
  625. o.closed, o.amount, o.netamount,
  626. e.name AS employee, m.name AS manager
  627. FROM $form->{db} ct
  628. JOIN oe o ON (o.$form->{db}_id = ct.id)
  629. LEFT JOIN business b ON (ct.business_id = b.id)
  630. LEFT JOIN employee e ON (o.employee_id = e.id)
  631. LEFT JOIN employee m ON (m.id = e.managerid)
  632. WHERE $where
  633. AND o.quotation = '0'
  634. $transwhere
  635. $openoe |;
  636. $union = qq| UNION|;
  637. }
  638. if ( $form->{l_quonumber} ) {
  639. $transwhere = "";
  640. $transwhere .=
  641. " AND o.transdate >= " . $dbh->quote( $form->{transdatefrom} )
  642. if $form->{transdatefrom};
  643. $transwhere .=
  644. " AND o.transdate <= " . $dbh->quote( $form->{transdateto} )
  645. if $form->{transdateto};
  646. $query .= qq|
  647. $union
  648. SELECT ct.*, b.description AS business,
  649. ' ' AS invnumber, o.ordnumber,
  650. o.quonumber, o.id AS invid,
  651. 'oe' AS module,
  652. 'quotation' AS formtype,
  653. o.closed, o.amount, o.netamount,
  654. e.name AS employee, m.name AS manager
  655. FROM $form->{db} ct
  656. JOIN oe o ON (o.$form->{db}_id = ct.id)
  657. LEFT JOIN business b ON (ct.business_id = b.id)
  658. LEFT JOIN employee e ON (o.employee_id = e.id)
  659. LEFT JOIN employee m ON (m.id = e.managerid)
  660. WHERE $where
  661. AND o.quotation = '1'
  662. $transwhere
  663. $openoe |;
  664. }
  665. $sortorder .= ", invid";
  666. }
  667. $query .= qq| ORDER BY $sortorder|;
  668. my $sth = $dbh->prepare($query);
  669. $sth->execute || $form->dberror($query);
  670. # accounts
  671. $query = qq|
  672. SELECT c.accno
  673. FROM chart c
  674. JOIN $form->{db}tax t ON (t.chart_id = c.id)
  675. WHERE t.$form->{db}_id = ?|;
  676. my $tth = $dbh->prepare($query);
  677. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  678. $tth->execute( $ref->{id} );
  679. while ( ($item) = $tth->fetchrow_array ) {
  680. $ref->{taxaccount} .= "$item ";
  681. }
  682. $tth->finish;
  683. chop $ref->{taxaccount};
  684. $ref->{address} = "";
  685. for (qw(address1 address2 city state zipcode country)) {
  686. $ref->{address} .= "$ref->{$_} ";
  687. }
  688. push @{ $form->{CT} }, $ref;
  689. }
  690. $sth->finish;
  691. $dbh->commit;
  692. }
  693. sub get_history {
  694. my ( $self, $myconfig, $form ) = @_;
  695. # connect to database
  696. my $dbh = $form->{dbh};
  697. my $query;
  698. my $where = "1 = 1";
  699. $form->{sort} = "partnumber" unless $form->{sort};
  700. my $sortorder = $form->{sort};
  701. my %ordinal = ();
  702. my $var;
  703. my $table;
  704. # setup ASC or DESC
  705. $form->sort_order();
  706. if ( $form->{"$form->{db}number"} ne "" ) {
  707. $var = $dbh->quote( $form->like( lc $form->{"$form->{db}number"} ) );
  708. $where .= " AND lower(ct.$form->{db}number) LIKE $var";
  709. }
  710. if ( $form->{address} ne "" ) {
  711. $var = $dbh->quote( $form->like( lc $form->{address} ) );
  712. $where .= " AND lower(ct.address1) LIKE $var";
  713. }
  714. for (qw(name contact email phone notes city state zipcode country)) {
  715. if ( $form->{$_} ne "" ) {
  716. $var = $dbh->quote( $form->like( lc $form->{$_} ) );
  717. $where .= " AND lower(ct.$_) LIKE $var";
  718. }
  719. }
  720. if ( $form->{employee} ne "" ) {
  721. $var = $dbh->quote($form->like(lc $form->{employee}));
  722. $where .= " AND lower(e.name) LIKE $var";
  723. }
  724. $transwhere .=
  725. " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
  726. if $form->{transdatefrom};
  727. $transwhere .= " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
  728. if $form->{transdateto};
  729. if ( $form->{open} || $form->{closed} ) {
  730. unless ( $form->{open} && $form->{closed} ) {
  731. if ( $form->{type} eq 'invoice' ) {
  732. $where .= " AND a.amount != a.paid"
  733. if $form->{open};
  734. $where .= " AND a.amount = a.paid"
  735. if $form->{closed};
  736. }
  737. else {
  738. $where .= " AND a.closed = '0'"
  739. if $form->{open};
  740. $where .= " AND a.closed = '1'"
  741. if $form->{closed};
  742. }
  743. }
  744. }
  745. my $invnumber = 'invnumber';
  746. my $deldate = 'deliverydate';
  747. my $buysell;
  748. my $sellprice = "sellprice";
  749. if ( $form->{db} eq 'customer' ) {
  750. $buysell = "buy";
  751. if ( $form->{type} eq 'invoice' ) {
  752. $where .= qq|
  753. AND a.invoice = '1' AND i.assemblyitem = '0'|;
  754. $table = 'ar';
  755. $sellprice = "fxsellprice";
  756. }
  757. else {
  758. $table = 'oe';
  759. if ( $form->{type} eq 'order' ) {
  760. $invnumber = 'ordnumber';
  761. $where .= qq| AND a.quotation = '0'|;
  762. }
  763. else {
  764. $invnumber = 'quonumber';
  765. $where .= qq| AND a.quotation = '1'|;
  766. }
  767. $deldate = 'reqdate';
  768. }
  769. }
  770. if ( $form->{db} eq 'vendor' ) {
  771. $buysell = "sell";
  772. if ( $form->{type} eq 'invoice' ) {
  773. $where .= qq| AND a.invoice = '1' AND i.assemblyitem = '0'|;
  774. $table = 'ap';
  775. $sellprice = "fxsellprice";
  776. }
  777. else {
  778. $table = 'oe';
  779. if ( $form->{type} eq 'order' ) {
  780. $invnumber = 'ordnumber';
  781. $where .= qq| AND a.quotation = '0'|;
  782. }
  783. else {
  784. $invnumber = 'quonumber';
  785. $where .= qq| AND a.quotation = '1'|;
  786. }
  787. $deldate = 'reqdate';
  788. }
  789. }
  790. my $invjoin = qq| JOIN invoice i ON (i.trans_id = a.id)|;
  791. if ( $form->{type} eq 'order' ) {
  792. $invjoin = qq| JOIN orderitems i ON (i.trans_id = a.id)|;
  793. }
  794. if ( $form->{type} eq 'quotation' ) {
  795. $invjoin = qq| JOIN orderitems i ON (i.trans_id = a.id)|;
  796. $where .= qq| AND a.quotation = '1'|;
  797. }
  798. %ordinal = (
  799. partnumber => 9,
  800. description => 12,
  801. "$deldate" => 16,
  802. serialnumber => 17,
  803. projectnumber => 18
  804. );
  805. $sortorder =
  806. "2 $form->{direction}, 1, 11, $ordinal{$sortorder} $form->{direction}";
  807. $query = qq|
  808. SELECT ct.id AS ctid, ct.name, ct.address1,
  809. ct.address2, ct.city, ct.state,
  810. p.id AS pid, p.partnumber, a.id AS invid,
  811. a.$invnumber, a.curr, i.description,
  812. i.qty, i.$sellprice AS sellprice, i.discount,
  813. i.$deldate, i.serialnumber, pr.projectnumber,
  814. e.name AS employee, ct.zipcode, ct.country, i.unit,
  815. (SELECT $buysell
  816. FROM exchangerate ex
  817. WHERE a.curr = ex.curr
  818. AND a.transdate = ex.transdate) AS exchangerate
  819. FROM $form->{db} ct
  820. JOIN $table a ON (a.$form->{db}_id = ct.id)
  821. $invjoin
  822. JOIN parts p ON (p.id = i.parts_id)
  823. LEFT JOIN project pr ON (pr.id = i.project_id)
  824. LEFT JOIN employee e ON (e.id = a.employee_id)
  825. WHERE $where
  826. ORDER BY $sortorder|;
  827. my $sth = $dbh->prepare($query);
  828. $sth->execute || $form->dberror($query);
  829. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  830. $ref->{address} = "";
  831. $ref->{exchangerate} ||= 1;
  832. for (qw(address1 address2 city state zipcode country)) {
  833. $ref->{address} .= "$ref->{$_} ";
  834. }
  835. $ref->{id} = $ref->{ctid};
  836. push @{ $form->{CT} }, $ref;
  837. }
  838. $sth->finish;
  839. $dbh->commit;
  840. }
  841. sub pricelist {
  842. my ( $self, $myconfig, $form ) = @_;
  843. # connect to database
  844. my $dbh = $form->{dbh};
  845. my $query;
  846. if ( $form->{db} eq 'customer' ) {
  847. $query = qq|SELECT p.id, p.partnumber, p.description,
  848. p.sellprice, pg.partsgroup, p.partsgroup_id,
  849. m.pricebreak, m.sellprice,
  850. m.validfrom, m.validto, m.curr
  851. FROM partscustomer m
  852. JOIN parts p ON (p.id = m.parts_id)
  853. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  854. WHERE m.customer_id = ?
  855. ORDER BY partnumber|;
  856. }
  857. if ( $form->{db} eq 'vendor' ) {
  858. $query = qq|SELECT p.id, p.partnumber AS sku, p.description,
  859. pg.partsgroup, p.partsgroup_id,
  860. m.partnumber, m.leadtime, m.lastcost, m.curr
  861. FROM partsvendor m
  862. JOIN parts p ON (p.id = m.parts_id)
  863. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  864. WHERE m.vendor_id = ?
  865. ORDER BY p.partnumber|;
  866. }
  867. my $sth;
  868. my $ref;
  869. if ( $form->{id} ) {
  870. $sth = $dbh->prepare($query);
  871. $sth->execute( $form->{id} ) || $form->dberror($query);
  872. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  873. push @{ $form->{all_partspricelist} }, $ref;
  874. }
  875. $sth->finish;
  876. }
  877. $query = qq|SELECT value FROM defaults where setting_key = 'curr'|;
  878. ( $form->{currencies} ) = $dbh->selectrow_array($query);
  879. $query = qq|SELECT id, partsgroup
  880. FROM partsgroup
  881. ORDER BY partsgroup|;
  882. $sth = $dbh->prepare($query);
  883. $sth->execute || $self->dberror($query);
  884. $form->{all_partsgroup} = ();
  885. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  886. push @{ $form->{all_partsgroup} }, $ref;
  887. }
  888. $sth->finish;
  889. $dbh->commit;
  890. }
  891. sub save_pricelist {
  892. my ( $self, $myconfig, $form ) = @_;
  893. my $dbh = $form->{dbh};
  894. my $query = qq|
  895. DELETE FROM parts$form->{db}
  896. WHERE $form->{db}_id = ?|;
  897. $sth = $dbh->prepare($query);
  898. $sth->execute( $form->{id} ) || $form->dberror($query);
  899. foreach $i ( 1 .. $form->{rowcount} ) {
  900. if ( $form->{"id_$i"} ) {
  901. if ( $form->{db} eq 'customer' ) {
  902. $form->{"validfrom_$i"} = undef if $form->{"validfrom_$i"} eq '';
  903. $form->{"validto_$i"} = undef if $form->{"validto_$i"} eq '';
  904. for (qw(pricebreak sellprice)) {
  905. $form->{"${_}_$i"} =
  906. $form->parse_amount( $myconfig, $form->{"${_}_$i"} );
  907. }
  908. $query = qq|
  909. INSERT INTO parts$form->{db}
  910. (parts_id, customer_id,
  911. pricebreak, sellprice,
  912. validfrom, validto, curr)
  913. VALUES (?, ?, ?, ?, ?, ?, ?)|;
  914. @queryargs = (
  915. $form->{"id_$i"}, $form->{id},
  916. $form->{"pricebreak_$i"}, $form->{"sellprice_$i"},
  917. $form->{"validfrom_$i"}, $form->{"validto_$i"},
  918. $form->{"curr_$i"}
  919. );
  920. }
  921. else {
  922. for (qw(leadtime lastcost)) {
  923. $form->{"${_}_$i"} =
  924. $form->parse_amount( $myconfig, $form->{"${_}_$i"} );
  925. }
  926. $query = qq|
  927. INSERT INTO parts$form->{db}
  928. (parts_id, vendor_id,
  929. partnumber, lastcost,
  930. leadtime, curr)
  931. VALUES (?, ?, ?, ?, ?, ?)|;
  932. @queryargs = (
  933. $form->{"id_$i"}, $form->{id},
  934. $form->{"partnumber_$i"}, $form->{"lastcost_$i"},
  935. $form->{"leadtime_$i"}, $form->{"curr_$i"}
  936. );
  937. }
  938. $sth = $dbh->prepare($query);
  939. $sth->execute(@queryargs) || $form->dberror($query);
  940. }
  941. }
  942. $_ = $dbh->commit;
  943. }
  944. sub retrieve_item {
  945. my ( $self, $myconfig, $form ) = @_;
  946. # connect to database
  947. my $dbh = $form->{dbh};
  948. my $i = $form->{rowcount};
  949. my $var;
  950. my $null;
  951. my $where = "WHERE p.obsolete = '0'";
  952. if ( $form->{db} eq 'vendor' ) {
  953. # parts, services, labor
  954. $where .= " AND p.assembly = '0'";
  955. }
  956. if ( $form->{db} eq 'customer' ) {
  957. # parts, assemblies, services
  958. $where .= " AND p.income_accno_id > 0";
  959. }
  960. if ( $form->{"partnumber_$i"} ne "" ) {
  961. $var = $dbh->quote( $form->like( lc $form->{"partnumber_$i"} ) );
  962. $where .= " AND lower(p.partnumber) LIKE $var";
  963. }
  964. if ( $form->{"description_$i"} ne "" ) {
  965. $var = $dbh->quote( $form->like( lc $form->{"description_$i"} ) );
  966. $where .= " AND lower(p.description) LIKE $var";
  967. }
  968. if ( $form->{"partsgroup_$i"} ne "" ) {
  969. ( $null, $var ) = split /--/, $form->{"partsgroup_$i"};
  970. $var = $dbh->quote($var);
  971. $where .= qq| AND p.partsgroup_id = $var|;
  972. }
  973. my $query = qq|
  974. SELECT p.id, p.partnumber, p.description, p.sellprice,
  975. p.lastcost, p.unit, pg.partsgroup, p.partsgroup_id
  976. FROM parts p
  977. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  978. $where
  979. ORDER BY partnumber|;
  980. my $sth = $dbh->prepare($query);
  981. $sth->execute || $form->dberror($query);
  982. my $ref;
  983. $form->{item_list} = ();
  984. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  985. push @{ $form->{item_list} }, $ref;
  986. }
  987. $sth->finish;
  988. $dbh->commit;
  989. }
  990. 1;