/
MapBom.py
237 lines (195 loc) · 7.71 KB
/
MapBom.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
#-------------------------------------------------------------------------------
# Name: MapBom.py
# Version: 0.2.5
# Purpose: Creates a Freemind mindmap file which shows a BOM structure
#
# Author: tb126975
#
# Created: 08/12/2009
#
# Changes: 0.2.5: Added links to drawings on Sheffield
# 0.2.4: If quantity is A/R then this is printed instead of error-off
# 0.2.3: Checks to see if Yaml file can be found. If not then uses a local copy
# 0.2.2: Added Yaml file containing Ireland BOMs
# 0.2.1: Development branch to investigate using SELECT IN statements
# 0.2: Modified SQL query to include INVIA part numbers [now irrelevant]
#-------------------------------------------------------------------------------
from tim_modules import pysyteline
import string
import os
import datetime
import getopt, sys
import yaml
import glob
__VERSION__ = '0.2.5'
namedata={}
matdata={}
drawingsdb = {}
drawingfilepaths = glob.glob('\\\\Sheffield\\SPD_Data\\Temporary\\TimBrowning\\Drawings\\*.pdf')
#print a short help message
def usage():
sys.stderr.write("""
-------------------------------------------------------
MapBom %s - Create a BOM map
-------------------------------------------------------
Tim Browning 8/12/2009
USAGE: %s <Top Assembly Number> [outputfile]
Requires Freemind.
""" % (__VERSION__, sys.argv[0], ))
def query(in_list):
command="""select DISTINCT
rvxCurrentMaterials.Item,
rvxCurrentMaterials.Description,
rvxCurrentMaterials.Material,
rvxCurrentMaterials.\"Material Description\" as matdesc,
rvxCurrentMaterials.Quantity
FROM UK_App.dbo.rvxCurrentMaterials
WHERE rvxCurrentMaterials.Item in (%s) AND rvxCurrentMaterials.Material like '_-%%'
ORDER BY rvxCurrentMaterials.Material""" % in_list
return command
def CreateDictionary(part):
if part[-1] != "'" and part[:1] != "'":
part = "'" + part + "'" # The part number needs to be in inverted commas for the SQL query
result = pysyteline.runquery(query(part))
if result != []:
for row in result:
if row.Item not in namedata:
namedata[row.Item] = row.Description
if row.Material not in namedata:
namedata[row.Material] = row.matdesc
for row in result:
if row.Item not in matdata:
matdata[row.Item] = [[row.Material, row.Quantity]]
else:
matdata[row.Item].append([row.Material, row.Quantity])
children = [child.Material for child in result]
child_list=''
for child in children[:-1]: # all child parts apart from the final one
child_list += "'" + str(child) + "', "
child_list += "'" + children[-1] + "'" # the final child in the list has no comma after it
CreateDictionary(child_list)
def AddIrelandParts():
Ire_prefix = " **IRE**"
yamlfile = "\\\\Sheffield\\SPD_Data\\Temporary\\TimBrowning\\IrelandBOMs\\IrelandBOM.yaml"
try:
yamldata = open(yamlfile, 'r').read()
except:
from IrelandBOM import yamldata
print 'Using local copy of Ireland assembly data.'
for assy in yaml.load_all(yamldata):
namedata[assy['Assembly']] = assy['Description'] + Ire_prefix
for part in assy['Items']:
if part['Qty'] == 'A/R':
qty = '999.999'
else:
qty = part['Qty']
if part['Part Number'] not in namedata:
namedata[part['Part Number']] = part['Description']
if assy['Assembly'] not in matdata:
matdata[assy['Assembly']] = [[part['Part Number'], qty]]
else:
matdata[assy['Assembly']].append([part['Part Number'], qty])
def CreateDrawingsDB():
for filepath in drawingfilepaths:
filename = filepath.split('\\')[-1:][0]
item = filename[1:15]
filename = filename.replace('[', '%5B').replace(']', '%5D').replace(' ', '%20').replace('&', '&')
drawingsdb[item] = filename
def findchildren(part,tab):
if tab==-1:
desc = namedata[part]
timeformat = format = "%d-%m-%Y %H:%M:%S"
timenow = datetime.datetime.today().strftime(timeformat)
part_text = part + ' ' + str(desc)
date_text = 'As of: ' + timenow
f.write('<node STYLE="fork" TEXT="' + part_text + '\n' + date_text + '">\n')
f.write('<edge WIDTH="thin"/>\n')
if part in matdata:
result = matdata[part]
if tab != -1:
f.write(' FOLDED="true" >\n')
tab+=1
for row in result:
material = str(row[0])
materialdesc = str(namedata[material])
html = materialdesc.replace('&' , '&').replace('"' , '"')
quant = str(pysyteline.clean_number(row[1], 3))
if quant == '999.999':
quant = 'A/R'
else:
quant = quant + '-off'
line = material + ' ' + html + ' ' + quant
#print line
if drawingsdb.has_key(material):
link = 'LINK="//Sheffield/SPD_Data/Temporary/TimBrowning/Drawings/' + drawingsdb[material] +'" '
else:
link = ''
f.write('<node ' + link + 'POSITION="right" TEXT="' + line + '"')
next = findchildren(material, tab)
if next != 'nochild':
f.write('</node>\n')
else:
f.write('/>\n')
return 'nochild'
if __name__ == '__main__':
#parse command line options
try:
opts, args = getopt.getopt(sys.argv[1:],"",)
except getopt.GetoptError:
# print help information and exit:
usage()
sys.exit(2)
if len(args) == 1:
outputfile = "BOMmindmap.mm"
part = args[0]
elif len(args) == 2:
part = args[0]
outputfile = args[1]
if outputfile[-3:] != ".mm":
outputfile+=".mm"
else:
print "MapBOM version %s" % __VERSION__
print "\n"
print "Enter all or part of the top level assembly number:"
part = raw_input()
outputfile = "BOMmindmap.mm"
item_num = 0
command="""select DISTINCT
rvxCurrentMaterials.Item,
rvxCurrentMaterials.Description
FROM UK_App.dbo.rvxCurrentMaterials
WHERE rvxCurrentMaterials.Item like ?
ORDER BY rvxCurrentMaterials.Item
"""
item = '%' + part + '%'
toplevel = pysyteline.runquery(command, item)
if toplevel == []:
print "\nPart %s doesn't exist in Syteline Current Materials." % (part)
print "\nPress return to end script.\n"
z=raw_input()
sys.exit()
if len(toplevel) > 1:
print "\nMore than one top level item found:\n"
for i, row in enumerate(toplevel):
print "%i: %s %s" % (i, row.Item, row.Description)
print "\nType number of item or press return to end script.\n"
z=raw_input()
try:
item_num = int(z)
except:
sys.exit()
print "\nCreating BOM mind map for %s %s" % (toplevel[item_num].Item, toplevel[item_num].Description)
print "\nDownloading data from Syteline."
CreateDictionary(toplevel[item_num].Item)
AddIrelandParts()
CreateDrawingsDB()
f=open(outputfile, 'w')
f.write('<map version="0.8.1">\n')
f.write('<!-- To view this file, download free mind mapping software FreeMind from http://freemind.sourceforge.net -->\n')
findchildren(toplevel[item_num].Item, -1)
f.write('</node>\n')
f.write('</map>\n')
f.close()
print "\nMind Map file saved as %s" % (outputfile)
print "\nOpening Mind Map"
os.popen(outputfile)