//takes ticket id as input and cancels the booking by removing required column in booking table
//increments the available tickets
//returns confirmation as a string with calculated refund item with respect to hours left to booking
public static string CancelTicket(string bookid)
{
try
{
double amountRefund;
var r = from L in d.Booking_Table
where L.Ticket_ID == bookid
select L;
Booking_Table B = r.FirstOrDefault();
string sid = B.SID;
int not = B.NoOfTickets;
var s = from L1 in d.Schedule_Table
where L1.SID == sid
select L1;
Schedule_Table S = s.FirstOrDefault();
string rid = S.Route_ID;
DateTime dateOT = S.DateOfJourney;
int timeOT = int.Parse(S.StartTime.Hours.ToString());
DateTime todaydate = DateTime.Now;
int age = (int)(dateOT.Subtract(todaydate).TotalHours);
int hoursLeft = age + timeOT;
var rt = from L2 in d.Route_Table
where L2.Route_ID == rid
select L2;
Route_Table R = rt.FirstOrDefault();
int cost = R.Cost;
int TicketCost = cost * not;
if (hoursLeft >= 48)
{
amountRefund = TicketCost - (0.1 * TicketCost);
}
else if (hoursLeft < 48 && hoursLeft >= 24)
{
amountRefund = TicketCost - (0.25 * TicketCost);
}
else if (hoursLeft < 24 && hoursLeft >= 12)
{
amountRefund = TicketCost - (0.5 * TicketCost);
}
else
{
amountRefund = TicketCost - (1 * TicketCost);
}
S.AvailableSeats = S.AvailableSeats + not;
d.SaveChanges();
d.Booking_Table.Remove(B);
d.SaveChanges();
return("Tickets Cancelled Successfully. Refund of Amount Rs:" + amountRefund + " inititated to Customer");
}
catch (DbUpdateException E)
{
SqlException ex = E.GetBaseException() as SqlException;
}
return(null);
}
//takes the required values and add schedule related information to database
public static string AddSchedule(addSchedule a)
{
try
{
var b = from L in d.BusDetails_Table
where L.Bus_ID == a.Bid
select L;
var s = from K in d.Schedule_Table
select K;
List <Schedule_Table> sc = s.ToList();
int flag = 0;
foreach (var c in sc)
{
if (a.Doj == c.DateOfJourney && a.Bid == c.Bus_ID)
{
flag = 1;
break;
}
}
BusDetails_Table B = b.FirstOrDefault();
if (B.Capacity >= a.Capacity)
{
if (flag == 0)
{
int sid = (from r in d.Schedule_Table
select r).Count() + 1;
Schedule_Table Sh = new Schedule_Table();
Sh.SID = "S" + sid;
Sh.Route_ID = a.Rid;
Sh.Bus_ID = a.Bid;
Sh.StartTime = a.StartTime.TimeOfDay;
Sh.DateOfJourney = a.Doj;
Sh.AvailableSeats = a.Capacity;
d.Schedule_Table.Add(Sh);
d.SaveChanges();
return("Schedule created with Schedule id " + Sh.SID);
}
else
{
return("Journey already exist");
}
}
else
{
return("Available seats are more than capacity");
}
}
catch (DbUpdateException E)
{
SqlException ex = E.GetBaseException() as SqlException;
}
return(null);
}
//confirms the ticket by putting data in Booking_Table
//reduces the number of available tickets
//returns string to show confirmation
public static string BookTicket(string sid, int seats, int cid)
{
try
{
var r = (from L in d.Schedule_Table
where L.SID == sid
select L).FirstOrDefault();
Schedule_Table S = r;
string bid = S.Bus_ID;
if (S.AvailableSeats < seats)
{
return("Sorry , no seats left");
}
else
{
S.AvailableSeats = S.AvailableSeats - seats;
d.SaveChanges();
}
string day = DateTime.Now.ToString("dd");
string month = DateTime.Now.ToString("MM");
string year = DateTime.Now.ToString("yy");
int bno = (from rno in d.Booking_Table
select rno).Count() + 1;
Booking_Table Bt = new Booking_Table();
Bt.Customer_ID = cid;
Bt.SID = sid;
Bt.NoOfTickets = seats;
Bt.Ticket_ID = bid + day + month + year + string.Format("{0:0000}", bno);
d.Booking_Table.Add(Bt);
d.SaveChanges();
return("“Ticket Booked successfully");
}
catch (DbUpdateException E)
{
SqlException ex = E.GetBaseException() as SqlException;
if (ex.Message.Contains("PK_Schedule_Table"))
{
return("schedule id already exists");
}
if (ex.Message.Contains("PK_BusDetails_Table"))
{
return("Bus id already exists");
}
}
return(null);
}