private static TileNotification LoadTile()
{
List <DataTemple> datalist0 = conn.Query <DataTemple>("select * from DataTemple");
foreach (var item in datalist0)
{
if (SecondaryTile.Exists(item.Schedule_name))
{
CreateSecondaryTile(item.Schedule_name, Calculator(item.Date), item.Date);
}
}
List <DataTemple> datalist = conn.Query <DataTemple>("select * from DataTemple where Date >= ? order by Date asc limit 1", DateTime.Now.ToString("yyyy-MM-dd"));
string _ScheduleName = "";
string _CaculatedDate = "";
string _Date = "";
foreach (var item in datalist)
{
_ScheduleName = item.Schedule_name;
_CaculatedDate = Calculator(item.Date);
_Date = item.Date;
}
if (_ScheduleName != "" && _CaculatedDate != "" && _Date != "")
{
var tile = CreateTile(_ScheduleName, _CaculatedDate, _Date);
return(tile);
}
else
{
return(null);
}
}
private async void SetPersonPicture()
{
List <PersonPictures> datalist = conn.Query <PersonPictures>("select * from PersonPictures where pictureName = ?", "picture");
foreach (var item in datalist)
{
if (item != null)
{
try
{
MemoryStream stream = new MemoryStream(item.picture);
BitmapImage bitmap = new BitmapImage();
await bitmap.SetSourceAsync(stream.AsRandomAccessStream());
MyPersonPicture.ProfilePicture = bitmap;
All.Current.AllPicture.ProfilePicture = bitmap;
}
catch
{
//throw ex;
}
}
}
}
private void InitialData()
{
if (localSettings.Values["Colorful"] != null && localSettings.Values["Colorful"].ToString() == "1")
{
ColorfulSwitch.IsOn = true;
ColorfulButton.IsEnabled = true;
if (localSettings.Values["ToolColor"] != null)
{
color = localSettings.Values["ToolColor"].ToString();
ColorfulEllipse.Fill = new SolidColorBrush(GetColor(color));
ColorfulEllipse.Visibility = Visibility.Visible;
ColorfulBcakgroundPicker.Color = GetColor(color);
}
else
{
ColorfulEllipse.Visibility = Visibility.Collapsed;
}
}
if (localSettings.Values["Transparent"] != null && localSettings.Values["Transparent"].ToString() == "1")
{
TransparentSwitch.IsOn = true;
}
else
{
TransparentSwitch.IsOn = false;
}
if (localSettings.Values["CornerName"] == null)
{
CornerNameTextBox.Text = "夏日";
}
else
{
CornerNameTextBox.Text = localSettings.Values["CornerName"].ToString();
}
int num = 0;
List <DataTemple> datalist0 = conn.Query <DataTemple>("select * from DataTemple");
foreach (var item in datalist0)
{
var pinToDesktopFlag = "DesktopFlag" + item.Schedule_name;
if (localSettings.Values[pinToDesktopFlag] != null && localSettings.Values[pinToDesktopFlag].ToString() == "1")
{
list.Add(item.Schedule_name);
DesViewModel2.DesktopDatas.Add(new DesktopEvents()
{
EventName = item.Schedule_name
});
num++;
SelectedCountTextBlock.Text = "Tip：最多选取三个日程 " + num.ToString() + "/3";
}
else
{
DesViewModel.DesktopDatas.Add(new DesktopEvents()
{
EventName = item.Schedule_name
});
}
}
}
public static ObservableCollection <SentinelsDataset> getNobOgrList(String filter = null)
{
string path = Settings.FullPathSQLite;
//\\192.168.18.182\C$\Data\Users\DefaultAccount\AppData\Local\Packages\06fb6d66-31b3-4beb-893c-2e0d9fe465f1_3asabdzxmrwg6\LocalState\Project1\settings.sqlite
var conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);
NobOgrList.Clear();
if (filter == null)
{
//List<Sentinels> rows = conn.Table<Sentinels>().ToList();
//var rows = conn.Query<Sentinels>("select *,(case NobetGunu WHEN 'PAZARTESİ' then 0 WHEN 'SALI' then 1 WHEN 'ÇARŞAMBA' then 2 WHEN 'PERŞEMBE' then 3 WHEN 'CUMA' then 4 END) as workdayno from Sentinels order by workdayno").ToList();
var Sentinels = conn.Query <SentinelsDataset>("select * from SentinelsDataset");
var rows = (from e in Sentinels
select new SentinelsDataset()
{
id = e.id,
SentinelFullName = e.SentinelFullName,
SentryLocation = e.SentryLocation,
SentryDate = e.SentryDate,
DayNO = (DayEnum)Enum.Parse(typeof(DayEnum), e.SentryDate)
}).OrderBy(e => e.DayNO);
// rows.ForEach(x => { NobOgrList.Add(x); });
foreach (var x in rows)
{
NobOgrList.Add(x);
}
}
else
{
string flt = $"%{filter}%";
var Sentinels = conn.Query <SentinelsDataset>("select * from SentinelsDataset where SentryLocation LIKE ? OR SentinelFullName LIKE ? OR SentryDate LIKE ?", flt, flt, flt).ToList();
var rows = (from e in Sentinels
select new SentinelsDataset()
{
id = e.id,
SentinelFullName = e.SentinelFullName,
SentryLocation = e.SentryLocation,
SentryDate = e.SentryDate,
DayNO = (DayEnum)Enum.Parse(typeof(DayEnum), e.SentryDate)
}).OrderBy(e => e.DayNO);
// rows.ForEach(x => { NobOgrList.Add(x); });
foreach (var x in rows)
{
NobOgrList.Add(x);
}
}
return(NobOgrList);
}
private void GetViewHeight()
{
int count = (int)localSettings.Values["ItemCount"];
List <DataTemple> datalist = new List <DataTemple>();
var allData = conn.Query <DataTemple>("select *from DataTemple");
switch (count)
{
case 1:
viewHeight = 110;
ApplicationView.GetForCurrentView().SetPreferredMinSize(new Size(330, viewHeight));
string a1 = localSettings.Values["DesktopKey0"].ToString();
foreach (var item in allData)
{
if (item.Schedule_name == a1)
{
datalist.Add(item);
}
}
break;
case 2:
viewHeight = 230;
ApplicationView.GetForCurrentView().SetPreferredMinSize(new Size(330, viewHeight));
string b1 = localSettings.Values["DesktopKey0"].ToString();
string b2 = localSettings.Values["DesktopKey1"].ToString();
foreach (var item in allData)
{
if (item.Schedule_name == b1 || item.Schedule_name == b2)
{
datalist.Add(item);
}
}
break;
case 3:
viewHeight = 315;
ApplicationView.GetForCurrentView().SetPreferredMinSize(new Size(330, viewHeight));
string c1 = localSettings.Values["DesktopKey0"].ToString();
string c2 = localSettings.Values["DesktopKey1"].ToString();
string c3 = localSettings.Values["DesktopKey2"].ToString();
foreach (var item in allData)
{
if (item.Schedule_name == c1 || item.Schedule_name == c2 || item.Schedule_name == c3)
{
datalist.Add(item);
}
}
break;
default:
viewHeight = 110;
ApplicationView.GetForCurrentView().SetPreferredMinSize(new Size(330, viewHeight));
break;
}
}
//Returns list of Scores from a given user id
public List <Score> getScoresByUser(int user_id)
{
var scores_query = conn.Query <Score>("Select * from score where id = ? ORDER BY score DESC LIMIT 5", user_id);
List <Score> scores = new List <Score>();
foreach (var score in scores_query)
{
scores.Add(score);
}
return(scores);
}
protected override void OnNavigatedTo(NavigationEventArgs e)
{
base.OnNavigatedTo(e);
parameters = (Database.Stranka)e.Parameter;
ObservableCollection <Database.Stranka> stranke = new ObservableCollection <Database.Stranka>();
Database.Stranka s1 = new Database.Stranka()
{
Ime = parameters.Ime, Priimek = parameters.Priimek, Naslov = parameters.Naslov, Telefonska = parameters.Telefonska, Avto = parameters.Avto
};
stranke.Add(s1);
listView.ItemsSource = stranke;
var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Avtomehanikdb.sqlite");
using (var con = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))
{
var query = con.Query <Database.Nalog>("SELECT * FROM Nalog WHERE StrankaId = ?", parameters.Id);
//foreach (var a in query)
//{
// a.Datum_Zacetka = DateTime.Parse(a.Datum_Zacetka.ToString());
// string s2 = a.Datum_Zacetka.ToString("dd/MM/yyyy hh:mm:ss");
// a.Datum_Zacetka = Convert.ToDateTime(s2);
//}
listView1.ItemsSource = new ObservableCollection <Database.Nalog>(query);
}
}
public static bool setValueByKey(string key_, string value_)
{
bool res = false;
var conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), FullPathSQLite);
try
{
var row = conn.Query <Settings>("select * from Settings where Key=?", key_).FirstOrDefault();
if (row != null)
{
row.Value = value_;
conn.RunInTransaction(() =>
{
conn.Update(row);
});
res = true;
}
else
{
Settings row_ = new Settings {
Key = key_, Value = value_, DefaultValue = value_
};
conn.Insert(row_);
res = true;
}
}
catch (Exception ex)
{
res = false;
}
return(res);
}
// Retrieve the specific Picture from the database.
public Picture ReadPicture(int PictureId)
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
var existingPicture = conn.Query <Picture>("select * from Picture where Id =" + PictureId).FirstOrDefault();
return(existingPicture);
}
}
private static Deputado ListarDeputadoBanco(string idDeputado)
{
using (SQLite.Net.SQLiteConnection conexao = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
var deputado = conexao.Query <Deputado>("select * from deputado where Id =" + "\"" + idDeputado + "\"").FirstOrDefault();
return(deputado);
}
}
// Retrieve the specific contact from the database.
public Contacts ReadContact(int contactid)
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
var existingconact = conn.Query <Contacts>("select * from Contacts where Id =" + contactid).FirstOrDefault();
return(existingconact);
}
}
private void huyButton_Click(object sender, RoutedEventArgs e)
{
NguoiDung nguoidung = connection.Query<NguoiDung>("SELECT * FROM NguoiDung WHERE TenDangNhap=?", muctieu.TenDangNhap).FirstOrDefault();
Frame.Navigate(typeof(TrangChu), nguoidung);
//// neu nhan nut huy thi xoa lich su
//Frame.BackStack.RemoveAt(Frame.BackStackDepth - 1);
//Frame.BackStack.RemoveAt(Frame.BackStackDepth - 1);
}
// Retrieve the specific contact from the database.
public User ReadUser(string username, string password)
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
var existingUser = conn.Query <User>("SELECT * FROM User WHERE Username = '******' AND Password = '******'").FirstOrDefault();
return(existingUser);
}
}
// Retrieve the specific Point from the database.
public Points ReadPoint(int pointid)
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
var existingPoint = conn.Query <Points>("select * from Points where Id =" + pointid).FirstOrDefault();
return(existingPoint);
}
}
private static ObservableCollection <Deputado> ListarDeputadosPorEstadoBanco(string uf)
{
using (SQLite.Net.SQLiteConnection conexao = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
var deputados = conexao.Query <Deputado>("select * from deputado where Uf = " + "\"" + uf + "\"").ToList <Deputado>();
ObservableCollection <Deputado> ListaDeputados = new ObservableCollection <Deputado>(deputados);
return(ListaDeputados);
}
}
private void refresh()
{
var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Avtomehanikdb.sqlite");
using (var con = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))
{
var query = con.Query <Database.Opravilo>("SELECT * FROM Opravilo WHERE NalogId= ?", parameterN.Id);
gridView.ItemsSource = new ObservableCollection <Database.Opravilo>(query);
}
}
public void AddButtonExecute()
{
WorkerTable currentWorker = new WorkerTable();
currentWorker.Lastname = "Worker";
currentWorker.Firstname = "New";
Database.Insert(currentWorker);
var current = Database.Query <WorkerTable>("SELECT * FROM WorkerTable Where id = (SELECT MAX(Id) FROM WorkerTable);");
WorkerListing.Add(new Worker(currentWorker.Firstname, currentWorker.Lastname, current[0].Id));
}
private void Iskanje_Click(object sender, RoutedEventArgs e)
{
iskanjeStrank = textBox.Text;
var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Avtomehanikdb.sqlite");
using (var con = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))
{
var query = con.Query <Database.Stranka>("SELECT * FROM STRANKA WHERE Ime = ? OR Priimek = ?", iskanjeStrank, iskanjeStrank);
listView.ItemsSource = new ObservableCollection <Database.Stranka>(query);
}
}
private void IzbrisiOpravilo(object sender, RoutedEventArgs e)
{
Button _button = (Button)sender;
var id = _button.Tag;
var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Avtomehanikdb.sqlite");
using (var con = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))
{
var query = con.Query <Database.Opravilo>("DELETE FROM Opravilo WHERE Id =?", id);
}
refresh();
}
//Update existing conatct
public void UpdateDetails(Contacts ObjContact)
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
var existingconact = conn.Query <Contacts>("select * from Contacts where Id =" + ObjContact.Id).FirstOrDefault();
if (existingconact != null)
{
conn.RunInTransaction(() =>
{
conn.Update(ObjContact);
});
}
}
}
//Update existing Picture
public void UpdateDetails(Picture ObjPicture)
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
var existingPicture = conn.Query <Picture>("select * from Picture where Id =" + ObjPicture.Id).FirstOrDefault();
if (existingPicture != null)
{
conn.RunInTransaction(() =>
{
conn.Update(ObjPicture);
});
}
}
}
public void AddButtonExecute()
{
ShiftTable currentShift = new ShiftTable();
currentShift.Start_hour = (new TimeSpan(0, 0, 0)).ToString("c");
currentShift.End_hour = (new TimeSpan(23, 59, 0)).ToString("c");
currentShift.Minwo = 0;
currentShift.Optwo = 0;
var Service = Database.Query <ServiceTable>("SELECT * FROM ServiceTable WHERE NAME= '" + SelectedActivity.Service_name + "';");
currentShift.FKService = Service[0].Id;
Database.Insert(currentShift);
SelectedActivity.ShiftListing.Add(new Shift(new TimeSpan(0, 0, 0), new TimeSpan(23, 59, 0), 0, 0));
}
//Delete specific Picture
public void DeletePicture(int Id)
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
var existingconact = conn.Query <Picture>("select * from Picture where Id =" + Id).FirstOrDefault();
if (existingconact != null)
{
conn.RunInTransaction(() =>
{
conn.Delete(existingconact);
});
}
}
}
public static People FindScore(int Score)
{
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
var existingconact = conn.Query <People>("select * from People where score = " + Score).FirstOrDefault();
if (existingconact == null)
{
return(existingconact);
}
else
{
return(existingconact);
}
}
}
private void SearchContact_Click(object sender, RoutedEventArgs e)
{
string NameValue = Name.Text;
string PhoneNumberValue = Phone.Text;
var querrysearch = connect.Query <Contact>("select * from Contact where Name = ? and PhoneNumber = ?", NameValue, PhoneNumberValue);
string NameResult = "";
string PhoneResult = "";
foreach (var q in querrysearch)
{
NameResult = NameResult + " " + q.Name;
PhoneResult = PhoneResult + " " + q.PhoneNumber;
}
Result.Text = "Name: " + NameResult + "\nPhoneNumber: " + PhoneResult;
}
private static ObservableCollection <DeputadoFrenquencia> ListarFrequenciaDeputadoBanco(string IdParlamentar)
{
try
{
using (SQLite.Net.SQLiteConnection conexao = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
List <DeputadoFrenquencia> frequencias = conexao.Query <DeputadoFrenquencia>("select * from DeputadoFrenquencia where IdParlamentar =" + "\"" + IdParlamentar + "\"").ToList <DeputadoFrenquencia>();
ObservableCollection <DeputadoFrenquencia> ListaDeputadoFrenquencia = new ObservableCollection <DeputadoFrenquencia>(frequencias);
return(ListaDeputadoFrenquencia);
}
}
catch
{
return(null);
}
}
private static ObservableCollection <Projeto> ListarProjetooDeputadoBanco(string IdParlamentarAutor)
{
try
{
using (SQLite.Net.SQLiteConnection conexao = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
List <Projeto> projetos = conexao.Query <Projeto>("select * from Projeto where IdParlamentarAutor =" + "\"" + IdParlamentarAutor + "\"").ToList <Projeto>();
ObservableCollection <Projeto> ListaProjetos = new ObservableCollection <Projeto>(projetos);
return(ListaProjetos);
}
}
catch
{
return(null);
}
}
private void Search_Contact(object sender, RoutedEventArgs e)
{
string name = Name.Text;
string phonenumber = Phone.Text;
var querry = connect.Query <Contact>("" +
"Select * from Contact where name like ? and phonenumber like ?", name, phonenumber);
string Name1 = "";
string PhoneNumber1 = "";
foreach (var q in querry)
{
Name1 = Name1 + " " + q.name;
PhoneNumber1 = PhoneNumber1 + " " + q.phonenumber;
}
Result.Text = "Name: " + Name1 + "\nPhoneNumber: " + PhoneNumber1;
}
private static ObservableCollection <Comissao> ListarComissaoDeputadoBanco(string idDeputado)
{
try
{
using (SQLite.Net.SQLiteConnection conexao = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
List <Comissao> comissoes = conexao.Query <Comissao>("select * from Comissao where IdeCadastroDeputado =" + "\"" + idDeputado + "\"").ToList <Comissao>();
ObservableCollection <Comissao> ListaComissoeDeputados = new ObservableCollection <Comissao>(comissoes);
return(ListaComissoeDeputados);
}
}
catch
{
return(null);
}
}
private static ObservableCollection <GastoTipo> ListarGastoTipoDeputadoBanco(string idDeputado)
{
try
{
using (SQLite.Net.SQLiteConnection conexao = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.DB_PATH))
{
List <GastoTipo> gastosTipo = conexao.Query <GastoTipo>("select * from GastoTipo where idDeputado =" + "\"" + idDeputado + "\"").ToList <GastoTipo>();
ObservableCollection <GastoTipo> ListaGastoCnpjj = new ObservableCollection <GastoTipo>(gastosTipo);
return(ListaGastoCnpjj);
}
}
catch
{
return(null);
}
}
