From c0d78aadfdaba93ca6e23afb4b40386523fffbca Mon Sep 17 00:00:00 2001
From: Daniel Jones <admin@danieljon.es>
Date: Mon, 8 Apr 2019 16:45:46 +0930
Subject: added importer script to import from the old database

---
 importer/import.py | 96 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 96 insertions(+)
 create mode 100755 importer/import.py

(limited to 'importer')

diff --git a/importer/import.py b/importer/import.py
new file mode 100755
index 0000000..48c0db2
--- /dev/null
+++ b/importer/import.py
@@ -0,0 +1,96 @@
+#!/usr/bin/env python3
+
+import mysql.connector;
+
+categories = ["Primer/Base", "BB/CC", "Foundation",
+	      "Contour", "Bronzer", "Concealer",
+	      "Setting Spray", "Highlighter", "Blusher",
+	      "Eye Primer", "Brow", "Eyeliner",
+	      "Eye Shadow", "Cream Eye Shadow", "Lipstick",
+	      "Face Powder", "Mascara", "Glitter",
+	      "Glitter Glue", "Eyeshadow Palette",
+	      "Lip Liner", "Highlighter Palette",
+	      "Blush Palette", "Glitter Liner", "Pigment",
+	      "Other", "Tests"];
+
+def truncateTables(toDBCursor):
+	print("truncating tables");
+	toDBCursor.execute("SET FOREIGN_KEY_CHECKS = 0;");
+	toDBCursor.execute("TRUNCATE TABLE category");
+	toDBCursor.execute("TRUNCATE TABLE product");
+	toDBCursor.execute("SET FOREIGN_KEY_CHECKS = 1;");
+
+
+def createCategories(toDBCursor):
+	for i in categories:
+		print("creating category: {}".format(i));
+		# we can assume no one is inejcting sql queries here...
+		toDBCursor.execute("INSERT INTO category (isactive, name) VALUES(1, \"{}\");".format(i));
+
+def importData(fromDBCursor, toDBCursor):
+	'''
+	how this works:
+	get every category from our todatabase
+	loop through each product in fromdb
+	where fromdb category name matches todb category name, add product
+	after, everything not in one of them is added to the "tests" category
+	'''
+	print("importing data");
+	toDBCursor.execute("SET FOREIGN_KEY_CHECKS = 0;");
+	toDBCursor.execute("SELECT categoryid, name FROM category;");
+	fromDBCursor.execute("SELECT * from products;");
+	todb = toDBCursor.fetchall();
+	fromdb = fromDBCursor.fetchall();
+	for t in todb:
+		print("trying {}".format(t[1]));
+		for f in fromdb:
+			if f[3] == t[1]:
+				print("{} == {}".format(f, t));
+				toDBCursor.execute("INSERT INTO product "
+						   "(categoryid, comment, brand, color, "
+						   "dateadded, deleted) VALUES "
+						   "({}, \"{}\", \"{}\", \"{}\", FROM_UNIXTIME({}), {});"
+						   .format(t[0], f[6], f[4], f[5], f[1], f[2]));
+				# in our current database we only have 1 image per product
+				# so a hack like this will work....
+				try:
+					image = f[7].decode().split("\"")[1];
+					print(image);
+					print("adding {} to productid {}".format(image, t[0]));
+					toDBCursor.execute("INSERT INTO productimage "
+							   "VALUES({}, \"{}\", FROM_UNIXTIME({}))".format
+							   (t[0], image, f[1]));
+				except IndexError:
+					print("no image");
+
+	toDBCursor.execute("SET FOREIGN_KEY_CHECKS = 1;");
+def main():
+	resp = input("Running this script will REMOVE ALL DATABASE ENTRIES in the toDB database, continue? (Y/N): ");
+	if resp.upper() != 'Y':
+		exit();
+	fromDB = mysql.connector.connect(
+		host="192.168.1.17",
+		user="inventory",
+		password="inventorypassword"
+		);
+	fromDBCursor = fromDB.cursor();
+	fromDBCursor.execute("USE inventory;");
+
+	toDB = mysql.connector.connect(
+		host="localhost",
+		user="inventory",
+		password="inventorypassword"
+		);
+	toDBCursor = toDB.cursor();
+	toDBCursor.execute("USE ProductInventory;");
+
+	truncateTables(toDBCursor);
+	createCategories(toDBCursor);
+	
+	importData(fromDBCursor, toDBCursor);
+
+	toDB.commit();
+
+
+if __name__ == "__main__":
+	main();
-- 
cgit v1.2.3