public void InserindoListaFabricantes(List<Fabricante> fabricantes)
{
using (var dbConn = new SQLiteConnection(DB_path))
{
dbConn.DropTable<Fabricante>();
dbConn.Dispose();
dbConn.Close();
}
foreach (Fabricante f in fabricantes)
this.Inserindo(f);
}
public App()
{
// The root page of your application
MainPage = new ContentPage {
Content = new StackLayout {
VerticalOptions = LayoutOptions.Center,
Children = {
new Label {
XAlign = TextAlignment.Center,
Text = "Welcome to Xamarin Forms!"
}
}
}
};
// path to db
var path = Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.MyDocuments), "mydb");
// open connection and attempt to apply encryption using PRAGMA statement
var conn = new SQLiteConnection (path);
conn.Execute ("PRAGMA key = 'passme'");
int v = conn.ExecuteScalar<int> ("SELECT count(*) FROM sqlite_master");
conn.Close ();
// open another connection, this time use wrong password. It will still open, but should fail the
// query (see docs on key PRAGMA https://www.zetetic.net/sqlcipher/sqlcipher-api/)
var conn2 = new SQLiteConnection (path);
conn2.Execute ("PRAGMA key = 'wrongpassword'");
int v2 = conn2.ExecuteScalar<int> ("SELECT count(*) FROM sqlite_master");
conn2.Close ();
}
public static void addCompletedCircle(Circle circle)
{
completeCircles.Add(circle);
string dbPath = GetDBPath();
SQLiteConnection db;
db = new SQLiteConnection(dbPath);
db.Insert(circle);
var result = db.Query<Line>("SELECT * FROM Lines");
foreach (Line l in result) {
Console.WriteLine("Line DB: bx {0}, by {1}, ex {2}, ey {3}", l.begin.X, l.begin.Y, l.end.X, l.end.Y);
}
var result2 = db.Query<Circle>("SELECT * FROM Circles");
foreach (Circle c in result2) {
Console.WriteLine("Circle DB: cx {0}, cy {1}, p2x {2}, p2yy {3}", c.center.X, c.center.Y, c.point2.X, c.point2.Y);
}
db.Close();
db = null;
foreach (Line l in completeLines) {
Console.WriteLine("Line CL: bx {0}, by {1}, ex {2}, ey {3}", l.begin.X, l.begin.Y, l.end.X, l.end.Y);
}
foreach (Circle c in completeCircles) {
Console.WriteLine("Circle CC: cx {0}, cy {1}, p2x {2}, p2yy {3}", c.center.X, c.center.Y, c.point2.X, c.point2.Y);
}
}
// This method checks to see if the database exists, and if it doesn't, it creates
// it and inserts some data
protected void CheckAndCreateDatabase(string dbName)
{
// create a connection object. if the database doesn't exist, it will create
// a blank database
using(SQLiteConnection db = new SQLiteConnection (GetDBPath (dbName)))
{
// create the tables
db.CreateTable<Person> ();
// skip inserting data if it already exists
if(db.Table<Person>().Count() > 0)
return;
// declare vars
List<Person> people = new List<Person> ();
Person person;
// create a list of people that we're going to insert
person = new Person () { FirstName = "Peter", LastName = "Gabriel" };
people.Add (person);
person = new Person () { FirstName = "Thom", LastName = "Yorke" };
people.Add (person);
person = new Person () { FirstName = "J", LastName = "Spaceman" };
people.Add (person);
person = new Person () { FirstName = "Benjamin", LastName = "Gibbard" };
people.Add (person);
// insert our people
db.InsertAll (people);
// close the connection
db.Close ();
}
}
async void Login_Loaded()
{
try
{
await ApplicationData.Current.LocalFolder.CreateFileAsync("Shopping.db3");
var path = Path.Combine(ApplicationData.Current.LocalFolder.Path, "Shopping.db3");
using (var db = new SQLite.SQLiteConnection(path))
{
// Create the tables if they don't exist
db.CreateTable<ShopLists>();
db.CreateTable<ShopAdmins>();
db.Commit();
db.Dispose();
db.Close();
}
ServiceReference1.SoapServiceSoapClient sc=new SoapServiceSoapClient();
sc.AllCouponsCompleted += (a, b) => MessageBox.Show(b.Result);
sc.AllCouponsAsync("hacking");
}
catch (Exception ex)
{
FaultHandling(ex.StackTrace);
}
}
public static List<Drink> getDrinks()
{
var conn = new SQLiteConnection (System.IO.Path.Combine (documentsFolder(), "database.db"));
var results = conn.Query<Drink> ("SELECT * FROM Drink");
conn.Close ();
return results;
}
public override void ViewWillAppear(bool animated)
{
base.ViewWillAppear (animated);
// This is not double loading becuase it is a new _loader each time and the _newSL is a get set
_loader = new List<SongToSave> ();
_dbWorker = new DBWorker ();
_dbWorker.StartDBWorker ();
dbPath = _dbWorker.GetPathToDb ();
var conn = new SQLiteConnection (dbPath);
// this seems redundant.
foreach (var item in conn.Table<SongToSave>()) {
var tempSong = new SongToSave () {
BookTitle = item.BookTitle,
BookAuthor = item.BookAuthor,
PlayPosition = item.PlayPosition
};
_loader.Add (tempSong);
}
conn.Close ();
// why am I using two list?
_newSL = _loader;
TableView.ReloadData ();
}
public async Task <ObservableCollection <AccountsModel> > GetAllAccountsDB()
{
ObservableCollection <AccountsModel> _accounts_DB = new ObservableCollection <AccountsModel>();
try
{
var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
using (var db = new SQLite.SQLiteConnection(dbpath))
{
var AccountsDB = db.Table <AccountsModel>().Where(a => a.Name.Contains("a"));
foreach (AccountsModel accountModel in AccountsDB)
{
_accounts_DB.Add(accountModel);
}
db.Commit();
db.Dispose();
db.Close();
//var line = new MessageDialog("Records Inserted");
//await line.ShowAsync();
}
}
catch (SQLiteException)
{
}
return(_accounts_DB);
}
private void Button_Click_4(object sender, RoutedEventArgs e)
{
// ApplicationBar.IsVisible = true;
try
{
var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
using (var db = new SQLite.SQLiteConnection(dbpath))
{
list1.Items.Clear();
// var sd = from x in db.Table<person>() select x;
//.Distinct<kural>()
if (list1.Items == null)
{
list1.Items.Add("Recent List is Empty!!!Start Search");
}
var d = from x in db.Table <person>() select x;
foreach (var sd in d)
{
list1.Items.Add(sd.word);
}
db.Close();
}
}
catch
{
}
//search(textbox2.Text.ToString());
}
private async Task AccountStore(ObservableCollection <AccountsModel> account)
{
try
{
var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
using (var db = new SQLite.SQLiteConnection(dbpath))
{
foreach (AccountsModel accountM in Accounts)
{
var accountInDB = db.Find <AccountsModel>(accountM.Accountid);
if (accountInDB != null)
{
db.Delete(accountM);
}
db.Insert(accountM);
}
db.Commit();
db.Dispose();
db.Close();
}
}
catch (SQLiteException)
{
}
}
private async void deleteselected(object sender, RoutedEventArgs e)
{
try
{
var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
using (var db = new SQLite.SQLiteConnection(dbpath))
{
db.Delete <person>(list1.SelectedItem.ToString());
var d = from x in db.Table <person>() select x;
list1.Items.Clear();
foreach (var sd in d)
{
list1.Items.Add(sd.name.ToString());
//list1.Items.Add(sd.address.ToString());
//list1.Items.Add(sd.phone.ToString());
}
db.Dispose();
db.Close();
}
var line = new MessageDialog("Selected Item Deleted");
await line.ShowAsync();
}
catch
{
}
}
public void criando_populando(string s)
{
dbConn = new SQLiteConnection(DB_path);
SQLiteCommand cmd = new SQLiteCommand(dbConn);
cmd.CommandText = s;
cmd.ExecuteNonQuery();
dbConn.Close();
}
public void wylaczBaze()
{
if (conn == null)
{
return;
}
conn.Close();
conn = null;
}
public static void clearLines()
{
completeLines.Clear();
string dbPath = GetDBPath();
SQLiteConnection db;
db = new SQLiteConnection(dbPath);
db.DeleteAll<Line>();
db.Close();
db = null;
}
public List<Fabricante> LendoFabricantesLocal()
{
using(dbConn = new SQLiteConnection(DB_path))
{
//dbConn.CreateTable<Fabricante>();
List<Fabricante> fabricantes = dbConn.Table<Fabricante>().ToList();
dbConn.Close();
return fabricantes;
}
}
public static SyncParams LoadSavedSyncParams()
{
SyncParams param = null;
SQLiteConnection db = new SQLiteConnection(SyncController.DatabasePath);
db.CreateTable<SyncParams>();
if (db.Table<SyncParams>().Count() != 0)
param = db.Table<SyncParams>().First();
else
param = new SyncParams();
db.Close();
return param;
}
// method to check for db exsistence, and create and insert if not found
protected void CheckAndCreateDatabase(string pathToDatabase)
{
using (var db = new SQLiteConnection (pathToDatabase)) {
db.CreateTable<SongToSave> ();
//this doesn't seem to make sense why it is here because I am creating it either way I think ^
if (db.Table<SongToSave> ().Count () > 0) {
//Console.WriteLine ("The DB is already at:" + pathToDatabase); // debugging
return;
}
db.Close ();
}
}
// public static bool saveChanges()
// {
// // Save to db
// string dbPath = GetDBPath();
// var db = new SQLiteConnection(dbPath);
// db.DeleteAll<BNRItem>();
// db.BeginTransaction();
// db.InsertAll(allItems);
// db.Commit();
// db.Close();
// return true;
// Archive method of saving
// // returns success or failure // For archiving method of saving
// string path = itemArchivePath(); // For archiving method of saving
// NSMutableArray newArray = new NSMutableArray(); // For archiving method of saving
// foreach (BNRItem item in allItems) { // For archiving method of saving
// newArray.Add(item); // For archiving method of saving
// } // For archiving method of saving
// return NSKeyedArchiver.ArchiveRootObjectToFile(newArray, path); // For archiving method of saving
// }
public static void addAssetType(string assetType)
{
string dbPath = GetDBPath();
SQLiteConnection db;
if (File.Exists(dbPath)) {
db = new SQLiteConnection(dbPath);
db.BeginTransaction();
var at = new BNRAssetType();
at.assetType = assetType;
allAssetTypes.Add(at);
db.Insert(at);
db.Commit();
db.Close();
}
}
// This method checks to see if the database exists, and if it doesn't, it creates
// it and inserts some data
public static void CheckAndCreateDatabase ()
{
string path = Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.Personal), "CourseSchedule.sqlite");
// create a connection object. if the database doesn't exist, it will create
// a blank database
using (SQLiteConnection db = new SQLiteConnection (path)) {
// create the tables
db.CreateTable<CourseSchedule> ();
// close the connection
db.Close ();
}
}
public static CourseSchedule FindCourseSchedule (decimal classId)
{
CheckAndCreateDatabase ();
string path = Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.Personal), "CourseSchedule.sqlite");
using (SQLiteConnection db = new SQLiteConnection (path)) {
// create the tables
var classSchedule = db.Find<CourseSchedule> (c => c.ClassId == classId);
// close the connection
db.Close ();
return classSchedule;
}
}
public void search(string add)
{
var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "encrypted.db3");
word.Text = add;
using (SQLiteConnection db = new SQLiteConnection(dbpath))
{
var query = from x in db.Table <embed>()
where x.word.ToLower() == add
select x;
foreach (var x in query)
{
tiled = x.meaning;
}
fff.Text = tiled;
//fff.Blocks.Clear();
//Run myRun1 = new Run();
//myRun1.Text = x.meaning;
//Paragraph myParagraph = new Paragraph();
//myParagraph.Inlines.Add(myRun1);
//fff.Blocks.Add(myParagraph);
cou = cou + 1;
tiles(tiled, cou);
var rpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "data.db3");
using (var rdb = new SQLite.SQLiteConnection(rpath))
{
// Create the tables if they don't exist
var d = from x in rdb.Table <person>() where x.word.ToLower() == add select x;
if (d.Count() == 0)
{
rdb.Insert(new person()
{
word = add,
});
}
rdb.Commit();
// rdb.Dispose();
rdb.Close();
}
db.Close();
}
}
public override Task<IEnumerable<Scrobble>> GetCachedAsync()
{
using (var db = new SQLiteConnection(DatabasePath, SQLiteOpenFlags.ReadOnly))
{
var tableInfo = db.GetTableInfo(typeof (Scrobble).Name);
if (!tableInfo.Any())
{
return Task.FromResult(Enumerable.Empty<Scrobble>());
}
var cached = db.Query<Scrobble>("SELECT * FROM Scrobble");
db.Close();
return Task.FromResult(cached.AsEnumerable());
}
}
public void Inserindo(Fabricante f)
{
try
{
dbConn = new SQLiteConnection(DB_path);
dbConn.CreateTable<Fabricante>();
dbConn.Insert(f);
//dbConn.Dispose();
dbConn.Close();
}
catch(Exception e)
{
System.Diagnostics.Debug.WriteLine("##Erro Inserindo## "+e);
}
}
public void LoadData()
{
SQLiteConnection sqlite_conn = new SQLiteConnection(dbPath, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite, true);
sqlite_conn.CreateTable<Person>();
//sqlite_conn.Insert(new Person() { FirstName = "Keming", LastName = "Chen" });
List<Person> personList = sqlite_conn.Query<Person>("SELECT * FROM Person");
for (int i = 0; i < personList.Count; i++)
{
_myListBox.Items.Add(personList[i].FirstName + " " + personList[i].LastName);
}
sqlite_conn.Close();
}
/// <summary>
/// Create Table for accout method
/// </summary>
///
private async Task <bool> createAccountTable()
{
try
{
var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
using (var db = new SQLite.SQLiteConnection(dbpath))
{
// Create the tables if they don't exist
db.CreateTable <AccountsModel>();
db.Commit();
db.Dispose();
db.Close();
}
//var line = new MessageDialog("Table Created");
//await line.ShowAsync();
}
catch (SQLiteException exLite)
{
throw new Exception(exLite.Message);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
try
{
var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
using (var db = new SQLite.SQLiteConnection(dbpath))
{
AccountStore(Accounts);
// Create the tables if they don't exist
db.Commit();
db.Dispose();
db.Close();
//var line = new MessageDialog("Records Inserted");
//await line.ShowAsync();
}
}
catch (SQLiteException)
{
}
return(true);
}
private async void drop(object sender, RoutedEventArgs e)
{
try
{
var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
using (var db = new SQLite.SQLiteConnection(dbpath))
{
db.DropTable <person>();
db.Dispose();
db.Close();
}
var line = new MessageDialog("Table Dropped");
await line.ShowAsync();
}
catch
{
}
}
private void AddContact()
{
// Create temp contact variable to help add info to the database.
Classes.Contact tempContact = new Classes.Contact();
// Open the Database
var db = new SQLite.SQLiteConnection(TempUser.GetDBPath());
// If Name and Number or Name and Email then set fields in tempContact and add to database
if (((Name.Text != "") && (Number.Text != "")) || ((Name.Text != "") && (Email.Text != "")))
{
// If name is not empty, then set name variable of temp
if (Name.Text != "")
{
tempContact.SetName(Name.Text);
}
// If number is not empty, then set phone number variable of temp
if (Number.Text != "")
{
tempContact.SetPhone(Number.Text);
}
// If email is not empty, then set email variable of temp
if (Email.Text != "")
{
tempContact.SetEmail(Email.Text);
}
// Reset Fields to Blank
Name.Text = "";
Number.Text = "";
Email.Text = "";
// Insert into Database
db.Insert(tempContact);
}
// Close Database
db.Close();
MainActivity.UpdateListview();
}
private void createtable()
{
try
{
var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
using (var db = new SQLite.SQLiteConnection(dbpath))
{
// Create the tables if they don't exist
db.CreateTable <person>();
db.Commit();
db.Dispose();
db.Close();
}
}
catch
{
}
}
private void Cache(IEnumerable<Scrobble> scrobbles)
{
using (var db = new SQLiteConnection(DatabasePath, SQLiteOpenFlags.ReadWrite))
{
var tableInfo = db.GetTableInfo(typeof (Scrobble).Name);
if (!tableInfo.Any())
{
db.CreateTable<Scrobble>();
}
db.BeginTransaction();
foreach (var scrobble in scrobbles)
{
db.Insert(scrobble);
}
db.Commit();
db.Close();
}
}
private async void insert(object sender, RoutedEventArgs e)
{
try
{
if (txt1.Text != "" && txt2.Text != "" && txt3.Text != "")
{
var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
using (var db = new SQLite.SQLiteConnection(dbpath))
{
// Create the tables if they don't exist
db.Insert(new person()
{
name = txt1.Text.ToString(),
address = txt2.Text.ToString(),
phone = Convert.ToDouble(txt3.Text.ToString()),
}
);
db.Commit();
db.Dispose();
db.Close();
var line = new MessageDialog("Records Inserted");
await line.ShowAsync();
}
}
else
{
throw new NullReferenceException("Enter The Data In Textboxes");
//var line = new MessageDialog("Enter The Data In Textboxes");
//await line.ShowAsync();
}
}
catch (SQLiteException)
{
}
catch (NullReferenceException ex)
{
list1.Items.Add(ex.Message);
}
}
public static void addCompletedLine(Line line)
{
completeLines.Add(line);
string dbPath = GetDBPath();
SQLiteConnection db;
db = new SQLiteConnection(dbPath);
db.Insert(line);
var result = db.Query<Line>("SELECT * FROM Lines");
foreach (Line l in result) {
Console.WriteLine("Line DB: bx {0}, by {1}, ex {2}, ey {3}", l.begin.X, l.begin.Y, l.end.X, l.end.Y);
}
db.Close();
db = null;
foreach (Line l in completeLines) {
Console.WriteLine("Line CL: bx {0}, by {1}, ex {2}, ey {3}", l.begin.X, l.begin.Y, l.end.X, l.end.Y);
}
}
// Top Songs Cache
// public static DateTime topSongsCacheDate {
// get {
// return (DateTime)NSUserDefaults.StandardUserDefaults.ValueForKey(new NSString("topSongsCacheDate"));
// }
// set {
// topSongsCacheDate = value;
// NSUserDefaults.StandardUserDefaults.SetValueForKey(value, new NSString("topSongsCacheDate"));
// }
// }
public static void FetchRSSFeed(Block completionBlock) // UITableViewController tvc)
{
channel = new RSSChannel();
channel.type = "posts";
items.Clear();
string dbPath = GetDBPath();
SQLiteConnection db;
if (!File.Exists(dbPath)) {
db = new SQLiteConnection(dbPath);
db.CreateTable<RSSItem>();
db.Close();
db = null;
}
db = new SQLiteConnection(dbPath);
items = db.Query<RSSItem>("SELECT * FROM RSSItems WHERE type='post' ORDER BY ID DESC");
db.Close();
db = null;
FetchRSSAsync(completionBlock);
}
private async void createtable(object sender, RoutedEventArgs e)
{
try
{
var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
using (var db = new SQLite.SQLiteConnection(dbpath))
{
// Create the tables if they don't exist
db.CreateTable <person>();
db.Commit();
db.Dispose();
db.Close();
}
var line = new MessageDialog("Table Created");
await line.ShowAsync();
}
catch
{
}
}
protected override void OnCreate(Bundle bundle)
{
base.OnCreate(bundle);
// Set our view from the "main" layout resource
SetContentView(Resource.Layout.Main);
// Get our button from the layout resource,
// and attach an event to it
using (var conn = new SQLite.SQLiteConnection(Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal).ToString(), "database.sqlite")))
{
if (conn.ExecuteScalar <int>("SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'Login'") == 0)
{
conn.CreateTable <Login>();
conn.Commit();
}
else
{
if (conn.ExecuteScalar <int>("Select Count(*) From Login") != 0)
{
var NxtAct = new Intent(this, typeof(StartActivity));
StartActivity(NxtAct);
}
}
conn.Close();
}
Button button = FindViewById <Button> (Resource.Id.Login);
button.Click += delegate {
var NxtAct = new Intent(this, typeof(LoginActivity));
StartActivity(NxtAct);
};
Button button1 = FindViewById <Button>(Resource.Id.Register);
button1.Click += delegate {
var NxtAct = new Intent(this, typeof(RegisterActivity));
StartActivity(NxtAct);
};
}
protected void saveProfileImage(string image)
{
ProfileImage profileImage = new ProfileImage();
profileImage.Base64 = image;
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(App.FilePath))
{
conn.DropTable <ProfileImage>();
conn.CreateTable <ProfileImage>();
int rowsAdded = conn.Insert(profileImage);
if (rowsAdded > 0)
{
Debug.WriteLine("Inserted Itinerary");
}
else
{
Debug.WriteLine("Failed to Insert Itinerary");
}
conn.Close();
}
}
public static void loadItemsFromArchive()
{
// Archive method of saving
// string path = lineArchivePath();
// var unarchiver = (NSMutableArray)NSKeyedUnarchiver.UnarchiveFile(path);
// if (unarchiver != null) {
// for (int i = 0; i < unarchiver.Count; i++) {
// completeLines.Add(unarchiver.GetItem<Line>(i));
// }
// }
string dbPath = GetDBPath();
SQLiteConnection db;
if (!File.Exists(dbPath)) {
db = new SQLiteConnection(dbPath);
db.CreateTable<Line>();
var columnInfo = db.GetTableInfo("Lines");
foreach (SQLiteConnection.ColumnInfo ci in columnInfo) {
//Console.WriteLine("Collumn Info: {0}", ci.Name);
}
db.Close();
db = null;
}
db = new SQLiteConnection(dbPath);
completeLines = db.Query<Line>("SELECT * FROM Lines");
// foreach (Line line in completeLines) {
// //line.setColor();
// //Console.WriteLine("Line bx {0}, by {1}, ex {2}, ey {3}", line.begin.X, line.begin.Y, line.end.X, line.end.Y);
// }
db.Close();
db = null;
}
public App(string filePath)
{
Syncfusion.Licensing.SyncfusionLicenseProvider.RegisterLicense("MjMyMTM2QDMxMzgyZTMxMmUzMGpzYzNpT1FVcnp4ZDJwdWhQUDhNdnI0MlNxMTlLTEEyZkRIOStpYkpMTlE9");
InitializeComponent();
MainPage = new MainPage();
FilePath = filePath;
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(App.FilePath))
{
conn.CreateTable<User>();
var users = conn.Table<User>().ToList();
var doctors = conn.Table<Doctor>();
//Each prescription reminder requires a unique ID which we will track and save to settings.
bool hasPrescriptionReminderKey = Preferences.ContainsKey("PrescriptionReminderNotifID");
int PrescriptionReminderID = 1000000;
//If the setting does not exist, create it and set it with a starting value.
if (!hasPrescriptionReminderKey)
{
Preferences.Set("PrescriptionReminderNotifID", PrescriptionReminderID);
}
else
{
PrescriptionReminderID = Preferences.Get("PrescriptionReminderNotifID", 1000000);
//If the ID number gets within 10000 of overflowing, we will reset it.
if ((PrescriptionReminderID + 10000) >= int.MaxValue)
{
PrescriptionReminderID = 1000000;
}
}
if (users?.Any() == true)
{
//Have to resubmit notifications after device restart because they are not saved.
var appts = conn.Table<Appointment>().ToList();
var prescriptions = conn.Table<Prescription>().ToList();
//We need to set up reminders for any prescriptions currently being taken.
prescriptions = prescriptions.Where(item => (DateTime.Parse(item.startDate).Date <= DateTime.Now.Date) && (DateTime.Parse(item.endDate).Date >= DateTime.Now.Date)).ToList();
if (prescriptions?.Any() == true)
{
foreach (var prescript in prescriptions)
{ //Refresh the reminder notifications for each prescription if it is not entirely past the time that it must be taken.
if (DateTime.Parse(prescript.endDate) >= DateTime.Now)
{
PrescriptionNotifClass.PrescriptionNotifHandler(prescript);
}
}
}
if (appts?.Any() == true)
{
foreach (var x in appts)
{
if (x.reminderTime > DateTime.Now)
{
var doctor = conn.GetWithChildren<Doctor>(x.dId);
CrossLocalNotifications.Current.Show("Appointment Reminder", "You have an appointment with Dr. " + doctor.dName + " at " + x.aptDate.ToShortTimeString(), x.Id, x.reminderTime);
}
}
}
}
conn.Close();
}
}
//카테고리 수정
public void UpdateIncomeCategoryHandling(string oldCategoryName, string newCategoryName, string tableFormName)
{
try
{
using (SQLiteConnection conn = new SQLiteConnection(dbPath, true))
{
//수입항목 카테고리 수정
if (tableFormName.Equals("IncomeCategoryForm"))
{
//기존에 존재하는 카테고리의 ID를 알기위해서 해당 목록을 불러온다.
IncomeCategoryForm exist = conn.Query<IncomeCategoryForm>("SELECT * FROM IncomeCategoryTable WHERE categoryName ='" + oldCategoryName + "';").FirstOrDefault();
if (exist != null)
{
//기존에 있던 정보를 수정한다.
exist.categoryName = newCategoryName;
//업데이트
conn.Update(exist);
}
// 현재 oldcategoryname으로 되어있는 항목들의 category를 전부 바꿔준다.
List<MoneyDBForm> existMoney = conn.Query<MoneyDBForm>("SELECT * FROM MoneyTable WHERE category = '" + oldCategoryName + "';").ToList<MoneyDBForm>();
if(existMoney != null)
{
foreach (var item in existMoney)
{
item.category = newCategoryName;
conn.Update(item);
}
}
}
//지출항목 카테고리 수정
else if (tableFormName.Equals("ExpenseCategoryForm"))
{
//ID알기
ExpenseCategoryForm exist = conn.Query<ExpenseCategoryForm>("SELECT * FROM ExpenseCategoryTable WHERE categoryName = '" + oldCategoryName + "';").FirstOrDefault();
if (exist != null)
{
//기존에 있던 정보를 수정한다.
exist.categoryName = newCategoryName;
//업데이트
conn.Update(exist);
}
// 현재 oldcategoryname으로 되어있는 항목들의 category를 전부 바꿔준다.
List<MoneyDBForm> existMoney = conn.Query<MoneyDBForm>("SELECT * FROM MoneyTable WHERE category = '" + oldCategoryName + "';").ToList<MoneyDBForm>();
if (existMoney != null)
{
foreach (var item in existMoney)
{
item.category = newCategoryName;
conn.Update(item);
}
}
}
//연결종료
conn.Close();
}
}
catch (Exception ex)
{
ex.Message.ToString();
}
}
//테이블 전체 삭제 및 재생성
public bool AllTableDrop()
{
bool result = false;
try
{
//DB Connection
SQLiteConnection conn = new SQLiteConnection(dbPath, true);
//테이블 삭제
conn.DropTable<DBForm.MoneyDBForm>();
conn.DropTable<DBForm.ExpenseCategoryForm>();
conn.DropTable<DBForm.IncomeCategoryForm>();
//연결종료
conn.Close();
//새로 테이블을 만든다.
CreateDatabaseAsync();
//카테고리 새로 세팅
BasicSetting();
//어플리케이션종료하기위해 결과 값을 리턴한다.
result = true;
}
catch (Exception ex)
{
ex.Message.ToString();
result = false;
}
return result;
}
//수입 카테고리 조회 쿼리문 처리
public List<IncomeCategoryForm> IncomeCategoryQueryHandling(string outQuery)
{
try
{
//연결
SQLiteConnection conn = new SQLiteConnection(dbPath, true);
//쿼리문을 날려서 해당 테이블을 조회해서 List로 출력
List<IncomeCategoryForm> incomeQueryResultList = conn.Query<IncomeCategoryForm>(outQuery);
//연결종료
conn.Close();
//정상적으로 되면 결과 리턴
return incomeQueryResultList;
}
catch (Exception ex)
{
ex.Message.ToString();
}
//불러오는데 실패하면 NULL을 리턴
return null;
}
public void SaveSyncParam()
{
SQLiteConnection db = new SQLiteConnection(SyncController.DatabasePath);
db.CreateTable<SyncParams>();
this.id = 1;
if (db.Table<SyncParams>().Count() != 0)
db.Update(this);
else
db.Insert(this);
db.Close();
}
public static void ProcessPath(string databasePath, string filePath)
{
//TODO ensure directory exists for the database
using (var db = new SQLiteConnection(databasePath)) {
DatabaseLookups.CreateTables(db);
var hdCollection = DriveUtilities.ProcessDriveList(db);
var start = DateTime.Now;
List<string> arrHeaders = DriveUtilities.GetFileAttributeList(db);
var directory = new DirectoryInfo(filePath);
var driveLetter = directory.FullName.Substring(0, 1);
//TODO line it up with the size or the serial number since we will have removable drives.
var drive = hdCollection.FirstOrDefault(letter => letter.DriveLetter.Equals(driveLetter, StringComparison.OrdinalIgnoreCase));
if (directory.Exists) {
ProcessFolder(db, drive, arrHeaders, directory);
}
//just in case something blew up and it is not committed.
if (db.IsInTransaction) {
db.Commit();
}
db.Close();
}
}
//Delete all contactlist or delete Contacts table
public void DeleteAllContact()
{
using (var dbConn = new SQLiteConnection(App.DB_PATH))
{
//dbConn.RunInTransaction(() =>
// {
dbConn.DropTable<GhiChu>();
dbConn.CreateTable<GhiChu>();
dbConn.Dispose();
dbConn.Close();
//});
}
}
public void Dispose()
{
connection.Close();
}
// This method checks to see if the database exists, and if it doesn't, it creates
// it and inserts some data
protected void CheckAndCreateDatabase(string dbName)
{
// create a connection object. if the database doesn't exist, it will create
// a blank database
using(SQLiteConnection db = new SQLiteConnection (GetDBPath (dbName)))
{
// create the tables
db.CreateTable<GroupPersonData> ();
// close the connection
db.Close ();
}
}
private string GetInvoiceSumm(DateTime printDate1, DateTime printDate2)
{
string text = "";
int count = 0;
double ttlAmtl=0;
double ttlCN=0;
double ttl = 0;
double rate = 0;
double roundVal = 0;
double GranInv = 0;
double GranCN = 0;
double GranCSInv = 0;
double GranCSCN = 0;
string cnno = "";
bool isSamedate = printDate1==printDate2;
string pathToDatabase = ((GlobalvarsApp)Application.Context).DATABASE_PATH;
SQLiteConnection db = new SQLiteConnection(pathToDatabase);
PrintSummHeader (printDate1, printDate2, ref text);
var list = db.Table<Invoice>().ToList();
var cnlist = db.Table<CNNote>().ToList();
var grps = from p in list
where p.invdate >= printDate1 && p.invdate <= printDate2
group p by p.invdate into g
select new { key = g.Key, result = g.ToList() };
foreach (var grp in grps)
{
count = 1;
GranInv = 0;
GranCN = 0;
GranCSInv = 0;
GranCSCN = 0;
//"------------------------------------------\";
//12. 123456789012345678901234567890 12345567
//CN without Inv
var cnNoInvs = cnlist.Where (x => x.invdate == grp.key && x.invno == "");
if (!isSamedate) {
text = text + grp.key.ToString ("dd-MM-yyyy") + "\n";
text = text + "------------------------------------------\n";
}
foreach (var itm in grp.result)
{
ttlAmtl=itm.amount+itm.taxamt;
ttlCN=0;
ttl = 0;
cnno = "";
//var cninv = cnlist.Where(x => x.invno == itm.invno && x.invdate == itm.invdate).FirstOrDefault();
var cninv = cnlist.Where(x => x.invno == itm.invno ).FirstOrDefault();
if (cninv!=null)
{
cnno = cninv.cnno;
ttlCN=cninv.amount + cninv.taxamt;
}
if (itm.trxtype == "CASH")
{
ttlAmtl = Utility.AdjustToNear(ttlAmtl, ref roundVal);
ttlCN = Utility.AdjustToNear(ttlCN, ref roundVal);
GranCSInv = GranCSInv + ttlAmtl;
GranCSCN = GranCSCN + ttlCN;
}
else
{
GranInv = GranInv + ttlAmtl;
GranCN = GranCN+ttlCN;
}
if (itm.description.Length > 30)
{
//12. 123456789012345678901234567890 12345567
text = text + count.ToString().PadRight(2, ' ') + ". " + itm.description.Substring(0, 30) + " " + itm.created.ToString("hh:mmtt") + "\n";
text = text + "".PadRight(4, ' ') + itm.description.Substring(30).Trim()+ "\n";
}
else
{
text = text + count.ToString().PadRight(2, ' ') + ". " + itm.description.PadRight(30,' ') + " " + itm.created.ToString("hh:mmtt") + "\n";
}
text = text + "INV AMT -"+itm.invno.PadRight(21,' ')+ttlAmtl.ToString("n2").PadLeft(12,' ')+"\n";
text = text + "CN AMT -" + cnno.PadRight(21, ' ') + ttlCN.ToString("n2").PadLeft(12, ' ') + "\n";
ttl = ttlAmtl - ttlCN;
if (ttlCN > 0)
rate = Math.Round((ttlCN / ttlAmtl)*100, 2);
text = text + "TOTAL COLLECT AMOUNT -".PadRight(30,' ') + ttl.ToString("n2").PadLeft(12, ' ') + "\n";
text = text + "RETURN RATE % -".PadRight(30, ' ') + rate.ToString("n2").PadLeft(12, ' ') + "\n\n";
count += 1;
}
foreach (var itm in cnNoInvs)
{
ttlAmtl=0;
ttlCN=itm.amount+itm.taxamt;
ttl = 0;
cnno = "";
if (itm.trxtype == "CASH")
{
ttlCN = Utility.AdjustToNear(ttlCN, ref roundVal);
GranCSCN = GranCSCN + ttlCN;
}
else
{
GranCN = GranCN+ttlCN;
}
if (itm.description.Length > 30)
{
//12. 123456789012345678901234567890 12345567
text = text + count.ToString().PadRight(2, ' ') + ". " + itm.description.Substring(0, 30) + " " + itm.created.ToString("hh:mmtt") + "\n";
text = text + "".PadRight(4, ' ') + itm.description.Substring(30).Trim()+ "\n";
}
else
{
text = text + count.ToString().PadRight(2, ' ') + ". " + itm.description.PadRight(30,' ') + " " + itm.created.ToString("hh:mmtt") + "\n";
}
text = text + "INV AMT -"+itm.invno.PadRight(21,' ')+ttlAmtl.ToString("n2").PadLeft(12,' ')+"\n";
text = text + "CN AMT -" + itm.cnno.PadRight(21, ' ') + ttlCN.ToString("n2").PadLeft(12, ' ') + "\n";
ttl = ttlAmtl - ttlCN;
rate = 0;
text = text + "TOTAL COLLECT AMOUNT -".PadRight(30,' ') + ttl.ToString("n2").PadLeft(12, ' ') + "\n";
text = text + "RETURN RATE % -".PadRight(30, ' ') + rate.ToString("n2").PadLeft(12, ' ') + "\n\n";
count += 1;
}
text = text + "\n";
text = text + "SALES SUMMARY\n\n";
text = text + "TOTAL INVOICE ".PadRight(30, ' ') + GranInv.ToString("n2").PadLeft(12, ' ') + "\n";
text = text + "TOTAL CN INVOICE".PadRight(30, ' ') + GranCN.ToString("n2").PadLeft(12, ' ') + "\n";
text = text + "TOTAL INVOICE COLLECT ".PadRight(30, ' ') + (GranInv - GranCN).ToString("n2").PadLeft(12, ' ') + "\n\n";
text = text + "TOTAL CASH ".PadRight(30, ' ') + GranCSInv.ToString("n2").PadLeft(12, ' ') + "\n";
text = text + "TOTAL CN CASH".PadRight(30, ' ') + GranCSCN.ToString("n2").PadLeft(12, ' ') + "\n";
text = text + "TOTAL CASH COLLECT ".PadRight(30, ' ') + (GranCSInv - GranCSCN).ToString("n2").PadLeft(12, ' ') + "\n\n";
}
text += "------------------------------------------\n";
text += "CASH COLLECTION :\n\n\n";
text += "(-)DIESEL EXP :\n\n\n";
text += "(-)OTHER EXP :\n\n\n";
text += "(=)NET COLLECTION :\n\n\n";
text += "(-)PAYMENT :\n\n\n";
text += "(=)SHORT :\n\n\n";
text += "PREPARED BY:\n\n\n\n\n";
text += "VERIFY BY :\n\n\n\n\n";
text += "------------------------------------------\n\n\n\n";
db.Close ();
return text;
}
