private void DlgSearchReplace_Loaded(object sender, RoutedEventArgs e)
{
try
{
PNLang.Instance.ApplyControlLanguage(this);
if (_Mode == SearchReplace.Search)
{
Title = PNLang.Instance.GetCaptionText("dlg_search_find", "Find");
lblReplace.Visibility =
cboReplace.Visibility =
cmdReplace.Visibility = cmdReplaceAll.Visibility = Visibility.Collapsed;
}
else
{
Title = PNLang.Instance.GetCaptionText("dlg_search_replace", "Replace");
}
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
using (var t = oData.FillDataTable("SELECT FIND, REPLACE FROM FIND_REPLACE"))
{
if (t.Rows.Count > 0)
{
if (!PNData.IsDBNull(t.Rows[0]["FIND"]))
{
var values = (Convert.ToString(t.Rows[0]["FIND"])).Split(',');
foreach (var s in values)
{
cboFind.Items.Add(s);
}
}
if (!PNData.IsDBNull(t.Rows[0]["REPLACE"]))
{
var values = (Convert.ToString(t.Rows[0]["REPLACE"])).Split(',');
foreach (var s in values)
{
cboReplace.Items.Add(s);
}
}
}
}
}
cboFind.Text = _Edit.SelectionLength > 0 ? _Edit.SelectedText : PNStatic.FindString;
cboReplace.Text = PNStatic.ReplaceString;
chkRegExp.IsChecked = PNStatic.SearchMode == SearchMode.RegularExp;
cboFind.Focus();
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
private List <string> deletedIDs(SQLiteDataObject oData)
{
var list = new List <string>();
try
{
using (var t = oData.FillDataTable("SELECT ID FROM NOTES WHERE GROUP_ID = " + (int)SpecialGroups.RecycleBin))
{
list.AddRange(from DataRow r in t.Rows select(string) r["ID"]);
}
return(list);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return(list);
}
}
private void DlgSearchInNotes_Loaded(object sender, RoutedEventArgs e)
{
try
{
applyLanguage();
PNStatic.FormMain.LanguageChanged += FormMain_LanguageChanged;
PNStatic.FormMain.NoteBooleanChanged += FormMain_NoteBooleanChanged;
PNStatic.FormMain.NoteNameChanged += FormMain_NoteNameChanged;
PNStatic.FormMain.NoteScheduleChanged += FormMain_NoteScheduleChanged;
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
using (var t = oData.FillDataTable("SELECT FIND, REPLACE FROM FIND_REPLACE"))
{
if (t.Rows.Count <= 0)
{
return;
}
if (!PNData.IsDBNull(t.Rows[0]["FIND"]))
{
var values = Convert.ToString(t.Rows[0]["FIND"]).Split(',');
foreach (var s in values)
{
cboFind.Items.Add(s);
}
}
if (!PNData.IsDBNull(t.Rows[0]["REPLACE"]))
{
var values = Convert.ToString(t.Rows[0]["REPLACE"]).Split(',');
foreach (var s in values)
{
cboReplace.Items.Add(s);
}
}
}
}
tvwResults.ItemsSource = _FoundItems;
setSettings();
cboFind.Focus();
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
//todo
private bool exchangeNotes(SQLiteDataObject dataSrc, SQLiteDataObject dataDest, string id, KeyValuePair<string, List<_FieldData>> tableData)
{
try
{
using (var t1 = dataSrc.FillDataTable("SELECT * FROM NOTES WHERE ID = '" + id + "'"))
{
using (var t2 = dataDest.FillDataTable("SELECT * FROM NOTES WHERE ID = '" + id + "'"))
{
if (t1.Rows.Count > 0 && t2.Rows.Count > 0)
{
var d1 = Convert.ToInt64(t1.Rows[0]["UPD_DATE"]);
var d2 = Convert.ToInt64(t2.Rows[0]["UPD_DATE"]);
if (d1 > d2)
{
return insertToNotes(dataDest, t1.Rows[0], tableData);
}
if (d2 > d1)
{
return insertToNotes(dataSrc, t2.Rows[0], tableData);
}
}
else if (t1.Rows.Count > 0)
{
return insertToNotes(dataDest, t1.Rows[0], tableData);
}
else if (t2.Rows.Count > 0)
{
return insertToNotes(dataSrc, t2.Rows[0], tableData);
}
}
}
return true;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return false;
}
}
private bool exchangeCustomNotesSettings(SQLiteDataObject dataSrc, SQLiteDataObject dataDest, string id, KeyValuePair <string, List <_FieldData> > tableData)
{
try
{
using (var t1 = dataSrc.FillDataTable("SELECT * FROM CUSTOM_NOTES_SETTINGS WHERE NOTE_ID = '" + id + "'"))
{
using (var t2 = dataDest.FillDataTable("SELECT * FROM CUSTOM_NOTES_SETTINGS WHERE NOTE_ID = '" + id + "'"))
{
if (t1.Rows.Count > 0 && t2.Rows.Count > 0)
{
var d1 = Convert.ToInt64(t1.Rows[0]["UPD_DATE"]);
var d2 = Convert.ToInt64(t2.Rows[0]["UPD_DATE"]);
if (d1 > d2)
{
return(insertToCustomNotesSettings(dataDest, t1.Rows[0], tableData));
}
if (d2 > d1)
{
return(insertToCustomNotesSettings(dataSrc, t2.Rows[0], tableData));
}
}
else if (t1.Rows.Count > 0)
{
return(insertToCustomNotesSettings(dataDest, t1.Rows[0], tableData));
}
else if (t2.Rows.Count > 0)
{
return(insertToCustomNotesSettings(dataSrc, t2.Rows[0], tableData));
}
}
}
return(true);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return(false);
}
}
internal static void RearrangeMenus(ContextMenu ctm)
{
try
{
var tableList = new List<Tuple<string, string, int>>();
var tempMenus = new List<Control>();
var sb =
new StringBuilder("SELECT MENU_NAME, IFNULL(PARENT_NAME, '') AS PARENT_NAME, ORDER_NEW FROM MENUS_ORDER WHERE CONTEXT_NAME = '");
sb.Append(ctm.Name);
sb.Append("' ORDER BY ORDER_NEW");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
using (var t = oData.FillDataTable(sb.ToString()))
{
tableList.AddRange(from DataRow r in t.Rows
select
Tuple.Create(Convert.ToString(r["MENU_NAME"]), Convert.ToString(r["PARENT_NAME"]),
Convert.ToInt32(r["ORDER_NEW"])));
tempMenus.AddRange(ctm.Items.Cast<Control>());
ctm.Items.Clear();
foreach (
var tm in
tableList.Where(tb => tb.Item2 == "")
.Select(tp => tempMenus.FirstOrDefault(m => m.Name == tp.Item1))
.Where(tm => tm != null))
{
ctm.Items.Add(tm);
addSubitem(tableList, tm);
}
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
private void DlgSearchInNotes_Loaded(object sender, RoutedEventArgs e)
{
try
{
applyLanguage();
PNStatic.FormMain.LanguageChanged += FormMain_LanguageChanged;
PNStatic.FormMain.NoteBooleanChanged += FormMain_NoteBooleanChanged;
PNStatic.FormMain.NoteNameChanged += FormMain_NoteNameChanged;
PNStatic.FormMain.NoteScheduleChanged += FormMain_NoteScheduleChanged;
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
using (var t = oData.FillDataTable("SELECT FIND, REPLACE FROM FIND_REPLACE"))
{
if (t.Rows.Count <= 0) return;
if (!PNData.IsDBNull(t.Rows[0]["FIND"]))
{
var values = Convert.ToString(t.Rows[0]["FIND"]).Split(',');
foreach (var s in values)
cboFind.Items.Add(s);
}
if (!PNData.IsDBNull(t.Rows[0]["REPLACE"]))
{
var values = Convert.ToString(t.Rows[0]["REPLACE"]).Split(',');
foreach (var s in values)
cboReplace.Items.Add(s);
}
}
}
tvwResults.ItemsSource = _FoundItems;
setSettings();
cboFind.Focus();
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void RearrangeMenus(ContextMenu ctm)
{
try
{
var tableList = new List <Tuple <string, string, int> >();
var tempMenus = new List <Control>();
var sb =
new StringBuilder("SELECT MENU_NAME, IFNULL(PARENT_NAME, '') AS PARENT_NAME, ORDER_NEW FROM MENUS_ORDER WHERE CONTEXT_NAME = '");
sb.Append(ctm.Name);
sb.Append("' ORDER BY ORDER_NEW");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
using (var t = oData.FillDataTable(sb.ToString()))
{
tableList.AddRange(from DataRow r in t.Rows
select
Tuple.Create(Convert.ToString(r["MENU_NAME"]), Convert.ToString(r["PARENT_NAME"]),
Convert.ToInt32(r["ORDER_NEW"])));
tempMenus.AddRange(ctm.Items.Cast <Control>());
ctm.Items.Clear();
foreach (
var tm in
tableList.Where(tb => tb.Item2 == "")
.Select(tp => tempMenus.FirstOrDefault(m => m.Name == tp.Item1))
.Where(tm => tm != null))
{
ctm.Items.Add(tm);
addSubitem(tableList, tm);
}
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal void SyncLocal(object localPaths)
{
var result = LocalSyncResult.Reload;
var filesSrc = new List<_NoteFile>();
var filesDest = new List<_NoteFile>();
var tempDir = "";
try
{
if (PNStatic.Settings.Network.SaveBeforeSync)
{
PNStatic.FormMain.ApplyAction(MainDialogAction.SaveAll, null);
}
var paths = localPaths as string[];
if (paths == null || paths.Length != 2)
{
throw new ArgumentException("Invalid local paths");
}
var destDir = paths[0].Trim();
var destDB = paths[1].Trim() != "" ? paths[1].Trim() : destDir;
destDB = Path.Combine(destDB, PNStrings.DB_FILE);
// create temp directory
tempDir = createTempDir();
var tempDBSrc = Path.Combine(tempDir, PNStrings.DB_FILE);
var tempDBDest = Path.Combine(tempDir, PNStrings.TEMP_DB_FILE);
// copy source db
File.Copy(PNPaths.Instance.DBPath, tempDBSrc, true);
// build source connection string
var srcConnectionString = "data source=\"" + tempDBSrc + "\"";
//string srcConnectionString = "metadata=res://*/PNModel.csdl|res://*/PNModel.ssdl|res://*/PNModel.msl;provider=System.Data.SQLite;provider connection string='data source=\"" + tempDBSrc + "\"'";
using (var eSrc = new SQLiteDataObject(srcConnectionString))
{
// drop triggers
eSrc.Execute(PNStrings.DROP_TRIGGERS);
// get listNames of all source notes files
var srcNotes = new DirectoryInfo(PNPaths.Instance.DataDir).GetFiles("*" + PNStrings.NOTE_EXTENSION);
filesSrc.AddRange(srcNotes.Select(fi => new _NoteFile { Path = fi.FullName, Name = fi.Name, Copy = false }));
// get deleted ids
if (!PNStatic.Settings.Network.IncludeBinInSync)
{
var deletedSrc = deletedIDs(eSrc);
filesSrc.RemoveAll(nf => deletedSrc.Contains(Path.GetFileNameWithoutExtension(nf.Name)));
}
// get listNames of all destination notes files
var destNotes = new DirectoryInfo(destDir).GetFiles("*" + PNStrings.NOTE_EXTENSION);
filesDest.AddRange(destNotes.Select(fir => new _NoteFile { Path = fir.FullName, Name = fir.Name, Copy = false }));
if (File.Exists(destDB))
{
// copy destination db to temp directory
File.Copy(destDB, tempDBDest, true);
// build connection string
var destConnectionString = "data source=\"" + tempDBDest + "\"";
using (var eDest = new SQLiteDataObject(destConnectionString))
{
// drop triggers
eDest.Execute(PNStrings.DROP_TRIGGERS);
if (areTablesDifferent(eSrc, eDest))
{
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(LocalSyncResult.AbortVersion));
}
return;
}
//todo: create tables data
var tablesData = new Dictionary<string, List<_FieldData>>();
foreach (var tn in _Tables)
{
var td = new List<_FieldData>();
var sb = new StringBuilder("pragma table_info('");
sb.Append(tn);
sb.Append("')");
using (var t = eSrc.FillDataTable(sb.ToString()))
{
td.AddRange(from DataRow r in t.Rows
select new _FieldData
{
Name = Convert.ToString(r["name"]),
Type = Convert.ToString(r["type"]),
NotNull = Convert.ToBoolean(r["notnull"])
});
}
tablesData.Add(tn, td);
}
// get deleted ids
if (!PNStatic.Settings.Network.IncludeBinInSync)
{
var deletedDest = deletedIDs(eDest);
filesDest.RemoveAll(nf => deletedDest.Contains(Path.GetFileNameWithoutExtension(nf.Name)));
}
foreach (var sf in filesSrc)
{
var id = Path.GetFileNameWithoutExtension(sf.Name);
// find destination file with same name
var df = filesDest.FirstOrDefault(f => f.Name == sf.Name);
if (df == null)
{
sf.Copy = true;
//todo
if (!insertToAllTables(eSrc, eDest, id, tablesData))
{
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(LocalSyncResult.Error));
}
return;
}
}
else
{
// check which note is more up to date
var dSrc = File.GetLastWriteTime(sf.Path);
var dDest = File.GetLastWriteTime(df.Path);
if (dSrc > dDest)
{
// compare two files
if (areFilesDifferent(sf.Path, df.Path))
{
// local file is younger then remote - copy it to remote client
sf.Copy = true;
}
}
else if (dSrc < dDest)
{
// compare two files
if (areFilesDifferent(sf.Path, df.Path))
{
// remote file is younger than local - copy it to local directory
df.Copy = true;
}
}
//todo
if (!exchangeData(eSrc, eDest, id, tablesData))
{
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(LocalSyncResult.Error));
}
return;
}
}
}
// check remaining destination files
var remDest = filesDest.Where(df => !df.Copy);
foreach (var df in remDest)
{
if (filesSrc.All(sf => sf.Name != df.Name))
{
df.Copy = true;
var id = Path.GetFileNameWithoutExtension(df.Name);
//todo
if (!exchangeData(eSrc, eDest, id, tablesData))
{
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(LocalSyncResult.Error));
}
return;
}
}
}
// synchronize groups
if (!exchangeGroups(eSrc, eDest, tablesData.FirstOrDefault(td => td.Key == "GROUPS")))
{
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(LocalSyncResult.Error));
}
return;
}
// restore triggers
eSrc.Execute(PNStrings.CREATE_TRIGGERS);
eDest.Execute(PNStrings.CREATE_TRIGGERS);
}
// copy files
var filesToCopy = filesSrc.Where(sf => sf.Copy);
foreach (var sf in filesToCopy)
{
var newPath = Path.Combine(destDir, sf.Name);
File.Copy(sf.Path, newPath, true);
}
filesToCopy = filesDest.Where(df => df.Copy);
foreach (var df in filesToCopy)
{
var newPath = Path.Combine(PNPaths.Instance.DataDir, df.Name);
File.Copy(df.Path, newPath, true);
}
if (filesDest.Count(df => df.Copy) == 0) result = LocalSyncResult.None;
// copy synchronized db files
File.Copy(tempDBSrc, PNPaths.Instance.DBPath, true);
File.Copy(tempDBDest, destDB, true);
}
else
{
// restore triggers
eSrc.Execute(PNStrings.CREATE_TRIGGERS);
// just copy all notes files and db file to remote client
File.Copy(PNPaths.Instance.DBPath, destDB, true);
foreach (var sf in filesSrc)
{
var newPath = Path.Combine(destDir, sf.Name);
File.Copy(sf.Path, newPath, true);
}
result = LocalSyncResult.None;
}
}
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(result));
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(LocalSyncResult.Error));
}
}
finally
{
if (tempDir != "" && Directory.Exists(tempDir))
{
Directory.Delete(tempDir, true);
}
}
}
private void DlgSearchReplace_Loaded(object sender, RoutedEventArgs e)
{
try
{
PNLang.Instance.ApplyControlLanguage(this);
if (_Mode == SearchReplace.Search)
{
Title = PNLang.Instance.GetCaptionText("dlg_search_find", "Find");
lblReplace.Visibility =
cboReplace.Visibility =
cmdReplace.Visibility = cmdReplaceAll.Visibility = Visibility.Collapsed;
}
else
{
Title = PNLang.Instance.GetCaptionText("dlg_search_replace", "Replace");
}
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
using (var t = oData.FillDataTable("SELECT FIND, REPLACE FROM FIND_REPLACE"))
{
if (t.Rows.Count > 0)
{
if (!PNData.IsDBNull(t.Rows[0]["FIND"]))
{
var values = (Convert.ToString(t.Rows[0]["FIND"])).Split(',');
foreach (var s in values)
cboFind.Items.Add(s);
}
if (!PNData.IsDBNull(t.Rows[0]["REPLACE"]))
{
var values = (Convert.ToString(t.Rows[0]["REPLACE"])).Split(',');
foreach (var s in values)
cboReplace.Items.Add(s);
}
}
}
}
cboFind.Text = _Edit.SelectionLength > 0 ? _Edit.SelectedText : PNStatic.FindString;
cboReplace.Text = PNStatic.ReplaceString;
chkRegExp.IsChecked = PNStatic.SearchMode == SearchMode.RegularExp;
cboFind.Focus();
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static bool LoadLinkedNotes(PNote note)
{
try
{
var sqlQuery = "SELECT LINK_ID FROM LINKED_NOTES WHERE NOTE_ID = '" + note.ID + "'";
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
using (var t = oData.FillDataTable(sqlQuery))
{
foreach (DataRow r in t.Rows)
{
note.LinkedNotes.Add((string)r["LINK_ID"]);
}
}
}
return true;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return false;
}
}
internal static void NormalizeCustomNotesTable(SQLiteDataObject oData)
{
try
{
var listSql = new List<string>();
var lfc = new LogFontConverter();
var wfc = new WPFFontConverter();
var drawingColorConverter = new System.Drawing.ColorConverter();
var mediaColorConverter = new ColorConverter();
using (
var t =
oData.FillDataTable(
"SELECT NOTE_ID, CAPTION_FONT FROM CUSTOM_NOTES_SETTINGS WHERE CAPTION_FONT LIKE '%^%'"))
{
foreach (DataRow r in t.Rows)
{
var lf = lfc.ConvertFromString((string)r["CAPTION_FONT"]);
var fnt = PNStatic.FromLogFont(lf);
var sb = new StringBuilder("UPDATE CUSTOM_NOTES_SETTINGS SET CAPTION_FONT = '");
sb.Append(wfc.ConvertToString(null, PNStatic.CultureInvariant, fnt));
sb.Append("' WHERE NOTE_ID = ");
sb.Append(r["NOTE_ID"]);
listSql.Add(sb.ToString());
}
}
using (var t = oData.FillDataTable("SELECT NOTE_ID, BACK_COLOR, CAPTION_FONT_COLOR FROM CUSTOM_NOTES_SETTINGS"))
{
var backColors = new List<TempColor>();
var capColors = new List<TempColor>();
foreach (DataRow r in t.Rows)
{
if (!IsDBNull(r["BACK_COLOR"]))
backColors.Add(new TempColor
{
Color = Convert.ToString(r["BACK_COLOR"]),
Id = Convert.ToString(r["NOTE_ID"])
});
if (!IsDBNull(r["CAPTION_FONT_COLOR"]))
capColors.Add(new TempColor
{
Color = Convert.ToString(r["CAPTION_FONT_COLOR"]),
Id = Convert.ToString(r["NOTE_ID"])
});
}
foreach (var cr in backColors.Where(c => !IsMediaColor(c.Color)))
{
var convertFromString = drawingColorConverter.ConvertFromString(null, PNStatic.CultureInvariant, cr.Color);
if (convertFromString == null) continue;
var clr = (System.Drawing.Color)convertFromString;
var color = Color.FromArgb(clr.A, clr.R, clr.G, clr.B);
var sb = new StringBuilder("UPDATE CUSTOM_NOTES_SETTINGS SET BACK_COLOR = '");
sb.Append(mediaColorConverter.ConvertToString(null, PNStatic.CultureInvariant, color));
sb.Append("' WHERE NOTE_ID = ");
sb.Append(cr.Id);
listSql.Add(sb.ToString());
}
foreach (var cr in capColors.Where(c => !IsMediaColor(c.Color)))
{
var convertFromString = drawingColorConverter.ConvertFromString(null, PNStatic.CultureInvariant, cr.Color);
if (convertFromString == null) continue;
var clr = (System.Drawing.Color)convertFromString;
var color = Color.FromArgb(clr.A, clr.R, clr.G, clr.B);
var sb = new StringBuilder("UPDATE CUSTOM_NOTES_SETTINGS SET CAPTION_FONT_COLOR = '");
sb.Append(mediaColorConverter.ConvertToString(null, PNStatic.CultureInvariant, color));
sb.Append("' WHERE NOTE_ID = ");
sb.Append(cr.Id);
listSql.Add(sb.ToString());
}
}
if (listSql.Count == 0) return;
ExecuteTransactionForList(listSql, ConnectionString);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void LoadDBSettings()
{
try
{
var mediaConverter = new ColorConverter();
var drawingConverter = new System.Drawing.ColorConverter();
var sc = new SizeConverter();
var pc = new PointConverter();
var wpfc = new WPFFontConverter();
//var fc = new FontConverter();
var v = Assembly.GetExecutingAssembly().GetName().Version;
PNStatic.Settings = new PNSettings();
SettingsConnectionString = SQLiteDataObject.CheckAndCreateDatabase(PNPaths.Instance.SettingsDBPath);
using (var oData = new SQLiteDataObject(SettingsConnectionString))
{
string sqlQuery;
//config
var pnc = PNStatic.Settings.Config;
if (!oData.TableExists("CONFIG"))
{
sqlQuery = "CREATE TABLE [CONFIG] ([LAST_PAGE] TEXT, [EXIT_FLAG] INT, [CP_LAST_GROUP] INT, [SKINNABLE] BOOLEAN, [CP_PVW_COLOR] TEXT, [CP_USE_CUST_PVW_COLOR] BOOLEAN, [CP_SIZE] TEXT, [CP_LOCATION] TEXT, [CONTROLS_STYLE] TEXT, [CP_PVW_RIGHT] BOOLEAN, [UI_FONT] TEXT, [PROGRAM_VERSION] TEXT, [CP_PVW_SHOW] BOOLEAN, [CP_GROUPS_SHOW] BOOLEAN, [NOTES_WITH_SHORTCUTS] TEXT, [SEARCH_NOTES_SETT] TEXT)";
oData.Execute(sqlQuery);
sqlQuery =
"INSERT INTO CONFIG VALUES(NULL, -1, NULL, NULL, NULL, NULL, '1000,600', NULL, NULL, NULL, NULL, '" +
v.ToString(3) + "', NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
PNSingleton.Instance.FontUser = new PNFont();
pnc.CPSize = new Size(1000, 600);
}
else
{
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'PROGRAM_VERSION' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
PNSingleton.Instance.PlatformChanged = true;
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [PROGRAM_VERSION] TEXT";
oData.Execute(sqlQuery);
//save previous edition files
PNStatic.SpTextProvider.SplashText = PNLang.Instance.GetMessageText("back_prev",
"Backing up files from previous edition...");
savePreviousFiles();
}
rows = t.Select("COLUMN_NAME = 'CP_PVW_RIGHT' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [CP_PVW_RIGHT] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'CP_PVW_SHOW' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [CP_PVW_SHOW] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'CP_GROUPS_SHOW' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [CP_GROUPS_SHOW] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'UI_FONT' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [UI_FONT] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'NOTES_WITH_SHORTCUTS' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [NOTES_WITH_SHORTCUTS] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'SEARCH_NOTES_SETT' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [SEARCH_NOTES_SETT] TEXT";
oData.Execute(sqlQuery);
}
}
//store version
sqlQuery = "UPDATE CONFIG SET PROGRAM_VERSION = '" + v.ToString(3) + "'";
oData.Execute(sqlQuery);
//upgrade colors an fonts
if (PNSingleton.Instance.PlatformChanged)
{
var obj = oData.GetScalar("SELECT CP_PVW_COLOR FROM CONFIG");
if (obj != null && !IsDBNull(obj))
{
obj = drawingConverter.ConvertFromString(null, PNStatic.CultureInvariant, (string)obj);
if (obj != null)
{
var clrD = (System.Drawing.Color)obj;
var clrM = Color.FromArgb(clrD.A, clrD.R, clrD.G, clrD.B);
var clrText = mediaConverter.ConvertToString(clrM);
oData.Execute("UPDATE CONFIG SET CP_PVW_COLOR = '" + clrText + "'");
}
}
obj = oData.GetScalar("SELECT UI_FONT FROM CONFIG");
if (obj != null && !IsDBNull(obj))
{
var pnFonf = PNStatic.FromDrawingFont((string)obj);
var fontText = wpfc.ConvertToString(pnFonf);
oData.Execute("UPDATE CONFIG SET UI_FONT = '" + fontText + "'");
}
}
sqlQuery = "SELECT * FROM CONFIG";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
if (!IsDBNull(r["LAST_PAGE"]))
{
pnc.LastPage = Convert.ToInt32(r["LAST_PAGE"]);
}
if (!IsDBNull(r["EXIT_FLAG"]))
{
pnc.ExitFlag = (int)r["EXIT_FLAG"];
}
if (!IsDBNull(r["CP_LAST_GROUP"]))
{
pnc.CPLastGroup = (int)r["CP_LAST_GROUP"];
}
if (!IsDBNull(r["SKINNABLE"]))
{
pnc.Skinnable = Convert.ToBoolean(r["SKINNABLE"]);
}
if (!IsDBNull(r["CP_PVW_COLOR"]))
{
var convertFromString = mediaConverter.ConvertFromString(null, PNStatic.CultureInvariant, (string)r["CP_PVW_COLOR"]);
if (
convertFromString != null)
pnc.CPPvwColor = (Color)convertFromString;
}
if (!IsDBNull(r["CP_USE_CUST_PVW_COLOR"]))
{
pnc.CPUseCustPvwColor = Convert.ToBoolean(r["CP_USE_CUST_PVW_COLOR"]);
}
if (!IsDBNull(r["CP_SIZE"]))
{
var str = Convert.ToString(r["CP_LOCATION"]);
if (!str.Contains('-'))
{
str = Convert.ToString(r["CP_SIZE"]);
var convertFromString = sc.ConvertFromString(null, PNStatic.CultureInvariant,
str);
if (convertFromString != null)
pnc.CPSize = (Size)convertFromString;
}
}
if (!IsDBNull(r["CP_LOCATION"]))
{
var convertFromString = pc.ConvertFromString(null, PNStatic.CultureInvariant, (string)r["CP_LOCATION"]);
if (
convertFromString != null)
pnc.CPLocation = (Point)convertFromString;
}
if (!IsDBNull(r["CONTROLS_STYLE"]))
{
pnc.ControlsStyle = (string)r["CONTROLS_STYLE"];
}
if (!IsDBNull(r["CP_PVW_RIGHT"]))
{
pnc.CPPvwRight = Convert.ToBoolean(r["CP_PVW_RIGHT"]);
}
if (!IsDBNull(r["CP_PVW_SHOW"]))
{
pnc.CPPvwShow = Convert.ToBoolean(r["CP_PVW_SHOW"]);
}
if (!IsDBNull(r["CP_GROUPS_SHOW"]))
{
pnc.CPGroupsShow = Convert.ToBoolean(r["CP_GROUPS_SHOW"]);
}
if (!IsDBNull(r["UI_FONT"]))
{
var temp = (string)(r["UI_FONT"]);
if (temp != "")
{
PNSingleton.Instance.FontUser = (PNFont)wpfc.ConvertFromString(temp);
}
else
{
PNSingleton.Instance.FontUser = new PNFont();
}
}
else
{
PNSingleton.Instance.FontUser = new PNFont();
}
if (!IsDBNull(r["SEARCH_NOTES_SETT"]))
{
var arr = Convert.ToString(r["SEARCH_NOTES_SETT"]).Split('|');
pnc.SearchNotesSettings.WholewWord = Convert.ToBoolean(arr[0]);
pnc.SearchNotesSettings.MatchCase = Convert.ToBoolean(arr[1]);
pnc.SearchNotesSettings.IncludeHidden = Convert.ToBoolean(arr[2]);
pnc.SearchNotesSettings.Criteria = Convert.ToInt32(arr[3]);
pnc.SearchNotesSettings.Scope = Convert.ToInt32(arr[4]);
}
SaveExitFlag(-1);
}
else
{
sqlQuery =
"INSERT INTO CONFIG VALUES(NULL, -1, NULL, NULL, NULL, NULL, '1000,600', NULL, NULL, NULL, NULL, '" +
v.ToString(3) + "', NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
PNSingleton.Instance.FontUser = new PNFont();
pnc.CPSize = new Size(1000, 600);
}
}
}
//general setting
var pngeneral = PNStatic.Settings.GeneralSettings;
if (!oData.TableExists("GENERAL_SETTINGS"))
{
sqlQuery = "CREATE TABLE [GENERAL_SETTINGS] ([LANGUAGE] TEXT, [RUN_ON_START] BOOLEAN, [SHOW_CP_ON_START] BOOLEAN, [CHECK_NEW_VERSION_ON_START] BOOLEAN, [HIDE_TOOLBAR] BOOLEAN, [USE_CUSTOM_FONTS] BOOLEAN, [SHOW_SCROLLBAR] BOOLEAN, [HIDE_DELETE_BUTTON] BOOLEAN, [CHANGE_HIDE_TO_DELETE] BOOLEAN, [HIDE_HIDE_BUTTON] BOOLEAN, [BULLETS_INDENT] INT, [MARGIN_WIDTH] INT, [SAVE_ON_EXIT] BOOLEAN, [CONFIRM_SAVING] BOOLEAN, [CONFIRM_BEFORE_DELETION] BOOLEAN, [SAVE_WITHOUT_CONFIRM_ON_HIDE] BOOLEAN, [WARN_ON_AUTOMATICAL_DELETE] BOOLEAN, [AUTO_SAVE] BOOLEAN, [AUTO_SAVE_PERIOD] INT, [REMOVE_FROM_BIN_PERIOD] INT, [DATE_FORMAT] TEXT, [TIME_FORMAT] TEXT, [SKINLESS_WIDTH] INT, [SKINLESS_HEIGHT] INT, [SPELL_COLOR] TEXT, [USE_SKINS] BOOLEAN, [SPELL_MODE] INT, [SPELL_DICT] TEXT, [DOCK_WIDTH] INT, [DOCK_HEIGHT] INT, [SHOW_PRIORITY_ON_START] BOOLEAN, [BUTTONS_SIZE] INT, [AUTOMATIC_SMILIES] BOOLEAN, [SPACE_POINTS] INT, [RESTORE_AUTO] BOOLEAN, [PARAGRAPH_INDENT] INT, [AUTO_HEIGHT] BOOLEAN, [CRITICAL_ON_START] BOOLEAN, [CRITICAL_PERIODICALLY] BOOLEAN, [DELETE_SHORTCUTS_ON_EXIT] BOOLEAN, [RESTORE_SHORTCUTS_ON_START] BOOLEAN, [CLOSE_ON_SHORTCUT] BOOLEAN)";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO GENERAL_SETTINGS VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
//store default english language
var langPath = Path.Combine(PNPaths.Instance.LangDir, pngeneral.Language);
PNLang.Instance.LoadLanguage(langPath);
}
else
{
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'BUTTONS_SIZE' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [BUTTONS_SIZE] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'AUTOMATIC_SMILIES' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [AUTOMATIC_SMILIES] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'SPACE_POINTS' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [SPACE_POINTS] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'RESTORE_AUTO' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [RESTORE_AUTO] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PARAGRAPH_INDENT' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [PARAGRAPH_INDENT] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'AUTO_HEIGHT' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [AUTO_HEIGHT] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'CRITICAL_ON_START' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [CRITICAL_ON_START] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'CRITICAL_PERIODICALLY' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [CRITICAL_PERIODICALLY] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'DELETE_SHORTCUTS_ON_EXIT' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [DELETE_SHORTCUTS_ON_EXIT] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'RESTORE_SHORTCUTS_ON_START' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [RESTORE_SHORTCUTS_ON_START] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'CLOSE_ON_SHORTCUT' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [CLOSE_ON_SHORTCUT] BOOLEAN";
oData.Execute(sqlQuery);
}
}
sqlQuery = "SELECT * FROM GENERAL_SETTINGS";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
if (!IsDBNull(r["LANGUAGE"]))
{
pngeneral.Language = (string) r["LANGUAGE"];
}
var langPath = Path.Combine(PNPaths.Instance.LangDir, pngeneral.Language);
PNLang.Instance.LoadLanguage(langPath);
if (!IsDBNull(r["RUN_ON_START"]))
{
pngeneral.RunOnStart = (bool)r["RUN_ON_START"];
}
if (!IsDBNull(r["HIDE_TOOLBAR"]))
{
pngeneral.HideToolbar = (bool)r["HIDE_TOOLBAR"];
}
if (!IsDBNull(r["SHOW_CP_ON_START"]))
{
pngeneral.ShowCPOnStart = (bool)r["SHOW_CP_ON_START"];
}
if (!IsDBNull(r["CHECK_NEW_VERSION_ON_START"]))
{
pngeneral.CheckNewVersionOnStart = (bool)r["CHECK_NEW_VERSION_ON_START"];
}
if (!IsDBNull(r["USE_CUSTOM_FONTS"]))
{
pngeneral.UseCustomFonts = (bool)r["USE_CUSTOM_FONTS"];
}
if (!IsDBNull(r["SHOW_SCROLLBAR"]))
{
pngeneral.ShowScrollbar =
(System.Windows.Forms.RichTextBoxScrollBars)
Convert.ToInt32(r["SHOW_SCROLLBAR"]);
}
if (!IsDBNull(r["HIDE_DELETE_BUTTON"]))
{
pngeneral.HideDeleteButton = (bool)r["HIDE_DELETE_BUTTON"];
}
if (!IsDBNull(r["CHANGE_HIDE_TO_DELETE"]))
{
pngeneral.ChangeHideToDelete = (bool)r["CHANGE_HIDE_TO_DELETE"];
}
if (!IsDBNull(r["HIDE_HIDE_BUTTON"]))
{
pngeneral.HideHideButton = (bool)r["HIDE_HIDE_BUTTON"];
}
if (!IsDBNull(r["BULLETS_INDENT"]))
{
pngeneral.BulletsIndent = (short)(int)r["BULLETS_INDENT"];
}
if (!IsDBNull(r["MARGIN_WIDTH"]))
{
pngeneral.MarginWidth = (short)(int)r["MARGIN_WIDTH"];
}
if (!IsDBNull(r["DATE_FORMAT"]))
{
pngeneral.DateFormat =
((string)r["DATE_FORMAT"]).Replace("H", "")
.Replace("h", "")
.Replace("m", "")
.Replace(":", "")
.Trim();
Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern = pngeneral.DateFormat;
}
if (!IsDBNull(r["TIME_FORMAT"]))
{
pngeneral.TimeFormat = (string)r["TIME_FORMAT"];
Thread.CurrentThread.CurrentCulture.DateTimeFormat.LongTimePattern = pngeneral.TimeFormat;
}
if (!IsDBNull(r["SAVE_ON_EXIT"]))
{
pngeneral.SaveOnExit = (bool)r["SAVE_ON_EXIT"];
}
if (!IsDBNull(r["CONFIRM_SAVING"]))
{
pngeneral.ConfirmSaving = (bool)r["CONFIRM_SAVING"];
}
if (!IsDBNull(r["CONFIRM_BEFORE_DELETION"]))
{
pngeneral.ConfirmBeforeDeletion = (bool)r["CONFIRM_BEFORE_DELETION"];
}
if (!IsDBNull(r["SAVE_WITHOUT_CONFIRM_ON_HIDE"]))
{
pngeneral.SaveWithoutConfirmOnHide = (bool)r["SAVE_WITHOUT_CONFIRM_ON_HIDE"];
}
if (!IsDBNull(r["WARN_ON_AUTOMATICAL_DELETE"]))
{
pngeneral.WarnOnAutomaticalDelete = (bool)r["WARN_ON_AUTOMATICAL_DELETE"];
}
if (!IsDBNull(r["REMOVE_FROM_BIN_PERIOD"]))
{
pngeneral.RemoveFromBinPeriod = (int)r["REMOVE_FROM_BIN_PERIOD"];
}
if (!IsDBNull(r["AUTO_SAVE"]))
{
pngeneral.Autosave = (bool)r["AUTO_SAVE"];
}
if (!IsDBNull(r["AUTO_SAVE_PERIOD"]))
{
pngeneral.AutosavePeriod = (int)r["AUTO_SAVE_PERIOD"];
}
if (!IsDBNull(r["SKINLESS_WIDTH"]))
{
pngeneral.Width = (int)r["SKINLESS_WIDTH"];
}
if (!IsDBNull(r["SKINLESS_HEIGHT"]))
{
pngeneral.Height = (int)r["SKINLESS_HEIGHT"];
}
if (!IsDBNull(r["SPELL_COLOR"]))
{
pngeneral.SpellColor = (System.Drawing.Color)
drawingConverter.ConvertFromString(null, PNStatic.CultureInvariant,
(string)r["SPELL_COLOR"]);
}
if (!IsDBNull(r["USE_SKINS"]))
{
pngeneral.UseSkins = (bool)r["USE_SKINS"];
}
if (!IsDBNull(r["SPELL_MODE"]))
{
pngeneral.SpellMode = (int)r["SPELL_MODE"];
}
if (!IsDBNull(r["SPELL_DICT"]))
{
pngeneral.SpellDict = (string)r["SPELL_DICT"];
}
if (!IsDBNull(r["DOCK_WIDTH"]))
{
pngeneral.DockWidth = (int)r["DOCK_WIDTH"];
}
if (!IsDBNull(r["DOCK_HEIGHT"]))
{
pngeneral.DockHeight = (int)r["DOCK_HEIGHT"];
}
if (!IsDBNull(r["SHOW_PRIORITY_ON_START"]))
{
pngeneral.ShowPriorityOnStart = (bool)r["SHOW_PRIORITY_ON_START"];
}
if (!IsDBNull(r["BUTTONS_SIZE"]))
{
pngeneral.ButtonsSize = (ToolStripButtonSize)((int)r["BUTTONS_SIZE"]);
}
if (!IsDBNull(r["AUTOMATIC_SMILIES"]))
{
pngeneral.AutomaticSmilies = (bool)r["AUTOMATIC_SMILIES"];
}
if (!IsDBNull(r["SPACE_POINTS"]))
{
pngeneral.SpacePoints = (int)r["SPACE_POINTS"];
}
if (!IsDBNull(r["RESTORE_AUTO"]))
{
pngeneral.RestoreAuto = (bool)r["RESTORE_AUTO"];
}
if (!IsDBNull(r["PARAGRAPH_INDENT"]))
{
pngeneral.ParagraphIndent = (int)r["PARAGRAPH_INDENT"];
}
if (!IsDBNull(r["AUTO_HEIGHT"]))
{
pngeneral.AutoHeight = (bool)r["AUTO_HEIGHT"];
}
if (!IsDBNull(r["CRITICAL_ON_START"]))
{
pngeneral.CheckCriticalOnStart = (bool)r["CRITICAL_ON_START"];
}
if (!IsDBNull(r["CRITICAL_PERIODICALLY"]))
{
pngeneral.CheckCriticalPeriodically = (bool)r["CRITICAL_PERIODICALLY"];
}
if (!IsDBNull(r["DELETE_SHORTCUTS_ON_EXIT"]))
{
pngeneral.DeleteShortcutsOnExit = (bool)r["DELETE_SHORTCUTS_ON_EXIT"];
}
if (!IsDBNull(r["RESTORE_SHORTCUTS_ON_START"]))
{
pngeneral.RestoreShortcutsOnStart = (bool)r["RESTORE_SHORTCUTS_ON_START"];
}
if (!IsDBNull(r["CLOSE_ON_SHORTCUT"]))
{
pngeneral.CloseOnShortcut = (bool)r["CLOSE_ON_SHORTCUT"];
}
}
else
{
sqlQuery = "INSERT INTO GENERAL_SETTINGS VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
}
}
PNStatic.SpTextProvider.SplashText = PNLang.Instance.GetMessageText("load_program_settings", "Loading program settings");
//schedule
if (!oData.TableExists("SCHEDULE"))
{
sqlQuery = "CREATE TABLE [SCHEDULE] ([SOUND] TEXT, [DATE_FORMAT] TEXT, [TIME_FORMAT] TEXT, [VOICE] TEXT, [ALLOW_SOUND] BOOLEAN, [TRACK_OVERDUE] BOOLEAN, [VISUAL_NOTIFY] BOOLEAN, [CENTER_SCREEN] BOOLEAN, [VOICE_VOLUME] INT, [VOICE_SPEED] INT, [VOICE_PITCH] INT)";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO SCHEDULE VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
else
{
sqlQuery = "SELECT * FROM SCHEDULE";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
var pnsc = PNStatic.Settings.Schedule;
if (!IsDBNull(r["SOUND"]))
{
pnsc.Sound = (string)r["SOUND"];
}
if (!IsDBNull(r["VOICE"]))
{
pnsc.Voice = (string)r["VOICE"];
}
if (!IsDBNull(r["ALLOW_SOUND"]))
{
pnsc.AllowSoundAlert = (bool)r["ALLOW_SOUND"];
}
if (!IsDBNull(r["TRACK_OVERDUE"]))
{
pnsc.TrackOverdue = (bool)r["TRACK_OVERDUE"];
}
if (!IsDBNull(r["VISUAL_NOTIFY"]))
{
pnsc.VisualNotification = (bool)r["VISUAL_NOTIFY"];
}
if (!IsDBNull(r["CENTER_SCREEN"]))
{
pnsc.CenterScreen = (bool)r["CENTER_SCREEN"];
}
if (!IsDBNull(r["VOICE_VOLUME"]))
{
pnsc.VoiceVolume = (int)r["VOICE_VOLUME"];
}
if (!IsDBNull(r["VOICE_SPEED"]))
{
pnsc.VoiceSpeed = (int)r["VOICE_SPEED"];
}
if (!IsDBNull(r["VOICE_PITCH"]))
{
pnsc.VoicePitch = (int)r["VOICE_PITCH"];
}
}
else
{
sqlQuery = "INSERT INTO SCHEDULE VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
}
}
//behavior
if (!oData.TableExists("BEHAVIOR"))
{
sqlQuery = "CREATE TABLE [BEHAVIOR] ([NEW_ALWAYS_ON_TOP] BOOLEAN, [RELATIONAL_POSITION] BOOLEAN, [HIDE_COMPLETED] BOOLEAN, [BIG_ICONS_ON_CP] BOOLEAN, [DO_NOT_SHOW_IN_LIST] BOOLEAN, [KEEP_VISIBLE_ON_SHOW_DESKTOP] BOOLEAN, [DBL_CLICK_ACTION] INT, [SINGLE_CLICK_ACTION] INT, [DEFAULT_NAMING] INT, [DEFAULT_NAME_LENGHT] INT, [CONTENT_COLUMN_LENGTH] INT, [HIDE_FLUENTLY] BOOLEAN, [PLAY_SOUND_ON_HIDE] BOOLEAN, [OPACITY] REAL, [RANDOM_COLOR] BOOLEAN, [INVERT_TEXT_COLOR] BOOLEAN, [ROLL_ON_DBLCLICK] BOOLEAN, [FIT_WHEN_ROLLED] BOOLEAN, [SHOW_SEPARATE_NOTES] BOOLEAN, [PIN_CLICK_ACTION] INT, [NOTE_START_POSITION] INT, [HIDE_MAIN_WINDOW] BOOLEAN, [THEME] TEXT, [PREVENT_RESIZING] BOOLEAN, [SHOW_PANEL] BOOLEAN, [PANEL_DOCK] INT, [PANEL_AUTO_HIDE] BOOLEAN, [PANEL_REMOVE_MODE] INT, [PANEL_SWITCH_OFF_ANIMATION] BOOLEAN, [PANEL_ENTER_DELAY] INT)";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO BEHAVIOR VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
else
{
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'NOTE_START_POSITION' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [NOTE_START_POSITION] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'HIDE_MAIN_WINDOW' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [HIDE_MAIN_WINDOW] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'THEME' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [THEME] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PREVENT_RESIZING' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PREVENT_RESIZING] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'SHOW_PANEL' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [SHOW_PANEL] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PANEL_DOCK' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_DOCK] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PANEL_AUTO_HIDE' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_AUTO_HIDE] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PANEL_REMOVE_MODE' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_REMOVE_MODE] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PANEL_SWITCH_OFF_ANIMATION' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_SWITCH_OFF_ANIMATION] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PANEL_ENTER_DELAY' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_ENTER_DELAY] INT";
oData.Execute(sqlQuery);
}
}
sqlQuery = "SELECT * FROM BEHAVIOR";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
var pnb = PNStatic.Settings.Behavior;
if (!IsDBNull(r["NEW_ALWAYS_ON_TOP"]))
{
pnb.NewNoteAlwaysOnTop = (bool)r["NEW_ALWAYS_ON_TOP"];
}
if (!IsDBNull(r["RELATIONAL_POSITION"]))
{
pnb.RelationalPositioning = (bool)r["RELATIONAL_POSITION"];
}
if (!IsDBNull(r["HIDE_COMPLETED"]))
{
pnb.HideCompleted = (bool)r["HIDE_COMPLETED"];
}
if (!IsDBNull(r["BIG_ICONS_ON_CP"]))
{
pnb.BigIconsOnCP = (bool)r["BIG_ICONS_ON_CP"];
}
if (!IsDBNull(r["DO_NOT_SHOW_IN_LIST"]))
{
pnb.DoNotShowNotesInList = (bool)r["DO_NOT_SHOW_IN_LIST"];
}
if (!IsDBNull(r["KEEP_VISIBLE_ON_SHOW_DESKTOP"]))
{
pnb.KeepVisibleOnShowDesktop = (bool)r["KEEP_VISIBLE_ON_SHOW_DESKTOP"];
}
if (!IsDBNull(r["DBL_CLICK_ACTION"]))
{
var index = (int) r["DBL_CLICK_ACTION"];
if (index < Enum.GetValues(typeof (TrayMouseAction)).Length)
pnb.DoubleClickAction = (TrayMouseAction) index;
}
if (!IsDBNull(r["SINGLE_CLICK_ACTION"]))
{
var index = (int)r["SINGLE_CLICK_ACTION"];
if (index < Enum.GetValues(typeof (TrayMouseAction)).Length)
pnb.SingleClickAction = (TrayMouseAction) index;
}
if (!IsDBNull(r["DEFAULT_NAMING"]))
{
var index = (int)r["DEFAULT_NAMING"];
if (index < Enum.GetValues(typeof(DefaultNaming)).Length)
pnb.DefaultNaming = (DefaultNaming)index;
}
if (!IsDBNull(r["DEFAULT_NAME_LENGHT"]))
{
pnb.DefaultNameLength = (int)r["DEFAULT_NAME_LENGHT"];
}
if (!IsDBNull(r["CONTENT_COLUMN_LENGTH"]))
{
pnb.ContentColumnLength = (int)r["CONTENT_COLUMN_LENGTH"];
}
if (!IsDBNull(r["HIDE_FLUENTLY"]))
{
pnb.HideFluently = (bool)r["HIDE_FLUENTLY"];
}
if (!IsDBNull(r["PLAY_SOUND_ON_HIDE"]))
{
pnb.PlaySoundOnHide = (bool)r["PLAY_SOUND_ON_HIDE"];
}
if (!IsDBNull(r["OPACITY"]))
{
pnb.Opacity = (double)r["OPACITY"];
}
if (!IsDBNull(r["RANDOM_COLOR"]))
{
pnb.RandomBackColor = (bool)r["RANDOM_COLOR"];
}
if (!IsDBNull(r["INVERT_TEXT_COLOR"]))
{
pnb.InvertTextColor = (bool)r["INVERT_TEXT_COLOR"];
}
if (!IsDBNull(r["ROLL_ON_DBLCLICK"]))
{
pnb.RollOnDblClick = (bool)r["ROLL_ON_DBLCLICK"];
}
if (!IsDBNull(r["FIT_WHEN_ROLLED"]))
{
pnb.FitWhenRolled = (bool)r["FIT_WHEN_ROLLED"];
}
if (!IsDBNull(r["SHOW_SEPARATE_NOTES"]))
{
pnb.ShowSeparateNotes = (bool)r["SHOW_SEPARATE_NOTES"];
}
if (!IsDBNull(r["PIN_CLICK_ACTION"]))
{
var index = (int)r["PIN_CLICK_ACTION"];
if (index < Enum.GetValues(typeof(PinClickAction)).Length)
pnb.PinClickAction = (PinClickAction)index;
}
if (!IsDBNull(r["NOTE_START_POSITION"]))
{
var index = (int)r["NOTE_START_POSITION"];
if (index < Enum.GetValues(typeof(NoteStartPosition)).Length)
pnb.StartPosition = (NoteStartPosition)index;
}
if (!IsDBNull(r["HIDE_MAIN_WINDOW"]))
{
pnb.HideMainWindow = (bool)r["HIDE_MAIN_WINDOW"];
}
if (!IsDBNull(r["THEME"]))
{
pnb.Theme = (string) r["THEME"];
}
else
{
pnb.Theme = PNStrings.DEF_THEME;
}
if (!IsDBNull(r["PREVENT_RESIZING"]))
{
pnb.PreventAutomaticResizing = (bool)r["PREVENT_RESIZING"];
}
if (!IsDBNull(r["SHOW_PANEL"]))
{
pnb.ShowNotesPanel = (bool)r["SHOW_PANEL"];
}
if (!IsDBNull(r["PANEL_DOCK"]))
{
pnb.NotesPanelOrientation = (NotesPanelOrientation)r["PANEL_DOCK"];
}
if (!IsDBNull(r["PANEL_AUTO_HIDE"]))
{
pnb.PanelAutoHide = (bool)r["PANEL_AUTO_HIDE"];
}
if (!IsDBNull(r["PANEL_REMOVE_MODE"]))
{
pnb.PanelRemoveMode = (PanelRemoveMode)r["PANEL_REMOVE_MODE"];
}
if (!IsDBNull(r["PANEL_SWITCH_OFF_ANIMATION"]))
{
pnb.PanelSwitchOffAnimation = (bool)r["PANEL_SWITCH_OFF_ANIMATION"];
}
if (!IsDBNull(r["PANEL_ENTER_DELAY"]))
{
pnb.PanelEnterDelay = (int)r["PANEL_ENTER_DELAY"];
}
}
else
{
sqlQuery = "INSERT INTO BEHAVIOR VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
}
}
//protection
if (!oData.TableExists("PROTECTION"))
{
sqlQuery = "CREATE TABLE [PROTECTION] ([STORE_AS_ENCRYPTED] BOOLEAN, [HIDE_TRAY_ICON] BOOLEAN, [BACKUP_BEFORE_SAVING] BOOLEAN, [SILENT_FULL_BACKUP] BOOLEAN, [BACKUP_DEEPNESS] INT, [DO_NOT_SHOW_CONTENT] BOOLEAN, [INCLUDE_BIN_IN_SYNC] BOOLEAN, [PASSWORD_STRING] TEXT, [FULL_BACKUP_DAYS] TEXT, [FULL_BACKUP_TIME] TEXT, [FULL_BACKUP_DATE] TEXT, [PROMPT_PASSWORD] BOOLEAN)";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO PROTECTION VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
else
{
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'FULL_BACKUP_DAYS' AND TABLE_NAME = 'PROTECTION'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE PROTECTION ADD COLUMN [FULL_BACKUP_DAYS] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'FULL_BACKUP_TIME' AND TABLE_NAME = 'PROTECTION'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE PROTECTION ADD COLUMN [FULL_BACKUP_TIME] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'FULL_BACKUP_DATE' AND TABLE_NAME = 'PROTECTION'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE PROTECTION ADD COLUMN [FULL_BACKUP_DATE] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PROMPT_PASSWORD' AND TABLE_NAME = 'PROTECTION'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE PROTECTION ADD COLUMN [PROMPT_PASSWORD] BOOLEAN";
oData.Execute(sqlQuery);
}
}
sqlQuery = "SELECT * FROM PROTECTION";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
var pnp = PNStatic.Settings.Protection;
if (!IsDBNull(r["STORE_AS_ENCRYPTED"]))
{
pnp.StoreAsEncrypted = (bool)r["STORE_AS_ENCRYPTED"];
}
if (!IsDBNull(r["HIDE_TRAY_ICON"]))
{
pnp.HideTrayIcon = (bool)r["HIDE_TRAY_ICON"];
}
if (!IsDBNull(r["BACKUP_BEFORE_SAVING"]))
{
pnp.BackupBeforeSaving = (bool)r["BACKUP_BEFORE_SAVING"];
}
if (!IsDBNull(r["SILENT_FULL_BACKUP"]))
{
pnp.SilentFullBackup = (bool)r["SILENT_FULL_BACKUP"];
}
if (!IsDBNull(r["BACKUP_DEEPNESS"]))
{
pnp.BackupDeepness = (int)r["BACKUP_DEEPNESS"];
}
if (!IsDBNull(r["DO_NOT_SHOW_CONTENT"]))
{
pnp.DontShowContent = (bool)r["DO_NOT_SHOW_CONTENT"];
}
if (!IsDBNull(r["INCLUDE_BIN_IN_SYNC"]))
{
pnp.IncludeBinInSync = (bool)r["INCLUDE_BIN_IN_SYNC"];
}
if (!IsDBNull(r["PASSWORD_STRING"]))
{
pnp.PasswordString = (string)r["PASSWORD_STRING"];
}
if (!IsDBNull(r["FULL_BACKUP_DAYS"]))
{
var temp = Convert.ToString(r["FULL_BACKUP_DAYS"]);
if (!string.IsNullOrEmpty(temp))
{
var days = temp.Split(',');
foreach (var d in days)
{
pnp.FullBackupDays.Add((DayOfWeek)Convert.ToInt32(d));
}
}
}
if (!IsDBNull(r["FULL_BACKUP_TIME"]))
{
pnp.FullBackupTime = DateTime.Parse((string)r["FULL_BACKUP_TIME"], PNStatic.CultureInvariant);
}
if (!IsDBNull(r["FULL_BACKUP_DATE"]))
{
pnp.FullBackupDate = DateTime.Parse((string)r["FULL_BACKUP_DATE"], PNStatic.CultureInvariant);
}
if (!IsDBNull(r["PROMPT_PASSWORD"]))
{
pnp.PromptForPassword = (bool)r["PROMPT_PASSWORD"];
}
}
else
{
sqlQuery = "INSERT INTO PROTECTION VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
}
}
//diary
if (!oData.TableExists("DIARY"))
{
sqlQuery = "CREATE TABLE [DIARY] ([CUSTOM_SETTINGS] BOOLEAN, [ADD_WEEKDAY] BOOLEAN, [FULL_WEEKDAY_NAME] BOOLEAN, [WEEKDAY_AT_THE_END] BOOLEAN, [DO_NOT_SHOW_PREVIOUS] BOOLEAN, [ASC_ORDER] BOOLEAN, [NUMBER_OF_PAGES] INT, [DATE_FORMAT] TEXT)";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO DIARY VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
else
{
sqlQuery = "SELECT * FROM DIARY";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
var pndr = PNStatic.Settings.Diary;
if (!IsDBNull(r["CUSTOM_SETTINGS"]))
{
pndr.CustomSettings = (bool)r["CUSTOM_SETTINGS"];
}
if (!IsDBNull(r["ADD_WEEKDAY"]))
{
pndr.AddWeekday = (bool)r["ADD_WEEKDAY"];
}
if (!IsDBNull(r["FULL_WEEKDAY_NAME"]))
{
pndr.FullWeekdayName = (bool)r["FULL_WEEKDAY_NAME"];
}
if (!IsDBNull(r["WEEKDAY_AT_THE_END"]))
{
pndr.WeekdayAtTheEnd = (bool)r["WEEKDAY_AT_THE_END"];
}
if (!IsDBNull(r["DO_NOT_SHOW_PREVIOUS"]))
{
pndr.DoNotShowPrevious = (bool)r["DO_NOT_SHOW_PREVIOUS"];
}
if (!IsDBNull(r["ASC_ORDER"]))
{
pndr.AscendingOrder = (bool)r["ASC_ORDER"];
}
if (!IsDBNull(r["NUMBER_OF_PAGES"]))
{
pndr.NumberOfPages = (int)r["NUMBER_OF_PAGES"];
}
if (!IsDBNull(r["DATE_FORMAT"]))
{
pndr.DateFormat = (string)r["DATE_FORMAT"];
}
}
else
{
sqlQuery = "INSERT INTO DIARY VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
}
}
//network
if (!oData.TableExists("NETWORK"))
{
sqlQuery = "CREATE TABLE [NETWORK] ([INCLUDE_BIN_IN_SYNC] BOOLEAN, [SYNC_ON_START] BOOLEAN, [SAVE_BEFORE_SYNC] BOOLEAN, [ENABLE_EXCHANGE] BOOLEAN, [SAVE_BEFORE_SEND] BOOLEAN, [NO_NOTIFY_ON_ARRIVE] BOOLEAN, [SHOW_RECEIVED_ON_CLICK] BOOLEAN, [SHOW_INCOMING_ON_CLICK] BOOLEAN, [NO_SOUND_ON_ARRIVE] BOOLEAN, [NO_NOTIFY_ON_SEND] BOOLEAN, [SHOW_AFTER_ARRIVE] BOOLEAN, [HIDE_AFTER_SEND] BOOLEAN, [NO_CONTACTS_IN_CONTEXT_MENU] BOOLEAN, [EXCHANGE_PORT] INT, [POST_COUNT] INT, [ALLOW_PING] BOOLEAN, [RECEIVED_ON_TOP] BOOLEAN)";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO NETWORK VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
else
{
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'POST_COUNT' AND TABLE_NAME = 'NETWORK'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NETWORK ADD COLUMN [POST_COUNT] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'ALLOW_PING' AND TABLE_NAME = 'NETWORK'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NETWORK ADD COLUMN [ALLOW_PING] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'RECEIVED_ON_TOP' AND TABLE_NAME = 'NETWORK'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NETWORK ADD COLUMN [RECEIVED_ON_TOP] BOOLEAN";
oData.Execute(sqlQuery);
}
}
sqlQuery = "SELECT * FROM NETWORK";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
var pnw = PNStatic.Settings.Network;
if (!IsDBNull(r["INCLUDE_BIN_IN_SYNC"]))
{
pnw.IncludeBinInSync = (bool)r["INCLUDE_BIN_IN_SYNC"];
}
if (!IsDBNull(r["SYNC_ON_START"]))
{
pnw.SyncOnStart = (bool)r["SYNC_ON_START"];
}
if (!IsDBNull(r["SAVE_BEFORE_SYNC"]))
{
pnw.SaveBeforeSync = (bool)r["SAVE_BEFORE_SYNC"];
}
if (!IsDBNull(r["ENABLE_EXCHANGE"]))
{
pnw.EnableExchange = (bool)r["ENABLE_EXCHANGE"];
}
if (!IsDBNull(r["SAVE_BEFORE_SEND"]))
{
pnw.SaveBeforeSending = (bool)r["SAVE_BEFORE_SEND"];
}
if (!IsDBNull(r["NO_NOTIFY_ON_ARRIVE"]))
{
pnw.NoNotificationOnArrive = (bool)r["NO_NOTIFY_ON_ARRIVE"];
}
if (!IsDBNull(r["SHOW_RECEIVED_ON_CLICK"]))
{
pnw.ShowReceivedOnClick = (bool)r["SHOW_RECEIVED_ON_CLICK"];
}
if (!IsDBNull(r["SHOW_INCOMING_ON_CLICK"]))
{
pnw.ShowIncomingOnClick = (bool)r["SHOW_INCOMING_ON_CLICK"];
}
if (!IsDBNull(r["NO_SOUND_ON_ARRIVE"]))
{
pnw.NoSoundOnArrive = (bool)r["NO_SOUND_ON_ARRIVE"];
}
if (!IsDBNull(r["NO_NOTIFY_ON_SEND"]))
{
pnw.NoNotificationOnSend = (bool)r["NO_NOTIFY_ON_SEND"];
}
if (!IsDBNull(r["SHOW_AFTER_ARRIVE"]))
{
pnw.ShowAfterArrive = (bool)r["SHOW_AFTER_ARRIVE"];
}
if (!IsDBNull(r["HIDE_AFTER_SEND"]))
{
pnw.HideAfterSending = (bool)r["HIDE_AFTER_SEND"];
}
if (!IsDBNull(r["NO_CONTACTS_IN_CONTEXT_MENU"]))
{
pnw.NoContactsInContextMenu = (bool)r["NO_CONTACTS_IN_CONTEXT_MENU"];
}
if (!IsDBNull(r["EXCHANGE_PORT"]))
{
pnw.ExchangePort = (int)r["EXCHANGE_PORT"];
}
if (!IsDBNull(r["POST_COUNT"]))
{
pnw.PostCount = (int)r["POST_COUNT"];
}
if (!IsDBNull(r["ALLOW_PING"]))
{
pnw.AllowPing = (bool)r["ALLOW_PING"];
}
if (!IsDBNull(r["RECEIVED_ON_TOP"]))
{
pnw.ReceivedOnTop = (bool)r["RECEIVED_ON_TOP"];
}
}
else
{
sqlQuery = "INSERT INTO NETWORK VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
}
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
private void createMenusOrder(SQLiteDataObject oData, bool firstTime)
{
var dn = new WndNote();
var dcp = new WndCP(false);
try
{
var ctmNote = dn.NoteMenu;
var ctmEdit = dn.EditMenu;
var ctmList = dcp.CPMenu;
if (firstTime)
{
PNStatic.SpTextProvider.SplashText = PNLang.Instance.GetMessageText("load_menus", "Preparing menus order table");
foreach (var ti in ctmPN.Items.OfType<Control>())
{
addMenusOrder(oData, ctmPN.Name, ti, null, ctmPN.Items.IndexOf(ti));
}
foreach (var ti in ctmNote.Items.OfType<Control>())
{
addMenusOrder(oData, ctmNote.Name, ti, null, ctmNote.Items.IndexOf(ti));
}
foreach (var ti in ctmEdit.Items.OfType<Control>())
{
addMenusOrder(oData, ctmEdit.Name, ti, null, ctmEdit.Items.IndexOf(ti));
}
foreach (var ti in ctmList.Items.OfType<Control>())
{
addMenusOrder(oData, ctmList.Name, ti, null, ctmList.Items.IndexOf(ti));
}
}
else
{
var updateList = new List<string>();
const string SQL = "SELECT MENU_NAME FROM MENUS_ORDER WHERE CONTEXT_NAME = '%CONTEXT_NAME%'";
using (var t = oData.FillDataTable(SQL.Replace("%CONTEXT_NAME%", ctmPN.Name)))
{
var names = t.AsEnumerable().Select(r => Convert.ToString(r["MENU_NAME"]));
foreach (var ti in ctmPN.Items.OfType<Control>())
{
newMenusOrder(oData, ctmPN.Name, ti, null, ctmPN.Items.IndexOf(ti), names, updateList);
}
}
using (var t = oData.FillDataTable(SQL.Replace("%CONTEXT_NAME%", ctmNote.Name)))
{
var names = t.AsEnumerable().Select(r => Convert.ToString(r["MENU_NAME"]));
foreach (var ti in ctmNote.Items.OfType<Control>())
{
newMenusOrder(oData, ctmNote.Name, ti, null, ctmNote.Items.IndexOf(ti), names, updateList);
}
}
using (var t = oData.FillDataTable(SQL.Replace("%CONTEXT_NAME%", ctmEdit.Name)))
{
var names = t.AsEnumerable().Select(r => Convert.ToString(r["MENU_NAME"]));
foreach (var ti in ctmEdit.Items.OfType<Control>())
{
newMenusOrder(oData, ctmEdit.Name, ti, null, ctmEdit.Items.IndexOf(ti), names, updateList);
}
}
using (var t = oData.FillDataTable(SQL.Replace("%CONTEXT_NAME%", ctmList.Name)))
{
var names = t.AsEnumerable().Select(r => Convert.ToString(r["MENU_NAME"]));
foreach (var ti in ctmList.Items.OfType<Control>())
{
newMenusOrder(oData, ctmList.Name, ti, null, ctmList.Items.IndexOf(ti), names, updateList);
}
}
updateList.RemoveAll(string.IsNullOrWhiteSpace);
if (updateList.Count > 0)
{
PNStatic.SpTextProvider.SplashText = PNLang.Instance.GetMessageText("update_menus_order",
"Updating menus indexes");
foreach (var s in updateList)
{
oData.Execute(s);
}
}
}
PNMenus.PrepareDefaultMenuStrip(ctmPN, MenuType.Main, false);
PNMenus.PrepareDefaultMenuStrip(ctmNote, MenuType.Note, false);
PNMenus.PrepareDefaultMenuStrip(ctmEdit, MenuType.Edit, false);
PNMenus.PrepareDefaultMenuStrip(ctmList, MenuType.ControlPanel, false);
PNMenus.PrepareDefaultMenuStrip(ctmPN, MenuType.Main, true);
PNMenus.PrepareDefaultMenuStrip(ctmNote, MenuType.Note, true);
PNMenus.PrepareDefaultMenuStrip(ctmEdit, MenuType.Edit, true);
PNMenus.PrepareDefaultMenuStrip(ctmList, MenuType.ControlPanel, true);
PNMenus.CheckAndApplyNewMenusOrder(ctmPN);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
finally
{
dn.Close();
dcp.Close();
}
}
private void loadDatabase()
{
try
{
if (!File.Exists(PNPaths.Instance.DBPath))
{
PNStatic.SpTextProvider.SplashText = PNLang.Instance.GetMessageText("create_database", "Creating database");
_FirstRun = true;
}
else
{
PNStatic.SpTextProvider.SplashText = PNLang.Instance.GetMessageText("load_database", "Loading database");
}
PNData.ConnectionString = SQLiteDataObject.CheckAndCreateDatabase(PNPaths.Instance.DBPath);
if (PNData.ConnectionString != "")
{
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
//notes
var sqlQuery = "CREATE TABLE IF NOT EXISTS [NOTES] ([ID] TEXT PRIMARY KEY NOT NULL, [NAME] TEXT NOT NULL, [GROUP_ID] INT NOT NULL, [PREV_GROUP_ID] INT, [OPACITY] REAL, [VISIBLE] BOOLEAN, [FAVORITE] BOOLEAN, [PROTECTED] BOOLEAN, [COMPLETED] BOOLEAN, [PRIORITY] BOOLEAN, [PASSWORD_STRING] TEXT, [PINNED] BOOLEAN, [TOPMOST] BOOLEAN, [ROLLED] BOOLEAN, [DOCK_STATUS] INT, [DOCK_ORDER] INT, [SEND_RECEIVE_STATUS] INT, [DATE_CREATED] TEXT, [DATE_SAVED] TEXT, [DATE_SENT] TEXT, [DATE_RECEIVED] TEXT, [DATE_DELETED] TEXT, [SIZE] TEXT, [LOCATION] TEXT, [EDIT_SIZE] TEXT, [REL_X] REAL, [REL_Y] REAL, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )), [SENT_TO] TEXT, [RECEIVED_FROM] TEXT, [PIN_CLASS] TEXT, [PIN_TEXT] TEXT, [SCRAMBLED] BOOLEAN DEFAULT (0), [THUMBNAIL] BOOLEAN DEFAULT (0), [RECEIVED_IP] TEXT)";
oData.Execute(sqlQuery);
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'SCRAMBLED' AND TABLE_NAME = 'NOTES'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES ADD COLUMN [SCRAMBLED] BOOLEAN DEFAULT (0)";
oData.Execute(sqlQuery);
sqlQuery = "UPDATE NOTES SET SCRAMBLED = 0";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'THUMBNAIL' AND TABLE_NAME = 'NOTES'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES ADD COLUMN [THUMBNAIL] BOOLEAN DEFAULT (0)";
oData.Execute(sqlQuery);
sqlQuery = "UPDATE NOTES SET THUMBNAIL = 0";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'RECEIVED_IP' AND TABLE_NAME = 'NOTES'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES ADD COLUMN [RECEIVED_IP] TEXT";
oData.Execute(sqlQuery);
sqlQuery = "UPDATE NOTES SET THUMBNAIL = 0";
oData.Execute(sqlQuery);
}
}
if (!PNSingleton.Instance.PlatformChanged)
{
//custom notes settings
sqlQuery =
"CREATE TABLE IF NOT EXISTS [CUSTOM_NOTES_SETTINGS] ([NOTE_ID] TEXT NOT NULL, [BACK_COLOR] TEXT, [CAPTION_FONT_COLOR] TEXT, [CAPTION_FONT] TEXT, [SKIN_NAME] TEXT, [CUSTOM_OPACITY] BOOLEAN, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )))";
oData.Execute(sqlQuery);
}
else
{
if (oData.TableExists("CUSTOM_NOTES_SETTINGS"))
{
PNData.NormalizeCustomNotesTable(oData);
}
else
{
sqlQuery =
"CREATE TABLE [CUSTOM_NOTES_SETTINGS] ([NOTE_ID] TEXT NOT NULL, [BACK_COLOR] TEXT, [CAPTION_FONT_COLOR] TEXT, [CAPTION_FONT] TEXT, [SKIN_NAME] TEXT, [CUSTOM_OPACITY] BOOLEAN, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )))";
oData.Execute(sqlQuery);
}
}
//notes tags
sqlQuery = "CREATE TABLE IF NOT EXISTS [NOTES_TAGS] ([NOTE_ID] TEXT NOT NULL, [TAG] TEXT NOT NULL, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )))";
oData.Execute(sqlQuery);
//notes schedule
sqlQuery = "CREATE TABLE IF NOT EXISTS [NOTES_SCHEDULE] ([NOTE_ID] TEXT NOT NULL, [SCHEDULE_TYPE] INT, [ALARM_DATE] TEXT, [START_DATE] TEXT, [LAST_RUN] TEXT, [SOUND] TEXT, [STOP_AFTER] INT, [TRACK] BOOLEAN, [REPEAT_COUNT] INT, [SOUND_IN_LOOP] BOOLEAN, [USE_TTS] BOOLEAN, [START_FROM] INT, [MONTH_DAY] TEXT, [ALARM_AFTER] TEXT, [WEEKDAYS] TEXT, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )), [PROG_TO_RUN] TEXT, [CLOSE_ON_NOTIFICATION] BOOLEAN)";
oData.Execute(sqlQuery);
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'PROG_TO_RUN' AND TABLE_NAME = 'NOTES_SCHEDULE'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES_SCHEDULE ADD COLUMN [PROG_TO_RUN] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'CLOSE_ON_NOTIFICATION' AND TABLE_NAME = 'NOTES_SCHEDULE'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES_SCHEDULE ADD COLUMN [CLOSE_ON_NOTIFICATION] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'MULTI_ALERTS' AND TABLE_NAME = 'NOTES_SCHEDULE'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES_SCHEDULE ADD COLUMN [MULTI_ALERTS] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'TIME_ZONE' AND TABLE_NAME = 'NOTES_SCHEDULE'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES_SCHEDULE ADD COLUMN [TIME_ZONE] TEXT";
oData.Execute(sqlQuery);
}
}
//linked notes
sqlQuery = "CREATE TABLE IF NOT EXISTS [LINKED_NOTES] ([NOTE_ID] TEXT NOT NULL, [LINK_ID] TEXT NOT NULL, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )))";
oData.Execute(sqlQuery);
//groups
if (!oData.TableExists("GROUPS"))
{
sqlQuery =
"CREATE TABLE [GROUPS] ([GROUP_ID] INT PRIMARY KEY NOT NULL UNIQUE, [PARENT_ID] INT, [GROUP_NAME] TEXT NOT NULL, [ICON] TEXT, [BACK_COLOR] TEXT NOT NULL, [CAPTION_FONT_COLOR] TEXT NOT NULL, [CAPTION_FONT] TEXT NOT NULL, [SKIN_NAME] TEXT NOT NULL, [PASSWORD_STRING] TEXT, [FONT] TEXT, [FONT_COLOR] TEXT, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )), [IS_DEFAULT_IMAGE] BOOLEAN)";
oData.Execute(sqlQuery);
var sb = new StringBuilder();
sb.Append(prepareGroupInsert((int)SpecialGroups.AllGroups, (int)SpecialGroups.DummyGroup,
"All groups", "gr_all.png"));
sb.Append(prepareGroupInsert(0, (int)SpecialGroups.AllGroups, "General", "gr.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.RecycleBin, (int)SpecialGroups.DummyGroup,
"Recycle Bin", "gr_bin.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.Diary, (int)SpecialGroups.DummyGroup,
"Diary", "gr_diary.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.SearchResults,
(int)SpecialGroups.DummyGroup, "Search Results", "gr_search.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.Backup, (int)SpecialGroups.DummyGroup,
"Backup", "gr_back.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.Favorites, (int)SpecialGroups.DummyGroup,
"Favorites", "gr_fav.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.Incoming, (int)SpecialGroups.DummyGroup,
"Incoming", "gr_inc.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.Docking, (int)SpecialGroups.DummyGroup,
"Docking", "dockall.png"));
sqlQuery = sb.ToString();
oData.Execute(sqlQuery);
}
else
{
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'IS_DEFAULT_IMAGE' AND TABLE_NAME = 'GROUPS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GROUPS ADD COLUMN [IS_DEFAULT_IMAGE] BOOLEAN";
oData.Execute(sqlQuery);
sqlQuery = "UPDATE GROUPS SET IS_DEFAULT_IMAGE = 1";
oData.Execute(sqlQuery);
}
}
if (PNSingleton.Instance.PlatformChanged)
{
PNData.NormalizeGroupsTable(oData, false);
upgradeGroups(oData);
}
}
var groupsWithoutParent = new List<PNGroup>();
sqlQuery = "SELECT GROUP_ID, PARENT_ID, GROUP_NAME, ICON, BACK_COLOR, CAPTION_FONT_COLOR, CAPTION_FONT, SKIN_NAME, PASSWORD_STRING, FONT, FONT_COLOR, IS_DEFAULT_IMAGE FROM GROUPS ORDER BY GROUP_ID ASC";
using (var t = oData.FillDataTable(sqlQuery))
{
foreach (DataRow r in t.Rows)
{
if ((int)r["GROUP_ID"] < 0)
{
if ((int)r["GROUP_ID"] != (int)SpecialGroups.Docking)
{
var group = new PNGroup();
fillGroup(group, r);
PNStatic.Groups.Insert(0, group);
}
else
{
fillGroup(PNStatic.Docking, r);
}
}
else
{
var gr = new PNGroup();
fillGroup(gr, r);
var parentExists = PNStatic.Groups.Select(grp => grp.GetGroupById(gr.ParentID)).Any(pg => pg != null);
if (!parentExists)
{
groupsWithoutParent.Add(gr);
continue;
}
foreach (var parent in PNStatic.Groups.Select(g => g.GetGroupById(gr.ParentID)).Where(parent => parent != null))
{
parent.Subgroups.Add(gr);
break;
}
}
}
}
while (groupsWithoutParent.Count > 0)
{
for (var i = groupsWithoutParent.Count - 1; i >= 0; i--)
{
if (PNStatic.Groups.Select(grp => grp.GetGroupById(groupsWithoutParent[i].ParentID)).All(pg => pg == null))
continue;
var i1 = i;
foreach (
var parent in
PNStatic.Groups.Select(g => g.GetGroupById(groupsWithoutParent[i1].ParentID))
.Where(parent => parent != null))
{
parent.Subgroups.Add(groupsWithoutParent[i]);
groupsWithoutParent.RemoveAt(i);
break;
}
}
}
applyStandardGroupsNames();
//sync comps
sqlQuery = "CREATE TABLE IF NOT EXISTS [SYNC_COMPS] ([COMP_NAME] TEXT NOT NULL, [DATA_DIR] TEXT NOT NULL, [DB_DIR] TEXT NOT NULL, [USE_DATA_DIR] TEXT NOT NULL)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM SYNC_COMPS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.SyncComps.Add(new PNSyncComp
{
CompName = (string)r["COMP_NAME"],
DataDir = (string)r["DATA_DIR"],
DBDir = (string)r["DB_DIR"],
UseDataDir = bool.Parse((string)r["USE_DATA_DIR"])
});
}
}
//contact groups
sqlQuery = "CREATE TABLE IF NOT EXISTS [CONTACT_GROUPS] ([ID] INT PRIMARY KEY ON CONFLICT REPLACE NOT NULL UNIQUE ON CONFLICT REPLACE, [GROUP_NAME] TEXT NOT NULL UNIQUE ON CONFLICT REPLACE)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM CONTACT_GROUPS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.ContactGroups.Add(new PNContactGroup { ID = (int)r["ID"], Name = (string)r["GROUP_NAME"] });
}
}
//contacts
sqlQuery = "CREATE TABLE IF NOT EXISTS [CONTACTS] ([ID] INT PRIMARY KEY ON CONFLICT REPLACE NOT NULL UNIQUE ON CONFLICT REPLACE, [GROUP_ID] INT NOT NULL, [CONTACT_NAME] TEXT NOT NULL UNIQUE ON CONFLICT REPLACE, [COMP_NAME] TEXT NOT NULL, [IP_ADDRESS] TEXT NOT NULL, [USE_COMP_NAME] BOOLEAN NOT NULL)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT ID, GROUP_ID, CONTACT_NAME, COMP_NAME, IP_ADDRESS, USE_COMP_NAME FROM CONTACTS"))
{
foreach (var cont in from DataRow r in t.Rows
select new PNContact
{
Name = (string)r["CONTACT_NAME"],
ComputerName = (string)r["COMP_NAME"],
IpAddress = (string)r["IP_ADDRESS"],
GroupID = (int)r["GROUP_ID"],
UseComputerName = (bool)r["USE_COMP_NAME"],
ID = (int)r["ID"]
})
{
PNStatic.Contacts.Add(cont);
}
Task.Factory.StartNew(updateContactsWithoutIp);
}
//externals
sqlQuery = "CREATE TABLE IF NOT EXISTS [EXTERNALS] ([EXT_NAME] TEXT PRIMARY KEY ON CONFLICT REPLACE NOT NULL UNIQUE ON CONFLICT REPLACE, [PROGRAM] TEXT NOT NULL, [COMMAND_LINE] TEXT NOT NULL)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT EXT_NAME, PROGRAM, COMMAND_LINE FROM EXTERNALS "))
{
foreach (DataRow r in t.Rows)
{
PNStatic.Externals.Add(new PNExternal
{
Name = (string)r["EXT_NAME"],
Program = (string)r["PROGRAM"],
CommandLine = (string)r["COMMAND_LINE"]
});
}
}
//search providers
if (!oData.TableExists("SEARCH_PROVIDERS"))
{
sqlQuery = "CREATE TABLE [SEARCH_PROVIDERS] ([SP_NAME] TEXT PRIMARY KEY ON CONFLICT REPLACE NOT NULL UNIQUE ON CONFLICT REPLACE, [SP_QUERY] TEXT NOT NULL)";
oData.Execute(sqlQuery);
//insert two default provider for the first time
sqlQuery = "INSERT INTO SEARCH_PROVIDERS VALUES('Google', 'http://www.google.com/search?q=')";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO SEARCH_PROVIDERS VALUES('Yahoo', 'http://search.yahoo.com/search?p=')";
oData.Execute(sqlQuery);
}
using (var t = oData.FillDataTable("SELECT * FROM SEARCH_PROVIDERS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.SearchProviders.Add(new PNSearchProvider
{
Name = (string)r["SP_NAME"],
QueryString = (string)r["SP_QUERY"]
});
}
}
//tags
sqlQuery = "CREATE TABLE IF NOT EXISTS [TAGS] ([TAG] TEXT PRIMARY KEY ON CONFLICT REPLACE NOT NULL UNIQUE ON CONFLICT REPLACE)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM TAGS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.Tags.Add((string)r["TAG"]);
}
}
//plugins
sqlQuery = "CREATE TABLE IF NOT EXISTS [POST_PLUGINS] ([PLUGIN] TEXT PRIMARY KEY NOT NULL UNIQUE)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM POST_PLUGINS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.PostPlugins.Add((string)r["PLUGIN"]);
}
}
sqlQuery = "CREATE TABLE IF NOT EXISTS [SYNC_PLUGINS] ([PLUGIN] TEXT PRIMARY KEY NOT NULL UNIQUE)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM SYNC_PLUGINS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.SyncPlugins.Add((string)r["PLUGIN"]);
}
}
//hotkeys
sqlQuery = "CREATE TABLE IF NOT EXISTS [HOT_KEYS] ([HK_TYPE] INT NOT NULL, [MENU_NAME] TEXT NOT NULL, [ID] INT NOT NULL DEFAULT(0), [MODIFIERS] INT NOT NULL DEFAULT(0), [VK] INT NOT NULL DEFAULT(0), [SHORTCUT] TEXT NOT NULL DEFAULT(''), PRIMARY KEY ([HK_TYPE], [MENU_NAME]))";
oData.Execute(sqlQuery);
prepareHotkeysTable(oData);
//find/replace
if (!oData.TableExists("FIND_REPLACE"))
{
sqlQuery = "CREATE TABLE [FIND_REPLACE] ([FIND] TEXT, [REPLACE] TEXT); ";
sqlQuery += "INSERT INTO FIND_REPLACE VALUES(NULL, NULL)";
}
oData.Execute(sqlQuery);
//remove possible program version table from previous versions
if (oData.TableExists("PROGRAM_VERSION"))
{
sqlQuery = "DROP TABLE [PROGRAM_VERSION]; ";
oData.Execute(sqlQuery);
}
//hidden menus
sqlQuery = "CREATE TABLE IF NOT EXISTS [HIDDEN_MENUS] ([MENU_NAME] TEXT NOT NULL, [MENU_TYPE] INT NOT NULL, PRIMARY KEY ([MENU_NAME], [MENU_TYPE]))";
oData.Execute(sqlQuery);
PNData.LoadHiddenMenus();
//menus order
if (!oData.TableExists("MENUS_ORDER"))
{
sqlQuery =
"CREATE TABLE [MENUS_ORDER] ([CONTEXT_NAME] TEXT NOT NULL, [MENU_NAME] TEXT NOT NULL, [ORDER_ORIGINAL] INT NOT NULL, [ORDER_NEW] INT NOT NULL, [PARENT_NAME] TEXT, PRIMARY KEY ( [CONTEXT_NAME], [MENU_NAME]));";
oData.Execute(sqlQuery);
createMenusOrder(oData, true);
}
else
{
createMenusOrder(oData, false);
}
//SMTP profiles
sqlQuery =
"CREATE TABLE IF NOT EXISTS [SMTP_PROFILES] ([ID] INT UNIQUE ON CONFLICT REPLACE, [ACTIVE] BOOLEAN, [HOST_NAME] TEXT, [DISPLAY_NAME] TEXT, [SENDER_ADDRESS] TEXT PRIMARY KEY UNIQUE, [PASSWORD] TEXT, [PORT] INT )";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM SMTP_PROFILES"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.SmtpProfiles.Add(new PNSmtpProfile
{
Active = Convert.ToBoolean(r["ACTIVE"]),
Id = Convert.ToInt32(r["ID"]),
HostName = Convert.ToString(r["HOST_NAME"]),
SenderAddress = Convert.ToString(r["SENDER_ADDRESS"]),
Password = Convert.ToString(r["PASSWORD"]),
Port = Convert.ToInt32(r["PORT"]),
DisplayName = Convert.ToString(r["DISPLAY_NAME"])
});
}
}
//mail contacts
sqlQuery = "CREATE TABLE IF NOT EXISTS [MAIL_CONTACTS] ([ID] INT UNIQUE ON CONFLICT REPLACE, [DISPLAY_NAME] TEXT, [ADDRESS] TEXT )";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM MAIL_CONTACTS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.MailContacts.Add(new PNMailContact
{
DisplayName = Convert.ToString(r["DISPLAY_NAME"]),
Address = Convert.ToString(r["ADDRESS"]),
Id = Convert.ToInt32(r["ID"]),
});
}
}
//services
sqlQuery =
"CREATE TABLE IF NOT EXISTS SERVICES ( APP_NAME TEXT PRIMARY KEY, CLIENT_ID TEXT, CLIENT_SECRET TEXT, ACCESS_TOKEN TEXT, REFRESH_TOKEN TEXT, TOKEN_EXPIRY TEXT )";
oData.Execute(sqlQuery);
prepareServices(oData);
//triggers
sqlQuery = PNStrings.CREATE_TRIGGERS;
oData.Execute(sqlQuery);
}
}
//load plugins
PNPlugins.Instance.LoadPlugins(this, PNPaths.Instance.PluginsDir);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
private bool exchangeNotesTags(SQLiteDataObject dataSrc, SQLiteDataObject dataDest, string id, KeyValuePair <string, List <_FieldData> > tableData)
{
try
{
using (var t1 = dataSrc.FillDataTable("SELECT * FROM NOTES_TAGS WHERE NOTE_ID = '" + id + "'"))
{
using (var t2 = dataDest.FillDataTable("SELECT * FROM NOTES_TAGS WHERE NOTE_ID = '" + id + "'"))
{
if (t1.Rows.Count > 0 && t2.Rows.Count > 0)
{
var t = t1.Clone();
foreach (DataRow r1 in t1.Rows)
{
if (t.AsEnumerable().All(r => (string)r["TAG"] != (string)r1["TAG"]))
{
t.Rows.Add(r1.ItemArray);
}
}
foreach (DataRow r2 in t2.Rows)
{
if (t.AsEnumerable().All(r => (string)r["TAG"] != (string)r2["TAG"]))
{
t.Rows.Add(r2.ItemArray);
}
}
foreach (DataRow r in t.Rows)
{
if (!insertToNotesTags(dataDest, r, tableData))
{
return(false);
}
if (!insertToNotesTags(dataSrc, r, tableData))
{
return(false);
}
}
}
else if (t1.Rows.Count > 0)
{
if (t1.Rows.Cast <DataRow>().Any(r => !insertToNotesTags(dataDest, r, tableData)))
{
return(false);
}
}
else if (t2.Rows.Count > 0)
{
if (t2.Rows.Cast <DataRow>().Any(r => !insertToNotesTags(dataSrc, r, tableData)))
{
return(false);
}
}
}
}
return(true);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return(false);
}
}
private bool exchangeGroups(SQLiteDataObject dataSrc, SQLiteDataObject dataDest, KeyValuePair <string, List <_FieldData> > tableData)
{
try
{
using (var t1 = dataSrc.FillDataTable("SELECT * FROM GROUPS"))
{
using (var t2 = dataDest.FillDataTable("SELECT * FROM GROUPS"))
{
var t = t1.Clone();
IEnumerable <DataRow> rows1 = t1.AsEnumerable();
IEnumerable <DataRow> rows2 = t2.AsEnumerable();
foreach (var r1 in rows1)
{
var r2 = rows2.FirstOrDefault(r => (int)r["GROUP_ID"] == (int)r1["GROUP_ID"]);
if (r2 != null)
{
if (Convert.ToInt64(r1["UPD_DATE"]) >= Convert.ToInt64(r2["UPD_DATE"]))
{
t.Rows.Add(r1.ItemArray);
}
else if (Convert.ToInt64(r2["UPD_DATE"]) > Convert.ToInt64(r1["UPD_DATE"]))
{
t.Rows.Add(r2.ItemArray);
}
}
else
{
t.Rows.Add(r1.ItemArray);
}
}
foreach (var r2 in rows2)
{
if (rows1.All(r => (int)r["GROUP_ID"] != (int)r2["GROUP_ID"]))
{
t.Rows.Add(r2.ItemArray);
}
}
var sqlList = new List <string> {
"DELETE FROM GROUPS"
};
sqlList.AddRange(from DataRow r in t.Rows select createInsert(r, tableData));
var inTrans1 = false;
var inTrans2 = false;
try
{
inTrans1 = dataSrc.BeginTransaction();
inTrans2 = dataDest.BeginTransaction();
if (inTrans1 && inTrans2)
{
foreach (string s in sqlList)
{
dataSrc.ExecuteInTransaction(s);
dataDest.ExecuteInTransaction(s);
}
dataSrc.CommitTransaction();
inTrans1 = false;
dataDest.CommitTransaction();
inTrans2 = false;
}
}
catch (Exception ex)
{
if (inTrans1)
{
dataSrc.RollbackTransaction();
}
if (inTrans2)
{
dataDest.RollbackTransaction();
}
PNStatic.LogException(ex);
return(false);
}
}
}
return(true);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return(false);
}
}
//todo: table data
private bool insertToAllTables(SQLiteDataObject dataSrc, SQLiteDataObject dataDest, string id, Dictionary <string, List <_FieldData> > tablesData)
{
try
{
using (var t = dataSrc.FillDataTable("SELECT * FROM NOTES WHERE ID = '" + id + "'"))
{
if (t.Rows.Count > 0)
{
if (!insertToNotes(dataDest, t.Rows[0], tablesData.FirstOrDefault(td => td.Key == "NOTES")))
{
return(false);
}
}
}
using (var t = dataSrc.FillDataTable("SELECT * FROM CUSTOM_NOTES_SETTINGS WHERE NOTE_ID = '" + id + "'"))
{
if (t.Rows.Count > 0)
{
if (!insertToCustomNotesSettings(dataDest, t.Rows[0], tablesData.FirstOrDefault(td => td.Key == "CUSTOM_NOTES_SETTINGS")))
{
return(false);
}
}
}
using (var t = dataSrc.FillDataTable("SELECT * FROM LINKED_NOTES WHERE NOTE_ID = '" + id + "'"))
{
if (t.Rows.Count > 0)
{
if (!insertToLinkedNotes(dataDest, t.Rows[0], tablesData.FirstOrDefault(td => td.Key == "LINKED_NOTES")))
{
return(false);
}
}
}
using (var t = dataSrc.FillDataTable("SELECT * FROM NOTES_SCHEDULE WHERE NOTE_ID = '" + id + "'"))
{
if (t.Rows.Count > 0)
{
if (!insertToNotesSchedule(dataDest, t.Rows[0], tablesData.FirstOrDefault(td => td.Key == "NOTES_SCHEDULE")))
{
return(false);
}
}
}
using (var t = dataSrc.FillDataTable("SELECT * FROM NOTES_TAGS WHERE NOTE_ID = '" + id + "'"))
{
if (t.Rows.Count > 0)
{
if (!insertToNotesTags(dataDest, t.Rows[0], tablesData.FirstOrDefault(td => td.Key == "NOTES_TAGS")))
{
return(false);
}
}
}
return(true);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return(false);
}
}
private bool areTablesDifferent(SQLiteDataObject dataSrc, SQLiteDataObject dataDest)
{
try
{
foreach (var tableName in _Tables)
{
var sqlQuery = "pragma table_info('" + tableName + "')";
using (var ts = dataSrc.FillDataTable(sqlQuery))
{
using (var td = dataDest.FillDataTable(sqlQuery))
{
var upper = ts.Rows.Count == td.Rows.Count
? ts.Rows.Count
: Math.Min(ts.Rows.Count, td.Rows.Count);
for (var i = 0; i < upper; i++)
{
if (Convert.ToString(ts.Rows[i]["name"]) != Convert.ToString(td.Rows[i]["name"]) ||
Convert.ToString(ts.Rows[i]["type"]) != Convert.ToString(td.Rows[i]["type"]))
{
return true;
}
}
if (upper >= ts.Rows.Count) continue;
for (var i = upper; i < ts.Rows.Count; i++)
{
var r = ts.Rows[i];
var sb = new StringBuilder("ALTER TABLE ");
sb.Append(tableName);
sb.Append(" ADD COLUMN [");
sb.Append(r["name"]);
sb.Append("] ");
sb.Append(r["type"]);
if (!DBNull.Value.Equals(r["dflt_value"]))
{
sb.Append(" DEFAULT (");
sb.Append(r["dflt_value"]);
sb.Append(")");
sb.Append("; UPDATE ");
sb.Append(tableName);
sb.Append(" SET ");
sb.Append(r["name"]);
sb.Append(" = ");
if (Convert.ToString(r["type"]).ToUpper() == "TEXT")
{
sb.Append("'");
sb.Append(Convert.ToString(r["dflt_value"]).Replace("'", "''"));
}
else
{
sb.Append(r["dflt_value"]);
}
if (Convert.ToString(r["type"]).ToUpper() == "TEXT")
{
sb.Append("'");
}
}
else
{
var updValue = getUpdateValue(tableName, Convert.ToString(r["name"]));
if (updValue != null)
{
sb.Append("; UPDATE ");
sb.Append(tableName);
sb.Append(" SET ");
sb.Append(r["name"]);
sb.Append(" = ");
if (Convert.ToString(r["type"]).ToUpper() == "TEXT")
{
sb.Append("'");
sb.Append(Convert.ToString(updValue).Replace("'", "''"));
}
else
{
sb.Append(updValue);
}
if (Convert.ToString(r["type"]).ToUpper() == "TEXT")
{
sb.Append("'");
}
}
}
dataDest.Execute(sb.ToString());
}
}
}
}
return false;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return false;
}
}
private List<string> deletedIDs(SQLiteDataObject oData)
{
var list = new List<string>();
try
{
using (var t = oData.FillDataTable("SELECT ID FROM NOTES WHERE GROUP_ID = " + (int)SpecialGroups.RecycleBin))
{
list.AddRange(from DataRow r in t.Rows select (string)r["ID"]);
}
return list;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return list;
}
}
private bool exchangeGroups(SQLiteDataObject dataSrc, SQLiteDataObject dataDest, KeyValuePair<string, List<_FieldData>> tableData)
{
try
{
using (var t1 = dataSrc.FillDataTable("SELECT * FROM GROUPS"))
{
using (var t2 = dataDest.FillDataTable("SELECT * FROM GROUPS"))
{
var t = t1.Clone();
IEnumerable<DataRow> rows1 = t1.AsEnumerable();
IEnumerable<DataRow> rows2 = t2.AsEnumerable();
foreach (var r1 in rows1)
{
var r2 = rows2.FirstOrDefault(r => (int)r["GROUP_ID"] == (int)r1["GROUP_ID"]);
if (r2 != null)
{
if (Convert.ToInt64(r1["UPD_DATE"]) >= Convert.ToInt64(r2["UPD_DATE"]))
{
t.Rows.Add(r1.ItemArray);
}
else if (Convert.ToInt64(r2["UPD_DATE"]) > Convert.ToInt64(r1["UPD_DATE"]))
{
t.Rows.Add(r2.ItemArray);
}
}
else
{
t.Rows.Add(r1.ItemArray);
}
}
foreach (var r2 in rows2)
{
if (rows1.All(r => (int)r["GROUP_ID"] != (int)r2["GROUP_ID"]))
{
t.Rows.Add(r2.ItemArray);
}
}
var sqlList = new List<string> { "DELETE FROM GROUPS" };
sqlList.AddRange(from DataRow r in t.Rows select createInsert(r, tableData));
var inTrans1 = false;
var inTrans2 = false;
try
{
inTrans1 = dataSrc.BeginTransaction();
inTrans2 = dataDest.BeginTransaction();
if (inTrans1 && inTrans2)
{
foreach (string s in sqlList)
{
dataSrc.ExecuteInTransaction(s);
dataDest.ExecuteInTransaction(s);
}
dataSrc.CommitTransaction();
inTrans1 = false;
dataDest.CommitTransaction();
inTrans2 = false;
}
}
catch (Exception ex)
{
if (inTrans1)
{
dataSrc.RollbackTransaction();
}
if (inTrans2)
{
dataDest.RollbackTransaction();
}
PNStatic.LogException(ex);
return false;
}
}
}
return true;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return false;
}
}
private void startPreparingMenuHotkeys(HotkeyType type, SQLiteDataObject oData, ContextMenu ctm, ref int id)
{
try
{
var sqlQuery = "SELECT MENU_NAME FROM HOT_KEYS WHERE HK_TYPE = " + ((int)type).ToString(CultureInfo.InvariantCulture);
using (var t = oData.FillDataTable(sqlQuery))
{
var names = (from DataRow r in t.Rows select (string)r[0]).ToList();
foreach (var ti in ctm.Items.OfType<MenuItem>())
{
prepareSingleMenuHotKey(ti, oData, names, type, ref id);
}
}
loadSpecificHotKeys(type, oData);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
private void loadSpecificHotKeys(HotkeyType type, SQLiteDataObject oData)
{
try
{
var sqlQuery = "SELECT MENU_NAME, ID, MODIFIERS, VK, SHORTCUT FROM HOT_KEYS WHERE HK_TYPE = " + ((int)type).ToString(CultureInfo.InvariantCulture);
using (var t = oData.FillDataTable(sqlQuery))
{
foreach (DataRow r in t.Rows)
{
switch (type)
{
case HotkeyType.Main:
PNStatic.HotKeysMain.Add(new PNHotKey { MenuName = (string)r["MENU_NAME"], ID = (int)r["ID"], Modifiers = (HotkeyModifiers)(int)r["MODIFIERS"], VK = (uint)(int)r["VK"], Shortcut = (string)r["SHORTCUT"], Type = type });
break;
case HotkeyType.Note:
PNStatic.HotKeysNote.Add(new PNHotKey { MenuName = (string)r["MENU_NAME"], ID = (int)r["ID"], Modifiers = (HotkeyModifiers)(int)r["MODIFIERS"], VK = (uint)(int)r["VK"], Shortcut = (string)r["SHORTCUT"], Type = type });
break;
case HotkeyType.Edit:
PNStatic.HotKeysEdit.Add(new PNHotKey { MenuName = (string)r["MENU_NAME"], ID = (int)r["ID"], Modifiers = (HotkeyModifiers)(int)r["MODIFIERS"], VK = (uint)(int)r["VK"], Shortcut = (string)r["SHORTCUT"], Type = type });
break;
case HotkeyType.Group:
PNStatic.HotKeysGroups.Add(new PNHotKey { MenuName = (string)r["MENU_NAME"], ID = (int)r["ID"], Modifiers = (HotkeyModifiers)(int)r["MODIFIERS"], VK = (uint)(int)r["VK"], Shortcut = (string)r["SHORTCUT"], Type = type });
break;
}
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal void SyncLocal(object localPaths)
{
var result = LocalSyncResult.Reload;
var filesSrc = new List <_NoteFile>();
var filesDest = new List <_NoteFile>();
var tempDir = "";
try
{
if (PNStatic.Settings.Network.SaveBeforeSync)
{
PNStatic.FormMain.ApplyAction(MainDialogAction.SaveAll, null);
}
var paths = localPaths as string[];
if (paths == null || paths.Length != 2)
{
throw new ArgumentException("Invalid local paths");
}
var destDir = paths[0].Trim();
var destDB = paths[1].Trim() != "" ? paths[1].Trim() : destDir;
destDB = Path.Combine(destDB, PNStrings.DB_FILE);
// create temp directory
tempDir = createTempDir();
var tempDBSrc = Path.Combine(tempDir, PNStrings.DB_FILE);
var tempDBDest = Path.Combine(tempDir, PNStrings.TEMP_DB_FILE);
// copy source db
File.Copy(PNPaths.Instance.DBPath, tempDBSrc, true);
// build source connection string
var srcConnectionString = "data source=\"" + tempDBSrc + "\"";
//string srcConnectionString = "metadata=res://*/PNModel.csdl|res://*/PNModel.ssdl|res://*/PNModel.msl;provider=System.Data.SQLite;provider connection string='data source=\"" + tempDBSrc + "\"'";
using (var eSrc = new SQLiteDataObject(srcConnectionString))
{
// drop triggers
eSrc.Execute(PNStrings.DROP_TRIGGERS);
// get listNames of all source notes files
var srcNotes = new DirectoryInfo(PNPaths.Instance.DataDir).GetFiles("*" + PNStrings.NOTE_EXTENSION);
filesSrc.AddRange(srcNotes.Select(fi => new _NoteFile {
Path = fi.FullName, Name = fi.Name, Copy = false
}));
// get deleted ids
if (!PNStatic.Settings.Network.IncludeBinInSync)
{
var deletedSrc = deletedIDs(eSrc);
filesSrc.RemoveAll(nf => deletedSrc.Contains(Path.GetFileNameWithoutExtension(nf.Name)));
}
// get listNames of all destination notes files
var destNotes = new DirectoryInfo(destDir).GetFiles("*" + PNStrings.NOTE_EXTENSION);
filesDest.AddRange(destNotes.Select(fir => new _NoteFile {
Path = fir.FullName, Name = fir.Name, Copy = false
}));
if (File.Exists(destDB))
{
// copy destination db to temp directory
File.Copy(destDB, tempDBDest, true);
// build connection string
var destConnectionString = "data source=\"" + tempDBDest + "\"";
using (var eDest = new SQLiteDataObject(destConnectionString))
{
// drop triggers
eDest.Execute(PNStrings.DROP_TRIGGERS);
if (areTablesDifferent(eSrc, eDest))
{
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(LocalSyncResult.AbortVersion));
}
return;
}
//todo: create tables data
var tablesData = new Dictionary <string, List <_FieldData> >();
foreach (var tn in _Tables)
{
var td = new List <_FieldData>();
var sb = new StringBuilder("pragma table_info('");
sb.Append(tn);
sb.Append("')");
using (var t = eSrc.FillDataTable(sb.ToString()))
{
td.AddRange(from DataRow r in t.Rows
select new _FieldData
{
Name = Convert.ToString(r["name"]),
Type = Convert.ToString(r["type"]),
NotNull = Convert.ToBoolean(r["notnull"])
});
}
tablesData.Add(tn, td);
}
// get deleted ids
if (!PNStatic.Settings.Network.IncludeBinInSync)
{
var deletedDest = deletedIDs(eDest);
filesDest.RemoveAll(nf => deletedDest.Contains(Path.GetFileNameWithoutExtension(nf.Name)));
}
foreach (var sf in filesSrc)
{
var id = Path.GetFileNameWithoutExtension(sf.Name);
// find destination file with same name
var df = filesDest.FirstOrDefault(f => f.Name == sf.Name);
if (df == null)
{
sf.Copy = true;
//todo
if (!insertToAllTables(eSrc, eDest, id, tablesData))
{
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(LocalSyncResult.Error));
}
return;
}
}
else
{
// check which note is more up to date
var dSrc = File.GetLastWriteTime(sf.Path);
var dDest = File.GetLastWriteTime(df.Path);
if (dSrc > dDest)
{
// compare two files
if (areFilesDifferent(sf.Path, df.Path))
{
// local file is younger then remote - copy it to remote client
sf.Copy = true;
}
}
else if (dSrc < dDest)
{
// compare two files
if (areFilesDifferent(sf.Path, df.Path))
{
// remote file is younger than local - copy it to local directory
df.Copy = true;
}
}
//todo
if (!exchangeData(eSrc, eDest, id, tablesData))
{
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(LocalSyncResult.Error));
}
return;
}
}
}
// check remaining destination files
var remDest = filesDest.Where(df => !df.Copy);
foreach (var df in remDest)
{
if (filesSrc.All(sf => sf.Name != df.Name))
{
df.Copy = true;
var id = Path.GetFileNameWithoutExtension(df.Name);
//todo
if (!exchangeData(eSrc, eDest, id, tablesData))
{
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(LocalSyncResult.Error));
}
return;
}
}
}
// synchronize groups
if (!exchangeGroups(eSrc, eDest, tablesData.FirstOrDefault(td => td.Key == "GROUPS")))
{
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(LocalSyncResult.Error));
}
return;
}
// restore triggers
eSrc.Execute(PNStrings.CREATE_TRIGGERS);
eDest.Execute(PNStrings.CREATE_TRIGGERS);
}
// copy files
var filesToCopy = filesSrc.Where(sf => sf.Copy);
foreach (var sf in filesToCopy)
{
var newPath = Path.Combine(destDir, sf.Name);
File.Copy(sf.Path, newPath, true);
}
filesToCopy = filesDest.Where(df => df.Copy);
foreach (var df in filesToCopy)
{
var newPath = Path.Combine(PNPaths.Instance.DataDir, df.Name);
File.Copy(df.Path, newPath, true);
}
if (filesDest.Count(df => df.Copy) == 0)
{
result = LocalSyncResult.None;
}
// copy synchronized db files
File.Copy(tempDBSrc, PNPaths.Instance.DBPath, true);
File.Copy(tempDBDest, destDB, true);
}
else
{
// restore triggers
eSrc.Execute(PNStrings.CREATE_TRIGGERS);
// just copy all notes files and db file to remote client
File.Copy(PNPaths.Instance.DBPath, destDB, true);
foreach (var sf in filesSrc)
{
var newPath = Path.Combine(destDir, sf.Name);
File.Copy(sf.Path, newPath, true);
}
result = LocalSyncResult.None;
}
}
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(result));
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
if (SyncComplete != null)
{
SyncComplete(this, new LocalSyncCompleteEventArgs(LocalSyncResult.Error));
}
}
finally
{
if (tempDir != "" && Directory.Exists(tempDir))
{
Directory.Delete(tempDir, true);
}
}
}
private void upgradeGroups(SQLiteDataObject oData)
{
try
{
var list = new List<string>();
using (
var t =
oData.FillDataTable(
"SELECT GROUP_ID, ICON FROM GROUPS"))
{
foreach (DataRow r in t.Rows)
{
var sb = new StringBuilder("UPDATE GROUPS SET ");
var isDefImage = 1;
switch (Convert.ToInt32(r["GROUP_ID"]))
{
case (int)SpecialGroups.AllGroups:
sb.Append(" ICON = 'gr_all.png', ");
break;
case 0:
sb.Append(" ICON = 'gr.png', ");
break;
case (int)SpecialGroups.Diary:
sb.Append(" ICON = 'gr_diary.png', ");
break;
case (int)SpecialGroups.SearchResults:
sb.Append(" ICON = 'gr_search.png', ");
break;
case (int)SpecialGroups.Backup:
sb.Append(" ICON = 'gr_back.png', ");
break;
case (int)SpecialGroups.Favorites:
sb.Append(" ICON = 'gr_fav.png', ");
break;
case (int)SpecialGroups.Incoming:
sb.Append(" ICON = 'gr_inc.png', ");
break;
case (int)SpecialGroups.Docking:
sb.Append(" ICON = 'dockall.png', ");
break;
case (int)SpecialGroups.RecycleBin:
sb.Append(" ICON = 'gr_bin.png', ");
break;
default:
var iconString = Convert.ToString(r["ICON"]);
if (iconString.StartsWith("resource."))
{
var imageName = iconString.Substring("resource.".Length);
imageName = imageName + ".png";
sb.Append(" ICON = '");
sb.Append(imageName);
sb.Append("', ");
}
else
{
isDefImage = 0;
}
break;
}
sb.Append(" IS_DEFAULT_IMAGE = ");
sb.Append(isDefImage);
sb.Append(" WHERE GROUP_ID = ");
sb.Append(r["GROUP_ID"]);
list.Add(sb.ToString());
}
}
PNData.ExecuteTransactionForList(list, oData.ConnectionString);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
//todo: table data
private bool insertToAllTables(SQLiteDataObject dataSrc, SQLiteDataObject dataDest, string id, Dictionary<string, List<_FieldData>> tablesData)
{
try
{
using (var t = dataSrc.FillDataTable("SELECT * FROM NOTES WHERE ID = '" + id + "'"))
{
if (t.Rows.Count > 0)
{
if (!insertToNotes(dataDest, t.Rows[0], tablesData.FirstOrDefault(td => td.Key == "NOTES")))
{
return false;
}
}
}
using (var t = dataSrc.FillDataTable("SELECT * FROM CUSTOM_NOTES_SETTINGS WHERE NOTE_ID = '" + id + "'"))
{
if (t.Rows.Count > 0)
{
if (!insertToCustomNotesSettings(dataDest, t.Rows[0], tablesData.FirstOrDefault(td => td.Key == "CUSTOM_NOTES_SETTINGS")))
{
return false;
}
}
}
using (var t = dataSrc.FillDataTable("SELECT * FROM LINKED_NOTES WHERE NOTE_ID = '" + id + "'"))
{
if (t.Rows.Count > 0)
{
if (!insertToLinkedNotes(dataDest, t.Rows[0], tablesData.FirstOrDefault(td => td.Key == "LINKED_NOTES")))
{
return false;
}
}
}
using (var t = dataSrc.FillDataTable("SELECT * FROM NOTES_SCHEDULE WHERE NOTE_ID = '" + id + "'"))
{
if (t.Rows.Count > 0)
{
if (!insertToNotesSchedule(dataDest, t.Rows[0], tablesData.FirstOrDefault(td => td.Key == "NOTES_SCHEDULE")))
{
return false;
}
}
}
using (var t = dataSrc.FillDataTable("SELECT * FROM NOTES_TAGS WHERE NOTE_ID = '" + id + "'"))
{
if (t.Rows.Count > 0)
{
if (!insertToNotesTags(dataDest, t.Rows[0], tablesData.FirstOrDefault(td => td.Key == "NOTES_TAGS")))
{
return false;
}
}
}
return true;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return false;
}
}
internal static void LoadGridColumns(GridView grd, List<ColumnProps> cols, List<GRDSort> sortColumns, string gridName)
{
try
{
cols.Clear();
var sqlList = new List<string>();
var tableName = gridName == "grdNotes" ? "GRD_NOTES_COLUMNS" : "GRD_BACK_COLUMNS";
using (var oData = new SQLiteDataObject(SettingsConnectionString))
{
if (oData.TableExists(tableName))
{
// check for "encrypted" column
if (gridName == "grdNotes")
{
var obj =
oData.GetScalar("SELECT CN_KEY FROM GRD_NOTES_COLUMNS WHERE CN_KEY = 'Note_Encrypted'");
if (obj == null || IsDBNull(obj))
{
oData.Execute(
"UPDATE GRD_NOTES_COLUMNS SET CN_DISPLAY_INDEX = CN_ORIGINAL_INDEX");
oData.Execute(
"UPDATE GRD_NOTES_COLUMNS SET CN_ORIGINAL_INDEX = CN_ORIGINAL_INDEX + 1, CN_DISPLAY_INDEX = CN_DISPLAY_INDEX + 1 WHERE CN_ORIGINAL_INDEX >= 9");
oData.Execute(
"INSERT INTO GRD_NOTES_COLUMNS VALUES('Note_Encrypted', 1, 9, 9, 32, 32, NULL, NULL)");
}
obj =
oData.GetScalar("SELECT CN_KEY FROM GRD_NOTES_COLUMNS WHERE CN_KEY = 'Note_PrevGroup'");
if (obj == null || IsDBNull(obj))
{
oData.Execute(
"UPDATE GRD_NOTES_COLUMNS SET CN_DISPLAY_INDEX = CN_ORIGINAL_INDEX");
oData.Execute(
"UPDATE GRD_NOTES_COLUMNS SET CN_ORIGINAL_INDEX = CN_ORIGINAL_INDEX + 1, CN_DISPLAY_INDEX = CN_DISPLAY_INDEX + 1 WHERE CN_ORIGINAL_INDEX >= 11");
oData.Execute(
"INSERT INTO GRD_NOTES_COLUMNS VALUES('Note_Encrypted', 1, 11, 11, 32, 32, NULL, NULL)");
}
}
using (var t = oData.FillDataTable("SELECT * FROM " + tableName))
{
foreach (DataRow r in t.Rows)
{
var column =
grd.Columns.Where(t1 => PNGridViewHelper.GetColumnName(t1) == (string)r["CN_KEY"])
.Cast<FixedWidthColumn>()
.FirstOrDefault();
if (column == null) continue;
if (!Convert.ToBoolean(r["CN_VISIBILITY"]))
{
column.Visibility = Visibility.Hidden;
column.Width = column.ActualWidth;
}
else
{
column.FixedWidth = Convert.ToInt32(r["CN_DISPLAY_WIDTH"]);
column.Width = column.FixedWidth;
}
column.DisplayIndex = Convert.ToInt32(r["CN_DISPLAY_INDEX"]);
column.OriginalIndex = Convert.ToInt32(r["CN_ORIGINAL_INDEX"]);
cols.Add(new ColumnProps
{
Name = (string)r["CN_KEY"],
Visibility = column.Visibility,
Width = Convert.ToInt32(r["CN_DISPLAY_WIDTH"])
});
var sort = new GRDSort { Key = (string)r["CN_KEY"] };
if (!IsDBNull(r["CN_SORT_ORDER"]))
{
sort.SortOrder = (ListSortDirection)Convert.ToInt32(r["CN_SORT_ORDER"]);
}
if (!IsDBNull(r["CN_LAST_SORTED"]))
{
sort.LastSorted = Convert.ToBoolean(r["CN_LAST_SORTED"]);
}
sortColumns.Add(sort);
}
}
}
else
{
sqlList.Add(
"CREATE TABLE [" + tableName + "] ([CN_KEY] TEXT PRIMARY KEY NOT NULL UNIQUE, [CN_VISIBILITY] BOOLEAN NOT NULL, [CN_ORIGINAL_INDEX] INT NOT NULL, [CN_DISPLAY_INDEX] INT NOT NULL, [CN_ORIGINAL_WIDTH] INT NOT NULL, [CN_DISPLAY_WIDTH] INT NOT NULL, [CN_SORT_ORDER] INT, [CN_LAST_SORTED] BOOLEAN)");
for (var i = 0; i < grd.Columns.Count; i++)
{
var c = (FixedWidthColumn)grd.Columns[i];
var sb = new StringBuilder();
sb.Append(
"INSERT INTO " + tableName + " (CN_KEY, CN_VISIBILITY, CN_ORIGINAL_INDEX, CN_DISPLAY_INDEX, CN_ORIGINAL_WIDTH, CN_DISPLAY_WIDTH, CN_SORT_ORDER, CN_LAST_SORTED) VALUES(");
sb.Append("'");
sb.Append(PNGridViewHelper.GetColumnName(c));
sb.Append("', ");
sb.Append(1);
sb.Append(", ");
sb.Append(i);
sb.Append(", ");
sb.Append(i);
sb.Append(", ");
sb.Append(c.FixedWidth);
sb.Append(", ");
sb.Append(c.FixedWidth);
sb.Append(", NULL, NULL)");
sqlList.Add(sb.ToString());
cols.Add(new ColumnProps
{
Name = PNGridViewHelper.GetColumnName(c),
Visibility = Visibility.Visible,
Width = c.FixedWidth
});
}
ExecuteTransactionForList(sqlList, SettingsConnectionString);
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
private bool areTablesDifferent(SQLiteDataObject dataSrc, SQLiteDataObject dataDest)
{
try
{
foreach (var tableName in _Tables)
{
var sqlQuery = "pragma table_info('" + tableName + "')";
using (var ts = dataSrc.FillDataTable(sqlQuery))
{
using (var td = dataDest.FillDataTable(sqlQuery))
{
var upper = ts.Rows.Count == td.Rows.Count
? ts.Rows.Count
: Math.Min(ts.Rows.Count, td.Rows.Count);
for (var i = 0; i < upper; i++)
{
if (Convert.ToString(ts.Rows[i]["name"]) != Convert.ToString(td.Rows[i]["name"]) ||
Convert.ToString(ts.Rows[i]["type"]) != Convert.ToString(td.Rows[i]["type"]))
{
return(true);
}
}
if (upper >= ts.Rows.Count)
{
continue;
}
for (var i = upper; i < ts.Rows.Count; i++)
{
var r = ts.Rows[i];
var sb = new StringBuilder("ALTER TABLE ");
sb.Append(tableName);
sb.Append(" ADD COLUMN [");
sb.Append(r["name"]);
sb.Append("] ");
sb.Append(r["type"]);
if (!DBNull.Value.Equals(r["dflt_value"]))
{
sb.Append(" DEFAULT (");
sb.Append(r["dflt_value"]);
sb.Append(")");
sb.Append("; UPDATE ");
sb.Append(tableName);
sb.Append(" SET ");
sb.Append(r["name"]);
sb.Append(" = ");
if (Convert.ToString(r["type"]).ToUpper() == "TEXT")
{
sb.Append("'");
sb.Append(Convert.ToString(r["dflt_value"]).Replace("'", "''"));
}
else
{
sb.Append(r["dflt_value"]);
}
if (Convert.ToString(r["type"]).ToUpper() == "TEXT")
{
sb.Append("'");
}
}
else
{
var updValue = getUpdateValue(tableName, Convert.ToString(r["name"]));
if (updValue != null)
{
sb.Append("; UPDATE ");
sb.Append(tableName);
sb.Append(" SET ");
sb.Append(r["name"]);
sb.Append(" = ");
if (Convert.ToString(r["type"]).ToUpper() == "TEXT")
{
sb.Append("'");
sb.Append(Convert.ToString(updValue).Replace("'", "''"));
}
else
{
sb.Append(updValue);
}
if (Convert.ToString(r["type"]).ToUpper() == "TEXT")
{
sb.Append("'");
}
}
}
dataDest.Execute(sb.ToString());
}
}
}
}
return(false);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return(false);
}
}
internal static void NormalizeGroupsTable(SQLiteDataObject oData, bool updateIcons = true)
{
try
{
var listSql = new List<string>();
var lfc = new LogFontConverter();
var wfc = new WPFFontConverter();
var drawingColorConverter = new System.Drawing.ColorConverter();
var mediaColorConverter = new ColorConverter();
if (updateIcons)
{
using (
var t =
oData.FillDataTable(
"SELECT GROUP_ID, ICON FROM GROUPS WHERE ICON NOT LIKE '%.png' OR ICON LIKE 'resource.%'")
)
{
foreach (DataRow r in t.Rows)
{
var sb = new StringBuilder("UPDATE GROUPS SET ");
var icon = Convert.ToString(r["ICON"]);
if (icon.StartsWith("resource."))
{
var imageName = icon.Substring("resource.".Length);
imageName = imageName + ".png";
sb.Append("ICON = '");
sb.Append(imageName);
sb.Append("'");
}
else
{
sb.Append("IS_DEFAULT_IMAGE = 0");
}
sb.Append(" WHERE GROUP_ID = ");
sb.Append(r["GROUP_ID"]);
listSql.Add(sb.ToString());
}
}
}
using (
var t =
oData.FillDataTable(
"SELECT GROUP_ID, CAPTION_FONT FROM GROUPS WHERE CAPTION_FONT LIKE '%^%'"))
{
foreach (DataRow r in t.Rows)
{
var lf = lfc.ConvertFromString((string)r["CAPTION_FONT"]);
var fnt = PNStatic.FromLogFont(lf);
var sb = new StringBuilder("UPDATE GROUPS SET CAPTION_FONT = '");
sb.Append(wfc.ConvertToString(null, PNStatic.CultureInvariant, fnt));
sb.Append("' WHERE GROUP_ID = ");
sb.Append(r["GROUP_ID"]);
listSql.Add(sb.ToString());
}
}
using (var t = oData.FillDataTable("SELECT GROUP_ID, BACK_COLOR, CAPTION_FONT_COLOR FROM GROUPS"))
{
var backColors = new List<TempColor>();
var capColors = new List<TempColor>();
foreach (DataRow r in t.Rows)
{
if (!IsDBNull(r["BACK_COLOR"]))
backColors.Add(new TempColor
{
Color = Convert.ToString(r["BACK_COLOR"]),
Id = Convert.ToString(r["GROUP_ID"])
});
if (!IsDBNull(r["CAPTION_FONT_COLOR"]))
capColors.Add(new TempColor
{
Color = Convert.ToString(r["CAPTION_FONT_COLOR"]),
Id = Convert.ToString(r["GROUP_ID"])
});
}
foreach (var cr in backColors.Where(c => !IsMediaColor(c.Color)))
{
var convertFromString = drawingColorConverter.ConvertFromString(null, PNStatic.CultureInvariant, cr.Color);
if (convertFromString == null) continue;
var clr = (System.Drawing.Color)convertFromString;
var color = Color.FromArgb(clr.A, clr.R, clr.G, clr.B);
var sb = new StringBuilder("UPDATE GROUPS SET BACK_COLOR = '");
sb.Append(mediaColorConverter.ConvertToString(null, PNStatic.CultureInvariant, color));
sb.Append("' WHERE GROUP_ID = ");
sb.Append(cr.Id);
listSql.Add(sb.ToString());
}
foreach (var cr in capColors.Where(c => !IsMediaColor(c.Color)))
{
var convertFromString = drawingColorConverter.ConvertFromString(null, PNStatic.CultureInvariant, cr.Color);
if (convertFromString == null) continue;
var clr = (System.Drawing.Color)convertFromString;
var color = Color.FromArgb(clr.A, clr.R, clr.G, clr.B);
var sb = new StringBuilder("UPDATE GROUPS SET CAPTION_FONT_COLOR = '");
sb.Append(mediaColorConverter.ConvertToString(null, PNStatic.CultureInvariant, color));
sb.Append("' WHERE GROUP_ID = ");
sb.Append(cr.Id);
listSql.Add(sb.ToString());
}
}
if (listSql.Count == 0) return;
ExecuteTransactionForList(listSql, ConnectionString);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void PrepareDefaultMenuStrip(ContextMenu source, MenuType type, bool forCurrent)
{
try
{
List<PNMenu> menuItems;
if (forCurrent)
{
switch (type)
{
case MenuType.Main:
menuItems = CurrentMainMenus;
break;
case MenuType.Edit:
menuItems = CurrentEditMenus;
break;
case MenuType.Note:
menuItems = CurrentNoteMenus;
break;
case MenuType.ControlPanel:
menuItems = CurrentCPMenus;
break;
default:
return;
}
}
else
{
switch (type)
{
case MenuType.Main:
menuItems = DefaultMainMenus;
break;
case MenuType.Edit:
menuItems = DefaultEditMenus;
break;
case MenuType.Note:
menuItems = DefaultNoteMenus;
break;
case MenuType.ControlPanel:
menuItems = DefaultCPMenus;
break;
default:
return;
}
}
menuItems.Clear();
var arr = source.Items.Cast<Control>().ToArray();
var tableList = new List<Tuple<string, string, int>>();
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
var sb =
new StringBuilder("SELECT MENU_NAME, ");
sb.Append(forCurrent ? "ORDER_NEW" : "ORDER_ORIGINAL");
sb.Append(", IFNULL(PARENT_NAME, '') AS PARENT_NAME FROM MENUS_ORDER WHERE CONTEXT_NAME = '");
sb.Append(source.Name);
sb.Append("' ORDER BY ");
sb.Append(forCurrent ? "ORDER_NEW" : "ORDER_ORIGINAL");
using (var t = oData.FillDataTable(sb.ToString()))
{
if (forCurrent)
{
tableList.AddRange(from DataRow r in t.Rows
select
Tuple.Create(Convert.ToString(r["MENU_NAME"]), Convert.ToString(r["PARENT_NAME"]),
Convert.ToInt32(r["ORDER_NEW"])));
}
else
{
tableList.AddRange(from DataRow r in t.Rows
select
Tuple.Create(Convert.ToString(r["MENU_NAME"]), Convert.ToString(r["PARENT_NAME"]),
Convert.ToInt32(r["ORDER_ORIGINAL"])));
}
var topItems = tableList.Where(tp => tp.Item2 == "");
foreach (var tp in topItems)
{
var si = arr.FirstOrDefault(a => a.Name == tp.Item1);
if (si == null) continue;
var pmi = new PNMenu(si.Name,
si.GetType() == typeof(MenuItem) ?((MenuItem) si).Header.ToString() : PNStrings.MENU_SEPARATOR_STRING, "", source.Name);
menuItems.Add(pmi);
var psi = si as MenuItem;
if (psi != null)
prepareSubitem(tableList, psi, pmi, source.Name);
}
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void LoadHiddenMenus()
{
try
{
PNStatic.HiddenMenus.Clear();
using (var oData = new SQLiteDataObject(ConnectionString))
{
using (var t = oData.FillDataTable("SELECT * FROM HIDDEN_MENUS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.HiddenMenus.Add(new PNHiddenMenu(Convert.ToString(r["MENU_NAME"]),
(MenuType)r["MENU_TYPE"]));
}
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
private void loadSingleNote(string id, bool show)
{
try
{
var groups = new List<int>();
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
var sqlQuery = "SELECT * FROM NOTES WHERE ID = '" + id + "'";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count <= 0) return;
var note = new PNote();
if (!PNNotesOperations.LoadNoteProperties(note, t.Rows[0]))
{
return;
}
//load custom settings
sqlQuery = "SELECT * FROM CUSTOM_NOTES_SETTINGS WHERE NOTE_ID = '" + note.ID + "'";
using (var tc = oData.FillDataTable(sqlQuery))
{
if (tc.Rows.Count > 0)
{
PNNotesOperations.LoadNoteCustomProperties(note, tc.Rows[0]);
}
}
//load tags
PNNotesOperations.LoadNoteTags(note);
//load linked notes
PNNotesOperations.LoadLinkedNotes(note);
//create new note window
if (note.Visible && show)
{
var showNote = true;
var pnGroup = PNStatic.Groups.GetGroupByID(note.GroupID);
if (pnGroup != null && !groups.Contains(pnGroup.ID))
{
showNote = PNNotesOperations.LoginToGroup(pnGroup, ref groups);
}
if (showNote)
{
if (note.PasswordString.Trim().Length > 0)
{
showNote &= PNNotesOperations.LoginToNote(note);
}
if (showNote)
{
note.Dialog = new WndNote(note, note.ID, NewNoteMode.Identificator);
note.Dialog.Show();
}
else
{
note.Visible = false;
}
}
}
PNStatic.Notes.Add(note);
if (NewNoteCreated != null)
{
NewNoteCreated(this, new NewNoteCreatedEventArgs(note));
}
subscribeToNoteEvents(note);
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
private static bool loadNoteSchedule(PNote note)
{
try
{
var sqlQuery = "SELECT * FROM NOTES_SCHEDULE WHERE NOTE_ID = '" + note.ID + "'";
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var mc = new MonthDayConverter();
var ac = new AlarmAfterValuesConverter();
var dw = new DaysOfWeekConverter();
var r = t.Rows[0];
note.Schedule.Type = (ScheduleType)Convert.ToInt32(r["SCHEDULE_TYPE"]);
note.Schedule.AlarmDate = DateTime.Parse((string)r["ALARM_DATE"], PNStatic.CultureInvariant);
note.Schedule.StartDate = DateTime.Parse((string)r["START_DATE"], PNStatic.CultureInvariant);
note.Schedule.LastRun = DateTime.Parse((string)r["LAST_RUN"], PNStatic.CultureInvariant);
note.Schedule.Sound = (string)r["SOUND"];
note.Schedule.StopAfter = Convert.ToInt32(r["STOP_AFTER"]);
note.Schedule.Track = Convert.ToBoolean(r["TRACK"]);
note.Schedule.RepeatCount = Convert.ToInt32(r["REPEAT_COUNT"]);
note.Schedule.SoundInLoop = Convert.ToBoolean(r["SOUND_IN_LOOP"]);
note.Schedule.UseTts = Convert.ToBoolean(r["USE_TTS"]);
note.Schedule.StartFrom = (ScheduleStart)Convert.ToInt32(r["START_FROM"]);
note.Schedule.MonthDay = (MonthDay)mc.ConvertFromString((string)r["MONTH_DAY"]);
note.Schedule.AlarmAfter = (AlarmAfterValues)ac.ConvertFromString((string)r["ALARM_AFTER"]);
note.Schedule.Weekdays = (List<DayOfWeek>)dw.ConvertFromString((string)r["WEEKDAYS"]);
if (!DBNull.Value.Equals(r["PROG_TO_RUN"]))
note.Schedule.ProgramToRunOnAlert = Convert.ToString(r["PROG_TO_RUN"]);
if (!DBNull.Value.Equals(r["CLOSE_ON_NOTIFICATION"]))
note.Schedule.CloseOnNotification = Convert.ToBoolean(r["CLOSE_ON_NOTIFICATION"]);
if (!PNData.IsDBNull(r["MULTI_ALERTS"]))
{
var arr1 = ((string)r["MULTI_ALERTS"]).Split(new[] { '^' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var ma in arr1.Select(s => s.Split('|')).Select(arr2 => new MultiAlert
{
Raised = Convert.ToBoolean(arr2[0]),
Date = DateTime.Parse(arr2[1], PNStatic.CultureInvariant)
}))
{
note.Schedule.MultiAlerts.Add(ma);
}
}
if (!PNData.IsDBNull(r["TIME_ZONE"]))
{
note.Schedule.TimeZone = TimeZoneInfo.FromSerializedString((string)r["TIME_ZONE"]);
}
if (note.Schedule.Type != ScheduleType.None)
{
note.Timer.Start();
}
}
}
}
return true;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return false;
}
}
private void startPreparingGroupsHotkeys(HotkeyType type, SQLiteDataObject oData, ref int id)
{
try
{
//delete possible duplicates first
var sqlQuery = "SELECT MAX(GROUP_ID) FROM GROUPS";
var obj = oData.GetScalar(sqlQuery);
if (obj == null || PNData.IsDBNull(obj))
return;
var maxId = Convert.ToInt32(obj);
var deleteList = new List<string>();
sqlQuery = "SELECT MENU_NAME, ID FROM HOT_KEYS WHERE HK_TYPE = 3";
using (var t = oData.FillDataTable(sqlQuery))
{
foreach (DataRow r in t.Rows)
{
var arr = Convert.ToString(r["MENU_NAME"]).Split('_');
if (Convert.ToInt32(arr[0]) <= maxId) continue;
var sb = new StringBuilder("DELETE FROM HOT_KEYS WHERE MENU_NAME = '");
sb.Append(r["MENU_NAME"]);
sb.Append("' AND ID = ");
sb.Append(r["ID"]);
deleteList.Add(sb.ToString());
}
}
if (deleteList.Count > 0)
{
PNData.ExecuteTransactionForList(deleteList, oData.ConnectionString);
}
sqlQuery = "SELECT MENU_NAME FROM HOT_KEYS WHERE HK_TYPE = " + ((int)type).ToString(CultureInfo.InvariantCulture);
using (var t = oData.FillDataTable(sqlQuery))
{
var names = (from DataRow r in t.Rows select (string)r[0]).ToList();
var group = PNStatic.Groups.GetGroupByID((int)SpecialGroups.AllGroups);
if (group != null)
{
foreach (var g in group.Subgroups)
{
prepareSingleGroupHotKey(g, oData, names, HotkeyType.Group, ref id);
}
}
}
loadSpecificHotKeys(type, oData);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static List<Tuple<string, string>> GetGoogleContacts()
{
var list = new List<Tuple<string, string>>();
try
{
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
var update = false;
using (var t = oData.FillDataTable("SELECT * FROM SERVICES WHERE APP_NAME = 'PNContactsLoader'"))
{
if (t.Rows.Count == 0) return list;
var row = t.Rows[0];
using (var enc = new PNEncryptor(PNKeys.ENC_KEY))
{
var gc = new GContacts(Convert.ToString(row["CLIENT_ID"]),
enc.DecryptString(Convert.ToString(row["CLIENT_SECRET"])),
Convert.ToString(row["APP_NAME"]));
if (!PNData.IsDBNull(row["ACCESS_TOKEN"]))
gc.AccessToken = enc.DecryptString(Convert.ToString(row["ACCESS_TOKEN"]));
if (!PNData.IsDBNull(row["REFRESH_TOKEN"]))
gc.RefreshToken = enc.DecryptString(Convert.ToString(row["REFRESH_TOKEN"]));
if (!PNData.IsDBNull(row["TOKEN_EXPIRY"]))
{
var expDate = Convert.ToDateTime(Convert.ToString(row["TOKEN_EXPIRY"]));
if (expDate <= DateTime.Now && !string.IsNullOrWhiteSpace(gc.RefreshToken) &&
!string.IsNullOrWhiteSpace(gc.AccessToken))
{
if (!string.IsNullOrWhiteSpace(gc.RefreshToken))
update = gc.RefreshAccessToken();
if (!update)
update = gc.Authenticate();
if (!update)
return list;
}
}
if (string.IsNullOrWhiteSpace(gc.AccessToken))
update = gc.Authenticate();
while (true)
{
try
{
list = gc.GetContacts();
if (update)
{
var sb = new StringBuilder("UPDATE SERVICES SET ACCESS_TOKEN = '");
sb.Append(enc.EncryptString(gc.AccessToken));
sb.Append("', REFRESH_TOKEN = '");
sb.Append(enc.EncryptString(gc.RefreshToken));
sb.Append("', TOKEN_EXPIRY = '");
sb.Append(gc.TokenExpiry.ToString("dd MMM yyyy HH:mm:ss"));
sb.Append("' WHERE APP_NAME = 'PNContactsLoader'");
oData.Execute(sb.ToString());
}
break;
}
catch (PNContactsException pnex)
{
if (pnex.AdditionalInfo.ToUpper().Contains("UNAUTHORIZED"))
{
if (!string.IsNullOrWhiteSpace(gc.RefreshToken))
update = gc.RefreshAccessToken();
if (update) continue;
update = gc.Authenticate();
if (update) continue;
PNStatic.LogException(pnex);
break;
}
PNStatic.LogException(pnex);
break;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
break;
}
}
}
}
}
return list;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return list;
}
}
private bool exchangeNotesTags(SQLiteDataObject dataSrc, SQLiteDataObject dataDest, string id, KeyValuePair<string, List<_FieldData>> tableData)
{
try
{
using (var t1 = dataSrc.FillDataTable("SELECT * FROM NOTES_TAGS WHERE NOTE_ID = '" + id + "'"))
{
using (var t2 = dataDest.FillDataTable("SELECT * FROM NOTES_TAGS WHERE NOTE_ID = '" + id + "'"))
{
if (t1.Rows.Count > 0 && t2.Rows.Count > 0)
{
var t = t1.Clone();
foreach (DataRow r1 in t1.Rows)
{
if (t.AsEnumerable().All(r => (string)r["TAG"] != (string)r1["TAG"]))
{
t.Rows.Add(r1.ItemArray);
}
}
foreach (DataRow r2 in t2.Rows)
{
if (t.AsEnumerable().All(r => (string)r["TAG"] != (string)r2["TAG"]))
{
t.Rows.Add(r2.ItemArray);
}
}
foreach (DataRow r in t.Rows)
{
if (!insertToNotesTags(dataDest, r, tableData))
{
return false;
}
if (!insertToNotesTags(dataSrc, r, tableData))
{
return false;
}
}
}
else if (t1.Rows.Count > 0)
{
if (t1.Rows.Cast<DataRow>().Any(r => !insertToNotesTags(dataDest, r, tableData)))
{
return false;
}
}
else if (t2.Rows.Count > 0)
{
if (t2.Rows.Cast<DataRow>().Any(r => !insertToNotesTags(dataSrc, r, tableData)))
{
return false;
}
}
}
}
return true;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return false;
}
}
internal static List <Tuple <string, string> > GetGoogleContacts()
{
var list = new List <Tuple <string, string> >();
try
{
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
var update = false;
using (var t = oData.FillDataTable("SELECT * FROM SERVICES WHERE APP_NAME = 'PNContactsLoader'"))
{
if (t.Rows.Count == 0)
{
return(list);
}
var row = t.Rows[0];
using (var enc = new PNEncryptor(PNKeys.ENC_KEY))
{
var gc = new GContacts(Convert.ToString(row["CLIENT_ID"]),
enc.DecryptString(Convert.ToString(row["CLIENT_SECRET"])),
Convert.ToString(row["APP_NAME"]));
if (!PNData.IsDBNull(row["ACCESS_TOKEN"]))
{
gc.AccessToken = enc.DecryptString(Convert.ToString(row["ACCESS_TOKEN"]));
}
if (!PNData.IsDBNull(row["REFRESH_TOKEN"]))
{
gc.RefreshToken = enc.DecryptString(Convert.ToString(row["REFRESH_TOKEN"]));
}
if (!PNData.IsDBNull(row["TOKEN_EXPIRY"]))
{
var expDate = Convert.ToDateTime(Convert.ToString(row["TOKEN_EXPIRY"]));
if (expDate <= DateTime.Now && !string.IsNullOrWhiteSpace(gc.RefreshToken) &&
!string.IsNullOrWhiteSpace(gc.AccessToken))
{
if (!string.IsNullOrWhiteSpace(gc.RefreshToken))
{
update = gc.RefreshAccessToken();
}
if (!update)
{
update = gc.Authenticate();
}
if (!update)
{
return(list);
}
}
}
if (string.IsNullOrWhiteSpace(gc.AccessToken))
{
update = gc.Authenticate();
}
while (true)
{
try
{
list = gc.GetContacts();
if (update)
{
var sb = new StringBuilder("UPDATE SERVICES SET ACCESS_TOKEN = '");
sb.Append(enc.EncryptString(gc.AccessToken));
sb.Append("', REFRESH_TOKEN = '");
sb.Append(enc.EncryptString(gc.RefreshToken));
sb.Append("', TOKEN_EXPIRY = '");
sb.Append(gc.TokenExpiry.ToString("dd MMM yyyy HH:mm:ss"));
sb.Append("' WHERE APP_NAME = 'PNContactsLoader'");
oData.Execute(sb.ToString());
}
break;
}
catch (PNContactsException pnex)
{
if (pnex.AdditionalInfo.ToUpper().Contains("UNAUTHORIZED"))
{
if (!string.IsNullOrWhiteSpace(gc.RefreshToken))
{
update = gc.RefreshAccessToken();
}
if (update)
{
continue;
}
update = gc.Authenticate();
if (update)
{
continue;
}
PNStatic.LogException(pnex);
break;
}
PNStatic.LogException(pnex);
break;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
break;
}
}
}
}
}
return(list);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return(list);
}
}
internal static void PrepareDefaultMenuStrip(ContextMenu source, MenuType type, bool forCurrent)
{
try
{
List <PNMenu> menuItems;
if (forCurrent)
{
switch (type)
{
case MenuType.Main:
menuItems = CurrentMainMenus;
break;
case MenuType.Edit:
menuItems = CurrentEditMenus;
break;
case MenuType.Note:
menuItems = CurrentNoteMenus;
break;
case MenuType.ControlPanel:
menuItems = CurrentCPMenus;
break;
default:
return;
}
}
else
{
switch (type)
{
case MenuType.Main:
menuItems = DefaultMainMenus;
break;
case MenuType.Edit:
menuItems = DefaultEditMenus;
break;
case MenuType.Note:
menuItems = DefaultNoteMenus;
break;
case MenuType.ControlPanel:
menuItems = DefaultCPMenus;
break;
default:
return;
}
}
menuItems.Clear();
var arr = source.Items.Cast <Control>().ToArray();
var tableList = new List <Tuple <string, string, int> >();
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
var sb =
new StringBuilder("SELECT MENU_NAME, ");
sb.Append(forCurrent ? "ORDER_NEW" : "ORDER_ORIGINAL");
sb.Append(", IFNULL(PARENT_NAME, '') AS PARENT_NAME FROM MENUS_ORDER WHERE CONTEXT_NAME = '");
sb.Append(source.Name);
sb.Append("' ORDER BY ");
sb.Append(forCurrent ? "ORDER_NEW" : "ORDER_ORIGINAL");
using (var t = oData.FillDataTable(sb.ToString()))
{
if (forCurrent)
{
tableList.AddRange(from DataRow r in t.Rows
select
Tuple.Create(Convert.ToString(r["MENU_NAME"]), Convert.ToString(r["PARENT_NAME"]),
Convert.ToInt32(r["ORDER_NEW"])));
}
else
{
tableList.AddRange(from DataRow r in t.Rows
select
Tuple.Create(Convert.ToString(r["MENU_NAME"]), Convert.ToString(r["PARENT_NAME"]),
Convert.ToInt32(r["ORDER_ORIGINAL"])));
}
var topItems = tableList.Where(tp => tp.Item2 == "");
foreach (var tp in topItems)
{
var si = arr.FirstOrDefault(a => a.Name == tp.Item1);
if (si == null)
{
continue;
}
var pmi = new PNMenu(si.Name,
si.GetType() == typeof(MenuItem) ?((MenuItem)si).Header.ToString() : PNStrings.MENU_SEPARATOR_STRING, "", source.Name);
menuItems.Add(pmi);
var psi = si as MenuItem;
if (psi != null)
{
prepareSubitem(tableList, psi, pmi, source.Name);
}
}
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}