///<summary></summary>
public static void ClearDuplicates()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod());
return;
}
//First, create a temp table with operatories for all blockouts
string command = "DROP TABLE IF EXISTS tempBlockoutOps";
Db.NonQ(command);
command = @"CREATE TABLE tempBlockoutOps
SELECT ScheduleNum,
(SELECT " + DbHelper.GroupConcat("so1.OperatoryNum", false, true) + @" FROM scheduleop so1 WHERE so1.ScheduleNum=schedule.ScheduleNum) AS ops
FROM schedule
WHERE SchedType=2
GROUP BY ScheduleNum" ;
Db.NonQ(command);
command = "ALTER TABLE tempBlockoutOps ADD INDEX (ScheduleNum)";
Db.NonQ(command);
//Get a list of scheduleNums that have duplicates
//A duplicate is defined as a matching opsList and matching times
//The result list will contain one random scheduleNum out of the many duplicates
command = @"SELECT SchedDate,MAX(ScheduleNum),StartTime,StopTime," //MAX on id. Cannot GROUP BY id without splitting up duplicates.
+ @"(SELECT ops FROM tempBlockoutOps WHERE tempBlockoutOps.ScheduleNum=schedule.ScheduleNum) ops_______________ops,
COUNT(*) countDups
FROM schedule
WHERE SchedType=2
GROUP BY SchedDate,ops_______________ops,StartTime,StopTime
HAVING countDups > 1" ;
DataTable table = Db.GetTable(command);
DateTime schedDate;
DateTime startTime;
DateTime stopTime;
string ops;
long scheduleNum;
for (int i = 0; i < table.Rows.Count; i++)
{
schedDate = PIn.Date(table.Rows[i][0].ToString());
scheduleNum = PIn.Long(table.Rows[i][1].ToString());
startTime = PIn.DateT(table.Rows[i][2].ToString());
stopTime = PIn.DateT(table.Rows[i][3].ToString());
ops = PIn.ByteArray(table.Rows[i][4]);
command = "DELETE FROM schedule WHERE "
+ "SchedDate = " + POut.Date(schedDate) + " "
+ "AND ScheduleNum != " + POut.Long(scheduleNum) + " "
+ "AND StartTime = '" + startTime.ToString("hh:mm", new DateTimeFormatInfo()) + ":00' "
+ "AND StopTime = '" + stopTime.ToString("hh:mm", new DateTimeFormatInfo()) + ":00' "
+ "AND (SELECT ops FROM tempBlockoutOps WHERE tempBlockoutOps.ScheduleNum=schedule.ScheduleNum) = '" + POut.String(ops) + "' ";
Db.NonQ(command);
}
command = "DROP TABLE IF EXISTS tempBlockoutOps";
Db.NonQ(command);
//clear all the orphaned scheduleops
command = "DELETE FROM scheduleop WHERE NOT EXISTS(SELECT * FROM schedule WHERE scheduleop.ScheduleNum=schedule.ScheduleNum)";
long result = Db.NonQ(command); //we can test the result in debug
}
/// <summary>Retrieves the Unearned Accounts dataset from the database.</summary>
/// <param name="listClinics">The list of clinics to filter by. Pass in an empty list if this should not be filtered by clinic.</param>
/// <returns></returns>
public static DataTable GetUnearnedAccountData(List <long> listClinics)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinics));
}
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => (!Prefs.GetBoolNoCache(PrefName.EasyNoClinics)));
string command = "";
string whereClin = "";
//This query is kind-of a mess, but we're trying to account for bugs in previous versions.
//Logic is meant to match the account module "Unearned" value logic as closely as possible.
//Cameron and I tried to optimize this code for speed as much as we could in mysql, but filtering by clinic won't make this query much faster.
//The patient table joins are quite slow for large customers, which is why they were moved outside the FROM.
//If a customer complains we might do some logic to get the patnums of any family member in that clinic first, so we can filter down the unions.
if (listClinics.Count > 0)
{
whereClin = "WHERE guar.ClinicNum IN (" + string.Join(",", listClinics) + ") ";
}
command = "SELECT " + DbHelper.Concat("guar.LName", "', '", "guar.FName", "' '", "guar.MiddleI") + ",";
command += DbHelper.GroupConcat("definition.ItemName", true, true, ",");
if (hasClinicsEnabled)
{
command += ",clinic.Description";
}
command += ",SUM(splits.Amt) Amount FROM (";
//Prepay
command += "SELECT paysplit.PatNum, paysplit.SplitAmt Amt,paysplit.UnearnedType "
+ "FROM paysplit "
+ "WHERE paysplit.UnearnedType!=0 AND paysplit.FSplitNum=0 ";
//UNION ALL
command += "UNION ALL ";
//Negative Split
command += "SELECT paysplit.PatNum, SUM(paysplit.SplitAmt) Amt,NULL UnearnedType " //UnearnedType should match prepayment, might be 0
+ "FROM paysplit "
+ "WHERE paysplit.FSplitNum!=0 "
+ "AND paysplit.FSplitNum IN (SELECT paysplit.SplitNum FROM paysplit WHERE paysplit.UnearnedType!=0 AND paysplit.FSplitNum=0) "
+ "GROUP BY paysplit.FSplitNum";
command += ") splits "
+ "INNER JOIN patient ON patient.PatNum=splits.PatNum "
+ "INNER JOIN patient guar ON guar.PatNum=patient.Guarantor "
+ "LEFT JOIN definition ON definition.DefNum=splits.UnearnedType ";
if (hasClinicsEnabled)
{
command += "LEFT JOIN clinic ON clinic.ClinicNum=guar.ClinicNum ";
}
command += whereClin;
command += "GROUP BY guar.PatNum HAVING ABS(Amount) > 0.005 "; //still won't work for oracle
command += "ORDER BY guar.LName, guar.FName, guar.MiddleI, Amount";
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
return(raw);
}
/// <summary>Retrieves the Unearned Accounts dataset from the database.</summary>
/// <param name="listClinics">The list of clinics to filter by. Pass in an empty list if this should not be filtered by clinic.</param>
/// <returns></returns>
public static DataTable GetUnearnedAccountData(List <long> listClinics)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinics));
}
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
List <long> listHiddenUnearnedDefNums = ReportsComplex.RunFuncOnReportServer(() =>
Defs.GetDefsNoCache(DefCat.PaySplitUnearnedType).FindAll(x => !string.IsNullOrEmpty(x.ItemValue)).Select(x => x.DefNum).ToList()
);
string command = "";
string whereClin = "";
//We used to get original paysplits based on FSplitNum being 0 and having an unearned type and then get the offsetting splits from the
//FSplitNum matching the original paysplit num.
//FSplitNums no longer are important when calculating unearned so they are no included in this logic intentionally.
//The patient table joins are quite slow for large customers, which is why they were moved outside the FROM.
//If a customer complains we might do some logic to get the patnums of any family member in that clinic first, so we can filter down the unions.
if (listClinics.Count > 0)
{
whereClin = "WHERE guar.ClinicNum IN (" + string.Join(",", listClinics) + ") ";
}
command = "SELECT " + DbHelper.Concat("guar.LName", "', '", "guar.FName", "' '", "guar.MiddleI") + ",";
command += DbHelper.GroupConcat("definition.ItemName", true, true, ",");
if (hasClinicsEnabled)
{
command += ",clinic.Abbr";
}
command += ",SUM(splits.Amt) Amount FROM (";
//Prepay. Unearned is simply defined as having an unearned type set. Nothing more.
command += "SELECT paysplit.PatNum, paysplit.SplitAmt Amt,paysplit.UnearnedType "
+ "FROM paysplit "
+ "WHERE paysplit.UnearnedType!=0 ";
if (listHiddenUnearnedDefNums.Count > 0)
{
command += $"AND paysplit.UnearnedType NOT IN ({string.Join(",",listHiddenUnearnedDefNums)}) ";
}
command += "GROUP BY paysplit.SplitNum";
command += ") splits "
+ "INNER JOIN patient ON patient.PatNum=splits.PatNum "
+ "INNER JOIN patient guar ON guar.PatNum=patient.Guarantor "
+ "LEFT JOIN definition ON definition.DefNum=splits.UnearnedType ";
if (hasClinicsEnabled)
{
command += "LEFT JOIN clinic ON clinic.ClinicNum=guar.ClinicNum ";
}
command += whereClin;
command += "GROUP BY guar.PatNum HAVING ABS(Amount) > 0.005 "; //still won't work for oracle
command += "ORDER BY guar.LName, guar.FName, guar.MiddleI, Amount";
DataTable raw = ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(command));
return(raw);
}
///<summary>This is only allowed because it's PRIVATE.</summary>
private static List <Schedule> RefreshAndFill(string command)
{
//Not a typical refreshandfill, as it contains a query.
//The GROUP_CONCAT() function returns a comma separated list of items.
//In this case, the ops column is filled with a comma separated list of
//operatories for the corresponding schedule record.
command = "SELECT s.*,"
+ DbHelper.IfNull(DbHelper.CastToChar("(SELECT " + DbHelper.GroupConcat("so.OperatoryNum") +
"FROM scheduleop so " +
"WHERE so.ScheduleNum=s.ScheduleNum " +
"GROUP BY so.ScheduleNum)"), "") + " ops " +
"FROM (" + command + ") s";
DataTable table = Db.GetTable(command);
return(ConvertTableToList(table));
}
public static int GetDuplicateBlockoutCount()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetInt(MethodBase.GetCurrentMethod()));
}
string command = @"SELECT COUNT(*) countDups,SchedDate,MAX(schedule.ScheduleNum) ScheduleNum," //MAX on id as to not change query behavior from prior to MySQL/Oracle independence.
+ @"(SELECT " + DbHelper.GroupConcat("so1.OperatoryNum", false, true) + @" FROM scheduleop so1 WHERE so1.ScheduleNum=schedule.ScheduleNum) AS ops
FROM schedule
WHERE SchedType=2
GROUP BY SchedDate,ops,StartTime,StopTime
HAVING countDups > 1" ;
DataTable table = Db.GetTable(command);
int retVal = 0;
for (int i = 0; i < table.Rows.Count; i++)
{
retVal += PIn.Int(table.Rows[i][0].ToString()) - 1;
}
return(retVal);
}
///<summary>Get unique MedLab orders, grouped by PatNum, ProvNum, and SpecimenID. Also returns the most recent DateTime the results
///were released from the lab and a list of test descriptions ordered. If includeNoPat==true, the lab orders not attached to a patient will be
///included. Filtered by MedLabs for the list of clinics supplied based on the medlab.PatAccountNum=clinic.MedLabAccountNum. ClinicNum 0 will
///be for those medlabs with PatAccountNum that does not match any of the MedLabAccountNums set for a clinic. listSelectedClinics is already
///filtered to only those clinics for which the current user has permission to access based on ClinicIsRestricted. If clinics are not enabled,
///listSelectedClinics will contain 0 and all medlabs will be returned.</summary>
public static List <MedLab> GetOrdersForPatient(Patient pat, bool includeNoPat, bool onlyNoPat, DateTime dateReportedStart, DateTime dateReportedEnd,
List <Clinic> listSelectedClinics)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <MedLab> >(MethodBase.GetCurrentMethod(), pat, includeNoPat, onlyNoPat, dateReportedStart, dateReportedEnd,
listSelectedClinics));
}
//include all patients unless a patient is specified.
string patNumClause = "medlab.PatNum>0";
if (pat != null)
{
patNumClause = "medlab.PatNum=" + POut.Long(pat.PatNum);
}
//do not include patnum=0 unless specified.
if (includeNoPat)
{
patNumClause += " OR medlab.PatNum=0";
}
if (onlyNoPat)
{
patNumClause = "medlab.PatNum=0";
}
List <string> listWhereClauseStrs = new List <string>();
if (PrefC.HasClinicsEnabled)
{
List <string> listAllClinicAcctNums = Clinics.GetWhere(x => !string.IsNullOrWhiteSpace(x.MedLabAccountNum)).Select(x => x.MedLabAccountNum).ToList();
if (listSelectedClinics.Any(x => x.ClinicNum == 0) && listAllClinicAcctNums.Count > 0) //include "Unassigned" medlabs
{
listWhereClauseStrs.Add("medlab.PatAccountNum NOT IN (" + string.Join(",", listAllClinicAcctNums) + ")");
}
listSelectedClinics.RemoveAll(x => x.ClinicNum <= 0 || string.IsNullOrWhiteSpace(x.MedLabAccountNum));
if (listSelectedClinics.Count > 0)
{
listWhereClauseStrs.Add("medlab.PatAccountNum IN (" + string.Join(",", listSelectedClinics.Select(x => x.MedLabAccountNum)) + ")");
}
}
string command = "SELECT MAX(CASE WHEN medlab.DateTimeReported=maxDate.DateTimeReported THEN MedLabNum ELSE 0 END) AS MedLabNum,"
+ "SendingApp,SendingFacility,medlab.PatNum,medlab.ProvNum,PatIDLab,PatIDAlt,PatAge,PatAccountNum,PatFasting,medlab.SpecimenID,"
+ "SpecimenIDFiller,ObsTestID,ObsTestLoinc,ObsTestLoincText,DateTimeCollected,TotalVolume,ActionCode,ClinicalInfo,"
+ "MIN(DateTimeEntered) AS DateTimeEntered,OrderingProvNPI,OrderingProvLocalID,OrderingProvLName,OrderingProvFName,SpecimenIDAlt,"
+ "maxdate.DateTimeReported,MIN(CASE WHEN medlab.DateTimeReported=maxDate.DateTimeReported THEN ResultStatus ELSE NULL END) AS ResultStatus,"
+ "ParentObsID,ParentObsTestID,NotePat,NoteLab,FileName,"
+ "MIN(CASE WHEN medlab.DateTimeReported=maxDate.DateTimeReported THEN OriginalPIDSegment ELSE NULL END) AS OriginalPIDSegment,"
+ DbHelper.GroupConcat("ObsTestDescript", distinct: true, separator: "\r\n") + " AS ObsTestDescript "
+ "FROM medlab "
+ "INNER JOIN ("
+ "SELECT PatNum,ProvNum,SpecimenID,MAX(DateTimeReported) AS DateTimeReported "
+ "FROM medlab "
+ "WHERE (" + patNumClause + ") " //Ex: WHERE (medlab.PatNum>0 OR medlab.Patnum=0)
+ "GROUP BY PatNum,ProvNum,SpecimenID "
+ "HAVING " + DbHelper.DtimeToDate("MAX(DateTimeReported)") + " BETWEEN " + POut.Date(dateReportedStart) + " AND " + POut.Date(dateReportedEnd)
+ ") maxDate ON maxDate.PatNum=medlab.PatNum AND maxDate.ProvNum=medlab.ProvNum AND maxDate.SpecimenID=medlab.SpecimenID ";
if (PrefC.HasClinicsEnabled && listWhereClauseStrs.Count > 0)
{
command += "WHERE (" + string.Join(" OR ", listWhereClauseStrs) + ") ";
}
command += "GROUP BY medlab.PatNum,medlab.ProvNum,medlab.SpecimenID "
+ "ORDER BY maxdate.DateTimeReported DESC,medlab.SpecimenID,MedLabNum"; //most recently received lab on top, with all for a specific specimen together
return(Crud.MedLabCrud.SelectMany(command));
}
