public static bool Authentication(UserProfileSessionData up)
        {
            return true;
            //if (up == null)
            //    return false;
            //SqlConnection _dba = DBModel.Get_DB_Connection();
            //StringBuilder _sba = new StringBuilder();

            //string query = _sba.AppendFormat("select top 1 * from {0} where password=@password and username=@username", DataBaseTables.USERS).ToString();
            //var result = _dba.Query<User>(query, new { up.username, up.password}).FirstOrDefault();
            //_dba.Dispose();
            //if (result != null)
            //{
            //    return true;
            //}
            //return false;
        }
 public static bool Validate(UserProfileSessionData up)
 {
     Auth ls = new Auth();
     return false;
 }
        public UserProfileSessionData Login(string username, string password)
        {
            _sb.Clear();
            string query = _sb.AppendFormat("select top 1 * from {0} where password=@password and username=@username and status = @ACTIVE",DBTables.USERS).ToString();
            var result = _db.Query<User>(query, new { username, password, ActiveStatus.ACTIVE }).FirstOrDefault();

            if (result !=null)
            {
                UserProfileSessionData up = new UserProfileSessionData();
                up.password = password;
                up.username = username;

                _sb.Clear();
                string query1 = "";
                if(result.role_code == RoleCode.STUDENT)
                {
                    query1 = _sb.AppendFormat("select top 1 * from {0} where user_id=@user_id", DBTables.STUDENT).ToString();
                    var result1 = _db.Query<Student>(query1, new { result.user_id, result.role_code }).FirstOrDefault();
                    up.fullname = result1.full_name;

                }
                else if (result.role_code == RoleCode.PARENT)
                {
                    query1 = _sb.AppendFormat("select top 1 * from {0} where user_id=@user_id", DBTables.PARENT).ToString();
                    var result1 = _db.Query<Parent>(query1, new { result.user_id, result.role_code }).FirstOrDefault();
                    up.fullname = result1.full_name;

                }
                else if (result.role_code == RoleCode.TEACHER)
                {
                    query1 = _sb.AppendFormat("select top 1 * from {0} where user_id=@user_id", DBTables.TEACHER).ToString();
                    var result1 = _db.Query<Parent>(query1, new { result.user_id, result.role_code }).FirstOrDefault();
                    up.fullname = result1.full_name;
                }
                else if (result.role_code == RoleCode.ADMIN)
                {
                    query1 = _sb.AppendFormat("select top 1 * from {0} where user_id=@user_id", DBTables.ADMIN).ToString();
                    var result1 = _db.Query<Users_detail>(query1, new { result.user_id}).FirstOrDefault();
                    up.fullname = result1.first_name + " " + result1.last_name;
                }

                if (result.role_code == RoleCode.TEACHER || result.role_code == RoleCode.ADMIN)
                {
                    var status = ActiveStatus.ACTIVE;
                    var query2 = "Select * from " + DBTables.SESSION + " where school_id = @school_id and isDefault=@status and status=@status";
                    var session = _db.Query<Session>(query2, new { result.school_id, status }).FirstOrDefault();
                    if(session !=  null)
                    {
                        up.session_id = session.session_id;
                        up.session_is_active = true;
                    }
                }
                else
                {
                    up.session_is_active = false;
                }

                up.user_id = result.user_id;
                up.role_code = result.role_code;
                up.school_id = result.school_id;

                _sb.Clear();
                string s1 = _sb.AppendFormat("select top 1 * from {0} where {0}.school_id=@school_id", DBTables.SCHOOL).ToString();
                var school = _db.Query<School>(s1, new { result.school_id }).FirstOrDefault();
                up.school_code = school.code;
                up.school_name = school.name;
                _db.Dispose();

                return up;
            }
            return null;
        }