Beispiel #1
0
        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);
        }
Beispiel #2
0
        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);
        }
Beispiel #3
0
        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);
        }
Beispiel #4
0
        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);
        }
Beispiel #5
0
        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);
        }
Beispiel #6
0
        //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());
        }
Beispiel #7
0
        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());
            }
        }
Beispiel #8
0
        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());
        }
Beispiel #9
0
        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());
        }