public int capacities(string lower, string upper, string campus)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var query = from acc in db.HOTELs
where acc.NEAREST_TOWN.Equals(campus.ToUpper())
select new Hotel
{
Capacity = acc.CAPACITY
};
int count = 0;
int lowerBound = Convert.ToInt32(lower);
int upperBound = Convert.ToInt32(upper);
foreach (Hotel acc in query)
{
if (acc.Capacity >= lowerBound && acc.Capacity < upperBound)
{
count++;
}
}
return(count);
}
}
catch (Exception)
{
return(0);
}
}
public Manager updateOfficer(string id, Manager off)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var query = (from acc in db.HOTEL_MANAGERs where acc.MANAGER_ID.Equals(Convert.ToInt32(id)) select acc);
if (query.Count() == 1)
{
HOTEL_MANAGER acc = query.Single();
off = new Manager()
{
EmployeeNumber = acc.EMPLOYEE_NUM,
CityID = acc.CITY_ID,
ContactNumber = acc.CONTACT_NUM,
Email = acc.EMAIL,
Gender = acc.GENDER,
Name = acc.FULL_NAMES,
Surname = acc.SURNAME,
Title = acc.TITLE,
};
db.SubmitChanges();
return(off);
}
else
{
return(null);
}
}
}
catch (Exception)
{
return(null);
}
}
public double getAccommoAvgRatings(string accommoId)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var query = (from rating in db.CLIENT_HOTEL_RATINGs
where rating.HOTEL_ID.Equals(Convert.ToInt32(accommoId))
select rating.RATING_VALUE);
if (query.Count() != 0)
{
int totAccommoRating = query.Sum();
int count = query.Count();
double avgRating = totAccommoRating / count;
return(avgRating);
}
return(-1.00);
}
}
catch (Exception)
{
return(-1.00);
}
}
public List <ImageFile> getImagesByCategory(string category)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
return(db.INSPEC_IMAGEs.Where(image => image.CATEGORY_ID.Equals(category)).Select(image => new ImageFile
{
ImageId = image.IMAGE_ID,
CategoryId = image.CATEGORY_ID,
ImageName = image.IMAGE_NAME,
InspectionId = image.INSPEC_ID,
ImageCategory = image.FILE_CATEGORY,
ContentType = image.CONTENT_TYPE,
FileSize = image.FILE_SIZE,
//Data = image.DATA.ToArray(),
DateUploaded = image.DATE_UPLOADED.ToString(),
Location = image.LOCATION
}).ToList());
}
}
catch (Exception)
{
return(null);
}
}
public string removeAdmin(string id)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var query = (from acc in db.HOTEL_MANAGERs where acc.MANAGER_ID.Equals(Convert.ToInt32(id)) select acc);
if (query.Count() == 1)
{
HOTEL_MANAGER student = query.Single();
if (student.AUTHENTICATION_LEVEL == "A")
{
student.AUTHENTICATION_LEVEL = "S";
db.SubmitChanges();
return("Success Update Successful");
}
else
{
return("Failed");
}
}
else
{
return("Failed Error User Not Found");
}
}
}
catch (Exception)
{
return("Failed Update Failed");
}
}
public List <Category> getCategories()
{
List <Category> catList = null;
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var query = from cat in db.CATEGORies
select new Category
{
CategoryId = cat.CATEGORY_ID,
CategoryName = cat.CATEGORY_NAME
};
catList = new List <Category>();
foreach (Category acc in query)
{
catList.Add(acc);
}
return(catList);
}
}
catch (Exception)
{
return(null);
}
}
//Work on the inner join when I get home
public HotelInspection getFullAccommoInspec(string accommoId)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var query = from acc in db.HOTELs
where acc.HOTEL_ID.Equals(Convert.ToInt32(accommoId))
join accApp in db.CYCLE_APPLICATIONs on acc.HOTEL_ID equals accApp.HOTEL_ID
join accInsp in db.HOTEL_INSPECTIONs on accApp.APPLICATION_ID equals accInsp.APPLICATION_ID
join inspDet in db.INSPEC_ADD_DETAILs on accInsp.INSPEC_ID equals inspDet.INSPEC_ID
select new HotelInspection
{
ApplicationId = accInsp.APPLICATION_ID,
OfficerId = accInsp.MANAGER_ID,
InspecId = accInsp.INSPEC_ID,
InspecDate = accInsp.INSPEC_DATE.ToString(),
InspecOutcome = accInsp.INSPEC_RESULT,
};
foreach (HotelInspection acc in query)
{
return(acc);
}
return(null);
}
}
catch (Exception)
{
return(null);
}
}
public Owner getOwnerById(string id)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
return(db.HOTEL_OWNERs.Where(person => person.OWNER_ID == Convert.ToInt32(id)
).Select(person => new Owner
{
ID = person.OWNER_ID,
ServiceProviderNumber = person.SERVICE_PROVIDER_NUM,
Name = person.FULL_NAMES,
Surname = person.SURNAME,
Gender = person.GENDER,
Email = person.EMAIL,
ContactNumber = person.CONTACT_NUM,
// Password = person.PASSWORD,
AuthenticationLevel = person.AUTHENTICATION_LEVEL,
Title = person.TITLE
}).First());
}
}
catch (Exception)
{
return(null);
}
}
public string RegisterClient(Client client)
{
//Using the using keyword ensures that the connection is closed automatically
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
CLIENT clientLinq = null;
try
{
int clientLinqTest = (from uStud in db.CLIENTs where uStud.EMAIL.Equals(client.Email) select uStud).Count();
if (clientLinqTest == 0)
{
clientLinq = ConvertToLinq.ConvertStudentToLinqObject(client);
db.CLIENTs.InsertOnSubmit(clientLinq);
db.SubmitChanges();
return("Success Regristration Successful");
}
else if (clientLinqTest != 0)
{
return("Failed Username already exists");
}
}
catch (Exception e)
{
return(e.GetBaseException().ToString());//"Registration failed, contact admin";
}
}
return("Why does this keeps happening???");
}
public Owner updateOwner(string id, Owner owner)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var query = (from acc in db.HOTEL_OWNERs where acc.OWNER_ID.Equals(Convert.ToInt32(id)) select acc);
if (query.Count() == 1)
{
HOTEL_OWNER acc = query.Single();
owner = new Owner()
{
ServiceProviderNumber = acc.SERVICE_PROVIDER_NUM,
ContactNumber = acc.CONTACT_NUM,
Email = acc.EMAIL,
Gender = acc.GENDER,
Name = acc.FULL_NAMES,
Surname = acc.SURNAME,
Title = acc.TITLE,
};
db.SubmitChanges();
return(owner);
}
else
{
return(null);
}
}
}
catch (Exception)
{
return(null);
}
}
public OwnersCompany updateCompany(string id, OwnersCompany comp)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var query = (from acc in db.OWNERS_COMPANies where acc.COMP_ID.Equals(Convert.ToInt32(id)) select acc);
if (query.Count() == 1)
{
OWNERS_COMPANY acc = query.Single();
comp = new OwnersCompany()
{
ContactNum = acc.CONTACT_NUM,
Email = acc.EMAIL,
CompanyName = acc.NAME,
RegNum = acc.REG_NUM
};
db.SubmitChanges();
return(comp);
}
else
{
return(null);
}
}
}
catch (Exception)
{
return(null);
}
}
public List <Owner> getAllOwners()
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
return(db.HOTEL_OWNERs.Select(off => new Owner
{
ID = off.OWNER_ID,
ServiceProviderNumber = off.SERVICE_PROVIDER_NUM,
Name = off.FULL_NAMES,
Surname = off.SURNAME,
Gender = off.GENDER,
Email = off.EMAIL,
ContactNumber = off.CONTACT_NUM,
//Password = off.PASSWORD,
AuthenticationLevel = off.AUTHENTICATION_LEVEL,
Title = off.TITLE
}).ToList());
}
}
catch (Exception)
{
return(null);
}
}
public List <OwnersCompany> getCompByOwnerId(string ownerId)
{
List <OwnersCompany> compList = null;
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var innerJoinQuery =
from owner in db.HOTEL_OWNERs where owner.OWNER_ID.Equals(Convert.ToInt32(ownerId))
join comp in db.OWNERS_COMPANies on owner.OWNER_ID equals comp.COMP_ID
select new OwnersCompany
{
CompanyName = comp.NAME,
CompId = comp.COMP_ID,
OwnerId = comp.COMP_ID,
ContactNum = comp.CONTACT_NUM,
Email = comp.EMAIL,
RegNum = comp.REG_NUM
};
compList = new List <OwnersCompany>();
foreach (OwnersCompany com in innerJoinQuery)
{
compList.Add(com);
}
return(compList);
}
}
catch (Exception)
{
return(null);
}
}
public int nonAccommdatedCount(string campus)
{
int campusID = 0;
if (campus.ToUpper() == "JHB")
{
campusID = 2;
}
if (campus.ToUpper() == "PTA")
{
campusID = 3;
}
if (campus.ToUpper() == "DBN")
{
campusID = 4;
}
if (campus.ToUpper() == "CPT")
{
campusID = 5;
}
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
int countAllStud = (from stud in db.CLIENTs where stud.CITY_ID.Equals(campusID) select stud).Count();
return(countAllStud - accommdatedCount(campus));
}
}
catch (Exception)
{
return(0);
}
}
public string insertCategories(List <Category> cat)
{
CATEGORY catLinq = null;
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
foreach (Category category in cat)
{
catLinq = new CATEGORY()
{
CATEGORY_NAME = category.CategoryName,
};
db.CATEGORies.InsertOnSubmit(catLinq);
}
db.SubmitChanges();
return("Success Insertion Successful");
}
}
catch (Exception)
{
return("Failed Insertion Failed");
}
}
public string RegisterManager(Manager manager)
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
HOTEL_MANAGER officerLinq = null;
try
{
int officerLinqTest = (from uOfficer in db.HOTEL_MANAGERs where uOfficer.EMAIL.Equals(manager.Email) select uOfficer).Count();
if (officerLinqTest == 0)
{
officerLinq = ConvertToLinq.ConvertOfficerToLinqObject(manager);
db.HOTEL_MANAGERs.InsertOnSubmit(officerLinq);
db.SubmitChanges();
return("Success Regristration Successful");
}
else if (officerLinqTest != 0)
{
return("Failed Username already exists");
}
}
catch (Exception)
{
return("Failed Registration failed, contact admin");
}
}
return("Failed Registration failed, contact admin");
}
public ImageFile getImageById(string fileId)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
return(db.INSPEC_IMAGEs.Where(image => image.IMAGE_ID.Equals(Convert.ToInt32(fileId))).Select(image => new ImageFile
{
ImageId = image.IMAGE_ID,
CategoryId = image.CATEGORY_ID,
ImageName = image.IMAGE_NAME,
InspectionId = image.INSPEC_ID,
ImageCategory = image.FILE_CATEGORY,
ContentType = image.CONTENT_TYPE,
FileSize = image.FILE_SIZE,
DateUploaded = image.DATE_UPLOADED.ToString(),
Location = image.LOCATION
}).First());
}
}
catch (Exception)
{
return(null);
}
}
public Address getAddressById(string id)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var innerJoinQuery =
from address in db.ADDRESSes
where address.ADDRESS_ID.Equals(Convert.ToInt32(id))
select new Address
{
AddressID = address.ADDRESS_ID,
Street = address.STREET,
City = address.CITY,
Town = address.TOWN,
PostalCode = address.POSTAL_CODE,
Lattitude = address.LATITUDE,
Longitude = address.LONGTUDE,
InfoUrl = address.INFO_URL
};
foreach (Address ca in innerJoinQuery)
{
return(ca);
}
return(null);
}
}
catch (Exception)
{
return(null);
}
}
public List <HotelInspection> getInspecsByDate(string date)
{
List <HotelInspection> catList = null;
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var query = from accInspec in db.HOTEL_INSPECTIONs
where accInspec.INSPEC_DATE.Equals(Convert.ToDateTime(date.ToString()))
select new HotelInspection
{
ApplicationId = accInspec.APPLICATION_ID,
OfficerId = accInspec.MANAGER_ID,
InspecId = accInspec.INSPEC_ID,
InspecDate = accInspec.INSPEC_DATE.ToString(),
InspecOutcome = accInspec.INSPEC_RESULT,
};
catList = new List <HotelInspection>();
foreach (HotelInspection acc in query)
{
catList.Add(acc);
}
return(catList);
}
}
catch (Exception)
{
return(null);
}
}
public string getStringToMap(string campusId, string accommoId)
{
ADDRESS fromAddressObj = null;
ADDRESS toAddressObj = null;
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var innJoin1 =
from campus in db.CITies
where campus.CITY_ID.Equals(Convert.ToInt32(campusId))
join address in db.ADDRESSes on campus.ADDRESS_ID equals address.ADDRESS_ID
select address;
var innJoin2 =
from acc in db.HOTELs
where acc.HOTEL_ID.Equals(Convert.ToInt32(accommoId))
join address in db.ADDRESSes on acc.ADDRESS_ID equals address.ADDRESS_ID
select address;
fromAddressObj = innJoin1.Single();
toAddressObj = innJoin2.Single();
string fromAddress = fromAddressObj.STREET + " " + fromAddressObj.TOWN + " " + fromAddressObj.CITY;
string toAddress = toAddressObj.STREET + " " + toAddressObj.TOWN + " " + toAddressObj.CITY;
return(fromAddress + " to " + toAddress);
}
}
catch (Exception)
{
return("Failed Cannot map to and from addresses");
}
}
public string changeInspecResultJason(string inspecId, string result)
{
HOTEL_INSPECTION inspec = null;
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var query = from accInspec in db.HOTEL_INSPECTIONs
where accInspec.INSPEC_ID.Equals(Convert.ToInt32(inspecId))
select accInspec;
inspec = query.Single();
inspec.INSPEC_RESULT = result;
db.SubmitChanges();
return("Success Update Successful");
}
}
catch (Exception)
{
return("Failed Update Failed");
}
}
public List <ApplicationFile> getFilesForAccommo(string applicationId)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
return(db.APPLICATION_FILEs.Where(file => file.APPLICATION_ID.Equals(Convert.ToInt32(applicationId))).Select(file => new ApplicationFile
{
FileId = file.FILES_ID,
ApplicationId = file.APPLICATION_ID,
FileName = file.NAME,
FileCategory = file.FILE_CATEGORY,
ContentType = file.CONTENT_TYPE,
FileSize = file.FILE_SIZE,
//data = file.DATA.ToArray(),
DateUploaded = file.DATE_UPLOADED.ToString(),
Location = file.LOCATION
}).ToList());
}
}
catch (Exception)
{
return(null);
}
}
public List <Manager> getAllOfficers()
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
return(db.HOTEL_MANAGERs.Select(off => new Manager
{
ID = off.MANAGER_ID,
CityID = off.CITY_ID,
EmployeeNumber = off.EMPLOYEE_NUM,
Name = off.FULL_NAMES,
Surname = off.SURNAME,
Gender = off.GENDER,
Email = off.EMAIL,
ContactNumber = off.CONTACT_NUM,
//Password = off.PASSWORD,
AuthenticationLevel = off.AUTHENTICATION_LEVEL,
Title = off.TITLE
}).ToList());
}
}
catch (Exception)
{
return(null);
}
}
public string updateAccredApplication(CycleApplications application)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var query = from accredApp in db.CYCLE_APPLICATIONs
where accredApp.HOTEL_ID.Equals(Convert.ToInt32(application.HotelId)) &&
accredApp.APPLICATION_ID.Equals(Convert.ToInt32(application.ApplicationId))
select accredApp;
CYCLE_APPLICATION updatedApp = new CYCLE_APPLICATION();
updatedApp = query.Single();
updatedApp.APP_STATUS = application.ApplicationStatus;
updatedApp.REASON = application.Reason;
db.SubmitChanges();
return("Success Update Successful");
}
}
catch (Exception)
{
return("Failed Update Failed");
}
}
public Manager getOffficerById(string id)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
return(db.HOTEL_MANAGERs.Where(person => person.MANAGER_ID == Convert.ToInt32(id)
).Select(person => new Manager
{
ID = person.MANAGER_ID,
CityID = person.CITY_ID,
EmployeeNumber = person.EMPLOYEE_NUM,
Name = person.FULL_NAMES,
Surname = person.SURNAME,
Gender = person.GENDER,
Email = person.EMAIL,
ContactNumber = person.CONTACT_NUM,
//Password = person.PASSWORD,
AuthenticationLevel = person.AUTHENTICATION_LEVEL,
Title = person.TITLE
}).First());
}
}
catch (Exception)
{
return(null);
}
}
public string insertQuestions(List <InpecQuestions> inspecQ)
{
INSPECTION_QUESTION questionLinq = null;
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
foreach (InpecQuestions question in inspecQ)
{
questionLinq = new INSPECTION_QUESTION()
{
CATEGORY_ID = question.CategoryId,
QUESTION = question.Question,
REQUIRES_PIC = question.RequiresPicture
};
db.INSPECTION_QUESTIONs.InsertOnSubmit(questionLinq);
}
db.SubmitChanges();
return("Success Insertion Successful");
}
}
catch (Exception)
{
return("Failed Insertion Failed");
}
}
public int rememberRating(string studId, string accommoId)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
var query = (from rate in db.CLIENT_HOTEL_RATINGs where rate.CLIENT_ID.Equals(Convert.ToInt32(studId)) && rate.HOTEL_ID.Equals(Convert.ToInt32(accommoId)) select rate);
if (query.Count() == 1)
{
CLIENT_HOTEL_RATING tbl = query.Single();
return(tbl.RATING_VALUE);
}
else
{
}
return(-1);
}
}
catch (Exception)
{
return(-1);
}
}
public string insertEvaluationComMembers(List <EvaluationCommitte> evalCommitte)
{
EVALUATION_COMM comm = null;
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
foreach (EvaluationCommitte person in evalCommitte)
{
comm = new EVALUATION_COMM()
{
INSPEC_ID = person.InspecId,
IDENTIFICATION = person.Identification,
NAME = person.Name,
SURNAME = person.Surname,
OCCUPATION = person.Occupation,
COMPANY = person.Oraganisation
};
db.EVALUATION_COMMs.InsertOnSubmit(comm);
}
db.SubmitChanges();
return("Success Insertion Successful");
}
}
catch (Exception)
{
return("Failed Insertion Failed");
}
}
public List <Hotel> getToFiveRatedAccommos()
{
try
{
List <Hotel> accList = new List <Hotel>();
Hotel accommo = null;
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
foreach (HOTEL acc in db.HOTELs)
{
accommo = new Hotel()
{
HotelName = acc.NAME,
NearestCity = acc.NEAREST_TOWN,
HotelID = acc.HOTEL_ID,
avgHotelRating = Convert.ToInt32(getAccommoAvgRatings(acc.HOTEL_ID.ToString())),
numRatings = getAccommoTotalUsrRatings(acc.HOTEL_ID.ToString()),
HotelMainImage = files.getMainImageByAccommoId(acc.HOTEL_ID.ToString()),
};
accList.Add(accommo);
}
return(accList);
}
}
catch (Exception)
{
return(null);
}
}
public int accommoListInDistanceRange(string lower, string upper, string campus)
{
try
{
using (HotelManagementServerDataContext db = new HotelManagementServerDataContext())
{
int count = 0;
int lowerBound = Convert.ToInt32(lower);
int upperBound = Convert.ToInt32(upper);
var query = from acc in db.HOTELs
where acc.NEAREST_TOWN.Equals(campus.ToUpper())
select new Hotel
{
NearestCity = acc.NEAREST_TOWN,
Distance = Convert.ToDecimal(acc.DISTANCE_FROM_TOWN),
};
foreach (Hotel accommo in query)
{
if (accommo.Distance >= lowerBound && accommo.Distance < upperBound)
{
count++;
}
}
return(count);
}
}
catch (Exception)
{
return(0);
}
}