def printOverviewActivityHeading():
try:
worksheet.set_column(ecr.getPrint_ActivityID_Heading()[0],
ecr.getPrint_ActivityID_Heading()[1], 10)
worksheet.write(ecr.getPrint_ActivityID_Heading()[0],
ecr.getPrint_ActivityID_Heading()[1], 'ACTIVITY ID',
format)
worksheet.set_column(ecr.getPrint_ActivityName_Heading()[0],
ecr.getPrint_ActivityName_Heading()[1], 30)
worksheet.write(ecr.getPrint_ActivityName_Heading()[0],
ecr.getPrint_ActivityName_Heading()[1], 'ACTIVITIES',
format)
worksheet.set_column(ecr.getPrint_TotalQuantities_Heading()[0],
ecr.getPrint_TotalQuantities_Heading()[1], 15)
worksheet.write(ecr.getPrint_TotalQuantities_Heading()[0],
ecr.getPrint_TotalQuantities_Heading()[1],
'TOTAL QUANTITIES', format)
worksheet.set_column(ecr.getPrint_UnitID_Heading()[0],
ecr.getPrint_UnitID_Heading()[1], 7)
worksheet.write(ecr.getPrint_UnitID_Heading()[0],
ecr.getPrint_UnitID_Heading()[1], 'UNIT ID', format)
worksheet.write(ecr.getPrint_UnitName_Heading()[0],
ecr.getPrint_UnitName_Heading()[1], 'UNITS', format)
worksheet.set_column(ecr.getPrint_Planned_Heading()[0],
ecr.getPrint_Planned_Heading()[1], 15)
worksheet.write(ecr.getPrint_Planned_Heading()[0],
ecr.getPrint_Planned_Heading()[1], 'PLANNED', format)
worksheet.set_column(ecr.getPrint_Actual_Heading()[0],
ecr.getPrint_Actual_Heading()[1], 15)
worksheet.write(ecr.getPrint_Actual_Heading()[0],
ecr.getPrint_Actual_Heading()[1], 'ACTUAL', format)
except Exception as e:
raise util.DLException(util.__FILE__(), util.__LINE__(), e)
def writeActivityOverview():
try:
global last_activity_row
for i in range(0, total_activities):
last_activity_row = last_activity_row + 1
for k in range(0, 7):
worksheet.write(k, last_activity_row, '', green_color_format)
worksheet.write(last_activity_row, 1, str(finalList[i][3]),
green_color_format)
last_activity_row = last_activity_row + 2
# ---- Write the activity details
worksheet.write(last_activity_row + 1, 1, 'SUBCONTRACTOR:', format)
worksheet.write(last_activity_row + 1, 2, str(finalList[i][7]))
last_activity_row = last_activity_row + 1
worksheet.write(last_activity_row + 1, 1, 'MILESTONE START:',
format)
worksheet.write(last_activity_row + 1, 2, str(finalList[i][8]))
last_activity_row = last_activity_row + 1
worksheet.write(last_activity_row + 1, 1, 'MILESTONE END:', format)
worksheet.write(last_activity_row + 1, 2, str(finalList[i][9]))
last_activity_row = last_activity_row + 1
# worksheet.write(6, 5, str(finalList[0][7]))
worksheet.write(last_activity_row + 1, 1, 'PLANNED DAILY AVG.',
format)
last_activity_row = last_activity_row + 1
worksheet.write(last_activity_row + 1, 1, 'ACTUAL DAILY AVG.',
format)
# last_activity_row = last_activity_row + 1
except Exception as e:
raise util.DLException(util.__FILE__(), util.__LINE__(), e)
def openFile(output):
try:
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
except Exception as e:
raise util.DLException(util.__FILE__(), util.__LINE__(), e)
finally:
return workbook
def openDatabaseConnection():
try:
con = dbu.getConn()
except psycopg2.DatabaseError as error:
raise util.DLException(util.__FILE__(), util.__LINE__(), error)
sys.exit(1)
finally:
return con
def writeActivityDetails():
try:
global last_activity_row
j = int(ecr.getPrint_Activity_Heading())
for i in range(0, total_activities):
col = 0
worksheet.write(j, col, finalList[i][0], format_1)
worksheet.write(j, col + 1, finalList[i][3], format_1)
worksheet.write(j, col + 3, finalList[i][4], format_1)
worksheet.write(j, col + 4, finalList[i][5], format_1)
# Draw the grey color in between the activities
for k in range(0, 7):
worksheet.write(j + 1, k, '', yellow_color_format)
j = j + 2
last_activity_row = j # set the last row that was written to
except Exception as e:
raise util.DLException(util.__FILE__(), util.__LINE__(), e)
def printOverviewHeaders():
try:
totCol = int(ecr.getTotalWorkingColumn())
for i in range(0, totCol):
worksheet.write(0, i, '', green_color_format)
# Get column positions to write the heading
dtHeading = []
dtHeading = ecr.getPrintDateTime()
# --- Write the heading in overview sheet
worksheet.write(dtHeading[0], dtHeading[1],
'TODAY IS: %s' % str(dateTime), green_color_format)
worksheet.set_column('E:E', 40)
worksheet.set_column('F:F', 30)
worksheet.write(ecr.getPrintProjectName_Heading()[0],
ecr.getPrintProjectName_Heading()[1], 'PROJECT',
format)
worksheet.write(ecr.getPrintProjectName_Value()[0],
ecr.getPrintProjectName_Value()[1],
str(finalList[0][2]))
worksheet.write(ecr.getPrintProjectID_Heading()[0],
ecr.getPrintProjectID_Heading()[1], 'PROJECT#', format)
worksheet.write(ecr.getPrintProjectID_Value()[0],
ecr.getPrintProjectID_Value()[1], str(finalList[0][1]))
worksheet.write(ecr.getPrint_ContractorName_Heading()[0],
ecr.getPrint_ContractorName_Heading()[1],
'SUBCONTRACTOR', format)
worksheet.write(ecr.getPrint_ContractorName_Value()[0],
ecr.getPrint_ContractorName_Value()[1],
str(finalList[0][7]))
worksheet.write(ecr.getPrint_Updated_Heading()[0],
ecr.getPrint_Updated_Heading()[1], 'UPDATED', format)
worksheet.write(ecr.getPrint_Updated_Value()[0],
ecr.getPrint_Updated_Value()[1], str(dateTime))
except Exception as e:
raise util.DLException(util.__FILE__(), util.__LINE__(), e)
def getProjectDetails(portfolio_id):
try:
# Get the project level details from the json file
project_name = getItemsChildLevel("project", "name")
project_start_date = getItemsChildLevel("project", "start")
project_end_date = getItemsChildLevel("project", "end")
project_workdays = getItemsChildLevel("project", "workdays")
project_budget = getItemsChildLevel("project", "budget")
project_loc_street1 = getItemsSubChildLevel("project", "location",
"street1")
project_loc_street2 = getItemsSubChildLevel("project", "location",
"street2")
project_loc_city = getItemsSubChildLevel("project", "location", "city")
project_loc_state = getItemsSubChildLevel("project", "location",
"state")
project_loc_postal = getItemsSubChildLevel("project", "location",
"postal")
project_loc_country = getItemsSubChildLevel("project", "location",
"country")
proj_loc_street = project_loc_street1 + " " + project_loc_street2
print(project_workdays)
except Exception as error:
print("Error %s in %s at %d" %
(error, util.__FILE__(), util.__LINE__()))
raise
try:
## Update the details to the database temp
connObj = dbu.getConn()
# --------------------------------------------------------
# Insert data to location table and get location id
# --------------------------------------------------------
start_date = util.formatDate(project_start_date)
end_date = util.formatDate(project_end_date)
execSQL = ('insert_location_data')
execData = (proj_loc_street, project_loc_city, project_loc_state,
project_loc_country, None, None)
project_loc_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0]
print("location id is")
print(project_loc_id)
execSQL = ('insert_project_data')
execData = (project_name, start_date, end_date,
json.dumps(project_workdays), int(project_budget), None,
project_loc_id, None)
project_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0]
## create a row in portfolio_projects to tie both portfolio and the project
execSQL = ('insert_portfolio_projects_data')
execData = (portfolio_id, project_id)
portfolio_project_id = dbu.fetchStoredFuncRes(connObj, execSQL,
execData)[0]
return project_id
except psycopg2.DatabaseError as error:
print("Database Error %s " % error)
raise
except Exception as error:
print("Error %s " % error)
raise
else:
connObj.close()
finally:
print("getProjectDetails")
if type(item_activities) == list:
print('Item activities is a List ---')
print('Calling func. writeActivitiesData_MultipleActivities() ---')
writeActivitiesData_MultipleActivities(project_id)
elif type(item_activities) == dict:
print('Item activities is a dictionary ---')
print('Calling func. writeBundle_Activity() ---')
writeBundle_Activity(project_id)
except util.DLException as error:
traceback.print_exc()
raise
except psycopg2.DatabaseError as error:
print("%s in %s at %d" % (
error.pgerror,
util.__FILE__(),
util.__LINE__(),
))
traceback.print_exc()
raise
except ValueError as ve:
print("%s in %s at %d" % (
ve,
util.__FILE__(),
util.__LINE__(),
))
traceback.print_exc()
raise
else:
## insert file_storage
connObj = dbu.getConn()
# update file_storage in db
rstList = []
conn = openDatabaseConnection()
cur = conn.cursor()
cur1 = conn.cursor()
cur.execute(
"SELECT DISTINCT ACTIVITY_ID,PROJ_ID,PROJ_NAME,ACTIVITY_NAME,UNIT_ID,UNIT_NAME,CONTRACTOR_ID,CONTRACTOR_NAME,PROJ_START,PROJ_END FROM TEMP.PROJECT_ACTIVITIES WHERE ACTIVITY_ID IS NOT NULL ORDER BY ACTIVITY_ID"
)
cur1.execute(
"SELECT COUNT(*) FROM TEMP.PROJECT_ACTIVITIES WHERE ACTIVITY_ID IS NOT NULL"
)
# raise the exception if there is no data fetch from the database
# Throw user-defined exception
lrow = cur1.fetchone()
if lrow[0] == 0:
raise util.DLException(util.__FILE__(), util.__LINE__(),
'No data in view project_activities')
# check whether records received from database based on the query
while True:
row = cur.fetchone()
rstList = []
if row == None:
break
rstList.append(row[0]) #- Activity ID
if row[0] == "":
raise util.DLException(util.__FILE__(), util.__LINE__(),
'ACTIVITY_ID IS NULL')
rstList.append(row[1]) #- Project ID