//method to update a Sag
private void btn_OpdaterSag_Click(object sender, EventArgs e)
{
//Initializes SagBLL and SagDAL
sag = new SagBLL(SagsID(), SagsStatus(), SagsBoligID(), SagsSælgerID(), SagsMæglerID());
try
{
if (SagBLL.SagExists(SagsID()))
{
//updates a Sag record
sag.OpdaterSag(sag);
}
else
{
MessageBox.Show("Der findes ikke nogen bolig i database med dette ID. Prøv venligst med en anden ID.");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//if Sag is beign closed because a house has been sold
if (sagStatus_cbox.SelectedItem.ToString() == "Lukket (solgt bolig)" && !HandelBLL.HandelExists(SagsID()))
{
//user must create a new Handle
MenuBarKnapper.HandlenCreate(SagsID().ToString());
}
//Loader data fra databasen ind i datagridview
SagUI_Load(sender, e);
//disable all TextBoxes
DisableAll();
}
//method to delete a Sag from DB
private void btn_SletSag_Click(object sender, EventArgs e)
{
//Initializes SagBLL and SagDAL
sag = new SagBLL(SagsID());
try
{
if (SagBLL.SagExists(SagsID()))
{
//delete a Sag from DB
sag.SletSag(sag);
}
else
{
MessageBox.Show("Der findes ikke nogen bolig i database med dette ID. Prøv venligst med en anden ID.");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//Loader data fra databasen ind i datagridview
SagUI_Load(sender, e);
//clear all TextBoxes
ClearAll();
//disable all TextBoxes
DisableAll();
//enable BoligID TextBox
sagID_txt.Enabled = true;
}
protected virtual void DisplaySag(List <SagBLL> sager)
{
//CLEAR OUTPUT EVERY TIME
//so that we don't have an infinite list
//output.Items.Clear();
if (output.Items.Count == 0)
{
foreach (SagBLL sag in sager)
{
output.Items.Add(sag.ToString());
}
}
else
{
SagBLL lastIndexItem = SagBLL.FromString(output.Items[output.Items.Count - 1].ToString());
//FOREACH ITEM IN THE LIST
//ADD ITEM TO OUTPUT
foreach (SagBLL sag in sager)
{
if (sag.SagsID > lastIndexItem.SagsID)
{
output.Items.Add(sag.ToString());
}
}
}
}
//method to retrieve a Sag from DB and show its attributes on TextBoxes
private void btn_HentSag_Click(object sender, EventArgs e)
{
//Initializes SagBLL and SagDAL
sag = new SagBLL(SagsID());
try
{
if (SagBLL.SagExists(SagsID()))
{
//retrieve a SagBLL from DB using SagsID
SagBLL matchingesag = SagBLL.HentSagViaID(sag);
//shows retrieved Sag from DB on TextBoxes
sagStatus_cbox.Text = matchingesag.Status.ToString();
sag_boligID_cbox.Text = matchingesag.BoligID.ToString();
sag_sælgerID_cbox.Text = matchingesag.SælgerID.ToString();
sag_ejendomsmæglerID_cbox.Text = matchingesag.MæglerID.ToString();
}
else
{
MessageBox.Show("Der findes ikke nogen bolig i database med dette ID. Prøv venligst med en anden ID.");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
sagID_txt.Enabled = false;
}
public void LukSag(SagBLL sag)
{
string sqlCommand = "UPDATE Sag SET Status = 'Lukket (solgt bolig)' WHERE SagsID = @SagsID";
SqlCommand cmd = new SqlCommand(sqlCommand, conn);
cmd.Parameters.AddWithValue("@SagsID", sag.SagsID);
try
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
Transactions.BeginReadCommittedTransaction(conn);
cmd.ExecuteNonQuery();
if (!Transactions.Commit(conn))
{
Transactions.Rollback(conn);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
public static SagBLL HentSagViaID(SagBLL sagToFind)
{
SagBLL matchingsag = null;
string sqlCommandSag = "SELECT * FROM Sag WHERE SagsID = @SagsID";
SqlCommand commandSag = new SqlCommand(sqlCommandSag, conn);
commandSag.Parameters.AddWithValue("@SagsID", sagToFind.SagsID);
try
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
Transactions.BeginReadCommittedTransaction(conn);
using (SqlDataReader reader = commandSag.ExecuteReader())
{
while (reader.Read())
{
matchingsag = new SagBLL((int)reader["SagsID"],
reader["Status"].ToString(),
(int)reader["BoligID"],
(int)reader["SælgerID"],
(int)reader["MæglerID"]);
}
//CLOSE READER
if (reader != null)
{
reader.Close();
}
}
//COMMIT OR ROLLBACK
if (!Transactions.Commit(conn))
{
Transactions.Rollback(conn);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
//CLOSE CONNECTION
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
//RETURN
return(matchingsag);
}
//method to create a new Sag
private void btn_OpretSag_Click(object sender, EventArgs e)
{
//Initializes SagBLL and SagDAL
sag = new SagBLL(SagsStatus(), SagsBoligID(), SagsSælgerID(), SagsMæglerID());
try
{
if (!SagBLL.BoligExistsISag(SagsBoligID()))
{
//creates a new SagBLL in DB
sag.OpretSag(sag);
//retrieves Sags ID from DB
SagBLL matchingsag = SagBLL.HentSag(sag);
//show SagsID in TextBox
sagID_txt.Text = matchingsag.SagsID.ToString();
}
else
{
MessageBox.Show("Der findes allerede en sag med denne bolig ID. Vælg venligst en anden bolig.");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//save SagsID in a string
string sagsid = null;
if (sagID_txt != null)
{
sagsid = sagID_txt.Text;
}
//if Sag is beign closed because a house has been sold
if (sagStatus_cbox.SelectedItem.ToString() == "Lukket (solgt bolig)")
{
//user must create a new Handle
MenuBarKnapper.HandlenCreate(sagsid);
}
//Loader data fra databasen ind i datagridview
SagUI_Load(sender, e);
//disable alle TextBoxes
DisableAll();
}
public void OpdaterSag(SagBLL sag)
{
//Tjekker om tekstboxe var tomme og undlader at opdaterer informationer for dem der er tomme
string sqlCommandSag = "UPDATE Sag SET " +
"Status = IsNull(NullIf(@Status, ''), Status), " +
"BoligID = IsNull(NullIf(@BoligID, ''), BoligID), " +
"SælgerID = IsNull(NullIf(@SælgerID, ''), SælgerID), " +
"MæglerID = IsNull(NullIf(@MæglerID, ''), MæglerID) " +
"WHERE SagsID = @SagsID";
//Sender input til database for at opdatere
SqlCommand commandSag = new SqlCommand(sqlCommandSag, conn);
commandSag.Parameters.AddWithValue("@Status", sag.Status);
commandSag.Parameters.AddWithValue("@BoligID", sag.BoligID);
commandSag.Parameters.AddWithValue("@SælgerID", sag.SælgerID);
commandSag.Parameters.AddWithValue("@MæglerID", sag.MæglerID);
commandSag.Parameters.AddWithValue("@SagsID", sag.SagsID);
try
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
Transactions.BeginReadCommittedTransaction(conn);
commandSag.ExecuteNonQuery();
if (!Transactions.Commit(conn))
{
Transactions.Rollback(conn);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
private void btn_oprethandel_Click(object sender, EventArgs e)
{
handel = new HandelBLL(HandelID(), Handelsdato(), HandelSalgspris(), HandelSagsID(), HandelKøberID());
sagBLL = new SagBLL(HandelSagsID());
try
{
if (HandelBLL.HandelExists(HandelSagsID()))
{
MessageBox.Show("En faktura (handel) for denne sag har allerede været oprettet.");
}
else if (!SagBLL.SagExists(HandelSagsID()))
{
MessageBox.Show("Denne SagsID kan desværre ikke findes i databasen. Prøv venligst igen!");
}
else
{
//OpretHandel
handel.OpretHandel(handel);
//retireve HandelID from DB
HandelBLL matchinghandel = HandelBLL.FindHandel(handel);
handelID_txt.Text = matchinghandel.HandelID.ToString();
//Luk sagen
if (SagBLL.SagExists(HandelSagsID()))
{
sagBLL.LukSag(sagBLL);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//Loader data fra databasen ind i datagridview -
HandelUI_Load(sender, e);
//disable all TextBoxes
DisableAll();
}
public void SletSag(SagBLL sag)
{
string sqlCommandSag = $"DELETE FROM Sag WHERE SagsID = @SagsID";
SqlCommand commandSag = new SqlCommand(sqlCommandSag, conn);
commandSag.Parameters.AddWithValue("@SagsID", sag.SagsID);
try
{
//OPEN CONNECTION
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
//BEGIN TRANSACTION
Transactions.BeginRepeatableReadTransaction(conn);
//EXECUTE QUERY
commandSag.ExecuteNonQuery();
//COMMIT OR ROLLBACK
if (!Transactions.Commit(conn))
{
Transactions.Rollback(conn);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
//CLOSE CONNECTION
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
public void OpretSag(SagBLL sag)
{
string sqlCommandSag = $"INSERT INTO Sag VALUES(@Status, @BoligID, @SælgerID, @MæglerID)";
SqlCommand commandSag = new SqlCommand(sqlCommandSag, conn);
commandSag.Parameters.AddWithValue("@Status", sag.Status);
commandSag.Parameters.AddWithValue("@BoligID", sag.BoligID);
commandSag.Parameters.AddWithValue("@SælgerID", sag.SælgerID);
commandSag.Parameters.AddWithValue("@MæglerID", sag.MæglerID);
try
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
Transactions.BeginRepeatableReadTransaction(conn);
commandSag.ExecuteNonQuery();
if (!Transactions.Commit(conn))
{
Transactions.Rollback(conn);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
private void beregn_hentData_btn_Click(object sender, EventArgs e)
{
try
{
if (SagBLL.SagExists(SagsID()))
{
ejendomsmægler = WorksOnDAL.HentMægler(SagsID());
}
if (ejendomsmægler != null)
{
beregn_mæglerID_txt.Text = ejendomsmægler.ToString();
}
else
{
MessageBox.Show("Husk at lukke sagen, før at beregne din salær!" +
"\nEller, prøv at vælge en sag fra boxen \"SagsID\"...");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static SagBLL HentSag(SagBLL sagToFind)
{
//initialize SagBLL matchingsag
SagBLL matchingsag = null;
//SQL QUERY
string sqlCommandSag = "SELECT * FROM Sag WHERE " +
"SagsID LIKE @SagsID OR " +
"Status LIKE @Status AND " +
"BoligID LIKE @BoligID AND " +
"SælgerID LIKE @SælgerID AND " +
"MæglerID LIKE @MæglerID ";
//SQL COMMAND + PARAMETERS
SqlCommand cmdSag = new SqlCommand(sqlCommandSag, conn);
cmdSag.Parameters.AddWithValue("@SagsID", sagToFind.SagsID);
cmdSag.Parameters.AddWithValue("@Status", sagToFind.Status);
cmdSag.Parameters.AddWithValue("@BoligID", sagToFind.BoligID);
cmdSag.Parameters.AddWithValue("@SælgerID", sagToFind.SælgerID);
cmdSag.Parameters.AddWithValue("@MæglerID", sagToFind.MæglerID);
try
{
//OPEN CONNECTION
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
//BEGIN TRANSACTION
Transactions.BeginReadCommittedTransaction(conn);
//EXECUTE READER (QUERY)
using (SqlDataReader reader = cmdSag.ExecuteReader())
{
//RETRIEVE SagBLL AND SAVE IN matchingsag
while (reader.Read())
{
matchingsag = new SagBLL((int)reader["SagsID"],
reader["Status"].ToString(),
(int)reader["BoligID"],
(int)reader["SælgerID"],
(int)reader["MæglerID"]);
}
//CLOSE READER
if (reader != null)
{
reader.Close();
}
}
//COMMIT OR ROLLBACK
if (!Transactions.Commit(conn))
{
Transactions.Rollback(conn);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
//CLOSE CONNECTION
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
//RETURN
return(matchingsag);
}
