class CalendarWriter:
def __init__(self):
self._cfg = Config()
self._path = self._cfg.get_value('path', 'path_to_share_folder') + 'calendar/'
self._dbmgr = DBMgr(db='calendar')
df = self._read_file()
self._write_data_to_db(df)
def _read_file(self):
df = pd.read_excel(self._path+'tw_calendar.xlsx')
df = df[['年月日']].sort_values(by='年月日').reset_index(drop=True)
df.columns = ['date']
return df
def _write_data_to_db(self, df):
# 不知道為啥create table & insert要分開來寫才行
sql = " drop table if exists `tw`;"
args = {}
status, row, result = self._dbmgr.insert(sql, args)
sql = " CREATE TABLE `tw`( \
`id` int(5) PRIMARY KEY AUTO_INCREMENT NOT NULL, \
`date` datetime COLLATE utf8mb4_unicode_ci \
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;"
args = {}
status, row, result = self._dbmgr.insert(sql, args)
sql = " INSERT INTO `tw` \
VALUES ('0', %(date)s)"
args = df.to_dict('records')
status, row, result = self._dbmgr.insert(sql, args, multiple=True)
class HostMsgHandler:
def __init__(self):
self._cfg = Config()
self._dbmgr = DBMgr()
self._server_name = getpass.getuser() # 伺服器使用者名稱
self._ip = socket.gethostbyname(socket.gethostname())
self._host_IP = self._cfg.get_value('IP', 'host_IP')
self.client_ID = self._server_name + "_" + self._ip
self._mqtt_account = self._cfg.get_value('MQTT', 'account')
self._mqtt_password = self._cfg.get_value('MQTT', 'password')
def active_mqtt(self):
mqtt_client = self.client_ID + " StatusRespond and FinishTask" # 設定節點名稱
client = mqtt.Client(client_id=mqtt_client)
client.on_connect = self._on_connect
client.on_message = self._on_message
client.username_pw_set(self._mqtt_account, self._mqtt_password)
client.connect(self._host_IP, 1883)
# 開始連線 執行設定的動作和處理重新連線問題
client.loop_start()
def _on_connect(self, client, userdata, flag, rc):
print("Connected with result code {}".format(str(rc)))
# 0: 連接成功
# 1: 協議版本錯誤
# 2: 無效的客戶端標示
# 3: 伺服器無法使用
# 4: 使用者帳號或密碼錯誤
# 5: 未經授權
# 將訂閱主題寫在 on_connect 中，當重新連線時將會重新訂閱
client.subscribe("Analysis/StatusRespond", qos=2)
client.subscribe("Analysis/HealthResponse", qos=2)
client.subscribe("Analysis/FinishTask", qos=2)
def _on_message(self, client, userdata, msg):
if msg.topic == "Analysis/StatusRespond":
self._handle_status_update(client, userdata, msg)
elif msg.topic == "Analysis/HealthResponse":
self._handle_health_update(client, userdata, msg)
elif msg.topic == "Analysis/FinishTask":
self._handle_finish_task(client, userdata, msg)
# (Publish) 發送節點狀態確認訊息
def publish_status_check(self):
mqtt_client = self.client_ID + " StatusCheck" # 設定節點名稱
# 送出狀態確認訊息，
payload = {"message": "StatusCheck"}
payload = json.dumps(payload)
publish.single(qos=2,
keepalive=60,
payload=payload,
client_id=mqtt_client,
topic="Analysis/StatusCheck",
hostname=self._host_IP,
auth={
'username': self._mqtt_account,
'password': self._mqtt_password
})
print("[HostMsgHandler] Host Status Check.....")
# (CallBack) 處理狀態確認任務完成訊息
# input: client : 發送訊息的節點ID
# userdata : 資料型態
# msg : 訊息內容
def _handle_status_update(self, client, userdata, msg):
node_msg = self._phase_mqtt_msg(msg)
print("[HostMsgHandler] get {} Status Check Respond".format(
node_msg['node_name']))
current_time = str(datetime.now())
sql = "SELECT * FROM `node` WHERE name = %(name)s"
args = {"name": str(node_msg['node_name'])}
status, row, result = self._dbmgr.query(sql, args)
# 新增節點
if row == 0:
sql = " INSERT INTO `node` \
VALUES ('0', %(name)s, %(cpu_status)s, %(core_num)s, %(health)s, %(health_time)s)"
args = {
"name": str(node_msg['node_name']),
"cpu_status": str(node_msg['node_cpu_status']),
"core_num": str(node_msg['node_core_number']),
"health": '0',
"health_time": current_time
}
status, row, result = self._dbmgr.insert(sql, args)
print("[HostMsgHandler] add a new node: {}".format(
node_msg['node_name']))
# 更新節點
else:
sql = " UPDATE `node` \
SET `cpu_status`=%(cpu_status)s, `core_num`=%(core_num)s, `health`=%(health)s, \
`health_time`=%(health_time)s \
WHERE `name`=%(name)s"
args = {
"name": str(node_msg['node_name']),
"cpu_status": str(node_msg['node_cpu_status']),
"core_num": str(node_msg['node_core_number']),
"health": '0',
"health_time": current_time
}
status, row, result = self._dbmgr.update(sql, args)
print("[HostMsgHandler] update node: {}".format(
node_msg['node_name']))
# (Publish) 發送節點健康狀態確認訊息
def publish_health_check(self):
mqtt_client = self.client_ID + " HealthCheck" # 設定節點名稱
# 送出狀態確認訊息，
payload = {"message": "HealthCheck"}
payload = json.dumps(payload)
publish.single(qos=2,
keepalive=60,
payload=payload,
client_id=mqtt_client,
topic="Analysis/HealthCheck",
hostname=self._host_IP,
auth={
'username': self._mqtt_account,
'password': self._mqtt_password
})
print("[HostMsgHandler] Host Health Check.....")
# (CallBack) 處理健康狀態確認任務完成訊息
# input: client : 發送訊息的節點ID
# userdata : 資料型態
# msg : 訊息內容
def _handle_health_update(self, client, userdata, msg):
node_msg = self._phase_mqtt_msg(msg)
current_time = str(datetime.now())
sql = "SELECT * FROM `node` WHERE name = %(name)s"
args = {"name": str(node_msg['node_name'])}
status, row, result = self._dbmgr.query(sql, args)
# 新增節點
if row == 0:
sql = " INSERT INTO `node` \
VALUES ('0', %(name)s, %(cpu_status)s, %(core_num)s, %(health)s, %(health_time)s)"
args = {
"name": str(node_msg['node_name']),
"cpu_status": str(node_msg['node_cpu_status']),
"core_num": str(node_msg['node_core_number']),
"health": '1',
"health_time": current_time
}
status, row, result = self._dbmgr.insert(sql, args)
# 更新節點
else:
sql = " UPDATE `node` \
SET `cpu_status`=%(cpu_status)s, `core_num`=%(core_num)s, `health`=%(health)s, \
`health_time`=%(health_time)s \
WHERE `name`=%(name)s"
args = {
"name": str(node_msg['node_name']),
"cpu_status": str(node_msg['node_cpu_status']),
"core_num": str(node_msg['node_core_number']),
"health": '1',
"health_time": current_time
}
status, row, result = self._dbmgr.update(sql, args)
def publish_processing_task(self, node_name, task_id, task_list):
mqtt_client = self.client_ID + "FactorAnalysisTaskPublish" # 設定節點名稱
payload = {
'owner': node_name, # 負責任務之節點ID
'task_list': task_list, # 任務細節編號
}
payload = json.dumps(payload)
publish.single(qos=2,
keepalive=60,
payload=payload,
client_id=mqtt_client,
topic="Analysis/FactorAnalysisTask",
hostname=self._host_IP,
auth={
'username': self._mqtt_account,
'password': self._mqtt_password
})
print("[HostMsgHandler] send Task...", "Node: ", node_name,
" taskID: ", task_id, " task_list:", task_list)
def _handle_finish_task(self, client, userdata, msg):
# status
# 0 - undo
# 1 - success
# 2 - error
# MQTT CallBack參數取出
node_msg = self._phase_mqtt_msg(msg)
current_time = str(datetime.now())
sql = " UPDATE `task_status` \
SET `finish_time`=%(finish_time)s, `owner`=%(owner)s, `status`=%(status)s \
WHERE `task_status_id`=%(task_status_id)s"
args = {
"finish_time": current_time,
"owner": str(node_msg['node_name']),
"status": str(node_msg['status']),
"task_status_id": str(node_msg['task_status_id']),
}
status, row, result = self._dbmgr.update(sql, args)
print("[HostMsgHandler] {} Node is Finsh {} has been updated to DB".
format(node_msg['node_name'], node_msg['task_status_id']))
def _phase_mqtt_msg(self, msg):
node_msg = str(msg.payload, encoding="utf-8")
node_msg = json.loads(node_msg)
return node_msg
class FactorAnalysisHandler:
def __init__(self):
self._dbmgr = DBMgr()
self._general = General()
def add_task_to_db(self, request):
current_time = str(datetime.now())
sql = "INSERT INTO `task` \
VALUES ('0', %(factor_list)s, %(strategy_list)s, %(window_list)s, %(method_list)s, \
%(group_list)s, %(position_list)s, %(begin_time)s)"
args = {
"factor_list":
self._general.factor_list_to_string(request['factor_list']),
"strategy_list":
self._general.list_to_string(request['strategy_list']),
"window_list":
self._general.list_to_string(request['window_list']),
"method_list":
self._general.list_to_string(request['method_list']),
"group_list":
self._general.list_to_string(request['group_list']),
"position_list":
self._general.list_to_string(request['position_list']),
"begin_time":
str(current_time),
}
status, row, result = self._dbmgr.insert(sql, args)
task_id = self.get_last_task_id(current_time)
return task_id
def add_task_detail_to_db(self, task_id, combination):
task_result = self.get_task_by_id(task_id)
args = []
for task_list in combination:
factor = task_list[0]
strategy = task_list[1]
window = task_list[2]
method = task_list[3]
group = task_list[4]
position = task_list[5]
args.append({
"task_id": str(task_id),
"factor": self._general.list_to_string(factor),
"strategy": str(strategy),
"window": str(window),
"method": str(method),
"group": str(group),
"position": str(position),
"finish_time": str(''),
"owner": str(''),
"status": str('0'),
})
sql = " INSERT INTO `task_status` \
VALUES ('0', %(task_id)s, %(factor)s, %(strategy)s, %(window)s, %(method)s, \
%(group)s, %(position)s, %(finish_time)s, %(owner)s, %(status)s)"
status, row, result = self._dbmgr.insert(sql, args, multiple=True)
def check_unfinished_task(self):
sql = "SELECT `task_status_id`, `task_id` FROM `task_status` WHERE status=%(status)s"
args = {"status": str('0')}
status, row, result = self._dbmgr.query(sql, args)
task_list = []
# 表示無未完成任務
if len(result) == 0:
status = False
task_id = -1
# 有未完成任務
else:
status = True
task_id = result[0]['task_id']
for sub_task in result:
task_list.append(sub_task['task_status_id'])
return status, task_id, task_list
def check_exist_task(self, request):
sql = " SELECT `task_id` \
FROM `task` \
WHERE `factor_list` = %(factor_list)s AND \
`strategy_list` = %(strategy_list)s AND \
`window_list` = %(window_list)s AND \
`method_list` = %(method_list)s AND \
`group_list` = %(group_list)s AND \
`position_list` = %(position_list)s"
args = {
"factor_list":
self._general.factor_list_to_string(request['factor_list']),
"strategy_list":
self._general.list_to_string(request['strategy_list']),
"window_list":
self._general.list_to_string(request['window_list']),
"method_list":
self._general.list_to_string(request['method_list']),
"group_list":
self._general.list_to_string(request['group_list']),
"position_list":
self._general.list_to_string(request['position_list']),
}
status, row, data = self._dbmgr.query(sql, args, fetch='one')
if status and data and len(data) == 1:
return True, data['task_id']
else:
return False, -1
def get_request(self, task_id):
task_info = {}
plateau_info = {}
# 取回任務清單
sql = "SELECT * FROM `task` WHERE `task_id`=%(task_id)s"
args = {"task_id": str(task_id)}
status, row, task_info = self._dbmgr.query(sql, args, fetch='one')
# 該任務不存在
if row == 0:
print('[FactorAnalysisHandler] 該任務編號 {} 不存在！'.format(task_id))
return {
'task_id': task_id,
'factor_list': [],
'strategy_list': [],
'window_list': [],
'method_list': [],
'group_list': [],
'position_list': [],
}
else:
return {
'task_id':
task_id,
'factor_list':
self._general.factor_string_to_list(task_info['factor_list']),
'strategy_list':
self._general.string_to_list(task_info['strategy_list']),
'window_list':
self._general.string_to_list(task_info['window_list']),
'method_list':
self._general.string_to_list(task_info['method_list']),
'group_list':
self._general.string_to_list(task_info['group_list']),
'position_list':
self._general.string_to_list(task_info['position_list']),
}
def get_task_by_id(self, task_id):
sql = "SELECT * FROM `task` WHERE task_id = %(task_id)s"
args = {"task_id": str(task_id)}
status, row, result = self._dbmgr.query(sql, args, fetch='one')
return result
def get_last_task_id(self, curr_time):
sql = "SELECT * FROM `task` WHERE begin_time = %(begin_time)s"
args = {"begin_time": str(curr_time)}
status, row, result = self._dbmgr.query(sql, args, fetch='one')
return result['task_id']
def get_all_task_list_by_status(self, task_id, status=0):
sql = "SELECT `task_status_id` FROM `task_status` WHERE status=%(status)s and task_id=%(task_id)s"
args = {"status": str(status), "task_id": str(task_id)}
sql_status, row, result = self._dbmgr.query(sql, args)
task_list = []
for sub_task in result:
task_list.append(sub_task['task_status_id'])
return task_list
def get_all_task_list(self, task_id):
sql = "SELECT `task_status_id` FROM `task_status` WHERE task_id=%(task_id)s"
args = {"task_id": str(task_id)}
status, row, result = self._dbmgr.query(sql, args)
task_list = []
for sub_task in result:
task_list.append(sub_task['task_status_id'])
return task_list
def check_node_have_task(self, node):
# 檢查這個 node 目前是否有未完成的任務
sql = " SELECT * \
FROM `task_status` \
WHERE `owner` = %(node)s AND `status` = 0"
args = {'node': node}
status, row, result = self._dbmgr.query(sql, args)
if status and row == 0:
return False
else:
return True
def update_task_owner(self, owner, task_list):
for task in task_list:
sql = " UPDATE `task_status` \
SET `owner`=%(owner)s \
WHERE `task_status_id` = %(task_status_id)s"
args = {"owner": str(owner), "task_status_id": str(task)}
status, row, result = self._dbmgr.update(sql, args)