private string saveReplace()
{
try
{
var str = cboReplace.Text.Trim();
if (cboReplace.Items.Contains(str))
{
return(str);
}
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
var sb = new StringBuilder();
sb.Append("UPDATE FIND_REPLACE SET REPLACE = '");
sb.Append(str);
foreach (string s in cboReplace.Items)
{
sb.Append(",");
sb.Append(s);
}
sb.Append("'");
oData.Execute(sb.ToString());
cboReplace.Items.Insert(0, str);
}
return(str);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return("");
}
}
internal static void SaveExitFlag(int flag)
{
try
{
using (var oData = new SQLiteDataObject(SettingsConnectionString))
{
var sqlQuery = "UPDATE CONFIG SET EXIT_FLAG = " + flag.ToString(PNStatic.CultureInvariant);
oData.Execute(sqlQuery);
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal bool restoreTriggers(string dbPath)
{
try
{
var connSrc = "data source = " + dbPath;
using (var src = new SQLiteDataObject(connSrc))
{
src.Execute(PNStrings.CREATE_TRIGGERS);
}
return(true);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return(false);
}
}
private void clearSearchHistory()
{
try
{
var message = PNLang.Instance.GetMessageText("clear_search_history", "Clear search history?");
if (PNMessageBox.Show(message, PNStrings.PROG_NAME, MessageBoxButton.YesNo, MessageBoxImage.Question) !=
MessageBoxResult.Yes)
{
return;
}
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute("UPDATE FIND_REPLACE SET FIND = NULL, REPLACE = NULL");
cboFind.Items.Clear();
cboReplace.Items.Clear();
cboFind.Text = "";
cboReplace.Text = "";
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void SaveNotePin(PNote note)
{
try
{
if (note.FromDB)
{
var sb = new StringBuilder();
sb.Append("UPDATE NOTES SET PINNED = ");
sb.Append(Convert.ToInt32(note.Pinned));
sb.Append(", PIN_CLASS = '");
sb.Append(note.PinClass);
sb.Append("', PIN_TEXT = '");
sb.Append(note.PinText);
sb.Append("' WHERE ID = '");
sb.Append(note.ID);
sb.Append("'");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void RemoveCustomNotesSettings(string id)
{
try
{
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
var sb = new StringBuilder("DELETE FROM CUSTOM_NOTES_SETTINGS WHERE NOTE_ID = '");
sb.Append(id);
sb.Append("'");
oData.Execute(sb.ToString());
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void DeleteNoteSchedule(PNote note)
{
try
{
var sb = new StringBuilder();
sb.Append("DELETE FROM NOTES_SCHEDULE WHERE NOTE_ID = '");
sb.Append(note.ID);
sb.Append("'");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void SaveNoteOnSend(PNote note)
{
try
{
var sb = new StringBuilder();
note.DateSaved = DateTime.Now;
sb.Append("UPDATE NOTES SET DATE_SENT = '");
sb.Append(note.DateSaved.ToString(PNStrings.DATE_TIME_FORMAT, PNStatic.CultureInvariant).Replace("'", "''"));
sb.Append("', SEND_RECEIVE_STATUS = ");
sb.Append(Convert.ToInt32(note.SentReceived));
sb.Append(", SENT_TO = '");
sb.Append(note.SentTo.Replace("'", "''"));
sb.Append("' WHERE ID = '");
sb.Append(note.ID);
sb.Append("'");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static bool SaveNoteDeletedState(PNote note, bool toBin)
{
try
{
var sb = new StringBuilder();
if (toBin)
{
sb.Append("UPDATE NOTES SET VISIBLE = 0, GROUP_ID = ");
sb.Append(note.GroupID);
sb.Append(", PREV_GROUP_ID = ");
sb.Append(note.PrevGroupID);
sb.Append(", DATE_DELETED = '");
sb.Append(note.DateDeleted.ToString(PNStrings.DATE_TIME_FORMAT, PNStatic.CultureInvariant).Replace("'", "''"));
sb.Append("' WHERE ID = '");
sb.Append(note.ID);
sb.Append("'; ");
}
else
{
sb.Append("DELETE FROM NOTES WHERE ID = '");
sb.Append(note.ID);
sb.Append("'; ");
//all other tables should be here
}
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
return true;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return false;
}
}
internal static bool SaveNewNote(PNote note)
{
try
{
var sb = new StringBuilder();
note.DateSaved = DateTime.Now;
sb.Append("INSERT INTO NOTES (ID, NAME, GROUP_ID, PREV_GROUP_ID, OPACITY, VISIBLE, FAVORITE, PROTECTED, COMPLETED, PRIORITY, PASSWORD_STRING, PINNED, TOPMOST, ROLLED, DOCK_STATUS, DOCK_ORDER, SEND_RECEIVE_STATUS, DATE_CREATED, DATE_SAVED, DATE_SENT, DATE_RECEIVED, DATE_DELETED, SIZE, LOCATION, EDIT_SIZE, REL_X, REL_Y, SENT_TO, RECEIVED_FROM, PIN_CLASS, PIN_TEXT, SCRAMBLED, RECEIVED_IP) VALUES('");
sb.Append(note.ID);
sb.Append("', '");
sb.Append(note.Name.Replace("'", "''"));
sb.Append("', ");
sb.Append(note.GroupID);
sb.Append(", ");
sb.Append(note.GroupID); //as prev png
sb.Append(", ");
sb.Append(note.Opacity.ToString(PNStatic.CultureInvariant));
sb.Append(", ");
sb.Append(Convert.ToInt32(note.Visible));
sb.Append(", ");
sb.Append(Convert.ToInt32(note.Favorite));
sb.Append(", ");
sb.Append(Convert.ToInt32(note.Protected));
sb.Append(", ");
sb.Append(Convert.ToInt32(note.Completed));
sb.Append(", ");
sb.Append(Convert.ToInt32(note.Priority));
sb.Append(", '");
sb.Append(note.PasswordString.Replace("'", "''"));
sb.Append("', ");
sb.Append(Convert.ToInt32(note.Pinned));
sb.Append(", ");
sb.Append(Convert.ToInt32(note.Topmost));
sb.Append(", ");
sb.Append(Convert.ToInt32(note.Rolled));
sb.Append(", ");
sb.Append(Convert.ToInt32(note.DockStatus));
sb.Append(", ");
sb.Append(Convert.ToInt32(note.DockOrder));
sb.Append(", ");
sb.Append(Convert.ToInt32(note.SentReceived));
sb.Append(", '");
sb.Append(note.DateCreated.ToString(PNStrings.DATE_TIME_FORMAT, PNStatic.CultureInvariant).Replace("'", "''"));
sb.Append("', '");
sb.Append(note.DateSaved.ToString(PNStrings.DATE_TIME_FORMAT, PNStatic.CultureInvariant).Replace("'", "''"));
sb.Append("', '");
sb.Append(note.DateSent.ToString(PNStrings.DATE_TIME_FORMAT, PNStatic.CultureInvariant).Replace("'", "''"));
sb.Append("', '");
sb.Append(note.DateReceived.ToString(PNStrings.DATE_TIME_FORMAT, PNStatic.CultureInvariant).Replace("'", "''"));
sb.Append("', '");
sb.Append(note.DateDeleted.ToString(PNStrings.DATE_TIME_FORMAT, PNStatic.CultureInvariant).Replace("'", "''"));
sb.Append("', '");
var sc = new SizeConverter();
sb.Append(sc.ConvertToString(null, PNStatic.CultureInvariant, note.NoteSize));
sb.Append("', '");
var pc = new PointConverter();
sb.Append(pc.ConvertToString(null, PNStatic.CultureInvariant, note.NoteLocation));
sb.Append("', '");
var scd = new System.Drawing.SizeConverter();
sb.Append(scd.ConvertToString(null, PNStatic.CultureInvariant, note.EditSize));
sb.Append("', ");
var factors = getRelationalFactors(note);
sb.Append(factors[0].ToString(PNStatic.CultureInvariant));
sb.Append(", ");
sb.Append(factors[1].ToString(PNStatic.CultureInvariant));
sb.Append(", '");
sb.Append(note.SentTo.Replace("'", "''"));
sb.Append("', '");
sb.Append(note.ReceivedFrom.Replace("'", "''"));
sb.Append("', '");
sb.Append(note.PinClass);
sb.Append("', '");
sb.Append(note.PinText);
sb.Append("', ");
sb.Append(Convert.ToInt32(note.Scrambled));
sb.Append(", '");
sb.Append(note.ReceivedIp);
sb.Append("'");
sb.Append(")");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
note.FromDB = true;
if (note.Skinless != null)
{
SaveNoteSkinless(note);
}
return true;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return false;
}
}
internal static void AddNewTags(IEnumerable<string> tags)
{
try
{
using (var oData = new SQLiteDataObject(ConnectionString))
{
foreach (var t in tags)
{
var sb = new StringBuilder("SELECT TAG FROM TAGS WHERE TAG = '");
sb.Append(t.Replace("'", "''"));
sb.Append("'");
var obj = oData.GetScalar(sb.ToString());
if (obj != null && !IsDBNull(obj)) continue;
sb = new StringBuilder("INSERT INTO TAGS VALUES('");
sb.Append(t.Replace("'", "''"));
sb.Append("')");
oData.Execute(sb.ToString());
PNStatic.Tags.Add(t);
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void SaveNotesWithShortcuts(IEnumerable<string> ids)
{
try
{
var sb = new StringBuilder("UPDATE CONFIG SET NOTES_WITH_SHORTCUTS = '");
if (ids != null)
{
sb.Append(string.Join(",", ids));
}
sb.Append("'");
using (var oData = new SQLiteDataObject(SettingsConnectionString))
{
oData.Execute(sb.ToString());
}
}
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();
}
}
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 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 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 saveFind()
{
try
{
var str = cboFind.Text.Trim();
if (cboFind.Items.Contains(str)) return;
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
var sb = new StringBuilder();
sb.Append("UPDATE FIND_REPLACE SET FIND = '");
sb.Append(str);
foreach (string s in cboFind.Items)
{
sb.Append(",");
sb.Append(s);
}
sb.Append("'");
oData.Execute(sb.ToString());
cboFind.Items.Insert(0, str);
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
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);
}
}
internal static void SaveLastPage()
{
try
{
using (var oData = new SQLiteDataObject(SettingsConnectionString))
{
var sqlQuery = "UPDATE CONFIG SET LAST_PAGE = " + PNStatic.Settings.Config.LastPage.ToString(PNStatic.CultureInvariant);
oData.Execute(sqlQuery);
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void SaveNoteDeletedDate(PNote note)
{
try
{
var sb = new StringBuilder();
sb.Append("UPDATE NOTES SET DATE_DELETED = '");
sb.Append(note.DateDeleted.ToString(PNStrings.DATE_TIME_FORMAT, PNStatic.CultureInvariant).Replace("'", "''"));
sb.Append("' WHERE ID = '");
sb.Append(note.ID);
sb.Append("'; ");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void SaveControlsStyle(bool isDeafault)
{
try
{
using (var oData = new SQLiteDataObject(SettingsConnectionString))
{
var sqlQuery = !isDeafault
? "UPDATE CONFIG SET CONTROLS_STYLE = '" +
PNStatic.Settings.Config.ControlsStyle +
"'"
: "UPDATE CONFIG SET CONTROLS_STYLE = NULL";
oData.Execute(sqlQuery);
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void RemoveDeletedNoteFromLists(PNote note)
{
try
{
foreach (var n in PNStatic.Notes)
{
n.LinkedNotes.RemoveAll(l => l == note.ID);
}
//clean tables
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
var sb = new StringBuilder();
sb.Append("DELETE FROM LINKED_NOTES WHERE LINK_ID = '");
sb.Append(note.ID);
sb.Append("';");
oData.Execute(sb.ToString());
}
//remove all backup copies
var di = new DirectoryInfo(PNPaths.Instance.BackupDir);
var fis = di.GetFiles(note.ID + "*" + PNStrings.NOTE_BACK_EXTENSION);
foreach (var fi in fis)
{
fi.Delete();
}
fis = di.GetFiles(note.ID + "*" + PNStrings.NOTE_AUTO_BACK_EXTENSION);
foreach (var fi in fis)
{
fi.Delete();
}
}
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);
}
}
internal static void SaveNoteDockStatus(PNote note)
{
try
{
var sb = new StringBuilder();
note.DateSaved = DateTime.Now;
sb.Append("UPDATE NOTES SET DOCK_STATUS = ");
sb.Append(Convert.ToInt32(note.DockStatus));
sb.Append(", DOCK_ORDER = ");
sb.Append(Convert.ToInt32(note.DockOrder));
sb.Append(" WHERE ID = '");
sb.Append(note.ID);
sb.Append("'");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void SaveNoteSkinless(PNote note)
{
try
{
var cr = new ColorConverter();
var lfc = new WPFFontConverter();
var sb = new StringBuilder();
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
if (isNotePresentedInCustomSettings(note))
{
sb.Append("UPDATE CUSTOM_NOTES_SETTINGS SET BACK_COLOR = ");
if (note.Skinless != null)
{
sb.Append("'");
sb.Append(cr.ConvertToString(null, PNStatic.CultureInvariant, note.Skinless.BackColor));
sb.Append("'");
}
else
{
sb.Append("NULL");
}
sb.Append(", CAPTION_FONT_COLOR = ");
if (note.Skinless != null)
{
sb.Append("'");
sb.Append(cr.ConvertToString(null, PNStatic.CultureInvariant, note.Skinless.CaptionColor));
sb.Append("'");
}
else
{
sb.Append("NULL");
}
sb.Append(", CAPTION_FONT = ");
if (note.Skinless != null)
{
sb.Append("'");
sb.Append(lfc.ConvertToString(note.Skinless.CaptionFont));
sb.Append("'");
}
else
{
sb.Append("NULL");
}
sb.Append(" WHERE NOTE_ID = '");
sb.Append(note.ID);
sb.Append("'");
}
else
{
sb.Append("INSERT INTO CUSTOM_NOTES_SETTINGS (NOTE_ID, BACK_COLOR, CAPTION_FONT_COLOR, CAPTION_FONT) VALUES(");
if (note.Skinless != null)
{
sb.Append("'");
sb.Append(note.ID);
sb.Append("', '");
sb.Append(cr.ConvertToString(null, PNStatic.CultureInvariant, note.Skinless.BackColor));
sb.Append("', '");
sb.Append(cr.ConvertToString(null, PNStatic.CultureInvariant, note.Skinless.CaptionColor));
sb.Append("', '");
sb.Append(lfc.ConvertToString(note.Skinless.CaptionFont));
sb.Append("')");
}
else
{
sb.Append("'");
sb.Append(note.ID);
sb.Append("', NULL, NULL, NULL)");
}
}
oData.Execute(sb.ToString());
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void SaveNoteSchedule(PNote note)
{
try
{
var ac = new AlarmAfterValuesConverter();
var mc = new MonthDayConverter();
var dc = new DaysOfWeekConverter();
var sb = new StringBuilder();
DeleteNoteSchedule(note);
sb.Append("INSERT INTO NOTES_SCHEDULE (NOTE_ID,SCHEDULE_TYPE,ALARM_DATE,START_DATE,LAST_RUN,SOUND,STOP_AFTER,TRACK,REPEAT_COUNT,SOUND_IN_LOOP,USE_TTS,START_FROM,MONTH_DAY,ALARM_AFTER,WEEKDAYS,PROG_TO_RUN,CLOSE_ON_NOTIFICATION,MULTI_ALERTS,TIME_ZONE) VALUES('");
sb.Append(note.ID);
sb.Append("',");
sb.Append(Convert.ToInt32(note.Schedule.Type));
sb.Append(",'");
sb.Append(note.Schedule.AlarmDate.ToString(PNStrings.DATE_TIME_FORMAT, PNStatic.CultureInvariant).Replace("'", "''"));
sb.Append("','");
sb.Append(note.Schedule.StartDate.ToString(PNStrings.DATE_TIME_FORMAT, PNStatic.CultureInvariant).Replace("'", "''"));
sb.Append("','");
sb.Append(note.Schedule.LastRun.ToString(PNStrings.DATE_TIME_FORMAT, PNStatic.CultureInvariant).Replace("'", "''"));
sb.Append("','");
sb.Append(note.Schedule.Sound);
sb.Append("',");
sb.Append(note.Schedule.StopAfter);
sb.Append(",");
sb.Append(Convert.ToInt32(note.Schedule.Track));
sb.Append(",");
sb.Append(note.Schedule.RepeatCount);
sb.Append(",");
sb.Append(Convert.ToInt32(note.Schedule.SoundInLoop));
sb.Append(",");
sb.Append(Convert.ToInt32(note.Schedule.UseTts));
sb.Append(",");
sb.Append(Convert.ToInt32(note.Schedule.StartFrom));
sb.Append(",'");
sb.Append(mc.ConvertToString(note.Schedule.MonthDay));
sb.Append("','");
sb.Append(ac.ConvertToString(note.Schedule.AlarmAfter));
sb.Append("','");
sb.Append(dc.ConvertToString(note.Schedule.Weekdays));
sb.Append("','");
sb.Append(note.Schedule.ProgramToRunOnAlert);
sb.Append("',");
sb.Append(Convert.ToInt32(note.Schedule.CloseOnNotification));
//MULTI_ALERTS
if (note.Schedule.MultiAlerts.Any())
{
var temp = new StringBuilder();
foreach (var ma in note.Schedule.MultiAlerts)
{
temp.Append(ma.Raised);
temp.Append("|");
temp.Append(ma.Date.ToString(PNStrings.DATE_TIME_FORMAT, PNStatic.CultureInvariant).Replace("'", "''"));
temp.Append("^");
}
sb.Append(",'");
sb.Append(temp);
sb.Append("'");
}
else
{
sb.Append(",NULL");
}
//TIME_ZONE
if (note.Schedule.TimeZone != null)
{
sb.Append(",'");
sb.Append(note.Schedule.TimeZone.ToSerializedString());
sb.Append("'");
}
else
{
sb.Append(",NULL");
}
sb.Append(")");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void SaveNoteSize(PNote note, Size size, System.Drawing.Size editSize)
{
try
{
note.NoteSize = size;
note.EditSize = editSize;
if (note.FromDB)
{
var sc = new SizeConverter();
var scd = new System.Drawing.SizeConverter();
var sb = new StringBuilder();
sb.Append("UPDATE NOTES SET SIZE = '");
sb.Append(sc.ConvertToString(null, PNStatic.CultureInvariant, note.NoteSize));
sb.Append("', EDIT_SIZE = '");
sb.Append(scd.ConvertToString(null, PNStatic.CultureInvariant, note.EditSize));
sb.Append("' WHERE ID = '");
sb.Append(note.ID);
sb.Append("'");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void SaveNoteSkin(PNote note)
{
try
{
var sb = new StringBuilder();
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
if (isNotePresentedInCustomSettings(note))
{
sb.Append("UPDATE CUSTOM_NOTES_SETTINGS SET SKIN_NAME = ");
if (note.Skin != null)
{
sb.Append("'");
sb.Append(note.Skin.SkinName);
sb.Append("'");
}
else
{
sb.Append("NULL");
}
sb.Append(" WHERE NOTE_ID = '");
sb.Append(note.ID);
sb.Append("'");
}
else
{
sb.Append("INSERT INTO CUSTOM_NOTES_SETTINGS (NOTE_ID, SKIN_NAME) VALUES(");
if (note.Skin != null)
{
sb.Append("'");
sb.Append(note.ID);
sb.Append("', '");
sb.Append(note.Skin.SkinName);
sb.Append("')");
}
else
{
sb.Append("'");
sb.Append(note.ID);
sb.Append("', NULL)");
}
}
oData.Execute(sb.ToString());
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
private bool saveHotkeys()
{
try
{
var sqlList = new List <string>();
var count = m_KeysMain.Count;
for (var i = 0; i < count; i++)
{
if (PNStatic.HotKeysMain[i] == m_KeysMain[i])
{
continue;
}
//first unregister existing hot key
if (PNStatic.HotKeysMain[i].VK != 0)
{
HotkeysStatic.UnregisterHK(PNStatic.FormMain.Handle, PNStatic.HotKeysMain[i].ID);
}
//if new hot key has been set
if (m_KeysMain[i].VK != 0)
{
//now try to register
if (!HotkeysStatic.RegisterHK(PNStatic.FormMain.Handle, m_KeysMain[i]))
{
continue;
}
//copy hot key
PNStatic.HotKeysMain[i].CopyFrom(m_KeysMain[i]);
var sqlQuery = createHotKeyUpdate(m_KeysMain[i]);
if (sqlQuery != "")
{
sqlList.Add(sqlQuery);
}
}
else
{
//hot key has been deleted
PNStatic.HotKeysMain[i].CopyFrom(m_KeysMain[i]);
var sqlQuery = createHotKeyDelete(m_KeysMain[i]);
if (sqlQuery != "")
{
sqlList.Add(sqlQuery);
}
}
}
count = m_KeysNote.Count;
for (int i = 0; i < count; i++)
{
if (PNStatic.HotKeysNote[i] == m_KeysNote[i])
{
continue;
}
//first unregister existing hot key
if (PNStatic.HotKeysNote[i].VK != 0)
{
HotkeysStatic.UnregisterHK(PNStatic.FormMain.Handle, PNStatic.HotKeysNote[i].ID);
}
//if new hot key has been set
if (m_KeysNote[i].VK != 0)
{
//now try to register
if (!HotkeysStatic.RegisterHK(PNStatic.FormMain.Handle, m_KeysNote[i]))
{
continue;
}
//copy hot key
PNStatic.HotKeysNote[i].CopyFrom(m_KeysNote[i]);
var sqlQuery = createHotKeyUpdate(m_KeysNote[i]);
if (sqlQuery != "")
{
sqlList.Add(sqlQuery);
}
}
else
{
//hot key has been deleted
PNStatic.HotKeysNote[i].CopyFrom(m_KeysNote[i]);
var sqlQuery = createHotKeyDelete(m_KeysNote[i]);
if (sqlQuery != "")
{
sqlList.Add(sqlQuery);
}
}
////copy hot key
//PNStatic.HotKeysNote[i].CopyFrom(m_KeysNote[i]);
//var sqlQuery = m_KeysNote[i].VK != 0
// ? createHotKeyUpdate(m_KeysNote[i])
// : createHotKeyDelete(m_KeysNote[i]);
//if (sqlQuery != "")
//{
// sqlList.Add(sqlQuery);
//}
}
count = m_KeysEdit.Count;
for (int i = 0; i < count; i++)
{
if (PNStatic.HotKeysEdit[i] == m_KeysEdit[i])
{
continue;
}
//first unregister existing hot key
if (PNStatic.HotKeysEdit[i].VK != 0)
{
HotkeysStatic.UnregisterHK(PNStatic.FormMain.Handle, PNStatic.HotKeysEdit[i].ID);
}
//if new hot key has been set
if (m_KeysEdit[i].VK != 0)
{
//now try to register
if (!HotkeysStatic.RegisterHK(PNStatic.FormMain.Handle, m_KeysEdit[i]))
{
continue;
}
//copy hot key
PNStatic.HotKeysEdit[i].CopyFrom(m_KeysEdit[i]);
var sqlQuery = createHotKeyUpdate(m_KeysEdit[i]);
if (sqlQuery != "")
{
sqlList.Add(sqlQuery);
}
}
else
{
//hot key has been deleted
PNStatic.HotKeysEdit[i].CopyFrom(m_KeysEdit[i]);
var sqlQuery = createHotKeyDelete(m_KeysEdit[i]);
if (sqlQuery != "")
{
sqlList.Add(sqlQuery);
}
}
////copy hot key
//PNStatic.HotKeysEdit[i].CopyFrom(m_KeysEdit[i]);
//var sqlQuery = m_KeysEdit[i].VK != 0
// ? createHotKeyUpdate(m_KeysEdit[i])
// : createHotKeyDelete(m_KeysEdit[i]);
//if (sqlQuery != "")
//{
// sqlList.Add(sqlQuery);
//}
}
count = m_KeysGroups.Count;
for (var i = 0; i < count; i++)
{
if (PNStatic.HotKeysGroups[i] == m_KeysGroups[i])
{
continue;
}
//first unregister existing hot key
if (PNStatic.HotKeysGroups[i].VK != 0)
{
HotkeysStatic.UnregisterHK(PNStatic.FormMain.Handle, PNStatic.HotKeysGroups[i].ID);
}
//if new hot key has been set
if (m_KeysGroups[i].VK != 0)
{
//now try to register
if (!HotkeysStatic.RegisterHK(PNStatic.FormMain.Handle, m_KeysGroups[i]))
{
continue;
}
//copy hot key
PNStatic.HotKeysGroups[i].CopyFrom(m_KeysGroups[i]);
var sqlQuery = createHotKeyUpdate(m_KeysGroups[i]);
if (sqlQuery != "")
{
sqlList.Add(sqlQuery);
}
}
else
{
//hot key has been deleted
PNStatic.HotKeysGroups[i].CopyFrom(m_KeysGroups[i]);
var sqlQuery = createHotKeyDelete(m_KeysGroups[i]);
if (sqlQuery != "")
{
sqlList.Add(sqlQuery);
}
}
}
if (sqlList.Count <= 0)
{
return(false);
}
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
foreach (var s in sqlList)
{
oData.Execute(s);
}
}
return(true);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return(false);
}
}
internal static void SaveNoteOpacity(PNote note)
{
try
{
var sb = new StringBuilder();
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
sb.Append("UPDATE NOTES SET OPACITY = ");
sb.Append(note.Opacity.ToString(PNStatic.CultureInvariant));
sb.Append(" WHERE ID = '");
sb.Append(note.ID);
sb.Append("'");
oData.Execute(sb.ToString());
sb = new StringBuilder();
if (isNotePresentedInCustomSettings(note))
{
sb.Append("UPDATE CUSTOM_NOTES_SETTINGS SET CUSTOM_OPACITY = ");
sb.Append(Convert.ToInt32(note.CustomOpacity));
sb.Append(" WHERE NOTE_ID = '");
sb.Append(note.ID);
sb.Append("'");
}
else
{
sb.Append("INSERT INTO CUSTOM_NOTES_SETTINGS (NOTE_ID, CUSTOM_OPACITY) VALUES('");
sb.Append(note.ID);
sb.Append("', ");
sb.Append(Convert.ToInt32(note.CustomOpacity));
sb.Append(")");
}
oData.Execute(sb.ToString());
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static bool SaveBooleanChange(PNote note, bool value, string field)
{
try
{
var sb = new StringBuilder();
sb.Append("UPDATE NOTES SET ");
sb.Append(field);
sb.Append(" = ");
sb.Append(Convert.ToInt32(value));
sb.Append(" WHERE ID = '");
sb.Append(note.ID);
sb.Append("'");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
return true;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return false;
}
}
internal static void SaveNoteThumbnail(PNote note)
{
try
{
if (note.FromDB)
{
var sb = new StringBuilder();
sb.Append("UPDATE NOTES SET THUMBNAIL = ");
sb.Append(Convert.ToInt32(note.Thumbnail));
sb.Append(" WHERE ID = '");
sb.Append(note.ID);
sb.Append("'");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void SaveGroupChange(PNote note)
{
try
{
var sb = new StringBuilder();
sb.Append("UPDATE NOTES SET GROUP_ID = ");
sb.Append(note.GroupID);
sb.Append(", PREV_GROUP_ID = ");
sb.Append(note.PrevGroupID);
sb.Append(" WHERE ID = '");
sb.Append(note.ID);
sb.Append("'");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void SaveNoteLocation(PNote note, Point location)
{
try
{
note.NoteLocation = location;
var factors = getRelationalFactors(note);
note.XFactor = factors[0];
note.YFactor = factors[1];
if (note.FromDB)
{
var pc = new PointConverter();
var sb = new StringBuilder();
sb.Append("UPDATE NOTES SET LOCATION = '");
sb.Append(pc.ConvertToString(null, PNStatic.CultureInvariant, note.NoteLocation));
sb.Append("', REL_X = ");
sb.Append(factors[0].ToString(PNStatic.CultureInvariant));
sb.Append(", REL_Y = ");
sb.Append(factors[1].ToString(PNStatic.CultureInvariant));
sb.Append(" WHERE ID = '");
sb.Append(note.ID);
sb.Append("'");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static bool SavePasswordChange(PNote note)
{
try
{
var sb = new StringBuilder();
sb.Append("UPDATE NOTES SET PASSWORD_STRING = '");
sb.Append(note.PasswordString.Replace("'", "''"));
sb.Append("' WHERE ID = '");
sb.Append(note.ID);
sb.Append("'");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
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 bool SaveExistingNote(PNote note)
{
try
{
var sb = new StringBuilder();
note.DateSaved = DateTime.Now;
sb.Append("UPDATE NOTES SET NAME = '");
sb.Append(note.Name.Replace("'", "''"));
sb.Append("', GROUP_ID = ");
sb.Append(note.GroupID);
sb.Append(", PREV_GROUP_ID = ");
sb.Append(note.PrevGroupID);
sb.Append(", DATE_SAVED = '");
sb.Append(note.DateSaved.ToString(PNStrings.DATE_TIME_FORMAT, PNStatic.CultureInvariant).Replace("'", "''"));
sb.Append("' WHERE ID = '");
sb.Append(note.ID);
sb.Append("'");
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
oData.Execute(sb.ToString());
}
return true;
}
catch (Exception ex)
{
PNStatic.LogException(ex);
return false;
}
}