public void RemoveEverything(SQLConnection sql)
{
var CommandText = $"DELETE FROM contacts;";
sql.ExecuteNonQuery(CommandText);
CommandText = $"DELETE FROM locations;";
sql.ExecuteNonQuery(CommandText);
CommandText = $"DELETE FROM sqlite_sequence;";
sql.ExecuteNonQuery(CommandText);
}
//TODO: confirmation messages etc. (maybe use events, tuples with boolean return value or premethods which check input)
//ADD CONTACT METHOD
public bool AddContact(ContactBookLogic contactbooklogic, SQLConnection sql, long countLocations, string name, Location location, long phoneNumber, string mailAddress, string gender)
{
int LocationID = (int)sql.GetLocationID(location);
Contact contact = new Contact
{
Name = name,
LocationID = LocationID,
PhoneNumber = phoneNumber,
MailAddress = mailAddress,
Gender = gender
};
List <Contact> contactslist = sql.OutputContactTableToList();
var conIsDupe = false;
foreach (var con1 in contactslist)
{
if ((contact.Name == con1.Name) && (contact.LocationID == con1.LocationID) && (contact.PhoneNumber == con1.PhoneNumber) && (contact.MailAddress == con1.MailAddress) && (contact.Gender == con1.Gender))
{
conIsDupe = true;
}
}
if (!conIsDupe)
{
var CommandText = $"INSERT INTO contacts(Name, LocationID, PhoneNumber, MailAddress, Gender) VALUES('{contact.Name}', '{LocationID}', '{contact.PhoneNumber}', '{contact.MailAddress}', '{contact.Gender}');";
sql.ExecuteNonQuery(CommandText);
}
return(conIsDupe);
}
//---------------------------------------ADD OR GET LOCATION-----------------------------------------------------------------------
public Location AddOrGetLocation(ContactBookLogic contactbooklogic, SQLConnection sql, long countLocations, string address, string cityName)
{
Location location = new Location()
{
Address = address,
CityName = cityName,
};
List <Location> locationslist = sql.OutputLocationTableToList();
var locIsDupe = false;
var CommandText = "";
foreach (var loc1 in locationslist)
{
if ((location.Address == loc1.Address) && (location.CityName == loc1.CityName))
{
locIsDupe = true;
}
}
if (!locIsDupe)
{
CommandText = $"INSERT INTO locations(Address, CityName) VALUES ('{location.Address}', '{location.CityName}')";
sql.ExecuteNonQuery(CommandText);
//TODOL: info ob location geaddet oder dupe war und nicht geaddet
}
return(location);
}
//----------------------------------------REMOVE METHOD------------------------------------------------------------------------------
public void RemoveContact(ContactBookLogic contactbooklogic, long countContact, SQLConnection sql, int value)
{
var CommandText = $"DELETE FROM contacts WHERE ContactID = '{value}';";
sql.ExecuteNonQuery(CommandText);
//TODOL: contact successfully deleted message + invalid input message
}
//----------------------------------------MERGE----------------------------------------------------------------------------------
public void MergeContacts(int temp1, int temp2, SQLConnection sql)
{
var CommandText = $"UPDATE contacts SET LocationID = (SELECT LocationID FROM contacts WHERE ContactID = {temp1}) WHERE ContactID = {temp2};";
sql.ExecuteNonQuery(CommandText);
//TODOL: message Contact's address and city successfully merged.
}
public void EditLocation(int inputindex, string c, SQLConnection sql, string newValue)
{
var CommandText = "";
var beforeEditValue = "";
bool newValueIsCorrectInput = false;
Location location = sql.OutputSingleLocation(inputindex);
CommandText = $"SELECT COUNT(*) FROM contacts c INNER JOIN locations l WHERE l.LocationID = c.LocationID AND l.Address = '{location.Address}' AND l.CityName = '{location.CityName}';";
long locHasConCount = sql.ExecuteScalar(CommandText);
if (locHasConCount == 0)
{
if (c == "1")
{
CommandText = $"SELECT Address FROM locations WHERE LocationID = {inputindex};";
beforeEditValue = sql.GetBeforeEditValueString(inputindex, CommandText);
newValueIsCorrectInput = InputChecker.NoEmptyInputCheck(newValue);
CommandText = $"UPDATE locations SET Address = '{newValue}' WHERE LocationID = {inputindex};";
}
else if (c == "2")
{
CommandText = $"SELECT CityName FROM locations WHERE LocationID = {inputindex};";
beforeEditValue = sql.GetBeforeEditValueString(inputindex, CommandText);
newValueIsCorrectInput = InputChecker.NoEmptyInputCheck(newValue);
CommandText = $"UPDATE locations SET CityName = '{newValue}' WHERE LocationID = {inputindex};";
}
sql.ExecuteNonQuery(CommandText);
//TODOL: message Contactname successfully changed from {beforeEditValue} to {newValue}!
CommandText = $"SELECT COUNT(*) FROM locations l WHERE l.Address = '{location.Address}' AND l.CityName = '{location.CityName}';";
long dupecount = sql.ExecuteScalar(CommandText);
if (dupecount >= 2)
{
CommandText = $"DELETE FROM locations WHERE LocationID = '{inputindex}';";
sql.ExecuteNonQuery(CommandText);
//TODOL: message Location with index: {inputindex} was a duplicate after editing and got removed.
}
}
//TODOL: message WARNING: You can't edit a location that is linked to an existing contact
}
//--------------------------------------------------EDIT------------------------------------------------------------------------
public void EditContact(int inputindex, string c, SQLConnection sql, string newValue)
{
//TODOL: message successfully changed old to new for all 3 options on correct position
bool newValueIsNumber = long.TryParse(newValue, out long newphoneno); //TODOL: this does not work if name is int
var CommandText = "";
if (c == "1")
{
CommandText = $"UPDATE contacts SET Name = '{newValue}' WHERE ContactID = {inputindex};";
sql.ExecuteNonQuery(CommandText);
}
else if (c == "2")
{
CommandText = $"UPDATE contacts SET phoneNumber = '{newphoneno}' WHERE ContactID = {inputindex};";
sql.ExecuteNonQuery(CommandText);
}
else if (c == "3")
{
CommandText = $"UPDATE contacts SET MailAddress = '{newValue}' WHERE ContactID = {inputindex};";
sql.ExecuteNonQuery(CommandText);
}
Contact contact = sql.OutputSingleContact(inputindex);
CommandText = $"SELECT COUNT(*) FROM contacts c INNER JOIN locations l ON c.LocationID = l.LocationID WHERE c.Name = '{contact.Name}' AND c.PhoneNumber = {contact.PhoneNumber} AND c.LocationID = {contact.LocationID} AND c.MailAddress = '{contact.MailAddress}' AND c.Gender = '{contact.Gender}' ";
long dupecount = sql.ExecuteScalar(CommandText);
if (dupecount >= 2)
{
CommandText = $"DELETE FROM contacts WHERE ContactID = '{inputindex}';";
sql.ExecuteNonQuery(CommandText);
//TODOL: message Contact with index: {inputindex} was a duplicate after editing and got removed.
}
//TODOL: show old and new value after edit
//GET OLD VALUE SAMPLE
//CommandText = $"SELECT Name FROM contacts WHERE ContactID = {inputindex};";
//string beforeEditValue = sql.GetBeforeEditValueString(inputindex, CommandText);
}
public void EditLocation(int inputindex, string c, SQLConnection sql, string newValue)
{
var CommandText = "";
Location location = sql.OutputSingleLocation(inputindex);
CommandText = $"SELECT COUNT(*) FROM contacts c INNER JOIN locations l WHERE l.LocationID = c.LocationID AND l.Address = '{location.Address}' AND l.CityName = '{location.CityName}';";
long locHasConCount = sql.ExecuteScalar(CommandText);
if (locHasConCount == 0) //TODOH: warum kann man keine location ändern die einem contact zugewiesen ist
{
if (c == "1")
{
CommandText = $"UPDATE locations SET Address = '{newValue}' WHERE LocationID = {inputindex};";
sql.ExecuteNonQuery(CommandText);
}
else if (c == "2")
{
CommandText = $"UPDATE locations SET CityName = '{newValue}' WHERE LocationID = {inputindex};";
sql.ExecuteNonQuery(CommandText);
}
sql.ExecuteNonQuery(CommandText);
//TODOL: message Contactname successfully changed from {beforeEditValue} to {newValue}!
CommandText = $"SELECT COUNT(*) FROM locations l WHERE l.Address = '{location.Address}' AND l.CityName = '{location.CityName}';";
long dupecount = sql.ExecuteScalar(CommandText);
if (dupecount >= 2)
{
CommandText = $"DELETE FROM locations WHERE LocationID = '{inputindex}';";
sql.ExecuteNonQuery(CommandText);
//TODOL: message Location with index: {inputindex} was a duplicate after editing and got removed.
}
}
//TODOL: message WARNING: You can't edit a location that is linked to an existing contact
}
public void RemoveLocation(ContactBookLogic contactbooklogic, long countLocation, SQLConnection sql, int value)
{
var CommandText = $"SELECT COUNT(l.LocationID) FROM locations l, contacts c WHERE {value} = c.LocationID AND {value} = l.LocationID;"; // check ob locationID von der zu löschenden location in contacts vorhanden ist - wenn ja -> nicht löschen
long count = sql.ExecuteScalar(CommandText);
CommandText = $"SELECT * FROM locations WHERE LocationID = {value}";
long c = sql.ExecuteScalar(CommandText);
if (count == 0 && c > 0)
{
CommandText = $"DELETE FROM locations WHERE LocationID = {value};";
sql.ExecuteNonQuery(CommandText);
//TODOL: location successfully deleted message
}
//TODOL: all messages for : alternatives else 1. no location with index value found 2. you can not delete location that is associated to a contact 3. invalid input
}
public void ReadAndAddLocationFromCsvFile(string csvLine, SQLConnection sql)
{
string[] parts = csvLine.Split(',');
parts = parts.Where(x => !string.IsNullOrEmpty(x)).ToArray();
string addressPart = InputChecker.CsvEmptyInputCheck(parts[0]);
string cityNamePart = InputChecker.CsvEmptyInputCheck(parts[1]);
if (addressPart != "wronginput" && cityNamePart != "wronginput")
{
Location location = new Location()
{
Address = addressPart,
CityName = cityNamePart,
};
List <Location> locationslist = sql.OutputLocationTableToList();
var locIsDupe = false;
var CommandText = "";
foreach (var loc1 in locationslist)
{
if ((location.Address == loc1.Address) && (location.CityName == loc1.CityName))
{
locIsDupe = true;
}
}
if (!locIsDupe)
{
CommandText = $"INSERT INTO locations(Address, CityName) VALUES ('{location.Address}', '{location.CityName}')";
sql.ExecuteNonQuery(CommandText);
}
//TODOL: message INFO: Location is duplicate and will not be added
}
//TODOL: message WARNING: Wrong input on line: {csvLine}
}
public void ReadAndAddContactFromCsvLine(string csvLine, SQLConnection sql)
{
string[] parts = csvLine.Split(',');
string namePart = InputChecker.CsvEmptyInputCheck(parts[0]);
string addressPart = InputChecker.CsvEmptyInputCheck(parts[1]);
string cityNamePart = InputChecker.CsvEmptyInputCheck(parts[2]);
long.TryParse(parts[3], out long a);
long phoneNumberPart = a;
string mailAddressPart = InputChecker.CsvMailFormatCheck(parts[4]);
string genderPart = InputChecker.CsvGenderCheck(parts[5]);
if (phoneNumberPart != 0 && genderPart != "wronginput" && namePart != "wronginput" && addressPart != "wronginput" && cityNamePart != "wronginput")
{
Location location = new Location()
{
Address = addressPart,
CityName = cityNamePart,
};
List <Location> locationslist = sql.OutputLocationTableToList();
var locIsDupe = false;
var CommandText = "";
foreach (var loc1 in locationslist)
{
if ((location.Address == loc1.Address) && (location.CityName == loc1.CityName))
{
locIsDupe = true;
}
}
if (!locIsDupe)
{
CommandText = $"INSERT INTO locations(Address, CityName) VALUES ('{location.Address}', '{location.CityName}')";
sql.ExecuteNonQuery(CommandText);
}
// TODOL: Message INFO: Location is duplicate and will not be added
long LocationID = sql.GetLocationID(location);
var contact = new Contact
{
Name = namePart,
LocationID = (int)LocationID,
PhoneNumber = phoneNumberPart,
MailAddress = mailAddressPart,
Gender = genderPart
};
List <Contact> contactslist = sql.OutputContactTableToList();
var conIsDupe = false;
foreach (var con1 in contactslist)
{
if ((contact.Name == con1.Name) && (contact.LocationID == con1.LocationID) && (contact.PhoneNumber == con1.PhoneNumber) && (contact.MailAddress == con1.MailAddress) && (contact.Gender == con1.Gender))
{
conIsDupe = true;
}
}
if (!conIsDupe)
{
CommandText = $"INSERT INTO contacts(Name, LocationID, PhoneNumber, MailAddress, Gender) VALUES('{contact.Name}', '{contact.LocationID}', '{contact.PhoneNumber}', '{contact.MailAddress}', '{contact.Gender}');";
sql.ExecuteNonQuery(CommandText);
}
// TODOL: Message INFO: Contact on {csvLine} is duplicate and will not be added
}
// TODOL: message WARNING: Wrong input on line: {csvLine}
}