Oracleパッケージの変換

CREATE OR REPLACE EDITIONABLE PACKAGE "TICKETMANAGEMENT" IS
  procedure sellTickets(person_id IN NUMBER, event_id IN NUMBER, quantity IN NUMBER DEFAULT 1);
  procedure generateTicketActivity(transaction_delay NUMBER, max_transactions IN NUMBER DEFAULT 1000);
  procedure generateTransferActivity(transaction_delay IN NUMBER DEFAULT 5, max_transactions IN NUMBER DEFAULT 100);
end ticketManagement;
/
CREATE OR REPLACE EDITIONABLE PACKAGE BODY "TICKETMANAGEMENT" AS
-- forward type declaration
  TYPE eventTab IS TABLE OF sporting_event%ROWTYPE INDEX BY BINARY_INTEGER;
  TYPE eventRecType IS RECORD ( sport_name sport_type.name%TYPE,
                                home_team_name sport_team.name%TYPE,
                                away_team_name sport_team.name%TYPE,
                                home_field     sport_location.name%TYPE,
                                date_time      sporting_event.start_date_time%TYPE);
-- package variables
  g_max_person_id person.id%TYPE;
  g_min_person_id person.id%TYPE;
-- forward declarations
  function get_open_events return eventTab;
  function get_event_details(event_id IN NUMBER) RETURN eventRecType;
  procedure sellRandomTickets;
--------------------------------
-- sell tickets proc
--------------------------------
procedure sellTickets(person_id IN NUMBER, event_id IN NUMBER, quantity IN NUMBER DEFAULT 1) IS
  not_enough_seats EXCEPTION;
  p_person_id person.id%TYPE := person_id;
  p_event_id sporting_event.id%TYPE := event_id;
  p_quantity NUMBER := quantity;
  r_seat_level   sporting_event_ticket.seat_level%TYPE;
  r_seat_section sporting_event_ticket.seat_section%type;
  r_seat_row     sporting_event_ticket.seat_row%type;
  event_rec eventRecType;
  CURSOR adjacent_seats(p_seat_level NUMBER, p_seat_section VARCHAR2, p_seat_row VARCHAR2) IS
  SELECT * FROM sporting_event_ticket
  WHERE sporting_event_id = p_event_id
  AND   seat_level = p_seat_level
  AND   seat_section = p_seat_section
  AND   seat_row = p_seat_row
  ORDER BY  seat_level, seat_section, seat_row
  FOR UPDATE OF ticketholder_id;
  cur_ticket sporting_event_ticket%ROWTYPE;
BEGIN
  event_rec := get_event_details(p_event_id);
  BEGIN
    SELECT seat_level, seat_section, seat_row
    INTO  r_seat_level, r_seat_section, r_seat_row
    FROM (SELECT seat_level,seat_section,seat_row
          FROM sporting_event_ticket
          WHERE sporting_event_id = p_event_id
          AND   ticketholder_id IS NULL
          GROUP BY seat_level,seat_section,seat_row
          HAVING COUNT(*) >= p_quantity)
    WHERE rownum < 2;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    RAISE not_enough_seats;
  END;
  OPEN adjacent_seats(r_seat_level,r_seat_section,r_seat_row);
  FOR i IN 1..p_quantity LOOP
    FETCH adjacent_seats INTO cur_ticket;
    UPDATE sporting_event_ticket
    SET    ticketholder_id = p_person_id
    WHERE CURRENT OF adjacent_seats;
    INSERT INTO ticket_purchase_hist(sporting_event_ticket_id, purchased_by_id, transaction_date_time, purchase_price)
    VALUES(cur_ticket.id, p_person_id, SYSDATE, cur_ticket.ticket_price);
  END LOOP;
  COMMIT;
EXCEPTION WHEN not_enough_seats THEN
  DBMS_OUTPUT.PUT_LINE('Sorry, there aren''t ' || p_quantity || ' adjacent seats for event:');
  DBMS_OUTPUT.PUT_LINE('   ' || event_rec.home_team_name || ' VS ' || event_rec.away_team_name || '   (' || event_rec.sport_name || ')' );
  DBMS_OUTPUT.PUT_LINE('   ' || event_rec.home_field || ':  ' || TO_CHAR(event_rec.date_time,'DD-MON-YYYY HH:MI'));
END;
--------------------------------
-- transfer ticket
--------------------------------
procedure transferTicket(ticket_id IN NUMBER, new_ticketholder_id IN NUMBER, transfer_all IN BOOLEAN DEFAULT FALSE, price IN NUMBER DEFAULT NULL) IS
  p_ticket_id           NUMBER := ticket_id;
  p_new_ticketholder_id NUMBER := new_ticketholder_id;
  p_price               NUMBER := price;
  xferall               NUMBER := 0;
  old_ticketholder_id   NUMBER;
  last_txn_date         DATE;
  CURSOR txfr_cur(p_purchased_by NUMBER, p_txn_date_time DATE) IS
  SELECT * FROM ticket_purchase_hist
  WHERE  purchased_by_id = p_purchased_by
  AND    transaction_date_time = p_txn_date_time;
BEGIN
  IF transfer_all THEN
    xferall := 1;
  END IF;
  SELECT max(h.transaction_date_time) as transaction_date_time
        ,t.ticketholder_id as ticketholder_id
  INTO  last_txn_date, old_ticketholder_id
  FROM   ticket_purchase_hist h
        ,sporting_event_ticket t
  WHERE  t.id = p_ticket_id
  AND    h.purchased_by_id = t.ticketholder_id
  AND    ((h.sporting_event_ticket_id = p_ticket_id) OR (xferall = 1) )
  GROUP BY t.ticketholder_id;
  FOR xrec IN txfr_cur(old_ticketholder_id, last_txn_date) LOOP
    UPDATE sporting_event_ticket
    SET    ticketholder_id = p_new_ticketholder_id
    WHERE  id = xrec.sporting_event_ticket_id;
    INSERT INTO ticket_purchase_hist(sporting_event_ticket_id, purchased_by_id, transferred_from_id, transaction_date_time, purchase_price)
    VALUES(xrec.sporting_event_ticket_id,  p_new_ticketholder_id, old_ticketholder_id, SYSDATE, NVL(p_price,xrec.purchase_price));
  END LOOP;
  COMMIT;  -- commit the group
EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
END;
---------------------------------------
-- generate ticket purchase activity
---------------------------------------
PROCEDURE generateTicketActivity(transaction_delay NUMBER, max_transactions IN NUMBER DEFAULT 1000) IS
 txn_count NUMBER := 0;
BEGIN
  WHILE txn_count < max_transactions LOOP
    sellRandomTickets;
    txn_count := txn_count +1;
    dbms_lock.sleep(transaction_delay);
  END LOOP;
END;
---------------------------------------
-- generate ticket purchase activity
---------------------------------------
PROCEDURE generateTransferActivity(transaction_delay IN NUMBER DEFAULT 5, max_transactions IN NUMBER DEFAULT 100) IS
  txn_count NUMBER := 0;
  min_tik_id sporting_event_ticket.id%TYPE;
  max_tik_id sporting_event_ticket.id%TYPE;
  tik_id     sporting_event_ticket.id%TYPE;
  new_ticketholder person.id%TYPE;
  xfer_all  BOOLEAN;
  chg_price BOOLEAN;
  new_price sporting_event_ticket.ticket_price%TYPE;
BEGIN
  WHILE txn_count < max_transactions LOOP
      SELECT min(sporting_event_ticket_id), max(sporting_event_ticket_id)
      INTO   min_tik_id, max_tik_id
      FROM  ticket_purchase_hist;
      SELECT MAX(sporting_event_ticket_id)
      INTO   tik_id
      FROM   ticket_purchase_hist
      WHERE  sporting_event_ticket_id <= dbms_random.value(min_tik_id,max_tik_id);
      new_ticketholder := TRUNC(dbms_random.value(g_min_person_id,g_max_person_id));
      xfer_all := (ROUND(dbms_random.value(1,5)) < 5);   -- transfer all tickets 80% of the time
      new_price := NULL;
      chg_price := (ROUND(dbms_random.value(1,3)) = 1);  --  30% of the time change price
      IF chg_price  THEN
        SELECT dbms_random.value(0.8,1.2) * ticket_price INTO new_price
        FROM   sporting_event_ticket
        WHERE  id = tik_id;
      END IF;
      transferTicket(tik_id, new_ticketholder, xfer_all, new_price);
      txn_count := txn_count +1;
      dbms_lock.sleep(transaction_delay);
    END LOOP;
EXCEPTION WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('No tickets available to transfer.');
END;
--------------------------------
-- get event details
--------------------------------
function get_event_details(event_id IN NUMBER) RETURN eventRecType IS
  eventRec eventRecType;
  p_event_id sporting_event.id%TYPE := event_id;
BEGIN
  SELECT e.sport_type_name
        ,h.name
        ,a.name
        ,l.name
        ,e.start_date_time
  INTO eventRec.sport_name, eventRec.home_team_name, eventRec.away_team_name, eventRec.home_field, eventRec.date_time
  FROM sporting_event e
      ,sport_team h
      ,sport_team a
      ,sport_location l
  WHERE e.id = p_event_id
  AND e.home_team_id = h.id
  AND e.away_team_id = a.id
  AND e.location_id = l.id;
 RETURN eventRec;
END;
--------------------------------
-- sell random tickets proc
--------------------------------
procedure sellRandomTickets IS
  event_tab eventTab;
  ticket_holder person.id%TYPE;
  row_ct NUMBER(8);
  event_idx NUMBER(8);
  event_id NUMBER;
  quantity NUMBER;
BEGIN
    event_tab := get_open_events;
    row_ct    := event_tab.COUNT;
    event_idx := TRUNC(dbms_random.value(1,row_ct));
    event_id := event_tab(event_idx).id;
    ticket_holder := TRUNC(dbms_random.value(g_min_person_id,g_max_person_id));
    quantity := dbms_random.value(1,6);
    sellTickets(ticket_holder,event_id,quantity);
END;
---------------------------------------
-- get open events function
---------------------------------------
  function get_open_events return eventTab IS
    event_tab eventTab;
    CURSOR open_events IS
    SELECT *
    FROM   sporting_event
    WHERE  sold_out = 0
    ORDER BY start_date_time;
    row_num BINARY_INTEGER := 1;
  BEGIN
    FOR oe_rec IN open_events LOOP
      event_tab(row_num) := oe_rec;
      row_num := row_num +1;
    END LOOP;
    return event_tab;
  END;
BEGIN
    --- initialize
    select min(id),max(id) INTO g_min_person_id,g_max_person_id from person;
END ticketManagement;
/

class TicketManagement {
  constructor(db) {
    this.db = db;
    this.g_max_person_id = null;
    this.g_min_person_id = null;
  }
async sellTickets(person_id, event_id, quantity = 1) {
  const notEnoughSeats = new Error('not_enough_seats');
  const p_person_id = person_id;
  const p_event_id = event_id;
  const p_quantity = quantity;
  let r_seat_level, r_seat_section, r_seat_row;
  let event_rec;
  try {
    event_rec = await this.get_event_details(p_event_id);
    const seatData = await this.db.collection('sportingEventTicket').aggregate([
      { $match: { sportingEventId: p_event_id, ticketholderId: null } },
      { $group: { _id: { seatLevel: "$seatLevel", seatSection: "$seatSection", seatRow: "$seatRow" }, count: { $sum: 1 } } },
      { $match: { count: { $gte: p_quantity } } },
      { $limit: 1 },
      { $project: { _id: 0, seatLevel: "$_id.seatLevel", seatSection: "$_id.seatSection", seatRow: "$_id.seatRow" } }
    ]).toArray();
    if (seatData.length === 0) throw notEnoughSeats;
    r_seat_level = seatData[0].seatLevel;
    r_seat_section = seatData[0].seatSection;
    r_seat_row = seatData[0].seatRow;
    const adjacentSeatsCursor = this.db.collection('sportingEventTicket').find({
      sportingEventId: p_event_id,
      seatLevel: r_seat_level,
      seatSection: r_seat_section,
      seatRow: r_seat_row
    }).sort({ seatLevel: 1, seatSection: 1, seatRow: 1 });
    for (let i = 0; i < p_quantity; i++) {
      const cur_ticket = await adjacentSeatsCursor.next();
      if (!cur_ticket) throw notEnoughSeats;
      await this.db.collection('sportingEventTicket').updateOne(
        { _id: cur_ticket._id },
        { $set: { ticketholderId: p_person_id } }
      );
      await this.db.collection('ticketPurchaseHist').insertOne({
        sportingEventTicketId: cur_ticket.id,
        purchasedById: p_person_id,
        transactionDateTime: new Date(),
        purchasePrice: cur_ticket.ticketPrice
      });
    }
  } catch (err) {
    if (err === notEnoughSeats) {
      console.log(`Sorry, there aren't ${p_quantity} adjacent seats for event:`);
      console.log(`   ${event_rec.home_team_name} VS ${event_rec.away_team_name}   (${event_rec.sport_name})`);
      console.log(`   ${event_rec.home_field}:  ${event_rec.date_time.toLocaleString('en-GB', { day: '2-digit', month: 'short', year: 'numeric', hour: '2-digit', minute: '2-digit' })}`);
    } else {
      throw err;
    }
  }
}
async $transferTicket(ticket_id, new_ticketholder_id, transfer_all = false, price = null) {
  const p_ticket_id = ticket_id;
  const p_new_ticketholder_id = new_ticketholder_id;
  const p_price = price;
  const xferall = transfer_all ? 1 : 0;
  try {
    const ticket = await this.db.collection('sportingEventTicket').findOne({ id: p_ticket_id });
    if (!ticket) throw new Error('Ticket not found');
    const lastTransaction = await this.db.collection('ticketPurchaseHist').aggregate([
      { $match: { purchased_by_id: ticket.ticketholderId, $or: [{ sporting_event_ticket_id: p_ticket_id }, { xferall: 1 }] } },
      { $group: { _id: '$ticketholderId', last_txn_date: { $max: '$transaction_date_time' } } }
    ]).toArray();
    if (lastTransaction.length === 0) throw new Error('No transaction found');
    const old_ticketholder_id = ticket.ticketholderId;
    const last_txn_date = lastTransaction[0].last_txn_date;
    const transactions = await this.db.collection('ticketPurchaseHist').find({
      purchased_by_id: old_ticketholder_id,
      transaction_date_time: last_txn_date
    }).toArray();
    for (const xrec of transactions) {
      await this.db.collection('sportingEventTicket').updateOne(
        { id: xrec.sporting_event_ticket_id },
        { $set: { ticketholderId: p_new_ticketholder_id } }
      );
      await this.db.collection('ticketPurchaseHist').insertOne({
        sporting_event_ticket_id: xrec.sporting_event_ticket_id,
        purchased_by_id: p_new_ticketholder_id,
        transferred_from_id: old_ticketholder_id,
        transaction_date_time: new Date(),
        purchase_price: p_price !== null ? p_price : xrec.purchase_price
      });
    }
  } catch (error) {
    console.error(error);
    throw error;
  }
}
async generateTicketActivity(transaction_delay, max_transactions = 1000) {
  let txn_count = 0;
  while (txn_count < max_transactions) {
    await this.sellRandomTickets();
    txn_count += 1;
    await new Promise(resolve => setTimeout(resolve, transaction_delay * 1000));
  }
}
async generateTransferActivity(transaction_delay = 5, max_transactions = 100) {
  let txn_count = 0;
  let min_tik_id, max_tik_id, tik_id, new_ticketholder, xfer_all, chg_price, new_price;
  while (txn_count < max_transactions) {
    const minMaxResult = await this.db.collection('ticketPurchaseHist').aggregate([
      {
        $group: {
          _id: null,
          min_tik_id: { $min: "$sportingEventTicketId" },
          max_tik_id: { $max: "$sportingEventTicketId" }
        }
      }
    ]).toArray();
    if (minMaxResult.length === 0) {
      console.log('No tickets available to transfer.');
      return;
    }
    min_tik_id = minMaxResult[0].min_tik_id;
    max_tik_id = minMaxResult[0].max_tik_id;
    const tikResult = await this.db.collection('ticketPurchaseHist').aggregate([
      {
        $match: {
          sportingEventTicketId: { $lte: Math.random() * (max_tik_id - min_tik_id) + min_tik_id }
        }
      },
      {
        $group: {
          _id: null,
          tik_id: { $max: "$sportingEventTicketId" }
        }
      }
    ]).toArray();
    if (tikResult.length === 0) {
      console.log('No tickets available to transfer.');
      return;
    }
    tik_id = tikResult[0].tik_id;
    new_ticketholder = Math.floor(Math.random() * (this.g_max_person_id - this.g_min_person_id) + this.g_min_person_id);
    xfer_all = Math.round(Math.random() * 4) < 4;
    new_price = null;
    chg_price = Math.round(Math.random() * 2) === 0;
    if (chg_price) {
      const priceResult = await this.db.collection('sportingEventTicket').findOne({ id: tik_id });
      new_price = Math.random() * (1.2 - 0.8) + 0.8 * priceResult.ticketPrice;
    }
    await this.transferTicket(tik_id, new_ticketholder, xfer_all, new_price);
    txn_count++;
    await new Promise(resolve => setTimeout(resolve, transaction_delay * 1000));
  }
}
async $get_event_details(event_id) {
  const p_event_id = event_id;
  const result = await this.db.collection('sportingEvent').aggregate([
    {
      $match: { id: p_event_id }
    },
    {
      $lookup: {
        from: 'sportTeam',
        localField: 'homeTeamId',
        foreignField: 'id',
        as: 'home_team'
      }
    },
    {
      $lookup: {
        from: 'sportTeam',
        localField: 'awayTeamId',
        foreignField: 'id',
        as: 'away_team'
      }
    },
    {
      $lookup: {
        from: 'sportLocation',
        localField: 'locationId',
        foreignField: 'id',
        as: 'location'
      }
    },
    {
      $unwind: '$home_team'
    },
    {
      $unwind: '$away_team'
    },
    {
      $unwind: '$location'
    },
    {
      $project: {
        sport_name: '$sportTypeName',
        home_team_name: '$home_team.name',
        away_team_name: '$away_team.name',
        home_field: '$location.name',
        date_time: '$startDateTime'
      }
    }
  ]).toArray();
  if (result.length === 0) {
    throw new Error('Event not found');
  }
  return result[0];
}
async sellRandomTickets() {
  const eventTab = await this.get_open_events();
  const row_ct = eventTab.length;
  const event_idx = Math.floor(Math.random() * row_ct);
  const event_id = eventTab[event_idx].id;
  const ticket_holder = Math.floor(Math.random() * (this.g_max_person_id - this.g_min_person_id + 1)) + this.g_min_person_id;
  const quantity = Math.floor(Math.random() * 6) + 1;
  await this.sellTickets(ticket_holder, event_id, quantity);
}
async get_open_events() {
  const openEvents = await this.db.collection('sportingEvent').find({ soldOut: 0 }).sort({ startDateTime: 1 }).toArray();
  return openEvents;
}
}

using MongoDB.Bson;
using MongoDB.Driver;
using System;
public class TicketManagement
{
    private readonly IMongoDatabase db;
    public TicketManagement(IMongoDatabase database)
    {
        db = database;
        Initialize();
    }
    private decimal g_max_person_id;
    private decimal g_min_person_id;
    private void Initialize()
    {
        var personCollection = db.GetCollection<BsonDocument>("person");
        var result = personCollection.Aggregate()
            .Group(new BsonDocument
            {
                { "_id", BsonNull.Value },
                { "minId", new BsonDocument("$min", "$id") },
                { "maxId", new BsonDocument("$max", "$id") }
            })
            .FirstOrDefault();
        g_min_person_id = result["minId"].AsDecimal;
        g_max_person_id = result["maxId"].AsDecimal;
    }
public void sellTickets(decimal person_id, decimal event_id, int quantity = 1)
{
    var notEnoughSeats = new Exception("Not enough seats");
    var p_person_id = person_id;
    var p_event_id = event_id;
    var p_quantity = quantity;
    int r_seat_level;
    string r_seat_section;
    string r_seat_row;
    var event_rec = get_event_details(p_event_id);
    try
    {
        var seatQuery = db.GetCollection<BsonDocument>("sportingEventTicket")
            .Aggregate()
            .Match(new BsonDocument { { "sportingEventId", p_event_id }, { "ticketholderId", BsonNull.Value } })
            .Group(new BsonDocument { { "_id", new BsonDocument { { "seatLevel", "$seatLevel" }, { "seatSection", "$seatSection" }, { "seatRow", "$seatRow" } } }, { "count", new BsonDocument("$sum", 1) } })
            .Match(new BsonDocument("count", new BsonDocument("$gte", p_quantity)))
            .Limit(1)
            .Project(new BsonDocument { { "seatLevel", "$_id.seatLevel" }, { "seatSection", "$_id.seatSection" }, { "seatRow", "$_id.seatRow" } })
            .FirstOrDefault();
        if (seatQuery == null)
        {
            throw notEnoughSeats;
        }
        r_seat_level = seatQuery["seatLevel"].AsInt32;
        r_seat_section = seatQuery["seatSection"].AsString;
        r_seat_row = seatQuery["seatRow"].AsString;
    }
    catch
    {
        throw notEnoughSeats;
    }
    var adjacentSeatsCursor = db.GetCollection<BsonDocument>("sportingEventTicket")
        .Find(new BsonDocument { { "sportingEventId", p_event_id }, { "seatLevel", r_seat_level }, { "seatSection", r_seat_section }, { "seatRow", r_seat_row } })
        .Sort(new BsonDocument { { "seatLevel", 1 }, { "seatSection", 1 }, { "seatRow", 1 } })
        .ToCursor();
    using (var session = db.Client.StartSession())
    {
        session.StartTransaction();
        try
        {
            for (int i = 0; i < p_quantity; i++)
            {
                if (!adjacentSeatsCursor.MoveNext())
                {
                    throw notEnoughSeats;
                }
                var cur_ticket = adjacentSeatsCursor.Current;
                var updateFilter = Builders<BsonDocument>.Filter.Eq("_id", cur_ticket["_id"]);
                var update = Builders<BsonDocument>.Update.Set("ticketholderId", p_person_id);
                db.GetCollection<BsonDocument>("sportingEventTicket").UpdateOne(updateFilter, update);
                var purchaseHistDoc = new BsonDocument
                {
                    { "sportingEventTicketId", cur_ticket["id"] },
                    { "purchasedById", p_person_id },
                    { "transactionDateTime", DateTime.Now },
                    { "purchasePrice", cur_ticket["ticketPrice"] }
                };
                db.GetCollection<BsonDocument>("ticketPurchaseHist").InsertOne(purchaseHistDoc);
            }
            session.CommitTransaction();
        }
        catch
        {
            session.AbortTransaction();
            throw;
        }
    }
}
public void generateTicketActivity(decimal transaction_delay, int max_transactions = 1000)
{
    int txn_count = 0;
    while (txn_count < max_transactions)
    {
        sellRandomTickets();
        txn_count += 1;
        System.Threading.Thread.Sleep((int)(transaction_delay * 1000));
    }
}
public void generateTransferActivity(int transaction_delay = 5, int max_transactions = 100)
{
    int txn_count = 0;
    decimal min_tik_id, max_tik_id, tik_id;
    decimal new_ticketholder;
    bool xfer_all;
    bool chg_price;
    decimal? new_price;
    while (txn_count < max_transactions)
    {
        var minMaxResult = db.GetCollection<BsonDocument>("ticketPurchaseHist")
            .Aggregate()
            .Group(new BsonDocument
            {
                { "_id", BsonNull.Value },
                { "min_tik_id", new BsonDocument("$min", "$sporting_event_ticket_id") },
                { "max_tik_id", new BsonDocument("$max", "$sporting_event_ticket_id") }
            })
            .FirstOrDefault();
        if (minMaxResult == null)
        {
            Console.WriteLine("No tickets available to transfer.");
            return;
        }
        min_tik_id = minMaxResult["min_tik_id"].AsDecimal;
        max_tik_id = minMaxResult["max_tik_id"].AsDecimal;
        var tikResult = db.GetCollection<BsonDocument>("ticketPurchaseHist")
            .Find(Builders<BsonDocument>.Filter.Lte("sporting_event_ticket_id", new BsonDecimal128(new Random().NextDouble() * (double)(max_tik_id - min_tik_id) + (double)min_tik_id)))
            .Sort(Builders<BsonDocument>.Sort.Descending("sporting_event_ticket_id"))
            .FirstOrDefault();
        if (tikResult == null)
        {
            Console.WriteLine("No tickets available to transfer.");
            return;
        }
        tik_id = tikResult["sporting_event_ticket_id"].AsDecimal;
        new_ticketholder = (decimal)(new Random().NextDouble() * (double)(g_max_person_id - g_min_person_id) + (double)g_min_person_id);
        xfer_all = new Random().Next(1, 6) < 5;
        new_price = null;
        chg_price = new Random().Next(1, 4) == 1;
        if (chg_price)
        {
            var ticketResult = db.GetCollection<BsonDocument>("sportingEventTicket")
                .Find(Builders<BsonDocument>.Filter.Eq("id", tik_id))
                .FirstOrDefault();
            if (ticketResult != null)
            {
                new_price = ticketResult["ticket_price"].AsDecimal * (decimal)(new Random().NextDouble() * 0.4 + 0.8);
            }
        }
        transferTicket(tik_id, new_ticketholder, xfer_all, new_price);
        txn_count++;
        System.Threading.Thread.Sleep(transaction_delay * 1000);
    }
}
public List<BsonDocument> get_open_events()
{
    var event_tab = new List<BsonDocument>();
    var open_events = db.GetCollection<BsonDocument>("sportingEvent")
                        .Find(Builders<BsonDocument>.Filter.Eq("soldOut", 0))
                        .Sort(Builders<BsonDocument>.Sort.Ascending("startDateTime"))
                        .ToList();
    foreach (var oe_rec in open_events)
    {
        event_tab.Add(oe_rec);
    }
    return event_tab;
}
public eventRecType get_event_details(decimal event_id)
{
    var eventRec = new eventRecType();
    var p_event_id = event_id;
    var pipeline = new[]
    {
        new BsonDocument("$match", new BsonDocument("id", p_event_id)),
        new BsonDocument("$lookup", new BsonDocument
        {
            { "from", "sportTeam" },
            { "localField", "homeTeamId" },
            { "foreignField", "id" },
            { "as", "homeTeam" }
        }),
        new BsonDocument("$lookup", new BsonDocument
        {
            { "from", "sportTeam" },
            { "localField", "awayTeamId" },
            { "foreignField", "id" },
            { "as", "awayTeam" }
        }),
        new BsonDocument("$lookup", new BsonDocument
        {
            { "from", "sportLocation" },
            { "localField", "locationId" },
            { "foreignField", "id" },
            { "as", "location" }
        }),
        new BsonDocument("$unwind", "$homeTeam"),
        new BsonDocument("$unwind", "$awayTeam"),
        new BsonDocument("$unwind", "$location"),
        new BsonDocument("$project", new BsonDocument
        {
            { "sport_name", "$sportTypeName" },
            { "home_team_name", "$homeTeam.name" },
            { "away_team_name", "$awayTeam.name" },
            { "home_field", "$location.name" },
            { "date_time", "$startDateTime" }
        })
    };
    var result = db.GetCollection<BsonDocument>("sportingEvent").Aggregate<BsonDocument>(pipeline).FirstOrDefault();
    if (result != null)
    {
        eventRec.sport_name = result["sport_name"].AsString;
        eventRec.home_team_name = result["home_team_name"].AsString;
        eventRec.away_team_name = result["away_team_name"].AsString;
        eventRec.home_field = result["home_field"].AsString;
        eventRec.date_time = result["date_time"].ToUniversalTime();
    }
    return eventRec;
}
void sellRandomTickets()
{
    var eventTab = getOpenEvents();
    var rowCt = eventTab.Count;
    var eventIdx = (int)Math.Truncate(new Random().NextDouble() * (rowCt - 1)) + 1;
    var eventId = eventTab[eventIdx].id;
    var ticketHolder = (int)Math.Truncate(new Random().NextDouble() * (g_max_person_id - g_min_person_id) + g_min_person_id);
    var quantity = (int)Math.Truncate(new Random().NextDouble() * 5) + 1;
    sellTickets(ticketHolder, eventId, quantity);
}
void transferTicket(decimal ticket_id, decimal new_ticketholder_id, bool transfer_all = false, decimal? price = null)
{
    var p_ticket_id = ticket_id;
    var p_new_ticketholder_id = new_ticketholder_id;
    var p_price = price;
    var xferall = transfer_all ? 1 : 0;
    decimal old_ticketholder_id;
    DateTime last_txn_date;
    var txfr_cur = db.GetCollection<BsonDocument>("ticketPurchaseHist")
        .Find(Builders<BsonDocument>.Filter.And(
            Builders<BsonDocument>.Filter.Eq("purchased_by_id", old_ticketholder_id),
            Builders<BsonDocument>.Filter.Eq("transaction_date_time", last_txn_date)
        )).ToList();
    var result = db.GetCollection<BsonDocument>("ticketPurchaseHist").Aggregate()
        .Match(Builders<BsonDocument>.Filter.And(
            Builders<BsonDocument>.Filter.Eq("sporting_event_ticket_id", p_ticket_id),
            Builders<BsonDocument>.Filter.Eq("purchased_by_id", old_ticketholder_id),
            Builders<BsonDocument>.Filter.Or(
                Builders<BsonDocument>.Filter.Eq("sporting_event_ticket_id", p_ticket_id),
                Builders<BsonDocument>.Filter.Eq("xferall", 1)
            )
        ))
        .Group(new BsonDocument
        {
            { "_id", "$ticketholder_id" },
            { "transaction_date_time", new BsonDocument("$max", "$transaction_date_time") }
        })
        .FirstOrDefault();
    if (result != null)
    {
        last_txn_date = result["transaction_date_time"].ToUniversalTime();
        old_ticketholder_id = result["_id"].AsDecimal;
        foreach (var xrec in txfr_cur)
        {
            db.GetCollection<BsonDocument>("sportingEventTicket").UpdateOne(
                Builders<BsonDocument>.Filter.Eq("id", xrec["sporting_event_ticket_id"]),
                Builders<BsonDocument>.Update.Set("ticketholder_id", p_new_ticketholder_id)
            );
            db.GetCollection<BsonDocument>("ticketPurchaseHist").InsertOne(new BsonDocument
            {
                { "sporting_event_ticket_id", xrec["sporting_event_ticket_id"] },
                { "purchased_by_id", p_new_ticketholder_id },
                { "transferred_from_id", old_ticketholder_id },
                { "transaction_date_time", DateTime.UtcNow },
                { "purchase_price", p_price ?? xrec["purchase_price"].AsDecimal }
            });
        }
    }
    else
    {
        throw new Exception("No tickets available to transfer.");
    }
}
}

public class TicketManagement {
    private MongoDatabase db;
    private Decimal128 g_max_person_id;
    private Decimal128 g_min_person_id;
    public TicketManagement(MongoDatabase db) {
        this.db = db;
        initialize();
    }
    private void initialize() {
        MongoCollection<Document> personCollection = db.getCollection("person");
        Document minMaxPersonId = personCollection.aggregate(Arrays.asList(
                Aggregates.group(null, Accumulators.min("minId", "$id"), Accumulators.max("maxId", "$id"))
        )).first();
        if (minMaxPersonId != null) {
            g_min_person_id = minMaxPersonId.get("minId", Decimal128.class);
            g_max_person_id = minMaxPersonId.get("maxId", Decimal128.class);
        }
    }
public void sellTickets(Number person_id, Number event_id, Number quantity) {
    try {
        Document eventRec = get_event_details(event_id);
        Document seatInfo = db.getCollection("sportingEventTicket")
            .aggregate(Arrays.asList(
                Aggregates.match(Filters.and(
                    Filters.eq("sportingEventId", event_id),
                    Filters.eq("ticketholderId", null)
                )),
                Aggregates.group(new Document("seatLevel", "$seatLevel")
                    .append("seatSection", "$seatSection")
                    .append("seatRow", "$seatRow"),
                    Accumulators.sum("count", 1)
                ),
                Aggregates.match(Filters.gte("count", quantity)),
                Aggregates.limit(1)
            )).first();
        if (seatInfo == null) {
            throw new Exception("not_enough_seats");
        }
        Number r_seat_level = seatInfo.getInteger("_id.seatLevel");
        String r_seat_section = seatInfo.getString("_id.seatSection");
        String r_seat_row = seatInfo.getString("_id.seatRow");
        MongoCursor<Document> cursor = db.getCollection("sportingEventTicket")
            .find(Filters.and(
                Filters.eq("sportingEventId", event_id),
                Filters.eq("seatLevel", r_seat_level),
                Filters.eq("seatSection", r_seat_section),
                Filters.eq("seatRow", r_seat_row)
            ))
            .sort(Sorts.ascending("seatLevel", "seatSection", "seatRow"))
            .iterator();
        for (int i = 0; i < quantity; i++) {
            if (!cursor.hasNext()) {
                throw new Exception("not_enough_seats");
            }
            Document cur_ticket = cursor.next();
            db.getCollection("sportingEventTicket").updateOne(
                Filters.eq("_id", cur_ticket.getObjectId("_id")),
                Updates.set("ticketholderId", person_id)
            );
            db.getCollection("ticketPurchaseHist").insertOne(new Document()
                .append("sportingEventTicketId", cur_ticket.getDecimal("id"))
                .append("purchasedById", person_id)
                .append("transactionDateTime", new Date())
                .append("purchasePrice", cur_ticket.getDecimal("ticketPrice"))
            );
        }
    } catch (Exception e) {
        if (e.getMessage().equals("not_enough_seats")) {
            System.out.println("Sorry, there aren't " + quantity + " adjacent seats for event:");
            System.out.println("   " + eventRec.getString("home_team_name") + " VS " + eventRec.getString("away_team_name") + "   (" + eventRec.getString("sport_name") + ")");
            System.out.println("   " + eventRec.getString("home_field") + ":  " + new SimpleDateFormat("dd-MMM-yyyy HH:mm").format(eventRec.getDate("date_time")));
        } else {
            e.printStackTrace();
        }
    }
}
public void generateTicketActivity(int transaction_delay, int max_transactions) {
    int txn_count = 0;
    while (txn_count < max_transactions) {
        sellRandomTickets();
        txn_count++;
        try {
            Thread.sleep(transaction_delay * 1000);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }
}
public void generateTransferActivity(double transaction_delay, int max_transactions) {
    int txn_count = 0;
    Decimal128 min_tik_id;
    Decimal128 max_tik_id;
    Decimal128 tik_id;
    Decimal128 new_ticketholder;
    boolean xfer_all;
    boolean chg_price;
    Decimal128 new_price;
    while (txn_count < max_transactions) {
        Document minMaxResult = db.getCollection("ticketPurchaseHist")
            .aggregate(Arrays.asList(
                Aggregates.group(null,
                    Accumulators.min("min_tik_id", "$sportingEventTicketId"),
                    Accumulators.max("max_tik_id", "$sportingEventTicketId")
                )
            )).first();
        if (minMaxResult == null) {
            System.out.println("No tickets available to transfer.");
            return;
        }
        min_tik_id = minMaxResult.get("min_tik_id", Decimal128.class);
        max_tik_id = minMaxResult.get("max_tik_id", Decimal128.class);
        Document tikResult = db.getCollection("ticketPurchaseHist")
            .aggregate(Arrays.asList(
                Aggregates.match(Filters.lte("sportingEventTicketId", new Decimal128(Math.random() * (max_tik_id.doubleValue() - min_tik_id.doubleValue()) + min_tik_id.doubleValue()))),
                Aggregates.group(null, Accumulators.max("tik_id", "$sportingEventTicketId"))
            )).first();
        if (tikResult == null) {
            System.out.println("No tickets available to transfer.");
            return;
        }
        tik_id = tikResult.get("tik_id", Decimal128.class);
        new_ticketholder = new Decimal128(Math.floor(Math.random() * (g_max_person_id.doubleValue() - g_min_person_id.doubleValue()) + g_min_person_id.doubleValue()));
        xfer_all = (Math.round(Math.random() * 4) < 4); // transfer all tickets 80% of the time
        new_price = null;
        chg_price = (Math.round(Math.random() * 2) == 0); // 30% of the time change price
        if (chg_price) {
            Document priceResult = db.getCollection("sportingEventTicket")
                .find(Filters.eq("id", tik_id))
                .projection(Projections.include("ticketPrice"))
                .first();
            if (priceResult != null) {
                new_price = new Decimal128(priceResult.getDouble("ticketPrice") * (Math.random() * 0.4 + 0.8));
            }
        }
        transferTicket(tik_id, new_ticketholder, xfer_all, new_price);
        txn_count++;
        try {
            Thread.sleep((long) (transaction_delay * 1000));
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }
}
public List<Document> getOpenEvents() {
    List<Document> eventTab = new ArrayList<>();
    MongoCollection<Document> sportingEventCollection = db.getCollection("sportingEvent");
    FindIterable<Document> openEvents = sportingEventCollection.find(Filters.eq("soldOut", 0))
            .sort(Sorts.ascending("startDateTime"));
    for (Document oeRec : openEvents) {
        eventTab.add(oeRec);
    }
    return eventTab;
}
public eventRecType get_event_details(Number event_id) {
    eventRecType eventRec = new eventRecType();
    Number p_event_id = event_id;
    List<Document> pipeline = Arrays.asList(
        new Document("$match", new Document("id", p_event_id)),
        new Document("$lookup", new Document("from", "sportTeam")
            .append("localField", "homeTeamId")
            .append("foreignField", "id")
            .append("as", "home_team")),
        new Document("$lookup", new Document("from", "sportTeam")
            .append("localField", "awayTeamId")
            .append("foreignField", "id")
            .append("as", "away_team")),
        new Document("$lookup", new Document("from", "sportLocation")
            .append("localField", "locationId")
            .append("foreignField", "id")
            .append("as", "location")),
        new Document("$unwind", "$home_team"),
        new Document("$unwind", "$away_team"),
        new Document("$unwind", "$location"),
        new Document("$project", new Document("sport_name", "$sportTypeName")
            .append("home_team_name", "$home_team.name")
            .append("away_team_name", "$away_team.name")
            .append("home_field", "$location.name")
            .append("date_time", "$startDateTime"))
    );
    MongoCollection<Document> collection = db.getCollection("sportingEvent");
    AggregateIterable<Document> result = collection.aggregate(pipeline);
    Document doc = result.first();
    if (doc != null) {
        eventRec.sport_name = doc.getString("sport_name");
        eventRec.home_team_name = doc.getString("home_team_name");
        eventRec.away_team_name = doc.getString("away_team_name");
        eventRec.home_field = doc.getString("home_field");
        eventRec.date_time = doc.getDate("date_time");
    }
    return eventRec;
}
public void sellRandomTickets() {
    List<Document> eventTab = getOpenEvents();
    int rowCt = eventTab.size();
    int eventIdx = (int) Math.floor(Math.random() * rowCt);
    Document event = eventTab.get(eventIdx);
    Decimal128 eventId = event.get("id", Decimal128.class);
    Decimal128 ticketHolder = new Decimal128(Math.floor(Math.random() * (g_max_person_id.doubleValue() - g_min_person_id.doubleValue()) + g_min_person_id.doubleValue()));
    int quantity = (int) Math.floor(Math.random() * 5) + 1;
    sellTickets(ticketHolder, eventId, quantity);
}
public void transferTicket(int ticket_id, int new_ticketholder_id, boolean transfer_all, Double price) {
    int p_ticket_id = ticket_id;
    int p_new_ticketholder_id = new_ticketholder_id;
    Double p_price = price;
    int xferall = transfer_all ? 1 : 0;
    int old_ticketholder_id;
    Date last_txn_date;
    List<Document> txfr_cur;
    try {
        Document result = db.getCollection("ticketPurchaseHist").aggregate(Arrays.asList(
            Aggregates.match(Filters.eq("sportingEventTicketId", p_ticket_id)),
            Aggregates.group("$ticketholderId", Accumulators.max("transaction_date_time", "$transactionDateTime")),
            Aggregates.lookup("sportingEventTicket", "ticketholderId", "ticketholderId", "ticket"),
            Aggregates.unwind("$ticket"),
            Aggregates.match(Filters.or(
                Filters.eq("ticket.id", p_ticket_id),
                Filters.eq("xferall", 1)
            )),
            Aggregates.project(Projections.fields(
                Projections.computed("transaction_date_time", "$transaction_date_time"),
                Projections.computed("ticketholder_id", "$ticket.ticketholderId")
            ))
        )).first();
        if (result != null) {
            last_txn_date = result.getDate("transaction_date_time");
            old_ticketholder_id = result.getInteger("ticketholder_id");
            txfr_cur = db.getCollection("ticketPurchaseHist").find(Filters.and(
                Filters.eq("purchased_by_id", old_ticketholder_id),
                Filters.eq("transaction_date_time", last_txn_date)
            )).into(new ArrayList<>());
            for (Document xrec : txfr_cur) {
                db.getCollection("sportingEventTicket").updateOne(
                    Filters.eq("id", xrec.getInteger("sportingEventTicketId")),
                    Updates.set("ticketholder_id", p_new_ticketholder_id)
                );
                db.getCollection("ticketPurchaseHist").insertOne(new Document()
                    .append("sportingEventTicketId", xrec.getInteger("sportingEventTicketId"))
                    .append("purchased_by_id", p_new_ticketholder_id)
                    .append("transferred_from_id", old_ticketholder_id)
                    .append("transaction_date_time", new Date())
                    .append("purchase_price", p_price != null ? p_price : xrec.getDouble("purchase_price"))
                );
            }
        }
        db.getCollection("sportingEventTicket").getDatabase().runCommand(new Document("commitTransaction", 1));
    } catch (Exception e) {
        db.getCollection("sportingEventTicket").getDatabase().runCommand(new Document("abortTransaction", 1));
    }
}
}