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