def do_inject(config): sugar_db = litesugarcrm.LiteSugarCRM(config['sugar.user'], config['sugar.password']) mysql_conf = {'user': config['mysql.user'], 'passwd': config['mysql.password'], 'db': config['mysql.db'], 'host': config['mysql.host'], 'charset': 'utf8'} sql_db = MySQLdb.connect(**mysql_conf) def my_config(binder): binder.bind(SugarDb, sugar_db) binder.bind(SQLDb, sql_db) inject.configure(my_config) Logger.info('Starting')
def __batch_update__(self, items): db = inject.instance(SugarDb) qry = ChainSugarCrmQuery(db) Logger.debug('Users list: Users->{}'.format(items.keys())) Logger.debug('Users list: fields->{} '.format(['user_name', 'id'])) qry.cursor().select_(['user_name', 'id']).from_('Users').where_().in_('users.user_name', items.keys(), esq=True).end_() r_dict = {} for h in qry.fetch_all(): r_dict = {k: v for k, v in h} Logger.debug('Users list: Returned->#{}'.format(len(r_dict))) Logger.debug('Users list: Output:->{}'.format(r_dict)) return r_dict
def do_run(config): observers = (sqlobservers.UserListObserver(), sqlobservers.TimesheetsObserver()) disp = Dispatcher() users = lazycollect.UserLazyList(observers[0].pre_load()) disp.register_event(users, 'updated') disp.subscribe(observers[0], users, 'updated') ts = lazycollect.TimesheetsLazyList({k: None for k in users.values()}) ts.year = config['year'] ts.month = config['month'] disp.register_event(ts, 'updated') disp.subscribe(observers[1], ts, 'updated') Logger.info('Year:{} and month:{}'.format(ts.year, ts.month)) u = {v: k for k, v in users.iteritems()} logstr = '' for uid, time_sheets in ts.iteritems(): hrs = 0 if time_sheets: hrs = sum(map(float, [s[1] for s in time_sheets])) logstr += "[{}: {} hrs]".format(u[uid], hrs) Logger.info('Timesheets: {}'.format(logstr)) for o in observers: o.flush() Logger.info('Done')
def do_inject(): """ :return: Nothing """ mysql_conf = {'user': config['mysql.user'], 'passwd': config['mysql.password'], 'db': config['mysql.db'], 'host': config['mysql.host'], 'charset': 'utf8'} sql_db = MySQLdb.connect(**mysql_conf) jira_opts = {'server': config['jira.url']} jira_auth = (config['jira.user'], config['jira.password']) jira = JIRA(options=jira_opts, basic_auth=jira_auth) # injecting mysql and jira def my_config(binder): binder.bind(SQLDb, sql_db) binder.bind(Jira, jira) Logger.debug("Injection complete") inject.configure(my_config)
def flush(self): if self._updated: Logger.debug("In UserList flush") q_sql = "update users set sugar_id = %s where sugar_uname = %s" c = self._db.cursor() q_params = [(v, k) for k, v in self._updated] Logger.debug("In UserList flush->params{}".format(q_params)) c.executemany(q_sql, q_params) self._db.commit() Logger.debug("Commit complete") self._updated = []
def report_worker(user): """ :param user: jira user name for timesheets retrieval :return: Dict {user_name: [array of timesheets]} """ def get_dates(): """ :return: First and last day of a month for reporting """ import datetime import calendar year = config['year'] month = config['month'] first = datetime.datetime(year=year, month=month, day=1) last = datetime.datetime(year=year, month=month, day=calendar.monthrange(year, month)[1]) return tuple([first, last]) def jdate2pydate(date_string): """ Converts JIRA date string to Python simple date value :param date_string: JIRA string date :return: Simple Python date without time zone """ import iso8601 import datetime complex_date = iso8601.parse_date(date_string) simple_date = datetime.datetime(year=complex_date.year, month=complex_date.month, day=complex_date.day) return simple_date #jira = inject.instance(Jira) jira = get_jira() start_date, finish_date = get_dates() Logger.debug("Querying from {} to {}".format(start_date, finish_date)) qry = 'key in workedIssues("{start}", "{finish}", "{user}")'.format(start=start_date.strftime("%Y/%m/%d"), finish=finish_date.strftime("%Y/%m/%d"), user=user) Logger.debug("Query is {}".format(qry)) issues = jira.search_issues(jql_str=qry, maxResults=1000) ts = [] for i in issues: for w in [w for w in jira.worklogs(i.key) if w.author.name == user]: d_created = jdate2pydate(w.started) if start_date <= d_created <= finish_date: # it might happens too! ts.append([w.id, w.comment, float(w.timeSpentSeconds) / 3600, d_created]) Logger.debug("Timesheets for user {} are {}".format(user, ts)) return {user: ts}
def set_timesheets(ts): """ :param ts: timesheets for a user :return: nothing """ db = inject.instance(SQLDb) flds = ['userid', 'id', 'description', 'time_spent', 'activity_date', 'source'] Logger.debug("Updating with fields {}".format(flds)) q_sql = 'insert into timesheets( ' + ','.join(flds) + ') values (' + ','.join( ['%s'] * (len(flds))) + ')' q_sql += ' ON DUPLICATE KEY UPDATE %s = VALUES(%s) ' % ('time_spent', 'time_spent') Logger.debug("Update query: {}".format(q_sql)) c = db.cursor() c.executemany(q_sql, ts) db.commit() Logger.debug("Update completed")
if start_date <= d_created <= finish_date: # it might happens too! ts.append([w.id, w.comment, float(w.timeSpentSeconds) / 3600, d_created]) Logger.debug("Timesheets for user {} are {}".format(user, ts)) return {user: ts} if __name__ == "__main__": config = do_parse(sys.argv[1:]) from primitives.logger import set_logging set_logging(config) do_inject() users = get_users() emails = users.keys() max_threads = int(config.get("jira.max_threads", 5)) Logger.info("Starting {} threads {}".format(max_threads, time.clock())) pool = Pool(max_threads) results = pool.map(report_worker, users) pool.close() pool.join() Logger.info("Stopping {}".format(time.clock())) timesheets = [] for r in results: for u, ts in r.iteritems(): Logger.debug("{}->{} hrs".format(u, sum([t[2] for t in ts]) / 3600)) Logger.debug("User: {} and timesheet {}".format(users[u], ts)) for t in ts: timesheets.append([users[u]] + t + ['JIRA']) set_timesheets(timesheets) Logger.debug("All done")
def __batch_update__(self, items): keys = [key for key, value in items.iteritems() if value != []] # special case for uniformity if keys: Logger.debug('TS List: Keys to update->#{}'.format(keys)) db = inject.instance(SugarDb) qry = ChainSugarCrmQuery(db) dates = self.__get_dates() Logger.debug('TS List: Dates to fetch->{}'.format(dates)) Logger.debug('TS List: Query params->fields:{}'.format(self._fields)) Logger.debug('TS List: Query params->created_by:{}'.format(keys)) Logger.debug('TS List: Query params->activity date between:{}'.format(dates)) qry.cursor().select_(self._fields).from_('ps_Timesheets').where_( ).in_('ps_timesheets.created_by', keys, esq=True).and_().bw_('ps_timesheets.activity_date', dates, esq=True).end_() # ts = collections.defaultdict(list) ts = {k: [] for k in keys} for entries in qry.fetch_all(): for entry in entries: ts[entry[0]].append(entry[1:]) Logger.debug('TS List: Query returned->{}'.format(ts)) return ts else: Logger.debug('TS List: No items to update, passing') return items
def flush(self, *args, **kwargs): if self._updated: Logger.debug("Inside timesheets flush") users = self._get_users() Logger.debug("Inside timesheets flush. Users->{}".format(users)) fields = kwargs.get('fields', ['userid', 'created_by', 'activity_date', 'time_spent', 'description', 'id', 'name']) Logger.debug("Inside timesheets flush. Fields->{}".format(fields)) if not 'userid' in fields: fields = ['userid'] + fields q_sql = 'insert into timesheets( ' + ','.join(fields) + ') values (' + ','.join( ['%s'] * (len(fields))) + ')' q_sql += ' ON DUPLICATE KEY UPDATE %s = VALUES(%s) ' % ('time_spent', 'time_spent') Logger.debug("Inside timesheets flush. Query->{}".format(q_sql)) c = self._db.cursor() values = [] for uid, timesheets in self._updated: for ts in timesheets: values.append(([users[uid]] + [uid] + ts)) Logger.debug("Inside timesheets flush. Values->{}".format(values)) c.executemany(q_sql, values) self._db.commit() Logger.debug("Commit complete") self._updated = []
def pre_load(self): Logger.debug("In UserList pre-load") q_sql = "select sugar_uname, sugar_id from users" c = self._db.cursor() c.execute(q_sql) return dict(c.fetchall())