def get_tree_other_level_node_info(dbconfig, batches, batch, box):
try:
db = MySQL(dbconfig)
#用order_num找的，不返回pid（同一返回pid=0）,用batches找的返回pid(以区分是第一层节点还是第二层节点)
sql_detail = "select id,material,material_description,order_num,batch,quantity_wi,unit,batches,pid from component_list where id in ({batches}) and batch != '';".\
format(batches=batches)
db.query(sql_detail)
detail_info = db.fetchAllRows()
sql_posting_date = "select posting_date from batch_order_relation where batch = '{batch}' order by posting_date desc limit 1;".format(
batch=batch)
db.query(sql_posting_date)
posting_date_info = db.fetchAllRows()
if posting_date_info != ():
posting_date = posting_date_info[0][0]
else:
posting_date = ''
db.close()
for detail in detail_info:
#如果子节点的batches存在，递归调用get_tree_other_level_node_info
batches = detail[7]
batch = detail[4]
line_info = dict(id=detail[0],
material=detail[1],
material_description=detail[2],
order_num=detail[3],
batch=detail[4],
quantity_wi=detail[5],
unit=detail[6],
pid=int(detail[8]),
posting_date=posting_date)
box.append(line_info)
if detail[7] != '':
get_tree_other_level_node_info(dbconfig, batches, batch, box)
else:
continue
except Exception as e:
print(e)
def materialtrace_old():
batch = request.args.get('batch', type=str, default="")
try:
dbconfig = {
'host': config.get('META', 'host'),
'port': int(config.get('META', 'port')),
'user': config.get('META', 'user'),
'passwd': config.get('META', 'pwd'),
'db': config.get('META', 'db'),
'charset': 'utf8'
}
boxes = []
db = MySQL(dbconfig)
#sql = "select batch,order_num from batch_order_relation where movement_type in (101) and batch = '{batch}' order by posting_date desc ;". \
# format(batch=batch)
sql = "select batch,order_num from batch_order_relation where movement_type in (101) and batch = '{batch}' group by batch,order_num ;". \
format(batch=batch)
db.query(sql)
batch_order_num_info = db.fetchAllRows()
db.close()
for batch_order_num in batch_order_num_info:
box = []
batch = batch_order_num[0]
order_num = batch_order_num[1]
tree_node_info = get_tree_first_level_node_info(
dbconfig, order_num, batch, box)
tree_info = list_to_tree(tree_node_info)
#logger.error(tree_info)
res = {}
res.setdefault("order_num",
"{order_num}".format(order_num=order_num))
res.setdefault("list", tree_info)
boxes.append(res)
#boxes.append({"order_num": "{order_num}".format(order_num=order_num), res.setdefault("list", []).append(tree_info)} )
#boxes.append(tree_info)
#print(boxes)
resp = Response(json.dumps(boxes))
resp.headers['Access-Control-Allow-Origin'] = '*'
resp.headers['Access-Control-Allow-Methods'] = 'GET,POST'
return resp
except Exception as e:
error_msg = "[action]:get material component " \
"[status]:FAIL" \
"[host]:{host}" \
"[port]:{port}" \
"[Errorcode]:{e}".format(e=e, host=config.get('META', 'host'), port=int(config.get('META', 'port')))
logger.error(error_msg)
def storagedetail():
'''
:return:
'''
binum = request.args.get('binum', type=str, default=None)
status = request.args.get('status', type=str, default=None)
if status == 'empty':
status = ''
try:
dbconfig = {'host': config.get('META', 'host'),
'port': int(config.get('META', 'port')),
'user': config.get('META', 'user'),
'passwd': config.get('META', 'pwd'),
'db': config.get('META', 'db'),
'charset': 'utf8'}
sql = "select material,storage_bin,status,batch,avail_stock,unit,material_desc,last_goods_rec,date_of_manuf,sled_bbd,next_inspection from tasly_warehouse_storage_info where storage_bin = '{binum}' and status='{status}';".format(binum=binum, status=status)
db = MySQL(dbconfig)
db.query(sql)
storagedetail_result = db.fetchAllRows()
db.close()
#print storagedetail_result
#dict_l = []
sub_dict_l = []
if storagedetail_result is not ():
for storagedetail in storagedetail_result:
material = storagedetail[0]
storage_bin = storagedetail[1]
status = storagedetail[2]
batch = storagedetail[3]
avail_stock = storagedetail[4]
unit = storagedetail[5]
material_desc = storagedetail[6]
last_goods_rec = storagedetail[7]
date_of_manuf = storagedetail[8]
sled_bbd = storagedetail[9]
next_inspection = storagedetail[10]
inventory_time = Caltime(last_goods_rec)
keys = ['material','storage_bin','status','batch','avail_stock','unit','material_desc','last_goods_rec',
'date_of_manuf', 'sled_bbd','next_inspection', 'inventory_time']
#print keys
values = [material, storage_bin, status, batch, avail_stock, unit, material_desc, last_goods_rec, date_of_manuf,
sled_bbd, next_inspection, inventory_time]
#print values
#sub_dict_l = []
for i in range(0,12):
#print values[i],keys[i]
sub_keys = ['name', 'value']
sub_values = [keys[i],values[i]]
detail_info = dict(zip(sub_keys, sub_values))
#print detail_info
sub_dict_l.append(detail_info)
#dict_l.append(sub_dict_l)
#print dict_l
else:
keys = ['material','storage_bin','status','batch','avail_stock','unit','material_desc','last_goods_rec',
'date_of_manuf', 'sled_bbd','next_inspection', 'inventory_time']
#print keys
values = ["", "", "", "", "", "", "", "", "", "", "", ""]
#print values
#sub_dict_l = []
for i in range(0,12):
#print values[i],keys[i]
sub_keys = ['name', 'value']
sub_values = [keys[i],values[i]]
detail_info = dict(zip(sub_keys, sub_values))
#print detail_info
sub_dict_l.append(detail_info)
#dict_l.append(sub_dict_l)
#print dict_l
#return jsonify(sub_dict_l)
resp = Response(json.dumps(sub_dict_l))
resp.headers['Access-Control-Allow-Origin'] = '*'
resp.headers['Access-Control-Allow-Methods'] = 'GET,POST'
return resp
except Exception as e:
error_msg = "[action]:get storage detail info " \
"[status]:FAIL" \
"[host]:{host}" \
"[port]:{port}" \
"[Errorcode]:{e}".format(e=e, host=config.get('META', 'host'), port=int(config.get('META', 'port')))
logger.error(error_msg)
def storagebin():
'''
:put in: warehouse_type 1~8
:return:
[{
"name": "Rack货架 1#",
"list": ["01-06-02", "01-06-03"]
},
{
"name": "Rack货架 2#",
"list": ["01-06-02", "01-06-03"]
}
]
'''
warehouse_type = request.args.get('warehouse_type', type=int, default=None)
try:
dbconfig = {'host': config.get('META', 'host'),
'port': int(config.get('META', 'port')),
'user': config.get('META', 'user'),
'passwd': config.get('META', 'pwd'),
'db': config.get('META', 'db'),
'charset': 'utf8'}
if warehouse_type == 1:
dict1_l = []
rack_type_l = ['一号货架', '二号货架', '三号货架', '四号货架', '五号货架', '六号货架']
for rack_type in rack_type_l:
#sql = "select a.rack_type,a.storage_bin,b.status from tasly_warehouse_storage_bin a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.warehouse_type = 1 and a.rack_type = '{rack_type}' order by a.storage_bin;".format(rack_type=rack_type)
sql = "select distinct(a.storage_bin),b.status,a.rack_type from tasly_warehouse_storage_bin a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.warehouse_type = 1 and a.rack_type = '{rack_type}' order by a.storage_bin;".format(
rack_type=rack_type)
db = MySQL(dbconfig)
db.query(sql)
Rack_info = db.fetchAllRows()
db.close()
#按照点位号 排序
#sorted(Rack_info, key=lambda x: x[1])
print(Rack_info)
name = Rack_info[0][2]
bin_list = []
sub_keys = ['name', 'status']
for bin_number in Rack_info:
#sub_values = [bin_number[1], bin_number[2]]
#print sub_values
#if bin_number[2] is None:
# sub_values = [bin_number[1], '']
#else:
# sub_values = [bin_number[1], bin_number[2]]
sub_values = [bin_number[0], bin_number[1]]
detail_info = dict(zip(sub_keys, sub_values))
bin_list.append(detail_info)
keys = ['name', 'list']
values = [name, bin_list]
dict1_rack = dict(zip(keys, values))
dict1_l.append(dict1_rack)
#return jsonify(dict1_l)
#return json.dumps(dict1_l,encoding = 'utf8', ensure_ascii=False)
resp = Response(json.dumps(dict1_l))
resp.headers['Access-Control-Allow-Origin'] = '*'
resp.headers['Access-Control-Allow-Methods'] = 'GET,POST'
return resp
else:
sql = "select distinct(a.storage_bin),b.status from tasly_warehouse_storage_bin a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where warehouse_type = {warehouse_type};".format(warehouse_type=warehouse_type)
db = MySQL(dbconfig)
db.query(sql)
storagebin_info = db.fetchAllRows()
#print storagebin_info
db.close()
bin_list = []
for bin_number in storagebin_info:
sub_keys = ['name', 'status']
#if bin_number[1] is None:
# sub_values = [bin_number[0], '']
#else:
# sub_values = [bin_number[0], bin_number[1]]
sub_values = [bin_number[0], bin_number[1]]
detail_info = dict(zip(sub_keys, sub_values))
bin_list.append(detail_info)
#return jsonify(bin_list)
#print json.dumps(reload_info)
resp = Response(json.dumps(bin_list))
resp.headers['Access-Control-Allow-Origin'] = '*'
resp.headers['Access-Control-Allow-Methods'] = 'GET,POST'
return resp
except Exception as e:
error_msg = "[action]:get storage bin info " \
"[status]:FAIL" \
"[host]:{host}" \
"[port]:{port}" \
"[Errorcode]:{e}".format(e=e, host=config.get('META', 'host'), port=int(config.get('META', 'port')))
logger.error(error_msg)
def batch():
'''
:put in: batch_type 1(API-原料),2(API-成品)
:return:
[{
"name": "API-原料",
"list": ["19000231", "19000230"]
}
]
[
{
"name": "API-成品",
"list": ["19000231", "19000230"]
}
]
'''
batch_type = request.args.get('batch_type', type=int, default=None)
try:
dbconfig = {
'host': config.get('META', 'host'),
'port': int(config.get('META', 'port')),
'user': config.get('META', 'user'),
'passwd': config.get('META', 'pwd'),
'db': config.get('META', 'db'),
'charset': 'utf8'
}
if batch_type == 1:
#sql = "select a.storage_bin,a.batch,b.status from tasly_warehouse_storage_info a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.storage_bin = 'API-原料';"
sql = "select storage_bin,batch,status from tasly_warehouse_storage_info where storage_bin = 'API-原料';"
db = MySQL(dbconfig)
db.query(sql)
storagebin_info = db.fetchAllRows()
#print storagebin_info
db.close()
bin_list = []
for bin_number in storagebin_info:
sub_keys = ['name', 'batch', 'status']
#if bin_number[1] is None:
# sub_values = [bin_number[0], '']
#else:
# sub_values = [bin_number[0], bin_number[1]]
sub_values = [bin_number[0], bin_number[1], bin_number[2]]
detail_info = dict(zip(sub_keys, sub_values))
bin_list.append(detail_info)
#return jsonify(bin_list)
#print json.dumps(reload_info)
resp = Response(json.dumps(bin_list))
resp.headers['Access-Control-Allow-Origin'] = '*'
resp.headers['Access-Control-Allow-Methods'] = 'GET,POST'
return resp
elif batch_type == 2:
#sql = "select a.storage_bin,a.batch,b.status from tasly_warehouse_storage_info a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.storage_bin = 'API-成品';"
sql = "select storage_bin,batch,status from tasly_warehouse_storage_info where storage_bin = 'API-成品';"
db = MySQL(dbconfig)
db.query(sql)
storagebin_info = db.fetchAllRows()
# print storagebin_info
db.close()
bin_list = []
for bin_number in storagebin_info:
sub_keys = ['name', 'batch', 'status']
# if bin_number[1] is None:
# sub_values = [bin_number[0], '']
# else:
# sub_values = [bin_number[0], bin_number[1]]
sub_values = [bin_number[0], bin_number[1], bin_number[2]]
detail_info = dict(zip(sub_keys, sub_values))
bin_list.append(detail_info)
# return jsonify(bin_list)
# print json.dumps(reload_info)
resp = Response(json.dumps(bin_list))
resp.headers['Access-Control-Allow-Origin'] = '*'
resp.headers['Access-Control-Allow-Methods'] = 'GET,POST'
return resp
elif batch_type == 3:
#sql = "select a.storage_bin,a.batch,b.status from tasly_warehouse_storage_info a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.storage_bin = 'API-成品';"
sql = "select storage_bin,batch,status from tasly_warehouse_storage_info where storage_bin = 'CF CABINET';"
db = MySQL(dbconfig)
db.query(sql)
storagebin_info = db.fetchAllRows()
# print storagebin_info
db.close()
bin_list = []
for bin_number in storagebin_info:
sub_keys = ['name', 'batch', 'status']
# if bin_number[1] is None:
# sub_values = [bin_number[0], '']
# else:
# sub_values = [bin_number[0], bin_number[1]]
sub_values = [bin_number[0], bin_number[1], bin_number[2]]
detail_info = dict(zip(sub_keys, sub_values))
bin_list.append(detail_info)
# return jsonify(bin_list)
# print json.dumps(reload_info)
resp = Response(json.dumps(bin_list))
resp.headers['Access-Control-Allow-Origin'] = '*'
resp.headers['Access-Control-Allow-Methods'] = 'GET,POST'
return resp
elif batch_type == 4:
#sql = "select a.storage_bin,a.batch,b.status from tasly_warehouse_storage_info a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.storage_bin = 'API-成品';"
sql = "select storage_bin,batch,status from tasly_warehouse_storage_info where storage_bin = 'VENDOR';"
db = MySQL(dbconfig)
db.query(sql)
storagebin_info = db.fetchAllRows()
# print storagebin_info
db.close()
bin_list = []
for bin_number in storagebin_info:
sub_keys = ['name', 'batch', 'status']
# if bin_number[1] is None:
# sub_values = [bin_number[0], '']
# else:
# sub_values = [bin_number[0], bin_number[1]]
sub_values = [bin_number[0], bin_number[1], bin_number[2]]
detail_info = dict(zip(sub_keys, sub_values))
bin_list.append(detail_info)
# return jsonify(bin_list)
# print json.dumps(reload_info)
resp = Response(json.dumps(bin_list))
resp.headers['Access-Control-Allow-Origin'] = '*'
resp.headers['Access-Control-Allow-Methods'] = 'GET,POST'
return resp
except Exception as e:
error_msg = "[action]:get batch info " \
"[status]:FAIL" \
"[host]:{host}" \
"[port]:{port}" \
"[Errorcode]:{e}".format(e=e, host=config.get('META', 'host'), port=int(config.get('META', 'port')))
logger.error(error_msg)
def searchinfo():
'''
:put in:
:return:
[{
"name": "API-原料",
"list": ["19000231", "19000230"]
}
]
[
{
"name": "API-成品",
"list": ["19000231", "19000230"]
}
]
'''
search_type = request.args.get('search_type', type=str, default="")
search_keys = request.args.get('search_keys', type=str, default="")
'''
if search_type == "storage_bin":
# 通过storage_bin字段搜索
sql = build_sql(search_type, search_keys)
elif search_type == "material":
# 通过material字段搜索
sql = build_sql(search_type, search_keys)
elif search_type == "batch":
# 通过batch字段搜索
sql = build_sql(search_type, search_keys)
elif search_type == "material_desc":
# 通过material_desc字段搜索
sql = build_sql(search_type, search_keys)
else:
sql = "select id, material, storage_bin, batch, material_desc, avail_stock, unit, last_goods_rec, date_of_manuf, sled_bbd, next_inspection, status from tasly_warehouse_storage_info order by id;"
'''
if search_type in ["storage_bin", "material", "batch", "material_desc"]:
#logger.error(search_type)
#logger.error(search_keys)
sql = build_sql(search_type, search_keys)
else:
sql = "select id, material, storage_bin, batch, material_desc, avail_stock, unit, last_goods_rec, date_of_manuf, sled_bbd, next_inspection, status from tasly_warehouse_storage_info order by id;"
try:
dbconfig = {
'host': config.get('META', 'host'),
'port': int(config.get('META', 'port')),
'user': config.get('META', 'user'),
'passwd': config.get('META', 'pwd'),
'db': config.get('META', 'db'),
'charset': 'utf8'
}
#sql = "select a.storage_bin,a.batch,b.status from tasly_warehouse_storage_info a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.storage_bin = 'API-原料';"
#sql = "select id, material, storage_bin, batch, material_desc, avail_stock, unit, last_goods_rec, date_of_manuf, sled_bbd, next_inspection, status from tasly_warehouse_storage_info order by id;"
db = MySQL(dbconfig)
db.query(sql)
storagebin_info = db.fetchAllRows()
#print storagebin_info
db.close()
bin_list = []
for bin_number in storagebin_info:
last_goods_rec = bin_number[7]
inventory_time = Caltime(last_goods_rec)
sub_keys = [
'id', 'material', 'storage_bin', 'batch', 'material_desc',
'avail_stock', 'unit', 'last_goods_rec', 'date_of_manuf',
'sled_bbd', 'next_inspection', 'inventory_time', 'status'
]
#if bin_number[1] is None:
# sub_values = [bin_number[0], '']
#else:
# sub_values = [bin_number[0], bin_number[1]]
sub_values = [
bin_number[0], bin_number[1], bin_number[2], bin_number[3],
bin_number[4], bin_number[5], bin_number[6], bin_number[7],
bin_number[8], bin_number[9], bin_number[10],
int(inventory_time), bin_number[11]
]
detail_info = dict(zip(sub_keys, sub_values))
bin_list.append(detail_info)
#return jsonify(bin_list)
#print json.dumps(reload_info)
bin_list.sort(key=takeSecond, reverse=True)
resp = Response(json.dumps(bin_list))
resp.headers['Access-Control-Allow-Origin'] = '*'
resp.headers['Access-Control-Allow-Methods'] = 'GET,POST'
return resp
except Exception as e:
error_msg = "[action]:get search info " \
"[status]:FAIL" \
"[host]:{host}" \
"[port]:{port}" \
"[Errorcode]:{e}".format(e=e, host=config.get('META', 'host'), port=int(config.get('META', 'port')))
logger.error(error_msg)
def get_tree_other_level_node_info(dbconfig, batch, relation_batches, box):
try:
#组成物料的信息
db = MySQL(dbconfig)
#用relation_id找的，不返回pid（同一返回pid=0）,用batches找的返回pid(以区分是第一层节点还是第二层节点)(第一层返回的是成品信息)(时间相关的值和单位在另外的逻辑里)
sql_detail = "select id,material,material_description,order_num,batch,quantity,unit,relation_batches,pid from batch_order_relation where id in ({relation_batches});".\
format(relation_batches=relation_batches)
db.query(sql_detail)
detail_info = db.fetchAllRows()
db.close()
for detail in detail_info:
db = MySQL(dbconfig)
'''
#单位unit
material = detail[1]
material_description = detail[2]
sql_unit = "select unit from unit_info where material = '{material}' and material_description = '{material_description}';".format(material=material, material_description=material_description)
#logger.info(sql_unit)
db.query(sql_unit)
unit_info = db.fetchOneRow()
#logger.info(unit_info)
if unit_info != ():
unit = unit_info[0]
else:
unit = ''
#logger.info(unit)
'''
#如果不再包含下级组件 则展示 生成时间='', 消耗时间,物料收货时间,放行时间; 若包含下级组件则展示 生成时间,消耗时间,物料收货时间='', 放行时间
batch_sub = detail[4]
#logger.info(batch_sub)
order_num = detail[3]
#logger.info(order_num)
#TODO:这个地方找的不对用１０１重新找！！！！！！
#sql_relation_batches = "select relation_batches from batch_order_relation where batch = '{batch_sub}' and order_num = '{order_num}' and movement_type in (101) order by posting_date desc limit 1;". \
# format(batch_sub=batch_sub, order_num=order_num)
sql_relation_batches = "select relation_batches from batch_order_relation where batch = '{batch_sub}' and movement_type in (101) order by posting_date desc limit 1;". \
format(batch_sub=batch_sub)
#logger.info(sql_relation_batches)
db.query(sql_relation_batches)
relation_batches_info = db.fetchAllRows()
if relation_batches_info == ():
relation_batches = ''
else:
relation_batches = relation_batches_info[0][0]
#logger.info(relation_batches)
#不再包含下级组件
if relation_batches == '':
# 生成时间 为空
creating_date = ''
# 消耗时间261 consuming_date
sql_consuming_date = "select order_num,max(posting_date) from batch_order_relation where batch = '{batch}' and order_num = '{order_num}' and movement_type in (261) group by order_num;".format(
batch=batch_sub, order_num=order_num)
# logger.info(sql_consuming_date)
db.query(sql_consuming_date)
consuming_date_info = db.fetchAllRows()
if consuming_date_info != ():
consuming_date = consuming_date_info[0][1]
else:
consuming_date = ''
# 物料收获时间101 gaining_date
# sql_gaining_date = "select order_num,max(posting_date) from batch_order_relation where batch = '{batch}' and order_num = '{order_num}' and movement_type in (101) group by order_num;".format(
# batch=batch_sub, order_num=order_num)
# 从全部数据里取当前batch下movement_type=101的数据，对应的posting_date为物料收获时间
sql_gaining_date = "select order_num,max(posting_date) from batch_order_relation where batch = '{batch}' and movement_type in (101) group by order_num;".format(
batch=batch_sub)
# logger.info(sql_gaining_date)
db.query(sql_gaining_date)
gaining_date_info = db.fetchAllRows()
if gaining_date_info != ():
gaining_date = gaining_date_info[0][1]
else:
gaining_date = ''
# 放行时间 321的posting_date
# sql_pasting_date = "select order_num,max(posting_date) from batch_order_relation where batch = '{batch}' and order_num = '{order_num}' and movement_type in (321) group by order_num;".format(
# batch=batch_sub,order_num=order_num)
# 从全部数据里取当前batch下movement_type=321的数据，对应的posting_date为放行时间
sql_pasting_date = "select order_num,max(posting_date) from batch_order_relation where batch = '{batch}' and movement_type in (321) group by order_num;".format(
batch=batch_sub)
# logger.info(sql_pasting_date)
db.query(sql_pasting_date)
pasting_date_info = db.fetchAllRows()
# logger.info(pasting_date_info)
if pasting_date_info != ():
pasting_date = pasting_date_info[0][1]
else:
pasting_date = ''
# logger.info(pasting_date)
#包含下级组件
else:
# 生成时间 101的posting_date
# sql_creating_date = "select order_num,max(posting_date) from batch_order_relation where batch = '{batch}' and order_num = '{order_num}' and movement_type in (101) group by order_num;".format(
# batch=batch_sub,order_num=order_num)
# 从全部数据里取当前batch下movement_type=101的数据，对应的posting_date为生成时间
sql_creating_date = "select order_num,max(posting_date) from batch_order_relation where batch = '{batch}' and movement_type in (101) group by order_num;".format(
batch=batch_sub)
# logger.info(sql_creating_date)
db.query(sql_creating_date)
creating_date_info = db.fetchAllRows()
if creating_date_info != ():
creating_date = creating_date_info[0][1]
else:
creating_date = ''
# 消耗时间 consuming_date
sql_consuming_date = "select order_num,max(posting_date) from batch_order_relation where batch = '{batch}' and order_num = '{order_num}' and movement_type in (261) group by order_num;".format(
batch=batch_sub, order_num=order_num)
# logger.info(sql_consuming_date)
db.query(sql_consuming_date)
consuming_date_info = db.fetchAllRows()
if consuming_date_info != ():
consuming_date = consuming_date_info[0][1]
else:
consuming_date = ''
# 物料收获时间为空 gaining_date
gaining_date = ''
# 放行时间 321的posting_date
# sql_pasting_date = "select order_num,max(posting_date) from batch_order_relation where batch = '{batch}' and order_num = '{order_num}' and movement_type in (321) group by order_num;".format(
# batch=batch_sub, order_num=order_num)
# 从全部数据里取当前batch下movement_type=321的数据，对应的posting_date为放行时间
sql_pasting_date = "select order_num,max(posting_date) from batch_order_relation where batch = '{batch}' and movement_type in (321) group by order_num;".format(
batch=batch_sub)
# logger.info(sql_pasting_date)
db.query(sql_pasting_date)
pasting_date_info = db.fetchAllRows()
if pasting_date_info != ():
pasting_date = pasting_date_info[0][1]
else:
pasting_date = ''
db.close()
#整合信息
quantity_wi = str(
abs(Decimal(detail[5]).quantize(Decimal('0.000'))))
line_info = dict(id=detail[0],
material=detail[1],
material_description=detail[2],
order_num=detail[3],
batch=detail[4],
quantity_wi=quantity_wi,
unit=detail[6],
pid=int(detail[8]),
creating_date=creating_date,
consuming_date=consuming_date,
gaining_date=gaining_date,
pasting_date=pasting_date)
#logger.info(line_info)
box.append(line_info)
#当前batch等于 上级batch 就继续
if batch == batch_sub:
continue
#还有下级组件
elif relation_batches != '':
#logger.info(relation_batches)
#logger.info(batch_sub)
get_tree_other_level_node_info(dbconfig, batch_sub,
relation_batches, box)
logger.info(box)
return box
except Exception as e:
print(e)
def materialtrace():
batch = request.args.get('batch', type=str, default="")
try:
dbconfig = {
'host': config.get('META', 'host'),
'port': int(config.get('META', 'port')),
'user': config.get('META', 'user'),
'passwd': config.get('META', 'pwd'),
'db': config.get('META', 'db'),
'charset': 'utf8'
}
boxes = []
db = MySQL(dbconfig)
sql = "select batch,order_num,max(id),max(relation_batches) from batch_order_relation where movement_type in (101) and batch = '{batch}' group by batch,order_num ;". \
format(batch=batch)
db.query(sql)
batch_order_num_id_info = db.fetchAllRows()
db.close()
for batch_order_num_id in batch_order_num_id_info:
box = []
batch = batch_order_num_id[0]
order_num = batch_order_num_id[1]
relation_id = batch_order_num_id[2]
relation_batches = batch_order_num_id[3]
if is_finish_product(dbconfig, batch, relation_batches):
#成品信息
finish_product_info = get_finish_product_info(
dbconfig, order_num, batch, relation_id)
else:
finish_product_info = dict(id='',
material='',
material_description='',
order_num='',
batch='',
quantity_wi='',
unit='',
creating_date='',
consuming_date='',
gaining_date='',
pasting_date='')
# 组成物料的信息
tree_node_info = get_tree_first_level_node_info(
dbconfig, batch, relation_batches, box)
#logger.info(tree_node_info)
#排序tree_node_info数组，把有下级组成物料的信息 压在数组的最下面，这样组成的树形结构更清晰
tree_node_info.sort(key=takeCreating_date)
#TODO:确实是这有问题 着重测这个函数
tree_info = list_to_tree(tree_node_info)
#logger.info(tree_info)
res = {}
res.setdefault("order_num",
"{order_num}".format(order_num=order_num))
res.setdefault("finish_product_info", finish_product_info)
res.setdefault("list", tree_info)
boxes.append(res)
#print(boxes)
#logger.info(boxes)
resp = Response(json.dumps(boxes))
resp.headers['Access-Control-Allow-Origin'] = '*'
resp.headers['Access-Control-Allow-Methods'] = 'GET,POST'
return resp
except Exception as e:
error_msg = "[action]:get material component " \
"[status]:FAIL" \
"[host]:{host}" \
"[port]:{port}" \
"[Errorcode]:{e}".format(e=e, host=config.get('META', 'host'), port=int(config.get('META', 'port')))
logger.error(error_msg)
def get_finish_product_info(dbconfig, order_num, batch, relation_id):
try:
#组成物料的信息
db = MySQL(dbconfig)
#用relation_id找的，不返回pid（同一返回pid=0）,用batches找的返回pid(以区分是第一层节点还是第二层节点)(第一层返回的是成品信息)(时间相关的值和单位在另外的逻辑里)
sql_detail = "select id,material,material_description,order_num,batch,quantity,unit,relation_batches,0 from batch_order_relation where id = {relation_id} ;".\
format(relation_id=relation_id)
#logger.info(sql_detail)
db.query(sql_detail)
detail_info = db.fetchAllRows()
#如果能取到成品信息
if detail_info != ():
'''
#单位现在直接取表中字段不用计算了
#单位unit
material = detail_info[0][1]
material_description = detail_info[0][2]
sql_unit = "select unit from unit_info where material = '{material}' and material_description = '{material_description}';".format(material=material, material_description=material_description)
#logger.info(sql_unit)
db.query(sql_unit)
unit_info = db.fetchAllRows()
if unit_info != ():
unit = unit_info[0][0]
else:
unit = ''
#logger.info('unit')
#logger.info(unit)
'''
#生成时间 101的posting_date
sql_creating_date = "select order_num,max(posting_date) from batch_order_relation where batch = '{batch}' and order_num = '{order_num}' and movement_type in (101) group by order_num;".format(
batch=batch, order_num=order_num)
#logger.info(sql_creating_date)
db.query(sql_creating_date)
creating_date_info = db.fetchAllRows()
if creating_date_info != ():
creating_date = creating_date_info[0][1]
else:
creating_date = ''
#logger.info('creating_date')
#logger.info(creating_date)
#消耗时间为空 consuming_date
consuming_date = ''
#物料收获时间为空 gaining_date
gaining_date = ''
#放行时间 321的posting_date
sql_pasting_date = "select order_num,max(posting_date) from batch_order_relation where batch = '{batch}' and order_num = '{order_num}' and movement_type in (321) group by order_num;".format(
batch=batch, order_num=order_num)
#logger.info(sql_pasting_date)
db.query(sql_pasting_date)
pasting_date_info = db.fetchAllRows()
logger.info('pasting_date_info')
logger.info(pasting_date_info)
if pasting_date_info != ():
pasting_date = pasting_date_info[0][1]
else:
pasting_date = ''
logger.info('pasting_date')
logger.info(pasting_date)
db.close()
quantity_wi = str(
abs(Decimal(detail_info[0][5]).quantize(Decimal('0.000'))))
finish_product_info = dict(id=detail_info[0][0],
material=detail_info[0][1],
material_description=detail_info[0][2],
order_num=detail_info[0][3],
batch=detail_info[0][4],
quantity_wi=quantity_wi,
unit=detail_info[0][6],
creating_date=creating_date,
consuming_date=consuming_date,
gaining_date=gaining_date,
pasting_date=pasting_date)
logger.info('finish_product_info')
logger.info(finish_product_info)
else:
finish_product_info = ()
#logger.info(finish_product_info)
return finish_product_info
except Exception as e:
print(e)
def is_finish_product(dbconfig, batch, relation_batches):
db = MySQL(dbconfig)
# 如果101的信息 同时又 包含261的信息 那么它就不是成品信息;除非它的下级中的batch有和它相同的，那么它就是半成品 ，要显示成品信息
sql_is_finish_product = "select batch,order_num,id,relation_batches from batch_order_relation where movement_type in (261) and batch = '{batch}' ;". \
format(batch=batch)
logger.info(sql_is_finish_product)
db.query(sql_is_finish_product)
finish_product_261_info = db.fetchAllRows()
logger.info(finish_product_261_info)
db.close()
#半成品
db = MySQL(dbconfig)
sql_sub_batch = "select batch from batch_order_relation where id in ({relation_batches}) ;". \
format(relation_batches=relation_batches)
#logger.info(sql_sub_batch)
db.query(sql_sub_batch)
sub_batch_info = db.fetchAllRows()
#logger.info(sub_batch_info)
db.close()
sub_batch_list = []
for sub_batch in sub_batch_info:
sub_batch_item = sub_batch[0]
sub_batch_list.append(sub_batch_item)
if finish_product_261_info == ():
return True
else:
if batch in sub_batch_list:
return True
else:
return False
def utilization_rate():
warehouse_type = request.args.get('warehouse_type', type=int, default=None)
'''
#高架库区１ 每个货架 货架总货位数
select count(1) from tasly_warehouse_storage_bin a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.warehouse_type = 1 and a.rack_type = 'Rack 货架1#' ;
#高架库区１ 每个货架 货架未使用位数
select count(1) from tasly_warehouse_storage_bin a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.warehouse_type = 1 and a.rack_type = 'Rack 货架1#' and b.status is NULL;
#高架库区１ 每个货架 货架 占用率
'''
try:
dbconfig = {
'host': config.get('META', 'host'),
'port': int(config.get('META', 'port')),
'user': config.get('META', 'user'),
'passwd': config.get('META', 'pwd'),
'db': config.get('META', 'db'),
'charset': 'utf8'
}
dict1_l = []
if warehouse_type == 1:
rack_type_l = ['一号货架', '二号货架', '三号货架', '四号货架', '五号货架', '六号货架']
for rack_type in rack_type_l:
#sql_total = "select count(1) from tasly_warehouse_storage_bin a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.warehouse_type = 1 and a.rack_type = '{rack_type}' ;".format(rack_type=rack_type)
sql_total = "select count(1) from tasly_warehouse_storage_bin where warehouse_type = 1 and rack_type = '{rack_type}' ;".format(
rack_type=rack_type)
db = MySQL(dbconfig)
db.query(sql_total)
Rack_total_info = db.fetchAllRows()
total_num = Rack_total_info[0][0]
sql_vacancy = "select count(1) from tasly_warehouse_storage_bin a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.warehouse_type = 1 and a.rack_type = '{rack_type}' and b.status is NULL;".format(
rack_type=rack_type)
db = MySQL(dbconfig)
db.query(sql_vacancy)
Rack_vacancy_info = db.fetchAllRows()
vacancy_num = Rack_vacancy_info[0][0]
db.close()
if int(total_num) != 0:
util_rate = (int(total_num) -
int(vacancy_num)) / int(total_num)
else:
util_rate = 0
#sub_keys = ['total_num', 'vacancy_num', 'util_rate']
sub_keys = [rack_type, '未使用', '使用率']
sub_values = [total_num, vacancy_num, util_rate]
detail_info = dict(zip(sub_keys, sub_values))
keys = ['name', 'list']
values = [rack_type, detail_info]
dict1_rack = dict(zip(keys, values))
dict1_l.append(dict1_rack)
#高架库区１ 总使用率，库位总量，总未使用
#sql_all_total = "select count(1) from tasly_warehouse_storage_bin a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.warehouse_type = 1 ;"
sql_all_total = "select count(1) from tasly_warehouse_storage_bin where warehouse_type = 1 ;"
db = MySQL(dbconfig)
db.query(sql_all_total)
Rack_all_total_info = db.fetchAllRows()
all_total_num = Rack_all_total_info[0][0]
sql_all_vacancy = "select count(1) from tasly_warehouse_storage_bin a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.warehouse_type = 1 and b.status is NULL;"
db = MySQL(dbconfig)
db.query(sql_all_vacancy)
Rack_all_vacancy_info = db.fetchAllRows()
all_vacancy_num = Rack_all_vacancy_info[0][0]
db.close()
if int(all_total_num) != 0:
all_util_rate = (int(all_total_num) -
int(all_vacancy_num)) / int(all_total_num)
else:
all_util_rate = 0
sub_keys = ['高架库区1', '未使用', '使用率']
sub_values = [all_total_num, all_vacancy_num, all_util_rate]
detail_info = dict(zip(sub_keys, sub_values))
keys = ['name', 'list']
values = ['total', detail_info]
dict1_rack = dict(zip(keys, values))
dict1_l.append(dict1_rack)
#return jsonify(dict1_l)
resp = Response(json.dumps(dict1_l))
resp.headers['Access-Control-Allow-Origin'] = '*'
resp.headers['Access-Control-Allow-Methods'] = 'GET,POST'
return resp
else:
sql_total = "select count(distinct(a.storage_bin)) from tasly_warehouse_storage_bin a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.warehouse_type = {warehouse_type} ;".format(
warehouse_type=warehouse_type)
db = MySQL(dbconfig)
db.query(sql_total)
Rack_total_info = db.fetchAllRows()
total_num = Rack_total_info[0][0]
sql_vacancy = "select count(1) from tasly_warehouse_storage_bin a left join tasly_warehouse_storage_info b on a.storage_bin = b.storage_bin where a.warehouse_type = {warehouse_type} and b.status is NULL;".format(
warehouse_type=warehouse_type)
db = MySQL(dbconfig)
db.query(sql_vacancy)
Rack_vacancy_info = db.fetchAllRows()
vacancy_num = Rack_vacancy_info[0][0]
db.close()
if int(total_num) != 0:
util_rate = (int(total_num) -
int(vacancy_num)) / int(total_num)
else:
util_rate = 0
#sub_keys = ['total_num', 'vacancy_num', 'util_rate']
sub_keys = ['库位总量', '未使用', '使用率']
sub_values = [total_num, vacancy_num, util_rate]
detail_info = dict(zip(sub_keys, sub_values))
keys = ['name', 'list']
values = [warehouse_type, detail_info]
dict1_rack = dict(zip(keys, values))
dict1_l.append(dict1_rack)
#return jsonify(dict1_l)
resp = Response(json.dumps(dict1_l))
resp.headers['Access-Control-Allow-Origin'] = '*'
resp.headers['Access-Control-Allow-Methods'] = 'GET,POST'
return resp
except Exception as e:
error_msg = "[action]:get storage utilization rate " \
"[status]:FAIL" \
"[host]:{host}" \
"[port]:{port}" \
"[Errorcode]:{e}".format(e=e, host=config.get('META', 'host'), port=int(config.get('META', 'port')))
logger.error(error_msg)