Schedule .csv question

[Jeff]

If I were to add another field in the .csv to import to my site, what are the steps to have it recognize the new field when I try to import it? The field I want to add is Services which will include the type of services offered for the particular flight (meals, refreshments, snacks, etc.).

[Guest lorathon]

If you open the admin/modules/import/import.php

find this area

// 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];

You should be able to add the following (the new field will be the 16th column counting from ZERO)

$services = $fields[16];

Then find this

$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
);

Add to the bottom of the array declaration

'services' => $services

The name of the variable ('services') must match the name of the column in the DB. Be sure to add a comma after the $flighttype.

[Jeff]

That's where it is, I wasn't even close. I thought it was somewhere in phpMyAdmin. Thanks Jeffrey

[Jeff]

Slight problem!

Here is the coding:

<?php
/**
* phpVMS - Virtual Airline Administration Software
* Copyright (c) 2008 Nabeel Shahzad
* For more information, visit www.phpvms.net
*	Forums: http://www.phpvms.net/forum
*	Documentation: http://www.phpvms.net/docs
*
* phpVMS is licenced under the following license:
*   Creative Commons Attribution Non-commercial Share Alike (by-nc-sa)
*   View license.txt in the root, or visit http://creativecommons.org/licenses/by-nc-sa/3.0/
*
* @author Nabeel Shahzad
* @copyright Copyright (c) 2008, Nabeel Shahzad
* @link http://www.phpvms.net
* @license http://creativecommons.org/licenses/by-nc-sa/3.0/
*/

class Import extends CodonModule
{
function HTMLHead()
{		
	switch($this->controller->function)
	{
		case '':
		default:
		case 'processimport':

			$this->set('sidebar', 'sidebar_import.tpl');

			break;
	}
}

public function index()
{
	$this->render('import_form.tpl');
}

public function export()
{
	$this->render('export_form.tpl');
}

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);
}

public function processimport()
{
	echo '<h3>Processing Import</h3>';

	if(!file_exists($_FILES['uploadedfile']['tmp_name']))
	{
		$this->set('message', 'File upload failed!');
		$this->render('core_error.tpl');
		return;
	}

	echo '<p><strong>DO NOT REFRESH OR STOP THIS PAGE</strong></p>';

	set_time_limit(270);
	$errs = array();
	$skip = false;

	$fp = fopen($_FILES['uploadedfile']['tmp_name'], 'r');

	if(isset($_POST['header'])) $skip = true;

	/* Delete all schedules before doing an import */
	if(isset($_POST['erase_routes']))
	{
		SchedulesData::deleteAllSchedules();
	}


	$added = 0;
	$updated = 0;
	$total = 0;
	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];
		$services = $fields[16];
		$duration = $fields[17];
		$airline = $fields[18];

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

		// Check the code:
		if(!OperationsData::GetAirlineByCode($code))
		{
			echo "Airline with code $code does not exist! Skipping...<br />";
			continue;
		}

		// Make sure airports exist:
		if(!($depapt = OperationsData::GetAirportInfo($depicao)))
		{
			$this->get_airport_info($depicao);
		}

		if(!($arrapt = OperationsData::GetAirportInfo($arricao)))
		{
			$this->get_airport_info($arricao);			
		}

		# Check the aircraft
		$aircraft = trim($aircraft);
		$ac_info = OperationsData::GetAircraftByReg($aircraft);

		# If the aircraft doesn't exist, skip it
		if(!$ac_info)
		{
			echo 'Aircraft "'.$aircraft.'" does not exist! Skipping<br />';
			continue;
		}
		$ac = $ac_info->id;

		if($flighttype == '')
		{
			$flighttype = 'P';
		}

		if($daysofweek == '')
			$daysofweek = '0123456';

		// Replace a 7 (Sunday) with 0 (since PHP thinks 0 is Sunday)
		$daysofweek = str_replace('7', '0', $daysofweek);

		# Check the distance

		if($distance == 0 || $distance == '')
		{
			$distance = OperationsData::getAirportDistance($depicao, $arricao);
		}

		$flighttype = strtoupper($flighttype);

		if($enabled == '0')
			$enabled = false;
		else
			$enabled = true;

		# 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,
						'services'=>$services,
						'duration'=>$duration,
						'airline'=>$airline);

		# Check if the schedule exists:
		if(($schedinfo = SchedulesData::getScheduleByFlight($code, $flightnum)))
		{
			# Update the schedule instead
			$val = SchedulesData::updateScheduleFields($schedinfo->id, $data);
			$updated++;
		}
		else
		{
			# Add it
			$val = SchedulesData::addSchedule($data);
			$added++;
		}

		if($val === false)
		{
			if(DB::errno() == 1216)
			{
				echo "Error adding $code$flightnum: The airline code, airports, or aircraft does not exist";
			}
			else
			{
				$error = (DB::error() != '') ? DB::error() : 'Route already exists';
				echo "$code$flightnum was not added, reason: $error<br />";
			}

			echo '<br />';
		}
		else
		{
			$total++;
			echo "Imported {$code}{$flightnum} ({$depicao} to {$arricao})<br />";
		}
	}

	CentralData::send_schedules();

	echo "The import process is complete, added {$added} schedules, updated {$updated}, for a total of {$total}<br />";

	foreach($errs as $error)
	{
		echo '    '.$error.'<br />';
	}

	echo '</div>';
}

protected function get_airport_info($icao)
{
	echo "ICAO $icao not added... retriving information: <br />";						
	$aptinfo = OperationsData::RetrieveAirportInfo($icao);

	if($aptinfo === false)
	{
		echo 'Could not retrieve information for '.$icao.', add it manually <br />';
	}
	else
	{
		echo "Found: $icao - ".$aptinfo->name
			.' ('.$aptinfo->lat.','.$aptinfo->lng.'), airport added<br /><br />';

		return $aptinfo;
	}
}
}

Here is what I am using to call it:

<TD CLASS="MYTABLE">Depart: <strong><?php echo $route->depicao;?></strong><br />Airport: <strong><?php $departname = OperationsData::getAirportInfo($route->depicao);?><?php echo $departname->name;?></strong><br />Depart Time: <strong><?php echo $route->deptime;?><br /></strong></TD>
       <TD CLASS="MYTABLE">Arrive: <strong><?php echo $route->arricao;?></strong><br />Airport: <strong><?php $arrivename = OperationsData::getAirportInfo($route->arricao);?><?php echo $arrivename->name;?></strong><br />Arrive Time: <strong><?php echo $route->arrtime;?><br /></strong></TD>
       <TD CLASS="MYTABLE">Flight: <strong><?php echo $route->code . -  $route->flightnum?></strong><br/>
                           Aircraft: <strong><?php echo $route->aircraft ?></strong><br/>
                           Duration: <strong><?php echo $route->notes ?></strong><br/>
                           Meals: <strong><?php echo $route->services ?></strong><br/>
                           Duration: <strong><?php echo $route->duration ?></strong><br/>
                           Operated By: <strong><?php echo $route->airline ?></strong><br/>
                           </TD>

[Guest lorathon]

First check to see if the table is being filled with the correct data though phpAdmin.

Second check the query that is pulling the data to make sure that it is pulling all fields from the schedules table. If it is not then you will need to change the query.

Do you get any errors when importing the new schedule with the new fields?

[Jeff]

Do you get any errors when importing the new schedule with the new fields?

Some of these (but a lot go through though, not sure why some do, some don't):

AC7397 was not added, reason: Unknown column 'services' in 'field list'

AC7399 was not added, reason: Unknown column 'services' in 'field list'

AC7394 was not added, reason: Unknown column 'services' in 'field list'

AC7396 was not added, reason: Unknown column 'services' in 'field list'

AC7443 was not added, reason: Unknown column 'services' in 'field list'

AC7445 was not added, reason: Unknown column 'services' in 'field list'

AC7442 was not added, reason: Unknown column 'services' in 'field list'

AC7446 was not added, reason: Unknown column 'services' in 'field list'

AC537 was not added, reason: Unknown column 'services' in 'field list'

AC538 was not added, reason: Unknown column 'services' in 'field list'

AC961 was not added, reason: Unknown column 'services' in 'field list'

[Jeff]

First check to see if the table is being filled with the correct data though phpAdmin.

Second check the query that is pulling the data to make sure that it is pulling all fields from the schedules table. If it is not then you will need to change the query.

I have to figure out how to do these.

Until then... do I need to add to this section at all?:

From the above code:

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}";

[Jeff]

I got it to work Jeffrey. The tables weren't getting filled as you said prior. I was able to figure out how to add to it. They are showing up for me now. Thank you for your help, it was actually easier than I thought. I guess I need to spend more time in phpMyAdmin more often to see how things work. Thanks a lot. +2

[Guest lorathon]

NP

[AlexEtz]

Could you describe how you added a new fields? because I am trying, everything is working fine until I call the $customfields ... something wrong on phpmyadmin that I didn't change... in fact I changed nothing because I don't know where should I look.