Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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());
        }
Esempio n. 3
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());
        }