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 <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 <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;
}));
}
private byte[] GetImageAsyncResult(IDataReader oReader)
{
while (oReader.Read())
{
return(SqlQueryResultParser.GetValue <byte[]>(oReader, "ImageData"));
}
return(new Byte[] { });
}
/// <summary>
/// Returns single column and row value.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="oReader"></param>
/// <param name="isResultRequired"></param>
/// <returns></returns>
protected T GetReturnValue <T>(IDataReader oReader, bool isResultRequired = true)
{
T val = default(T);
if (oReader.Read())
{
val = SqlQueryResultParser.GetReturnValue <T>(oReader, isResultRequired: isResultRequired);
}
return(val);
}
public async Task <List <User> > GetUserList()
{
SqlQuery proc = new SqlQuery(@"
select userId, userTypeId, isActive, email from users;
", 30, System.Data.CommandType.Text);
return(await _queryExecutor.ExecuteAsync(proc, dataReader =>
{
List <User> users = new List <User>();
while (dataReader.Read())
{
users.Add(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 users;
}));
}
//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);
}
private string AddImageAsyncResult(IDataReader oReader)
{
oReader.Read();
return(SqlQueryResultParser.GetValue <string>(oReader, "imageGUID"));
}
