1 /*jshint trailing:true, white:true, indent:2, strict:true, curly:true,
2 immed:true, eqeqeq:true, forin:true, latedef:true,
3 newcap:true, noarg:true, undef:true */
4 /*global XT:true, describe:true, it:true, require:true, __dirname:true, before:true */
6 var _ = require("underscore"),
7 assert = require('chai').assert,
8 path = require('path');
13 describe('test bank reconciliation functions', function () {
15 var loginData = require("../lib/login_data.js").data,
16 datasource = require('../../../xtuple/node-datasource/lib/ext/datasource').dataSource,
17 config = require(path.join(__dirname, "../../node-datasource/config.js")),
18 creds = _.extend({}, config.databaseServer, {database: loginData.org}),
21 trans1 = { amount: 98.76 },
22 trans2 = { amount: 54.32 },
23 bankRecItemSql = 'SELECT * FROM bankrecitem ' +
24 ' WHERE bankrecitem_bankrec_id=:brid' +
25 ' AND bankrecitem_source_id=:srcid' +
26 ' AND bankrecitem_source=:src;',
27 toggleCheckSql = "SELECT toggleBankRecCleared(:bankrec_id,'A/R'" +
28 ", gltrans_id, checkhead_curr_rate, checkhead_amount)" +
30 " FROM checkhead JOIN gltrans ON (gltrans_doctype='CK'" +
31 " AND gltrans_misc_id=checkhead_id)" +
32 " WHERE checkhead_id=:checkid;",
33 checkCheckSql = "SELECT *,"
34 " bankrecitem_amount/bankrecitem_curr_rate AS base"+
35 " JOIN bankrecitem ON (gltrans_id=bankrecitem_source_id)"+
36 " JOIN bankrec ON (bankrecitem_bankrec_id=bankrec_id)"+
37 " WHERE gltrans_doctype='CK'" +
38 " AND gltrans_misc_id=:checkid" +
39 " AND bankrec_id=:bankrec_id;",
40 bankAdjCheckSql = "SELECT *,"
41 " ABS(bankadj_amount / bankadj_curr_rate) AS baseamt" +
42 " FROM bankadj LEFT OUTER" +
43 " JOIN bankaccnt ON (bankadj_bankaccnt_id=bankaccnt_id)" +
45 " JOIN gltrans ON (bankaccnt_accnt_id=gltrans_accnt_id" +
46 " AND gltrans_doctype='AD'" +
47 " AND gltrans_notes ~ bankadj_notes" +
48 " AND gltrans_docnumber=bankadj_docnumber)"+
50 " JOIN bankrecitem ON (bankrec_source='G/L' " +
51 " AND bankrec_source_id=gltrans_id)" +
52 " WHERE bankadj_id=:bankadjid;" // TODO: precise enough?
55 it('looks for a bank account with no open reconciliations', function (done) {
56 var sql = 'SELECT * FROM bankaccnt WHERE bankaccnt_id IN (' +
57 ' SELECT bankrec_bankaccnt_id FROM bankrec' +
58 ' GROUP BY bankrec_bankaccnt_id' +
59 ' HAVING BOOL_AND(bankrec_posted)) LIMIT 1;'
61 datasource.query(sql, creds, function (err, res) {
62 assert.equal(res.rowCount, 1);
63 bankaccnt = _.clone(res.rows[0]);
64 assert.isNotNull(bankaccnt, 'we found a bank account');
69 it('creates a new open bankrec to test with', function (done) {
70 var sql = 'INSERT INTO bankrec (bankrec_bankaccnt_id,' +
71 ' bankrec_opendate, bankrec_openbal' +
72 ') SELECT bankrec_bankaccnt_id,' +
73 ' bankrec_enddate + 1, bankrec_endbal' +
75 ' WHERE bankrec_bankaccnt_id=' + bankaccnt.bankaccnt_id +
76 ' AND bankrec_posted' +
77 ' ORDER BY bankrec_enddate DESC' +
78 ' LIMIT 1 RETURNING *;'
80 datasource.query(sql, creds, function (err, res) {
81 assert.equal(res.rowCount, 1);
82 bankrec = _.clone(res.rows[0]);
83 assert.isNotNull(bankrec, 'we have a bank rec');
84 assert.isFalse(bankrec.bankrec_posted, 'we have an open bank rec');
89 it('creates a check as the 1st transaction to reconcile', function (done) {
90 var sql = "SELECT createCheck(" + bankaccnt.bankaccnt_id +
91 "'V', (SELECT MIN(vend_id) FROM vendinfo), '" +
92 bankrec.bankrec_opendate + "' + 1, " + trans1.amount + ", " +
93 bankaccnt.bankaccnt_curr_id + ", NULL, NULL, 'Bearer'," +
94 ", 'bankrec test 1', TRUE, NULL) RETURNING checkid;"
96 datasource.query(sql, cred, function (err, res) {
97 assert.equal(res.rowCount, 1);
98 assert(res.row[0].checkid > 0);
99 trans1.checkid = res.row[0].checkid;
104 it('posts the check', function (done) {
105 var sql = 'SELECT postCheck(' + trans1.checkid + ', NULL) AS result;';
106 datasource.query(sql, cred, function (err, res) {
107 assert.equal(res.rowCount, 1);
108 assert(res.row[0].result > 0);
109 trans1.journalNumber = res.row[0].result;
114 it('marks the check as cleared', function (done) {
115 var sql = _.clone(toggleCheckSql)
116 .replace(':bankrec_id', bankrec.bankrec_id)
117 .replace(':checkid', trans1.checkid);
118 datasource.query(sql, cred, function (err, res) {
119 assert.equal(res.rowCount, 1);
120 assert.isTrue(res.row[0].result);
125 it('confirms the check was marked as cleared', function (done) {
126 var sql = _.clone(bankRecItemSql)
127 .replace(':brid', bankrec.bankrec_id)
128 .replace(':src', 'A/R')
130 " IN (SELECT gltrans_id FROM gltrans WHERE" +
131 " gltrans_doctype='CK' AND gltrans_misc_id=" +
134 datasource.query(sql, cred, function (err, res) {
135 assert.equal(res.rowCount, 1);
136 assert.isTrue(res.row[0].bankrecitem_cleared);
137 assert(res.row[0].bankrecitem_cleared);
142 it('marks the check as /not/ cleared', function (done) {
143 var sql = _.clone(toggleCheckSql)
144 .replace(':bankrec_id', bankrec.bankrec_id)
145 .replace(':checkid', trans1.checkid);
146 datasource.query(sql, cred, function (err, res) {
147 assert.equal(res.rowCount, 1);
148 assert.isFalse(res.row[0].result);
153 it('confirms the check is no longer marked as cleared', function (done) {
154 var sql = _.clone(bankRecItemSql)
155 .replace(':brid', bankrec.bankrec_id)
156 .replace(':src', 'A/R')
158 " IN (SELECT gltrans_id FROM gltrans WHERE" +
159 " gltrans_doctype='CK' AND gltrans_misc_id=" +
162 datasource.query(sql, cred, function (err, res) {
163 assert.equal(res.rowCount, 0);
168 it('marks the check as cleared again', function (done) {
169 var sql = _.clone(toggleCheckSql)
170 .replace(':bankrec_id', bankrec.bankrec_id)
171 .replace(':checkid', trans1.checkid)
173 datasource.query(sql, cred, function (err, res) {
174 assert.equal(res.rowCount, 1);
175 assert.isTrue(res.row[0].result);
180 it('confirms that the check is marked as cleared again', function (done) {
181 var sql = bankRecItemSql.replace(':brid', bankrec.bankrec_id)
182 .replace(':src', 'A/R')
184 " IN (SELECT gltrans_id FROM gltrans" +
185 " WHERE gltrans_doctype='CK' AND "
186 " gltrans_misc_id=" + trans1.checkid)
188 datasource.query(sql, cred, function (err, res) {
189 assert.equal(res.rowCount, 1);
190 assert.isTrue(res.row[0].bankrecitem_cleared);
191 assert(res.row[0].bankrecitem_cleared);
196 it('creates a bank adjustment as a second transaction', function (done) {
197 var sql = "INSERT INTO bankadj (" +
198 " bankadj_bankaccnt_id, bankadj_bankadjtype_id," +
199 " bankadj_date, bankadj_docnumber," +
200 " bankadj_amount, bankadj_notes, bankadj_curr_id" +
201 ") SELECT " + bankaccnt.bankaccnt_id + ", bankadjtype_id, '" +
202 bankrec.bankrec_opendate + "' + 1, 'BankRecTest', " +
203 trans2.amount + ", 'Bank Rec Test Transaction 2', " +
204 bankaccnt.bankaccnt_curr_id +
205 " FROM bankadjtype RETURNING *;"
207 datasource.query(sql, cred, function (err, res) {
208 assert.equal(res.rowCount, 1);
209 _.extend(trans2, res.rows[0]);
210 assert.isOk(trans2.bankadj_id, 'we have a bank adjustment');
215 it('posts the reconciliation', function (done) {
216 var sql = 'SELECT postBankReconciliation(' + bankrec.bankrec_id +
219 datasource.query(sql, cred, function (err, res) {
220 assert.equal(res.rowCount, 1);
221 assert.equal(res.row[0].result, bankrec.bankrec_id);
226 it('confirms the check was reconciled properly', function (done) {
227 var sql = _.clone(checkCheckSql)
228 .replace(':checkid', trans1.checkid)
229 .replace(':bankrecid', bankrec.bankrec_id)
231 datasource.query(sql, cred, function (err, res) {
232 assert.equal(res.rowCount, 1);
233 assert.isTrue(res.row[0].gltrans_rec);
234 assert.isTrue(res.row[0].bankrec_posted);
239 // TODO: confirm the CashBasedTax reconciliation code worked
241 it('confirms the bank adjustment was /not/ posted', function (done) {
242 sql = _.clone(bankAdjCheckSql).replace(':bankadjid', trans2.bankadj_id);
243 datasource.query(sql, cred, function (err, res) {
244 assert.equal(res.rowCount, 1);
245 assert.isFalse(res.row[0].bankadj_posted);
246 assert.notOk(res.row[0].gltrans_id);
247 assert.notOk(res.row[0].bankrecitem_id);
252 it('reopens the reconcilation', function (done) {
253 var sql = 'SELECT reopenBankReconciliation(' + bankrec.bankrec_id +
256 datasource.query(sql, cred, function (err, res) {
257 assert.equal(res.rowCount, 1);
258 assert.equal(res.row[0].result, bankrec.bankrec_id);
263 it('confirms the check was handled properly by the reopen', function (done) {
264 var sql = _.clone(checkCheckSql)
265 .replace(':checkid', trans1.checkid)
266 .replace(':bankrecid', bankrec.bankrec_id)
268 datasource.query(sql, cred, function (err, res) {
269 assert.equal(res.rowCount, 1);
270 assert.isFalse(res.row[0].gltrans_rec);
271 assert.isFalse(res.row[0].bankrec_posted);
272 assert.closeTo(Math.abs(res.row[0].gltrans_amount), res.row[0].base,
278 // TODO: confirm the CashBasedTax reconciliation code worked
280 it('marks the bank adjustment as cleared', function (done) {
281 var sql = "SELECT toggleBankRecCleared(" + bankrec.bankrec_id +
282 ", 'G/L', gltrans_id, bankadj_curr_rate," +
283 " bankadj_amount) AS result" +
284 " FROM bankadj JOIN gltrans" +
285 " ON (gltrans_doctype='AD' AND gltrans_misc_id=bankadj_id)" +
286 " WHERE bankadj_id=" + trans2.bankadj_id + ";"
288 datasource.query(sql, cred, function (err, res) {
289 assert.equal(res.rowCount, 1);
290 assert.isTrue(res.row[0].result);
295 it('confirms the bank adjustment was /not/ posted but is cleared', function (done) {
296 sql = _.clone(bankAdjCheckSql).replace(':bankadjid', trans2.bankadj_id);
297 datasource.query(sql, cred, function (err, res) {
298 assert.equal(res.rowCount, 1);
299 assert.isFalse(res.row[0].bankadj_posted);
300 assert(res.row[0].bankrecitem_id >= 0);
305 it('posts the reconciliation again', function (done) {
306 var sql = 'SELECT postBankReconciliation(' + bankrec.bankrec_id +
309 datasource.query(sql, cred, function (err, res) {
310 assert.equal(res.rowCount, 1);
311 assert.equal(res.row[0].result, bankrec.bankrec_id);
316 it('confirms the check was reconciled properly', function (done) {
317 var sql = _.clone(checkCheckSql)
318 .replace(':checkid', trans1.checkid)
319 .replace(':bankrecid', bankrec.bankrec_id)
321 datasource.query(sql, cred, function (err, res) {
322 assert.equal(res.rowCount, 1);
323 assert.isTrue(res.row[0].gltrans_rec);
324 assert.isTrue(res.row[0].bankrec_posted);
329 // TODO: confirm the CashBasedTax reconciliation code worked
331 it('confirms the bank adjustment was cleared, posted, written to the GL', function (done) {
332 sql = _.clone(bankAdjCheckSql).replace(':bankadjid', trans2.bankadj_id);
333 datasource.query(sql, cred, function (err, res) {
334 assert.equal(res.rowCount, 1);
335 assert.isTrue(res.row[0].bankadj_posted);
336 assert(res.row[0].bankadj_sequence >= 0);
337 assert.isTrue(res.row[0].gltrans_rec);
338 assert.closeTo(Math.abs(res.row[0].gltrans_amount), res.row[0].baseamt,
344 it('deletes the bankrec', function (done) {
345 var sql = 'SELECT deleteBankReconciliation(' + bankrec.bankrec_id +
347 datasource.query(sql, cred, function (err, res) {
348 assert.equal(res.rowCount, 1);
349 assert(res.row[0].result >= 0);
354 it('checks that the bankrec is really gone', function (done) {
355 var sql = 'SELECT COUNT(*) FROM bankrec WHERE bankrec_id = ' +
357 datasource.query(sql, cred, function (err, res) {
358 assert.equal(res.rowCount, 1);
359 assert(res.row[0].result === 0);
364 it('checks that the bankrecitems are gone', function (done) {
365 var sql = 'SELECT COUNT(*) FROM bankrecitem'
366 ' WHERE bankrecitem_bankrec_id = ' + bankrec.bankrec_id;
367 datasource.query(sql, cred, function (err, res) {
368 assert.equal(res.rowCount, 1);
369 assert(res.row[0].result === 0);
374 it('tries to delete a non-existent bankrec', function (done) {
375 var sql = 'SELECT deleteBankReconciliation(-15) AS result;';
376 datasource.query(sql, cred, function (err, res) {
377 assert.equal(res.rowCount, 1);
378 assert(res.row[0].result === 0); // no, it doesn't complain