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