public bool EditPassword(string username, string newPassword)
{
DatabaseManager dbManager = new DatabaseManager();
Cryptography crypto = new Cryptography();
return (dbManager.NonReturnQuery("UPDATE Admin set Admin_Password = '******' WHERE Admin_Username ='******';"));
}
public bool EditPassword(string email, string newPassword)
{
DatabaseManager dbManager = new DatabaseManager();
Cryptography crypto = new Cryptography();
return (dbManager.NonReturnQuery("UPDATE Agent set Agent_Password = '******' WHERE Agent_Email ='" + email + "';"));
}
public bool EditPassword(string email, string newPassword)
{
DatabaseManager dbManager = new DatabaseManager();
Cryptography crypto = new Cryptography();
return (dbManager.NonReturnQuery("UPDATE Clients set Client_Password = '******' WHERE Client_Email ='" + email + "';"));
}
public bool DeleteListing(int listingID)
{
int propID = -1;
int addID = -1;
int compID = -1;
DatabaseManager dbManager = new DatabaseManager();
var propIDs = dbManager.ReturnQuery("SELECT Property_ID FROM Listing WHERE List_ID = " + listingID);
foreach (var i in propIDs)
{
propID = Convert.ToInt32(i[0]);
}
var compIDs = dbManager.ReturnQuery("SELECT Complex_ID FROM Property WHERE Property_ID = " + propID);
foreach (var i in compIDs)
{
compID = Convert.ToInt32(i[0]);
}
var addIDs = dbManager.ReturnQuery("SELECT Address_ID FROM Property WHERE Property_ID = " + propID);
foreach (var i in addIDs)
{
addID = Convert.ToInt32(i[0]);
}
dbManager.NonReturnQuery("DELETE FROM Image WHERE ( Image.Property_ID = " + propID + ");");
if (countPropertiesInComplex(compID) == 0 && countPropertiesWithAddress(addID) == 0)
return (dbManager.NonReturnQuery("DELETE FROM Complex, Address, Property, Listing USING Complex, Address, Property, Listing WHERE ( Complex.Complex_ID = Property.Complex_ID AND Property.Property_ID = Listing.Property_ID AND Property.Address_ID = Address.Address_ID AND Listing.List_ID =" + listingID + ");"));
else if (countPropertiesInComplex(compID) == 0 && countPropertiesWithAddress(addID) != 0)
return (dbManager.NonReturnQuery("DELETE FROM Complex, Property, Listing USING Complex, Property, Listing WHERE (Complex.Complex_ID = Property.Complex_ID AND Property.Property_ID = Listing.Property_ID AND Listing.List_ID =" + listingID + ");"));
else if (countPropertiesInComplex(compID) != 0 && countPropertiesWithAddress(addID) == 0)
return (dbManager.NonReturnQuery("DELETE FROM Address, Property, Listing USING Address, Property, Listing WHERE ( Property.Property_ID = Listing.Property_ID AND Property.Address_ID = Address.Address_ID AND Listing.List_ID =" + listingID + ");"));
else
return (dbManager.NonReturnQuery("DELETE FROM Complex, Address, Property, Listing USING Complex, Address, Property, Listing WHERE ( Complex.Complex_ID = Property.Complex_ID AND Property.Property_ID = Listing.Property_ID AND Property.Address_ID = Address.Address_ID AND Listing.List_ID =" + listingID + ");"));
}
public bool AddAdmin(string username, string password)
{
DatabaseManager dbManager = new DatabaseManager();
Cryptography crypto = new Cryptography();
return (dbManager.NonReturnQuery("INSERT INTO Admin (Admin_Username , Admin_Password) VALUES ('" + username + "','" + crypto.EncryptString(password) + "');"));
}
private void TestConnection()
{
SetServerStatus(CONNECTING);
SetTaskbarState(CONNECTING);
SetLoadingStatus(true);
DisplayNotifyBox("Connecting", "Please wait while the application attempts to connect to the database");
new System.Threading.Thread(() =>
{
Classes.DatabaseManager dbManger = new Classes.DatabaseManager();
connectionWorking = dbManger.ConnectionWorking();
if (connectionWorking)
{
SetServerStatus(CONNECTED);
SetTaskbarState(CONNECTED);
DisplayNotifyBox("Connected", "Successfully connected to the database");
}
else
{
SetServerStatus(NOT_CONNECTED);
SetTaskbarState(NOT_CONNECTED);
DisplayNotifyBox("Not connected", "Could not connect to the database. Please check your internet connection");
}
SetFormState(connectionWorking);
SetLoadingStatus(false);
}).Start();
}
private string GetPassword(string username, bool admin)
{
string retValue = "";
DatabaseManager dbManager = new DatabaseManager();
if (admin)
{
var result = dbManager.ReturnQuery("SELECT Admin_Password FROM Admin WHERE Admin_Username = '******';");
foreach (var r in result)
{
retValue = r[0].ToString();
break;
}
}
else
{
var result = dbManager.ReturnQuery("SELECT Agent_Password FROM Agent WHERE Agent_Email = '" + username + "';");
foreach (var r in result)
{
retValue = r[0].ToString();
break;
}
}
return retValue;
}
public bool AddClient(string name, string surname, string phone, string email, string password)
{
DatabaseManager dbManager = new DatabaseManager();
Cryptography crypto = new Cryptography();
return (dbManager.NonReturnQuery("INSERT INTO Clients (Client_Name , Client_Surname, Client_Phone, Client_Email, Client_Password) VALUES ('" + name + "','" + surname + "','" + phone + "','" + email + "','" + crypto.EncryptString(password) + "');"));
}
public string AreaFullName(int areaID)
{
string fullName = "";
int cityId = -1;
int provinceID = -1;
DatabaseManager dbManager = new DatabaseManager();
var areaName = (dbManager.ReturnQuery("SELECT Area_Name, Area_City_ID FROM Area WHERE Area_ID = " + areaID + ";"));
foreach (var i in areaName)
{
fullName = i[0].ToString();
cityId = Convert.ToInt32(i[1]);
}
var cityName = (dbManager.ReturnQuery("SELECT City_Name, City_Province_ID FROM City WHERE City_ID = " + cityId.ToString() + ";"));
foreach (var j in cityName)
{
fullName = j[0].ToString() + ", " + fullName;
provinceID = Convert.ToInt32(j[1]);
}
var provinceName = (dbManager.ReturnQuery("SELECT Province_Name FROM Province WHERE Province_ID = " + provinceID.ToString() + ";"));
foreach (var k in provinceName)
{
fullName = k[0].ToString() + ", " + fullName;
}
return fullName;
}
public bool AddAgent(string name, string surname, string phone, string email, string password)
{
DatabaseManager dbManager = new DatabaseManager();
Cryptography crypto = new Cryptography();
MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand();
return (dbManager.NonReturnQuery("INSERT INTO Agent (Agent_Name , Agent_Surname, Agent_Phone, Agent_Email, Agent_Password) VALUES ('" + name + "','" + surname + "','" + phone + "','" + email + "','" + Cryptography.CreateHash(password).ToString() + "');"));
}
public bool AddPreferenceArea(int preferenceID, string areaIn)
{
string[] area;
string[] dell = new string[] { ", " };
area = areaIn.Split(dell, System.StringSplitOptions.None);
DatabaseManager dbManager = new DatabaseManager();
LocationManager locManager = new LocationManager();
return (dbManager.NonReturnQuery("INSERT INTO Preference_Area (Preference_ID, Area_ID) values (" + preferenceID + "," + locManager.AreaID(locManager.CityID(area[1], locManager.ProvinceID(area[0])), area[2]) + ");"));
}
public bool DeletePreferenceArea(int preferenceID, string areaIn)
{
string[] area;
string[] dell = new string[] { ", " };
area = areaIn.Split(dell, System.StringSplitOptions.None);
DatabaseManager dbManager = new DatabaseManager();
LocationManager locManager = new LocationManager();
return (dbManager.NonReturnQuery("DELETE FROM Preference_Area WHERE Preference_ID = " + preferenceID + " AND Area_ID = " + locManager.AreaID(locManager.CityID(area[1], locManager.ProvinceID(area[0])), area[2]) + ";"));
}
public int GetPreferenceID(int clientID)
{
DatabaseManager dbManager = new DatabaseManager();
int preferenceID = 0;
var preferences = (dbManager.ReturnQuery("SELECT Preference_ID FROM Preference WHERE Preference_Client_ID = " + clientID.ToString() + " ORDER BY Preference_ID ASC;"));
foreach (var i in preferences)
{
preferenceID = Convert.ToInt32(i[0]);
}
return preferenceID;
}
public List<int> GetListings(int agentID)
{
DatabaseManager dbManager = new DatabaseManager();
List<int> listing_IDs = new List<int>();
var listings = (dbManager.ReturnQuery("SELECT List_ID FROM Listing WHERE Agent_ID = " + agentID));
foreach (var i in listings)
{
listing_IDs.Add(Convert.ToInt32(i[0]));
}
return listing_IDs;
}
public int GetAgentID(string email)
{
DatabaseManager dbManager = new DatabaseManager();
int agentID = -1;
var agentIDs = dbManager.ReturnQuery("SELECT Agent_ID FROM Agent WHERE Agent_Email = '" + email + "';");
foreach (var id in agentIDs)
{
agentID = Convert.ToInt32(id[0]);
}
return agentID;
}
public int AreaID(int cityID, string areaName)
{
DatabaseManager dbManger = new DatabaseManager();
int areaID = -1;
var restult = dbManger.ReturnQuery("SELECT Area_ID FROM Area WHERE Area_Name = '" + areaName + "' AND Area_City_ID = " + cityID.ToString() + ";");
foreach (var i in restult)
{
areaID = int.Parse(i[0]);
break;
}
return areaID;
}
public bool CanAddPreference(int clientID, int minBedrooms, int maxBedrooms, int minBathrooms, int maxBathrooms, int minGarages, int maxGarages, int minPlotSize, int maxPlotSize, int minHouseSize, int maxHouseSize, int minPrice, int maxPrice, int hasPool)
{
DatabaseManager dbManager = new DatabaseManager();
bool canAdd = true;
var preferenceEmails = dbManager.ReturnQuery("SELECT Preference_ID FROM Preference WHERE (Preference_Client_ID = " + clientID + " AND Preference_Min_Bedrooms = " + minBedrooms + " AND Preference_Max_Bedrooms = " + maxBedrooms + " AND Preference_Min_Bathrooms = " + minBathrooms + " AND Preference_Max_Bathrooms = " + maxBathrooms + " AND Preference_Min_Garages = " + minGarages + " AND Preference_Max_Garages = " + maxGarages + " AND Preference_hasPool = " + hasPool + " AND Preference_Min_Plot_Size = " + minPlotSize + " AND Preference_Max_Plot_Size = " + maxPlotSize + " AND Preference_Min_House_Size = " + minHouseSize + " AND Preference_Max_House_Size = " + maxHouseSize + " AND Preference_Min_Price = " + minPrice + " AND Preference_Max_Price = " + maxPrice + ");");
foreach (var preferenceEmail in preferenceEmails)
{
canAdd = false;
break;
}
return canAdd;
}
public bool CanAddClient(string email)
{
DatabaseManager dbManager = new DatabaseManager();
bool canAdd = true;
var clientEmails = dbManager.ReturnQuery("SELECT Client_Email FROM Clients WHERE Client_Email = '" + email + "';");
foreach (var clientEmail in clientEmails)
{
canAdd = false;
break;
}
return canAdd;
}
public bool CanAddArea(string areaName,string cityName, string provinceName)
{
DatabaseManager dbManager = new DatabaseManager();
bool canAdd = true;
var result = dbManager.ReturnQuery("SELECT Area_Name FROM Area WHERE Area_Name ='" + areaName + "' AND Area_City_ID = " + CityID(cityName,ProvinceID(provinceName)).ToString() + ";");
foreach (var i in result)
{
canAdd = false;
break;
}
return canAdd;
}
public bool CanAddAdmin(string username)
{
DatabaseManager dbManager = new DatabaseManager();
bool canAdd = true;
var adminUsernames = dbManager.ReturnQuery("SELECT Admin_Username FROM Admin WHERE Admin_Username = '******';");
foreach (var adminUsername in adminUsernames)
{
canAdd = false;
break;
}
return canAdd;
}
public bool AddOffer(int listID, int clientID, int offerPrice, int offerStatus, string date)
{
DatabaseManager dbManager = new DatabaseManager();
return(dbManager.NonReturnQuery("INSERT INTO Offer (List_ID, Client_ID, Offer_Price, Offer_Status, Offer_Change_Date) VALUES (" + listID + "," + clientID + "," + offerPrice + "," + offerStatus + ",'" + date + "');"));
}
public bool EditOffer(int offerID, int listID, int clientID, int offerPrice, int offerStatus, string date)
{
DatabaseManager dbManager = new DatabaseManager();
return dbManager.NonReturnQuery("UPDATE Offer SET List_ID = " + listID + ", Client_ID = " + clientID + ", Offer_Price = " + offerPrice + ", Offer_Status = " + offerStatus + ", Offer_Change_Date = '" + date + "' WHERE Offer_ID = " + offerID + ";");
}
public bool DeleteOffer(int offerID)
{
DatabaseManager dbManager = new DatabaseManager();
return dbManager.NonReturnQuery("DELETE FROM Offer WHERE Offer_ID = " + offerID + ";");
}
public bool AddOffer(int listID, int clientID, int offerPrice, int offerStatus, string date)
{
DatabaseManager dbManager = new DatabaseManager();
return dbManager.NonReturnQuery("INSERT INTO Offer (List_ID, Client_ID, Offer_Price, Offer_Status, Offer_Change_Date) VALUES (" + listID + "," + clientID + "," + offerPrice + "," + offerStatus + ",'" + date + "');");
}
private void RefreshListings()
{
new System.Threading.Thread(() =>
{
ClearListingsGrid();
DatabaseManager dbManager = new DatabaseManager();
var listingInfo = dbManager.ReturnQuery("SELECT Client_Name, Client_Surname, Client_Phone, Client_Email FROM Clients ORDER BY Client_Surname, Client_Name;");
foreach (var list in listingInfo)
{
InsertIntoListingsGrid(list[0], list[1], list[2], list[3]);
}
}).Start();
}
public bool ClearPreferenceArea(int preferenceID)
{
DatabaseManager dbManager = new DatabaseManager();
return (dbManager.NonReturnQuery("DELETE FROM Preference_Area WHERE Preference_ID = " + preferenceID + ";"));
}
public bool DeletePreference(int preferenceID)
{
DatabaseManager dbManager = new DatabaseManager();
return (dbManager.NonReturnQuery("DELETE FROM Preference WHERE Preference_ID = " + preferenceID + ";"));
}
public bool EditPreference(int preferenceID, int clientID, int minBedrooms, int maxBedrooms, int minBathrooms, int maxBathrooms, int minGarages, int maxGarages, int minPlotSize, int maxPlotSize, int minHouseSize, int maxHouseSize, int minPrice, int maxPrice, int hasPool)
{
DatabaseManager dbManager = new DatabaseManager();
return (dbManager.NonReturnQuery("UPDATE Preference SET Preference_Min_Bedrooms = " + minBedrooms + ", Preference_Max_Bedrooms = " + maxBedrooms + ", Preference_Min_Bathrooms = " + minBathrooms + ", Preference_Max_Bathrooms = " + maxBathrooms + ", Preference_Min_Garages = " + minGarages + ", Preference_Max_Garages = " + maxGarages + ", Preference_hasPool = " + hasPool + ", Preference_Min_Plot_Size = " + minPlotSize + ", Preference_Max_Plot_Size = " + maxPlotSize + ", Preference_Min_House_Size = " + minHouseSize + ", Preference_Max_House_Size = " + maxHouseSize + ", Preference_Min_Price = " + minPrice + ", Preference_Max_Price = " + maxPrice + " WHERE (Preference_Client_ID = " + clientID + " AND Preference_ID = " + preferenceID + ");"));
}
public bool AddPreference(int clientID, int minBedrooms, int maxBedrooms, int minBathrooms, int maxBathrooms, int minGarages, int maxGarages, int minPlotSize, int maxPlotSize, int minHouseSize, int maxHouseSize, int minPrice, int maxPrice, int hasPool)
{
DatabaseManager dbManager = new DatabaseManager();
return dbManager.NonReturnQuery("INSERT INTO Preference (Preference_Client_ID, Preference_Min_Bedrooms, Preference_Max_Bedrooms, Preference_Min_Bathrooms, Preference_Max_Bathrooms, Preference_Min_Garages, Preference_Max_Garages, Preference_hasPool, Preference_Min_Plot_Size, Preference_Max_Plot_Size, Preference_Min_House_Size, Preference_Max_House_Size, Preference_Min_Price, Preference_Max_Price) VALUES (" + clientID + "," + minBedrooms + "," + maxBedrooms + "," + minBathrooms + "," + maxBathrooms + "," + minGarages + "," + maxGarages + "," + hasPool + "," + minPlotSize + "," + maxPlotSize + "," + minHouseSize + "," + maxHouseSize + "," + minPrice + "," + maxPrice + ");");
}
public void LoadListingInfo(int listID)
{
try
{
currentListID = listID;
new System.Threading.Thread(() =>
{
LoadComboBoxes();
SetLoadingState(true);
DatabaseManager dbManager = new DatabaseManager();
AgentManager agManager = new AgentManager();
var listingInfo = dbManager.ReturnQuery("SELECT * FROM Listing WHERE List_ID = " + currentListID + " ORDER BY List_ID;");
foreach (var list in listingInfo)
{
DatabaseManager dbManager2 = new DatabaseManager();
var propertyInfo = dbManager2.ReturnQuery("SELECT * FROM Property WHERE Property_ID = " + Convert.ToInt32(list[1]) + " ORDER BY Property_ID;");
foreach (var property in propertyInfo)
{
DatabaseManager dbManager3 = new DatabaseManager();
var adressInfo = dbManager3.ReturnQuery("SELECT * FROM Address WHERE Address_ID = " + Convert.ToInt32(property[4]) + " ORDER BY Address_ID;");
foreach (var address in adressInfo)
{
DatabaseManager dbManager4 = new DatabaseManager();
if(Convert.ToInt32(property[2]) !=-1 && Convert.ToInt32(property[2]) !=0)
{
var complexInfo = dbManager4.ReturnQuery("SELECT * FROM Complex WHERE Address_ID = " + Convert.ToInt32(address[0]) + " ORDER BY Complex_ID;");
foreach (var complex in complexInfo)
{
DatabaseManager dbManager5 = new DatabaseManager();
var clientInfo = dbManager5.ReturnQuery("SELECT * FROM Clients WHERE Client_ID = " + Convert.ToInt32(property[1]) + " ORDER BY Client_ID;");
foreach (var client in clientInfo)
{
InsertIntoListingsView(client[1] + ", " + client[4], Convert.ToInt32(address[3]), address[1], address[2], Convert.ToInt32(property[2]), complex[1], property[3], property[5], property[6], property[7], property[9], property[10], property[11], (list[2]), Convert.ToInt32(list[4]), Convert.ToInt32(list[5]), Convert.ToInt32(property[8]), list[6]);
LoadPropertyImages(Convert.ToInt32(list[1]));
}
}
}
else
{
DatabaseManager dbManager5 = new DatabaseManager();
var clientInfo = dbManager5.ReturnQuery("SELECT * FROM Clients WHERE Client_ID = " + Convert.ToInt32(property[1]) + " ORDER BY Client_ID;");
foreach (var client in clientInfo)
{
InsertIntoListingsView(client[1] + ", " + client[4], Convert.ToInt32(address[3]), address[1], address[2], Convert.ToInt32(property[2]), "" , property[3], property[5], property[6], property[7], property[9], property[10], property[11], (list[2]), Convert.ToInt32(list[4]), Convert.ToInt32(list[5]), Convert.ToInt32(property[8]), list[6]);
LoadPropertyImages(Convert.ToInt32(list[1]));
}
}
}
}
}
SetLoadingState(false);
}).Start();
}
catch
{
}
}
private void LoadPropertyImages(int propertyID)
{
this.Dispatcher.Invoke(() =>
{
DatabaseManager dbManager = new DatabaseManager();
var images = dbManager.ReturnQuery("SELECT * FROM Image WHERE Property_ID = " + propertyID + " ORDER BY Image_ID;");
foreach (var image in images)
{
imageID.Add(Convert.ToInt32(image[0]));
CB_Images.Items.Add(fileDirPath + "/" + image[2].Substring(33));
imageSource.Add(" ");
imageCaptions.Add(image[3]);
if (!File.Exists(fileDirPath + "/" + image[2].Substring(33)))
{
DownloadImage(image[2]);
}
TB_ImageCaption.IsEnabled = false;
//LoadImage
}
});
}
public bool EditOffer(int offerID, int listID, int clientID, int offerPrice, int offerStatus, string date)
{
DatabaseManager dbManager = new DatabaseManager();
return(dbManager.NonReturnQuery("UPDATE Offer SET List_ID = " + listID + ", Client_ID = " + clientID + ", Offer_Price = " + offerPrice + ", Offer_Status = " + offerStatus + ", Offer_Change_Date = '" + date + "' WHERE Offer_ID = " + offerID + ";"));
}
private bool UsernameInAdminTable(string username)
{
DatabaseManager dbManger = new DatabaseManager();
var result = dbManger.ReturnQuery("SELECT Admin_Username FROM Admin WHERE Admin_Username = '******';");
bool retValue = false;
foreach (var r in result)
{
retValue = true;
break;
}
return retValue;
}
public bool DeleteOffer(int offerID)
{
DatabaseManager dbManager = new DatabaseManager();
return(dbManager.NonReturnQuery("DELETE FROM Offer WHERE Offer_ID = " + offerID + ";"));
}