private int GeIndextNode(string dataType, int parentid) { int nodeid = 0; string nodeName = dataType + "s"; string query = $" where INDEXID = {parentid}"; IndexAccess idxAccess = new IndexAccess(); List <IndexModel> idxResults = idxAccess.SelectIndexesByQuery(query, databaseConnectionString); if (idxResults.Count == 1) { string indexNode = idxResults[0].TextIndexNode; int indexLevel = idxResults[0].IndexLevel + 1; string strProcedure = $"EXEC spGetNumberOfDescendants '{indexNode}', {indexLevel}"; query = ""; DataTable idx = _dbConn.GetDataTable(strProcedure, query); if (idx.Rows.Count == 1) { string condition = $"DATANAME={nodeName}"; var rows = indexTable.Select(condition); if (rows.Length > 0) { nodeid = Convert.ToInt32(rows[0]["INDEXID"]); } } if (nodeid == 0) { nodeid = _dbConn.InsertIndex(parentid, nodeName, nodeName, "", "", 0.0, 0.0); } } return(nodeid); }
public static string GetLogCurveDepths(DbUtilities dbConn, string dataObject) { JObject logObject = JObject.Parse(dataObject); string uwi = logObject["UWI"].ToString(); string curveName = logObject["CURVE_ID"].ToString(); string select = "select UWI, INDEX_VALUE, MEASURED_VALUE from WELL_LOG_CURVE_VALUE "; string query = $"where CURVE_ID = '{curveName}' and UWI = '{uwi}' order by INDEX_VALUE"; DataTable lc = dbConn.GetDataTable(select, query); //if (lc.Rows.Count == 0) log.Warning("No log curve values are available"); if (lc.Rows.Count > 0) { double[] measuredValue = new double[lc.Rows.Count]; double[] indexValue = new double[lc.Rows.Count];; for (int j = 0; j < lc.Rows.Count; j++) { measuredValue[j] = Convert.ToDouble(lc.Rows[j]["MEASURED_VALUE"]); indexValue[j] = Convert.ToDouble(lc.Rows[j]["INDEX_VALUE"]); } double topDepth = indexValue.Min(); logObject["MIN_INDEX"] = topDepth; double bottomDepth = indexValue.Max(); logObject["MAX_INDEX"] = bottomDepth; return(logObject.ToString()); } else { return(""); } }
public async Task <string> GetIndexData(string sourceName) { string result = ""; ConnectParameters connector = await Common.GetConnectParameters(azureConnectionString, sourceName); if (connector.SourceType == "DataBase") { string jsonConnectDef = await _fileStorage.ReadFile("connectdefinition", "PPDMDataAccess.json"); connector.DataAccessDefinition = jsonConnectDef; } else { Exception error = new Exception($"RuleManagement: data source must be a Database type"); throw error; } _dbConn.OpenConnection(connector); string strProcedure = $"EXEC spGetNumberOfDescendants '/', 1"; string query = ""; DataTable qc = _dbConn.GetDataTable(strProcedure, query); List <DmsIndex> index = ProcessAllChildren(qc); result = JsonConvert.SerializeObject(index, Formatting.Indented); _dbConn.CloseConnection(); return(result); }
public static DataTable GetNeighbors(DbUtilities dbConn, QcRuleSetup qcSetup) { int indexId = qcSetup.IndexId; RuleModel rule = JsonConvert.DeserializeObject <RuleModel>(qcSetup.RuleObject); string failRule = rule.FailRule; string path = $"$.{rule.DataAttribute}"; string strProcedure = $"EXEC spGetNeighborsNoFailuresDepth {indexId}, '%{failRule}%', '{path}'"; DataTable nb = dbConn.GetDataTable(strProcedure, ""); return(nb); }
private DataTable NewDataTable(string dataType) { string select = dataAccess.Select; string query = $" where 0 = 1"; DbUtilities db = new DbUtilities(); db.OpenWithConnectionString(connectionString); DataTable table = db.GetDataTable(select, query); db.CloseConnection(); return(table); }
public static ColumnProperties GetColumnSchema(DbUtilities dbConn, string sql) { ColumnProperties colProps = new ColumnProperties(); string attributeType = ""; string table = Common.GetTable(sql); string select = $"Select * from INFORMATION_SCHEMA.COLUMNS "; string query = $" where TABLE_NAME = '{table}'"; DataTable dt = dbConn.GetDataTable(select, query); if (dt.Rows.Count == 0) { throw new ArgumentException("Table does not exist"); } string[] sqlAttributes = Common.GetAttributes(sql); dt.CaseSensitive = false; foreach (string attribute in sqlAttributes) { string attributeIndex = attribute.Trim(); query = $"COLUMN_NAME = '{attributeIndex}'"; DataRow[] dtRows = dt.Select(query); if (dtRows.Length == 1) { attributeType = dtRows[0]["DATA_TYPE"].ToString(); if (attributeType == "nvarchar") { string charLength = dtRows[0]["CHARACTER_MAXIMUM_LENGTH"].ToString(); attributeType = attributeType + "(" + charLength + ")"; } else if (attributeType == "numeric") { string numericPrecision = dtRows[0]["NUMERIC_PRECISION"].ToString(); string numericScale = dtRows[0]["NUMERIC_SCALE"].ToString(); attributeType = attributeType + "(" + numericPrecision + "," + numericScale + ")"; } } else { //Console.WriteLine("Warning: attribute not found"); } colProps[attributeIndex] = attributeType; } return(colProps); }
private void CreateSqlSources(DbUtilities dbConn) { string sql = ""; try { sql = @"CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******'"; dbConn.SQLExecute(sql); } catch (Exception ex) { //logger.LogInformation("Problems creating master key, it may already exist, {ex}"); } sql = "Select * from sys.external_data_sources "; string query = " where name = 'PDOAzureBlob'"; DataTable dt = dbConn.GetDataTable(sql, query); if (dt.Rows.Count > 0) { sql = "DROP EXTERNAL DATA SOURCE PDOAzureBlob "; dbConn.SQLExecute(sql); } try { //sql = $"DROP DATABASE SCOPED CREDENTIAL {_credentials}"; //dbConn.SQLExecute(sql); } catch (Exception ex) { //logger.LogInformation($"Problems deleting credentials, it may not exist, {ex}"); } try { //sql = $"CREATE DATABASE SCOPED CREDENTIAL {_credentials} WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '{_secret}'"; //dbConn.SQLExecute(sql); //sql = $"CREATE EXTERNAL DATA SOURCE PDOAzureBlob WITH(TYPE = BLOB_STORAGE, LOCATION = '{_blobStorage}', CREDENTIAL = {_credentials})"; //dbConn.SQLExecute(sql); } catch (Exception ex) { //logger.LogInformation($"Problems crreating external data source, {ex}"); } }
public async Task <DataTable> GetLASWellHeaders(ConnectParameters source, ConnectParameters target) { string accessJson = await fileStorageService.ReadFile("connectdefinition", "PPDMDataAccess.json"); _dataDef = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson); //lasAccessJson = JObject.Parse(await fileStorageService.ReadFile("connectdefinition", "LASDataAccess.json")); string lasMappingsStr = await fileStorageService.ReadFile("connectdefinition", "LASDataAccess.json"); lasMappings = JsonConvert.DeserializeObject <LASMappings>(lasMappingsStr); List <string> files = new List <string>(); files = await GetLASFileNames(source.Catalog); DataAccessDef dataType = _dataDef.First(x => x.DataType == "WellBore"); string select = dataType.Select; string query = $" where 0 = 1"; _dbConn.OpenConnection(target); DataTable dt = _dbConn.GetDataTable(select, query); foreach (string file in files) { LASSections ls = await GetLASSections(source.Catalog, file); lasSections.Add(ls); GetVersionInfo(ls.versionInfo); string json = GetHeaderInfo(ls.wellInfo); DataRow row = dt.NewRow(); JObject jo = JObject.Parse(json); foreach (JProperty property in jo.Properties()) { string strValue = property.Value.ToString(); if (!string.IsNullOrEmpty(strValue)) { if (row.Table.Columns.Contains(property.Name)) { row[property.Name] = property.Value; } } } dt.Rows.Add(row); } return(dt); }
private string GetArrays(DataRow dataRow, string inJson) { string outJson = inJson; foreach (JToken array in _currentItem.Arrays) { string attribute = array["Attribute"].ToString(); string select = array["Select"].ToString(); string parentKeys = array["ParentKey"].ToString(); string query = GetParentKey(dataRow, parentKeys); query = " where " + query; DataTable dt = dbConn.GetDataTable(select, query); if (dt.Rows.Count == 1) { string result = dt.Rows[0]["ARRAY"].ToString(); JObject dataObject = JObject.Parse(outJson); dataObject[attribute] = result; outJson = dataObject.ToString(); } } return(outJson); }
public static string GetJsonForMissingDataObject(string parameters, DbUtilities dbConn) { string json = ""; MissingObjectsParameters missingObjectParms = new MissingObjectsParameters(); missingObjectParms = JsonConvert.DeserializeObject <MissingObjectsParameters>(parameters); string select = "SELECT TOP(1) JSONDATAOBJECT FROM pdo_qc_index"; string query = $" WHERE DATATYPE = '{missingObjectParms.DataType}'"; DataTable dt = dbConn.GetDataTable(select, query); if (dt.Rows.Count == 1) { json = dt.Rows[0]["JSONDATAOBJECT"].ToString(); JObject dataObject = JObject.Parse(json); foreach (KeyValuePair <String, JToken> tag in dataObject) { var tagName = tag.Key; var variable = tag.Value; var type = variable.Type; if (type == JTokenType.Float) { dataObject[tagName] = -99999.0; } else { dataObject[tagName] = ""; } } json = dataObject.ToString(); } else { json = "Error"; } return(json); }
public async Task <string> GetAttributeInfo(string sourceName, string dataType) { string json = ""; ConnectParameters connector = await Common.GetConnectParameters(azureConnectionString, sourceName); string accessJson = await _fileStorage.ReadFile("connectdefinition", "PPDMDataAccess.json"); List <DataAccessDef> accessDefs = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson); DataAccessDef dataAccess = accessDefs.First(x => x.DataType == dataType); string sql = dataAccess.Select; string table = Common.GetTable(sql); string query = $" where 0 = 1"; DbUtilities dbConn = new DbUtilities(); dbConn.OpenWithConnectionString(connector.ConnectionString); DataTable dt = dbConn.GetDataTable(sql, query); AttributeInfo attributeInfo = new AttributeInfo(); attributeInfo.DataAttributes = dt.GetColumnTypes(); json = JsonConvert.SerializeObject(attributeInfo); return(json); }
public static PredictionResult PredictFormationOrder(QcRuleSetup qcSetup, DbUtilities dbConn) { List <StratUnits> inv = new List <StratUnits>(); PredictionResult result = new PredictionResult { Status = "Failed" }; string formation; string tempTable = "#MinMaxAllFormationPick"; DataTable idx = new DataTable(); try { string select = "select * from #MinMaxAllFormationPick"; string query = ""; idx = dbConn.GetDataTable(select, query); } catch (Exception ex) { if (ex.InnerException.Message.Contains("Invalid object name")) { string select = $"EXEC spGetMinMaxAllFormationPick"; string query = ""; idx = dbConn.GetDataTable(select, query); string SQLCreateTempTable = Common.GetCreateSQLFromDataTable(tempTable, idx); dbConn.SQLExecute(SQLCreateTempTable); dbConn.BulkCopy(idx, tempTable); } else { throw; } } JObject dataObject = JObject.Parse(qcSetup.DataObject); formation = dataObject["STRAT_UNIT_ID"].ToString(); string tmpFormation = Common.FixAposInStrings(formation); string condition = $"STRAT_UNIT_ID = '{tmpFormation}'"; var rows = idx.Select(condition); if (rows.Length == 1) { RuleModel rule = JsonConvert.DeserializeObject <RuleModel>(qcSetup.RuleObject); int age = Convert.ToInt32(rows[0]["AGE"]); dataObject[rule.DataAttribute] = age; string remark = dataObject["REMARK"] + $";{rule.DataAttribute} has been predicted by QCEngine;"; dataObject["REMARK"] = remark; result.DataObject = dataObject.ToString(); result.DataType = rule.DataType; result.SaveType = "Update"; result.IndexId = qcSetup.IndexId; result.Status = "Passed"; } else if (rows.Length > 1) { throw new Exception("PredictFormationOrder: Multiple occurences of formation not allowed"); } return(result); }
public static PredictionResult PredictDominantLithology(QcRuleSetup qcSetup, DbUtilities dbConn) { PredictionResult result = new PredictionResult { Status = "Failed" }; JObject dataObject = JObject.Parse(qcSetup.DataObject); string uwi = dataObject["UWI"].ToString(); string curveName = "GR"; JToken value = dataObject.GetValue("PICK_DEPTH"); double? pickDepth = value.GetNumberFromJToken(); if (pickDepth == null || pickDepth == -99999.0) { result.Status = "Failed"; } else { string select = "SELECT JSONDATAOBJECT from pdo_qc_index"; string query = " where IndexNode = '/'"; DataTable idx = dbConn.GetDataTable(select, query); if (idx.Rows.Count == 1) { string jsondata = idx.Rows[0]["JSONDATAOBJECT"].ToString(); ConnectParameters source = JsonConvert.DeserializeObject <ConnectParameters>(jsondata); List <DataAccessDef> accessDefs = JsonConvert.DeserializeObject <List <DataAccessDef> >(source.DataAccessDefinition); DataAccessDef logType = accessDefs.First(x => x.DataType == "Log"); select = logType.Select; query = $" where CURVE_ID = '{curveName}' and UWI = '{uwi}'"; DataTable lc = dbConn.GetDataTable(select, query); if (lc.Rows.Count == 1) { double logNullValue = Common.GetDataRowNumber(lc.Rows[0], "NULL_REPRESENTATION"); DataAccessDef logCurvedType = accessDefs.First(x => x.DataType == "LogCurve"); select = logCurvedType.Select; query = $" where CURVE_ID = '{curveName}' and UWI = '{uwi}'"; DataTable lg = dbConn.GetDataTable(select, query); DataTable sortedCurve = RuleMethodUtilities.GetSortedLogCurve(lg, uwi); if (sortedCurve.Rows.Count > 0) { int rowNumber = RuleMethodUtilities.GetRowNumberForPickDepth(sortedCurve, (double)pickDepth); double?smoothLogValue = RuleMethodUtilities.GetSmoothLogValue(sortedCurve, logNullValue, rowNumber); string rock; if (smoothLogValue >= 0 & smoothLogValue < 10) { rock = "Salt"; } if (smoothLogValue >= 10 & smoothLogValue < 20) { rock = "Limestone"; } else if (smoothLogValue >= 20 & smoothLogValue < 55) { rock = "Sandstone"; } else if (smoothLogValue >= 55 & smoothLogValue < 150) { rock = "Shale"; } else { rock = "Unknown"; } dataObject["DOMINANT_LITHOLOGY"] = rock; string remark = dataObject["REMARK"] + $";Pick depth has been predicted by QCEngine"; dataObject["REMARK"] = remark; RuleModel rule = JsonConvert.DeserializeObject <RuleModel>(qcSetup.RuleObject); result.DataObject = dataObject.ToString(); result.DataType = rule.DataType; result.SaveType = "Update"; result.IndexId = qcSetup.IndexId; result.Status = "Passed"; } } } } return(result); }