/**
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;
}
}
}
}