Exemplo n.º 1
0
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
Exemplo n.º 2
0
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
Exemplo n.º 3
0
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