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