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