コード例 #1
0
def tasksfinal(data, test=False):

    ms_data = excel_list(mapfile, 'Milestones', folder)
    ms_map = excel_list(mapfile, 'Milestone Fields', folder)
    milestonedics = tasks.ms_details_dic(ms_data)
    newmsdic = milestonedics[2]

    data = tasks.addmilestones(data, newmsdic)
    mergedic = tasks.mergemilestones(data, ms_map)
    newdata = tasks.nslist(data, ms_map, mergedic, milestonedics, test)
    # final = columnsused(newdata)
    final = newdata

    return final
コード例 #2
0
def projectSFIDdic():
    folder = 'C:\\Users\jfew\OneDrive - Allbridge\\Netsuite Projects\\'
    idlist = excel_list('Lookups for Projects.xlsx', 'External ID', folder)

    dic = {}

    for row in idlist[1:]:
        proj = row[0]
        id = row[1]
        dic[proj] = id

    return dic
コード例 #3
0
def mainoptions():
    data = excel_list(mapfile, 'Data Selection', folder)
    head = data[0]
    dic = {}
    for row in data[1:]:
        crt = row[0]
        for n in range(0, len(head)):
            cat = head[n]

            if row[n] == True:
                if cat not in list(dic.keys()):
                    dic[cat] = [crt]
                else:
                    dic[cat].append(crt)

    return dic
コード例 #4
0
def customerlookup():
    folder = 'C:\\Users\jfew\OneDrive - Allbridge\\Netsuite Projects\\'
    nsextract = excel_list('Lookups for Projects.xlsx', 'Customers', folder)
    head = nsextract[0]
    internal = head.index('Internal ID')
    custnum = head.index('SF Customer Number')
    account = head.index('Name')
    dic = {}
    numv = None
    for row in nsextract[1:]:
        id = row[internal]
        num = row[custnum]
        act = row[account]

        if num != None:
            dic[num] = id
        if act != None:
            dic[act] = id

    # if cnum != None:
    #     try:
    #         numv = dic[cnum]
    #     except KeyError:
    #         numv = None
    #
    # try:
    #     aval = dic[aname]
    # except KeyError:
    #     aval = None
    #
    # if numv != None:
    #     ans = numv
    # else:
    #     if aval != None:
    #         ans = aval
    #     else:
    #         ans = error
    #
    # if test == True:
    #     ans = 16163

    return dic
コード例 #5
0
def projectsfinal(data, test=False):
    mapdic = proj.pmapdic(excel_list(mapfile, 'Project Fields', folder))
    final = proj.ptrans(data, mapdic, test)

    return final
コード例 #6
0
    pdata = [projhead]
    tdata = [taskhead]

    pi = projhead.index('Case Record Type')
    ti = taskhead.index('Case: Case Record Type')
    for row in projfull[1:]:
        if row[pi] in select:
            pdata.append(row)

    for row in taskfull[1:]:
        if row[ti] in select:
            tdata.append(row)

    return pdata, tdata


if __name__ == '__main__':

    test = True

    projext = excel_list(extractfile, 'Projects', folder)
    taskext = excel_list(extractfile, 'Milestones', folder)
    select = optionselect()
    re = removedata(projext, taskext, select)

    project = projectsfinal(re[0], test)
    task = tasksfinal(re[1], test)

    excelcreate(project, writefile, 'Projects', folder)
    excelcreate(task, writefile, 'Milestones', folder)
コード例 #7
0
        rows.append(r)

    return rows


if __name__ == '__main__':
    folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite Integration\\Sales Orders 1.6.20\\'

    mapfile = 'Scenarios 1-7 Missing Fields.xlsx'
    writefile = 'Scenarios 1 3 and 8 - 1.7.20 Add Missing Fields.xlsx'
    sheet = 'Scenario 1&3'

    writefile = 'Scenario 7 - Transform 1.4.20 Add Missing Fields.xlsx'
    sheet = 'MASTER'

    data = excel_list(writefile, sheet, folder)
    mdic = mapdic(excel_list(mapfile, 'Map', folder))
    ext = excel_list(mapfile, 'Extract', folder)
    extdic = extractdic(ext, mdic)

    full = appenddata(data, extdic, mdic)
    print(full)

    wdic = {'Sheet1': full, 'Sheet2': [['Jackson Test']]}

    newfile = 'Scenario 7 New Fields.xlsx'

    txt = open('7.txt', 'w')
    textlist = textfile(full)
    txt.writelines(textlist)
    # txt.write('Jackson')
コード例 #8
0
    savefiles = {'Regular':'Regular Items 10.21.19.xlsx', 'Serial':'Serial Items 10.22.19.xlsx', \
                'Assembly':'Non-Serial Assembly 10.22.19.xlsx', 'Serial Assembly':'Serial Assembly 10.23.19.xlsx'}

    print('Please Select Item Type')
    for x in itemtypes:
        n = str(itemtypes.index(x)) + ': ' + x
        print(n)

    i = int(input())
    item = itemtypes[i]

    file = savefiles[item]
    print(file)

    gla = gla_dic(excel_list('GLA MOD.xlsx', 'Mod', folder))
    extid = extid_dic(excel_list('External_ID.xlsx', 'Sheet1', folder))
    units = unit_dic(
        excel_list('Units of Measure 10.22.19.xlsx', 'Summary', folder))
    bins = binloc(
        excel_list('Bins and Locations 10.22.19.xlsx', 'Summary', folder))
    ven = vendors(excel_list('Vendors 10.22.19.xlsx', 'Vendors', folder))
    itemdic = assItemdic(excel_list(assfile, 'ItemDic', folder))

    guide = excel_list(mapfile, item, folder)
    extract = excel_list(extractfile, item, folder)

    full = ns_list(guide,
                   extract,
                   gla,
                   extid,
コード例 #9
0
                name = 'Project Manager'

        row[head.index('Milestone Owner')] = name
        row[head.index('Default Resource')] = name

    return data


if __name__ == '__main__':
    folder = 'C:\\Users\jfew\OneDrive - Allbridge\\Netsuite Projects\\'
    mapping = 'Project and Milestone Mapping.xlsx'
    # extract = 'Extract 12.31.19.xlsx'
    extract = 'Extract 1.17.20.xlsx'
    writefile = 'Milestone Transform.xlsx'

    data = excel_list(extract, 'Milestones', folder)

    ms_data = excel_list(mapping, 'Milestones', folder)
    ms_map = excel_list(mapping, 'Milestone Fields', folder)
    milestonedics = ms_details_dic(ms_data)
    newmsdic = milestonedics[2]
    print(milestonedics[0])
    print(milestonedics[1]['Headend']['PTR'])

    test = False

    projlookup = projectInternalID(
        excel_list('NetSuite Projects.xlsx', 'Projects', folder), test)
    data = addmilestones(data, newmsdic, projlookup)

    mergedic = mergemilestones(data, ms_map)
コード例 #10
0
def createdic(data):
    full = []
    for row in data:
        t = row[-1]
        i = data.index(row)
        if t == 'Topic':
            topici = data.index(row)
        elif t == 'Question':
            qi = data.index(row)
            row.append(data[topici][1])
        else:
            data[qi].append(row)

    for row in data:
        try:
            if row[2] == 'Question':
                full.append(row)
        except IndexError:
            print(row, data.index(row))

    return full


if __name__ == '__main__':
    raw = excel_list('AdminTestQuestions.xlsx', 'Sheet1')

    reform = reformatlist(raw)
    data = createdic(reform)

    print(data)
コード例 #11
0
        nrow[s] = item[0]
        nrow[qty_i] = item[4]
        # nrow[pack_i] = item[5]

        if nrow[s] == None:
            try:
                nrow[s] = sodic[so][0]
            except KeyError:
                nrow[s] = 'Open'

        if nrow[s] != 'Cancelled':
            full.append(nrow)

    return full


if __name__ == '__main__':
    folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite Integration\\Sales Orders 1.6.20\\'
    file = 'Scenario 8.xlsx'

    sfdata = excel_list('Ascent SO Lines Scenario 8.xlsx', 'Ascent SO', folder)
    kvdata = excel_list(file, 'Master', folder)

    pdic = productdic(sfdata)
    cdic = casedic(sfdata)
    sodic = sostatus(sfdata)

    final = combine(kvdata, cdic, pdic, sodic)

    excelcreate(final, file, 'Combine', folder)
コード例 #12
0
def customname(data):
    id = data[0].index('Identifier')
    bom = data[0].index('BOM')

    dic = {}
    for row in data[1:]:
        dic[row[bom]] = row[id]

    return dic


if __name__ == '__main__':
    folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite Integration\\Items into Production 10.21.19'
    file = 'Assembly Components 12.23.19.xlsx'

    data = excel_list(file, 'Extract', folder)

    nsdic = nsitems(excel_list(file, 'Item IDs', folder))

    custBOM = customname(excel_list(file, 'Custom Names', folder))
    t = transform(data, custBOM)
    bomdic = t[0]
    itemdic = t[1]
    full = AssCompForm(data, nsdic, error=None, iid=True)

    bomlist = dictolist(bomdic)

    mem = 'Excel'
    bom = BOM(bomlist, memo=mem)
    rev = BOMrev(full, bomdic, memo=mem)
コード例 #13
0
    dic = {}
    for row in ext[1:]:
        op = row[head.index('Sales Order: Sales Order #')]
        item = row[head.index('Product 18 Char ID')]
        status = row[head.index('Status')]

        key = (op, item)
        dic[key] = status

    return dic


if __name__ == '__main__':
    folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite Integration\\Sales Orders 1.6.20\\'
    extfile = 'SO Line Status Extract 1.14.20.xlsx'

    sheet = 'Scenario 9'

    ext = excel_list(extfile, sheet, folder)
    sdic = statusdic(ext)
    file = 'SO Line Status Data 1.14.20.xlsx'

    data = excel_list(file, sheet, folder)

    full = statusupdate(data, sdic)

    writefile = 'SO Line Status Update 1.14.20.xlsx'

    excelcreate(full, writefile, sheet, folder)
コード例 #14
0
            n = row[0]
        elif row[1] != None:
            n = row[1]
        elif row[2] != None:
            n = row[2]
        elif row[3] != None:
            n = row[3]

        for x in row[5:]:
            if x != None and x != ':(':
                new.append([None, n, x])

    return new

if __name__ == '__main__':
    folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite\\'
    # file = 'NetSuite Permissions 12.2.19.xlsx'
    #
    #
    # extract = excel_list(file, 'Extract', folder)
    #
    # data = perm_format(extract)
    #
    # print(data)
    #
    # excelcreate(data, file, 'Extract Table', folder)
    role = 'Operations Roles.xlsx'
    roledata = excel_list(role, 'Assign Roles', folder)
    newdata = roles(roledata)
    excelcreate(newdata, 'Operations Roles 12.9.19.xlsx', 'Operations', folder)
    print(newdata)
コード例 #15
0
        full.append(nrow)

    return full


if __name__ == '__main__':
    folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite Integration\\Sales Orders 1.6.20\\'
    # file = 'Scenario 7 1.16.20 Part 1.xlsx'
    file = 'Scenario 7 1.16.20 Split.xlsx'

    multi = True
    num = 5

    if multi == True:
        comsheet = 'Sheet' + str(num)
        tsheet = 'Transform' + str(num)
    else:
        comsheet = 'Combine'
        tsheet = 'Transform'

    print('Start - ', dt.datetime.now())
    mapping = pmapdic(excel_list('SO Mapping.xlsx', 'Mapping', folder))
    print('Mapping - ', dt.datetime.now())
    data = excel_list(file, comsheet, folder)
    print('Data - ', dt.datetime.now())

    tran = transform(data, mapping)
    print('Transform - ', dt.datetime.now())

    excelcreate(tran, file, tsheet, folder)
    print('Finish - ', dt.datetime.now())
コード例 #16
0
def dictolist(dic):

    full = []
    for x in list(dic.keys()):
        row = dic[x]
        row.insert(0, x)
        full.append(row)

    return full


if __name__ == '__main__':
    folder = 'C:\\Users\\jfew\\OneDrive - Allbridge\\NetSuite\\'
    file = 'MPN Dupe Check.xlsx'

    report = excel_list(file, 'Report', folder)

    dupes = Dupes(report)

    itemdic = dupes[0]
    mpndic = dupes[1]
    nompn = dupes[2]

    mpn = dictolist(mpndic)

    sheetdic = {'MPN': mpn, 'No MPN': nompn}

    multiexcelwrite(file, sheetdic, folder)

    # excelcreate(mpn, file, 'MPN', folder)
コード例 #17
0
    data[0].append('Opp 18 ID')
    head = data[0]
    opid = head.index('Opportunity ID Ref')
    opp = head.index('Opportunity')

    full = [head]
    for row in data[1:]:
        nrow = row.copy()
        try:
            id18 = opdic[(row[opid], row[opp])]
        except KeyError:
            id18 = None

        nrow.append(id18)
        full.append(nrow)

    return full


if __name__ == '__main__':
    folder = 'C:\\Users\jfew\OneDrive - Allbridge\\Netsuite Projects\\'
    file = 'Projects SO Update.xlsx'

    data = excel_list(file, 'Case Extract', folder)

    opp = oppdic(excel_list(file, 'Opp Extract', folder))

    final = addopp(data, opp)

    excelcreate(final, file, 'Transform', folder)
コード例 #18
0
            sdic['Predecessor Sublist: Task'] = Taskdetail(
            )[old_crt][old_pretask][0]
        except KeyError:
            sdic['Predecessor Sublist: Task'] = None
        sdic['Predecessor Sublist: Type'] = None  #Not Sure
        sdic['Task Length in Days'] = task_len(tstart, tend)
        sdic['PJ Task Subtab Link'] = tname

        for r in mapping[1:]:
            field = r[0]
            i = newhead.index(field)
            include = r[mapping[0].index(tname)]
            if include == True:
                if field in special:
                    newrow[i] = sdic[field]
                else:
                    sf = r[1]
                    sf_i = datahead_dic[sf]
                    newrow[i] = row[sf_i]
        datanew.append(newrow)
    return datanew


if __name__ == '__main__':
    tmap = excel_list('Milestone Field Mapping.xlsx', 'COMM')
    data = excel_list('COMM Task Test 10.16.19.xlsx', 'Sheet1')

    newdata = dateconvert(task_transform(data, tmap))

    excelcreate(newdata, 'COMM Task Test 10.16.19.xlsx', 'Sheet2')
コード例 #19
0
            full[ri][fi] = n

    full = dependmod(full, mapdic)

    return full





if __name__ == '__main__':
    folder = 'C:\\Users\jfew\OneDrive - Allbridge\\Netsuite Projects\\'
    mapping = 'Project and Milestone Mapping.xlsx'
    extract = 'Extract 12.31.19.xlsx'
    writefile = 'Milestone Transform.xlsx'
    writesheet = 'Projects'

    test = False


    mapdic = pmapdic(excel_list(mapping, 'Project Fields', folder))
    ext = excel_list(extract, 'Projects', folder)

    final = ptrans(ext, mapdic, test)
    # print(mapdic)
    print(final)

    excelcreate(final, writefile, writesheet, folder)
    # print(customerlookup('C67375', None, 'Missing'))
コード例 #20
0
    newhead = []
    for f in head:
        if dic[f] != 0:
            newhead.append(f)

    newdata = [newhead]

    for row in full[1:]:
        newrow = [None] * len(newhead)
        for x in row:
            i = row.index(x)
            field = head[i]

            if dic[field] != 0:
                newi = newhead.index(field)
                newrow[newi] = x
        newdata.append(newrow)

    return newdata


if __name__ == '__main__':
    folder = 'C:\\Users\jfew\OneDrive - Allbridge\\Netsuite Projects\\'
    file = 'System Types.xlsx'

    data = excel_list(file, 'Sheet4', folder)

    moddata = systemtypeTEST(data)

    excelcreate(moddata, file, 'Sheet4', folder)
コード例 #21
0
    transfile = 'Adjustment Transform.xlsx'
    adjtype = ['Regular', 'Serial']
    sheetdic = {
        'Regular': ['Bin', 'NoBin'],
        'Serial': ['BinSerial', 'NoBinSerial']
    }

    print('Please Select an Item Type')
    for x in adjtype:
        msg = str(adjtype.index(x)) + ': ' + x
        print(msg)
    i = int(input())
    selec = adjtype[i]

    units = unit_dic(
        excel_list('Units of Measure 10.22.19.xlsx', 'Summary', folder))
    bins = binloc(
        excel_list('Bins and Locations 10.22.19.xlsx', 'Summary', folder))
    items = nsitems(excel_list(nsfile, 'Items', folder))

    extract = excel_list(extractfile, selec, folder)
    guide = excel_list(mapfile, selec, folder)

    adj = Adj(extract, guide, items, bins, units)
    # full = adj[0]
    yesbin = finaladj(adj[1], None)
    nobin = finaladj(adj[2], None)

    # excelcreate(full, transfile, 'Full', folder)
    excelcreate(yesbin, transfile, sheetdic[selec][0], folder)
    excelcreate(nobin, transfile, sheetdic[selec][1], folder)