protected override void ExportMethod() { try { using (Utils.ProgressBlock fixscr = new Utils.ProgressBlock(this, STR_EXPORTINGGPX, STR_CREATINGFILE, 1, 0)) { System.Collections.Hashtable logTypes = new System.Collections.Hashtable(); logTypes.Add(2, "Found it"); logTypes.Add(3, "Didn't find it"); logTypes.Add(4, "Write note"); logTypes.Add(5, "Archive"); logTypes.Add(7, "Needs Archived"); logTypes.Add(9, "Will Attend"); logTypes.Add(10, "Attended"); logTypes.Add(11, "Webcam Photo Taken"); logTypes.Add(12, "Unarchive"); logTypes.Add(22, "Temporarily Disable Listing"); logTypes.Add(23, "Enable Listing"); logTypes.Add(24, "Publish Listing"); logTypes.Add(25, "Retract Listing"); logTypes.Add(45, "Needs Maintenance"); logTypes.Add(46, "Owner Maintenance"); logTypes.Add(47, "Update Coordinates"); logTypes.Add(68, "Post Reviewer Note"); logTypes.Add(74, "Announcement"); if (System.IO.File.Exists(_filename)) { System.IO.File.Delete(_filename); } using (var strm = Assembly.GetExecutingAssembly().GetManifestResourceStream("GlobalcachingApplication.Plugins.Locus.sqlite.db3")) { byte[] data = new byte[strm.Length]; strm.Read(data, 0, data.Length); File.WriteAllBytes(_filename, data); } SqliteConnection dbconFiles = null; string basePath = null; int imgFolderIndex = 0; int imgInFolderCount = 0; if (Properties.Settings.Default.ExportGrabbedImages) { basePath = System.IO.Path.GetDirectoryName(_filename); basePath = System.IO.Path.Combine(basePath, ".GrabbedImages"); if (!System.IO.Directory.Exists(basePath)) { System.IO.Directory.CreateDirectory(basePath); } if (Properties.Settings.Default.MaxFilesInFolder > 0) { string imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex)); while (System.IO.Directory.Exists(imgSubFolder)) { imgFolderIndex++; imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex)); } } dbconFiles = new SqliteConnection(string.Format("data source=file:{0}", System.IO.Path.Combine(basePath, "files.db3"))); dbconFiles.Open(); using (SqliteCommand cmd = new SqliteCommand("", dbconFiles)) { cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='fdone'"; object o = cmd.ExecuteScalar(); if (o == null || o.GetType() == typeof(DBNull)) { cmd.CommandText = "CREATE TABLE fdone (dlink text)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE INDEX ifdone on fdone (dlink)"; cmd.ExecuteNonQuery(); } cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='files'"; o = cmd.ExecuteScalar(); if (o == null || o.GetType() == typeof(DBNull)) { cmd.CommandText = "CREATE TABLE files (Link text collate nocase, Fname text collate nocase, Found integer)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE INDEX ilink on files (Link)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE INDEX ifname on files (Fname)"; cmd.ExecuteNonQuery(); } cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='purge'"; o = cmd.ExecuteScalar(); if (o == null || o.GetType() == typeof(DBNull)) { cmd.CommandText = "CREATE TABLE purge (pfile text)"; cmd.ExecuteNonQuery(); } } } using (SqliteConnection dbcon = new SqliteConnection(string.Format("data source=file:{0}", _filename))) { dbcon.Open(); DbParameter par; using (Utils.ProgressBlock progress = new Utils.ProgressBlock(this, STR_SAVING, STR_SAVINGGEOCACHES, _gcList.Count, 0)) { using (SqliteCommand cmd = new SqliteCommand("", dbcon)) using (SqliteCommand cmd2 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd3 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd4 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd5 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd6 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd7 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd8 = new SqliteCommand("", dbcon)) { cmd.CommandText = "drop index CachesSmart"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into Caches (Code, Name, PlacedBy, Archived, CacheId, CacheType, Container, Country, Difficulty, Found, HasCorrected, HasUserNote, Latitude, LongHtm, Longitude, OwnerName, PlacedDate, ShortHtm, State, Terrain, UserFlag, IsOwner, LatOriginal, LonOriginal, Status, GcNote, IsPremium, FavPoints) values (@Code, @Name, @PlacedBy, @Archived, @CacheId, @CacheType, @Container, @Country, @Difficulty, @Found, @HasCorrected, @HasUserNote, @Latitude, @LongHtm, @Longitude, @OwnerName, @PlacedDate, @ShortHtm, @State, @Terrain, @UserFlag, @IsOwner, @LatOriginal, @LonOriginal, @Status, @GcNote, @IsPremium, @FavPoints)"; cmd2.CommandText = "insert into CacheMemo (Code, LongDescription, ShortDescription, Url, Hints, UserNote) values (@Code, @LongDescription, @ShortDescription, @Url, @Hints, @UserNote)"; cmd3.CommandText = "insert into Attributes (aCode, aId, aInc) values (@aCode, @aId, @aInc)"; cmd4.CommandText = "insert into LogMemo (lParent, lLogId, lText) values (@lParent, @lLogId, @lText)"; cmd5.CommandText = "insert into Logs (lParent, lLogId, lType, lBy, lDate, lLat, lLon, lEncoded, lownerid, lHasHtml, lIsowner, lTime) values (@lParent, @lLogId, @lType, @lBy, @lDate, @lLat, @lLon, @lEncoded, @lownerid, @lHasHtml, @lIsowner, @lTime)"; cmd6.CommandText = "insert into WayMemo (cParent, cCode, cComment, cUrl) values (@cParent, @cCode, @cComment, @cUrl)"; cmd7.CommandText = "insert into Waypoints (cParent, cCode, cPrefix, cName, cType, cLat, cLon, cByuser, cDate, cFlag, sB1) values (@cParent, @cCode, @cPrefix, @cName, @cType, @cLat, @cLon, @cByuser, @cDate, @cFlag, @sB1)"; cmd8.CommandText = "insert into Corrected (kCode, kBeforeLat, kBeforeLon, kAfterLat, kAfterLon) values (@kCode, @kBeforeLat, @kBeforeLon, @kAfterLat, @kAfterLon)"; par = cmd8.CreateParameter(); par.ParameterName = "@kCode"; par.DbType = DbType.String; cmd8.Parameters.Add(par); par = cmd8.CreateParameter(); par.ParameterName = "@kBeforeLat"; par.DbType = DbType.String; cmd8.Parameters.Add(par); par = cmd8.CreateParameter(); par.ParameterName = "@kBeforeLon"; par.DbType = DbType.String; cmd8.Parameters.Add(par); par = cmd8.CreateParameter(); par.ParameterName = "@kAfterLat"; par.DbType = DbType.String; cmd8.Parameters.Add(par); par = cmd8.CreateParameter(); par.ParameterName = "@kAfterLon"; par.DbType = DbType.String; cmd8.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cParent"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cCode"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cPrefix"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cName"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cType"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cLat"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cLon"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cByuser"; par.DbType = DbType.Boolean; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cDate"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cFlag"; par.DbType = DbType.Boolean; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@sB1"; par.DbType = DbType.Boolean; cmd7.Parameters.Add(par); par = cmd6.CreateParameter(); par.ParameterName = "@cParent"; par.DbType = DbType.String; cmd6.Parameters.Add(par); par = cmd6.CreateParameter(); par.ParameterName = "@cCode"; par.DbType = DbType.String; cmd6.Parameters.Add(par); par = cmd6.CreateParameter(); par.ParameterName = "@cComment"; par.DbType = DbType.String; cmd6.Parameters.Add(par); par = cmd6.CreateParameter(); par.ParameterName = "@cUrl"; par.DbType = DbType.String; cmd6.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lParent"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lLogId"; par.DbType = DbType.Int32; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lType"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lBy"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lDate"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lLat"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lLon"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lEncoded"; par.DbType = DbType.Boolean; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lownerid"; par.DbType = DbType.Int32; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lHasHtml"; par.DbType = DbType.Boolean; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lIsowner"; par.DbType = DbType.Boolean; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lTime"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd4.CreateParameter(); par.ParameterName = "@lParent"; par.DbType = DbType.String; cmd4.Parameters.Add(par); par = cmd4.CreateParameter(); par.ParameterName = "@lLogId"; par.DbType = DbType.Int32; cmd4.Parameters.Add(par); par = cmd4.CreateParameter(); par.ParameterName = "@lText"; par.DbType = DbType.String; cmd4.Parameters.Add(par); par = cmd3.CreateParameter(); par.ParameterName = "@aCode"; par.DbType = DbType.String; cmd3.Parameters.Add(par); par = cmd3.CreateParameter(); par.ParameterName = "@aId"; par.DbType = DbType.Int32; cmd3.Parameters.Add(par); par = cmd3.CreateParameter(); par.ParameterName = "@aInc"; par.DbType = DbType.Int32; cmd3.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@Code"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@LongDescription"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@ShortDescription"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@Url"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@Hints"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@UserNote"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Code"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Name"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@PlacedBy"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Archived"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@CacheId"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@CacheType"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Container"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Country"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Difficulty"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Found"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@HasCorrected"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@HasUserNote"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Latitude"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@LongHtm"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Longitude"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@OwnerName"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@PlacedDate"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@ShortHtm"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@State"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Terrain"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@UserFlag"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@IsOwner"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@LatOriginal"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@LonOriginal"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Status"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@GcNote"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@IsPremium"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@FavPoints"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); cmd.Prepare(); cmd2.Prepare(); cmd3.Prepare(); cmd4.Prepare(); cmd5.Prepare(); cmd6.Prepare(); cmd7.Prepare(); cmd8.Prepare(); //using (DbTransaction trans = dbcon.BeginTransaction()) //{ int index = 0; int procStep = 0; foreach (Framework.Data.Geocache gc in _gcList) { string notes = ""; if (!string.IsNullOrEmpty(gc.Notes)) { notes = System.Web.HttpUtility.HtmlDecode(gc.Notes); } if (!string.IsNullOrEmpty(gc.PersonaleNote)) { notes = string.Concat(notes, gc.PersonaleNote); } cmd2.Parameters["@Code"].Value = gc.Code; cmd2.Parameters["@LongDescription"].Value = gc.LongDescription ?? ""; cmd2.Parameters["@ShortDescription"].Value = gc.ShortDescription ?? ""; cmd2.Parameters["@Url"].Value = gc.Url ?? ""; cmd2.Parameters["@Hints"].Value = gc.EncodedHints ?? ""; cmd2.Parameters["@UserNote"].Value = notes; cmd.Parameters["@Code"].Value = gc.Code; cmd.Parameters["@Name"].Value = gc.Name ?? ""; cmd.Parameters["@PlacedBy"].Value = gc.PlacedBy ?? ""; cmd.Parameters["@Archived"].Value = gc.Archived ? 1 : 0; cmd.Parameters["@CacheId"].Value = gc.ID ?? "1"; cmd.Parameters["@CacheType"].Value = getCacheType(gc.GeocacheType); cmd.Parameters["@Container"].Value = getContainer(gc.Container); cmd.Parameters["@Country"].Value = gc.Country ?? ""; cmd.Parameters["@Difficulty"].Value = gc.Difficulty; cmd.Parameters["@Found"].Value = gc.Found ? 1 : 0; cmd.Parameters["@HasCorrected"].Value = (gc.CustomCoords || gc.ContainsCustomLatLon) ? 1 : 0; cmd.Parameters["@HasUserNote"].Value = gc.ContainsNote ? 1 : 0; cmd.Parameters["@LatOriginal"].Value = gc.Lat.ToString().Replace(',', '.'); cmd.Parameters["@LonOriginal"].Value = gc.Lon.ToString().Replace(',', '.'); if (gc.ContainsCustomLatLon) { cmd.Parameters["@Latitude"].Value = gc.CustomLat.ToString().Replace(',', '.'); cmd.Parameters["@Longitude"].Value = gc.CustomLon.ToString().Replace(',', '.'); } else { cmd.Parameters["@Latitude"].Value = gc.Lat.ToString().Replace(',', '.'); cmd.Parameters["@Longitude"].Value = gc.Lon.ToString().Replace(',', '.'); } cmd.Parameters["@LongHtm"].Value = gc.LongDescriptionInHtml ? 1 : 0; cmd.Parameters["@OwnerName"].Value = gc.Owner ?? ""; cmd.Parameters["@PlacedDate"].Value = gc.PublishedTime.ToString("yyyy-MM-dd"); cmd.Parameters["@ShortHtm"].Value = gc.ShortDescriptionInHtml ? 1 : 0; cmd.Parameters["@State"].Value = gc.State ?? ""; cmd.Parameters["@Terrain"].Value = gc.Terrain; cmd.Parameters["@UserFlag"].Value = gc.Flagged ? 1 : 0; cmd.Parameters["@IsOwner"].Value = gc.IsOwn ? 1 : 0; cmd.Parameters["@Status"].Value = gc.Available ? "A" : gc.Archived ? "X" : "T"; cmd.Parameters["@GcNote"].Value = notes; cmd.Parameters["@IsPremium"].Value = gc.MemberOnly ? 1 : 0; cmd.Parameters["@FavPoints"].Value = gc.Favorites; cmd.ExecuteNonQuery(); cmd2.ExecuteNonQuery(); if (gc.ContainsCustomLatLon) { cmd8.Parameters["@kCode"].Value = gc.Code; cmd8.Parameters["@kBeforeLat"].Value = gc.Lat.ToString().Replace(',', '.'); cmd8.Parameters["@kBeforeLon"].Value = gc.Lon.ToString().Replace(',', '.'); cmd8.Parameters["@kAfterLat"].Value = gc.CustomLat.ToString().Replace(',', '.'); cmd8.Parameters["@kAfterLon"].Value = gc.CustomLon.ToString().Replace(',', '.'); cmd8.ExecuteNonQuery(); } List<int> attr = gc.AttributeIds; foreach (int att in attr) { cmd3.Parameters["@aCode"].Value = gc.Code; cmd3.Parameters["@aId"].Value = Math.Abs(att); cmd3.Parameters["@aInc"].Value = att < 0 ? 0 : 1; cmd3.ExecuteNonQuery(); } List<Framework.Data.Log> logs = Utils.DataAccess.GetLogs(Core.Logs, gc.Code).Take(Properties.Settings.Default.MaxLogs).ToList(); foreach (Framework.Data.Log l in logs) { try { int logid = 0; if (!int.TryParse(l.ID, out logid)) { logid = Utils.Conversion.GetCacheIDFromCacheCode(l.ID); } cmd4.Parameters["@lLogId"].Value = logid; cmd4.Parameters["@lText"].Value = l.Text ?? ""; cmd4.Parameters["@lParent"].Value = gc.Code; cmd4.ExecuteNonQuery(); cmd5.Parameters["@lLogId"].Value = logid; cmd5.Parameters["@lParent"].Value = gc.Code; object o = logTypes[l.LogType.ID]; if (o == null) { cmd5.Parameters["@lType"].Value = 4; } else { cmd5.Parameters["@lType"].Value = (string)o; } cmd5.Parameters["@lBy"].Value = l.Finder ?? ""; cmd5.Parameters["@lDate"].Value = l.Date.ToString("yyyy-MM-dd HH:mm:ss"); cmd5.Parameters["@lLat"].Value = DBNull.Value; cmd5.Parameters["@lLon"].Value = DBNull.Value; cmd5.Parameters["@lEncoded"].Value = l.Encoded; try { cmd5.Parameters["@lownerid"].Value = int.Parse(l.FinderId); } catch { } cmd5.Parameters["@lHasHtml"].Value = false; cmd5.Parameters["@lIsowner"].Value = (l.Finder == Core.GeocachingAccountNames.GetAccountName(gc.Code)); cmd5.Parameters["@lTime"].Value = ""; cmd5.ExecuteNonQuery(); } catch { } } List<Framework.Data.Waypoint> wps = Utils.DataAccess.GetWaypointsFromGeocache(Core.Waypoints, gc.Code); foreach (Framework.Data.Waypoint w in wps) { try { cmd6.Parameters["@cParent"].Value = gc.Code; cmd6.Parameters["@cCode"].Value = w.Code; cmd6.Parameters["@cComment"].Value = w.Comment; cmd6.Parameters["@cUrl"].Value = w.Url; cmd7.Parameters["@cParent"].Value = gc.Code; cmd7.Parameters["@cCode"].Value = w.Code; cmd7.Parameters["@cPrefix"].Value = w.Code.Substring(0, 2); cmd7.Parameters["@cName"].Value = w.Name ?? ""; cmd7.Parameters["@cType"].Value = getWPType(w.WPType); cmd7.Parameters["@cLat"].Value = w.Lat == null ? "0.0" : w.Lat.ToString().Replace(',', '.'); cmd7.Parameters["@cLon"].Value = w.Lon == null ? "0.0" : w.Lon.ToString().Replace(',', '.'); cmd7.Parameters["@cByuser"].Value = false; cmd7.Parameters["@cDate"].Value = w.Time.ToString("yyyy-MM-dd"); cmd7.Parameters["@cFlag"].Value = false; cmd7.Parameters["@sB1"].Value = false; cmd7.ExecuteNonQuery(); cmd6.ExecuteNonQuery(); } catch { } } if (dbconFiles != null && (gc.LongDescriptionInHtml || gc.ShortDescriptionInHtml)) { try { List<string> linksInDescr = Utils.ImageSupport.GetImageUrlsFromGeocache(gc); foreach (string link in linksInDescr) { string p = Utils.ImageSupport.Instance.GetImagePath(link); if (!string.IsNullOrEmpty(p) && IsLocalFile(p)) { using (SqliteCommand filescmd = new SqliteCommand("", dbconFiles)) { filescmd.CommandText = string.Format("SELECT Fname FROM files WHERE Link='{0}'", link.Replace("'", "''")); object o = filescmd.ExecuteScalar(); if (o == null || o.GetType() == typeof(DBNull)) { filescmd.CommandText = string.Format("insert into files (Link, Fname, Found) values ('{0}', '{1}', 1)", link.Replace("'", "''"), System.IO.Path.GetFileName(p).Replace("'", "''")); filescmd.ExecuteNonQuery(); } } if (Properties.Settings.Default.MaxFilesInFolder > 0) { imgInFolderCount++; if (imgInFolderCount > Properties.Settings.Default.MaxFilesInFolder) { imgFolderIndex++; imgInFolderCount = 1; } string imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex)); if (imgInFolderCount == 1) { if (!System.IO.Directory.Exists(imgSubFolder)) { System.IO.Directory.CreateDirectory(imgSubFolder); } } string dst = System.IO.Path.Combine(imgSubFolder, System.IO.Path.GetFileName(p)); if (!System.IO.File.Exists(dst)) { System.IO.File.Copy(p, dst, true); } } else { string dst = System.IO.Path.Combine(basePath, System.IO.Path.GetFileName(p)); if (!System.IO.File.Exists(dst)) { System.IO.File.Copy(p, dst, true); } } } } } catch { } } index++; procStep++; if (procStep >= 200) { progress.UpdateProgress(STR_SAVING, STR_SAVINGGEOCACHES, _gcList.Count, index); procStep = 0; } } //trans.Commit(); } } } if (dbconFiles != null) { dbconFiles.Dispose(); dbconFiles = null; } } } catch (Exception e) { MessageBox.Show(e.Message, Utils.LanguageSupport.Instance.GetTranslation(Utils.LanguageSupport.Instance.GetTranslation(STR_ERROR)), MessageBoxButtons.OK, MessageBoxIcon.Error); } }
protected override void ImportMethod() { System.Collections.Hashtable logTypes = new System.Collections.Hashtable(); using (Utils.ProgressBlock fixpr = new Utils.ProgressBlock(this, STR_IMPORTING, STR_IMPORTINGDATA, 1, 0)) { String connect = String.Format("data source=file:{0}", _filename); using (SqliteConnection dbcon = new SqliteConnection(connect)) { //System.Diagnostics.Debugger.Break(); logTypes.Add("Found it", 2); logTypes.Add("Didn't find it", 3); logTypes.Add("Write note", 4); logTypes.Add("Archive", 5); logTypes.Add("Needs Archived", 7); logTypes.Add("Will Attend", 9); logTypes.Add("Attended", 10); logTypes.Add("Webcam Photo Taken", 11); logTypes.Add("Unarchive", 12); logTypes.Add("Temporarily Disable Listing", 22); logTypes.Add("Enable Listing", 23); logTypes.Add("Publish Listing", 24); logTypes.Add("Retract Listing", 25); logTypes.Add("Needs Maintenance", 45); logTypes.Add("Owner Maintenance", 46); logTypes.Add("Update Coordinates", 47); logTypes.Add("Post Reviewer Note", 68); logTypes.Add("Announcement", 74); int index = 0; int procStep = 0; dbcon.Open(); SqliteCommand lookup = new SqliteCommand("select aId, aInc from attributes where aCode = @Code", dbcon); lookup.CommandType = CommandType.Text; DbParameter par = lookup.CreateParameter(); par.Direction = ParameterDirection.Input; par.ParameterName = "@Code"; lookup.Parameters.Add(par); lookup.Prepare(); SqliteCommand import = new SqliteCommand("select count(1) from caches", dbcon); import.CommandType = CommandType.Text; int gcCount = (int)(long)import.ExecuteScalar(); if (gcCount > 0) { using (Utils.ProgressBlock progress = new Utils.ProgressBlock(this, STR_IMPORTING, STR_IMPORTINGGEOCACHES, gcCount, 0)) { bool isPremiumAvailable = false; bool isFavPointAvailable = false; bool isGCNoteAvailable = false; try { import.CommandText = "select IsPremium from Caches limit 1"; using (SqliteDataReader checkdr = import.ExecuteReader()) { isPremiumAvailable = true; } } catch { } try { import.CommandText = "select FavPoints from Caches limit 1"; using (SqliteDataReader checkdr = import.ExecuteReader()) { isFavPointAvailable = true; } } catch { } try { import.CommandText = "select gcnote from Caches limit 1"; using (SqliteDataReader checkdr = import.ExecuteReader()) { isGCNoteAvailable = true; } } catch { } import.CommandText = "select caches.Code, Name, LastGPXDate, PlacedDate, Latitude, Longitude, Status, " + "Archived, Country, State, CacheType, PlacedBy, OwnerName, OwnerId, Container, Terrain, Difficulty, ShortHTM" + ", LongHTM, " + string.Format("{0}", isPremiumAvailable? "IsPremium, ":"") + " HasCorrected, LatOriginal, LonOriginal, UserFlag, Found, " + string.Format("{0}", isFavPointAvailable? "FavPoints, ":"") + " ShortDescription, LongDescription, Hints, Url, UserNote" + string.Format("{0}", isGCNoteAvailable ? ", gcnote" : "") + " from caches" + " inner join cachememo on cachememo.code = caches.code"; SqliteDataReader dr = import.ExecuteReader(); while (dr.Read()) { Framework.Data.Geocache gc = new Framework.Data.Geocache(); int cacheType; try { cacheType = getCacheType(((String)dr["CacheType"])[0]); } catch (ArgumentOutOfRangeException) { continue; } int container = getContainer(((String)dr["Container"])[0]); gc.Code = (string)dr["code"]; gc.Name = (string)dr["name"]; gc.DataFromDate = DateTime.Parse((string)dr["LastGPXDate"]); gc.Available = ((String)dr["Status"]).Equals("A"); gc.Archived = (int)dr["archived"] != 0; gc.Country = (string)dr["country"]; gc.State = (string)dr["state"]; gc.GeocacheType = Utils.DataAccess.GetGeocacheType(Core.GeocacheTypes, cacheType); gc.PlacedBy = (string)dr["placedby"]; gc.Owner = (string)dr["OwnerName"]; gc.OwnerId = dr["ownerid"].GetType() == typeof(DBNull) ? "" : dr["ownerid"].ToString(); gc.Container = Utils.DataAccess.GetGeocacheContainer(Core.GeocacheContainers, container); gc.Terrain = (double)dr["terrain"]; gc.Difficulty = (double)dr["difficulty"]; gc.ShortDescription = (string)dr["ShortDescription"]; gc.ShortDescriptionInHtml = (int)dr["ShortHTM"] != 0; gc.LongDescription = (string)dr["LongDescription"]; gc.LongDescriptionInHtml = (int)dr["LongHTM"] != 0; gc.EncodedHints = (string)dr["Hints"]; gc.Url = (string)dr["url"]; if (isPremiumAvailable) { gc.MemberOnly = (int)dr["IsPremium"] != 0; } else { gc.MemberOnly = false; } gc.CustomCoords = (int)dr["HasCorrected"] != 0; if (gc.CustomCoords) { gc.CustomLat = Utils.Conversion.StringToDouble(dr["Latitude"] as String); gc.CustomLon = Utils.Conversion.StringToDouble(dr["Longitude"] as String); gc.Lat = Utils.Conversion.StringToDouble(dr["LatOriginal"] as string); gc.Lon = Utils.Conversion.StringToDouble(dr["LonOriginal"] as string); } else { gc.Lat = Utils.Conversion.StringToDouble(dr["Latitude"] as string); gc.Lon = Utils.Conversion.StringToDouble(dr["Longitude"] as string); } par.Value = gc.Code; DbDataReader attrs = lookup.ExecuteReader(); List<int> attrList = new List<int>(); while (attrs.Read()) { int attr = (int)(int)attrs["aId"]; if (attrs["aInc"].ToString() == "0") { attr *= -1; } attrList.Add(attr); } attrs.Close(); gc.AttributeIds = attrList; gc.Notes = (string)dr["UserNote"]; gc.PublishedTime = DateTime.Parse((string)dr["PlacedDate"]); if (isGCNoteAvailable) { gc.PersonaleNote = (string)dr["gcnote"]; } else { gc.PersonaleNote = ""; } gc.Flagged = (int)dr["UserFlag"] != 0; gc.Found = (int)dr["Found"] != 0; if (isFavPointAvailable) { gc.Favorites = (int)(int)dr["FavPoints"]; } else { gc.Favorites = 0; } gc.Selected = false; Calculus.SetDistanceAndAngleGeocacheFromLocation(gc, Core.CenterLocation); AddGeocache(gc, null); index++; procStep++; if (procStep >= 500) { progress.UpdateProgress(STR_IMPORTING, STR_IMPORTINGGEOCACHES, gcCount, index); procStep = 0; } } dr.Close(); } import.CommandText = "select count(1) from logs"; int logCount = (int)(long)import.ExecuteScalar(); if (logCount > 0) { using (Utils.ProgressBlock progress = new Utils.ProgressBlock(this, STR_IMPORTING, STR_IMPORTINGLOGS, logCount, 0)) { index = 0; procStep = 0; import.CommandText = "select l.lLogId, l.lParent, lDate, lTime, lBy, lownerid, lEncoded, lType, lText " + " from logs l" + " inner join logmemo m on m.lLogId = l.lLogId and m.lParent = l.lParent"; DbDataReader dr = import.ExecuteReader(); while (dr.Read()) { Framework.Data.Log lg = new Framework.Data.Log(); String type = (String)dr["lType"]; int logType = (int)logTypes[type]; //id text, gccode text, tbcode text, date text, finder text, finderid text, logtext text, encoded integer, datafromdate text, logtype integer lg.ID = dr["lLogiD"].ToString(); lg.GeocacheCode = (string)dr["lParent"]; lg.TBCode = ""; lg.Date = (DateTime)dr["lDate"]; lg.Finder = (string)dr["lBy"]; lg.FinderId = dr["lownerid"].ToString(); lg.Text = (string)dr["lText"]; lg.Encoded = (long)dr["lEncoded"]!=0; lg.DataFromDate = DateTime.Now; lg.LogType = Utils.DataAccess.GetLogType(Core.LogTypes, logType); AddLog(lg); index++; procStep++; if (procStep >= 1000) { progress.UpdateProgress(STR_IMPORTING, STR_IMPORTINGLOGS, logCount, index); procStep = 0; } } dr.Close(); } } import.CommandText = "select count(1) from logimages"; int logimgCount = 0; try { logimgCount = (int)(long)import.ExecuteScalar(); } catch { //table does not exists } if (logimgCount > 0) { using (Utils.ProgressBlock progress = new Utils.ProgressBlock(this, STR_IMPORTING, STR_IMPORTINGLOGIMAGES, logimgCount, 0)) { index = 0; procStep = 0; import.CommandText = "select iCode, iLogId, iImage, iName from logimages"; DbDataReader dr = import.ExecuteReader(); while (dr.Read()) { Framework.Data.LogImage lg = new Framework.Data.LogImage(); lg.ID = (string)dr["iCode"]; lg.LogID = dr["iLogID"].ToString(); lg.Url = (string)dr["iImage"]; lg.Name = (string)dr["iName"]; AddLogImage(lg); index++; procStep++; if (procStep >= 500) { progress.UpdateProgress(STR_IMPORTING, STR_IMPORTINGLOGIMAGES, logimgCount, index); procStep = 0; } } dr.Close(); } } //id text, code text, geocachecode text, name text, datafromdate text, comment text, description text, url text, urlname text, wptype integer, lat real, lon real, time text import.CommandText = "select count(1) from waypoints"; int wptCount = (int)(long)import.ExecuteScalar(); if (wptCount > 0) { using (Utils.ProgressBlock progress = new Utils.ProgressBlock(this, STR_IMPORTING, STR_IMPORTINGWAYPOINTS, wptCount, 0)) { index = 0; procStep = 0; import.CommandText = "select w.cParent, w.cCode, cName, cDate, cType, cLat, cLon," + " cComment, cUrl" + " from waypoints w" + " inner join wayMemo m on w.cParent = m.cParent and w.cCode=m.cCode"; DbDataReader dr = import.ExecuteReader(); while (dr.Read()) { Framework.Data.Waypoint wp = new Framework.Data.Waypoint(); int wpType = getWPType(((string)dr["cType"])[0]); wp.ID = (string)dr["cCode"]; wp.Code = (string)dr["cCode"]; wp.Url = (string)dr["cUrl"]; //wp.UrlName = (string)dr["urlname"]; wp.Name = (string)dr["cName"]; wp.DataFromDate = (DateTime)dr["cDate"]; wp.Comment = (string)dr["cComment"]; wp.GeocacheCode = (string)dr["cParent"]; //wp.Description = (string)dr["description"]; wp.WPType = Utils.DataAccess.GetWaypointType(Core.WaypointTypes, wpType); double lat = Utils.Conversion.StringToDouble(dr["clat"] as string); double lon = Utils.Conversion.StringToDouble(dr["clon"] as string); if (Math.Abs(lat) < 0.00001) { wp.Lat = null; } else { wp.Lat = lat; } if (Math.Abs(lon) < 0.00001) { wp.Lon = null; } else { wp.Lon = lon; } wp.Time = (DateTime)dr["cDate"]; wp.Description = wp.WPType.Name; wp.UrlName = wp.WPType.Name; AddWaypoint(wp); index++; procStep++; if (procStep >= 500) { progress.UpdateProgress(STR_IMPORTING, STR_IMPORTINGWAYPOINTS, wptCount, index); procStep = 0; } } dr.Close(); } } try { //import corrected if table exists import.CommandText = "select kCode, kAfterLat, kAfterLon from Corrected"; DbDataReader dr = import.ExecuteReader(); while (dr.Read()) { string gcCode = dr["kCode"] as string ?? ""; Framework.Data.Geocache gc = Utils.DataAccess.GetGeocache(Core.Geocaches, gcCode); if (gc != null) { object oLat = dr["kAfterLat"]; object oLon = dr["kAfterLon"]; if (oLat != null && oLat.GetType() != typeof(DBNull) && oLon != null && oLon.GetType() != typeof(DBNull)) { string sLat = oLat as string; string sLon = oLon as string; if (sLat.Length > 0 && sLon.Length > 0) { gc.CustomLat = Utils.Conversion.StringToDouble(sLat); gc.CustomLon = Utils.Conversion.StringToDouble(sLon); } } } } } catch { } } } } }
public bool Save(SqliteConnection dbcon, bool forceFullData) { bool result = true; using (Utils.ProgressBlock fixpr = new ProgressBlock(this, STR_SAVING, STR_SAVINGDATA, 1, 0)) { if (dbcon != null) { string[] custAttr = Core.Geocaches.CustomAttributes; List<string> activeAttr = new List<string>(); using (SqliteCommand cmd = new SqliteCommand("select field_name from geocache_cfields", dbcon)) using (SqliteDataReader dr = cmd.ExecuteReader()) while (dr.Read()) { activeAttr.Add(string.Format("{0}", dr["field_name"])); } foreach (string s in activeAttr) { if (!custAttr.Contains(s) && ColumnExists(dbcon, "geocache", string.Format("_{0}", s))) { //drop column not supported! } } //geocache_cfields using (SqliteCommand cmd = new SqliteCommand("delete from geocache_cfields", dbcon)) cmd.ExecuteNonQuery(); foreach (string s in custAttr) { if (!activeAttr.Contains(s)) { using (SqliteCommand cmd = new SqliteCommand(string.Format("insert into geocache_cfields (field_name) values ('{0}')", s), dbcon)) cmd.ExecuteNonQuery(); } if (!ColumnExists(dbcon, "geocache", string.Format("_{0}", s))) { using (SqliteCommand cmd = new SqliteCommand(string.Format("alter table geocache add _{0} text)", s), dbcon)) cmd.ExecuteNonQuery(); } } //delete geoacches that are not in the list anymore. string[] c = (from string a in _geocachesInDB.Keys select a).ToArray(); using (SqliteCommand cmd = new SqliteCommand("", dbcon)) for (int i = 0; i < c.Length; i++) { if (Utils.DataAccess.GetGeocache(Core.Geocaches, c[i]) == null) { cmd.CommandText = string.Format("delete from geocache where code='{0}'", c[i]); cmd.ExecuteNonQuery(); _geocachesInDB.Remove(c[i]); } } //reset selection using (SqliteCommand cmd = new SqliteCommand("update geocache set selected=0", dbcon)) cmd.ExecuteNonQuery(); //now get all the selected and data changed geocaches List<Framework.Data.Geocache> gclist = (from Framework.Data.Geocache wp in Core.Geocaches where wp.Selected || !wp.Saved select wp).ToList(); if (gclist.Count > 0) { using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGGEOCACHES, gclist.Count, 0)) { string updateSqlFull = "update geocache set id=@id, name=@name, datafromdate=@datafromdate, lat=@lat, lon=@lon, disttocent=@disttocent, angletocent=@angletocent, available=@available, archived=@archived, country=@country, state=@state, cachetype=@cachetype, placedby=@placedby, owner=@owner, ownerid=@ownerid, container=@container, terrain=@terrain, difficulty=@difficulty, shortdescr=@shortdescr, shortdescrhtml=@shortdescrhtml, longdescr=@longdescr, longdescrhtml=@longdescrhtml, encodedhints=@encodedhints, url=@url, memberonly=@memberonly, customcoords=@customcoords, attrids=@attrids, favorites=@favorites, selected=@selected, municipality=@municipality, city=@city, customlat=@customlat, customlon=@customlon, notes=@notes, publiceddate=@publiceddate, personalnote=@personalnote, flagged=@flagged, found=@found, locked=@locked where code=@code"; string insertSqlFull = "insert into geocache (id, code, name, datafromdate, lat, lon, disttocent, angletocent, available, archived, country, state, cachetype, placedby, owner, ownerid, container, terrain, difficulty, shortdescr, shortdescrhtml, longdescr, longdescrhtml, encodedhints, url, memberonly, customcoords, attrids, favorites, selected, municipality, city, customlat, customlon, notes, publiceddate, personalnote, flagged, found, locked) values (@id, @code, @name, @datafromdate, @lat, @lon, @disttocent, @angletocent, @available, @archived, @country, @state, @cachetype, @placedby, @owner, @ownerid, @container, @terrain, @difficulty, @shortdescr, @shortdescrhtml, @longdescr, @longdescrhtml, @encodedhints, @url, @memberonly, @customcoords, @attrids, @favorites, @selected, @municipality, @city, @customlat, @customlon, @notes, @publiceddate, @personalnote, @flagged, @found, @locked)"; string updateSqlShort = "update geocache set id=@id, name=@name, datafromdate=@datafromdate, lat=@lat, lon=@lon, disttocent=@disttocent, angletocent=@angletocent, available=@available, archived=@archived, country=@country, state=@state, cachetype=@cachetype, placedby=@placedby, owner=@owner, ownerid=@ownerid, container=@container, terrain=@terrain, difficulty=@difficulty, encodedhints=@encodedhints, url=@url, memberonly=@memberonly, customcoords=@customcoords, attrids=@attrids, favorites=@favorites, selected=@selected, municipality=@municipality, city=@city, customlat=@customlat, customlon=@customlon, notes=@notes, publiceddate=@publiceddate, personalnote=@personalnote, flagged=@flagged, found=@found, locked=@locked where code=@code"; string insertSqlShort = "insert into geocache (id, code, name, datafromdate, lat, lon, disttocent, angletocent, available, archived, country, state, cachetype, placedby, owner, ownerid, container, terrain, difficulty, encodedhints, url, memberonly, customcoords, attrids, favorites, selected, municipality, city, customlat, customlon, notes, publiceddate, personalnote, flagged, found, locked) values (@id, @code, @name, @datafromdate, @lat, @lon, @disttocent, @angletocent, @available, @archived, @country, @state, @cachetype, @placedby, @owner, @ownerid, @container, @terrain, @difficulty, @encodedhints, @url, @memberonly, @customcoords, @attrids, @favorites, @selected, @municipality, @city, @customlat, @customlon, @notes, @publiceddate, @personalnote, @flagged, @found, @locked)"; using (SqliteCommand cmd = new SqliteCommand("",dbcon)) { cmd.CommandType = CommandType.Text; DbParameter par = cmd.CreateParameter(); par.ParameterName = "@id"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@code"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@name"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@datafromdate"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@lat"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@lon"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@disttocent"; par.DbType = DbType.Int64; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@angletocent"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@available"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@archived"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@country"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@state"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@cachetype"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@placedby"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@owner"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@ownerid"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@container"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@terrain"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@difficulty"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@shortdescr"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@shortdescrhtml"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@longdescr"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@longdescrhtml"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@encodedhints"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@url"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@memberonly"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@customcoords"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@attrids"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@favorites"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@selected"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@municipality"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@city"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@customlat"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@customlon"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@notes"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@publiceddate"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@personalnote"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@flagged"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@found"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@locked"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); foreach (string s in custAttr) { par = cmd.CreateParameter(); par.ParameterName = string.Format("@_{0}", s); par.DbType = DbType.String; cmd.Parameters.Add(par); } cmd.Prepare(); StringBuilder sb = new StringBuilder(); using (DbTransaction trans = dbcon.BeginTransaction()) { int index = 0; int procStep = 0; foreach (Framework.Data.Geocache gc in gclist) { index++; procStep++; if (!gc.Saved) { cmd.Parameters["@id"].Value = gc.ID ?? ""; cmd.Parameters["@code"].Value = gc.Code; cmd.Parameters["@name"].Value = gc.Name ?? ""; cmd.Parameters["@datafromdate"].Value = gc.DataFromDate.ToUniversalTime().ToString("u"); cmd.Parameters["@lat"].Value = gc.Lat; cmd.Parameters["@lon"].Value = gc.Lon; cmd.Parameters["@disttocent"].Value = gc.DistanceToCenter; cmd.Parameters["@angletocent"].Value = gc.AngleToCenter; cmd.Parameters["@available"].Value = gc.Available ? 1 : 0; cmd.Parameters["@archived"].Value = gc.Archived ? 1 : 0; cmd.Parameters["@country"].Value = gc.Country ?? ""; cmd.Parameters["@state"].Value = gc.State ?? ""; cmd.Parameters["@cachetype"].Value = gc.GeocacheType == null ? -1 : gc.GeocacheType.ID; cmd.Parameters["@placedby"].Value = gc.PlacedBy ?? ""; cmd.Parameters["@owner"].Value = gc.Owner ?? ""; cmd.Parameters["@ownerid"].Value = gc.OwnerId ?? ""; cmd.Parameters["@container"].Value = gc.Container == null ? -1 : gc.Container.ID; cmd.Parameters["@terrain"].Value = gc.Terrain; cmd.Parameters["@difficulty"].Value = gc.Difficulty; if (forceFullData || gc.FullDataLoaded) { cmd.Parameters["@shortdescr"].Value = gc.ShortDescription ?? ""; cmd.Parameters["@shortdescrhtml"].Value = gc.ShortDescriptionInHtml ? 1 : 0; cmd.Parameters["@longdescr"].Value = gc.LongDescription ?? ""; cmd.Parameters["@longdescrhtml"].Value = gc.LongDescriptionInHtml ? 1 : 0; } cmd.Parameters["@encodedhints"].Value = gc.EncodedHints ?? ""; cmd.Parameters["@url"].Value = gc.Url ?? ""; cmd.Parameters["@memberonly"].Value = gc.MemberOnly ? 1 : 0; cmd.Parameters["@customcoords"].Value = gc.CustomCoords ? 1 : 0; sb.Length = 0; foreach (int attrId in gc.AttributeIds) { sb.AppendFormat("|{0}|", attrId); } cmd.Parameters["@attrids"].Value = sb.ToString(); cmd.Parameters["@favorites"].Value = gc.Favorites; cmd.Parameters["@selected"].Value = gc.Selected ? 1 : 0; cmd.Parameters["@municipality"].Value = gc.Municipality ?? ""; cmd.Parameters["@city"].Value = gc.City ?? ""; if (gc.CustomLat == null) { cmd.Parameters["@customlat"].Value = DBNull.Value; } else { cmd.Parameters["@customlat"].Value = gc.CustomLat; } if (gc.CustomLon == null) { cmd.Parameters["@customlon"].Value = DBNull.Value; } else { cmd.Parameters["@customlon"].Value = gc.CustomLon; } cmd.Parameters["@notes"].Value = gc.Notes ?? ""; cmd.Parameters["@publiceddate"].Value = gc.PublishedTime.ToUniversalTime().ToString("u"); cmd.Parameters["@personalnote"].Value = gc.PersonaleNote ?? ""; cmd.Parameters["@flagged"].Value = gc.Flagged ? 1 : 0; cmd.Parameters["@found"].Value = gc.Found ? 1 : 0; cmd.Parameters["@locked"].Value = gc.Locked ? 1 : 0; foreach (string s in custAttr) { object o = gc.GetCustomAttribute(s); if (o == null) { cmd.Parameters[string.Format("@_{0}", s)].Value = DBNull.Value; } else { cmd.Parameters[string.Format("@_{0}", s)].Value = o.ToString(); } } bool indb = _geocachesInDB[gc.Code] != null; if (forceFullData || gc.FullDataLoaded) { cmd.CommandText = updateSqlFull; if (!indb || cmd.ExecuteNonQuery() == 0) { cmd.CommandText = insertSqlFull; cmd.ExecuteNonQuery(); if (!indb) { _geocachesInDB[gc.Code] = gc.Code; } } } else { cmd.CommandText = updateSqlShort; if (!indb || cmd.ExecuteNonQuery() == 0) { cmd.CommandText = insertSqlShort; cmd.ExecuteNonQuery(); if (!indb) { _geocachesInDB[gc.Code] = gc.Code; } } } gc.Saved = true; } else if (gc.Selected) { cmd.CommandText = string.Format("update geocache set selected=1 where code='{0}'", gc.Code); cmd.ExecuteNonQuery(); } if (procStep >= 200) { progress.UpdateProgress(STR_SAVING, STR_SAVINGGEOCACHES, gclist.Count, index); procStep = 0; } } trans.Commit(); } } } } //delete logs that are not in the list anymore. c = (from string a in _logsInDB.Keys select a).ToArray(); using (SqliteCommand cmd = new SqliteCommand("",dbcon)) for (int i = 0; i < c.Length; i++) { if (Utils.DataAccess.GetLog(Core.Logs, c[i]) == null) { cmd.CommandText = string.Format("delete from log where id='{0}'", c[i]); cmd.ExecuteNonQuery(); _logsInDB.Remove(c[i]); } } //now get all the selected and data changed geocaches List<Framework.Data.Log> lglist = (from Framework.Data.Log wp in Core.Logs where !wp.Saved select wp).ToList(); if (lglist.Count > 0) { using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGLOGS, lglist.Count, 0)) { // tbcode, finderid, logtext, encoded string updateSqlFull = "update log set gccode=@gccode, tbcode=@tbcode, date=@date, finder=@finder, finderid=@finderid, logtext=@logtext, encoded=@encoded, datafromdate=@datafromdate, logtype=@logtype where id=@id"; string insertSqlFull = "insert into log (id, gccode, tbcode, date, finder, finderid, logtext, encoded, datafromdate, logtype) values (@id, @gccode, @tbcode, @date, @finder, @finderid, @logtext, @encoded, @datafromdate, @logtype)"; string updateSqlShort = "update log set gccode=@gccode, date=@date, finder=@finder, datafromdate=@datafromdate, logtype=@logtype where id=@id"; string insertSqlShort = "insert into log (id, gccode, date, finder, datafromdate, logtype) values (@id, @gccode, @date, @finder, @datafromdate, @logtype)"; using (SqliteCommand cmd = new SqliteCommand("",dbcon)) { cmd.CommandType = CommandType.Text; cmd.Parameters.Clear(); DbParameter par = cmd.CreateParameter(); par.ParameterName = "@id"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@gccode"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@tbcode"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@date"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@finder"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@finderid"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@logtext"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@encoded"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@datafromdate"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@logtype"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); cmd.Prepare(); using (DbTransaction trans = dbcon.BeginTransaction()) { int index = 0; int procStep = 0; foreach (Framework.Data.Log lg in lglist) { index++; procStep++; cmd.Parameters["@id"].Value = lg.ID; cmd.Parameters["@gccode"].Value = lg.GeocacheCode; cmd.Parameters["@date"].Value = lg.Date.ToUniversalTime().ToString("u"); cmd.Parameters["@finder"].Value = lg.Finder ?? ""; cmd.Parameters["@datafromdate"].Value = lg.DataFromDate.ToUniversalTime().ToString("u"); cmd.Parameters["@logtype"].Value = lg.LogType == null ? -1 : lg.LogType.ID; if (forceFullData || lg.FullDataLoaded) { cmd.Parameters["@tbcode"].Value = lg.TBCode ?? ""; cmd.Parameters["@finderid"].Value = lg.FinderId ?? ""; cmd.Parameters["@logtext"].Value = lg.Text ?? ""; cmd.Parameters["@encoded"].Value = lg.Encoded ? 1 : 0; } bool indb = _logsInDB[lg.ID] != null; if (forceFullData || lg.FullDataLoaded) { cmd.CommandText = updateSqlFull; if (!indb || cmd.ExecuteNonQuery() == 0) { cmd.CommandText = insertSqlFull; cmd.ExecuteNonQuery(); if (!indb) { _logsInDB[lg.ID] = lg.ID; } } } else { cmd.CommandText = updateSqlShort; if (!indb || cmd.ExecuteNonQuery() == 0) { cmd.CommandText = insertSqlShort; cmd.ExecuteNonQuery(); if (!indb) { _logsInDB[lg.ID] = lg.ID; } } } lg.Saved = true; if (procStep >= 2000) { progress.UpdateProgress(STR_SAVING, STR_SAVINGLOGS, lglist.Count, index); procStep = 0; } } trans.Commit(); } } } } //delete log images that are not in the list anymore. c = (from string a in _logimgsInDB.Keys select a).ToArray(); using (SqliteCommand cmd = new SqliteCommand("",dbcon)) for (int i = 0; i < c.Length; i++) { if (Utils.DataAccess.GetLogImage(Core.LogImages, c[i]) == null) { cmd.CommandText = string.Format("delete from logimage where id='{0}'", c[i]); cmd.ExecuteNonQuery(); _logimgsInDB.Remove(c[i]); } } //now get all the selected and data changed geocaches List<Framework.Data.LogImage> imglist = (from Framework.Data.LogImage wp in Core.LogImages where !wp.Saved select wp).ToList(); if (imglist.Count > 0) { using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGLOGIMAGES, imglist.Count, 0)) { string updateSql = "update logimage set logid=@logid, url=@url, name=@name, datafromdate=@datafromdate where id=@id"; string insertSql = "insert into logimage (id, logid, url, name, datafromdate) values (@id, @logid, @url, @name, @datafromdate)"; using (SqliteCommand cmd = new SqliteCommand("",dbcon)) { cmd.CommandType = CommandType.Text; cmd.Parameters.Clear(); DbParameter par = cmd.CreateParameter(); par.ParameterName = "@id"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@logid"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@url"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@name"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@datafromdate"; par.DbType = DbType.String; cmd.Parameters.Add(par); cmd.Prepare(); using (DbTransaction trans = dbcon.BeginTransaction()) { int index = 0; int procStep = 0; foreach (Framework.Data.LogImage lg in imglist) { index++; procStep++; cmd.Parameters["@id"].Value = lg.ID; cmd.Parameters["@logid"].Value = lg.LogID ?? ""; cmd.Parameters["@url"].Value = lg.Url ?? ""; cmd.Parameters["@name"].Value = lg.Name ?? ""; cmd.Parameters["@datafromdate"].Value = lg.DataFromDate.ToUniversalTime().ToString("u"); bool indb = _logimgsInDB[lg.ID] != null; cmd.CommandText = updateSql; if (!indb || cmd.ExecuteNonQuery() == 0) { cmd.CommandText = insertSql; cmd.ExecuteNonQuery(); if (!indb) { _logimgsInDB[lg.ID] = lg.ID; } } lg.Saved = true; if (procStep >= 500) { progress.UpdateProgress(STR_SAVING, STR_SAVINGLOGIMAGES, imglist.Count, index); procStep = 0; } } trans.Commit(); } } } } c = (from string a in _wptsInDB.Keys select a).ToArray(); using (SqliteCommand cmd = new SqliteCommand("",dbcon)) for (int i = 0; i < c.Length; i++) { if (Utils.DataAccess.GetWaypoint(Core.Waypoints, c[i]) == null) { cmd.CommandText = string.Format("delete from waypoint where code='{0}'", c[i]); cmd.ExecuteNonQuery(); _wptsInDB.Remove(c[i]); } } //now get all the selected and data changed geocaches List<Framework.Data.Waypoint> wplist = (from Framework.Data.Waypoint wp in Core.Waypoints where !wp.Saved select wp).ToList(); if (wplist.Count > 0) { using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGWAYPOINTS, wplist.Count, 0)) { string updateSql = "update waypoint set id=@id, geocachecode=@geocachecode, name=@name, datafromdate=@datafromdate, comment=@comment, description=@description, url=@url, urlname=@urlname, wptype=@wptype, lat=@lat, lon=@lon, time=@time where code=@code"; string insertSql = "insert into waypoint (id, code, geocachecode, name, datafromdate, comment, description, url, urlname, wptype, lat, lon, time) values (@id, @code, @geocachecode, @name, @datafromdate, @comment, @description, @url, @urlname, @wptype, @lat, @lon, @time)"; using (SqliteCommand cmd = new SqliteCommand("",dbcon)) { cmd.CommandType = CommandType.Text; cmd.Parameters.Clear(); DbParameter par = cmd.CreateParameter(); par.ParameterName = "@id"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@code"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@url"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@name"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@datafromdate"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@geocachecode"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@comment"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@description"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@urlname"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@wptype"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@lat"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@lon"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@time"; par.DbType = DbType.String; cmd.Parameters.Add(par); cmd.Prepare(); using (DbTransaction trans = dbcon.BeginTransaction()) { int index = 0; int procStep = 0; foreach (Framework.Data.Waypoint wp in wplist) { index++; procStep++; cmd.Parameters["@id"].Value = wp.ID; cmd.Parameters["@code"].Value = wp.Code ?? ""; cmd.Parameters["@url"].Value = wp.Url ?? ""; cmd.Parameters["@urlname"].Value = wp.UrlName ?? ""; cmd.Parameters["@name"].Value = wp.Name ?? ""; cmd.Parameters["@comment"].Value = wp.Comment ?? ""; cmd.Parameters["@geocachecode"].Value = wp.GeocacheCode ?? ""; cmd.Parameters["@description"].Value = wp.Description ?? ""; cmd.Parameters["@datafromdate"].Value = wp.DataFromDate.ToUniversalTime().ToString("u"); cmd.Parameters["@time"].Value = wp.Time.ToUniversalTime().ToString("u"); cmd.Parameters["@wptype"].Value = wp.WPType.ID; if (wp.Lat == null) { cmd.Parameters["@lat"].Value = DBNull.Value; } else { cmd.Parameters["@lat"].Value = (double)wp.Lat; } if (wp.Lon == null) { cmd.Parameters["@lon"].Value = DBNull.Value; } else { cmd.Parameters["@lon"].Value = wp.Lon; } bool indb = _wptsInDB[wp.Code] != null; cmd.CommandText = updateSql; if (!indb || cmd.ExecuteNonQuery() == 0) { cmd.CommandText = insertSql; cmd.ExecuteNonQuery(); if (!indb) { _wptsInDB[wp.Code] = wp.Code; } } wp.Saved = true; if (procStep >= 500) { progress.UpdateProgress(STR_SAVING, STR_SAVINGWAYPOINTS, wplist.Count, index); procStep = 0; } } trans.Commit(); } } } } int[] ci = (from int a in _usrwptsInDB.Keys select a).ToArray(); using (SqliteCommand cmd = new SqliteCommand("",dbcon)) for (int i = 0; i < ci.Length; i++) { if (Utils.DataAccess.GetUserWaypoint(Core.UserWaypoints, ci[i]) == null) { cmd.CommandText = string.Format("delete from userwaypoint where id={0}", ci[i]); cmd.ExecuteNonQuery(); _usrwptsInDB.Remove(ci[i]); } } //now get all the selected and data changed geocaches List<Framework.Data.UserWaypoint> usrwplist = (from Framework.Data.UserWaypoint wp in Core.UserWaypoints where !wp.Saved select wp).ToList(); if (usrwplist.Count > 0) { string updateSql = "update userwaypoint set geocachecode=@geocachecode, description=@description, lat=@lat, lon=@lon, time=@time where id=@id"; string insertSql = "insert into userwaypoint (id, geocachecode, description, lat, lon, time) values (@id, @geocachecode, @description, @lat, @lon, @time)"; using (SqliteCommand cmd = new SqliteCommand("",dbcon)) { cmd.CommandType = CommandType.Text; cmd.Parameters.Clear(); DbParameter par = cmd.CreateParameter(); par.ParameterName = "@id"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@geocachecode"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@description"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@lat"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@lon"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@time"; par.DbType = DbType.String; cmd.Parameters.Add(par); cmd.Prepare(); using (DbTransaction trans = dbcon.BeginTransaction()) { foreach (Framework.Data.UserWaypoint wp in usrwplist) { cmd.Parameters["@id"].Value = wp.ID; cmd.Parameters["@geocachecode"].Value = wp.GeocacheCode ?? ""; cmd.Parameters["@description"].Value = wp.Description ?? ""; cmd.Parameters["@time"].Value = wp.Date.ToUniversalTime().ToString("u"); cmd.Parameters["@lat"].Value = (double)wp.Lat; cmd.Parameters["@lon"].Value = wp.Lon; bool indb = _usrwptsInDB[wp.ID] != null; cmd.CommandText = updateSql; if (!indb || cmd.ExecuteNonQuery() == 0) { cmd.CommandText = insertSql; cmd.ExecuteNonQuery(); if (!indb) { _usrwptsInDB[wp.ID] = wp.ID; } } wp.Saved = true; } trans.Commit(); } } } using (SqliteCommand cmd = new SqliteCommand(string.Format("update counter set geocache={0}, log={1}, waypoint={2}, logimage={3}", _geocachesInDB.Count, _logsInDB.Count, _wptsInDB.Count, _logimgsInDB.Count), dbcon)) cmd.ExecuteNonQuery(); } } return result; }
public async Task ExportToGDAK(List<Core.Data.Geocache> gcList, string targetFolder, int maxLogCount, bool offlineImages, int maxInImgFolder) { string filename = Path.Combine(targetFolder, "sqlite.db3"); await Task.Run(() => { try { DateTime nextUpdate = DateTime.Now.AddSeconds(1); using (Utils.ProgressBlock fixscr = new Utils.ProgressBlock("ExportingGDAK", "CreatingFile", 1, 0, true)) { System.Collections.Hashtable logTypes = new System.Collections.Hashtable(); logTypes.Add(2, "Found it"); logTypes.Add(3, "Didn't find it"); logTypes.Add(4, "Write note"); logTypes.Add(5, "Archive"); logTypes.Add(7, "Needs Archived"); logTypes.Add(9, "Will Attend"); logTypes.Add(10, "Attended"); logTypes.Add(11, "Webcam Photo Taken"); logTypes.Add(12, "Unarchive"); logTypes.Add(22, "Temporarily Disable Listing"); logTypes.Add(23, "Enable Listing"); logTypes.Add(24, "Publish Listing"); logTypes.Add(25, "Retract Listing"); logTypes.Add(45, "Needs Maintenance"); logTypes.Add(46, "Owner Maintenance"); logTypes.Add(47, "Update Coordinates"); logTypes.Add(68, "Post Reviewer Note"); logTypes.Add(74, "Announcement"); if (System.IO.File.Exists(filename)) { System.IO.File.Delete(filename); } SqliteConnection dbconFiles = null; string basePath = null; int imgFolderIndex = 0; int imgInFolderCount = 0; if (Core.Settings.Default.GDAKExportOfflineImages) { basePath = System.IO.Path.Combine(targetFolder, "GrabbedImages"); if (!System.IO.Directory.Exists(basePath)) { System.IO.Directory.CreateDirectory(basePath); } if (Core.Settings.Default.GDAKMaxImagesInFolder > 0) { string imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex)); while (System.IO.Directory.Exists(imgSubFolder)) { imgFolderIndex++; imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex)); } } dbconFiles = new SqliteConnection(string.Format("data source=file:{0}", System.IO.Path.Combine(basePath, "files.db3"))); dbconFiles.Open(); using (SqliteCommand cmd = new SqliteCommand("", dbconFiles)) { cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='fdone'"; object o = cmd.ExecuteScalar(); if (o == null || o.GetType() == typeof(DBNull)) { cmd.CommandText = "CREATE TABLE fdone (dlink text)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE INDEX ifdone on fdone (dlink)"; cmd.ExecuteNonQuery(); } cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='files'"; o = cmd.ExecuteScalar(); if (o == null || o.GetType() == typeof(DBNull)) { cmd.CommandText = "CREATE TABLE files (Link text collate nocase, Fname text collate nocase, Found integer)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE INDEX ilink on files (Link)"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE INDEX ifname on files (Fname)"; cmd.ExecuteNonQuery(); } cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='purge'"; o = cmd.ExecuteScalar(); if (o == null || o.GetType() == typeof(DBNull)) { cmd.CommandText = "CREATE TABLE purge (pfile text)"; cmd.ExecuteNonQuery(); } } } using (SqliteConnection dbcon = new SqliteConnection(string.Format("data source=file:{0}", filename))) { dbcon.Open(); using (SqliteCommand cmd = new SqliteCommand("", dbcon)) { foreach (string s in SQLCREATEDBTABLES) { cmd.CommandText = s; cmd.ExecuteNonQuery(); } foreach (string s in SQLCREATEDBINDEXES) { cmd.CommandText = s; cmd.ExecuteNonQuery(); } foreach (string s in SQLCREATEDBTRIGGERS) { cmd.CommandText = s; cmd.ExecuteNonQuery(); } cmd.CommandText = "PRAGMA user_version = 5003"; cmd.ExecuteNonQuery(); } DbParameter par; using (Utils.ProgressBlock progress = new Utils.ProgressBlock("SavingGeocaches", gcList.Count, 0)) { using (SqliteCommand cmd = new SqliteCommand("", dbcon)) using (SqliteCommand cmd2 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd3 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd4 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd5 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd6 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd7 = new SqliteCommand("", dbcon)) { cmd.CommandText = "insert into Caches (Code, Name, PlacedBy, Archived, CacheId, CacheType, Container, Country, Difficulty, Found, HasCorrected, HasUserNote, Latitude, LongHtm, Longitude, OwnerName, PlacedDate, ShortHtm, State, Terrain, UserFlag, IsOwner, LatOriginal, LonOriginal, Status, GcNote, IsPremium, FavPoints, IsLite) values (@Code, @Name, @PlacedBy, @Archived, @CacheId, @CacheType, @Container, @Country, @Difficulty, @Found, @HasCorrected, @HasUserNote, @Latitude, @LongHtm, @Longitude, @OwnerName, @PlacedDate, @ShortHtm, @State, @Terrain, @UserFlag, @IsOwner, @LatOriginal, @LonOriginal, @Status, @GcNote, @IsPremium, @FavPoints, @IsLite)"; cmd2.CommandText = "insert into CacheMemo (Code, LongDescription, ShortDescription, Url, Hints, UserNote) values (@Code, @LongDescription, @ShortDescription, @Url, @Hints, @UserNote)"; cmd3.CommandText = "insert into Attributes (aCode, aId, aInc) values (@aCode, @aId, @aInc)"; cmd4.CommandText = "insert into LogMemo (lParent, lLogId, lText) values (@lParent, @lLogId, @lText)"; cmd5.CommandText = "insert into Logs (lParent, lLogId, lType, lBy, lDate, lLat, lLon, lEncoded, lownerid, lHasHtml, lIsowner, lTime) values (@lParent, @lLogId, @lType, @lBy, @lDate, @lLat, @lLon, @lEncoded, @lownerid, @lHasHtml, @lIsowner, @lTime)"; cmd6.CommandText = "insert into WayMemo (cParent, cCode, cComment, cUrl) values (@cParent, @cCode, @cComment, @cUrl)"; cmd7.CommandText = "insert into Waypoints (cParent, cCode, cPrefix, cName, cType, cLat, cLon, cByuser, cDate, cFlag, sB1) values (@cParent, @cCode, @cPrefix, @cName, @cType, @cLat, @cLon, @cByuser, @cDate, @cFlag, @sB1)"; par = cmd7.CreateParameter(); par.ParameterName = "@cParent"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cCode"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cPrefix"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cName"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cType"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cLat"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cLon"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cByuser"; par.DbType = DbType.Boolean; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cDate"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cFlag"; par.DbType = DbType.Boolean; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@sB1"; par.DbType = DbType.Boolean; cmd7.Parameters.Add(par); par = cmd6.CreateParameter(); par.ParameterName = "@cParent"; par.DbType = DbType.String; cmd6.Parameters.Add(par); par = cmd6.CreateParameter(); par.ParameterName = "@cCode"; par.DbType = DbType.String; cmd6.Parameters.Add(par); par = cmd6.CreateParameter(); par.ParameterName = "@cComment"; par.DbType = DbType.String; cmd6.Parameters.Add(par); par = cmd6.CreateParameter(); par.ParameterName = "@cUrl"; par.DbType = DbType.String; cmd6.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lParent"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lLogId"; par.DbType = DbType.Int32; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lType"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lBy"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lDate"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lLat"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lLon"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lEncoded"; par.DbType = DbType.Boolean; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lownerid"; par.DbType = DbType.Int32; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lHasHtml"; par.DbType = DbType.Boolean; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lIsowner"; par.DbType = DbType.Boolean; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lTime"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd4.CreateParameter(); par.ParameterName = "@lParent"; par.DbType = DbType.String; cmd4.Parameters.Add(par); par = cmd4.CreateParameter(); par.ParameterName = "@lLogId"; par.DbType = DbType.Int32; cmd4.Parameters.Add(par); par = cmd4.CreateParameter(); par.ParameterName = "@lText"; par.DbType = DbType.String; cmd4.Parameters.Add(par); par = cmd3.CreateParameter(); par.ParameterName = "@aCode"; par.DbType = DbType.String; cmd3.Parameters.Add(par); par = cmd3.CreateParameter(); par.ParameterName = "@aId"; par.DbType = DbType.Int32; cmd3.Parameters.Add(par); par = cmd3.CreateParameter(); par.ParameterName = "@aInc"; par.DbType = DbType.Int32; cmd3.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@Code"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@LongDescription"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@ShortDescription"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@Url"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@Hints"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@UserNote"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Code"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Name"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@PlacedBy"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Archived"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@CacheId"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@CacheType"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Container"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Country"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Difficulty"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Found"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@HasCorrected"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@HasUserNote"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Latitude"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@LongHtm"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Longitude"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@OwnerName"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@PlacedDate"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@ShortHtm"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@State"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Terrain"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@UserFlag"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@IsOwner"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@LatOriginal"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@LonOriginal"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Status"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@GcNote"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@IsPremium"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@FavPoints"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@IsLite"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); cmd.Prepare(); cmd2.Prepare(); cmd3.Prepare(); cmd4.Prepare(); cmd5.Prepare(); cmd6.Prepare(); cmd7.Prepare(); //using (DbTransaction trans = dbcon.BeginTransaction()) { int index = 0; foreach (Core.Data.Geocache gc in gcList) { cmd2.Parameters["@Code"].Value = gc.Code; cmd2.Parameters["@LongDescription"].Value = gc.LongDescription ?? ""; cmd2.Parameters["@ShortDescription"].Value = gc.ShortDescription ?? ""; cmd2.Parameters["@Url"].Value = gc.Url ?? ""; cmd2.Parameters["@Hints"].Value = gc.EncodedHints ?? ""; cmd2.Parameters["@UserNote"].Value = string.IsNullOrEmpty(gc.Notes) ? "" : System.Web.HttpUtility.HtmlDecode(gc.Notes); cmd.Parameters["@Code"].Value = gc.Code; cmd.Parameters["@Name"].Value = gc.Name ?? ""; cmd.Parameters["@PlacedBy"].Value = gc.PlacedBy ?? ""; cmd.Parameters["@Archived"].Value = gc.Archived ? 1 : 0; cmd.Parameters["@CacheId"].Value = Utils.Conversion.GetCacheIDFromCacheCode(gc.Code); cmd.Parameters["@CacheType"].Value = getCacheType(gc.GeocacheType); cmd.Parameters["@Container"].Value = getContainer(gc.Container); cmd.Parameters["@Country"].Value = gc.Country ?? ""; cmd.Parameters["@Difficulty"].Value = gc.Difficulty; cmd.Parameters["@Found"].Value = gc.Found ? 1 : 0; cmd.Parameters["@HasCorrected"].Value = gc.ContainsCustomLatLon ? 1 : 0; cmd.Parameters["@HasUserNote"].Value = gc.ContainsNote ? 1 : 0; cmd.Parameters["@LatOriginal"].Value = gc.Lat.ToString().Replace(',', '.'); cmd.Parameters["@LonOriginal"].Value = gc.Lon.ToString().Replace(',', '.'); if (gc.ContainsCustomLatLon) { cmd.Parameters["@Latitude"].Value = gc.CustomLat.ToString().Replace(',', '.'); cmd.Parameters["@Longitude"].Value = gc.CustomLon.ToString().Replace(',', '.'); } else { cmd.Parameters["@Latitude"].Value = gc.Lat.ToString().Replace(',', '.'); cmd.Parameters["@Longitude"].Value = gc.Lon.ToString().Replace(',', '.'); } cmd.Parameters["@LongHtm"].Value = gc.LongDescriptionInHtml ? 1 : 0; cmd.Parameters["@OwnerName"].Value = gc.Owner ?? ""; cmd.Parameters["@PlacedDate"].Value = gc.PublishedTime.ToString("yyyy-MM-dd"); cmd.Parameters["@ShortHtm"].Value = gc.ShortDescriptionInHtml ? 1 : 0; cmd.Parameters["@State"].Value = gc.State ?? ""; cmd.Parameters["@Terrain"].Value = gc.Terrain; cmd.Parameters["@UserFlag"].Value = gc.Flagged ? 1 : 0; cmd.Parameters["@IsOwner"].Value = gc.IsOwn ? 1 : 0; cmd.Parameters["@Status"].Value = gc.Available ? "A" : gc.Archived ? "X" : "T"; cmd.Parameters["@GcNote"].Value = gc.PersonalNote ?? ""; cmd.Parameters["@IsPremium"].Value = gc.MemberOnly ? 1 : 0; cmd.Parameters["@FavPoints"].Value = gc.Favorites; cmd.Parameters["@IsLite"].Value = 0; cmd.ExecuteNonQuery(); //if (index == 256) System.Diagnostics.Debugger.Break(); cmd2.ExecuteNonQuery(); List<int> attr = gc.AttributeIds; foreach (int att in attr) { cmd3.Parameters["@aCode"].Value = gc.Code; cmd3.Parameters["@aId"].Value = Math.Abs(att); cmd3.Parameters["@aInc"].Value = att < 0 ? 0 : 1; cmd3.ExecuteNonQuery(); } List<Core.Data.Log> logs = Utils.DataAccess.GetLogs(gc.Database, gc.Code).Take(Core.Settings.Default.GDAKMaxLogCount).ToList(); foreach (Core.Data.Log l in logs) { try { int logid = 0; if (!int.TryParse(l.ID, out logid)) { logid = Utils.Conversion.GetCacheIDFromCacheCode(l.ID); } cmd4.Parameters["@lLogId"].Value = logid; cmd4.Parameters["@lText"].Value = l.Text ?? ""; cmd4.Parameters["@lParent"].Value = gc.Code; cmd4.ExecuteNonQuery(); cmd5.Parameters["@lLogId"].Value = logid; cmd5.Parameters["@lParent"].Value = gc.Code; object o = logTypes[l.LogType.ID]; if (o == null) { cmd5.Parameters["@lType"].Value = 4; } else { cmd5.Parameters["@lType"].Value = (string)o; } cmd5.Parameters["@lBy"].Value = l.Finder ?? ""; cmd5.Parameters["@lDate"].Value = l.Date.ToString("yyyy-MM-dd HH:mm:ss"); cmd5.Parameters["@lLat"].Value = DBNull.Value; cmd5.Parameters["@lLon"].Value = DBNull.Value; cmd5.Parameters["@lEncoded"].Value = l.Encoded; try { cmd5.Parameters["@lownerid"].Value = int.Parse(l.FinderId); } catch { } cmd5.Parameters["@lHasHtml"].Value = false; cmd5.Parameters["@lIsowner"].Value = (l.Finder == Core.ApplicationData.Instance.AccountInfos.GetAccountInfo(gc.Code.Substring(0,2)).AccountName); cmd5.Parameters["@lTime"].Value = ""; cmd5.ExecuteNonQuery(); } catch(Exception e) { Core.ApplicationData.Instance.Logger.AddLog(this, e); } } List<Core.Data.Waypoint> wps = Utils.DataAccess.GetWaypointsFromGeocache(gc.Database, gc.Code); foreach (Core.Data.Waypoint w in wps) { try { cmd6.Parameters["@cParent"].Value = gc.Code; cmd6.Parameters["@cCode"].Value = w.Code; cmd6.Parameters["@cComment"].Value = w.Comment; cmd6.Parameters["@cUrl"].Value = w.Url; cmd7.Parameters["@cParent"].Value = gc.Code; cmd7.Parameters["@cCode"].Value = w.Code; cmd7.Parameters["@cPrefix"].Value = w.Code.Substring(0, 2); cmd7.Parameters["@cName"].Value = w.Name ?? ""; cmd7.Parameters["@cType"].Value = getWPType(w.WPType); cmd7.Parameters["@cLat"].Value = w.Lat == null ? "0.0" : w.Lat.ToString().Replace(',', '.'); cmd7.Parameters["@cLon"].Value = w.Lon == null ? "0.0" : w.Lon.ToString().Replace(',', '.'); cmd7.Parameters["@cByuser"].Value = false; cmd7.Parameters["@cDate"].Value = w.Time.ToString("yyyy-MM-dd"); cmd7.Parameters["@cFlag"].Value = false; cmd7.Parameters["@sB1"].Value = false; cmd7.ExecuteNonQuery(); cmd6.ExecuteNonQuery(); } catch (Exception e) { Core.ApplicationData.Instance.Logger.AddLog(this, e); } } if (dbconFiles != null && (gc.LongDescriptionInHtml || gc.ShortDescriptionInHtml)) { try { List<string> imgs = Utils.DataAccess.GetImagesOfGeocache(gc.Database, gc.Code); Dictionary<string, string> oimgs = ImageGrabber.OfflineImagesManager.Instance.GetImages(gc); foreach (string im in imgs) { if (oimgs.ContainsKey(im)) { string p = oimgs[im]; using (SqliteCommand filescmd = new SqliteCommand("", dbconFiles)) { filescmd.CommandText = string.Format("SELECT Fname FROM files WHERE Link='{0}'", im.Replace("'", "''")); object o = filescmd.ExecuteScalar(); if (o == null || o.GetType() == typeof(DBNull)) { filescmd.CommandText = string.Format("insert into files (Link, Fname, Found) values ('{0}', '{1}', 1)", im.Replace("'", "''"), System.IO.Path.GetFileName(p).Replace("'", "''")); filescmd.ExecuteNonQuery(); } } if (Core.Settings.Default.GDAKMaxImagesInFolder > 0) { imgInFolderCount++; if (imgInFolderCount > Core.Settings.Default.GDAKMaxImagesInFolder) { imgFolderIndex++; imgInFolderCount = 1; } string imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex)); if (imgInFolderCount == 1) { if (!System.IO.Directory.Exists(imgSubFolder)) { System.IO.Directory.CreateDirectory(imgSubFolder); } } string dst = System.IO.Path.Combine(imgSubFolder, System.IO.Path.GetFileName(p)); if (!System.IO.File.Exists(dst)) { System.IO.File.Copy(p, dst, true); } } else { string dst = System.IO.Path.Combine(basePath, System.IO.Path.GetFileName(p)); if (!System.IO.File.Exists(dst)) { System.IO.File.Copy(p, dst, true); } } } } } catch (Exception e) { Core.ApplicationData.Instance.Logger.AddLog(this, e); } } index++; if (DateTime.Now>=nextUpdate) { if (!progress.Update("SavingGeocaches", gcList.Count, index)) { break; } nextUpdate = DateTime.Now.AddSeconds(1); } } //trans.Commit(); } } } } if (dbconFiles != null) { dbconFiles.Dispose(); dbconFiles = null; } } } catch (Exception e) { Core.ApplicationData.Instance.Logger.AddLog(this, e); } }); }
public static void ExportToFile(string filename, List<Core.Data.Geocache> gcList) { try { DateTime nextUpdate = DateTime.Now.AddSeconds(1); using (Utils.ProgressBlock fixscr = new Utils.ProgressBlock("ExportingiGeoKnife", "CreatingFile", 1, 0, true)) { System.Collections.Hashtable logTypes = new System.Collections.Hashtable(); logTypes.Add(2, "Found it"); logTypes.Add(3, "Didn't find it"); logTypes.Add(4, "Write note"); logTypes.Add(5, "Archive"); logTypes.Add(7, "Needs Archived"); logTypes.Add(9, "Will Attend"); logTypes.Add(10, "Attended"); logTypes.Add(11, "Webcam Photo Taken"); logTypes.Add(12, "Unarchive"); logTypes.Add(22, "Temporarily Disable Listing"); logTypes.Add(23, "Enable Listing"); logTypes.Add(24, "Publish Listing"); logTypes.Add(25, "Retract Listing"); logTypes.Add(45, "Needs Maintenance"); logTypes.Add(46, "Owner Maintenance"); logTypes.Add(47, "Update Coordinates"); logTypes.Add(68, "Post Reviewer Note"); logTypes.Add(74, "Announcement"); if (System.IO.File.Exists(filename)) { System.IO.File.Delete(filename); } Utils.ResourceHelper.SaveToFile("/iGeoKnife/sqlite.db3", filename, true); using (SqliteConnection dbcon = new SqliteConnection(string.Format("data source=file:{0}", filename))) { dbcon.Open(); DbParameter par; using (Utils.ProgressBlock progress = new Utils.ProgressBlock("ExportingiGeoKnife", "SavingGeocaches", gcList.Count, 0, true)) { using (SqliteCommand cmd = new SqliteCommand("", dbcon)) using (SqliteCommand cmd2 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd3 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd4 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd5 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd6 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd7 = new SqliteCommand("", dbcon)) using (SqliteCommand cmd8 = new SqliteCommand("", dbcon)) { cmd.CommandText = "drop index CachesSmart"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into Caches (Code, Name, PlacedBy, Archived, CacheId, CacheType, Container, Country, Difficulty, Found, HasCorrected, HasUserNote, Latitude, LongHtm, Longitude, OwnerName, PlacedDate, ShortHtm, State, Terrain, UserFlag, IsOwner, LatOriginal, LonOriginal, Status, GcNote, IsPremium, FavPoints) values (@Code, @Name, @PlacedBy, @Archived, @CacheId, @CacheType, @Container, @Country, @Difficulty, @Found, @HasCorrected, @HasUserNote, @Latitude, @LongHtm, @Longitude, @OwnerName, @PlacedDate, @ShortHtm, @State, @Terrain, @UserFlag, @IsOwner, @LatOriginal, @LonOriginal, @Status, @GcNote, @IsPremium, @FavPoints)"; cmd2.CommandText = "insert into CacheMemo (Code, LongDescription, ShortDescription, Url, Hints, UserNote) values (@Code, @LongDescription, @ShortDescription, @Url, @Hints, @UserNote)"; cmd3.CommandText = "insert into Attributes (aCode, aId, aInc) values (@aCode, @aId, @aInc)"; cmd4.CommandText = "insert into LogMemo (lParent, lLogId, lText) values (@lParent, @lLogId, @lText)"; cmd5.CommandText = "insert into Logs (lParent, lLogId, lType, lBy, lDate, lLat, lLon, lEncoded, lownerid, lHasHtml, lIsowner, lTime) values (@lParent, @lLogId, @lType, @lBy, @lDate, @lLat, @lLon, @lEncoded, @lownerid, @lHasHtml, @lIsowner, @lTime)"; cmd6.CommandText = "insert into WayMemo (cParent, cCode, cComment, cUrl) values (@cParent, @cCode, @cComment, @cUrl)"; cmd7.CommandText = "insert into Waypoints (cParent, cCode, cPrefix, cName, cType, cLat, cLon, cByuser, cDate, cFlag, sB1) values (@cParent, @cCode, @cPrefix, @cName, @cType, @cLat, @cLon, @cByuser, @cDate, @cFlag, @sB1)"; cmd8.CommandText = "insert into Corrected (kCode, kBeforeLat, kBeforeLon, kAfterLat, kAfterLon) values (@kCode, @kBeforeLat, @kBeforeLon, @kAfterLat, @kAfterLon)"; par = cmd8.CreateParameter(); par.ParameterName = "@kCode"; par.DbType = DbType.String; cmd8.Parameters.Add(par); par = cmd8.CreateParameter(); par.ParameterName = "@kBeforeLat"; par.DbType = DbType.String; cmd8.Parameters.Add(par); par = cmd8.CreateParameter(); par.ParameterName = "@kBeforeLon"; par.DbType = DbType.String; cmd8.Parameters.Add(par); par = cmd8.CreateParameter(); par.ParameterName = "@kAfterLat"; par.DbType = DbType.String; cmd8.Parameters.Add(par); par = cmd8.CreateParameter(); par.ParameterName = "@kAfterLon"; par.DbType = DbType.String; cmd8.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cParent"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cCode"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cPrefix"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cName"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cType"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cLat"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cLon"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cByuser"; par.DbType = DbType.Boolean; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cDate"; par.DbType = DbType.String; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@cFlag"; par.DbType = DbType.Boolean; cmd7.Parameters.Add(par); par = cmd7.CreateParameter(); par.ParameterName = "@sB1"; par.DbType = DbType.Boolean; cmd7.Parameters.Add(par); par = cmd6.CreateParameter(); par.ParameterName = "@cParent"; par.DbType = DbType.String; cmd6.Parameters.Add(par); par = cmd6.CreateParameter(); par.ParameterName = "@cCode"; par.DbType = DbType.String; cmd6.Parameters.Add(par); par = cmd6.CreateParameter(); par.ParameterName = "@cComment"; par.DbType = DbType.String; cmd6.Parameters.Add(par); par = cmd6.CreateParameter(); par.ParameterName = "@cUrl"; par.DbType = DbType.String; cmd6.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lParent"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lLogId"; par.DbType = DbType.Int32; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lType"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lBy"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lDate"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lLat"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lLon"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lEncoded"; par.DbType = DbType.Boolean; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lownerid"; par.DbType = DbType.Int32; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lHasHtml"; par.DbType = DbType.Boolean; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lIsowner"; par.DbType = DbType.Boolean; cmd5.Parameters.Add(par); par = cmd5.CreateParameter(); par.ParameterName = "@lTime"; par.DbType = DbType.String; cmd5.Parameters.Add(par); par = cmd4.CreateParameter(); par.ParameterName = "@lParent"; par.DbType = DbType.String; cmd4.Parameters.Add(par); par = cmd4.CreateParameter(); par.ParameterName = "@lLogId"; par.DbType = DbType.Int32; cmd4.Parameters.Add(par); par = cmd4.CreateParameter(); par.ParameterName = "@lText"; par.DbType = DbType.String; cmd4.Parameters.Add(par); par = cmd3.CreateParameter(); par.ParameterName = "@aCode"; par.DbType = DbType.String; cmd3.Parameters.Add(par); par = cmd3.CreateParameter(); par.ParameterName = "@aId"; par.DbType = DbType.Int32; cmd3.Parameters.Add(par); par = cmd3.CreateParameter(); par.ParameterName = "@aInc"; par.DbType = DbType.Int32; cmd3.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@Code"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@LongDescription"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@ShortDescription"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@Url"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@Hints"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd2.CreateParameter(); par.ParameterName = "@UserNote"; par.DbType = DbType.String; cmd2.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Code"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Name"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@PlacedBy"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Archived"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@CacheId"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@CacheType"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Container"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Country"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Difficulty"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Found"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@HasCorrected"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@HasUserNote"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Latitude"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@LongHtm"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Longitude"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@OwnerName"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@PlacedDate"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@ShortHtm"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@State"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Terrain"; par.DbType = DbType.Double; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@UserFlag"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@IsOwner"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@LatOriginal"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@LonOriginal"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@Status"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@GcNote"; par.DbType = DbType.String; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@IsPremium"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@FavPoints"; par.DbType = DbType.Int32; cmd.Parameters.Add(par); cmd.Prepare(); cmd2.Prepare(); cmd3.Prepare(); cmd4.Prepare(); cmd5.Prepare(); cmd6.Prepare(); cmd7.Prepare(); cmd8.Prepare(); //using (DbTransaction trans = dbcon.BeginTransaction()) //{ int index = 0; foreach (Core.Data.Geocache gc in gcList) { string notes = ""; if (!string.IsNullOrEmpty(gc.Notes)) { notes = System.Web.HttpUtility.HtmlDecode(gc.Notes); } if (!string.IsNullOrEmpty(gc.PersonalNote)) { notes = string.Concat(notes, gc.PersonalNote); } cmd2.Parameters["@Code"].Value = gc.Code; cmd2.Parameters["@LongDescription"].Value = gc.LongDescription ?? ""; cmd2.Parameters["@ShortDescription"].Value = gc.ShortDescription ?? ""; cmd2.Parameters["@Url"].Value = gc.Url ?? ""; cmd2.Parameters["@Hints"].Value = gc.EncodedHints ?? ""; cmd2.Parameters["@UserNote"].Value = notes; cmd.Parameters["@Code"].Value = gc.Code; cmd.Parameters["@Name"].Value = gc.Name ?? ""; cmd.Parameters["@PlacedBy"].Value = gc.PlacedBy ?? ""; cmd.Parameters["@Archived"].Value = gc.Archived ? 1 : 0; cmd.Parameters["@CacheId"].Value = Utils.Conversion.GetCacheIDFromCacheCode(gc.Code).ToString(); cmd.Parameters["@CacheType"].Value = getCacheType(gc.GeocacheType); cmd.Parameters["@Container"].Value = getContainer(gc.Container); cmd.Parameters["@Country"].Value = gc.Country ?? ""; cmd.Parameters["@Difficulty"].Value = gc.Difficulty; cmd.Parameters["@Found"].Value = gc.Found ? 1 : 0; cmd.Parameters["@HasCorrected"].Value = gc.ContainsCustomLatLon ? 1 : 0; cmd.Parameters["@HasUserNote"].Value = gc.ContainsNote ? 1 : 0; cmd.Parameters["@LatOriginal"].Value = gc.Lat.ToString().Replace(',', '.'); cmd.Parameters["@LonOriginal"].Value = gc.Lon.ToString().Replace(',', '.'); if (gc.ContainsCustomLatLon) { cmd.Parameters["@Latitude"].Value = gc.CustomLat.ToString().Replace(',', '.'); cmd.Parameters["@Longitude"].Value = gc.CustomLon.ToString().Replace(',', '.'); } else { cmd.Parameters["@Latitude"].Value = gc.Lat.ToString().Replace(',', '.'); cmd.Parameters["@Longitude"].Value = gc.Lon.ToString().Replace(',', '.'); } cmd.Parameters["@LongHtm"].Value = gc.LongDescriptionInHtml ? 1 : 0; cmd.Parameters["@OwnerName"].Value = gc.Owner ?? ""; cmd.Parameters["@PlacedDate"].Value = gc.PublishedTime.ToString("yyyy-MM-dd"); cmd.Parameters["@ShortHtm"].Value = gc.ShortDescriptionInHtml ? 1 : 0; cmd.Parameters["@State"].Value = gc.State ?? ""; cmd.Parameters["@Terrain"].Value = gc.Terrain; cmd.Parameters["@UserFlag"].Value = gc.Flagged ? 1 : 0; cmd.Parameters["@IsOwner"].Value = gc.IsOwn ? 1 : 0; cmd.Parameters["@Status"].Value = gc.Available ? "A" : gc.Archived ? "X" : "T"; cmd.Parameters["@GcNote"].Value = notes; cmd.Parameters["@IsPremium"].Value = gc.MemberOnly ? 1 : 0; cmd.Parameters["@FavPoints"].Value = gc.Favorites; cmd.ExecuteNonQuery(); cmd2.ExecuteNonQuery(); if (gc.ContainsCustomLatLon) { cmd8.Parameters["@kCode"].Value = gc.Code; cmd8.Parameters["@kBeforeLat"].Value = gc.Lat.ToString().Replace(',', '.'); cmd8.Parameters["@kBeforeLon"].Value = gc.Lon.ToString().Replace(',', '.'); cmd8.Parameters["@kAfterLat"].Value = gc.CustomLat.ToString().Replace(',', '.'); cmd8.Parameters["@kAfterLon"].Value = gc.CustomLon.ToString().Replace(',', '.'); cmd8.ExecuteNonQuery(); } List<int> attr = gc.AttributeIds; foreach (int att in attr) { cmd3.Parameters["@aCode"].Value = gc.Code; cmd3.Parameters["@aId"].Value = Math.Abs(att); cmd3.Parameters["@aInc"].Value = att < 0 ? 0 : 1; cmd3.ExecuteNonQuery(); } List<Core.Data.Log> logs = Utils.DataAccess.GetLogs(gc.Database, gc.Code).Take(Core.Settings.Default.IGeoKnifeMaxLogs).ToList(); foreach (Core.Data.Log l in logs) { try { int logid = 0; if (!int.TryParse(l.ID, out logid)) { logid = Utils.Conversion.GetCacheIDFromCacheCode(l.ID); } cmd4.Parameters["@lLogId"].Value = logid; cmd4.Parameters["@lText"].Value = l.Text ?? ""; cmd4.Parameters["@lParent"].Value = gc.Code; cmd4.ExecuteNonQuery(); cmd5.Parameters["@lLogId"].Value = logid; cmd5.Parameters["@lParent"].Value = gc.Code; object o = logTypes[l.LogType.ID]; if (o == null) { cmd5.Parameters["@lType"].Value = 4; } else { cmd5.Parameters["@lType"].Value = (string)o; } cmd5.Parameters["@lBy"].Value = l.Finder ?? ""; cmd5.Parameters["@lDate"].Value = l.Date.ToString("yyyy-MM-dd HH:mm:ss"); cmd5.Parameters["@lLat"].Value = DBNull.Value; cmd5.Parameters["@lLon"].Value = DBNull.Value; cmd5.Parameters["@lEncoded"].Value = l.Encoded; try { cmd5.Parameters["@lownerid"].Value = int.Parse(l.FinderId); } catch { } cmd5.Parameters["@lHasHtml"].Value = false; cmd5.Parameters["@lIsowner"].Value = gc.IsOwn; cmd5.Parameters["@lTime"].Value = ""; cmd5.ExecuteNonQuery(); } catch(Exception e) { Core.ApplicationData.Instance.Logger.AddLog(new Export(), e); } } List<Core.Data.Waypoint> wps = Utils.DataAccess.GetWaypointsFromGeocache(gc.Database, gc.Code); foreach (Core.Data.Waypoint w in wps) { try { cmd6.Parameters["@cParent"].Value = gc.Code; cmd6.Parameters["@cCode"].Value = w.Code; cmd6.Parameters["@cComment"].Value = w.Comment; cmd6.Parameters["@cUrl"].Value = w.Url; cmd7.Parameters["@cParent"].Value = gc.Code; cmd7.Parameters["@cCode"].Value = w.Code; cmd7.Parameters["@cPrefix"].Value = w.Code.Substring(0, 2); cmd7.Parameters["@cName"].Value = w.Name ?? ""; cmd7.Parameters["@cType"].Value = getWPType(w.WPType); cmd7.Parameters["@cLat"].Value = w.Lat == null ? "0.0" : w.Lat.ToString().Replace(',', '.'); cmd7.Parameters["@cLon"].Value = w.Lon == null ? "0.0" : w.Lon.ToString().Replace(',', '.'); cmd7.Parameters["@cByuser"].Value = false; cmd7.Parameters["@cDate"].Value = w.Time.ToString("yyyy-MM-dd"); cmd7.Parameters["@cFlag"].Value = false; cmd7.Parameters["@sB1"].Value = false; cmd7.ExecuteNonQuery(); cmd6.ExecuteNonQuery(); } catch(Exception e) { Core.ApplicationData.Instance.Logger.AddLog(new Export(), e); } } index++; if (DateTime.Now>=nextUpdate) { if (!progress.Update("SavingGeocaches", gcList.Count, index)) { break; } nextUpdate = DateTime.Now.AddSeconds(1); } } //trans.Commit(); } } } } } catch(Exception e) { Core.ApplicationData.Instance.Logger.AddLog(new Export(), e); } }
private static bool Import(Database database, string filename) { System.Collections.Hashtable logTypes = new System.Collections.Hashtable(); String connect = String.Format("data source=file:{0}", filename); using (SqliteConnection dbcon = new SqliteConnection(connect)) { //System.Diagnostics.Debugger.Break(); logTypes.Add("Found it", 2); logTypes.Add("Didn't find it", 3); logTypes.Add("Write note", 4); logTypes.Add("Archive", 5); logTypes.Add("Needs Archived", 7); logTypes.Add("Will Attend", 9); logTypes.Add("Attended", 10); logTypes.Add("Webcam Photo Taken", 11); logTypes.Add("Unarchive", 12); logTypes.Add("Temporarily Disable Listing", 22); logTypes.Add("Enable Listing", 23); logTypes.Add("Publish Listing", 24); logTypes.Add("Retract Listing", 25); logTypes.Add("Needs Maintenance", 45); logTypes.Add("Owner Maintenance", 46); logTypes.Add("Update Coordinates", 47); logTypes.Add("Post Reviewer Note", 68); logTypes.Add("Announcement", 74); dbcon.Open(); SqliteCommand lookup = new SqliteCommand("select aId, aInc from attributes where aCode = @Code", dbcon); lookup.CommandType = CommandType.Text; DbParameter par = lookup.CreateParameter(); par.Direction = ParameterDirection.Input; par.ParameterName = "@Code"; lookup.Parameters.Add(par); lookup.Prepare(); SqliteCommand import = new SqliteCommand("select count(1) from caches", dbcon); import.CommandType = CommandType.Text; int index = 0; int gcCount = (int)(long)import.ExecuteScalar(); if (gcCount > 0) { DateTime progShow = DateTime.Now.AddSeconds(1); using (Utils.ProgressBlock prog = new ProgressBlock("Import GSAK database", "Importing...", 1, 0)) { bool isPremiumAvailable = false; bool isFavPointAvailable = false; bool isGCNoteAvailable = false; try { import.CommandText = "select IsPremium from Caches limit 1"; using (SqliteDataReader checkdr = import.ExecuteReader()) { isPremiumAvailable = true; } } catch { } try { import.CommandText = "select FavPoints from Caches limit 1"; using (SqliteDataReader checkdr = import.ExecuteReader()) { isFavPointAvailable = true; } } catch { } try { import.CommandText = "select gcnote from Caches limit 1"; using (SqliteDataReader checkdr = import.ExecuteReader()) { isGCNoteAvailable = true; } } catch { } import.CommandText = "select caches.Code, Name, LastGPXDate, PlacedDate, Latitude, Longitude, Status, " + "Archived, Country, State, CacheType, PlacedBy, OwnerName, OwnerId, Container, Terrain, Difficulty, ShortHTM" + ", LongHTM, " + string.Format("{0}", isPremiumAvailable ? "IsPremium, " : "") + " HasCorrected, LatOriginal, LonOriginal, UserFlag, Found, " + string.Format("{0}", isFavPointAvailable ? "FavPoints, " : "") + " ShortDescription, LongDescription, Hints, Url, UserNote" + string.Format("{0}", isGCNoteAvailable ? ", gcnote" : "") + " from caches" + " inner join cachememo on cachememo.code = caches.code"; SqliteDataReader dr = import.ExecuteReader(); while (dr.Read()) { GeocacheData gc = new GeocacheData(); int cacheType; try { cacheType = getCacheType(((String)dr["CacheType"])[0]); } catch (ArgumentOutOfRangeException) { continue; } int container = getContainer(((String)dr["Container"])[0]); gc.Code = (string)dr["code"]; gc.Name = (string)dr["name"]; gc.DataFromDate = DateTime.Parse((string)dr["LastGPXDate"]); gc.Available = ((String)dr["Status"]).Equals("A"); gc.Archived = (int)dr["archived"] != 0; gc.Country = (string)dr["country"]; gc.State = (string)dr["state"]; gc.GeocacheType = Utils.DataAccess.GetGeocacheType(cacheType); gc.PlacedBy = (string)dr["placedby"]; gc.Owner = (string)dr["OwnerName"]; gc.OwnerId = dr["ownerid"].GetType() == typeof(DBNull) ? "" : dr["ownerid"].ToString(); gc.Container = Utils.DataAccess.GetGeocacheContainer(container); gc.Terrain = (double)dr["terrain"]; gc.Difficulty = (double)dr["difficulty"]; gc.ShortDescription = (string)dr["ShortDescription"]; gc.ShortDescriptionInHtml = (int)dr["ShortHTM"] != 0; gc.LongDescription = (string)dr["LongDescription"]; gc.LongDescriptionInHtml = (int)dr["LongHTM"] != 0; gc.EncodedHints = (string)dr["Hints"]; gc.Url = (string)dr["url"]; if (isPremiumAvailable) { gc.MemberOnly = (int)dr["IsPremium"] != 0; } else { gc.MemberOnly = false; } bool customCoords = (int)dr["HasCorrected"] != 0; if (customCoords) { gc.CustomLat = Utils.Conversion.StringToDouble(dr["Latitude"] as String); gc.CustomLon = Utils.Conversion.StringToDouble(dr["Longitude"] as String); gc.Lat = Utils.Conversion.StringToDouble(dr["LatOriginal"] as string); gc.Lon = Utils.Conversion.StringToDouble(dr["LonOriginal"] as string); } else { gc.Lat = Utils.Conversion.StringToDouble(dr["Latitude"] as string); gc.Lon = Utils.Conversion.StringToDouble(dr["Longitude"] as string); } par.Value = gc.Code; DbDataReader attrs = lookup.ExecuteReader(); List<int> attrList = new List<int>(); while (attrs.Read()) { int attr = (int)(int)attrs["aId"]; if (attrs["aInc"].ToString() == "0") { attr *= -1; } attrList.Add(attr); } attrs.Close(); gc.AttributeIds = attrList; gc.Notes = (string)dr["UserNote"]; gc.PublishedTime = DateTime.Parse((string)dr["PlacedDate"]); if (isGCNoteAvailable) { gc.PersonalNote = (string)dr["gcnote"]; } else { gc.PersonalNote = ""; } gc.Flagged = (int)dr["UserFlag"] != 0; gc.Found = (int)dr["Found"] != 0; if (isFavPointAvailable) { gc.Favorites = (int)(int)dr["FavPoints"]; } else { gc.Favorites = 0; } DataAccess.AddGeocache(database, gc); index++; if (DateTime.Now >= progShow) { prog.Update("Importing", gcCount, index); progShow = DateTime.Now.AddSeconds(1); } } dr.Close(); } } } return true; }