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 Predictions(string azureConnectionString)
{
_azureConnectionString = azureConnectionString;
var builder = new ConfigurationBuilder();
IConfiguration configuration = builder.Build();
_fileStorage = new AzureFileStorageServiceCommon(configuration);
_fileStorage.SetConnectionString(azureConnectionString);
_dbConn = new DbUtilities();
_dp = new DapperDataAccess();
_indexData = new IndexDBAccess(_dp);
}
private void UpdateDatabase(string jsonDataObject, string connectionString, string dataType)
{
JObject dataObject = JObject.Parse(jsonDataObject);
dataObject["ROW_CHANGED_BY"] = Environment.UserName;
jsonDataObject = dataObject.ToString();
jsonDataObject = Helpers.Common.SetJsonDataObjectDate(jsonDataObject, "ROW_CHANGED_DATE");
DbUtilities dbConn = new DbUtilities();
dbConn.OpenWithConnectionString(connectionString);
dbConn.UpdateDataObject(jsonDataObject, dataType);
dbConn.CloseConnection();
}
private void DeleteInDatabase(ConnectParameters connector, IndexModel indexItem)
{
string dataType = indexItem.DataType;
string dataKey = indexItem.DataKey;
List <DataAccessDef> accessDefs = JsonConvert.DeserializeObject <List <DataAccessDef> >(connector.DataAccessDefinition);
DataAccessDef accessDef = accessDefs.First(x => x.DataType == dataType);
string select = accessDef.Select;
string dataTable = Common.GetTable(select);
string dataQuery = "where " + dataKey;
DbUtilities dbConn = new DbUtilities();
dbConn.OpenWithConnectionString(connector.ConnectionString);
dbConn.DBDelete(dataTable, dataQuery);
dbConn.CloseConnection();
}
private async Task CreateFunctions(ConnectParameters connector)
{
string sql = await ReadDatabaseFile("Functions.sql");
string[] commandText = sql.Split(new string[] { String.Format("{0}GO{0}", Environment.NewLine) }, StringSplitOptions.RemoveEmptyEntries);
DbUtilities dbConn = new DbUtilities();
dbConn.OpenConnection(connector);
for (int x = 0; x < commandText.Length; x++)
{
if (commandText[x].Trim().Length > 0)
{
dbConn.SQLExecute(commandText[x]);
}
}
}
private void BuildGetProcedure(DbUtilities dbConn, string dataType, DataAccessDef accessDef)
{
string sqlCommand = $"DROP PROCEDURE IF EXISTS spGet{dataType} ";
dbConn.SQLExecute(sqlCommand);
sqlCommand = "";
string sql = accessDef.Select;
sqlCommand = sqlCommand + $"CREATE PROCEDURE spGet{dataType} ";
sqlCommand = sqlCommand + " AS ";
sqlCommand = sqlCommand + " BEGIN ";
sqlCommand = sqlCommand + sql;
sqlCommand = sqlCommand + " END";
dbConn.SQLExecute(sqlCommand);
}
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 async Task CreateDMSModel(DataModelParameters dmParameters, ConnectParameters connector)
{
try
{
string sql = await ReadDatabaseFile("DataScienceManagement.sql");
string sqlFunctions = await ReadDatabaseFile("InternalRuleFunctions.sql");
DbUtilities dbConn = new DbUtilities();
dbConn.OpenConnection(connector);
dbConn.SQLExecute(sql);
dbConn.SQLExecute(sqlFunctions);
//CreateSqlSources(dbConn);
dbConn.CloseConnection();
string fileName = "WellBore.json";
string taxonomy = await ReadDatabaseFile(fileName);
await _fileStorage.SaveFile(dmParameters.FileShare, fileName, taxonomy);
fileName = "PPDMDataAccess.json";
string definition = await ReadDatabaseFile(fileName);
await _fileStorage.SaveFile("connectdefinition", fileName, definition);
fileName = "LASDataAccess.json";
definition = await ReadDatabaseFile(fileName);
await _fileStorage.SaveFile("connectdefinition", fileName, definition);
fileName = "CSVDataAccess.json";
definition = await ReadDatabaseFile(fileName);
await _fileStorage.SaveFile("connectdefinition", fileName, definition);
fileName = "PPDMReferenceTables.json";
definition = await ReadDatabaseFile(fileName);
await _fileStorage.SaveFile("connectdefinition", fileName, definition);
}
catch (Exception ex)
{
Exception error = new Exception("Create DMS Model Error: ", ex);
throw error;
}
}
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 RuleManagement(string azureConnectionString)
{
this.azureConnectionString = azureConnectionString;
var builder = new ConfigurationBuilder();
IConfiguration configuration = builder.Build();
_fileStorage = new AzureFileStorageServiceCommon(configuration);
_fileStorage.SetConnectionString(azureConnectionString);
_azureDataTables = new AzureDataTable(configuration);
_azureDataTables.SetConnectionString(azureConnectionString);
_predictionSetData = new PredictionSetData(_azureDataTables);
_dp = new DapperDataAccess();
_db = new ADODataAccess();
_ruleData = new RuleData(_dp, _db);
_functionData = new FunctionData(_dp);
_dbConn = new DbUtilities();
}
private void BuildGetProcedureWithId(DbUtilities dbConn, string dataType, DataAccessDef accessDef)
{
string sqlCommand = $"DROP PROCEDURE IF EXISTS spGetWithId{dataType} ";
dbConn.SQLExecute(sqlCommand);
sqlCommand = "";
string sql = accessDef.Select;
string query = " WHERE ID = @id ";
sqlCommand = sqlCommand + $"CREATE PROCEDURE spGetWithId{dataType} ";
sqlCommand = sqlCommand + " @id INT ";
sqlCommand = sqlCommand + " AS ";
sqlCommand = sqlCommand + " BEGIN ";
sqlCommand = sqlCommand + sql + query;
sqlCommand = sqlCommand + " END";
dbConn.SQLExecute(sqlCommand);
}
private void CreateUserDefinedTypes(DbUtilities dbConn, ColumnProperties attributeProperties, string sql, string dataType)
{
string[] tableAttributes = Common.GetAttributes(sql);
string comma = "";
string attributes = "";
foreach (var word in tableAttributes)
{
string attribute = word.Trim();
string dataProperty = attributeProperties[attribute];
attributes = attributes + comma + attribute + " " + dataProperty;
comma = ",";
}
string sqlCommand = $"CREATE TYPE [dbo].[UDT{dataType}] AS TABLE ( ";
sqlCommand = sqlCommand + attributes + ")";
dbConn.SQLExecute(sqlCommand);
}
private void BuildGetProcedureWithQcString(DbUtilities dbConn, DataAccessDef accessDef)
{
string sqlCommand = $"DROP PROCEDURE IF EXISTS spGetWithQcStringIndex ";
dbConn.SQLExecute(sqlCommand);
sqlCommand = "";
string sql = accessDef.Select;
sqlCommand = sqlCommand + $"CREATE PROCEDURE spGetWithQcStringIndex ";
sqlCommand = sqlCommand + " @qcstring VARCHAR(10) ";
sqlCommand = sqlCommand + " AS ";
sqlCommand = sqlCommand + " BEGIN ";
sqlCommand = sqlCommand + " declare @query as varchar(240) ";
sqlCommand = sqlCommand + " set @query = '%' + @qcstring + ';%'";
sqlCommand = sqlCommand + sql;
sqlCommand = sqlCommand + " WHERE QC_STRING like @query";
sqlCommand = sqlCommand + " END";
dbConn.SQLExecute(sqlCommand);
}
private async Task CreateUpdateStoredProcedure(DbUtilities dbConn)
{
List <DataAccessDef> accessDefs = await GetDataAccessDefinitions();
var dataTypes = accessDefs.Select(s => s.DataType).Where(s => s != "Index").ToList();
foreach (string dataType in dataTypes)
{
DataAccessDef accessDef = accessDefs.First(x => x.DataType == dataType);
BuildUpdateProcedure(dbConn, dataType, accessDef);
}
RuleManagement rm = new RuleManagement();
string type = "Rules";
DataAccessDef ruleDef = rm.GetDataAccessDefinition(type);
BuildUpdateProcedure(dbConn, type, ruleDef);
type = "Functions";
DataAccessDef functionDef = rm.GetDataAccessDefinition(type);
BuildUpdateProcedure(dbConn, type, functionDef);
}
private async Task CreateGetStoredProcedure(DbUtilities dbConn)
{
RuleManagement rm = new RuleManagement();
string type = "Rules";
DataAccessDef ruleDef = rm.GetDataAccessDefinition(type);
BuildGetProcedure(dbConn, type, ruleDef);
BuildGetProcedureWithId(dbConn, type, ruleDef);
type = "Functions";
DataAccessDef functionDef = rm.GetDataAccessDefinition(type);
BuildGetProcedure(dbConn, type, functionDef);
BuildGetProcedureWithId(dbConn, type, functionDef);
IndexAccess ia = new IndexAccess();
type = "Index";
DataAccessDef indexDef = ia.GetDataAccessDefinition();
BuildGetProcedure(dbConn, type, indexDef);
BuildGetProcedureWithQcString(dbConn, indexDef);
}
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 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);
}
private async Task CreatePpdmModifications(ConnectParameters connector)
{
try
{
string sql = await ReadDatabaseFile("PpdmModifications.sql");
string[] commandText = sql.Split(new string[] { String.Format("{0}GO{0}", Environment.NewLine) }, StringSplitOptions.RemoveEmptyEntries);
DbUtilities dbConn = new DbUtilities();
dbConn.OpenConnection(connector);
for (int x = 0; x < commandText.Length; x++)
{
if (commandText[x].Trim().Length > 0)
{
dbConn.SQLExecute(commandText[x]);
}
}
dbConn.CloseConnection();
}
catch (Exception ex)
{
Exception error = new Exception("Create DMS Model Error: ", ex);
throw error;
}
}
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);
}
private void BuildUpdateProcedure(DbUtilities dbConn, string dataType, DataAccessDef accessDef)
{
string comma;
string attributes;
string sqlCommand = $"DROP PROCEDURE IF EXISTS spUpdate{dataType} ";
dbConn.SQLExecute(sqlCommand);
sqlCommand = "";
string sql = accessDef.Select;
string[] keys = accessDef.Keys.Split(',');
string table = Common.GetTable(sql);
ColumnProperties attributeProperties = CommonDbUtilities.GetColumnSchema(dbConn, sql);
string[] tableAttributes = Helpers.Common.GetAttributes(sql);
sqlCommand = sqlCommand + $"CREATE PROCEDURE spUpdate{dataType} ";
sqlCommand = sqlCommand + "@json NVARCHAR(max) ";
sqlCommand = sqlCommand + "AS ";
sqlCommand = sqlCommand + "BEGIN ";
sqlCommand = sqlCommand + $"SELECT ";
comma = " ";
attributes = "";
foreach (var word in tableAttributes)
{
string attribute = word.Trim();
attributes = attributes + comma + attribute;
comma = ",";
}
sqlCommand = sqlCommand + attributes;
sqlCommand = sqlCommand + " INTO #TempJson ";
sqlCommand = sqlCommand + $" FROM OPENJSON(@json) ";
comma = "";
attributes = " WITH (";
foreach (var word in tableAttributes)
{
string attribute = word.Trim();
string dataProperty = attributeProperties[attribute];
attributes = attributes + comma + attribute + " " + dataProperty +
" '$." + attribute + "'";
comma = ",";
}
sqlCommand = sqlCommand + attributes;
sqlCommand = sqlCommand + ") AS jsonValues ";
sqlCommand = sqlCommand + $" UPDATE A ";
sqlCommand = sqlCommand + $" SET ";
comma = " ";
attributes = "";
foreach (var word in tableAttributes)
{
if (word != "Id")
{
string attribute = word.Trim();
attributes = attributes + comma + "A." + attribute + " = " + "B." + attribute;
comma = ",";
}
}
sqlCommand = sqlCommand + attributes;
sqlCommand = sqlCommand + $" FROM ";
sqlCommand = sqlCommand + $" {table} AS A ";
sqlCommand = sqlCommand + " INNER JOIN #TempJson AS B ON ";
comma = " ";
attributes = "";
foreach (string key in keys)
{
attributes = attributes + comma + "A." + key.Trim() + " = " + "B." + key.Trim();
comma = " AND ";
}
sqlCommand = sqlCommand + attributes;
sqlCommand = sqlCommand + " END";
dbConn.SQLExecute(sqlCommand);
}
public async Task LoadCSVFile(ConnectParameters source, ConnectParameters target, string fileName)
{
DateTime timeStart = DateTime.Now;
connectionString = target.ConnectionString;
string accessJson = await fileStorageService.ReadFile("connectdefinition", "PPDMDataAccess.json");
_dataDef = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson);
string referenceJson = await fileStorageService.ReadFile("connectdefinition", "PPDMReferenceTables.json");
_references = JsonConvert.DeserializeObject <List <ReferenceTable> >(referenceJson);
string csvJson = await fileStorageService.ReadFile("connectdefinition", "CSVDataAccess.json");
_csvDef = JsonConvert.DeserializeObject <List <CSVAccessDef> >(csvJson);
//Console.WriteLine("start reading csv file");
string csvText = await fileStorageService.ReadFile(source.Catalog, fileName);
DateTime timeEnd = DateTime.Now;
TimeSpan diff = timeEnd - timeStart;
//Console.WriteLine($"Time span, read all definitions files: {diff}");
string dataType = source.DataType.Remove(source.DataType.Length - 1, 1);
dataAccess = _dataDef.First(x => x.DataType == dataType);
CSVAccessDef csvAccess = _csvDef.First(x => x.DataType == dataType);
Dictionary <string, int> attributes = csvAccess.Mappings.ToDictionary();
Dictionary <string, string> constants = csvAccess.Constants.ToStringDictionary();
Dictionary <string, string> columnTypes = dt.GetColumnTypes();
DbUtilities dbConn = new DbUtilities();
dbConn.OpenWithConnectionString(connectionString);
string dataTypeSql = dataAccess.Select;
attributeProperties = CommonDbUtilities.GetColumnSchema(dbConn, dataTypeSql);
dbConn.CloseConnection();
//Console.WriteLine("Start parsing csv file");
using (TextReader csvStream = new StringReader(csvText))
{
var conf = new CsvConfiguration(CultureInfo.InvariantCulture)
{
//BadDataFound = null
};
using (var csv = new CsvReader(csvStream, conf))
{
csv.Read();
csv.ReadHeader();
string[] headerRow = csv.HeaderRecord;
var attributeMappings = new Dictionary <string, string>();
foreach (var item in attributes)
{
int colNumber = item.Value;
attributeMappings.Add(headerRow[colNumber], item.Key);
}
List <dynamic> csvRecords = csv.GetRecords <dynamic>().ToList();
timeEnd = DateTime.Now;
diff = timeEnd - timeStart;
//Console.WriteLine($"Time span, parsed cvs file into dynamic objects: {diff}");
foreach (var row in csvRecords)
{
DynamicObject newCsvRecord = new DynamicObject();
foreach (var item in row)
{
if (attributeMappings.ContainsKey(item.Key))
{
string dbAttribute = attributeMappings[item.Key];
string value = item.Value;
string dataProperty = attributeProperties[dbAttribute];
if (dataProperty.Contains("varchar"))
{
string numberString = Regex.Match(dataProperty, @"\d+").Value;
int maxCharacters = Int32.Parse(numberString);
if (value.Length > maxCharacters)
{
value = value.Substring(0, maxCharacters);
}
}
newCsvRecord.AddProperty(dbAttribute, value);
}
}
FixKey(newCsvRecord);
}
timeEnd = DateTime.Now;
diff = timeEnd - timeStart;
//Console.WriteLine($"Time span, fixed dynamic objects: {diff}");
dt = DynamicToDT(newCsvRecords);
timeEnd = DateTime.Now;
diff = timeEnd - timeStart;
//Console.WriteLine($"Time span, transfer from csv to datatable: {diff}");
InsertTableToDatabase(attributes, dataType, target, constants);
}
}
timeEnd = DateTime.Now;
diff = timeEnd - timeStart;
//Console.WriteLine($"Time span, completion: {diff}");
}
private void BuildInsertProcedure(DbUtilities dbConn, string dataType, DataAccessDef accessDef)
{
string comma;
string attributes;
string sqlCommand = $"DROP PROCEDURE IF EXISTS spInsert{dataType}; ";
sqlCommand = sqlCommand + $"DROP TYPE IF EXISTS[dbo].[UDT{dataType}];";
dbConn.SQLExecute(sqlCommand);
sqlCommand = "";
string sql = accessDef.Select;
string[] keys = accessDef.Keys.Split(',');
string table = Common.GetTable(sql);
ColumnProperties attributeProperties = CommonDbUtilities.GetColumnSchema(dbConn, sql);
string[] tableAttributes = Common.GetAttributes(sql);
tableAttributes = tableAttributes.Where(w => w != "Id").ToArray();
sqlCommand = sqlCommand + $"CREATE PROCEDURE spInsert{dataType} ";
sqlCommand = sqlCommand + " @json NVARCHAR(max) ";
sqlCommand = sqlCommand + " AS ";
sqlCommand = sqlCommand + " BEGIN ";
sqlCommand = sqlCommand + $" INSERT INTO {table }";
attributes = " (";
comma = "";
foreach (var word in tableAttributes)
{
string attribute = word.Trim();
attributes = attributes + comma + "[" + attribute + "]";
comma = ",";
}
attributes = attributes + ")";
sqlCommand = sqlCommand + attributes;
sqlCommand = sqlCommand + $" SELECT";
comma = " ";
attributes = "";
foreach (var word in tableAttributes)
{
string attribute = word.Trim();
attributes = attributes + comma + attribute;
comma = ",";
}
sqlCommand = sqlCommand + attributes;
sqlCommand = sqlCommand + $" FROM OPENJSON(@json)";
comma = "";
attributes = " WITH (";
foreach (var word in tableAttributes)
{
string attribute = word.Trim();
string dataProperty = attributeProperties[attribute];
attributes = attributes + comma + attribute + " " + dataProperty +
" '$." + attribute + "'";
comma = ",";
}
sqlCommand = sqlCommand + attributes;
sqlCommand = sqlCommand + ") AS jsonValues ";
sqlCommand = sqlCommand + " END";
dbConn.SQLExecute(sqlCommand);
}
public static PredictionResult PredictMissingDataObjects(QcRuleSetup qcSetup, DbUtilities dbConn)
{
PredictionResult result = new PredictionResult
{
Status = "Failed"
};
RuleModel rule = JsonConvert.DeserializeObject <RuleModel>(qcSetup.RuleObject);
string rulePar = rule.RuleParameters;
JObject ruleParObject = JObject.Parse(rulePar);
string dataType = ruleParObject["DataType"].ToString();
if (string.IsNullOrEmpty(rulePar))
{
throw new NullReferenceException("Rule parameter is null.");
}
string emptyJson = RuleMethodUtilities.GetJsonForMissingDataObject(rulePar, dbConn);
if (emptyJson == "Error")
{
throw new NullReferenceException("Could not create an empty json data object, maybe you are missing Datatype in parameters");
}
string json = RuleMethodUtilities.PopulateJsonForMissingDataObject(rulePar, emptyJson, qcSetup.DataObject);
if (json == "Error")
{
throw new NullReferenceException("Could not create an json data object, problems with keys in parameters");
}
json = RuleMethodUtilities.AddDefaultsForMissingDataObjects(rulePar, json);
if (json == "Error")
{
throw new NullReferenceException("Could not create an json data object, problems with defaults in parameters");
}
result.DataObject = json;
result.DataType = dataType;
result.SaveType = "Insert";
result.IndexId = qcSetup.IndexId;
result.Status = "Passed";
return(result);
}
public static PredictionResult PredictLogDepthAttributes(QcRuleSetup qcSetup, DbUtilities dbConn)
{
PredictionResult result = new PredictionResult
{
Status = "Failed"
};
JObject dataObject = JObject.Parse(qcSetup.DataObject);
RuleModel rule = JsonConvert.DeserializeObject <RuleModel>(qcSetup.RuleObject);
string jsonLog = RuleMethodUtilities.GetLogCurveDepths(dbConn, qcSetup.DataObject);
if (!string.IsNullOrEmpty(jsonLog))
{
JObject logObject = JObject.Parse(jsonLog);
string attribute = rule.DataAttribute;
dataObject[attribute] = logObject[attribute];
string remark = dataObject["REMARK"] + $";{attribute} was calculated from curve array;";
dataObject["REMARK"] = remark;
result.DataObject = dataObject.ToString();
result.DataType = rule.DataType;
result.SaveType = "Update";
result.IndexId = qcSetup.IndexId;
result.Status = "Passed";
}
return(result);
}
public IndexBuilder(IDataAccess sourceAccess)
{
dbConn = new DbUtilities();
this.sourceAccess = sourceAccess;
}
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);
}