public static DataView getViewEmployees(string agent)
{
DataView dataViewEmployees = new DataView();
DataTable dataTableEmployees = new DataTable();
SqlConnection connection = Remax.getConnection();
string viewStatement = "SELECT Employees.refEmployee as Id,Employees.Name,Employees.Phone,Employees.Email,Employees.Gender,Employees.Address,Employees.Image,Employees.Role,Employees.Password FROM Employees ";
try
{
connection.Open();
SqlCommand selectCommand = new SqlCommand(viewStatement, connection);
SqlDataReader employeeReader = selectCommand.ExecuteReader();
dataTableEmployees.Load(employeeReader);
dataViewEmployees = dataTableEmployees.DefaultView;
if (agent != "")
{
dataViewEmployees.RowFilter = "Role='Agent'";
}
return(dataViewEmployees);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static DataView getViewClients(string agent)
{
DataView dataViewClients = new DataView();
DataTable dataTableClients = new DataTable();
SqlConnection connection = Remax.getConnection();
string viewStatement = " SELECT Clients.refClient as Id, Clients.Name, Clients.Phone, Clients.Email, Clients.Role, Employees.Name as Agent, Clients.Comments ";
viewStatement += " FROM Clients ";
viewStatement += " INNER JOIN Employees on Clients.referEmployee = Employees.refEmployee";
try
{
connection.Open();
SqlCommand selectCommand = new SqlCommand(viewStatement, connection);
SqlDataReader clientsReader = selectCommand.ExecuteReader();
dataTableClients.Load(clientsReader);
dataViewClients = dataTableClients.DefaultView;
if (agent != "")
{
dataViewClients.RowFilter = " Agent = '" + agent + "'";
}
return(dataViewClients);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static Employee getEmployee(string email, string password)
{
SqlConnection connection = Remax.getConnection();
string selectStatement = "SELECT * FROM Employees WHERE Email = @email AND Password = @password";
try
{
connection.Open();
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@email", email);
selectCommand.Parameters.AddWithValue("@password", password);
SqlDataReader empReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (empReader.Read())
{
Employee emp = new Employee();
if (empReader["Role"].ToString() == "Admin")
{
Admin admin = new Admin();
admin.Id = empReader["refEmployee"].ToString();
admin.Name = empReader["Name"].ToString();
admin.Phone = empReader["Phone"].ToString();
admin.Email = empReader["Email"].ToString();
admin.Role = empReader["Role"].ToString();
emp = admin;
}
if (empReader["Role"].ToString() == "Agent")
{
Agent agent = new Agent();
agent.Id = empReader["refEmployee"].ToString();
agent.Name = empReader["Name"].ToString();
agent.Phone = empReader["Phone"].ToString();
agent.Email = empReader["Email"].ToString();
agent.Role = empReader["Role"].ToString();
agent.Gender = empReader["Gender"].ToString();
agent.Address = empReader["Address"].ToString();
agent.Image = empReader["Image"].ToString();
agent.Address = empReader["Address"].ToString();
agent.Languages = null;
emp = agent;
}
return(emp);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static Agent getAgent(string id)
{
SqlConnection connection = Remax.getConnection();
string selectStatement = "SELECT * FROM Employees WHERE refEmployee = @id";
string selectStatementLanguages = "SELECT AgentLanguages.referLanguage as refLanguage,Languages.Language FROM AgentLanguages INNER JOIN Languages on AgentLanguages.referLanguage = Languages.refLanguage WHERE AgentLanguages.referEmployee = @id ";
try
{
connection.Open();
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@id", id);
SqlDataReader empReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (empReader.Read())
{
Agent agent = new Agent();
agent.Id = empReader["refEmployee"].ToString();
agent.Name = empReader["Name"].ToString();
agent.Phone = empReader["Phone"].ToString();
agent.Email = empReader["Email"].ToString();
agent.Role = empReader["Role"].ToString();
agent.Gender = empReader["Gender"].ToString();
agent.Password = empReader["Password"].ToString();
agent.Address = empReader["Address"].ToString();
agent.Image = empReader["Image"].ToString();
agent.Address = empReader["Address"].ToString();
connection.Close();
connection.Open();
SqlCommand selectCommandLanguages = new SqlCommand(selectStatementLanguages, connection);
selectCommandLanguages.Parameters.AddWithValue("@id", id);
SqlDataReader langReader = selectCommandLanguages.ExecuteReader();
while (langReader.Read())
{
Language lang = new Language(langReader["refLanguage"].ToString(), langReader["Language"].ToString());
agent.Languages.Add(lang);
}
return(agent);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static void UpdateClientsDB(DataTable newClients)
{
SqlConnection connection = Remax.getConnection();
SqlCommand command = new SqlCommand("SELECT * FROM Clients", connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet dataSet = new DataSet();
connection.Open();
adapter.Fill(dataSet, "Clients");
connection.Close();
DataTable oldClients = dataSet.Tables["Clients"];
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(newClients);
}
public static House getHouse(string id)
{
SqlConnection connection = Remax.getConnection();
string selectStatement = " SELECT * FROM Houses WHERE refHouse = @refHouse";
try
{
connection.Open();
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@refHouse", id);
SqlDataReader houseReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (houseReader.Read())
{
House house = new House();
house.Id = houseReader["refHouse"].ToString();
house.BuildingType = houseReader["referBuildingType"].ToString();
house.NumberBedrooms = houseReader["referNumBedrooms"].ToString();
house.NumberParking = houseReader["referNumParking"].ToString();
house.PropertyType = houseReader["referPropertyType"].ToString();
house.Price = float.Parse(houseReader["price"].ToString());
house.IdAgent = houseReader["referEmployee"].ToString();
house.IdSeller = houseReader["referClient"].ToString();
house.Pool = (bool)houseReader["Pool"];
house.Waterfront = (bool)houseReader["Waterfront"];
house.Elevator = (bool)houseReader["Elevator"];
house.AdapterMobility = (bool)houseReader["Reduced mobility"];
house.NetArea = (int)houseReader["NetArea"];
house.YearBuilt = (int)houseReader["YearBuilt"];
house.Description = houseReader["Description"].ToString();
house.Name = houseReader["Name"].ToString();
house.Address = houseReader["Address"].ToString();
return(house);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static DataView getViewHouses(string user)
{
DataView dataViewHouses = new DataView();
DataTable dataTableHouses = new DataTable();
SqlConnection connection = Remax.getConnection();
string viewStatement = "";
if (user == "admin")
{
viewStatement = "SELECT Houses.refHouse as Id,Houses.Name,Houses.YearBuilt as Year,Houses.NetArea , Houses.price as Price, BuildingType.BuildingType as Building, NumBedrooms.NumBedrooms as Bedroom, NumParking.NumParking as Parking, PropertyType.PropertyType as Property, Employees.Name as Agent,Clients.Name as Seller,Houses.Pool,Houses.Waterfront as Water,Houses.[Reduced mobility] as Mobility,Houses.Elevator,Houses.Address, Houses.Description";
}
else
{
viewStatement = "SELECT Houses.refHouse as Id,Houses.Name,Houses.YearBuilt as Year,Houses.NetArea , Houses.price as Price, BuildingType.BuildingType as Building, NumBedrooms.NumBedrooms as Bedroom, NumParking.NumParking as Parking, PropertyType.PropertyType as Property, Employees.Name as Agent,Houses.Pool,Houses.Waterfront as Water,Houses.[Reduced mobility] as Mobility,Houses.Elevator,Houses.Address, Houses.Description";
}
viewStatement += " FROM Houses ";
viewStatement += " INNER JOIN BuildingType on Houses.referBuildingType = BuildingType.refBuildingType ";
viewStatement += " INNER JOIN NumBedrooms on Houses.referNumBedrooms = NumBedrooms.refNumBedrooms ";
viewStatement += " INNER JOIN NumParking on Houses.referNumParking = NumParking.refNumParking ";
viewStatement += " INNER JOIN PropertyType on Houses.referPropertyType = PropertyType.refPropertyType ";
viewStatement += " INNER JOIN Employees on Houses.referEmployee = Employees.refEmployee ";
viewStatement += " INNER JOIN Clients on Houses.referClient = Clients.refClient ";
try
{
connection.Open();
SqlCommand selectCommand = new SqlCommand(viewStatement, connection);
SqlDataReader housesReader = selectCommand.ExecuteReader();
dataTableHouses.Load(housesReader);
dataViewHouses = dataTableHouses.DefaultView;
return(dataViewHouses);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static void UpdateEmployeesDB(DataTable newEmployees, DataTable newLanguages)
{
SqlConnection connection = Remax.getConnection();
SqlCommand command = new SqlCommand("SELECT * FROM Employees", connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet dataSet = new DataSet();
SqlCommand commandL = new SqlCommand("SELECT * FROM AgentLanguages", connection);
SqlDataAdapter adapterL = new SqlDataAdapter(commandL);
DataSet dataSetL = new DataSet();
connection.Open();
adapter.Fill(dataSet, "Employees");
adapterL.Fill(dataSetL, "AgentLanguages");
connection.Close();
SqlCommandBuilder builderL = new SqlCommandBuilder(adapterL);
adapterL.Update(newLanguages);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(newEmployees);
}
public static Client getClient(string id)
{
SqlConnection connection = Remax.getConnection();
string selectStatement = "SELECT * FROM Clients WHERE refClient = @refClient";
try
{
connection.Open();
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@refClient", id);
SqlDataReader clientReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (clientReader.Read())
{
Client client = new Client();
client.Id = clientReader["refClient"].ToString();
client.Name = clientReader["Name"].ToString();
client.Phone = clientReader["Phone"].ToString();
client.Email = clientReader["Email"].ToString();
client.Comment = clientReader["Comments"].ToString();
client.IdAgent = clientReader["referEmployee"].ToString();
client.Role = clientReader["Role"].ToString();
return(client);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
