///<summary>If not using clinics then supply an empty list of clinicNums. listClinicNums must have at least one item if using clinics.</summary> public static DataTable GetInsTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, List <long> listInsuranceTypes, List <long> listClaimPayGroups, bool hasAllProvs, bool hasAllClinics, bool hasInsuranceTypes, bool isGroupedByPatient, bool hasAllClaimPayGroups, bool doShowProvSeparate) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listInsuranceTypes, listClaimPayGroups, hasAllProvs, hasAllClinics, hasInsuranceTypes, isGroupedByPatient, hasAllClaimPayGroups, doShowProvSeparate)); } string whereProv = ""; if (!hasAllProvs) { whereProv += " AND claimproc.ProvNum IN("; for (int i = 0; i < listProvNums.Count; i++) { if (i > 0) { whereProv += ","; } whereProv += POut.Long(listProvNums[i]); } whereProv += ") "; } string whereClin = ""; //reports should no longer use the cache bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics))); if (hasClinicsEnabled) { whereClin += " AND claimproc.ClinicNum IN("; for (int i = 0; i < listClinicNums.Count; i++) { if (i > 0) { whereClin += ","; } whereClin += POut.Long(listClinicNums[i]); } whereClin += ") "; } string whereClaimPayGroup = ""; if (!hasAllClaimPayGroups) { whereClaimPayGroup = " AND PayGroup IN (" + String.Join(",", listClaimPayGroups) + ") "; } string queryIns = @"SELECT claimproc.DateCP,carrier.CarrierName,MAX(" + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + @") lfname,GROUP_CONCAT(DISTINCT provider.Abbr) Provider, "; if (hasClinicsEnabled) { queryIns += "clinic.Description clinicDesc, "; } queryIns += @"claimpayment.CheckNum,SUM(claimproc.InsPayAmt) amt,claimproc.ClaimNum,claimpayment.PayType FROM claimproc LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum LEFT JOIN patient ON claimproc.PatNum = patient.PatNum LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum LEFT JOIN provider ON provider.ProvNum=claimproc.ProvNum LEFT JOIN claimpayment ON claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum " ; if (hasClinicsEnabled) { queryIns += "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum "; } queryIns += "WHERE (claimproc.Status=1 OR claimproc.Status=4) " //received or supplemental + whereProv + whereClin + whereClaimPayGroup + "AND claimpayment.CheckDate >= " + POut.Date(dateFrom) + " " + "AND claimpayment.CheckDate <= " + POut.Date(dateTo) + " "; if (!hasInsuranceTypes && listInsuranceTypes.Count > 0) { queryIns += "AND claimpayment.PayType IN ("; for (int i = 0; i < listInsuranceTypes.Count; i++) { if (i > 0) { queryIns += ","; } queryIns += POut.Long(listInsuranceTypes[i]); } queryIns += ") "; } queryIns += @"GROUP BY claimproc.DateCP,claimproc.ClaimPaymentNum,"; if (doShowProvSeparate) { queryIns += @"provider.ProvNum,"; } if (hasClinicsEnabled) { queryIns += "claimproc.ClinicNum,clinic.Description,"; } queryIns += "carrier.CarrierName,claimpayment.CheckNum"; if (isGroupedByPatient) { queryIns += ",patient.PatNum"; } queryIns += " ORDER BY claimpayment.PayType,claimproc.DateCP,lfname"; if (!hasInsuranceTypes && listInsuranceTypes.Count == 0) { queryIns = DbHelper.LimitOrderBy(queryIns, 0); } return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryIns))); }
///<summary>If not using clinics, or for all clinics with clinics enabled, supply an empty list of clinicNums. If the user is restricted, for all ///clinics supply only those clinics for which the user has permission to access, otherwise it will be run for all clinics.</summary> public static DataTable GetPatTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums, List <long> listPatientTypes, bool hasAllProvs, bool hasAllClinics, bool hasPatientTypes, bool isGroupedByPatient, bool isUnearnedIncluded, bool doShowProvSeparate) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listPatientTypes, hasAllProvs, hasAllClinics, hasPatientTypes, isGroupedByPatient, isUnearnedIncluded, doShowProvSeparate)); } //reports should no longer use the cache bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics))); //patient payments----------------------------------------------------------------------------------------- //the selected columns have to remain in this order due to the way the report complex populates the returned sheet string queryPat = "SELECT payment.PayDate DatePay," + "MAX(" + DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ") lfname,GROUP_CONCAT(DISTINCT provider.Abbr),"; if (hasClinicsEnabled) { queryPat += "clinic.Description clinicDesc,"; } queryPat += "payment.CheckNum,SUM(COALESCE(paysplit.SplitAmt,0)) amt,payment.PayNum,ItemName,payment.PayType " + "FROM payment " + "LEFT JOIN paysplit ON payment.PayNum=paysplit.PayNum " + "LEFT JOIN patient ON payment.PatNum=patient.PatNum " + "LEFT JOIN provider ON paysplit.ProvNum=provider.ProvNum " + "LEFT JOIN definition ON payment.PayType=definition.DefNum "; if (hasClinicsEnabled) { queryPat += "LEFT JOIN clinic ON clinic.ClinicNum=paysplit.ClinicNum "; } queryPat += "WHERE payment.PayDate BETWEEN " + POut.Date(dateFrom) + " AND " + POut.Date(dateTo) + " "; if (hasClinicsEnabled && listClinicNums.Count > 0) { queryPat += "AND paysplit.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") "; } if (!hasAllProvs && listProvNums.Count > 0) { queryPat += "AND paysplit.ProvNum IN(" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") "; } if (!hasPatientTypes && listPatientTypes.Count > 0) { queryPat += "AND payment.PayType IN (" + string.Join(",", listPatientTypes.Select(x => POut.Long(x))) + ") "; } if (!isUnearnedIncluded) //UnearnedType of 0 means the paysplit is NOT unearned { queryPat += "AND paysplit.UnearnedType=0 "; } queryPat += "GROUP BY payment.PayNum,payment.PayDate,payment.CheckNum,definition.ItemName,payment.PayType "; if (doShowProvSeparate) { queryPat += ",provider.ProvNum "; } if (hasClinicsEnabled) { queryPat += ",clinic.Description "; } if (isGroupedByPatient) { queryPat += ",patient.PatNum "; } queryPat += "ORDER BY payment.PayType,payment.PayDate,lfname"; if (!hasPatientTypes && listPatientTypes.Count == 0) { queryPat = DbHelper.LimitOrderBy(queryPat, 0); } return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryPat))); }
public static DataTable GetClaimsNotSent(DateTime fromDate, DateTime toDate, List <long> listClinicNums , bool hasClaimTypeExpanded, ClaimNotSentStatuses claimStatusFilter) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), fromDate, toDate, listClinicNums , hasClaimTypeExpanded, claimStatusFilter)); } bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => !Prefs.GetBoolNoCache(PrefName.EasyNoClinics)); string command = ""; string whereClin = ""; string claimFilter = ""; if (hasClinicsEnabled && listClinicNums.Count > 0) //construct the IN statement for all of the selected clinics { whereClin += " AND claim.ClinicNum IN(" + string.Join(",", listClinicNums) + ")"; } if (hasClinicsEnabled) { command = "SELECT clinic.Abbr AS 'Clinic',"; } else { command = "SELECT "; } if (hasClaimTypeExpanded) { command += "claim.DateService,(CASE WHEN claim.ClaimType='P' THEN 'Primary' WHEN claim.ClaimType='S' THEN 'Secondary' " + "WHEN claim.ClaimType='Cap' THEN 'Capitation' ELSE claim.ClaimType END) AS ClaimType,"; } else { command += "claim.DateService,claim.ClaimType,"; } //Claim statuses of Unsent, Hold until pri, and Waiting are considered for "All" in this report. string claimStatusAll = "AND claim.ClaimStatus IN ('U','H','W')"; switch (claimStatusFilter) { case ClaimNotSentStatuses.Primary: claimFilter = "AND claim.ClaimType='P' " + claimStatusAll; break; case ClaimNotSentStatuses.Secondary: claimFilter = "AND claim.ClaimType='S' " + claimStatusAll; break; case ClaimNotSentStatuses.Holding: claimFilter = "AND claim.ClaimStatus='H'"; break; default: //All claimFilter += claimStatusAll; break; } string clinJoin = ""; if (hasClinicsEnabled) { clinJoin = " LEFT JOIN clinic ON clinic.ClinicNum=claim.ClinicNum"; } command += "(CASE WHEN claim.ClaimStatus='U' THEN 'Unsent' WHEN " + "claim.ClaimStatus='H' THEN 'Hold' WHEN claim.ClaimStatus='W' THEN 'WaitQ' ELSE claim.ClaimStatus END) AS ClaimStatus, " + "CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) as 'Patient Name',carrier.CarrierName" + ",claim.ClaimFee,GROUP_CONCAT(procedurecode.ProcCode SEPARATOR ', ') ProcCodes,claim.ClaimNum,claim.PatNum " + "FROM patient" + " INNER JOIN claim ON claim.PatNum=patient.PatNum" + " INNER JOIN claimproc ON claimproc.ClaimNum=claim.ClaimNum" + " INNER JOIN procedurelog ON procedurelog.ProcNum=claimproc.ProcNum" + " INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum" + " INNER JOIN insplan ON insplan.PlanNum=claim.PlanNum" + " INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum" + clinJoin + " WHERE claim.DateService >= " + POut.Date(fromDate) + " AND claim.DateService <= " + POut.Date(toDate) + whereClin + claimFilter + " GROUP BY claim.ClaimNum"; if (DataConnection.DBtype == DatabaseType.Oracle) { command += ",claim.Status,patient.LName,patient.FName,patient.MiddleI,carrier.CarrierName,claim.ClaimFee,claim.ClaimNum,claim.PatNum"; } command += " ORDER BY claim.DateService"; return(ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command))); }
///<summary>this code is similar to code in the phone tracking server. But here, we frequently only change clockStatus and ColorBar by setting employeeNum=-1. If employeeNum is not -1, then EmployeeName also gets set. If employeeNum==0, then clears employee from that row.</summary> public static void SetPhoneStatus(ClockStatusEnum clockStatus, int extens, long employeeNum = -1) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { Meth.GetVoid(MethodBase.GetCurrentMethod(), clockStatus, extens, employeeNum); return; } string command = @"SELECT phoneempdefault.EmployeeNum,phoneempdefault.IsTriageOperator,Description,phoneempdefault.EmpName,HasColor,phone.ClockStatus " + "FROM phone " + "LEFT JOIN phoneempdefault ON phone.Extension=phoneempdefault.PhoneExt " + "WHERE phone.Extension=" + POut.Long(extens); DataTable tablePhone = Db.GetTable(command); if (tablePhone.Rows.Count == 0) { //It would be nice if we could create a phone row for this extension. return; } long empNum = PIn.Long(tablePhone.Rows[0]["EmployeeNum"].ToString()); bool isTriageOperator = PIn.Bool(tablePhone.Rows[0]["IsTriageOperator"].ToString()); string empName = PIn.String(tablePhone.Rows[0]["EmpName"].ToString()); string clockStatusDb = PIn.String(tablePhone.Rows[0]["ClockStatus"].ToString()); Employee emp = Employees.GetEmp(employeeNum); if (emp != null) //A new employee is going to take over this extension. { empName = emp.FName; empNum = emp.EmployeeNum; } else if (employeeNum == 0) //Clear the employee from that row. { empName = ""; empNum = 0; } //if these values are null because of missing phoneempdefault row, they will default to false //PhoneEmpStatusOverride statusOverride=(PhoneEmpStatusOverride)PIn.Int(tablePhone.Rows[0]["StatusOverride"].ToString()); bool hasColor = PIn.Bool(tablePhone.Rows[0]["HasColor"].ToString()); #region DateTimeStart //When a user shows up as a color on the phone panel, we want a timer to be constantly going to show how long they've been off the phone. string dateTimeStart = ""; //It's possible that a new user has never clocked in before, therefore their clockStatus will be empty. Simply set it to the status that they are trying to go to. if (clockStatusDb == "") { clockStatusDb = clockStatus.ToString(); } if (clockStatus == ClockStatusEnum.Break || clockStatus == ClockStatusEnum.Lunch) { //The user is going on Lunch or Break. Start the DateTimeStart counter so we know how long they have been gone. dateTimeStart = "DateTimeStart=NOW(), "; } else if (clockStatus == ClockStatusEnum.Home) { //User is going Home. Always clear the DateTimeStart column no matter what. dateTimeStart = "DateTimeStart='0001-01-01', "; } else //User shows as a color on big phones and is not going to a status of Home, Lunch, or Break. Example: Available, Training etc. //Get the current clock status from the database. { ClockStatusEnum clockStatusCur = (ClockStatusEnum)Enum.Parse(typeof(ClockStatusEnum), clockStatusDb); //Start the clock if the user is going from a break status to any other non-break status. if (clockStatusCur == ClockStatusEnum.Home || clockStatusCur == ClockStatusEnum.Lunch || clockStatusCur == ClockStatusEnum.Break) { //The user is clocking in from home, lunch, or break. Start the timer up. if (hasColor) //Only start up the timer when someone with color clocks in. { dateTimeStart = "DateTimeStart=NOW(), "; } else //Someone with no color then reset the timer. They are back from break, that's all we need to know. { dateTimeStart = "DateTimeStart='0001-01-01', "; } } } string dateTimeNeedsHelpStart; if (clockStatus == ClockStatusEnum.NeedsHelp) { dateTimeNeedsHelpStart = "DateTimeNeedsHelpStart=NOW(), "; } else { dateTimeNeedsHelpStart = "DateTimeNeedsHelpStart=" + POut.DateT(DateTime.MinValue) + ", "; } #endregion //Update the phone row to reflect the new clock status of the user. string clockStatusNew = clockStatus.ToString(); if (clockStatus == ClockStatusEnum.None) { clockStatusNew = ""; } if (clockStatus == ClockStatusEnum.HelpOnTheWay && clockStatusDb == ClockStatusEnum.HelpOnTheWay.ToString()) //If HelpOnTheWay already { clockStatusNew = ClockStatusEnum.Available.ToString(); } command = "UPDATE phone SET ClockStatus='" + POut.String(clockStatusNew) + "', " + dateTimeStart + dateTimeNeedsHelpStart //+"ColorBar=-1, " //ColorBar is now determined at runtime by OD using Phones.GetPhoneColor. + "EmployeeNum=" + POut.Long(empNum) + ", " + "EmployeeName='" + POut.String(empName) + "' " + "WHERE Extension=" + extens; Db.NonQ(command); //Zero out any duplicate phone table rows for this employee. //This is possible if a user logged off and another employee logs into their computer. This would cause duplicate entries in the big phones window. UpdatePhoneToEmpty(employeeNum, extens); }
///<summary>Gets data for the history grid in the SendClaims window. The listEtransType must contain as least one item.</summary> public static DataTable RefreshHistory(DateTime dateFrom,DateTime dateTo,List<EtransType> listEtransType) { if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetTable(MethodBase.GetCurrentMethod(),dateFrom,dateTo,listEtransType); } string command="SELECT (CASE WHEN etrans.PatNum=0 THEN etrans.PatientNameRaw " +"ELSE CONCAT(CONCAT(patient.LName,', '),patient.FName) END) AS PatName," +"(CASE WHEN etrans.carrierNum=0 THEN etrans.CarrierNameRaw ELSE carrier.CarrierName END) AS CarrierName," +"clearinghouse.Description AS Clearinghouse,DateTimeTrans,etrans.OfficeSequenceNumber," +"etrans.CarrierTransCounter,Etype,etrans.ClaimNum,etrans.EtransNum,etrans.AckCode,etrans.Note,etrans.EtransMessageTextNum,etrans.TranSetId835," +"etrans.UserNum,etrans.PatNum " +"FROM etrans " +"LEFT JOIN carrier ON etrans.CarrierNum=carrier.CarrierNum " +"LEFT JOIN patient ON patient.PatNum=etrans.PatNum " +"LEFT JOIN clearinghouse ON clearinghouse.ClearinghouseNum=etrans.ClearinghouseNum WHERE " +DbHelper.DtimeToDate("DateTimeTrans")+" >= "+POut.Date(dateFrom)+" AND " +DbHelper.DtimeToDate("DateTimeTrans")+" <= "+POut.Date(dateTo)+" " +"AND Etype IN ("+POut.Long((int)listEtransType[0]); for(int i=1;i<listEtransType.Count;i++){//String.Join doesn't work because there's no way to cast the enums to ints in the function, db uses longs. command+=", "+POut.Long((int)listEtransType[i]); } command+=") " //For Canada, when the undo button is used from Manage | Send Claims, the ClaimNum is set to 0 instead of deleting the etrans entry. //For transaction types related to claims where the claimnum=0, we do not want them to show in the history section of Manage | Send Claims because they have been undone. +"AND (ClaimNum<>0 OR Etype NOT IN ("+POut.Long((int)EtransType.Claim_CA)+","+POut.Long((int)EtransType.ClaimCOB_CA)+","+POut.Long((int)EtransType.Predeterm_CA)+","+POut.Long((int)EtransType.ClaimReversal_CA)+")) " +"ORDER BY DateTimeTrans"; DataTable table=Db.GetTable(command); DataTable tHist=new DataTable("Table"); tHist.Columns.Add("patName"); tHist.Columns.Add("CarrierName"); tHist.Columns.Add("Clearinghouse"); tHist.Columns.Add("dateTimeTrans"); tHist.Columns.Add("OfficeSequenceNumber"); tHist.Columns.Add("CarrierTransCounter"); tHist.Columns.Add("etype"); tHist.Columns.Add("Etype"); tHist.Columns.Add("ClaimNum"); tHist.Columns.Add("EtransNum"); tHist.Columns.Add("ack"); tHist.Columns.Add("Note"); tHist.Columns.Add("EtransMessageTextNum"); tHist.Columns.Add("TranSetId835"); tHist.Columns.Add("UserNum"); tHist.Columns.Add("PatNum"); DataRow row; string etype; for(int i=0;i<table.Rows.Count;i++) { row=tHist.NewRow(); row["patName"]=table.Rows[i]["PatName"].ToString(); row["CarrierName"]=table.Rows[i]["CarrierName"].ToString(); row["Clearinghouse"]=table.Rows[i]["Clearinghouse"].ToString(); row["dateTimeTrans"]=PIn.DateT(table.Rows[i]["DateTimeTrans"].ToString()).ToShortDateString(); row["OfficeSequenceNumber"]=table.Rows[i]["OfficeSequenceNumber"].ToString(); row["CarrierTransCounter"]=table.Rows[i]["CarrierTransCounter"].ToString(); row["Etype"]=table.Rows[i]["Etype"].ToString(); etype=Lans.g("enumEtransType",((EtransType)PIn.Long(table.Rows[i]["Etype"].ToString())).ToString()); if(etype.EndsWith("_CA")){ etype=etype.Substring(0,etype.Length-3); } row["etype"]=etype; row["ClaimNum"]=table.Rows[i]["ClaimNum"].ToString(); row["EtransNum"]=table.Rows[i]["EtransNum"].ToString(); if(table.Rows[i]["AckCode"].ToString()=="A"){ row["ack"]=Lans.g("Etrans","Accepted"); } else if(table.Rows[i]["AckCode"].ToString()=="R") { row["ack"]=Lans.g("Etrans","Rejected"); } else if(table.Rows[i]["AckCode"].ToString()=="Recd") { row["ack"]=Lans.g("Etrans","Received"); } else { row["ack"]=""; } row["Note"]=table.Rows[i]["Note"].ToString(); row["EtransMessageTextNum"]=table.Rows[i]["EtransMessageTextNum"].ToString(); row["TranSetId835"]=table.Rows[i]["TranSetId835"].ToString(); row["UserNum"]=table.Rows[i]["UserNum"].ToString(); row["PatNum"]=table.Rows[i]["PatNum"].ToString(); tHist.Rows.Add(row); } return tHist; }
///<summary>Etrans type will be figured out by this class. Either TextReport, Acknowledge_997, Acknowledge_999, or StatusNotify_277.</summary> public static void ProcessIncomingReport(DateTime dateTimeTrans,long hqClearinghouseNum,string messageText,long userNum) { if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { Meth.GetVoid(MethodBase.GetCurrentMethod(),dateTimeTrans,hqClearinghouseNum,messageText,userNum); return; } Etrans etrans=CreateEtrans(dateTimeTrans,hqClearinghouseNum,messageText,userNum); string command; X12object Xobj=X12object.ToX12object(messageText); if(Xobj!=null) {//Is a correctly formatted X12 message. if(Xobj.IsAckInterchange()) { etrans.Etype=EtransType.Ack_Interchange; Etranss.Insert(etrans); //At some point in the future, we should use TA101 to match to batch number and TA104 to get the ack code, //then update historic etrans entries like we do for 997s, 999s and 277s. } else if(Xobj.Is997()) { X997 x997=new X997(messageText); etrans.Etype=EtransType.Acknowledge_997; etrans.BatchNumber=x997.GetBatchNumber(); Etranss.Insert(etrans); string batchack=x997.GetBatchAckCode(); if(batchack=="A"||batchack=="R") {//accepted or rejected command="UPDATE etrans SET AckCode='"+batchack+"', " +"AckEtransNum="+POut.Long(etrans.EtransNum) +" WHERE BatchNumber="+POut.Long(etrans.BatchNumber) +" AND ClearinghouseNum="+POut.Long(hqClearinghouseNum) +" AND DateTimeTrans > "+POut.DateT(dateTimeTrans.AddDays(-14)) +" AND DateTimeTrans < "+POut.DateT(dateTimeTrans.AddDays(1)) +" AND AckEtransNum=0"; Db.NonQ(command); } else {//partially accepted List<int> transNums=x997.GetTransNums(); string ack; for(int i=0;i<transNums.Count;i++) { ack=x997.GetAckForTrans(transNums[i]); if(ack=="A"||ack=="R") {//accepted or rejected command="UPDATE etrans SET AckCode='"+ack+"', " +"AckEtransNum="+POut.Long(etrans.EtransNum) +" WHERE BatchNumber="+POut.Long(etrans.BatchNumber) +" AND TransSetNum="+POut.Long(transNums[i]) +" AND ClearinghouseNum="+POut.Long(hqClearinghouseNum) +" AND DateTimeTrans > "+POut.DateT(dateTimeTrans.AddDays(-14)) +" AND DateTimeTrans < "+POut.DateT(dateTimeTrans.AddDays(1)) +" AND AckEtransNum=0"; Db.NonQ(command); } } } //none of the other fields make sense, because this ack could refer to many claims. } else if(Xobj.Is999()) { X999 x999=new X999(messageText); etrans.Etype=EtransType.Acknowledge_999; etrans.BatchNumber=x999.GetBatchNumber(); Etranss.Insert(etrans); string batchack=x999.GetBatchAckCode(); if(batchack=="A"||batchack=="R") {//accepted or rejected command="UPDATE etrans SET AckCode='"+batchack+"', " +"AckEtransNum="+POut.Long(etrans.EtransNum) +" WHERE BatchNumber="+POut.Long(etrans.BatchNumber) +" AND ClearinghouseNum="+POut.Long(hqClearinghouseNum) +" AND DateTimeTrans > "+POut.DateT(dateTimeTrans.AddDays(-14)) +" AND DateTimeTrans < "+POut.DateT(dateTimeTrans.AddDays(1)) +" AND AckEtransNum=0"; Db.NonQ(command); } else {//partially accepted List<int> transNums=x999.GetTransNums(); string ack; for(int i=0;i<transNums.Count;i++) { ack=x999.GetAckForTrans(transNums[i]); if(ack=="A"||ack=="R") {//accepted or rejected command="UPDATE etrans SET AckCode='"+ack+"', " +"AckEtransNum="+POut.Long(etrans.EtransNum) +" WHERE BatchNumber="+POut.Long(etrans.BatchNumber) +" AND TransSetNum="+POut.Long(transNums[i]) +" AND ClearinghouseNum="+POut.Long(hqClearinghouseNum) +" AND DateTimeTrans > "+POut.DateT(dateTimeTrans.AddDays(-14)) +" AND DateTimeTrans < "+POut.DateT(dateTimeTrans.AddDays(1)) +" AND AckEtransNum=0"; Db.NonQ(command); } } } //none of the other fields make sense, because this ack could refer to many claims. } else if(X277.Is277(Xobj)) { X277 x277=new X277(messageText); etrans.Etype=EtransType.StatusNotify_277; Etranss.Insert(etrans); List<string> listClaimIdentifiers=x277.GetClaimTrackingNumbers(); //Dictionary to run one update command per ack code for many claims. Dictionary <string,List<X12ClaimMatch>> dictClaimMatchesByAck=new Dictionary<string,List<X12ClaimMatch>>(); for(int i=0;i<listClaimIdentifiers.Count;i++) { X12ClaimMatch claimMatch=new X12ClaimMatch(); claimMatch.ClaimIdentifier=listClaimIdentifiers[i]; string[] arrayClaimInfo=x277.GetClaimInfo(claimMatch.ClaimIdentifier); claimMatch.PatFname=PIn.String(arrayClaimInfo[0]); claimMatch.PatLname=PIn.String(arrayClaimInfo[1]); claimMatch.DateServiceStart=PIn.DateT(arrayClaimInfo[6]); claimMatch.DateServiceEnd=PIn.DateT(arrayClaimInfo[7]); claimMatch.ClaimFee=PIn.Double(arrayClaimInfo[9]); claimMatch.SubscriberId=PIn.String(arrayClaimInfo[10]); claimMatch.EtransNum=etrans.EtransNum; string ack=arrayClaimInfo[3]; if(!dictClaimMatchesByAck.ContainsKey(ack)) { dictClaimMatchesByAck.Add(ack,new List<X12ClaimMatch>()); } dictClaimMatchesByAck[ack].Add(claimMatch); } foreach(string ack in dictClaimMatchesByAck.Keys) { List <long> listClaimNums=Claims.GetClaimFromX12(dictClaimMatchesByAck[ack]); if(listClaimNums!=null) { listClaimNums=listClaimNums.Where(x => x!=0).ToList(); if(listClaimNums.Count > 0) { //Locate the latest etrans entries for the claims based on DateTimeTrans with EType of ClaimSent or Claim_Ren and update the AckCode and AckEtransNum. //We overwrite existing acks from 997s, 999s and older 277s. command="UPDATE etrans SET AckCode='"+ack+"', " +"AckEtransNum="+POut.Long(etrans.EtransNum) +" WHERE EType IN ("+POut.Int((int)EtransType.ClaimSent)+","+POut.Int((int)EtransType.Claim_Ren)+") " +" AND ClaimNum IN("+String.Join(",",listClaimNums.Select(x => POut.Long(x)))+")" +" AND ClearinghouseNum="+POut.Long(hqClearinghouseNum) +" AND DateTimeTrans > "+POut.DateT(dateTimeTrans.AddDays(-14)) +" AND DateTimeTrans < "+POut.DateT(dateTimeTrans.AddDays(1)); Db.NonQ(command); } } //none of the other fields make sense, because this ack could refer to many claims. } } else if(X835.Is835(Xobj)) { etrans.Etype=EtransType.ERA_835; List <string> listTranSetIds=Xobj.GetTranSetIds(); List <Etrans> listEtrans=new List<Etrans>(); List <X835> list835s=new List<X835>(); //We pull in the 835 data in two loops so that we can ensure the 835 is fully parsed before we create any etrans entries. for(int i=0;i<listTranSetIds.Count;i++) { etrans.TranSetId835=listTranSetIds[i]; if(i>0) { etrans.EtransNum=0;//To get a new record to insert. } X835 x835=new X835(etrans,messageText,etrans.TranSetId835);//parse. If parsing fails, then no etrans entries will be inserted. etrans.CarrierNameRaw=x835.PayerName; List<string> listUniquePatientNames=new List<string>(); for(int j=0;j<x835.ListClaimsPaid.Count;j++) { string patName=x835.ListClaimsPaid[j].PatientName.ToString(false); if(!listUniquePatientNames.Contains(patName)) { listUniquePatientNames.Add(patName); } } if(listUniquePatientNames.Count==1) { etrans.PatientNameRaw=listUniquePatientNames[0]; } else { etrans.PatientNameRaw="("+listUniquePatientNames.Count+" "+Lans.g("Etranss","patients")+")"; } listEtrans.Add(etrans.Copy()); list835s.Add(x835); } //The 835 was completely parsed. Create etrans entries. for(int i=0;i<listEtrans.Count;i++) { etrans=listEtrans[i]; X835 x835=list835s[i]; Etranss.Insert(etrans);//insert List<long> listClaimNums=x835.ListClaimsPaid.Select(x => x.ClaimNum).Where(x => x!=0).ToList(); if(listClaimNums.Count > 0) { //Locate the latest etrans entries for the claim based on DateTimeTrans with EType of ClaimSent or Claim_Ren and update the AckCode and AckEtransNum. //We overwrite existing acks from 997s, 999s, and 277s. command="UPDATE etrans SET AckCode='A', " +"AckEtransNum="+POut.Long(etrans.EtransNum) +" WHERE EType IN (0,3) "//ClaimSent and Claim_Ren +" AND ClaimNum IN("+String.Join(",",listClaimNums.Select(x => POut.Long(x)))+")" +" AND ClearinghouseNum="+POut.Long(hqClearinghouseNum) +" AND DateTimeTrans > "+POut.DateT(dateTimeTrans.AddDays(-14)) +" AND DateTimeTrans < "+POut.DateT(dateTimeTrans.AddDays(1)); Db.NonQ(command); } //none of the other fields make sense, because this ack could refer to many claims. } } else {//unknown type of X12 report. etrans.Etype=EtransType.TextReport; Etranss.Insert(etrans); } } else {//not X12 etrans.Etype=EtransType.TextReport; Etranss.Insert(etrans); } }
///<summary>Throws exceptions. ///When etrans.Etype is associated to a Canadian request EType, this runs multiple queries to set etrans.CarrierTransCounter and ///etrans.CarrierTransCounter2. Otherwise returns without making any changes. ///The etrans.CarrierNum, etrans.CarrierNum2 and etrans.Etype columns must be set prior to running this.</summary> public static Etrans SetCanadianEtransFields(Etrans etrans,bool hasSecondary=true) { if(!etrans.Etype.GetAttributeOrDefault<EtransTypeAttr>().IsCanadaType || !etrans.Etype.GetAttributeOrDefault<EtransTypeAttr>().IsRequestType) { return etrans; } if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) { return Meth.GetObject<Etrans>(MethodBase.GetCurrentMethod(),etrans,hasSecondary); } etrans.OfficeSequenceNumber=0; //find the next officeSequenceNumber string command="SELECT MAX(OfficeSequenceNumber) FROM etrans"; DataTable table=Db.GetTable(command); if(table.Rows.Count>0) { etrans.OfficeSequenceNumber=PIn.Int(table.Rows[0][0].ToString()); if(etrans.OfficeSequenceNumber==999999){//if the office has sent > 1 million messages, and has looped back around to 1. throw new ApplicationException("OfficeSequenceNumber has maxed out at 999999. This program will need to be enhanced."); } } etrans.OfficeSequenceNumber++; //find the next CarrierTransCounter for the primary carrier #region CarrierTransCounter etrans.CarrierTransCounter=0; command="SELECT MAX(CarrierTransCounter) FROM etrans " +"WHERE CarrierNum="+POut.Long(etrans.CarrierNum); table=Db.GetTable(command); int tempcounter=0; if(table.Rows.Count>0) { tempcounter=PIn.Int(table.Rows[0][0].ToString()); } if(tempcounter>etrans.CarrierTransCounter) { etrans.CarrierTransCounter=tempcounter; } command="SELECT MAX(CarrierTransCounter2) FROM etrans " +"WHERE CarrierNum2="+POut.Long(etrans.CarrierNum); table=Db.GetTable(command); if(table.Rows.Count>0) { tempcounter=PIn.Int(table.Rows[0][0].ToString()); } if(tempcounter>etrans.CarrierTransCounter) { etrans.CarrierTransCounter=tempcounter; } if(etrans.CarrierTransCounter==99999){ throw new ApplicationException("CarrierTransCounter has maxed out at 99999. This program will need to be enhanced."); //maybe by adding a reset date to the preference table which will apply to all counters as a whole. } etrans.CarrierTransCounter++; #endregion CarrierTransCounter if(!hasSecondary || etrans.CarrierNum2==0) { return etrans; } #region CarrierTransCounter2 etrans.CarrierTransCounter2=1; command="SELECT MAX(CarrierTransCounter) FROM etrans " +"WHERE CarrierNum="+POut.Long(etrans.CarrierNum2); table=Db.GetTable(command); if(table.Rows.Count>0) { tempcounter=PIn.Int(table.Rows[0][0].ToString()); } if(tempcounter>etrans.CarrierTransCounter2) { etrans.CarrierTransCounter2=tempcounter; } command="SELECT MAX(CarrierTransCounter2) FROM etrans " +"WHERE CarrierNum2="+POut.Long(etrans.CarrierNum2); table=Db.GetTable(command); if(table.Rows.Count>0) { tempcounter=PIn.Int(table.Rows[0][0].ToString()); } if(tempcounter>etrans.CarrierTransCounter2) { etrans.CarrierTransCounter2=tempcounter; } if(etrans.CarrierTransCounter2==99999) { throw new ApplicationException("CarrierTransCounter has maxed out at 99999. This program will need to be enhanced."); } etrans.CarrierTransCounter2++; #endregion return etrans; }
///<summary>Computes aging for the family specified. Specify guarantor=0 in order to calculate aging for all families. ///Gets all info from database. ///The aging calculation will use the following rules within each family: ///1) The aging "buckets" (0 to 30, 31 to 60, 61 to 90 and Over 90) ONLY include account activity on or ///before AsOfDate. ///2) BalTotal will always include all account activity, even future entries, except when in historical ///mode, where BalTotal will exclude account activity after AsOfDate. ///3) InsEst will always include all insurance estimates, even future estimates, except when in ///historical mode where InsEst excludes insurance estimates after AsOfDate. ///4) PayPlanDue will always include all payment plan charges minus credits, except when in ///historical mode where PayPlanDue excludes payment plan charges and payments after AsOfDate.</summary> public static void ComputeAging(long guarantor, DateTime AsOfDate, bool historic) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { Meth.GetVoid(MethodBase.GetCurrentMethod(), guarantor, AsOfDate, historic); return; } //Zero out either entire database or entire family. //Need to zero everything out first to catch former guarantors. string command = "UPDATE patient SET " + "Bal_0_30 = 0" + ",Bal_31_60 = 0" + ",Bal_61_90 = 0" + ",BalOver90 = 0" + ",InsEst = 0" + ",BalTotal = 0" + ",PayPlanDue= 0"; if (guarantor != 0) { command += " WHERE Guarantor=" + POut.Long(guarantor); } Db.NonQ(command); if (AsOfDate.Year < 1880) { AsOfDate = DateTime.Today; } string asOfDate = POut.Date(AsOfDate); string billInAdvanceDate = POut.Date(AsOfDate.AddDays(PrefC.GetLong(PrefName.PayPlansBillInAdvanceDays))); if (historic) { billInAdvanceDate = POut.Date(DateTime.Today.AddDays(PrefC.GetLong(PrefName.PayPlansBillInAdvanceDays))); } string thirtyDaysAgo = POut.Date(AsOfDate.AddDays(-30)); string sixtyDaysAgo = POut.Date(AsOfDate.AddDays(-60)); string ninetyDaysAgo = POut.Date(AsOfDate.AddDays(-90)); string familyPatNums = ""; Collection <string> familyPatNumList = new Collection <string> (); if (guarantor != 0) { familyPatNums = "("; command = "SELECT p.PatNum FROM patient p WHERE p.Guarantor=" + guarantor; DataTable tFamilyPatNums = Db.GetTable(command); for (int i = 0; i < tFamilyPatNums.Rows.Count; i++) { if (i > 0) { familyPatNums += ","; } string patNum = tFamilyPatNums.Rows[i][0].ToString(); familyPatNums += patNum; familyPatNumList.Add(patNum); } familyPatNums += ")"; } //We use temporary tables using the "CREATE TEMPORARY TABLE" syntax here so that any temporary //tables created are specific to the current database connection and no actual files are created //in the database. This will prevent rogue files from collecting in the live database, and will //prevent aging calculations on one computer from affecting the aging calculations on another computer. //Unfortunately, this has one side effect, which is that our connector reopens the //connection every time a command is run, so the temporary tables only last for a single //command. To get around this issue, we run the aging script as a single command/script. //Unfortunately, the "CREATE TEMPORARY TABLE" syntax gets replicated if MySQL replication is enabled, //which becomes a problem becauase the command is then no longer connection specific. Therefore, //to accomodate to the few offices using database replication with MySQL, when creating the temporary aging tables, //we append a random string to the temporary table names so the possibility to temporary table //name collision is practically zero. //Create a temporary table to calculate aging into temporarily, so that the patient table is //not being changed by multiple threads if more than one user is calculating aging. //Since a temporary table is dropped automatically only when the connection is closed, //and since we use connection pooling, drop them before using. string tempTableSuffix = CodeBase.MiscUtils.CreateRandomAlphaNumericString(14);//max size for a table name in oracle is 30 chars. string tempAgingTableName = "tempaging" + tempTableSuffix; string tempOdAgingTransTableName = "tempodagingtrans" + tempTableSuffix; if (DataConnection.DBtype == DatabaseType.Oracle) { try { //We would use DROP TEMPORARY TABLE IF EXISTS syntax here but no such syntax exists in Oracle. command = "DROP TEMPORARY TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); } catch { //The tables do not exist. Nothing to do. } try { //We would use DROP TABLE IF EXISTS syntax here but no such syntax exists in Oracle. command = "DROP TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); } catch { //The tables do not exist. Nothing to do. } } else { command = "DROP TEMPORARY TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); command = "DROP TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); } if (DataConnection.DBtype == DatabaseType.Oracle) { command = "CREATE GLOBAL TEMPORARY TABLE " + tempAgingTableName + " (" + "PatNum NUMBER," + "Guarantor NUMBER," + "Charges_0_30 NUMBER(38,8) DEFAULT 0," + "Charges_31_60 NUMBER(38,8) DEFAULT 0," + "Charges_61_90 NUMBER(38,8) DEFAULT 0," + "ChargesOver90 NUMBER(38,8) DEFAULT 0," + "TotalCredits NUMBER(38,8) DEFAULT 0," + "InsEst NUMBER(38,8) DEFAULT 0," + "PayPlanDue NUMBER(38,8) DEFAULT 0," + "BalTotal NUMBER(38,8) DEFAULT 0" + ");"; } else { command = "CREATE TEMPORARY TABLE " + tempAgingTableName + " (" + "PatNum bigint," + "Guarantor bigint," + "Charges_0_30 DOUBLE DEFAULT 0," + "Charges_31_60 DOUBLE DEFAULT 0," + "Charges_61_90 DOUBLE DEFAULT 0," + "ChargesOver90 DOUBLE DEFAULT 0," + "TotalCredits DOUBLE DEFAULT 0," + "InsEst DOUBLE DEFAULT 0," + "PayPlanDue DOUBLE DEFAULT 0," + "BalTotal DOUBLE DEFAULT 0" + ");"; } if (guarantor == 0) { //We insert all of the patient numbers and guarantor numbers only when we are running aging for everyone, //since we do not want to examine every patient record when running aging for a single family. command += "INSERT INTO " + tempAgingTableName + " (PatNum,Guarantor) " + "SELECT p.PatNum,p.Guarantor " + "FROM patient p;"; //When there is only one patient that aging is being calculated for, then the indexes actually //slow the calculation down slightly, but they significantly improve the speed when aging is being //calculated for all familes. if (DataConnection.DBtype == DatabaseType.Oracle) { command += "CREATE INDEX " + tempAgingTableName.ToUpper() + "_PATNUM ON " + tempAgingTableName + " (PatNum);"; command += "CREATE INDEX " + tempAgingTableName.ToUpper() + "_GUAR ON " + tempAgingTableName + " (Guarantor);"; } else { command += "ALTER TABLE " + tempAgingTableName + " ADD INDEX IDX_" + tempAgingTableName.ToUpper() + "_PATNUM (PatNum);"; command += "ALTER TABLE " + tempAgingTableName + " ADD INDEX IDX_" + tempAgingTableName.ToUpper() + "_GUARANTOR (Guarantor);"; } } else { //Manually create insert statements to avoid having the database system visit every patient record again. //In my testing, this saves about 0.25 seconds on an individual family aging calculation on my machine in MySQL. command += "INSERT INTO " + tempAgingTableName + " (PatNum,Guarantor) VALUES "; for (int i = 0; i < familyPatNumList.Count; i++) { if (i > 0) { command += ","; } command += "(" + familyPatNumList[i] + "," + guarantor + ")"; } command += ";"; } //Create another temporary table which holds a very concise summary of the entire office transaction history, //so that all transactions can be treated as either a general credit or a general charge in the aging calculation. //Since we are recreating a temporary table with the same name as last time aging was run, //the old temporary table gets wiped out. if (DataConnection.DBtype == DatabaseType.Oracle) { command += "CREATE GLOBAL TEMPORARY TABLE " + tempOdAgingTransTableName + " (" + "PatNum NUMBER," + "TranDate DATE DEFAULT TO_DATE('0001-01-01', 'yyyy-mm-dd')," + "TranAmount NUMBER(38,8) DEFAULT 0" + ");"; } else { command += "CREATE TEMPORARY TABLE " + tempOdAgingTransTableName + " (" + "PatNum bigint," + "TranDate DATE DEFAULT '0001-01-01'," + "TranAmount DOUBLE DEFAULT 0" + ");"; } //Get the completed procedure dates and charges for the entire office history. command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " + "SELECT pl.PatNum PatNum," + "pl.ProcDate TranDate," + "pl.ProcFee*(pl.UnitQty+pl.BaseUnits) TranAmount " + "FROM procedurelog pl " + "WHERE pl.ProcStatus=2 " + (guarantor == 0?"":(" AND pl.PatNum IN " + familyPatNums)) + ";"; //Paysplits for the entire office history. command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " + "SELECT ps.PatNum PatNum," + "ps.DatePay TranDate," + "-ps.SplitAmt TranAmount " + "FROM paysplit ps " + "WHERE ps.PayPlanNum=0 " + //Only splits not attached to payment plans. (guarantor == 0?"":(" AND ps.PatNum IN " + familyPatNums)) + ";"; //Get the adjustment dates and amounts for the entire office history. command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " + "SELECT a.PatNum PatNum," + "a.AdjDate TranDate," + "a.AdjAmt TranAmount " + "FROM adjustment a " + "WHERE a.AdjAmt<>0 " + (guarantor == 0?"":(" AND a.PatNum IN " + familyPatNums)) + ";"; //Claim payments and capitation writeoffs for the entire office history. command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " + "SELECT cp.PatNum PatNum," + "cp.DateCp TranDate," + //Always use DateCP rather than ProcDate to calculate the date of a claim payment. "-cp.InsPayAmt-cp.Writeoff TranAmount " + "FROM claimproc cp " + "WHERE cp.status IN (1,4,5,7) " + //received, supplemental, CapClaim or CapComplete. (guarantor == 0?"":(" AND cp.PatNum IN " + familyPatNums)) + ";"; //Payment plan principal for the entire office history. command += "INSERT INTO " + tempOdAgingTransTableName + " (PatNum,TranDate,TranAmount) " + "SELECT pp.PatNum PatNum," + "pp.PayPlanDate TranDate," + "-pp.CompletedAmt TranAmount " + "FROM payplan pp " + "WHERE pp.CompletedAmt<>0 " + (guarantor == 0?"":(" AND pp.PatNum IN " + familyPatNums)) + ";"; if (DataConnection.DBtype == DatabaseType.Oracle) { //The aging calculation buckets, insurance estimates, and payment plan due amounts are //not yet calculated for Oracle as they have not been needed yet. Just calculates //account balance totals. string tempTotalsTableName = "temptotals" + tempTableSuffix; command += "CREATE GLOBAL TEMPORARY TABLE " + tempTotalsTableName + " (" + "PatNum NUMBER DEFAULT 0," + "BalTotal NUMBER(38,8) DEFAULT 0" + ");"; command += "CREATE INDEX " + tempTotalsTableName.ToUpper() + "_PATNU ON " + tempTotalsTableName + " (PatNum);"; command += "INSERT INTO " + tempTotalsTableName + " " + "SELECT PatNum,ROUND(SUM(TranAmount),2) FROM " + tempOdAgingTransTableName + "GROUP BY PatNum;"; command += "UPDATE patient p " + "SET p.BalTotal=(SELECT t.BalTotal FROM " + tempTotalsTableName + " t WHERE t.PatNum=p.PatNum " + DbHelper.LimitAnd(1) + ");"; Db.NonQ(command); } else { //Now that we have all of the pertinent transaction history, we will calculate all of the charges for //the associated patients. //Calculate over 90 day charges for all specified families. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the total charges for each patient during this time period and //place the results into memory table 'chargesOver90'. "(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " + "WHERE t.TranAmount>0 AND t.TranDate<" + DbHelper.DateColumn(ninetyDaysAgo) + " GROUP BY t.PatNum) chargesOver90 " + //Update the tempaging table with the caculated charges for the time period. "SET a.ChargesOver90=chargesOver90.TotalCharges " + "WHERE a.PatNum=chargesOver90.PatNum;"; //Calculate 61 to 90 day charges for all specified families. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the total charges for each patient during this time period and //place the results into memory table 'charges_61_90'. "(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " + "WHERE t.TranAmount>0 AND t.TranDate<" + DbHelper.DateColumn(sixtyDaysAgo) + " AND " + "t.TranDate>=" + DbHelper.DateColumn(ninetyDaysAgo) + " GROUP BY t.PatNum) charges_61_90 " + //Update the tempaging table with the caculated charges for the time period. "SET a.Charges_61_90=charges_61_90.TotalCharges " + "WHERE a.PatNum=charges_61_90.PatNum;"; //Calculate 31 to 60 day charges for all specified families. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the total charges for each patient during this time period and //place the results into memory table 'charges_31_60'. "(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " + "WHERE t.TranAmount>0 AND t.TranDate<" + DbHelper.DateColumn(thirtyDaysAgo) + " AND " + "t.TranDate>=" + DbHelper.DateColumn(sixtyDaysAgo) + " GROUP BY t.PatNum) charges_31_60 " + //Update the tempaging table with the caculated charges for the time period. "SET a.Charges_31_60=charges_31_60.TotalCharges " + "WHERE a.PatNum=charges_31_60.PatNum;"; //Calculate 0 to 30 day charges for all specified families. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the total charges for each patient during this time period and //place the results into memory table 'charges_0_30'. "(SELECT t.PatNum,SUM(t.TranAmount) TotalCharges FROM " + tempOdAgingTransTableName + " t " + "WHERE t.TranAmount>0 AND t.TranDate<=" + DbHelper.DateColumn(asOfDate) + " AND " + "t.TranDate>=" + DbHelper.DateColumn(thirtyDaysAgo) + " GROUP BY t.PatNum) charges_0_30 " + //Update the tempaging table with the caculated charges for the time period. "SET a.Charges_0_30=charges_0_30.TotalCharges " + "WHERE a.PatNum=charges_0_30.PatNum;"; //Calculate the total credits each patient has ever received so we can apply the credits to the aged charges below. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the total credits for each patient and store the results in memory table 'credits'. "(SELECT t.PatNum,-SUM(t.TranAmount) TotalCredits FROM " + tempOdAgingTransTableName + " t " + "WHERE t.TranAmount<0 AND t.TranDate<=" + DbHelper.DateColumn(asOfDate) + " GROUP BY t.PatNum) credits " + //Update the total credit for each patient into the tempaging table. "SET a.TotalCredits=credits.TotalCredits " + "WHERE a.PatNum=credits.PatNum;"; //Calculate claim estimates for each patient individually on or before the specified date. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the insurance estimates for each patient and store the results into //memory table 't'. "(SELECT cp.PatNum,SUM(cp.InsPayEst+cp.Writeoff) InsEst " + "FROM claimproc cp " + "WHERE cp.PatNum<>0 " + (historic?(" AND ((cp.Status=0 AND cp.ProcDate<=" + DbHelper.DateColumn(asOfDate) + ") OR " + "(cp.Status=1 AND cp.DateCP>" + DbHelper.DateColumn(asOfDate) + ")) AND cp.ProcDate<=" + DbHelper.DateColumn(asOfDate) + " "):" AND cp.Status=0 ") + (guarantor == 0?"":(" AND cp.PatNum IN " + familyPatNums + " ")) + "GROUP BY cp.PatNum) t " + //not received claims. //Update the tempaging table with the insurance estimates for each patient. "SET a.InsEst=t.InsEst " + "WHERE a.PatNum=t.PatNum;"; //Calculate the payment plan charges for each payment plan guarantor //on or before the specified date (also considering the PayPlansBillInAdvanceDays setting). //We cannot exclude payments made outside the specified family, since payment plan //guarantors can be in another family. command += "UPDATE " + tempAgingTableName + " a," + "(SELECT ppc.Guarantor,IFNULL(SUM(ppc.Principal+ppc.Interest),0) PayPlanCharges " + "FROM payplancharge ppc " + "WHERE ppc.ChargeDate<=" + DbHelper.DateColumn(billInAdvanceDate) + " " + //bill in adv. date accounts for historic vs current because of how it is set above. "GROUP BY ppc.Guarantor) c " + "SET a.PayPlanDue=c.PayPlanCharges " + "WHERE c.Guarantor=a.PatNum;"; //Calculate the total payments made to each payment plan //on or before the specified date and store the results in memory table 'p'. //We cannot exclude payments made outside the specified family, since payment plan //guarantors can be in another family. command += "UPDATE " + tempAgingTableName + " a," + "(SELECT ps.PatNum,SUM(ps.SplitAmt) PayPlanPayments " + "FROM paysplit ps " + "WHERE ps.PayPlanNum<>0 " + //only payments attached to payment plans. (historic?(" AND ps.DatePay<=" + DbHelper.DateColumn(asOfDate) + " "):"") + "GROUP BY ps.PatNum) p " + "SET a.PayPlanDue=a.PayPlanDue-p.PayPlanPayments " + "WHERE p.PatNum=a.PatNum;"; //Calculate the total balance for each patient. //In historical mode, only transactions on or before AsOfDate will be included. command += "UPDATE " + tempAgingTableName + " a," + //Calculate the total balance for each patient and //place the results into memory table 'totals'. "(SELECT t.PatNum,SUM(t.TranAmount) BalTotal FROM " + tempOdAgingTransTableName + " t " + "WHERE t.TranAmount<>0 " + (historic?(" AND t.TranDate<=" + DbHelper.DateColumn(asOfDate)):"") + " GROUP BY t.PatNum) totals " + //Update the tempaging table with the caculated charges for the time period. "SET a.BalTotal=totals.BalTotal " + "WHERE a.PatNum=totals.PatNum;"; //Update the family aged balances onto the guarantor rows of the patient table //by placing credits on oldest charges first, then on younger charges. command += "UPDATE patient p," + //Sum each colum within each family group inside of the tempaging table so that we are now //using family amounts instead of individual patient amounts, and store the result into //memory table 'f'. "(SELECT a.Guarantor,SUM(a.Charges_0_30) Charges_0_30,SUM(a.Charges_31_60) Charges_31_60," + "SUM(a.Charges_61_90) Charges_61_90,SUM(a.ChargesOver90) ChargesOver90," + "SUM(TotalCredits) TotalCredits,SUM(InsEst) InsEst,SUM(PayPlanDue) PayPlanDue," + "SUM(BalTotal) BalTotal " + "FROM " + tempAgingTableName + " a " + "GROUP BY a.Guarantor) f " + //Perform the update of the patient table based on the family amounts summed into table 'f', and //distribute the payments into the oldest balances first. "SET " + "p.BalOver90=ROUND((CASE " + //over 90 balance paid in full. "WHEN f.TotalCredits>=f.ChargesOver90 THEN 0 " + //over 90 balance partially paid or unpaid. "ELSE f.ChargesOver90-f.TotalCredits END),2)," + "p.Bal_61_90=ROUND((CASE " + //61 to 90 day balance unpaid. "WHEN f.TotalCredits<=f.ChargesOver90 THEN f.Charges_61_90 " + //61 to 90 day balance paid in full. "WHEN f.ChargesOver90+f.Charges_61_90<=f.TotalCredits THEN 0 " + //61 to 90 day balance partially paid. "ELSE f.ChargesOver90+f.Charges_61_90-f.TotalCredits END),2)," + "p.Bal_31_60=ROUND((CASE " + //31 to 60 day balance unpaid. "WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90 THEN f.Charges_31_60 " + //31 to 60 day balance paid in full. "WHEN f.ChargesOver90+f.Charges_61_90+f.Charges_31_60<=f.TotalCredits THEN 0 " + //31 to 60 day balance partially paid. "ELSE f.ChargesOver90+f.Charges_61_90+f.Charges_31_60-f.TotalCredits END),2)," + "p.Bal_0_30=ROUND((CASE " + //0 to 30 day balance unpaid. "WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90+f.Charges_31_60 THEN f.Charges_0_30 " + //0 to 30 day balance paid in full. "WHEN f.ChargesOver90+f.Charges_61_90+f.Charges_31_60+f.Charges_0_30<=f.TotalCredits THEN 0 " + //0 to 30 day balance partially paid. "ELSE f.ChargesOver90+f.Charges_61_90+f.Charges_31_60+f.Charges_0_30-f.TotalCredits END),2)," + "p.BalTotal=ROUND(f.BalTotal,2)," + "p.InsEst=ROUND(f.InsEst,2)," + "p.PayPlanDue=ROUND(f.PayPlanDue,2) " + "WHERE p.PatNum=f.Guarantor;"; //Aging calculations only apply to guarantors. Db.NonQ(command); } if (DataConnection.DBtype == DatabaseType.Oracle) { try { //We would use DROP TEMPORARY TABLE IF EXISTS syntax here but no such syntax exists in Oracle. command = "DROP TEMPORARY TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); } catch { //The tables do not exist. Nothing to do. } try { //We would use DROP TABLE IF EXISTS syntax here but no such syntax exists in Oracle. command = "DROP TABLE " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); } catch { //The tables do not exist. Nothing to do. } } else { command = "DROP TEMPORARY TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); command = "DROP TABLE IF EXISTS " + tempAgingTableName + ", " + tempOdAgingTransTableName; Db.NonQ(command); } }
///<summary>For orderBy, use 0 for BillingType and 1 for PatientName.</summary> public static DataTable GetBilling(bool isSent, int orderBy, DateTime dateFrom, DateTime dateTo, long clinicNum) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), isSent, orderBy, dateFrom, dateTo, clinicNum)); } DataTable table = new DataTable(); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("amountDue"); table.Columns.Add("balTotal"); table.Columns.Add("billingType"); table.Columns.Add("insEst"); table.Columns.Add("IsSent"); table.Columns.Add("lastStatement"); table.Columns.Add("mode"); table.Columns.Add("name"); table.Columns.Add("PatNum"); table.Columns.Add("payPlanDue"); table.Columns.Add("StatementNum"); List <DataRow> rows = new List <DataRow>(); string command = "SELECT BalTotal,BillingType,FName,InsEst,statement.IsSent," + "IFNULL(MAX(s2.DateSent)," + POut.Date(DateTime.MinValue) + ") LastStatement," + "LName,MiddleI,statement.Mode_,PayPlanDue,Preferred," + "statement.PatNum,statement.StatementNum " + "FROM statement " + "LEFT JOIN patient ON statement.PatNum=patient.PatNum " + "LEFT JOIN statement s2 ON s2.PatNum=patient.PatNum " + "AND s2.IsSent=1 "; if (PrefC.GetBool(PrefName.BillingIgnoreInPerson)) { command += "AND s2.Mode_ !=1 "; } if (orderBy == 0) //BillingType { command += "LEFT JOIN definition ON patient.BillingType=definition.DefNum "; } command += "WHERE statement.IsSent=" + POut.Bool(isSent) + " "; //if(dateFrom.Year>1800){ command += "AND statement.DateSent>=" + POut.Date(dateFrom) + " "; //greater than midnight this morning //} //if(dateFrom.Year>1800){ command += "AND statement.DateSent<" + POut.Date(dateTo.AddDays(1)) + " "; //less than midnight tonight //} if (clinicNum > 0) { command += "AND patient.ClinicNum=" + clinicNum + " "; } command += "GROUP BY BalTotal,BillingType,FName,InsEst,statement.IsSent," + "LName,MiddleI,statement.Mode_,PayPlanDue,Preferred," + "statement.PatNum,statement.StatementNum "; if (orderBy == 0) //BillingType { command += "ORDER BY definition.ItemOrder,LName,FName,MiddleI,PayPlanDue"; } else { command += "ORDER BY LName,FName"; } DataTable rawTable = Db.GetTable(command); Patient pat; StatementMode mode; double balTotal; double insEst; double payPlanDue; DateTime lastStatement; for (int i = 0; i < rawTable.Rows.Count; i++) { row = table.NewRow(); balTotal = PIn.Double(rawTable.Rows[i]["BalTotal"].ToString()); insEst = PIn.Double(rawTable.Rows[i]["InsEst"].ToString()); payPlanDue = PIn.Double(rawTable.Rows[i]["PayPlanDue"].ToString()); row["amountDue"] = (balTotal - insEst).ToString("F"); row["balTotal"] = balTotal.ToString("F");; row["billingType"] = DefC.GetName(DefCat.BillingTypes, PIn.Long(rawTable.Rows[i]["BillingType"].ToString())); if (insEst == 0) { row["insEst"] = ""; } else { row["insEst"] = insEst.ToString("F"); } row["IsSent"] = rawTable.Rows[i]["IsSent"].ToString(); lastStatement = PIn.Date(rawTable.Rows[i]["LastStatement"].ToString()); if (lastStatement.Year < 1880) { row["lastStatement"] = ""; } else { row["lastStatement"] = lastStatement.ToShortDateString(); } mode = (StatementMode)PIn.Long(rawTable.Rows[i]["Mode_"].ToString()); row["mode"] = Lans.g("enumStatementMode", mode.ToString()); pat = new Patient(); pat.LName = rawTable.Rows[i]["LName"].ToString(); pat.FName = rawTable.Rows[i]["FName"].ToString(); pat.Preferred = rawTable.Rows[i]["Preferred"].ToString(); pat.MiddleI = rawTable.Rows[i]["MiddleI"].ToString(); row["name"] = pat.GetNameLF(); row["PatNum"] = rawTable.Rows[i]["PatNum"].ToString(); if (payPlanDue == 0) { row["payPlanDue"] = ""; } else { row["payPlanDue"] = payPlanDue.ToString("F"); } row["StatementNum"] = rawTable.Rows[i]["StatementNum"].ToString(); rows.Add(row); } for (int i = 0; i < rows.Count; i++) { table.Rows.Add(rows[i]); } return(table); }
public static DataTable GetSmsUsageLocal(List <long> listClinicNums, DateTime dateMonth) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, dateMonth)); } #region Initialize retVal DataTable List <SmsPhone> listSmsPhones = GetForClinics(listClinicNums); DateTime dateStart = dateMonth.Date.AddDays(1 - dateMonth.Day); //remove time portion and day of month portion. Remainder should be midnight of the first of the month DateTime dateEnd = dateStart.AddMonths(1); //This should be midnight of the first of the following month. //This query builds the data table that will be filled from several other queries, instead of writing one large complex query. //It is written this way so that the queries are simple to write and understand, and makes Oracle compatibility easier to maintain. string command = @"SELECT 0 ClinicNum, ' ' PhoneNumber, ' ' CountryCode, 0 SentMonth, 0.0 SentCharge, 0 ReceivedMonth, 0.0 ReceivedCharge FROM DUAL" ; //this is a simple way to get a data table with the correct layout without having to query any real data. DataTable retVal = Db.GetTable(command).Clone(); //use .Clone() to get schema only, with no rows. retVal.TableName = "SmsUsageLocal"; for (int i = 0; i < listClinicNums.Count; i++) { DataRow row = retVal.NewRow(); row["ClinicNum"] = listClinicNums[i]; row["PhoneNumber"] = "No Active Phones"; SmsPhone firstActivePhone = listSmsPhones .Where(x => x.ClinicNum == listClinicNums[i]) //phones for this clinic .Where(x => x.DateTimeInactive.Year < 1880) //that are active .FirstOrDefault(x => x.DateTimeActive == listSmsPhones //and have the smallest active date (the oldest/first phones activated) .Where(y => y.ClinicNum == x.ClinicNum) .Where(y => y.DateTimeInactive.Year < 1880) .Min(y => y.DateTimeActive)); if (firstActivePhone != null) { row["PhoneNumber"] = firstActivePhone.PhoneNumber; row["CountryCode"] = firstActivePhone.CountryCode; } row["SentMonth"] = 0; row["SentCharge"] = 0.0; row["ReceivedMonth"] = 0; row["ReceivedCharge"] = 0.0; retVal.Rows.Add(row); } #endregion #region Fill retVal DataTable //Sent Last Month command = "SELECT ClinicNum, COUNT(*), ROUND(SUM(MsgChargeUSD),2) FROM smstomobile " + "WHERE DateTimeSent >=" + POut.Date(dateStart) + " " + "AND DateTimeSent<" + POut.Date(dateEnd) + " " + "AND MsgChargeUSD>0 GROUP BY ClinicNum"; DataTable table = Db.GetTable(command); for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < retVal.Rows.Count; j++) { if (retVal.Rows[j]["ClinicNum"].ToString() != table.Rows[i]["ClinicNum"].ToString()) { continue; } retVal.Rows[j]["SentMonth"] = table.Rows[i][1]; //.ToString(); retVal.Rows[j]["SentCharge"] = table.Rows[i][2]; //.ToString(); break; } } //Received Month command = "SELECT ClinicNum, COUNT(*) FROM smsfrommobile " + "WHERE DateTimeReceived >=" + POut.Date(dateStart) + " " + "AND DateTimeReceived<" + POut.Date(dateEnd) + " " + "GROUP BY ClinicNum"; table = Db.GetTable(command); for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < retVal.Rows.Count; j++) { if (retVal.Rows[j]["ClinicNum"].ToString() != table.Rows[i]["ClinicNum"].ToString()) { continue; } retVal.Rows[j]["ReceivedMonth"] = table.Rows[i][1].ToString(); retVal.Rows[j]["ReceivedCharge"] = "0"; break; } } #endregion return(retVal); }
///<summary><para>Surround with try catch. Safely renames list by creating new list, selecting existing list into new list, then deleting existing list.</para> ///<para>This code could be used to either copy or backup lists in the future. (With minor modifications).</para></summary> public static void Rename(string nameOriginal, string nameNew) { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { Meth.GetVoid(MethodBase.GetCurrentMethod(), nameOriginal, nameNew); return; } //Name should already have been validated and available. string command = "CREATE TABLE wikilist_" + POut.String(nameNew) + " AS SELECT * FROM wikilist_" + POut.String(nameOriginal); Db.NonQ(command); //Validate content before altering and deleting things DataTable tableNew = GetByName(nameNew); DataTable tableOld = GetByName(nameOriginal); if (tableNew.Rows.Count != tableOld.Rows.Count) { command = "DROP TABLE wikilist_" + POut.String(nameNew); Db.NonQ(command); throw new Exception("Error occurred renaming list. Mismatch found in row count. No changes made."); } if (tableNew.Columns.Count != tableOld.Columns.Count) { command = "DROP TABLE wikilist_" + POut.String(nameNew); Db.NonQ(command); throw new Exception("Error occurred renaming list. Mismatch found in column count. No changes made."); } for (int r1 = 0; r1 < tableNew.Rows.Count; r1++) { for (int r2 = 0; r2 < tableOld.Rows.Count; r2++) { if (tableNew.Rows[r1][0] != tableOld.Rows[r2][0]) { continue; //pk does not match } for (int c = 0; c < tableNew.Columns.Count; c++) //both lists have same number of columns { if (tableNew.Rows[r1][c] == tableOld.Rows[r2][c]) { continue; //contents match } throw new Exception("Error occurred renaming list. Mismatch Error found in row data. No changes made."); } //end columns } //end tableOld } //end tableNew //Alter table names---------------------------------------------------------------------------- string priKeyColNameOrig = POut.String(nameOriginal) + "Num"; if (!tableNew.Columns.Contains(priKeyColNameOrig)) //if new table doesn't contain a PK based on the old table name, make the first column the nameNew+"Num" PK column { priKeyColNameOrig = POut.String(tableNew.Columns[0].ColumnName); } if (DataConnection.DBtype == DatabaseType.MySql) { command = "ALTER TABLE wikilist_" + POut.String(nameNew) + " CHANGE " + priKeyColNameOrig + " " + POut.String(nameNew) + "Num bigint NOT NULL auto_increment PRIMARY KEY"; } else { command = "RENAME COLUMN wikilist_" + POut.String(nameNew) + "." + priKeyColNameOrig + " TO " + POut.String(nameNew) + "Num"; } Db.NonQ(command); command = "UPDATE wikilistheaderwidth SET ListName='" + POut.String(nameNew) + "' WHERE ListName='" + POut.String(nameOriginal) + "'"; Db.NonQ(command); command = $@"UPDATE wikilistheaderwidth SET ColName='{POut.String(nameNew)}Num' WHERE ListName='{POut.String(nameNew)}' AND ColName='{priKeyColNameOrig}'" ; Db.NonQ(command); //drop old table--------------------- command = "DROP TABLE wikilist_" + POut.String(nameOriginal); Db.NonQ(command); WikiListHeaderWidths.RefreshCache(); }