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, console:true */
6 /* note: much of this test consists of set up for testing tax handling */
8 // TODO: add cash receipt
9 // TODO: add use of sltrans as well as gltrans
10 var _ = require("underscore"),
11 assert = require('chai').assert,
12 path = require('path');
17 // TODO: implement a real metasql parser; this one is stupid and minimal
18 var mqlToSql = function (query, params) {
19 var result = _.clone(query);
20 _.each(params, function (value, key) {
21 var valueRE = new RegExp("<\\? *value\\(['\"]" + key + "['\"]\\) *\\?>", "g"),
22 literalRE = new RegExp("<\\? *literal\\(['\"]" + key + "['\"]\\) *\\?>", "g");
23 if (_.isNumber(value)) {
24 result = result.replace(valueRE, value);
26 result = result.replace(valueRE, "'" + value + "'");
28 result = result.replace(literalRE, value);
33 describe('test bank reconciliation functions', function () {
35 var loginData = require("../lib/login_data.js").data,
36 datasource = require('../../../xtuple/node-datasource/lib/ext/datasource').dataSource,
37 config = require(path.join(__dirname, "../../node-datasource/config.js")),
38 creds = _.extend({}, config.databaseServer, {database: loginData.org}),
40 testTag = 'bankrec test ' + start.toLocaleTimeString(),
43 bankadj = { amount: 54.32 },
48 aropen = { amount: 12.34 },
57 vomisc = { amount: 67.89 },
62 bankRecItemSql = 'SELECT * FROM bankrecitem ' +
63 ' WHERE bankrecitem_bankrec_id=<? value("brid") ?>' +
64 ' AND bankrecitem_source=<? value("src") ?>' +
65 ' AND bankrecitem_source_id <? literal("srcid") ?>;',
66 toggleCheckSql = "SELECT toggleBankRecCleared(<? value('bankrecid') ?>,'GL'," +
67 " gltrans_id, checkhead_curr_rate, checkhead_amount)" +
69 " FROM checkhead JOIN gltrans ON (gltrans_doctype='CK'" +
70 " AND gltrans_misc_id=checkhead_id)" +
71 " WHERE checkhead_id=<? value('checkid') ?>" +
72 " AND gltrans_amount > 0;",
73 postCheckSql = "SELECT postCheck(<? value('id') ?>, NULL) AS result;",
74 checkCheckSql = "SELECT *," +
75 " bankrecitem_amount/bankrecitem_curr_rate AS base" +
77 " JOIN bankrecitem ON (gltrans_id=bankrecitem_source_id)" +
78 " JOIN bankrec ON (bankrecitem_bankrec_id=bankrec_id)" +
79 " WHERE gltrans_doctype='CK'" +
80 " AND gltrans_misc_id=<? value('checkid') ?>" +
81 " AND bankrec_id=<? value('bankrecid') ?>;",
82 bankAdjCheckSql = "SELECT *," +
83 " ROUND(bankadj_amount / bankadj_curr_rate, 2) AS baseamt," +
84 " CASE WHEN gltrans_id IS NULL AND bankrecitem_id IS NULL" +
86 " WHEN gltrans_id IS NULL OR bankrecitem_id IS NULL" +
88 " ELSE 2 END AS preferred" +
89 " FROM bankadj LEFT OUTER" +
90 " JOIN gltrans ON (gltrans_doctype='AD'" +
91 " AND gltrans_misc_id=bankadj_id)" +
93 " JOIN bankrecitem ON (bankrecitem_source='AD'" +
94 " AND bankrecitem_source_id=bankadj_id)" +
95 " OR (bankrecitem_source='GL'" +
96 " AND bankrecitem_source_id=gltrans_id)" +
97 " WHERE bankadj_id=<? value('bankadjid') ?>" +
98 " ORDER BY preferred DESC LIMIT 1;",
99 getBankRecSql = "SELECT * FROM bankrec WHERE bankrec_id=<? value('id') ?>;",
100 bankRecGLSql = "SELECT gltrans.* " +
102 " JOIN bankrecitem ON gltrans_id=bankrecitem_source_id" +
103 " AND bankrecitem_source='GL'" +
104 " WHERE bankrecitem_bankrec_id=<? value('bankrecid') ?>" +
105 " AND bankrecitem_cleared;",
106 postBankRecSql = "SELECT postBankReconciliation(<? value('id') ?>)" +
108 taxhistCheckSql = "SELECT * FROM taxhist" +
109 " WHERE taxhist_parent_id = <? value('voitem') ?>" +
110 " ORDER BY taxhist_id DESC;",
111 gltransCheckSql = "SELECT count(*) FROM gltrans;", // TODO: make smarter
115 it("patches tax accts to ensure tax handling /can/ work", function (done) {
116 var sql = "UPDATE tax SET tax_dist_accnt_id =" +
117 " (SELECT accnt_id FROM accnt" +
118 " WHERE accnt_descrip = 'Accounts Payable')" +
119 " WHERE tax_dist_accnt_id IS NULL" +
120 " RETURNING tax_id;";
121 datasource.query(sql, creds, function (err, res) {
123 badTaxIds = _.map(res.rows, function (v) { return v.tax_id; });
128 it('looks for a bank account to work with', function (done) {
129 var sql = 'SELECT * FROM bankaccnt WHERE bankaccnt_id IN (' +
130 ' SELECT bankrec_bankaccnt_id FROM bankrec' +
131 ' GROUP BY bankrec_bankaccnt_id' +
132 ' HAVING BOOL_AND(bankrec_posted)) LIMIT 1;'
134 datasource.query(sql, creds, function (err, res) {
135 if (res.rowCount === 1) {
136 bankaccnt = _.clone(res.rows[0]);
137 assert.isNotNull(bankaccnt, 'we found a bank account');
141 var sql = 'SELECT * FROM bankaccnt WHERE bankaccnt_id IN (' +
142 ' SELECT bankrec_bankaccnt_id FROM bankrec' +
143 ' WHERE bankrec_opendate IN' +
144 ' (SELECT MAX(bankrec_opendate) FROM bankrec' +
145 ' WHERE NOT bankrec_posted)' +
148 datasource.query(sql, creds, function (err, res) {
149 assert.equal(res.rowCount, 1);
150 bankaccnt = _.clone(res.rows[0]);
151 assert.isNotNull(bankaccnt, 'we found a bank account');
159 it('gets an open bankrec to test with', function (done) {
161 if (bankrec === "create") {
162 mql = 'INSERT INTO bankrec (bankrec_bankaccnt_id,' +
163 ' bankrec_opendate, bankrec_openbal' +
164 ') SELECT bankrec_bankaccnt_id,' +
165 ' bankrec_enddate + 1, bankrec_endbal' +
167 ' WHERE bankrec_bankaccnt_id=<? value("bankaccnt") ?>' +
168 ' AND bankrec_posted' +
169 ' ORDER BY bankrec_enddate DESC' +
170 ' LIMIT 1 RETURNING *;'
172 } else if (bankrec === "select") {
173 mql = 'SELECT * FROM bankrec' +
174 ' WHERE bankrec_bankaccnt_id=<? value("bankaccnt") ?>' +
175 ' AND bankrec_opendate IN' +
176 ' (SELECT MAX(bankrec_opendate) FROM bankrec' +
177 ' WHERE NOT bankrec_posted' +
178 ' AND bankrec_bankaccnt_id=<? value("bankaccnt") ?>' +
182 sql = mqlToSql(mql, {bankaccnt: bankaccnt.bankaccnt_id});
184 datasource.query(sql, creds, function (err, res) {
185 assert.equal(res.rowCount, 1);
186 bankrec = _.clone(res.rows[0]);
187 assert.isNotNull(bankrec, 'we have a bank rec');
188 assert.isFalse(bankrec.bankrec_posted, 'we have an open bank rec');
193 it('ensures there is an open accounting period', function (done) {
194 var sql = 'SELECT period_id, period_closed, period_freeze' +
196 ' WHERE CURRENT_DATE BETWEEN period_start AND period_end;'
198 datasource.query(sql, creds, function (err, res) {
200 if (res.rowCount !== 1) {
201 sql = mqlToSql("INSERT INTO period (" +
202 " period_closed, period_freeze, period_name," +
203 " period_quarter, period_start," +
204 " period_end, period_number" +
205 ") VALUES (FALSE, FALSE, <? value('testTag') ?>," +
206 " EXTRACT(QUARTER FROM DATE CURRENT_DATE)," +
207 " DATE_TRUNC(MONTH, CURRENT_DATE)," +
208 " DATE_TRUNC(MONTH, CURRENT_DATE) + '1 month'," +
209 " EXTRACT(month FROM DATE CURRENT_DATE)" +
210 ") RETURNING period_id;",
211 { testTag: testTag });
212 } else if (res.rows[0].period_closed === true) {
213 sql = mqlToSql('SELECT openAccountingPeriod(<? value("period") ?>)' +
214 ' AS period_id;', { period: res.rows[0].period_id });
217 datasource.query(sql, creds, function (err, res) {
218 assert.equal(res.rowCount, 1);
219 assert(res.period_id >= 0);
228 it('turns on cash-based tax handling if necessary', function (done) {
229 var sql = "SELECT fetchMetricBool('CashBasedTax') AS result;";
230 datasource.query(sql, creds, function (err, res) {
231 assert.equal(res.rowCount, 1);
232 wasCashBasedTax = res.rows[0].result === 't';
233 if (wasCashBasedTax) {
236 var sql = "SELECT setMetric('CashBasedTax', 't') AS result;";
237 datasource.query(sql, creds, function (err, res) {
238 assert.equal(res.rowCount, 1);
245 it('creates a purchase order', function (done) {
246 var sql = mqlToSql("INSERT INTO pohead (pohead_number, pohead_status," +
247 " pohead_agent_username, pohead_vend_id," +
248 " pohead_taxzone_id, pohead_orderdate," +
249 " pohead_curr_id, pohead_saved, pohead_comments," +
250 " pohead_warehous_id," +
251 " pohead_printed, pohead_terms_id,pohead_taxtype_id" +
252 ") SELECT fetchPoNumber(), 'U'," +
253 " CURRENT_USER, vend_id," +
254 " vend_taxzone_id, CURRENT_DATE," +
255 " vend_curr_id, false, <? value('testTag') ?>," +
256 " (SELECT MIN(warehous_id) FROM whsinfo WHERE " +
257 " warehous_active AND NOT warehous_transit)," +
258 " false, vend_terms_id, taxass_taxtype_id" +
260 " JOIN taxass ON vend_taxzone_id=taxass_taxzone_id" +
261 " JOIN taxrate ON taxass_tax_id=taxrate_tax_id" +
262 " WHERE vend_active" +
263 " AND (taxrate_percent > 0 OR taxrate_amount > 0)" +
264 " LIMIT 1 RETURNING *;",
265 { testTag: testTag });
266 datasource.query(sql, creds, function (err, res) {
267 assert.equal(res.rowCount, 1);
273 it('creates a purchase order item', function (done) {
274 var sql = mqlToSql("INSERT INTO poitem (poitem_pohead_id," +
275 " poitem_linenumber, poitem_status," +
276 " poitem_taxtype_id, poitem_itemsite_id," +
277 " poitem_itemsrc_id, poitem_qty_ordered," +
278 " poitem_unitprice," +
279 " poitem_duedate, poitem_comments" +
280 ") SELECT pohead_id, 1, 'O'," +
281 " pohead_taxtype_id, itemsite_id," +
282 " itemsrc_id, 100," +
283 " itemsrcprice(itemsrc_id, itemsite_warehous_id," +
284 " false, 100, pohead_curr_id,CURRENT_DATE)," +
285 " now() + '5 days', <? value('testTag') ?>" +
287 " JOIN itemsrc ON pohead_vend_id=itemsrc_vend_id" +
288 " JOIN itemsite ON itemsrc_item_id=itemsite_item_id" +
289 " WHERE itemsite_active AND itemsrc_active" +
290 " AND pohead_id=<? value('poheadid') ?>" +
291 " LIMIT 1 RETURNING *;",
292 { poheadid: po.pohead_id, testTag: testTag });
293 datasource.query(sql, creds, function (err, res) {
294 assert.equal(res.rowCount, 1);
295 poitem = res.rows[0];
300 it('calculates purchase order amounts', function (done) {
301 var sql = mqlToSql("SELECT" +
302 " SUM(poitem_qty_ordered*poitem_unitprice) AS amt," +
303 " SUM(poitem_freight) + pohead_freight AS freight," +
304 " (SELECT SUM(tax) FROM" +
305 " (SELECT ROUND(SUM(taxdetail_tax), 2) AS tax" +
307 " calculateTaxDetailSummary('PO',pohead_id,'T')" +
308 " ON (tax_id=taxdetail_tax_id)" +
309 " GROUP BY tax_id) AS taxdata" +
312 " JOIN pohead ON poitem_pohead_id=pohead_id" +
313 " WHERE pohead_id=<? value('pohead_id') ?>" +
314 " GROUP BY pohead_id, pohead_freight;",
315 { pohead_id: po.pohead_id });
316 datasource.query(sql, creds, function (err, res) {
317 assert.equal(res.rowCount, 1);
318 po.amount = res.rows[0].amt;
319 po.freight = res.rows[0].freight;
320 po.tax = res.rows[0].tax;
321 apcheck.amount = po.amount + po.freight + po.tax;
326 it('releases the purchase order', function (done) {
327 var sql = mqlToSql("SELECT releasePurchaseOrder(<? value('id') ?>)" +
328 " AS result;", { id: po.pohead_id });
329 datasource.query(sql, creds, function (err, res) {
330 assert.equal(res.rowCount, 1);
331 assert(res.rows[0].result > 0);
336 it('receives the purchase order', function (done) {
337 var sql = mqlToSql("SELECT enterReceipt('PO', poitem_id, 100, 0, ''," +
338 " pohead_curr_id, CURRENT_DATE, NULL) AS result" +
340 " JOIN pohead ON poitem_pohead_id=pohead_id" +
341 " WHERE poitem_id=<? value('poitem_id') ?>;",
342 { poitem_id: poitem.poitem_id });
343 datasource.query(sql, creds, function (err, res) {
344 assert.equal(res.rowCount, 1);
345 recvid = res.rows[0].result;
350 it('posts the receipt', function (done) {
351 var sql = mqlToSql("SELECT postReceipt(<? value('recvid') ?>, NULL)" +
352 " AS result;", { recvid: recvid });
353 datasource.query(sql, creds, function (err, res) {
354 assert.equal(res.rowCount, 1);
355 assert(res.rows[0].result > 0);
360 it('creates a voucher', function (done) {
361 var sql = mqlToSql("INSERT INTO vohead (vohead_number, vohead_posted," +
362 " vohead_pohead_id, vohead_taxzone_id," +
363 " vohead_vend_id, vohead_terms_id," +
364 " vohead_distdate, vohead_docdate, vohead_duedate," +
365 " vohead_invcnumber, vohead_reference, vohead_1099," +
366 " vohead_amount, vohead_curr_id, vohead_notes" +
367 ") SELECT fetchVoNumber(), false," +
368 " pohead_id, pohead_taxzone_id," +
369 " pohead_vend_id, pohead_terms_id," +
370 " CURRENT_DATE, CURRENT_DATE, now() + '30 days'," +
371 " 'Vend Invoice', <? value('testTag') ?>, false," +
372 " <? value('vototal') ?>," +
373 " pohead_curr_id, <? value('testTag') ?>" +
375 " WHERE pohead_id=<? value('poheadid') ?>" +
377 " currRate(vohead_curr_id, CURRENT_DATE) AS exrate;",
378 { poheadid: po.pohead_id,
379 vototal: apcheck.amount + vomisc.amount,
381 datasource.query(sql, creds, function (err, res) {
382 assert.equal(res.rowCount, 1);
383 voucher = res.rows[0];
388 it('distributes the p/o item to the voucher', function (done) {
389 var sql = mqlToSql("SELECT distributeVoucherLine(vohead_id," +
390 " poitem_id, vohead_curr_id) AS result" +
391 " FROM vohead JOIN poitem" +
392 " ON (vohead_pohead_id=poitem_pohead_id)" +
393 " WHERE poitem_id = <? value('poitem_id') ?>;",
394 { poitem_id: poitem.poitem_id });
395 datasource.query(sql, creds, function (err, res) {
396 assert.equal(res.rowCount, 1);
397 assert.equal(res.rows[0].result, 1);
402 it('gets the voitem', function (done) {
403 var sql = mqlToSql("SELECT * FROM voitem" +
404 " WHERE voitem_vohead_id=<? value('vohead') ?>;",
405 { vohead: voucher.vohead_id });
406 datasource.query(sql, creds, function (err, res) {
407 assert.equal(res.rowCount, 1);
408 voitem = res.rows[0];
409 assert(voitem.voitem_id > 0);
414 it('creates a misc voucher distribution', function (done) {
415 var sql = mqlToSql("INSERT INTO vodist (vodist_vohead_id," +
416 " vodist_poitem_id, vodist_costelem_id," +
417 " vodist_accnt_id, vodist_amount," +
418 " vodist_discountable, vodist_expcat_id," +
419 " vodist_tax_id, vodist_notes" +
420 ") SELECT <? value('vohead') ?>, -1, -1," +
421 " COALESCE(tax_sales_accnt_id,tax_dist_accnt_id)," +
422 " <? value('miscamt') ?>," +
423 " FALSE, -1, tax_id, <? value('testTag') ?>" +
425 " JOIN taxass ON tax_id = taxass_tax_id" +
426 " JOIN taxtype ON taxass_taxtype_id=taxtype_id" +
427 " JOIN poitem ON taxtype_id=poitem_taxtype_id" +
428 " JOIN pohead ON poitem_pohead_id=pohead_id" +
429 " AND taxass_taxzone_id=pohead_taxzone_id" +
430 " WHERE poitem_id=<? value('poitemid') ?>" +
432 { vohead: voucher.vohead_id, miscamt: vomisc.amount,
433 poitemid: poitem.poitem_id, testTag: testTag });
434 datasource.query(sql, creds, function (err, res) {
435 assert.equal(res.rowCount, 1);
436 vomisc = res.rows[0];
437 assert(vomisc.vodist_id > 0);
442 it('posts the voucher', function (done) {
443 var sql = mqlToSql("SELECT postVoucher(<? value('id') ?>, TRUE)" +
444 " AS result;", { id: voucher.vohead_id });
445 datasource.query(sql, creds, function (err, res) {
446 assert.equal(res.rowCount, 1);
447 assert(res.rows[0].result > 0);
452 it('checks for voucher tax distributions', function (done) {
453 var sql = mqlToSql("SELECT 1 AS seq, * FROM voheadtax" +
454 " WHERE taxhist_parent_id=<? value('voheadid') ?>" +
456 "SELECT 2 AS seq, voitemtax.*" +
458 " JOIN voitem ON taxhist_parent_id=voitem_id" +
459 " WHERE voitem_vohead_id=<? value('voheadid') ?>" +
460 " ORDER BY seq, taxhist_id;",
461 { voheadid: voucher.vohead_id });
462 datasource.query(sql, creds, function (err, res) {
463 assert.equal(res.rowCount, 2); // TODO: why not 3 = head + poitem + vodist
465 assert.closeTo(votax.taxhist_basis, 0, closeEnough);
466 voitemtax = res.rows[1];
467 assert.closeTo(voitemtax.taxhist_basis,
468 - poitem.poitem_unitprice * poitem.poitem_qty_ordered,
470 /* TODO vomisctax = res.rows[2];
471 assert.closeTo(vomisctax.taxhist_basis,
472 vomisc.vodist_amount, closeEnough); */
477 it('creates an apcheck to reconcile', function (done) {
478 var sql = mqlToSql("SELECT createCheck(<? value('bankaccntid') ?>, 'V'," +
479 " vohead_vend_id, CURRENT_DATE, vohead_amount," +
480 " vohead_curr_id, NULL, NULL, 'AP Bearer'," +
481 " <? value('testTag') ?>, TRUE, NULL) AS checkid" +
483 " WHERE vohead_id=<? value('voheadid') ?>;",
484 { bankaccntid: bankaccnt.bankaccnt_id,
485 voheadid: voucher.vohead_id,
487 datasource.query(sql, creds, function (err, res) {
488 assert.equal(res.rowCount, 1);
489 assert(res.rows[0].checkid > 0);
490 var checkid = res.rows[0].checkid;
491 sql = mqlToSql("UPDATE checkhead SET checkhead_number=" +
492 " fetchNextCheckNumber(<? value('bank') ?>)" +
493 " WHERE checkhead_id=<? value('check') ?>" +
495 { bank: bankaccnt.bankaccnt_id, check: checkid });
496 datasource.query(sql, creds, function (err, res) {
497 assert.equal(res.rowCount, 1);
498 apcheck = res.rows[0];
504 it('creates a credit memo to attach to the apcheck', function (done) {
505 var sql = mqlToSql("SELECT createAPCreditMemo(NULL, pohead_vend_id," +
506 " fetchJournalNumber('AP-MISC')," +
507 " <? value('vonumber') ?>, pohead_number," +
508 " CURRENT_DATE, <? value('amount') ?>," +
509 " <? value('testTag') ?>, -1," +
510 " CAST(now()+'30 days' AS DATE)," +
511 " pohead_terms_id, pohead_curr_id) AS result" +
513 " WHERE pohead_id=<? value('poheadid') ?>;",
514 { amount: apcheck.checkhead_amount,
516 vonumber: voucher.vohead_number,
517 poheadid: po.pohead_id });
518 datasource.query(sql, creds, function (err, res) {
519 assert.equal(res.rowCount, 1);
520 cm = res.rows[0].result;
526 it('creates a checkitem for the credit memo', function (done) {
527 var sql = mqlToSql("INSERT INTO checkitem (" +
528 " checkitem_checkhead_id, checkitem_apopen_id," +
529 " checkitem_vouchernumber, checkitem_amount," +
530 " checkitem_ponumber, checkitem_discount," +
531 " checkitem_docdate," +
532 " checkitem_curr_id, checkitem_curr_rate" +
533 ") SELECT <? value('checkid') ?>, apopen_id," +
534 " <? value('vonumber') ?>, apopen_amount," +
535 " apopen_ponumber, 0, CURRENT_DATE," +
536 " apopen_curr_id, apopen_curr_rate" +
538 " WHERE apopen_journalnumber=<? value('journal') ?>" +
540 { checkid: apcheck.checkhead_id,
541 vonumber: voucher.vohead_number,
543 datasource.query(sql, creds, function (err, res) {
544 assert.equal(res.rowCount, 1);
545 apcheckitem = res.rows[0];
546 assert(apcheckitem.checkitem_id > 0);
551 it('posts the apcheck', function (done) {
552 var sql = mqlToSql(postCheckSql, { id: apcheck.checkhead_id });
553 datasource.query(sql, creds, function (err, res) {
554 assert.equal(res.rowCount, 1);
555 assert(res.rows[0].result > 0);
556 apcheck.journalNumber = res.rows[0].result;
561 it('marks the apcheck as cleared', function (done) {
562 var sql = mqlToSql(toggleCheckSql, { bankrecid: bankrec.bankrec_id,
563 checkid: apcheck.checkhead_id });
564 datasource.query(sql, creds, function (err, res) {
565 assert.equal(res.rowCount, 1);
566 assert.isTrue(res.rows[0].result);
571 it('confirms the apcheck was marked as cleared', function (done) {
572 var sql = mqlToSql(bankRecItemSql,
573 { brid: bankrec.bankrec_id, src: 'GL',
574 srcid: " IN (SELECT gltrans_id FROM gltrans WHERE" +
575 " gltrans_doctype='CK' AND gltrans_misc_id=" +
576 apcheck.checkhead_id + ")"});
577 datasource.query(sql, creds, function (err, res) {
578 assert.equal(res.rowCount, 1);
579 assert.isTrue(res.rows[0].bankrecitem_cleared);
580 assert(res.rows[0].bankrecitem_cleared);
585 it('marks the apcheck as /not/ cleared', function (done) {
586 var sql = mqlToSql(toggleCheckSql, { bankrecid: bankrec.bankrec_id,
587 checkid: apcheck.checkhead_id });
588 datasource.query(sql, creds, function (err, res) {
589 assert.equal(res.rowCount, 1);
590 assert.isFalse(res.rows[0].result);
595 it('confirms the apcheck is no longer marked as cleared', function (done) {
596 var sql = mqlToSql(bankRecItemSql,
597 { brid: bankrec.bankrec_id, src: 'GL',
598 srcid: " IN (SELECT gltrans_id FROM gltrans WHERE" +
599 " gltrans_doctype='CK' AND gltrans_misc_id=" +
600 apcheck.checkhead_id + ")" });
601 datasource.query(sql, creds, function (err, res) {
602 assert.equal(res.rowCount, 0);
607 it('marks the apcheck as cleared again', function (done) {
608 var sql = mqlToSql(toggleCheckSql, { bankrecid: bankrec.bankrec_id,
609 checkid: apcheck.checkhead_id });
610 datasource.query(sql, creds, function (err, res) {
611 assert.equal(res.rowCount, 1);
612 assert.isTrue(res.rows[0].result);
617 it('confirms that the apcheck is marked as cleared again', function (done) {
618 var sql = mqlToSql(bankRecItemSql,
619 { brid: bankrec.bankrec_id, src: 'GL',
620 srcid: " IN (SELECT gltrans_id FROM gltrans" +
621 " WHERE gltrans_doctype='CK' AND " +
622 " gltrans_misc_id=" + apcheck.checkhead_id + ")"});
623 datasource.query(sql, creds, function (err, res) {
624 assert.equal(res.rowCount, 1);
625 assert.isTrue(res.rows[0].bankrecitem_cleared);
626 assert(res.rows[0].bankrecitem_cleared);
631 it('creates an a/r credit memo', function (done) {
632 var sql = mqlToSql("SELECT createArCreditMemo(NULL, cust_id," +
633 " fetchArMemoNumber(), cohead_number, CURRENT_DATE," +
634 " <? value('refund') ?>, <? value('testTag') ?>," +
635 " (SELECT MIN(rsncode_id) FROM rsncode), NULL, NULL," +
636 " CURRENT_DATE, cust_terms_id, cust_salesrep_id) AS result" +
638 " JOIN cohead ON cust_id=cohead_cust_id" +
639 " JOIN taxass ON cust_taxzone_id=taxass_taxzone_id" +
640 " JOIN taxrate ON taxass_tax_id=taxrate_tax_id" +
641 " WHERE cust_id IN (SELECT MIN(cust_id)" +
642 " FROM custinfo WHERE cust_active)" +
643 " AND (taxrate_percent > 0 OR taxrate_amount > 0) LIMIT 1;",
644 { refund: aropen.amount, testTag: testTag });
645 datasource.query(sql, creds, function (err, res) {
646 assert.equal(res.rowCount, 1);
647 aropen.id = res.rows[0].result;
648 assert(aropen.id > 0);
653 it('creates an archeck to reconcile', function (done) {
654 var sql = mqlToSql("SELECT createCheck(<? value('bankaccntid') ?>, 'C'," +
655 " aropen_cust_id, CURRENT_DATE, aropen_amount," +
656 " aropen_curr_id, NULL, NULL, 'AR Bearer'," +
657 " <? value('testTag') ?>, TRUE, <? value('aropen') ?>) AS checkid" +
659 " WHERE aropen_id=<? value('aropen') ?>;",
660 { bankaccntid: bankaccnt.bankaccnt_id,
663 datasource.query(sql, creds, function (err, res) {
664 assert.equal(res.rowCount, 1);
665 assert(res.rows[0].checkid > 0);
666 var checkid = res.rows[0].checkid;
667 sql = mqlToSql("UPDATE checkhead SET checkhead_number=" +
668 " fetchNextCheckNumber(<? value('bank') ?>)" +
669 " WHERE checkhead_id=<? value('check') ?>" +
671 { bank: bankaccnt.bankaccnt_id, check: checkid });
672 datasource.query(sql, creds, function (err, res) {
673 assert.equal(res.rowCount, 1);
674 archeck = res.rows[0];
680 it('posts the archeck', function (done) {
681 var sql = mqlToSql(postCheckSql, { id: archeck.checkhead_id });
682 datasource.query(sql, creds, function (err, res) {
683 assert.equal(res.rowCount, 1);
684 assert(res.rows[0].result > 0);
685 archeck.journalNumber = res.rows[0].result;
690 it('marks the archeck as cleared', function (done) {
691 var sql = mqlToSql(toggleCheckSql, { bankrecid: bankrec.bankrec_id,
692 checkid: archeck.checkhead_id });
693 datasource.query(sql, creds, function (err, res) {
694 assert.equal(res.rowCount, 1);
695 assert.isTrue(res.rows[0].result);
700 it('confirms the archeck was marked as cleared', function (done) {
701 var sql = mqlToSql(bankRecItemSql,
702 { brid: bankrec.bankrec_id, src: 'GL',
703 srcid: " IN (SELECT gltrans_id FROM gltrans WHERE" +
704 " gltrans_doctype='CK' AND gltrans_misc_id=" +
705 archeck.checkhead_id + ")"});
706 datasource.query(sql, creds, function (err, res) {
707 assert.equal(res.rowCount, 1);
708 assert.isTrue(res.rows[0].bankrecitem_cleared);
709 assert(res.rows[0].bankrecitem_cleared);
714 it('creates a bank adjustment', function (done) {
715 var sql = mqlToSql("INSERT INTO bankadj (" +
716 " bankadj_bankaccnt_id, bankadj_bankadjtype_id," +
717 " bankadj_date, bankadj_docnumber, bankadj_amount," +
718 " bankadj_notes, bankadj_curr_id," +
719 " bankadj_curr_rate" +
720 ") SELECT <? value('bankaccnt') ?>, bankadjtype_id," +
721 " CURRENT_DATE, 'BankRecTest', <? value('amount') ?>," +
722 " <? value('testTag') ?>, <? value('currid') ?>," +
723 " currrate(<? value('currid') ?>, basecurrid()," +
724 " CURRENT_DATE) FROM bankadjtype RETURNING *;",
725 { amount: bankadj.amount, bankaccnt: bankaccnt.bankaccnt_id,
726 testTag: testTag, currid: bankaccnt.bankaccnt_curr_id })
728 datasource.query(sql, creds, function (err, res) {
729 assert.equal(res.rowCount, 1);
730 _.extend(bankadj, res.rows[0]);
731 assert.ok(bankadj.bankadj_id, 'we have a bank adjustment');
736 it('looks at the gltrans table before posting', function (done) {
737 var sql = mqlToSql(gltransCheckSql, { testTag: testTag });
738 datasource.query(sql, creds, function (err, res) {
739 lastGltransCount = res.rows.length;
744 it('posts the reconciliation', function (done) {
745 var sql = mqlToSql(postBankRecSql, {id: bankrec.bankrec_id});
746 datasource.query(sql, creds, function (err, res) {
747 assert.equal(res.rowCount, 1);
748 assert.equal(res.rows[0].result, bankrec.bankrec_id);
753 it('confirms the posted bankrec was updated properly', function (done) {
754 var sql = mqlToSql(getBankRecSql, { id: bankrec.bankrec_id });
755 datasource.query(sql, creds, function (err, res) {
756 assert.equal(res.rowCount, 1);
757 assert.isTrue(res.rows[0].bankrec_posted);
758 assert.isNotNull(res.rows[0].bankrec_postdate, 'expect a post date');
763 it('confirms the gl for the posted bankrec was updated', function (done) {
764 var sql = mqlToSql(bankRecGLSql, { bankrecid: bankrec.bankrec_id });
765 datasource.query(sql, creds, function (err, res) {
766 var recorded = _.filter(res.rows,
767 function (v) { return v.gltrans_rec; });
768 assert.equal(res.rows.length, recorded.length, 'AND(gltrans_rec) should be true');
773 it('confirms the apcheck was reconciled properly', function (done) {
774 var sql = mqlToSql(checkCheckSql, { checkid: apcheck.checkhead_id,
775 bankrecid: bankrec.bankrec_id });
776 datasource.query(sql, creds, function (err, res) {
777 assert.equal(res.rowCount, 1);
778 assert.isTrue(res.rows[0].gltrans_rec);
779 assert.isTrue(res.rows[0].bankrec_posted);
780 assert.closeTo(res.rows[0].gltrans_amount,
781 apcheck.checkhead_amount / apcheck.checkhead_curr_rate,
787 // we expect the reconcile to create gltrans records for voitem tax
788 it('confirms reconcile updated gltrans properly', function (done) {
789 var sql = mqlToSql(gltransCheckSql, { testTag: testTag });
790 datasource.query(sql, creds, function (err, res) {
791 // TODO assert(res.rows.length > lastGltransCount, 'expected new gltrans');
792 lastGltransCount = res.rows.length;
797 it('confirms reconcile updated taxhist properly', function (done) {
798 var sql = mqlToSql(taxhistCheckSql, { voitem: voitem.voitem_id });
799 datasource.query(sql, creds, function (err, res) {
800 assert.equal(res.rowCount, 1, "expect 1 taxhist record for the voitem");
801 assert.closeTo(-res.rows[0].taxhist_basis, po.amount, closeEnough);
802 assert.closeTo(-res.rows[0].taxhist_tax, po.tax, closeEnough);
807 it('confirms the bank adjustment was /not/ posted', function (done) {
808 var sql = mqlToSql(bankAdjCheckSql, { bankadjid: bankadj.bankadj_id});
809 datasource.query(sql, creds, function (err, res) {
810 assert.equal(res.rowCount, 1);
811 assert.isFalse(res.rows[0].bankadj_posted);
812 assert.isNull(res.rows[0].gltrans_id, 'expecting no gltrans');
813 assert.isNull(res.rows[0].bankrecitem_id, 'expecting no bankrecitem');
818 it('reopens the reconcilation', function (done) {
819 var sql = 'SELECT reopenBankReconciliation(' + bankrec.bankrec_id +
821 datasource.query(sql, creds, function (err, res) {
822 assert.equal(res.rowCount, 1);
823 assert.equal(res.rows[0].result, bankrec.bankrec_id);
828 it('confirms the reopened bankrec was updated properly', function (done) {
829 var sql = mqlToSql(getBankRecSql, { id: bankrec.bankrec_id });
830 datasource.query(sql, creds, function (err, res) {
831 assert.equal(res.rowCount, 1);
832 assert.isFalse(res.rows[0].bankrec_posted);
833 assert.isNull(res.rows[0].bankrec_postdate, 'expect empty post date');
838 it('confirms the gl for the reopened bankrec was updated', function (done) {
839 var sql = mqlToSql(bankRecGLSql, { bankrecid: bankrec.bankrec_id });
840 datasource.query(sql, creds, function (err, res) {
841 var recorded = _.filter(res.rows,
842 function (v) { return v.gltrans_rec; });
843 assert.equal(0, recorded.length, 'AND(gltrans_rec) should be true');
848 it('confirms the apcheck was "reopened" properly', function (done) {
849 var sql = mqlToSql(checkCheckSql, { checkid: apcheck.checkhead_id,
850 bankrecid: bankrec.bankrec_id });
851 datasource.query(sql, creds, function (err, res) {
852 assert.equal(res.rowCount, 1);
853 assert.isFalse(res.rows[0].gltrans_rec);
854 assert.isFalse(res.rows[0].bankrec_posted);
855 assert.closeTo(Math.abs(res.rows[0].gltrans_amount), res.rows[0].base,
861 // we expect the reconcile to create gltrans records for voitem tax
862 it('confirms unreconcile updated gltrans properly', function (done) {
863 var sql = mqlToSql(gltransCheckSql, { testTag: testTag });
864 datasource.query(sql, creds, function (err, res) {
865 // TODO assert(res.rows.length > lastGltransCount, 'expected new gltrans');
866 lastGltransCount = res.rows.length;
871 it('confirms reconcile updated taxhist properly', function (done) {
872 var sql = mqlToSql(taxhistCheckSql, { voitem: voitem.voitem_id });
873 datasource.query(sql, creds, function (err, res) {
874 // TODO assert.equal(res.rowCount, 2, "expect 2 taxhist record for the voitem");
875 assert.closeTo(-res.rows[0].taxhist_basis, po.amount, closeEnough);
876 assert.closeTo(-res.rows[0].taxhist_tax, po.tax, closeEnough);
881 it('marks the bank adjustment as cleared', function (done) {
882 var sql = "SELECT toggleBankRecCleared(" + bankrec.bankrec_id +
883 ", 'AD', " + bankadj.bankadj_id +
884 ", " + bankadj.bankadj_curr_rate +
885 ", " + bankadj.bankadj_amount + ") AS result;"
887 datasource.query(sql, creds, function (err, res) {
888 assert.equal(res.rowCount, 1);
889 assert.isTrue(res.rows[0].result);
894 it('confirms the bank adjustment was /not/ posted but is cleared', function (done) {
895 var sql = mqlToSql(bankAdjCheckSql, { bankadjid: bankadj.bankadj_id});
896 datasource.query(sql, creds, function (err, res) {
897 assert.equal(res.rowCount, 1);
898 assert.isFalse(res.rows[0].bankadj_posted);
899 assert(res.rows[0].bankrecitem_id >= 0);
904 it('posts the reconciliation again', function (done) {
905 var sql = mqlToSql(postBankRecSql, {id: bankrec.bankrec_id});
906 datasource.query(sql, creds, function (err, res) {
907 assert.equal(res.rowCount, 1);
908 assert.equal(res.rows[0].result, bankrec.bankrec_id);
913 it('confirms the 2nd posted bankrec was updated properly', function (done) {
914 var sql = mqlToSql(getBankRecSql, { id: bankrec.bankrec_id });
915 datasource.query(sql, creds, function (err, res) {
916 assert.equal(res.rowCount, 1);
917 assert.isTrue(res.rows[0].bankrec_posted);
918 assert.isNotNull(res.rows[0].bankrec_postdate, 'expect a post date');
923 it('confirms the gl for 2nd posted bankrec was updated', function (done) {
924 var sql = mqlToSql(bankRecGLSql, { bankrecid: bankrec.bankrec_id });
925 datasource.query(sql, creds, function (err, res) {
926 var recorded = _.filter(res.rows,
927 function (v) { return v.gltrans_rec; });
928 assert.equal(res.rows.length, recorded.length, 'AND(gltrans_rec) should be true');
933 it('confirms the apcheck was reconciled properly', function (done) {
934 var sql = mqlToSql(checkCheckSql, { checkid: apcheck.checkhead_id,
935 bankrecid: bankrec.bankrec_id });
936 datasource.query(sql, creds, function (err, res) {
937 assert.equal(res.rowCount, 1);
938 assert.isTrue(res.rows[0].gltrans_rec);
939 assert.isTrue(res.rows[0].bankrec_posted);
940 assert.closeTo(res.rows[0].gltrans_amount,
941 apcheck.checkhead_amount / apcheck.checkhead_curr_rate,
947 // we expect the reconcile to create gltrans records for voitem tax
948 it('confirms 2nd reconcile updated gltrans properly', function (done) {
949 var sql = mqlToSql(gltransCheckSql, { testTag: testTag });
950 datasource.query(sql, creds, function (err, res) {
951 // TODO assert(res.rows.length > lastGltransCount, 'expected new gltrans');
952 lastGltransCount = res.rows.length;
957 it('confirms 2nd reconcile updated taxhist properly', function (done) {
958 var sql = mqlToSql(taxhistCheckSql, { voitem: voitem.voitem_id });
959 datasource.query(sql, creds, function (err, res) {
960 // TODO assert.equal(res.rowCount, 3, "expect 3 taxhist record for the voitem");
961 assert.closeTo(-res.rows[0].taxhist_basis, po.amount, closeEnough);
962 assert.closeTo(-res.rows[0].taxhist_tax, po.tax, closeEnough);
967 it('confirms the bank adj was posted & written to the GL', function (done) {
968 var sql = mqlToSql(bankAdjCheckSql, { bankadjid: bankadj.bankadj_id});
969 datasource.query(sql, creds, function (err, res) {
970 assert.equal(res.rowCount, 1);
971 assert.isTrue(res.rows[0].bankadj_posted);
972 assert(res.rows[0].bankadj_sequence >= 0);
973 assert.equal(res.rows[0].bankrecitem_source, 'GL');
974 assert.equal(res.rows[0].bankrecitem_source_id, res.rows[0].gltrans_id);
975 assert.isTrue(res.rows[0].gltrans_rec);
976 assert.closeTo(Math.abs(res.rows[0].gltrans_amount),
977 res.rows[0].baseamt, closeEnough);
982 it('deletes the bankrec', function (done) {
983 var sql = 'SELECT deleteBankReconciliation(' + bankrec.bankrec_id +
985 datasource.query(sql, creds, function (err, res) {
986 assert.equal(res.rowCount, 1);
987 assert(res.rows[0].result >= 0);
992 it('checks that the bankrec is really gone', function (done) {
993 var sql = 'SELECT COUNT(*) AS result FROM bankrec WHERE bankrec_id = ' +
994 bankrec.bankrec_id + ';';
995 datasource.query(sql, creds, function (err, res) {
996 assert.equal(res.rowCount, 1);
997 assert(res.rows[0].result === 0);
1002 it('checks that the bankrecitems are gone', function (done) {
1003 var sql = 'SELECT COUNT(*) AS result FROM bankrecitem' +
1004 ' WHERE bankrecitem_bankrec_id = ' + bankrec.bankrec_id + ';';
1005 datasource.query(sql, creds, function (err, res) {
1006 assert.equal(res.rowCount, 1);
1007 assert(res.rows[0].result === 0);
1012 it('turns off cash-based tax handling if necessary', function (done) {
1013 if (wasCashBasedTax) {
1016 var sql = "SELECT setMetric('CashBasedTax', 'f') AS result;";
1017 datasource.query(sql, creds, function (err, res) {
1018 assert.equal(res.rowCount, 1);
1024 it('tries to delete a non-existent bankrec', function (done) {
1025 var sql = 'SELECT deleteBankReconciliation(-15) AS result;';
1026 datasource.query(sql, creds, function (err, res) {
1027 assert.equal(res.rowCount, 1);
1028 assert(res.rows[0].result === 0); // no, it doesn't complain
1033 it('resets tax_dist_accnt_id to NULL', function (done) {
1034 var sql = mqlToSql('UPDATE tax SET tax_dist_accnt_id = NULL' +
1035 ' WHERE tax_id IN (<? literal("idlist") ?>);',
1036 { idlist: badTaxIds.join(', ') });
1037 if (badTaxIds.length <= 0) {
1040 datasource.query(sql, creds, function (err, res) {