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