blib/lib/Finance/Tiller2QIF.pm

filter: Statementstmt 100% 178 / 178 Branchbran 93.1% 87 / 88 (7 uncoverable) Conditioncond 83.7% 36 / 43 MC/DCmcdc 72.0% 18 / 25 Subroutinesub 100% 27 / 27 Podpod 0.0% 0 / 1 totaltotal 94.2% 346 / 362 (7 uncoverable) CC 63 SCAR 35.2
CC
63
Cov
96%
run_cli
54.2
_confirm
6.1
_emit
2.1
CRAP 33.7
↑ Summary blib/lib/Finance/Tiller2QIF/Map.pm »
1 package Finance::Tiller2QIF;
2 # ABSTRACT: Convert Tiller CSV exports to QIF format
3 2 $Finance::Tiller2QIF::VERSION = '1.23';
4 2 ▶use 5.036;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 2 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
6 2 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
7 2 ▶use utf8;
Subroutine BEGIN: called CC=1 SCAR=0.0
8 2 ▶use warnings FATAL => 'utf8';
Subroutine BEGIN: called CC=1 SCAR=0.0
9 2 ▶use open ':std', ':encoding(UTF-8)';
Subroutine BEGIN: called CC=1 SCAR=0.0
10  
11 2 ▶use feature qw/signatures postderef try/;
Subroutine BEGIN: called CC=1 SCAR=0.0
12  
13 2 ▶use Path::Tiny;
Subroutine BEGIN: called CC=1 SCAR=0.0
14 2 ▶use Getopt::Long::Descriptive;
Subroutine BEGIN: called CC=1 SCAR=0.0
15 2 ▶use Cpanel::JSON::XS;
Subroutine BEGIN: called CC=1 SCAR=0.0
16 2 ▶use Finance::Tiller2QIF::ReadCSV;
Subroutine BEGIN: called CC=1 SCAR=0.0
17 2 ▶use Finance::Tiller2QIF::Map;
Subroutine BEGIN: called CC=1 SCAR=0.0
18 2 ▶use Finance::Tiller2QIF::Util qw/vPrint/;
Subroutine BEGIN: called CC=1 SCAR=0.0
19 2 ▶use Finance::Tiller2QIF::WriteQIF;
Subroutine BEGIN: called CC=1 SCAR=0.0
20 2 ▶use Time::Piece;
Subroutine BEGIN: called CC=1 SCAR=0.0
21 # use Data::Printer;
22  
23  
24 =pod
25  
26 =encoding utf8
27  
28 =head1 NAME
29  
30 tiller2qif
31  
32 Finance::Tiller2QIF
33  
34 =head1 DESCRIPTION
35  
36 Convert Tiller CSV exports to QIF for import into Financial software like GnuCash, KMyMoney, Quicken, HomeBank, Money Manager Ex and many others.
37  
38 =head1 SYNOPSIS
39  
40 # Command-line
41 tiller2qif run --input export.csv --db tiller.sqlite3 \
42 --output import.qif [--mapfile mapping.txt]
43  
44 # Programmatic — see PROGRAMMATIC USE below
45 use Finance::Tiller2QIF::ReadCSV;
46 use Finance::Tiller2QIF::Map;
47 use Finance::Tiller2QIF::WriteQIF;
48  
49 Finance::Tiller2QIF::ReadCSV::Ingest( 'export.csv', 'tiller.sqlite3' );
50 Finance::Tiller2QIF::Map::Map({ db_path => 'tiller.sqlite3', mapfile => 'mapping.txt' });
51 Finance::Tiller2QIF::WriteQIF::Emit( 'tiller.sqlite3', 'import.qif' );
52  
53 =head1 OVERVIEW
54  
55 Tiller Money (tillerapp.com) aggregates bank and credit-card transactions
56 into a Google Sheet and lets you export a CSV. This module ingests that CSV
57 into a SQLite database, optionally applies a category-mapping file to
58 translate Tiller's auto-assigned categories to match your accounts/categories, then emits a QIF file ready for import.
59  
60 The three phases can be run individually or together:
61  
62 =over 4
63  
64 =item B<ingest> — parse the CSV and load rows into the SQLite database.
65  
66 =item B<map> — apply a user-supplied mapping file that rewrites categories,
67 suppresses duplicates (card-payment credits), and assigns destination accounts.
68  
69 =item B<emit> — read unexported rows from the database and write a QIF file.
70  
71 =back
72  
73 =head1 INSTALLATION
74  
75 =head2 From CPAN
76  
77 cpan Finance::Tiller2QIF
78 # or
79 cpanm Finance::Tiller2QIF
80  
81 =head2 Supported Platforms
82  
83 Supported platforms are Strawberry Perl on Windows, macOS, Linux and BSD distributions (FreeBSD, NetBSD, OpenBSD, DragonflyBSD). Cygwin is not supported and installation is refused. Other platforms, such as Haiku, Solaris and AIX, are unsupported but not blocked from installation.
84  
85 =head2 Perl Dependencies
86  
87 Runtime: Cpanel::JSON::XS, DateTime::Format::Flexible, DBD::SQLite, DBI,
88 Getopt::Long::Descriptive, Path::Tiny, Text::CSV; on Windows also Win32::ShellQuote
89  
90 Testing: Capture::Tiny, Test2::V0, Test2::Bundle::More, Test2::Tools::Exception
91  
92 =head3 On Debian/Ubuntu:
93  
94 All of Tiller2QIF’s dependencies are available through package management if you need to install to system Perl on Debian 13 or Ubuntu 26.04 or later.
95  
96 sudo apt install libpath-tiny-perl libtext-csv-perl libtest2-suite-perl libcapture-tiny-perl \
97 libdbi-perl libdbd-sqlite3-perl libgetopt-long-descriptive-perl \
98 libcpanel-json-xs-perl libdatetime-format-flexible-perl
99 sudo cpan install Finance::Tiller2QIF
100  
101 =head3 On Windows
102  
103 tiller2qif works with Strawberry Perl, after installing Strawberry Perl, install from CPAN. tiller2qif cannot be installed to ActivePerl and Cygwin.
104  
105 =head1 CLI COMMANDS
106  
107 The command word may be given either before or after the options; any other stray argument on the command line is an error.
108  
109 =over 4
110  
111 =item B<run> -- ingest, map, and emit in one step
112  
113 tiller2qif run --input export.csv --db tiller.sqlite3 \
114 --output import.qif [--mapfile mapping.txt]
115  
116 run will always create a checkpoint even when the flag is not set.
117  
118 =item B<interactive> -- edit and review a complete import before committing it
119  
120 tiller2qif interactive --input export.csv --db tiller.sqlite3 \
121 --output import.qif --mapfile mapping.txt --viewer 'code --wait'
122  
123 Creates an isolated session with a checkpoint of the original database, a fixed
124 copy of the input CSV, and a writable C<corrections.t2qsql> SQL file. The viewer opens
125 the read-only preview, mapping file (when supplied), and corrections together;
126 C<--multipreview> and C<--confirm> are not needed. With C<--viewer console>, the
127 preview is printed and the SQL path is reported for editing separately.
128  
129 Choose C<A> to apply saved edits and refresh, C<V> to reopen the files, C<E> to
130 export, or C<C> to cancel. End of input also cancels. Each refresh rebuilds from
131 the initial checkpoint and captured CSV, then runs C<beforemap>, the current
132 mapping rules, C<aftermap>, and session corrections, in that order. Hooks also
133 run when no mapping file is supplied. Changes do not accumulate across refreshes;
134 removing a correction removes its effect. Mapping edits can restore transactions
135 skipped earlier in this session, but not historical skips in the checkpoint.
136  
137 The preview includes full transaction IDs for targeted SQL, for example:
138  
139 UPDATE transactions SET mapped_category = 'Expenses:Food'
140 WHERE id = 'transaction-id-from-preview';
141  
142 Session SQL runs last, so it can override mapped categories, payees, memos,
143 amounts, accounts, and skip flags. Use C<mapped_category> to override the exported
144 category. Restoring a skipped transaction via SQL requires setting both
145 C<skipped = 0> and C<exported = 0>. SQL scripts may contain multiple statements;
146 C<ATTACH>, C<DETACH>, C<PRAGMA>, and explicit transaction statements are not
147 supported in interactive hooks or corrections. The session controls isolation.
148  
149 Save editor changes before choosing Apply. An export is refused after a failed
150 refresh or when saved mapping, hook, or correction files differ from the last
151 successful review. Invalid CSV rows, mapping errors, and SQL failures cannot
152 silently produce a partial import. Fix the files and refresh, or cancel; changes
153 to the captured CSV require starting a new session.
154  
155 The original database is locked against competing access for the session.
156 Only Export publishes the reviewed database (including export flags) and QIF.
157 Cancel or an error before publication leaves the original database and any
158 existing output QIF unchanged. Publication failures roll back both files.
159 Because two filesystem renames cannot be atomic together, a process crash during
160 publication can temporarily leave mixed outputs: a journal beside the database
161 restores the originals on the next C<interactive> invocation, or preserves both
162 outputs if the commit marker was already written. Restart C<interactive> before
163 using either output after an interrupted publication. Recovery refuses to
164 overwrite files modified externally since the interruption.
165  
166 Session artifacts are retained in the printed C<.tiller2qif-session-*> directory
167 beside the database, including checkpoint, CSV, SQL, previews, and working
168 databases. Mapping-file edits survive cancellation. Corrections are specific to
169 this session and are never automatically reused in a future run. These artifacts
170 contain financial data; remove them manually when no longer needed, and never
171 remove artifacts referenced by a pending C<.interactive.json> journal.
172  
173 Requires an existing database (create it with C<newdb>), writable database and
174 output directories, and a rollback-journal SQLite database; WAL mode is not
175 supported. Do not modify the original database or output from another process
176 during the session. Existing C<run>, C<emit --confirm>, and standalone C<preview>
177 behavior is unchanged.
178  
179 =item B<ingest> -- load CSV into the database
180  
181 tiller2qif ingest --input export.csv --db tiller.sqlite3
182  
183 =item B<map> -- apply category mapping rules
184  
185 tiller2qif map --db tiller.sqlite3 [--mapfile mapping.txt] \
186 [--beforemap before.sql] [--aftermap after.sql]
187  
188 =item B<preview> -- preview the records that would be emitted
189  
190 tiller2qif preview --db tiller.sqlite3
191  
192 Each transaction is shown as an untruncated block, with its ID, date, and account
193 on the first line and indented, labeled detail lines:
194  
195 ID: transaction-id | Date: 2026-09-27 | Account: Assets:Checking
196 Amount: -42.00
197 Payee: Corner Market
198 Memo: Full transaction memo
199 Category: [Groceries] Expenses:Groceries
200  
201 Long values are retained in full. Embedded newlines in detail fields use indented
202 continuation lines; newlines and tabs in the first-line fields are displayed as
203 escaped text so the transaction header stays on one line. Original categories
204 appear in brackets when mapping changes them.
205  
206 # read the preview in an editor or pager instead of the terminal
207 tiller2qif preview --db tiller.sqlite3 --viewer less
208  
209 # and open the mapping file beside it
210 tiller2qif preview --db tiller.sqlite3 --mapfile tiller.mapping \
211 --viewer 'code --wait' --multipreview
212  
213 =item B<emit> -- write QIF from the database
214  
215 tiller2qif emit --db tiller.sqlite3 --output import.qif [--qifdate mdy]
216  
217 =item B<newdb> -- initialise a new SQLite database
218  
219 tiller2qif newdb --db tiller.sqlite3
220  
221 =item B<newconfig> -- create a starter config file
222  
223 tiller2qif newconfig [--config ~/.config/tiller2qif.conf]
224  
225 =item B<checkconfig> -- check the merged values of cli arguments and config file
226  
227 # The verbose flag will run checkconfig before beginning any operations.
228 tiller2qif checkconfig [--config ~/.config/tiller2qif.conf]
229  
230 =item B<clean> -- remove checkpoint copies of the database
231  
232 Deletes all timestamped checkpoint files created by C<--checkpoint> or C<run>,
233 leaving the live database intact.
234  
235 tiller2qif clean --db tiller.sqlite3
236  
237 =item B<version> -- print the installed version number
238  
239 tiller2qif version
240  
241 =back
242  
243 =head1 OPTIONS
244  
245 All options can be supplied on the command line or in a JSON config file.
246 Use C<tiller2qif newconfig --config path/to/file.conf> to generate a starter
247 config file. A typical config file looks like:
248  
249 {
250 "input": "~/Downloads/mytillerdump.csv",
251 "output": "/tmp/tillerout.qif",
252 "db": "~/.data/tiller2qif.sqlite3",
253 "mapfile": "~/.config/tiller.mapping",
254 "viewer": "console",
255 "verbose": false,
256 "checkpoint": false,
257 "confirm": false,
258 "multipreview": false
259 }
260  
261 Pass the config file with C<--config>. Command-line options override config
262 file values.
263  
264 =over 4
265  
266 =item B<--config> Path to a JSON config file.
267  
268 =item B<--input> CSV export from Tiller.
269  
270 =item B<--output> QIF file to create.
271  
272 =item B<--db> SQLite database file used to store and transform transactions between phases.
273  
274 =item B<--mapfile> File containing category mapping rules. Optional; omitting it passes
275 transactions through with their original Tiller categories.
276  
277 =item B<--beforemap> Path to a SQL script to execute against the database immediately
278 before the mapping rules are applied. Useful for preprocessing transactions — for
279 example, renaming accounts or correcting data — in ways that affect which map rules
280 fire.
281  
282 =item B<--aftermap> Path to a SQL script to execute against the database immediately
283 after the mapping rules are applied. Useful for post-processing the mapped results —
284 for example, marking or transforming rows based on what the map phase produced.
285  
286 =item B<--qifdate> QIF output date format. Accepts C<ymd> (default, ISO 8601:
287 C<2026-04-24>), C<mdy> (US: C<04/24/2026>), or C<dmy> (European: C<24/04/2026>).
288 Use C<mdy> or C<dmy> when your financial software does not recognise ISO dates
289 during import.
290  
291 =item B<--checkpoint> Copy the database with a timestamp suffix before any operations.
292 The C<run> command always checkpoints, even without this flag.
293  
294 =item B<--confirm> Run preview before emit (including on C<run>) and prompt for
295 confirmation before writing the QIF file. When used with C<--checkpoint>, adds
296 a revert option (press C<r>) to restore the database to its checkpoint state,
297 useful if you want to undo changes made during ingest or mapping.
298  
299 =item B<--viewer> A program to display preview output instead of the default, C<console>. The command may include arguments if given as a quoted string, for example C<less>, C<gedit>, or C<"code --wait">. The preview is written to a read-only temporary file named C<tiller2qif-preview-*.t2qpv>, the command is run with that file's path appended.
300  
301 On Windows, the command runs through C<cmd.exe>. Use double quotes around program paths containing spaces. Preview, mapping and SQL file arguments are quoted for Windows automatically.
302  
303 =item B<--multipreview> Opens the mapping file in the preview along with the preview , so you can read the pending transactions and edit the rules that produced them side by side. Both files are passed to a single invocation of the viewer. This requires C<--viewer> and C<--mapfile>.
304  
305 =item B<--verbose> Print detailed progress information during each phase. Also
306 runs C<checkconfig> automatically before any operations begin.
307  
308 =item B<--version> Print the installed version number and exit.
309  
310 =back
311  
312 =head1 MAPPING FILE
313  
314 The mapping file controls how Tiller categories are translated into destination
315 account or category names and which transactions to suppress. Each non-comment
316 line has the form:
317  
318 [AccountFilter] field | pattern | destination
319  
320 Lines beginning with C<#> and blank lines are ignored. Rules are evaluated in
321 order; the first matching rule wins and no further rules are checked for that
322 transaction.
323  
324 =over 4
325  
326 =item B<AccountFilter> (optional) — a Perl regex in square brackets that
327 restricts the rule to transactions on matching accounts. Alternation works
328 naturally: C<[Checking|Savings]>. Omit to match all accounts.
329  
330 =item B<field> — the transaction field to test: C<category>, C<payee>,
331 C<memo>, C<date>, or C<amount>.
332  
333 =item B<pattern> — a Perl regex applied case-insensitively to the field value.
334  
335 For a simple pattern containing no C<|>, write it as-is:
336  
337 payee | Starbucks | Expenses:Coffee
338  
339 To allow setting a category with only an AccountFilter, use C<*> as the entire pattern to match against any field value:
340  
341 # Without an account filter all transactions will match!
342 [AccountFilter] any_matchable_field | * | new_category
343  
344 To use regex alternation (matching either of several values), enclose the
345 pattern in forward slashes:
346  
347 payee | /Starbucks|Dunkin/ | Expenses:Coffee
348  
349 To match a literal pipe character in the data, escape it with a backslash:
350  
351 payee | Cash\|App Payment | Expenses:Transfers
352  
353 Patterns are Perl regular expressions, so escape other regex metacharacters
354 when they should be literal (C<.>, C<*>, C<+>, the question mark, C<(>, C<)>,
355 C<[>, C<]>, C<$>, C<^>, C<\>, or C</> in a slash-delimited pattern). Apostrophes
356 have no special meaning and do not need escaping:
357  
358 payee | /^kaplan's new model$/ | Expenses:Food
359  
360 More complex regular expressions are supported when a simple pattern is not
361 enough. For example, this matches Kaplan, Kaplan's, or Kaplans followed by
362 “New” and an optional “Model”:
363  
364 payee | /kaplan(?:'s|s)? new(?: model)?/ | Expenses:Bakeries
365  
366 Test complex patterns carefully, to make sure they are interpreted as expected.
367  
368 =item C<source> — keep the original Tiller category unchanged.
369  
370 =item C<blank> — emit no category field in the QIF output.
371  
372 =item C<skip> — exclude the transaction from QIF output entirely (useful for
373 suppressing the credit-side of card payments that appear in both accounts).
374  
375 =back
376  
377 For double-entry programs such as GnuCash, destination is a full account name
378 (e.g. C<Expenses:Groceries>). For single-entry programs such as Quicken it is
379 a category name.
380  
381  
382 The optional C<default> line sets the fallback for transactions that match no
383 rule. It must appear as the last non-comment line:
384  
385 default | source
386  
387 If the C<default> line is omitted, unmatched transactions behave as
388 C<default | source>.
389  
390 =head2 EXAMPLES
391  
392 =over 4
393  
394 =item * Map by category
395  
396 category | Groceries | Expenses:Groceries
397  
398 =item * Map by payee with alternation (slash-delimited pattern)
399  
400 payee | /Starbucks|Dunkin/ | Expenses:Coffee
401  
402 =item * Map by payee, scoped to one account
403  
404 [Checking] payee | Payroll | Income:Salary
405  
406 =item * Scope to multiple accounts using alternation in the account filter
407  
408 [Checking|Savings] category | Transfer | skip
409  
410 =item * Skip card-payment credits on the card account
411  
412 [CapitalOne] category | Transfer | skip
413  
414 =item * Match a literal pipe character in a payee name
415  
416 payee | Cash\|App | Expenses:Transfers
417  
418 =item * Suppress category in QIF (no L field)
419  
420 category | Miscellaneous | blank
421  
422 =item * Default: leave unmatched transactions with their Tiller category
423  
424 default | source
425  
426 =back
427  
428 =head1 VS CODE EXTENSION
429  
430 The repository includes a Visual Studio Code extension, C<vscode-tiller-map/>, which highlights mapping files and preview files and completes destination account names from your chart of accounts. It is not published to the Marketplace, it can only be installed from a checkout of the repository.
431  
432 With B<--viewer code> and B<--multipreview> the actions B<preview> and B<run> will open the preview and map file in vscode
433  
434 =head2 Installation
435  
436 git clone https://github.com/brainbuz/tiller2qif
437 cp -r tiller2qif/vscode-tiller-map ~/.vscode/extensions/
438 # or, to track the checkout:
439 ln -s "$PWD/tiller2qif/vscode-tiller-map" ~/.vscode/extensions/
440  
441 Reload VS Code afterwards.
442  
443 =head2 Syntax Highlighting
444  
445 Files with C<.map> and C<.mapping> extensions are recognized as mappings, preview
446 files have the C<.t2qpv> extension. Session correction files use C<.t2qsql> and the
447 dedicated B<Tiller2QIF SQL> language mode, with SQL highlighting. Ordinary C<.sql>
448 file associations are unchanged. Preview files enable word wrapping by default.
449  
450 =head2 Completion Hinting
451  
452 In addition to completion of Tiller2QIF keywords you can also configure your Chart of Accounts for Completion! The extension's settings C<tiller2qifMap.coaPath> and C<tiller2qifMap.coaFormat> control this. Currently the only available coaFormat is C<gnucash-csv>.
453  
454 In B<Tiller2QIF SQL> files, the same chart of accounts supplies completions inside
455 single-quoted values assigned to or compared with C<account>, C<category>, and
456 C<mapped_category>. For example, type inside the quotes in
457 C<UPDATE transactions SET mapped_category = '' WHERE id = '...';>.
458 Suggestions appear while typing, after an opening quote or colon, or with
459 C<Ctrl+Space>. Account names containing apostrophes are escaped for SQL. Map-only
460 keywords such as C<skip> and C<source> are not offered as SQL values, and hints do
461 not appear in comments, payees, memos, or transaction IDs.
462  
463 For an older session's C<corrections.sql>, manually select B<Tiller2QIF SQL> using
464 VS Code's language-mode selector; no rename is necessary. Other Tiller SQL files
465 can opt in using the same language mode or the C<.t2qsql> extension. Refresh the
466 installed extension copy (if not symlinked to this checkout) and reload VS Code
467 after updating it.
468  
469 =head2 Row Colors
470  
471 C<tiller2qifMap.rowColors> controls row backgrounds in mappings and previews.
472 C<rainbow>, the default, gives transactions a repeating series of subtle background
473 colors; C<none> turns backgrounds off. Every line in a preview transaction block,
474 including multiline field continuations, receives the same color.
475  
476 =head1 Advanced Use
477  
478 You can write SQL scripts or use an interactive sqlite3 client to make changes between steps. For example your Tiller sheet might have an account "Checking", while your table of accounts has "Assets::Current Assets::Bank::Checking". With custom SQL you can keep the short name in Tiller even though mapping rules can't rename accounts.
479  
480 The C<--beforemap> and C<--aftermap> options allow SQL scripts to run immediately before
481 and after the map phase without having to break the workflow into separate commands.
482 This is the preferred way to preprocess or post-process transactions when using C<run>,
483 or C<map> as a single step.
484  
485 The C<preview> command is meant to be run between map and emit. You may run the steps individually (ingest, map, preview, emit), or use the C<--confirm> option to run preview before emit (including run). C<--viewer> lets you read the full transaction blocks in an editor or pager; combined with C<--confirm> it lets you review the pending transactions and then answer the export prompt.
486  
487 When using C<--confirm> with C<--checkpoint>, three choices Y=Yes N=No R=Revert are offered. No keeps the database state while not completing the export, Revert restores the database to the checkpoint in addition to aborting.
488  
489 While other CSV export sources are not directly supported, you can write a script to remap the fields for ingestion or just import into the table, and then use the map and emit stages to complete your export. If translating other CSV sources be aware that Tiller currently only provides it's data in the US 'MM/DD/YYYY' format, this program can also accept dates in ISO 8601 'YYYY-MM-DD'. Data is written into the SQLite database using the ISO 8601 format.
490  
491 =head1 PROGRAMMATIC USE
492  
493 C<Finance::Tiller2QIF> is primarily a CLI tool; the public functions exist to
494 support the command dispatcher. Programmatic users will likely use this module
495 as example code and call the sub-modules directly (C<Finance::Tiller2QIF::ReadCSV>,
496 C<Finance::Tiller2QIF::Map>, C<Finance::Tiller2QIF::WriteQIF>).
497  
498 Note that all functions expect C<db_path> as the database parameter. The CLI
499 normalises the C<--db> option to C<db_path> internally; programmatic callers
500 should use C<db_path> directly.
501  
502 =for Pod::Coverage run_cli
503  
504 =head1 AUTHOR
505  
506 John Karr E<lt>cpan@brainbuz.orgE<gt>
507  
508 =head1 LICENSE
509  
510 GPL version 3 or later.
511  
512 =cut
513  
514 # ---------------------------------------------------------------------------
515 # Public API
516 # ---------------------------------------------------------------------------
517  
518 22 ▶sub _ingest (%options) {
Subroutine _ingest: called CC=1 SCAR=0.0
519 Finance::Tiller2QIF::ReadCSV::Ingest( $options{input}, $options{db_path},
520 22 $options{verbose} );
521 }
522  
523 4 ▶sub _apply_map (%options) {
Subroutine _apply_map: called CC=1 SCAR=0.0
524 4 Finance::Tiller2QIF::Map::Map( \%options );
525 }
526  
527 10 ▶sub _emit (%options) {
Subroutine _emit: called CC=2 SCAR=7.6
528 Finance::Tiller2QIF::WriteQIF::Emit( $options{db_path}, $options{output},
529 10 ▶ $options{verbose}, $options{qifdate} // 'ymd' );
Condition50%1/2
$options{"qifdate"} // "ymd"
l!l
100
MC/DC0%0/1
$options{"qifdate"} // "ymd"
$options{"qifdate"}
Truth table50%1/2
A
A= $options{"qifdate"}
Aresult
11
00
530 }
531  
532 10 ▶sub _preview (%options) {
Subroutine _preview: called CC=2 SCAR=6.9
533 10 ▶ my @also = $options{multipreview} ? ( $options{mapfile} ) : ();
Branch: 1 branch
BranchTrueFalseTotal
$options{'multipreview'} ? : 2 8 10
534 Finance::Tiller2QIF::WriteQIF::Preview( $options{db_path}, $options{verbose},
535 10 $options{viewer}, @also );
536 }
537  
538 1 ▶sub _run (%options) {
Subroutine _run: called CC=1 SCAR=0.0
539 1 _ingest(%options);
540 1 _apply_map(%options);
541 1 _emit(%options);
542 }
543  
544 10 ▶sub _checkpoint($file) {
Subroutine _checkpoint: called CC=1 SCAR=0.0
545 10 my $new = "$file." . localtime()->datetime();
546 10 $new =~ s/[T:]/_/g;
547 10 path($file)->copy($new);
548 10 return $new;
549 }
550  
551 1 ▶sub _clean_checkpoints($file) {
Subroutine _clean_checkpoints: called CC=2 SCAR=6.9
552 2 ▶ my @checkpoints = grep { /\.\d{4}-\d{2}-\d{2}_\d{2}_\d{2}_\d{2}$/ } glob( $file . '.*' );
Subroutine BEGIN: called CC=1 SCAR=0.0
553 1 for my $cp (@checkpoints) {
554 2 path($cp)->remove;
555 2 say "Removed checkpoint: $cp";
556 }
557 1 return scalar @checkpoints;
558 }
559  
560 55 ▶sub _version_text {
Subroutine _version_text: called CC=2 SCAR=7.0
561 # dzil is in charge of the $VERSION variable which means it is undefined during development
562 # uncoverable branch true
563 # uncoverable branch false
564 2 ▶ my $v = do { no strict 'vars'; $VERSION // 'unversioned' };
Subroutine BEGIN: called CC=1 SCAR=0.0
Condition50%1/2
$VERSION // "unversioned"
l!l
550
MC/DC0%0/1
$VERSION // "unversioned"
$VERSION
Truth table50%1/2
A
A= $VERSION
Aresult
11
00
565 55 return "Tiller2QIF VERSION: ${v}";
566 }
567  
568 10 ▶sub _confirm ( %options ) {
Subroutine _confirm: called CC=6 SCAR=18.0
569 # uncoverable branch true
570 # uncoverable branch false
571 10 ▶ if ( $options{confirm} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($options{"confirm"}) 3 7 10
572 3 my $count = _preview(%options);
573 3 say '-'x60;
574 3 say "${count} transaction(s) pending export.";
575 3 say '-'x60;
576 3 my $msg = "Complete export? Y/n: ";
577 3 ▶ if( $options{confirm} && $options{checkpoint}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($options{"confirm"} and $options{"checkpoint"}) 1 2 3
Condition66%2/3
$options{"confirm"} and $options{"checkpoint"}
!ll&&!rl&&r
021
MC/DC50%1/2
$options{"confirm"} and $options{"checkpoint"}
$options{"confirm"} $options{"checkpoint"}
Truth table66%2/3
A and B
A= $options{"confirm"}B= $options{"checkpoint"}
ABresult
0X0
100
111
578 1 $msg = "Y=Yes N=No R=No and Revert to last Checkpoint\nComplete export? Y/n/r: ";
579 }
580 3 say $msg;
581 3 my $response = <STDIN>;
582 3 ▶ return 1 if $response =~ /^y/i; # user confirmed
Branch: 1 branch
BranchTrueFalseTotal
if $response =~ /^y/iu 1 2 3
583 2 ▶ if ( $response =~ /^r/i ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($response =~ /^r/iu) 1 1 2
584 # copy checkpoint over db
585 1 path($options{checkpointfile})->copy($options{db_path});
586 }
587 2 return 0; # didn't confirm
588 }
589 7 return 1; # confirmation not requested return true as if confirmed.
590 }
591  
592 # ---------------------------------------------------------------------------
593 # CLI entry point — called by bin/tiller2qif
594 # ---------------------------------------------------------------------------
595  
596 2 my $VALID_COMMANDS = join '|',
597 qw(run interactive ingest map emit preview newdb newconfig checkconfig clean version);
598  
599 2 my $hlpmsg = <<'END_USAGE';
600 tiller2qif — Convert Tiller Money CSV exports to QIF format.
601  
602 Ingests a Tiller CSV into a SQLite database, optionally applies a
603 category-mapping file, then writes a QIF file ready for import into
604 GnuCash, KMyMoney, Quicken, HomeBank, and similar programs.
605  
606 Usage: tiller2qif <command> %o
607  
608 Commands: run | interactive | ingest | map | emit | preview | newdb | newconfig | checkconfig | version
609  
610 For the full manual: perldoc Finance::Tiller2QIF
611 END_USAGE
612  
613 2 my $badcmdhelp = <<'BADCMD';
614  
615 There was an error in your command line.
616 Common causes are:
617 * mistyping an option
618 * accidental text in the line
619  
620 BADCMD
621  
622 67 ▶sub run_cli {
Subroutine run_cli: called CC=54 SCAR=39.9
Pod undocumented
623 67 my $cmd = '';
624 # uncoverable branch true
625 # uncoverable branch false
626 67 ▶ if ( @ARGV && $ARGV[0] !~ /^-/ ) {
Branch: 1 branch
BranchTrueFalseTotal
if (@ARGV and not $ARGV[0] =~ /^-/u) 59 8 67
Condition100%3/3
@ARGV and not $ARGV[0] =~ /^-/u
!ll&&!rl&&r
1759
MC/DC100%2/2
@ARGV and not $ARGV[0] =~ /^-/u
@ARGV not $ARGV[0] =~ /^-/u
Truth table100%3/3
A and not(B)
A= @ARGVB= not $ARGV[0] =~ /^-/u
ABresult
0X0
100
111
627 59 $cmd = lc shift @ARGV;
628 }
629  
630 67 my ( $opt, $usage ) = eval {
631 67 describe_options(
632 $hlpmsg,
633 [ 'config|c=s', "JSON config file" ],
634 [ 'checkpoint|C', "copy database with timestamp before run." ],
635 [ 'input|i=s', "CSV file to read (ingest, run, interactive)" ],
636 [ 'output|o=s', "QIF file to write (emit, run, interactive)" ],
637 [ 'db|d=s', "SQLite database file (all commands except newconfig)" ],
638 [ 'mapfile|f=s', "Category mapping file (map, run, interactive — optional)" ],
639 [ 'beforemap=s', "sql script to run prior to map" ],
640 [ 'aftermap=s', "sql script to run after map" ],
641 [ 'qifdate=s', "QIF date format: ymd (default), mdy, or dmy" ],
642 [ 'confirm', "run preview before emit and confirm export"],
643 [ 'viewer=s',
644 "external program to view preview output (default: console)" ],
645 [ 'multipreview',
646 "also open the mapping file in the preview viewer" ],
647 [ 'verbose|v', "Print detailed progress information" ],
648 [ 'version', "Print the installed version and exit", { shortcircuit => 1 } ],
649 [],
650 [ 'help|h', "Print usage and exit", { shortcircuit => 1 } ],
651 );
652 };
653 # uncoverable branch true
654 67 ▶ die $@, $badcmdhelp if $@;
Branch: 1 branch
BranchTrueFalseTotal
if $@ 0 67 67
655  
656 67 ▶ if ( $opt->help ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($opt->help) 2 65 67
657 2 say $usage;
658 2 return;
659 }
660  
661 65 ▶ if ( $opt->version ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($opt->version) 1 64 65
662 1 say _version_text();
663 1 return;
664 }
665  
666 # Getopt::Long permutes @ARGV, so a command word given after the options
667 # is left behind here rather than eaten as an option value; pick it up
668 # if the pre-parse grab above didn't already find one.
669 64 ▶ if ( !$cmd && @ARGV && $ARGV[0] !~ /^-/ ) {
Branch: 1 branch
BranchTrueFalseTotal
if (not $cmd and @ARGV and not $ARGV[0] =~ /^-/u) 3 61 64
Condition83%5/6
not $cmd and @ARGV and not $ARGV[0] =~ /^-/u
!ll&&!rl&&r
6103
not $cmd and @ARGV
!ll&&!rl&&r
5923
MC/DC66%2/3
not $cmd and @ARGV and not $ARGV[0] =~ /^-/u
not $cmd @ARGV not $ARGV[0] =~ /^-/u
Truth table75%3/4
not(A) and B and not(C)
A= not $cmdB= @ARGVC= not $ARGV[0] =~ /^-/u
ABCresult
0XX0
10X0
1100
1111
670 3 $cmd = lc shift @ARGV;
671 }
672  
673 64 ▶ die "tiller2qif: unexpected argument(s): @ARGV\n$badcmdhelp" if @ARGV;
Branch: 1 branch
BranchTrueFalseTotal
if @ARGV 1 63 64
674  
675 63 ▶ if ( !$cmd ) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($cmd) 2 61 63
676 2 die
677 "Command Missing! Valid commands: $VALID_COMMANDS\nFor help: tiller2qif --help\n";
678 }
679  
680 61 ▶ die qq{Unknown command '$cmd'.
Branch: 1 branch
BranchTrueFalseTotal
unless $cmd =~ /^(?:$VALID_COMMANDS)$/u 2 59 61
681 Valid commands: $VALID_COMMANDS
682 for help tiller2qif --help or perldoc tiller2qif
683 \n}
684 unless $cmd =~ /^(?:$VALID_COMMANDS)$/;
685  
686 59 ▶ if ( $cmd eq 'version' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($cmd eq "version") 1 58 59
687 1 say _version_text();
688 1 return;
689 }
690  
691 58 ▶ if ( $cmd eq 'newconfig' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($cmd eq "newconfig") 2 56 58
692 2 ▶ die "newconfig requires --config\n" unless $opt->config;
Branch: 1 branch
BranchTrueFalseTotal
unless $opt->config 1 1 2
693 1 vPrint( $opt->verbose, _version_text() );
694 # say "Creating config file: " . $opt->config if $opt->verbose;
695 1 vPrint( $opt->verbose, "Creating config file: " . $opt->config );
696 1 Finance::Tiller2QIF::Util::InitConfig( $opt->config );
697 1 say "Config file created: " . $opt->config;
698 1 return;
699 }
700  
701 # Precedence: defaults < config file < CLI args
702 56 my %options = (
703 input => undef,
704 output => undef,
705 db => undef,
706 mapfile => undef,
707 beforemap => undef,
708 aftermap => undef,
709 qifdate => 'ymd',
710 verbose => 0,
711 checkpoint => 0,
712 confirm => 0,
713 viewer => 'console',
714 multipreview => 0,
715 );
716  
717 56 ▶ if ( $opt->config ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($opt->config) 4 52 56
718 4 my $config = Cpanel::JSON::XS->new->utf8->relaxed->decode(
719 path( $opt->config )->slurp_utf8 );
720 4 for my $key ( keys %options ) {
721 48 ▶ $options{$key} = $config->{$key} if defined $config->{$key};
Branch: 1 branch
BranchTrueFalseTotal
if defined $config->{$key} 9 39 48
722 }
723 }
724  
725 56 for my $key ( keys %options ) {
726 672 my $val = $opt->$key();
727 672 ▶ $options{$key} = $val if defined $val;
Branch: 1 branch
BranchTrueFalseTotal
if defined $val 165 507 672
728 }
729 56 ▶ $options{checkpoint} = 1 if $cmd eq 'run';
Branch: 1 branch
BranchTrueFalseTotal
if $cmd eq "run" 5 51 56
730  
731 # Only validate the viewer when this command will actually use it —
732 # otherwise a blank/odd viewer in a shared config file would block
733 # unrelated commands like newdb or ingest that never touch it.
734 56 ▶ if ( $cmd eq 'preview' || $cmd eq 'interactive' || ( $cmd =~ /^(?:emit|run)$/ && $options{confirm} ) ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($cmd eq "preview" or $cmd eq "interactive" or $cmd =~ /^(?:emit|run)$/u and $options{"confirm"}) 36 20 56
Condition100%9/9
$cmd eq "preview" or $cmd eq "interactive" or $cmd =~ /^(?:emit|run)$/u and $options{"confirm"}
l!l&&r!l&&!r
33320
$cmd eq "preview" or $cmd eq "interactive"
l!l&&r!l&&!r
112223
$cmd =~ /^(?:emit|run)$/u and $options{"confirm"}
!ll&&!rl&&r
1283
MC/DC100%4/4
$cmd eq "preview" or $cmd eq "interactive" or $cmd =~ /^(?:emit|run)$/u and $options{"confirm"}
$cmd eq "preview" $cmd eq "interactive" $cmd =~ /^(?:emit|run)$/u $options{"confirm"}
Truth table100%5/5
A or B or C and D
A= $cmd eq "preview"B= $cmd eq "interactive"C= $cmd =~ /^(?:emit|run)$/uD= $options{"confirm"}
ABCDresult
1XXX1
01XX1
00111
000X0
00100
735 die "--viewer must name a program or 'console'\n"
736 36 ▶ unless defined $options{viewer} && $options{viewer} =~ /\S/;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $options{"viewer"} and $options{"viewer"} =~ /\S/u 2 34 36
Condition66%2/3
defined $options{"viewer"} and $options{"viewer"} =~ /\S/u
!ll&&!rl&&r
0234
MC/DC50%1/2
defined $options{"viewer"} and $options{"viewer"} =~ /\S/u
defined $options{"viewer"} $options{"viewer"} =~ /\S/u
Truth table66%2/3
A and B
A= defined $options{"viewer"}B= $options{"viewer"} =~ /\S/u
ABresult
0X0
100
111
737  
738 34 ▶ if ( $options{multipreview} && $cmd ne 'interactive' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($options{"multipreview"} and $cmd ne "interactive") 4 30 34
Condition66%2/3
$options{"multipreview"} and $cmd ne "interactive"
!ll&&!rl&&r
3004
MC/DC50%1/2
$options{"multipreview"} and $cmd ne "interactive"
$options{"multipreview"} $cmd ne "interactive"
Truth table66%2/3
A and B
A= $options{"multipreview"}B= $cmd ne "interactive"
ABresult
0X0
100
111
739 die "--multipreview requires --viewer, the console can't open files\n"
740 4 ▶ if lc $options{viewer} eq 'console';
Branch: 1 branch
BranchTrueFalseTotal
if lc $options{"viewer"} eq "console" 1 3 4
741 3 ▶ die "--multipreview requires --mapfile\n" unless $options{mapfile};
Branch: 1 branch
BranchTrueFalseTotal
unless $options{"mapfile"} 1 2 3
742 }
743 }
744  
745 52 vPrint( $options{verbose}, _version_text() );
746  
747 52 ▶ $options{db_path} = delete $options{db} if defined $options{db};
Branch: 1 branch
BranchTrueFalseTotal
if defined $options{"db"} 49 3 52
748  
749 52 ▶ if ( $cmd eq 'newdb' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($cmd eq "newdb") 4 48 52
750 4 ▶ die "newdb requires --db\n" unless $options{db_path};
Branch: 1 branch
BranchTrueFalseTotal
unless $options{"db_path"} 1 3 4
751 3 vPrint( $opt->verbose, "Creating database: " . $options{db_path} );
752 # say "Creating database: " . $options{db_path} if $opt->verbose;
753 3 Finance::Tiller2QIF::Util::InitDB( $options{db_path} );
754 3 say "Database initialized: " . $options{db_path};
755 3 return;
756 }
757  
758 48 ▶ if ( $cmd eq 'clean' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($cmd eq "clean") 2 46 48
759 2 ▶ die "clean requires --db\n" unless $options{db_path};
Branch: 1 branch
BranchTrueFalseTotal
unless $options{"db_path"} 1 1 2
760 1 my $removed = _clean_checkpoints( $options{db_path} );
761 1 say "Removed $removed checkpoint(s)";
762 1 return;
763 }
764  
765 46 my @missing;
766 46 ▶ push @missing, '--input' if $cmd =~ /^(?:ingest|run|interactive)$/ && !$options{input};
Branch: 1 branch
BranchTrueFalseTotal
if $cmd =~ /^(?:ingest|run|interactive)$/u and not $options{"input"} 1 45 46
Condition100%3/3
$cmd =~ /^(?:ingest|run|interactive)$/u and not $options{"input"}
!ll&&!rl&&r
14311
MC/DC100%2/2
$cmd =~ /^(?:ingest|run|interactive)$/u and not $options{"input"}
$cmd =~ /^(?:ingest|run|interactive)$/u not $options{"input"}
Truth table100%3/3
A and not(B)
A= $cmd =~ /^(?:ingest|run|interactive)$/uB= not $options{"input"}
ABresult
0X0
100
111
767 46 ▶ push @missing, '--db' if !$options{db_path};
Branch: 1 branch
BranchTrueFalseTotal
unless $options{"db_path"} 1 45 46
768 46 ▶ push @missing, '--output' if $cmd =~ /^(?:emit|run|interactive)$/ && !$options{output};
Branch: 1 branch
BranchTrueFalseTotal
if $cmd =~ /^(?:emit|run|interactive)$/u and not $options{"output"} 1 45 46
Condition100%3/3
$cmd =~ /^(?:emit|run|interactive)$/u and not $options{"output"}
!ll&&!rl&&r
13321
MC/DC100%2/2
$cmd =~ /^(?:emit|run|interactive)$/u and not $options{"output"}
$cmd =~ /^(?:emit|run|interactive)$/u not $options{"output"}
Truth table100%3/3
A and not(B)
A= $cmd =~ /^(?:emit|run|interactive)$/uB= not $options{"output"}
ABresult
0X0
100
111
769  
770 46 ▶ if (@missing) {
Branch: 1 branch
BranchTrueFalseTotal
if (@missing) 3 43 46
771 3 die "tiller2qif $cmd: missing required option(s): "
772 . join( ', ', @missing ) . "\n";
773 }
774  
775 43 ▶ if ( $cmd eq 'interactive' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($cmd eq "interactive") 22 21 43
776 22 require Finance::Tiller2QIF::Interactive;
777 22 return Finance::Tiller2QIF::Interactive::Run(\%options);
778 }
779  
780 21 ▶ if ( $options{verbose} || $cmd eq 'checkconfig' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($options{"verbose"} or $cmd eq "checkconfig") 3 18 21
Condition100%3/3
$options{"verbose"} or $cmd eq "checkconfig"
l!l&&r!l&&!r
2118
MC/DC100%2/2
$options{"verbose"} or $cmd eq "checkconfig"
$options{"verbose"} $cmd eq "checkconfig"
Truth table100%3/3
A or B
A= $options{"verbose"}B= $cmd eq "checkconfig"
ABresult
1X1
011
000
781 3 Finance::Tiller2QIF::Util::CheckConfig(%options);
782 }
783  
784 21 ▶ if ( $options{checkpoint} || $cmd eq 'run' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($options{"checkpoint"} or $cmd eq "run") 7 14 21
Condition66%2/3
$options{"checkpoint"} or $cmd eq "run"
l!l&&r!l&&!r
7014
MC/DC50%1/2
$options{"checkpoint"} or $cmd eq "run"
$options{"checkpoint"} $cmd eq "run"
Truth table66%2/3
A or B
A= $options{"checkpoint"}B= $cmd eq "run"
ABresult
1X1
011
000
785 7 $options{checkpointfile} = _checkpoint( $options{db_path} );
786 }
787  
788 21 ▶ if ( $cmd =~ /^(?:ingest|run)$/ ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($cmd =~ /^(?:ingest|run)$/u) 8 13 21
789 8 vPrint( $options{verbose}, "Ingesting CSV: " . $options{input} );
790  
791 8 my $newitems = _ingest(%options);
792 8 say "Ingested: ${newitems} transactions from: " . $options{input};
793 }
794  
795 21 ▶ if ( $cmd =~ /^(?:map|run)$/ ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($cmd =~ /^(?:map|run)$/u) 5 16 21
796 5 ▶ if ( $options{mapfile} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($options{'mapfile'}) { } 2 3 5
797 2 ▶ say "Applying mapping: " . $options{mapfile} if $options{verbose};
Branch: 1 branch
BranchTrueFalseTotal
if $options{"verbose"} 1 1 2
798 2 _apply_map(%options);
799 2 say "Mapping applied: " . $options{mapfile};
800 }
801 else {
802 3 say "No mapfile provided, skipping mapping phase";
803 }
804 }
805  
806 21 ▶ if ( $cmd =~ /^(?:emit|run)$/ ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($cmd =~ /^(?:emit|run)$/u) 10 11 21
807 # uncoverable branch true
808 # uncoverable branch false
809 10 ▶ return unless _confirm ( %options );
Branch: 1 branch
BranchTrueFalseTotal
unless _confirm(%options) 2 8 10
810 8 vPrint( $options{verbose}, "Writing QIF: " . $options{output} );
811 8 my $changed = _emit(%options);
812 8 say "QIF written: ${\ $options{output}}, ${changed} records emitted!";
813 }
814  
815 19 ▶ if ( $cmd eq 'preview' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($cmd eq "preview") 7 12 19
816 7 my $count = _preview(%options);
817 5 say "${count} transaction(s) pending export.";
818 }
819 }
820  
821 2 1;
822  
823 =for Pod::Coverage run_cli
↑ Summary blib/lib/Finance/Tiller2QIF/Map.pm »