Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision 44c040d3

Von G. Richardson vor mehr als 12 Jahren hinzugefügt

  • ID 44c040d36bf1816cd3abfb16d357359ec5c398ca
  • Vorgänger a521b29b
  • Nachfolger 165c6039

Verkaufsbericht: numerische Sortierung bei Monat

Die alte Abfrage hat im Modus Monat/Ware die Artikel nicht korrekt zusammengefasst
Statt Sortierung nach Monatsname/Rechnungsdatum/Artikelname jetzt einfach Monatsnummer/Artikelname

Unterschiede anzeigen:

SL/VK.pm
51 51 
  my @values;
52 52 

  		




  53
  53
  
    
  my $query =


  		




  54
  
  
    
    qq|SELECT ct.id as customerid, ct.name as customername,ct.customernumber,ct.country,ar.invnumber,ar.id,ar.transdate,p.partnumber,pg.partsgroup,i.parts_id,i.qty,i.price_factor,i.discount,i.description as description,i.lastcost,i.sellprice,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 | .


  		




  
  54
  
    
    qq|SELECT ct.id as customerid, ct.name as customername,ct.customernumber,ct.country,ar.invnumber,ar.id,ar.transdate,p.partnumber,pg.partsgroup,i.parts_id,i.qty,i.price_factor,i.discount,i.description as description,i.lastcost,i.sellprice,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 | .


  		




  55
  55
  
    
    qq|FROM invoice i | .  


  		




  56
  56
  
    
    qq|JOIN ar on (i.trans_id = ar.id) | .


  		




  57
  57
  
    
    qq|JOIN parts p on (i.parts_id = p.id) | .


  		




  ......




  78
  78
  
    
  $where .= " AND i.assemblyitem is not true ";


  		




  79
  79
  
    

  		




  80
  80
  
    
  my $sortorder;


  		




  81
  
  
    
  # sorting by month is a special case:


  		




  82
  
  
    
  # Sorting by month, using salesman as an example:


  		




  83
  
  
    
  # Sorting with month as mainsort: ORDER BY month,salesman,ar.transdate,ar.invnumber


  		




  84
  
  
    
  # Sorting with month as subsort:  ORDER BY salesman,ar.transdate,month,ar.invnumber


  		




  
  81
  
    

  		




  
  82
  
    
  # sorting by month is a special case, we don't want to sort alphabetically by


  		




  
  83
  
    
  # month name, so we also extract a numerical month in the from YYYYMM to sort


  		




  
  84
  
    
  # by in case of month sorting


  		




  
  85
  
    
  # Sorting by month, using description as an example:


  		




  
  86
  
    
  # Sorting with month as mainsort: ORDER BY nummonth,description,ar.transdate,ar.invnumber


  		




  
  87
  
    
  # Sorting with month as subsort:  ORDER BY description,nummonth,ar.transdate,ar.invnumber


  		




  85
  88
  
    
  if ($form->{mainsort} eq 'month') {


  		




  86
  
  
    
    $sortorder .= "ar.transdate,month,"


  		




  
  89
  
    
    $sortorder .= "nummonth,"


  		




  87
  90
  
    
  } else {


  		




  88
  91
  
    
    $sortorder .= $form->{mainsort} . ",";


  		




  89
  92
  
    
  };


  		




  90
  93
  
    
  if ($form->{subsort} eq 'month') {


  		




  91
  
  
    
    $sortorder .= "ar.transdate,month,"


  		




  
  94
  
    
    $sortorder .= "nummonth,"


  		




  92
  95
  
    
  } else {


  		




  93
  96
  
    
    $sortorder .= $form->{subsort} . ",";


  		




  94
  97
  
    
  };


  		




  95
  
  
    
  $sortorder .= 'ar.transdate,' unless $form->{subsort} eq 'month';


  		




  96
  
  
    
  $sortorder .= 'ar.invnumber';


  		




  
  98
  
    
  $sortorder .= 'ar.transdate,ar.invnumber';  # Default sorting order after mainsort und subsort


  		




  97
  99
  
    

  		




  98
  
  
    
#  $sortorder =~ s/month/ar.transdate/;


  		




  99
  100
  
    

  		




  100
  101
  
    
  if ($form->{customer_id}) {


  		




  101
  102
  
    
    $where .= " AND ar.customer_id = ?";


  		












Auch abrufbar als:  Unified diff