SHARE
TWEET

Untitled

a guest Apr 19th, 2019 105 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. # To make a table, we first need column names. On the start of a screen, every table must be blank.
  2. # In Queries.py, inside the load() method of your screen, make a simple SELECT query and get all of the columns you need.
  3. # Don't worry about filtering data for now, we just need the column names:
  4. """
  5. cursor.execute(f"SELECT CONCAT(FirstName, ' ', LastName) AS Name, COUNT(*) As NumShifts FROM "
  6.                f"assignto JOIN user on assignto.StaffUsername = user.Username GROUP BY Name")
  7. staff = cursor.fetchall()
  8. """
  9.  
  10. # cursor.fetchall() returns a list of dictionaries. For this example, the variable staff would look like:
  11. # [{'Name': 'James Smith', 'NumShifts': 3}, {'Name': 'Staff 1', 'NumShifts': 2} {'Name': 'Staff 2', 'NumShifts': 5}]
  12.  
  13. # However, we want only the column names. To do this, I simply replace all of the
  14. # values in each dictionary with empty strings.
  15. """
  16. for i in staff:
  17.     for key in i:
  18.         i[key] = ""
  19. """
  20.  
  21. # In order for the table to accept data, it needs to be in the form of
  22. # {1: {ColName: Value, ColName2: Value2}, 2: {ColName: Value, ColName2: Value2}}   where 1, 2, 3... are the numbers of the rows.
  23.  
  24. # And, in this case, Value1, Value2, Value3... will always be an empty string.
  25. # We only need one row to fill the table with (since all the values are empty and we only need column names),
  26. # and we need it in the form (like I said above) {RowNum: {ColName: Value}}, so we do the following line:
  27.  
  28. """
  29. staff = {1: staff[0]}
  30. """
  31.  
  32. # And now the variable staff looks like : {1: {'Name': '', 'NumShifts': ''}}.
  33. # We return this, along with anything else we need (for instance, a list of sites for a dropdown, etc).
  34.  
  35. # Now, back in Beltline.py,
  36. # You make a table this this, inside the display() method of your screen.
  37. """
  38. staff = self.SQL.load()
  39. self.resultTable = TableCanvas(self, editable=True, data=staff,
  40.                                read_only=True, rowheaderwidth=15, maxcellwidth=200, cellwidth=150,
  41.                                rows=len(staff), thefont=('Helvetica', 10), autoresizecols=1,
  42.                                width=150 * len(list(staff.values())[0]), height=25 * 7)
  43. self.resultTable.show()
  44. """
  45. # Excellent, now we have a blank table. Whenever the user presses the filter button, inside the filter() method,
  46. # we will get all of the stuff to filter with (i.e. price, date range, etc) from all of the boxes/dropdowns on the screen.
  47.  
  48. # This is inside the filter() method in Beltline.py.
  49. # Here, we get all of the info the user can input.
  50. """
  51. site, fname, lname, d1, d2 = self.site.get(), self.fname.get(), self.lname.get(), self.d1.get(), self.d2.get()
  52. """
  53. #Next, you'd want to check to make sure all of these are the right kinds of values.
  54. #For instance, check if dates are actually dates, check if prices are actually floats, etc etc
  55.  
  56.  
  57. # Then, we want to convert any blank values, or any dropdowns with the value 'Any', to None. This is important because,
  58. # in Queries.py, if a value is None, then we do not include it in a WHERE clause.
  59. """
  60. conv = {'': None, 'Any': None}
  61. site, fname, lname, d1, d2 = conv.get(site, site), conv.get(fname, fname), conv.get(lname, lname), conv.get(d1, d1), conv.get(d2, d2)
  62. """
  63. # .get() is a method for dictionaries. Basically, we look up a value in the dict, and if it isn't there then we return a default.
  64. # Like this: .get(ItemToFind, Value to Return if Item Isn't In There).
  65. # So, for example, we conv.get(site, site), which looks for an entry in conv. If site is '' or 'Any', it returns None,
  66. # but if site is anything else, then we return the default value, site.
  67.  
  68. # Now, all of our values are either None, which implies the user didn't input anything, or they are something that the user input.
  69.  
  70. # By default, the table should be sorted by the first sortable column, so if sort wasn't defined, we make it 'Name',
  71. # which in this case is the first sortable column.
  72. """
  73. if sort is None:
  74.     sort = 'Name'
  75. """
  76. # Now, we call self.SQL.filter() with all of the data, some of which is None, and some of which is actual user input.
  77. """
  78. staff = self.SQL.filter(site, fname, lname, d1, d2, sort)
  79. """
  80.  
  81. # Inside Queries.py, in the filter() method for your screen, you'd do the following:
  82. """
  83. query = "SELECT CONCAT(FirstName, ' ', LastName) AS Name, COUNT(*) As NumShifts FROM " \
  84.                 "assignto JOIN user on assignto.StaffUsername = user.Username WHERE 1=1 "
  85.  
  86.         if site:
  87.             query += f"AND SiteName = '{site}'"
  88.         if fname:
  89.             query += f"AND FirstName LIKE '%{fname}%' "
  90.         if lname:
  91.             query += f"AND LastName LIKE '%{lname}%' "
  92.         if d1 and d2:
  93.             query += f"AND StartDate BETWEEN {d1} AND {d2} "
  94.         elif d1:
  95.             query += f"AND StartDate >= {d1} "
  96.         elif d2:
  97.             query += f"AND StartDate <= {d2} "
  98.  
  99.         query += f"GROUP BY Name ORDER BY {sort}"
  100. """
  101. # This is why it was important to change the values to None, because we can easily say
  102. # if Value:
  103. #   query += WHERE CLAUSE
  104.  
  105. # At the end of constructing our where statement, we will add a GROUP BY clause if necessary, and ORDER BY whatever sort we passed in.
  106. # Then, we execute the query and do .fetchall()
  107. """
  108. staff = cursor.fetchall()
  109.  
  110. for i in staff:
  111.     for key in i:
  112.         i[key] = str(i[key])
  113. """
  114. # The for loop here converts everything to a String, which might not be necessary, but I do it just to be safe
  115. # Since I don't know how the Table will display certain values.
  116.  
  117. # Remember, the Table accepts data in the format {RowNum: {ColName: Value}},
  118. # and fetchall() returns a list of dictionaries. So, let's fix that.
  119. """
  120. staff = {i + 1: staff[i] for i in range(len(staff))}
  121.  
  122. return staff
  123. """
  124. # Now, back in Beltline.py, we have a list of staff. We clear the table completely, and reimport the data.
  125. """
  126. self.resultTable.model.deleteRows(range(0, self.resultTable.model.getRowCount()))
  127. self.resultTable.model.importDict(staff)
  128. self.resultTable.redraw()
  129. """
  130.  
  131.  
  132. # Okay, perfect, the table works. What if we want to select a row from the data (for example, stuff like Event/Site Detail)?
  133. # Just do something the following:
  134. """
  135. row = self.resultTable.model.getData()[self.resultTable.getSelectedRow() + 1]
  136.     user_name, user_type, status = row['Username'], row['UserType'], row['Status']
  137.  
  138.     if any([user_name == '', user_type == '', status == '']):
  139.         messagebox.showwarning('Error', 'No user selected. Make sure to click on the non-empty '
  140.                                         'row number to select which transit you are taking.')
  141.         return
  142. """
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top