/// <summary>
/// 更新配置主表
/// </summary>
/// <param name="keyValue"></param>
/// <param name="entity"></param>
public void UpdateEntity(string keyValue, ExcelImportEntity entity)
{
try
{
excelImportService.UpdateEntity(keyValue, entity);
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowBusinessException(ex);
}
}
}
/// <summary>
/// 保存表单（新增、修改）
/// </summary>
/// <param name="keyValue">主键值</param>
/// <param name="entity">实体数据</param>
/// <param name="filedList">字段列表</param>
/// <returns></returns>
public void SaveEntity(string keyValue, ExcelImportEntity entity, List <ExcelImportFieldEntity> filedList)
{
try
{
excelImportService.SaveEntity(keyValue, entity, filedList);
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowBusinessException(ex);
}
}
}
/// <summary>
/// 更新配置主表
/// </summary>
/// <param name="keyValue"></param>
/// <param name="entity"></param>
public void UpdateEntity(string keyValue, ExcelImportEntity entity)
{
try
{
if (!string.IsNullOrEmpty(keyValue))
{
entity.Modify(keyValue);
this.BaseRepository().Update(entity);
}
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowServiceException(ex);
}
}
}
/// <summary>
/// 保存表单（新增、修改）
/// </summary>
/// <param name="keyValue">主键值</param>
/// <param name="entity">实体数据</param>
/// <param name="filedList">字段列表</param>
/// <returns></returns>
public void SaveEntity(string keyValue, ExcelImportEntity entity, List <ExcelImportFieldEntity> filedList)
{
var db = this.BaseRepository().BeginTrans();
try
{
if (!string.IsNullOrEmpty(keyValue))
{
entity.Modify(keyValue);
db.Update(entity);
}
else
{
entity.Create();
db.Insert(entity);
}
string importId = entity.F_Id;
db.Delete <ExcelImportFieldEntity>(t => t.F_ImportId == importId);
foreach (var item in filedList)
{
item.F_ImportId = importId;
item.Create();
db.Insert(item);
}
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowServiceException(ex);
}
}
}
/// <summary>
/// excel 数据导入（未导入数据写入缓存）
/// </summary>
/// <param name="templateId">导入模板主键</param>
/// <param name="fileId">文件ID</param>
/// <param name="dt">导入数据</param>
/// <returns></returns>
public string ImportTable(string templateId, string fileId, DataTable dt)
{
int snum = 0;
int fnum = 0;
try
{
if (dt.Rows.Count > 0)
{
ExcelImportEntity entity = GetEntity(templateId);
List <ExcelImportFieldEntity> list = (List <ExcelImportFieldEntity>)GetFieldList(templateId);
if (entity != null && list.Count > 0)
{
UserInfo userInfo = LoginUserInfo.Get();
// 获取当前表的所有字段
IEnumerable <DatabaseTableFieldModel> fieldList = databaseTableIBLL.GetTableFiledList(entity.F_DbId, entity.F_DbTable);
Dictionary <string, string> fieldMap = new Dictionary <string, string>();
foreach (var field in fieldList)// 遍历字段设置每个字段的数据类型
{
fieldMap.Add(field.f_column, field.f_datatype);
}
// 拼接导入sql语句
string sql = " INSERT INTO " + entity.F_DbTable + " (";
string sqlValue = "(";
bool isfirt = true;
foreach (var field in list)
{
if (!isfirt)
{
sql += ",";
sqlValue += ",";
}
sql += field.F_Name;
sqlValue += "@" + field.F_Name;
isfirt = false;
}
sql += " ) VALUES " + sqlValue + ")";
string sqlonly = " select * from " + entity.F_DbTable + " where ";
// 创建一个datatable容器用于保存导入失败的数据
DataTable failDt = new DataTable();
dt.Columns.Add("导入错误", typeof(string));
foreach (DataColumn dc in dt.Columns)
{
failDt.Columns.Add(dc.ColumnName, dc.DataType);
}
// 数据字典数据
Dictionary <string, List <DataItemDetailEntity> > dataItemMap = new Dictionary <string, List <DataItemDetailEntity> >();
// 循环遍历导入
foreach (DataRow dr in dt.Rows)
{
string identity = string.Empty;
string orderID = string.Empty;
string realName = string.Empty;
string categoryID = string.Empty;
string gender = string.Empty;
string employerID = string.Empty;
string mobile = string.Empty;
try
{
var dp = new DynamicParameters(new { });
foreach (var col in list)
{
string paramName = "@" + col.F_Name;
DbType dbType = FieldTypeHepler.ToDbType(fieldMap[col.F_Name]);
switch (col.F_RelationType)
{
case 0: //无关联
dp.Add(col.F_Name, dr[col.F_ColName].ToString(), dbType);
if (col.F_ColName == "身份证号")
{
identity = dr[col.F_ColName].ToString();
}
if (col.F_ColName == "姓名")
{
realName = dr[col.F_ColName].ToString();
}
if (col.F_ColName == "联系方式")
{
mobile = dr[col.F_ColName].ToString();
}
IsOnlyOne(col, sqlonly, dr[col.F_ColName].ToString(), entity.F_DbId, dbType);
break;
case 1: //GUID
dp.Add(col.F_Name, Guid.NewGuid().ToString(), dbType);
break;
case 2: //数据字典
string dataItemName = "";
if (!dataItemMap.ContainsKey(col.F_DataItemCode))
{
List <DataItemDetailEntity> dataItemList = dataItemIBLL.GetDetailList(col.F_DataItemCode);
dataItemMap.Add(col.F_DataItemCode, dataItemList);
}
dataItemName = FindDataItemValue(dataItemMap[col.F_DataItemCode], dr[col.F_ColName].ToString(), col.F_ColName);
dp.Add(col.F_Name, dataItemName, dbType);
IsOnlyOne(col, sqlonly, dataItemName, entity.F_DbId, dbType);
if (col.F_ColName == "性别")
{
gender = dr[col.F_ColName].ToString();
}
break;
case 3: //数据表
string v = "";
try
{
string[] dataSources = col.F_DataSourceId.Split(',');
string strWhere = " " + dataSources[1] + " =@" + dataSources[1];
string queryJson = "{" + dataSources[1] + ":\"" + dr[col.F_ColName].ToString() + "\"}";
DataTable sourceDt = dataSourceIBLL.GetDataTable(dataSources[0], strWhere, queryJson);
v = sourceDt.Rows[0][0].ToString();
dp.Add(col.F_Name, v, dbType);
if (col.F_ColName == "活动")
{
orderID = dr[col.F_ColName].ToString();
}
if (col.F_ColName == "工种")
{
categoryID = dr[col.F_ColName].ToString();
}
if (col.F_ColName == "单位")
{
employerID = dr[col.F_ColName].ToString();
}
}
catch (Exception)
{
throw (new Exception("【" + col.F_ColName + "】 找不到对应的数据"));
}
IsOnlyOne(col, sqlonly, v, entity.F_DbId, dbType);
break;
case 4: //固定值
dp.Add(col.F_Name, col.F_Value, dbType);
break;
case 5: //操作人ID
dp.Add(col.F_Name, userInfo.userId, dbType);
break;
case 6: //操作人名字
dp.Add(col.F_Name, userInfo.realName, dbType);
break;
case 7: //操作时间
dp.Add(col.F_Name, DateTime.Now, dbType);
break;
}
}
if (entity.F_DbTable.ToLower() == "lr_base_tempuser")
{
databaseLinkIBLL.ExecuteBySql(entity.F_DbId, sql, dp, identity, orderID, realName, categoryID, gender, employerID, mobile);
}
else
{
databaseLinkIBLL.ExecuteBySql(entity.F_DbId, sql, dp);
}
snum++;
}
catch (Exception ex)
{
if (entity.F_ErrorType == 0)// 如果错误机制是终止
{
fnum++;
dr["导入错误"] = ex.Message + "【之后数据未被导入】";
failDt.Rows.Add(dr.ItemArray);
break;
}
else
{
if (entity.F_DbTable.ToLower() == "lr_base_tempuser")
{
int num = ex.Message.IndexOf("身份证号");
if (num >= 0)
{
//判断该用户是否在黑名单中
num = databaseLinkIBLL.CheckBlack(identity);
if (num > 0)
{
fnum++;
dr["导入错误"] = "【该用户】已在黑名单";
failDt.Rows.Add(dr.ItemArray);
}
else
{
databaseLinkIBLL.InsertOrderDetail(identity, orderID, realName, categoryID, gender, employerID, mobile);
}
}
else
{
fnum++;
dr["导入错误"] = ex.Message;
failDt.Rows.Add(dr.ItemArray);
}
}
else
{
fnum++;
dr["导入错误"] = ex.Message;
failDt.Rows.Add(dr.ItemArray);
}
}
}
}
// 写入缓存如果有未导入的数据
if (failDt.Rows.Count > 0)
{
string errordt = failDt.ToJson();
cache.Write <string>(cacheKey + fileId, errordt, CacheId.excel);
}
}
}
return(snum + "|" + fnum);
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowBusinessException(ex);
}
}
}