dbaircraft.cpp 9.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291
  1. #include "dbaircraft.h"
  2. #include "dbman.cpp"
  3. /*!
  4. * \brief RetreiveRegistration Looks up tail_id from Database
  5. * \param tail_ID Primary Key of tails database
  6. * \return Registration
  7. */
  8. QString dbAircraft::retreiveRegistration(QString tail_ID)
  9. {
  10. QString acftRegistration("");
  11. QSqlQuery query;
  12. query.prepare("SELECT registration FROM tails WHERE tail_id == ?");
  13. query.addBindValue(tail_ID.toInt());
  14. query.exec();
  15. if(query.first());
  16. else
  17. qDebug() << ("No Aircraft with this ID found");
  18. query.previous();//To go back to index 0
  19. while (query.next()) {
  20. acftRegistration.append(query.value(0).toString());
  21. }
  22. return acftRegistration;
  23. }
  24. /*!
  25. * \brief newAcftGetString Looks up an aircraft Registration in the database
  26. * \param searchstring
  27. * \return Registration, make, model and variant
  28. */
  29. QStringList dbAircraft::newAcftGetString(QString searchstring)
  30. {
  31. QStringList result;
  32. if(searchstring.length()<2){return result;}
  33. QSqlQuery query;
  34. query.prepare("SELECT registration, make, model, variant "
  35. "FROM aircraft "
  36. "INNER JOIN tails on tails.aircraft_ID = aircraft.aircraft_id "
  37. "WHERE tails.registration LIKE ?");
  38. searchstring.append("%"); searchstring.prepend("%");
  39. query.addBindValue(searchstring);
  40. query.exec();
  41. while(query.next())
  42. {
  43. result.append(query.value(0).toString() + " (" + query.value(1).toString() + "-" + query.value(2).toString() + "-" + query.value(3).toString() + ")");
  44. }
  45. qDebug() << "newAcftGetString: " << result.length() << result;
  46. return result;
  47. }
  48. /*!
  49. * \brief dbAircraft::newAcftGetId Looks up a registration in the databse
  50. * \param registration Aircraft Registration
  51. * \return tail_id
  52. */
  53. QString dbAircraft::retreiveTailId(QString registration)
  54. {
  55. QString result;
  56. QSqlQuery query;
  57. query.prepare("SELECT tail_id "
  58. "FROM tails "
  59. "WHERE registration LIKE ?");
  60. registration.prepend("%"); registration.append("%");
  61. query.addBindValue(registration);
  62. query.exec();
  63. while(query.next())
  64. {
  65. result.append(query.value(0).toString());
  66. }
  67. qDebug() << "retreiveTailId: " << result;
  68. return result;
  69. }
  70. /*!
  71. * \brief dbAircraft::retreiveAircraftId Looks up aircraft_id in tails table
  72. * \param tail_id
  73. * \return aircraft_id
  74. */
  75. QString dbAircraft::retreiveAircraftId(QString tail_id)
  76. {
  77. QString result;
  78. QSqlQuery query;
  79. query.prepare("SELECT aircraft_id "
  80. "FROM tails "
  81. "WHERE tail_id = ?");
  82. query.addBindValue(tail_id);
  83. query.exec();
  84. while(query.next())
  85. {
  86. result.append(query.value(0).toString());
  87. }
  88. qDebug() << "retreiveAircraftId: " << result;
  89. return result;
  90. }
  91. /*!
  92. * \brief dbAircraft::retreiveAircraftTypeFromReg Searches the tails Database
  93. * \param searchstring
  94. * \return {registration, type, iata Code, tail_id} or {}
  95. */
  96. QVector<QString> dbAircraft::retreiveAircraftTypeFromReg(QString searchstring)
  97. {
  98. QSqlQuery query;
  99. query.prepare("SELECT Name, iata, registration, tail_id " //"SELECT Registration, Name, icao, iata "
  100. "FROM aircraft "
  101. "INNER JOIN tails on tails.aircraft_ID = aircraft.aircraft_id "
  102. "WHERE tails.registration LIKE ?");
  103. // Returns Registration/Name/icao/iata
  104. searchstring.prepend("%");
  105. searchstring.append("%");
  106. query.addBindValue(searchstring);
  107. query.exec();
  108. QVector<QString> result;
  109. if(query.first())
  110. {
  111. QString acType = query.value(0).toString();
  112. QString iataCode = query.value(1).toString();
  113. QString registration = query.value(2).toString();
  114. QString tail_id = query.value(3).toString();
  115. result.append(registration); result.append(acType);
  116. result.append(iataCode); result.append(tail_id);
  117. return result;
  118. }else
  119. {
  120. return result; // return empty
  121. }
  122. }
  123. /*!
  124. * \brief dbAircraft::retreiveAircraftMake Search function to provide a
  125. * QStringList to the QCompleter
  126. * \param searchstring A possible aircraft manufacturer
  127. * \return Possible values according to the aircraft database
  128. */
  129. QStringList dbAircraft::retreiveAircraftMake(QString searchstring)
  130. {
  131. QStringList result;
  132. QSqlQuery query;
  133. query.prepare("SELECT make from aircraft WHERE make LIKE ?");
  134. searchstring.prepend("%"); searchstring.append("%");
  135. query.addBindValue(searchstring);
  136. query.exec();
  137. while(query.next())
  138. {
  139. result.append(query.value(0).toString());
  140. }
  141. qDebug() << "db::RetreiveAircraftMake... Result:" << result;
  142. return result;
  143. }
  144. /*!
  145. * \brief dbAircraft::retreiveAircraftModel Search function to provide a
  146. * QStringList to the QCompleter
  147. * \param make A possible aircraft family (A320, 737,...)
  148. * \param searchstring
  149. * \return
  150. */
  151. QStringList dbAircraft::retreiveAircraftModel(QString make, QString searchstring)
  152. {
  153. QStringList result;
  154. QSqlQuery query;
  155. query.prepare("SELECT model FROM aircraft WHERE make = ? AND model LIKE ?");
  156. query.addBindValue(make);
  157. searchstring.prepend("%"); searchstring.append("%");
  158. query.addBindValue(searchstring);
  159. query.exec();
  160. while(query.next())
  161. {
  162. result.append(query.value(0).toString());
  163. qDebug() << "db::RetreiveAircraftModel... Result:" << result;
  164. }
  165. return result;
  166. }
  167. /*!
  168. * \brief dbAircraft::retreiveAircraftVariant Search function to provide a
  169. * QStringList to the QCompleter
  170. * \param make Aircraft manufacturer
  171. * \param model Aircraft family
  172. * \param searchstring
  173. * \return Aircraft Variant
  174. */
  175. QStringList dbAircraft::retreiveAircraftVariant(QString make, QString model, QString searchstring)
  176. {
  177. QStringList result;
  178. QSqlQuery query;
  179. query.prepare("SELECT variant from aircraft WHERE make = ? AND model = ? AND variant LIKE ?");
  180. query.addBindValue(make);
  181. query.addBindValue(model);
  182. searchstring.prepend("%"); searchstring.append("%");
  183. query.addBindValue(searchstring);
  184. query.exec();
  185. while(query.next())
  186. {
  187. result.append(query.value(0).toString());
  188. qDebug() << "db::RetreiveAircraftVariant... Result:" << result;
  189. }
  190. return result;
  191. }
  192. /*!
  193. * \brief dbAircraft::retreiveAircraftIdFromMakeModelVariant Looks up the unique
  194. * aircraft id for a given specification of make, model and variant
  195. * \param make Aircraft manufacturer (e.g. Boeing)
  196. * \param model Aircraft family (e.g. 737)
  197. * \param variant Aircraft variant (e.g. -800)
  198. * \return arcraft_id primary key of aircraft database
  199. */
  200. QString dbAircraft::retreiveAircraftIdFromMakeModelVariant(QString make, QString model, QString variant)
  201. {
  202. QString result;
  203. QSqlQuery query;
  204. query.prepare("SELECT aircraft_id FROM aircraft WHERE make = ? AND model = ? AND variant = ?");
  205. query.addBindValue(make);
  206. query.addBindValue(model);
  207. query.addBindValue(variant);
  208. query.exec();
  209. if(query.first())
  210. {
  211. result.append(query.value(0).toString());
  212. qDebug() << "db::RetreiveAircraftIdFromMakeModelVariant: Aircraft found! ID# " << result;
  213. return result;
  214. }else
  215. {
  216. result = result.left(result.length()-1);
  217. result.append("0");
  218. qDebug() << "db::RetreiveAircraftIdFromMakeModelVariant: ERROR - no AircraftId found.";
  219. return result;
  220. }
  221. }
  222. /*!
  223. * \brief dbAircraft::commitTailToDb Creates a new entry in the tails database
  224. * \param registration
  225. * \param aircraft_id Primary key of aircraft database
  226. * \param company optional entry if a/c is associated with a certain company
  227. * \return true on success
  228. */
  229. bool dbAircraft::commitTailToDb(QString registration, QString aircraft_id, QString company)
  230. {
  231. QSqlQuery commit;
  232. commit.prepare("INSERT INTO tails (registration, aircraft_id, company) VALUES (?,?,?)");
  233. commit.addBindValue(registration);
  234. commit.addBindValue(aircraft_id);
  235. commit.addBindValue(company);
  236. commit.exec();
  237. if(commit.lastError().text().length() < 0){
  238. qWarning() << "db::CommitAircraftToDb:: SQL error:" << commit.lastError().text();
  239. return false;
  240. }else{
  241. return true;
  242. }
  243. }
  244. QVector<QString> dbAircraft::retreiveAircraftDetails(QString aircraft_id)
  245. {
  246. QSqlQuery query;
  247. query.prepare("SELECT singlepilot, multipilot, singleengine, "
  248. "multiengine, turboprop, jet, heavy "
  249. "FROM aircraft "
  250. "WHERE aircraft_id = ?");
  251. query.addBindValue(aircraft_id);
  252. query.exec();
  253. QVector<QString> result;
  254. while(query.next())
  255. {
  256. result.append(query.value(0).toString()); // Singlepilot
  257. result.append(query.value(1).toString()); // Multipilot
  258. result.append(query.value(2).toString()); // Singlengine
  259. result.append(query.value(3).toString()); // Multiengine
  260. result.append(query.value(4).toString()); // turboprop
  261. result.append(query.value(5).toString()); // jet
  262. result.append(query.value(6).toString()); // heavy
  263. qDebug() << "dbaircraft::retreiveAircraftDetails... Result:" << result;
  264. }
  265. return result;
  266. }