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);
}
//AM Issue#32865 1.0.0.9 03/18/2008
public static DataTable ProblemDetails(String problemID)
{
String selectStatement = "SELECT PBT_PSQ,PBT_RowID PROBLEMID, PBT_LOGID AS ENTEREDBY,PBT_RESID AS RESOLVEDBY,%EXTERNAL(PBT_LoggedDate) AS ENTEREDDATE,PBT_LoggedTime AS ENTEREDTIME,%EXTERNAL(PBT_RESDate) AS RESOLUTIONDATE,PBT_REStime AS RESOLUTIONTIME,PBT_Comment AS COMMENTS,PBT_Resolution AS RESOLUTION,PBT_AccessionDR AS ACCESSION, PBT_LabLocation LABLOCATION, PBT_ProbTP AS PROBLEMTYPE, $$CO17^XT58(PBT_RESID) AS RESOLVEDBYDISPNAME, $$CO17^XT58(PBT_LOGID) AS ENTEREDBYDISPNAME";
selectStatement = selectStatement + " FROM CSV_ProbTracking WHERE PBT_RowID = '" + problemID + "'";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement));
}
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 DataTable getDetailedUnitsByName(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 %SQLUPPER UC_DisplayReportingTitle %STARTSWITH %SQLUPPER '" + unitName + "'";
//String selectStatement = "SELECT UC_RowID As UnitCodeRowID,UC_UnitCode As UnitCode,UC_DisplayReportingTitle As UnitCodeName,UC_SpecimenRequirements As SpecimenRequirement,'Test Data - Test Schedule' As TestSchedule,'Test Data - Test Price' As Price FROM DIC_UnitCode WHERE %STRING(UC_DisplayReportingTitle) %STARTSWITH %STRING('" + unitName + "')";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement, startIndex, noOfRecords));
}
public static DataTable getReason()
{
DataTable returnData = new DataTable();
string selectStatement = "SELECT RDEL_ReasonText ReasonText, RDEL_ReasonID ReasonID FROM DIC_ReasonForDeletion ORDER BY ReasonText";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
returnData = cache.FillCacheDataTable(selectStatement);
return(returnData);
}
public static DataTable getProblemSuperCatReport()
{
DataTable returnDataTable = new DataTable();
string strQuery = "Select DIC_PTYPG_ProblemType.PTGPT_PTYPG_ParRef->PTYPG_GroupName As GroupName,PTYP_Description,PTYP_ProblemType From DIC_ProblemType INNER JOIN DIC_PTYPG_ProblemType ON DIC_ProblemType.PTYP_ProblemType=DIC_PTYPG_ProblemType.PTGPT_ProblemTypeDR ORDER BY GroupName ASC";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
returnDataTable = cache.FillCacheDataTable(strQuery);
return(returnDataTable);
}
//AM Issue#38713 06/17/2008
public static DataTable getSystemMessages()
{
DataTable returnData = new DataTable();
string selectStatement = "SELECT SM_RowID MessageID, SM_MessageText MessageText, SM_DisplayOnLogin IsDisplayOnLogin FROM DIC_SystemMessage ORDER BY MessageID";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
returnData = cache.FillCacheDataTable(selectStatement);
return(returnData);
}
//AM Issue#38926 05/26/2008 Build Number 1.0.0.9
public static DataTable getReason(string DepartmentID)
{
DataTable returnData = new DataTable();
string selectStatement = "SELECT DRDEL_ReasonDR->RDEL_ReasonText ReasonText, DRDEL_ReasonDR->RDEL_ReasonID ReasonID FROM DIC_DepartmentReasonList WHERE DIC_DepartmentReasonList.DRDEL_DEPT_ParRef = '" + DepartmentID + "'";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
returnData = cache.FillCacheDataTable(selectStatement);
return(returnData);
}
public static DataTable getClientAtRiskDetails()
{
DataTable returnDataTable = new DataTable();
StringBuilder sb = new StringBuilder();
sb.Append("Select BICD_ProblemTypeGroupDR,BICD_Count,BICD_BICAR_PR,BICD_RowID From BI_TMP_ClientsAtRiskDetail");
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(sb.ToString()));
}
public static DataTable getClientAtRiskDetailsChild()
{
DataTable returnDataTable = new DataTable();
StringBuilder sb = new StringBuilder();
sb.Append("Select CPTPL_ProblemTypeDR,CPTPL_Count,CPTPL_BICD_PR From BI_TMP_CAR_ProblemTypeList");
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(sb.ToString()));
}
public static DataTable getTotalProbCount(string strDateFrom, string strDateTo)
{
DataTable returnDataTable = new DataTable();
StringBuilder sb = new StringBuilder();
sb.Append("Select TOP 1 $$GETTOTALPROBCOUNT^XBIUTIL('" + strDateFrom + "','" + strDateTo + "') As TotalProbsAll");
sb.Append(" From CSV_ProbTracking");
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
returnDataTable = cache.FillCacheDataTable(sb.ToString());
return(returnDataTable);
}
// AM Isssue#32876 AntechCSM 1.0.14.0 09/22/2008
public static DataTable getUnitsOrderedByAccession(string accessionNumber)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT ");
sb.Append("AWLC_ComponentUnitCodeDR As TESTCODE, ");
sb.Append("CASE AWLC_ComponentUnitCodeDR->UC_IsProfile WHEN 'Y' then {fn CONCAT(AWLC_ComponentUnitCodeDR->UC_DisplayReportingTitle, AWLC_NameToDisplay)} ELSE AWLC_NameToDisplay END As TESTNAME, ");
sb.Append("AWLC_ComponentUnitCodeDR->UC_OrderingMnemonics As TestMnemonic ");
sb.Append("FROM ORD_AccessionWorklistComponent ");
sb.Append("WHERE AWLC_AWL_ParRef->AWL_ACC_ParRef->ACC_Accession = '" + accessionNumber + "'");
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(sb.ToString()));
}
public static DataTable getInterLabCommDetails(string AccessionNumber, string InitiatingUser, string MessageToLab, string CurrentStatus, string DateFrom, string DateTo, string MessageFromLab, string AccountNumber, string InnitiatingMessageCode)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
sb.Append("ILC_RowID AS ROWID,");
sb.Append("ILC_AccessionDR AS ACCESSION,");
sb.Append("$$CO17^XT58(ILC_InitiatingUserDR->USER_UserID) AS INITIATINGUSER,");
sb.Append("ILC_InitiatingLabDR->LABLO_LabName AS INITIATINGLAB,");
sb.Append("ILC_CurrentStatus AS CURRENTSTATUS,");
sb.Append("ILC_FirstMessage AS MESSAGE,");
sb.Append("ILC_DateEntered AS DATEENTERED,");
sb.Append("ILC_TimeEntered AS TIMEENTERED, ");
sb.Append("ILC_CurrentOwnerLabDR->LABLO_LabName AS CURRENTLYWITHLAB ");
sb.Append("FROM ");
sb.Append("ORD_InterlabCommunication ");
sb.Append("Where 1=1 ");
if (AccessionNumber.Length > 0)
{
sb.Append(" AND ILC_AccessionDR ='" + AccessionNumber + "'");
}
if (InitiatingUser.Length > 0)
{
sb.Append(" AND ILC_InitiatingUserDR ='" + InitiatingUser + "'");
}
if (MessageToLab.Length > 0)
{
sb.Append(" AND ILC_CurrentOwnerLabDR ='" + MessageToLab + "'");
}
if (CurrentStatus.Length > 0)
{
sb.Append(" AND ILC_CurrentStatus ='" + CurrentStatus + "'");
}
if (DateFrom.Length > 0 && DateTo.Length > 0)
{
sb.Append(" AND ILC_DateEntered>= TO_DATE('" + DateFrom + "','MM/DD/YYYY') AND ILC_DateEntered<= TO_DATE('" + DateTo + "','MM/DD/YYYY')");
}
if (MessageFromLab.Length > 0)
{
sb.Append(" AND ILC_InitiatingLabDR ='" + MessageFromLab + "'");
}
if (AccountNumber.Length > 0)
{
sb.Append(" AND ILC_ClientDR->CLF_CLNUM ='" + AccountNumber + "'");
}
if (InnitiatingMessageCode.Length > 0)
{
sb.Append(" AND ILC_InitiatingMessageCodeDR ='" + InnitiatingMessageCode + "'");
}
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(sb.ToString()));
}
//AM Issue#32895 04/18/2008 Build Number 0.0.0.9
public DataTable getMailDetails(String MailID) // this is user for getting message details as well as updating new mail status.
{
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
String strSQLSelect = "SELECT MAIL_FromUser FROMUSERID,MAIL_FromUser->USER_LastFirstName FromUserName,%EXTERNAL(MAIL_DateReceived) DATERECEIVED,%EXTERNAL(MAIL_TimeReceived) TIMERECEIVED,MAIL_Subject SUBJECT,MAIL_USER_ParRef USERID, MAIL_IsNewMessage IsNewMessage, MAIL_MessageID MESSAGEID, MAIL_MessageTextToDisplay MESSAGETEXT, MAIL_AcknowledgementRequested ISACK, MAIL_AcknowledgementID ACKMESSAGE, MAIL_RowID MAILROWID, MAIL_ToUserString ToUserString,MAIL_ToGroupString ToGroupString FROM DIC_UserMail WHERE DIC_UserMail.MAIL_RowID ='" + MailID + "'";
DataTable returnData = cache.FillCacheDataTable(strSQLSelect);
if (returnData.Rows[0]["IsNewMessage"].ToString().Equals("1"))
{
SetMailRead(returnData.Rows[0]["USERID"].ToString(), returnData.Rows[0]["MESSAGEID"].ToString());
}
return(returnData);
}
/*
* public DataTable getAccessionDetails(string AccessionNumber)
* {
* // ACC_Accession
* // ACC_PatientName
* // ACC_ClientDR->CLF_CLNUM AS ACCOUNTNUM
* // ACC_ClientDR->CLF_CLMNE AS AccountMnemonic
* // ACC_ClientDR->CLF_CLNAM AS ACCOUNTNAME
* // ACC_ClientDR->CLF_CLPHN As PHONE
* // ACC_ClientDR->CLF_CLAD1 AS ADDRESS1
* // ACC_ClientDR->CLF_CLAD2 AS ADDRESS2
* // ACC_ClientDR->CLF_CLTER AS SalesTerritory
* // 'SALES REP TEST DATA 1' AS SalesRepresentative
* // ACC_ClientDR->CLF_AGNO AS AUTODIALGR
* // zoasis_num AS ZOASIS
* // ACC_ClientDR->CLF_CLRTS AS RouteStop
* // ACC_ClientDR->CLF_LabLocationDR->LABLO_LabName AS LOCATION
* // ACC_CollectionDateText As ORDERDATE
* // ACC_TestsOrderedDisplayString AS TESTSORDERED
* // 'ReportStatus' As ReportStatus
* // 'S' As StatDesignation
* // zoasis.CLN_RowID
*
* string selectStatement = "SELECT DISTINCT UC_UnitCode AS TESTCODE, UC_DisplayReportingTitle AS TESTNAME, ACC_ClientDR->CLF_LabLocationDR AS LABCODE FROM ORD_Accession ";
* //selectStatement = selectStatement + " left outer join zoasis on ACC_ClientDR->CLF_CLNUM = zoasis.CLN_RowID";
* selectStatement = selectStatement + " left outer join ORD_AccessionTestOrdered on ACC_Accession=ORD_AccessionTestOrdered.ATO_ACC_ParRef";
* selectStatement = selectStatement + " left outer join DIC_UnitCode on ATO_UnitCodeDR=DIC_UnitCode.UC_RowID";
* selectStatement = selectStatement + " left outer join DIC_UnitCodeTestCode on UC_RowID=DIC_UnitCodeTestCode.UCTC_UC_ParRef";
* // selectStatement = selectStatement + " left outer join DIC_TestCode on UCTC_TestCodeDR=DIC_TestCode.TC_RowID";
*
* selectStatement = selectStatement + " WHERE 1=1";
*
* selectStatement = (AccessionNumber != "" ? selectStatement + " AND ACC_Accession ='" + AccessionNumber + "'" : selectStatement);
* CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
* //DataSet returnDS = cache.StoredProcedure("call SUPAccessionDetails_AccessionSearch(?)", AccessionNumber);
* DataSet returnDS = cache.FillCacheDataSet(selectStatement);
* if (returnDS.Tables.Count > 0)
* {
* return returnDS.Tables[0];
* }
* else
* {
* return null;
* }
* }
*/
#endregion Unused code
//AM Issue#32875 03/26/2008 Build Number 1.0.0.9
public DataTable getTestDeletionDetails(String testDelID)
{
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 DATEENTERED, TD_TimeEntered AS TIMEENTERED,TO_DATE(TD_DatePrinted,'MM/dd/yyyy') AS DATECHECKED,TD_TimePrinted AS TIMECHECKED,TO_DATE(TD_DateProcessed,'MM/dd/yyyy') AS DATEPROCESSED,TD_TimeProcessed AS TIMEPROCESSED,TD_TestsToDelete AS TESTDELETED,TD_ProcessingStatus AS STATUS,TD_DepartmentDR->DEPT_Name AS DEPARTMENT,TD_REASONFORDELETIONDR AS REASON, TD_Comments AS COMMENTS, TD_UserProcessedBy AS PROCESSEDBY, TD_UserPrintedBy AS PRINTEDBY, 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 AND TD_RowID ='" + testDelID + "'";
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
returnDataTable = cache.FillCacheDataTable(selectStatement);
return(returnDataTable);
}
public static DataTable DiscountAuthorization(String AuthID)
{
DataTable returnDataTable = new DataTable();
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append("SELECT CLF_CLNUM AS CLIENTNUMBER,");
sbSQL.Append(" CLF_CLNAM AS CLIENTNAME,");
sbSQL.Append(" DAUTH_AccessionDR AS ACCESSION,");
sbSQL.Append(" %EXTERNAL(DAUTH_InHouseResub) AS INHOUSERESUB,");
sbSQL.Append(" DAUTH_LocationDR->LABLO_LabName AS LOCATION,");
sbSQL.Append(" CLF_LabLocationDR->LABLO_LabName AS CLIENTLOCATION,");
sbSQL.Append(" DAUTH_AuthorizationNumber AS AUTHORIZATIONNUMBER,");
sbSQL.Append(" DAUTH_DiscountCodeDR AS DISCOUNTCODE,");
sbSQL.Append(" DAUTH_UserID AS UID,");
sbSQL.Append(" %EXTERNAL(DAUTH_DateOfAuthorization) AS DATEOFAUTHORIZATION,");
sbSQL.Append(" DAUTH_Status AS STATUS,");
sbSQL.Append(" DAUTH_Territory AS TERRITORY,");
sbSQL.Append(" 'PHONE 1' AS PHONE,");
sbSQL.Append(" CLF_CLAD1 AS ADDRESS1,");
sbSQL.Append(" CLF_CLAD2 AS ADDRESS2,");
sbSQL.Append(" CLF_SalesTerritoryDR->ST_SalesRepName AS SalesRepresentative,");
sbSQL.Append(" CLF_AGNO AS AUTODIALGR,");
sbSQL.Append(" Zoasis_num AS ZOASIS,");
sbSQL.Append(" CLF_CLRTS AS RouteStop,");
sbSQL.Append(" DAUTH_CLIENTMNEMONICDR AS AccountMnemonic,");
sbSQL.Append(" DAUTH_AnimalName AS ANIMALNAME,");
sbSQL.Append(" DAUTH_AddTestCode AS ADDTESTCODE,");
sbSQL.Append(" DAUTH_DeleteTestCode AS DELTESTCODE,");
sbSQL.Append(" DAUTH_Amount AS AMOUNT,");
sbSQL.Append(" DAUTH_Reason AS REASON,");
sbSQL.Append(" ACC_Sex as SEX,");
sbSQL.Append(" ACC_Species as SPECIES,");
sbSQL.Append(" ACC_AgeDob as AGEDOB,");
sbSQL.Append(" %EXTERNAL(ACC_CollectionDateText) AS COLLECTIONDATE,");
sbSQL.Append(" ACC_CollectionTimeHours AS COLLECTIONTIME,");
sbSQL.Append(" ACC_TestsOrderedDisplayString AS TESTORDEREDSTRING,");
sbSQL.Append(" $$GETREVHIST^XT1(CLF_CLMNE) AS ClientRevenue");
sbSQL.Append(" FROM FIN_DiscountAuthorization");
sbSQL.Append(" LEFT OUTER JOIN ORD_Accession ON DAUTH_AccessionDR=ACC_Accession");
sbSQL.Append(" LEFT OUTER JOIN CLF_ClientFile ON DAUTH_CLIENTMNEMONICDR=CLF_CLMNE");
sbSQL.Append(" LEFT OUTER JOIN zoasis ON CLF_CLNUM = zoasis.CLN_RowID");
sbSQL.Append(" WHERE DAUTH_AuthorizationNumber ='" + AuthID + "'");
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
returnDataTable = cache.FillCacheDataTable(sbSQL.ToString());
return(returnDataTable);
}
private static DataTable getProfilesByCode(String[] searchValues, String AndOrSwitch, String clientCountry)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
sb.Append("UC_RowID As ProfileCode, ");
sb.Append("UC_DisplayReportingTitle ProfileName, ");
sb.Append("UC_Mnemonics As UnitCodeMnemonic, ");
sb.Append("UC_Alias As Alias, ");
sb.Append("{fn SUBSTRING(UC_Status,1,1)} As Status, ");
sb.Append("$$GETALTVALUE^XT27(UC_UnitCode)As UnitAltCode ");
sb.Append("FROM DIC_UnitCode ");
sb.Append("WHERE ");
sb.Append("UC_IsProfile='Y' ");
for (int i = 0; i < searchValues.Length; i++)
{
string searchvalue = searchValues[i].Trim();
if (searchvalue.Length > 0)
{
if (i == 0)
{
sb.Append("AND ");
}
else
{
sb.Append(AndOrSwitch + " ");
}
sb.Append("(SP_getProfileUnits(UC_RowID) ='" + searchvalue + "' ");
sb.Append("OR ");
sb.Append("SP_getProfileUnits(UC_RowID) LIKE '" + searchvalue + ",%' ");
sb.Append("OR ");
sb.Append("SP_getProfileUnits(UC_RowID) LIKE '%," + searchvalue + "' ");
sb.Append("OR ");
sb.Append("SP_getProfileUnits(UC_RowID) LIKE '%," + searchvalue + ",%') ");
}
}
String selectStatement = sb.ToString();
if (clientCountry == "US")
{
selectStatement += " AND UC_IsUSCode='1'";
}
else if (clientCountry == "CANADA")
{
selectStatement += " AND UC_IsCanadaCode='1'";
}
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement));
}
public static DataTable getUnitsByCombinedIndex(String clientID, String unitName, Int32 startIndex, Int32 noOfRecords, String clientCountry)
{
String selectStatement = "SELECT UC_RowID As UnitCodeRowID,UC_UnitCode As UnitCode,UC_DisplayReportingTitle As UnitCodeName,UC_Mnemonics As UnitCodeMnemonic, 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,{fn SUBSTRING(UC_Status,1,1)} As Status FROM DIC_UnitCode WHERE UC_SearchField %STARTSWITH '" + unitName + "'"; //PD Antech 05/12/2011
if (clientCountry == "US")
{
selectStatement += " AND UC_IsUSCode='1'";
}
else if (clientCountry == "CANADA")
{
selectStatement += " AND UC_IsCanadaCode='1'";
}
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement, startIndex, noOfRecords));
}
public static DataTable getDetailedUnitsByCode(String clientID, String unitCode, String clientCountry)
{
String selectStatement = "SELECT UC_RowID As UnitCodeRowID,UC_UnitCode As UnitCode,UC_DisplayReportingTitle As UnitCodeName,UC_Mnemonics As UnitCodeMnemonic, 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 + "'"; //PD Antech 05/12/2011
if (clientCountry == "US")
{
selectStatement += " AND UC_IsUSCode='1'";
}
else if (clientCountry == "CANADA")
{
selectStatement += " AND UC_IsCanadaCode='1'";
}
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement));
}
/*
* public static DataTable getUnitCodesForProfile(String profileRowID)
* {
* // UCPC_ComponentUnitCodeDR as fkUnitCodeRowID = fk Profile Code Row ID
* // UCPC_ComponentUnitCodeDR->UC_RowID1 as UnitCodeRowID = Unit Code Row ID
* // UCPC_ComponentUnitCodeDR->UC_UnitCode As UnitCode = Unit Code
* // UCPC_ComponentUnitCodeDR->UC_DisplayReportingTitle As UnitCodeName = Unit Code Reporting Title
* // UCPC_UC_ParRef = fk Unit Code Row ID
*
* String selectStatement = "SELECT UCPC_ComponentUnitCodeDR as fkUnitCodeRowID,UCPC_ComponentUnitCodeDR->UC_UnitCode as UnitCodeRowID,UCPC_ComponentUnitCodeDR->UC_UnitCode As UnitCode,UCPC_ComponentUnitCodeDR->UC_DisplayReportingTitle As UnitCodeName,UCPC_ComponentUnitCodeDR->UC_Mnemonics As UnitCodeMnemonic,UCPC_ComponentUnitCodeDR->UC_Alias As Alias FROM DIC_UnitCodeProfileComponent WHERE UCPC_UC_ParRef = '" + profileRowID + "'";
* CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
* return cache.FillCacheDataTable(selectStatement);
* }*/
#endregion Unused code
public static DataTable getUnitCodesForProfile(String profileRowID, String clientCountry)
{
String selectStatement = "SELECT UCPC_ComponentUnitCodeDR as fkUnitCodeRowID,UCPC_ComponentUnitCodeDR->UC_UnitCode as UnitCodeRowID,UCPC_ComponentUnitCodeDR->UC_UnitCode As UnitCode,UCPC_ComponentUnitCodeDR->UC_DisplayReportingTitle As UnitCodeName,UCPC_ComponentUnitCodeDR->UC_Mnemonics As UnitCodeMnemonic,UCPC_ComponentUnitCodeDR->UC_Alias As Alias,{fn SUBSTRING(UCPC_ComponentUnitCodeDR->UC_Status,1,1)} As Status,$$GETALTVALUE^XT27(UCPC_ComponentUnitCodeDR->UC_UnitCode)As UnitAltCode FROM DIC_UnitCodeProfileComponent WHERE UCPC_UC_ParRef = '" + profileRowID + "'";
if (clientCountry == "US")
{
selectStatement += " AND UCPC_ComponentUnitCodeDR->UC_IsUSCode='1'";
}
else if (clientCountry == "CANADA")
{
selectStatement += " AND UCPC_ComponentUnitCodeDR->UC_IsCanadaCode='1'";
}
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement));
}
public static DataTable getUnitsByCombinedIndex(String searchValue, int startIndex, int noOfRecords, String clientCountry)
{
String selectStatement = "SELECT UC_RowID As UnitCodeRowID,UC_UnitCode As UnitCode,UC_DisplayReportingTitle As UnitCodeName,UC_IsProfile As UnitCodeIsProfile,UC_Mnemonics As UnitCodeMnemonic,UC_Alias As Alias,{fn SUBSTRING(UC_Status,1,1)} As Status,$$GETALTVALUE^XT27(UC_UnitCode)As UnitAltCode FROM DIC_UnitCode WHERE UC_SearchField %STARTSWITH '" + searchValue + "'";
if (clientCountry == "US")
{
selectStatement += " AND UC_IsUSCode='1'";
}
else if (clientCountry == "CANADA")
{
selectStatement += " AND UC_IsCanadaCode='1'";
}
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement, startIndex, noOfRecords));
}
public static DataTable getUnitsByName(String unitName, String clientCountry)
{
String selectStatement = "SELECT UC_RowID As UnitCodeRowID,UC_UnitCode As UnitCode,UC_DisplayReportingTitle As UnitCodeName,UC_IsProfile As UnitCodeIsProfile,UC_Mnemonics As UnitCodeMnemonic,UC_Alias As Alias,{fn SUBSTRING(UC_Status,1,1)} As Status,$$GETALTVALUE^XT27(UC_UnitCode)As UnitAltCode FROM SQLUser.DIC_UnitCode WHERE %SQLUPPER UC_DisplayReportingTitle %STARTSWITH %SQLUPPER '" + unitName + "'";
if (clientCountry == "US")
{
selectStatement += " AND UC_IsUSCode='1'";
}
else if (clientCountry == "CANADA")
{
selectStatement += " AND UC_IsCanadaCode='1'";
}
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(selectStatement));
}
public static DataTable getNTMCallbackNotes(string accession)
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append("SELECT ");
sbSQL.Append("NTM_CallbackNotes AS CallbackNotes ");
sbSQL.Append("FROM ORD_NoTestMarked ");
sbSQL.Append("WHERE NTM_AccessionDR ='");
sbSQL.Append(accession);
sbSQL.Append("'");
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(sbSQL.ToString()));
}
public static DataTable getSpecimenLocation(string accessionNumber)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT ");
sb.Append("SPLOC_OwnerLabLocationDR->LABLO_LabName As LabName, ");
sb.Append("SPLOC_Date As LocationDate, ");
sb.Append("SPLOC_Rack As LocationRack, ");
sb.Append("SPLOC_Position As LocationPosition, ");
sb.Append("$$CO17^XT58(SPLOC_ScannedByUser) As UserID ");
sb.Append("FROM ORD_SpecimenLocation");
sb.Append(" WHERE SPLOC_AccessionDR = '" + accessionNumber + "'");
sb.Append(" ORDER BY SPLOC_OwnerLabLocationDR, SPLOC_Date DESC");
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(sb.ToString()));
}
public static DataTable getProbAnalysisByTerritoryChildDetails(string strDateFrom, string strDateTo, string strLabLocation, string strClient, string strProblemGroup, string strProblemType, string strSalesTerritory)
{
DataTable returnDataTable = new DataTable();
StringBuilder sb = new StringBuilder();
sb.Append("Select DIC_PTYPG_ProblemType.PTGPT_PTYPG_ParRef->PTYPG_GroupName As Type,PBT_ProbTP->PTYP_Description As DESCVALUE, PBT_CLDesRef->CLF_SalesTerritoryDR AS Territory, COUNT(*) As CountValue ");
sb.Append(" From CSV_ProbTracking LEFT JOIN DIC_PTYPG_ProblemType ON CSV_ProbTracking.PBT_ProbTP=DIC_PTYPG_ProblemType.PTGPT_ProblemTypeDR");
sb.Append(" WHERE 1=1");
if (strDateFrom.Length > 0)
{
sb.Append(" AND PBT_LoggedDate>=TO_DATE('");
sb.Append(strDateFrom);
sb.Append("','MM/dd/yyyy')");
}
if (strDateTo.Length > 0)
{
sb.Append(" AND PBT_LoggedDate<=TO_DATE('");
sb.Append(strDateTo);
sb.Append("','MM/dd/yyyy')");
}
if (strLabLocation.Length > 0)
{
sb.Append(String.Concat(" AND PBT_LabLocation IN (", strLabLocation, ")"));
}
if (strClient.Length > 0)
{
sb.Append(String.Concat(" AND PBT_CLDesRef->CLF_CLNUM IN (", strClient, ")"));
}
if (strProblemGroup.Length > 0)
{
sb.Append(String.Concat(" AND DIC_PTYPG_ProblemType.PTGPT_PTYPG_ParRef IN (", strProblemGroup, ")"));
}
if (strProblemType.Length > 0)
{
sb.Append(String.Concat(" AND PBT_ProbTP IN (", strProblemType, ")"));
}
if (strSalesTerritory.Length > 0)
{
sb.Append(String.Concat(" AND PBT_CLDesRef->CLF_SalesTerritoryDR IN (", strSalesTerritory, ")"));
}
sb.Append(" GROUP BY PBT_ProbTP->PTYP_ProblemType, PBT_CLDesRef->CLF_SalesTerritoryDR");
sb.Append(" ORDER BY Type ASC");
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
returnDataTable = cache.FillCacheDataTable(sb.ToString());
return(returnDataTable);
}
public static DataTable getProblemAnalysisByTerritorySummary(string strDateFrom, string strDateTo, string strLabLocation, string strClient, string strProblemGroup, string strProblemType, string strSalesTerritory)
{
DataTable returnDataTable = new DataTable();
StringBuilder sb = new StringBuilder();
sb.Append("Select PBT_CLDesRef->CLF_SalesTerritoryDR As Territory,PBT_CLDesRef->CLF_SalesTerritoryDR->ST_SalesRepName As SalesRepName,DIC_PTYPG_ProblemType.PTGPT_PTYPG_ParRef As Type, ");
sb.Append("$$GETTOTALPROBCOUNT^XBIUTIL('" + strDateFrom + "','" + strDateTo + "') As TotalProbsAll");
sb.Append(" From");
sb.Append(" CSV_ProbTracking LEFT JOIN DIC_PTYPG_ProblemType ON CSV_ProbTracking.PBT_ProbTP=DIC_PTYPG_ProblemType.PTGPT_ProblemTypeDR");
sb.Append(" WHERE 1=1");
if (strDateFrom.Length > 0)
{
sb.Append(" AND PBT_LoggedDate>=TO_DATE('");
sb.Append(strDateFrom);
sb.Append("','MM/dd/yyyy')");
}
if (strDateTo.Length > 0)
{
sb.Append(" AND PBT_LoggedDate<=TO_DATE('");
sb.Append(strDateTo);
sb.Append("','MM/dd/yyyy')");
}
if (strLabLocation.Length > 0)
{
sb.Append(String.Concat(" AND PBT_LabLocation IN (", strLabLocation, ")"));
}
if (strClient.Length > 0)
{
sb.Append(String.Concat(" AND PBT_CLDesRef->CLF_CLNUM IN (", strClient, ")"));
}
if (strProblemGroup.Length > 0)
{
sb.Append(String.Concat(" AND DIC_PTYPG_ProblemType.PTGPT_PTYPG_ParRef IN (", strProblemGroup, ")"));
}
if (strProblemType.Length > 0)
{
sb.Append(String.Concat(" AND PBT_ProbTP IN (", strProblemType, ")"));
}
if (strSalesTerritory.Length > 0)
{
sb.Append(String.Concat(" AND PBT_CLDesRef->CLF_SalesTerritoryDR IN (", strSalesTerritory, ")"));
}
sb.Append(" ORDER BY Territory ASC");
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
returnDataTable = cache.FillCacheDataTable(sb.ToString());
return(returnDataTable);
}
public static DataTable GetTestDetails(string rowId)
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append("SELECT ");
sbSQL.Append("MSPL_PetName||' '||MSPL_OwnerName||' / '||MSPL_TestsRequested||' / '||MSPL_SamplesSubmitted AS TestDetails ");
sbSQL.Append("FROM ORD_MissingSpecimenPetList ");
sbSQL.Append("WHERE MSPL_MISSS_PR ='");
sbSQL.Append(rowId);
sbSQL.Append("' ");
sbSQL.Append("ORDER BY MSPL_ChildSub");
CACHEDAL.ConnectionClass cache = new CACHEDAL.ConnectionClass();
return(cache.FillCacheDataTable(sbSQL.ToString()));
}
