office_directors_email.js

// heroku scheduler script for automated email to office directors
// heroku scheduler every friday

var MongoClient = require('mongodb').MongoClient;
var ObjectId = require('mongodb').ObjectId;

var moment = require('moment');
moment().format();

const sgClient = require('@sendgrid/mail');

sgClient.setApiKey(process.env.SENDGRID_API_KEY);
var sender ="healthyreentry-notifications@thorntontomasetti.com"
var url = process.env.MONGO_URL;

const fs = require('fs');
var content = fs.readFileSync("./server/assets/email_templates/officeDirectorsReport.html").toString("utf-8");

const userCountByOffice = require("./server/util/officeHeadCount.js");
const directors = require("./server/util/officeDirectors.js");


function checkVaccinationStatus(user, vaccination) {  
  var vaccinationStatus = "No vaccination data recorded";
  if (user.fullyVaccinated){
    vaccinationStatus = "Fully Vaccinated";
  }
  else if (user.duringVacIncubation == true) {
    vaccinationStatus = "Incubation Period(2wks)";
  }
  else if (vaccination && vaccination.count > 0){
    vaccinationStatus = "Partially Vaccinated"
  }

  return vaccinationStatus;
}

//"Name,Email\r\n";
function nodeToCsv(node, vaccination) {
    const statusDate = node.status? moment(node.status.date).format('MMMM DD YYYY') : "Not Available";
    const vaccinationStatus = checkVaccinationStatus(node, vaccination);
    // return `${node.name},${node.email}\r\n`;
    if (vaccination) return `${node.name},${node.email},${statusDate},${vaccinationStatus}, ${node.PCRDate}\r\n`;
    else return `${node.name},${node.email},${statusDate},${vaccinationStatus}, ${node.PCRDate} \r\n`;
}

let allUsers = [];

let officeCount = 0;
let finishedOfficeCount = 0;


MongoClient.connect(url, {
    useUnifiedTopology: true
}).then(function (db) {
    console.log("CONNECTED TO DB");
    var today = new Date();

    if (today.getDay() == 5) console.log("today is friday");
    else {
        console.log("today is not friday yet!")
        db.close();
        return;
    }


    getUsers(db).then(function () {
      officeCount = Object.keys(directors).length;

        Object.keys(directors).forEach((key, index) => {
            // three attachment
            // 1. employees who have signed up for the app,
            // 2. employees who have signed up for the app but haven't reported their health status for more than 7 days,
            // 3. the number of app users in your office with a health status of Red and/or Orange.
            getOfficeUserVaccination(db, key).then(usersWithVacs => {
              // 1. employees who have signed up for the app,
              const usersbyOffice = allUsers.filter(u => u.location === key);

              if(usersbyOffice.length === 0 ) return;
              // let csvHeader = "Name,Email, \r\n";
              let csvHeader = "Name,Email, Last reported status, Vaccination Status, Latest PCR Date\r\n";
              let csv = csvHeader;

              usersbyOffice.forEach((user) => {                
                  const userWithVac = usersWithVacs.filter(uVac => String(uVac._id) == String(user._id))[0];
                  user.vaccination = userWithVac.vaccination; // COMBINE!
                  csv += nodeToCsv(user, userWithVac.vaccination);
              })
              let attachment = Buffer.from(csv).toString('base64');


              // 2. employees who have signed up for the app but haven't reported their health status for more than 7 days,
              var checkDate = new Date();
              var pastDate = checkDate.getDate() - 7;
              checkDate.setDate(pastDate);

            
              // 3. the number of app users in your office with a health status of Red and/or Orange.
              const usersStatusOrange = usersbyOffice.filter(u => u.status.status === 1);
              const usersStatusRed = usersbyOffice.filter(u => u.status.status === 2);

              
              let csv3 = "Office,Number of Orange,Number of Red,Total Signups in Office, Total Office Employee Count, Total Fully Vaccinated in Office, Percentage of Employees Fully Vaccinated, Total Partially Vaccinated, Total in Vaccine Incubation, Total No Vaccination Record\r\n";
              var numberOfOrange = usersStatusOrange.length;
              var numberOfRed = usersStatusRed.length;
              var numberOfIncubation = usersbyOffice.filter(u => u.duringVacIncubation);
              var numberOfPartialVac = usersbyOffice.filter(u => u.fullyVaccinated == false && u.duringVacIncubation == false && u.vaccination && u.vaccination.count > 0);
              var numberOfNoVac = usersbyOffice.filter(u => !u.vaccination);

              var total =  usersbyOffice.length;
              const employeeVaccinated = usersWithVacs.filter(u=>u.fullyVaccinated).length;


              if (key === "New York") {
                csv3 += `${"120 Broadway"},${numberOfOrange},${numberOfRed},${total}, ${userCountByOffice["New York - 120 Broadway"]}, ${employeeVaccinated}, ${String((employeeVaccinated/userCountByOffice["New York - 120 Broadway"]*100).toFixed(2)) + "%"}, ${numberOfPartialVac.length}, ${numberOfIncubation.length}, ${numberOfNoVac.length}\r\n`;
                csv3 += `${"Downtown"},${''},${''},${''},${userCountByOffice["New York - Downtown"]},${''},${''}, ${''}, ${''}, ${''} \r\n`;
                // csv3 += `${"Midtown"},${''},${''},${''},${''},${userCountByOffice["New York - Madison"]},${employeeInOffice2}, ${String((employeeInOffice/userCountByOffice["New York - Madison"]*100).toFixed(2)) + "%"},${''} \r\n`;
                // csv3 += `${"120 Broadway"},${''},${''},${''},${''},${userCountByOffice["New York - 120 Broadway"]},${employeeInOffice3}, ${String((employeeInOffice/userCountByOffice["New York - 120 Broadway"]*100).toFixed(2)) + "%"}\r\n`;
              }
              else csv3 += `${key},${numberOfOrange},${numberOfRed},${total},${userCountByOffice[key]}, ${employeeVaccinated}, ${String((employeeVaccinated/userCountByOffice[key]*100).toFixed(2)) + "%"}, ${numberOfPartialVac.length}, ${numberOfIncubation.length}, ${numberOfNoVac.length}\r\n`;
              let attachment3 = Buffer.from(csv3).toString('base64');


              sendEmail(directors[key], key, attachment, attachment3);   
            })
            .catch(e => {
              console.log("error in getting vaccination info: ", key, e);
            });
          
        });

    })

}).catch(err => {
    db.close();
    console.error("An error occurred reading the database.");
    console.error(err);
});

function getUsers(client_db) {
    return new Promise((resolve, reject) => {
        let db = client_db.db();

        let include = {
            "_id": 1,
            // "dateOfConsent": 1,
            "name": 1,
            "location": 1,
            "fullyVaccinated": 1
        }

        let collection = db.collection('users');
        let statusCollection = db.collection('status');
        let wpCollection = db.collection('workpreferences');

        collection.find({}, include).toArray(function (err, users) {
            var counter = 0;
            // getWorkPreferences(client_db).then(function(){
              for (let u of users) {
                  statusCollection.find({
                          "user": u._id
                      })
                      .sort({
                          date: -1
                      })
                      .limit(1).toArray(function (error, st) {
                          u.status = st[0];

                          if (u.PCRTests && u.PCRTests.length > 0) {
                            wpCollection.find({
                              "user": u._id
                            })
                            .sort({
                              pcrTestDate: -1
                            }).limit(1).toArray(function(err, pcrTest) {
                              u.PCRDate = moment(pcrTest[0].pcrTestDate).format('MMMM Do YYYY'); 
                              allUsers.push(u);
                              isDone();
                            })
                          }
                          else {
                            u.PCRDate = "Not Available";
                            allUsers.push(u);
                            isDone();
                          }  

                          
                      });
              }
            // })


            function isDone() {
                counter += 1;
                if (users.length === counter) {
                    // client_db.close();
                    resolve(true);
                }
            }
        });
    });
}


function getOfficeUserVaccination(client_db, curOffice) {
  return new Promise((resolve, reject) => {
    var officeToSearch = curOffice;
    if (curOffice == "New York - 120 Broadway") officeToSearch = "New York";
    else if (curOffice == "New York - Downtown" || curOffice == "New York - Madison") resolve([]);
    
    let db = client_db.db();
    let userCollection = db.collection('users');
    let vaccinationCollection = db.collection('vaccinations');
 

    let includeUser = {
        "_id": 1,
        "email": 1,
        "name":1
    }
    // Search all users in this office
    userCollection
      .find({location: officeToSearch}, includeUser)
      .toArray( function (err, users) {
        if (err) {
          console.log("vaccination search err", err);
          resolve([]);
        }
        if (!users || users.length == 0) resolve([]);

        let promises = [];

        if (users && Array.isArray(users)) {
          users.forEach(u => {
            let curP = vaccinationCollection.find({
              "user": u._id
            })
            .sort({
              date: -1
            }).toArray();
  
            promises.push(curP);
          });


          Promise.all(promises).then((usersWithVacs) => {
            usersWithVacs.forEach((vaccine, index) => {
              var curVac = {
                manufacturer:null,
                lastDate: null,
                count: 0
              }
              if (!vaccine || vaccine.length == 0) return;
  
              const uniqueManufacturer = [];
  
              vaccine.forEach(v => {
                if (!uniqueManufacturer.includes(v.manufacturer)) uniqueManufacturer.push(v.manufacturer);
              })
              
              
              curVac.count = vaccine.length;
              if (vaccine.length == 1) {
                curVac.manufacturer = vaccine[0].manufacturer    
              }
              else {
                curVac.manufacturer = uniqueManufacturer.join("; ");
              }
  
              curVac.lastDate = vaccine[0].date;                    
              users[index].vaccination = curVac;
            })

            finishedOfficeCount += 1;

            if (finishedOfficeCount == officeCount) {
              console.log("closing db");         
              client_db.close(); 
            }

            resolve(users)

              
          }); 
        }
        else {
          resolve([]);
        } 
    });
  })
}



function sendEmail(emails, location, attachment, attachment3) {
    if (emails == null) return;
    var toEmails = Array.isArray(emails)? emails : [emails];

    const mailOptions = {
        // to: toEmail,
        // to: "hsun@thorntontomasetti.com",
        from: sender,
        bcc: 'hsun@thorntontomasetti.com',
        subject: "Healthy Reentry – Weekly Report for " + location,
        html: content
    };

    if (attachment) {
        mailOptions.attachments = [{
                "content": attachment,
                "filename": "Employees Who Have Signed up.csv",
                "type": "text/csv"
            },
            // {
            //     "content": attachment2,
            //     "filename": "Employees Who Have Not Updated in 7 Days.csv",
            //     "type": "text/csv"
            // },
            {
                "content": attachment3,
                "filename": "Office Breakdown.csv",
                "type": "text/csv"
            }

        ]
    }

    const messages = [];
    toEmails.forEach(function(toEmail){
      var curOption = JSON.parse(JSON.stringify(mailOptions))
      curOption["to"] = toEmail;
      messages.push(curOption);
    })

    sgClient.send(messages).then(() => {
      console.log('emails sent successfully to: ', toEmails, location);
    }).catch(error => {
      console.log(error);
    });

    // sgClient.send(messages, function (err) {
    //     console.log("err?", err)
    //     if (err)
    //         return;
    //     else
    //         console.log('sent');
    //
    // });



}