internal static void SetSettings(string key, string value)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return;
}
string oldValue = GetSettingsString(key);
if (oldValue == null)
{
string cmd = "INSERT INTO Settings (Key, Value) VALUES (?, ?)";
var command = databaseConnection.CreateCommand(cmd, new object[] { key, value });
command.ExecuteNonQuery();
}
else
{
string cmd = "UPDATE Settings SET Value = ? WHERE Key = ?";
var command = databaseConnection.CreateCommand(cmd, new object[] { value, key });
command.ExecuteNonQuery();
}
}
internal static void SetShoppingItemQuantity(int articleId, decimal newdQuantity)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return;
}
SQLiteCommand command;
string cmd = string.Empty;
bool isInList = Database.IsArticleInShoppingList(articleId);
if (!isInList)
{
cmd = "INSERT INTO ShoppingList (ArticleId, Quantity) VALUES (?, ?)";
command = databaseConnection.CreateCommand(cmd, new object[] { articleId, newdQuantity });
}
else
{
cmd = "UPDATE ShoppingList SET Quantity = ? WHERE ArticleId = ?";
command = databaseConnection.CreateCommand(cmd, new object[] { newdQuantity, articleId });
}
command.ExecuteNonQuery();
}
/// <inheritdoc />
public override Task RemoveAsync(IEntry entry, CancellationToken cancellationToken)
{
_connection
.CreateCommand("DELETE FROM props WHERE path=?", entry.Path.ToString())
.ExecuteNonQuery();
if (entry is ICollection)
{
_connection
.CreateCommand("DELETE FROM props WHERE path like '?%'", entry.Path.ToString())
.ExecuteNonQuery();
}
return(Task.FromResult(0));
}
internal static ArticleImage GetArticleImage(int articleId, bool showLarge)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return(null);
}
string cmd = string.Empty;
cmd += "SELECT ImageId, ArticleId, Type, ";
if (showLarge)
{
cmd += "ImageLarge";
}
else
{
cmd += "ImageSmall";
}
cmd += " FROM ArticleImage";
cmd += " WHERE ArticleId = ?";
cmd += " AND Type = 0";
var command = databaseConnection.CreateCommand(cmd, new object[] { articleId });
return(command.ExecuteQuery <ArticleImage>().FirstOrDefault());
}
internal static List <string> GetCategoryNames()
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
// Artikel suchen, die schon abgelaufen sind.
string cmd = string.Empty;
cmd += "SELECT DISTINCT Category AS Value";
cmd += " FROM Article";
cmd += " WHERE Category IS NOT NULL";
cmd += " ORDER BY Category COLLATE NOCASE";
SQLiteCommand command = databaseConnection.CreateCommand(cmd, new object[] { });
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
IList <StringResult> result = command.ExecuteQuery <StringResult>();
stopWatch.Stop();
Tools.TRACE("Dauer der Abfrage für DISTINCT Category: {0}", stopWatch.Elapsed.ToString());
List <string> stringList = new List <string>();
foreach (StringResult item in result)
{
stringList.Add(item.Value);
}
return(stringList);
}
internal static List <string> GetManufacturerNames()
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
// Artikel suchen, die schon abgelaufen sind.
string cmd = string.Empty;
cmd += "SELECT DISTINCT Manufacturer AS Value";
cmd += " FROM Article";
cmd += " WHERE Manufacturer IS NOT NULL";
cmd += " AND Manufacturer <> ''";
cmd += " ORDER BY Manufacturer COLLATE NOCASE";
SQLiteCommand command = databaseConnection.CreateCommand(cmd, new object[] { });
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
IList <StringResult> result = command.ExecuteQuery <StringResult>();
stopWatch.Stop();
Tools.TRACE("Dauer der Abfrage für DISTINCT Manufacturer: {0}", stopWatch.Elapsed.ToString());
List <string> stringList = new List <string>();
for (int i = 0; i < result.Count; i++)
{
string supermarketName = result[i].Value;
stringList.Add(supermarketName);
}
return(stringList);
}
protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
{
base.OnNavigatedTo(e);
string ISBN = "";
if (NavigationContext.QueryString.TryGetValue("BookISBN", out ISBN))
{
currentISBN = ISBN;
dbPath = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "jadeface.sqlite"));
dbConn = new SQLiteConnection(dbPath);
SQLiteCommand command = dbConn.CreateCommand("select * from booklistitem where isbn = '" + ISBN + "'");
List<BookListItem> books = command.ExecuteQuery<BookListItem>();
if (books.Count == 1)
{
book = books.First();
BookInformationGrid.DataContext = book;
StartPage.Text = "";
EndPage.Text = "";
ReadingRecordPanel.DataContext = record;
}
Debug.WriteLine("[DEBUG]Navigate to ReadingRecordPage...");
bookService = BookService.getInstance();
RefreshReadingRecord();
}
else
{
MessageBox.Show("读书记录页面加载出错！");
NavigationService.Navigate(new Uri("/MainPage.xaml", UriKind.Relative));
}
}
protected override void OnNavigatedTo(NavigationEventArgs e)
{
bookService = BookService.getInstance();
this.datePicker.ValueChanged += new EventHandler<DateTimeValueChangedEventArgs>(picker_ValueChanged);
List<string> pl = new List<string>() { "高", "中", "低" };
this.prioritylist.ItemsSource = pl;
this.prioritylist.SelectedItem = pl[1];
string username = phoneAppServeice.State["username"].ToString();
dbPath = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "jadeface.sqlite"));
dbConn = new SQLiteConnection(dbPath);
SQLiteCommand command = dbConn.CreateCommand("select * from booklistitem where userid = '" + username + "' and status = 1");
List<BookListItem> books = command.ExecuteQuery<BookListItem>();
Debug.WriteLine("[DEBUG]The count of book name list : " + books.Count);
List<string> bookns = new List<string>();
foreach (BookListItem book in books)
{
bookns.Add(book.Title);
}
bl = books;
this.booknamelist.ItemsSource = bookns;
base.OnNavigatedTo(e);
}
protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
{
base.OnNavigatedTo(e);
if (NavigationContext.QueryString.TryGetValue("BookISBN", out ISBN))
{
dbPath = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "jadeface.sqlite"));
dbConn = new SQLiteConnection(dbPath);
SQLiteCommand command = dbConn.CreateCommand("select * from booklistitem where isbn = '" + ISBN + "'");
List<BookListItem> books = command.ExecuteQuery<BookListItem>();
if (books.Count == 1)
{
book = books.First();
//BookDetailGrid.DataContext = book;
}
}
else
{
MessageBox.Show("详细信息页面加载出错！");
NavigationService.Navigate(new Uri("/MainPage.xaml", UriKind.Relative));
}
bookService = BookService.getInstance();
showNoteList();
}
public static void Remove_StarredItem(StarredDataSQL starred)
{
using (var db = new SQLite.SQLiteConnection(SQLDBPath))
{
SQLiteCommand cmd = db.CreateCommand(string.Format("DELETE from StarredDataSQL WHERE StarredTitle = \"{0}\"", starred.StarredTitle));
List <StarredDataSQL> result = cmd.ExecuteQuery <StarredDataSQL>();
}
}
public static void RemoveAll_FavoriteItem()
{
using (var db = new SQLite.SQLiteConnection(SQLDBPath))
{
SQLiteCommand cmd = db.CreateCommand("DELETE from FavDataSQL");
List <FavDataSQL> result = cmd.ExecuteQuery <FavDataSQL>();
}
}
public static void Remove_FavoriteItem(FavDataSQL starred)
{
using (var db = new SQLite.SQLiteConnection(SQLDBPath))
{
SQLiteCommand cmd = db.CreateCommand(string.Format("DELETE from FavDataSQL WHERE FavID = {0}", starred.FavID));
List <FavDataSQL> result = cmd.ExecuteQuery <FavDataSQL>();
}
}
private void InsertSingleData(string st)
{
var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
using (var db = new SQLiteConnection(dbPath))
{
var command = db.CreateCommand(st);
command.ExecuteNonQuery();
}
}
internal static List <string> GetSubcategoriesOf(string category = null, bool inStorageArticlesOnly = false)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
// Artikel suchen, die schon abgelaufen sind.
string cmd = string.Empty;
cmd += "SELECT DISTINCT SubCategory AS Value";
cmd += " FROM Article";
cmd += " WHERE SubCategory IS NOT NULL";
cmd += " AND SubCategory <> ''";
if (category != null)
{
cmd += " AND Category = ?";
}
if (inStorageArticlesOnly)
{
cmd += " AND ArticleId IN (SELECT StorageItem.ArticleId FROM StorageItem)";
}
cmd += " ORDER BY SubCategory COLLATE NOCASE";
SQLiteCommand command;
if (category != null)
{
command = databaseConnection.CreateCommand(cmd, new object[] { category });
}
else
{
command = databaseConnection.CreateCommand(cmd, new object[] { });
}
List <StringResult> result = command.ExecuteQuery <StringResult>();
List <string> stringList = new List <string>();
foreach (StringResult item in result)
{
stringList.Add(item.Value);
}
return(stringList);
}
public static List <ItemOfCategory> GetDBItems(string sqlCommand)
{
using (var conn = new SQLite.SQLiteConnection(Settings.dbPath))
{
var cmd = conn.CreateCommand(sqlCommand);
var r = cmd.ExecuteQuery <ItemOfCategory>();
Console.Write(r);
return(r);
}
}
internal static List <string> GetCategoryAndSubCategoryNames()
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
// Artikel suchen, die schon abgelaufen sind.
string cmd = string.Empty;
cmd += "SELECT DISTINCT Category AS Value1, Subcategory AS Value2";
cmd += " FROM Article";
cmd += " WHERE Category IS NOT NULL";
cmd += " ORDER BY Category COLLATE NOCASE, Subcategory COLLATE NOCASE";
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
var command = databaseConnection.CreateCommand(cmd);
IList <StringPairResult> result = command.ExecuteQuery <StringPairResult>();
stopWatch.Stop();
Tools.TRACE("Dauer der Abfrage für DISTINCT Category, Subcategory: {0}", stopWatch.Elapsed.ToString());
string lastCategory = string.Empty;
List <string> stringList = new List <string>();
foreach (StringPairResult item in result)
{
string categoryName = item.Value1;
string subCategoryName = item.Value2;
if (string.IsNullOrEmpty(categoryName) && string.IsNullOrEmpty(subCategoryName))
{
continue;
}
if (categoryName != lastCategory)
{
stringList.Add(categoryName);
lastCategory = categoryName;
}
if (!string.IsNullOrEmpty(subCategoryName))
{
// Die Zeichenfülge " - " vor dem {0} ist wichtig
// für das Erkennen der Unterkategorie bei Auswahl.
stringList.Add(string.Format(" - {0}", subCategoryName));
}
}
return(stringList);
}
internal static bool IsArticleInShoppingList(int articleId)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return(false);
}
string cmd = "SELECT COUNT(*) FROM ShoppingList WHERE ArticleId = ?";
SQLiteCommand command = databaseConnection.CreateCommand(cmd, new object[] { articleId });
int count = command.ExecuteScalar <int>();
return(count > 0);
}
internal static double AddToShoppingList(int articleId, double addQuantity)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return(0);
}
SQLiteCommand command;
string cmd = string.Empty;
double isQuantity = (double)Database.GetShoppingListQuantiy(articleId);
double newQuantity = isQuantity + addQuantity;
bool isInList = Database.IsArticleInShoppingList(articleId);
if (!isInList)
{
cmd = "INSERT INTO ShoppingList (ArticleId, Quantity) VALUES (?, ?)";
command = databaseConnection.CreateCommand(cmd, new object[] { articleId, newQuantity });
}
else
{
if (newQuantity < 0)
{
newQuantity = 0;
}
cmd = "UPDATE ShoppingList SET Quantity = ? WHERE ArticleId = ?";
command = databaseConnection.CreateCommand(cmd, new object[] { newQuantity, articleId });
}
command.ExecuteNonQuery();
return(newQuantity);
}
internal static void RemoveFromShoppingList(int articleId)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return;
}
SQLiteCommand command;
string cmd = string.Empty;
cmd += "DELETE FROM ShoppingList WHERE ArticleId = ?";
command = databaseConnection.CreateCommand(cmd, new object[] { articleId });
command.ExecuteNonQuery();
}
public async static void insertData(string Name, string Surname, string Username, DateTime DateOfBirth, string Admin)
{
try
{
using (var connection = new SQLite.SQLiteConnection("GGzDB.db"))
{
var statement = connection.CreateCommand(@"INSERT INTO tbl_User (Name, Surname, Username, DateOfBirth, Admin) VALUES(?, ? , ? , ? , ?);", Name, Surname, Username, DateOfBirth, Admin);
statement.ExecuteNonQuery();
}
MessageDialog msgbox = new MessageDialog("Meld aan met uw gebruikersnaam en laat het wachtwoord invullen door de behandelaar.");
await msgbox.ShowAsync();
}
catch (Exception)
{
}
}
internal static void SetShoppingItemBought(int articleId, bool isChecked)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return;
}
string cmd = string.Empty;
SQLiteCommand command;
command = databaseConnection.CreateCommand(
"UPDATE ShoppingList SET Bought = ? WHERE ArticleId = ?",
new object[] { isChecked, articleId });
command.ExecuteNonQuery();
}
public async static void Init()
{
var LocalFolder = Windows.Storage.ApplicationData.Current.LocalFolder;
var Database = await LocalFolder.CreateFolderAsync("Database", CreationCollisionOption.OpenIfExists);
DbFile = Path.Combine(Database.Path, "iLocalMobileMedical.db");
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(DbFile))
{
SQLiteCommand cmd = conn.CreateCommand("");
string[] aryScripts = GetInitScript().Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string SqlInit in aryScripts)
{
cmd.CommandText = SqlInit;
cmd.ExecuteNonQuery();
}
}
}
internal static decimal GetArticleQuantityInStorage(int articleId)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
// Artikel suchen, die schon abgelaufen sind.
string cmd = string.Empty;
cmd += "SELECT SUM(Quantity) AS Quantity";
cmd += " FROM StorageItem";
cmd += " WHERE ArticleId = ?";
var command = databaseConnection.CreateCommand(cmd, new object[] { articleId });
IList <QuantityResult> result = command.ExecuteQuery <QuantityResult>();
decimal anzahl = result[0].Quantity;
return(anzahl);
}
internal static string GetArticleName(int articleId)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return(null);
}
string cmd = string.Empty;
cmd += "SELECT Name";
cmd += " FROM Article";
cmd += " WHERE ArticleId = ?";
var command = databaseConnection.CreateCommand(cmd, new object[] { articleId });
return(command.ExecuteScalar <string>());
}
internal static string GetSettingsString(string key)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return(null);
}
string cmd = string.Empty;
cmd += "SELECT Value";
cmd += " FROM Settings";
cmd += " WHERE Key = ?";
var command = databaseConnection.CreateCommand(cmd, new object[] { key });
return(command.ExecuteScalar <string>());
}
internal static Article GetArticle(int articleId)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return(null);
}
string cmd = string.Empty;
cmd += "SELECT *";
cmd += " FROM Article";
cmd += " WHERE ArticleId = ?";
var command = databaseConnection.CreateCommand(cmd, new object[] { articleId });
return(command.ExecuteQuery <Article>().FirstOrDefault());
}
internal static decimal GetArticleCount()
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return(0);
}
// Artikel suchen, die schon abgelaufen sind.
string cmd = string.Empty;
cmd += "SELECT COUNT(*) AS Quantity";
cmd += " FROM Article";
var command = databaseConnection.CreateCommand(cmd);
IList <QuantityResult> result = command.ExecuteQuery <QuantityResult>();
return(result[0].Quantity);
}
internal static decimal GetShoppingListQuantiy(int articleId, decimal notFoundDefault = 0)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return(0);
}
string cmd = "SELECT Quantity FROM ShoppingList WHERE ArticleId = ?";
SQLiteCommand command = databaseConnection.CreateCommand(cmd, new object[] { articleId });
IList <QuantityResult> result = command.ExecuteQuery <QuantityResult>();
if (result.Count == 0)
{
return(notFoundDefault);
}
return(result[0].Quantity);
}
internal static void SaveArticleImages(int articleId, byte[] imageLarge, byte[] image)
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return;
}
string cmd = string.Empty;
cmd += "UPDATE Article SET";
cmd += " ImageLarge = ?,";
cmd += " Image = ?";
cmd += " WHERE ArticleId = ?";
var command = databaseConnection.CreateCommand(cmd, new object[] { imageLarge, imageLarge, articleId });
command.ExecuteNonQuery();
}
internal static List <string> GetStorageNames(bool inStorageArticlesOnly = false)
{
List <string> stringList = new List <string>();
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return(stringList);
}
// Lagernamen aus dem Artikelstamm und den Positionen ermitteln.
string cmd = string.Empty;
cmd += "SELECT DISTINCT Article.StorageName AS Value";
cmd += " FROM Article";
cmd += " WHERE Article.StorageName IS NOT NULL AND Article.StorageName <> ''";
if (inStorageArticlesOnly)
{
cmd += " AND Article.ArticleId IN (SELECT StorageItem.ArticleId FROM StorageItem)";
}
cmd += " UNION";
cmd += " SELECT StorageName AS Value";
cmd += " FROM StorageItem";
cmd += " WHERE StorageName IS NOT NULL AND StorageName <> ''";
cmd += " ORDER BY 1 COLLATE NOCASE";
SQLiteCommand command = databaseConnection.CreateCommand(cmd, new object[] { });
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
IList <StringResult> result = command.ExecuteQuery <StringResult>();
stopWatch.Stop();
Tools.TRACE("Dauer der Abfrage für DISTINCT StorageName: {0}", stopWatch.Elapsed.ToString());
foreach (StringResult item in result)
{
stringList.Add(item.Value);
}
return(stringList);
}
internal static IList <StorageItemQuantityResult> GetBestBeforeItemQuantity(int articleId, string storageName = null)
{
IList <StorageItemQuantityResult> result = new List <StorageItemQuantityResult>();
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return(result);
}
string cmd = string.Empty;
string filter = string.Empty;
SQLiteCommand command;
IList <object> parameter = new List <object>();
filter = " WHERE StorageItem.ArticleId = ?";
parameter.Add(articleId);
if (!string.IsNullOrEmpty(storageName))
{
// Positionen direkt mit dem Lager oder Positionen ohne Lager, aber wenn der Artikel das Lager hat.
filter += " AND (StorageItem.StorageName = ?";
filter += " OR (IFNULL(StorageItem.StorageName, '') = '') AND StorageItem.ArticleId IN (SELECT ArticleId FROM Article WHERE Article.StorageName = ?))";
parameter.Add(storageName);
parameter.Add(storageName);
}
cmd += "SELECT BestBefore, SUM(Quantity) AS Quantity, Article.WarnInDays";
cmd += " FROM StorageItem";
cmd += " LEFT JOIN Article ON StorageItem.ArticleId = Article.ArticleId";
cmd += filter;
cmd += " GROUP BY BestBefore";
cmd += " ORDER BY BestBefore";
command = databaseConnection.CreateCommand(cmd, parameter.ToArray <object>());
return(command.ExecuteQuery <StorageItemQuantityResult>());
}
internal static IList <Article> GetArticlesByEanCode(string eanCode)
{
IList <Article> result = new List <Article>();
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return(result);
}
string cmd = string.Empty;
cmd = string.Empty;
cmd += "SELECT ArticleId";
cmd += " FROM Article";
cmd += " WHERE EANCode LIKE ?";
var command = databaseConnection.CreateCommand(cmd, new object[] { "%" + eanCode + "%" });
return(command.ExecuteQuery <Article>());
}
private bool TableExists(string tableName)
{
bool sw = false;
try
{
using (SQLite.SQLiteConnection connection = new SQLite.SQLiteConnection(RutaBD))
{
string query = string.Format("SELECT name FROM sqlite_master WHERE type='table' AND name='{0}';", tableName);
SQLiteCommand cmd = connection.CreateCommand(query);
var item = connection.Query <object>(query);
if (item.Count > 0)
{
sw = true;
}
return(sw);
}
}
catch (SQLiteException ex)
{
throw ex;
}
}
/// <summary>
/// Artikel suchen, für die eine Warnung ausgegeben werden soll.
/// </summary>
/// <returns></returns>
internal static decimal GetArticleCount_BaldZuVerbrauchen()
{
SQLite.SQLiteConnection databaseConnection = Android_Database.Instance.GetConnection();
if (databaseConnection == null)
{
return(0);
}
string cmd = string.Empty;
cmd = string.Empty;
cmd += "SELECT SUM(Quantity) AS Quantity";
cmd += " FROM StorageItem";
cmd += " JOIN Article ON StorageItem.ArticleId = Article.ArticleId";
cmd += " WHERE (date(BestBefore, (-WarnInDays || ' day')) <= date('now'))";
cmd += " AND BestBefore >= date('now')";
cmd += " AND WarnInDays <> 0";
//cmd += " OR 1 = 1";
var command = databaseConnection.CreateCommand(cmd);
var result = command.ExecuteQuery <QuantityResult>();
return(result[0].Quantity);
}
public string Save(MealSuggestionCategoryViewModel mealSuggestionCategory)
{
string result = string.Empty;
using (var db = new SQLite.SQLiteConnection(App.DBPath))
{
string change = string.Empty;
try
{
var existingMealSuggestion = (db.Table<MealSuggestionCategory>().Where(
c => c.Id == mealSuggestionCategory.Id)).SingleOrDefault();
if (existingMealSuggestion != null)
{
existingMealSuggestion.Name = mealSuggestionCategory.Name;
int success = db.Update(existingMealSuggestion);
}
else
{
int success = db.Insert(new MealSuggestionCategoryViewModel()
{
Name = mealSuggestionCategory.Name
});
SQLiteCommand cmd = db.CreateCommand("SELECT last_insert_rowid()");
int rowId = cmd.ExecuteScalar<int>();
cmd.CommandText = "SELECT Id FROM MealSuggestionCategory WHERE rowid = " + rowId.ToString();
mealSuggestionCategory.Id = cmd.ExecuteScalar<int>();
}
result = "Success";
}
catch
{
result = "This meal suggestion category was not saved.";
}
}
return result;
}
/// <summary>
/// Получаем адрес сервера синхронизации
/// </summary>
public string getSyncServer()
{
string changes;
var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
using (var db = new SQLiteConnection(dbPath))
{
var command = db.CreateCommand("SELECT ServerAddr FROM SyncData;");
changes = command.ExecuteScalar<string>();
}
return changes;
}
/// <summary>
/// Получаем время последней синхроинзации
/// </summary>
public DateTime getLastSync()
{
DateTime lsync;
var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
using (var db = new SQLiteConnection(dbPath))
{
var command = db.CreateCommand("SELECT LastSync FROM SyncData;");
lsync = command.ExecuteScalar<DateTime>();
}
return lsync;
}
/// <summary>
/// Изменение сервера синхронизации
/// </summary>
public void SetServer(string SrvAddr)
{
var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
using (var db = new SQLiteConnection(dbPath))
{
var command = db.CreateCommand("UPDATE SyncData SET ServerAddr ='" + SrvAddr + "';");
command.ExecuteNonQuery();
}
}
/*public async void CreateSyncFile(List<string> str)
{
var folder = ApplicationData.Current.LocalFolder;
var file = await folder.CreateFileAsync("sync.txt", CreationCollisionOption.ReplaceExisting);
await FileIO.WriteLinesAsync(file, str);
}
public async void ReadSyncFile()
{
var storageFolder = ApplicationData.Current.LocalFolder;
var file = await storageFolder.GetFileAsync("sync.txt");
var text = await FileIO.ReadLinesAsync(file);
InsertData(text);
}*/
/// <summary>
/// Изменение времени последней синхронизации
/// <summary>
public void SetLastSync()
{
var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
using (var db = new SQLiteConnection(dbPath))
{
var command = db.CreateCommand("UPDATE SyncData SET LastSync ='" + DateTime.Now + "';");
command.ExecuteNonQuery();
}
}
private ReadingPlan GetSeletcedPlan()
{
string planid;
ReadingPlan selectedplan = null;
if (NavigationContext.QueryString.TryGetValue("planID", out planid))
{
dbPath = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "jadeface.sqlite"));
dbConn = new SQLiteConnection(dbPath);
int Id = Int32.Parse(planid);
SQLiteCommand command = dbConn.CreateCommand("select * from readingplan where Id = " + Id);
List<ReadingPlan> plans = command.ExecuteQuery<ReadingPlan>();
if (plans.Count == 1)
{
selectedplan = plans.First();
}
}
else
{
MessageBox.Show("编辑计划出错！");
NavigationService.Navigate(new Uri("/MainPage.xaml", UriKind.Relative));
}
return selectedplan;
}
public string SaveContact(ContactViewModel contact)
{
string result = string.Empty;
using (var db = new SQLite.SQLiteConnection(App.DBPath))
{
string change = string.Empty;
try
{
var existingContact = (db.Table<Contact>().Where(
c => c.Id == contact.Id)).SingleOrDefault();
if (existingContact != null)
{
existingContact.Attention = contact.Attention;
existingContact.FirstName = contact.FirstName;
existingContact.LastName = contact.LastName;
existingContact.NameAndAddress = contact.NameAndAddress;
existingContact.Company = contact.Company;
existingContact.Street = contact.Street;
existingContact.Zip = contact.Zip;
existingContact.City = contact.City;
existingContact.PhoneNr = contact.PhoneNr;
existingContact.CellPhoneNr = contact.CellPhoneNr;
int success = db.Update(existingContact);
}
else
{
int success = db.Insert(new Contact()
{
Attention = contact.Attention,
FirstName = contact.FirstName,
LastName = contact.LastName,
NameAndAddress = contact.NameAndAddress,
Company = contact.Company,
Street = contact.Street,
Zip = contact.Zip,
City = contact.City,
PhoneNr = contact.PhoneNr,
CellPhoneNr = contact.CellPhoneNr
});
SQLiteCommand cmd = db.CreateCommand("SELECT last_insert_rowid()");
int rowId = cmd.ExecuteScalar<int>();
cmd.CommandText = "SELECT Id FROM Contact WHERE rowid = " + rowId.ToString();
contact.Id = cmd.ExecuteScalar<int>();
}
result = "Success";
}
catch
{
result = "This contact was not saved.";
}
}
return result;
}
// применяем к счету значение из параметра (меняется только значение суммы на счете, запоминаем экшн)
public string ChangeAccount(int _acc_id, double _sum, int _cat_id)
{
string res = "";
var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
using (var db = new SQLiteConnection(dbPath))
{
// Работа с БД
var command = db.CreateCommand("SELECT CurrentMoneyAmount from AccountTable WHERE _account_Id =" + _acc_id);
var current_sum = command.ExecuteScalar<double>();
var command1 = db.CreateCommand("Select _Name from AccountTable WHERE _account_Id =" + _acc_id);
var _name = command1.ExecuteScalar<string>();
var _acc = new AccountTable() { _account_Id = _acc_id, CurrentMoneyAmount = current_sum + _sum, _Name = _name };
db.Update(_acc);
var _act = new ActionTable() {AccountID = _acc_id, Money = _sum, CategoryID = _cat_id};
db.Insert(_act);
// Если до этого был просто быдлокод, то теперь будет быдлокод в квадрате!!!
var _change_acc = new ChangesTable() { _DBString = "UPDATE AccountTable SET _Name='" + _name + "', CurrentMoneyAmount='" + (current_sum + _sum) + "' WHERE _account_ID='" + _acc_id + "';", _ChangesDatetime = DateTime.Now };
db.Insert(_change_acc);
var _change_act = new ChangesTable() { _DBString = "INSERT INTO ActionTable(AccountID, Money, CategoryID) VALUES('" + _acc_id + "', '" + _sum + "', '" + _cat_id + "');", _ChangesDatetime = DateTime.Now };
db.Insert(_change_act);
}
return res;
}
public FootballPlayerCell()
{
connection = new SQLiteConnection (App.Path);
MenuItem favouriteAction = new MenuItem { Text = "Favourite" };
favouriteAction.SetBinding (MenuItem.CommandParameterProperty, new Binding("."));
favouriteAction.Clicked += async (sender, e) => {
await Task.Run (() => {
Player player;
var menuItem = ((MenuItem)sender);
player = (Player) menuItem.CommandParameter;
bool isFavourite = !(player.IsFavourite);
SQLiteCommand command = connection.CreateCommand("UPDATE FootballPlayer SET IsFavourite = ? where Name = ?", isFavourite, player.Name);
command.ExecuteNonQuery();
});
MessagingCenter.Send<FootballPlayerCell> (this, "Favourite");
};
ContextActions.Add (favouriteAction);
MenuItem deleteAction = new MenuItem { Text = "Delete", IsDestructive = true };
deleteAction.SetBinding (MenuItem.CommandParameterProperty, new Binding ("."));
deleteAction.Clicked += async (sender, e) => {
await Task.Run (() => {
Player player;
var menuItem = ((MenuItem)sender);
player = (Player) menuItem.CommandParameter;
SQLiteCommand command = connection.CreateCommand("DELETE from FootballPlayer where Name = ?", player.Name);
command.ExecuteNonQuery();
});
MessagingCenter.Send<FootballPlayerCell> (this, "Delete");
};
ContextActions.Add (deleteAction);
Image PlayerImage = new Image
{
WidthRequest = 50,
HeightRequest = 50
};
PlayerImage.SetBinding (Image.SourceProperty, "PlayerImage");
Image PlayerFlag = new Image
{
WidthRequest = 50,
HeightRequest = 30
};
PlayerFlag.SetBinding (Image.SourceProperty, "PlayerFlag");
Label Name = new Label
{
TextColor = Color.Black
};
Name.SetBinding (Label.TextProperty, "Name");
Label DateOfBirth = new Label
{
TextColor = Color.Black
};
DateOfBirth.SetBinding (Label.TextProperty, "DateOfBirth");
Label Age = new Label
{
TextColor = Color.Black,
};
Age.SetBinding (Label.TextProperty, "Age");
StackLayout descriptionLayout = new StackLayout
{
Orientation = StackOrientation.Horizontal,
Spacing = 10,
Children =
{
DateOfBirth,
Age
}
};
StackLayout detailsLayout = new StackLayout
{
Orientation = StackOrientation.Vertical,
Spacing = 20,
Children =
{
Name,
descriptionLayout
}
};
var cellLayout = new StackLayout
{
Spacing = 30,
Padding = new Thickness (10, 5, 10, 5),
Orientation = StackOrientation.Horizontal,
HorizontalOptions = LayoutOptions.FillAndExpand,
Children =
{
PlayerImage,
detailsLayout,
PlayerFlag
}
};
this.View = cellLayout;
}
private static void ProcessFolder(SQLiteConnection db, DriveInformation drive, List<string> arrHeaders, DirectoryInfo directory)
{
try {
if (!directory.Exists)
return;
if (IgnoreFolder(directory)) {
return;
}
//go get the cached items for the folder.
var directoryId = DatabaseLookups.GetDirectoryId(db, drive, directory);
var cmd = db.CreateCommand("Select * from " + typeof(FileInformation).Name + " Where DriveId = ? AND DirectoryId = ?", drive.DriveId, directoryId);
var databaseFiles = cmd.ExecuteQuery<FileInformation>();
//obtain the file metadata for all of the files in the directory so we can determine if we care about this folder.
var processList = GetFilesToProcess(databaseFiles, arrHeaders, directory);
if (processList.Count > 0) {
db.BeginTransaction();
Shell32.Shell shell = new Shell32.Shell();
Shell32.Folder folder = shell.NameSpace(directory.FullName);
foreach (var item in processList) {
try {
var fi = item.FileInfo;
var headerList = new List<FileAttributeInformation>();
for (int i = 0; i < arrHeaders.Count; i++) {
var header = arrHeaders[i];
if (!IgnoreHeader(header)) {
var value = folder.GetDetailsOf(item.FolderItem, i);
if (!string.IsNullOrWhiteSpace(value)) {
headerList.Add(new FileAttributeInformation() {
AttributeId = DatabaseLookups.GetAttributeId(db, header),
Value = value
});
}
}
}
//this should have been already checked but we want to be safe.
if (fi.Exists) {
var fileInfo = databaseFiles.FirstOrDefault(info => info.FileName.Equals(fi.Name, StringComparison.OrdinalIgnoreCase));
if (fileInfo == null) {
fileInfo = new FileInformation() {
DriveId = drive.DriveId,
DirectoryId = directoryId,
FileName = fi.Name
};
SetFileInformation(fi, fileInfo);
db.Insert(fileInfo);
Console.WriteLine("Inserted:" + fi.FullName);
}
else {
SetFileInformation(fi, fileInfo);
db.Update(fileInfo);
var deleteCount = db.Execute("Delete from " + typeof(FileAttributeInformation).Name + " WHERE FileId = ?", fileInfo.FileId);
Console.WriteLine("Changed:" + fi.FullName);
}
//save the headers
headerList.ForEach(hl => hl.FileId = fileInfo.FileId);
db.InsertAll(headerList);
}
}
catch (Exception ex) {
Console.WriteLine(ex.ToString());
}
}
db.Commit();
}
//see if we have any additional folders. If we get access denied it will throw an error
try {
foreach (var subDirectory in directory.GetDirectories()) {
ProcessFolder(db, drive, arrHeaders, subDirectory);
}
}
catch (Exception ex) {
Console.WriteLine(ex.ToString());
}
}
catch (UnauthorizedAccessException) {
}
catch (Exception ex) {
Console.WriteLine(ex.ToString());
}
}
public string DeleteMFU(int id)
{
string result = "1;1";
var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
using (var db = new SQLiteConnection(dbPath))
{
// Работа с БД
var command = db.CreateCommand("Select _Account_Id from MoneyFlowUnitTable WHERE _MFU_Id =" + id);
var _acc_id = command.ExecuteScalar<int>();
var command1 = db.CreateCommand("Select _Count from MoneyFlowUnitTable WHERE _MFU_Id =" + id);
var count = command1.ExecuteScalar<double>();
var command2 = db.CreateCommand("Select _Category from MoneyFlowUnitTable WHERE _MFU_Id =" + id);
var _cat_id = command2.ExecuteScalar<int>();
ChangeAccount(_acc_id, -count, _cat_id);
db.Delete<MoneyFlowUnitTable>(id);
var _change = new ChangesTable() { _DBString = "DELETE FROM MoneyFlowUnitTable WHERE _MFU_Id = '" + id + "';", _ChangesDatetime = DateTime.Now };
db.Insert(_change);
}
return result;
}
void getDatabase()
{
FootballPlayersList = new ObservableCollection<Player> ();
connection = new SQLiteConnection (App.Path);
SQLiteCommand command = connection.CreateCommand("SELECT * from FootballPlayer ORDER BY IsFavourite DESC , Name");
var footballPlayersList = command.ExecuteQuery<FootballPlayer> ();
foreach (FootballPlayer footballPlayer in footballPlayersList)
{
TimeSpan timeSpan = (DateTime.Now - DateTime.Parse(footballPlayer.DateOfBirth));
double years = timeSpan.Days / 365;
string playerFlag = null;
if (footballPlayer.Country == "India")
{
playerFlag = "India.png";
}
else if (footballPlayer.Country == "China")
{
playerFlag = "China.png";
}
else if (footballPlayer.Country == "Japan")
{
playerFlag = "Japan.png";
}
else if (footballPlayer.Country == "Korea")
{
playerFlag = "Korea.png";
}
Player player = new Player(footballPlayer.Name, footballPlayer.DateOfBirth, years.ToString(), footballPlayer.Country, playerFlag, footballPlayer.PlayerImage, footballPlayer.IsFavourite);
FootballPlayersList.Add (player);
}
}
public string SaveMeal(IMealViewModel mealSuggestion)
{
string result = string.Empty;
using (var db = new SQLite.SQLiteConnection(App.DBPath))
{
string change = string.Empty;
try
{
var existingMealSuggestion = (db.Table<MealSuggestion>().Where(
c => c.Id == mealSuggestion.Id)).SingleOrDefault();
if (existingMealSuggestion != null)
{
existingMealSuggestion.CategoryId = mealSuggestion.CategoryId;
existingMealSuggestion.MealItemIDsWithWeight =
(byte[])_dictionaryConverterFloat.Convert(mealSuggestion.MealItemIDsWithWeight, null, null, "");
int success = db.Update(existingMealSuggestion);
}
else
{
int success = db.Insert(new MealSuggestion()
{
CategoryId = mealSuggestion.CategoryId,
MealItemIDsWithWeight =
(byte[])_dictionaryConverterFloat.Convert(mealSuggestion.MealItemIDsWithWeight, null, null, "")
});
SQLiteCommand cmd = db.CreateCommand("SELECT last_insert_rowid()");
int rowId = cmd.ExecuteScalar<int>();
cmd.CommandText = "SELECT Id FROM MealSuggestion WHERE rowid = " + rowId.ToString();
mealSuggestion.Id = cmd.ExecuteScalar<int>();
}
result = "Success";
}
catch
{
result = "This meal suggestion was not saved.";
}
}
return result;
}
public string ChangeMFU(int _id, int AccId, double Summ, int CategoryID, string comment, DateTime date)
{
string res = "";
var dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, dbname);
using (var db = new SQLiteConnection(dbPath))
{
// Работа с БД
var command = db.CreateCommand("Select _Account_Id from MoneyFlowUnitTable WHERE _MFU_Id =" + _id);
var _acc_id = command.ExecuteScalar<int>();
var command1 = db.CreateCommand("Select _Count from MoneyFlowUnitTable WHERE _MFU_Id =" + _id);
var count = command1.ExecuteScalar<double>();
var command2 = db.CreateCommand("Select _Category from MoneyFlowUnitTable WHERE _MFU_Id =" + _id);
var _cat_id = command2.ExecuteScalar<int>();
ChangeAccount(_acc_id, -count, _cat_id);
var _mfu = new MoneyFlowUnitTable() {_MFU_Id=_id, _Date=DateTime.Now, Date = date, _Account_Id = AccId,
_Category = CategoryID, _Count = Summ, Comment = comment};
ChangeAccount(AccId, Summ, CategoryID);
db.Update(_mfu);
var _change = new ChangesTable() { _DBString = "UPDATE MoneyFlowUnitTable SET _Date='" + DateTime.Now + "', Date='" + date + "', _Account_Id='" + AccId
+ "', _Category='" + CategoryID + "', _Count='" + Summ + "', Comment='" + comment + "' WHERE _MFU_Id='" + _id + "';", _ChangesDatetime = DateTime.Now };
db.Insert(_change);
}
return res;
}
private SQLiteConnection CreateDatabase(string dbFilePath)
{
var connection = new SQLiteConnection(dbFilePath, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite);
using (Stream stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(DatabaseCreationScriptFilePath))
using (var reader = new StreamReader(stream, System.Text.Encoding.Default))
{
string[] sqls = reader.ReadToEnd().Split(';');
connection.RunInTransaction(() =>
{
foreach (var command in from sql in sqls where !string.IsNullOrEmpty(sql.Replace("\r\n", "")) select connection.CreateCommand(sql))
{
command.ExecuteNonQuery();
}
});
}
return connection;
}
public static int GetDirectoryId(SQLiteConnection db, DriveInformation drive, DirectoryInfo directory)
{
var directoryPath = directory.ToDirectoryPath();
directoryPath = (directoryPath ?? string.Empty).Trim();
var cmd = db.CreateCommand("Select * from " + typeof(DirectoryInformation).Name + " Where DriveId = ? AND Path = ?", drive.DriveId, directoryPath);
var retVal = cmd.ExecuteQuery<DirectoryInformation>().FirstOrDefault();
if (retVal != null) {
return retVal.DirectoryId;
}
int? parentDirectoryInfo = null;
if (directory.Parent != null) {
var parentName = directory.Parent.FullName.Substring(3);
parentDirectoryInfo = GetDirectoryId(db, drive, directory.Parent);
}
var directoryName = directory.Name;
if (directoryName.IndexOf(":") > -1) {
directoryName = directoryName.Substring(3);
}
//create a new record
var newDirectory = new DirectoryInformation() {
DriveId = drive.DriveId,
Name = directoryName,
ParentDirectoryId = parentDirectoryInfo.HasValue ? parentDirectoryInfo : null,
Path = directoryPath
};
db.Insert(newDirectory);
return newDirectory.DirectoryId;
}