def readHolidays():
try:
global holidayFlag
print(datetime.now(), '<< READING HOLIDAYS Table :- readHolidays() >>')
# get database connection
db_conn = dbu.getConn()
stProc = "SELECT holiday from holidays where project_id = '%s'" % ProjectID
m_row = dbu.executeQueryRes(db_conn, stProc)
# if there is no data in holidays table, then set the holidayFlag = 0
if len(m_row) == 0:
print('--There is no Holiday Data ----- ', file=tempOutFile)
holidayFlag = 0
# Reading the data fetched from the database
for row in m_row:
if row[0] != None:
dtDate = row[0].strftime('%Y-%m-%d')
holiday_data.append(dtDate)
print(datetime.now(),
'<< FINISHED: READING HOLIDAYS Table :- readHolidays() >>')
except (Exception) as error:
print(datetime.now(), "Error in readHolidayExcel(): %s" % error)
print(sys.exc_traceback.tb_lineno)
except (ErrorOccured) as e:
print(e)
print(sys.exc_traceback.tb_lineno)
def readHolidays():
try:
global holidayFlag
print(datetime.now(),'<< READING HOLIDAYS Table :- readHolidays() >>')
# get database connection
db_conn = dbu.getConn()
stProc = "SELECT holiday from holidays"
m_row = dbu.executeQueryRes(db_conn, stProc)
# if there is no data in holidays[], then set the holidayFlag = 0
# Once the global flag is set, then the expand dates
# will not call this function for each date value
if len(m_row) == 0:
print('--There was no Holiday Data ----- ', file=tempOutFile)
holidayFlag = 0
# Reading the data fetched from the database
for row in m_row:
if row[0] != None:
dtDate = row[0].strftime('%Y-%m-%d')
holiday_data.append(dtDate)
print(datetime.now(),'<< FINISHED: READING HOLIDAYS Table :- readHolidays() >>')
except (Exception) as error:
print (datetime.now(),"Error in readHolidayExcel(): %s" %error)
except (ErrorOccured) as e:
print (e)
def readHolidays(wkbook,sheetindex):
try:
# get database connection
db_conn = dbu.getConn()
stProc = "SELECT holiday from holidays"
m_row = dbu.executeQueryRes(db_conn, stProc)
# First make sure that the Holidays sheet exist : Make it the active sheet
# if Holidays sheet does not exist, then this module is skipped
#sheet = wkbook['Holidays']
# get the max count of rows and cols
#m_row = sheet.max_row
#m_row = m_row + 1
numHolidays = len(m_row)
# Reading the excel sheet from row number 2
#for curr_row in range(0, numHolidays, 1):
for row in m_row:
result_unique_row = []
# Appending the activity name
#data = sheet.cell(row=curr_row, column=1)
if row[0] != None:
# dtDate = datetime.strptime(row[0], '%B %d,%Y')
dtDate = row[0].strftime("%Y-%m-%d")
holiday_data.append(dtDate)
#print(holiday_data)
except (Exception) as error:
print ("Error in readHolidayExcel(): %s" %error)
except (ErrorOccured) as e:
print (e)
def insertProjectData(prjName):
try:
print(
datetime.now(),
'<< Getting ProjectID from PROJECT TABLE : insertProjectData() >>')
# get database connection
db_conn = dbu.getConn()
stProc = "SELECT ID from PROJECTS WHERE NAME='%s'" % prjName
#m_row = dbu.executeQueryRes(db_conn, stProc)[0]
m_row = dbu.executeQueryRes(db_conn, stProc)
if len(m_row) > 0:
return m_row[0]
else:
print(datetime.now(),
"----- INSERT Statements for New Project ------------------",
file=tempOutFile)
execSQL = ('insert_project_data')
execData = (prjName, None, None, None, None, None, None, None)
print(execSQL, execData, file=tempOutFile)
prjID = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0]
return prjID
print(
datetime.now(),
'<< FINISHED: Getting ProjectID from PROJECT TABLE : insertProjectData() >>'
)
except (Exception, psycopg2.DatabaseError) as error:
print(datetime.now(), "Database Error %s " % error)
raise
def readHolidays():
try:
# get database connection
db_conn = dbu.getConn()
stProc = "SELECT holiday from holidays"
m_row = dbu.executeQueryRes(db_conn, stProc)
# Reading the data fetched from the database
for row in m_row:
if row[0] != None:
dtDate = row[0].strftime('%Y-%m-%d')
holiday_data.append(dtDate)
except (Exception) as error:
print("Error in readHolidayExcel(): %s" % error)
except (ErrorOccured) as e:
print(e)
def insertBundlesData():
try:
# First check to make sure the final_wbs_list is not empty
if (len(final_wbs_list)) == 0:
raise ErrorOccured(
"Empty final_wbs_list in function insertBundlesData()")
global ProjectID
print(
datetime.now(),
'<< INSERTING BUNDLES DATA in BUNDLES table: insertBundlesData() >>'
)
# get database connection
db_conn = dbu.getConn()
lParentBundleID = None
print(datetime.now(),
"-------Writing WBS Data to Bundles Table --------------------",
file=tempOutFile)
for i in range(0, len(final_wbs_list)):
lPhaseID = None
local_bundle_list = []
lBundleID = final_wbs_list[i][0]
lBundleName = final_wbs_list[i][1]
lParentBundleID = final_wbs_list[i][2]
# create a list with client_bundleid,bundle_name & database created bundleID
local_bundle_list.append(lBundleID)
# Get the phaseID from key_value_list : contains db_phaseID, client_taskID, client_bundlesID
lPhaseID = findPhaseValue(lBundleID)
# First get the parentBundleID from table Bundles
# Pass the client_bundle_id and get the bundle_id from table
# This is to establish the parent-child relationship for bundles
stProc = "SELECT ID FROM BUNDLES WHERE CLIENT_BUNDLE_ID='%s'" % lParentBundleID
m_row = dbu.executeQueryRes(db_conn, stProc)
#First check if the m_row is having value or empty
# if it has no value, then the parentBundleID is set to None
# Else insert the bundleId of db of the parent
if len(m_row) > 0:
ParentBundleID = m_row[0]
if len(m_row) == 0:
ParentBundleID = None
print(datetime.now(),
"----- INSERT Statements for Bundles ------------------",
file=tempOutFile)
execSQL = ('insert_bundles_data')
execData = (ParentBundleID, lBundleName, ProjectID, None, lPhaseID,
lBundleID)
print(execSQL, execData, file=tempOutFile)
#print(execSQL, execData)
# inserting the bundles into the bundles table
lCurrentBundleID = dbu.fetchStoredFuncRes(db_conn, execSQL,
execData)[0]
local_bundle_list.append(
lCurrentBundleID
) # this is the current bundleid from database query
#lParentBundleID = lCurrentBundleID
bundle_list.append(local_bundle_list)
# update the bundle_dictionary which will be read for inserting bundle_activities data
bundle_dict.update({lBundleID: lCurrentBundleID})
# ------------ Code for inserting data into BUNDLE_PHASES -----------------
# Since phaseID is part of bundles table, we are not inserting any data in BUNDLE_PHASES table
# The below code is left back, in case in future, data needs to be inserted
# --------------------------------------------------------------------------------------------
#if lBundleID in phases_dict:
# print("----- INSERT Statements for BUNDLE_PHASES ------------------", file=tempOutFile)
# execSQL = "INSERT INTO BUNDLE_PHASES (BUNDLE_ID, PHASE_ID) VALUES (%s,%s);"
# execData = (lBundleID,phases_dict[lBundleID])
# print(execSQL, execData, file=tempOutFile)
# dbu.executeQueryWithData(db_conn, execSQL, execData)
print(datetime.now(),
'-------------Bundle List ----------------------',
file=tempOutFile)
print(bundle_list, file=tempOutFile)
print(datetime.now(),
'-------------Bundle Dictionary ----------------------',
file=tempOutFile)
print(bundle_dict, file=tempOutFile)
print(
datetime.now(),
'<< FINISHED: INSERTING BUNDLES DATA in BUNDLES table: insertBundlesData() >>'
)
except (Exception, psycopg2.DatabaseError, ErrorOccured) as error:
print(datetime.now(),
"Database Error in insertBundlesData() %s " % error)
raise