def getDetailList_update4(model_name,name,line_cd, process_name, datatype_id, limit, start_time, end_time):
try:
row = model_setting.getProcess(model_name, name)
INSPECT = (row[0]['INSPECT']).split(',')
str_inspect = ""
for item in INSPECT:
str_inspect += "'%s'," % (item)
str_inspect = str_inspect[:-1]
cur = connections[model_name].cursor()
sql = '''select m.line_cd,m.partsserial_cd,m.serial_cd,d.judge_text,m.process_at,d.inspect_cd,d.inspect_text from t_data_(model_name) as d,
(select i.insp_seq,f.serial_cd,i.line_cd,i.process_at,f.partsserial_cd,i.judge_text,row_number() over (partition by f.serial_cd,f.datatype_id order by f.process_at) from t_faci_(model_name) f ,
(select insp_seq,serial_cd,process_at,judge_text,line_cd,process_cd from
(select a.insp_seq,a.serial_cd,a.process_at,a.judge_text,b.line_cd,b.process_cd,row_number() over (partition by a.serial_cd order by a.process_at desc) from t_insp_(model_name) a,m_process b
where a.proc_uuid = b.proc_uuid
and b.line_cd = '(line_cd)'
and b.process_cd = '(process_cd)'
and a.process_at >= '(start_time)'
and a.process_at <= '(end_time)'
) as n
where n.row_number = '1'
order by n.process_at desc
limit '(limit)'
) as I
where f.serial_cd = i.serial_cd
and f.datatype_id = '(datatype_id)'
) as m
where d.insp_seq = m.insp_seq
and m.row_number = '1'
and d.inspect_cd in ('''+ str_inspect +''')'''
sql = sql.replace('(model_name)', model_name) \
.replace("(line_cd)", line_cd) \
.replace("(process_cd)", process_name)\
.replace("(start_time)", start_time)\
.replace("(end_time)",end_time)\
.replace("(datatype_id)", datatype_id)\
.replace("(limit)", str(limit))
cur.execute(sql)
rows = cur.fetchall()
data_list = []
for row in rows:
re = {}
re['line_cd'] = row[0]
re['station_slot'] = row[1]
re['serial_cd'] = row[2]
re['judge_text'] = row[3]
re['process_at'] = row[4].strftime("%Y-%m-%d %H:%M:%S")
re['inspect_cd'] = row[5]
re['inspect_text'] = row[6]
data_list.append(re)
except BaseException as exp:
data_list = databaseException(exp)
connections[model_name].close()
return data_list
def get_line(request):
model = request.GET.get("model")
process = request.GET.get("process")
datatype_Id = request.GET.get("datatypeId")
row = model_setting.getProcess(model, process)
name = row[0]['NAME']
process_name = row[0]['PROCESS_NAME']
JIG = str(row[0]['JIG_S']) + ',' + str(row[0]['JIG_E'])
PROCESS = str(row[0]['PROCESS_S'] )+ ',' + str(row[0]['PROCESS_E'])
limit = int(row[0]['LIMIT'])
JIG_type = row[0]['JIG_TYPE']
PROCESS_type = row[0]['PROCESS_TYPE']
result = model_homePage.getLine(model,name,process_name,datatype_Id,JIG,PROCESS,JIG_type,PROCESS_type,limit)
jsonstr = json.dumps(result)
return HttpResponse(jsonstr)
def get_InspectList(request):
Logger.write_log("Inspect Detail数据")
model_name = request.GET.get('model_name')
process_cd = request.GET.get('process_cd')
datatype_id = request.GET.get('datatype_id')
line_cd = request.GET.get('line_cd')
start_time = request.GET.get('start_time')
end_time = request.GET.get('end_time')
serial_cd = request.GET.get('serial_cd')
row = model_setting.getProcess(model_name, process_cd)
name = row[0]['NAME']
limit = row[0]['LIMIT']
process_name = row[0]['PROCESS_NAME']
result = models_inspectDetail.getInspectList(model_name,name,datatype_id,limit,start_time, end_time, line_cd, serial_cd, process_name)
jsonstr = json.dumps(result)
return HttpResponse(jsonstr)
def get_SerialList(request):
Logger.write_log("获取Warning Detail数据")
model_name = request.GET.get('model_name')
process_cd = request.GET.get('process_cd')
datatype_id = request.GET.get('datatype_id')
line_cd = request.GET.get('line_cd')
station_slot = request.GET.get('station_slot')
start_time = request.GET.get('start_time')
end_time = request.GET.get('end_time')
# allJson = request.GET.get('allJson')
row = model_setting.getProcess(model_name, process_cd)
name = row[0]['NAME']
limit = row[0]['LIMIT']
process_name = row[0]['PROCESS_NAME']
result = models_serialDetail.getSerialDetail(model_name, name, limit,
process_name, datatype_id,
line_cd, start_time, end_time)
jsonstr = json.dumps(result)
return HttpResponse(jsonstr)
def get_DetailList(request):
Logger.write_log("获取Warning Detail数据")
model_name = request.GET.get('model_name')
process_cd = request.GET.get('process_cd')
datatype_id = request.GET.get('datatype_id')
line_cd = request.GET.get('line_cd')
start_time = request.GET.get('start_time')
end_time = request.GET.get('end_time')
row = model_setting.getProcess(model_name, process_cd)
name = row[0]['NAME']
process_name = row[0]['PROCESS_NAME']
JIG = str(row[0]['JIG_S']) + ',' + str(row[0]['JIG_E'])
PROCESS = str(row[0]['PROCESS_S']) + ',' + str(row[0]['PROCESS_E'])
limit = row[0]['LIMIT']
JIG_type = row[0]['JIG_TYPE']
PROCESS_type = row[0]['PROCESS_TYPE']
result = models_warningDetail.getDetailList(model_name,name, process_name, datatype_id, line_cd,JIG,PROCESS, start_time, end_time, limit, JIG_type, PROCESS_type)
jsonstr = json.dumps(result)
return HttpResponse(jsonstr)
def getInspectList(model_name, serial_cd, process_cd):
inspectList = []
# INSPECT = getInspectFromConf(model_name, process_cd)
row = model_setting.getProcess(model_name, process_cd)
INSPECT = (row[0]['INSPECT']).split(';')
INSPECT_LIST = []
for item in INSPECT:
INSPECT_LIST.append({
'name': item.split(':')[0],
'value': item.split(':')[1]
})
try:
cur = connections[model_name].cursor()
sql = "SELECT DISTINCT \
inspect_cd,d.inspect_text\
FROM \
t_insp_" + model_name + " i \
INNER JOIN t_data_" + model_name + " d ON i.insp_seq = d.insp_seq \
AND i.serial_cd = %s \
AND d.judge_text = '1' \
ORDER BY inspect_cd"
cur.execute(sql, (serial_cd, ))
rows = cur.fetchall()
for item in INSPECT_LIST:
print(item['name'])
for row in rows:
flag, list = check_exist(row[0], INSPECT_LIST)
if flag:
inspectList.append({'inspect_code': row[0], 'inspect': row[1]})
except BaseException as exp:
print(exp)
inspectList = models_common.databaseException(exp)
connections[model_name].close()
return inspectList
def getDetailList_update2(model_name,name, process_name, datatype_id, limit, start_time, end_time,JIG_type):
result = []
try:
row = model_setting.getProcess(model_name, name)
INSPECT = (row[0]['INSPECT']).split(',')
str_inspect = ""
for item in INSPECT:
str_inspect += "'%s'," % (item)
str_inspect = str_inspect[:-1]
database_list = get_config("database")
lineArr = []
for row in database_list:
if row['MODEL'] == model_name:
# 从配置文件里取得LINE
lineArr = row['LINE']
break
cur = connections[model_name].cursor()
# 1. before
# sql = "SELECT DISTINCT line_cd,station_slot,serial_cd,judge_text,process_at,inspect_cd,inspect_text FROM\
# (SELECT T2.line_cd,station_slot,T2.serial_cd,T2.judge_text,T2.process_at,d.inspect_cd,d.inspect_text FROM( \
# SELECT line_cd,serial_cd,judge_text,insp_seq,process_at FROM ( SELECT DISTINCT P .line_cd,i.serial_cd, \
# i.process_at,i.judge_text,i.insp_seq,ROW_NUMBER () OVER ( PARTITION BY serial_cd ORDER BY process_at DESC \
# ) RANK FROM t_insp_" + model_name + " i,m_process P WHERE i.proc_uuid = P .proc_uuid AND P .line_cd IN (%s) \
# AND P .process_cd = '(process_cd)' AND i.process_at >= '(start_time)' AND i.process_at <= '(end_time)' \
# ORDER BY i.process_at DESC LIMIT '(limit)') BASE WHERE RANK = 1 ) T2 LEFT JOIN t_data_" + model_name + " d ON T2.insp_seq = d.insp_seq \
# INNER JOIN ( SELECT DISTINCT f.partsserial_cd AS station_slot,f.serial_cd,f.process_at FROM m_process P,t_faci_" + model_name + " f \
# WHERE f.proc_uuid = P .proc_uuid AND P .Process_cd = '(process_cd)' AND f.datatype_id = '(datatype_id)' ) T3 ON T2.serial_cd = T3.serial_cd) T4 \
# WHERE inspect_cd in ("+ str_inspect +") "
# sql = sql % ','.join(['%s'] * len(lineArr))
# sql = sql.replace("(process_cd)", process_name).replace("(start_time)", start_time).replace("(end_time)",end_time).replace("(datatype_id)", datatype_id).replace("(limit)", str(limit))
# 2. after
sql = '''select m.line_cd,m.partsserial_cd,m.serial_cd,d.judge_text,m.process_at,d.inspect_cd,d.inspect_text from t_data_(model_name) as d,
(select i.insp_seq,f.serial_cd,i.line_cd,i.process_at,f.partsserial_cd,i.judge_text,row_number() over (partition by f.serial_cd,f.datatype_id order by f.process_at) from t_faci_(model_name) f ,
(select insp_seq,serial_cd,process_at,judge_text,line_cd,process_cd from
(select a.insp_seq,a.serial_cd,a.process_at,a.judge_text,b.line_cd,b.process_cd,row_number() over (partition by a.serial_cd order by a.process_at desc) from t_insp_(model_name) a,m_process b
where a.proc_uuid = b.proc_uuid
and b.line_cd in (%s)
and b.process_cd = '(process_cd)'
and a.process_at >= '(start_time)'
and a.process_at <= '(end_time)'
) as n
where n.row_number = '1'
order by n.process_at desc
limit '(limit)'
) as I
where f.serial_cd = i.serial_cd
and f.datatype_id = '(datatype_id)'
) as m
where d.insp_seq = m.insp_seq
and m.row_number = '1'
and d.inspect_cd in ('''+ str_inspect +''')'''
sql = sql % ','.join(['%s'] * len(lineArr))
sql = sql.replace('(model_name)', model_name)\
.replace("(process_cd)", process_name)\
.replace("(start_time)", start_time)\
.replace("(end_time)",end_time)\
.replace("(datatype_id)", datatype_id)\
.replace("(limit)", str(limit))
cur.execute(sql, lineArr)
rows = cur.fetchall()
data_list = []
for row in rows:
re = {}
re['line_cd'] = row[0]
re['station_slot'] = row[1]
re['serial_cd'] = row[2]
re['judge_text'] = row[3]
re['process_at'] = row[4].strftime("%Y-%m-%d %H:%M:%S")
re['inspect_cd'] = row[5]
re['inspect_text'] = row[6]
data_list.append(re)
# if not row[0] in line_cd_list:
# line_cd_list.append(row[0])
# data_list = [
# {'line_cd': 'L04', 'station_slot': 'L08MS05-03', 'serial_cd': 'GH9830188QVJL4K7A', 'judge_text': '0',
# 'process_at': '2018-08-05 12:30:24', 'inspect_cd': 'OVEN_ST_TIME', 'inspect_text': '16:35:56'},
# {'line_cd': 'L04', 'station_slot': 'L08MS05-03', 'serial_cd': 'GH9830188QVJL4K7B', 'judge_text': '0',
# 'process_at': '2018-08-05 12:30:24', 'inspect_cd': 'OVEN_ED_TIME', 'inspect_text': '17:34:56'},
# {'line_cd': 'L04', 'station_slot': 'L08MS05-03', 'serial_cd': 'GH9830188W3JL4K7C', 'judge_text': '1',
# 'process_at': '2018-08-05 12:34:03', 'inspect_cd': 'OVEN_ST_TIME', 'inspect_text': '0'},
# {'line_cd': 'L04', 'station_slot': 'L08MS05-03', 'serial_cd': 'GH9830188W3JL4K7D', 'judge_text': '1',
# 'process_at': '2018-08-05 12:34:03', 'inspect_cd': 'OVEN_ED_TIME', 'inspect_text': '0'},
# {'line_cd': 'L04', 'station_slot': 'L08MS05-03', 'serial_cd': 'GH9830188W3JL4K7D', 'judge_text': '1',
# 'process_at': '2018-08-05 12:34:03', 'inspect_cd': 'OVEN_ST_TIME', 'inspect_text': '0'}]
JIG_list = []
line_cd_list = []
serial_cd_list_1 = []
serial_cd_list_2 = []
for item in data_list:
if not item['line_cd'] in line_cd_list:
line_cd_list.append(item['line_cd'])
for item in line_cd_list:
for row_1 in data_list:
if item == row_1['line_cd']:
if not row_1['station_slot'] in JIG_list:
JIG_list.append(row_1['station_slot'])
for JIG_item in JIG_list:
IN_ = 0
NG_ = 0
for row_ in data_list:
if row_['line_cd'] == item and row_['station_slot'] == JIG_item and int(row_['judge_text']) == 1:
if not row_['serial_cd'] in serial_cd_list_1:
serial_cd_list_1.append(row_['serial_cd'])
NG_ = NG_ + 1
if row_['line_cd'] == item and row_['station_slot'] == JIG_item:
if not row_['serial_cd'] in serial_cd_list_2:
serial_cd_list_2.append(row_['serial_cd'])
IN_ = IN_ + 1
Yield = float('%.2f' % (100 * ((IN_- NG_)/IN_)))
result.append({"line_cd":item,"station_slot":JIG_item,"ng_count":NG_,"in":IN_,"yield":Yield})
JIG_list = []
if JIG_type == 'NG COUNT':
result.sort(key=lambda x:x['ng_count'], reverse=True)
else:
result.sort(key=lambda x:x['yield'])
except BaseException as exp:
print(exp)
result = databaseException(exp)
connections[model_name].close()
return result, data_list
