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