public virtual CustomerCollection GetCustomerListReceiveMessage(CustomerColumns orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
{
try
{
Database database = DatabaseFactory.CreateDatabase("CustommerServiceConnection");
DbCommand dbCommand = database.GetStoredProcCommand("spCustomerGetListReceiveMsg");
database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy.ToString());
database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection.ToString());
database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 4);
CustomerCollection customerCollection = new CustomerCollection();
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
Customer customer = CreateCustomerFromReader(reader);
customerCollection.Add(customer);
}
reader.Close();
}
totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");
return customerCollection;
}
catch (Exception ex)
{
// log this exception
log4net.Util.LogLog.Error(ex.Message, ex);
// wrap it and rethrow
throw new ApplicationException(SR.DataAccessGetCustomerListException, ex);
}
}
public JsonResult GetCustomerQueries(DTParameters parameters)
{
var columnSearch = parameters.Columns.Select(s => s.Search.Value).Take(CustomerColumns.Count()).ToList();
//XMLPath uses nested queries so to avoid that we construct these 4 filters ourselves
var sql = new PetaPoco.Sql($"Select distinct c.CustomerQueryID,ServiceId, ServiceName, Email, Phone, Substring(Query,1,100) + '...' as Query, FName, SName, CheckIn, Tdate, ServiceTypeId, NoPax, Qty from CustomerQuery c");
var fromsql = new PetaPoco.Sql();
var wheresql = new PetaPoco.Sql("where c.CustomerQueryID >0");
wheresql.Append($"{GetWhereWithOrClauseFromColumns(CustomerColumns, columnSearch)} Order By CustomerQueryID Desc ");
sql.Append(fromsql);
sql.Append(wheresql);
try
{
var res = db.Query <CustomerQuery>(sql).Skip(parameters.Start).Take(parameters.Length).ToList();
var dataTableResult = new DTResult <CustomerQuery>
{
draw = parameters.Draw,
data = res,
recordsFiltered = 10,
recordsTotal = res.Count()
};
return(Json(dataTableResult, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
throw ex;
}
}
public virtual CustomerCollection GetCustomerList(CustomerColumns orderBy, string orderDirection)
{
int totalRecords = 0;
return GetCustomerList(orderBy, orderDirection, 0, 0, out totalRecords);
}
public static CustomerCollection GetCustomerList(CustomerColumns orderBy, string orderDirection)
{
try
{
CustomerDAO customerDAO = new CustomerDAO();
return customerDAO.GetCustomerList(orderBy, orderDirection);
}
catch (ApplicationException)
{
throw;
}
catch (Exception ex)
{
// log this exception
log4net.Util.LogLog.Error(ex.Message, ex);
// wrap it and rethrow
throw new ApplicationException(SR.BusinessGetCustomerListException, ex);
}
}
public static CustomerCollection GetCustomerListSearch(int TypeId, string CustomerId, string CustomerName, string CustomerNameViet, string Email,string mobile, CustomerColumns orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
{
try
{
CustomerDAO customerDAO = new CustomerDAO();
return customerDAO.GetCustomerListSearch(TypeId, CustomerId, CustomerName, CustomerNameViet, Email,mobile, orderBy, orderDirection, page, pageSize, out totalRecords);
}
catch (ApplicationException)
{
throw;
}
catch (Exception ex)
{
// log this exception
log4net.Util.LogLog.Error(ex.Message, ex);
// wrap it and rethrow
throw new ApplicationException(SR.BusinessGetCustomerListException, ex);
}
}
