test/test-qbo.sgl

1;;; Test suite for (tally qbo)

2;;;

3;;; Tests CSV parsing, row grouping, account mapping, and journal

4;;; conversion using fixture data.

6(import (sigil test)

7 (sigil string)

8 (sigil struct)

9 (sigil fs)

10 (ledger)

11 (tally qbo))

13;; ========== CSV Parsing ==========

15(test-group "parse-csv-line"

16 (test "parses simple fields"

17 (let ((fields (parse-csv-line "a,b,c")))

18 (assert-equal 3 (length fields))

19 (assert-equal "a" (list-ref fields 0))

20 (assert-equal "b" (list-ref fields 1))

21 (assert-equal "c" (list-ref fields 2))))

23 (test "handles quoted fields"

24 (let ((fields (parse-csv-line "\"hello, world\",b,c")))

25 (assert-equal 3 (length fields))

26 (assert-equal "hello, world" (list-ref fields 0))))

28 (test "handles escaped quotes"

29 (let ((fields (parse-csv-line "\"say \"\"hello\"\"\",b")))

30 (assert-equal 2 (length fields))

31 (assert-equal "say \"hello\"" (list-ref fields 0))))

33 (test "handles empty fields"

34 (let ((fields (parse-csv-line "a,,c,")))

35 (assert-equal 4 (length fields))

36 (assert-equal "" (list-ref fields 1))

37 (assert-equal "" (list-ref fields 3))))

39 (test "handles amounts with commas in quotes"

40 (let ((fields (parse-csv-line "date,type,num,name,memo,acct,\"1,500.00\",")))

41 (assert-equal 8 (length fields))

42 (assert-equal "1,500.00" (list-ref fields 6)))))

44;; ========== QBO CSV Parsing ==========

46(test-group "parse-qbo-csv"

47 (test "parses CSV text into qbo-row records"

48 (let* ((csv (string-append

49 "Date,Transaction Type,Num,Name,Memo/Description,Account,Debit,Credit\n"

50 "01/15/2026,Invoice,1001,Acme Corp,Web design,Accounts Receivable,\"1,500.00\",\n"

51 "01/15/2026,Invoice,1001,Acme Corp,Web design,Services,,\"1,500.00\"\n"))

52 (rows (parse-qbo-csv csv)))

53 (assert-equal 2 (length rows))

54 (let ((first (car rows)))

55 (assert-equal "01/15/2026" (qbo-row-date first))

56 (assert-equal "Invoice" (qbo-row-type first))

57 (assert-equal "1001" (qbo-row-num first))

58 (assert-equal "Acme Corp" (qbo-row-name first))

59 (assert-equal "Web design" (qbo-row-memo first))

60 (assert-equal "Accounts Receivable" (qbo-row-account first))

61 (assert-equal 1500.0 (qbo-row-debit first))

62 (assert-false (qbo-row-credit first)))))

64 (test "skips blank and non-data rows"

65 (let* ((csv (string-append

66 "Date,Transaction Type,Num,Name,Memo/Description,Account,Debit,Credit\n"

67 "\n"

68 "Total,,,,,,\"5,000.00\",\"5,000.00\"\n"

69 "01/15/2026,Invoice,1001,Test,Memo,Account,100.00,\n"))

70 (rows (parse-qbo-csv csv)))

71 (assert-equal 1 (length rows))

72 (assert-equal "01/15/2026" (qbo-row-date (car rows)))))

74 (test "handles parenthesized negative amounts"

75 (let* ((csv (string-append

76 "Date,Transaction Type,Num,Name,Memo/Description,Account,Debit,Credit\n"

77 "01/15/2026,Credit Memo,CM-1,Customer,Refund,Revenue,(500.00),\n"))

78 (rows (parse-qbo-csv csv)))

79 (assert-equal 1 (length rows))

80 (assert-equal -500.0 (qbo-row-debit (car rows))))))

82;; ========== Row Grouping ==========

84(test-group "group-qbo-rows"

85 (test "groups rows with same date+type+num"

86 (let* ((rows (list

87 (qbo-row date: "01/15/2026" type: "Invoice" num: "1001"

88 account: "AR" debit: 1500.0)

89 (qbo-row date: "01/15/2026" type: "Invoice" num: "1001"

90 account: "Income" credit: 1500.0)

91 (qbo-row date: "01/20/2026" type: "Payment" num: "1001"

92 account: "Cash" debit: 1500.0)))

93 (groups (group-qbo-rows rows)))

94 (assert-equal 2 (length groups))

95 (assert-equal 2 (length (car groups)))

96 (assert-equal 1 (length (cadr groups)))))

98 (test "groups consecutive rows with empty num"

99 (let* ((rows (list

100 (qbo-row date: "02/03/2026" type: "Expense" num: ""

101 name: "Office Depot" account: "Supplies" debit: 85.42)

102 (qbo-row date: "02/03/2026" type: "Expense" num: ""

103 name: "Office Depot" account: "Checking" credit: 85.42)

104 (qbo-row date: "02/14/2026" type: "Expense" num: ""

105 name: "Amazon" account: "Supplies" debit: 29.99)))

106 (groups (group-qbo-rows rows)))

107 ;; First two share date+type+num (all empty), third differs by date

108 (assert-equal 2 (length groups))

109 (assert-equal 2 (length (car groups)))

110 (assert-equal 1 (length (cadr groups)))))

111

112 (test "handles multi-posting journal entries"

113 (let* ((rows (list

114 (qbo-row date: "03/01/2026" type: "Journal Entry" num: "JE-001"

115 account: "Depreciation" debit: 500.0)

116 (qbo-row date: "03/01/2026" type: "Journal Entry" num: "JE-001"

117 account: "Supplies" credit: 200.0)

118 (qbo-row date: "03/01/2026" type: "Journal Entry" num: "JE-001"

119 account: "Utilities" credit: 300.0)))

120 (groups (group-qbo-rows rows)))

121 (assert-equal 1 (length groups))

122 (assert-equal 3 (length (car groups)))))

123

124 (test "returns empty list for empty input"

125 (assert-equal '() (group-qbo-rows '()))))

126

127;; ========== Account Mapping ==========

128

129(test-group "map-account-name"

130 (test "exact match"

131 (assert-equal "assets:bank:checking"

132 (map-account-name default-account-map "Checking")))

133

134 (test "prefix match appends suffix"

135 (assert-equal "liabilities:credit-card:business-visa"

136 (map-account-name default-account-map "Credit Card Business Visa")))

137

138 (test "case insensitive matching"

139 (assert-equal "assets:bank:checking"

140 (map-account-name default-account-map "checking")))

141

142 (test "unmatched accounts are sanitized"

143 (assert-equal "my-custom-account"

144 (map-account-name default-account-map "My Custom Account")))

145

146 (test "custom map overrides defaults"

147 (let ((custom (qbo-account-map

148 rules: '(("Revenue" . "income:revenue")

149 ("Checking" . "assets:bank:primary")))))

150 (assert-equal "assets:bank:primary"

151 (map-account-name custom "Checking"))

152 (assert-equal "income:revenue"

153 (map-account-name custom "Revenue")))))

154

155;; ========== Date Conversion ==========

156

157(test-group "date conversion"

158 (test "converts MM/DD/YYYY to YYYY-MM-DD"

159 (let* ((rows (list (qbo-row date: "01/15/2026" type: "Invoice" num: "1001"

160 name: "Test" account: "Checking" debit: 100.0)))

161 (groups (group-qbo-rows rows))

162 (txn (qbo-group->transaction (car groups) default-account-map)))

163 (assert-equal "2026-01-15" (journal-transaction-date txn))))

164

165 (test "pads single-digit month and day"

166 (let* ((rows (list (qbo-row date: "3/5/2026" type: "Expense" num: ""

167 name: "Test" account: "Checking" debit: 10.0)))

168 (groups (group-qbo-rows rows))

169 (txn (qbo-group->transaction (car groups) default-account-map)))

170 (assert-equal "2026-03-05" (journal-transaction-date txn)))))

171

172;; ========== Transaction Conversion ==========

173

174(test-group "qbo-group->transaction"

175 (test "converts a two-posting group to journal transaction"

176 (let* ((rows (list

177 (qbo-row date: "01/20/2026" type: "Payment" num: "1001"

178 name: "Acme Corp" memo: "Payment for Invoice"

179 account: "Checking" debit: 1500.0)

180 (qbo-row date: "01/20/2026" type: "Payment" num: "1001"

181 name: "Acme Corp" memo: "Payment for Invoice"

182 account: "Accounts Receivable" credit: 1500.0)))

183 (groups (group-qbo-rows rows))

184 (txn (qbo-group->transaction (car groups) default-account-map)))

185 ;; Transaction header

186 (assert-equal "2026-01-20" (journal-transaction-date txn))

187 (assert-equal "!" (journal-transaction-status txn))

188 (assert-equal "1001" (journal-transaction-code txn))

189 (assert-equal "Acme Corp" (journal-transaction-description txn))

190 ;; Ref tag

191 (assert-equal "qbo:Payment:1001"

192 (cdr (assoc "ref" (journal-transaction-tags txn))))

193 ;; Postings

194 (let ((postings (journal-transaction-postings txn)))

195 (assert-equal 2 (length postings))

196 ;; First posting: checking debit

197 (let ((p1 (car postings)))

198 (assert-equal "assets:bank:checking" (journal-posting-account p1))

199 (assert-equal 1500.0 (journal-amount-quantity (journal-posting-amount p1)))

200 (assert-equal "$" (journal-amount-commodity (journal-posting-amount p1))))

201 ;; Second posting: AR credit

202 (let ((p2 (cadr postings)))

203 (assert-equal "assets:accounts-receivable" (journal-posting-account p2))

204 (assert-equal -1500.0 (journal-amount-quantity (journal-posting-amount p2)))))))

205

206 (test "converts three-posting journal entry"

207 (let* ((rows (list

208 (qbo-row date: "03/01/2026" type: "Journal Entry" num: "JE-001"

209 memo: "Q1 depreciation" account: "Depreciation" debit: 500.0)

210 (qbo-row date: "03/01/2026" type: "Journal Entry" num: "JE-001"

211 memo: "Q1 depreciation" account: "Office Supplies" credit: 200.0)

212 (qbo-row date: "03/01/2026" type: "Journal Entry" num: "JE-001"

213 memo: "Q1 depreciation" account: "Utilities" credit: 300.0)))

214 (groups (group-qbo-rows rows))

215 (txn (qbo-group->transaction (car groups) default-account-map)))

216 (assert-equal 3 (length (journal-transaction-postings txn)))

217 ;; Debit posting

218 (assert-equal 500.0

219 (journal-amount-quantity

220 (journal-posting-amount (car (journal-transaction-postings txn)))))

221 ;; Credit postings

222 (assert-equal -200.0

223 (journal-amount-quantity

224 (journal-posting-amount (cadr (journal-transaction-postings txn)))))

225 (assert-equal -300.0

226 (journal-amount-quantity

227 (journal-posting-amount (list-ref (journal-transaction-postings txn) 2))))))

228

229 (test "uses memo as description when name is empty"

230 (let* ((rows (list

231 (qbo-row date: "03/01/2026" type: "Journal Entry" num: "JE-001"

232 name: "" memo: "Q1 depreciation"

233 account: "Depreciation" debit: 500.0)))

234 (groups (group-qbo-rows rows))

235 (txn (qbo-group->transaction (car groups) default-account-map)))

236 (assert-equal "Q1 depreciation" (journal-transaction-description txn))))

237

238 (test "marks transactions as pending (!) not cleared"

239 (let* ((rows (list

240 (qbo-row date: "01/15/2026" type: "Invoice" num: "1001"

241 name: "Test" account: "Checking" debit: 100.0)))

242 (groups (group-qbo-rows rows))

243 (txn (qbo-group->transaction (car groups) default-account-map)))

244 (assert-equal "!" (journal-transaction-status txn)))))

245

246;; ========== Full Conversion Pipeline ==========

247

248(test-group "convert-qbo-journal"

249 (test "converts CSV text to journal transactions"

250 (let* ((csv (string-append

251 "Date,Transaction Type,Num,Name,Memo/Description,Account,Debit,Credit\n"

252 "01/15/2026,Invoice,1001,Acme Corp,Web design,Accounts Receivable,\"1,500.00\",\n"

253 "01/15/2026,Invoice,1001,Acme Corp,Web design,Services,,\"1,500.00\"\n"

254 "02/03/2026,Expense,,Office Depot,Supplies,Office Supplies,85.42,\n"

255 "02/03/2026,Expense,,Office Depot,Supplies,Checking,,85.42\n"))

256 (txns (convert-qbo-journal csv)))

257 (assert-equal 2 (length txns))

258 ;; First transaction: Invoice

259 (let ((inv (car txns)))

260 (assert-equal "2026-01-15" (journal-transaction-date inv))

261 (assert-equal "Acme Corp" (journal-transaction-description inv))

262 (assert-equal 2 (length (journal-transaction-postings inv))))

263 ;; Second transaction: Expense

264 (let ((exp (cadr txns)))

265 (assert-equal "2026-02-03" (journal-transaction-date exp))

266 (assert-equal "Office Depot" (journal-transaction-description exp)))))

267

268 (test "accepts custom account map"

269 (let* ((csv (string-append

270 "Date,Transaction Type,Num,Name,Memo/Description,Account,Debit,Credit\n"

271 "01/15/2026,Invoice,1001,Client,Work,Revenue,\"1,000.00\",\n"))

272 (custom (qbo-account-map rules: '(("Revenue" . "income:consulting"))))

273 (txns (convert-qbo-journal csv custom)))

274 (assert-equal 1 (length txns))

275 (assert-equal "income:consulting"

276 (journal-posting-account

277 (car (journal-transaction-postings (car txns))))))))

278

279;; ========== Journal Output Format ==========

280

281(test-group "formatted output"

282 (test "produces valid journal syntax"

283 (let* ((csv (string-append

284 "Date,Transaction Type,Num,Name,Memo/Description,Account,Debit,Credit\n"

285 "01/20/2026,Payment,1001,Acme Corp,Invoice payment,Checking,\"1,500.00\",\n"

286 "01/20/2026,Payment,1001,Acme Corp,Invoice payment,Accounts Receivable,,\"1,500.00\"\n"))

287 (txns (convert-qbo-journal csv))

288 (formatted (format-transaction (car txns))))

289 ;; Should contain the date

290 (assert-true (string-contains? formatted "2026-01-20"))

291 ;; Should contain pending status

292 (assert-true (string-contains? formatted "!"))

293 ;; Should contain the code

294 (assert-true (string-contains? formatted "1001"))

295 ;; Should contain description

296 (assert-true (string-contains? formatted "Acme Corp"))

297 ;; Should contain mapped accounts

298 (assert-true (string-contains? formatted "assets:bank:checking"))

299 (assert-true (string-contains? formatted "assets:accounts-receivable"))

300 ;; Should contain ref tag

301 (assert-true (string-contains? formatted "ref: qbo:Payment:1001"))))

302

303 (test "multi-transaction output separates with blank lines"

304 (let* ((csv (string-append

305 "Date,Transaction Type,Num,Name,Memo/Description,Account,Debit,Credit\n"

306 "01/15/2026,Invoice,1001,Client A,Work,Checking,500.00,\n"

307 "01/15/2026,Invoice,1001,Client A,Work,Services,,500.00\n"

308 "02/01/2026,Invoice,1002,Client B,Work,Checking,300.00,\n"

309 "02/01/2026,Invoice,1002,Client B,Work,Services,,300.00\n"))

310 (txns (convert-qbo-journal csv))

311 (formatted (format-transactions txns)))

312 (assert-true (string-contains? formatted "\n\n")))))

313

314;; ========== Deduplication Integration ==========

315

316(test-group "deduplication with QBO transactions"

317 (test "deduplicates by ref tag"

318 (let* ((existing (list

319 (journal-transaction

320 date: "2026-01-15"

321 status: "!"

322 code: "1001"

323 description: "Acme Corp"

324 tags: (list (cons "ref" "qbo:Invoice:1001"))

325 postings: (list

326 (journal-posting account: "assets:accounts-receivable"

327 amount: (journal-amount quantity: 1500 commodity: "$"))

328 (journal-posting account: "income:services"

329 amount: (journal-amount quantity: -1500 commodity: "$"))))))

330 (csv (string-append

331 "Date,Transaction Type,Num,Name,Memo/Description,Account,Debit,Credit\n"

332 "01/15/2026,Invoice,1001,Acme Corp,Web design,Accounts Receivable,\"1,500.00\",\n"

333 "01/15/2026,Invoice,1001,Acme Corp,Web design,Services,,\"1,500.00\"\n"

334 "02/03/2026,Expense,,Office Depot,Supplies,Office Supplies,85.42,\n"

335 "02/03/2026,Expense,,Office Depot,Supplies,Checking,,85.42\n"))

336 (new-txns (convert-qbo-journal csv))

337 (unique (deduplicate-transactions new-txns existing)))

338 ;; Only the expense should remain (invoice already exists)

339 (assert-equal 1 (length unique))

340 (assert-equal "Office Depot" (journal-transaction-description (car unique))))))

341

342(run-tests)