blib/lib/Finance/Tiller2QIF/WriteQIF.pm

filter: Statementstmt 94.5% 122 / 128 (1 uncoverable) Branchbran 78.5% 22 / 28 Conditioncond 73.6% 14 / 19 MC/DCmcdc 45.4% 5 / 11 Subroutinesub 95.4% 21 / 22 Podpod 100% 2 / 2 totaltotal 88.1% 186 / 210 (1 uncoverable) CC 34 SCAR 17.4
CC
34
Cov
90%
_launch_windows_viewer
12.0
_launch_viewer
8.2
Preview
8.1
CRAP 5.7
« blib/lib/Finance/Tiller2QIF/Util.pm ↑ Summary blib/lib/Finance/Tiller2QIF/DB.pm »
1 package Finance::Tiller2QIF::WriteQIF;
2 # ABSTRACT: Write transactions to QIF format
3 8 $Finance::Tiller2QIF::WriteQIF::VERSION = '1.23';
4 =encoding utf8
5  
6 =head1 DESCRIPTION
7  
8 Exports transactions from the SQLite database to QIF (Quicken Interchange Format) for import into financial software. Transactions are grouped by account and sorted by date. Skipped transactions and those without effective categories are handled appropriately.
9  
10 =head1 FUNCTIONS
11  
12 =head2 Emit
13  
14 Finance::Tiller2QIF::WriteQIF::Emit( $db_path, $outfile );
15  
16 Write all unexported, non-skipped transactions from the database to a QIF file. Each account is written as a separate QIF account block with transactions sorted by date then payee. Marks written transactions as exported in the database.
17  
18 =head2 Preview
19  
20 Finance::Tiller2QIF::WriteQIF::Preview( $db_path );
21 Finance::Tiller2QIF::WriteQIF::Preview( $db_path, $verbose, $viewer );
22 Finance::Tiller2QIF::WriteQIF::Preview( $db_path, $verbose, $viewer, $mapfile );
23  
24 Display all unexported, non-skipped transactions as labeled blocks. The first line shows transaction ID, date, and account; indented lines show amount, payee, memo, and category without truncation. Shows original category in brackets if mapped to a different category. Returns the count of transactions displayed.
25  
26 C<$viewer> selects where the table goes. The default, C<console>, prints to STDOUT. Any other value is used as-is as a shell command line (optionally with arguments, e.g. C<code --wait>); the table is written to a read-only temporary file with a C<.t2qpv> suffix, and the command is run with that file's path (shell-quoted) appended, then the path is printed. Because the whole line goes to the shell, ordinary shell quoting works — a program path containing spaces can be double- or single-quoted within C<$viewer> itself. The temporary file is left in place for the viewer to read. An undefined or blank viewer is fatal, and so is anything other than a normal, successful run of the command — there is no silent fallback to the console, and no attempt to report why it failed beyond that.
27  
28 Any further arguments are additional files to open alongside the preview, in the same viewer invocation; the CLI passes the mapping file here for C<--multipreview>. They require a real viewer (not C<console>) and must be readable, or the call dies.
29  
30 On Windows, the command runs through C<cmd.exe> with Windows quoting for the file arguments. Use double quotes around program paths containing spaces. Delayed environment-variable expansion is disabled so file names containing C<!> remain literal.
31  
32 =head1 AUTHOR
33  
34 John Karr E<lt>cpan@brainbuz.orgE<gt>
35  
36 =head1 LICENSE
37  
38 GPL version 3 or later.
39  
40 =cut
41  
42 8 ▶use 5.036;
Subroutine BEGIN: called CC=1 SCAR=0.0
43  
44 8 ▶use Path::Tiny;
Subroutine BEGIN: called CC=1 SCAR=0.0
45 8 ▶use Text::CSV;
Subroutine BEGIN: called CC=1 SCAR=0.0
46 8 ▶use File::Temp ();
Subroutine BEGIN: called CC=1 SCAR=0.0
47 8 ▶use Finance::Tiller2QIF::DB qw( connect_db );
Subroutine BEGIN: called CC=1 SCAR=0.0
48 8 ▶use utf8;
Subroutine BEGIN: called CC=1 SCAR=0.0
49 8 ▶use warnings FATAL => 'utf8';
Subroutine BEGIN: called CC=1 SCAR=0.0
50 8 ▶use open ':std', ':encoding(UTF-8)';
Subroutine BEGIN: called CC=1 SCAR=0.0
51 8 ▶use feature qw/signatures postderef/;
Subroutine BEGIN: called CC=1 SCAR=0.0
52 # use Data::Printer;
53  
54 75 ▶sub _init($db_path) {
Subroutine _init: called CC=1 SCAR=0.0
55 75 my $dbh = connect_db($db_path);
56 75 my $sth = $dbh->prepare(
57 q{SELECT distinct(account) FROM transactions WHERE exported = 0 AND skipped = 0;}
58 );
59 75 $sth->execute;
60 75 my @accounts = map { $_->[0] } $sth->fetchall_arrayref->@*;
61 75 return ( $dbh, \@accounts );
62 }
63  
64 my %_date_fmt = (
65 74 ▶ ymd => sub { $_[0] }, # database uses ymd, no transformation
Subroutine __ANON__: called CC=1 SCAR=0.0
66 1 ▶ mdy => sub { my ($y,$m,$d) = split /-/, $_[0]; "$m/$d/$y" },
Subroutine __ANON__: called CC=1 SCAR=0.0
67 1 ▶ dmy => sub { my ($y,$m,$d) = split /-/, $_[0]; "$d/$m/$y" },
Subroutine __ANON__: called CC=1 SCAR=0.0
68 8 );
69  
70 76 ▶sub _format_date ( $iso_date, $fmt ) {
Subroutine _format_date: called CC=1 SCAR=0.0
71 76 my $fn = $_date_fmt{ $fmt };
72 76 $fn->($iso_date);
73 }
74  
75 38 ▶sub Emit ( $db_path, $outfile, $verbose=0, $qifdate='ymd' ) {
Subroutine Emit: called CC=7 SCAR=19.5
Pod documented
76 38 my ( $dbh, $accounts ) = _init($db_path);
77 38 my @qif;
78 38 my $emitted = 0;
79 38 for my $account (@$accounts) {
80 42 my $header = join( "\n", "!Account", "N$account", "^", "!Type:Bank" );
81 42 my $sth = $dbh->prepare(
82 q{ SELECT *,
83 COALESCE(mapped_category, category) AS effective_category
84 FROM transactions
85 WHERE exported = 0
86 AND skipped = 0
87 AND account = ?
88 ORDER BY date, payee; }
89 );
90 42 $sth->execute($account);
91 42 my @tx = $sth->fetchall_arrayref({})->@*;
92 my @qif_tx = map {
93 42 my $date = _format_date( $_->{date}, $qifdate );
94 join( "\n",
95 "D$date", sprintf("T%.2f", $_->{amount}),
96 ( $_->{check_number} ? "N$_->{check_number}" : () ),
97 "P$_->{payee}",
98 ( $_->{memo} ? "M$_->{memo}" : () ),
99 76 ▶ ( $_->{effective_category} ? "L$_->{effective_category}" : () ), "^" )
Branch: 3 branches
BranchTrueFalseTotal
$_->{'check_number'} ? : 2 74 76
$_->{'memo'} ? : 75 1 76
$_->{'effective_category'} ? : 75 1 76
100 } @tx;
101 42 $emitted += scalar @tx;
102 42 push @qif, $header, @qif_tx;
103 }
104  
105 # Combine all QIF fragments into a single multi-account QIF and write to file
106  
107 38 path($outfile)->spew_utf8( join( "\n", @qif ) . "\n" );
108  
109 38 $dbh->do('UPDATE transactions SET exported = 1 WHERE exported = 0');
110 38 $dbh->disconnect;
111 38 return $emitted;
112 }
113  
114 114 ▶sub _preview_inline ($value) {
Subroutine _preview_inline: called CC=2 SCAR=7.0
115 114 ▶ $value //= '';
Condition50%1/2
$value //= ""
l!l
1140
MC/DC0%0/1
$value //= ""
$value
Truth table50%1/2
A
A= $value
Aresult
11
00
116 114 $value =~ s/\\/\\\\/g;
117 114 $value =~ s/\r/\\r/g;
118 114 $value =~ s/\n/\\n/g;
119 114 $value =~ s/\t/\\t/g;
120 114 return $value;
121 }
122  
123 152 ▶sub _preview_field ($label, $value) {
Subroutine _preview_field: called CC=4 SCAR=13.9
124 152 ▶ $value //= '';
Condition100%2/2
$value //= ""
l!l
1502
MC/DC100%1/1
$value //= ""
$value
Truth table100%2/2
A
A= $value
Aresult
11
00
125 152 $value =~ s/\r\n?/\n/g;
126 152 my ($first, @rest) = split /\n/, $value, -1;
127 152 ▶ my $text = sprintf " %-10s%s\n", "$label:", $first // '';
Condition100%2/2
$first // ""
l!l
1493
MC/DC100%1/1
$first // ""
$first
Truth table100%2/2
A
A= $first
Aresult
11
00
128 152 $text .= " $_\n" for @rest;
129 152 return $text;
130 }
131  
132 37 ▶sub _preview_text ($db_path) {
Subroutine _preview_text: called CC=8 SCAR=20.8
133 37 my ( $dbh, $accounts ) = _init($db_path);
134  
135 37 my @rows;
136  
137 37 for my $account (@$accounts) {
138 34 my $sth = $dbh->prepare(
139 q{ SELECT * FROM transactions
140 WHERE exported = 0 AND skipped = 0 AND account = ?
141 ORDER BY date, payee; }
142 );
143 34 $sth->execute($account);
144 34 my @tx = $sth->fetchall_arrayref({})->@*;
145  
146 34 for my $tx (@tx) {
147 38 ▶ my $orig = $tx->{category} // '';
Condition100%2/2
$tx->{"category"} // ""
l!l
371
MC/DC100%1/1
$tx->{"category"} // ""
$tx->{"category"}
Truth table100%2/2
A
A= $tx->{"category"}
Aresult
11
00
148 38 ▶ my $mapped = $tx->{mapped_category} // $orig; # uncoverable statement
Condition66%2/3
$tx->{"mapped_category"} // $orig
l!l&&r!l&&!r
22160
MC/DC0%0/2
$tx->{"mapped_category"} // $orig
$tx->{"mapped_category"} $orig
Truth table66%2/3
A // B
A= $tx->{"mapped_category"}B= $orig
ABresult
1X1
011
000
149 38 ▶ my $cat = $mapped ne $orig
Branch: 1 branch
BranchTrueFalseTotal
$mapped ne $orig ? : 19 19 38
150 ? '[' . $orig . '] ' . $mapped
151 : $mapped;
152  
153 push @rows, {
154 id => $tx->{id},
155 account => $account,
156 date => $tx->{date},
157 amount => sprintf( "%.2f", $tx->{amount} ),
158 payee => $tx->{payee},
159 cat => $cat,
160 memo => $tx->{memo},
161 38 };
162 }
163 }
164  
165 37 ▶ my $text = @rows ? '' : "No transactions pending export.\n";
Branch: 1 branch
BranchTrueFalseTotal
@rows ? : 34 3 37
166 37 for my $row (@rows) {
167 $text .= sprintf "ID: %s | Date: %s | Account: %s\n",
168 38 map { _preview_inline($_) } @{$row}{qw(id date account)};
169 38 $text .= _preview_field('Amount', $row->{amount});
170 38 $text .= _preview_field('Payee', $row->{payee});
171 38 $text .= _preview_field('Memo', $row->{memo});
172 38 $text .= _preview_field('Category', $row->{cat});
173 38 $text .= "\n";
174 }
175  
176 37 $dbh->disconnect;
177 37 return ( scalar @rows, $text );
178 }
179  
180 15 ▶sub _shell_quote ($str) {
Subroutine _shell_quote: called CC=1 SCAR=0.0
181 15 $str =~ s/'/'\\''/g;
182 15 return "'$str'";
183 }
184  
185 0 ▶sub _launch_windows_viewer ($viewer, @files) {
Subroutine _launch_windows_viewer: not called CC=3 SCAR=24.8
186 0 require Win32::ShellQuote;
187 0 my $cmd = join ' ', $viewer, Win32::ShellQuote::quote_cmd(@files);
188 0 ▶ my $shell = $ENV{COMSPEC} // 'cmd.exe';
Condition0%0/2
$ENV{"COMSPEC"} // "cmd.exe"
l!l
00
MC/DC0%0/1
$ENV{"COMSPEC"} // "cmd.exe"
$ENV{"COMSPEC"}
Truth table0%0/2
A
A= $ENV{"COMSPEC"}
Aresult
11
00
189 # Force cmd.exe so quoting does not depend on Perl's shell autodetection.
190 # Disable AutoRun and delayed expansion so file names containing ! survive.
191 # Perl adds the outer quotes for /c; adding another pair breaks viewers
192 # whose executable path is already quoted.
193 die "preview viewer '$viewer' failed\n"
194 0 ▶ if system { $shell } $shell, '/d', '/v:off', '/s', '/c', $cmd;
Branch: 1 branch
BranchTrueFalseTotal
if system {$shell;} $shell, "/d", "/v:off", "/s", "/c", $cmd 0 0 0
195 }
196  
197 7 ▶sub _launch_viewer ( $viewer, $text, @also ) {
Subroutine _launch_viewer: called CC=8 SCAR=21.1
198 7 for my $extra (@also) {
199 2 ▶ die "preview cannot open '$extra': it does not exist or can't be read\n"
Branch: 1 branch
BranchTrueFalseTotal
unless -r $extra 1 1 2
200 unless -r $extra;
201 }
202  
203 6 my ( $fh, $file ) = File::Temp::tempfile(
204 'tiller2qif-preview-XXXXXXXX',
205 SUFFIX => '.t2qpv',
206 TMPDIR => 1,
207 UNLINK => 0,
208 );
209 6 binmode $fh, ':encoding(UTF-8)';
210 6 print {$fh} $text;
211 6 ▶ close $fh or die "unable to write preview file $file: $!\n";
Branch: 1 branch
BranchTrueFalseTotal
close $fh or die "unable to write preview file ${file}: $!\n" 6 0 6
212 6 ▶ chmod 0400, $file or die "unable to make preview file $file read-only: $!\n";
Branch: 1 branch
BranchTrueFalseTotal
chmod 0400, $file or die "unable to make preview file $file read-only: $!\n" 6 0 6
213  
214 6 say "Preview written to $file";
215 6 say "Also opening: $_" for @also;
216 6 ▶ if ($^O eq 'MSWin32') {
Branch: 1 branch
BranchTrueFalseTotal
if ($^O eq 'MSWin32') { } 0 6 6
217 0 _launch_windows_viewer($viewer, $file, @also);
218 }
219 else {
220 6 my $cmd = join ' ', $viewer, map { _shell_quote($_) } $file, @also;
221 6 ▶ die "preview viewer '$viewer' failed\n" if system($cmd);
Branch: 1 branch
BranchTrueFalseTotal
if system $cmd 2 4 6
222 }
223 4 return $file;
224 }
225  
226 15 ▶sub Preview ( $db_path, $verbose=0, $viewer='console', @also ) {
Subroutine Preview: called CC=8 SCAR=20.9
Pod documented
227 15 ▶ die "preview viewer is not set; use 'console' to print to STDOUT\n"
Branch: 1 branch
BranchTrueFalseTotal
unless defined $viewer and $viewer =~ /\S/u 2 13 15
Condition100%3/3
defined $viewer and $viewer =~ /\S/u
!ll&&!rl&&r
1113
MC/DC100%2/2
defined $viewer and $viewer =~ /\S/u
defined $viewer $viewer =~ /\S/u
Truth table100%3/3
A and B
A= defined $viewerB= $viewer =~ /\S/u
ABresult
0X0
100
111
228 unless defined $viewer && $viewer =~ /\S/;
229 13 ▶ die "opening additional files requires a preview viewer, not 'console'\n"
Branch: 1 branch
BranchTrueFalseTotal
if @also and lc $viewer eq "console" 0 13 13
Condition66%2/3
@also and lc $viewer eq "console"
!ll&&!rl&&r
1120
MC/DC0%0/2
@also and lc $viewer eq "console"
@also lc $viewer eq "console"
Truth table66%2/3
A and B
A= @alsoB= lc $viewer eq "console"
ABresult
0X0
100
111
230 if @also && lc $viewer eq 'console';
231  
232 13 my ( $count, $text ) = _preview_text($db_path);
233  
234 13 ▶ if ( lc $viewer eq 'console' ) { print $text }
Branch: 1 branch
BranchTrueFalseTotal
if (lc $viewer eq 'console') { } 6 7 13
235 7 else { _launch_viewer( $viewer, $text, @also ) }
236  
237 10 return $count;
238 }
239  
240 8 1;
« blib/lib/Finance/Tiller2QIF/Util.pm ↑ Summary blib/lib/Finance/Tiller2QIF/DB.pm »