X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCT.pm;h=3be8ce94fdb6dbe2a8322cb92a273107a88b3f70;hb=72be9c763f3b7f7df1fae4fe10011e45f9e2ad1d;hp=2397fe7d5ab3553ca4f7ad67b66f604274103aef;hpb=efd3637e62612c8fb239bfd62ff6d75074d3ca77;p=kivitendo-erp.git diff --git a/SL/CT.pm b/SL/CT.pm index 2397fe7d5..3be8ce94f 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -44,6 +44,9 @@ use SL::CVar; use SL::DBUtils; use SL::FU; use SL::Notes; +use SL::TransNumber; + +use strict; sub get_tuple { $main::lxdebug->enter_sub(); @@ -62,10 +65,13 @@ sub get_tuple { qq|ORDER BY cp.cp_id LIMIT 1|; my $sth = prepare_execute_query($form, $dbh, $query, $form->{id}); - my $ref = $sth->fetchrow_hashref(NAME_lc); + my $ref = $sth->fetchrow_hashref("NAME_lc"); map { $form->{$_} = $ref->{$_} } keys %$ref; + # remove any trailing whitespace + $form->{curr} =~ s/\s*$//; + $sth->finish; if ( $form->{salesman_id} ) { my $query = @@ -116,7 +122,7 @@ sub get_tuple { if ($ref) { foreach my $key (keys %{ $ref }) { my $new_key = $key; - $new_key =~ s/^([^_]+)/\U\1\E/; + $new_key =~ s/^([^_]+)/\U$1\E/; $form->{$new_key} = $ref->{$key}; } } @@ -155,6 +161,7 @@ sub populate_drop_down_boxes { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form, $provided_dbh) = @_; + my $query; my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect($myconfig); @@ -190,7 +197,7 @@ sub query_titles_and_greetings { $main::lxdebug->enter_sub(); my ( $self, $myconfig, $form ) = @_; - my ( %tmp, $ref ); + my ( %tmp, $ref, $query ); my $dbh = $form->dbconnect($myconfig); @@ -203,7 +210,7 @@ sub query_titles_and_greetings { qq|FROM vendor | . qq|WHERE greeting ~ '[a-zA-Z]' | . qq|ORDER BY greeting|; - my %tmp; + map({ $tmp{$_} = 1; } selectall_array_query($form, $dbh, $query)); $form->{COMPANY_GREETINGS} = [ sort(keys(%tmp)) ]; @@ -232,7 +239,7 @@ sub save_customer { $form->{klass} = 0 unless ($form->{klass}); # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $form->get_standard_dbh; map( { $form->{"cp_${_}"} = $form->{"selected_cp_${_}"} @@ -258,21 +265,12 @@ sub save_customer { } } else { - if (!$form->{customernumber} && $form->{business}) { - $form->{customernumber} = - $form->update_business($myconfig, $form->{business}, $dbh); - } - if (!$form->{customernumber}) { - $form->{customernumber} = - $form->update_defaults($myconfig, "customernumber", $dbh); - } - - $query = qq|SELECT c.id FROM customer c WHERE c.customernumber = ?|; - ($f_id) = selectrow_query($form, $dbh, $query, $form->{customernumber}); - if ($f_id ne "") { - $main::lxdebug->leave_sub(); - return 3; - } + my $customernumber = SL::TransNumber->new(type => 'customer', + dbh => $dbh, + number => $form->{customernumber}, + business_id => $form->{business}, + save => 1); + $form->{customernumber} = $customernumber->create_unique unless $customernumber->is_unique; $query = qq|SELECT nextval('id')|; ($form->{id}) = selectrow_query($form, $dbh, $query); @@ -320,7 +318,8 @@ sub save_customer { qq|taxzone_id = ?, | . qq|user_password = ?, | . qq|c_vendor_id = ?, | . - qq|klass = ? | . + qq|klass = ?, | . + qq|curr = ? | . qq|WHERE id = ?|; my @values = ( $form->{customernumber}, @@ -362,6 +361,7 @@ sub save_customer { $form->{user_password}, $form->{c_vendor_id}, conv_i($form->{klass}), + substr($form->{currency}, 0, 3), $form->{id} ); do_query( $form, $dbh, $query, @values ); @@ -446,10 +446,10 @@ sub save_customer { CVar->save_custom_variables('dbh' => $dbh, 'module' => 'CT', 'trans_id' => $form->{id}, - 'variables' => $form); + 'variables' => $form, + 'always_valid' => 1); - $rc = $dbh->commit(); - $dbh->disconnect(); + my $rc = $dbh->commit(); $main::lxdebug->leave_sub(); return $rc; @@ -462,7 +462,7 @@ sub save_vendor { $form->{taxzone_id} *= 1; # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $form->get_standard_dbh; map( { $form->{"cp_${_}"} = $form->{"selected_cp_${_}"} @@ -484,9 +484,11 @@ sub save_vendor { $query = qq|INSERT INTO vendor (id, name) VALUES (?, '')|; do_query($form, $dbh, $query, $form->{id}); - if ( !$form->{vendornumber} ) { - $form->{vendornumber} = $form->update_defaults( $myconfig, "vendornumber", $dbh ); - } + my $vendornumber = SL::TransNumber->new(type => 'vendor', + dbh => $dbh, + number => $form->{vendornumber}, + save => 1); + $form->{vendornumber} = $vendornumber->create_unique unless $vendornumber->is_unique; } $query = @@ -527,9 +529,10 @@ sub save_vendor { qq| language_id = ?, | . qq| username = ?, | . qq| user_password = ?, | . - qq| v_customer_id = ? | . + qq| v_customer_id = ?, | . + qq| curr = ? | . qq|WHERE id = ?|; - @values = ( + my @values = ( $form->{vendornumber}, $form->{name}, $form->{greeting}, @@ -567,6 +570,7 @@ sub save_vendor { $form->{username}, $form->{user_password}, $form->{v_customer_id}, + substr($form->{currency}, 0, 3), $form->{id} ); do_query($form, $dbh, $query, @values); @@ -651,10 +655,10 @@ sub save_vendor { CVar->save_custom_variables('dbh' => $dbh, 'module' => 'CT', 'trans_id' => $form->{id}, - 'variables' => $form); + 'variables' => $form, + 'always_valid' => 1); - $rc = $dbh->commit(); - $dbh->disconnect(); + my $rc = $dbh->commit(); $main::lxdebug->leave_sub(); return $rc; @@ -691,13 +695,15 @@ sub search { my @values; my %allowed_sort_columns = - map({ $_, 1 } qw(id customernumber vendornumber name contact phone fax email - taxnumber business invnumber ordnumber quonumber)); - $sortorder = $form->{sort} && $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name"; + map { $_, 1 } qw( + id customernumber vendornumber name contact phone fax email street + taxnumber business invnumber ordnumber quonumber zipcode city + ); + my $sortorder = $form->{sort} && $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name"; $form->{sort} = $sortorder; my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; - if ($sortorder ne 'id' && 1 >= scalar grep { $form->{$_} } qw(l_ordnumber l_quonumber l_invnumber)) { + if ($sortorder !~ /(business|id)/ && 1 >= scalar grep { $form->{$_} } qw(l_ordnumber l_quonumber l_invnumber )) { $sortorder = "lower($sortorder) ${sortdir}"; } else { $sortorder .= " ${sortdir}"; @@ -860,7 +866,7 @@ sub get_contact { qq|SELECT * FROM contacts c | . qq|WHERE cp_id = ? ORDER BY cp_id limit 1|; my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id}); - my $ref = $sth->fetchrow_hashref(NAME_lc); + my $ref = $sth->fetchrow_hashref("NAME_lc"); map { $form->{$_} = $ref->{$_} } keys %$ref; @@ -886,7 +892,7 @@ sub get_shipto { my $query = qq|SELECT * FROM shipto WHERE shipto_id = ?|; my $sth = prepare_execute_query($form, $dbh, $query, $form->{shipto_id}); - my $ref = $sth->fetchrow_hashref(NAME_lc); + my $ref = $sth->fetchrow_hashref("NAME_lc"); map { $form->{$_} = $ref->{$_} } keys %$ref; @@ -936,7 +942,7 @@ sub get_delivery { qq|SELECT s.shiptoname, i.qty $qty_sign, | . qq| ${arap}.id, ${arap}.transdate, ${arap}.invnumber, ${arap}.ordnumber, | . qq| i.description, i.unit, i.sellprice, | . - qq| oe.id AS oe_id | . + qq| oe.id AS oe_id, invoice | . qq|FROM $arap | . qq|LEFT JOIN shipto s ON | . ($arap eq "ar" @@ -1051,17 +1057,17 @@ sub delete_shipto { $main::lxdebug->leave_sub(); } -sub delete_shipto { +sub delete_contact { $main::lxdebug->enter_sub(); my $self = shift; - my $shipto_id = shift; + my $cp_id = shift; my $form = $main::form; my %myconfig = %main::myconfig; my $dbh = $form->get_standard_dbh(\%myconfig); - do_query($form, $dbh, qq|UPDATE contacts SET cp_cv_id = NULL WHERE cp_id = ?|, $shipto_id); + do_query($form, $dbh, qq|UPDATE contacts SET cp_cv_id = NULL WHERE cp_id = ?|, $cp_id); $dbh->commit(); @@ -1083,7 +1089,7 @@ sub get_bank_info { my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor'; my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id}); - my $placeholders = ('?') x scalar @ids; + my $placeholders = join ", ", ('?') x scalar @ids; my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic FROM ${table} WHERE id IN (${placeholders})|; @@ -1101,4 +1107,80 @@ sub get_bank_info { return $result; } +sub parse_excel_file { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + my $locale = $main::locale; + + $form->{formname} = 'sales_quotation'; + $form->{type} = 'sales_quotation'; + $form->{format} = 'excel'; + $form->{media} = 'screen'; + $form->{quonumber} = 1; + + + # $form->{"notes"} will be overridden by the customer's/vendor's "notes" field. So save it here. + $form->{ $form->{"formname"} . "notes" } = $form->{"notes"}; + + my $inv = "quo"; + my $due = "req"; + $form->{"${inv}date"} = $form->{transdate}; + $form->{label} = $locale->text('Quotation'); + my $numberfld = "sqnumber"; + my $order = 1; + + # assign number + $form->{what_done} = $form->{formname}; + + map({ delete($form->{$_}); } grep(/^cp_/, keys(%{ $form }))); + + my $output_dateformat = $myconfig->{"dateformat"}; + my $output_numberformat = $myconfig->{"numberformat"}; + my $output_longdates = 1; + + # map login user variables + map { $form->{"login_$_"} = $myconfig->{$_} } ("name", "email", "fax", "tel", "company"); + + # format item dates + for my $field (qw(transdate_oe deliverydate_oe)) { + map { + $form->{$field}[$_] = $locale->date($myconfig, $form->{$field}[$_], 1); + } 0 .. $#{ $form->{$field} }; + } + + if ($form->{shipto_id}) { + $form->get_shipto($myconfig); + } + + $form->{notes} =~ s/^\s+//g; + + $form->{templates} = $myconfig->{templates}; + + delete $form->{printer_command}; + + $form->get_employee_info($myconfig); + + my ($cvar_date_fields, $cvar_number_fields) = CVar->get_field_format_list('module' => 'CT', 'prefix' => 'vc_'); + + if (scalar @{ $cvar_date_fields }) { + format_dates($output_dateformat, $output_longdates, @{ $cvar_date_fields }); + } + + while (my ($precision, $field_list) = each %{ $cvar_number_fields }) { + reformat_numbers($output_numberformat, $precision, @{ $field_list }); + } + + $form->{excel} = 1; + my $extension = 'xls'; + + $form->{IN} = "$form->{formname}.${extension}"; + + delete $form->{OUT}; + + $form->parse_template($myconfig); + + $main::lxdebug->leave_sub(); +} + 1;