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;
}));
}
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);
}
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);
}
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);
}
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;
}));
}
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)));
}
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;
}));
}
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)));
}
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);
}
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)));
}
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);
}
//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;
}));
}
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;
}));
}
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);
}
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);
}