Flight Schedule

Jeff · May 26, 2010

Okay guys, and gals,

I have been up, down, inside and out of this forum since I purchased PHPVMS from www.fivedev.net, and I'd have to say I have yet to see a post anywhere that asks or shows how people go about creating schedules to insert into the MyAdmin panel of php/VMS. I'd have to say that when we were using php/VMS on a free server, it was very hard to come up with an easier way to create a schedule. Included in this post is a link to the file you need to make this process alot easier for you to create hundreds or thousands of schedules at a time before you convert it over to .csv

How it works:

You need Microsoft Excel 2003 or greater to use this (as I'm sure most of you already have)

Open the file and you will see that I gave you an example flight to see how info gets inserted.

For the most part, the only places you need to type something in is the GREEN AREAS. the gray areas will automatically fill in as you go.

The auto info reads from the two other pages "IACO DATA" and "AIRPORTS" (do not edit those pages or it can mess up airport data). The data stored covers nearly every single airport in the world.

The information it asks for is:

CODE: Airline code (either IATA or IACO)
Flight Number: Flight number (pretty explanatory) one (1) to four (4) digit number
DepIACO: Departure Airport ICAO code for the departing Airport
ArrIACO: Arrival Airport ICAO code for the arrival Airport
DepTime: Departure time for flight departure (24 hour format)
ArrTime: Arrival time for flight arrival (24 hour format)
FlightTime: this will automatically be filled out for you, but you will need to tweak it out if you cross time zones or if the arrival time goes past midnight.
Aircraft: Number of the aircraft you are using for the flight (N-548DL for those of you using different aircraft for certain routes)
OperatedBy: you can change the name of that header to whatever suits you, it is actually the NOTES part found in the .csv file you upload in the database.
Price: The price you want to charge for the flight, I just normally use $1.00 per mile flown (you can charge what you want.
Type: you can change "p" Passenger to "c" Cargo or "h" Charter
Daysofweek: The frequency of your flight (0= Sunday, 1= Monday, 2= Tuesday, 3= Wednesday, 4= Thursday, 5= Friday, 6= Saturday)

once you get past 100, you will need to copy/paste more into the document so you can continue, I just added 100 to save file size.

Once you are ready to upload it to your site, copy the needed ares and paste it in the .csv file for uploading.

Any questions, I can help you on just ask.

Hope you like it.

www.oneworldvirtual.com/files/flight_schedule.zip

TennShadow · May 26, 2010

It is good stuff. It even works with Open Office. Could you attach the zip file to this thread?

Jeff · May 26, 2010

It is good stuff. It even works with Open Office. Could you attach the zip file to this thread?

the file is larger than maximum global quota, but the direct link is: http://www.oneworldvirtual.com/files/flight_schedule.zip

Jeff · May 27, 2010

I'm here for another few hours, so if you need help, I should be here.

CrashGordon · May 27, 2010

Maybe I'm dumb, but how does this differ from simply creating a csv file with the following data?

code flightnum depicao arricao route aircraft flightlevel distance deptime arrtime flighttime notes price flighttype daysofweek enabled

I don't understand why creating a schedule in one fale and then pasting the data into another is better than simply entering the data in the csv file you intend to import. What am I missing?

Jeff · May 27, 2010

Maybe I'm dumb, but how does this differ from simply creating a csv file with the following data?

code flightnum depicao arricao route aircraft flightlevel distance deptime arrtime flighttime notes price flighttype daysofweek enabled

I don't understand why creating a schedule in one fale and then pasting the data into another is better than simply entering the data in the csv file you intend to import. What am I missing?

Most of the information will be added for you as you start typing in the information, as well as the coordinates and airport altitude so you don't need to search the web for that information. For me it is easier to use that instead of trying to calculate the correct time, most airlines websites won't tell you the Total Time of the flight, this file helps. Give it a try, if you don't like it, you can always delete it, and still say you tried.

CrashGordon · May 27, 2010

Ahhhh! I see, said the blind man, as he picked up his hammer and saw.

Now, I understand. Nice!

MANFahrer · May 27, 2010

I'm too stupid for me to attach a file with the whole works.

No matter how it forms part of Excel is always the same error see picture

TennShadow · May 28, 2010

I'm too stupid for me to attach a file with the whole works.

No matter how it forms part of Excel is always the same error see picture

You can't use that as the import because it has too much information. You only use it to get some of the fields automatically filled out for you. Once you have gotten all the schedules you need you have to copy and paste the columns into the .csv file you downloaded from your schedules export page.

Jeff · May 28, 2010

I'm too stupid for me to attach a file with the whole works.

No matter how it forms part of Excel is always the same error see picture

When transferring the flight schedule to the .csv file, you only need to copy/paste the green areas (and also the TotalTime and Flight Type)

Wingthor · January 24, 2011

the file is larger than maximum global quota, but the direct link is: http://www.oneworldvirtual.com/files/flight_schedule.zip

Link is dead. Anyone know where to get it?

Regards

Wingthor · February 10, 2011

Well since no one came up with a link, I had to find my own solution which I think i so briliant that I will share it with you. Adding schedules is a hack, adding airports is not. An obvousily you don't want a schedule for an airport that is not in your DB. Most of you will have accsess to send queries directly to the DB by, webmin, phpmyadmin heidi sql or even odbc.

So why don't add the greatcircle formula to the phpvms DB, and get the airports data straigt from the DB.

SET GLOBAL log_bin_trust_function_creators=TRUE; 

DROP FUNCTION IF EXISTS GreaterCircleNm;
DELIMITER go
CREATE FUNCTION GreaterCircleNm( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float
BEGIN
 DECLARE pi, q1, dist FLOAT;
 SET pi = PI();
 SET lat1 = lat1 * pi / 180;
 SET lon1 = lon1 * pi / 180;
 SET lat2 = lat2 * pi / 180;
 SET lon2 = lon2 * pi / 180;
SET q1 = ACOS(sin(lat1)*sin(lat2)+COS(lat1)*COS(lat2)*COS(lon1-lon2));
SET dist = q1*180*60/pi;
RETURN dist;
END;
go
DELIMITER ;

Make sure it goes in your phpvms DB.

So I lets say I wan't generate routes to and from all the airports in my DB...

 SELECT
   NULL,
   depart.icao,
   arrive.icao,
   ROUND(GreaterCircDistNm(depart.lat,depart.lng,arrive.lat,arrive.lng),2) as Nm
FROM airports AS depart
INNER JOIN airports AS arrive ON depart.icao <> arrive.icao

There you go now you have a node between every node in you selection which easely can be exported as CVS.

But just to show the briliance.

I have a aircraft which has a limit on 1000NM, I whish the plane to be station at London HTRW. 'EGLL'....

Well into the QRY it goes...

SELECT
   NULL,
   depart.icao,
   arrive.icao,
   ROUND(GreaterCircDistNm(depart.lat,depart.lng,arrive.lat,arrive.lng),2) as Nm
FROM airports AS depart
INNER JOIN airports AS arrive ON depart.icao <> arrive.icao
where depart.icao = 'EGLL'
HAVING Nm <= 1000;

And there you have a circle. Get it into your sheet template, and sky is the limit.....

Regards...

February 10, 2011

Very Cool Wing. How do you create the function? Run in the SQL window in phpAdmin?

Can you give detailed info?

Wingthor · February 10, 2011

Very Cool Wing. How do you create the function? Run in the SQL window in phpAdmin?

Can you give detailed info?

Yes, run as SQL... And add fucntion before QRY's. As you can see the QRY use calls to the function.

You need directly access to the DB as this is pure SQL. So if you have phpMyAdmin paste in as SQL and run it. I don't have phpMySQL, right now. So my instruction can only be bried. But as I remember there was an option to run SQL on a DB when you have selected it. But im sure there is experienced phpMyAdmins users on the forum which can give more detailed instruction on how to run SQL from phpMyAdmin.

Check this http://community.mybb.com/thread-4720.html I can't verify of mentions reasons...

February 10, 2011

Thanks I got it in but I had to remove the first line.

SET GLOBAL log_bin_trust_function_creators=TRUE;

February 10, 2011

Unfortunatley I can not run the new function. I am getting a return of

xecute command denied to user 'xxxxxx_xxxx'@'localhost' for routine 'xxxxxx_xxxxx.GreaterCircleNm'

Even though the user has all privileges available.

Also there is a miss-type in your query. You are calling the function as "GreaterCircDistNm" but in the create you call it "GreaterCircleNm". Easy fix to rename but just thought you would like to know.

Wingthor · February 10, 2011

Unfortunatley I can not run the new function. I am getting a return of

Even though the user has all privileges available.

Also there is a miss-type in your query. You are calling the function as "GreaterCircDistNm" but in the create you call it "GreaterCircleNm". Easy fix to rename but just thought you would like to know.

Which version of MySql do you have. http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html

I works fine on my MySql 5.0.75.

Wingthor · February 10, 2011

Which version of MySql do you have. http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html

I works fine on my MySql 5.0.75.

And check that you have 'EXECUTE' rights from localhost on the DB.

February 10, 2011

MySQL ver 5.0.91

But this is on a hosted site. Not sure if they actually grant all privileges when they say ALL privileges.

February 10, 2011

UPDATE - the query works through the SQL panel but not through the site. It is only when attempting to run the query through the actual code on the site that it fails.

February 10, 2011

I may have found the issue (i have not tried yet but will this evening)

On my test site I can not grant myself "EXECUTE" privileges. On the actual site I can. So it may be tied to this privilege. I will give it a go tonight.

Wingthor · February 10, 2011

Ok, but also remember that MySQL you can download and run from your PC. Just dump your phpvms DB or the airports tbl, with the backup function in phpMyAdmin and get into your local Mysql where you have all grants. In case it will not work. I am using heidiSQL to manipulating the DB.

http://www.heidisql.com/download.php

February 10, 2011

I want to use this function real time. I am actually going to use this for a different reason than what you wrote it for. It actually has no use for me to create schedules since we use RW schedules. But it will be useful for something else I am planning.

Toyuko · March 13, 2011

hey jeff, your download link is not working.

Jeff · March 19, 2011

hey jeff, your download link is not working.

Sorry, I haven't been on in almost a year. Here is a permanent link to the file.

Download File

Kyle · March 20, 2011

Jeff!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Welcome back!!!

Sign In

Flight Schedule

18 members have voted

1. How many use this for their schedueling

Recommended Posts

Jeff

TennShadow

Jeff

Jeff

CrashGordon

Jeff

CrashGordon

MANFahrer

TennShadow

Jeff

Wingthor

Wingthor

Guest lorathon

Wingthor

Guest lorathon

Guest lorathon

Wingthor

Wingthor

Guest lorathon

Guest lorathon

Guest lorathon

Wingthor

Guest lorathon

Toyuko

Jeff

Kyle

Join the conversation