def auctions_year(conn, year, in_out): """We draw up an auction report for the year.""" column_inout = get_date_inout(in_out) if not column_inout: show_report( "Invalid parameter '{}' in function 'auctions_year()'.".format( in_out)) return False query = "SELECT CAST(strftime('%m', {0}) as INTEGER) as month, COUNT(), SUM(money) \ FROM auctions \ WHERE val_code = ? AND \ CAST(strftime('%Y', {0}) as INTEGER) = ? \ GROUP BY month \ ORDER BY month ASC;".format(column_inout) show_report("\n= Money {} / Year: {} =".format(in_out.upper(), year)) show_report() for val_code in get_valcode(conn): result = [] data = get_from_db(conn, query, (val_code, year)) for month in range(1, 13): count = 0 money = 0 for row in data: if row[0] == month: _, count, money = row result.append((month, count, money)) show_result('Month', val_code, result)
def update_attendance(*args): """ 每天检查排班表,如果数量不足7天, 则创建接下来一天的排班记录 以此达到效果: 总是能看到从当前时间往后推一周的排班计划 """ logger.info("start update attendance...") today = datetime.date.today() attendances = Attendance.objects.filter(date__gte=today).order_by("date") last = attendances.last() if attendances.count() < get_from_db("SHOW_DUTY_DAYS", int, 7): next_username = get_next_username(last.worker.username) if next_username: next = { "date": last.date + datetime.timedelta(days=1), "worker": UserProfile.objects.filter(username=next_username).first(), "by_id": 1, # 默认为超管 } try: Attendance.objects.create(**next) except Exception as e: logger.exception(e) else: logger.info("update attendance succeed...") return logger.error("got next_username failed...") logger.info("attendance's update not needed...")
def client_moved_points(ws): """ Socket endpoint to receive the moved points from client interaction. If there exsits the old moved points from the previous interaction, we should merge them together. """ while not ws.closed: message = ws.receive() if message: fixed_data = utils.get_from_db(key='fixed_points') fixed_points = json.loads(fixed_data) if fixed_data else {} new_moved_points = json.loads(message) print("New moving points: ", new_moved_points) for p in new_moved_points: pid = p['id'] # for fixed_points dict, key is string pos = [float(p['x']), float(p['y'])] fixed_points[pid] = pos shared_states['interaction_data'].put({ 'fixed_ids': [int(k) for k in fixed_points.keys()], 'fixed_pos': list(fixed_points.values()) }) utils.set_to_db('fixed_points', json.dumps(fixed_points)) utils.continue_server()
def auctions_all(conn): """We get all the records.""" get_all = "SELECT * FROM auctions \ WHERE CAST(strftime('%Y', date_in) as INTEGER) > 2011 \ ORDER BY date_in DESC, auct_num DESC;" for row in get_from_db(conn, get_all): show_report(row)
def run_send_to_client(ws): """ Main loop of the thread that read the subscribed data and turn it into a json object and send back to client. The returned message is a dataframe in `/tsnex/do_embedding` route """ print("[PUBSUB] Thread to read subscribed data is starting ... ") while True: fixed_data = utils.get_from_db(key='fixed_points') fixed_ids = [] if fixed_data: fixed_points = json.loads(fixed_data) fixed_ids = [int(id) for id in fixed_points.keys()] subscribedData = utils.get_subscribed_data() if subscribedData is not None: if not ws.closed: # pause server and wait until client receives new data # if user does not pause client, a `continous` command # will be sent automatically to continue server utils.pause_server() # prepare the `embedding` in subscribedData # do not need to touch the other fields X_embedded = subscribedData['embedding'] zInfo = subscribedData['z_info'] idx = np.argsort(zInfo)[::-1] y = utils.get_y() labels = json.loads(utils.get_from_db(key='labels')) raw_points = [{ 'id': str(i), 'x': float(X_embedded[i][0]), 'y': float(X_embedded[i][1]), 'z': float(zInfo[i]), 'text': labels[i], 'label': str(y[i]), 'fixed': i in fixed_ids } for i in idx] subscribedData['embedding'] = raw_points ws.send(json.dumps(subscribedData)) status = utils.get_server_status(['tick_frequence', 'stop']) if status['stop']: break else: time.sleep(status['tick_frequence'])
def get_schedule_config(suite_id: int) -> tuple: '''gets the configurable fields from a scheduled suite''' query = ''' select active, period from scheduledSuite where suiteid = %s ''' return get_from_db(query, (suite_id))[0] or (False, 0)
def list_tests(suite_id: int) -> tuple: '''uses a suite id to list out all tests in that suite''' query = ''' select testname, testid from tests where suiteid = %s ''' return get_from_db(query, (suite_id, ))
def list_suites() -> tuple: '''list out all the suites in the database''' query = ''' select suitename, suiteid, description, browser, width, height from suites join suite_config using(suiteid) ''' return get_from_db(query)
def get_tests_scheduled_later_than_now() -> tuple: ''' gets tests that are scheduled to run later than now ''' query = ''' select id, suiteid, testid from scheduledTest where nextrun < now() ''' return get_from_db(query)
def get_runs(testid: int) -> tuple: ''' gets all runs using a test id ''' query = ''' select runid, start, end, passed, screenshot_passed from runs where testid = %s ''' return get_from_db(query, (testid))
def get_steps_for_test(testid: int) -> tuple: '''returns all the steps in a test''' query = '''select action, optional, args, screenshot, screenshot_name, threshold from steps left join tests using(testid) where tests.testid=%s order by stepnumber''' return tuple(task(*i) for i in get_from_db(query, (testid)))
def get_most_recent_run_state(testid: int) -> tuple: '''gets the state of the most recent run for a test''' query = ''' select passed, screenshot_passed, start from runs where testid = %s order by runid desc limit 1 ''' return get_from_db(query, (testid))
def get_schedule_config(suite_id: int, test_id: int) -> tuple: ''' gets the configurable fields from a scheduled test ''' query = ''' select period from scheduledTest where suiteid = %s and testid = %s ''' return get_from_db(query, (suite_id, test_id))
def get_settings_for_suite(suiteid: int) -> tuple: '''get the configuration settings for a suite from the database''' query = ''' select browser, width, height from suite_config where suiteid = %s ''' rows = get_from_db(query, (suiteid)) if rows: return rows[0] return None
def authenticate(self, request, username=None, password=None, **kwargs): try: account = UserAccount.objects.get( Q(email=username) | Q(username=username)) except UserAccount.DoesNotExist: pass else: if account.check_password(password) and self.user_can_authenticate( account): if get_from_db("UPGRADING", int, 0): if not account.is_superuser: # 维护期间不允许超管之外的人登录 account.is_staff = False return account
def get_suite(suite_id: int) -> tuple: '''return the suite name and description from the db using the suite id''' query = ''' select suitename, description from suites where suiteid = %s ''' rows = get_from_db(query, (suite_id, )) if rows: return rows[0] return None
def get_steps_for_run(runid: int) -> tuple: ''' gets all steps for a run using a run id ''' query = ''' select action, optional, args, passed, take_screenshot, screenshot_percentage, screenshot_passed, screenshot_name from run_step where runid = %s ''' return get_from_db(query, (runid))
def get_suites_scheduled_later_than_now() -> tuple: ''' gets tests and suites that are scheduled to run later than now ''' query = ''' select id, suiteid from scheduledSuite where nextrun < now() and active ''' return get_from_db(query)
def get_test_count(suite_id: int) -> tuple: '''count the tests in a suite''' query = ''' select count(*) from tests join suites using(suiteid) where suiteid = %s ''' return get_from_db(query, (suite_id))
def get_name_from_id(test_id: int) -> str: '''uses a test id to get the name of a test''' query = ''' select testname from tests where testid = %s ''' rows = get_from_db(query, (test_id, )) if rows: if rows[0]: return rows[0][0] return None
def get_description_from_id(suite_id: int) -> str: '''get the description of the suite using the suite id''' query = ''' select description from suites where suiteid = %s ''' rows = get_from_db(query, (suite_id, )) if rows: if rows[0]: return rows[0][0] return None
def get_name_from_id(suite_id: int) -> str: '''get the name of the suite from the suite id''' query = ''' select suitename from suites where suiteid = %s ''' rows = get_from_db(query, (suite_id, )) if rows: if rows[0]: return rows[0][0] return None
def get_most_recent_run_state(suite_id: int) -> tuple: '''get the most recent run from the most recently ran test''' query = ''' select start from runs join tests using(testid) join suites using(suiteid) where suiteid = %s order by runid desc limit 1 ''' return get_from_db(query, (suite_id))
def insert_data(conn, data_file): """We connect to the database and record data.""" check_data = "SELECT * FROM auctions WHERE auct_num = ? AND date_in = ?;" insert_data = "INSERT INTO auctions (auct_num, date_in, date_out, money, \ percent, val_code, stock_code) \ VALUES (?, ?, ?, ?, ?, ?, ?);" data = get_data(data_file) if data: PRIMARY_KEY = slice(0, 2) for row in data: id_exists = get_from_db(conn, check_data, row[PRIMARY_KEY]) if not id_exists: insert_in_db(conn, insert_data, row) if conn.total_changes: conn.commit() # show_report the result. show_report() show_report("Rows in data: {}".format(len(data))) show_report("Total changes: {}".format(conn.total_changes)) show_report()
def auctions_stats(conn, in_out): """We make a report on all auctions.""" column_inout = get_date_inout(in_out) if not column_inout: show_report( "Invalid parameter '{}' in function 'auctions_stats()'.".format( in_out)) return False # We can't make a year selection "> 2011" in the request, # because then the SUM(money) is erroneously calculated. query = "SELECT CAST(strftime('%Y', {}) as INTEGER) as year, COUNT(), SUM(money) \ FROM auctions \ WHERE val_code = ? \ GROUP BY year \ ORDER BY year ASC;".format(column_inout) show_report("\n=== Money {} ===".format(in_out.upper())) show_report() for val_code in get_valcode(conn): data = get_from_db(conn, query, (val_code, )) result = [row for row in data if row[0] > 2011] show_result('Year', val_code, result)
def get_queryset(self): return Attendance.objects.filter( date__gte=datetime.date.today(), date__lte=datetime.date.today() + datetime.timedelta(days=get_from_db('SHOW_DUTY_DAYS', int, 7)), )
def get_valcode(conn): """Get a dataset valcode: ['UAH', 'USD', 'EUR'].""" get_valcode = "SELECT DISTINCT val_code FROM auctions \ ORDER BY val_code DESC;" return [x[0] for x in get_from_db(conn, get_valcode)]