public void create(SchedulesDTO schedule)
{
try
{
conn.Open();
string sql = "INSERT INTO Schedules VALUES(@id, @date, CONVERT(VARCHAR(8), @time, 108), @aircraftID, @routesID, @flightNumber, " +
"@economyPrice, @confirmed)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("id", schedule.ScheduleID);
cmd.Parameters.AddWithValue("date", schedule.Date.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("time", schedule.Time);
cmd.Parameters.AddWithValue("aircraftID", schedule.AircraftID);
cmd.Parameters.AddWithValue("routesID", schedule.RoutesID);
cmd.Parameters.AddWithValue("flightNumber", schedule.FlightNumber);
cmd.Parameters.AddWithValue("economyPrice", schedule.EconomyPrice);
cmd.Parameters.AddWithValue("confirmed", schedule.Confirmed);
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception e)
{
conn.Close();
MessageBox.Show(e.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnConfirmFlight_Click(object sender, EventArgs e)
{
if (dgv.CurrentCell == null)
{
return;
}
schedule = schedules.ElementAt(dgv.CurrentCell.RowIndex);
SchedulesDTO schedulesDTO = new SchedulesDTO();
if (schedule.Confirmed == 1)
{
schedulesDTO.Confirmed = 0;
btnConfirmlFlight.Text = "Confirm Flight";
}
else
{
schedulesDTO.Confirmed = 1;
btnConfirmlFlight.Text = "Cancel Flight";
}
schedulesDTO.ScheduleID = schedule.SchedulesID;
schedulesBUL.updateConfirmed(schedulesDTO);
schedules = schedulesBUL.getList();
this.loadData();
}
public SchedulesDTO getSchedule(String flightNumber, String depatureDate)
{
SchedulesDTO schedule = new SchedulesDTO();
try
{
conn.Open();
string sql = "SELECT TOP 1 SchedulesID, RoutesID FROM schedules where FlightNumber = @flightNumber and DateFlight = @depatureDate";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("flightNumber", flightNumber);
cmd.Parameters.AddWithValue("depatureDate", DateTime.Parse(depatureDate).ToString("yyyy-MM-dd"));
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
schedule.ScheduleID = dr["SchedulesID"].ToString().Trim();
schedule.RoutesID = dr["RoutesID"].ToString().Trim();
}
conn.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
conn.Close();
}
return(schedule);
}
public void updateWhenImport(SchedulesDTO schedule)
{
try
{
conn.Open();
string sql = "update Schedules set DateFlight = @dateFlight, TimeFlight = @timeFlight, " +
"AircraftID = @aircraftID, FlightNumber = @flightNumber, EconomyPrice = @economyPrice, " +
"Confirmed = @confirmed where SchedulesID = @scheduleID";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("dateFlight", schedule.Date);
cmd.Parameters.AddWithValue("timeFlight", schedule.Time);
cmd.Parameters.AddWithValue("aircraftID", schedule.AircraftID);
cmd.Parameters.AddWithValue("flightNumber", schedule.FlightNumber);
cmd.Parameters.AddWithValue("economyPrice", schedule.EconomyPrice);
cmd.Parameters.AddWithValue("confirmed", schedule.Confirmed);
cmd.Parameters.AddWithValue("scheduleID", schedule.ScheduleID);
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
conn.Close();
MessageBox.Show(ex.Message);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
string from = cbbFrom.Text;
string to = cbbTo.Text;
if (from == to)
{
MessageBox.Show("Sân bay đi và Sân bay đên không được trùng nhau, mời bạn chọn lại");
return;
}
AircraftDTO aircraft = new AircraftDTO();
aircraft.AircraftName = txtAircraft.Text;
aircraft.AircraftID = scheduleManager.AircraftID;
RoutesDTO route = new RoutesDTO();
route.ArrivalAirportID = airportsFrom.ElementAt(cbbFrom.SelectedIndex).AirportID;
route.DepartureAirportID = airportsTo.ElementAt(cbbTo.SelectedIndex).AirportID;
route.RouteID = scheduleManager.RoutesID;
SchedulesDTO schedule = new SchedulesDTO();
schedule.Date = dtpDate.Value.ToString();
schedule.Time = dtpTime.Value.ToString();
schedule.EconomyPrice = float.Parse(txtEconomyPrice.Text);
schedule.ScheduleID = scheduleManager.SchedulesID;
try
{
aircraftsBUL.update(aircraft);
routesBUL.update(route);
schedulesBUL.update(schedule);
MessageBox.Show("Cập nhật thành công");
} catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
this.Close();
frmMain frm = new frmMain();
frm.Show();
}
public void updateConfirmed(SchedulesDTO schedule)
{
try
{
conn.Open();
string sql = "update Schedules set Confirmed = @confirmed where SchedulesID = @scheduleID";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("scheduleID", schedule.ScheduleID);
cmd.Parameters.AddWithValue("confirmed", schedule.Confirmed);
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
conn.Close();
MessageBox.Show(ex.Message);
}
}
public void update(SchedulesDTO schedule)
{
try
{
conn.Open();
string sql = "update Schedules set EconomyPrice = @economyPrice, " +
"DateFlight = @dateFlight, TimeFlight = @timeFlight where SchedulesID = @scheduleID";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("dateFlight", schedule.Date);
cmd.Parameters.AddWithValue("timeFlight", schedule.Time);
cmd.Parameters.AddWithValue("economyPrice", schedule.EconomyPrice);
cmd.Parameters.AddWithValue("scheduleID", schedule.ScheduleID);
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception e)
{
conn.Close();
}
}
public void updateConfirmed(SchedulesDTO schedule)
{
schedulesDAL.updateConfirmed(schedule);
}
public void updateSchedule(SchedulesDTO schedule)
{
schedulesDAL.updateSchedule(schedule);
}
public List <List <int> > importSchedule(String[][] data)
{
// tạo ra các danh sách status khi import.
// mỗi ds status bao gồm: phần tử đầu tiên là status, các phần tử còn lại là dòng trong excel ứng vs mỗi status
List <int> success = new List <int> {
1
};
List <int> duplicate = new List <int> {
0
};
List <int> recordMissing = new List <int> {
-1
};
List <int> aircraftInvalid = new List <int> {
-2
};
List <int> routesInvalid = new List <int> {
-3
};
List <int> recordNotFound = new List <int> {
-4
};
List <List <int> > statusImport = new List <List <int> >(); // trả về 1 danh sách bao gồm các danh sách status
for (int i = 0; i < data.Length; i++)
{
bool isContinue = false;
for (int j = 0; j < data[i].Length; j++)
{
if (data[i][j] == null) // check thiếu dữ liệu
{
recordMissing.Add(i + 2); // thêm dòng excel bị lỗi. dữ liệu tính từ dòng thứ 2 trong excel => (+2)
isContinue = true;
}
}
if (isContinue)
{
continue;
}
String operation = data[i][0];
String date = data[i][1];
String time = data[i][2];
String from = data[i][3];
String to = data[i][4];
String flightNumber = data[i][5];
String aircraft = data[i][6];
float economyPrice;
try { economyPrice = float.Parse(data[i][7]); } catch { economyPrice = 0; };
int confirmed = data[i][8].Equals("OK") ? 1 : 0;
SchedulesDTO schedule = schedulesDAL.getSchedule(flightNumber, date);
String aircraftID = aircraftDAL.getAircraftID(aircraft);
String routesID = routesDAL.getRoutesID(from, to);
if (operation.Equals("ADD"))
{
if (schedule.ScheduleID != null) // check trùng bản ghi dựa vào flightNumber & date
{
duplicate.Add(i + 2);
continue;
}
else
{
if (aircraftID == null) // check tồn tại Aircraft
{
aircraftInvalid.Add(i + 2);
continue;
}
else
{
if (routesID == null) // check tồn tại Routes
{
routesInvalid.Add(i + 2);
continue;
}
else // thêm thành công
{
String lastScheduleID = schedulesDAL.getLastID(); // lấy id của bản ghi cuối cùng
String id = "S01";
if (lastScheduleID != null)
{
id = (int.Parse(lastScheduleID.Substring(1)) + 1).ToString(); // tăng id lên 1
id = "S" + id.PadLeft(2, '0');
}
schedule.ScheduleID = id;
schedule.Date = DateTime.Parse(date);
schedule.Time = time;
schedule.RoutesID = routesID;
schedule.AircraftID = aircraftID;
schedule.FlightNumber = flightNumber;
schedule.EconomyPrice = economyPrice;
schedule.Confirmed = confirmed;
schedulesDAL.create(schedule);
success.Add(i + 2);
}
}
}
}
if (operation.Equals("EDIT"))
{
if (schedule.ScheduleID == null) // check tồn tại ghi dựa vào flightNumber & date
{
recordNotFound.Add(i + 2);
continue;
}
else
{
if (aircraftID == null) // check tồn tại Aircraft
{
aircraftInvalid.Add(i + 2);
continue;
}
else
{
String fromAirportID = airportsDAL.getByIATAcode(from);
String toAirportID = airportsDAL.getByIATAcode(to);
if (fromAirportID == null || toAirportID == null)
{
routesInvalid.Add(i + 2);
continue;
}
else
{
schedule.ScheduleID = schedule.ScheduleID;
schedule.Date = DateTime.Parse(date);
schedule.Time = time;
schedule.RoutesID = routesID;
schedule.AircraftID = aircraftID;
schedule.FlightNumber = flightNumber;
schedule.EconomyPrice = economyPrice;
schedule.Confirmed = confirmed;
schedulesDAL.updateWhenImport(schedule);
RoutesDTO routes = new RoutesDTO();
routes.RoutesID = schedule.RoutesID;
routes.DepartureAirportID = fromAirportID;
routes.ArrivalAirportID = toAirportID;
routesDAL.update(routes);
success.Add(i + 2);
}
}
}
}
}
statusImport.Add(success);
statusImport.Add(duplicate);
statusImport.Add(recordMissing);
statusImport.Add(aircraftInvalid);
statusImport.Add(routesInvalid);
return(statusImport);
}
