public bool FillRdField(FillFieldRdColumnModel param)
{
var result = true;
try
{
LegacyCodeHandler legacyCodeHandler = new LegacyCodeHandler(_legacyCodeHandler);
var rd = _dbKyStatic.Queryable <ResultData>().Where(x => x.Id == param.ResultDataId).ToList().FirstOrDefault();
var tableName = rd.TableName;
StringBuilder filter = new StringBuilder();
filter.Append($" update dbo.[{tableName}] set [{param.ColumnName}] =@value where ");
switch (param.Condition)
{
case "null":
filter.Append($"[{param.ColumnName}] is null and ");
break;
case "blank":
filter.Append($"[{param.ColumnName}] ='' and ");
break;
case "blankOrNull":
filter.Append($"[{param.ColumnName}] is null or [{param.ColumnName}] ='' and ");
break;
}
filter.Append(" 1=1");
var count = _dbResearch.Ado.ExecuteCommand(filter.ToString(), new List <SugarParameter>()
{
new SugarParameter("@value", param.FieldValue)
});
if (count > 0)
{
var dt = GetOriginalDataFromMySql(tableName);
legacyCodeHandler.GenerateColumnDataForSingleColumn(dt, param.ColumnName, param.RdDataColumnId);
}
}
catch (Exception ex)
{
_logger.LogError("FillRdField：" + ex.ToString());
result = false;
}
return(result);
}
public bool DataColumnFillField(string node, string fieldName, string condition, string filedValue)
{
var result = true;
try
{
var cacheData = GetDataFlowCache(node);
var dt = cacheData.DataTable;
StringBuilder filter = new StringBuilder();
switch (condition)
{
case "null":
filter.Append($"{fieldName} is null and ");
break;
case "blank":
filter.Append($"{fieldName} ='' and ");
break;
case "blankOrNull":
filter.Append($"{fieldName} is null or {fieldName} ='' and ");
break;
}
filter.Append(" 1=1");
var rows = dt.Select(filter.ToString());
if (rows != null && rows.Length > 0)
{
for (int i = 0; i < rows.Length; i++)
{
rows[i][fieldName] = filedValue;
}
var handler = new LegacyCodeHandler(_extractEngineDataHandlerlogger);
cacheData.DataColumns = handler.GenerateColumnDataForCache(cacheData.DataTable, cacheData.DataColumns);
SetDataFlowCache(cacheData, node);
}
}
catch (Exception ex)
{
_logger.LogError("DataColumnFillField失败：" + ex.ToString());
result = false;
}
return(result);
}
public int DataRowFilterByColumns(string node, List <DataRowFilter> filterColumns)
{
var result = 0;
try
{
var cacheData = GetDataFlowCache(node);
StringBuilder filter = new StringBuilder();
foreach (var r in filterColumns)
{
if (r.Operation.Equals("like"))
{
filter.Append($"{r.Column} {r.Operation} '%{r.Value}%' and ");
}
else if (r.Operation.Equals("nl"))
{
filter.Append($"{r.Column} not like '%{r.Value}%' and ");
}
else
{
filter.Append($"{r.Column} {r.Operation} '{r.Value}' and ");
}
}
filter.Append(" 1=1");
var filterRows = cacheData.DataTable.Select(filter.ToString());
if (filterRows.Length > 0)
{
result = filterRows.Length;
cacheData.DataTable = filterRows.CopyToDataTable();
cacheData.DataCount = filterRows.Length;
var handler = new LegacyCodeHandler(_extractEngineDataHandlerlogger);
cacheData.DataColumns = handler.GenerateColumnDataForCache(cacheData.DataTable, cacheData.DataColumns);
SetDataFlowCache(cacheData, node);
}
}
catch (Exception ex)
{
_logger.LogError("DataRowFilterByColumns失败：" + ex.ToString());
}
return(result);
}
public int ExtractEngineDataForService(string projectTypeStr, string projectName, string userName, DataTable dtFormat)
{
var extractDataSetMappingModel = new ExtractDataSetMappingModel();
try
{
_logger.LogInformation($"projectTypeStr:{projectTypeStr},projectName:{projectName},userName:{userName}");
string tableName = Guid.NewGuid().ToString("N");
string datasetName = projectName + "_" + DateTime.Now.ToString("yyyyMMddhhmmssfff");
_logger.LogInformation("开始在sql server创建mapping关系");
//ms sql server 创建dataset table_config的mapping关系
var createMappingSql = GetSqlText("ExtractEngineData-CreateMappingForService.sql");
extractDataSetMappingModel = _dbKyStatic.Ado.SqlQuerySingle <ExtractDataSetMappingModel>(createMappingSql, new List <SugarParameter>()
{
new SugarParameter("@projectTypeStr", projectTypeStr),
new SugarParameter("@projectName", projectName),
new SugarParameter("@userName", userName),
new SugarParameter("@tableName", tableName),
new SugarParameter("@datasetName", datasetName),
new SugarParameter("@dataCount", dtFormat.Rows.Count)
});
_logger.LogInformation("开始导入数据");
LegacyCodeHandler legacyCodeHandler = new LegacyCodeHandler(_legacyCodeHandler);
//创建数据表 将所有列插入到data_field 表
dtFormat.TableName = tableName;
legacyCodeHandler.CreateSchemaAndExportData(extractDataSetMappingModel, dtFormat);
_logger.LogInformation($"抽取结束datasetId:{extractDataSetMappingModel.DataSetId},tableId:{extractDataSetMappingModel.TableId}");
}
catch (Exception ex)
{
_logger.LogError("接口导入到sql server失败：" + ex.ToString());
DeleteDataSet(extractDataSetMappingModel.DataSetId);
extractDataSetMappingModel.DataSetId = 0;
}
return(extractDataSetMappingModel.DataSetId);
}
public void ExtractEngineData(int exportDataId, int projectId, string userName)
{
var extractDataSetMappingModel = new ExtractDataSetMappingModel();
try
{
_logger.LogInformation($"开始抽取export_id:{exportDataId},projectId:{projectId},userName:{userName}");
string tableName = Guid.NewGuid().ToString("N");
var engineData = _dbMySql.Queryable <SearchEngineDataModel>().Where(x => x.Id == exportDataId).First();
var extractDataListSql = string.Format(GetSqlText("ExtractEngineData-GetExportDataList.sql"), engineData.DataType, exportDataId);
//获取原始数据
DataTable dtSource = _dbMySql.Ado.GetDataTable(extractDataListSql);
_logger.LogInformation("开始数据表格式处理");
LegacyCodeHandler legacyCodeHandler = new LegacyCodeHandler(_legacyCodeHandler);
//数据转成表格
DataTable dtFormat = legacyCodeHandler.ConvertToStandardTable(dtSource, engineData.DataType);
dtFormat.TableName = tableName;
_logger.LogInformation("开始在sql server创建mapping关系");
//ms sql server 创建dataset table_config的mapping关系
var createMappingSql = string.Format(GetSqlText("ExtractEngineData-CreateMapping.sql"), engineData.DiseaseId, engineData.DataName, engineData.Id, userName, tableName, projectId, engineData.DataType, dtFormat.Rows.Count);
extractDataSetMappingModel = _dbKyStatic.Ado.SqlQuerySingle <ExtractDataSetMappingModel>(createMappingSql);
_logger.LogInformation("开始导入数据");
//创建数据表 将所有列插入到data_field 表
legacyCodeHandler.CreateSchemaAndExportData(extractDataSetMappingModel, dtFormat);
_logger.LogInformation($"抽取结束datasetId:{extractDataSetMappingModel.DataSetId},tableId:{extractDataSetMappingModel.TableId}");
}
catch (Exception ex)
{
_logger.LogError("搜索引擎数据导入到sql server失败：" + ex.ToString());
DeleteDataSet(extractDataSetMappingModel.DataSetId);
}
}
public bool DataCombineAppend(string node, List <string> prevNodeIds, string fieldSource)
{
var result = true;
try
{
var mainData = GetDataFlowCache(prevNodeIds[0], "");
var secondData = GetDataFlowCache(prevNodeIds[1], "");
var table1 = mainData.DataTable;
var table2 = secondData.DataTable;
var modifyTable = table1.Copy();
if (fieldSource == "mainData")
{
modifyTable.Merge(table2, true, MissingSchemaAction.Ignore);
}
else
{
modifyTable.Merge(table2, true, MissingSchemaAction.Add);
}
var mainColumns = mainData.DataColumns.Select(x => x.Name).ToList();
var secondColumns = secondData.DataColumns.Select(x => x.Name).ToList();
//DataTable dt = new DataTable();
//if (fieldSource == "mainData")
//{
// string[] colCommon = mainColumns.Intersect(secondColumns).ToArray();
// dt = mainData.DataTable.Copy();
// DataTable dt2 = secondData.DataTable.DefaultView.ToTable(false, colCommon);
// foreach (DataRow itemR in dt2.Rows)
// {
// DataRow dr = dt.NewRow();
// foreach (System.Data.DataColumn itemC in dt2.Columns)
// {
// dr[itemC] = itemR[itemC];
// }
// dt.Rows.Add(dr);
// }
//}
//else
//{
// string[] colCommon = mainColumns.Union(secondColumns).ToArray();
// //获取两个数据源的并集
// IEnumerable<DataRow> query2 = secondData.DataTable.AsEnumerable().Union(secondData.DataTable.AsEnumerable(), DataRowComparer.Default);
// //两个数据源的并集集合
// dt = query2.CopyToDataTable();
//}
var diffColumns = secondColumns.Except(mainColumns).ToList();
var data = new DataFlowCacheModel();
var handler = new LegacyCodeHandler(_extractEngineDataHandlerlogger);
var columns = mainData.DataColumns;
if (diffColumns.Count > 0)
{
foreach (var r in diffColumns)
{
columns.Add(secondData.DataColumns.FirstOrDefault(x => x.Name == r));
}
}
data.DataTable = modifyTable;
data.DataColumns = handler.GenerateColumnDataForCache(modifyTable, columns);
data.DataCount = modifyTable.Rows.Count;
data.DataSetInfo = mainData.DataSetInfo;
data.Id = mainData.Id;
data.Name = mainData.Name;
data.ProjectInfo = mainData.ProjectInfo;
data.TableName = mainData.TableName;
data.WdName = mainData.WdName;
SetDataFlowCache(data, node);
}
catch (Exception ex)
{
_logger.LogError("ExtractSimple失败：" + ex.ToString());
result = false;
}
return(result);
}
public bool DataSampleExtractSimple(string node, DataSampleModel simObj)
{
var result = true;
try
{
var cacheData = GetDataFlowCache(node);
DataTable dtRel = new DataTable();
var dt = cacheData.DataTable;
int dataCount = dt.Rows.Count;
var rowList = new List <DataRow>();
switch (simObj.Method)
{
case "extract":
var count = Convert.ToInt32(dataCount / simObj.SimVal);
dtRel = dt.AsEnumerable().Take(count).CopyToDataTable();
break;
case "pecent":
bool valBoo = System.Text.RegularExpressions.Regex.IsMatch(simObj.SimVal.ToString(), @"^[1-9]\d*$");
if (!valBoo)
{
simObj.SimVal = simObj.SimVal * 100;
}
if (simObj.SimMax > 0)
{
double dc = 0;
if (valBoo)
{
dc = dataCount * ((double)(simObj.SimVal) / 100);
}
else
{
dc = dataCount * ((double)(simObj.SimVal));
}
if (simObj.SimMax > dc)
{
dtRel = dt.AsEnumerable().Take(simObj.SimMax).CopyToDataTable();
}
else
{
dtRel = dt.AsEnumerable().Take(Convert.ToInt32(Math.Round(simObj.SimMax / (double)(simObj.SimVal / 100), 0))).CopyToDataTable();
dtRel = dtRel.AsEnumerable().Take(Convert.ToInt32(Math.Round(dataCount * (simObj.SimVal / 100), 0))).CopyToDataTable();
}
}
else
{
dtRel = dt.AsEnumerable().Take(Convert.ToInt32(Math.Round(dataCount * (simObj.SimVal / 100), 0))).CopyToDataTable();
}
break;
default:
dtRel = dt.AsEnumerable().Take(Convert.ToInt32(Math.Round(simObj.SimVal, 0))).CopyToDataTable();
break;
}
cacheData.DataTable = dtRel;
cacheData.DataCount = dtRel.Rows.Count;
var handler = new LegacyCodeHandler(_extractEngineDataHandlerlogger);
cacheData.DataColumns = handler.GenerateColumnDataForCache(cacheData.DataTable, cacheData.DataColumns);
SetDataFlowCache(cacheData, node);
}
catch (Exception ex)
{
_logger.LogError("ExtractSimple失败：" + ex.ToString());
result = false;
}
return(result);
}
public List <DFDataColumn> DataFilterGetColumns(string node, string filterType, int filterPercent)
{
var cols = new List <DFDataColumn>();
try
{
var cacheData = GetDataFlowCache(node);
var count = cacheData.DataCount;
var dt = cacheData.DataTable;
var handler = new LegacyCodeHandler(_extractEngineDataHandlerlogger);
switch (filterType)
{
case "unique":
foreach (System.Data.DataColumn item in dt.Columns)
{
bool s = handler.ConvertTypeToSqlDbType(item.DataType, count, dt, item.ColumnName, filterPercent);
if (s)
{
cols.Add(cacheData.DataColumns.FirstOrDefault(x => x.Name == item.ColumnName));
}
}
break;
case "single":
DataView dataView = dt.DefaultView;
foreach (System.Data.DataColumn item in dt.Columns)
{
int toal = dataView.ToTable(true, $"{item.ColumnName}").Rows.Count;
if (toal == 0)
{
continue;
}
bool s = (float)toal / count >= (float)filterPercent / 100;
if (s)
{
cols.Add(cacheData.DataColumns.FirstOrDefault(x => x.Name == item.ColumnName));
}
}
//DataTable dataTableDistinct = dataView.ToTable(true, "FieldName1", "FieldName2", "...");
break;
default:
foreach (System.Data.DataColumn item in dt.Columns)
{
int toal = dt.Select($"[{item.ColumnName}] is null ").Count();
if (toal == 0)
{
continue;
}
bool s = (float)toal / count >= (float)filterPercent / 100;
if (s)
{
cols.Add(cacheData.DataColumns.FirstOrDefault(x => x.Name == item.ColumnName));
}
}
break;
}
}
catch (Exception ex)
{
_logger.LogError("DataFilter失败：" + ex.ToString());
}
return(cols);
}
