partmgr/partmgr-import-partdb

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
#
# PartMgr - Part-DB V0.1.3 RW import filter
#
# Copyright 2014 Michael Buesch <m@bues.ch>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License along
# with this program; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
#

from partmgr.core.database import *

import sys
import getopt
import mysql.connector


class Partdb_Part(object):
	def __init__(self,
		     id, id_category, name, instock, mininstock,
		     comment, id_footprint, id_storeloc, id_supplier,
		     supplierpartnr):
		self.id = id
		self.id_category = id_category
		self.name = name
		self.instock = instock
		self.mininstock = mininstock
		self.comment = comment
		self.id_footprint = id_footprint
		self.id_storeloc = id_storeloc
		self.id_supplier = id_supplier
		self.supplierpartnr = supplierpartnr

class Partdb_Category(object):
	def __init__(self,
		     id, name, parentnode):
		self.id = id
		self.name = name
		self.parentnode = parentnode

class Partdb_Footprint(object):
	def __init__(self,
		     id, name):
		self.id = id
		self.name = name

class Partdb_Storeloc(object):
	def __init__(self,
		     id, name):
		self.id = id
		self.name = name

class Partdb_Supplier(object):
	def __init__(self,
		     id, name):
		self.id = id
		self.name = name

class Partdb_Price(object):
	def __init__(self,
		     id, part_id, ma, price, timestamp):
		self.id = id
		self.part_id = part_id
		self.ma = ma
		self.price = price
		self.timestamp = timestamp

def addCategories(catMap, db, conn, parentId):
	c = conn.cursor(buffered = True)
	c.execute("SELECT id, name, parentnode "
		  "FROM categories "
		  "WHERE parentnode=%s "
		  "ORDER BY name;",
		  (str(parentId),))
	for row in c:
		srcCat = Partdb_Category(*row)

		if parentId:
			parent = catMap[parentId]
		else:
			parent = None
		cat = Category(name = srcCat.name,
			       parent = parent)
		db.modifyCategory(cat)

		catMap[srcCat.id] = cat

		addCategories(catMap, db, conn, srcCat.id)
	c.close()

def addFootprints(footpMap, db, conn):
	c = conn.cursor()
	c.execute("SELECT id, name "
		  "FROM footprints;")
	for row in c:
		srcFootp = Partdb_Footprint(*row)

		footp = Footprint(name = srcFootp.name)
		db.modifyFootprint(footp)

		footpMap[srcFootp.id] = footp
	c.close()

def addSuppliers(suppMap, db, conn):
	c = conn.cursor()
	c.execute("SELECT id, name "
		  "FROM suppliers;")
	for row in c:
		srcSupp = Partdb_Supplier(*row)

		supp = Supplier(name = srcSupp.name)
		db.modifySupplier(supp)

		suppMap[srcSupp.id] = supp
	c.close()

def addLocation(locMap, db, conn):
	c = conn.cursor()
	c.execute("SELECT id, name "
		  "FROM storeloc;")
	for row in c:
		srcLoc = Partdb_Storeloc(*row)

		loc = Location(name = srcLoc.name)
		db.modifyLocation(loc)

		locMap[srcLoc.id] = loc
	c.close()

def importFromPartdb(db, conn):
	if db.countRootCategories():
		print("Error: The target database %s is not empty" %\
		      db.filename)
		return 1

	# Build the category tree.
	catMap = {} # key = partdb-id, value = Category()
	addCategories(catMap, db, conn, 0)

	# Build the footprints.
	footpMap = {} # key = partdb-id, value = Footprint()
	addFootprints(footpMap, db, conn)

	# Build the store locations.
	locMap = {} # key = partdb-id, value = Location()
	addLocation(locMap, db, conn)

	# Build the suppliers.
	suppMap = {} # key = partdb-id, value = Supplier()
	addSuppliers(suppMap, db, conn)

	# Get all prices.
	priceMap = {} # key = partdb-part-id, value = Partdb_Price()
	c = conn.cursor()
	c.execute("SELECT id, part_id, ma, preis, t "
		  "FROM preise;")
	for row in c:
		price = Partdb_Price(*row)
		priceMap[price.part_id] = price
	c.close()

	# Build the stock items.
	c = conn.cursor()
	c.execute("SELECT id, id_category, name, instock, mininstock, "
		  "comment, id_footprint, id_storeloc, id_supplier, "
		  "supplierpartnr "
		  "FROM parts;")
	for row in c:
		srcPart = Partdb_Part(*row)

		cat = catMap[srcPart.id_category]
		footp = footpMap[srcPart.id_footprint]
		supp = suppMap[srcPart.id_supplier]
		loc = locMap[srcPart.id_storeloc]

		part = Part(name = srcPart.name,
			    category = cat)
		db.modifyPart(part)

		minQty = srcPart.mininstock
		if minQty == 0:
			targetQty = 0
		else:
			targetQty = minQty + int(round((minQty * 0.25)))
			if targetQty == minQty:
				targetQty += 1
		stockItem = StockItem(name = srcPart.name,
				      description = srcPart.comment,
				      part = part,
				      category = cat,
				      footprint = footp,
				      minQuantity = minQty,
				      targetQuantity = targetQty)
		db.modifyStockItem(stockItem)

		try:
			price = priceMap[srcPart.id].price
			priceStamp = priceMap[srcPart.id].timestamp
		except KeyError:
			price = Origin.NO_PRICE
			priceStamp = 0
		origin = Origin(name = "",
				stockItem = stockItem,
				supplier = supp,
				orderCode = srcPart.supplierpartnr,
				price = price,
				priceTimeStamp = priceStamp)
		db.modifyOrigin(origin)

		storage = Storage(name = "",
				  stockItem = stockItem,
				  location = loc,
				  quantity = srcPart.instock)
		db.modifyStorage(storage)
	c.close()

	return 0

def usage():
	print("PartMgr - Part-DB-V0.1.3-RW import filter")
	print()
	print("Usage: partmgr-import-partdb TARGETFILE.pmg SQLHOST SQLUSER SQLPASSWORD [SQLDATABASE]")
	print()
	print(" TARGETFILE.pmg  The target database file to write to")
	print(" SQLHOST         The Part-DB MySQL hostname")
	print(" SQLUSER         The Part-DB MySQL username")
	print(" SQLPASSWORD     The Part-DB MySQL password")
	print(" SQLDATABASE     The Part-DB MySQL database (default: partdb)")

def main():
	try:
		(opts, args) = getopt.getopt(sys.argv[1:],
			"h",
			[ "help", ])
	except getopt.GetoptError as e:
		printError(str(e))
		usage()
		return 1
	for (o, v) in opts:
		if o in ("-h", "--help"):
			usage()
			return 0
	if len(args) < 4 or len(args) > 5:
		usage()
		return 1
	targetfile = args[0]
	host = args[1]
	username = args[2]
	password = args[3]
	if len(args) > 4:
		database = args[4]
	else:
		database = "partdb"

	app = QApplication(sys.argv)

	conn = None
	db = None
	try:
		conn = mysql.connector.connect(user = username,
					       password = password,
					       host = host,
					       database = database)
		db = Database(targetfile)
		res = importFromPartdb(db, conn)
		db.close(collectGarbage = (res == 0),
			 updateRevision = (res == 0))
		conn.close()
		return res
	except (mysql.connector.Error, PartMgrError) as e:
		try:
			if db:
				db.close(commit = False)
		except Exception:
			pass
		try:
			if conn:
				conn.close()
		except Exception:
			pass
		print(e)
		return 1

if __name__ == "__main__":
	sys.exit(main())