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