//
		// This method is invoked when the application has loaded and is ready to run. In this
		// method you should instantiate the window, load the UI into it and then make the window
		// visible.
		//
		// You have 17 seconds to return from this method, or iOS will terminate your application.
		//
		public override bool FinishedLaunching (UIApplication app, NSDictionary options)
		{
			Forms.Init ();
			Xamarin.FormsMaps.Init ();

			// create a new window instance based on the screen size
			window = new UIWindow (UIScreen.MainScreen.Bounds);

			var sqliteFilename = "EvolveSQLite.db3";
			string documentsPath = Environment.GetFolderPath (Environment.SpecialFolder.Personal); // Documents folder
			string libraryPath = Path.Combine (documentsPath, "..", "Library"); // Library folder
			var path = Path.Combine(libraryPath, sqliteFilename);

			// This is where we copy in the prepopulated database
			Console.WriteLine (path);
			if (!File.Exists (path)) {
				File.Copy (sqliteFilename, path);
			}

			var conn = new SQLite.SQLiteConnection(path);

			// Set the database connection string
			App.SetDatabaseConnection (conn);

			// If you have defined a view, add it here:
			// window.RootViewController  = navigationController;
			window.RootViewController = App.GetMainPage ().CreateViewController ();

			// make the window visible
			window.MakeKeyAndVisible ();

			return true;
		}

 public IEnumerable<Project> FindAllExcept(int projectId)
 {
     using (var db = new SQLite.SQLiteConnection(Settings.DatabasePath))
     {
         return db.Query<Project>("select * from Project where Id <> ?", projectId);
     }
 }

		//
		// This method is invoked when the application has loaded and is ready to run. In this
		// method you should instantiate the window, load the UI into it and then make the window
		// visible.
		//
		// You have 17 seconds to return from this method, or iOS will terminate your application.
		//
		public override bool FinishedLaunching (UIApplication app, NSDictionary options)
		{
			Forms.Init ();
			Xamarin.FormsMaps.Init ();

			// create a new window instance based on the screen size
			window = new UIWindow (UIScreen.MainScreen.Bounds);

			var sqliteFilename = "EvolveSQLite.db3";
			string documentsPath = Environment.GetFolderPath (Environment.SpecialFolder.Personal); // Documents folder
			string libraryPath = Path.Combine (documentsPath, "..", "Library"); // Library folder
			var path = Path.Combine(libraryPath, sqliteFilename);

			// This is where we copy in the prepopulated database
			Console.WriteLine (path);
			if (!File.Exists (path)) {
				File.Copy (sqliteFilename, path);
			}

			var conn = new SQLite.SQLiteConnection(path);

			// Set the database connection string
			App.SetDatabaseConnection (conn);

			LoadApplication (new App ());

			return base.FinishedLaunching (app, options);
		}

		public IPage<Artist> Page(ICriteria<Artist> criteria) {
			using (var cn = new SQLite.SQLiteConnection(_dbConnectionString)) {
				var query = cn.Table<Artist>().AsQueryable();

				if (criteria.Keywords.Any()) {
					criteria.Keywords.ToList().ForEach(word => query = query.Where(a => a.Name.ToLower().Contains(word.ToLower())));
				}

				var totalRecords = query.Count();

				if (criteria.SortBy.Any()) {
					foreach (var kvp in criteria.SortBy) {
						switch (kvp.Value) {
							case ListSortDirection.Ascending:
								query = query.OrderBy(kvp.Key);
								break;
							case ListSortDirection.Descending:
								query = query.OrderBy(kvp.Key + " descending");
								break;
						}
					}
				}

				if (criteria.Page.HasValue) {
					query = query.Skip((criteria.Page.Value - 1) * criteria.PageSize.GetValueOrDefault());
				}

				if (criteria.PageSize.HasValue) {
					query = query.Take(criteria.PageSize.Value);
				}

				return new Page<Artist>(criteria.Page ?? 0, criteria.PageSize ?? totalRecords, totalRecords, query.ToList());
			}
		}

        public void update(string name,string address)
        {          
            using (var db = new SQLite.SQLiteConnection(app.dbPath))
            {
                try
                {//db.Execute("update meterbox set currentUnits = currentUnits -" + used);
                    var existing = db.Query<Job>("select * from Job").First();

                    if (existing != null)
                    {
                        existing.name = name;
                        existing.address = address;
                        db.RunInTransaction(() =>
                        {
                            db.Update(existing);
                        });
                    }
                }

                catch (Exception e)
                {

                }
            }
        }

        public bool AddAlbum(AlbumViewModel model)
        {
            try
            {
                using (var db = new SQLite.SQLiteConnection(app.DBPath))
                {
                    int success = db.Insert(new Album()
                    {
                        CollectionId = model.CollectionId,
                        Title = model.Title,
                        Artist = model.Artist,
                        LastFmId = model.LastFmId,
                        MusicBrainzId = model.MusicBrainzId,
                        DateAdded = DateTime.Now,
                        Void = false
                    });
                    if (success != 0)
                        return true;
                }
                return false;
            }

            catch
            {
                return false;
            }
        }

 public IEnumerable<Item> FindAll(int sourceId)
 {
     using (var db = new SQLite.SQLiteConnection(Settings.DatabasePath))
     {
         return db.Query<Item>("select * from Item where SourceId = ?", sourceId);
     }
 }

 public void UpdateText(string newText, int id)
 {
     using (var db = new SQLite.SQLiteConnection(Settings.DatabasePath))
     {
         db.Execute("update Item set Text = ? where Id = ?", newText, id);
     }
 }

		private void CreateTable()
		{
			using (var conn = new SQLite.SQLiteConnection(GetDatabasePath()))
			{
				conn.CreateTable<Person>();
			}
		}

        public SingleCollectionViewModel GetSingleCollection(int collectionId)
        {
            SingleCollectionViewModel collection = new SingleCollectionViewModel();

            using (var db = new SQLite.SQLiteConnection(app.DBPath))
            {
                var _collection = (db.Table<Collection>().Where(
                    c => c.Id == collectionId)).FirstOrDefault();
                collection.Id = _collection.Id;
                collection.Title = _collection.Title;
                collection.DateCreated = _collection.DateCreated;
                collection.Void = _collection.Void;

                // GET ALBUMS
                var _albums = (db.Table<Album>().Where(
                    c => c.CollectionId == collectionId)).ToList();

                List<lfm> albumList = new List<lfm>();
                foreach (var _album in _albums)
                {
                    lfm album = LastGetAlbum(_album.MusicBrainzId);
                    albumList.Add(album);
                }
                collection.Albums = albumList;
            }
            return collection;
        }

        public int AddPet(Pet _pet)
        {
            //returns new ID
            int success;
            using (var db = new SQLite.SQLiteConnection(Constants.DbPath))
            {
                success = db.Insert(new Pet()
                {
                    PetStageId = _pet.PetStageId,
                    FavoriteGameObjectId = _pet.FavoriteGameObjectId,
                    DislikeGameObjectId = _pet.DislikeGameObjectId,
                    Name = _pet.Name,
                    Health = _pet.Health,
                    Hygene = _pet.Hygene,
                    Hunger = _pet.Hunger,
                    Energy = _pet.Energy,
                    Discipline = _pet.Discipline,
                    Mood = _pet.Mood,
                    Gender = _pet.Gender,
                    Age = _pet.Age,
                    Sleeping = _pet.Sleeping,
                    Current = _pet.Current,
                    BirthDate = _pet.BirthDate,
                    LastUpdated = _pet.LastUpdated,
                    Dead = false
                });

            }
            return success;
        }

 public Project Get(int projectId)
 {
     using (var db = new SQLite.SQLiteConnection(Settings.DatabasePath))
     {
         return db.Find<Project>(projectId);
     }
 }

 public IEnumerable<Preference> FindAll()
 {
     using (var db = new SQLite.SQLiteConnection(Settings.DatabasePath))
     {
         return db.Query<Preference>("select * from Preference");
     }
 }

        private void btnAdd_Click(object sender, RoutedEventArgs e)
        {

            if (FieldValidationExtensions.GetIsValid(AddressbookUserName) && FieldValidationExtensions.GetIsValid(AddressbookEmail))
            {

                var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
                using (var db = new SQLite.SQLiteConnection(dbPath))
                {
                    db.CreateTable<AddressBookEntree>();

                    User addressbookuser = new User();
                    addressbookuser.UserName = AddressbookUserName.Text;
                    addressbookuser.EmailAddress = AddressbookEmail.Text;

                    db.RunInTransaction(() =>
                    {
                        db.Insert(addressbookuser);

                        db.Insert(new AddressBookEntree() { OwnerUserID = App.loggedInUser.Id, EntreeUserID = addressbookuser.Id });
                    });
                }

            }

            this.Frame.Navigate(typeof(Dashboard));

        }

        private void RemptionLoaded(object sender, RoutedEventArgs e)
        {
            try
            {
                var path = Path.Combine(ApplicationData.Current.LocalFolder.Path, "Shopping.db3");

                using (var db = new SQLite.SQLiteConnection(path))
                {
                    var a = from x in db.Table<ShopLists>() where x.AdminName == AdminName && x.Condition == 1 select x;
                    //MessageBox.Show(a.Count().ToString());

                    foreach (var v in a)
                    {
                        RedemptionShopList.Add(v);
                    }

                }

                RedemptionList.ItemsSource = RedemptionShopList;
            }
            catch
            {

            }
        }

 public ObservableCollection<SubjectViewModel> getEnglishQuestions1(string gr)
 {
     string no = "no";
     string yes = "yes";
     subject = new ObservableCollection<SubjectViewModel>();
     using (var db = new SQLite.SQLiteConnection(app.dbPath))
     {
         //var query = db.Table<English>();
         var query = db.Query<English>("select * from english where GRADE ='" + gr + "' and read ='" + no + "'");
         foreach (var j in query)
         {
             var sub = new SubjectViewModel()
             {
                 ID = j.Id,
                 QUESTION = j.question,
                 ANSWER = j.answer,
                 ANSWER1 = j.answer2,
                 ANSWER2 = j.answer3,
                 GRADE = j.GRADE,
                 READ = j.read
             };
             subject.Add(sub);
         }
     }
     return subject;
 }

 private void butSaveClick(object sender,EventArgs e)
 {
     TextView txtprinter =FindViewById<TextView> (Resource.Id.txtad_printer);
     TextView txtprefix =FindViewById<TextView> (Resource.Id.txtad_prefix);
     TextView txttitle =FindViewById<TextView> (Resource.Id.txtad_title);
     pathToDatabase = ((GlobalvarsApp)this.Application).DATABASE_PATH;
     AdPara apra = new AdPara ();
     apra.Prefix = txtprefix.Text.ToUpper();
     apra.PrinterName = txtprinter.Text.ToUpper();
     using (var db = new SQLite.SQLiteConnection(pathToDatabase))
     {
         var list  = db.Table<AdPara> ().ToList<AdPara> ();
         if (list.Count == 0) {
             db.Insert (apra);
         } else {
             apra = list [0];
             apra.Prefix = txtprefix.Text.ToUpper();
             apra.PrinterName = txtprinter.Text.ToUpper();
             apra.PaperSize = spinner.SelectedItem.ToString ();
             apra.ReceiptTitle =txttitle.Text.ToUpper();
             db.Update (apra);
         }
     }
     base.OnBackPressed();
 }

        public SQLite.SQLiteConnection GetConnection()
        {
            var sqliteFilename = "TodoSQLite.db3";
            var  documentsPath =  System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal); // Documents folder

            // string documentsPath = System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal); // Documents folder
            var path = Path.Combine(documentsPath, sqliteFilename);

            // This is where we copy in the prepopulated database
            Console.WriteLine (path);

            /*
             * nel caso dovessi usare un database pre-popolato
            if (!File.Exists(path))
            {
                var s = Forms.Context.Resources.OpenRawResource(Resource.Raw.TodoSQLite);  // RESOURCE NAME ###
                // create a write stream
                FileStream writeStream = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);
                // write to the stream
                ReadWriteStream(s, writeStream);
            }
            */

            var conn = new SQLite.SQLiteConnection(path);

            // Return the database connection
            return conn;
        }

        public ObservableCollection<MealViewModel> GetMeals()
        {
            meals = new ObservableCollection<MealViewModel>();
            using (var db = new SQLite.SQLiteConnection(App.DBPath))
            {
                var query = db.Table<Meal>().OrderBy(c => c.Name);
                foreach (var _meal in query)
                {
                    var meal = new MealViewModel()
                    {
                        Id = _meal.Id,
                        DeliveryNoteId = _meal.DeliveryNoteId,
                        Name = _meal.Name,
                        DeliveryDate = _meal.DeliveryDate,
                        DeliveryTime = _meal.DeliveryTime,
                        DeliveryLocation = _meal.DeliveryLocation,
                        PickupDate = _meal.PickupDate,
                        Contact = _meal.Contact,
                        ContactId = _meal.ContactId,
                        NumberOfGuests = _meal.NumberOfGuests,
                        SilverWare = _meal.SilverWare,
                        MealItemIDs = (List<int>)_converter.ConvertBack(_meal.MealItemIDs, null, null, ""),
                        MealItemIDsWithWeight = (Dictionary<float,float>)_dictionaryConverterFloat.ConvertBack(_meal.MealItemIDsWithWeight, null, null, "")
                    };

                    meals.Add(meal);
                }
            }

            return meals;
        }

 private void Update(Source model)
 {
     using (var db = new SQLite.SQLiteConnection(Settings.DatabasePath))
     {
         db.Update(model);
     }
 }