1 # This file has been auto-generated only because it didn't exist.
2 # Feel free to modify it at will; it will not be overwritten automatically.
4 package SL::DB::BankTransaction;
8 use SL::DB::MetaSetup::BankTransaction;
9 use SL::DB::Manager::BankTransaction;
10 use SL::DB::Helper::LinkedRecords;
12 require SL::DB::Invoice;
13 require SL::DB::PurchaseInvoice;
15 __PACKAGE__->meta->initialize;
18 # Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.
19 #__PACKAGE__->meta->make_manager_class;
22 my ($self, $other) = @_;
24 return 1 if $self->transdate && !$other->transdate;
25 return -1 if !$self->transdate && $other->transdate;
28 $result = $self->transdate <=> $other->transdate if $self->transdate;
29 return $result || ($self->id <=> $other->id);
35 #my $record_links = $self->linked_records(direction => 'both');
39 my $record_links = SL::DB::Manager::RecordLink->get_all(where => [ from_table => 'bank_transactions', from_id => $self->id ]);
41 foreach my $record_link (@{ $record_links }) {
42 push @linked_invoices, SL::DB::Manager::Invoice->find_by(id => $record_link->to_id)->invnumber if $record_link->to_table eq 'ar';
43 push @linked_invoices, SL::DB::Manager::PurchaseInvoice->find_by(id => $record_link->to_id)->invnumber if $record_link->to_table eq 'ap';
46 return [ @linked_invoices ];
49 sub get_agreement_with_invoice {
50 my ($self, $invoice) = @_;
52 die "first argument is not an invoice object"
53 unless ref($invoice) eq 'SL::DB::Invoice' or ref($invoice) eq 'SL::DB::PurchaseInvoice';
56 cust_vend_name_in_purpose => 1,
57 cust_vend_number_in_purpose => 1,
62 datebonus_negative => -1,
63 depositor_matches => 2,
65 exact_open_amount => 4,
66 invnumber_in_purpose => 2,
67 own_invnumber_in_purpose => 5,
68 # overpayment => -1, # either other invoice is more likely, or several invoices paid at once
69 payment_before_invoice => -2,
70 payment_within_30_days => 1,
71 remote_account_number => 3,
72 skonto_exact_amount => 5,
74 sepa_export_item => 5,
77 my ($agreement,$rule_matches);
79 # compare banking arrangements
80 my ($iban, $bank_code, $account_number);
81 $bank_code = $invoice->customer->bank_code if $invoice->is_sales;
82 $account_number = $invoice->customer->account_number if $invoice->is_sales;
83 $iban = $invoice->customer->iban if $invoice->is_sales;
84 $bank_code = $invoice->vendor->bank_code if ! $invoice->is_sales;
85 $iban = $invoice->vendor->iban if ! $invoice->is_sales;
86 $account_number = $invoice->vendor->account_number if ! $invoice->is_sales;
87 if ( $bank_code eq $self->remote_bank_code && $account_number eq $self->remote_account_number ) {
88 $agreement += $points{remote_account_number};
89 $rule_matches .= 'remote_account_number(' . $points{'remote_account_number'} . ') ';
91 if ( $iban eq $self->remote_account_number ) {
92 $agreement += $points{remote_account_number};
93 $rule_matches .= 'remote_account_number(' . $points{'remote_account_number'} . ') ';
96 my $datediff = $self->transdate->{utc_rd_days} - $invoice->transdate->{utc_rd_days};
97 $invoice->{datediff} = $datediff;
100 if (abs(abs($invoice->amount) - abs($self->amount)) < 0.01) {
101 $agreement += $points{exact_amount};
102 $rule_matches .= 'exact_amount(' . $points{'exact_amount'} . ') ';
105 # compare open amount, preventing double points when open amount = invoice amount
106 if ( $invoice->amount != $invoice->open_amount && abs(abs($invoice->open_amount) - abs($self->amount)) < 0.01) {
107 $agreement += $points{exact_open_amount};
108 $rule_matches .= 'exact_open_amount(' . $points{'exact_open_amount'} . ') ';
111 if ( $invoice->skonto_date && abs(abs($invoice->amount_less_skonto) - abs($self->amount)) < 0.01) {
112 $agreement += $points{skonto_exact_amount};
113 $rule_matches .= 'skonto_exact_amount(' . $points{'skonto_exact_amount'} . ') ';
116 #search invoice number in purpose
117 my $invnumber = $invoice->invnumber;
118 # invnumber has to have at least 3 characters
119 my $squashed_purpose = $self->purpose;
120 $squashed_purpose =~ s/ //g;
121 if (length($invnumber) > 4 && $squashed_purpose =~ /$invnumber/ && $invoice->is_sales){
122 $agreement += $points{own_invnumber_in_purpose};
123 $rule_matches .= 'own_invnumber_in_purpose(' . $points{'own_invnumber_in_purpose'} . ') ';
124 } elsif (length($invnumber) > 3 && $squashed_purpose =~ /$invnumber/ ) {
125 $agreement += $points{invnumber_in_purpose};
126 $rule_matches .= 'invnumber_in_purpose(' . $points{'invnumber_in_purpose'} . ') ';
130 if ( $invoice->is_sales && $self->amount < 0 ) {
131 $agreement += $points{wrong_sign};
132 $rule_matches .= 'wrong_sign(' . $points{'wrong_sign'} . ') ';
134 if ( ! $invoice->is_sales && $self->amount > 0 ) {
135 $agreement += $points{wrong_sign};
136 $rule_matches .= 'wrong_sign(' . $points{'wrong_sign'} . ') ';
139 # search customer/vendor number in purpose
141 $cvnumber = $invoice->customer->customernumber if $invoice->is_sales;
142 $cvnumber = $invoice->vendor->vendornumber if ! $invoice->is_sales;
143 if ( $cvnumber && $self->purpose =~ /\b$cvnumber\b/i ) {
144 $agreement += $points{cust_vend_number_in_purpose};
145 $rule_matches .= 'cust_vend_number_in_purpose(' . $points{'cust_vend_number_in_purpose'} . ') ';
148 # search for customer/vendor name in purpose (may contain GMBH, CO KG, ...)
150 $cvname = $invoice->customer->name if $invoice->is_sales;
151 $cvname = $invoice->vendor->name if ! $invoice->is_sales;
152 if ( $cvname && $self->purpose =~ /\b$cvname\b/i ) {
153 $agreement += $points{cust_vend_name_in_purpose};
154 $rule_matches .= 'cust_vend_name_in_purpose(' . $points{'cust_vend_name_in_purpose'} . ') ';
157 # compare depositorname, don't try to match empty depositors
159 $depositorname = $invoice->customer->depositor if $invoice->is_sales;
160 $depositorname = $invoice->vendor->depositor if ! $invoice->is_sales;
161 if ( $depositorname && $self->remote_name =~ /$depositorname/ ) {
162 $agreement += $points{depositor_matches};
163 $rule_matches .= 'depositor_matches(' . $points{'depositor_matches'} . ') ';
166 #Check if words in remote_name appear in cvname
167 my $check_string_points = _check_string($self->remote_name,$cvname);
168 if ( $check_string_points ) {
169 $agreement += $check_string_points;
170 $rule_matches .= 'remote_name(' . $check_string_points . ') ';
173 # transdate prefilter: compare transdate of bank_transaction with transdate of invoice
174 if ( $datediff < -5 ) { # this might conflict with advance payments
175 $agreement += $points{payment_before_invoice};
176 $rule_matches .= 'payment_before_invoice(' . $points{'payment_before_invoice'} . ') ';
178 if ( $datediff < 30 ) {
179 $agreement += $points{payment_within_30_days};
180 $rule_matches .= 'payment_within_30_days(' . $points{'payment_within_30_days'} . ') ';
183 # only if we already have a good agreement, let date further change value of agreement.
184 # this is so that if there are several plausible open invoices which are all equal
185 # (rent jan, rent feb...) the one with the best date match is chosen over
188 # another way around this is to just pre-filter by periods instead of matching everything
189 if ( $agreement > 5 ) {
190 if ( $datediff == 0 ) {
191 $agreement += $points{datebonus0};
192 $rule_matches .= 'datebonus0(' . $points{'datebonus0'} . ') ';
193 } elsif ( $datediff > 0 and $datediff <= 14 ) {
194 $agreement += $points{datebonus14};
195 $rule_matches .= 'datebonus14(' . $points{'datebonus14'} . ') ';
196 } elsif ( $datediff >14 and $datediff < 35) {
197 $agreement += $points{datebonus35};
198 $rule_matches .= 'datebonus35(' . $points{'datebonus35'} . ') ';
199 } elsif ( $datediff >34 and $datediff < 120) {
200 $agreement += $points{datebonus120};
201 $rule_matches .= 'datebonus120(' . $points{'datebonus120'} . ') ';
202 } elsif ( $datediff < 0 ) {
203 $agreement += $points{datebonus_negative};
204 $rule_matches .= 'datebonus_negative(' . $points{'datebonus_negative'} . ') ';
206 # e.g. datediff > 120
210 # if there is exactly one non-executed sepa_export_item for the invoice
211 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
212 if ( scalar @$seis == 1 ) {
213 my $sei = $seis->[0];
215 # test for amount and id matching only, sepa transfer date and bank
216 # transaction date needn't match
217 my $arap = $invoice->is_sales ? 'ar' : 'ap';
218 if ( abs($self->amount) == ($sei->amount)
219 && $invoice->id == $sei->arap_id
221 $agreement += $points{sepa_export_item};
222 $rule_matches .= 'sepa_export_item(' . $points{'sepa_export_item'} . ') ';
225 # zero or more than one sepa_export_item, do nothing for this invoice
226 # zero: do nothing, no sepa_export_item exists, no match
227 # more than one: does this ever apply? Currently you can't create sepa
228 # exports for invoices that already have a non-executed sepa_export
232 return ($agreement,$rule_matches);
236 my $bankstring = shift;
237 my $namestring = shift;
238 return 0 unless $bankstring and $namestring;
240 my @bankwords = grep(/^\w+$/, split(/\b/,$bankstring));
243 foreach my $bankword ( @bankwords ) {
244 # only try to match strings with more than 2 characters
245 next unless length($bankword)>2;
246 if ( $namestring =~ /\b$bankword\b/i ) {
261 SL::DB::BankTransaction
267 =item C<get_agreement_with_invoice $invoice>
269 Using a point system this function checks whether the bank transaction matches
270 an invoices, using a variety of tests, such as
276 =item * amount_less_skonto
280 =item * invoice number in purpose
282 =item * customer or vendor name in purpose
284 =item * account number matches account number of customer or vendor
288 The total number of points, and the rules that matched, are returned.
291 my $bt = SL::DB::Manager::BankTransaction->find_by(id => 522);
292 my $invoice = SL::DB::Manager::Invoice->find_by(invnumber => '198');
293 my ($agreement,rule_matches) = $bt->get_agreement_with_invoice($invoice);
299 G. Richardson E<lt>grichardson@kivitendo-premium.de<gt>