SELECT
  projectissue.projectIssueId,
  projectissue.ticketId as 'Number',
    reporter.email as 'Reporter',
    projectissue.name as 'Short Description',
    projectissue.Description as 'Description',
    projectissue.companycustomfield15 as 'Product',
    projectissue.companycustomfield13 as 'Document',
    issuestatus.name as 'Status',
    assignee.name as 'Assignee',
    ADDTIME(projectissue.CreateDate, '-01:00') as 'Created',
    ADDTIME(projectissue.completionDate, '-01:00') as 'Closed',
    issuehistory.EventText as 'Comment',
    author.name as 'commentAuthor'
FROM
  projectissue
INNER JOIN issuestatus
    ON projectissue.IssueStatusId = issuestatus.IssueStatusId
INNER JOIN systemuser assignee
  ON projectissue.ResponsibleSystemUserId = assignee.SystemUserId
INNER JOIN systemuser reporter
  ON projectissue.CreatedBySystemUserId = reporter.SystemUserId
INNER JOIN issuehistory ON
  issuehistory.ProjectIssueId = projectissue.ProjectIssueId
INNER JOIN systemuser author
  ON issuehistory.SystemUserId = author.SystemUserId
WHERE
  projectissue.ProjectId = 54 AND (issuehistory.IssueEventTypeId = 5 OR issuehistory.IssueEventTypeId = 10 OR issuehistory.IssueEventTypeId = 2)
    #projectissue.ProjectId = 54
ORDER BY projectissue.TicketId ASC, issuehistory.EventDate ASC

SELECT
  document.documentId,
  document.name,
    document.FullPath,
  SUBSTRING_INDEX(SUBSTRING_INDEX(document.FullPath, '/', -2), '/', 1) as 'projectIssueId',
    projectissue.ticketId as 'Number'
FROM
  visionflow.document
INNER JOIN projectissue
ON projectissue.ProjectIssueId = SUBSTRING_INDEX(SUBSTRING_INDEX(document.FullPath, '/', -2), '/', 1)
WHERE
  document.FullPath like '%/54/issuedocuments/%'
ORDER BY projectissueid

import pandas as pd, os
from tqdm import tqdm
def renameFolders():
    df = pd.read_csv('/Downloads/folder_rename.csv')
    pbar = tqdm(total=len(df))
    for _ , row in df.iterrows():
        old_name = row['old_folder_name']
        new_name = row['new_folder_name']
        try:
            os.rename(f'/Downloads/home/tomcat/vflowdocs/54/issuedocuments/{old_name}', f'/Downloads/home/tomcat/vflowdocs/54/issuedocuments/{new_name}')
            pbar.update(1)
        except:
            pbar.update(1)
def removeEmptyFolders():
    folder_list = os.listdir('/Downloads/home/tomcat/vflowdocs/54/issuedocuments/')
    for folder in folder_list:
        path = f'/Downloads/home/tomcat/vflowdocs/54/issuedocuments/{folder}'
        try:
            os.rmdir(path)
        except:
            if len(os.path.basename(path)) < 6 and os.path.basename(path) != 'nan':
                print(f'ServiceNow SysId not found for item: {os.path.basename(path)}')
renameFolders()
removeEmptyFolders()

import os, glob, filetype, requests, mimetypes
from tqdm import tqdm
import pandas as pd
def number_of_files():
    files_number = 0
    folder_list = os.listdir('/Downloads/home/tomcat/vflowdocs/54/issuedocuments/')
    for folder in folder_list:
       files_number += len(os.listdir(f'/Downloads/home/tomcat/vflowdocs/54/issuedocuments/{folder}/'))
    return files_number
#Progress Bar
pbar = tqdm(total=1297)
log_messages_status = []
log_messages_filepath = []
log_messages_filename = []
log_messages_target = []
def uploadAllFiles(folder_name):
    #Variables
    entire_list = glob.glob(f'/Downloads/home/tomcat/vflowdocs/54/issuedocuments/{folder_name}/*')
    my_list_updated = []
    #Get Files Size
    def getSize(fileobject):
        fileobject.seek(0,2)
        size = fileobject.tell()
        return size
    #Upload Files
    def uploadFunc(filename, sys_id, path_to_file, content_type):
        url = f'https://instance.service-now.com/api/now/attachment/file?file_name={filename}&table_name=table_name&table_sys_id={sys_id}'
        payload=open(path_to_file, 'rb').read()
        headers = {
            'Accept': 'application/json',
            'Authorization': 'Bearer ',
            'Content-Type': content_type,
            }
        response = requests.request("POST", url, headers=headers, data=payload)
        if response.status_code == 201:
            #print(f'Success: {filename} was uploaded to the incident with sys_id {sys_id}')
            pbar.update(1)
            log_messages_status.append('Success')
            log_messages_filename.append(filename)
            log_messages_filepath.append(path_to_file)
            log_messages_target.append(sys_id)
        else:
            pbar.update(1)
            #print(f'Error: {filename} was not uploaded to the incident with sys_id {sys_id}')
            log_messages_status.append('Error')
            log_messages_filename.append(filename)
            log_messages_filepath.append(path_to_file)
            log_messages_target.append(sys_id)
    #Remove Duplicates
    def removeDuplicatesByName(list_of_elements):
        list_of_elements.sort()
        if len(list_of_elements) > 1:
            for item in list_of_elements:
                item_to_compare = item.split('.')[0]
                for element in list_of_elements:
                    if item_to_compare in element:
                        entire_list.remove(element)
                    else:
                        pass
            return list_of_elements
        else:
            return list_of_elements
    my_list = removeDuplicatesByName(entire_list)
    for item in my_list:
        file_size = open(item, 'rb')
        if getSize(file_size) > 3000:
            my_list_updated.append(item)
        else:
            pass
    for attach in my_list_updated:
        kind = filetype.guess_mime(attach)
        if kind != None:
            uploadFunc(os.path.basename(attach), os.path.dirname(attach).split('/')[-1], attach, kind)
        elif kind == None and attach.split('.')[-1] == 'txt':
            uploadFunc(os.path.basename(attach), os.path.dirname(attach).split('/')[-1], attach, 'text/plain')
        else:
            uploadFunc(os.path.basename(attach), os.path.dirname(attach).split('/')[-1], attach, 'application/octet-stream')
def getFolders():
    folder_list = os.listdir('/Downloads/home/tomcat/vflowdocs/54/issuedocuments/')
    for folder in folder_list:
        if folder != '.DS_Store':
            uploadAllFiles(folder)
getFolders()
data_to_write = pd.DataFrame({
    'status': log_messages_status,
    'file_name' : log_messages_filename,
    'file_path' : log_messages_filepath,
    'target' : log_messages_target
})
data_to_write.to_csv('/Downloads/results_log.csv')