///<summary>Check static CacheLock fields and verify that they are still valid given the new filter criteria.
///Returns true if cache needs to be refreshed. Otherwise returns false.</summary>
private bool IsInvalid(DashboardFilter filterIn, out DateTime dateFromOut, out DateTime dateToOut)
{
dateFromOut = DashboardCacheLock <T> .FilterOptions.DateFrom; dateToOut = DashboardCacheLock <T> .FilterOptions.DateTo;
lock (DashboardCacheLock <T> .Lock) {
if (_cacheS == null) //Hasn't run yet so it is invalid.
{
dateFromOut = filterIn.DateFrom;
dateToOut = filterIn.DateTo;
return(true);
}
if (!DashboardCacheLock <T> .FilterOptions.UseDateFilter)
{
//Date filter has previously been turned off, which means we have already gotten the untiltered cache at least once.
//There will be nothing else to get so cache is valid.
return(false);
}
bool ret = false;
if (filterIn.DateFrom < DashboardCacheLock <T> .FilterOptions.DateFrom) //New 'from' is before old 'from', invalidate.
{
dateFromOut = filterIn.DateFrom;
ret = true;
}
if (filterIn.DateTo > DashboardCacheLock <T> .FilterOptions.DateTo) //New 'to' is after old 'to', invalidate.
{
dateToOut = filterIn.DateTo;
ret = true;
}
return(ret);
}
}
protected override string GetCommand(DashboardFilter filter)
{
string where = "";
List <string> listWhereClauses = new List <string>();
if (filter.UseDateFilter)
{
listWhereClauses.Add("AdjDate BETWEEN " + POut.Date(filter.DateFrom) + " AND " + POut.Date(filter.DateTo) + " ");
}
if (filter.UseProvFilter)
{
listWhereClauses.Add("ProvNum=" + POut.Long(filter.ProvNum) + " ");
}
if (listWhereClauses.Count > 0)
{
where = "WHERE " + string.Join("AND ", listWhereClauses);
}
return
("SELECT AdjDate,ProvNum,SUM(AdjAmt) AdjTotal, ClinicNum "
+ "FROM adjustment "
+ where
+ "GROUP BY AdjDate,ProvNum,ClinicNum "
+ "HAVING AdjTotal<>0 "
+ "ORDER BY AdjDate,ProvNum ");
}
protected override List <T> GetCache(DashboardFilter filter)
{
return(DashboardQueries.GetTable(GetCommand(filter))
.AsEnumerable()
.Select(x => GetInstanceFromDataRow(x))
.ToList());
}
///<summary>Fills the instance cache and the static cache (when necessary) using the given criteria. This IS thread-safe.
///If invalidateFirst==true then cache will be invalidated and forcefully refreshed. Use this sparingly.</summary>
public void Run(DashboardFilter filter, bool invalidateFirst)
{
lock (DashboardCacheLock <T> .Lock) {
if (invalidateFirst) //This will cause a force refresh.
{
_cacheS = null;
}
if (!AllowQueryDateFilter())
{
DashboardCacheLock <T> .FilterOptions.UseDateFilter = false;
}
DateTime dateFromOut; DateTime dateToOut;
if (IsInvalid(filter, out dateFromOut, out dateToOut)) //The cache is invalid and should be filled using the new filter options.
{
DashboardCacheLock <T> .FilterOptions.DateFrom = dateFromOut;
DashboardCacheLock <T> .FilterOptions.DateTo = dateToOut;
if (!filter.UseDateFilter && DashboardCacheLock <T> .FilterOptions.UseDateFilter)
{
//Previously we used a data filter but now we are not. Set the flag indicating that we have retrieved the unfiltered cache.
DashboardCacheLock <T> .FilterOptions.UseDateFilter = false;
}
//This is potentially a slow query so set the static cache here.
_cacheS = GetCache(DashboardCacheLock <T> .FilterOptions);
RemoveBadData(_cacheS);
//This is a simple shallow copy so it is safe enough to set _cache inside of this lock. It makes _cache available even when _cacheS is unavailable due to slow query.
_cache = new List <T>(_cacheS);
}
}
}
protected override List <Clinic> GetCache(DashboardFilter filter)
{
List <Clinic> list = Clinics.GetDeepCopy();
_dictClinicNames = list.ToDictionary(x => x.ClinicNum, x => string.IsNullOrEmpty(x.Description) ? x.ClinicNum.ToString() : x.Description);
return(list);
}
protected override string GetCommand(DashboardFilter filter)
{
string where = "";
List <string> listWhereClauses = new List <string>();
if (filter.UseDateFilter)
{
listWhereClauses.Add("DATE(AdjDate) BETWEEN " + POut.Date(filter.DateFrom) + " AND " + POut.Date(filter.DateTo) + " ");
}
if (filter.UseProvFilter)
{
listWhereClauses.Add("ProvNum=" + POut.Long(filter.ProvNum) + " ");
}
if (listWhereClauses.Count > 0)
{
where = "WHERE " + string.Join("AND ", listWhereClauses);
}
return
("SELECT AdjDate,ProvNum,COUNT(AdjNum) AdjCount,ClinicNum,AdjType, SUM(AdjAmt) AdjAmt "
+ "FROM adjustment "
+ "INNER JOIN definition ON definition.DefNum=adjustment.AdjType "
+ "AND definition.ItemValue = '+' "
+ where
+ "GROUP BY AdjDate,ProvNum,ClinicNum,AdjType "
+ "ORDER BY AdjDate,ProvNum,ClinicNum ");
}
protected override List <DashboardAR> GetCache(DashboardFilter filter)
{
DateTime firstOfMonth = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
filter.DateTo = filter.DateTo < firstOfMonth ? filter.DateTo : firstOfMonth;
filter.DateFrom = new DateTime(filter.DateFrom.Year, filter.DateFrom.Month, 1);
return(DashboardQueries.GetAR(filter.DateFrom, filter.DateTo, DashboardARs.Refresh(filter.DateFrom)));
}
protected override List <Provider> GetCache(DashboardFilter filter)
{
List <Provider> list = Providers.GetDeepCopy();
_dictProvNames = list.ToDictionary(x => x.ProvNum, x => string.IsNullOrEmpty(x.Abbr) ? x.ProvNum.ToString() : x.Abbr);
_dictProvColors = list.GroupBy(x => x.Abbr).ToDictionary(x => string.IsNullOrEmpty(x.Key) ? x.First().ProvNum.ToString() : x.Key, x => x.First().ProvColor);
return(list);
}
///<summary>Helper method to convert from json string to ODGraphBaseSettingsAbs. Most importantly, provides filter settings for a given DashboardCell.</summary>
public static ODGraphSettingsBase Deserialize(string json)
{
ODGraphSettingsBase ret = JsonConvert.DeserializeObject <ODGraphSettingsBase>(json);
Cache.DashboardFilter filter = GetDatesFromQuickRange(ret.QuickRangePref, ret.DateFrom, ret.DateTo);
ret.DateFrom = filter.DateFrom;
ret.DateTo = filter.DateTo;
return(ret);
}
protected override string GetCommand(DashboardFilter filter)
{
return
("SELECT PatNum, MIN(ProcDate) FirstProc, ClinicNum, ProvNum "
+ "FROM procedurelog USE INDEX(indexPNPSCN) "
+ "INNER JOIN procedurecode ON procedurecode.CodeNum = procedurelog.CodeNum "
+ "AND procedurecode.ProcCode NOT IN ('D9986','D9987')"
+ "WHERE ProcStatus=" + POut.Int((int)ProcStat.C) + " "
+ "GROUP BY PatNum");
}
protected override List <T> GetCache(DashboardFilter filter)
{
if (filter.UseProvFilter && filter.ProvNum == 0)
{
return(new List <T>());
}
return(DashboardQueries.GetTable(GetCommand(filter), UseReportServer)
.AsEnumerable()
.Select(x => GetInstanceFromDataRow(x))
.ToList());
}
protected override string GetCommand(DashboardFilter filter)
{
string where = "";
if (filter.UseDateFilter)
{
where = "DateCP BETWEEN " + POut.Date(filter.DateFrom) + " AND " + POut.Date(filter.DateTo) + " AND ";
}
return
("SELECT ProvNum,DateCP,SUM(InsPayAmt) AS GrossIncome,ClinicNum "
+ "FROM claimproc "
+ "WHERE " + where + "ClaimPaymentNum<>0 AND InsPayAmt<>0 "
+ "GROUP BY ProvNum,DateCP,ClinicNum ");
}
///<summary>Start a thread dedicated to filling the given cache.</summary>
private static void FillCacheThreaded <T>(DashboardCacheBase <T> cache, DashboardFilter filter, string groupName, bool invalidateFirst)
{
ODThread thread = new ODThread(new ODThread.WorkerDelegate((ODThread th) => {
if (OnSetDb != null)
{
OnSetDb(cache, new EventArgs());
}
cache.Run(filter, invalidateFirst);
}));
thread.GroupName = groupName;
thread.Start(false);
}
protected override string GetCommand(DashboardFilter filter)
{
string where = "WHERE AptStatus=" + (int)ApptStatus.Broken + " ";
if (filter.UseDateFilter)
{
where += "AND DATE(AptDateTime) BETWEEN " + POut.Date(filter.DateFrom) + " AND " + POut.Date(filter.DateTo) + " ";
}
return
("SELECT DATE(AptDateTime) ApptDate,ProvNum,ClinicNum,COUNT(AptNum) ApptCount "
+ "FROM appointment "
+ where
+ "GROUP BY ApptDate,ProvNum,ClinicNum ");
}
protected override string GetCommand(DashboardFilter filter)
{
string where = "";
if (filter.UseDateFilter)
{
where = "DatePay BETWEEN " + POut.Date(filter.DateFrom) + " AND " + POut.Date(filter.DateTo) + " AND ";
}
return
("SELECT ProvNum,DatePay,SUM(SplitAmt) AS GrossSplit,ClinicNum "
+ "FROM paysplit "
+ "WHERE " + where + "IsDiscount=0 "
+ "GROUP BY ProvNum,DatePay,ClinicNum ");
}
protected override string GetCommand(DashboardFilter filter)
{
string where = "";
if (filter.UseDateFilter)
{
where = "WHERE AdjDate BETWEEN " + POut.Date(filter.DateFrom) + " AND " + POut.Date(filter.DateTo) + " ";
}
return
("SELECT AdjDate,ProvNum,SUM(AdjAmt) AdjTotal, ClinicNum "
+ "FROM adjustment "
+ where
+ "GROUP BY AdjDate,ProvNum,ClinicNum "
+ "HAVING AdjTotal<>0 "
+ "ORDER BY AdjDate,ProvNum ");
}
protected override string GetCommand(DashboardFilter filter)
{
string where = "WHERE procedurelog.ProcStatus=" + POut.Int((int)ProcStat.C) + " ";
if (filter.UseDateFilter)
{
where += "AND procedurelog.ProcDate BETWEEN " + POut.Date(filter.DateFrom) + " AND " + POut.Date(filter.DateTo) + " ";
}
return
("SELECT procedurelog.ProcDate,procedurelog.ProvNum,procedurelog.ClinicNum, "
+ "SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) AS GrossProd, "
+ "COUNT(procedurelog.ProcNum) AS ProcCount "
+ "FROM procedurelog "
+ where
+ "GROUP BY procedurelog.ProcDate,procedurelog.ProvNum,procedurelog.ClinicNum ");
}
protected override string GetCommand(DashboardFilter filter)
{
string where = "WHERE ProcStatus=" + (int)ProcStat.C + " ";
if (filter.UseDateFilter)
{
where = "AND DATE(ProcDate) BETWEEN " + POut.Date(filter.DateFrom) + " AND " + POut.Date(filter.DateTo) + " ";
}
return
("SELECT ProcDate,ProvNum,ClinicNum,COUNT(ProcNum) ProcCount, SUM(ProcFee) ProcFee,ProcCode "
+ "FROM procedurelog "
+ "INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum "
+ "AND procedurecode.ProcCode IN('D9986','D9987') "
+ where
+ "GROUP BY ProcDate,ProvNum,ClinicNum,ProcCode ");
}
protected override string GetCommand(DashboardFilter filter)
{
string where = "";
if (filter.UseDateFilter)
{
where = "WHERE DATE(AdjDate) BETWEEN " + POut.Date(filter.DateFrom) + " AND " + POut.Date(filter.DateTo) + " ";
}
return
("SELECT AdjDate,ProvNum,COUNT(AdjNum) AdjCount,ClinicNum,AdjType, SUM(AdjAmt) AdjAmt "
+ "FROM adjustment "
+ "INNER JOIN definition ON definition.DefNum=adjustment.AdjType "
+ "AND definition.ItemValue = '+' "
+ where
+ "GROUP BY AdjDate,ProvNum,ClinicNum,AdjType "
+ "ORDER BY AdjDate,ProvNum,ClinicNum ");
}
protected override string GetCommand(DashboardFilter filter)
{
string where = "ProcStatus=" + POut.Int((int)ProcStat.C);
if (filter.UseProvFilter)
{
where += " AND ProvNum=" + POut.Long(filter.ProvNum);
}
string cmd =
"SELECT PatNum, MIN(ProcDate) FirstProc, ClinicNum, ProvNum "
+ "FROM procedurelog USE INDEX(indexPNPSCN) "
+ "INNER JOIN procedurecode ON procedurecode.CodeNum = procedurelog.CodeNum "
+ "AND procedurecode.ProcCode NOT IN ('D9986','D9987')"
+ "WHERE " + where + " "
+ "GROUP BY PatNum";
return(cmd);
}
private void comboQuickRange_SelectedIndexChanged(object sender, EventArgs e)
{
_allowFilter = false;
if (QuickRangePref == QuickRange.custom)
{
dateTimeTo.Enabled = true;
dateTimeFrom.Enabled = true;
}
else
{
dateTimeTo.Enabled = false;
dateTimeFrom.Enabled = false;
Cache.DashboardFilter filter = Filter;
DateFrom = filter.DateFrom;
DateTo = filter.DateTo;
}
_allowFilter = true;
FilterData(sender, e);
}
protected override string GetCommand(DashboardFilter filter)
{
string where = "WHERE TRUE ";
if (filter.UseDateFilter)
{
where += "AND ProcDate BETWEEN " + POut.Date(filter.DateFrom) + " AND " + POut.Date(filter.DateTo) + " ";
}
return
("SELECT ProcDate,ProvNum,SUM(WriteOff) AS WriteOffs, IF(claimproc.Status=" + (int)ClaimProcStatus.CapComplete + ",'1','0') AS IsCap, ClinicNum "
+ "FROM claimproc "
+ where
+ "AND claimproc.Status IN ("
+ POut.Int((int)ClaimProcStatus.Received) + ","
+ POut.Int((int)ClaimProcStatus.Supplemental) + ","
+ POut.Int((int)ClaimProcStatus.NotReceived) + ","
+ POut.Int((int)ClaimProcStatus.CapComplete) + ") "
+ "GROUP BY ProcDate,ProvNum,(claimproc.Status=" + (int)ClaimProcStatus.CapComplete + "),ClinicNum "
+ "HAVING WriteOffs<>0 ");
}
public void DeserializeFromJson(string json)
{
try {
QuantityOverTimeGraphSettings settings = ODGraphSettingsBase.Deserialize <QuantityOverTimeGraphSettings>(json);
this.QtyType = settings.QtyType;
this.SeriesType = settings.SeriesType;
this.GroupByType = settings.GroupByType;
this.LegendDock = settings.LegendDock;
this.BreakdownPref = settings.BreakdownPref;
this.BreakdownVal = settings.BreakdownVal;
this.GraphTitle = settings.Title;
this.ChartSubTitle = settings.SubTitle;
//Important that quick range is set last to prevent FilterData from firing multiple times.
this.QuickRangePref = settings.QuickRangePref;
//we should always recreate the dates so that they are current.
Cache.DashboardFilter filter = ODGraphSettingsBase.GetDatesFromQuickRange(settings.QuickRangePref, settings.DateFrom, settings.DateTo);
this.DateFrom = filter.DateFrom;
this.DateTo = filter.DateTo;
}
catch (Exception e) {
MessageBox.Show(e.Message);
}
}
///<summary>If quickRange==QuickRange.custom then return a filter containing customDateFrom and customDateTo.
///In all other cases, the date range will be calculated given the quickRange.</summary>
public static Cache.DashboardFilter GetDatesFromQuickRange(Enumerations.QuickRange quickRange, DateTime customDateFrom, DateTime customDateTo)
{
Cache.DashboardFilter filter = new Cache.DashboardFilter();
switch (quickRange)
{
case Enumerations.QuickRange.custom:
filter.DateTo = customDateTo;
filter.DateFrom = customDateFrom;
break;
case Enumerations.QuickRange.last7Days:
filter.DateTo = DateTime.Today;
filter.DateFrom = filter.DateTo.AddDays(-7);
break;
case Enumerations.QuickRange.last30Days:
filter.DateTo = DateTime.Today;
filter.DateFrom = filter.DateTo.AddDays(-30);
break;
case Enumerations.QuickRange.last365Days:
filter.DateTo = DateTime.Today;
filter.DateFrom = filter.DateTo.AddDays(-365);
break;
case Enumerations.QuickRange.last12Months:
filter.DateTo = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1).AddDays(-1);
filter.DateFrom = filter.DateTo.AddMonths(-12).AddDays(1);
break;
case Enumerations.QuickRange.previousWeek:
filter.DateFrom = DateTime.Today.AddDays(-(int)DateTime.Today.DayOfWeek).AddDays(-7);
filter.DateTo = filter.DateFrom.AddDays(7);
break;
case Enumerations.QuickRange.previousMonth:
filter.DateFrom = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1).AddMonths(-1);
filter.DateTo = filter.DateFrom.AddMonths(1);
break;
case Enumerations.QuickRange.previousYear:
filter.DateFrom = new DateTime(DateTime.Today.Year - 1, 1, 1);
filter.DateTo = filter.DateFrom.AddYears(1);
break;
case Enumerations.QuickRange.thisWeek:
filter.DateFrom = DateTime.Today.AddDays(-(int)DateTime.Today.DayOfWeek);
filter.DateTo = filter.DateFrom.AddDays(7);
break;
case Enumerations.QuickRange.thisMonth:
filter.DateFrom = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
filter.DateTo = filter.DateFrom.AddMonths(1);
break;
case Enumerations.QuickRange.thisYear:
filter.DateFrom = new DateTime(DateTime.Today.Year, 1, 1);
filter.DateTo = filter.DateFrom.AddYears(1);
break;
case Enumerations.QuickRange.weekToDate:
filter.DateFrom = DateTime.Today.AddDays(-(int)DateTime.Today.DayOfWeek);
filter.DateTo = DateTime.Today;
break;
case Enumerations.QuickRange.monthToDate:
filter.DateFrom = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
filter.DateTo = DateTime.Today;
break;
case Enumerations.QuickRange.yearToDate:
filter.DateFrom = new DateTime(DateTime.Today.Year, 1, 1);
filter.DateTo = DateTime.Today;
break;
case Enumerations.QuickRange.allTime:
filter.DateFrom = new DateTime(1880, 1, 1);
filter.DateTo = filter.DateFrom.AddYears(300);
filter.UseDateFilter = false;
break;
default:
throw new Exception("Unsupported QuickRange: " + quickRange.ToString());
}
return(filter);
}
protected abstract string GetCommand(DashboardFilter filter);
public virtual List <T> GetCacheUnthreaded(DashboardFilter filter)
{
return(GetCache(filter));
}
protected override List <HQMessage> GetCache(DashboardFilter filter)
{
return(ListHqMessages);
}
///<summary>Concrete implementers must override this and provide the cache given the input criteria.</summary> protected abstract List <T> GetCache(DashboardFilter filter);
///<summary>Refresh the cache(s) associated with the given cellType. If waitToReturn==true then block until finished.
///If waitToReturn==false then runs async and returns immediately. Optionally wait for the method to return or run async.
///onExit event will be fired after async version has completed. Will not fire when sync version is run as this is a blocking call so when it returns it is done.
///If invalidateFirst==true then cache will be invalidated and forcefully refreshed. Use this sparingly.</summary>
public static void RefreshCellTypeIfInvalid(DashboardCellType cellType, DashboardFilter filter, bool waitToReturn, bool invalidateFirst, EventHandler onExit = null)
{
//Create a random group name so we can arbitrarily group and wait on the threads we are about to start.
string groupName = cellType.ToString() + _rand.Next();
try {
switch (cellType)
{
case DashboardCellType.HQPhone:
case DashboardCellType.HQConfirmation:
case DashboardCellType.HQMtMessage:
case DashboardCellType.HQBillingUsageAccess:
case DashboardCellType.HQMoMessage:
case DashboardCellType.HQBillingInboundOutbound:
case DashboardCellType.HQSignups:
//Broadcast monitor does not use the ODGraph cache.
return;
}
//Always fill certain caches first. These will not be threaded as they need to be available to the threads which will run below.
//It doesn't hurt to block momentarily here as the queries will run very quickly.
Providers.Run(new DashboardFilter()
{
UseDateFilter = false
}, invalidateFirst);
Clinics.Run(new DashboardFilter()
{
UseDateFilter = false
}, invalidateFirst);
//Start certain cache threads depending on which cellType we are interested in. Each cache will have its own thread.
switch (cellType)
{
case DashboardCellType.ProductionGraph:
FillCacheThreaded(CompletedProcs, filter, groupName, invalidateFirst);
FillCacheThreaded(Writeoffs, filter, groupName, invalidateFirst);
FillCacheThreaded(Adjustments, filter, groupName, invalidateFirst);
break;
case DashboardCellType.IncomeGraph:
FillCacheThreaded(PaySplits, filter, groupName, invalidateFirst);
FillCacheThreaded(ClaimPayments, filter, groupName, invalidateFirst);
break;
case DashboardCellType.AccountsReceivableGraph:
FillCacheThreaded(AR, filter, groupName, invalidateFirst);
break;
case DashboardCellType.NewPatientsGraph:
FillCacheThreaded(Patients, filter, groupName, invalidateFirst);
break;
case DashboardCellType.BrokenApptGraph:
FillCacheThreaded(BrokenAppts, filter, groupName, invalidateFirst);
FillCacheThreaded(BrokenProcs, filter, groupName, invalidateFirst);
FillCacheThreaded(BrokenAdjs, filter, groupName, invalidateFirst);
break;
case DashboardCellType.NotDefined:
default:
throw new Exception("Unsupported DashboardCellType: " + cellType.ToString());
}
}
finally {
if (waitToReturn) //Block until all threads have completed.
{
ODThread.JoinThreadsByGroupName(System.Threading.Timeout.Infinite, groupName, true);
}
else if (onExit != null) //Exit immediately but fire event later once all threads have completed.
{
ODThread.AddGroupNameExitHandler(groupName, onExit);
}
}
}
