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
        }
Beispiel #2
0
        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}
        }
Beispiel #3
0
        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}
        }