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())); }