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 if ($department_name) {
85 $department = SL::DB::Manager::Department->get_first(description => { like => $department_name . '%' });
88 my $amount = $::form->parse_amount({ numberformat => '1000,00' }, $row->[0]);
90 $current_transaction = SL::DB::GLTransaction->new(
91 employee_id => $::form->{employee_id},
92 transdate => $dt_to_kivi,
93 description => $row->[13],
94 reference => $row->[13],
95 department_id => ref $department eq 'SL::DB::Department' ? $department->id : undef,
101 source => $::form->{ATTACHMENTS}{file}{filename},
102 )->add_chart_booking(
105 source => $::form->{ATTACHMENTS}{file}{filename},
108 push @{ $self->{gl_trans} }, $current_transaction;
110 if ($::instance_conf->get_doc_storage) {
111 my $file = SL::File->save(object_id => $current_transaction->id,
112 object_type => 'gl_transaction',
113 mime_type => 'text/csv',
114 source => 'uploaded',
115 file_type => 'attachment',
116 file_name => $::form->{ATTACHMENTS}{file}{filename},
117 file_contents => $::form->{file},
124 }) or do { die t8("Cannot add Booking, reason: #1 DB: #2 ", $@, SL::DB->client->error) };
128 $::auth->assert('general_ledger');
131 sub setup_pay_posting_action_bar {
134 for my $bar ($::request->layout->get('actionbar')) {
137 $::locale->text('Import'),
138 submit => [ '#form', { action => 'PayPostingImport/import_datev_pay_postings' } ],
139 accesskey => 'enter',
155 SL::Controller::PayPostingImport
156 Controller for importing pay postings.
157 Currently only DATEV format is supported.
164 =item C<action_upload_pay_postings>
166 Simple upload form. HTML Form allows only CSV files.
169 =item C<action_import_datev_pay_postings>
171 Does some sanity checks for the CSV file according to the expected DATEV data structure
172 If successful calls the parse_and_import function
174 =item C<parse_and_import>
176 Internal function for parsing and importing every line of the CSV data as a GL Booking.
177 Adds the attribute imported for the GL Booking.
178 If a chart which uses a tax automatic is assigned the tax will be calculated with the
179 'tax_included' option, which defaults to the DATEV format.
181 Furthermore adds the original CSV filename for every AccTransaction and puts the CSV in every GL Booking
182 if the feature DMS is active.
183 If a Chart is missing or any kind of different error occurs the whole import including the DMS addition