def createTask(sheet, now_date, task_id, filename):
print("\tCREATING TASK: %s" % task_id)
project_id = str(sheet['E1'].value).strip()
site_id = '20WHAR01'
task_type = 'Movement+Collection'
last_update = now_date
vendor_atf = 'Hariff Daya Tunggal Engineering'
remark_task = 'Back End Upload %s' % (filename)
vendor = vendor_atf
# atf_no = str(sheet['H5'].value).strip()
atf_no = '-'
division = 'RAN Java & Bali Nusa'
plan_date = '2099-12-31'
system = str(sheet['E20'].value)
ne_code = aliases.getSystemAlias(system.strip())
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 Approval2'
task_origin_data = (task_id, project_id, site_id, plan_date, status_origin,
remark_task, task_type, last_update, vendor, ne_id,
atf_no, division, site_id_destination)
queries.insertTblTask(*task_origin_data)
def createTask(sheet, now_date, task_id):
print("\tCREATING TASK: %s" % task_id)
project_id = str(sheet['E1'].value).strip()
site_id = '03ZTE010'
remark_task = 'Back End Upload Dismantle ZTE MPFS Disposal'
task_type = 'Movement+Collection'
last_update = now_date
vendor_atf = 'PT.ZTE Indonesia'
vendor = vendor_atf
atf_no = str(sheet['H5'].value).strip()
division = 'MPFS ZTE Jabo'
plan_date = '2099-12-31'
system = str(sheet['E20'].value)
ne_code = aliases.getSystemAlias(system.strip())
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 Approval2'
task_origin_data = (task_id, project_id, site_id, plan_date, status_origin,
remark_task, task_type, last_update, vendor, ne_id,
atf_no, division, site_id_destination)
queries.insertTblTask(*task_origin_data)
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