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