public TripDetails GetTripDetails(string tripId)
{
var objReturn = new TripDetails();
using (var con = new MySqlConnection(ConnectionHelper.ServicesConnection()))
{
con.Open();
var cmd = new MySqlCommand("SELECT id, trip_id, trip, pax_name, gender, pax_email, pax_id, cast(seat as int) as seat, cost_center,ip, date_submit, cast(code as int) as code, random FROM seat WHERE trip_id=@trip_id AND gender='Driver'", con);
cmd.Parameters.AddWithValue("@trip_id", tripId);
var dr = cmd.ExecuteReader();
while (dr.Read())
{
objReturn = new TripDetails
{
car = dr["pax_id"].ToString(),
driver = dr["pax_name"].ToString(),
phone = dr["ip"].ToString(),
passengers = GetPassengers(tripId),
trip = dr["cost_center"].ToString(),
trip_date = Convert.ToDateTime(dr["trip"].ToString()),
trip_id = tripId,
};
}
}
return(objReturn);
}
public RequestRemarks CreateUpdateRequest(PostRequest newRequest)
{
var objReturn = new RequestRemarks();
var eecEmployee = new EECEmployee();
var employeeInfo = eecEmployee.SearchOnDirectory(newRequest.emp_id);
var info = employeeInfo.Count != 0 ? employeeInfo[0] : null;
if (info != null)
{
if (IsTripValid(newRequest.trip_id))
{
if (newRequest.seat_no >= 0 && newRequest.seat_no <= 6)
{
if (!IsSeatAvailable(newRequest.trip_id, newRequest.seat_no))
{
objReturn = new RequestRemarks
{
remarks = "Seat selected is no longer available!",
subject = "Already Taken"
};
}
else
{
if (IsUpdateSeat(info.emp_ID, newRequest.trip_id, newRequest.seat_no))
{
if (IsRandomCodeValid(newRequest.trip_id, newRequest.random))
{
using (var con = new MySqlConnection(ConnectionHelper.ServicesConnection()))
{
con.Open();
var cmd = new MySqlCommand("UPDATE seat SET seat=@seat,date_submit=@date_submit WHERE pax_id=@pax_id AND trip_id=@trip_id", con);
cmd.Parameters.AddWithValue("@seat", newRequest.seat_no);
cmd.Parameters.AddWithValue("@date_submit", DateTime.Now);
cmd.Parameters.AddWithValue("@trip_id", newRequest.trip_id);
cmd.Parameters.AddWithValue("@pax_id", info.emp_ID);
if (cmd.ExecuteNonQuery() > 0)
{
objReturn = new RequestRemarks
{
remarks = "You have updated your reserved seat!",
subject = "Updated"
};
AppendLog(info.e_Mail.Split('@')[0], " updated to " + newRequest.seat_no + " on ", newRequest.trip_id);
}
}
}
else
{
objReturn = new RequestRemarks
{
remarks = "Trip not found.",
subject = "Notice"
};
}
}
else
{
var tripDetails = new TripDetails();
var details = tripDetails.GetTripDetails(newRequest.trip_id);
using (var con = new MySqlConnection(ConnectionHelper.ServicesConnection()))
{
con.Open();
var cmd = new MySqlCommand("INSERT INTO seat ( trip_id, trip, pax_name, gender, pax_email, pax_id, seat, cost_center, ip, date_submit, code, random) VALUES(@trip_id,@trip,@pax_name,@gender,@pax_email,@pax_id,@seat,@cost_center,@ip,@date_submit,@code,@random)", con);
cmd.Parameters.AddWithValue("@trip_id", newRequest.trip_id);
cmd.Parameters.AddWithValue("@trip", details.trip_date);
cmd.Parameters.AddWithValue("@pax_name", info.employee_Name_English);
cmd.Parameters.AddWithValue("@gender", info.gender);
cmd.Parameters.AddWithValue("@pax_email", info.e_Mail);
cmd.Parameters.AddWithValue("@pax_id", info.emp_ID);
cmd.Parameters.AddWithValue("@seat", newRequest.seat_no);
cmd.Parameters.AddWithValue("@cost_center", info.cost_Center);
cmd.Parameters.AddWithValue("@ip", "EEC Mobility");
cmd.Parameters.AddWithValue("@date_submit", DateTime.Now);
cmd.Parameters.AddWithValue("@code", 1);
cmd.Parameters.AddWithValue("@random", RandomizeHelper.GenerateRandomString());
if (cmd.ExecuteNonQuery() > 0)
{
objReturn = new RequestRemarks
{
remarks = "You have reserved a seat!",
subject = "Reserved"
};
AppendLog(info.e_Mail.Split('@')[0], " reserved " + newRequest.seat_no + " on ", newRequest.trip_id);
}
}
}
}
}
else
{
objReturn = new RequestRemarks
{
remarks = "Seat not found.",
subject = "Notice"
};
}
}
else
{
objReturn = new RequestRemarks
{
remarks = "Trip not found.",
subject = "Notice"
};
}
}
else
{
objReturn = new RequestRemarks
{
remarks = "Request should be initiated by an EEC Employee.",
subject = "Notice"
};
}
return(objReturn);
}