/* PopulateMain will firstly populate the necessary rows (which is the facility ID determine by activity)
* Then for each row, the obj behind each selection element (the labelled 1 to 6 buttons) will be generated.
* Then base on the availability table, the button will be disabled for those unavailable timeslot
*/
private void PopulateMain(string Activity, DateTime Date)
{
clearows();
List <FacilityInformation> FindAllFacility = new List <FacilityInformation>();
List <AvailabilityCheck> AvailList = new List <AvailabilityCheck>();
int Maxcount = 0;
int Maxcount2 = 0;
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
var queryFacility = from x in context.FacilityInformations where x.Activity == Activity select x;
FindAllFacility = queryFacility.ToList <FacilityInformation>();
Maxcount = FindAllFacility.Count <FacilityInformation>();
var BookingInitialisation = from x in context.BookingTableInitialisations select x;
TransactionList = BookingInitialisation.ToList <BookingTableInitialisation>();
var AvailQuery = from x in context.AvailabilityChecks where x.BookingDate == Date select x;
AvailList = AvailQuery.ToList <AvailabilityCheck>();
Maxcount2 = AvailList.Count <AvailabilityCheck>();
}
catch
{
MessageBox.Show("Connection fail. Please try again.");
}
PopulateRows(Maxcount, FindAllFacility);
GenerateTimeslotTransInfo(Maxcount, FindAllFacility, Maxcount2, AvailList);
BlockOutUnavailable(Maxcount);
((MAIN)MdiParent).UpdateStatusLabel("Booking initialised");
}
private void buttonFacilityInformation_Click(object sender, EventArgs e)
{
SA46Team09BEntities context = new SA46Team09BEntities();
FacilitiesInfo CR = new FacilitiesInfo();
CR.SetDataSource(context.FacilityInformations);
crystalReportViewerReport.ReportSource = CR;
((MAIN)MdiParent).UpdateStatusLabel("Report Generated");
}
private void buttonMemberList_Click(object sender, EventArgs e)
{
SA46Team09BEntities context = new SA46Team09BEntities();
MemberManagement CR = new MemberManagement();
CR.SetDataSource(context.Members);
crystalReportViewerReport.ReportSource = CR;
((MAIN)MdiParent).UpdateStatusLabel("Report Generated");
}
private void buttonTransaction_Click(object sender, EventArgs e)
{
SA46Team09BEntities context = new SA46Team09BEntities();
TransactionsList CR = new TransactionsList();
CR.Database.Tables[0].SetDataSource(context.Transactions);
CR.Database.Tables[1].SetDataSource(context.Members);
crystalReportViewerReport.ReportSource = CR;
((MAIN)MdiParent).UpdateStatusLabel("Report Generated");
}
private void AvailabilityPurge()
{
SA46Team09BEntities context = new SA46Team09BEntities();
DateTime mydate = DateTime.Now.Date;
var Query = from x in context.AvailabilityChecks where x.BookingDate < mydate select x;
List <AvailabilityCheck> purgelist = Query.ToList <AvailabilityCheck>();
if (purgelist.Any())
{
context.AvailabilityChecks.RemoveRange(purgelist);
}
context.SaveChanges();
}
private void buttonWeeklyUsage_Click(object sender, EventArgs e)
{
SA46Team09BEntities context = new SA46Team09BEntities();
DateTime mydate = DateTime.Now.Date;
DailyUsageRate CR = new DailyUsageRate();
var Query = from x in context.Transactions where x.CancelRef == 0 && x.BookingDateTime >= mydate select x;
List <Transaction> Translist = Query.ToList <Transaction>();
CR.Database.Tables[0].SetDataSource(context.FacilityInformations);
CR.Database.Tables[1].SetDataSource(Translist);
crystalReportViewerReport.ReportSource = CR;
((MAIN)MdiParent).UpdateStatusLabel("Report Generated");
}
// Search all button code
private void buttonViewAllFacilities_Click(object sender, EventArgs e)
{
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
var queryViewAllFacilities = from x in context.FacilityInformations select x;
List <FacilityInformation> ViewAllFacilitiesList = queryViewAllFacilities.ToList <FacilityInformation>();
dataGridViewFacilities.DataSource = ViewAllFacilitiesList;
}
catch
{
MessageBox.Show("Connection fail. Please try again.");
}
}
// [Member Form - Create Button codes]
private void CreateMember(Member NewMember)
{
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
context.Members.Add(NewMember);
context.SaveChanges();
((MAIN)MdiParent).UpdateStatusLabel("Member record successfully added");
}
catch
{
MessageBox.Show("Creation Error. NUSID probably existed. Please check again.");
((MAIN)MdiParent).UpdateStatusLabel("Error encounter in last transaction...");
}
}
// Search for all Bookings that DO NOT have a corresponding cancel entry (this is determined by the cancel ref property)
private void buttonViewAllDeleteBooking_Click(object sender, EventArgs e)
{
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
var query = from x in context.Transactions where x.TransactionType == "Book" && x.CancelRef == 0 select x;
List <Transaction> QueryAllTransaction = query.ToList <Transaction>();
dataGridViewDeleteBooking.DataSource = QueryAllTransaction;
((MAIN)MdiParent).UpdateStatusLabel("Query Completed");
}
catch
{
MessageBox.Show("Connection fail. Please try again.");
((MAIN)MdiParent).UpdateStatusLabel("Error encounter in last transaction...");
}
}
// [Member Form - Search Button codes]
private void buttonViewAllMembers_Click(object sender, EventArgs e)
{
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
var queryViewAllMembers = from x in context.Members select x;
List <Member> ViewAllMemberList = queryViewAllMembers.ToList <Member>();
dataGridViewMembers.DataSource = ViewAllMemberList;
((MAIN)MdiParent).UpdateStatusLabel("Query Completed");
}
catch
{
MessageBox.Show("Connection fail. Please try again.");
((MAIN)MdiParent).UpdateStatusLabel("Error encounter in last transaction...");
}
}
// Search By Activity
private void searchByActivity()
{
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
string useractivity = this.comboBoxActivityFacilities.Text;
var queryViewActivity = from x in context.FacilityInformations where x.Activity == useractivity select x;
List <FacilityInformation> ViewActivityList = queryViewActivity.ToList <FacilityInformation>();
dataGridViewFacilities.DataSource = ViewActivityList;
((MAIN)MdiParent).UpdateStatusLabel("Query Completed");
}
catch
{
MessageBox.Show("Connection fail. Please try again.");
((MAIN)MdiParent).UpdateStatusLabel("Error encounter in last transaction...");
}
}
// Facility Creation - actual Entity action
private void createFacility(FacilityInformation NewFacility)
{
try
{
using (SA46Team09BEntities context = new SA46Team09BEntities())
{
context.FacilityInformations.Add(NewFacility);
context.SaveChanges();
((MAIN)MdiParent).UpdateStatusLabel("Facilities record successfully added");
}
}
catch
{
MessageBox.Show("Creation Error. FacilityID probably existed. Please check again.");
((MAIN)MdiParent).UpdateStatusLabel("Error encounter in last transaction...");
}
}
// Query based on NUSID
private void SearchByNUSID(DataChecker datacheck)
{
string searchinput = this.textBoxNusIDDeleteBooking.Text.Trim().ToUpper();
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
var querySearchByNUSID = from x in context.Transactions where x.NUSID.Contains(searchinput) && x.CancelRef == 0 select x;
List <Transaction> ViewTransactionByNUSID = querySearchByNUSID.ToList <Transaction>();
dataGridViewDeleteBooking.DataSource = ViewTransactionByNUSID;
}
catch
{
MessageBox.Show("Connection fail. Please try again.");
((MAIN)MdiParent).UpdateStatusLabel("Error encounter in last transaction...");
}
}
//Delete booking codes
private void buttonDeleteBooking_Click(object sender, EventArgs e)
{
int timeslot = Convert.ToInt32(this.labelFillTimeSlotDeleteBooking.Text);
int transID = Convert.ToInt32(this.labelFillTransactionIDDeleteBooking.Text);
int TempID = 0;
//Creates an entry for Cancel booking record
Transaction DeleteTrans = new Transaction();
DeleteTrans.NUSID = this.labelFillNusIDDeleteBooking.Text;
DeleteTrans.FacilityID = this.labelFillFacilityIDDeleteBooking.Text;
DeleteTrans.Activity = this.labelFillActivityDeleteBooking.Text;
DeleteTrans.BookingDateTime = DateTime.Parse(this.labelFillBookDateDeleteBooking.Text);
DeleteTrans.TransDateTime = DateTime.Now;
DeleteTrans.TimeSlot = timeslot;
DeleteTrans.CancelRef = transID;
DeleteTrans.TransactionType = "Cancel";
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
context.Transactions.Add(DeleteTrans);
context.SaveChanges();
//This is to find and set the transaction ID for the new CANCEL entry to TempID
var RefTransQuery = from x in context.Transactions where x.CancelRef == transID select x;
List <Transaction> RefTrans = RefTransQuery.ToList <Transaction>();
TempID = RefTrans[0].TransactionID;
// This is to find and set the Cancelref with the cancel booking trans ID
// With this step done, the booking and cancellation will now have a CROSS-Reference and this can be used as a filter for refresh bookings in the cancellation screen (to ensure no records are deleted twice).
var RefTransQuery2 = from x in context.Transactions where x.TransactionID == transID select x;
List <Transaction> RefTrans2 = RefTransQuery2.ToList <Transaction>();
RefTrans2[0].CancelRef = TempID;
AvailabilityCheck DeleteAvail = (AvailabilityCheck)context.AvailabilityChecks.Where(M => M.TransactionID == transID).First();
context.AvailabilityChecks.Remove(DeleteAvail);
context.SaveChanges();
resetscreen();
((MAIN)MdiParent).UpdateStatusLabel("Delete Transaction and Availability entry successfully added");
}
catch
{
MessageBox.Show("Deletion Error. Transaction ID probably did not existed or connection errors. Please check again.");
}
}
// Search by both activity and location
private void searchByActivityLocation()
{
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
string useractivity = this.comboBoxActivityFacilities.Text;
var queryViewActivity = from x in context.FacilityInformations where x.Activity == useractivity select x;
List <FacilityInformation> ViewActivityList = queryViewActivity.ToList <FacilityInformation>();
string userlocation = this.comboBoxLocationFacilities.Text;
var queryViewLocation = from x in ViewActivityList where x.LocationTag == userlocation select x;
List <FacilityInformation> ViewActivityLocationList = queryViewLocation.ToList <FacilityInformation>();
dataGridViewFacilities.DataSource = ViewActivityLocationList;
}
catch
{
MessageBox.Show("Connection fail. Please try again.");
}
}
private void buttonSearchMembers_Click(object sender, EventArgs e)
{
string searchinput = this.textBoxNusIDMembers.Text.Trim().ToUpper();
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
var queryViewAllMembers = from x in context.Members where x.NUSID.Contains(searchinput) select x;
List <Member> ViewAllMemberList = queryViewAllMembers.ToList <Member>();
dataGridViewMembers.DataSource = ViewAllMemberList;
((MAIN)MdiParent).UpdateStatusLabel("Query Completed");
}
catch
{
MessageBox.Show("Connection fail. Please try again.");
((MAIN)MdiParent).UpdateStatusLabel("Error encounter in last transaction...");
}
}
private bool DeleteMember(string NUSID)
{
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
Member DeleteMember = (Member)context.Members.Where(M => M.NUSID == NUSID).First();
context.Members.Remove(DeleteMember);
context.SaveChanges();
((MAIN)MdiParent).UpdateStatusLabel("Member record successfully deleted");
}
catch
{
MessageBox.Show("Deletion Error. NUSID probably did not existed. Please check again.");
((MAIN)MdiParent).UpdateStatusLabel("Error encounter in last transaction...");
return(false);
}
return(true);
}
// Auto-Populated combo-boxes from datasource
private void InitialiseBoxes()
{
List <string> Array1 = new List <string>();
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
var ActivityQuery = from x in context.FacilityInformations group x by x.Activity into y orderby y.Key select y;
foreach (var x in ActivityQuery)
{
Array1.Add(x.Key.ToString());
}
this.comboBoxActivityQuery.DataSource = Array1;
}
catch
{
MessageBox.Show("Connection error. Please try again.");
}
}
// Crystal Report Generation codes
// Coded by Janelle
private void TransIDUpdate()
{
List <string> Array1 = new List <string>();
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
var ActivityQuery = from x in context.Transactions select x;
foreach (var x in ActivityQuery)
{
Array1.Add(x.TransactionID.ToString());
}
this.comboBoxTransID.DataSource = Array1;
}
catch
{
MessageBox.Show("Connection error. Please try again.");
}
}
// delete facility - actual Entity action
private bool DeleteFacility(string FacilityID)
{
try
{
using (SA46Team09BEntities context = new SA46Team09BEntities())
{
FacilityInformation DeleteFacility = (FacilityInformation)context.FacilityInformations.Where(F => F.FacilityID == FacilityID).First();
context.FacilityInformations.Remove(DeleteFacility);
context.SaveChanges();
((MAIN)MdiParent).UpdateStatusLabel("Facilities successfully deleted");
}
}
catch
{
MessageBox.Show("Deletion Error. FacilityID probably did not existed. Please check again.");
((MAIN)MdiParent).UpdateStatusLabel("Error encounter in last transaction...");
return(false);
}
return(true);
}
// Query based on Transaction ID
private void SearchByTransID(DataChecker datacheck)
{
string searchinput = this.textBoxNusIDDeleteBooking.Text.Trim();
if (datacheck.PositiveIntCheck(searchinput) == true)
{
SA46Team09BEntities context = new SA46Team09BEntities();
int searchinputint = Convert.ToInt32(searchinput);
var querySearchByTransID = from x in context.Transactions where x.TransactionID == searchinputint && x.CancelRef == 0 select x;
List <Transaction> ViewTransactionByTransID = querySearchByTransID.ToList <Transaction>();
dataGridViewDeleteBooking.DataSource = ViewTransactionByTransID;
((MAIN)MdiParent).UpdateStatusLabel("Query Completed");
}
else
{
this.labelWarning.Visible = true;
this.labelNusIDDeleteBooking.ForeColor = System.Drawing.Color.Red;
this.labelWarning.Text = "Please enter only positive integers for Transaction ID";
((MAIN)MdiParent).UpdateStatusLabel("Error encounter in last transaction...");
}
}
//helper to check if the user has an active booking. Current scope is that we restrict the user to 1 booking per day
// code by Noel-Noel Han
public bool CheckMemberBooking()
{
bool temp = true;
SA46Team09BEntities context = new SA46Team09BEntities();
var data = (from transactions in context.Transactions
join availabilityChecks in context.AvailabilityChecks on transactions.NUSID equals availabilityChecks.NUSID
where availabilityChecks.BookingDate == transactions.BookingDateTime && availabilityChecks.Activity == transactions.Activity
select new { transactions, availabilityChecks });
if (data.Any())
{
foreach (var v in data)
{
if (v.availabilityChecks.NUSID == this.textBoxNUSIDBooking.Text.ToString() && v.availabilityChecks.BookingDate.Date == DateTime.Parse(this.dateTimePickerBooking.Text).Date&& v.transactions.TransactionType == "Book")
{
MessageBox.Show("You can only make one booking per day.");
return(false);
}
}
}
return(temp);
}
private void buttonBookingReceipt_Click(object sender, EventArgs e)
{
List <Transaction> Translist = new List <Transaction>();
TransactionsList CR = new TransactionsList();
List <Member> Memlist = new List <Member>();
SA46Team09BEntities context = new SA46Team09BEntities();
int transid = Convert.ToInt32(this.comboBoxTransID.Text);
var Query = from x in context.Transactions where x.TransactionID == transid select x;
Translist = Query.ToList <Transaction>();
string NUSID = Translist[0].NUSID;
var Query2 = from x in context.Members where x.NUSID == NUSID select x;
Memlist = Query2.ToList <Member>();
CR.Database.Tables[0].SetDataSource(Translist);
CR.Database.Tables[1].SetDataSource(Memlist);
crystalReportViewerReport.ReportSource = CR;
((MAIN)MdiParent).UpdateStatusLabel("Report Generated");
}
//Helper to check if NUSID is a registered member (lookup member table). True if registered member.
// Coded by Noel-Noel Han
public bool MemberCheck()
{
bool temp = false;
SA46Team09BEntities context = new SA46Team09BEntities();
var data = (from member in context.Members
select member);
if (data.Any())
{
foreach (var v in data)
{
if (v.NUSID == this.textBoxNUSIDBooking.Text.ToString())
{
temp = true;
}
}
if (temp == false)
{
MessageBox.Show("Booking denied. Please register as a member first.");
return(false);
}
}
return(true);
}
// Creation of TimeSlotBox class which will be used as a datasource for availability checking
private void InitialiseTimeBoxes()
{
List <AvailabilityCheck> AvailList = new List <AvailabilityCheck>();
List <FacilityInformation> FacilityList = new List <FacilityInformation>();
int Availcount = 0;
try
{
SA46Team09BEntities context = new SA46Team09BEntities();
var Query = from x in context.AvailabilityChecks select x;
AvailList = Query.ToList <AvailabilityCheck>();
Availcount = AvailList.Count <AvailabilityCheck>();
var Query2 = from x in context.FacilityInformations select x;
FacilityList = Query2.ToList <FacilityInformation>();
}
catch
{
MessageBox.Show("Connection error. Please try again.");
}
// First create the timebox regardless of availability - This creation is mainly to initiate the class objs.
DateTime TodayDate = DateTime.Now;
for (int i = 0; i < 7; i++)
{
foreach (FacilityInformation x in FacilityList)
{
for (int j = 0; j < 6; j++)
{
TimeSlotBox newbox = new TimeSlotBox();
newbox.Activity = x.Activity;
newbox.FacilityID = x.FacilityID;
newbox.Timeslot = j + 1;
newbox.Bookdate = TodayDate.Date.AddDays(i);
newbox.Available = true;
MyBoxes.Add(newbox);
}
}
}
//Then using availability table, set the timeboxes obj available property
if (Availcount != 0)
{
foreach (TimeSlotBox x in MyBoxes)
{
foreach (AvailabilityCheck y in AvailList)
{
if (x.FacilityID == y.FacilityID)
{
if (x.Bookdate == y.BookingDate)
{
if (x.Timeslot == y.TimeSlot)
{
x.Available = false;
}
}
}
}
}
}
}
// This is the block code for booking. The booking process creates an entry at the Transaction table (for recording) + an entry at AvailabilityCheck table (for tracking of availability).
private void BookingCreation(string NUSID)
{
bool booksuccess = false;
SA46Team09BEntities context = new SA46Team09BEntities();
if (MemberCheck()) // Only allow a booking if user is member
{
if (CheckMemberBooking() == true)
{
Transaction TempTransaction = new Transaction();
TempTransaction.NUSID = NUSID;
TempTransaction.Activity = TransactionList[BookingArrayRef].Activity.ToUpper();
TempTransaction.FacilityID = TransactionList[BookingArrayRef].FacilityID.ToUpper();
TempTransaction.BookingDateTime = TransactionList[BookingArrayRef].BookingDateTime;
TempTransaction.TransDateTime = DateTime.Now;
TempTransaction.TimeSlot = TransactionList[BookingArrayRef].TimeSlot;
TempTransaction.TransactionType = "Book";
TempTransaction.CancelRef = 0;
try
{
context.Transactions.Add(TempTransaction);
context.SaveChanges();
booksuccess = true;
}
catch
{
MessageBox.Show("Booking Error. Please check again.");
((MAIN)MdiParent).UpdateStatusLabel("Error encounter in last transaction...");
booksuccess = false;
}
}
//Thread.Sleep(2000);
// Only create Availability Entry if the booking creation is successful
if (booksuccess == true)
{
var queryTransactions = from x in context.Transactions select x;
List <Transaction> NewList = queryTransactions.ToList <Transaction>();
Transaction LastRecord = NewList.OrderByDescending(Transaction => Transaction.TransactionID).First();
AvailabilityCheck AvailabilityBlock = new AvailabilityCheck();
AvailabilityBlock.BookingDate = TransactionList[BookingArrayRef].BookingDateTime;
AvailabilityBlock.FacilityID = TransactionList[BookingArrayRef].FacilityID;
AvailabilityBlock.TimeSlot = TransactionList[BookingArrayRef].TimeSlot;
AvailabilityBlock.TransactionID = LastRecord.TransactionID;
AvailabilityBlock.Activity = TransactionList[BookingArrayRef].Activity;
AvailabilityBlock.NUSID = NUSID;
try
{
context.AvailabilityChecks.Add(AvailabilityBlock);
context.SaveChanges();
((MAIN)MdiParent).UpdateStatusLabel("Transaction record and Availability entry Added");
}
catch
{
MessageBox.Show("Booking Error. Please check again.");
((MAIN)MdiParent).UpdateStatusLabel("Error encounter in last transaction...");
}
}
}
}