Reports - user usefullness

sitescripts/reports/utils.py

 # coding: utf-8
 
 # This Source Code is subject to the terms of the Mozilla Public License
 # version 2.0 (the "License"). You can obtain a copy of the License at
 # http://mozilla.org/MPL/2.0/.
 
 import hashlib, hmac, base64, MySQLdb, os, re, marshal, subprocess
 from sitescripts.utils import get_config, cached, get_template, sendMail
 
 def getReportSubscriptions(guid):
@@ skipping 93 matching lines @@
          '''DELETE FROM #PFX#reports WHERE guid = %s''',
          (guid))
   get_db().commit()
   file = os.path.join(get_config().get('reports', 'dataPath'), guid[0], guid[1], guid[2], guid[3], guid + '.html')
   if os.path.isfile(file):
     os.remove(file)
   file = os.path.join(get_config().get('reports', 'dataPath'), guid[0], guid[1], guid[2], guid[3], guid + '.png')
   if os.path.isfile(file):
     os.remove(file)
 
+@cached(3600)
+def getUserUsefulnessScore(contact):
+  if contact == None:
+    return 0
+
+  cursor = get_db().cursor()
+  # source from http://www.evanmiller.org/how-not-to-sort-by-average-rating.html
+  executeQuery(cursor,
+              '''SELECT ((positive + 1.9208) / (positive + negative)
+                        - 1.96 * SQRT((positive * negative) / (positive + negative) + 0.9604) / (positive + negative))
+                        / (1 + 3.8416 / (positive + negative)) AS score FROM #PFX#users WHERE id = %s''',
+              (contact))
+  score = cursor.fetchone()
+  if score == None:
+    return 0
+
+  if score[0] == None: # no score yet
+    return 0.3
+  else:
+    return 4 * score[0]
+
+def updateUserUsefulness(contact, newusefulness, oldusefulness):
+  new = int(newusefulness)
+  old = int(oldusefulness)
+  if new == old:
+    return
+  positive = 0
+  negative = 0
+  if old > 0:
+    positive -= 1
+  elif old < 0:
+    negative -= 1
+  if new > 0:
+    positive += 1
+  elif new < 0:
+    negative += 1
+  cursor = get_db().cursor()
+  executeQuery(cursor,
+              '''UPDATE #PFX#users SET negative = negative + %s, positive = positive + %s WHERE id = %s''',
+              (negative, positive, contact))
+  get_db().commit()
+
 def saveScreenshot(guid, screenshot):
   prefix = 'data:image/png;base64,'
   if not screenshot.startswith(prefix):
     raise TypeError('Screenshot is not a PNG image')
   data = base64.b64decode(screenshot[len(prefix):])
   file = os.path.join(get_config().get('reports', 'dataPath'), guid[0], guid[1], guid[2], guid[3], guid + '.png')
   dir = os.path.dirname(file)
   if not os.path.exists(dir):
     os.makedirs(dir)
   f = open(file, 'wb')
@@ skipping 51 matching lines @@
   dbpasswd = get_config().get('reports', 'dbpassword')
   if os.name == 'nt':
     return MySQLdb.connect(user=dbuser, passwd=dbpasswd, db=database, use_unicode=True, charset='utf8', named_pipe=True)
   else:
     return MySQLdb.connect(user=dbuser, passwd=dbpasswd, db=database, use_unicode=True, charset='utf8')
     
 def executeQuery(cursor, query, args=None):
   tablePrefix = get_config().get('reports', 'dbprefix')
   query = re.sub(r'#PFX#', tablePrefix, query)
   cursor.execute(query, args)