def writeActivitiesData_MultipleActivities(project_id): activityList = [] connObj = dbu.getConn() node1 = "bundles" node2 = "activities" bundles_name = getItemsChildLevel(len(d), "bundles", "name") bundles_phases = getItemsChildLevel(len(d), "bundles", "phases") execSQL = ('insert_bundles_data') execData = (None, bundles_name, project_id, None) bundle_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] execSQL = ('insert_phases_data') execData = (bundles_phases, None, None, None, None) phase_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] for i in range(0, len(item_activities)): activities_name = getItemsSubChildLevel_List(len(d),node1,node2,"name",i) activities_contractor = getItemsSubChildLevel_List(len(d), node1,node2, "contractor",i) activities_total_planned_hours = getItemsSubChildLevel_List(len(d),node1,node2, "total_planned_hours",i) activities_total_planned_units = getItemsSubChildLevel_List(len(d),node1,node2, "total_planned_units",i) activities_planned_start = getItemsSubChildLevel_List(len(d), node1,node2, "planned_start",i) activities_planned_end = getItemsSubChildLevel_List(len(d),node1,node2, "planned_end",i) activities_actual_start = getItemsSubChildLevel_List(len(d), node1,node2, "actual_start",i) activities_actual_end = getItemsSubChildLevel_List(len(d), node1,node2, "actual_end",i) activities_unit_name = d[len(d) -1]["bundles"]["activities"][i]["unit"]["name"] execSQL = ('insert_units_data') execData = (activities_unit_name,0) unit_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] print ('units id is ') print (unit_id) activities_material = getItemsSubChildLevel_List(len(d), "bundles", "activities", "material",i) # -------------------------------------------------------- # Insert data to project and location table # -------------------------------------------------------- planned_start = datetime.datetime.strptime(activities_planned_start, '%m%d%Y').date().strftime('%Y%m%d') planned_end = datetime.datetime.strptime(activities_planned_end, '%m%d%Y').date().strftime('%Y%m%d') actual_start = datetime.datetime.strptime(activities_actual_start, '%m%d%Y').date().strftime('%Y%m%d') actual_end = datetime.datetime.strptime(activities_actual_end, '%m%d%Y').date().strftime('%Y%m%d') prj_name = getProjectName() execSQL = "INSERT INTO TEMP.ACTIVITIES(NAME,CONTRACTOR_NAME,TOTAL_PLANNED_HOURS,PROJECT_NAME,TOTAL_PLANNED_UNITS,PLANNED_START,PLANNED_END,UNIT_NAME,ACTUAL_START,ACTUAL_END) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);" execData = (activities_name, activities_contractor, activities_total_planned_hours, prj_name,activities_total_planned_units,planned_start, planned_end, activities_unit_name, actual_start, actual_end) dbu.executeQueryWithData(connObj, execSQL, execData) print('#####################') print(execSQL) print(execData) print('writing values') print('Bundle Name : %s' %bundles_name) print('Bundle Phases : %s' %bundles_phases) print ('Activity Name : %s' %activities_name) print ('Activity Unit Name : %s' %activities_unit_name) print ('Activity Material: %s' %activities_material) print('#####################')
def writeBundle_Activity(project_id): ## Update the details to the database temp connObj = dbu.getConn() bundles_name = getItemsChildLevel(len(d),"bundles","name") bundles_phases = getItemsChildLevel(len(d), "bundles", "phases") execSQL = ('insert_bundles_data') execData = (None, bundles_name, project_id, None) bundle_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] execSQL = ('insert_phases_data') execData = (bundles_phases, None, None, None, None) phase_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] activities_name = getItemsSubChildLevel("bundles","activities","name") activities_contractor = getItemsSubChildLevel("bundles", "activities", "contractor") activities_total_planned_hours = getItemsSubChildLevel("bundles", "activities", "total_planned_hours") activities_total_planned_units = getItemsSubChildLevel("bundles", "activities", "total_planned_units") activities_planned_start = getItemsSubChildLevel("bundles", "activities", "planned_start") activities_planned_end = getItemsSubChildLevel("bundles", "activities", "planned_end") activities_actual_start = getItemsSubChildLevel("bundles", "activities", "actual_start") activities_actual_end = getItemsSubChildLevel("bundles", "activities", "actual_end") activities_unit_name = d[len(d)-1]["bundles"]["activities"]["unit"]["name"] execSQL = ('insert_units_data') execData = (activities_unit_name,0) unit_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] print('units id is ') print(unit_id) activities_material = getItemsSubChildLevel("bundles", "activities", "material") # -------------------------------------------------------- # Insert data to activities # -------------------------------------------------------- planned_start = datetime.datetime.strptime(activities_planned_start, '%m%d%Y').date().strftime('%Y%m%d') planned_end = datetime.datetime.strptime(activities_planned_end, '%m%d%Y').date().strftime('%Y%m%d') actual_start = datetime.datetime.strptime(activities_actual_start, '%m%d%Y').date().strftime('%Y%m%d') actual_end = datetime.datetime.strptime(activities_actual_end, '%m%d%Y').date().strftime('%Y%m%d') prj_name = getProjectName() execSQL = "INSERT INTO TEMP.ACTIVITIES(NAME,CONTRACTOR_NAME,TOTAL_PLANNED_HOURS,PROJECT_NAME,TOTAL_PLANNED_UNITS,PLANNED_START,PLANNED_END,UNIT_NAME,ACTUAL_START,ACTUAL_END) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);" execData = (activities_name,activities_contractor,activities_total_planned_hours,prj_name,activities_total_planned_units,planned_start,planned_end,activities_unit_name,actual_start,actual_end) print(execSQL) print(execData) dbu.executeQueryWithData(connObj, execSQL, execData) connObj.close() print('#####################') print('writing values') print('Bundle Name : %s' % bundles_name) print('Bundle Phases : %s' % bundles_phases) print('Activity Name : %s' % activities_name) print('Activity Unit Name : %s' % activities_unit_name) print('Activity Material: %s' % activities_material) print('#####################')
def writeBundleList_DictActivity(bundle_item, project_id): connObj = dbu.getConn() print('I am entering fn writeBundleList_DictActivity(i) ---') bundles_name = d[len(d) -1]["bundles"][bundle_item]["name"] bundles_phases = d[len(d) -1]["bundles"][bundle_item]["phases"] execSQL = ('insert_bundles_data') execData = (None, bundles_name, project_id, None) bundle_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] execSQL = ('insert_phases_data') execData = (bundles_phases, None, None, None, None) phase_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] activities_name = getwriteBundleList_DictActivity("bundles","activities","name",bundle_item) activities_contractor = getwriteBundleList_DictActivity("bundles","activities", "contractor",bundle_item) activities_total_planned_hours = getwriteBundleList_DictActivity("bundles","activities", "total_planned_hours",bundle_item) activities_total_planned_units = getwriteBundleList_DictActivity("bundles","activities", "total_planned_units",bundle_item) activities_planned_start = getwriteBundleList_DictActivity("bundles", "activities", "planned_start",bundle_item) activities_planned_end = getwriteBundleList_DictActivity("bundles","activities", "planned_end",bundle_item) activities_actual_start = getwriteBundleList_DictActivity("bundles","activities", "actual_start",bundle_item) activities_actual_end = getwriteBundleList_DictActivity("bundles","activities", "actual_end",bundle_item) activities_unit_name = d[len(d)-1]["bundles"][bundle_item]["activities"]["unit"]["name"] execSQL = ('insert_units_data') execData = (activities_unit_name,0) unit_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] print('units id is ') print(unit_id) activities_material = getwriteBundleList_DictActivity("bundles","activities", "material",bundle_item) # -------------------------------------------------------- # Insert data to project and location table # -------------------------------------------------------- planned_start = datetime.datetime.strptime(activities_planned_start, '%m%d%Y').date().strftime('%Y%m%d') planned_end = datetime.datetime.strptime(activities_planned_end, '%m%d%Y').date().strftime('%Y%m%d') actual_start = datetime.datetime.strptime(activities_actual_start, '%m%d%Y').date().strftime('%Y%m%d') actual_end = datetime.datetime.strptime(activities_actual_end, '%m%d%Y').date().strftime('%Y%m%d') prj_name = getProjectName() execSQL = "INSERT INTO TEMP.ACTIVITIES(NAME,CONTRACTOR_NAME,TOTAL_PLANNED_HOURS,PROJECT_NAME,TOTAL_PLANNED_UNITS,PLANNED_START,PLANNED_END,UNIT_NAME,ACTUAL_START,ACTUAL_END) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);" execData = (activities_name, activities_contractor, activities_total_planned_hours, prj_name,activities_total_planned_units,planned_start, planned_end, activities_unit_name, actual_start, actual_end) dbu.executeQueryWithData(connObj, execSQL, execData) print('###############-- writeBundleList_DictActivity() --######') print('writing values') print('Bundle Name : %s' % bundles_name) print('Bundle Phases : %s' % bundles_phases) print('Activity Name : %s' % activities_name) print('Activity Unit Name : %s' % activities_unit_name) print('Activity Material: %s' % activities_material) print('#####################') connObj.close()
def insertActivityPredecessor(): try: # First check to make sure the final_wbs_list is not empty if (len(task_predecessor)) == 0: raise ErrorOccured( "Empty task_predecessor[] in function insertActivityPredecessor()" ) print( datetime.now(), '<< INSERTING ACTIVITIY DEPENDENVY DATA in ACTIVITY_DEPENDENCIES table: insertActivityPredecessor() >>' ) print("\n", file=tempOutFile) db_conn = dbu.getConn() print(len(task_predecessor)) for i in range(0, len(task_predecessor)): activityID = findActivityIDForGivenClientTaskID( task_predecessor[i][0]) activityID_Pred = findActivityIDForGivenClientTaskID( task_predecessor[i][1]) if activityID_Pred != None: print( "----- INSERT Statements for ACTIVITY DEPENDENCIES ------------------", file=tempOutFile) execSQL = ('insert_activity_dependencies_data') execData = (activityID, activityID_Pred) print(execSQL, execData, file=tempOutFile) l_id = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] print( datetime.now(), '<< FINISHED: INSERTING ACTIVITIY DEPENDENVY DATA in ACTIVITY_DEPENDENCIES table: insertActivityPredecessor() >>' ) except (Exception) as error: print('Error in insertActivityPredecessor()', error) print(sys.exc_traceback.tb_lineno)
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 getContractorDetails_Dict(): try: ## Update the details to the database temp connObj = dbu.getConn() # Get the project level details from the json file contractor_name = getItemsChildLevel("contractors", "name") contractor_email = getItemsChildLevel("contractors", "email") contractor_phone = getItemsChildLevel("contractors", "phone") contractor_pcontact = getItemsChildLevel("contractors", "primary_contact") # -------------------------------------------------------- # Insert data to public.contractors # -------------------------------------------------------- execSQL = ('insert_contractor_data') execData = (contractor_name, contractor_email, contractor_phone, contractor_pcontact) contractor_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] 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("getContractorDetails_Dict")
def insertPhases(): try: # first read the phaseID_data [] # then start inserting items into the phases table # get database connection db_conn = dbu.getConn() print( "-------Writing PHASES Data to PHASES Table --------------------", file=tempOutFile) for i in range(0, len(phaseID_data)): lPhaseName = phaseID_data[i][1] lPhaseID = phaseID_data[i][0] print("----- INSERT Statements for Phases ------------------", file=tempOutFile) execSQL = ('insert_phases_data') execData = (lPhaseName, None, None, None, None, ProjectID, None, None) print(execSQL, execData, file=tempOutFile) lCurrentPhaseID = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] #store the db_phaseID along with client wbsID in the phases_dict phases_dict.update({lPhaseID: lCurrentPhaseID}) print('Phases Dictionary :', phases_dict) except (Exception) as error: print('Error in insertPhases() %s:' % error)
def insertPhases(): try: # First check to make sure the phasesID_Data[] is not empty if (len(phaseID_data)) == 0: raise ErrorOccured("Empty phaseID_Data[] in function insertPhases") print(datetime.now(),'<< INSERTING PHASES DATA in PHASES TABLE :- insertPhases() >>') # first read the phaseID_data [] # then start inserting items into the phases table # get database connection db_conn = dbu.getConn() print(datetime.now(),"-------Writing PHASES Data to PHASES Table --------------------", file=tempOutFile) for i in range(0, len(phaseID_data)): lPhaseName = phaseID_data[i][1] lPhaseID = phaseID_data[i][0] print(datetime.now(),"----- INSERT Statements for Phases ------------------", file=tempOutFile) execSQL = ('insert_phases_data') execData = (lPhaseName,None,None,None,None,ProjectID,None,None) print(execSQL, execData, file=tempOutFile) lCurrentPhaseID = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] #store the db_phaseID along with client wbsID in the phases_dict phases_dict.update({lPhaseID: lCurrentPhaseID}) #print('Phases Dictionary :', phases_dict) print(datetime.now(),'<< FINISHED: INSERTING PHASES DATA in PHASES TABLE :- insertPhases() >>') except (Exception,ErrorOccured) as error: print(datetime.now(),'Error in insertPhases() %s:' %error)
def getContractorDetails_List(): try: connObj = dbu.getConn() lengthList = len(d) for i in range(0, len(d[lengthList - 1]["contractors"])): # Get the project level details from the json file contractor_name = d[lengthList - 1]["contractors"][i]["name"] contractor_email = d[lengthList - 1]["contractors"][i]["email"] contractor_phone = d[lengthList - 1]["contractors"][i]["phone"] contractor_pcontact = d[lengthList - 1]["contractors"][i]["primary_contact"] # -------------------------------------------------------- # Insert data to public.contractors # -------------------------------------------------------- execSQL = ('insert_contractor_data') execData = (contractor_name, contractor_email, contractor_phone, contractor_pcontact) contractor_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] 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("getContractorDetails_List")
def insertProjectData(prjName): try: db_conn = dbu.getConn() execSQL = ('insert_project_data') execData = (prjName, None, None, None, None, None, None, None) print(execSQL, execData) prjID = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] db_conn.close() return prjID except (Exception, psycopg2.DatabaseError) as error: print(datetime.now(), "Database Error %s " % error) print(sys.exc_traceback.tb_lineno) raise
def getProjectDetails(): # 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) ## Update the details to the database temp connObj = dbu.getConn() # -------------------------------------------------------- # Insert data to location table and get location id # -------------------------------------------------------- start_date = datetime.datetime.strptime(project_start_date,'%m%d%Y').date().strftime('%Y%m%d') end_date = datetime.datetime.strptime(project_end_date,'%m%d%Y').date().strftime('%Y%m%d') 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] connObj.close() return project_id
def getContractorDetails_Dict(): ## Update the details to the database temp connObj = dbu.getConn() # Get the project level details from the json file contractor_name = getItemsChildLevel("contractors","name") contractor_email = getItemsChildLevel("contractors","email") contractor_phone = getItemsChildLevel("contractors","phone") contractor_pcontact = getItemsChildLevel("contractors","primary_contact") # -------------------------------------------------------- # Insert data to public.contractors # -------------------------------------------------------- execSQL = ('insert_contractor_data') execData = (contractor_name, contractor_email, contractor_phone, contractor_pcontact) contractor_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] connObj.close()
def getContractorDetails_List(): connObj = dbu.getConn() lengthList = len(d) for i in range(0,len(d[lengthList -1]["contractors"])): # Get the project level details from the json file contractor_name = d[lengthList-1]["contractors"][i]["name"] contractor_email = d[lengthList-1]["contractors"][i]["email"] contractor_phone = d[lengthList-1]["contractors"][i]["phone"] contractor_pcontact = d[lengthList-1]["contractors"][i]["primary_contact"] # -------------------------------------------------------- # Insert data to public.contractors # -------------------------------------------------------- execSQL = ('insert_contractor_data') execData = (contractor_name, contractor_email, contractor_phone, contractor_pcontact) contractor_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] connObj.close()
def getProjectAndPhaseData(): try: print('Reading project info') ## Get project info and insert it into the database global projectID, phaseID projectName = activeSheet.cell_value(2, 1).split(":")[1] projectID = insertProjectData(projectName) ## Get phase information and insert it into the database db_conn = dbu.getConn() phaseName = activeSheet.cell_value(4, 1).split(":")[1] execSQL = ('insert_phases_data') execData = (phaseName, projectID) print(execSQL, execData) phaseID = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] db_conn.close() except (Exception) as error: print(datetime.now(), error) raise
def insertProjectData(prjName): try: print( datetime.now(), '<< Getting ProjectID from PROJECT TABLE : insertProjectData() >>') # get database connection db_conn = dbu.getConn() 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) print(sys.exc_traceback.tb_lineno) raise
def getPortfolioDetails(): portfolio_name = "Default" try: ## Update the details to the database temp connObj = dbu.getConn() # -------------------------------------------------------- # Insert data to profolios table # -------------------------------------------------------- execSQL = ('insert_portfolios_data') execData = (portfolio_name, 0) portfolio_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] return portfolio_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("getPortfolioDetails")
def insertActivity(): try: # First check to make sure the final_wbs_list is not empty if (len(final_task_list)) == 0: raise ErrorOccured( "Empty final_task_list in function insertActivity()") print( datetime.now(), '<< INSERTING ACTIVITIES DATA in ACTIVITIES table: insertActivity() >>' ) print("\n", file=tempOutFile) db_conn = dbu.getConn() # first remove empty list from result_data i.e, if there are empty rows in the excel totalRec = len(final_task_list) localProjectID = ProjectID for i in range(1, totalRec): local_list = [] LactID = [] activityId_temp = final_task_list[i][0] activity_taskCode = final_task_list[i][16] #getting the dbPhaseID based on the client activity phaseID_activityID = findPhaseValue(activityId_temp) activityExternalID = int(activityId_temp) bundleID = final_task_list[i][1] activity_name_temp = final_task_list[i][2] activityName = activity_name_temp actualStDate = final_task_list[i][5] actualEndDate = final_task_list[i][6] plannedStDate = final_task_list[i][13] plannedEndDate = final_task_list[i][14] # we are capturing the total actual units and putting it in total_planned_hours total_actual_units_temp = float(final_task_list[i][3]) total_actual_units = round(total_actual_units_temp) total_planned_units_temp = float(final_task_list[i][4]) total_planned_units = round(total_planned_units_temp) #Find out if the activity is a mileStone isMileStone = findIfMileStone(activityId_temp) # If the Actual StartDate and Actual EndDate are null [29],[30] # then take late StartDate and late EndDate [31],[32] if actualStDate == "": actualStDate = None if actualEndDate == "": actualEndDate = None if plannedStDate == "": plannedStDate = None if plannedEndDate == "": plannedEndDate = None print( datetime.now(), "----- INSERT Statements for activities() ------------------", file=tempOutFile) execSQL = ('insert_activities_data') execData = (activityName, None, None, None, None, phaseID_activityID, localProjectID, total_planned_units, plannedStDate, plannedEndDate, None, None, None, activity_taskCode, None, isMileStone, None, None, None, None, None) print(execSQL, execData, file=tempOutFile) lCurrentActivityID = dbu.fetchStoredFuncRes( db_conn, execSQL, execData)[0] local_list.append(lCurrentActivityID) local_list.append(plannedStDate) local_list.append(plannedEndDate) local_list.append(total_actual_units) local_list.append(total_planned_units) # inserting the db_activityID and client_ActivityID in to activityID_clientActivityID [] # This data is required for inserting values into activity_dependencies table LactID.append(lCurrentActivityID) LactID.append(activityId_temp) activityID_clientActivityID.append(LactID) # contains the db_activityID, planned start date and planned end date # This list is used for expanding dates into the activity_data table # This list will be used by expandDates() expand_dates_list.append(local_list) #Now get the db_bundle id from the dictionary and insert into the corresponding bundle_activity table db_BundleID = bundle_dict.get(bundleID) # Bundle activities table insert print( datetime.now(), "----- INSERT Statements for BUNDLE_ACTIVITIES ------------------", file=tempOutFile) execSQL = ('insert_bundle_activities_data') execData = (db_BundleID, lCurrentActivityID) print(execSQL, execData, file=tempOutFile) dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] print('-------- Task Predecessor List -------------') print(activityID_clientActivityID, file=tempOutFile) print( datetime.now(), '<< FINISHED: INSERTING ACTIVITIES DATA in ACTIVITIES table: insertActivity() >>' ) except (Exception, ErrorOccured) as error: print("Error in insertActivity:%s" % error) print(sys.exc_traceback.tb_lineno) except (psycopg2) as dberror: print(dberror) print(sys.exc_traceback.tb_lineno)
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
def writeBundle_Activity(project_id): try: ## Update the details to the database temp connObj = dbu.getConn() bundles_name = getItemsChildLevel("bundles", "name") bundles_phases = getItemsChildLevel("bundles", "phases") execSQL = ('insert_bundles_data') execData = (None, bundles_name, project_id, None) bundle_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] execSQL = ('insert_phases_data') execData = (bundles_phases, None, None, None, None) phase_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] activities_name = getItemsSubChildLevel("bundles", "activities", "name") activities_contractor = getItemsSubChildLevel("bundles", "activities", "contractor") activities_total_planned_hours = getItemsSubChildLevel( "bundles", "activities", "total_planned_hours") activities_total_planned_units = getItemsSubChildLevel( "bundles", "activities", "total_planned_units") activities_planned_start = getItemsSubChildLevel( "bundles", "activities", "planned_start") activities_planned_end = getItemsSubChildLevel("bundles", "activities", "planned_end") activities_actual_start = getItemsSubChildLevel( "bundles", "activities", "actual_start") activities_actual_end = getItemsSubChildLevel("bundles", "activities", "actual_end") activities_unit_name = d[len(d) - 1]["bundles"]["activities"]["unit"]["name"] execSQL = ('insert_units_data') execData = (activities_unit_name, 0) unit_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] print('units id is ') print(unit_id) activities_material = getItemsSubChildLevel("bundles", "activities", "material") # -------------------------------------------------------- # Insert data to activities # -------------------------------------------------------- planned_start = util.formatDate(activities_planned_start) planned_end = util.formatDate(activities_planned_end) actual_start = util.formatDate(activities_actual_start) actual_end = util.formatDate(activities_actual_end) prj_name = getProjectName() execSQL = ('insert_activities_data') execData = (activities_name, unit_id, activities_contractor, None, activities_total_planned_hours, phase_id, project_id, activities_total_planned_units, planned_start, planned_end, activities_unit_name, actual_start, actual_end, None) print(execData) activities_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] print('#####################') print('writing values') print('Bundle Name : %s' % bundles_name) print('Bundle Phases : %s' % bundles_phases) print('Activity Name : %s' % activities_name) print('Activity Unit Name : %s' % activities_unit_name) print('Activity Material: %s' % activities_material) print('#####################') 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("writeBundle_Activity")
def writeBundleList_DictActivity(bundle_item, project_id): try: connObj = dbu.getConn() print('I am entering fn writeBundleList_DictActivity(i) ---') bundles_name = d[len(d) - 1]["bundles"][bundle_item]["name"] bundles_phases = d[len(d) - 1]["bundles"][bundle_item]["phases"] execSQL = ('insert_bundles_data') execData = (None, bundles_name, project_id, None) bundle_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] execSQL = ('insert_phases_data') execData = (bundles_phases, None, None, None, None) phase_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] activities_name = getwriteBundleList_DictActivity( "bundles", "activities", "name", bundle_item) activities_contractor = getwriteBundleList_DictActivity( "bundles", "activities", "contractor", bundle_item) activities_total_planned_hours = getwriteBundleList_DictActivity( "bundles", "activities", "total_planned_hours", bundle_item) activities_total_planned_units = getwriteBundleList_DictActivity( "bundles", "activities", "total_planned_units", bundle_item) activities_planned_start = getwriteBundleList_DictActivity( "bundles", "activities", "planned_start", bundle_item) activities_planned_end = getwriteBundleList_DictActivity( "bundles", "activities", "planned_end", bundle_item) activities_actual_start = getwriteBundleList_DictActivity( "bundles", "activities", "actual_start", bundle_item) activities_actual_end = getwriteBundleList_DictActivity( "bundles", "activities", "actual_end", bundle_item) activities_unit_name = d[ len(d) - 1]["bundles"][bundle_item]["activities"]["unit"]["name"] execSQL = ('insert_units_data') execData = (activities_unit_name, 0) unit_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] print('units id is ') print(unit_id) activities_material = getwriteBundleList_DictActivity( "bundles", "activities", "material", bundle_item) # -------------------------------------------------------- # Insert data to project and location table # -------------------------------------------------------- planned_start = util.formatDate(activities_planned_start) planned_end = util.formatDate(activities_planned_end) actual_start = util.formatDate(activities_actual_start) actual_end = util.formatDate(activities_actual_end) prj_name = getProjectName() execSQL = ('insert_activities_data') execData = (activities_name, unit_id, activities_contractor, None, activities_total_planned_hours, phase_id, project_id, activities_total_planned_units, planned_start, planned_end, activities_unit_name, actual_start, actual_end, None) print(execData) activities_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] print('###############-- writeBundleList_DictActivity() --######') print('writing values') print('Bundle Name : %s' % bundles_name) print('Bundle Phases : %s' % bundles_phases) print('Activity Name : %s' % activities_name) print('Activity Unit Name : %s' % activities_unit_name) print('Activity Material: %s' % activities_material) print("Activities ID: %d" % activities_id) print('#####################') 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("writeBundleList_DictActivity")
def writeActivitiesData_MultiBundles_MultipleActivities( bundle_item, project_id): try: activityList = [] node1 = "bundles" node2 = "activities" ## Update the details to the database temp connObj = dbu.getConn() #write the values of bundles bundles_name = d[len(d) - 1][node1][bundle_item]["name"] bundles_phases = d[len(d) - 1][node1][bundle_item]["phases"] execSQL = ('insert_bundles_data') execData = (None, bundles_name, project_id, None) bundle_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] execSQL = ('insert_phases_data') execData = (bundles_phases, None, None, None, None) phase_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] #L_item_activities = d[lengthList - 1]["bundles"][bundle_item]["activities"] for i in range(0, len(item_activities)): activities_name = getItems_MultiBundle_MultiActivity( node1, node2, "name", bundle_item, i) activities_contractor = getItems_MultiBundle_MultiActivity( node1, node2, "contractor", bundle_item, i) activities_total_planned_hours = getItems_MultiBundle_MultiActivity( node1, node2, "total_planned_hours", bundle_item, i) activities_total_planned_units = getItems_MultiBundle_MultiActivity( node1, node2, "total_planned_units", bundle_item, i) activities_planned_start = getItems_MultiBundle_MultiActivity( node1, node2, "planned_start", bundle_item, i) activities_planned_end = getItems_MultiBundle_MultiActivity( node1, node2, "planned_end", bundle_item, i) activities_actual_start = getItems_MultiBundle_MultiActivity( node1, node2, "actual_start", bundle_item, i) activities_actual_end = getItems_MultiBundle_MultiActivity( node1, node2, "actual_end", bundle_item, i) activities_unit_name = d[ len(d) - 1]["bundles"][bundle_item]["activities"][i]["unit"]["name"] execSQL = ('insert_units_data') execData = (activities_unit_name, 0) print(execData) print(execSQL) unit_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] print('units id is ') print(unit_id) activities_material = getItems_MultiBundle_MultiActivity( node1, node2, "material", bundle_item, i) print(activities_planned_start) # -------------------------------------------------------- # Insert data to project and location table # -------------------------------------------------------- planned_start = util.formatDate(activities_planned_start) planned_end = util.formatDate(activities_planned_end) actual_start = util.formatDate(activities_actual_start) actual_end = util.formatDate(activities_actual_end) prj_name = getProjectName() execSQL = ('insert_activities_data') execData = (activities_name, unit_id, activities_contractor, None, activities_total_planned_hours, phase_id, project_id, activities_total_planned_units, planned_start, planned_end, activities_unit_name, actual_start, actual_end, None) print(execData) activities_id = dbu.fetchStoredFuncRes(connObj, execSQL, execData)[0] print('#####################-- MultiBundle-MultiActivity----') print(execSQL) print(execData) print('writing values') print('Bundle Name : %s' % bundles_name) print('Bundle Phases : %s' % bundles_phases) print('Activity ID : %d' % activities_id) print('Activity Name : %s' % activities_name) print('Activity Unit Name : %s' % activities_unit_name) print('Activity Material: %s' % activities_material) print('#####################') 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("writeActivitiesData_MultiBundles_MultipleActivities")
def getBundlesAndActivities(): try: row_count = wb.sheet_by_index(sheetIndex).nrows db_conn = dbu.getConn() ## Get Parent bundle inserted bundleName = activeSheet.cell_value(3, 1).split(":")[1] execSQL = ('insert_bundles_data') execData = (None, bundleName, projectID, None, phaseID, None) # print(execSQL, execData) # parentBundleID = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] bundleID = 0 parentBundleID = None for i in range(7, row_count): if (activeSheet.cell_value( i, 1) == 'Grand Total'): ## End of activities break if (activeSheet.cell_value(i, 1).find('Total') == -1 ): ## Ignore rows that are sub totals ## Check to see if the row is a bundle or activity ## If its a bundle insert into bundles database if activeSheet.cell_value(i, 2) == '': ## This is a bundle execSQL = ('insert_bundles_data') clientBundleID = str(activeSheet.cell_value(i, 0)) bundleName = activeSheet.cell_value(i, 1) execData = (parentBundleID, bundleName, projectID, None, phaseID, clientBundleID) print(execSQL, execData) bundleID = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] continue ## Get activities and tie them to bundles clientActivityID = str(activeSheet.cell_value(i, 0)) activityName = activeSheet.cell_value(i, 1) plannedStart = None plannedEnd = None if (activeSheet.cell_value(i, 2) != 'NA'): plannedStart = xlrd.xldate.xldate_as_datetime( activeSheet.cell_value(i, 2), wb.datemode).strftime('%Y%m%d') if (activeSheet.cell_value(i, 3) != 'NA'): plannedEnd = xlrd.xldate.xldate_as_datetime( activeSheet.cell_value(i, 3), wb.datemode).strftime('%Y%m%d') ##Get units ID unitName = activeSheet.cell_value(i, 4) execSQL = ('insert_units_data') execData = (unitName, None) print(execSQL, execData) unitID = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] plannedUnits = activeSheet.cell_value(i, 5) if plannedUnits == '' or plannedUnits == 'NA': plannedUnits = 0 actualUnits = activeSheet.cell_value(i, 6) if actualUnits == '' or actualUnits == 'NA': actualUnits = 0 execSQL = ('insert_activities_data') execData = (activityName, unitID, None, None, None, phaseID, projectID, plannedUnits, plannedStart, plannedEnd, unitName, None, None, clientActivityID, None, None, None, None, None, None, None) print(execSQL, execData) activityID = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] ## Insert into bundle_activities execSQL = ('insert_bundle_activities_data') execData = (bundleID, activityID) print(execSQL, execData) dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] ## Now insert into activity_data insertActivityData(activityID, plannedStart, plannedEnd, plannedUnits, actualUnits) except (Exception) as error: print(datetime.now(), error) raise
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")
def insertBundlesData(): try: # get database connection db_conn = dbu.getConn() lParentBundleID = None print("-------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] # 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) #Get the phase id to be inserted into the bundle table from phases_dict #if lBundleID in phases_dict: # lPhaseID = phases_dict[lBundleID] print("----- INSERT Statements for Bundles ------------------", file=tempOutFile) execSQL = ('insert_bundles_data') execData = (lParentBundleID, lBundleName, ProjectID, None, lPhaseID) 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('-------------Bundle List ----------------------', file=tempOutFile) print(bundle_list, file=tempOutFile) print('-------------Bundle Dictionary ----------------------', file=tempOutFile) print(bundle_dict, file=tempOutFile) except (Exception, psycopg2.DatabaseError) as error: print("Database Error in insertBundlesData() %s " % error) raise
def insertActivity(): try: print("\n", file=tempOutFile) db_conn = dbu.getConn() # first remove empty list from result_data i.e, if there are empty rows in the excel totalRec = len(final_task_list) for i in range(1, totalRec): local_list = [] localProjectID = ProjectID[0] activityId_temp = final_task_list[i][0] activity_taskCode = final_task_list[i][16] #getting the dbPhaseID based on the client activity phaseID_activityID = findPhaseValue(activityId_temp) activityExternalID = int(activityId_temp) bundleID = final_task_list[i][1] activity_name_temp = final_task_list[i][2] activityName = activity_name_temp actualStDate = final_task_list[i][5] actualEndDate = final_task_list[i][6] plannedStDate = final_task_list[i][13] plannedEndDate = final_task_list[i][14] total_planned_units_temp = float(final_task_list[i][4]) total_planned_units = round(total_planned_units_temp) #Find out if the activity is a mileStone isMileStone = findIfMileStone(activityId_temp) # If the Actual StartDate and Actual EndDate are null [29],[30] # then take late StartDate and late EndDate [31],[32] if actualStDate == "": actualStDate = None if actualEndDate == "": actualEndDate = None if plannedStDate == "": plannedStDate = None if plannedEndDate == "": plannedEndDate = None print( "----- INSERT Statements for activities() ------------------", file=tempOutFile) execSQL = ('insert_activities_data') execData = (activityName, None, None, None, None, phaseID_activityID, localProjectID, total_planned_units, plannedStDate, plannedEndDate, None, actualStDate, actualEndDate, None, None, activity_taskCode, None, isMileStone, None, None, None) print(execSQL, execData, file=tempOutFile) lCurrentActivityID = dbu.fetchStoredFuncRes( db_conn, execSQL, execData)[0] local_list.append(lCurrentActivityID) local_list.append(plannedStDate) local_list.append(plannedEndDate) # contains the db_activityID, planned start date and planned end date # This list is used for expanding dates into the activity_data table # This list will be used by expandDates() expand_dates_list.append(local_list) #Now get the db_bundle id from the dictionary and insert into the corresponding bundle_activity table db_BundleID = bundle_dict.get(bundleID) # Bundle activities table insert print( "----- INSERT Statements for BUNDLE_ACTIVITIES ------------------", file=tempOutFile) execSQL = "INSERT INTO BUNDLE_ACTIVITIES (ACTIVITY_ID,BUNDLE_ID) VALUES (%s,%s);" execData = (lCurrentActivityID, db_BundleID) print(execSQL, execData, file=tempOutFile) dbu.executeQueryWithData(db_conn, execSQL, execData) except (Exception) as error: print("Error in insertActivity:%s" % error) except (psycopg2) as dberror: print(dberror)