Jeff Posted May 26, 2010 Report Share Posted 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 2 Quote Link to comment Share on other sites More sharing options...
TennShadow Posted May 26, 2010 Report Share Posted May 26, 2010 It is good stuff. It even works with Open Office. Could you attach the zip file to this thread? Quote Link to comment Share on other sites More sharing options...
Jeff Posted May 26, 2010 Author Report Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Jeff Posted May 27, 2010 Author Report Share Posted May 27, 2010 I'm here for another few hours, so if you need help, I should be here. Quote Link to comment Share on other sites More sharing options...
CrashGordon Posted May 27, 2010 Report Share Posted 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? Quote Link to comment Share on other sites More sharing options...
Jeff Posted May 27, 2010 Author Report Share Posted 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. 1 Quote Link to comment Share on other sites More sharing options...
CrashGordon Posted May 27, 2010 Report Share Posted May 27, 2010 Ahhhh! I see, said the blind man, as he picked up his hammer and saw. Now, I understand. Nice! Quote Link to comment Share on other sites More sharing options...
MANFahrer Posted May 27, 2010 Report Share Posted 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 Quote Link to comment Share on other sites More sharing options...
TennShadow Posted May 28, 2010 Report Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Jeff Posted May 28, 2010 Author Report Share Posted 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) Quote Link to comment Share on other sites More sharing options...
Wingthor Posted January 24, 2011 Report Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Wingthor Posted February 10, 2011 Report Share Posted 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... Quote Link to comment Share on other sites More sharing options...
Guest lorathon Posted February 10, 2011 Report Share Posted February 10, 2011 Very Cool Wing. How do you create the function? Run in the SQL window in phpAdmin? Can you give detailed info? Quote Link to comment Share on other sites More sharing options...
Wingthor Posted February 10, 2011 Report Share Posted 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... Quote Link to comment Share on other sites More sharing options...
Guest lorathon Posted February 10, 2011 Report Share Posted February 10, 2011 Thanks I got it in but I had to remove the first line. SET GLOBAL log_bin_trust_function_creators=TRUE; Quote Link to comment Share on other sites More sharing options...
Guest lorathon Posted February 10, 2011 Report Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Wingthor Posted February 10, 2011 Report Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Wingthor Posted February 10, 2011 Report Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Guest lorathon Posted February 10, 2011 Report Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Guest lorathon Posted February 10, 2011 Report Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Guest lorathon Posted February 10, 2011 Report Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Wingthor Posted February 10, 2011 Report Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Guest lorathon Posted February 10, 2011 Report Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Toyuko Posted March 13, 2011 Report Share Posted March 13, 2011 hey jeff, your download link is not working. Quote Link to comment Share on other sites More sharing options...
Jeff Posted March 19, 2011 Author Report Share Posted 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 2 Quote Link to comment Share on other sites More sharing options...
Moderators Kyle Posted March 20, 2011 Moderators Report Share Posted March 20, 2011 Jeff!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Welcome back!!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.