Revision a521b29b
Von G. Richardson vor mehr als 12 Jahren hinzugefügt
| SL/VK.pm | ||
|---|---|---|
| 51 | 51 |
my @values; |
| 52 | 52 |
|
| 53 | 53 |
my $query = |
| 54 |
qq|SELECT cus.name,cus.customernumber,ar.invnumber,ar.id,ar.transdate,p.partnumber,i.parts_id,i.qty,i.price_factor,i.discount,i.description,i.lastcost,i.sellprice,i.fxsellprice,i.marge_total,i.marge_percent,i.unit | .
|
|
| 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 | .
|
|
| 55 | 55 |
qq|FROM invoice i | . |
| 56 |
qq|join ar on (i.trans_id = ar.id) | . |
|
| 57 |
qq|join parts p on (i.parts_id = p.id) | . |
|
| 58 |
qq|join customer cus on (cus.id = ar.customer_id) |; |
|
| 56 |
qq|JOIN ar on (i.trans_id = ar.id) | . |
|
| 57 |
qq|JOIN parts p on (i.parts_id = p.id) | . |
|
| 58 |
qq|LEFT JOIN partsgroup pg on (p.partsgroup_id = pg.id) | . |
|
| 59 |
qq|LEFT JOIN customer ct on (ct.id = ar.customer_id) | . |
|
| 60 |
qq|LEFT JOIN business b on (ct.business_id = b.id) | . |
|
| 61 |
qq|LEFT JOIN employee e ON (ar.employee_id = e.id) | . |
|
| 62 |
qq|LEFT JOIN employee e2 ON (ar.salesman_id = e2.id) |; |
|
| 59 | 63 |
|
| 60 | 64 |
my $where = "1 = 1"; |
| 61 | 65 |
|
| 66 |
# if employee can only see his own invoices, make sure this also holds for sales report |
|
| 67 |
# limits by employees (Bearbeiter), not salesmen! |
|
| 68 |
if (!$main::auth->assert('sales_all_edit', 1)) {
|
|
| 69 |
$where .= " AND ar.employee_id = (select id from employee where login= ?)"; |
|
| 70 |
push (@values, $form->{login});
|
|
| 71 |
} |
|
| 72 |
|
|
| 62 | 73 |
# Stornierte Rechnungen und Stornorechnungen in invoice rausfiltern |
| 74 |
# was ist mit Gutschriften? |
|
| 63 | 75 |
$where .= " AND ar.storno is not true "; |
| 64 | 76 |
|
| 65 | 77 |
# Bestandteile von Erzeugnissen herausfiltern |
| 66 | 78 |
$where .= " AND i.assemblyitem is not true "; |
| 67 | 79 |
|
| 68 |
my $sortorder = "cus.name,i.parts_id,ar.transdate"; |
|
| 69 |
if ($form->{sortby} eq 'artikelsort') {
|
|
| 70 |
$sortorder = "i.parts_id,cus.name,ar.transdate"; |
|
| 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 |
|
| 85 |
if ($form->{mainsort} eq 'month') {
|
|
| 86 |
$sortorder .= "ar.transdate,month," |
|
| 87 |
} else {
|
|
| 88 |
$sortorder .= $form->{mainsort} . ",";
|
|
| 71 | 89 |
}; |
| 90 |
if ($form->{subsort} eq 'month') {
|
|
| 91 |
$sortorder .= "ar.transdate,month," |
|
| 92 |
} else {
|
|
| 93 |
$sortorder .= $form->{subsort} . ",";
|
|
| 94 |
}; |
|
| 95 |
$sortorder .= 'ar.transdate,' unless $form->{subsort} eq 'month';
|
|
| 96 |
$sortorder .= 'ar.invnumber'; |
|
| 97 |
|
|
| 98 |
# $sortorder =~ s/month/ar.transdate/; |
|
| 72 | 99 |
|
| 73 | 100 |
if ($form->{customer_id}) {
|
| 74 | 101 |
$where .= " AND ar.customer_id = ?"; |
| 75 | 102 |
push(@values, $form->{customer_id});
|
| 76 | 103 |
}; |
| 77 | 104 |
if ($form->{customernumber}) {
|
| 78 |
$where .= qq| AND cus.customernumber = ? |;
|
|
| 105 |
$where .= qq| AND ct.customernumber = ? |;
|
|
| 79 | 106 |
push(@values, $form->{customernumber});
|
| 80 | 107 |
} |
| 81 | 108 |
if ($form->{partnumber}) {
|
| 82 | 109 |
$where .= qq| AND (p.partnumber ILIKE ?)|; |
| 83 | 110 |
push(@values, '%' . $form->{partnumber} . '%');
|
| 84 | 111 |
} |
| 112 |
if ($form->{partsgroup_id}) {
|
|
| 113 |
$where .= qq| AND (pg.id = ?)|; |
|
| 114 |
push(@values, $form->{partsgroup_id});
|
|
| 115 |
} |
|
| 116 |
if ($form->{country}) {
|
|
| 117 |
$where .= qq| AND (ct.country ILIKE ?)|; |
|
| 118 |
push(@values, '%' . $form->{country} . '%');
|
|
| 119 |
} |
|
| 85 | 120 |
# nimmt man description am Besten aus invoice oder parts? |
| 86 | 121 |
if ($form->{description}) {
|
| 87 | 122 |
$where .= qq| AND (i.description ILIKE ?)|; |
| ... | ... | |
| 100 | 135 |
$where .= " AND ar.department_id = ?"; |
| 101 | 136 |
push(@values, $department_id); |
| 102 | 137 |
} |
| 138 |
if ($form->{employee_id}) {
|
|
| 139 |
$where .= " AND ar.employee_id = ?"; |
|
| 140 |
push @values, conv_i($form->{employee_id});
|
|
| 141 |
} |
|
| 142 |
|
|
| 143 |
if ($form->{salesman_id}) {
|
|
| 144 |
$where .= " AND ar.salesman_id = ?"; |
|
| 145 |
push @values, conv_i($form->{salesman_id});
|
|
| 146 |
} |
|
| 103 | 147 |
if ($form->{project_id}) {
|
| 104 | 148 |
$where .= |
| 105 | 149 |
qq|AND ((ar.globalproject_id = ?) OR EXISTS | . |
| ... | ... | |
| 107 | 151 |
qq| WHERE i.project_id = ? AND i.trans_id = ar.id))|; |
| 108 | 152 |
push(@values, $form->{"project_id"}, $form->{"project_id"});
|
| 109 | 153 |
} |
| 154 |
if ($form->{business_id}) {
|
|
| 155 |
$where .= qq| AND ct.business_id = ? |; |
|
| 156 |
push(@values, $form->{"business_id"});
|
|
| 157 |
} |
|
| 158 |
|
|
| 159 |
my ($cvar_where_ct, @cvar_values_ct) = CVar->build_filter_query('module' => 'CT',
|
|
| 160 |
'trans_id_field' => 'ct.id', |
|
| 161 |
'filter' => $form); |
|
| 162 |
|
|
| 163 |
if ($cvar_where_ct) {
|
|
| 164 |
$where .= qq| AND ($cvar_where_ct)|; |
|
| 165 |
push @values, @cvar_values_ct; |
|
| 166 |
} |
|
| 167 |
|
|
| 110 | 168 |
|
| 111 |
$query .= " WHERE $where ORDER BY $sortorder"; |
|
| 169 |
my ($cvar_where_ic, @cvar_values_ic) = CVar->build_filter_query('module' => 'IC',
|
|
| 170 |
'trans_id_field' => 'p.id', |
|
| 171 |
'filter' => $form); |
|
| 172 |
|
|
| 173 |
if ($cvar_where_ic) {
|
|
| 174 |
$where .= qq| AND ($cvar_where_ic)|; |
|
| 175 |
push @values, @cvar_values_ic; |
|
| 176 |
} |
|
| 177 |
|
|
| 178 |
$query .= " WHERE $where ORDER BY $sortorder "; # LIMIT 5000"; |
|
| 112 | 179 |
|
| 113 | 180 |
my @result = selectall_hashref_query($form, $dbh, $query, @values); |
| 114 | 181 |
|
Auch abrufbar als: Unified diff
Verkaufsberichtsortierung um Land, Warengruppen, Kundentyp, Verkäufer und Monat erweitert
Hauptsortierung und Untersortierung sind jetzt nicht mehr auf Ware und Kunde
begrenzt, sondern man kann eine Kombinationen erstellen aus:
Es kann jetzt auch nach benutzerdefinierten Variablen gefiltert werden.
Der Verkaufsbericht spaltet sich mit seinen Optionen langsam in zwei
unterschiedliche Bereiche auf, den Artikelmodus, wo die einzelnen Zeilen aus
invoice angezeigt werden, und den Rechnungsmodus, wo nur die Zeilen der
Zwischensummen und Summen angezeigt werden, und die Detailinformationen
aus invoice nur stören.
Default ist Rechnungsmodus, den Artikelmodus kann man per Häkchen auswählen.
Je nachdem auf welcher Ebene man sich befindet machen dann auch Informationen
wie "Durchschnittsverkaufspreis" keinen Sinn mehr.
Bei Zuordnungen wo die Sortierung keinen Wert hat (z.B. Sortierung nach Land,
aber beim Kunden ist kein Land hinterlegt), erscheint als Überschrift "leer",
und alle leeren Werte werden als eine Gruppe zusammengefasst.