summaryrefslogtreecommitdiff
path: root/LedgerSMB/CT.pm
blob: 62cc45ae597784f93309070d2beca573933d4575 (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 (! $sth->fetchrow_array) {
  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 (! $sth->fetchrow_array) {
  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. $form->{startdate} = undef unless $form->{startdate};
  374. $form->{enddate} = undef unless $form->{enddate};
  375. $query = qq|
  376. UPDATE vendor
  377. SET vendornumber = ?,
  378. name = ?,
  379. address1 = ?,
  380. address2 = ?,
  381. city = ?,
  382. state = ?,
  383. zipcode = ?,
  384. country = ?,
  385. contact = ?,
  386. phone = ?,
  387. fax = ?,
  388. email = ?,
  389. cc = ?,
  390. bcc = ?,
  391. notes = ?,
  392. discount = ?,
  393. creditlimit = ?,
  394. terms = ?,
  395. taxincluded = ?,
  396. gifi_accno = ?,
  397. business_id = ?,
  398. taxnumber = ?,
  399. sic_code = ?,
  400. iban = ?,
  401. bic = ?,
  402. employee_id = ?,
  403. language_code = ?,
  404. pricegroup_id = ?,
  405. curr = ?,
  406. startdate = ?,
  407. enddate = ?
  408. WHERE id = ?|;
  409. $sth = $dbh->prepare($query);
  410. $sth->execute(
  411. $form->{vendornumber}, $form->{name}, $form->{address1},
  412. $form->{address2}, $form->{city}, $form->{state},
  413. $form->{zipcode}, $form->{country}, $form->{contact},
  414. $form->{phone}, $form->{fax}, $form->{email}, $form->{cc},
  415. $form->{bcc}, $form->{notes}, $form->{discount},
  416. $form->{creditlimit}, $form->{terms}, $form->{taxincluded},
  417. $form->{gifi_accno}, $business_id, $form->{taxnumber},
  418. $form->{sic_code}, $form->{iban}, $form->{bic}, $employee_id,
  419. $language_code, $pricegroup_id,
  420. $form->{curr}, $form->{startdate}, $form->{enddate},
  421. $form->{id})
  422. || $form->dberror($query);
  423. # save taxes
  424. foreach $item (split / /, $form->{taxaccounts}) {
  425. if ($form->{"tax_$item"}) {
  426. $query = qq|
  427. INSERT INTO vendortax (vendor_id, chart_id)
  428. VALUES (?, (SELECT id
  429. FROM chart
  430. WHERE accno = ?))|;
  431. $sth = $dbh->prepare($query);
  432. $sth->execute($form->{id}, $item)
  433. || $form->dberror($query);
  434. }
  435. }
  436. # add shipto
  437. $form->add_shipto($dbh, $form->{id});
  438. $dbh->commit;
  439. }
  440. sub delete {
  441. my ($self, $myconfig, $form) = @_;
  442. # connect to database
  443. my $dbh = $form->{dbh};
  444. # delete customer/vendor
  445. my $query = qq|DELETE FROM $form->{db}
  446. WHERE id = ?|;
  447. $sth = $dbh->prepare($query);
  448. $sth->execute($form->{id}) || $form->dberror($query);
  449. $dbh->commit;
  450. }
  451. sub search {
  452. my ($self, $myconfig, $form) = @_;
  453. # connect to database
  454. my $dbh = $form->{dbh};
  455. my $where = "1 = 1";
  456. $form->{sort} = ($form->{sort}) ? $form->{sort} : "name";
  457. my @a = qw(name);
  458. my $sortorder = $form->sort_order(\@a);
  459. my $var;
  460. my $item;
  461. @a = ("$form->{db}number");
  462. push @a, qw(name contact city state zipcode country notes phone email);
  463. if ($form->{employee}) {
  464. $var = $form->like(lc $form->{employee});
  465. $where .= " AND lower(e.name) LIKE '$var'";
  466. }
  467. foreach $item (@a) {
  468. if ($form->{$item} ne "") {
  469. $var = $form->like(lc $form->{$item});
  470. $where .= " AND lower(ct.$item) LIKE '$var'";
  471. }
  472. }
  473. if ($form->{address} ne "") {
  474. $var = $dbh->quote($form->like(lc $form->{address}));
  475. $where .= " AND (lower(ct.address1) LIKE $var OR lower(ct.address2) LIKE '$var')";
  476. }
  477. if ($form->{startdatefrom}) {
  478. $where .= " AND ct.startdate >= ".
  479. $dbh->quote($form->{startdatefrom});
  480. }
  481. if ($form->{startdateto}) {
  482. $where .= " AND ct.startdate <= ".
  483. $dbh->quote($form->{startdateto});
  484. }
  485. if ($form->{status} eq 'active') {
  486. $where .= " AND ct.enddate IS NULL";
  487. }
  488. if ($form->{status} eq 'inactive') {
  489. $where .= " AND ct.enddate <= current_date";
  490. }
  491. if ($form->{status} eq 'orphaned') {
  492. $where .= qq|
  493. AND ct.id NOT IN (SELECT o.$form->{db}_id
  494. FROM oe o, $form->{db} vc
  495. WHERE vc.id = o.$form->{db}_id)|;
  496. if ($form->{db} eq 'customer') {
  497. $where .= qq| AND ct.id NOT IN (SELECT a.customer_id
  498. FROM ar a, customer vc
  499. WHERE vc.id = a.customer_id)|;
  500. }
  501. if ($form->{db} eq 'vendor') {
  502. $where .= qq| AND ct.id NOT IN (SELECT a.vendor_id
  503. FROM ap a, vendor vc
  504. WHERE vc.id = a.vendor_id)|;
  505. }
  506. $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
  507. }
  508. my $query = qq|
  509. SELECT ct.*, b.description AS business,
  510. e.name AS employee, g.pricegroup,
  511. l.description AS language, m.name AS manager
  512. FROM $form->{db} ct
  513. LEFT JOIN business b ON (ct.business_id = b.id)
  514. LEFT JOIN employee e ON (ct.employee_id = e.id)
  515. LEFT JOIN employee m ON (m.id = e.managerid)
  516. LEFT JOIN pricegroup g ON (ct.pricegroup_id = g.id)
  517. LEFT JOIN language l ON (l.code = ct.language_code)
  518. WHERE $where|;
  519. # redo for invoices, orders and quotations
  520. if ($form->{l_transnumber}
  521. || $form->{l_invnumber}
  522. || $form->{l_ordnumber}
  523. || $form->{l_quonumber}) {
  524. my ($ar, $union, $module);
  525. $query = "";
  526. my $transwhere;
  527. my $openarap = "";
  528. my $openoe = "";
  529. if ($form->{open} || $form->{closed}) {
  530. unless ($form->{open} && $form->{closed}) {
  531. $openarap = " AND a.amount != a.paid"
  532. if $form->{open};
  533. $openarap = " AND a.amount = a.paid"
  534. if $form->{closed};
  535. $openoe = " AND o.closed = '0'"
  536. if $form->{open};
  537. $openoe = " AND o.closed = '1'"
  538. if $form->{closed};
  539. }
  540. }
  541. if ($form->{l_transnumber}) {
  542. $ar = ($form->{db} eq 'customer') ? 'ar' : 'ap';
  543. $module = $ar;
  544. $transwhere = "";
  545. $transwhere .= " AND a.transdate >= "
  546. .$dbh->quote($form->{transdatefrom})
  547. if $form->{transdatefrom};
  548. $transwhere .= " AND a.transdate <= ".
  549. $dbh->quote($form->{transdateto})
  550. if $form->{transdateto};
  551. $query = qq|
  552. SELECT ct.*, b.description AS business,
  553. a.invnumber, a.ordnumber,
  554. a.quonumber,
  555. a.id AS invid, '$ar' AS module,
  556. 'invoice' AS formtype,
  557. (a.amount = a.paid) AS closed,
  558. a.amount,
  559. a.netamount, e.name AS employee,
  560. m.name AS manager
  561. FROM $form->{db} ct
  562. JOIN $ar a ON (a.$form->{db}_id = ct.id)
  563. LEFT JOIN business b ON (ct.business_id = b.id)
  564. LEFT JOIN employee e ON (a.employee_id = e.id)
  565. LEFT JOIN employee m ON (m.id = e.managerid)
  566. WHERE $where
  567. AND a.invoice = '0'
  568. $transwhere
  569. $openarap |;
  570. $union = qq| UNION |;
  571. }
  572. if ($form->{l_invnumber}) {
  573. $ar = ($form->{db} eq 'customer') ? 'ar' : 'ap';
  574. $module = ($ar eq 'ar') ? 'is' : 'ir';
  575. $transwhere = "";
  576. $transwhere .= " AND a.transdate >= ".
  577. $dbh->quote($form->{transdatefrom})
  578. if $form->{transdatefrom};
  579. $transwhere .= " AND a.transdate <= ".
  580. $dbh->quote($form->{transdateto})
  581. if $form->{transdateto};
  582. $query .= qq|
  583. $union
  584. SELECT ct.*, b.description AS business,
  585. a.invnumber, a.ordnumber, a.quonumber,
  586. a.id AS invid,
  587. '$module' AS module,
  588. 'invoice' AS formtype,
  589. (a.amount = a.paid) AS closed,
  590. a.amount, a.netamount,
  591. e.name AS employee, m.name AS manager
  592. FROM $form->{db} ct
  593. JOIN $ar a ON (a.$form->{db}_id = ct.id)
  594. LEFT JOIN business b ON (ct.business_id = b.id)
  595. LEFT JOIN employee e ON (a.employee_id = e.id)
  596. LEFT JOIN employee m ON (m.id = e.managerid)
  597. WHERE $where
  598. AND a.invoice = '1'
  599. $transwhere
  600. $openarap |;
  601. $union = qq| UNION|;
  602. }
  603. if ($form->{l_ordnumber}) {
  604. $transwhere = "";
  605. $transwhere .= " AND o.transdate >= ".
  606. $dbh->quote($form->{transdatefrom})
  607. if $form->{transdatefrom};
  608. $transwhere .= " AND o.transdate <= ".
  609. $dbh->quote($form->{transdateto})
  610. if $form->{transdateto};
  611. $query .= qq|
  612. $union
  613. SELECT ct.*, b.description AS business,
  614. ' ' AS invnumber, o.ordnumber,
  615. o.quonumber, o.id AS invid,
  616. 'oe' AS module, 'order' AS formtype,
  617. o.closed, o.amount, o.netamount,
  618. e.name AS employee, m.name AS manager
  619. FROM $form->{db} ct
  620. JOIN oe o ON (o.$form->{db}_id = ct.id)
  621. LEFT JOIN business b ON (ct.business_id = b.id)
  622. LEFT JOIN employee e ON (o.employee_id = e.id)
  623. LEFT JOIN employee m ON (m.id = e.managerid)
  624. WHERE $where
  625. AND o.quotation = '0'
  626. $transwhere
  627. $openoe |;
  628. $union = qq| UNION|;
  629. }
  630. if ($form->{l_quonumber}) {
  631. $transwhere = "";
  632. $transwhere .= " AND o.transdate >= ".
  633. $dbh->quote($form->{transdatefrom})
  634. if $form->{transdatefrom};
  635. $transwhere .= " AND o.transdate <= ".
  636. $dbh->quote($form->{transdateto})
  637. if $form->{transdateto};
  638. $query .= qq|
  639. $union
  640. SELECT ct.*, b.description AS business,
  641. ' ' AS invnumber, o.ordnumber,
  642. o.quonumber, o.id AS invid,
  643. 'oe' AS module,
  644. 'quotation' AS formtype,
  645. o.closed, o.amount, o.netamount,
  646. e.name AS employee, m.name AS manager
  647. FROM $form->{db} ct
  648. JOIN oe o ON (o.$form->{db}_id = ct.id)
  649. LEFT JOIN business b ON (ct.business_id = b.id)
  650. LEFT JOIN employee e ON (o.employee_id = e.id)
  651. LEFT JOIN employee m ON (m.id = e.managerid)
  652. WHERE $where
  653. AND o.quotation = '1'
  654. $transwhere
  655. $openoe |;
  656. }
  657. $sortorder .= ", invid";
  658. }
  659. $query .= qq| ORDER BY $sortorder|;
  660. my $sth = $dbh->prepare($query);
  661. $sth->execute || $form->dberror($query);
  662. # accounts
  663. $query = qq|
  664. SELECT c.accno
  665. FROM chart c
  666. JOIN $form->{db}tax t ON (t.chart_id = c.id)
  667. WHERE t.$form->{db}_id = ?|;
  668. my $tth = $dbh->prepare($query);
  669. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  670. $tth->execute($ref->{id});
  671. while (($item) = $tth->fetchrow_array) {
  672. $ref->{taxaccount} .= "$item ";
  673. }
  674. $tth->finish;
  675. chop $ref->{taxaccount};
  676. $ref->{address} = "";
  677. for (qw(address1 address2 city state zipcode country)) {
  678. $ref->{address} .= "$ref->{$_} ";
  679. }
  680. push @{ $form->{CT} }, $ref;
  681. }
  682. $sth->finish;
  683. $dbh->commit;
  684. }
  685. sub get_history {
  686. my ($self, $myconfig, $form) = @_;
  687. # connect to database
  688. my $dbh = $form->{dbh};
  689. my $query;
  690. my $where = "1 = 1";
  691. $form->{sort} = "partnumber" unless $form->{sort};
  692. my $sortorder = $form->{sort};
  693. my %ordinal = ();
  694. my $var;
  695. my $table;
  696. # setup ASC or DESC
  697. $form->sort_order();
  698. if ($form->{"$form->{db}number"} ne "") {
  699. $var = $dbh->($form->like(lc $form->{"$form->{db}number"}));
  700. $where .= " AND lower(ct.$form->{db}number) LIKE $var";
  701. }
  702. if ($form->{address} ne "") {
  703. $var = $dbh->quote($form->like(lc $form->{address}));
  704. $where .= " AND lower(ct.address1) LIKE $var";
  705. }
  706. for (qw(name contact email phone notes city state zipcode country)) {
  707. if ($form->{$_} ne "") {
  708. $var = $dbh->quote($form->like(lc $form->{$_}));
  709. $where .= " AND lower(ct.$_) LIKE $var";
  710. }
  711. }
  712. if ($form->{employee} ne "") {
  713. $var = $form->like(lc $form->{employee});
  714. $where .= " AND lower(e.name) LIKE '$var'";
  715. }
  716. $transwhere .= " AND a.transdate >= ".
  717. $dbh->quote($form->{transdatefrom})
  718. if $form->{transdatefrom};
  719. $transwhere .= " AND a.transdate <= ".
  720. $dbh->quote($form->{transdateto})
  721. if $form->{transdateto};
  722. if ($form->{open} || $form->{closed}) {
  723. unless ($form->{open} && $form->{closed}) {
  724. if ($form->{type} eq 'invoice') {
  725. $where .= " AND a.amount != a.paid"
  726. if $form->{open};
  727. $where .= " AND a.amount = a.paid"
  728. if $form->{closed};
  729. } else {
  730. $where .= " AND a.closed = '0'"
  731. if $form->{open};
  732. $where .= " AND a.closed = '1'"
  733. if $form->{closed};
  734. }
  735. }
  736. }
  737. my $invnumber = 'invnumber';
  738. my $deldate = 'deliverydate';
  739. my $buysell;
  740. my $sellprice = "sellprice";
  741. if ($form->{db} eq 'customer') {
  742. $buysell = "buy";
  743. if ($form->{type} eq 'invoice') {
  744. $where .= qq|
  745. AND a.invoice = '1' AND i.assemblyitem = '0'|;
  746. $table = 'ar';
  747. $sellprice = "fxsellprice";
  748. } else {
  749. $table = 'oe';
  750. if ($form->{type} eq 'order') {
  751. $invnumber = 'ordnumber';
  752. $where .= qq| AND a.quotation = '0'|;
  753. } else {
  754. $invnumber = 'quonumber';
  755. $where .= qq| AND a.quotation = '1'|;
  756. }
  757. $deldate = 'reqdate';
  758. }
  759. }
  760. if ($form->{db} eq 'vendor') {
  761. $buysell = "sell";
  762. if ($form->{type} eq 'invoice') {
  763. $where .= qq| AND a.invoice = '1' AND i.assemblyitem = '0'|;
  764. $table = 'ap';
  765. $sellprice = "fxsellprice";
  766. } else {
  767. $table = 'oe';
  768. if ($form->{type} eq 'order') {
  769. $invnumber = 'ordnumber';
  770. $where .= qq| AND a.quotation = '0'|;
  771. } else {
  772. $invnumber = 'quonumber';
  773. $where .= qq| AND a.quotation = '1'|;
  774. }
  775. $deldate = 'reqdate';
  776. }
  777. }
  778. my $invjoin = qq| JOIN invoice i ON (i.trans_id = a.id)|;
  779. if ($form->{type} eq 'order') {
  780. $invjoin = qq| JOIN orderitems i ON (i.trans_id = a.id)|;
  781. }
  782. if ($form->{type} eq 'quotation') {
  783. $invjoin = qq| JOIN orderitems i ON (i.trans_id = a.id)|;
  784. $where .= qq| AND a.quotation = '1'|;
  785. }
  786. %ordinal = ( partnumber => 9,
  787. description => 12,
  788. "$deldate" => 16,
  789. serialnumber => 17,
  790. projectnumber => 18 );
  791. $sortorder = "2 $form->{direction}, 1, 11, $ordinal{$sortorder} $form->{direction}";
  792. $query = qq|
  793. SELECT ct.id AS ctid, ct.name, ct.address1,
  794. ct.address2, ct.city, ct.state,
  795. p.id AS pid, p.partnumber, a.id AS invid,
  796. a.$invnumber, a.curr, i.description,
  797. i.qty, i.$sellprice AS sellprice, i.discount,
  798. i.$deldate, i.serialnumber, pr.projectnumber,
  799. e.name AS employee, ct.zipcode, ct.country, i.unit,
  800. (SELECT $buysell
  801. FROM exchangerate ex
  802. WHERE a.curr = ex.curr
  803. AND a.transdate = ex.transdate) AS exchangerate
  804. FROM $form->{db} ct
  805. JOIN $table a ON (a.$form->{db}_id = ct.id)
  806. $invjoin
  807. JOIN parts p ON (p.id = i.parts_id)
  808. LEFT JOIN project pr ON (pr.id = i.project_id)
  809. LEFT JOIN employee e ON (e.id = a.employee_id)
  810. WHERE $where
  811. ORDER BY $sortorder|;
  812. my $sth = $dbh->prepare($query);
  813. $sth->execute || $form->dberror($query);
  814. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  815. $ref->{address} = "";
  816. $ref->{exchangerate} ||= 1;
  817. for (qw(address1 address2 city state zipcode country)) { $ref->{address} .= "$ref->{$_} " }
  818. $ref->{id} = $ref->{ctid};
  819. push @{ $form->{CT} }, $ref;
  820. }
  821. $sth->finish;
  822. $dbh->commit;
  823. }
  824. sub pricelist {
  825. my ($self, $myconfig, $form) = @_;
  826. # connect to database
  827. my $dbh = $form->{dbh};
  828. my $query;
  829. if ($form->{db} eq 'customer') {
  830. $query = qq|SELECT p.id, p.partnumber, p.description,
  831. p.sellprice, pg.partsgroup, p.partsgroup_id,
  832. m.pricebreak, m.sellprice,
  833. m.validfrom, m.validto, m.curr
  834. FROM partscustomer m
  835. JOIN parts p ON (p.id = m.parts_id)
  836. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  837. WHERE m.customer_id = ?
  838. ORDER BY partnumber|;
  839. }
  840. if ($form->{db} eq 'vendor') {
  841. $query = qq|SELECT p.id, p.partnumber AS sku, p.description,
  842. pg.partsgroup, p.partsgroup_id,
  843. m.partnumber, m.leadtime, m.lastcost, m.curr
  844. FROM partsvendor m
  845. JOIN parts p ON (p.id = m.parts_id)
  846. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  847. WHERE m.vendor_id = ?
  848. ORDER BY p.partnumber|;
  849. }
  850. my $sth;
  851. my $ref;
  852. if ($form->{id}) {
  853. $sth = $dbh->prepare($query);
  854. $sth->execute($form->{id}) || $form->dberror($query);
  855. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  856. push @{ $form->{all_partspricelist} }, $ref;
  857. }
  858. $sth->finish;
  859. }
  860. $query = qq|SELECT value FROM defaults where setting_key = 'curr'|;
  861. ($form->{currencies}) = $dbh->selectrow_array($query);
  862. $query = qq|SELECT id, partsgroup
  863. FROM partsgroup
  864. ORDER BY partsgroup|;
  865. $sth = $dbh->prepare($query);
  866. $sth->execute || $self->dberror($query);
  867. $form->{all_partsgroup} = ();
  868. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  869. push @{ $form->{all_partsgroup} }, $ref;
  870. }
  871. $sth->finish;
  872. $dbh->connect;
  873. }
  874. sub save_pricelist {
  875. my ($self, $myconfig, $form) = @_;
  876. my $dbh = $form->{dbh};
  877. my $query = qq|
  878. DELETE FROM parts$form->{db}
  879. WHERE $form->{db}_id = ?}|;
  880. $sth = $dbh->prepare($query);
  881. $sth->execute($form->{id}) || $form->dberror($query);
  882. foreach $i (1 .. $form->{rowcount}) {
  883. if ($form->{"id_$i"}) {
  884. if ($form->{db} eq 'customer') {
  885. for (qw(pricebreak sellprice)) {
  886. $form->{"${_}_$i"} =
  887. $form->parse_amount(
  888. $myconfig,
  889. $form->{"${_}_$i"});
  890. }
  891. $query = qq|
  892. INSERT INTO parts$form->{db}
  893. (parts_id, customer_id,
  894. pricebreak, sellprice,
  895. validfrom, validto, curr)
  896. VALUES (?, ?, ?, ?, ?, ?, ?)|;
  897. @queryargs = ($form->{"id_$i"}, $form->{id},
  898. $form->{"pricebreak_$i"},
  899. $form->{"sellprice_$i"},
  900. $form->{"validfrom_$i"},
  901. $form->{"validto_$i"},
  902. $form->{"curr_$i"});
  903. } else {
  904. for (qw(leadtime lastcost)) {
  905. $form->{"${_}_$i"} =
  906. $form->parse_amount(
  907. $myconfig,
  908. $form->{"${_}_$i"})
  909. }
  910. $query = qq|
  911. INSERT INTO parts$form->{db}
  912. (parts_id, vendor_id,
  913. partnumber, lastcost,
  914. leadtime, curr)
  915. VALUES (?, ?, ?, ?, ?, ?)|;
  916. @queryargs = ($form->{"id_$i"}, $form->{id},
  917. $form->{"partnumber_$i"},
  918. $form->{"lastcost_$i"},
  919. $form->{"leadtime_$i"},
  920. $form->{"curr_$i"});
  921. }
  922. $sth = $dbh->prepare($query);
  923. $sth->execute(@queryargs) || $form->dberror($query);
  924. }
  925. }
  926. $_ = $dbh->commit;
  927. }
  928. sub retrieve_item {
  929. my ($self, $myconfig, $form) = @_;
  930. # connect to database
  931. my $dbh = $form->{dbh};
  932. my $i = $form->{rowcount};
  933. my $var;
  934. my $null;
  935. my $where = "WHERE p.obsolete = '0'";
  936. if ($form->{db} eq 'vendor') {
  937. # parts, services, labor
  938. $where .= " AND p.assembly = '0'";
  939. }
  940. if ($form->{db} eq 'customer') {
  941. # parts, assemblies, services
  942. $where .= " AND p.income_accno_id > 0";
  943. }
  944. if ($form->{"partnumber_$i"} ne "") {
  945. $var = $dbh->quote($form->like(lc $form->{"partnumber_$i"}));
  946. $where .= " AND lower(p.partnumber) LIKE $var";
  947. }
  948. if ($form->{"description_$i"} ne "") {
  949. $var = $dbh->quote($form->like(lc $form->{"description_$i"}));
  950. $where .= " AND lower(p.description) LIKE $var";
  951. }
  952. if ($form->{"partsgroup_$i"} ne "") {
  953. ($null, $var) = split /--/, $form->{"partsgroup_$i"};
  954. $var = $dbh->quote($var);
  955. $where .= qq| AND p.partsgroup_id = $var|;
  956. }
  957. my $query = qq|
  958. SELECT p.id, p.partnumber, p.description, p.sellprice,
  959. p.lastcost, p.unit, pg.partsgroup, p.partsgroup_id
  960. FROM parts p
  961. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  962. $where
  963. ORDER BY partnumber|;
  964. my $sth = $dbh->prepare($query);
  965. $sth->execute || $form->dberror($query);
  966. my $ref;
  967. $form->{item_list} = ();
  968. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  969. push @{ $form->{item_list} }, $ref;
  970. }
  971. $sth->finish;
  972. $dbh->commit;
  973. }
  974. 1;