public string Put()
{
RbacUser currentUser = HttpContext.Session.Get <RbacUser>("currentuser");
DanpheHTTPResponse <object> responseData = new DanpheHTTPResponse <object>();
responseData.Status = "OK";
try
{
BillingDbContext billingDbContext = new BillingDbContext(connString);
string reqType = this.ReadQueryStringData("reqType");
int depositId = ToInt(this.ReadQueryStringData("depositId"));
if (reqType == "updateDepositPrintCount")
{
BillingDeposit deposit = billingDbContext.BillingDeposits
.Where(a => a.DepositId == depositId)
.FirstOrDefault <BillingDeposit>();
if (deposit != null)
{
deposit.PrintCount = deposit.PrintCount == null || deposit.PrintCount == 0 ? 1 : deposit.PrintCount + 1;
deposit.PrintedOn = System.DateTime.Now; //Yubraj: 13th August'19
deposit.PrintedBy = currentUser.EmployeeId;
billingDbContext.Entry(deposit).Property(a => a.PrintCount).IsModified = true;
}
billingDbContext.SaveChanges();
responseData.Status = "OK";
}
}
catch (Exception ex)
{
responseData.Status = "Failed";
responseData.ErrorMessage = ex.Message + " exception details:" + ex.ToString();
}
return(DanpheJSONConvert.SerializeObject(responseData, true));
}
public TariffRegulator(IServiceScopeFactory scopeFactory)
{
var scope = scopeFactory.CreateScope();
var sp = scope.ServiceProvider;
_dbContext = sp.GetRequiredService <BillingDbContext>();
}
public static void UpdateInsuranceCurrentBalance(string connString,
int patientId,
int insuranceProviderId,
int currentUserId,
double amount,
bool isDeduct = false)
{
BillingDbContext dbContext = new BillingDbContext(connString);
try
{
InsuranceModel insurance = dbContext.Insurances.Where(ins => ins.PatientId == patientId && ins.InsuranceProviderId == insuranceProviderId).FirstOrDefault();
if (insurance != null)
{
insurance.CurrentBalance = isDeduct ? insurance.CurrentBalance - amount : amount;
insurance.ModifiedOn = DateTime.Now;
insurance.ModifiedBy = currentUserId;
dbContext.Entry(insurance).State = EntityState.Modified;
dbContext.SaveChanges();
}
else
{
throw new Exception("Unable to update Insurance Balance. Detail: Insurance object is null.");
}
}
catch (Exception ex)
{
throw new Exception("Unable to update Insurance Balance. Detail:" + ex.ToString());
}
}
public AccessRightsController(BillingDbContext ctx,
RoleManager <Role> roleMngr,
UserManager <User> userMngr,
IServiceScopeFactory ssFactory)
: base(ctx, roleMngr, userMngr, ssFactory)
{
}
public string Put(string reqType, int settlementId)
{
RbacUser currentUser = HttpContext.Session.Get <RbacUser>("currentuser");
DanpheHTTPResponse <object> responseData = new DanpheHTTPResponse <object>();
responseData.Status = "OK";//by default status would be OK, hence assigning at the top
try
{
if (reqType == "updateSettlementPrintCount")
{
BillingDbContext bilDbContext = new BillingDbContext(connString);
int settlmntId = settlementId;
var currSettlment = bilDbContext.BillSettlements.Where(s => s.SettlementId == settlmntId).FirstOrDefault();
if (currSettlment != null)
{
int?printCount = currSettlment.PrintCount.HasValue ? currSettlment.PrintCount : 0;
printCount += 1;
bilDbContext.BillSettlements.Attach(currSettlment);
currSettlment.PrintCount = printCount;
currSettlment.PrintedOn = System.DateTime.Now; //Yubraj: 13th August'19
currSettlment.PrintedBy = currentUser.EmployeeId;
bilDbContext.Entry(currSettlment).Property(b => b.PrintCount).IsModified = true;
bilDbContext.SaveChanges();
responseData.Results = new { SettlementId = settlementId, PrintCount = printCount };
}
}
}
catch (Exception ex)
{
responseData.Status = "Failed";
responseData.ErrorMessage = ex.Message + " exception details:" + ex.ToString();
}
return(DanpheJSONConvert.SerializeObject(responseData, true));
}
[HttpPost]// POST api/values
public string Post()
{
DanpheHTTPResponse <object> responseData = new DanpheHTTPResponse <object>();
int CreatedBy = ToInt(this.ReadQueryStringData("CreatedBy"));
responseData.Status = "OK";//by default status would be OK, hence assigning at the top
string ipDataString = this.ReadPostData();
string reqType = this.ReadQueryStringData("reqType");
try
{
BillingDbContext billingDbContext = new BillingDbContext(connString);
if (reqType == "")
{
}
else
{
responseData.Status = "failed";
responseData.ErrorMessage = "Invalid request type.";
}
//responseData.Results = null;
}
catch (Exception ex)
{
responseData.Status = "failed";
responseData.ErrorMessage = ex.Message + " exception details:" + ex.ToString();
}
return(DanpheJSONConvert.SerializeObject(responseData, true));
}
public PersonalDataModel(
UserManager <IdentityUser> userManager,
BillingDbContext dbContext)
{
_dbContext = dbContext;
_userManager = userManager;
}
public string getNephrologyPatients()
{
DanpheHTTPResponse <object> responseData = new DanpheHTTPResponse <object>();
BillingDbContext billingDbContext = new BillingDbContext(connString);
responseData.Results = "OK";
try
{
var nephPats = (from pat in billingDbContext.Patient
join bti in billingDbContext.BillingTransactionItems on pat.PatientId equals bti.PatientId
join msd in billingDbContext.ServiceDepartment on bti.ServiceDepartmentId equals msd.ServiceDepartmentId
where msd.IntegrationName == "Nephrology"
select new
{
pat.PatientId,
pat.PatientCode,
pat.Gender,
pat.Age,
pat.DateOfBirth,
pat.Address,
pat.PhoneNumber,
bti.ProviderName,
ShortName = pat.FirstName + " " + (string.IsNullOrEmpty(pat.MiddleName) ? "" : pat.MiddleName + " ") + pat.LastName,
}).OrderByDescending(patient => patient.PatientId).ToList();
responseData.Results = nephPats;
responseData.Status = "OK";
}
catch (Exception ex)
{
responseData.Status = "Failed";
responseData.ErrorMessage = ex.Message + " exception details:" + ex.ToString();
}
return(DanpheJSONConvert.SerializeObject(responseData, true));
}
//this function post IRD posting log details to Danphe IRD_Log table
public static void PostIRDLog(IRDLogModel irdLogdata, BillingDbContext dbContext)
{
try
{
irdLogdata.CreatedOn = DateTime.Now;
string url_IRDNepal = ConfigurationManager.AppSettings["url_IRDNepal"];
switch (irdLogdata.BillType)
{
case "billing-sales":
{
string api_SalesIRDNepal = ConfigurationManager.AppSettings["api_SalesIRDNepal"];
irdLogdata.UrlInfo = url_IRDNepal + "/" + api_SalesIRDNepal;
break;
}
case "billing-sales-return":
{
string api_SalesReturnIRDNepal = ConfigurationManager.AppSettings["api_SalesReturnIRDNepal"];
irdLogdata.UrlInfo = url_IRDNepal + "/" + api_SalesReturnIRDNepal;
break;
}
}
dbContext.IRDLog.Add(irdLogdata);
dbContext.SaveChanges();
}
catch (Exception ex)
{
}
}
private static async void PersistsToCache(IEnumerable <InvoiceProformaItemDTO> list)
{
System.Diagnostics.Debug.WriteLine("<WRITING_TO_CACHE>");
var recsPerWrite = Properties.Settings.Default.RecordsPerWrite;
var list1 = list.ToArray();
var list2 = new List <InvoiceProformaItemDTO>();
using (var dao = new BillingDbContext())
{
dao.Configuration.AutoDetectChangesEnabled = false;
for (var i = 0; i < list1.Count(); i++)
{
list2.Add(list1[i]);
if (i % recsPerWrite == 0 && i != 0)
{
dao.CachedBillings.AddRange(list2);
dao.SaveChanges();
list2 = new List <InvoiceProformaItemDTO>();
}
}
dao.CachedBillings.AddRange(list2);
dao.SaveChanges();
dao.Configuration.AutoDetectChangesEnabled = true;
}
System.Diagnostics.Debug.WriteLine("</WRITING_TO_CACHE>");
await Task.Delay(2000);
}
public AccessRightsManager(IServiceScopeFactory scopeFactory)
{
var scope = scopeFactory.CreateScope();
var sp = scope.ServiceProvider;
_userManager = sp.GetRequiredService <UserManager <User> >();
_dbContext = sp.GetRequiredService <BillingDbContext>();
}
public ClientController(BillingDbContext dbContext,
RoleManager <Role> roleManager,
UserManager <IdentityUser> userManager,
TariffRegulator tariffRegulator,
IServiceScopeFactory scopeFactory) : base(dbContext, roleManager, userManager, scopeFactory)
{
_tariffRegulator = tariffRegulator;
}
public CassaController(BillingDbContext dbContext,
RoleManager <Role> roleManager,
UserManager <IdentityUser> userManager,
AccessRightsManager rightsManager,
IServiceScopeFactory scopeFactory) : base(dbContext, roleManager, userManager, scopeFactory)
{
_rightsManager = rightsManager;
}
protected int GetTotalCached(long cachedVersion)
{
using (var dao = new BillingDbContext())
{
return((from o in dao.CachedBillings where o.CachedVersion == cachedVersion select o).Count());
//return sql.AsParallel().ToList().Count;
}
}
//updates billStatus in respective tables.
public static void UpdateRequisitionItemsBillStatus(BillingDbContext billingDbContext,
string serviceDepartmentName,
string billStatus, //provisional,paid,unpaid,returned
int userId,
long?requisitionId,
DateTime?modifiedDate)
{
string integrationName = billingDbContext.ServiceDepartment
.Where(a => a.ServiceDepartmentName == serviceDepartmentName)
.Select(a => a.IntegrationName).FirstOrDefault();
if (integrationName != null)
{
//update return status in lab
if (integrationName.ToLower() == "lab")
{
var labItem = billingDbContext.LabRequisitions.Where(req => req.RequisitionId == requisitionId).FirstOrDefault();
if (labItem != null)
{
labItem.BillingStatus = billStatus;
labItem.ModifiedOn = modifiedDate;
labItem.ModifiedBy = userId;
billingDbContext.Entry(labItem).Property(a => a.BillingStatus).IsModified = true;
billingDbContext.Entry(labItem).Property(a => a.ModifiedOn).IsModified = true;
billingDbContext.Entry(labItem).Property(a => a.ModifiedBy).IsModified = true;
}
}
//update return status for Radiology
else if (integrationName.ToLower() == "radiology")
{
var radioItem = billingDbContext.RadiologyImagingRequisitions.Where(req => req.ImagingRequisitionId == requisitionId).FirstOrDefault();
if (radioItem != null)
{
radioItem.BillingStatus = billStatus;
radioItem.ModifiedOn = modifiedDate;
radioItem.ModifiedBy = userId;
billingDbContext.Entry(radioItem).Property(a => a.BillingStatus).IsModified = true;
billingDbContext.Entry(radioItem).Property(a => a.ModifiedOn).IsModified = true;
billingDbContext.Entry(radioItem).Property(a => a.ModifiedBy).IsModified = true;
}
}
//update return status for Visit
else if (integrationName.ToLower() == "opd" || integrationName.ToLower() == "er")
{
var visitItem = billingDbContext.Visit.Where(vis => vis.PatientVisitId == requisitionId).FirstOrDefault();
if (visitItem != null)
{
visitItem.BillingStatus = billStatus;
visitItem.ModifiedOn = modifiedDate;
visitItem.ModifiedBy = userId;
billingDbContext.Entry(visitItem).Property(a => a.BillingStatus).IsModified = true;
billingDbContext.Entry(visitItem).Property(a => a.ModifiedOn).IsModified = true;
billingDbContext.Entry(visitItem).Property(a => a.ModifiedBy).IsModified = true;
}
}
billingDbContext.SaveChanges();
}
}
public static int?GetDepositReceiptNo(string connString)
{
int fiscalYearId = GetFiscalYear(connString).FiscalYearId;
BillingDbContext billDbContext = new BillingDbContext(connString);
int?receiptNo = (from depTxn in billDbContext.BillingDeposits
where depTxn.FiscalYearId == fiscalYearId
select depTxn.ReceiptNo).DefaultIfEmpty(0).Max();
return(receiptNo + 1);
}
public DbInitializer(IServiceScopeFactory scopeFactory)
{
var scope = scopeFactory.CreateScope();
var sp = scope.ServiceProvider;
_userManager = sp.GetRequiredService <UserManager <User> >();
_roleManager = sp.GetRequiredService <RoleManager <Role> >();
_dbContext = sp.GetRequiredService <BillingDbContext>();
_random = new Random();
}
public static int?GetProvisionalReceiptNo(string connString)
{
int fiscalYearId = GetFiscalYear(connString).FiscalYearId;
BillingDbContext billDbContext = new BillingDbContext(connString);
int?receiptNo = (from txnItems in billDbContext.BillingTransactionItems
where txnItems.ProvisionalFiscalYearId == fiscalYearId
select txnItems.ProvisionalReceiptNo).DefaultIfEmpty(0).Max();
return(receiptNo + 1);
}
protected List <InvoiceProformaItemDTO> GetCachedBillings(long cachedVersion)
{
using (var dao = new BillingDbContext())
{
System.Diagnostics.Debug.WriteLine("<READ_FROM_CACHE/>");
var sql = from o in dao.CachedBillings where o.CachedVersion == cachedVersion select o;
var list1 = sql.AsParallel().ToList();
return(list1);
}
}
private int GetSettlementReceiptNo(BillingDbContext dbContext)
{
int?currSettlmntNo = dbContext.BillSettlements.Max(a => a.SettlementReceiptNo);
if (!currSettlmntNo.HasValue)
{
currSettlmntNo = 0;
}
return(currSettlmntNo.Value + 1);
}
protected int CountZeroVersion(RunInvoiceHeaderDTO head, BillingDbContext dao)
{
var sql = from o in dao.InvoiceProformaHeaders.Where(o => o.BillingBlock == head.BillingDocsCriteria &&
o.ReasonForRejection ==
head.ReasonForRejection &&
o.ProformaFlag == head.ProformaFlag &&
o.BillingNo == head.BillingNo &&
o.SoldToParty == head.SoldToParty &&
o.Version == 0)
select o;
return(sql.Count());
}
public override SAPResponse ExecuteCommand()
{
InMemoryCache.Instance.ClearCached(Username + Suffix.PERFORMED_INITIAL_SAVE);
InMemoryCache.Instance.Cache(Username + Suffix.PERFORMED_INITIAL_SAVE, true);
var header = InMemoryCache.Instance.GetCached(Username + Suffix.REQUEST_HEADER) as RunInvoiceHeaderDTO;
if (header == null)
{
return(null);
}
using (var dao = new BillingDbContext())
{
System.Diagnostics.Debug.WriteLine("<CREATE_ZERO_VERSION CALL = 'FROM SAVE INITIAL' />");
CreateOrOverwriteZeroVersion(true, dao);
}
return(null);
}
public int?SaveBillingBlock(string user, string billingNumber)
{
List <ContractBlock> listContract = new List <ContractBlock>();
const string FUNCTIONAL_BAPI = "ZBAPI_SALESORDER_CHANGE";
using (var dao = new BillingDbContext())
{
var sql1 = from o in dao.InvoiceProformaHeaders.Where(o => o.BillingNo == billingNumber).OrderByDescending(o => o.Version).Include(o => o.Billings)
select o;
var count = sql1.ToList().Count;
if (count > 1)
{
InvoiceProformaHeader oldHeaderVersion = sql1.ToArray()[0];
var dest = GetRfcDestination();
if (dest != null)
{
var repo = dest.Repository;
var func = repo.CreateFunction(FUNCTIONAL_BAPI);
IRfcTable I_REJECT1 = func.GetTable("I_REJECT1");
foreach (var bi in oldHeaderVersion.Billings)
{
I_REJECT1.Append();
I_REJECT1.SetValue("MANDT", "999");
I_REJECT1.SetValue("VBELN", bi.AUBEL);
I_REJECT1.SetValue("POSNR", bi.AUPOS);
I_REJECT1.SetValue("ABGRU", bi.VBAK_ABGRU_T);
}
func.Invoke(dest);
}
}
}
return(1);
}
public static void AddPatientInsurancePackage(BillingDbContext dbContext, int packageId, int patientId, int currentUserId)
{
try
{
PatientInsurancePackageTransactionModel patInsPkgTxn = new PatientInsurancePackageTransactionModel();
patInsPkgTxn.PatientId = patientId;
patInsPkgTxn.PackageId = packageId;
patInsPkgTxn.StartDate = DateTime.Now;
patInsPkgTxn.CreatedOn = DateTime.Now;
patInsPkgTxn.CreatedBy = currentUserId;
patInsPkgTxn.IsActive = true;
dbContext.PatientInsurancePackageTransactions.Add(patInsPkgTxn);
dbContext.SaveChanges();
}
catch (Exception ex)
{
throw new Exception("Unable to update Insurance Balance. Detail:" + ex.ToString());
}
}
[HttpPost]// POST api/values
public string Post()
{
DanpheHTTPResponse <object> responseData = new DanpheHTTPResponse <object>(); //type 'object' since we have variable return types
responseData.Status = "OK"; //by default status would be OK, hence assigning at the top
string ipDataString = this.ReadPostData();
string reqType = this.ReadQueryStringData("reqType");
try
{
RbacUser currentUser = HttpContext.Session.Get <RbacUser>("currentuser");
BillingDbContext billingDbContext = new BillingDbContext(connString);
if (reqType == "Deposit")
{
BillingDeposit deposit = DanpheJSONConvert.DeserializeObject <BillingDeposit>(ipDataString);
deposit.CreatedOn = System.DateTime.Now;
deposit.CreatedBy = currentUser.EmployeeId;
BillingFiscalYear fiscYear = BillingBL.GetFiscalYear(connString);
deposit.FiscalYearId = fiscYear.FiscalYearId;
if (deposit.DepositType != "depositdeduct")
{
deposit.ReceiptNo = BillingBL.GetDepositReceiptNo(connString);
}
deposit.FiscalYear = fiscYear.FiscalYearFormatted;
EmployeeModel currentEmp = billingDbContext.Employee.Where(emp => emp.EmployeeId == currentUser.EmployeeId).FirstOrDefault();
deposit.BillingUser = currentEmp.FirstName + " " + currentEmp.LastName;
deposit.IsActive = true; //yubraj: 18th Dec '18
billingDbContext.BillingDeposits.Add(deposit);
billingDbContext.SaveChanges();
responseData.Status = "OK";
responseData.Results = deposit;//check if we need to send back the whole input object back to client.--sudarshan
}
}
catch (Exception ex)
{
responseData.Status = "Failed";
responseData.ErrorMessage = ex.Message + " exception details:" + ex.ToString();
}
return(DanpheJSONConvert.SerializeObject(responseData, true));
}
public override SAPResponse ExecuteQuery()
{
InMemoryCache.Instance.ClearCached(Username + Suffix.QUERIED_VERSION_FROM_DB);
using (var dao = new BillingDbContext())
{
var resp = new InvoiceProformaBillingRunsDTO();
var sql = from o in dao.InvoiceProformaHeaders.Where(o => o.Version > -1) select o;
var list2 = sql.ToList();
resp.Items = list2.Select(h => new InvoiceProformaBillingRunDTO
{
Created = h.Created,
Version = h.Version,
No = (int)h.No,
SoldToParty = h.SoldToParty,
Draft = h.Draft,
EndDate = h.EndDate,
StartDate = h.StartDate
}).ToList();
return(resp);
}
}
//added: sud-31May'18 -- to display TotalProvisional, TotalCredit & TotalDepositBalances in billing dashboard
public string BILLDsbOverallBillStatus()
{
DanpheHTTPResponse <object> responseData = new DanpheHTTPResponse <object>();
try
{
BillingDbContext bilDbContext = new BillingDbContext(connString);
//added: sud: 31May'18-- to show in dashboard
double?provisionalTot = bilDbContext.BillingTransactionItems
.Where(itm => itm.BillStatus == "provisional").Sum(itm => itm.TotalAmount);
double?creditTotAmt = bilDbContext.BillingTransactions
.Where(txn => txn.BillStatus == "unpaid").Sum(txn => txn.TotalAmount);
double?totalDeposit = bilDbContext.BillingDeposits.Where(dep => dep.DepositType.ToLower() == "deposit").Sum(dep => dep.Amount);
double?totalDeduct = bilDbContext.BillingDeposits.Where(dep => dep.DepositType.ToLower() == "depositdeduct").Sum(dep => dep.Amount);
//make values zero if null.
totalDeposit = totalDeposit != null ? totalDeposit : 0;
totalDeduct = totalDeduct != null ? totalDeduct : 0;
var retObj = new
{
TotalProvisional = provisionalTot,
TotalCredits = creditTotAmt,
DepositBalance = totalDeposit - totalDeduct
};
responseData.Results = retObj;
responseData.Status = "OK";
}
catch (Exception ex)
{
responseData.Status = "Failed";
responseData.ErrorMessage = ex.Message;
}
return(DanpheJSONConvert.SerializeObject(responseData));
}
public static Boolean ReAssignProviderTxn(VisitDbContext visitDb, VisitModel visit, BillingDbContext billingDb)
{
using (var dbContextTxn = visitDb.Database.BeginTransaction())
{
try
{
//updating visit-table
visitDb.Visits.Attach(visit);
visitDb.Entry(visit).Property(x => x.ProviderId).IsModified = true;
visitDb.Entry(visit).Property(x => x.ProviderName).IsModified = true;
visitDb.Entry(visit).Property(x => x.ModifiedBy).IsModified = true;
visitDb.Entry(visit).Property(x => x.ModifiedOn).IsModified = true;
visitDb.Entry(visit).Property(x => x.Remarks).IsModified = true;
visitDb.SaveChanges();
//updating billingTxnItem table
//getting ServiceDepartmentId of OPD
int servDeptId = (from d in billingDb.ServiceDepartment
where d.ServiceDepartmentName == "OPD"
select d.ServiceDepartmentId).FirstOrDefault();
//for updating get data from table using PatientVisitId as RequisitionId
BillingTransactionItemModel billitm = (from b in billingDb.BillingTransactionItems
where b.RequisitionId == visit.PatientVisitId && b.ServiceDepartmentId == servDeptId
select b).FirstOrDefault();
//assiging updated values
billitm.ProviderId = visit.ProviderId;
billitm.ProviderName = visit.ProviderName;
billingDb.BillingTransactionItems.Attach(billitm);
billingDb.Entry(billitm).Property(x => x.ProviderId).IsModified = true;
billingDb.Entry(billitm).Property(x => x.ProviderName).IsModified = true;
billingDb.SaveChanges();
//Commit Transaction
dbContextTxn.Commit();
return(true);
}
catch (Exception ex)
{
//Rollback all transaction if exception occured
dbContextTxn.Rollback();
throw ex;
}
}
}
public override SAPResponse ExecuteQuery()
{
var resp = new InvoiceProformaItemsDTO();
var isFromDB = InMemoryCache.Instance.GetCached(Username + Suffix.QUERIED_FROM_DB) is bool && (bool)InMemoryCache.Instance.GetCached(Username + Suffix.QUERIED_FROM_DB);
#region "QUERY FROM DB"
if (isFromDB)
{
System.Diagnostics.Debug.WriteLine("<QUERY_TOTAL_RECORDS FROM = 'DB'>");
var rawHead = InMemoryCache.Instance.GetCached(Username + Suffix.QUERIED_PROFORMA_HEADER) as InvoiceProformaHeaderDto;
InMemoryCache.Instance.ClearCached(Username + Suffix.CACHED_VERSION);
using (var dao = new BillingDbContext())
{
var sql0 = from o in dao.InvoiceProformaHeaders.Where(o => o.No == rawHead.No) select o;
var count = sql0.Count();
if (count > 0)
{
var cachedVersion = DateTime.Now.Ticks;
InMemoryCache.Instance.Cache(Username + Suffix.CACHED_VERSION, cachedVersion);
// Load all by selected version
var sql2 = from o in dao.InvoiceProformaHeaders.Where(o => o.No == rawHead.No).Include(o => o.Billings) select o;
var verHead = sql2.AsParallel().FirstOrDefault();
if (verHead != null && verHead.Version == 0)
{
var list1 = verHead.Billings;
InMemoryCache.Instance.Cache(Username + Suffix.QUERIED_PROFORMA_TOTAL_RECS, list1.Count);
var list2 = SAPHandlerHelper.Instance.FromListOfBillingModels2DTOs(list1, cachedVersion);
resp.Items = list2.ToList();
}
else if (verHead != null)
{
// Load all version 0
System.Diagnostics.Debug.WriteLine("<ROWS_QUERY VERSION = '0'/>");
var sql1 =
from o in dao.InvoiceProformaHeaders.Where(o => o.BillingNo == verHead.BillingNo &&
o.BillingBlock == verHead.BillingBlock &&
o.ProformaFlag == verHead.ProformaFlag &&
o.ReasonForRejection ==
verHead.ReasonForRejection &&
o.Version == 0).Include(o => o.Billings)
select o;
var zeroHeader = sql1.FirstOrDefault();
if (zeroHeader == null)
{
return(null);
}
var list1 = zeroHeader.Billings;
System.Diagnostics.Debug.WriteLine("<ROWS_QUERY COMBINING'/>");
var list2 = verHead.Billings;
foreach (var upb in list2)
{
InvoiceProformaBilling upb1 = upb;
var bil = from o in list1.Where(o => o.VBAK_VBELN == upb1.VBAK_VBELN &&
o.VBAK_POSNR == upb1.VBAK_POSNR)
select o;
var dest = bil.FirstOrDefault();
if (dest == null)
{
list1.Add(upb);
}
else
{
SAPHandlerHelper.Instance.CopyBillingValues(upb, dest);
}
}
var list3 = SAPHandlerHelper.Instance.FromListOfBillingModels2DTOs(list1, cachedVersion);
//var list4 = list3 as InvoiceProformaItemDTO[] ?? list3.ToArray();
InMemoryCache.Instance.Cache(Username + Suffix.QUERIED_PROFORMA_TOTAL_RECS, list3.Count());
resp.Items = list3;
}
else
{
throw new FaultException("Selected header is null!");
}
}
else
{
throw new FaultException("Selected header is null!");
}
}
}
#endregion
#region "QUERY FROM SAP"
else
{
InMemoryCache.Instance.ClearCached(Username + Suffix.CACHED_VERSION);
System.Diagnostics.Debug.WriteLine("<QUERY_TOTAL_RECORDS FROM = 'SAP'>");
var cred = ParseCredential(Username);
var dest = SAPConnectionFactory.Instance.GetRfcDestination(cred);
if (dest != null)
{
var cachedVersion = DateTime.Now.Ticks;
InMemoryCache.Instance.Cache(Username + Suffix.CACHED_VERSION, cachedVersion);
var totalBills = InMemoryCache.Instance.GetCached(Username + Suffix.QUERIED_PROFORMA_TOTAL_RECS) is int
?(int)InMemoryCache.Instance.GetCached(Username + Suffix.QUERIED_PROFORMA_TOTAL_RECS)
: 0;
var sessionId = WaitForSessionId();
var repo = dest.Repository;
var func = repo.CreateFunction("ZBAPI_PROFORMA_PAGING");
func.SetValue("SESSIONID", sessionId);
func.SetValue("STARTROW", 0);
func.SetValue("ENDROW", totalBills);
func.Invoke(dest);
var proformaTbl = func.GetTable("PROFORMA");
var ret = proformaTbl.Select(e => new InvoiceProformaItemDTO
{
VBELN = e.GetString("VBELN"),
POSNR = e.GetString("POSNR"),
AUBEL = e.GetString("AUBEL"),
AUPOS = e.GetString("AUPOS"),
MATKL = e.GetString("MATKL"),
ITEM_TYPE = e.GetString("ITEM_TYPE"),
CATEGORY = e.GetString("CATEGORY"),
SUBCATEGORY = e.GetString("SUBCATEGORY"),
WGBEZ = e.GetString("WGBEZ"),
MATNR = e.GetString("MATNR"),
ARKTX = e.GetString("ARKTX"),
FKIMG = e.GetString("FKIMG"),
VRKME = e.GetString("VRKME"),
TOTAL1 = e.GetString("TOTAL1"),
TOTAL2 = e.GetString("TOTAL2"),
TOTAL3 = e.GetString("TOTAL3"),
TOTAL4 = e.GetString("TOTAL4"),
TOTAL5 = e.GetString("TOTAL5"),
VBAK_VBELN = e.GetString("VBAK_VBELN"),
VBAK_POSNR = e.GetString("VBAK_POSNR"),
VBAK_ZMENG = e.GetString("VBAK_ZMENG"),
VBAK_ZIEME = e.GetString("VBAK_ZIEME"),
VBAK_KONDM = e.GetString("VBAK_KONDM"),
VBAK_VTEXT1 = e.GetString("VBAK_VTEXT1"),
VBAK_BSTKD_E = e.GetString("VBAK_BSTKD_E"),
VBAK_EQUNR = e.GetString("VBAK_EQUNR"),
VBAK_TYPBZ = e.GetString("VBAK_TYPBZ"),
VBAK_MAPAR = e.GetString("VBAK_MAPAR"),
VBAK_SERGE = e.GetString("VBAK_SERGE"),
VBAK_LOCATION = e.GetString("VBAK_LOCATION"),
VBAK_PERNR1 = e.GetString("VBAK_PERNR1"),
VBAK_PERNR2 = e.GetString("VBAK_PERNR2"),
VBAK_EMPNAME = e.GetString("VBAK_EMPNAME"),
VBAK_PERSAREA = e.GetString("VBAK_PERSAREA"),
VBAK_PERSAREA_T = e.GetString("VBAK_PERSAREA_T"),
VBAK_SUBAREA = e.GetString("VBAK_SUBAREA"),
VBAK_SUBAREA_T = e.GetString("VBAK_SUBAREA_T"),
VBAK_ORGUNIT = e.GetString("VBAK_ORGUNIT"),
VBAK_ORGUNIT_T = e.GetString("VBAK_ORGUNIT_T"),
VBAK_OUCODE = e.GetString("VBAK_OUCODE"),
VBAK_KOSTL = e.GetString("VBAK_KOSTL"),
VBAK_WAERK = e.GetString("VBAK_WAERK"),
VBAK_NETWR = e.GetString("VBAK_NETWR"),
VBAK_BEDAT = e.GetString("VBAK_BEDAT"),
VBAK_ENDAT = e.GetString("VBAK_ENDAT"),
VBAK_ABGRU_T = e.GetString("VBAK_ABGRU_T"),
VBAK_PS_PSP_PNR = e.GetString("VBAK_PS_PSP_PNR"),
VBAK_PSTYV = e.GetString("VBAK_PSTYV"),
VBAK_KONDA = e.GetString("VBAK_KONDA"),
VBAK_VTEXT2 = e.GetString("VBAK_VTEXT2"),
VBAK_BSTKD = e.GetString("VBAK_BSTKD"),
VBAK_BSTDK = e.GetString("VBAK_BSTDK"),
VBAK_NAME = e.GetString("VBAK_NAME"),
VBAK_APPROVAL = e.GetString("VBAK_APPROVAL"),
VBAK_NOTE = e.GetString("VBAK_NOTE"),
VBAK_ADDRESS = e.GetString("VBAK_ADDRESS"),
VBAK_FAKSK_T = e.GetString("VBAK_FAKSK_T"),
VBAK_EQ_YEAR = e.GetString("VBAK_EQ_YEAR"),
LogMessage = "", //NOTO
CachedVersion = cachedVersion,
NO = e.GetString("VBAK_VBELN") + "#" + e.GetString("VBAK_POSNR"),
});
Task.Run(() => PersistsToCache(ret));
resp.Items = ret.ToList();
// InMemoryCache.Instance.ClearCached(Username + Suffix.QUERIED_SAP_SESSIONID);
}
else
{
System.Diagnostics.Debug.WriteLine("</QUERY_TOTAL_RECORDS>");
throw new FaultException("RfcDestination is null!");
}
}
#endregion
return(resp);
}
public string Get(string reqType,
string searchText,
DateTime toDate,
DateTime fromDate)
{
DanpheHTTPResponse <object> responseData = new DanpheHTTPResponse <object>();
string ipDataString = this.ReadPostData();
responseData.Status = "OK";//by default status would be OK, hence assigning at the top
try
{
BillingDbContext billingDbContext = new BillingDbContext(connString);
if (reqType == "insurance-patients-list")
{
PatientDbContext patDbContext = new PatientDbContext(connString);
var allPats = (from pat in patDbContext.Patients
join ins in patDbContext.Insurances on pat.PatientId equals ins.PatientId
join country in patDbContext.CountrySubdivisions
on pat.CountrySubDivisionId equals country.CountrySubDivisionId
where pat.IsActive == true
select new
{
PatientId = pat.PatientId,
PatientCode = pat.PatientCode,
ShortName = pat.FirstName + " " + (string.IsNullOrEmpty(pat.MiddleName) ? "" : pat.MiddleName + " ") + pat.LastName,
FirstName = pat.FirstName,
LastName = pat.LastName,
MiddleName = pat.MiddleName,
PatientNameLocal = pat.PatientNameLocal,
Age = pat.Age,
Gender = pat.Gender,
PhoneNumber = pat.PhoneNumber,
DateOfBirth = pat.DateOfBirth,
Address = pat.Address,
IsOutdoorPat = pat.IsOutdoorPat,
CreatedOn = pat.CreatedOn,
CountryId = pat.CountryId,
CountrySubDivisionId = pat.CountrySubDivisionId,
CountrySubDivisionName = country.CountrySubDivisionName,
pat.BloodGroup,
CurrentBalance = ins.CurrentBalance,
InsuranceProviderId = ins.InsuranceProviderId,
IMISCode = ins.IMISCode,
PatientInsuranceInfoId = ins.PatientInsuranceInfoId
}).OrderByDescending(p => p.PatientInsuranceInfoId).ToList();
responseData.Results = allPats;
}
else if (reqType == "insurance-billing-items")
{
var itemList = (from item in billingDbContext.BillItemPrice
join srv in billingDbContext.ServiceDepartment on item.ServiceDepartmentId equals srv.ServiceDepartmentId
where item.IsActive == true && item.InsuranceApplicable == true
select new
{
BillItemPriceId = item.BillItemPriceId,
ServiceDepartmentId = srv.ServiceDepartmentId,
ServiceDepartmentName = srv.ServiceDepartmentName,
ServiceDepartmentShortName = srv.ServiceDepartmentShortName,
ItemId = item.ItemId,
ItemName = item.ItemName,
ProcedureCode = item.ProcedureCode,
Price = item.GovtInsurancePrice,
TaxApplicable = item.TaxApplicable,
DiscountApplicable = item.DiscountApplicable,
Description = item.Description,
IsDoctorMandatory = item.IsDoctorMandatory,
item.IsInsurancePackage
}).ToList().OrderBy(a => a.ServiceDepartmentId).ThenBy(a => a.ItemId);
responseData.Status = "OK";
responseData.Results = itemList;
}
else if (reqType == "unclaimed-insurance-bills")
{
var unclaimedInvoicesList = (from txn in billingDbContext.BillingTransactions
join pat in billingDbContext.Patient on txn.PatientId equals pat.PatientId
join fis in billingDbContext.BillingFiscalYears on txn.FiscalYearId equals fis.FiscalYearId
where txn.IsInsuranceBilling == true && txn.IsInsuranceClaimed == false && txn.ReturnStatus == false &&
(DbFunctions.TruncateTime(txn.CreatedOn) >= fromDate.Date && DbFunctions.TruncateTime(txn.CreatedOn) <= toDate.Date)
select new
{
BillingTransactionId = txn.BillingTransactionId,
BillingDate = txn.CreatedOn,
InvoiceNo = txn.InvoiceNo,
PatientFName = pat.FirstName,
PatientMName = pat.MiddleName,
PatientLName = pat.LastName,
TotalAmount = txn.TotalAmount,
FiscalYear = fis.FiscalYearName,
IsInsuranceClaimed = txn.IsInsuranceClaimed
//FiscalYearId= txn.FiscalYearId
}).OrderByDescending(invoice => invoice.BillingDate).ToList();
responseData.Status = "OK";
responseData.Results = unclaimedInvoicesList;
}
else if (reqType == "insurance-packages")
{
List <BillingPackageModel> packageList = billingDbContext.BillingPackages.Where(a => a.IsActive == true && a.InsuranceApplicable == true)
.OrderBy(a => a.BillingPackageName).ToList();
if (packageList.Count > 0)
{
foreach (var package in packageList)
{
string jsonValues = "[]";//by default it'll be empty json-array.
if (!string.IsNullOrEmpty(package.BillingItemsXML))
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(package.BillingItemsXML);
jsonValues = JsonConvert.SerializeXmlNode(doc, Newtonsoft.Json.Formatting.None, true);
}
package.BillingItemsXML = jsonValues;
}
}
responseData.Status = "OK";
responseData.Results = packageList;
}
//Yubraj: 21st July '19 -- Getting All Patient which does not have Insurance...
else if (reqType == "all-patients-for-insurance")
{
PatientDbContext patDbContext = new PatientDbContext(connString);
if (string.IsNullOrEmpty(searchText))
{
responseData.Results = new List <string>();//send empty string.
}
else
{
var allPats = (from pat in patDbContext.Patients
join country in patDbContext.CountrySubdivisions
on pat.CountrySubDivisionId equals country.CountrySubDivisionId
//left join insurance information.
from ins in patDbContext.Insurances.Where(a => a.PatientId == pat.PatientId).DefaultIfEmpty()
where pat.IsActive == true && (pat.IsOutdoorPat == null || pat.IsOutdoorPat == false) //exclude Inactive and OutDoor patients.
&& ((pat.FirstName + " " + (string.IsNullOrEmpty(pat.MiddleName) ? "" : pat.MiddleName + " ") + pat.LastName + pat.PatientCode + pat.PhoneNumber).Contains(searchText))
select new
{
PatientId = pat.PatientId,
PatientCode = pat.PatientCode,
ShortName = pat.FirstName + " " + (string.IsNullOrEmpty(pat.MiddleName) ? "" : pat.MiddleName + " ") + pat.LastName,
FirstName = pat.FirstName,
LastName = pat.LastName,
MiddleName = pat.MiddleName,
Age = pat.Age,
Gender = pat.Gender,
PhoneNumber = pat.PhoneNumber,
DateOfBirth = pat.DateOfBirth,
Address = pat.Address,
CreatedOn = pat.CreatedOn,
CountryId = pat.CountryId,
CountrySubDivisionId = pat.CountrySubDivisionId,
CountrySubDivisionName = country.CountrySubDivisionName,
CurrentBalance = ins != null ? ins.CurrentBalance : 0,
InsuranceProviderId = ins != null ? ins.InsuranceProviderId : 0,
IMISCode = ins != null ? ins.IMISCode : null
}).OrderByDescending(p => p.PatientId).ToList();
responseData.Results = allPats;
}
responseData.Status = "OK";
}
else
{
responseData.Status = "failed";
responseData.ErrorMessage = "Invalid request type.";
}
//responseData.Results = null;
}
catch (Exception ex)
{
responseData.Status = "Failed";
responseData.ErrorMessage = ex.Message + " exception details:" + ex.ToString();
}
return(DanpheJSONConvert.SerializeObject(responseData, true));
}
