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); }