import os
import time
import datetime
import json
import mysql.connector
 
import default_setting

# Get Current Path
dir_path = os.path.dirname(os.path.realpath(__file__))

def main():
# Default Connection / System Settings
    defaults = default_setting.defaultSettings()
# mySql Connector
#    connection = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
    connection = mysql.connector.connect(user='root',password='root',host='vimsrrmc',database='vantec')
    cursor = connection.cursor(dictionary=True)
    
    dataQuery = "TRUNCATE TABLE e2reconc"
    try:
        cursor.execute(dataQuery)
        connection.commit()
    except Exception as err:
        print('Error',err)

    fileName = "c:/rrmc/ZIF_LSP_STOCK.OUT"
    zifLsp = open(fileName,"r")
    for line in zifLsp:
        line = line.replace('\n','')
        line = line.replace(',','')
        line = line.replace("'",'')
        try:	  
            rrmcPart = line.split("!") [2]
            rrmcQty  = line.split("!") [3]
            rrmcPart = rrmcPart.strip()
            rrmcQty = rrmcQty.strip()
        except Exception as err:
            print("READ ERROR ",err)
            continue
        
       dataQuery = "SELECT part.id AS id,part.part_number AS part_number,part.part_description AS part_description, (SELECT SUM(CASE WHEN part.conversion_factor IS null THEN inventory_master.inventory_qty ELSE inventory_master.inventory_qty/part.conversion_factor END) FROM inventory_master WHERE inventory_master.part_id=part.id) AS inventory_qty FROM part WHERE part.part_number LIKE('%"+rrmcPart+"%')"
        try:
            cursor.execute(dataQuery)
            data = cursor.fetchall()
            if (cursor.rowcount >0):
                for row in data:
                    print('Error',err)
                    insQuery = "INSERT INTO E2RECONC (id,version,date_created,part_id,part_number,part_description,rrmc_qty,vims_qty,diff_qty,miss_qty,qa_qty,rrmc_opqty) VALUES(DEFAULT,0,now(),"+str(row['id'])+",'"+row['part_number']+"','"+row['part_description']+"',0,"+str(row['inventory_qty'])+",0-"+str(row['inventory_qty'])+",0,0,0)"
                    try:
                        cursor.execute(insQuery)
                        connection.commit()
                        print("Part Inserted",rrmcPart)
                    except Exception as err:
                        print('Error',err)
            else:
                print("Part Not Found",rrmcPart)
                insQuery = "SELECT id FROM E2RECONC WHERE part_number='"+rrmcPart+"' LIMIT 1"
                cursor.execute(dataQuery)
                data = cursor.fetchall()
                if (cursor.rowcount ==0):
                    insQuery = "INSERT INTO E2RECONC (id,version,date_created,part_id,part_number,part_description,rrmc_qty,vims_qty,diff_qty,miss_qty,qa_qty,rrmc_opqty) VALUES(DEFAULT,0,now(),null,'"+rrmcPart+"','UNKNOWN',0,0,0,0,0,0)"
                    try:
                        cursor.execute(insQuery)
                        connection.commit()
                        print("Part Inserted",rrmcPart)
                    except Exception as err:
                        print('Error',err)
        except Exception as err:
            print('Error',err)
        insQuery = "UPDATE E2RECONC SET rrmc_qty=rrmc_qty+"+str(rrmcQty)+",diff_qty=rrmc_qty-vims_qty WHERE part_number LIKE('%"+rrmcPart+"%') LIMIT 1"
        try:
            cursor.execute(insQuery)
            connection.commit()
            print("Part Updated",rrmcPart)
        except Exception as err:
            print('Error',err)
        
#        print("Part: ",rrmcPart," Qty: ",rrmcQty)
#        if (rrmcPart != ""):
#            sqlGetRrmcData(sql, rrmcPart, rrmcQty)
        
if __name__ == '__main__':
	main()