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