Deep in the Code

Happy New Year!

The result of Part 1 was a list of XER files, all of which were in one folder. It is not clear from where in the Enterprise Project Structure (EPS) that each file belonged.

Perhaps you would like to create a hierarchy of folders similar to the project screen in P6?

I have modified my original Python script to build out a folder structure that is similar to the EPS. I have not been able to find all levels of the hierarchy within the P6 database, but I was able to get most of the levels represented.

import cx_Oracle
import datetime
import fileinput
import os
import subprocess
import sys
from codecs import open

arguments = len(sys.argv)-1
if arguments < 6:
	print ("The script is called with %i arguments - 6 arguments are required. (DBUsername, DBpassword, DBalias, APPusername, APPpassword, OneXer)" % (arguments))
	sys.exit()
	
DBusername= sys.argv[1] 
DBpassword=sys.argv[2] 
DBalias=sys.argv[3] 
APPusername=sys.argv[4] 
APPpassword=sys.argv[5]
OneXer=sys.argv[6]

if OneXer != "0":
	OneXer = True
else:
	OneXer = False

"""
Url: https://gist.github.com/wassname/1393c4a57cfcbf03641dbc31886123b8
"""
import unicodedata
import string

valid_filename_chars = "-_.() %s%s" % (string.ascii_letters, string.digits)
char_limit = 255

def clean_filename(filename, whitelist=valid_filename_chars, replace=' '):
    # replace spaces
    for r in replace:
        filename = filename.replace(r,'_')
    
    # keep only valid ascii chars
    cleaned_filename = unicodedata.normalize('NFKD', filename).encode('ASCII', 'ignore').decode()
    
    # keep only whitelisted chars
    cleaned_filename = ''.join(c for c in cleaned_filename if c in whitelist)
    if len(cleaned_filename)>char_limit:
        print("Warning, filename truncated because it was over {}. Filenames may no longer be unique".format(char_limit))
    return cleaned_filename[:char_limit]  
"""
End of function
"""

def secure_log():
	if os.path.exists('myLog.txt'):
		with fileinput.FileInput('myLog.txt', inplace=True) as file:
			for line in file:			
				print(line.replace(APPpassword, len(APPpassword)*'X'), end='')

ospath = os.path.dirname(os.path.abspath(__file__))
pmpath = subprocess.check_output("WHERE PM.EXE", shell=True) 


def begin_process():
	secure_log()
	numtodo = 0
	proj_name_str_first = ''
	if os.path.exists("actions.xml"):
		os.rename("actions.xml", "actions" + datetime.datetime.now().strftime("%Y%m%d%H%M%S") + ".xml")	

	con = cx_Oracle.connect("{}/{}@{}".format(DBusername, DBpassword, DBalias))
	#print (con.version)

	cursor = con.cursor()
	if OneXer:
		cursor.execute("""
			SELECT DISTINCT PROJ_SHORT_NAME FROM PROJECT 
			WHERE ORIG_PROJ_ID IS NULL			
			AND PROJ_SHORT_NAME NOT LIKE '%^%'
			AND PROJ_SHORT_NAME NOT LIKE '%&%'
			ORDER BY PROJ_SHORT_NAME
			""")
	else:
		cursor.execute("""
			SELECT PROJ_SHORT_NAME, FILEPATH FROM (
			SELECT PROJ_SHORT_NAME,
			LTRIM(MAX(SYS_CONNECT_BY_PATH(OBS_NAME,'\\'))
			KEEP (DENSE_RANK LAST ORDER BY OBS_ID),',') AS FILEPATH
			FROM
			(SELECT REPLACE(REPLACE(REPLACE(REPLACE(o.OBS_NAME,',',''),' ','_'),'&','and'),'/','-') AS OBS_NAME, p.PROJ_SHORT_NAME, o.PARENT_OBS_ID, o.OBS_ID
			FROM OBSPROJ op
			JOIN OBS o ON op.OBS_ID = o.OBS_ID
			JOIN PROJECT p ON p.PROJ_ID = op.PROJ_ID
			WHERE p.ORIG_PROJ_ID IS NULL
			AND p.RISK_LEVEL IS NULL
			AND p.PROJ_SHORT_NAME NOT LIKE '%^%'
			AND p.PROJ_SHORT_NAME NOT LIKE '%&%')
			GROUP BY PROJ_SHORT_NAME
			CONNECT BY PARENT_OBS_ID = PRIOR OBS_ID AND PROJ_SHORT_NAME = PRIOR PROJ_SHORT_NAME
			START WITH PARENT_OBS_ID IS NULL)
			ORDER BY PROJ_SHORT_NAME ASC
			""")
			
	f = open(ospath + '\\actions.xml','a')
	xml_file_begin = '<actions>\n'
	f.write(xml_file_begin)
	
	if OneXer:
		xml_file = '\t<action>\n\t\t<type>export</type>\n\t\t<Projects>'
		f.write(xml_file)
			
	for row in cursor:
		proj_name_actual = row[0]				
		filepath = (ospath + row[1])
		proj_name_str = filepath + '\\' + clean_filename(proj_name_actual)
		#print(proj_name_str)		
		#print("Project:", proj_name_actual)
		if OneXer:
			xml_file = '\n\t\t\t<ProjectID>' + proj_name_actual + '</ProjectID>'
			f.write(xml_file)
		else:
			if not os.path.exists(proj_name_str + '.xer'):
				if not os.path.exists(filepath):
					#print('mkdir ' + '\"' + filepath + '\"')
					os.system('mkdir ' + filepath)
				if proj_name_str_first == '':
					proj_name_str_first = proj_name_str
				numtodo += 1
				
				xml_file = '\t<action>\n\t\t<type>export</type>\n\t\t<Projects>\n\t\t\t<ProjectID>' + proj_name_actual + '</ProjectID>\n\t\t</Projects>\n\t\t<outputFile>' + proj_name_str +'.xer</outputFile>\n\t</action>\n'	
				f.write(xml_file)	
			else:
				print("Skipped: Project "+ proj_name_actual + " already exported or errored.")
			
	if OneXer:
		xml_file = '\n\t\t</Projects>\n\t\t<outputFile>' + ospath + '\\OneFile.xer</outputFile>\n\t</action>\n'
		f.write(xml_file)	
	
	xml_file_end = '</actions>\n'
	f.write(xml_file_end)
	f.close()
	if not OneXer:
		print("Number of projects left to process:", numtodo)		
	os.system('PM.EXE /username=' + APPusername + ' /password=' + APPpassword + ' /actionScript=\"' + ospath + '\\actions.xml\" /logfile="' + ospath + '\\_myLog.txt\"')
	con.close()
	secure_log()
	
	if numtodo > 0:
		if not os.path.exists(proj_name_str_first + '.xer'):
			print("Errored on: ", proj_name_str_first)
			g = open(proj_name_str_first + '.xer','w')
			g.write('Error!!')
			g.close()
			h = open(proj_name_str_first + '-ERRROR.txt','w')
			h.write('Error!!')
			h.close()
		begin_process()		
		
begin_process()

The result of running this script should be a folder structure with names that are similar to the headings in the EPS. The exported files will be put into these folders. Strange characters in the names may need to be escaped or replaced (as is done above in the SQL query).