def dumpcheck():
cursor = Database(dbname='ics',
username='******',
password='******',
host='10.31.9.24',
port='3306')
searchbag = "SELECT DEREGISTER_DT, lpc, xbid, xpid, REGISTER_LOCATION, DEREGISTER_LOCATION, flightnr FROM FACT_BAG_SUMMARIES_V WHERE DEREGISTER_DT >= to_date( to_char( SYSDATE - 1/ ( 24 * 60 ), 'yyyy-mm-dd hh24:mi:ss' ), 'yyyy-mm-dd hh24:mi:ss' ) AND DEREGISTER_LOCATION IN ( 'M41', 'M81', 'SAT-M10a', 'SAT-M10b' ) order by DEREGISTER_DT DESC"
destinationResult = accessOracle(searchbag)
for row in destinationResult:
searchbag = "select bid from ics.dumpbag where bid = '{}'".format(
row[2])
bid = cursor.run_query(searchbag)
if not bid: # 新行李
if row[1]:
lpc = row[1]
else:
lpc = 'NULL'
if row[6]:
flight = row[6]
else:
flight = ""
adddumpBag = "insert into ics.dumpbag (created_time, lpc, bid, pid, REGISTER_LOCATION, DEREGISTER_LOCATION, flight) values ('{}', {}, '{}', {}, '{}', '{}', '{}'); ".format(
row[0], lpc, row[2], row[3], row[4], row[5], flight)
cursor.run_query(adddumpBag)
logging.info(
"the bag {} have already arrived destination:{}".format(
row[1], row[5]))
def bagdata():
bagdata = []
today = datetime.datetime.now().strftime("%Y-%m-%d")
searchbag = "select created_time,lpc,currentstation,destination, DEPAIRLINE, DEPFLIGHT, STD, status from ics.onlinebag where created_time > '{}' and status is NULL".format(
today)
cursor = Database(dbname='ics',
username='******',
password='******',
host='10.110.191.24',
port='3306')
queryResult = cursor.run_query(searchbag)
for row in queryResult:
bag_dist = {
'measurement': 'bags',
'tags': {
'createdTime': row[0],
"flight": "{}".format(row[4], row[5]),
"STD": row[6]
},
'fields': {
"lpc": float(row[1]),
"currentstation": row[2],
"destination": row[3],
"status": random.randint(0, 4)
}
}
bagdata.append(bag_dist)
return bagdata
def init():
# sqlquery = "insert into ics.plcname_contrast (plcname, sortname) values ('{}', '{}')".format(plcname_list[i], sortname_list[i])
cursor = Database(dbname='ics', username='******', password='******', host='10.31.9.24', port='3306')
for i in range(1):
# sqlquery = "insert into ics.plcname_contrast (plcname, sortname) values ('{}', '{}')".format(plcname_list[i], sortname_list[i])
addDelayBag = "insert into ics.plcname_contrast (plcname, sortname) values ('{}', '{}'); ".format("time", "time")
queryResult = cursor.run_query(addDelayBag)
print(queryResult)
def firstCheck(): # 需要补充一个STD时间距离现在不到1小时的紧急行李
before30mins = (
datetime.datetime.now() -
datetime.timedelta(minutes=30)).strftime("%Y-%m-%d %H:%M:%S")
today = datetime.datetime.now().strftime("%Y-%m-%d")
searchbag = "select lpc, created_time, DEPAIRLINE, DEPFLIGHT, STD from ics.onlinebag where created_time > '{}' and status is NULL and (created_time < '{}' or STD < NOW()+INTERVAL 1 HOUR) ".format(
today, before30mins)
cursor = Database(dbname='ics',
username='******',
password='******',
host='10.31.9.24',
port='3306')
queryResult = cursor.run_query(searchbag)
for lpc_list in queryResult:
sqlquery = "WITH cr AS ( SELECT IDEVENT FROM WC_PACKAGEINFO WHERE lpc = {} and L_DESTINATIONSTATIONID is not null ORDER BY EVENTTS DESC ) SELECT CURRENTSTATIONID, L_DESTINATIONSTATIONID FROM WC_PACKAGEINFO where IDEVENT = ( SELECT max( IDEVENT ) FROM cr )".format(
lpc_list[0])
destinationResult = accessOracle(sqlquery)
for row in destinationResult:
if row[0] == row[1]: # 当前位置就是目的地
updatebagstatus = "update onlinebag set status = 'arrived', currentstation='{}',destination = '{}' where lpc = {}".format(
row[0], row[1], lpc_list[0])
queryResult = cursor.run_query(updatebagstatus)
# logging.info(queryResult)
elif int(row[0]) in [41, 42, 81, 82, 220, 221]: # 已到达弃包处
updatebagstatus = "update onlinebag set status = 'dump', currentstation='{}',destination = '{}' where lpc = {}".format(
row[0], row[1], lpc_list[0])
queryResult = cursor.run_query(updatebagstatus)
logging.info(
"the bag:{} from flight:{}{} was dump,it is location in {}"
.format(lpc_list[0], lpc_list[2], lpc_list[3], row[0]))
elif int(row[0]) in [100, 110, 200, 210]: # 早到处
logging.info(
"the bag:{} from flight:{}{} is location in store {}".
format(lpc_list[0], lpc_list[2], lpc_list[3], row[0]))
updatebagstatus = "update onlinebag set status = 'store', currentstation='{}',destination = '{}' where lpc = {}".format(
row[0], row[1], lpc_list[0])
queryResult = cursor.run_query(updatebagstatus)
searchbag = "select lpc from ics.storebag where lpc = {}".format(
lpc_list[0])
lpc = cursor.run_query(searchbag)
if not lpc:
addStoreBag = "insert into ics.storebag (created_time, lpc, DEPAIRLINE, DEPFLIGHT, STD) values ('{}', {}, '{}', '{}', '{}'); ".format(
lpc_list[1], lpc_list[0], lpc_list[2], lpc_list[3],
lpc_list[4])
queryResult = cursor.run_query(addStoreBag)
else: # 异常行李
searchbag = "select lpc from ics.delaybag where lpc = {}".format(
lpc_list[0])
lpc = cursor.run_query(searchbag)
if not lpc:
addDelayBag = "insert into ics.delaybag (created_time, lpc, DEPAIRLINE, DEPFLIGHT, STD, currentstation, destination) values ('{}', {}, '{}', '{}', '{}', '{}', '{}'); ".format(
lpc_list[1], lpc_list[0], lpc_list[2], lpc_list[3],
lpc_list[4], row[0], row[1])
queryResult = cursor.run_query(addDelayBag)
logging.info(
"the bag:{} didn't arrive, the lastest position is {}".
format(lpc_list[0], row[0]))
def getBagNumber(localtime):
list1 = [localtime]
query = "WITH ar AS ( select _name, max( _timestamp ) time FROM baggage_collection GROUP BY _name ), br AS ( select bag.* FROM ar, baggage_collection bag WHERE bag._name = ar._name AND bag._timestamp = ar.time AND bag._timestamp > '2022-05-25 13:00:00' ) select br._name,br._value FROM br, plcname_contrast WHERE plcname_contrast.plcname = br._name ORDER BY plcname_contrast.id"
cursor = Database(dbname='test',
username='******',
password='******',
host='10.31.9.24',
port='3306')
queryResult = cursor.run_query(query)
for row in queryResult:
list1.append(int(row[1]))
return list1
def getBagNumber(localtime):
list1 = [localtime]
query = "with br as (WITH cr AS ( select _name, max( _timestamp ) time FROM baggage_collection GROUP BY _name ) select BAG._NAME, bag._VALUE FROM baggage_collection bag, cr WHERE bag._name = cr._name AND bag._timestamp = cr.time AND bag._timestamp > '2022-05-25 13:00:00' ) select br._name ,br._value from br, plcname_contrast where br._name = plcname order by plcname_contrast.id"
cursor = Database(dbname='test',
username='******',
password='******',
host='10.31.9.24',
port='3306')
queryResult = cursor.run_query(query)
for row in queryResult:
list1.append(int(row[1]))
return list1
def secondcheck():
today = datetime.datetime.now().strftime("%Y-%m-%d")
searchbag = "select lpc, created_time, DEPAIRLINE, DEPFLIGHT, STD from ics.delaybag where created_time > '{}' ".format(
today) # 暂时
cursor = Database(dbname='ics',
username='******',
password='******',
host='10.31.9.24',
port='3306')
queryResult = cursor.run_query(searchbag)
for lpc_list in queryResult:
sqlquery = "select lpc, sum( CASE WHEN EXECUTEDTASK = 'AutoScan' THEN 1 ELSE 0 END ) autoscan FROM WC_PACKAGEINFO WHERE lpc ={} AND TARGETPROCESSID LIKE 'BSIS%' and CURRENTSTATIONID in (581,582,583,584,585,586,587,588) GROUP BY lpc".format(
lpc_list[0])
Result = accessOracle(sqlquery)
if Result[0][1] > 4:
add_mulcirclebag = "insert into ics.overcirclebag (created_time, lpc, autoscantimes) values ('{}', '{}', {})".format(
today, Result[0][0], Result[0][1])
queryResult = cursor.run_query(add_mulcirclebag)
logging.info("the bag:{} cricle time is {}".format(
lpc_list[0], Result[0][1]))
def main():
currentWeek = (datetime.datetime.now() -
datetime.timedelta(days=7)).strftime("%Y%m%d")
filename = "c://work//Datacollector//weeklyreport//rsocheckdata-{}.xlsx".format(
currentWeek) # 要追加或者修改表格的文件名。
query1 = "select count(*) from ics.noread"
cursor = Database(dbname='ics',
username='******',
password='******',
host='10.31.9.24',
port='3306')
queryResult = cursor.run_query(query1)
rownumber = queryResult[0][0]
query2 = "select * from ics.noread limit {},{}".format(
int(rownumber) - 7, int(rownumber))
queryResult = cursor.run_query(query2)
columns = ["date", "departure", "arrive"]
df = pd.DataFrame(queryResult, columns=columns)
dc = df
try:
with pd.ExcelWriter(filename) as writer:
dc.to_excel(writer, sheet_name='sheet1', index=False)
except Exception as e:
logging.error(e)
"Data.PLC36.T3_Arrive_13", "Data.PLC36.T3_Arrive_14",
"Data.PLC37.T3_Arrive_15", "Data.PLC37.T3_Arrive_16",
"Data.PLC37.T3_Arrive_17", "Data.PLC37.T3_Arrive_18",
"Data.PLC04.VCC_output", "Data.PLC05.VCC_output", "Data.PLC06.VCC_output",
"Data.PLC07.VCC_output", "Data.PLC08.VCC_output", "Data.PLC20.VCC_output",
"Data.PLC21.VCC_output", "Data.PLC22.VCC_output", "Data.PLC23.VCC_output",
"Data.PLC24.VCC_output", "Data.PLC38.VCC_output", "Data.PLC39.VCC_output",
"Data.PLC3116.A_EI", "Data.PLC3115.A_EO", "Data.PLC3145.A_WI",
"Data.PLC3146.A_WO", "Data.PLC3241.D01_O", "Data.PLC3242.D02_O",
"Data.PLC3241.D01_I", "Data.PLC3242.D02_I", "Data.PLC3243.D01",
"Data.PLC3244.D02", "Data.PLC3205.A01", "Data.PLC3245.A02"
] # 输出首行
cursor = Database(dbname='test',
username='******',
password='******',
host='10.31.9.24',
port='3306')
def main():
sqlquery = "insert into test.test (plcname, sortname) values ('{}', '{}'); ".format(
"time", "time")
cursor.run_query(sqlquery)
# addDelayBag = "insert into ics.plcname_contrast (plcname, sortname) values ('{}', '{}'); ".format("time", "time")
# queryResult = cursor.run_query(addDelayBag)
if __name__ == '__main__':
main()
def bagdata():
bagdata = []
today = datetime.datetime.now().strftime("%Y-%m-%d")
# before30mins = (datetime.datetime.now() - datetime.timedelta(minutes=30)).strftime("%Y-%m-%d %H:%M:%S")
cursor = Database(dbname='ics', username='******', password='******', host='10.31.9.24', port='3306')
searchbag = "select created_time,lpc,currentstation,destination, DEPAIRLINE, DEPFLIGHT, STD, status from ics.onlinebag where created_time > '{}' and (STATUS != 'arrived' OR STATUS IS NULL) ".format(today)
queryResult = cursor.run_query(searchbag)
for row in queryResult:
try:
destination = int(row[3])
except:
if row[3] == "100,110,200,210,220,221,42,82":
destination = 1000 # 弃包和中转总称
elif row[3] == "220,221,41,42,81,82":
destination = 1001 # 弃包地
elif row[3] == "None":
logging.error("destination write error.{}".format(row[3]))
destination = None
else:
destination = 1002 # 其他异常
logging.error("1002 error")
match row[7]:
case "arrived":
status = 1
case "store":
status = 2
case "dump":
status = 3
case _:
status = 4
if len(row[5]) < 4:
flightnr = str(row[5]).zfill(4)
else:
flightnr = str(row[5])
flight = "{}{}".format(row[4], flightnr)
bag_dist = {
'measurement': 'bags',
'tags': {
'createdTime': row[0],
"flight": flight,
"STD": row[6]
},
'fields': {
"lpc": int(row[1]),
"currentstation": int(row[2]),
"destination": destination,
"status": status
}
}
bagdata.append(bag_dist)
searchdumpbag = "select created_time,lpc,bid,pid,REGISTER_LOCATION, DEREGISTER_LOCATION, flight from ics.dumpbag where created_time > '{}' ".format(today)
queryResult = cursor.run_query(searchdumpbag)
for row in queryResult:
if not row[1]:
lpc = ''
else:
lpc = row[1]
if not row[6]:
flight = ''
else:
flight = row[6]
dumpbag_dist = {
'measurement': 'dumpbags',
'tags': {
'createdTime': row[0],
"lpc": lpc,
"bid": row[2],
"REGISTER_LOCATION": row[4],
"DEREGISTER_LOCATION": row[5],
"FLIGHT": flight
},
'fields': {
"pid": int(row[3])
}
}
bagdata.append(dumpbag_dist)
searchdelaybag = "with cr as ( select lpc from delaybag where created_time > curdate() ) select created_time, onlinebag.lpc, currentstation, destination, DEPAIRLINE, DEPFLIGHT, STD, `status` from onlinebag ,cr where onlinebag.lpc = cr.lpc "
queryResult = cursor.run_query(searchdelaybag)
for row in queryResult:
if row[3] == "100,110,200,210,220,221,42,82":
destination = 1000 # 弃包和中转总称
elif row[3] == "220,221,41,42,81,82":
destination = 1001 # 弃包地
elif row[3] == "None":
logging.error("destination write error.{}".format(row[3]))
destination = None
else:
destination = int(row[3])
match row[7]:
case "arrived":
status = 1
case "store":
status = 2
case "dump":
status = 3
case _:
status = 4
delaybag_dist = {
'measurement': 'delaybags',
'tags': {
'createdTime': row[0],
"flight": "{}{}".format(row[4], row[5]),
"STD": row[6]
},
'fields': {
"lpc": int(row[1]),
"currentstation": int(row[2]),
"destination": destination,
"status": status
}
}
bagdata.append(delaybag_dist)
return bagdata
def secondcheck():
today = datetime.datetime.now().strftime("%Y-%m-%d")
searchbag = "select lpc, created_time, DEPAIRLINE, DEPFLIGHT, STD from ics.delaybag where created_time > '{}' and currentstation is not null ".format(
today) # 暂时
cursor = Database(dbname='ics',
username='******',
password='******',
host='10.31.9.24',
port='3306')
queryResult = cursor.run_query(searchbag)
for lpc_list in queryResult:
sqlquery = "WITH ar AS ( SELECT IDEVENT, EVENTTS, AREAID, ZONEID, EQUIPMENTID FROM WC_TRACKINGREPORT track WHERE lpc = {} ), br AS ( SELECT lpc, EVENTTS, ( AREAID || '.' || ZONEID || '.' || EQUIPMENTID ) location , L_DESTINATION FROM WC_TRACKINGREPORT WHERE IDEVENT = ( SELECT max( IDEVENT ) FROM ar ) ), dr AS ( SELECT CURRENTSTATIONID, IDEVENT, lpc FROM WC_PACKAGEINFO WHERE lpc = {} ) SELECT CURRENTSTATIONID, L_DESTINATION, br.EVENTTS AS latest_time, br.LOCATION FROM dr, br WHERE dr.IDEVENT = ( SELECT max( IDEVENT ) FROM dr )".format(
lpc_list[0], lpc_list[0])
destinationResult = accessOracle(sqlquery)
# print(destinationResult)
for row in destinationResult:
if row[0] == row[1]: # 当前位置就是目的地
updatebagstatus = "update onlinebag set status = 'arrived', currentstation='{}',destination = '{}' where lpc = {}".format(
row[0], row[1], lpc_list[0])
cursor.run_query(updatebagstatus)
deleteDelayBag = "DELETE FROM ics.delaybag WHERE lpc = {};".format(
lpc_list[0])
cursor.run_query(deleteDelayBag)
logging.info(
"the bag {} have already arrived destination:{} and removed from delaybag list"
.format(lpc_list[0], row[0]))
elif int(row[0]) in [41, 42, 81, 82, 220, 221]: # 已到达弃包处
updatebagstatus = "update onlinebag set status = 'dump', currentstation='{}',destination = '{}' where lpc = {}".format(
row[0], row[1], lpc_list[0])
cursor.run_query(updatebagstatus)
deleteDelayBag = "DELETE FROM ics.delaybag WHERE lpc = {};".format(
lpc_list[0])
cursor.run_query(deleteDelayBag)
logging.info(
"the bag:{} from flight:{}{} has already dumped to {} and removed from the delaybag list"
.format(lpc_list[0], lpc_list[2], lpc_list[3], row[0]))
elif int(row[0]) in [100, 110, 200, 210]: # 这部分好像不需要
logging.info(
"the bag:{} from flight:{}{} is location in store {}".
format(lpc_list[0], lpc_list[2], lpc_list[3], row[0]))
updatebagstatus = "update onlinebag set status = 'store' where lpc = {}".format(
lpc_list[0])
queryResult = cursor.run_query(updatebagstatus)
searchbag = "select lpc from ics.storebag where lpc = {}".format(
lpc_list[0])
lpc = cursor.run_query(searchbag)
if not lpc:
addStoreBag = "insert into ics.storebag (created_time, lpc, DEPAIRLINE, DEPFLIGHT, STD) values ('{}', {}, '{}', '{}', '{}'); ".format(
lpc_list[1], lpc_list[0], lpc_list[2], lpc_list[3],
lpc_list[4])
queryResult = cursor.run_query(addStoreBag)
else:
searchbag = "select currentstation from ics.delaybag where lpc = {}".format(
lpc_list[0])
currentstation = cursor.run_query(searchbag)
if currentstation != row[3]:
localTime = row[2] + datetime.timedelta(hours=8)
latest_time = localTime.strftime("%Y-%m-%d %H:%M:%S")
updatebagstatus = "update delaybag set currentstation='{}',destination = '{}', latest_time = '{}' where lpc = '{}'".format(
row[3], row[1], latest_time,
lpc_list[0]) # 更新delaybag当前位置
queryResult = cursor.run_query(updatebagstatus)
logging.info(
"the bag:{} didn't arrive, the lastest position is {}".
format(lpc_list[0], row[3]))