コード例 #1
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);
            }
        }
コード例 #2
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");
                }
            }
        }
コード例 #3
0
        /// <summary>
        /// Constructor to Fill Data from Data Given, or update record
        /// </summary>
        /// <param name="AppointmentId"></param>
        /// <param name=""></param>
        public Appointment(string UpdateOrFill, int AppointmentId, int CustomerId, int UserId, string Title, string Description, string Location, string Contact, string Url, DateTime StartTime, DateTime EndTime, string CreatedBy, DateTime CreateDate, string UpdatedBy, DateTime UpdateDate, string AppointmentType, string UserLoggedIn)
        {
            using (var conn = new MySqlConnection(connecterString))
            {
                loggedInUser  = UserLoggedIn;
                type          = AppointmentType;
                appointmentId = AppointmentId;
                customerId    = CustomerId;
                fillCustomerObject(conn);
                userId       = UserId;
                title        = Title;
                description  = Description;
                location     = Location;
                contact      = Contact;
                url          = Url;
                start        = StartTime;
                end          = EndTime;
                createdBy    = CreatedBy;
                createDate   = CreateDate;
                lastUpdate   = UpdateDate;
                lastUpdateBy = UpdatedBy;
                if (UpdateOrFill.ToUpper() == "UPDATE")
                {
                    lastUpdate   = DateTime.Now;
                    lastUpdateBy = loggedInUser;

                    string updateQuery = "UPDATE appointment SET customerId = " + customerId.ToString()
                                         + ", title = '" + title + "', description = '" + description + "', location = '" + location
                                         + "', contact = '" + contact + "', url = '" + url + "', start = '" + start.ToString("yyyy-MM-dd HH:mm")
                                         + "', end = '" + end.ToString("yyyy-MM-dd HH:mm") + "', createDate = '" + createDate.ToString("yyyy-MM-dd")
                                         + "', createdBy = '" + createdBy + "', lastUpdateBy = '" + lastUpdateBy
                                         + "', appointmentType = '" + type + "', userId = " + userId.ToString() + " WHERE appointmentId = " + appointmentId;
                    //DataChanges db = new DataChanges();
                    DataChanges.ExecuteMySqlCommand(updateQuery, conn);
                }
                else
                {
                }
            }
        }
コード例 #4
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
        }
コード例 #5
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);
                }
            }
        }
コード例 #6
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);
            }
        }
コード例 #7
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);
                }
            }
        }