Neue Verkaufsberichtvariante mit Umsatz-Sortierung

Es gibt einen neuen Menüeintrag "Verkaufsbericht Top", wo man nach den
gleichen Kategorien wie beim normalen Verkaufsbericht gruppieren kann,
aber wo man das Ergebnis nach Umsatz, Marge, Menge oder Gewicht
vorsortieren kann, was beim Standardverkaufsbericht nicht möglich war.
Dadurch kann man sich z.B. eine nach Umsatz sortierte Liste der Kunden
für einen Zeitraum anzeigen lassen.  Es wird weiterhin nur auf Daten aus
"invoice" zurückgegriffen.
Es wird bei dieser Variante auf den gleichen Code zurückgegriffen,
allerdings wurde aus Gründen der Übersicht ein neuer Menüpunkt
eingeführt, in Zukunft könnte man dies vielleicht mit eigenen Reitern
besser machen.

diff --git a/SL/VK.pm b/SL/VK.pm
index 2c0ce67259df4bdea355ef5182eca6445f7d9b9c..bcc43ad4f318400b20b704890eb243b65c1569bb 100644 (file)
--- a/SL/VK.pm
+++ b/SL/VK.pm
@@ -53,9 +53,15 @@ sub invoice_transactions {
   # default usage: always use parts.description for (sub-)totalling and in header and subheader lines
   # but use invoice.description in article mode
   # so we extract both versions in our query and later overwrite the description in article mode
   # default usage: always use parts.description for (sub-)totalling and in header and subheader lines
   # but use invoice.description in article mode
   # so we extract both versions in our query and later overwrite the description in article mode

+#    qq| , (SELECT sum(invoice.qty*invoice.sellprice/CASE WHEN price_factor IS NOT NULL THEN price_factor ELSE 1.0 END) FROM invoice where invoice.parts_id = i.parts_id) as "sellpricelinetotal2" | .

 
   my $query =
 
   my $query =

-    qq|SELECT ct.id as customerid, ct.name as customername,ct.customernumber,ct.country,ar.invnumber,ar.id,ar.transdate,p.partnumber,p.description as description, pg.partsgroup,i.parts_id,i.qty,i.price_factor,i.discount,i.description as invoice_description,i.lastcost,i.sellprice,i.fxsellprice,i.marge_total,i.marge_percent,i.unit,b.description as business,e.name as employee,e2.name as salesman, to_char(ar.transdate,'Month') as month, to_char(ar.transdate, 'YYYYMM') as nummonth, p.unit as parts_unit, p.weight | .
+    qq|SELECT ct.id as customerid, ct.name as customername,ct.customernumber,ct.country,ar.invnumber,ar.id,ar.transdate,p.partnumber,p.description as description, pg.partsgroup,i.parts_id,i.qty,i.price_factor,i.discount,i.description as invoice_description,i.lastcost,i.sellprice,i.fxsellprice,i.marge_total,i.marge_percent,i.unit,b.description as business,e.name as employee,e2.name as salesman, to_char(ar.transdate,'Month') as month, to_char(ar.transdate, 'YYYYMM') as nummonth, p.unit as parts_unit, p.weight |; 
+
+  # debug modus for comparing linetotal according to database and perl
+  $query .= qq| , (i.qty*i.sellprice/CASE WHEN price_factor IS NOT NULL THEN i.price_factor ELSE 1.0 END) as sellpricelinetotal | if $form->{"l_sellpricelinetotal"};  
+
+  $query .=

     qq|FROM invoice i | .
     qq|JOIN ar on (i.trans_id = ar.id) | .
     qq|JOIN parts p on (i.parts_id = p.id) | .
     qq|FROM invoice i | .
     qq|JOIN ar on (i.trans_id = ar.id) | .
     qq|JOIN parts p on (i.parts_id = p.id) | .

diff --git a/bin/mozilla/gl.pl b/bin/mozilla/gl.pl
index d2e0f9cc32c946e2760c416bd630591d03cc7e6e..89359ad40185efdc735f73eb3924d6098ebf629f 100644 (file)
--- a/bin/mozilla/gl.pl
+++ b/bin/mozilla/gl.pl
@@ -967,7 +967,7 @@ sub yes {
   if (GL->delete_transaction(\%myconfig, \%$form)){
     # saving the history
       if(!exists $form->{addition} && $form->{id} ne "") {
   if (GL->delete_transaction(\%myconfig, \%$form)){
     # saving the history
       if(!exists $form->{addition} && $form->{id} ne "") {

-        $form->{snumbers} = qq|ordnumber_| . $form->{ordnumber};
+        $form->{snumbers} = qq|glnumber_| . $form->{id};

         $form->{addition} = "DELETED";
         $form->save_history;
       }
         $form->{addition} = "DELETED";
         $form->save_history;
       }


@@ -1139,8 +1139,9 @@ sub post_transaction {
   }
   undef($form->{callback});
   # saving the history
   }
   undef($form->{callback});
   # saving the history

+  

   if(!exists $form->{addition} && $form->{id} ne "") {
   if(!exists $form->{addition} && $form->{id} ne "") {

-    $form->{snumbers} = qq|ordnumber_| . $form->{ordnumber};
+    $form->{snumbers} = qq|glnumber_| . $form->{id};

     $form->{addition} = "SAVED";
     $form->{what_done} = $locale->text("Buchungsnummer") . " = " . $form->{id};
     $form->save_history;
     $form->{addition} = "SAVED";
     $form->{what_done} = $locale->text("Buchungsnummer") . " = " . $form->{id};
     $form->save_history;


@@ -1209,7 +1210,7 @@ sub storno {
 
   # saving the history
   if(!exists $form->{addition} && $form->{id} ne "") {
 
   # saving the history
   if(!exists $form->{addition} && $form->{id} ne "") {

-    $form->{snumbers} = "ordnumber_$form->{ordnumber}";
+    $form->{snumbers} = "glnumber_$form->{id}";

     $form->{addition} = "STORNO";
     $form->save_history;
   }
     $form->{addition} = "STORNO";
     $form->save_history;
   }

diff --git a/bin/mozilla/vk.pl b/bin/mozilla/vk.pl
index 9206e7e741f471307bf90a9ddb06cc71ba711f5a..b1176f209461aa9d905f4b346f4955c1db3de513 100644 (file)
--- a/bin/mozilla/vk.pl
+++ b/bin/mozilla/vk.pl
@@ -31,6 +31,9 @@
 #
 #======================================================================
 
 #
 #======================================================================
 

+# TODO
+# if qty or weight is shown add unit
+

 use POSIX qw(strftime);
 use List::Util qw(sum first);
 
 use POSIX qw(strftime);
 use List::Util qw(sum first);
 


@@ -90,6 +93,49 @@ sub search_invoice {
   $main::lxdebug->leave_sub();
 }
 
   $main::lxdebug->leave_sub();
 }
 

+sub search_invoice_top {
+  $main::lxdebug->enter_sub();
+  $main::auth->assert('general_ledger | invoice_edit');
+
+  my $form     = $main::form;
+  my %myconfig = %main::myconfig;
+  my $locale   = $main::locale;
+
+  my ($customer, $department);
+
+  # setup customer selection
+  $form->all_vc(\%myconfig, "customer", "AR");
+
+  $form->{title}    = $locale->text('Sales Report Top');
+
+  $form->get_lists("projects"        => { "key" => "ALL_PROJECTS", "all" => 1 },
+                   "departments"     => "ALL_DEPARTMENTS",
+                   "business_types"  => "ALL_BUSINESS_TYPES",
+                   "salesmen"        => "ALL_SALESMEN",
+                   'employees'       => 'ALL_EMPLOYEES',
+                   'partsgroup'      => 'ALL_PARTSGROUPS',
+                   "customers"       => "ALL_VC");
+  $form->{CUSTOM_VARIABLES_IC}                  = CVar->get_configs('module' => 'IC');
+  ($form->{CUSTOM_VARIABLES_FILTER_CODE_IC},
+   $form->{CUSTOM_VARIABLES_INCLUSION_CODE_IC}) = CVar->render_search_options('variables'      => $form->{CUSTOM_VARIABLES_IC},
+                                                                           'include_prefix' => 'l_',
+                                                                           'include_value'  => 'Y');
+
+  $form->{CUSTOM_VARIABLES_CT}                  = CVar->get_configs('module' => 'CT');
+  ($form->{CUSTOM_VARIABLES_FILTER_CODE_CT},
+   $form->{CUSTOM_VARIABLES_INCLUSION_CODE_CT}) = CVar->render_search_options('variables'      => $form->{CUSTOM_VARIABLES_CT},
+                                                                           'include_prefix' => 'l_',
+                                                                           'include_value'  => 'Y');
+  $form->{vc_keys}   = sub { "$_[0]->{name}--$_[0]->{id}" };
+  $form->{employee_labels} = sub { $_[0]->{"name"} || $_[0]->{"login"} };
+  $form->{salesman_labels} = $form->{employee_labels};
+
+  $form->header;
+  print $form->parse_html_template('vk/search_invoice_top', { %myconfig });
+
+  $main::lxdebug->leave_sub();
+}
+

 sub invoice_transactions {
   $main::lxdebug->enter_sub();
 
 sub invoice_transactions {
   $main::lxdebug->enter_sub();
 


@@ -99,8 +145,26 @@ sub invoice_transactions {
   my %myconfig = %main::myconfig;
   my $locale   = $main::locale;
 
   my %myconfig = %main::myconfig;
   my $locale   = $main::locale;
 

+  my @data_to_be_sorted;
+  # debug mode for finding differences in Umsatz as calculated by database and perl
+  # set to "Y" to turn on
+  $form->{"l_sellpricelinetotal"} = "";
+

   my ($callback, $href, @columns);
 
   my ($callback, $href, @columns);
 

+  # sort_by_total mode
+  if ( $form->{l_sort_by_total} ) {
+    # uncheck Mainheadings
+    # check both subheadings
+    # set subsort = mainsort
+    $form->{l_headers_mainsort}  = '';
+    $form->{l_subtotal_mainsort} = "Y";
+    $form->{l_headers_subsort} = ''; 
+    $form->{l_subtotal_subsort} = "Y";
+    $form->{mainsort} = $form->{topsortgroup};
+    $form->{subsort} = $form->{mainsort};   # trigger subsort = mainsort
+  };
+

   # can't currently be configured from report, empty line between main sortings
   my $addemptylines = 1;
 
   # can't currently be configured from report, empty line between main sortings
   my $addemptylines = 1;
 


@@ -139,6 +203,8 @@ sub invoice_transactions {
   VK->invoice_transactions(\%myconfig, \%$form);
 
 
   VK->invoice_transactions(\%myconfig, \%$form);
 
 

+
+

   if ( $form->{mainsort} eq 'month' or $form->{subsort} eq 'month' ) {
 
     # Data already comes out of SELECT statement in correct month order, but
   if ( $form->{mainsort} eq 'month' or $form->{subsort} eq 'month' ) {
 
     # Data already comes out of SELECT statement in correct month order, but


@@ -155,6 +221,9 @@ sub invoice_transactions {
 
   @columns =
     qw(description invnumber transdate customernumber customername partnumber partsgroup country business transdate qty parts_unit weight sellprice sellprice_total discount lastcost lastcost_total marge_total marge_percent employee salesman);
 
   @columns =
     qw(description invnumber transdate customernumber customername partnumber partsgroup country business transdate qty parts_unit weight sellprice sellprice_total discount lastcost lastcost_total marge_total marge_percent employee salesman);

+  push(@columns, "sellpricelinetotal") if $form->{"l_sellpricelinetotal"};
+  
+# sellpricelinetotal is qty*sellprice as calculated by database

 
   my @includeable_custom_variables = grep { $_->{includeable} } @{ $cvar_configs_ic }, @{ $cvar_configs_ct };
   my @searchable_custom_variables  = grep { $_->{searchable} }  @{ $cvar_configs_ic }, @{ $cvar_configs_ct };
 
   my @includeable_custom_variables = grep { $_->{includeable} } @{ $cvar_configs_ic }, @{ $cvar_configs_ct };
   my @searchable_custom_variables  = grep { $_->{searchable} }  @{ $cvar_configs_ic }, @{ $cvar_configs_ct };


@@ -169,8 +238,10 @@ sub invoice_transactions {
   my @hidden_variables  = (qw(l_headers_mainsort l_headers_subsort l_subtotal_mainsort l_subtotal_subsort l_total l_parts l_customername l_customernumber transdatefrom transdateto decimalplaces customer customername customer_id department partnumber partsgroup country business description project_id customernumber salesman employee salesman_id employee_id business_id partsgroup_id mainsort subsort),
       "$form->{db}number",
       map({ "cvar_$_->{name}" } @searchable_custom_variables),
   my @hidden_variables  = (qw(l_headers_mainsort l_headers_subsort l_subtotal_mainsort l_subtotal_subsort l_total l_parts l_customername l_customernumber transdatefrom transdateto decimalplaces customer customername customer_id department partnumber partsgroup country business description project_id customernumber salesman employee salesman_id employee_id business_id partsgroup_id mainsort subsort),
       "$form->{db}number",
       map({ "cvar_$_->{name}" } @searchable_custom_variables),

-      map { "l_$_" } @columns
+      map { "l_$_" } @columns,

       );
       );

+  push(@hidden_variables, qw(topsortgroup topsorttype l_sort_by_total));  # hidden variables for top
+

   my @hidden_nondefault = grep({ $form->{$_} } @hidden_variables);
   # Variablen werden dann als Hidden Variable mitgegeben, z.B.
   # <input type="hidden" name="report_generator_hidden_transdateto" value="21.05.2010">
   my @hidden_nondefault = grep({ $form->{$_} } @hidden_variables);
   # Variablen werden dann als Hidden Variable mitgegeben, z.B.
   # <input type="hidden" name="report_generator_hidden_transdateto" value="21.05.2010">


@@ -200,6 +271,7 @@ sub invoice_transactions {
     'marge_percent'           => { 'text' => $locale->text('Sales margin %'), },
     'customernumber'          => { 'text' => $locale->text('Customer Number'), },
     'customername'            => { 'text' => $locale->text('Customer Name'), },
     'marge_percent'           => { 'text' => $locale->text('Sales margin %'), },
     'customernumber'          => { 'text' => $locale->text('Customer Number'), },
     'customername'            => { 'text' => $locale->text('Customer Name'), },

+    'sellpricelinetotal'      => { 'text' => $locale->text('sellpricelinetotal'), }, # debug control

 # add 3 more column_defs so we have a translation for top_info_text
     'customer'                => { 'text' => $locale->text('Customer'), },
     'part'                    => { 'text' => $locale->text('Part'), },
 # add 3 more column_defs so we have a translation for top_info_text
     'customer'                => { 'text' => $locale->text('Customer'), },
     'part'                    => { 'text' => $locale->text('Part'), },


@@ -207,14 +279,19 @@ sub invoice_transactions {
     %column_defs_cvars,
   );
 
     %column_defs_cvars,
   );
 

-  map { $column_defs{$_}->{visible} = $form->{"l_$_"} eq 'Y' } @columns;
+  if ( not defined $form->{"l_parts"} ) {
+    # the columns for sellprice, lastcost and marge_percent will be averages in subtotal and total lines
+    map { $column_defs{$_}->{text} = $locale->text('Average symbol') . " " . $column_defs{$_}->{text} } qw(sellprice lastcost marge_percent);
+  };

 
 

-  my %column_alignment = map { $_ => 'right' } qw(lastcost sellprice sellprice_total lastcost_total parts_unit discount marge_total marge_percent qty weight);
+  map { $column_defs{$_}->{visible} = $form->{"l_$_"} eq 'Y' } @columns;

 
 

+  my %column_alignment = map { $_ => 'right' } qw(lastcost sellprice sellprice_total lastcost_total parts_unit discount marge_total marge_percent qty weight sellpricelinetotal );

 
   # so now the check-box "Description" is only used as switch for part description in invoice-mode
   # always fill the column "Description" if we are in Zwischensummenmode
 
   # so now the check-box "Description" is only used as switch for part description in invoice-mode
   # always fill the column "Description" if we are in Zwischensummenmode

-  $form->{"l_description"} = "Y" if not defined $form->{"l_parts"};;
+  $form->{"l_description"} = "Y" if not defined $form->{"l_parts"};
+

   map { $column_defs{$_}->{visible} = $form->{"l_${_}"} ? 1 : 0 } @columns;
 
   my @options;
   map { $column_defs{$_}->{visible} = $form->{"l_${_}"} ? 1 : 0 } @columns;
 
   my @options;


@@ -240,11 +317,20 @@ sub invoice_transactions {
 
   my $report = SL::ReportGenerator->new(\%myconfig, $form);
 
 
   my $report = SL::ReportGenerator->new(\%myconfig, $form);
 

+  if ( not $form->{l_sort_by_total} ) {

   $report->set_options('top_info_text'        => join("\n", $locale->text('Main sorting') . ' : ' . $column_defs{$form->{mainsort}}->{text} , $locale->text('Secondary sorting') . ' : ' . $column_defs{$form->{'subsort'}}->{text}, @options),
                        'output_format'        => 'HTML',
                        'title'                => $form->{title},
                        'attachment_basename'  => $locale->text('Sales Report') . strftime('_%Y%m%d', localtime time),
     );
   $report->set_options('top_info_text'        => join("\n", $locale->text('Main sorting') . ' : ' . $column_defs{$form->{mainsort}}->{text} , $locale->text('Secondary sorting') . ' : ' . $column_defs{$form->{'subsort'}}->{text}, @options),
                        'output_format'        => 'HTML',
                        'title'                => $form->{title},
                        'attachment_basename'  => $locale->text('Sales Report') . strftime('_%Y%m%d', localtime time),
     );

+  } else {
+  $report->set_options('top_info_text'        => join("\n", $locale->text('Grouping') . ' : ' . $column_defs{$form->{topsortgroup}}->{text} , $locale->text('Sort By') . ' : ' . $column_defs{$form->{'topsorttype'}}->{text}, @options),
+                       'output_format'        => 'HTML',
+                       'title'                => $form->{title},
+                       'attachment_basename'  => $locale->text('Sales Report') . strftime('_%Y%m%d', localtime time),
+    );
+  };
+

   $report->set_options_from_form();
   $locale->set_numberformat_wo_thousands_separator(\%myconfig) if lc($report->{options}->{output_format}) eq 'csv';
 
   $report->set_options_from_form();
   $locale->set_numberformat_wo_thousands_separator(\%myconfig) if lc($report->{options}->{output_format}) eq 'csv';
 


@@ -253,7 +339,8 @@ sub invoice_transactions {
 
   $report->set_export_options('invoice_transactions', @hidden_variables, qw(mainsort sortdir));
 
 
   $report->set_export_options('invoice_transactions', @hidden_variables, qw(mainsort sortdir));
 

-  $report->set_sort_indicator($form->{mainsort}, $form->{sortdir});
+  $report->set_sort_indicator($form->{mainsort}, $form->{sortdir}) unless $form->{"l_sort_by_total"};
+  

 
   CVar->add_custom_variables_to_report('module'         => 'CT',
       'trans_id_field' => 'customerid',
 
   CVar->add_custom_variables_to_report('module'         => 'CT',
       'trans_id_field' => 'customerid',


@@ -284,11 +371,13 @@ sub invoice_transactions {
   # escape callback for href
   $callback = $form->escape($href);
 
   # escape callback for href
   $callback = $form->escape($href);
 

-  my @subtotal_columns = qw(qty weight sellprice sellprice_total lastcost lastcost_total marge_total marge_percent discount);
+  my @subtotal_columns = qw(qty weight sellprice sellprice_total lastcost lastcost_total marge_total marge_percent discount sellpricelinetotal);

   # Total sum:
   # sum of sellprice_total, lastcost_total and marge_total
   # average of marge_percent
   # Total sum:
   # sum of sellprice_total, lastcost_total and marge_total
   # average of marge_percent

-  my @total_columns = qw(sellprice_total lastcost_total marge_total marge_percent );
+# absolutes: qty, weight, sellprice_total, lastcost_total, marge_total
+# averages: sellprice, lastcost, marge_percent
+  my @total_columns = qw(sellprice_total lastcost_total marge_total marge_percent sellpricelinetotal);

 
   my %totals     = map { $_ => 0 } @total_columns;
   my %subtotals1 = map { $_ => 0 } @subtotal_columns;
 
   my %totals     = map { $_ => 0 } @total_columns;
   my %subtotals1 = map { $_ => 0 } @subtotal_columns;


@@ -396,9 +485,12 @@ sub invoice_transactions {
     map { $ar->{$_} = $form->format_amount(\%myconfig, $ar->{$_}, 2) } qw(marge_percent qty);
     map { $ar->{$_} = $form->format_amount(\%myconfig, $ar->{$_}, 3) } qw(weight);
     map { $ar->{$_} = $form->format_amount(\%myconfig, $ar->{$_}, $form->{"decimalplaces"} )} qw(lastcost sellprice sellprice_total lastcost_total marge_total);
     map { $ar->{$_} = $form->format_amount(\%myconfig, $ar->{$_}, 2) } qw(marge_percent qty);
     map { $ar->{$_} = $form->format_amount(\%myconfig, $ar->{$_}, 3) } qw(weight);
     map { $ar->{$_} = $form->format_amount(\%myconfig, $ar->{$_}, $form->{"decimalplaces"} )} qw(lastcost sellprice sellprice_total lastcost_total marge_total);

+    map { $ar->{$_} = $form->format_amount(\%myconfig, $ar->{$_}, $form->{"decimalplaces"} )} qw(sellpricelinetotal) if $form->{"l_sellpricelinetotal"};

 
     # Einzelzeilen nur zeigen wenn l_parts gesetzt ist, nützlich, wenn man nur
     # Subtotals und Totals sehen möchte
 
     # Einzelzeilen nur zeigen wenn l_parts gesetzt ist, nützlich, wenn man nur
     # Subtotals und Totals sehen möchte

+    # these lines are only added to report in individual mode
+    

     if ($form->{l_parts}) {
       my %row = (
         map { ($_ => { data => $ar->{$_}, align => $column_alignment{$_} }) } @columns
     if ($form->{l_parts}) {
       my %row = (
         map { ($_ => { data => $ar->{$_}, align => $column_alignment{$_} }) } @columns


@@ -414,6 +506,7 @@ sub invoice_transactions {
 
     # hier wird bei l_subtotal nicht differenziert zwischen mainsort und subsort
     # macht man l_subtotal_mainsort aus wird l_subtotal_subsort auch nicht ausgeführt
 
     # hier wird bei l_subtotal nicht differenziert zwischen mainsort und subsort
     # macht man l_subtotal_mainsort aus wird l_subtotal_subsort auch nicht ausgeführt

+    # create a subtotal line if we are at the last element or either the subsort or mainsort element has changed

     if (   ($form->{l_subtotal_mainsort} eq 'Y')
         && ($form->{l_subtotal_subsort}  eq 'Y')
         && (($idx == (scalar @{ $form->{AR} } - 1))   # last element always has a subtotal
     if (   ($form->{l_subtotal_mainsort} eq 'Y')
         && ($form->{l_subtotal_subsort}  eq 'Y')
         && (($idx == (scalar @{ $form->{AR} } - 1))   # last element always has a subtotal


@@ -421,9 +514,20 @@ sub invoice_transactions {
           || ($ar->{ $form->{'mainsort'} } ne $form->{AR}->[$idx + 1]->{ $form->{'mainsort'} })
           )) {   # if value that is sorted by changes, print subtotal
 
           || ($ar->{ $form->{'mainsort'} } ne $form->{AR}->[$idx + 1]->{ $form->{'mainsort'} })
           )) {   # if value that is sorted by changes, print subtotal
 

-      $report->add_data(create_subtotal_row_invoice(\%subtotals2, \@columns, \%column_alignment, \@subtotal_columns, $form->{l_parts} ? 'listsubtotal' : undef, $ar->{ $form->{'subsort'} }));
-      $report->add_data({ %empty_row }) if $form->{l_parts} and $addemptylines;
-    }
+
+      # stick subtotal_row_invoice in temporary variable
+      # if TOP mode store it in @data_to_be_sorted so we can sort the lines after all lines have been added
+      # otherwise just add to $report to be shown in defualt
+      my $tmpobject = create_subtotal_row_invoice(\%subtotals2, \@columns, \%column_alignment, \@subtotal_columns, $form->{l_parts} ? 'listsubtotal' : undef, $ar->{ $form->{'subsort'} }); 
+      if ( $form->{l_sort_by_total} and not $form->{l_parts} ) {
+        # add subtotal data to temporary array that can later be sorted
+        push(@data_to_be_sorted, $tmpobject);
+      } else {
+        $report->add_data( $tmpobject);
+      };
+     $report->add_data({ %empty_row }) if $form->{l_parts} and $addemptylines;
+   
+    };

 
     # if last mainsort is reached or mainsort has changed, add mainsort subtotal and empty row
     if (   ($form->{l_subtotal_mainsort} eq 'Y')
 
     # if last mainsort is reached or mainsort has changed, add mainsort subtotal and empty row
     if (   ($form->{l_subtotal_mainsort} eq 'Y')


@@ -440,12 +544,34 @@ sub invoice_transactions {
 
     $idx++;
   }
 
     $idx++;
   }

+
+
+  if ( $form->{l_sort_by_total} ) {
+    # filter allowed parameters for sorttype
+    my @allowed_fields = qw(sellprice_total marge_total qty weight);
+    my ($sorttype) = grep { /^$form->{topsorttype}$/ } @allowed_fields;
+    die "illegal parameter for topsorttype" unless $sorttype;
+  
+    # add marker to column being sorted
+    $report->set_sort_indicator($sorttype);
+  
+    # add subtotal lines to report in numerical order of sorttype
+    @data_to_be_sorted = reverse sort { $a->{$sorttype}{unformatted} <=> $b->{$sorttype}{unformatted} } @data_to_be_sorted;
+    foreach( @data_to_be_sorted ) {
+      # to limit output, e.g. top 100, one could add a counter here
+      # that only adds a certain number of entries
+      # the total at the end would still be over all entries though 
+      $report->add_data( $_ );
+    };
+  };
+

   if ( $form->{l_total} eq "Y" ) {
     $report->add_separator();
     $report->add_data(create_subtotal_row_invoice(\%totals, \@columns, \%column_alignment, \@total_columns, 'listtotal', 'l_total'))
   };
   if ( $form->{l_total} eq "Y" ) {
     $report->add_separator();
     $report->add_data(create_subtotal_row_invoice(\%totals, \@columns, \%column_alignment, \@total_columns, 'listtotal', 'l_total'))
   };

-
+  

   $report->generate_with_headers();
   $report->generate_with_headers();

+

   $main::lxdebug->leave_sub();
 }
 
   $main::lxdebug->leave_sub();
 }
 


@@ -470,10 +596,10 @@ sub create_subtotal_row_invoice {
     $row->{description}->{data} = $locale->text('Total') . ' ' . $name;
   };
 
     $row->{description}->{data} = $locale->text('Total') . ' ' . $name;
   };
 

+  map { $row->{$_}->{unformatted} = $totals->{$_} } qw(sellprice_total lastcost_total marge_total qty weight);

   map { $row->{$_}->{data} = $form->format_amount(\%myconfig, $totals->{$_}, 2) } qw(marge_total marge_percent qty);
   map { $row->{$_}->{data} = $form->format_amount(\%myconfig, $totals->{$_}, 3) } qw(weight);
   map { $row->{$_}->{data} = $form->format_amount(\%myconfig, $totals->{$_}, 2) } qw(marge_total marge_percent qty);
   map { $row->{$_}->{data} = $form->format_amount(\%myconfig, $totals->{$_}, 3) } qw(weight);

-  map { $row->{$_}->{data} = $form->format_amount(\%myconfig, $totals->{$_}, $form->{decimalplaces}) } qw(lastcost sellprice sellprice_total lastcost_total);
-
+  map { $row->{$_}->{data} = $form->format_amount(\%myconfig, $totals->{$_}, $form->{decimalplaces}) } qw(lastcost sellprice sellprice_total lastcost_total sellpricelinetotal);

 
   map { $totals->{$_} = 0 } @{ $subtotal_columns };
 
 
   map { $totals->{$_} = 0 } @{ $subtotal_columns };
 

diff --git a/locale/de/all b/locale/de/all
index 87de98e61726976afe48c782fd5f86dafac4ad8f..7aec6c3c4669ae15b04c068031c21d56d137032a 100755 (executable)
--- a/locale/de/all
+++ b/locale/de/all
@@ -255,6 +255,8 @@ $self->{texts} = {
   'Automatically created invoice for fee and interest for dunning %s' => 'Automatisch erzeugte Rechnung für Gebühren und Zinsen zu Mahnung %s',
   'Available'                   => 'Verfügbar',
   'Available qty'               => 'Lagerbestand',
   'Automatically created invoice for fee and interest for dunning %s' => 'Automatisch erzeugte Rechnung für Gebühren und Zinsen zu Mahnung %s',
   'Available'                   => 'Verfügbar',
   'Available qty'               => 'Lagerbestand',

+  'Average symbol'              => 'Ø',
+  'Averaged values'             => 'Gemittelte Werte',

   'BALANCE SHEET'               => 'BILANZ',
   'BIC'                         => 'BIC',
   'BOM'                         => 'Stückliste',
   'BALANCE SHEET'               => 'BILANZ',
   'BIC'                         => 'BIC',
   'BOM'                         => 'Stückliste',


@@ -546,6 +548,7 @@ $self->{texts} = {
   'Credit Tax Account'          => 'Umsatzsteuerkonto',
   'Credit note (one letter abbreviation)' => 'G',
   'Cumulated or averaged values' => 'Kumulierte oder gemittelte Werte',
   'Credit Tax Account'          => 'Umsatzsteuerkonto',
   'Credit note (one letter abbreviation)' => 'G',
   'Cumulated or averaged values' => 'Kumulierte oder gemittelte Werte',

+  'Cumulated values'            => 'Kumulierte Werte',

   'Curr'                        => 'Währung',
   'Currencies'                  => 'Währungen',
   'Currency'                    => 'Währung',
   'Curr'                        => 'Währung',
   'Currencies'                  => 'Währungen',
   'Currency'                    => 'Währung',


@@ -999,6 +1002,7 @@ $self->{texts} = {
   'Group membership'            => 'Gruppenzugehörigkeit',
   'Group missing!'              => 'Warengruppe fehlt!',
   'Group saved!'                => 'Warengruppe gespeichert!',
   'Group membership'            => 'Gruppenzugehörigkeit',
   'Group missing!'              => 'Warengruppe fehlt!',
   'Group saved!'                => 'Warengruppe gespeichert!',

+  'Grouping'                    => 'Gruppierung',

   'Groups'                      => 'Warengruppen',
   'Groups that are valid for this client for access rights' => 'Gruppen, die für diesen Mandanten gültig sind',
   'Groups this user is a member in' => 'Gruppen, in denen Benutzer Mitglied ist',
   'Groups'                      => 'Warengruppen',
   'Groups that are valid for this client for access rights' => 'Gruppen, die für diesen Mandanten gültig sind',
   'Groups this user is a member in' => 'Gruppen, in denen Benutzer Mitglied ist',


@@ -1726,6 +1730,7 @@ $self->{texts} = {
   'Sales Price information'     => 'Verkaufspreisinformation',
   'Sales Quotations'            => 'Angebote',
   'Sales Report'                => 'Verkaufsbericht',
   'Sales Price information'     => 'Verkaufspreisinformation',
   'Sales Quotations'            => 'Angebote',
   'Sales Report'                => 'Verkaufsbericht',

+  'Sales Report Top'            => 'Verkaufsbericht Top',

   'Sales and purchase invoices with inventory transactions with taxkeys' => 'Einkaufs- und Verkaufsrechnungen mit Warenbestandsbuchungen mit Steuerschlüsseln',
   'Sales delivery order'        => 'Lieferschein (Verkauf)',
   'Sales invoice number'        => 'Ausgangsrechnungsnummer',
   'Sales and purchase invoices with inventory transactions with taxkeys' => 'Einkaufs- und Verkaufsrechnungen mit Warenbestandsbuchungen mit Steuerschlüsseln',
   'Sales delivery order'        => 'Lieferschein (Verkauf)',
   'Sales invoice number'        => 'Ausgangsrechnungsnummer',


@@ -1733,6 +1738,8 @@ $self->{texts} = {
   'Sales invoices changeable'   => 'Änderbarkeit von Verkaufsrechnungen',
   'Sales margin'                => 'Marge',
   'Sales margin %'              => 'Marge prozentual',
   'Sales invoices changeable'   => 'Änderbarkeit von Verkaufsrechnungen',
   'Sales margin'                => 'Marge',
   'Sales margin %'              => 'Marge prozentual',

+  'Sales margin percent'        => 'Verkaufsmarge prozentual',
+  'Sales margin total'          => 'Verkaufsmarge gesamt',

   'Sales net amount'            => 'VK-Betrag',
   'Sales price'                 => 'VK-Preis',
   'Sales price total'           => 'VK-Betrag',
   'Sales net amount'            => 'VK-Betrag',
   'Sales price'                 => 'VK-Preis',
   'Sales price total'           => 'VK-Betrag',


@@ -2256,6 +2263,7 @@ $self->{texts} = {
   'Top Level Designation only'  => 'Nur Hauptartikelbezeichnung',
   'Total'                       => 'Summe',
   'Total Fees'                  => 'Kumulierte Gebühren',
   'Top Level Designation only'  => 'Nur Hauptartikelbezeichnung',
   'Total'                       => 'Summe',
   'Total Fees'                  => 'Kumulierte Gebühren',

+  'Total revenue'               => 'Gesamtumsatz',

   'Total stock value'           => 'Gesamter Bestandswert',
   'Total sum'                   => 'Gesamtsumme',
   'Total weight'                => 'Gesamtgewicht',
   'Total stock value'           => 'Gesamter Bestandswert',
   'Total sum'                   => 'Gesamtsumme',
   'Total weight'                => 'Gesamtgewicht',


@@ -2646,6 +2654,7 @@ $self->{texts} = {
   'saved'                       => 'gespeichert',
   'saved!'                      => 'gespeichert',
   'saving data'                 => 'Speichere Daten',
   'saved'                       => 'gespeichert',
   'saved!'                      => 'gespeichert',
   'saving data'                 => 'Speichere Daten',

+  'sellpricelinetotal'          => 'sellpricelinetotal',

   'sent'                        => 'gesendet',
   'sent to printer'             => 'an Drucker geschickt',
   'service'                     => 'Dienstleistung',
   'sent'                        => 'gesendet',
   'sent to printer'             => 'an Drucker geschickt',
   'service'                     => 'Dienstleistung',

diff --git a/menus/erp.ini b/menus/erp.ini
index b0bff6139632df560f821c53775114edff50ca9b..2d4b5299f825fa73d71ed2529b5fe1f49211eaeb 100644 (file)
--- a/menus/erp.ini
+++ b/menus/erp.ini
@@ -159,6 +159,12 @@ module=vk.pl
 action=search_invoice
 nextsub=invoice_transactions
 
 action=search_invoice
 nextsub=invoice_transactions
 

+[AR--Reports--Sales Report Top]
+ACCESS=invoice_edit
+module=vk.pl
+action=search_invoice_top
+nextsub=invoice_transactions
+

 [AR--Reports--Dunnings]
 ACCESS=dunning_edit
 module=dn.pl
 [AR--Reports--Dunnings]
 ACCESS=dunning_edit
 module=dn.pl

diff --git a/templates/webpages/common/search_history.html b/templates/webpages/common/search_history.html
index 8076bd8bb592802d37bb01412475176dcd0df9cf..be0d487c488d2c3905ada83cc98f5e03e00cee00 100644 (file)
--- a/templates/webpages/common/search_history.html
+++ b/templates/webpages/common/search_history.html
@@ -74,7 +74,7 @@
 
 <script type="text/javascript">
   <!--
 
 <script type="text/javascript">
   <!--

-  var defaults = ['SAVED', 'DELETED', 'ADDED', 'PAYMENT POSTED', 'POSTED', 'POSTED AS NEW', 'SAVED FOR DUNNING', 'DUNNING STARTED', 'PRINTED'];
+  var defaults = ['SAVED', 'DELETED', 'ADDED', 'PAYMENT POSTED', 'POSTED', 'POSTED AS NEW', 'SAVED FOR DUNNING', 'DUNNING STARTED', 'PRINTED', 'MAILED'];

   var available;
   var selected;
   var translated = {
   var available;
   var selected;
   var translated = {


@@ -87,6 +87,7 @@
     'SAVED FOR DUNNING' : '[% 'SAVED FOR DUNNING' | $T8 %]',
     'DUNNING STARTED'   : '[% 'DUNNING STARTED' | $T8 %]',
     'PRINTED'           : '[% 'PRINTED' | $T8 %]',
     'SAVED FOR DUNNING' : '[% 'SAVED FOR DUNNING' | $T8 %]',
     'DUNNING STARTED'   : '[% 'DUNNING STARTED' | $T8 %]',
     'PRINTED'           : '[% 'PRINTED' | $T8 %]',

+    'MAILED'            : '[% 'MAILED' | $T8 %]',

   };
 
   function addForm(index) {
   };
 
   function addForm(index) {

diff --git a/templates/webpages/vk/search_invoice.html b/templates/webpages/vk/search_invoice.html
index 8a773cec635c092e726a964c8001feaca3f9765f..d04e386360ac79f541ab2c3545d4657e8028b1d2 100644 (file)
--- a/templates/webpages/vk/search_invoice.html
+++ b/templates/webpages/vk/search_invoice.html
@@ -249,14 +249,14 @@
           <tr>
             <td align=left><input name="l_sellprice_total" class=checkbox type=checkbox value=Y checked>[% 'Sales price total' | $T8 %]</td>
             <td align=left><input name="l_lastcost_total" class=checkbox type=checkbox value=Y checked>[% 'Purchase price total' | $T8 %]</td>
           <tr>
             <td align=left><input name="l_sellprice_total" class=checkbox type=checkbox value=Y checked>[% 'Sales price total' | $T8 %]</td>
             <td align=left><input name="l_lastcost_total" class=checkbox type=checkbox value=Y checked>[% 'Purchase price total' | $T8 %]</td>

-            <td align=left><input name="l_marge_total" class=checkbox type=checkbox value=Y checked>[% 'Margetotal' | $T8 %]</td>
+            <td align=left><input name="l_marge_total" class=checkbox type=checkbox value=Y checked>[% 'Sales margin total' | $T8 %]</td>

             <td colspan="4"> ([% 'Single values in item mode, cumulated values in invoice mode' | $T8 %])
           </tr>
 
           <tr>
             <td align=left><input name="l_sellprice" class=checkbox type=checkbox value=Y checked>[% 'Sales price' | $T8 %]</td>
             <td align=left><input name="l_lastcost" class=checkbox type=checkbox value=Y checked>[% 'Purchase price' | $T8 %]</td>
             <td colspan="4"> ([% 'Single values in item mode, cumulated values in invoice mode' | $T8 %])
           </tr>
 
           <tr>
             <td align=left><input name="l_sellprice" class=checkbox type=checkbox value=Y checked>[% 'Sales price' | $T8 %]</td>
             <td align=left><input name="l_lastcost" class=checkbox type=checkbox value=Y checked>[% 'Purchase price' | $T8 %]</td>

-            <td align=left><input name="l_marge_percent" class=checkbox type=checkbox value=Y checked>[% 'Margepercent' | $T8 %]</td>
+            <td align=left><input name="l_marge_percent" class=checkbox type=checkbox value=Y checked>[% 'Sales margin percent' | $T8 %]</td>

             <td colspan="4">([% 'averaged values, in invoice mode only useful when filtered by a part' | $T8 %])</td>
           </tr>
 
             <td colspan="4">([% 'averaged values, in invoice mode only useful when filtered by a part' | $T8 %])</td>
           </tr>
 

diff --git a/templates/webpages/vk/search_invoice_top.html b/templates/webpages/vk/search_invoice_top.html
new file mode 100644 (file)
index 0000000..199c8a6
--- /dev/null
+++ b/templates/webpages/vk/search_invoice_top.html
@@ -0,0 +1,280 @@
+[%- USE T8 %]
+[%- USE L %]
+
+<form method=post name="search_invoice_top" action=[% script %]>
+  <input type="hidden" name="sort" value="transdate">
+  <input type="hidden" name="l_sort_by_total" value="Y">
+  <input type=hidden name=nextsub value=[% nextsub %]>
+
+  <div class="listtop">[% title %]</div>
+
+
+  <table>
+
+    <tr>
+      <td align="right">[% 'Main sorting' | $T8 %]</td>
+      <td>
+        <select name="topsortmain" id="topsortmain">
+          <option value="description">[% 'Part' | $T8 %]</option>
+          <option value="customername" selected="selected">[% 'Customer' | $T8 %]</option>
+          <option value="country">[% 'Country' | $T8 %]</option>
+          <option value="partsgroup">[% 'Group' | $T8 %]</option>
+          <option value="business">[% 'Customer type' | $T8 %]</option>
+          <option value="salesman">[% 'Salesman' | $T8 %]</option>
+          <option value="month">[% 'Month' | $T8 %]</option>
+        </select>
+    </tr>
+    <tr>
+      <td align="right">[% 'Sort By' | $T8 %]</td>
+      <td>
+        <select name="topsorttype" id="topsorttype">
+          <option value="sellprice_total" selected="selected">[% 'Total revenue' | $T8 %]</option>
+          <option value="marge_total">[% 'Sales margin' | $T8 %]</option>
+          <option value="qty">[% 'Quantity' | $T8 %]</option>
+          <option value="weight">[% 'Weight' | $T8 %]</option>
+        </select>
+      </td>
+      </td>
+    </tr>
+
+    <tr>
+      <th align="right">[% 'Total sum' | $T8 %]</th>
+      <td colspan="1" align=left><input name="l_total" class=checkbox type=checkbox value=Y checked></td>
+      <td align="right" nowrap>[% 'Decimalplaces' | $T8 %]: </td>
+      <td colspan="2"><input name="decimalplaces" size="2" value="2"></td>
+    </tr>
+
+
+    <tr>
+      <td></td>
+      <td colspan="7">
+        <hr size="1" noshade="">
+      </td>
+    <tr>
+
+    <tr>
+      <th align=right>[% 'Customer' | $T8 %]</th>
+      <td>
+        [%- INCLUDE 'generic/multibox.html'
+          name          = 'customer',
+          default       = oldcustomer,
+          style         = 'width: 250px',
+          DATA          = ALL_VC,
+          id_sub        = 'vc_keys',
+          label_key     = 'name',
+          select        = vc_select,
+          limit         = vclimit,
+          show_empty    = 1,
+          allow_textbox = 1,
+          class         = 'initial_focus',
+        -%]
+      </td>
+
+      <th align="right" nowrap>[% 'Customer Number' | $T8 %]</th>
+      <td>
+        <input name="customernumber" size="20">
+      </td>
+    </tr>
+
+    <tr>
+      <th align=right nowrap>[% 'Department' | $T8 %]</th>
+      <td>
+        [%- INCLUDE 'generic/multibox.html'
+          name          = 'department',
+          style         = 'width: 250px',
+          DATA          = ALL_DEPARTMENTS,
+          id_key        = 'id',
+          label_key     = 'description',
+          show_empty    = 1,
+          allow_textbox = 0,
+        -%]
+      </td>
+
+      <th align="right">[% 'Project Number' | $T8 %]</th>
+      <td>
+        [%- INCLUDE 'generic/multibox.html'
+          name          =  'project_id',
+          style         = "width: 250px",
+          DATA          =  ALL_PROJECTS,
+          id_key        = 'id',
+          label_key     = 'projectnumber',
+          limit         = vclimit,
+          show_empty    = 1,
+          allow_textbox = 0,
+        -%]
+      </td>
+    </tr>
+
+    <tr>
+      <th align="right" nowrap>[% 'Part Number' | $T8 %]</th>
+      <td><input name="partnumber" size="20"></td>
+    </tr>
+
+    <tr>
+      <th align="right" nowrap>[% 'Part Description' | $T8 %]</th>
+      <td>
+        <input name="description" size="40">
+      </td>
+    </tr>
+
+    <tr>
+      <th align="right">[% 'Group' | $T8 %]</th>
+      <td>
+        [%- INCLUDE 'generic/multibox.html'
+          name          = 'partsgroup_id',
+          style         = 'width: 250px',
+          DATA          =  ALL_PARTSGROUPS,
+          id_key        = 'id',
+          label_key     = 'partsgroup',
+          show_empty    = 1,
+          allow_textbox = 0,
+        -%]
+      </td>
+
+      <td align="right" nowrap>[% 'Country' | $T8 %]</td>
+      <td><input name="country" size="20"></td>
+    </tr>
+
+    <tr>
+      <th align="right">[% 'Employee' | $T8 %]</th>
+      <td>
+        [%- INCLUDE 'generic/multibox.html'
+          name          = 'employee_id',
+          style         = 'width: 250px',
+          DATA          =  ALL_EMPLOYEES,
+          id_key        = 'id',
+          label_sub     = 'employee_labels',
+          limit         = vclimit,
+          show_empty    = 1,
+          allow_textbox = 0,
+          default       = ' ',
+        -%]
+      </td>
+
+      <th align="right">[% 'Salesman' | $T8 %]</th>
+      <td>
+        [%- INCLUDE 'generic/multibox.html'
+          name          = 'salesman_id',
+          style         = 'width: 250px',
+          DATA          =  ALL_SALESMEN,
+          id_key        = 'id',
+          label_sub     = 'salesman_labels',
+          limit         = vclimit,
+          show_empty    = 1,
+          allow_textbox = 0,
+        -%]
+      </td>
+    </tr>
+
+    <tr>
+      <th align="right">[% 'Customer type' | $T8 %]</th>
+      <td>
+        [%- INCLUDE 'generic/multibox.html'
+           name          =  'business_id',
+           style         = "width: 250px",
+           DATA          =  ALL_BUSINESS_TYPES,
+           id_key        = 'id',
+           label_key     = 'description',
+           limit         = vclimit,
+           show_empty    = 1,
+           allow_textbox = 0,
+        -%]
+      </td>
+    </tr>
+
+    <tr>
+      <th align=right nowrap>[% 'Invoice Date' | $T8 %] [% 'From' | $T8 %]</th>
+      <td>
+        [% L.date_tag('transdatefrom') %]
+      </td>
+
+      <th align=right>[% 'Bis' | $T8 %]</th>
+
+      <td>
+        [% L.date_tag('transdateto') %]
+      </td>
+    </tr>
+
+
+    <tr>
+      <td></td>
+      <th colspan="4" align="left">
+        [% 'Filter for customer variables' | $T8 %]
+      </th>
+    </tr>
+    [% CUSTOM_VARIABLES_FILTER_CODE_CT %]
+
+
+    <tr>
+      <td></td>
+      <th colspan="4" align="left">
+        [% 'Filter for item variables' | $T8 %]
+      </th>
+    </tr>
+    [% CUSTOM_VARIABLES_FILTER_CODE_IC %]
+
+
+
+    <tr>
+      <td></td>
+      <td colspan="7">
+        <hr size="1" noshade="">
+      </td>
+    <tr>
+
+
+
+    <tr>
+      <th align=right nowrap>[% 'Include in Report' | $T8 %]</th>
+
+      <td colspan="3">
+        <table>
+          <tr>
+            <td align=left><input name="l_qty" class=checkbox type=checkbox value=Y checked>[% 'Quantity' | $T8 %]</td>
+            <td align=left><input name="l_sellprice_total" class=checkbox type=checkbox value=Y checked>[% 'Sales price total' | $T8 %]</td>
+            <td align=left><input name="l_lastcost_total" class=checkbox type=checkbox value=Y>[% 'Purchase price total' | $T8 %]</td>
+            <td align=left><input name="l_marge_total" class=checkbox type=checkbox value=Y>[% 'Margetotal' | $T8 %]</td>
+            <td colspan="1"> ([% 'Cumulated values' | $T8 %])
+          </tr>
+
+          <tr>
+            <td align=left><input name="l_discount" class=checkbox type=checkbox value=Y>[% 'Discount' | $T8 %]</td>
+            <td align=left><input name="l_sellprice" class=checkbox type=checkbox value=Y>[% 'Sales price' | $T8 %]</td>
+            <td align=left><input name="l_lastcost" class=checkbox type=checkbox value=Y>[% 'Purchase price' | $T8 %]</td>
+            <td align=left><input name="l_marge_percent" class=checkbox type=checkbox value=Y>[% 'Margepercent' | $T8 %]</td>
+            <td colspan="1">([% 'Averaged values' | $T8 %])</td>
+          </tr>
+
+          <tr>
+            <td align=left><input name="l_weight" class=checkbox type=checkbox value=Y>[% 'Weight' | $T8 %]</td>
+            <td></td>
+            <td></td>
+            <td></td>
+            <td>([% 'Averaged values' | $T8 %])</td>
+          </tr>
+
+          <tr>
+            <td colspan="7">&nbsp;</td>
+          </tr>
+
+        </table>
+      </td>
+    </tr>
+
+  </table>
+
+  <hr size="3" noshade="">
+
+  <input class=submit type=submit name=action value="[% 'Continue' | $T8 %]">
+
+  <br><br>
+
+</form>
+
+<script type="text/javascript">
+<!--
+$(document).ready(function(){
+  $('customer').focus();
+})
+//-->
+</script>