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 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,total_actual_units,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) 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) except (psycopg2) as dberror: print(dberror)
def expandDates(): try: print( datetime.now(), '<< EXPANDING DATES for ACTIVITY_DATA Table :- expandDates() >>') if (len(expand_dates_list)) == 0: raise ErrorOccured("Empty result List") #get database connection db_conn = dbu.getConn() # get the total workdays in a week tWdays = ecr.getTotalWorkdays() planned_hours = 8 # assign planned hours per day as 8 hours totalRecords = len(expand_dates_list) #counter=0 print("\n", file=actvity_txtFile) print(datetime.now(), "#### Printing insert query for activity_data ######", file=actvity_txtFile) ## Truncate temp.activity_data. We will insert rows into this table ## and then call a stored function to transfer them into activity_data table #execSQL = "TRUNCATE TABLE activity_data" #dbu.executeQuery(db_conn, execSQL) execSQL = """delete from activity_data ad using activities a where ad.activity_id = a.id and a.project_id = '{id}';""" execSQL = execSQL.format(id=ProjectID) dbu.executeQuery(db_conn, execSQL) for i in range(0, totalRecords): workingDayCount = 0 # this variable to store the total working days for a given activityID activityN = expand_dates_list[i][0] startDate = expand_dates_list[i][1] TendDate = expand_dates_list[i][2] totalActualUnits = expand_dates_list[i][3] totalPlannedUnits = expand_dates_list[i][4] dtDate = datetime.strptime(startDate, '%Y-%m-%d %H:%M') enddtDate = datetime.strptime(TendDate, '%Y-%m-%d %H:%M') #Now for each activity, expand the dates startDate until end date # and insert into the activities_data table dd = [ dtDate + timedelta(days=x) for x in range((enddtDate - dtDate).days + 1) ] print(dd, file=actvity_txtFile) for d in dd: #execSQL = ('insert_activity_data_data') execSQL = "INSERT INTO ACTIVITY_DATA (ACTIVITY_ID,DATE,PLANNED_HOURS) VALUES (%s,%s,%s);" # get the weekday wDay = getDayofWeek(d) dstat = 'w' if holidayFlag == 1: dstat = checkIfHoliday(d) planned_hours = 8 dt = datetime.date(d) if tWdays == '5': # if its a 5 day work week if dstat == 'w': # if its not a holiday if wDay == 0 or wDay == 1 or wDay == 2 or wDay == 3 or wDay == 4: #monday - friday # activities table insert workingDayCount = workingDayCount + 1 execData = (activityN, d, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) print(execSQL, execData, file=actvity_txtFile) #counter = counter + 1 #comment this line in production elif wDay == 5 or wDay == 6: # if it is a saturday or sunday, insert a NONE for the planned hours planned_hours = None #execData = (activityN, dt, None, None, None, planned_hours, None, None) #l_id = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] execData = (activityN, d, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) print(execSQL, execData, file=actvity_txtFile) #counter = counter + 1 # comment this line in production elif dstat == 'h': # if it is a holiday, insert a NONE for the planned hours planned_hours = None #execData = (activityN, dt, None, None, None, planned_hours, None, None) #l_id = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] execData = (activityN, d, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) print(execSQL, execData, file=actvity_txtFile) # insert the planned units and actual units elif tWdays == '6': # if its a 6 day work week : monday to Saturday if dstat == 'w': if wDay == 0 or wDay == 1 or wDay == 2 or wDay == 3 or wDay == 4 or wDay == 5: workingDayCount = workingDayCount + 1 #execData = (activityN, dt, None, None, None, planned_hours, None, None) #l_id = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] execData = (activityN, d, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) print(execSQL, execData, file=actvity_txtFile) #counter = counter + 1 #comment this line in production elif wDay == 6: # if it is a saturday or sunday, insert a NONE for the planned hours planned_hours = None #execData = (activityN, dt, None, None, None, planned_hours, None, None) #l_id = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] execData = (activityN, d, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) print(execSQL, execData, file=actvity_txtFile) elif dstat == 'h': # if it is a holiday, insert a NONE for the planned hours planned_hours = None #execData = (activityN, dt, None, None, None, planned_hours, None, None) #l_id = dbu.fetchStoredFuncRes(db_conn, execSQL, execData)[0] execData = (activityN, d, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) print(execSQL, execData, file=actvity_txtFile) #now divide the planned units and actual units by the total working day count # this calculation goes into table activity_data : planned_units and actual_units # if totalPlannedUnits from xer file is zero then we can just insert the planned units to Zero # Also the workingDayCount cannot be zero else it will lead to zero division error # this condition arises when the given activity is scheduled for a weekend and if the # working week is a 5 day week. # First calculate for planned units if totalPlannedUnits == 0 or workingDayCount == 0: dailyPlannedUnits = 0 elif totalPlannedUnits != 0 and workingDayCount != 0: dailyPlannedUnits = (totalPlannedUnits / workingDayCount) #Next calculate for Actual Units if totalActualUnits == 0 or workingDayCount == 0: dailyActualUnits = 0 elif totalActualUnits != 0 and workingDayCount != 0: dailyActualUnits = (totalActualUnits / workingDayCount) print('ActivityID=', activityN, 'Total Working Days=', workingDayCount, 'Planned Units=', dailyPlannedUnits, 'Actual Units=', dailyActualUnits, file=actvity_txtFile) # now update the actual units and planned units for each date based on the calculation execSQL = "UPDATE ACTIVITY_DATA SET PLANNED_UNITS=%s, ACTUAL_UNITS=%s WHERE ACTIVITY_ID=%s AND" \ " PLANNED_HOURS=%s;" execData = (dailyPlannedUnits, dailyActualUnits, activityN, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) print(execSQL, execData, file=actvity_txtFile) print( datetime.now(), '<< FINISHED : EXPANDING DATES for ACTIVITY_DATA Table :- expandDates() >>' ) except (Exception) as error: print(datetime.now(), "Error in expandDates(): %s" % error) print(sys.exc_traceback.tb_lineno) except (ErrorOccured) as e: print(e.Message) print(sys.exc_traceback.tb_lineno)
def writeActivitiesData_MultiBundles_MultipleActivities(bundle_item): 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"] #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"] activities_material = getItems_MultiBundle_MultiActivity( node1, node2, "material", bundle_item, i) print(activities_planned_start) # -------------------------------------------------------- # First truncate the data from project and location table # -------------------------------------------------------- # LsqlQuery = "TRUNCATE TABLE TEMP.PROJECTS" # dbu.executeQueryString(LsqlQuery, connObj) # -------------------------------------------------------- # 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.executeSQLData(execSQL, execData, connObj) print('#####################-- MultiBundle-MultiActivity----') 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('#####################') connObj.close()
def writeBundle_Activity(): ## Update the details to the database temp connObj = dbu.getConn() bundles_name = getItemsChildLevel(len(d), "bundles", "name") bundles_phases = getItemsChildLevel(len(d), "bundles", "phases") 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"] activities_material = getItemsSubChildLevel("bundles", "activities", "material") # -------------------------------------------------------- # First truncate the data from project and location table # -------------------------------------------------------- #LsqlQuery = "TRUNCATE TABLE TEMP.PROJECTS" #dbu.executeQueryString(LsqlQuery, connObj) # -------------------------------------------------------- # 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) print(execSQL) print(execData) dbu.executeSQLData(execSQL, execData, connObj) 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 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 writeActivitiesData_MultipleActivities(): activityList = [] connObj = dbu.getConn() node1 = "bundles" node2 = "activities" bundles_name = getItemsChildLevel(len(d), "bundles", "name") bundles_phases = getItemsChildLevel(len(d), "bundles", "phases") 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"] 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.executeSQLData(execSQL, execData, connObj) 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 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 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")
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 dbu.updateFileObjectIntoDB(dbu, str(d).encode(), 'Streaming content for BaselineData', 'Structural') connObj.close() sys.exit(0) finally: print("Finished output") ''' def main(args): hello(args[1]) if __name__ == '__main__': main(sys.argv) '''
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 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 insertActivityData(activityID, plannedStart, plannedEnd, plannedUnits, actualUnits): try: db_conn = dbu.getConn() tWdays = ecr.getTotalWorkdays() execSQL = """delete from activity_data ad using activities a where ad.activity_id = '{a_id}' and a.project_id = '{id}';""" execSQL = execSQL.format(a_id=activityID, id=projectID) dbu.executeQuery(db_conn, execSQL) if (plannedStart == None or plannedEnd == None): execSQL = "INSERT INTO ACTIVITY_DATA (ACTIVITY_ID,PLANNED_UNITS,ACTUAL_UNITS) VALUES (%s,%s,%s);" execData = (activityID, plannedUnits, actualUnits) dbu.executeQueryWithData(db_conn, execSQL, execData) return execSQL = "INSERT INTO ACTIVITY_DATA (ACTIVITY_ID,DATE,PLANNED_HOURS) VALUES (%s,%s,%s);" dtDate = datetime.strptime(plannedStart, '%Y%m%d') enddtDate = datetime.strptime(plannedEnd, '%Y%m%d') # Now for each activity, expand the dates startDate until end date # and insert into the activities_data table dd = [ dtDate + timedelta(days=x) for x in range((enddtDate - dtDate).days + 1) ] workingDayCount = 0 planned_hours = 8 for d in dd: # execSQL = ('insert_activity_data_data' wDay = getDayofWeek(d) dt = datetime.date(d) if wDay == 0 or wDay == 1 or wDay == 2 or wDay == 3 or wDay == 4: # monday - friday planned_hours = 8 workingDayCount = workingDayCount + 1 elif wDay == 5 or wDay == 6: # if it is a saturday or sunday, insert a NONE for the planned hours planned_hours = None execData = (activityID, d, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) # First calculate for planned units dailyPlannedUnits = 0 if plannedUnits != 0 and workingDayCount != 0: dailyPlannedUnits = (plannedUnits / workingDayCount) # Next calculate for Actual Units dailyActualUnits = 0 if actualUnits != 0 and workingDayCount != 0: dailyActualUnits = (actualUnits / workingDayCount) planned_hours = 8 execSQL = "UPDATE ACTIVITY_DATA SET PLANNED_UNITS=%s, ACTUAL_UNITS=%s WHERE ACTIVITY_ID=%s AND" \ " PLANNED_HOURS=%s;" execData = (dailyPlannedUnits, dailyActualUnits, activityID, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) except (Exception) as error: print(datetime.now(), error) raise
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)
def expandDates(): try: if (len(expand_dates_list)) == 0: raise ErrorOccured("Empty result List") #get database connection db_conn = dbu.getConn() # get the total workdays in a week tWdays = ecr.getTotalWorkdays() planned_hours = 8 # assign planned hours per day as 8 hours totalRecords = len(expand_dates_list) #counter=0 print("\n", file=actvity_txtFile) print("#### Printing insert query for activity_data ######", file=actvity_txtFile) ## Truncate temp.activity_data. We will insert rows into this table ## and then call a stored function to transfer them into activity_data table execSQL = "TRUNCATE TABLE activity_data" dbu.executeQuery(db_conn, execSQL) for i in range(0, totalRecords): activityN = expand_dates_list[i][0] startDate = expand_dates_list[i][1] TendDate = expand_dates_list[i][2] dtDate = datetime.strptime(startDate, '%Y-%m-%d %H:%M') enddtDate = datetime.strptime(TendDate, '%Y-%m-%d %H:%M') #Now for each activity, expand the dates startDate until end date # and insert into the activities_data table dd = [ dtDate + timedelta(days=x) for x in range((enddtDate - dtDate).days + 1) ] for d in dd: execSQL = "INSERT INTO ACTIVITY_DATA (ACTIVITY_ID,DATE,PLANNED_UNITS) VALUES (%s,%s,%s);" # get the weekday wDay = getDayofWeek(d) dstat = checkIfHoliday(d) planned_hours = 8 if tWdays == '5': # if its a 5 day work week if dstat == 'w': # if its not a holiday if wDay == 0 or wDay == 1 or wDay == 2 or wDay == 3 or wDay == 4: #monday - friday # activities table insert execData = (activityN, d, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) print(execSQL, execData, file=actvity_txtFile) #counter = counter + 1 #comment this line in production elif wDay == 5 or wDay == 6: # if it is a saturday or sunday, insert a NONE for the planned hours planned_hours = None execData = (activityN, d, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) print(execSQL, execData, file=actvity_txtFile) #counter = counter + 1 # comment this line in production elif dstat == 'h': # if it is a holiday, insert a NONE for the planned hours planned_hours = None execData = (activityN, d, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) print(execSQL, execData, file=actvity_txtFile) elif tWdays == '6': # if its a 6 day work week : monday to Saturday if dstat == 'w': if wDay == 0 or wDay == 1 or wDay == 2 or wDay == 3 or wDay == 4 or wDay == 5: execData = (activityN, d, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) print(execSQL, execData, file=f) #counter = counter + 1 #comment this line in production elif wDay == 6: # if it is a saturday or sunday, insert a NONE for the planned hours planned_hours = None execData = (activityN, d, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) print(execSQL, execData, file=actvity_txtFile) elif dstat == 'h': # if it is a holiday, insert a NONE for the planned hours planned_hours = None execData = (activityN, d, planned_hours) dbu.executeQueryWithData(db_conn, execSQL, execData) print(execSQL, execData, file=actvity_txtFile) except (Exception) as error: print("Error in expandDates(): %s" % error) print(sys.exc_traceback.tb_lineno) except (ErrorOccured) as e: print(e.Message) print(sys.exc_traceback.tb_lineno)