summaryrefslogtreecommitdiff
path: root/LedgerSMB/RC.pm
blob: 36488e37b5b78831ef3978ad4e17dc51e5eab2a7 (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) 2002
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors:
  23. #
  24. #======================================================================
  25. #
  26. # This file has undergone whitespace cleanup.
  27. #
  28. #======================================================================
  29. #
  30. # Account reconciliation routines
  31. #
  32. #======================================================================
  33. package RC;
  34. sub getposlines{
  35. my ($self, $myconfig, $form) = @_;
  36. %pos_config = %{$form->{pos_config}};
  37. %pos_sources = %{$form->{pos_sources}};
  38. my $sources = '';
  39. foreach $key (keys %pos_sources){
  40. $sources .= ", '$key'";
  41. }
  42. $sources =~ s/^,\s*//;
  43. my $dbh = $form->{dbh};
  44. my $query = qq|
  45. SELECT sum(amount) AS amount, source FROM acc_trans
  46. WHERE chart_id = (SELECT id FROM chart
  47. WHERE accno = ?)
  48. AND transdate = date 'NOW' AND cleared IS NOT TRUE
  49. GROUP BY source|;
  50. my $sth = $dbh->prepare($query);
  51. $sth->execute($pos_config{till_accno}) || $form->dberror($query);
  52. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  53. push @{$form->{TB}}, $ref;
  54. }
  55. $sth->finish;
  56. my $query = qq|
  57. SELECT sum(amount) AS sum FROM acc_trans
  58. WHERE chart_id = (SELECT id FROM chart WHERE accno = ?)
  59. AND transdate = date 'NOW'
  60. AND cleared IS NOT TRUE|;
  61. my $sth = $dbh->prepare($query);
  62. $sth->execute($pos_config{till_accno}) || $form->dberror($query);
  63. my $ref = $sth->fetchrow_hashref(NAME_lc);
  64. $form->{sum} = $ref->{sum};
  65. $sth->finish;
  66. }
  67. sub clear_till {
  68. my ($self, $myconfig, $form) = @_;
  69. %pos_config = %{$form->{pos_config}};
  70. %pos_sources = %{$form->{pos_sources}};
  71. my $sources = '';
  72. foreach $key (keys %pos_sources){
  73. $sources .= ", '$key'";
  74. }
  75. $sources =~ s/^,\s//;
  76. my $dbh = $form->{dbh};
  77. my $query = qq|
  78. UPDATE acc_trans
  79. SET cleared = TRUE
  80. WHERE chart_id =
  81. (SELECT id FROM chart WHERE accno = ?)
  82. AND transdate = date 'NOW'|;
  83. my $sth = $dbh->prepare($query);
  84. $sth->execute($pos_config{till_accno}) || $form->dberror($query);
  85. }
  86. sub getbalance{
  87. my ($self, $myconfig, $form) = @_;
  88. my $dbh = $form->{dbh};
  89. my $query = qq|
  90. SELECT sum(amount) AS balance
  91. FROM acc_trans
  92. WHERE chart_id = (SELECT id FROM chart WHERE accno = ?)|;
  93. my $sth = $dbh->prepare($query);
  94. $sth->execute($form->{accno}) || $form->dberror($query);
  95. my $ref = $sth->fetchrow_hashref(NAME_lc);
  96. $form->{balance} = $ref->{balance};
  97. }
  98. sub paymentaccounts {
  99. my ($self, $myconfig, $form) = @_;
  100. my $dbh = $form->{dbh};
  101. my $query = qq|
  102. SELECT accno, description
  103. FROM chart
  104. WHERE link LIKE '%_paid%'
  105. AND (category = 'A' OR category = 'L')
  106. ORDER BY accno|;
  107. my $sth = $dbh->prepare($query);
  108. $sth->execute || $form->dberror($query);
  109. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  110. push @{ $form->{PR} }, $ref;
  111. }
  112. $sth->finish;
  113. $form->all_years($myconfig, $dbh);
  114. }
  115. sub payment_transactions {
  116. my ($self, $myconfig, $form) = @_;
  117. my $dbh = $form->{dbh};
  118. my $query;
  119. my $sth;
  120. $query = qq|SELECT category FROM chart WHERE accno = ?|;
  121. $sth = $dbh->prepare($query);
  122. $sth->execute($form->{accno});
  123. ($form->{category}) = $sth->fetchrow_array();
  124. my $cleared;
  125. ($form->{fromdate}, $form->{todate})
  126. = $form->from_to(
  127. $form->{year}, $form->{month}, $form->{interval})
  128. if $form->{year} && $form->{month};
  129. my $transdate = qq| AND ac.transdate < date |.
  130. $dbh->quote($form->{fromdate});
  131. if (! $form->{fromdate}) {
  132. $cleared = qq| AND ac.cleared = '1'|;
  133. $transdate = "";
  134. }
  135. # get beginning balance
  136. $query = qq|
  137. SELECT sum(ac.amount)
  138. FROM acc_trans ac
  139. JOIN chart ch ON (ch.id = ac.chart_id)
  140. WHERE ch.accno = ? $transdate $cleared |;
  141. $sth = $dbh->prepare($query);
  142. $sth->execute($form->{accno});
  143. ($form->{beginningbalance}) = $sth->fetchrow_array();
  144. $query = qq|
  145. SELECT sum(ac.amount)
  146. FROM acc_trans ac
  147. JOIN chart ch ON (ch.id = ac.chart_id)
  148. WHERE ch.accno = ? AND ac.fx_transaction = '1'
  149. $transdate $cleared|;
  150. $sth = $dbh->prepare($query);
  151. $sth->execute($form->{accno});
  152. ($form->{fx_balance}) = $sth->fetchrow_array();
  153. $transdate = "";
  154. if ($form->{todate}) {
  155. $transdate = qq| AND ac.transdate <= date |.
  156. $dbh->quote($form->{todate});
  157. }
  158. # get statement balance
  159. $query = qq|
  160. SELECT sum(ac.amount)
  161. FROM acc_trans ac
  162. JOIN chart ch ON (ch.id = ac.chart_id)
  163. WHERE ch.accno = ? $transdate|;
  164. $sth = $dbh->prepare($query);
  165. $sth->execute($form->{accno});
  166. ($form->{endingbalance}) = $sth->fetchrow_array();
  167. # fx balance
  168. $query = qq|
  169. SELECT sum(ac.amount)
  170. FROM acc_trans ac
  171. JOIN chart ch ON (ch.id = ac.chart_id)
  172. WHERE ch.accno = ? AND ac.fx_transaction = '1' $transdate |;
  173. $sth = $dbh->prepare($query);
  174. $sth->execute($form->{accno});
  175. ($form->{fx_endingbalance}) = $sth->fetchrow_array();
  176. $cleared = qq| AND ac.cleared = '0'| unless $form->{fromdate};
  177. if ($form->{report}) {
  178. $cleared = qq| AND NOT (ac.cleared = '0' OR ac.cleared = '1')|;
  179. if ($form->{cleared}) {
  180. $cleared = qq| AND ac.cleared = '1'|;
  181. }
  182. if ($form->{outstanding}) {
  183. $cleared =
  184. ($form->{cleared})
  185. ? ""
  186. : qq| AND ac.cleared = '0'|;
  187. }
  188. if (! $form->{fromdate}) {
  189. $form->{beginningbalance} = 0;
  190. $form->{fx_balance} = 0;
  191. }
  192. }
  193. my $fx_transaction;
  194. if ($form->{fx_transaction}) {
  195. $fx_transaction = qq|
  196. AND NOT (ac.chart_id IN
  197. (SELECT value AS fxgain_accno_id FROM defaults
  198. WHERE setting_key = 'fxgain_accno_id'
  199. UNION
  200. SELECT value AS fxloss_accno_id FROM defaults
  201. WHERE setting_key = 'fxloss_accno_id))|;
  202. } else {
  203. $fx_transaction = qq|
  204. AND ac.fx_transaction = '0'|;
  205. }
  206. if ($form->{summary}) {
  207. $query = qq|
  208. SELECT ac.transdate, ac.source,
  209. sum(ac.amount) AS amount, ac.cleared
  210. FROM acc_trans ac
  211. JOIN chart ch ON (ac.chart_id = ch.id)
  212. WHERE ch.accno = |.$dbh->quote($form->{accno}).qq|
  213. AND ac.amount >= 0 $fx_transaction $cleared|;
  214. $query .= " AND ac.transdate >= ".$dbh->quote($form->{fromdate})
  215. if $form->{fromdate};
  216. $query .= " AND ac.transdate <= ".$dbh->quote($form->{todate})
  217. if $form->{todate};
  218. $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";
  219. $query .= qq|
  220. UNION ALL
  221. SELECT ac.transdate, ac.source,
  222. sum(ac.amount) AS amount, ac.cleared
  223. FROM acc_trans ac
  224. JOIN chart ch ON (ac.chart_id = ch.id)
  225. WHERE ch.accno = |.$dbh->quote($form->{accno}).qq|
  226. AND ac.amount < 0 $fx_transaction $cleared|;
  227. $query .= " AND ac.transdate >= ".$dbh->quote($form->{fromdate})
  228. if $form->{fromdate};
  229. $query .= " AND ac.transdate <= ".$dbh->quote($form->{todate})
  230. if $form->{todate};
  231. $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";
  232. $query .= " ORDER BY 1,2";
  233. } else {
  234. $query = qq|
  235. SELECT ac.transdate, ac.source, ac.fx_transaction,
  236. ac.amount, ac.cleared, g.id, g.description
  237. FROM acc_trans ac
  238. JOIN chart ch ON (ac.chart_id = ch.id)
  239. JOIN gl g ON (g.id = ac.trans_id)
  240. WHERE ch.accno = |.$dbh->quote($form->{accno}).qq|
  241. $fx_transaction $cleared|;
  242. $query .= " AND ac.transdate >= ".$dbh->quote($form->{fromdate})
  243. if $form->{fromdate};
  244. $query .= " AND ac.transdate <= ".$dbh->quote($form->{todate})
  245. if $form->{todate};
  246. $query .= qq|
  247. UNION ALL
  248. SELECT ac.transdate, ac.source, ac.fx_transaction,
  249. ac.amount, ac.cleared, a.id, n.name
  250. FROM acc_trans ac
  251. JOIN chart ch ON (ac.chart_id = ch.id)
  252. JOIN ar a ON (a.id = ac.trans_id)
  253. JOIN customer n ON (n.id = a.customer_id)
  254. WHERE ch.accno = |.$dbh->quote($form->{accno}).qq|
  255. $fx_transaction $cleared|;
  256. $query .= " AND ac.transdate >= ".$dbh->quote($form->{fromdate})
  257. if $form->{fromdate};
  258. $query .= " AND ac.transdate <= ".$dbh->quote($form->{todate})
  259. if $form->{todate};
  260. $query .= qq|
  261. UNION ALL
  262. SELECT ac.transdate, ac.source, ac.fx_transaction,
  263. ac.amount, ac.cleared, a.id, n.name
  264. FROM acc_trans ac
  265. JOIN chart ch ON (ac.chart_id = ch.id)
  266. JOIN ap a ON (a.id = ac.trans_id)
  267. JOIN vendor n ON (n.id = a.vendor_id)
  268. WHERE ch.accno = |.$dbh->quote($form->{accno}).qq|
  269. $fx_transaction $cleared|;
  270. $query .= " AND ac.transdate >= ".$dbh->quote($form->{fromdate})
  271. if $form->{fromdate};
  272. $query .= " AND ac.transdate <= ".$dbh->quote($form->{todate})
  273. if $form->{todate};
  274. $query .= " ORDER BY 1,2,3";
  275. }
  276. $sth = $dbh->prepare($query);
  277. $sth->execute || $form->dberror($query);
  278. my $dr;
  279. my $cr;
  280. if ($form->{summary}) {
  281. $query = qq|
  282. SELECT c.name
  283. FROM customer c
  284. JOIN ar a ON (c.id = a.customer_id)
  285. JOIN acc_trans ac ON (a.id = ac.trans_id)
  286. JOIN chart ch ON (ac.chart_id = ch.id)
  287. WHERE ac.transdate = ?
  288. AND ch.accno = |.$dbh->quote($form->{accno}).qq|
  289. AND (ac.source = ? OR ac.source IS NULL)
  290. AND ac.amount >= 0 $cleared
  291. UNION
  292. SELECT v.name
  293. FROM vendor v
  294. JOIN ap a ON (v.id = a.vendor_id)
  295. JOIN acc_trans ac ON (a.id = ac.trans_id)
  296. JOIN chart ch ON (ac.chart_id = ch.id)
  297. WHERE ac.transdate = ?
  298. AND ch.accno = |.$dbh->quote($form->{accno}).qq|
  299. AND (ac.source = ? OR ac.source IS NULL)
  300. AND ac.amount > 0 $cleared
  301. UNION
  302. SELECT g.description
  303. FROM gl g
  304. JOIN acc_trans ac ON (g.id = ac.trans_id)
  305. JOIN chart ch ON (ac.chart_id = ch.id)
  306. WHERE ac.transdate = ?
  307. AND ch.accno = |.$dbh->quote($form->{accno}).qq|
  308. AND (ac.source = ? OR ac.source IS NULL)
  309. AND ac.amount >= 0 $cleared|;
  310. $query .= " ORDER BY 1";
  311. $dr = $dbh->prepare($query);
  312. $query = qq|
  313. SELECT c.name
  314. FROM customer c
  315. JOIN ar a ON (c.id = a.customer_id)
  316. JOIN acc_trans ac ON (a.id = ac.trans_id)
  317. JOIN chart ch ON (ac.chart_id = ch.id)
  318. WHERE ac.transdate = ?
  319. AND ch.accno = |.$dbh->quote($form->{accno}).qq|
  320. AND (ac.source = ? OR ac.source IS NULL)
  321. AND ac.amount < 0 $cleared
  322. UNION
  323. SELECT v.name
  324. FROM vendor v
  325. JOIN ap a ON (v.id = a.vendor_id)
  326. JOIN acc_trans ac ON (a.id = ac.trans_id)
  327. JOIN chart ch ON (ac.chart_id = ch.id)
  328. WHERE ac.transdate = ?
  329. AND ch.accno = |.$dbh->quote($form->{accno}).qq|
  330. AND (ac.source = ? OR ac.source IS NULL)
  331. AND ac.amount < 0 $cleared
  332. UNION
  333. SELECT g.description
  334. FROM gl g
  335. JOIN acc_trans ac ON (g.id = ac.trans_id)
  336. JOIN chart ch ON (ac.chart_id = ch.id)
  337. WHERE ac.transdate = ?
  338. AND ch.accno = |.$dbh->quote($form->{accno}).qq|
  339. AND (ac.source = ? OR ac.source IS NULL)
  340. AND ac.amount < 0 $cleared|;
  341. $query .= " ORDER BY 1";
  342. $cr = $dbh->prepare($query);
  343. }
  344. my $name;
  345. my $ref;
  346. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  347. if ($form->{summary}) {
  348. if ($ref->{amount} > 0) {
  349. $dr->execute(
  350. $ref->{transdate}, $ref->{source},
  351. $ref->{transdate}, $ref->{source},
  352. $ref->{transdate}, $ref->{source});
  353. $ref->{oldcleared} = $ref->{cleared};
  354. $ref->{name} = ();
  355. while (($name) = $dr->fetchrow_array) {
  356. push @{ $ref->{name} }, $name;
  357. }
  358. $dr->finish;
  359. } else {
  360. $cr->execute(
  361. $ref->{transdate}, $ref->{source},
  362. $ref->{transdate}, $ref->{source},
  363. $ref->{transdate}, $ref->{source});
  364. $ref->{oldcleared} = $ref->{cleared};
  365. $ref->{name} = ();
  366. while (($name) = $cr->fetchrow_array) {
  367. push @{ $ref->{name} }, $name;
  368. }
  369. $cr->finish;
  370. }
  371. } else {
  372. push @{ $ref->{name} }, $ref->{description};
  373. }
  374. push @{ $form->{PR} }, $ref;
  375. }
  376. $sth->finish;
  377. $dbh->commit;
  378. }
  379. sub reconcile {
  380. my ($self, $myconfig, $form) = @_;
  381. my $dbh = $form->dbconnect($myconfig);
  382. my $query = qq|SELECT id FROM chart
  383. WHERE accno = '$form->{accno}'|;
  384. my ($chart_id) = $dbh->selectrow_array($query);
  385. $chart_id *= 1;
  386. $query = qq|
  387. SELECT trans_id FROM acc_trans
  388. WHERE (source = ? OR source IS NULL) AND transdate = ?
  389. AND cleared = '0'
  390. AND chart_id = |.$dbh->quote($chart_id);
  391. my $sth = $dbh->prepare($query) || $form->dberror($query);
  392. my $i;
  393. my $trans_id;
  394. $query = qq|
  395. UPDATE acc_trans
  396. SET cleared = '1'
  397. WHERE cleared = '0' AND trans_id = ? AND transdate = ?
  398. AND chart_id = |.$dbh->quote($chart_id);
  399. my $tth = $dbh->prepare($query) || $form->dberror($query);
  400. # clear flags
  401. for $i (1 .. $form->{rowcount}) {
  402. if ($form->{"cleared_$i"} && ! $form->{"oldcleared_$i"}) {
  403. if ($form->{summary}) {
  404. $sth->execute(
  405. $form->{"source_$i"},
  406. $form->{"transdate_$i"}
  407. ) || $form->dberror;
  408. while (($trans_id) = $sth->fetchrow_array) {
  409. $tth->execute(
  410. $trans_id,
  411. $form->{"transdate_$i"}
  412. ) || $form->dberror;
  413. $tth->finish;
  414. }
  415. $sth->finish;
  416. } else {
  417. $tth->execute(
  418. $form->{"id_$i"},
  419. $form->{"transdate_$i"}
  420. ) || $form->dberror;
  421. $tth->finish;
  422. }
  423. }
  424. }
  425. $dbh->commit;
  426. }
  427. 1;