private static DateTime?GetDateImportDb(this SheetData sheetData, WorkbookPart workBookPart, string sheetName)
{
DateTime?datetime = sheetData.GetValueCell(workBookPart, 5, 2).Trim().Split(" ").AsEnumerable().Last().ConvertStringToDate();
datetime = datetime.HasValue ? datetime : null;
if (datetime == null)
{
DateTime?date = sheetName.ConvertStringToDate("dd-MM-yyyy");
datetime = !date.HasValue ? datetime : date;
}
return(datetime);
}
private static bool WriteDatabase(this SheetData sheetData, string sheetName, WorkbookPart workBookPart, Models.DbEntities db, bool isBC_QuanTrac = true)
{
DateTime?datetime = sheetData.GetDateImportDb(workBookPart, sheetName);
if (datetime == null)
{
return(false);
}
bool response = false;
db.Database.Log = Console.Write;
bool isUpdate = isBC_QuanTrac
? db.NV_DulieuQuantrac.Select(x => x.NgayQuantrac).Distinct()
.Any(row => DateTime.Compare(datetime.Value, row) == 0)
: db.NV_Dulieudubao.Select(x => x.Ngaydubao).Distinct()
.Any(row => DateTime.Compare(datetime.Value, row) == 0);
var lstTT = db.NV_MaubaocaoThuoctinh.Where(x => x.MauBC_ID.Equals("BC_CLN")).OrderBy(x => x.STT).Select(row => row.Thuoctinh_ID);
var lstDD = db.NV_MaubaocaoDiadanh.Where(x => x.MauBC_ID.Equals("BC_CLN")).OrderBy(x => x.STT).Select(row => row.Diadanh_ID);
int?rowIndex = null;
int?colIndex = null;
try
{
int?soLieuIndex = null;
if (isBC_QuanTrac)
{
var lastEntity = db.NV_DulieuQuantrac.AsEnumerable()
.Select(x => new { id = Convert.ToInt32(x.SolieuQuantrac_ID) })
.OrderByDescending(x => x.id).FirstOrDefault();
soLieuIndex = lastEntity == null ? 0 : lastEntity.id;
}
else
{
var lastEntity = db.NV_Dulieudubao
.Select(x => new { id = Convert.ToInt32(x.SolieuDB_ID) })
.OrderByDescending(x => x.id).FirstOrDefault();
soLieuIndex = lastEntity == null ? 0 : lastEntity.id;
}
lstDD.AsEnumerable().ForEach(ref rowIndex, row =>
{
colIndex = null;
lstTT.AsEnumerable().ForEach(ref colIndex, col =>
{
soLieuIndex = soLieuIndex ?? 0;
double value;
string val = sheetData.GetValueCell(workBookPart, rowIndex.Value + 6, colIndex.Value + 3);
if (string.IsNullOrEmpty(val) || !double.TryParse(val, out value))
{
value = 0;
}
if (isBC_QuanTrac)
{
if (isUpdate)
{
var isExits = db.NV_DulieuQuantrac.Any(res =>
res.BaocaoThuoctinh_ID == col &&
res.BaocaoDiadanh_ID == row &&
DateTime.Compare(datetime.Value, res.NgayQuantrac) == 0);
if (isExits)
{
#region update du lieu quan trac
var temp = db.NV_DulieuQuantrac.FirstOrDefault(res =>
res.BaocaoThuoctinh_ID == col &&
res.BaocaoDiadanh_ID == row &&
DateTime.Compare(datetime.Value, res.NgayQuantrac) == 0);
temp.Giatri = value;
#endregion
}
else
{
#region add du lieu quan trac
soLieuIndex++;
Models.NV_DulieuQuantrac entity = new Models.NV_DulieuQuantrac()
{
SolieuQuantrac_ID = soLieuIndex.ToString(),
NgayQuantrac = datetime.Value,
BaocaoDiadanh_ID = row,
BaocaoThuoctinh_ID = col,
Giatri = value
};
db.NV_DulieuQuantrac.Add(entity);
#endregion
}
}
else
{
#region add du lieu quan trac
soLieuIndex++;
Models.NV_DulieuQuantrac entity = new Models.NV_DulieuQuantrac()
{
SolieuQuantrac_ID = soLieuIndex.ToString(),
NgayQuantrac = datetime.Value,
BaocaoDiadanh_ID = row,
BaocaoThuoctinh_ID = col,
Giatri = value
};
db.NV_DulieuQuantrac.Add(entity);
#endregion
}
}
else
{
if (isUpdate)
{
var isExits = db.NV_Dulieudubao.Any(res =>
res.BaocaoThuoctinh_ID == col &&
res.BaocaoDiadanh_ID == row &&
DateTime.Compare(datetime.Value, res.Ngaydubao) == 0);
if (isExits)
{
#region update du lieu du bao
var temp = db.NV_Dulieudubao.FirstOrDefault(res =>
res.BaocaoThuoctinh_ID == col &&
res.BaocaoDiadanh_ID == row &&
DateTime.Compare(datetime.Value, res.Ngaydubao) == 0
);
temp.Giatri = value;
#endregion
}
else
{
#region add du lieu du bao
soLieuIndex++;
Models.NV_Dulieudubao entity = new Models.NV_Dulieudubao()
{
SolieuDB_ID = soLieuIndex.ToString(),
Ngaydubao = datetime.Value,
BaocaoDiadanh_ID = row,
BaocaoThuoctinh_ID = col,
Giatri = value
};
db.NV_Dulieudubao.Add(entity);
#endregion
}
}
else
{
#region add du lieu du bao
soLieuIndex++;
Models.NV_Dulieudubao entity = new Models.NV_Dulieudubao()
{
SolieuDB_ID = soLieuIndex.ToString(),
Ngaydubao = datetime.Value,
BaocaoDiadanh_ID = row,
BaocaoThuoctinh_ID = col,
Giatri = value
};
db.NV_Dulieudubao.Add(entity);
#endregion
}
}
db.SaveChanges();
});
});
response = true;
}
catch (Exception ex)
{
Console.WriteLine("Error occurred: " + ex.StackTrace);
response = false;
}
return(response);
}