class WorkSpams():
''''''
def __init__(self, role='master'):
self.role = role
def __enter__(self):
self.con = Connect(role=self.role)
self.m = self.con.open()
return self
def __exit__(self, exc_type, exc_value, exc_tb):
if exc_type is None:
self.con.close()
else:
app.logger.warning('WorkSpams mysql connection closing is failed')
return False
def create(self):
'''
'''
query = ('''
INSERT
''')
self.m.execute(query)
def get(self):
'''
'''
pass
def list(self):
'''
'''
pass
def edit(self):
'''
'''
pass
def get_ping(self):
# Connection with MYSQL Master
mysql_res_m_con = Mysql_con(role='master')
mysql_res_m = mysql_res_m_con.is_connect()
mysql_res_m_con.close()
# Connection with MYSQL Slave
mysql_res_s_con = Mysql_con(role='slave')
mysql_res_s = mysql_res_s_con.is_connect()
mysql_res_s_con.close()
# Connection with Redis API Master
redis_res_m = Redis_con(host='api', role='master').is_connect()
# Connection with Redis API Slave
redis_res_s = Redis_con(host='api', role='slave').is_connect()
# Connection with Redis PubSub
redis_res_pubsub_s = Redis_con(host='pubsub').is_connect()
if os.getenv('ENVIRONMENT') != 'development':
# Connection with Chatwork
chatwork_res = Chatwork().is_connect()
if not chatwork_res:
return abort(500, 'Chatwork Connection Error')
if not mysql_res_m:
return abort(500, 'MYSQL Master Connection Error')
if not mysql_res_s:
return abort(500, 'MYSQL Slave Connection Error')
if not redis_res_m:
return abort(500, 'Redis API Master Connection Error')
if not redis_res_s:
return abort(500, 'Redis API Slave Connection Error')
if not redis_res_pubsub_s:
return abort(500, 'Redis PubSub Master Connection Error')
return jsonify({'ping': 'pong'})
def __enter__(self):
self.con = Connect(role=self.role)
self.m = self.con.open()
return self
class MessageSpams():
''''''
def __init__(self, role='master'):
self.role = role
def __enter__(self):
self.con = Connect(role=self.role)
self.m = self.con.open()
return self
def __exit__(self, exc_type, exc_value, exc_tb):
if exc_type is None:
self.con.close()
else:
app.logger.warning(
'MessageSpams mysql connection closing is failed'
)
return False
def _get_spam_status(self, ms):
'''unit testしやすいようにメソッド化した'''
if ms['feedback_from_admin'] == 1:
spam = 2
elif ms['feedback_from_admin'] == 2:
spam = 0
elif ms['feedback_from_user'] == 1:
spam = 1
elif ms['predict'] == 1:
spam = 1
else:
spam = 0
return spam
def parse(self, mss):
'''
本来はここに書くような内容のメソッドではないと思うが、とりあえずここで。
APIで返す用にパースする。
SQLの結果をそのまま使いたいことも将来的にはあるだろうから、別メソッドとして分離し、
呼び出し側でparseを呼び出すスタイルにする
@param list of dict mss
@return list
'''
items = []
# 空だった場合
if not mss:
return items
# byte型をそのまま返すことはできないので変換
# spam項目を追加
for ms in mss:
# 非常に稀にdescriptionが空のケースが存在する
if ms:
description = re.sub(
'\s', '', ms['description'].decode('utf-8'))
else:
description = ms['description']
items.append({
ms['message_id']: {
'id': ms['id'],
'created': ms['created'].strftime("%Y-%m-%d %H:%M:%S"),
'board_id': ms['board_id'],
'message_id': ms['message_id'],
'description': description,
'send_user_id': ms['send_user_id'],
'send_user_nickname':
ms['send_user_nickname'].decode('utf-8'),
'send_user_status': ms['send_user_status'].decode('utf-8'),
'spam': self._get_spam_status(ms)
}
})
return items
def create(self, args):
'''
@param tuple args
@return int insertされたレコードのID
'''
query = ('''
INSERT INTO message_spams
(created, modified, board_id, message_id, score, predict, biz_filter)
VALUES
(NOW(), NOW(), {0}, {1}, {2}, {3}, '{4}')
'''.format(args[0], args[1], args[2], args[3], args[4]))
self.m.execute(query)
self.con.commit()
return self.m.lastrowid
def list_with_board_id(self, board_id):
'''
特定のboard_idの全てのmessagesを全て取得する
message_spamsとleft joinする。
@param int board_id
@return list of dict
'''
query = ('''
SELECT
MsgSpam.id,
Message.created,
Message.board_id,
Board.title,
Message.id as message_id,
Message.description,
Message.user_id as send_user_id,
User.nickname as send_user_nickname,
User.status as send_user_status,
MsgSpam.predict,
MsgSpam.feedback_from_admin,
MsgSpam.feedback_from_user
FROM messages as Message
LEFT JOIN message_spams as MsgSpam on Message.id = MsgSpam.message_id
INNER JOIN users as User on User.id = Message.user_id
INNER JOIN boards as Board on Board.id = MsgSpam.board_id
WHERE
Message.board_id = {0}
'''.format(board_id))
self.m.execute(query)
return self.m.fetchall()
def list_for_admin_page(self, page=1):
'''
@param int page
@return list of dict
e.g. [{'id': 8,...},...])
'''
# オフセット値 = (ページ番号 - 1) * 1ページあたりの表示件数
offset = (page - 1) * 50
query = ('''
SELECT
MsgSpam.id,
MsgSpam.created,
MsgSpam.board_id,
Board.title,
MsgSpam.message_id,
Message.description,
Message.user_id as send_user_id,
User.nickname as send_user_nickname,
User.status as send_user_status,
MsgSpam.predict,
MsgSpam.feedback_from_admin,
MsgSpam.feedback_from_user
FROM message_spams as MsgSpam
INNER JOIN messages as Message on Message.id = MsgSpam.message_id
INNER JOIN users as User on User.id = Message.user_id
INNER JOIN boards as Board on Board.id = MsgSpam.board_id
WHERE MsgSpam.feedback_from_admin = 0
ORDER BY id DESC
LIMIT 50 OFFSET {0}
'''.format(offset))
self.m.execute(query)
return self.m.fetchall()
def count(self):
'''
API: list messagesのレスポンスで使用する
@return int
'''
query = ('''
SELECT
count(*) as count
FROM message_spams as MsgSpam
WHERE
MsgSpam.feedback_from_admin = 0
''')
self.m.execute(query)
res = self.m.fetchone()
return res['count']
def get_for_report(self):
'''
blacked判定されるuserは1日遅れでもいるだろうから、blacked判定が確定されるのが、
1日前だと仮定して、1日前のデータをレポーティングする
2日前の17:00:00 ~ 昨日の17:00:00 のデータを取得する
'''
now = datetime.now(pytz.timezone('Asia/Tokyo'))
# 2日前の17:00:00
five_two_days_ago = (now - timedelta(days=2)).replace(
hour=17, minute=00, second=00, microsecond=00
).strftime("%Y-%m-%d %H:%M:%S")
# 昨日の17:00:00
five_yesterday = (now - timedelta(days=1)).replace(
hour=17, minute=00, second=00, microsecond=00
).strftime("%Y-%m-%d %H:%M:%S")
query = ('''
SELECT
MsgSpam.id,
MsgSpam.created,
MsgSpam.board_id,
MsgSpam.message_id,
MsgSpam.score,
Message.description,
Message.user_id as send_user_id,
User.nickname as send_user_nickname,
User.status as send_user_status,
MsgSpam.predict,
MsgSpam.feedback_from_admin,
MsgSpam.feedback_from_user
FROM message_spams as MsgSpam
INNER JOIN messages as Message on Message.id = MsgSpam.message_id
INNER JOIN users as User on User.id = Message.user_id
WHERE
MsgSpam.created between '{0}' AND '{1}'
'''.format(five_two_days_ago, five_yesterday))
self.m.execute(query)
return self.m.fetchall()
def is_exist(self, message_spam_id):
'''
@param int message_spam_id
@return bool
'''
query = ('''
SELECT
MsgSpam.id
FROM message_spams as MsgSpam
WHERE
MsgSpam.id = {0}
'''.format(message_spam_id))
self.m.execute(query)
res = self.m.fetchone()
if res:
return True
else:
return False
def get_detail(self, message_spam_id):
'''
そもそもmessage_spamsテーブルには送信者のmessageしか保存されない
@param int message_spam_id
@return dict
'''
query = ('''
SELECT
MsgSpam.id,
MsgSpam.created,
MsgSpam.board_id,
Board.title as board_title,
MsgSpam.message_id,
MsgSpam.predict,
MsgSpam.feedback_from_admin,
MsgSpam.feedback_from_user,
MsgSpam.biz_filter,
Message.description,
Message.user_id as send_user_id,
User.nickname as send_user_nickname,
User.status as send_user_status
FROM message_spams as MsgSpam
INNER JOIN messages as Message on Message.id = MsgSpam.message_id
INNER JOIN users as User on User.id = Message.user_id
INNER JOIN boards as Board on Board.id = MsgSpam.board_id
WHERE
MsgSpam.id = {0}
'''.format(message_spam_id))
self.m.execute(query)
return self.m.fetchone()
def get_board_users(self, board_id):
'''
@param int board_id
@return list of dict
'''
query = ('''
SELECT
BoardUser.user_id
FROM board_users as BoardUser
WHERE
BoardUser.board_id = {0}
'''.format(board_id))
self.m.execute(query)
return self.m.fetchall()
def edit(self, args):
'''
v1.0の時点では、feedback_from_adminのみ許可している
@param tuple args
@return int insertされたレコードのID
'''
query = ('''
UPDATE message_spams SET
feedback_from_admin = {0},
modified = NOW()
WHERE id = {1}
'''.format(args[0], args[1]))
self.m.execute(query)
self.con.commit()
return self.m.lastrowid
class Urls():
'''
This class is need to be called with context manager
e.g.
with Urls() as u:
is_exist = tu.is_exist('https://www.example.com')
if is_exist:
pass
'''
def __init__(self, role='master'):
self.role = role
def __enter__(self):
self.con = Connect(role=self.role)
self.m = self.con.open()
return self
def __exit__(self, exc_type, exc_value, exc_tb):
'''
意図的にcloseしないとすぐこのエラーが発生する。
ReferenceError: weakly-referenced object no longer exists
'''
if exc_type is None:
self.con.close()
else:
app.logger.warning('Urls mysql connection closing is failed')
return False
def add(self, urls):
'''
:param tuple urls
:return int ID was inserted
'''
query = ('''
INSERT INTO urls (
datetime,
scheme,
netloc,
host,
domain,
path,
pwa,
urls_external
) VALUES (
%s, %s, %s, %s, %s, %s, %s, %s)
''')
params = (
urls['datetime'],
urls['scheme'],
urls['netloc'],
urls['host'],
urls['domain'],
urls['path'],
urls['pwa'],
urls['urls_external'],
)
self.m.execute(query, params)
self.con.commit()
return self.m.lastrowid
def is_exist(self, netloc):
'''
同じnetlocがすでに保存済みかチェックする
存在しなければ0,存在すれば1を返す
:param str netloc
:return int
'''
query = '''
SELECT
Url.id
FROM urls AS Url
WHERE
Url.netloc = %s
'''
self.m.execute(query, (netloc, ))
return self.m.rowcount
def is_exist_strictly(self, netloc, domain):
'''
同じnetlocがすでに保存済みかチェックする
存在しなければ0,存在すれば1を返す
:param str netloc
:return bool
'''
query = '''
SELECT
Url.id
FROM urls AS Url
WHERE
Url.netloc = %s
'''
self.m.execute(query, (netloc, ))
if self.m.rowcount >= 1:
return True
query = '''
SELECT
Url.id
FROM urls AS Url
WHERE
Url.domain = %s
'''
res = self.m.execute(query, (domain, ))
if self.m.rowcount > 12:
return True
return False
def get_pwas(self):
'''
pwa対応のページを全て取得する
'''
query = '''
SELECT
Url.id,
Url.netloc
FROM urls AS Url
WHERE
Url.pwa = 1
'''
self.m.execute(query)
return self.m.fetchall()
class Messages():
'''
コンテキストマネージャで呼び出すこと。
'''
def __init__(self, role='master'):
self.role = role
def __enter__(self):
self.con = Connect(role=self.role)
self.m = self.con.open()
return self
def __exit__(self, exc_type, exc_value, exc_tb):
'''
意図的にcloseしてあげないとすぐこのエラーが発生する。
ReferenceError: weakly-referenced object no longer exists
'''
if exc_type is None:
self.con.close()
else:
app.logger.warning('Message mysql connection closing is failed')
return False
def get_for_local(self, after_this_id):
'''
指定日時以降に作成された送信者のmessagesを取得
@param int after_this_id
@return list
'''
if not after_this_id:
app.logger.debug('after_this_id を手動でRedisにsetすること')
return
query = ('''
SELECT
Message.id
FROM messages as Message
WHERE
Message.id > {0}
order by Message.id asc
'''.format(after_this_id))
self.m.execute(query)
return self.m.fetchall()
def get_latest(self):
'''
一番新しいmessageを取得する
restore時に使用する
'''
query = ('''
SELECT
Message.id
FROM messages as Message
order by Message.id desc
limit 1
''')
self.m.execute(query)
return self.m.fetchone()
def get_messages(self, message_id):
'''
messageが属するboardのメッセージを全て取得する
user_id_aとuser_id_bが同じであれば同じユーザが作成したmessageとなる
@param list board_ids
@return list of dict
'''
query = ('''
SELECT
Msg_B.id,
Board.owner_id,
User.nickname,
Message.user_id as user_id_a,
Msg_B.user_id as user_id_b,
Msg_B.board_id,
Msg_B.description
FROM messages as Message
INNER JOIN messages as Msg_B on Message.board_id = Msg_B.board_id
INNER JOIN boards as Board on Board.id = Msg_B.board_id
INNER JOIN users as User on User.id = Msg_B.user_id
WHERE
Msg_B.description != 'send file' AND
Msg_B.description != '' AND
Msg_B.description IS NOT NULL AND
Message.id = {0}
'''.format(message_id))
self.m.execute(query)
return self.m.fetchall()
def get_pos(self, min_datetime=None, max_datetime=None):
'''
blackedユーザのmessagesを取得
2017-09-13時点で約12万件ある
Message.user_id = Board.owner_id を指定することで、
送信者のmessageのみを取得する
@param string max_datetime
@param string min_datetime
@return list
e.g. [{'id': 391778,...
'''
if min_datetime:
min_datetime = min_datetime
else:
# MLMに関して、CCチームが基準を定めて本格対応し始めたのが
# 2017-02-01 00:00:00からなので2017-02-01 00:00:00以降の
# データのみを対象とする
min_datetime = '2017-02-01 00:00:00'
if max_datetime:
max_datetime = max_datetime
else:
# 指定されていなければ現時点をセット
max_datetime = datetime.now(
pytz.timezone('Asia/Tokyo')
).strftime('%Y-%m-%d %H:%M:%S')
query = ('''
SELECT
Message.id,
Message.user_id,
Message.created,
Message.board_id,
Message.description
FROM messages as Message
INNER JOIN users as User on Message.user_id = User.id
INNER JOIN boards as Board on Board.id = Message.board_id
WHERE
User.status = "blacked" AND
Message.user_id = Board.owner_id AND
Message.created between '{0}' AND '{1}' AND
Message.description != 'send file' AND
Message.description != '' AND
Message.description IS NOT NULL
'''.format(min_datetime, max_datetime))
self.m.execute(query)
return self.m.fetchall()
def get_neg(self, min_datetime=None, max_datetime=None):
'''
クライアントユーザの不正ではないmessagesを取得
feedback_countを指定することで違反メッセージを作成するようなユーザではない
であろうことを保証している。min_datetimeはpositiveに合わせる
Message.user_id = Board.owner_id を指定することで、
送信者のmessageのみを取得する
@param string max_datetime
@param string min_datetime
@return list
e.g. [{'id': 391778,...
'''
if min_datetime:
min_datetime = min_datetime
else:
min_datetime = '2017-02-01 00:00:00'
if max_datetime:
max_datetime = max_datetime
else:
# 指定されていなければ現時点をセット
max_datetime = datetime.now(
pytz.timezone('Asia/Tokyo')
).strftime('%Y-%m-%d %H:%M:%S')
query = ('''
SELECT
Message.id,
Message.user_id,
Message.created,
Message.board_id,
Message.description
FROM messages as Message
INNER JOIN users as User on Message.user_id = User.id
INNER JOIN user_profiles as UPro on User.id = UPro.user_id
INNER JOIN boards as Board on Board.id = Message.board_id
WHERE
User.status = "active" AND
User.created between '2015-01-01 00:00:00' AND '2016-12-31 23:59:59' AND
User.deleted = '' AND
UPro.actived > '2017-10-01 00:00:00' AND
UPro.lancers_check = 1 AND
UPro.identification = 1 AND
UPro.phone_check = 'checked' AND
UPro.feedback > 4 AND
UPro.feedback_count > 10 AND
Message.created between '{0}' AND '{1}' AND
Message.description != 'send file' AND
Message.description != '' AND
Message.description IS NOT NULL AND
Message.user_id = Board.owner_id
'''.format(min_datetime, max_datetime))
self.m.execute(query)
return self.m.fetchall()
class Scores():
'''
This class is need to be called with context manager
e.g.
with Urls() as u:
is_exist = tu.is_exist('https://www.example.com')
if is_exist:
pass
'''
def __init__(self, role='master'):
self.role = role
def __enter__(self):
self.con = Connect(role=self.role)
self.m = self.con.open()
return self
def __exit__(self, exc_type, exc_value, exc_tb):
'''
意図的にcloseしないとすぐこのエラーが発生する。
ReferenceError: weakly-referenced object no longer exists
'''
if exc_type is None:
self.con.close()
else:
app.logger.warning('Urls mysql connection closing is failed')
return False
def add(self, urls):
'''
:param dict urls
:return int ID was inserted
'''
query = ('''
INSERT INTO scores (
created_at,
updated_at,
url_id,
is_ssl,
performance,
pwa,
accessibility,
best_practice,
seo
) VALUES (
NOW(), NOW(), %s, %s, %s, %s, %s, %s, %s)
''')
params = (urls['url_id'], urls['is_ssl'], urls['performance'],
urls['pwa'], urls['accessibility'], urls['best_practice'],
urls['seo'])
self.m.execute(query, params)
self.con.commit()
return self.m.lastrowid
def is_exist(self, url_id):
'''
存在しなければ0, 存在すれば1を返す
:param int url_id
:return int
'''
query = '''
SELECT
Score.id
FROM scores AS Score
WHERE
Score.url_id = %s
'''
self.m.execute(query, (url_id, ))
return self.m.rowcount
class Works():
'''
コンテキストマネージャで呼び出すこと。
'''
def __init__(self, role='master'):
self.role = role
def __enter__(self):
self.con = Connect(role=self.role)
self.m = self.con.open()
return self
def __exit__(self, exc_type, exc_value, exc_tb):
'''
意図的にcloseしてあげないとすぐこのエラーが発生する。
ReferenceError: weakly-referenced object no longer exists
'''
if exc_type is None:
self.con.close()
else:
app.logger.warning('Work mysql connection closing is failed')
return False
def get_latest_pjt(self):
'''
一番新しいpjtを取得する
@return dict
'''
query = ('''
SELECT
Work.id
FROM works as Work
WHERE type = 'project'
order by Work.id desc
limit 1
''')
self.m.execute(query)
return self.m.fetchone()
def get_for_local(self, after_this_id):
'''
指定日時以降に作成された送信者のpjtを取得
@param int after_this_id
@return list
'''
if not after_this_id:
app.logger.debug('after_this_id を手動でRedisにsetすること')
return
query = ('''
SELECT
Work.id
FROM works as Work
WHERE
Work.type = 'project' AND
Work.id > {0}
order by Work.id asc
'''.format(after_this_id))
self.m.execute(query)
return self.m.fetchall()
def get_work_user(self, work_id):
'''
work id とそのworkを作ったuserを取得する
@return dic
e.g. {'id': 1594834, ...
'''
query = ('''
SELECT
Work.id,
Work.created,
Work.title,
Work.description,
Work.type,
Work.status as work_status,
User.id as user_id,
User.created as user_created,
User.nickname,
User.status as user_status
FROM works AS Work
INNER JOIN users as User ON Work.user_id = User.id
WHERE Work.id = {0}
'''.format(work_id))
self.m.execute(query)
return self.m.fetchone()
def get(self, min_datetime=None, max_datetime=None):
'''
違反検知の対象となるworkを取得
ランサーストアは対象外とする
Localで動かすバッチで使用する専用のメソッド
@param string min_datetime
@param string max_datetime
@return list
e.g. [{'id': 391778,...
'''
if min_datetime:
min_datetime = min_datetime
else:
# 何も指定されなければ、実行時の00:00:00とする
now = datetime.now(pytz.timezone('Asia/Tokyo'))
min_datetime = now.replace(
hour=0, minute=0, second=0, microsecond=0
)
if max_datetime:
max_datetime = max_datetime
else:
# 指定されていなければ現時点をセット
now = datetime.now(pytz.timezone('Asia/Tokyo'))
max_datetime = now.strftime('%Y-%m-%d %H:%M:%S')
query = ('''
SELECT
Work.id,
Work.created as created,
Work.title,
Work.description,
Work.type,
Work.violation_status,
Work.status as work_status,
User.id as user_id,
User.created as user_created,
User.nickname,
User.status as user_status
FROM works as Work
INNER JOIN users as User on Work.user_id = User.id
LEFT JOIN menus_works as MenuWorks on Work.id = MenuWorks.work_id
where
MenuWorks.id is null AND
Work.type = 'project' AND
Work.created between '{0}' AND '{1}'
'''.format(min_datetime, max_datetime))
self.m.execute(query)
return self.m.fetchall()
def get_works(self, user_id):
'''
過去にworkを作成したか、violationを作ったことがあるかを調べるためのSQL
@return list
e.g. [{'id': 391778,
'created': datetime.datetime(2014, 8, 3, 12, 28, 48),...
'''
query = ('''
SELECT
Work.id,
Work.violation_status
FROM works AS Work
WHERE
Work.user_id = {0}
'''.format(user_id))
self.m.execute(query)
return self.m.fetchall()
def get_pos(self, min_datetime=None, max_datetime=None):
'''
users.statusがblackedのユーザが作成したworkを取得する
blackedというのは、退会処理させられるとセットされる値
重複内容が多いが、2017-09-06時点で19,469件あった
重複を取り除くと 3,595 になった。重複を取り除く計算に丸二日かかった
@param string max_datetime
@param string min_datetime
@return list
e.g. [{'id': 391778,
'created': datetime.datetime(2014, 8, 3, 12, 28, 48),...
'''
if min_datetime:
min_datetime = min_datetime
else:
# 一番古いblackedが作成したレコードは'2014-08-03 12:28:48'から存在するが、
# CCチームが基準を定めて本格対応し始めたのが2017-02-01 00:00:00からなので
# 2017-02-01 00:00:00以降のデータのみを対象とする
min_datetime = '2017-02-01 00:00:00'
if max_datetime:
max_datetime = max_datetime
else:
# 指定されていなければ現時点をセット
now = datetime.now(pytz.timezone('Asia/Tokyo'))
max_datetime = now.strftime('%Y-%m-%d %H:%M:%S')
query = ('''
SELECT
Work.id,
Work.user_id,
Work.created,
Work.title,
Work.description,
Work.type,
Work.violation_status
FROM works as Work
INNER JOIN users on Work.user_id = users.id
WHERE
users.status = "blacked" AND
Work.created between '{0}' AND '{1}' AND
Work.type = 'project'
order by id desc
'''.format(min_datetime, max_datetime))
self.m.execute(query)
return self.m.fetchall()
def get_neg(self, max_datetime=None, min_datetime=None):
'''
クライアントが作った良質なworkをmlmのnegativeとする
@param string max_datetime
@param string min_datetime
@return list
e.g. [{'id': 391778,...
'''
if min_datetime:
min_datetime = min_datetime
else:
min_datetime = '2016-09-01 00:00:00'
if max_datetime:
max_datetime = max_datetime
else:
# 指定されていなければ現時点をセット
now = datetime.now(pytz.timezone('Asia/Tokyo'))
max_datetime = now.strftime('%Y-%m-%d %H:%M:%S')
query = ('''
SELECT
Work.id,
Work.user_id,
Work.created,
Work.title,
Work.description,
Work.type,
Work.violation_status
FROM works as Work
INNER JOIN users as User on Work.user_id = User.id
INNER JOIN user_profiles as UPro on User.id = UPro.user_id
INNER JOIN work_infos as WorkInfo on Work.id = WorkInfo.work_id
LEFT JOIN menus_works as MenuWorks on Work.id = MenuWorks.work_id
WHERE
User.status = "active" AND
User.deleted = '' AND
UPro.purpose = 1 AND
UPro.feedback_count > 20 AND
Work.type = 'project' AND
Work.violation_status = '' AND
Work.status = 'completed' AND
WorkInfo.view_count >= 5 AND
Work.private = 0 AND
Work.expert = 0 AND
Work.award_early = 0 AND
Work.open_level IN (0, 10) AND
MenuWorks.id is null AND
Work.created BETWEEN '{0}' AND '{1}'
'''.format(min_datetime, max_datetime))
self.m.execute(query)
return self.m.fetchall()
def _get_vl_pos(self, min_datetime=None, max_datetime=None, work_type='project'):
'''
violation_statusは99%がotherになっている。
@param string max_datetime
@param string min_datetime
@return list
e.g. [{'id': 391778,...
'''
if min_datetime:
min_datetime = min_datetime
else:
# 一番古いblackedが作成したレコードは'2011-07-20 16:53:38'から存在するが、
# CCチームが基準を定めて本格対応し始めたのが'2016-10-01 00:00:00'0からなので
# '2016-10-01 00:00:00'以降のデータのみを対象とする
min_datetime = '2016-10-01 00:00:00'
if max_datetime:
max_datetime = max_datetime
else:
# 指定されていなければ現時点をセット
now = datetime.now(pytz.timezone('Asia/Tokyo'))
max_datetime = now.strftime('%Y-%m-%d %H:%M:%S')
query = ('''
SELECT
Work.id,
Work.user_id,
Work.created,
Work.title,
Work.description,
Work.type,
Work.violation_status
FROM works AS Work
INNER JOIN users on Work.user_id = users.id
WHERE
users.status != "blacked" AND
Work.violation_status = 'other' AND
Work.created between '{0}' AND '{1}' AND
Work.type = '{2}'
order by id desc
'''.format(min_datetime, max_datetime, work_type))
self.m.execute(query)
return self.m.fetchall()
def _get_vl_neg(self, max_datetime=None, min_datetime=None):
'''
Negative data for violation
@param string max_datetime
@param string min_datetime
@return list
e.g. [{'id': 391778,...
'''
app.config.from_object('config.Whitelists')
if min_datetime:
min_datetime = min_datetime
else:
min_datetime = '2017-08-01 00:00:00'
if max_datetime:
max_datetime = max_datetime
else:
# 指定されていなければ現時点をセット
now = datetime.now(pytz.timezone('Asia/Tokyo'))
max_datetime = now.strftime('%Y-%m-%d %H:%M:%S')
query = ('''
SELECT
Work.id,
Work.user_id,
Work.created,
Work.title,
Work.description,
Work.type,
Work.violation_status
FROM works as Work
INNER JOIN users as User on Work.user_id = User.id
INNER JOIN user_profiles as UPro on User.id = UPro.user_id
INNER JOIN work_infos as WorkInfo on Work.id = WorkInfo.work_id
LEFT JOIN menus_works as MenuWorks on Work.id = MenuWorks.work_id
WHERE
User.status = "active" AND
User.deleted = '' AND
Work.type = 'project' AND
Work.violation_status = '' AND
Work.status = 'completed' AND
Work.created BETWEEN '{0}' AND '{1}' AND
MenuWorks.id is null
'''.format(min_datetime, max_datetime))
self.m.execute(query)
return self.m.fetchall()