def parseSTime(self, rawSTime): return TimeUtils.wpServerToLocalTime(TimeUtils.wpStrptime(rawSTime))
def wp_modtime(self): return TimeUtils.wpServerToLocalTime(TimeUtils.wpStrptime(self.get("Edited in Wordpress", 0)))
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)