예제 #1
0
        //Class Methods
        /// <summary>
        /// Fills the class with Address information from database using addressId
        /// </summary>
        /// <param name="existingAddressId"></param>
        /// <param name="conn"></param>
        private void setDatabaseAddressInfo(int existingAddressId, MySqlConnection conn)
        {
            using (conn)
            {
                string          myQuery = "SELECT * FROM address WHERE addressId = " + existingAddressId.ToString();
                MySqlDataReader reader  = DataChanges.GetMySqlReader(conn, myQuery);
                while (reader.Read())
                {
                    addressId  = (int)reader["addressId"];
                    address    = reader["address"].ToString();
                    address2   = reader["address2"].ToString();
                    cityId     = (int)reader["cityId"];
                    postalCode = reader["postalCode"].ToString();
                    phone      = reader["phone"].ToString();
                    //createDate = (DateTime)reader["createDate"];
                    createdBy = reader["createdBy"].ToString();
                    //lastUpdate = (DateTime)reader["lastUpdate"];
                    lastUpdateBy = reader["lastUpdateBy"].ToString();
                }
                conn.Close();

                if (addressId != existingAddressId)
                {
                    throw new Exception("Address ID wasn't found");
                }
                cityObject = new City(cityId, loggedInUser, conn);
            }
        }
예제 #2
0
        //Class Methods
        /// <summary>
        /// Finds a city by the city ID, and fills an object with the information from the database
        /// </summary>
        /// <param name="CityId"></param>
        /// <param name="CreatedBy"></param>
        /// <param name="conn"></param>
        private void setDatabaseCityInfo(int CityId, string CreatedBy, MySqlConnection conn)
        {
            using (conn)
            {
                string          myExecuteQuery = "SELECT * FROM city WHERE cityId = " + CityId.ToString();
                MySqlDataReader reader         = DataChanges.GetMySqlReader(conn, myExecuteQuery);
                while (reader.Read())
                {
                    cityId       = (int)reader["cityId"];
                    cityName     = reader["city"].ToString();
                    countryId    = (int)reader["countryId"];
                    createDate   = (DateTime)reader["createDate"];
                    createdBy    = reader["createdBy"].ToString();
                    lastUpdate   = (DateTime)reader["lastUpdate"];
                    lastUpdateBy = reader["lastUpdateBy"].ToString();
                }
                conn.Close();

                if (cityId != CityId)
                {
                    throw new Exception("City ID wasn't found");
                }
                countryObject = new Country(countryId, conn);
            }
        }
예제 #3
0
        // Class Methods
        /// <summary>
        /// Sets the Customer Object with Database info
        /// </summary>
        /// <param name="existingCustomerId"></param>
        /// <param name="conn"></param>
        private void setDatabaseCustomerInfo(int existingCustomerId, MySqlConnection conn)
        {
            using (conn)
            {
                string          myExecuteQuery = "SELECT * FROM customer WHERE customerId = " + existingCustomerId.ToString();
                MySqlDataReader reader         = DataChanges.GetMySqlReader(conn, myExecuteQuery);
                while (reader.Read())
                {
                    customerID   = (int)reader["customerId"];
                    customerName = reader["customerName"].ToString();
                    addressID    = (int)reader["addressId"];
                    bool activeCheck = (bool)reader["active"];
                    if (activeCheck == true)
                    {
                        active = 1;
                    }
                    else
                    {
                        active = 0;
                    }
                    createdBy    = reader["createdBy"].ToString();
                    lastUpdateBy = reader["lastUpdateBy"].ToString();
                }
                conn.Close();

                if (customerID != existingCustomerId)
                {
                    throw new Exception("Customer ID wasn't found");
                }
                addressObject = new Address(addressID, conn);
            }
        }
예제 #4
0
        /// <summary>
        /// Constructor to Add Customer to Database
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <param name="UserId"></param>
        /// <param name="Title"></param>
        /// <param name="Description"></param>
        /// <param name="Location"></param>
        /// <param name="Contact"></param>
        /// <param name="Type"></param>
        /// <param name="Url"></param>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="CreatedBy"></param>
        /// <param name="UpdatedBy"></param>
        /// <param name="AppointmentType"></param>
        public Appointment(int CustomerId, int UserId, string Title, string Description, string Location, string Contact, string Url, DateTime StartTime, DateTime EndTime, string CreatedBy, string UpdatedBy, string AppointmentType)
        {
            using (var conn = new MySqlConnection(connecterString))
            {
                string findHighestAppointmentId = "SELECT MAX(appointmentId) FROM appointment";
                var    reader = DataChanges.GetMySqlReader(conn, findHighestAppointmentId);
                while (reader.Read())
                {
                    try
                    {
                        highestAppointmentId = (int)reader["MAX(appointmentId)"];
                    }
                    catch
                    {
                        highestAppointmentId = 0;
                    }
                }
                conn.Close();

                type = AppointmentType;
                highestAppointmentId++;
                appointmentId = highestAppointmentId;
                customerId    = CustomerId;
                userId        = UserId;
                title         = Title;
                description   = Description;
                location      = Location;
                contact       = Contact;
                url           = Url;
                start         = StartTime;
                end           = EndTime;
                createdBy     = CreatedBy;
                createDate    = DateTime.Now;
                lastUpdate    = DateTime.Now;
                lastUpdateBy  = UpdatedBy;

                string addAppointmentQuery = "INSERT INTO appointment VALUES(" + appointmentId.ToString()
                                             + ", " + customerId.ToString() + ", '" + title + "', '" + description + "', '"
                                             + location + "', '" + contact + "', '" + url + "', '"
                                             + start.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "', '"
                                             + createDate.ToString("yyyy-MM-dd") + "', '" + createdBy + "', '" + lastUpdate.ToString("yyyy-MM-dd")
                                             + "', '" + lastUpdateBy + "', '" + type + "', " + userId.ToString() + ")";
                //DataChanges db = new DataChanges();
                DataChanges.ExecuteMySqlCommand(addAppointmentQuery, conn);
            }
        }
예제 #5
0
 //Class Methods
 /// <summary>
 /// Sets the Object's properties with the Database information based on Country ID with the given connection
 /// </summary>
 /// <param name="CountryId"></param>
 /// <param name="conn"></param>
 private void getDatabaseCountryInfo(int CountryId, MySqlConnection conn)
 {
     //Search Database to find country by ID, then populate the information with the returned information.
     using (conn)
     {
         string          myExecuteQuery = "SELECT * FROM country WHERE countryId = " + CountryId.ToString();
         MySqlDataReader reader         = DataChanges.GetMySqlReader(conn, myExecuteQuery);
         while (reader.Read())
         {
             countryId     = (int)reader["countryId"];
             country       = reader["country"].ToString();
             createDate    = (DateTime)reader["createDate"];
             createdBy     = reader["createdBy"].ToString();
             lastUpdate    = (DateTime)reader["lastUpdate"];
             lastUpdatedBy = reader["lastUpdateBy"].ToString();
         }
     }
 }
예제 #6
0
        /// <summary>
        /// Constructor to create a new user
        /// </summary>
        /// <param name="Username"></param>
        /// <param name="Password"></param>
        /// <param name="userCreating"></param>
        /// <param name="Connection"></param>
        public User(string Username, string Password, string userCreating, MySqlConnection Connection)
        {
            using (Connection)
            {
                try
                {
                    int    numOfUsers       = 0;
                    string getAllUsersQuery = "SELECT * FROM user";
                    // Create a user based on the Username and password given
                    //Get the number of users currently in the database, Add one, and save it as a variable to use as a new ID
                    MySqlDataReader reader = DataChanges.GetMySqlReader(Connection, getAllUsersQuery);
                    while (reader.Read())
                    {
                        numOfUsers++;
                    }
                    Connection.Close();
                    numOfUsers++;
                    DateTime today = DateTime.Now;

                    //Fill User Object with
                    userId        = numOfUsers;
                    userName      = Username;
                    password      = Password;
                    lastUpdate    = today;
                    lastUpdatedBy = userCreating;
                    createdBy     = userCreating;
                    createDate    = today;
                    active        = 1;


                    //DataChanges db = new DataChanges();
                    //Create a Command to insert a new user with the id as the count + 1 of users, the username and password given, the user adding the user.
                    string insertCommand = "INSERT INTO user VALUES (" + userId.ToString() + ", '" + userName + "', '" + password + "', " + active.ToString()
                                           + ", '" + createdBy + "', '" + createDate.ToString("yyyy-mm-dd") + "', '" + lastUpdate.ToString("yyyy-mm-dd") + "', '" + lastUpdatedBy + "')";
                    DataChanges.ExecuteMySqlCommand(insertCommand, Connection);
                    //See how many users are in Database
                    //reader = DataChanges.GetMySqlReader(conn, "SELECT * FROM user");
                }
                catch
                {
                    throw new Exception("Failed to Add User");
                }
            }
        }
예제 #7
0
        // Constructors
        /// <summary>
        /// City Constructor using City Name
        /// </summary>
        /// <param name="CityName"></param>
        /// <param name="CountryName"></param>
        /// <param name="CreatedBy"></param>
        /// <param name="conn"></param>
        public City(string CityName, string CountryName, string CreatedBy, MySqlConnection conn)
        {
            //Search Database for City that matches.
            //Check to see if Country with given name exists. If not, update the database to add the country
            using (conn)
            {
                countryObject = new Country(CountryName, CreatedBy, conn);
                int  cityCount      = 0;
                bool cityExists     = false;
                int  existingCityId = 0;

                bool databaseEmpty = true;
                int  newMaxCityId  = 0;

                //Get the max value of id instead of counting all of the accounts.
                string          myQuery = "SELECT MAX(cityId) FROM city";
                MySqlDataReader reader1 = DataChanges.GetMySqlReader(conn, myQuery);
                //Use this to see if the country exists, and to count the number of countries
                while (reader1.Read())
                {
                    newMaxCityId  = (int)reader1["Max(cityId)"];
                    databaseEmpty = false;
                }
                conn.Close();

                myQuery = "SELECT * FROM city";
                MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myQuery);
                //Use this to see if the city exists, and to count the number of cities
                while (reader.Read())
                {
                    cityCount++;
                    string checkCity    = reader["city"].ToString();
                    int    checkCountry = (int)reader["countryId"];

                    if (checkCity == CityName)
                    {
                        if (countryObject.countryId == checkCountry)
                        {
                            cityExists = true;
                            var cityId = reader["cityId"];
                            existingCityId = (int)cityId;
                        }
                    }
                }
                conn.Close();
                if (cityExists == true)
                {
                    setDatabaseCityInfo(existingCityId, CreatedBy, conn);
                }
                else
                {
                    //Increment the number of countries by one to use as the id
                    cityCount++;
                    if (databaseEmpty == true)
                    {
                        cityId = 1;
                    }
                    else
                    {
                        newMaxCityId++;
                        cityId = newMaxCityId;
                    }
                    //Set values of Class
                    cityName = CityName;
                    //Create a country object to check if a country is already created, and if it's not, create it, and use the id of the existing or new Country

                    countryId    = countryObject.countryId;
                    createDate   = DateTime.Now;
                    createdBy    = CreatedBy;
                    lastUpdate   = DateTime.Now;
                    lastUpdateBy = CreatedBy;

                    //add city to database if it doesn't exist
                    //DataChanges db = new DataChanges();
                    myQuery = "INSERT INTO city VALUES(" + cityId.ToString() + ", '" + cityName + "', "
                              + countryId.ToString() + ", '" + createDate.ToString("yyyy-MM-dd") + "', '" + createdBy
                              + "', '" + lastUpdate.ToString("yyyy-MM-dd") + "', '" + lastUpdateBy + "')";
                    DataChanges.ExecuteMySqlCommand(myQuery, conn);
                }
            }
            //If there isn't one that matches, add it with the information given
        }
예제 #8
0
        //Constructors
        /// <summary>
        /// Constructor based on Country Name
        /// </summary>
        /// <param name="CountryName"></param>
        /// <param name="CreatedBy"></param>
        /// <param name="conn"></param>
        public Country(string CountryName, string CreatedBy, MySqlConnection conn)
        {
            //Check to see if Country with given name exists. If not, update the database to add the country
            using (conn)
            {
                int  countryCount      = 0;
                bool countryExists     = false;
                int  existingCountryId = 0;
                bool databaseEmpty     = true;
                int  newMaxCountryId   = 0;

                //Get the max value of id instead of counting all of the accounts.
                string          myQuery = "SELECT MAX(countryId) FROM country";
                MySqlDataReader reader1 = DataChanges.GetMySqlReader(conn, myQuery);
                //Use this to see if the country exists, and to count the number of countries
                while (reader1.Read())
                {
                    newMaxCountryId = (int)reader1["Max(countryId)"];
                    databaseEmpty   = false;
                }
                conn.Close();



                myQuery = "SELECT * FROM country";
                MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myQuery);
                //Use this to see if the country exists, and to count the number of countries
                while (reader.Read())
                {
                    countryCount++;
                    string checkCountry = reader["country"].ToString();

                    if (checkCountry == CountryName)
                    {
                        countryExists = true;
                        var countryId = reader["countryId"];
                        existingCountryId = (int)countryId;
                    }
                }
                conn.Close();
                if (countryExists == true)
                {
                    getDatabaseCountryInfo(existingCountryId, conn);
                }
                else
                {
                    //Increment the number of countries by one to use as the id
                    countryCount++;
                    //Set values of Class
                    if (databaseEmpty == true)
                    {
                        countryId = 1;
                    }
                    else
                    {
                        newMaxCountryId++;
                        countryId = newMaxCountryId;
                    }
                    country       = CountryName;
                    createDate    = DateTime.Now;
                    createdBy     = CreatedBy;
                    lastUpdate    = DateTime.Now;
                    lastUpdatedBy = CreatedBy;

                    //add country to database if it doesn't exist
                    //DataChanges db = new DataChanges();
                    myQuery = "INSERT INTO country VALUES(" + countryId.ToString() + ", '" + country + "', '"
                              + createDate.ToString("yyyy-MM-dd") + "', '" + createdBy + "', '"
                              + lastUpdate.ToString("yyyy-MM-dd") + "', '" + lastUpdatedBy + "')";
                    DataChanges.ExecuteMySqlCommand(myQuery, conn);
                }
            }
        }
예제 #9
0
        // Constructors
        /// <summary>
        /// Address Constructor Using Address Information.
        /// </summary>
        /// <param name="Address"></param>
        /// <param name="Address2"></param>
        /// <param name="City"></param>
        /// <param name="CountryName"></param>
        /// <param name="PostalCode"></param>
        /// <param name="Phone"></param>
        /// <param name="CreatedBy"></param>
        /// <param name="conn"></param>
        public Address(string Address, string Address2, string City, String CountryName, string PostalCode, string Phone, string CreatedBy, MySqlConnection conn)
        {
            //Check Database to see if the address matches exactly with any records.
            //If It does, populate information with information from Database

            //If it's not, use the given information to add a new record to the database
            //Get City ID by searching for matches for City
            //Use current date/time for creade date and last update
            //Throw error if Insert failed
            cityObject = new City(City, CountryName, CreatedBy, conn);
            int  addressCount      = 0;
            bool addressExists     = false;
            int  existingAddressId = 0;



            string          myQuery = "SELECT * FROM address";
            MySqlDataReader reader  = DataChanges.GetMySqlReader(conn, myQuery);

            //Use this to see if the country exists, and to count the number of countries
            while (reader.Read())
            {
                addressCount++;
                string checkAddress = reader["address"].ToString() + reader["address2"].ToString() + reader["cityId"].ToString();
                string givenAddress = Address + Address2 + cityObject.cityId.ToString();
                if (givenAddress == checkAddress)
                {
                    //Set existingAddressId to be able to Call function that fills class from reader by id
                    //after connection is closed
                    addressExists     = true;
                    existingAddressId = (int)reader["addressId"];
                }
            }
            conn.Close();
            if (addressExists == true)
            {
                setDatabaseAddressInfo(existingAddressId, conn);
            }
            else
            {
                //Increment the number of addresses by one to use as the id
                addressCount++;
                //Set values of Class
                addressId = addressCount;
                address   = Address;
                address2  = Address2;
                //Create a country object to check if a country is already created, and if it's not, create it, and use the id of the existing or new Country
                cityId       = cityObject.cityId;
                postalCode   = PostalCode;
                phone        = Phone;
                createDate   = DateTime.Now;
                createdBy    = CreatedBy;
                lastUpdate   = DateTime.Now;
                lastUpdateBy = CreatedBy;

                //add country to database if it doesn't exist
                //DataChanges db = new DataChanges();
                myQuery = "INSERT INTO address VALUES(" + addressId.ToString() + ", '" + address + "', '"
                          + address2 + "', " + cityId + ", '" + postalCode + "', '" + phone + "', '"
                          + createDate.ToString("yyyy-MM-dd") + "', '" + createdBy + "', '"
                          + lastUpdate.ToString("yyyy-MM-dd") + "', '" + lastUpdateBy + "')";
                DataChanges.ExecuteMySqlCommand(myQuery, conn);
            }
        }
예제 #10
0
        //Constructors
        /// <summary>
        /// Constructor for Adding a new Customer, or loading customer object if matching customer exists
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="Address1"></param>
        /// <param name="Address2"></param>
        /// <param name="City"></param>
        /// <param name="CountryName"></param>
        /// <param name="PostalCode"></param>
        /// <param name="Phone"></param>
        /// <param name="CreatedBy"></param>
        /// <param name="conn"></param>
        public Customer(string Name, string Address1, string Address2, string City, string CountryName, string PostalCode, string Phone, string CreatedBy, MySqlConnection conn)
        {
            //Check Database for number of customers, add one, and use as customer ID
            using (conn)
            {
                addressObject = new Address(Address1, Address2, City, CountryName, PostalCode, Phone, CreatedBy, conn);
                bool customerExists     = false;
                int  existingCustomerId = 0;

                bool databaseEmpty = true;
                int  newMaxCityId  = 0;

                //Get the max value of id instead of counting all of the accounts.
                string          myQuery = "SELECT MAX(customerId) FROM customer";
                MySqlDataReader reader1 = DataChanges.GetMySqlReader(conn, myQuery);
                //Use this to see if the country exists, and to count the number of countries
                while (reader1.Read())
                {
                    newMaxCityId  = (int)reader1["Max(customerId)"];
                    databaseEmpty = false;
                }
                conn.Close();


                //Check to see if there are any matches to the customer
                string          mySqlQuery = "SELECT * FROM customer JOIN address ON customer.addressId = address.addressId";
                MySqlDataReader reader     = DataChanges.GetMySqlReader(conn, mySqlQuery);
                while (reader.Read())
                {
                    string TempName = reader["customerName"].ToString();
                    if (TempName == Name)
                    {
                        string tempAddress = reader["address"].ToString() + reader["address2"].ToString();
                        string tempPhone   = reader["phone"].ToString();
                        if (tempAddress == Address1 + Address2 && tempPhone == Phone)
                        {
                            customerExists     = true;
                            existingCustomerId = (int)reader["customerId"];
                        }
                    }
                }
                conn.Close();
                if (customerExists == true)
                {
                    setDatabaseCustomerInfo(existingCustomerId, conn);
                }
                else
                {
                    newMaxCityId++;
                    if (databaseEmpty == true)
                    {
                        customerID = 1;
                    }
                    else
                    {
                        customerID = newMaxCityId;
                    }
                    //Set name = the name given
                    //use address constructor to set phone, and address along with postal Code, etc in an object
                    //get address ID from address object to add address ID
                    //Set active,
                    //Use current date/time to set create and last update dates
                    //Use the given logged in user name to set createdBy, and lastupdated by
                    customerName = Name;
                    addressID    = addressObject.addressId;
                    active       = 1;
                    createDate   = DateTime.Now;
                    CreatedBy    = loggedInUser;
                    lastUpdate   = DateTime.Now;
                    lastUpdateBy = loggedInUser;

                    //add customer to database if it doesn't exist
                    //DataChanges db = new DataChanges();
                    myQuery = "INSERT INTO customer VALUES(" + customerID.ToString() + ", '" + customerName + "', "
                              + addressID.ToString() + ", " + active.ToString() + ", '" + createDate.ToString("yyyy-MM-dd") + "', '" + createdBy
                              + "', '" + lastUpdate.ToString("yyyy-MM-dd") + "', '" + lastUpdateBy + "')";
                    DataChanges.ExecuteMySqlCommand(myQuery, conn);
                }
            }
        }