def getInfoComponents(self, info_fmt="%s"):
info_components = super(SyncUpdate_Usr, self).getInfoComponents(info_fmt)
info_components += [
(info_fmt % ("Last Sale", TimeUtils.wpTimeToString(self.bTime))) if self.bTime else "No Last Sale",
(info_fmt % ("%s Mod Time" % self.master_name, TimeUtils.wpTimeToString(self.mTime))) if self.mMod else "%s Not Modded" % self.master_name,
(info_fmt % ("%s Mod Time" % self.slave_name, TimeUtils.wpTimeToString(self.sTime))) if self.sMod else "%s Not Modded" % self.slave_name
]
for tracking_name, cols in self.colData.getACTTrackedCols().items():
col = cols[0]
mColModTime = self.getMColModTime(col)
sColModTime = self.getSColModTime(col)
if mColModTime or sColModTime:
info_components.append(info_fmt % (tracking_name, '%s: %s; %s: %s' % (
self.master_name,
TimeUtils.wpTimeToString(mColModTime),
self.slave_name,
TimeUtils.wpTimeToString(sColModTime),
)))
return info_components
def displayUpdateList(self, updateList, tablefmt=None):
if updateList:
delimeter = "<br/>" if tablefmt=="html" else "\n"
subject_fmt = "<h4>%s</h4>" if tablefmt=="html" else "%s"
# header = ["Column", "Reason", "Old", "New"]
header = OrderedDict([
('col', 'Column'),
('reason', 'Reason'),
('oldLoserValue', 'Old'),
('oldWinnerValue', 'New'),
('mColTime', 'M TIME'),
('sColTime', 'S TIME'),
])
subjects = {}
for warnings in updateList.values():
for warning in warnings:
subject = warning['subject']
if subject not in subjects.keys():
subjects[subject] = []
warning_fmtd = dict([\
(key, SanitationUtils.sanitizeForTable(val))\
for key, val in warning.items()\
if key in header
])
for key in ['mColTime', 'sColTime']:
try:
rawTime = int(warning[key])
if rawTime:
warning_fmtd[key] = TimeUtils.wpTimeToString(rawTime)
except Exception, e:
pass
subjects[subject].append(warning_fmtd)
tables = []
for subject, warnings in subjects.items():
antiSubject_fmtd = subject_fmt % self.opposite_src(subject)
table = [header]+warnings
table_fmtd = tabulate(table, headers='firstrow', \
tablefmt=tablefmt )
tables.append(delimeter.join([antiSubject_fmtd, table_fmtd]))
return delimeter.join(tables)
def analyseRemote(self, parser, since=None, limit=None, filterItems=None):
self.assertConnect()
# srv_offset = self.dbParams.pop('srv_offset','')
self.dbParams['port'] = self.service.local_bind_address[-1]
cursor = pymysql.connect( **self.dbParams ).cursor()
sm_where_clauses = []
if since:
since_t = TimeUtils.wpServerToLocalTime( TimeUtils.wpStrptime(since))
assert since_t, "Time should be valid format, got %s" % since
since_s = TimeUtils.wpTimeToString(since_t)
sm_where_clauses.append( "tu.`time` > '%s'" % since_s )
modtime_cols = [
"tu.`user_id` as `user_id`",
"MAX(tu.`time`) as `Edited in Wordpress`"
]
for tracking_name, aliases in ColData_User.getWPTrackedCols().items():
case_clauses = []
for alias in aliases:
case_clauses.append("LOCATE('\"%s\"', tu.`changed`) > 0" % alias)
modtime_cols.append("MAX(CASE WHEN {case_clauses} THEN tu.`time` ELSE \"\" END) as `{tracking_name}`".format(
case_clauses = " OR ".join(case_clauses),
tracking_name = tracking_name
))
if sm_where_clauses:
sm_where_clause = 'WHERE ' + ' AND '.join(sm_where_clauses)
else:
sm_where_clause = ''
sql_select_modtime = """\
SELECT
{modtime_cols}
FROM
{tbl_tu} tu
{sm_where_clause}
GROUP BY
tu.`user_id`""".format(
modtime_cols = ",\n\t\t".join(modtime_cols),
tbl_tu=self.tbl_prefix+'tansync_updates',
sm_where_clause = sm_where_clause,
)
# print sql_select_modtime
if since:
cursor.execute(sql_select_modtime)
headers = [SanitationUtils.coerceUnicode(i[0]) for i in cursor.description]
results = [[SanitationUtils.coerceUnicode(cell) for cell in row] for row in cursor]
table = [headers] + results
# print tabulate(table, headers='firstrow')
# results = list(cursor)
# if len(results) == 0:
# #nothing to analyse
# return
# else:
# # n rows to analyse
# print "THERE ARE %d ITEMS" % len(results)
wpDbMetaCols = ColData_User.getWPDBCols(meta=True)
wpDbCoreCols = ColData_User.getWPDBCols(meta=False)
userdata_cols = ",\n\t\t".join(filter(None,
[
"u.%s as `%s`" % (key, name)\
for key, name in wpDbCoreCols.items()
] + [
"MAX(CASE WHEN um.meta_key = '%s' THEN um.meta_value ELSE \"\" END) as `%s`" % (key, name) \
for key, name in wpDbMetaCols.items()
]
))
# wpCols = OrderedDict(filter( lambda (k, v): not v.get('wp',{}).get('generated'), ColData_User.getWPCols().items()))
# assert all([
# 'Wordpress ID' in wpCols.keys(),
# wpCols['Wordpress ID'].get('wp', {}).get('key') == 'ID',
# wpCols['Wordpress ID'].get('wp', {}).get('final')
# ]), 'ColData should be configured correctly'
# userdata_cols2 = ",\n\t\t".join(filter(None,[
# ("MAX(CASE WHEN um.meta_key = '%s' THEN um.meta_value ELSE \"\" END) as `%s`" if data['wp'].get('meta') else "u.%s as `%s`") % (data['wp']['key'], col)\
# for col, data in wpCols.items()
# ]))
# print " -> COLS1: ", userdata_cols
# print " -> COLS2: ", userdata_cols2
# print userdata_cols
sql_select_user = """
SELECT
{usr_cols}
FROM
{tbl_u} u
LEFT JOIN {tbl_um} um
ON ( um.`user_id` = u.`ID`)
GROUP BY
u.`ID`""".format(
tbl_u = self.tbl_prefix+'users',
tbl_um = self.tbl_prefix+'usermeta',
usr_cols = userdata_cols,
)
um_on_clauses = []
um_where_clauses = []
um_on_clauses.append('ud.`Wordpress ID` = lu.`user_id`')
if filterItems:
if 'cards' in filterItems:
um_where_clauses.append( "ud.`MYOB Card ID` IN (%s)" % (','.join([
'"%s"' % card for card in filterItems['cards']
])))
if um_on_clauses:
um_on_clause = ' AND '.join([
"(%s)" % clause for clause in um_on_clauses
])
else:
um_on_clause = ''
if um_where_clauses:
um_where_clause = 'WHERE ' + ' AND '.join([
"(%s)" % clause for clause in um_where_clauses
])
else:
um_where_clause = ''
# print sql_select_user
sql_select_user_modtime = """
SELECT *
FROM
(
{sql_ud}
) as ud
{join_type} JOIN
(
{sql_mt}
) as lu
ON {um_on_clause}
{um_where_clause}
{limit_clause};""".format(
sql_ud = sql_select_user,
sql_mt = sql_select_modtime,
join_type = "INNER" if sm_where_clause else "LEFT",
limit_clause = "LIMIT %d" % limit if limit else "",
um_on_clause = um_on_clause,
um_where_clause = um_where_clause
)
if Registrar.DEBUG_CLIENT: Registrar.registerMessage(sql_select_user_modtime)
cursor.execute(sql_select_user_modtime)
headers = [SanitationUtils.coerceUnicode(i[0]) for i in cursor.description]
results = [[SanitationUtils.coerceUnicode(cell) for cell in row] for row in cursor]
rows = [headers] + results
# print rows
if results:
print "there are %d results" % len(results)
parser.analyseRows(rows)
