public static async Task <Species[]> GetSpeciesAsync(UserInfo userInfo, UserInfoQueryFlags flags = UserInfoQueryFlags.Default)
        {
            string query = userInfo.Id == UserInfo.NullId ?
                           "SELECT * FROM Species WHERE owner = $owner" :
                           "SELECT * FROM Species WHERE user_id = $user_id";

            if (flags.HasFlag(UserInfoQueryFlags.MatchEither))
            {
                query = "SELECT * FROM Species WHERE owner = $owner OR user_id = $user_id";
            }

            List <Species> result = new List <Species>();

            using (SQLiteCommand cmd = new SQLiteCommand(query)) {
                cmd.Parameters.AddWithValue("$owner", userInfo.Username);
                cmd.Parameters.AddWithValue("$user_id", userInfo.Id);

                using (DataTable rows = await Database.GetRowsAsync(cmd)) {
                    foreach (DataRow row in rows.Rows)
                    {
                        result.Add(await SpeciesUtils.SpeciesFromDataRow(row));
                    }

                    result.Sort((lhs, rhs) => lhs.ShortName.CompareTo(rhs.ShortName));
                }
            }

            return(result.ToArray());
        }
        public static async Task <UserInfo> GetUserInfoAsync(string username, ulong userId, UserInfoQueryFlags flags = UserInfoQueryFlags.Default)
        {
            // Note that we may have a null username or a null user ID.
            // At least one of them has to be non-null.

            UserInfo result = null;

            if (username != UserInfo.NullUsername || userId != UserInfo.NullId)
            {
                // If we've been given a user ID, get all species records where that user is the owner.
                // If we've just been given a username, we'll go by username instead.

                string query;

                if (flags.HasFlag(UserInfoQueryFlags.MatchEither))
                {
                    query = "SELECT owner, user_id, timestamp FROM Species WHERE user_id = $user_id OR owner = $owner";
                }
                else
                {
                    if (userId != UserInfo.NullId)
                    {
                        query = "SELECT owner, user_id, timestamp FROM Species WHERE user_id = $user_id";
                    }
                    else
                    {
                        query = "SELECT owner, user_id, timestamp FROM Species WHERE owner = $owner COLLATE NOCASE";
                    }
                }

                using (SQLiteCommand cmd = new SQLiteCommand(query)) {
                    cmd.Parameters.AddWithValue("$user_id", userId);
                    cmd.Parameters.AddWithValue("$owner", username);

                    using (DataTable table = await Database.GetRowsAsync(cmd))
                        if (table.Rows.Count > 0)
                        {
                            userId = table.Rows.Cast <DataRow>()
                                     .Select(x => x.IsNull("user_id") ? UserInfo.NullId : (ulong)x.Field <long>("user_id"))
                                     .FirstOrDefault(x => x != UserInfo.NullId);

                            username = table.Rows.Cast <DataRow>()
                                       .Select(x => x.IsNull("owner") ? UserInfo.NullUsername : x.Field <string>("owner"))
                                       .FirstOrDefault(x => x != UserInfo.NullUsername);

                            result = new UserInfo {
                                Id                       = userId == default(ulong) ? UserInfo.NullId : userId,
                                Username                 = username == default(string) ? UserInfo.NullUsername : username,
                                SubmissionCount          = table.Rows.Count,
                                FirstSubmissionTimestamp = table.Rows.Cast <DataRow>().Select(x => (long)x.Field <decimal>("timestamp")).OrderBy(x => x).FirstOrDefault(),
                                LastSubmissionTimestamp  = table.Rows.Cast <DataRow>().Select(x => (long)x.Field <decimal>("timestamp")).OrderByDescending(x => x).FirstOrDefault()
                            };
                        }
                }
            }

            return(result);
        }
        public static async Task <ICreator> GetCreatorAsync(this SQLiteDatabase database, ICreator creator, UserInfoQueryFlags flags = UserInfoQueryFlags.Default)
        {
            ICreator result = null;

            // Note that we may have a null username or a null user ID.
            // At least one of them has to be non-null.

            if (!string.IsNullOrEmpty(creator.Name) || creator.UserId.HasValue)
            {
                // If we've been given a user ID, get all species records where that user is the owner.
                // If we've just been given a username, we'll go by username instead.

                string query;

                if (flags.HasFlag(UserInfoQueryFlags.MatchEither))
                {
                    query = "SELECT owner, user_id, timestamp FROM Species WHERE user_id = $user_id OR owner = $owner";
                }
                else
                {
                    if (creator.UserId.HasValue)
                    {
                        query = "SELECT owner, user_id, timestamp FROM Species WHERE user_id = $user_id";
                    }
                    else
                    {
                        query = "SELECT owner, user_id, timestamp FROM Species WHERE owner = $owner COLLATE NOCASE";
                    }
                }

                using (SQLiteCommand cmd = new SQLiteCommand(query)) {
                    cmd.Parameters.AddWithValue("$user_id", creator.UserId);
                    cmd.Parameters.AddWithValue("$owner", creator.Name);

                    IEnumerable <DataRow> rows = await database.GetRowsAsync(cmd);

                    if (rows.Count() > 0)
                    {
                        ulong?userId = rows.Select(row => row.IsNull("user_id") ? null : (ulong?)row.Field <long>("user_id"))
                                       .FirstOrDefault(id => id.HasValue);

                        string username = rows.Select(row => row.IsNull("owner") ? string.Empty : row.Field <string>("owner"))
                                          .FirstOrDefault(name => !string.IsNullOrEmpty(name));

                        if (userId.HasValue || !string.IsNullOrEmpty(username))
                        {
                            long firstSpeciesTimestamp = rows.Select(row => (long)row.Field <decimal>("timestamp")).OrderBy(timestamp => timestamp).FirstOrDefault();
                            long lastSpeciesTimestamp  = rows.Select(rowx => (long)rowx.Field <decimal>("timestamp")).OrderByDescending(timestamp => timestamp).FirstOrDefault();

                            result = new Creator(userId, username)
                            {
                                SpeciesCount     = rows.Count(),
                                FirstSpeciesDate = firstSpeciesTimestamp > 0 ? DateUtilities.GetDateFromTimestamp(firstSpeciesTimestamp) : default(DateTimeOffset?),
                                LastSpeciesDate  = lastSpeciesTimestamp > 0 ? DateUtilities.GetDateFromTimestamp(lastSpeciesTimestamp) : default(DateTimeOffset?)
                            };
                        }
                    }
                }
            }

            return(result);
        }
 public static async Task <UserRank> GetRankAsync(UserInfo userInfo, UserInfoQueryFlags flags = UserInfoQueryFlags.Default)
 {
     return((await GetRanksAsync())
            .Where(x => x.User.Id == userInfo.Id || ((flags.HasFlag(UserInfoQueryFlags.MatchEither) || x.User.Id == UserInfo.NullId) && x.User.Username == userInfo.Username))
            .FirstOrDefault());
 }
 public static async Task <UserRank> GetRankAsync(this SQLiteDatabase database, ICreator creator, UserInfoQueryFlags flags = UserInfoQueryFlags.Default)
 {
     return((await database.GetRanksAsync())
            .Where(x => x.User.Id == creator.UserId || ((flags.HasFlag(UserInfoQueryFlags.MatchEither) || x.User.Id == UserInfo.NullId) && x.User.Username == creator.Name))
            .FirstOrDefault());
 }