Exemplo n.º 1
0
        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
        }
Exemplo n.º 2
0
        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
        }
Exemplo n.º 3
0
        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
        }
Exemplo n.º 4
0
        //--------------------------------------------------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);
        }