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