Flight Schedule

phpVMS Legacy Add-Ons (v2/v5) Development Help
1

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

2

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

3

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

4

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

5

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?

6

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.

1 Like
7

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

Now, I understand. Nice!

8

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

[![post-612-034020800 1274995801_thumb.jpg](<fileStore.core_Attachment>/monthly_05_2010/post-612-034020800%201274995801_thumb.jpg)](<fileStore.core_Attachment>/monthly_05_2010/post-612-034020800%201274995801.jpg)

9

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.

10

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)

11

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

12

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…

13

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…

14

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.

15

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.

16

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

17

hey jeff, your download link is not working.

18

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

2 Likes
19

Jeff!!!

Welcome back!!!