[$175] Create a script to import Jobs data from Recruit CRM

[maxceem]

Background

We already have some Jobs in Recruite CRM. As we are going to run our own TaaS App, we would like to import data from Recruit CRM to our TaaS App. We can export CSV files with jobs list from Recruit CRM. We would like to be able to import data from such files in DEV and PROD environment by running a script.

Task

• Create a script inside scripts directory. I should have it's own directory, which would contain:
  • index.js - the script itself to run
  • README.md - brief readme on how to run the script (which ENV vars to set and how to point CSV file)
• As we need to add data to both DB and ES, I guess the easier way would be make the script call TaaS API for creating jobs.
• We would have around 400 jobs in real data.
• Script should get all params as ENV variables (for example M2M token config for calling TaaS API) or anything else
• CSV file path should be provided as an argument.

CSV file example:

DirectprojectId,projectId,externalId,title,startDate,endDate,numPositions,userHandle,jobid,customerRate,memberRate
24568,(dynamic load),0,taas-demo-job5,10/26/2020,1/29/2021,2,nkumartest,(dynamic load),20,10
24567,(dynamic load),1212,Dummy Description,10/20/2020,1/29/2021,2,pshah_manager,(dynamic load),150,100
24566,(dynamic load),23850272,33fromzaps330,2/21/2021,3/15/2021,7,nkumar2,(dynamic load),50,30
24565,(dynamic load),23843365,Designer,2/24/2021,3/30/2021,1,GunaK-TopCoder,(dynamic load),70,70
24564,(dynamic load),23836459,demo-dev-19janV4,1/20/2021,1/30/2021,1,nkumar1,(dynamic load),400,200

Script workflow

1. Read a line from csv file
2. If externalId is missing, log a warning about this and skip the line (goto 1).
3. From DirectProjectID get projectId by calling {TC_API}/projects/?directProjectId={DirectProjectID}
4. We should create a job record in TaaS API from CSV file, only if it doesn't exists in TaaS API yet. To check it we have to use externalId and check if there is existent job with provided externalId:
   • If not, then create a job and note down created jobId (id)
   • if a job with such externalId already exists, then don't create it, and just note down it's jobId (id)
5. Get userId by userHandle field (via v5/users endpoint)
6. Now we have to create a ResourceBooking record for particular user and particular job if doesn't exists yet:
   • If userid and jobId already exists at ResourceBooking then skip it (goto 1)
   • otherwise create a resourcebooking using jobId and userId
7. Patch resourcebooking status based csvfile.ENDDATE. if today date > end date = "closed" else "assigned"
8. Delay for a second (config delay via env variable) and goto 1.

Notes

1. The dates shared are for ResourceBooking start and end date, do not post this for Job creation.
2. Validate for not null, if data missing alert for "field" cannot be null
3. Dates has to be in mm/dd/yyyy format and validate for same.
4. Provide a summary of total post, total successful loads, skips and failure. ( total post for jobs and total post for resourcebooking, provide total failed post for job and rb.)

The mapping between CSV file columns and Job and ResourceBooking Models

[maxceem]

Challenge https://www.topcoder.com/challenges/24fb9772-2a3b-4fec-bb04-902ee5b2f68c has been created for this ticket.

This is an automated message for maxceem via Topcoder X

[maxceem]

@imcaizheng this is ready for pickup.

It would be great if it could be done by Monday, Feb 1.

We are still gathering real data from the services to create a real CSV file that would look like the example above, but there might be some changes in the final CSV file format. So we might need some adjustments into script later.

[maxceem]

Challenge https://www.topcoder.com/challenges/24fb9772-2a3b-4fec-bb04-902ee5b2f68c has been assigned to aaron2017.

This is an automated message for maxceem via Topcoder X

[nkumar-topcoder]

@imcaizheng One more small req for reports, when searching for v5/users if users are missing, you cannot book for resourcebooking. But, please capture missing v5/users in the report.
cc @maxceem

[imcaizheng]

PR created #120

[maxceem]

@imcaizheng I've tested the script on locally and on DEV, it seems to work good. Though when I run script 2 times the log looks almost the same and it's not clear which records were created, and which records already existed.

Could you please, add more details to the log output, so we can see:

• which jobs and resourcebookings were created, and which were already exists during script running
• during script running also show ids of existent jobs and resources bookings and ids of created jobs and resourcebookings
• in the summary, also show how many jobs and resourcebookings were created and how many jobs and resourcesbooking were already existed.

[maxceem]

Sorry, I've just noticed that the data I've requested above is already logged, I didn't notice it.
So let's just update the summary:

• in the summary, also show how many jobs and resourcebookings were created and how many jobs and resourcesbooking were already existed.

[imcaizheng]

As for

which jobs and resourcebookings were created, and which were already exists during script running

and

during script running also show ids of existent jobs and resources bookings and ids of created jobs and resourcebookings

Actually the log does contains the info though it is not obvious. The following commands could help find out the details:

# find out all jobs created
cat /tmp/report.txt | grep -E '#[0-9]+' | grep 'job created'
# find out all resource bookings created
cat /tmp/report.txt | grep -E '#[0-9]+' | grep 'resource booking created'
# find out all jobs that already exists
cat /tmp/report_2nd.txt | grep -E '#[0-9]+' | grep 'job already exists'
# find out all resource bookings that already exists
cat /tmp/report_2nd.txt | grep -E '#[0-9]+' | grep 'resource booking already exists'

@maxceem Please let me know whether the details in the log are desired.