private string GetUsername(string username, bool admin)
{
string retValue = "";
DatabaseManager dbManager = new DatabaseManager();
if (admin)
{
var result = dbManager.ReturnQuery("SELECT Admin_Username FROM Admin WHERE Admin_Username = '******';");
foreach (var r in result)
{
retValue = r[0].ToString();
break;
}
}
else
{
var result = dbManager.ReturnQuery("SELECT Agent_Email FROM Agent WHERE Agent_Email = '" + username + "';");
foreach (var r in result)
{
retValue = r[0].ToString();
break;
}
}
return(retValue);
}
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 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 + ");"));
}
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 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 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 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 GetAddressID(int AreaID, string streetName, int streetNo)
{
DatabaseManager dbManager = new DatabaseManager();
int address_ID = -1;
var addressID = (dbManager.ReturnQuery("SELECT Address_ID FROM Address WHERE (Area_ID = " + AreaID + " AND Address_Streetname = '" + streetName + "' AND Address_StreetNo = " + streetNo + ")"));
foreach (var i in addressID)
{
address_ID = Convert.ToInt32(i[0]);
}
return(address_ID);
}
public int GetComplexID(string complexName, int addressID)
{
DatabaseManager dbManager = new DatabaseManager();
int complex_ID = -1;
var complexID = (dbManager.ReturnQuery("SELECT Complex_ID FROM Complex WHERE (Complex_Name = '" + complexName + "' AND Address_ID = " + addressID + ")"));
foreach (var i in complexID)
{
complex_ID = Convert.ToInt32(i[0]);
}
return(complex_ID);
}
public int GetPropertyID(int agentID, int listingID)
{
DatabaseManager dbManager = new DatabaseManager();
int propertyID = 0;
var properties = (dbManager.ReturnQuery("SELECT Property_ID FROM Listing WHERE Agent_ID = " + agentID + " AND List_ID = " + listingID + " ORDER BY Property_ID ASC;"));
foreach (var i in properties)
{
propertyID = Convert.ToInt32(i[0]);
}
return(propertyID);
}
public int GetAgentID(string agentEmail)
{
DatabaseManager dbManager = new DatabaseManager();
int agentID = -1;
var agent = (dbManager.ReturnQuery("SELECT Agent_ID FROM Agent WHERE Agent_Email = '" + agentEmail + "'"));
foreach (var i in agent)
{
agentID = Convert.ToInt32(i[0]);
}
return(agentID);
}
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);
}
private int countPropertiesWithAddress(int addressID)
{
int count = -1;
DatabaseManager dbManager = new DatabaseManager();
var addressess = (dbManager.ReturnQuery("SELECT * FROM Address WHERE (Address_ID = " + addressID + ")"));
foreach (var address in addressess)
{
count++;
}
return(count);
}
private int countPropertiesInComplex(int complexID)
{
int count = -1;
DatabaseManager dbManager = new DatabaseManager();
var complexes = (dbManager.ReturnQuery("SELECT * FROM Complex WHERE (Complex_ID = " + complexID + ")"));
foreach (var complex in complexes)
{
count++;
}
return(count);
}
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 <string> GetAreas(int area_City_ID)
{
DatabaseManager dbManager = new DatabaseManager();
List <string> area_Names = new List <string>();
var areasName = (dbManager.ReturnQuery("SELECT Area_Name FROM Area WHERE Area_City_ID = " + area_City_ID + " ORDER BY Area_Name"));
string areaName;
foreach (var i in areasName)
{
areaName = Convert.ToString(i[0]);
area_Names.Add(areaName);
}
return(area_Names);
}
public List <string> GetCities(int city_Province_ID)
{
DatabaseManager dbManager = new DatabaseManager();
List <string> city_Names = new List <string>();
var citiesName = (dbManager.ReturnQuery("SELECT City_Name FROM City WHERE City_Province_ID = " + city_Province_ID + " ORDER BY City_Name"));
string cityName;
foreach (var i in citiesName)
{
cityName = Convert.ToString(i[0]);
city_Names.Add(cityName);
}
return(city_Names);
}
public List <string> GetProvinces()
{
DatabaseManager dbManager = new DatabaseManager();
List <string> province_Names = new List <string>();
var provinces = (dbManager.ReturnQuery("SELECT Province_Name FROM Province ORDER BY Province_Name"));
string provinceName;
foreach (var i in provinces)
{
provinceName = Convert.ToString(i[0]);
province_Names.Add(provinceName);
}
return(province_Names);
}
public List <int> GetClientsID()
{
DatabaseManager dbManager = new DatabaseManager();
List <int> clients_ID = new List <int>();
var clientsID = (dbManager.ReturnQuery("SELECT Client_ID FROM Clients"));
int clientID;
foreach (var i in clientsID)
{
clientID = Convert.ToInt32(i[0]);
clients_ID.Add(clientID);
}
return(clients_ID);
}
public List <int> GetProvincesID()
{
DatabaseManager dbManager = new DatabaseManager();
List <int> provinces_ID = new List <int>();
var provincesID = (dbManager.ReturnQuery("SELECT Province_ID, Province_Name FROM Province ORDER BY Province_Name"));
int provinceID;
foreach (var i in provincesID)
{
provinceID = Convert.ToInt32(i[0]);
provinces_ID.Add(provinceID);
}
return(provinces_ID);
}
public List <int> GetAreasID(int area_City_ID)
{
DatabaseManager dbManager = new DatabaseManager();
List <int> areas_ID = new List <int>();
var areasID = (dbManager.ReturnQuery("SELECT Area_ID, Area_Name FROM Area WHERE Area_City_ID = " + area_City_ID + " ORDER BY Area_Name"));
int areaID;
foreach (var i in areasID)
{
areaID = Convert.ToInt32(i[0]);
areas_ID.Add(areaID);
}
return(areas_ID);
}
public List <int> GetCitiesID(int city_Province_ID)
{
DatabaseManager dbManager = new DatabaseManager();
List <int> cities_ID = new List <int>();
var citiesID = (dbManager.ReturnQuery("SELECT City_ID, City_Name FROM City WHERE City_Province_ID = " + city_Province_ID + " ORDER BY City_Name"));
int cityID;
foreach (var i in citiesID)
{
cityID = Convert.ToInt32(i[0]);
cities_ID.Add(cityID);
}
return(cities_ID);
}
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 List <string> GetClients()
{
DatabaseManager dbManager = new DatabaseManager();
List <string> clients_Email = new List <string>();
var clients = (dbManager.ReturnQuery("SELECT Client_Name , Client_Email FROM Clients"));
string clientName, clientEmail;
foreach (var i in clients)
{
clientName = Convert.ToString(i[0]);
clientEmail = Convert.ToString(i[1]);
clients_Email.Add(clientName + ", " + clientEmail);
}
return(clients_Email);
}
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 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 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 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 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 CanAddProvince(string provinceName)
{
bool canAdd = true;
DatabaseManager dbManager = new DatabaseManager();
var provinceNames = dbManager.ReturnQuery("SELECT Province_Name FROM Province WHERE Province_Name = '" + provinceName + "';");
foreach (var province in provinceNames)
{
canAdd = false;
break;
}
return(canAdd);
}
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 int ProvinceID(string provinceName)
{
DatabaseManager dbManager = new DatabaseManager();
var result = dbManager.ReturnQuery("SELECT Province_ID FROM Province WHERE Province_Name = '" + provinceName + "';");
int provinceID = -1;
foreach (var i in result)
{
provinceID = int.Parse(i[0]);
break;
}
return(provinceID);
}
public bool CanAddCity(string cityName, string provinceName)
{
bool canAdd = true;
DatabaseManager dbManager = new DatabaseManager();
var results = dbManager.ReturnQuery("SELECT City_Name FROM City WHERE City_Name ='" + cityName + "' AND City_Province_ID = (SELECT Province_ID FROM Province WHERE Province_Name ='" + provinceName + "');");
foreach (var i in results)
{
canAdd = false;
break;
}
return(canAdd);
}
public int CityID(string cityName, int provinceID)
{
DatabaseManager dbManger = new DatabaseManager();
int cityID = -1;
var restult = dbManger.ReturnQuery("SELECT City_ID FROM City WHERE City_Name = '" + cityName + "' AND City_Province_ID = " + provinceID.ToString() + ";");
foreach (var i in restult)
{
cityID = int.Parse(i[0]);
break;
}
return(cityID);
}
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 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 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 CanAddAgent(string email)
{
DatabaseManager dbManager = new DatabaseManager();
bool canAdd = true;
var agentEmails = dbManager.ReturnQuery("SELECT Agent_Email FROM Agent WHERE Agent_Email = '" + email + "';");
foreach (var agentEmail in agentEmails)
{
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;
}
private void RefreshProperties()
{
Console.WriteLine("=============================================Refresh");
new System.Threading.Thread(() =>
{
ClearPropertiesGrid();
DatabaseManager dbManager = new DatabaseManager();
var properties = dbManager.ReturnQuery("SELECT Property_ID, Property_Bedroom_Count, Property_Bathroom_Count, Property_Garage_Count, Property_hasPool, Property_Plot_Size, Property_House_Size FROM Property ORDER BY Property_ID ASC;");
foreach (var property in properties)
{
Console.WriteLine("=============================================" + property[0]);
DatabaseManager dbManager2 = new DatabaseManager();
var agentIDs = dbManager2.ReturnQuery("SELECT Agent_ID FROM Listing WHERE Property_ID = " + Convert.ToInt32(property[0]) + ";");
foreach (var agentID in agentIDs)
{
Console.WriteLine("=============================================" + agentID[0]);
DatabaseManager dbManager3 = new DatabaseManager();
var agentEmails = dbManager3.ReturnQuery("SELECT Agent_Email FROM Agent WHERE Agent_ID = " + Convert.ToInt32(agentID[0]) + ";");
foreach (var agentEmail in agentEmails)
{
Console.WriteLine("============================================="+agentEmail[0]);
InsertIntoPropertiesGrid(Convert.ToString(agentEmail[0]), Convert.ToInt32(property[1]), Convert.ToInt32(property[2]), Convert.ToInt32(property[3]), Convert.ToInt32(property[4]), Convert.ToInt32(property[5]), Convert.ToInt32(property[6]));
}
}
}
}).Start();
}
private void Search()
{
new System.Threading.Thread(() =>
{
ClearClientsGrid();
int searchColumn = GetSelectedSearchField();
DatabaseManager dbManager = new DatabaseManager();
var result = dbManager.ReturnQuery("SELECT Client_Name, Client_Surname, Client_Phone, Client_Email FROM Clients ORDER BY Client_Surname, Client_Name;");
foreach( var client in result)
{
if( client[searchColumn].ToString().Contains(GetSearchValue()) )
{
InsertIntoClientsGrid(client[0], client[1], client[2], client[3]);
}
}
}).Start();
}
public List<string> GetClients()
{
DatabaseManager dbManager = new DatabaseManager();
List<string> clients_Email = new List<string>();
var clients = (dbManager.ReturnQuery("SELECT Client_Name , Client_Email FROM Clients"));
string clientName, clientEmail;
foreach (var i in clients)
{
clientName = Convert.ToString(i[0]);
clientEmail = Convert.ToString(i[1]);
clients_Email.Add(clientName + ", " + clientEmail);
}
return clients_Email;
}
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
{
}
}
public List<string> GetAreas(int area_City_ID)
{
DatabaseManager dbManager = new DatabaseManager();
List<string> area_Names = new List<string>();
var areasName = (dbManager.ReturnQuery("SELECT Area_Name FROM Area WHERE Area_City_ID = " + area_City_ID + ""));
string areaName;
foreach (var i in areasName)
{
areaName = Convert.ToString(i[0]);
area_Names.Add(areaName);
}
return area_Names;
}
public int GetAddressID(int AreaID, string streetName, int streetNo)
{
DatabaseManager dbManager = new DatabaseManager();
int address_ID = -1;
var addressID = (dbManager.ReturnQuery("SELECT Address_ID FROM Address WHERE (Area_ID = " + AreaID + " AND Address_Streetname = '" + streetName + "' AND Address_StreetNo = " + streetNo + ")"));
foreach (var i in addressID)
{
address_ID = Convert.ToInt32(i[0]);
}
return address_ID;
}
public List<string> GetCities(int city_Province_ID)
{
DatabaseManager dbManager = new DatabaseManager();
List<string> city_Names = new List<string>();
var citiesName = (dbManager.ReturnQuery("SELECT City_Name FROM City WHERE City_Province_ID = " + city_Province_ID + ""));
string cityName;
foreach (var i in citiesName)
{
cityName = Convert.ToString(i[0]);
city_Names.Add(cityName);
}
return city_Names;
}
public List<int> GetProvincesID()
{
DatabaseManager dbManager = new DatabaseManager();
List<int> provinces_ID = new List<int>();
var provincesID = (dbManager.ReturnQuery("SELECT Province_ID FROM Province"));
int provinceID;
foreach (var i in provincesID)
{
provinceID = Convert.ToInt32(i[0]);
provinces_ID.Add(provinceID);
}
return provinces_ID;
}
public List<string> GetProvinces()
{
DatabaseManager dbManager = new DatabaseManager();
List<string> province_Names = new List<string>();
var provinces = (dbManager.ReturnQuery("SELECT Province_Name FROM Province"));
string provinceName;
foreach (var i in provinces)
{
provinceName = Convert.ToString(i[0]);
province_Names.Add(provinceName);
}
return province_Names;
}
public int GetComplexID(string complexName, int addressID)
{
DatabaseManager dbManager = new DatabaseManager();
int complex_ID = -1;
var complexID = (dbManager.ReturnQuery("SELECT Complex_ID FROM Complex WHERE (Complex_Name = '" + complexName + "' AND Address_ID = " + addressID + ")"));
foreach (var i in complexID)
{
complex_ID = Convert.ToInt32(i[0]);
}
return complex_ID;
}
public List<int> GetClientsID()
{
DatabaseManager dbManager = new DatabaseManager();
List<int> clients_ID = new List<int>();
var clientsID = (dbManager.ReturnQuery("SELECT Client_ID FROM Clients"));
int clientID;
foreach (var i in clientsID)
{
clientID = Convert.ToInt32(i[0]);
clients_ID.Add(clientID);
}
return clients_ID;
}
private void Search()
{
new System.Threading.Thread(() =>
{
ClearPropertiesGrid();
int searchColumn = GetSelectedSearchField();
DatabaseManager dbManager = new DatabaseManager();
var properties = dbManager.ReturnQuery("SELECT Property_ID, Property_Bedroom_Count, Property_Bathroom_Count, Property_Garage_Count, Property_hasPool, Property_Plot_Size, Property_House_Size FROM Property ORDER BY Property_ID ASC;");
foreach (var property in properties)
{
var agentIDs = dbManager.ReturnQuery("SELECT Agent_ID FROM Listing WHERE Property_ID = " + Convert.ToInt32(property[0]) + ";");
foreach (var agentID in agentIDs)
{
var agentEmails = dbManager.ReturnQuery("SELECT Agent_Email FROM Agent WHERE Agent_ID = " + Convert.ToInt32(agentID[0]) + ";");
foreach (var agentEmail in agentEmails)
{
if (property[searchColumn].ToString().Contains(GetSearchValue()))
{
InsertIntoPropertiesGrid(Convert.ToString(agentEmail[0]), Convert.ToInt32(property[1]), Convert.ToInt32(property[2]), Convert.ToInt32(property[3]), Convert.ToInt32(property[4]), Convert.ToInt32(property[5]), Convert.ToInt32(property[6]));
}
}
}
}
}).Start();
}
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;
}
private void RefreshListings()
{
new System.Threading.Thread(() =>
{
SetLoadingState(true);
ClearListingsGrid();
DatabaseManager dbManager = new DatabaseManager();
AgentManager agManager = new AgentManager();
var listingInfo = dbManager.ReturnQuery("SELECT * FROM Listing WHERE Agent_ID = " + agManager.GetAgentID(GetCurrentAgent()) + " ORDER BY List_ID;");
foreach (var list in listingInfo)
{
DatabaseManager dbManager2 = new DatabaseManager();
var propertyInfo = dbManager2.ReturnQuery("SELECT Client_ID, Property_Unit_No, Address_ID, Complex_ID, Property_ID 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 Address_ID, Address_Streetname, Address_Streetno FROM Address WHERE Address_ID = " + Convert.ToInt32(property[2]) + " ORDER BY Address_ID;");
foreach (var address in adressInfo)
{
DatabaseManager dbManager4 = new DatabaseManager();
if (Convert.ToInt32(property[3]) != 0 && Convert.ToInt32(property[3]) != -1)
{
var complexInfo = dbManager4.ReturnQuery("SELECT Complex_Name, Complex_ID 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 Client_Email FROM Clients WHERE Client_ID = " + Convert.ToInt32(property[0]) + " ORDER BY Client_ID;");
foreach (var client in clientInfo)
{
string yesNoSold;
string yesNoNegotiable;
if (Convert.ToInt32(list[4]) == 0)
yesNoNegotiable = "No";
else
yesNoNegotiable = "Yes";
if (Convert.ToInt32(list[5]) == 0)
yesNoSold = "No";
else
yesNoSold = "Yes";
InsertIntoListingsGrid(Convert.ToInt32(list[0]), client[0], address[1] + " " + address[2], complex[0] + " " + property[1], list[2], yesNoSold, yesNoNegotiable, list[6]);
}
}
}
else
{
DatabaseManager dbManager5 = new DatabaseManager();
var clientInfo = dbManager5.ReturnQuery("SELECT Client_Email FROM Clients WHERE Client_ID = " + Convert.ToInt32(property[0]) + " ORDER BY Client_ID;");
foreach (var client in clientInfo)
{
string yesNoSold;
string yesNoNegotiable;
if (Convert.ToInt32(list[4]) == 0)
yesNoNegotiable = "No";
else
yesNoNegotiable = "Yes";
if (Convert.ToInt32(list[5]) == 0)
yesNoSold = "No";
else
yesNoSold = "Yes";
InsertIntoListingsGrid(Convert.ToInt32(list[0]), client[0], address[1] + " " + address[2], "N/A", list[2], yesNoSold, yesNoNegotiable, list[6]);
}
}
}
}
}
SetLoadingState(false);
}).Start();
}
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 List<int> GetAreasID(int area_City_ID)
{
DatabaseManager dbManager = new DatabaseManager();
List<int> areas_ID = new List<int>();
var areasID = (dbManager.ReturnQuery("SELECT Area_ID FROM Area WHERE Area_City_ID = " + area_City_ID + ""));
int areaID;
foreach (var i in areasID)
{
areaID = Convert.ToInt32(i[0]);
areas_ID.Add(areaID);
}
return areas_ID;
}
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();
}
private void RefreshClients()
{
new System.Threading.Thread(() =>
{
ClearClientsGrid();
DatabaseManager dbManager = new DatabaseManager();
var clientNames = dbManager.ReturnQuery("SELECT Client_Name, Client_Surname, Client_Phone, Client_Email FROM Clients ORDER BY Client_Surname, Client_Name;");
foreach (var client in clientNames)
{
InsertIntoClientsGrid(client[0], client[1], client[2], client[3]);
}
}).Start();
}
public int GetAgentID(string agentEmail)
{
DatabaseManager dbManager = new DatabaseManager();
int agentID = -1;
var agent = (dbManager.ReturnQuery("SELECT Agent_ID FROM Agent WHERE Agent_Email = '" + agentEmail + "'"));
foreach (var i in agent)
{
agentID = Convert.ToInt32(i[0]);
}
return agentID;
}
public List<int> GetCitiesID(int city_Province_ID)
{
DatabaseManager dbManager = new DatabaseManager();
List<int> cities_ID = new List<int>();
var citiesID = (dbManager.ReturnQuery("SELECT City_ID FROM City WHERE City_Province_ID = " + city_Province_ID + ""));
int cityID;
foreach (var i in citiesID)
{
cityID = Convert.ToInt32(i[0]);
cities_ID.Add(cityID);
}
return cities_ID;
}
