public IEnumerable <Post> GetFeedByUserId(Guid userId)
{
var sql = @"SELECT * FROM TB_POST
WHERE
TB_POST.ID_USER = @userId OR
TB_POST.ID_USER in
(
SELECT ID_SENDER from TB_INVITE
INNER JOIN TB_INVITE_STATUS on TB_INVITE.ID_INVITE_STATUS = TB_INVITE_STATUS.ID
WHERE DS_INVITE_STATUS = 'Aceito' AND ID_RECEIVER = @userId
UNION
SELECT ID_RECEIVER from TB_INVITE
INNER JOIN TB_INVITE_STATUS on TB_INVITE.ID_INVITE_STATUS = TB_INVITE_STATUS.ID
WHERE DS_INVITE_STATUS = 'Aceito' AND ID_SENDER = @userId
)
ORDER BY DT_UPDATED DESC";
using (var cn = SqlConnectionFactory.Create())
{
var result = cn.Query <Post>(sql, new { userId });
return(result);
}
}
public async Task <User> Get(int id)
{
using (var conn = await SqlConnectionFactory.Create())
{
return(await conn.GetUser(id));
}
}
public Midia GetAvatarByUserId(Guid userId)
{
var sql = @"SELECT
TB_MIDIA.*,
TB_MIDIA_TYPE.*
FROM TB_MIDIA
INNER JOIN TB_USER ON TB_USER.ID_AVATAR = TB_MIDIA.ID
INNER JOIN TB_MIDIA_TYPE ON TB_MIDIA.ID_MIDIA_TYPE = TB_MIDIA_TYPE.ID
WHERE TB_USER.ID = @userId ";
using (var cn = SqlConnectionFactory.Create())
{
return(cn.Query <Midia, MidiaType, Midia>(sql, (midia, midiatype) =>
{
midia.MidiaType = midiatype;
midia.MidiaTypeId = midiatype.Id;
return midia;
},
splitOn: "ID",
param: new { userId }
)
.First());
}
}
public Gallery GetFeedByUserId(Guid userId)
{
var sql = @"SELECT
TB_GALLERY.*,
TB_MIDIA.*,
TB_MIDIA_TYPE.*
FROM TB_MIDIA
INNER JOIN TB_MIDIA_GALLERY ON TB_MIDIA.ID = TB_MIDIA_GALLERY.ID_MIDIA
INNER JOIN TB_GALLERY ON TB_GALLERY.ID = TB_MIDIA_GALLERY.ID_GALLERY
INNER JOIN TB_USER ON TB_GALLERY.ID_USER = TB_USER.ID
INNER JOIN TB_MIDIA_TYPE ON TB_MIDIA_TYPE.ID = TB_MIDIA.ID_MIDIA_TYPE
WHERE
TB_USER.ID = @userId AND
TB_GALLERY.NM_GALLERY = 'Feed'
ORDER BY TB_MIDIA.DT_CREATED";
var parameters = new { userId = userId.ToString() };
using (var cn = SqlConnectionFactory.Create())
{
return(cn.Query <Gallery, Midia, MidiaType, Gallery>(sql, (gallery, midia, midiaType) =>
{
midia.MidiaType = midiaType;
gallery.Midias.Add(midia);
return gallery;
},
splitOn: "ID",
param: parameters).First());
}
}
public async Task <List <User> > Get()
{
using (var conn = await SqlConnectionFactory.Create())
{
return(await conn.GetAllUsers());
}
}
public override User Get(Guid id)
{
var sql = @"SELECT
TB_USER.*,
TB_GENDER.*,
AVATAR.*,
COVER.*
FROM TB_USER
INNER JOIN TB_GENDER ON TB_USER.ID_GENDER = TB_GENDER.ID
INNER JOIN TB_MIDIA AS AVATAR ON TB_USER.ID_AVATAR = AVATAR.ID
LEFT JOIN TB_MIDIA AS COVER ON TB_USER.ID_COVER = COVER.ID
WHERE TB_USER.ID = @id";
using (var cn = SqlConnectionFactory.Create())
{
return(cn.Query <User, Gender, Midia, Midia, User>(sql, (user, gender, avatar, cover) =>
{
user.Gender = gender;
user.Avatar = avatar;
user.Cover = cover;
return user;
},
splitOn: "ID",
param: new { id }).First());
}
}
public async Task Post(int id, [FromBody] User user)
{
using (var conn = await SqlConnectionFactory.Create())
{
user.Id = id;
await conn.UpdateUser(user);
}
}
public async Task <IActionResult> Get(int id)
{
using (var conn = await SqlConnectionFactory.Create())
{
var profilePic = await conn.GetProfilePic(id);
return(File(profilePic.Pic, profilePic.Extension.AsContentType()));
}
}
public void Create_NoArgument_CreatesDefaultWrapper()
{
// Arrange
var factory = new SqlConnectionFactory();
// Act
var actualSqlConnection = factory.Create();
// Assert
Assert.IsNotNull(actualSqlConnection);
Assert.IsNotNull(actualSqlConnection.SqlConnectionInstance);
}
public void CreatesSqlConnectionWithSpecifiedConnectionString()
{
// Arrange
var sut = new SqlConnectionFactory(DatabaseFixture.LogEventsConnectionString, false, _azureManagedServiceAuthenticatorMock.Object);
// Act
using (var connection = sut.Create())
{
// Assert
Assert.Equal(DatabaseFixture.LogEventsConnectionString, connection.ConnectionString);
}
}
public void CreateCallsAzureManagedServiceAuthenticator()
{
// Arrange
var sut = new SqlConnectionFactory(DatabaseFixture.LogEventsConnectionString, _azureManagedServiceAuthenticatorMock.Object);
// Act
using (var connection = sut.Create())
{
// Assert
_azureManagedServiceAuthenticatorMock.Verify(a => a.SetAuthenticationToken(connection), Times.Once);
}
}
public void Create_ExistingSqlConnectionObject_WrapsTheObject()
{
// Arrange
var expectedSqlConnection = new SqlConnection();
var factory = new SqlConnectionFactory();
// Act
var actualSqlConnection = factory.Create(expectedSqlConnection);
// Assert
Assert.IsNotNull(actualSqlConnection);
Assert.AreSame(expectedSqlConnection, actualSqlConnection.SqlConnectionInstance);
}
public void CreateWithUseAzureManagedIdentitiesFalseDoesNotCallAzureManagedServiceAuthenticator()
{
// Arrange
var sut = new SqlConnectionFactory(DatabaseFixture.LogEventsConnectionString, false, _azureManagedServiceAuthenticatorMock.Object);
// Act
using (var connection = sut.Create())
{
}
// Assert
_azureManagedServiceAuthenticatorMock.Verify(a => a.GetAuthenticationToken(), Times.Never);
}
public void Create_ConnectionStringArgument_CreatesSqlConnection()
{
// Arrange
var expectedConnectionString = "Server=localhost;Database=db1;Trusted_Connection=True";
var factory = new SqlConnectionFactory();
// Act
var actualSqlConnection = factory.Create(expectedConnectionString);
// Assert
Assert.IsNotNull(actualSqlConnection);
Assert.AreEqual(expectedConnectionString, actualSqlConnection.ConnectionString);
}
public void ReturnSqlConnection()
{
//Arrange
var connectionString = "Server=server;Database=database;User Id=user;Password=password";
var connectionStringFactory = new ConnectionStringFactory(connectionString);
var connectionFactory = new SqlConnectionFactory(connectionStringFactory);
//Act
var result = connectionFactory.Create();
//Assert
Assert.IsInstanceOfType(result, typeof(SqlConnection));
}
public void SetsEnlistTrueOnConnectionStringIfEnlistTransactionTrue()
{
// Arrange
var sut = new SqlConnectionFactory(DatabaseFixture.LogEventsConnectionString, true, false,
_sqlConnectionStringBuilderWrapperMock.Object, _azureManagedServiceAuthenticatorMock.Object);
// Act
using (var connection = sut.Create())
{ }
// Assert
_sqlConnectionStringBuilderWrapperMock.VerifySet(c => c.ConnectionString = DatabaseFixture.LogEventsConnectionString);
_sqlConnectionStringBuilderWrapperMock.VerifySet(c => c.Enlist = true);
}
public void CreateWithUseAzureManagedIdentitiesTrueCallsAzureManagedServiceAuthenticator()
{
// Arrange
var sut = new SqlConnectionFactory(DatabaseFixture.LogEventsConnectionString, true, true,
_sqlConnectionStringBuilderWrapperMock.Object, _azureManagedServiceAuthenticatorMock.Object);
// Act
using (var connection = sut.Create())
{
}
// Assert
_azureManagedServiceAuthenticatorMock.Verify(a => a.GetAuthenticationToken(), Times.Once);
}
public async Task Post(int id)
{
var httpRequest = HttpContext.Request;
var file = httpRequest.Form.Files[0];
var fileName = httpRequest.Form["filename"][0];
var ext = Path.GetExtension(fileName);
var stream = file.OpenReadStream();
var picByt = new byte[stream.Length];
using (BinaryReader br = new BinaryReader(stream))
{
picByt = br.ReadBytes((int)stream.Length);
}
using (var conn = await SqlConnectionFactory.Create())
{
await conn.SetProfilePic(id, picByt, ext);
}
}
private void ConfigureDatabase()
{
database = new SqlServerDatabase(SqlConnectionFactory.Create(Configuration));
}
public override Post Get(Guid id)
{
using (var cn = SqlConnectionFactory.Create())
{
var sql = @"SELECT
TB_POST.*
,TB_MIDIA.*
,TB_MIDIA_TYPE.*
,TB_LIKE.*
,LIKE_USER.*
,COMMENTS.*
,AUTHOR.*
FROM TB_POST
LEFT JOIN TB_POST_MIDIA ON TB_POST.ID = TB_POST_MIDIA.ID_POST
LEFT JOIN TB_MIDIA ON TB_POST_MIDIA.ID_MIDIA = TB_MIDIA.ID
LEFT JOIN TB_MIDIA_TYPE ON TB_MIDIA.ID_MIDIA_TYPE = TB_MIDIA_TYPE.ID
LEFT JOIN TB_LIKE ON TB_POST.ID = TB_LIKE.ID_POST
LEFT JOIN TB_USER ON TB_LIKE.ID_USER = TB_USER.ID
LEFT JOIN TB_USER AS LIKE_USER ON LIKE_USER.ID = TB_LIKE.ID_USER
LEFT JOIN TB_POST AS COMMENTS ON TB_POST.ID_PARENT = COMMENTS.ID
LEFT JOIN TB_USER AS AUTHOR ON TB_POST.ID_USER = AUTHOR.ID
LEFT JOIN TB_USER AS AUTHOR_COMMENT ON COMMENTS.ID_USER = AUTHOR_COMMENT.ID
WHERE TB_POST.ID = @id";
return(cn.Query <Post, Midia, MidiaType, Like, User, Post, User, Post>
(sql, (post, midia, midiatype, like, likeUser, comment, author) =>
{
if (midia != null)
{
midia.MidiaType = midiatype;
midia.MidiaTypeId = midiatype.Id;
post.Midias.Add(midia);
}
if (comment != null)
{
post.Comments.Add(comment);
}
if (like != null)
{
//likeUser.Cover = null;
//likeUser.CoverId = null;
//likeUser.Avatar = null;
like.User = likeUser;
like.UserId = likeUser.Id;
like.Post = null;
post.Likes.Add(like);
}
post.Author = author;
post.AuthorId = author.Id;
return post;
},
splitOn: "ID",
param: new { id })
.First());
}
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.Configure <CookiePolicyOptions>(options =>
{
// This lambda determines whether user consent for non-essential cookies is needed for a given request.
options.CheckConsentNeeded = context => true;
options.MinimumSameSitePolicy = SameSiteMode.None;
})
.AddMvc();
// Add identity types
services.AddIdentity <ChronicyUser, ChronicyRole>(config =>
{
#if DEBUG
config.SignIn.RequireConfirmedEmail = false;
#else
config.SignIn.RequireConfirmedEmail = true;
#endif
})
.AddDefaultTokenProviders();
// Add database
services.AddTransient <ISqlDatabase>(e => new SqlServerDatabase(SqlConnectionFactory.Create(Configuration)));
// Add automation services
services.AddTransient <IActivityManager, ActivityManager>();
services.AddTransient <IAutomationManager, AutomationManager>();
services.AddTransient <IStatisticsManager, StatisticsManager>();
// Identity Services
services.AddTransient <IUserStore <ChronicyUser>, UserStore>();
services.AddTransient <IRoleStore <ChronicyRole>, RoleStore>();
services.AddTransient <IEmailSender, EmailSender>(e => new EmailSender(Configuration.GetValue <string>(Settings.Email.Host),
new NetworkCredential
{
UserName = Configuration.GetValue <string>(Settings.Email.Username),
Password = Configuration.GetValue <string>(Settings.Email.Password)
}));
services.AddTransient <IEmailBuilder, ConfirmationEmailBuilder>();
services.Configure <IdentityOptions>(options =>
{
// Password settings
options.Password.RequireDigit = true;
options.Password.RequireLowercase = true;
options.Password.RequireNonAlphanumeric = true;
options.Password.RequireUppercase = true;
options.Password.RequiredLength = 8;
options.Password.RequiredUniqueChars = 1;
// Debug only options
options.Password.RequireDigit = false;
options.Password.RequireLowercase = false;
options.Password.RequireNonAlphanumeric = false;
options.Password.RequireUppercase = false;
options.Password.RequiredLength = 1;
options.Password.RequiredUniqueChars = 1;
// Lockout settings
options.Lockout.DefaultLockoutTimeSpan = TimeSpan.FromMinutes(10);
options.Lockout.MaxFailedAccessAttempts = 5;
options.Lockout.AllowedForNewUsers = true;
// User settings
options.User.RequireUniqueEmail = true;
});
services.ConfigureApplicationCookie(options =>
{
// Cookie settings
options.Cookie.HttpOnly = true;
options.ExpireTimeSpan = TimeSpan.FromMinutes(5);
options.LoginPath = WebsitePaths.Login;
options.AccessDeniedPath = WebsitePaths.AccessDenied;
options.SlidingExpiration = true;
});
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_3_0);
}
