def sanity_check_counts(): """ Execute the sanity check queries and report the failed cases Args: No Arguments Returns: No return Arguments """ subtaskid = createSubTask("sanity check queries", getMaintaskId()) try: conn = dbConnection() cur = conn.cursor() cur.execute("select query, name, expected_value from birepusr.etl_data_sanity_queries where project_name='planview'") rows = cur.fetchall() for row in rows: conn = dbConnection() cur = conn.cursor() cur.execute(row[0]) x = cur.fetchall() if x == []: actual_value = 0 else: actual_value = x[0][0] condition = row[1] expected_value = row[2] status = 'Success' if not expected_value == actual_value: status = 'Fail' message = '''Expected vs Actual values not matching for check '{0}': expected {1} rows but found {2}'''.format(condition, expected_value, actual_value) send_custom_mail('Service-Desk ETL : DATA SANITY TEST FAILED', message) executeQuery('''insert into birepusr.etl_data_sanity_test_results(condition, created_date, expected_value, actual_value, status, task_id,project_name) values ('{0}', now(), {1}, {2}, '{3}', '{4}','planview')'''.format(condition, expected_value, actual_value, status, subtaskid)) updateSubTask(subtaskid, "SUCCESS") except (Exception, psycopg2.Error) as error: insertErrorLog(subtaskid, error)
def init_timesheet_insert(timesheet_entry,entry_flag): """ This function initiate Planview WS API call to insert timesheet entry for given user. Args: DataFrame: pandas dataframe with timehseet details for all users for latest sprints String: Identifier to distingish fresh timehseet entry or retry attempt of failed timesheet Returns: No Return """ if not timesheet_entry.empty: df_ts = timesheet_entry.groupby(by=['timesheetId','userId'], sort=False).apply(lambda x: x.to_dict(orient='records')) df_ts = df_ts.drop(columns="userId") for row in df_ts.to_list(): user_ts_list = {'timesheetId':row[0]['timesheetId'], 'userId': int(row[0]['userId'])} user = int(row[0]['userId']) timesheet = row[0]['timesheetId'] for r in row: del r['userId'] user_ts_list['entriesProject']=row try: if entry_flag == 'New': planview.insertTimesheet(user_ts_list) else: #TO DO optimize this code to perform bul operation at end planview.insertTimesheet(user_ts_list) conn = dbConnection() cur = conn.cursor() cur.execute('''delete from idw.planview_failed_timesheetID where timesheetId = {0} and userId = {1}'''.format(timesheet,user)) conn.commit() except Exception as e: #TO DO: add valid timesheet record and user details in email body send_custom_mail("Failed to populate timesheet in planview", e) x1 = timesheet_entry[(timesheet_entry.timesheetId == timesheet) & (timesheet_entry.userId == user)] insertFailedEntries(x1)
def createDf(df, temp_table, entity_name): """ This function insert entity data in temporary table and call function to process data for respective entity using newly created table. Args: dataframe : take dataframe with source data as input string : temporary table inwhile data frame will get persisted string : entity name for which data is being loaded and processed Returns: No Return """ conn = dbConnection() cur = conn.cursor() cur.execute('''{0}'''.format(temp_table)) output = io.StringIO() df = df.replace('None', '') df.to_csv(output, sep='|', header=True, index=False) output.seek(0) copy_query = "COPY pv_entity_temp_table FROM STDOUT csv DELIMITER '|' NULL '' ESCAPE '\\' HEADER " cur.copy_expert(copy_query, output) proc_name = ("idw.planview_" + entity_name).lower() cur.callproc(proc_name) conn.commit()
def test_max_time_spent_by_user(self): # check maximum hours spent by any users conn = dbConnection() cur = conn.cursor() cur.execute( """select (sum(time_spent)/ actual_sprint_days):: numeric(10) as hr from idw.task_time_spent_fact f inner join idw.jira_sprint_summary ss on f.sprint_number=ss.sprint_number and f.team_dim_id=ss.team_dim_id where f.sprint_number=2448 and portfolio_dim_id is not null group by f.team_dim_id,f.sprint_number,ss.actual_sprint_days""") rows = cur.fetchall() summary = round(rows[0][0]) self.assertEqual(summary, 8)
def loadEntityData(): subtaskid = createSubTask("Load planview data into tables", getMaintaskId()) try: conn = dbConnection() cur = conn.cursor() cur.execute( "select entity_ppm_id from idw.planview_entity_dim order by load_sequence" ) rows1 = cur.fetchall() # rows1 = [5] for row in rows1: ppm_id = row[0] #ppm_id=row cur.execute( "select column_select_list from idw.planview_entity_dim where entity_ppm_id={0}" .format(ppm_id)) rows = cur.fetchall() json_dict = rows[0][0].values() get_list = ",".join([str(e) for e in json_dict]) mList = [int(e) if e.isdigit() else e for e in get_list.split(",")] json_keys = rows[0][0].keys() get_Cols = ",".join([str(e) for e in json_keys]) column_list = [ int(e) if e.isdigit() else e for e in get_Cols.split(",") ] data = getData(ppm_id, mList) df = data['methodValues'] print(df) test_df(df, column_list) ##df.apply(lambda row: getPlanviewData(row, column_list, ppm_id)) createDf(ppm_id, dfObj, temp_table) print(dfObj) dfObj.drop(columns=column_list, inplace=True) df.drop(df.index, inplace=True) updateSubTask(subtaskid, "SUCCESS") except (Exception) as error: insertErrorLog(subtaskid, error) subtaskid = createSubTask("Load planview data into tables", getMaintaskId()) try: updateSubTask(subtaskid, "SUCCESS") except (Exception) as error: insertErrorLog(subtaskid, error)
def test_fetch_timesheet_entries(self): planview_entities = executeQueryAndReturnDF( "select string_agg(res.resource_ppm_id::varchar(15), ', ') AS user_list,'2020/01/01' as min from (select r.resource_ppm_id,r.resource_dim_id from idw.user_team_map ut inner join idw.team_dim t on ut.team_dim_id=t.team_dim_id inner join idw.planview_resource_dim r ON ut.user_dim_id = r.resource_dim_id where is_reportable=true) res" ) conn = dbConnection() cur = conn.cursor() for index, row in planview_entities.iterrows(): users = row['user_list'] if users is not None: mList = [ int(e) if e.isdigit() else int(e) for e in users.split(',') ] else: raise Exception("User list is Empty") startDate = row['min'] sheet = planview.getTimesheet_users(mList, startDate) self.assertTrue(len(sheet.index)) break
def test_time_spent_by_team(self): # check number of developers conn = dbConnection() cur = conn.cursor() cur.execute("""select sum(per_day_time_spent)::numeric(10) as hr from idw.planview_get_timesheet_entries('2448') group by team_name, sprint_name;""") rows = cur.fetchall() summary = round(rows[0][0]) cur.execute( """select (sum(time_spent)* number_of_devs)::numeric(10) as hr from idw.task_time_spent_fact f inner join idw.jira_sprint_summary ss on f.sprint_number=ss.sprint_number and f.team_dim_id=ss.team_dim_id where f.sprint_number=2448 and portfolio_dim_id is not null group by f.team_dim_id,f.sprint_number,ss.number_of_devs;""") row1 = cur.fetchall() issues = round(row1[0][0]) self.assertEqual(summary, issues)
def insertFailedEntries(entry): """ This function insert failed timesheets in idw.planview_failed_timesheetID table so that we can retry them at later point Args: Dataframe: Dataframe with timesheet object which failed while populating data in planview Returns: No Return """ conn = dbConnection() cur = conn.cursor() cur.execute('''create temp table timesheet_entry (companyId int,entryTypeId int,timesheetId bigint,userId bigint,level1Id bigint,level2Id bigint,entryDate date,entryId bigint,level3Id bigint,entryHours numeric) ''') output = io.StringIO() entry.to_csv(output, sep='|', header=True, index=False) output.seek(0) copy_query = "COPY timesheet_entry FROM STDOUT csv DELIMITER '|' NULL '' ESCAPE '\\' HEADER " cur.copy_expert(copy_query, output) cur.execute('''insert into idw.planview_failed_timesheetID(companyId,entryTypeId,timesheetId,userId,level1Id,level2Id,entryDate,entryId,level3Id,entryHours,created_date) select companyId,entryTypeId,timesheetId,userId,level1Id,level2Id,entryDate,entryId,level3Id,COALESCE(entryHours, 0) entryHours,now() as created_date from timesheet_entry where concat(timesheetid,userid) not in (select concat(timesheetid,userid) from idw.planview_failed_timesheetID)''') conn.commit()
def createDf(ppm_id, df, temp_table): subtaskid = createSubTask( "Create temp table to load the planview data into db tables", getMaintaskId()) try: conn = dbConnection() cur = conn.cursor() cur.execute('''{0}'''.format(temp_table)) output = io.StringIO() df.to_csv(output, sep='|', header=True, index=False) output.seek(0) copy_query = "COPY temp_table FROM STDOUT csv DELIMITER '|' NULL '' ESCAPE '\\' HEADER " cur.copy_expert(copy_query, output) cur.execute( '''select entity_name from idw.planview_entity_dim where entity_ppm_id = {0}''' .format(ppm_id)) ppm = cur.fetchall() proc_name = ("idw.proc_" + ppm[0][0]).lower() cur.callproc(proc_name) conn.commit() updateSubTask(subtaskid, "SUCCESS") except (Exception) as error: insertErrorLog(subtaskid, error)
def timesheet_entry_user(): """ This function process generates data using timesheet and sprint data, to insert timesheet entry into planview. Args: No Arguments Returns: No Return """ subtaskid = al.createSubTask( "Populate timesheet for latest sprints in planview", al.getMaintaskId()) try: #Fetch user ids for processing planview_entities = executeQueryAndReturnDF(dq.get_userlist_to_populate_timesheet) users = planview_entities['user_list'] ts_columns = {'companyid':'companyId','entrytypeid':'entryTypeId','timesheetid':'timesheetId','userid':'userId', 'level1id':'level1Id','level2id':'level2Id','entrydate':'entryDate','entryid':'entryId','level3id':'level3Id','entryhours':'entryHours'} if users[0] is not None: mList = [int(e) if e.isdigit() else int(e) for e in users[0].split(',')] startDate = planview_entities['min'] #Fetch timesheet ids for above user list sheet = planview.getTimesheet_users(mList,startDate[0]) column_names = ['timesheetId','userId','startDate','endDate','entryId','entryDate','entryHours','project_ppm_id','task_ppm_id'] user_timesheet_data = pd.DataFrame(columns=column_names) for tsrow in sheet.itertuples(index=True, name='Pandas'): if not tsrow.entriesProject: user_timesheet_data = user_timesheet_data.append({'timesheetId': tsrow.timesheetId,'userId':tsrow.userId, 'startDate':tsrow.startDate, 'endDate':tsrow.endDate,'entryId': None,'entryDate': None,'entryHours': None,'project_ppm_id': None,'task_ppm_id': None}, ignore_index=True) else: for entry in tsrow.entriesProject: user_timesheet_data = user_timesheet_data.append({'timesheetId': tsrow.timesheetId, 'userId':tsrow.userId, 'startDate':tsrow.startDate, 'endDate':tsrow.endDate,'entryId': entry['entryId'],'entryDate': entry['entryDate'],'entryHours': entry['entryHours'],'project_ppm_id': entry['level1Id'],'task_ppm_id': entry['level2Id']}, ignore_index=True) sprint_number = executeQueryAndReturnDF(dq.get_sprints_for_timesheet) sprint_list = sprint_number['sprint_number'][0] conn = dbConnection() cur = conn.cursor() #export timesheet details into temporary table cur.execute('''create temp table timesheet_data (timesheetId bigint, userId bigint, startDate date, endDate date, entryId bigint, entryDate date,entryHours numeric, project_ppm_id bigint, task_id bigint) ''') output = io.StringIO() user_timesheet_data.to_csv(output, sep='|', header=True, index=False) output.seek(0) copy_query = "COPY timesheet_data FROM STDOUT csv DELIMITER '|' NULL '' ESCAPE '\\' HEADER " cur.copy_expert(copy_query, output) #Joining timesheet data and sprint data query = dq.timesheet_population_query.format(sprint_list) timesheet_entry = pd.read_sql_query(query, coerce_float=False, con=conn) conn.commit() timesheet_entry.rename(columns=ts_columns,inplace=True) init_timesheet_insert(timesheet_entry,'New') cur.execute('''update idw.jira_sprint_summary set is_timesheet_populated = true where sprint_number in ({0})'''.format(sprint_list)) conn.commit() else: print("User list is Empty") #Processing failed timesheet entries failed_entries_df = executeQueryAndReturnDF("select companyId,entryTypeId,timesheetId,userId,level1Id,level2Id,to_char(entryDate,'YYYY/MM/DD') entryDate,entryId,level3Id,entryHours from idw.planview_failed_timesheetID") failed_entries_df.rename(columns=ts_columns,inplace=True) init_timesheet_insert(failed_entries_df,'Existing') al.updateSubTask(subtaskid, "SUCCESS") except Exception as error: al.insertErrorLog(subtaskid, error)
def delete_entries(): subtaskid = al.createSubTask("Delete timesheet entries for a team", al.getMaintaskId()) try: timesheet_team = executeQueryAndReturnDF(dq.delete_timesheet_entries) for team in timesheet_team.itertuples(index=True, name='Pandas'): print(team.resource_list) users = team.resource_list if users is not None: mList = [ int(e) if e.isdigit() else int(e) for e in users.split(',') ] team_id = team.team_dim_id startDate = team.from_date endDate = team.to_date #Fetch timesheet ids for above user list sheet = planview.getTimesheet_users(mList, startDate) column_names = [ 'timesheetId', 'userId', 'startDate', 'endDate', 'entryId', 'entryDate', 'entryHours', 'project_ppm_id', 'task_ppm_id' ] user_timesheet_data = pd.DataFrame(columns=column_names) for tsrow in sheet.itertuples(index=True, name='Pandas'): if not tsrow.entriesProject: user_timesheet_data = user_timesheet_data.append( { 'timesheetId': tsrow.timesheetId, 'userId': tsrow.userId, 'startDate': tsrow.startDate, 'endDate': tsrow.endDate, 'entryId': None, 'entryDate': None, 'entryHours': None, 'project_ppm_id': None, 'task_ppm_id': None }, ignore_index=True) else: for entry in tsrow.entriesProject: user_timesheet_data = user_timesheet_data.append( { 'timesheetId': tsrow.timesheetId, 'userId': tsrow.userId, 'startDate': tsrow.startDate, 'endDate': tsrow.endDate, 'entryId': entry['entryId'], 'entryDate': entry['entryDate'], 'entryHours': entry['entryHours'], 'project_ppm_id': entry['level1Id'], 'task_ppm_id': entry['level2Id'] }, ignore_index=True) print(user_timesheet_data.to_string()) #export timesheet details into temporary table conn = dbConnection() cur = conn.cursor() cur.execute( '''create temp table timesheet_data_delete (timesheetId bigint, userId bigint, startDate date, endDate date, entryId bigint, entryDate date,entryHours numeric, project_ppm_id bigint, task_id bigint) ''' ) output = io.StringIO() user_timesheet_data.to_csv(output, sep='|', header=True, index=False) output.seek(0) copy_query = "COPY timesheet_data_delete FROM STDOUT csv DELIMITER '|' NULL '' ESCAPE '\\' HEADER " cur.copy_expert(copy_query, output) query = '''select timesheetid,userid,string_agg(entryid::varchar(15), ', ') AS entryid from timesheet_data_delete where entrydate between '{0}' and '{1}' group by timesheetid,userid'''.format( startDate, endDate) entries = pd.read_sql_query(query, con=conn) print(entries) conn.commit() for i in entries.itertuples(index=True, name='Pandas'): print(i.timesheetid) print(i.entryid) entries = i.entryid if entries is not None: entry_List = [ int(e) if e.isdigit() else int(e) for e in entries.split(',') ] print(entry_List) timesheet_id = i.timesheetid #delete timesheet entries for timesheetid planview.deleteTimesheet_entries(timesheet_id, entry_List) cur.execute( '''update idw.timesheet_refresh_log set is_timesheet_refreshed = true where team_dim_id = {0}''' .format(team_id)) conn.commit() except (Exception) as error: al.insertErrorLog(subtaskid, error)
def timesheet_entry_all_user(): """ This function pull timesheet entries from planview for users and insert into planview_timesheet_entries table Args: No Arguments Returns: No Return """ subtaskid = al.createSubTask( "Pull timesheet entries from planview for users and insert into planview_timesheet_entries table", al.getMaintaskId()) try: #Fetch user ids for processing planview_entities = executeQueryAndReturnDF(dq.get_user_list_to_pull_timesheet_entries) conn = dbConnection() cur = conn.cursor() for index, row in planview_entities.iterrows(): users = row['user_list'] if users is not None: mList = [int(e) if e.isdigit() else int(e) for e in users.split(',')] else: raise Exception("User list is Empty") startDate = row['min'] #Fetch timesheet entries for user list sheet = planview.getTimesheet_users(mList,startDate) column_names = ['timesheetId', 'companyId', 'entryDate', 'entryHours', 'entryId', 'entryTypeId', 'externalId', 'internalRate', 'billableRate', 'isBillable', 'isProductive', 'level1Id', 'level2Id', 'level3Id', 'locationId', 'notes', 'state', 'userId'] user_timesheet_data = pd.DataFrame(columns=column_names) for tsrow in sheet.itertuples(index=True, name='Pandas'): for entry in tsrow.entriesProject: user_timesheet_data = user_timesheet_data.append({'timesheetId': tsrow.timesheetId,'companyId': entry['companyId'], 'entryDate': entry['entryDate'],'entryHours': entry['entryHours'],'entryId': entry['entryId'],'entryTypeId': entry['entryTypeId'] ,'externalId': entry['externalId'] ,'internalRate': entry['internalRate'] ,'billableRate': entry['billableRate'] ,'isBillable': entry['isBillable'] ,'isProductive': entry['isProductive'] ,'level1Id': entry['level1Id'],'level2Id': entry['level2Id'],'level3Id': entry['level3Id'],'locationId': entry['locationId'],'notes': entry['notes'],'state': entry['state'],'userId':tsrow.userId}, ignore_index=True) for entry1 in tsrow.entriesOther: user_timesheet_data = user_timesheet_data.append({'timesheetId': tsrow.timesheetId,'companyId': entry1['companyId'], 'entryDate': entry1['entryDate'],'entryHours': entry1['entryHours'],'entryId': entry1['entryId'],'entryTypeId': entry1['entryTypeId'] ,'externalId': entry1['externalId'] ,'internalRate': entry1['internalRate'] ,'billableRate': entry1['billableRate'] ,'isBillable': entry1['isBillable'] ,'isProductive': entry1['isProductive'] ,'level1Id': entry1['level1Id'],'level2Id': entry1['level2Id'],'level3Id': entry1['level3Id'],'locationId': entry1['locationId'],'notes': entry1['notes'],'state': entry1['state'],'userId':tsrow.userId}, ignore_index=True) for entry2 in tsrow.entriesAccount: user_timesheet_data = user_timesheet_data.append({'timesheetId': tsrow.timesheetId,'companyId': entry2['companyId'], 'entryDate': entry2['entryDate'],'entryHours': entry2['entryHours'],'entryId': entry2['entryId'],'entryTypeId': entry2['entryTypeId'] ,'externalId': entry2['externalId'] ,'internalRate': entry2['internalRate'] ,'billableRate': entry2['billableRate'] ,'isBillable': entry2['isBillable'] ,'isProductive': entry2['isProductive'] ,'level1Id': entry2['level1Id'],'level2Id': entry2['level2Id'],'level3Id': entry2['level3Id'],'locationId': entry2['locationId'],'notes': entry2['notes'],'state': entry2['state'],'userId':tsrow.userId}, ignore_index=True) for entry3 in tsrow.entriesPortfolio: user_timesheet_data = user_timesheet_data.append({'timesheetId': tsrow.timesheetId,'companyId': entry3['companyId'], 'entryDate': entry3['entryDate'],'entryHours': entry3['entryHours'],'entryId': entry3['entryId'],'entryTypeId': entry3['entryTypeId'] ,'externalId': entry3['externalId'] ,'internalRate': entry3['internalRate'] ,'billableRate': entry3['billableRate'] ,'isBillable': entry3['isBillable'] ,'isProductive': entry3['isProductive'] ,'level1Id': entry3['level1Id'],'level2Id': entry3['level2Id'],'level3Id': entry3['level3Id'],'locationId': entry3['locationId'],'notes': entry3['notes'],'state': entry3['state'],'userId':tsrow.userId}, ignore_index=True) user_timesheet_data['isBillable'] = user_timesheet_data['isBillable'].astype('bool') user_timesheet_data['isProductive'] = user_timesheet_data['isProductive'].astype('bool') #export timesheet details into temporary table cur.execute('''drop table if exists timesheet_data;''') cur.execute('''create temp table timesheet_data (timesheetid numeric, companyid numeric, entrydate date, entryhours numeric, entryid numeric, entrytypeid numeric, externalid varchar(100), internalrate numeric, billablerate numeric, isbillable boolean, isproductive boolean, level1id numeric, level2id numeric, level3id numeric, locationid numeric, notes text, state text, userid numeric);''') output = io.StringIO() user_timesheet_data.to_csv(output, sep='~', header=True, index=False) output.seek(0) copy_query = "COPY timesheet_data FROM STDOUT csv DELIMITER '~' NULL '' ESCAPE '\\' HEADER " cur.copy_expert(copy_query, output) conn.commit() cur.execute(dq.update_is_active_flag_for_ts_entries) cur.execute(dq.insert_timesheet_entries) cur.execute(dq.update_timesheet_dim_ids) conn.commit() al.updateSubTask(subtaskid, "SUCCESS") except Exception as error: print(error) send_custom_mail("Failed to fetch timesheet entries from planview", error) al.insertErrorLog(subtaskid, error)
def timesheet_entry_user(): #Fetch user ids for processing planview_entities = executeQueryAndReturnDF( "select string_agg(a1.resource_ppm_id::varchar(15), ', ') AS user_list, to_char(min(a1.sprint_start_date), 'YYYY/MM/DD') as min from ( SELECT d.resource_ppm_id, min(a.sprint_start_date) as sprint_start_date FROM idw.jira_sprint_summary a inner join idw.user_team_map c ON a.team_dim_id = c.team_dim_id inner join idw.planview_resource_dim d ON c.user_dim_id = d.resource_dim_id AND c.team_entry_date < a.sprint_start_date AND COALESCE(c.team_exit_date::timestamp with time zone, now()) > a.sprint_end_date where coalesce(a.is_timehseet_populated,false)=false and a.sprint_end_date > '2020-05-14' and a.sprint_end_date <= current_date group by d.resource_ppm_id ) a1" ) users = planview_entities['user_list'] mList = [int(e) if e.isdigit() else int(e) for e in users[0].split(',')] startDate = planview_entities['min'] #Fetch timesheet ids for above user list sheet = getTimesheet_users(mList, startDate[0]) #print(row['entryHours'],row['entryDate'],row['timesheetId']) timesheet_data = sheet[['timesheetId', 'userId', 'startDate', 'endDate']] #print(timesheet_data) conn = dbConnection() cur = conn.cursor() cur.execute( '''create temp table timesheet_entry (entryId bigint, timesheetId bigint, entryDate date, entryHours numeric) ''' ) l = sheet['entriesProject'] for row in l: for x in row: cur.execute( '''insert into timesheet_entry(entryId,timesheetId,entryDate,entryHours) values ({0},{1},'{2}','{3}')''' .format(x['entryId'], x['timesheetId'], x['entryDate'], x['entryHours'])) conn.commit() #Creating temporary table to store timesheet data cur.execute( '''create temp table timesheet_data (timesheetId bigint, userId bigint, startDate date, endDate date) ''' ) output = io.StringIO() timesheet_data.to_csv(output, sep='|', header=True, index=False) output.seek(0) copy_query = "COPY timesheet_data FROM STDOUT csv DELIMITER '|' NULL '' ESCAPE '\\' HEADER " cur.copy_expert(copy_query, output) conn.commit() #Fetch list of sprint numbers sprint_number = executeQueryAndReturnDF( "SELECT string_agg(sprint_number::varchar(15), ', ') AS sprint_number from (select distinct sprint_number FROM idw.jira_sprint_summary a where coalesce(a.is_timehseet_populated,false)=false and a.sprint_end_date > '2020-05-14' and a.sprint_end_date <= current_date)x1" ) sprint_list = sprint_number['sprint_number'][0] print(sprint_list) #Joining timesheet data and sprint data #query = '''select pd.*, td.timesheetId::bigint timesheetId, td.entryid, td.entrydate from (select resource_ppm_id::bigint,to_char(date_of_sprint, 'YYYY/MM/DD') date_of_sprint,per_day_time_spent,project_ppm_id::bigint,task_ppm_id::bigint,resource_role_id::bigint from idw.planview_get_timesheet_entries('2448, 2506, 2519, 2552')) pd inner join (select x1.timesheetid,x2.entryid, x2.entrydate, x1.userId, x1.startDate, x1.endDate from timesheet_data x1 left outer join timesheet_entry x2 on x1.timesheetid = x2.timesheetid) td on pd.resource_ppm_id = td.userId and case when entrydate is null then (date_of_sprint::date >= startDate::date and date_of_sprint::date <= endDate::date) else (date_of_sprint::date = entrydate::date) end'''.format(sprint_list) #timesheet_entry = pd.read_sql_query(query, con=conn) entryTypeId = 1 companyId = 1 query1 = '''select timesheetid,userid from timesheet_data''' timesheet_users = pd.read_sql_query(query1, con=conn) print(timesheet_users) for index, row1 in timesheet_users.iterrows(): print(row1['timesheetid'], row1['userid']) #query = '''select pd.*, td.timesheetId::bigint timesheetId, td.entryid, td.entrydate from (select resource_ppm_id::bigint,to_char(date_of_sprint, 'YYYY/MM/DD') date_of_sprint,per_day_time_spent,project_ppm_id::bigint,task_ppm_id::bigint,resource_role_id::bigint from idw.planview_get_timesheet_entries('2448, 2506, 2519, 2552')) pd inner join (select x1.timesheetid,x2.entryid, x2.entrydate, x1.userId, x1.startDate, x1.endDate from timesheet_data x1 left outer join timesheet_entry x2 on x1.timesheetid = x2.timesheetid) td on pd.resource_ppm_id = td.userId and case when entrydate is null then (date_of_sprint::date >= startDate::date and date_of_sprint::date <= endDate::date) else (date_of_sprint::date = entrydate::date) end where pd.resource_ppm_id = '{1}' and td.timesheetid = {2} '''.format(sprint_list, row['timesheetid'], row['userid']) query = '''select pd.*, td.timesheetId::bigint timesheetId, td.entryid, to_char(td.entrydate::date, 'YYYY/MM/DD') entrydate from (select resource_ppm_id::bigint,to_char(date_of_sprint, 'YYYY/MM/DD') date_of_sprint,per_day_time_spent,project_ppm_id::bigint,task_ppm_id::bigint,resource_role_id::bigint from idw.planview_get_timesheet_entries('2448, 2506, 2519, 2552')) pd inner join (select x1.timesheetid,x2.entryid, x2.entrydate, x1.userId, x1.startDate, x1.endDate from timesheet_data x1 left outer join timesheet_entry x2 on x1.timesheetid = x2.timesheetid) td on pd.resource_ppm_id = td.userId and case when entrydate is null then (date_of_sprint::date >= startDate::date and date_of_sprint::date <= endDate::date) else (date_of_sprint::date = entrydate::date) end where pd.resource_ppm_id = {1} and td.timesheetid = {2} '''.format( sprint_list, row1['userid'], row1['timesheetid']) timesheet_entry = pd.read_sql_query(query, con=conn) print(timesheet_entry) strmy = [] str1 = '[' for index, row in timesheet_entry.iterrows(): print(row) l1 = row['entryid'] print(l1) #x = "{" + '''"companyId": 1, "entryId" : {0}, "entryDate" : '{1}', "entryHours": {2},"entryTypeId": 1, "level1Id": {3}, "level2Id": {4}, "level3Id": {5}, "timesheetId": {6}'''.format(row['entryid'],row['entrydate'],row['per_day_time_spent'],row['project_ppm_id'],row['task_ppm_id'],row['resource_role_id'],row['timesheetid']) + "}" x = '{' + """'companyId': 1, 'entryId' : {0}, 'entryDate' : '{1}', 'entryHours': {2},'entryTypeId': 1, 'level1Id': {3}, 'level2Id': {4}, 'level3Id': {5}, 'timesheetId': {6}""".format( row['entryid'], row['entrydate'], row['per_day_time_spent'], row['project_ppm_id'], row['task_ppm_id'], row['resource_role_id'], row['timesheetid']) + '}' #strmy.append(x) str1 = str1 + x + "," x1 = str1.rstrip(',') + "]" print(x1) if x1 != '[]': insertTimesheet1(row1['timesheetid'], row1['userid'], x1)