Автоматизация выгрузки тикетов из Jira
и визуализации отчетов в PowerBI

#Обновление пакетов
sudo apt update
#Установка PostgreSQL
sudo apt install postgresql postgresql-contrib -y
#Установка инструментов PostgreSQL (psql-client), в том числе pg_dump, который нам потребуется для бэкапов.
sudo apt install postgresql-client -y
#Вывод этой команды покажет, запущен ли процесс PostgreSQL на сервере
sudo systemctl status postgresql
#Вывод этой команды покажет версию установленного PostgreSQL
psql --version


#После установки PostgreSQL создается юзер postgres и можно подключиться к базе локально. Вы можете получить доступ к командной строке PostgreSQL, используя этого пользователя:
sudo -i -u postgres
psql
postgres=#

#Начнем с обновления пакетов на сервере
sudo apt update
#Установим последнюю стабильную версию Python и необходимые инструменты
sudo apt install python3 python3-pip -y
#Чтобы убедиться, что Python установлен правильно, проверьте его версию
python3 --version
#Для работы со скриптами, которые будут взаимодействовать с Jira API и PostgreSQL, потребуется установить дополнительные библиотеки. Используем pip для установки необходимых пакетов:
pip3 install requests jira pandas xlsxwriter python-dotenv psycopg2-binary sqlalchemy

mkdir jira_script_folder
cd jira_script_folder
touch config.env

/* Env file for credentials replace x with relevanat things (DB-DB_port is meant to be not enclosed)*/

DB_host="x"  
DB_port= x
DB_user="x"
DB_password="x"
DB_name="x"

jiraToken = "x" #input your credentials here 
server = "x"
accountName = "x"

touch jiraScript.py

# Code for installing dependencies if not found
def install_dependencies():
    required_modules = ['requests', 'jira', 'pandas', 'xlsxwriter', 'datetime', 'subprocess', 'sys', 'base64', 'os', 'sqlalchemy']

    for module in required_modules:
        try:
            # Check if the required module is already installed
            __import__(module)
        except ImportError:
            print(f"{module} not found. Installing...")
            
            # Use subprocess to run the pip install command
            subprocess.check_call([sys.executable, '-m', 'pip', 'install', module])

            print(f"Installation of {module} successful.")

# Call the function to check and install dependencies
install_dependencies()

import subprocess
import sys
import requests
import base64
from jira.client import Jira
import pandas as pd
from datetime import datetime, timedelta
import os
from dotenv import load_dotenv
import psycopg2

from datetime import datetime
from sqlalchemy import create_engine

script_dir = os.path.dirname(__file__)
dotenv_path = os.path.join(script_dir, 'config.env')
load_dotenv(dotenv_path=dotenv_path)
# Creds from config.env
host = os.getenv('DB_host')
port = os.getenv('DB_port')
user = os.getenv('DB_user')
password = os.getenv('DB_password')
dbname = os.getenv('DB_name')

server = os.getenv('server')
accountName = os.getenv('accountName')  
jiraToken = os.getenv('jiraToken')

end_date = datetime.utcnow().replace(day=1, hour=0, minute=0, second=0, microsecond=0) - timedelta(days=1)
start_date = end_date.replace(day=1)

# Format the dates as required by the Opsgenie API
start_date_str = start_date.strftime('%Y-%m-%dT00:00:00.000Z')  # Alter here to to = "2024-03-01T00:00:00.000Z" this format to limit your date
current_date = datetime.now()
first_day_of_current_month = current_date.replace(day=1)
last_day_of_previous_month = first_day_of_current_month - timedelta(days=1)
end_date_str = end_date.strftime('%Y-%m-%dT23:59:59.999Z')
end_date_str_Jira = current_date.strftime('%Y-%m-%d 23:59')  # "2024-05-01 23:59"#last_day_of_previous_month.strftime('%Y-%m-%d 23:59')
start_date_str_Jira = start_date.strftime('%Y-%m-%d 00:00')  # Alter here to to = "2024-03-01 00:00" this format to limit your date
jql = f"project = ITSM and \"acknowledgment time[time stamp]\" >= \"{start_date_str_Jira}\" and \"acknowledgment time[time stamp]\" <= \"{end_date_str_Jira}\" and \"rate[dropdown]\" in (1.5, 2, 2.5)  AND \"Time Spent SRE (h)[Short text]\" IS NOT EMPTY"  # Jira query 
regex_pattern_components = r"name='([^']+)'"
incident = "Incident"
cred = "Basic " + base64.b64encode(str.encode(accountName + ":" + jiraToken)).decode("utf-8")  # here insert your own account and token from Jira
headers = {
   "Accept": "application/json",
   "Content-Type": "application/json",
   "Authorization": cred
}

# Connect to Jira
jira = Jira(options={'server': server}, basic_auth=(accountName, jiraToken))
jira_issues = jira.search_issues(jql, maxResults=False)

# Convert Jira issues to pandas DataFrame
issues_list = []
for issue in jira_issues:
    component_value = str(issue.fields.components)
    start_index = component_value.find("'") + 1
    end_index = component_value.find("'", start_index)
    component_name = component_value[start_index:end_index]
    d = {
        'id': issue.id,
        'key': issue.key,
        'self': issue.self,
        'assignee': str(issue.fields.customfield_12249.displayName),
        'Begin_Date1': pd.to_datetime(issue.fields.customfield_12254).tz_convert('Europe/Vilnius').strftime('%Y-%m-%d %H:%M'),
        'timespent': float(issue.fields.customfield_12260),
        'summary': str(issue.fields.summary),
        'rate': str(issue.fields.customfield_12170),
        'Working hours': str(issue.fields.customfield_12171),
        'Escalation': str(issue.fields.customfield_12168),
        'Post Mortem': str(issue.fields.customfield_12167),
        'components': component_name,
    }
    issues_list.append(d)
issues = pd.DataFrame(issues_list)

engine = create_engine(f'postgresql+psycopg2://{user}:{password}@{host}:{port}/{dbname}')
# Load existing issues
existing_issues_df = pd.read_sql("SELECT * FROM public.issues", engine)
# Drop duplicates based on 'key' column
existing_issues_df = existing_issues_df.drop_duplicates(subset=['key'])

# Convert existing issues to dictionary for quick lookup
existing_issues_dict = existing_issues_df.set_index('key').to_dict(orient='index')

# Prepare new data for insertion and update
new_rows = []
update_rows = []

for index, row in issues.iterrows():
    key = row['key']
    if key in existing_issues_dict:
        existing_row = existing_issues_dict[key]
        # Compare all columns
        columns_to_compare = ['self', 'assignee', 'Begin_Date1', 'timespent', 'summary', 'rate',
                              'Working hours', 'Escalation', 'Post Mortem', 'components']
        if any(row[col] != existing_row[col] for col in columns_to_compare):
            update_rows.append(row)
    else:
        new_rows.append(row)

# Insert new rows into the "issues" table

if new_rows:
    new_rows_df = pd.DataFrame(new_rows)
    new_rows_df.to_sql("issues", engine, schema="public", if_exists="append", index=False)
# Update existing rows in the "issues" table
conn = engine.raw_connection()
cursor = conn.cursor()

for row in update_rows:
    key = row['key']
    cursor.execute("""
    INSERT INTO issues (
        id, "key", "self", assignee, "Begin_Date1", timespent, summary, rate,
        "Working hours", Escalation, "Post Mortem", components
    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    ON CONFLICT (id) DO UPDATE SET
        "key" = EXCLUDED."key",
        "self" = EXCLUDED."self",
        assignee = EXCLUDED.assignee,
        "Begin_Date1" = EXCLUDED."Begin_Date1",
        timespent = EXCLUDED.timespent,
        summary = EXCLUDED.summary,
        rate = EXCLUDED.rate,
        "Working hours" = EXCLUDED."Working hours",
        Escalation = EXCLUDED.Escalation,
        "Post Mortem" = EXCLUDED."Post Mortem",
        components = EXCLUDED.components
    """, (row['id'], row['key'], row['self'], row['assignee'], row['Begin_Date1'], row['timespent'], 
          row['summary'], row['rate'], row['Working hours'], row['Escalation'], row['Post Mortem'], row['components']))

# Commit changes and close connection
conn.commit()
cursor.close()
conn.close()

print('All done!')