blib/lib/Finance/Tiller2QIF/Map.pm

filter: Statementstmt 97.7% 132 / 135 Branchbran 92.3% 72 / 78 Conditioncond 90.0% 18 / 20 MC/DCmcdc 81.8% 9 / 11 Subroutinesub 100% 14 / 14 Podpod 100% 1 / 1 totaltotal 94.9% 246 / 259 CC 54 SCAR 28.0
CC
54
Cov
95%
Map
25.3
_parse_mapping_file
20.0
_run_sql_file
6.0
CRAP 16.5
« blib/lib/Finance/Tiller2QIF.pm ↑ Summary blib/lib/Finance/Tiller2QIF/Interactive.pm »
1 package Finance::Tiller2QIF::Map;
2 # ABSTRACT: Apply mapping rules to categorize transactions
3 14 $Finance::Tiller2QIF::Map::VERSION = '1.23';
4 =head1 DESCRIPTION
5  
6 Applies user-defined mapping rules to transactions in the SQLite database. Rules can filter by account, match transaction fields (category, payee, memo, date, amount), and set or suppress categories. Rules are evaluated in order; the first match wins.
7  
8 =head1 FUNCTIONS
9  
10 =head2 Map
11  
12 Finance::Tiller2QIF::Map::Map( $db_path, $mapfile );
13  
14 Apply mapping rules from C<$mapfile> to transactions in the database. C<$mapfile> is optional; if omitted, transactions pass through unchanged. Rules are evaluated in order; the first matching rule sets the transaction's C<mapped_category> and/or C<skipped> flag.
15  
16 =head2 Rule ordering
17  
18 Rules are first-match-wins, so order matters. If a rule is not firing as expected, the most common cause is that an earlier rule is capturing the transaction first.
19  
20 Place specific overrides I<before> broad rules. For example, a C<payee | Wawa | blank> rule must appear before any C<category | Groceries | Expenses:Groceries> rule, or the category rule will match Wawa transactions first and the payee rule will never be evaluated.
21  
22 A good convention is to put specific payee and memo overrides near the top, and broad category-renaming rules (which map source categories to chart-of-accounts names) near the bottom. Category renaming is the least specific operation and should generally be last.
23  
24 =head1 AUTHOR
25  
26 John Karr E<lt>cpan@brainbuz.orgE<gt>
27  
28 =head1 LICENSE
29  
30 GPL version 3 or later.
31  
32 =cut
33  
34 14 ▶use 5.036;
Subroutine BEGIN: called CC=1 SCAR=0.0
35  
36 14 ▶use Finance::Tiller2QIF::DB qw( connect_db );
Subroutine BEGIN: called CC=1 SCAR=0.0
37 14 ▶use Path::Tiny;
Subroutine BEGIN: called CC=1 SCAR=0.0
38 14 ▶use utf8;
Subroutine BEGIN: called CC=1 SCAR=0.0
39 14 ▶use warnings FATAL => 'utf8';
Subroutine BEGIN: called CC=1 SCAR=0.0
40 14 ▶use feature qw/signatures postderef/;
Subroutine BEGIN: called CC=1 SCAR=0.0
41 14 ▶use open ':std', ':encoding(UTF-8)';
Subroutine BEGIN: called CC=1 SCAR=0.0
42  
43 14 ▶no warnings 'experimental::try';
Subroutine BEGIN: called CC=1 SCAR=0.0
44 14 ▶use feature 'try';
Subroutine BEGIN: called CC=1 SCAR=0.0
45  
46 14 my %VALID_FIELDS = map { $_ => 1 } qw(category payee memo account date amount);
47  
48 162 ▶sub _resolve_dest ($dest) {
Subroutine _resolve_dest: called CC=3 SCAR=11.0
49 162 ▶ return undef if $dest eq 'source';
Branch: 1 branch
BranchTrueFalseTotal
if $dest eq "source" 44 118 162
50 118 ▶ return '' if $dest eq 'blank';
Branch: 1 branch
BranchTrueFalseTotal
if $dest eq "blank" 8 110 118
51 110 return $dest;
52 }
53  
54 183 ▶sub _parse_line ($line) {
Subroutine _parse_line: called CC=4 SCAR=13.9
55 # Slash-delimited pattern: field | /regex/ | dest
56 183 ▶ if ( $line =~ /^([^|]+?)\s*\|\s*\/((?:[^\/]|\\.)*?)\/\s*\|\s*(.+?)$/ ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($line =~ m[^([^|]+?)\s*\|\s*/((?:[^/]|\\.)*?)/\s*\|\s*(.+?)$]u) 16 167 183
57 return (
58 16 scalar( $1 =~ s/^\s+|\s+$//gr ),
59 $2,
60 scalar( $3 =~ s/^\s+|\s+$//gr ),
61 );
62 }
63 # Simple three-field split: no | allowed in pattern except \| for literal pipe
64 167 my @parts = split /(?<!\\)\|/, $line;
65 167 ▶ die "Bare alternation without slash-quoting\n"
Branch: 1 branch
BranchTrueFalseTotal
if @parts > 3 2 165 167
66 if @parts > 3;
67  
68 165 @parts = map { s/^\s+|\s+$//gr } @parts;
69 165 my $field = shift @parts;
70 165 my $dest = pop @parts;
71 165 ▶ my $pattern = $parts[0] // '';
Condition100%2/2
$parts[0] // ""
l!l
11253
MC/DC100%1/1
$parts[0] // ""
$parts[0]
Truth table100%2/2
A
A= $parts[0]
Aresult
11
00
72 165 return ( $field, $pattern, $dest );
73 }
74  
75 91 ▶sub _parse_mapping_file ($file) {
Subroutine _parse_mapping_file: called CC=20 SCAR=30.0
76 91 my @rules;
77 91 my $default = undef; # parse-time constant: undef means use source category
78 91 my $default_skip = 0; # parse-time constant: reset into $skip at the top of each tx loop
79 # defined from default rule $dest.
80  
81 91 my $lineno = 0;
82 91 for my $line ( path($file)->lines_utf8({ chomp => 1 }) ) {
83 205 $lineno++;
84 205 ▶ next if $line =~ /^\s*(?:#|$)/;
Branch: 1 branch
BranchTrueFalseTotal
if $line =~ /^\s*(?:#|$)/u 21 184 205
85  
86 # Optional account filter: [AccountPattern] at start of line.
87 # Pattern follows the same alternation rules as field patterns.
88 184 my $acct_re = undef;
89 184 ▶ if ( $line =~ s/^\s*\[([^\]]+)\]\s*// ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($line =~ s/^\s*\[([^\]]+)\]\s*//u) 29 155 184
90 29 my $acct_pat = $1;
91 29 ▶ $acct_re = eval { qr/$acct_pat/i }
Branch: 1 branch
BranchTrueFalseTotal
$acct_re = eval { do { qr/$acct_pat/iu } } or die "Invalid account filter '[$acct_pat]' at line ${lineno}: $@\n" 28 1 29
92 or die "Invalid account filter '[$acct_pat]' at line $lineno: $@\n";
93 }
94  
95 183 my ($field, $pattern, $dest) = _parse_line($line);
96 181 $field = lc $field;
97 181 ▶ $dest =~ s/\\\|/|/g if defined $dest;
Branch: 1 branch
BranchTrueFalseTotal
if defined $dest 181 0 181
98  
99 181 ▶ if ( $field eq 'default' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($field eq "default") 53 128 181
100 53 ▶ die "Account filter not valid on default line at line $lineno\n"
Branch: 1 branch
BranchTrueFalseTotal
if defined $acct_re 1 52 53
101 if defined $acct_re;
102 52 ▶ die "Default line missing destination at line $lineno\n"
Branch: 1 branch
BranchTrueFalseTotal
unless defined $dest and length $dest 1 51 52
Condition66%2/3
defined $dest and length $dest
!ll&&!rl&&r
0151
MC/DC50%1/2
defined $dest and length $dest
defined $dest length $dest
Truth table66%2/3
A and B
A= defined $destB= length $dest
ABresult
0X0
100
111
103 unless defined $dest && length $dest;
104 51 ▶ $default_skip = ( $dest eq 'skip' ? 1 : 0 );
Branch: 1 branch
BranchTrueFalseTotal
$dest eq 'skip' ? : 2 49 51
105 51 ▶ $default = ( $dest eq 'skip' ? undef : _resolve_dest($dest) );
Branch: 1 branch
BranchTrueFalseTotal
$dest eq 'skip' ? : 2 49 51
106 51 next;
107 }
108  
109 die "Unknown field '$field' at line $lineno. Valid fields: "
110 . join( ', ', sort keys %VALID_FIELDS ) . "\n"
111 128 ▶ unless $VALID_FIELDS{$field};
Branch: 1 branch
BranchTrueFalseTotal
unless $VALID_FIELDS{$field} 4 124 128
112  
113 124 ▶ die "Missing pattern at line $lineno\n"
Branch: 1 branch
BranchTrueFalseTotal
unless length $pattern 1 123 124
114 unless length $pattern;
115  
116 123 ▶ die "Missing destination at line $lineno (use 'blank' for empty category)\n"
Branch: 1 branch
BranchTrueFalseTotal
unless defined $dest and length $dest 1 122 123
Condition66%2/3
defined $dest and length $dest
!ll&&!rl&&r
01122
MC/DC50%1/2
defined $dest and length $dest
defined $dest length $dest
Truth table66%2/3
A and B
A= defined $destB= length $dest
ABresult
0X0
100
111
117 unless defined $dest && length $dest;
118  
119 122 ▶ my $re_src = ( $pattern eq '*' ) ? '.*' : $pattern;
Branch: 1 branch
BranchTrueFalseTotal
$pattern eq '*' ? : 2 120 122
120 122 ▶ my $re = eval { qr/$re_src/i }
Branch: 1 branch
BranchTrueFalseTotal
my $re = eval { do { qr/$re_src/iu } } or die "Invalid regex '${pattern}' at line ${lineno}: $@\n" 120 2 122
121 or die "Invalid regex '$pattern' at line $lineno: $@\n";
122  
123 120 ▶ my $is_skip = ( $dest eq 'skip' ? 1 : 0 );
Branch: 1 branch
BranchTrueFalseTotal
$dest eq 'skip' ? : 7 113 120
124 120 ▶ push @rules, {
Branch: 1 branch
BranchTrueFalseTotal
$is_skip ? : 7 113 120
125 field => $field,
126 pattern => $re,
127 pattern_str => $pattern,
128 destination => ( $is_skip ? undef : _resolve_dest($dest) ),
129 skip => $is_skip,
130 account_filter => $acct_re,
131 };
132 }
133  
134 78 return ( \@rules, $default, $default_skip );
135 }
136  
137 9 ▶sub _run_sql_file ( $dbh, $file, $verbose ) {
Subroutine _run_sql_file: called CC=6 SCAR=17.9
138 9 my $sql = path($file)->slurp_utf8;
139 9 my @statements;
140 9 my $current = '';
141 9 for my $line ( split /\n/, $sql ) {
142 21 $current .= $line . "\n";
143 21 ▶ if ( $line =~ /;\s*$/ ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($line =~ /;\s*$/u) 19 2 21
144 19 push @statements, $current;
145 19 $current = '';
146 }
147 }
148 9 ▶ push @statements, $current if $current =~ /\S/;
Branch: 1 branch
BranchTrueFalseTotal
if $current =~ /\S/u 0 9 9
149 9 for my $stmt (@statements) {
150 19 my $rows = $dbh->do($stmt);
151 19 ▶ if ( $verbose ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($verbose) 4 15 19
152 4 ( my $summary = $stmt ) =~ s/\s+/ /g;
153 4 $summary =~ s/^\s+|\s+$//g;
154 # dbi returns 0E0 meaning 0 but true
155 4 say " " . int($rows) . " row(s) affected: $summary";
156 }
157 }
158 }
159  
160 92 ▶sub Map ( $options ) {
Subroutine Map: called CC=25 SCAR=32.3
Pod documented
161  
162 92 ▶ return unless defined $options->{mapfile};
Branch: 1 branch
BranchTrueFalseTotal
unless defined $options->{"mapfile"} 1 91 92
163 91 my $mapfile = $options->{mapfile};
164 91 my $db_path = $options->{db_path};
165 91 ▶ my $verbose = $options->{verbose} || 0;
Condition100%2/2
$options->{'verbose'} || 0
l!l
289
MC/DC100%1/1
$options->{'verbose'} || 0
$options->{'verbose'}
Truth table100%2/2
A
A= $options->{'verbose'}
Aresult
11
00
166 91 ▶ my $beforemap = $options->{beforemap} // undef;
Condition100%2/2
$options->{"beforemap"} // undef
l!l
883
MC/DC100%1/1
$options->{"beforemap"} // undef
$options->{"beforemap"}
Truth table100%2/2
A
A= $options->{"beforemap"}
Aresult
11
00
167 91 ▶ my $aftermap = $options->{aftermap} // undef;
Condition100%2/2
$options->{"aftermap"} // undef
l!l
883
MC/DC100%1/1
$options->{"aftermap"} // undef
$options->{"aftermap"}
Truth table100%2/2
A
A= $options->{"aftermap"}
Aresult
11
00
168 91 my ( $rules, $default, $default_skip ) = _parse_mapping_file($mapfile);
169  
170 78 my $dbh = connect_db($db_path);
171 78 ▶ $options->{prepare_db}->($dbh) if $options->{prepare_db};
Branch: 1 branch
BranchTrueFalseTotal
if $options->{"prepare_db"} 22 56 78
172 78 ▶ my $run_sql = $options->{sql_runner} // \&_run_sql_file;
Condition100%2/2
$options->{"sql_runner"} // \&Finance::Tiller2QIF::Map::_run_sql_file
l!l
2256
MC/DC100%1/1
$options->{"sql_runner"} // \&Finance::Tiller2QIF::Map::_run_sql_file
$options->{"sql_runner"}
Truth table100%2/2
A
A= $options->{"sql_runner"}
Aresult
11
00
173  
174 78 ▶ if ( defined $beforemap ) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $beforemap) 8 70 78
175 8 ▶ say "Running beforemap: $beforemap" if $verbose;
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 1 7 8
176 8 try {
177 8 $run_sql->( $dbh, $beforemap, $verbose );
178 8 ▶ say "beforemap completed successfully" if $verbose;
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 1 7 8
179 }
180 0 ▶ catch ($e) { die "beforemap error: $e" if $options->{strict}; warn "beforemap error: $e" }
Branch: 1 branch
BranchTrueFalseTotal
if $options->{"strict"} 0 0 0
181 }
182  
183 78 my $select = $dbh->prepare('SELECT * FROM transactions WHERE exported = 0');
184 78 $select->execute;
185 78 my @transactions = $select->fetchall_arrayref({})->@*;
186 78 my $update = $dbh->prepare(
187 'UPDATE transactions SET mapped_category = ?, skipped = ?, exported = ? WHERE id = ?'
188 );
189  
190 78 for my $tx (@transactions) {
191 162 my $mc = $default; # reset each tx; overwritten if a rule matches
192 162 my $skip = $default_skip; # reset each tx; overwritten if a rule matches
193 162 my $matched_rule;
194  
195 162 MAPRULE: for my $rule (@$rules) {
196 390 ▶ if ( defined $rule->{account_filter} ) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $rule->{"account_filter"}) 72 318 390
197 72 ▶ next MAPRULE unless $tx->{account} =~ $rule->{account_filter};
Branch: 1 branch
BranchTrueFalseTotal
unless $tx->{"account"} =~ /$rule->{"account_filter"}/u 38 34 72
198 }
199 352 my $val = $tx->{ $rule->{field} };
200 352 ▶ next MAPRULE unless defined $val;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $val 0 352 352
201 352 ▶ if ( $val =~ $rule->{pattern} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($val =~ /$rule->{"pattern"}/u) 127 225 352
202 127 $mc = $rule->{destination};
203 127 $skip = $rule->{skip};
204 127 ▶ $matched_rule = "$rule->{field} ~ /$rule->{pattern_str}/ => " . ($rule->{skip} ? 'skip' : ($mc // 'source'));
Branch: 1 branch
BranchTrueFalseTotal
$rule->{'skip'} ? : 8 119 127
Condition100%2/2
$mc // "source"
l!l
1181
MC/DC100%1/1
$mc // "source"
$mc
Truth table100%2/2
A
A= $mc
Aresult
11
00
205 127 last MAPRULE;
206 }
207 }
208  
209 162 ▶ if ($verbose) {
Branch: 1 branch
BranchTrueFalseTotal
if ($verbose) 6 156 162
210 6 ▶ my $result = $matched_rule // 'default';
Condition100%2/2
$matched_rule // "default"
l!l
42
MC/DC100%1/1
$matched_rule // "default"
$matched_rule
Truth table100%2/2
A
A= $matched_rule
Aresult
11
00
211 6 say "TX $tx->{id} ($tx->{payee}): $result";
212 }
213  
214 162 $update->execute( $mc, $skip, $skip, $tx->{id} );
215 }
216  
217 78 ▶ if ( defined $aftermap ) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $aftermap) 8 70 78
218 8 ▶ say "Running aftermap: $aftermap" if $verbose;
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 1 7 8
219 8 try {
220 8 $run_sql->( $dbh, $aftermap, $verbose );
221 7 ▶ say "aftermap completed successfully" if $verbose;
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 1 6 7
222 }
223 0 ▶ catch ($e) { die "aftermap error: $e" if $options->{strict}; warn "aftermap error: $e" }
Branch: 1 branch
BranchTrueFalseTotal
if $options->{"strict"} 1 0 1
224 }
225  
226 77 $dbh->disconnect;
227 }
228  
229 14 1;
« blib/lib/Finance/Tiller2QIF.pm ↑ Summary blib/lib/Finance/Tiller2QIF/Interactive.pm »