def generateProjectFile(featureId):
book = xlsxwriter.Workbook('tempfiles/projectList.xlsx')
sheet = book.add_worksheet('Project')
session = createSession('try')
format_title = book.add_format(FORMAT_TITLE)
format_text = book.add_format(FORMAT_TEXT)
col_names = ['Project','Lead','Description','State','Jira']
sheet.set_column('A:A', 50)
sheet.set_column('B:B', 20)
sheet.set_column('C:C', 100)
sheet.set_column('D:D', 30)
sheet.set_column('E:E', 50)
col = 0
for item in col_names:
sheet.write(0, col, item, format_title)
col += 1
projectList = session.query(Activity).filter(Activity.parent_id==featureId)
row=1
for item in projectList:
jiraInfo = ''
for elem in re.findall('"([^"]*)"', item.jira):
jiraInfo = jiraInfo + elem + ',';
sheet.write(row, 0, item.name, format_text)
sheet.write(row, 1, item.lead)
sheet.write(row, 2, item.description, format_text)
sheet.write(row, 3, item.state)
sheet.write(row, 4, jiraInfo)
row += 1
sheet.data_validation('D2:D50', {'validate': 'list','source': ['Unplanned-Unstaffed','Planned-Unstaffed','Planned-Partially Staffed','Scheduled']})
book.close()
def generateMemberFile(teamname):
book = xlsxwriter.Workbook('tempfiles/teammember.xlsx')
sheet = book.add_worksheet('Members')
session = createSession('try')
format_title = book.add_format(FORMAT_TITLE)
sheet.set_column('A:A', 30)
sheet.set_column('B:B', 60)
sheet.set_column('C:G', 18)
col_names = ['Name','Email','Position','Location','Type','Team','Mobile']
col = 0
for item in col_names:
sheet.write(0, col, item, format_title)
col += 1
memberList = session.query(Person).filter(Person.team == teamname)
row = 1
for member in memberList:
sheet.write(row, 0, member.display_name)
sheet.write(row, 1, member.email)
sheet.write(row, 2, member.position)
sheet.write(row, 3, member.location)
sheet.write(row, 4, member.user_type)
sheet.write(row, 5, member.team)
sheet.write(row, 6, member.mobile_num)
row += 1
sheet.data_validation('C2:C50', {'validate': 'list','source': ['MTS']})
sheet.data_validation('D2:D50', {'validate': 'list','source': ['Acton MA US','Prague Czech Republic']})
sheet.data_validation('E2:E50', {'validate': 'list','source': ['Fulltime','Contractor','Intern']})
sheet.data_validation('F2:F50', {'validate': 'list','source': ['','Content','EMS','Gateway','Infrastructure','Networking','SQA','Sustaining']})
sheet.data_validation('G2:G50', {'validate': 'length',
'criteria': '>',
'value': 0,
'input_title': 'Mobile Number'})
book.close()
def generateFeatureFile(releaseId=''):
book = xlsxwriter.Workbook('tempfiles/featureList.xlsx')
sheet = book.add_worksheet('Feature')
session = createSession('try')
format_title = book.add_format(FORMAT_TITLE)
format_text = book.add_format(FORMAT_TEXT)
col_names = ['Feature','REQS','Category','Customer','Priority','Realease','Lead']
sheet.set_column('A:A', 50, format_text)
sheet.set_column('B:B', 30)
sheet.set_column('C:C', 30, format_text)
sheet.set_column('D:D', 20, format_text)
sheet.set_column('E:E', 10)
sheet.set_column('F:F', 50, format_text)
sheet.set_column('G:G', 20)
col = 0
for item in col_names:
sheet.write(0, col, item, format_title)
col += 1
if releaseId == '':
featureList = session.query(Activity).filter(Activity.activity_type=='Feature')
else:
featureList = session.query(Activity).filter(Activity.parent_id == releaseId)
row=1
for item in featureList:
jiraInfo = ''
for elem in re.findall('"([^"]*)"', item.jira):
jiraInfo = jiraInfo + elem + ',';
sheet.write(row, 0, item.name)
sheet.write(row, 1, jiraInfo)
sheet.write(row, 2, item.category)
sheet.write(row, 3, item.customer)
sheet.write(row, 4, item.priority)
if item.parent_id != '' and item.parent_id != None:
parentname = list(session.query(Activity).filter(Activity.objectId==item.parent_id))[0].name
else:
parentname = ''
sheet.write(row, 5, parentname)
sheet.write(row, 6, item.lead)
row += 1
sheet.data_validation('E2:E50', {'validate': 'list','source': ['P1','P2','P3']})
book.close()
def read(filename,act_type=''):
book=xlrd.open_workbook(filename)
list_data=[]
#Get effective columns in excel
feature_dict ={'name': ['Feature','feature','name','Name'],
'jira': ['REQS','Jira'] ,
'category': ['Category'] ,
'customer': ['Customer'] ,
'priority': ['Priority'] ,
'lead': ['Feature Lead','Lead'] ,
'release' : ['Release','release']
}
project_dict ={'project': ['Project','project'],
'lead': ['lead','Lead'] ,
'description': ['Description','description'],
'state': ['State','state'],
'jira': ['REQS','Jira','jira']
}
assignment_dict ={'assignment': ['Assignment','assignment'],
'team': ['Team','team'] ,
'resources': ['Resources','resources','Resource','resource'],
'start': ['Start Date','start date','Start date'],
'week': ['Duration(Weeks)','Duration','duration','Weeks','weeks'],
'workload': ['Workload','workload','Work load','work load'],
'description': ['Description','description']
}
member_dict ={ 'name': ['name','Name'],
'email': ['email','Email'] ,
'position': ['position','Position'] ,
'location': ['location','Location'] ,
'user_type': ['user_type','User_type','User_Type','type','Type'] ,
'team': ['team','Team'] ,
'mobile': ['mobile','Mobile','Mobile Number','Phone Number','Phone','phone']
}
if act_type=='feature':
effective_dict = copy.deepcopy(feature_dict)
elif act_type=='project':
effective_dict = copy.deepcopy(project_dict)
elif act_type=='assignment':
effective_dict = copy.deepcopy(assignment_dict)
else:
effective_dict = copy.deepcopy(member_dict)
effective_col = {}
#map columns in database
empty_flag = 0
effective_row = 0
sheet = book.sheet_by_index(0)
for col in range(sheet.ncols):
for col_name in effective_dict.keys():
if sheet.cell(0,col).value in effective_dict[col_name]:
effective_col[col]=col_name
del effective_dict[col_name]
#get effective rows
for row in range(1,sheet.nrows):
empty_flag = 1
for col in effective_col:
if sheet.cell(row,col).ctype != 0 :
empty_flag = 0
break
if empty_flag == 1:
effective_row = row
break
#check if the excel is in format
#print effective_col
if(len(effective_col)==0):
return ''
if effective_row==0:
effective_row=sheet.nrows
#get data from excel book
for row in range(1,effective_row):
row_data={}
for col in effective_col:
col_name = effective_col[col]
row_data[col_name]=sheet.cell(row,col).value
if act_type== 'feature':
if col_name == 'release': #Add new release into db
session = createSession('try')
release = list(session.query(Activity).filter(Activity.activity_type=="Release", Activity.name == row_data[col_name]))
if len(release) == 0 and row_data[col_name] != '' : # if the release does not exist in the db
new_release = Activity(name = row_data[col_name] , activity_type = "Release" , description = "Add from excel import" , state = "Planning")
session.add(new_release)
session.commit()
if col_name == 'jira' :
row_data[col_name] = row_data[col_name].split(',')
if col_name == 'description':
row_data[col_name] = row_data[col_name].replace("'","\u0027")
if col_name == 'start':
if row_data[col_name] != '':
dateTuple = xlrd.xldate_as_tuple(row_data[col_name],book.datemode)
row_data[col_name] = str(dateTuple[0]) + '-' + str(dateTuple[1]) + '-' + str(dateTuple[2])
list_data.append(row_data)
#print list_data
return json.dumps(list_data)
def generateTaskFile(projectId):
session = createSession('try')
book = xlsxwriter.Workbook('tempfiles/assignments.xlsx')
sheet = book.add_worksheet('Assignments')
format_title = book.add_format(FORMAT_TITLE)
format_text = book.add_format(FORMAT_TEXT)
dateformat = book.add_format({'num_format': 'yyyy-mm-dd'})
sheet.set_column('A:A', 50)
sheet.set_column('B:B', 15)
sheet.set_column('C:C', 20)
sheet.set_column('D:D', 12)
sheet.set_column('E:E', 20)
sheet.set_column('F:F', 12)
sheet.set_column('G:G', 20)
sheet.set_column('H:H', 10)
sheet.set_column('I:I', 100)
col_names = ['Assignment','Team','Resources','Start Date','Duration','End Date','State','Workload','Description']
col = 0
for item in col_names:
sheet.write(0, col, item, format_title)
col += 1
assignmentList = session.query(Assignment).filter(Assignment.activity_id==projectId)
row=1
for item in assignmentList:
if item.task_phase > 100:
pass
else:
sheet.write(row, 0, item.name,format_text)
sheet.write(row, 1, item.team)
sheet.write(row, 2, item.person)
if item.task_phase == 0:
count_date = item.planned_start_date
end_date = item.forecast_end_date
state = 'Planing'
elif item.task_phase == 1:
count_date = item.forecast_start_date
end_date = item.forecast_end_date
state = 'Forecasted'
elif item.task_phase == 2:
count_date = item.actual_start_date
end_date = item.forecast_end_date
state = 'Start'
elif item.task_phase == 3:
count_date = item.actual_start_date
end_date = item.actual_end_date
state = 'Complete'
else:
count_date = item.planned_start_date
end_date = item.forecast_end_date
state = 'Common Task'
sheet.write(row, 3, count_date, dateformat)
sheet.write(row, 4, item.planned_num_weeks)
sheet.write(row, 5, end_date, dateformat)
sheet.write(row, 6, state)
sheet.write(row, 7, item.work_load)
sheet.write(row, 8, item.description, format_text)
row += 1
sheet.data_validation('B2:B50', {'validate': 'list','source': ['','Content','EMS','Gateway','Infrastructure','Networking','SQA','Sustaining']})
sheet.data_validation('D2:D50', {'validate': 'date',
'criteria': 'between',
'minimum': date(1900, 1, 1),
'maximum': date(2040, 12, 12),
'input_title': 'Start date',
'error_title': 'Invalid date',
'error_message':
'Enter a date or leave it empty!'})
sheet.data_validation('E2:E50', {'validate': 'length',
'criteria': '<',
'value': 100,
'input_title': 'Duration:',
'input_message': 'Enter number of weeks, or specified duration,ie. 5w, 5 weeks, 1 week 3 days',
'error_title': 'Input value is not valid!',
'error_message':
'It should be an integer!'})
sheet.set_column = ('F2:F50', {'validate': 'date',
'criteria': 'between',
'minimum': date(1900, 1, 1),
'maximum': date(2040, 12, 12),
'input_title': 'End date will not be used for planing from spreadsheet',
'error_title': 'Invalid date',
'error_message':
'Enter a date or leave it empty!'})
sheet.data_validation('G2:G50', {'validate': 'list','source': ['Planning','Forecasted','Start','Complete']})
sheet.data_validation('H2:H50', {'validate': 'decimal',
'criteria': 'between',
'minimum': 0.00,
'maximum': 1.00,
'input_title': 'Work Load',
'input_message': 'between 0 and 1',
'error_title': 'Input value is not valid!',
'error_message':
'It should be an number between 0 and 1'})
book.close()
