#!/usr/bin/env python

import time
import os
import sys
import getpass
import Pyro

from sndcs.Config import config

engines = {1:"MySQL", 2:"PostgreSQL", 3:"MSSQL"}

def loadDB(db_engine):
    try:
        if db_engine==1:
            import MySQLdb
            return MySQLdb
        elif db_engine==2:
            import pyscopg
            return pyscopg
        elif db_engine==3:
            if not os.name=="nt":
                print "***********************************************************"
                print "Sorry you cannot run mx.ODBC.Windows on non-Windows systems"
                print "***********************************************************"
                sys.exit(1)

            import mx.ODBC.Windows
            mx.ODBC.Windows.test="testIfReal"
    except:
        print "*******************************************************"
        print "You do not have the %s python database module installed." % engines(db_engine)
        print "Please install it and try again."
        print "*******************************************************"
        sys.exit(1)


valid = 0
default = 1 
while not valid:
    print "Select your source RDBMS Engine"
    for key, value in engines.iteritems():
        print str(key) + ": " + value
    tmp = raw_input("Enter number [%s]: " % default)

    if not tmp:
        tmp = default
    try:
        db_engine = int(tmp)
        if db_engine == 3:
            typename="DSN"
        else:
            typename="host"
        valid = 1
    except ValueError:
        print "Not a valid RDBMS"
source_host = ""
while source_host=="":
    source_host = raw_input("Source database %s: " % typename)
source_user=""
while source_user=="":
    source_user = raw_input("Source database user: ")

source_password = getpass.getpass("Source database password: ")
source_database=""
while source_database=="":
    source_database = raw_input("Source database name: ")

print
print "---> Trying to connect to %s as %s..." % (source_host, source_user)

try:
    source_db = loadDB(db_engine).connect(source_host, source_user, source_password, source_database)
    source_cur = source_db.cursor()
except Exception, e:
    print "---> FAILED: Connection to database failed: %s" % (str(e))
    print
    sys.exit(1)

print "---> SUCCESS"
print


print "---> Trying to connect to destination database..."

try:
    from sndcs.Store import store
except Exception, e:
    print "---> FAILED: Connection to target database failed: %s" % (str(e))
    print

print "---> SUCCESS"
print

#f=open("../sndcs/sndcs.mkmodel/Settings.config","r")
#a=f.readlines()
#b=""
#for x in a:
#    b=b+x
#b=dict(eval(b))
#db_name=b["Database"]

#if raw_input("Do you want to drop the database %s(if it exists) first (type yesido if so): " % db_name)=="yesido": 
#   print
#   print "--->WARNING: Deleting the datbase in 5 seconds..."
#    time.sleep(5)
#    print
#    print "Deleting Database"
#    print
#    source_cur.execute("DROP DATABASE %s" % db_name)
#    print
#    print "Creating Database"
#    print
#    source_cur.execute("CREATE DATABASE %s" % db_name)
#else:
#    pass
#    print
#    print "--->Not Dropping Database"
#    print
#    time.sleep(5)


# Set the Pyro Name Server if specified in config
pyro_ns_hostname = config.get("pyro", "ns_hostname", None)
if pyro_ns_hostname:
    Pyro.config.PYRO_NS_HOSTNAME = pyro_ns_hostname


print "---> Updating shifts"
num_shift = 0
shift_map = {} # We will save a mapping of old shift id's to new id's so we can properly update the new employee records
source_cur.execute("SELECT * FROM shift")
from sndcs.ShiftFactory import ShiftFactory
from sndcs.Shift import Shift
factory=ShiftFactory()
for shift in source_cur.fetchall():
    old_id = shift[0]
    description_field=shift[1]
    active_field=bool(int(shift[2]))
    start_field=shift[3]
    end_field=shift[4]

    new = factory.new()
    new.setName(description_field)
    new.setActive(active_field)
    new.setStartTime(start_field)
    new.setEndTime(end_field)
    store.addObject(new)
    store.saveChanges()
    shift_map[old_id] = new
    num_shift += 1
print "---> Adding %s shifts" % (num_shift)

print
print "---> Updating employees..."
num_emp = 0
source_cur.execute("SELECT * FROM employees")
from sndcs.EmployeeFactory import EmployeeFactory
factory = EmployeeFactory()
for employee in source_cur.fetchall():
    # TODO: handle pictures
    new = factory.new(employee[1], firstName = employee[3], lastName = employee[2])
    try:
        shift = shift_map[employee[7]]
    except KeyError:
        shift = None
    if shift:
        new.setShift(shift)
    new.setShift_override_start(employee[8])
    new.setShift_override_end(employee[9])
    store.addObject(new)
    store.saveChanges()
    num_emp += 1
print "---> Adding %s employees." % (num_emp)


# Copy the customer records and save in new format
#print "---> Updating customers..."
#num_cust = 0
#source_cur.execute("SELECT * FROM customer_link")
#from sndcs.CustomerFactory import CustomerFactory
#from sndcs.Customer import Customer
#factory = CustomerFactory()
#for customer in source_cur.fetchall():
        #customer_field=str(customer[1])
        #
        #try:
            ## Numbering system is irrevelant in DCS 2.0?
            #int(customer_field[:4])
            #customer_field=customer_field[4:]
        #except ValueError:
            #pass
        #
        #if not store.fetchObjectsOfClass(Customer,clauses="WHERE name='%s'" % customer_field):
            #new = factory.new()
            #new.setName(customer_field)
            #new.setStatus("Active")
            #new.setCustomerId(str(num_cust))
            #store.addObject(new)
            #store.saveChanges()
            #num_cust += 1



#print "---> Adding %s customers." % (num_cust)
#store.saveChanges()


# The old customer_link table was never fully fleshed out... just add a default customer
print
print "---> Adding default customer."
from sndcs.CustomerFactory import CustomerFactory
customer = CustomerFactory().new()
customer.setName("Default Customer")
customer.setStatus("Active")
customer.setCustomerId("100")
store.addObject(customer)
store.saveChanges()


print
print "---> Adding default item."
from sndcs.ItemFactory import ItemFactory
item = ItemFactory().new()
item.setNumber("100")
item.setDescription("Default Item")
item.setSalesPrice(0)
item.setPurchasePrice(0)

store.addObject(item)
store.saveChanges()



# Copy the indirect activities
print
print "---> Updating indirect activities..."
num_indirect = 0
source_cur.execute("SELECT * FROM indirect_tasks")
from sndcs.IndirectFactory import IndirectFactory
factory = IndirectFactory()

indirect_dict = {"BREAK": 1, "LUNCH": 2}

for indirect in source_cur.fetchall():
    # TODO: handle notes
    if indirect[1] in ["BREAK", "LUNCH"]: continue
    new = factory.new(indirect[1], description = indirect[2], isActive = bool(indirect[3]))
    store.addObject(new)
    store.saveChanges()
    indirect_dict.update({str(indirect[1]):new.serialNum()})
    num_indirect += 1
print "---> Adding %s indirect activities." % (num_indirect)

# Copy tasks
print
print "---> Updating tasks..."
num_tasks = 0
task_map = {}
source_cur.execute("SELECT * FROM task")
from sndcs.Tasks import Tasks
for task in source_cur.fetchall():
    # TODO: handle notes
    new = Tasks()
    new.setCode(task[1])
    new.setDescription(task[2])
    store.addObject(new)
    store.saveChanges()
    task_map[task[1]] = new
    num_tasks += 1
print "---> Adding %s tasks." % (num_tasks)



# Copy work centers (become departments)
print
print "---> Updating departments..."
num_departments = 0
department_map = {}
source_cur.execute("SELECT * FROM wc")
from sndcs.DepartmentFactory import DepartmentFactory
factory = DepartmentFactory()
for department in source_cur.fetchall():
    # TODO: handle notes
    new = factory.new()
    new.setCode(department[1])
    new.setDescription(department[2])
    new.setActive(1)
    store.addObject(new)
    store.saveChanges()
    department_map[department[1]] = new
    num_departments += 1
print "---> Adding %s departments" % (num_departments)



# Create units of measure
print
print "---> Updating units of measure..."
num_uofm = 0
uofm_map = {}
source_cur.execute("SELECT DISTINCT um FROM jobop")
from sndcs.UnitOfMeasureFactory import UnitOfMeasureFactory
factory = UnitOfMeasureFactory()
for uofm in source_cur.fetchall():
    new = factory.new()
    new.setCode(uofm[0])
    new.setDescription(uofm[0])
    store.addObject(new)
    store.saveChanges()
    uofm_map[uofm[0]] = new
    num_uofm += 1
print "---> Adding %s units of measure" % (num_uofm)


# Copy the JobHeds
print
print "---> Preparing to update JobHeds, Assemblies, and Operations..."
source_cur.execute("SELECT jobhed.jobnum, jobcomplete, partnum, partdesc, jobpriority, customer_id, qty_required, qty_scrap, qty_complete FROM jobhed LEFT JOIN customer_link USING(jobnum)")
jobheds = source_cur.fetchall()
total_jobs = len(jobheds)
print
print "---> %d JobHeds to import..." % (total_jobs)
print "---> Updating JobHeds, Assemblies, and Operations. Please wait..."
from sndcs.JobHedFactory import JobHedFactory
from sndcs.AssemblyFactory import AssemblyFactory
from sndcs.OperationFactory import OperationFactory
job_factory = JobHedFactory()
asm_factory = AssemblyFactory()
opr_factory = OperationFactory()
num_job = 0
num_ops = 0
count_loop = 0
job_dict = {}

for jobhed in jobheds:
    job = job_factory.new()
    job.setNumber(jobhed[0])
    job.setComplete(bool(jobhed[1]))
    job.setItem(item)
    job.setItemNumber(jobhed[2])
    job.setItemDescription(jobhed[3])
    job.setPriority(jobhed[4])
    job.setQtyRequired(jobhed[6])
    job.setQtyScrap(jobhed[7])
    job.setQtyComplete(jobhed[8])
    
    #customer=jobhed[5]
    #if customer:
        #try:
            ## Numbering system is irrevelant in DCS 2.0?
            #int(customer[:4])
            #customer=customer[5:]
        #except ValueError:
            #pass
        #
        #try:
            #customer=store.fetchObjectsOfClass(Customer,"WHERE name='%s'"% customer)[0]
            #job.setCustomer(customer)
        #except:
            #pass

    job.setCustomer(customer) # Just attach the default customer
    
    store.addObject(job)
    store.saveChanges()
    job_dict.update({jobhed[0]: {} })# Create dict() item for JobHed
    
    num_job += 1
    count_loop += 1
    if count_loop == 100:
        print "---> %d of %d JobHeds imported..." % (num_job, total_jobs)
        count_loop = 0

    # Add the default assembly too
    asm = asm_factory.new()
    asm.setNumber("0")
    asm.setComplete(bool(jobhed[1]))
    asm.setDescription("No Description")
    store.addObject(asm)
    store.saveChanges()
    job.addToAssemblies(asm)
    
    job_dict[str(job.number())].update({"0": {} })# Create dict() for Assembly
    store.saveChanges()
    # Add the operations
    source_cur.execute("SELECT opr_seq, description, oprcomplete, wc_code, task_code, um FROM jobop WHERE jobnum=%s" % (jobhed[0]))
    all = source_cur.fetchall()
    if not all:
        # Create the default operation record
        opr = opr_factory.new(assembly=asm)
        opr.setNumber("0")
        opr.setComplete(bool(jobhed[1]))
        store.addObject(opr)
        store.saveChanges()
        num_ops += 1
    else:
        for operation in all:
            opr = opr_factory.new(assembly=asm)
            opr.setNumber(operation[0])
            oprDesc=operation[1]
            if not oprDesc:
                oprDesc="No Description"
            opr.setDescription(oprDesc)
            opr.setComplete(bool(jobhed[1]) or bool(operation[2]))
            opr.setConversionUnit(1)
            try:
                department = department_map[operation[3]]
            except KeyError:
                department = None
            if department:
                opr.setDepartment(department)
            try:
                task = task_map[operation[4]]
            except KeyError:
                task = None
            if task:
                opr.setTasks(task)
            try:
                uofm = uofm_map[operation[5]]
            except KeyError:
                uofm = None
            if uofm:
                opr.setUnitOfMeasure(uofm)
            store.addObject(opr)
            store.saveChanges()
            num_ops += 1
            job_dict[str(job.number())][str(asm.number())].update({str(opr.number()): opr.serialNum()})# Finally for operations

store.saveChanges()

print "---> Added %d JobHeds, %d Assemblies, and %d Operations." % (num_job, num_job, num_ops)
print

print
print "---> Preparing to update LaborHeds, and LaborDtls.."
source_cur.execute("SELECT laborhed_seq,emp_id, posted, start_stamp, end_stamp, start_terminal_id, end_terminal_id FROM laborhed")
all = source_cur.fetchall()
total_laborhed = len(all)

print
print "---> %d LaborHeds to import..." % total_laborhed
print "---> Updating LaborHeds and LaborDtls. Please wait..."

num_laborhed = 0
num_labordtl = 0
count = 0

from sndcs.LaborHedFactory import LaborHedFactory
from sndcs.LaborDtlFactory import LaborDtlFactory

from time import *
from datetime import datetime

def epochtime(x):
    year,month,day,hour,minute,second,wk,jd,ds=gmtime(int(x) + timezone)
    return datetime(year,month,day,hour,minute,second)

for laborhed in all:
    if not laborhed[6]:
        print "<-- Active LaborHed: Not Added id(%s)" % laborhed[1]
        continue #Active LaborHed

    new=LaborHedFactory().new()
    new.setEmployee( EmployeeFactory().getEmployeeByEmpId( int(laborhed[1]) ) )
    new.setPosted( bool( int(laborhed[2]) ) )

    new.setStartStamp( epochtime(laborhed[3]) )
    new.setEndStamp( epochtime(laborhed[4]) )
    
    new.setStartTerminal( laborhed[5] )
    new.setEndTerminal( laborhed[6] )

    store.addObject(new)
    source_cur.execute("SELECT jobtype,activity_percentage,qty_scrap,qty_complete,start_terminal_id,end_terminal_id,start_stamp,end_stamp,posted,indirect,jobnum,jobasm,jobop,id FROM labordtl WHERE laborhed_seq=%s" % (laborhed[0]))
    labordtls = source_cur.fetchall()
    for x in labordtls:

        if x[10] and not x[9]:
            job_type = x[0].lower()

            if job_type == "s": job_type = "Setup"
            else: job_type = "Production"
            try:
                labordtl = LaborDtlFactory().new( operation = OperationFactory().getOperationBySerialNum( int(job_dict[x[10]][x[11]][x[12]]) ), jobType = job_type )
            except Exception,e:
                print
                print "Error: Couldn't import Production/Setup labordtl entry: id(%s) jobnum(%s) asmnum(%s) opnum(%s)\nException: %s" % ( x[13],x[10],x[11],x[12], str(e) )
                continue

        elif x[9] and not x[10]:
            try:
                labordtl = LaborDtlFactory().new( indirect = IndirectFactory().getIndirectBySerialNum( indirect_dict[ x[9] ] ) ) 
            except Exception,e:
                print "Error: Couldn't import Indirect labordtl entry: id(%s) code(%s) Exception: %s" % ( x[13], x[9], str(e) )
                continue
        else:
            print
            print "No usable attributes for labordtl %s." % x[13]
            continue
        
        labordtl.setPosted( bool( int(x[8]) ) ) 
        labordtl.setActivityPercentage( x[1] )
        labordtl.setQtyScrap( int(x[2]) )
        labordtl.setQtyComplete( int(x[3]) )
        
        labordtl.setStartTerminal( x[4] )
        labordtl.setEndTerminal( x[5] )

        labordtl.setStartStamp( epochtime(x[6]) )
        labordtl.setEndStamp( epochtime(x[7]) )

        labordtl.setActiveTrans(0)
        store.addObject(labordtl)
        store.saveChanges()
        
        new.addToLaborDtls(labordtl)
        num_labordtl += 1

    store.saveChanges()
    num_laborhed += 1    
    count_loop += 1
    if count_loop == 100:
        print "---> %d of %d LaborHeds imported..." % (num_laborhed,total_laborhed)
        count_loop = 0

   

print "---> Added %d LaborHeds, and %d LaborDtls." % (num_laborhed, num_labordtl)

print "---> FINISHED!"