public async Task <List <ShipLocationHistoryResult> > GetRawGeolocationsForShip(ShipToken ship)
{
var sw = System.Diagnostics.Stopwatch.StartNew();
var connection = new SQLite.Net.SQLiteConnection(
TinyIoC.TinyIoCContainer.Current.Resolve <ISQLitePlatform>(),
TinyIoC.TinyIoCContainer.Current.Resolve <IFolderProvider>().MapDatabasePath);
List <ShipLocationHistoryResult> mainShipLocationResults = new List <ShipLocationHistoryResult> ();
var query = connection.Table <shipLocationDate>().Where(r => r.shipID == ship.ID && r.shiplocationdatetype == "log"); //.OrderBy (r => r.startdate);
int locationIndex = 1;
foreach (var shipLocation in query)
{
var possibleLocation = connection.Table <locationJSON> ().Where(l => l.name == shipLocation.locationname).FirstOrDefault();
if (possibleLocation == null)
{
//We purposely skip some locations when processing. May want to re-think that!
continue;
}
DateTime startDate;
DateTime endDate;
var hasStartDate = string.IsNullOrEmpty(shipLocation.startdate) ? false : DateTime.TryParse(shipLocation.startdate, out startDate);
var hasEndDate = string.IsNullOrEmpty(shipLocation.enddate) ? false : DateTime.TryParse(shipLocation.enddate, out endDate);
mainShipLocationResults.Add(new ShipLocationHistoryResult()
{
Location = shipLocation.locationname,
PossibleEndDate = hasStartDate ? startDate : default(DateTime),
PossibleStartDate = hasEndDate ? endDate : default(DateTime),
ShipToken = ship,
LocationIndex = locationIndex++,
LocationGeocodeResult = possibleLocation != null ? JsonConvert.DeserializeObject <GeocodeResultMain> (possibleLocation.geocodeJSON) : null,
LocationGuid = shipLocation.locationguid
});
/*foreach (var location in locations) {
* List<GeocodeResultMain> results = new List<GeocodeResultMain> ();
* var query = connection.Table<locationJSON> ().Where (l => l.name == location);
* foreach (var locationJSONEntry in query) {
* mainGeocodeResults.Add(JsonConvert.DeserializeObject<GeocodeResultMain>(locationJSONEntry.geocodeJSON));
* }*/
}
sw.Stop();
System.Diagnostics.Debug.WriteLine($"Total geolocation query time: {sw.ElapsedMilliseconds} ms");
return(mainShipLocationResults);
}
private void Retrieve_Click(object sender, RoutedEventArgs e)
{
var query = conn.Table <Logindata>();
var dummy = 0;
textBlock3.Text = "";
foreach (var message in query)
{
if (textBox.Text == message.Username)
{
dummy = 1;
if (textBox1.Password == message.Password)
{
textBlock3.Text = "User found! Login Successful";
var parameters = new class1();
parameters.username = textBox.Text;
this.Frame.Navigate(typeof(BlankPage1), parameters);
}
else
{
textBlock3.Text = "Password is incorrect!";
}
break;
}
}
if (dummy == 0)
{
textBlock3.Text = "User not found! Register first";
}
}
public void loadListView()
{
int categoryCounter = 0, itemCounter = 0;
var queryCategory = conn.Table <Category>();
foreach (var category in queryCategory)
{
categoryCounter++;
ListViewItem categoryListView = new ListViewItem();
categoryListView.Name = category.Name;
categoryListView.Content = categoryCounter + " " + category.Name;
categoryListView.Margin = new Thickness(40, 5, 40, 0);
categoryListView.Background = new SolidColorBrush(Windows.UI.Colors.LightGray);
ItemListView.Items.Add(categoryListView);
var queryItem = (from I in conn.Table <Item>()
where I.CategoryName == category.Name
select I).ToList();
itemCounter = 0;
foreach (var item in queryItem)
{
itemCounter++;
ListViewItem itemListView = new ListViewItem();
itemListView.Name = item.Name;
itemListView.Content = categoryCounter + "." + itemCounter + " " + item.Name;
itemListView.Margin = new Thickness(60, 5, 60, 0);
itemListView.Background = new SolidColorBrush(Windows.UI.Colors.WhiteSmoke);
ItemListView.Items.Add(itemListView);
}
}
}
public void loadListView()
{
/*
* This function loads ItemListView.
* Each category and item will be added as a item of ItemListView.
* If any item is already available it will be inserted in AvailableItem.
* Submit button will update AvailableItem list item.
*/
AvailableItem = new List <string>();
int categoryCounter = 0, itemCounter = 0;
var queryCategory = conn.Table <Category>();
foreach (var category in queryCategory)
{
categoryCounter++;
// Prepare category as ItemListView item
ListViewItem categoryListView = new ListViewItem();
categoryListView.Name = category.Name;
categoryListView.Content = categoryCounter + " " + category.Name;
categoryListView.Margin = new Thickness(0, 5, 0, 0);
ItemListView.Items.Add(categoryListView);
var queryItem = (from I in conn.Table <Item>()
where I.CategoryName == category.Name
select I).ToList();
// Prepare items as ItemListView item
itemCounter = 0;
foreach (var item in queryItem)
{
itemCounter++;
TextBlock textblock = new TextBlock();
textblock.Text = categoryCounter + "." + itemCounter + " " + item.Name;
textblock.Margin = new Thickness(0, 5, 0, 0);
CheckBox checkbox = new CheckBox();
checkbox.Name = category.Id + "_" + item.Id;
checkbox.IsChecked = item.Available == 1 ? true : false;
checkbox.Margin = new Thickness(10, 0, 0, 0);
checkbox.Checked += Checkbox_Checked;
checkbox.Unchecked += Checkbox_Unchecked;
StackPanel stackpanel = new StackPanel();
stackpanel.Orientation = Orientation.Horizontal;
stackpanel.Margin = new Thickness(20, 5, 20, 0);
stackpanel.Children.Add(textblock);
stackpanel.Children.Add(checkbox);
ItemListView.Items.Add(stackpanel);
if (item.Available == 1)
{
AvailableItem.Add(category.Id + "_" + item.Id);
}
}
}
}
protected override void OnNavigatedTo(NavigationEventArgs e)
{
muctieu = (MucTieu)e.Parameter;
nguoidung = connection.Table<NguoiDung>().Where(r => r.TenDangNhap == muctieu.TenDangNhap).FirstOrDefault();
int tuoi = DateTime.Today.Year - DateTime.ParseExact(nguoidung.NgaySinh, "dd/MM/yyyy", new CultureInfo("vi-vn")).Year;
chiso = new ChiSo(muctieu.CanNangBanDau, muctieu.ChieuCaoBanDau, nguoidung.GioiTinh, tuoi);
// so ngay giam de nghi cho mac dinh = 30;
int socangiamdenghi = chiso.tinhSoCanGiamDeNghi(); // cannang - cannanglytuong
// muc 1
DeNghi muc1 = new DeNghi();
muc1.SoCanGiamDeNghi = socangiamdenghi;
muc1.SoNgayGiamDeNghi = Convert.ToInt32(Math.Round(muc1.SoCanGiamDeNghi / 0.05));
muc1Button.Content = string.Format("Giảm {0} kg trong vòng {1} ngày", muc1.SoCanGiamDeNghi, muc1.SoNgayGiamDeNghi);
muc1Button.Tag = muc1;
// muc 2
DeNghi muc2 = new DeNghi();
muc2.SoCanGiamDeNghi = socangiamdenghi;
muc2.SoNgayGiamDeNghi = Convert.ToInt32(Math.Round(muc2.SoCanGiamDeNghi / 0.1));
muc2Button.Content = string.Format("Giảm {0} kg trong vòng {1} ngày", muc2.SoCanGiamDeNghi, muc2.SoNgayGiamDeNghi);
muc2Button.Tag = muc2;
// muc3
DeNghi muc3 = new DeNghi();
muc3.SoCanGiamDeNghi = socangiamdenghi;
muc3.SoNgayGiamDeNghi = Convert.ToInt32(Math.Round(muc3.SoCanGiamDeNghi / 0.15));
muc3Button.Content = string.Format("Giảm {0} kg trong vòng {1} ngày", muc3.SoCanGiamDeNghi, muc3.SoNgayGiamDeNghi);
muc3Button.Tag = muc3;
}
private void LoadDetails()
{
Order order = (from O in conn.Table <Order>()
where O.Id == orderId
select O).First <Order>();
OrderNoTextBlock.Text = order.OrderNo.ToString();
OrderDateTextBlock.Text = "Order date: " + order.OrderDate;
OrderTimeTextBlock.Text = "Order Time: " + order.OrderTime;
OrderByTextBlock.Text = "Order By: " + (from user in conn.Table <User>()
where user.Id == order.UserId
select user).First <User>().UserName;
TotalCostTextBlock.Text = "Total Cost: " + order.TotalAmount;
ProcessTimeTextBlock.Text = "Processing Time: " + order.ProcessTime;
AcknowledgeCheckBox.IsChecked = order.Acknowledged == 1 ? true : false;
DeliveryCheckBox.IsChecked = order.Deliveried == 1 ? true : false;
CancelCheckBox.IsChecked = order.Canceled == 1 ? true : false;
var orderItemList = (from orderItem in conn.Table <OrderItem>()
where orderItem.OrderId == order.Id
select orderItem).ToList <OrderItem>();
int counter = 1;
foreach (var orderItem in orderItemList)
{
string itemName = (from item in conn.Table <Item>()
where item.Id == orderItem.ItemId
select item).First <Item>().Name;
TextBlock textblock = new TextBlock();
textblock.Foreground = new SolidColorBrush(Windows.UI.Colors.DarkGreen);
textblock.Text = counter++ + ". " + itemName + " " + orderItem.Price + "tk. Quantity: " + orderItem.Quantity + " Total: " + orderItem.Total;
OrderItemsListView.Items.Add(textblock);
}
}
public CardViewModel()
{
try
{
currentModel = new CardCurrent();
firstStart = true;
previousCardModified = false;
goingBack = false;
goingBackNumberOfCards = 1;
imageNumber = 0;
id = 0;
//There was a problem with database initialization
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
{
verbs = db.Table<Verbs>().ToList();
adjectives = db.Table<Adjectives>().ToList();
nouns = db.Table<Nouns>().ToList();
images = db.Table<Images>().ToList();
if (verbs.Count == 0 || adjectives.Count == 0 || nouns.Count == 0 || images.Count == 0)
{
Debug.WriteLine("Database not initialized");
App app = new App();
app.Initialize();
}
}
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
Debug.WriteLine(e.StackTrace);
}
}
private async void radioButton_Checked(object sender, RoutedEventArgs e)
{
int rating = 1;
if (radioButton.IsChecked == true)
{
rating = 1;
}
var query1 = conn.Table <TransactionsTable>().Where(t => t.ID == k1);
int k = 0;
string st = "";
string st2 = "";
foreach (var item1 in query1)
{
k = item1.ID;
st = item1.DriverId;
st2 = item1.CabID;
}
conn.Execute("UPDATE TransactionsTable SET DriverRating = ? Where ID = ?", rating, k);
conn.Execute("UPDATE DriverTable SET DRating = ? Where DUserName = ?", 1, st);
conn.Execute("UPDATE DriverTable SET DAvailabilityFlag = ? Where DUserName = ?", "Aa", st);
conn.Execute("UPDATE CarTable SET Availability = ? Where RegistrationNumber = ?", "A", st2);
var message = new MessageDialog("Driver Rated!");
await message.ShowAsync();
this.Frame.Navigate(typeof(MainPage1), k);
}
private void listBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
var driver = listBox.SelectedItem;
selectedQueTxtBx.Text = driver.ToString();
DriverName = selectedQueTxtBx.Text;
var query1 = conn.Table <TransactionsTable>().Where(t => t.UserName == usr);
int k = 0;
foreach (var item1 in query1)
{
k = item1.ID;
}
// var query3 = conn.Table<CarTable>().Where(t => t.Availability == "A" && t.RegistrationNumber == j);
var query2 = conn.Table <DriverTable>().Where(t => t.DUserName == DriverName);
string j = "";
foreach (var item4 in query2)
{
j = item4.DFirstName;
}
// Debug.WriteLine("tRANSACTIONStABLEcOUNT = " + k);
conn.Execute("UPDATE TransactionsTable SET DriverID = ? Where ID = ?", DriverName.ToString(), k);
conn.Execute("UPDATE TransactionsTable SET DriverName = ? Where ID = ?", j, k);
// conn.Execute("UPDATE TransactionsTable SET CabID = ? Where ID = ?", j, k);
//edit2
conn.Execute("UPDATE DriverTable SET DAvailabilityFlag= ? Where DUserName = ?", "Bb", DriverName.ToString());
this.Frame.Navigate(typeof(PaymentPage), k);
}
public BookDriver()
{
this.InitializeComponent();
db = new Database();
path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "MyDataBase.sqlite");
conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);
//edit1
var que = conn.Table <DriverTable>().
Where(t => (t.DRating < 5 && t.DAvailabilityFlag == "Aa"));
var query2 = conn.Table <DriverTable>().
Where(t => (t.DAvailabilityFlag == "Aa" && t.DRating == 5));
foreach (var item2 in query2)
{
listBox.Items.Add(item2.DUserName);
// listBox.Items.Add(new SolidColorBrush(Color.FromArgb(255, 48, 179, 221)));
}
foreach (var ite in que)
{
//edit1
/* var query = conn.Table<DriverTable>().
* Where(t => (t.DAvailabilityFlag == "Aa" && t.DRating == ite.DRating)); //t.DAvailabilityFlag == "Aa" &&
*
* foreach (var item in query)
* {*/
listBox.Items.Add(ite.DUserName);
//}
}
}
public List <Party> PartyList()
{
List <Party> partyList = new List <Party>();
foreach (var party in conn.Table <Party>())
{
partyList.Add(party);
}
return(partyList);
}
public List <PartySubtype> PartySubtypeList()
{
List <PartySubtype> accountList = new List <PartySubtype>();
foreach (var subtype in conn.Table <PartySubtype>())
{
accountList.Add(subtype);
}
return(accountList);
}
public List <Account> AccountList()
{
List <Account> accountList = new List <Account>();
foreach (var account in conn.Table <Account>())
{
accountList.Add(account);
}
return(accountList);
}
private void CreateInternalOpeningJournalType()
{
bool already = (from sub in conn.Table <JournalType>()
where sub.prefix == "IOJ"
select sub
).Any <JournalType>();
if (!already)
{
Add("Internal Opening Journal", "IOJ", "1000", "-1", "-1");
}
}
private void Sign_Click(object sender, RoutedEventArgs e)
{
stack.Visibility = Visibility.Visible;
signup1.IsPaneOpen = false;
var query = conn.Table <info>(); string id = ""; string name = ""; string email = ""; String password = "";
foreach (var message in query)
{
id = id + " " + message.Id; name = name + " " + message.Name; email = email + " " + message.Email; password = password + " " + message.Password;
}
// text1.Text = "ID: " + id + "\nName: " + name + "\nemail: " + email+"\npassword:"+password;
}
private void EditCategoryButton_Click(object sender, RoutedEventArgs e)
{
string categoryName = CategoryComboBox.SelectedValue.ToString();
string newCategory = EditCategoryTextBox.Text;
if (newCategory == string.Empty)
{
WarningTextBlock.Text = "Please provide a category name.";
WarningTextBlock.Foreground = new SolidColorBrush(Windows.UI.Colors.Red);
}
else
{
WarningTextBlock.Text = "";
// Cahecking duplicate value.
bool same = false;
var queryCategory = conn.Table <Category>();
foreach (var category in queryCategory)
{
if (category.Name == newCategory && category.Name != categoryName)
{
same = true;
break;
}
}
// If duplicate value prompt user else enter in database.
if (!same)
{
var cat = (from c in conn.Table <Category>()
where c.Name == categoryName
select c).ToList <Category>();
var add = conn.Update(new Category()
{
Id = cat[0].Id,
Name = newCategory,
Deleted = 0
});
Debug.WriteLine(path);
EditCategoryTextBox.Text = string.Empty;
WarningTextBlock.Text = "Category edited successfully.";
WarningTextBlock.Foreground = new SolidColorBrush(Windows.UI.Colors.Green);
}
else
{
WarningTextBlock.Text = "This category name is already in used.";
WarningTextBlock.Foreground = new SolidColorBrush(Windows.UI.Colors.Red);
}
}
}
public void Show_Contact(object sender, RoutedEventArgs e)
{
var querry2 = connect.Table <Contact>();
string Name = "";
string PhoneNumber = "";
foreach (var q in querry2)
{
Name = Name + " " + q.name;
PhoneNumber = PhoneNumber + " " + q.phonenumber;
}
Result.Text = "Name: " + Name + "\nPhoneNumber: " + PhoneNumber;
}
protected override void OnNavigatedTo(NavigationEventArgs e)
{
base.OnNavigatedTo(e);
var parameters = (class1)e.Parameter;
textBlock1.Text = "Hello " + parameters.username + ",";
us = parameters.username;
var query = conn.Table <Userdata>();
// parameters.Name
// parameters.Text
// ...
}
protected override void OnNavigatedTo(NavigationEventArgs e)
{
nguoidung = e.Parameter as NguoiDung;
// kiem tra ngay hien tai da co trong db chua, chua co thi them vao
muctieu = connection.Table <MucTieu>().Where(r => r.TenDangNhap == nguoidung.TenDangNhap && (r.TrangThai == "Đã bắt đầu" || r.TrangThai == "Chưa bắt đầu")).FirstOrDefault();
string today = DateTime.Today.ToString("dd/MM/yyyy");
thongkengay = (muctieu != null) ? connection.Table <ThongKeNgay>().Where(r => r.IdMucTieu == muctieu.IdMucTieu && r.Ngay == today).FirstOrDefault() : null;
tendangnhapTextBlock.Text = nguoidung.TenDangNhap;
tuoiTextBlock.Text = (DateTime.Today.Year - DateTime.ParseExact(nguoidung.NgaySinh, "dd/MM/yyyy", new CultureInfo("vi-vn")).Year).ToString();
gioitinhTextBlock.Text = nguoidung.GioiTinh;
}
private void GetData_Click(object sender, RoutedEventArgs e)
{
var query = conn.Table <Customer>();
string id = "";
string name = "";
string phone = "";
foreach (var message in query)
{
id = id + " " + message.Id;
name = name + " " + message.Name;
phone = phone + " " + message.PhoneNumber;
}
textBlock2.Text = "Id" + id + "\nName" + name + "\nPhoneNumber" + phone;
}
private void EditPartySubtypeButton_Click(object sender, RoutedEventArgs e)
{
try
{
int id = (Int32)((Button)sender).Tag;
var accountSubtype = (from sub in conn.Table <PartySubtype>()
where sub.id == id
select sub
).First <PartySubtype>();
}
catch (Exception ex)
{
Debug.WriteLine("Processor Usage" + ex.Message);
}
}
protected void Save()
{
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(m_Initialize.Platform, m_Initialize.DBPath))
{
db.CreateTable <TTable>();
if (0 == db.Table <TTable>().Count())
{
Load();
}
var table = db.Table <TTable>().First();
Save(table);
db.Update(table);
}
}
public MainPage()
{
this.InitializeComponent();
//this.ViewModel = new CompanyCodeViewModel();
string SQLPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
SQLite.Net.SQLiteConnection SQLConnection = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), SQLPath);
/*
* string[] longArray = longText.Split(',');
* SQLConnection.DropTable<CompanyCode>();
* SQLConnection.CreateTable<CompanyCode>();
* string code = "blank";
* string oldCode = code;
* string description = "BLANK";
* string oldDescription = description;
* for (int i = 0; i < longArray.Length; i+=2)
* {
* code = longArray[i];
* description = longArray[i+1];
* if (description != oldDescription)
* {
* CompanyCode newBusinessCode = new CompanyCode(code, description);
* var row = SQLConnection.Insert(newBusinessCode);
* }
* oldDescription = description;
* }
*/
this.ViewModel = new CompanyCodeViewModel(SQLConnection.Table <CompanyCode>());
ViewModel.LimitDisplayNAICSCode("");
}
public void populateListView()
{
var itemsToAdd = connection.Table <AddTrip>();
//string name;
//string dest;
//string start;
//AddTrip addTrp = new AddTrip();
foreach (var addTrip in itemsToAdd)
{
StoredTrips newTrip = new StoredTrips();
newTrip.startingPoint = addTrip.startingPoint;
newTrip.destination = addTrip.destination;
newTrip.tripName = addTrip.tripName;
storedTrips.Add(newTrip);
ListViewItem newButton = new ListViewItem();
trips.Add(newButton);
newButton.Content = addTrip.tripName;
listView.Items.Add(newButton);
}
}
//-------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------
public pndashboard()
{
this.InitializeComponent();
path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path,
"db.sqlite");
conn = new SQLite.Net.SQLiteConnection(new
SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);
var query = conn.Table <pnTable>();
int id = 0;
string name = "";
foreach (var message in query)
{
id = message.Id;
name = message.Name;
}
pnNamed.Text = name;
localName.Text = name;
// conn.DropTable<pnTable>();
}
protected override void OnNavigatedTo(NavigationEventArgs e)
{
ScenarioEnableButton.IsEnabled = true;
ScenarioDisableButton.IsEnabled = false;
ScenarioPauseButton.IsEnabled = false;
nguoidung = TrangChu.nguoidung;
muctieu = TrangChu.muctieu;
if (muctieu != null)
{
thongkengay = TrangChu.getThongKeNgayHienTai(muctieu);
chaybo = connection.Table <ThongKeBaiTap>().Where(r => r.IdThongKeNgay == thongkengay.IdThongKeNgay && r.IdBaiTap == 1).FirstOrDefault();
if (chaybo == null)
{
chaybo = new ThongKeBaiTap()
{
IdBaiTap = 1,
IdThongKeNgay = thongkengay.IdThongKeNgay,
QuangDuong = 0,
SoBuoc = 0,
LuongKaloTieuHao = 0,
ThoiGianTap = 0
};
connection.Insert(chaybo);
}
}
// mục tiêu == null || thống kê ngày == null -> tập nhưng không đưa vào database
else
{
chaybo = new ThongKeBaiTap();
}
}
private async void ViewAccountButton_Click(object sender, RoutedEventArgs e)
{
try
{
int id = (Int32)((Button)sender).Tag;
var account = (from acc in conn.Table <Account>()
where acc.id == id
select acc
).First <Account>();
string message = "Name : " + account.name + "\nCode : " + account.code +
"\nType : " + Enum.GetName(typeof(AccountType), account.type) +
"\nSubtype : " + viewModel.GetSubtype(account.subtype).name
;
if (account.parent_id != -1)
{
message += "\nParent : " + viewModel.Get(account.parent_id).name;
}
message += "\nOpening Balance : " + account.opening_balance.ToString() +
"\nOpening Date : " + account.entry_date.ToString() +
"\nNote : " + account.note;
var dialog = new Windows.UI.Popups.MessageDialog(message, account.name);
var result = await dialog.ShowAsync();
}
catch (Exception ex)
{
Debug.WriteLine("Processor Usage" + ex.Message);
}
}
public static List <Category> getCategory()
{
string path;
SQLite.Net.SQLiteConnection conn;
path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);
conn.CreateTable <Category>();
int i = 1;
List <Category> categoryList = new List <Category>();
var queryCategory = conn.Table <Category>();
foreach (var category in queryCategory)
{
categoryList.Add(new Category()
{
Id = i,
Name = category.Name
});
i++;
}
return(categoryList);
}
public static List <User> getUsers()
{
string path;
SQLite.Net.SQLiteConnection conn;
path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);
conn.CreateTable <User>();
int i = 1;
List <User> userList = new List <User>();
var queryUser = conn.Table <User>();
foreach (var user in queryUser)
{
userList.Add(new User()
{
Id = i,
FirstName = user.FirstName,
LastName = user.LastName,
FullName = user.FullName,
UserName = user.UserName,
Password = user.Password,
Email = user.Email,
ContactNo = user.ContactNo,
Type = user.Type
});
i++;
}
return(userList);
}
private async void ViewJournalButton_Click(object sender, RoutedEventArgs e)
{
try
{
int id = (Int32)((Button)sender).Tag;
var journal = (from jour in conn.Table <Journal>()
where jour.id == id
select jour
).First <Journal>();
string message = "Number : " + journal.number +
"\nType : " + viewModel.GetType(journal.type).name + "\nAmount : " + journal.amount.ToString();
if (journal.party_id != -1)
{
message += "\nParty : " + viewModel.GetParty(journal.party_id).name;
}
if (journal.ref_journal_id != -1)
{
message += "\nRef Journal : " + viewModel.Get(journal.ref_journal_id).number;
}
message += "\nDescription : " + journal.description +
"\nAccounts Information :\n" + viewModel.GetAccountsName(journal.account_info);
var dialog = new Windows.UI.Popups.MessageDialog(message, journal.number);
var result = await dialog.ShowAsync();
}
catch (Exception ex)
{
Debug.WriteLine("Processor Usage" + ex.Message);
}
}
/// <summary>
/// Initializes The GUI by adding all history from the SQLite db
/// </summary>
private void InitializeGUI()
{
var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "GameHistoryDB.sqlite");
SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath);
var games = from g in conn.Table<GameHistory>()
select g;
ListGameHistory.ItemsSource = games.ToList();
}
/// <summary>
/// Deletes all cards in a pack
/// </summary>
/// <param name="pack">Pack which you want to remove</param>
public void DeleteSavedPack(int pack)
{
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
{
IEnumerable<SavedCards> packQuery = db.Table<SavedCards>().Where(card => card.PackNumber == pack);
foreach (SavedCards savedCard in packQuery)
{
db.Delete(savedCard);
}
}
}
/// <summary>
/// Deletes a individual card in a pack
/// </summary>
/// <param name="pack">A packnumber where you want the card to be removed</param>
/// <param name="card">Cardnumber the be removed</param>
public void DeleteSavedCard(int pack, int card)
{
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
{
IEnumerable<SavedCards> entityQuery = db.Table<SavedCards>().Where(x => x.PackNumber == pack && x.CardNumber == card); // from savedCards in context.SavedCardPacks where savedCards.packNumber == pack && savedCards.packCard == card select savedCards;
foreach (SavedCards savedCard in entityQuery)
{
db.Delete(savedCard);
}
}
}
public SQLite.Net.SQLiteConnection GetConnection()
{
// Create the connection
var plat = new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS ();
var conn = new SQLite.Net.SQLiteConnection (plat, FilePath);
// Return the database connection
var d = conn.GetTableInfo ("Person");
var m = conn.Table<Person> ().Table;
return conn;
}
public static Day getByDayID(int dayID)
{
var path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
Day day = new Day();
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path))
{
day = (from d in conn.Table<Day>()
where d.DayID == dayID
select d
).ToList().FirstOrDefault();
}
return day;
}
public static User getByUserID(int userID)
{
var path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
User user = new User();
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path))
{
user = (from u in conn.Table<User>()
where u.UserID == userID
select u
).ToList().FirstOrDefault();
}
return user;
}
public static List<Eat> getAll()
{
List<Eat> lm = new List<Eat> { };
var path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path))
{
lm = (from m in conn.Table<Eat>()
select m).ToList();
}
return lm;
}
/// <summary>
/// Reorders saved cards pivot page cards after delete.
/// </summary>
/// <param name="pack">The pack number where a card was removed</param>
/// <param name="cardPosition">Position number of the card where card was removed</param>
public void ReOrderSavedCards(int pack, int cardPosition)
{
int newCardPosition = cardPosition;
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
{
IEnumerable<SavedCards> reOrderQuery = db.Table<SavedCards>()
.Where(card => card.PackNumber == pack && card.CardNumber > cardPosition);
foreach (SavedCards savedCard in reOrderQuery)
{
savedCard.CardNumber = newCardPosition;
db.Update(savedCard);
newCardPosition++;
}
}
}
public static Day getDatByDate(DateTimeOffset dt)
{
Day d;
var path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path))
{
d = (from m in conn.Table<Day>()
let convertedDate = (DateTime)m.Date
where (m.Date.Day == dt.Date.Day) &&
(m.Date.Month == dt.Date.Month) &&
(m.Date.Year == dt.Date.Year)
select m).ToList().FirstOrDefault();
}
return d;
}
/// <summary>
/// Check if card is null
/// If it is null, then return true
/// </summary>
/// <param name="pack">The pack to be check</param>
/// <param name="card">The card to be check</param>
/// <returns>true if card in the pack is null</returns>
/// <returns>false if card in the pack is not null </returns>
public bool CheckIfCardIsNull(int pack, int card)
{
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLitePlatformWinRT(), App.path))
{
try
{
int size = db.Table<SavedCards>().Where(packs => packs.PackNumber == pack).Count();
if (size > card)
{
return false;
}
else
{
return true;
}
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
return true;
}
}
}
public void RandomKuva_ManipulationCompleted()
{
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
{
Random rnd = new Random();
currentModel.Image = rnd.Next(db.Table<Images>().Count());
}
if (goingBackNumberOfCards < 0)
{
previousCardModified = true;
}
if (goingBackNumberOfCards == 0)
{
previousCardModified = true;
}
}
/// <summary>
/// Used to save a card, when user is browsing generated cards.
/// </summary>
/// <param name="overWrite">If we are overwriting existing genererated card then overWrite should be true, else false</param>
/// <param name="index">Generated card index which we are overwriting</param>
public void SaveGeneratedCard(bool overWrite, int index)
{
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
{
if (overWrite == false)
{
GeneratedCards generatedCards = new GeneratedCards() { VerbWord = currentModel.Verb, AdjectiveWord = currentModel.Adjective, NounWord = currentModel.Noun, Image = currentModel.Image };
try
{
db.Insert(generatedCards);
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
}
}
else
{
try
{
GeneratedCards generatedCard = db.Table<GeneratedCards>().Skip(index).First();
generatedCard.Image = currentModel.Image;
generatedCard.VerbWord = currentModel.Verb;
generatedCard.AdjectiveWord = currentModel.Adjective;
generatedCard.NounWord = currentModel.Noun;
db.Update(generatedCard);
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
}
}
}
}
/// <summary>
/// Gets random image
/// </summary>
public void GetImage()
{
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
{
Random random = new Random();
currentModel.Image = random.Next(db.Table<Images>().Count() + 1);
}
previousCardModified = false;
}
public static List<User> getAll()
{
var path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
List<User> users = new List<User> { };
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path))
{
users = (from u in conn.Table<User>()
select u).ToList();
}
return users;
}
public async void GetNewCard()
{
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
{
int i = 0;
Random rnd = new Random();
try
{
goingBack = false;
if (goingBackNumberOfCards <= 0)
{
SaveGeneratedCard(false, 0);
previousCardModified = false;
}
//If previous card was an old card and it was modified
if (goingBackNumberOfCards == 0 && previousCardModified == true)
{
SaveGeneratedCard(true, back);
previousCardModified = false;
}
if (goingBackNumberOfCards == 1)
{
goingBackNumberOfCards = 0;
}
id += 1;
imageNumber = 0;
var card = new CardCurrent();
//Example values:
//card.Verb = "help";
//card.Adjective = "worried";
//card.Noun = "girls";
//card.Image = 143;
i = rnd.Next(db.Table<Verbs>().Count());
card.Verb = db.Table<Verbs>().Skip(i).First().VerbWord;
i = rnd.Next(db.Table<Adjectives>().Count());
card.Adjective = db.Table<Adjectives>().Skip(i).First().AdjectiveWord;
i = rnd.Next(db.Table<Nouns>().Count());
card.Noun = db.Table<Nouns>().Skip(i).First().NounWord;
i = rnd.Next(1, db.Table<Images>().Count());
card.Image = db.Table<Images>().Skip(i).First().ImageNumber;
previousCardModified = false;
currentModel = card;
firstStart = false;
}
catch (Exception error)
{
Debug.WriteLine(db.Table<Verbs>().Count());
Debug.WriteLine(db.Table<Adjectives>().Count());
Debug.WriteLine(db.Table<Nouns>().Count());
Debug.WriteLine(db.Table<Images>().Count());
var dialog = new MessageDialog("Error on " + error.Message + " Stacktrace " + error.StackTrace);
await dialog.ShowAsync();
Debug.WriteLine(error.Message + "||" + error.StackTrace);
}
}
}
public void GetOldCard()
{
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
{
int count = db.Table<GeneratedCards>().Count() - 1;
if (goingBackNumberOfCards <= -1 && previousCardModified == true)
{
SaveGeneratedCard(true, back);
previousCardModified = false;
}
goingBackNumberOfCards++;
back = count + goingBackNumberOfCards;
var card = new CardCurrent();
card.Verb = db.Table<GeneratedCards>().Skip(back).First().VerbWord;
card.Adjective = db.Table<GeneratedCards>().Skip(back).First().AdjectiveWord;
card.Noun = db.Table<GeneratedCards>().Skip(back).First().NounWord;
card.Image = db.Table<GeneratedCards>().Skip(back).First().Image;
currentModel = card;
}
}
/// <summary>
/// Loads previous card
/// </summary>
public void Previous_Button_Click(object sender, RoutedEventArgs e)
{
if (firstStart == false)
{
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
{
goingBackNumberOfCards -= 1;
int count = db.Table<GeneratedCards>().Count();
//We are browsing previous card and saving generated card. It's the first time user have pressed "previous" button when genereting a new card
if (goingBackNumberOfCards == -1 && goingBack == false)
{
SaveGeneratedCard(false, 0);
previousCardModified = false;
id += 1;
}
if (goingBackNumberOfCards < -1 && previousCardModified == true && Math.Abs(goingBackNumberOfCards) != count)
{
SaveGeneratedCard(true, back);
previousCardModified = false;
}
if (previousCardModified == true)
{
SaveGeneratedCard(true, back);
previousCardModified = false;
}
goingBack = true;
if (Math.Abs(goingBackNumberOfCards) == count + 1) //Check if card now is the first card. If it is the first card--> do nothing.
{
goingBackNumberOfCards += 1;
}
else
{
if (id > 1)//if user has two cards
{
back = count + goingBackNumberOfCards;
var card = new CardCurrent();
card.Verb = db.Table<GeneratedCards>().Skip(back).First().VerbWord;
card.Adjective = db.Table<GeneratedCards>().Skip(back).First().AdjectiveWord;
card.Noun = db.Table<GeneratedCards>().Skip(back).First().NounWord;
card.Image = db.Table<GeneratedCards>().Skip(back).First().Image;
currentModel = card;
}
}
}
}
}
/// <summary>
/// Deletes every card in genereatedCards.
/// </summary>
public void ClearGeneratedCards()
{
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
{
int size = db.Table<GeneratedCards>().Count();
if (size > 0)
{
IEnumerable<GeneratedCards> entityQuery = db.Table<GeneratedCards>().ToList();
db.DeleteAll<GeneratedCards>();
}
}
}
public async void SavePack_Click(object sender, RoutedEventArgs e)
{
try
{
bool isAlreadyInDatabase = false;
AppBarButton button = new AppBarButton { };
button = sender as AppBarButton;
int packnumber = 1;
packnumber = Convert.ToInt32(button.Label.Substring(13, 1));
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
{
int sizeQuery = db.Table<SavedCards>().Where(packs => packs.PackNumber == packnumber && packs.NounWord == currentModel.Noun &&
packs.Image == currentModel.Image &&
packs.VerbWord == currentModel.Verb &&
packs.AdjectiveWord == currentModel.Adjective).Count();
if (sizeQuery > 0)
{
isAlreadyInDatabase = true;
}
if (isAlreadyInDatabase == false)
{
int size = db.Table<SavedCards>().Where(me => me.PackNumber == packnumber).Count();
if (size < 12)
{
try
{
SavedCards savedcard = new SavedCards() { VerbWord = currentModel.Verb, AdjectiveWord = currentModel.Adjective, NounWord = currentModel.Noun, Image = currentModel.Image, CardNumber = size, PackNumber = packnumber };
db.Insert(savedcard);
}
catch (Exception error)
{
Debug.WriteLine(error.Message);
}
}
else
{
var dialog = new MessageDialog("Pack " + packnumber + " has maximum number of cards!");
await dialog.ShowAsync();
}
}
else
{
var dialog = new MessageDialog("This card is already in the database");
await dialog.ShowAsync();
}
}
}
catch (Exception error)
{
Debug.WriteLine(error.Message);
}
}
public void SubsanttiiviTextBlock_ManipulationCompleted()
{
using (SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
{
int i = 0;
Random rnd = new Random();
i = rnd.Next(db.Table<Nouns>().Count());
currentModel.Noun = db.Table<Nouns>().Skip(i).First().NounWord;
}
if (goingBackNumberOfCards < 0)
{
previousCardModified = true;
}
if (goingBackNumberOfCards == 0)
{
previousCardModified = true;
}
}
private void LoadAllOrders()
{
AllOrdersListView.Items.Clear();
var orderList = (from order in conn.Table <Order>()
orderby order.Id descending
select order).ToList <Order>();
int counter = 1;
foreach (var order in orderList)
{
StackPanel stackpanel = new StackPanel();
stackpanel.Name = order.Id.ToString();
stackpanel.Orientation = Orientation.Horizontal;
stackpanel.Margin = new Thickness(5, 0, 0, 0);
User user = (from oneUser in conn.Table <User>()
where oneUser.Id == order.UserId
select oneUser).ToList <User>()[0];
string orderNo = order.OrderNo;
string userName = user.UserName.ToString();
string date = order.OrderDate.ToString();
string time = order.OrderTime.ToString();
TextBlock textblock = new TextBlock();
textblock.Text = counter++ + ". " + orderNo + " (" + userName + ") " + date;
textblock.FontSize = 18;
stackpanel.Children.Add(textblock);
AllOrdersListView.Items.Add(stackpanel);
}
}
