///<summary>Builds WHERE clauses appropriate to the type of GlobalFilterType. Returns empty string if not filtering. Pass filterClinicFkey=0
///and filterRegionFkey=0 to intentionally bypass filtering.</summary>
public static string BuildFilterWhereClause(long currentUserNum, long filterClinicFkey, long filterRegionFkey)
{
string command = string.Empty;
//Only add WHERE clauses if filtering. Filtering will never happen if clinics are turned off, because regions link via clinics.
if ((GlobalTaskFilterType)PrefC.GetInt(PrefName.TasksGlobalFilterType) == GlobalTaskFilterType.Disabled ||
(filterClinicFkey == 0 && filterRegionFkey == 0) || !PrefC.HasClinicsEnabled)
{
return(command);
}
List <Clinic> listUnrestrictedClinics = Clinics.GetAllForUserod(Userods.GetUser(currentUserNum));
List <long> listClinicNums = new List <long>()
{
0
}; //All users can see Tasks associated to HQ clinic or "0" region.
List <long> listClinicNumsInRegion = new List <long>()
{
0
}; //All users can see Tasks associated to HQ clinic or "0" region.
List <long> listUnrestrictedClinicNums = listUnrestrictedClinics.Select(x => x.ClinicNum).ToList(); //User can view these clinicnums.
List <long> listUnrestrictedClinicNumsInRegion = listUnrestrictedClinics.FindAll(x => x.Region == filterRegionFkey).Select(x => x.ClinicNum).ToList();
if (filterClinicFkey.In(listUnrestrictedClinicNums)) //Make sure user is not restricted for this clinic.
{
listClinicNums.Add(filterClinicFkey);
}
listClinicNumsInRegion.AddRange(listUnrestrictedClinicNumsInRegion);
string strClinicFilterNums = string.Join(",", listClinicNums.Select(x => POut.Long(x)));
string strRegionFilterNums = string.Join(",", listClinicNumsInRegion.Select(x => POut.Long(x)));
//Clause for TaskLists that have Default filter.
string cmdFilterTaskListByDefault = "(tasklistfortask.GlobalTaskFilterType=" + POut.Long((long)GlobalTaskFilterType.Default)
+ GetDefaultFilterTypeString((GlobalTaskFilterType)PrefC.GetInt(PrefName.TasksGlobalFilterType), strClinicFilterNums, strRegionFilterNums) + ") ";
//Clause for TaskLists that have None filter.
string cmdFilterTaskListByNone = "(tasklistfortask.GlobalTaskFilterType=" + POut.Long((long)GlobalTaskFilterType.None) + ")";
//Clause for TaskLists that have Clinic filter.
string cmdFilterTaskListByClinic = "(tasklistfortask.GlobalTaskFilterType=" + POut.Long((long)GlobalTaskFilterType.Clinic)
+ " AND (patient.ClinicNum IN (" + strClinicFilterNums + ") OR appointment.ClinicNum IN (" + strClinicFilterNums + "))) ";
//Clause for TaskLists that have Region filter.
string cmdFilterTaskListByRegion = "(tasklistfortask.GlobalTaskFilterType=" + POut.Long((long)GlobalTaskFilterType.Region)
+ " AND (patient.ClinicNum IN (" + strRegionFilterNums + ") OR appointment.ClinicNum IN (" + strRegionFilterNums + "))) ";
//Clause for Tasks that are not connected to a patient or clinic.
string cmdTaskClinicIsNull = "((patient.ClinicNum IS NULL) AND (appointment.ClinicNum IS NULL))";
command = " AND (" + cmdFilterTaskListByDefault + " OR " + cmdFilterTaskListByNone + " OR " + cmdFilterTaskListByClinic + " OR "
+ cmdFilterTaskListByRegion + "OR " + cmdTaskClinicIsNull + ") ";
return(command);
}
public static string GetChangesDescription(TaskHist taskCur, TaskHist taskNext)
{
if (taskCur.Descript.StartsWith("This task was cut from task list ") || taskCur.Descript.StartsWith("This task was copied from task "))
{
return(taskCur.Descript);
}
if (taskCur.DateTimeEntry == DateTime.MinValue)
{
return(Lans.g("TaskHists", "New task."));
}
StringBuilder strb = new StringBuilder();
strb.Append("");
if (taskNext.TaskListNum != taskCur.TaskListNum)
{
string descOne = Lans.g("TaskHists", "(DELETED)");
string descTwo = Lans.g("TaskHists", "(DELETED)");
TaskList taskList = TaskLists.GetOne(taskCur.TaskListNum);
if (taskList != null)
{
descOne = taskList.Descript;
}
taskList = TaskLists.GetOne(taskNext.TaskListNum);
if (taskList != null)
{
descTwo = taskList.Descript;
}
strb.Append(Lans.g("TaskHists", "Task list changed from") + " " + descOne + " " + Lans.g("TaskHists", "to") + " " + descTwo + ".\r\n");
}
if (taskNext.ObjectType != taskCur.ObjectType)
{
strb.Append(Lans.g("TaskHists", "Task attachment changed from") + " "
+ taskCur.ObjectType.ToString() + " " + Lans.g("TaskHists", "to") + " " + taskNext.ObjectType.ToString() + ".\r\n");
}
if (taskNext.KeyNum != taskCur.KeyNum)
{
strb.Append(Lans.g("TaskHists", "Task account attachment changed.") + "\r\n");
}
if (taskNext.Descript != taskCur.Descript && !taskNext.Descript.StartsWith("This task was cut from task list ") &&
!taskNext.Descript.StartsWith("This task was copied from task "))
{
//We change the description of a task when it is cut/copied.
//This prevents the history grid from showing a description changed when it wasn't changed by the user.
strb.Append(Lans.g("TaskHists", "Task description changed.") + "\r\n");
}
if (taskNext.TaskStatus != taskCur.TaskStatus)
{
strb.Append(Lans.g("TaskHists", "Task status changed from") + " "
+ taskCur.TaskStatus.ToString() + " " + Lans.g("TaskHists", "to") + " " + taskNext.TaskStatus.ToString() + ".\r\n");
}
if (taskNext.DateTimeEntry != taskCur.DateTimeEntry)
{
strb.Append(Lans.g("TaskHists", "Task date added changed from") + " "
+ taskCur.DateTimeEntry.ToString()
+ " " + Lans.g("TaskHists", "to") + " "
+ taskNext.DateTimeEntry.ToString() + ".\r\n");
}
if (taskNext.UserNum != taskCur.UserNum)
{
strb.Append(Lans.g("TaskHists", "Task author changed from ")
+ Userods.GetUser(taskCur.UserNum).UserName
+ " " + Lans.g("TaskHists", "to") + " "
+ Userods.GetUser(taskNext.UserNum).UserName + ".\r\n");
}
if (taskNext.DateTimeFinished != taskCur.DateTimeFinished)
{
strb.Append(Lans.g("TaskHists", "Task date finished changed from") + " "
+ taskCur.DateTimeFinished.ToString()
+ " " + Lans.g("TaskHists", "to") + " "
+ taskNext.DateTimeFinished.ToString() + ".\r\n");
}
if (taskNext.PriorityDefNum != taskCur.PriorityDefNum)
{
strb.Append(Lans.g("TaskHists", "Task priority changed from") + " "
+ Defs.GetDef(DefCat.TaskPriorities, taskCur.PriorityDefNum).ItemName
+ " " + Lans.g("TaskHists", "to") + " "
+ Defs.GetDef(DefCat.TaskPriorities, taskNext.PriorityDefNum).ItemName + ".\r\n");
}
if (taskCur.IsNoteChange) //Using taskOld because the notes changed from the old one to the new one.
{
strb.Append(Lans.g("TaskHists", "Task notes changed."));
}
return(strb.ToString());
}
///<summary>Checks dependencies first. Throws exception if can't delete.</summary>
public static void Delete(Clinic clinic)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), clinic);
return;
}
//Check FK dependencies.
#region Patients
string command = "SELECT LName,FName FROM patient WHERE ClinicNum ="
+ POut.Long(clinic.ClinicNum);
DataTable table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string pats = "";
for (int i = 0; i < table.Rows.Count; i++)
{
pats += "\r";
if (i == 15)
{
pats += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
pats += table.Rows[i]["LName"].ToString() + ", " + table.Rows[i]["FName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because it is in use by the following patients:") + pats);
}
#endregion
#region Payments
command = "SELECT patient.LName,patient.FName FROM patient,payment "
+ "WHERE payment.ClinicNum =" + POut.Long(clinic.ClinicNum)
+ " AND patient.PatNum=payment.PatNum";
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string pats = "";
for (int i = 0; i < table.Rows.Count; i++)
{
pats += "\r";
if (i == 15)
{
pats += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
pats += table.Rows[i]["LName"].ToString() + ", " + table.Rows[i]["FName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following patients have payments using it:") + pats);
}
#endregion
#region ClaimPayments
command = "SELECT patient.LName,patient.FName FROM patient,claimproc,claimpayment "
+ "WHERE claimpayment.ClinicNum =" + POut.Long(clinic.ClinicNum)
+ " AND patient.PatNum=claimproc.PatNum"
+ " AND claimproc.ClaimPaymentNum=claimpayment.ClaimPaymentNum "
+ "GROUP BY patient.LName,patient.FName,claimpayment.ClaimPaymentNum";
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string pats = "";
for (int i = 0; i < table.Rows.Count; i++)
{
pats += "\r";
if (i == 15)
{
pats += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
pats += table.Rows[i]["LName"].ToString() + ", " + table.Rows[i]["FName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following patients have claim payments using it:") + pats);
}
#endregion
#region Appointments
command = "SELECT patient.LName,patient.FName FROM patient,appointment "
+ "WHERE appointment.ClinicNum =" + POut.Long(clinic.ClinicNum)
+ " AND patient.PatNum=appointment.PatNum";
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string pats = "";
for (int i = 0; i < table.Rows.Count; i++)
{
pats += "\r";
if (i == 15)
{
pats += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
pats += table.Rows[i]["LName"].ToString() + ", " + table.Rows[i]["FName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following patients have appointments using it:") + pats);
}
#endregion
#region Procedures
//reassign procedure.ClinicNum=0 if the procs are status D.
command = "SELECT ProcNum FROM procedurelog WHERE ProcStatus=" + POut.Int((int)ProcStat.D) + " AND ClinicNum=" + POut.Long(clinic.ClinicNum);
List <long> listProcNums = Db.GetListLong(command);
if (listProcNums.Count > 0)
{
command = "UPDATE procedurelog SET ClinicNum=0 WHERE ProcNum IN (" + string.Join(",", listProcNums.Select(x => POut.Long(x))) + ")";
Db.NonQ(command);
}
command = "SELECT patient.LName,patient.FName FROM patient,procedurelog "
+ "WHERE procedurelog.ClinicNum =" + POut.Long(clinic.ClinicNum)
+ " AND patient.PatNum=procedurelog.PatNum";
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string pats = "";
for (int i = 0; i < table.Rows.Count; i++)
{
pats += "\r";
if (i == 15)
{
pats += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
pats += table.Rows[i]["LName"].ToString() + ", " + table.Rows[i]["FName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following patients have procedures using it:") + pats);
}
#endregion
#region Operatories
command = "SELECT OpName FROM operatory "
+ "WHERE ClinicNum =" + POut.Long(clinic.ClinicNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string ops = "";
for (int i = 0; i < table.Rows.Count; i++)
{
ops += "\r";
if (i == 15)
{
ops += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
ops += table.Rows[i]["OpName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following operatories are using it:") + ops);
}
#endregion
#region Userod
command = "SELECT UserName FROM userod "
+ "WHERE ClinicNum =" + POut.Long(clinic.ClinicNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string userNames = "";
for (int i = 0; i < table.Rows.Count; i++)
{
userNames += "\r";
if (i == 15)
{
userNames += Lans.g("Clinics", "And") + " " + (table.Rows.Count - i) + " " + Lans.g("Clinics", "others");
break;
}
userNames += table.Rows[i]["UserName"].ToString();
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following Open Dental users are using it:") + userNames);
}
#endregion
#region AlertSub
command = "SELECT DISTINCT UserNum FROM AlertSub "
+ "WHERE ClinicNum =" + POut.Long(clinic.ClinicNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
List <string> listUsers = new List <string>();
for (int i = 0; i < table.Rows.Count; i++)
{
long userNum = PIn.Long(table.Rows[i]["UserNum"].ToString());
Userod user = Userods.GetUser(userNum);
if (user == null) //Should not happen.
{
continue;
}
listUsers.Add(user.UserName);
}
throw new Exception(Lans.g("Clinics", "Cannot delete clinic because the following Open Dental users are subscribed to it:") + "\r" + String.Join("\r", listUsers.OrderBy(x => x).ToArray()));
}
#endregion
#region UserClinics
command = "SELECT userod.UserName FROM userclinic INNER JOIN userod ON userclinic.UserNum=userod.UserNum "
+ "WHERE userclinic.ClinicNum=" + POut.Long(clinic.ClinicNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
string users = "";
for (int i = 0; i < table.Rows.Count; i++)
{
if (i > 0)
{
users += ",";
}
users += table.Rows[i][0].ToString();
}
throw new Exception(
Lans.g("Clinics", "Cannot delete clinic because the following users are restricted to this clinic in security setup:") + " " + users);
}
#endregion
//End checking for dependencies.
//Clinic is not being used, OK to delete.
//Delete clinic specific program properties.
command = "DELETE FROM programproperty WHERE ClinicNum=" + POut.Long(clinic.ClinicNum) + " AND ClinicNum!=0"; //just in case a programming error tries to delete an invalid clinic.
Db.NonQ(command);
Crud.ClinicCrud.Delete(clinic.ClinicNum);
}
