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>); }