113 lines
4.3 KiB
Python
113 lines
4.3 KiB
Python
import csv
|
|
import os
|
|
|
|
script_dir = os.path.dirname(os.path.abspath(__file__))
|
|
csv_livo_path = os.path.join(script_dir, 'livo-boxes.csv')
|
|
csv_pienet_path = os.path.join(script_dir, 'pienet-boxes.csv')
|
|
csv_isot_path = os.path.join(script_dir, 'isot-boxes.csv')
|
|
sql_path = os.path.join(script_dir, 'boxes.sql')
|
|
|
|
insert_lines = []
|
|
|
|
with open(csv_livo_path, encoding='utf-8') as csvfile:
|
|
insert_lines.append("")
|
|
insert_lines.append("-- Livonsaari")
|
|
|
|
reader = csv.DictReader(csvfile)
|
|
rows = list(reader)
|
|
|
|
if not rows:
|
|
print("No data in CSV.")
|
|
exit(0)
|
|
|
|
for row in rows:
|
|
identifier = row['identifier']
|
|
location = row['location']
|
|
if identifier == '' or location == '':
|
|
print(f"Skipping row with empty identifier or location: {row}")
|
|
continue
|
|
|
|
insert_lines.append(f"INSERT INTO loota_customer (identifier) VALUES ('{identifier}');")
|
|
insert_lines.append(f"INSERT INTO loota_order(customer_id, location) VALUES ((SELECT id FROM loota_customer WHERE identifier = '{identifier}'), '{location}');")
|
|
|
|
# Add boxes
|
|
for date in set(row).difference({'identifier', 'location', ''}):
|
|
if row[date] == '':
|
|
continue
|
|
|
|
day = int(date.split('.')[0])
|
|
month = int(date.split('.')[1])
|
|
date_str = f"2025-{month:02}-{day:02} 10:00:00+00"
|
|
|
|
insert_lines.append(f"INSERT INTO loota_box (order_id, delivery_date) VALUES ((SELECT id FROM loota_order WHERE customer_id = (SELECT id FROM loota_customer WHERE identifier = '{identifier}')), '{date_str}');")
|
|
|
|
with open(csv_pienet_path, encoding='utf-8') as csvfile:
|
|
insert_lines.append("")
|
|
insert_lines.append("-- Pienet")
|
|
|
|
reader = csv.DictReader(csvfile)
|
|
rows = list(reader)
|
|
|
|
if not rows:
|
|
print("No data in CSV.")
|
|
exit(0)
|
|
|
|
for row in rows:
|
|
identifier = row['identifier']
|
|
location = row['location']
|
|
if identifier == '' or location == '':
|
|
print(f"Skipping row with empty identifier or location: {row}")
|
|
continue
|
|
|
|
insert_lines.append(f"INSERT INTO loota_customer (identifier) VALUES ('{identifier}');")
|
|
insert_lines.append(f"INSERT INTO loota_order(customer_id, location) VALUES ((SELECT id FROM loota_customer WHERE identifier = '{identifier}'), '{location}');")
|
|
|
|
# Add boxes
|
|
for date in set(row).difference({'identifier', 'location', ''}):
|
|
if row[date] == '':
|
|
continue
|
|
|
|
day = int(date.split('.')[0])
|
|
month = int(date.split('.')[1])
|
|
date_str = f"2025-{month:02}-{day:02} 10:00:00+00"
|
|
|
|
insert_lines.append(f"INSERT INTO loota_box (order_id, delivery_date) VALUES ((SELECT id FROM loota_order WHERE customer_id = (SELECT id FROM loota_customer WHERE identifier = '{identifier}')), '{date_str}');")
|
|
|
|
with open(csv_isot_path, encoding='utf-8') as csvfile:
|
|
insert_lines.append("")
|
|
insert_lines.append("-- Isot")
|
|
|
|
reader = csv.DictReader(csvfile)
|
|
rows = list(reader)
|
|
|
|
if not rows:
|
|
print("No data in CSV.")
|
|
exit(0)
|
|
|
|
for row in rows:
|
|
identifier = row['identifier']
|
|
location = row['location']
|
|
if identifier == '' or location == '':
|
|
print(f"Skipping row with empty identifier or location: {row}")
|
|
continue
|
|
|
|
insert_lines.append(f"INSERT INTO loota_customer (identifier) VALUES ('{identifier}');")
|
|
insert_lines.append(f"INSERT INTO loota_order(customer_id, location) VALUES ((SELECT id FROM loota_customer WHERE identifier = '{identifier}'), '{location}');")
|
|
|
|
# Add boxes
|
|
for date in set(row).difference({'identifier', 'location', ''}):
|
|
if row[date] == '':
|
|
continue
|
|
|
|
day = int(date.split('.')[0])
|
|
month = int(date.split('.')[1])
|
|
date_str = f"2025-{month:02}-{day:02} 10:00:00+00"
|
|
|
|
insert_lines.append(f"INSERT INTO loota_box (order_id, delivery_date) VALUES ((SELECT id FROM loota_order WHERE customer_id = (SELECT id FROM loota_customer WHERE identifier = '{identifier}')), '{date_str}');")
|
|
|
|
os.makedirs(os.path.dirname(sql_path), exist_ok=True)
|
|
with open(sql_path, 'w', encoding='utf-8') as f:
|
|
f.write('\n'.join(insert_lines))
|
|
|
|
print(f"Generated SQL with {len(insert_lines)} rows to {sql_path}")
|