public void saveData()
{
if (!IsValid())
{
return;
}
int idEmployee = int.Parse(cbHousekeeper.SelectedValue.ToString());
var cleaning = db.CleaningRoom
.FirstOrDefault(X =>
X.Date == dtpDate.Value.Date &&
X.EmployeeID == idEmployee);
if (cleaning == null)
{
cleaning = new CleaningRoom
{
Date = dtpDate.Value.Date,
EmployeeID = idEmployee
};
db.CleaningRoom.Add(cleaning);
db.SaveChanges();
}
int idRoom = int.Parse(cbRoomNumber.SelectedValue.ToString());
db.CleaningRoomDetail.Add(new CleaningRoomDetail
{
CleaningRoomID = cleaning.ID,
RoomID = idRoom
});
db.SaveChanges();
loadData();
}
private void btnImport_Click(object sender, EventArgs e)
{
var excel = new Excel.Application();
var workbooks = excel.Workbooks;
var dialog = new OpenFileDialog
{
Title = "Choose location to save",
Filter = "excel file(*.xlsx)|*.xlsx",
Multiselect = false
};
if (dialog.ShowDialog() != DialogResult.OK)
{
return;
}
var workbook = workbooks.Open(dialog.FileName);
Excel.Worksheet worksheet = workbook.Worksheets[1];
var cells = worksheet.Cells;
cells.ClearFormats();
var range = worksheet.UsedRange;
var columns = range.Columns;
var rows = range.Rows;
Excel.Range range2 = worksheet.Range[worksheet.Cells[5, 1], worksheet.Cells[rows.Count, columns.Count]];
var values = (object[, ])range2.Value2;
string lastRoomNumber = "";
var cleaningRoom = db.CleaningRoom
.Where(X => X.EmployeeID == X.Employee.ID)
.ToArray();
var cleaningDetail = db.CleaningRoomDetail
.ToArray()
.Where(X => cleaningRoom.Select(Y => Y.ID)
.Contains(X.CleaningRoomID))
.ToArray();
var cleaningItem = db.CleaningRoomItem
.ToArray()
.Where(X => cleaningDetail.Select(Y => Y.CleaningRoomID)
.Contains(X.ID))
.ToArray();
try{
db.CleaningRoomItem.RemoveRange(cleaningItem);
db.SaveChanges();
db.CleaningRoomDetail.RemoveRange(cleaningDetail);
db.SaveChanges();
db.CleaningRoom.RemoveRange(cleaningRoom);
db.SaveChanges();
}
catch
{
MessageBox.Show("Error during deleting");
}
string date = cells[1, 2].Value2.ToString();
DateTime date2 = new DateTime();
try
{
date2 = DateTime.ParseExact(date, "dd MM yyyy hh:mm", null);
}
catch
{
MessageBox.Show("date time in wrong Format");
date2 = DateTime.Now.Date;
}
var cleaning = new CleaningRoom
{
Date = date2,
EmployeeID = employee.ID
};
db.CleaningRoom.Add(cleaning);
db.SaveChanges();
var detail = new CleaningRoomDetail();
for (int i = 1; i <= values.GetLength(0); i++)
{
bool addingNew = false;
if (values[i, 1] != null && values[i, 1].ToString() != "")
{
lastRoomNumber = values[i, 1].ToString();
addingNew = true;
}
if (addingNew)
{
detail.CleaningRoomID = cleaning.ID;
detail.RoomID = db.Room.FirstOrDefault(x => x.RoomNumber == lastRoomNumber).ID;
if (values[i, 2] == null)
{
detail.StartDateTime = null;
}
else
{
detail.StartDateTime = DateTime.ParseExact(values[i, 2].ToString(), "dd MMMM yyyy hh:mm", CultureInfo.InvariantCulture);
}
if (values[i, 3] == null)
{
detail.FinishDateTime = null;
}
else
{
detail.FinishDateTime = DateTime.ParseExact(values[i, 3].ToString(), "dd MMMM yyyy hh:mm", CultureInfo.InvariantCulture);
}
if (values[i, 4] == null)
{
detail.Note = null;
}
else
{
detail.Note = values[i, 4].ToString();
}
if (values[i, 5] == null)
{
detail.StatusCleaning = null;
}
else
{
detail.StatusCleaning = values[i, 5].ToString();
}
db.CleaningRoomDetail.Add(detail);
db.SaveChanges();
}
string name = values[i, 6].ToString();
int qty = int.Parse(values[i, 7].ToString());
string status = values[i, 8].ToString();
var item = db.Item.FirstOrDefault(x => x.Name == name);
if (item == null)
{
MessageBox.Show($"Item not found at row {i}");
}
else
{
var detailItem = new CleaningRoomItem
{
CleaningRoomDetailID = detail.ID,
ItemID = item.ID,
Qty = qty,
Status = status
};
db.CleaningRoomItem.Add(detailItem);
db.SaveChanges();
}
}
Marshal.ReleaseComObject(range2);
Marshal.ReleaseComObject(columns);
Marshal.ReleaseComObject(rows);
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(cells);
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excel);
}
