# import mysql.connector import pymysql import json import hashlib mydb = pymysql.connect( host="127.0.0.1", user="root", password="root", db="textile" ) # Create a cursor object cursor = mydb.cursor() #login user # Input string input_string = "12345678" # Convert the string to its SHA-1 hash sha1_hash = hashlib.sha1(input_string.encode()).hexdigest().upper() print("SHA-1 Hash:", sha1_hash) email = "ibrahim.wasti@kics.edu.pk" query = "SELECT * FROM users WHERE email = %s AND password_hash = %s" cursor.execute(query, (email,sha1_hash)) userData = cursor.fetchall() userDataJson = {} for row in userData: userDataJson = { 'id':row[0], 'role':row[1], 'email':row[2], "firstName":row[3], "lastName":row[4], "password_hash":row[5], "phone_number":row[6] } cursor.execute("Select * from operators") operatorsData = cursor.fetchall() operators = [] for row in operatorsData: operators.append({"operatorsInfo":{ "id":row[0], "name":row[1], "cnic":row[2], "phone_number":row[3], "address":row[4], "skills":row[8], "tag_id":row[6], "timings" :row[7], "status_id":row[9] }}) cursor.execute("Select * from machine") machineOperatorData = cursor.fetchall() machineInfo = [] machineID = [] for row in machineOperatorData: machineID.append( { "id":row[0], "title":row[1], "device_id":row[2], "state_id":row[3], "skill":row[4] }) print(machineID) # machineInfo.append({ # "id":row[0], # "machine_title":row[1], # "device_ID":row[2], # "state_id":row[3], # "created_on":row[4], # "article":row[5] # }) # print('id'+str(machineID)) machineOps = [] # for rows in operators: # print(rows["operatorsInfo"]["id"]) for row in machineID: # print(row['id']) cursor.execute("Select * from machine_operator WHERE machine_ID ="+str(row["id"])) machine = cursor.fetchall() # print(machine) for rows in machine: machineOps.append( { "id":rows[0], "operatorID":rows[1], "machineId":rows[2], "startTime":rows[4], "endTime":rows[5] } ) machineRes = [] for row in machineID: for rows in machineOps: for oprows in operators: # print(rows[0]) if oprows["operatorsInfo"]["id"] == rows["operatorID"]: machineRes.append({ "id":row["id"], "title":row["title"], "device_Id":row["device_id"], "state_id":row["state_id"], "skill":row["skill"], "Operationdata":[{ "ID":rows["id"], "operator_ID":rows["operatorID"], "operator_name":oprows["operatorsInfo"]["name"], "machine_ID":row["id"], "machine_title":row["id"], "StartTime":rows["startTime"], "EndTime":rows["endTime"] }] }) #machineInfo # print(machineRes) query = "SELECT COUNT(*) FROM bundle" cursor.execute(query) bundlefetch = cursor.fetchall() totalBundle = bundlefetch[0][0] query = "SELECT COUNT(*) FROM bundle WHERE state =3" cursor.execute(query) completeBundle = cursor.fetchall() print("count: "+str(completeBundle[0][0])) complete = completeBundle[0][0] query = "SELECT COUNT(*) FROM bundle WHERE state =1" cursor.execute(query) createdBundle = cursor.fetchall() print("count: "+str(createdBundle[0][0])) created = createdBundle[0][0] query = "SELECT COUNT(*) FROM bundle WHERE state =2" cursor.execute(query) stitching = cursor.fetchall() print("count: "+str(stitching[0][0])) stitchings = stitching[0][0] query = "SELECT COUNT(*) FROM bundle WHERE state =4" cursor.execute(query) error = cursor.fetchall() print("count: "+str(error[0][0])) errors = error[0][0] res = { "status":True, "userInfo":userDataJson, "operators":operators, "machineInfo":machineRes, "bundleInfo":{ "Created":created, "Stitching":stitchings, "QualityError":errors } } print(res) # for row in articlefetch: # finalMachine = [] # for row in machineOps: # print(row[0]) # finalMachine.append({ # "id":row[0], # "operatorID":row[1], # "machinrID":row[2], # "startTime":row[4], # "endTime":row[5] # }) # print(machineOps) # if(rows["operatorsInfo"]["id"] == row[1]): # machineOps.append({ # "id":row[0], # "title":row[1], # "device_id":row[2], # "state_id":row[3], # "skills":row[4], # "operator_data":[ # { # "ID":22, # "operator_ID":row[1], # "operator_name":rows["operatorInfo"]["name"], # "machine_ID":row[0], # "machine_title":22, # "startTime":row[4], # "endTime":row[5], # } # ] # }) # print(machine) # print(machineOps) # print(operatorsData) # x = json.load(str(operators)) # print(operators[0]["operatorsInfo"]["id"]) # print(userDataJson) # print(userData) # Execute SQL commands getting bundle againt group # cursor.execute("SELECT * from bundle b WHERE b.articlceMainPartID="+str(396)) # groupsData = cursor.fetchall() # bundleArray = [] # for row in groupsData: # bundleArray.append({ # "bundleID":row[0], # "articleID":row[1], # "groupID":row[2], # "tagID":row[3], # "stepID":row[4], # "bundleSize":row[5], # "state":row[6], # "status":row[7], # "qa_status":row[8], # "qa_person":row[9] # }) # # x= json.load(bundleArray) # print(bundleArray) # cursor.execute('SELECT * from machineOperatorBundle mob WHERE mob.startTime IS NOT NULL AND mob.endTime IS NOT NULL AND operator_ID = 19') # # Fetch data # data = cursor.fetchall() # # Print the data # myArray = [] # operator = [] # for row in data: # operator.append(row[5]) # # myArray.append({ # # "id":row[0], # # "name":row[1], # # "email":row[2] # # }) # print(row) # print(operator) # for operators in operator: # print(operators) # cursor.execute('Select article_ID from bundle where bundle.ID='+str(operators)) # data1 = cursor.fetchall() # art ='' # for row in data1: # art = row[0] # print(art) # qwe ='' # cursor.execute('Select pieces from article where article.ID='+str(art)) # data2 = cursor.fetchall() # for row in data2: # if(len(data2) == 1 and len(operator)>1): # qwe = row[0]+row[0] # else: # if(len(data2)>1): # print(row) # qwe = row + row # else: # print(row) # qwe = row[0] # print('TotalPieces:'+str(qwe)) # Commit changes and close the connection mydb.commit() mydb.close()