Ysurac /
FlightAirMap
Duplicate code is one of the most pungent code smells. A rule that is often used is to re-structure code once it is duplicated in three or more places.
Common duplication problems, and corresponding solutions are:
Complex classes like update_db often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes. You can also have a look at the cohesion graph to spot any un-connected, or weakly-connected components.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use update_db, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 10 | class update_db { |
||
| 11 | public static $db_sqlite; |
||
| 12 | |||
| 13 | public static function download($url, $file, $referer = '') { |
||
| 25 | |||
| 26 | public static function gunzip($in_file,$out_file_name = '') { |
||
| 42 | |||
| 43 | public static function unzip($in_file) { |
||
| 54 | |||
| 55 | public static function connect_sqlite($database) { |
||
| 63 | |||
| 64 | public static function retrieve_route_sqlite_to_dest($database_file) { |
||
| 65 | global $globalDebug, $globalTransaction; |
||
| 66 | //$query = 'TRUNCATE TABLE routes'; |
||
| 67 | if ($globalDebug) echo " - Delete previous routes from DB -"; |
||
| 68 | $query = "DELETE FROM routes WHERE Source = '' OR Source = :source"; |
||
| 69 | $Connection = new Connection(); |
||
| 70 | try { |
||
| 71 | //$Connection = new Connection(); |
||
| 72 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 73 | $sth->execute(array(':source' => $database_file)); |
||
| 74 | } catch(PDOException $e) { |
||
| 75 | return "error : ".$e->getMessage(); |
||
| 76 | } |
||
| 77 | |||
| 78 | if ($globalDebug) echo " - Add routes to DB -"; |
||
| 79 | update_db::connect_sqlite($database_file); |
||
| 80 | //$query = 'select Route.RouteID, Route.callsign, operator.Icao AS operator_icao, FromAir.Icao AS FromAirportIcao, ToAir.Icao AS ToAirportIcao from Route inner join operator ON Route.operatorId = operator.operatorId LEFT JOIN Airport AS FromAir ON route.FromAirportId = FromAir.AirportId LEFT JOIN Airport AS ToAir ON ToAir.AirportID = route.ToAirportID'; |
||
| 81 | $query = "select Route.RouteID, Route.callsign, operator.Icao AS operator_icao, FromAir.Icao AS FromAirportIcao, ToAir.Icao AS ToAirportIcao, rstp.allstop AS AllStop from Route inner join operator ON Route.operatorId = operator.operatorId LEFT JOIN Airport AS FromAir ON route.FromAirportId = FromAir.AirportId LEFT JOIN Airport AS ToAir ON ToAir.AirportID = route.ToAirportID LEFT JOIN (select RouteId,GROUP_CONCAT(icao,' ') as allstop from routestop left join Airport as air ON routestop.AirportId = air.AirportID group by RouteID) AS rstp ON Route.RouteID = rstp.RouteID"; |
||
| 82 | try { |
||
| 83 | $sth = update_db::$db_sqlite->prepare($query); |
||
| 84 | $sth->execute(); |
||
| 85 | } catch(PDOException $e) { |
||
| 86 | return "error : ".$e->getMessage(); |
||
| 87 | } |
||
| 88 | //$query_dest = 'INSERT INTO routes (`RouteID`,`CallSign`,`Operator_ICAO`,`FromAirport_ICAO`,`ToAirport_ICAO`,`RouteStop`,`Source`) VALUES (:RouteID, :CallSign, :Operator_ICAO, :FromAirport_ICAO, :ToAirport_ICAO, :routestop, :source)'; |
||
| 89 | $query_dest = 'INSERT INTO routes (CallSign,Operator_ICAO,FromAirport_ICAO,ToAirport_ICAO,RouteStop,Source) VALUES (:CallSign, :Operator_ICAO, :FromAirport_ICAO, :ToAirport_ICAO, :routestop, :source)'; |
||
| 90 | $Connection = new Connection(); |
||
| 91 | $sth_dest = $Connection->db->prepare($query_dest); |
||
|
1 ignored issue
–
show
|
|||
| 92 | try { |
||
| 93 | if ($globalTransaction) $Connection->db->beginTransaction(); |
||
|
1 ignored issue
–
show
|
|||
| 94 | while ($values = $sth->fetch(PDO::FETCH_ASSOC)) { |
||
| 95 | //$query_dest_values = array(':RouteID' => $values['RouteId'],':CallSign' => $values['Callsign'],':Operator_ICAO' => $values['operator_icao'],':FromAirport_ICAO' => $values['FromAirportIcao'],':ToAirport_ICAO' => $values['ToAirportIcao'],':routestop' => $values['AllStop'],':source' => $database_file); |
||
| 96 | $query_dest_values = array(':CallSign' => $values['Callsign'],':Operator_ICAO' => $values['operator_icao'],':FromAirport_ICAO' => $values['FromAirportIcao'],':ToAirport_ICAO' => $values['ToAirportIcao'],':routestop' => $values['AllStop'],':source' => $database_file); |
||
| 97 | $sth_dest->execute($query_dest_values); |
||
| 98 | } |
||
| 99 | if ($globalTransaction) $Connection->db->commit(); |
||
|
1 ignored issue
–
show
|
|||
| 100 | } catch(PDOException $e) { |
||
| 101 | if ($globalTransaction) $Connection->db->rollBack(); |
||
| 102 | return "error : ".$e->getMessage(); |
||
| 103 | } |
||
| 104 | return ''; |
||
| 105 | } |
||
| 106 | public static function retrieve_modes_sqlite_to_dest($database_file) { |
||
| 107 | global $globalTransaction; |
||
| 108 | //$query = 'TRUNCATE TABLE aircraft_modes'; |
||
| 109 | $query = "DELETE FROM aircraft_modes WHERE Source = '' OR Source IS NULL OR Source = :source"; |
||
| 110 | try { |
||
| 111 | $Connection = new Connection(); |
||
| 112 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 113 | $sth->execute(array(':source' => $database_file)); |
||
| 114 | } catch(PDOException $e) { |
||
| 115 | return "error : ".$e->getMessage(); |
||
| 116 | } |
||
| 117 | $query = "DELETE FROM aircraft_owner WHERE Source = '' OR Source IS NULL OR Source = :source"; |
||
| 118 | try { |
||
| 119 | $Connection = new Connection(); |
||
| 120 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 121 | $sth->execute(array(':source' => $database_file)); |
||
| 122 | } catch(PDOException $e) { |
||
| 123 | return "error : ".$e->getMessage(); |
||
| 124 | } |
||
| 125 | |||
| 126 | update_db::connect_sqlite($database_file); |
||
| 127 | $query = 'select * from Aircraft'; |
||
| 128 | try { |
||
| 129 | $sth = update_db::$db_sqlite->prepare($query); |
||
| 130 | $sth->execute(); |
||
| 131 | } catch(PDOException $e) { |
||
| 132 | return "error : ".$e->getMessage(); |
||
| 133 | } |
||
| 134 | //$query_dest = 'INSERT INTO aircraft_modes (`AircraftID`,`FirstCreated`,`LastModified`, `ModeS`,`ModeSCountry`,`Registration`,`ICAOTypeCode`,`SerialNo`, `OperatorFlagCode`, `Manufacturer`, `Type`, `FirstRegDate`, `CurrentRegDate`, `Country`, `PreviousID`, `DeRegDate`, `Status`, `PopularName`,`GenericName`,`AircraftClass`, `Engines`, `OwnershipStatus`,`RegisteredOwners`,`MTOW`, `TotalHours`, `YearBuilt`, `CofACategory`, `CofAExpiry`, `UserNotes`, `Interested`, `UserTag`, `InfoUrl`, `PictureUrl1`, `PictureUrl2`, `PictureUrl3`, `UserBool1`, `UserBool2`, `UserBool3`, `UserBool4`, `UserBool5`, `UserString1`, `UserString2`, `UserString3`, `UserString4`, `UserString5`, `UserInt1`, `UserInt2`, `UserInt3`, `UserInt4`, `UserInt5`) VALUES (:AircraftID,:FirstCreated,:LastModified,:ModeS,:ModeSCountry,:Registration,:ICAOTypeCode,:SerialNo, :OperatorFlagCode, :Manufacturer, :Type, :FirstRegDate, :CurrentRegDate, :Country, :PreviousID, :DeRegDate, :Status, :PopularName,:GenericName,:AircraftClass, :Engines, :OwnershipStatus,:RegisteredOwners,:MTOW, :TotalHours,:YearBuilt, :CofACategory, :CofAExpiry, :UserNotes, :Interested, :UserTag, :InfoUrl, :PictureUrl1, :PictureUrl2, :PictureUrl3, :UserBool1, :UserBool2, :UserBool3, :UserBool4, :UserBool5, :UserString1, :UserString2, :UserString3, :UserString4, :UserString5, :UserInt1, :UserInt2, :UserInt3, :UserInt4, :UserInt5)'; |
||
| 135 | $query_dest = 'INSERT INTO aircraft_modes (LastModified, ModeS,ModeSCountry,Registration,ICAOTypeCode,type_flight,Source) VALUES (:LastModified,:ModeS,:ModeSCountry,:Registration,:ICAOTypeCode,:type,:source)'; |
||
| 136 | |||
| 137 | $query_dest_owner = 'INSERT INTO aircraft_owner (registration,owner,Source) VALUES (:registration,:owner,:source)'; |
||
| 138 | |||
| 139 | $Connection = new Connection(); |
||
| 140 | $sth_dest = $Connection->db->prepare($query_dest); |
||
|
1 ignored issue
–
show
|
|||
| 141 | $sth_dest_owner = $Connection->db->prepare($query_dest_owner); |
||
|
1 ignored issue
–
show
|
|||
| 142 | try { |
||
| 143 | if ($globalTransaction) $Connection->db->beginTransaction(); |
||
|
1 ignored issue
–
show
|
|||
| 144 | while ($values = $sth->fetch(PDO::FETCH_ASSOC)) { |
||
| 145 | //$query_dest_values = array(':AircraftID' => $values['AircraftID'],':FirstCreated' => $values['FirstCreated'],':LastModified' => $values['LastModified'],':ModeS' => $values['ModeS'],':ModeSCountry' => $values['ModeSCountry'],':Registration' => $values['Registration'],':ICAOTypeCode' => $values['ICAOTypeCode'],':SerialNo' => $values['SerialNo'], ':OperatorFlagCode' => $values['OperatorFlagCode'], ':Manufacturer' => $values['Manufacturer'], ':Type' => $values['Type'], ':FirstRegDate' => $values['FirstRegDate'], ':CurrentRegDate' => $values['CurrentRegDate'], ':Country' => $values['Country'], ':PreviousID' => $values['PreviousID'], ':DeRegDate' => $values['DeRegDate'], ':Status' => $values['Status'], ':PopularName' => $values['PopularName'],':GenericName' => $values['GenericName'],':AircraftClass' => $values['AircraftClass'], ':Engines' => $values['Engines'], ':OwnershipStatus' => $values['OwnershipStatus'],':RegisteredOwners' => $values['RegisteredOwners'],':MTOW' => $values['MTOW'], ':TotalHours' => $values['TotalHours'],':YearBuilt' => $values['YearBuilt'], ':CofACategory' => $values['CofACategory'], ':CofAExpiry' => $values['CofAExpiry'], ':UserNotes' => $values['UserNotes'], ':Interested' => $values['Interested'], ':UserTag' => $values['UserTag'], ':InfoUrl' => $values['InfoURL'], ':PictureUrl1' => $values['PictureURL1'], ':PictureUrl2' => $values['PictureURL2'], ':PictureUrl3' => $values['PictureURL3'], ':UserBool1' => $values['UserBool1'], ':UserBool2' => $values['UserBool2'], ':UserBool3' => $values['UserBool3'], ':UserBool4' => $values['UserBool4'], ':UserBool5' => $values['UserBool5'], ':UserString1' => $values['UserString1'], ':UserString2' => $values['UserString2'], ':UserString3' => $values['UserString3'], ':UserString4' => $values['UserString4'], ':UserString5' => $values['UserString5'], ':UserInt1' => $values['UserInt1'], ':UserInt2' => $values['UserInt2'], ':UserInt3' => $values['UserInt3'], ':UserInt4' => $values['UserInt4'], ':UserInt5' => $values['UserInt5']); |
||
| 146 | if ($values['UserString4'] == 'M') $type = 'military'; |
||
| 147 | else $type = null; |
||
| 148 | $query_dest_values = array(':LastModified' => $values['LastModified'],':ModeS' => $values['ModeS'],':ModeSCountry' => $values['ModeSCountry'],':Registration' => $values['Registration'],':ICAOTypeCode' => $values['ICAOTypeCode'],':source' => $database_file,':type' => $type); |
||
| 149 | $sth_dest->execute($query_dest_values); |
||
| 150 | if ($values['RegisteredOwners'] != '' && $values['RegisteredOwners'] != NULL && $values['RegisteredOwners'] != 'Private') { |
||
| 151 | $query_dest_owner_values = array(':registration' => $values['Registration'],':source' => $database_file,':owner' => $values['RegisteredOwners']); |
||
| 152 | $sth_dest_owner->execute($query_dest_owner_values); |
||
| 153 | } |
||
| 154 | } |
||
| 155 | if ($globalTransaction) $Connection->db->commit(); |
||
|
1 ignored issue
–
show
|
|||
| 156 | } catch(PDOException $e) { |
||
| 157 | return "error : ".$e->getMessage(); |
||
| 158 | } |
||
| 159 | |||
| 160 | $query = "DELETE FROM aircraft_modes WHERE Source = :source AND ModeS IN (SELECT * FROM (SELECT ModeS FROM aircraft_modes WHERE Source = 'ACARS') _alias)"; |
||
| 161 | try { |
||
| 162 | $Connection = new Connection(); |
||
| 163 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 164 | $sth->execute(array(':source' => $database_file)); |
||
| 165 | } catch(PDOException $e) { |
||
| 166 | return "error : ".$e->getMessage(); |
||
| 167 | } |
||
| 168 | return ''; |
||
| 169 | } |
||
| 170 | |||
| 171 | public static function retrieve_modes_flarmnet($database_file) { |
||
| 239 | |||
| 240 | public static function retrieve_modes_ogn($database_file) { |
||
| 241 | global $globalTransaction; |
||
| 242 | //$query = 'TRUNCATE TABLE aircraft_modes'; |
||
| 243 | $query = "DELETE FROM aircraft_modes WHERE Source = '' OR Source IS NULL OR Source = :source"; |
||
| 244 | try { |
||
| 245 | $Connection = new Connection(); |
||
| 246 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 247 | $sth->execute(array(':source' => $database_file)); |
||
| 248 | } catch(PDOException $e) { |
||
| 249 | return "error : ".$e->getMessage(); |
||
| 250 | } |
||
| 251 | |||
| 252 | if ($fh = fopen($database_file,"r")) { |
||
| 253 | //$query_dest = 'INSERT INTO aircraft_modes (`AircraftID`,`FirstCreated`,`LastModified`, `ModeS`,`ModeSCountry`,`Registration`,`ICAOTypeCode`,`SerialNo`, `OperatorFlagCode`, `Manufacturer`, `Type`, `FirstRegDate`, `CurrentRegDate`, `Country`, `PreviousID`, `DeRegDate`, `Status`, `PopularName`,`GenericName`,`AircraftClass`, `Engines`, `OwnershipStatus`,`RegisteredOwners`,`MTOW`, `TotalHours`, `YearBuilt`, `CofACategory`, `CofAExpiry`, `UserNotes`, `Interested`, `UserTag`, `InfoUrl`, `PictureUrl1`, `PictureUrl2`, `PictureUrl3`, `UserBool1`, `UserBool2`, `UserBool3`, `UserBool4`, `UserBool5`, `UserString1`, `UserString2`, `UserString3`, `UserString4`, `UserString5`, `UserInt1`, `UserInt2`, `UserInt3`, `UserInt4`, `UserInt5`) VALUES (:AircraftID,:FirstCreated,:LastModified,:ModeS,:ModeSCountry,:Registration,:ICAOTypeCode,:SerialNo, :OperatorFlagCode, :Manufacturer, :Type, :FirstRegDate, :CurrentRegDate, :Country, :PreviousID, :DeRegDate, :Status, :PopularName,:GenericName,:AircraftClass, :Engines, :OwnershipStatus,:RegisteredOwners,:MTOW, :TotalHours,:YearBuilt, :CofACategory, :CofAExpiry, :UserNotes, :Interested, :UserTag, :InfoUrl, :PictureUrl1, :PictureUrl2, :PictureUrl3, :UserBool1, :UserBool2, :UserBool3, :UserBool4, :UserBool5, :UserString1, :UserString2, :UserString3, :UserString4, :UserString5, :UserInt1, :UserInt2, :UserInt3, :UserInt4, :UserInt5)'; |
||
| 254 | $query_dest = 'INSERT INTO aircraft_modes (ModeS,Registration,ICAOTypeCode,Source) VALUES (:ModeS,:Registration,:ICAOTypeCode,:source)'; |
||
| 255 | |||
| 256 | $Connection = new Connection(); |
||
| 257 | $sth_dest = $Connection->db->prepare($query_dest); |
||
|
1 ignored issue
–
show
|
|||
| 258 | try { |
||
| 259 | if ($globalTransaction) $Connection->db->beginTransaction(); |
||
|
1 ignored issue
–
show
|
|||
| 260 | $tmp = fgetcsv($fh,9999,',',"'"); |
||
| 261 | while (!feof($fh)) { |
||
| 262 | $line = fgetcsv($fh,9999,',',"'"); |
||
| 263 | |||
| 264 | //FFFFFF RIDEAU VALLEY SOARINGASW-20 C-FBKN MZ 123.400 |
||
| 265 | //print_r($line); |
||
| 266 | $values['ModeS'] = $line[1]; |
||
| 267 | $values['Registration'] = $line[3]; |
||
| 268 | $aircraft_name = $line[2]; |
||
| 269 | // Check if we can find ICAO, else set it to GLID |
||
| 270 | $aircraft_name_split = explode(' ',$aircraft_name); |
||
| 271 | $search_more = ''; |
||
| 272 | View Code Duplication | if (count($aircraft_name) > 1 && strlen($aircraft_name_split[1]) > 3) $search_more .= " AND LIKE '%".$aircraft_name_split[0]."%'"; |
|
|
1 ignored issue
–
show
|
|||
| 273 | $query_search = "SELECT * FROM aircraft WHERE type LIKE '%".$aircraft_name."%'".$search_more; |
||
| 274 | $sth_search = $Connection->db->prepare($query_search); |
||
|
1 ignored issue
–
show
|
|||
| 275 | try { |
||
| 276 | $sth_search->execute(); |
||
| 277 | $result = $sth_search->fetch(PDO::FETCH_ASSOC); |
||
| 278 | View Code Duplication | if (isset($result['icao']) && $result['icao'] != '') $values['ICAOTypeCode'] = $result['icao']; |
|
|
1 ignored issue
–
show
|
|||
| 279 | } catch(PDOException $e) { |
||
| 280 | return "error : ".$e->getMessage(); |
||
| 281 | } |
||
| 282 | //if (!isset($values['ICAOTypeCode'])) $values['ICAOTypeCode'] = 'GLID'; |
||
| 283 | // Add data to db |
||
| 284 | View Code Duplication | if ($values['ModeS'] != '' && $values['Registration'] != '' && $values['Registration'] != '0000' && $values['ICAOTypeCode'] != '') { |
|
|
1 ignored issue
–
show
|
|||
| 285 | //$query_dest_values = array(':AircraftID' => $values['AircraftID'],':FirstCreated' => $values['FirstCreated'],':LastModified' => $values['LastModified'],':ModeS' => $values['ModeS'],':ModeSCountry' => $values['ModeSCountry'],':Registration' => $values['Registration'],':ICAOTypeCode' => $values['ICAOTypeCode'],':SerialNo' => $values['SerialNo'], ':OperatorFlagCode' => $values['OperatorFlagCode'], ':Manufacturer' => $values['Manufacturer'], ':Type' => $values['Type'], ':FirstRegDate' => $values['FirstRegDate'], ':CurrentRegDate' => $values['CurrentRegDate'], ':Country' => $values['Country'], ':PreviousID' => $values['PreviousID'], ':DeRegDate' => $values['DeRegDate'], ':Status' => $values['Status'], ':PopularName' => $values['PopularName'],':GenericName' => $values['GenericName'],':AircraftClass' => $values['AircraftClass'], ':Engines' => $values['Engines'], ':OwnershipStatus' => $values['OwnershipStatus'],':RegisteredOwners' => $values['RegisteredOwners'],':MTOW' => $values['MTOW'], ':TotalHours' => $values['TotalHours'],':YearBuilt' => $values['YearBuilt'], ':CofACategory' => $values['CofACategory'], ':CofAExpiry' => $values['CofAExpiry'], ':UserNotes' => $values['UserNotes'], ':Interested' => $values['Interested'], ':UserTag' => $values['UserTag'], ':InfoUrl' => $values['InfoURL'], ':PictureUrl1' => $values['PictureURL1'], ':PictureUrl2' => $values['PictureURL2'], ':PictureUrl3' => $values['PictureURL3'], ':UserBool1' => $values['UserBool1'], ':UserBool2' => $values['UserBool2'], ':UserBool3' => $values['UserBool3'], ':UserBool4' => $values['UserBool4'], ':UserBool5' => $values['UserBool5'], ':UserString1' => $values['UserString1'], ':UserString2' => $values['UserString2'], ':UserString3' => $values['UserString3'], ':UserString4' => $values['UserString4'], ':UserString5' => $values['UserString5'], ':UserInt1' => $values['UserInt1'], ':UserInt2' => $values['UserInt2'], ':UserInt3' => $values['UserInt3'], ':UserInt4' => $values['UserInt4'], ':UserInt5' => $values['UserInt5']); |
||
| 286 | $query_dest_values = array(':ModeS' => $values['ModeS'],':Registration' => $values['Registration'],':ICAOTypeCode' => $values['ICAOTypeCode'],':source' => $database_file); |
||
| 287 | //print_r($query_dest_values); |
||
| 288 | $sth_dest->execute($query_dest_values); |
||
| 289 | } |
||
| 290 | } |
||
| 291 | if ($globalTransaction) $Connection->db->commit(); |
||
|
1 ignored issue
–
show
|
|||
| 292 | } catch(PDOException $e) { |
||
| 293 | return "error : ".$e->getMessage(); |
||
| 294 | } |
||
| 295 | } |
||
| 296 | |||
| 297 | $query = "DELETE FROM aircraft_modes WHERE Source = :source AND ModeS IN (SELECT * FROM (SELECT ModeS FROM aircraft_modes WHERE Source = 'ACARS') _alias)"; |
||
| 298 | try { |
||
| 299 | $Connection = new Connection(); |
||
| 300 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 301 | $sth->execute(array(':source' => $database_file)); |
||
| 302 | } catch(PDOException $e) { |
||
| 303 | return "error : ".$e->getMessage(); |
||
| 304 | } |
||
| 305 | return ''; |
||
| 306 | } |
||
| 307 | |||
| 308 | public static function retrieve_owner($database_file,$country = 'F') { |
||
| 309 | global $globalTransaction; |
||
| 310 | //$query = 'TRUNCATE TABLE aircraft_modes'; |
||
| 311 | $query = "DELETE FROM aircraft_owner WHERE Source = '' OR Source IS NULL OR Source = :source"; |
||
| 312 | try { |
||
| 313 | $Connection = new Connection(); |
||
| 314 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 315 | $sth->execute(array(':source' => $database_file)); |
||
| 316 | } catch(PDOException $e) { |
||
| 317 | return "error : ".$e->getMessage(); |
||
| 318 | } |
||
| 319 | |||
| 320 | if ($fh = fopen($database_file,"r")) { |
||
| 321 | //$query_dest = 'INSERT INTO aircraft_modes (`AircraftID`,`FirstCreated`,`LastModified`, `ModeS`,`ModeSCountry`,`Registration`,`ICAOTypeCode`,`SerialNo`, `OperatorFlagCode`, `Manufacturer`, `Type`, `FirstRegDate`, `CurrentRegDate`, `Country`, `PreviousID`, `DeRegDate`, `Status`, `PopularName`,`GenericName`,`AircraftClass`, `Engines`, `OwnershipStatus`,`RegisteredOwners`,`MTOW`, `TotalHours`, `YearBuilt`, `CofACategory`, `CofAExpiry`, `UserNotes`, `Interested`, `UserTag`, `InfoUrl`, `PictureUrl1`, `PictureUrl2`, `PictureUrl3`, `UserBool1`, `UserBool2`, `UserBool3`, `UserBool4`, `UserBool5`, `UserString1`, `UserString2`, `UserString3`, `UserString4`, `UserString5`, `UserInt1`, `UserInt2`, `UserInt3`, `UserInt4`, `UserInt5`) VALUES (:AircraftID,:FirstCreated,:LastModified,:ModeS,:ModeSCountry,:Registration,:ICAOTypeCode,:SerialNo, :OperatorFlagCode, :Manufacturer, :Type, :FirstRegDate, :CurrentRegDate, :Country, :PreviousID, :DeRegDate, :Status, :PopularName,:GenericName,:AircraftClass, :Engines, :OwnershipStatus,:RegisteredOwners,:MTOW, :TotalHours,:YearBuilt, :CofACategory, :CofAExpiry, :UserNotes, :Interested, :UserTag, :InfoUrl, :PictureUrl1, :PictureUrl2, :PictureUrl3, :UserBool1, :UserBool2, :UserBool3, :UserBool4, :UserBool5, :UserString1, :UserString2, :UserString3, :UserString4, :UserString5, :UserInt1, :UserInt2, :UserInt3, :UserInt4, :UserInt5)'; |
||
| 322 | $query_dest = 'INSERT INTO aircraft_owner (registration,base,owner,date_first_reg,Source) VALUES (:registration,:base,:owner,:date_first_reg,:source)'; |
||
| 323 | |||
| 324 | $Connection = new Connection(); |
||
| 325 | $sth_dest = $Connection->db->prepare($query_dest); |
||
|
1 ignored issue
–
show
|
|||
| 326 | try { |
||
| 327 | if ($globalTransaction) $Connection->db->beginTransaction(); |
||
|
1 ignored issue
–
show
|
|||
| 328 | $tmp = fgetcsv($fh,9999,',','"'); |
||
| 329 | while (!feof($fh)) { |
||
| 330 | $line = fgetcsv($fh,9999,',','"'); |
||
| 331 | //print_r($line); |
||
| 332 | if ($country == 'F') { |
||
| 333 | $values['registration'] = $line[0]; |
||
| 334 | $values['base'] = $line[4]; |
||
| 335 | $values['owner'] = $line[5]; |
||
| 336 | if ($line[6] == '') $values['date_first_reg'] = null; |
||
| 337 | else $values['date_first_reg'] = date("Y-m-d",strtotime($line[6])); |
||
| 338 | $values['cancel'] = $line[7]; |
||
| 339 | View Code Duplication | } elseif ($country == 'EI') { |
|
|
1 ignored issue
–
show
|
|||
| 340 | // TODO : add modeS & reg to aircraft_modes |
||
| 341 | $values['registration'] = $line[0]; |
||
| 342 | $values['base'] = $line[3]; |
||
| 343 | $values['owner'] = $line[2]; |
||
| 344 | if ($line[1] == '') $values['date_first_reg'] = null; |
||
| 345 | else $values['date_first_reg'] = date("Y-m-d",strtotime($line[1])); |
||
| 346 | $values['cancel'] = ''; |
||
| 347 | } elseif ($country == 'HB') { |
||
| 348 | // TODO : add modeS & reg to aircraft_modes |
||
| 349 | $values['registration'] = $line[0]; |
||
| 350 | $values['base'] = null; |
||
| 351 | $values['owner'] = $line[5]; |
||
| 352 | $values['date_first_reg'] = null; |
||
| 353 | $values['cancel'] = ''; |
||
| 354 | View Code Duplication | } elseif ($country == 'OK') { |
|
|
1 ignored issue
–
show
|
|||
| 355 | // TODO : add modeS & reg to aircraft_modes |
||
| 356 | $values['registration'] = $line[3]; |
||
| 357 | $values['base'] = null; |
||
| 358 | $values['owner'] = $line[5]; |
||
| 359 | if ($line[18] == '') $values['date_first_reg'] = null; |
||
| 360 | else $values['date_first_reg'] = date("Y-m-d",strtotime($line[18])); |
||
| 361 | $values['cancel'] = ''; |
||
| 362 | } elseif ($country == 'VH') { |
||
| 363 | // TODO : add modeS & reg to aircraft_modes |
||
| 364 | $values['registration'] = $line[0]; |
||
| 365 | $values['base'] = null; |
||
| 366 | $values['owner'] = $line[12]; |
||
| 367 | if ($line[28] == '') $values['date_first_reg'] = null; |
||
| 368 | else $values['date_first_reg'] = date("Y-m-d",strtotime($line[28])); |
||
| 369 | |||
| 370 | $values['cancel'] = $line[39]; |
||
| 371 | } elseif ($country == 'OE' || $country == '9A' || $country == 'VP' || $country == 'LX' || $country == 'P2' || $country == 'HC') { |
||
| 372 | $values['registration'] = $line[0]; |
||
| 373 | $values['base'] = null; |
||
| 374 | $values['owner'] = $line[4]; |
||
| 375 | $values['date_first_reg'] = null; |
||
| 376 | $values['cancel'] = ''; |
||
| 377 | } elseif ($country == 'CC') { |
||
| 378 | $values['registration'] = $line[0]; |
||
| 379 | $values['base'] = null; |
||
| 380 | $values['owner'] = $line[6]; |
||
| 381 | $values['date_first_reg'] = null; |
||
| 382 | $values['cancel'] = ''; |
||
| 383 | View Code Duplication | } elseif ($country == 'HJ') { |
|
|
1 ignored issue
–
show
|
|||
| 384 | $values['registration'] = $line[0]; |
||
| 385 | $values['base'] = null; |
||
| 386 | $values['owner'] = $line[8]; |
||
| 387 | if ($line[7] == '') $values['date_first_reg'] = null; |
||
| 388 | else $values['date_first_reg'] = date("Y-m-d",strtotime($line[7])); |
||
| 389 | $values['cancel'] = ''; |
||
| 390 | } elseif ($country == 'PP') { |
||
| 391 | $values['registration'] = $line[0]; |
||
| 392 | $values['base'] = null; |
||
| 393 | $values['owner'] = $line[4]; |
||
| 394 | if ($line[6] == '') $values['date_first_reg'] = null; |
||
| 395 | else $values['date_first_reg'] = date("Y-m-d",strtotime($line[6])); |
||
| 396 | $values['cancel'] = $line[7]; |
||
| 397 | View Code Duplication | } elseif ($country == 'E7') { |
|
|
1 ignored issue
–
show
|
|||
| 398 | $values['registration'] = $line[0]; |
||
| 399 | $values['base'] = null; |
||
| 400 | $values['owner'] = $line[4]; |
||
| 401 | if ($line[5] == '') $values['date_first_reg'] = null; |
||
| 402 | else $values['date_first_reg'] = date("Y-m-d",strtotime($line[5])); |
||
| 403 | $values['cancel'] = ''; |
||
| 404 | } elseif ($country == '8Q') { |
||
| 405 | $values['registration'] = $line[0]; |
||
| 406 | $values['base'] = null; |
||
| 407 | $values['owner'] = $line[3]; |
||
| 408 | if ($line[7] == '') $values['date_first_reg'] = null; |
||
| 409 | else $values['date_first_reg'] = date("Y-m-d",strtotime($line[7])); |
||
| 410 | $values['cancel'] = ''; |
||
| 411 | } elseif ($country == 'ZK' || $country == 'OM' || $country == 'TF') { |
||
| 412 | $values['registration'] = $line[0]; |
||
| 413 | $values['base'] = null; |
||
| 414 | $values['owner'] = $line[3]; |
||
| 415 | $values['date_first_reg'] = null; |
||
| 416 | $values['cancel'] = ''; |
||
| 417 | } |
||
| 418 | if ($values['cancel'] == '' && $values['registration'] != null) { |
||
| 419 | $query_dest_values = array(':registration' => $values['registration'],':base' => $values['base'],':date_first_reg' => $values['date_first_reg'],':owner' => $values['owner'],':source' => $database_file); |
||
| 420 | $sth_dest->execute($query_dest_values); |
||
| 421 | } |
||
| 422 | } |
||
| 423 | if ($globalTransaction) $Connection->db->commit(); |
||
|
1 ignored issue
–
show
|
|||
| 424 | } catch(PDOException $e) { |
||
| 425 | return "error : ".$e->getMessage(); |
||
| 426 | } |
||
| 427 | } |
||
| 428 | return ''; |
||
| 429 | } |
||
| 430 | |||
| 431 | /* |
||
| 432 | * This function is used to create a list of airports. Sources : Wikipedia, ourairports.com ans partow.net |
||
| 433 | */ |
||
| 434 | public static function update_airports() { |
||
| 435 | global $tmp_dir, $globalTransaction; |
||
| 436 | |||
| 437 | require_once(dirname(__FILE__).'/libs/sparqllib.php'); |
||
| 438 | $db = sparql_connect('http://dbpedia.org/sparql'); |
||
| 439 | $query = ' |
||
| 440 | PREFIX dbo: <http://dbpedia.org/ontology/> |
||
| 441 | PREFIX dbp: <http://dbpedia.org/property/> |
||
| 442 | PREFIX geo: <http://www.w3.org/2003/01/geo/wgs84_pos#> |
||
| 443 | PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> |
||
| 444 | PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> |
||
| 445 | SELECT ?name ?icao ?iata ?faa ?lid ?latitude ?longitude ?airport ?homepage ?type ?country ?country_bis ?altitude ?image |
||
| 446 | FROM <http://dbpedia.org> |
||
| 447 | WHERE { |
||
| 448 | ?airport rdf:type <http://dbpedia.org/ontology/Airport> . |
||
| 449 | |||
| 450 | OPTIONAL { |
||
| 451 | ?airport dbo:icaoLocationIdentifier ?icao . |
||
| 452 | FILTER regex(?icao, "^[A-Z0-9]{4}$") |
||
| 453 | } |
||
| 454 | |||
| 455 | OPTIONAL { |
||
| 456 | ?airport dbo:iataLocationIdentifier ?iata . |
||
| 457 | FILTER regex(?iata, "^[A-Z0-9]{3}$") |
||
| 458 | } |
||
| 459 | |||
| 460 | OPTIONAL { |
||
| 461 | ?airport dbo:locationIdentifier ?lid . |
||
| 462 | FILTER regex(?lid, "^[A-Z0-9]{4}$") |
||
| 463 | FILTER (!bound(?icao) || (bound(?icao) && (?icao != ?lid))) |
||
| 464 | OPTIONAL { |
||
| 465 | ?airport_y rdf:type <http://dbpedia.org/ontology/Airport> . |
||
| 466 | ?airport_y dbo:icaoLocationIdentifier ?other_icao . |
||
| 467 | FILTER (bound(?lid) && (?airport_y != ?airport && ?lid = ?other_icao)) |
||
| 468 | } |
||
| 469 | FILTER (!bound(?other_icao)) |
||
| 470 | } |
||
| 471 | |||
| 472 | OPTIONAL { |
||
| 473 | ?airport dbo:faaLocationIdentifier ?faa . |
||
| 474 | FILTER regex(?faa, "^[A-Z0-9]{3}$") |
||
| 475 | FILTER (!bound(?iata) || (bound(?iata) && (?iata != ?faa))) |
||
| 476 | OPTIONAL { |
||
| 477 | ?airport_x rdf:type <http://dbpedia.org/ontology/Airport> . |
||
| 478 | ?airport_x dbo:iataLocationIdentifier ?other_iata . |
||
| 479 | FILTER (bound(?faa) && (?airport_x != ?airport && ?faa = ?other_iata)) |
||
| 480 | } |
||
| 481 | FILTER (!bound(?other_iata)) |
||
| 482 | } |
||
| 483 | |||
| 484 | FILTER (bound(?icao) || bound(?iata) || bound(?faa) || bound(?lid)) |
||
| 485 | |||
| 486 | OPTIONAL { |
||
| 487 | ?airport rdfs:label ?name |
||
| 488 | FILTER (lang(?name) = "en") |
||
| 489 | } |
||
| 490 | |||
| 491 | OPTIONAL { |
||
| 492 | ?airport foaf:homepage ?homepage |
||
| 493 | } |
||
| 494 | |||
| 495 | OPTIONAL { |
||
| 496 | ?airport dbp:coordinatesRegion ?country |
||
| 497 | } |
||
| 498 | |||
| 499 | OPTIONAL { |
||
| 500 | ?airport dbp:type ?type |
||
| 501 | } |
||
| 502 | |||
| 503 | OPTIONAL { |
||
| 504 | ?airport dbo:elevation ?altitude |
||
| 505 | } |
||
| 506 | OPTIONAL { |
||
| 507 | ?airport dbp:image ?image |
||
| 508 | } |
||
| 509 | |||
| 510 | { |
||
| 511 | ?airport geo:lat ?latitude . |
||
| 512 | ?airport geo:long ?longitude . |
||
| 513 | FILTER (datatype(?latitude) = xsd:float) |
||
| 514 | FILTER (datatype(?longitude) = xsd:float) |
||
| 515 | } UNION { |
||
| 516 | ?airport geo:lat ?latitude . |
||
| 517 | ?airport geo:long ?longitude . |
||
| 518 | FILTER (datatype(?latitude) = xsd:double) |
||
| 519 | FILTER (datatype(?longitude) = xsd:double) |
||
| 520 | OPTIONAL { |
||
| 521 | ?airport geo:lat ?lat_f . |
||
| 522 | ?airport geo:long ?long_f . |
||
| 523 | FILTER (datatype(?lat_f) = xsd:float) |
||
| 524 | FILTER (datatype(?long_f) = xsd:float) |
||
| 525 | } |
||
| 526 | FILTER (!bound(?lat_f) && !bound(?long_f)) |
||
| 527 | } |
||
| 528 | |||
| 529 | } |
||
| 530 | ORDER BY ?airport |
||
| 531 | '; |
||
| 532 | $result = sparql_query($query); |
||
| 533 | |||
| 534 | $query = 'TRUNCATE TABLE airport'; |
||
| 535 | try { |
||
| 536 | $Connection = new Connection(); |
||
| 537 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 538 | $sth->execute(); |
||
| 539 | } catch(PDOException $e) { |
||
| 540 | return "error : ".$e->getMessage(); |
||
| 541 | } |
||
| 542 | |||
| 543 | |||
| 544 | $query = 'ALTER TABLE airport DROP INDEX icaoidx'; |
||
| 545 | try { |
||
| 546 | $Connection = new Connection(); |
||
| 547 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 548 | $sth->execute(); |
||
| 549 | } catch(PDOException $e) { |
||
| 550 | return "error : ".$e->getMessage(); |
||
| 551 | } |
||
| 552 | |||
| 553 | $query_dest = "INSERT INTO airport (`airport_id`,`name`,`city`,`country`,`iata`,`icao`,`latitude`,`longitude`,`altitude`,`type`,`home_link`,`wikipedia_link`,`image_thumb`,`image`) |
||
| 554 | VALUES (:airport_id, :name, :city, :country, :iata, :icao, :latitude, :longitude, :altitude, :type, :home_link, :wikipedia_link, :image_thumb, :image)"; |
||
| 555 | $Connection = new Connection(); |
||
| 556 | $sth_dest = $Connection->db->prepare($query_dest); |
||
|
1 ignored issue
–
show
|
|||
| 557 | if ($globalTransaction) $Connection->db->beginTransaction(); |
||
|
1 ignored issue
–
show
|
|||
| 558 | |||
| 559 | $i = 0; |
||
| 560 | while($row = sparql_fetch_array($result)) |
||
| 561 | { |
||
| 562 | if ($i >= 1) { |
||
| 563 | //print_r($row); |
||
| 564 | if (!isset($row['iata'])) $row['iata'] = ''; |
||
| 565 | if (!isset($row['icao'])) $row['icao'] = ''; |
||
| 566 | if (!isset($row['type'])) $row['type'] = ''; |
||
| 567 | if (!isset($row['altitude'])) $row['altitude'] = ''; |
||
| 568 | if (isset($row['city_bis'])) { |
||
| 569 | $row['city'] = $row['city_bis']; |
||
| 570 | } |
||
| 571 | if (!isset($row['city'])) $row['city'] = ''; |
||
| 572 | if (!isset($row['country'])) $row['country'] = ''; |
||
| 573 | if (!isset($row['homepage'])) $row['homepage'] = ''; |
||
| 574 | if (!isset($row['wikipedia_page'])) $row['wikipedia_page'] = ''; |
||
| 575 | if (!isset($row['name'])) continue; |
||
| 576 | if (!isset($row['image'])) { |
||
| 577 | $row['image'] = ''; |
||
| 578 | $row['image_thumb'] = ''; |
||
| 579 | } else { |
||
| 580 | $image = str_replace(' ','_',$row['image']); |
||
| 581 | $digest = md5($image); |
||
| 582 | $folder = $digest[0] . '/' . $digest[0] . $digest[1] . '/' . $image . '/220px-' . $image; |
||
| 583 | $row['image_thumb'] = 'http://upload.wikimedia.org/wikipedia/commons/thumb/' . $folder; |
||
| 584 | $folder = $digest[0] . '/' . $digest[0] . $digest[1] . '/' . $image; |
||
| 585 | $row['image'] = 'http://upload.wikimedia.org/wikipedia/commons/' . $folder; |
||
| 586 | } |
||
| 587 | |||
| 588 | $country = explode('-',$row['country']); |
||
| 589 | $row['country'] = $country[0]; |
||
| 590 | |||
| 591 | $row['type'] = trim($row['type']); |
||
| 592 | if ($row['type'] == 'Military: Naval Auxiliary Air Station' || $row['type'] == 'http://dbpedia.org/resource/Naval_air_station' || $row['type'] == 'Military: Naval Air Station' || $row['type'] == 'Military Northern Fleet' || $row['type'] == 'Military and industrial' || $row['type'] == 'Military: Royal Air Force station' || $row['type'] == 'http://dbpedia.org/resource/Military_airbase' || $row['type'] == 'Military: Naval air station' || preg_match('/air base/i',$row['name'])) { |
||
| 593 | $row['type'] = 'Military'; |
||
| 594 | } elseif ($row['type'] == 'http://dbpedia.org/resource/Airport' || $row['type'] == 'Civil' || $row['type'] == 'Public use' || $row['type'] == 'Public' || $row['type'] == 'http://dbpedia.org/resource/Civilian' || $row['type'] == 'Public, Civilian' || $row['type'] == 'Public / Military' || $row['type'] == 'Private & Civilian' || $row['type'] == 'Civilian and Military' || $row['type'] == 'Public/military' || $row['type'] == 'Active With Few Facilities' || $row['type'] == '?ivilian' || $row['type'] == 'Civil/Military' || $row['type'] == 'NA' || $row['type'] == 'Public/Military') { |
||
| 595 | $row['type'] = 'small_airport'; |
||
| 596 | } |
||
| 597 | |||
| 598 | $row['city'] = urldecode(str_replace('_',' ',str_replace('http://dbpedia.org/resource/','',$row['city']))); |
||
| 599 | $query_dest_values = array(':airport_id' => $i, ':name' => $row['name'],':iata' => $row['iata'],':icao' => $row['icao'],':latitude' => $row['latitude'],':longitude' => $row['longitude'],':altitude' => $row['altitude'],':type' => $row['type'],':city' => $row['city'],':country' => $row['country'],':home_link' => $row['homepage'],':wikipedia_link' => $row['wikipedia_page'],':image' => $row['image'],':image_thumb' => $row['image_thumb']); |
||
| 600 | //print_r($query_dest_values); |
||
| 601 | |||
| 602 | try { |
||
| 603 | $sth_dest->execute($query_dest_values); |
||
| 604 | } catch(PDOException $e) { |
||
| 605 | return "error : ".$e->getMessage(); |
||
| 606 | } |
||
| 607 | } |
||
| 608 | |||
| 609 | $i++; |
||
| 610 | } |
||
| 611 | if ($globalTransaction) $Connection->db->commit(); |
||
|
1 ignored issue
–
show
|
|||
| 612 | echo "Delete duplicate rows...\n"; |
||
| 613 | $query = 'ALTER IGNORE TABLE airport ADD UNIQUE INDEX icaoidx (icao)'; |
||
| 614 | try { |
||
| 615 | $Connection = new Connection(); |
||
| 616 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 617 | $sth->execute(); |
||
| 618 | } catch(PDOException $e) { |
||
| 619 | return "error : ".$e->getMessage(); |
||
| 620 | } |
||
| 621 | |||
| 622 | |||
| 623 | if ($globalDebug) echo "Insert Not available Airport...\n"; |
||
| 624 | $query = "INSERT INTO airport (`airport_id`,`name`,`city`,`country`,`iata`,`icao`,`latitude`,`longitude`,`altitude`,`type`,`home_link`,`wikipedia_link`,`image`,`image_thumb`) |
||
| 625 | VALUES (:airport_id, :name, :city, :country, :iata, :icao, :latitude, :longitude, :altitude, :type, :home_link, :wikipedia_link, :image, :image_thumb)"; |
||
| 626 | $query_values = array(':airport_id' => $i, ':name' => 'Not available',':iata' => 'NA',':icao' => 'NA',':latitude' => '0',':longitude' => '0',':altitude' => '0',':type' => 'NA',':city' => 'N/A',':country' => 'N/A',':home_link' => '',':wikipedia_link' => '',':image' => '',':image_thumb' => ''); |
||
| 627 | try { |
||
| 628 | $Connection = new Connection(); |
||
| 629 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 630 | $sth->execute($query_values); |
||
| 631 | } catch(PDOException $e) { |
||
| 632 | return "error : ".$e->getMessage(); |
||
| 633 | } |
||
| 634 | $i++; |
||
| 635 | /* |
||
| 636 | $query = 'DELETE FROM airport WHERE airport_id IN (SELECT * FROM (SELECT min(a.airport_id) FROM airport a GROUP BY a.icao) x)'; |
||
| 637 | try { |
||
| 638 | $Connection = new Connection(); |
||
| 639 | $sth = $Connection->db->prepare($query); |
||
| 640 | $sth->execute(); |
||
| 641 | } catch(PDOException $e) { |
||
| 642 | return "error : ".$e->getMessage(); |
||
| 643 | } |
||
| 644 | */ |
||
| 645 | |||
| 646 | echo "Download data from ourairports.com...\n"; |
||
| 647 | $delimiter = ','; |
||
| 648 | $out_file = $tmp_dir.'airports.csv'; |
||
| 649 | update_db::download('http://ourairports.com/data/airports.csv',$out_file); |
||
| 650 | if (!file_exists($out_file) || !is_readable($out_file)) return FALSE; |
||
| 651 | echo "Add data from ourairports.com...\n"; |
||
| 652 | |||
| 653 | $header = NULL; |
||
| 654 | if (($handle = fopen($out_file, 'r')) !== FALSE) |
||
| 655 | { |
||
| 656 | $Connection = new Connection(); |
||
| 657 | //$Connection->db->beginTransaction(); |
||
| 658 | while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE) |
||
| 659 | { |
||
| 660 | if(!$header) $header = $row; |
||
| 661 | else { |
||
| 662 | $data = array(); |
||
| 663 | $data = array_combine($header, $row); |
||
| 664 | try { |
||
| 665 | $sth = $Connection->db->prepare('SELECT COUNT(*) FROM airport WHERE `icao` = :icao'); |
||
|
1 ignored issue
–
show
|
|||
| 666 | $sth->execute(array(':icao' => $data['gps_code'])); |
||
| 667 | } catch(PDOException $e) { |
||
| 668 | return "error : ".$e->getMessage(); |
||
| 669 | } |
||
| 670 | if ($sth->fetchColumn() > 0) { |
||
| 671 | $query = 'UPDATE airport SET `type` = :type WHERE icao = :icao'; |
||
| 672 | try { |
||
| 673 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 674 | $sth->execute(array(':icao' => $data['gps_code'],':type' => $data['type'])); |
||
| 675 | } catch(PDOException $e) { |
||
| 676 | return "error : ".$e->getMessage(); |
||
| 677 | } |
||
| 678 | } else { |
||
| 679 | $query = "INSERT INTO airport (`airport_id`,`name`,`city`,`country`,`iata`,`icao`,`latitude`,`longitude`,`altitude`,`type`,`home_link`,`wikipedia_link`) |
||
| 680 | VALUES (:airport_id, :name, :city, :country, :iata, :icao, :latitude, :longitude, :altitude, :type, :home_link, :wikipedia_link)"; |
||
| 681 | $query_values = array(':airport_id' => $i, ':name' => $data['name'],':iata' => $data['iata_code'],':icao' => $data['gps_code'],':latitude' => $data['latitude_deg'],':longitude' => $data['longitude_deg'],':altitude' => $data['elevation_ft'],':type' => $data['type'],':city' => $data['municipality'],':country' => $data['iso_country'],':home_link' => $data['home_link'],':wikipedia_link' => $data['wikipedia_link']); |
||
| 682 | try { |
||
| 683 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 684 | $sth->execute($query_values); |
||
| 685 | } catch(PDOException $e) { |
||
| 686 | return "error : ".$e->getMessage(); |
||
| 687 | } |
||
| 688 | $i++; |
||
| 689 | } |
||
| 690 | } |
||
| 691 | } |
||
| 692 | fclose($handle); |
||
| 693 | //$Connection->db->commit(); |
||
| 694 | } |
||
| 695 | |||
| 696 | echo "Download data from another free database...\n"; |
||
| 697 | $out_file = $tmp_dir.'GlobalAirportDatabase.zip'; |
||
| 698 | update_db::download('http://www.partow.net/downloads/GlobalAirportDatabase.zip',$out_file); |
||
| 699 | if (!file_exists($out_file) || !is_readable($out_file)) return FALSE; |
||
| 700 | update_db::unzip($out_file); |
||
| 701 | $header = NULL; |
||
| 702 | echo "Add data from another free database...\n"; |
||
| 703 | $delimiter = ':'; |
||
| 704 | $Connection = new Connection(); |
||
| 705 | if (($handle = fopen($tmp_dir.'GlobalAirportDatabase.txt', 'r')) !== FALSE) |
||
| 706 | { |
||
| 707 | //$Connection->db->beginTransaction(); |
||
| 708 | while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE) |
||
| 709 | { |
||
| 710 | if(!$header) $header = $row; |
||
| 711 | else { |
||
| 712 | $data = $row; |
||
| 713 | |||
| 714 | $query = 'UPDATE airport SET `city` = :city, `country` = :country WHERE icao = :icao'; |
||
| 715 | try { |
||
| 716 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 717 | $sth->execute(array(':icao' => $data[0],':city' => ucwords(strtolower($data[3])),':country' => ucwords(strtolower($data[4])))); |
||
| 718 | } catch(PDOException $e) { |
||
| 719 | return "error : ".$e->getMessage(); |
||
| 720 | } |
||
| 721 | } |
||
| 722 | } |
||
| 723 | fclose($handle); |
||
| 724 | //$Connection->db->commit(); |
||
| 725 | } |
||
| 726 | |||
| 727 | echo "Put type military for all air base"; |
||
| 728 | $Connection = new Connection(); |
||
| 729 | try { |
||
| 730 | $sth = $Connection->db->prepare("SELECT icao FROM airport WHERE `name` LIKE '%Air Base%'"); |
||
|
1 ignored issue
–
show
|
|||
| 731 | $sth->execute(); |
||
| 732 | } catch(PDOException $e) { |
||
| 733 | return "error : ".$e->getMessage(); |
||
| 734 | } |
||
| 735 | while ($row = $sth->fetch(PDO::FETCH_ASSOC)) { |
||
| 736 | $query2 = 'UPDATE airport SET `type` = :type WHERE icao = :icao'; |
||
| 737 | try { |
||
| 738 | $sth2 = $Connection->db->prepare($query2); |
||
|
1 ignored issue
–
show
|
|||
| 739 | $sth2->execute(array(':icao' => $row['icao'],':type' => 'military')); |
||
| 740 | } catch(PDOException $e) { |
||
| 741 | return "error : ".$e->getMessage(); |
||
| 742 | } |
||
| 743 | } |
||
| 744 | |||
| 745 | |||
| 746 | |||
| 747 | return "success"; |
||
| 748 | } |
||
| 749 | |||
| 750 | public static function translation() { |
||
| 751 | require_once(dirname(__FILE__).'/../require/class.Spotter.php'); |
||
| 752 | global $tmp_dir, $globalTransaction; |
||
| 753 | $Spotter = new Spotter(); |
||
| 754 | //$out_file = $tmp_dir.'translation.zip'; |
||
| 755 | //update_db::download('http://www.acarsd.org/download/translation.php',$out_file); |
||
| 756 | //if (!file_exists($out_file) || !is_readable($out_file)) return FALSE; |
||
| 757 | |||
| 758 | //$query = 'TRUNCATE TABLE translation'; |
||
| 759 | $query = "DELETE FROM translation WHERE Source = '' OR Source = :source"; |
||
| 760 | try { |
||
| 761 | $Connection = new Connection(); |
||
| 762 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 763 | $sth->execute(array(':source' => 'translation.csv')); |
||
| 764 | } catch(PDOException $e) { |
||
| 765 | return "error : ".$e->getMessage(); |
||
| 766 | } |
||
| 767 | |||
| 768 | |||
| 769 | //update_db::unzip($out_file); |
||
| 770 | $header = NULL; |
||
| 771 | $delimiter = ';'; |
||
| 772 | $Connection = new Connection(); |
||
| 773 | if (($handle = fopen($tmp_dir.'translation.csv', 'r')) !== FALSE) |
||
| 774 | { |
||
| 775 | $i = 0; |
||
| 776 | //$Connection->db->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE); |
||
| 777 | //$Connection->db->beginTransaction(); |
||
| 778 | while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE) |
||
| 779 | { |
||
| 780 | $i++; |
||
| 781 | if($i > 12) { |
||
| 782 | $data = $row; |
||
| 783 | $operator = $data[2]; |
||
| 784 | View Code Duplication | if ($operator != '' && is_numeric(substr(substr($operator, 0, 3), -1, 1))) { |
|
|
1 ignored issue
–
show
|
|||
| 785 | $airline_array = $Spotter->getAllAirlineInfo(substr($operator, 0, 2)); |
||
| 786 | //echo substr($operator, 0, 2)."\n";; |
||
| 787 | if (count($airline_array) > 0) { |
||
| 788 | //print_r($airline_array); |
||
| 789 | $operator = $airline_array[0]['icao'].substr($operator,2); |
||
| 790 | } |
||
| 791 | } |
||
| 792 | |||
| 793 | $operator_correct = $data[3]; |
||
| 794 | View Code Duplication | if ($operator_correct != '' && is_numeric(substr(substr($operator_correct, 0, 3), -1, 1))) { |
|
|
1 ignored issue
–
show
|
|||
| 795 | $airline_array = $Spotter->getAllAirlineInfo(substr($operator_correct, 0, 2)); |
||
| 796 | if (count($airline_array) > 0) { |
||
| 797 | $operator_correct = $airline_array[0]['icao'].substr($operator_correct,2); |
||
| 798 | } |
||
| 799 | } |
||
| 800 | $query = 'INSERT INTO translation (Reg,Reg_correct,Operator,Operator_correct,Source) VALUES (:Reg, :Reg_correct, :Operator, :Operator_correct, :source)'; |
||
| 801 | try { |
||
| 802 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 803 | $sth->execute(array(':Reg' => $data[0],':Reg_correct' => $data[1],':Operator' => $operator,':Operator_correct' => $operator_correct, ':source' => 'translation.csv')); |
||
| 804 | } catch(PDOException $e) { |
||
| 805 | return "error : ".$e->getMessage(); |
||
| 806 | } |
||
| 807 | } |
||
| 808 | } |
||
| 809 | fclose($handle); |
||
| 810 | //$Connection->db->commit(); |
||
| 811 | } |
||
| 812 | return ''; |
||
| 813 | } |
||
| 814 | |||
| 815 | public static function translation_fam() { |
||
| 816 | require_once(dirname(__FILE__).'/../require/class.Spotter.php'); |
||
| 817 | global $tmp_dir, $globalTransaction; |
||
| 818 | $Spotter = new Spotter(); |
||
| 819 | $query = "DELETE FROM translation WHERE Source = '' OR Source = :source"; |
||
| 820 | try { |
||
| 821 | $Connection = new Connection(); |
||
| 822 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 823 | $sth->execute(array(':source' => 'website_fam')); |
||
| 824 | } catch(PDOException $e) { |
||
| 825 | return "error : ".$e->getMessage(); |
||
| 826 | } |
||
| 827 | |||
| 828 | |||
| 829 | //update_db::unzip($out_file); |
||
| 830 | $header = NULL; |
||
| 831 | $delimiter = "\t"; |
||
| 832 | $Connection = new Connection(); |
||
| 833 | if (($handle = fopen($tmp_dir.'translation.tsv', 'r')) !== FALSE) |
||
| 834 | { |
||
| 835 | $i = 0; |
||
| 836 | //$Connection->db->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE); |
||
| 837 | //$Connection->db->beginTransaction(); |
||
| 838 | while (($data = fgetcsv($handle, 1000, $delimiter)) !== FALSE) |
||
| 839 | { |
||
| 840 | if ($i > 0) { |
||
| 841 | $query = 'INSERT INTO translation (Reg,Reg_correct,Operator,Operator_correct,Source) VALUES (:Reg, :Reg_correct, :Operator, :Operator_correct, :source)'; |
||
| 842 | try { |
||
| 843 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 844 | $sth->execute(array(':Reg' => $data[0],':Reg_correct' => $data[1],':Operator' => $data[2],':Operator_correct' => $data[3], ':source' => 'website_fam')); |
||
| 845 | } catch(PDOException $e) { |
||
| 846 | return "error : ".$e->getMessage(); |
||
| 847 | } |
||
| 848 | } |
||
| 849 | $i++; |
||
| 850 | } |
||
| 851 | fclose($handle); |
||
| 852 | //$Connection->db->commit(); |
||
| 853 | } |
||
| 854 | return ''; |
||
| 855 | } |
||
| 856 | |||
| 857 | /** |
||
| 858 | * Convert a HTML table to an array |
||
| 859 | * @param String $data HTML page |
||
| 860 | * @return Array array of the tables in HTML page |
||
| 861 | */ |
||
| 862 | private static function table2array($data) { |
||
| 889 | |||
| 890 | /** |
||
| 891 | * Get data from form result |
||
| 892 | * @param String $url form URL |
||
| 893 | * @return String the result |
||
| 894 | */ |
||
| 895 | private static function getData($url) { |
||
| 903 | /* |
||
| 904 | public static function waypoints() { |
||
| 905 | $data = update_db::getData('http://www.fallingrain.com/world/FR/waypoints.html'); |
||
| 906 | $table = update_db::table2array($data); |
||
| 907 | // print_r($table); |
||
| 908 | $query = 'TRUNCATE TABLE waypoints'; |
||
| 909 | try { |
||
| 910 | $Connection = new Connection(); |
||
| 911 | $sth = $Connection->db->prepare($query); |
||
| 912 | $sth->execute(); |
||
| 913 | } catch(PDOException $e) { |
||
| 914 | return "error : ".$e->getMessage(); |
||
| 915 | } |
||
| 916 | |||
| 917 | $query_dest = 'INSERT INTO waypoints (`ident`,`latitude`,`longitude`,`control`,`usage`) VALUES (:ident, :latitude, :longitude, :control, :usage)'; |
||
| 918 | $Connection = new Connection(); |
||
| 919 | $sth_dest = $Connection->db->prepare($query_dest); |
||
| 920 | $Connection->db->beginTransaction(); |
||
| 921 | foreach ($table as $row) { |
||
| 922 | if ($row[0] != 'Ident') { |
||
| 923 | $ident = $row[0]; |
||
| 924 | $latitude = $row[2]; |
||
| 925 | $longitude = $row[3]; |
||
| 926 | $control = $row[4]; |
||
| 927 | if (isset($row[5])) $usage = $row[5]; else $usage = ''; |
||
| 928 | $query_dest_values = array(':ident' => $ident,':latitude' => $latitude,':longitude' => $longitude,':control' => $control,':usage' => $usage); |
||
| 929 | try { |
||
| 930 | $sth_dest->execute($query_dest_values); |
||
| 931 | } catch(PDOException $e) { |
||
| 932 | return "error : ".$e->getMessage(); |
||
| 933 | } |
||
| 934 | } |
||
| 935 | } |
||
| 936 | $Connection->db->commit(); |
||
| 937 | |||
| 938 | } |
||
| 939 | */ |
||
| 940 | public static function waypoints($filename) { |
||
| 997 | |||
| 998 | public static function ivao_airlines($filename) { |
||
| 1033 | |||
| 1034 | public static function update_airspace() { |
||
| 1052 | |||
| 1053 | public static function update_vatsim() { |
||
| 1059 | |||
| 1060 | public static function update_countries() { |
||
| 1061 | global $tmp_dir, $globalDBdriver; |
||
| 1062 | include_once('class.create_db.php'); |
||
| 1063 | $Connection = new Connection(); |
||
| 1064 | View Code Duplication | if ($Connection->tableExists('countries')) { |
|
|
1 ignored issue
–
show
|
|||
| 1065 | $query = 'DROP TABLE countries'; |
||
| 1066 | try { |
||
| 1067 | $sth = $Connection->db->prepare($query); |
||
|
1 ignored issue
–
show
|
|||
| 1068 | $sth->execute(); |
||
| 1069 | } catch(PDOException $e) { |
||
| 1070 | echo "error : ".$e->getMessage(); |
||
| 1071 | } |
||
| 1072 | } |
||
| 1073 | if ($globalDBdriver == 'mysql') { |
||
| 1074 | update_db::gunzip('../db/countries.sql.gz',$tmp_dir.'countries.sql'); |
||
| 1075 | } else { |
||
| 1076 | update_db::gunzip('../db/pgsql/countries.sql.gz',$tmp_dir.'countries.sql'); |
||
| 1077 | } |
||
| 1078 | $error = create_db::import_file($tmp_dir.'countries.sql'); |
||
| 1079 | return $error; |
||
| 1080 | } |
||
| 1081 | |||
| 1082 | |||
| 1083 | public static function update_waypoints() { |
||
| 1094 | |||
| 1095 | public static function update_ivao() { |
||
| 1117 | |||
| 1118 | View Code Duplication | public static function update_routes() { |
|
| 1161 | |||
| 1162 | View Code Duplication | public static function update_ModeS_flarm() { |
|
| 1175 | |||
| 1176 | View Code Duplication | public static function update_ModeS_ogn() { |
|
| 1189 | |||
| 1190 | public static function update_owner() { |
||
| 1367 | |||
| 1368 | View Code Duplication | public static function update_translation() { |
|
| 1384 | |||
| 1385 | View Code Duplication | public static function update_translation_fam() { |
|
| 1401 | |||
| 1402 | public static function update_models() { |
||
| 1403 | global $tmp_dir, $globalDebug; |
||
| 1437 | |||
| 1438 | public static function update_aircraft() { |
||
| 1461 | |||
| 1462 | public static function update_notam() { |
||
| 1544 | |||
| 1545 | View Code Duplication | public static function check_last_update() { |
|
| 1563 | |||
| 1564 | public static function insert_last_update() { |
||
| 1575 | |||
| 1576 | View Code Duplication | public static function check_last_notam_update() { |
|
| 1594 | |||
| 1595 | public static function insert_last_notam_update() { |
||
| 1606 | |||
| 1607 | View Code Duplication | public static function check_last_owner_update() { |
|
| 1625 | |||
| 1626 | public static function insert_last_owner_update() { |
||
| 1637 | |||
| 1638 | public static function update_all() { |
||
| 1646 | } |
||
| 1647 | |||
| 1663 |
Loading history...
This check looks for variable assignements that are either overwritten by other assignments or where the variable is not used subsequently.
Both the
$myVarassignment in line 1 and the$higherassignment in line 2 are dead. The first because$myVaris never used and the second because$higheris always overwritten for every possible time line.