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