public static string PhaseDescription(string strParameter, string strEquipment)
{
//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 strEquipmentNumeric = null;
string strModifier = "CURRENT";
string strPrettyPrint = null;
//Extract the numerical value only from the equipment name
if (!string.IsNullOrEmpty(strEquipment))
{
strEquipmentNumeric = new String(strEquipment.Where(Char.IsDigit).ToArray());
}
//Build the tag query and query IP21 for the tag, store result in strTagResult
string strQueryTagName = "SELECT name from IP_textDef where name like '" + strEquipmentNumeric + "%' " + " and ip_description like 'Phase Description'";
strTagResult = IP21.GenericQuery("IP-REC", strQueryTagName);
//Build the tag query and query IP21 for the tag, store result in strTagResult
string strSortOrder = "order by ip_trend_time desc"; //Treat lack of qualifers as "current"
string strParameterField = "case IP_TREND_VALUE when '' then 'null' else IP_TREND_VALUE end";
string strQueryIP21 = "SELECT " + strParameterField + " FROM \"" + strTagResult + "\" " + strSortOrder + ";";
strResult = IP21.GenericQuery("IP-REC", strQueryIP21);
if (strResult == "null")
{
strPrettyPrint = "There's no active " + strParameter + "s on " + strEquipment;
}
else
{
strPrettyPrint = "The " + strModifier.ToLower() + " " + strParameter + " for " + strEquipment + " is " + strResult;
}
//Pretty print the result
return(strPrettyPrint.Trim());
}
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 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 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());
}
