public UpdateModel updateTester(string id, UpdateModel um)
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
var query = (from acc in db.UpdateTesters where acc.TesterID.Equals(Convert.ToInt32(id)) select acc);
if (query.Count() == 1)
{
UpdateTester acc = query.Single();
// acc.TesterID = um.ID;
acc.Name = um.Name;
acc.Surname = um.Surname;
db.SubmitChanges();
um = new UpdateModel()
{
ID = acc.TesterID,
Name = acc.Name,
Surname = acc.Surname,
};
// return accommo;
// string accName = um.Name;
return(um);
}
else
{
return(null);
}
}
}
public EventGuest GuestLogin(string email, string password)
{
HashPass hashedPass = new HashPass();
string pass = HashPass.HashPassword(password);
using (EventrixDBDataContext mde = new EventrixDBDataContext())
{
try
{
return(mde.Guests.Where(pe => pe.Email == email && pe.Password == pass).Select(pe => new EventGuest
{
ID = pe.G_ID,
NAME = pe.Name,
EMAIL = pe.Email,
SURNAME = pe.Surname,
PASS = pe.Password,
TYPE = pe.Type
}).First());
}
catch
{
return(null);
}
};
}
public string deleteProductByID(string eventID)
{
int _id = Convert.ToInt32(eventID);
try
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
List <Product> toDelete = (from dl in db.Products where dl.eventid == _id select dl).ToList();
if (toDelete == null)
{
return("success: Product Not Found");
}
else
{
db.Products.DeleteAllOnSubmit(toDelete);
db.SubmitChanges();
return("success");
}
};
}
catch (Exception)
{
return("Failed");
}
}
public string deleteQRCodeFileByTicketID(string ID)
{
int _id = Convert.ToInt32(ID);
try
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
List <Guest_QRCode> toDelete = (from dl in db.Guest_QRCodes where dl.ticket_Id == _id select dl).ToList();
if (toDelete == null)
{
return("Failed: Image Not Found");
}
else
{
db.Guest_QRCodes.DeleteAllOnSubmit(toDelete);
db.SubmitChanges();
return("success");
}
};
}
catch (Exception)
{
return("Failed");
}
}
public bool UpdateQRCode(QRCodeImage image, string ID)
{
int _id = Convert.ToInt32(ID);
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
var query = (from img in db.Guest_QRCodes
where img.QR_Id.Equals(_id)
select img);
if (query.Count() != 0)
{
Guest_QRCode toinsert = query.Single();
toinsert.Name = image.Name;
toinsert.Location = image.Location;
// db.EventViews.InsertOnSubmit(toinsert);
db.SubmitChanges();
return(true);
}
else
{
return(false);
}
};
}
public EventAddress getAddressByEventId(string eventid)
{
int _id = Convert.ToInt32(eventid);
try
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
Address _address =
(from _event in db.MAIN_EVENTs
where _event.E_ID.Equals(_id)
join add in db.Addresses on _event.AD_Id equals add.AD_Id
select add).First();
EventAddress foundAddress = new EventAddress();
foundAddress.ID = _address.AD_Id;
foundAddress.STREET = _address.AD_StreetName;
foundAddress.CITY = _address.AD_City;
foundAddress.PROVINCE = _address.AD_Province;
foundAddress.COUNTRY = _address.AD_Country;
return(foundAddress);
}
}
catch (Exception)
{
return(null);
}
}
public string RegisterHost(Host host)
{
HashPass hashedPass = new HashPass();
string pass = HashPass.HashPassword(host.PASS);
using (EventrixDBDataContext dbd = new EventrixDBDataContext())
{
try
{
int MailChecker = (from eh in dbd.EventHosts where eh.Email.Equals(host.EMAIL) select eh).Count();
if (MailChecker == 0)
{
EventHost _host = new EventHost();
_host.Name = host.NAME;
_host.Email = host.EMAIL;
_host.Surname = host.SURNAME;
_host.Password = pass;
dbd.EventHosts.InsertOnSubmit(_host);
dbd.SubmitChanges();
return("Registered " + _host.Name + "successfully");
}
else
{
return("Error: Account already taken");
}
}
catch (Exception e)
{
return(e.GetBaseException().ToString());
}
};
}
public List <EventTicket> findticketbyGuestID(string id)
{
int _id = Convert.ToInt32(id);
using (EventrixDBDataContext mde = new EventrixDBDataContext())
{
try
{
return(mde.Ticket_Templates.Where(pe => pe.eventid == _id).Select(pe => new EventTicket
{
_TicketID = pe.TicketID,
_Type = pe.Type,
_Credit = Convert.ToInt32(pe.Credit),
_Price = Convert.ToDecimal(pe.Price),
_Refund = pe.RefundPolicy,
_StartDate = Convert.ToDateTime(pe.StartDate),
_EndDate = Convert.ToDateTime(pe.EndDate),
}).ToList());
}
catch
{
return(null);
}
};
}
//get bridging table info by event id
public List <GuestTicket_BT> getGuestTicketByEventID(string evnetID)
{
List <GuestTicket_BT> Guest_tickets = null;
try
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
var innerJoinQuery =
from ticket_template in db.Ticket_Templates
where ticket_template.eventid.Equals(evnetID)
join guest_ticket in db.Guest_Tickets on ticket_template.TicketID equals guest_ticket.ticket_temp_id
// join QRCode in db.Guest_QRCodes on guest_ticket.ticket_Id equals QRCode.ticket_Id
select new GuestTicket_BT
{
guestticket_BT_ID = guest_ticket.ticket_Id,
Guest_ID = guest_ticket.G_ID,
ticket_template_id = guest_ticket.ticket_temp_id,
numTicket = guest_ticket.numTicket,
};
Guest_tickets = new List <GuestTicket_BT>();
foreach (GuestTicket_BT tickets in innerJoinQuery)
{
Guest_tickets.Add(tickets);
}
return(Guest_tickets);
}
}
catch (Exception)
{
return(null);
}
}
//public List<EventStaff> findallstaffbyEventID(string id)
//{
// using (EventrixDBDataContext mde = new EventrixDBDataContext())
// {
// try
// {
// int _id = Convert.ToInt32(id);
// return mde.Staffs.Where(pe => pe.eventid == _id).Select(pe => new EventStaff
// {
// ID = pe.StaffId,
// NAME = pe.Name,
// EMAIL = pe.Email,
// PASS = pe.Password,
// Occupation = pe.Occupation,
// EventID = pe.eventid
// }).ToList();
// }
// catch
// {
// return null;
// }
// };
//}
public List <StaffModel> findallstaffbyEventID(string id)
{
using (EventrixDBDataContext mde = new EventrixDBDataContext())
{
List <StaffModel> items = new List <StaffModel>();
List <Staff> myStaff = new List <Staff>();
StaffModel _st = new StaffModel();
try
{
int _id = Convert.ToInt32(id);
var query = (from add in mde.Staffs where add.eventid.Equals(Convert.ToInt32(id)) select add);
if (query.Count() == 0) //return all values
{
items = findallstaff();
return(items);
}
else
{
return(mde.Staffs.Where(pe => pe.eventid == _id).Select(pe => new StaffModel
{
ID = pe.StaffId,
NAME = pe.Name,
EMAIL = pe.Email,
PASS = pe.Password,
Occupation = pe.Occupation,
EventID = pe.eventid
}).ToList());
}
}
catch
{
return(null);
}
};
}
public bool CheckInGuest(string QR_ID, string S_ID)
{
int s_ID = Convert.ToInt32(S_ID);
int _id = Convert.ToInt32(QR_ID);
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
try
{
var query = (from qr in db.Guest_QRCodes where qr.QR_Id.Equals(QR_ID) select qr).First();
if (query != null)
{
Guest_QRCode data = query;
data.Checked_In = 1;
data.EntranceTime = DateTime.Now;
db.SubmitChanges();
UpdateStaffNumScans(s_ID);
return(true);
}
else
{
return(false);
}
}catch (Exception)
{
return(false);
}
};
}
public string deleteStaffByEventID(string eventID)
{
int _id = Convert.ToInt32(eventID);
try
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
List <Staff> toDelete = (from dl in db.Staffs where dl.eventid == _id select dl).ToList();
if (toDelete == null)
{
return("success: staff not found");
}
else
{
db.Staffs.DeleteAllOnSubmit(toDelete);
db.SubmitChanges();
return("success: staff deleted");
}
};
}
catch (Exception)
{
return("failed");
}
}
public bool deleteStaff(EventStaff _staff)
{
int _id = Convert.ToInt32(_staff.EventID);
try
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
List <Staff> toDelete = (from dl in db.Staffs where dl.eventid == _id select dl).ToList();
if (toDelete == null)
{
return(false);
}
else
{
db.Staffs.DeleteAllOnSubmit(toDelete);
db.SubmitChanges();
return(true);
}
};
}
catch (Exception)
{
return(false);
}
}
public bool createStaff(StaffModel _staff)
{
using (EventrixDBDataContext dbd = new EventrixDBDataContext())
{
try
{
int MailChecker = (from eh in dbd.Staffs where eh.Email.Equals(_staff.EMAIL) && eh.eventid == _staff.EventID select eh).Count();
if (MailChecker == 0)
{
int _id = Convert.ToInt32(_staff.EventID);
Staff staff = new Staff();
staff.Name = _staff.NAME;
staff.Email = _staff.EMAIL;
staff.Occupation = _staff.Occupation;
staff.Password = _staff.PASS;
staff.eventid = _id;
dbd.Staffs.InsertOnSubmit(staff);
dbd.SubmitChanges();
return(true);
}
else
{
return(false);
}
}
catch (Exception)
{
return(true);
}
};
}
public string deleteAddressByID(string ID)
{
int _id = Convert.ToInt32(ID);
try
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
List <Address> toDelete = (from dl in db.Addresses where dl.AD_Id.Equals(_id) select dl).ToList();
if (toDelete == null)
{
return("Failed: Image Not Found");
}
else
{
db.Addresses.DeleteAllOnSubmit(toDelete);
db.SubmitChanges();
return("success");
}
};
}
catch (Exception)
{
return("Failed");
}
}
//get Guest QR Codes by eventID
public List <QRCodeImage> getQRCodeListByEventID(string evnetID)
{
List <QRCodeImage> QRCodes = null;
try
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
var innerJoinQuery =
from ticket_template in db.Ticket_Templates
where ticket_template.eventid.Equals(evnetID)
join guest_ticket in db.Guest_Tickets on ticket_template.TicketID equals guest_ticket.ticket_temp_id
join QRCode in db.Guest_QRCodes on guest_ticket.ticket_Id equals QRCode.ticket_Id
select new QRCodeImage
{
QRCodeID = QRCode.QR_Id,
Name = QRCode.Name,
ticket_ID = Convert.ToInt32(QRCode.ticket_Id),
Location = QRCode.Location,
};
QRCodes = new List <QRCodeImage>();
foreach (QRCodeImage img in innerJoinQuery)
{
QRCodes.Add(img);
}
return(QRCodes);
}
}
catch (Exception)
{
return(null);
}
}
public EventAddress getAddressById(string id)
{
try
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
var innerJoinQuery =
from address in db.Addresses
where address.AD_Id.Equals(Convert.ToInt32(id))
select new EventAddress
{
ID = address.AD_Id,
STREET = address.AD_StreetName,
CITY = address.AD_City,
LATITUDE = address.AD_Latitude,
LONGITUDE = address.AD_Longitude,
COUNTRY = address.AD_Country,
PROVINCE = address.AD_Province,
};
foreach (EventAddress ca in innerJoinQuery)
{
return(ca);
}
return(null);
}
}
catch (Exception)
{
return(null);
}
}
public EventTicket getVVIPTicket(string EventID)
{
int _id = Convert.ToInt32(EventID);
using (EventrixDBDataContext mde = new EventrixDBDataContext())
{
try
{
return(mde.Ticket_Templates.Where(pe => pe.eventid == _id && pe.Type.Equals("VVIP")).Select(pe => new EventTicket
{
_TicketID = pe.TicketID,
_Type = pe.Type,
_Credit = Convert.ToInt32(pe.Credit),
_Price = Convert.ToDecimal(pe.Price),
_Refund = pe.RefundPolicy,
_StartDate = Convert.ToDateTime(pe.StartDate),
_EndDate = Convert.ToDateTime(pe.EndDate),
_EventID = Convert.ToInt32(pe.eventid),
}).First());
}
catch
{
return(null);
}
};
}
public string RegisterStaff(EventStaff _staff)
{
HashPass hashedPass = new HashPass();
string pass = HashPass.HashPassword(_staff.PASS);
using (EventrixDBDataContext dbd = new EventrixDBDataContext())
{
try
{
int MailChecker = (from eh in dbd.Staffs where eh.Email.Equals(_staff.EMAIL) && eh.eventid == _staff.EventID select eh).Count();
if (MailChecker == 0)
{
int _id = Convert.ToInt32(_staff.EventID);
Staff staff = new Staff();
staff.Name = _staff.NAME;
staff.Email = _staff.EMAIL;
staff.Occupation = _staff.Occupation;
staff.Password = pass;
staff.eventid = _id;
dbd.Staffs.InsertOnSubmit(staff);
dbd.SubmitChanges();
return("Registered " + staff.Name + " successfully");
}
else
{
return("Error: Account already taken");
}
}
catch (Exception e)
{
return(e.GetBaseException().ToString());
}
};
}
//purchase ticket
public int updateTicket(EventTicket _ticket)
{
try
{
using (EventrixDBDataContext dbd = new EventrixDBDataContext())
{
int isAvail = (from st in dbd.Guest_Tickets where st.ticket_temp_id.Equals(_ticket._TicketID) && st.G_ID.Equals(_ticket._GuestID) select st).Count();
if (isAvail != 0)
{
return(0); //ticket already exist
}
else //update available tickets
{
Guest_Ticket toInsert = new Guest_Ticket();
toInsert.ticket_temp_id = _ticket._TicketID;
toInsert.G_ID = _ticket._GuestID;
toInsert.numTicket = _ticket.numTicket;
dbd.Guest_Tickets.InsertOnSubmit(toInsert);
dbd.SubmitChanges();
}
}
using (EventrixDBDataContext dbd = new EventrixDBDataContext())
{
Guest_Ticket toInsert = (from st in dbd.Guest_Tickets where st.ticket_temp_id.Equals(_ticket._TicketID) && st.G_ID.Equals(_ticket._GuestID) select st).First();
int ID = toInsert.ticket_Id;
return(ID);
};
}
catch (Exception)
{
return(-1);
}
}
public string ImportData(string Name, string Surname, string Email)
{
var result = false;
try
{
//check exists
EventrixDBDataContext db = new EventrixDBDataContext();
if (db.Guests.Where(t => t.Email.Equals(Email)).Count() == 0) //create new guest
{
var item = new Guest();
item.Name = Name;
item.Surname = Surname;
item.Email = Email;
item.Password = "******";
item.Type = "Private";
db.Guests.InsertOnSubmit(item);
db.SubmitChanges();
result = true;
}
}
catch (Exception)
{
return("Failed");
}
if (result == true)
{
return("Success");
}
return("Failed");
}
// delete ticket template by event ID
public string deleteTicketTemplateByEventID(string EventID)
{
int _id = Convert.ToInt32(EventID);
try
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
List <Ticket_Template> toDelete = (from dl in db.Ticket_Templates where dl.eventid == _id select dl).ToList();
if (toDelete == null)
{
return("success: Tickets Not Found");
}
else
{
db.Ticket_Templates.DeleteAllOnSubmit(toDelete);
db.SubmitChanges();
return("success");
}
};
}
catch (Exception)
{
return("Failed");
}
}
public string getImageLocationByEventId(string strID)
{
try
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
var innerJoinQuery =
(from file in db.EventImages
where file.EventID.Equals(Convert.ToInt32(strID)) select file).FirstOrDefault();
string fileImage = innerJoinQuery.Location;
string output = "";
if (fileImage == null)
{
output = "Events/Eventrix_Default_Image.png";
}
else
{
output = fileImage.Substring(fileImage.IndexOf('E')); //trim string path from Event
}
return(output);
};
}
catch (Exception)
{
return("Events/Eventrix_Default_Image.png");
}
}
//delete guest ticket bridging table
public string delete_Guest_Ticket_ByEventID(string eventID)
{
int _id = Convert.ToInt32(eventID);
try
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
List <Guest_Ticket> toDelete =
(from ticket_template in db.Ticket_Templates
where ticket_template.eventid.Equals(_id)
join guest_ticket in db.Guest_Tickets on ticket_template.TicketID equals guest_ticket.ticket_temp_id
select guest_ticket).ToList();
// join QRCode in db.Guest_QRCodes on guest_ticket.ticket_Id equals QRCode.ticket_Id
if (toDelete == null)
{
return("success: tickets not found");
}
else
{
db.Guest_Tickets.DeleteAllOnSubmit(toDelete);
db.SubmitChanges();
return("success");
}
}
}
catch (Exception)
{
return(null);
}
}
public ImageFile getImageById(string EventID)
{
try
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
var innerJoinQuery =
from file in db.EventImages
where file.EventID.Equals(Convert.ToInt32(EventID))
select new ImageFile
{
ImageId = file.EventImageId,
ImageName = file.Name,
Location = file.Location,
ContentType = file.ContentType,
FileSize = (long)file.Size,
DateUploaded = file.DateUploaded.ToString(),
};
foreach (ImageFile img in innerJoinQuery)
{
return(img);
}
return(null);
};
}
catch (Exception)
{
return(null);
}
}
//Upload User CreditsS
public bool UploadCredit(EventTicket _ticket)
{
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
try
{
Ticket_Template toInsert = (from st in db.Ticket_Templates where st.TicketID.Equals(_ticket._TicketID) select st).First();
toInsert.Credit = _ticket._Credit;
db.SubmitChanges();
List <Guest_Ticket> insert = (from st in db.Guest_Tickets where st.ticket_temp_id.Equals(_ticket._TicketID) && st.G_ID.Equals(_ticket._GuestID) select st).ToList();
// toInsert.Credit = _ticket._Credit;
foreach (Guest_Ticket gt in insert)
{
var query = (from qr in db.Guest_QRCodes where qr.ticket_Id.Equals(gt.ticket_Id) select qr).First();
Guest_QRCode GQR = query;
GQR.Credit = _ticket._Credit;
db.SubmitChanges();
}
return(true);
}
catch
{
return(false);
}
}
}
public EventStaff StaffLogin(string email, string password)
{
HashPass hashedPass = new HashPass();
string pass = HashPass.HashPassword(password);
using (EventrixDBDataContext mde = new EventrixDBDataContext())
{
try
{
return(mde.Staffs.Where(pe => pe.Email == email && pe.Password == pass).Select(pe => new EventStaff
{
ID = pe.StaffId,
NAME = pe.Name,
EMAIL = pe.Email,
Occupation = pe.Occupation,
PASS = pe.Password,
EventID = Convert.ToInt32(pe.eventid)
}).First());
}
catch
{
return(null);
}
};
}
//Find All Guest Attennding event
public List <EventGuest> findGuestAttendingEvent(string eventID)
{
int E_ID = Convert.ToInt32(eventID);
List <EventGuest> data = new List <EventGuest>();
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
try
{
var query = (from tick_temp in db.Ticket_Templates
where tick_temp.eventid.Equals(E_ID)
join guestBT in db.Guest_Tickets on tick_temp.TicketID equals guestBT.ticket_temp_id
join gst in db.Guests on guestBT.G_ID equals gst.G_ID
select new EventGuest
{
NAME = gst.Name,
SURNAME = gst.Surname,
EMAIL = gst.Email,
}).ToList();
return(query);
}
catch (Exception)
{
return(null);
}
};
}
public List <EventProduct> EventProduct(string EventID)
{
int _id = Convert.ToInt32(EventID);
using (EventrixDBDataContext mde = new EventrixDBDataContext())
{
try
{
return(mde.Products.Where(pe => pe.eventid == _id).Select(pe => new EventProduct
{
_ID = pe.ProductID,
_Name = pe.P_Name,
_Desc = pe.P_Description,
_Quantity = Convert.ToInt32(pe.P_Quantity),
_Price = Convert.ToInt32(pe.P_Price),
EventID = Convert.ToInt32(pe.eventid),
ProdRemaining = Convert.ToInt32(pe.P_Quantity_Remaining),
}).ToList());
}
catch
{
return(null);
}
};
}
public List <GuestModel> RSVPGuest(string eventID, string Type)
{
EventService event_service = new EventService();
List <GuestModel> items = new List <GuestModel>();
int _id = Convert.ToInt32(eventID);
using (EventrixDBDataContext db = new EventrixDBDataContext())
{
try
{
var query = (from res in db.EventRSVPs where res.E_ID.Equals(eventID) && res.Status.Equals(Type)
select new GuestModel
{
ID = res.Guest.G_ID,
NAME = res.Guest.Name,
SURNAME = res.Guest.Surname,
EMAIL = res.Guest.Email,
}).ToList();
return(query);
}
catch (Exception)
{
return(null);
}
};
}
