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 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 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)