public IHttpActionResult InsertCompetitor(Competitor competitor)
{
PenOCDataContext db = new PenOCDataContext();
try
{
tblCompetitor competitorRecord = new tblCompetitor
{
strFirstName = competitor.firstName,
strSurname = competitor.surname,
intGender = competitor.genderId,
};
db.tblCompetitors.InsertOnSubmit(competitorRecord);
db.SubmitChanges();
competitor.id = competitorRecord.idCompetitor;
return Ok(competitor);
}
catch(Exception e)
{
return BadRequest(e.Message);
}
}
public IHttpActionResult InsertOEvent(OEvent oevent)
{
PenOCDataContext db = new PenOCDataContext();
tblEvent eventRecord = new tblEvent
{
strName = oevent.name,
strControllerReport = oevent.controllerReport,
intVenue = oevent.venueId,
strCourses = oevent.courses,
dteDate = oevent.date,
strDirections = oevent.directions,
intPlanner = oevent.plannerId,
intController = oevent.controllerId,
strRegTime = oevent.registrationTime,
strStarts = oevent.startTime,
strClose = oevent.close,
strSpecialNote = oevent.specialNote,
decCoordinateLat = oevent.coordinateLatitude,
decCoordinateLong = oevent.coordinateLongitude,
strCost = oevent.cost,
intMaxPoints = oevent.maxPoints,
strPlannerReport = oevent.plannerReport,
intOrganisingClub = oevent.organizingClubId
};
db.tblEvents.InsertOnSubmit(eventRecord);
db.SubmitChanges();
oevent.id = eventRecord.idEvent;
return Ok(oevent);
}
public IHttpActionResult UpdateResult(int courseId, Result[] courseResults)
{
PenOCDataContext db = new PenOCDataContext();
IQueryable<tblResult> queryResults = db.tblResults.Where(r => r.intCourse == courseId);
db.tblResults.DeleteAllOnSubmit(queryResults);
foreach (Result courseResult in courseResults) {
tblResult resultRecord = new tblResult
{
intCourse = courseId,
intPosition = courseResult.position,
intCompetitor = courseResult.competitorId,
intClub = courseResult.clubId,
intCategory = courseResult.categoryId,
strRaceNumber = courseResult.raceNumber,
dteTime = courseResult.time,
intPoints = courseResult.points,
blnDisqualified = courseResult.disqualified,
strComment = courseResult.comment
};
db.tblResults.InsertOnSubmit(resultRecord);
db.SubmitChanges();
};
return Ok();
}
public IHttpActionResult GetOEvents(int idEvent)
{
PenOCDataContext db = new PenOCDataContext();
IQueryable<OEvent> queryEvents = QueryOEvents().Where(@event => @event.id == idEvent);
return Ok(queryEvents.Take(1));
}
public IHttpActionResult DeleteVenue(int venueId)
{
PenOCDataContext db = new PenOCDataContext();
IQueryable<tblVenue> queryResults = db.tblVenues.Where(venue => venue.idVenue == venueId);
db.tblVenues.DeleteAllOnSubmit(queryResults);
db.SubmitChanges();
return Ok();
}
public IHttpActionResult DeleteClub(int clubId)
{
PenOCDataContext db = new PenOCDataContext();
IQueryable<lutClub> queryResults = db.lutClubs.Where(club => club.idClub == clubId);
db.lutClubs.DeleteAllOnSubmit(queryResults);
db.SubmitChanges();
return Ok();
}
public IHttpActionResult DeleteCourseResults(int idCourse)
{
PenOCDataContext db = new PenOCDataContext();
IQueryable<tblResult> queryResults = db.tblResults.Where(r => r.intCourse == idCourse);
db.tblResults.DeleteAllOnSubmit(queryResults);
// Ask the DataContext to save all the changes.
db.SubmitChanges();
return Ok();
}
public IHttpActionResult UpdateVenue(Venue venue)
{
PenOCDataContext db = new PenOCDataContext();
tblVenue venueRecord = db.tblVenues.Single(v => v.idVenue == venue.id);
venueRecord.strName = venue.name;
db.SubmitChanges();
return Ok(venue);
}
private static IQueryable<NewsItem> QueryNewsItem()
{
PenOCDataContext db = new PenOCDataContext();
return (from newsItem in db.tblNews
select new NewsItem
{
id = newsItem.idNews,
date = newsItem.dteDate,
title = newsItem.strTitle,
news = newsItem.strNews
}).OrderByDescending(newsItem => newsItem.date);
}
public IHttpActionResult UpdateClub(Club club)
{
PenOCDataContext db = new PenOCDataContext();
lutClub clubRecord = db.lutClubs.Single(c => c.idClub == club.id);
clubRecord.strFullName = club.fullName;
clubRecord.strShortName = club.shortName;
db.SubmitChanges();
return Ok(club);
}
public IHttpActionResult GetCategories()
{
PenOCDataContext db = new PenOCDataContext();
var categories = from category in db.lutCategories
select new LookupValue
{
name = category.strCategory,
id = category.idCategory
};
return Ok(categories);
}
public IHttpActionResult GetTechnicalDifficulties()
{
PenOCDataContext db = new PenOCDataContext();
var difficulties = from difficulty in db.lutTechnicals
select new LookupValue
{
name = difficulty.strTechnical,
id = difficulty.idTechnical
};
return Ok(difficulties);
}
public IHttpActionResult GetVenues()
{
PenOCDataContext db = new PenOCDataContext();
var venues = from venue in db.tblVenues
select new LookupValue
{
name = venue.strName,
id = venue.idVenue
};
return Ok(venues);
}
public IHttpActionResult GetVenues()
{
PenOCDataContext db = new PenOCDataContext();
var genders = from gender in db.lutGenders
select new LookupValue
{
name = gender.strGender,
id = gender.idGender
};
return Ok(genders);
}
public IHttpActionResult UpdateNewsItem(NewsItem newsItem)
{
PenOCDataContext db = new PenOCDataContext();
tblNews newsItemRecord = db.tblNews.Single(n => n.idNews == newsItem.id);
newsItemRecord.dteDate = newsItem.date;
newsItemRecord.strTitle = newsItem.title;
newsItemRecord.strNews = newsItem.news;
db.SubmitChanges();
return Ok(newsItem);
}
public IHttpActionResult DeleteCourse(int courseId)
{
PenOCDataContext db = new PenOCDataContext();
IQueryable<tblResult> queryResults = db.tblResults.Where(result => result.intCourse == courseId);
db.tblResults.DeleteAllOnSubmit(queryResults);
IQueryable<tblCourse> queryCourses = db.tblCourses.Where(course => course.idCourse == courseId);
db.tblCourses.DeleteAllOnSubmit(queryCourses);
db.SubmitChanges();
return Ok();
}
public IHttpActionResult GetClubs()
{
PenOCDataContext db = new PenOCDataContext();
var clubs = from club in db.lutClubs
select new Club
{
fullName = club.strFullName,
shortName = club.strShortName,
id = club.idClub
};
return Ok(clubs);
}
public IHttpActionResult InsertVenue(Venue venue)
{
PenOCDataContext db = new PenOCDataContext();
tblVenue venueRecord = new tblVenue
{
strName = venue.name,
};
db.tblVenues.InsertOnSubmit(venueRecord);
db.SubmitChanges();
venue.id = venueRecord.idVenue;
return Ok(venue);
}
public IHttpActionResult InsertClub(Club club)
{
PenOCDataContext db = new PenOCDataContext();
lutClub clubRecord = new lutClub
{
strFullName = club.fullName,
strShortName = club.shortName
};
db.lutClubs.InsertOnSubmit(clubRecord);
db.SubmitChanges();
club.id = clubRecord.idClub;
return Ok(club);
}
public IHttpActionResult InsertNewsItem(NewsItem newsItem)
{
PenOCDataContext db = new PenOCDataContext();
tblNews newsItemRecord = new tblNews
{
dteDate = newsItem.date,
strTitle = newsItem.title,
strNews = newsItem.news
};
db.tblNews.InsertOnSubmit(newsItemRecord);
db.SubmitChanges();
newsItem.id = newsItemRecord.idNews;
return Ok(newsItem);
}
public IHttpActionResult UpdateResult(Result result)
{
PenOCDataContext db = new PenOCDataContext();
tblResult resultRecord = db.tblResults.Single(r => r.intCourse == result.courseId && r.intCompetitor == result.competitorId);
resultRecord.intCategory = result.categoryId;
resultRecord.intClub = result.clubId;
resultRecord.intPoints = result.points;
resultRecord.intPosition = result.position;
resultRecord.strComment = result.comment;
resultRecord.strRaceNumber = result.raceNumber;
resultRecord.blnDisqualified = result.disqualified;
resultRecord.dteTime = result.time;
db.SubmitChanges();
return Ok(result);
}
public IHttpActionResult InsertCourse(Course course)
{
PenOCDataContext db = new PenOCDataContext();
tblCourse courseRecord = new tblCourse
{
strName = course.name,
intEvent = course.eventId,
intLength = course.length,
intClimb = course.climb,
intControls = course.controls,
intTechnical = course.difficultyId,
intListOrder = course.listOrder
};
db.tblCourses.InsertOnSubmit(courseRecord);
db.SubmitChanges();
course.id = courseRecord.idCourse;
return Ok(course);
}
public IHttpActionResult UpdateCompetitor(Competitor competitor)
{
PenOCDataContext db = new PenOCDataContext();
tblCompetitor competitorRecord = db.tblCompetitors.Single(c => c.idCompetitor == competitor.id);
competitorRecord.strFirstName = competitor.firstName;
competitorRecord.strSurname = competitor.surname;
competitorRecord.intGender = competitor.genderId;
competitorRecord.intCategory = competitor.categoryId;
competitorRecord.intEmitNumber = competitor.emitNumber;
competitorRecord.strEmail = competitor.email;
competitorRecord.strTelephone1 = competitor.telephone1;
competitorRecord.strTelephone2 = competitor.telephone2;
db.SubmitChanges();
return Ok(competitor);
}
public IHttpActionResult UpdateOEvent(OEvent oevent)
{
PenOCDataContext db = new PenOCDataContext();
tblEvent eventRecord = db.tblEvents.Single(e => e.idEvent == oevent.id);
eventRecord.strName = oevent.name;
eventRecord.intVenue = oevent.venueId;
eventRecord.strCourses = oevent.courses;
eventRecord.dteDate = oevent.date;
eventRecord.strDirections = oevent.directions;
eventRecord.intPlanner = oevent.plannerId;
eventRecord.intController = oevent.controllerId;
eventRecord.strRegTime = oevent.registrationTime;
eventRecord.strStarts = oevent.startTime;
eventRecord.strClose = oevent.close;
eventRecord.strSpecialNote = oevent.specialNote;
eventRecord.decCoordinateLat = oevent.coordinateLatitude;
eventRecord.decCoordinateLong = oevent.coordinateLongitude;
eventRecord.strCost = oevent.cost;
eventRecord.intMaxPoints = oevent.maxPoints;
eventRecord.strPlannerReport = oevent.plannerReport;
eventRecord.strControllerReport = oevent.controllerReport;
eventRecord.intOrganisingClub = oevent.organizingClubId;
db.SubmitChanges();
return Ok(oevent);
}
//---------------------------------------------------------------------------------
private static IQueryable<Result> QueryResults()
{
PenOCDataContext db = new PenOCDataContext();
return (from result in db.tblResults
orderby result.intPosition
select new Result
{
courseId = result.intCourse,
competitorId = result.intCompetitor,
competitor = result.tblCompetitor.strReadOnlyFullName,
categoryId = result.intCategory,
clubId = result.intClub,
comment = result.strComment,
disqualified = result.blnDisqualified,
points = result.intPoints,
position = result.intPosition,
raceNumber = result.strRaceNumber,
time = result.dteTime
});
}
//---------------------------------------------------------------------------------
private static IQueryable<Competitor> QueryCompetitors()
{
PenOCDataContext db = new PenOCDataContext();
return (from competitor in db.tblCompetitors
orderby competitor.strReadOnlyFullName
select new Competitor
{
id = competitor.idCompetitor,
firstName = competitor.strFirstName,
surname = competitor.strSurname,
fullName = competitor.strReadOnlyFullName,
gender = competitor.lutGender.strGender,
genderId = competitor.intGender,
category = competitor.lutCategory.strCategory,
categoryId = competitor.intCategory,
emitNumber = competitor.intEmitNumber,
email = competitor.strEmail,
telephone1=competitor.strTelephone1,
telephone2=competitor.strTelephone2
});
}
public IHttpActionResult UpdateCourse(Course course)
{
PenOCDataContext db = new PenOCDataContext();
tblCourse theCourse = db.tblCourses.Single(c => c.idCourse == course.id);
theCourse.intEvent = course.eventId;
theCourse.strName = course.name;
theCourse.intLength = course.length;
theCourse.intClimb = course.climb;
theCourse.intControls = course.controls;
theCourse.intTechnical = course.difficultyId;
theCourse.intListOrder = course.listOrder;
db.SubmitChanges();
return Ok(course);
}
//---------------------------------------------------------------------------------
private static IQueryable<Course> QueryCourses()
{
PenOCDataContext db = new PenOCDataContext();
return (from course in db.tblCourses
orderby course.intListOrder
select new Course
{
id = course.idCourse,
eventId = course.intEvent,
name = course.strName,
length = course.intLength,
climb = course.intClimb,
controls = course.intControls,
difficultyId = course.intTechnical,
difficulty = course.lutTechnical.strTechnical,
listOrder = course.intListOrder
});
}
//---------------------------------------------------------------------------------
private static IQueryable<OEvent> QueryOEvents()
{
PenOCDataContext db = new PenOCDataContext();
return (from @event in db.tblEvents
select new OEvent
{
id = @event.idEvent,
date = @event.dteDate,
name = @event.strName,
venue = @event.tblVenue.strName,
venueId = @event.intVenue,
courses = @event.strCourses,
plannerId = @event.intPlanner,
planner = @event.tblCompetitorPlanner.strReadOnlyFullName,
controllerId = @event.intController,
controller = @event.tblCompetitorController.strReadOnlyFullName,
plannerReport = @event.strPlannerReport,
controllerReport = @event.strControllerReport,
specialNote = @event.strSpecialNote,
registrationTime = @event.strRegTime,
startTime = @event.strStarts,
close = @event.strClose,
directions = @event.strDirections,
maxPoints = @event.intMaxPoints,
organizingClubId = @event.intOrganisingClub,
organizingClub = @event.lutClub.strShortName,
cost = @event.strCost,
coordinateLatitude = @event.decCoordinateLat,
coordinateLongitude = @event.decCoordinateLong
}).OrderBy(@event => @event.date); //
}
