///<summary>Gets the list of fees by feeschednums and clinicnums from the db. Returns an empty list if listFeeSchedNums is null or empty.
///Throws an application exception if listClinicNums is null or empty. Always provide at least one ClinicNum.
///We throw instead of returning an empty list which would make it look like there are no fees for the fee schedules passed in.
///If this method returns an empty list it is because no valied fee schedules were given or the database truly doesn't have any fees.</summary>
public static List <FeeLim> GetByFeeSchedNumsClinicNums(List <long> listFeeSchedNums, List <long> listClinicNums)
{
if (listFeeSchedNums == null || listFeeSchedNums.Count == 0)
{
return(new List <FeeLim>()); //This won't hurt the FeeCache because there will be no corresponding fee schedules to "blank out".
}
if (listClinicNums == null || listClinicNums.Count == 0)
{
//Returning an empty list here would be detrimental to the FeeCache.
throw new ApplicationException("Invalid listClinicNums passed into GetByFeeSchedNumsClinicNums()");
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
//Unusual Middle Tier check. This method can cause out of memory exceptions when called over Middle Tier, so we are batching into multiple
//calls of 100 fee schedules at a time.
List <FeeLim> listFeeLims = new List <FeeLim>();
for (int i = 0; i < listFeeSchedNums.Count; i += 100)
{
List <long> listFeeSchedsNumsThisBatch = listFeeSchedNums.GetRange(i, Math.Min(100, listFeeSchedNums.Count - i));
listFeeLims.AddRange(Meth.GetObject <List <FeeLim> >(MethodBase.GetCurrentMethod(), listFeeSchedsNumsThisBatch, listClinicNums));
}
return(listFeeLims);
}
string command = "SELECT FeeNum,Amount,FeeSched,CodeNum,ClinicNum,ProvNum,SecDateTEdit FROM fee "
+ "WHERE FeeSched IN (" + string.Join(",", listFeeSchedNums.Select(x => POut.Long(x))) + ") "
+ "AND ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ")";
return(Db.GetTable(command).AsEnumerable()
.Select(x => new FeeLim {
FeeNum = PIn.Long(x["FeeNum"].ToString()),
Amount = PIn.Double(x["Amount"].ToString()),
FeeSched = PIn.Long(x["FeeSched"].ToString()),
CodeNum = PIn.Long(x["CodeNum"].ToString()),
ClinicNum = PIn.Long(x["ClinicNum"].ToString()),
ProvNum = PIn.Long(x["ProvNum"].ToString()),
SecDateTEdit = PIn.DateT(x["SecDateTEdit"].ToString()),
}).ToList());
}
public List <T> ToList <T>()
{
Type tp = typeof(T);
//List<object> list=new List<object>();
List <T> list = new List <T>();
FieldInfo[] fieldInfo = tp.GetFields();
Object obj = default(T);
for (int i = 0; i < Rows.Count; i++)
{
ConstructorInfo constructor = tp.GetConstructor(System.Type.EmptyTypes);
obj = constructor.Invoke(null);
for (int f = 0; f < fieldInfo.Length; f++)
{
if (fieldInfo[f].FieldType == typeof(int))
{
fieldInfo[f].SetValue(obj, PIn.Long(Rows[i][f]));
}
else if (fieldInfo[f].FieldType == typeof(bool))
{
fieldInfo[f].SetValue(obj, PIn.Bool(Rows[i][f]));
}
else if (fieldInfo[f].FieldType == typeof(string))
{
fieldInfo[f].SetValue(obj, PIn.String(Rows[i][f]));
}
else if (fieldInfo[f].FieldType.IsEnum)
{
object val = ((object[])Enum.GetValues(fieldInfo[f].FieldType))[PIn.Long(Rows[i][f])];
fieldInfo[f].SetValue(obj, val);
}
}
list.Add((T)obj);
//Collection
}
return(list); //(List<T>)list.Cast<T>();
}
///<summary>Gets a list of all dunnings.</summary>
public static Dunning[] Refresh()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <Dunning[]>(MethodBase.GetCurrentMethod()));
}
string command = "SELECT * FROM dunning "
+ "ORDER BY BillingType,AgeAccount,InsIsPending";
DataTable table = Db.GetTable(command);
Dunning[] List = new Dunning[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
List[i] = new Dunning();
List[i].DunningNum = PIn.Long(table.Rows[i][0].ToString());
List[i].DunMessage = PIn.String(table.Rows[i][1].ToString());
List[i].BillingType = PIn.Long(table.Rows[i][2].ToString());
List[i].AgeAccount = PIn.Byte(table.Rows[i][3].ToString());
List[i].InsIsPending = (YN)PIn.Long(table.Rows[i][4].ToString());
List[i].MessageBold = PIn.String(table.Rows[i][5].ToString());
}
return(List);
}
///<summary>Returns only 5 columns for all saved treatment plans.</summary>
public static List <TreatPlan> GetAllSavedLim()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TreatPlan> >(MethodBase.GetCurrentMethod()));
}
string command = "SELECT TreatPlanNum, PatNum, DateTP, SecUserNumEntry, UserNumPresenter "
+ " FROM treatplan WHERE treatplan.TPStatus=" + POut.Int((int)TreatPlanStatus.Saved);
DataTable table = Db.GetTable(command);
List <TreatPlan> listSavedTreatPlanLim = new List <TreatPlan>();
foreach (DataRow row in table.Rows)
{
TreatPlan treatPlanCur = new TreatPlan();
treatPlanCur.TreatPlanNum = PIn.Long(row["TreatPlanNum"].ToString());
treatPlanCur.PatNum = PIn.Long(row["PatNum"].ToString());
treatPlanCur.DateTP = PIn.Date(row["DateTP"].ToString());
treatPlanCur.SecUserNumEntry = PIn.Long(row["SecUserNumEntry"].ToString());
treatPlanCur.UserNumPresenter = PIn.Long(row["UserNumPresenter"].ToString());
listSavedTreatPlanLim.Add(treatPlanCur);
}
return(listSavedTreatPlanLim);
}
///<summary>Attempts to fill the list of engineers from the wikilist. Fills with empty if something failed</summary>
private static void FillEngineerList()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod());
return;
}
_listEngineers = new List <Engineer>();
try {
string command = "SELECT Title,EmployeeNum FROM wikilist_employees WHERE Title LIKE '%Engineer%'";
DataTable dt = Db.GetTable(command);
foreach (DataRow dr in dt.Rows)
{
Employee emp = Employees.GetEmp(PIn.Long(dr["EmployeeNum"].ToString()));
Userod user = Userods.GetUserByEmployeeNum(emp.EmployeeNum);
Engineer newEngineer = new Engineer(user, emp, PIn.String(dr["Title"].ToString()));
_listEngineers.Add(newEngineer);
}
}
catch (Exception e) {
//Do nothing
}
}
///<summary></summary>
public static List <LabTurnaround> GetForLab(long laboratoryNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <LabTurnaround> >(MethodBase.GetCurrentMethod(), laboratoryNum));
}
string command = "SELECT * FROM labturnaround WHERE LaboratoryNum=" + POut.Long(laboratoryNum);
DataTable table = Db.GetTable(command);
List <LabTurnaround> retVal = new List <LabTurnaround>();
LabTurnaround lab;
for (int i = 0; i < table.Rows.Count; i++)
{
lab = new LabTurnaround();
lab.LabTurnaroundNum = PIn.Long(table.Rows[i][0].ToString());
lab.LaboratoryNum = PIn.Long(table.Rows[i][1].ToString());
lab.Description = PIn.String(table.Rows[i][2].ToString());
lab.DaysPublished = PIn.Int(table.Rows[i][3].ToString());
lab.DaysActual = PIn.Int(table.Rows[i][4].ToString());
retVal.Add(lab);
}
return(retVal);
}
///<summary></summary>
private static List <ClaimPaySplit> ClaimPaySplitTableToList(DataTable table)
{
//No need to check RemotingRole; no call to db.
List <ClaimPaySplit> splits = new List <ClaimPaySplit>();
ClaimPaySplit split;
for (int i = 0; i < table.Rows.Count; i++)
{
split = new ClaimPaySplit();
split.DateClaim = PIn.Date(table.Rows[i]["DateService"].ToString());
split.ProvAbbr = Providers.GetAbbr(PIn.Long(table.Rows[i]["ProvTreat"].ToString()));
split.PatName = PIn.String(table.Rows[i]["patName_"].ToString());
split.PatNum = PIn.Long(table.Rows[i]["PatNum"].ToString());
split.Carrier = PIn.String(table.Rows[i]["CarrierName"].ToString());
split.FeeBilled = PIn.Double(table.Rows[i]["feeBilled_"].ToString());
split.InsPayAmt = PIn.Double(table.Rows[i]["insPayAmt_"].ToString());
split.ClaimNum = PIn.Long(table.Rows[i]["ClaimNum"].ToString());
split.ClaimPaymentNum = PIn.Long(table.Rows[i]["ClaimPaymentNum"].ToString());
split.PaymentRow = PIn.Int(table.Rows[i]["PaymentRow"].ToString());
splits.Add(split);
}
return(splits);
}
///<summary>Deletes the etrans entry and changes the status of the claim back to W. If it encounters an entry that's not a claim, it skips it for now. Later, it will handle all types of undo. It will also check Canadian claims to prevent alteration if an ack or EOB has been received.</summary>
public static void Undo(long etransNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), etransNum);
return;
}
//see if it's a claim.
string command = "SELECT ClaimNum FROM etrans WHERE EtransNum=" + POut.Long(etransNum);
DataTable table = Db.GetTable(command);
long claimNum = PIn.Long(table.Rows[0][0].ToString());
if (claimNum == 0) //if no claim
{
return; //for now
}
//future Canadian check will go here
//Change the claim back to W.
command = "UPDATE claim SET ClaimStatus='W' WHERE ClaimNum=" + POut.Long(claimNum);
Db.NonQ(command);
if (CultureInfo.CurrentCulture.Name.EndsWith("CA")) //Canadian. en-CA or fr-CA
//We cannot delete etrans entries, because we need to retain the OfficeSequenceNumber in order to prevent reuse.
//We used to allow deleting here, but some customers were getting the "invalid dental claim number or office sequence number" error message when sending claims thereafter.
//Office sequence numbers must be unique for every request and response, whether related to a claim or not.
//Instead of deleting, we simply detach from the claim, so that this historic entry will no longer display within the Manage | Send Claims window.
{
command = "UPDATE etrans SET ClaimNum=0 WHERE EtransNum=" + POut.Long(etransNum);
Db.NonQ(command);
}
else
{
//Delete this etrans
command = "DELETE FROM etrans WHERE EtransNum=" + POut.Long(etransNum);
Db.NonQ(command);
}
}
/*
* ///<summary></summary>
* public static void Update(TaskAncestor ancestor) {
* if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
* Meth.GetVoid(MethodBase.GetCurrentMethod(),ancestor);
* return;
* }
* Crud.TaskAncestorCrud.Update(ancestor);
* }*/
public static void Synch(Task task)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), task);
return;
}
string command = "DELETE FROM taskancestor WHERE TaskNum=" + POut.Long(task.TaskNum);
Db.NonQ(command);
long taskListNum = 0;
long parentNum = task.TaskListNum;
DataTable table;
TaskAncestor ancestor;
while (true)
{
if (parentNum == 0)
{
break; //no parent to mark
}
//get the parent
command = "SELECT TaskListNum,Parent FROM tasklist WHERE TaskListNum=" + POut.Long(parentNum);
table = Db.GetTable(command);
if (table.Rows.Count == 0) //in case of database inconsistency
{
break;
}
taskListNum = PIn.Long(table.Rows[0]["TaskListNum"].ToString());
parentNum = PIn.Long(table.Rows[0]["Parent"].ToString());
ancestor = new TaskAncestor();
ancestor.TaskNum = task.TaskNum;
ancestor.TaskListNum = taskListNum;
Insert(ancestor);
}
}
///<summary>Upserts the InvalidType.SmsTextMsgReceivedUnreadCount signal which tells all client machines to update the received unread SMS
///message count. There should only be max one of this signal IType in the database.</summary>
public static List <SmsFromMobiles.SmsNotification> UpsertSmsNotification()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <SmsFromMobiles.SmsNotification> >(MethodBase.GetCurrentMethod()));
}
string command = "SELECT ClinicNum,COUNT(*) AS CountUnread FROM smsfrommobile WHERE SmsStatus=0 AND IsHidden=0 GROUP BY ClinicNum "
+ "ORDER BY ClinicNum";
List <SmsFromMobiles.SmsNotification> ret = Db.GetTable(command).AsEnumerable()
.Select(x => new SmsFromMobiles.SmsNotification()
{
ClinicNum = PIn.Long(x["ClinicNum"].ToString()),
Count = PIn.Int(x["CountUnread"].ToString()),
}).ToList();
//Insert as structured data signal so all workstations won't have to query the db to get the counts. They will get it directly from Signalod.MsgValue.
string json = SmsFromMobiles.SmsNotification.GetJsonFromList(ret);
//FKeyType SmsMsgUnreadCount is written to db as a string.
command = "SELECT * FROM signalod WHERE IType=" + POut.Int((int)InvalidType.SmsTextMsgReceivedUnreadCount)
+ " AND FKeyType='" + POut.String(KeyType.SmsMsgUnreadCount.ToString()) + "' ORDER BY SigDateTime DESC LIMIT 1";
DataTable table = Db.GetTable(command);
Signalod sig = Crud.SignalodCrud.TableToList(table).FirstOrDefault();
if (sig != null && sig.MsgValue == json) //No changes, not need to insert a new signal.
{
return(ret); //Return the list of notifications, but do not update the existing signal.
}
Signalods.Insert(new Signalod()
{
IType = InvalidType.SmsTextMsgReceivedUnreadCount,
FKeyType = KeyType.SmsMsgUnreadCount,
MsgValue = json,
RemoteRole = RemotingClient.RemotingRole
});
return(ret);
}
///<summary>Returns the ClaimNum for the claim that has a claim identifier beginning with the specified claimIdentifier, but only if there is exactly one claim matched. Otherwise 0 is returned.</summary>
public static long GetClaimNumForIdentifier(string claimIdentifier)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <long>(MethodBase.GetCurrentMethod(), claimIdentifier));
}
//Our claim identifiers can be longer than 20 characters (mostly when using replication). When the claim identifier is sent out on the claim, it is truncated to 20
//characters. Therefore, if the claim identifier is longer than 20 characters, then it was truncated when sent out, so we have to look for claims beginning with the
//claim identifier given if there is not an exact match.
string command = "SELECT ClaimNum FROM claim WHERE ClaimIdentifier='" + POut.String(claimIdentifier) + "'";
DataTable dtClaims = Db.GetTable(command);
if (dtClaims.Rows.Count == 0) //No exact match for the claim identifier. This will happen with replication sometimes.
{
command = "SELECT ClaimNum FROM claim WHERE ClaimIdentifier LIKE CONCAT('" + POut.String(claimIdentifier) + "','%')";
dtClaims = Db.GetTable(command);
//There is a slight chance that we will have more than one match, and in this case we will return 0.
if (dtClaims.Rows.Count != 1)
{
return(0);
}
}
return(PIn.Long(dtClaims.Rows[0][0].ToString()));
}
///<summary>Gets the installment plans for these super families. If there are none for a super family, the super family head will not be
///present in the dictionary.</summary>
///<returns>Dictionary where the key is the super family head and the value is the installment plan for the super family.</returns>
public static SerializableDictionary <long, List <InstallmentPlan> > GetForSuperFams(List <long> listSuperFamNums)
{
if (listSuperFamNums.Count == 0)
{
return(new SerializableDictionary <long, List <InstallmentPlan> >());
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <SerializableDictionary <long, List <InstallmentPlan> > >(MethodBase.GetCurrentMethod(), listSuperFamNums));
}
string command = "SELECT installmentplan.*,patient.SuperFamily FROM installmentplan "
+ "INNER JOIN patient ON installmentplan.PatNum=patient.PatNum "
+ "WHERE patient.SuperFamily IN(" + string.Join(",", listSuperFamNums.Select(x => POut.Long(x))) + ") "
+ "AND patient.HasSuperBilling=1 "
+ "GROUP BY installmentplan.PatNum";
if (DataConnection.DBtype != DatabaseType.MySql)
{
command += ",installmentplan.InstallmentPlanNum,installmentplan.DateAgreement,installmentplan.DateFirstPayment"
+ ",installmentplan.MonthlyPayment,installmentplan.APR,installmentplan.Note,patient.SuperFamily";
}
DataTable table = Db.GetTable(command);
List <InstallmentPlan> listInstallmentPlans = Crud.InstallmentPlanCrud.TableToList(table);
SerializableDictionary <long, List <InstallmentPlan> > dictPlans = new SerializableDictionary <long, List <InstallmentPlan> >();
for (int i = 0; i < table.Rows.Count; i++)
{
long superFamNum = PIn.Long(table.Rows[i]["SuperFamily"].ToString());
if (!dictPlans.ContainsKey(superFamNum))
{
dictPlans.Add(superFamNum, new List <InstallmentPlan>());
}
dictPlans[superFamNum].Add(listInstallmentPlans[i]);
}
return(dictPlans);
}
///<summary>For orderBy, use 0 for BillingType and 1 for PatientName.</summary>
public static DataTable GetBilling(bool isSent, int orderBy, DateTime dateFrom, DateTime dateTo, List <long> clinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), isSent, orderBy, dateFrom, dateTo, clinicNums));
}
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");
table.Columns.Add("SuperFamily");
table.Columns.Add("ClinicNum");
string command = "SELECT guar.BalTotal,patient.BillingType,patient.FName,guar.InsEst,statement.IsSent,"
+ "IFNULL(MAX(s2.DateSent)," + POut.Date(DateTime.MinValue) + ") LastStatement,"
+ "patient.LName,patient.MiddleI,statement.Mode_,guar.PayPlanDue,patient.Preferred,"
+ "statement.PatNum,statement.StatementNum,statement.SuperFamily,patient.ClinicNum "
+ "FROM statement "
+ "LEFT JOIN patient ON statement.PatNum=patient.PatNum "
+ "LEFT JOIN patient guar ON guar.PatNum=patient.Guarantor "
+ "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 (clinicNums.Count > 0)
{
command += "AND patient.ClinicNum IN (" + string.Join(",", clinicNums) + ") ";
}
command += "GROUP BY guar.BalTotal,patient.BillingType,patient.FName,guar.InsEst,statement.IsSent,"
+ "patient.LName,patient.MiddleI,statement.Mode_,guar.PayPlanDue,patient.Preferred,"
+ "statement.PatNum,statement.StatementNum,statement.SuperFamily ";
if (orderBy == 0) //BillingType
{
command += "ORDER BY definition.ItemOrder,patient.LName,patient.FName,patient.MiddleI,guar.PayPlanDue";
}
else
{
command += "ORDER BY patient.LName,patient.FName";
}
DataTable rawTable = Db.GetTable(command);
double balTotal;
double insEst;
double payPlanDue;
DateTime lastStatement;
List <Patient> listFamilyGuarantors;
foreach (DataRow rawRow in rawTable.Rows)
{
row = table.NewRow();
if (rawRow["SuperFamily"].ToString() == "0") //not a super statement, just get bal info from guarantor
{
balTotal = PIn.Double(rawRow["BalTotal"].ToString());
insEst = PIn.Double(rawRow["InsEst"].ToString());
payPlanDue = PIn.Double(rawRow["PayPlanDue"].ToString());
}
else //super statement, add all guar positive balances to get bal total for super family
{
listFamilyGuarantors = Patients.GetSuperFamilyGuarantors(PIn.Long(rawRow["SuperFamily"].ToString())).FindAll(x => x.HasSuperBilling);
//exclude fams with neg balances in the total for super family stmts (per Nathan 5/25/2016)
if (PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
listFamilyGuarantors = listFamilyGuarantors.FindAll(x => x.BalTotal > 0);
insEst = 0;
}
else
{
listFamilyGuarantors = listFamilyGuarantors.FindAll(x => (x.BalTotal - x.InsEst) > 0);
insEst = listFamilyGuarantors.Sum(x => x.InsEst);
}
balTotal = listFamilyGuarantors.Sum(x => x.BalTotal);
payPlanDue = listFamilyGuarantors.Sum(x => x.PayPlanDue);
}
row["amountDue"] = (balTotal - insEst).ToString("F");
row["balTotal"] = balTotal.ToString("F");;
row["billingType"] = Defs.GetName(DefCat.BillingTypes, PIn.Long(rawRow["BillingType"].ToString()));
if (insEst == 0)
{
row["insEst"] = "";
}
else
{
row["insEst"] = insEst.ToString("F");
}
row["IsSent"] = rawRow["IsSent"].ToString();
lastStatement = PIn.Date(rawRow["LastStatement"].ToString());
if (lastStatement.Year < 1880)
{
row["lastStatement"] = "";
}
else
{
row["lastStatement"] = lastStatement.ToShortDateString();
}
row["mode"] = Lans.g("enumStatementMode", ((StatementMode)PIn.Int(rawRow["Mode_"].ToString())).ToString());
row["name"] = Patients.GetNameLF(rawRow["LName"].ToString(), rawRow["FName"].ToString(), rawRow["Preferred"].ToString(), rawRow["MiddleI"].ToString());
row["PatNum"] = rawRow["PatNum"].ToString();
if (payPlanDue == 0)
{
row["payPlanDue"] = "";
}
else
{
row["payPlanDue"] = payPlanDue.ToString("F");
}
row["StatementNum"] = rawRow["StatementNum"].ToString();
row["SuperFamily"] = rawRow["SuperFamily"].ToString();
row["ClinicNum"] = rawRow["ClinicNum"].ToString();
table.Rows.Add(row);
}
return(table);
}
///<summary>Sets the status of the claim to sent, usually as part of printing. Also makes an entry in etrans. If this is Canadian eclaims, then this function gets run first. If the claim is to be sent elecronically, then the messagetext is created after this method and an attempt is made to send the claim. Finally, the messagetext is added to the etrans. This is necessary because the transaction numbers must be incremented and assigned to each claim before creating the message and attempting to send. For Canadians, it will always record the attempt as an etrans even if claim is not set to status of sent.</summary>
public static Etrans SetClaimSentOrPrinted(long claimNum, long patNum, long clearinghouseNum, EtransType etype, int batchNumber)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <Etrans>(MethodBase.GetCurrentMethod(), claimNum, patNum, clearinghouseNum, etype, batchNumber));
}
string command;
Etrans etrans = new Etrans();
//etrans.DateTimeTrans handled automatically
etrans.ClearingHouseNum = clearinghouseNum;
etrans.Etype = etype;
etrans.ClaimNum = claimNum;
etrans.PatNum = patNum;
//Get the primary and secondary carrierNums for this claim.
command = "SELECT carrier1.CarrierNum,carrier2.CarrierNum AS CarrierNum2 FROM claim "
+ "LEFT JOIN insplan insplan1 ON insplan1.PlanNum=claim.PlanNum "
+ "LEFT JOIN carrier carrier1 ON carrier1.CarrierNum=insplan1.CarrierNum "
+ "LEFT JOIN insplan insplan2 ON insplan2.PlanNum=claim.PlanNum2 "
+ "LEFT JOIN carrier carrier2 ON carrier2.CarrierNum=insplan2.CarrierNum "
+ "WHERE claim.ClaimNum=" + POut.Long(claimNum);
DataTable table = Db.GetTable(command);
etrans.CarrierNum = PIn.Long(table.Rows[0][0].ToString());
etrans.CarrierNum2 = PIn.Long(table.Rows[0][1].ToString()); //might be 0 if no secondary on this claim
etrans.BatchNumber = batchNumber;
//if(X837.IsX12(messageText)) {
// X837 x837=new X837(messageText);
// etrans.TransSetNum=x837.GetTransNum(claimNum);
//}
if (etype == EtransType.Claim_CA || etype == EtransType.ClaimCOB_CA || etype == EtransType.Predeterm_CA || etype == EtransType.PredetermEOB_CA)
{
etrans.OfficeSequenceNumber = 0;
//find the next officeSequenceNumber
command = "SELECT MAX(OfficeSequenceNumber) FROM etrans";
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.");
}
}
#if DEBUG
etrans.OfficeSequenceNumber = PIn.Int(File.ReadAllText(@"..\..\..\TestCanada\LastOfficeSequenceNumber.txt"));
File.WriteAllText(@"..\..\..\TestCanada\LastOfficeSequenceNumber.txt", (etrans.OfficeSequenceNumber + 1).ToString());
#endif
etrans.OfficeSequenceNumber++;
//find the next CarrierTransCounter for the primary carrier
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.");
}
etrans.CarrierTransCounter++;
if (etrans.CarrierNum2 > 0) //if there is secondary coverage on this claim
{
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++;
}
}
command = "UPDATE claim SET ClaimStatus = 'S',"
+ "DateSent= " + POut.Date(MiscData.GetNowDateTime())
+ " WHERE claimnum = " + POut.Long(claimNum);
Db.NonQ(command);
EtransMessageText etransMessageText = new EtransMessageText();
etransMessageText.MessageText = "";
EtransMessageTexts.Insert(etransMessageText);
etrans.EtransMessageTextNum = etransMessageText.EtransMessageTextNum;
Etranss.Insert(etrans);
return(GetEtrans(etrans.EtransNum)); //Since the DateTimeTrans is set upon insert, we need to read the record again in order to get the date.
}
///<summary>Gets data for the history grid in the SendClaims window.</summary>
public static DataTable RefreshHistory(DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo));
}
string command = "Select CONCAT(CONCAT(patient.LName,', '),patient.FName) AS PatName,carrier.CarrierName,"
+ "clearinghouse.Description AS Clearinghouse,DateTimeTrans,etrans.OfficeSequenceNumber,"
+ "etrans.CarrierTransCounter,Etype,etrans.ClaimNum,etrans.EtransNum,etrans.AckCode,etrans.Note "
+ "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 "
//if(DataConnection.DBtype==DatabaseType.Oracle){
// command+="TO_";
//}
+ DbHelper.DateColumn("DateTimeTrans") + " >= " + POut.Date(dateFrom) + " AND "
//if(DataConnection.DBtype==DatabaseType.Oracle){
// command+="TO_";
//}
+ DbHelper.DateColumn("DateTimeTrans") + " <= " + POut.Date(dateTo) + " "
+ "AND Etype!=" + POut.Long((int)EtransType.Acknowledge_997) + " "
+ "AND Etype!=" + POut.Long((int)EtransType.Acknowledge_999) + " "
+ "AND Etype!=" + POut.Long((int)EtransType.BenefitInquiry270) + " "
+ "AND Etype!=" + POut.Long((int)EtransType.BenefitResponse271) + " "
+ "AND Etype!=" + POut.Long((int)EtransType.AckError) + " "
//We exclude canadian transaction response types, since the responses can be accessed from the request transaction inside of the claim history portion of FormSendClaims.
+ "AND Etype!=" + POut.Long((int)EtransType.ClaimAck_CA) + " " //Could be attached to a claim, cob claim or ROT.
+ "AND Etype!=" + POut.Long((int)EtransType.ClaimEOB_CA) + " " //Could be attached to a claim, cob claim or ROT.
+ "AND Etype!=" + POut.Long((int)EtransType.EligResponse_CA) + " " //Will always be attached to an Eligibility request.
+ "AND Etype!=" + POut.Long((int)EtransType.EmailResponse_CA) + " " //Will always be attached to a Request for Outstanding Transactions (ROT).
+ "AND Etype!=" + POut.Long((int)EtransType.OutstandingAck_CA) + " " //Will always be attached to an ROT.
+ "AND Etype!=" + POut.Long((int)EtransType.PaymentResponse_CA) + " " //Will always be attached to a Request for Payment Reconciliation (RPR).
+ "AND Etype!=" + POut.Long((int)EtransType.PredetermAck_CA) + " " //Could be attached to a Predetermination request or an ROT.
+ "AND Etype!=" + POut.Long((int)EtransType.PredetermEOB_CA) + " " //Could be attached to a Predetermination request or an ROT.
+ "AND Etype!=" + POut.Long((int)EtransType.ReverseResponse_CA) + " " //Will always be attached to a Reversal request.
+ "AND Etype!=" + POut.Long((int)EtransType.SummaryResponse_CA) + " " //Will always be attached to a Request for Summary Reconciliation (RSR).
//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");
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");
}
row["Note"] = table.Rows[i]["Note"].ToString();
tHist.Rows.Add(row);
}
return(tHist);
}
///<summary>Used to get sheets filled via the web. Passing in a null or empty list of clinic nums will only return sheets that are not assigned to a clinic.</summary>
public static DataTable GetWebFormSheetsTable(DateTime dateFrom, DateTime dateTo, List <long> listClinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listClinicNums));
}
if (listClinicNums == null || listClinicNums.Count == 0)
{
listClinicNums = new List <long>()
{
0
}; //To ensure we filter on at least one clinic (HQ).
}
DataTable table = new DataTable("");
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("date");
table.Columns.Add("dateOnly", typeof(DateTime)); //to help with sorting
table.Columns.Add("dateTime", typeof(DateTime));
table.Columns.Add("description");
table.Columns.Add("time");
table.Columns.Add("timeOnly", typeof(TimeSpan)); //to help with sorting
table.Columns.Add("PatNum");
table.Columns.Add("SheetNum");
table.Columns.Add("IsDeleted");
table.Columns.Add("ClinicNum");
List <DataRow> rows = new List <DataRow>();
string command = "SELECT DateTimeSheet,Description,PatNum,SheetNum,IsDeleted,ClinicNum "
+ "FROM sheet WHERE "
+ "DateTimeSheet >= " + POut.Date(dateFrom) + " AND DateTimeSheet <= " + POut.Date(dateTo.AddDays(1)) + " "
+ "AND IsWebForm = " + POut.Bool(true) + " "
+ "AND (SheetType=" + POut.Long((int)SheetTypeEnum.PatientForm) + " OR SheetType=" + POut.Long((int)SheetTypeEnum.MedicalHistory) + ") "
+ (PrefC.HasClinicsEnabled ? "AND ClinicNum IN (" + string.Join(",", listClinicNums) + ") " : "");
DataTable rawSheet = Db.GetTable(command);
DateTime dateT;
for (int i = 0; i < rawSheet.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.DateT(rawSheet.Rows[i]["DateTimeSheet"].ToString());
row["date"] = dateT.ToShortDateString();
row["dateOnly"] = dateT.Date;
row["dateTime"] = dateT;
row["description"] = rawSheet.Rows[i]["Description"].ToString();
row["PatNum"] = rawSheet.Rows[i]["PatNum"].ToString();
row["SheetNum"] = rawSheet.Rows[i]["SheetNum"].ToString();
if (dateT.TimeOfDay != TimeSpan.Zero)
{
row["time"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower();
}
row["timeOnly"] = dateT.TimeOfDay;
row["IsDeleted"] = rawSheet.Rows[i]["IsDeleted"].ToString();
row["ClinicNum"] = PIn.Long(rawSheet.Rows[i]["ClinicNum"].ToString());
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
DataView view = table.DefaultView;
view.Sort = "dateOnly,timeOnly";
table = view.ToTable();
return(table);
}
public static List <List <int> > GetProdProvs(DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <List <int> > >(MethodBase.GetCurrentMethod(), dateFrom, dateTo));
}
Random rnd = new Random();
string rndStr = rnd.Next(1000000).ToString();
string command;
#if DEBUG
_elapsedTimeProdProvs = "";
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch();
_elapsedTimeProdProvs = "Elapsed time for GetProdProvs:\r\n";
stopWatch.Restart();
stopWatchTotal.Restart();
#endif
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//this table will contain approx 12x3xProv rows if there was production for each prov in each month.
command = @"CREATE TABLE tempdash" + rndStr + @" (
DatePeriod date NOT NULL,
ProvNum bigint NOT NULL,
production decimal NOT NULL
) DEFAULT CHARSET=utf8" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "CREATE TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//procs. Inserts approx 12xProv rows
command = @"INSERT INTO tempdash" + rndStr + @"
SELECT procedurelog.ProcDate,procedurelog.ProvNum,
SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(claimproc.WriteOff),0)
FROM procedurelog USE INDEX(indexPNPD)
LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum
AND claimproc.Status='7' /*only CapComplete writeoffs are subtracted here*/
WHERE procedurelog.ProcStatus = '2'
AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + @"
AND procedurelog.ProcDate <= " + POut.Date(dateTo) + @"
GROUP BY procedurelog.ProvNum,MONTH(procedurelog.ProcDate)" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "INSERT INTO: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//todo 2 more tables
//get all the data as 12xProv rows
command = @"SELECT DatePeriod,ProvNum,SUM(production) prod
FROM tempdash" + rndStr + @"
GROUP BY ProvNum,MONTH(DatePeriod)" ;//this fails with date issue
DataTable tableProd = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "tableProd: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
Db.NonQ(command);
command = @"SELECT ProvNum
FROM provider WHERE IsHidden=0
ORDER BY ItemOrder" ;
DataTable tableProv = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
stopWatchTotal.Stop();
_elapsedTimeProdProvs += "SELECT ProvNum FROM provider: " + stopWatch.Elapsed.ToString() + "\r\n";
_elapsedTimeProdProvs += "Total: " + stopWatchTotal.Elapsed.ToString();
if (_showElapsedTimesForDebug)
{
System.Windows.Forms.MessageBox.Show(_elapsedTimeProdProvs);
}
#endif
List <List <int> > retVal = new List <List <int> >();
for (int p = 0; p < tableProv.Rows.Count; p++) //loop through each provider
{
long provNum = PIn.Long(tableProv.Rows[p]["ProvNum"].ToString());
List <int> listInt = new List <int>(); //12 items
for (int i = 0; i < 12; i++)
{
decimal prod = 0;
DateTime datePeriod = dateFrom.AddMonths(i); //only the month and year are important
for (int j = 0; j < tableProd.Rows.Count; j++)
{
if (provNum == PIn.Long(tableProd.Rows[j]["ProvNum"].ToString()) &&
datePeriod.Month == PIn.Date(tableProd.Rows[j]["DatePeriod"].ToString()).Month &&
datePeriod.Year == PIn.Date(tableProd.Rows[j]["DatePeriod"].ToString()).Year)
{
prod = PIn.Decimal(tableProd.Rows[j]["prod"].ToString());
break;
}
}
listInt.Add((int)(prod));
}
retVal.Add(listInt);
}
return(retVal);
}
public static List <List <int> > GetProdProvs(DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <List <int> > >(MethodBase.GetCurrentMethod(), dateFrom, dateTo));
}
string command;
command = "DROP TABLE IF EXISTS tempdash;";
Db.NonQ(command);
//this table will contain approx 12x3xProv rows if there was production for each prov in each month.
command = @"CREATE TABLE tempdash (
DatePeriod date NOT NULL,
ProvNum bigint NOT NULL,
production decimal NOT NULL
) DEFAULT CHARSET=utf8" ;
Db.NonQ(command);
//procs. Inserts approx 12xProv rows
command = @"INSERT INTO tempdash
SELECT procedurelog.ProcDate,procedurelog.ProvNum,
SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(claimproc.WriteOff),0)
FROM procedurelog
LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum
AND claimproc.Status='7' /*only CapComplete writeoffs are subtracted here*/
WHERE procedurelog.ProcStatus = '2'
AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + @"
AND procedurelog.ProcDate <= " + POut.Date(dateTo) + @"
GROUP BY procedurelog.ProvNum,MONTH(procedurelog.ProcDate)" ;
Db.NonQ(command);
//todo 2 more tables
//get all the data as 12xProv rows
command = @"SELECT DatePeriod,ProvNum,SUM(production) prod
FROM tempdash
GROUP BY ProvNum,MONTH(DatePeriod)" ;//this fails with date issue
DataTable tableProd = Db.GetTable(command);
command = "DROP TABLE IF EXISTS tempdash;";
Db.NonQ(command);
command = @"SELECT ProvNum
FROM provider WHERE IsHidden=0
ORDER BY ItemOrder" ;
DataTable tableProv = Db.GetTable(command);
List <List <int> > retVal = new List <List <int> >();
for (int p = 0; p < tableProv.Rows.Count; p++) //loop through each provider
{
long provNum = PIn.Long(tableProv.Rows[p]["ProvNum"].ToString());
List <int> listInt = new List <int>(); //12 items
for (int i = 0; i < 12; i++)
{
decimal prod = 0;
DateTime datePeriod = dateFrom.AddMonths(i); //only the month and year are important
for (int j = 0; j < tableProd.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tableProd.Rows[j]["DatePeriod"].ToString()).Year &&
datePeriod.Month == PIn.Date(tableProd.Rows[j]["DatePeriod"].ToString()).Month &&
provNum == PIn.Long(tableProd.Rows[j]["ProvNum"].ToString()))
{
prod = PIn.Decimal(tableProd.Rows[j]["prod"].ToString());
break;
}
}
listInt.Add((int)(prod));
}
retVal.Add(listInt);
}
return(retVal);
}
public static DataTable GetPatientFormsTable(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNum));
}
//DataConnection dcon=new DataConnection();
DataTable table = new DataTable("");
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("date");
table.Columns.Add("dateOnly", typeof(DateTime)); //to help with sorting
table.Columns.Add("dateTime", typeof(DateTime));
table.Columns.Add("description");
table.Columns.Add("DocNum");
table.Columns.Add("imageCat");
table.Columns.Add("SheetNum");
table.Columns.Add("showInTerminal");
table.Columns.Add("time");
table.Columns.Add("timeOnly", typeof(TimeSpan)); //to help with sorting
//but we won't actually fill this table with rows until the very end. It's more useful to use a List<> for now.
List <DataRow> rows = new List <DataRow>();
//sheet---------------------------------------------------------------------------------------
string command = "SELECT DateTimeSheet,SheetNum,Description,ShowInTerminal "
+ "FROM sheet WHERE IsDeleted=0 "
+ "AND PatNum =" + POut.Long(patNum) + " "
+ "AND (SheetType=" + POut.Long((int)SheetTypeEnum.PatientForm) + " OR SheetType=" + POut.Long((int)SheetTypeEnum.MedicalHistory);
if (PrefC.GetBool(PrefName.PatientFormsShowConsent))
{
command += " OR SheetType=" + POut.Long((int)SheetTypeEnum.Consent); //Show consent forms if pref is true.
}
command += ")";
//+"ORDER BY ShowInTerminal";//DATE(DateTimeSheet),ShowInTerminal,TIME(DateTimeSheet)";
DataTable rawSheet = Db.GetTable(command);
DateTime dateT;
for (int i = 0; i < rawSheet.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.DateT(rawSheet.Rows[i]["DateTimeSheet"].ToString());
row["date"] = dateT.ToShortDateString();
row["dateOnly"] = dateT.Date;
row["dateTime"] = dateT;
row["description"] = rawSheet.Rows[i]["Description"].ToString();
row["DocNum"] = "0";
row["imageCat"] = "";
row["SheetNum"] = rawSheet.Rows[i]["SheetNum"].ToString();
if (rawSheet.Rows[i]["ShowInTerminal"].ToString() == "0")
{
row["showInTerminal"] = "";
}
else
{
row["showInTerminal"] = rawSheet.Rows[i]["ShowInTerminal"].ToString();
}
if (dateT.TimeOfDay != TimeSpan.Zero)
{
row["time"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower();
}
row["timeOnly"] = dateT.TimeOfDay;
rows.Add(row);
}
//document---------------------------------------------------------------------------------------
command = "SELECT DateCreated,DocCategory,DocNum,Description "
+ "FROM document,definition "
+ "WHERE document.DocCategory=definition.DefNum"
+ " AND PatNum =" + POut.Long(patNum)
+ " AND definition.ItemValue LIKE '%F%'";
//+" ORDER BY DateCreated";
DataTable rawDoc = Db.GetTable(command);
long docCat;
for (int i = 0; i < rawDoc.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.DateT(rawDoc.Rows[i]["DateCreated"].ToString());
row["date"] = dateT.ToShortDateString();
row["dateOnly"] = dateT.Date;
row["dateTime"] = dateT;
row["description"] = rawDoc.Rows[i]["Description"].ToString();
row["DocNum"] = rawDoc.Rows[i]["DocNum"].ToString();
docCat = PIn.Long(rawDoc.Rows[i]["DocCategory"].ToString());
row["imageCat"] = Defs.GetName(DefCat.ImageCats, docCat);
row["SheetNum"] = "0";
row["showInTerminal"] = "";
if (dateT.TimeOfDay != TimeSpan.Zero)
{
row["time"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower();
}
row["timeOnly"] = dateT.TimeOfDay;
rows.Add(row);
}
//Sorting
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
DataView view = table.DefaultView;
view.Sort = "dateOnly,showInTerminal,timeOnly";
table = view.ToTable();
return(table);
}
///<summary></summary>
public static void DeleteObject(long sheetDefNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), sheetDefNum);
return;
}
//validate that not already in use by a refferral.
string command = "SELECT LName,FName FROM referral WHERE Slip=" + POut.Long(sheetDefNum);
DataTable table = Db.GetTable(command);
//int count=PIn.PInt(Db.GetCount(command));
string referralNames = "";
for (int i = 0; i < table.Rows.Count; i++)
{
if (i > 0)
{
referralNames += ", ";
}
referralNames += table.Rows[i]["FName"].ToString() + " " + table.Rows[i]["LName"].ToString();
}
if (table.Rows.Count > 0)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is already in use by referrals. Not allowed to delete.") + " " + referralNames);
}
//validate that not already in use by automation.
command = "SELECT AutomationNum FROM automation WHERE SheetDefNum=" + POut.Long(sheetDefNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is in use by automation. Not allowed to delete."));
}
//validate that not already in use by a laboratory
command = "SELECT Description FROM laboratory WHERE Slip=" + POut.Long(sheetDefNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is in use by laboratories. Not allowed to delete.")
+ "\r\n" + string.Join(", ", table.Select().Select(x => x["Description"].ToString())));
}
//validate that not already in use by clinicPref.
command = "SELECT ClinicNum FROM clinicpref WHERE ValueString='" + POut.Long(sheetDefNum) + "' AND PrefName='" + POut.String(PrefName.SheetsDefaultRx.ToString()) + "'";
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is in use by clinics. Not allowed to delete.")
+ "\r\n" + string.Join(", ", table.Select().Select(x => Clinics.GetAbbr(PIn.Long(x["ClinicNum"].ToString())))));
}
//validate that not already in use by eClipboard
command = "SELECT EClipboardSheetDefNum,ClinicNum FROM eclipboardsheetdef WHERE SheetDefNum=" + POut.Long(sheetDefNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
if (PrefC.HasClinicsEnabled)
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is in use by eClipboard. Not allowed to delete.")
+ "\r\n" + string.Join(", ", table.Select()
.Select(x => Clinics.GetAbbr(PIn.Long(x["ClinicNum"].ToString())))
.Select(x => string.IsNullOrEmpty(x) ? "Default" : x)));
}
else
{
throw new ApplicationException(Lans.g("sheetDefs", "SheetDef is in use by eClipboard. Not allowed to delete."));
}
}
command = "DELETE FROM sheetfielddef WHERE SheetDefNum=" + POut.Long(sheetDefNum);
Db.NonQ(command);
Crud.SheetDefCrud.Delete(sheetDefNum);
}
/// <summary>Only Called only from FormPayment.butOK click. Only called if the user did not enter any splits. Usually just adds one split for the current patient. But if that would take the balance negative, then it loops through all other family members and creates splits for them. It might still take the current patient negative once all other family members are zeroed out.</summary>
public static List <PaySplit> Allocate(Payment pay) //double amtTot,int patNum,Payment payNum){
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <PaySplit> >(MethodBase.GetCurrentMethod(), pay));
}
string command =
"SELECT Guarantor FROM patient "
+ "WHERE PatNum = " + POut.Long(pay.PatNum);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return(new List <PaySplit>());
}
command =
"SELECT patient.PatNum,EstBalance,PriProv,SUM(InsPayEst)+SUM(Writeoff) insEst_ "
+ "FROM patient "
+ "LEFT JOIN claimproc ON patient.PatNum=claimproc.PatNum "
+ "AND Status=0 " //NotReceived
+ "WHERE Guarantor = " + table.Rows[0][0].ToString() + " "
+ "GROUP BY patient.PatNum,EstBalance,PriProv";
//+" ORDER BY PatNum!="+POut.PInt(pay.PatNum);//puts current patient in position 0 //Oracle does not allow
table = Db.GetTable(command);
List <Patient> pats = new List <Patient>();
Patient pat;
//first, put the current patient at position 0.
for (int i = 0; i < table.Rows.Count; i++)
{
if (table.Rows[i]["PatNum"].ToString() == pay.PatNum.ToString())
{
pat = new Patient();
pat.PatNum = PIn.Long(table.Rows[i][0].ToString());
pat.EstBalance = PIn.Double(table.Rows[i][1].ToString());
if (!PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
pat.EstBalance -= PIn.Double(table.Rows[i]["insEst_"].ToString());
}
pat.PriProv = PIn.Long(table.Rows[i][2].ToString());
pats.Add(pat.Copy());
}
}
//then, do all the rest of the patients.
for (int i = 0; i < table.Rows.Count; i++)
{
if (table.Rows[i]["PatNum"].ToString() == pay.PatNum.ToString())
{
continue;
}
pat = new Patient();
pat.PatNum = PIn.Long(table.Rows[i][0].ToString());
pat.EstBalance = PIn.Double(table.Rows[i][1].ToString());
if (!PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
pat.EstBalance -= PIn.Double(table.Rows[i]["insEst_"].ToString());
}
pat.PriProv = PIn.Long(table.Rows[i][2].ToString());
pats.Add(pat.Copy());
}
//first calculate all the amounts
double amtRemain = pay.PayAmt; //start off with the full amount
double[] amtSplits = new double[pats.Count];
//loop through each family member, starting with current
for (int i = 0; i < pats.Count; i++)
{
if (pats[i].EstBalance == 0 || pats[i].EstBalance < 0)
{
continue; //don't apply paysplits to anyone with a negative balance
}
if (amtRemain < pats[i].EstBalance) //entire remainder can be allocated to this patient
{
amtSplits[i] = amtRemain;
amtRemain = 0;
break;
}
else //amount remaining is more than or equal to the estBal for this family member
{
amtSplits[i] = pats[i].EstBalance;
amtRemain -= pats[i].EstBalance;
}
}
//add any remainder to the split for this patient
amtSplits[0] += amtRemain;
//now create a split for each non-zero amount
PaySplit PaySplitCur;
List <PaySplit> retVal = new List <PaySplit>();
for (int i = 0; i < pats.Count; i++)
{
if (amtSplits[i] == 0)
{
continue;
}
PaySplitCur = new PaySplit();
PaySplitCur.PatNum = pats[i].PatNum;
PaySplitCur.PayNum = pay.PayNum;
PaySplitCur.DatePay = pay.PayDate;
PaySplitCur.ClinicNum = pay.ClinicNum;
PaySplitCur.ProvNum = Patients.GetProvNum(pats[i]);
PaySplitCur.SplitAmt = Math.Round(amtSplits[i], CultureInfo.CurrentCulture.NumberFormat.CurrencyDecimalDigits);
//PaySplitCur.InsertOrUpdate(true);
retVal.Add(PaySplitCur);
}
//finally, adjust each EstBalance, but no need to do current patient
//This no longer works here. Must do it when closing payment window somehow
/*for(int i=1;i<pats.Length;i++){
* if(amtSplits[i]==0){
* continue;
* }
* command="UPDATE patient SET EstBalance=EstBalance-"+POut.PDouble(amtSplits[i])
+" WHERE PatNum="+POut.PInt(pats[i].PatNum);
* Db.NonQ(command);
* }*/
return(retVal);
}
///<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 void AddUnreads(long taskNum, long curUserNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), taskNum, curUserNum);
return;
}
//if the task is done, don't add unreads
string command = "SELECT TaskStatus,UserNum,ReminderGroupId,DateTimeEntry," + DbHelper.Now() + " DbTime "
+ "FROM task WHERE TaskNum = " + POut.Long(taskNum);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return; //only happens when a task was deleted by one user but left open on another user's computer.
}
TaskStatusEnum taskStatus = (TaskStatusEnum)PIn.Int(table.Rows[0]["TaskStatus"].ToString());
long userNumOwner = PIn.Long(table.Rows[0]["UserNum"].ToString());
if (taskStatus == TaskStatusEnum.Done) //
{
return;
}
//Set it unread for the original owner of the task.
if (userNumOwner != curUserNum) //but only if it's some other user
{
SetUnread(userNumOwner, taskNum);
}
//Set it for this user if a future repeating task, so it will be new when "due". Doing this here so we don't check every row below.
//Only for future dates because we don't want to mark as new if it was already "due" and you added a note or something.
if ((PIn.String(table.Rows[0]["ReminderGroupId"].ToString()) != "") && //Is a reminder
(PIn.DateT(table.Rows[0]["DateTimeEntry"].ToString()) > PIn.DateT(table.Rows[0]["DbTime"].ToString()))) //Is "due" in the future by DbTime
{
SetUnread(curUserNum, taskNum); //Set unread for current user only, other users dealt with below.
}
//Then, for anyone subscribed
long userNum;
bool isUnread;
//task subscriptions are not cached yet, so we use a query.
//Get a list of all subscribers to this task
command = @"SELECT
tasksubscription.UserNum,
(CASE WHEN taskunread.UserNum IS NULL THEN 0 ELSE 1 END) IsUnread
FROM tasksubscription
INNER JOIN tasklist ON tasksubscription.TaskListNum = tasklist.TaskListNum
INNER JOIN taskancestor ON taskancestor.TaskListNum = tasklist.TaskListNum
AND taskancestor.TaskNum = " + POut.Long(taskNum) + " ";
command += "LEFT JOIN taskunread ON taskunread.UserNum = tasksubscription.UserNum AND taskunread.TaskNum=taskancestor.TaskNum";
table = Db.GetTable(command);
List <long> listUserNums = new List <long>();
for (int i = 0; i < table.Rows.Count; i++)
{
userNum = PIn.Long(table.Rows[i]["UserNum"].ToString());
isUnread = PIn.Bool(table.Rows[i]["IsUnread"].ToString());
if (userNum == userNumOwner || //already set
userNum == curUserNum || //If the current user is subscribed to this task. User has obviously already read it.
listUserNums.Contains(userNum) ||
isUnread) //Unread currently exists
{
continue;
}
listUserNums.Add(userNum);
}
SetUnreadMany(listUserNums, taskNum); //This no longer results in duplicates like it used to
}
///<summary>Throws exceptions. Will purposefully throw ODExceptions that are already translated and and formatted.
///Returns the PaymentId given by PaySimple.</summary>
private static ApiResponse MakePaymentNoPat(decimal payAmt, string ccNum, DateTime ccExpDate, string billingZipCode = "", string cvv = "", long clinicNum = -1)
{
ValidateProgram(clinicNum);
if (string.IsNullOrWhiteSpace(ccNum) || ccExpDate.Year < DateTime.Today.Year)
{
throw new ODException(Lans.g("PaySimple", "Error making payment"));
}
long psCustomerId = AddCustomer("UNKNOWN", "UNKNOWN", "", clinicNum);
ApiResponse apiResponse = AddCreditCard(psCustomerId, ccNum, ccExpDate, billingZipCode);
string accountId = apiResponse.PaySimpleToken;
return(PaySimpleApi.PostPayment(GetAuthHeader(clinicNum), PaySimpleApi.MakeNewPaymentData(PIn.Long(accountId), payAmt, cvv)));
}
///<summary>Get all payment plans for this patient with the insurance plan identified by PlanNum and InsSubNum attached (marked used for tracking expected insurance payments) that have not been paid in full. Only returns plans with no claimprocs currently attached or claimprocs from the claim identified by the claimNum sent in attached. If claimNum is 0 all payment plans with planNum, insSubNum, and patNum not paid in full will be returned.</summary>
public static List <PayPlan> GetValidInsPayPlans(long patNum, long planNum, long insSubNum, long claimNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <PayPlan> >(MethodBase.GetCurrentMethod(), patNum, planNum, insSubNum, claimNum));
}
string command = "";
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += "SELECT payplan.*,MAX(claimproc.ClaimNum) ClaimNum";
}
else
{
command += "SELECT payplan.PayPlanNum,payplan.PatNum,payplan.Guarantor,payplan.PayPlanDate,"
+ "payplan.APR,payplan.Note,payplan.PlanNum,payplan.CompletedAmt,payplan.InsSubNum,MAX(claimproc.ClaimNum) ClaimNum";
}
command += " FROM payplan"
+ " LEFT JOIN claimproc ON claimproc.PayPlanNum=payplan.PayPlanNum"
+ " WHERE payplan.PatNum=" + POut.Long(patNum)
+ " AND payplan.PlanNum=" + POut.Long(planNum)
+ " AND payplan.InsSubNum=" + POut.Long(insSubNum);
if (claimNum > 0)
{
command += " AND (claimproc.ClaimNum IS NULL OR claimproc.ClaimNum=" + POut.Long(claimNum) + ")"; //payplans with no claimprocs attached or only claimprocs from the same claim
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
command += " GROUP BY payplan.PayPlanNum";
}
else
{
command += " GROUP BY payplan.PayPlanNum,payplan.PatNum,payplan.Guarantor,payplan.PayPlanDate,"
+ "payplan.APR,payplan.Note,payplan.PlanNum,payplan.CompletedAmt,payplan.InsSubNum";
}
command += " HAVING payplan.CompletedAmt>SUM(COALESCE(claimproc.InsPayAmt,0))"; //has not been paid in full yet
if (claimNum == 0) //if current claimproc is not attached to a claim, do not return payplans with claimprocs from existing claims already attached
{
command += " AND (MAX(claimproc.ClaimNum) IS NULL OR MAX(claimproc.ClaimNum)=0)";
}
command += " ORDER BY payplan.PayPlanDate";
DataTable payPlansWithClaimNum = Db.GetTable(command);
List <PayPlan> retval = new List <PayPlan>();
for (int i = 0; i < payPlansWithClaimNum.Rows.Count; i++)
{
PayPlan planCur = new PayPlan();
planCur.PayPlanNum = PIn.Long(payPlansWithClaimNum.Rows[i]["PayPlanNum"].ToString());
planCur.PatNum = PIn.Long(payPlansWithClaimNum.Rows[i]["PatNum"].ToString());
planCur.Guarantor = PIn.Long(payPlansWithClaimNum.Rows[i]["Guarantor"].ToString());
planCur.PayPlanDate = PIn.Date(payPlansWithClaimNum.Rows[i]["PayPlanDate"].ToString());
planCur.APR = PIn.Double(payPlansWithClaimNum.Rows[i]["APR"].ToString());
planCur.Note = payPlansWithClaimNum.Rows[i]["Note"].ToString();
planCur.PlanNum = PIn.Long(payPlansWithClaimNum.Rows[i]["PlanNum"].ToString());
planCur.CompletedAmt = PIn.Double(payPlansWithClaimNum.Rows[i]["CompletedAmt"].ToString());
planCur.InsSubNum = PIn.Long(payPlansWithClaimNum.Rows[i]["InsSubNum"].ToString());
if (claimNum > 0 && payPlansWithClaimNum.Rows[i]["ClaimNum"].ToString() == claimNum.ToString())
{
//if a payplan exists with claimprocs from the same claim as the current claimproc attached, always only return that one payplan
//claimprocs from one claim are not allowed to be attached to different payplans
retval.Clear();
retval.Add(planCur);
break;
}
retval.Add(planCur);
}
return(retval);
}
///<summary>Gets the full list to display in the Chart of Accounts, including balances.</summary>
public static DataTable GetFullList(DateTime asOfDate, bool showInactive)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), asOfDate, showInactive));
}
DataTable table = new DataTable("Accounts");
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("type");
table.Columns.Add("Description");
table.Columns.Add("balance");
table.Columns.Add("BankNumber");
table.Columns.Add("inactive");
table.Columns.Add("color");
table.Columns.Add("AccountNum");
//but we won't actually fill this table with rows until the very end. It's more useful to use a List<> for now.
List <DataRow> rows = new List <DataRow>();
//first, the entire history for the asset, liability, and equity accounts (except Retained Earnings)-----------
string command = "SELECT account.AcctType, account.Description, account.AccountNum, "
+ "SUM(DebitAmt) AS SumDebit, SUM(CreditAmt) AS SumCredit, account.BankNumber, account.Inactive, account.AccountColor "
+ "FROM account "
+ "LEFT JOIN journalentry ON journalentry.AccountNum=account.AccountNum AND "
+ "DateDisplayed <= " + POut.Date(asOfDate) + " WHERE AcctType<=2 ";
if (!showInactive)
{
command += "AND Inactive=0 ";
}
command += "GROUP BY account.AccountNum, account.AcctType, account.Description, account.BankNumber,"
+ "account.Inactive, account.AccountColor ORDER BY AcctType, Description";
DataTable rawTable = Db.GetTable(command);
AccountType aType;
decimal debit = 0;
decimal credit = 0;
for (int i = 0; i < rawTable.Rows.Count; i++)
{
row = table.NewRow();
aType = (AccountType)PIn.Long(rawTable.Rows[i]["AcctType"].ToString());
row["type"] = Lans.g("enumAccountType", aType.ToString());
row["Description"] = rawTable.Rows[i]["Description"].ToString();
debit = PIn.Decimal(rawTable.Rows[i]["SumDebit"].ToString());
credit = PIn.Decimal(rawTable.Rows[i]["SumCredit"].ToString());
if (DebitIsPos(aType))
{
row["balance"] = (debit - credit).ToString("N");
}
else
{
row["balance"] = (credit - debit).ToString("N");
}
row["BankNumber"] = rawTable.Rows[i]["BankNumber"].ToString();
if (rawTable.Rows[i]["Inactive"].ToString() == "0")
{
row["inactive"] = "";
}
else
{
row["inactive"] = "X";
}
row["color"] = rawTable.Rows[i]["AccountColor"].ToString(); //it will be an unsigned int at this point.
row["AccountNum"] = rawTable.Rows[i]["AccountNum"].ToString();
rows.Add(row);
}
//now, the Retained Earnings (auto) account-----------------------------------------------------------------
DateTime firstofYear = new DateTime(asOfDate.Year, 1, 1);
command = "SELECT AcctType, SUM(DebitAmt) AS SumDebit, SUM(CreditAmt) AS SumCredit "
+ "FROM account,journalentry "
+ "WHERE journalentry.AccountNum=account.AccountNum "
+ "AND DateDisplayed < " + POut.Date(firstofYear) //all from previous years
+ " AND (AcctType=3 OR AcctType=4) " //income or expenses
+ "GROUP BY AcctType ORDER BY AcctType"; //income first, but could return zero rows.
rawTable = Db.GetTable(command);
decimal balance = 0;
for (int i = 0; i < rawTable.Rows.Count; i++)
{
aType = (AccountType)PIn.Long(rawTable.Rows[i]["AcctType"].ToString());
debit = PIn.Decimal(rawTable.Rows[i]["SumDebit"].ToString());
credit = PIn.Decimal(rawTable.Rows[i]["SumCredit"].ToString());
//this works for both income and expenses, because we are subracting expenses, so signs cancel
balance += credit - debit;
}
row = table.NewRow();
row["type"] = Lans.g("enumAccountType", AccountType.Equity.ToString());
row["Description"] = Lans.g("Accounts", "Retained Earnings (auto)");
row["balance"] = balance.ToString("N");
row["BankNumber"] = "";
row["color"] = Color.White.ToArgb();
row["AccountNum"] = "0";
rows.Add(row);
//finally, income and expenses------------------------------------------------------------------------------
command = "SELECT account.AcctType, account.Description, account.AccountNum, "
+ "SUM(DebitAmt) AS SumDebit, SUM(CreditAmt) AS SumCredit, account.BankNumber, account.Inactive, account.AccountColor "
+ "FROM account "
+ "LEFT JOIN journalentry ON journalentry.AccountNum=account.AccountNum "
+ "AND DateDisplayed <= " + POut.Date(asOfDate)
+ " AND DateDisplayed >= " + POut.Date(firstofYear) //only for this year
+ " WHERE (AcctType=3 OR AcctType=4) ";
if (!showInactive)
{
command += "AND Inactive=0 ";
}
command += "GROUP BY account.AccountNum, account.AcctType, account.Description, account.BankNumber,"
+ "account.Inactive, account.AccountColor ORDER BY AcctType, Description";
rawTable = Db.GetTable(command);
for (int i = 0; i < rawTable.Rows.Count; i++)
{
row = table.NewRow();
aType = (AccountType)PIn.Long(rawTable.Rows[i]["AcctType"].ToString());
row["type"] = Lans.g("enumAccountType", aType.ToString());
row["Description"] = rawTable.Rows[i]["Description"].ToString();
debit = PIn.Decimal(rawTable.Rows[i]["SumDebit"].ToString());
credit = PIn.Decimal(rawTable.Rows[i]["SumCredit"].ToString());
if (DebitIsPos(aType))
{
row["balance"] = (debit - credit).ToString("N");
}
else
{
row["balance"] = (credit - debit).ToString("N");
}
row["BankNumber"] = rawTable.Rows[i]["BankNumber"].ToString();
if (rawTable.Rows[i]["Inactive"].ToString() == "0")
{
row["inactive"] = "";
}
else
{
row["inactive"] = "X";
}
row["color"] = rawTable.Rows[i]["AccountColor"].ToString(); //it will be an unsigned int at this point.
row["AccountNum"] = rawTable.Rows[i]["AccountNum"].ToString();
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
return(table);
}
///<summary>Gets a list of unfinalized insurance payments.</summary>
public static List <UnfinalizedInsPay> GetUnfinalizedInsPay(string carrierName, DateTime dateClaimPayZero)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <UnfinalizedInsPay> >(MethodBase.GetCurrentMethod(), carrierName, dateClaimPayZero));
}
string command = @"
SELECT partialpay.PayType,partialpay.PatNum,partialpay.ClaimPaymentNum,partialpay.ClinicNum,partialpay.CarrierName,partialpay.Date,
partialpay.DOS,partialpay.Amount,partialpay.ClaimNum,partialpay.CountPats
FROM (
SELECT 'PartialPayment' PayType,COALESCE(MAX(claimproc.PatNum),0) PatNum,MAX(claimpayment.ClaimPaymentNum) ClaimPaymentNum,
COUNT(DISTINCT claimproc.PatNum) CountPats,MAX(claimpayment.ClinicNum) ClinicNum,MAX(claimpayment.CarrierName) CarrierName,
MAX(claimpayment.CheckDate) Date,COALESCE(MAX(claimproc.ProcDate),'0001-01-01') DOS,MAX(claimpayment.CheckAmt) Amount,0 ClaimNum
FROM claimpayment
LEFT JOIN claimproc ON claimproc.ClaimPaymentNum=claimpayment.ClaimPaymentNum
WHERE claimpayment.IsPartial = 1
AND claimpayment.CarrierName LIKE '%" + POut.String(carrierName.Trim()) + "%' " + @"
GROUP BY claimpayment.ClaimPaymentNum
UNION ALL
SELECT 'UnfinalizedPayment' PayType,MAX(claimproc.PatNum) PatNum,0 ClaimPaymentNum,1 CountPats,MAX(claimproc.ClinicNum) ClinicNum,
MAX(carrier.CarrierName) CarrierName,MAX(claimproc.DateCP) Date,MAX(claimproc.ProcDate) DOS,SUM(claimproc.InsPayAmt) Amount,
claimproc.ClaimNum
FROM claimproc
INNER JOIN insplan ON insplan.PlanNum=claimproc.PlanNum
INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
AND carrier.CarrierName LIKE '%" + POut.String(carrierName.Trim()) + "%' " + @"
WHERE claimproc.ClaimPaymentNum = 0
AND (claimproc.InsPayAmt != 0 " + ((dateClaimPayZero.Year > 1880)?("OR ProcDate >= " + POut.Date(dateClaimPayZero)):"") + ")" +
@"AND claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + "," + POut.Int((int)ClaimProcStatus.CapClaim) + @")
GROUP BY claimproc.ClaimNum
) partialpay" ;
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
List <Patient> listPats = Patients.GetMultPats(table.Select().Select(x => PIn.Long(x["PatNum"].ToString())).ToList()).ToList();
List <Claim> listClaims = Claims.GetClaimsFromClaimNums(table.Select().Select(x => PIn.Long(x["ClaimNum"].ToString())).ToList());
List <ClaimPayment> listPayments = ClaimPayments.GetByClaimPaymentNums(table.Select().Select(x => PIn.Long(x["ClaimPaymentNum"].ToString()))
.ToList());
List <UnfinalizedInsPay> listUnfinalizedInsPay = new List <UnfinalizedInsPay>();
for (int i = 0; i < table.Rows.Count; i++)
{
listUnfinalizedInsPay.Add(new UnfinalizedInsPay(table.Rows[i]["PayType"].ToString(),
listPats.FirstOrDefault(x => x.PatNum == PIn.Long(table.Rows[i]["PatNum"].ToString())),
PIn.Long(table.Rows[i]["ClinicNum"].ToString()),
table.Rows[i]["CarrierName"].ToString(),
PIn.Date(table.Rows[i]["Date"].ToString()),
PIn.Date(table.Rows[i]["DOS"].ToString()),
PIn.Double(table.Rows[i]["Amount"].ToString()),
listPayments.FirstOrDefault(x => x.ClaimPaymentNum == PIn.Long(table.Rows[i]["ClaimPaymentNum"].ToString())),
listClaims.FirstOrDefault(x => x.ClaimNum == PIn.Long(table.Rows[i]["ClaimNum"].ToString())),
PIn.Int(table.Rows[i]["CountPats"].ToString())
));
}
return(listUnfinalizedInsPay);
}
///<summary>Gets all of the families and their corresponding InsSubs for all families passed in (saves calling RefreshForFam() one by one).
///Returns a dictionary of key: family and all of their corresponding value: InsSubs</summary>
public static SerializableDictionary <Family, List <InsSub> > GetDictInsSubsForFams(List <Family> listFamilies)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetSerializableDictionary <Family, List <InsSub> >(MethodBase.GetCurrentMethod(), listFamilies));
}
SerializableDictionary <Family, List <InsSub> > dictFamilyInsSubs = new SerializableDictionary <Family, List <InsSub> >();
if (listFamilies == null || listFamilies.Count < 1)
{
return(dictFamilyInsSubs);
}
List <long> listPatNums = listFamilies.SelectMany(x => x.ListPats).Select(x => x.PatNum).ToList();
if (listPatNums == null || listPatNums.Count < 1)
{
return(dictFamilyInsSubs);
}
//The command is written ina nested fashion in order to be compatible with both MySQL and Oracle.
string command = "SELECT D.*,C.OnBehalfOf "
+ "FROM inssub D,((SELECT A.InsSubNum,A.Subscriber AS OnBehalfOf "
+ "FROM inssub A "
+ "WHERE A.Subscriber IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ")"
//in union, distinct is implied
+ ") UNION (SELECT B.InsSubNum,P.PatNum AS OnBehalfOf "
+ "FROM inssub B,patplan P "
+ "WHERE B.InsSubNum=P.InsSubNum AND P.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + "))"
+ ") C "
+ "WHERE D.InsSubNum=C.InsSubNum "
+ "ORDER BY " + DbHelper.UnionOrderBy("DateEffective", 4);
DataTable table = Db.GetTable(command);
foreach (Family family in listFamilies)
{
List <long> listOnBehalfOfs = family.ListPats.Select(x => x.PatNum).ToList();
List <InsSub> listInsSubs = new List <InsSub>();
List <DataRow> listDataRows = table.Select().Where(x => listOnBehalfOfs.Exists(y => y == PIn.Long(x["OnBehalfOf"].ToString()))).ToList();
DataTable tableFamilyInsSubs = table.Clone();
foreach (DataRow row in listDataRows)
{
tableFamilyInsSubs.ImportRow(row);
}
dictFamilyInsSubs[family] = Crud.InsSubCrud.TableToList(tableFamilyInsSubs);
}
return(dictFamilyInsSubs);
}
///<summary>Throws exceptions. Will purposefully throw ODExceptions that are already translated and and formatted.
///If PatNum is 0, we will make a one time payment for an UNKNOWN patient. This is currently only intended for prepaid insurance cards.
///Returns the PaymentId given by PaySimple.</summary>
public static ApiResponse MakePayment(long patNum, CreditCard cc, decimal payAmt, string ccNum, DateTime ccExpDate, bool isOneTimePayment, string billingZipCode = "", string cvv = "", long clinicNum = -1)
{
ValidateProgram(clinicNum);
if (patNum == 0)
{
//MakePaymentNoPat will validate its credentials.
return(MakePaymentNoPat(payAmt, ccNum, ccExpDate, billingZipCode, cvv, clinicNum));
}
if ((cc == null || string.IsNullOrWhiteSpace(cc.PaySimpleToken)) && (string.IsNullOrWhiteSpace(ccNum) || ccExpDate.Year < DateTime.Today.Year))
{
throw new ODException(Lans.g("PaySimple", "Error making payment"));
}
if (cc == null)
{
cc = new CreditCard()
{
PatNum = patNum,
PaySimpleToken = "",
};
}
if (string.IsNullOrWhiteSpace(cc.PaySimpleToken))
{
Patient patCur = Patients.GetPat(cc.PatNum);
if (patCur == null)
{
patCur = new Patient()
{
PatNum = patNum,
FName = "",
LName = "",
};
}
long psCustomerId = GetCustomerIdForPat(patCur.PatNum, patCur.FName, patCur.LName, clinicNum);
ApiResponse apiResponse = AddCreditCard(psCustomerId, ccNum, ccExpDate, billingZipCode, clinicNum);
cc.PaySimpleToken = apiResponse.PaySimpleToken;
if (!isOneTimePayment && cc.CreditCardNum > 0) //If the user doesn't want Open Dental to store their account id, we will let them continue entering their CC info.
{
CreditCards.Update(cc);
}
}
return(PaySimpleApi.PostPayment(GetAuthHeader(clinicNum), PaySimpleApi.MakeNewPaymentData(PIn.Long(cc.PaySimpleToken), payAmt, cvv)));
}
///<summary>Returns the number of subscribers moved.
///No need to pass in userNum, it's set before remoting role check and passed to the server if necessary.</summary>
public static long MoveSubscribers(long insPlanNumFrom, long insPlanNumTo, long userNum = 0)
{
if (RemotingClient.RemotingRole != RemotingRole.ServerWeb)
{
userNum = Security.CurUser.UserNum; //must be before normal remoting role check to get user at workstation
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod(), insPlanNumFrom, insPlanNumTo, userNum));
}
List <InsSub> listInsSubsFrom = GetListForPlanNum(insPlanNumFrom);
List <long> listBlockedPatNums = new List <long>();
//Perform the same validation as when the user manually drops insplans from FormInsPlan using the Drop button.
for (int i = 0; i < listInsSubsFrom.Count; i++)
{
InsSub insSubFrom = listInsSubsFrom[i];
List <PatPlan> listPatPlanFrom = PatPlans.Refresh(insSubFrom.Subscriber);
for (int j = 0; j < listPatPlanFrom.Count; j++)
{
PatPlan patPlanFrom = listPatPlanFrom[j];
//The following comments and logic are copied from the FormInsPlan Drop button...
//If they have a claim for this ins with today's date, don't let them drop.
//We already have code in place to delete claimprocs when we drop ins, but the claimprocs attached to claims are protected.
//The claim clearly needs to be deleted if they are dropping. We need the user to delete the claim before they drop the plan.
//We also have code in place to add new claimprocs when they add the correct insurance.
List <Claim> listClaims = Claims.Refresh(patPlanFrom.PatNum); //Get all claims for patient.
for (int k = 0; k < listClaims.Count; k++)
{
if (listClaims[k].PlanNum != insPlanNumFrom) //Make sure the claim is for the insurance plan we are about to change, not any other plans the patient might have.
{
continue;
}
if (listClaims[k].DateService != DateTime.Today) //not today
{
continue;
}
//Patient currently has a claim for the insplan they are trying to drop.
if (!listBlockedPatNums.Contains(patPlanFrom.PatNum))
{
listBlockedPatNums.Add(patPlanFrom.PatNum);
}
}
}
}
if (listBlockedPatNums.Count > 0)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < listBlockedPatNums.Count; i++)
{
sb.Append("\r\n");
Patient pat = Patients.GetPat(listBlockedPatNums[i]);
sb.Append("#" + listBlockedPatNums[i] + " " + pat.GetNameFLFormal());
}
throw new ApplicationException(Lans.g("InsSubs", "Before changing the subscribers on the insurance plan being moved from, please delete all of today's claims related to the insurance plan being moved from for the following patients") + ":" + sb.ToString());
}
//This loop mimics some of the logic in PatPlans.Delete().
int insSubMovedCount = 0;
for (int i = 0; i < listInsSubsFrom.Count; i++)
{
InsSub inssub = listInsSubsFrom[i];
long oldInsSubNum = inssub.InsSubNum;
inssub.InsSubNum = 0; //This will allow us to insert a new record.
inssub.PlanNum = insPlanNumTo;
inssub.DateEffective = DateTime.MinValue;
inssub.BenefitNotes = "";
inssub.SubscNote = "";
inssub.SecUserNumEntry = userNum;
long insSubNumNew = InsSubs.Insert(inssub);
string command = "SELECT PatNum FROM patplan WHERE InsSubNum=" + POut.Long(oldInsSubNum);
DataTable tablePatsForInsSub = Db.GetTable(command);
if (tablePatsForInsSub.Rows.Count == 0)
{
continue;
}
insSubMovedCount++;
for (int j = 0; j < tablePatsForInsSub.Rows.Count; j++)
{
long patNum = PIn.Long(tablePatsForInsSub.Rows[j]["PatNum"].ToString());
List <PatPlan> listPatPlans = PatPlans.Refresh(patNum);
for (int k = 0; k < listPatPlans.Count; k++)
{
PatPlan patPlan = listPatPlans[k];
if (patPlan.InsSubNum == oldInsSubNum)
{
command = "DELETE FROM benefit WHERE PatPlanNum=" + POut.Long(patPlan.PatPlanNum); //Delete patient specific benefits (rare).
Db.NonQ(command);
patPlan.InsSubNum = insSubNumNew;
PatPlans.Update(patPlan);
}
}
//Now that the plan has changed for the current subscriber, recalculate estimates.
bool prefChanged = false;
//Forcefully set pref false to prevent creating new estimates for all procs (including completed, sent procs)
if (Prefs.UpdateBool(PrefName.ClaimProcsAllowedToBackdate, false))
{
prefChanged = true; //We will turn the preference back on for the user after we finish our computations.
}
Family fam = Patients.GetFamily(patNum);
Patient pat = fam.GetPatient(patNum);
List <ClaimProc> listClaimProcs = ClaimProcs.Refresh(patNum);
List <Procedure> listProcs = Procedures.Refresh(patNum);
listPatPlans = PatPlans.Refresh(patNum);
List <InsSub> listInsSubs = InsSubs.RefreshForFam(fam);
List <InsPlan> listInsPlans = InsPlans.RefreshForSubList(listInsSubs);
List <Benefit> listBenefits = Benefits.Refresh(listPatPlans, listInsSubs);
Procedures.ComputeEstimatesForAll(patNum, listClaimProcs, listProcs, listInsPlans, listPatPlans, listBenefits, pat.Age, listInsSubs);
if (prefChanged)
{
Prefs.UpdateBool(PrefName.ClaimProcsAllowedToBackdate, true); //set back to original value if changed.
}
}
}
InsPlan insPlanFrom = InsPlans.RefreshOne(insPlanNumFrom);
InsPlan planOld = insPlanFrom.Copy();
insPlanFrom.IsHidden = true;
InsPlans.Update(insPlanFrom, planOld);
return(insSubMovedCount);
}
