コード例 #1
0
        public async Task <User> ValidateUser(string email, string password, UserType userTypeId)
        {
            SqlQuery proc = new SqlQuery(@" 
                select * from users 
                where email=@email 
                and  password =HashBytes('SHA2_256', @password) and userTypeId=@userTypeId;
                ", 30, System.Data.CommandType.Text);

            proc.AddInputParam("email", SqlDbType.NVarChar, email);
            proc.AddInputParam("password", SqlDbType.NVarChar, password);
            proc.AddInputParam("userTypeId", SqlDbType.Int, (int)userTypeId);
            return(await _queryExecutor.ExecuteAsync(proc, dataReader =>
            {
                while (dataReader.Read())
                {
                    return new UserResponse()
                    {
                        UserId = SqlQueryResultParser.GetValue <Int32>(dataReader, "userId"),
                        Role = (UserType)SqlQueryResultParser.GetValue <Int32>(dataReader, "userTypeId"),
                        IsActive = SqlQueryResultParser.GetValue <Boolean>(dataReader, "isActive")
                    };
                }
                return null;
            }));
        }
コード例 #2
0
        public async Task SetIsTrial(int teamId, bool isTrial)
        {
            SqlQuery proc = new SqlQuery(@" 
          update team set IsTrial=@isTrial, Updated=GETUTCDATE() where teamId=@teamId;
                ", 30, System.Data.CommandType.Text);

            proc.AddInputParam("isTrial", SqlDbType.Bit, isTrial);
            proc.AddInputParam("teamId", SqlDbType.Int, teamId);
            await _queryExecutor.ExecuteAsync(proc);
        }
コード例 #3
0
        public async Task UpdateTeamDocDescription(int teamId, string description, string newDescription)
        {
            SqlQuery proc = new SqlQuery(
                @"update team_images 
                set description = @newDescription 
                where teamId = @teamId and description = @description;",
                30, System.Data.CommandType.Text
                );

            proc.AddInputParam("teamId", SqlDbType.Int, teamId);
            proc.AddInputParam("newDescription", SqlDbType.NVarChar, newDescription);
            proc.AddInputParam("description", SqlDbType.NVarChar, description);
            await _queryExecutor.ExecuteAsync(proc);
        }
コード例 #4
0
        public async Task AddTeamImage(int teamId, string imageGuid, string description)
        {
            SqlQuery proc = new SqlQuery(@"    
                insert into team_images
                    (TeamId, ImageGuid, Description)
                values
                    (@teamId, @imageGuid, @description); 
                ", 30, System.Data.CommandType.Text);

            proc.AddInputParam("teamId", SqlDbType.Int, teamId);
            proc.AddInputParam("imageGuid", SqlDbType.NVarChar, imageGuid);
            proc.AddInputParam("description", SqlDbType.NVarChar, description);
            await _queryExecutor.ExecuteAsync(proc);
        }
コード例 #5
0
        public async Task <List <TeamPhotoResponse> > GetTeamImageDocs(int teamId)
        {
            SqlQuery proc = new SqlQuery(@" 
                select * 
                from team_images 
                where teamId=@teamId;
                ", 30, System.Data.CommandType.Text);

            proc.AddInputParam("teamId", SqlDbType.Int, teamId);
            return(await _queryExecutor.ExecuteAsync(proc, dataReader =>
            {
                List <TeamPhotoResponse> ltsd = new List <TeamPhotoResponse>();
                while (dataReader.Read())
                {
                    ltsd.Add(new TeamPhotoResponse()
                    {
                        TeamId = SqlQueryResultParser.GetValue <Int32>(dataReader, "TeamId"),
                        ImageGuid = SqlQueryResultParser.GetValue <String>(dataReader, "ImageGuid"),
                        Description = SqlQueryResultParser.GetValue <String>(dataReader, "Description"),
                        Created = SqlQueryResultParser.GetValue <DateTime>(dataReader, "Created")
                    });
                }
                return ltsd;
            }));
        }
コード例 #6
0
        public async Task <int?> CreateUser(UserCreate user)
        {
            SqlQuery proc = new SqlQuery(@" 
	IF (Select count(*) from users where email=@email and usertypeid=@userTypeId ) = 0
	BEGIN
	insert into users (password, email, UserTypeId)
	values (HashBytes('SHA2_256', @password),@email,@userTypeId); select SCOPE_IDENTITY();

	END;
                ", 30, System.Data.CommandType.Text);

            proc.AddInputParam("email", SqlDbType.NVarChar, user.Email);
            proc.AddInputParam("password", SqlDbType.NVarChar, user.Password);
            proc.AddInputParam("userTypeId", SqlDbType.Int, (int)user.Role);
            return(await _queryExecutor.ExecuteAsync(proc, sqlReader => GetReturnValue <int?>(sqlReader)));
        }
コード例 #7
0
        public async Task <User> GetUserFromAPIKey(string apiKey)
        {
            SqlQuery proc = new SqlQuery(@" 

                    select u.* from user_api_keys uak
                        inner join users u on u.userId=uak.userId
                        where uak.ApiKeyValue=@apikey;
                ", 30, System.Data.CommandType.Text);

            proc.AddInputParam("apiKey", SqlDbType.NVarChar, apiKey);
            return(await _queryExecutor.ExecuteAsync(proc, dataReader =>
            {
                while (dataReader.Read())
                {
                    return (new User()
                    {
                        UserId = SqlQueryResultParser.GetValue <Int32>(dataReader, "userId"),
                        Email = SqlQueryResultParser.GetValue <String>(dataReader, "email"),
                        IsActive = SqlQueryResultParser.GetValue <Boolean>(dataReader, "IsActive"),
                        Role = (UserType)SqlQueryResultParser.GetValue <Int32>(dataReader, "UserTypeId")
                    });
                }
                return null;
            }));
        }
コード例 #8
0
        public async Task <int?> CheckUserAPIKey(string apiKey)
        {
            SqlQuery proc = new SqlQuery(@" select userId from user_api_keys where ApiKeyValue=@apikey; ",
                                         30, System.Data.CommandType.Text);

            proc.AddInputParam("apiKey", SqlDbType.NVarChar, apiKey);
            return(await _queryExecutor.ExecuteAsync(proc, sqlReader => GetReturnValue <int?>(sqlReader)));
        }
コード例 #9
0
        public async Task UpdateTeamDate(int teamId)
        {
            SqlQuery proc = new SqlQuery(@" 
          update team set Updated=GETUTCDATE() where teamId=@teamId;
                ", 30, System.Data.CommandType.Text);

            proc.AddInputParam("teamId", SqlDbType.Int, teamId);
            await _queryExecutor.ExecuteAsync(proc);
        }
コード例 #10
0
        public async Task <string> AddImageAsync(byte[] imageIn, int userId)
        {
            byte[] imageData = ImageHelper.CreateImageThumbnail(imageIn, true, 1000, 1000); //don't know if we always want a small pic
                                                                                            //right now just saving to jpg
                                                                                            //  byte[] imageThumbData = ImageHelper.CreateImageThumbnail(model.ImageData, 200, 200);

            byte[]   imageThumb = ImageHelper.CreateImageThumbnail(imageIn, false, 50, 50);
            SqlQuery proc       = new SqlQuery(@" 
insert into images (ImageData, ImageThumb, UserId)
OUTPUT inserted.ImageGUID 
values(@imageData,@imageThumb, @userId)
;
                ", 30, System.Data.CommandType.Text);

            proc.AddInputParam("ImageData", SqlDbType.Image, imageData);
            proc.AddInputParam("ImageThumb", SqlDbType.Image, imageThumb);
            proc.AddInputParam("UserId", SqlDbType.NVarChar, userId);
            return(await _queryExecutor.ExecuteAsync(proc, sqlReader => GetReturnValue <string>(sqlReader)));
        }
コード例 #11
0
        public async Task <byte[]> GetImageAsync(string imageGUID, bool isThumb)
        {
            string sql = " SELECT ImageData  FROM Images WHERE[ImageGUID] = @imageGUID;";

            if (isThumb)
            {
                sql = " SELECT ImageThumb as ImageData FROM Images WHERE[ImageGUID] = @imageGUID;";
            }
            SqlQuery proc = new SqlQuery(sql, 30, System.Data.CommandType.Text);



            proc.AddInputParam("imageGUID", SqlDbType.VarChar, imageGUID);


            var procedureResult = await _queryExecutor.ExecuteAsync(proc, GetImageAsyncResult);

            return(procedureResult);
        }
コード例 #12
0
        //TODO add user from Cognito

        public async Task <User> GetUserInfo(int userId)
        {
            SqlQuery proc = new SqlQuery(@" 
                select * from users 
                where UserId=@userId;
                ", 30, System.Data.CommandType.Text);

            proc.AddInputParam("userId", SqlDbType.Int, userId);
            return(await _queryExecutor.ExecuteAsync(proc, dataReader =>
            {
                while (dataReader.Read())
                {
                    return new User()
                    {
                        UserId = SqlQueryResultParser.GetValue <Int32>(dataReader, "userId"),
                        Role = (UserType)SqlQueryResultParser.GetValue <Int32>(dataReader, "userTypeId"),
                        IsActive = SqlQueryResultParser.GetValue <Boolean>(dataReader, "isActive"),
                        Email = SqlQueryResultParser.GetValue <String>(dataReader, "email")
                    };
                }
                return null;
            }));
        }
コード例 #13
0
        public async Task <TeamAdminResponse> GetTeamInfo(int teamId)
        {
            SqlQuery proc = new SqlQuery(@" 
                select 
                    t.teamId, 
                    t.name, 
                    t.AdminUserId, 
                    t.IsTrial,
                    t.IsHomePay, 
                    t.CreditNotes, 
                    u.Email, 
                    tc.Credits 
                from team t
                inner join users u on t.AdminUserId=u.UserId 
                left join team_credit tc on tc.TeamId=t.teamId
                where t.teamId=@teamId;
                ", 30, System.Data.CommandType.Text);

            proc.AddInputParam("teamId", SqlDbType.Int, teamId);
            return(await _queryExecutor.ExecuteAsync(proc, dataReader =>
            {
                while (dataReader.Read())
                {
                    return (new TeamAdminResponse()
                    {
                        TeamId = SqlQueryResultParser.GetValue <Int32>(dataReader, "TeamId"),
                        AdminEmail = SqlQueryResultParser.GetValue <String>(dataReader, "Email"),
                        AdminUserId = SqlQueryResultParser.GetValue <Int32>(dataReader, "AdminUserId"),
                        TeamName = SqlQueryResultParser.GetValue <String>(dataReader, "Name"),
                        IsTrial = SqlQueryResultParser.GetValue <Boolean>(dataReader, "IsTrial")
                    });
                }

                return null;
            }));
        }
コード例 #14
0
        public async Task <List <TeamInfo> > GetAllTeams(string search, bool sortasc, TeamSort sortby, int limit)
        {
            string whereSql = "";

            if (!string.IsNullOrEmpty(search))
            {
                whereSql = " where Name like @search or u.email like @search";
            }
            string selectSql = @"
                    t.*,
                    tc.credits, 
                    u.email, 
                    u.Created 
                ";
            string sortbySql;

            switch (sortby)
            {
            case TeamSort.Updated:
                sortbySql = "t.Updated";

                break;

            case TeamSort.TeamName:
                sortbySql = "t.Name";
                break;

            default:
                throw new ArgumentOutOfRangeException("sortby argument is invalid");
            }

            string sortAscSql = "";

            if (!sortasc)
            {
                sortAscSql = " desc ";
            }
            List <TeamInfo> teams = new List <TeamInfo>();
            SqlQuery        proc  = new SqlQuery($@" 
                select top {limit} {selectSql}
                from team t 
                inner join users u on u.userId=t.adminUserId
                left outer join team_credit tc on tc.TeamId=t.TeamId {whereSql} 
                order by {sortbySql} {sortAscSql}", 30, System.Data.CommandType.Text);

            proc.AddInputParam("@search", SqlDbType.NVarChar, "%" + search + "%", false);
            await _queryExecutor.ExecuteAsync(proc, dataReader =>
            {
                while (dataReader.Read())
                {
                    TeamInfo tm = new TeamInfo()
                    {
                        TeamId      = SqlQueryResultParser.GetValue <Int32>(dataReader, "TeamId"),
                        TeamName    = SqlQueryResultParser.GetValue <String>(dataReader, "Name"),
                        AdminEmail  = SqlQueryResultParser.GetValue <String>(dataReader, "Email"),
                        Created     = SqlQueryResultParser.GetValue <DateTime>(dataReader, "Created"),
                        Updated     = SqlQueryResultParser.GetValue <DateTime>(dataReader, "Updated"),
                        IsTrial     = SqlQueryResultParser.GetValue <bool>(dataReader, "IsTrial"),
                        AdminUserId = SqlQueryResultParser.GetValue <Int32>(dataReader, "AdminUserId")
                    };

                    teams.Add(tm);
                }
                return(teams);
            });

            return(teams);
        }
コード例 #15
0
        public async Task <List <TeamInfo> > GetTeamInfoList(string searchName, string searchEmail, bool sortAsc, TeamSort teamSort)
        {
            string whereName = "";

            if (!string.IsNullOrEmpty(searchName))
            {
                whereName = " where Name like @searchName ";
            }
            string whereEmail = "";

            if (!string.IsNullOrEmpty(searchEmail))
            {
                whereEmail = " where u.email like @searchEmail ";
            }

            string sortSql = " t.Name ";

            if (teamSort == TeamSort.TeamName)
            {
                sortSql = " t.Name ";
            }
            else if (teamSort == TeamSort.Updated)
            {
                sortSql = " t.Updated ";
            }

            string sortAscSql = "";

            if (!sortAsc)
            {
                sortAscSql = " desc ";
            }
            List <TeamInfo> lt   = new List <TeamInfo>();
            SqlQuery        proc = new SqlQuery($@" 
                select top 50 t.*, tc.credits, u.email, u.Created
                from team t 
                inner join users u on u.userId=t.adminUserId
                left outer join team_credit tc on tc.TeamId=t.TeamId {whereEmail}  {whereName} 
                order by {sortSql} {sortAscSql}", 30, System.Data.CommandType.Text);


            proc.AddInputParam("@searchName", SqlDbType.NVarChar, "%" + searchName + "%", false);
            proc.AddInputParam("@searchEmail", SqlDbType.NVarChar, "%" + searchEmail + "%", false);
            await _queryExecutor.ExecuteAsync(proc, dataReader =>
            {
                while (dataReader.Read())
                {
                    TeamInfo tm = new TeamInfo()
                    {
                        TeamId      = SqlQueryResultParser.GetValue <Int32>(dataReader, "TeamId"),
                        TeamName    = SqlQueryResultParser.GetValue <String>(dataReader, "Name"),
                        AdminEmail  = SqlQueryResultParser.GetValue <String>(dataReader, "Email"),
                        Created     = SqlQueryResultParser.GetValue <DateTime>(dataReader, "Created"),
                        Updated     = SqlQueryResultParser.GetValue <DateTime>(dataReader, "Updated"),
                        IsTrial     = SqlQueryResultParser.GetValue <bool>(dataReader, "IsTrial"),
                        AdminUserId = SqlQueryResultParser.GetValue <Int32>(dataReader, "AdminUserId")
                    };
                    lt.Add(tm);
                }
                return(lt);
            });

            return(lt);
        }