//AM Issue#32875 03/26/2008 Build Number 1.0.0.9
public DataTable getTestDeletionDetails(string accountNumber, string accessionNumber, string dateDeletedFrom, string dateDeletedTo, string department, string location, string status, string reason, string EnteredBy, String checkedBy)
{
DataTable returnDataTable = new DataTable();
string selectStatement = "SELECT TD_RowID AS TESTDELROWID,ACC_Accession AS ACCESSIONNUM,TD_AccessionDR->ACC_ClientDR->CLF_CLNUM AS ACCOUNTNUM,LABLO_LabName AS LABLOCATION,TO_DATE(TD_DateEntered,'MM/dd/yyyy') AS DATETIMESTAMP, TD_TimeEntered AS TIMEENTERED,TD_TestsToDelete AS TESTDELETED,TD_ProcessingStatus AS STATUS, TD_ProcessingStatus AS STATUSCODE,TD_DepartmentDR->DEPT_Name AS DEPARTMENT,TD_REASONFORDELETIONDR AS REASON, $$CO17^XT58(TD_UserPrintedBy) AS PRINTEDBY, $$CO17^XT58(TD_UserEnteredBy) ENTEREDBY";
selectStatement = selectStatement + " FROM ORD_TestDeletion";
selectStatement = selectStatement + " left outer join DIC_LabLocation on TD_LabLocationDR=LABLO_RowID";
selectStatement = selectStatement + " left outer join ORD_Accession on TD_AccessionDR=ACC_Accession";
selectStatement = selectStatement + " left outer join DIC_ReasonForDeletion on TD_REASONFORDELETIONDR=RDEL_ReasonID";
selectStatement = selectStatement + " WHERE 1=1";
selectStatement = (accountNumber != "" ? selectStatement + " AND TD_AccessionDR->ACC_ClientDR->CLF_CLNUM ='" + accountNumber + "'" : selectStatement);
selectStatement = (accessionNumber != "" ? selectStatement + " AND ACC_Accession ='" + accessionNumber + "'" : selectStatement);
selectStatement = (department != "" ? selectStatement + " AND TD_DepartmentDR ='" + department + "'" : selectStatement);
selectStatement = (location != "" ? selectStatement + " AND LABLO_RowID ='" + location + "'" : selectStatement);
selectStatement = (reason != "" ? selectStatement + " AND TD_REASONFORDELETIONDR ='" + reason + "'" : selectStatement);
selectStatement = (status != "" ? selectStatement + " AND TD_ProcessingStatus ='" + status + "'" : selectStatement);
selectStatement = (dateDeletedFrom != "" ? selectStatement + " AND TD_DateEntered>= TO_DATE('" + dateDeletedFrom + "','MM/dd/yyyy') AND TD_DateEntered<= TO_DATE('" + dateDeletedTo + "','MM/dd/yyyy')" : selectStatement);
selectStatement = (EnteredBy != "" ? selectStatement + " AND %SQLUPPER TD_UserEnteredBy LIKE %SQLUPPER '" + EnteredBy + "'" : selectStatement); //AM Issue#46178 1.0.0.18 10/13/2008 - adding new search criteria 'Entered By'
if (checkedBy.Length > 0)
{
selectStatement = selectStatement + "AND %SQLUPPER TD_UserPrintedBy LIKE %SQLUPPER '" + checkedBy + "'";
}
selectStatement = selectStatement + " ORDER BY TD_RowID ASC";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
returnDataTable = cache.FillCacheDataTable(selectStatement);
return(returnDataTable);
}
public static String insertTestDeletion(string Accession, string DateEntered, string Department, string LabLocation, string ReasonForDeletion, string TestsToDeleteString, string Comments, string ProcessingStatus, string UserEnteredBy)
{
//System.Text.StringBuilder sb = new System.Text.StringBuilder();
//sb.Append("Insert into ORD_TestDeletion (TD_AccessionDR,TD_DateEntered,TD_DepartmentDR, TD_LabLocationDR, TD_REASONFORDELETIONDR, TD_TestsToDelete, TD_Comments,TD_ProcessingStatus, TD_UserEnteredBy) VALUES( ");
//sb.Append("'" + Accession + "'" + ", ");
//string EnteredDate = "TO_DATE('" + DateEntered + "','MM/DD/YYYY')";
//sb.Append(EnteredDate + ", ");
//sb.Append("'" + Department + "'" + ", ");
//sb.Append("'" + LabLocation + "'" + ", ");
//sb.Append("'" + ReasonForDeletion + "'" + ", ");
//sb.Append("'" + TestsToDeleteString + "'" + ", ");
//sb.Append("'" + Comments + "'" + ", ");
//sb.Append("'" + ProcessingStatus + "'" + ",");
//sb.Append("'" + UserEnteredBy + "')");
//string insertStatement = sb.ToString();
//CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
//return Convert.ToString(cache.Insert(insertStatement));
Dictionary <string, string> _testDeletionData = new Dictionary <string, string>();
_testDeletionData.Add("ACCESSION", Accession);
_testDeletionData.Add("DEPARTMENT", Department);
_testDeletionData.Add("LAB", LabLocation);
_testDeletionData.Add("REASON", ReasonForDeletion);
_testDeletionData.Add("COMMENTTEXT", Comments);
_testDeletionData.Add("TESTS", TestsToDeleteString);
_testDeletionData.Add("USER", UserEnteredBy);
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.StoredProcedure("?=call SP2_SaveTestDeletion(?,?,?,?,?,?,?)", _testDeletionData).Value.ToString());
}
public static DataTable getRelatedAccessions(String PetName, String owner, String DateFrom, String DateTo, String AccountNo)
{
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
#region Preparing Query
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT ");
sb.Append("ACC_Accession,");
sb.Append("ACC_OwnerLastName||', '||ACC_PetFirstName AS PATIENT,");
sb.Append("ACC_TestsOrderedDisplayString AS TESTSORDERED,");
sb.Append("ACC_ClientDR->CLF_CLNUM AS ACCOUNTNUM,");
sb.Append("ACC_MiniLogDate As ORDERDATE,");
sb.Append("ACC_MiniLogTime As ORDERTIME ");
sb.Append("FROM ");
sb.Append("ORD_Accession ");
sb.Append("WHERE ");
sb.Append("ACC_ClientDR->CLF_CLNUM = '");
sb.Append(AccountNo);
sb.Append("' AND ACC_OwnerLastName ='");
sb.Append(owner);
sb.Append("' AND ACC_PetFirstName = '");
sb.Append(PetName);
sb.Append("' AND ACC_MiniLogDate>= {d '");
sb.Append(DateTime.Parse(DateFrom).ToString("yyyy-MM-dd"));
sb.Append("'} AND ACC_MiniLogDate<={d '");
sb.Append(DateTime.Parse(DateTo).ToString("yyyy-MM-dd"));
sb.Append("'}");
sb.Append(" AND $$ISCONSULT^XT11(ACC_Accession)='0' ");
sb.Append(" ORDER BY ACC_MiniLogDate DESC, ACC_MiniLogTime DESC");
#endregion Preparing Query
return(cache.FillCacheDataTable(sb.ToString()));
}
public static DataTable getClientAtRisk(string strDateFrom, string strDateTo, string groupString, out string sessionID)
{
Dictionary <String, String> _getBICAR = new Dictionary <String, String>();
_getBICAR.Add("FROMDATE", strDateFrom);
_getBICAR.Add("TODATE", strDateTo);
_getBICAR.Add("GROUPSTRING", groupString);
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
sessionID = cache.StoredProcedure("?=call SP2_ClientAtRisk(?,?,?)", _getBICAR).Value.ToString();
DataTable returnDataTable = new DataTable();
StringBuilder sb = new StringBuilder();
if (sessionID.Length > 0)
{
sb.Append("Select BICAR_RowID, BICAR_ClientDR, BICAR_ClientDR->CLF_CLNUM As AccountNumber, ");
sb.Append("BICAR_ClientDR->CLF_CLNAM As AccountName, ");
sb.Append("BICAR_ClientDR->CLF_CLPHN As AccountPhone, ");
sb.Append("BICAR_ClientDR->CLF_CLSRT As Territory, ");
sb.Append("BICAR_ClientDR->CLF_CLSRT->ST_SalesRepName As SalesRepName, ");
sb.Append("$$GETREVHIST^XT1(BICAR_ClientDR) AS ClientRevenue");
sb.Append(" From");
sb.Append(" BI_TMP_ClientsAtRisk");
sb.Append(" WHERE 1=1");
sb.Append(String.Concat(" AND BICAR_SessionID=", sessionID, ""));
sb.Append(" AND BICAR_AtRisk='Y'");
cache = new CACHEDAL.ConnectionClass();
returnDataTable = cache.FillCacheDataTable(sb.ToString());
}
return(returnDataTable);
}
public static DataTable getAccessionDetailsExtended_OnlyforTestName(String TestCode, Int32 noOfRecords, String pageName, Int32 timeout, out Boolean isError)
{
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
#region Preparing Query
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT TOP ");
sb.Append(noOfRecords.ToString());
sb.Append(" ACC_Accession,");
sb.Append("ACC_OwnerLastName||', '||ACC_PetFirstName AS PATIENT,"); // Last Name + First Name
sb.Append("ACC_TestsOrderedDisplayString AS TESTSORDERED,");
sb.Append("ACC_ClientDR->CLF_CLNUM AS ACCOUNTNUM,");
sb.Append("ACC_MiniLogDate As ORDERDATE,");
sb.Append("ACC_MiniLogTime As ORDERTIME,");
sb.Append("ACC_ReportStatus As ReportStatus,");
sb.Append("ACC_IsStat As StatDesignation,");
sb.Append("$$GETINQNOTE^XT11(ACC_Accession) As InqNotes ");
sb.Append("FROM ");
sb.Append("ORD_Accession ");
sb.Append("WHERE ");
sb.Append("1=1");
sb.Append(" AND (ACC_TestsOrderedDisplayString ='" + TestCode + "' OR ACC_TestsOrderedDisplayString %STARTSWITH '" + TestCode + ",' OR ACC_TestsOrderedDisplayString LIKE '%, " + TestCode + "' OR ACC_TestsOrderedDisplayString LIKE '%, " + TestCode + ", %')");
String selectStatement = sb.ToString();
#endregion Preparing Query
return(cache.FillCacheDataTable(selectStatement, pageName, timeout, out isError));
}
public static DataTable getAccessionDetailsByAccessionNumber(String AccessionNumber, String pageName, Int32 timeout, out Boolean isError)
{
#region Preparing Query
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT ");
sb.Append("ACC_Accession,");
sb.Append("ACC_PatientName AS PATIENT,"); //SSM 13/10/2011 Issue#114139 Commented for passing the formatted patient name
sb.Append("ACC_OwnerLastName||', '||ACC_PetFirstName AS PATIENTFULLNAME,"); //SSM 18/10/2011 #Issue114138 Modified fieldName for not to interrupt other feature
sb.Append("ACC_TestsOrderedDisplayString AS TESTSORDERED,");
sb.Append("ACC_ClientDR->CLF_CLNUM AS ACCOUNTNUM,");
sb.Append("ACC_MiniLogDate As ORDERDATE,");
sb.Append("ACC_MiniLogTime As ORDERTIME,");
sb.Append("ACC_ReportStatus As ReportStatus,");
sb.Append("ACC_IsStat As StatDesignation,");
sb.Append("$$GETINQNOTE^XT11(ACC_Accession) As InqNotes ");
sb.Append("FROM ");
sb.Append("ORD_Accession ");
sb.Append("WHERE ");
sb.Append("ACC_Accession ='" + AccessionNumber + "'");
string selectStatement = sb.ToString();
#endregion Preparing Query
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement, pageName, timeout, out isError));
}
public static Int32 AccessionCount(String accessionNumber)
{
String selectStatement = "SELECT COUNT(*) FROM ORD_Accession WHERE ACC_Accession = '" + accessionNumber + "'";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return((Int32)cache.CacheExScalar(selectStatement));
}
public static DataTable getEventsByClientID(String clientID)
{
#region Preparing SQL Statement
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT ");
sb.Append("EVENT_RowID AS EventRowId, ");
sb.Append("EVENT_DateLogged As EventLogDate, ");
sb.Append("EVENT_TimeLogged As EventLogTime, ");
sb.Append("$$CO17^XT58(EVENT_Agent) As EventAgent, ");
sb.Append("EVENT_Lab As EventLab, ");
sb.Append("EVENT_AccessionDR As AccessionNumber, ");
sb.Append("EVENT_CategoryDR As EventCategory, ");
sb.Append("EVENT_LogDesc As EventLogDescription, ");
sb.Append("EVENT_CategoryDR->EVECT_IsEditable AS IsEditable ");
sb.Append("FROM ");
sb.Append("CLF_ClientEventLog ");
sb.Append("WHERE ");
sb.Append("EVENT_CLF_ParRef->CLF_CLNUM = '" + clientID + "'");
sb.Append(" ORDER BY ");
sb.Append("EVENT_DateLogged DESC, EVENT_TimeLogged DESC, EVENT_RowID DESC");
#endregion Preparing SQL Statement
string selectStatement = sb.ToString();
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement));
}
internal static DataTable getUnitCodeDetails(string unitCode)
{
string strSQL = "SELECT UC_RowID,UC_OrderingMnemonics,UC_ReportingTitle,UC_StandardQuantity,UC_OrderAs,UC_OrderingNotes,UC_CategoryDR->UCC_CategoryName As CAT FROM DIC_UNITCODE WHERE UC_RowID='" + unitCode + "'";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(strSQL));
}
public static DataTable getUnitDetails(String unitCode)
{
String selectStatement = "SELECT UC_Mnemonics As UnitCodeMnemonic, UC_DisplayReportingTitle As ReportingTitle, UC_Alias As Alias, UC_SpecimentType As SpecimenType, UC_Worklist As Worklist, UC_TestCodes As TestCodes, UC_ReferralToNode As ReferredToNode, UC_ReferredtoName As ReferredToName, UC_ReferredToAddress As ReferredToAddress, UC_ReferenceLabID As ReferenceLabID, UC_PathologistReview As PathologistReview, UC_SpecimenRequirements As SpecimenRequirements, UC_ContainerType As ContainerType, UC_OtherAcceptableSpecReq As AcceptableSpecimenRequirement, UC_TransportTemp As TransportTemperature, UC_MinimumSpecimenVolume As MinimumSpecimenVolume, UC_LabArea As LabArea, UC_AnalyticTime As AnalyticTime, UC_DaysTestSetUp As DaysTestSetUp, UC_TimeOfDay As TimeOfDay, UC_MaxLabTime As MaxLabTime, UC_Methodology As Methodology, UC_RefRangeAndUnitofMeas As ReferenceRangeAndUOM, UC_PanicValues As PanicValues, UC_SpecimenStability As SpecimenStability, UC_rejectDuetoHemolysis As RejectDuetoHemolysis, UC_RejectDuetoLipemia As RejectDuetoLipemia, UC_RejectDuetoThawingOranyotherRejection As RejectDueToThawing, UC_RoutineInstructionsForInquiry As RoutineInstructionsForInquiry, UC_SpecialInstructForDrawList As SpecialInstructForDrawList, UC_SpecimenRetention As SpecimenRetention, UC_ClinicalSignificance As ClinicalSignificance, UC_PrincOfAssayAndJournRef As PrincOfAssayAndJournRef, UC_CPTCode As CPTCode, UC_FEE As Fee, UC_EtiologicAgent As EtiologicAgent, UC_CapWorkUnits As CapWorkUnits, UC_NumberOfCollectLabels As NumberOfCollectionLabels, UC_AlwaysMessage As AlwaysMessageCode, UC_AlwaysMessage->MSG_MessageText As AlwaysMessage, UC_ReportSequenceForReport As ReportSequenceForReport, UC_Category As Category, UC_AlwaysMessageEffectiveDate As AlwaysMessageEffectiveDate,UC_Status As Status,UC_OrderEntryNoteOE As OrderEntryNoteOE FROM DIC_UnitCode where UC_UnitCode = '" + unitCode + "'";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement));
}
internal static DataTable getUnitCodeByCategory(string category)
{
string strSQL = "SELECT UC_RowID,UC_ReportingTitle FROM DIC_UNITCODE WHERE UC_CategoryDR='" + category + "'";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(strSQL));
}
internal static DataTable getUCCategory()
{
string strSQL = "SELECT UCC_CategoryCode,UCC_CategoryName FROM DIC_UCCategory ORDER BY UCC_CategoryName";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(strSQL));
}
public static DataTable getTransitRecords(string strAccountNo, string strConfNo, string strFromDate, string strToDate)
{
DataTable returnDataTable = new DataTable();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
sb.Append("ADDON_ConfirmationNumber As RowId,ADDON_ClientDR->CLF_CLNUM As Account,");
sb.Append("ADDON_LabLocationDR As LabId,");
sb.Append("ADDON_LabLocationDR->LABLO_LabName As LabName,");
sb.Append("ADDON_Email,");
sb.Append("ADDON_Date,");
sb.Append("ADDON_Time,");
sb.Append("ADDON_User,");
sb.Append("ADDON_RequestType,");
sb.Append("ADDON_CallerName,");
sb.Append("CLF_CLNAM AS AccountName,");
sb.Append("CLF_CLMNE AS AccountMnemonic,");
sb.Append("CLF_CLPHN As ClientPhone,");
sb.Append("CLF_CLAD1 AS ClientAddress1,");
sb.Append("CLF_CLAD2 AS ClientAddress2,");
sb.Append("CLF_SalesTerritoryDR->ST_TerritoryCode AS SalesTerritory,");
sb.Append("CLF_IsHot As ClientIsHot,");
sb.Append("CLF_IsAlliedClient As ClientIsAllied,");
sb.Append("CLF_SalesTerritoryDR->ST_SalesRepName AS SalesRepresentative,");
sb.Append("CLF_CLADG AS AutodialGroup,");
sb.Append("CLF_IsNew AS ClientIsNew,");
sb.Append("CLF_CLRTS AS RouteStop,");
sb.Append("zoasis_num AS Zoasis,");
sb.Append("$$GETREVHIST^XT1(ADDON_ClientDR) AS ClientRevenue");
sb.Append(" FROM");
sb.Append(" ORD_AddOn");
sb.Append(" LEFT OUTER JOIN");
sb.Append(" CLF_ClientFile Client on ADDON_ClientDR = Client.CLF_CLMNE ");
sb.Append(" LEFT OUTER JOIN ");
sb.Append("zoasis ON Client.CLF_CLNUM = zoasis.CLN_RowID ");
sb.Append("WHERE 1=1");
sb.Append(" AND ADDON_RequestType='X'");
if (strAccountNo.Length > 0)
{
sb.Append(String.Concat(" AND ADDON_ClientDR->CLF_CLNUM ='", strAccountNo, "'"));
}
if (strFromDate.Length > 0)
{
sb.Append(String.Concat(" AND ADDON_Date>= TO_DATE('", strFromDate, "','MM/dd/yyyy')"));
}
if (strToDate.Length > 0)
{
sb.Append(String.Concat(" AND ADDON_Date<= TO_DATE('", strToDate, "','MM/dd/yyyy')"));
}
if (strConfNo.Length > 0)
{
sb.Append(String.Concat(" AND ADDON_ConfirmationNumber='", strConfNo, "'"));
}
sb.Append(String.Concat(" ORDER BY ADDON_Date DESC, ADDON_Time DESC"));
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
returnDataTable = cache.FillCacheDataTable(sb.ToString());
return(returnDataTable);
}
public static DataTable getCITDetails(string strRowID)
{
DataTable returnDataTable = new DataTable();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
sb.Append("ADDON_ConfirmationNumber As ConfirmationNumber, ");
sb.Append("ADDON_CallerName As CallerName, ");
sb.Append("ADDON_Date As RequestDate, ");
sb.Append("ADDON_Time As RequestTime, ");
sb.Append("ADDON_RequestType As RequestType, ");
sb.Append("ADDON_Email As Email, ");
sb.Append("ADDON_SpecialInstructions As SpecialInstructions, ");
sb.Append("ADDON_Tests As Tests, ");
sb.Append("ADDON_LabLocationDR As LabID, ");
sb.Append("ADDON_LabLocationDR->LABLO_LabName As LabLocation, ");
sb.Append("ADDON_User As UserValue, ");
sb.Append("ADDON_ClientDR->CLF_CLNUM As Account ");
sb.Append("FROM ORD_AddOn");
sb.Append(" WHERE ADDON_ConfirmationNumber = '" + strRowID + "'");
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
returnDataTable = cache.FillCacheDataTable(sb.ToString());
return(returnDataTable);
}
public static Int32 updateAddOnVerificationRequests(String confirmationID, String callerName, String changeRequestType, String email, String labLocation, String specialInstructions, String addOnTests, String userID, String ReasonForReq, String PathologistRequested, String OriginalPathologist, String CheckTubeType, bool isModify)
{
#region ObsoleteCode
/*System.Text.StringBuilder sb = new System.Text.StringBuilder();
* sb.Append("UPDATE ORD_ADDON SET ");
* sb.Append("ADDON_CALLERNAME = '" + callerName + "',");
* sb.Append(" ADDON_RequestType = '" + changeRequestType + "',");
* sb.Append(" ADDON_Email = '" + email + "',");
* sb.Append(" ADDON_LabLocationDR = '" + labLocation + "',");
* sb.Append(" ADDON_SpecialInstructions = '" + specialInstructions + "',");
* sb.Append(" ADDON_Tests = '" + addOnTests + "',");
* sb.Append(" ADDON_User = '******'");
* sb.Append(" WHERE ADDON_ConfirmationNumber = '" + confirmationID + "'");
* CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
* return cache.Insert(sb.ToString());*/
#endregion ObsoleteCode
Dictionary <String, String> _addonReport = new Dictionary <String, String>();
_addonReport.Add("ConfirmationNumber", confirmationID);
_addonReport.Add("Caller", callerName);
_addonReport.Add("RequestType", changeRequestType);
_addonReport.Add("Email", email);
_addonReport.Add("Lab", labLocation);
_addonReport.Add("SpecialInst", specialInstructions);
_addonReport.Add("Tests", addOnTests);
_addonReport.Add("UserID", userID);
_addonReport.Add("ReasonForReq", ReasonForReq);
_addonReport.Add("PathologistRequested", PathologistRequested);
_addonReport.Add("OriginalPathologist", OriginalPathologist);
_addonReport.Add("CheckTubeType", CheckTubeType);
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(Convert.ToInt32(cache.StoredProcedure("?=call SP2_UpdateAddon(?,?,?,?,?,?,?,?,?,?,?,?)", _addonReport).Value));
}
public static DataTable getRecentIssuesByClientMnemonic(Int32 noOfTopRecords, String clientMnemonic)
{
#region Prepare SQL Statement
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT ");
sb.Append("TOP " + noOfTopRecords.ToString());
sb.Append(" PBT_PSQ As ProblemID, ");
sb.Append("PBT_ProbTP->PTYP_ProblemType As ProblemCategory, ");
sb.Append("$$CO17^XT58(PBT_LOGID) As ProblemEnteredBy, ");
sb.Append("$$CO17^XT58(PBT_RESID) As ProblemResolvedBy, ");
sb.Append("PBT_Comment As ProblemDescription, ");
sb.Append("PBT_AccessionDR->ACC_Accession As ProblemAccessionNo, ");
sb.Append("PBT_LoggedDate As ProblemLoggedDate, ");
sb.Append("PBT_LoggedTime As ProblemLoggedTime, ");
sb.Append("PBT_RESDate As ProblemResolutionDate, ");
sb.Append("PBT_RESTime As ProblemResolutionTime, ");
sb.Append("PBT_AccessionDR->ACC_TestsOrderedDisplayString As TestOrdered, ");
sb.Append("PBT_Resolution As ProblemResolution ");
sb.Append("FROM ");
sb.Append("CSV_PROBTRACKING ");
sb.Append("WHERE ");
sb.Append("PBT_CLDesRef = '" + clientMnemonic + "'");
sb.Append("ORDER BY ");
sb.Append("PBT_LoggedDate DESC, PBT_LoggedTime DESC, PBT_ROWID DESC");
String selectStatement = sb.ToString();
#endregion Prepare SQL Statement
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement));
}
public static void insertTestDeletion(string Accession, string DateEntered, string Department, string LabLocation, string ReasonForDeletion, string Comments, string TestsToDeleteString, string userID)
{
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
cache.StoredProcedure("call SP_SaveTestDeletion(?,?,?,?,?,?,?,?)", Accession, DateEntered, Department, LabLocation, ReasonForDeletion, Comments, TestsToDeleteString, userID);
//string returnValue = cache.StoredProcedure("?=call SP_getCurrDate()", Microsoft.Data.Odbc.OdbcType.NVarChar).Value.ToString();
}
public static DataTable getMissingBeforeAccnDetails(string rowId)
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append("SELECT ");
sbSQL.Append("MISSS_RowID AS RowId,");
sbSQL.Append("MISSS_EnteredByUserDR->USER_UserID AS EnteredByUser,");
sbSQL.Append("MISSS_ClientDR AS AccountMnemonic,");
sbSQL.Append("MISSS_ContactPerson AS ContactPerson,");
sbSQL.Append("MISSS_AlternateContactNumber AS AltContactNo,");
sbSQL.Append("MISSS_DateRangeSubmitted AS DateSubmitted,");
sbSQL.Append("MISSS_OutcomeStatus AS OutcomeStatus,");
sbSQL.Append("%INTERNAL(MISSS_ProcessingStatus) AS ProcessingStatus,");
sbSQL.Append("MISSS_CheckedOutByUserDR AS CheckOutBy,");
sbSQL.Append("%EXTERNAL(MISSS_DateEntered) As DateEntered,");
sbSQL.Append("%EXTERNAL(MISSS_TimeEntered) As TimeEntered,");
sbSQL.Append("%EXTERNAL(MISSS_TRFStatus) AS TRFStatusId,");
sbSQL.Append("MISSS_SamplesOnRouteSheet AS SamplesOnRouteSheet,");
sbSQL.Append("MISSS_SamplesOnSystem AS SamplesOnSystem,");
sbSQL.Append("MISSS_RouteStop AS RouteStop,");
sbSQL.Append("MISSS_MissingFromLabDR->LABLO_LabName AS MissingFromLab,");
sbSQL.Append("MISSS_MissingFromLabDR AS MissingFromLabId,");
sbSQL.Append("%EXTERNAL(MISSS_RequiresLabCAttention) AS ReqLabCAttn,");
sbSQL.Append("$$CO17^XT58(MISSS_EnteredByUserDR->USER_UserID) As EnteredByUserDispName");
sbSQL.Append(" FROM ORD_MissingSpecimen ");
sbSQL.Append("WHERE MISSS_RowID ='");
sbSQL.Append(rowId);
sbSQL.Append("'");
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(sbSQL.ToString()));
}
public static void UpdateAcknowledgmentFlag(string mailRowId)
{
string strSQL = "UPDATE DIC_UserMail SET MAIL_AcknowledgementRequested ='0' WHERE MAIL_RowID ='" + mailRowId + "'";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
cache.Transaction(strSQL);
}
public static DataTable getUnitsByName(String unitName)
{
String selectStatement = "SELECT UC_RowID As UnitCodeRowID,UC_UnitCode As UnitCode,UC_DisplayReportingTitle As UnitCodeName,UC_IsProfile As UnitCodeIsProfile,$$GETALTVALUE^XT27(UC_UnitCode)As UnitAltCode FROM DIC_UnitCode WHERE %SQLUPPER UC_DisplayReportingTitle %STARTSWITH %SQLUPPER '" + unitName + "'";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement));
}
public static DataTable getUnitsByCombinedIndex(String clientID, String unitName, Int32 startIndex, Int32 noOfRecords)
{
String selectStatement = "SELECT UC_RowID As UnitCodeRowID,UC_UnitCode As UnitCode,UC_DisplayReportingTitle As UnitCodeName,UC_SpecimenRequirements As SpecimenRequirement,'Day(s) Test Set-up: '||UC_DaysTestSetUp||'<br>Time of Day: '||UC_TimeOfDay||'<br>Maximum Lab Time: '||UC_MaxLabTime As TestSchedule,SP_returnPrice('" + clientID + "',UC_UnitCode) As Price FROM DIC_UnitCode WHERE UC_SearchField %STARTSWITH '" + unitName + "'";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement, startIndex, noOfRecords));
}
public static DataTable getUnitsByCombinedIndex(String searchValue)
{
String selectStatement = "SELECT UC_RowID As UnitCodeRowID,UC_UnitCode As UnitCode,UC_DisplayReportingTitle As UnitCodeName,UC_IsProfile As UnitCodeIsProfile,$$GETALTVALUE^XT27(UC_UnitCode)As UnitAltCode FROM DIC_UnitCode WHERE UC_SearchField %STARTSWITH '" + searchValue + "'";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement));
}
public static DataTable getMessageDetailsByCode(String messageCode)
{
string selectStatement = "SELECT MSG_MessageText, MSG_AutoProblemComment,MSG_AutoProblemResolution, MSG_DefaultProblemCategoryDR, MSG_AutoInquiryNoteText FROM DIC_Message WHERE %SQLUPPER MSG_Code LIKE %SQLUPPER '" + messageCode + "'";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement));
}
public static DataTable getNTMDetailsReport(string[] accessionList)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
string accessions = string.Join("','", accessionList);
sb.Append("SELECT ");
sb.Append("ACC_ClientDR->CLF_CLNUM As AccountNo, ");
sb.Append("ACC_MiniLogDate As MiniLogDate, ");
sb.Append("ACC_MiniLogTime As MiniLogTime, ");
sb.Append("ACC_ClientDR->CLF_CLNAM As AccountName, ");
sb.Append("ACC_ClientDR->CLF_SalesTerritoryDR As SalesTerritory, ");
sb.Append("ACC_OwnerLabDR->LABLO_LabName As LabName, ");
sb.Append("ACC_ClientDR->CLF_CLPHN As AccountPhone, ");
sb.Append("ACC_Accession As Accession, ");
sb.Append("ACC_Species As Species, ");
sb.Append("ACC_PatientName As OnwerName, ");
sb.Append("ACC_PetFirstName As PetName, ");
sb.Append("ACC_TestsOrderedDisplayString As Tests, ");
sb.Append("ACC_SpecimenSubmitted As Specs, ");
sb.Append("$$GETINQNOTE^XT11(ACC_Accession) As InqNotes, ");
sb.Append("$$GETSPECIMENLOCATION^XT51(ACC_Accession) AS TubeLocation ");
sb.Append("FROM ORD_Accession");
sb.Append(" WHERE ACC_Accession IN ('" + accessions + "')");
sb.Append(" ORDER BY LabName ASC,AccountNo ASC");
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(sb.ToString()));
}
//AM Issue#32801 04/09/2008 0.0.0.9 -- AM Issue#32801 09/17/2008 AntechCSM 1.0.13.0 (Search with Message Keyword list)
public static DataTable getMessageDetails(String SearchText, String SearchOption)
{
string selectStatement = "SELECT MSG_Code, MSG_FirstLine, MSG_MessageText, MSG_AutoProblemComment,MSG_AutoProblemResolution, MSG_DefaultProblemCategoryDR,MSG_KeywordList FROM DIC_Message";
selectStatement = selectStatement + " WHERE 1=1 ";
if (SearchOption == "Message_Code")
{
selectStatement = (SearchText != "" ? selectStatement + " AND %SQLUPPER MSG_Code %STARTSWITH %SQLUPPER '" + SearchText + "'" : selectStatement);
}
else
{
selectStatement = (SearchText != "" ? selectStatement + " AND %SQLUPPER MSG_KeywordList LIKE %SQLUPPER '%" + SearchText + "%'" : selectStatement);
}
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
DataSet returnDS = cache.FillCacheDataSet(selectStatement);
if (returnDS.Tables.Count > 0)
{
return(returnDS.Tables[0]);
}
else
{
return(null);
}
}
public static DataTable getTestsInquiry(String clientID, String unitCode, String date, out String UnitCodeTitle, out String SpecialPrice, out String SpecialOptionPrice, String clientMnemonic, String userId, String userLab, String testName, String logEvent)
{
UnitCodeTitle = String.Empty;
SpecialPrice = String.Empty;
SpecialOptionPrice = String.Empty;
Dictionary <String, String> _getTestsInquiry = new Dictionary <String, String>();
_getTestsInquiry.Add("ClientID", clientID);
_getTestsInquiry.Add("UnitCode", unitCode);
_getTestsInquiry.Add("Date", date);
_getTestsInquiry.Add("ClientMnemonic", clientMnemonic);
_getTestsInquiry.Add("UserId", userId);
_getTestsInquiry.Add("UserLab", userLab);
_getTestsInquiry.Add("TestName", testName);
_getTestsInquiry.Add("LogEvent", logEvent);
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
String fullOutput = cache.StoredProcedure("?=call SP2_PRICEINQ(?,?,?,?,?,?,?,?)", _getTestsInquiry, 999999).Value.ToString();
String[] values = fullOutput.Split('^');
if (values.Length == 4)
{
UnitCodeTitle = values[0];
SpecialPrice = values[1];
SpecialOptionPrice = values[2];
DataTable returnDataTable = AtlasIndia.AntechCSM.Data.DL_functions.StringToDataTable(values[3], ';', '~');
if (returnDataTable.Columns.Count == 3)
{
returnDataTable.Columns[1].ColumnName = "UnitCode";
returnDataTable.Columns[2].ColumnName = "UnitCodeTitle";
returnDataTable.AcceptChanges();
return(returnDataTable);
}
}
return(null);
}
public static DataTable getDetailedUnitsByCode(String clientID, String unitCode)
{
String selectStatement = "SELECT UC_RowID As UnitCodeRowID,UC_UnitCode As UnitCode,UC_DisplayReportingTitle As UnitCodeName,UC_SpecimenRequirements As SpecimenRequirement,'Day(s) Test Set-up: '||UC_DaysTestSetUp||'<br>Time of Day: '||UC_TimeOfDay||'<br>Maximum Lab Time: '||UC_MaxLabTime As TestSchedule,SP_returnPrice('" + clientID + "','" + unitCode + "') As Price FROM DIC_UnitCode WHERE UC_UnitCode ='" + unitCode + "'";
//String selectStatement = "SELECT UC_SpecimenRequirements, UC_DaysTestSetUp||'\r\n'||UC_TimeOfDay||'\r\n'||UC_MaxLabTime AS TestSchedule, SP_returnPrice('99',UC_UnitCode,'08/04/2007') FROM DIC_UnitCode
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement));
}
public static String getILCMessageDefaultStatus(String MessageCode)
{
Dictionary <String, String> _ILCData = new Dictionary <String, String>();
_ILCData.Add("MSGCODE", MessageCode);
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.StoredProcedure("?=call SP2_GetILCMessageDefaultStatus(?)", _ILCData).Value.ToString());
}
public static String getDefaultILCToLab(String rowId)
{
Dictionary <String, String> _ILCData = new Dictionary <String, String>();
_ILCData.Add("rowId", rowId);
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.StoredProcedure("?=call SP2_GetILCDefaultToLab(?)", _ILCData).Value.ToString());
}
public static String getClientCountry(String clientID)
{
Dictionary <string, string> _getClientCountry = new Dictionary <string, string>();
_getClientCountry.Add("ClientID", clientID);
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.StoredProcedure("?=call SP_getClientCountry(?)", _getClientCountry).Value.ToString());
}