internal static void GenerateDriverInserts()
{
Random rng = new Random();
int drivers = rng.Next(2000, 2100);
List <string> employeeIds = null;
using (MetroDB db = new MetroDB("guest", "guest"))
{
if (db.Connect())
{
var employeeData = db.GetTable("EMPLOYEE");
employeeIds = employeeData[0];
}
else
{
Console.WriteLine("Unable to retrieve employee data. Cannot generate driver data.");
return;
}
}
List <int> driverIds = Enumerable.Range(1, 9998).ToList();
StringBuilder bldr = new StringBuilder();
for (int i = 0; i < drivers; i++)
{
int eIndex = rng.Next(employeeIds.Count);
string eId = employeeIds[eIndex];
employeeIds.RemoveAt(eIndex);
int dIndex = rng.Next(driverIds.Count);
int dId = driverIds[dIndex];
driverIds.RemoveAt(dIndex);
double rngNorm = rng.NextNormal(0, 1);
int milesDriven = (int)((3 + rngNorm) * 250000);
bldr.AppendFormat("INSERT INTO DRIVER Values ({0}, {1}, {2});\r\n", eId, dId, milesDriven);
}
string queries = bldr.ToString();
string curDir = Environment.CurrentDirectory;
File.WriteAllText(Path.Combine(curDir, "drivers.sql"), queries);
}
internal static void GenerateParkAndRideInserts()
{
//Get park and rides
Console.Write("Scraping park and rides...");
List <ParkAndRide> parkAndRides = ScrapeParkAndRides();
int max = parkAndRides.Max(t => t.Address.Length);
Console.WriteLine("Done.");
//Get routes so we can match to closest stop
List <BusStop> stops = new List <BusStop>();
using (MetroDB db = new MetroDB("guest", "guest"))
{
if (db.Connect())
{
var tableData = db.GetTable("BUS_STOP");
for (int i = 0; i < tableData[0].Count; i++)
{
stops.Add(new BusStop(tableData[1][i], tableData[2][i], int.Parse(tableData[0][i])));
}
}
else
{
Console.WriteLine("Unable to retrieve bus stop data. Cannot generate p&r data.");
return;
}
}
//Match to closest stop
Console.WriteLine("Matching Stops...");
foreach (var p in parkAndRides)
{
Console.WriteLine("Searching for stop at {0}", p.Name);
BusStop closest = ClosestStop(p, stops);
p.StopID = closest.StopID;
}
string queries = GenerateParkAndRideInserts(parkAndRides);
string curDir = Environment.CurrentDirectory;
File.WriteAllText(Path.Combine(curDir, "parkandrides.sql"), queries);
}
internal static void GenerateTransactions()
{
Random rng = new Random();
List <string> busNums = null;
using (MetroDB db = new MetroDB("guest", "guest"))
{
if (db.Connect())
{
var tableData = db.GetTable("BUS");
busNums = tableData[0];
}
else
{
Console.WriteLine("Unable to retrieve bus stop data. Cannot generate transit card data.");
return;
}
}
List <int> tids = Enumerable.Range(10000, 89999).ToList();
StringBuilder bldr = new StringBuilder();
for (int i = 0; i < 40000; i++)
{
int tIndex = rng.Next(tids.Count);
int tId = tids[tIndex];
int bIndex = rng.Next(busNums.Count);
string bNum = busNums[bIndex];
string type = rng.Next(2) == 0 ? "cash" : "card";
double dollarAmount = 2.75;
if (rng.NextDouble() < 0.25 && type.Equals("cash")) //25% of the time dont pay exact
{
switch (rng.Next(10))
{
case 0:
case 1:
case 2:
case 3:
case 4:
case 5:
dollarAmount = 5;
break;
case 6:
case 7:
case 8:
dollarAmount = 10;
break;
case 9:
dollarAmount = 20;
break;
}
}
else
{
switch (rng.Next(5))
{
case 0:
dollarAmount = 1;
break;
case 1:
dollarAmount = 1.50;
break;
case 2:
case 3:
case 4:
dollarAmount = 2.75;
break;
}
}
bldr.AppendFormat("INSERT INTO TRANSACT Values ({0}, \"{1}\", {2}, {3});\r\n", tId, type, dollarAmount, bNum);
}
string queries = bldr.ToString();
string curDir = Environment.CurrentDirectory;
File.WriteAllText(Path.Combine(curDir, "transactions.sql"), queries);
}
internal static void GenerateBusInserts()
{
Random rng = new Random();
int busCount = 1540;
List <int> availableNumbers = Enumerable.Range(1, 9998).ToList();
List <BusRoute> routes = new List <BusRoute>();
List <string> driverIds = null;
List <string> baseAddresses = null;
using (MetroDB db = new MetroDB("guest", "guest"))
{
if (db.Connect())
{
var routeData = db.GetTable("BUS_ROUTE");
for (int i = 0; i < routeData[0].Count; i++)
{
BusRoute br = new BusRoute();
br.ToName = routeData[0][i];
br.FromName = routeData[1][i];
br.RouteNumber = int.Parse(routeData[2][i]);
br.RouteName = routeData[2][i];
br.DaysOfOperation = byte.Parse(routeData[3][i]);
routes.Add(br);
}
var driverDat = db.GetTable("DRIVER");
driverIds = driverDat[0];
var basesDat = db.GetTable("BASE");
baseAddresses = basesDat[0];
}
else
{
Console.WriteLine("Unable to retrieve data. Cannot generate bus data.");
return;
}
}
StringBuilder bldr = new StringBuilder();
List <BusRoute> distro = new List <BusRoute>(routes);
for (int i = 0; i < busCount; i++)
{
int bIndex = rng.Next(availableNumbers.Count);
int bNum = availableNumbers[bIndex];
availableNumbers.RemoveAt(bIndex);
int dIndex = rng.Next(driverIds.Count);
string dId = driverIds[dIndex];
driverIds.RemoveAt(dIndex);
int rIndex = rng.Next(distro.Count);
BusRoute route = distro[rIndex];
distro.RemoveAt(rIndex);
if (distro.Count == 0)
{
distro.AddRange(routes);
}
int mIndex = rng.Next(models.Length);
string model = models[rng.Next(mIndex)];
int seatCnt = seats[mIndex];
double rngNorm = rng.NextNormal(0, 1);
int milesDriven = (int)((3 + rngNorm) * ((models.Length - mIndex) * 25000));
int baIndex = rng.Next(baseAddresses.Count);
string bAddr = baseAddresses[baIndex];
bldr.AppendFormat("INSERT INTO BUS Values ({0}, {1}, \"{2}\", \"{3}\", {4}, \"{5}\", {6}, {7}, \"{8}\");\r\n",
bNum,
dId,
route.ToName,
route.FromName,
route.RouteNumber,
model,
seatCnt,
milesDriven,
bAddr);
}
string queries = bldr.ToString();
string curDir = Environment.CurrentDirectory;
File.WriteAllText(Path.Combine(curDir, "buses.sql"), queries);
}
