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