def processATF(excelfile, output_path, now_date):
print('PROCESSING ATF :\t', os.path.basename(excelfile))
book = openpyxl.load_workbook(excelfile)
sheet = book.active
# atf_no = str(sheet['H5'].value).strip()
# if queries.getATFNo(atf_no)[0][0] > 0:
# print("ATF ALREADY UPLOADED")
# return os.path.basename(excelfile), "ATF ALREADY UPLOADED"
start_row = 19
end_row = sheet.max_row
for cell in sheet['A']:
if cell.value is not None:
if str(cell.value).strip().lower() == 'no.':
start_row = cell.row
if cell.row > start_row:
if cell.value is None:
end_row = cell.row
break
# CHECK IF FILE CONTAINS BLANK DATA
for row in range(start_row + 1, end_row):
major_equipment = str(sheet['F' + str(row)].value).strip()
# manufacturing_atf = str(sheet['L' + str(row)].value).strip()
equipment_category_atf = str(sheet['G' + str(row)].value).strip()
equipment_type_atf = str(sheet['K' + str(row)].value).strip()
unit_category = str(sheet['I' + str(row)].value).strip()
if any([
major_equipment.isspace() or not major_equipment
or major_equipment == 'None',
unit_category.isspace() or not unit_category
or unit_category == 'None',
# manufacturing_atf.isspace() or not manufacturing_atf,
equipment_category_atf.isspace() or not equipment_category_atf
or equipment_category_atf == 'None',
equipment_type_atf.isspace() or not equipment_type_atf
or equipment_type_atf == 'None',
]):
print("\tATF REJECTED. REASON : ATF CONTAINS BLANK INFORMATION")
return os.path.basename(
excelfile), "ATF CONTAINS BLANK INFORMATION"
# task_id = queries.generateTaskID()[0][0]
# task_id_folder = output_path + "\\" + task_id
# checkDir(task_id_folder)
# createTask(sheet, now_date, task_id)
material_list = []
duplicate_serialnumbers = []
duplicate_serialnumbers_counter = 1
equipment_limit_per_task = 50
equipment_limit_per_task_counter = 0
for row in range(start_row + 1, end_row):
if equipment_limit_per_task_counter % equipment_limit_per_task == 0:
task_id = queries.generateTaskID()[0][0]
task_id_folder = output_path + "\\" + task_id
checkDir(task_id_folder)
createTask(sheet, now_date, task_id, os.path.basename(excelfile))
system = str(sheet['E' + str(row)].value)
ne_code = aliases.getSystemAlias(system.strip())
major_equipment = str(sheet['F' + str(row)].value).strip()
unit_category = str(sheet['I' + str(row)].value).strip()
manufacturing_atf = str(sheet['L' + str(row)].value).strip()
equipment_category_atf = str(sheet['G' + str(row)].value).strip()
equipment_type_atf = str(sheet['K' + str(row)].value).strip()
flag_waste_atf = 'yes' if 'cable' in equipment_type_atf.lower(
) else 'no' # PERUMPAMAAN
unit_of_measurement_atf = str(sheet['N' + str(row)].value).strip()
netgearmaterial = queries.getPN(ne_code, major_equipment,
unit_category)
manufacturing = netgearmaterial[0][
3] if netgearmaterial else manufacturing_atf
equipment_category = netgearmaterial[0][
4] if netgearmaterial else equipment_category_atf
equipment_type = netgearmaterial[0][
5] if netgearmaterial else equipment_type_atf
flag_waste = netgearmaterial[0][
7] if netgearmaterial else flag_waste_atf
unit_of_measurement = netgearmaterial[0][
8] if netgearmaterial else unit_of_measurement_atf
web_defined_status = ' '
description = ' '
mobile_defined_status = 'NOT EXIST'
remark = str(sheet['B' + str(row)].value).strip()
# site_id_fri = str(sheet['B' + str(row)].value).strip()
site_id_fri = '20WHAR01'
barcode_flag = ' '
movement_flag = 'MOVING'
req_qty_db = str(sheet['M' + str(row)].value).strip()
req_qty = str(sheet['M' + str(row)].value).strip()
# reuse_matrix = ' '
# pallet_id = ' ',
# location_id = ' ',
# condition = ' ',
# remark_condition = ' '
serial_number_atf = str(sheet['J' + str(row)].value).strip()
exception_serialnumbers_in = [
'broken', 'blank', 'none', 'terbaca', 'buram'
]
exception_serialnumbers_is = ['NA', '-']
if serial_number_atf is None:
serial_number = queries.getDummySN()[0][0]
elif any(sn in serial_number_atf.lower()
for sn in exception_serialnumbers_in):
serial_number = queries.getDummySN()[0][0]
elif any(sn == serial_number_atf.upper()
for sn in exception_serialnumbers_is):
serial_number = queries.getDummySN()[0][0]
elif any(sn == serial_number_atf.lower()
for sn in duplicate_serialnumbers):
serial_number = serial_number_atf.lower(
) + ' (%s)' % str(duplicate_serialnumbers_counter)
duplicate_serialnumbers_counter += 1
else:
serial_number = serial_number_atf
serial_number = serial_number.replace("/", "-")
# =============== FILES FOR TASK ORIGIN =================
fri_filepath = task_id_folder + "\\" + "%s_%s_%s.fri" % (
task_id, site_id_fri, serial_number)
fri_content = (major_equipment, manufacturing, equipment_category,
equipment_type, unit_category, serial_number,
web_defined_status, description, mobile_defined_status,
remark, site_id_fri, barcode_flag, movement_flag,
unit_of_measurement, flag_waste, req_qty_db, req_qty)
writeFRI(fri_filepath, *fri_content)
# kt_filepath = task_id_folder + "\\" + "%s_%s_%s.kt" % (task_id, site_id_fri, serial_number)
# writeKT(kt_filepath, site_id_fri)
#
# wpd_filepath = task_id_folder + "\\" + "%s_%s_%s.wpd" % (task_id, site_id_fri, serial_number)
# writeWPD(wpd_filepath, kt_filepath)
item_id_date = now_date.replace('_', '')
item_id = queries.generateItemID(item_id_date)[0][0]
tbl_item = (major_equipment, manufacturing, equipment_category,
equipment_type, unit_category, serial_number,
unit_of_measurement, req_qty, flag_waste, now_date,
movement_flag, mobile_defined_status, task_id, item_id)
queries.insertTblItem(*tbl_item)
# queries.insertTblItemFRI(*tbl_item)
duplicate_serialnumbers.append(serial_number_atf.lower())
# RECORD MATERIAL
data_atf_to_record = (system, major_equipment, manufacturing_atf,
equipment_category_atf, equipment_type_atf,
unit_category, serial_number_atf, flag_waste_atf,
unit_of_measurement_atf, req_qty)
kd_acs = netgearmaterial[0][0] if netgearmaterial else ''
data_netgear_to_record = (kd_acs, ne_code, major_equipment,
manufacturing, equipment_category,
equipment_type, unit_category, serial_number,
flag_waste, unit_of_measurement, req_qty,
now_date, movement_flag,
mobile_defined_status, task_id,
os.path.basename(excelfile))
material_list.append((data_atf_to_record, data_netgear_to_record))
equipment_limit_per_task_counter += 1
return material_list
def processATF(excelfile, output_path, now_date):
print('PROCESSING ATF :\t', os.path.basename(excelfile))
book = openpyxl.load_workbook(excelfile)
sheet = book.active
atf_no = str(sheet['H5'].value).strip()
# if queries.getATFNo(atf_no)[0][0] > 0:
# print("ATF ALREADY UPLOADED")
# return os.path.basename(excelfile), "ATF ALREADY UPLOADED"
start_row = 19
end_row = sheet.max_row
for cell in sheet['A']:
if cell.value is not None:
if str(cell.value).strip().lower() == 'no.':
start_row = cell.row
if cell.row > start_row:
if cell.value is None:
end_row = cell.row
break
task_id = queries.generateTaskID()[0][0]
task_id_folder = output_path + "\\" + task_id
checkDir(task_id_folder)
material_list = []
duplicate_serialnumbers = []
duplicate_serialnumbers_counter = 1
# CHECK IF FILE CONTAINS BLANK DATA
for row in range(start_row + 1, end_row):
major_equipment = str(sheet['F' + str(row)].value).strip()
# manufacturing_atf = str(sheet['L' + str(row)].value).strip()
equipment_category_atf = str(sheet['G' + str(row)].value).strip()
equipment_type_atf = str(sheet['K' + str(row)].value).strip()
unit_category = str(sheet['I' + str(row)].value).strip()
if any([
major_equipment.isspace() or not major_equipment,
unit_category.isspace() or not unit_category,
# manufacturing_atf.isspace() or not manufacturing_atf,
equipment_category_atf.isspace() or not equipment_category_atf,
equipment_type_atf.isspace() or not equipment_type_atf
]):
print("\tATF REJECTED. REASON : ATF CONTAINS BLANK INFORMATION")
return os.path.basename(
excelfile), "ATF CONTAINS BLANK INFORMATION"
for row in range(start_row + 1, end_row):
system = str(sheet['E' + str(row)].value)
ne_code = aliases.getSystemAlias(system.strip())
major_equipment = str(sheet['F' + str(row)].value).strip()
unit_category = str(sheet['I' + str(row)].value).strip()
manufacturing_atf = str(sheet['L' + str(row)].value).strip()
equipment_category_atf = str(sheet['G' + str(row)].value).strip()
equipment_type_atf = str(sheet['K' + str(row)].value).strip()
flag_waste_atf = 'yes' if 'cable' in equipment_type_atf.lower(
) else 'no' # PERUMPAMAAN
unit_of_measurement_atf = str(sheet['N' + str(row)].value).strip()
netgearmaterial = queries.getPN(ne_code, major_equipment,
unit_category)
manufacturing = netgearmaterial[0][
3] if netgearmaterial else manufacturing_atf
equipment_category = netgearmaterial[0][
4] if netgearmaterial else equipment_category_atf
equipment_type = netgearmaterial[0][
5] if netgearmaterial else equipment_type_atf
flag_waste = netgearmaterial[0][
7] if netgearmaterial else flag_waste_atf
unit_of_measurement = netgearmaterial[0][
8] if netgearmaterial else unit_of_measurement_atf
web_defined_status = ' '
description = ' '
mobile_defined_status = 'NOT EXIST'
remark = ' '
# site_id_fri = str(sheet['B' + str(row)].value).strip()
site_id_fri = '14WNOK01'
barcode_flag = ' '
movement_flag = 'MOVING'
req_qty_db = str(sheet['M' + str(row)].value).strip()
req_qty = str(sheet['M' + str(row)].value).strip()
reuse_matrix = ' '
pallet_id = ' ',
location_id = ' ',
condition = ' ',
remark_condition = ' '
serial_number_atf = str(sheet['J' + str(row)].value).strip()
exception_serialnumbers_in = [
'broken', 'blank', 'none', 'terbaca', 'buram'
]
exception_serialnumbers_is = ['NA', '-']
if serial_number_atf is None:
serial_number = queries.getDummySN()[0][0]
elif any(sn in serial_number_atf.lower()
for sn in exception_serialnumbers_in):
serial_number = queries.getDummySN()[0][0]
elif any(sn == serial_number_atf.upper()
for sn in exception_serialnumbers_is):
serial_number = queries.getDummySN()[0][0]
elif any(sn == serial_number_atf.lower()
for sn in duplicate_serialnumbers):
serial_number = serial_number_atf.lower(
) + ' (%s)' % str(duplicate_serialnumbers_counter)
duplicate_serialnumbers_counter += 1
else:
serial_number = serial_number_atf
serial_number = serial_number.replace("/", "-")
# =============== FILES FOR TASK ORIGIN =================
fri_filepath = task_id_folder + "\\" + "%s_%s_%s.fri" % (
task_id, site_id_fri, serial_number)
fri_content = (major_equipment, manufacturing, equipment_category,
equipment_type, unit_category, serial_number,
web_defined_status, description, mobile_defined_status,
remark, site_id_fri, barcode_flag, movement_flag,
unit_of_measurement, flag_waste, req_qty_db, req_qty)
writeFRI(fri_filepath, *fri_content)
# kt_filepath = task_id_folder + "\\" + "%s_%s_%s.kt" % (task_id, site_id_fri, serial_number)
# writeKT(kt_filepath, site_id_fri)
#
# wpd_filepath = task_id_folder + "\\" + "%s_%s_%s.wpd" % (task_id, site_id_fri, serial_number)
# writeWPD(wpd_filepath, kt_filepath)
item_id_date = now_date.replace('_', '')
item_id = queries.generateItemID(item_id_date)[0][0]
tbl_item = (major_equipment, manufacturing, equipment_category,
equipment_type, unit_category, serial_number,
unit_of_measurement, req_qty, flag_waste, now_date,
movement_flag, mobile_defined_status, task_id, item_id)
queries.insertTblItem(*tbl_item)
queries.insertTblItemFRI(*tbl_item)
duplicate_serialnumbers.append(serial_number_atf.lower())
# RECORD MATERIAL
data_atf_to_record = (system, major_equipment, manufacturing_atf,
equipment_category_atf, equipment_type_atf,
unit_category, serial_number_atf, flag_waste_atf,
unit_of_measurement_atf, req_qty)
kd_acs = netgearmaterial[0][0] if netgearmaterial else ''
data_netgear_to_record = (kd_acs, ne_code, major_equipment,
manufacturing, equipment_category,
equipment_type, unit_category, serial_number,
flag_waste, unit_of_measurement, req_qty,
now_date, movement_flag,
mobile_defined_status, task_id,
os.path.basename(excelfile))
material_list.append((data_atf_to_record, data_netgear_to_record))
# ============== FILES FOR TASK DESTINATION ================
# task_id_destination = task_id + 'd'
# task_id_destination_folder = output_path + "\\" + task_id_destination
# checkDir(task_id_destination_folder)
#
# copy_tree(task_id_folder, task_id_destination_folder)
#
# files = os.listdir(task_id_destination_folder)
# for index, file in enumerate(files):
# frm = os.path.join(task_id_destination_folder, file)
# to = os.path.join(task_id_destination_folder, file.replace(task_id, task_id + 'd'))
# os.rename(frm, to)
project_id = str(sheet['E1'].value).strip()
# site_id = str(sheet['E15'].value).strip()
site_id = site_id_fri
remark = str(sheet['H5'].value).strip()
task_type = 'Movement+Collection'
last_update = now_date
# vendor_atf = str(sheet['E18'].value).strip()
vendor_atf = 'Nokia'
vendor = vendor_atf
# vendor = aliases.getVendorAlias(vendor_atf)
atf_no = str(sheet['H5'].value).strip()
# division = queries.getDivision(vendor)[0][0] if 'mpfs' not in project_id.lower() else queries.getDivision('mpfs' + vendor)[0][0]
division = 'RAN Java & Bali Nusa'
# plan_date = str(sheet['H7'].value).strip()
plan_date = '2099-12-31'
site_id_destination_excel_attempt = ['I15', 'J15']
for site_id_destination_excel in site_id_destination_excel_attempt:
try:
site_id_destination = aliases.getWHIDAlias(
str(sheet[site_id_destination_excel].value).strip())
except KeyError:
continue
else:
break
else:
raise KeyError
# noinspection PyUnboundLocalVariable
ne_id = queries.getNEID(site_id, ne_code)[0][0]
status_origin = 'Need Approval0'
task_origin_data = (task_id, project_id, site_id, plan_date, status_origin,
remark, task_type, last_update, vendor, ne_id, atf_no,
division, site_id_destination)
queries.insertTblTask(*task_origin_data)
# ne_id_destination = queries.getNEID(site_id_destination, ne_code)[0][0]
# status_destination = 'Need Approval1'
# task_destination_data = (
# task_id_destination, project_id, site_id_destination, plan_date, status_destination,
# remark, task_type, last_update, vendor, ne_id_destination,
# atf_no, division, site_id_destination, site_id, task_id
# )
# queries.insertTblTaskDestination(*task_destination_data)
return material_list
def processATF(excelfile, output_path, now_date):
print('PROCESSING DISPOSAL :\t', os.path.basename(excelfile))
book = openpyxl.load_workbook(excelfile)
sheet = book['List to Inbound to DHL Cikarang']
start_row = 1
end_row = sheet.max_row
material_list = []
duplicate_serialnumbers = []
duplicate_serialnumbers_counter = 1
rejected_tasks = []
for row in range(start_row + 1, end_row + 1):
task_id = queries.generateTaskIDDisposal()[0][0]
serial_number_xls = str(sheet['H' + str(row)].value).strip()
print("\tPROCESSING (%s/%s): SN %s of task %s" %
(row - 1, end_row + 1, serial_number_xls, task_id))
ne_code = str(sheet['C' + str(row)].value).strip()
major_equipment_xls = '-'
unit_category_xls = str(sheet['D' + str(row)].value).strip()
netgearmaterial = queries.getPN(ne_code, major_equipment_xls,
unit_category_xls)
major_equipment = netgearmaterial[0][2]
manufacturing = netgearmaterial[0][3]
equipment_category = netgearmaterial[0][4]
equipment_type = netgearmaterial[0][5]
unit_category = unit_category_xls
flag_waste = netgearmaterial[0][7]
unit_of_measurement = netgearmaterial[0][8]
task_id_folder = output_path + "\\" + task_id
checkDir(task_id_folder)
site_id = str(sheet['B' + str(row)].value).strip()
web_defined_status = ' '
description = ' '
mobile_defined_status = 'NOT EXIST'
remark = ' '
barcode_flag = ' '
movement_flag = 'MOVING'
req_qty_db = str(sheet['K' + str(row)].value).strip()
req_qty = str(sheet['K' + str(row)].value).strip()
# reuse_matrix = ' '
# pallet_id = ' ',
# location_id = ' ',
# condition = ' ',
# remark_condition = ' '
exception_serialnumbers_in = [
'broken', 'blank', 'none', 'terbaca', 'buram', 'Broken'
]
exception_serialnumbers_is = ['NA', '-', 'NO SN']
if serial_number_xls is None:
serial_number = queries.getDummySN()[0][0]
elif any(sn in serial_number_xls.lower()
for sn in exception_serialnumbers_in):
serial_number = queries.getDummySN()[0][0]
elif any(sn == serial_number_xls.upper()
for sn in exception_serialnumbers_is):
serial_number = queries.getDummySN()[0][0]
elif any(sn == serial_number_xls.lower()
for sn in duplicate_serialnumbers):
serial_number = serial_number_xls.lower(
) + ' (%s)' % str(duplicate_serialnumbers_counter)
duplicate_serialnumbers_counter += 1
else:
serial_number = serial_number_xls
# =============== FILES FOR TASK ORIGIN =================
fri_filepath = task_id_folder + "\\" + "%s_%s_%s.fri" % (
task_id, site_id, serial_number)
fri_content = (major_equipment, manufacturing, equipment_category,
equipment_type, unit_category_xls, serial_number,
web_defined_status, description, mobile_defined_status,
remark, site_id, barcode_flag, movement_flag,
unit_of_measurement, flag_waste, req_qty_db, req_qty)
writeFRI(fri_filepath, *fri_content)
# kt_filepath = task_id_folder + "\\" + "%s_%s_%s.kt" % (task_id, site_id_fri, serial_number)
# writeKT(kt_filepath, site_id_fri)
#
# wpd_filepath = task_id_folder + "\\" + "%s_%s_%s.wpd" % (task_id, site_id_fri, serial_number)
# writeWPD(wpd_filepath, kt_filepath)
item_id_date = now_date.replace('_', '')
item_id = queries.generateItemID(item_id_date)[0][0]
tbl_item = (major_equipment, manufacturing, equipment_category,
equipment_type, unit_category, serial_number,
unit_of_measurement, req_qty, flag_waste, now_date,
movement_flag, mobile_defined_status, task_id, item_id)
queries.insertTblItem(*tbl_item)
queries.insertTblItemFRI(*tbl_item)
duplicate_serialnumbers.append(serial_number_xls.lower())
# RECORD MATERIAL
system = '-'
manufacturing_xls = str(sheet['E' + str(row)].value).strip()
equipment_category_xls = str(sheet['F' + str(row)].value).strip()
equipment_type_xls = str(sheet['G' + str(row)].value).strip()
flag_waste_xls = '-'
unit_of_measurement_xls = str(sheet['L' + str(row)].value).strip()
data_xls_to_record = (system, major_equipment, manufacturing_xls,
equipment_category_xls, equipment_type_xls,
unit_category, serial_number_xls, flag_waste_xls,
unit_of_measurement_xls, req_qty)
kd_acs = netgearmaterial[0][0] if netgearmaterial else ''
site_id_destination = '03WDHL02'
data_netgear_to_record = (kd_acs, ne_code, major_equipment,
manufacturing, equipment_category,
equipment_type, unit_category, serial_number,
flag_waste, unit_of_measurement, req_qty,
now_date, movement_flag,
mobile_defined_status, task_id,
os.path.basename(excelfile), site_id,
site_id_destination)
if (site_id != str(sheet['B' + str(row + 1)].value).strip()
or ne_code != str(sheet['C' + str(row + 1)].value).strip()):
project_id = "ZTE Movement (Ex-MPFS)"
plan_date = '2099-12-31'
status_origin = 'Need Approval2'
task_type = 'Movement+Collection'
remark = "Disposal ZTE Movement (Ex-MPFS) Backend Upload"
division = aliases.getDivisionAlias(queries.getSite(site_id)[0][1])
last_update = now_date
vendor = 'PT.ZTE Indonesia'
atf_no = '-'
ne_id = queries.getNEID(site_id, ne_code)[0][0]
task_origin_data = (task_id, project_id, site_id, plan_date,
status_origin, remark, task_type, last_update,
vendor, ne_id, atf_no, division,
site_id_destination)
queries.insertTblTask(*task_origin_data)
material_list.append((data_xls_to_record, data_netgear_to_record))
return material_list, rejected_tasks
def processATF(excelfile, output_path, now_date):
print('PROCESSING ATF :\t', os.path.basename(excelfile))
book = openpyxl.load_workbook(excelfile)
sheet = book.active
start_row = 1
end_row = sheet.max_row
# CHECK IF FILE CONTAINS BLANK DATA
# for row in range(start_row + 1, end_row):
# major_equipment = str(sheet['H' + str(row)].value).strip()
# manufacturing_atf = str(sheet['I' + str(row)].value).strip()
# equipment_category_atf = str(sheet['J' + str(row)].value).strip()
# equipment_type_atf = str(sheet['K' + str(row)].value).strip()
# unit_category = str(sheet['L' + str(row)].value).strip()
# if any([
# major_equipment.isspace() or not major_equipment,
# manufacturing_atf.isspace() or not manufacturing_atf,
# equipment_category_atf.isspace() or not equipment_category_atf,
# equipment_type_atf.isspace() or not equipment_type_atf,
# unit_category.isspace() or not unit_category
# ]):
# print("\tATF REJECTED. REASON : ATF CONTAINS BLANK INFORMATION")
# return os.path.basename(excelfile), "ATF CONTAINS BLANK INFORMATION"
material_list = []
duplicate_serialnumbers = []
duplicate_serialnumbers_counter = 1
task_id_destination_list = []
rejected_tasks = []
for row in range(start_row + 1, end_row + 1):
task_id_destination = str(sheet['A' + str(row)].value).strip()
major_equipment = str(sheet['H' + str(row)].value).strip()
manufacturing_atf = str(sheet['I' + str(row)].value).strip()
equipment_category_atf = str(sheet['J' + str(row)].value).strip()
equipment_type_atf = str(sheet['K' + str(row)].value).strip()
unit_category = str(sheet['L' + str(row)].value).strip()
if any([
major_equipment.isspace() or not major_equipment,
manufacturing_atf.isspace() or not manufacturing_atf,
equipment_category_atf.isspace() or not equipment_category_atf,
equipment_type_atf.isspace() or not equipment_type_atf,
unit_category.isspace() or not unit_category
]):
print("\tATF REJECTED. REASON : ATF CONTAINS BLANK INFORMATION")
rejected_tasks.append((task_id_destination,
"DATA ON EXCEL CONTAINS BLANK INFORMATION"))
continue
serial_number_atf = str(sheet['G' + str(row)].value).strip()
print("\tPROCESSING (%s/%s): SN %s of task %s" %
(row - 1, end_row + 1, serial_number_atf, task_id_destination))
task_id_origin = queries.getOriginTaskData(task_id_destination)
if not task_id_origin:
rejected_tasks.append(
(task_id_destination, "TASK ID DESTINATION DOES NOT EXIST"))
print("\tTASK ID DESTINATION DOES NOT EXIST")
continue
task_id_excess = task_id_origin[0][0] + '_exc'
# if queries.getTaskData(task_id_excess):
# print("TASK ALREADY CREATED")
# return os.path.basename(excelfile), "TASK ALREADY CREATED"
exc_counter = 2
while queries.getTaskData(task_id_excess):
task_id_excess = task_id_excess + str(exc_counter)
exc_counter += 1
task_id_folder = output_path + "\\" + task_id_excess
checkDir(task_id_folder)
ne_id = queries.getOriginTaskData(task_id_destination)[0][2]
ne_code = ne_id[-5:-2]
major_equipment = str(sheet['H' + str(row)].value).strip()
unit_category = str(sheet['L' + str(row)].value).strip()
manufacturing_atf = str(sheet['I' + str(row)].value).strip()
equipment_category_atf = str(sheet['J' + str(row)].value).strip()
equipment_type_atf = str(sheet['K' + str(row)].value).strip()
flag_waste_atf = str(sheet['M' + str(row)].value).strip()
unit_of_measurement_atf = str(sheet['O' + str(row)].value).strip()
netgearmaterial = queries.getPN(ne_code, major_equipment,
unit_category)
manufacturing = netgearmaterial[0][
3] if netgearmaterial else manufacturing_atf
equipment_category = netgearmaterial[0][
4] if netgearmaterial else equipment_category_atf
equipment_type = netgearmaterial[0][
5] if netgearmaterial else equipment_type_atf
flag_waste = netgearmaterial[0][
7] if netgearmaterial else flag_waste_atf
unit_of_measurement = netgearmaterial[0][
8] if netgearmaterial else unit_of_measurement_atf
web_defined_status = ' '
description = ' '
mobile_defined_status = 'NOT EXIST'
remark = ' '
site_id_fri = queries.getOriginTaskData(task_id_destination)[0][1]
barcode_flag = ' '
movement_flag = 'MOVING'
req_qty_db = str(sheet['N' + str(row)].value).strip()
req_qty = str(sheet['N' + str(row)].value).strip()
# reuse_matrix = ' '
# pallet_id = ' ',
# location_id = ' ',
# condition = ' ',
# remark_condition = ' '
exception_serialnumbers_in = ['broken', 'blank', 'none']
exception_serialnumbers_is = ['NA', '-']
if serial_number_atf is None:
serial_number = queries.getDummySN()[0][0]
elif any(sn in serial_number_atf.lower()
for sn in exception_serialnumbers_in):
serial_number = queries.getDummySN()[0][0]
elif any(sn == serial_number_atf.upper()
for sn in exception_serialnumbers_is):
serial_number = queries.getDummySN()[0][0]
elif any(sn == serial_number_atf.lower()
for sn in duplicate_serialnumbers):
serial_number = serial_number_atf.lower(
) + ' (%s)' % str(duplicate_serialnumbers_counter)
duplicate_serialnumbers_counter += 1
else:
serial_number = serial_number_atf
# =============== FILES FOR TASK ORIGIN =================
fri_filepath = task_id_folder + "\\" + "%s_%s_%s.fri" % (
task_id_excess, site_id_fri, serial_number)
fri_content = (major_equipment, manufacturing, equipment_category,
equipment_type, unit_category, serial_number,
web_defined_status, description, mobile_defined_status,
remark, site_id_fri, barcode_flag, movement_flag,
unit_of_measurement, flag_waste, req_qty_db, req_qty)
writeFRI(fri_filepath, *fri_content)
# kt_filepath = task_id_folder + "\\" + "%s_%s_%s.kt" % (task_id, site_id_fri, serial_number)
# writeKT(kt_filepath, site_id_fri)
#
# wpd_filepath = task_id_folder + "\\" + "%s_%s_%s.wpd" % (task_id, site_id_fri, serial_number)
# writeWPD(wpd_filepath, kt_filepath)
item_id_date = now_date.replace('_', '')
item_id = queries.generateItemID(item_id_date)[0][0]
tbl_item = (major_equipment, manufacturing_atf, equipment_category_atf,
equipment_type_atf, unit_category, serial_number,
unit_of_measurement_atf, req_qty, flag_waste_atf, now_date,
movement_flag, mobile_defined_status, task_id_excess,
item_id)
queries.insertTblItem(*tbl_item)
duplicate_serialnumbers.append(serial_number_atf.lower())
# RECORD MATERIAL
data_atf_to_record = (ne_code, major_equipment, manufacturing_atf,
equipment_category_atf, equipment_type_atf,
unit_category, serial_number_atf, flag_waste_atf,
unit_of_measurement_atf, req_qty)
kd_acs = netgearmaterial[0][0] if netgearmaterial else ''
data_netgear_to_record = (kd_acs, ne_code, major_equipment,
manufacturing, equipment_category,
equipment_type, unit_category, serial_number,
flag_waste, unit_of_measurement, req_qty,
now_date, movement_flag,
mobile_defined_status, task_id_excess,
os.path.basename(excelfile))
task_id_destination_list.append(task_id_destination)
material_list.append((data_atf_to_record, data_netgear_to_record))
# ============== FILES FOR TASK DESTINATION ================
# task_id_destination = task_id + 'd'
# task_id_destination_folder = output_path + "\\" + task_id_destination
# checkDir(task_id_destination_folder)
#
# copy_tree(task_id_folder, task_id_destination_folder)
#
# files = os.listdir(task_id_destination_folder)
# for index, file in enumerate(files):
# frm = os.path.join(task_id_destination_folder, file)
# to = os.path.join(task_id_destination_folder, file.replace(task_id, task_id + 'd'))
# os.rename(frm, to)
task_id_destination_list = list(dict.fromkeys(task_id_destination_list))
for tid in task_id_destination_list:
tio = queries.getOriginTaskData(tid)[0][0]
tie = tio + '_exc'
exc_counter = 2
while queries.getTaskData(tie):
tie = tie + str(exc_counter)
exc_counter += 1
print('\tCreating Task\t%s' % tie)
site_id_origin = queries.getOriginTaskData(tid)[0][1]
remark = 'Excess of task %s' % tio
task_type = 'Movement+Collection'
last_update = now_date
vendor = queries.getOriginTaskData(tid)[0][6]
atf_no = ' '
project_id = ' '
creator_id = queries.getOriginTaskData(tid)[0][5]
division = queries.getOriginTaskData(tid)[0][3]
# plan_date = str(sheet['H7'].value).strip()
plan_date = '2099-12-31'
site_id_destination = queries.getOriginTaskData(tid)[0][4]
# noinspection PyUnboundLocalVariable
ne_id = queries.getOriginTaskData(task_id_destination)[0][2]
status_origin = 'Need Approval0'
task_origin_data = (tie, project_id, site_id_origin, plan_date,
status_origin, remark, task_type, last_update,
vendor, ne_id, atf_no, division,
site_id_destination, creator_id)
queries.insertTblTask(*task_origin_data)
# ne_id_destination = queries.getNEID(site_id_destination, ne_code)[0][0]
# status_destination = 'Need Approval1'
# task_destination_data = (
# task_id_destination, project_id, site_id_destination, plan_date, status_destination,
# remark, task_type, last_update, vendor, ne_id_destination,
# atf_no, division, site_id_destination, site_id, task_id
# )
# queries.insertTblTaskDestination(*task_destination_data)
return material_list, rejected_tasks