def main():
c = create_connection()
timeslots = [
('3', '01:00:00', '01:29:59'),
('4', '01:30:00', '01:59:59'),
('5', '02:00:00', '02:29:59'),
('6', '02:30:00', '02:59:59'),
('7', '03:00:00', '03:29:59'),
('8', '03:30:00', '03:59:59'),
('9', '04:00:00', '04:29:59'),
('10', '04:30:00', '04:59:59'),
('11', '05:00:00', '05:29:59'),
('12', '05:30:00', '05:59:59'),
('13', '06:00:00', '06:29:59'),
('14', '06:30:00', '06:59:59'),
('15', '07:00:00', '07:29:59'),
('16', '07:30:00', '07:59:59'),
('17', '08:00:00', '08:29:59'),
('18', '08:30:00', '08:59:59'),
('19', '09:00:00', '09:29:59'),
('20', '09:30:00', '09:59:59'),
('21', '10:00:00', '10:29:59'),
('22', '10:30:00', '10:59:59'),
('23', '11:00:00', '11:29:59'),
('24', '11:30:00', '11:59:59'),
('25', '12:00:00', '12:29:59'),
('26', '12:30:00', '12:59:59'),
('27', '13:00:00', '13:29:59'),
('28', '13:30:00', '13:59:59'),
('29', '14:00:00', '14:29:59'),
('30', '14:30:00', '14:59:59'),
('31', '15:00:00', '15:29:59'),
('32', '15:30:00', '15:59:59'),
('33', '16:00:00', '16:29:59'),
('34', '16:30:00', '16:59:59'),
('35', '17:00:00', '17:29:59'),
('36', '17:30:00', '17:59:59'),
('37', '18:00:00', '18:29:59'),
('38', '18:30:00', '18:59:59'),
('39', '19:00:00', '19:29:59'),
('40', '19:30:00', '19:59:59'),
('41', '20:00:00', '20:29:59'),
('42', '20:30:00', '20:59:59'),
('43', '21:00:00', '21:29:59'),
('44', '21:30:00', '21:59:59'),
('45', '22:00:00', '22:29:59'),
('46', '22:30:00', '22:59:59'),
('47', '23:00:00', '23:29:59'),
('48', '23:30:00', '23:59:59'),
]
sql = '''
insert into cases_timeslot(id, timeslotfr, timeslotto) values (?,?,?)
'''
try:
r = c.executemany(sql, timeslots)
print r
except Error as e:
print e
def EmptyQueue():
print('ETL STOPPED...')
conn = create_connection()
conn.execute('delete from cases_qDefect;')
conn.commit()
conn.execute('delete from cases_qDefect;')
conn.commit()
conn.close()
return None
def Init_TechnicianStatistics():
conn = create_connection()
sql_agg = '''
insert into cases_technicianStatistic(technician_id, ProblemArea, CountSolved, MeanServiceDuration)
select fixer_id,ProblemArea, count(1), avg(ServiceDuration)
from cases_caseheader
where status = 'CLOSED'
group by fixer_id,ProblemArea;
'''
conn.execute('delete from cases_technicianStatistic;')
conn.commit()
conn.execute(sql_agg)
conn.commit()
conn.close()
return None
def UpdateQueue(LastRefreshed): #Removed Closed Cases conn = create_connection() sql_del = ''' DELETE FROM cases_qDefect WHERE defect_id IN ( SELECT id FROM cases_CaseHeader WHERE Status = 'CLOSED' ); ''' conn.execute(sql_del) conn.commit() conn.close() return None
def Listen(LastRefreshed):
conn = create_connection()
sql_get = '''
insert into cases_qDefect( TimeSlot_Id, Airplane_Id, Defect_Id,
ProblemArea, Status , ServicingPort, DeadlineTime
)
select tt.id, f.airplane_id, c.id,
c.ProblemArea , 'SCHEDULED', PortTo, time(f.DateTimeTo)
FROM cases_timeslot tt
INNER JOIN flights_parking f ON time(f.DateTimeFr) BETWEEN tt.TimeSlotFr AND tt.TimeSlotTo
INNER JOIN cases_caseHeader c ON f.airplane_id = c.Airplane_id
WHERE PortTo = 'SIN'
AND c.CreatedOn > :LastRefreshed;
'''
conn.execute(sql_get,{'LastRefreshed':LastRefreshed})
conn.commit()
conn.close()
return None
def InitQueue(): conn = create_connection() sql_init = ''' insert into cases_qdefect( TimeSlot_Id, Airplane_Id, Defect_Id, ProblemArea ,Status, ServicingPort, DeadlineTime ) select tt.id, f.airplane_id, c.id, c.ProblemArea , 'SCHEDULED', PortTo, time(f.DateTimeTo) FROM cases_timeslot tt INNER JOIN flights_parking f ON time(f.DateTimeFr) BETWEEN tt.TimeSlotFr AND tt.TimeSlotTo INNER JOIN cases_caseHeader c ON f.airplane_id = c.Airplane_id WHERE PortTo = 'SIN' AND c.Status != 'CLOSED'; ''' conn.execute(sql_init) conn.commit() conn.close() return None
def main():
print ('Defect Planning STARTED...')
#Collecting Datasets and Produce Queue.
InitQueue()
#Re-Collecting Statistical Data from Past Defect List
Init_TechnicianStatistics()
while True:
LastRefreshed = datetime.now()
print('LastRefreshed: '+str(LastRefreshed))
sleep(interval)
#Listening whether there is a new incoming Defect
Listen(LastRefreshed)
#Update the Task Queue based on the latest defect Status
UpdateQueue(LastRefreshed)
#Filter only defects that requires attention. No Fixer or No Parts
#Order is determined by priority defined.
sql_pre = '''
select
q.id,
q.TimeSlot_Id ,
q.ProblemArea,
q.Defect_Id,
q.Status,
q.ServicingPort,
q.Airplane_Id,
q.DeadlineTime
from cases_qDefect q
INNER JOIN cases_CaseHeader cc
ON q.Defect_id = cc.id
WHERE q.Fixer_Id is null
OR isPartAvailable = 0
OR q.Status != 'TRANSFERRED'
ORDER BY cc.Priority;
'''
conn = create_connection()
cur = conn.cursor()
defects = cur.execute(sql_pre)
#Main Section: Processing each Defects in the working queue.
for defect in defects:
TimeLeft = datetime.strptime(str(defect[7]),'%H:%M:%S') - datetime.strptime(LastRefreshed.strftime('%H:%M:%S'),'%H:%M:%S')
#Find the fixer, first check any specialist available. if not anyone.
fixer_id = AssignSpecialist(conn,defect[1], defect[2])
if fixer_id == None:
fixer_id = AssignAny(conn,defect[0], defect[1])
#Check if part is available.
isPartAvailable = CheckPart(conn,defect[0],defect[3])
#Check if it is neccessary to transfer problem elsewhere
#e.g. if there is no part or if there is not enought time left.
nextPort = defect[5]
print defect[4]
if defect[4] == 'TRANSFERRED' or TimeLeft.seconds/60 < 30:
nextPort = Transfer(conn,defect[5],defect[6])
print(nextPort)
if nextPort == None:
nextPort = defect[5]
sql_upd = '''
UPDATE cases_qDefect
SET isPartAvailable = :isPartAvailable
,fixer_id = :fixer_id
,ServicingPort = :nextPort
WHERE id = :qid
'''
conn.execute(sql_upd,{
'qid': defect[0],
'isPartAvailable': isPartAvailable,
'fixer_id':fixer_id,
'nextPort':nextPort,
}
)
conn.commit()
sql_sync = '''
UPDATE cases_CaseHeader
SET Fixer_Id = :fixer_id
WHERE id = :defect_id
'''
conn.execute(sql_sync,
{'defect_id':defect[3],
'fixer_id':fixer_id,
}
)
conn.commit()
#Loop till there is not more tasks require attention.
conn.close()