private async Task CreateStoredProcedures(ConnectParameters connector)
{
try
{
string sql = await ReadDatabaseFile("StoredProcedures.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]);
}
}
await CreateGetStoredProcedure(dbConn);
await CreateInsertStoredProcedure(dbConn);
await CreateUpdateStoredProcedure(dbConn);
dbConn.CloseConnection();
}
catch (Exception ex)
{
Exception error = new Exception("Create DMS Model Error: ", ex);
throw error;
}
}
public async Task ExecutePrediction(PredictionParameters parms)
{
string accessJson = await _fileStorage.ReadFile("connectdefinition", "PPDMDataAccess.json");
_accessDefs = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson);
ConnectParameters connector = await GetConnector(parms.DataConnector);
databaseConnectionString = connector.ConnectionString;
_dbConn.OpenConnection(connector);
string sourceConnector = GetSource();
if (parms.DataConnector == sourceConnector)
{
syncPredictions = true;
}
else
{
syncPredictions = false;
}
RuleManagement rules = new RuleManagement(_azureConnectionString);
RuleModel rule = await rules.GetRuleAndFunction(parms.DataConnector, parms.PredictionId);
manageIndexTable = new ManageIndexTable(_accessDefs, connector.ConnectionString, rule.DataType, rule.FailRule);
manageIndexTable.InitQCFlags(false);
await MakePredictions(rule, connector);
_dbConn.CloseConnection();
manageIndexTable.SaveQCFlags();
}
private async Task CreatePPDMModel(DataModelParameters dmParameters, ConnectParameters connector)
{
try
{
string sql = await _fileStorage.ReadFile(dmParameters.FileShare, dmParameters.FileName);
if (string.IsNullOrEmpty(sql))
{
Exception error = new Exception($"Empty data from {dmParameters.FileName}");
throw error;
}
else
{
DbUtilities dbConn = new DbUtilities();
dbConn.OpenConnection(connector);
dbConn.SQLExecute(sql);
dbConn.CloseConnection();
}
}
catch (Exception ex)
{
Exception error = new Exception("Create PPDM Model Error: ", ex);
throw error;
}
}
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 async Task <List <int> > ExecuteQcRule(DataQCParameters qcParms)
{
try
{
ConnectParameters connector = await GetConnector(qcParms.DataConnector);
RuleModel rule = await GetRuleAndFunctionInfo(qcParms.DataConnector, qcParms.RuleId);
string accessJson = await _fileStorage.ReadFile("connectdefinition", "PPDMDataAccess.json");
_accessDefs = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson);
DbUtilities dbConn = new DbUtilities();
dbConn.OpenConnection(connector);
manageQCFlags = new ManageIndexTable(_accessDefs, connector.ConnectionString, rule.DataType);
List <int> failedObjects = await QualityCheckDataType(dbConn, rule, connector);
dbConn.CloseConnection();
return(failedObjects);
}
catch (Exception ex)
{
Exception error = new Exception($"DataQc: Could process rule {qcParms.RuleId}, {ex}");
throw error;
}
}
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);
}
public void InitializeIndex(ConnectParameters connectionString, ConnectParameters source, string jsonTaxonomy)
{
_connectionString = connectionString.ConnectionString;
_taxonomy = jsonTaxonomy;
myIndex = new IndexDataCollection();
dbConn.OpenConnection(connectionString);
sourceAccess.OpenConnection(source, connectionString);
dataAccessDefs = JsonConvert.DeserializeObject <List <DataAccessDef> >(connectionString.DataAccessDefinition);
JsonIndexArray = JArray.Parse(jsonTaxonomy);
_idxData = new List <IndexFileData>();
foreach (JToken level in JsonIndexArray)
{
_idxData.Add(ProcessJTokens(level));
ProcessIndexArray(JsonIndexArray, level);
}
}
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 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;
}
}
