1 package SL::Controller::PayPostingImport;
3 use parent qw(SL::Controller::Base);
6 use SL::Helper::DateTime;
7 use SL::Helper::Flash qw(flash_later);
8 use SL::Locale::String qw(t8);
13 __PACKAGE__->run_before('check_auth');
16 sub action_upload_pay_postings {
17 my ($self, %params) = @_;
19 $self->setup_pay_posting_action_bar;
20 $self->render('pay_posting_import/form', title => $::locale->text('Import Pay Postings'));
23 sub action_import_datev_pay_postings {
24 my ($self, %params) = @_;
26 die t8("missing file for action import") unless ($::form->{file});
28 my $filename= $::form->{ATTACHMENTS}{file}{filename};
29 # check name and first fields of CSV data
30 die t8("Wrong file name, expects name like: DTVF_*_LOHNBUCHUNG*.csv") unless $filename =~ /^DTVF_.*_LOHNBUCHUNGEN_LUG.*\.csv$/;
31 die t8("not a valid DTVF file, expected first field in A1 'DTVF'") unless ($::form->{file} =~ m/^"DTVF";/);
32 die t8("not a valid DTVF file, expected field header start with 'Umsatz; (..) ;Konto;Gegenkonto'")
33 unless ($::form->{file} =~ m/Umsatz;S\/H;;;;;Konto;Gegenkonto.*;;Belegdatum;Belegfeld 1;Belegfeld 2;;Buchungstext/);
35 # check if file is already imported
36 my $acc_trans_doc = SL::DB::Manager::AccTransaction->get_first(query => [ source => $filename ]);
37 die t8("Already imported: ") . $acc_trans_doc->source if ref $acc_trans_doc eq 'SL::DB::AccTransaction';
39 if (parse_and_import($self)) {
40 flash_later('info', t8("All pay postings successfully imported."));
42 $self->setup_pay_posting_action_bar;
43 $self->render('pay_posting_import/form', title => $::locale->text('Imported Pay Postings'));
46 sub parse_and_import {
49 my $csv = Text::CSV_XS->new ({ binary => 0, auto_diag => 1, sep_char => ";" });
50 open (my $fh, "<:encoding(cp1252)", \$::form->{file}) or die "cannot open $::form->{file} $!";
52 # Umsatz S/H Konto Gegenkonto (ohne BU-Schlüssel) Belegdatum Belegfeld 1 Belegfeld 2 Buchungstext
53 my $year = substr($csv->getline($fh)->[12], 0, 4);
55 # whole import or nothing
56 my $current_transaction;
57 SL::DB->client->with_transaction(sub {
58 while (my $row = $csv->getline($fh)) {
59 next unless $row->[0] =~ m/\d/;
60 my ($credit, $debit, $dt_to_kivi, $length, $accno_credit, $accno_debit,
61 $department_name, $department);
63 # check valid soll/haben kennzeichen
64 croak("No valid debit/credit sign") unless $row->[1] =~ m/^(S|H)$/;
66 # check transaction date can be 4 or 3 digit (leading 0 omitted)
67 $length = length $row->[9] == 4 ? 2 : 1;
68 $dt_to_kivi = DateTime->new(year => $year,
69 month => substr ($row->[9], -2),
70 day => substr($row->[9],0, $length))->to_kivitendo;
72 croak("Something wrong with date conversion") unless $dt_to_kivi;
74 $accno_credit = $row->[1] eq 'S' ? $row->[7] : $row->[6];
75 $accno_debit = $row->[1] eq 'S' ? $row->[6] : $row->[7];
76 $credit = SL::DB::Manager::Chart->find_by(accno => $accno_credit);
77 $debit = SL::DB::Manager::Chart->find_by(accno => $accno_debit);
79 croak("No such Chart $accno_credit") unless ref $credit eq 'SL::DB::Chart';
80 croak("No such Chart $accno_debit") unless ref $debit eq 'SL::DB::Chart';
82 # optional KOST1 - KOST2 ?
83 $department_name = $row->[36];
84 $department = SL::DB::Manager::Department->get_first(description => { like => $department_name . '%' });
86 my $amount = $::form->parse_amount({ numberformat => '1000,00' }, $row->[0]);
88 $current_transaction = SL::DB::GLTransaction->new(
89 employee_id => $::form->{employee_id},
90 transdate => $dt_to_kivi,
91 description => $row->[13],
92 reference => $row->[13],
93 department_id => ref $department eq 'SL::DB::Department' ? $department->id : undef,
99 source => $::form->{ATTACHMENTS}{file}{filename},
100 )->add_chart_booking(
103 source => $::form->{ATTACHMENTS}{file}{filename},
106 push @{ $self->{gl_trans} }, $current_transaction;
108 if ($::instance_conf->get_doc_storage) {
109 my $file = SL::File->save(object_id => $current_transaction->id,
110 object_type => 'gl_transaction',
111 mime_type => 'text/csv',
112 source => 'uploaded',
113 file_type => 'attachment',
114 file_name => $::form->{ATTACHMENTS}{file}{filename},
115 file_contents => $::form->{file},
122 }) or do { die t8("Cannot add Booking, reason: #1 DB: #2 ", $@, SL::DB->client->error) };
126 $::auth->assert('general_ledger');
129 sub setup_pay_posting_action_bar {
132 for my $bar ($::request->layout->get('actionbar')) {
135 $::locale->text('Import'),
136 submit => [ '#form', { action => 'PayPostingImport/import_datev_pay_postings' } ],
137 accesskey => 'enter',
153 SL::Controller::PayPostingImport
154 Controller for importing pay postings.
155 Currently only DATEV format is supported.
162 =item C<action_upload_pay_postings>
164 Simple upload form. HTML Form allows only CSV files.
167 =item C<action_import_datev_pay_postings>
169 Does some sanity checks for the CSV file according to the expected DATEV data structure
170 If successful calls the parse_and_import function
172 =item C<parse_and_import>
174 Internal function for parsing and importing every line of the CSV data as a GL Booking.
175 Adds the attribute imported for the GL Booking.
176 If a chart which uses a tax automatic is assigned the tax will be calculated with the
177 'tax_included' option, which defaults to the DATEV format.
179 Furthermore adds the original CSV filename for every AccTransaction and puts the CSV in every GL Booking
180 if the feature DMS is active.
181 If a Chart is missing or any kind of different error occurs the whole import including the DMS addition