public string AddUser(UserModel user)
        {
            string status = "";
            string query = "select max(user_id) from users";
            DataTable dt1 = new DataTable();
            SQLiteCommand cmd;

            try
            {
                dbConn.Open();

                if (IsNewUser(user.Email))
                {
                    using (cmd = new SQLiteCommand(query, dbConn))
                    {
                        using (SQLiteDataReader dr = cmd.ExecuteReader())
                        {
                            dt1.Load(dr);
                            // Calculate new user_id
                            user.UserId = Convert.ToInt32(dt1.Rows[0].ItemArray[0].ToString()) + 1;
                        }
                    }

                    query = "insert into users values (" + user.UserId + ", '" + user.Type.ToString() +
                        "', '" + user.Email + "', '" + user.Password + "', '" + user.Status.ToString() + "', '" + user.FirstName + "', '" + user.LastName + "')";

                    cmd = new SQLiteCommand(query, dbConn);
                    cmd.ExecuteNonQuery();

                    status = "User information has been sent to administrators for approval";
                }
                else
                {
                    status = "The given email address is already in use by another user";
                }

                dbConn.Close();
            }
            catch (SQLiteException ex)
            {
                Console.Write(ex.ToString());
                status = ex.ToString();
                dbConn.Close();
            }

            return status;
        }
        public ActionResult Register(RegisterViewModel model)
        {
            if (!ModelState.IsValid)
            {
                return View(model);
            }

            UserModel userModel = new UserModel(model);
            string status = userModel.AddUser();
            ViewData["StatusMessage"] = status;
            model.PopulateUserTypes();

            return View(model);

            //if (ModelState.IsValid)
            //{
            //    var user = new ApplicationUser { UserName = model.Email, Email = model.Email };
            //    var result = await UserManager.CreateAsync(user, model.Password);
            //    if (result.Succeeded)
            //    {
            //        await SignInManager.SignInAsync(user, isPersistent:false, rememberBrowser:false);
                    
            //        // For more information on how to enable account confirmation and password reset please visit http://go.microsoft.com/fwlink/?LinkID=320771
            //        // Send an email with this link
            //        // string code = await UserManager.GenerateEmailConfirmationTokenAsync(user.Id);
            //        // var callbackUrl = Url.Action("ConfirmEmail", "Account", new { userId = user.Id, code = code }, protocol: Request.Url.Scheme);
            //        // await UserManager.SendEmailAsync(user.Id, "Confirm your account", "Please confirm your account by clicking <a href=\"" + callbackUrl + "\">here</a>");

            //        return RedirectToAction("Index", "Home");
            //    }
            //    AddErrors(result);
            //}

            //// If we got this far, something failed, redisplay form
            //return View(model);
        }
        public List<UserModel> GetUsers()
        {
            string query;
            SQLiteCommand cmd;
            UserModel userModel;
            List<UserModel> Users = new List<UserModel>();

            try
            {
                dbConn.Open();

                query = "select * from users";
                DataTable dt = new DataTable();
                using (cmd = new SQLiteCommand(query, dbConn))
                {
                    using (SQLiteDataReader dr = cmd.ExecuteReader())
                    {
                        // Load the reader data into the DataTable
                        dt.Load(dr);

                        // While there are rows in the returned data create EventModels and add them to the EventModel list
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            userModel = new UserModel(dt.Rows[i]);
                            Users.Add(userModel);
                        }
                    }
                }

                dbConn.Close();
            }
            catch (SQLiteException ex)
            {
                Console.Write(ex.ToString());
                dbConn.Close();
            }

            return Users;
        }