public bool AddEvent(AEvent tourEvent)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
Event newEvent = new Event();
newEvent.TourID = System.Guid.Parse(tourEvent.TourID);
newEvent.TourDate = tourEvent.TourDate;
newEvent.TourGuide = tourEvent.TourGuide;
newEvent.IsOn = (byte)(tourEvent.IsOn? 1 : 0);
dc.Events.InsertOnSubmit(newEvent);
dc.SubmitChanges();
return true;
}
}
public bool AddTour(ATour tour)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
Tour newTour = new Tour();
newTour.TourName = tour.TourName;
newTour.TourPrice = tour.TourPrice;
newTour.TourLocation = tour.TourLocation;
newTour.TourArea = tour.TourArea;
newTour.TourCategory = tour.TourCategory;
newTour.TourDuration = (short)tour.TourDuration;
newTour.TourDescription = tour.TourDescription;
newTour.MinReg = (byte)tour.MinReg;
newTour.MaxReg = (byte)tour.MaxReg;
newTour.TourID = System.Guid.NewGuid();
dc.Tours.InsertOnSubmit(newTour);
dc.SubmitChanges();
return true;
}
}
public bool AddUser(AUser userReg)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
User user = new User();
user.RegTime = (DateTime)userReg.RegTime;
user.RegIP = userReg.UserIP;
user.UserFirstName = userReg.UserFirstName;
user.UserLastName = userReg.UserLastName;
user.UserPhone = userReg.UserPhone;
user.UserEmail = userReg.UserEmail;
user.UserPassword = userReg.UserPassword;
user.UserBirthday = userReg.UserBirthday;
user.Username = userReg.Username;
user.salt = userReg.Salt;
user.UserID = System.Guid.NewGuid();
dc.Users.InsertOnSubmit(user);
dc.SubmitChanges();
return true;
}
}
public bool AddReg(AReg reg)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
Registration registration = new Registration();
registration.IsSentEmail = 0;
registration.IsPaid = 0;
registration.Attended = 0;
registration.RegFirstName = reg.RegFirstName;
registration.RegLastName = reg.RegLastName;
registration.RegBirthday = reg.RegBirthday;
registration.TourDate = reg.TourDate;
registration.TourID = System.Guid.Parse(reg.TourID);
registration.UserID = System.Guid.Parse(reg.UserID);
registration.RegTime = DateTime.Now;
registration.WillAttend = (byte)(reg.WillAttend?1:0);
registration.RegID = System.Guid.NewGuid();
dc.Registrations.InsertOnSubmit(registration);
dc.SubmitChanges();
return true;
}
}
// gets tour details by tour id
public ATour GetTourByID(string tourID)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
ATour tour = (from c in dc.Tours
where c.TourID.ToString() == tourID
select new ATour()
{
TourID = c.TourID.ToString(),
TourName = c.TourName,
TourLocation = c.TourLocation,
TourArea = c.TourArea,
TourCategory = c.TourCategory,
TourDuration = (int)c.TourDuration,
TourPrice = (decimal)c.TourPrice,
MinReg = (int)c.MinReg,
MaxReg = (int)c.MaxReg,
TourDescription = c.TourDescription,
ImageData = c.ImageData,
ImageMimeType = c.ImageMimeType
}
).FirstOrDefault<ATour>();
return tour;
}
}
// gets registrations related to user
public List<AReg> GetRegistrationsByUserID(string userID)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
List<AReg> rows = (from tours in dc.Tours
join regs in dc.Registrations on tours.TourID equals regs.TourID
where regs.UserID.ToString()==userID
orderby regs.TourDate
select new AReg()
{
RegID = regs.RegID.ToString(),
TourID = regs.TourID.ToString(),
TourName = tours.TourName,
TourDate = regs.TourDate,
UserID = regs.UserID.ToString(),
UserName = "",
RegFirstName = regs.RegFirstName,
RegLastName = regs.RegLastName,
RegBirthday = regs.RegBirthday,
WillAttend = regs.WillAttend == 1 ? true : false,
IsPaid = regs.IsPaid == 1 ? true : false,
IsSentEmail = regs.IsSentEmail == 1 ? true : false,
Attended = regs.Attended == 1 ? true : false,
RegTime = regs.RegTime
}
).ToList<AReg>();
return rows;
}
}
// gets events by the tourid
public List<AEvent> GetEventsByTourId(string id)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
List<AEvent> rows = (from tours in dc.Tours
where tours.TourID.ToString() == id
join events in dc.Events on tours.TourID equals events.TourID
orderby events.TourDate
select new AEvent()
{
TourName = tours.TourName,
TourID = events.TourID.ToString(),
TourDate = events.TourDate,
TourOriginalDate = events.TourDate,
TourGuide = events.TourGuide,
IsOn = events.IsOn==1?true:false
}
).ToList<AEvent>();
return rows;
}
}
public bool DeleteTour(string id)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
Tour row = (from c in dc.Tours
where (c.TourID.ToString() == id)
select c).FirstOrDefault<Tour>();
dc.Tours.DeleteOnSubmit(row);
// The deleted Tour's events must be deleted too. Otherwise, the sql servers throws an
// exeption.
List<Event> events = (from e in dc.Events
where (e.TourID.ToString() == id)
select e).ToList();
foreach (Event e in events)
{
DeleteEvent(e.TourID.ToString(), e.TourDate); // this will also deleted all registrations for the event
}
dc.SubmitChanges();
return true;
}
}
// gets all users
public List<AUser> GetUsers()
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
List<AUser> rows = (from c in dc.Users
orderby c.RegTime
select new AUser()
{
UserID = c.UserID.ToString(),
RegTime = c.RegTime,
UserIP = c.RegIP,
UserFirstName = c.UserFirstName,
UserLastName = c.UserLastName,
UserPhone = c.UserPhone,
UserEmail = c.UserEmail,
UserPassword = c.UserPassword,
Username = c.Username,
UserBirthday = c.UserBirthday,
ResetToken = c.ResetToken,
Salt = c.salt.ToString()
}
).ToList<AUser>();
return rows;
}
}
// gets an event by tour id and tour date
public AEvent GetEvent(string id, DateTime date)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
AEvent tourEvent = (from tours in dc.Tours
join events in dc.Events on tours.TourID equals events.TourID
where events.TourDate == date && events.TourID.ToString() == id
select new AEvent()
{
TourName = tours.TourName,
TourID = events.TourID.ToString(),
TourDate = events.TourDate,
TourOriginalDate = events.TourDate,
TourGuide = events.TourGuide,
IsOn = events.IsOn==1?true:false
}
).Single<AEvent>();
return tourEvent;
}
}
public bool EditUser(AUser user)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
User row = (from c in dc.Users
where (c.UserID.ToString() == user.UserID)
select c).FirstOrDefault<User>();
row.UserBirthday = user.UserBirthday;
row.UserEmail = user.UserEmail;
row.UserFirstName = user.UserFirstName;
row.UserLastName = user.UserLastName;
row.Username = user.Username;
row.UserPassword = user.UserPassword;
row.UserPhone = user.UserPhone;
row.ResetToken = user.ResetToken;
row.salt = user.Salt;
dc.SubmitChanges();
return true;
}
}
public bool EditTour(ATour tour)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
Tour row = (from c in dc.Tours
where (c.TourID.ToString() == tour.TourID)
select c).FirstOrDefault<Tour>();
row.MaxReg = (byte)tour.MaxReg;
row.MinReg = (byte)tour.MinReg;
row.TourArea = tour.TourArea;
row.TourCategory = tour.TourCategory;
row.TourDescription = tour.TourDescription;
row.TourDuration = (short)tour.TourDuration;
row.TourLocation = tour.TourLocation;
row.TourName = tour.TourName;
row.TourPrice = tour.TourPrice;
row.ImageData = tour.ImageData;
row.ImageMimeType = tour.ImageMimeType;
dc.SubmitChanges();
return true;
}
}
public bool EditReg(AReg reg)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
Registration row = (from c in dc.Registrations
where (c.RegID.ToString() == reg.RegID)
select c).FirstOrDefault<Registration>();
row.IsSentEmail = (byte)(reg.IsSentEmail?1:0);
row.IsPaid = (byte)(reg.IsPaid?1:0);
row.Attended = (byte)(reg.Attended?1:0);
row.RegFirstName = reg.RegFirstName;
row.RegLastName = reg.RegLastName;
row.RegBirthday = reg.RegBirthday;
row.WillAttend = (byte)(reg.WillAttend?1:0);
dc.SubmitChanges();
return true;
}
}
public bool EditEvent(AEvent tourEvent)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
if (tourEvent.TourDate == tourEvent.TourOriginalDate) // The TourDate is not changed - save the other changes
{
Event row = (from c in dc.Events
where (c.TourID.ToString() == tourEvent.TourID && c.TourDate == tourEvent.TourDate)
select c).FirstOrDefault<Event>();
row.TourGuide = tourEvent.TourGuide;
row.IsOn = (byte)(tourEvent.IsOn?1:0);
dc.SubmitChanges();
return true;
}
else // The TourDate has been changed - delete the event and create a new one with the new date. Copy the registrations with the new date
{
AddEvent(tourEvent); // Adding the new event (the same event with the new date)
// Adding new registrations to the new event (with the new date)
List<Registration> registrations = (from r in dc.Registrations
where (r.TourID.ToString() == tourEvent.TourID && r.TourDate == tourEvent.TourOriginalDate)
select r).ToList();
foreach (Registration reg in registrations)
{
AReg newReg = new AReg();
newReg.IsSentEmail = reg.IsSentEmail==1?true:false;
newReg.IsPaid = reg.IsPaid==1?true:false;
newReg.Attended = reg.Attended==1?true:false;
newReg.RegFirstName = reg.RegFirstName;
newReg.RegLastName = reg.RegLastName;
newReg.RegBirthday = reg.RegBirthday;
newReg.TourDate = tourEvent.TourDate;
newReg.TourID = tourEvent.TourID;
newReg.UserID = reg.UserID.ToString();
newReg.WillAttend = reg.WillAttend==1?true:false;
AddReg(newReg);
}
DeleteEvent(tourEvent.TourID, tourEvent.TourOriginalDate); // this function also deletes the previous date regisrations
dc.SubmitChanges();
return true;
}
}
}
public bool DeleteUser(string id)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
User row = (from c in dc.Users
where (c.UserID.ToString() == id)
select c).FirstOrDefault<User>();
dc.Users.DeleteOnSubmit(row);
// The deleted User's registrations must be deleted too. Otherwise, the sql servers throws an
// exeption.
List<Registration> registrations = (from r in dc.Registrations
where (r.UserID.ToString() == id)
select r).ToList();
foreach (Registration reg in registrations)
{
dc.Registrations.DeleteOnSubmit(reg);
}
dc.SubmitChanges();
return true;
}
}
// gets tours by location/area/category
public List<ATour> GetTours(string keyword)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
List<ATour> rows = (from c in dc.Tours
where c.TourLocation.Contains(keyword) || c.TourArea.Contains(keyword) || c.TourCategory.Contains(keyword)
select new ATour()
{
TourID= c.TourID.ToString(),
TourName = c.TourName,
TourLocation = c.TourLocation,
TourArea = c.TourArea,
TourCategory = c.TourCategory,
TourDuration = (int)c.TourDuration,
TourPrice = (decimal)c.TourPrice,
MinReg = (int)c.MinReg,
MaxReg = (int)c.MaxReg,
TourDescription = c.TourDescription,
ImageData = c.ImageData,
ImageMimeType = c.ImageMimeType
}
).ToList<ATour>();
return rows;
}
}
// gets user by username
public AUser GetUser(string username)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
AUser user = (from c in dc.Users
where c.Username == username
select new AUser()
{
UserID = c.UserID.ToString(),
RegTime = c.RegTime,
UserIP = c.RegIP,
UserFirstName = c.UserFirstName,
UserLastName = c.UserLastName,
UserPhone = c.UserPhone,
UserEmail = c.UserEmail,
UserPassword = c.UserPassword,
Username = c.Username,
UserBirthday = c.UserBirthday,
ResetToken = c.ResetToken,
Salt = c.salt.ToString()
}
).SingleOrDefault<AUser>();
return user;
}
}
// gets events by date
public List<AEvent> GetEvents(DateTime date)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
List<AEvent> rows = (from c in dc.Events
where c.TourDate == date
select new AEvent()
{
TourID = c.TourID.ToString(),
TourDate = c.TourDate,
TourGuide = c.TourGuide,
IsOn = c.IsOn==1?true:false
}
).ToList<AEvent>();
return rows;
}
}
public bool UpdateEmailSent(string userid, string first, string last, string tourid, DateTime tourdate, bool sentEmail)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
Registration row = (from c in dc.Registrations
where (c.UserID.ToString()== userid &&
c.RegFirstName == first &&
c.RegLastName == last &&
c.TourID.ToString() == tourid &&
c.TourDate == tourdate
)
select c).FirstOrDefault<Registration>();
row.IsSentEmail = (byte)(sentEmail? 1 : 0);
dc.SubmitChanges();
return true;
}
}
// gets events with sorting by guide or date
public List<AEvent> GetEventsByEventField(string sort)
{
List<string> months = new List<string>{"January", "February", "March", "April", "May", "June",
"July", "August", "September", "October", "November", "December"};
int month = 0;
if(months.Contains(sort))
{
month = DateTime.Parse("1." + sort + " 1900").Month;
}
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
List<AEvent> rows = (from events in dc.Events
where events.TourGuide == sort || events.TourDate.Month == month
join tours in dc.Tours on events.TourID equals tours.TourID
orderby events.TourDate
select new AEvent()
{
TourName = tours.TourName,
TourID = events.TourID.ToString(),
TourDate = events.TourDate,
TourOriginalDate = events.TourDate,
TourGuide = events.TourGuide,
IsOn = events.IsOn==1? true:false
}
).ToList<AEvent>();
return rows;
}
}
public bool DeleteEvent(string tourid, DateTime tourdate)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
Event row = (from c in dc.Events
where (c.TourID.ToString() == tourid && c.TourDate == tourdate)
select c).FirstOrDefault<Event>();
dc.Events.DeleteOnSubmit(row);
// The deleted event's registrations must be deleted too. Otherwise, the sql servers throws an
// exeption.
List<Registration> registrations= (from r in dc.Registrations
where (r.TourID.ToString() == tourid && r.TourDate == tourdate)
select r).ToList();
foreach(Registration reg in registrations)
{
dc.Registrations.DeleteOnSubmit(reg);
}
dc.SubmitChanges();
return true;
}
}
public bool DeleteReg(string id)
{
using (DataClassesTourGuideDataContext dc = new DataClassesTourGuideDataContext())
{
Registration row = (from c in dc.Registrations
where (c.RegID.ToString() == id)
select c).FirstOrDefault<Registration>();
dc.Registrations.DeleteOnSubmit(row);
dc.SubmitChanges();
return true;
}
}
