def insert_same_data(bon_version, url, new_data, shape_data, bin_data, comment, best_num):
conn = Mssql()
sql_text = "SELECT * FROM T_BOM_PlateUtilUsedRate WHERE BOMVersion='%s'" % bon_version
# 拿结果
res = conn.exec_query(sql_text)
# 先看是否存在, 存在就删除原来数据
sql_text = "delete T_BOM_PlateUtilState where BOMVersion='%s'" % new_data['BOMVersion']
conn.exec_non_query(sql_text)
sql_text = "delete T_BOM_PlateUtilUsedRate where BOMVersion='%s'" % new_data['BOMVersion']
conn.exec_non_query(sql_text)
# 插入新数据
insert_data = list()
for data in res:
insert_data.append((new_data['SkuCode'], new_data['BOMVersion'], data[3], data[4]))
sql_text = "insert into T_BOM_PlateUtilUsedRate values (%s, %s, %s, %s)"
conn.exec_many_query(sql_text, insert_data)
# 插入新的状态
timestamps = dt.today().strftime('%Y-%m-%d %H:%M:%S')
sql_text = "insert into T_BOM_PlateUtilState values ('%s','%s','%s','%s','%s','%s','%s','%s','%s', '%s')" % (
new_data['SkuCode'], new_data['BOMVersion'], comment, url, shape_data,
bin_data, OK_STATUS, timestamps, timestamps, best_num)
conn.exec_non_query(sql_text)
def find_skucode(bon_version):
conn = Mssql()
sql_text = "SELECT BOMVersion FROM T_BOM_PlateUtilState WHERE BOMVersion='%s'" % bon_version
res = conn.exec_query(sql_text)
if len(res) > 0:
# 先删除（状态表和结果表），再更新
sql_text = "DELETE T_BOM_PlateUtilState WHERE BOMVersion = '%s'" % bon_version
conn.exec_non_query(sql_text)
sql_text = "DELETE T_BOM_PlateUtilUsedRate WHERE BOMVersion = '%s'" % bon_version
conn.exec_non_query(sql_text)
def package_data_check(input_data):
# 数据库连接-检查数据库
try:
parm = {
'comment': input_data['project_comment'],
'shape_data': input_data['shape_data'],
'bin_data': input_data['bin_data'],
}
other = json.dumps({'border': input_data['border']})
user = input_data['userName']
except Exception as e:
update_mix_status(status=u'缺少参数')
return {'error': True, 'info': u'缺少参数'}
# 是否有重复
conn = Mssql()
sql_text = """SELECT Guid, CreateUser FROM T_BOM_PlateUtilMixedState WHERE
Comment='{comment}' and ShapeData='{shape_data}' and BinData='{bin_data}' and Paramets='{other}'""".format(
comment=parm['comment'],
shape_data=parm['shape_data'],
bin_data=parm['bin_data'],
other=other)
res = conn.exec_query(sql_text)
row_id = None
if res:
if res[0][1] == user:
# 更新-更新时间,不返回guid
update_mix_status_time(res[0][0])
else:
# 新任务
row_id = insert_mix_status(parm, user, other)
else:
# 新任务
row_id = insert_mix_status(parm, user, other)
return {'error': False, 'row_id': row_id}
def get_data(bom_version=None):
# init output connection
conn = Mssql()
if bom_version:
sql_text = "select * From T_BOM_PlateUtilState where Status='{status}' " \
"and BOMVersion='{bom_version}'".format(status=BEGIN_STATUS.encode('utf8'),
bom_version=bom_version)
else:
sql_text = "select * From T_BOM_PlateUtilState where Status='%s'" % BEGIN_STATUS.encode('utf8')
res = conn.exec_query(sql_text)
content = list()
for input_data in res:
content.append({
'row_id': input_data[0],
'SkuCode': input_data[1],
'ShapeData': input_data[5],
'BinData': input_data[6],
'Created': dt.today(),
'Product': input_data[3],
'BOMVersion': input_data[2],
})
update_running_work(content)
return content
def has_same_work(shape_data, bin_data):
conn = Mssql()
sql_text = "SELECT BOMVersion, Status, Url, BestNum FROM T_BOM_PlateUtilState WHERE ShapeData='%s' and BinData='%s'" % (
shape_data, bin_data)
return conn.exec_query(sql_text)