/// <summary>
/// Customer Inquiry
/// </summary>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="paging"></param>
/// <returns></returns>
public List<CustomerInquiry> CustomerInquiry(string firstName, string lastName, DataGridPagingInformation paging)
{
string sortExpression = paging.SortExpression;
if (paging.SortDirection != string.Empty)
sortExpression = sortExpression + " " + paging.SortDirection;
var customerQuery = Customers.AsQueryable();
if (firstName != null && firstName.Trim().Length > 0)
{
customerQuery = customerQuery.Where(c => c.FirstName.StartsWith(firstName));
}
if (lastName != null && lastName.Trim().Length > 0)
{
customerQuery = customerQuery.Where(c => c.LastName.StartsWith(lastName));
}
var customers = from c in customerQuery
join p in PaymentTypes on c.PaymentTypeID equals p.PaymentTypeID
select new { c.CustomerID, c.FirstName, c.LastName, c.EmailAddress, c.City, p.Description };
int numberOfRows = customers.Count();
customers = customers.OrderBy(sortExpression);
var customerList = customers.Skip((paging.CurrentPageNumber - 1) * paging.PageSize).Take(paging.PageSize);
paging.TotalRows = numberOfRows;
paging.TotalPages = Utilities.CalculateTotalPages(numberOfRows, paging.PageSize);
List<CustomerInquiry> customerInquiry = new List<CustomerInquiry>();
foreach (var customer in customerList)
{
CustomerInquiry customerData = new CustomerInquiry();
customerData.CustomerID = customer.CustomerID;
customerData.FirstName = customer.FirstName;
customerData.LastName = customer.LastName;
customerData.EmailAddress = customer.EmailAddress;
customerData.City = customer.City;
customerData.PaymentTypeDescription = customer.Description;
customerInquiry.Add(customerData);
}
return customerInquiry;
}
public void CustomerInquiryIntegrationTest()
{
TransactionalInformation transaction;
CustomerApplicationService customerApplicationService = new CustomerApplicationService(customerDataService);
DataGridPagingInformation paging = new DataGridPagingInformation();
paging.CurrentPageNumber = 1;
paging.PageSize = 15;
paging.SortExpression = "LastName";
paging.SortDirection = "ASC";
List<CustomerInquiry> customers = customerApplicationService.CustomerInquiry("", "", paging, out transaction);
string returnMessage = Utilities.GetReturnMessage(transaction.ReturnMessage);
Assert.AreEqual(15, customers.Count, returnMessage);
}
public HttpResponseMessage CustomerInquiry(string FirstName, string LastName, int CurrentPageNumber, string SortExpression, string SortDirection, int PageSize)
{
TransactionalInformation transaction;
if (FirstName == null) FirstName = string.Empty;
if (LastName == null) LastName = string.Empty;
if (SortDirection == null) SortDirection = string.Empty;
if (SortExpression == null) SortExpression = string.Empty;
CustomerInquiryViewModel customerInquiryViewModel = new CustomerInquiryViewModel();
DataGridPagingInformation paging = new DataGridPagingInformation();
paging.CurrentPageNumber = CurrentPageNumber;
paging.PageSize = PageSize;
paging.SortExpression = SortExpression;
paging.SortDirection = SortDirection;
if (paging.SortDirection == "") paging.SortDirection = "ASC";
if (paging.SortExpression == "") paging.SortExpression = "LastName";
CustomerApplicationService customerApplicationService = new CustomerApplicationService(customerDataService);
List<CustomerInquiry> customers = customerApplicationService.CustomerInquiry(FirstName, LastName, paging, out transaction);
customerInquiryViewModel.Customers = customers;
customerInquiryViewModel.ReturnStatus = transaction.ReturnStatus;
customerInquiryViewModel.ReturnMessage = transaction.ReturnMessage;
customerInquiryViewModel.TotalPages = paging.TotalPages;
customerInquiryViewModel.TotalRows = paging.TotalRows;
customerInquiryViewModel.PageSize = paging.PageSize;
if (transaction.ReturnStatus == true)
{
var response = Request.CreateResponse<CustomerInquiryViewModel>(HttpStatusCode.OK, customerInquiryViewModel);
return response;
}
var badResponse = Request.CreateResponse<CustomerInquiryViewModel>(HttpStatusCode.BadRequest, customerInquiryViewModel);
return badResponse;
}
/// <summary>
/// Customer Inquiry
/// </summary>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="paging"></param>
/// <returns></returns>
public List<CustomerInquiry> CustomerInquiry(string firstName, string lastName, DataGridPagingInformation paging)
{
List<Customer> customers = new List<Customer>();
string sortExpression = paging.SortExpression;
int maxRowNumber;
int minRowNumber;
minRowNumber = (paging.PageSize * (paging.CurrentPageNumber - 1)) + 1;
maxRowNumber = paging.PageSize * paging.CurrentPageNumber;
StringBuilder sqlBuilder = new StringBuilder();
StringBuilder sqlWhereBuilder = new StringBuilder();
string sqlWhere = string.Empty;
if (firstName != null && firstName.Trim().Length > 0)
sqlWhereBuilder.Append(" c.FirstName LIKE @FirstName AND ");
if (lastName != null && lastName.Trim().Length > 0)
sqlWhereBuilder.Append(" c.LastName LIKE @LastName AND ");
if (sqlWhereBuilder.Length > 0)
sqlWhere = " WHERE " + sqlWhereBuilder.ToString().Substring(0, sqlWhereBuilder.Length - 4);
sqlBuilder.Append(" SELECT COUNT(*) as total_records FROM Customers c ");
sqlBuilder.Append(sqlWhere);
sqlBuilder.Append(";");
sqlBuilder.Append(" SELECT * FROM ( ");
sqlBuilder.Append(" SELECT (ROW_NUMBER() OVER (ORDER BY " + paging.SortExpression + " " + paging.SortDirection + ")) as record_number, ");
sqlBuilder.Append(" c.*, p.Description as PaymentTypeDescription ");
sqlBuilder.Append(" FROM Customers c ");
sqlBuilder.Append(" INNER JOIN PaymentTypes p ON p.PaymentTypeID = c.PaymentTypeID ");
sqlBuilder.Append(sqlWhere);
sqlBuilder.Append(" ) Rows ");
sqlBuilder.Append(" where record_number between " + minRowNumber + " and " + maxRowNumber);
string sql = sqlBuilder.ToString();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = sql;
sqlCommand.Connection = dbConnection;
if (firstName != null && firstName.Trim().Length > 0)
{
sqlCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.VarChar);
sqlCommand.Parameters["@FirstName"].Value = firstName + "%";
}
if (lastName != null && lastName.Trim().Length > 0)
{
sqlCommand.Parameters.Add("@LastName", System.Data.SqlDbType.VarChar);
sqlCommand.Parameters["@LastName"].Value = lastName + "%";
}
SqlDataReader reader = sqlCommand.ExecuteReader();
reader.Read();
paging.TotalRows = Convert.ToInt32(reader["Total_Records"]);
paging.TotalPages = Utilities.CalculateTotalPages(paging.TotalRows, paging.PageSize);
reader.NextResult();
List<CustomerInquiry> customerList = new List<CustomerInquiry>();
while (reader.Read())
{
CustomerInquiry customer = new CustomerInquiry();
DataReader dataReader = new DataReader(reader);
customer.CustomerID = dataReader.GetGuid("CustomerID");
customer.FirstName = dataReader.GetString("FirstName");
customer.LastName = dataReader.GetString("LastName");
customer.EmailAddress = dataReader.GetString("EmailAddress");
customer.City = dataReader.GetString("City");
customer.PaymentTypeDescription = dataReader.GetString("PaymentTypeDescription");
customerList.Add(customer);
}
reader.Close();
return customerList;
}
public void UpdateCustomerIntegrationTest()
{
string returnMessage;
TransactionalInformation transaction;
CustomerApplicationService customerApplicationService = new CustomerApplicationService(customerDataService);
DataGridPagingInformation paging = new DataGridPagingInformation();
paging.CurrentPageNumber = 1;
paging.PageSize = 15;
paging.SortExpression = "LastName";
paging.SortDirection = "ASC";
List<CustomerInquiry> customers = customerApplicationService.CustomerInquiry("", "", paging, out transaction);
var customerInformation = (from c in customers select c).First();
Guid customerID = customerInformation.CustomerID;
Customer customer = customerApplicationService.GetCustomerByCustomerID(customerID, out transaction);
customerApplicationService.UpdateCustomer(customer, out transaction);
returnMessage = Utilities.GetReturnMessage(transaction.ReturnMessage);
Assert.AreEqual(true, transaction.ReturnStatus, returnMessage);
}
/// <summary>
/// Customer Inquiry
/// </summary>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="paging"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public List<CustomerInquiry> CustomerInquiry(string firstName, string lastName, DataGridPagingInformation paging, out TransactionalInformation transaction)
{
transaction = new TransactionalInformation();
try
{
CustomerDataService.CreateSession();
List<CustomerInquiry> customers = CustomerDataService.CustomerInquiry(firstName, lastName, paging);
transaction.ReturnStatus = true;
return customers;
}
catch (Exception ex)
{
transaction.ReturnMessage = new List<string>();
string errorMessage = ex.Message;
transaction.ReturnStatus = false;
transaction.ReturnMessage.Add(errorMessage);
return null;
}
finally
{
CustomerDataService.CloseSession();
}
}
