Перенос данных из VisionFlow в ServiceNow

Transform map позволяет нам задать ключевое поле, по которому система будет понимать, что запись с данными параметрами уже присутствует в таблице и требуется только обновление полей

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')