Lieferfähigkeit prüfen

H

HolgerH

Benutzer
Beiträge
6
Hey alle zusammen,

ich hoffe, ihr könnt mir helfen, denn ich bin mit meinem Latein am Ende...

Für eine Verwaltung habe ich eine Seite mit der Übersicht von Bestellungen.
Soweit so gut, nun brauch ich noch "Filter". Ich möchte z.B. filtern können: Zeige mir alle lieferbaren Bestellungen an. In der normalen Übersicht erledigt das live meine PHP Klasse und gibt das grafisch aus.
Aber als richtiger Filter taugt das ja nichts, da dann meine Paginierungs Klasse nicht mehr hinhaut, wenn man was "unter den Tisch fallen lässt" :)

Also, muss es schon in der DB Abfrage gefiltert werden.
Um es knapp zu halten, nur das wichtigste:

Spalten der Tabelle "bestellungen":
b_id

Spalten der Tabelle "bestell_positionen":
bp_id
bp_artikel_id
bp_bestell_id

Spalten der Tabelle "lagerplaetze"
lp_id
lp_artikel_id
lp_anzahl

Meine gekürzte Abfrage um die Übersicht der Bestellungen auszugeben (Tabelle "lagerplaetze" wird noch nicht verwendet):

SELECT
bestellungen.b_id,
bestell_positionen.bp_id,
COUNT(bestell_positionen.bp_id) AS positions
FROM
bestellungen
JOIN bestell_positionen
ON bestellungen.b_id = bestell_positionen.bp_bestell_id
GROUP BY bestellungen.b_id

Das funktioniert soweit alles wie es soll...

Wie muss die Abfrage aussehen, wenn er mir nur Bestellungen zeigen soll, deren Bestellpositionen alle lieferbar sind?
Mein Problem ist dabei: In einer Bestellung kann es ja 10 oder mehr Bestellpositionen (Artikel) geben.
Davon muss dann ja noch jeder einzlene Bestellposition geprüft werden, ob auf den Lagerplätzen (eine Bestellposition kann auf mehreren Lagerplätzen vorhanden sein) genug da ist.
Ich stehe da echt auf dem Schlauch, denn Filtern würde ich gerne:
Zeige mir alle "lieferbaren" Bestellungen
Zeige mir alle "teilweise" lieferbaren Bestellungen
Zeige mir alle Bestellungen, die nicht lieferbar sind

Die "live" Ampel (durch PHP Klassen gebaut) funktioniert in der Ausgabe der Übersicht einwandfrei. Aber als SQL Statement schaffe ich das einfach nicht.

Oder ist das u.U. auch garnicht möglich was ich da vorhabe?

Bin für jede Anregung dankbar.

Vielen Dank und LG,
Holger

PS: sicherlich kann ich die Ausgabe durch meine PHP Klassen ganz einfach manipulieren und die "gefilterten" Bestellungen einfach überspringen. Aber dann stimmt meine Blätterfunktion nicht mehr. Ich bekomme 50 Ergebnisse pro Seite geliefert, 15 fliegen durch meine Filter Klassen raus bleiben nur 35 zum Anzeigen,
aber die Blätterfunktion sagt logisch 1 - 50 usw.
 
Werbung:
HolgerH schrieb:
SELECT
bestellungen.b_id,
bestell_positionen.bp_id,
COUNT(bestell_positionen.bp_id) AS positions
FROM
bestellungen
JOIN bestell_positionen
ON bestellungen.b_id = bestell_positionen.bp_bestell_id
GROUP BY bestellungen.b_id

Das funktioniert soweit alles wie es soll...
Zum Vergrößern anklicken....

Nein, das ist syntaktisch falsch. Alle Spalten des Resultates müssen entweder aggregiert oder gruppiert sein.
 
Ok, ich schaue auch schon nach alternativen Möglichkeiten. Eine weitere Spalte in die Tabelle "bestellungen" z.B. "live_status".
Und bei jeder Lagerbewegung die Lieferfähigkeit neuer Bestellungen durch meine PHP Klassen abgleichen zu lassen.
Das ist zwar mehr Aufwand, aber da bin ich einfach fitter, als in komplexen SQL Statements :-)

Mal noch aus Neugierde, wie müsste denn meine Abfrage syntaktisch korrekt aussehen?
Immerhin funktioniert sie ja einwandfrei und liefert mir auch die passende Menge an Bestellpositionen pro Bestellung.
 
HolgerH schrieb:
Mal noch aus Neugierde, wie müsste denn meine Abfrage syntaktisch korrekt aussehen?
Immerhin funktioniert sie ja einwandfrei und liefert mir auch die passende Menge an Bestellpositionen pro Bestellung.
Zum Vergrößern anklicken....

wie ich schrieb, alle Spalten aggregieren oder gruppieren, ALLE! In zukünftigen Versionen von MySQL wird es nicht mehr so gehen, derzeit ist MySQL aber blind und liefert Dir ein Ergebniss, welches zufällig richtig sein kann, aber nicht muß.
 
Mal als Fingerübung:

Code: 
test=*# \d artikel
  Table "public.artikel"
 Column |  Type  | Modifiers
--------+---------+-----------
 id  | integer | not null  
 name  | text  |   
Indexes:   
  "artikel_pkey" PRIMARY KEY, btree (id)
Referenced by:   
  TABLE "bestand" CONSTRAINT "bestand_artikel_id_fkey" FOREIGN KEY (artikel_id) REFERENCES artikel(id)
  TABLE "positionen" CONSTRAINT "positionen_artikel_fkey" FOREIGN KEY (artikel) REFERENCES artikel(id)

test=*# \d lager
  Table "public.lager"
 Column |  Type  | Modifiers
--------+---------+-----------
 id  | integer | not null  
 name  | text  |   
Indexes:   
  "lager_pkey" PRIMARY KEY, btree (id)
Referenced by:
  TABLE "bestand" CONSTRAINT "bestand_lager_id_fkey" FOREIGN KEY (lager_id) REFERENCES lager(id)

test=*# \d bestand
  Table "public.bestand"
  Column  |  Type  | Modifiers
------------+---------+-----------
 lager_id  | integer | not null
 artikel_id | integer | not null
 menge  | integer |
Indexes:
  "bestand_pkey" PRIMARY KEY, btree (lager_id, artikel_id)
Foreign-key constraints:
  "bestand_artikel_id_fkey" FOREIGN KEY (artikel_id) REFERENCES artikel(id)
  "bestand_lager_id_fkey" FOREIGN KEY (lager_id) REFERENCES lager(id)

test=*# \d bestellung
  Table "public.bestellung"
 Column |  Type  | Modifiers
--------+---------+-----------
 id  | integer | not null
 name  | text  |
Indexes:
  "bestellung_pkey" PRIMARY KEY, btree (id)
Referenced by:
  TABLE "positionen" CONSTRAINT "positionen_bestellung_fkey" FOREIGN KEY (bestellung) REFERENCES bestellung(id)

test=*# \d positionen
  Table "public.positionen"
  Column  |  Type  | Modifiers
------------+---------+-----------
 id  | integer | not null
 bestellung | integer |
 artikel  | integer |
 anzahl  | integer |
Indexes:
  "positionen_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
  "positionen_artikel_fkey" FOREIGN KEY (artikel) REFERENCES artikel(id)
  "positionen_bestellung_fkey" FOREIGN KEY (bestellung) REFERENCES bestellung(id)

test=*#

Und diesen Daten

Code: 
test=*# select * from artikel ;
 id | name   
----+------   
  1 | art1   
  2 | art2   
  3 | art3   
(3 rows)   

Time: 0,160 ms
test=*# select * from lager ;
 id |  name
----+--------
  1 | lager1
  2 | lager2
  3 | lager3
(3 rows)

Time: 0,158 ms
test=*# select * from bestand ;
 lager_id | artikel_id | menge
----------+------------+-------
  1 |  1 |  10
  1 |  3 |  5
  2 |  2 |  12
  2 |  3 |  8
  3 |  1 |  2
(5 rows)

Time: 0,167 ms
test=*# select * from bestellung ;
 id |  name
----+----------
  1 | bestell1
  2 | bestell2
  3 | bestell3
(3 rows)

Time: 0,157 ms
test=*# select * from positionen ;
 id | bestellung | artikel | anzahl
----+------------+---------+--------
  1 |  1 |  1 |  2
  2 |  1 |  2 |  2
  3 |  2 |  1 |  1
  4 |  2 |  2 |  100
  5 |  3 |  1 |  1
  6 |  3 |  3 |  1
(6 rows)

bekommst so eine Auflistung aller Bestellpositionen und ob lieferbar oder nicht:

Code: 
test=*# with artikelbestand as (select b.artikel_id as artikel, sum(menge) as anzahl from bestand b left join artikel a on b.artikel_id = a.id left joinlager l on b.lager_id=l.id group by artikel_id), bestellpositionen as (select b.id, p.artikel, sum(anzahl) as anzahl from bestellung b left join positionen p on b.id=p.bestellung group by b.id, p.artikel) select b.id, b.artikel, b.anzahl, a.anzahl, case when b.anzahl <= a.anzahl then 'lieferbar' else 'nicht im Bestand' end as status from bestellpositionen b left join artikelbestand a on b.artikel = a.artikel order by b.id;
 id | artikel | anzahl | anzahl |  status
----+---------+--------+--------+------------------
  1 |  1 |  2 |  12 | lieferbar
  1 |  2 |  2 |  12 | lieferbar
  2 |  2 |  100 |  12 | nicht im Bestand
  2 |  1 |  1 |  12 | lieferbar
  3 |  1 |  1 |  12 | lieferbar
  3 |  3 |  1 |  13 | lieferbar
(6 rows)

Dies liefert Dir alle Bestellungen, die lieferbar wären (count-Spalte = 0)

Code: 
test=*# select id, count(*) filter (where status = 'nicht im Bestand') from (with artikelbestand as (select b.artikel_id as artikel, sum(menge) as anzahl from bestand b left join artikel a on b.artikel_id = a.id left join lager l on b.lager_id=l.id group by artikel_id), bestellpositionen as (select b.id,p.artikel, sum(anzahl) as anzahl from bestellung b left join positionen p on b.id=p.bestellung group by b.id, p.artikel) select b.id, b.artikel, b.anzahl, a.anzahl, case when b.anzahl <= a.anzahl then 'lieferbar' else 'nicht im Bestand' end as status from bestellpositionen b left join artikelbestand a onb.artikel = a.artikel order by b.id) foo group by id;
 id | count
----+-------
  1 |  0
  2 |  1
  3 |  0

Also, Bestellung 1 und 3 wären lieferbar.


Man muß natürlich aufpassen: wenn man was ausliefert, verringert sich wieder der Bestand, da kann es also passieren, ich liefere Bestellung X aus und dann ist Bestellung Y plötzlich nicht mehr lieferbar.

Kann man alles natürlich a bissl lesbarer gestalten, das ist mir nur so grad auf die Tastatur gefallen...
 
Grundstäzlich gesagt: Die Abfragen sind alle nicht ganz korrekt...
Was ist wenn du erst in zwei Wochen liefern musst, derzeit noch nicht genug Bestand vorhanden ist aber nächste Woche nocheinmal genug für die Lieferung produziert wird? :)
 
@Distrilec: Ok, das ist kein Problem! Dafür soll ja die Prüfung der Lieferfähigkeit erfolgen.
Grundsätzlich kann nur das ausgeliefert werden, was auch vorhanden ist. Daher suchte ich ja nach einer Lösung, sich die lieferbaren Bestellungen zu filtern.
Gefiltert wird zusätzlich auch nach Bestelleingang. Also wer zuerst kommt, malt auch zuerst.

@akretschmer: Wow, wenn dir das "nur mal gerade so" auf die Tastatur gefallen ist, alle Achtung!
Da muss ich mich nun erst einmal "durchkämpfen" um das zu alles verinnerlichen... Hab jedenfalls tausend Dank für deine ganze Mühe!
Wenn ich das so sehe, hab ich da wohl noch echten Nachholbedarf, was SQL angeht :-)
 
HolgerH schrieb:
@Distrilec: Ok, das ist kein Problem! Dafür soll ja die Prüfung der Lieferfähigkeit erfolgen.
Grundsätzlich kann nur das ausgeliefert werden, was auch vorhanden ist. Daher suchte ich ja nach einer Lösung, sich die lieferbaren Bestellungen zu filtern.
Gefiltert wird zusätzlich auch nach Bestelleingang. Also wer zuerst kommt, malt auch zuerst.
Zum Vergrößern anklicken....
Aus welcher Sicht soll die Übersicht den kommen? Aus Versand-Sicht ? Denn da gibt es dann ganz andere Probleme als Beispielsweise in der Auftragsplanung... :)
Als Anreiz: Zwei Bestellungen für den gleichen Artikel, am gleichen Tag... Du hast aber nur Bestand für eine Bestellung... Was tun?
 
HolgerH schrieb:
Da muss ich mich nun erst einmal "durchkämpfen" um das zu alles verinnerlichen... Hab jedenfalls tausend Dank für deine ganze Mühe!
Wenn ich das so sehe, hab ich da wohl noch echten Nachholbedarf, was SQL angeht
Zum Vergrößern anklicken....

Die Syntax ist einklich SQL-Standard (mit WITH und so), aber MySQL ist zu weit weg von diesen Standards um das zu verstehen. Also mit diesem Spielzeug wird das so erst einmal nicht gehen.
 
Sicht der AV ist das kein Problem, denn die können einzlnen Bestellungen eine Prio mitgeben, oder die ganze Bestellung in eine Art Warteschleife schieben.
 
Hab in diesem Forum das Problem, das es meine Antworten nicht speichern mag. :-(
Egal, welcher Browser. Der Balken oben rechts "rödelt" wie blöde, aber nichts passiert...
 
Werbung:

Ähnliche Themen

ukulele
Performance-Totalausfall durch Filterkriterium
Antworten
10
Aufrufe
811
ukulele
ukulele
Zurück
Oben