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)]