شركة التطبيقات المتكاملة لتصميم النظم البرمجية الخاصة

Integrated Applications Programming Company

Skip Navigation Links

Public general use code classes and xml files that we've compiled and used over the years:

NgaCountryWaypoint Support class for TentPlay Waypoint entity data

   1:  using System;
   2:  using System.Collections.Generic;
   3:  using System.Linq;
   4:  using System.Text;
   5:  using System.Data;
   6:  using System.Data.Entity;
   7:  using System.IO;
   8:  using System.Collections;
   9:  using System.Text.RegularExpressions;
  10:   
  11:  namespace Ia.TentPlay.Waypoint.Cl.Model.Data
  12:  {
  13:      ////////////////////////////////////////////////////////////////////////////
  14:   
  15:      /// <summary publish="true">
  16:      /// NgaCountryWaypoint Support class for TentPlay Waypoint entity data
  17:      /// </summary>
  18:      /// 
  19:      /// <remarks> 
  20:      /// Copyright © 2006-2015 Jasem Y. Al-Shamlan (info@ia.com.kw), Internet Applications - Kuwait. All Rights Reserved.
  21:      ///
  22:      /// This library is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by
  23:      /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
  24:      ///
  25:      /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
  26:      /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
  27:      /// 
  28:      /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
  29:      /// 
  30:      /// Copyright notice: This notice may not be removed or altered from any source distribution.
  31:      /// </remarks> 
  32:      public class NgaCountryWaypoint
  33:      {
  34:          ////////////////////////////////////////////////////////////////////////////
  35:   
  36:          /// <summary>
  37:          ///
  38:          /// </summary>
  39:          public static void DeleteTruncateTableFromDatabase(out Ia.Cl.Model.Result result)
  40:          {
  41:              int count;
  42:              Ia.Cl.Model.Db.SqlServer sqlServer;
  43:   
  44:              result = new Ia.Cl.Model.Result();
  45:              sqlServer = new Ia.Cl.Model.Db.SqlServer();
  46:   
  47:              count = sqlServer.ScalarInteger("select count(0) from NgaCountryWaypoints");
  48:   
  49:              sqlServer.Truncate("NgaCountryWaypoints");
  50:   
  51:              result.AddSuccess("Deleted row count: " + count + ". ");
  52:   
  53:              /*
  54:              using (var db = new Ia.TentPlay.Db.Waypoint())
  55:              {
  56:                  if (db.Database.Exists())
  57:                  {
  58:  
  59:                      // below: first will delete all current records
  60:                      var all = from c in db.NgaCountryWaypoints select c;
  61:                      db.NgaCountryWaypoints.RemoveRange(all);
  62:                      db.SaveChanges();
  63:                  }
  64:  
  65:                  db.SaveChanges();
  66:              }
  67:              */
  68:          }
  69:   
  70:          ////////////////////////////////////////////////////////////////////////////
  71:   
  72:          /// <summary>
  73:          ///
  74:          /// </summary>
  75:          public static void DownloadAndUnzipAndInsertIntoDatabase(string ngaFileCountry, out Ia.Cl.Model.Result result)
  76:          {
  77:              string ngaCountryWaypointFileNameFips, ngaCountryWaypointFileNameIso2, extractedFile;
  78:   
  79:              result = new Ia.Cl.Model.Result();
  80:   
  81:              //ngaCountryWaypointFileNameFips = Ia.Cl.Model.Upload.FileName.Replace(".txt", "");
  82:              ngaCountryWaypointFileNameFips = ngaFileCountry;
  83:   
  84:              ngaCountryWaypointFileNameIso2 = Ia.Cl.Model.Country.Iso2FromFips(ngaCountryWaypointFileNameFips.ToUpper());
  85:   
  86:              if (ngaCountryWaypointFileNameIso2 != null)
  87:              {
  88:                  ngaCountryWaypointFileNameIso2 = ngaCountryWaypointFileNameIso2.ToLower();
  89:   
  90:                  Ia.TentPlay.Waypoint.Cl.Model.Business.Default.DownloadNgaGnsCountryZipFileAndExtractAndSaveIt(ngaCountryWaypointFileNameFips.ToLower(), out extractedFile);
  91:   
  92:                  if (File.Exists(extractedFile))
  93:                  {
  94:                      ProcessNgaCountryFileToNgaCountryWaypointList(ngaCountryWaypointFileNameIso2, extractedFile, out result);
  95:   
  96:                      File.Delete(extractedFile);
  97:                  }
  98:                  else
  99:                  {
 100:                      result.AddError("Extracted file does not exist. ");
 101:                  }
 102:              }
 103:              else
 104:              {
 105:                  result.AddError("Could not produce iso2. ");
 106:              }
 107:          }
 108:   
 109:          ////////////////////////////////////////////////////////////////////////////
 110:   
 111:          /// <summary>
 112:          ///
 113:          /// </summary>
 114:          public static void ProcessNgaCountryFileToNgaCountryWaypointList(string ngaCountryWaypointFileNameIso2, string file, out Ia.Cl.Model.Result result)
 115:          {
 116:              bool isUsFile;
 117:              int geoNameId, UFI, UNI, recordCount, storeRecordCount, lineCount;
 118:              string[] fileLines;
 119:              Dictionary<int, Ia.TentPlay.Waypoint.Cl.Model.Business.Feature> featureDictionary;
 120:              Dictionary<int, bool> uniIsInfeatureNameListDictionary;
 121:              MatchCollection matchCollection;
 122:              FileInfo fileInfo;
 123:              Ia.Cl.Model.Result result1, result2;
 124:              Ia.Cl.Model.Country country;
 125:              Ia.Cl.Model.Language language;
 126:              Ia.TentPlay.Waypoint.Cl.Model.Business.Feature feature;
 127:              Ia.TentPlay.Waypoint.Cl.Model.Business.FeatureName featureName;
 128:   
 129:              result = new Ia.Cl.Model.Result();
 130:              language = new Ia.Cl.Model.Language();
 131:   
 132:              fileInfo = null;
 133:   
 134:              isUsFile = ngaCountryWaypointFileNameIso2 == "us";
 135:   
 136:              country = (from c in Ia.Cl.Model.Country.List where c.Iso2.ToLower() == ngaCountryWaypointFileNameIso2 select c).SingleOrDefault();
 137:   
 138:              if (country != null)
 139:              {
 140:                  Ia.TentPlay.Waypoint.Cl.Model.Data.NgaCountryWaypoint.Delete(country.Iso2.ToLower(), out result);
 141:   
 142:                  if (!result.HasError)
 143:                  {
 144:                      if (File.Exists(file))
 145:                      {
 146:                          fileInfo = new FileInfo(file);
 147:   
 148:                          recordCount = 0;
 149:                          lineCount = 0;
 150:   
 151:                          featureDictionary = new Dictionary<int, Ia.TentPlay.Waypoint.Cl.Model.Business.Feature>();
 152:                          uniIsInfeatureNameListDictionary = new Dictionary<int, bool>();
 153:   
 154:                          fileLines = File.ReadAllLines(file);
 155:   
 156:                          foreach (string line in fileLines)
 157:                          {
 158:                              lineCount++;
 159:   
 160:                              if (!string.IsNullOrEmpty(line))
 161:                              {
 162:                                  //line += "\t"; // to match the last item
 163:   
 164:                                  if (isUsFile)
 165:                                  {
 166:                                      // USA file only
 167:   
 168:                                      matchCollection = Regex.Matches(line, @"(.*?)\t");
 169:                                      // 2130833    McArthur Reef    McArthur Reef        52.06667    177.86667    U    RFU    US        AK    016            0        -9999    Asia/Kamchatka    2016-07-05
 170:   
 171:                                      // US record don't have multiple feature names for every feature. I will use this to lower pressure on hashtables and avoid an Out of Memory errors.
 172:                                      if (matchCollection.Count > 0)
 173:                                      {
 174:                                          if (int.TryParse(matchCollection[0].Groups[1].Value.Trim(), out geoNameId))
 175:                                          {
 176:                                              feature = new Ia.TentPlay.Waypoint.Cl.Model.Business.Feature();
 177:                                              //featureName = new Ia.TentPlay.Waypoint.Cl.Model.Business.FeatureName();
 178:                                              //feature.FeatureNames = new List<Ia.TentPlay.Waypoint.Cl.Model.Business.FeatureName>();
 179:   
 180:                                              feature.UFI = geoNameId;
 181:                                              feature.LAT = decimal.Parse(matchCollection[4].Groups[1].Value);
 182:                                              feature.LONG = decimal.Parse(matchCollection[5].Groups[1].Value);
 183:                                              feature.FC = matchCollection[6].Groups[1].Value;
 184:                                              feature.DSG = matchCollection[7].Groups[1].Value;
 185:                                              feature.CC1 = matchCollection[8].Groups[1].Value;
 186:   
 187:                                              //featureName.UNI = geoNameId;
 188:                                              //featureName.FULL_NAME_RO = matchCollection[1].Groups[1].Value;
 189:   
 190:                                              //featureName.Feature = feature; // featureDictionary[geoNameId]; // much faster
 191:   
 192:                                              //if (featureName.Feature != null) featureName.Feature.FeatureNames.Add(featureName);
 193:   
 194:                                              feature.UsFeatureName = matchCollection[1].Groups[1].Value;
 195:   
 196:                                              // ??
 197:                                              feature.UsFeatureNoDiacriticLowerCaseName = matchCollection[2].Groups[1].Value.ToLower(); ;
 198:                                              feature.UsFeatureNativeName = matchCollection[2].Groups[1].Value; ;
 199:   
 200:                                              featureDictionary[feature.UFI] = feature;
 201:                                          }
 202:                                      }
 203:                                  }
 204:                                  else
 205:                                  {
 206:                                      // other countries' files
 207:   
 208:                                      matchCollection = Regex.Matches(line, @"(.*?)\t");
 209:   
 210:                                      if (lineCount > 1 && matchCollection.Count > 0)
 211:                                      {
 212:                                          UFI = int.Parse(matchCollection[1].Groups[1].Value.Trim());
 213:                                          UNI = int.Parse(matchCollection[2].Groups[1].Value.Trim());
 214:   
 215:                                          if (!featureDictionary.ContainsKey(UFI))
 216:                                          {
 217:                                              feature = new Ia.TentPlay.Waypoint.Cl.Model.Business.Feature();
 218:                                              feature.RC = int.Parse(matchCollection[0].Groups[1].Value);
 219:                                              feature.UFI = UFI;
 220:                                              feature.LAT = decimal.Parse(matchCollection[3].Groups[1].Value);
 221:                                              feature.LONG = decimal.Parse(matchCollection[4].Groups[1].Value);
 222:                                              feature.FC = matchCollection[9].Groups[1].Value;
 223:                                              feature.DSG = matchCollection[10].Groups[1].Value;
 224:                                              feature.CC1 = matchCollection[12].Groups[1].Value;
 225:   
 226:                                              feature.FeatureNames = new List<Ia.TentPlay.Waypoint.Cl.Model.Business.FeatureName>();
 227:   
 228:                                              featureDictionary[feature.UFI] = feature;
 229:                                          }
 230:   
 231:                                          if (!uniIsInfeatureNameListDictionary.ContainsKey(UNI))
 232:                                          {
 233:                                              featureName = new Ia.TentPlay.Waypoint.Cl.Model.Business.FeatureName();
 234:                                              featureName.UNI = UNI;
 235:                                              featureName.UFI = UFI;
 236:                                              featureName.NT = matchCollection[17].Groups[1].Value;
 237:                                              featureName.FULL_NAME_RO = matchCollection[22].Groups[1].Value;
 238:                                              featureName.FULL_NAME_ND_RO = matchCollection[23].Groups[1].Value;
 239:   
 240:                                              featureName.Feature = featureDictionary[UFI]; // much faster
 241:   
 242:                                              if (featureName.Feature != null) featureName.Feature.FeatureNames.Add(featureName);
 243:   
 244:                                              uniIsInfeatureNameListDictionary[UNI] = true;
 245:                                          }
 246:                                      }
 247:                                  }
 248:                              }
 249:   
 250:                              // below: if we hit n lines count we will clear save into database then clear values, then continue
 251:                              if (lineCount % 100000 == 0)
 252:                              {
 253:                                  ///result.AddSuccess(lineCount + " lines read. ");
 254:   
 255:                                  Store(ref featureDictionary, country, out storeRecordCount, out result1);
 256:                                  //result.AddResult(result1);
 257:   
 258:                                  recordCount += storeRecordCount;
 259:   
 260:                                  featureDictionary.Clear();
 261:                                  uniIsInfeatureNameListDictionary.Clear();
 262:                              }
 263:                          }
 264:   
 265:                          // final store when the function exists
 266:                          Store(ref featureDictionary, country, out storeRecordCount, out result2);
 267:   
 268:                          result.AddResult(result2);
 269:   
 270:                          recordCount += storeRecordCount;
 271:   
 272:                          if (!result.HasError)
 273:                          {
 274:                              result.AddSuccess(lineCount + " lines read from " + country.Iso2.ToLower() + ".txt file with " + recordCount + " new record(s) inserted. ");
 275:                          }
 276:                          else result.AddResult(result2);
 277:                      }
 278:                      else
 279:                      {
 280:                          result.AddError("File does not exist. ");
 281:                      }
 282:                  }
 283:                  else
 284:                  {
 285:                      result.AddError("Could not delete " + country.Iso2.ToLower() + " datatable. ");
 286:                  }
 287:              }
 288:              else
 289:              {
 290:                  result.AddError("Country is null for file " + ngaCountryWaypointFileNameIso2 + ". ");
 291:              }
 292:          }
 293:   
 294:          ////////////////////////////////////////////////////////////////////////////
 295:   
 296:          /// <summary>
 297:          ///
 298:          /// </summary>
 299:          private static void Store(ref Dictionary<int, Ia.TentPlay.Waypoint.Cl.Model.Business.Feature> featureDictionary, Ia.Cl.Model.Country country, out int recordCount, out Ia.Cl.Model.Result result)
 300:          {
 301:              bool isUsFile;
 302:              string featureNameCountryIso2;
 303:              DataTable dataTable;
 304:              Ia.TentPlay.Waypoint.Cl.Model.Business.Feature feature;
 305:              Ia.TentPlay.Waypoint.Cl.Model.Business.FeatureName featureName, featureNativeName;
 306:              Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint waypoint;
 307:              Ia.Cl.Model.Db.SqlServer sqlServer;
 308:              Ia.Cl.Model.Geography.Location countryCapitalLocation, waypointLocation;
 309:              List<string> allowedNgaFeatureDesignationCodeList;
 310:              List<string> ngaCountryWaypointsIdList;
 311:              List<Ia.TentPlay.Waypoint.Cl.Model.NgaFeatureDesignationCode> ngaFeatureDesignationCodesList;
 312:              List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> waypointList;
 313:   
 314:              result = new Ia.Cl.Model.Result();
 315:              sqlServer = new Ia.Cl.Model.Db.SqlServer();
 316:              waypointList = new List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint>();
 317:   
 318:              countryCapitalLocation = new Ia.Cl.Model.Geography.Location(country.CapitalLatitude, country.CapitalLongitude);
 319:   
 320:              isUsFile = country.Iso2.ToLower() == "us";
 321:   
 322:              allowedNgaFeatureDesignationCodeList = Ia.TentPlay.Waypoint.Cl.Model.Business.Default.AllowedNgaFeatureDesignationCodeList(country.Iso2.ToLower());
 323:   
 324:              using (var db = new Ia.TentPlay.Db.Waypoint())
 325:              {
 326:                  recordCount = 0;
 327:   
 328:                  ngaFeatureDesignationCodesList = (from nfdc in db.NgaFeatureDesignationCodes select nfdc).ToList();
 329:   
 330:                  if (ngaFeatureDesignationCodesList.Count != 0)
 331:                  {
 332:                      ngaCountryWaypointsIdList = (from ncw in db.NgaCountryWaypoints where ncw.Country == country.Iso2.ToLower() select ncw.Id).ToList();
 333:   
 334:                      foreach (KeyValuePair<int, Ia.TentPlay.Waypoint.Cl.Model.Business.Feature> kvp in featureDictionary)
 335:                      {
 336:                          feature = kvp.Value;
 337:   
 338:                          if (allowedNgaFeatureDesignationCodeList.Contains(feature.DSG))
 339:                          {
 340:                              // below: we loop through all countries in designation
 341:                              string[] cc1CountryList = feature.CC1.Split(',');
 342:   
 343:                              foreach (string cc1Country in cc1CountryList)
 344:                              {
 345:                                  featureNameCountryIso2 = Ia.Cl.Model.Country.Iso2FromFips(cc1Country);
 346:   
 347:                                  if (featureNameCountryIso2 != null)
 348:                                  {
 349:                                      featureNameCountryIso2 = featureNameCountryIso2.ToLower();
 350:   
 351:                                      if (isUsFile)
 352:                                      {
 353:                                          waypoint = new Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint();
 354:   
 355:                                          waypoint.Id = country.Iso2.ToLower() + "." + feature.UFI.ToString() + "." + featureNameCountryIso2;
 356:                                          waypoint.Latitude = (double)feature.LAT;
 357:                                          waypoint.Longitude = (double)feature.LONG;
 358:   
 359:                                          waypointLocation = new Ia.Cl.Model.Geography.Location(waypoint.Latitude, waypoint.Longitude);
 360:                                          waypoint.DistanceToCapital = (int)countryCapitalLocation.GetDistanceTo(waypointLocation);
 361:   
 362:                                          waypoint.CMap = Ia.Cl.Model.Geography.Location.ReturnCMapValueForResolutionAndCoordinates(1, (double)feature.LAT, (double)feature.LONG);
 363:                                          waypoint.Class = feature.FC.Substring(0, 1); // take only the first letter "A - administrative ..."
 364:                                          waypoint.Designation = (from nfdc in ngaFeatureDesignationCodesList where nfdc.Id == feature.DSG select nfdc).SingleOrDefault();
 365:                                          // dataRow["language"] = iso6393;
 366:   
 367:                                          waypoint.Country = featureNameCountryIso2;
 368:                                          waypoint.NgaFileCountry = country.Iso2.ToLower();
 369:   
 370:                                          waypoint.Name = feature.UsFeatureName;
 371:                                          waypoint.NoDiacriticLowerCaseName = Ia.Cl.Model.Language.RemoveNonLatinCharacters(feature.UsFeatureNoDiacriticLowerCaseName);
 372:                                          waypoint.NativeName = feature.UsFeatureNativeName;
 373:                                          waypoint.NoDiacriticNativeName = Ia.Cl.Model.Language.RemoveDiacritics(feature.UsFeatureNativeName);
 374:   
 375:                                          waypoint.Deleted = false;
 376:                                          waypoint.Created = DateTime.UtcNow.AddHours(3);
 377:                                          waypoint.Updated = DateTime.UtcNow.AddHours(3);
 378:   
 379:                                          waypointList.Add(waypoint);
 380:                                          recordCount++;
 381:                                      }
 382:                                      else
 383:                                      {
 384:                                          featureName = feature.ProperNameFeature;
 385:                                          featureNativeName = feature.ProperNativeNameFeature;
 386:   
 387:                                          if (featureName != null)
 388:                                          {
 389:                                              waypoint = new Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint();
 390:   
 391:                                              waypoint.Id = country.Iso2.ToLower() + "." + feature.UFI.ToString() + "." + featureName.UNI + "." + featureNameCountryIso2;
 392:                                              waypoint.Latitude = (double)feature.LAT;
 393:                                              waypoint.Longitude = (double)feature.LONG;
 394:   
 395:                                              waypointLocation = new Ia.Cl.Model.Geography.Location(waypoint.Latitude, waypoint.Longitude);
 396:                                              waypoint.DistanceToCapital = (int)countryCapitalLocation.GetDistanceTo(waypointLocation);
 397:   
 398:                                              waypoint.CMap = Ia.Cl.Model.Geography.Location.ReturnCMapValueForResolutionAndCoordinates(1, (double)feature.LAT, (double)feature.LONG);
 399:                                              waypoint.Class = feature.FC.Substring(0, 1); // take only the first letter "A - administrative ..."
 400:                                              waypoint.Designation = (from nfdc in ngaFeatureDesignationCodesList where nfdc.Id == feature.DSG select nfdc).SingleOrDefault();
 401:                                              // dataRow["language"] = iso6393;
 402:   
 403:                                              waypoint.Country = featureNameCountryIso2;
 404:                                              waypoint.NgaFileCountry = country.Iso2.ToLower();
 405:   
 406:                                              if (featureName != null)
 407:                                              {
 408:                                                  if (!string.IsNullOrEmpty(featureName.FULL_NAME_RO))
 409:                                                  {
 410:                                                      waypoint.Name = featureName.FULL_NAME_RO;
 411:                                                  }
 412:   
 413:                                                  if (!string.IsNullOrEmpty(featureName.FULL_NAME_ND_RO))
 414:                                                  {
 415:                                                      waypoint.NoDiacriticLowerCaseName = Ia.Cl.Model.Language.RemoveNonLatinCharacters(featureName.FULL_NAME_ND_RO.ToLower());
 416:                                                  }
 417:                                              }
 418:   
 419:                                              if (featureNativeName != null && !string.IsNullOrEmpty(featureNativeName.FULL_NAME_RO))
 420:                                              {
 421:                                                  waypoint.NativeName = featureNativeName.FULL_NAME_RO;
 422:                                                  waypoint.NoDiacriticNativeName = Ia.Cl.Model.Language.RemoveDiacritics(waypoint.NativeName);
 423:                                              }
 424:   
 425:                                              waypoint.Deleted = false;
 426:                                              waypoint.Created = DateTime.UtcNow.AddHours(3);
 427:                                              waypoint.Updated = DateTime.UtcNow.AddHours(3);
 428:   
 429:                                              waypointList.Add(waypoint);
 430:                                              recordCount++;
 431:                                          }
 432:                                          else
 433:                                          {
 434:                                              result.AddWarning("Feature name for UFI: " + feature.UFI + " was null. ");
 435:                                          }
 436:                                      }
 437:                                  }
 438:                                  else
 439:                                  {
 440:                                      result.AddWarning("Iso2 for cc1Country: " + cc1Country + " is null. ");
 441:                                  }
 442:                              }
 443:                          }
 444:                          else
 445:                          {
 446:                              result.AddWarning("Feature DSG: " + feature.DSG + " not allowed. ");
 447:                          }
 448:                      }
 449:   
 450:                      if (!result.HasError)
 451:                      {
 452:                          dataTable = Ia.Cl.Model.Default.GenerateDataTableFromGenericClassList<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint>(waypointList, "NgaCountryWaypoints");
 453:   
 454:                          if (!result.HasError)
 455:                          {
 456:                              sqlServer.SqlBulkCopy(dataTable, out result);
 457:                          }
 458:                      }
 459:                      else
 460:                      {
 461:                      }
 462:                  }
 463:                  else
 464:                  {
 465:                      result.AddError("ngaFeatureDesignationCodesList.Count == 0.");
 466:                  }
 467:              }
 468:          }
 469:   
 470:          ////////////////////////////////////////////////////////////////////////////
 471:   
 472:          /// <summary>
 473:          ///
 474:          /// </summary>
 475:          public List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> List(out Ia.Cl.Model.Result result)
 476:          {
 477:              List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> ncwList;
 478:   
 479:              result = new Ia.Cl.Model.Result();
 480:   
 481:              using (var db = new Ia.TentPlay.Db.Waypoint())
 482:              {
 483:                  ncwList = (from q in db.NgaCountryWaypoints select q).ToList();
 484:              }
 485:   
 486:              result.AddSuccess("Number of records: " + ncwList.Count);
 487:   
 488:              return ncwList;
 489:          }
 490:   
 491:          ////////////////////////////////////////////////////////////////////////////
 492:   
 493:          /// <summary>
 494:          ///
 495:          /// </summary>
 496:          public static List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> List(string ngaFileCountry, out Ia.Cl.Model.Result result)
 497:          {
 498:              List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> list;
 499:   
 500:              result = new Ia.Cl.Model.Result();
 501:   
 502:              using (var db = new Ia.TentPlay.Db.Waypoint())
 503:              {
 504:                  list = (from ncw in db.NgaCountryWaypoints.Include(u => u.Designation) where ncw.NgaFileCountry == ngaFileCountry select ncw).ToList();
 505:              }
 506:   
 507:              result.AddSuccess("Number of records (" + ngaFileCountry + "): " + list.Count);
 508:   
 509:              return list;
 510:          }
 511:   
 512:          ////////////////////////////////////////////////////////////////////////////
 513:   
 514:          /// <summary>
 515:          ///
 516:          /// </summary>
 517:          public static int DistanceToCapitalOfTheNthFurthestWaypointToCapital(string ngaFileCountry, int maximumNumberOfWaypointToDisplayNearTheCapitalList, out Ia.Cl.Model.Result result)
 518:          {
 519:              int distanceToCapital;
 520:              Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint item;
 521:   
 522:              result = new Ia.Cl.Model.Result();
 523:   
 524:              using (var db = new Ia.TentPlay.Db.Waypoint())
 525:              {
 526:                  item = (from ncw in db.NgaCountryWaypoints where ncw.NgaFileCountry == ngaFileCountry orderby ncw.DistanceToCapital select ncw).ToList().Take(maximumNumberOfWaypointToDisplayNearTheCapitalList).LastOrDefault();
 527:   
 528:                  distanceToCapital = item.DistanceToCapital;
 529:              }
 530:   
 531:              return distanceToCapital;
 532:          }
 533:   
 534:          ////////////////////////////////////////////////////////////////////////////
 535:   
 536:          /// <summary>
 537:          ///
 538:          /// </summary>
 539:          public static List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> Statistic(string ngaFileCountry, out int featureCount, out int administrative, out int populated, out int vegetation, out int locality, out int undersea, out int street, out int hypsographic, out int hydrographic, out int spot, out Ia.Cl.Model.Result result)
 540:          {
 541:              List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> list;
 542:   
 543:              result = new Ia.Cl.Model.Result();
 544:   
 545:              using (var db = new Ia.TentPlay.Db.Waypoint())
 546:              {
 547:                  list = (from ncw in db.NgaCountryWaypoints.Include(u => u.Designation) where ncw.NgaFileCountry == ngaFileCountry select ncw).ToList();
 548:   
 549:                  featureCount = list.Count;
 550:   
 551:                  administrative = list.Count(u => u.Class == "A");
 552:                  populated = list.Count(u => u.Class == "P");
 553:                  vegetation = list.Count(u => u.Class == "V");
 554:                  locality = list.Count(u => u.Class == "L");
 555:                  undersea = list.Count(u => u.Class == "U");
 556:                  street = list.Count(u => u.Class == "R");
 557:                  hypsographic = list.Count(u => u.Class == "T");
 558:                  hydrographic = list.Count(u => u.Class == "H");
 559:                  spot = list.Count(u => u.Class == "S");
 560:              }
 561:   
 562:              result.AddSuccess("Number of records (" + ngaFileCountry + "): " + list.Count);
 563:   
 564:              return list;
 565:          }
 566:   
 567:          ////////////////////////////////////////////////////////////////////////////
 568:   
 569:          /// <summary>
 570:          ///
 571:          /// </summary>
 572:          public static List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> Search(string ngaFileCountry, string searchTerm, out Ia.Cl.Model.Result result)
 573:          {
 574:              return Search(ngaFileCountry, searchTerm, 999999, out result);
 575:          }
 576:   
 577:          ////////////////////////////////////////////////////////////////////////////
 578:   
 579:          /// <summary>
 580:          ///
 581:          /// </summary>
 582:          public static List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> Search(string ngaFileCountry, string searchTerm, int numberOfRecordsToTake, out Ia.Cl.Model.Result result)
 583:          {
 584:              List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> list;
 585:   
 586:              result = new Ia.Cl.Model.Result();
 587:   
 588:              using (var db = new Ia.TentPlay.Db.Waypoint())
 589:              {
 590:                  list = (from ncw in db.NgaCountryWaypoints.Include(u => u.Designation) where ncw.NgaFileCountry == ngaFileCountry && (ncw.Name.Contains(searchTerm) || ncw.NoDiacriticLowerCaseName.Contains(searchTerm)) select ncw).Take(numberOfRecordsToTake).ToList();
 591:   
 592:                  if (list == null || list.Count == 0)
 593:                  {
 594:                      result.AddWarning("No waypoint found for \"" + searchTerm + "\". ");
 595:                  }
 596:                  else
 597:                  {
 598:                      result.AddSuccess("Number of waypoints found: " + list.Count + " for \"" + searchTerm + "\". ");
 599:                  }
 600:              }
 601:   
 602:              return list;
 603:          }
 604:   
 605:          ////////////////////////////////////////////////////////////////////////////
 606:   
 607:          /// <summary>
 608:          ///
 609:          /// </summary>
 610:          public static Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint Read(string id)
 611:          {
 612:              Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint item;
 613:   
 614:              using (var db = new Ia.TentPlay.Db.Waypoint())
 615:              {
 616:                  item = (from ncw in db.NgaCountryWaypoints where ncw.Id == id select ncw).SingleOrDefault();
 617:              }
 618:   
 619:              return item;
 620:          }
 621:   
 622:          ////////////////////////////////////////////////////////////////////////////
 623:   
 624:          /// <summary>
 625:          ///
 626:          /// </summary>
 627:          public static List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> List(string ngaFileCountry, int numberOfRecordsToTake, out Ia.Cl.Model.Result result)
 628:          {
 629:              List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> list;
 630:   
 631:              result = new Ia.Cl.Model.Result();
 632:   
 633:              using (var db = new Ia.TentPlay.Db.Waypoint())
 634:              {
 635:                  list = (from ncw in db.NgaCountryWaypoints.Include(u => u.Designation) where ncw.NgaFileCountry == ngaFileCountry select ncw).Take(numberOfRecordsToTake).ToList();
 636:              }
 637:   
 638:              result.AddSuccess("Number of records (" + ngaFileCountry + "): " + list.Count);
 639:   
 640:              return list;
 641:          }
 642:   
 643:          ////////////////////////////////////////////////////////////////////////////
 644:   
 645:          /// <summary>
 646:          ///
 647:          /// </summary>
 648:          public static void Delete(string ngaFileCountry, out Ia.Cl.Model.Result result)
 649:          {
 650:              int count;
 651:              Ia.Cl.Model.Db.SqlServer sqlServer;
 652:   
 653:              result = new Ia.Cl.Model.Result();
 654:              sqlServer = new Ia.Cl.Model.Db.SqlServer();
 655:   
 656:              count = sqlServer.ScalarInteger("select count(0) from NgaCountryWaypoints where NgaFileCountry = '" + ngaFileCountry + "'");
 657:   
 658:              sqlServer.Sql("delete from NgaCountryWaypoints where NgaFileCountry = '" + ngaFileCountry + "'");
 659:   
 660:              result.AddSuccess("Deleted row count (" + ngaFileCountry + "): " + count + ". ");
 661:   
 662:              // below: this is good for EF but too slow
 663:              /*
 664:              try
 665:              {
 666:                  using (var db = new Ia.TentPlay.Db.Waypoint())
 667:                  {
 668:                      var ncwDeletedList = from ncw in db.NgaCountryWaypoints where ncw.NgaFileCountry == ngaFileCountry select ncw;
 669:  
 670:                      c = ncwDeletedList.Count();
 671:  
 672:                      db.NgaCountryWaypoints.RemoveRange(ncwDeletedList);
 673:                      db.SaveChanges();
 674:  
 675:                      result.Message = "NgaCountryWaypoints: " + c + " deleted for NgaFileCountry " + ngaFileCountry + ". ";
 676:                  }
 677:              }
 678:              catch (Exception ex)
 679:              {
 680:                  result.AddError(ex.ToString());
 681:              }
 682:              */
 683:          }
 684:   
 685:          ////////////////////////////////////////////////////////////////////////////
 686:   
 687:          /// <summary>
 688:          ///
 689:          /// </summary>
 690:          public List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> UpdatedAfterDateList(DateTime date)
 691:          {
 692:              List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> list;
 693:   
 694:              using (var db = new Ia.TentPlay.Db.Waypoint())
 695:              {
 696:                  list = (from q in db.NgaCountryWaypoints where q.Updated >= date select q).ToList<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint>();
 697:              }
 698:   
 699:              return list;
 700:          }
 701:   
 702:          ////////////////////////////////////////////////////////////////////////////
 703:   
 704:          /// <summary>
 705:          ///
 706:          /// </summary>
 707:          public List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> ReadByCountryIso2(string countryIso2)
 708:          {
 709:              List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> list;
 710:   
 711:              using (var db = new Ia.TentPlay.Db.Waypoint())
 712:              {
 713:                  list = (from q in db.NgaCountryWaypoints where q.Country == countryIso2 select q).ToList<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint>();
 714:              }
 715:   
 716:              return list;
 717:          }
 718:   
 719:          ////////////////////////////////////////////////////////////////////////////
 720:   
 721:          /// <summary>
 722:          ///
 723:          /// </summary>
 724:          public List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> ReadyCountryIso2AndUpdatedAfterDateList(string countryIso2, DateTime date)
 725:          {
 726:              List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> list;
 727:   
 728:              using (var db = new Ia.TentPlay.Db.Waypoint())
 729:              {
 730:                  list = (from q in db.NgaCountryWaypoints where q.Country == countryIso2 && q.Updated >= date select q).ToList<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint>();
 731:              }
 732:   
 733:              return list;
 734:          }
 735:   
 736:          ////////////////////////////////////////////////////////////////////////////
 737:   
 738:          /// <summary>
 739:          ///
 740:          /// </summary>
 741:          public List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> WithinCMapList(string cmap)
 742:          {
 743:              List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> list;
 744:   
 745:              list = null;
 746:   
 747:              if (cmap.Length > 0)
 748:              {
 749:                  using (var db = new Ia.TentPlay.Db.Waypoint())
 750:                  {
 751:                      list = (from q in db.NgaCountryWaypoints where q.CMap.Contains(cmap) select q).ToList<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint>();
 752:                  }
 753:              }
 754:              else
 755:              {
 756:              }
 757:   
 758:              return list;
 759:          }
 760:   
 761:          ////////////////////////////////////////////////////////////////////////////
 762:   
 763:          /// <summary>
 764:          ///
 765:          /// </summary>
 766:          public static Dictionary<string, string> DistinctNgaCountryFileWithCountList()
 767:          {
 768:              Dictionary<string, string> dictionary = new Dictionary<string, string>();
 769:   
 770:              using (var db = new Ia.TentPlay.Db.Waypoint())
 771:              {
 772:                  var list = (from ncw in db.NgaCountryWaypoints
 773:                              group ncw by ncw.NgaFileCountry into grp
 774:                              select new { NgaCountryName = grp.Key, Count = grp.Count() }).ToList();
 775:   
 776:                  dictionary = list.ToDictionary(
 777:                      ncw => ncw.NgaCountryName,
 778:                      ncw => (from country in Ia.Cl.Model.Country.List where country.Iso2 == ncw.NgaCountryName.ToUpper() select country.Name).SingleOrDefault() + " (" + ncw.Count.ToString() + ")");
 779:              }
 780:   
 781:              return dictionary;
 782:          }
 783:   
 784:          ////////////////////////////////////////////////////////////////////////////
 785:          ////////////////////////////////////////////////////////////////////////////
 786:      }
 787:  }