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.Cl.Models.Data.Trek

   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: }