Hi again,
the setting
The problem above is embedded in this setting:
I have over sea ISO containers that are placed in a certain location. This location is divided in coordinates (row -> ID_posX, depth -> ID_posY, level -> ID_posZ). As there might be more than one area this is also attached as Lagerort. All this data is concated to "place": Lagerort*Reihe-Tiefe/Ebene so for example 8*1-2/3. This would be a place in area 8, row 1, depth (from the forlift drivers point of view) 2 and level 3. Below that are already two other containers (of course, as no container can hover in midair).
Now the forklift driver takes a container and takes it to another location. At this point my application is used to change that location. The driver therefore selects a new location from the list.
This list should of course be ordered and show (old solution): only places that are a) not empty or should always be show (ImmerZeigen=Ja)
and b) not in midair, but the lowest available.
The new version (see below) is: only places that are a) have less containers than allowed (BelegtBei) in this place
and b) not in midair, but the lowest available.
old version solution
pbötcher wrote me a PM after the problem was not solved here and suggested some code, that works (thank you very much here again):
First of all all custom SQL has to be a one line string without line breaks! This is very important, otherwise the regex will not find your ORDER.
This code sorts correctly and loads the rest of the list, when I scroll down, works with the ajax search and shows only the empty locations.
This is his code (I am using an image here, so you see, there is not much difference to the new code below, I also use SQL to make helping easier by copy&paste)
- alt.png (24.64 KiB) Viewed 4659 times
again as sql:
Code: Select all
SELECT *
FROM (SELECT e1.ID_LocationNum,
e1.Location_Auto,
e1.ID_Lagerorte,
e1.ID_posX,
e1.ID_posY,
Min(e1.ID_posZ) AS id_posZ,
e1.Lagerort_Kurz
FROM (SELECT e.Location_Auto,
e.ID_Lagerorte,
e.ID_posX,
e.ID_posY,
e.ID_posZ,
e.ID_LocationNum,
e.ImmerZeigen,
ecomo_Lagerorte.Lagerort_Kurz
FROM ecomo_Location e
LEFT JOIN ecomo_Lagerorte
ON ecomo_Lagerorte.ID_LagerorteNum = e.ID_Lagerorte
HAVING ( e.ID_LocationNum NOT IN (SELECT ecomo_Container.ID_Location
FROM ecomo_Container) )
OR ( e.ImmerZeigen = 'Ja' )) e1
GROUP BY e1.ID_posX,
e1.ID_posY,
e1.Lagerort_Kurz) ecomo_Location
ORDER BY ecomo_Location.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY DESC,
ecomo_Location.id_posZ DESC
the new (not working) solution
Now I changed my database to make locations not show if they are empty of ImmerZeigen=ja as above, but the user would be able to set a certain number that defines, when a location is full BelegtBei. A location will be shown in the dropdown list, if is has less entries in the table than the BelegtBei value is. This results in this SQL which works fine in phpmyadmin, returning the correct records in the correct order:
- neu.png (44.41 KiB) Viewed 4659 times
again as sql:
Code: Select all
SELECT *
FROM (SELECT e1.ID_LocationNum,
e1.Location_Auto,
e1.ID_Lagerorte,
e1.ID_posX,
e1.ID_posY,
Min(e1.ID_posZ) AS id_posZ,
e1.Lagerort_Kurz
FROM (SELECT e.Location_Auto,
e.ID_Lagerorte,
e.ID_posX,
e.ID_posY,
e.ID_posZ,
e.ID_LocationNum,
e.ImmerZeigen,
ecomo_Lagerorte.Lagerort_Kurz
FROM ecomo_Location e
LEFT JOIN ecomo_Lagerorte
ON ecomo_Lagerorte.ID_LagerorteNum = e.ID_Lagerorte
HAVING ( e.ID_LocationNum IN (SELECT aLocation.ID_LocationNum
FROM ecomo_Location aLocation
INNER JOIN (SELECT e.ID_LocationNum,
Count(ecomo_Container.ID_ContainerNum) AS c
FROM ecomo_Location e
LEFT JOIN ecomo_Container
ON ecomo_Container.ID_Location = e.ID_LocationNum
GROUP BY e.ID_LocationNum) aZahlContVorhanden
ON aZahlContVorhanden.ID_LocationNum = aLocation.ID_LocationNum
WHERE aZahlContVorhanden.c < aLocation.BelegtBei) )) e1
GROUP BY e1.ID_posX,
e1.ID_posY,
e1.Lagerort_Kurz) ecomo_Location
ORDER BY ecomo_Location.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY DESC,
ecomo_Location.id_posZ DESC
the problem with the new SQL in the generated AppGini code
As you see, I marked two sections with a border in the images. This is the only difference and the result of the complete SQL is exactly the same.
But with the
new SQL the generated AppGini files do not work anymore!
The
Order is still correct and loading more from the list works too, but AJAX search does not work - the spinner keeps spinning but does not suggest anything as can bee seen in the picture below.
- ec64.png (6.82 KiB) Viewed 4659 times
As the SQL does work in Mysql I am requesting your help.
I can not see any reason, why this happens but I think something is strange with the AJAX search.
Olaf