Revision 3bfb43ab
| SL/BackgroundJob/SetBankAccountsMasterData.pm | ||
|---|---|---|
| 13 | 13 |
|
| 14 | 14 |
die "No valid integer for months" if $data->{months} && $data->{months} !~ /^[1-9][0-9]*$/;
|
| 15 | 15 |
die "No valid value for overwrite" if $data->{overwrite} && $data->{overwrite} !~ /^(0|1)$/;
|
| 16 |
die "No valid value for overwrite" if $data->{dry_run} && $data->{dry_run} !~ /^(0|1)$/;
|
|
| 16 |
die "No valid value for dry_run" if $data->{dry_run} && $data->{dry_run} !~ /^(0|1)$/;
|
|
| 17 | 17 |
|
| 18 | 18 |
$self->{dry_run} = $data->{dry_run} ? 1 : 0;
|
| 19 | 19 |
$self->{overwrite} = $data->{overwrite} ? 1 : 0;
|
| ... | ... | |
| 51 | 51 |
sub _get_bank_data_vc {
|
| 52 | 52 |
my (%params) = @_; |
| 53 | 53 |
|
| 54 |
die "Need valid vc param, got" . $params{vc} unless $params{vc} && $params{vc} =~ /^(customer|vendor)$/;
|
|
| 55 |
die "Need valid months param, got" . $params{months} unless $params{months} && $params{months} =~ /^[1-9][0-9]*$/;
|
|
| 54 |
die "Need valid vc param, got:" . $params{vc} unless $params{vc} && $params{vc} =~ /^(customer|vendor)$/;
|
|
| 55 |
die "Need valid months param, got:" . $params{months} unless $params{months} && $params{months} =~ /^[1-9][0-9]*$/;
|
|
| 56 | 56 |
|
| 57 | 57 |
my $vc_id = $params{vc} . '_id';
|
| 58 |
|
|
| 58 | 59 |
my $arap = $params{vc} eq 'customer' ? 'ar'
|
| 59 | 60 |
: $params{vc} eq 'vendor' ? 'ap'
|
| 60 | 61 |
: undef; |
| 61 | 62 |
|
| 62 |
die "Invalid state" unless $arap; |
|
| 63 | 63 |
|
| 64 | 64 |
my $dbh = SL::DB->client->dbh; |
| 65 | 65 |
my $query = <<SQL; |
| 66 | 66 |
SELECT bt.remote_bank_code, bt.remote_account_number, $vc_id |
| 67 | 67 |
FROM $arap |
| 68 |
LEFT JOIN bank_transaction_acc_trans bta on id = bta.${arap}_id
|
|
| 69 |
LEFT JOIN bank_transactions bt on bt.id = bta.bank_transaction_id
|
|
| 70 |
WHERE $vc_id in (select distinct $vc_id from $arap where transdate > now() - interval '$params{months} month' AND paid = amount)
|
|
| 71 |
AND bta.${arap}_id is not NULL
|
|
| 68 |
LEFT JOIN bank_transaction_acc_trans bta ON id = bta.${arap}_id
|
|
| 69 |
LEFT JOIN bank_transactions bt ON bt.id = bta.bank_transaction_id
|
|
| 70 |
WHERE $vc_id IN (SELECT DISTINCT $vc_id FROM $arap WHERE transdate > now() - interval '$params{months} month' AND paid = amount)
|
|
| 71 |
AND bta.${arap}_id IS NOT NULL
|
|
| 72 | 72 |
GROUP BY bt.remote_account_number,bt.remote_bank_code, $vc_id |
| 73 | 73 |
ORDER BY $vc_id |
| 74 | 74 |
SQL |
Auch abrufbar als: Unified diff
Überarbeitung nach PR 387 (SetBankAccountsMasterData)