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,
units,
bins,
ven,
error=None,
iID=True)
if item.find('Assembly') != -1:
full = addAss(full, itemdic)
excelcreate(full, file, 'Transform', folder)
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)
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)
# projlookup = projectlookup(excel_list(extract, 'Projects', folder))
# print(mergedic)
newdata = nslist(data, ms_map, mergedic, milestonedics, projlookup, test)
newdata = missingPred(newdata)
newdata = owner(newdata,
excel_list('NetSuite Projects.xlsx', 'Owner', folder))
excelcreate(newdata, writefile, 'All Milestones', folder)
"""COLUMNSUSED IS NOT WORKING PROPERLY KMS"""
"""NS Mapping is actually pretty automated so this really isnt needed"""
# columns = columnsused(newdata)
# excelcreate(columns, writefile, 'Milestones', folder)
# print(len(columns[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')
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'))
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)
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)
excelcreate(dic_list_tolist(itemdic), file, 'ItemDic', folder)
excelcreate(bom, file, 'BOM', folder)
excelcreate(rev, file, 'BOM Revision', folder)
# excelcreate(full, file, 'Transform', folder)
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)
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)
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)
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())
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')
txt.close()
excelcreate(full, newfile, 'Sheet1', folder)
# multiexcelwrite(newfile, wdic, folder)
# excelcreate([['Jackson Test']], newfile, 'Sheet1', folder)
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)
# print(full)
