public bool CreateUser(string firstName, string lastName, string email, string phonenumber, string password)
{
var query = String.Format(@"Insert into Customers (FirstName, LastName, Email, ContactNbr, Password)
Values ('{0}', '{1}', '{2}', '{3}', '{4}');
Select @@Identity as 'Identity'", firstName, lastName, email, phonenumber, password);
using (var cmd = new SqlCommand(query, new SqlConnection(WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer, WingtipTicketApp.Config.TenantDbName))))
{
using (var sdAdapter = new SqlDataAdapter(cmd))
{
var dsUser = new DataSet();
sdAdapter.Fill(dsUser);
if (dsUser.Tables.Count > 0 && dsUser.Tables[0].Rows.Count > 0)
{
var newUser = new Customer
{
FirstName = firstName,
LastName = lastName,
Email = email,
PhoneNumber = phonenumber,
CustomerId = Convert.ToInt32(dsUser.Tables[0].Rows[0]["Identity"])
};
HttpContext.Current.Session["SessionUser"] = newUser;
Startup.SessionUsers.Add(newUser);
}
}
}
VenuesDbContext.LogAction("Added new user - " + firstName + " " + lastName);
return true;
}
public bool Login(string email, string password)
{
string query = String.Format(@"Select FirstName, LastName, CustomerId from Customers Where Email='{0}' and Password='******'", email, password);
try
{
using (var cmd = new SqlCommand(query, new SqlConnection(WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer, WingtipTicketApp.Config.TenantDbName))))
using (var sdAdapter = new SqlDataAdapter(cmd))
{
DataSet dsUser = new DataSet();
sdAdapter.Fill(dsUser);
if (dsUser.Tables.Count > 0 && dsUser.Tables[0].Rows.Count > 0)
{
Customer newUser = new Customer { FirstName = dsUser.Tables[0].Rows[0]["FirstName"].ToString(), LastName = dsUser.Tables[0].Rows[0]["LastName"].ToString(), Email = email, CustomerId = Convert.ToInt32(dsUser.Tables[0].Rows[0]["CustomerId"]) };
HttpContext.Current.Session["SessionUser"] = newUser;
if (Startup.SessionUsers.Any(a => a.Email != null && a.Email.ToUpper() == email.ToUpper()))
Startup.SessionUsers.Remove(Startup.SessionUsers.First(a => a.Email.ToUpper() == email.ToUpper()));
Startup.SessionUsers.Add(newUser);
}
}
return true;
}
catch { return false; }
}
public List<ConcertTicket> WriteNewTicketToDb(Customer customer, int ConcertId, int SeatMapId, int ticketPrice, int ticketCount)
{
using (var insertConnection = new SqlConnection(constructTicketsDbConnnectString()))
{
insertConnection.Open();
for (int i = 0; i < ticketCount; i++)
{
String ticketName = String.Format("Ticket ({0} of {1}) for user {2} to concert-{3}", (i + 1), ticketCount, customer.FirstName, ConcertId);
string insertQuery = String.Format(@"INSERT INTO Tickets (CustomerId, Name, TicketLevelId, ConcertId, PurchaseDate)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')", customer.CustomerId, ticketName, SeatMapId, ConcertId, DateTime.Now);
using (var insertCommand = new SqlCommand(insertQuery, insertConnection))
{ insertCommand.ExecuteNonQuery(); }
}
insertConnection.Close();
insertConnection.Dispose();
}
return ReturnPurchasedTicketsByConcertId(customer.CustomerId, ConcertId);
}
public MyEventsView GenerateMyEvents(Customer customer, string venueName = null)
{
var myEventsView = new MyEventsView();
if (customer == null)
{
return myEventsView;
}
var concertTicketsList = ConcertTicketDbContext.ReturnPurchasedTicketsByCustomerId(customer.CustomerId);
var venuesList = VenuesDbContext.GetVenues();
var concertsList = new List<Concert>(ConcertDbContext.GetConcerts());
var ticketLevelsList = ConcertTicketDbContext.GetTicketLevels();
foreach (var ticket in concertTicketsList)
{
var concert = concertsList.Find(c => c.ConcertId.Equals(ticket.ConcertId));
var tempTicket = new PurchasedTicket(
concert.Performer.ShortName,
concert.ConcertId,
venuesList.Find(v => v.VenueId.Equals(concert.VenueId)).VenueName,
1,
Convert.ToInt32(ticketLevelsList.Find(l => l.TicketLevelId.Equals(ticket.TicketLevelId)).TicketPrice).ToString(),
"N/A",
concert.ConcertDate,
ticket.ConcertId,
concert.VenueId
);
switch (Convert.ToInt32(tempTicket.SectionName))
{
case 55:
tempTicket.SectionName = "219-221";
break;
case 60:
tempTicket.SectionName = "218-214";
break;
case 65:
tempTicket.SectionName = "222-226";
break;
case 70:
tempTicket.SectionName = "210-213";
break;
case 75:
tempTicket.SectionName = "201-204";
break;
case 80:
tempTicket.SectionName = "114-119";
break;
case 85:
tempTicket.SectionName = "120-126";
break;
case 90:
tempTicket.SectionName = "104-110";
break;
case 95:
tempTicket.SectionName = "111-113";
break;
case 100:
tempTicket.SectionName = "101-103";
break;
}
if (myEventsView.PurchasedTickets.Exists(x => x.ConcertId == ticket.ConcertId && x.SectionName == tempTicket.SectionName))
{
var index = myEventsView.PurchasedTickets.FindIndex(x => x.ConcertId == ticket.ConcertId && x.SectionName == tempTicket.SectionName);
myEventsView.PurchasedTickets[index].TicketQuantity++;
}
else
{
myEventsView.PurchasedTickets.Add(tempTicket);
}
// Sort all events by date
if (myEventsView.PurchasedTickets != null && myEventsView.PurchasedTickets.Count > 0)
{
myEventsView.PurchasedTickets.Sort((a, b) => a.EventDateTime.CompareTo(b.EventDateTime));
}
}
return myEventsView;
}