///<summary>Gets a list of all future appointments for a given Operatory. Ordered by dateTime</summary>
public static bool HasFutureApts(long operatoryNum, params ApptStatus[] arrayIgnoreStatuses)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), operatoryNum, arrayIgnoreStatuses));
}
string command = "SELECT COUNT(*) FROM appointment "
+ "WHERE Op = " + POut.Long(operatoryNum) + " ";
if (arrayIgnoreStatuses.Length > 0)
{
command += "AND AptStatus NOT IN (";
for (int i = 0; i < arrayIgnoreStatuses.Length; i++)
{
if (i > 0)
{
command += ",";
}
command += POut.Int((int)arrayIgnoreStatuses[i]);
}
command += ") ";
}
command += "AND AptDateTime > " + DbHelper.Now();
return(PIn.Int(Db.GetScalar(command)) > 0);
}
///<summary>When user clicks on a colored light, they intend to ack it to turn it off. This acks all sigmessages with the specified index.
///This is in case multiple sigmessages have been created from different workstations. This acks them all in one shot.
///Must specify a time because you only want to ack sigmessages earlier than the last time this workstation was refreshed.
///A newer sigmessage would not get acked. If this seems slow, then I will need to check to make sure all these tables are properly indexed.
///Inserts a signal for every SigMessageNum that was updated.</summary>
public static void AckButton(int buttonIndex, DateTime time)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), buttonIndex, time);
return;
}
List <long> listSigMessageNums = new List <long>();
string command = "SELECT DISTINCT sigmessage.SigMessageNum FROM sigmessage "
+ "INNER JOIN sigelementdef ON (sigmessage.SigElementDefNumUser=sigelementdef.SigElementDefNum "
+ "OR sigmessage.SigElementDefNumExtra=sigelementdef.SigElementDefNum "
+ "OR sigmessage.SigElementDefNumMsg=sigelementdef.SigElementDefNum) "
+ "WHERE sigmessage.AckDateTime < " + POut.Date(new DateTime(1880, 1, 1), true) + " "
+ "AND MessageDateTime <= " + POut.DateT(time) + " "
+ "AND sigelementdef.LightRow=" + POut.Long(buttonIndex);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return;
}
listSigMessageNums = table.Select().Select(x => PIn.Long(x["SigMessageNum"].ToString())).ToList();
command = "UPDATE sigmessage SET AckDateTime = " + DbHelper.Now() + " "
+ "WHERE SigMessageNum IN (" + string.Join(",", listSigMessageNums) + ")";
Db.NonQ(command);
listSigMessageNums.ForEach(x => Signalods.SetInvalid(InvalidType.SigMessages, KeyType.SigMessage, x));
}
public static void UpdateHeartBeat(string computerName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), computerName);
return;
}
string command = "UPDATE computer SET LastHeartBeat=" + DbHelper.Now() + " WHERE CompName = '" + POut.String(computerName) + "'";
Db.NonQ(command);
}
///<summary>Use by the proximity sensors in WebcamOD/ProximityOD</summary>
public static void SetProximity(bool isProximal, int extension)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), isProximal, extension);
return;
}
string command = "UPDATE phone SET DateTProximal = " + DbHelper.Now() + ", IsProximal = " + POut.Bool(isProximal)
+ " WHERE Extension = " + POut.Int(extension);
Db.NonQ(command);
}
///<summary>Returns the last known status for the Listener Service.
///Returns Critical if a signal has not been entered in the last 5 minutes.
///Returns Error if there are ANY error signals that have not been processed.</summary>
public static eServiceSignalSeverity GetListenerServiceStatus()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <eServiceSignalSeverity>(MethodBase.GetCurrentMethod()));
}
//Additionally, this query will run a subselect to get the count of all unprocessed errors.
//Running that query as a subselect here simply saves an extra call to the database.
//This subselect should be fine to run here since the query is limited to one result and the count of unprocessed errors should be small.
string command = "SELECT eservicesignal.*," //eservicesignal.* is required because we will manually call TableToList() later.
+ "(SELECT COUNT(*) FROM eservicesignal WHERE Severity=" + POut.Int((int)eServiceSignalSeverity.Error) + " AND IsProcessed=0) PendingErrors, "
+ DbHelper.Now() + " ServerTime "
+ "FROM eservicesignal WHERE ServiceCode=" + POut.Int((int)eServiceCode.ListenerService) + " "
+ "AND Severity IN(" + POut.Int((int)eServiceSignalSeverity.NotEnabled) + ","
+ POut.Int((int)eServiceSignalSeverity.Working) + ","
+ POut.Int((int)eServiceSignalSeverity.Error) + ","
+ POut.Int((int)eServiceSignalSeverity.Critical) + ") "
+ "ORDER BY SigDateTime DESC, Severity DESC ";
command = DbHelper.LimitOrderBy(command, 1);
DataTable table = Db.GetTable(command);
List <EServiceSignal> listSignal = Crud.EServiceSignalCrud.TableToList(table);
if (listSignal.Count == 0) //No signals means the eConnector has never run. Nothing to report.
{
return(eServiceSignalSeverity.None);
}
if (listSignal[0].Severity == eServiceSignalSeverity.NotEnabled) //NotEnabled means they don't care what the status is. Nothing to report.
{
return(eServiceSignalSeverity.NotEnabled);
}
DateTime dtNow = PIn.DateT(table.Rows[0]["ServerTime"].ToString());
if (
//eConnector exited gracefully and inserted its own critical signal.
listSignal[0].Severity == eServiceSignalSeverity.Critical ||
//eConnector did not exit gracefully but has not inserted a heartbeat in at least 6 minutes. It is considered critical.
//Listener is dropping a heartbeat every 5 minutes, so give 1 minute grace period to squelch race condition.
listSignal[0].SigDateTime < dtNow.AddMinutes(-6))
{
return(eServiceSignalSeverity.Critical);
}
//We need to flag the service monitor as Error if there are ANY pending errors.
if (table.Rows[0]["PendingErrors"].ToString() != "0")
{
return(eServiceSignalSeverity.Error);
}
return(listSignal[0].Severity);
}
/// <summary>When starting up, in an attempt to be fast, it will not add a new computer to the list.</summary>
public static void UpdateHeartBeat(string computerName, bool isStartup)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), computerName, isStartup);
return;
}
if (!isStartup && list == null)
{
RefreshCache(); //adds new computer to list
}
string command = "UPDATE computer SET LastHeartBeat=" + DbHelper.Now() + " WHERE CompName = '" + POut.String(computerName) + "'";
Db.NonQ(command);
}
///<summary>Updates the employee's ClockStatus if necessary based on their clock events. This method handles future clock events as having
///already occurred. Ex: If I clock out for home at 6:00 but edit my time card to say 7:00, at 6:30 my status will say Home.</summary>
public static void UpdateClockStatus(long employeeNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), employeeNum);
return;
}
//Get the last clockevent for the employee.
string command = @"SELECT * FROM clockevent
WHERE TimeDisplayed2<=" + DbHelper.Now() + " AND TimeDisplayed1<=" + DbHelper.Now() + @"
AND EmployeeNum=" + POut.Long(employeeNum) + @"
ORDER BY IF(YEAR(TimeDisplayed2) < 1880,TimeDisplayed1,TimeDisplayed2) DESC" ;
command = DbHelper.LimitOrderBy(command, 1);
ClockEvent clockEvent = Crud.ClockEventCrud.SelectOne(command);
Employee employee = GetEmp(employeeNum);
Employee employeeOld = employee.Copy();
if (clockEvent == null || //Employee has never clocked in
(clockEvent.TimeDisplayed2.Year > 1880 && clockEvent.ClockStatus == TimeClockStatus.Home)) //Clocked out for home
{
employee.ClockStatus = Lans.g("enumTimeClockStatus", TimeClockStatus.Home.ToString());
}
else if (clockEvent.TimeDisplayed2.Year > 1880 && clockEvent.ClockStatus == TimeClockStatus.Lunch) //Clocked out for lunch
{
employee.ClockStatus = Lans.g("enumTimeClockStatus", TimeClockStatus.Lunch.ToString());
}
else if (clockEvent.TimeDisplayed1.Year > 1880 && clockEvent.TimeDisplayed2.Year < 1880 && clockEvent.ClockStatus == TimeClockStatus.Break)
{
employee.ClockStatus = Lans.g("enumTimeClockStatus", TimeClockStatus.Break.ToString());
}
else if (clockEvent.TimeDisplayed2.Year > 1880 && clockEvent.ClockStatus == TimeClockStatus.Break) //Clocked back in from break
{
employee.ClockStatus = Lans.g("ContrStaff", "Working");
}
else //The employee has not clocked out yet.
{
employee.ClockStatus = Lans.g("ContrStaff", "Working");
}
Crud.EmployeeCrud.Update(employee, employeeOld);
}
/// <summary>When starting up, in an attempt to be fast, it will not add a new computer to the list.</summary>
public static void UpdateHeartBeat(string computerName, bool isStartup)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), computerName, isStartup);
return;
}
string command;
if (!isStartup)
{
if (_computerCache.ListIsNull())
{
RefreshCache(); //adds new computer to list
}
command = "SELECT LastHeartBeat<" + DbHelper.DateAddMinute(DbHelper.Now(), "-3") + " FROM computer WHERE CompName='" + POut.String(computerName) + "'";
if (!PIn.Bool(Db.GetScalar(command))) //no need to update if LastHeartBeat is already within the last 3 mins
{
return; //remote app servers with multiple connections would fight over the lock on a single row to update the heartbeat unnecessarily
}
}
command = "UPDATE computer SET LastHeartBeat=" + DbHelper.Now() + " WHERE CompName = '" + POut.String(computerName) + "'";
Db.NonQ(command);
}
///<Summary></Summary>
public static DataTable GetAllWithoutCharges()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod()));
}
DataTable table = new DataTable();
table.Columns.Add("dateStop");
table.Columns.Add("family");
table.Columns.Add("PatNum");
table.Columns.Add("RegKey");
string command = @"
DROP TABLE IF EXISTS tempRegKeys;
CREATE TABLE tempRegKeys(
tempRegKeyId int auto_increment NOT NULL,
PatNum bigint NOT NULL,
RegKey VARCHAR(255) NOT NULL,
IsMissing tinyint NOT NULL,
Date_ DATE NOT NULL DEFAULT '0001-01-01',
PRIMARY KEY(tempRegKeyId),
KEY(PatNum));
/*Fill table with patnums for all guarantors of regkeys that are still active.*/
INSERT INTO tempRegKeys (PatNum,RegKey,Date_)
SELECT patient.Guarantor,RegKey,'0001-01-01'
FROM registrationkey
LEFT JOIN patient ON registrationkey.PatNum=patient.PatNum
WHERE DateDisabled='0001-01-01'
AND DateEnded='0001-01-01'
AND IsFreeVersion=0
AND IsOnlyForTesting=0;
/*Set indicators on keys with missing repeatcharges*/
UPDATE tempRegKeys
SET IsMissing=1
WHERE NOT EXISTS(SELECT * FROM repeatcharge WHERE repeatcharge.PatNum=tempRegKeys.PatNum);
/*Now, look for expired repeating charges. This is done in two steps.*/
/*Step 1: Mark all keys that have expired repeating charges.*/
/*Step 2: Then, remove those markings for all keys that also have unexpired repeating charges.*/
UPDATE tempRegKeys
SET Date_=(
SELECT IFNULL(MAX(DateStop),'0001-01-01')
FROM repeatcharge
WHERE repeatcharge.PatNum=tempRegKeys.PatNum
AND DateStop < " + DbHelper.Now() + @" AND DateStop > '0001-01-01');
/*Step 2:*/
UPDATE tempRegKeys
SET Date_='0001-01-01'
WHERE EXISTS(
SELECT * FROM repeatcharge
WHERE repeatcharge.PatNum=tempRegKeys.PatNum
AND DateStop = '0001-01-01');
SELECT LName,FName,tempRegKeys.PatNum,tempRegKeys.RegKey,IsMissing,Date_
FROM tempRegKeys
LEFT JOIN patient ON patient.PatNum=tempRegKeys.PatNum
WHERE IsMissing=1
OR Date_ > '0001-01-01'
ORDER BY tempRegKeys.PatNum;
DROP TABLE IF EXISTS tempRegKeys;" ;
DataTable raw = Db.GetTable(command);
DataRow row;
DateTime dateRepeatStop;
for (int i = 0; i < raw.Rows.Count; i++)
{
row = table.NewRow();
if (raw.Rows[i]["IsMissing"].ToString() == "1")
{
row["dateStop"] = "Missing Repeat Charge";
}
else
{
row["dateStop"] = "";
}
dateRepeatStop = PIn.Date(raw.Rows[i]["Date_"].ToString());
if (dateRepeatStop.Year > 1880)
{
if (row["dateStop"].ToString() != "")
{
row["dateStop"] += "\r\n";
}
row["dateStop"] += "Expired Repeat Charge:" + dateRepeatStop.ToShortDateString();
}
row["family"] = raw.Rows[i]["LName"].ToString() + ", " + raw.Rows[i]["FName"].ToString();
row["PatNum"] = raw.Rows[i]["PatNum"].ToString();
row["RegKey"] = raw.Rows[i]["RegKey"].ToString();
table.Rows.Add(row);
}
return(table);
}
///<summary>Gets all task lists for the trunk of the user tab.</summary>
public static List <TaskList> RefreshUserTrunk(long userNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TaskList> >(MethodBase.GetCurrentMethod(), userNum));
}
string command = @"SELECT tasklist.*,COALESCE(unreadtasks.Count,0) 'NewTaskCount',t2.Descript 'ParentDesc1',t3.Descript 'ParentDesc2'
FROM tasklist
LEFT JOIN tasksubscription ON tasksubscription.TaskListNum=tasklist.TaskListNum
LEFT JOIN tasklist t2 ON t2.TaskListNum=tasklist.Parent
LEFT JOIN tasklist t3 ON t3.TaskListNum=t2.Parent
LEFT JOIN (
SELECT taskancestor.TaskListNum,COUNT(*) 'Count'
FROM taskancestor
INNER JOIN task ON task.TaskNum=taskancestor.TaskNum
AND NOT(COALESCE(task.ReminderGroupId,'') != '' AND task.DateTimeEntry > " + DbHelper.Now() + ") ";//no future reminders
if (PrefC.GetBool(PrefName.TasksNewTrackedByUser))
{
command += @"
INNER JOIN taskunread ON taskunread.TaskNum=task.TaskNum
WHERE taskunread.UserNum = " + POut.Long(userNum) + @"
AND task.TaskStatus!=" + POut.Int((int)TaskStatusEnum.Done);
}
else
{
command += @"
WHERE task.TaskStatus=" + POut.Int((int)TaskStatusEnum.New);
}
command += @"
GROUP BY taskancestor.TaskListNum) unreadtasks ON unreadtasks.TaskListNum = tasklist.TaskListNum
WHERE tasksubscription.UserNum=" + POut.Long(userNum) + @"
AND tasksubscription.TaskListNum!=0
ORDER BY tasklist.Descript,tasklist.DateTimeEntry" ;
return(TableToList(Db.GetTable(command)));
}
///<summary>Gets all task lists for the main trunk. Pass in the current user.</summary>
public static List <TaskList> RefreshMainTrunk(long userNum, TaskType taskType)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TaskList> >(MethodBase.GetCurrentMethod(), userNum, taskType));
}
string command = @"SELECT tasklist.*,COALESCE(unreadtasks.Count,0) 'NewTaskCount'
FROM tasklist
LEFT JOIN (SELECT tasklist.TaskListNum,COUNT(*) Count
FROM tasklist
INNER JOIN taskancestor ON taskancestor.TaskListNum = tasklist.TaskListNum
INNER JOIN task ON task.TaskNum = taskancestor.TaskNum " ;
if (taskType == TaskType.Reminder)
{
command += "AND COALESCE(task.ReminderGroupId,'') != '' "; //reminders only
}
else if (taskType == TaskType.Normal)
{
command += "AND NOT(COALESCE(task.ReminderGroupId,'') != '' AND task.DateTimeEntry > " + DbHelper.Now() + ") "; //no future reminders
}
else
{
//No filter.
}
if (PrefC.GetBool(PrefName.TasksNewTrackedByUser))
{
command += "AND task.TaskStatus!=" + POut.Int((int)TaskStatusEnum.Done) + " ";
command += @"
LEFT JOIN (
SELECT TaskListInBox,UserNum
FROM userod
GROUP BY TaskListInBox
) usr ON usr.TaskListInBox = tasklist.TaskListNum
INNER JOIN (
SELECT TaskNum,UserNum
FROM taskunread
) isUnread ON isUnread.TaskNum = task.TaskNum AND (CASE WHEN usr.UserNum IS NOT NULL THEN isUnread.UserNum=usr.UserNum ELSE isUnread.UserNum = " + POut.Long(userNum) + @" END)
" ;
}
else
{
command += "AND task.TaskStatus=0 ";
}
command += "GROUP BY tasklist.TaskListNum) unreadtasks ON unreadtasks.TaskListNum=tasklist.TaskListNum "
+ "WHERE Parent=0 "
+ "AND DateTL < " + POut.Date(new DateTime(1880, 01, 01)) + " "
+ "AND IsRepeating=0 "
+ "ORDER BY tasklist.Descript,tasklist.DateTimeEntry";
return(TableToList(Db.GetTable(command)));
}
private static List <Appointment> GetAppointmentsToSendReview(ReviewInvitationTrigger trigger, long programNum, bool isNewPatient)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Appointment> >(MethodBase.GetCurrentMethod(), trigger, programNum, isNewPatient));
}
string minutesToWaitCompleted = ProgramProperties.GetPropVal(programNum, PropertyDescs.ApptSetCompletedMinutes);
string minutesToWaitTimeArrived = ProgramProperties.GetPropVal(programNum, PropertyDescs.ApptTimeArrivedMinutes);
string minutesToWaitTimeDismissed = ProgramProperties.GetPropVal(programNum, PropertyDescs.ApptTimeDismissedMinutes);
string command = "SELECT * "
+ "FROM appointment "
+ "LEFT JOIN securitylog ON securitylog.FKey=appointment.AptNum "
+ "AND securitylog.PermType=" + POut.Int((int)Permissions.AppointmentEdit) + " AND securitylog.LogText LIKE '%Set Complete%' "
+ "LEFT JOIN commlog ON commlog.PatNum=appointment.PatNum "
+ "AND commlog.CommSource=" + POut.Int((int)CommItemSource.ProgramLink) + " "
+ "AND DATE(commlog.DateTimeEnd)=" + DbHelper.Curdate() + " "
+ "AND commlog.ProgramNum=" + POut.Long(programNum) + " "
+ "WHERE ISNULL(commlog.PatNum) AND appointment.AptDateTime BETWEEN " + DbHelper.Curdate() + " AND " + DbHelper.Now() + " + INTERVAL 1 HOUR "//Hard code an hour to allow for appointments that have an early DateTimeArrived
+ "AND appointment.IsNewPatient=" + POut.Bool(isNewPatient) + " ";
if (trigger == ReviewInvitationTrigger.AppointmentCompleted)
{
command += "AND appointment.AptStatus=" + POut.Int((int)ApptStatus.Complete) + " "
+ "AND NOT ISNULL(securitylog.PatNum) "
+ "AND securitylog.LogDateTime + INTERVAL " + minutesToWaitCompleted + " MINUTE <=" + DbHelper.Now() + " ";
}
else if (trigger == ReviewInvitationTrigger.AppointmentTimeArrived)
{
command += "AND appointment.AptStatus IN (" + POut.Int((int)ApptStatus.Scheduled) + "," + POut.Int((int)ApptStatus.Complete) + ") "
+ "AND ((appointment.AptStatus=" + POut.Int((int)ApptStatus.Complete) + " AND NOT ISNULL(securitylog.PatNum) AND securitylog.LogDateTime + INTERVAL " + minutesToWaitCompleted + " MINUTE <=" + DbHelper.Now() + ") "
+ "OR (appointment.DateTimeArrived>" + DbHelper.Curdate() + " AND appointment.DateTimeArrived + INTERVAL " + minutesToWaitTimeArrived + " MINUTE<=" + DbHelper.Now() + ")) ";
}
else if (trigger == ReviewInvitationTrigger.AppointmentTimeDismissed)
{
command += "AND appointment.AptStatus IN (" + POut.Int((int)ApptStatus.Scheduled) + "," + POut.Int((int)ApptStatus.Complete) + ") "
+ "AND ((appointment.AptStatus=" + POut.Int((int)ApptStatus.Complete) + " AND NOT ISNULL(securitylog.PatNum) AND securitylog.LogDateTime + INTERVAL 90 MINUTE <=" + DbHelper.Now() + ") "
+ "OR (appointment.DateTimeDismissed>" + DbHelper.Curdate() + " AND appointment.DateTimeDismissed + INTERVAL " + minutesToWaitTimeDismissed + " MINUTE<=" + DbHelper.Now() + ")) ";
}
return(Crud.AppointmentCrud.SelectMany(command));
}
///<summary>0 is not allowed, because that would be a trunk. Pass in the current user. Also, if this is in someone's inbox, then pass in the userNum whose inbox it is in. If not in an inbox, pass in 0.</summary>
public static List <TaskList> RefreshChildren(long parent, long userNum, long userNumInbox, TaskType taskType)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TaskList> >(MethodBase.GetCurrentMethod(), parent, userNum, userNumInbox, taskType));
}
string command =
"SELECT tasklist.*,"
+ "(SELECT COUNT(*) FROM taskancestor,task WHERE taskancestor.TaskListNum=tasklist.TaskListNum "
+ "AND task.TaskNum=taskancestor.TaskNum ";
if (taskType == TaskType.Reminder)
{
command += "AND COALESCE(task.ReminderGroupId,'') != '' "; //reminders only
}
else if (taskType == TaskType.Normal)
{
command += "AND NOT(COALESCE(task.ReminderGroupId,'') != '' AND task.DateTimeEntry > " + DbHelper.Now() + ") "; //no future reminders
}
else
{
//No filter.
}
if (PrefC.GetBool(PrefName.TasksNewTrackedByUser))
{
command += "AND EXISTS(SELECT * FROM taskunread WHERE taskunread.TaskNum=task.TaskNum ";
//if a list is someone's inbox,
if (userNumInbox > 0)
{
//then restrict by that user
command += "AND taskunread.UserNum=" + POut.Long(userNumInbox) + ") ";
}
else
{
//otherwise, restrict by current user
command += "AND taskunread.UserNum=" + POut.Long(userNum) + ") ";
}
}
else
{
command += "AND task.TaskStatus=" + POut.Int((int)TaskStatusEnum.New);
}
command += ") NewTaskCount "
+ "FROM tasklist "
+ "WHERE Parent=" + POut.Long(parent) + " "
+ "ORDER BY tasklist.Descript,tasklist.DateTimeEntry";
return(TableToList(Db.GetTable(command)));
}
public static DataTable GetLetterMergeInfo(Patient PatCur, LetterMerge letter)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), PatCur, letter));
}
//jsparks- This is messy and prone to bugs. It needs to be reworked to work just like
//in SheetFiller.FillFieldsInStaticText. Just grab a bunch of separate objects
//instead of one result row.
string command;
//We need a very small table that tells us which tp is the most recent.
//command="DROP TABLE IF EXISTS temptp;";
//Db.NonQ(command);
//command=@"CREATE TABLE temptp(
// DateTP date NOT NULL default '0001-01-01')";
//Db.NonQ(command);
//command+=@"CREATE TABLE temptp
// SELECT MAX(treatplan.DateTP) DateTP
// FROM treatplan
// WHERE PatNum="+POut.PInt(PatCur.PatNum)+";";
//Db.NonQ(command);
command = "SET @maxTpDate=(SELECT MAX(treatplan.DateTP) FROM treatplan WHERE PatNum=" + POut.Long(PatCur.PatNum) + ");";
command += "SELECT ";
for (int i = 0; i < letter.Fields.Count; i++)
{
if (i > 0)
{
command += ",";
}
if (letter.Fields[i] == "NextAptNum")
{
command += "MAX(plannedappt.AptNum) NextAptNum";
}
//other:
else if (letter.Fields[i] == "TPResponsPartyNameFL")
{
command += DbHelper.Concat("MAX(patResp.FName)", "' '", "MAX(patResp.LName)") + " TPResponsPartyNameFL";
}
else if (letter.Fields[i] == "TPResponsPartyAddress")
{
command += "MAX(patResp.Address) TPResponsPartyAddress";
}
else if (letter.Fields[i] == "TPResponsPartyCityStZip")
{
command += DbHelper.Concat("MAX(patResp.City)", "', '", "MAX(patResp.State)", "' '", "MAX(patResp.Zip)") + " TPResponsPartyCityStZip";
}
else if (letter.Fields[i] == "SiteDescription")
{
command += "MAX(site.Description) SiteDescription";
}
else if (letter.Fields[i] == "DateOfLastSavedTP")
{
command += DbHelper.DateColumn("MAX(treatplan.DateTP)") + " DateOfLastSavedTP";
}
else if (letter.Fields[i] == "DateRecallDue")
{
command += "MAX(recall.DateDue) DateRecallDue";
}
else if (letter.Fields[i] == "CarrierName")
{
command += "MAX(CarrierName) CarrierName";
}
else if (letter.Fields[i] == "CarrierAddress")
{
command += "MAX(carrier.Address) CarrierAddress";
}
else if (letter.Fields[i] == "CarrierCityStZip")
{
command += DbHelper.Concat("MAX(carrier.City)", "', '", "MAX(carrier.State)", "' '", "MAX(carrier.Zip)") + " CarrierCityStZip";
}
else if (letter.Fields[i] == "SubscriberNameFL")
{
command += DbHelper.Concat("MAX(patSubsc.FName)", "' '", "MAX(patSubsc.LName)") + " SubscriberNameFL";
}
else if (letter.Fields[i] == "SubscriberID")
{
command += "MAX(inssub.SubscriberID) SubscriberID";
}
else if (letter.Fields[i] == "NextSchedAppt")
{
command += "MIN(appointment.AptDateTime) NextSchedAppt";
}
else if (letter.Fields[i] == "Age")
{
command += "MAX(patient.Birthdate) BirthdateForAge";
}
else if (letter.Fields[i] == "Guarantor")
{
command += DbHelper.Concat("MAX(patGuar.FName)", "' '", "MAX(patGuar.LName)") + " Guarantor";
}
else if (letter.Fields[i] == "GradeSchool")
{
command += "MAX(site.Description) GradeSchool";
}
else if (letter.Fields[i].StartsWith("referral."))
{
command += "MAX(referral." + letter.Fields[i].Substring(9) + ") " + letter.Fields[i].Substring(9);
}
else
{
command += "MAX(patient." + letter.Fields[i] + ") " + letter.Fields[i];
}
}
command += " FROM patient "
+ "LEFT JOIN refattach ON patient.PatNum=refattach.PatNum AND refattach.IsFrom=1 "
+ "LEFT JOIN referral ON refattach.ReferralNum=referral.ReferralNum "
+ "LEFT JOIN plannedappt ON plannedappt.PatNum=patient.PatNum AND plannedappt.ItemOrder=1 "
+ "LEFT JOIN site ON patient.SiteNum=site.SiteNum "
+ "LEFT JOIN treatplan ON patient.PatNum=treatplan.PatNum AND DateTP=@maxTpDate "
+ "LEFT JOIN patient patResp ON treatplan.ResponsParty=patResp.PatNum "
+ "LEFT JOIN recall ON recall.PatNum=patient.PatNum "
+ "AND (recall.RecallTypeNum=" + POut.Long(PrefC.GetLong(PrefName.RecallTypeSpecialProphy))
+ " OR recall.RecallTypeNum=" + POut.Long(PrefC.GetLong(PrefName.RecallTypeSpecialPerio)) + ") "
+ "LEFT JOIN patplan ON patplan.PatNum=patient.PatNum AND Ordinal=1 "
+ "LEFT JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum "
+ "LEFT JOIN insplan ON inssub.PlanNum=insplan.PlanNum "
+ "LEFT JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum "
+ "LEFT JOIN patient patSubsc ON patSubsc.PatNum=inssub.Subscriber "
+ "LEFT JOIN appointment ON appointment.PatNum=patient.PatNum "
+ "AND AptStatus=" + POut.Long((int)ApptStatus.Scheduled) + " "
+ "AND AptDateTime > " + DbHelper.Now() + " "
+ "LEFT JOIN patient patGuar ON patGuar.PatNum=patient.Guarantor "
+ "WHERE patient.PatNum=" + POut.Long(PatCur.PatNum)
+ " GROUP BY patient.PatNum "
+ "ORDER BY refattach.ItemOrder";
return(Db.GetTable(command));
}
///<summary>Removes the providers from the future schedule. Currently called from DBM to clean up hidden providers still on the schedule.</summary>
public static void RemoveProvsFromFutureSchedule(List <long> provNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), provNums);
return;
}
string provs = "";
for (int i = 0; i < provNums.Count; i++)
{
if (provNums[i] < 1) //Invalid provNum, nothing to do.
{
continue;
}
if (i > 0)
{
provs += ",";
}
provs += provNums[i].ToString();
}
if (provs == "") //No valid provNums were passed in. Simply return.
{
return;
}
string command = "DELETE FROM schedule WHERE ProvNum IN (" + provs + ") AND SchedDate > " + DbHelper.Now();
Db.NonQ(command);
}
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></summary>
private static void InsertRow(long patNum)
{
//No need to check RemotingRole; Private static method.
//Random keys not necessary to check because of 1:1 patNum.
//However, this is a lazy insert, so multiple locations might attempt it.
//Just in case, we will have it fail silently.
try {
string command = "INSERT INTO patientnote (PatNum,SecDateTEntry) VALUES('" + patNum + "'," + DbHelper.Now() + ")";
if (DataConnection.DBtype == DatabaseType.MySql)
{
//We may need to do this in Oracle in the future as well.
//If using Replication, then we need to watch for duplicate errors, because the insert is lazy.
//Replication servers can insert a patient note with a primary key belonging to another replication server's key range.
command += " ON DUPLICATE KEY UPDATE PatNum='" + patNum + "'";
}
Db.NonQ(command);
}
catch {
//Fail Silently.
}
}
///<summary>0 is not allowed, because that would be a trunk. Pass in the current user. Also, if this is in someone's inbox, then pass in the
///userNum whose inbox it is in. If not in an inbox, pass in 0. filterClinicFkey and filterRegionFkey are only used for NewTaskCount and do
///not affect which TaskLists are returned by this method. Pass filterClinicFkey=0 and filterRegionFkey=0 to intentionally bypass filtering.
///</summary>
public static List <TaskList> RefreshChildren(long parent, long userNum, long userNumInbox, TaskType taskType, long filterClinicFkey = 0
, long filterRegionFkey = 0)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TaskList> >(MethodBase.GetCurrentMethod(), parent, userNum, userNumInbox, taskType, filterClinicFkey, filterRegionFkey));
}
string command =
"SELECT tasklist.*,"
+ "(SELECT COUNT(*) FROM taskancestor INNER JOIN task ON task.TaskNum=taskancestor.TaskNum ";
command += BuildFilterJoins(filterClinicFkey);
command += "WHERE taskancestor.TaskListNum=tasklist.TaskListNum ";
if (taskType == TaskType.Reminder)
{
command += "AND COALESCE(task.ReminderGroupId,'') != '' "; //reminders only
}
else if (taskType == TaskType.Normal)
{
command += "AND NOT(COALESCE(task.ReminderGroupId,'') != '' AND task.DateTimeEntry > " + DbHelper.Now() + ") "; //no future reminders
}
else
{
//No filter.
}
if (PrefC.GetBool(PrefName.TasksNewTrackedByUser))
{
command += "AND EXISTS(SELECT * FROM taskunread WHERE taskunread.TaskNum=task.TaskNum ";
//If a task is marked done, we don't care if it is unread. Usually if a task is done all the taskunreads will be cleared.
//Added this for an HQ issue where tasklists always showed you had an unread task, even though there were not any open unread tasks.
command += "AND task.TaskStatus!=" + POut.Int((int)TaskStatusEnum.Done) + " ";
//if a list is someone's inbox,
if (userNumInbox > 0)
{
//then restrict by that user
command += "AND taskunread.UserNum=" + POut.Long(userNumInbox) + ") ";
}
else
{
//otherwise, restrict by current user
command += "AND taskunread.UserNum=" + POut.Long(userNum) + ") ";
}
}
else
{
command += "AND task.TaskStatus=" + POut.Int((int)TaskStatusEnum.New);
}
command += BuildFilterWhereClause(userNum, filterClinicFkey, filterRegionFkey);
command += ") NewTaskCount "
+ "FROM tasklist "
+ "WHERE Parent=" + POut.Long(parent) + " "
+ "ORDER BY tasklist.Descript,tasklist.DateTimeEntry";
return(TableToList(Db.GetTable(command)));
}
///<summary>Gets all Signals since a given DateTime. If it can't connect to the database, then it returns a list of length 0.
///Remeber that the supplied dateTime is server time. This has to be accounted for.</summary>
public static List <Signalod> RefreshTimed(DateTime sinceDateT)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Signalod> >(MethodBase.GetCurrentMethod(), sinceDateT));
}
//This command was written to take into account the fact that MySQL truncates seconds to the the whole second on DateTime columns. (newer versions support fractional seconds)
//By selecting signals less than Now() we avoid missing signals the next time this function is called. Without the addition of Now() it was possible
//to miss up to ((N-1)/N)% of the signals generated in the worst case scenario.
string command = "SELECT * FROM signalod "
+ "WHERE (SigDateTime>" + POut.DateT(sinceDateT) + " AND SigDateTime< " + DbHelper.Now() + ") "
+ "ORDER BY SigDateTime";
//note: this might return an occasional row that has both times newer.
List <Signalod> listSignals = new List <Signalod>();
try {
listSignals = Crud.SignalodCrud.SelectMany(command);
}
catch {
//we don't want an error message to show, because that can cause a cascade of a large number of error messages.
}
return(listSignals);
}
///<summary>Gets the masked CC# and exp date for all cards setup for monthly charges for the specified patient. Only used for filling [CreditCardsOnFile] variable when emailing statements.</summary>
public static string GetMonthlyCardsOnFile(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetString(MethodBase.GetCurrentMethod(), patNum));
}
string result = "";
string command = "SELECT * FROM creditcard WHERE PatNum=" + POut.Long(patNum)
+ " AND (" + DbHelper.Year("DateStop") + "<1880 OR DateStop>" + DbHelper.Now() + ") "//Recurring card is active.
+ " AND ChargeAmt>0";
List <CreditCard> monthlyCards = Crud.CreditCardCrud.SelectMany(command);
for (int i = 0; i < monthlyCards.Count; i++)
{
if (i > 0)
{
result += ", ";
}
result += monthlyCards[i].CCNumberMasked + " exp:" + monthlyCards[i].CCExpiration.ToString("MM/yy");
}
return(result);
}