public bool InsertLike(AccountBijdrage accountBijdrage)
{
try
{
using (SqlConnection connection = Connection.SQLconnection)
{
string query = "insertLike";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@accountID", accountBijdrage.AccountID);
command.Parameters.AddWithValue("@bijdrageID", accountBijdrage.BijdrageID);
command.Parameters.AddWithValue("@like", accountBijdrage.Like);
command.Parameters.AddWithValue("@ongewenst", accountBijdrage.Ongenwenst);
command.ExecuteNonQuery();
}
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public List <Bijdrage> GetAllReportedBijdrages()
{
List <Bijdrage> bijdrageList = new List <Bijdrage>();
using (SqlConnection connection = Connection.SQLconnection)
{
string query =
"SELECT c.bijdrage_id, c.naam, b.*, be.*, br.*, a.*, ab.* FROM ACCOUNT_BIJDRAGE ab " +
"LEFT JOIN BIJDRAGE b on ab.bijdrage_id = b.ID LEFT JOIN CATEGORIE c on b.ID = c.bijdrage_id " +
"LEFT JOIN BESTAND be on b.ID = be.bijdrage_id LEFT JOIN BERICHT br on b.ID = br.bijdrage_id " +
"LEFT JOIN ACCOUNT a on b.account_id = a.ID " +
"WHERE ab.ongewenst >= 1";
using (SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Bijdrage bijdrage = CreateBijdrageFromReader(reader, connection);
bijdrageList.Add(bijdrage);
}
}
}
foreach (Bijdrage bijdrage in bijdrageList)
{
string query2 =
"SELECT ab.* FROM ACCOUNT_BIJDRAGE ab LEFT JOIN bijdrage b ON ab.bijdrage_id = b.ID";
using (SqlCommand command = new SqlCommand(query2, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string ab = reader["id"].ToString();
string ab1 = reader["account_id"].ToString();
string ab2 = reader["bijdrage_id"].ToString();
string ab3 = reader["like"].ToString();
string ab4 = reader["ongewenst"].ToString();
if (bijdrage.Id == Convert.ToInt32(ab2))
{
AccountBijdrage accountBijdrage1 = new AccountBijdrage(Convert.ToInt32(ab), Convert.ToInt32(ab1),
Convert.ToInt32(ab2), Convert.ToInt32(ab3),
Convert.ToInt32(ab4));
bijdrage.AccountBijdrage.Add(accountBijdrage1);
}
else
{
continue;
}
}
}
}
}
}
return(bijdrageList);
}
public List <Bijdrage> GetAllBijdragesByUserId(int userid)
{
List <Bijdrage> bijdrageList = new List <Bijdrage>();
using (SqlConnection connection = Connection.SQLconnection)
{
string query =
"SELECT * FROM BIJDRAGE b " +
"LEFT JOIN CATEGORIE c on b.ID = c.bijdrage_id " +
"LEFT JOIN BESTAND be on b.ID = be.bijdrage_id " +
"LEFT JOIN BERICHT br on b.ID = br.bijdrage_id " +
"LEFT JOIN ACCOUNT acc on b.account_id = acc.ID " +
"WHERE acc.ID = @userid";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@userid", userid);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Bijdrage bijdrage = CreateBijdrageFromReader(reader, connection);
bijdrageList.Add(bijdrage);
}
}
}
foreach (Bijdrage bijdrage in bijdrageList)
{
string query2 =
"SELECT ab.* FROM ACCOUNT_BIJDRAGE ab LEFT JOIN bijdrage b ON ab.bijdrage_id = b.ID";
using (SqlCommand command = new SqlCommand(query2, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string ab = reader["id"].ToString();
string ab1 = reader["account_id"].ToString();
string ab2 = reader["bijdrage_id"].ToString();
string ab3 = reader["like"].ToString();
string ab4 = reader["ongewenst"].ToString();
if (bijdrage.Id == Convert.ToInt32(ab2))
{
AccountBijdrage accountBijdrage1 = new AccountBijdrage(Convert.ToInt32(ab), Convert.ToInt32(ab1),
Convert.ToInt32(ab2), Convert.ToInt32(ab3),
Convert.ToInt32(ab4));
bijdrage.AccountBijdrage.Add(accountBijdrage1);
}
else
{
continue;
}
}
}
}
}
}
return(bijdrageList);
}
public List <Bericht> LoadBerichtenByPostId(int id)
{
List <Bericht> berichtenList = new List <Bericht>();
using (SqlConnection connection = Connection.SQLconnection)
{
string query =
"select * from BERICHT b " +
"left join BIJDRAGE_BERICHT bb on bb.bericht_id = b.bijdrage_id left join BIJDRAGE bd on b.bijdrage_id = bd.ID where bb.bijdrage_id = @id";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@id", id);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Bericht bericht = CreateBerichtFromReader(reader);
berichtenList.Add(bericht);
}
}
}
foreach (Bericht bericht in berichtenList)
{
string query2 =
"SELECT ab.* FROM ACCOUNT_BIJDRAGE ab LEFT JOIN bijdrage b ON ab.bijdrage_id = b.ID";
using (SqlCommand command = new SqlCommand(query2, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string ab = reader["id"].ToString();
string ab1 = reader["account_id"].ToString();
string ab2 = reader["bijdrage_id"].ToString();
string ab3 = reader["like"].ToString();
string ab4 = reader["ongewenst"].ToString();
if (bericht.Id == Convert.ToInt32(ab2))
{
AccountBijdrage accountBijdrage1 = new AccountBijdrage(Convert.ToInt32(ab), Convert.ToInt32(ab1),
Convert.ToInt32(ab2), Convert.ToInt32(ab3),
Convert.ToInt32(ab4));
bericht.AccountBijdrage.Add(accountBijdrage1);
}
else
{
continue;
}
}
}
}
}
}
return(berichtenList);
}
public bool InsertLike(AccountBijdrage accountBijdrage)
{
return(context.InsertLike(accountBijdrage));
}
private Bijdrage CreateBijdrageFromReader(SqlDataReader reader)
{
AccountRepository accRepo = new AccountRepository(new AccountSQLContext());
if (reader["soort"].ToString() == "bestand")
{
{ Account account = accRepo.GetAccountById(Convert.ToInt32(reader["acccount_id"]));
AccountBijdrage accBijdrage;
try
{
accBijdrage = new AccountBijdrage(Convert.ToInt32(reader["account_id"]), Convert.ToInt32(reader["bijdrage_id"]), Convert.ToInt32(reader["like"]), Convert.ToInt32(reader["ongewenst"]));
}
catch (Exception e)
{
accBijdrage = new AccountBijdrage(0, 0, 0, 0, 0);
}
return(new Bestand(
Convert.ToInt32(reader["ID"]),
account,
Convert.ToDateTime(reader["datum"]),
Convert.ToString(reader["soort"]),
Convert.ToInt32(reader["categorie_id"] != DBNull.Value ? Convert.ToInt32(reader["categorie_id"]) : 0),
Convert.ToString(reader["bestandslocatie"] != DBNull.Value ? Convert.ToString(reader["bestandslocatie"]) : ""),
Convert.ToInt32(reader["grootte"] != DBNull.Value ? Convert.ToInt32(reader["grootte"]) : 0),
accBijdrage
)); }
}
if (reader["soort"].ToString() == "categorie")
{
{
Account account = accRepo.GetAccountById(Convert.ToInt32(reader["acccount_id"]));
AccountBijdrage accBijdrage;
try
{
accBijdrage = new AccountBijdrage(Convert.ToInt32(reader["account_id"]),
Convert.ToInt32(reader["bijdrage_id"]), Convert.ToInt32(reader["like"]),
Convert.ToInt32(reader["ongewenst"]));
}
catch (Exception e)
{
accBijdrage = new AccountBijdrage(0, 0, 0, 0, 0);
}
return(new Categorie(
Convert.ToInt32(reader["ID"]),
account,
Convert.ToDateTime(reader["datum"]),
Convert.ToString(reader["soort"]),
Convert.ToInt32(reader["categorie_id"] != DBNull.Value
? Convert.ToInt32(reader["categorie_id"])
: 0),
Convert.ToString(reader["naam"]),
accBijdrage
));
}
}
if (reader["soort"].ToString() == "bericht")
{
{ Account account = accRepo.GetAccountById(Convert.ToInt32(reader["acccount_id"]));
AccountBijdrage accBijdrage;
try
{
accBijdrage = new AccountBijdrage(Convert.ToInt32(reader["account_id"]), Convert.ToInt32(reader["bijdrage_id"]), Convert.ToInt32(reader["like"]), Convert.ToInt32(reader["ongewenst"]));
}
catch (Exception e)
{
accBijdrage = new AccountBijdrage(0, 0, 0, 0, 0);
}
return(new Bericht(
Convert.ToInt32(reader["ID"]),
account,
accBijdrage,
Convert.ToDateTime(reader["datum"]),
Convert.ToString(reader["soort"]),
Convert.ToString(reader["titel"]),
Convert.ToString(reader["inhoud"])
)); }
}
else
{
return(null);
}
}
