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 MediaQuery(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 = "Sorry! I'm having difficulties connecting to MES right now, please try again later";
string strMediaBatchID = 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 "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);
}
//Get the media batch ID
if (strTrigger == "BATCH FEED")
{
strMediaBatchID = OracleSQL.SimpleQuery("MES", "select BATCH_ID from SSFMES.CO_PROC_RESULT_ST where RESULTS like '" + strLot.Trim() + "' and UPPER(UNIT_PROCEDURE_ID) like '%BATCH%FEED%'");
}
else
{
strMediaBatchID = OracleSQL.SimpleQuery("MES", "select BATCH_ID from SSFMES.CO_PROC_RESULT_ST where RESULTS like '" + strLot.Trim() + "' and UPPER(UNIT_PROCEDURE_ID) not like '%BATCH%FEED%'");
}
//If batch ID can't be found try it again in Genealogy table
if (string.IsNullOrEmpty(strMediaBatchID))
{
strMediaBatchID = 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='" + strMediaBatchID + "'");
}
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 '" + strMediaBatchID
+ "' 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 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());
}