using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Entity;
using System.Data.Linq;

namespace Nusel.Controllers
{
   public class Authentication 
    {
        public class Connection_action : Connectionstring
        {
            public string Name2;
            public string Matricula;
            public string Lastconn;
            public Controllers.Log_in.Login_actions Login_Action;
            public Controllers.Connectionstring Class_String;
            public MainWindows.Welcome welcome;
            public string Connectionstring;
            public Models.ModelContext context;

            public void IsvalidUser(string ID, string Password)
            {
                Connectionstring = Class_String.con;
                if (ID.Length == 0)
                {
                    errormessage.Text = "Enter an ID or Password.";
                    TextBoxID.Focus();
                }
                else if (ID.Length <= 4 || Password.Length <= 4)
                {
                    errormessage.Text = "Invalid ID Or Password.";
                    TextBoxID.Focus();
                }
                else
                {
                    try
                    {
                        using (var db = new Models.ModelContext())
                        {
                            var query = db.Student_LoginModel.Find(ID, Password);

                            if (query == null)
                            {
                                var binky = db.Student_LoginModel.Where(u => u.Student_FirstName == ID).FirstOrDefault();
                            }
                            else
                            {

                            }
                        }

///
///Esto de aqui abajo es lo que trato de simplificar con entity
///
                       // var UsernameQuery = from cust in con.Customers where cust.City == "London" select cust.CompanyName;
                        SqlConnection con = new SqlConnection(Connectionstring);
                        con.Open();
                        SqlCommand cmd = new SqlCommand("Select * from Students where ID='" + ID + "'  and Password_ID='" + Password + "'", con);
                        cmd.CommandType = CommandType.Text;
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        DataSet dataSet = new DataSet();
                        adapter.Fill(dataSet);
                        if (dataSet.Tables[0].Rows.Count > 0)
                        {
                            Name2 = dataSet.Tables[0].Rows[0]["FirstName"].ToString() + " " + dataSet.Tables[0].Rows[0]["LastName"].ToString();
                            Matricula = dataSet.Tables[0].Rows[0]["ID"].ToString();
                            Lastconn = dataSet.Tables[0].Rows[0]["LastLogin"].ToString();
                            welcome.CompleteName.Text = Name2;
                            welcome.LastconnDate.Text = Lastconn;
                            welcome.Matricula.Text = Matricula;
                            Login_Action.Login_action(ID);
                            welcome.Show();
                            Close();
                        }
                        else
                        {
                            errormessage.Text = "Sorry! Please enter existing ID/Password.";
                        }
                        if (con.State == ConnectionState.Open)
                        {
                            con.Close();
                        }
                    }
                    catch (Exception)
                    {
                        errormessage.Text = "Failed to connect with the server";
                    }
                }
            }
        }
    }
}