public object GetSingaporeAdmissionInvoice(string referenceNo)
{
try
{
using (var db = new PERFECTIBSEntities())
{
var qry = (from q in db.SingaporeAdmissions
where q.ReferenceNo == referenceNo
select new
{
CaseNo = q.CaseNumber.Trim(),
NameOnInsurance = q.PatientName.Trim(),
ReferenceNo = q.ReferenceNo.Trim(),
LocationOfIncident = q.Hospital.Trim(),
DateOfAdmission = q.AdmissionDate,
DateOfDischarge = q.DischargeDate,
CMAInvoiceNumber = q.CMAInvoiceNumber.Trim(),
MedicalExpenses = q.FinalAmount,
CaseFee = 0,
}).FirstOrDefault();
return(qry);
}
}
catch (Exception)
{
throw;
}
}
public DashboardVM GetQuotationCount()
{
try
{
DashboardVM objDashboardVM = new DashboardVM();
PERFECTIBSEntities db = new PERFECTIBSEntities();
objDashboardVM.PendingQuotationCount = unitOfWork.TblQuotationHeaderRepository.Get(o => o.QuotationStatusCode.Equals("QP")).Count();
objDashboardVM.ApprovedQuotationCount = unitOfWork.TblQuotationHeaderRepository.Get(o => o.QuotationStatusCode.Equals("CA")).Count();
objDashboardVM.NotCreatingQuotationCount = unitOfWork.TblQuotationHeaderRepository.Get(o => o.QuotationStatusCode.Equals("QNC")).Count();
objDashboardVM.TCNIQuotationCount = unitOfWork.TblQuotationHeaderRepository.Get(o => o.QuotationStatusCode.Equals("TCNI")).Count();
objDashboardVM.QuotationTotalCount = unitOfWork.TblQuotationHeaderRepository.Get().Count();
objDashboardVM.ClientRequestCount = unitOfWork.TblClientRequestHeaderRepository.Get().Count();
List <sp_GetClientVSQuotation_Count_Result> ListItems = db.sp_GetClientVSQuotation_Count().ToList <sp_GetClientVSQuotation_Count_Result>();
// objDashboardVM.ClientRequestWithQuotation = ListItems;
return(objDashboardVM);
}
catch (Exception ex)
{
throw ex;
}
}
public IEnumerable <RenewalListMapper> GetClientExpireInfo(FilterMapper filterMapper)
{
try
{
using (var db = new PERFECTIBSEntities())
{
var qry = (from crh in db.tblClientRequestHeaders
join c in db.tblClients on crh.ClientID equals c.ClientID
where filterMapper.fromDate <= crh.PolicyEnd && filterMapper.toDate >= crh.PolicyEnd
select new RenewalListMapper
{
ClientId = c.ClientID,
ClientName = c.ClientName,
ContactNo = c.ContactNo,
Address = c.ClientAddress,
ExpirationDate = crh.PolicyEnd,
}).ToList();
return(qry);
}
}
catch (Exception)
{
throw;
}
}
public List <AgenCommissionMapper> GetBUPACommissionDetailByDate(FilterMapper filterMapper)
{
try
{
using (var db = new PERFECTIBSEntities())
{
var qry = (from tran in db.tblBankTransactionDetails
join cur in db.tblCurrencies on tran.currencyType equals cur.CurrencyID
join ag in db.tblAgents on tran.AgentID equals ag.AgentID
join cl in db.tblClients on tran.ClientID equals cl.ClientID
where tran.IsInvoiceTrf == 0 &&
tran.PaymentDate >= filterMapper.fromDate && tran.PaymentDate <= filterMapper.toDate
select new AgenCommissionMapper
{
AgentCode = ag.AgentCode.Trim(),
PaymentDate = tran.PaymentDate,
Amount = tran.Amount,
CurrencyType = cur.CurrencyName,
CurrencyRate = 0,
}).ToList();
return(qry);
}
}
catch (Exception)
{
throw;
}
}
public object GetBUPACommissionByDate(FilterMapper filterMapper)
{
try
{
using (var db = new PERFECTIBSEntities())
{
var qry = (from tran in db.tblBankTransactionDetails
join cur in db.tblCurrencies on tran.currencyType equals cur.CurrencyID
join ag in db.tblAgents on tran.AgentID equals ag.AgentID
join cl in db.tblClients on tran.ClientID equals cl.ClientID
where tran.IsInvoiceTrf == 0 &&
tran.PaymentDate >= filterMapper.fromDate && tran.PaymentDate <= filterMapper.toDate
group tran by new { cur.CurrencyName, tran.currencyType, ag.AgentCode, tran.PaymentDate, cl.ClientName } into gr
select new CommissionDetailMapper
{
AgentCode = gr.Key.AgentCode,
ClientName = gr.Key.ClientName,
PaidDate = gr.Key.PaymentDate,
CurrencyType = gr.Key.CurrencyName,
Amount = gr.Sum(s => s.AgentAmount),
CurrencyRate = 0,
}).ToList();
return(qry);
}
}
catch (Exception)
{
throw;
}
}
public List <CurrencyTypeMapper> GetCurrency()
{
using (PERFECTIBSEntities context = new PERFECTIBSEntities())
{
var qry = (from q in context.tblCurrencies
select new CurrencyTypeMapper
{
CurrencyCode = q.CurrencyCode,
CurrencyName = q.CurrencyName
}).ToList();
return(qry);
}
}
public AmountMapper GetBupaAmount(FilterMapper map)
{
try
{
using (var db = new PERFECTIBSEntities())
{
var custCode = (from b in db.tblBussinessUnits
where b.BussinessUnit == "BUPA"
select new
{
b.ERPCustomerCode,
}).FirstOrDefault();
int[] ids = db.tblBankTransactionDetails.Where(t => t.IsInvoiceTrf == 0).Select(t => t.BankDetailID).ToArray();
var qry = (from tran in db.tblBankTransactionDetails
join cur in db.tblCurrencies on tran.currencyType equals cur.CurrencyID
where tran.IsInvoiceTrf == 0 && tran.PaymentDate >= map.fromDate && tran.PaymentDate <= map.toDate
group tran by new { cur.CurrencyName, tran.currencyType } into gr
select new AmountDetailMapper
{
CurrencyType = gr.Key.CurrencyName,
Amount = gr.Sum(s => s.IBSAmount),
CurrencyRate = 0,
AgentCode = custCode.ERPCustomerCode,
}).AsQueryable();
qry = qry.Where(q => q.Amount > 0).AsQueryable();
if (map != null)
{
if (map.type != null)
{
if (map.type != "0")
{
qry = qry.Where(q => q.CurrencyType == map.type).AsQueryable();
}
}
}
AmountMapper mapper = new AmountMapper();
mapper.Ids = ids;
mapper.AmountDetailMappers = new List <AmountDetailMapper>();
mapper.AmountDetailMappers.AddRange(qry.ToList());
return(mapper);
}
}
catch (Exception)
{
throw;
}
}
public object GetAgentCommission(FilterMapper map)
{
try
{
AgenCommissionRootMapper mapper = new AgenCommissionRootMapper();
using (var db = new PERFECTIBSEntities())
{
mapper.Ids = db.tblBankTransactionDetails.Where(t => t.IsAgentTrf == 0).Select(t => t.BankDetailID).ToArray();
var qry = (from tran in db.tblBankTransactionDetails
join cur in db.tblCurrencies on tran.currencyType equals cur.CurrencyID
join ag in db.tblAgents on tran.AgentID equals ag.AgentID
where tran.IsAgentTrf == 0 && tran.PaymentDate >= map.fromDate && tran.PaymentDate <= map.toDate
// group tran by new { cur.CurrencyName, tran.currencyType, ag.AgentCode } into gr
select new AgenCommissionMapper
{
//CurrencyType = gr.Key.CurrencyName,
//Amount = gr.Sum(s => s.AgentAmount),
//CurrencyRate = 0,
//AgentCode = gr.Key.AgentCode
PaymentDate = tran.PaymentDate,
CurrencyType = cur.CurrencyName,
Amount = tran.AgentAmount,
CurrencyRate = 0,
AgentCode = ag.AgentCode,
}).AsQueryable();
qry = qry.Where(q => q.Amount > 0).AsQueryable();
if (map.type != null)
{
if (map.type != "0")
{
qry = qry.Where(q => q.CurrencyType == map.type).AsQueryable();
}
}
mapper.AgenCommissionMappers = new List <AgenCommissionMapper>();
mapper.AgenCommissionMappers.AddRange(qry.ToList());
return(mapper);
}
}
catch (Exception)
{
throw;
}
}
public List <AdmissionViewVM> GetAllSingaporeAdmissions()
{
using (var db = new PERFECTIBSEntities())
{
var qry = (from pa in db.SingaporeAdmissions
select new AdmissionViewVM
{
PatientName = pa.PatientName.Trim(),
AdmissionDate = pa.AdmissionDate,
BHTNumber = pa.CaseNumber.Trim(),
Hospital = pa.Hospital.Trim(),
PassportNumber = pa.PassportNumber.Trim(),
ReferenceNo = pa.ReferenceNo.Trim()
}).ToList();
return(qry);
}
}
public bool Insert(IntegrationViewModel integrationViewModel)
{
try
{
using (TransactionScope scope = new TransactionScope())
{
using (var db = new PERFECTIBSEntities())
{
var transactions = new List <tblTransactionDetail>();
foreach (AmountInfo model in integrationViewModel.amountInfo)
{
var transaction = new tblTransactionDetail();
transaction.CurrencyCode = model.CurrencyType;
transaction.Amount = model.Amount;
transaction.PaymentDate = integrationViewModel.paymentDate;
transaction.Rate = model.CurrencyRate;
transaction.Type = integrationViewModel.type;
transaction.AgentCode = model.AgentCode;
transaction.IsTransfer = false;
transactions.Add(transaction);
}
db.tblTransactionDetails.AddRange(transactions);
db.SaveChanges();
bool isUpdated = false;
if (integrationViewModel.type.Trim() == "BupaInvoice")
{
isUpdated = UpdateBupaInvoiceFlag(integrationViewModel.ids);
}
else if (integrationViewModel.type.Trim() == "AgentCommssion")
{
isUpdated = UpdateBupaAmountFlag(integrationViewModel.ids);
}
if (isUpdated)
{
scope.Complete();
}
return(true);
}
}
}
catch (Exception)
{
throw;
}
}
public List <ClientWithQuotationCount> GetClienVSQuotation()
{
try
{
PERFECTIBSEntities db = new PERFECTIBSEntities();
var QuotationData = db.sp_GetClientVSQuotation_Count().ToList();
// List<sp_GetClientVSQuotation_Count_Result> ListItems = db.sp_GetClientVSQuotation_Count().ToList<sp_GetClientVSQuotation_Count_Result>();
List <ClientWithQuotationCount> List = new List <ClientWithQuotationCount>();
foreach (var Quotation in QuotationData)
{
ClientWithQuotationCount ClientQuoVM = new ClientWithQuotationCount();
ClientQuoVM.count = Quotation.COUNTS;
ClientQuoVM.January = Quotation.January.ToString();
ClientQuoVM.Febrary = Quotation.Febrary.ToString();
ClientQuoVM.March = Quotation.March.ToString();
ClientQuoVM.April = Quotation.April.ToString();
ClientQuoVM.May = Quotation.May.ToString();
ClientQuoVM.June = Quotation.June.ToString();
ClientQuoVM.July = Quotation.July.ToString();
ClientQuoVM.August = Quotation.August.ToString();
ClientQuoVM.September = Quotation.September.ToString();
ClientQuoVM.October = Quotation.October.ToString();
ClientQuoVM.November = Quotation.November.ToString();
ClientQuoVM.December = Quotation.December.ToString();
List.Add(ClientQuoVM);
}
return(List);
}
catch (Exception ex)
{
throw ex;
}
}
public bool UpdateBupaAmountFlag(int[] ids)
{
try
{
using (var db = new PERFECTIBSEntities())
{
string id = string.Empty;
for (int i = 0; i < ids.Length; i++)
{
id += ids[i] + ",";
}
id = id.Remove(id.Length - 1);
string qry = $"UPDATE tblBankTransactionDetails SET IsAgentTrf = 1 WHERE BankDetailID IN ({id})";
var query = db.Database.ExecuteSqlCommand(qry);
}
return(true);
}
catch (Exception)
{
throw;
}
}
public bool SaveInvoice(SingaporeAdmissionInvoiceVM admissionVM)
{
UnitOfWork unitOfWork = new UnitOfWork();
using (var dbTransaction = unitOfWork.dbContext.Database.BeginTransaction())
{
try
{
SingaporeInvoice singaporeAdmission;
bool isModify = false;
using (var db = new PERFECTIBSEntities())
{
singaporeAdmission = db.SingaporeInvoices.Where(p => p.ReferenceNo.Equals(admissionVM.ReferenceNo.Trim())).FirstOrDefault();
if (singaporeAdmission != null)
{
isModify = true;
}
}
if (singaporeAdmission == null)
{
singaporeAdmission = new SingaporeInvoice();
}
#region Assign Properties
singaporeAdmission.ReferenceNo = admissionVM.ReferenceNo.Trim();
singaporeAdmission.AccountNumber = admissionVM.AccountNo.Trim();
singaporeAdmission.BankAddress = admissionVM.BankAddress.Trim();
singaporeAdmission.BankBrach = admissionVM.BankBranch.Trim();
singaporeAdmission.BankCode = admissionVM.BankCode.Trim();
singaporeAdmission.BankName = admissionVM.BankName.Trim();
singaporeAdmission.BeneficiaryName = admissionVM.ClaimAssessor.Trim();
singaporeAdmission.ClaimsAssessor = admissionVM.ClaimAssessor.Trim();
singaporeAdmission.Customerid = admissionVM.CustomerId.Trim();
singaporeAdmission.MembershipNo = admissionVM.MembershipNo.Trim();
singaporeAdmission.SwiftCode = admissionVM.SwiftCode.Trim();
singaporeAdmission.Terms = admissionVM.Terms.Trim();
#endregion
if (!isModify)
{
unitOfWork.TblSingaporeInvoice.Insert(singaporeAdmission);
}
else
{
unitOfWork.TblSingaporeInvoice.Update(singaporeAdmission);
}
unitOfWork.Save();
dbTransaction.Commit();
return(true);
}
catch (Exception)
{
dbTransaction.Rollback();
return(false);
}
}
}
public bool SaveAdmissionRecording(AdmissionVM admissionVM)
{
UnitOfWork unitOfWork = new UnitOfWork();
using (var dbTransaction = unitOfWork.dbContext.Database.BeginTransaction())
{
try
{
Admission_Singapore singaporeAdmission;
bool isModify = false;
using (var db = new PERFECTIBSEntities())
{
singaporeAdmission = db.Admission_Singapore.Where(p => p.ReferenceNo.Equals(admissionVM.ReferenceNo.Trim())).FirstOrDefault();
if (singaporeAdmission != null)
{
isModify = true;
}
}
if (singaporeAdmission == null)
{
singaporeAdmission = new Admission_Singapore();
}
#region Assign Properties
singaporeAdmission.SingaporeAdmissionId = 0;
singaporeAdmission.ReferenceNo = admissionVM.ReferenceNo == null ? "":admissionVM.ReferenceNo.Trim();
singaporeAdmission.PatientName = admissionVM == null || admissionVM.DeductionID == null?"0": admissionVM.DeductionID.ToString();
singaporeAdmission.DateOfBirth = !string.IsNullOrEmpty(admissionVM.DateOfBirth) ? DateTime.ParseExact(admissionVM.DateOfBirth, "dd/MM/yyyy", CultureInfo.InvariantCulture) : DateTime.Parse("01/01/1900");
// singaporeAdmission.DateOfBirth = DateTime.Parse(admissionVM.DateOfBirth);
singaporeAdmission.PassportNumber = admissionVM.PassportNumber.Trim();
singaporeAdmission.Scheme = admissionVM == null?"": admissionVM.Scheme.Trim();
singaporeAdmission.InimatedDate = !string.IsNullOrEmpty(admissionVM.InimatedDate) ? DateTime.ParseExact(admissionVM.InimatedDate, "dd/MM/yyyy", CultureInfo.InvariantCulture) : DateTime.Parse("01/01/1900");
singaporeAdmission.InceptionDate = !string.IsNullOrEmpty(admissionVM.InceptionDate) ? DateTime.ParseExact(admissionVM.InceptionDate, "dd/MM/yyyy", CultureInfo.InvariantCulture) : DateTime.Parse("01/01/1900");;
singaporeAdmission.Deductible = admissionVM == null?0: admissionVM.Deductible;
singaporeAdmission.DeductibleForTheYear = admissionVM == null?0: admissionVM.DeductibleUsedForTheYear;
singaporeAdmission.Exclusions = admissionVM == null?0: admissionVM.Exclusions;
singaporeAdmission.Hospital = admissionVM == null || admissionVM.Hospital == null? "": admissionVM.Hospital;
singaporeAdmission.AdmissionDate = !string.IsNullOrEmpty(admissionVM.AdmissionDate) ? DateTime.ParseExact(admissionVM.AdmissionDate, "dd/MM/yyyy", CultureInfo.InvariantCulture) : DateTime.Parse("01/01/1900");
singaporeAdmission.DischargeDate = !string.IsNullOrEmpty(admissionVM.DischargedDate) ? DateTime.ParseExact(admissionVM.DischargedDate, "dd/MM/yyyy", CultureInfo.InvariantCulture) : DateTime.Parse("01/01/1900");;
singaporeAdmission.CaseNumber = admissionVM == null || admissionVM.CaseNumber == null ? "" : admissionVM.CaseNumber;;
singaporeAdmission.Illness = admissionVM == null || admissionVM.Illness == null? "": admissionVM.Illness.Trim();
singaporeAdmission.ConsultantName = admissionVM == null || admissionVM.ConsultantName == null ? "" : admissionVM.ConsultantName.Trim();
singaporeAdmission.InformedBy = admissionVM == null || admissionVM.InformedBy == null? "" : admissionVM.InformedBy.Trim();
singaporeAdmission.GOPAmount = admissionVM == null ? 0 : admissionVM.GOPAmount;
singaporeAdmission.ExtendedGOP = admissionVM == null ? 0 : admissionVM.ExtendedGOP;
singaporeAdmission.FinalAmount = admissionVM == null ? 0 : admissionVM.FinalAmount;
singaporeAdmission.ConsultantFee = admissionVM == null ? 0 : admissionVM.ConsultantFee;
singaporeAdmission.FinalBillReceivedDate = !string.IsNullOrEmpty(admissionVM.FinalBillRecievedDate) ? DateTime.ParseExact(admissionVM.FinalBillRecievedDate, "dd/MM/yyyy", CultureInfo.InvariantCulture) : DateTime.Parse("01/01/1900");
singaporeAdmission.PaymentGivenToAccount = admissionVM == null ? 0 : admissionVM.PaymentGivenToAccount;
singaporeAdmission.ReferalFee = admissionVM == null ? 0 : admissionVM.ReferalFee;
singaporeAdmission.FinalBill = admissionVM == null ? 0 : admissionVM.FinalBillAmount;
singaporeAdmission.ReferalFeeReceivedDate = !string.IsNullOrEmpty(admissionVM.ReferalFeeReceivedDate) ? DateTime.ParseExact(admissionVM.ReferalFeeReceivedDate, "dd/MM/yyyy", CultureInfo.InvariantCulture) : DateTime.Parse("01/01/1900");
singaporeAdmission.PaymentGivenToAccount = admissionVM == null ? 0 : admissionVM.PaymentGivenToAccount;
singaporeAdmission.ReferalFeeReceivedTTNo = admissionVM == null || admissionVM.ReferalFeeReceivedTtTransfer == null? "": admissionVM.ReferalFeeReceivedTtTransfer.ToString(); // WRONG DT
singaporeAdmission.Remark = admissionVM == null || admissionVM.Remark == null ? "" : admissionVM.Remark.Trim();
singaporeAdmission.ExtendedGOPDate = !string.IsNullOrEmpty(admissionVM.ExtendedGOPDate) ? DateTime.ParseExact(admissionVM.ExtendedGOPDate, "dd/MM/yyyy", CultureInfo.InvariantCulture) : DateTime.Parse("01/01/1900");
singaporeAdmission.CMAInvoiceNumber = admissionVM == null || admissionVM.CMAInvoiceNumber == null ? "" : admissionVM.CMAInvoiceNumber;
singaporeAdmission.MemberID = 0;
singaporeAdmission.DependantID = 0;
#endregion
if (!isModify)
{
unitOfWork.tblAdmission_Singapore.Insert(singaporeAdmission);
}
else
{
unitOfWork.tblAdmission_Singapore.Update(singaporeAdmission);
}
unitOfWork.Save();
dbTransaction.Commit();
return(true);
}
catch (Exception ex)
{
dbTransaction.Rollback();
return(false);
}
}
}