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