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); }