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