summaryrefslogtreecommitdiff
path: root/LedgerSMB/AM.pm
blob: 19a60f4bdece3b168942a4e65c3a10bf61acba6b (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. $form->db_parse_numeric(sth => $sth, hashref => $ref);
  803. if ( $ref->{id} != $id ) {
  804. if (%e) {
  805. $form->{transactions}{$transaction}->[$i]->{recurringemail} =
  806. "";
  807. for ( keys %e ) {
  808. $form->{transactions}{$transaction}->[$i]
  809. ->{recurringemail} .= "${_}:";
  810. }
  811. chop $form->{transactions}{$transaction}->[$i]
  812. ->{recurringemail};
  813. }
  814. if (%p) {
  815. $form->{transactions}{$transaction}->[$i]->{recurringprint} =
  816. "";
  817. for ( keys %p ) {
  818. $form->{transactions}{$transaction}->[$i]
  819. ->{recurringprint} .= "${_}:";
  820. }
  821. chop $form->{transactions}{$transaction}->[$i]
  822. ->{recurringprint};
  823. }
  824. %e = ();
  825. %p = ();
  826. push @{ $form->{transactions}{ $ref->{transaction} } }, $ref;
  827. $id = $ref->{id};
  828. $i = $#{ $form->{transactions}{ $ref->{transaction} } };
  829. }
  830. $transaction = $ref->{transaction};
  831. $e{ $ref->{recurringemail} } = 1 if $ref->{recurringemail};
  832. $p{ $ref->{recurringprint} } = 1 if $ref->{recurringprint};
  833. }
  834. $sth->finish;
  835. # this is for the last row
  836. if (%e) {
  837. $form->{transactions}{$transaction}->[$i]->{recurringemail} = "";
  838. for ( keys %e ) {
  839. $form->{transactions}{$transaction}->[$i]->{recurringemail} .=
  840. "${_}:";
  841. }
  842. chop $form->{transactions}{$transaction}->[$i]->{recurringemail};
  843. }
  844. if (%p) {
  845. $form->{transactions}{$transaction}->[$i]->{recurringprint} = "";
  846. for ( keys %p ) {
  847. $form->{transactions}{$transaction}->[$i]->{recurringprint} .=
  848. "${_}:";
  849. }
  850. chop $form->{transactions}{$transaction}->[$i]->{recurringprint};
  851. }
  852. $dbh->commit;
  853. }
  854. sub recurring_details {
  855. my ( $self, $myconfig, $form, $id ) = @_;
  856. my $dbh = $form->{dbh};
  857. my $query = qq|
  858. SELECT s.*, ar.id AS arid, ar.invoice AS arinvoice,
  859. ap.id AS apid, ap.invoice AS apinvoice,
  860. ar.duedate - ar.transdate AS overdue,
  861. ar.datepaid - ar.transdate AS paid,
  862. oe.reqdate - oe.transdate AS req,
  863. oe.id AS oeid, oe.customer_id, oe.vendor_id
  864. FROM recurring s
  865. LEFT JOIN ar ON (ar.id = s.id)
  866. LEFT JOIN ap ON (ap.id = s.id)
  867. LEFT JOIN oe ON (oe.id = s.id)
  868. WHERE s.id = ?|;
  869. my $sth = $dbh->prepare($query);
  870. $sth->execute($id) || $form->dberror($query);
  871. my $ref = $sth->fetchrow_hashref(NAME_lc);
  872. $form->{vc} = "customer" if $ref->{customer_id};
  873. $form->{vc} = "vendor" if $ref->{vendor_id};
  874. for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
  875. $sth->finish;
  876. $form->{invoice} = ( $form->{arid} && $form->{arinvoice} );
  877. $form->{invoice} = ( $form->{apid} && $form->{apinvoice} )
  878. unless $form->{invoice};
  879. $query = qq|
  880. SELECT *
  881. FROM recurringemail
  882. WHERE id = ?|;
  883. $sth = $dbh->prepare($query);
  884. $sth->execute($id) || $form->dberror($query);
  885. $form->{recurringemail} = "";
  886. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  887. $form->{recurringemail} .= "$ref->{formname}:$ref->{format}:";
  888. $form->{message} = $ref->{message};
  889. }
  890. $sth->finish;
  891. $query = qq|
  892. SELECT *
  893. FROM recurringprint
  894. WHERE id = ?|;
  895. $sth = $dbh->prepare($query);
  896. $sth->execute($id) || $form->dberror($query);
  897. $form->{recurringprint} = "";
  898. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  899. $form->{recurringprint} .=
  900. "$ref->{formname}:$ref->{format}:$ref->{printer}:";
  901. }
  902. $sth->finish;
  903. chop $form->{recurringemail};
  904. chop $form->{recurringprint};
  905. for (qw(arinvoice apinvoice)) { delete $form->{$_} }
  906. $dbh->commit;
  907. }
  908. sub update_recurring {
  909. my ( $self, $myconfig, $form, $id ) = @_;
  910. my $dbh = $form->{dbh};
  911. $id = $dbh->quote($id);
  912. my $query = qq|
  913. SELECT nextdate, repeat, unit
  914. FROM recurring
  915. WHERE id = $id|;
  916. my ( $nextdate, $repeat, $unit ) = $dbh->selectrow_array($query);
  917. $nextdate = $dbh->quote($nextdate);
  918. my $interval = $dbh->quote("$repeat $unit");
  919. # check if it is the last date
  920. $query = qq|
  921. SELECT (date $nextdate + interval $interval) > enddate
  922. FROM recurring
  923. WHERE id = $id|;
  924. my ($last_repeat) = $dbh->selectrow_array($query);
  925. if ($last_repeat) {
  926. $advance{ $myconfig->{dbdriver} } = "NULL";
  927. }
  928. $query = qq|
  929. UPDATE recurring
  930. SET nextdate = (date $nextdate + interval $interval)
  931. WHERE id = $id|;
  932. $dbh->do($query) || $form->dberror($query);
  933. $dbh->commit;
  934. }
  935. sub check_template_name {
  936. my ( $self, $myconfig, $form ) = @_;
  937. my @allowedsuff = qw(css tex txt html xml);
  938. if ( $form->{file} =~ /^(.:)*?\/|:|\.\.\/|^\// ) {
  939. $form->error("Directory transversal not allowed.");
  940. }
  941. if ( $form->{file} =~ /^${LedgerSMB::Sysconfig::backuppath}\// ) {
  942. $form->error(
  943. "Not allowed to access ${LedgerSMB::Sysconfig::backuppath}/ with this method"
  944. );
  945. }
  946. my $whitelisted = 0;
  947. for (@allowedsuff) {
  948. if ( $form->{file} =~ /$_$/ ) {
  949. $whitelisted = 1;
  950. }
  951. }
  952. if ( !$whitelisted ) {
  953. $form->error("Error: File is of type that is not allowed.");
  954. }
  955. if ( $form->{file} !~ /^$myconfig->{templates}\// ) {
  956. $form->error("Not in a whitelisted directory: $form->{file}")
  957. unless $form->{file} =~ /^css\//;
  958. }
  959. }
  960. sub load_template {
  961. my ( $self, $myconfig, $form ) = @_;
  962. $self->check_template_name( \%$myconfig, \%$form );
  963. open( TEMPLATE, '<', "$form->{file}" )
  964. or $form->error("$form->{file} : $!");
  965. while (<TEMPLATE>) {
  966. $form->{body} .= $_;
  967. }
  968. close(TEMPLATE);
  969. }
  970. sub save_template {
  971. my ( $self, $myconfig, $form ) = @_;
  972. $self->check_template_name( \%$myconfig, \%$form );
  973. open( TEMPLATE, '>', "$form->{file}" )
  974. or $form->error("$form->{file} : $!");
  975. # strip
  976. $form->{body} =~ s/\r//g;
  977. print TEMPLATE $form->{body};
  978. close(TEMPLATE);
  979. }
  980. sub save_preferences {
  981. my ( $self, $myconfig, $form ) = @_;
  982. # connect to database
  983. my $dbh = $form->{dbh};
  984. # get username, is same as requested?
  985. my @queryargs;
  986. my $query = qq|
  987. SELECT login
  988. FROM employee
  989. WHERE login = ?|;
  990. @queryargs = ( $form->{login} );
  991. my $sth = $dbh->prepare($query);
  992. $sth->execute(@queryargs) || $form->dberror($query);
  993. my ($dbusername) = $sth->fetchrow_array;
  994. $sth->finish;
  995. return 0 if ( $dbusername ne $form->{login} );
  996. # update name
  997. $query = qq|
  998. UPDATE employee
  999. SET name = ?
  1000. WHERE login = ?|;
  1001. @queryargs = ( $form->{name}, $form->{login} );
  1002. $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query);
  1003. # get default currency
  1004. $query = qq|
  1005. SELECT value, (SELECT value FROM defaults
  1006. WHERE setting_key = 'businessnumber')
  1007. FROM defaults
  1008. WHERE setting_key = 'curr'|;
  1009. ( $form->{currency}, $form->{businessnumber} ) =
  1010. $dbh->selectrow_array($query);
  1011. $form->{currency} =~ s/:.*//;
  1012. $dbh->commit;
  1013. my $myconfig = LedgerSMB::User->new( $form->{login} );
  1014. map { $myconfig->{$_} = $form->{$_} if exists $form->{$_} }
  1015. qw(name email dateformat signature numberformat vclimit tel fax
  1016. company menuwidth countrycode address timeout stylesheet
  1017. printer password);
  1018. $myconfig->{password} = $form->{new_password}
  1019. if ( $form->{old_password} ne $form->{new_password} );
  1020. $myconfig->save_member();
  1021. 1;
  1022. }
  1023. sub save_defaults {
  1024. my ( $self, $myconfig, $form ) = @_;
  1025. for (qw(IC IC_income IC_expense FX_gain FX_loss)) {
  1026. ( $form->{$_} ) = split /--/, $form->{$_};
  1027. }
  1028. my @a;
  1029. $form->{curr} =~ s/ //g;
  1030. for ( split /:/, $form->{curr} ) { push( @a, uc pack "A3", $_ ) if $_ }
  1031. $form->{curr} = join ':', @a;
  1032. # connect to database
  1033. my $dbh = $form->{dbh};
  1034. # save defaults
  1035. $sth_plain = $dbh->prepare( "
  1036. UPDATE defaults SET value = ? WHERE setting_key = ?" );
  1037. $sth_accno = $dbh->prepare(
  1038. qq|
  1039. UPDATE defaults
  1040. SET value = (SELECT id
  1041. FROM chart
  1042. WHERE accno = ?)
  1043. WHERE setting_key = ?|
  1044. );
  1045. my %translation = (
  1046. inventory_accno_id => 'IC',
  1047. income_accno_id => 'IC_income',
  1048. expense_accno_id => 'IC_expense',
  1049. fxgain_accno_id => 'FX_gain',
  1050. fxloss_accno_id => 'FX_loss'
  1051. );
  1052. for (
  1053. qw(inventory_accno_id income_accno_id expense_accno_id
  1054. fxgain_accno_id fxloss_accno_id glnumber sinumber vinumber
  1055. sonumber ponumber sqnumber rfqnumber partnumber employeenumber
  1056. customernumber vendornumber projectnumber yearend curr
  1057. weightunit businessnumber)
  1058. )
  1059. {
  1060. my $val = $form->{$_};
  1061. if ( $translation{$_} ) {
  1062. $val = $form->{ $translation{$_} };
  1063. }
  1064. if ( $_ =~ /accno/ ) {
  1065. $sth_accno->execute( $val, $_ )
  1066. || $form->dberror("Saving $_");
  1067. }
  1068. else {
  1069. $sth_plain->execute( $val, $_ )
  1070. || $form->dberror("Saving $_");
  1071. }
  1072. }
  1073. my $rc = $dbh->commit;
  1074. $rc;
  1075. }
  1076. sub defaultaccounts {
  1077. my ( $self, $myconfig, $form ) = @_;
  1078. # connect to database
  1079. my $dbh = $form->{dbh};
  1080. # get defaults from defaults table
  1081. my $query = qq|
  1082. SELECT setting_key, value FROM defaults
  1083. WHERE setting_key LIKE ?|;
  1084. my $sth = $dbh->prepare($query);
  1085. $sth->execute('%accno_id') || $form->dberror($query);
  1086. my $ref;
  1087. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1088. $form->{ $ref->{setting_key} } = $ref->{value};
  1089. }
  1090. $form->{defaults}{IC} = $form->{inventory_accno_id};
  1091. $form->{defaults}{IC_income} = $form->{income_accno_id};
  1092. $form->{defaults}{IC_sale} = $form->{income_accno_id};
  1093. $form->{defaults}{IC_expense} = $form->{expense_accno_id};
  1094. $form->{defaults}{IC_cogs} = $form->{expense_accno_id};
  1095. $form->{defaults}{FX_gain} = $form->{fxgain_accno_id};
  1096. $form->{defaults}{FX_loss} = $form->{fxloss_accno_id};
  1097. $sth->finish;
  1098. $query = qq|
  1099. SELECT id, accno, description, link
  1100. FROM chart
  1101. WHERE link LIKE '%IC%'
  1102. ORDER BY accno|;
  1103. $sth = $dbh->prepare($query);
  1104. $sth->execute || $form->dberror($query);
  1105. my $nkey;
  1106. while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1107. foreach my $key ( split( /:/, $ref->{link} ) ) {
  1108. if ( $key =~ /IC/ ) {
  1109. $nkey = $key;
  1110. if ( $key =~ /cogs/ ) {
  1111. $nkey = "IC_expense";
  1112. }
  1113. if ( $key =~ /sale/ ) {
  1114. $nkey = "IC_income";
  1115. }
  1116. %{ $form->{accno}{$nkey}{ $ref->{accno} } } = (
  1117. id => $ref->{id},
  1118. description => $ref->{description}
  1119. );
  1120. }
  1121. }
  1122. }
  1123. $sth->finish;
  1124. $query = qq|
  1125. SELECT id, accno, description
  1126. FROM chart
  1127. WHERE (category = 'I' OR category = 'E')
  1128. AND charttype = 'A'
  1129. ORDER BY accno|;
  1130. $sth = $dbh->prepare($query);
  1131. $sth->execute || $form->dberror($query);
  1132. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1133. %{ $form->{accno}{FX_gain}{ $ref->{accno} } } = (
  1134. id => $ref->{id},
  1135. description => $ref->{description}
  1136. );
  1137. %{ $form->{accno}{FX_loss}{ $ref->{accno} } } = (
  1138. id => $ref->{id},
  1139. description => $ref->{description}
  1140. );
  1141. }
  1142. $sth->finish;
  1143. $dbh->commit;
  1144. }
  1145. sub taxes {
  1146. my ( $self, $myconfig, $form ) = @_;
  1147. my $taxaccounts = '';
  1148. # connect to database
  1149. my $dbh = $form->{dbh};
  1150. my $query = qq|
  1151. SELECT c.id, c.accno, c.description,
  1152. t.rate * 100 AS rate, t.taxnumber, t.validto,
  1153. t.pass, m.taxmodulename
  1154. FROM chart c
  1155. JOIN tax t ON (c.id = t.chart_id)
  1156. JOIN taxmodule m ON (t.taxmodule_id = m.taxmodule_id)
  1157. ORDER BY 3, 6|;
  1158. my $sth = $dbh->prepare($query);
  1159. $sth->execute || $form->dberror($query);
  1160. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1161. $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
  1162. push @{ $form->{taxrates} }, $ref;
  1163. $taxaccounts .= " " . $ref{accno};
  1164. }
  1165. $sth->finish;
  1166. $query = qq|
  1167. SELECT taxmodule_id, taxmodulename FROM taxmodule
  1168. ORDER BY 2|;
  1169. $sth = $dbh->prepare($query);
  1170. $sth->execute || $form->dberror($query);
  1171. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1172. $form->{ "taxmodule_" . $ref->{taxmodule_id} } = $ref->{taxmodulename};
  1173. }
  1174. $sth->finish;
  1175. $dbh->commit;
  1176. }
  1177. sub save_taxes {
  1178. my ( $self, $myconfig, $form ) = @_;
  1179. # connect to database
  1180. my $dbh = $form->{dbh};
  1181. my $query = qq|DELETE FROM tax|;
  1182. $dbh->do($query) || $form->dberror($query);
  1183. $query = qq|
  1184. INSERT INTO tax (chart_id, rate, taxnumber, validto,
  1185. pass, taxmodule_id)
  1186. VALUES (?, ?, ?, ?, ?, ?)|;
  1187. my $sth = $dbh->prepare($query);
  1188. foreach my $item ( split / /, $form->{taxaccounts} ) {
  1189. my ( $chart_id, $i ) = split /_/, $item;
  1190. my $rate =
  1191. $form->parse_amount( $myconfig, $form->{"taxrate_$i"} ) / 100;
  1192. my $validto = $form->{"validto_$i"};
  1193. $validto = undef if not $validto;
  1194. my @queryargs = (
  1195. $chart_id, $rate, $form->{"taxnumber_$i"},
  1196. $validto, $form->{"pass_$i"}, $form->{"taxmodule_id_$i"}
  1197. );
  1198. $sth->execute(@queryargs) || $form->dberror($query);
  1199. }
  1200. my $rc = $dbh->commit;
  1201. $rc;
  1202. }
  1203. sub backup {
  1204. my ( $self, $myconfig, $form ) = @_;
  1205. my $mail;
  1206. my $err;
  1207. my @t = localtime(time);
  1208. $t[4]++;
  1209. $t[5] += 1900;
  1210. $t[3] = substr( "0$t[3]", -2 );
  1211. $t[4] = substr( "0$t[4]", -2 );
  1212. my $boundary = time;
  1213. my $tmpfile =
  1214. "${LedgerSMB::Sysconfig::backuppath}/$boundary.$globalDBname-$form->{dbversion}-$t[5]$t[4]$t[3].sql";
  1215. $form->{OUT} = "$tmpfile";
  1216. open( OUT, '>', "$form->{OUT}" ) or $form->error("$form->{OUT} : $!");
  1217. # get sequences, functions and triggers
  1218. my $today = scalar localtime;
  1219. # compress backup if gzip defined
  1220. my $suffix = "c";
  1221. if ( $form->{media} eq 'email' ) {
  1222. print OUT
  1223. qx(PGPASSWORD="$myconfig->{dbpasswd}" pg_dump -U $myconfig->{dbuser} -h $myconfig->{dbhost} -Fc -p $myconfig->{dbport} $myconfig->{dbname});
  1224. close OUT;
  1225. use LedgerSMB::Mailer;
  1226. $mail = new Mailer;
  1227. $mail->{to} = qq|"$myconfig->{name}" <$myconfig->{email}>|;
  1228. $mail->{from} = qq|"$myconfig->{name}" <$myconfig->{email}>|;
  1229. $mail->{subject} =
  1230. "LedgerSMB Backup / $globalDBname-$form->{dbversion}-$t[5]$t[4]$t[3].sql$suffix";
  1231. @{ $mail->{attachments} } = ($tmpfile);
  1232. $mail->{version} = $form->{version};
  1233. $mail->{fileid} = "$boundary.";
  1234. $mail->{format} = "plain";
  1235. $mail->{format} = "octet-stream";
  1236. $myconfig->{signature} =~ s/\\n/\n/g;
  1237. $mail->{message} = "-- \n$myconfig->{signature}";
  1238. $err = $mail->send;
  1239. }
  1240. if ( $form->{media} eq 'file' ) {
  1241. open( IN, '<', "$tmpfile" ) or $form->error("$tmpfile : $!");
  1242. open( OUT, ">-" ) or $form->error("STDOUT : $!");
  1243. print OUT qq|Content-Type: application/file;\n|
  1244. . qq|Content-Disposition: attachment; filename="$myconfig->{dbname}-$form->{dbversion}-$t[5]$t[4]$t[3].sql$suffix"\n\n|;
  1245. print OUT
  1246. qx(PGPASSWORD="$myconfig->{dbpasswd}" pg_dump -U $myconfig->{dbuser} -h $myconfig->{dbhost} -Fc -p $myconfig->{dbport} $myconfig->{dbname});
  1247. }
  1248. unlink "$tmpfile";
  1249. }
  1250. sub closedto {
  1251. my ( $self, $myconfig, $form ) = @_;
  1252. my $dbh = $form->{dbh};
  1253. my $query = qq|
  1254. SELECT (SELECT value FROM defaults
  1255. WHERE setting_key = 'closedto'),
  1256. (SELECT value FROM defaults
  1257. WHERE setting_key = 'revtrans'),
  1258. (SELECT value FROM defaults
  1259. WHERE setting_key = 'audittrail')|;
  1260. ( $form->{closedto}, $form->{revtrans}, $form->{audittrail} ) =
  1261. $dbh->selectrow_array($query);
  1262. $dbh->commit;
  1263. }
  1264. sub closebooks {
  1265. my ( $self, $myconfig, $form ) = @_;
  1266. my $dbh = $form->{dbh};
  1267. my $query = qq|
  1268. UPDATE defaults SET value = ?
  1269. WHERE setting_key = ?|;
  1270. my $sth = $dbh->prepare($query);
  1271. my $sth_closedto = $dbh->prepare(qq|
  1272. UPDATE defaults SET value = to_char(?::date, 'YYYY-MM-DD')
  1273. WHERE setting_key = ?|);
  1274. for (qw(revtrans closedto audittrail)) {
  1275. if ( $form->{$_} ) {
  1276. $val = $form->{$_};
  1277. }
  1278. else {
  1279. $val = 0;
  1280. }
  1281. if ($_ eq 'closedto'){
  1282. $sth_closedto->execute( $val || undef, $_);
  1283. } else {
  1284. $sth->execute( $val, $_ );
  1285. }
  1286. }
  1287. if ( $form->{removeaudittrail} ) {
  1288. $query = qq|
  1289. DELETE FROM audittrail
  1290. WHERE transdate < | . $dbh->quote( $form->{removeaudittrail} );
  1291. $dbh->do($query) || $form->dberror($query);
  1292. }
  1293. $dbh->commit;
  1294. }
  1295. sub earningsaccounts {
  1296. my ( $self, $myconfig, $form ) = @_;
  1297. my ( $query, $sth, $ref );
  1298. # connect to database
  1299. my $dbh = $form->{dbh};
  1300. # get chart of accounts
  1301. $query = qq|
  1302. SELECT accno,description
  1303. FROM chart
  1304. WHERE charttype = 'A'
  1305. AND category = 'Q'
  1306. ORDER BY accno|;
  1307. $sth = $dbh->prepare($query);
  1308. $sth->execute || $form->dberror($query);
  1309. $form->{chart} = "";
  1310. while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  1311. push @{ $form->{chart} }, $ref;
  1312. }
  1313. $sth->finish;
  1314. $dbh->commit;
  1315. }
  1316. sub post_yearend {
  1317. my ( $self, $myconfig, $form ) = @_;
  1318. # connect to database, turn off AutoCommit
  1319. my $dbh = $form->{dbh};
  1320. my $query;
  1321. my @queryargs;
  1322. my $uid = localtime;
  1323. $uid .= "$$";
  1324. $query = qq|
  1325. INSERT INTO gl (reference, employee_id)
  1326. VALUES (?, (SELECT id FROM employee
  1327. WHERE login = ?))|;
  1328. $dbh->prepare($query)->execute( $uid, $form->{login} )
  1329. || $form->dberror($query);
  1330. $query = qq|
  1331. SELECT id
  1332. FROM gl
  1333. WHERE reference = ?|;
  1334. my $sth = $dbh->prepare($query);
  1335. $sth->execute($uid);
  1336. ( $form->{id} ) = $sth->fetchrow_array;
  1337. $query = qq|
  1338. UPDATE gl
  1339. SET reference = ?,
  1340. description = ?,
  1341. notes = ?,
  1342. transdate = ?,
  1343. department_id = 0
  1344. WHERE id = ?|;
  1345. @queryargs = (
  1346. $form->{reference}, $form->{description}, $form->{notes},
  1347. $form->{transdate}, $form->{id}
  1348. );
  1349. $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query);
  1350. my $amount;
  1351. my $accno;
  1352. $query = qq|
  1353. INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
  1354. source)
  1355. VALUES (?, (SELECT id
  1356. FROM chart
  1357. WHERE accno = ?),
  1358. ?, ?, ?)|;
  1359. # insert acc_trans transactions
  1360. for my $i ( 1 .. $form->{rowcount} ) {
  1361. # extract accno
  1362. ($accno) = split( /--/, $form->{"accno_$i"} );
  1363. $amount = 0;
  1364. if ( $form->{"credit_$i"} ) {
  1365. $amount = $form->{"credit_$i"};
  1366. }
  1367. if ( $form->{"debit_$i"} ) {
  1368. $amount = $form->{"debit_$i"} * -1;
  1369. }
  1370. # if there is an amount, add the record
  1371. if ($amount) {
  1372. my @args = (
  1373. $form->{id}, $accno, $amount, $form->{transdate},
  1374. $form->{reference}
  1375. );
  1376. $dbh->prepare($query)->execute(@args)
  1377. || $form->dberror($query);
  1378. }
  1379. }
  1380. $query = qq|
  1381. INSERT INTO yearend (trans_id, transdate)
  1382. VALUES (?, ?)|;
  1383. $dbh->prepare($query)->execute( $form->{id}, $form->{transdate} )
  1384. || $form->dberror($query);
  1385. my %audittrail = (
  1386. tablename => 'gl',
  1387. reference => $form->{reference},
  1388. formname => 'yearend',
  1389. action => 'posted',
  1390. id => $form->{id}
  1391. );
  1392. $form->audittrail( $dbh, "", \%audittrail );
  1393. # commit and redirect
  1394. my $rc = $dbh->commit;
  1395. $rc;
  1396. }
  1397. sub get_all_defaults {
  1398. my ( $self, $form ) = @_;
  1399. my $dbh = $form->{dbh};
  1400. my $query = "select setting_key, value FROM defaults";
  1401. $sth = $dbh->prepare($query);
  1402. $sth->execute;
  1403. while ( ( $skey, $value ) = $sth->fetchrow_array() ) {
  1404. $form->{$skey} = $value;
  1405. }
  1406. $self->defaultaccounts( undef, $form );
  1407. $dbh->commit;
  1408. }
  1409. 1;