def get_ngQuantity_line(model_name, process_at, sql_timepart, lineNum):
result = []
try:
cur = connections[model_name].cursor()
sql = "SELECT\
line_cd,\
assy_text,\
dayornight,\
SUM( ng_quantity ) AS ng_quantity \
FROM\
(\
SELECT DISTINCT\
auto_ng.line_cd,\
m_assy.assy_text,\
auto_ng.process_id,\
auto_ng.dayornight,\
auto_ng.ng_quantity AS ng_quantity \
FROM\
m_assy\
INNER JOIN (\
SELECT\
line_cd,\
process_id,\
dayornight,\
SUM( quantity ) AS ng_quantity\
FROM\
(\
SELECT\
line_cd,\
process_id,\
time_part AS dayornight,\
quantity \
FROM\
t_1_auto_io_" + lineNum + " \
WHERE\
process_at = '(process_at)' \
AND judge_text = 'ng' \
AND " + sql_timepart + " \
) AS auto \
GROUP BY\
line_cd,\
process_id,\
dayornight \
) AS auto_ng ON m_assy.process_id = auto_ng.process_id\
) AS main \
GROUP BY\
line_cd,\
assy_text,\
dayornight \
ORDER BY line_cd,assy_text,dayornight"
sql = sql.replace("(process_at)", process_at)
cur.execute(sql)
rows = cur.fetchall()
for row in rows:
result.append({"line_cd":row[0],"assy_text":row[1],"dayornight":row[2], "ng_quantity":int(row[3]),})
except BaseException as exp:
print(exp)
result = models_common.databaseException(exp)
connections[model_name].close()
return result
def getSummaryTimeType(model_name):
try:
from MfcOpeSys.models import models_common
result = []
cur = connections[model_name].cursor()
SQL = 'select time_tbl_cd from m_time_table order by time_tbl_cd limit 1'
cur.execute(SQL)
row = cur.fetchone()
SQL = 'SELECT time_part from m_time_table WHERE time_tbl_cd = %s order by time_part;'
cur.execute(SQL, (row[0], ))
rows = cur.fetchall()
result_temp1 = []
result_temp2 = []
for row in rows:
if row[0] == 'All' or row[0] == 'Night' or row[0] == 'Day':
result_temp1.append(row[0])
else:
result_temp2.append(row[0])
result = result_temp1
result.extend(result_temp2)
except BaseException as exp:
print(exp)
result = models_common.databaseException(exp)
connections[model_name].close()
return result
def get_processIDs(model_name, from_process_at, to_process_at, config, line,
datatype_id, pernum, firstDatatypeFlg):
result = []
from_process_at = from_process_at + '+08'
to_process_at = to_process_at + '+08'
sql1 = "SELECT \
process_id,\
process_text,\
assy_text,\
process_cd,\
hold_for \
FROM("
sql2 = "SELECT DISTINCT \
assy.process_id,\
assy.process_text,\
assy.assy_text,\
tracert.process_cd,\
assy.hold_for \
FROM \
t_1_sn_tracert tracert \
INNER JOIN t_1_sn_target target ON tracert.serial_cd = target.serial_cd \
AND target.line_cd = '(line_cd)' \
AND target.config_cd = '(config_cd)' "
sql3 = "INNER JOIN m_assy assy ON assy.datatype_id = target.datatype_id \
AND assy.process_cd = tracert.process_cd \
AND assy.tracert_sw = 'ON' \
AND assy.datatype_id = '(datatype_id)' \
ORDER BY assy.process_id "
sql_limit = " LIMIT 1 ) t1 UNION "
sql_offset = " OFFSET 1 ) t2 ORDER BY process_id"
# 是第一datatype的场合
if firstDatatypeFlg:
sub_datetime_sql = " AND tracert.process_at >= '" + from_process_at + "' AND tracert.process_at <= '" + to_process_at + "' "
sql = sql1 + sql2 + sql3 + sql_limit + sql1 + sql2 + sub_datetime_sql + sql3 + sql_offset
else:
sql = sql2 + sql3
try:
cur = connections[model_name].cursor()
sql = sql.replace("(line_cd)", line).replace("(datatype_id)",
datatype_id).replace(
"(config_cd)", config)
cur.execute(sql)
rows = cur.fetchall()
for row in rows:
result.append( \
{"process_id": row[0], "process_code": row[3], "process_name": row[1], "assy_name": row[2],\
"ok": 0, "ng": 0, "input": 0,\
"yield": "0.00%", "ipqc": 0, "wip": 0,\
"index": 0, "s_index": 0, "e_index": pernum, "first_display": 1, "next_display": 1,\
"detail": [], "hold_for": row[4], })
except BaseException as exp:
print(exp)
result = models_common.databaseException(exp)
connections[model_name].close()
return result
def initSummaryOffsetData(model_name):
result = []
try:
today = datetime.date.today()
oneday = datetime.timedelta(days=1)
yesterday = today - oneday
cur = connections[model_name].cursor()
cur.execute(
"SELECT DISTINCT assy_text,min(process_id) as a FROM m_assy GROUP BY assy_text ORDER BY a;"
)
rows = cur.fetchall()
create_summary_offset_table(model_name, cur)
for row in rows:
assy_text = row[0]
cur.execute(
"SELECT ng_count,reason,date FROM m_summary_offset WHERE assy_text = %s;",
(assy_text, ))
row_m = cur.fetchone()
if (row_m != None):
if (row_m[2] == yesterday):
result.append({
'assy_text': row[0],
'ng_count': row_m[0],
'reason': row_m[1]
})
else:
result.append({
'assy_text': row[0],
'ng_count': 0,
'reason': ''
})
else:
result.append({
'assy_text': row[0],
'ng_count': 0,
'reason': ''
})
except BaseException as exp:
print(exp)
result = models_common.databaseException(exp)
connections[model_name].close()
return result
def get_inspect(model_name, start_date, end_date, process_cd):
result = []
try:
cur = connections[model_name].cursor()
sql = "SELECT DISTINCT \
defect.inspect_cd \
FROM \
t_1_defect_2nd defect \
INNER JOIN t_1_auto_io_2nd auto ON defect.data_seq = auto.data_seq \
AND auto.process_at >= %s \
AND auto.process_at <= %s \
INNER JOIN m_assy assy ON auto.process_id = auto.process_id \
AND assy.process_cd = %s \
ORDER BY inspect_cd"
cur.execute(sql, (start_date, end_date, process_cd))
rows = cur.fetchall()
for row in rows:
result.append(row[0])
except BaseException as exp:
print(exp)
result = models_common.databaseException(exp)
connections[model_name].close()
return result
def set_sntracert(model_name, from_process_at, to_process_at, config, line,
datatype_id, pernum, relationDic):
result = []
try:
# 第一工位全部信息
result_first = {}
# 第一工位的serial_cd数组
result_first_serialCd = []
index = 0
for key in relationDic:
index = index + 1
# 最底层的datatype_id场合
if relationDic[key][0] == datatype_id:
# 获取数据
resultDic = getDataByDatatype(model_name, from_process_at, to_process_at, config, line,\
relationDic[key][0], pernum, False, [], True)
# 异常场合
if type(resultDic) == int:
return resultDic
if len(resultDic['result']) == 0:
return result
else:
result = result + resultDic['result']
if len(resultDic['lastDataArr']) == 0:
return result
else:
# 获取parent的第一工位数据
result_firstDic = get_parentFirstData(model_name, from_process_at, to_process_at, config, line,\
relationDic[key][0], resultDic['lastDataArr'],\
relationDic[key][1], pernum)
# 异常场合
if type(result_firstDic) == int:
return result_firstDic
result_first_serialCd = result_firstDic['serial_cds']
result_first = result_firstDic['result']
else:
# parent的第一工位无数据的场合
if len(result_first_serialCd) == 0:
return result
else:
# 获取数据
resultDic = getDataByDatatype(model_name, from_process_at, to_process_at, config, line,\
relationDic[key][0], pernum, True, result_first_serialCd, False)
# 异常场合
if type(resultDic) == int:
return resultDic
if len(resultDic['result']) == 0:
return result
else:
# 替换第一工位的数据
resultDic["result"][0] = result_first
# 不是最上层的datatype_id场合
if index < len(relationDic):
getLastFlg = True
else:
getLastFlg = False
result = result + resultDic['result']
if len(resultDic['lastDataArr']) > 0:
# 不是最上层的datatype_id场合
if getLastFlg:
# 获取parent的第一工位数据
result_firstDic = get_parentFirstData(model_name, from_process_at, to_process_at,\
config, line,\
relationDic[key][0], resultDic['lastDataArr'],\
relationDic[key][1], pernum)
# 异常场合
if type(result_firstDic) == int:
return result_firstDic
result_first_serialCd = result_firstDic[
'serial_cds']
result_first = result_firstDic['result']
else:
return result
return result
except BaseException as exp:
print(exp)
result = models_common.databaseException(exp)
return result
def get_sntracert(model_name, from_process_at, to_process_at, config, line,
datatype_id, mode, pernum):
result = []
relationDic = {}
try:
if mode == "LINK":
cur = connections[model_name].cursor()
# 获取父子继承关系
sql_relation = "WITH RECURSIVE r AS (\
SELECT \
child_sn,\
parent_sn, \
datatype_seq \
FROM \
m_sn_datatype_relation son \
WHERE \
child_sn = %s \
UNION ALL \
SELECT \
parent.child_sn,\
parent.parent_sn, \
parent.datatype_seq \
FROM \
m_sn_datatype_relation parent,\
r \
WHERE \
parent.child_sn = r.parent_sn \
) SELECT datatype_seq,child_sn,parent_sn FROM r ORDER BY datatype_seq"
cur.execute(sql_relation, (datatype_id, ))
rows_relation = cur.fetchall()
# key:son,value:parent
for row in rows_relation:
arr = []
arr.append(row[1])
arr.append(row[2])
relationDic[row[0]] = arr
# 最上层场合
if len(relationDic) == 1 or mode == "INDIVID":
# 获取数据
resultDic = getDataByDatatype(model_name, from_process_at, to_process_at, config, line,\
datatype_id, pernum, False, [], True)
# 异常场合
if type(resultDic) == int:
return resultDic
# wip的再设定
result = set_sntracert_wip(resultDic['result'])
else:
# sntracert信息的取得和设定
resultArr = set_sntracert(model_name, from_process_at,
to_process_at, config, line, datatype_id,
pernum, relationDic)
# wip的再设定
result = set_sntracert_wip(resultArr)
except BaseException as exp:
print(exp)
result = models_common.databaseException(exp)
connections[model_name].close()
return result
def get_dataByProcessId(model_name, from_process_at, to_process_at, config,
line, process_id, pernum, firstFlg,
previousSerialNoArr, holdForFlg, firstProFlg):
result = {}
from_process_at = from_process_at + '+08'
to_process_at = to_process_at + '+08'
sub_serial_sql = ""
sub_judge_sql = ""
sub_datetime_sql = ""
# 不是第一工位的场合
if firstFlg == False:
sub_serial_sql = " AND tracert.serial_cd IN( %s ) "
# 当前检索的工位数据是hold_for数据
if holdForFlg:
sub_judge_sql = " AND tracert.judge_text IN ( '1','IPQC') "
# 是第一datatype且是第一工位的场合
if firstProFlg:
sub_datetime_sql = " AND tracert.process_at >= '" + from_process_at + "' AND tracert.process_at <= '" + to_process_at + "' "
try:
cur = connections[model_name].cursor()
sql = "SELECT \
tracert.serial_cd,\
tracert.judge_text \
FROM \
t_1_sn_tracert tracert \
INNER JOIN t_1_sn_target target ON tracert.serial_cd = target.serial_cd " + sub_serial_sql + \
"AND target.line_cd = '(line_cd)' \
AND target.config_cd = '(config_cd)' " + sub_datetime_sql + \
"INNER JOIN m_assy assy ON assy.datatype_id = target.datatype_id \
AND assy.process_cd = tracert.process_cd \
AND assy.process_id = '(process_id)' " + sub_judge_sql + \
"ORDER BY tracert.serial_cd"
sql = sql.replace("(line_cd)", line).replace("(process_id)",
str(process_id)).replace(
"(config_cd)", config)
if firstFlg:
cur.execute(sql)
else:
sql = sql % ','.join(['%s'] * len(previousSerialNoArr))
cur.execute(sql, previousSerialNoArr)
rows = cur.fetchall()
ok_quantity = 0
ng_quantity = 0
ipqc_quantity = 0
data_exist_flg = False
holdFor_input = len(previousSerialNoArr)
detail = []
detail_temp = []
yield2 = "0.00%"
next_display = 1
for row in rows:
data_exist_flg = True
detailResult = ''
if operator.eq(row[1], '0'):
detailResult = 'OK'
ok_quantity = ok_quantity + 1
elif operator.eq(row[1], '1'):
detailResult = 'NG'
ng_quantity = ng_quantity + 1
elif operator.eq(row[1], 'IPQC'):
detailResult = 'IPQC'
ipqc_quantity = ipqc_quantity + 1
detail.append({
"serial_no": row[0],
"result": detailResult,
})
# 当前检索的工位数据是hold_for数据
if holdForFlg:
detail_temp.append(row[0])
# 当前检索的工位数据是hold_for数据
if holdForFlg:
# 翻页按钮活性设定
if holdFor_input > pernum:
next_display = 0
# holdFor工位的NG数据不存在的场合
if data_exist_flg == False:
for value in previousSerialNoArr:
detail.append({
"serial_no": value,
"result": "OK",
})
result = {"ok": holdFor_input, "ng": 0, "input": holdFor_input,\
"yield": "100.00%", "ipqc": 0, "wip": 0,\
"next_display": next_display,\
"detail": detail, }
else:
ok_quantity = holdFor_input - ng_quantity - ipqc_quantity
if ok_quantity + ng_quantity > 0:
yield2 = "%.2f%%" % (ok_quantity / (
(ok_quantity + ng_quantity) * 1.0) * 100)
for value in previousSerialNoArr:
if value not in detail_temp:
detail.append({
"serial_no": value,
"result": "OK",
})
# detail按照序列号排序
detail = sorted(detail, key=lambda x: (x['serial_no']))
result = {"ok": ok_quantity, "ng": ng_quantity, "input": holdFor_input,\
"yield": yield2, "ipqc": ipqc_quantity, "wip": 0,\
"next_display": next_display,\
"detail": detail, }
else:
if ok_quantity + ng_quantity > 0:
yield2 = "%.2f%%" % (ok_quantity /
((ok_quantity + ng_quantity) * 1.0) * 100)
# 翻页按钮活性设定
if len(detail) > pernum:
next_display = 0
result = {"ok": ok_quantity, "ng": ng_quantity, "input": ok_quantity + ng_quantity + ipqc_quantity,\
"yield": yield2, "ipqc": ipqc_quantity, "wip": 0,\
"next_display": next_display,\
"detail": detail, }
except BaseException as exp:
print(exp)
result = models_common.databaseException(exp)
connections[model_name].close()
return result
def get_parentFirstData(model_name, from_process_at, to_process_at, config,
line, datatype_id, lastDataArr, parentDatatype,
pernum):
result = {}
resultDic = {}
serial_cd = []
from_process_at = from_process_at + '+08'
to_process_at = to_process_at + '+08'
try:
cur = connections[model_name].cursor()
# 获取第一工位的process相关信息
sql = "SELECT\
assy.process_id,\
assy.process_text,\
assy.assy_text,\
tracert.process_cd,\
assy.hold_for \
FROM\
t_1_sn_tracert tracert\
INNER JOIN t_1_sn_target target ON tracert.serial_cd = target.serial_cd \
AND target.line_cd = '(line_cd)' \
AND target.config_cd = '(config_cd)' \
INNER JOIN m_assy assy ON assy.datatype_id = target.datatype_id\
AND assy.process_cd = tracert.process_cd \
AND assy.tracert_sw = 'ON' \
AND assy.datatype_id = '(datatype_id)'\
ORDER BY\
assy.process_id\
limit 1"
sql = sql.replace("(line_cd)", line).replace("(datatype_id)",
parentDatatype).replace(
"(config_cd)", config)
cur.execute(sql)
rows = cur.fetchall()
first_processId = ""
for row in rows:
first_processId = str(row[0])
result = {"process_id": row[0], "process_code": row[3], "process_name": row[1], "assy_name": row[2],\
"ok": 0, "ng": 0, "input": 0, \
"yield": "0.00%", "ipqc": 0, "wip": 0, \
"index": 0, "s_index": 0, "e_index": pernum, "first_display": 1, "next_display": 1,\
"detail": [], "hold_for": row[4], }
# 获取第一工位的OK，NG，detail等相关信息
sql = "SELECT\
tracert.serial_cd,\
tracert.judge_text \
FROM\
t_1_sn_tracert tracert\
INNER JOIN t_1_sn_target target ON tracert.serial_cd = target.serial_cd \
AND target.line_cd = '(line_cd)' \
AND target.config_cd = '(config_cd)' \
AND tracert.serial_cd IN ( SELECT serial_cd FROM t_1_sn_matg WHERE child_cd IN ( %s ) AND datatype_id = '(datatype_id)' ) \
INNER JOIN m_assy assy ON assy.datatype_id = target.datatype_id \
AND assy.process_cd = tracert.process_cd \
AND assy.tracert_sw = 'ON' \
AND assy.datatype_id = '(parent_datatype_id)'\
AND assy.process_id = '(process_id)'\
ORDER BY tracert.serial_cd" % ','.join(
['%s'] * len(lastDataArr))
sql = sql.replace("(line_cd)", line).replace("(datatype_id)", datatype_id).replace("(config_cd)", config)\
.replace("(parent_datatype_id)", parentDatatype).replace("(process_id)", first_processId)
cur.execute(sql, lastDataArr)
rows = cur.fetchall()
ok_quantity = 0
ng_quantity = 0
ipqc_quantity = 0
for row in rows:
detailResult = ''
yield2 = '0.00%'
if operator.eq(row[1], '0'):
detailResult = 'OK'
ok_quantity = ok_quantity + 1
elif operator.eq(row[1], '1'):
detailResult = 'NG'
ng_quantity = ng_quantity + 1
elif operator.eq(row[1], 'IPQC'):
detailResult = 'IPQC'
ipqc_quantity = ipqc_quantity + 1
if ok_quantity + ng_quantity > 0:
yield2 = "%.2f%%" % (ok_quantity /
((ok_quantity + ng_quantity) * 1.0) * 100)
result['ok'] = ok_quantity
result['ng'] = ng_quantity
result['input'] = ok_quantity + ng_quantity + ipqc_quantity
result['yield'] = yield2
result['ipqc'] = ipqc_quantity
result['detail'].append({
"serial_no": row[0],
"result": detailResult,
})
# 翻页按钮活性设定
if result['detail']:
if len(result['detail']) > pernum:
result['next_display'] = 0
serial_cd.append(row[0])
except BaseException as exp:
print(exp)
result = models_common.databaseException(exp)
resultDic['result'] = result
resultDic['serial_cds'] = serial_cd
return resultDic
def get_trend(model_name, start_date, end_date, process_cd, inspect_cd, time_part, type):
process_at_list = models_common.get_periodDate(start_date, end_date)
timePartStr = ""
for value in time_part:
timePartStr = timePartStr + "'" + value + "',"
timePartStr = timePartStr[:-1]
inspect_cd = inspect_cd.upper()
# typeName设定
if type == "NG":
typeName = "failed_quantity"
else:
typeName = "yield2"
data_zero = []
data_NP = []
try:
i = 0
while i < len(process_at_list):
data_zero.append(0)
data_NP.append("NP")
i = i + 1
cur = connections[model_name].cursor()
sql = "SELECT DISTINCT \
auto_ng.line_cd,\
auto_ng.time_part,\
auto_ng.line_cd || ' ' || auto_ng.time_part AS lineAndTimepart, \
auto_ng.process_at, \
COALESCE ( auto_ok.ok_quantity, 0 ) AS ok_quantity,\
COALESCE ( auto_ng.ng_quantity, 0 ) AS ng_quantity,\
COALESCE ( defect.inspect_count, 0 ) AS failed_quantity,\
( COALESCE ( auto_ok.ok_quantity, 0 ) + COALESCE ( auto_ng.ng_quantity, 0 ) ) AS input_quantity,\
COALESCE ( defect.inspect_count, 0 ) / ( COALESCE ( auto_ok.ok_quantity, 0 ) + COALESCE ( auto_ng.ng_quantity, 0 ) ) AS detractor \
FROM \
( \
SELECT \
line_cd,\
time_part,\
process_at,\
SUM( quantity ) AS ok_quantity \
FROM \
m_assy A \
INNER JOIN t_1_auto_io_2nd auto ON A.process_id = auto.process_id \
WHERE \
process_at >= '(start_date)' \
AND process_at <= '(end_date)' \
AND time_part IN( '(time_part)' ) \
AND judge_text = 'ok' \
AND process_cd = '(process_cd)' \
GROUP BY \
line_cd,\
time_part,\
process_at \
) AS auto_ok \
INNER JOIN (\
SELECT \
line_cd,\
time_part,\
process_at,\
SUM( quantity ) AS ng_quantity \
FROM \
m_assy A \
INNER JOIN t_1_auto_io_2nd auto ON A.process_id = auto.process_id \
WHERE \
process_at >= '(start_date)' \
AND process_at <= '(end_date)' AND time_part IN('(time_part)') AND judge_text = 'ng' AND quantity > 0 \
AND process_cd = '(process_cd)' \
GROUP BY \
line_cd,\
time_part,\
process_at \
) AS auto_ng ON auto_ok.line_cd = auto_ng.line_cd \
AND auto_ok.time_part = auto_ng.time_part \
AND auto_ok.process_at = auto_ng.process_at \
INNER JOIN (\
SELECT \
line_cd,\
time_part,\
process_at,\
COUNT( serial_cd ) AS inspect_count \
FROM \
(\
SELECT DISTINCT \
line_cd,\
time_part,\
process_at,\
defect.data_seq,\
defect.serial_cd \
FROM \
m_assy A \
INNER JOIN t_1_auto_io_2nd auto ON A.process_id = auto.process_id \
INNER JOIN t_1_defect_2nd defect ON auto.data_seq = defect.data_seq \
WHERE \
process_at >= '(start_date)' \
AND process_at <= '(end_date)' AND time_part IN('(time_part)') AND judge_text = 'ng' AND quantity > 0 \
AND process_cd = '(process_cd)' \
AND UPPER(inspect_cd) LIKE '%(inspect_cd_str)%' \
) AS defect1 \
GROUP BY \
line_cd,\
time_part,\
process_at \
) AS defect ON auto_ng.line_cd = defect.line_cd \
AND auto_ng.time_part = defect.time_part \
AND auto_ng.process_at = defect.process_at \
ORDER BY \
line_cd,\
time_part,\
process_at"
sql = sql.replace("(start_date)", start_date).replace("(end_date)", end_date).replace("(process_cd)", process_cd) \
.replace("(inspect_cd_str)", inspect_cd).replace("'(time_part)'", timePartStr)
cur.execute(sql)
rows = cur.fetchall()
result_quantity = []
for row in rows:
# result_quantity.append({"line_cd":row[0], "lineAndTimepart":row[2], "process_at": str(row[3]),\
# "failed_quantity": int(row[6]), "yield2":float(1-float(row[8])),})
result_quantity.append({"line_cd": row[0], "lineAndTimepart": row[2], "process_at": str(row[3]), \
"failed_quantity": int(row[6]), "yield2": float(float(row[8])), })
if type == 'NG':
# 获取All Line(sum)数据
subSql_sum1 = "SELECT process_at,SUM( failed_quantity ) AS sum_failed_quantity FROM("
subSql_sum2 = ")AS TB GROUP BY process_at ORDER BY process_at"
sql = subSql_sum1 + sql + subSql_sum2
cur.execute(sql)
rows = cur.fetchall()
result_quantity_all = []
for row in rows:
result_quantity_all.append({"process_at": str(row[0]), "sum_quantity": int(row[1]), })
# get all lines
lineArr = models_common.get_allLines(model_name)
if lineArr == 101 or lineArr == 102:
return lineArr
# 设定目标json数据
result = setDataList(process_at_list, result_quantity, typeName, lineArr, time_part)
# sort
result = sorted(result, key=lambda x: (x['name']))
# All Line追加
if type == 'NG':
if len(result_quantity) == 0:
result_all = {"name": "All Line", "data_tb": data_zero, 'data':data_zero}
else:
result_all = models_common.supplementZero("All Line", result_quantity_all, process_at_list, 'trend_chart')
else:
# get allLine
if len(result_quantity) == 0:
result_all = {'name': 'All Line', 'data_tb': data_zero, 'data':data_zero}
else:
# 求取平均值
data = models_common.getAvg_allLine(result, process_at_list)
result_all = {'name': 'All Line', 'data_tb': data, 'data':data}
result.append(result_all)
# 数据format
if type == 'Y2':
for row in result:
row_data = []
row_data_tb = []
i = 0
for value in row['data_tb']:
if value != 'NP':
row_data_tb.append("%.2f%%" % (value * 100))
row_data.append(float("%.2f" % (value * 100)))
else:
row_data_tb.append("NP")
row_data.append(float("%.2f" % 0))
i = i + 1
row['data'] = row_data
row['data_tb'] = row_data_tb
else:
for row in result:
row_data = []
i = 0
for value in row['data']:
if value == 'NP':
row_data.append(0)
else:
row_data.append(value)
i = i + 1
row['data'] = row_data
except BaseException as exp:
print(exp)
result = models_common.databaseException(exp)
connections[model_name].close()
return result
def get_progressDetail(model_name, search_date,lineNum):
result = []
header_data = []
lineArr = []
# 页面跳转用
lines_hid = []
lineArr_temp = []
dateArr = []
dayNightArr = []
timepartArr = []
time_part = ''
try:
database_list = models_common.get_config("database")
assyDic = {}
assyDic_int = {}
formulaDic = {}
timepartDic = {}
fpy_sub_formula_dic = {}
# timeparts：例：{0："D01"}
timeparts = {}
timepartName = "--"
timepartValue = "--"
for row in database_list:
if operator.eq(row['MODEL'], model_name):
# 从配置文件里取得ASSY
assyDic = row['ASSY']
# 从配置文件里取得FORMULA
formulaDic = row['FORMULA_SUMMARY']
# 从配置文件里取得time_part
timepartDic = row['TIME_PART']
break
# 系统日期的取得
now = datetime.datetime.now()
process_at = now.strftime("%Y-%m-%d")
times = now.strftime("%H:%M")
sysdatatime = process_at + ' ' + times
yesterday = now + datetime.timedelta(days=-1)
yesterday = yesterday.strftime('%Y-%m-%d')
# 从config文件取得time_part
j = 1
sorted(timepartDic.keys())
timepartList = sorted(timepartDic.items(), key=lambda d: d[0], reverse=False)
for key in timepartDic:
timeparts[int(key)] = timepartDic[key].split(',')[0]
for key in timepartList:
timepartStart = process_at + ' ' + key[1].split(',')[1].split('-')[0]
timepartEnd = process_at + ' ' + key[1].split(',')[1].split('-')[1]
if compare_time(sysdatatime, timepartStart, timepartEnd):
j = int(key[0])
break
sql_timepart = "time_part = '" + time_part + "' "
if j != 1:
time_part = timeparts[j - 1]
timepartName = time_part
timepartValue = timepartDic[str(j - 1)].split(',')[1]
if time_part[0] == "N":
sql_timepart = "(time_part = '" + time_part + "' or time_part = 'Day') "
else:
sql_timepart = "time_part = '" + time_part + "' "
# 根据time_part，设定日期(00:00-夜班下班点：日期为前一天)
# 夜班下班时间取得
closeTime = timepartDic[str(len(timeparts))].split(',')[1].split('-')[1]
if times < closeTime and times >= "00:00":
process_at = yesterday
# 各个line的first processid的取得
lineOfFirstPro = models_common.get_lineFirstProcess(model_name, process_at,lineNum) #lineNum切换
if lineOfFirstPro == 101 or lineOfFirstPro == 102:
return lineOfFirstPro
# input
result_inputQuantity = get_inputQuantity_line(model_name, process_at, sql_timepart,lineNum) #lineNum切换
if result_inputQuantity == 101 or result_inputQuantity == 102:
return result_inputQuantity
# output
result_outputQuantity = get_outputQuantity_line(model_name, process_at, sql_timepart, lineNum) #lineNum切换
if result_outputQuantity == 101 or result_outputQuantity == 102:
return result_outputQuantity
# ng
result_ngQuantity = get_ngQuantity_line(model_name, process_at, sql_timepart, lineNum) #lineNum切换
if result_ngQuantity == 101 or result_ngQuantity == 102:
return result_ngQuantity
# get assy
assyArr = models_common.get_allAssy(model_name)
if assyArr == 101 or assyArr == 102:
return assyArr
# 给key为整数的字典assyDic_int赋值
for key in assyDic:
assyDic_int[int(key)] = assyDic[key]
# 字典排序
sorted(assyDic_int.keys())
fpy_sub_formula = formulaDic["Assembly Yield(sub+main)"]
assembly_yield_sub_formula = formulaDic["Assy Yield(main)"]
fpy_formula = formulaDic["FPY(sub+main)"]
assembly_yield_formula = formulaDic["FPY(main)"]
# 将Assembly Yield(sub+main)的计算公式存放到字典
for i in range(len(fpy_sub_formula)):
fpy_sub_formula_dic[i] = fpy_sub_formula[i].split(',')
for row in lineOfFirstPro:
flag = True
for inputrow in result_inputQuantity:
if row['line_cd'] == inputrow['line_cd'] and row['assy_text'] == inputrow['assy_text'] \
and row['dayornight'][0] == inputrow['dayornight'][0]:
flag = False
if flag:
result_inputQuantity.append({"line_cd":row['line_cd'],"assy_text":row['assy_text'], "process_id":row['process_id'],"first_ok_quantity":0,"first_ng_quantity":0,"dayornight":row['dayornight'],"input_quantity":0,})
# outputQuantity,ngQuantity合并到inputQuantity
for inputrow in result_inputQuantity :
output_quantity = 0
for outputrow in result_outputQuantity :
if operator.eq(outputrow['line_cd'], inputrow['line_cd']) and operator.eq(outputrow['assy_text'], inputrow['assy_text']) \
and operator.eq(outputrow['dayornight'], inputrow['dayornight']):
output_quantity = outputrow['output_quantity']
break
inputrow["output_quantity"] = output_quantity
ng_quantity = 0
for ngrow in result_ngQuantity :
if operator.eq(ngrow['line_cd'], inputrow['line_cd']) and operator.eq(ngrow['assy_text'], inputrow['assy_text']) \
and operator.eq(ngrow['dayornight'], inputrow['dayornight']):
ng_quantity = ngrow['ng_quantity']
break
inputrow["ng_quantity"] = ng_quantity
if inputrow["input_quantity"] > 0:
inputrow["y1"] = inputrow["output_quantity"] / (inputrow["input_quantity"] * 1.0)
else:
inputrow["y1"] = 0
if inputrow["output_quantity"] + inputrow["ng_quantity"] > 0:
inputrow["y2"] = inputrow["output_quantity"] / ((inputrow["output_quantity"] + inputrow["ng_quantity"]) * 1.0)
else:
inputrow["y2"] = 0
cur = connections[model_name].cursor()
cur.execute("SELECT DISTINCT\
line_cd,\
m_assy.assy_text \
FROM\
m_assy\
INNER JOIN m_work ON m_work.assy_text = m_assy.assy_text \
ORDER BY line_cd,m_assy.assy_text")
rows = cur.fetchall()
last_line = ''
lineArr.append("Config Test SW")
dateArr.append("Production Date")
dayNightArr.append("Shift")
temp = process_at.split('-')
date = temp[1] + "/" + temp[2]
for row in rows:
if operator.eq(row[0], last_line) == False:
lineArr.append(row[0])
lineArr_temp.append(row[0])
dateArr.append(date)
dateArr.append(date)
dayNightArr.append("Day")
dayNightArr.append("Night")
if time_part == "":
timepartArr.append(time_part)
timepartArr.append(time_part)
elif time_part[0] == 'D':
timepartArr.append(time_part)
timepartArr.append("Night")
else:
timepartArr.append("Day")
timepartArr.append(time_part)
last_line = row[0]
lines_hid.append(row[0])
lines_hid.append(row[0])
lineArr.append("All Line")
dateArr.append(date)
dayNightArr.append("")
header_data.append(lineArr)
header_data.append(dateArr)
header_data.append(dayNightArr)
lines_hid.append("all")
# FPY(sub+main)等的计算
y1_fpy_sub_data = ["Assembly Yield1(sub+main)"]
y2_fpy_sub_data = ["Assembly Yield2(sub+main)"]
y1_assembly_yield_sub_data = ["Assy Yield(main)"]
y1_fpy_data = ["FPY(sub+main)"]
y1_assembly_yield_data = ["FPY(main)"]
assy_len = len(assyDic)
for line in lineArr_temp:
y1_day = {}
y2_day = {}
y1_night = {}
y2_night = {}
y1_day_temp = {}
y2_day_temp = {}
y1_night_temp = {}
y2_night_temp = {}
for inputrow in result_inputQuantity:
if operator.eq(inputrow['line_cd'], line) == False:
continue
for key in assyDic:
if operator.eq(inputrow['assy_text'], assyDic[key]):
if operator.eq(inputrow['dayornight'][0], 'D'):
if inputrow['y1'] == float(0):
y1_day[int(key)] = float(1)
y1_day_temp[int(key)] = 'NP'
else:
y1_day[int(key)] = inputrow['y1']
y1_day_temp[int(key)] = inputrow['y1']
if inputrow['y2'] == float(0):
y2_day[int(key)] = float(1)
y2_day_temp[int(key)] = 'NP'
else:
y2_day[int(key)] = inputrow['y2']
y2_day_temp[int(key)] = inputrow['y2']
if operator.eq(inputrow['dayornight'][0], 'N'):
if inputrow['y1'] == float(0):
y1_night[int(key)] = float(1)
y1_night_temp[int(key)] = 'NP'
else:
y1_night[int(key)] = inputrow['y1']
y1_night_temp[int(key)] = inputrow['y1']
if inputrow['y2'] == float(0):
y2_night[int(key)] = float(1)
y2_night_temp[int(key)] = 'NP'
else:
y2_night[int(key)] = inputrow['y2']
y2_night_temp[int(key)] = inputrow['y2']
break
# 字典排序
sorted(y1_day.keys())
sorted(y1_night.keys())
sorted(y2_day.keys())
sorted(y2_night.keys())
sorted(y1_day_temp.keys())
sorted(y1_night_temp.keys())
sorted(y2_day_temp.keys())
sorted(y2_night_temp.keys())
index = 0
# y1,y2不存在的情况，默认设为0.00
while index < assy_len:
if index not in y1_day.keys():
y1_day[index] = float(1)
y1_day_temp[index] = 'NP'
if index not in y1_night.keys():
y1_night[index] = float(1)
y1_night_temp[index] = 'NP'
if index not in y2_day.keys():
y2_day[index] = float(1)
y2_day_temp[index] = 'NP'
if index not in y2_night.keys():
y2_night[index] = float(1)
y2_night_temp[index] = 'NP'
index = index + 1
y1_day_list = []
y1_night_list = []
y2_day_list = []
y2_night_list = []
y1_day_list_temp = []
y1_night_list_temp = []
y2_day_list_temp = []
y2_night_list_temp = []
# y1,y2的值分别保存在数组中
for key in y1_day:
y1_day_list.append(y1_day[key])
y2_day_list.append(y2_day[key])
y1_day_list_temp.append(y1_day_temp[key])
y2_day_list_temp.append(y2_day_temp[key])
for key in y1_night:
y1_night_list.append(y1_night[key])
y2_night_list.append(y2_night[key])
y1_night_list_temp.append(y1_night_temp[key])
y2_night_list_temp.append(y2_night_temp[key])
# 计算Assembly Yield(sub+main)
y1_fpy_sub_day_value = models_common.get_formula_value(y1_day_list, y1_day_list_temp, fpy_sub_formula)
y1_fpy_sub_night_value = models_common.get_formula_value(y1_night_list, y1_night_list_temp, fpy_sub_formula)
y2_fpy_sub_day_value = models_common.get_formula_value(y2_day_list, y2_day_list_temp, fpy_sub_formula)
y2_fpy_sub_night_value = models_common.get_formula_value(y2_night_list, y2_night_list_temp, fpy_sub_formula)
# 计算Assy Yield(main)
y1_assembly_yield_sub_day_value = models_common.get_formula_value(y1_day_list, y1_day_list_temp, assembly_yield_sub_formula)
y1_assembly_yield_sub_night_value = models_common.get_formula_value(y1_night_list, y1_night_list_temp, assembly_yield_sub_formula)
# 计算FPY(sub+main)
y1_fpy_day_value = models_common.get_formula_value(y1_day_list, y1_day_list_temp, fpy_formula)
y1_fpy_night_value = models_common.get_formula_value(y1_night_list, y1_night_list_temp, fpy_formula)
# 计算FPY(main)
y1_assembly_yield_day_value = models_common.get_formula_value(y1_day_list, y1_day_list_temp, assembly_yield_formula)
y1_assembly_yield_night_value = models_common.get_formula_value(y1_night_list, y1_night_list_temp, assembly_yield_formula)
# y1_fpy_sub的计算
y1_fpy_sub_data.append("%.2f%%" % (y1_fpy_sub_day_value * 100))
y1_fpy_sub_data.append("%.2f%%" % (y1_fpy_sub_night_value * 100))
# y2_fpy_sub的计算
y2_fpy_sub_data.append("%.2f%%" % (y2_fpy_sub_day_value * 100))
y2_fpy_sub_data.append("%.2f%%" % (y2_fpy_sub_night_value * 100))
# y1_assembly_yield_sub的计算
y1_assembly_yield_sub_data.append("%.2f%%" % (y1_assembly_yield_sub_day_value * 100))
y1_assembly_yield_sub_data.append("%.2f%%" % (y1_assembly_yield_sub_night_value * 100))
# y1_fpy的计算
y1_fpy_data.append("%.2f%%" % (y1_fpy_day_value * 100))
y1_fpy_data.append("%.2f%%" % (y1_fpy_night_value * 100))
# y1_assembly_yield的计算
y1_assembly_yield_data.append("%.2f%%" % (y1_assembly_yield_day_value * 100))
y1_assembly_yield_data.append("%.2f%%" % (y1_assembly_yield_night_value * 100))
img_path = models_common.get_config("img_path")
result_allLine = []
result_assy = []
# 各assy的内容的取得和追加
for assy in assyArr:
assy_data = []
input_data = ["Input"]
input_data_int = []
output_data = ["Output"]
output_data_int = []
ng_data = ["NG"]
ng_data_int = []
yield_data = ["Yield2"]
for line in lineArr_temp:
input_day = 0
input_night = 0
output_day = 0
output_night = 0
ng_day = 0
ng_night = 0
yield_day = "NP"
yield_night = "NP"
for inputrow in result_inputQuantity:
if operator.eq(inputrow['line_cd'], line) and operator.eq(inputrow['assy_text'], assy) \
and operator.eq(inputrow['dayornight'][0], 'D'):
input_day = int(inputrow['input_quantity'])
output_day = int(inputrow['output_quantity'])
ng_day = int(inputrow['ng_quantity'])
if inputrow['y2'] != 0:
yield_day = "%.2f%%" % (inputrow['y2'] * 100)
elif operator.eq(inputrow['line_cd'], line) and operator.eq(inputrow['assy_text'], assy) \
and operator.eq(inputrow['dayornight'][0], 'N'):
input_night = int(inputrow['input_quantity'])
output_night = int(inputrow['output_quantity'])
ng_night = int(inputrow['ng_quantity'])
if inputrow['y2'] != 0:
yield_night = "%.2f%%" % (inputrow['y2'] * 100)
input_data.append(input_day)
input_data.append(input_night)
input_data_int.append(input_day)
input_data_int.append(input_night)
output_data.append(output_day)
output_data.append(output_night)
output_data_int.append(output_day)
output_data_int.append(output_night)
ng_data.append(ng_day)
ng_data.append(ng_night)
ng_data_int.append(ng_day)
ng_data_int.append(ng_night)
yield_data.append(yield_day)
yield_data.append(yield_night)
sums = sum(input_data_int)
input_data.append(sums)
sums = sum(output_data_int)
output_data.append(sums)
sums = sum(ng_data_int)
ng_data.append(sums)
if (sum(output_data_int) + sum(ng_data_int)) > 0:
sums = "%.2f%%" % (sum(output_data_int)/((sum(output_data_int) + sum(ng_data_int))*1.0) * 100)
else:
sums = "NP"
yield_data.append(sums)
assy_data.append(input_data)
assy_data.append(output_data)
assy_data.append(ng_data)
assy_data.append(yield_data)
img_full_name = img_path + models_common.getPicture(assy)
# 获取Target
target = models_common.get_target(assy,model_name)
result_assy.append({"name": assy,"target":target,"img": img_full_name,"data": assy_data, })
result_allLine.append({"assy_text": assy,"input_quantity": sum(input_data_int),"output_quantity": sum(output_data_int),\
"ng_quantity": sum(ng_data_int), })
y1_list = []
y1_list_temp = []
y2_list = []
y2_list_temp = []
# All Line的FPY(sub+main)等的计算
for key in assyDic_int:
flg = False
for row in result_allLine:
if operator.eq(row['assy_text'], assyDic_int[key]):
flg = True
if row["input_quantity"] > 0:
y1_list.append(row["output_quantity"] / (row["input_quantity"] * 1.0))
y1_list_temp.append(row["output_quantity"] / (row["input_quantity"] * 1.0))
else:
y1_list.append(float(1))
y1_list_temp.append('NP')
if row["output_quantity"] + row["ng_quantity"] > 0:
y2_list.append(row["output_quantity"] / ((row["output_quantity"] + row["ng_quantity"]) * 1.0))
y2_list_temp.append(row["output_quantity"] / ((row["output_quantity"] + row["ng_quantity"]) * 1.0))
else:
y2_list.append(float(1))
y2_list_temp.append('NP')
break
# 当前Assy在result_allLine里不存在的场合
if flg == False:
y1_list.append(float(1))
y1_list_temp.append('NP')
y2_list.append(float(1))
y2_list_temp.append('NP')
# Assembly Yield(sub+main)
y1_fpy_sub_value = models_common.get_formula_value(y1_list, y1_list_temp, fpy_sub_formula)
y2_fpy_sub_value = models_common.get_formula_value(y2_list, y2_list_temp, fpy_sub_formula)
# 计算Assy Yield(main)
y1_assembly_yield_sub_value = models_common.get_formula_value(y1_list, y1_list_temp, assembly_yield_sub_formula)
# 计算FPY(sub+main)
y1_fpy_value = models_common.get_formula_value(y1_list, y1_list_temp, fpy_formula)
# 计算FPY(main)
y1_assembly_yield_value = models_common.get_formula_value(y1_list, y1_list_temp, assembly_yield_formula)
# y1_fpy_sub的计算
y1_fpy_sub_data.append("%.2f%%" % (y1_fpy_sub_value * 100))
# y2_fpy_sub的计算
y2_fpy_sub_data.append("%.2f%%" % (y2_fpy_sub_value * 100))
# y1_assembly_yield_sub的计算
y1_assembly_yield_sub_data.append("%.2f%%" % (y1_assembly_yield_sub_value * 100))
# y1_fpy的计算
y1_fpy_data.append("%.2f%%" % (y1_fpy_value * 100))
# y1_assembly_yield的计算
y1_assembly_yield_data.append("%.2f%%" % (y1_assembly_yield_value * 100))
header_data.append(y1_fpy_sub_data)
header_data.append(y2_fpy_sub_data)
header_data.append(y1_assembly_yield_sub_data)
header_data.append(y1_fpy_data)
header_data.append(y1_assembly_yield_data)
result.append({"name": "VS", "img": "", "data": header_data, "lines": lines_hid, \
"timepart":timepartArr, "timepartName":timepartName, "timepartValue":timepartValue, \
"process_at":process_at,})
result.extend(result_assy)
except BaseException as exp:
print(exp)
result = models_common.databaseException(exp)
connections[model_name].close()
return result
def get_outputQuantity_line(model_name, process_at, sql_timepart, lineNum):
result = []
try:
cur = connections[model_name].cursor()
sql = "SELECT DISTINCT\
last_process.line_cd,\
last_process.assy_text,\
auto_ok.process_id,\
auto_ok.dayornight,\
auto_ok.ok_quantity AS output_quantity\
FROM\
(\
SELECT\
line_cd,\
process_id,\
dayornight,\
SUM( quantity ) AS ok_quantity\
FROM\
(\
SELECT\
line_cd,\
process_id,\
time_part AS dayornight,\
quantity\
FROM\
t_1_auto_io_" + lineNum + "\
WHERE\
process_at = '(process_at)' \
AND judge_text = 'ok' \
AND " + sql_timepart + " \
) AS auto\
GROUP BY\
line_cd,\
process_id,\
dayornight\
) AS auto_ok\
INNER JOIN (\
SELECT\
line_cd,\
assy_text,\
dayornight,\
MAX( process_id ) AS process_id\
FROM\
(\
SELECT\
auto2.line_cd,\
assy_text,\
auto2.process_id,\
time_part AS dayornight,\
SUM( auto2.quantity )\
FROM\
m_assy\
INNER JOIN t_1_auto_io_" + lineNum + " auto2 ON m_assy.process_id = auto2.process_id\
AND auto2.judge_text = 'ok' \
AND auto2.quantity > 0 \
AND process_at = '(process_at)' \
AND " + sql_timepart + " \
GROUP BY\
auto2.line_cd,\
assy_text,\
auto2.process_id,\
dayornight\
) AS a\
GROUP BY\
line_cd,\
assy_text,\
dayornight\
) AS last_process ON auto_ok.process_id = last_process.process_id\
AND auto_ok.line_cd = last_process.line_cd\
AND auto_ok.dayornight = last_process.dayornight\
ORDER BY last_process.line_cd,auto_ok.process_id,dayornight"
sql = sql.replace("(process_at)", process_at)
cur.execute(sql)
rows = cur.fetchall()
for row in rows:
result.append({"line_cd":row[0],"assy_text":row[1], "process_id":row[2],"dayornight":row[3],"output_quantity":int(row[4]),})
except BaseException as exp:
print(exp)
result = models_common.databaseException(exp)
connections[model_name].close()
return result
def get_trend(model_name, start_date, end_date, object, content, type):
process_at_list = models_common.get_periodDate(start_date, end_date)
# typeName设定
if type == "Y1":
typeName = "Y1"
elif type == "Y2":
typeName = "Y2"
elif type == "Input":
typeName = "input_quantity"
elif type == "Output":
typeName = "output_quantity"
else:
typeName = "ng_quantity"
data_zero = []
data_NP = []
i = 0
while i < len(process_at_list):
data_zero.append(0)
data_NP.append("NP")
i = i + 1
try:
if operator.eq('Line', object):
if operator.eq('Input', type):
# input
lineNum = "2nd"
result_quantity = models_common.get_inputQuantity(
model_name, start_date, end_date, 'All', False, content,
lineNum)
if result_quantity == 101 or result_quantity == 102:
return result_quantity
if operator.eq('Output', type):
# output
result_quantity = models_common.get_outputQuantity(
model_name, start_date, end_date, 'All', False, content,
lineNum)
if result_quantity == 101 or result_quantity == 102:
return result_quantity
if operator.eq('NG', type):
# input
lineNum = "2nd"
result_quantity = models_common.get_inputQuantity(
model_name, start_date, end_date, 'All', False, content,
lineNum)
if result_quantity == 101 or result_quantity == 102:
return result_quantity
# ng
result_ngQuantity = models_common.get_ngQuantity(
model_name, start_date, end_date, 'All', False, content,
lineNum)
if result_ngQuantity == 101 or result_ngQuantity == 102:
return result_ngQuantity
# ngQuantity合并到inputQuantity
for inputrow in result_quantity:
ng_quantity = 0
for ngrow in result_ngQuantity:
if operator.eq(ngrow['assy_text'], inputrow['assy_text']) and operator.eq(\
ngrow['process_at'], inputrow['process_at']):
ng_quantity = ngrow['ng_quantity']
break
inputrow["ng_quantity"] = ng_quantity
if operator.eq('Y1', type) or operator.eq('Y2', type):
# input
lineNum = "2nd"
result_quantity = models_common.get_inputQuantity(
model_name, start_date, end_date, 'All', False, content,
lineNum)
if result_quantity == 101 or result_quantity == 102:
return result_quantity
# output
result_outputQuantity = models_common.get_outputQuantity(
model_name, start_date, end_date, 'All', False, content,
lineNum)
if result_outputQuantity == 101 or result_outputQuantity == 102:
return result_outputQuantity
# ng
result_ngQuantity = models_common.get_ngQuantity(
model_name, start_date, end_date, 'All', False, content,
lineNum)
if result_ngQuantity == 101 or result_ngQuantity == 102:
return result_ngQuantity
# 计算y2，合并到inputQuantity
for inputrow in result_quantity:
output_quantity = 0
for outputrow in result_outputQuantity:
if operator.eq(outputrow['assy_text'], inputrow['assy_text']) and operator.eq(\
outputrow['process_at'], inputrow['process_at']):
output_quantity = outputrow['output_quantity']
break
ng_quantity = 0
for ngrow in result_ngQuantity:
if operator.eq(ngrow['assy_text'], inputrow['assy_text']) and operator.eq(\
ngrow['process_at'], inputrow['process_at']):
ng_quantity = ngrow['ng_quantity']
break
if output_quantity + ng_quantity > 0:
inputrow["Y2"] = output_quantity / (
(output_quantity + ng_quantity) * 1.0)
else:
inputrow["Y2"] = "NP"
if inputrow["input_quantity"] > 0:
inputrow["Y1"] = output_quantity / (
inputrow["input_quantity"] * 1.0)
else:
inputrow["Y1"] = "NP"
# get assy
objectArr = models_common.get_allAssyAndProcessId(model_name)
if objectArr == 101 or objectArr == 102:
return objectArr
# sort
result_quantity = sorted(result_quantity,
key=lambda x:
(x['assy_text'], x['process_at']))
else:
if operator.eq('Input', type):
# input
result_quantity = models_common.get_inputQuantity_sumOfLine(
model_name, start_date, end_date, content, False)
if result_quantity == 101 or result_quantity == 102:
return result_quantity
# get allLine
result_quantity_all = models_common.get_inputQuantity_sumOfLine(
model_name, start_date, end_date, content, True)
if result_quantity_all == 101 or result_quantity_all == 102:
return result_quantity_all
if operator.eq('Output', type):
# output
result_quantity = models_common.get_outputQuantity_sumOfLine(
model_name, start_date, end_date, content, False)
if result_quantity == 101 or result_quantity == 102:
return result_quantity
# get allLine
result_quantity_all = models_common.get_outputQuantity_sumOfLine(
model_name, start_date, end_date, content, True)
if result_quantity_all == 101 or result_quantity_all == 102:
return result_quantity_all
if operator.eq('NG', type):
# ng
result_quantity = models_common.get_ngQuantity_sumOfLine(
model_name, start_date, end_date, content, False)
if result_quantity == 101 or result_quantity == 102:
return result_quantity
# get allLine
result_quantity_all = models_common.get_ngQuantity_sumOfLine(
model_name, start_date, end_date, content, True)
if result_quantity_all == 101 or result_quantity_all == 102:
return result_quantity_all
if operator.eq('Y1', type) or operator.eq('Y2', type):
# input
result_quantity = models_common.get_inputQuantity_sumOfLine(
model_name, start_date, end_date, content, False)
if result_quantity == 101 or result_quantity == 102:
return result_quantity
# output
result_outputQuantity = models_common.get_outputQuantity_sumOfLine(
model_name, start_date, end_date, content, False)
if result_outputQuantity == 101 or result_outputQuantity == 102:
return result_outputQuantity
# ng
result_ngQuantity = models_common.get_ngQuantity_sumOfLine(
model_name, start_date, end_date, content, False)
if result_ngQuantity == 101 or result_ngQuantity == 102:
return result_ngQuantity
# 计算y2，合并到inputQuantity
for inputrow in result_quantity:
output_quantity = 0
for outputrow in result_outputQuantity:
if operator.eq(outputrow['line_cd'], inputrow['line_cd']) and operator.eq(\
outputrow['process_at'], inputrow['process_at']):
output_quantity = outputrow['output_quantity']
break
ng_quantity = 0
for ngrow in result_ngQuantity:
if operator.eq(ngrow['line_cd'], inputrow['line_cd']) and operator.eq(\
ngrow['process_at'], inputrow['process_at']):
ng_quantity = ngrow['ng_quantity']
break
if output_quantity + ng_quantity > 0:
inputrow["Y2"] = output_quantity / (
(output_quantity + ng_quantity) * 1.0)
else:
inputrow["Y2"] = "NP"
if inputrow["input_quantity"] > 0:
inputrow["Y1"] = output_quantity / (
inputrow["input_quantity"] * 1.0)
else:
inputrow["Y1"] = "NP"
# get line
objectArr = models_common.get_allLines(model_name)
if objectArr == 101 or objectArr == 102:
return objectArr
# 设定目标json数据
result = models_common.setDataList(process_at_list, result_quantity,
objectArr, model_name, object,
content, typeName)
# sort
if operator.eq('Line', object):
result = sorted(result, key=lambda x: (x['process_id']))
else:
result = sorted(result, key=lambda x: (x['process']))
# All Line追加
if object == 'Assy':
if type != 'Y1' and type != 'Y2':
if len(result_quantity_all) == 0:
result_all = {"process": "All Line", "data": data_zero}
else:
result_all = models_common.supplementZero(
"All Line", result_quantity_all, process_at_list,
'trend')
else:
# get allLine
if len(result_quantity) == 0:
if type == 'Y2':
# 获取TARGET
target = models_common.get_target(content, model_name)
result_all = {
'process': 'All Line',
'data': data_zero,
'target': target,
}
else:
result_all = {
'process': 'All Line',
'data': data_zero,
}
else:
data = models_common.getAvg_allLine(
result, process_at_list)
if type == 'Y2':
# 获取TARGET
target = models_common.get_target(content, model_name)
result_all = {
'process': 'All Line',
'data': data,
'target': target,
}
else:
result_all = {
'process': 'All Line',
'data': data,
}
result.append(result_all)
# 计算公式项追加
if object == 'Line' and (type == 'Y1' or type == 'Y2'):
database_list = models_common.get_config("database")
formulaDic = {}
assyDic = {}
assyDic_int = {}
for row in database_list:
if operator.eq(row['MODEL'], model_name):
# 从配置文件里取得ASSY
assyDic = row['ASSY']
# 从配置文件里取得FORMULA
formulaDic = row['FORMULA_TREND']
break
# 给key为整数的字典assyDic_int赋值
for key in assyDic:
assyDic_int[int(key)] = assyDic[key]
# 字典排序
sorted(assyDic_int.keys())
# fpy计算公式的取得
fpy_formula = formulaDic["FPY(main)"]
# FPY(sub+main)计算公式的取得
tester_yield_formula = formulaDic["FPY(sub+main)"]
# Assembly Yield(sub+main)计算公式的取得
assembly_yield_formula = formulaDic["Assembly Yield(sub+main)"]
# 按配置文件里ASSY的顺序排列result
result_temp = []
for key in assyDic_int:
flg = False
for row in result:
if operator.eq(row['process'], assyDic_int[key]):
flg = True
result_temp.append(row)
break
# 当前config里设定的Assy在result里不存在的场合
if flg == False:
# 获取TARGET
target = models_common.get_target(assyDic_int[key],
model_name)
result_temp.append({
'process': assyDic_int[key],
'data': data_NP,
'target': target,
})
assembly_yield_list = []
tester_yield_list = []
fpy_list = []
for i in range(len(process_at_list)):
y2_list = []
y2_list_temp = []
for row in result_temp:
yield2 = row['data'][i]
y2_list_temp.append(yield2)
if operator.eq(yield2, 'NP'):
yield2 = float(1)
y2_list.append(yield2)
# 计算y2_FPY(main)
y2_fpy_value = models_common.get_formula_value(
y2_list, y2_list_temp, fpy_formula)
# 计算y2_FPY(sub+main)
y2_tester_yield_value = models_common.get_formula_value(
y2_list, y2_list_temp, tester_yield_formula)
# 计算y2_Assembly Yield(sub+main)
y2_assembly_yield_value = models_common.get_formula_value(
y2_list, y2_list_temp, assembly_yield_formula)
# 将当前计算值添加到list
assembly_yield_list.append(y2_assembly_yield_value)
tester_yield_list.append(y2_tester_yield_value)
fpy_list.append(y2_fpy_value)
# 获取TARGET
assembly_yield_target = models_common.get_target(
'Assembly Yield(sub+main)', model_name)
tester_yield_target = models_common.get_target(
'Assembly Yield(sub+main)', model_name)
fpy_target = models_common.get_target('FPY(main)', model_name)
result.append({
'process': 'Assembly Yield(sub+main)',
'data': assembly_yield_list,
'target': assembly_yield_target,
})
result.append({
'process': 'FPY(sub+main)',
'data': tester_yield_list,
'target': tester_yield_target,
})
result.append({
'process': 'FPY(main)',
'data': fpy_list,
'target': fpy_target,
})
if type == 'Y1' or type == 'Y2':
# 转成百分比
for row in result:
i = 0
for value in row['data']:
if value != 'NP':
row['data'][i] = "%.2f%%" % (value * 100)
i = i + 1
except BaseException as exp:
print(exp)
result = models_common.databaseException(exp)
connections[model_name].close()
return result