///<summary></summary>
public static List <BugSubmission> GetAllInRange(DateTime dateFrom, DateTime dateTo, List <string> listVersionFilters = null, bool useConnectionStore = false)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <BugSubmission> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listVersionFilters, useConnectionStore));
}
bool hasSelections = (!listVersionFilters.IsNullOrEmpty());
List <BugSubmission> listBugSubs = new List <BugSubmission>();
DataAction.RunBugsHQ(() => {
string command = $@"SELECT * FROM bugsubmission WHERE {DbHelper.BetweenDates("SubmissionDateTime",dateFrom,dateTo)}
{(hasSelections
? $@" AND ({string.Join(" OR ",listVersionFilters.Select(x =>
(x=="Mobile"
? "DbInfoJson like '%\"DeviceId\":%' and DbInfoJson NOT LIKE '%\"DeviceId\":null%'"
: "DbVersion LIKE '"+POut.String(x)+"%'"
)))
})"
: ""
)}
" ;
listBugSubs = Crud.BugSubmissionCrud.SelectMany(command);
}, useConnectionStore);
return(listBugSubs);
}
public static DataTable GetReportData(List <long> listProvNums, List <long> listUnearnedTypeDefNums, List <long> listClinicNums
, bool hasClinicsEnabled, DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listProvNums, listUnearnedTypeDefNums, listClinicNums, hasClinicsEnabled, dateFrom, dateTo));
}
string command = $@"SELECT paysplit.DatePay,
CONCAT(patient.LName,', ',patient.FName),provider.Abbr," ;
if (hasClinicsEnabled)
{
command += "clinic.Abbr,";
}
command += $@"procedurecode.ProcCode,
procedurecode.Descript,paysplit.SplitAmt
FROM paysplit
INNER JOIN definition ON definition.DefNum=paysplit.UnearnedType AND definition.ItemValue!=''
INNER JOIN patient ON patient.PatNum=paysplit.PatNum
LEFT JOIN procedurelog ON procedurelog.ProcNum=paysplit.ProcNum
LEFT JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum
LEFT JOIN provider ON provider.ProvNum=paysplit.ProvNum
LEFT JOIN clinic ON clinic.ClinicNum=paysplit.ClinicNum
WHERE paysplit.ProvNum IN ({string.Join(",",listProvNums)})
AND paysplit.UnearnedType IN ({string.Join(",",listUnearnedTypeDefNums)})
AND {DbHelper.BetweenDates("paysplit.DatePay",dateFrom,dateTo)} " ;
if (listClinicNums.Count > 0)
{
command += $"AND paysplit.ClinicNum IN ({string.Join(",",listClinicNums)}) ";
}
command += "ORDER BY paysplit.DatePay,patient.LName,patient.FName";
return(Db.GetTable(command));
}
public static DataTable GetMissingPaymentsTable(DateTime dateStart, DateTime dateEnd)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
}
string command = "SELECT xchargetransaction.* "
+ "FROM xchargetransaction "
+ "WHERE " + DbHelper.BetweenDates("TransactionDateTime", dateStart, dateEnd) + " "
+ "AND xchargetransaction.ResultCode=0"; //Valid entries to count have result code 0
List <XChargeTransaction> listTrans = Crud.XChargeTransactionCrud.SelectMany(command);
command = "SELECT payment.* "
+ "FROM payment "
//only payments with the same PaymentType as the X-Charge PaymentType for the clinic
+ "INNER JOIN ("
+ "SELECT ClinicNum,PropertyValue PaymentType FROM programproperty "
+ "WHERE ProgramNum=" + POut.Long(Programs.GetProgramNum(ProgramName.Xcharge)) + " AND PropertyDesc='PaymentType'"
+ ") paytypes ON paytypes.ClinicNum=payment.ClinicNum AND paytypes.PaymentType=payment.PayType "
+ "WHERE DateEntry BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd);
List <Payment> listPays = Crud.PaymentCrud.SelectMany(command);
for (int i = listTrans.Count - 1; i >= 0; i--) //Looping backwards in order to remove items
{
XChargeTransaction tran = listTrans[i];
Payment pay = listPays.Where(x => x.PatNum == tran.PatNum)
.Where(x => x.DateEntry.Date == tran.TransactionDateTime.Date)
.Where(x => x.PayAmt.Equals(tran.Amount))
.FirstOrDefault();
if (pay == null) //The XCharge transaction does not have a corresponding payment.
{
continue;
}
listTrans.RemoveAt(i);
listPays.Remove(pay); //So that the same payment does not get counted for more than one XCharge transaction.
}
DataTable table = Crud.XChargeTransactionCrud.ListToTable(listTrans);
List <string> listColumnsToKeep = new List <string> {
"TransactionDateTime", "TransType", "ClerkID", "ItemNum", "PatNum", "CreditCardNum", "Expiration", "Result", "Amount"
};
//Remove columns we don't want.
for (int i = table.Columns.Count - 1; i >= 0; i--)
{
if (table.Columns[i].ColumnName.In(listColumnsToKeep))
{
continue;
}
table.Columns.RemoveAt(i);
}
//Reorder the column in the order we want them.
for (int i = 0; i < listColumnsToKeep.Count; i++)
{
table.Columns[listColumnsToKeep[i]].SetOrdinal(i);
}
return(table);
}
///<summary>Returns a list of bug submissions and their corresponding bugs.
///If a bugsubmission is not associated to a bug then the BugObj field on the bugsubmission object will be null.
///Performs grouping logic in order to minimize the amount of bugsubmissions in the return results.</summary>
public static List <BugSubmission> GetBugSubsForRegKeys(List <string> listRegKeys, DateTime dateFrom, DateTime dateTo)
{
if (listRegKeys == null || listRegKeys.Count == 0)
{
return(new List <BugSubmission>()); //No point in going through middle tier.
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <BugSubmission> >(MethodBase.GetCurrentMethod(), listRegKeys, dateFrom, dateTo));
}
List <BugSubmission> listRetVals = new List <BugSubmission>();
DataAction.RunBugsHQ(() => {
string command = "SELECT * FROM bugsubmission "
+ "LEFT JOIN bug ON bug.BugId=bugsubmission.BugId "
+ "WHERE bugsubmission.RegKey IN(" + string.Join(",", listRegKeys.Select(x => "'" + POut.String(x) + "'")) + ") "
+ "AND " + DbHelper.BetweenDates("SubmissionDateTime", dateFrom, dateTo) + " "
+ "ORDER BY bug.CreationDate DESC, bugsubmission.SubmissionDateTime DESC";
//The query selects all columns for the bugsubmission and bug tables in one query. Hopefully we never have conflicting columns that differ.
DataTable table = Db.GetTable(command);
//Make a clone of the table structure for the bug objects and only fill it with entries where the BugId row is valid.
DataTable tableBugs = table.Clone();
foreach (DataRow row in table.Select().Where(x => PIn.Long(x["BugId"].ToString(), false) != 0))
{
tableBugs.ImportRow(row);
}
//Extract all of the bug objects from the subset table.
List <Bug> listBugs = Crud.BugCrud.TableToList(tableBugs);
//Extract all of the bugsubmission objects from the results.
List <BugSubmission> listBugSubs = Crud.BugSubmissionCrud.TableToList(table);
//Associate any bug object with its corresponding bugsubmission object.
listBugSubs.ForEach(x => x.BugObj = listBugs.FirstOrDefault(y => y.BugId == x.BugId));
//Group the bug submissions by RegKey, ExceptionStackTrace, and BugId.
//Each grouping will be ordered by ProgramVersion individually.
//Take the first bugsubmission from each group and add it to listGroupedBugSubs for the return value.
foreach (BugSubmission bugSub in listBugSubs)
{
if (bugSub.TagCustom != null) //Used to skip already considered bug submissions via grouping logic.
{
continue;
}
List <BugSubmission> listGroupedSubs = listBugSubs.FindAll(x => x.RegKey == bugSub.RegKey &&
x.ExceptionStackTrace == bugSub.ExceptionStackTrace &&
x.BugId == bugSub.BugId);
listGroupedSubs.ForEach(x => x.TagCustom = true); //Flag all bugsubmissions in this group as handled.
//Only add the most pertinent item from the group to our return value.
listRetVals.Add(listGroupedSubs.OrderByDescending(x => new Version(x.TryGetPrefValue(PrefName.ProgramVersion, "0.0.0.0"))).First());
}
}, false);
return(listRetVals);
}
///<summary></summary>
public static List <BugSubmission> GetAllInRange(DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <BugSubmission> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo));
}
List <BugSubmission> listBugSubs = new List <BugSubmission>();
DataAction.RunBugsHQ(() => {
string command = "SELECT * FROM bugsubmission WHERE " + DbHelper.BetweenDates("SubmissionDateTime", dateFrom, dateTo);
listBugSubs = Crud.BugSubmissionCrud.TableToList(DataCore.GetTable(command));
}, false);
return(listBugSubs);
}
///<summary>Returns a list of bug submissions and any associated bugs.
///If a bugSubmission is not associated to a bug then bug entry will be null.</summary>
public static List <Tuple <BugSubmission, Bug> > GetBugSubsForRegKeys(List <string> listRegKeys, DateTime dateFrom, DateTime dateTo)
{
if (listRegKeys == null || listRegKeys.Count == 0)
{
return(new List <Tuple <BugSubmission, Bug> >()); //No point in going through middle tier.
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Tuple <BugSubmission, Bug> > >(MethodBase.GetCurrentMethod(), listRegKeys, dateFrom, dateTo));
}
List <long> listBugIDs = new List <long>();
List <Tuple <BugSubmission, Bug> > listSubBugs = new List <Tuple <BugSubmission, Bug> >();
DataAction.RunBugsHQ(() => {
string command = "SELECT * FROM bugsubmission "
+ "LEFT JOIN bug ON bug.BugId=bugsubmission.BugId "
+ "WHERE bugsubmission.RegKey IN(" + string.Join(",", listRegKeys.Select(x => "'" + POut.String(x) + "'")) + ") "
+ "AND " + DbHelper.BetweenDates("SubmissionDateTime", dateFrom, dateTo) + " "
+ "ORDER BY bug.CreationDate DESC, bugsubmission.SubmissionDateTime DESC";
DataTable table = DataCore.GetTable(command);
DataTable tableBugs = table.Clone(); //Clones structure, not row data.
foreach (DataRow row in table.Rows)
{
if (PIn.Long(row["BugId"].ToString(), false) == 0)
{
continue; //Do not import rows that do not have bugs, LEFT JOIN bug must be successful.
}
tableBugs.ImportRow(row);
}
List <Bug> listBugs = Crud.BugCrud.TableToList(tableBugs);
List <BugSubmission> listBugSubs = Crud.BugSubmissionCrud.TableToList(table);
foreach (BugSubmission sub in listBugSubs)
{
if (sub.TagOD != null)
{
continue; //Already handled.
}
//Grouping mimics FormBugSubmissions.cs
List <BugSubmission> listGroupedSubs = listBugSubs.FindAll(x => x.RegKey == sub.RegKey &&
x.ExceptionStackTrace == sub.ExceptionStackTrace &&
x.BugId == sub.BugId);
listGroupedSubs.ForEach(x => x.TagOD = true); //Used to skip already considered bug submissions via grouping logic.
BugSubmission subMax = listGroupedSubs.OrderByDescending(x => new Version(x.Info.DictPrefValues[PrefName.ProgramVersion])).First();
Bug bug = listBugs.FirstOrDefault(x => x.BugId == subMax.BugId); //Can be null.
listSubBugs.Add(new Tuple <BugSubmission, Bug>(subMax, bug));
}
}, false);
return(listSubBugs);
}
///<summary>Gets a list of AsapComms (along with a few more fields) for use in the Web Sched History window. To view for all patients or clinics,
///pass in null for those parameters.</summary>
public static List <AsapCommHist> GetHist(DateTime dateFrom, DateTime dateTo, List <long> listPatNums = null, List <long> listClinicNums = null)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <AsapCommHist> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listPatNums, listClinicNums));
}
string command = @"
SELECT asapcomm.*," + DbHelper.Concat("patient.LName", "', '", "patient.FName") + @" PatientName,COALESCE(schedule.StartTime,'00:00:00') StartTime,
COALESCE(schedule.StopTime,'00:00:00') StopTime,COALESCE(schedule.SchedDate,'0001-01-01') SchedDate,
COALESCE(emailmessage.BodyText,'') EmailMessageText,COALESCE(smstomobile.MsgText,'') SMSMessageText
FROM asapcomm
INNER JOIN patient ON patient.PatNum=asapcomm.PatNum
LEFT JOIN schedule ON schedule.ScheduleNum=asapcomm.ScheduleNum
LEFT JOIN emailmessage ON emailmessage.EmailMessageNum=asapcomm.EmailMessageNum
LEFT JOIN smstomobile ON smstomobile.GuidMessage=asapcomm.GuidMessageToMobile
WHERE " + DbHelper.BetweenDates("asapcomm.DateTimeEntry", dateFrom, dateTo) + " ";
if (listPatNums != null)
{
if (listPatNums.Count == 0)
{
return(new List <AsapCommHist>());
}
command += "AND asapcomm.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ") ";
}
if (listClinicNums != null)
{
if (listClinicNums.Count == 0)
{
return(new List <AsapCommHist>());
}
command += "AND asapcomm.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
DataTable table = Db.GetTable(command);
List <AsapCommHist> listHists = Crud.AsapCommCrud.TableToList(table).Select(x => new AsapCommHist()
{
AsapComm = x
}).ToList();
for (int i = 0; i < listHists.Count; i++)
{
listHists[i].PatientName = PIn.String(table.Rows[i]["PatientName"].ToString());
listHists[i].DateTimeSlotStart = PIn.Date(table.Rows[i]["SchedDate"].ToString()).Add(PIn.Time(table.Rows[i]["StartTime"].ToString()));
listHists[i].DateTimeSlotEnd = PIn.Date(table.Rows[i]["SchedDate"].ToString()).Add(PIn.Time(table.Rows[i]["StopTime"].ToString()));
listHists[i].EmailMessageText = PIn.String(table.Rows[i]["EmailMessageText"].ToString());
listHists[i].SMSMessageText = PIn.String(table.Rows[i]["SMSMessageText"].ToString());
}
return(listHists);
}
///<summary>Returns all jobs that the Query department would care about in regards to the date range passed in.
///This method will only return jobs that are in certain categories and are in the Complete or Documentaion phases.
///Optionally pass in a list of JobNums to exclude from the result set.</summary>
public static List <Job> GetForQueries(DateTime dateFrom, DateTime dateTo, List <long> listExcludeJobNums = null)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Job> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listExcludeJobNums));
}
string command = $@"SELECT * FROM job
WHERE Category='{JobCategory.Query}'
AND PhaseCur IN ('{JobPhase.Complete}','{JobPhase.Cancelled}')
AND {DbHelper.BetweenDates("DateTimeEntry",dateFrom,dateTo)} " ;
if (!listExcludeJobNums.IsNullOrEmpty())
{
command += $"AND JobNum NOT IN ({string.Join(",",listExcludeJobNums)})";
}
return(Crud.JobCrud.SelectMany(command));
}
///<summary>Returns a list of BugSubmissionHashes based on DateTimeEntry and given date range.</summary>
public static List <BugSubmissionHash> GetMany(DateTime dateTimeFrom = default, DateTime dateTimeTo = default, bool useConnectionStore = false)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <BugSubmissionHash> >(MethodBase.GetCurrentMethod(), dateTimeFrom, dateTimeTo, useConnectionStore));
}
List <BugSubmissionHash> listRetVals = new List <BugSubmissionHash>();
DataAction.RunBugsHQ(() => {
string command = $@"SELECT * FROM bugsubmissionhash ";
if (dateTimeFrom != DateTime.MinValue && dateTimeTo != DateTime.MinValue)
{
command += $@"WHERE " + DbHelper.BetweenDates("DateTimeEntry", dateTimeFrom, dateTimeTo);
}
command += "ORDER BY DateTimeEntry DESC";
listRetVals = Crud.BugSubmissionHashCrud.SelectMany(command);
}, useConnectionStore);
return(listRetVals);
}
///<summary>Returns all jobs for the date range and phases passed in. An empty list of phases will be treated as "Any" phase.
///Optionally pass in a list of JobNums to exclude from the result set.</summary>
public static List <Job> GetForSearch(DateTime dateFrom, DateTime dateTo, List <JobPhase> listPhases, List <long> listPriorities,
List <long> listExcludeJobNums = null)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Job> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listPhases, listPriorities, listExcludeJobNums));
}
string command = $@"SELECT * FROM job
WHERE {DbHelper.BetweenDates("DateTimeEntry",dateFrom,dateTo)} " ;
if (!listPhases.IsNullOrEmpty())
{
command += $"AND PhaseCur IN ({string.Join(",",listPhases.Select(x => $"'{x}'"))}) ";
}
if (!listExcludeJobNums.IsNullOrEmpty())
{
command += $"AND JobNum NOT IN ({string.Join(",",listExcludeJobNums)}) ";
}
if (!listPriorities.IsNullOrEmpty())
{
command += $"AND Priority IN ({string.Join(",",listPriorities.Select(x => $"'{x}'"))})";
}
return(Crud.JobCrud.SelectMany(command));
}
public static DataSet GetMonthlyGoalDataSet(DateTime dateFrom, DateTime dateTo, List <Provider> listProvs, List <Clinic> listClinics, bool hasAllProvs
, bool hasAllClinics, PPOWriteoffDateCalc writeoffPayType, bool isCEMT = false)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvs, listClinics, hasAllProvs, hasAllClinics, writeoffPayType, isCEMT));
}
List <long> listClinicNums = listClinics.Select(x => x.ClinicNum).ToList();
List <long> listProvNums = listProvs.Select(x => x.ProvNum).ToList();
#region Procedures
string whereProv = "";
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
string whereClin = "";
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
string command = "SELECT "
+ "procedurelog.ProcDate,procedurelog.ClinicNum,"
+ "SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(cp.WriteOff),0) Production "
+ "FROM procedurelog "
+ "LEFT JOIN (SELECT SUM(claimproc.WriteOff) AS WriteOff, claimproc.ProcNum FROM claimproc "
+ "WHERE claimproc.Status=7 " //only CapComplete writeoffs are subtracted here.
+ "GROUP BY claimproc.ProcNum) cp ON procedurelog.ProcNum=cp.ProcNum "
+ "WHERE procedurelog.ProcStatus = 2 "
+ whereProv
+ whereClin
+ "AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(dateTo) + " "
+ "GROUP BY ClinicNum,YEAR(procedurelog.ProcDate),MONTH(procedurelog.ProcDate),DAY(procedurelog.ProcDate)"; //Does not work for Oracle. Consider enhancing with DbHelper.Year(),DbHelper.Month()
command += " ORDER BY ClinicNum,ProcDate";
DataTable tableProduction = new DataTable();
if (isCEMT)
{
tableProduction = Db.GetTable(command);
}
else
{
tableProduction = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableProduction.TableName = "tableProduction";
#endregion
#region Adjustments
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND adjustment.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND adjustment.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
command = "SELECT "
+ "adjustment.AdjDate,"
+ "adjustment.ClinicNum,"
+ "SUM(adjustment.AdjAmt) Adjustment "
+ "FROM adjustment "
+ "WHERE adjustment.AdjDate >= " + POut.Date(dateFrom) + " "
+ "AND adjustment.AdjDate <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "GROUP BY ClinicNum,YEAR(adjustment.AdjDate),MONTH(adjustment.AdjDate),DAY(adjustment.AdjDate)";
command += " ORDER BY ClinicNum,AdjDate";
DataTable tableAdj = new DataTable();
if (isCEMT)
{
tableAdj = Db.GetTable(command);
}
else
{
tableAdj = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableAdj.TableName = "tableAdj";
#endregion
#region TableInsWriteoff
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND claimproc.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND claimproc.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (writeoffPayType == PPOWriteoffDateCalc.InsPayDate)
{
command = "SELECT "
+ "claimproc.DateCP Date,"
+ "claimproc.ClinicNum,"
+ "SUM(claimproc.WriteOff) WriteOff "
+ "FROM claimproc "
+ "WHERE claimproc.DateCP >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.DateCP <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + ") "//received or supplemental
+ "GROUP BY ClinicNum,DATE(claimproc.DateCP) "
+ "ORDER BY ClinicNum,DateCP";
}
else if (writeoffPayType == PPOWriteoffDateCalc.ProcDate)
{
command = "SELECT "
+ "claimproc.ProcDate Date,"
+ "claimproc.ClinicNum,"
+ "SUM(claimproc.WriteOff) WriteOff "
+ "FROM claimproc "
+ "WHERE claimproc.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.ProcDate <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "//received or supplemental or notreceived
+ "GROUP BY ClinicNum,DATE(claimproc.ProcDate) "
+ "ORDER BY ClinicNum,ProcDate";
}
else // writeoffPayType==PPOWriteoffDateCalc.ClaimPayDate
{
command = "SELECT "
+ "claimsnapshot.DateTEntry Date,"
+ "claimproc.ClinicNum,"
+ "SUM(CASE WHEN claimsnapshot.WriteOff=-1 THEN 0 ELSE claimsnapshot.WriteOff END) WriteOff "
+ "FROM claimproc "
+ "INNER JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimProc.ClaimProcNum "
+ "WHERE " + DbHelper.BetweenDates("claimsnapshot.DateTEntry", dateFrom, dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "//received or supplemental or notreceived
+ "GROUP BY ClinicNum,DATE(claimsnapshot.DateTEntry) "
+ "ORDER BY ClinicNum,claimsnapshot.DateTEntry";
}
DataTable tableInsWriteoff = new DataTable();
if (isCEMT)
{
tableInsWriteoff = Db.GetTable(command);
}
else
{
tableInsWriteoff = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableInsWriteoff.TableName = "tableInsWriteoff";
#endregion
#region TableSched
DataTable tableSched = new DataTable();
//Reads from the procedurelog table instead of claimproc because we are looking for scheduled procedures.
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
command = "SELECT " + DbHelper.DtimeToDate("t.AptDateTime") + " SchedDate,SUM(t.Fee-t.WriteoffEstimate) Amount,ClinicNum "
+ "FROM (SELECT appointment.AptDateTime,IFNULL(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits),0) Fee,appointment.ClinicNum,";
if (ReportsComplex.RunFuncOnReportServer(() => Prefs.GetBoolNoCache(PrefName.ReportPandIschedProdSubtractsWO)))
{
//Subtract both PPO and capitation writeoffs
command += "SUM(IFNULL(CASE WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END,0)) WriteoffEstimate ";
}
else
{
//Always subtract CapEstimate writeoffs from scheduled production. This is so that the scheduled production will match actual production
//when the procedures are set complete. Nathan decided this 01/05/2017.
command += "SUM(IFNULL((CASE WHEN claimproc.Status=" + POut.Int((int)ClaimProcStatus.Estimate) + " THEN 0 "
+ "WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END),0)) WriteoffEstimate ";
}
command += "FROM appointment "
+ "LEFT JOIN procedurelog ON appointment.AptNum = procedurelog.AptNum AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.TP) + " "
+ "LEFT JOIN claimproc ON procedurelog.ProcNum = claimproc.ProcNum "
+ "AND claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Estimate) + "," + POut.Int((int)ClaimProcStatus.CapEstimate) + ") "
+ " AND (WriteOffEst != -1 OR WriteOffEstOverride != -1) "
+ "WHERE appointment.AptStatus = " + POut.Int((int)ApptStatus.Scheduled) + " "
+ "AND " + DbHelper.DtimeToDate("appointment.AptDateTime") + " >= " + POut.Date(dateFrom) + " "
+ "AND " + DbHelper.DtimeToDate("appointment.AptDateTime") + " <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ " GROUP BY procedurelog.ProcNum) t " //without this, there can be duplicate proc rows due to the claimproc join with dual insurance.
+ "GROUP BY SchedDate,ClinicNum "
+ "ORDER BY SchedDate";
if (isCEMT)
{
tableSched = Db.GetTable(command);
}
else
{
tableSched = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableSched.TableName = "tableSched";
#endregion
#region TableProdGoal
//1. Find all schedules for the month
//2. ClinicNum will come from the schedule's operatory
//3. Fetch HourlyProdGoalAmt from provider on the schedule
//4. Sum scheduled hours, grouped by prov and clinic
//5. Multiply the scheduled hours by the provider's HourlyProdGoalAmt
DataTable tableProdGoal = new DataTable();
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND schedule.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND COALESCE(operatory.ClinicNum,0) IN (" + string.Join(",", listClinicNums) + ") ";
}
//Fetch all schedules for the month and associated information (clinic from operatory, HourlyProdGoalAmt from provider)
command = "SELECT " + DbHelper.DtimeToDate("schedule.SchedDate") + @" AS SchedDate, schedule.StartTime AS StartTime, schedule.StopTime AS StopTime,
COALESCE(operatory.ClinicNum,0) AS ClinicNum, provider.HourlyProdGoalAmt AS ProvProdGoal, provider.ProvNum AS ProvNum
FROM schedule
INNER JOIN provider ON provider.ProvNum=schedule.ProvNum
LEFT JOIN scheduleop ON scheduleop.ScheduleNum=schedule.ScheduleNum
LEFT JOIN operatory ON scheduleop.OperatoryNum=operatory.OperatoryNum
WHERE schedule.SchedType=" + POut.Int((int)ScheduleType.Provider) + " "
+ "AND schedule.Status=" + POut.Int((int)SchedStatus.Open) + " "
+ "AND schedule." + DbHelper.BetweenDates("SchedDate", dateFrom, dateTo) + " "
+ whereProv
+ whereClin;
if (isCEMT)
{
tableProdGoal = Db.GetTable(command);
}
else
{
tableProdGoal = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableProdGoal.TableName = "tableProdGoal";
#endregion
#region WriteOffAdjustments
DataTable tableWriteOffAdjustments = new DataTable();
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND claimproc.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND claimproc.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate)
{
//Insurance WriteOff Adjustments----------------------------------------------------------------------------
command = $@"SELECT claimproc.DateCP Date,claimproc.ClinicNum,
-SUM(CASE WHEN COALESCE(claimsnapshot.WriteOff,-1)=-1 THEN 0 ELSE claimsnapshot.WriteOff END) WriteOffEst,
SUM(claimproc.WriteOff) WriteOff,
claimproc.ClaimNum
FROM claimproc
LEFT JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum
WHERE claimproc.DateCP BETWEEN {POut.Date(dateFrom)} AND {POut.Date(dateTo)}
AND claimproc.Status IN ({(int)ClaimProcStatus.Received},{(int)ClaimProcStatus.Supplemental})
{whereProv}
{whereClin}
GROUP BY ClinicNum,DATE(claimproc.DateCP)" ;
if (isCEMT)
{
tableWriteOffAdjustments = Db.GetTable(command);
}
else
{
tableWriteOffAdjustments = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
}
tableWriteOffAdjustments.TableName = "tableWriteOffAdjustments";
#endregion WriteOffAdjustments
DataSet dataSet = new DataSet();
dataSet.Tables.Add(tableProduction);
dataSet.Tables.Add(tableAdj);
dataSet.Tables.Add(tableInsWriteoff);
dataSet.Tables.Add(tableWriteOffAdjustments);
dataSet.Tables.Add(tableSched);
dataSet.Tables.Add(tableProdGoal);
return(dataSet);
}
public static DataTable GetOverPaidProcs(long patNum, List <long> listProvNums, List <long> listClinics, DateTime dateStart, DateTime dateEnd)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNum, listProvNums, listClinics, dateStart, dateEnd));
}
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
#region Completed Procs
string command = "SELECT ";
if (PrefC.GetBool(PrefName.ReportsShowPatNum))
{
command += DbHelper.Concat("CAST(patient.PatNum AS CHAR)", "'-'", "patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
else
{
command += DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI");
}
command += @" AS 'patientName',
procedurelog.ProcDate,
procedurecode.ProcCode,
procedurelog.ToothNum,
provider.Abbr,
(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) AS fee,
patient.PatNum,
procedurelog.ProcNum
FROM procedurelog
INNER JOIN patient ON patient.PatNum=procedurelog.PatNum
INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum
INNER JOIN provider ON provider.ProvNum=procedurelog.ProvNum
WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " AND "
+ DbHelper.BetweenDates("procedurelog.ProcDate", dateStart, dateEnd) + " "
+ "AND procedurelog.ProcFee>0 ";
if (listProvNums != null && listProvNums.Count > 0)
{
command += "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums.Select(x => POut.Long(x))) + ") ";
}
if (listClinics != null && listClinics.Count > 0)
{
command += "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinics.Select(x => POut.Long(x))) + ") ";
}
if (patNum > 0)
{
command += "AND procedurelog.PatNum=" + POut.Long(patNum) + " ";
}
command += "ORDER BY procedurelog.ProcDate,patientName,procedurecode.ProcCode,provider.Abbr";
DataTable rawCompletedProcTable = Db.GetTable(command);
Dictionary <long, DataRow> dictCompletedProcRows = rawCompletedProcTable.Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
DataTable table = new DataTable();
if (dictCompletedProcRows.Count == 0)
{
return(table);
}
#region ClaimProcs
List <long> listPatNums = rawCompletedProcTable.Select().Select(x => PIn.Long(x["PatNum"].ToString())).Distinct().ToList();
command = @"SELECT MIN(claimproc.ProcNum) ProcNum,MIN(claimproc.PatNum) PatNum,MIN(claimproc.ProcDate) ProcDate,SUM(claimproc.InsPayAmt) insPayAmt,
SUM(claimproc.Writeoff) writeoff
FROM claimproc
WHERE claimproc.Status NOT IN(" + string.Join(",", new List <int> {
(int)ClaimProcStatus.Preauth,
(int)ClaimProcStatus.CapEstimate, (int)ClaimProcStatus.CapComplete, (int)ClaimProcStatus.Estimate, (int)ClaimProcStatus.InsHist
}
.Select(x => POut.Int(x))) + ") "
+ "AND " + DbHelper.BetweenDates("claimproc.ProcDate", dateStart, dateEnd) + " "
+ "AND claimproc.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ") "
+ @"GROUP BY claimproc.ProcNum
HAVING SUM(claimproc.InsPayAmt+claimproc.Writeoff)>0
ORDER BY NULL" ;
Dictionary <long, DataRow> dictClaimProcRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
#region Patient Payments
command = @"SELECT paysplit.ProcNum,SUM(paysplit.SplitAmt) ptAmt
FROM paysplit
WHERE paysplit.ProcNum>0
AND paysplit.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + $@") ";
if (listHiddenUnearnedDefNums.Count > 0)
{
command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
command += @"
GROUP BY paysplit.ProcNum
ORDER BY NULL" ;
Dictionary <long, DataRow> dictPatPayRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
#region Adjustments
command = @"SELECT adjustment.ProcNum,SUM(adjustment.AdjAmt) AdjAmt
FROM adjustment
WHERE adjustment.ProcNum>0
AND adjustment.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + @")
GROUP BY adjustment.ProcNum
ORDER BY NULL" ;
Dictionary <long, DataRow> dictAdjRows = Db.GetTable(command).Select().ToDictionary(x => PIn.Long(x["ProcNum"].ToString()));
#endregion
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("patientName");
table.Columns.Add("ProcDate", typeof(DateTime));
table.Columns.Add("ProcCode");
table.Columns.Add("ToothNum");
table.Columns.Add("Abbr");
table.Columns.Add("fee");
table.Columns.Add("insPaid");
table.Columns.Add("wo");
table.Columns.Add("ptPaid");
table.Columns.Add("adjAmt");
table.Columns.Add("overPay");
table.Columns.Add("PatNum");
DataRow row;
foreach (KeyValuePair <long, DataRow> kvp in dictCompletedProcRows)
{
long procNum = kvp.Key;
decimal procFeeAmt = PIn.Decimal(kvp.Value["fee"].ToString());
decimal insPaidAmt = 0;
decimal woAmt = 0;
decimal ptPaidAmt = 0;
decimal adjAmt = 0;
if (dictClaimProcRows.ContainsKey(procNum))
{
insPaidAmt = PIn.Decimal(dictClaimProcRows[procNum]["insPayAmt"].ToString());
woAmt = PIn.Decimal(dictClaimProcRows[procNum]["writeoff"].ToString());
}
if (dictPatPayRows.ContainsKey(procNum))
{
ptPaidAmt = PIn.Decimal(dictPatPayRows[procNum]["ptAmt"].ToString());
}
if (dictAdjRows.ContainsKey(procNum))
{
adjAmt = PIn.Decimal(dictAdjRows[procNum]["AdjAmt"].ToString());
}
decimal overPay = procFeeAmt - insPaidAmt - woAmt - ptPaidAmt + adjAmt;
if (!overPay.IsLessThanZero())
{
continue; //No overpayment. Not need to continue;
}
row = table.NewRow();
row["patientName"] = PIn.String(kvp.Value["patientName"].ToString());
row["ProcDate"] = PIn.Date(kvp.Value["ProcDate"].ToString());
row["ProcCode"] = PIn.String(kvp.Value["ProcCode"].ToString());
row["ToothNum"] = PIn.String(kvp.Value["ToothNum"].ToString());
row["Abbr"] = PIn.String(kvp.Value["Abbr"].ToString());;
row["fee"] = procFeeAmt.ToString();
row["insPaid"] = insPaidAmt.ToString();
row["wo"] = woAmt.ToString();
row["ptPaid"] = ptPaidAmt.ToString();
row["adjAmt"] = adjAmt.ToString();
row["overPay"] = overPay.ToString();
row["PatNum"] = PIn.Long(kvp.Value["PatNum"].ToString());
table.Rows.Add(row);
}
return(table);
}
///<summary>If not using clinics then supply an empty list of clinicNums.</summary>
public static DataTable GetAppointmentTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums,
bool hasClinicsEnabled, bool isShowRecall, bool isShowNewPat, bool isShowASAP, SortAndFilterBy sortBy, List <ApptStatus> listApptStatusesToExclude,
List <long> listConfirmationStatuses, string formSender)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, hasClinicsEnabled,
isShowRecall, isShowNewPat, isShowASAP, sortBy, listApptStatusesToExclude, listConfirmationStatuses, formSender));
}
//Appointment status conditions
string whereApptStatus = "";
if (listApptStatusesToExclude.Count > 0)
{
whereApptStatus += " appointment.AptStatus NOT IN (" + string.Join(",", listApptStatusesToExclude.Select(x => POut.Int((int)x))) + ") AND ";
}
//Provider Conditions
string whereProv = "";
if (listProvNums.Count > 0)
{
whereProv += " (appointment.ProvNum IN(" + string.Join(",", listProvNums) + ") "
+ " OR appointment.ProvHyg IN(" + string.Join(",", listProvNums) + ")) AND ";
}
//Clinic Conditions
string whereClinics = "";
if (hasClinicsEnabled && listClinicNums.Count > 0)
{
whereClinics += " appointment.ClinicNum IN(" + string.Join(",", listClinicNums) + ") AND ";
}
//Appointment confirmation conditions
string whereConfStatus = "";
if (listConfirmationStatuses.Count > 0)
{
whereConfStatus += " appointment.Confirmed IN (" + string.Join(",", listConfirmationStatuses) + ") AND ";
}
//WebSched Appointments
string innerJoinWebSchedBoth = "";
List <LogSources> listSources = new List <LogSources>();
if (isShowNewPat)
{
listSources.Add(LogSources.WebSchedNewPatAppt);
}
if (isShowRecall)
{
listSources.Add(LogSources.WebSched);
}
if (isShowASAP)
{
listSources.Add(LogSources.WebSchedASAP);
}
if (listSources.Count > 0)
{
List <int> listPerms = new List <int>()
{
(int)Permissions.AppointmentCreate
};
if (sortBy == SortAndFilterBy.SecurityLogDate)
{
listPerms.Add((int)Permissions.AppointmentEdit);
listPerms.Add((int)Permissions.AppointmentMove);
}
innerJoinWebSchedBoth = " INNER JOIN securitylog ON appointment.AptNum=securitylog.FKey"
+ " AND securitylog.PermType IN (" + string.Join(",", listPerms.Select(x => POut.Int(x))) + ") "
+ " AND securitylog.LogSource IN (" + string.Join(",", listSources.Select(x => (int)x)) + ") ";
}
//Query
string command = @"SELECT ";
if (sortBy == SortAndFilterBy.SecDateTEntry)
{
command += "appointment.SecDateTEntry,";
}
if (sortBy == SortAndFilterBy.SecurityLogDate)
{
command += "securitylog.LogDateTime,";
}
command +=
@"appointment.AptDateTime,
patient.PatNum,
TRIM(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),CASE WHEN LENGTH(patient.Preferred) > 0 THEN CONCAT(CONCAT('(',patient.Preferred),') ') ELSE '' END),patient.FName), ' '),patient.MiddleI)) PatName,
patient.Birthdate,
appointment.AptDateTime,
LENGTH(appointment.Pattern)*5,
appointment.ProcDescript,
patient.HmPhone,
patient.WkPhone,
patient.WirelessPhone,
COALESCE(clinic.Description,'" + POut.String(Lans.g("formSender", "Unassigned")) + @"') ClinicDesc,
appointment.SecDateTEntry AS 'DateTimeCreated',
appointment.Confirmed,
appointment.Note,
appointment.AptNum
FROM appointment
INNER JOIN patient ON appointment.PatNum=patient.PatNum "
+ innerJoinWebSchedBoth +
@" LEFT JOIN clinic ON appointment.ClinicNum=clinic.ClinicNum
WHERE "
+ whereApptStatus
+ whereProv
+ whereClinics
+ whereConfStatus;
if (sortBy == SortAndFilterBy.SecDateTEntry)
{
command += " " + DbHelper.BetweenDates("appointment.SecDateTEntry", dateStart, dateEnd)
+ " ORDER BY appointment.ClinicNum,appointment.SecDateTEntry,PatName";
}
else if (sortBy == SortAndFilterBy.AptDateTime)
{
command += " appointment.AptDateTime BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd.AddDays(1))
+ " ORDER BY appointment.ClinicNum,appointment.AptDateTime,PatName";
}
else if (sortBy == SortAndFilterBy.SecurityLogDate && listSources.Count > 0)
{
command += " securitylog.LogDateTime BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd.AddDays(1))
+ " ORDER BY securitylog.LogDateTime,appointment.AptDateTime,PatName";
}
DataTable table = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
return(table);
}
