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