private void btnSearchAvailablility_Click(object sender, EventArgs e)
        {
            Database db = new Database();

            if (cmboScheduleID.Text.Equals("") || date.Value.Date < DateTime.Today.Date)
            {
                MessageBox.Show("Error! invalid fields!");
            }
            else
            {
                if (db.searchScheduleDateLog(int.Parse(cmboScheduleID.Text), date.Value))
                {
                    scheduleDateLog     = db.getScheduleDateLog(int.Parse(cmboScheduleID.Text), date.Value);
                    txtSeatsBooked.Text = "" + scheduleDateLog.getSeatsBooked();
                    txtSerialNo.Text    = "" + scheduleDateLog.getSerialNo();
                }
                else
                {
                    try
                    {
                        scheduleDateLog.setDate(date.Value.Date);
                        scheduleDateLog.setScheduleID(int.Parse(cmboScheduleID.Text));
                        scheduleDateLog.setSeatsBooked(0);
                        db.addScheduleDateLog(scheduleDateLog);
                        txtSeatsBooked.Text = "" + 0;
                        txtSerialNo.Text    = "" + db.getSerialNo(int.Parse(cmboScheduleID.Text), date.Value);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
                try
                {
                    schedule = db.getSchedule(int.Parse(cmboScheduleID.Text));
                    if (schedule != null)
                    {
                        stations = db.getStationsOfRoute(schedule.getRoute());
                        foreach (Station station in stations)
                        {
                            cmboSourceStation.Items.Add(station.getName());
                            cmboDestinationStation.Items.Add(station.getName());
                        }
                        txtTrain.Text = schedule.getTrain().getRegNo();
                        coaches       = db.getTrainCoaches(schedule.getTrain());
                        if (coaches != null)
                        {
                            foreach (Coach coach in coaches)
                            {
                                cmboCoachType.Items.Add(coach.getCoachId());
                            }
                        }
                        else
                        {
                            MessageBox.Show("Coaches not found");
                        }
                    }
                    else
                    {
                        MessageBox.Show("Unable to find Schedule!");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
        private void btnsubmitTicket_Click(object sender, EventArgs e)
        {
            Database db = new Database();

            if (txtPassengerID.Text.Equals("") || txtSeatNo.Text.Equals("") || txtFare.Equals(""))
            {
                MessageBox.Show("Error! passenger id, seat or fare is empty!");
            }
            else
            {
                Coach findCoach = new Coach();
                foreach (Coach coach in coaches)
                {
                    if (coach.getCoachId() == int.Parse(cmboCoachType.Text))
                    {
                        findCoach = coach;
                    }
                }

                try
                {
                    if (db.getBookedSeatsInCoachCount(scheduleDateLog.getSerialNo(), findCoach.getCoachId()) < findCoach.getNoOfSeats())
                    {
                        int seat_no = int.Parse(txtSeatNo.Text);
                        if (db.isSeatBooked(seat_no, findCoach, scheduleDateLog))
                        {
                            MessageBox.Show("Error! seat is already booked.");
                            return;
                        }

                        Ticket ticket = new Ticket();
                        ticket.setSeat(int.Parse(txtSeatNo.Text));
                        passenger.setPid(int.Parse(txtPassengerID.Text));
                        ticket.setPassenger(passenger);
                        ticket.setFare(int.Parse(txtFare.Text));
                        Station srcStation = new Station();
                        Station dstStation = new Station();
                        foreach (Station station in stations)
                        {
                            if (station.getName().Equals(cmboSourceStation.Text))
                            {
                                srcStation = station;
                            }
                            if (station.getName().Equals(cmboDestinationStation.Text))
                            {
                                dstStation = station;
                            }
                        }
                        ticket.setSource(srcStation);
                        ticket.setDestination(dstStation);
                        ticket.setCoach(findCoach);
                        scheduleDateLog = db.getScheduleDateLog(int.Parse(cmboScheduleID.Text), date.Value);
                        ticket.setScheduleDateLog(scheduleDateLog);
                        if (db.addTicket(ticket))
                        {
                            ticket.setId(db.getTicketId(ticket));


                            scheduleDateLog.setSeatsBooked(scheduleDateLog.getSeatsBooked() + 1);
                            txtSeatsBooked.Text = "" + scheduleDateLog.getSeatsBooked();
                            if (db.updateBookedTicketsCount(scheduleDateLog))
                            {
                                MessageBox.Show("Ticket successfully added!");
                                this.Hide();
                                frmGenerateTicket frm = new frmGenerateTicket(active, ticket);
                                frm.Show();
                            }
                            else
                            {
                                MessageBox.Show("Unable to update booked seats counter!");
                            }
                        }
                        else
                        {
                            MessageBox.Show("Unable to add ticket!");
                        }
                    }
                    else
                    {
                        MessageBox.Show("All seats are booked in this coach!");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }