/// <summary>
/// 执行加载
/// </summary>
/// <param name="pDataRow">ETCL数据项</param>
/// <param name="pUserInfo">用户信息</param>
/// <param name="oResult">抽取结果详情</param>
/// <param name="pTran">数据库事务</param>
/// <returns>TRUE：抽取成功，FALSE：抽取失败。</returns>
public bool Process(IETCLDataItem[] pDataRow, BasicUserInfo pUserInfo, out IETCLResultItem[] oResult, IDbTransaction pTran)
{
bool isPass = true;
List <ETCLCommonResultItem> lstDuplicateCell = new List <ETCLCommonResultItem>();
CommonExcelDataItem[] excelDataItems = (CommonExcelDataItem[])pDataRow;
foreach (var entityItem in excelDataItems)
{
try
{
object oQueryResult = Reflection.DynamicReflectionHandler.CreateEntity(entityItem.Table, new object[] { pUserInfo }, entityItem.Entity, pTran);
}
catch (Exception ex)
{
Loggers.Exception(new ExceptionLogInfo(ex));
isPass = false;
ETCLCommonResultItem result = new ETCLCommonResultItem();
result.OPType = OperationType.CreateEntity;
result.TableIndex = entityItem.Table.Index.Value;
result.RowIndex = entityItem.Index.Value;
result.Message = string.Format("[实体创建失败(添加到数据库表).].");
lstDuplicateCell.Add(result);
}
}
oResult = lstDuplicateCell.ToArray();
return(isPass);
}
///// <summary>
///// 检查当前Worksheet中的所有数据
///// </summary>
///// <param name="pDataSource">ETCL工作簿信息</param>
///// <param name="oResult">检查结果详情</param>
///// <returns>TRUE：检查通过，FALSE：检查不通过。</returns>
//private bool CommonCheck(object pDataSource, out IETCLResultItem[] oResult)
//{
// bool isPass = true;
// List<ETCLCommonResultItem> lstCheckResult = new List<ETCLCommonResultItem>();
// ETCLWorkSheetInfo worksheetItem = (ETCLWorkSheetInfo)pDataSource;
// foreach (var tableItem in worksheetItem.Tables)
// {//每个库表进行处理
// if (tableItem.Entities != null)
// {
// if (!Check(tableItem.Entities.ToArray(), out oResult))
// return false;
// }
// }
// oResult = lstCheckResult.ToArray();
// return isPass;
//}
/// <summary>
/// 检查指定的ETCL数据项
/// </summary>
/// <param name="pDataItems">ETCL数据项</param>
/// <param name="oResult">检查结果详情</param>
/// <returns>TRUE：检查通过，FALSE：检查不通过。</returns>
protected override bool Check(IETCLDataItem[] pDataItems, out IETCLResultItem[] oResults)
{
if (pDataItems == null || pDataItems.Length == 0)
{
oResults = null;
return(true);
}
//3.实体的每个属性
//CommonExcelDataItem[] dataItems = pDataItems.Cast<CommonExcelDataItem>().ToArray();
bool isPass = true;
List <ETCLCommonResultItem> lstCheckResult = new List <ETCLCommonResultItem>();
List <string> tableNames = new List <string>();
foreach (var item in pDataItems)
{
CommonExcelDataItem dataItem = (CommonExcelDataItem)item;
if (tableNames.Contains(dataItem.Table.TableName))
{
continue;
}
tableNames.Add(dataItem.Table.TableName);
foreach (var properyItem in dataItem.Table.Columns)
{// 列 （并不是所有的列都要校验）
if (properyItem.Checkers == null || properyItem.Checkers.Count == 0 || properyItem.IsLogicalField.Value)
{
continue;
}
foreach (var checker in properyItem.Checkers)
{
foreach (var rowItem in pDataItems)
{ //4.每一行记录
CommonExcelDataItem entityItem = (CommonExcelDataItem)rowItem;
object oCellValue = properyItem.ValueCache[entityItem.Index.Value]; // Reflection.DynamicReflectionHandler.GetProperty(entityItem.Entity, properyItem.ColumnName);
ETCLCommonResultItem tempResult = new ETCLCommonResultItem();
tempResult.WorksheetIndex = entityItem.Table.Worksheet.Index.Value;
tempResult.TableIndex = entityItem.Table.Index.Value;
tempResult.RowIndex = entityItem.Index.Value;
tempResult.ColumnOrder = entityItem.Table.Columns.IndexOf(properyItem);
IETCLResultItem tempResult1 = new ETCLCommonResultItem();
if (!checker.Process(oCellValue, properyItem.ColumnText, entityItem, ref tempResult1))
{//无效
isPass = false;
tempResult.Message = tempResult1.Message;
tempResult.OPType = tempResult1.OPType;
tempResult.ResultCode = tempResult1.ResultCode;
lstCheckResult.Add(tempResult);
}
}
}
}
}
oResults = lstCheckResult.ToArray();
return(isPass);
}
/// <summary>
/// 执行检查
/// </summary>
/// <param name="pPropertyValue">待检查的数据值</param>
/// <param name="pPropertyText">此数据的属性名称（列描述）</param>
/// <param name="pRowData">当前数据项的信息</param>
/// <param name="oResult">检查结果</param>
/// <returns>TRUE：通过检查，FALSE：未通过检查。</returns>
public bool Process(object pPropertyValue, string pPropertyText, IETCLDataItem pRowData, ref IETCLResultItem oResult)
{
bool isPass = true;
if (pPropertyValue == null || string.IsNullOrWhiteSpace(pPropertyValue.ToString()))//空值不检查依赖项
{
return(true);
}
//检查当前XLS中是否有此依赖表
ETCLTable referenceTable = this._etclWorksheet.GetTableByName(this._etclColumn.ReferenceTableName);
if (referenceTable == null)
{
throw new ETCLException(200, string.Format("未找到关联表的相关定义，请修改XML配置。列名:{0},关联表:{1}", this._etclColumn.ColumnName, this._etclColumn.ReferenceTableName));
}
//通过反射从数据库中读取此依赖表信息
if (!_dictCachedReference.ContainsKey(this._etclColumn.ReferenceTableName))
{
object[] oTempEntities = Reflection.DynamicReflectionHandler.QueryAll(referenceTable, new object[] { this._userInfo });
_dictCachedReference.Add(this._etclColumn.ReferenceTableName, oTempEntities);
}
object[] oEntities = _dictCachedReference[this._etclColumn.ReferenceTableName];
foreach (var entityItem in oEntities)
{
object oTempValue = Reflection.DynamicReflectionHandler.GetProperty(entityItem, this._etclColumn.ReferenceTextColumnName);
if (oTempValue.Equals(pPropertyValue))
{
isPass = false;
oResult = new ETCLCommonResultItem();
oResult.OPType = OperationType.ForeignKeyDependence;
oResult.Message = string.Format("[{数据项:{0}}]的数据在关联表中不存在.", pPropertyText);
}
}
return(isPass);
}
public bool Process(DataTable pDataSource, BasicUserInfo pUserInfo, out IETCLDataItem[] oData, out IETCLResultItem[] oResult)
{
bool isPass = true;
List <ETCLCommonResultItem> lstCheckResult = new List <ETCLCommonResultItem>();
////将原始数据按表拆分到各个DataTable
List <TenantPlatformExcelDataItem> lstDataItem = new List <TenantPlatformExcelDataItem>();
if (pDataSource != null)
{
bool isAllNullOrEmpty = true;
//移除全为null或string.Empty的记录
for (int i = 0; i < pDataSource.Rows.Count; i++)
{
isAllNullOrEmpty = true;
DataRow dr = pDataSource.Rows[i];
for (int j = 0; j < pDataSource.Columns.Count; j++)
{
if (dr[j] != DBNull.Value && dr[j].ToString().Trim() != string.Empty)
{
isAllNullOrEmpty = false;
break;
}
}
if (isAllNullOrEmpty)
{
pDataSource.Rows.RemoveAt(i);
i--;
}
}
pDataSource.AcceptChanges();
foreach (var tableItem in this.worksheetInfo.Tables)
{
if (tableItem.ReferenceOnly == 1)
{
continue;
}
tableItem.Data = pDataSource.Copy();
tableItem.Data.TableName = tableItem.TableName;
Dictionary <string, string> columnTextNameMapping = new Dictionary <string, string>();
foreach (var columnItem in tableItem.Columns)
{
columnTextNameMapping.Add(columnItem.ColumnText, columnItem.ColumnName);
}
//去掉多余列
foreach (DataColumn columnItem in pDataSource.Columns)
{
if (columnTextNameMapping.ContainsKey(columnItem.ColumnName))
{//文本改成列名
tableItem.Data.Columns[columnItem.ColumnName].ColumnName = columnTextNameMapping[columnItem.ColumnName];
}
else
{
tableItem.Data.Columns.Remove(columnItem.ColumnName);
}
}
}
}
else
{
oData = null;
oResult = null;
return(true);
}
var index = 0;
//转换到Entity
foreach (DataRow item in pDataSource.Rows)
{
Dictionary <string, string> d = new Dictionary <string, string>();
try
{
for (int j = 0; j < pDataSource.Columns.Count; j++)
{
d.Add(pDataSource.Columns[j].ColumnName, item[j] == DBNull.Value ? "" : item[j].ToString().Replace("'", " "));
}
TenantPlatformExcelDataItem dItem = new TenantPlatformExcelDataItem();
dItem.OriginalRow = new Dictionary <string, string>();
for (int i = 0; i < pDataSource.Columns.Count; i++)
{
dItem.OriginalRow.Add(pDataSource.Columns[i].ColumnName, item[i].ToString().Replace("'", " "));
}
dItem.Entity = d;
dItem.TableName = "User";
dItem.Index = index;
lstDataItem.Add(dItem);
}
catch (Exception ex)
{
ETCLCommonResultItem etclCommonResultItem = new ETCLCommonResultItem();
etclCommonResultItem.ColumnOrder = index;
etclCommonResultItem.Message = ex.Message;
etclCommonResultItem.OPType = OperationType.Extract;
etclCommonResultItem.ResultCode = 500;
etclCommonResultItem.RowIndex = pDataSource.Rows.IndexOf(item);
etclCommonResultItem.ServerityLevel = ServerityLevel.Error;
etclCommonResultItem.TableIndex = 0;
lstCheckResult.Add(etclCommonResultItem);
isPass = false;
}
index++;
}
oResult = lstCheckResult.ToArray();
oData = lstDataItem.ToArray();
return(isPass);
}
/// <summary>
/// 将XLS抽取到DataTable
/// </summary>
/// <param name="pDataSource">数据源（XLS的文件路径）</param>
/// <param name="oData">抽取出的直接数据（DataTable）</param>
/// <param name="oResult">抽取结果详情</param>
/// <returns>TRUE：抽取成功，FALSE：抽取失败。</returns>
public bool Process(object pDataSource, out object oData, out IETCLResultItem[] oResult)
{
bool bReturn = true;
List <ETCLCommonResultItem> lstErrorInfo = new List <ETCLCommonResultItem>();
List <ETCLCommonResultItem> pcheckResult = new List <ETCLCommonResultItem>();
Worksheet worksheetItem = (Worksheet)pDataSource;
int maxRowCount = worksheetItem.Cells.MaxRow;
int maxColCount = worksheetItem.Cells.MaxColumn;
List <string> columnNames = new List <string>();
if (maxRowCount > 0 && maxColCount > 0)
{
//1.检查Excel工作表的第一行,如果第一行全为空或string.Empty,则表示没有数据/数据是已经被处理过的
bool bypassed = true;
for (int i = 0; i < maxColCount + 1; i++)
{
if (worksheetItem.Cells[0, i].Value != null && (!string.IsNullOrEmpty(worksheetItem.Cells[0, i].StringValue)))
{
bypassed = false;
columnNames.Add(worksheetItem.Cells[0, i].StringValue.Trim().ToUpper());
}
}
if (bypassed)
{
oData = null;
oResult = null;
return(true);
}
//检查列名是否重复
for (int i = 0; i < columnNames.Count; i++)
{
for (int j = i + 1; j < columnNames.Count; j++)
{
if (columnNames[i] == columnNames[j])
{
bReturn = false;
ETCLCommonResultItem resultItem = new ETCLCommonResultItem();
resultItem.ColumnOrder = -1;
resultItem.Message = string.Format("工作表[{0}]中第{1}列的列名[{3}]与第{2}列的列名重复.", worksheetItem.Name, i + 1, j + 1, columnNames[i]);
resultItem.OPType = OperationType.Extract;
resultItem.ResultCode = 105;
resultItem.RowIndex = -1;
resultItem.TableIndex = -1;
resultItem.WorksheetIndex = worksheetItem.Index;
lstErrorInfo.Add(resultItem);
}
}
}
if (lstErrorInfo.Count == 0)
{
//记录原始数据
DataTable dt = worksheetItem.Cells.ExportDataTableAsString(0, 0, maxRowCount + 1, maxColCount + 1, true);
oData = dt;
}
else
{
oData = null;
}
}
else
{
oData = null;
}
oResult = lstErrorInfo.ToArray();
return(bReturn);
}
/// <summary>
/// 执行转换，并进行文本-键转换
/// </summary>
/// <param name="pDataSource">从数据源取出的直接数据</param>
/// <param name="pUserInfo">用户信息</param>
/// <param name="oData">转换后的数据</param>
/// <param name="oResult">抽取结果详情</param>
/// <returns>TRUE：抽取成功，FALSE：抽取失败。</returns>
public bool Process(DataTable pDataSource, BasicUserInfo pUserInfo, out IETCLDataItem[] oData, out IETCLResultItem[] oCheckResult)
{
bool isPass = true;
List <ETCLCommonResultItem> lstCheckResult = new List <ETCLCommonResultItem>();
////将原始数据按表拆分到各个DataTable
List <CommonExcelDataItem> lstDataItem = new List <CommonExcelDataItem>();
if (pDataSource != null)
{
bool isAllNullOrEmpty = true;
//移除全为null或string.Empty的记录
for (int i = 0; i < pDataSource.Rows.Count; i++)
{
isAllNullOrEmpty = true;
DataRow dr = pDataSource.Rows[i];
for (int j = 0; j < pDataSource.Columns.Count; j++)
{
if (dr[j] != DBNull.Value && dr[j].ToString().Trim() != string.Empty)
{
isAllNullOrEmpty = false;
break;
}
}
if (isAllNullOrEmpty)
{
pDataSource.Rows.RemoveAt(i);
i--;
}
}
pDataSource.AcceptChanges();
foreach (var tableItem in this._workSheetInfo.Tables)
{
if (tableItem.ReferenceOnly == 1)
{
continue;
}
tableItem.Data = pDataSource.Copy();
tableItem.Data.TableName = tableItem.TableName;
Dictionary <string, string> columnTextNameMapping = new Dictionary <string, string>();
foreach (var columnItem in tableItem.Columns)
{
columnTextNameMapping.Add(columnItem.ColumnText, columnItem.ColumnName);
}
//去掉多余列
foreach (DataColumn columnItem in pDataSource.Columns)
{
if (columnTextNameMapping.ContainsKey(columnItem.ColumnName))
{//文本改成列名
tableItem.Data.Columns[columnItem.ColumnName].ColumnName = columnTextNameMapping[columnItem.ColumnName];
}
else
{
tableItem.Data.Columns.Remove(columnItem.ColumnName);
}
}
}
}
else
{
oData = null;
oCheckResult = null;
return(true);
}
string assemblyPath;
if (System.Environment.CurrentDirectory == AppDomain.CurrentDomain.BaseDirectory)
{//Winform
assemblyPath = AppDomain.CurrentDomain.BaseDirectory;
}
else
{//Web
assemblyPath = AppDomain.CurrentDomain.BaseDirectory + "Bin/";
}
//转换到Entity
Dictionary <string, object[]> dictDBTableCache = new Dictionary <string, object[]>();
foreach (var tableItem in this._workSheetInfo.Tables)
{
if (tableItem.Data == null || tableItem.ReferenceOnly == 1)
{
continue;
}
List <CommonExcelDataItem> lstEntity = new List <CommonExcelDataItem>();
Dictionary <string, Dictionary <object, object> > dictCachedReference = new Dictionary <string, Dictionary <object, object> >();
foreach (DataRow tableRowItem in tableItem.Data.Rows)
{
CommonExcelDataItem oInstance = new CommonExcelDataItem();
lstDataItem.Add(oInstance);
oInstance.Table = tableItem;
oInstance.Index = lstEntity.Count;
if (string.IsNullOrEmpty(tableItem.EntityName))
{//使用默认规则创建Entity（TableName+“Entity”）
oInstance.Entity = Reflection.DynamicReflectionHandler.CreateInstance(assemblyPath + "TenantPlatform.Entity.dll", "JIT.TenantPlatform.Entity." + tableItem.TableName + "Entity", null);
}
else
{
oInstance.Entity = Reflection.DynamicReflectionHandler.CreateInstance(assemblyPath + tableItem.EntityAssemblyName, tableItem.EntityName, null);
}
//依次填充属性
foreach (ETCLColumn columnItem in tableItem.Columns)
{//值->键转换处理。
if (!tableItem.Data.Columns.Contains(columnItem.ColumnName))
{
continue;
}
object oCellValue = tableRowItem[columnItem.ColumnName];
columnItem.ValueCache.Add(oCellValue);
object oReferencedID = null;
if (!string.IsNullOrWhiteSpace(columnItem.ReferenceTableName) && !string.IsNullOrWhiteSpace(columnItem.ReferenceColumnName) && !string.IsNullOrWhiteSpace(columnItem.ReferenceTextColumnName))
{
//每个单元格;
if (oCellValue == null || string.IsNullOrWhiteSpace(oCellValue.ToString()))//空值不检查依赖项
{
continue;
}
bool bExists = false;
Dictionary <object, object> dictValuesOfCurrentColumn;
//a.缓存中查找
if (dictCachedReference.ContainsKey(columnItem.ColumnName))
{//缓存中查找
dictValuesOfCurrentColumn = dictCachedReference[columnItem.ColumnName];
if (dictValuesOfCurrentColumn.ContainsKey(oCellValue))
{
bExists = true;
oReferencedID = dictValuesOfCurrentColumn[oCellValue];
}
}
else
{
dictValuesOfCurrentColumn = new Dictionary <object, object>();
dictCachedReference.Add(columnItem.ColumnName, dictValuesOfCurrentColumn);
}
//b.数据库中查找
if (!bExists)
{//通过反射从数据库中读取此依赖表信息
ETCLTable referenceTable = this._workSheetInfo.GetTableByName(columnItem.ReferenceTableName);
object[] oTempEntities;
if (dictDBTableCache.ContainsKey(columnItem.ReferenceTableName))
{
oTempEntities = dictDBTableCache[columnItem.ReferenceTableName];
}
else
{
oTempEntities = Reflection.DynamicReflectionHandler.QueryAll(referenceTable, new object[] { pUserInfo });
dictDBTableCache.Add(columnItem.ReferenceTableName, oTempEntities);
}
foreach (var entityItem in oTempEntities)
{
bool bMached = true;
if (!string.IsNullOrWhiteSpace(columnItem.ReferenceAdditionalCondition))
{//附加条件(如，从Options中查数据时，需要OptionName和OptionValue多个字段)
string[] conditions = columnItem.ReferenceAdditionalCondition.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var conditionItem in conditions)
{
string[] conditionNameValue = conditionItem.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
if (conditionNameValue == null || conditionNameValue.Length != 2)
{
continue;
}
object oValue = Reflection.DynamicReflectionHandler.GetProperty(entityItem, conditionNameValue[0]);
if (oValue == null || oValue.ToString() != conditionNameValue[1])
{
bMached = false;
break;
}
}
if (!bMached)
{
continue;
}
}
object oTempValue = Reflection.DynamicReflectionHandler.GetProperty(entityItem, columnItem.ReferenceTextColumnName);
if (oTempValue != null && oTempValue.Equals(oCellValue))
{
oReferencedID = Reflection.DynamicReflectionHandler.GetProperty(entityItem, columnItem.ReferenceColumnName);
dictValuesOfCurrentColumn.Add(oCellValue, oReferencedID);
bExists = true;
break;
}
}
}
if (bExists)//有效的依赖项
{
if (!Reflection.DynamicReflectionHandler.SetProperty(oInstance.Entity, columnItem.ColumnName, oReferencedID))
{
throw new ETCLException(400, string.Format("反射方法设置依赖项属性失败,表:{0} 列:{1} 关联:{2}", tableItem.TableName, columnItem.ColumnName, oReferencedID));
}
}
else
{
isPass = false;
ETCLCommonResultItem resultItem = new ETCLCommonResultItem();
resultItem.ColumnOrder = tableItem.Columns.IndexOf(columnItem);
resultItem.Message = string.Format("[数据项:【{0}】]的数据在关联表中不存在.", columnItem.ColumnText);
resultItem.OPType = OperationType.ForeignKeyDependence;
resultItem.ResultCode = 102;
resultItem.RowIndex = tableItem.Data.Rows.IndexOf(tableRowItem);
resultItem.TableIndex = this._workSheetInfo.Tables.IndexOf(tableItem);
resultItem.WorksheetIndex = this._workSheetInfo.Index.Value;// etclInfo.WorkSheets.IndexOf(worksheetItem);
lstCheckResult.Add(resultItem);
}
}
else
{
if (oCellValue != null)
{
if (columnItem.ValueCountCache.ContainsKey(oCellValue))
{
columnItem.ValueCountCache[oCellValue]++;
}
else
{
columnItem.ValueCountCache.Add(oCellValue, 1);
}
}
if (!Reflection.DynamicReflectionHandler.SetProperty(oInstance.Entity, columnItem.ColumnName, oCellValue))
{
throw new ETCLException(400, string.Format("反射方法设置属性失败,表:{0} 列:{1} 值:{2}", tableItem.TableName, columnItem.ColumnName, oCellValue));
}
}
}
lstEntity.Add(oInstance);
}
tableItem.Entities = lstEntity;
}
oCheckResult = lstCheckResult.ToArray();
oData = lstDataItem.ToArray();
return(isPass);
}