/**
            Crea un nuevo cliente
        */
        public string createCustomer(Customer customer)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("INSERT INTO CUSTOMERS.CUSTOMER (Id, first_name, last_name, phone_number, ");
            sql.Append("email, creditcard_number, creditcard_type, status ) VALUES ( :Id, ");
            sql.Append(":first_name, :last_name, :phone_number, :email, :creditcard_number, ");
            sql.Append(":creditcard_type, :status)");

            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;
                OracleTransaction transaction;

                try
                {
                    connection.Open();
                    transaction = connection.BeginTransaction();

                    using (OracleCommand command = new OracleCommand(sql.ToString(), connection))
                    {
                        command.Parameters.Add(new OracleParameter("Id", customer.Id));
                        command.Parameters.Add(new OracleParameter("first_name", customer.first_name));
                        command.Parameters.Add(new OracleParameter("last_name", customer.last_name));
                        command.Parameters.Add(new OracleParameter("phone_number", customer.phone_number));
                        command.Parameters.Add(new OracleParameter("email", customer.email));
                        command.Parameters.Add(new OracleParameter("creditcard_number", customer.creditcard_number));
                        command.Parameters.Add(new OracleParameter("creditcard_type", customer.creditcard_type));
                        command.Parameters.Add(new OracleParameter("status", customer.status));

                        command.ExecuteNonQuery();

                        createCustomerAddress(customer.Id, customer.address, connection, transaction);

                        transaction.Commit();
                    }

                }
                catch (Exception ex)
                {
                    throw new PlatformException(ex.Message);
                }
            }

            return "OK";
        }
 public string createCustomer(Customer customer)
 {
     try
     {
         customerDAO = new CustomerDAO();
         return customerDAO.createCustomer(customer);
     }
     catch (BusinessException e)
     {
         throw e;
     }
     catch (PlatformException e)
     {
         throw e;
     }
     catch (Exception e)
     {
         throw new PlatformException(e.Message);
     }
 }
        public string createCustomer(Customer customer)
        {
            try
            {
               customerBoundary = new CustomerBoundary();

                return customerBoundary.createCustomer(customer);
            }catch(BusinessException e)
            {
                throw new WebFaultException<GeneralResponse>
                    (new GeneralResponse { message = e.Message, status = GeneralResponse.STATUS_ERROR,
                        code = "400"}, HttpStatusCode.BadRequest);
            }
            catch (PlatformException e)
            {
                throw new WebFaultException<GeneralResponse>
                    (new GeneralResponse
                    {
                        message = e.Message,
                        status = GeneralResponse.STATUS_ERROR,
                        code = "500"
                    }, HttpStatusCode.BadRequest);
            }
        }
        /**
            Actualiza los datos principales de un cliente
        */
        public string updateCustomer(Customer customer)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("UPDATE CUSTOMERS.CUSTOMER SET first_name = :first_name, last_name = :last_name, ");
            sql.Append("phone_number = :phone_number, email = :email, ");
            sql.Append("creditcard_number = :creditcard_number, creditcard_type = :creditcard_type, status = :status ");
            sql.Append("WHERE Id = :Id");

            using (OracleConnection connection = new OracleConnection())
            {
                try
                {
                    connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(sql.ToString(), connection))
                    {
                        command.Parameters.Add(new OracleParameter("Id", customer.Id));
                        command.Parameters.Add(new OracleParameter("first_name", customer.first_name));
                        command.Parameters.Add(new OracleParameter("last_name", customer.last_name));
                        command.Parameters.Add(new OracleParameter("phone_number", customer.phone_number));
                        command.Parameters.Add(new OracleParameter("email", customer.email));
                        command.Parameters.Add(new OracleParameter("creditcard_number", customer.creditcard_number));
                        command.Parameters.Add(new OracleParameter("creditcard_type", customer.creditcard_type));
                        command.Parameters.Add(new OracleParameter("status", customer.status));

                        command.ExecuteNonQuery();

                        return "OK";
                    }
                }
                catch (Exception ex)
                {
                    throw new PlatformException(ex.Message);
                }
            }
        }
        /**
            Obtiene una lista de clientes
            @pagina numero de pagina a consultar

            @regPagina numero de registros por pagina
        */
        public IList<Customer> getCustomers(int pagina, int regPagina)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT Id, first_name, last_name, phone_number, email, ");
            sql.Append("creditcard_number, creditcard_type, status FROM ");
            sql.Append("(SELECT ROW_NUMBER()Over(Order by CUSTOMERS.CUSTOMER.first_name ASC) As RowNum, ");
            sql.Append("Id, first_name, last_name, phone_number, email, ");
            sql.Append("creditcard_number, creditcard_type, status FROM CUSTOMERS.CUSTOMER) AS Resultado ");
            sql.Append("WHERE RowNum BETWEEN (:pagina -1) * :regPagina + 1 AND :pagina * :regPagina");

            IList<Customer> customers = new List<Customer>();

            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;

                using (OracleCommand command = new OracleCommand(sql.ToString(), connection))
                {

                    command.Parameters.Add(new OracleParameter("pagina", pagina));
                    command.Parameters.Add(new OracleParameter("regPagina", regPagina));

                    try
                    {
                        connection.Open();
                        OracleDataReader reader = command.ExecuteReader();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Customer customer = new Customer()
                                {
                                    Id = reader.GetString(0),
                                    first_name = reader.GetString(1),
                                    last_name = reader.GetString(2),
                                    phone_number = reader.GetString(3),
                                    email = reader.GetString(4),
                                    creditcard_number = reader.GetString(5),
                                    creditcard_type = reader.GetString(6),
                                    status = reader.GetString(7)
                                };
                                customers.Add(customer);
                            }
                            return customers;
                        }
                        else
                        {
                            throw new BusinessException("Customers result is empty");
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new PlatformException(ex.Message, ex);
                    }
                }
            }
        }
        public Customer getCustomerByEmail(string email)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT Id, first_name, last_name, phone_number, email, ");
            sql.Append("creditcard_number, creditcard_type, status FROM CUSTOMERS.CUSTOMER WHERE email=:email");

            Customer customer = new Customer();

            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;

                using (OracleCommand command = new OracleCommand(sql.ToString(), connection))
                {
                    command.Parameters.Add(new OracleParameter("email", email));
                    connection.Open();

                    try
                    {
                        OracleDataReader reader = command.ExecuteReader();
                        if (reader.Read())
                        {
                            customer = new Customer()
                            {
                                Id = reader.GetString(0),
                                first_name = reader.GetString(1),
                                last_name = reader.GetString(2),
                                phone_number = reader.GetString(3),
                                email = reader.GetString(4),
                                creditcard_number = reader.GetString(5),
                                creditcard_type = reader.GetString(6),
                                status = reader.GetString(7)
                            };
                            return customer;
                        }
                        else
                        {
                            throw new BusinessException("The customer not exist");
                        }
                    }
                    catch (PlatformException ex)
                    {
                        throw new PlatformException(ex.Message, ex);
                    }
                    catch (BusinessException ex)
                    {
                        throw ex;
                    }
                }
            }
        }