public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string queryParameters = null) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
context.Configuration.LazyLoadingEnabled = false;
string[] ids = queryParameters.Split(',');
List <CMDDashboardProductAndContact> customerProductContactListResult = null;
int practiceId = Convert.ToInt32(ids[0]);
int businessUnitId = Convert.ToInt32(ids[1]);
try
{
IQueryable <CMDDashboardProductAndContact> cmdDashboardCustomerProductAndContactList = CreateSelectQuery(context, practiceId, businessUnitId);
customerProductContactListResult = cmdDashboardCustomerProductAndContactList.ToList();
CMDLogger.LogAudit("Obtained the Product List by Practice ID from CMD", ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(customerProductContactListResult as List <T>);
}
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string queryParameters = null) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
context.Configuration.LazyLoadingEnabled = false;
int practiceId = Convert.ToInt32(queryParameters);
List <CMDDashboardCustomerBusinessUnit> customerPracticeListResult = null;
try
{
IQueryable <CMDDashboardCustomerBusinessUnit> cmdDashboardPracticeProductList = CreateSelectQuery(context, practiceId);
if (!string.IsNullOrEmpty(whereCondition))
{
cmdDashboardPracticeProductList = cmdDashboardPracticeProductList.Where(whereCondition);
}
orderByCondition = !string.IsNullOrEmpty(orderByCondition) ? orderByCondition : "Id";
cmdDashboardPracticeProductList = cmdDashboardPracticeProductList.OrderBy(orderByCondition).Skip(skip).Take(take);
customerPracticeListResult = cmdDashboardPracticeProductList.ToList();
CMDLogger.LogAudit("Obtained the BusinessUnit List by Practice ID from CMD", ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(customerPracticeListResult as List <T>);
}
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string queryParameters = null) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
context.Configuration.LazyLoadingEnabled = false;
List <CMDDashboardProduct> productListResult = null;
int productID = Convert.ToInt32(queryParameters);
try
{
IQueryable <CMDDashboardProduct> cmdDashboardProductList =
(context.CMDProducts
.Where(cmdProduct => cmdProduct.Id == productID && cmdProduct.IsActive == true)
.Select(product => new CMDDashboardProduct()
{
Id = product.Id,
Name = product.Name,
CMDProductTypeID = product.CMDProductTypeID,
CMDBusinessUnitID = product.CMDBusinessUnitID,
SKU = product.SKU,
IsActive = product.IsActive,
CreatedDate = product.CreatedDate,
UpdatedDate = product.UpdatedDate,
UpdatedBy = product.UpdatedBy,
CMDBusinessUnit = product.CMDBusinessUnit,
CMDProductType = product.CMDProductType,
CMDProductTypeName = product.CMDProductType.TypeName,
CMDBusinessUnitName = product.CMDBusinessUnit.Name
})
);
productListResult = cmdDashboardProductList.ToList();
CMDLogger.LogAudit("Obtained the Product List by Product ID from CMD", ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(productListResult as List <T>);
}
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string Id = null) where T : class
{
context.Configuration.LazyLoadingEnabled = false;
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
int businessUnitID = Convert.ToInt32(Id);
List <CMDDashboardProduct> customerProductContactListResult = null;
try
{
IQueryable <CMDDashboardProduct> cmdDashboardBusinessUnitProductList =
(context.CMDBusinessUnits
.Where(businessUnit => businessUnit.Id == (businessUnitID) && businessUnit.IsActive == true)
.SelectMany(recordBusinessUnit => recordBusinessUnit.CMDProducts)
.Select(cmdProduct => new CMDDashboardProduct()
{
CMDBusinessUnitID = cmdProduct.CMDBusinessUnitID,
CMDBusinessUnit = cmdProduct.CMDBusinessUnit,
CMDProductType = cmdProduct.CMDProductType,
CMDProductTypeID = cmdProduct.CMDProductTypeID,
CreatedDate = cmdProduct.CreatedDate,
Id = cmdProduct.Id,
IsActive = cmdProduct.IsActive,
Name = cmdProduct.Name,
SKU = cmdProduct.SKU,
UpdatedBy = cmdProduct.UpdatedBy,
UpdatedDate = cmdProduct.UpdatedDate
}));
if (!string.IsNullOrEmpty(whereCondition))
{
cmdDashboardBusinessUnitProductList = cmdDashboardBusinessUnitProductList.Where(whereCondition);
}
if (!string.IsNullOrEmpty(orderByCondition))
{
cmdDashboardBusinessUnitProductList = cmdDashboardBusinessUnitProductList.OrderBy(orderByCondition).Skip(skip).Take(take);
}
else
{
cmdDashboardBusinessUnitProductList = cmdDashboardBusinessUnitProductList.OrderBy("Id").Skip(skip).Take(take);
}
customerProductContactListResult = cmdDashboardBusinessUnitProductList.ToList();
CMDLogger.LogAudit("Obtained the Product List by BusinessUnit ID from CMD", ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(customerProductContactListResult as List <T>);
}
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0) where T : class
{
context.Configuration.LazyLoadingEnabled = false;
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
List <CMDDashboardCustomer> existingCustomerListResult = null;
try
{
IQueryable <CMDDashboardCustomer> cmdDashboardExistingCustomerList = CreateSelectQuery(context);
if (!string.IsNullOrEmpty(whereCondition))
{
cmdDashboardExistingCustomerList = cmdDashboardExistingCustomerList.Where(whereCondition);
}
existingCustomerListResult = cmdDashboardExistingCustomerList.ToList();
if (existingCustomerListResult != null)
{
existingCustomerListResult =
cmdDashboardExistingCustomerList.ToList()
.GroupBy(existingCustomer => existingCustomer.CustomerID)
.Select(recordCustomer => recordCustomer.First()).ToList();
var countCustomer = existingCustomerListResult.Count();
#region Order By Condition
orderByCondition = !string.IsNullOrEmpty(orderByCondition) ? orderByCondition : "CustomerID";
cmdDashboardExistingCustomerList = cmdDashboardExistingCustomerList.OrderBy(orderByCondition).Skip(skip).Take(take);
#endregion
existingCustomerListResult =
cmdDashboardExistingCustomerList.ToList()
.GroupBy(existingCustomer => existingCustomer.CustomerID)
.Select(recordCustomer => recordCustomer.First()).ToList();
existingCustomerListResult.All(m => { m.CustomersCount = countCustomer; return(true); });
}
CMDLogger.LogAudit("Obtained the Customers List having Duplicate Customers record in CMD", ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(existingCustomerListResult as List <T>);
}
/// <summary>
/// This method returns a list of CMDDashboardSourceRecord for the practice whose Id is passed.
/// </summary>
/// <typeparam name="T">Type of object returned by RPC, here CMDDashboardSourceRecord</typeparam>
/// <param name="context">Obj. of CMDDatabaseContext</param>
/// <param name="whereCondition">where condition string</param>
/// <param name="orderByCondition">order by condition string</param>
/// <param name="skip">No. of records to be skipped</param>
/// <param name="take">No. of records to be taken</param>
/// <param name="queryParameters">PracticeId</param>
/// <returns></returns>
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string queryParameters = null) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
context.Configuration.LazyLoadingEnabled = false;
List <CMDDashboardSourceRecord> cmdPracticedSourceRecordListResult = null;
try
{
if (!string.IsNullOrEmpty(queryParameters))
{
int practiceId = Convert.ToInt32(queryParameters);
if (practiceId <= 0)
{
return(cmdPracticedSourceRecordListResult as List <T>);
}
IQueryable <CMDDashboardSourceRecord> cmdDashboardSourceRecordList = CreateSelectQuery(context, practiceId);
if (!string.IsNullOrEmpty(whereCondition))
{
cmdDashboardSourceRecordList = cmdDashboardSourceRecordList.Where(whereCondition);
}
if (!string.IsNullOrEmpty(orderByCondition))
{
cmdDashboardSourceRecordList = cmdDashboardSourceRecordList.OrderBy(orderByCondition).Skip(skip).Take(take);
}
else
{
cmdDashboardSourceRecordList = cmdDashboardSourceRecordList.OrderBy("CreatedDate").Skip(skip).Take(take);
}
cmdPracticedSourceRecordListResult = cmdDashboardSourceRecordList.ToList();
CMDLogger.LogAudit(string.Format("Obtained the CMD Source Track Record List by {0} ID from CMD", ALPHAEON.CMD.Common.Enums.Entity.CMDPractice), ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(cmdPracticedSourceRecordListResult as List <T>);
}
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0) where T : class
{
context.Configuration.LazyLoadingEnabled = false;
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
List <CMDDashboardPractice> existingPracticeListResult = null;
try
{
IQueryable <CMDDashboardPractice> cmdDashboardExistingPracticeList = CreateSelectQuery(context);
if (!string.IsNullOrEmpty(whereCondition))
{
cmdDashboardExistingPracticeList = cmdDashboardExistingPracticeList.Where(whereCondition);
}
//// Order By Condition
orderByCondition = !string.IsNullOrEmpty(orderByCondition) ? orderByCondition : "Id";
cmdDashboardExistingPracticeList = cmdDashboardExistingPracticeList.OrderBy(orderByCondition).Skip(skip).Take(take);
existingPracticeListResult = cmdDashboardExistingPracticeList.ToList();
if (existingPracticeListResult != null)
{
existingPracticeListResult =
cmdDashboardExistingPracticeList.ToList()
.GroupBy(existingPractice => existingPractice.Id)
.Select(recordPractice => recordPractice.First()).ToList();
}
CMDLogger.LogAudit("Obtained the Duplicate Practices List by Practice ID from CMD", ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(existingPracticeListResult as List <T>);
}
/// <summary>
/// This method returns list of CMDDashboardGlobalSearch objects, as a result of search on Practice and Customers
/// </summary>
/// <typeparam name="T">Type of list. CMDDashboardGlobalSearch</typeparam>
/// <param name="context">CMD DB Context</param>
/// <param name="whereCondition">Where condition need to apply on result</param>
/// <param name="orderByCondition">Order by condition need to apply on result</param>
/// <param name="skip">Number of records need to skip from result</param>
/// <param name="take">Number of records need to take from result</param>
/// <param name="queryParameters"></param>
/// <returns>List of GLobal serach result</returns>
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string queryParameters = null) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
var cmdGlobalSearchList = new List <CMDDashboardGlobalSearchResult>();
const string CMDEntityName = "CMDEntityName";
int customerSearchResultCount = 0;
int practiceSearchResultCount = 0;
int alternateSkip = 0;
int alternateTake = 0;
try
{
//// Split query parametes and find entity type(CMDCustomer, CMDPractice ) and serahc condition
if (!string.IsNullOrEmpty(queryParameters))
{
int entityTypeId = 0;
string searchString = string.Empty;
if (queryParameters.Contains('|'))
{
string[] parameters = queryParameters.Split('|');
entityTypeId = Convert.ToInt32(parameters[0]);
searchString = parameters[1];
if (parameters.Length > 2)
{
alternateSkip = Convert.ToInt32(parameters[2]);
alternateTake = Convert.ToInt32(parameters[3]);
}
}
else
{
searchString = queryParameters;
}
//// Replcae special characters like "&", "#" to ";amp;", ";tmp;" and vise-versa
whereCondition = whereCondition.ToggleAmpersand();
IQueryable <CMDDashboardGlobalSearch> customerSearchQuery = null;
IQueryable <CMDDashboardGlobalSearch> practiceSearchQuery = null;
var searchStringList = GetSearchTerms(searchString);
//// Find query to search customer/practice
switch (entityTypeId)
{
case (int)ALPHAEON.CMD.Common.Enums.Entity.CMDCustomer:
customerSearchQuery = GetCustomerSearchQuery(context, searchStringList, whereCondition);
break;
case (int)ALPHAEON.CMD.Common.Enums.Entity.CMDPractice:
practiceSearchQuery = GetPracticeSearchQuery(context, searchStringList, whereCondition);
break;
default:
customerSearchQuery = GetCustomerSearchQuery(context, searchStringList, whereCondition);
practiceSearchQuery = GetPracticeSearchQuery(context, searchStringList, whereCondition);
break;
}
customerSearchResultCount = customerSearchQuery != null?customerSearchQuery.Count() : 0;
practiceSearchResultCount = practiceSearchQuery != null?practiceSearchQuery.Count() : 0;
#region OrderBy Condition
if (!string.IsNullOrEmpty(orderByCondition))
{
if (entityTypeId == (int)ALPHAEON.CMD.Common.Enums.Entity.CMDCustomer || entityTypeId == 0)
{
if (orderByCondition.IndexOf(CMDEntityName, StringComparison.OrdinalIgnoreCase) != -1)
{
if (customerSearchQuery != null)
{
customerSearchQuery = GetCustomerListWithOrderByCondition(orderByCondition, skip, take, customerSearchQuery);
}
}
else
{
if (customerSearchQuery != null)
{
customerSearchQuery = customerSearchQuery.OrderBy(orderByCondition).Skip(skip).Take(take);
}
}
if (entityTypeId == 0)
{
if (practiceSearchQuery != null)
{
practiceSearchQuery = practiceSearchQuery.OrderBy(orderByCondition).Skip(skip).Take(take);
}
}
else
{
if (practiceSearchQuery != null)
{
practiceSearchQuery = practiceSearchQuery.OrderBy(CMDEntityName).Skip(alternateSkip).Take(alternateTake);
}
}
}
if (entityTypeId == (int)ALPHAEON.CMD.Common.Enums.Entity.CMDPractice)
{
if (practiceSearchQuery != null)
{
practiceSearchQuery = practiceSearchQuery.OrderBy(orderByCondition).Skip(skip).Take(take);
}
if (customerSearchQuery != null)
{
customerSearchQuery = customerSearchQuery.OrderBy(CMDEntityName).Skip(alternateSkip).Take(alternateTake);
}
}
}
else
{
if (customerSearchQuery != null)
{
customerSearchQuery = GetCustomerListWithOrderByCondition(orderByCondition, skip, take, customerSearchQuery);
}
if (practiceSearchQuery != null)
{
practiceSearchQuery = practiceSearchQuery.OrderBy(CMDEntityName).Skip(skip).Take(take);
}
}
#endregion OrderBy Condition
//// Add seach result, counts to final list
cmdGlobalSearchList.Add(
new CMDDashboardGlobalSearchResult()
{
SearchResultCustomerList = customerSearchQuery != null ? customerSearchQuery.ToList() : null,
SearchResultPracticeList = practiceSearchQuery != null ? practiceSearchQuery.ToList() : null,
CustomerSearchResultCount = customerSearchResultCount,
PracticeSearchResultCount = practiceSearchResultCount,
SearchResultCount = customerSearchResultCount + practiceSearchResultCount
});
CMDLogger.LogAudit("Obtained the Global Search result from CMD DB", ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(cmdGlobalSearchList as List <T>);
}
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
List <CMDDashboardBusinessUnit> businessUnitListResult = null;
context.Configuration.LazyLoadingEnabled = false;
try
{
IQueryable <CMDDashboardBusinessUnit> businessUnitList =
(from cmdBusinessUnit in context.CMDBusinessUnits
select new CMDDashboardBusinessUnit()
{
Id = cmdBusinessUnit.Id,
ContactPerson = cmdBusinessUnit.ContactPerson,
Description = cmdBusinessUnit.Description,
CMDContact = cmdBusinessUnit.CMDContact,
CMDContactID = cmdBusinessUnit.CMDContactID,
Name = cmdBusinessUnit.Name,
WebURL = cmdBusinessUnit.CMDContact.WebsiteURL,
CustomerSupportNumber = cmdBusinessUnit.CMDContact.Phone,
CustomerSupportEmail = cmdBusinessUnit.CMDContact.PublicEmail,
UpdatedDate = cmdBusinessUnit.UpdatedDate,
IsActive = cmdBusinessUnit.IsActive,
CreatedDate = cmdBusinessUnit.CreatedDate,
UpdatedBy = cmdBusinessUnit.UpdatedBy,
BusinessUnitType = cmdBusinessUnit.BusinessUnitType,
CMDBusinessUnitProductListCount =
(context.CMDBusinessUnits)
.Where(businessUnit => businessUnit.Id == (cmdBusinessUnit.Id) && businessUnit.IsActive == true)
.SelectMany(recordBusinessUnit => recordBusinessUnit.CMDProducts).Where(cmdProductRecord => cmdProductRecord.IsActive == true).Distinct().Count(),
});
if (!string.IsNullOrEmpty(whereCondition))
{
businessUnitList = businessUnitList.Where(whereCondition);
}
if (!string.IsNullOrEmpty(orderByCondition))
{
businessUnitList = businessUnitList.OrderBy(orderByCondition).Skip(skip).Take(take);
}
else
{
businessUnitList = businessUnitList.OrderBy("Id").Skip(skip).Take(take);
}
businessUnitListResult = businessUnitList.ToList();
CMDLogger.LogAudit("Obtained the BusinessUnit List from CMD", ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(businessUnitListResult as List <T>);
}
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string queryParameters = null) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
context.Configuration.LazyLoadingEnabled = false;
int practiceId = 0;
List <CMDDashboardTransaction> customerPracticeListResult = null;
try
{
if (!string.IsNullOrEmpty(queryParameters) && Convert.ToInt32(queryParameters) > 0)
{
practiceId = Convert.ToInt32(queryParameters);
}
else
{
return(customerPracticeListResult as List <T>);
}
#region Select Query
IQueryable <CMDDashboardTransaction> cmdDashboardPracticeTransactionList = null;
var queyGetCMDTransactionListByPracticeId = ApplicationServices.LINQQueries.CMDTransaction.GetQueryTransactionListByCMDPracticeId(context, practiceId);
cmdDashboardPracticeTransactionList = GetCMDDashboardPracticeTransactionList(context, queyGetCMDTransactionListByPracticeId);
#endregion Select Query
#region Where Condition
//// Create where condition
CreateWhereCondition(ref whereCondition, ref cmdDashboardPracticeTransactionList);
#endregion Where Condition
var countTransaction = cmdDashboardPracticeTransactionList.Count();
take = take == 0 ? countTransaction : take;
#region OrderBy Condition
orderByCondition = !string.IsNullOrEmpty(orderByCondition) ? orderByCondition : "Id";
cmdDashboardPracticeTransactionList = cmdDashboardPracticeTransactionList.OrderBy(orderByCondition).Skip(skip).Take(take);
#endregion OrderBy Condition
customerPracticeListResult = cmdDashboardPracticeTransactionList.ToList();
if (customerPracticeListResult != null)
{
var totalTransactionAmount = customerPracticeListResult.Sum(t => t.TotalAmount);
customerPracticeListResult.All(m =>
{
m.TransactionCount = countTransaction;
m.TotalTransactionAmount = totalTransactionAmount;
return(true);
});
}
CMDLogger.LogAudit("Obtained the TransactionList by PracticeID from CMD", ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(customerPracticeListResult as List <T>);
}
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string queryParameters = null) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
context.Configuration.LazyLoadingEnabled = false;
int customerId = 0;
List <CMDDashboardTransaction> customerTransactionListResult = null;
try
{
if (!string.IsNullOrEmpty(queryParameters))
{
customerId = Convert.ToInt32(queryParameters);
}
#region Select Query
IQueryable <CMDDashboardTransaction> cmdDashboardCustomerTransactionList = CreateSelectQuery(context, customerId);
#endregion Select Query
#region Where Condition
if (!string.IsNullOrEmpty(whereCondition))
{
#region FilterByMonths
string filterByMonthString = string.Empty;
if (whereCondition.Contains("months|"))
{
filterByMonthString = CommonUtility.GetFilterByStartEndParameters(ref whereCondition, "months|", "|months");
}
if (!string.IsNullOrEmpty(filterByMonthString))
{
string[] filterByMonthArray = filterByMonthString.Split(',');
int month = DateTime.Now.Month;
if (!string.IsNullOrEmpty(filterByMonthArray[0]))
{
month = Convert.ToInt32(filterByMonthArray[0]);
}
int year = DateTime.Now.Year;
if (!string.IsNullOrEmpty(filterByMonthArray[1]))
{
year = Convert.ToInt32(filterByMonthArray[1]);
}
cmdDashboardCustomerTransactionList = cmdDashboardCustomerTransactionList.Where(t => t.TxDate != null && t.TxDate.Value.Month == month && t.TxDate.Value.Year == year);
}
#endregion
#region FilterByperiod
string filterByYearString = string.Empty;
if (whereCondition.Contains("period|"))
{
filterByYearString = CommonUtility.GetFilterByStartEndParameters(ref whereCondition, "period|", "|period");
}
if (!string.IsNullOrEmpty(filterByYearString))
{
string[] filterByYearArray = filterByYearString.Split(',');
var startDate = DateTime.MinValue;
if (!string.IsNullOrEmpty(filterByYearArray[0]))
{
startDate = Convert.ToDateTime(filterByYearArray[0]);
}
var endDate = DateTime.MinValue;
if (!string.IsNullOrEmpty(filterByYearArray[1]))
{
endDate = Convert.ToDateTime(filterByYearArray[1]);
}
// filter the transaction nullable date b/w start and end date.
cmdDashboardCustomerTransactionList = cmdDashboardCustomerTransactionList.Where(t => t.TxDate.HasValue && System.Data.Entity.DbFunctions.TruncateTime(t.TxDate) >= startDate.Date && System.Data.Entity.DbFunctions.TruncateTime(t.TxDate) <= endDate.Date);
}
#endregion
if (!string.IsNullOrEmpty(whereCondition))
{
cmdDashboardCustomerTransactionList = cmdDashboardCustomerTransactionList.Where(whereCondition);
}
}
#endregion Where Condition
var countTransaction = cmdDashboardCustomerTransactionList.Count();
take = take == 0 ? countTransaction : take;
#region OrderBy Condition
orderByCondition = !string.IsNullOrEmpty(orderByCondition) ? orderByCondition : "Id";
cmdDashboardCustomerTransactionList = cmdDashboardCustomerTransactionList.OrderBy(orderByCondition).Skip(skip).Take(take);
#endregion OrderBy Condition
customerTransactionListResult = cmdDashboardCustomerTransactionList.ToList();
if (customerTransactionListResult != null)
{
var totalTransactionAmount = customerTransactionListResult.Sum(t => t.TotalAmount);
customerTransactionListResult.All(m =>
{
m.TransactionCount = countTransaction; m.TotalTransactionAmount = totalTransactionAmount;
return(true);
});
}
CMDLogger.LogAudit("Obtained the TransactionList by Customer ID from CMD", ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(customerTransactionListResult as List <T>);
}
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string queryParameters = null) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
////check if search string contains ;amp; , then replace with &
whereCondition = whereCondition.ToggleAmpersand();
int duplicateCustomerId = 0;
if (!string.IsNullOrEmpty(queryParameters))
{
duplicateCustomerId = Convert.ToInt32(queryParameters);
}
context.Configuration.LazyLoadingEnabled = false;
IQueryable <CMDDashboardCustomer> customerList = null;
List <CMDDashboardCustomer> customerListResult = new List <CMDDashboardCustomer>();
int countCustomer = 0;
try
{
#region Data for Duplicate Customer List View
customerList =
from cmdCust in context.CMDCustomers
select new CMDDashboardCustomer()
{
CustomerID = cmdCust.Id,
FirstName = cmdCust.FirstName,
MiddleName = cmdCust.MiddleName,
LastName = cmdCust.LastName,
IsActive = cmdCust.IsActive,
IsDuplicate = cmdCust.IsDuplicate,
FullName = cmdCust.FirstName.ToLower().Trim().Replace(" ", string.Empty).Replace("\"", string.Empty) + " "
+ (string.IsNullOrEmpty(cmdCust.MiddleName) ? string.Empty : cmdCust.MiddleName.ToLower().Trim().Replace(" ", string.Empty).Replace("\"", string.Empty) + " ")
+ cmdCust.LastName.ToLower().Trim().Replace(" ", string.Empty).Replace("\"", string.Empty),
OriginalBusinessUnitID = cmdCust.OriginalBusinessUnitID,
OriginalBusinessUnitName = context.CMDBusinessUnits
.Where(cmdBusinessUnit => cmdBusinessUnit.IsActive && cmdBusinessUnit.Id == cmdCust.OriginalBusinessUnitID)
.Select(recordCMDBusinessUnit => recordCMDBusinessUnit.Name).FirstOrDefault(),
////If the selected customer is duplicate customer then, give count of Existing Customers for which the selected customer is duplicate
ExistingCustomersCountforSelectedDuplicate =
context.CMDCustomerDuplicateMap
.Where(cmdCustomerDuplicateMap => cmdCustomerDuplicateMap.IsActive && cmdCustomerDuplicateMap.ResolveAction == null &&
cmdCustomerDuplicateMap.DuplicateCustomerID == cmdCust.Id)
.Join(
context.CMDCustomers,
recordCMDCustomerDuplicateMap => recordCMDCustomerDuplicateMap.ExistingCustomerID,
recordCMDCustomer => recordCMDCustomer.Id,
(recordCMDCustomerDuplicateMap, recordCMDCustomer) => recordCMDCustomer)
.Where(recordCMDCust => recordCMDCust.IsActive).Count(),
};
#endregion
if (customerList != null)
{
if (!string.IsNullOrEmpty(whereCondition))
{
#region Search
// Find search string
string searchString = string.Empty;
if (whereCondition.Contains(ALPHAEON.CMD.Common.Constants.Dashboard.CustomerSearchStartFilter))
{
int posA = whereCondition.IndexOf(ALPHAEON.CMD.Common.Constants.Dashboard.CustomerSearchStartFilter);
int posB = whereCondition.LastIndexOf(ALPHAEON.CMD.Common.Constants.Dashboard.CustomerSearchEndFilter);
int adjustedPosA = posA + ALPHAEON.CMD.Common.Constants.Dashboard.CustomerSearchStartFilter.Length;
searchString = whereCondition.Substring(adjustedPosA, posB - adjustedPosA);
whereCondition = whereCondition.Replace(ALPHAEON.CMD.Common.Constants.Dashboard.CustomerSearchStartFilter + searchString + ALPHAEON.CMD.Common.Constants.Dashboard.CustomerSearchEndFilter, string.Empty);
}
// Find the customer by full name...
// If customer can not found by full name then find by first name, middle name and last name.
if (!string.IsNullOrEmpty(searchString))
{
string searchCondition = whereCondition + " AND FullName.Contains(\"" + searchString.Trim().ToggleAmpersand().Replace("\"", string.Empty) + "\")";
// Find customer by fullname
if (customerList.Where(searchCondition).ToList().Count() > 0)
{
customerList = customerList.Where(searchCondition);
}
else
{
// Variable to hold the first, middle and last name
string[] nameArray = searchString.Split(' ');
string searchByNameCondition = string.Empty;
// Create 'OR' condition for first name, middle name and last name
if (nameArray.Count() > 0)
{
foreach (string name in nameArray)
{
if (!string.IsNullOrEmpty(name))
{
if (!string.IsNullOrEmpty(searchByNameCondition))
{
searchByNameCondition += " OR FullName.Contains(\"" + name.ToggleAmpersand().Trim().Replace("\"", string.Empty) + "\")";
}
else
{
searchByNameCondition = "FullName.Contains(\"" + name.ToggleAmpersand().Trim().Replace("\"", string.Empty) + "\")";
}
}
}
}
if (!string.IsNullOrEmpty(searchByNameCondition))
{
searchCondition = whereCondition + " AND (" + searchByNameCondition + ")";
}
else
{
searchCondition = whereCondition;
}
}
// Find customer
customerList = customerList.Where(searchCondition);
}
else
{
customerList = customerList.Where(whereCondition);
}
#endregion
}
countCustomer = customerList.Count();
if (countCustomer > 0)
{
if (!string.IsNullOrEmpty(orderByCondition))
{
if (orderByCondition.Contains("FirstName"))
{
string[] orderByArray = orderByCondition.Trim().Split(' ');
if (orderByArray.Count() > 1)
{
string order = orderByArray[1].ToLower().Trim();
if (order.Equals("asc"))
{
customerList = customerList.OrderBy(x => x.FirstName).ThenBy(x => x.MiddleName).ThenBy(x => x.LastName).Skip(skip).Take(take);
}
else
{
customerList = customerList.OrderByDescending(x => x.FirstName).ThenByDescending(x => x.MiddleName).ThenByDescending(x => x.LastName).Skip(skip).Take(take);
}
}
}
else
{
customerList = customerList.OrderBy(orderByCondition).Skip(skip).Take(take);
}
}
else
{
customerList = customerList.OrderBy("CustomerID").Skip(skip).Take(take);
}
customerListResult = customerList.ToList();
}
if (customerListResult != null && customerListResult.Count > 0)
{
customerListResult.All(m => { m.CustomersCount = countCustomer; return(true); });
}
CMDLogger.LogAudit(string.Format("Obtained the Customer List from CMD DB for view Type Duplicate List View"), ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(customerListResult as List <T>);
}
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
////check if search string contains ;amp; , then replace with &
whereCondition = whereCondition.ToggleAmpersand();
context.Configuration.LazyLoadingEnabled = false;
IQueryable <CMDDashboardCustomer> customerList = null;
List <CMDDashboardCustomer> customerListResult = new List <CMDDashboardCustomer>();
int countCustomer = 0;
try
{
#region Data for Existing Customer on Merge with Existing Customer View
customerList =
from cmdCust in context.CMDCustomers
select new CMDDashboardCustomer()
{
CustomerID = cmdCust.Id,
FirstName = cmdCust.FirstName,
MiddleName = cmdCust.MiddleName,
LastName = cmdCust.LastName,
Email = cmdCust.Email,
Phone = cmdCust.Phone,
DOB = cmdCust.DOB,
IsActive = cmdCust.IsActive,
FullName = cmdCust.FirstName.ToLower().Trim().Replace(" ", string.Empty).Replace("\"", string.Empty) + " "
+ (string.IsNullOrEmpty(cmdCust.MiddleName) ? string.Empty : cmdCust.MiddleName.ToLower().Trim().Replace(" ", string.Empty).Replace("\"", string.Empty) + " ")
+ cmdCust.LastName.ToLower().Trim().Replace(" ", string.Empty).Replace("\"", string.Empty),
CustomerTypeID = cmdCust.CMDCustomerTypeID,
CustomerTypeName = cmdCust.CMDCustomerType.Name,
OriginalBusinessUnitID = cmdCust.OriginalBusinessUnitID,
OriginalBusinessUnitName = context.CMDBusinessUnits
.Where(cmdBusinessUnit => cmdBusinessUnit.IsActive && cmdBusinessUnit.Id == cmdCust.OriginalBusinessUnitID)
.Select(recordCMDBusinessUnit => recordCMDBusinessUnit.Name).FirstOrDefault(),
CMDCustomerEmailList = context.CMDCustomerEmails
.Where(cmdCustomerEmail => cmdCustomerEmail.IsActive && cmdCustomerEmail.CMDCustomerID == cmdCust.Id)
.Select(cmdCustEmailList => cmdCustEmailList).ToList(),
CMDContactList =
from cmdContact in context.CMDContacts
where cmdContact.IsActive
join cmdCustomerContactMap in context.CMDCustomerContactMaps
on cmdContact.Id equals cmdCustomerContactMap.CMDContactID
where cmdCustomerContactMap.CMDCustomerID == cmdCust.Id && cmdCustomerContactMap.IsActive
select new CMDDashboardContact
{
Address1 = cmdContact.Address1,
Address2 = cmdContact.Address2,
City = cmdContact.City,
Cell = cmdContact.Cell,
CMDStateID = cmdContact.CMDState.Id,
CMDStateAbbreviation = cmdContact.CMDState.Abbriviation,
CMDCountryAbbreviation = cmdContact.CMDState.CMDCountry.Abbreviation,
ContactName = cmdContact.ContactName,
CreatedDate = cmdContact.CreatedDate,
Fax = cmdContact.Fax,
Id = cmdContact.Id,
IsDefault = cmdContact.IsDefault,
Phone = cmdContact.Phone,
PrivateEmail = cmdContact.PrivateEmail,
PublicEmail = cmdContact.PublicEmail,
UpdatedDate = cmdContact.UpdatedDate,
WebsiteURL = cmdContact.WebsiteURL,
ZipCode = cmdContact.ZipCode,
CMDCountryName = cmdContact.CMDState.CMDCountry.Name,
CMDStateName = cmdContact.CMDState.Name
},
CMDSpecialityList =
from cmdSpeciality in context.CMDSpecialities
where cmdSpeciality.IsActive
join cmdSpecialityMap in context.CMDCustomerSpecialityMaps
on cmdSpeciality.Id equals cmdSpecialityMap.CMDSpecialityID
where cmdSpecialityMap.CMDCustomerID == cmdCust.Id && cmdSpecialityMap.IsActive
select new CMDDashboardSpeciality
{
CreatedDate = cmdSpeciality.CreatedDate,
Id = cmdSpeciality.Id,
IsActive = cmdSpeciality.IsActive,
Name = cmdSpeciality.Name,
UpdatedBy = cmdSpeciality.UpdatedBy,
UpdatedDate = cmdSpeciality.UpdatedDate
},
DuplicateMatchPercent = context.CMDCustomerDuplicateMap.FirstOrDefault(recordCMDCustomerDuplicateMap => recordCMDCustomerDuplicateMap.DuplicateCustomerID == cmdCust.Id && recordCMDCustomerDuplicateMap.IsActive && recordCMDCustomerDuplicateMap.ResolveAction == null).DuplicateMatchPercent,
// If the selected customer is having any duplicate record then set this flag to true
HasDuplicates = context.CMDCustomerDuplicateMap
.Where(recordCMDCustomerDuplicateMap => recordCMDCustomerDuplicateMap.ExistingCustomerID == cmdCust.Id && recordCMDCustomerDuplicateMap.IsActive && recordCMDCustomerDuplicateMap.ResolveAction == null)
.Any(),
IsDuplicate = cmdCust.IsDuplicate,
////If the selected customer is duplicate customer then, give count of Existing Customers for which the selected customer is duplicate
ExistingCustomersCountforSelectedDuplicate =
context.CMDCustomerDuplicateMap
.Where(cmdCustomerDuplicateMap => cmdCustomerDuplicateMap.IsActive && cmdCustomerDuplicateMap.ResolveAction == null &&
cmdCustomerDuplicateMap.DuplicateCustomerID == cmdCust.Id)
.Join(
context.CMDCustomers,
recordCMDCustomerDuplicateMap => recordCMDCustomerDuplicateMap.ExistingCustomerID,
recordCMDCustomer => recordCMDCustomer.Id,
(recordCMDCustomerDuplicateMap, recordCMDCustomer) => recordCMDCustomer)
.Where(recordCMDCust => recordCMDCust.IsActive).Count(),
CMDPracticeList =
context.CMDCustomerPracticeMaps
.Where(cmdCustomerPracticeMap => cmdCustomerPracticeMap.CMDCustomerID == cmdCust.Id &&
cmdCustomerPracticeMap.IsActive && cmdCustomerPracticeMap.CMDPractice.IsActive)
.Select(recordCMDCustomerPracticeMap => recordCMDCustomerPracticeMap.CMDPractice),
CMDProductList =
context.CMDCustomers
.Where(cmdCustomerRecord => cmdCustomerRecord.Id == cmdCust.Id && cmdCustomerRecord.IsActive)
.Join(
context.CMDCustomerAccountMaps,
recCustomer => recCustomer.Id,
cmdCustomerAccountMap => cmdCustomerAccountMap.CMDCustomerID,
(recCustomer, cmdCustomerAccountMap) => cmdCustomerAccountMap)
.Where(cmdCustomerAccountMap => cmdCustomerAccountMap.IsActive &&
cmdCustomerAccountMap.CMDAccount.IsActive)
.Select(cmdCustomerAccountMap => cmdCustomerAccountMap.CMDAccount.CMDTransactions)
.SelectMany(transact => transact)
.Where(cmdTransaction => cmdTransaction.IsActive)
.Join(
context.CMDTransactionProductMaps,
recTransaction => recTransaction.Id,
cmdTransactionProductMap => cmdTransactionProductMap.CMDTransactionID,
(recTransaction, cmdTransactionProductMap) => cmdTransactionProductMap)
.Where(cmdTransactionProductMap => cmdTransactionProductMap.IsActive)
.Select(cmdTransactionProductMap => cmdTransactionProductMap.CMDProduct)
.Where(cmdProduct => cmdProduct.IsActive).Distinct(),
CreatedDate = cmdCust.CreatedDate,
};
#endregion
if (customerList != null)
{
if (!string.IsNullOrEmpty(whereCondition))
{
#region Search
// Find search string
string searchString = string.Empty;
if (whereCondition.Contains(ALPHAEON.CMD.Common.Constants.Dashboard.CustomerSearchStartFilter))
{
int posA = whereCondition.IndexOf(ALPHAEON.CMD.Common.Constants.Dashboard.CustomerSearchStartFilter);
int posB = whereCondition.LastIndexOf(ALPHAEON.CMD.Common.Constants.Dashboard.CustomerSearchEndFilter);
int adjustedPosA = posA + ALPHAEON.CMD.Common.Constants.Dashboard.CustomerSearchStartFilter.Length;
searchString = whereCondition.Substring(adjustedPosA, posB - adjustedPosA);
whereCondition = whereCondition.Replace(ALPHAEON.CMD.Common.Constants.Dashboard.CustomerSearchStartFilter + searchString + ALPHAEON.CMD.Common.Constants.Dashboard.CustomerSearchEndFilter, string.Empty);
}
// Find the customer by full name...
// If customer can not found by full name then find by first name, middle name and last name.
if (!string.IsNullOrEmpty(searchString))
{
string searchCondition = whereCondition + " AND FullName.Contains(\"" + searchString.Trim().ToggleAmpersand().Replace("\"", string.Empty) + "\")";
// Find customer by fullname
if (customerList.Where(searchCondition).ToList().Count() > 0)
{
customerList = customerList.Where(searchCondition);
}
else
{
// Variable to hold the first, middle and last name
string[] nameArray = searchString.Split(' ');
string searchByNameCondition = string.Empty;
// Create 'OR' condition for first name, middle name and last name
if (nameArray.Count() > 0)
{
foreach (string name in nameArray)
{
if (!string.IsNullOrEmpty(name))
{
if (!string.IsNullOrEmpty(searchByNameCondition))
{
searchByNameCondition += " OR FullName.Contains(\"" + name.ToggleAmpersand().Trim().Replace("\"", string.Empty) + "\")";
}
else
{
searchByNameCondition = "FullName.Contains(\"" + name.ToggleAmpersand().Trim().Replace("\"", string.Empty) + "\")";
}
}
}
}
if (!string.IsNullOrEmpty(searchByNameCondition))
{
searchCondition = whereCondition + " AND (" + searchByNameCondition + ")";
}
else
{
searchCondition = whereCondition;
}
}
// Find customer
customerList = customerList.Where(searchCondition);
}
else
{
customerList = customerList.Where(whereCondition);
}
#endregion
}
countCustomer = customerList.Count();
if (countCustomer > 0)
{
if (!string.IsNullOrEmpty(orderByCondition))
{
if (orderByCondition.Contains("FirstName"))
{
string[] orderByArray = orderByCondition.Trim().Split(' ');
if (orderByArray.Count() > 1)
{
string order = orderByArray[1].ToLower().Trim();
if (order.Equals("asc"))
{
customerList = customerList.OrderBy(x => x.FirstName).ThenBy(x => x.MiddleName).ThenBy(x => x.LastName).Skip(skip).Take(take);
}
else
{
customerList = customerList.OrderByDescending(x => x.FirstName).ThenByDescending(x => x.MiddleName).ThenByDescending(x => x.LastName).Skip(skip).Take(take);
}
}
}
else
{
customerList = customerList.OrderBy(orderByCondition).Skip(skip).Take(take);
}
}
else
{
customerList = customerList.OrderBy("CustomerID").Skip(skip).Take(take);
}
customerListResult = customerList.ToList();
}
if (customerListResult != null && customerListResult.Count > 0)
{
customerListResult.All(m => { m.CustomersCount = countCustomer; return(true); });
}
CMDLogger.LogAudit(string.Format("Obtained the Customer List from CMD DB for view Type Duplicate Merge View"), ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(customerListResult as List <T>);
}
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string queryParameters = null) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
context.Configuration.LazyLoadingEnabled = false;
List <CMDDashboardCustomerBusinessUnit> customerBusinessUnitListResult = null;
int customerId = Convert.ToInt32(queryParameters);
if (customerId <= 0)
{
return(customerBusinessUnitListResult as List <T>);
}
try
{
//// Get the Product Lines for the selected Customer
//// Also get the transaction date for the product line
IQueryable <CMDDashboardCustomerBusinessUnit> cmdDashboardCustomerBusinessUnitList =
context.CMDCustomers
.Where(cmdCustomer => cmdCustomer.Id == customerId && cmdCustomer.IsActive)
.Join(
context.CMDCustomerAccountMaps,
recCustomer => recCustomer.Id,
cmdCustomerAccountMap => cmdCustomerAccountMap.CMDCustomerID,
(recCustomer, cmdCustomerAccountMap) => cmdCustomerAccountMap)
.Where(cmdCustomerAccountMap => cmdCustomerAccountMap.IsActive &&
cmdCustomerAccountMap.CMDAccount.IsActive)
.Select(cmdCustomerAccountMap => cmdCustomerAccountMap.CMDBusinessUnit)
.Select(cmdBusinessUnit => new CMDDashboardCustomerBusinessUnit
{
CustomerSupportEmail = cmdBusinessUnit.CMDContact.PublicEmail,
CustomerSupportNumber = cmdBusinessUnit.CMDContact.Phone,
#region Getting first transaction date for customer
FirstTransactionDate = context.CMDCustomers
.Where(cmdCustomer => cmdCustomer.Id == customerId && cmdCustomer.IsActive)
.Join(
context.CMDCustomerAccountMaps,
recCustomer => recCustomer.Id,
cmdCustomerAccountMap => cmdCustomerAccountMap.CMDCustomerID,
(recCustomer, cmdCustomerAccountMap) => cmdCustomerAccountMap)
.Where(cmdCustomerAccountMap => cmdCustomerAccountMap.IsActive &&
cmdCustomerAccountMap.CMDAccount.IsActive)
.Select(cmdCustomerAccountMap => cmdCustomerAccountMap.CMDAccount.CMDTransactions)
.SelectMany(transact => transact)
.Where(cmdTransaction => cmdTransaction.IsActive).ToList().OrderBy(transact => transact.TxDate).FirstOrDefault().TxDate,
#endregion
Id = cmdBusinessUnit.Id,
IsActive = cmdBusinessUnit.IsActive,
Name = cmdBusinessUnit.Name,
WebURL = cmdBusinessUnit.CMDContact.WebsiteURL,
ImageURL = cmdBusinessUnit.ImageURL,
CMDContact = cmdBusinessUnit.CMDContact,
CMDContactID = cmdBusinessUnit.CMDContactID,
}).Distinct();
if (!string.IsNullOrEmpty(whereCondition))
{
cmdDashboardCustomerBusinessUnitList = cmdDashboardCustomerBusinessUnitList.Where(whereCondition);
}
if (!string.IsNullOrEmpty(orderByCondition))
{
cmdDashboardCustomerBusinessUnitList = cmdDashboardCustomerBusinessUnitList.OrderBy(orderByCondition).Skip(skip).Take(take);
}
else
{
cmdDashboardCustomerBusinessUnitList = cmdDashboardCustomerBusinessUnitList.OrderBy("Id").Skip(skip).Take(take);
}
customerBusinessUnitListResult = cmdDashboardCustomerBusinessUnitList.ToList();
CMDLogger.LogAudit("Obtained the BusinessUnit List by Customer ID from CMD", ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(customerBusinessUnitListResult as List <T>);
}
/// <summary>
/// This method retrives the customers excluding customers having CMDCustomerSourceTrack entries for passed list of business units.
/// </summary>
/// <typeparam name="T">Type of List</typeparam>
/// <param name="context">CMD DB Context</param>
/// <param name="whereCondition">Where Condition need to apply on result</param>
/// <param name="orderByCondition">OrderBy Condition need to apply on result</param>
/// <param name="skip">Records to be skipped from result</param>
/// <param name="take">Records to be taken from result</param>
/// <param name="queryParameters">Comma separated list of Business Unit Ids, which need to be consider as exclude condition</param>
/// <returns></returns>
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string queryParameters = null) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
context.Configuration.LazyLoadingEnabled = false;
List <CMDCustomer> cmdCustomerListResult = new List <CMDCustomer>();
IQueryable <CMDCustomer> cmdCustomers = null;
try
{
//If query parameter does not containg anything then return else execute
if (!string.IsNullOrEmpty(queryParameters))
{
string[] parameters = queryParameters.Split(',');
List <int> businessUnitIdsList = parameters.Select(Int32.Parse).ToList();
//Check - Customer List not equal to customer list which has sourcerecord entry for businessunitid passed for exclude in queryparameters
var customerIdListToBeExcluded = (context.CMDCustomerSourceTracks
.Where(cmdCustomerSourceTrackRecord => cmdCustomerSourceTrackRecord.IsActive == true &&
(businessUnitIdsList.Any(businessUnitId => businessUnitId == cmdCustomerSourceTrackRecord.BusinessUnitID)))
.Select(cmdCustomerSourceTrackRecord => cmdCustomerSourceTrackRecord.CMDCustomerID).Distinct()
);
cmdCustomers = context.CMDCustomers
.Where(cmdCustomerRecord => !customerIdListToBeExcluded.Contains(cmdCustomerRecord.Id))
.Select(cmdCustomerRecord => cmdCustomerRecord).Distinct();
//cmdCustomers =
// context.CMDCustomerSourceTracks
// .Where(cmdCustomerSourceTrackRecord => cmdCustomerSourceTrackRecord.IsActive == true &&
// (businessUnitIdsList.Any(businessUnitId => businessUnitId != cmdCustomerSourceTrackRecord.BusinessUnitID)))
// .Select(cmdCustomerSourceTrackRecord => cmdCustomerSourceTrackRecord.CMDCustomer).Distinct();
}
else
{
cmdCustomers = context.CMDCustomers;
}
if (!string.IsNullOrEmpty(whereCondition))
{
cmdCustomers = cmdCustomers.Where(whereCondition);
}
if (!string.IsNullOrEmpty(orderByCondition))
{
cmdCustomers = cmdCustomers.OrderBy(orderByCondition).Skip(skip).Take(take);
}
else
{
cmdCustomers = cmdCustomers.OrderBy("Id").Skip(skip).Take(take);
}
cmdCustomerListResult = cmdCustomers.ToList();
CMDLogger.LogAudit(string.Format("Obtained the CMDCustomers excluding those having CMDCustomerSourceTrack record entried for Business Units Ids {0} from CMD", queryParameters)
, ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(cmdCustomerListResult as List <T>);
}
/// <summary>
/// This method returns a list of CMDContacts for the entity whose Id is passed. Entity type can be CMDCustomer or CMDPractice.
/// </summary>
/// <typeparam name="T">Type of object returned by RPC, here CMDContact</typeparam>
/// <param name="context">Obj. of CMDDatabaseContext</param>
/// <param name="whereCondition">where condition string</param>
/// <param name="orderByCondition">order by condition string</param>
/// <param name="skip">No. of records to be skipped</param>
/// <param name="take">No. of records to be taken</param>
/// <param name="queryParameters">Query Parameters, comma separated IDs entityId, entityTypeId</param>
/// <returns></returns>
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string queryParameters = null) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
context.Configuration.LazyLoadingEnabled = false;
List <CMDDashboardContact> cmdContactListResult = null;
try
{
if (!string.IsNullOrEmpty(queryParameters))
{
string[] parameters = queryParameters.Split(',');
int entityId = Convert.ToInt32(parameters[0]);
int entityTypeId = Convert.ToInt32(parameters[1]);
int usage = Convert.ToInt32(parameters[2]);
int businessUnitId = 0;
if (usage == (int)ALPHAEON.CMD.Common.Enums.Usage.CMDApi)
{
businessUnitId = Convert.ToInt32(parameters[3]);
}
IQueryable <CMDDashboardContact> contactList = null;
if (entityTypeId == (int)ALPHAEON.CMD.Common.Enums.Entity.CMDCustomer)
{
if (businessUnitId > 0)
{
#region Customer CMDContact List
contactList = (from cmdContact in context.CMDContacts
where cmdContact.IsActive == true
join cmdCustomerContactMap in context.CMDCustomerContactMaps
on cmdContact.Id equals cmdCustomerContactMap.CMDContactID
where cmdCustomerContactMap.CMDCustomerID == entityId && cmdCustomerContactMap.IsActive == true && cmdCustomerContactMap.CMDBusinessUnitID == businessUnitId
select new CMDDashboardContact
{
Address1 = cmdContact.Address1,
Address2 = cmdContact.Address2,
City = cmdContact.City,
Cell = cmdContact.Cell,
CMDStateID = cmdContact.CMDState.Id,
CMDStateAbbreviation = cmdContact.CMDState.Abbriviation,
ContactName = cmdContact.ContactName,
CreatedDate = cmdContact.CreatedDate,
Fax = cmdContact.Fax,
Id = cmdContact.Id,
IsDefault = cmdContact.IsDefault,
Phone = cmdContact.Phone,
PrivateEmail = cmdContact.PrivateEmail,
PublicEmail = cmdContact.PublicEmail,
UpdatedDate = cmdContact.UpdatedDate,
WebsiteURL = cmdContact.WebsiteURL,
ZipCode = cmdContact.ZipCode,
CMDCountryName = cmdContact.CMDState.CMDCountry.Name,
CMDStateName = cmdContact.CMDState.Name,
IsActive = cmdContact.IsActive,
});
#endregion
}
else
{
#region Customer CMDContact List
contactList = (from cmdContact in context.CMDContacts
where cmdContact.IsActive == true
join cmdCustomerContactMap in context.CMDCustomerContactMaps
on cmdContact.Id equals cmdCustomerContactMap.CMDContactID
where cmdCustomerContactMap.CMDCustomerID == entityId && cmdCustomerContactMap.IsActive == true
select new CMDDashboardContact
{
Address1 = cmdContact.Address1,
Address2 = cmdContact.Address2,
City = cmdContact.City,
Cell = cmdContact.Cell,
CMDStateID = cmdContact.CMDState.Id,
CMDStateAbbreviation = cmdContact.CMDState.Abbriviation,
CMDCountryAbbreviation = cmdContact.CMDState.CMDCountry.Abbreviation,
ContactName = cmdContact.ContactName,
CreatedDate = cmdContact.CreatedDate,
Fax = cmdContact.Fax,
Id = cmdContact.Id,
IsDefault = cmdContact.IsDefault,
Phone = cmdContact.Phone,
PrivateEmail = cmdContact.PrivateEmail,
PublicEmail = cmdContact.PublicEmail,
UpdatedDate = cmdContact.UpdatedDate,
WebsiteURL = cmdContact.WebsiteURL,
ZipCode = cmdContact.ZipCode,
CMDCountryName = cmdContact.CMDState.CMDCountry.Name,
CMDStateName = cmdContact.CMDState.Name,
IsActive = cmdContact.IsActive,
});
#endregion
}
}
if (entityTypeId == (int)ALPHAEON.CMD.Common.Enums.Entity.CMDPractice)
{
#region Practice CMDContact List
contactList = (from cmdContact in context.CMDContacts
where cmdContact.IsActive == true
join cmdPracticeContactMap in context.CMDPracticeContactMaps
on cmdContact.Id equals cmdPracticeContactMap.CMDContactID
where cmdPracticeContactMap.CMDPracticeID == entityId && cmdPracticeContactMap.IsActive == true
select new CMDDashboardContact
{
Address1 = cmdContact.Address1,
Address2 = cmdContact.Address2,
City = cmdContact.City,
Cell = cmdContact.Cell,
CMDStateID = cmdContact.CMDState.Id,
CMDState = cmdContact.CMDState.Name,
Country = cmdContact.CMDState.CMDCountry.Name,
CMDStateAbbreviation = cmdContact.CMDState.Abbriviation,
CMDCountryAbbreviation = cmdContact.CMDState.CMDCountry.Abbreviation,
ContactName = cmdContact.ContactName,
CreatedDate = cmdContact.CreatedDate,
Fax = cmdContact.Fax,
Id = cmdContact.Id,
IsDefault = cmdContact.IsDefault,
Phone = cmdContact.Phone,
PrivateEmail = cmdContact.PrivateEmail,
PublicEmail = cmdContact.PublicEmail,
UpdatedDate = cmdContact.UpdatedDate,
WebsiteURL = cmdContact.WebsiteURL,
ZipCode = cmdContact.ZipCode,
CMDCountryName = cmdContact.CMDState.CMDCountry.Name,
CMDStateName = cmdContact.CMDState.Name,
IsActive = cmdContact.IsActive,
});
#endregion
}
if (!string.IsNullOrEmpty(whereCondition))
{
contactList = contactList.Where(whereCondition);
}
if (!string.IsNullOrEmpty(orderByCondition))
{
contactList = contactList.OrderBy(orderByCondition).Skip(skip).Take(take);
}
else
{
contactList = contactList.OrderBy("Id").Skip(skip).Take(take);
}
cmdContactListResult = contactList.ToList();
if (entityTypeId == (int)ALPHAEON.CMD.Common.Enums.Entity.CMDCustomer)
{
CMDLogger.LogAudit(string.Format("Obtained the CMD Contact List by {0} ID from CMD", ALPHAEON.CMD.Common.Enums.Entity.CMDCustomer), ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
if (entityTypeId == (int)ALPHAEON.CMD.Common.Enums.Entity.CMDPractice)
{
CMDLogger.LogAudit(string.Format("Obtained the CMD Contact List by {0} ID from CMD", ALPHAEON.CMD.Common.Enums.Entity.CMDPractice), ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
}
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(cmdContactListResult as List <T>);
}
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string queryParameters = null) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
context.Configuration.LazyLoadingEnabled = false;
int businessUnitId = 0;
List <CMDDashboardProductTransaction> transactionListResult = null;
IQueryable <CMDDashboardProductTransaction> cmdDashboardBusinessUnitTransactionList = null;
try
{
if (!string.IsNullOrEmpty(queryParameters))
{
businessUnitId = Convert.ToInt32(queryParameters);
}
if (businessUnitId <= 0)
{
return(transactionListResult as List <T>);
}
#region Select Query
cmdDashboardBusinessUnitTransactionList = CreateSelectQuery(context, businessUnitId);
#endregion Select Query
#region Where Condition
CreateWhereCondition(ref whereCondition, ref cmdDashboardBusinessUnitTransactionList);
#endregion Where Condition
// All the records are fetched for export functionality
var countTransaction = cmdDashboardBusinessUnitTransactionList.Count();
take = take == 0 ? countTransaction : take;
#region OrederBy Condition
orderByCondition = !string.IsNullOrEmpty(orderByCondition) ? orderByCondition : "Id";
cmdDashboardBusinessUnitTransactionList = cmdDashboardBusinessUnitTransactionList.OrderBy(orderByCondition).Skip(skip).Take(take);
#endregion OrederBy Condition
transactionListResult = cmdDashboardBusinessUnitTransactionList.ToList();
if (transactionListResult != null)
{
var totalTransactionAmount = transactionListResult.Sum(t => t.TotalAmount);
transactionListResult.All(m =>
{
m.TransactionCount = countTransaction;
m.TotalTransactionAmount = totalTransactionAmount;
return(true);
});
}
CMDLogger.LogAudit("Obtained the TransactionList by BusinessUnit ID from CMD", ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(transactionListResult as List <T>);
}
public List <T> ExecuteList <T>(CMDDatabaseContext context, string whereCondition, string orderByCondition, int skip = 0, int take = 0, string id = null) where T : class
{
string classAndMethodName = string.Format(ALPHAEON.CMD.Common.Constants.General.ClassAndMethodName, this.GetType().Name, System.Reflection.MethodBase.GetCurrentMethod().Name);
List <CMDDashboardCustomer> existingCustomerListResult = null;
context.Configuration.LazyLoadingEnabled = false;
try
{
int customerId = Convert.ToInt32(id);
IQueryable <CMDDashboardCustomer> cmdDashboardExistingCustomerList =
context.CMDCustomers
.Where(cmdCust => cmdCust.Id == customerId && cmdCust.IsActive && cmdCust.IsDuplicate)
.Join(
context.CMDCustomerDuplicateMap,
recCustomer => recCustomer.Id,
cmdCustomerDuplicateMap => cmdCustomerDuplicateMap.DuplicateCustomerID,
(recCustomer, cmdCustomerDuplicateMap) => cmdCustomerDuplicateMap)
.Where(recCMDCustomerDuplicateMap => recCMDCustomerDuplicateMap.IsActive && recCMDCustomerDuplicateMap.ResolveAction == null)
.Select(recCustomerDuplicateMap => recCustomerDuplicateMap)
.Join(
context.CMDCustomers,
recordDuplicateCustomer => recordDuplicateCustomer.ExistingCustomerID,
recordCustomer => recordCustomer.Id,
(recordDuplicateCustomer, recordCustomer) => new CMDDashboardCustomer()
{
CustomerID = recordCustomer.Id,
FirstName = recordCustomer.FirstName,
MiddleName = recordCustomer.MiddleName,
LastName = recordCustomer.LastName,
Email = recordCustomer.Email,
Phone = recordCustomer.Phone,
DOB = recordCustomer.DOB,
IsActive = recordCustomer.IsActive,
CustomerTypeID = recordCustomer.CMDCustomerTypeID,
CustomerTypeName = recordCustomer.CMDCustomerType.Name,
OriginalBusinessUnitID = recordCustomer.OriginalBusinessUnitID,
OriginalBusinessUnitName = context.CMDBusinessUnits
.Where(cmdBusinessUnit => cmdBusinessUnit.IsActive && cmdBusinessUnit.Id == recordCustomer.OriginalBusinessUnitID)
.Select(recordCMDBusinessUnit => recordCMDBusinessUnit.Name).FirstOrDefault(),
// Get the list of emails assigned to customer
CMDCustomerEmailList = context.CMDCustomerEmails
.Where(cmdCustomerEmail => cmdCustomerEmail.IsActive && cmdCustomerEmail.CMDCustomerID == recordCustomer.Id)
.Select(cmdCustEmailList => cmdCustEmailList).ToList(),
// Get the list of contacts assigned to customer
CMDContactList = from cmdContact in context.CMDContacts
where cmdContact.IsActive
join cmdCustomerContactMap in context.CMDCustomerContactMaps
on cmdContact.Id equals cmdCustomerContactMap.CMDContactID
where cmdCustomerContactMap.CMDCustomerID == recordCustomer.Id && cmdCustomerContactMap.IsActive
select new CMDDashboardContact
{
Address1 = cmdContact.Address1,
Address2 = cmdContact.Address2,
City = cmdContact.City,
Cell = cmdContact.Cell,
CMDStateID = cmdContact.CMDState.Id,
CMDStateAbbreviation = cmdContact.CMDState.Abbriviation,
CMDCountryAbbreviation = cmdContact.CMDState.CMDCountry.Abbreviation,
ContactName = cmdContact.ContactName,
CreatedDate = cmdContact.CreatedDate,
Fax = cmdContact.Fax,
Id = cmdContact.Id,
IsDefault = cmdContact.IsDefault,
Phone = cmdContact.Phone,
PrivateEmail = cmdContact.PrivateEmail,
PublicEmail = cmdContact.PublicEmail,
UpdatedDate = cmdContact.UpdatedDate,
WebsiteURL = cmdContact.WebsiteURL,
ZipCode = cmdContact.ZipCode,
CMDCountryName = cmdContact.CMDState.CMDCountry.Name,
CMDStateName = cmdContact.CMDState.Name
},
// Get the list of speciality assigned to customer
CMDSpecialityList = from cmdSpeciality in context.CMDSpecialities
where cmdSpeciality.IsActive
join cmdSpecialityMap in context.CMDCustomerSpecialityMaps
on cmdSpeciality.Id equals cmdSpecialityMap.CMDSpecialityID
where cmdSpecialityMap.CMDCustomerID == recordCustomer.Id && cmdSpecialityMap.IsActive
select new CMDDashboardSpeciality
{
CreatedDate = cmdSpeciality.CreatedDate,
Id = cmdSpeciality.Id,
IsActive = cmdSpeciality.IsActive,
Name = cmdSpeciality.Name,
UpdatedBy = cmdSpeciality.UpdatedBy,
UpdatedDate = cmdSpeciality.UpdatedDate
},
// Get the list of practices assigned to customer
CMDPracticeList = context.CMDCustomerPracticeMaps.Where(cmdCustomerPracticeMap => cmdCustomerPracticeMap.CMDCustomerID == recordCustomer.Id &&
cmdCustomerPracticeMap.IsActive && cmdCustomerPracticeMap.CMDPractice.IsActive).Select(recordCMDCustomerPracticeMap => recordCMDCustomerPracticeMap.CMDPractice),
// Get the list of products assigned to customer
CMDProductList = context.CMDCustomers
.Where(cmdCustomerRecord => cmdCustomerRecord.Id == recordCustomer.Id && cmdCustomerRecord.IsActive)
.Join(context.CMDCustomerAccountMaps, recCustomer => recCustomer.Id,
cmdCustomerAccountMap => cmdCustomerAccountMap.CMDCustomerID,
(recCustomer, cmdCustomerAccountMap) => cmdCustomerAccountMap)
.Where(cmdCustomerAccountMap => cmdCustomerAccountMap.IsActive &&
cmdCustomerAccountMap.CMDAccount.IsActive)
.Select(cmdCustomerAccountMap => cmdCustomerAccountMap.CMDAccount.CMDTransactions)
.SelectMany(transact => transact)
.Where(cmdTransaction => cmdTransaction.IsActive)
.Join(context.CMDTransactionProductMaps, recTransaction => recTransaction.Id,
cmdTransactionProductMap => cmdTransactionProductMap.CMDTransactionID,
(recTransaction, cmdTransactionProductMap) => cmdTransactionProductMap)
.Where(cmdTransactionProductMap => cmdTransactionProductMap.IsActive)
.Select(cmdTransactionProductMap => cmdTransactionProductMap.CMDProduct)
.Where(cmdProduct => cmdProduct.IsActive).Distinct(),
Criteria = recordDuplicateCustomer.Criteria,
CreatedDate = recordCustomer.CreatedDate
});
if (!string.IsNullOrEmpty(whereCondition))
{
cmdDashboardExistingCustomerList = cmdDashboardExistingCustomerList.Where(whereCondition);
}
orderByCondition = !string.IsNullOrEmpty(orderByCondition) ? orderByCondition : "CustomerId";
cmdDashboardExistingCustomerList = cmdDashboardExistingCustomerList.OrderBy(orderByCondition).Skip(skip).Take(take);
existingCustomerListResult = cmdDashboardExistingCustomerList.ToList();
CMDLogger.LogAudit("Obtained the Duplicate Customers List by Customer ID from CMD", ALPHAEON.CMD.Common.Constants.General.ApplicationName);
}
catch (Exception ex)
{
CMDLogger.LogException(ex, ALPHAEON.CMD.Common.Constants.General.ObjectName, ALPHAEON.CMD.Common.Constants.General.ApplicationName, classAndMethodName);
}
return(existingCustomerListResult as List <T>);
}