summaryrefslogtreecommitdiff
path: root/LedgerSMB/AM.pm
blob: c0f574e50b60a887f5d6498c6deaba9b34de8032 (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: Jim Rawlings <jim@your-dba.com>
  23. #
  24. #======================================================================
  25. #
  26. # This file has undergone whitespace cleanup.
  27. #
  28. #======================================================================
  29. #
  30. # Administration module
  31. # Chart of Accounts
  32. # template routines
  33. # preferences
  34. #
  35. #======================================================================
  36. package AM;
  37. use LedgerSMB::Tax;
  38. use LedgerSMB::Sysconfig;
  39. sub get_account {
  40. my ($self, $myconfig, $form) = @_;
  41. my $dbh = $form->{dbh};
  42. my $query = qq|
  43. SELECT accno, description, charttype, gifi_accno,
  44. category, link, contra
  45. FROM chart
  46. WHERE id = ?|;
  47. my $sth = $dbh->prepare($query);
  48. $sth->execute($form->{id}) || $form->dberror($query);
  49. my $ref = $sth->fetchrow_hashref(NAME_lc);
  50. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  51. $sth->finish;
  52. # get default accounts
  53. $query = qq|
  54. SELECT (SELECT value FROM defaults
  55. WHERE setting_key = 'inventory_accno_id')
  56. AS inventory_accno_id,
  57. (SELECT value FROM defaults
  58. WHERE setting_key = 'income_accno_id')
  59. AS income_accno_id,
  60. (SELECT value FROM defaults
  61. WHERE setting_key = 'expense_accno_id')
  62. AS expense_accno_id,
  63. (SELECT value FROM defaults
  64. WHERE setting_key = 'fxgain_accno_id')
  65. AS fxgain_accno_id,
  66. (SELECT value FROM defaults
  67. WHERE setting_key = 'fxloss_accno_id')
  68. AS fxloss_accno_id|;
  69. $sth = $dbh->prepare($query);
  70. $sth->execute || $form->dberror($query);
  71. $ref = $sth->fetchrow_hashref(NAME_lc);
  72. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  73. $sth->finish;
  74. # check if we have any transactions
  75. $query = qq|
  76. SELECT trans_id
  77. FROM acc_trans
  78. WHERE chart_id = ?
  79. LIMIT 1|;
  80. $sth = $dbh->prepare($query);
  81. $sth->execute($form->{id});
  82. ($form->{orphaned}) = $sth->fetchrow_array();
  83. $form->{orphaned} = !$form->{orphaned};
  84. $dbh->commit;
  85. }
  86. sub save_account {
  87. my ($self, $myconfig, $form) = @_;
  88. # connect to database, turn off AutoCommit
  89. my $dbh = $form->{dbh};
  90. $form->{link} = "";
  91. foreach my $item ($form->{AR},
  92. $form->{AR_amount},
  93. $form->{AR_tax},
  94. $form->{AR_paid},
  95. $form->{AP},
  96. $form->{AP_amount},
  97. $form->{AP_tax},
  98. $form->{AP_paid},
  99. $form->{IC},
  100. $form->{IC_income},
  101. $form->{IC_sale},
  102. $form->{IC_expense},
  103. $form->{IC_cogs},
  104. $form->{IC_taxpart},
  105. $form->{IC_taxservice}) {
  106. $form->{link} .= "${item}:" if ($item);
  107. }
  108. chop $form->{link};
  109. # strip blanks from accno
  110. for (qw(accno gifi_accno)) { $form->{$_} =~ s/( |')//g }
  111. foreach my $item (qw(accno gifi_accno description)) {
  112. $form->{$item} =~ s/-(-+)/-/g;
  113. $form->{$item} =~ s/ ( )+/ /g;
  114. }
  115. my $query;
  116. my $sth;
  117. $form->{contra} *= 1;
  118. my @queryargs;
  119. @queryargs = ($form->{accno}, $form->{description},
  120. $form->{charttype}, $form->{gifi_accno},
  121. $form->{category}, $form->{"link"},
  122. $form->{contra});
  123. # if we have an id then replace the old record
  124. if ($form->{id}) {
  125. $query = qq|
  126. UPDATE chart SET accno = ?,
  127. description = ?,
  128. charttype = ?,
  129. gifi_accno = ?,
  130. category = ?,
  131. link = ?,
  132. contra = ?
  133. WHERE id = ?|;
  134. push @queryargs, $form->{id};
  135. } else {
  136. $query = qq|
  137. INSERT INTO chart
  138. (accno, description, charttype,
  139. gifi_accno, category, link, contra)
  140. VALUES (?, ?, ?, ?, ?, ?, ?)|;
  141. }
  142. $sth = $dbh->prepare($query);
  143. $sth->execute(@queryargs) || $form->dberror($query);
  144. $sth->finish;
  145. $chart_id = $dbh->quote($form->{id});
  146. if (! $form->{id}) {
  147. # get id from chart
  148. $query = qq|
  149. SELECT id
  150. FROM chart
  151. WHERE accno = ?|;
  152. $sth = $dbh->prepare($query);
  153. $sth->execute($form->{accno});
  154. ($chart_id) = $sth->fetchrow_array();
  155. $sth->finish;
  156. }
  157. if ($form->{IC_taxpart} || $form->{IC_taxservice} || $form->{AR_tax} || $form->{AP_tax}) {
  158. # add account if it doesn't exist in tax
  159. $query = qq|SELECT chart_id
  160. FROM tax
  161. WHERE chart_id = $chart_id|;
  162. my ($tax_id) = $dbh->selectrow_array($query);
  163. # add tax if it doesn't exist
  164. unless ($tax_id) {
  165. $query = qq|INSERT INTO tax (chart_id, rate)
  166. VALUES ($chart_id, 0)|;
  167. $dbh->do($query) || $form->dberror($query);
  168. }
  169. } else {
  170. # remove tax
  171. if ($form->{id}) {
  172. $query = qq|DELETE FROM tax
  173. WHERE chart_id = $form->{id}|;
  174. $dbh->do($query) || $form->dberror($query);
  175. }
  176. }
  177. # commit
  178. my $rc = $dbh->commit;
  179. $rc;
  180. }
  181. sub delete_account {
  182. my ($self, $myconfig, $form) = @_;
  183. # connect to database, turn off AutoCommit
  184. my $dbh = $form->{dbh};
  185. my $sth;
  186. my $query = qq|
  187. SELECT count(*)
  188. FROM acc_trans
  189. WHERE chart_id = ?|;
  190. $sth = $dbh->prepare($query);
  191. $sth->execute($form->{id});
  192. my ($rowcount) = $sth->fetchrow_array();
  193. if ($rowcount) {
  194. $form->error(
  195. "Cannot delete accounts with associated transactions!"
  196. );
  197. }
  198. # delete chart of account record
  199. $query = qq|
  200. DELETE FROM chart
  201. WHERE id = ?|;
  202. $sth = $dbh->prepare($query);
  203. $sth->execute($form->{id}) || $form->dberror($query);
  204. # set inventory_accno_id, income_accno_id, expense_accno_id to defaults
  205. $query = qq|
  206. UPDATE parts
  207. SET inventory_accno_id = (SELECT value::int
  208. FROM defaults
  209. WHERE setting_key =
  210. 'inventory_accno_id')
  211. WHERE inventory_accno_id = ?|;
  212. $sth = $dbh->prepare($query);
  213. $sth->execute($form->{id}) || $form->dberror($query);
  214. for (qw(income_accno_id expense_accno_id)){
  215. $query = qq|
  216. UPDATE parts
  217. SET $_ = (SELECT value::int
  218. FROM defaults
  219. WHERE setting_key = '$_')
  220. WHERE $_ = ?|;
  221. $sth = $dbh->prepare($query);
  222. $sth->execute($form->{id}) || $form->dberror($query);
  223. $sth->finish;
  224. }
  225. foreach my $table (qw(partstax customertax vendortax tax)) {
  226. $query = qq|
  227. DELETE FROM $table
  228. WHERE chart_id = ?|;
  229. $sth = $dbh->prepare($query);
  230. $sth->execute($form->{id}) || $form->dberror($query);
  231. $sth->finish;
  232. }
  233. # commit and redirect
  234. my $rc = $dbh->commit;
  235. $rc;
  236. }
  237. sub gifi_accounts {
  238. my ($self, $myconfig, $form) = @_;
  239. # connect to database
  240. my $dbh = $form->{dbh};
  241. my $query = qq|
  242. SELECT accno, description
  243. FROM gifi
  244. ORDER BY accno|;
  245. $sth = $dbh->prepare($query);
  246. $sth->execute || $form->dberror($query);
  247. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  248. push @{ $form->{ALL} }, $ref;
  249. }
  250. $sth->finish;
  251. $dbh->commit;
  252. }
  253. sub get_gifi {
  254. my ($self, $myconfig, $form) = @_;
  255. # connect to database
  256. my $dbh = $form->{dbh};
  257. my $sth;
  258. my $query = qq|
  259. SELECT accno, description
  260. FROM gifi
  261. WHERE accno = ?|;
  262. $sth = $dbh->prepare($query);
  263. $sth->execute($form->{accno}) || $form->dberror($query);
  264. ($form->{accno}, $form->{description}) = $sth->fetchrow_array();
  265. $sth->finish;
  266. # check for transactions
  267. $query = qq|
  268. SELECT count(*)
  269. FROM acc_trans a
  270. JOIN chart c ON (a.chart_id = c.id)
  271. JOIN gifi g ON (c.gifi_accno = g.accno)
  272. WHERE g.accno = ?|;
  273. $sth = $dbh->prepare($query);
  274. $sth->execute($form->{accno}) || $form->dberror($query);
  275. ($numrows) = $sth->fetchrow_array;
  276. if (($numrows * 1) == 0){
  277. $form->{orphaned} = 1;
  278. } else {
  279. $form->{orphaned} = 0;
  280. }
  281. $dbh->commit;
  282. }
  283. sub save_gifi {
  284. my ($self, $myconfig, $form) = @_;
  285. my $dbh = $form->{dbh};
  286. $form->{accno} =~ s/( |')//g;
  287. foreach my $item (qw(accno description)) {
  288. $form->{$item} =~ s/-(-+)/-/g;
  289. $form->{$item} =~ s/ ( )+/ /g;
  290. }
  291. my @queryargs = ($form->{accno}, $form->{description});
  292. # id is the old account number!
  293. if ($form->{id}) {
  294. $query = qq|
  295. UPDATE gifi
  296. SET accno = ?,
  297. description = ?
  298. WHERE accno = ?|;
  299. push @queryargs, $form->{id};
  300. } else {
  301. $query = qq|
  302. INSERT INTO gifi (accno, description)
  303. VALUES (?, ?)|;
  304. }
  305. $sth = $dbh->prepare($query);
  306. $sth->execute(@queryargs) || $form->dberror;
  307. $sth->finish;
  308. $dbh->commit;
  309. }
  310. sub delete_gifi {
  311. my ($self, $myconfig, $form) = @_;
  312. # connect to database
  313. my $dbh = $form->{dbh};
  314. # id is the old account number!
  315. $query = qq|
  316. DELETE FROM gifi
  317. WHERE accno = ?|;
  318. $sth = $dbh->prepare($query);
  319. $sth->execute($form->{id}) || $form->dberror($query);
  320. $sth->finish;
  321. $dbh->commit;
  322. }
  323. sub warehouses {
  324. my ($self, $myconfig, $form) = @_;
  325. # connect to database
  326. my $dbh = $form->{dbh};
  327. $form->sort_order();
  328. my $query = qq|
  329. SELECT id, description
  330. FROM warehouse
  331. ORDER BY description $form->{direction}|;
  332. $sth = $dbh->prepare($query);
  333. $sth->execute || $form->dberror($query);
  334. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  335. push @{ $form->{ALL} }, $ref;
  336. }
  337. $sth->finish;
  338. $dbh->commit;
  339. }
  340. sub get_warehouse {
  341. my ($self, $myconfig, $form) = @_;
  342. # connect to database
  343. my $dbh = $form->{dbh};
  344. my $sth;
  345. my $query = qq|
  346. SELECT description
  347. FROM warehouse
  348. WHERE id = ?|;
  349. $sth = $dbh->prepare($query);
  350. $sth->execute($form->{id}) || $form->dberror($query);
  351. ($form->{description}) = $sth->fetchrow_array;
  352. $sth->finish;
  353. # see if it is in use
  354. $query = qq|
  355. SELECT count(*)
  356. FROM inventory
  357. WHERE warehouse_id = ?|;
  358. $sth = $dbh->prepare($query);
  359. $sth->execute($form->{id});
  360. ($form->{orphaned}) = $sth->fetchrow_array;
  361. if (($form->{orphaned} * 1) == 0){
  362. $form->{orphaned} = 1;
  363. } else {
  364. $form->{orphaned} = 0;
  365. }
  366. $dbh->commit;
  367. }
  368. sub save_warehouse {
  369. my ($self, $myconfig, $form) = @_;
  370. # connect to database
  371. my $dbh = $form->{dbh};
  372. my $sth;
  373. my @queryargs = ($form->{description});
  374. $form->{description} =~ s/-(-)+/-/g;
  375. $form->{description} =~ s/ ( )+/ /g;
  376. if ($form->{id}) {
  377. $query = qq|
  378. UPDATE warehouse
  379. SET description = ?
  380. WHERE id = ?|;
  381. push @queryargs, $form->{id};
  382. } else {
  383. $query = qq|
  384. INSERT INTO warehouse (description)
  385. VALUES (?)|;
  386. }
  387. $sth = $dbh->prepare($query);
  388. $sth->execute(@queryargs) || $form->dberror($query);
  389. $sth->finish;
  390. $dbh->commit;
  391. }
  392. sub delete_warehouse {
  393. my ($self, $myconfig, $form) = @_;
  394. # connect to database
  395. my $dbh = $form->{dbh};
  396. $query = qq|
  397. DELETE FROM warehouse
  398. WHERE id = ?|;
  399. $dbh->prepare($query)->execute($form->{id}) || $form->dberror($query);
  400. $dbh->commit;
  401. }
  402. sub departments {
  403. my ($self, $myconfig, $form) = @_;
  404. # connect to database
  405. my $dbh = $form->{dbh};
  406. $form->sort_order();
  407. my $query = qq|SELECT id, description, role
  408. FROM department
  409. ORDER BY description $form->{direction}|;
  410. $sth = $dbh->prepare($query);
  411. $sth->execute || $form->dberror($query);
  412. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  413. push @{ $form->{ALL} }, $ref;
  414. }
  415. $sth->finish;
  416. $dbh->commit;
  417. }
  418. sub get_department {
  419. my ($self, $myconfig, $form) = @_;
  420. # connect to database
  421. my $dbh = $form->{dbh};
  422. my $sth;
  423. my $query = qq|
  424. SELECT description, role
  425. FROM department
  426. WHERE id = ?|;
  427. $sth = $dbh->prepare($query);
  428. $sth->execute($form->{id});
  429. ($form->{description}, $form->{role}) = $sth->fetchrow_array;
  430. $sth->finish;
  431. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  432. # see if it is in use
  433. $query = qq|
  434. SELECT count(*)
  435. FROM dpt_trans
  436. WHERE department_id = ? |;
  437. $sth = $dbh->prepare($query);
  438. $sth->execute($form->{id});
  439. ($form->{orphaned}) = $sth->fetchrow_array;
  440. if (($form->{orphaned} * 1) == 0){
  441. $form->{orphaned} = 1;
  442. } else {
  443. $form->{orphaned} = 0;
  444. }
  445. $dbh->commit;
  446. }
  447. sub save_department {
  448. my ($self, $myconfig, $form) = @_;
  449. # connect to database
  450. my $dbh = $form->{dbh};
  451. $form->{description} =~ s/-(-)+/-/g;
  452. $form->{description} =~ s/ ( )+/ /g;
  453. my $sth;
  454. my @queryargs = ($form->{description}, $form->{role});
  455. if ($form->{id}) {
  456. $query = qq|
  457. UPDATE department
  458. SET description = ?,
  459. role = ?
  460. WHERE id = ?|;
  461. push @queryargs, $form->{id};
  462. } else {
  463. $query = qq|
  464. INSERT INTO department (description, role)
  465. VALUES (?, ?)|;
  466. }
  467. $sth = $dbh->prepare($query);
  468. $sth->execute(@queryargs) || $form->dberror($query);
  469. $dbh->commit;
  470. }
  471. sub delete_department {
  472. my ($self, $myconfig, $form) = @_;
  473. # connect to database
  474. my $dbh = $form->{dbh};
  475. $query = qq|
  476. DELETE FROM department
  477. WHERE id = ?|;
  478. $dbh->prepare($query)->execute($form->{id});
  479. $dbh->commit;
  480. }
  481. sub business {
  482. my ($self, $myconfig, $form) = @_;
  483. # connect to database
  484. my $dbh = $form->{dbh};
  485. $form->sort_order();
  486. my $query = qq|
  487. SELECT id, description, discount
  488. FROM business
  489. ORDER BY description $form->{direction}|;
  490. $sth = $dbh->prepare($query);
  491. $sth->execute || $form->dberror($query);
  492. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  493. push @{ $form->{ALL} }, $ref;
  494. }
  495. $sth->finish;
  496. $dbh->commit;
  497. }
  498. sub get_business {
  499. my ($self, $myconfig, $form) = @_;
  500. # connect to database
  501. my $dbh = $form->{dbh};
  502. my $query = qq|
  503. SELECT description, discount
  504. FROM business
  505. WHERE id = ?|;
  506. $sth = $dbh->prepare($query);
  507. $sth->execute($form->{id});
  508. ($form->{description}, $form->{discount}) = $sth->fetchrow_array();
  509. $dbh->commit;
  510. }
  511. sub save_business {
  512. my ($self, $myconfig, $form) = @_;
  513. # connect to database
  514. my $dbh = $form->{dbh};
  515. $form->{description} =~ s/-(-)+/-/g;
  516. $form->{description} =~ s/ ( )+/ /g;
  517. $form->{discount} /= 100;
  518. my $sth;
  519. my @queryargs = ($form->{description}, $form->{discount});
  520. if ($form->{id}) {
  521. $query = qq|
  522. UPDATE business
  523. SET description = ?,
  524. discount = ?
  525. WHERE id = ?|;
  526. push @queryargs, $form->{id};
  527. } else {
  528. $query = qq|INSERT INTO business (description, discount)
  529. VALUES (?, ?)|;
  530. }
  531. $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query);
  532. $dbh->commit;
  533. }
  534. sub delete_business {
  535. my ($self, $myconfig, $form) = @_;
  536. # connect to database
  537. my $dbh = $form->{dbh};
  538. $query = qq|
  539. DELETE FROM business
  540. WHERE id = ?|;
  541. $dbh->prepare($query)->execute($form->{id}) || $form->dberror($query);
  542. $dbh->commit;
  543. }
  544. sub sic {
  545. my ($self, $myconfig, $form) = @_;
  546. # connect to database
  547. my $dbh = $form->{dbh};
  548. $form->{sort} = "code" unless $form->{sort};
  549. my @a = qw(code description);
  550. my %ordinal = ( code => 1,
  551. description => 3 );
  552. my $sortorder = $form->sort_order(\@a, \%ordinal);
  553. my $query = qq|SELECT code, sictype, description
  554. FROM sic
  555. ORDER BY $sortorder|;
  556. $sth = $dbh->prepare($query);
  557. $sth->execute || $form->dberror($query);
  558. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  559. push @{ $form->{ALL} }, $ref;
  560. }
  561. $sth->finish;
  562. $dbh->commit;
  563. }
  564. sub get_sic {
  565. my ($self, $myconfig, $form) = @_;
  566. # connect to database
  567. my $dbh = $form->{dbh};
  568. my $query = qq|
  569. SELECT code, sictype, description
  570. FROM sic
  571. WHERE code = |.$dbh->quote($form->{code});
  572. my $sth = $dbh->prepare($query);
  573. $sth->execute || $form->dberror($query);
  574. my $ref = $sth->fetchrow_hashref(NAME_lc);
  575. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  576. $sth->finish;
  577. $dbh->commit;
  578. }
  579. sub save_sic {
  580. my ($self, $myconfig, $form) = @_;
  581. # connect to database
  582. my $dbh = $form->{dbh};
  583. foreach my $item (qw(code description)) {
  584. $form->{$item} =~ s/-(-)+/-/g;
  585. }
  586. my $sth;
  587. @queryargs = ($form->{code}, $form->{sictype}, $form->{description});
  588. # if there is an id
  589. if ($form->{id}) {
  590. $query = qq|
  591. UPDATE sic
  592. SET code = ?,
  593. sictype = ?,
  594. description = ?
  595. WHERE code = ?)|;
  596. push @queryargs, $form->{id};
  597. } else {
  598. $query = qq|
  599. INSERT INTO sic (code, sictype, description)
  600. VALUES (?, ?, ?)|;
  601. }
  602. $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query);
  603. $dbh->commit;
  604. }
  605. sub delete_sic {
  606. my ($self, $myconfig, $form) = @_;
  607. # connect to database
  608. my $dbh = $form->{dbh};
  609. $query = qq|
  610. DELETE FROM sic
  611. WHERE code = ?|;
  612. $dbh->prepare($query)->execute($form->{code});
  613. $dbh->commit;
  614. }
  615. sub language {
  616. my ($self, $myconfig, $form) = @_;
  617. # connect to database
  618. my $dbh = $form->{dbh};
  619. $form->{sort} = "code" unless $form->{sort};
  620. my @a = qw(code description);
  621. my %ordinal = ( code => 1,
  622. description => 2 );
  623. my $sortorder = $form->sort_order(\@a, \%ordinal);
  624. my $query = qq|
  625. SELECT code, description
  626. FROM language
  627. ORDER BY $sortorder|;
  628. $sth = $dbh->prepare($query);
  629. $sth->execute || $form->dberror($query);
  630. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  631. push @{ $form->{ALL} }, $ref;
  632. }
  633. $sth->finish;
  634. $dbh->commit;
  635. }
  636. sub get_language {
  637. my ($self, $myconfig, $form) = @_;
  638. # connect to database
  639. my $dbh = $form->{dbh};
  640. ## needs fixing (SELECT *...)
  641. my $query = qq|
  642. SELECT *
  643. FROM language
  644. WHERE code = ?|;
  645. my $sth = $dbh->prepare($query);
  646. $sth->execute($form->{code}) || $form->dberror($query);
  647. my $ref = $sth->fetchrow_hashref(NAME_lc);
  648. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  649. $sth->finish;
  650. $dbh->commit;
  651. }
  652. sub save_language {
  653. my ($self, $myconfig, $form) = @_;
  654. # connect to database
  655. my $dbh = $form->{dbh};
  656. $form->{code} =~ s/ //g;
  657. foreach my $item (qw(code description)) {
  658. $form->{$item} =~ s/-(-)+/-/g;
  659. $form->{$item} =~ s/ ( )+/-/g;
  660. }
  661. my $sth;
  662. my @queryargs = ($form->{code}, $form->{description});
  663. # if there is an id
  664. if ($form->{id}) {
  665. $query = qq|
  666. UPDATE language
  667. SET code = ?,
  668. description = ?
  669. WHERE code = ?|;
  670. push @queryargs, $form->{id};
  671. } else {
  672. $query = qq|
  673. INSERT INTO language (code, description)
  674. VALUES (?, ?)|;
  675. }
  676. $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query);
  677. $dbh->commit;
  678. }
  679. sub delete_language {
  680. my ($self, $myconfig, $form) = @_;
  681. # connect to database
  682. my $dbh = $form->{dbh};
  683. $query = qq|
  684. DELETE FROM language
  685. WHERE code = |.$dbh->quote($form->{code});
  686. $dbh->do($query) || $form->dberror($query);
  687. $dbh->{dbh};
  688. }
  689. sub recurring_transactions {
  690. my ($self, $myconfig, $form) = @_;
  691. my $dbh = $form->{dbh};
  692. my $query = qq|SELECT value FROM defaults where setting_key = 'curr'|;
  693. my ($defaultcurrency) = $dbh->selectrow_array($query);
  694. $defaultcurrency = $dbh->quote($defaultcurrency =~ s/:.*//g);
  695. $form->{sort} ||= "nextdate";
  696. my @a = ($form->{sort});
  697. my $sortorder = $form->sort_order(\@a);
  698. $query = qq|
  699. SELECT 'ar' AS module, 'ar' AS transaction, a.invoice,
  700. n.name AS description, a.amount,
  701. s.*, se.formname AS recurringemail,
  702. sp.formname AS recurringprint,
  703. s.nextdate - current_date AS overdue,
  704. 'customer' AS vc,
  705. ex.buy AS exchangerate, a.curr,
  706. (s.nextdate IS NULL OR s.nextdate > s.enddate)
  707. AS expired
  708. FROM recurring s
  709. JOIN ar a ON (a.id = s.id)
  710. JOIN customer n ON (n.id = a.customer_id)
  711. LEFT JOIN recurringemail se ON (se.id = s.id)
  712. LEFT JOIN recurringprint sp ON (sp.id = s.id)
  713. LEFT JOIN exchangerate ex
  714. ON (ex.curr = a.curr AND a.transdate = ex.transdate)
  715. UNION
  716. SELECT 'ap' AS module, 'ap' AS transaction, a.invoice,
  717. n.name AS description, a.amount,
  718. s.*, se.formname AS recurringemail,
  719. sp.formname AS recurringprint,
  720. s.nextdate - current_date AS overdue, 'vendor' AS vc,
  721. ex.sell AS exchangerate, a.curr,
  722. (s.nextdate IS NULL OR s.nextdate > s.enddate)
  723. AS expired
  724. FROM recurring s
  725. JOIN ap a ON (a.id = s.id)
  726. JOIN vendor n ON (n.id = a.vendor_id)
  727. LEFT JOIN recurringemail se ON (se.id = s.id)
  728. LEFT JOIN recurringprint sp ON (sp.id = s.id)
  729. LEFT JOIN exchangerate ex ON
  730. (ex.curr = a.curr AND a.transdate = ex.transdate)
  731. UNION
  732. SELECT 'gl' AS module, 'gl' AS transaction, FALSE AS invoice,
  733. a.description, (SELECT SUM(ac.amount)
  734. FROM acc_trans ac
  735. WHERE ac.trans_id = a.id
  736. AND ac.amount > 0) AS amount,
  737. s.*, se.formname AS recurringemail,
  738. sp.formname AS recurringprint,
  739. s.nextdate - current_date AS overdue, '' AS vc,
  740. '1' AS exchangerate, $defaultcurrency AS curr,
  741. (s.nextdate IS NULL OR s.nextdate > s.enddate)
  742. AS expired
  743. FROM recurring s
  744. JOIN gl a ON (a.id = s.id)
  745. LEFT JOIN recurringemail se ON (se.id = s.id)
  746. LEFT JOIN recurringprint sp ON (sp.id = s.id)
  747. UNION
  748. SELECT 'oe' AS module, 'so' AS transaction, FALSE AS invoice,
  749. n.name AS description, a.amount,
  750. s.*, se.formname AS recurringemail,
  751. sp.formname AS recurringprint,
  752. s.nextdate - current_date AS overdue,
  753. 'customer' AS vc,
  754. ex.buy AS exchangerate, a.curr,
  755. (s.nextdate IS NULL OR s.nextdate > s.enddate)
  756. AS expired
  757. FROM recurring s
  758. JOIN oe a ON (a.id = s.id)
  759. JOIN customer n ON (n.id = a.customer_id)
  760. LEFT JOIN recurringemail se ON (se.id = s.id)
  761. LEFT JOIN recurringprint sp ON (sp.id = s.id)
  762. LEFT JOIN exchangerate ex ON
  763. (ex.curr = a.curr AND a.transdate = ex.transdate)
  764. WHERE a.quotation = '0'
  765. UNION
  766. SELECT 'oe' AS module, 'po' AS transaction, FALSE AS invoice,
  767. n.name AS description, a.amount,
  768. s.*, se.formname AS recurringemail,
  769. sp.formname AS recurringprint,
  770. s.nextdate - current_date AS overdue, 'vendor' AS vc,
  771. ex.sell AS exchangerate, a.curr,
  772. (s.nextdate IS NULL OR s.nextdate > s.enddate)
  773. AS expired
  774. FROM recurring s
  775. JOIN oe a ON (a.id = s.id)
  776. JOIN vendor n ON (n.id = a.vendor_id)
  777. LEFT JOIN recurringemail se ON (se.id = s.id)
  778. LEFT JOIN recurringprint sp ON (sp.id = s.id)
  779. LEFT JOIN exchangerate ex ON
  780. (ex.curr = a.curr AND a.transdate = ex.transdate)
  781. WHERE a.quotation = '0'
  782. ORDER BY $sortorder|;
  783. my $sth = $dbh->prepare($query);
  784. $sth->execute || $form->dberror($query);
  785. my $id;
  786. my $transaction;
  787. my %e = ();
  788. my %p = ();
  789. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  790. $ref->{exchangerate} ||= 1;
  791. if ($ref->{id} != $id) {
  792. if (%e) {
  793. $form->{transactions}{$transaction}->[$i]->{recurringemail} = "";
  794. for (keys %e) {
  795. $form->{transactions}{$transaction}->[$i]->{recurringemail} .= "${_}:";
  796. }
  797. chop $form->{transactions}{$transaction}->[$i]->{recurringemail};
  798. }
  799. if (%p) {
  800. $form->{transactions}{$transaction}->[$i]->{recurringprint} = "";
  801. for (keys %p) { $form->{transactions}{$transaction}->[$i]->{recurringprint} .= "${_}:" }
  802. chop $form->{transactions}{$transaction}->[$i]->{recurringprint};
  803. }
  804. %e = ();
  805. %p = ();
  806. push @{ $form->{transactions}{$ref->{transaction}} }, $ref;
  807. $id = $ref->{id};
  808. $i = $#{ $form->{transactions}{$ref->{transaction}} };
  809. }
  810. $transaction = $ref->{transaction};
  811. $e{$ref->{recurringemail}} = 1 if $ref->{recurringemail};
  812. $p{$ref->{recurringprint}} = 1 if $ref->{recurringprint};
  813. }
  814. $sth->finish;
  815. # this is for the last row
  816. if (%e) {
  817. $form->{transactions}{$transaction}->[$i]->{recurringemail} = "";
  818. for (keys %e) { $form->{transactions}{$transaction}->[$i]->{recurringemail} .= "${_}:" }
  819. chop $form->{transactions}{$transaction}->[$i]->{recurringemail};
  820. }
  821. if (%p) {
  822. $form->{transactions}{$transaction}->[$i]->{recurringprint} = "";
  823. for (keys %p) { $form->{transactions}{$transaction}->[$i]->{recurringprint} .= "${_}:" }
  824. chop $form->{transactions}{$transaction}->[$i]->{recurringprint};
  825. }
  826. $dbh->commit;
  827. }
  828. sub recurring_details {
  829. my ($self, $myconfig, $form, $id) = @_;
  830. my $dbh = $form->{dbh};
  831. my $query = qq|
  832. SELECT s.*, ar.id AS arid, ar.invoice AS arinvoice,
  833. ap.id AS apid, ap.invoice AS apinvoice,
  834. ar.duedate - ar.transdate AS overdue,
  835. ar.datepaid - ar.transdate AS paid,
  836. oe.reqdate - oe.transdate AS req,
  837. oe.id AS oeid, oe.customer_id, oe.vendor_id
  838. FROM recurring s
  839. LEFT JOIN ar ON (ar.id = s.id)
  840. LEFT JOIN ap ON (ap.id = s.id)
  841. LEFT JOIN oe ON (oe.id = s.id)
  842. WHERE s.id = ?|;
  843. my $sth = $dbh->prepare($query);
  844. $sth->execute($id) || $form->dberror($query);
  845. my $ref = $sth->fetchrow_hashref(NAME_lc);
  846. $form->{vc} = "customer" if $ref->{customer_id};
  847. $form->{vc} = "vendor" if $ref->{vendor_id};
  848. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  849. $sth->finish;
  850. $form->{invoice} = ($form->{arid} && $form->{arinvoice});
  851. $form->{invoice} = ($form->{apid} && $form->{apinvoice}) unless $form->{invoice};
  852. $query = qq|
  853. SELECT *
  854. FROM recurringemail
  855. WHERE id = ?|;
  856. $sth = $dbh->prepare($query);
  857. $sth->execute($id) || $form->dberror($query);
  858. $form->{recurringemail} = "";
  859. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  860. $form->{recurringemail} .= "$ref->{formname}:$ref->{format}:";
  861. $form->{message} = $ref->{message};
  862. }
  863. $sth->finish;
  864. $query = qq|
  865. SELECT *
  866. FROM recurringprint
  867. WHERE id = ?|;
  868. $sth = $dbh->prepare($query);
  869. $sth->execute($id) || $form->dberror($query);
  870. $form->{recurringprint} = "";
  871. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  872. $form->{recurringprint} .=
  873. "$ref->{formname}:$ref->{format}:$ref->{printer}:";
  874. }
  875. $sth->finish;
  876. chop $form->{recurringemail};
  877. chop $form->{recurringprint};
  878. for (qw(arinvoice apinvoice)) { delete $form->{$_} }
  879. $dbh->commit;
  880. }
  881. sub update_recurring {
  882. my ($self, $myconfig, $form, $id) = @_;
  883. my $dbh = $form->{dbh};
  884. $id = $dbh->quote($id);
  885. my $query = qq|
  886. SELECT nextdate, repeat, unit
  887. FROM recurring
  888. WHERE id = $id|;
  889. my ($nextdate, $repeat, $unit) = $dbh->selectrow_array($query);
  890. $nextdate = $dbh->quote($nextdate);
  891. my $interval = $dbh->quote("$repeat $unit");
  892. # check if it is the last date
  893. $query = qq|
  894. SELECT (date $nextdate + interval $interval) > enddate
  895. FROM recurring
  896. WHERE id = $id|;
  897. my ($last_repeat) = $dbh->selectrow_array($query);
  898. if ($last_repeat) {
  899. $advance{$myconfig->{dbdriver}} = "NULL";
  900. }
  901. $query = qq|
  902. UPDATE recurring
  903. SET nextdate = (date $nextdate + interval $interval)
  904. WHERE id = $id|;
  905. $dbh->do($query) || $form->dberror($query);
  906. $dbh->commit;
  907. }
  908. sub check_template_name {
  909. my ($self, $myconfig, $form) = @_;
  910. my @allowedsuff = qw(css tex txt html xml);
  911. if ($form->{file} =~ /^(.:)*?\/|:|\.\.\/|^\//){
  912. $form->error("Directory transversal not allowed.");
  913. }
  914. if ($form->{file} =~ /^${LedgerSMB::Sysconfig::backuppath}\//){
  915. $form->error("Not allowed to access ${LedgerSMB::Sysconfig::backuppath}/ with this method");
  916. }
  917. my $whitelisted = 0;
  918. for (@allowedsuff){
  919. if ($form->{file} =~ /$_$/){
  920. $whitelisted = 1;
  921. }
  922. }
  923. if (!$whitelisted){
  924. $form->error("Error: File is of type that is not allowed.");
  925. }
  926. if ($form->{file} !~ /^$myconfig->{templates}\//){
  927. $form->error("Not in a whitelisted directory: $form->{file}") unless $form->{file} =~ /^css\//;
  928. }
  929. }
  930. sub load_template {
  931. my ($self, $myconfig, $form) = @_;
  932. $self->check_template_name(\%$myconfig, \%$form);
  933. open(TEMPLATE, '<', "$form->{file}") or $form->error("$form->{file} : $!");
  934. while (<TEMPLATE>) {
  935. $form->{body} .= $_;
  936. }
  937. close(TEMPLATE);
  938. }
  939. sub save_template {
  940. my ($self, $myconfig, $form) = @_;
  941. $self->check_template_name(\%$myconfig, \%$form);
  942. open(TEMPLATE, '>', "$form->{file}") or $form->error("$form->{file} : $!");
  943. # strip
  944. $form->{body} =~ s/\r//g;
  945. print TEMPLATE $form->{body};
  946. close(TEMPLATE);
  947. }
  948. sub save_preferences {
  949. my ($self, $myconfig, $form) = @_;
  950. # connect to database
  951. my $dbh = $form->{dbh};
  952. # get username, is same as requested?
  953. my @queryargs;
  954. my $query = qq|
  955. SELECT login
  956. FROM employee
  957. WHERE login = ?|;
  958. @queryargs = ($form->{login});
  959. my $sth = $dbh->prepare($query);
  960. $sth->execute(@queryargs) || $form->dberror($query);
  961. my ($dbusername) = $sth->fetchrow_array;
  962. $sth->finish;
  963. return 0 if ($dbusername ne $form->{login});
  964. # update name
  965. $query = qq|
  966. UPDATE employee
  967. SET name = ?
  968. WHERE login = ?|;
  969. @queryargs = ($form->{name}, $form->{login});
  970. $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query);
  971. # get default currency
  972. $query = qq|
  973. SELECT value, (SELECT value FROM defaults
  974. WHERE setting_key = 'businessnumber')
  975. FROM defaults
  976. WHERE setting_key = 'curr'|;
  977. ($form->{currency}, $form->{businessnumber}) =
  978. $dbh->selectrow_array($query);
  979. $form->{currency} =~ s/:.*//;
  980. $dbh->commit;
  981. my $myconfig = LedgerSMB::User->new($form->{login});
  982. map {$myconfig->{$_} = $form->{$_} if exists $form->{$_}}
  983. qw(name email dateformat signature numberformat vclimit tel fax
  984. company menuwidth countrycode address timeout stylesheet
  985. printer password);
  986. $myconfig->{password} = $form->{new_password} if ($form->{old_password} ne $form->{new_password});
  987. $myconfig->save_member();
  988. 1;
  989. }
  990. sub save_defaults {
  991. my ($self, $myconfig, $form) = @_;
  992. for (qw(IC IC_income IC_expense FX_gain FX_loss)) { ($form->{$_}) = split /--/, $form->{$_} }
  993. my @a;
  994. $form->{curr} =~ s/ //g;
  995. for (split /:/, $form->{curr}) { push(@a, uc pack "A3", $_) if $_ }
  996. $form->{curr} = join ':', @a;
  997. # connect to database
  998. my $dbh = $form->{dbh};
  999. # save defaults
  1000. $sth_plain = $dbh->prepare("
  1001. UPDATE defaults SET value = ? WHERE setting_key = ?");
  1002. $sth_accno = $dbh->prepare(qq|
  1003. UPDATE defaults
  1004. SET value = (SELECT id
  1005. FROM chart
  1006. WHERE accno = ?)
  1007. WHERE setting_key = ?|);
  1008. my %translation = (
  1009. inventory_accno_id => 'IC',
  1010. income_accno_id => 'IC_income',
  1011. expense_accno_id => 'IC_expense',
  1012. fxgain_accno_id => 'FX_gain',
  1013. fxloss_accno_id => 'FX_loss'
  1014. );
  1015. for (
  1016. qw(inventory_accno_id income_accno_id expense_accno_id
  1017. fxgain_accno_id fxloss_accno_id glnumber sinumber vinumber
  1018. sonumber ponumber sqnumber rfqnumber partnumber employeenumber
  1019. customernumber vendornumber projectnumber yearend curr
  1020. weightunit businessnumber)
  1021. ){
  1022. my $val = $form->{$_};
  1023. if ($translation{$_}){
  1024. $val = $form->{$translation{$_}};
  1025. }
  1026. if ($_ =~ /accno/){
  1027. $sth_accno->execute($val, $_)
  1028. || $form->dberror("Saving $_");
  1029. } else {
  1030. $sth_plain->execute($val, $_)
  1031. || $form->dberror("Saving $_");
  1032. }
  1033. }
  1034. my $rc = $dbh->commit;
  1035. $rc;
  1036. }
  1037. sub defaultaccounts {
  1038. my ($self, $myconfig, $form) = @_;
  1039. # connect to database
  1040. my $dbh = $form->{dbh};
  1041. # get defaults from defaults table
  1042. my $query = qq|
  1043. SELECT setting_key, value FROM defaults
  1044. WHERE setting_key LIKE ?|;
  1045. my $sth = $dbh->prepare($query);
  1046. $sth->execute('%accno_id') || $form->dberror($query);
  1047. my $ref;
  1048. while ($ref = $sth->fetchrow_hashref(NAME_lc)){
  1049. $form->{$ref->{setting_key}} = $ref->{value};
  1050. }
  1051. $form->{defaults}{IC} = $form->{inventory_accno_id};
  1052. $form->{defaults}{IC_income} = $form->{income_accno_id};
  1053. $form->{defaults}{IC_sale} = $form->{income_accno_id};
  1054. $form->{defaults}{IC_expense} = $form->{expense_accno_id};
  1055. $form->{defaults}{IC_cogs} = $form->{expense_accno_id};
  1056. $form->{defaults}{FX_gain} = $form->{fxgain_accno_id};
  1057. $form->{defaults}{FX_loss} = $form->{fxloss_accno_id};
  1058. $sth->finish;
  1059. $query = qq|
  1060. SELECT id, accno, description, link
  1061. FROM chart
  1062. WHERE link LIKE '%IC%'
  1063. ORDER BY accno|;
  1064. $sth = $dbh->prepare($query);
  1065. $sth->execute || $form->dberror($query);
  1066. my $nkey;
  1067. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1068. foreach my $key (split(/:/, $ref->{link})) {
  1069. if ($key =~ /IC/) {
  1070. $nkey = $key;
  1071. if ($key =~ /cogs/) {
  1072. $nkey = "IC_expense";
  1073. }
  1074. if ($key =~ /sale/) {
  1075. $nkey = "IC_income";
  1076. }
  1077. %{ $form->{accno}{$nkey}{$ref->{accno}} } = ( id => $ref->{id},
  1078. description => $ref->{description} );
  1079. }
  1080. }
  1081. }
  1082. $sth->finish;
  1083. $query = qq|
  1084. SELECT id, accno, description
  1085. FROM chart
  1086. WHERE (category = 'I' OR category = 'E')
  1087. AND charttype = 'A'
  1088. ORDER BY accno|;
  1089. $sth = $dbh->prepare($query);
  1090. $sth->execute || $form->dberror($query);
  1091. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  1092. %{ $form->{accno}{FX_gain}{$ref->{accno}} } = ( id => $ref->{id},
  1093. description => $ref->{description} );
  1094. %{ $form->{accno}{FX_loss}{$ref->{accno}} } = ( id => $ref->{id},
  1095. description => $ref->{description} );
  1096. }
  1097. $sth->finish;
  1098. $dbh->commit;
  1099. }
  1100. sub taxes {
  1101. my ($self, $myconfig, $form) = @_;
  1102. my $taxaccounts = '';
  1103. # connect to database
  1104. my $dbh = $form->{dbh};
  1105. my $query = qq|
  1106. SELECT c.id, c.accno, c.description,
  1107. t.rate * 100 AS rate, t.taxnumber, t.validto,
  1108. t.pass, m.taxmodulename
  1109. FROM chart c
  1110. JOIN tax t ON (c.id = t.chart_id)
  1111. JOIN taxmodule m ON (t.taxmodule_id = m.taxmodule_id)
  1112. ORDER BY 3, 6|;
  1113. my $sth = $dbh->prepare($query);
  1114. $sth->execute || $form->dberror($query);
  1115. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  1116. push @{ $form->{taxrates} }, $ref;
  1117. $taxaccounts .= " " . $ref{accno};
  1118. }
  1119. $sth->finish;
  1120. $query = qq|
  1121. SELECT taxmodule_id, taxmodulename FROM taxmodule
  1122. ORDER BY 2|;
  1123. $sth = $dbh->prepare($query);
  1124. $sth->execute || $form->dberror($query);
  1125. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  1126. $form->{"taxmodule_".$ref->{taxmodule_id}} =
  1127. $ref->{taxmodulename};
  1128. }
  1129. $sth->finish;
  1130. $dbh->commit;
  1131. }
  1132. sub save_taxes {
  1133. my ($self, $myconfig, $form) = @_;
  1134. # connect to database
  1135. my $dbh = $form->{dbh};
  1136. my $query = qq|DELETE FROM tax|;
  1137. $dbh->do($query) || $form->dberror($query);
  1138. $query = qq|
  1139. INSERT INTO tax (chart_id, rate, taxnumber, validto,
  1140. pass, taxmodule_id)
  1141. VALUES (?, ?, ?, ?, ?, ?)|;
  1142. my $sth = $dbh->prepare($query);
  1143. foreach my $item (split / /, $form->{taxaccounts}) {
  1144. my ($chart_id, $i) = split /_/, $item;
  1145. my $rate = $form->parse_amount(
  1146. $myconfig, $form->{"taxrate_$i"}) / 100;
  1147. my $validto = $form->{"validto_$i"};
  1148. $validto = undef if not $validto;
  1149. my @queryargs = ($chart_id, $rate, $form->{"taxnumber_$i"},
  1150. $validto, $form->{"pass_$i"},
  1151. $form->{"taxmodule_id_$i"});
  1152. $sth->execute(@queryargs) || $form->dberror($query);
  1153. }
  1154. my $rc = $dbh->commit;
  1155. $rc;
  1156. }
  1157. sub backup {
  1158. my ($self, $myconfig, $form) = @_;
  1159. my $mail;
  1160. my $err;
  1161. my @t = localtime(time);
  1162. $t[4]++;
  1163. $t[5] += 1900;
  1164. $t[3] = substr("0$t[3]", -2);
  1165. $t[4] = substr("0$t[4]", -2);
  1166. my $boundary = time;
  1167. my $tmpfile = "${LedgerSMB::Sysconfig::backuppath}/$boundary.$globalDBname-$form->{dbversion}-$t[5]$t[4]$t[3].sql";
  1168. $form->{OUT} = "$tmpfile";
  1169. open(OUT, '>', "$form->{OUT}") or $form->error("$form->{OUT} : $!");
  1170. # get sequences, functions and triggers
  1171. my $today = scalar localtime;
  1172. # compress backup if gzip defined
  1173. my $suffix = "";
  1174. if ($form->{media} eq 'email') {
  1175. print OUT qx(PGPASSWORD="$myconfig->{dbpasswd}" pg_dump -U $myconfig->{dbuser} -h $myconfig->{dbhost} -Fc -p $myconfig->{dbport} $myconfig->{dbname});
  1176. close OUT;
  1177. use LedgerSMB::Mailer;
  1178. $mail = new Mailer;
  1179. $mail->{to} = qq|"$myconfig->{name}" <$myconfig->{email}>|;
  1180. $mail->{from} = qq|"$myconfig->{name}" <$myconfig->{email}>|;
  1181. $mail->{subject} = "LedgerSMB Backup / $globalDBname-$form->{dbversion}-$t[5]$t[4]$t[3].sql$suffix";
  1182. @{ $mail->{attachments} } = ($tmpfile);
  1183. $mail->{version} = $form->{version};
  1184. $mail->{fileid} = "$boundary.";
  1185. $mail->{format} = "plain";
  1186. $mail->{format} = "octet-stream";
  1187. $myconfig->{signature} =~ s/\\n/\n/g;
  1188. $mail->{message} = "-- \n$myconfig->{signature}";
  1189. $err = $mail->send;
  1190. }
  1191. if ($form->{media} eq 'file') {
  1192. open(IN, '<', "$tmpfile") or $form->error("$tmpfile : $!");
  1193. open(OUT, ">-") or $form->error("STDOUT : $!");
  1194. print OUT qq|Content-Type: application/file;\n| .
  1195. qq|Content-Disposition: attachment; filename="$myconfig->{dbname}-$form->{dbversion}-$t[5]$t[4]$t[3].sql$suffix"\n\n|;
  1196. print OUT qx(PGPASSWORD="$myconfig->{dbpasswd}" pg_dump -U $myconfig->{dbuser} -h $myconfig->{dbhost} -Fc -p $myconfig->{dbport} $myconfig->{dbname});
  1197. }
  1198. unlink "$tmpfile";
  1199. }
  1200. sub closedto {
  1201. my ($self, $myconfig, $form) = @_;
  1202. my $dbh = $form->{dbh};
  1203. my $query = qq|
  1204. SELECT (SELECT value FROM defaults
  1205. WHERE setting_key = 'closedto'),
  1206. (SELECT value FROM defaults
  1207. WHERE setting_key = 'revtrans'),
  1208. (SELECT value FROM defaults
  1209. WHERE setting_key = 'audittrail')|;
  1210. ($form->{closedto}, $form->{revtrans}, $form->{audittrail})
  1211. = $dbh->selectrow_array($query);
  1212. $dbh->commit;
  1213. }
  1214. sub closebooks {
  1215. my ($self, $myconfig, $form) = @_;
  1216. my $dbh = $form->{dbh};
  1217. my $query = qq|
  1218. UPDATE defaults SET value = ?
  1219. WHERE setting_key = ?|;
  1220. my $sth = $dbh->prepare($query);
  1221. for (qw(revtrans closedto audittrail)){
  1222. if ($form->{$_}){
  1223. $val = 1;
  1224. } else {
  1225. $val = 0;
  1226. }
  1227. $sth->execute($val, $_);
  1228. }
  1229. if ($form->{removeaudittrail}) {
  1230. $query = qq|
  1231. DELETE FROM audittrail
  1232. WHERE transdate < | .
  1233. $dbh->quote($form->{removeaudittrail});
  1234. $dbh->do($query) || $form->dberror($query);
  1235. }
  1236. $dbh->commit;
  1237. }
  1238. sub earningsaccounts {
  1239. my ($self, $myconfig, $form) = @_;
  1240. my ($query, $sth, $ref);
  1241. # connect to database
  1242. my $dbh = $form->{dbh};
  1243. # get chart of accounts
  1244. $query = qq|
  1245. SELECT accno,description
  1246. FROM chart
  1247. WHERE charttype = 'A'
  1248. AND category = 'Q'
  1249. ORDER BY accno|;
  1250. $sth = $dbh->prepare($query);
  1251. $sth->execute || $form->dberror($query);
  1252. $form->{chart} = "";
  1253. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  1254. push @{ $form->{chart} }, $ref;
  1255. }
  1256. $sth->finish;
  1257. $dbh->commit;
  1258. }
  1259. sub post_yearend {
  1260. my ($self, $myconfig, $form) = @_;
  1261. # connect to database, turn off AutoCommit
  1262. my $dbh = $form->{dbh};
  1263. my $query;
  1264. my @queryargs;
  1265. my $uid = localtime;
  1266. $uid .= "$$";
  1267. $query = qq|
  1268. INSERT INTO gl (reference, employee_id)
  1269. VALUES (?, (SELECT id FROM employee
  1270. WHERE login = ?))|;
  1271. $dbh->prepare($query)->execute($uid, $form->{login})
  1272. || $form->dberror($query);
  1273. $query = qq|
  1274. SELECT id
  1275. FROM gl
  1276. WHERE reference = ?|;
  1277. my $sth = $dbh->prepare($query);
  1278. $sth->execute($uid);
  1279. ($form->{id}) = $sth->fetchrow_array;
  1280. $query = qq|
  1281. UPDATE gl
  1282. SET reference = ?,
  1283. description = ?,
  1284. notes = ?,
  1285. transdate = ?,
  1286. department_id = 0
  1287. WHERE id = ?|;
  1288. @queryargs = ($form->{reference}, $form->{description}, $form->{notes},
  1289. $form->{transdate}, $form->{id});
  1290. $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query);
  1291. my $amount;
  1292. my $accno;
  1293. $query = qq|
  1294. INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
  1295. source)
  1296. VALUES (?, (SELECT id
  1297. FROM chart
  1298. WHERE accno = ?),
  1299. ?, ?, ?)|;
  1300. # insert acc_trans transactions
  1301. for my $i (1 .. $form->{rowcount}) {
  1302. # extract accno
  1303. ($accno) = split(/--/, $form->{"accno_$i"});
  1304. $amount = 0;
  1305. if ($form->{"credit_$i"}) {
  1306. $amount = $form->{"credit_$i"};
  1307. }
  1308. if ($form->{"debit_$i"}) {
  1309. $amount = $form->{"debit_$i"} * -1;
  1310. }
  1311. # if there is an amount, add the record
  1312. if ($amount) {
  1313. my @args = ($form->{id}, $accno, $amount,
  1314. $form->{transdate}, $form->{reference});
  1315. $dbh->prepare($query)->execute(@args)
  1316. || $form->dberror($query);
  1317. }
  1318. }
  1319. $query = qq|
  1320. INSERT INTO yearend (trans_id, transdate)
  1321. VALUES (?, ?)|;
  1322. $dbh->prepare($query)->execute($form->{id}, $form->{transdate})
  1323. || $form->dberror($query);
  1324. my %audittrail = (
  1325. tablename => 'gl',
  1326. reference => $form->{reference},
  1327. formname => 'yearend',
  1328. action => 'posted',
  1329. id => $form->{id} );
  1330. $form->audittrail($dbh, "", \%audittrail);
  1331. # commit and redirect
  1332. my $rc = $dbh->commit;
  1333. $rc;
  1334. }
  1335. sub get_all_defaults{
  1336. my ($self, $form) = @_;
  1337. my $dbh = $form->{dbh};
  1338. my $query = "select setting_key, value FROM defaults";
  1339. $sth = $dbh->prepare($query);
  1340. $sth->execute;
  1341. while (($skey, $value) = $sth->fetchrow_array()){
  1342. $form->{$skey} = $value;
  1343. }
  1344. $self->defaultaccounts(undef, $form);
  1345. $dbh->commit;
  1346. }
  1347. 1;