gistfile1.txt

from boxsdk import Client
from boxsdk import JWTAuth
from boxsdk.object.collaboration import CollaborationRole
from datetime import datetime, timezone
from dateutil.relativedelta import relativedelta
import csv # Needed to parse the whitelist file
import json # Needed to generate metadata for output files
import os # Needed to create output files
from boxsdk.exception import BoxAPIException
import sys
import datetime
import values
import openpyxl
import calendar
# from boxsdk.config import Proxy
# Proxy.URL = 'http://zproxy.statestr.com:80'
def get_box_client():
    """
    Returns Box client created from JWT.
    """
    auth = JWTAuth.from_settings_file(constants.BOX_JWT)
    return Client(auth)

def getItem(item_type, client, item_id):
    """
    Gets folder name based on folder ID
    """
    admin = client.user(user_id=constants.ADMIN_ID)
    admin_client = client.as_user(admin)

    if item_type == 'folder':
        # Create output csv report structure for folders to record collaborators found
        folder = admin_client.folder(folder_id=item_id).get()
        print(folder.name)
        return folder

    elif item_type == 'file':
        file = admin_client.file(file_id=item_id).get()
        # path_collection = file.path_collection
        # file_path = create_file_path(path_collection)
        
        print(file.name)
        return file

def get_file_path(path_object):
    
    path_array = [file_level['name'] for file_level in path_object['entries']]

    return path_array


def get_directory_files(client, folder_id, result_files=[]):
    items = client.folder(folder_id=folder_id).get_items()
    
    for item in items:
        print(item)
        if item.type == 'folder':
            file_info = getItem('folder', client, item.id)
            file_info_dict= {}
            
            file_path_details = file_info['path_collection']
            file_path_arr = get_file_path(file_path_details)
            file_path_str = '/'.join(file_path_arr)


            file_info_dict={
                'file_type':'folder',
                'file_id' :file_info['id'],
                'file_name':file_info['name'],
                'file_path':file_path_str,
                'file_owner':file_info['created_by']['name'],
                'last_modified':file_info['content_modified_at'],
                'parent_folder_id':file_info['parent']['id']
            }
            result_files.append(file_info_dict)
            get_directory_files(client, item.id, result_files)
            
        if item.type == 'file':
            file_info = getItem('file', client, item.id)
            file_info_dict= {}
            
            file_path_details = file_info['path_collection']
            file_path_arr = get_file_path(file_path_details)
            file_path_str = '/'.join(file_path_arr)


            file_info_dict={
                'file_type':'file',
                'file_id' :file_info['id'],
                'file_name':file_info['name'],
                'file_path':file_path_str,
                'file_owner':file_info['created_by']['name'],
                'last_modified':file_info['content_modified_at'],
                'parent_folder_id':file_info['parent']['id']
            }
            result_files.append(file_info_dict)

    print(f'NUMBER OF FILES ====== {len(result_files)}')
    return result_files
    


def move_file(client, file_id, target_folder_id):

    admin = client.user(user_id=constants.ADMIN_ID)
    admin_client = client.as_user(admin)

    file_to_move = admin_client.file(file_id)
    destination_folder = admin_client.folder(target_folder_id)

    moved_file = file_to_move.move(parent_folder=destination_folder)
    return moved_file

def bookmark_file(client, file_id, bookmark_name, folder_id):

    admin = client.user(user_id=constants.ADMIN_ID)
    admin_client = client.as_user(admin)
    data = {
        "name": bookmark_name,
        "url": f"https://app.box.com/file/{file_id}"
    }

    # Create the bookmark
    try:
        web_link = admin_client.folder(folder_id=folder_id).create_web_link(data['url'], data['name'])
        print(web_link.url)
        return web_link
    except BoxAPIException as e:
        print(f"Bookmark creation faileds: ", e)
        return "bookmark failed"
################## taken from constants.py ################################
config_file = values.config_file
admin_user_id = values.admin_user_id
clients_folder_parent = values.clients_folder_parent
external_folder_parent= values.external_folder_parent
metadata_template_name = values.metadata_template_name
four_eye_field = values.four_eye_field
external_field = values.external_field
jwt_dict = values.jwt_dict
###########################################################################

# Create Four Eye Review Folders under each Client
    # Excel template for each client located in each Client's folder
    # Create Four-Eye Review folder for each Client>Fund
    # Note Folder ID of Four Eye Review folder for each Fund to apply as metadata on the actual Fund folders
# Create External Facing folder structure for each client
    # Down to the fund/period level - not the subfolders after that
    # Note Folder ID of External facing folder so that it can also be applied as metadata
# Create Folder structure under each client - per fund per period
    # Apply Four Eye Review folder ID as metadata to each Actual Fund-period folder, so that integration knows where to move the folder into
    # Apply External Folder ID as metadata to actual fund-period folder as well, so that when files are moved to four eye review, we know which external folder to move them into after the review is complete
    # Cascade the metadata so it is on all files and those files have clear destination folders to move to by us being able to refence the external Folder ID
    # Build out folder structure down to the period specified in the excel

def main():

    #Shut down this app if the config file was not found

    #Setup authorization using config.json file
    auth = JWTAuth.from_settings_dictionary(jwt_dict)
    auth.authenticate_instance()

    #Create Box API client
    sa_client = Client(auth)
    admin_user = sa_client.user(user_id=admin_user_id)  # fsufi+demo admin user
    client = sa_client.as_user(admin_user)

    months_dict = {1: 'January', 2: 'February', 3: 'March', 4: 'April', 
    5: 'May', 6: 'June', 7: 'July', 8: 'August', 9: 'September', 
    10: 'October', 11: 'November', 12: 'December'}

    #get client folders located under parent folder
    client_folders = client.folder(folder_id=clients_folder_parent).get_items()
    today = datetime.datetime.today()
    year = today.year

    for client_folder in client_folders:
        #get items under each client folder. The folder structure excel template should be located here in order for a structure to be built out
        items = client.folder(folder_id=client_folder.id).get_items()

        for item in items:
            print('item.name: ', item.name)
            #if four eye review folder exists for the client, continue, otherwise create one
            if item.name == 'Files for Four Eye Review':
                continue

            else:
                four_eye_folder = createFolder(client, client_folder.id, 'Files for Four Eye Review')

            #if template folder exists, read from it
            if item.name == 'Box Folder Structure.xlsx':
                #get excel file from Box and write contents to a local file
                file_content = client.file(item.id).content()
                f = open("./folderstructureFile.xlsx", "wb")
                f.write(file_content)
                f.close()

                xlsx_to_csv('./folderstructureFile.xlsx','structure.csv')

                with open('structure.csv', newline='') as csvfile:
                    csvreader = csv.reader(csvfile, delimiter=',')
                    count = 0

                    for row in csvreader:

                        count +=1
                        client_code = row[1]
                        fund_code = row[2]
                        folder_initiation = row[3][:10]
                        folder_termination = row[4]
                        permission_1 = row[5]
                        permission_2 = row[6]
                        permission_daily = row[7]
                        team_email = row[8]
                        period = row[9]
                        folder_structure = row[10]
                        daily_recon = row[11]
                        pricing = row[12]
                        allocation_cap_act = row[13]
                        allocation_ecdi = row[14]
                        recon_cash = row[15]
                        recon_position = row[16]
                        recon_PnL = row[17]
                        corp_act_broker_recon = row[18]
                        corp_act_second_source = row[19]
                        trading = row[20]
                        non_trading = row[21]
                        controls = row[22]
                        required = row[23]

                        #skip first two rows
                        if count > 2:

                            if required == 'Y':
                                initiation_month = datetime.datetime.strptime(folder_initiation, '%Y-%m-%d').month

                                # create fund folder in Four Eye Review Folder:
                                four_eye_fund_folder = createFolder(client, four_eye_folder, fund_code)

                                # print("NEXT")

                                #create external facing client folder
                                external_client_folder = createFolder(client, external_folder_parent, client_code)

                                #create subfolder dictionary based on Y values in each column to determine if subfolder will be needed
                                subfolder_dict = {}

                                if pricing == 'Y':
                                    subfolder_dict['Pricing'] = 'Y'                                    

                                if allocation_cap_act == 'Y' or allocation_ecdi == 'Y':
                                    subfolder_dict['Allocations'] = 'Y'
                                    allocations_dict = {}
                                    if allocation_cap_act == 'Y':
                                        allocations_dict['Capital Activity'] = 'Y'
                                    if allocation_ecdi == 'Y':
                                        allocations_dict['ECDI'] = 'Y'

                                if recon_cash == 'Y' or recon_position == 'Y' or recon_PnL == 'Y' or recon_position =='Y':
                                    subfolder_dict['Reconciliations'] = 'Y'
                                    # daily_recon_subfolder_dict['Reconciliations'] = 'Y'

                                    recon_dict = {}
                                    if recon_cash == 'Y':
                                        recon_dict['Cash'] = 'Y'
                                    if recon_position == 'Y':
                                        recon_dict['Positions'] = 'Y'
                                    if recon_PnL == 'Y':
                                        recon_dict['PnL'] = 'Y'

                                if corp_act_broker_recon == 'Y' or corp_act_second_source == 'Y':
                                    subfolder_dict['Corporate Action'] = 'Y'
                                    corp_action_dict = {}
                                    if corp_act_broker_recon == 'Y':
                                        corp_action_dict['Broker Recon'] = 'Y'
                                    if corp_act_second_source == 'Y':
                                        corp_action_dict['2nd Source Recon'] = 'Y'

                                if trading == 'Y':
                                    subfolder_dict['Trading Accruals'] = 'Y'

                                if non_trading == 'Y':
                                    subfolder_dict['Non Trading Accruals'] = 'Y'

                                if controls == 'Y':
                                    subfolder_dict['Controls'] = 'Y'

                                #Month based reconciliation folder creation (weekly, monthly, daily, bi-monthly)
                                if period != 'Quarterly':
                                    period_id = createFolder(client, client_folder.id, 'Monthly')

                                    external_period_id = createFolder(client, external_client_folder, 'Monthly')

                                    year_folder_id = createFolder(client, period_id, str(year))

                                    external_year_folder = createFolder(client, external_period_id, str(year))

                                    fund_folder_id = createFolder(client, year_folder_id, fund_code)

                                    external_fund_folder_id = createFolder(client, external_year_folder, fund_code)

                                    current_month = datetime.datetime.today().month

                                    for index in range(current_month + 3):
                                        month_folder = createFolder(client, fund_folder_id, months_dict[index+1])
                                        external_month_folder = createFolder(client, external_fund_folder_id, months_dict[index+1])
                                        
                                        if period == 'Bi-Monthly':
                                            time_periods = ['Period 1', 'Period 2']
                                        elif period == 'Weekly':
                                            time_periods = ['Week 1', 'Week 2', 'Week 3', 'Week 4', 'Week 5']
                                        elif period == 'Daily':
                                            num_days = calendar.monthrange(year,index+1)[1]
                                            weekdays = []
                                            for i in range(num_days):
                                                my_date = datetime.date(year, index+1, i+1)
                                                if my_date.weekday() < 5:
                                                    weekdays.append(i+1)

                                            time_periods = []
                                            for each in weekdays:
                                                time_periods.append(months_dict[index+1] + " " + str(each))

                                        
                                        if period == 'Monthly':
                                            metadata = {
                                                four_eye_field: str(four_eye_fund_folder),
                                                external_field: str(external_month_folder)
                                                    }
                                            applyMetadataAndCascade(month_folder, client, metadata)
                                            createSubfolderStructure(client, subfolder_dict, month_folder, recon_dict, corp_action_dict, allocations_dict)

                                        else:
                                            for time_period in time_periods:

                                                period_folder = createFolder(client, month_folder, time_period)
                                                external_period_folder = createFolder(client, external_month_folder, time_period)

                                                metadata = {
                                                four_eye_field: int(four_eye_fund_folder),
                                                external_field: int(external_period_folder)
                                                    }

                                                applyMetadataAndCascade(period_folder, client, metadata)
                                                createSubfolderStructure(client, subfolder_dict, period_folder, recon_dict, corp_action_dict, allocations_dict)

                                #Quarterly reconciliation folder creation (different structure than monthly reconciliations)
                                elif period == 'Quarterly':
                                    period_id = createFolder(client, client_folder.id, 'Quarterly')
                                    external_period_id = createFolder(client, external_client_folder, 'Quarterly')
                                    year_folder_id = createFolder(client, period_id, str(year))
                                    external_year_folder = createFolder(client, external_period_id, str(year))
                                    fund_folder_id = createFolder(client, year_folder_id, fund_code)
                                    external_fund_folder_id = createFolder(client, external_year_folder, fund_code)
                                    month_count = initiation_month

                                    current_month = datetime.datetime.today().month

                                    if current_month in [1,2,3]:
                                        quarters = ['Q1 - March', 'Q2 - June']
                                    if current_month in [4,5,6]:
                                        quarters = ['Q1 - March', 'Q2 - June', 'Q3 - September']
                                    if current_month in [7,8,9]:
                                        quarters = ['Q1 - March', 'Q2 - June', 'Q3 - September', 'Q4 - December']
                                    if current_month in [10,11,12]:
                                        quarters = ['Q1 - March', 'Q2 - June', 'Q3 - September', 'Q4 - December']

                                    for time_period in quarters:

                                        period_folder = createFolder(client, fund_folder_id, time_period)
                                        external_period_folder = createFolder(client, external_fund_folder_id, time_period)
                                        createSubfolderStructure(client, subfolder_dict, period_folder, recon_dict, corp_action_dict, allocations_dict)

                                if daily_recon == 'Y':
                                    daily_recon_subfolder_dict = {}
                                    daily_recon_subfolder_dict['Pricing'] = 'Y'
                                    daily_recon_subfolder_dict['Reconciliations'] = 'Y'
                                    recon_dict['Cash'] = 'Y'
                                    recon_dict['PnL'] = 'Y'
                                    recon_dict['Positions'] = 'Y'
                                    daily_id = createFolder(client, client_folder.id, 'Daily')
                                    year_folder_id = createFolder(client, daily_id, str(year))
                                    fund_folder_id = createFolder(client, year_folder_id, fund_code)

                                    current_month = datetime.date.today().month

                                    for index in range(current_month + 3):
                                        num_days = calendar.monthrange(year,index+1)[1]
                                        weekdays = []
                                        for i  in range(num_days):
                                            my_date = datetime.date(year, index+1, i+1)
                                            if my_date.weekday() < 5:
                                                weekdays.append(i+1)

                                        month_folder = createFolder(client, fund_folder_id, months_dict[index+1])
                                        time_periods = []

                                        for each in weekdays:
                                            time_periods.append(months_dict[index+1] + " " + str(each))

                                        for day in time_periods:
                                            period_folder = createFolder(client, month_folder, day)
                                            createSubfolderStructure(client, daily_recon_subfolder_dict, period_folder, recon_dict, {}, {})

            else:
                print("no need to process because no excel sheet. move on")

        print("done with a client folder, onto next")

def createSubfolderStructure(client, subfolder_dict, period_folder, recon_dict, corp_action_dict, allocations_dict):
#Function to create subfolders based on subfolder dictionary
    for each in subfolder_dict:
        if each == 'Reconciliations':
            if subfolder_dict[each] == 'Y':
                subfolder = createFolder(client, period_folder, each)
                for item in recon_dict:
                    recon_subfolder = createFolder(client, subfolder, item)
                    recon_sub_sub = createFolder(client, recon_subfolder, 'Broker Stmt')
                    recon_sub_sub = createFolder(client, recon_subfolder, 'Break Backup')
        if each == 'Corporate Action':
            if subfolder_dict[each] == 'Y':
                subfolder = createFolder(client, period_folder, each)
                for item in corp_action_dict:
                    corp_subfolder = createFolder(client, subfolder, item)
        if each == 'Allocations':
            if subfolder_dict[each] == 'Y':
                subfolder = createFolder(client, period_folder, each)
                for item in allocations_dict:
                    allosubfolder = createFolder(client, subfolder, item)
        else:
            subfolder = createFolder(client, period_folder, each)


def applyMetadataAndCascade(period_folder, client, metadata):
#Function to apply metadata on folder/file to denote four eye folder ID and external folder ID
    print(metadata)
    try:
        folder = client.folder(period_folder)
        metadata_template = client.metadata_template('enterprise', 'navFilesFourEye')
        applied_metadata = client.folder(folder_id=period_folder).metadata(scope='enterprise', template='navFilesFourEye').set(metadata)
                                                
        cascade_policy = folder.cascade_metadata(metadata_template)
        print(f'Folder {cascade_policy.parent.id} has a metadata cascade policy for {cascade_policy.scope} template "{cascade_policy.templateKey}"')
    except BoxAPIException as e:
        print("Apply metadata/cascade error", e)

    try:
        metadata = {'four_eye_elligible': 'yes'}       
        applied_metadata = client.folder(folder_id=period_folder).metadata().create(metadata)
        print("applied elligibility metadata")
    except BoxAPIException as e:
        print("error applying four eye elligible", e)


def createFolder(client, parentFolder_id, newFolderName):
    print("creating a folder for {0}".format(newFolderName))
    try:
        subfolder = client.folder(parentFolder_id).create_subfolder(newFolderName)
        return subfolder.id
    except BoxAPIException as e:
        print ("Create folder exception status code: " + str(e.status))
        status_code=e.status
        if status_code == 409:
            print("Folder with same name found. Need to get existing folder id for " + newFolderName)
            folders = client.folder(folder_id=parentFolder_id).get_items()
            for folder in folders:
                if folder.name == newFolderName:
                    print("Found folder id: {0} and name: {1}".format(folder.id, folder.name))
                    return folder.id


def xlsx_to_csv(xlsx_file, csv_file):

    workbook = openpyxl.load_workbook(xlsx_file, data_only=True)
    sheet = workbook['Template']

    with open(csv_file, 'w', newline='') as f:
        writer = csv.writer(f)
        for row in sheet.iter_rows():
            csv_row = []
            for cell in row:
                if cell.value is None:
                    csv_row.append("")
                elif isinstance(cell.value, str):
                    csv_row.append(cell.value)
                else:
                    csv_row.append(cell.value)
            writer.writerow(csv_row)


if __name__ == "__main__":
    main()