blib/lib/Finance/Tiller2QIF/ReadCSV.pm

filter: Statementstmt 99.0% 107 / 108 Branchbran 87.5% 28 / 32 Conditioncond 76.4% 26 / 34 MC/DCmcdc 52.9% 9 / 17 Subroutinesub 100% 18 / 18 Podpod 100% 1 / 1 totaltotal 90.0% 189 / 210 CC 34 SCAR 22.3
CC
34
Cov
93%
_insert_row
17.6
Ingest
10.1
_normalize_amount
7.0
CRAP 9.3
« blib/lib/Finance/Tiller2QIF/Interactive.pm ↑ Summary blib/lib/Finance/Tiller2QIF/Util.pm »
1 package Finance::Tiller2QIF::ReadCSV;
2 # ABSTRACT: Read and parse Tiller CSV export files
3 15 $Finance::Tiller2QIF::ReadCSV::VERSION = '1.23';
4 =head1 DESCRIPTION
5  
6 Ingests Tiller CSV exports into a SQLite database.
7  
8 =head1 FUNCTIONS
9  
10 =head2 Ingest
11  
12 Finance::Tiller2QIF::ReadCSV::Ingest( $csv_file, $db_path );
13  
14 Parse a Tiller CSV export and insert rows into the SQLite database. Handles multiple number formats (US, European, with currency symbols). Dates are normalized to YYYY-MM-DD format. Missing Transaction IDs cause the row to be skipped.
15  
16 =head1 AUTHOR
17  
18 John Karr E<lt>cpan@brainbuz.orgE<gt>
19  
20 =head1 LICENSE
21  
22 GPL version 3 or later.
23  
24 =cut
25  
26 15 ▶use 5.036;
Subroutine BEGIN: called CC=1 SCAR=0.0
27  
28 15 ▶use Path::Tiny;
Subroutine BEGIN: called CC=1 SCAR=0.0
29 15 ▶use Text::CSV;
Subroutine BEGIN: called CC=1 SCAR=0.0
30 15 ▶use Finance::Tiller2QIF::DB qw( connect_db );
Subroutine BEGIN: called CC=1 SCAR=0.0
31 15 ▶use utf8;
Subroutine BEGIN: called CC=1 SCAR=0.0
32 15 ▶use warnings FATAL => 'utf8';
Subroutine BEGIN: called CC=1 SCAR=0.0
33 15 ▶use feature qw/signatures postderef/;
Subroutine BEGIN: called CC=1 SCAR=0.0
34 15 ▶use DateTime::Format::Flexible;
Subroutine BEGIN: called CC=1 SCAR=0.0
35 15 ▶use Finance::Tiller2QIF::Util qw( vPrint );
Subroutine BEGIN: called CC=1 SCAR=0.0
36  
37 # Enable experimental try/catch for Perl 5.32, suppressing warnings
38 15 ▶no warnings 'experimental::try';
Subroutine BEGIN: called CC=1 SCAR=0.0
39 15 ▶use feature 'try';
Subroutine BEGIN: called CC=1 SCAR=0.0
40  
41 237 ▶sub _normalize_amount ($raw) {
Subroutine _normalize_amount: called CC=7 SCAR=19.5
42 237 my $amount = $raw;
43 237 $amount =~ s/[£€¥₹\$]//g;
44 237 $amount =~ s/\s//g;
45 237 ▶ return $amount unless length $amount;
Branch: 1 branch
BranchTrueFalseTotal
unless length $amount 3 234 237
46  
47 234 my $last_dot = rindex( $amount, '.' );
48 234 my $last_comma = rindex( $amount, ',' );
49  
50 234 ▶ if ( $last_dot >= 0 && $last_comma >= 0 ) {
Branch: 2 branches
BranchTrueFalseTotal
if ($last_dot >= 0 and $last_comma >= 0) { } 5 229 234
elsif ($last_comma >= 0) { } 5 224 229
Condition100%3/3
$last_dot >= 0 and $last_comma >= 0
!ll&&!rl&&r
72225
MC/DC100%2/2
$last_dot >= 0 and $last_comma >= 0
$last_dot >= 0 $last_comma >= 0
Truth table100%3/3
A and B
A= $last_dot >= 0B= $last_comma >= 0
ABresult
0X0
100
111
51 5 ▶ if ( $last_comma > $last_dot ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($last_comma > $last_dot) { } 3 2 5
52 # European format: 1.234,56 — dot=thousands, comma=decimal
53 3 $amount =~ s/\.//g;
54 3 $amount =~ s/,/./;
55 }
56 else {
57 # US format: 1,234.56 — comma=thousands, dot=decimal
58 2 $amount =~ s/,//g;
59 }
60 }
61 elsif ( $last_comma >= 0 ) {
62 5 ▶ if ( $amount =~ /,\d{1,2}$/ ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($amount =~ /,\d{1,2}$/u) { } 3 2 5
63 # European decimal without thousands: 100,50
64 3 $amount =~ s/,/./;
65 }
66 else {
67 # US thousands without decimal: 1,000
68 2 $amount =~ s/,//g;
69 }
70 }
71  
72 234 return $amount;
73 }
74  
75 123 ▶sub _prepare_insert ($dbdbi) {
Subroutine _prepare_insert: called CC=1 SCAR=0.0
76 123 $dbdbi->prepare(
77 q{
78 INSERT OR IGNORE INTO transactions (id, account, date, amount, payee, memo, category, check_number)
79 VALUES (?, ?, ?, ?, ?, ?, ?, ?)
80 }
81 );
82 }
83  
84 15 my @REQUIRED_HEADERS = ( 'Date', 'Transaction ID', 'Account', 'Amount', 'Description' );
85  
86 124 ▶sub _validate_csv ( $csv_file, $csv, $fh, $header ) {
Subroutine _validate_csv: called CC=3 SCAR=11.1
87 124 my %found = map { $_ => 1 } @$header;
88 124 my @missing = grep { !$found{$_} } @REQUIRED_HEADERS;
89 124 ▶ if (@missing) {
Branch: 1 branch
BranchTrueFalseTotal
if (@missing) 2 122 124
90 2 die "CSV validation failed for $csv_file: missing required column(s): "
91 . join( ', ', @missing ) . "\n";
92 }
93 122 ▶ if ( $csv->error_diag() ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($csv->error_diag) 0 122 122
94 0 die "CSV parse error in $csv_file: " . $csv->error_diag() . "\n";
95 }
96 }
97  
98 122 ▶sub _prepare_count ($dbdbi) {
Subroutine _prepare_count: called CC=1 SCAR=0.0
99 122 $dbdbi->prepare(
100 q{ SELECT COUNT (*) FROM transactions; }
101 );
102 }
103  
104 224 ▶sub _insert_row ( $insert, $columns, $row, $verbose, $strict=0 ) {
Subroutine _insert_row: called CC=17 SCAR=28.7
105 224 my %r;
106 224 @r{@$columns} = @$row;
107 224 ▶ my $amount = _normalize_amount( $r{'Amount'} // '' );
Condition50%1/2
$r{"Amount"} // ""
l!l
2240
MC/DC0%0/1
$r{"Amount"} // ""
$r{"Amount"}
Truth table50%1/2
A
A= $r{"Amount"}
Aresult
11
00
108 224 ▶ my $date = $r{'Date'} // '';
Condition50%1/2
$r{"Date"} // ""
l!l
2240
MC/DC0%0/1
$r{"Date"} // ""
$r{"Date"}
Truth table50%1/2
A
A= $r{"Date"}
Aresult
11
00
109 224 ▶ my $rowstr = join( ", ", map { $_ // '' } @r{@$columns} );
Condition50%1/2
$_ // ""
l!l
16740
MC/DC0%0/1
$_ // ""
$_
Truth table50%1/2
A
A= $_
Aresult
11
00
110 224 my $dt;
111 224 try {
112 224 $dt = DateTime::Format::Flexible->parse_datetime($date);
113 221 $date = $dt->ymd;
114 }
115 catch ($e) {
116 3 ▶ die "Could not parse date '$date': $e" if $strict;
Branch: 1 branch
BranchTrueFalseTotal
if $strict 1 2 3
117 2 print "[WARN] Could not parse date '$date' for record: $rowstr\n";
118 2 return;
119 }
120 221 my $id = $r{'Transaction ID'};
121 221 ▶ if ( !$id ) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($id) 2 219 221
122 2 ▶ die "Missing Transaction ID\n" if $strict;
Branch: 1 branch
BranchTrueFalseTotal
if $strict 0 2 2
123 2 return;
124 }
125 219 ▶ my $account = $r{'Account'} || '';
Condition50%1/2
$r{'Account'} || ''
l!l
2190
MC/DC0%0/1
$r{'Account'} || ''
$r{'Account'}
Truth table50%1/2
A
A= $r{'Account'}
Aresult
11
00
126 219 ▶ my $payee = $r{'Description'} || $r{'Full Description'} // '';
Condition60%3/5
($r{"Description"} || $r{"Full Description"}) // ""
l!l
2190
$r{"Description"} or $r{"Full Description"}
l!l&&r!l&&!r
21450
MC/DC0%0/2
($r{"Description"} || $r{"Full Description"}) // ""
$r{"Description"} $r{"Full Description"}
Truth table66%2/3
A or B
A= $r{"Description"}B= $r{"Full Description"}
ABresult
1X1
011
000
127 219 ▶ my $memo = $r{'Full Description'} || '';
Condition100%2/2
$r{'Full Description'} || ''
l!l
2172
MC/DC100%1/1
$r{'Full Description'} || ''
$r{'Full Description'}
Truth table100%2/2
A
A= $r{'Full Description'}
Aresult
11
00
128 219 ▶ my $cat = $r{'Category'} || '';
Condition100%2/2
$r{'Category'} || ''
l!l
2163
MC/DC100%1/1
$r{'Category'} || ''
$r{'Category'}
Truth table100%2/2
A
A= $r{'Category'}
Aresult
11
00
129 219 ▶ my $check_num = $r{'Check Number'} // '';
Condition100%2/2
$r{"Check Number"} // ""
l!l
14205
MC/DC100%1/1
$r{"Check Number"} // ""
$r{"Check Number"}
Truth table100%2/2
A
A= $r{"Check Number"}
Aresult
11
00
130 219 ▶ $check_num = undef if !length($check_num) || $check_num eq '0';
Branch: 1 branch
BranchTrueFalseTotal
if not length $check_num or $check_num eq 0 217 2 219
Condition100%3/3
not length $check_num or $check_num eq 0
l!l&&r!l&&!r
21522
MC/DC100%2/2
not length $check_num or $check_num eq 0
not length $check_num $check_num eq 0
Truth table100%3/3
not(A) or B
A= not length $check_numB= $check_num eq 0
ABresult
1X1
011
000
131 219 try {
132 # if the insert fails catch will report the issue.
133 219 ▶ local $SIG{__WARN__} = sub {};
Subroutine __ANON__: called CC=1 SCAR=0.0
134 219 $insert->execute( $id, $account, $date, $amount, $payee, $memo, $cat, $check_num );
135 218 vPrint( $verbose, "Ingest: $rowstr" );
136 }
137 catch ($e) {
138 1 ▶ die "Failed to import transaction '$id': $e" if $strict;
Branch: 1 branch
BranchTrueFalseTotal
if $strict 0 1 1
139 1 say "Failed to import row to transactions ${rowstr} \n$e ";
140 }
141 }
142  
143 126 ▶sub Ingest ( $csv_file, $db_path, $verbose=0, $strict=0 ) {
Subroutine Ingest: called CC=10 SCAR=23.1
Pod documented
144 126 my $dbdbi = connect_db($db_path);
145 126 my $csv = Text::CSV->new({ binary => 1 });
146 126 ▶ my $fh = path($csv_file)->openr_utf8 or die "File not found: $csv_file\n";
Branch: 1 branch
BranchTrueFalseTotal
my $fh = path($csv_file)->openr_utf8 or die "File not found: $csv_file\n" 125 0 125
147 125 ▶ my $header = $csv->getline($fh)
Branch: 1 branch
BranchTrueFalseTotal
my $header = $csv->getline($fh) or die "CSV appears empty or unreadable\n" 124 1 125
148 or die "CSV appears empty or unreadable\n";
149 124 _validate_csv( $csv_file, $csv, $fh, $header );
150  
151 122 my $insert = _prepare_insert($dbdbi);
152 122 my $counter = _prepare_count($dbdbi);
153 122 $counter->execute();
154 122 my ($StartCnt) = $counter->fetchrow_array;
155  
156 122 ▶ while ( my $row = $csv->getline($fh) ) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $row = $csv->getline($fh)) 223 121 344
157 223 _insert_row( $insert, $header, $row, $verbose, $strict );
158 }
159 121 my ($csv_error, $csv_message) = $csv->error_diag;
160 # eof() is also true for malformed input such as an unterminated final quote.
161 # Text::CSV reports 2012 for ordinary end of input; other codes are failures.
162 121 ▶ die "CSV parse error: $csv_message\n"
Branch: 1 branch
BranchTrueFalseTotal
if $strict and !$csv->eof || $csv_error && $csv_error != 2012 1 120 121
Condition77%7/9
$strict and !$csv->eof || $csv_error && $csv_error != 2012
!ll&&!rl&&r
94261
not $csv->eof or $csv_error and $csv_error != 2012
l!l&&r!l&&!r
0126
$csv_error and $csv_error != 2012
!ll&&!rl&&r
0261
MC/DC50%2/4
$strict and !$csv->eof || $csv_error && $csv_error != 2012
$strict not $csv->eof $csv_error $csv_error != 2012
Truth table60%3/5
A and not(B) or C and D
A= $strictB= not $csv->eofC= $csv_errorD= $csv_error != 2012
ABCDresult
0XXX0
100X0
10100
11XX1
10111
163 if $strict && (!$csv->eof || ($csv_error && $csv_error != 2012));
164 120 $counter->execute();
165 120 my ($EndCnt) = $counter->fetchrow_array;
166 120 return ( $EndCnt - $StartCnt );
167 }
168  
169 15 1;
« blib/lib/Finance/Tiller2QIF/Interactive.pm ↑ Summary blib/lib/Finance/Tiller2QIF/Util.pm »