public JsonResult ExportData(string fromDate, string toDate, string isDuBao)
{
// fix data request
DateTime?fromdate = fromDate.ConvertStringToDate();
DateTime?todate = toDate.ConvertStringToDate();
// lấy dữ liệu xử lý
using (var db = new Models.DbEntities())
{
string fileName;
Dictionary <DateTime, string[, ]> list = GetDataSearch(db, fromDate, toDate, isDuBao);
string excelFile = Excel.getCopyExcelTemplateFile(out fileName);
var excelBytes = list.WriteExcel(excelFile);
if (excelBytes != null)
{
string fullPath = Path.Combine(Server.MapPath("~/TemplateFile/temp"), fileName);
using (var exportData = new MemoryStream())
{
exportData.Write(excelBytes, 0, excelBytes.Length);
exportData.Position = 0;
FileStream file = new FileStream(fullPath, FileMode.Create, FileAccess.Write);
exportData.WriteTo(file);
file.Close();
}
return(Json(new { messenger = "success", fileName, status = true }));
}
else
{
return(Json(new { messenger = list.Count() > 0 ? "success" : "not_data", status = false }));
}
}
}
public JsonResult GetData(string fromDate, string toDate, string bc)
{
try
{
Dictionary <DateTime, string[, ]> list = new Dictionary <DateTime, string[, ]>();
using (var db = new Models.DbEntities())
{
list = GetDataSearch(db, fromDate, toDate, bc);
}
return(Json(new { date = list.Keys, arrDatas = list.Values, status = true }));
}
catch (Exception ex)
{
return(Json(new { messenger = ex.Message, status = false }));
}
}
public static bool ReadAndWriteDataToExcel(this string filePath, bool isBC_QuanTrac)
{
bool response = true;
using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, true))
{
WorkbookPart workBookPart = spreadDocument.WorkbookPart;
Workbook workbook = workBookPart.Workbook;
using (var db = new Models.DbEntities())
{
using (DbContextTransaction transaction = db.Database.BeginTransaction())
{
try
{
workBookPart.Workbook.Descendants <Sheet>().ForEach(sheet =>
{
if (response)
{
WorksheetPart wsPart = (WorksheetPart)workBookPart.GetPartById(sheet.Id);
SheetData sheetData = wsPart.Worksheet.GetFirstChild <SheetData>();
bool res = sheetData.WriteDatabase(sheet.Name, workBookPart, db, isBC_QuanTrac);
if (!res)
{
response = false;
}
}
});
if (response)
{
transaction.Commit();
}
}
catch (Exception ex)
{
transaction.Rollback();
response = false;
}
}
}
}
return(response);
}
private Dictionary <DateTime, string[, ]> GetDataSearch(Models.DbEntities db, string fromDate, string toDate, string isDuBao)
{
// fix data request
DateTime?fromdate = fromDate.ConvertStringToDate();
DateTime?todate = toDate.ConvertStringToDate();
Dictionary <DateTime, string[, ]> list = new Dictionary <DateTime, string[, ]>();
string[,] arr;
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);
if (!"true".Equals(isDuBao))
{
var dataNgayQuanTrac = db.NV_DulieuQuantrac.Select(x => x.NgayQuantrac).Distinct()
.WhereIf(fromdate.HasValue, row => DateTime.Compare(fromdate.Value, row) <= 0)
.WhereIf(todate.HasValue, row => DateTime.Compare(row, todate.Value) <= 0);
// loaddata to string[,]
int?rowIndex, colIndex;
dataNgayQuanTrac.ForEach(date =>
{
var lstTTs = from thuoctinh in db.TD_Thuoctinh
select new DanhSachDiaDanh()
{
thuocTinhId = thuoctinh.Thuoctinh_ID,
diadanh = (from dd in db.TD_Diadanh
join dlQT in db.NV_DulieuQuantrac on dd.Diadanh_ID equals dlQT.BaocaoDiadanh_ID
where dlQT.BaocaoThuoctinh_ID == thuoctinh.Thuoctinh_ID && dlQT.NgayQuantrac == date
select new DanhSachThuocTinh()
{
DiaDanhId = dd.Diadanh_ID, GiaTri = dlQT.Giatri.ToString()
})
};
arr = null;
rowIndex = null;
lstDD.AsEnumerable().ForEach(ref rowIndex, row =>
{
colIndex = null;
arr = arr ?? new string[lstDD.Count(), lstTT.Count()];
lstTT.AsEnumerable().ForEach(ref colIndex, col =>
{
var danhSachDiaDanhs = lstTTs.Where(r => r.thuocTinhId.Equals(col)).FirstOrDefault();
if (danhSachDiaDanhs == null)
{
arr[rowIndex.Value, colIndex.Value] = "0";
}
else
{
var tt = danhSachDiaDanhs.diadanh.Where(d => d.DiaDanhId.Equals(row)).FirstOrDefault();
arr[rowIndex.Value, colIndex.Value] = tt == null ? "0" : tt.GiaTri;
}
});
});
list.Add(date, arr);
}
);
}
else
{
var dataNgayDuBao = db.NV_Dulieudubao.Select(x => x.Ngaydubao).Distinct()
.WhereIf(fromdate.HasValue, row => DateTime.Compare(fromdate.Value, row) <= 0)
.WhereIf(todate.HasValue, row => DateTime.Compare(row, todate.Value) <= 0);
// loaddata to string[,]
int?rowIndex, colIndex;
dataNgayDuBao.ForEach(date =>
{
var lstTTs = from thuoctinh in db.TD_Thuoctinh
select new DanhSachDiaDanh()
{
thuocTinhId = thuoctinh.Thuoctinh_ID,
diadanh = (from dd in db.TD_Diadanh
join dlDB in db.NV_Dulieudubao on dd.Diadanh_ID equals dlDB.BaocaoDiadanh_ID
where dlDB.BaocaoThuoctinh_ID == thuoctinh.Thuoctinh_ID && dlDB.Ngaydubao == date
select new DanhSachThuocTinh()
{
DiaDanhId = dd.Diadanh_ID, GiaTri = dlDB.Giatri.ToString()
})
};
arr = null;
rowIndex = null;
lstDD.AsEnumerable().ForEach(ref rowIndex, row =>
{
colIndex = null;
arr = arr ?? new string[lstDD.Count(), lstTT.Count()];
lstTT.AsEnumerable().ForEach(ref colIndex, col =>
{
var danhSachDiaDanhs = lstTTs.Where(r => r.thuocTinhId.Equals(col)).FirstOrDefault();
if (danhSachDiaDanhs == null)
{
arr[rowIndex.Value, colIndex.Value] = "0";
}
else
{
var tt = danhSachDiaDanhs.diadanh.Where(d => d.DiaDanhId.Equals(row)).FirstOrDefault();
arr[rowIndex.Value, colIndex.Value] = tt == null ? "0" : tt.GiaTri;
}
});
});
list.Add(date, arr);
}
);
}
return(list);
}
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);
}