def update_new_work(data):
conn = Mssql()
for d in data:
update_sql = "update T_BOM_PlateUtilState set Product='%s',UpdateDate='%s',SkuCode='%s'where BOMVersion='%s'" % (
d['Product'], d['Update'].strftime('%Y-%m-%d %H:%M:%S'), d['SkuCode'], d['BOMVersion'])
conn.exec_non_query(update_sql.encode('utf8'))
def update_mix_status_result(guid, url):
update_time = dt.today()
conn = Mssql()
sql_text = """UPDATE T_BOM_PlateUtilMixedState SET
UpdateDate='{update_time}', Url='{url}', Status='{status}' WHERE Guid='{guid}'""".format(
guid=guid,
status=u'运算结束',
url=url,
update_time=update_time.strftime('%Y-%m-%d %H:%M:%S'))
conn.exec_non_query(sql_text)
def update_result(data):
update_middle_result(data)
conn = Mssql()
if 'rates' in data.keys():
# 先看是否存在, 存在就删除原来数据
sql_text = "delete T_BOM_PlateUtilUsedRate where BOMVersion='%s'" % data['BOMVersion']
conn.exec_non_query(sql_text)
sql_text = "insert into T_BOM_PlateUtilUsedRate values (%s, %s, %s, %s)"
conn.exec_many_query(sql_text, data['rates'])
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 update_mix_status(guid=None, status=None):
if not guid:
guid = uuid.uuid4()
if not status:
status = u'运行出错'
update_time = dt.today()
conn = Mssql()
sql_text = """UPDATE T_BOM_PlateUtilMixedState
SET UpdateDate='{update_time}', Status='{status}' WHERE Guid='{guid}'""".format(
guid=guid,
status=status,
update_time=update_time.strftime('%Y-%m-%d %H:%M:%S'))
conn.exec_non_query(sql_text)
def update_middle_result(data):
# TODO: 保存中间结果
conn = Mssql()
if data['status'] == OK_STATUS:
update_sql = "update T_BOM_PlateUtilState set Status='%s',Url='%s',UpdateDate='%s', BestNum=%d " \
"where BOMVersion='%s'" % (data['status'], data['url'],
data["Created"].strftime('%Y-%m-%d %H:%M:%S'),
data['best_num'], data['BOMVersion'])
elif data['status'] == NO_NUM_STATUS:
update_sql = "update T_BOM_PlateUtilState set Status='%s',UpdateDate='%s'where BOMVersion='%s'" % (
data['status'], data['Created'].strftime('%Y-%m-%d %H:%M:%S'), data['BOMVersion'])
else:
update_sql = "update T_BOM_PlateUtilState set Status='%s',UpdateDate='%s', BestNum=%d " \
"where BOMVersion='%s'" % (data['status'], data["Created"].strftime('%Y-%m-%d %H:%M:%S'),
data['best_num'], data['BOMVersion'])
conn.exec_non_query(update_sql.encode('utf8'))
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 insert_mix_status(paramets, user_name, other):
created = dt.today()
conn = Mssql()
row_id = uuid.uuid4()
sql_text = "insert into T_BOM_PlateUtilMixedState values ('%s','%s','%s','%s','%s','%s','%s', '%s', '%s', '%s')" % (
row_id, u'新任务', ' ', paramets['comment'], user_name,
created.strftime('%Y-%m-%d %H:%M:%S'),
created.strftime('%Y-%m-%d %H:%M:%S'), paramets['shape_data'],
paramets['bin_data'], other)
conn.exec_non_query(sql_text)
# 更新明细
comments = json.loads(paramets['comment'])
# 整理数据
insert_data = list()
for data in comments:
insert_data.append(
(row_id, data['Series'], data['SkuCode'], data['ItemName'],
data['SkuName'], data['SeriesVersion'], data['BOMVersion'],
data['Amount']))
sql_text = "insert into T_BOM_PlateUtilMixedDetail values (%s,%s,%s,%s,%s,%s,%s,%s)"
conn.exec_many_query(sql_text, insert_data)
return row_id
def update_mix_status_time(guid):
update_time = dt.today()
conn = Mssql()
sql_text = "UPDATE T_BOM_PlateUtilMixedState SET UpdateDate='{update_time}' WHERE Guid='{guid}'".format(
guid=guid, update_time=update_time.strftime('%Y-%m-%d %H:%M:%S'))
conn.exec_non_query(sql_text)
def update_running_work(data):
conn = Mssql()
for d in data:
update_sql = "update T_BOM_PlateUtilState set Status='%s',UpdateDate='%s' where BOMVersion='%s'" % (
u'运行中', d['Created'].strftime('%Y-%m-%d %H:%M:%S'), d['BOMVersion'])
conn.exec_non_query(update_sql.encode('utf8'))