///<summary>Deletes all the voice mails and their corresponding files that have a status of Deleted and a DateCreated that is before the passed
///in date. Called from PhoneTrackingServer.</summary>
public static void DeleteBefore(DateTime dateBefore)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), dateBefore);
return;
}
string command = @"SELECT * FROM voicemail
WHERE StatusVM=" + POut.Int((int)VoiceMailStatus.Deleted) + @"
AND " + DbHelper.DtimeToDate("DateCreated") + "<" + POut.Date(dateBefore);
List <VoiceMail> listVoiceMails = Crud.VoiceMailCrud.SelectMany(command);
Exception firstEx = null;
foreach (VoiceMail voiceMail in listVoiceMails)
{
try {
Delete(voiceMail); //Also deletes the files
}
catch (Exception ex) {
firstEx = firstEx ?? ex;
}
}
if (firstEx != null)
{
throw firstEx;
}
}
private static string GetInsVerifyCleanupQuery(DateTime startDate, DateTime endDate)
{
return(@"SELECT InsVerifyNum
FROM (
SELECT InsVerifyNum,patplan.PatNum
FROM patplan
INNER JOIN inssub ON inssub.InsSubNum=patplan.InsSubNum
INNER JOIN insplan ON insplan.PlanNum=inssub.PlanNum
AND insplan.HideFromVerifyList=0
INNER JOIN insverify ON VerifyType=" + POut.Int((int)VerifyTypes.InsuranceBenefit) + @"
AND insverify.FKey=insplan.PlanNum
WHERE insverify.DateLastAssigned>'0001-01-01'
AND insverify.DateLastAssigned<" + POut.Date(DateTime.Today.AddDays(-30)) + @"
UNION
SELECT InsVerifyNum,patplan.PatNum
FROM patplan
INNER JOIN insverify ON VerifyType=" + POut.Int((int)VerifyTypes.PatientEnrollment) + @"
AND insverify.FKey=patplan.PatPlanNum
WHERE insverify.DateLastAssigned>'0001-01-01'
AND insverify.DateLastAssigned<" + POut.Date(DateTime.Today.AddDays(-30)) + @"
) insverifies
LEFT JOIN appointment ON appointment.PatNum=insverifies.PatNum
AND appointment.AptStatus IN (" + POut.Int((int)ApptStatus.Scheduled) + "," + POut.Int((int)ApptStatus.Complete) + @")
AND " + DbHelper.DtimeToDate("appointment.AptDateTime") + " BETWEEN " + POut.Date(startDate) + " AND " + POut.Date(endDate) + @"
GROUP BY insverifies.InsVerifyNum
HAVING MAX(appointment.AptNum) IS NULL" );
}
private static string ByApptStatusQuery(bool hasClinicsEnabled, DateTime dateStart, DateTime dateEnd, string whereProv, string whereClin)
{
string queryBrokenApts = "SELECT " + DbHelper.DateTFormatColumn("appointment.AptDateTime", "%m/%d/%Y %H:%i:%s") + " AptDateTime, "
+ "" + DbHelper.Concat("patient.LName", "', '", "patient.FName") + " Patient,doctor.Abbr Doctor,hygienist.Abbr Hygienist, "
+ "appointment.IsHygiene IsHygieneApt ";
if (hasClinicsEnabled)
{
queryBrokenApts += ",COALESCE(clinic.Description,'" + POut.String(Lans.g("FormRpBrokenAppointments", "Unassigned")) + "') ClinicDesc "; //Coalesce is Oracle compatible
}
queryBrokenApts +=
"FROM appointment "
+ "INNER JOIN patient ON appointment.PatNum=patient.PatNum "
+ "LEFT JOIN provider doctor ON doctor.ProvNum=appointment.ProvNum "
+ "LEFT JOIN provider hygienist ON hygienist.ProvNum=appointment.ProvHyg ";
if (hasClinicsEnabled)
{
queryBrokenApts += "LEFT JOIN clinic ON clinic.ClinicNum=appointment.ClinicNum ";
}
queryBrokenApts +=
"WHERE " + DbHelper.DtimeToDate("appointment.AptDateTime") + " BETWEEN " + POut.Date(dateStart)
+ " AND " + POut.Date(dateEnd) + " "
+ "AND appointment.AptStatus=" + POut.Int((int)ApptStatus.Broken) + " "
+ whereProv;
if (hasClinicsEnabled)
{
queryBrokenApts += whereClin + " "
+ "ORDER BY clinic.Description,appointment.AptDateTime,patient.LName,patient.FName";
}
else
{
queryBrokenApts += "ORDER BY appointment.AptDateTime,patient.LName,patient.FName ";
}
return(queryBrokenApts);
}
///<summary>Validates and throws exceptions. Deletes automatic adjustments that fall within the pay period.</summary>
public static List <TimeAdjust> GetListForTimeCardManage(long empNum, long clinicNum, DateTime fromDate, DateTime toDate, bool isAll)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TimeAdjust> >(MethodBase.GetCurrentMethod(), empNum, clinicNum, fromDate, toDate, isAll));
}
List <TimeAdjust> retVal = new List <TimeAdjust>();
//List<TimeAdjust> listTimeAdjusts=new List<TimeAdjust>();
string command =
"SELECT * FROM timeadjust WHERE "
+ "EmployeeNum = " + POut.Long(empNum) + " "
+ "AND " + DbHelper.DtimeToDate("TimeEntry") + " >= " + POut.Date(fromDate) + " "
+ "AND " + DbHelper.DtimeToDate("TimeEntry") + " <= " + POut.Date(toDate) + " ";
if (!isAll)
{
command += "AND ClinicNum = " + POut.Long(clinicNum) + " ";
}
command += "ORDER BY TimeEntry";
//listTimeAdjusts=Crud.TimeAdjustCrud.SelectMany(command);
return(Crud.TimeAdjustCrud.SelectMany(command));
//Delete automatic adjustments.------------------------------------------------------------------------------------------
//for(int i=0;i<listTimeAdjusts.Count;i++) {
// if(!listTimeAdjusts[i].IsAuto) {//skip and never delete manual adjustments
// retVal.Add(listTimeAdjusts[i]);
// continue;
// }
// TimeAdjusts.Delete(listTimeAdjusts[i]);//delete auto adjustments for current pay period
//}
//Validate---------------------------------------------------------------------------------------------------------------
//none necessary at this time.
//return retVal;
}
///<summary>Gets list of CodeValue strings from interventions with DateEntry in the last year and CodeSet equal to the supplied codeSet.
///Result list is grouped by CodeValue, CodeSystem even though we only return the list of CodeValues. However, there are no codes in the
///EHR intervention code list that conflict between code systems, so we should never have a duplicate code in the returned list.</summary>
public static List <string> GetAllForCodeSet(InterventionCodeSet codeSet)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod(), codeSet));
}
string command = "SELECT CodeValue FROM intervention WHERE CodeSet=" + POut.Int((int)codeSet) + " "
+ "AND " + DbHelper.DtimeToDate("DateEntry") + ">=" + POut.Date(MiscData.GetNowDateTime().AddYears(-1)) + " "
+ "GROUP BY CodeValue,CodeSystem";
return(Db.GetListString(command));
}
///<summary>Creates a SQLParam that evaluates the specified column with regards to the specified value.</summary>
public static SQLWhere Create <T>(string columnName, ComparisonOperator comparison, T value, bool doTreatDtAsDate = false, string tableName = "")
{
if (!string.IsNullOrEmpty(tableName))
{
columnName = tableName.ToLower() + "." + columnName;
}
SQLWhere sqlParam = new SQLWhere();
sqlParam._whereClause = (doTreatDtAsDate ? DbHelper.DtimeToDate(columnName) : columnName)
+ comparison.GetDescription() + POutObj(value, doTreatDtAsDate);
return(sqlParam);
}
///<summary>Creates a NOT BETWEEN clause with the specified column and the specified values.</summary>
public static SQLWhere CreateNotBetween <T>(string columnName, T valueLower, T valueHigher, bool doTreatDtAsDate = false, string tableName = "")
{
if (!string.IsNullOrEmpty(tableName))
{
columnName = tableName.ToLower() + "." + columnName;
}
SQLWhere sqlParam = new SQLWhere();
sqlParam._whereClause = (doTreatDtAsDate ? DbHelper.DtimeToDate(columnName) : columnName)
+ " NOT BETWEEN " + POutObj(valueLower, doTreatDtAsDate) + " AND " + POutObj(valueHigher, doTreatDtAsDate) + "";
return(sqlParam);
}
public static int GetRecallUndoCount(DateTime date)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetInt(MethodBase.GetCurrentMethod(), date));
}
string command = "SELECT COUNT(*) FROM commlog "
+ "WHERE " + DbHelper.DtimeToDate("CommDateTime") + " = " + POut.Date(date) + " "
+ "AND (SELECT ItemValue FROM definition WHERE definition.DefNum=commlog.CommType) ='" + CommItemTypeAuto.RECALL.ToString() + "'";
return(PIn.Int(Db.GetScalar(command)));
}
public static void RecallUndo(DateTime date)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), date);
return;
}
string command = "DELETE FROM commlog "
+ "WHERE " + DbHelper.DtimeToDate("CommDateTime") + " = " + POut.Date(date) + " "
+ "AND (SELECT ItemValue FROM definition WHERE definition.DefNum=commlog.CommType) ='" + CommItemTypeAuto.RECALL.ToString() + "'";
Db.NonQ(command);
}
///<summary>Gets all SMS incoming messages for the specified filters.</summary>
///<param name="dateStart">If dateStart is 01/01/0001, then no start date will be used.</param>
///<param name="dateEnd">If dateEnd is 01/01/0001, then no end date will be used.</param>
///<param name="listClinicNums">Will filter by clinic only if not empty and patNum is -1.</param>
///<param name="patNum">If patNum is not -1, then only the messages for the specified patient will be returned, otherwise messages for all
///patients will be returned.</param>
///<param name="isMessageThread">Indicates if this is a message thread.</param>
///<param name="phoneNumber">The phone number to search by. Should be just the digits, no formatting.</param>
///<param name="arrayStatuses">Messages with these statuses will be found. If none, all statuses will be returned.</param>
public static List <SmsFromMobile> GetMessages(DateTime dateStart, DateTime dateEnd, List <long> listClinicNums, long patNum,
bool isMessageThread, string phoneNumber, params SmsFromStatus[] arrayStatuses)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <SmsFromMobile> >(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listClinicNums, patNum, isMessageThread,
phoneNumber, arrayStatuses));
}
List <SmsFromStatus> statusFilters = new List <SmsFromStatus>(arrayStatuses);
List <string> listCommandFilters = new List <string>();
if (dateStart > DateTime.MinValue)
{
listCommandFilters.Add(DbHelper.DtimeToDate("DateTimeReceived") + ">=" + POut.Date(dateStart));
}
if (dateEnd > DateTime.MinValue)
{
listCommandFilters.Add(DbHelper.DtimeToDate("DateTimeReceived") + "<=" + POut.Date(dateEnd));
}
if (patNum == -1)
{
//Only limit clinic if not searching for a particular PatNum.
if (listClinicNums.Count > 0)
{
listCommandFilters.Add("ClinicNum IN (" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ")");
}
}
else
{
listCommandFilters.Add($"PatNum = {POut.Long(patNum)}");
}
if (!string.IsNullOrEmpty(phoneNumber))
{
listCommandFilters.Add($"MobilePhoneNumber='{POut.String(phoneNumber)}'");
}
if (!isMessageThread) //Always show unread in the grid.
{
statusFilters.Add(SmsFromStatus.ReceivedUnread);
}
if (statusFilters.Count > 0)
{
listCommandFilters.Add("SmsStatus IN (" + string.Join(",", statusFilters.GroupBy(x => x).Select(x => POut.Int((int)x.Key))) + ")");
}
string command = "SELECT * FROM smsfrommobile";
if (listCommandFilters.Count > 0)
{
command += " WHERE " + string.Join(" AND ", listCommandFilters);
}
return(Crud.SmsFromMobileCrud.SelectMany(command));
}
///<summary>Gets codes (SNOMEDCT) from CodeValueResult for EhrMeasureEvents with DateTEvent within the last year for the given EhrMeasureEventType.
///Result list is grouped by code.</summary>
public static List <string> GetListCodesUsedForType(EhrMeasureEventType eventType)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod(), eventType));
}
string command = "SELECT CodeValueResult FROM ehrmeasureevent "
+ "WHERE EventType=" + POut.Int((int)eventType) + " "
+ "AND CodeValueResult!='' "
+ "AND " + DbHelper.DtimeToDate("DateTEvent") + ">=" + POut.Date(MiscData.GetNowDateTime().AddYears(-1)) + " "
+ "GROUP BY CodeValueResult";
return(Db.GetListString(command));
}
///<summary></summary>
public static List <TimeAdjust> Refresh(long empNum, DateTime fromDate, DateTime toDate)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TimeAdjust> >(MethodBase.GetCurrentMethod(), empNum, fromDate, toDate));
}
string command =
"SELECT * FROM timeadjust WHERE "
+ "EmployeeNum = " + POut.Long(empNum) + " "
+ "AND " + DbHelper.DtimeToDate("TimeEntry") + " >= " + POut.Date(fromDate) + " "
+ "AND " + DbHelper.DtimeToDate("TimeEntry") + " <= " + POut.Date(toDate) + " "
+ "ORDER BY TimeEntry";
return(Crud.TimeAdjustCrud.SelectMany(command));
}
///<summary>Gets list of RxCui code strings for medications with RxCui in the supplied list ordered for patients in the last year.
///"Ordered" is based on there being a DateStart. Result list is grouped by RxCui.</summary>
public static List <string> GetAllForRxCuis(List <string> listRxCuis)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <string> >(MethodBase.GetCurrentMethod(), listRxCuis));
}
if (listRxCuis == null || listRxCuis.Count == 0)
{
return(new List <string>());
}
string command = "SELECT RxCui FROM medicationpat WHERE RxCui IN(" + string.Join(",", listRxCuis) + ") "
+ "AND " + DbHelper.DtimeToDate("DateStart") + ">=" + POut.Date(MiscData.GetNowDateTime().AddYears(-1)) + " "
+ "GROUP BY RxCui";
return(Db.GetListString(command));
}
///<summary>Returns all automatically generated timeAdjusts for a given employee between the date range (inclusive).</summary>
public static List <TimeAdjust> GetSimpleListAuto(long employeeNum, DateTime startDate, DateTime stopDate)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TimeAdjust> >(MethodBase.GetCurrentMethod(), employeeNum, startDate, stopDate));
}
List <TimeAdjust> retVal = new List <TimeAdjust>();
//List<TimeAdjust> listTimeAdjusts=new List<TimeAdjust>();
string command =
"SELECT * FROM timeadjust WHERE "
+ "EmployeeNum = " + POut.Long(employeeNum) + " "
+ "AND " + DbHelper.DtimeToDate("TimeEntry") + " >= " + POut.Date(startDate) + " "
+ "AND " + DbHelper.DtimeToDate("TimeEntry") + " < " + POut.Date(stopDate.AddDays(1)) + " " //add one day to go the end of the specified date.
+ "AND IsAuto=1";
//listTimeAdjusts=Crud.TimeAdjustCrud.SelectMany(command);
return(Crud.TimeAdjustCrud.SelectMany(command));
}
///<summary>Get all sheets for a patient for today.</summary>
public static List <Sheet> GetForPatientForToday(long patNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Sheet> >(MethodBase.GetCurrentMethod(), patNum));
}
string datesql = "CURDATE()";
if (DataConnection.DBtype == DatabaseType.Oracle)
{
datesql = "(SELECT CURRENT_DATE FROM dual)";
}
string command = "SELECT * FROM sheet WHERE PatNum=" + POut.Long(patNum) + " "
+ "AND " + DbHelper.DtimeToDate("DateTimeSheet") + " = " + datesql + " "
+ "AND IsDeleted=0";
return(Crud.SheetCrud.SelectMany(command));
}
///<summary>Creates a NOT IN clause using the specified column and the specified values.</summary>
public static SQLWhere CreateNotIn <T>(string columnName, List <T> listValues, bool doTreatDtAsDate = false, string tableName = "")
{
if (!string.IsNullOrEmpty(tableName))
{
columnName = tableName.ToLower() + "." + columnName;
}
SQLWhere sqlParam = new SQLWhere();
if (listValues.Count == 0)
{
sqlParam._whereClause = " TRUE ";
}
else
{
sqlParam._whereClause = (doTreatDtAsDate ? DbHelper.DtimeToDate(columnName) : columnName)
+ " NOT IN (" + string.Join(",", listValues.Select(x => POutObj(x, doTreatDtAsDate))) + ")";
}
return(sqlParam);
}
///<summary>Returns all sheets for the given patient in the given date range which have a description matching the examDescript in a case insensitive manner. If examDescript is blank, then sheets with any description are returned.</summary>
public static List <Sheet> GetExamSheetsTable(long patNum, DateTime startDate, DateTime endDate, string examDescript)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Sheet> >(MethodBase.GetCurrentMethod(), patNum, startDate, endDate, examDescript));
}
string command = "SELECT * "
+ "FROM sheet WHERE IsDeleted=0 "
+ "AND PatNum=" + POut.Long(patNum) + " "
+ "AND SheetType=" + POut.Int((int)SheetTypeEnum.ExamSheet) + " ";
if (examDescript != "")
{
command += "AND Description LIKE '%" + POut.String(examDescript) + "%' "; //case insensitive text matches
}
command += "AND " + DbHelper.DtimeToDate("DateTimeSheet") + ">=" + POut.Date(startDate) + " AND " + DbHelper.DtimeToDate("DateTimeSheet") + "<=" + POut.Date(endDate) + " "
+ "ORDER BY DateTimeSheet";
return(Crud.SheetCrud.SelectMany(command));
}
///<summary>Returns all sheets for the given patient in the given date range which have a description matching the examDescript in a case insensitive manner. If examDescript is blank, then sheets with any description are returned.</summary>
public static List <Sheet> GetExamSheetsTable(long patNum, DateTime startDate, DateTime endDate, long sheetDefNum = -1)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Sheet> >(MethodBase.GetCurrentMethod(), patNum, startDate, endDate, sheetDefNum));
}
string command = "SELECT * "
+ "FROM sheet WHERE IsDeleted=0 "
+ "AND PatNum=" + POut.Long(patNum) + " "
+ "AND SheetType=" + POut.Int((int)SheetTypeEnum.ExamSheet) + " ";
if (sheetDefNum != -1)
{
command += "AND SheetDefNum = " + POut.Long(sheetDefNum) + " ";
}
command += "AND " + DbHelper.DtimeToDate("DateTimeSheet") + ">=" + POut.Date(startDate) + " AND " + DbHelper.DtimeToDate("DateTimeSheet") + "<=" + POut.Date(endDate) + " "
+ "ORDER BY DateTimeSheet";
return(Crud.SheetCrud.SelectMany(command));
}
///<summary>Validates and throws exceptions. Gets all time adjusts between date range and time adjusts made during the current work week. </summary>
public static List <TimeAdjust> GetValidList(long empNum, DateTime fromDate, DateTime toDate)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TimeAdjust> >(MethodBase.GetCurrentMethod(), empNum, fromDate, toDate));
}
List <TimeAdjust> retVal = new List <TimeAdjust>();
string command =
"SELECT * FROM timeadjust WHERE "
+ "EmployeeNum = " + POut.Long(empNum) + " "
+ "AND " + DbHelper.DtimeToDate("TimeEntry") + " >= " + POut.Date(fromDate) + " "
+ "AND " + DbHelper.DtimeToDate("TimeEntry") + " <= " + POut.Date(toDate) + " "
+ "ORDER BY TimeEntry";
retVal = Crud.TimeAdjustCrud.SelectMany(command);
//Validate---------------------------------------------------------------------------------------------------------------
//none necessary at this time.
return(retVal);
}
///<summary>When called we will make sure to send a startDate and endDate. Status parameter 0:All, 1:OutPending, 2:OutSent, 3:OutFailed, 4:InProcessed, 5:InFailed. This will not return hl7msg.MsgText due to large size of text of many messages. To see the message text of one of the returned rows, use GetOne(long hl7MsgNum) above.</summary>
public static List <HL7Msg> GetHL7Msgs(DateTime startDate, DateTime endDate, long patNum, int status)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <HL7Msg> >(MethodBase.GetCurrentMethod(), startDate, endDate, patNum, status));
}
//join with the patient table so we can display patient name instead of PatNum
string command = @"SELECT HL7MsgNum,HL7Status,'' AS MsgText,AptNum,DateTStamp,PatNum,Note "
+ "FROM hl7msg WHERE " + DbHelper.DtimeToDate("hl7msg.DateTStamp") + " BETWEEN " + POut.Date(startDate) + " AND " + POut.Date(endDate) + " ";
if (patNum > 0)
{
command += "AND hl7msg.PatNum=" + POut.Long(patNum) + " ";
}
if (status > 0)
{
command += "AND hl7msg.HL7Status=" + POut.Long(status - 1) + " "; //minus 1 because 0=All but our enum starts at 0
}
command += "ORDER BY hl7msg.DateTStamp";
return(Crud.HL7MsgCrud.SelectMany(command));
}
///<summary>Gets all SMS messages for the specified filters.</summary>
///<param name="dateStart">If dateStart is 01/01/0001, then no start date will be used.</param>
///<param name="dateEnd">If dateEnd is 01/01/0001, then no end date will be used.</param>
///<param name="listClinicNums">Will filter by clinic only if not empty and patNum is -1.</param>
///<param name="patNum">If patNum is not -1, then only the messages for the specified patient will be returned, otherwise messages for all
///patients will be returned.</param>
///<param name="phoneNumber">The phone number to search by. Should be just the digits, no formatting.</param>
public static List <SmsToMobile> GetMessages(DateTime dateStart, DateTime dateEnd, List <long> listClinicNums, long patNum = -1, string phoneNumber = "")
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <SmsToMobile> >(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listClinicNums, patNum, phoneNumber));
}
List <string> listCommandFilters = new List <string>();
if (dateStart > DateTime.MinValue)
{
listCommandFilters.Add(DbHelper.DtimeToDate("DateTimeSent") + ">=" + POut.Date(dateStart));
}
if (dateEnd > DateTime.MinValue)
{
listCommandFilters.Add(DbHelper.DtimeToDate("DateTimeSent") + "<=" + POut.Date(dateEnd));
}
if (patNum == -1)
{
//Only limit clinic if not searching for a particular PatNum.
if (listClinicNums.Count > 0)
{
listCommandFilters.Add("ClinicNum IN (" + String.Join(",", listClinicNums.Select(x => POut.Long(x))) + ")");
}
}
else
{
listCommandFilters.Add($"PatNum = {patNum}");
}
if (!string.IsNullOrEmpty(phoneNumber))
{
listCommandFilters.Add($"MobilePhoneNumber = '{POut.String(phoneNumber)}'");
}
string command = "SELECT * FROM smstomobile";
if (listCommandFilters.Count > 0)
{
command += " WHERE " + String.Join(" AND ", listCommandFilters);
}
return(Crud.SmsToMobileCrud.SelectMany(command));
}
///<summary></summary>
public static List <TimeAdjust> GetListForTimeCardManage(List <long> listEmpNums, long clinicNum, DateTime fromDate, DateTime toDate, bool isAll)
{
if (listEmpNums.IsNullOrEmpty())
{
return(new List <TimeAdjust>());
}
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <TimeAdjust> >(MethodBase.GetCurrentMethod(), listEmpNums, clinicNum, fromDate, toDate, isAll));
}
string command = "SELECT * FROM timeadjust WHERE "
+ "EmployeeNum IN (" + string.Join(",", listEmpNums.Select(x => POut.Long(x))) + ") "
+ "AND " + DbHelper.DtimeToDate("TimeEntry") + " >= " + POut.Date(fromDate) + " "
+ "AND " + DbHelper.DtimeToDate("TimeEntry") + " <= " + POut.Date(toDate) + " ";
if (!isAll)
{
command += "AND ClinicNum = " + POut.Long(clinicNum) + " ";
}
command += "ORDER BY TimeEntry";
return(Crud.TimeAdjustCrud.SelectMany(command));
}
public static DataTable GetMissingXTransTable(DateTime dateStart, DateTime dateEnd)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
}
string command = "SELECT payment.PatNum,LName,FName,payment.DateEntry,payment.PayDate,payment.PayNote,payment.PayAmt "
+ "FROM patient "
+ "INNER JOIN payment ON payment.PatNum=patient.PatNum "
//only payments with the same PaymentType as the X-Charge PaymentType for the clinic
+ "INNER JOIN ("
+ "SELECT ClinicNum,PropertyValue AS PaymentType FROM programproperty "
+ "WHERE ProgramNum=" + POut.Long(Programs.GetProgramNum(ProgramName.Xcharge)) + " AND PropertyDesc='PaymentType'"
+ ") paytypes ON paytypes.ClinicNum=payment.ClinicNum AND paytypes.PaymentType=payment.PayType "
+ "LEFT JOIN xchargetransaction ON xchargetransaction.PatNum=payment.PatNum "
+ "AND " + DbHelper.DtimeToDate("TransactionDateTime") + "=payment.DateEntry "
+ "AND (CASE WHEN xchargetransaction.ResultCode=5 THEN 0 ELSE xchargetransaction.Amount END)=payment.PayAmt "
+ "AND xchargetransaction.ResultCode IN(0,5,10) "
+ "WHERE payment.DateEntry BETWEEN " + POut.Date(dateStart) + " AND " + POut.Date(dateEnd) + " "
+ "AND TransactionDateTime IS NULL "
+ "ORDER BY payment.PayDate ASC,LName,FName";
return(Db.GetTable(command));
}
///<summary>Gets data for the history grid in the SendClaims window. The listEtransType must contain as least one item.</summary>
public static DataTable RefreshHistory(DateTime dateFrom,DateTime dateTo,List<EtransType> listEtransType) {
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetTable(MethodBase.GetCurrentMethod(),dateFrom,dateTo,listEtransType);
}
string command="SELECT (CASE WHEN etrans.PatNum=0 THEN etrans.PatientNameRaw "
+"ELSE CONCAT(CONCAT(patient.LName,', '),patient.FName) END) AS PatName,"
+"(CASE WHEN etrans.carrierNum=0 THEN etrans.CarrierNameRaw ELSE carrier.CarrierName END) AS CarrierName,"
+"clearinghouse.Description AS Clearinghouse,DateTimeTrans,etrans.OfficeSequenceNumber,"
+"etrans.CarrierTransCounter,Etype,etrans.ClaimNum,etrans.EtransNum,etrans.AckCode,etrans.Note,etrans.EtransMessageTextNum,etrans.TranSetId835,"
+"etrans.UserNum,etrans.PatNum "
+"FROM etrans "
+"LEFT JOIN carrier ON etrans.CarrierNum=carrier.CarrierNum "
+"LEFT JOIN patient ON patient.PatNum=etrans.PatNum "
+"LEFT JOIN clearinghouse ON clearinghouse.ClearinghouseNum=etrans.ClearinghouseNum WHERE "
+DbHelper.DtimeToDate("DateTimeTrans")+" >= "+POut.Date(dateFrom)+" AND "
+DbHelper.DtimeToDate("DateTimeTrans")+" <= "+POut.Date(dateTo)+" "
+"AND Etype IN ("+POut.Long((int)listEtransType[0]);
for(int i=1;i<listEtransType.Count;i++){//String.Join doesn't work because there's no way to cast the enums to ints in the function, db uses longs.
command+=", "+POut.Long((int)listEtransType[i]);
}
command+=") "
//For Canada, when the undo button is used from Manage | Send Claims, the ClaimNum is set to 0 instead of deleting the etrans entry.
//For transaction types related to claims where the claimnum=0, we do not want them to show in the history section of Manage | Send Claims because they have been undone.
+"AND (ClaimNum<>0 OR Etype NOT IN ("+POut.Long((int)EtransType.Claim_CA)+","+POut.Long((int)EtransType.ClaimCOB_CA)+","+POut.Long((int)EtransType.Predeterm_CA)+","+POut.Long((int)EtransType.ClaimReversal_CA)+")) "
+"ORDER BY DateTimeTrans";
DataTable table=Db.GetTable(command);
DataTable tHist=new DataTable("Table");
tHist.Columns.Add("patName");
tHist.Columns.Add("CarrierName");
tHist.Columns.Add("Clearinghouse");
tHist.Columns.Add("dateTimeTrans");
tHist.Columns.Add("OfficeSequenceNumber");
tHist.Columns.Add("CarrierTransCounter");
tHist.Columns.Add("etype");
tHist.Columns.Add("Etype");
tHist.Columns.Add("ClaimNum");
tHist.Columns.Add("EtransNum");
tHist.Columns.Add("ack");
tHist.Columns.Add("Note");
tHist.Columns.Add("EtransMessageTextNum");
tHist.Columns.Add("TranSetId835");
tHist.Columns.Add("UserNum");
tHist.Columns.Add("PatNum");
DataRow row;
string etype;
for(int i=0;i<table.Rows.Count;i++) {
row=tHist.NewRow();
row["patName"]=table.Rows[i]["PatName"].ToString();
row["CarrierName"]=table.Rows[i]["CarrierName"].ToString();
row["Clearinghouse"]=table.Rows[i]["Clearinghouse"].ToString();
row["dateTimeTrans"]=PIn.DateT(table.Rows[i]["DateTimeTrans"].ToString()).ToShortDateString();
row["OfficeSequenceNumber"]=table.Rows[i]["OfficeSequenceNumber"].ToString();
row["CarrierTransCounter"]=table.Rows[i]["CarrierTransCounter"].ToString();
row["Etype"]=table.Rows[i]["Etype"].ToString();
etype=Lans.g("enumEtransType",((EtransType)PIn.Long(table.Rows[i]["Etype"].ToString())).ToString());
if(etype.EndsWith("_CA")){
etype=etype.Substring(0,etype.Length-3);
}
row["etype"]=etype;
row["ClaimNum"]=table.Rows[i]["ClaimNum"].ToString();
row["EtransNum"]=table.Rows[i]["EtransNum"].ToString();
if(table.Rows[i]["AckCode"].ToString()=="A"){
row["ack"]=Lans.g("Etrans","Accepted");
}
else if(table.Rows[i]["AckCode"].ToString()=="R") {
row["ack"]=Lans.g("Etrans","Rejected");
}
else if(table.Rows[i]["AckCode"].ToString()=="Recd") {
row["ack"]=Lans.g("Etrans","Received");
}
else {
row["ack"]="";
}
row["Note"]=table.Rows[i]["Note"].ToString();
row["EtransMessageTextNum"]=table.Rows[i]["EtransMessageTextNum"].ToString();
row["TranSetId835"]=table.Rows[i]["TranSetId835"].ToString();
row["UserNum"]=table.Rows[i]["UserNum"].ToString();
row["PatNum"]=table.Rows[i]["PatNum"].ToString();
tHist.Rows.Add(row);
}
return tHist;
}
///<summary>If not using clinics then supply an empty list of clinicNums.</summary>
public static DataTable GetWriteoffTable(DateTime dateStart, DateTime dateEnd, List <long> listProvNums, List <long> listClinicNums
, bool hasAllClinics, bool hasClinicsEnabled, bool hasWriteoffPay)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd, listProvNums, listClinicNums, hasAllClinics
, hasClinicsEnabled, hasWriteoffPay));
}
string whereProv = "";
if (listProvNums.Count > 0)
{
whereProv += " AND claimproc.ProvNum IN(" + string.Join(",", listProvNums) + ") ";
}
string whereClin = "";
if (hasClinicsEnabled && listClinicNums.Count > 0) //Using clinics
{
whereClin += " AND claimproc.ClinicNum IN(" + string.Join(",", listClinicNums) + ") ";
}
string query = "SET @FromDate=" + POut.Date(dateStart) + ", @ToDate=" + POut.Date(dateEnd) + ";";
if (hasWriteoffPay)
{
query += "SELECT " + DbHelper.DtimeToDate("claimproc.DateCP") + " date,"
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ","
+ "carrier.CarrierName,"
+ "provider.Abbr,";
if (hasClinicsEnabled)
{
query += "clinic.Description,";
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
query += "SUM(claimproc.WriteOff) $amount,";
}
else //Oracle needs quotes.
{
query += "SUM(claimproc.WriteOff) \"$amount\",";
}
query += "claimproc.ClaimNum "
+ "FROM claimproc " //,insplan,patient,carrier,provider "
+ "LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum "
+ "LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
+ "LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
+ "LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
+ "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum "
+ "WHERE (claimproc.Status=1 OR claimproc.Status=4) " /*received or supplemental*/
+ whereProv
+ whereClin
+ "AND claimproc.DateCP >= @FromDate "
+ "AND claimproc.DateCP <= @ToDate "
+ "AND claimproc.WriteOff > 0 "
+ "GROUP BY claimproc.ProvNum,claimproc.DateCP,claimproc.ClinicNum,claimproc.PatNum "
+ "ORDER BY claimproc.DateCP,claimproc.PatNum";
}
else //using procedure date
{
query += "SELECT " + DbHelper.DtimeToDate("claimproc.ProcDate") + " date, "
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + ", "
+ "carrier.CarrierName, "
+ "provider.Abbr,";
if (hasClinicsEnabled)
{
query += "clinic.Description,";
}
if (DataConnection.DBtype == DatabaseType.MySql)
{
query += "SUM(claimproc.WriteOff) $amount ";
}
else //Oracle needs quotes.
{
query += "SUM(claimproc.WriteOff) \"$amount\" ";
}
query += "FROM claimproc " //,insplan,patient,carrier,provider "
+ "LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum "
+ "LEFT JOIN patient ON claimproc.PatNum = patient.PatNum "
+ "LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum "
+ "LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum "
+ "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum "
+ "WHERE (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " /*received or supplemental or notreceived*/
+ whereProv
+ whereClin
+ "AND claimproc.ProcDate >= @FromDate "
+ "AND claimproc.ProcDate <= @ToDate "
+ "AND claimproc.WriteOff > 0 "
+ "GROUP BY claimproc.ProvNum,claimproc.ProcDate,claimproc.ClinicNum,claimproc.PatNum "
+ "ORDER BY claimproc.ProcDate,claimproc.PatNum";
}
return(ReportsComplex.RunFuncOnReportServer(() => ReportsComplex.GetTable(query)));
}
public static DataSet GetMonthlyGoalDataSet(DateTime dateFrom, DateTime dateTo, List <Provider> listProvs, List <Clinic> listClinics, bool hasAllProvs
, bool hasAllClinics, PPOWriteoffDateCalc writeoffPayType, bool isCEMT = false)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetDS(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvs, listClinics, hasAllProvs, hasAllClinics, writeoffPayType, isCEMT));
}
List <long> listClinicNums = listClinics.Select(x => x.ClinicNum).ToList();
List <long> listProvNums = listProvs.Select(x => x.ProvNum).ToList();
#region Procedures
string whereProv = "";
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
string whereClin = "";
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
string command = "SELECT "
+ "procedurelog.ProcDate,procedurelog.ClinicNum,"
+ "SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(cp.WriteOff),0) Production "
+ "FROM procedurelog "
+ "LEFT JOIN (SELECT SUM(claimproc.WriteOff) AS WriteOff, claimproc.ProcNum FROM claimproc "
+ "WHERE claimproc.Status=7 " //only CapComplete writeoffs are subtracted here.
+ "GROUP BY claimproc.ProcNum) cp ON procedurelog.ProcNum=cp.ProcNum "
+ "WHERE procedurelog.ProcStatus = 2 "
+ whereProv
+ whereClin
+ "AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND procedurelog.ProcDate <= " + POut.Date(dateTo) + " "
+ "GROUP BY ClinicNum,YEAR(procedurelog.ProcDate),MONTH(procedurelog.ProcDate),DAY(procedurelog.ProcDate)"; //Does not work for Oracle. Consider enhancing with DbHelper.Year(),DbHelper.Month()
command += " ORDER BY ClinicNum,ProcDate";
DataTable tableProduction = new DataTable();
if (isCEMT)
{
tableProduction = Db.GetTable(command);
}
else
{
tableProduction = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableProduction.TableName = "tableProduction";
#endregion
#region Adjustments
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND adjustment.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND adjustment.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
command = "SELECT "
+ "adjustment.AdjDate,"
+ "adjustment.ClinicNum,"
+ "SUM(adjustment.AdjAmt) Adjustment "
+ "FROM adjustment "
+ "WHERE adjustment.AdjDate >= " + POut.Date(dateFrom) + " "
+ "AND adjustment.AdjDate <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "GROUP BY ClinicNum,YEAR(adjustment.AdjDate),MONTH(adjustment.AdjDate),DAY(adjustment.AdjDate)";
command += " ORDER BY ClinicNum,AdjDate";
DataTable tableAdj = new DataTable();
if (isCEMT)
{
tableAdj = Db.GetTable(command);
}
else
{
tableAdj = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableAdj.TableName = "tableAdj";
#endregion
#region TableInsWriteoff
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND claimproc.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND claimproc.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (writeoffPayType == PPOWriteoffDateCalc.InsPayDate)
{
command = "SELECT "
+ "claimproc.DateCP Date,"
+ "claimproc.ClinicNum,"
+ "SUM(claimproc.WriteOff) WriteOff "
+ "FROM claimproc "
+ "WHERE claimproc.DateCP >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.DateCP <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + ") "//received or supplemental
+ "GROUP BY ClinicNum,DATE(claimproc.DateCP) "
+ "ORDER BY ClinicNum,DateCP";
}
else if (writeoffPayType == PPOWriteoffDateCalc.ProcDate)
{
command = "SELECT "
+ "claimproc.ProcDate Date,"
+ "claimproc.ClinicNum,"
+ "SUM(claimproc.WriteOff) WriteOff "
+ "FROM claimproc "
+ "WHERE claimproc.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.ProcDate <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "//received or supplemental or notreceived
+ "GROUP BY ClinicNum,DATE(claimproc.ProcDate) "
+ "ORDER BY ClinicNum,ProcDate";
}
else // writeoffPayType==PPOWriteoffDateCalc.ClaimPayDate
{
command = "SELECT "
+ "claimsnapshot.DateTEntry Date,"
+ "claimproc.ClinicNum,"
+ "SUM(CASE WHEN claimsnapshot.WriteOff=-1 THEN 0 ELSE claimsnapshot.WriteOff END) WriteOff "
+ "FROM claimproc "
+ "INNER JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimProc.ClaimProcNum "
+ "WHERE " + DbHelper.BetweenDates("claimsnapshot.DateTEntry", dateFrom, dateTo) + " "
+ whereProv
+ whereClin
+ "AND claimproc.Status IN (" + (int)ClaimProcStatus.Received + "," + (int)ClaimProcStatus.Supplemental + "," + (int)ClaimProcStatus.NotReceived + ") "//received or supplemental or notreceived
+ "GROUP BY ClinicNum,DATE(claimsnapshot.DateTEntry) "
+ "ORDER BY ClinicNum,claimsnapshot.DateTEntry";
}
DataTable tableInsWriteoff = new DataTable();
if (isCEMT)
{
tableInsWriteoff = Db.GetTable(command);
}
else
{
tableInsWriteoff = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableInsWriteoff.TableName = "tableInsWriteoff";
#endregion
#region TableSched
DataTable tableSched = new DataTable();
//Reads from the procedurelog table instead of claimproc because we are looking for scheduled procedures.
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND procedurelog.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND procedurelog.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
command = "SELECT " + DbHelper.DtimeToDate("t.AptDateTime") + " SchedDate,SUM(t.Fee-t.WriteoffEstimate) Amount,ClinicNum "
+ "FROM (SELECT appointment.AptDateTime,IFNULL(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits),0) Fee,appointment.ClinicNum,";
if (ReportsComplex.RunFuncOnReportServer(() => Prefs.GetBoolNoCache(PrefName.ReportPandIschedProdSubtractsWO)))
{
//Subtract both PPO and capitation writeoffs
command += "SUM(IFNULL(CASE WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END,0)) WriteoffEstimate ";
}
else
{
//Always subtract CapEstimate writeoffs from scheduled production. This is so that the scheduled production will match actual production
//when the procedures are set complete. Nathan decided this 01/05/2017.
command += "SUM(IFNULL((CASE WHEN claimproc.Status=" + POut.Int((int)ClaimProcStatus.Estimate) + " THEN 0 "
+ "WHEN WriteOffEstOverride != -1 THEN WriteOffEstOverride ELSE WriteOffEst END),0)) WriteoffEstimate ";
}
command += "FROM appointment "
+ "LEFT JOIN procedurelog ON appointment.AptNum = procedurelog.AptNum AND procedurelog.ProcStatus=" + POut.Int((int)ProcStat.TP) + " "
+ "LEFT JOIN claimproc ON procedurelog.ProcNum = claimproc.ProcNum "
+ "AND claimproc.Status IN(" + POut.Int((int)ClaimProcStatus.Estimate) + "," + POut.Int((int)ClaimProcStatus.CapEstimate) + ") "
+ " AND (WriteOffEst != -1 OR WriteOffEstOverride != -1) "
+ "WHERE appointment.AptStatus = " + POut.Int((int)ApptStatus.Scheduled) + " "
+ "AND " + DbHelper.DtimeToDate("appointment.AptDateTime") + " >= " + POut.Date(dateFrom) + " "
+ "AND " + DbHelper.DtimeToDate("appointment.AptDateTime") + " <= " + POut.Date(dateTo) + " "
+ whereProv
+ whereClin
+ " GROUP BY procedurelog.ProcNum) t " //without this, there can be duplicate proc rows due to the claimproc join with dual insurance.
+ "GROUP BY SchedDate,ClinicNum "
+ "ORDER BY SchedDate";
if (isCEMT)
{
tableSched = Db.GetTable(command);
}
else
{
tableSched = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableSched.TableName = "tableSched";
#endregion
#region TableProdGoal
//1. Find all schedules for the month
//2. ClinicNum will come from the schedule's operatory
//3. Fetch HourlyProdGoalAmt from provider on the schedule
//4. Sum scheduled hours, grouped by prov and clinic
//5. Multiply the scheduled hours by the provider's HourlyProdGoalAmt
DataTable tableProdGoal = new DataTable();
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND schedule.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND COALESCE(operatory.ClinicNum,0) IN (" + string.Join(",", listClinicNums) + ") ";
}
//Fetch all schedules for the month and associated information (clinic from operatory, HourlyProdGoalAmt from provider)
command = "SELECT " + DbHelper.DtimeToDate("schedule.SchedDate") + @" AS SchedDate, schedule.StartTime AS StartTime, schedule.StopTime AS StopTime,
COALESCE(operatory.ClinicNum,0) AS ClinicNum, provider.HourlyProdGoalAmt AS ProvProdGoal, provider.ProvNum AS ProvNum
FROM schedule
INNER JOIN provider ON provider.ProvNum=schedule.ProvNum
LEFT JOIN scheduleop ON scheduleop.ScheduleNum=schedule.ScheduleNum
LEFT JOIN operatory ON scheduleop.OperatoryNum=operatory.OperatoryNum
WHERE schedule.SchedType=" + POut.Int((int)ScheduleType.Provider) + " "
+ "AND schedule.Status=" + POut.Int((int)SchedStatus.Open) + " "
+ "AND schedule." + DbHelper.BetweenDates("SchedDate", dateFrom, dateTo) + " "
+ whereProv
+ whereClin;
if (isCEMT)
{
tableProdGoal = Db.GetTable(command);
}
else
{
tableProdGoal = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
tableProdGoal.TableName = "tableProdGoal";
#endregion
#region WriteOffAdjustments
DataTable tableWriteOffAdjustments = new DataTable();
if (!hasAllProvs && listProvNums.Count > 0)
{
whereProv = "AND claimproc.ProvNum IN (" + string.Join(",", listProvNums) + ") ";
}
if (!hasAllClinics && listClinicNums.Count > 0)
{
whereClin = "AND claimproc.ClinicNum IN (" + string.Join(",", listClinicNums) + ") ";
}
if (writeoffPayType == PPOWriteoffDateCalc.ClaimPayDate)
{
//Insurance WriteOff Adjustments----------------------------------------------------------------------------
command = $@"SELECT claimproc.DateCP Date,claimproc.ClinicNum,
-SUM(CASE WHEN COALESCE(claimsnapshot.WriteOff,-1)=-1 THEN 0 ELSE claimsnapshot.WriteOff END) WriteOffEst,
SUM(claimproc.WriteOff) WriteOff,
claimproc.ClaimNum
FROM claimproc
LEFT JOIN claimsnapshot ON claimsnapshot.ClaimProcNum=claimproc.ClaimProcNum
WHERE claimproc.DateCP BETWEEN {POut.Date(dateFrom)} AND {POut.Date(dateTo)}
AND claimproc.Status IN ({(int)ClaimProcStatus.Received},{(int)ClaimProcStatus.Supplemental})
{whereProv}
{whereClin}
GROUP BY ClinicNum,DATE(claimproc.DateCP)" ;
if (isCEMT)
{
tableWriteOffAdjustments = Db.GetTable(command);
}
else
{
tableWriteOffAdjustments = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
}
}
tableWriteOffAdjustments.TableName = "tableWriteOffAdjustments";
#endregion WriteOffAdjustments
DataSet dataSet = new DataSet();
dataSet.Tables.Add(tableProduction);
dataSet.Tables.Add(tableAdj);
dataSet.Tables.Add(tableInsWriteoff);
dataSet.Tables.Add(tableWriteOffAdjustments);
dataSet.Tables.Add(tableSched);
dataSet.Tables.Add(tableProdGoal);
return(dataSet);
}
public static DataTable GetListOrderBy2014(List <EhrPatListElement2014> elementList)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), elementList));
}
DataTable table = new DataTable();
string select = "SELECT patient.PatNum,patient.LName,patient.FName";
string from = "FROM patient";
string where = "WHERE TRUE "; //Makes formatting easier when adding additional clauses because they will all be AND clauses.
for (int i = 0; i < elementList.Count; i++)
{
switch (elementList[i].Restriction)
{
case EhrRestrictionType.Birthdate: //---------------------------------------------------------------------------------------------------------------------------
select += ",patient.BirthDate, ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5))) AS Age";
from += ""; //only selecting from patient table
where += "AND ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5)))" + GetOperandText(elementList[i].Operand) + "" + PIn.String(elementList[i].CompareString) + " ";
break;
case EhrRestrictionType.Gender: //------------------------------------------------------------------------------------------------------------------------------
select += ",patient.Gender"; //will look odd if user adds multiple gender columns, enum needs to be "decoded" when filling grid.
break;
case EhrRestrictionType.LabResult: //---------------------------------------------------------------------------------------------------------------------------
//TODO Units
from += ",ehrlab AS ehrlab" + i + ",ehrlabresult AS ehrlabresult" + i + " ";
where += "AND ehrlab" + i + ".PatNum=patient.PatNum AND ehrlab" + i + ".EhrLabNum=ehrlabresult" + i + ".EhrLabNum "; //join
where += "AND ('" + elementList[i].CompareString + "'=ehrlabresult" + i + ".ObservationIdentifierID OR '"
+ elementList[i].CompareString + "'=ehrlabresult" + i + ".ObservationIdentifierIDAlt) "; //filter, LOINC of lab observation
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND ehrlabresult" + i + ".ObservationDateTime >=" + POut.Date(elementList[i].StartDate) + " "; //on or after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND ehrlabresult" + i + ".ObservationDateTime <=" + POut.Date(elementList[i].EndDate) + " "; //on or before this date
}
switch (elementList[i].LabValueType)
{
//CE and CWE should be SNOMEDCT codes, string compare elementList[i].LabValue to ehrlabresult.ObservationValueCodedElementID or ObservationValueCodedElementIDAlt
case HL70125.CE:
case HL70125.CWE:
select += ",(CASE WHEN ehrlabresult" + i + ".ObservationValueCodedElementID='' THEN ehrlabresult" + i + ".ObservationValueCodedElementIDAlt ELSE ehrlabresult" + i + ".ObservationValueCodedElementID END) AS LabValue";
where += "AND (ehrlabresult" + i + ".ObservationValueCodedElementID='" + elementList[i].LabValue + "' OR "
+ "ehrlabresult" + i + ".ObservationValueCodedElementIDAlt='" + elementList[i].LabValue + "') "
+ "AND (ehrlabresult" + i + ".ValueType='CWE' OR ehrlabresult" + i + ".ValueType='CE') ";
break;
//DT is stored as a string in ehrlabresult.ObservationValueDateTime as YYYY[MM[DD]]
case HL70125.DT:
select += ",ehrlabresult" + i + ".ObservationValueDateTime "; //+DbHelper.DateFormatColumn("RPAD(ehrlabresult"+i+".ObservationValueDateTime,8,'01')","%m/%d/%Y");
where += "AND " + DbHelper.DtimeToDate("RPAD(ehrlabresult" + i + ".ObservationValueDateTime,8,'01')")
+ GetOperandText(elementList[i].Operand) + "'" + POut.String(elementList[i].LabValue) + "' "
+ "AND ehrlabresult" + i + ".ValueType='DT' ";
break;
//TS is YYYYMMDDHHMMSS, string compare
case HL70125.TS:
select += ",ehrlabresult" + i + ".ObservationValueDateTime "; //+DbHelper.DateTFormatColumn("ehrlabresult"+i+".ObservationValueDateTime","%m/%d/%Y %H:%i:%s");
where += "AND ehrlabresult" + i + ".ObservationValueDateTime " //+POut.DateT(PIn.DateT(DbHelper.DateTFormatColumn("ehrlabresult"+i+".ObservationValueDateTime","%m/%d/%Y %H:%i:%s")))
+ GetOperandText(elementList[i].Operand) + "'" + POut.String(elementList[i].LabValue) + "' "
+ "AND ehrlabresult" + i + ".ValueType='TS' ";
break;
//00:00:00
case HL70125.TM:
select += ",ehrlabresult" + i + ".ObservationValueTime";
where += "AND ehrlabresult" + i + ".ObservationValueTime" + GetOperandText(elementList[i].Operand) + "'" + POut.TSpan(PIn.TSpan(elementList[i].LabValue)) + "' "
+ "AND ehrlabresult" + i + ".ValueType='TM' ";
break;
case HL70125.SN:
select += ",CONCAT(CONCAT(CONCAT(ehrlabresult" + i + ".ObservationValueComparator,ehrlabresult" + i + ".ObservationValueNumber1),ehrlabresult" + i + ".ObservationValueSeparatorOrSuffix),ehrlabresult" + i + ".ObservationValueNumber2)";
where += "AND ehrlabresult" + i + ".ValueType='SN' ";
break;
case HL70125.NM:
select += ",ehrlabresult" + i + ".ObservationValueNumeric";
where += "AND ehrlabresult" + i + ".ObservationValueNumeric" + GetOperandText(elementList[i].Operand) + POut.Double(PIn.Double(elementList[i].LabValue)) + " "
+ "AND ehrlabresult" + i + ".ValueType='NM' ";
break;
case HL70125.FT:
case HL70125.ST:
case HL70125.TX:
select += ",ehrlabresult" + i + ".ObservationValueText";
//where+="AND ehrlabresult"+i+".ObservationValueText"+GetOperandText(elementList[i].Operand)+POut.String(elementList[i].LabValue)+" "
where += "AND (ehrlabresult" + i + ".ValueType='FT' OR ehrlabresult" + i + ".ValueType='ST' OR ehrlabresult" + i + ".ValueType='TX') ";
break;
}
select += ",ehrlabresult" + i + ".ObservationDateTime ";
//select+=",labresult"+i+".ObsValue,labresult"+i+".DateTimeTest";//format column name when filling grid.
//from+=",labresult AS labresult"+i+", labpanel AS labpanel"+i;
//where+="AND labpanel"+i+".LabpanelNum=labresult"+i+".LabpanelNum AND patient.PatNum=labpanel"+i+".PatNum ";//join
//where+="AND labresult"+i+".TestId='"+elementList[i].CompareString+"' "
// +"AND labresult"+i+".ObsValue"+GetOperandText(elementList[i].Operand)+"'"+PIn.String(elementList[i].LabValue)+"' ";//filter
//if(elementList[i].StartDate!=null && elementList[i].StartDate.Year>1880) {
// where+="AND labresult"+i+".DateTimeTest>"+POut.Date(elementList[i].StartDate)+" ";//after this date
//}
//if(elementList[i].EndDate!=null && elementList[i].EndDate.Year>1880) {
// where+="AND labresult"+i+".DateTimeTest<"+POut.Date(elementList[i].EndDate)+" ";//before this date
//}
break;
case EhrRestrictionType.Medication: //--------------------------------------------------------------------------------------------------------------------------
select += ",medicationpat" + i + ".DateStart"; //Name of medication will be in column title.
from += ",medication AS medication" + i + ", medicationpat AS medicationpat" + i;
where += "AND medicationpat" + i + ".PatNum=patient.PatNum "; //join
//This is unusual. Part of the join logic is in the code below because medicationPat.MedicationNum might be 0 if it came from newcrop.
where += "AND ((medication" + i + ".MedicationNum=MedicationPat" + i + ".MedicationNum AND medication" + i + ".MedName LIKE '%" + PIn.String(elementList[i].CompareString) + "%') "
+ " OR (medication" + i + ".MedicationNum=0 AND medicationpat" + i + ".MedDescript LIKE '%" + PIn.String(elementList[i].CompareString) + "%')) ";
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND medicationpat" + i + ".DateStart>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND medicationpat" + i + ".DateStart<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.Problem: //-----------------------------------------------------------------------------------------------------------------------------
select += ",disease" + i + ".DateStart"; //Name of problem will be in column title.
from += ",disease AS disease" + i + ", diseasedef AS diseasedef" + i;
where += "AND diseasedef" + i + ".DiseaseDefNum=disease" + i + ".DiseaseDefNum AND disease" + i + ".PatNum=patient.PatNum "; //join
where += "AND (diseasedef" + i + ".ICD9Code='" + PIn.String(elementList[i].CompareString) + "' OR diseasedef" + i + ".SnomedCode='" + PIn.String(elementList[i].CompareString) + "') "; //filter
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND disease" + i + ".DateStart>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND disease" + i + ".DateStart<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.Allergy: //-----------------------------------------------------------------------------------------------------------------------------
select += ",allergy" + i + ".DateAdverseReaction"; //Name of allergy will be in column title.
from += ",allergy AS allergy" + i + ", allergydef AS allergydef" + i;
where += "AND allergydef" + i + ".AllergyDefNum=allergy" + i + ".AllergyDefNum AND allergy" + i + ".PatNum=patient.PatNum "; //join
where += "AND allergydef" + i + ".Description='" + PIn.String(elementList[i].CompareString) + "' "; //filter
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND allergy" + i + ".DateAdverseReaction>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND allergy" + i + ".DateAdverseReaction<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.CommPref: //----------------------------------------------------------------------------------------------------------------------------
select += ",patient.PreferContactConfidential";
from += ""; //only selecting from patient table
where += "AND patient.PreferContactConfidential=" + PIn.Int(contactMethodHelper(elementList[i].CompareString)) + " ";
break;
default:
//should never happen.
continue;
}
}
string command = select + " " + from + " " + where;
return(Db.GetTable(command));
}
///<summary>Used when printing or emailing recall to make a commlog entry without any display.
///Set commSource to the corresponding entity that is making this recall. E.g. Web Sched.
///If the commSource is a 3rd party, set it to ProgramLink and make an overload that accepts the ProgramNum.</summary>
public static Commlog InsertForRecall(long patNum, CommItemMode _mode, int numberOfReminders, long defNumNewStatus, CommItemSource commSource,
long userNum, DateTime dateTimeNow)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <Commlog>(MethodBase.GetCurrentMethod(), patNum, _mode, numberOfReminders, defNumNewStatus, commSource, userNum, dateTimeNow));
}
long recallType = Commlogs.GetTypeAuto(CommItemTypeAuto.RECALL);
string command;
string datesql = "CURDATE()";
if (DataConnection.DBtype == DatabaseType.Oracle)
{
datesql = "(SELECT CURRENT_DATE FROM dual)";
}
if (recallType != 0)
{
command = "SELECT * FROM commlog WHERE ";
command += DbHelper.DtimeToDate("CommDateTime") + " = " + datesql;
command += " AND PatNum=" + POut.Long(patNum) + " AND CommType=" + POut.Long(recallType)
+ " AND Mode_=" + POut.Long((int)_mode)
+ " AND SentOrReceived=1";
List <Commlog> listComms = Crud.CommlogCrud.SelectMany(command).OrderByDescending(x => x.CommDateTime).ToList();
if (listComms.Count > 0)
{
return(listComms[0]);
}
}
Commlog com = new Commlog();
com.PatNum = patNum;
com.CommDateTime = dateTimeNow;
com.CommType = recallType;
com.Mode_ = _mode;
com.SentOrReceived = CommSentOrReceived.Sent;
com.Note = "";
if (numberOfReminders == 0)
{
com.Note = Lans.g("FormRecallList", "Recall reminder.");
}
else if (numberOfReminders == 1)
{
com.Note = Lans.g("FormRecallList", "Second recall reminder.");
}
else if (numberOfReminders == 2)
{
com.Note = Lans.g("FormRecallList", "Third recall reminder.");
}
else
{
com.Note = Lans.g("FormRecallList", "Recall reminder:") + " " + (numberOfReminders + 1).ToString();
}
if (defNumNewStatus == 0)
{
com.Note += " " + Lans.g("Commlogs", "Status None");
}
else
{
com.Note += " " + Defs.GetName(DefCat.RecallUnschedStatus, defNumNewStatus);
}
com.UserNum = userNum;
com.CommSource = commSource;
com.CommlogNum = Insert(com);
EhrMeasureEvent newMeasureEvent = new EhrMeasureEvent();
newMeasureEvent.DateTEvent = com.CommDateTime;
newMeasureEvent.EventType = EhrMeasureEventType.ReminderSent;
newMeasureEvent.PatNum = com.PatNum;
newMeasureEvent.MoreInfo = com.Note;
EhrMeasureEvents.Insert(newMeasureEvent);
return(com);
}
///<summary>Called from FormRpOutstandingIns. Gets outstanding insurance claims. Requires all fields. provNumList may be empty (but will return null if isAllProv is false). listClinicNums may be empty. dateMin and dateMax will not be used if they are set to DateTime.MinValue() (01/01/0001). If isPreauth is true only claims of type preauth will be returned.</summary>
public static List <OutstandingInsClaim> GetOutInsClaims(List <long> listProvNums, DateTime dateFrom,
DateTime dateTo, bool isPreauth, List <long> listClinicNums, string carrierName, List <long> listUserNums, DateFilterBy dateFilterBy)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <OutstandingInsClaim> >(MethodBase.GetCurrentMethod(), listProvNums, dateFrom,
dateTo, isPreauth, listClinicNums, carrierName, listUserNums, dateFilterBy));
}
string command = @"
SELECT carrier.CarrierName,
carrier.Phone carrierPhone,
claim.ClaimType,
claim.DateService,
claim.DateSent,
claim.DateSentOrig DateOrigSent,
claim.ClaimFee,
claim.ClaimNum,
claim.ClinicNum,
insplan.GroupNum,
insplan.GroupName,
inssub.SubscriberID SubID,"
+ DbHelper.Concat("sub.LName", "', '", "sub.FName") + @" SubName,
sub.Birthdate SubDOB,
patient.FName PatFName,
patient.LName PatLName,
patient.MiddleI PatMiddleI,
patient.PatNum,
patient.Birthdate PatDOB,
definition.ItemValue DaysSuppressed,"
+ DbHelper.DtimeToDate("statusHistory.DateTimeEntry") + @" DateLog,
definition.DefNum CustomTrackingDefNum,
statusHistory.TrackingErrorDefNum ErrorCodeDefNum,
COALESCE(claimtracking.UserNum,0) UserNum
FROM carrier
INNER JOIN insplan ON insplan.CarrierNum=carrier.CarrierNum
INNER JOIN claim ON claim.PlanNum=insplan.PlanNum
AND claim.ClaimStatus='S' " ;
if (dateFrom != DateTime.MinValue)
{
if (dateFilterBy == DateFilterBy.DateSentOrig)
{
command += "AND claim.DateSentOrig >= " + POut.Date(dateFrom) + " ";
}
else if (dateFilterBy == DateFilterBy.DateSent)
{
command += "AND claim.DateSent >= " + POut.Date(dateFrom) + " ";
}
else
{
command += "AND claim.DateService >= " + POut.Date(dateFrom) + " ";
}
}
if (dateTo != DateTime.MinValue)
{
if (dateFilterBy == DateFilterBy.DateSentOrig)
{
command += "AND claim.DateSentOrig <= " + POut.Date(dateTo) + " ";
}
else if (dateFilterBy == DateFilterBy.DateSent)
{
command += "AND claim.DateSent <= " + POut.Date(dateTo) + " ";
}
else
{
command += "AND claim.DateService <= " + POut.Date(dateTo) + " ";
}
}
if (listProvNums.Count > 0)
{
command += "AND claim.ProvTreat IN (" + String.Join(",", listProvNums) + ") ";
}
if (listClinicNums.Count > 0)
{
command += "AND claim.ClinicNum IN (" + String.Join(",", listClinicNums) + ") ";
}
if (!isPreauth)
{
command += "AND claim.ClaimType!='Preauth' ";
}
command += "LEFT JOIN claimtracking ON claimtracking.ClaimNum=claim.ClaimNum AND TrackingType='" + POut.String(ClaimTrackingType.ClaimUser.ToString()) + "' ";
command += "LEFT JOIN definition ON definition.DefNum=claim.CustomTracking "
+ "LEFT JOIN claimtracking statusHistory ON statusHistory.ClaimNum=claim.ClaimNum "
+ "AND statusHistory.TrackingDefNum=definition.DefNum "
+ "AND statusHistory.DateTimeEntry=(SELECT MAX(ct.DateTimeEntry) FROM claimtracking ct WHERE ct.ClaimNum=claim.ClaimNum AND ct.TrackingDefNum!=0) "
+ "AND statusHistory.TrackingType='" + POut.String(ClaimTrackingType.StatusHistory.ToString()) + "' "
+ "INNER JOIN patient ON patient.PatNum=claim.PatNum "
+ "LEFT JOIN inssub ON claim.InsSubNum = inssub.InsSubNum "
+ "LEFT JOIN patient sub ON inssub.Subscriber = sub.PatNum "
+ "WHERE carrier.CarrierName LIKE '%" + POut.String(carrierName.Trim()) + "%' ";
if (listUserNums.Count > 0)
{
command += "AND (claimtracking.UserNum IN (" + String.Join(",", listUserNums) + ") ";
if (listUserNums.Contains(0))
{
//Selected users includes 'Unassigned' so we want to allow claims without associated claimTracking rows to show.
command += " OR claimtracking.UserNum IS NULL";
}
command += ") ";
}
command += "ORDER BY carrier.CarrierName,claim.DateService,patient.LName,patient.FName,claim.ClaimType";
object[] parameters = { command };
Plugins.HookAddCode(null, "Claims.GetOutInsClaims_beforequeryrun", parameters); //Moved entire method from Claims.cs
command = (string)parameters[0];
DataTable table = Db.GetTable(command);
List <OutstandingInsClaim> listOutstandingInsClaims = table.Rows.OfType <DataRow>().Select(x => new OutstandingInsClaim(x)).ToList();
return(listOutstandingInsClaims);
}
///<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));
}
