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 void SaveQCFlagDapper()
{
idxAccess = new IndexAccess();
foreach (var idx in idxList)
{
idx.QC_String = qcFlags[idx.IndexId];
}
idxAccess.UpdateDataQCFlags(_connectionString, idxList);
}
private IndexRootJson GetIndexRootData()
{
IndexRootJson rootJson = new IndexRootJson();
string idxQuery = $" where INDEXNODE = '/'";
IndexAccess idxAccess = new IndexAccess();
List <IndexModel> idxResults = idxAccess.SelectIndexesByQuery(idxQuery, databaseConnectionString);
if (idxResults.Count > 0)
{
string jsonStringObject = idxResults[0].JsonDataObject;
rootJson = JsonConvert.DeserializeObject <IndexRootJson>(jsonStringObject);
}
return(rootJson);
}
public async Task ClearQCFlags(string source)
{
try
{
ConnectParameters connector = await GetConnector(source);
IndexAccess idxAccess = new IndexAccess();
idxAccess.ClearAllQCFlags(connector.ConnectionString);
}
catch (Exception ex)
{
Exception error = new Exception($"DataQc: Could not clear qc flags, {ex}");
throw error;
}
}
public async Task <List <QcResult> > GetQCRules(DataQCParameters qcParms)
{
List <QcResult> qcResult = new List <QcResult>();
ConnectParameters connector = await GetConnector(qcParms.DataConnector);
RuleManagement rules = new RuleManagement(_azureConnectionString);
IndexAccess idxAccess = new IndexAccess();
string jsonString = await rules.GetActiveQCRules(connector.SourceName);
qcResult = JsonConvert.DeserializeObject <List <QcResult> >(jsonString);
foreach (QcResult qcItem in qcResult)
{
string query = $" where QC_STRING like '%{qcItem.RuleKey};%'";
qcItem.Failures = idxAccess.IndexCountByQuery(query, connector.ConnectionString);
}
return(qcResult);
}
public async Task <List <PredictionCorrection> > GetPredictions(string source)
{
List <PredictionCorrection> predictionResults = new List <PredictionCorrection>();
ConnectParameters connector = await GetConnector(source);
RuleManagement rules = new RuleManagement(_azureConnectionString);
string jsonString = await rules.GetActivePredictionRules(source);
predictionResults = JsonConvert.DeserializeObject <List <PredictionCorrection> >(jsonString);
IndexAccess idxAccess = new IndexAccess();
foreach (PredictionCorrection predItem in predictionResults)
{
string query = $" where QC_STRING like '%{predItem.RuleKey};%'";
predItem.NumberOfCorrections = idxAccess.IndexCountByQuery(query, connector.ConnectionString);
}
return(predictionResults);
}
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);
}
private void UpdateAction(PredictionResult result, string qcStr)
{
string idxQuery = $" where INDEXID = {result.IndexId}";
IndexAccess idxAccess = new IndexAccess();
List <IndexModel> idxResults = idxAccess.SelectIndexesByQuery(idxQuery, databaseConnectionString);
if (idxResults.Count == 1)
{
string condition = $"INDEXID={result.IndexId}";
var rows = indexTable.Select(condition);
rows[0]["JSONDATAOBJECT"] = result.DataObject;
rows[0]["QC_STRING"] = qcStr;
indexTable.AcceptChanges();
if (syncPredictions)
{
string jsonDataObject = result.DataObject;
JObject dataObject = JObject.Parse(jsonDataObject);
dataObject["ROW_CHANGED_BY"] = Environment.UserName;
jsonDataObject = dataObject.ToString();
jsonDataObject = Helpers.Common.SetJsonDataObjectDate(jsonDataObject, "ROW_CHANGED_DATE");
string dataType = idxResults[0].DataType;
try
{
_dbConn.UpdateDataObject(jsonDataObject, dataType);
}
catch (Exception ex)
{
string error = ex.ToString();
//logger.LogWarning($"Error updating data object");
throw;
}
}
}
else
{
//logger.LogWarning("Cannot find data key during update");
}
}
public ManageIndexTable(List <DataAccessDef> accessDefs, string connectionString,
string dataType = "", string qcRule = "")
{
_accessDefs = accessDefs;
IndexAccess idxAccess = new IndexAccess();
string select = idxAccess.GetSelectSQL();
if (!string.IsNullOrEmpty(dataType))
{
select = select + $" where DATATYPE = '{dataType}'";
if (!string.IsNullOrEmpty(qcRule))
{
select = select + $" and QC_STRING like '%{qcRule}%'";
}
}
sqlCn = new SqlConnection(connectionString);
indexAdapter = new SqlDataAdapter();
indexAdapter.SelectCommand = new SqlCommand(select, sqlCn);
indexTable = new DataTable();
indexAdapter.Fill(indexTable);
qcFlags = new QcFlags();
}