public List <FeeSetupReportDTO> GetNoBankAppSubmissionReport(string strguid) { try { db = new DatabaseEntities(); List <string> lstCustGuid = strguid.Split(',').ToList(); List <Guid> BE_CustomerList = db.BankEnrollments.Where(x => x.IsActive == true && x.StatusCode != EMPConstants.Ready && x.ArchiveStatusCode != EMPConstants.Archive).Select(a => a.CustomerId ?? Guid.Empty).ToList(); //List<Guid> ES_CustomerList = (from be in db.EnrollmentBankSelections // // join ssbc in db.SubSiteBankConfigs on new { x1 = be.BankId, x2 = be.CustomerId } equals new { x1 = ssbc.BankMaster_ID, x2 = ssbc.emp_CustomerInformation_ID } // where !BE_CustomerList.Contains(be.CustomerId) && be.StatusCode == EMPConstants.Active // select be.CustomerId).ToList(); //var data = (from cu in db.emp_CustomerInformation // join cl in db.emp_CustomerLoginInformation on cu.Id equals cl.CustomerOfficeId // where ES_CustomerList.Contains(cu.Id) && cl.CrossLinkUserId != null // select new { cu, cl }).Take(1); List <ReportCustomerModel> data = new List <ReportCustomerModel>(); EnrollmentBankSelectionInfo.EnrollmentBankSelectionService oService = new EnrollmentBankSelectionInfo.EnrollmentBankSelectionService(); if (db.emp_CustomerInformation.Any(a => lstCustGuid.Contains(a.Id.ToString()))) { List <string> lstGuids = GetChildData(lstCustGuid); data = (from cu in db.emp_CustomerInformation join cl in db.emp_CustomerLoginInformation on cu.Id equals cl.CustomerOfficeId where !BE_CustomerList.Contains(cu.Id) && lstGuids.Contains(cu.Id.ToString()) && cl.CrossLinkUserId != null //&& ES_CustomerList.Contains(cu.Id) select new ReportCustomerModel { cu = cu, cl = cl }).ToList(); } else { data = (from cu in db.emp_CustomerInformation join cl in db.emp_CustomerLoginInformation on cu.Id equals cl.CustomerOfficeId where !BE_CustomerList.Contains(cu.Id) && cl.CrossLinkUserId != null // && ES_CustomerList.Contains(cu.Id) select new ReportCustomerModel { cu = cu, cl = cl }).ToList(); } List <FeeSetupReportDTO> lstmodel = new List <FeeSetupReportDTO>(); DropDownService _ddService = new DropDownService(); //if (data.Count() > 0) { foreach (var itm in data) { List <Guid> ES_BankList = (from be in db.EnrollmentBankSelections // join ssbc in db.SubSiteBankConfigs on new { x1 = be.BankId, x2 = be.CustomerId } equals new { x1 = ssbc.BankMaster_ID, x2 = ssbc.emp_CustomerInformation_ID } where be.CustomerId == itm.cu.Id && !BE_CustomerList.Contains(be.CustomerId) && be.StatusCode == EMPConstants.Active select be.BankId).ToList(); if (ES_BankList.Count > 0) { if (itm.cu.EntityId != (int)EMPConstants.Entity.SO && itm.cu.EntityId != (int)EMPConstants.Entity.SOME && itm.cu.EntityId != (int)EMPConstants.Entity.SOME_SS) { List <EntityHierarchyDTO> EntityHierarchyDTOs = new List <EntityHierarchyDTO>(); EntityHierarchyDTOs = _ddService.GetEntityHierarchies(itm.cu.Id); Guid TopParentId = Guid.Empty; if (EntityHierarchyDTOs.Count > 0) { var TopFromHierarchy = EntityHierarchyDTOs.OrderByDescending(o => o.Customer_Level).FirstOrDefault(); TopParentId = TopFromHierarchy.CustomerId ?? Guid.Empty; var SubSiteBankConfigs = db.SubSiteBankConfigs.Where(o => o.emp_CustomerInformation_ID == TopParentId && ES_BankList.Contains(o.BankMaster_ID)).Select(o => o.BankMaster_ID).ToList(); if (SubSiteBankConfigs.Count() == 0) { continue; } // SubSiteBankConfigs } else { var SubSiteBankConfigs = db.SubSiteBankConfigs.Where(o => o.emp_CustomerInformation_ID == itm.cu.Id && ES_BankList.Contains(o.BankMaster_ID)).Select(o => o.BankMaster_ID).ToList(); if (SubSiteBankConfigs.Count() == 0) { continue; } } } FeeSetupReportDTO omodel = new FeeSetupReportDTO(); omodel.UserID = itm.cl.EMPUserId; if (itm.cu.ParentId != null) { omodel.ParentUserID = db.emp_CustomerLoginInformation.Where(a => a.CustomerOfficeId == itm.cu.ParentId).Select(a => a.CrossLinkUserId).FirstOrDefault(); } else { omodel.ParentUserID = ""; } omodel.CompanyName = itm.cu.CompanyName; omodel.Efin = itm.cu.EFIN.ToString().PadLeft(6, '0'); omodel.MasterID = itm.cl.MasterIdentifier; omodel.AccountOwner = itm.cu.BusinessOwnerFirstName; lstmodel.Add(omodel); } } } return(lstmodel.OrderBy(a => a.CompanyName).ToList()); } catch (Exception ex) { EMPPortal.Core.Utilities.ExceptionLogger.LogException(ex.ToString(), "ReportsService/GetNoBankAppSubmissionReport", Guid.Empty); return(new List <FeeSetupReportDTO>()); } }
public List <FeeSetupReportDTO> GetFeeSetUpReport(string strguid) { try { List <string> lstCustGuid = strguid.Split(',').ToList(); db = new DatabaseEntities(); List <ReportCustomerModel> data = new List <ReportCustomerModel>(); //var data = (from cu in db.emp_CustomerInformation // join cl in db.emp_CustomerLoginInformation on cu.Id equals cl.CustomerOfficeId // where cu.ParentId != null && cl.CrossLinkUserId != null && cu.StatusCode != EMPConstants.Pending // select new { cu, cl }).Take(1); EnrollmentBankSelectionInfo.EnrollmentBankSelectionService oService = new EnrollmentBankSelectionInfo.EnrollmentBankSelectionService(); if (db.emp_CustomerInformation.Any(a => lstCustGuid.Contains(a.Id.ToString()))) { List <string> lstGuids = GetChildData(lstCustGuid); data = (from cu in db.emp_CustomerInformation join cl in db.emp_CustomerLoginInformation on cu.Id equals cl.CustomerOfficeId where lstGuids.Contains(cu.Id.ToString()) && cl.CrossLinkUserId != null && cu.StatusCode != EMPConstants.Pending select new ReportCustomerModel { cu = cu, cl = cl }).ToList(); } else { data = (from cu in db.emp_CustomerInformation join cl in db.emp_CustomerLoginInformation on cu.Id equals cl.CustomerOfficeId where cl.CrossLinkUserId != null && (cu.StatusCode != EMPConstants.InProgress && cu.StatusCode != EMPConstants.NewCustomer) orderby cl.EMPUserId select new ReportCustomerModel { cu = cu, cl = cl }).ToList(); } var custids = data.Select(x => x.cl.CustomerOfficeId).Distinct().ToList(); var enrollstatus = (from eb in db.EnrollmentBankSelections join be in db.BankEnrollments on new { x1 = eb.BankId, x2 = eb.CustomerId } equals new { x1 = be.BankId.Value, x2 = be.CustomerId.Value } join b in db.BankMasters on eb.BankId equals b.Id where custids.Contains(be.CustomerId) && custids.Contains(eb.CustomerId) && ((be.StatusCode == EMPConstants.EnrPending || be.StatusCode == EMPConstants.Submitted || be.StatusCode == EMPConstants.Approved || be.StatusCode == EMPConstants.Rejected) && be.ArchiveStatusCode != EMPConstants.Archive) && be.IsActive == true && eb.StatusCode == EMPConstants.Active select new { eb.CustomerId, b.Id, b.BankName, be.StatusCode, eb.ServiceBureauBankAmount, eb.TransmissionBankAmount, eb.IsServiceBureauFee, eb.IsTransmissionFee }).ToList(); //var bankenrolls = db.BankEnrollments.Where(x => custids.Contains(x.CustomerId) && // (x.StatusCode == EMPConstants.EnrPending || x.StatusCode == EMPConstants.Submitted || x.StatusCode == EMPConstants.Approved || x.StatusCode == EMPConstants.Rejected) // && x.ArchiveStatusCode != EMPConstants.Archive).ToList(); List <FeeSetupReportDTO> lstmodel = new List <FeeSetupReportDTO>(); if (data.Count() > 0) { foreach (var itm in data) { //var enrollstatus = (from eb in db.EnrollmentBankSelections // join b in db.BankMasters on eb.BankId equals b.Id // where eb.CustomerId == itm.cu.Id // select new { b.Id, b.BankName, eb.StatusCode, eb.ServiceBureauBankAmount, eb.TransmissionBankAmount, eb.IsServiceBureauFee, eb.IsTransmissionFee }).ToList(); var banks = enrollstatus.Where(o => o.CustomerId == itm.cl.CustomerOfficeId).ToList(); foreach (var bank in banks) //if (bank != null) { //var isexist = bankenrolls.Where(x => x.CustomerId == itm.cl.CustomerOfficeId && x.BankId == bank.Id).FirstOrDefault(); //if (isexist == null) // continue; FeeSetupReportDTO omodel = new FeeSetupReportDTO(); omodel.UserID = itm.cl.EMPUserId; if (itm.cu.ParentId != null) { omodel.ParentUserID = db.emp_CustomerLoginInformation.Where(a => a.CustomerOfficeId == itm.cu.ParentId).Select(a => a.CrossLinkUserId).FirstOrDefault(); } else { omodel.ParentUserID = ""; } omodel.CompanyName = itm.cu.CompanyName; omodel.Efin = itm.cu.EFIN.ToString().PadLeft(6, '0'); omodel.MasterID = itm.cl.MasterIdentifier; omodel.AccountOwner = itm.cu.BusinessOwnerFirstName; if (enrollstatus != null) { omodel.Bank = bank.BankName; if (bank.IsServiceBureauFee == true) { omodel.SBAddOn = bank.ServiceBureauBankAmount.ToString(); } else { omodel.SBAddOn = string.Empty; } if (bank.IsTransmissionFee == true) { omodel.AddonFeeERO = bank.TransmissionBankAmount.ToString(); } else { omodel.AddonFeeERO = string.Empty; } //var enroll = bankenrolls.Where(x => x.CustomerId == itm.cu.Id && x.IsActive == true && x.ArchiveStatusCode != EMPConstants.Archive).Select(x => x).FirstOrDefault(); // if (isexist != null) { omodel.EnrollmentStatus = bank.StatusCode == EMPConstants.EnrPending ? "Pending" : ((bank.StatusCode == EMPConstants.Submitted) ? "Submitted" : (bank.StatusCode == EMPConstants.Approved ? "Approved" : (bank.StatusCode == EMPConstants.Rejected ? "Rejected" : (bank.StatusCode == EMPConstants.Denied ? "Denied" : "")))); } // else // omodel.EnrollmentStatus = "Not Started"; var BankFee = db.SubSiteBankFeesConfigs.Where(a => a.emp_CustomerInformation_ID == itm.cu.Id && a.BankMaster_ID == bank.Id).ToList(); if (BankFee.Count > 0) { if (itm.cu.IsMSOUser == true) { omodel.SBFee = BankFee.Where(a => a.ServiceOrTransmitter == 1).Select(a => a.BankMaxFees_MSO).FirstOrDefault().ToString(); // db.SubSiteBankFeesConfigs.Where(a => a.emp_CustomerInformation_ID == itm.cu.Id && a.BankMaster_ID == bank.Id && a.ServiceOrTransmitter == 1).Select(a => a.BankMaxFees_MSO).FirstOrDefault().ToString(); } else { omodel.SBFee = BankFee.Where(a => a.ServiceOrTransmitter == 1).Select(a => a.BankMaxFees).FirstOrDefault().ToString();// db.SubSiteBankFeesConfigs.Where(a => a.emp_CustomerInformation_ID == itm.cu.Id && a.BankMaster_ID == bank.Id && a.ServiceOrTransmitter == 1).Select(a => a.BankMaxFees).FirstOrDefault().ToString(); } if (itm.cu.IsMSOUser == true) { omodel.AddonFeeSB = BankFee.Where(a => a.ServiceOrTransmitter == 2).Select(a => a.BankMaxFees_MSO).FirstOrDefault().ToString();// db.SubSiteBankFeesConfigs.Where(a => a.emp_CustomerInformation_ID == itm.cu.Id && a.BankMaster_ID == bank.Id && a.ServiceOrTransmitter == 2).Select(a => a.BankMaxFees_MSO).FirstOrDefault().ToString(); } else { omodel.AddonFeeSB = BankFee.Where(a => a.ServiceOrTransmitter == 2).Select(a => a.BankMaxFees).FirstOrDefault().ToString();// db.SubSiteBankFeesConfigs.Where(a => a.emp_CustomerInformation_ID == itm.cu.Id && a.BankMaster_ID == bank.Id && a.ServiceOrTransmitter == 2).Select(a => a.BankMaxFees).FirstOrDefault().ToString(); } } } else { omodel.Bank = ""; omodel.SBAddOn = ""; omodel.AddonFeeSB = ""; omodel.AddonFeeERO = ""; omodel.EnrollmentStatus = ""; omodel.SBFee = ""; } List <EnrollmentBankSelectionDTO> olist = oService.GetBankandFeesInfo(itm.cu.EntityId ?? 0, itm.cu.Id, Guid.Empty.ToString()).ToList(); //, bank.Id omodel.uTaxFee = olist.Where(a => a.Name == "Service Bureau Fees").Select(a => a.Amount).FirstOrDefault().ToString(); //omodel.SBFee = olist.Where(a => a.Name == "Transmission Fees").Select(a => a.Amount).FirstOrDefault().ToString(); lstmodel.Add(omodel); } } } return(lstmodel.OrderBy(a => a.CompanyName).ToList()); } catch (Exception ex) { EMPPortal.Core.Utilities.ExceptionLogger.LogException(ex.ToString(), "ReportsService/GetFeeSetUpReport", Guid.Empty); return(null); } }