Right, I'm working on creating a page for our intranet and I've already had some mucho grande help to
another question which I thank yah for. Now the problem is, they like what I've done but want an 'all' option in my nice little drop down which will turn all the details for every staff member so I have:
CODE
DROP TABLE IF EXISTS Staff;
CREATE TEMPORARY TABLE Staff
SELECT DISTINCT Planner AS Display, Planner as StaffSearchID,
CONCAT('StaffSearchID=', Planner) AS CacheArgs
FROM DM_Planning;
INSERT INTO Staff VALUES('All','All','StaffSearchID=All');
SELECT * FROM Staff
ORDER BY Display
populates the drop down and:
CODE
DROP TABLE IF EXISTS FDPlan;
CREATE TEMPORARY TABLE FDPlan
SELECT
tourrefcode AS tourref, tourdeparturedate AS departuredate, toursequenceno AS sequenceno, itinerarylinefromairport AS airport, SUM(pax) AS booked
FROM
explorer.booking_record
INNER JOIN explorer.booking_linetype_record ON booking_linetype_record.bookingref = booking_record.bookingref
WHERE bookingstatus = 'F' AND itinerarylinetype = 'F' AND offset = 1 AND agentref <> 'LEAD'
AND tourdeparturedate >= CURDATE() AND DATE_ADD(CURDATE(), INTERVAL +56 DAY) >=tourdeparturedate
GROUP BY tourrefcode, tourdeparturedate, toursequenceno, itinerarylinefromairport;
SELECT FDPlan.tourref AS TourCode, FDPlan.DepartureDate, DM_Holidays.TourName,
flight_availability_record.fromairport, flight_availability_record.toairport, flight_availability_record.flightno,
flight_availability_record.depttime AS DepTime, flight_availability_record.arrivaltime,
FDPlan.booked, LE_Departures.LeaderName, LE_Departures.LeaderUID, DM_Planning.planner
FROM explorer.itinerary_linetype_record
INNER JOIN explorer.flight_availability_record ON (itinerary_linetype_record.itinerarylinefromairport = flight_availability_record.fromairport) AND (itinerary_linetype_record.itinerarylinetoairport = flight_availability_record.toairport) AND (itinerary_linetype_record.itinerarylinerelief = flight_availability_record.reliefcode)
INNER JOIN explorer.itinerary_date_record ON itinerary_date_record.tourref = itinerary_linetype_record.tourref AND itinerary_date_record.departuredate = itinerary_linetype_record.departuredate AND itinerary_date_record.sequenceno = itinerary_linetype_record.sequenceno
INNER JOIN FDPlan ON FDPlan.tourref = itinerary_linetype_record.tourref AND FDPlan.departuredate = itinerary_linetype_record.departuredate AND FDPlan.sequenceno = itinerary_linetype_record.sequenceno AND FDPlan.airport = flight_availability_record.fromairport
INNER JOIN explorer.airport ON flight_availability_record.fromairport = airport.code
INNER JOIN DM_Tours ON DM_Tours.TourCode = itinerary_date_record.tourref
INNER JOIN DM_Holidays ON DM_Tours.PrimaryTourCode = DM_Holidays.PrimaryTourCode
LEFT JOIN explorer.itinerary_closed_reasons ON (itinerary_closed_reasons.sequenceno = itinerary_date_record.sequenceno) AND (itinerary_closed_reasons.departuredate = itinerary_date_record.departuredate) AND (itinerary_closed_reasons.tourref = itinerary_date_record.tourref)
LEFT JOIN leaders.LE_Departures ON LE_Departures.DepartureDate = itinerary_date_record.departuredate AND LE_Departures.TourCode = itinerary_date_record.tourref
LEFT JOIN DM_Planning ON FDPlan.tourref = DM_Planning.Tourcode
WHERE DATE_ADD(itinerary_linetype_record.departuredate, INTERVAL itinerary_linetype_record.offset - 1 DAY) = flight_availability_record.flightdate AND (airport.country ="GB" OR airport.code = 'BHX') AND airport.code <> 'GCI'
AND (itinerary_closed_reasons.closedoutreason IS NULL OR itinerary_closed_reasons.closedoutreason = 0) AND Planner='{StaffSearchID}'
ORDER BY TourCode, FDPlan.DepartureDate;
populates the table (those really paying attention will notice that I've ended up not using the BETWEEN statement mentioned in the previous thread, because the damn thing wouldn't work
)
Now I'm fairly certain I need an IF statement somewhere in there to make it ignore the last item in the WHERE clause should StaffSearchID be 'all' but I'm damned if I can work out where to put it or how exactly the syntax should be layed out. Obviously if there is a more elegant option that'd be even better.
I am looking to learn here and working with this page has already taught me a lot, I feel like if I can just get this last bit sorted things will just kinda fall into place a bit.
P.S. should point out I have made several failed attempts at getting this to do what I want myself, whats posted above is the code in a working form.
15 Jul, 2008 - 05:43 AM
| 