Posted By

stagger on 02/24/08


Tagged

sql textmate python wxpython ttm thesis


Versions (?)

GUI Collector - Main


 / Published in: Python
 

This is the main file. You also need the libraries posted here for the program to work.

  1. #!/usr/bin/env python
  2. # encoding: utf-8
  3. """
  4. collector.py
  5.  
  6. Created by Daniel Link on 2007-12-05.
  7. Copyright (c) 2007 Top Trend Manucfacturing Co., Ltd.. All rights reserved.
  8. """
  9. import wx
  10. import wx.lib.scrolledpanel as scrolled
  11. import wx.lib.newevent
  12. from wx.lib.evtmgr import eventManager
  13. import sys
  14. import os
  15. import pymssql
  16. import softpro
  17. import validators
  18. import string
  19. import csv
  20. import time
  21. import thread
  22.  
  23. FB_SO = 1
  24. FB_CU = 2
  25. FB_PR = 4
  26. FB_MO = 8
  27. FB_OD = 16
  28. FB_CD = 32
  29. FB_SM = 64
  30.  
  31. # This creates a new Event class and a EVT binder function
  32. (ThreadDone, EVT_THREAD_DONE) = wx.lib.newevent.NewEvent()
  33.  
  34. class MainApp(wx.App):
  35. """ Collects various information from SQL server """
  36. def OnInit(self):
  37. """ Create main frame and initialize variables """
  38. self.connected = False # SQL server connection status
  39. self.verbose = False
  40.  
  41. self.main_frame = MainFrame(None, -1, "SoftPRO data collector")
  42. self.nb = self.main_frame.notebook
  43.  
  44. self.thread_id = 1
  45. self.threads = {}
  46.  
  47. # SQL events:
  48. eventManager.Register(self.OnConnect, wx.EVT_BUTTON, self.nb.sql_win.connect_button)
  49. eventManager.Register(self.OnConnect, wx.EVT_TEXT_ENTER, self.nb.sql_win.host_edit)
  50. eventManager.Register(self.OnConnect, wx.EVT_TEXT_ENTER, self.nb.sql_win.user_edit)
  51. eventManager.Register(self.OnConnect, wx.EVT_TEXT_ENTER, self.nb.sql_win.password_edit)
  52. eventManager.Register(self.OnConnect, wx.EVT_TEXT_ENTER, self.nb.sql_win.database_edit)
  53. # Job events:
  54. eventManager.Register(self.OnOPCollect, wx.EVT_BUTTON, self.nb.pages["op_win"].collect_button)
  55. #eventManager.Register(self.OnCostCollect, wx.EVT_BUTTON, self.nb.pages["costs_win"].collect_button)
  56. # Thread events:
  57. eventManager.Register(self.OnClose, wx.EVT_CLOSE, self.main_frame)
  58. #eventManager.Register(self.OnGaugeUpdate, EVT_GAUGE_UPDATE, self.main_frame)
  59. eventManager.Register(self.OnThreadDone, EVT_THREAD_DONE, self.main_frame)
  60.  
  61. self.main_frame.Show(True)
  62.  
  63. return True
  64.  
  65. def OnConnect(self, e):
  66. """ Connect / Disconnect from SQL Server """
  67. nb = self.main_frame.notebook
  68. sql = self.main_frame.notebook.sql_win
  69. host = sql.host_edit.GetValue()
  70. user = sql.user_edit.GetValue()
  71. password = sql.password_edit.GetValue()
  72. database = sql.database_edit.GetValue()
  73.  
  74. if not self.connected: # connect
  75. try:
  76. self.sql_connection = pymssql.connect(host=host, user=user, password=password, database=database)
  77. except:
  78. wx.LogError("Connection failed.nnException: " + str(sys.exc_info()[0]))
  79. raise
  80. self.connected = True
  81. for widget in sql.login_widgets:
  82. widget.Disable()
  83. wx.LogStatus("Connected to %s as %s" % (host, user))
  84. sql.connect_button.SetLabel("Disconnect")
  85. for page in nb.pages.itervalues():
  86. page.Enable()
  87. else: # disconnect
  88. self.sql_connection.close()
  89. self.connected = False
  90. for widget in sql.login_widgets:
  91. widget.Enable()
  92. wx.LogStatus("Disconnected %s from %s" % (user, host))
  93. sql.connect_button.SetLabel("Connect")
  94. for page in nb.pages.itervalues():
  95. page.Disable()
  96.  
  97. def OnOPCollect(self, e):
  98. """ Collect OP strings which pass through filter and write them to disk """
  99.  
  100. dlg = wx.FileDialog(self.main_frame, message="Save file as ...",
  101. defaultDir=os.getcwd(),
  102. defaultFile="",
  103. wildcard="CSV Table for Excel Import|*.csv",
  104. style=wx.SAVE)
  105.  
  106. if dlg.ShowModal() != wx.ID_OK:
  107. return
  108.  
  109. op_filter_box = self.nb.op_filter_box
  110. cur = self.sql_connection.cursor()
  111.  
  112. # Disable some widgets
  113. self.nb.pages["op_win"].collect_button.Disable()
  114. op_filter_box.Disable()
  115.  
  116. wx.LogStatus("Searching for product variants...")
  117.  
  118. # get all sales orders that pass through filter
  119. name = "od20sord"
  120. t = op_filter_box.Translate(name)
  121. query = "SELECT %s.fsono" % t
  122. #for field in op_filter_box.GetCheckedFields():
  123. # if (field != t + ".fsono"):
  124. # query += ", " + op_filter_box.MakeShort(field)
  125. query += " FROM %s %s WHERE %s.fitemno1 LIKE ' 1' " % (name, t, t)
  126. if not op_filter_box.HasChecked():
  127. wx.LogWarning("You have chosen not to filter output. The operation will probably take a very long time.")
  128. else:
  129. query += op_filter_box.GetString()
  130. query += " GROUP BY %s.fsono" % t
  131. wx.LogVerbose("Executing Query: %s" % query)
  132.  
  133. try:
  134. cur.execute(query)
  135. except:
  136. message = "SQL Query failed. "
  137. if wx.Log.GetVerbose():
  138. message += "Check the log for details. Does the query correctly execute when you run it manually with Microsoft SQL Enterprise Manager?"
  139. else:
  140. message += "Please enable verbose output on the logging page and repeat what you just did."
  141. wx.LogError(message + "nnException: " + str(sys.exc_info()[0]))
  142. wx.LogStatus("")
  143. raise
  144.  
  145. results = cur.fetchall()
  146. if not results:
  147. wx.LogWarning("Your query was too restrictive. The server did not return any results. Sorry.")
  148. return
  149. codes = []
  150. for row in results:
  151. codes.append(row[0])
  152.  
  153. wx.LogStatus("Finding OP codes for %d Sales Orders..." % len(codes))
  154. wx.LogVerbose("Get some coffee! Seriously!")
  155.  
  156. # Start thread here
  157. assert not self.threads.has_key("op"), "The OP thread seems to be running already."
  158. self.threads["op"] = OPCollectThread(self.main_frame, self.thread_id, cur, self.nb.op_gauge, self.nb.op_remain, codes, dlg.GetPath()+".csv")
  159. self.threads["op"].Start()
  160.  
  161. button = self.nb.pages["op_win"].collect_button
  162. eventManager.DeregisterWindow(button)
  163. eventManager.Register(self.OnOPAbort, wx.EVT_BUTTON, button)
  164. button.SetLabel("Abort")
  165. button.Enable()
  166.  
  167. self.thread_id += 1
  168.  
  169. def OnCostCollect(self, e):
  170. """Gives all the costs which pass through the filter"""
  171. pass
  172.  
  173. def OnClose(self, e):
  174. """ Stop running threads on window close """
  175. if len(self.threads) > 0:
  176. busy = wx.BusyInfo("Shutting down pending processes...")
  177.  
  178. for t in self.threads.itervalues():
  179. t.Stop()
  180.  
  181. running = 1
  182. while running:
  183. running = 0
  184. for t in self.threads.itervalues():
  185. running = running + t.IsRunning()
  186. time.sleep(0.1)
  187.  
  188. # Pass event to the next handler (and, thus, close the window)
  189. e.Skip()
  190.  
  191. def OnThreadDone(self, e):
  192. t = e.type
  193. assert self.threads.has_key(t), "There is no %s thread!" % t
  194. del self.threads[t]
  195.  
  196. button = self.nb.pages[t+"_win"].collect_button
  197. eventManager.DeregisterWindow(button)
  198. eventManager.Register(self.OnOPCollect, wx.EVT_BUTTON, button)
  199. button.SetLabel("Collect")
  200.  
  201. def OnOPAbort(self, e):
  202. assert self.threads.has_key("op"), "The process can not be aborted, because it is not running!"
  203. self.threads["op"].Stop()
  204. wx.LogStatus("OP Collection aborted.")
  205.  
  206.  
  207. class BasicThread:
  208. def __init__(self, parent, id, cur, gauge, remain, filename):
  209. """ Initialize Thread
  210.  
  211. parent: where EVT_GAUGE_UPDATE shall be sent
  212. cur: pymssql SQL connection cursor
  213. gauge: wx.Gauge
  214. remain: wx.StaticText for remaining time
  215. filename: where to save the results
  216.  
  217. Derived classes _must_ set self.total in their __init__!
  218.  
  219. """
  220. self.parent = parent
  221. self.cur = cur
  222. self.gauge = gauge
  223. self.remain = remain
  224. self.filename = filename
  225.  
  226. self.running = False
  227. self.keepgoing = False
  228. self.progress = 0
  229.  
  230. def Start(self):
  231. self.running = True
  232. self.keepgoing = True
  233. self.start_time = time.time()
  234. wx.LogVerbose("Starting Thread...")
  235. thread.start_new_thread(self.Run, ())
  236.  
  237. def Stop(self):
  238. self.keepgoing = False
  239.  
  240. def IsRunning(self):
  241. return self.running
  242.  
  243. def Run(self):
  244. self.running = False
  245.  
  246.  
  247. class OPCollectThread(BasicThread):
  248. def __init__(self, parent, id, cur, gauge, remain, sorders, filename):
  249. """ Initialize Thread
  250.  
  251. sorders: list of sales order codes
  252.  
  253. """
  254. BasicThread.__init__(self, parent, id, cur, gauge, remain, filename)
  255.  
  256. self.sorders = sorders
  257. self.total = len(sorders)
  258. wx.CallAfter(gauge.SetRange, self.total)
  259. self.opdic = {}
  260.  
  261. def Run(self):
  262. # collect OP codes for current sales order:
  263. while self.keepgoing and (self.progress < self.total):
  264. code = self.sorders[self.progress] # code of current sales order
  265. sorder = softpro.SalesOrder(self.cur, code)
  266.  
  267. for soitem in sorder.items:
  268. bom = soitem.bom
  269. if bom:
  270. opstring = soitem.get_opstring()
  271. qty = int(soitem.bom.quantity)
  272. if opstring:
  273. if not self.opdic.has_key(opstring):
  274. self.opdic[opstring] = qty
  275. else:
  276. self.opdic[opstring] += qty
  277. wx.LogVerbose("%s:%s (%6d) => %s" % (code, soitem.number.lstrip(), soitem.bom.quantity, opstring))
  278.  
  279. self.progress += 1
  280. # Update Gauge
  281. wx.CallAfter(self.gauge.SetValue, self.progress)
  282. # Update StaticText with remaining time
  283. elapsed = time.time() - self.start_time
  284. per_so = elapsed/self.progress
  285. remain = per_so*(self.total-self.progress)
  286. text = time.strftime("%H:%M:%S", time.gmtime(remain))
  287. wx.CallAfter(self.remain.SetLabel, text)
  288.  
  289. # if all sales orders have been processed, write them to disk:
  290. if (self.progress == self.total):
  291. wx.LogVerbose("%d product variants could be identified." % len(self.opdic))
  292.  
  293. writer = csv.writer(file(self.filename, "w"), dialect="excel", delimiter=";")
  294.  
  295. position = 0
  296.  
  297. wx.LogVerbose("Writing %s rows to " % self.filename)
  298.  
  299. for op, quantity in self.opdic.items():
  300. position += 1
  301. writer.writerow([op, quantity])
  302. if (position % 100 == 0):
  303. wx.LogVerbose("%d rows written" % position)
  304.  
  305. wx.CallAfter(wx.LogStatus, "Wrote %d product variants to %s" % (position, self.filename))
  306.  
  307. # reset Gauge
  308. wx.CallAfter(self.gauge.SetValue, 0)
  309.  
  310. # signal thread stop
  311. self.running = False
  312. evt = ThreadDone(type="op")
  313. wx.PostEvent(self.parent, evt)
  314.  
  315.  
  316. class MainFrame(wx.Frame):
  317. def __init__(self,parent,id,title):
  318. wx.Frame.__init__(self, parent, wx.ID_ANY, title, size=(800,600), style=wx.DEFAULT_FRAME_STYLE)
  319. self.CreateStatusBar()
  320. sizer = wx.BoxSizer(wx.VERTICAL)
  321. self.notebook = notebook = MainNotebook(self, wx.ID_ANY)
  322. sizer.Add(notebook, 1, wx.EXPAND)
  323. self.SetSizer(sizer)
  324.  
  325.  
  326. class MainNotebook(wx.Notebook):
  327. def __init__(self, parent, id):
  328. """ Add pages and disable all except SQL Connection """
  329. wx.Notebook.__init__(self, parent, id)
  330.  
  331. self.pages = {} # for easy disabling of collector pages
  332.  
  333. self.AddGeneralPage()
  334. self.AddOPPage()
  335. #self.AddCostsPage()
  336.  
  337. for page in self.pages.itervalues():
  338. page.Disable()
  339.  
  340. def AddGeneralPage(self):
  341. """ Add Page for SQL Connection """
  342. row = 1
  343. self.sql_win = win = wx.Panel(self, wx.ID_ANY)
  344. sizer = wx.BoxSizer(wx.VERTICAL)
  345.  
  346. win.login_widgets = []
  347. login_box = wx.StaticBox(win, wx.ID_ANY, "SQL Connection")
  348. login_box_sizer = wx.StaticBoxSizer(login_box, wx.VERTICAL)
  349. login_sizer = wx.FlexGridSizer(cols=2, vgap=10, hgap=10)
  350. login_sizer.AddGrowableCol(1)
  351. # Host
  352. text = wx.StaticText(win, wx.ID_ANY, "Host:")
  353. win.login_widgets.append(text)
  354. login_sizer.Add(text, 0)
  355. sql_host = "server ip"
  356. if (sys.platform == "darwin"): # Mac needs port number too
  357. sql_host += ":1433"
  358. win.host_edit = edit = wx.TextCtrl(win, wx.ID_ANY, sql_host, validator=validators.HostValidator(), style=wx.TE_PROCESS_ENTER)
  359. win.login_widgets.append(edit)
  360. login_sizer.Add(edit, 1, wx.EXPAND)
  361. row += 1
  362. # User
  363. text = wx.StaticText(win, wx.ID_ANY, "User:")
  364. win.login_widgets.append(text)
  365. login_sizer.Add(text, 0)
  366. win.user_edit = edit = wx.TextCtrl(win, wx.ID_ANY, "user name", style=wx.TE_PROCESS_ENTER)
  367. win.login_widgets.append(edit)
  368. login_sizer.Add(edit, 1, wx.EXPAND)
  369. row += 1
  370. # Password
  371. text = wx.StaticText(win, wx.ID_ANY, "Password:")
  372. win.login_widgets.append(text)
  373. login_sizer.Add(text, 0)
  374. win.password_edit = edit = wx.TextCtrl(win, wx.ID_ANY, style=wx.TE_PASSWORD | wx.TE_PROCESS_ENTER)
  375. edit.SetFocus()
  376. win.login_widgets.append(edit)
  377. login_sizer.Add(edit, 1, wx.EXPAND)
  378. row += 1
  379. # Database
  380. text = wx.StaticText(win, wx.ID_ANY, "Database:")
  381. win.login_widgets.append(text)
  382. login_sizer.Add(text, 0)
  383. win.database_edit = edit = wx.TextCtrl(win, wx.ID_ANY, "TTM", style=wx.TE_PROCESS_ENTER)
  384. win.login_widgets.append(edit)
  385. login_sizer.Add(edit, 1, wx.EXPAND)
  386. row += 1
  387. # Events
  388. eventManager.Register(self.OnSQLChange, wx.EVT_TEXT, win)
  389. # Put in supersizer
  390. login_box_sizer.Add(login_sizer, 0, wx.EXPAND)
  391. login_box_sizer.AddSpacer((1,10))
  392. # Connect Button
  393. win.connect_button = button = wx.Button(win, wx.ID_ANY, "Connect")
  394. login_box_sizer.Add(button, 0, wx.EXPAND)
  395. # Add box sizer to window sizer
  396. sizer.Add(login_box_sizer, 0, wx.EXPAND)
  397.  
  398. log_box = wx.StaticBox(win, wx.ID_ANY, "Logging")
  399. log_box_sizer = wx.StaticBoxSizer(log_box, wx.VERTICAL)
  400. # Toggle Verbose Mode
  401. self.check_verbose = check = wx.CheckBox(win, wx.ID_ANY, "Verbose Mode")
  402. eventManager.Register(self.OnVerboseCheck, wx.EVT_CHECKBOX, check)
  403. log_box_sizer.Add(check, 0)
  404. sizer.Add(log_box_sizer, 0, wx.EXPAND)
  405.  
  406. win.SetSizer(sizer)
  407. self.AddPage(win, "General")
  408.  
  409. def AddOPPage(self):
  410. """ Add Page for OP Sequence """
  411. self.pages["op_win"] = win = scrolled.ScrolledPanel(self, wx.ID_ANY)
  412. sizer = wx.BoxSizer(wx.VERTICAL)
  413.  
  414. """
  415. # Output options
  416. self.op_group_box = box = GroupBox(win, wx.ID_ANY, "Output Fields")
  417. sizer.Add(box.sizer, 0, wx.EXPAND)
  418. """
  419.  
  420. # Filter
  421. self.op_filter_box = box = FilterBox(win, wx.ID_ANY, "Filter by", style=FB_SO|FB_OD|FB_CD)
  422. sizer.Add(box.sizer, 0, wx.EXPAND)
  423.  
  424. # Gauge
  425. subsizer = wx.BoxSizer(wx.HORIZONTAL)
  426. self.op_gauge = gauge = wx.Gauge(win, wx.ID_ANY)
  427. subsizer.Add(gauge, 1)
  428. subsizer.AddSpacer((5,1))
  429. self.op_remain = text = wx.StaticText(win, wx.ID_ANY, "00:00:00")
  430. subsizer.Add(text, 0)
  431. subsizer.AddSpacer((5,1))
  432. sizer.Add(subsizer, 0, wx.EXPAND)
  433.  
  434. # Collect Button
  435. win.collect_button = button = wx.Button(win, wx.ID_ANY, "Collect")
  436. sizer.Add(button, 0, wx.EXPAND)
  437.  
  438. """
  439. # Test Button
  440. button = wx.Button(win, wx.ID_ANY, "Print SQL Filter and Group string")
  441. eventManager.Register(self.OnTestButton, wx.EVT_BUTTON, button)
  442. sizer.Add(button, 0, wx.EXPAND)
  443. """
  444.  
  445. win.SetSizer(sizer)
  446. win.SetupScrolling()
  447. self.AddPage(win, "OP Sequence")
  448.  
  449. def AddCostsPage(self):
  450. """ Add Page for Actual Costs """
  451. self.pages["costs_win"] = win = scrolled.ScrolledPanel(self, wx.ID_ANY)
  452. sizer = wx.BoxSizer(wx.VERTICAL)
  453.  
  454. # Output options
  455. self.costs_group_box = box = GroupBox(win, wx.ID_ANY, "Output Fields")
  456. sizer.Add(box.sizer, 0, wx.EXPAND)
  457.  
  458. # Filter
  459. self.costs_filter_box = box = FilterBox(win, wx.ID_ANY, "Filter by")
  460. sizer.Add(box.sizer, 0, wx.EXPAND)
  461.  
  462. # Gauge
  463. self.costs_gauge = gauge = wx.Gauge(win, wx.ID_ANY)
  464. sizer.Add(gauge, 0, wx.EXPAND)
  465.  
  466. # Collect Button
  467. win.collect_button = button = wx.Button(win, wx.ID_ANY, "Collect")
  468. sizer.Add(button, 0, wx.EXPAND)
  469.  
  470. win.SetSizer(sizer)
  471. win.SetupScrolling()
  472. self.AddPage(win, "Actual Costs")
  473.  
  474. def OnSQLChange(self, event):
  475. """ Disable Connect button when required edit field is empty """
  476. if event.GetString() == "":
  477. self.connect_button.Disable()
  478. else:
  479. self.connect_button.Enable()
  480.  
  481. def OnVerboseCheck(self, event):
  482. wx.Log.SetVerbose(self.check_verbose.GetValue())
  483.  
  484. def OnTestButton(self, event):
  485. pass
  486.  
  487.  
  488. class SQLBox(wx.StaticBox):
  489. def __init__(self, parent, id, label):
  490. wx.StaticBox.__init__(self, parent, id, label)
  491.  
  492. self.row = 0
  493.  
  494. # "internal name": ("name on server", "output title")
  495. self.sql_fields = {"sales order": ("od20sord.fsono", "Sales Order"),
  496. "so item": ("od20sord.fitemno1", "Sales Order Item"),
  497. "work order": ("pd20woi1.forderno", "Work Order"),
  498. "op seq": ("bd01stb1.fopseq", "OP Sequence"),
  499. "op code": ("bd01stb1.fopcode", "OP Code"),
  500. "comp code": ("bd01stb2.fcompcode", "Component Code"),
  501. "comp qty": ("bd01stb2.fqty", "Component Quantity"),
  502. "customer": ("empty", "Customer ID"),
  503. "product": ("od20sord.fpdcode", "Product Code"),
  504. "mould": ("empty", "Mould Code"),
  505. "order date": ("od20sord.fsodate", "Order Date"),
  506. "commit date": ("od20sord.fddate", "Commit Date"),
  507. "order qty": ("od20sord.fqty", "Order Quantity"),
  508. "plan qty": ("pd20woi1.fwoqty", "Plan Quantity"),
  509. "salesman": ("empty", "Salesman ID")}
  510.  
  511. # building dict like: {"Product Code":"od20sord.fpdcode"}
  512. self.titles = {}
  513. for field in self.sql_fields.iterkeys():
  514. key = self.sql_fields[field][1]
  515. value = field
  516. self.titles[key] = value
  517.  
  518. # building dict like: {"od20sord.fpdcode":"product"}
  519. self.names = {}
  520. for field in self.sql_fields.iterkeys():
  521. key = self.sql_fields[field][0]
  522. value = field
  523. self.names[key] = value
  524.  
  525. # building table translator dictionary likke: {"od20sord": "t1"}
  526. self.translator = {}
  527. index = 1
  528. for name in self.names.iterkeys():
  529. if self.translator.has_key(name):
  530. continue
  531. table = name.rstrip(string.letters).replace(".", "")
  532. self.translator[table] = "t%d" % index
  533. index += 1
  534.  
  535. def GetTitles(self):
  536. """ Gives a simple list of field descriptions """
  537. field_names = []
  538. for title in self.titles.iterkeys():
  539. field_names.append(title)
  540. return field_names
  541.  
  542. def Translate(self, table):
  543. """ Give table's short identifier """
  544. assert self.translator.has_key(table), "No dictionary entry for %s!" % table
  545. return self.translator[table]
  546.  
  547. def MakeShort(self, field):
  548. """ Replaces table in table.field with short identifier """
  549. alphanum = string.letters + string.digits
  550. table = field.rstrip(alphanum).replace(".", "")
  551. table = self.Translate(table)
  552. field = field.lstrip(alphanum)
  553. complete = table + field
  554. assert complete, "%s does not have the expected format "table.field""
  555. return complete
  556.  
  557.  
  558. class FilterBox(SQLBox):
  559. def __init__(self, parent, id, label, style=FB_SO|FB_CU|FB_PR|FB_MO|FB_OD|FB_CD|FB_SM):
  560. SQLBox.__init__(self, parent, id, label)
  561.  
  562. self.ranges = {} # {name+"_min": textctrl, name+"_max": textctrl}
  563. self.checks = {} # {name: checkbox}
  564. self.selects = {} # {name+"_sel": listbox}
  565. self.ids = {} # {button_id: listbox/editctrl}
  566. self.id = 100
  567. self.sizer = wx.StaticBoxSizer(self, wx.VERTICAL)
  568. self.subsizer = wx.GridBagSizer(vgap=10, hgap=10)
  569. self.style = style
  570.  
  571. def AddRangeLine(linetype, name):
  572. self.ids[self.id] = {}
  573. self.ids[self.id+1] = {}
  574. parent = self.GetParent()
  575. title = self.sql_fields[name][1]
  576.  
  577. if linetype == "text":
  578. self.ids[self.id]["textctrl"] = self.ranges[name+"_min"] = wx.TextCtrl(parent, wx.ID_ANY)
  579. self.ids[self.id]["textctrl"] = self.ranges[name+"_max"] = wx.TextCtrl(parent, wx.ID_ANY)
  580. elif linetype == "date":
  581. self.ids[self.id]["datepickerctrl"] = self.ranges[name+"_min"] = wx.DatePickerCtrl(parent, wx.ID_ANY, style = wx.DP_DEFAULT | wx.DP_SHOWCENTURY)
  582. self.ids[self.id+1]["datepickerctrl"] = self.ranges[name+"_max"] = wx.DatePickerCtrl(parent, wx.ID_ANY, style = wx.DP_DEFAULT | wx.DP_SHOWCENTURY)
  583. else:
  584. raise "Unknown type of range line: " + linetype
  585.  
  586. self.checks[name] = check = wx.CheckBox(parent, wx.ID_ANY, title)
  587.  
  588. self.subsizer.Add(check, (self.row,0))
  589. self.subsizer.Add(wx.StaticText(parent, wx.ID_ANY, "min:"), (self.row,1))
  590. self.subsizer.Add(self.ranges[name+"_min"], (self.row,2))
  591. self.subsizer.Add(wx.StaticText(parent, wx.ID_ANY, "max:"), (self.row,3))
  592. self.subsizer.Add(self.ranges[name+"_max"], (self.row,4))
  593.  
  594. self.id += 2
  595. self.row += 1
  596.  
  597. def AddSelectLine(name):
  598. self.ids[self.id] = {}
  599. parent = self.GetParent()
  600. title = "Certain %ss" % self.sql_fields[name][1]
  601.  
  602. self.checks[name+"_sel"] = check = wx.CheckBox(parent, wx.ID_ANY, title)
  603. self.subsizer.Add(check, (self.row,0))
  604.  
  605. self.ids[self.id]["textctrl"] = self.selects[name+"_sel"] = wx.TextCtrl(parent, wx.ID_ANY)
  606. self.subsizer.Add(self.selects[name+"_sel"], (self.row,2))
  607.  
  608. button = wx.Button(parent, self.id, "Add")
  609. eventManager.Register(self.OnListAdd, wx.EVT_BUTTON, button)
  610. self.subsizer.Add(button, (self.row,3))
  611.  
  612. self.ids[self.id]["listbox"] = self.selects[name] = wx.ListBox(parent, wx.ID_ANY, style=wx.LB_SINGLE | wx.LB_NEEDED_SB | wx.LB_SORT)
  613. self.subsizer.Add(self.selects[name], (self.row,4))
  614.  
  615. button = wx.Button(parent, self.id, "Remove")
  616. eventManager.Register(self.OnListDel, wx.EVT_BUTTON, button)
  617. self.subsizer.Add(button, (self.row,5))
  618.  
  619. self.id += 1
  620. self.row += 1
  621.  
  622. if style & FB_SO:
  623. AddRangeLine("text", "sales order")
  624. if style & FB_CU:
  625. AddRangeLine("text", "customer")
  626. AddSelectLine("customer")
  627. if style & FB_PR:
  628. AddRangeLine("text", "product")
  629. AddSelectLine("product")
  630. if style & FB_MO:
  631. AddRangeLine("text", "mould")
  632. AddSelectLine("mould")
  633. if style & FB_OD:
  634. AddRangeLine("date", "order date")
  635. if style & FB_CD:
  636. AddRangeLine("date", "commit date")
  637. if style & FB_SM:
  638. AddRangeLine("text", "salesman")
  639.  
  640. self.sizer.Add(self.subsizer, 0, wx.EXPAND)
  641.  
  642. def GetString(self):
  643. """gives selected filters as SQL conditions"""
  644. sql_filter = ""
  645.  
  646. def from_select(name):
  647. """Give SQL condition from listbox items"""
  648.  
  649. if not self.checks[name+"_sel"].GetValue():
  650. return ""
  651.  
  652. sel = self.selects[name+"_sel"]
  653. count = sel.GetCount()
  654. items = []
  655. sql_filter = ""
  656.  
  657. field = self.sql_fields[name][0]
  658. field = self.MakeShort(field)
  659.  
  660. for pos in range(count):
  661. string = sel.GetString(pos)
  662. items.append(string)
  663.  
  664. for item in items:
  665. sql_filter += "OR %s LIKE %s " % (field, item)
  666.  
  667. return sql_filter
  668.  
  669. def from_range(fieldtype, name):
  670. "Give SQL range condition from TextCtrl (edit) fields"
  671.  
  672. if not self.checks[name].GetValue():
  673. return ""
  674.  
  675. tmp = ""
  676. field = self.sql_fields[name][0]
  677. field = self.MakeShort(field)
  678.  
  679. min_val = self.ranges[name+"_min"].GetValue()
  680. if min_val:
  681. if fieldtype == "date":
  682. tmp += "AND DATEPART(dd, %s) >= %s " % (field, min_val.GetDay())
  683. tmp += "AND DATEPART(mm, %s) >= %s " % (field, min_val.GetMonth())
  684. tmp += "AND DATEPART(yyyy, %s) >= %s " % (field, min_val.GetYear())
  685. else:
  686. tmp += "AND %s >= %s " % (field, min_val)
  687.  
  688. max_val = self.ranges[name+"_max"].GetValue()
  689. if max_val:
  690. if fieldtype == "date":
  691. tmp += "AND DATEPART(dd, %s) <= %s " % (field, max_val.GetDay())
  692. tmp += "AND DATEPART(mm, %s) <= %s " % (field, max_val.GetMonth())
  693. tmp += "AND DATEPART(yyyy, %s) <= %s " % (field, max_val.GetYear())
  694. else:
  695. tmp += "AND %s <= %s " % (field, max_val)
  696.  
  697. return tmp
  698.  
  699. if self.style & FB_SO:
  700. sql_filter += from_range("text", "sales order")
  701. if self.style & FB_CU:
  702. sql_filter += from_range("text", "customer")
  703. sql_filter += from_select("customer")
  704. if self.style & FB_PR:
  705. sql_filter += from_range("text", "product")
  706. sql_filter += from_select("product")
  707. if self.style & FB_MO:
  708. sql_filter += from_range("text", "mould")
  709. sql_filter += from_select("mould")
  710. if self.style & FB_OD:
  711. sql_filter += from_range("date", "order date")
  712. if self.style & FB_CD:
  713. sql_filter += from_range("date", "commit date")
  714. if self.style & FB_SM:
  715. sql_filter += from_range("text", "salesman")
  716.  
  717. return sql_filter
  718.  
  719. def GetCheckedFields(self):
  720. """ Gives a list of all checked SQL field names. """
  721. fields = []
  722. for name, check in self.checks.items():
  723. field = self.sql_fields[name.replace("_sel", "")][0]
  724. if check.GetValue() and (field != "empty"):
  725. fields.append(field)
  726. return fields
  727.  
  728. def OnListAdd(self, e):
  729. """Add a value from an edit field to a listbox"""
  730. listbox = self.ids[e.GetId()]["listbox"]
  731. edit = self.ids[e.GetId()]["textctrl"]
  732. value = edit.GetValue()
  733. if value:
  734. listbox.Append(value)
  735.  
  736. def OnListDel(self, e):
  737. """Delete the selected value from a listbox"""
  738. listbox = self.ids[e.GetId()]["listbox"]
  739. edit = self.ids[e.GetId()]["textctrl"]
  740. selection = listbox.GetSelections()
  741. if selection:
  742. listbox.Delete(selection[0])
  743.  
  744. def HasChecked(self):
  745. """ True if any item is checked. """
  746. result = False
  747. for checkbox in self.checks.itervalues():
  748. if checkbox.GetValue():
  749. result = True
  750. return result
  751.  
  752.  
  753. class GroupBox(SQLBox):
  754. def __init__(self, parent, id, label):
  755. SQLBox.__init__(self, parent, id, label)
  756.  
  757. # create and add the CheckListBox
  758. self.sizer = wx.StaticBoxSizer(self, wx.VERTICAL)
  759. self.checklist = wx.CheckListBox(parent, wx.ID_ANY, choices=self.GetTitles(), style=wx.LB_SORT)
  760. self.sizer.Add(self.checklist, 1, wx.EXPAND)
  761.  
  762. def GetSelection(self):
  763. """ Gives a dictionary of selected fields like: {"title": "field name"} """
  764. count = self.checklist.GetCount()
  765. checked = {}
  766. for index in range(count):
  767. if self.checklist.IsChecked(index):
  768. key = self.checklist.GetString(index)
  769. value = self.titles[key]
  770. checked[key] = value
  771. return checked
  772.  
  773. def GetString(self, get_all=False):
  774. """gives selected columns as SQL GROUP BY for output"""
  775. sql_group = ""
  776.  
  777. def is_empty(name):
  778. if (name == "empty"):
  779. wx.LogError("Missing SQL field translation!")
  780. return True
  781. return False
  782.  
  783. if get_all:
  784. for value in self.sql_fields.itervalues():
  785. if is_empty(value[0]):
  786. continue
  787. sql_group += value[0] + ", "
  788. else:
  789. for name in self.GetSelection().itervalues():
  790. if is_empty(name):
  791. continue
  792. sql_group += name + ", "
  793.  
  794. if sql_group:
  795. sql_group = "GROUP BY " + sql_group.rstrip(", ") + " ORDER BY " + self.sql_fields["sales order"][0]
  796.  
  797. return sql_group
  798.  
  799. def HasChecked(self):
  800. """ True if any item is checked. """
  801. return (len(self.GetSelection()) > 0)
  802.  
  803.  
  804.  
  805.  
  806. if __name__ == '__main__':
  807. App = MainApp(redirect = 0) # redirect stdio to console
  808. App.SetAppName("SoftPRO Data Collector")
  809. App.MainLoop()

Report this snippet  

You need to login to post a comment.