I am trying to get the results from the database for each month from January and create a csv file for the historic data. I have 3-4 columns as a fixed one and the last column(minutes) should be mapped to each month and append the data to the same csv file. I am not sure how to achieve this in json2csv. Can you please help if you have already came across this scenario?
username role Name activity Name 01-01-2023 01-02-2023 till 01-08-2023 [headers]
user1 role1 act1 25(minutes) 45(minutes)
user1 role1 act2 65(minutes) 150(minutes) 1000(minutes)
user1 role1 act2 89 450
let headers = ["username", "role Name", "activity Name"];
async function generateHeaders() {
const interval = getIntervalRanges(); // Get the dates from january till previous month
for (const i of interval.reverse()) {
const [start, end] = i.split('/');
const startDate = moment(start);
const endDate = moment(end);
const start_date = startDate.utc().add(1, 'day').format('YYYY-MM-DD');
const end_date = endDate.utc().subtract(1, 'day').format('YYYY-MM-DD');
headers.push(start_date);
}
await main()
}
async function main() {
const interval = getInterval();
for (const i of interval.reverse()) {
const [start, end] = i.split('/');
const startDate = moment(start);
const endDate = moment(end);
const start_date = startDate.utc().add(1, 'day').format('YYYY-MM-DD');
const end_date = endDate.utc().subtract(1, 'day').format('YYYY-MM-DD');
const results = await sequelize.query("Query", {
replacements: {
start_date: start_date,
end_date: end_date
},
type: Sequelize.QueryTypes.SELECT
}); // results will have the JSON data for every month from Jan.
const values = results && results.length && results.map((r) => {
return {
userName: r.name,
roleName: r.role,
activityName: r.activity,
minutes: r.minutes
}
})
if (values.length > 0) {
await createCSVFile(values);
}
}
}
async function createFile(valuesToWrite) {
const fileName = output.csv;
var csvParser = new Parser({
header: !fs.existsSync(fileName),
delimiter: ",",
fields: headers
});
var csvData = csvParser.parse(valuesToWrite)
fs.appendFileSync(fileName, csvData + "\n");
}
generateHeaders();