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