'All,this is code that will put production counts off machines and load them into a lean manufacturing software. This tracks production states and Preventative maintenance for machines. The code used to work until network changes started with changing v-lans and now the latest no proxy server. since then they assigned the PC which this code will be running on with a static IP not requiring it to read from the company server, nor go through a proxy. When starting Python with command line Shift right click (l2l.py) the screen is black then goes away. If the code is tested with Notepad++, the code gives errors pertaining to xlrd, then requests. I changed all upgraded the xlrd and the files are in the script folder of the pc. Still striking out to no avail. I don't do much C code, but have been searching several websites for answers. Any help would be appreciated. Thank You- Bruno'
import xlrd # module to interface with excel
import requests # module to upload the data to L2L
from datetime import datetime #module for time stamps
import os
import time
outtime = 5
import requests
session = requests.Session()
session.trust_env = false
response = session.get('http://www.graham.leading2lean.com')
from requests.packages.urllib3.exceptions import InsecureRequestWarning
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
L1Startlog = 0
L1Startgood = 0
L1Flog = 0
L1Fgood = 0
L1EndLog = 0
L1EndGood = 0
L1Err = 0
file_location = "C:/Users/brfdc/Desktop/L2L.xlsm" #location of excel file on local computer
workbook = xlrd.open_workbook(file_location)#opening the excel file specified in the previous line
sheet = workbook.sheet_by_index(0) #opening the first sheet in the excel file.
L3Sgood = sheet.cell_value(3,1) #Line 3 starting report good bottle count
#--------------------------------------------------
#Line three error checking
if (L3Sgood != 42) and (L3Slog != 42):
L3Startgood = L3Sgood
L3Startlog = L3Slog
else:
pass
if (L3Bottle != 42):
L3BottleG = L3Bottle
else:
L3BottleG = 0
#end of line 3 error checking
#--------------------------------------------------
if (L5Sgood != 42) and (L5Slog != 42): #Line 5 Error checking
L5Startgood = L5Sgood #Line 5
L5Startlog = L5Slog #Line 5
else:
pass
if (L1Sgood !=42) and (L1Slog != 42): #Line 1 Error checking
L1Startgood = L1Sgood #Line 1
L1Startlog = L1Slog #Line 1
else:
pass
if (L2Sgood != 42) and (L2Slog != 42): #Line 2 Error checking
L2Startgood = L2Sgood #Line 2
L2Startlog = L2Slog #Line 2
else:
pass
if (L7Sgood != 42) and (L7SAlog != 42) and (L7SBlog != 42): #Line 7 Error checking
L7Startgood = L7Sgood #Line 7
L7StartlogA = L7SAlog #Line 7
L7StartlogB = L7SBlog #Line 7
else:
pass
if (L6Sgood != 42) and (L6Slog != 42): #Line 6 error checking
L6Startgood = L6Sgood #Line 6
L6Startlog = L6Slog #Line 6
else:
pass
if (L8Sgood != 42) and (L8SAlog != 42) and (L8SBlog != 42): #Line 8 Error checking
L8Startgood = L8Sgood #Line 8
L8StartlogA = L8SAlog #Line 8
L8StartlogB = L8SBlog #Line 8
else:
pass
Api_End_Point = 'https://graham.leading2lean.com/api/1.0/pitchdetails/record_details/'
API_key = "c74de26eee9d5617de3218b5301d00de"
url = Api_End_Point +"?auth=" + API_key
DATETIME_SECONDS_STRING_FORMAT = "%Y-%m-%d %H:%M:%S"
testsendfinal = 0
L7StartlogT = L7StartlogA + L7StartlogB #Add the two SIPA sides together Line 7
L8StartlogT = L8StartlogA + L8StartlogB #Add the two SIPA sides together Line 8
while True:
#while True:
try:
startingTime = datetime.now().strftime(DATETIME_SECONDS_STRING_FORMAT) #assigning the current time to this variable
print(startingTime)
secs = datetime.now().strftime("%S")
mins = datetime.now().strftime("%M")
#corrective math for on the dot 30 minute marks because program drifts in time over the course of an hour.
if (int(mins) >= 27 and int(mins) < 30):
minutes_to_30 = 29 - int(mins)
minutes_to_seconds = minutes_to_30 * 60
secs_to_go = 60 - int(secs)
seconds_to_30 = secs_to_go + minutes_to_seconds
pause_time = seconds_to_30
print(seconds_to_30)
else:
pause_time = 60 #180
#--------------------------------------------------------------------------------------------------------------------------
print('start time of log ' + startingTime) #this is just printing on the screen what the time is. used for troubleshooting
L3SLoss = L3Startlog - L3Startgood #Line 3 Difference in two counters to calculate scrap
L5SLoss = L5Startlog - L5Startgood #Line 5 Difference in two counters to calculate scrap
L1SLoss = L1Startlog - L1Startgood #Line 1 Difference in two counters to calculate scrap
L2SLoss = L2Startlog - L2Startgood #Line 2 Difference in two counters to calculate scrap
L6SLoss = L6Startlog - L6Startgood #Line 6 Difference in two counters to calculate scrap
L7SLoss = L7StartlogT - L7Startgood #Line 7 Difference in two counters to calculate scrap
L8SLoss = L8StartlogT - L8Startgood #Line 8 Difference in two counters to calculate scrap
#change 6:30 and 18:30 start of report numbers. force these numbers to zero when at these two times.
hour_time = datetime.now().strftime("%H")
minute_time = datetime.now().strftime("%M")
if int(hour_time) == 6 or int(hour_time) == 18:
if int(minute_time) == 30:
L1SLoss = 0
L2SLoss = 0
L3SLoss = 0
L5SLoss = 0
L6SLoss = 0
L7SLoss = 0
L8SLoss = 0
L1Startgood = 0
L2Startgood = 0
L3Startgood = 0
L5Startgood = 0
L6Startgood = 0
L7Startgood = 0
L8Startgood = 0
L1EndGood = 0#added this 10/29/2018
L2EndGood = 0
L3EndGood = 0
L5EndGood = 0
L6EndGood = 0
L7EndGood = 0
L8EndGood = 0
L1EndLog = 0
L2EndLog = 0
L3EndLog = 0
L5EndLog = 0
L6EndLog = 0
L7EndLogA = 0
L7EndLogB = 0
L8EndLogA = 0
L8EndLogB = 0
os.system("new.bat")
else:
print("Not right minute for number force to zero")
else:
print("Not Right time for number force to zero ")
#--------------------------------------------------------------------------------------
time.sleep(pause_time) # sleep for ~3 minutes
#--------------------------------------------------------------------------------------
currentTime = datetime.now().strftime(DATETIME_SECONDS_STRING_FORMAT) #assigning the current time to this variable.
file_location = "C:/Users/brfdc/Desktop/L2L.xlsm" #location of excel file on local computer
workbook = xlrd.open_workbook(file_location) #opening the excel file specified in the previous line
sheet = workbook.sheet_by_index(0) #opening the first sheet in the excel file
L3Fgood = sheet.cell_value(3,1) #Line 3 end report good bottle count
L3Flog = sheet.cell_value(2,1) #Line 3 end report log bottle count
L5Flog = sheet.cell_value(6,1) #Line 5 end report log bottle count
L5Fgood = sheet.cell_value(7,1) #Line 5 end report good bottle count
L1Flog = sheet.cell_value(9,2) #Line 1 end report log bottle count
L1Fgood = sheet.cell_value(10,1)#Line 1 end report good bottle count
L2Fgood = sheet.cell_value(14,1)#Line 2 end report good bottle count
L2Flog = sheet.cell_value(13,1) #Line 2 end report log bottle count
L7Fgood = sheet.cell_value(21,1)#Line 7 starting report good bottle count
L7FAlog = sheet.cell_value(19,1)#Line 7 starting report log bottle count side A
L7FBlog = sheet.cell_value(20,1)#Line 7 starting report log bottle count side B
L6Flog = sheet.cell_value(17,1) #Line 6 end report log bottle count
L6Fgood = sheet.cell_value(18,1)#Line 6 end report good bottle count
L3Bottle = sheet.cell_value(3,3)#Line 3 bottle being run. 1 means running arizona
L8Fgood = sheet.cell_value(25,1)#Line 8 starting report good bottle count
L8FAlog = sheet.cell_value(23,1)#Line 8 starting report log bottle count side A
L8FBlog = sheet.cell_value(24,1)#Line 8 starting report log bottle count side B
mins_time = datetime.now().strftime("%M")
m = datetime.now().strftime("%M")
if (L3Fgood != 42) and (L3Flog != 42): #Line 3
L3EndGood = L3Fgood #Line 3
L3EndLog = L3Flog #Line 3
L3Err = 0
else:
L3Err += 1
if (L3Bottle != 42):
L3BottleG = L3Bottle
if (L5Flog != 42) and (L5Fgood != 42): #Line 5
L5EndGood = L5Fgood #Line 5
L5EndLog = L5Flog #Line 5
L5Err = 0
else:
L5Err += 1
if (L1Flog != 42) and (L1Fgood != 42): #Line 1
L1EndGood = L1Fgood #Line 1
L1EndLog = L1Flog #Line 1
L1Err = 0
else:
L1Err += 1
if (L2Fgood != 42) and (L2Flog != 42): #Line 2
L2EndGood = L2Fgood #Line 2
L2EndLog = L2Flog #Line 2
L2Err = 0
else:
L2Err += 1
if (L7Fgood != 42) and (L7FAlog != 42) and (L7FBlog != 42):
L7EndGood = L7Fgood
L7EndLogA = L7FAlog
L7EndLogB = L7FBlog
L7Err = 0
else:
L7Err += 1
if (L6Fgood != 42) and (L6Flog != 42):
L6EndGood = L6Fgood
L6EndLog = L6Flog
L6Err = 0
else:
L6Err += 1
if (L8Fgood != 42) and (L8FAlog != 42) and (L8FBlog != 42):
L8EndGood = L8Fgood
L8EndLogA = L8FAlog
L8EndLogB = L8FBlog
L8Err = 0
else:
L8Err += 1
L3EndLoss = L3EndLog - L3EndGood #Line 3 Difference in two counters at end of report to calculate scrap
L5EndLoss = L5EndLog - L5EndGood #Line 5 Difference in two counters at end of report to calculate scrap
L1EndLoss = L1EndLog - L1EndGood #Line 1 Difference in two counters at end of report to calculate scrap
L2EndLoss = L2EndLog - L2EndGood #Line 2 Difference in two counters at end of report to calculate scrap
L6EndLoss = L6EndLog - L6EndGood #Line 6 Difference in two counters at end of report to calculate scrap
L7EndLogT = L7EndLogA + L7EndLogB #Line 7 add two SIPA log bottle sides together
L7EndLoss = L7EndLogT - L7EndGood #Line 7 Difference in two counters at end of report to calculate scrap
L8EndLogT = L8EndLogA + L8EndLogB #Line 8 add two SIPA log bottle sides together
L8EndLoss = L8EndLogT - L8EndGood #Line 8 Difference in two counters at end of report to calculate scrap
#--------------------------------------------------------------------------------------
L3ChangeGood = L3EndGood - L3Startgood #Line 3 Change in starting report good bottle count and ending report good bottle count
L3ChangeLoss = L3EndLoss - L3SLoss #Line 3 Change in two different scrap numbers calculated above
L5ChangeGood = L5EndGood - L5Startgood #Line 5 Change in starting report good bottle count and ending report good bottle count
L5ChangeLoss = L5EndLoss - L5SLoss #Line 5 Change in starting report loss calculation and ending report loss calculation
L1ChangeGood = L1EndGood - L1Startgood #Line 1 Change in starting report good bottle count and ending report good bottle count
L1ChangeLoss = L1EndLoss - L1SLoss #Line 1 Change in starting report loss calculation and ending report loss calculation
L2ChangeGood = L2EndGood - L2Startgood #Line 2 Change in starting report good bottle count and ending report good bottle count
L2ChangeLoss = L2EndLoss - L2SLoss #Line 2 Change in two different scrap numbers calculated above
L6ChangeGood = L6EndGood - L6Startgood #Line 6 change in starting report good bottle count and end report good bottle count
L6ChangeLoss = L6EndLoss - L6SLoss #Line 6 change in two different scrap numbers calculated above
L7ChangeGood = L7EndGood - L7Startgood #Line 7 Change in starting report good bottle count and ending report good bottle count
L7ChangeLoss = L7EndLoss - L7SLoss #Line 7 Change in two different scrap numbers calculated above
L8ChangeGood = L8EndGood - L8Startgood #Line 8 Change in starting report good bottle count and ending report good bottle count
L8ChangeLoss = L8EndLoss - L8SLoss #Line 8 Change in two different scrap numbers calculated above
#--------------------------------------------------------------------------------------
L7DiffLogA = L7EndLogA - L7StartlogA
L7DiffLogB = L7EndLogB - L7StartlogB
L8DiffLogA = L8EndLogA - L8StartlogA
L8DiffLogB = L8EndLogB - L8StartlogB
if L7DiffLogA >= 1 and L7DiffLogB == 0:
L7Bottle = "Side A"
elif L7DiffLogB >= 1 and L7DiffLogA == 0:
L7Bottle = "Side B"
else:
L7Bottle = "Side A & B"
if L8DiffLogA >= 1 and L8DiffLogB == 0:
L8Bottle = "Side A"
elif L8DiffLogB >= 1 and L8DiffLogA == 0:
L8Bottle = "Side B"
else:
L8Bottle = "Side A & B"
#--------------------------------------------------------------------------------------
#below is logic to never send negative numbers for actual bottles and not send a relatively large amount of lost bottles
m = datetime.now().strftime("%M")
if (L3ChangeLoss < -1000) or (L3ChangeLoss>300): #Line 3
L3SendLoss = 0 #Line 3
else: #Line 3
L3SendLoss = L3ChangeLoss #Line 3
if (L3ChangeGood <0): #Line 3
if int(m) < 33 and int(m) > 30: #Line 3
L3SendGood = L3EndGood #Line 3
else: #Line 3
L3SendGood = 0 #Line 3
else: #Line 3
L3SendGood = L3ChangeGood #Line 3
if (L5ChangeLoss < -1000) or (L5ChangeLoss>600): #Line 5
L5SendLoss = 0 #Line 5
else: #Line 5
L5SendLoss = L5ChangeLoss #Line 5
if (L5ChangeGood < 0): #Line 5
if int(m) < 33 and int(m) > 30: #Line 5
L5SendGood = L5EndGood #Line 5
else: #Line 5
L5SendGood = 0 #Line 5
else: #Line 5
L5SendGood = L5ChangeGood #Line 5
if (L1ChangeLoss < -1000): #Line 1
L1SendLoss = 0 #Line 1
else: #Line 1
L1SendLoss = L1ChangeLoss #Line 1
if (L1ChangeGood < 0): #Line 1
if int(m) < 33 and int(m) > 30: #Line 1
L1SendGood = L1EndGood #Line 1
else: #Line 1
L1SendGood = 0 #Line 1
else: #Line 1
L1SendGood = L1ChangeGood #Line 1
if (L2ChangeLoss < -1000) or (L2ChangeLoss>300): #Line 2
L2SendLoss = 0 #Line 2
else: #Line 2
L2SendLoss = L2ChangeLoss #Line 2
if (L2ChangeGood <0): #Line 2
if int(m) < 33 and int(m) > 30: #Line 2
L2SendGood = L2EndGood #Line 2
else: #Line 2
L2SendGood = 0 #Line 2
else: #Line 2
L2SendGood = L2ChangeGood #Line 2
if (L6ChangeLoss<-1000) or (L6ChangeLoss>3000): #Line 6
L6SendLoss = 0 #Line 6
else: #Line 6
L6SendLoss = L6ChangeLoss #Line 6
if (L6ChangeGood <0): #Line 6
if int(m) < 33 and int(m) > 30: #Line 6
L6SendGood = L6EndGood #Line 6
else: #Line 6
L6SendGood = 0 #Line 6
else: #Line 6
L6SendGood = L6ChangeGood #Line 6
if (L7ChangeGood <0): #Line 7
if int(m) < 33 and int(m) > 30: #Line 7
L7SendGood = L7EndGood #Line 7
else: #Line 7
L7SendGood = 0 #Line 7
else: #Line 7
L7SendGood = L7ChangeGood #Line 7
if (L8ChangeGood <0): #Line 8
if int(m) < 33 and int(m) > 30: #Line 8
L8SendGood = L8EndGood #Line 8
else: #Line 8
L8SendGood = 0 #Line 8
else: #Line 8
L8SendGood = L8ChangeGood #Line 8
if (L3BottleG == 0):
RunningBottle = "Heinz"
elif (L3BottleG == 2):
RunningBottle = "Hershey"
elif (L3BottleG == 3):
RunningBottle = "RedGold"
else:
RunningBottle = "128oz Arizona"
print(L3BottleG,RunningBottle)
#--------------------------------------------------------------------------------------
parameters = {
'site': 1, # the site code for site 0123 (bradford) is 1
'linecode': '0123-BLWM-0003', # line code for line 3
'start': startingTime, # start time of data being collected
'end': currentTime, # end time of data being collected
'actual': L3SendGood, # number of good bottles going to
'scrap': L3SendLoss, # This will be determined as to what numbers i really need. I assume log count less final count.
'operation_count': 2, # number of operators on the line. usually one but have to ask if this is needed
'productcode': RunningBottle, # this will be where we put the name of product being produced. if needed.128oz Arizona Heinz
}
parametersL5 = {
'site': 1, # the site code for site 0123 (bradford) is 1
'linecode': '0123-BLWM-0005', # line code for line 5
'start': startingTime, # start time of data being collected
'end': currentTime, # end time of data being collected
'actual': L5SendGood, # number of good bottles going to
'scrap': L5SendLoss, # This will be determined as to what numbers i really need. I assume log count less final count.
'operation_count': 1, # number of operators on the line. usually one but have to ask if this is needed
'productcode': 'Bottle', # this will be where we put the name of product being produced. if needed.
}
parametersL1 = {
'site': 1, # the site code for site 0123 (bradford) is 1
'linecode': '0123-BLWM-0001', # line code for line 1
'start': startingTime, # start time of data being collected
'end': currentTime, # end time of data being collected
'actual': L1SendGood, # number of good bottles going to
'scrap': L1SendLoss, # This will be determined as to what numbers i really need. I assume log count less final count.
'operation_count': 1, # number of operators on the line. usually one but have to ask if this is needed
'productcode': 'Old Country', # this will be where we put the name of product being produced. if needed.
}
parametersL2 = {
'site': 1, # the site code for site 0123 (bradford) is 1
'linecode': '0123-BLWM-0002', # line code for line 2
'start': startingTime, # start time of data being collected
'end': currentTime, # end time of data being collected
'actual': L2SendGood, # number of good bottles going to
'scrap': L2SendLoss, # This will be determined as to what numbers i really need. I assume log count less final count.
'operation_count': 1, # number of operators on the line. usually one but have to ask if this is needed
'productcode': '128oz Arizona', # this will be where we put the name of product being produced. if needed.
}
parametersL7 = {
'site': 1, # the site code for site 0123 (bradford) is 1
'linecode': '0123-BLWM-0007', # line code for line 7
'start': startingTime, # start time of data being collected
'end': currentTime, # end time of data being collected
'actual': L7SendGood, # number of good bottles going to
'scrap': L7ChangeLoss, # This will be determined as to what numbers i really need. I assume log count less final count.
'operation_count': 1, # number of operators on the line. usually one but have to ask if this is needed
'productcode': L7Bottle, # this will be where we put the name of product being produced. if needed.
}
parametersL8 = {
'site': 1, # the site code for site 0123 (bradford) is 1
'linecode': '0123-BLWM-0008', # line code for line 7
'start': startingTime, # start time of data being collected
'end': currentTime, # end time of data being collected
'actual': L8SendGood, # number of good bottles going to
'scrap': L8ChangeLoss, # This will be determined as to what numbers i really need. I assume log count less final count.
'operation_count': 1, # number of operators on the line. usually one but have to ask if this is needed
'productcode': L8Bottle, # this will be where we put the name of product being produced. if needed.
}
parametersL6 = {
'site': 1, # the site code for site 0123 (bradford) is 1
'linecode': '0123-BLWM-0006', # line code for line 7
'start': 'startingTime', # start time of data being collected
'end': 'currentTime', # end time of data being collected
'actual': L6SendGood, # number of good bottles going to
'scrap': L6ChangeLoss, # This will be determined as to what numbers i really need. I assume log count less final count.
'operation_count': 1, # number of operators on the line. usually one but have to ask if this is needed
'productcode': "L6Bottle", # this will be where we put the name of product being produced. if needed.
}
if (L3SendGood == 0) and (L3SendLoss == 0):
print("Zero numbers no Send Line 3") #This is just for my entertainment
else:
print("sending L3")
three = requests.post(url, proxies=proxies, data=parameters,verify=False,timeout=out-time) #line actually making the call to upload the log
#print(three)
L3list = three.text
if L3list.find("true") != -1:
L3Startgood = L3EndGood #RT is running total. this will be for bottle count correction
L3Startlog = L3EndLog
print("L3 good")
else:
print("Number didn-t go through. Will not update starting numbers.")
if (L5SendGood == 0) and (L5SendLoss == 0):
print("Zero numbers no Send Line 5")
else:
print("sending L5")
five = requests.post(url, data=parametersL5,verify=False,timeout=out-time)
#print(five)
L5list = five.text
if L5list.find("true") != -1:
L5Startgood = L5EndGood
L5Startlog = L5EndLog
print("L5 good")
else:
Print("L5 numbers didn't go through. Will not update starting numbers.")
if (L1SendGood == 0) and (L1SendLoss == 0):
print("Zero numbers no Send Line 1")
else:
print("sending L1")
one = requests.post(url, data=parametersL1,verify=False,timeout=out-time)
#print(one)
L1list = one.text
if L1list.find("true") != -1:
L1Startgood = L1EndGood
L1Startlog = L1EndLog
print("L1 good")
else:
print("L1 numbers didn't go through. Will not update starting numbers.")
if (L2SendGood == 0) and (L2SendLoss == 0):
print("Zero numbers no Send Line 2")
else:
print("sending L2")
two = requests.post(url, data=parametersL2,verify=False,timeout=out-time)
#print(two)
L2list = two.text
if L2list.find("true") != -1:
L2Startgood = L2EndGood
L2Startlog = L2EndLog
print("L2 good")
else:
print("L2 numbers didn't go through. Will not update starting numbers.")
if (L7SendGood == 0) and (L7ChangeLoss == 0):
print("Zero numbers no Send Line 7")
else:
print("sending L7")
seven = requests.post(url, data=parametersL7,verify=False,timeout=out-time)
#print(seven)
L7list = seven.text
if L7list.find("true") != -1:
L7Startgood = L7EndGood
L7StartlogT = L7EndLogT
print("L7 good")
else:
print("L7 numbers didn't go through. Will not update starting numbers.")
if (L8SendGood == 0) and (L8ChangeLoss == 0):
print("Zero numbers no Send Line 8")
else:
print("sending L8")
eight = requests.post(url, data=parametersL8,verify=False,timeout=out-time)
#print(eight)
L8list = eight.text
if L8list.find("true") != -1:
L8Startgood = L8EndGood
L8StartlogT = L8EndLogT
print("L8 good")
else:
print("L8 numbers didn't go through. Will not update starting numbers.")
if (L6SendGood == 0) and (L6SendLoss == 0):
print("Zero numbers no Send Line 6") #This is just for my entertainment
else:
print("sending L6")
six = requests.post(url, data=parametersL6,verify=False,timeout=out-time) #line actually making the call to upload the log
#print(six)
L6list = six.text
if L6list.find("true") != -1:
L6Startgood = L6EndGood
L6Startlog = L6EndLog
print("L6 good")
else:
print("Number didn't go through. Will not update starting numbers.")
except Exception as e:
print(e)
