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