def insertPedido(proveedor, idempleado, codigoSKU, precioSKU, garantia, categoria, detalleUbicacion, costo, cantidad, sucursales):
# Create random generator
gen = pydbgen.pydb()
# Connect to an existing database and open a cursor to perform database operations
conn = psycopg2.connect(dbname="postgres", user="******", password="******")
cur = conn.cursor()
cur.execute("SELECT IdProveedor FROM Proveedor WHERE Nombre = %s",
(proveedor,))
idproveedor = cur.fetchone()[0]
cur.execute("SELECT COUNT(*) FROM Pedido")
CantidadPedidos = cur.fetchone()[0]
idpedido = CantidadPedidos + 1
pedido = (idpedido, fecha, idproveedor, idempleado)
cur.execute("INSERT INTO Pedido (IdPedido, Fecha, IdProveedor, IdEncargado) VALUES (%s, %s, %s, %s)", pedido)
cur.execute("SELECT IdSKU FROM SKU WHERE Codigo = %s", (codigoSKU,))
idsku = cur.fetchone()
if idsku != None:
idsku = idsku[0]
else:
cur.execute("SELECT COUNT(*) FROM SKU")
CantidadSKUs = cur.fetchone()[0]
cur.execute("SELECT IdCategoria FROM Categoria WHERE Nombre = %s", (categoria,))
idcategoria = cur.fetchone()
estado = 1
idsku = CantidadSKUs + 1
sku = (idsku, codigoSKU, idcategoria, estado, precioSKU, fecha, garantia, detalleUbicacion)
cur.execute(
"INSERT INTO SKU (IdSKU, Codigo, IdCategoria, IdEstado, PrecioActual, FechaRegistro, Garantia, DetalleUbicacion)"
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
sku)
# Assign SKU to Sucursal
sucursalSKUs = []
for sucursal in sucursales:
sucursalSKUs.append((idsku, sucursal))
cur.executemany(
"INSERT INTO SucursalSKU (IdSKU, IdSucursal) VALUES (%s, %s)",
sucursalSKUs)
codigos = gen.gen_data_series(cantidad, data_type='ssn')
cur.execute("SELECT COUNT(*) FROM Articulo")
CantidadArticulos = cur.fetchone()[0]
articulos = []
for i in range(1, cantidad + 1):
codigo = codigos[i-1]
idArticulo = CantidadArticulos + i
articulos.append((idArticulo, idsku, codigo, costo, idpedido, 1))
cur.executemany(
"INSERT INTO Articulo (IdArticulo, IdSKU, Codigo, Costo, IdPedido, IdEstadoArticulo) VALUES (%s, %s, %s, %s, %s, %s)",
articulos)
# Make the changes to the database persistent
conn.commit()
# Close communication with the database
cur.close()
conn.close()
# dependency
# pip install pydbgen
from pydbgen import pydbgen
import pandas as pd
generator = pydbgen.pydb()
# Generate a license-plate (US style)
print(generator.license_plate())
# Generate few random names
print(generator.gen_data_series(num=10, data_type='name'))
# Generate random phone numbers
print(generator.simple_ph_num())
print(generator.gen_data_series(num=10, data_type='phone_number_full'))
# Generate a full data frame with random name, street address, SSN, date
df = generator.gen_dataframe(fields=['name', 'street_address', 'ssn', 'date'])
print(df)
df_pd = pd.DataFrame(df)
export_csv = df_pd.to_csv('export_dataframe.csv', index=None, header=True)
print('done!')
class TestDataGen(Frame):
# Populated Lists
newMenuList = ['Select','ssn', 'name','country', 'date', 'company','state','city', 'real_city', 'zipcode', 'latitude', 'longitude','name_month', 'weekday', 'year', 'time', 'date',
'email', 'phone_number_simple', 'license_plate']
personalList = ['Select' 'ssn', 'name', 'email', 'phone_number-simple', 'company', 'license_plate']
geographicList = ['Select','country', 'state', 'city', 'real_city', 'zipcode', 'latitude', 'longitude']
dateList = ['Select','name_month', 'weekday', 'year', 'time', 'date']
menuList = ['ssn', 'name', 'country', 'date', 'company']
categoriesList = ['Categories', 'Personal', 'Geographic', 'Date']
# Values Lists
entryListInput = []
entryListVals = []
gridList = []
dropDownVals = []
dropDownValsUpdate = []
# Widgets Lists
dropDownCatWidgList = []
entryListWidgets = []
deleteButtonWidgList = []
fieldDDWidgList = []
widgetRowsList = []
myDB = pydbgen.pydb()
def __init__(self, master=None):
Frame.__init__(self,master)
self.master = master
#self.grid_size = grid_size
self.init_window()
def init_window(self):
self.master.title("Test Data Generator")
self.master.config(background = 'light blue')
self.master.geometry('600x400+0+0')
self.master.lbl1 = Label(self.master, text='Field Types')
self.master.lbl1.grid(column=2, row=2, pady=10, padx = 30)
self.master.lbl1.config(font=("Hevetica", 15), background = 'light blue')
self.master.lbl = Label(self.master, text='Field Names')
self.master.lbl.grid(column=0, row=2, padx = 40)
self.master.lbl.grid_columnconfigure((0,1,2), weight = 1)
self.master.lbl.config(font=("helvetica", 15), background = 'light blue')
self.master.entRows = Entry(self.master, width = 8)
self.master.entRows.grid (column = 1, row = 1)
self.master.entRows.focus_set()
self.master.lblRows = Label(self.master, text = 'Number of rows:')
self.master.lblRows.grid(column = 0, row=1, pady=5, padx = 5)
self.master.lblRows.config(font=("Helvetica", 15), background = 'light blue')
self.master.dataBtn = Button(self.master, text = 'Generate', command = self.buttonClick)
self.master.dataBtn.grid(column = 2, row = 0, pady = 5, padx = 5)
self.master.dataBtn.configure(takefocus = 0)
self.master.addFieldBtn = Button(self.master, text = 'Add Field', command = self.addRow)
self.master.addFieldBtn.configure(takefocus = 0)
self.master.addFieldBtn.grid(column = 2, row = 1)
#Textbox for fieldnames
self.master.entryPoint = Entry(self.master, width = 13)
self.master.entryPoint.grid(column = 0, row = 3, pady = 3, padx = 3)
self.master.entryPoint.configure(takefocus = 1)
self.master.destroyRowButton = Button(self.master, text = 'X', command = self.button_grid)
self.master.destroyRowButton.config(width = 2)
self.master.destroyRowButton.grid(column = 3, row = 3)
self.master.destroyRowButton.configure(takefocus = 0)
self.master.cat1 = StringVar(self.master)
self.master.cat1.set(TestDataGen.categoriesList[0])
self.master.dropDownCat = OptionMenu(self.master, self.master.cat1, '', TestDataGen.categoriesList[0], TestDataGen.categoriesList[1], TestDataGen.categoriesList[2], TestDataGen.categoriesList[3])
self.master.dropDownCat.grid(column = 1, row = 3,pady = 3, padx = 3)
self.master.dropDownCat.configure(takefocus = 0)
self.master.fieldDropDVar = StringVar(self.master)
self.master.fieldDropDVar.set(TestDataGen.newMenuList[0])
self.master.fieldDD = OptionMenu(self.master, self.master.fieldDropDVar, '', TestDataGen.newMenuList[0], TestDataGen.newMenuList[1], TestDataGen.newMenuList[2], TestDataGen.newMenuList[3], TestDataGen.newMenuList[4], TestDataGen.newMenuList[5], TestDataGen.newMenuList[6], TestDataGen.newMenuList[7], TestDataGen.newMenuList[8], TestDataGen.newMenuList[9], TestDataGen.newMenuList[10], TestDataGen.newMenuList[11], TestDataGen.newMenuList[12], TestDataGen.newMenuList[13], TestDataGen.newMenuList[14], TestDataGen.newMenuList[15], TestDataGen.newMenuList[16], TestDataGen.newMenuList[17], TestDataGen.newMenuList[18], TestDataGen.newMenuList[19])
self.master.fieldDD.grid(column = 2, row = 3, pady = 3, padx = 3)
self.master.fieldDD.configure(takefocus = 0)
self.master.listLen = len(TestDataGen.menuList)
for i in range(self.master.listLen):
print('I value :',i)
# Value Appends
TestDataGen.dropDownVals.append(self.master.fieldDropDVar)
TestDataGen.entryListInput.append(self.master.entryPoint)
# Widget Appends
TestDataGen.dropDownCatWidgList.append(self.master.dropDownCat)
TestDataGen.fieldDDWidgList.append(self.master.fieldDD)
TestDataGen.entryListWidgets.append(self.master.entryPoint)
TestDataGen.deleteButtonWidgList.append(self.master.destroyRowButton)
self.master.mainloop
def addRow(self):
self.master.fieldDropDVarAdd = StringVar(self.master)
self.master.fieldDropDVarAdd.set(TestDataGen.newMenuList [0])
self.master.fieldDDAdd = OptionMenu(self.master, self.master.fieldDropDVarAdd, '', TestDataGen.newMenuList[0], TestDataGen.newMenuList[1], TestDataGen.newMenuList[2], TestDataGen.newMenuList[3], TestDataGen.newMenuList[4], TestDataGen.newMenuList[5], TestDataGen.newMenuList[6], TestDataGen.newMenuList[7], TestDataGen.newMenuList[8], TestDataGen.newMenuList[9], TestDataGen.newMenuList[10], TestDataGen.newMenuList[11], TestDataGen.newMenuList[12], TestDataGen.newMenuList[13], TestDataGen.newMenuList[14], TestDataGen.newMenuList[15], TestDataGen.newMenuList[16], TestDataGen.newMenuList[17], TestDataGen.newMenuList[18], TestDataGen.newMenuList[19])
self.master.entryPointAdd = Entry(self.master, width = 13)
self.master.entryPointAdd.grid(column = 0, row = self.master.grid_size()[1], pady = 3, padx = 3)
self.master.fieldDDAdd.grid(column= 2, row = self.master.grid_size()[1] - 1, pady = 3, padx = 3)
self.master.fieldDDAdd.configure(takefocus = 0)
self.master.cat1Add = StringVar(self.master)
self.master.cat1Add.set(TestDataGen.categoriesList[0])
self.master.dropDownCatAdd = OptionMenu(self.master, self.master.cat1Add, '', TestDataGen.categoriesList[0], TestDataGen.categoriesList[1], TestDataGen.categoriesList[2], TestDataGen.categoriesList[3] )
self.master.dropDownCatAdd.grid(column = 1, row =self.master.grid_size()[1] - 1,pady = 3, padx = 3)
self.master.dropDownCatAdd.configure(takefocus = 0)
self.master.destroyRowButtonAdd = Button(self.master, text = 'X', command = self.button_grid)
self.master.destroyRowButtonAdd.config(width = 2)
self.master.destroyRowButtonAdd.grid(column = 3, row = self.master.grid_size()[1] - 1)
self.master.destroyRowButtonAdd.configure(takefocus = 0)
# Added Value Appends
TestDataGen.dropDownVals.append(self.master.fieldDropDVarAdd)
TestDataGen.entryListInput.append(self.master.entryPointadd)
# Added Widget Appends
TestDataGen.dropDownCatWidgList.append(self.master.dropDownCatAdd)
TestDataGen.fieldDDWidgList.append(self.master.fieldDDAdd)
TestDataGen.entryListWidgets.append(self.master.entryPointAdd)
TestDataGen.deleteButtonWidgList.append(self.master.destroyRowButtonAdd)
def buttonClick(self):
try:
if self.master.entRows.get() == '':
messagebox.showerror(title = 'Error', message = 'Please enter number of rows.')
elif not (1 <= int(self.master.entRows.get()) <= 1000000):
messagebox.showerror(title = 'Error', message = 'Please enter a number between 1 and 1,000,000 for number of rows.')
elif self.master.entryPoint.get() == '':
messagebox.showerror(title = 'Error', message = 'Please enter field names for all fields.')
# elif self.master.entryPointAdd.get() == '':
# messagebox.showerror(title = 'Error', message = 'Please enter field names for all fields.') This Don't Work
elif len(self.master.entryPoint.get()) > 20:
messagebox.showerror(title = 'Error', message = 'Max 20 characters allowed.')
elif self.master.fieldDropDVar.get() == 'Select':
messagebox.showerror(title = 'Error', message = 'Please choose field types for all option menus.')
# elif self.master.fieldDropDVarAdd.get() == 'Select':
# messagebox.showerror(title = 'Error', message = 'Please choose field types for all option menus.') This Don't Work
else:
self.r = Tk()
self.master.entRowsvalue = self.master.entRows.get()
for entry in TestDataGen.entryListInput:
TestDataGen.entryListVals.append(entry.get())
for value in TestDataGen.dropDownVals:
TestDataGen.dropDownValsUpdate.append(value.get())
self.master.dataFrameGen = self.myDB.gen_dataframe(int(self.master.entRowsvalue), fields = TestDataGen.dropDownValsUpdate)
for i in range(len(TestDataGen.dropDownVals)):
self.master.dataFrameGen.rename(columns = {TestDataGen.dropDownValsUpdate[i] : TestDataGen.entryListVals[i]}, inplace = True)
self.master.datagenlabel = Label(self.r, text = self.master.dataFrameGen)
self.master.datagenlabel.grid(column = 0)
self.master.dataFrameGen.to_excel('excel_test.xlsx', sheet_name = 'data')
TestDataGen.dropDownValsUpdate.clear()
TestDataGen.entryListVals.clear()
self.r.mainloop()
except ValueError:
messagebox.showerror(title = 'Error', message = 'Please enter a number between 1 and 1,000,000 for number or rows.')
def button_grid(self):
print('deletion')
self.master.destroyRowButtonAdd.destroy()
self.master.entryPointAdd.destroy()
self.master.fieldDDAdd.pack()
self.master.dropDownCatAdd.grid_remove()
from faker import Faker
from pydbgen import pydbgen
import pandas as pd
from tkinter import *
from tkinter.ttk import *
from tkinter import messagebox
import os
import random
import openpyxl
myDB = pydbgen.pydb()
window = Tk()
global count
window.title("Test Data Gen")
window.config(background = 'light blue')
Label.config(window, background = 'light blue')
'''
fields = []
window.geometry('300x350')
class Fields(Frame):
def __init__(self, master=None):
Frame.__init__(self, master)
self.number = 0
self.widgets =[]
self.grid(column = 3)
self.createWidgets()
def createWidgets(self):
self.cloneButton = Button(self,text = 'clone', command = self.clone)
def random_city():
my_db = pydbgen.pydb()
return (my_db.city_real())
def createDummyData():
# Create random generator
gen = pydbgen.pydb()
# Connect to an existing database and open a cursor to perform database operations
conn = psycopg2.connect(dbname="postgres", user="******", password="******")
cur = conn.cursor()
# Create tables
sql_file = open('warehouse/schema/table_creation.sql','r', encoding='utf-8')
cur.execute(sql_file.read())
# Create procedures
sql_file = open('warehouse/queries/reports.sql','r', encoding='utf-8')
cur.execute(sql_file.read())
# Execute the inserts on each table
# Add seeds
sql_file = open('warehouse/schema/seeds.sql','r', encoding='utf-8')
cur.execute(sql_file.read())
# Fill table Camion
placas = gen.gen_data_series(CantidadCamiones, data_type='license_plate')
marcas = ["Freightliner", "International", "Kenworth", "Volvo", "Mercedes-Benz", "Hyundai"]
estado = [0, 1]
camiones = []
for i in range(1, CantidadCamiones + 1):
placa = placas[i-1]
marca = choice(marcas)
estado = randint(1, 2)
camiones.append((i, placa, marca, estado))
cur.executemany("INSERT INTO Camion (IdCamion, Placa, Marca, IdEstado) VALUES (%s, %s, %s, %s)", camiones)
# Fill tables Canton and Distrito
website = "https://ubicaciones.paginasweb.cr/provincia/"
idcanton = 1
cantones = []
iddistrito = 1
distritos = []
for provincia in range(1, 8):
r = requests.get(website + provincia.__str__() + "/cantones.json")
requestCantones = r.json()
for canton in requestCantones:
cantones.append((idcanton, requestCantones[canton], provincia))
r = requests.get(website + provincia.__str__() + "/canton/" + canton + "/distritos.json")
requestsDistritos = r.json()
for distrito in requestsDistritos:
distritos.append((iddistrito, requestsDistritos[distrito], idcanton))
iddistrito += 1
idcanton += 1
cur.executemany("INSERT INTO Canton (IdCanton, Nombre, IdProvincia) VALUES (%s, %s, %s)", cantones)
cur.executemany("INSERT INTO Distrito (IdDistrito, Nombre, IdCanton) VALUES (%s, %s, %s)", distritos)
# Add seeds2
sql_file = open('warehouse/schema/seeds2.sql','r', encoding='utf-8')
cur.execute(sql_file.read())
# #Fill tables Empleado, Persona and Direccion
empleados = []
personas = []
direcciones = []
cur.execute("SELECT COUNT(*) FROM Sucursal")
CantidadSucursales = cur.fetchone()[0]
cur.execute("SELECT COUNT(*) FROM Puesto")
CantidadPuestos = cur.fetchone()[0]
cur.execute("SELECT COUNT(*) FROM Direccion")
ContadorDireccion = cur.fetchone()[0]
detalles1 = gen.gen_data_series(CantidadEmpleados, data_type='street_address')
detalles2 = gen.gen_data_series(CantidadEmpleados, data_type='zipcode')
cedulas = gen.gen_data_series(CantidadEmpleados, data_type='ssn')
fakepersons = json.load(open("warehouse/scripts/fakepersons.json", 'r'))
for i in range(1, CantidadEmpleados + 1):
# Direccion
detalle1 = detalles1[i-1]
detalle2 = detalles2[i-1]
distrito = randint(1, len(distritos))
direcciones.append((ContadorDireccion + i, distrito, detalle1, detalle2))
# Persona
identificacion = cedulas[i-1]
nombre = fakepersons[i-1]['Nombre']
apellido1 = fakepersons[i-1]['Apellido1']
apellido2 = fakepersons[i-1]['Apellido2']
telefono = fakepersons[i-1]['Telefono']
correo = fakepersons[i-1]['Correo']
nacimiento = fakepersons[i-1]['FechaNacimiento']
registro = fakepersons[i-1]['FechaRegistro']
estado = randint(1, 2)
personas.append((i, identificacion, nombre, apellido1, apellido2, telefono, correo, nacimiento, registro, estado, ContadorDireccion + i))
# Empleados
sucursal = randint(1, CantidadSucursales)
puesto = randint(1, CantidadPuestos)
salario = randint(400000, 1500000)
fecha = fakepersons[i-1]['FechaRegistro']
estado = randint(1, 2)
empleados.append((i, i, puesto, sucursal, salario, fecha, estado))
cur.executemany("INSERT INTO Direccion (IdDireccion, IdDistrito, Detalle1, Detalle2) VALUES (%s, %s, %s, %s)", direcciones)
cur.executemany("INSERT INTO Persona (IdPersona, Identificacion, Nombre, Apellido1, Apellido2, Telefono, Correo, "
"FechaNacimiento, FechaRegistro, IdEstado, IdDireccion) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", personas)
cur.executemany("INSERT INTO Empleado (IdEmpleado, IdPersona, IdPuesto, IdSucursal, Salario, Fecha, IdEstado) "
"VALUES (%s, %s, %s, %s, %s, %s, %s)", empleados)
# Fill table Proveedor
nombres = gen.gen_data_series(CantidadProveedores, data_type='company')
proveedores = []
for i in range(1, CantidadProveedores + 1):
nombre = nombres[i-1]
estado = randint(1, 2)
proveedores.append((i, nombre, estado))
cur.executemany("INSERT INTO Proveedor (IdProveedor, Nombre, IdEstado) VALUES (%s, %s, %s)", proveedores)
# Fill table Pedido
fechas = gen.gen_data_series(CantidadPedidos, data_type='date')
pedidos = []
for i in range(1, CantidadPedidos + 1):
fecha = fechas[i-1]
proveedor = randint(1, CantidadProveedores)
encargado = randint(1, CantidadEmpleados)
estado = randint(1, 2)
pedidos.append((i, fecha, proveedor, encargado))
cur.executemany("INSERT INTO Pedido (IdPedido, Fecha, IdProveedor, IdEncargado) VALUES (%s, %s, %s, %s)", pedidos)
# Fill table SKU
codigos = gen.gen_data_series(CantidadSKUs, data_type='ssn')
fechas = gen.gen_data_series(CantidadSKUs, data_type='date')
garantias = [1, 2, 3, 12]
detalles = ['Mostrador', 'Ventana', 'Pasillo principal', 'Pasillo de acuerdo a la categoria']
cur.execute("SELECT COUNT(*) FROM Categoria")
CantidadCategorias = cur.fetchone()[0]
skus = []
for i in range(1, CantidadSKUs + 1):
codigo = codigos[i-1]
categoria = randint(1, CantidadCategorias)
estado = 1
precio = randrange(5000, 50000, 500)
fecha = fechas[i-1]
garantia = choice(garantias)
detalle = choice(detalles)
skus.append((i, codigo, categoria, estado, precio, fecha, garantia, detalle))
cur.executemany("INSERT INTO SKU (IdSKU, Codigo, IdCategoria, IdEstado, PrecioActual, FechaRegistro, Garantia, DetalleUbicacion) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s)", skus)
# Fill table SucursalSKU
sucursalSKUs = []
for sku in range(1, CantidadSKUs):
storeList = []
for i in range(1, CantidadSucursales + 1):
store = randint(1, CantidadSucursales)
if store not in storeList:
storeList.append(store)
sucursalSKUs.append((sku, store))
if random() > 0.35:
break
cur.executemany("INSERT INTO SucursalSKU (IdSKU, IdSucursal) VALUES (%s, %s)", sucursalSKUs)
# Fill table Articulo
codigos = gen.gen_data_series(CantidadArticulos, data_type='ssn')
articulos = []
for i in range(1, CantidadArticulos + 1):
sku = randint(1, len(skus))
codigo = codigos[i-1]
costo = randrange(500, 10000, 500)
pedido = randint(1, CantidadPedidos)
articulos.append((i, sku, codigo, costo, pedido, 1))
cur.executemany("INSERT INTO Articulo (IdArticulo, IdSKU, Codigo, Costo, IdPedido, IdEstadoArticulo) "
"VALUES (%s, %s, %s, %s, %s, %s)", articulos)
# Make the changes to the database persistent
conn.commit()
# Close communication with the database
cur.close()
conn.close()
def generateShipments():
# Create random generator
gen = pydbgen.pydb()
# Connect to the warehouse database and open a cursor to perform database operations
conn = psycopg2.connect(dbname="postgres", user="******", password="******")
warehousedb = conn.cursor()
# Connect to the store 1 database and open a cursor to perform database operations
branch1 = mysql.connector.connect(
host="localhost",
port="3306",
user="******",
database="Ska8-4-TEC-Alajuela",
passwd="admin"
)
branch1db = branch1.cursor()
# Connect to the store 2 database and open a cursor to perform database operations
branch2 = mysql.connector.connect(
host="0.0.0.0",
port="3307",
user="******",
database="Ska8-4-TEC-Cartago",
passwd="admin"
)
branch2db = branch2.cursor()
# Connect to the store 3 database and open a cursor to perform database operations
branch3 = mysql.connector.connect(
host="0.0.0.0",
port="3308",
user="******",
database="Ska8-4-TEC-San-Jose",
passwd="admin"
)
branch3db = branch3.cursor()
branchList = [branch1, branch2, branch3]
cursorList = [branch1db, branch2db, branch3db]
# Generate shipments with a truck
warehousedb.execute("SELECT COUNT(*) FROM Sucursal")
CantidadEnvios = warehousedb.fetchone()[0]
warehousedb.execute("SELECT IdCamion FROM camion where idestado = %s",
(1,))
camionesDisponibles = warehousedb.fetchall()
camiones = choices(camionesDisponibles, k=CantidadEnvios)
warehousedb.execute("SELECT COUNT(*) FROM Empleado")
CantidadEmpleados = warehousedb.fetchone()[0]
warehousedb.execute("SELECT COUNT(*) FROM Envio")
EnviosPrevios = warehousedb.fetchone()[0]
envios = []
for i in range(1, CantidadEnvios + 1):
idEnvio = EnviosPrevios + i
camion = camiones[i - 1][0]
encargado = randint(1, CantidadEmpleados)
envios.append((idEnvio, camion, encargado, i, fecha))
warehousedb.executemany(
"INSERT INTO Envio (IdEnvio, IdCamion, IdEncargado, IdSucursal, Fecha) VALUES (%s, %s, %s, %s, %s)",
envios)
warehousedb.execute("SELECT IdSKU FROM SKU WHERE IdEstado = 1")
skus = warehousedb.fetchall()
for sku in skus:
id = sku[0]
warehousedb.execute("SELECT IdSucursal FROM SucursalSKU WHERE IdSKU = %s", (id,))
storelist = warehousedb.fetchall()
warehousedb.execute("SELECT IdArticulo FROM Articulo WHERE idestadoarticulo = 1 AND IdSKU = %s", (id,))
itemlist = warehousedb.fetchall()
storeNeeds = []
for store in storelist:
storeId = store[0]
warehousedb.execute(
"SELECT COUNT(*) FROM Articulo WHERE IdSKU = %s AND idsucursal = %s AND IdEstadoArticulo = 2",
(id, storeId))
stock = warehousedb.fetchone()[0]
if stock < 5:
storeNeeds.append(store)
if len(itemlist) < len(storeNeeds) * 6:
warehousedb.execute(
"SELECT P1.Nombre, P2.IdEncargado, S.Codigo, S.PrecioActual, S.Garantia, C1.Nombre, S.DetalleUbicacion, A.Costo "
"FROM Pedido P2 INNER JOIN Proveedor P1 ON P2.IdProveedor = P1.IdProveedor "
"INNER JOIN Articulo A ON A.IdPedido = P2.IdPedido INNER JOIN SKU S ON S.IdSKU = A.IdSKU "
"INNER JOIN Categoria C1 ON C1.IdCategoria = S.IdCategoria WHERE S.IdSKU = %s",
(id,))
pedido = warehousedb.fetchall()[0] + (50, [])
print(pedido)
insertPedido(*pedido)
warehousedb.execute("SELECT IdArticulo FROM Articulo WHERE idestadoarticulo = 1 AND IdSKU = %s", (id,))
itemlist = warehousedb.fetchall()
ammount = 6
for store in storeNeeds:
articulos = itemlist[0:ammount]
itemlist = itemlist[ammount:]
for articulo in articulos:
id = articulo[0]
warehousedb.execute(
"UPDATE Articulo SET IdEstadoArticulo = 2, IdSucursal = %s WHERE IdArticulo = %s", (store, id))
warehousedb.execute("INSERT INTO EnvioPaquete (IdEnvio, IdArticulo) VALUES (%s, %s)",
(EnviosPrevios + store[0], id))
# Fragmentation
for i in range(0, len(branchList)):
# cursorList[i].execute("USE sk8;")
# SKUs
warehousedb.execute(
"SELECT S.* FROM SKU S INNER JOIN SucursalSKU SS ON S.IdSKU = SS.IdSKU WHERE IdSucursal = %s AND FechaRegistro = %s",
(i + 1, fecha))
skus = warehousedb.fetchall()
for sku in skus:
try:
cursorList[i].execute(
"INSERT INTO SKU (IdSKU, Codigo, IdCategoria, IdEstado, PrecioActual, FechaRegistro, Garantia, DetalleUbicacion) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
sku)
except:
print("SKU already shipped")
# Articulos
warehousedb.execute(
"SELECT A.IdArticulo, A.IdSKU, A.Codigo, A.IdEstadoArticulo FROM Articulo A INNER JOIN "
"EnvioPaquete EA ON A.IdArticulo = EA.IdArticulo INNER JOIN Envio E ON EA.IdEnvio = E.IdEnvio WHERE "
"E.IdSucursal = %s AND E.Fecha = %s",
(i + 1, fecha))
articulos = warehousedb.fetchall()
for articulo in articulos:
try:
cursorList[i].execute(
"INSERT INTO Articulo (IdArticulo, IdSKU, Codigo, IdEstadoArticulo) VALUES (%s, %s, %s, %s)",
articulo)
except:
print("Item already shipped")
try:
# Direccion
warehousedb.execute(
"SELECT D.* FROM Direccion D INNER JOIN Persona P on D.IdDireccion = P.IdDireccion INNER JOIN Empleado E "
"ON P.IdPersona = E.IdPersona WHERE E.IdSucursal = %s AND E.Fecha = %s",
(i + 1, fecha))
direcciones = warehousedb.fetchall()
cursorList[i].executemany(
"INSERT INTO Direccion (IdDireccion, IdDistrito, Detalle1, Detalle2) VALUES (%s, %s, %s, %s)",
direcciones)
# Persona
warehousedb.execute(
"SELECT P.* FROM Persona P INNER JOIN Empleado E on P.IdPersona = E.IdPersona WHERE E.IdSucursal = %s AND E.Fecha = %s",
(i + 1, fecha))
personas = warehousedb.fetchall()
cursorList[i].executemany(
"INSERT INTO Persona (IdPersona, Identificacion, Nombre, Apellido1, Apellido2, Telefono, Correo, "
"FechaNacimiento, FechaRegistro, IdEstado, IdDireccion) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
personas)
# Empleado
warehousedb.execute(
"SELECT IdEmpleado, IdPersona, IdPuesto, Salario, Fecha, IdEstado FROM Empleado WHERE IdSucursal = %s AND Fecha = %s",
(i + 1, fecha))
empleados = warehousedb.fetchall()
cursorList[i].executemany(
"INSERT INTO Empleado (IdEmpleado, IdPersona, IdPuesto, Salario, Fecha, IdEstado) VALUES (%s, %s, %s, %s, %s, %s)",
empleados)
except:
print("Error: Empleado duplicado")
branchList[i].commit()
cursorList[i].close()
branchList[i].close()
# Make the changes to the database persistent
conn.commit()
# Close communication with the database
warehousedb.close()
conn.close()
CantidadTalleres = CantidadBodegas
CantidadPasaportes = CantidadPasajeros * 2
CantidadPuestosAerolinea = 5
CantidadPuestosAeropuerto = 5
CantidadRepuestos = CantidadAviones
CantidadAvionAerolinea = CantidadAviones
CantidadAvionesBodega = CantidadAviones * 2
CantidadControladores = CantidadAviones * 1
CantidadEmpleadosAerolinea = CantidadEmpleados // 2
CantidadEmpleadosAeropuerto = CantidadEmpleados // 2
CantidadFacturas = CantidadAviones * 4
CantidadRepuestosFactura = CantidadRepuestos * CantidadFacturas
CantidadAvionesAeropuerto = CantidadAviones * CantidadAeropuertos
gen = pydbgen.pydb()
con = lite.connect("../Aeropuerto.sqlite3")
with con:
cur = con.cursor()
aerolineas = []
companies = gen.gen_data_series(CantidadAerolineas, data_type='company')
for i in range(1, CantidadAerolineas):
name = companies[i-1]
aerolineas.append((i,
name[:2].upper(),
name.capitalize()))
cur.executemany("INSERT INTO Aerolinea VALUES(?, ?, ?)", aerolineas)
def initBranches():
# Create random generator
gen = pydbgen.pydb()
# Connect to the warehouse database and open a cursor to perform database operations
conn = psycopg2.connect(dbname="postgres",
user="******",
password="******")
warehousedb = conn.cursor()
# Connect to the store 1 database and open a cursor to perform database operations
branch1 = mysql.connector.connect(host="localhost",
port="3306",
user="******",
database="Ska8-4-TEC-Alajuela",
passwd="admin")
branch1db = branch1.cursor()
# Connect to the store 2 database and open a cursor to perform database operations
branch2 = mysql.connector.connect(host="0.0.0.0",
port="3307",
user="******",
database="Ska8-4-TEC-Cartago",
passwd="admin")
branch2db = branch2.cursor()
# Connect to the store 3 database and open a cursor to perform database operations
branch3 = mysql.connector.connect(host="0.0.0.0",
port="3308",
user="******",
database="Ska8-4-TEC-San-Jose",
passwd="admin")
branch3db = branch3.cursor()
branchList = [branch1, branch2, branch3]
cursorList = [branch1db, branch2db, branch3db]
# Insert Pais, Provincia, Canton, Distrito
# Paises
warehousedb.execute("SELECT * FROM Pais")
paises = warehousedb.fetchall()
# Provincias
warehousedb.execute("SELECT * FROM Provincia")
provincias = warehousedb.fetchall()
# Cantones
warehousedb.execute("SELECT * FROM Canton")
cantones = warehousedb.fetchall()
# Distritos
warehousedb.execute("SELECT * FROM Distrito")
distritos = warehousedb.fetchall()
# Categorias
warehousedb.execute("SELECT * FROM Categoria")
categorias = warehousedb.fetchall()
# Estado
warehousedb.execute("SELECT * FROM Estado")
estados = warehousedb.fetchall()
# Estado articulo
warehousedb.execute("SELECT * FROM EstadoArticulo")
estadosArticulo = warehousedb.fetchall()
# Puestos
warehousedb.execute("SELECT * FROM Puesto")
puestos = warehousedb.fetchall()
# Metodo de pago
warehousedb.execute("SELECT * FROM MetodoPago")
metodos = warehousedb.fetchall()
for i in range(0, len(branchList)):
# Create tables
sql_file = open('branch/schema/table_creation.sql',
'r',
encoding='utf-8')
queries = sql_file.read().split(';')
for query in queries:
if query != None:
cursorList[i].execute(query)
cursorList[i].executemany(
"INSERT INTO Pais (IdPais, Nombre) VALUES (%s, %s)", paises)
cursorList[i].executemany(
"INSERT INTO Provincia (IdProvincia, Nombre, IdPais) VALUES (%s, %s, %s)",
provincias)
cursorList[i].executemany(
"INSERT INTO Canton (IdCanton, Nombre, IdProvincia) VALUES (%s, %s, %s)",
cantones)
cursorList[i].executemany(
"INSERT INTO Distrito (IdDistrito, Nombre, IdCanton) VALUES (%s, %s, %s)",
distritos)
cursorList[i].executemany(
"INSERT INTO Categoria (IdCategoria, Nombre, Descripcion) VALUES (%s, %s, %s)",
categorias)
cursorList[i].executemany(
"INSERT INTO Estado (IdEstado, Descripcion) VALUES (%s, %s)",
estados)
cursorList[i].executemany(
"INSERT INTO EstadoArticulo (IdEstadoArticulo, Nombre) VALUES (%s, %s)",
estadosArticulo)
cursorList[i].executemany(
"INSERT INTO Puesto (IdPuesto, Nombre, Descripcion) VALUES (%s, %s, %s)",
puestos)
cursorList[i].executemany(
"INSERT INTO MetodoPago (IdMetodoPago, Metodo, Descripcion) VALUES (%s, %s, %s)",
metodos)
# Direccion
warehousedb.execute(
"SELECT D.* FROM Direccion D INNER JOIN Persona P on D.iddireccion = P.iddireccion "
"INNER JOIN Empleado E on P.idpersona = E.idpersona WHERE E.idsucursal = %s",
(i + 1, ))
direcciones = warehousedb.fetchall()
cursorList[i].executemany(
"INSERT INTO Direccion (IdDireccion, IdDistrito, Detalle1, Detalle2) VALUES (%s, %s, %s, %s)",
direcciones)
# Persona
warehousedb.execute(
"SELECT P.* FROM Persona P INNER JOIN Empleado E on P.idpersona = E.idpersona WHERE E.idsucursal = %s",
(i + 1, ))
personas = warehousedb.fetchall()
cursorList[i].executemany(
"INSERT INTO Persona (IdPersona, Identificacion, Nombre, Apellido1, Apellido2, Telefono, Correo, FechaNacimiento,"
" FechaRegistro, IdEstado, IdDireccion) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
personas)
# Empleado
warehousedb.execute(
"SELECT IdEmpleado, IdPersona, IdPuesto, Salario, Fecha, IdEstado FROM Empleado WHERE IdSucursal = %s",
(i + 1, ))
empleados = warehousedb.fetchall()
cursorList[i].executemany(
"INSERT INTO Empleado (IdEmpleado, IdPersona, IdPuesto, Salario, Fecha, IdEstado) VALUES (%s, %s, %s, %s, %s, %s)",
empleados)
# SKUs
warehousedb.execute(
"SELECT sku.* FROM sku INNER JOIN sucursalsku s on sku.idsku = s.idsku WHERE idsucursal = %s "
"AND idestado = 1", (i + 1, ))
skus = warehousedb.fetchall()
cursorList[i].executemany(
"INSERT INTO SKU (IdSKU, Codigo, IdCategoria, IdEstado, PrecioActual, FechaRegistro, "
"Garantia, DetalleUbicacion) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
skus)
branchList[i].commit()
cursorList[i].close()
branchList[i].close()
# Close communication with the database
warehousedb.close()
conn.close()
