Accident::download_update() - Code Metrics - Inspection of "Add stats for fatalities" - Ysurac/FlightAirMap - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 6eadb9...5e27ff )

by Yannick

created 2017-02-01 14:26 UTC

Accident::download_update() D

↳ Parent: Accident

Complexity

Conditions	17
Paths	160

Size

Total Lines	39
Code Lines	30

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	17
eloc	30
nc	160
nop	0
dl	0
loc	39
rs	4.7011
c	0
b	0
f	0

How to fix Complexity

<?php
require_once(dirname(__FILE__).'/class.Connection.php');
require_once(dirname(__FILE__).'/class.Spotter.php');
require_once(dirname(__FILE__).'/class.Image.php');
require_once(dirname(__FILE__).'/class.Translation.php');

class Accident {
	public $db;

	public function __construct($dbc = null) {
		$Connection = new Connection($dbc);
		$this->db = $Connection->db();
	}


	
	public function get() {
		$query = 'SELECT DISTINCT registration FROM accidents ORDER BY date DESC';
		$sth = $this->db->prepare($query);
		$sth->execute();
		$result = $sth->fetchAll(PDO::FETCH_ASSOC);
		return $result;
	}
	
	/**
	* Get Accidents data from DB
	* @param String $limit Limit
	* @param String $type Set type accident or incident
	* @param String $date get data for a date
	* @return Array Return Accidents data in array
	*/
	public function getAccidentData($limit = '',$type = '',$date = '') {
		global $globalURL, $globalDBdriver;
		$Image = new Image($this->db);
		$Spotter = new Spotter($this->db);
		$Translation = new Translation($this->db);
		$date = filter_var($date,FILTER_SANITIZE_STRING);
		date_default_timezone_set('UTC');
		$result = array();
		$limit_query = '';
		if ($limit != "")
		{
			$limit_array = explode(",", $limit);
			$limit_array[0] = filter_var($limit_array[0],FILTER_SANITIZE_NUMBER_INT);
			$limit_array[1] = filter_var($limit_array[1],FILTER_SANITIZE_NUMBER_INT);
			if ($limit_array[0] >= 0 && $limit_array[1] >= 0)
			{
				$limit_query = " LIMIT ".$limit_array[1]." OFFSET ".$limit_array[0];
			}
		}

		if ($type != '') {
			if ($date != '') {
				if (preg_match("/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/",$date)) {
					$query = "SELECT * FROM accidents WHERE accidents_id IN (SELECT max(accidents_id) FROM accidents WHERE type = :type AND date = :date GROUP BY registration) ORDER BY date DESC".$limit_query;
				} else {
					$date = $date.'%';
					$query = "SELECT * FROM accidents WHERE accidents_id IN (SELECT max(accidents_id) FROM accidents WHERE type = :type AND to_char(date,'YYYY-MM-DD') LIKE :date GROUP BY registration) ORDER BY date DESC".$limit_query;
				}
				$query_values = array(':type' => $type,':date' => $date);
			} else {
				$query = "SELECT * FROM accidents WHERE accidents_id IN (SELECT max(accidents_id) FROM accidents WHERE type = :type GROUP BY registration) ORDER BY date DESC".$limit_query;
				$query_values = array(':type' => $type);
			}
		} else {
			if ($date != '') {
				if (preg_match("/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/",$date)) {
					$query = "SELECT * FROM accidents WHERE accidents_id IN (SELECT max(accidents_id) FROM accidents WHERE date = :date GROUP BY registration) ORDER BY date DESC".$limit_query;
				} else {
					$date = $date.'%';
					$query = "SELECT * FROM accidents WHERE accidents_id IN (SELECT max(accidents_id) FROM accidents WHERE to_char(date,'YYYY-MM-DD') LIKE :date GROUP BY registration) ORDER BY date DESC".$limit_query;
				}
				$query_values = array(':date' => $date);
			} else {
				$query = "SELECT * FROM accidents WHERE accidents_id IN (SELECT max(accidents_id) FROM accidents GROUP BY registration) ORDER BY date DESC".$limit_query;
				$query_values = array();
			}
		}

		try {
			$sth = $this->db->prepare($query);
			$sth->execute($query_values);
		} catch(PDOException $e) {
			return "error : ".$e->getMessage();
		}
		$i = 0;
		while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
			$data = array();
			if ($row['registration'] != '') {
				$image_array = $Image->getSpotterImage($row['registration']);
				if (count($image_array) > 0) $data = array_merge($data,array('image' => $image_array[0]['image'],'image_thumbnail' => $image_array[0]['image_thumbnail'],'image_copyright' => $image_array[0]['image_copyright'],'image_source' => $image_array[0]['image_source'],'image_source_website' => $image_array[0]['image_source_website']));
				else $data = array_merge($data,array('image' => '','image_thumbnail' => '','image_copyright' => '','image_source' => '','image_source_website' => ''));
				$aircraft_type = $Spotter->getAllAircraftTypeByRegistration($row['registration']);
				$aircraft_info = $Spotter->getAllAircraftInfo($aircraft_type);
				if (!empty($aircraft_info)) {
					$data['aircraft_type'] = $aircraft_info[0]['icao'];
					$data['aircraft_name'] = $aircraft_info[0]['type'];
					$data['aircraft_manufacturer'] = $aircraft_info[0]['manufacturer'];
				} else {
					$data = array_merge($data,array('aircraft_type' => 'NA'));
				}
				$owner_data = $Spotter->getAircraftOwnerByRegistration($row['registration']);
				if (!empty($owner_data)) {
					$data['aircraft_owner'] = $owner_data['owner'];
					$data['aircraft_base'] = $owner_data['base'];
					$data['aircraft_date_first_reg'] = $owner_data['date_first_reg'];
				}
			} else $data = array_merge($data,array('image' => '','image_thumbnail' => '','image_copyright' => '','image_source' => '','image_source_website' => ''));
			if ($row['registration'] == '') $row['registration'] = 'NA';
			if ($row['ident'] == '') $row['ident'] = 'NA';
			$identicao = $Spotter->getAllAirlineInfo(substr($row['ident'],0,3));
			if (isset($identicao[0])) {
				if (substr($row['ident'],0,2) == 'AF') {
					if (filter_var(substr($row['ident'],2),FILTER_VALIDATE_INT,array("flags"=>FILTER_FLAG_ALLOW_OCTAL))) $icao = $row['ident'];
					else $icao = 'AFR'.ltrim(substr($row['ident'],2),'0');
				} else $icao = $identicao[0]['icao'].ltrim(substr($row['ident'],2),'0');
				$data = array_merge($data,array('airline_icao' => $identicao[0]['icao'],'airline_name' => $identicao[0]['name']));
			} else $icao = $row['ident'];
			$icao = $Translation->checkTranslation($icao,false);
			//$data = array_merge($data,array('registration' => $row['registration'], 'date' => $row['date'], 'ident' => $icao,'url' => $row['url']));
			if ($row['airline_name'] != '' && !isset($data['airline_name'])) {
				//echo 'Check airline info... for '.$row['airline_name'].' ';
				//echo $row['airline_name'];
				$airline_info = $Spotter->getAllAirlineInfoByName($row['airline_name']);
				if (!empty($airline_info)) {
					//echo 'data found !'."\n";
					//print_r($airline_info);
					$data = array_merge($data,$airline_info);
				} 
				//else echo 'No data...'."\n";
			}
			$data = array_merge($row,$data);
			if ($data['ident'] == null) $data['ident'] = $icao;
			if ($data['title'] == null) {
				$data['message'] = $row['type'].' of '.$row['registration'].' at '.$row['place'].','.$row['country'];
			} else $data['message'] = strtolower($data['title']);
			$result[] = $data;
			$i++;
		}
		if (isset($result)) {
			$result[0]['query_number_rows'] = $i;
			return $result;
		}
		else return array();
	}
	
	/*
	* Get fatalities by year
	* @return Array number of fatalities by year
	*/
	public function countFatalitiesByYear() {
		$query = 'SELECT EXTRACT(year FROM date) AS year, SUM(fatalities) as count FROM accidents WHERE accidents_id IN (SELECT MAX(accidents_id) FROM accidents WHERE fatalities > 0 AND EXTRACT(year FROM date) > 2006 GROUP BY registration) GROUP BY EXTRACT(year FROM date) ORDER BY year';
		try {
			$sth = $this->db->prepare($query);
			$sth->execute();
		} catch(PDOException $e) {
			echo "Error : ".$e->getMessage();
		}
		return $sth->fetchAll(PDO::FETCH_ASSOC);
	}

	/*
	* Get fatalities last 12 months
	* @return Array number of fatalities last 12 months
	*/
	public function countFatalitiesLast12Months() {
		$query = "SELECT EXTRACT(month FROM date) AS month, EXTRACT(year FROM date) AS year, SUM(fatalities) as count FROM accidents WHERE accidents_id IN (SELECT MAX(accidents_id) FROM accidents WHERE fatalities > 0 AND date > (current_date - INTERVAL '12 months') GROUP BY registration) GROUP BY EXTRACT(month FROM date), EXTRACT(year FROM date) ORDER BY year,month";
		try {
			$sth = $this->db->prepare($query);
			$sth->execute();
		} catch(PDOException $e) {
			echo "Error : ".$e->getMessage();
		}
		return $sth->fetchAll(PDO::FETCH_ASSOC);
	}
	
	/*
	* Import csv accidents file into the DB
	* @param String $file filename of the file to import
	*/
	public function import($file) {
		global $globalTransaction, $globalDebug;
		if ($globalDebug) echo 'Import '.$file."\n";
		$result = array();
		if (file_exists($file)) {
			if (($handle = fopen($file,'r')) !== FALSE) {
				while (($data = fgetcsv($handle,2000,",")) !== FALSE) {
					if (isset($data[1]) && $data[1] != '0000-00-00 00:00:00') {
						$result[] = array('registration' => $data[0],'date' => strtotime($data[1]),'url' => $data[2],'country' => $data[3],'place' => $data[4],'title' => $data[5],'fatalities' => $data[6],'latitude' => $data[7],'longitude' => $data[8],'type' => $data[9],'ident' => $data[10],'aircraft_manufacturer' => $data[11],'aircraft_name' => $data[12],'operator' => $data[13],'source' => 'website_fam');
					}
				}
				fclose($handle);
			}
			if (!empty($result)) $this->add($result,true);
			elseif ($globalDebug) echo 'Nothing to import';
		}
	}

	/*
	* Check if file changed since last update, if true import modified files
	*/
	public function download_update() {
		global $globalDebug;
		require_once('class.Common.php');
		$Common = new Common();
		$all_md5 = array();
		$all_md5_new = array();
		if (file_exists(dirname(__FILE__).'/../install/tmp/cr-all.md5')) {
			if ($this->check_accidents_nb() > 0) {
				if (($handle = fopen(dirname(__FILE__).'/../install/tmp/cr-all.md5','r')) !== FALSE) {
					while (($data = fgetcsv($handle,2000,"\t")) !== FALSE) {
						if (isset($data[1])) {
							$year = $data[0];
							$all_md5[$year] = $data[1];
						}
					}
					fclose($handle);
				}
			}
		}
		$Common->download('http://data.flightairmap.fr/data/cr/cr-all.md5',dirname(__FILE__).'/../install/tmp/cr-all.md5');
		if (file_exists(dirname(__FILE__).'/../install/tmp/cr-all.md5')) {
			if (($handle = fopen(dirname(__FILE__).'/../install/tmp/cr-all.md5','r')) !== FALSE) {
				while (($data = fgetcsv($handle,2000,"\t")) !== FALSE) {
					if (isset($data[1])) {
						$year = $data[0];
						$all_md5_new[$year] = $data[1];
					}
				}
				fclose($handle);
			} elseif ($globalDebug) echo "Can't open ".dirname(__FILE__).'/../install/tmp/cr-all.md5';
		} elseif ($globalDebug) echo 'Download cr-all.md5 failed. '.dirname(__FILE__).'/../install/tmp/cr-all.md5 not here.';
		$result = $Common->arr_diff($all_md5_new,$all_md5);
		if (empty($result) && $globalDebug) echo 'Nothing to update';
		foreach ($result as $file => $md5) {
			$Common->download('http://data.flightairmap.fr/data/cr/'.$file,dirname(__FILE__).'/../install/tmp/'.$file);
			if (file_exists(dirname(__FILE__).'/../install/tmp/'.$file)) $this->import(dirname(__FILE__).'/../install/tmp/'.$file);
			elseif ($globalDebug) echo 'Download '.$file.' failed';
		}
	}

	/*
	* Add data to DB
	* @param Array $crash An array with accidents/incidents data
	*/
	public function add($crash,$new = false) {
		global $globalTransaction, $globalDebug;
		require_once('class.Connection.php');
		require_once('class.Image.php');
		require_once('class.Spotter.php');
		$Connection = new Connection();
		$Image = new Image();
		$Spotter = new Spotter();

		if (empty($crash)) return false;
		if (!$new) {
			$query_delete = 'DELETE FROM accidents WHERE source = :source';
			$sthd = $Connection->db->prepare($query_delete);
			$sthd->execute(array(':source' => $crash[0]['source']));
		}
		if ($globalTransaction) $Connection->db->beginTransaction();

		$initial_array = array('ident' => null,'type' => 'accident','url' => null,'registration' => null, 'date' => null, 'place' => null,'country' => null, 'latitude' => null, 'longitude' => null, 'fatalities' => null, 'title' => '','source' => '','aircraft_manufacturer' => null,'aircraft_name' => null,'operator' => null);
		$query_check = 'SELECT COUNT(*) as nb FROM accidents WHERE registration = :registration AND date = :date AND type = :type AND source = :source';
		$sth_check = $Connection->db->prepare($query_check);
		$query = 'INSERT INTO accidents (aircraft_manufacturer,aircraft_name,ident,registration,date,url,country,place,title,fatalities,latitude,longitude,type,airline_name,source) VALUES (:aircraft_manufacturer,:aircraft_name,:ident,:registration,:date,:url,:country,:place,:title,:fatalities,:latitude,:longitude,:type,:airline_name,:source)';
		$sth = $Connection->db->prepare($query);
		$j = 0;
		try {
			foreach ($crash as $cr) {
				//print_r($cr);
				$cr = $cr + $initial_array;
				$cr = array_map(function($value) {
					return $value === "" ? NULL : $value;
				}, $cr);
				if ($cr['date'] != '' && $cr['registration'] != null && $cr['registration'] != '' && $cr['registration'] != '?' && $cr['registration'] != '-' && strtolower($cr['registration']) != 'unknown' && $cr['date'] < time() && !preg_match('/\s/',$cr['registration'])) {
					if (strpos($cr['registration'],'-') === FALSE) $cr['registration'] = $Spotter->convertAircraftRegistration($cr['registration']);
					$query_check_values = array(':registration' => $cr['registration'],':date' => date('Y-m-d',$cr['date']),':type' => $cr['type'],':source' => $cr['source']);
					$sth_check->execute($query_check_values);
					$result_check = $sth_check->fetch(PDO::FETCH_ASSOC);
					if ($result_check['nb'] == 0) {
						$query_values = array(':registration' => trim($cr['registration']),':date' => date('Y-m-d',$cr['date']),':url' => $cr['url'],':country' => $cr['country'],':place' => $cr['place'],':title' => $cr['title'],':fatalities' => $cr['fatalities'],':latitude' => $cr['latitude'],':longitude' => $cr['longitude'],':type' => $cr['type'],':source' => $cr['source'],':ident' => $cr['ident'],':aircraft_manufacturer' => $cr['aircraft_manufacturer'],':aircraft_name' => $cr['aircraft_name'],':airline_name' => $cr['operator']);
						$sth->execute($query_values);
						if ($cr['date'] > time()-(30*86400)) {
							if (empty($Image->getSpotterImage($cr['registration']))) {
								//if ($globalDebug) echo 'Get image...'."\n";
								$Image->addSpotterImage($cr['registration']);
							}
							// elseif ($globalDebug) echo 'Image already in DB'."\n";
						}
						$Spotter->setHighlightFlightByRegistration($cr['registration'],$cr['title'],date('Y-m-d',$cr['date']));
					}
				}
				if ($globalTransaction && $j % 1000 == 0) {
					$Connection->db->commit();

					$Connection->db->beginTransaction();

				}
			}
			if ($globalTransaction) $Connection->db->commit();

		} catch(PDOException $e) {
			if ($globalTransaction) $Connection->db->rollBack();

			echo $e->getMessage();
		}
		$sth_check->closeCursor();
	}

	/*
	* Get number of accidents
	* @return Integer Number of accidents/incidents in table
	*/
	public static function check_accidents_nb() {
		global $globalDBdriver;
			$query = "SELECT COUNT(*) as nb FROM accidents";
		try {
			$Connection = new Connection();
			$sth = $Connection->db->prepare($query);
			$sth->execute();
		} catch(PDOException $e) {
			return "error : ".$e->getMessage();
		}
		$row = $sth->fetch(PDO::FETCH_ASSOC);
		return $row['nb'];
	}

	public static function check_last_accidents_update() {
		global $globalDBdriver;
		if ($globalDBdriver == 'mysql') {
			$query = "SELECT COUNT(*) as nb FROM config WHERE name = 'last_update_accident_db' AND value > DATE_SUB(NOW(), INTERVAL 1 DAY)";
		} else {
			$query = "SELECT COUNT(*) as nb FROM config WHERE name = 'last_update_accident_db' AND value::timestamp > CURRENT_TIMESTAMP - INTERVAL '1 DAYS'";
		}
		try {
			$Connection = new Connection();
			$sth = $Connection->db->prepare($query);
			$sth->execute();
		} catch(PDOException $e) {
			return "error : ".$e->getMessage();
		}
		$row = $sth->fetch(PDO::FETCH_ASSOC);
		if ($row['nb'] > 0) return false;
		else return true;
	}

	public static function insert_last_accidents_update() {
		$query = "DELETE FROM config WHERE name = 'last_update_accident_db';
		    INSERT INTO config (name,value) VALUES ('last_update_accident_db',NOW());";
		try {
			$Connection = new Connection();
			$sth = $Connection->db->prepare($query);
			$sth->execute();
		} catch(PDOException $e) {
			return "error : ".$e->getMessage();
		}
	}

}
?>

1			<?php
2			require_once(dirname(__FILE__).'/class.Connection.php');
3			require_once(dirname(__FILE__).'/class.Spotter.php');
4			require_once(dirname(__FILE__).'/class.Image.php');
5			require_once(dirname(__FILE__).'/class.Translation.php');
6
7			class Accident {
8			public $db;
9
10			public function __construct($dbc = null) {
11			$Connection = new Connection($dbc);
12			$this->db = $Connection->db();
13			}
14
15
16
17			public function get() {
18			$query = 'SELECT DISTINCT registration FROM accidents ORDER BY date DESC';
19			$sth = $this->db->prepare($query);
20			$sth->execute();
21			$result = $sth->fetchAll(PDO::FETCH_ASSOC);
22			return $result;
23			}
24
25			/**
26			* Get Accidents data from DB
27			* @param String $limit Limit
28			* @param String $type Set type accident or incident
29			* @param String $date get data for a date
30			* @return Array Return Accidents data in array
31			*/
32			public function getAccidentData($limit = '',$type = '',$date = '') {
33			global $globalURL, $globalDBdriver;
34			$Image = new Image($this->db);
35			$Spotter = new Spotter($this->db);
36			$Translation = new Translation($this->db);
37			$date = filter_var($date,FILTER_SANITIZE_STRING);
38			date_default_timezone_set('UTC');
39			$result = array();
40			$limit_query = '';
41			if ($limit != "")
42			{
43			$limit_array = explode(",", $limit);
44			$limit_array[0] = filter_var($limit_array[0],FILTER_SANITIZE_NUMBER_INT);
45			$limit_array[1] = filter_var($limit_array[1],FILTER_SANITIZE_NUMBER_INT);
46			if ($limit_array[0] >= 0 && $limit_array[1] >= 0)
47			{
48			$limit_query = " LIMIT ".$limit_array[1]." OFFSET ".$limit_array[0];
49			}
50			}
51
52			if ($type != '') {
53			if ($date != '') {
54			if (preg_match("/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/",$date)) {
55			$query = "SELECT * FROM accidents WHERE accidents_id IN (SELECT max(accidents_id) FROM accidents WHERE type = :type AND date = :date GROUP BY registration) ORDER BY date DESC".$limit_query;
56			} else {
57			$date = $date.'%';
58			$query = "SELECT * FROM accidents WHERE accidents_id IN (SELECT max(accidents_id) FROM accidents WHERE type = :type AND to_char(date,'YYYY-MM-DD') LIKE :date GROUP BY registration) ORDER BY date DESC".$limit_query;
59			}
60			$query_values = array(':type' => $type,':date' => $date);
61			} else {
62			$query = "SELECT * FROM accidents WHERE accidents_id IN (SELECT max(accidents_id) FROM accidents WHERE type = :type GROUP BY registration) ORDER BY date DESC".$limit_query;
63			$query_values = array(':type' => $type);
64			}
65			} else {
66			if ($date != '') {
67			if (preg_match("/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/",$date)) {
68			$query = "SELECT * FROM accidents WHERE accidents_id IN (SELECT max(accidents_id) FROM accidents WHERE date = :date GROUP BY registration) ORDER BY date DESC".$limit_query;
69			} else {
70			$date = $date.'%';
71			$query = "SELECT * FROM accidents WHERE accidents_id IN (SELECT max(accidents_id) FROM accidents WHERE to_char(date,'YYYY-MM-DD') LIKE :date GROUP BY registration) ORDER BY date DESC".$limit_query;
72			}
73			$query_values = array(':date' => $date);
74			} else {
75			$query = "SELECT * FROM accidents WHERE accidents_id IN (SELECT max(accidents_id) FROM accidents GROUP BY registration) ORDER BY date DESC".$limit_query;
76			$query_values = array();
77			}
78			}
79
80			try {
81			$sth = $this->db->prepare($query);
82			$sth->execute($query_values);
83			} catch(PDOException $e) {
84			return "error : ".$e->getMessage();
85			}
86			$i = 0;
87			while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
88			$data = array();
89			if ($row['registration'] != '') {
90			$image_array = $Image->getSpotterImage($row['registration']);
91			if (count($image_array) > 0) $data = array_merge($data,array('image' => $image_array[0]['image'],'image_thumbnail' => $image_array[0]['image_thumbnail'],'image_copyright' => $image_array[0]['image_copyright'],'image_source' => $image_array[0]['image_source'],'image_source_website' => $image_array[0]['image_source_website']));
92			else $data = array_merge($data,array('image' => '','image_thumbnail' => '','image_copyright' => '','image_source' => '','image_source_website' => ''));
93			$aircraft_type = $Spotter->getAllAircraftTypeByRegistration($row['registration']);
94			$aircraft_info = $Spotter->getAllAircraftInfo($aircraft_type);
95			if (!empty($aircraft_info)) {
96			$data['aircraft_type'] = $aircraft_info[0]['icao'];
97			$data['aircraft_name'] = $aircraft_info[0]['type'];
98			$data['aircraft_manufacturer'] = $aircraft_info[0]['manufacturer'];
99			} else {
100			$data = array_merge($data,array('aircraft_type' => 'NA'));
101			}
102			$owner_data = $Spotter->getAircraftOwnerByRegistration($row['registration']);
103			if (!empty($owner_data)) {
104			$data['aircraft_owner'] = $owner_data['owner'];
105			$data['aircraft_base'] = $owner_data['base'];
106			$data['aircraft_date_first_reg'] = $owner_data['date_first_reg'];
107			}
108			} else $data = array_merge($data,array('image' => '','image_thumbnail' => '','image_copyright' => '','image_source' => '','image_source_website' => ''));
109			if ($row['registration'] == '') $row['registration'] = 'NA';
110			if ($row['ident'] == '') $row['ident'] = 'NA';
111			$identicao = $Spotter->getAllAirlineInfo(substr($row['ident'],0,3));
112			if (isset($identicao[0])) {
113			if (substr($row['ident'],0,2) == 'AF') {
114			if (filter_var(substr($row['ident'],2),FILTER_VALIDATE_INT,array("flags"=>FILTER_FLAG_ALLOW_OCTAL))) $icao = $row['ident'];
115			else $icao = 'AFR'.ltrim(substr($row['ident'],2),'0');
116			} else $icao = $identicao[0]['icao'].ltrim(substr($row['ident'],2),'0');
117			$data = array_merge($data,array('airline_icao' => $identicao[0]['icao'],'airline_name' => $identicao[0]['name']));
118			} else $icao = $row['ident'];
119			$icao = $Translation->checkTranslation($icao,false);
120			//$data = array_merge($data,array('registration' => $row['registration'], 'date' => $row['date'], 'ident' => $icao,'url' => $row['url']));
121			if ($row['airline_name'] != '' && !isset($data['airline_name'])) {
122			//echo 'Check airline info... for '.$row['airline_name'].' ';
123			//echo $row['airline_name'];
124			$airline_info = $Spotter->getAllAirlineInfoByName($row['airline_name']);
125			if (!empty($airline_info)) {
126			//echo 'data found !'."\n";
127			//print_r($airline_info);
128			$data = array_merge($data,$airline_info);
129			}
130			//else echo 'No data...'."\n";
131			}
132			$data = array_merge($row,$data);
133			if ($data['ident'] == null) $data['ident'] = $icao;
134			if ($data['title'] == null) {
135			$data['message'] = $row['type'].' of '.$row['registration'].' at '.$row['place'].','.$row['country'];
136			} else $data['message'] = strtolower($data['title']);
137			$result[] = $data;
138			$i++;
139			}
140			if (isset($result)) {
141			$result[0]['query_number_rows'] = $i;
142			return $result;
143			}
144			else return array();
145			}
146
147			/*
148			* Get fatalities by year
149			* @return Array number of fatalities by year
150			*/
151			public function countFatalitiesByYear() {
152			$query = 'SELECT EXTRACT(year FROM date) AS year, SUM(fatalities) as count FROM accidents WHERE accidents_id IN (SELECT MAX(accidents_id) FROM accidents WHERE fatalities > 0 AND EXTRACT(year FROM date) > 2006 GROUP BY registration) GROUP BY EXTRACT(year FROM date) ORDER BY year';
153			try {
154			$sth = $this->db->prepare($query);
155			$sth->execute();
156			} catch(PDOException $e) {
157			echo "Error : ".$e->getMessage();
158			}
159			return $sth->fetchAll(PDO::FETCH_ASSOC);
160			}
161
162			/*
163			* Get fatalities last 12 months
164			* @return Array number of fatalities last 12 months
165			*/
166			public function countFatalitiesLast12Months() {
167			$query = "SELECT EXTRACT(month FROM date) AS month, EXTRACT(year FROM date) AS year, SUM(fatalities) as count FROM accidents WHERE accidents_id IN (SELECT MAX(accidents_id) FROM accidents WHERE fatalities > 0 AND date > (current_date - INTERVAL '12 months') GROUP BY registration) GROUP BY EXTRACT(month FROM date), EXTRACT(year FROM date) ORDER BY year,month";
168			try {
169			$sth = $this->db->prepare($query);
170			$sth->execute();
171			} catch(PDOException $e) {
172			echo "Error : ".$e->getMessage();
173			}
174			return $sth->fetchAll(PDO::FETCH_ASSOC);
175			}
176
177			/*
178			* Import csv accidents file into the DB
179			* @param String $file filename of the file to import
180			*/
181			public function import($file) {
182			global $globalTransaction, $globalDebug;
183			if ($globalDebug) echo 'Import '.$file."\n";
184			$result = array();
185			if (file_exists($file)) {
186			if (($handle = fopen($file,'r')) !== FALSE) {
187			while (($data = fgetcsv($handle,2000,",")) !== FALSE) {
188			if (isset($data[1]) && $data[1] != '0000-00-00 00:00:00') {
189			$result[] = array('registration' => $data[0],'date' => strtotime($data[1]),'url' => $data[2],'country' => $data[3],'place' => $data[4],'title' => $data[5],'fatalities' => $data[6],'latitude' => $data[7],'longitude' => $data[8],'type' => $data[9],'ident' => $data[10],'aircraft_manufacturer' => $data[11],'aircraft_name' => $data[12],'operator' => $data[13],'source' => 'website_fam');
190			}
191			}
192			fclose($handle);
193			}
194			if (!empty($result)) $this->add($result,true);
195			elseif ($globalDebug) echo 'Nothing to import';
196			}
197			}
198
199			/*
200			* Check if file changed since last update, if true import modified files
201			*/
202			public function download_update() {
203			global $globalDebug;
204			require_once('class.Common.php');
205			$Common = new Common();
206			$all_md5 = array();
207			$all_md5_new = array();
208			if (file_exists(dirname(__FILE__).'/../install/tmp/cr-all.md5')) {
209			if ($this->check_accidents_nb() > 0) {
210			if (($handle = fopen(dirname(__FILE__).'/../install/tmp/cr-all.md5','r')) !== FALSE) {
211			while (($data = fgetcsv($handle,2000,"\t")) !== FALSE) {
212			if (isset($data[1])) {
213			$year = $data[0];
214			$all_md5[$year] = $data[1];
215			}
216			}
217			fclose($handle);
218			}
219			}
220			}
221			$Common->download('http://data.flightairmap.fr/data/cr/cr-all.md5',dirname(__FILE__).'/../install/tmp/cr-all.md5');
222			if (file_exists(dirname(__FILE__).'/../install/tmp/cr-all.md5')) {
223			if (($handle = fopen(dirname(__FILE__).'/../install/tmp/cr-all.md5','r')) !== FALSE) {
224			while (($data = fgetcsv($handle,2000,"\t")) !== FALSE) {
225			if (isset($data[1])) {
226			$year = $data[0];
227			$all_md5_new[$year] = $data[1];
228			}
229			}
230			fclose($handle);
231			} elseif ($globalDebug) echo "Can't open ".dirname(__FILE__).'/../install/tmp/cr-all.md5';
232			} elseif ($globalDebug) echo 'Download cr-all.md5 failed. '.dirname(__FILE__).'/../install/tmp/cr-all.md5 not here.';
233			$result = $Common->arr_diff($all_md5_new,$all_md5);
234			if (empty($result) && $globalDebug) echo 'Nothing to update';
235			foreach ($result as $file => $md5) {
236			$Common->download('http://data.flightairmap.fr/data/cr/'.$file,dirname(__FILE__).'/../install/tmp/'.$file);
237			if (file_exists(dirname(__FILE__).'/../install/tmp/'.$file)) $this->import(dirname(__FILE__).'/../install/tmp/'.$file);
238			elseif ($globalDebug) echo 'Download '.$file.' failed';
239			}
240			}
241
242			/*
243			* Add data to DB
244			* @param Array $crash An array with accidents/incidents data
245			*/
246			public function add($crash,$new = false) {
247			global $globalTransaction, $globalDebug;
248			require_once('class.Connection.php');
249			require_once('class.Image.php');
250			require_once('class.Spotter.php');
251			$Connection = new Connection();
252			$Image = new Image();
253			$Spotter = new Spotter();
254
255			if (empty($crash)) return false;
256			if (!$new) {
257			$query_delete = 'DELETE FROM accidents WHERE source = :source';
258			$sthd = $Connection->db->prepare($query_delete);
259			$sthd->execute(array(':source' => $crash[0]['source']));
260			}
261			if ($globalTransaction) $Connection->db->beginTransaction();
			1 ignored issue – show Bug introduced 2017-01-17 17:13 UTC by Report Bug Copy Issue Report The method `beginTransaction` cannot be called on `$Connection->db` (of type `null`). Methods can only be called on objects. This check looks for methods being called on variables that have been inferred to never be objects. Loading history...
262			$initial_array = array('ident' => null,'type' => 'accident','url' => null,'registration' => null, 'date' => null, 'place' => null,'country' => null, 'latitude' => null, 'longitude' => null, 'fatalities' => null, 'title' => '','source' => '','aircraft_manufacturer' => null,'aircraft_name' => null,'operator' => null);
263			$query_check = 'SELECT COUNT(*) as nb FROM accidents WHERE registration = :registration AND date = :date AND type = :type AND source = :source';
264			$sth_check = $Connection->db->prepare($query_check);
265			$query = 'INSERT INTO accidents (aircraft_manufacturer,aircraft_name,ident,registration,date,url,country,place,title,fatalities,latitude,longitude,type,airline_name,source) VALUES (:aircraft_manufacturer,:aircraft_name,:ident,:registration,:date,:url,:country,:place,:title,:fatalities,:latitude,:longitude,:type,:airline_name,:source)';
266			$sth = $Connection->db->prepare($query);
267			$j = 0;
268			try {
269			foreach ($crash as $cr) {
270			//print_r($cr);
271			$cr = $cr + $initial_array;
272			$cr = array_map(function($value) {
273			return $value === "" ? NULL : $value;
274			}, $cr);
275			if ($cr['date'] != '' && $cr['registration'] != null && $cr['registration'] != '' && $cr['registration'] != '?' && $cr['registration'] != '-' && strtolower($cr['registration']) != 'unknown' && $cr['date'] < time() && !preg_match('/\s/',$cr['registration'])) {
276			if (strpos($cr['registration'],'-') === FALSE) $cr['registration'] = $Spotter->convertAircraftRegistration($cr['registration']);
277			$query_check_values = array(':registration' => $cr['registration'],':date' => date('Y-m-d',$cr['date']),':type' => $cr['type'],':source' => $cr['source']);
278			$sth_check->execute($query_check_values);
279			$result_check = $sth_check->fetch(PDO::FETCH_ASSOC);
280			if ($result_check['nb'] == 0) {
281			$query_values = array(':registration' => trim($cr['registration']),':date' => date('Y-m-d',$cr['date']),':url' => $cr['url'],':country' => $cr['country'],':place' => $cr['place'],':title' => $cr['title'],':fatalities' => $cr['fatalities'],':latitude' => $cr['latitude'],':longitude' => $cr['longitude'],':type' => $cr['type'],':source' => $cr['source'],':ident' => $cr['ident'],':aircraft_manufacturer' => $cr['aircraft_manufacturer'],':aircraft_name' => $cr['aircraft_name'],':airline_name' => $cr['operator']);
282			$sth->execute($query_values);
283			if ($cr['date'] > time()-(30*86400)) {
284			if (empty($Image->getSpotterImage($cr['registration']))) {
285			//if ($globalDebug) echo 'Get image...'."\n";
286			$Image->addSpotterImage($cr['registration']);
287			}
288			// elseif ($globalDebug) echo 'Image already in DB'."\n";
289			}
290			$Spotter->setHighlightFlightByRegistration($cr['registration'],$cr['title'],date('Y-m-d',$cr['date']));
291			}
292			}
293			if ($globalTransaction && $j % 1000 == 0) {
294			$Connection->db->commit();
			1 ignored issue – show Bug introduced 2017-01-17 17:13 UTC by Report Bug Copy Issue Report The method `commit` cannot be called on `$Connection->db` (of type `null`). Methods can only be called on objects. This check looks for methods being called on variables that have been inferred to never be objects. Loading history...
295			$Connection->db->beginTransaction();
			1 ignored issue – show Bug introduced 2017-01-17 17:13 UTC by Report Bug Copy Issue Report The method `beginTransaction` cannot be called on `$Connection->db` (of type `null`). Methods can only be called on objects. This check looks for methods being called on variables that have been inferred to never be objects. Loading history...
296			}
297			}
298			if ($globalTransaction) $Connection->db->commit();
			1 ignored issue – show Bug introduced 2017-01-17 17:13 UTC by Report Bug Copy Issue Report The method `commit` cannot be called on `$Connection->db` (of type `null`). Methods can only be called on objects. This check looks for methods being called on variables that have been inferred to never be objects. Loading history...
299			} catch(PDOException $e) {
300			if ($globalTransaction) $Connection->db->rollBack();
			1 ignored issue – show Bug introduced 2017-01-17 17:13 UTC by Report Bug Copy Issue Report The method `rollBack` cannot be called on `$Connection->db` (of type `null`). Methods can only be called on objects. This check looks for methods being called on variables that have been inferred to never be objects. Loading history...
301			echo $e->getMessage();
302			}
303			$sth_check->closeCursor();
304			}
305
306			/*
307			* Get number of accidents
308			* @return Integer Number of accidents/incidents in table
309			*/
310			public static function check_accidents_nb() {
311			global $globalDBdriver;
312			$query = "SELECT COUNT(*) as nb FROM accidents";
313			try {
314			$Connection = new Connection();
315			$sth = $Connection->db->prepare($query);
316			$sth->execute();
317			} catch(PDOException $e) {
318			return "error : ".$e->getMessage();
319			}
320			$row = $sth->fetch(PDO::FETCH_ASSOC);
321			return $row['nb'];
322			}
323
324			public static function check_last_accidents_update() {
325			global $globalDBdriver;
326			if ($globalDBdriver == 'mysql') {
327			$query = "SELECT COUNT(*) as nb FROM config WHERE name = 'last_update_accident_db' AND value > DATE_SUB(NOW(), INTERVAL 1 DAY)";
328			} else {
329			$query = "SELECT COUNT(*) as nb FROM config WHERE name = 'last_update_accident_db' AND value::timestamp > CURRENT_TIMESTAMP - INTERVAL '1 DAYS'";
330			}
331			try {
332			$Connection = new Connection();
333			$sth = $Connection->db->prepare($query);
334			$sth->execute();
335			} catch(PDOException $e) {
336			return "error : ".$e->getMessage();
337			}
338			$row = $sth->fetch(PDO::FETCH_ASSOC);
339			if ($row['nb'] > 0) return false;
340			else return true;
341			}
342
343			public static function insert_last_accidents_update() {
344			$query = "DELETE FROM config WHERE name = 'last_update_accident_db';
345			INSERT INTO config (name,value) VALUES ('last_update_accident_db',NOW());";
346			try {
347			$Connection = new Connection();
348			$sth = $Connection->db->prepare($query);
349			$sth->execute();
350			} catch(PDOException $e) {
351			return "error : ".$e->getMessage();
352			}
353			}
354
355			}
356			?>

Ysurac / FlightAirMap

Push — master ( 6eadb9...5e27ff )

Accident::download_update() D

Complexity

Size

Duplication

Importance

How to fix Complexity

Long Method

Duplication Side-by-Side

Filter issues like