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