)>}]
شركة التطبيقات المتكاملة لتصميم وبرمجة البرمجيات الخاصة ش.ش.و.
Integrated Applications Programming Company
Home » Code Library » NgaCountryWaypoint (Ia.TentPlay.Waypoint.Cl.Model.Data)

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), Integrated 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.Models.Result result)
   40:         {
   41:             int count;
   42:             Ia.Cl.Models.Db.SqlServer sqlServer;
   43:  
   44:             result = new Ia.Cl.Models.Result();
   45:             sqlServer = new Ia.Cl.Models.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.Models.Result result)
   76:         {
   77:             string ngaCountryWaypointFileNameFips, ngaCountryWaypointFileNameIso2, extractedFile;
   78:  
   79:             result = new Ia.Cl.Models.Result();
   80:  
   81:             //ngaCountryWaypointFileNameFips = Ia.Cl.Models.Upload.FileName.Replace(".txt", "");
   82:             ngaCountryWaypointFileNameFips = ngaFileCountry;
   83:  
   84:             ngaCountryWaypointFileNameIso2 = Ia.Cl.Models.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.Models.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.Models.Result result1, result2;
  124:             Ia.Cl.Models.Country country;
  125:             Ia.Cl.Models.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.Models.Result();
  130:             language = new Ia.Cl.Models.Language();
  131:  
  132:             fileInfo = null;
  133:  
  134:             isUsFile = ngaCountryWaypointFileNameIso2 == "us";
  135:  
  136:             country = (from c in Ia.Cl.Models.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.Models.Country country, out int recordCount, out Ia.Cl.Models.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.Models.Db.SqlServer sqlServer;
  308:             Ia.Cl.Models.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.Models.Result();
  315:             sqlServer = new Ia.Cl.Models.Db.SqlServer();
  316:             waypointList = new List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint>();
  317:  
  318:             countryCapitalLocation = new Ia.Cl.Models.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.Models.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.Models.Geography.Location(waypoint.Latitude, waypoint.Longitude);
  360:                                         waypoint.DistanceToCapital = (int)countryCapitalLocation.GetDistanceTo(waypointLocation);
  361:  
  362:                                         waypoint.CMap = Ia.Cl.Models.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.Models.Language.RemoveNonLatinCharacters(feature.UsFeatureNoDiacriticLowerCaseName);
  372:                                         waypoint.NativeName = feature.UsFeatureNativeName;
  373:                                         waypoint.NoDiacriticNativeName = Ia.Cl.Models.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.Models.Geography.Location(waypoint.Latitude, waypoint.Longitude);
  396:                                             waypoint.DistanceToCapital = (int)countryCapitalLocation.GetDistanceTo(waypointLocation);
  397:  
  398:                                             waypoint.CMap = Ia.Cl.Models.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.Models.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.Models.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.Models.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.Models.Result result)
  476:         {
  477:             List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> ncwList;
  478:  
  479:             result = new Ia.Cl.Models.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.Models.Result result)
  497:         {
  498:             List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> list;
  499:  
  500:             result = new Ia.Cl.Models.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.Models.Result result)
  518:         {
  519:             int distanceToCapital;
  520:             Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint item;
  521:  
  522:             result = new Ia.Cl.Models.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.Models.Result result)
  540:         {
  541:             List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> list;
  542:  
  543:             result = new Ia.Cl.Models.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.Models.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.Models.Result result)
  583:         {
  584:             List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> list;
  585:  
  586:             result = new Ia.Cl.Models.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.Models.Result result)
  628:         {
  629:             List<Ia.TentPlay.Waypoint.Cl.Model.NgaCountryWaypoint> list;
  630:  
  631:             result = new Ia.Cl.Models.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.Models.Result result)
  649:         {
  650:             int count;
  651:             Ia.Cl.Models.Db.SqlServer sqlServer;
  652:  
  653:             result = new Ia.Cl.Models.Result();
  654:             sqlServer = new Ia.Cl.Models.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.Models.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: }