//Support Calls private static string GetCCDBBatchID(string strConstraints, string strUserUnix) { // SQL condition for getting a completed batch. (omit if running a unit test) string strSQLCompletedBatch = (strUserUnix == "test") ? null : " and INOCTIME > (SYSDATE - 30) and HARVESTTIME is null"; //Find the top most in-progress run that matches the search criteria, because predictions are only applicable for a single, non-completed run. string strBatchID = OracleSQL.SimpleQuery("CCDB", "SELECT ISI.CCBATCHES.BATCHID FROM ISI.CCBATCHES where ISI.CCBATCHES.BATCHID is not null" + strConstraints + strSQLCompletedBatch + " order by INOCTIME desc"); return(strBatchID); }
public static string GODWQuery(string strUserUnix, string strFlag, string strParameter, string strThemeID) { string strResult = null; string strParameterField = null; string strSQLThemeID = null; string strPrettyPrint = null; //GOWD Query Example //select distinct PROJECT_THEME_DESC from S_F_FTE_ACTUAL where PROJECT_THEME_NAME='70094' //Understand what the user is searching for, set at the parameter field switch (strParameter.ToUpper()) { case "ASSIGNED": strParameterField = "distinct PROJECT_THEME_DESC"; break; case "RECORD": strParameterField = "distinct PROJECT_THEME_DESC"; break; default: strParameterField = "distinct PROJECT_THEME_DESC"; break; } //Define Base query string strSQLbase = "select <FIELD> from S_F_FTE_ACTUAL where PROJECT_THEME_NAME is not null"; //Build the conditional clauses from information provided if (!string.IsNullOrEmpty(strThemeID)) { strSQLThemeID = " and PROJECT_THEME_NAME='" + strThemeID.Trim().ToLower() + "'"; } ; string strSQLFinal = strSQLbase.Replace("<FIELD>", strParameterField) + strSQLThemeID; strResult = OracleSQL.SimpleQuery("GODW", strSQLFinal); if (!string.IsNullOrEmpty(strResult)) { strPrettyPrint = "The project associated with theme " + strThemeID + " is " + strResult; } else { strPrettyPrint = "There doesn't seem to be a project associated with theme " + strThemeID + ". Please try a different theme"; } return(strPrettyPrint.Trim()); }
/// <summary> /// Queries MSAT_SENTRY_DEFINE for action/alert limits. /// </summary> /// <param name="strParameter">ex: online dO2</param> /// <param name="strProduct">ex: anti-Myostatin</param> /// <param name="strVesselClass">ex: 2kL</param> /// <param name="strLimitType">ex: upper action limit</param> /// <returns></returns> public static string LimitQuery(string strParameter, string strProduct, string strVesselClass, string strLimitType) { // Initialize variables string strResult = $"Sorry! I can't seem to find the {strLimitType} you requested, can you refine your request and try again?"; string strUOM = null; // Pretty print the user input, before any additional lookups string strConstraintsPP = PrettyPrintConstraints(strParameter, strProduct, strVesselClass, strLimitType); // Define SQL statement strLimitType = strLimitType.Replace(" ", "_"); string strSQLLimitType = $"select {strLimitType}, CHECK_PARA_UOM from MSAT_SENTRY_DEFINE_VW" + $" where (upper(DEFINE_NAME) = '{strParameter}' or upper(CHECK_PARA_ABBREV) = '{strParameter}')" + $" and (upper(CCDB_NAME) = '{strProduct}' or upper(PROCESS_ALIAS) like '%{strProduct}%')" + $" and upper(AREA_ALIAS) like '%{strVesselClass}%'"; //string strSQLlimit = $"select {strLimit} from MSAT_SENTRY_DEFINE_VW where CHECK_PARA_NAME = :pParam and CCDB_NAME = :pProduct and AREA_ALIAS like '%pVessel%'"; // Query the database DataTable dtResult = OracleSQL.DataTableQuery("DATATOOLS", strSQLLimitType); DataRow[] drResults = dtResult.Select(); if (drResults.Length > 0) { strResult = drResults[0][strLimitType].ToString(); strUOM = drResults[0]["CHECK_PARA_UOM"].ToString(); } // Pretty print string strPrettyPrint = strConstraintsPP + " is "; switch (strParameter) { case "ONLINE PH": // Do people here like "pH 7.0" or "7.0 pH"? strPrettyPrint += $"{strUOM} {strResult}"; break; default: strPrettyPrint += $"{strResult} {strUOM}"; break; } return(strPrettyPrint); }
public static string UtilityQuery(string strParameter, string strUtility, string strRoom, string strModifier, double dblDuration) { //Querys IP21 based on start and end times or equipment found in CCDB string strResult = "Sorry! I'm having difficulties connecting to IP21 right now, please try again later"; string strTagResult = null; string strPrettyPrint = null; string strStartTimeResult = null; string strDurationPrint = null; //Re-Define search windows if a duration is detected if (dblDuration > 0) { string strSQLStartTime = "select TO_CHAR(SYSDATE - " + dblDuration + " / 86400,'YYYY-MM-DD HH24:MI:SS') START_TIME from dual"; strStartTimeResult = OracleSQL.SimpleQuery("DATATOOLS", strSQLStartTime); strDurationPrint = " in the last " + Math.Round(dblDuration / 3600) + " hours"; } //Build the tag query and query IP21 for the tag, store result in strTagResult string strQueryTagName = "SELECT name from ip_analogdef where ip_description like '" + strRoom + "%' and ip_description like '%" + strUtility + "%' and IP_ENG_UNITS like 'DEGC'"; strTagResult = IP21.GenericQuery("IP-UTIL", strQueryTagName); //Build the tag query and query IP21 for the tag, store result in strTagResult string strSortOrder = "order by ts desc"; //Treat lack of qualifers as "current" string strParameterField = "ROUND(value,3)"; //Add query modifiers if requested if (!string.IsNullOrEmpty(strModifier)) { switch (strModifier.ToUpper()) { case "CURRENT": strSortOrder = " order by ts desc"; break; case "MIN": case "MINIMUM": strParameterField = "MIN(" + strParameterField + ")"; strSortOrder = null; break; case "MAX": case "MAXIMUM": strParameterField = "MAX(" + strParameterField + ")"; strSortOrder = null; break; case "AVERAGE": strParameterField = "AVG(" + strParameterField + ")"; strSortOrder = null; break; case "RANGE": strParameterField = "MAX(" + strParameterField + ") - MIN(" + strParameterField + ")"; strSortOrder = null; break; default: break; } } string strQueryIP21 = "SELECT " + strParameterField + " FROM history where name='" + strTagResult + "' " + strSortOrder + ";"; strResult = IP21.GenericQuery("IP-UTIL", strQueryIP21); //Pretty print the result strPrettyPrint = "The current " + strParameter + " value for " + strRoom + " " + strUtility + " is " + strResult + " Deg C"; return(strPrettyPrint.Trim()); }
public static string TiterQuery(string strProduct, string strVesselClass, string strEquipment, string strRun, string strLot, string strStep) { //Need to also connect to CCDB for this one to interpret string strResult = "Sorry! I can't find a titer value for this batch, it's most likely not in LIMS yet"; string strParameterField = "ASSAY_RESULTS_RAW"; string strSQLItemType = null; string strSQLTestCode = " and TEST_CODE in ('Q12318','Q12274')"; string strSQLLot = null; string strPrettyPrint = null; string strUnits = null; //LIMS Query Example //select * from MSAT.T_LIMS where ITEM_TYPE='PHCCF' and TEST_CODE='Q12318' and MES_LOT_NUMBER='3138969' and SITE_FLAG='SSF' order by ESB_WRITE_TIME desc; //Define Base query string strSQLbase = "select <FIELD> from MSAT.T_LIMS where SITE_FLAG='SSF'"; //Combine all constraints string strConstraints = CCDB.ConcatConstraints(strProduct, "12KL", strEquipment, strRun, strLot, null); string strConstraintsPP = CCDB.PrettyPrintConstraints(strProduct, "12KL", strEquipment, strRun, strLot, null); //Find the Harvest Lot - use IMS reports string strSQLHarvLot = "select BATCH from ISI.IMSREPORTS where AREA='CENTRIFUGE' and START_TIME is not null and RECIPE is not null " + "and START_TIME + 6 / 24 > (select HARVESTTIME - 6 / 24 from ISI.CCBATCHES where ISI.CCBATCHES.BATCHID is not null " + strConstraints + " and HARVESTTIME is not null) order by START_TIME"; strLot = OracleSQL.SimpleQuery("CCDB", strSQLHarvLot); //Build the conditional clauses from information provided try { strStep = Define.stepItemAlias[strStep]; } catch (KeyNotFoundException) { strStep = null; } if (!string.IsNullOrEmpty(strStep)) { strSQLItemType = " and ITEM_TYPE='" + strStep + "'"; } ; if (!string.IsNullOrEmpty(strLot)) { strSQLLot = " and MES_LOT_NUMBER='" + strLot.Trim() + "'"; } ; //Find the titer result string strSQLFinal = strSQLbase.Replace("<FIELD>", strParameterField) + strSQLItemType + strSQLTestCode + strSQLLot + " and UPPER(COMPONENT) like '%CONC%' order by ESB_WRITE_TIME desc"; strResult = OracleSQL.SimpleQuery("LIMS", strSQLFinal); //Find the titer units string strSQLUnit = strSQLbase.Replace("<FIELD>", "UNITS") + strSQLItemType + strSQLTestCode + strSQLLot + " and UPPER(COMPONENT) like '%CONC%' order by ESB_WRITE_TIME desc"; strUnits = OracleSQL.SimpleQuery("LIMS", strSQLUnit); strPrettyPrint = "The " + strStep + " titer for " + strConstraintsPP.Trim() + " is " + strResult + " " + strUnits; return(strPrettyPrint.Trim()); }
public static string LIMSQuery(string strParameter, string strItemType, string strTestCode, string strLot) { string strResult = null; string strPrettyPrint = null; string strParameterField = "ASSAY_RESULTS_RAW"; string strSQLItemType = null; string strSQLTestCode = null; string strSQLLot = null; string strUnits = null; string strPPItemType = null; string strPPTestCode = null; string strPPPLot = null; string strConstraintsPP = null; //LIMS Query Example //select * from MSAT.T_LIMS where ITEM_TYPE='PHCCF' and TEST_CODE='Q12318' and MES_LOT_NUMBER='3138969' and SITE_FLAG='SSF' order by ESB_WRITE_TIME desc; //Define Base query string strSQLbase = "select <FIELD> from MSAT.T_LIMS where SITE_FLAG='SSF'"; //Build the conditional clauses from information provided if (!string.IsNullOrEmpty(strItemType)) { strSQLItemType = " and ITEM_TYPE like '%" + strItemType.Trim().ToUpper() + "%'"; } ; if (!string.IsNullOrEmpty(strTestCode)) { strSQLTestCode = " and TEST_CODE like '%" + strTestCode.Trim().ToUpper() + "%'"; } ; if (!string.IsNullOrEmpty(strLot)) { strSQLLot = " and MES_LOT_NUMBER='" + strLot.Trim() + "'"; } ; //Pretty print constrints if (!string.IsNullOrEmpty(strItemType)) { strPPItemType = " item type " + strItemType.Trim().ToUpper(); } ; if (!string.IsNullOrEmpty(strTestCode)) { strPPTestCode = " test code " + strTestCode.Trim().ToUpper(); } ; if (!string.IsNullOrEmpty(strLot)) { strPPPLot = " lot " + strLot.Trim(); } ; strConstraintsPP = strPPItemType + strPPTestCode + strPPPLot; string strSQLFinal = strSQLbase.Replace("<FIELD>", strParameterField) + strSQLItemType + strSQLTestCode + strSQLLot + " order by ESB_WRITE_TIME desc"; strResult = OracleSQL.SimpleQuery("LIMS", strSQLFinal); string strSQLUnit = strSQLbase.Replace("<FIELD>", "UNITS") + strSQLItemType + strSQLTestCode + strSQLLot + " order by ESB_WRITE_TIME desc"; strUnits = OracleSQL.SimpleQuery("LIMS", strSQLUnit); strPrettyPrint = "The " + strParameter + " value for " + strConstraintsPP.Trim() + " is " + strResult + " " + strUnits; return(strPrettyPrint.Trim()); }
public static string BufferQuery(string strTrigger, string strParameter, string strProduct, string strVesselClass, string strEquipment, string strRun, string strLot, string strStation) { // Query get Media information used for a selected run/lot/batch // Should answer the follwing: What's the media lot for Avastin Run 162 12kL? //Initialize variables string strResult = null; string strBufferBatchID = null; //Specific Batch Query Example //select BATCH_ID from SSFMES.CO_PROC_RESULT_ST where RESULTS like '" + strLot + "' and UPPER(UNIT_PROCEDURE_ID) like '%BATCH%FEED%' switch (strParameter.ToUpper()) { case "PRODUCT": strProduct = null; break; case "SCALE": strVesselClass = null; break; case "EQUIPMENT": strEquipment = null; break; case "RUN": strRun = null; break; case "LOT": strLot = null; break; case "STATION": strStation = null; break; default: break; } // Pretty print the user input, before any additional lookups string strConstraintsPP = PrettyPrintConstraints(strTrigger, strParameter, strProduct, strVesselClass, strEquipment, strRun, strLot, strStation); //Define Base query if (string.IsNullOrEmpty(strLot)) { string strSQLbase = "select <FIELD> from ISI.CCBATCHES where ISI.CCBATCHES.BATCHID is not null"; //Combine all constraints string strConstraints = CCDB.ConcatConstraints(strProduct, strVesselClass, strEquipment, strRun, strLot, strStation); //Get the culture lot from CCDB string strCultureLot = strSQLbase.Replace("<FIELD>", "LOT") + strConstraints + " order by INOCTIME desc"; strLot = OracleSQL.SimpleQuery("CCDB", strCultureLot); } strBufferBatchID = OracleSQL.SimpleQuery("MES", "select distinct BATCH_ID from SSFMES.MM_GENEALOGY_LG where DEST_LOT_ID='" + strLot.Trim() + "'"); //Get the result, if user is requesting a lot number, find it in Genealogy table if (strParameter == "LOT") { strResult = OracleSQL.SimpleQuery("MES", "select distinct DEST_LOT_ID from SSFMES.MM_GENEALOGY_LG where BATCH_ID='" + strBufferBatchID + "'"); } else { strResult = OracleSQL.SimpleQuery("MES", "select case when RESULTN is not null then to_char(RESULTN) else RESULTS end VAL from SSFMES.CO_PROC_RESULT_ST where BATCH_ID like '" + strBufferBatchID + "' and UPPER(STEP_INSTANCE_ID) like '%" + strParameter + "%' order by STEP_INSTANCE_ID, ENTRY_TIMESTAMP desc"); } string strPrettyPrint = strConstraintsPP + " is " + strResult.Trim(); return(strPrettyPrint); }
public static string Snooze(string strCCDBParameter, string strIPFermParameter, string strUserUnix, string strEquipment, string strLot, double dblDuration) { //Initialize variables string strResult = null; string strJobParameter = null; string strJobContext = null; //Figure out where the parameter is located CCDB or IP-FERM if (!string.IsNullOrEmpty(strCCDBParameter)) { strJobParameter = strCCDBParameter; } ; if (!string.IsNullOrEmpty(strIPFermParameter)) { strJobParameter = strIPFermParameter; } ; if (!string.IsNullOrEmpty(strEquipment)) { strJobContext = strEquipment; } ; if (!string.IsNullOrEmpty(strLot)) { strJobContext = strLot; } ; if (dblDuration == 0) { dblDuration = 2 * 60 * 60; } ; //Default 2 hours for snooze alarm //If nothing is provided, then look up the previous alert from Service log and infer the details if (string.IsNullOrEmpty(strCCDBParameter) && string.IsNullOrEmpty(strIPFermParameter)) { string strParaName = null; string strRefValue = OracleSQL.SimpleQuery("DATATOOLS", "select REFERENCE_VALUE from MSAT_SERVICE_LOG_VW where USER_UNIX='" + strUserUnix + "' and APPLICATION='SentryService' order by MESSAGE_TIME desc"); string strRefSQL = OracleSQL.SimpleQuery("DATATOOLS", "select REFERENCE_SQL from MSAT_SERVICE_LOG_VW where USER_UNIX='" + strUserUnix + "' and APPLICATION='SentryService' order by MESSAGE_TIME desc"); if (!string.IsNullOrEmpty(strRefSQL)) { strParaName = OracleSQL.SimpleQuery("DATATOOLS", "select CHECK_PARA_NAME from MSAT_SENTRY_DEFINE_VW where DEFINE_ID is not null " + strRefSQL); } ; if (string.IsNullOrEmpty(strJobContext) & !string.IsNullOrEmpty(strRefValue)) { strJobContext = strRefValue.ToUpper(); } ; if (string.IsNullOrEmpty(strJobParameter) & !string.IsNullOrEmpty(strParaName)) { strJobParameter = strParaName.ToUpper(); } ; } //Create Snooze job for Sentry if (strUserUnix != "test") { OracleSQL.OracleWrite("DATATOOLS", "insert into MSAT_SERVICE_JOBS (JOB_ID,APPLICATION,REQUEST_TIME,EXPIRATION_TIME, " + "USER_ID,JOB_PARA_ID,JOB_CONTEXT,JOB_OPERATION) values " + "(SQ_MSAT_SERVICE_JOB_ID.NextVal,'SentryService',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP+(" + dblDuration / 86400 + ")," + "(select USER_ID from MSAT_TIMELY_USERS where UNIX='" + strUserUnix + "')," + "(select PARA_ID from MSAT_PARAMETERS where UPPER(PARA_NAME)='" + strJobParameter + "'),'" + strJobContext + "','SNOOZE')"); } strResult = "Alerts for " + strJobParameter + " on " + strJobContext + " will be snoozed for the next " + Math.Round(dblDuration / 3600, 2) + " hours"; return(strResult); }
public static string CreateJob(string strCCDBParameter, string strIPFermParameter, string strIPRecParameter, string strUserUnix, string strEquipment, string strStation, string strLot, double dblDuration, string strlimit, string strlimitvalue, string strModifier) { //Initialize variables string strResult = null; string strIP21Result = null; string strTagResult = null; string strJobParameter = null; string strJobOperation = null; string strJobContext = null; string strJobCriteria = null; //Figure out where the parameter is located CCDB or IP-FERM if (!string.IsNullOrEmpty(strCCDBParameter)) { strJobParameter = strCCDBParameter; } ; if (!string.IsNullOrEmpty(strIPFermParameter)) { strJobParameter = strIPFermParameter; } ; if (!string.IsNullOrEmpty(strIPRecParameter)) { strJobParameter = strIPRecParameter; } ; if (!string.IsNullOrEmpty(strlimitvalue)) { strJobCriteria = strlimitvalue; } ; if (!string.IsNullOrEmpty(strEquipment)) { strJobContext = strEquipment; } ; if (!string.IsNullOrEmpty(strStation)) { strJobContext = strStation; } ; if (!string.IsNullOrEmpty(strLot)) { strJobContext = strLot; } ; switch (strJobParameter) { case "PHASES": strJobParameter = "PHASE"; break; } //Figure out what the current value is so that the job operation can be assigned, if no limit value is found, the user is asking for a state change if (!string.IsNullOrEmpty(strlimitvalue)) { if (strlimit == "REACH" || strlimit == "HIT" || strlimit == "WILL BE") { if (!string.IsNullOrEmpty(strIPFermParameter)) { string strEquipmentNumeric = new String(strEquipment.Where(Char.IsDigit).ToArray()); string strQueryTagName = "SELECT name from ip_analogdef where name like '" + strEquipmentNumeric + "%' " + IP21.TagAliasDescription[strIPFermParameter.ToUpper()]; strTagResult = IP21.GenericQuery("IP-FERM", strQueryTagName); string strQueryIP21 = "SELECT value FROM history where name='" + strTagResult + "' and request=4 order by ts desc;"; strIP21Result = IP21.GenericQuery("IP-FERM", strQueryIP21); if (Convert.ToDouble(strIP21Result) < Convert.ToDouble(strlimitvalue)) { strJobOperation = "GREATER"; } ; if (Convert.ToDouble(strIP21Result) > Convert.ToDouble(strlimitvalue)) { strJobOperation = "LESS"; } } } if (strlimit == "GREATER" || strlimit == "ABOVE") { strJobOperation = "GREATER"; } ; if (strlimit == "LESS" || strlimit == "BELOW") { strJobOperation = "LESS"; } ; //Create Job for Sentry if (strUserUnix != "test") { OracleSQL.OracleWrite("DATATOOLS", "insert into MSAT_SERVICE_JOBS (JOB_ID,APPLICATION,REQUEST_TIME,EXPIRATION_TIME, " + "USER_ID,JOB_PARA_ID,JOB_CONTEXT,JOB_OPERATION,JOB_CRITERIA) values " + "(SQ_MSAT_SERVICE_JOB_ID.NextVal,'SentryService',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP+20," + "(select USER_ID from MSAT_TIMELY_USERS where UNIX='" + strUserUnix + "')," + "(select PARA_ID from MSAT_PARAMETERS where UPPER(PARA_NAME)='" + strJobParameter + "'),'" + strJobContext + "','" + strJobOperation + "','" + strJobCriteria + "')"); } strResult = "A Sentry alert for when " + strJobParameter + " on " + strJobContext + " is " + strJobOperation + " than " + strJobCriteria + " was created for you. This alert will auto expire in 20 days if not triggered"; } else { // Allow user to turn off phase change alerts if (strlimit == "DISABLE" || strlimit == "DEACTIVATE" || strlimit == "TURN OFF") { //if (string.IsNullOrEmpty(strlimitvalue)) { strJobCriteria = strModifier; }; if (strUserUnix != "test") { string strSQLDisablePhaseChange = "update MSAT_SERVICE_JOBS " + "set COMPLETION_TIME = CURRENT_TIMESTAMP, JOB_RESULT = 'Disabled by user' " + "where USER_ID=(select USER_ID from MSAT_TIMELY_USERS where UNIX='" + strUserUnix + "') " + "and JOB_OPERATION='STATE_CHANGE' " + "and JOB_CONTEXT='" + strJobContext + "'"; OracleSQL.OracleWrite("DATATOOLS", strSQLDisablePhaseChange); } strResult = "Alerts disabled for " + strJobParameter + " on " + strJobContext; } // Allow user to assign themselves equipment and get phase change alerts else if (strlimit == "ENABLE" || strlimit == "ACTIVATE" || strlimit == "TURN ON") { //if (string.IsNullOrEmpty(strlimitvalue)) { strJobCriteria = strModifier; }; // Note: no expiration time for phase change alerts if (strUserUnix != "test") { string strSQLPhaseChange = "insert into MSAT_SERVICE_JOBS (JOB_ID,APPLICATION,REQUEST_TIME, " + "USER_ID,JOB_PARA_ID,JOB_CONTEXT,JOB_OPERATION,JOB_CRITERIA) values " + "(SQ_MSAT_SERVICE_JOB_ID.NextVal,'SentryService',CURRENT_TIMESTAMP," + "(select USER_ID from MSAT_TIMELY_USERS where UNIX='" + strUserUnix + "')," + "(select PARA_ID from MSAT_PARAMETERS where UPPER(PARA_NAME)='" + strJobParameter + "'),'" + strJobContext + "','STATE_CHANGE','" + strJobCriteria + "')"; OracleSQL.OracleWrite("DATATOOLS", strSQLPhaseChange); } strResult = "Sentry will notify you when " + strJobParameter + " on " + strJobContext + " is changed/updated. " + "This monitoring will be active until you disable it. To disable, reply with disable, deactivate, or turn off"; } } return(strResult); }
public static string PredictPCVQuery(string strProduct, string strVesselClass, string strEquipment, string strRun, string strLot, double dblDuration, string strTimeFlag, string strlimitvalue, string strUserUnix) { //PCV prediction, only really useful for N-3 thru N-1, maybe at the beginning of N as well string strResult = "Sorry! I can't perform any PCV predictions with the given parameters, can you refine your request and try again?"; double dblResult = -1; //Sample Query //Durations in CCDB is measured in hours //select GLUCOSE from (select ABS(DURATION - 200) TIMEDIFF, DURATION, GLUCOSE from ISI.CCSAMPLES, ISI.CCBATCHES where ISI.CCSAMPLES.BATCHID=ISI.CCBATCHES.BATCHID //and SCALE='12000' and RUN=162 and PRODUCTID='rhuMAb VEGF G7 v1.2' order by SAMPLEID) order by TIMEDIFF //Define Base query string strSQLbase = "select <FIELD> from ISI.CCSAMPLES, ISI.CCBATCHES where ISI.CCSAMPLES.BATCHID=ISI.CCBATCHES.BATCHID"; //Combine all constraints string strConstraints = ConcatConstraints(strProduct, strVesselClass, strEquipment, strRun, strLot, null); //Find the top most in-progress run that matches the search criteria, because predictions are only applicable for a single, non-completed run. string strBatchID = GetCCDBBatchID(strConstraints, strUserUnix); if (!string.IsNullOrEmpty(strBatchID)) { //Calculate the slope and intercept string strSQLSlope = strSQLbase.Replace("<FIELD>", "ROUND(REGR_SLOPE(LN(PCV),DURATION),9)") + " and ISI.CCBATCHES.BATCHID= " + strBatchID + " order by DURATION"; string strSlope = OracleSQL.SimpleQuery("CCDB", strSQLSlope); string strSQLIntercept = strSQLbase.Replace("<FIELD>", "ROUND(REGR_INTERCEPT(LN(PCV),DURATION),9)") + " and ISI.CCBATCHES.BATCHID= " + strBatchID + " order by DURATION"; string strIntercept = OracleSQL.SimpleQuery("CCDB", strSQLIntercept); //Get the current duration string strSQLDuration = strSQLbase.Replace("<FIELD>", "distinct ROUND((SYSDATE-CAST((FROM_TZ(CAST(INOCTIME AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE))*24,9) VAL") + " and ISI.CCBATCHES.BATCHID= " + strBatchID; string strCurrentDuration = OracleSQL.SimpleQuery("CCDB", strSQLDuration); //Make the final calculation if (!string.IsNullOrEmpty(strSlope) && !string.IsNullOrEmpty(strIntercept) && !string.IsNullOrEmpty(strCurrentDuration)) { if (!string.IsNullOrEmpty(strTimeFlag) && !string.IsNullOrEmpty(strlimitvalue)) { //User is requesting a time based on a desired target value, go find it. Keep in mind duration is calculated in hours dblDuration = (Math.Log(Convert.ToDouble(strlimitvalue)) - Convert.ToDouble(strIntercept)) / Convert.ToDouble(strSlope); //Get the desired time from the duration if (dblDuration > Convert.ToDouble(strCurrentDuration)) { string strTimeQuery = strSQLbase.Replace("<FIELD>", "CAST((FROM_TZ(CAST(INOCTIME + " + Convert.ToDouble(dblDuration) / 24 + " AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE)") + " and ISI.CCBATCHES.BATCHID= " + strBatchID; strResult = OracleSQL.SimpleQuery("CCDB", strTimeQuery); } else { strResult = "The PCV for this batch has likely already exceeded " + strlimitvalue + "% by now"; } } else { dblResult = Math.Exp(Convert.ToDouble(strSlope) * (Convert.ToDouble(strCurrentDuration) + dblDuration / 3600) + Convert.ToDouble(strIntercept)); } } else { strResult = "Sorry! I can't make any PCV predictions as there isn't enough data yet. Please try again later"; } if (dblResult > -1) { strResult = Math.Round(dblResult, 2).ToString(); } ; } else { strResult = "Sorry! Either the batch you requested is already complete or I can't seem to find the batch you requested, can you refine your request and try again?"; } return(strResult); }
public static string ViabilityCrashQuery(string strProduct, string strVesselClass, string strEquipment, string strRun, string strLot) { //Viability Crash prediction...cuz why not string strResult = "Sorry! I can't analyze any viability crashes with the given parameters, can you refine your request and try again?"; double dblResult = -1; //Sample Query //Durations in CCDB is measured in hours //select VIABILITY from ISI.CCSAMPLES, ISI.CCBATCHES where ISI.CCSAMPLES.BATCHID=ISI.CCBATCHES.BATCHID //and SCALE='12000' and RUN=162 and PRODUCTID='rhuMAb VEGF G7 v1.2' order by SAMPLEID //Define Base query string strSQLbase = "select <FIELD> from ISI.CCSAMPLES, ISI.CCBATCHES where ISI.CCSAMPLES.BATCHID=ISI.CCBATCHES.BATCHID"; //Default class to 12kL if not provided with run and product (will be 99%) of searches if (!string.IsNullOrEmpty(strProduct) && !string.IsNullOrEmpty(strRun) && string.IsNullOrEmpty(strVesselClass)) { strVesselClass = "12KL"; } //Combine all constraints string strConstraints = ConcatConstraints(strProduct, strVesselClass, strEquipment, strRun, strLot, null); //Calculate the slope based on the last 4 viability points string strSQLSlope = strSQLbase.Replace("<FIELD>", "ROUND(REGR_SLOPE(VIABILITY,DURATION),9) from (select DURATION, VIABILITY, rownum CNT from (select DURATION, VIABILITY") + strConstraints + " order by DURATION desc)) where CNT<=4"; string strSlope = OracleSQL.SimpleQuery("CCDB", strSQLSlope); //Viability crash threshold is around -0.4, -0.3 might be threshold for early drop/crash if (!string.IsNullOrEmpty(strSlope)) { if (Convert.ToDouble(strSlope) <= -0.4) { //Viability drop detected, get the viability drop over the duration string strSQLDuration = strSQLbase.Replace("<FIELD>", "MAX(DURATION) - MIN(DURATION) from (select DURATION, VIABILITY, rownum CNT from (select DURATION, VIABILITY") + strConstraints + " order by DURATION desc)) where CNT<=4"; string strDuration = OracleSQL.SimpleQuery("CCDB", strSQLDuration); string strSQLViability = strSQLbase.Replace("<FIELD>", "MAX(VIABILITY) - MIN(VIABILITY) from (select DURATION, VIABILITY, rownum CNT from (select DURATION, VIABILITY") + strConstraints + " order by DURATION desc)) where CNT<=4"; string strViability = OracleSQL.SimpleQuery("CCDB", strSQLViability); if (!string.IsNullOrEmpty(strDuration) && !string.IsNullOrEmpty(strViability)) { strResult = "Uh oh...Viability crash detected! There was a viability drop of " + strViability + "% over " + strDuration + " hours"; } } else if (Convert.ToDouble(strSlope) <= -0.3 && Convert.ToDouble(strSlope) > -0.4) { strResult = "Hmm...there might be a viability drop with this batch, it might be too early to tell, suggest keeping an eye on it."; } else { strResult = "No viability crash detected. Culture looks fine =)"; } } else { strResult = "Sorry! There isn't enough viability data yet. Please try again later."; } if (dblResult > -1) { strResult = Math.Round(dblResult, 2).ToString(); } ; return(strResult); }
public static string PredictGlucoseQuery(string strProduct, string strVesselClass, string strEquipment, string strRun, string strLot, string strModifier, double dblDuration, string strTimeFlag, string strlimitvalue, string strUserUnix) { //Glucose prediction, becuase TK, caclulation done in hours string strResult = "Sorry! I can't perform any glucose predictions with the given parameters, can you refine your request and try again?"; double dblResult = -1; string strConstraint = null; string strSort = " and DURATION>0"; string strTestDate = "SYSDATE"; string strBeforeTestDate = ""; // For Tests if (strUserUnix == "test") { // To test glucose prediction as if today was that day strTestDate = "TO_DATE('7/20/2018 8:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')"; strBeforeTestDate = " and SAMPLETIME < " + strTestDate; } //Sample Query //Durations in CCDB is measured in hours //select GLUCOSE from (select ABS(DURATION - 200) TIMEDIFF, DURATION, GLUCOSE from ISI.CCSAMPLES, ISI.CCBATCHES where ISI.CCSAMPLES.BATCHID=ISI.CCBATCHES.BATCHID //and SCALEID='12000' and RUN=162 and PRODUCTID='rhuMAb VEGF G7 v1.2' order by SAMPLEID) order by TIMEDIFF //Define Base query string strSQLbase = "select <FIELD> from ISI.CCSAMPLES, ISI.CCBATCHES where ISI.CCSAMPLES.BATCHID=ISI.CCBATCHES.BATCHID"; //Default scale to 12kL as that'll be 99% of searches if (!string.IsNullOrEmpty(strRun) && string.IsNullOrEmpty(strVesselClass) && string.IsNullOrEmpty(strLot)) { strVesselClass = "12KL"; } ; //Combine all constraints string strConstraints = ConcatConstraints(strProduct, strVesselClass, strEquipment, strRun, strLot, null); //Find the top most in-progress run that matches the search criteria, because predictions are only applicable for a single, non-completed run. string strBatchID = GetCCDBBatchID(strConstraints, strUserUnix); //If proper batch is found, make the calculation if (!string.IsNullOrEmpty(strBatchID)) { //Find the duration of the previous max glucose string strSQLFinal = strSQLbase.Replace("<FIELD>", "DURATION from (select CNT, DURATION, GLUCOSE, ROUND(POWER((GLUCOSE - lag(GLUCOSE) over(order by CNT desc)) / GLUCOSE,2),2) MARK " + " from (select rownum CNT, DURATION, GLUCOSE") + " and ISI.CCBATCHES.BATCHID= " + strBatchID + strBeforeTestDate + " order by DURATION desc)) where MARK > 1 order by DURATION desc"; //string strSQLFinal = strSQLbase.Replace("<FIELD>", "DURATION from (select CNT, DURATION, GLUCOSE, ROUND(POWER((GLUCOSE - lag(GLUCOSE) over(order by CNT desc)) / GLUCOSE,2),2) MARK " + // " from (select rownum CNT, DURATION, GLUCOSE") + " and ISI.CCBATCHES.BATCHID= " + strBatchID + " order by DURATION desc)) where MARK > 1 order by DURATION desc"; string strMarkDuration = OracleSQL.SimpleQuery("CCDB", strSQLFinal); if (!string.IsNullOrEmpty(strMarkDuration)) { strSort = " and DURATION>" + strMarkDuration; } ; //Use the last three data points if it's TK's formula if (strModifier == "TK") { strConstraint = " from (select DURATION, GLUCOSE, rownum CNT from (select DURATION, GLUCOSE "; strSort = strSort + " order by DURATION desc)) where CNT<=3 "; } //Calculate the slope and intercept string strSQLSlope = strSQLbase.Replace("<FIELD>", "ROUND(REGR_SLOPE(GLUCOSE,DURATION),9)" + strConstraint) + " and ISI.CCBATCHES.BATCHID= " + strBatchID + strSort + strBeforeTestDate; string strSlope = OracleSQL.SimpleQuery("CCDB", strSQLSlope); string strSQLIntercept = strSQLbase.Replace("<FIELD>", "ROUND(REGR_INTERCEPT(GLUCOSE,DURATION),9)" + strConstraint) + " and ISI.CCBATCHES.BATCHID= " + strBatchID + strSort + strBeforeTestDate; string strIntercept = OracleSQL.SimpleQuery("CCDB", strSQLIntercept); //Get the current duration string strSQLDuration = strSQLbase.Replace("<FIELD>", "distinct ROUND((" + strTestDate + "-CAST((FROM_TZ(CAST(INOCTIME AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE))*24,9) VAL") + " and ISI.CCBATCHES.BATCHID= " + strBatchID; string strCurrentDuration = OracleSQL.SimpleQuery("CCDB", strSQLDuration); //Make the final calculation if (!string.IsNullOrEmpty(strSlope) && !string.IsNullOrEmpty(strIntercept) && !string.IsNullOrEmpty(strCurrentDuration)) { //Check if the user is requesting a timestamp if (!string.IsNullOrEmpty(strTimeFlag) && !string.IsNullOrEmpty(strlimitvalue)) { //User is requesting a time based on a desired target value, go find it. Keep in mind duration is calculated in hours dblDuration = (Convert.ToDouble(strlimitvalue) - Convert.ToDouble(strIntercept)) / Convert.ToDouble(strSlope); //Get the desired time from the duration if (dblDuration > Convert.ToDouble(strCurrentDuration)) { string strTimeQuery = strSQLbase.Replace("<FIELD>", "CAST((FROM_TZ(CAST(INOCTIME + " + Convert.ToDouble(dblDuration) / 24 + " AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE)") + " and ISI.CCBATCHES.BATCHID= " + strBatchID; strResult = OracleSQL.SimpleQuery("CCDB", strTimeQuery); strResult = "According to my calculations, the glucose value for this batch will drop below " + strlimitvalue + " g/L on " + strResult; } else { strResult = "The glucose value for this batch has likely already exceeded " + strlimitvalue + " g/L by now"; } } else { dblResult = Convert.ToDouble(strSlope) * (Convert.ToDouble(strCurrentDuration) + dblDuration / 3600) + Convert.ToDouble(strIntercept); } } else { strResult = "Sorry! I can't make glucose consumption prediction for the batch you requested as a batch/glucose feed just occurred. Please try again later."; } } else { strResult = "Sorry! Either the batch you requested is already complete or I can't seem to find the batch you requested, can you refine your request and try again?"; } if (dblResult > -1) { strResult = Math.Round(dblResult, 2).ToString(); } ; return(strResult); }
//Main Calls public static string BatchQuery(string strParameter, string strProduct, string strVesselClass, string strEquipment, string strRun, string strLot, string strStation, string strListFlag) { //Initialize variables string strResult = "Sorry! I can't seem to find the batch data you requested, can you refine your request and try again?"; string strScaleID = null; string strPrettyPrint = null; string strParameterField = parameterField[strParameter.ToUpper()]; if (!string.IsNullOrEmpty(strVesselClass)) { strScaleID = ScaleID[strVesselClass.ToUpper()]; } ; //Specific Batch Query Example //select * from ISI.CCBATCHES where SCALEID='12000' and RUN=162 and PRODUCTID='rhuMAb VEGF G7 v1.2' order by INOCTIME //Current Active Batches Example //select * from ISI.CCBATCHES where HARVESTTIME is null and INOCTIME > (SYSDATE - 30) order by INOCTIME desc //Define Base query string strSQLbase = "select <FIELD> from ISI.CCBATCHES where ISI.CCBATCHES.BATCHID is not null"; //For parameters that could also be search criterias (e.g. Run/Lot/Equipment), check if they're the target parameter, if so, null out the value switch (strParameter.ToUpper()) { case "PRODUCT": strProduct = null; break; case "SCALE": strVesselClass = null; break; case "EQUIPMENT": strEquipment = null; break; case "TANK": strEquipment = null; break; case "VESSEL": strEquipment = null; break; case "FERM": strEquipment = null; break; case "BIOREACTOR": strEquipment = null; break; case "RUN": strRun = null; break; case "LOT": strLot = null; break; case "STATION": strStation = null; break; default: break; } //Combine all constraints string strConstraints = ConcatConstraints(strProduct, strVesselClass, strEquipment, strRun, strLot, strStation); string strConstraintsPP = PrettyPrintConstraints(strProduct, strVesselClass, strEquipment, strRun, strLot, strStation); //Check if user wants to return a list if (!string.IsNullOrEmpty(strListFlag)) { string[] strList = null; string strSQLFinal = strSQLbase.Replace("<FIELD>", "distinct " + strParameterField) + strConstraints + " and HARVESTTIME is null and INOCTIME > (SYSDATE - 30)"; strList = OracleSQL.ListQuery("CCDB", strSQLFinal); strPrettyPrint = "The following " + strConstraintsPP + " " + strParameter.ToLower() + "s are currently in process: " + string.Join(", ", strList); } else { string strSQLFinal = strSQLbase.Replace("<FIELD>", strParameterField) + strConstraints + " order by INOCTIME desc"; strResult = OracleSQL.SimpleQuery("CCDB", strSQLFinal); strPrettyPrint = "The " + strParameter + " value for " + strConstraintsPP + " is " + strResult + " " + parameterUOM[strParameter.ToUpper()]; } //Pretty print the resul return(strPrettyPrint.Trim()); }
public static string SampleQuery(string strParameter, string strProduct, string strVesselClass, string strEquipment, string strRun, string strLot, string strStation, string strModifier, double dblDuration, string sTimeflag) { //Initialize variables, default error message string strResult = "Sorry! I can't seem to find the sample data you requested, can you refine your request and try again?"; string strParameterField = parameterField[strParameter.ToUpper()]; //Treat lack of qualifers as "current" string strSQLSort = " order by SAMPLEID desc"; if (string.IsNullOrEmpty(strModifier)) { strModifier = "CURRENT"; } ; //Sample Query //select * from ISI.CCSAMPLES, ISI.CCBATCHES where ISI.CCSAMPLES.BATCHID=ISI.CCBATCHES.BATCHID and SCALEID='12000' and RUN=162 and PRODUCTID='rhuMAb VEGF G7 v1.2' order by SAMPLEID //Define Base query string strSQLbase = "select <FIELD> from ISI.CCSAMPLES, ISI.CCBATCHES where ISI.CCSAMPLES.BATCHID=ISI.CCBATCHES.BATCHID"; //Combine all constraints string strConstraints = ConcatConstraints(strProduct, strVesselClass, strEquipment, strRun, strLot, strStation); string strConstraintsPP = PrettyPrintConstraints(strProduct, strVesselClass, strEquipment, strRun, strLot, strStation); //Find the most likely batch that matches search criteria string strBatchID = OracleSQL.SimpleQuery("CCDB", "SELECT ISI.CCBATCHES.BATCHID FROM ISI.CCBATCHES where ISI.CCBATCHES.BATCHID is not null" + strConstraints + " order by INOCTIME desc"); if (!string.IsNullOrEmpty(strBatchID)) { if (!string.IsNullOrEmpty(strModifier)) { switch (strModifier.ToUpper()) { case "INITIAL": strSQLSort = " order by SAMPLEID"; break; case "FINAL": strSQLSort = " order by SAMPLEID desc"; break; case "FIRST": strSQLSort = " order by SAMPLEID"; break; case "LAST": strSQLSort = " order by SAMPLEID desc"; break; case "CURRENT": strSQLSort = " order by SAMPLEID desc"; break; case "PREVIOUS": strSQLSort = " order by SAMPLEID desc"; strParameterField = "lag(" + strParameterField + ")over(order by SAMPLEID)"; break; case "MIN": strParameterField = "MIN(" + strParameterField + ")"; break; case "MAX": strParameterField = "MAX(" + strParameterField + ")"; break; case "AVERAGE": strParameterField = "ROUND(AVG(" + strParameterField + "),2)"; break; case "PEAK": strParameterField = "MAX(" + strParameterField + ")"; break; default: break; } } //Handle if user is requesting a data point from a specific duration if (dblDuration > 0) { strSQLSort = " order by SAMPLEID) order by TIMEDIFF"; string strSQLFinal = strSQLbase.Replace("<FIELD>", strParameterField + " from (select ABS(DURATION - " + dblDuration / 3600 + " ) TIMEDIFF, DURATION, " + strParameterField) + " and ISI.CCBATCHES.BATCHID= " + strBatchID + strSQLSort; strResult = OracleSQL.SimpleQuery("CCDB", strSQLFinal); } else { string strSQLFinal = strSQLbase.Replace("<FIELD>", strParameterField) + " and ISI.CCBATCHES.BATCHID= " + strBatchID + strSQLSort; strResult = OracleSQL.SimpleQuery("CCDB", strSQLFinal); } } if (!string.IsNullOrEmpty(sTimeflag)) { string strPrettyPrint = "The " + strModifier.ToLower() + " " + strParameter + " time for " + strConstraintsPP + " was on " + strResult; return(strPrettyPrint.Trim()); } else { //Pretty print the result string strPrettyPrint = "The " + strModifier.ToLower() + " " + strParameter + " value for " + strConstraintsPP + " is " + strResult + " " + parameterUOM[strParameter.ToUpper()]; return(strPrettyPrint.Trim()); } }
public static string DataQuery(string strParameter, string strProduct, string strVesselClass, string strEquipment, string strRun, string strLot, string strStation, string strModifier, double dblDuration) { //Querys IP21 based on start and end times or equipment found in CCDB string strResult = "Sorry! I'm having difficulties connecting to IP21 right now, please try again later"; string strTagResult = null; string strTagUOM = null; string strStartTimeResult = null; string strEndTimeResult = null; string strEquipmentNumeric = null; string strDurationPrint = null; string strScaleID = null; if (string.IsNullOrEmpty(strModifier)) { strModifier = "CURRENT"; } ; if (!string.IsNullOrEmpty(strVesselClass)) { strScaleID = ScaleID[strVesselClass.ToUpper()]; } ; string strSQLSort = " order by INOCTIME desc"; //Define Base query string strSQLbase = "select <FIELD> from ISI.CCBATCHES where ISI.CCBATCHES.BATCHID is not null"; string strEqipmentResult = strEquipment; //Combine all constraints string strConstraints = CCDB.ConcatConstraints(strProduct, strVesselClass, strEquipment, strRun, strLot, strStation); string strConstraintsPP = CCDB.PrettyPrintConstraints(strProduct, strVesselClass, strEquipment, strRun, strLot, strStation); //Find the most likely batch that matches search criteria, if equipment modifer is filled if (string.IsNullOrEmpty(strEquipment) && string.IsNullOrEmpty(strStation)) { // Get scale ID to determine if it's seed train if (string.IsNullOrEmpty(strScaleID)) { string strSQLScaleID = strSQLbase.Replace("<FIELD>", "ISI.CCBATCHES.SCALEID") + strConstraints + strSQLSort; strScaleID = OracleSQL.SimpleQuery("CCDB", strSQLScaleID); } //Get station number for seed train, ferm id for others if (strScaleID == "20") { string strSQLStation = strSQLbase.Replace("<FIELD>", "ISI.CCBATCHES.STATION") + strConstraints + strSQLSort; strStation = OracleSQL.SimpleQuery("CCDB", strSQLStation); } else { string strSQLEquipment = strSQLbase.Replace("<FIELD>", "ISI.CCBATCHES.FERMID") + strConstraints + strSQLSort; strEqipmentResult = OracleSQL.SimpleQuery("CCDB", strSQLEquipment); } } //If there's a seed train station, assign it to the equipment variable if (string.IsNullOrEmpty(strEquipment) && !string.IsNullOrEmpty(strStation)) { strEquipmentNumeric = strStation; } //Extract the numerical value only from the equipment name if (!string.IsNullOrEmpty(strEquipment)) { strEquipmentNumeric = new String(strEqipmentResult.Where(Char.IsDigit).ToArray()); } //Extract the numerical value only from the equipment name if (string.IsNullOrEmpty(strEquipment) && string.IsNullOrEmpty(strStation)) { strEquipmentNumeric = new String(strEqipmentResult.Where(Char.IsDigit).ToArray()); } //Re-Define search windows if a duration is detected if (dblDuration > 0) { string strSQLStartTime = "select TO_CHAR(SYSDATE - " + dblDuration + " / 86400,'YYYY-MM-DD HH24:MI:SS') START_TIME from dual"; strStartTimeResult = OracleSQL.SimpleQuery("CCDB", strSQLStartTime); strDurationPrint = " in the last " + Math.Round(dblDuration / 3600) + " hours"; } else { string strSQLStartTime = strSQLbase.Replace("<FIELD>", "TO_CHAR(CAST((FROM_TZ(CAST(INOCTIME AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE),'YYYY-MM-DD HH24:MI:SS') START_TIME") + strConstraints + strSQLSort; strStartTimeResult = OracleSQL.SimpleQuery("CCDB", strSQLStartTime); string strSQLEndTime = strSQLbase.Replace("<FIELD>", "TO_CHAR(CAST((FROM_TZ(CAST(HARVESTTIME AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE),'YYYY-MM-DD HH24:MI:SS') END_TIME ") + strConstraints + strSQLSort; strEndTimeResult = OracleSQL.SimpleQuery("CCDB", strSQLEndTime); } // In order for PADME Service Library to work if (strParameter.ToUpper() == "TEMPERATURE") { strParameter = "TEMP"; } if (strParameter.ToUpper() == "AIR") { strParameter = "AIR SPARGE"; } if (strParameter.ToUpper() == "O2") { strParameter = "O2 SPARGE"; } //TODO: stop hardcoding //Build the tag query and query IP21 for the tag, store result in strTagResult string strQueryTagName = "SELECT name from ip_analogdef where name like '%" + strEquipmentNumeric + "%' " + IP21.TagAliasDescription[strParameter.ToUpper()] + " order by name desc"; strTagResult = IP21.GenericQuery("IP-FERM", strQueryTagName); string strQueryTagUOM = "SELECT ip_eng_units from ip_analogdef where name like '%" + strEquipmentNumeric + "%' " + IP21.TagAliasDescription[strParameter.ToUpper()] + " order by name desc"; strTagUOM = IP21.GenericQuery("IP-FERM", strQueryTagUOM); //Build the tag query and query IP21 for the tag, store result in strTagResult string strEndTimeConstraint = null; string strSortOrder = " order by ts desc"; //Treat lack of qualifers as "current" string strParameterField = "ROUND(value,3)"; //Add query modifiers if requested if (!string.IsNullOrEmpty(strModifier)) { switch (strModifier.ToUpper()) { case "CURRENT": strSortOrder = " order by ts desc"; break; case "MIN": case "MINIMUM": strParameterField = "MIN(" + strParameterField + ")"; strSortOrder = null; break; case "MAX": case "MAXIMUM": strParameterField = "MAX(" + strParameterField + ")"; strSortOrder = null; break; case "AVERAGE": strParameterField = "AVG(" + strParameterField + ")"; strSortOrder = null; break; case "RANGE": strParameterField = "MAX(" + strParameterField + ") - MIN(" + strParameterField + ")"; strSortOrder = null; break; default: break; } } if (!string.IsNullOrEmpty(strEndTimeResult)) { strEndTimeConstraint = "' and ts<=TIMESTAMP'" + strEndTimeResult; } string strQueryIP21 = "SELECT " + strParameterField + " FROM history where name='" + strTagResult + "' and ts>=TIMESTAMP'" + strStartTimeResult + strEndTimeConstraint + "'" + strSortOrder + ";"; strResult = IP21.GenericQuery("IP-FERM", strQueryIP21); //Pretty print the result string strPrettyPrint = "The " + strModifier.ToLower() + " " + strParameter + " value for " + strConstraintsPP + strDurationPrint + " is " + strResult + " " + strTagUOM; return(strPrettyPrint.Trim()); }
public static string TWQuery(string strUserUnix, string strFlag, string strParameter, string strRecordID, string strTimeflag) { string strResult = null; string strParameterField = null; string strSQLAssigneeUnix = null; string strSQLRecordID = null; string strPrettyPrint = null; //LIMS Query Example //select * from TWR_ALL_OPEN where ASSIGNEE_UNIX='yucheng'; //Understand what the user is searching for, set at the parameter field switch (strParameter.ToUpper()) { case "ASSIGNED": strParameterField = "REC_ASSIGNED_TO"; strParameter = "assignee"; break; case "RECORD": strParameterField = "REC_ID"; break; case "PARENT": strParameterField = "PARENT_REC_ID"; break; case "TYPE": strParameterField = "REC_PROJECT"; break; case "SUBTYPE": strParameterField = "REC_SUBTYPE"; break; case "STATUS": strParameterField = "REC_STATUS"; break; case "STATE": strParameterField = "REC_STATE"; break; case "DETAIL": strParameterField = "REC_SHORT_DESCRIPTION"; break; case "DESCRIPTION": strParameterField = "REC_SHORT_DESCRIPTION"; break; case "DUE": strParameterField = "TO_CLOSE"; break; case "UPDATE": case "UPDATED": strParameterField = "CAST((FROM_TZ(CAST(UPDATED_UTC AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE)"; strParameter = "updated"; break; case "CREATE": strParameterField = "CAST((FROM_TZ(CAST(CREATED_UTC AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE)"; break; case "OPEN": case "OPENED": strParameterField = "CAST((FROM_TZ(CAST(CREATED_UTC AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE)"; strParameter = "opened"; break; case "CLOSE": case "CLOSED": strParameterField = "CAST((FROM_TZ(CAST(CLOSED_UTC AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE)"; strParameter = "closed"; break; default: strParameterField = "REC_ID"; break; } //Define Base query string strSQLbase = "select <FIELD> from TWR where REC_ID is not null"; //Build the conditional clauses from information provided if (!string.IsNullOrEmpty(strRecordID)) { strSQLRecordID = " and REC_ID='" + strRecordID.Trim().ToLower() + "'"; } ; if (!string.IsNullOrEmpty(strUserUnix)) { strSQLAssigneeUnix = " and REC_ASSIGNED_TO_UNIX='" + strUserUnix.Trim().ToLower() + "'"; } ; if (strParameter == "ME" || strParameter == "MY") { string[] strList = null; string strSQLFinal = strSQLbase.Replace("<FIELD>", strParameterField) + strSQLAssigneeUnix + " and REC_STATUS='OPEN' order by REC_ID desc"; strList = OracleSQL.ListQuery("FOUNDRY", strSQLFinal); if (strList.Count() == 0) { strPrettyPrint = "Hooray, you currently have no active records assigned to you! Keep crushing it"; } else { strPrettyPrint = "The following open records are currently assigned to you: " + string.Join(", ", strList); } } else { string strSQLFinal = strSQLbase.Replace("<FIELD>", strParameterField) + strSQLRecordID + " order by REC_ID desc"; strResult = OracleSQL.SimpleQuery("FOUNDRY", strSQLFinal); if (!string.IsNullOrEmpty(strTimeflag)) { strPrettyPrint = "The record " + strRecordID + " was " + strParameter + " on " + strResult + " "; } else { strPrettyPrint = "The " + strParameter + " of the record " + strRecordID + " is " + strResult + " "; } } return(strPrettyPrint.Trim()); }