public async Task <FuncResult> DeleteData(string key)
{
FuncResult fr = new FuncResult()
{
IsSuccess = true, Message = "操作成功!"
};
try
{
if (string.IsNullOrWhiteSpace(key))
{
fr.IsSuccess = false;
fr.Message = "未接收到参数信息!";
}
//var _key = Convert.ToDecimal(key);
ApdFctLandTown2 town2 = context.ApdFctLandTown2.FirstOrDefault(f => f.RecordId.Equals(key));
if (town2 == null)
{
fr.IsSuccess = false;
fr.Message = "异常参数，未找到数据!";
}
List <ApdFctLandTown> listtown = context.ApdFctLandTown.Where(f => f.T2Id.Equals(key)).ToList();
//删除
context.ApdFctLandTown2.Remove(town2);
context.ApdFctLandTown.RemoveRange(listtown);
using (IDbContextTransaction trans = context.Database.BeginTransaction())
{
try
{
await context.SaveChangesAsync();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
fr.IsSuccess = false;
fr.Message = $"{ex.InnerException},{ex.Message}";
throw new Exception("error", ex);
}
}
return(fr);
}
catch (Exception ex)
{
throw new Exception("error", ex);
}
}
public FuncResult Import(string year)
{
FuncResult result = new FuncResult()
{
IsSuccess = true, Message = "操作成功!"
};
try
{
var excelfile = Request.Form.Files[0];
List <Demo> datalist = new List <Demo>();
if (excelfile.Length > 0)
{
DataTable dt = new DataTable();
string strMsg;
string filename = excelfile.FileName;//文件名即为区域名
//利用IFormFile里面的OpenReadStream()方法直接读取文件流
dt = ExcelHelper.ExcelToDatatable(excelfile.OpenReadStream(), Path.GetExtension(excelfile.FileName), out strMsg);
if (!string.IsNullOrEmpty(strMsg))
{
result.IsSuccess = false;
result.Message = strMsg;
return(result);
}
if (dt.Rows.Count > 0)
{
var listorgan = context.ApdDimOrg.ToList();
//需要导入到数据库的数据
datalist = JsonConvert.DeserializeObject <List <Demo> >(JsonConvert.SerializeObject(dt));
#region 部分数据格式判断
//需要导入到数据库的数据
var dynamiclist = JsonConvert.DeserializeObject <List <dynamic> >(JsonConvert.SerializeObject(dt));
var prefilter = dynamiclist.Where(f => f.G10 != null && f.G11 != null && f.G12 != null).ToList();
if (prefilter == null || prefilter.Count() <= 0)
{
result.IsSuccess = false;
result.Message = "未选择正确的Excel文件或选择的Excel文件无可导入数据！";
return(result);
}
/*
* 1、筛选数据前，检查数据格式，只需要检测数值类型的列
* **/
int count = 1;//错误列号(对应实际列7)
string colname = "";
for (int i = 0; i < prefilter.Count(); i++)
{
try
{
var current = prefilter[i];
var g_10 = current.G10;
if (g_10 == "")
{
continue;
}
colname = "G10";
Convert.ToDecimal(g_10);
var g_11 = current.G11;
if (g_11 == "")
{
continue;
}
colname = "G11";
Convert.ToDecimal(g_11);
var g_12 = current.G12;
if (g_12 == "")
{
continue;
}
colname = "G12";
Convert.ToDecimal(g_12);
var g_13 = current.G13;
if (g_13 == "")
{
continue;
}
colname = "G13";
Convert.ToDecimal(g_13);
var g_14 = current.G14;
if (g_14 == "")
{
continue;
}
colname = "G14";
Convert.ToDecimal(g_14);
var g_15 = current.G15;
if (g_15 == "")
{
continue;
}
colname = "G15";
Convert.ToDecimal(g_15);
count++;
}
catch (Exception ex)
{
LogService.WriteError(ex);
result.IsSuccess = false;
result.Message = $"第{count + 6}行，{colname}列数据异常！";
return(result);
}
}
#endregion
List <Demo> filterdata = datalist.Where(f => f.G10 != null && f.G11 != null && f.G12 != null).ToList();
if (filterdata == null || filterdata.Count <= 0)
{
result.IsSuccess = false;
result.Message = "未选择正确的Excel文件或选择的Excel文件无可导入数据！";
return(result);
}
//处理筛选过后的数据
string g1 = string.Empty;
string g3 = string.Empty;
foreach (var item in filterdata)
{
if (!string.IsNullOrEmpty(item.G1))
{
g1 = item.G1;
}
if (string.IsNullOrEmpty(item.G1))
{
//为上一个有值的g1
item.G1 = g1;
}
if (!string.IsNullOrEmpty(item.G3))
{
g3 = item.G3;
}
if (string.IsNullOrEmpty(item.G3))
{
//为上一个有值的g3
item.G3 = g3;
}
}
var groupdata_1 = filterdata.GroupBy(g => new { g.G1, g.G3, g.G10, g.G11, g.G12 }).Select(s => new ReflctModel1
{
OWNERSHIPLAND = Convert.ToDecimal(s.Key.G10),
PROTECTIONLAN = Convert.ToDecimal(s.Key.G11),
REDUCELAND = Convert.ToDecimal(s.Key.G12),
ORGANIZATION = s.Key.G1,
ORGCODE = s.Key.G3
});
var groupdata_2 = filterdata.GroupBy(g => new { g.G1, g.G3, g.G13, g.G14, g.G15, g.G16 }).Select(s => new ReflctModel2
{
FACTLAND = Convert.ToDecimal(s.Key.G13),
RENTLAND = Convert.ToDecimal(s.Key.G14),
LEASELAND = Convert.ToDecimal(s.Key.G15),
REMARK = s.Key.G16,
ORGANIZATION = s.Key.G1,
ORGCODE = s.Key.G3
}).Where(f => (f.FACTLAND != null && f.RENTLAND != null && f.LEASELAND != null) == true);
string currenttown2key = "";//本次apd_fct_town2表的主键
//删除当前年份已有数据
//物理删除
var formatyear = Convert.ToDecimal(year);
var alreadytown2 = context.ApdFctLandTown2.Where(f => f.PeriodYear.Equals(formatyear));
var alreadytown = context.ApdFctLandTown.Where(f => alreadytown2.Select(g => g.RecordId).Contains(f.T2Id));
context.ApdFctLandTown2.RemoveRange(alreadytown2);
context.ApdFctLandTown.RemoveRange(alreadytown);
//t2作为t1的主表
foreach (var item in groupdata_2)
{
var currentorganization = listorgan.FirstOrDefault(f => f.OrgCode.Equals(item.ORGCODE));
if (currentorganization == null)
{
result.IsSuccess = false;
result.Message = $"此机构号:{item.ORGCODE}找不到对应机构，导入失败！";
return(result);
}
//bool isalreadyexport = isAlreadyExport(item.ORGCODE, year);
//if (isalreadyexport)
//{
// //删除(添加删除标记字段)
// //物理删除
// var formatyear = Convert.ToDecimal(year);
// var alreadytown2 = context.ApdFctLandTown2.Where(f => f.OrgCode.Equals(item.ORGCODE) && f.PeriodYear.Equals(formatyear));
// var alreadytown = context.ApdFctLandTown.Where(f => alreadytown2.Select(g => g.RecordId).Contains(f.T2Id));
// context.ApdFctLandTown2.RemoveRange(alreadytown2);
// context.ApdFctLandTown.RemoveRange(alreadytown);
//}
ApdFctLandTown2 t2 = new ApdFctLandTown2()
{
OrgCode = item.ORGCODE,
FactLand = item.FACTLAND,
RentLand = item.RENTLAND,
LeaseLand = item.LEASELAND,
Remark = item.REMARK,
PeriodYear = Convert.ToDecimal(year),
RecordId = Guid.NewGuid().ToString(),
CreatedBy = Convert.ToDecimal(HttpContext.CurrentUser(cache).Id),
CreationDate = DateTime.Now,
LastUpdateDate = DateTime.Now,
LastUpdatedBy = Convert.ToDecimal(HttpContext.CurrentUser(cache).Id),
Count = groupdata_1.Where(f => f.ORGCODE.Equals(item.ORGCODE)).Count()
};
context.ApdFctLandTown2.Add(t2);
foreach (var itemdetail in groupdata_1.Where(f => f.ORGCODE.Equals(item.ORGCODE)))
{
ApdFctLandTown t1 = new ApdFctLandTown()
{
OrgCode = itemdetail.ORGCODE,
OwnershipLand = itemdetail.OWNERSHIPLAND,
ProtectionLand = itemdetail.PROTECTIONLAN,
ReduceLand = itemdetail.REDUCELAND,
CreatedBy = Convert.ToDecimal(HttpContext.CurrentUser(cache).Id),
CreationDate = DateTime.Now,
LastUpdateDate = DateTime.Now,
LastUpdatedBy = Convert.ToDecimal(HttpContext.CurrentUser(cache).Id),
PeriodYear = DateTime.Now.Year,
RecordId = Guid.NewGuid().ToString(),
T2Id = t2.RecordId
};
context.ApdFctLandTown.Add(t1);
}
}
using (IDbContextTransaction trans = context.Database.BeginTransaction())
{
try
{
context.SaveChangesAsync();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw new Exception("error", ex);
}
}
//将groupdata_1、groupdata_2写入到两张表
}
else
{
result.IsSuccess = false;
result.Message = "excel无数据！";
}
}
return(result);
}
catch (Exception ex)
{
LogService.WriteError(ex);
result.IsSuccess = false;
result.Message = $"{ex.Message},{ex.InnerException}";
return(result);
}
}