def insert_batch_order_relation(dbconfig, material, material_description, movement_type, batch, order_num, posting_date, quantity, unit):
db = MySQL(dbconfig)
sql = "INSERT INTO tasly_warehouse.batch_order_relation (material, material_description, movement_type, batch, order_num, posting_date, quantity, unit) " \
"VALUES ('{material}','{material_description}','{movement_type}','{batch}','{order_num}',{posting_date},{quantity},'{unit}');".\
format(material=material, material_description=material_description, movement_type=movement_type, batch=batch, order_num=order_num, posting_date=posting_date, quantity=quantity, unit=unit)
print(sql)
db.insert(sql)
db.close()
def insert_unit_info(dbconfig, material, material_description, unit):
db = MySQL(dbconfig)
sql = "INSERT INTO tasly_warehouse.unit_info (material, material_description, unit) " \
"VALUES ('{material}','{material_description}','{unit}');".\
format(material=material, material_description=material_description, unit=unit)
print(sql)
db.insert(sql)
db.close()
def insert_tasly_warehouse_storage_info(dbconfig, material, storage_bin, batch,
material_desc, avail_stock, unit,
last_goods_rec, date_of_manuf,
sled_bbd, next_inspection, status):
db = MySQL(dbconfig)
sql = "INSERT INTO tasly_warehouse.tasly_warehouse_storage_info (material,storage_bin, batch, material_desc, avail_stock, unit, last_goods_rec, date_of_manuf, sled_bbd, next_inspection, status) " \
"VALUES ('{material}','{storage_bin}','{batch}','{material_desc}',{avail_stock},'{unit}',{last_goods_rec},{date_of_manuf},{sled_bbd},{next_inspection},'{status}');".\
format(material=material, storage_bin=storage_bin, batch=batch, material_desc=material_desc, avail_stock=avail_stock,unit=unit, last_goods_rec=last_goods_rec, date_of_manuf=date_of_manuf, sled_bbd=sled_bbd, next_inspection=next_inspection, status=status)
#print (sql)
db.insert(sql)
db.close()
def insert_batch_order_relation(
dbconfig, supply_type, material, material_description, unit, supplier,
sante_material_requirements_mon, sante_material_requirements,
supplier_inventory, supplier_production_quantity,
estimate_completion_time):
db = MySQL(dbconfig)
sql = "INSERT INTO tasly_warehouse.supplier_stock_management (supply_type,material,material_description,unit,supplier,sante_material_requirements_mon,sante_material_requirements,supplier_inventory,supplier_production_quantity,estimate_completion_time) " \
"VALUES ('{supply_type}','{material}','{material_description}','{unit}','{supplier}','{sante_material_requirements_mon}','{sante_material_requirements}','{supplier_inventory}','{supplier_production_quantity}',{estimate_completion_time});".\
format(supply_type=supply_type, material=material, material_description=material_description, unit=unit, supplier=supplier, sante_material_requirements_mon=sante_material_requirements_mon,sante_material_requirements=sante_material_requirements, supplier_inventory=supplier_inventory,
supplier_production_quantity=supplier_production_quantity, estimate_completion_time=estimate_completion_time)
print(sql)
db.insert(sql)
db.close()
def flush_batch_order_relation_pid(dbconfig):
db = MySQL(dbconfig)
sql = "update tasly_warehouse.batch_order_relation set pid = '';"
db.insert(sql)
db.close()
def insert_batch_order_relation_relationbatches(dbconfig):
try:
db = MySQL(dbconfig)
#sql = "select material,material_description,max(id),max(order_num) from batch_order_relation where movement_type in (101) and order_num != '' and batch = '000203007T' group by material,material_description;"
sql = "select material,material_description,batch,max(id),max(order_num) from batch_order_relation where movement_type in (101) and order_num != '' group by material,material_description,batch;"
db.query(sql)
order_num_id_info = db.fetchAllRows()
db.close()
for material,material_description,batch,batch_order_relation_id, order_num in order_num_id_info:
#print(batch_order_relation_id, order_num)
db_component_list = MySQL(dbconfig)
sql_get_batches = "select batch,max(id) from batch_order_relation where order_num = '{order_num}' and movement_type in (261) group by batch ;".format(order_num=order_num)
db_component_list.query(sql_get_batches)
relationbatches_info = db_component_list.fetchAllRows()
db_component_list.close()
if relationbatches_info != ():
#print(batches_info)
relationbatches_list = []
for relationbatches in relationbatches_info:
relationbatches_list.append(str(relationbatches[1]))
relationbatches_result = ','.join(relationbatches_list)
db_batch_order_relation = MySQL(dbconfig)
sql_update_batches = "update batch_order_relation set relation_batches = '{relationbatches_result}' where id = {batch_order_relation_id};".\
format(relationbatches_result=relationbatches_result, batch_order_relation_id=batch_order_relation_id)
#print(sql_update_batches)
db_batch_order_relation.update(sql_update_batches)
db_batch_order_relation.close()
except Exception as e:
print(e)
def insert_batch_order_relation_pid(dbconfig):
try:
db = MySQL(dbconfig)
sql = "select id from batch_order_relation where order_num != '' and batch != '' and movement_type in (261);"
db.query(sql)
component_list_id_info = db.fetchAllRows()
db.close()
for component_list_id in component_list_id_info:
#print(component_list_id[0])
db_component_list = MySQL(dbconfig)
sql_get_batch_101 = "select batch from batch_order_relation where (relation_batches like '%,{component_list_id},%' or relation_batches like '{component_list_id},%' or relation_batches like '%,{component_list_id}') and batch != '' and movement_type in (101);".format(component_list_id=component_list_id[0])
db_component_list.query(sql_get_batch_101)
component_list_batch_info_101 = db_component_list.fetchAllRows()
db_component_list.close()
if component_list_batch_info_101 != ():
#print('XXXXX')
#print(component_list_pid_info)
#print('XXXXX')
batches_list = []
for component_list_batch_101 in component_list_batch_info_101:
db_component_list = MySQL(dbconfig)
sql_get_batch_261 = "select id from batch_order_relation where batch = '{component_list_batch_101}' and movement_type in (261) order by posting_date desc;".format(
component_list_batch_101=component_list_batch_101[0])
db_component_list.query(sql_get_batch_261)
component_list_batch_id_info_261 = db_component_list.fetchOneRow()
#print('XXXXXX')
#print(component_list_batch_id_info_261)
#print('XXXXXXX')
db_component_list.close()
if component_list_batch_id_info_261 is not None:
for component_list_batch_id_261 in component_list_batch_id_info_261:
print('component_list_batch_id_info_261')
print(component_list_batch_id_info_261)
print('component_list_batch_id_info_261')
batches_list.append(str(component_list_batch_id_261))
component_list_pid_result = ','.join(batches_list)
#print('YYYYY')
#print(component_list_pid_result)
#print('YYYYY')
db_component_list = MySQL(dbconfig)
sql_update_batches = "update batch_order_relation set pid = '{component_list_pid_result}' where id = {component_list_id};". \
format(component_list_pid_result=component_list_pid_result, component_list_id=component_list_id[0])
#print(sql_update_batches)
db_component_list.update(sql_update_batches)
db_component_list.close()
except Exception as e:
print(e)
def flush_batch_order_relation(dbconfig):
db = MySQL(dbconfig)
sql = "truncate table tasly_warehouse.supplier_stock_management"
db.insert(sql)
db.close()
def flush_batch_order_relation(dbconfig):
db = MySQL(dbconfig)
sql = "truncate table tasly_warehouse.batch_order_relation"
db.insert(sql)
db.close()
def flush_unit_info(dbconfig):
db = MySQL(dbconfig)
sql = "truncate table tasly_warehouse.unit_info"
db.insert(sql)
db.close()
def insert_component_list_batches(dbconfig):
try:
db = MySQL(dbconfig)
sql = "select id,batch from component_list where order_num != '' and batch != '';"
db.query(sql)
batch_id_info = db.fetchAllRows()
db.close()
for component_list_id, batch in batch_id_info:
#print(component_list_id, batch)
db_batch_order_relation = MySQL(dbconfig)
sql_get_batches = "select batches from batch_order_relation where batch = '{batch}' and movement_type in (101) and order_num != '' and batches != '' order by posting_date limit 1;".format(
batch=batch)
#print(sql_get_batches)
db_batch_order_relation.query(sql_get_batches)
batches_info = db_batch_order_relation.fetchAllRows()
#print(batches_info)
db_batch_order_relation.close()
if batches_info != ():
#print(batches_info[0])
batches = batches_info[0][0]
db_component_list = MySQL(dbconfig)
sql_update_batches = "update component_list set batches = '{batches}' where id = {component_list_id};". \
format(batches=batches, component_list_id=component_list_id)
#print(sql_update_batches)
db_component_list.update(sql_update_batches)
db_component_list.close()
except Exception as e:
print(e)
def flush_component_list_batches(dbconfig):
db = MySQL(dbconfig)
sql = "update tasly_warehouse.component_list set batches = '';"
db.insert(sql)
db.close()
def insert_batch_order_relation_batches(dbconfig):
try:
db = MySQL(dbconfig)
sql = "select id,order_num from batch_order_relation where movement_type in (101) and order_num != '';"
db.query(sql)
order_num_id_info = db.fetchAllRows()
db.close()
for batch_order_relation_id, order_num in order_num_id_info:
#print(batch_order_relation_id, order_num)
db_component_list = MySQL(dbconfig)
sql_get_batches = "select id from component_list where order_num = '{order_num}' and batch != '';".format(
order_num=order_num)
db_component_list.query(sql_get_batches)
batches_info = db_component_list.fetchAllRows()
db_component_list.close()
if batches_info != ():
#print(batches_info)
batches_list = []
for batches in batches_info:
batches_list.append(str(batches[0]))
batches_result = ','.join(batches_list)
db_batch_order_relation = MySQL(dbconfig)
sql_update_batches = "update batch_order_relation set batches = '{batches_result}' where id = {batch_order_relation_id};".\
format(batches_result=batches_result,batch_order_relation_id=batch_order_relation_id)
#print(sql_update_batches)
db_batch_order_relation.update(sql_update_batches)
db_batch_order_relation.close()
except Exception as e:
print(e)
def insert_component_list_pid(dbconfig):
try:
db = MySQL(dbconfig)
sql = "select id from component_list where order_num != '' and batch != '';"
db.query(sql)
component_list_id_info = db.fetchAllRows()
db.close()
for component_list_id in component_list_id_info:
#print(component_list_id[0])
db_component_list = MySQL(dbconfig)
sql_get_batches = "select id from component_list where batches like '%{component_list_id}%' and batch != '';".format(
component_list_id=component_list_id[0])
db_component_list.query(sql_get_batches)
component_list_pid_info = db_component_list.fetchAllRows()
db_component_list.close()
if component_list_pid_info != ():
#print('XXXXX')
#print(component_list_pid_info)
#print('XXXXX')
batches_list = []
for component_list_pid in component_list_pid_info:
batches_list.append(str(component_list_pid[0]))
component_list_pid_result = ','.join(batches_list)
#print('YYYYY')
#print(component_list_pid_result)
#print('YYYYY')
db_component_list = MySQL(dbconfig)
sql_update_batches = "update component_list set pid = '{component_list_pid_result}' where id = {component_list_id};". \
format(component_list_pid_result=component_list_pid_result, component_list_id=component_list_id[0])
#print(sql_update_batches)
db_component_list.update(sql_update_batches)
db_component_list.close()
except Exception as e:
print(e)
def flush_tasly_warehouse_storage_info(dbconfig):
db = MySQL(dbconfig)
sql = "truncate table tasly_warehouse.tasly_warehouse_storage_info"
db.insert(sql)
db.close()
