MySqlService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using TeamDBAwesome.Models;
using System.Diagnostics;

namespace TeamDBAwesome.SqlService
{
    public class MySqlService
    {
        private MySqlConnection SqlConn;
        private string server, database, uid, password;

        //constructor
        /// <summary>
        /// Creates an instance of the MySqlService 
        /// </summary>
        public MySqlService()
        {
            Init_Connection();
        }

        /// <summary>
        /// creates the actual connection, keeping things private
        /// </summary>
        private void Init_Connection()
        {
            server = "localhost";
            database = "chinook";
            uid = "root";
            //changeme
            password = "";

            string connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            SqlConn = new MySqlConnection(connectionString);
        }

        private string GetDBString(string SqlFieldName, MySqlDataReader Reader)
        {
            return Reader[SqlFieldName].Equals(DBNull.Value) ? String.Empty : Reader.GetString(SqlFieldName);
        }

        private DateTime GetSqlDate(string SqlFieldName, MySqlDataReader Reader)
        {
            return new DateTime();
        }
        /// <summary>
        /// opens a connection to the DB
        /// </summary>
        /// <returns>a bool representing the success or failure</returns>
        private bool OpenConnection()
        {
            try
            {
                SqlConn.Open();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        /// <summary>
        /// closes a connection to the DB
        /// </summary>
        /// <returns>bool for success or failure</returns>
        private bool CloseConnection()
        {
            try
            {
                SqlConn.Close();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        /// <summary>
        /// Adds a new User into the DB
        /// </summary>
        /// <param name="newCust">a new customer object</param>
        /// <returns>a 0 on success, 1 otherwise</returns>
        public int AddNewUser(Customer newCust)
        {
            bool open = this.OpenConnection();

            if (open == true)
            {

                //do the update into person
                string update = "INSERT into chinook.Person(LastName,FirstName,Address,City,State,PostalCode,Country,Phone,Fax,Email)" +
                    " VALUES (\"" + newCust.LName + "\",\"" + newCust.FName + "\",\"" + newCust.Address + "\",\"" + newCust.City + "\",\"" + newCust.State +
                    "\",\"" + newCust.Post + "\",\"" + newCust.Country + "\",\"" + newCust.Phone + "\",\"" + newCust.Fax + "\",\"" + newCust.Email + "\");";

                MySqlCommand command = new MySqlCommand(update, SqlConn);
                command.ExecuteNonQuery();

                //now we need to get the primary key from that recent update
                string pk_query = "SELECT LAST_INSERT_ID()";
                command = new MySqlCommand(pk_query, SqlConn);
                string newPersonID = command.ExecuteScalar() + "";

                //it turns out that CustomerID isn't an auto incrementing value because people suck, so we need to get that
                string cust_pk_query = "SELECT CustomerID FROM customer ORDER BY CustomerId DESC LIMIT 1";
                command = new MySqlCommand(cust_pk_query, SqlConn);
                string nextCustIDstring = command.ExecuteScalar() + "";
                int next_custID_int = int.Parse(nextCustIDstring);
                next_custID_int = next_custID_int + 1;
                nextCustIDstring = next_custID_int.ToString();

                //now we can put into Customer
                if (newCust.Company == "" || newCust.Company == null)
                {
                    update = "INSERT INTO chinook.Customer(CustomerID,Company,SupportRepID,PersonID) VALUES (" + nextCustIDstring + ",\"" + newCust.Company + "\",\"" + newCust.SupportRepId +
                        "\",\"" + newPersonID + "\")";
                }
                else
                {
                    update = "INSERT INTO chinook.Customer(CustomerID,Company,SupportRepID,PersonID) VALUES (" + nextCustIDstring + ", NULL" + "," + newCust.SupportRepId +
                        "," + newPersonID + ")";
                }

                command = new MySqlCommand(update, SqlConn);
                command.ExecuteNonQuery();

                //close the connection
                this.CloseConnection();

                return int.Parse(newPersonID);
            }
            else
            {
                return 0;
            }


        }

        /// <summary>
        /// adds a payment type to the database
        /// </summary>
        /// <param name="payment">an instance of the Payment Model</param>
        /// <returns>0 on success, 1 on error</returns>
        public int AddPayment(Payment payment)
        {
            bool open = this.OpenConnection();

            if (open == true)
            {

                string new_payment = "insert into chinook.payment(CustomerId,IsDefault) VALUES (" + payment.CustomerId + "," + payment.is_default + ")";

                MySqlCommand command = new MySqlCommand(new_payment, SqlConn);
                command.ExecuteNonQuery();

                //now we need to get the primary key from that recent update
                string pk_query = "SELECT LAST_INSERT_ID()";
                command = new MySqlCommand(pk_query, SqlConn);
                string newPayId = command.ExecuteScalar() + "";

                string PayTypeInsert;

                if (payment.Type == "CC")
                {
                    PayTypeInsert = "insert into creditcard(PayId,CreditCardNumber,ExpirationDate) VALUES (" + newPayId + "," +
                        payment.cardnum + ",\"" + payment.expr_date + "\")";                    
                }
                else if (payment.Type == "AP")
                {
                    PayTypeInsert = "insert into applepay(PayId,ApplePayToken) VALUES (" + newPayId + "," +
                        payment.token + ")";
                }
                else if (payment.Type == "GP")
                {
                    PayTypeInsert = "insert into googlepay(PayId,GoogleEmail,GoogleToken) VALUES (" + newPayId + ",\"" +
                        payment.email + "\"," + payment.token + ")";
                }
                else
                {
                    return 1;
                }
                command = new MySqlCommand(PayTypeInsert, SqlConn);
                command.ExecuteNonQuery();
            }

            return 0;
        }

        /// <summary>
        /// Update a Customer Object
        /// </summary>
        /// <param name="customer">A customer object</param>
        /// <returns>0 success, 1 otherwise</returns>
        public int UpdateCustomer(Customer customer)
        {
            string update_person = "Update person SET FirstName=\'" + customer.FName + "\', LastName=\'" + customer.LName +
                "\', Address=\'" + customer.Address + "\', City=\'" + customer.City + "\', State=\'" + customer.State +
                "\', Country=\'" + customer.Country + "\', PostalCode=\'" + customer.Post + "\', Phone=\'" +
                customer.Phone + "\', Fax=\'" + customer.Fax + "\', Email=\'" + customer.Email + "\' WHERE PersonId=\'" +
                customer.PersonID + "\'";

            string update_customer = "Update customer set Company=\'" + customer.Company + "\', SupportRepID=\'" +
                customer.SupportRepId + "\' WHERE CustomerId =\'" + customer.CustomerID + "\'";

            Debug.WriteLine(update_person);
            Debug.WriteLine(update_customer);

            bool open = this.OpenConnection();

            if (open == true)
            {
                MySqlCommand command = new MySqlCommand(update_person, SqlConn);
                command.ExecuteNonQuery();

                command = new MySqlCommand(update_customer, SqlConn);
                command.ExecuteNonQuery();

                this.CloseConnection();

                return 1;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// Gets a Customer from the DB
        /// </summary>
        /// <param name="PersonID">a personID related to the customer</param>
        /// <returns>A customer object</returns>
        public Customer GetCustomer(int PersonID)
        {
            bool open = this.OpenConnection();
            Customer customer = new Customer();

            if (open == true)
            {
                string customer_query = "select * from `person` left join `customer` on person.PersonId=customer.PersonID where Customer.CustomerID = " + PersonID.ToString();
                MySqlCommand cmd = new MySqlCommand(customer_query, SqlConn);

                MySqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    customer.FName = GetDBString("FirstName", reader);
                    customer.LName = GetDBString("LastName", reader);
                    customer.CustomerID = int.Parse(GetDBString("CustomerID", reader));
                    customer.Address = GetDBString("Address", reader);
                    customer.City = GetDBString("City", reader);
                    customer.State = GetDBString("State", reader);
                    customer.Post = GetDBString("PostalCode", reader);
                    customer.Country = GetDBString("Country", reader);
                    customer.Phone = GetDBString("Phone", reader);
                    customer.Fax = GetDBString("Fax", reader);
                    customer.Email = GetDBString("Email", reader);
                    customer.Company = GetDBString("Company", reader);
                    customer.SupportRepId = int.Parse(GetDBString("SupportRepId", reader));
                    customer.PersonID = int.Parse(GetDBString("PersonID", reader));
                }


                return customer;
            }
            else
            {
                return customer;
            }

        }

        /// <summary>
        /// Searches the Database
        /// </summary>
        /// <param name="search">a search object parameterized by tags from the user</param>
        /// <returns>a SearchResult object</returns>
        public SearchResult Search(Search search)
        {
            bool open = this.OpenConnection();
            SearchResult searchresult = new SearchResult();

            if (open == true)
            {
                //media / album / artist / track / composer / genre

                //do the search
                //need to write some logic to see any of the fields are blank, otherwise do each search, and add them to the lists in result
                Dictionary<string, string> queries = new Dictionary<string, string>();

                queries.Add("Media", "select * from chinook.mediatype where name like \'%" + search.Media + "%\' ");
                queries.Add("Album", "select * from chinook.album where Title like \'%" + search.Album + "%\' ");
                queries.Add("Artist", "select * from chinook.artist where name like \'%" + search.Artist + "%\' ");

                string trackQuery = "select track.TrackId as trackid,track.Name as trackname,track.Composer as trackcomposer,track.Milliseconds,track.Bytes,track.UnitPrice,"
                    + "Album.Title as albumtitle,MediaType.Name as mediatype,Genre.Name as genre "
                    + "from track left join Album on track.AlbumId = Album.AlbumId left join mediatype on track.MediaTypeId=mediatype.MediaTypeId left join genre on track.GenreId = genre.GenreId "
                    + "where track.Name like \'%" + search.Track + "%\'";

                queries.Add("Track", trackQuery);

                string composerQuery = "select track.TrackId as trackid,track.Name as trackname,track.Composer as trackcomposer,track.Milliseconds,track.Bytes,track.UnitPrice,"
                    + "Album.Title as albumtitle,MediaType.Name as mediatype,Genre.Name as genre "
                    + "from track left join Album on track.AlbumId = Album.AlbumId left join mediatype on track.MediaTypeId=mediatype.MediaTypeId left join genre on track.GenreId = genre.GenreId "
                    + "where track.Composer like \'%" + search.Composer + "%\'";

                queries.Add("Composer", composerQuery);
                queries.Add("Genre", "select * from chinook.genre where name like \'%" + search.Genre + "%\' ");

                //declare theresult and init it
                //SearchResult searchresult = new SearchResult();

                //declare the cmd and the reader
                //MySqlCommand cmd;


                foreach (var key in queries)
                {
                    MySqlCommand cmd = new MySqlCommand(key.Value, SqlConn);
                    MySqlDataReader reader;
                    reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        if (search.GetType().GetProperty(key.Key).GetValue(search).ToString() != "")
                        {
                            if (key.Key == "Media")
                            {
                                searchresult.Media.Add(new Media
                                {
                                    MediaTypeId = int.Parse(GetDBString("MediaTypeId", reader)),
                                    Name = GetDBString("Name", reader)
                                });
                            }
                            else if (key.Key == "Album")
                            {
                                searchresult.Album.Add(new Album
                                {
                                    AlbumId = int.Parse(GetDBString("AlbumId", reader)),
                                    ArtistId = int.Parse(GetDBString("ArtistId", reader)),
                                    Title = GetDBString("Title", reader)
                                });
                            }
                            else if (key.Key == "Artist")
                            {
                                searchresult.Artist.Add(new Artist
                                {
                                    ArtistId = int.Parse(GetDBString("ArtistId", reader)),
                                    Name = GetDBString("Name", reader)
                                });
                            }
                            else if (key.Key == "Track")
                            {
                                searchresult.Track.Add(new Track
                                {
                                    TrackId = int.Parse(GetDBString("TrackId", reader)),
                                    TrackName = GetDBString("trackname", reader),
                                    Composer = GetDBString("trackcomposer", reader),
                                    Milliseconds = int.Parse(GetDBString("Milliseconds", reader)),
                                    Bytes = int.Parse(GetDBString("Bytes", reader)),
                                    UnitPrice = float.Parse(GetDBString("UnitPrice", reader)),
                                    AlbumTitle = GetDBString("albumtitle", reader),
                                    MediaType = GetDBString("mediatype", reader),
                                    Genre = GetDBString("genre", reader)
                                });
                            }
                            else if (key.Key == "Composer")
                            {
                                searchresult.Composer.Add(new Track
                                {
                                    TrackId = int.Parse(GetDBString("TrackId", reader)),
                                    TrackName = GetDBString("trackname", reader),
                                    Composer = GetDBString("trackcomposer", reader),
                                    Milliseconds = int.Parse(GetDBString("Milliseconds", reader)),
                                    Bytes = int.Parse(GetDBString("Bytes", reader)),
                                    UnitPrice = float.Parse(GetDBString("UnitPrice", reader)),
                                    AlbumTitle = GetDBString("albumtitle", reader),
                                    MediaType = GetDBString("mediatype", reader),
                                    Genre = GetDBString("genre", reader)
                                });
                            }
                            else if (key.Key == "Genre")
                            {
                                searchresult.Genre.Add(new Genre
                                {
                                    GenreId = int.Parse(GetDBString("GenreId", reader)),
                                    Name = GetDBString("Name", reader)
                                });
                            }
                        }
                    }
                    reader.Close();
                }

                this.CloseConnection();

                //result = searchresult;
                return searchresult;

            }
            else
            {
                return searchresult;
            }
        }

        public List<Payment> GetPayTypes(int customerId)
        {
            List<Payment> payList = new List<Payment>();
            bool open = this.OpenConnection();

            if(open == true)
            {
                string query = "select payment.PayId as PayId,payment.CustomerId as CustomerID, applepay.ApplePayToken as AppleToken, "
                    + "googlepay.GoogleToken as GoogleToken, googlepay.GoogleEmail as GoogleEmail, creditcard.CreditCardNumber as CardNum, "
                    + "creditcard.ExpirationDate as ExprDate, payment.IsDefault as IsDefault "
                    + "from chinook.payment left join googlepay on payment.PayId = googlepay.PayId "  
                    + "left join applepay on payment.PayId = applepay.PayId left join creditcard on creditcard.PayId = payment.PayId "
                    + "where CustomerId = " + customerId;

                MySqlCommand command = new MySqlCommand(query, SqlConn);
                MySqlDataReader reader = command.ExecuteReader();

                Payment newPayment;

                while (reader.Read())
                {
                    newPayment = new Payment();
                    
                    //change token, type with logic
                    newPayment.CustomerId = int.Parse(GetDBString("CustomerID", reader));
                    newPayment.PayId = int.Parse(GetDBString("PayId", reader));
                    string is_default = GetDBString("IsDefault", reader); 
                    string appleToken = GetDBString("AppleToken", reader);
                    string googleToken = GetDBString("GoogleToken", reader);
                    string googleEmail = GetDBString("GoogleEmail", reader);
                    string cardNum = GetDBString("CardNum", reader);
                    string exprDate = GetDBString("ExprDate", reader);

                    if(is_default == "True")
                    {
                        newPayment.is_default = 1;
                    }
                    else
                    {
                        newPayment.is_default = 0; 
                    }

                    if (cardNum != "")
                    {
                        newPayment.Type = "CC";
                        newPayment.cardnum = cardNum;
                        newPayment.expr_date = exprDate;                        
                    }
                    else if (googleToken != "")
                    {
                        newPayment.Type = "GP";
                        newPayment.email = googleEmail;
                        newPayment.token = googleToken;
                    }
                    else if (appleToken != "")
                    {
                        newPayment.Type = "AP";
                        newPayment.token = appleToken;
                    }

                    payList.Add(newPayment);
                }

            }


            return payList;
        }  


        /// <summary>
        /// Gets a track from the DB
        /// </summary>
        /// <param name="trackId">The TrackID</param>
        /// <returns>a track object</returns>
        public Track GetTrack(int trackId)
        {
            Track track = new Track();
            bool open = this.OpenConnection();

            if (open == true)
            {
                string query = "select track.TrackId as trackid,track.Name as trackname,track.Composer as trackcomposer,track.Milliseconds,track.Bytes,track.UnitPrice,"
                    + "Album.Title as albumtitle,MediaType.Name as mediatype,Genre.Name as genre "
                    + "from track left join Album on track.AlbumId = Album.AlbumId left join mediatype on track.MediaTypeId=mediatype.MediaTypeId left join genre on track.GenreId = genre.GenreId "
                    + "where track.TrackId = \'" + trackId.ToString() + "\'";

                MySqlCommand cmd = new MySqlCommand(query, SqlConn);

                MySqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    track.TrackId = int.Parse(GetDBString("TrackId", reader));
                    track.TrackName = GetDBString("trackname", reader);
                    track.Composer = GetDBString("trackcomposer", reader);
                    track.Milliseconds = int.Parse(GetDBString("Milliseconds", reader));
                    track.Bytes = int.Parse(GetDBString("Bytes", reader));
                    track.UnitPrice = float.Parse(GetDBString("UnitPrice", reader));
                    track.AlbumTitle = GetDBString("albumtitle", reader);
                    track.MediaType = GetDBString("mediatype", reader);
                    track.Genre = GetDBString("genre", reader);

                }
            }
            else
            {
                //do a thing
            }


            return track;
        }

        public int UpdateTrack(Track track)
        {
            bool open = this.OpenConnection();

            if (open == true)
            {
                string update_track = "";

                MySqlCommand command = new MySqlCommand(update_track, SqlConn);
                command.ExecuteNonQuery();

                return 0;
            }
            else
            {
                return 1;
            }
        }
    } // ADL: I added this ending paren.
}