SQL help

[ZekeElias]

Hey everyone,

I added fields on my pilot brief to have information such as arrival and departure gate listed, I changed the import.tpl but have no luck in getting the SQL file to take the gate number and post it back on the brief. I made it work once as then I imported the schedule again and it all got deleted. I'm going crazy trying to fix it. Can anyone please help me?

Zeke

[Jeff]

Your Schedule CSV

1) Open your .csv using Microsoft Excel (I find this better than notepad).

2) Create 2 fields at the top called depgate and arrgate

3) Type your gate numbers in their respected boxes for each flight.

4) Save your .csv

phpMyAdmin

1) Open phpMyAdmin from your cPanel

2) Choose your database name (from the left)

3) From the scroll-down, click "phpvms_schedules"

4) At the top, click "Structure"

5) Just below the table, look for this Add 1 Column. click the radio button for "After" then choose "Enabled" from the drop-down.

6) Type the following in their correct boxes:

Column depgate

Type TEXT

Default None

Collation latin1_swedish_ci

7) Click SAVE

8) Repeat steps 5-7 to add the arrgate to the structure (you want to type arrgate instead of depgate on this one, and instead of entering it after "Enabled", you want to add it after "depgate").

File Manager (Editing the schedules module)

1) Go to the following directory: /public_html/admin/modules/Import

2) Open the Import.php file (please make a back-up of this before editing)

3) Look for this code:

	public function processexport()
{
	$export='';
	$all_schedules = SchedulesData::GetSchedules('', false);

	if(!$all_schedules)
	{
		echo 'No schedules found!';
		return;
	}

	header('Content-Type: text/plain');
	header('Content-Disposition: attachment; filename="schedules.csv"');

	$fp = fopen('php://output', 'w');

	$line=file_get_contents(SITE_ROOT.'/admin/lib/template.csv');
	fputcsv($fp, explode(',', $line));

	foreach($all_schedules as $s)
	{
		$line ="{$s->code},{$s->flightnum},{$s->depicao},{$s->arricao},"
				."{$s->route},{$s->registration},{$s->flightlevel},{$s->distance},"
				."{$s->deptime}, {$s->arrtime}, {$s->flighttime}, {$s->notes}, "
				."{$s->price}, {$s->flighttype}, {$s->daysofweek}, {$s->enabled}";

		fputcsv($fp, explode(',', $line));
	}

	fclose($fp);
}

4) Change it to this:

	public function processexport()
{
	$export='';
	$all_schedules = SchedulesData::GetSchedules('', false);

	if(!$all_schedules)
	{
		echo 'No schedules found!';
		return;
	}

	header('Content-Type: text/plain');
	header('Content-Disposition: attachment; filename="schedules.csv"');

	$fp = fopen('php://output', 'w');

	$line=file_get_contents(SITE_ROOT.'/admin/lib/template.csv');
	fputcsv($fp, explode(',', $line));

	foreach($all_schedules as $s)
	{
		$line ="{$s->code},{$s->flightnum},{$s->depicao},{$s->arricao},"
				."{$s->route},{$s->registration},{$s->flightlevel},{$s->distance},"
				."{$s->deptime}, {$s->arrtime}, {$s->flighttime}, {$s->notes}, "
				."{$s->price}, {$s->flighttype}, {$s->daysofweek}, {$s->enabled}, "
				."{$s->depgate}, {$s->arrgate}, ";

		fputcsv($fp, explode(',', $line));
	}

	fclose($fp);
}

5) Scroll down, and look for this:

echo '<div style="overflow: auto; height: 400px; border: 1px solid #666; margin-bottom: 20px; padding: 5px; padding-top: 0px; padding-bottom: 20px;">';

	while($fields = fgetcsv($fp, 1000, ','))
	{
		// Skip the first line
		if($skip == true)
		{
			$skip = false;
			continue;
		}

		// list fields:
		$code = $fields[0];
		$flightnum = $fields[1];
		$depicao = $fields[2];
		$arricao = $fields[3];
		$route = $fields[4];
		$aircraft = $fields[5];
		$flightlevel = $fields[6];
		$distance = $fields[7];
		$deptime = $fields[8];
		$arrtime = $fields[9];
		$flighttime = $fields[10];
		$notes = $fields[11];
		$price = $fields[12];
		$flighttype = $fields[13];
		$daysofweek = $fields[14];
		$enabled = $fields[15];

		if($code == '')
		{
			continue;
		}

6) Change it to this:

echo '<div style="overflow: auto; height: 400px; border: 1px solid #666; margin-bottom: 20px; padding: 5px; padding-top: 0px; padding-bottom: 20px;">';

	while($fields = fgetcsv($fp, 1000, ','))
	{
		// Skip the first line
		if($skip == true)
		{
			$skip = false;
			continue;
		}

		// list fields:
		$code = $fields[0];
		$flightnum = $fields[1];
		$depicao = $fields[2];
		$arricao = $fields[3];
		$route = $fields[4];
		$aircraft = $fields[5];
		$flightlevel = $fields[6];
		$distance = $fields[7];
		$deptime = $fields[8];
		$arrtime = $fields[9];
		$flighttime = $fields[10];
		$notes = $fields[11];
		$price = $fields[12];
		$flighttype = $fields[13];
		$daysofweek = $fields[14];
		$enabled = $fields[15];
		$depgate = $fields[16];
		$arrgate = $fields[17];

		if($code == '')
		{
			continue;
		}

7) Scroll down a little more, and find this:

			# This is our 'struct' we're passing into the schedule function
		#	to add or edit it

		$data = array(	'code'=>$code,
						'flightnum'=>$flightnum,
						'depicao'=>$depicao,
						'arricao'=>$arricao,
						'route'=>$route,
						'aircraft'=>$ac,
						'flightlevel'=>$flightlevel,
						'distance'=>$distance,
						'deptime'=>$deptime,
						'arrtime'=>$arrtime,
						'flighttime'=>$flighttime,
						'daysofweek'=>$daysofweek,
						'notes'=>$notes,
						'enabled'=>$enabled,
						'price'=>$price,
						'flighttype'=>$flighttype);

8) Change it to this:

			# This is our 'struct' we're passing into the schedule function
		#	to add or edit it

		$data = array(	'code'=>$code,
						'flightnum'=>$flightnum,
						'depicao'=>$depicao,
						'arricao'=>$arricao,
						'route'=>$route,
						'aircraft'=>$ac,
						'flightlevel'=>$flightlevel,
						'distance'=>$distance,
						'deptime'=>$deptime,
						'arrtime'=>$arrtime,
						'flighttime'=>$flighttime,
						'daysofweek'=>$daysofweek,
						'notes'=>$notes,
						'enabled'=>$enabled,
						'price'=>$price,
						'flighttype'=>$flighttype,
						'depgate'=>$depgate,
						'arrgate'=>$arrgate);

9) Save your work.

Testing out csv

1) Import your schedules to your database using your Admin Center (I have to upload it twice for some reason in order for them to show up for me).

2) Let me know if you run into any problems.

[Jeff]

Instructions added. (Good luck)

[ZekeElias]

Jeff,

Thanks for your help! I followed all the steps but no luck. I had tried that before and it worked once and now it doesn't. Maybe a limit changed? There is only one flight that uploads and the gate is E1, that's it.

[Jeff]

Are you using the correct code to call for the gate numbers?

schedule_results.tpl

<?php echo $route->depgate ?>
<?php echo $route->arrgate ?>

schedule_details.tpl

<?php echo $schedule->depgate?>
<?php echo $schedule->arrgate?>

[ZekeElias]

Jeff, I made it work. I was deleting the old schedule, and it wasn't uploading all the rest, once I unclicked the delete the old schedule, it worked!. Thanks!