generator.py

import random
import string
import re
import datetime
from typing import List, Set, Dict, Tuple, Optional

#### Warning
#### Υπάρχουν Hardcoded range()'s, γιατί έχω μόνο 5 γεωραφικές περιοχές και κατηγορίες.
#### Δεν έχω χρόνο να τα φτιάξω όλα εντός της προθεσμίας για το 2ο παραδοτέο.
#### Επίσης, φτιάξετε πρώτα μικα datasets, να δείτε ότι είναι σωστά
#### *χωρίς να τα βάλετε στους πίνακές σας*. Αν βγάζει σωστά inserts και κάνει σωστά τις πράξεις,
#### κάντε redirect το output του script σε ένα νέο αρχείο[1] για να το περάσετε στη βάση σας.
#### [1]: https://www.google.com/search?q=how+to+redirect+output+to+a+file+in+windows&oq=how+to+redirect+output+to+a+file+in+windows&aqs=chrome..69i57j0i22i30l5j69i60l2.169j0j4&sourceid=chrome&ie=UTF-8

def get_random_string(length):
	letters = string.ascii_lowercase
	result_str = ''.join(random.choice(letters) for i in range(length))
	return result_str


def customer_generator(amount, start_at=0):
	queries = list()

	table = {
		'id_cus': None,
		'ssn': None,
		'name': None,
		'address': None,
		'phone_num': None,
		'id_zone': None
	}

	if start_at > 0:
		start_at -= 1

	for j in range(start_at, amount+start_at):

		i = j + 1

		person = table.copy()
		person['id_cus'] = i
		person['ssn'] = str(random.randint(1000000000, 9999999999)) + \
			(len(str(amount)) - len(str(i)))*'0' + str(i)
		person['name'] = 'Name' + str(i) + ' ' + get_random_string(12)
		person['address'] = str(random.randint(0, 150)) + ' Street ' + \
			get_random_string(8) + ' ' + str(random.randint(10000, 89999))
		person['phone_num'] = str(random.randint(6940000000, 6999999999))
		person['id_zone'] = str(random.randint(1, 5))

		x = '''
				INSERT INTO Customer (id_cus,ssn,name,address,phone_num,id_zone) 
				VALUES ({id_cus},'{ssn}','{name}','{address}','{phone_num}',{id_zone});
				'''.format(**person)

		x = x.replace('\t', '')

		queries.append(x)

	return queries


def gen_zone(zonelist):

	queries = list()

	table = {
		'id_zone': None,
		'zone_name': None,
		'population': None
	}

	for i in range(len(zonelist)):
		zone = table.copy()

		zone['id_zone'] = i+1
		zone['zone_name'] = zonelist[i]
		zone['population'] = random.randint(50000, 4_000_000)

		x = '''
				INSERT INTO Geo_Zone (id_zone,zone_name,population) 
				VALUES({id_zone},'{zone_name}',{population});
				'''.format(**zone)
		x = x.replace('\t', '')

		queries.append(x)

	return queries


def product_generator(amount, start_at=0):
	queries = list()

	table = {
		'sku': None,
		'pd_name': None,
		'price': None,
		'descript': None,
		'stock': None,
		'id_categ': None
	}

	if start_at > 0:
		start_at -= 1

	for j in range(start_at, amount+start_at):

		i = j + 1

		product = table.copy()
		product['sku'] = i
		product['pd_name'] = 'Product' + \
			(len(str(amount)) - len(str(i)))*'0' + str(i)
		product['price'] = str(random.randint(0, 2000))
		product['descript'] = get_random_string(40)
		product['stock'] = str(str(random.randint(0, 10000)))
		product['id_categ'] = str(random.randint(1, 5))

		x = '''
				INSERT INTO Product (sku,pd_name,price,descript,stock,id_categ) 
				VALUES ({sku},'{pd_name}',{price},'{descript}',{stock},{id_categ});
				'''.format(**product)

		x = x.replace('\t', '')

		queries.append(x)

	return queries


def supplier_generator(amount, start_at=0):
	queries = list()

	table = {
		'id_sup': None,
		'ssn': None,
		'name': None,
		'street_num': None,
		'street': None,
		'pc': None,
		'id_zone': None
	}

	if start_at > 0:
		start_at -= 1

	for j in range(start_at, amount+start_at):

		i = j + 1

		Suplier = table.copy()
		Suplier['id_sup'] = i
		Suplier['ssn'] = str(random.randint(1000000000, 9999999999)) + \
			(len(str(amount)) - len(str(i)))*'0' + str(i)
		Suplier['name'] = 'Name' + str(i) + ' ' + get_random_string(12)
		Suplier['street_num'] = str(random.randint(0, 150))
		Suplier['street'] = ' Street ' + get_random_string(8)
		Suplier['pc'] = str(random.randint(10000, 89999))
		Suplier['id_zone'] = str(random.randint(1, 5))

		x = '''
				INSERT INTO Suplier (id_sup,ssn,name,street_num,street,pc,id_zone) 
				VALUES ({id_sup},'{ssn}','{name}',{street_num},'{street}','{pc}',{id_zone}) ;
				'''.format(**Suplier)

		x = x.replace('\t', '')

		queries.append(x)

	return queries


def payment_generator(id_cus, start, end):
	queries = list()
	payment = random.randint(100, 3000)

	table = {
		'id_cus': None,
		'payday': None,
		'payment': None
	}

	(start_year, start_month) = start
	(end_year, end_month) = end

	for year in range(start_year, end_year+1):
		for month in range(start_month, 13):
			if year == end_year and month == end_month+1:
				break

			Payment = table.copy()
			Payment['id_cus'] = id_cus
			Payment['payday'] = datetime.datetime(
				year, month, 12+id_cus % 10, 12, 0, 0).strftime("%Y%m%d %H:%M:%S")
			Payment['payment'] = payment

			x = '''
						INSERT INTO Payment (id_cus,payday,payment) 
						VALUES ({id_cus},'{payday}',{payment}) ;
						'''.format(**Payment)

			x = x.replace('\t', '')

			queries.append(x)

	return queries

def gen_supplyordr(start, end, num_of_products,num_of_suppliers):
	"""
		num_of_suppliers MUST BE more that 5
	"""



	def generate_prd_sup_list(num_of_products,num_of_suppliers):
		'''
		Generates a lists of arrays. Each array represents 2-5 suppliers
		for each product.
		sups_of_pd(0) is the list of suppliers for product with id=1 (sku in my table)
		'''
		sups_of_pd = list()

		for p in range(1,num_of_products+1):
			suplie_ids = random.sample(range(1, num_of_suppliers+1), random.randint(2,5))
			sups_of_pd.append(suplie_ids)
		return sups_of_pd

	queries = list()

	table = {
		'id_sup_ordr': None,
		'date_in': None,
		'quantity': None,
		'id_sup': None,
		'sku': None
	}

	(start_year, start_month) = start
	(end_year, end_month) = end

	unique_id = 1

	sups_of_pd = generate_prd_sup_list(num_of_products,num_of_suppliers)

	for year in range(start_year, end_year+1):
		for month in range(start_month, 13):
			if year == end_year and month == end_month+1:
				break
			for sku in range(1,num_of_products+1):
				for id_sup in sups_of_pd[sku-1]:
						Supply_order = table.copy()
						Supply_order['id_sup_ordr'] = unique_id
						Supply_order['date_in'] = datetime.datetime(
							year, month, 12+id_sup % 10, 12, 0, 0).strftime("%Y%m%d %H:%M:%S")
						Supply_order['quantity'] = random.randint(100, 3000)
						Supply_order['id_sup'] = id_sup
						Supply_order['sku'] = sku

						x = '''
									INSERT INTO Supply_order (id_sup_ordr,date_in,quantity,id_sup,sku)
									VALUES ({id_sup_ordr},'{date_in}',{quantity},{id_sup},{sku}) ;
									'''.format(**Supply_order)

						x = x.replace('\t', '')
						unique_id += 1
						queries.append(x)

	return queries

def generate_partial_order(sku: int, id_ordr: int, quantity: int):
	x = '''
				INSERT INTO Part_cust_Ordr (sku,id_ordr,quantity) 
				VALUES ({0},{1},{2});
				'''.format(sku,id_ordr,quantity)
	x = x.replace('\t', '')
	return x

def generate_customer_order(id_ordr: int, 
							order_date: str, 
							order_sent: str, 
							total_cost: int, 
							id_cus: int):
	x = '''
				INSERT INTO Customer_Order (id_ordr,order_date,order_sent,total_cost,id_cus) 
				VALUES ({0},'{1}','{2}',{3},{4});
				'''.format(id_ordr, order_date, order_sent, total_cost, id_cus)
	x = x.replace('\t', '')
	return x


def random_order_generator(cus_id_range: Tuple[int, int],
							prod_prices: List[int],
							start: Tuple[int, int], 
							end: Tuple[int, int] ):
	"""Generates random orders at random times for random customers,
	with random products and random amounts for each product.
	Up to 5
	"""
	queries = list()

	date      = datetime.datetime(start[0], start[1], 1, 12, 0, 0)
	last_date = datetime.datetime(end[0],   end[1],   1, 12, 0, 0)

	id_ordr = 1

	while date < last_date:

		id_cus = random.randint(cus_id_range[0],cus_id_range[1])
		products = random.sample(range(1, len(prod_prices)+1), random.randint(1,5))

		order_sent_obj = date + datetime.timedelta(days=random.randint(1,15))

		order_date = date.strftime("%Y%m%d %H:%M:%S")
		order_sent = order_sent_obj.strftime("%Y%m%d %H:%M:%S")

		partials = []
		total_cost = 0
		for sku in products:
			quantity =  random.randint(10,2600)
			total_cost += prod_prices[sku-1]*quantity
			partials.append( generate_partial_order(sku, id_ordr, quantity) )

		queries.append( generate_customer_order(id_ordr, order_date, order_sent, total_cost, id_cus) )
		queries = queries + partials

		id_ordr += 1
		date = date + datetime.timedelta(days=random.randint(1,15))

	return queries

##############################################
##############################################
##############################################
##############################################

#### Warning
#### Υπάρχουν Hardcoded range()'s, γιατί έχω μόνο 5 γεωραφικές περιοχές και κατηγορίες.
#### Δεν έχω χρόνο να τα φτιάξω όλα εντός της προθεσμίας για το 2ο παραδοτέο.


# Examples

# Manually
# 	INSERT INTO Category (id_categ,descript)
# 	VALUES ( , '');

# Products
# for i in product_generator(10):
#       print(i)

# Geo Zones
# zones = ['Area'+str(x) for x in range(1,6)]
# for i in gen_zone(zones):
#       print(i)


# Suppliers
# for i in supplier_generator(10):
#       print(i)

# Supplier phone numbers
# TODO suplier_pnums table (not needed)

# Customers:
# for i in customer_generator(10):
#       print(i)

# Orders + partial orders
# Η λίστα prod_prices είναι η λίστα με τις τιμές των προιόντων.
# prod_prices[0] είναι η τιμή του 1ου προϊόντως,
# prod_prices[1] είναι η τιμή του 2ου προϊόντως, κλπ
# prod_prices = [ 1689, 1737, 1498, 318, 1473, 91, 1186, 1393, 289, 1136 ]
# for i in random_order_generator((1,10),prod_prices,(2011,1), (2014,12)):
# 	print(i)


# Supply_orders
# for i in gen_supplyordr((2011,1), (2014,12), 10,10):
# 	print(i)


# Manually:
# 	# INSERT INTO Best_customer (id_cus,credit,balance)
	# VALUES (,,);

# Payments:
# for j in range(1,6):
#       for i in (payment_generator(j,(2011,1),(2014,12))):
#               print(i)

Δεν έχω τεστάρει τίποτα ακόμα. Αν βρείτε λάθος παρακαλώ στείλτε μου μήνυμα ή κάντε comment.

Για το ερώτημα 13, είναι σχεδόν απίθανο να παράξει προϊόν που να πληρεί τις προϋποθέσεις!!