bonipak
asked on
Shipping door and associated orders webpage
I'm a sysadmin/beginner developer and am filling in a little bit to give our development team some help. My goal is to create a simple webpage which refreshes data periodically from an sql query and displays a kind of realtime status of shipping door order assignments and when orders are staged to be loaded.
SQL:
select distinct WMSLocation.WMSLocationId as Door, salesline.salesid
from WMSLocation
inner join WMPallet on WMPallet.recidLocation = WMSLocation.RecId and WMPallet.dataareaid = WMSLocation.Dataareaid
inner join WMCartonGroup on WMCartonGroup.idPallet = WMPallet.Id and WMCartonGroup.dataareaid = WMPallet.Dataareaid
inner join salesline on salesline.recid = wmcartongroup.refrecsalesl ine and salesline.dataareaid = wmcartongroup.dataareaid
where WMSLocation.AisleId = 'Door' and WMSLocation.Inventlocation Id = 'CITY' and WMSLocation.dataareaid = 'company';
Example Results:
+-------------+----------- ---------- --
| Door-13 | 433132
+-------------+----------- ---------- --
| Door-14 | 158795
+-------------+----------- ---------- --
| Door-C | 125432
+-------------+----------- ---------- --
| Door-14 | 125845
+-------------+----------- ---------- --
Results returned are assumed staged since it is required in the users workflow to change the order status. Max number of orders to a door is 12 (rare) but I guess it happens. Order numbers are either 6 digits or an "EDI-000000" number sequence. I'm going to show this on a large wide screen display. I haven't settled on a resolution and table size combination for maximium visiblilty, but I can work that out if we get a functional page.
The results are displayed in a simple fixed height, variable width HTML table. Doors with no orders are shown:
+-----------+------------- ---------- ---------- ---------- ---------- ---------
| Doors | Orders
+-----------+------------- ---------- ---------- ---------- ---------- ---------
| 14 | 125845, 158795
+-----------+------------- ---------- ---------- ---------- ---------- ---------
| 13 | 433132
+-----------+------------- ---------- ---------- ---------- ---------- ---------
| 12 |
+-----------+------------- ---------- ---------- ---------- ---------- ---------
| 11 |
+-----------+------------- ---------- ---------- ---------- ---------- ---------
..down to door 1 then A, B, C, D (A-D, 1-14)
When a door has an order the rows background is changed to green and all orders for that door are listed comma seporated. When the door is no longer in the data the order is cleared from the table and the background returns to white.
The attached page is where I am currently. It is a hodgepodge of attempts to do the things I want then bring it all together.
I have a feeling I'm over thinking this one and there is a easy method to acomplish this using tools which I'm not yet aware.
shipping-dashboard.asp.txt
SQL:
select distinct WMSLocation.WMSLocationId as Door, salesline.salesid
from WMSLocation
inner join WMPallet on WMPallet.recidLocation = WMSLocation.RecId and WMPallet.dataareaid = WMSLocation.Dataareaid
inner join WMCartonGroup on WMCartonGroup.idPallet = WMPallet.Id and WMCartonGroup.dataareaid = WMPallet.Dataareaid
inner join salesline on salesline.recid = wmcartongroup.refrecsalesl
where WMSLocation.AisleId = 'Door' and WMSLocation.Inventlocation
Example Results:
+-------------+-----------
| Door-13 | 433132
+-------------+-----------
| Door-14 | 158795
+-------------+-----------
| Door-C | 125432
+-------------+-----------
| Door-14 | 125845
+-------------+-----------
Results returned are assumed staged since it is required in the users workflow to change the order status. Max number of orders to a door is 12 (rare) but I guess it happens. Order numbers are either 6 digits or an "EDI-000000" number sequence. I'm going to show this on a large wide screen display. I haven't settled on a resolution and table size combination for maximium visiblilty, but I can work that out if we get a functional page.
The results are displayed in a simple fixed height, variable width HTML table. Doors with no orders are shown:
+-----------+-------------
| Doors | Orders
+-----------+-------------
| 14 | 125845, 158795
+-----------+-------------
| 13 | 433132
+-----------+-------------
| 12 |
+-----------+-------------
| 11 |
+-----------+-------------
..down to door 1 then A, B, C, D (A-D, 1-14)
When a door has an order the rows background is changed to green and all orders for that door are listed comma seporated. When the door is no longer in the data the order is cleared from the table and the background returns to white.
The attached page is where I am currently. It is a hodgepodge of attempts to do the things I want then bring it all together.
I have a feeling I'm over thinking this one and there is a easy method to acomplish this using tools which I'm not yet aware.
shipping-dashboard.asp.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
just taking some notes, im away from my computer on another project:
dim dock(0 to 17, 0 to 24) As String
fill dock
dock(0,1)=Door-A,$ORDER1
dock(0,2)=Door-A,$ORDER2
dock(5,3)=Door-01,$ORDER3
if dock has no orders, create a normal table row with dock name in column 1
else green background, create table row with dock name in column 1 and loop through and response.write orders for column 2
?
dim dock(0 to 17, 0 to 24) As String
fill dock
dock(0,1)=Door-A,$ORDER1
dock(0,2)=Door-A,$ORDER2
dock(5,3)=Door-01,$ORDER3
if dock has no orders, create a normal table row with dock name in column 1
else green background, create table row with dock name in column 1 and loop through and response.write orders for column 2
?
ASKER
make a new query?!
ASKER
I did make a new query with help from our sql savvy developer.
I now get all orders for the doors and feel it serves it's purpose. Thanks for the information on transposing my query. You deserve the points for educating me.
select distinct
WMSLocation.WMSLocationId as Door
, (
select salesid + ' '
from (
select distinct
salesid
from SALESLINE
where salesline.RECID = wmcartongroup.refrecsalesline
and salesline.DATAAREAID = wmcartongroup.DATAAREAID
) intern
for xml path('')
) as orders
from WMSLocation
inner join WMPallet
on WMPallet.recidLocation = WMSLocation.RecId
and WMPallet.dataareaid = WMSLocation.Dataareaid
inner join WMCartonGroup
on WMCartonGroup.idPallet = WMPallet.Id
and WMCartonGroup.dataareaid = WMPallet.Dataareaid
inner join salesline
on salesline.recid = wmcartongroup.refrecsalesline
and salesline.dataareaid = wmcartongroup.dataareaid
where WMSLocation.AisleId = 'Door'
and WMSLocation.InventlocationId = 'city'
and WMSLocation.dataareaid = 'company'
order by 'Door'
I now get all orders for the doors and feel it serves it's purpose. Thanks for the information on transposing my query. You deserve the points for educating me.
Glad I could help you figuring it out ...
ASKER
I cleaned up my code and have a semi funtional page. I do not have all the doors in my html table, which is ok for now, and I'm also not able to group my order numbers into a single door row yet. That may be where your suggestion may comes into play.
I am posting my progress so you experts don't spend time on something I've already done. I'm working on figuring out the correct combination of HTML, SQL and VB logic at this point.
door-status.asp.txt