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