public List <EventTierInfo> GetEventTiersByEventId(long eventId)
{
using (var context = new AnnoDBContext())
{
var data = (from t in context.EventsTier
where t.record_status == RecordStatuses.Live &&
t.event_id == eventId
select new EventTierInfo()
{
TierId = t.tier_id,
EventId = t.event_id,
ReferenceId = t.ref_id,
Title = t.title,
Description = t.description,
TotalTickets = t.total_tickets,
AvailableTickets = t.available_tickets,
Price = t.price,
Status = t.status,
TierUniqueId = t.address
})
.ToList();
return(data);
}
}
public EventInfo GetEventByRef(long hostId, string refId)
{
using (var context = new AnnoDBContext())
{
var data = (from e in context.Events
join w in context.Wallet on e.event_id equals w.owner_id
where w.owner_type == WalletOwnerTypes.Event &&
e.record_status == RecordStatuses.Live &&
w.record_status == RecordStatuses.Live &&
e.host_id == hostId &&
e.ref_id == refId
select new EventInfo()
{
EventId = e.event_id,
ReferenceId = e.ref_id,
Title = e.title,
Description = e.description,
Status = e.status,
StartDate = e.start_date,
EventUniqueId = e.address,
WalletAddress = w.address,
WalletBalance = w.balance
})
.FirstOrDefault();
return(data);
}
}
public List <TicketInfo> GetTicketsByEvent(long hostId, string eventReferenceId)
{
using (var context = new AnnoDBContext())
{
var data = (from tic in context.CustomerTicket
join b in context.CustomerBooking on tic.booking_id equals b.booking_id
join e in context.Events on b.event_id equals e.event_id
join t in context.EventsTier on new { Key1 = e.event_id, Key2 = tic.tier_id } equals new { Key1 = t.event_id, Key2 = t.tier_id }
join u in context.Customer on b.customer_id equals u.customer_id
where b.record_status == RecordStatuses.Live &&
e.record_status == RecordStatuses.Live &&
tic.record_status == RecordStatuses.Live &&
e.host_id == hostId &&
e.ref_id == eventReferenceId
select new TicketInfo
{
BookingId = b.booking_id,
TicketId = tic.ticket_id,
CustomerReferenceId = u.ref_id,
EventReferenceId = e.ref_id,
TierReferenceId = t.ref_id,
EventTitle = e.title,
TierTitle = t.title,
TicketNo = tic.ticket_number,
BookingConfirmationNo = b.confirmation_number,
Status = tic.status,
PaidPrice = tic.paid_price,
TicketAddress = tic.address
}).ToList();
return(data);
}
}
public EventTierInfo GetEventTiersByRef(long hostId, string refId)
{
using (var context = new AnnoDBContext())
{
var data = (from t in context.EventsTier
where t.record_status == RecordStatuses.Live &&
t.host_id == hostId &&
t.ref_id == refId
select new EventTierInfo()
{
TierId = t.tier_id,
EventId = t.event_id,
ReferenceId = t.ref_id,
Title = t.title,
Description = t.description,
TotalTickets = t.total_tickets,
AvailableTickets = t.available_tickets,
Price = t.price,
Status = t.status,
TierUniqueId = t.address
})
.FirstOrDefault();
return(data);
}
}
public void Transfer(string fromAddress, string toAddress, decimal amount, long?bookingId, string description)
{
using (var context = new AnnoDBContext())
{
//Insert transaction to database
context.Transaction.Add(new Transaction()
{
transaction_datetime = DateTime.UtcNow,
address_from = fromAddress,
address_to = toAddress,
amount = amount,
booking_id = bookingId,
description = description,
created_date = DateTime.UtcNow
});
//Update sender wallet
var fromWallet = context.Wallet.Where(x => x.address == fromAddress).FirstOrDefault();
if (fromWallet != null)
{
fromWallet.balance = fromWallet.balance - amount;
}
//Update recipient wallet
var toWallet = context.Wallet.Where(x => x.address == toAddress).FirstOrDefault();
if (toWallet != null)
{
toWallet.balance = toWallet.balance + amount;
}
context.SaveChanges();
}
}
public long InsertUserTicket(long customerId, long bookingId, long eventId, long tierId, string ticketNumber, decimal paidPrice, string status, string address)
{
long newTicketId = 0;
using (var context = new AnnoDBContext())
{
//Insert customer ticket to database
var newCustomerTicket = new CustomerTicket()
{
customer_id = customerId,
booking_id = bookingId,
event_id = eventId,
tier_id = tierId,
ticket_number = ticketNumber,
status = status,
seat_number = null,
paid_price = paidPrice,
address = address,
record_status = RecordStatuses.Live,
created_date = DateTime.UtcNow
};
context.CustomerTicket.Add(newCustomerTicket);
context.SaveChanges();
//Get the ID of the newly created record
newTicketId = newCustomerTicket.ticket_id;
}
return(newTicketId);
}
public BookingDetails GetBookingByConfirmationNo(long hostId, string confirmationNo)
{
using (var context = new AnnoDBContext())
{
BookingDetails result = null;
//TODO: Change to Linq query
string sql = @"SELECT b.confirmation_number, e.title as event_title, t.title as tier_title, e.ref_id as event_ref, t.ref_id as tier_ref, u.ref_id as customer_ref,
tic.ticket_id, tic.ticket_number, tic.status as ticket_status, tic.paid_price, tic.address as ticket_address
FROM customer_booking b
INNER JOIN customer_ticket tic ON (tic.customer_id = b.customer_id AND tic.event_id = b.event_id)
INNER JOIN events e ON (e.event_id = b.event_id)
INNER JOIN events_tier t ON (t.tier_id = tic.tier_id)
INNER JOIN customer u ON (u.customer_id = tic.customer_id)
WHERE b.record_status='Live'
AND tic.record_status='Live'
AND e.record_status='Live'
AND t.record_status='Live'
AND e.host_id=@hostId
AND b.confirmation_number=@confirmationNo";
sql = sql
.Replace("@hostId ", DataUtility.ToMySqlParam(hostId))
.Replace("@confirmationNo", DataUtility.ToMySqlParam(confirmationNo));
DataTable dt = context.DataTable(sql);
if (DataUtility.HasRecord(dt))
{
DataRow firstRow = dt.Rows[0];
result = new BookingDetails();
result.UserReferenceId = ConvertUtility.ToString(firstRow["customer_ref"]);
result.EventReferenceId = ConvertUtility.ToString(firstRow["event_ref"]);
result.ConfirmationNumber = ConvertUtility.ToString(firstRow["confirmation_number"]);
result.Tickets = new List <TicketInfo>();
foreach (DataRow row in dt.Rows)
{
TicketInfo ticket = new TicketInfo();
ticket.TicketId = ConvertUtility.ToInt64(row["ticket_id"]);
ticket.CustomerReferenceId = ConvertUtility.ToString(row["customer_ref"]);
ticket.EventReferenceId = ConvertUtility.ToString(row["event_ref"]);
ticket.TierReferenceId = ConvertUtility.ToString(row["tier_ref"]);
ticket.TicketNo = ConvertUtility.ToString(row["ticket_number"]);
ticket.Status = ConvertUtility.ToString(row["ticket_status"]);
ticket.PaidPrice = ConvertUtility.ToInt64(row["paid_price"]);
ticket.TicketAddress = ConvertUtility.ToString(row["ticket_address"]);
ticket.EventTitle = ConvertUtility.ToString(row["event_title"]);
ticket.TierTitle = ConvertUtility.ToString(row["tier_title"]);
ticket.BookingConfirmationNo = ConvertUtility.ToString(row["confirmation_number"]);
result.Tickets.Add(ticket);
}
}
return(result);
}
}
public string AddressOf(string addressType, long id)
{
string result = null;
using (var context = new AnnoDBContext())
{
if (addressType == AddressTypes.Host)
{
var data = context.Host.Where(x => x.host_id == id).FirstOrDefault();
if (data != null)
{
result = data.address;
}
}
else if (addressType == AddressTypes.Customer)
{
var data = context.Customer.Where(x => x.customer_id == id).FirstOrDefault();
if (data != null)
{
result = data.address;
}
}
else if (addressType == AddressTypes.Event)
{
var data = context.Events.Where(x => x.event_id == id).FirstOrDefault();
if (data != null)
{
result = data.address;
}
}
else if (addressType == AddressTypes.EventTier)
{
var data = context.EventsTier.Where(x => x.tier_id == id).FirstOrDefault();
if (data != null)
{
result = data.address;
}
}
else if (addressType == AddressTypes.Ticket)
{
var data = context.CustomerTicket.Where(x => x.ticket_id == id).FirstOrDefault();
if (data != null)
{
result = data.address;
}
}
else
{
throw new ArgumentException("Unrecognized address type", addressType);
}
}
return(result);
}
public void UpdateTicketStatus(long ticketId, string status)
{
using (var context = new AnnoDBContext())
{
var record = context.CustomerTicket.SingleOrDefault(x => x.ticket_id == ticketId);
if (record != null)
{
record.status = status;
context.SaveChanges();
}
}
}
public void UpdateAvailableTickets(long tierId, int availableTickets)
{
using (var context = new AnnoDBContext())
{
var record = context.EventsTier.SingleOrDefault(x => x.tier_id == tierId);
if (record != null)
{
record.available_tickets = availableTickets;
context.SaveChanges();
}
}
}
/// <summary>
/// Inserts a new customer record in database.
/// </summary>
public CreateCustomerResponse CreateCustomer(long hostId, CreateCustomerRequest value)
{
CreateCustomerResponse result = new CreateCustomerResponse();
long newCustomerId = 0;
//Generate address
string customerAddress = HashUtility.GenerateHash();
using (var context = new AnnoDBContext())
{
//Insert customer to database
Customer customer = new Customer()
{
host_id = hostId,
ref_id = value.ReferenceId,
address = customerAddress,
record_status = RecordStatuses.Live,
created_date = DateTime.UtcNow
};
context.Customer.Add(customer);
context.SaveChanges();
//Get the Id of the newly created customer
newCustomerId = customer.customer_id;
}
//Create customer wallet
WalletServices walletServices = new WalletServices();
walletServices.CreateWallet(newCustomerId, WalletOwnerTypes.Customer, customerAddress);
//Commit to blockchain
IdentityServices identityService = new IdentityServices();
BlockchainContract blockchainContract = new BlockchainContract();
blockchainContract.CreateCustomer(identityService.AddressOf(IdentityServices.AddressTypes.Host, hostId), customerAddress, value.ReferenceId);
//TODO: For demo purpose, give 1000 ANN tokens to new customers
walletServices.Transfer(Config.OwnerScriptHash, customerAddress, 1000, null, "Demo");
//Commit to blockchain
blockchainContract.Transfer(Config.OwnerScriptHash, customerAddress, 1000);
result.WalletAddress = customerAddress;
result.WalletBalance = 1000;
return(result);
}
public void UpdateEventStatus(long hostId, UpdateEventStatusRequest value)
{
using (var context = new AnnoDBContext())
{
var record = context.Events.SingleOrDefault(x =>
x.host_id == hostId &&
x.ref_id == value.ReferenceId &&
x.record_status == RecordStatuses.Live);
if (record != null)
{
record.status = value.NewStatus;
context.SaveChanges();
}
}
}
public void CreateWallet(long ownerId, string ownerType, string address)
{
using (var context = new AnnoDBContext())
{
context.Wallet.Add(new Wallet()
{
owner_id = ownerId,
owner_type = ownerType,
address = address,
balance = 0,
record_status = RecordStatuses.Live,
created_date = DateTime.UtcNow
});
context.SaveChanges();
}
}
/// <summary>
/// Inserts a new host record in database.
/// </summary>
public void CreateHost(CreateHostRequest value, out string newAPIKey)
{
long newHostId = 0;
//Generate address
WalletServices walletServices = new WalletServices();
string hostAddress = walletServices.GenerateWalletAddress();
using (var context = new AnnoDBContext())
{
//Insert host to database
var newHost = new Host()
{
name = value.Name,
address = hostAddress,
record_status = RecordStatuses.Live,
created_date = DateTime.UtcNow
};
context.Host.Add(newHost);
context.SaveChanges();
//Get the ID of the newly created host
newHostId = newHost.host_id;
//Generate new API key
newAPIKey = Guid.NewGuid().ToString().Replace("-", "");
//Insert into api_keys table
context.ApiKey.Add(new ApiKey()
{
host_id = newHostId,
api_key = newAPIKey,
record_status = RecordStatuses.Live,
created_date = DateTime.UtcNow
});
context.SaveChanges();
//Create host wallet
WalletServices walletService = new WalletServices();
walletService.SaveWallet(newHostId, WalletOwnerTypes.Host, hostAddress);
}
//Commit to blockchain
ContractApi blockchainContract = new ContractApi();
blockchainContract.CreateHost(hostAddress, value.Name);
}
public bool IsRefIdExists(string refIdType, long hostId, string refId)
{
using (var context = new AnnoDBContext())
{
if (refIdType == RefIdTypes.Customer)
{
var data = (from a in context.Customer
where a.record_status == RecordStatuses.Live &&
a.host_id == hostId &&
a.ref_id == refId
select a.ref_id)
.FirstOrDefault();
return(data != null);
}
else if (refIdType == RefIdTypes.Event)
{
var data = (from a in context.Events
where a.record_status == RecordStatuses.Live &&
a.host_id == hostId &&
a.ref_id == refId
select a.ref_id)
.FirstOrDefault();
return(data != null);
}
else if (refIdType == RefIdTypes.EventTier)
{
var data = (from a in context.EventsTier
where a.record_status == RecordStatuses.Live &&
a.host_id == hostId &&
a.ref_id == refId
select a.ref_id)
.FirstOrDefault();
return(data != null);
}
else
{
throw new ArgumentException("Unrecognized reference ID type", refIdType);
}
}
}
public static long?GetCallerHostId()
{
using (var context = new AnnoDBContext())
{
var data = (from a in context.ApiKey
where a.api_key == RequestHeaders.API_KEY
select new { a.host_id })
.FirstOrDefault();
if (data != null)
{
return(data.host_id);
}
else
{
return(null);
}
}
}
/// <summary>
/// Gets the host record by the given api key.
/// Returns null if record is not found.
/// </summary>
/// <param name="apiKey">API Key</param>
/// <returns>Host record, null if record is not found.</returns>
public Host GetHostByAPIKey(string apiKey)
{
using (var context = new AnnoDBContext())
{
var data = (from a in context.Host
join b in context.ApiKey on a.host_id equals b.host_id
where b.api_key == apiKey
select new Host()
{
host_id = a.host_id,
name = a.name,
address = a.address,
record_status = a.record_status,
created_date = a.created_date
})
.FirstOrDefault();
return(data);
}
}
public List <BookingSummary> GetBookingsSummary(long hostId)
{
using (var context = new AnnoDBContext())
{
List <BookingSummary> result = null;
//TODO: Change to Linq query
string sql = @"SELECT e.ref_id as event_ref, e.title as event_title, t.ref_id as tier_ref, t.title as tier_title, count(tic.ticket_id) as tickets_sold
FROM customer_booking b
INNER JOIN customer_ticket tic ON (tic.booking_id = b.booking_id)
INNER JOIN events e ON (e.event_id = b.event_id)
INNER JOIN events_tier t ON (t.event_id = e.event_id and t.tier_id=tic.tier_id)
WHERE b.record_status='Live'
AND e.record_status='Live'
AND e.host_id=@hostId
GROUP BY e.event_id, t.tier_id";
sql = sql
.Replace("@hostId ", DataUtility.ToMySqlParam(hostId));
DataTable dt = context.DataTable(sql);
if (DataUtility.HasRecord(dt))
{
result = new List <BookingSummary>();
foreach (DataRow row in dt.Rows)
{
result.Add(new BookingSummary()
{
EventReferenceId = ConvertUtility.ToString(row["event_ref"]),
TierReferenceId = ConvertUtility.ToString(row["tier_ref"]),
EventTitle = ConvertUtility.ToString(row["event_title"]),
TierTitle = ConvertUtility.ToString(row["tier_title"]),
TicketsSoldCount = ConvertUtility.ToInt32(row["tickets_sold"])
});
}
}
return(result);
}
}
/// <summary>
/// Gets all customer records by host.
/// </summary>
public List <CustomerInfo> GetCustomers(long hostId)
{
using (var context = new AnnoDBContext())
{
var data = (from u in context.Customer
join w in context.Wallet on u.customer_id equals w.owner_id
where w.owner_type == WalletOwnerTypes.Customer &&
u.record_status == RecordStatuses.Live &&
w.record_status == RecordStatuses.Live &&
u.host_id == hostId
select new CustomerInfo()
{
CustomerId = u.customer_id,
RefId = u.ref_id,
CustomerAddress = u.address,
WalletBalance = w.balance,
WalletAddress = w.address
}).ToList();
return(data);
}
}
public List <Booking> GetBookingsByCustomer(long hostId, string customerReferenceId)
{
using (var context = new AnnoDBContext())
{
var data = (from b in context.CustomerBooking
join u in context.Customer on b.customer_id equals u.customer_id
join e in context.Events on b.event_id equals e.event_id
where b.record_status == RecordStatuses.Live &&
e.record_status == RecordStatuses.Live &&
e.host_id == hostId &&
u.ref_id == customerReferenceId
select new Booking
{
UserReferenceId = u.ref_id,
EventReferenceId = e.ref_id,
EventTitle = e.title,
ConfirmationNumber = b.confirmation_number
}).ToList();
return(data);
}
}
public HostInfo GetHostInfoById(long hostId)
{
using (var context = new AnnoDBContext())
{
var data = (from h in context.Host
join w in context.Wallet on h.host_id equals w.owner_id //TODO: join owner type here
where w.owner_type == WalletOwnerTypes.Host &&
h.record_status == RecordStatuses.Live &&
w.record_status == RecordStatuses.Live &&
h.host_id == hostId
select new HostInfo()
{
HostId = h.host_id,
Name = h.name,
WalletAddress = w.address,
WalletBalance = w.balance
})
.FirstOrDefault();
return(data);
}
}
public long InsertUserBooking(long customerId, long eventId, string confirmationNumber)
{
long newBookingId = 0;
using (var context = new AnnoDBContext())
{
//Insert customer booking to database
var newCustomerBooking = new CustomerBooking()
{
customer_id = customerId,
event_id = eventId,
confirmation_number = confirmationNumber,
record_status = RecordStatuses.Live,
created_date = DateTime.UtcNow
};
context.CustomerBooking.Add(newCustomerBooking);
context.SaveChanges();
//Get the ID of the newly created record
newBookingId = newCustomerBooking.booking_id;
}
return(newBookingId);
}
/// <summary>
/// Inserts a new event record in the Events table.
/// </summary>
public void CreateEvent(long hostId, CreateEventsRequest value)
{
long newEventId = 0;
List <string> eventTierUniqueIdList = new List <string>();
//Generate address
string eventUniqueId = HashUtility.GenerateHash();
using (var context = new AnnoDBContext())
{
//Insert event to database
var newEvent = new Events()
{
host_id = hostId,
ref_id = value.ReferenceId,
title = value.Title,
description = value.Description,
start_date = value.StartDate,
status = "Active",
address = eventUniqueId,
record_status = RecordStatuses.Pending,
created_date = DateTime.UtcNow
};
context.Events.Add(newEvent);
context.SaveChanges();
//Get the ID of the newly created record
newEventId = newEvent.event_id;
//Insert event tiers to database
foreach (var tier in value.Tiers)
{
//Generate address
string eventTierUniqueId = HashUtility.GenerateHash();
//Insert event to database
var newEventTier = new EventsTier()
{
host_id = hostId,
event_id = newEventId,
ref_id = tier.ReferenceId,
title = tier.Title,
description = tier.Description,
total_tickets = tier.TotalTickets,
available_tickets = tier.TotalTickets, //available tickets is set to total tickets for initial insert
price = Convert.ToDecimal(tier.Price),
status = "Active",
address = eventTierUniqueId,
record_status = RecordStatuses.Live,
created_date = DateTime.UtcNow
};
context.EventsTier.Add(newEventTier);
context.SaveChanges();
eventTierUniqueIdList.Add(eventTierUniqueId);
}
//Update event record status to live
var record = context.Events.SingleOrDefault(x => x.event_id == newEventId);
if (record != null)
{
record.record_status = RecordStatuses.Live;
context.SaveChanges();
}
}
//Create event wallet
WalletServices walletServices = new WalletServices();
walletServices.SaveWallet(newEventId, WalletOwnerTypes.Event, eventUniqueId);
//Commit to blockchain
IdentityServices identityServices = new IdentityServices();
string hostAddress = identityServices.AddressOf(IdentityServices.AddressTypes.Host, hostId);
ContractApi blockchainContract = new ContractApi();
blockchainContract.CreateEvent(eventUniqueId, hostAddress, value.ReferenceId, value.Title, value.StartDate.Value, "Active");
for (int i = 0; i < value.Tiers.Count; i++)
{
blockchainContract.CreateEventTier(
eventTierUniqueIdList[i], hostAddress, eventUniqueId, value.Tiers[i].ReferenceId, value.Tiers[i].Title,
value.Tiers[i].TotalTickets.Value, value.Tiers[i].TotalTickets.Value, value.Tiers[i].Price.Value);
}
}
