import mysql.connector
class MyDB(object):
 """docstring for MyDB"""
 def __init__(self, host, user, passwd , db):
  self.host = host
  self.user = user
  self.passwd = passwd
  self.db = db

  self.connect = None
  self.cursor = None
 def db_connect(self):
  """數據庫連接
  """
  self.connect = mysql.connector.connect(host=self.host, user=self.user, passwd=self.passwd, database=self.db)
  return self
 def db_cursor(self):
  if self.connect is None:
   self.connect = self.db_connect()

  if not self.connect.is_connected():
   self.connect = self.db_connect()
  self.cursor = self.connect.cursor()
  return self
 def get_rows(self , sql):
  """ 查詢數據庫結果
  :param sql: SQL語句
  :param cursor: 數據庫游標
  """
  self.cursor.execute(sql)
  return self.cursor.fetchall()
 def db_execute(self, sql):
  self.cursor.execute(sql)
  self.connect.commit()
 def db_close(self):
  """關閉數據庫連接和游標
  :param connect: 數據庫連接實例
  :param cursor: 數據庫游標
  """
  if self.connect:
   self.connect.close()
  if self.cursor:
   self.cursor.close()

# -*- coding: utf-8 -*-

'''
Copyright (c) 2018, mersap
All rights reserved.

摘 要: pinpoint.py
創 建 者: mersap
創建日期: 2019-01-17
'''

import sys
import requests
import time
import datetime
import json

sys.path.append('../Golf')
import db #db.py

PPURL = "https://pinpoint.*******.com"


From_Time = datetime.datetime.now() + datetime.timedelta(seconds=-60)
To_Time = datetime.datetime.now()
From_TimeStamp = int(time.mktime(From_Time.timetuple()))*1000
To_TimeStamp = int(time.mktime(datetime.datetime.now().timetuple()))*1000


class PinPoint(object):
 """docstring for PinPoint"""
 def __init__(self, db):
  self.db = db
  super(PinPoint, self).__init__()

 """獲取pinpoint中應用"""
 def get_applications(self):
  '''return application dict
  '''
  applicationListUrl = PPURL + "/applications.pinpoint"
  res = requests.get(applicationListUrl)
  if res.status_code != 200:
   print("請求異常,請檢查")
   return
  applicationLists = []
  for app in res.json():
   applicationLists.append(app)
  applicationListDict={}
  applicationListDict["applicationList"] = applicationLists
  return applicationListDict
 def getAgentList(self, appname):
  AgentListUrl = PPURL + "/getAgentList.pinpoint"
  param = {
   'application':appname
  }
  res = requests.get(AgentListUrl, params=param)
  if res.status_code != 200:
   print("請求異常,請檢查")
   return
  return len(res.json().keys()),json.dumps(list(res.json().keys()))
  
 def update_servermap(self, appname , from_time=From_TimeStamp,
       to_time=To_TimeStamp, serviceType='SPRING_BOOT'):
  '''更新app上下游關系
  :param appname: 應用名稱
  :param serviceType: 應用類型
  :param from_time: 起始時間
  :param to_time: 終止時間
  :
  '''
  #https://pinpoint.*****.com/getServerMapData.pinpoint?applicationName=test-app&from=1547721493000&to=1547721553000&callerRange=1&calleeRange=1&serviceTypeName=TOMCAT&_=1547720614229
  param = {
   'applicationName':appname,
   'from':from_time,
   'to':to_time,
   'callerRange':1,
   'calleeRange':1,
   'serviceTypeName':serviceType
  }

  # serverMapUrl = PPURL + "/getServerMapData.pinpoint"
  serverMapUrl = "{}{}".format(PPURL, "/getServerMapData.pinpoint")
  res = requests.get(serverMapUrl, params=param)
  if res.status_code != 200:
   print("請求異常,請檢查")
   return
  update_time = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
  links = res.json()["applicationMapData"]["linkDataArray"]
  for link in links :
   ###排除test的應用
   if link['sourceInfo']['applicationName'].startswith('test'):
    continue
   #應用名稱、應用類型、下游應用名稱、下游應用類型、應用節點數、下游應用節點數、總請求數、 錯誤請求數、慢請求數(本應用到下一個應用的數量)
   application = link['sourceInfo']['applicationName']
   serviceType = link['sourceInfo']['serviceType']
   to_application = link['targetInfo']['applicationName']
   to_serviceType = link['targetInfo']['serviceType']
   agents = len(link.get('fromAgent',' '))
   to_agents = len(link.get('toAgent',' '))
   totalCount = link['totalCount']
   errorCount = link['errorCount']
   slowCount = link['slowCount']

   sql = """
    REPLACE into application_server_map (application, serviceType, 
    agents, to_application, to_serviceType, to_agents, totalCount, 
    errorCount,slowCount, update_time, from_time, to_time) 
    VALUES ("{}", "{}", {}, "{}", "{}", {}, {}, {}, {},"{}","{}",
    "{}")""".format(
     application, serviceType, agents, to_application, 
     to_serviceType, to_agents, totalCount, errorCount,
      slowCount, update_time, From_Time, To_Time)
   self.db.db_execute(sql)

 def update_app(self):
  """更新application
  """
  appdict = self.get_applications()
  apps = appdict.get("applicationList")
  update_time = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
  for app in apps:
   if app['applicationName'].startswith('test'):
    continue
   agents, agentlists = self.getAgentList(app['applicationName'])
   sql = """
    REPLACE into application_list( application_name, 
    service_type, code, agents, agentlists, update_time) 
    VALUES ("{}", "{}", {}, {}, '{}', "{}");""".format(
     app['applicationName'], app['serviceType'], 
     app['code'], agents, agentlists, update_time)
   self.db.db_execute(sql)
  return True

 def update_all_servermaps(self):
  """更新所有應用數
  """
  appdict = self.get_applications()
  apps = appdict.get("applicationList")
  for app in apps:
   self.update_servermap(app['applicationName'], serviceType=app['serviceType'])
  ###刪除7天前數據
  Del_Time = datetime.datetime.now() + datetime.timedelta(days=-7)

  sql = """delete from application_server_map where update_time <= "{}"
  """.format(Del_Time)
  self.db.db_execute(sql)
  return True


def connect_db():
 """ 建立SQL連接
 """
 mydb = db.MyDB(
   host="rm-*****.mysql.rds.aliyuncs.com",
   user="user",
   passwd="passwd",
   db="pinpoint"
   )
 mydb.db_connect()
 mydb.db_cursor()
 return mydb

def main():
 db = connect_db()
 pp = PinPoint(db)
 pp.update_app()
 pp.update_all_servermaps()
 db.db_close()


if __name__ == '__main__':
 main()

CREATE TABLE `application_list` (
 `application_name` varchar(32) NOT NULL,
 `service_type` varchar(32) DEFAULT NULL COMMENT '服務類型',
 `code` int(11) DEFAULT NULL COMMENT '服務類型代碼',
 `agents` int(11) DEFAULT NULL COMMENT 'agent個數',
 `agentlists` varchar(256) DEFAULT NULL COMMENT 'agent list',
 `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
 PRIMARY KEY (`application_name`),
 UNIQUE KEY `Unique_App` (`application_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='pinpoint app list'

CREATE TABLE `application_server_map` (
 `application` varchar(32) NOT NULL COMMENT '應用名稱',
 `serviceType` varchar(8) NOT NULL,
 `agents` int(2) NOT NULL COMMENT 'agent個數',
 `to_application` varchar(32) NOT NULL COMMENT '下游服務名稱',
 `to_serviceType` varchar(32) DEFAULT NULL COMMENT '下游服務類型',
 `to_agents` int(2) DEFAULT NULL COMMENT '下游服務agent數量',
 `totalCount` int(8) DEFAULT NULL COMMENT '總請求數',
 `errorCount` int(8) DEFAULT NULL,
 `slowCount` int(8) DEFAULT NULL,
 `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
 `from_time` datetime DEFAULT NULL,
 `to_time` datetime DEFAULT NULL,
 PRIMARY KEY (`application`,`to_application`),
 UNIQUE KEY `Unique_AppMap` (`application`,`to_application`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='應用鏈路數據'