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