def get_user_info(user_id: int, sid: str = None) -> dict:
mined_users = [
row["UserID"] for row in Database.query('SELECT * FROM "MinedUsers"')
]
if user_id not in mined_users:
mine_user_info(user_id, sid)
# get user days count in dataset (except weekends)
total_days = Database.query_row(
'select "TotalDays" from "MinedUsers" where "UserID" = %s',
(user_id, ))['TotalDays']
result = {}
result['user_responsibility'] = __calculate_user_responsibility(user_id)
result['user_sociability'] = __calculate_user_sociability(
user_id, total_days)
result['user_procrastination'] = __calculate_user_procrastination(
user_id, total_days)
result['user_often_leaving'] = __calculate_user_leaving(user_id)
result['user_punctuality'] = __calculate_user_punctuality(user_id)
result['user_leaving_state'] = __calculate_user_leaving_state(user_id)
return result
def __get_user_activity(user_id: int, sid: str, datelist: list = None):
"""
Получает данные активности пользователя и сохраняет их в базу.
:param user_id: идентификатор пользователя, по которому необходимо собрать статистику
:type user_id: int
:param datelist: список дат по которым необходимо собрать статистику, defaults to None
:param datelist: list, optional
"""
insert_query = """
INSERT INTO "UserActivity"("UserID", "Date", "Category", "Useful", "WastedTime")
VALUES (%s, %s, %s, %s, %s);
"""
if not datelist:
datelist = __get_date_range(date.today())
for cur_date in datelist:
rpc_result = SabyInvoker.invoke('Report.PersonProductivityStatistic',
sid,
Фильтр=SabyFormatsBuilder.build_record(
{
"Date": cur_date,
"Person": user_id
}),
Сортировка=None,
Навигация=None,
ДопПоля=[])
person_activities = [
activity for activity in rpc_result['rec'] if activity['Parent@']
] if rpc_result else []
for activity in person_activities:
# write user activity
Database.query(
insert_query,
(user_id, cur_date, activity['Name'], activity['Useful'],
__convert_magic_string(activity['Duration'])))
# get user calls
out_calls = __get_user_out_calls(user_id, cur_date, sid)
Database.query(
insert_query,
(user_id, cur_date, "Звонки СБИС", 0, out_calls['duration']))
def __get_user_neural_data(user_id: int, sid: str):
# get data from last month
last_days_range = __get_date_range(date.today(), 1)
last_count, last_time = __get_user_calls(user_id, last_days_range, sid)
last_overwork = __get_user_overwork(user_id, last_days_range, sid)
# get data from first 2 months
first_start_day = date.today() - relativedelta(months=1)
first_days_range = __get_date_range(first_start_day, 2)
first_count, first_time = __get_user_calls(user_id, first_days_range, sid)
first_overwork = __get_user_overwork(user_id, first_days_range, sid)
Database.query(
"""
INSERT INTO "UsersNeuralData"(
"UserID", "UserFirstCalls", "UserLastCalls", "UserFirstDuration", "UserLastDuration", "UserFirstOverwork", "UserLastOverwork"
)
VALUES (%s, %s, %s, %s, %s, %s, %s);
""", (user_id, first_count, last_count, first_time, last_time,
first_overwork, last_overwork))
def __get_user_location_and_overwork(user_id: int,
sid: str,
datelist: list = None):
"""
Получает данные местонахождения пользователя и сохраняет их в базу.
:param user_id: идентификатор пользователя, по которому необходимо собрать статистику
:type user_id: int
:param datelist: список дат по которым необходимо собрать статистику, defaults to None
:param datelist: list, optional
"""
if not datelist:
datelist = __get_date_range(date.today())
for cur_date in datelist:
rpc_result = __get_user_day_location(user_id, cur_date, sid)
entrances = [
entity for entity in rpc_result['activity_detail']['rec']
if entity['Описание'] == 'entrance'
]
for entrance in entrances:
Database.query(
"""
INSERT INTO "UserLocation" ("UserID", "DateTime", "Status")
VALUES (%s, %s, %s);
""", (user_id, entrance["ВремяНачало"], entrance["Действие"]))
# Смотрим всю активность
activity_summary = rpc_result.get('activity_summary')
# Выбираем необходимое время работы
need_time_str = activity_summary.get('ВремяРаботыГрафик', '00:00:00')
# Выбираем фактически сколько сотрудник отработал
fact_time_str = activity_summary.get('ВремяРаботы', '00:00:00')
Database.query(
"""
INSERT INTO "UserOverwork" ("UserID", "Date", "Overwork")
VALUES (%s, %s, %s);
""", (user_id, cur_date,
__calculate_overwork(need_time_str, fact_time_str)))
def mine_user_info(user_id: int, sid: str):
dates = __get_date_range(date.today())
# Get user location and overwork
__get_user_location_and_overwork(user_id, sid, dates)
# Get user activity
__get_user_activity(user_id, sid, dates)
# Get user plan percent
__get_user_plan_percent(user_id, sid)
# Prepare neural dataset
__get_user_neural_data(user_id, sid)
# Add user id in mined persons
Database.query(
"""
INSERT INTO "MinedUsers"("UserID", "TotalDays")
VALUES (%s, %s);
""", (user_id, len(dates)))
# Apply database changes
Database.commit_changes()
def __get_user_plan_percent(user_id: int, sid: str, month_count: int = 3):
"""
Получает данные по выполнению плана за указанный период и сохраняет их в базу.
:param user_id: идентификатор пользователя, по которому необходимо собрать статистику
:type user_id: int
:param month_count: кол-во месяцев за которое надо собрать статистику, defaults to 3
:type user_id: int, optional
"""
today = date.today()
start_date = date(year=today.year, month=today.month,
day=1) - relativedelta(months=month_count)
end_date = date(year=today.year, month=today.month,
day=1) - relativedelta(days=1)
rpc_result = SabyInvoker.invoke('ПланРабот.ПунктыНаКарточкеСотрудника',
sid,
Фильтр=SabyFormatsBuilder.build_record({
"ДатаНачала":
str(start_date),
"ДатаОкончания":
str(end_date),
"ФильтрПериод":
"Период",
"ЧастноеЛицо":
user_id
}),
Сортировка=None,
Навигация=None,
ДопПоля=[])
percent_structure = rpc_result.get('outcome', None) if rpc_result else None
if percent_structure:
Database.query(
"""
INSERT INTO "UserPlanPercent"("UserID", "PlanPercent")
VALUES (%s, %s);
""", (user_id, percent_structure['Процент']))
def __calculate_user_punctuality(user_id: int) -> int:
# !Settings
# max deviation minutes per day (setting)
MAX_DEVIATION = 30
user_incoming = Database.query(
"""
With "Dates" as (
select "DateTime"::date as "Date"
from "UserLocation"
Where "UserID" = %s and "Status" = 1
group by "Date"
)
select dates."Date", min(main."DateTime"::time) as "ComingTime"
from "UserLocation" as main
inner join "Dates" as dates
on dates."Date" = main."DateTime"::date
group by dates."Date"
order by dates."Date"
""", (user_id, ))
if user_incoming:
# get user incoming times as seconds
timelist = [row['ComingTime'] for row in user_incoming]
timelist = [
timedelta(hours=x.hour, minutes=x.minute,
seconds=x.second).total_seconds() for x in timelist
]
# convert max deviation to seconds
time_max_deviation = timedelta(minutes=MAX_DEVIATION).total_seconds()
# calculate average tive with deviation
k_mean = kmeans(timelist, 1)[0][0]
max_time = k_mean + time_max_deviation
min_time = k_mean - time_max_deviation
# find punctual points
punctual = [
time for time in timelist if time > min_time and time < max_time
]
return int(round(len(punctual) / len(timelist) * 10))
return -1