public void CreatePlanning(PlannerDTO dto) { var command = new OleDbCommand(string.Empty, _connection); command.CommandText = $"SELECT Id FROM tblPlanning WHERE StaffId = (SELECT Id FROM tblStaff WHERE HKID = {dto.HKID})"; var result = command.ExecuteScalar(); if (result != null) { _connection.Close(); throw new InvalidOperationException("Planning already exists for this staff."); } CreatePlaning(command, dto); }
private void btnSave_Click(object sender, EventArgs e) { try { if (txtHKId.Text == string.Empty) { MessageBox.Show("Invalid HKID"); } else if (txtName.Text == string.Empty) { MessageBox.Show("Invalid Name"); } else if (txtRank.Text == string.Empty) { MessageBox.Show("Invalid Rank"); } else if (txtVesselName.Text == string.Empty) { MessageBox.Show("Invalid Vessel Name"); } else if (txtTechnicalGroup.Text == string.Empty) { MessageBox.Show("Invalid Technical Group"); } else { var dto = new PlannerDTO { COC = dtpCOC.Value, HKID = Convert.ToInt32(txtHKId.Text), Name = txtName.Text, Rank = txtRank.Text, Remarks = txtRemarks.Text, Replacement = txtReplacement.Text, SerialNumber = Convert.ToInt32(txtSerialNumber.Text), TechnicalGroup = txtTechnicalGroup.Text, Vessel = txtVesselName.Text }; DBLayer.Instance.CreatePlanning(dto); MessageBox.Show("Planning created successfully."); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public static IEnumerable <PlannerDTO> GetExcelData(string filePath) { using (XLWorkbook workbook = new XLWorkbook(filePath)) { IXLWorksheet worksheet = workbook.Worksheet(1); var count = 0; foreach (IXLRow row in worksheet.RowsUsed()) { count++; if (count == 1) { continue; } var dto = new PlannerDTO(); dto.SerialNumber = Convert.ToInt32(row.Cell(1).Value); dto.HKID = Convert.ToInt32(row.Cell(2).Value); dto.Name = Convert.ToString(row.Cell(3).Value); dto.Rank = Convert.ToString(row.Cell(4).Value); dto.Vessel = Convert.ToString(row.Cell(5).Value); dto.TechnicalGroup = Convert.ToString(row.Cell(6).Value); DateTime coc; double cocNumberdate; DateTime cocDate; if (row.Cell(7).TryGetValue <DateTime>(out coc)) { dto.COC = coc; } else if (row.Cell(7).TryGetValue <double>(out cocNumberdate)) { dto.COC = DateTime.FromOADate(cocNumberdate); } else if (DateTime.TryParseExact(Convert.ToString(row.Cell(7).Value), Constants.DateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out cocDate)) { dto.COC = cocDate; } dto.Replacement = Convert.ToString(row.Cell(8).Value); dto.Remarks = Convert.ToString(row.Cell(9).Value); yield return(dto); } } }
public static IEnumerable <PlannerDTO> GetStaffData(string filePath) { using (XLWorkbook workbook = new XLWorkbook(filePath)) { IXLWorksheet worksheet = workbook.Worksheet(1); var count = 0; foreach (IXLRow row in worksheet.RowsUsed()) { count++; if (count == 1) { continue; } var dto = new PlannerDTO(); dto.HKID = Convert.ToInt32(row.Cell(1).Value); dto.Name = Convert.ToString(row.Cell(2).Value); dto.Rank = Convert.ToString(row.Cell(3).Value); dto.Vessel = Convert.ToString(row.Cell(4).Value); yield return(dto); } } }
private void CreatePlaning(OleDbCommand command, PlannerDTO item) { command.CommandText = $"SELECT Id FROM tblStaff WHERE HKID = {item.HKID}"; int hkid = Convert.ToInt32(command.ExecuteScalar()); if (hkid == 0) { hkid = CreateStaff(command, item.HKID, item.Name); } command.CommandText = $"SELECT Id FROM tblRank WHERE Name = '{item.Rank}'"; int rankId = Convert.ToInt32(command.ExecuteScalar()); if (rankId == 0) { rankId = CreateRank(command, item.Name); } command.CommandText = $"SELECT Id FROM tblVessel WHERE Name = '{item.Vessel}'"; int vesselId = Convert.ToInt32(command.ExecuteScalar()); if (vesselId == 0) { vesselId = CreateVessel(command, item.Vessel); } command.CommandText = $"SELECT Id FROM tblTechnicalGroup WHERE Name = '{item.TechnicalGroup}'"; int techgroupId = Convert.ToInt32(command.ExecuteScalar()); if (techgroupId == 0) { techgroupId = CreateTechnicalGroup(command, item.TechnicalGroup); } command.CommandText = $@"INSERT INTO tblPlanning(SerialNumber, StaffId, RankId, VesselId, TechnicalGroupId, COC, Replacement, Remarks) VALUES({item.SerialNumber}, {hkid}, {rankId}, {vesselId}, {techgroupId}, '{item.COC}', '{item.Replacement.Replace("'", "''")}', '{item.Remarks.Replace("'", "''")}')"; command.ExecuteNonQuery(); }