Secure Sign in System

signIn.gs

function newEntry(e) {
  //get variables
  var securityPhone = '+2348099828250'
  Logger.log('Running')
  var range = e.range;
  
  var value = range.getValue();
  Logger.log(range);
  Logger.log(value);
  var row = range.getRow();
  
  //if change is not in first column or is in the first row, end.
  if (row == 1 || range.getColumn() !== 1){
    return;
  }
  
  //add * to value to match Id database
  Logger.log('Value is: '+value);
 
  
  //get date objects and variables
  var date = new Date();
  var year = date.getYear();
  var monthNumber = date.getMonth();
  var month = new Array();
month[0] = "January";
month[1] = "February";
month[2] = "March";
month[3] = "April";
month[4] = "May";
month[5] = "June";
month[6] = "July";
month[7] = "August";
month[8] = "September";
month[9] = "October";
month[10] = "November";
month[11] = "December";
  
var dayArray = ['Sun', 'Mon', 'Tue', 'Wed', 'Th', 'Fri', 'Sat']
  var day = dayArray[date.getDay()]+' '+date.getDate()
  var time = Utilities.formatDate(date, 'GMT+1:00', "HH:mm:ss");
 var thisSheet = SpreadsheetApp.getActiveSheet();
  var currentMS = Date.now();
   //check if is string
  if (isNaN(value)){
    
  var personObject = getInfoByIndex(value);
      
  }else{
  value = '*'+value+'*';
  //get person object data
  var personObject = getInfoById(value);
  }
  //checks to see visitor is allowed on campus
  if (personObject.type == 'visitor'){
    if (personObject.startValid > currentMS){
      personObject.role = 'void';
      personObject.last = 'Too early';
      var alert = 'ID is not set up for this day: Too Early!';
    } else if (personObject.endValid < currentMS) {
      personObject.last = 'ID Voided';
      personObject.role = 'void';
      var alert = 'This ID is old and has been voided!';
    }
  }
  
  //place into sheet
  thisSheet.getRange(row, 2).setValue(personObject.first);
  thisSheet.getRange(row, 3).setValue(personObject.last);
  thisSheet.getRange(row, 4).setValue(personObject.role);
  thisSheet.getRange(row, 5).setValue(time);
  thisSheet.getRange(row, 6).setValue(currentMS);
  /*
  //if photo was found, place it in the sheet
  if (personObject.photo && personObject.photo !== 'unknown'){
    thisSheet.setRowHeight(row, 85);
   var blob = DriveApp.getFileById(personObject.photo).getAs('image/jpeg');
  thisSheet.insertImage(blob, 7, row);
  }
  */
  
  //if person was unknown, set bg to red and notify security
  if (personObject.first == 'unknown'){
      Logger.log('Unknown Entry')
      
      thisSheet.getRange(row, 1, 1, 7).setBackground('#ff9900');
      SpreadsheetApp.flush();
      var gateName = thisSheet.getParent().getName();
      var message = 'Unknown at '+gateName;
      //sendSms(securityPhone, message);
      //MailApp.sendEmail('douglas.black@aisabuja.com', message, message);
      
  } else if (personObject.role == "void"){
       Logger.log('Unauthorized Entry')
      thisSheet.getRange(row, 1, 1, 7).setBackground('#ff1a1a');
      SpreadsheetApp.flush();
      var gateName = thisSheet.getParent().getName();
      var message = 'Unauthorized Entry at '+gateName;
      //sendSms(securityPhone, message);
      //MailApp.sendEmail('douglas.black@aisabuja.com', message, personObject.first+' '+personObject.last+' has tried to enter campus at '+gateName+' with a voided ID card');
             
     } else {
    //otherwise set bg to green
    thisSheet.getRange(row, 1, 1, 7).setBackground('#66ccff');
    SpreadsheetApp.flush();
    
    
    //get the filename and get the file with that name if it exists, otherwise create it
    var fileName = personObject.first+' '+personObject.last+' '+' Time Card '+year
    Logger.log(fileName);
      var filesIt = DriveApp.getFilesByName(fileName);
    if (filesIt.hasNext()){
      Logger.log('File Found');
      var file = filesIt.next();
      var timeSS = SpreadsheetApp.open(file);
      
    }else{
      Logger.log('File Not Found');
      var oldFile = DriveApp.getFileById('1-s7W5No-xTMkS3KJMVrQuoK0pFjxYSlu8GHRpEJaEEQ');
      var folder = DriveApp.getFolderById('0B3QaE26f2r25WDVVaV9KWi1DZ2M');
      var file = oldFile.makeCopy(fileName, folder);
      var timeSS = SpreadsheetApp.open(file);
    }
    
    //get the sheet with the month name, otherwise create it
    var monthName = month[monthNumber];
    
    
    try {
      var timeSheet = timeSS.getSheetByName(monthName);
      
      var timeValues = timeSheet.getDataRange().getValues();
      Logger.log(timeValues[0][1]);
      Logger.log(monthName+' found');
      //find the row that corrisponds to the day
      var lastEntryRow = findTime(timeValues, day);
    }
    catch(err){
      Logger.log(monthName+' not found');
      var templateSheet = timeSS.getSheetByName('Template Sheet');
      var timeSheet = templateSheet.copyTo(timeSS);
      timeSheet.setName(monthName);
      var lastEntryRow = -1
      
      }
    
    //append logs to log sheets
    var logSS = SpreadsheetApp.openById('1fPIdPqXqW5g0p3gQnRRQt7eTEttYu0SHcA_JigzYLMs');
    var rowContents = [time+' '+day,personObject.first,personObject.last,personObject.role,currentMS,SpreadsheetApp.getActive().getName()]
    var logSheetName = 'Current'
    try {
      var logSheet = logSS.getSheetByName(logSheetName);
      logSheet.appendRow(rowContents)
    }
    catch(e){
     var logSheet = logSS.insertSheet(logSheetName, 0);
     logSheet.appendRow(['Time', 'First Name', 'Last Name', 'Role', 'Unix Timestamp','Gate']);
      logSheet.setFrozenRows(1);
      logSheet.appendRow(rowContents);
      
      
      
    }
    
    //if the person has not signed in before on that day, create a new record their spreadsheet
    if (lastEntryRow === -1){
     
      timeSheet.appendRow([day,time,'','',Date.now(),'','In']);
      if (day !== 6 && day !== 0){
        var hour = date.getHours();
        var minute = date.getMinutes();
        if ((hour >= 7 && minute > 30) || (hour > 7)){
          var daysLate = timeSheet.getRange(2, 8).getValue();
          daysLate++;
          timeSheet.getRange(2, 8).setValue(daysLate);
        }
        
      }
    }else{
      //otherwise update the sign out time on the spreadsheet
      var eTimeOut = Date.now();
      var eTimeIn = timeSheet.getRange(lastEntryRow, 5).getValue();
      var minWorked = Math.floor( (eTimeOut - eTimeIn)/(1000*60));
      timeSheet.getRange(lastEntryRow, 4).setValue(minWorked);
      timeSheet.getRange(lastEntryRow, 3).setValue(time);
      timeSheet.getRange(lastEntryRow, 6).setValue(eTimeOut);
      var status = timeSheet.getRange(lastEntryRow, 7).getValue();
      if (status === 'In'){
        timeSheet.getRange(lastEntryRow, 7).setValue('Out');
        timeSheet.getRange(lastEntryRow, 7).setBackground('blue');
    } else {
      timeSheet.getRange(lastEntryRow, 7).setValue('In');
      timeSheet.getRange(lastEntryRow, 7).setBackground('red');
    }
                           
      
    }
      
  }
  
  
}

//find info from the the id and return an object
function getInfoById(id){
 var idDatabase = SpreadsheetApp.openById('1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A');
  var firstDigits = id.slice(1, 3);
 if (firstDigits=='97'){
   var data = idDatabase.getSheetByName('visitor').getDataRange().getValues();
   var type = 'visitor';
 } else if (firstDigits=='99') {
     var data = idDatabase.getSheetByName('parents').getDataRange().getValues();
   var type = 'parent';
 
   } else if (firstDigits=='10') {
     var data = idDatabase.getSheetByName('students').getDataRange().getValues();
   var type = 'student';
 
   } else{
   var data = idDatabase.getSheetByName('staff').getDataRange().getValues();
   var type = 'staff';
 }
  
  for (var i = 0; i < data.length; i++){
    var tableId = data[i][3];
    if (id == tableId){
      if (type == 'visitor'){
        var personObject = {first:data[i][1], last:data[i][2],id:tableId, role:data[i][4], photo:data[i][5], startValid:data[i][6], endValid:data[i][7], type:type};
      }else{
        var personObject = {first:data[i][1], last:data[i][2],id:tableId, role:data[i][4], photo:data[i][5], type:type};
      }
      Logger.log('person found')
      return personObject;   
    }   
  }
  return {first:'unknown', last:'unknown', id:id, role:'unknown',photo:'unknown'};
}

//finds the time time the person signed in, or submits -1
function findTime(table, day){
  for (var i = 1; i < table.length; i++){
    var tableDay = table[i][0];
    if (tableDay == day){
      return i+1;
      
    }
  }
  return -1;
}
  

function testSubmit(){
  var e = {value: 'test', range: SpreadsheetApp.getActiveSheet().getRange(2, 1)};
  newEntry(e);
}

//Creates a new sheet for the day and places it first, triggered daily
function newSheet(){
  var date = new Date();
  var sheetName = Utilities.formatDate(date, 'UTC+1', 'yyyy-MM-dd');
 var newSheet = SpreadsheetApp.getActive().insertSheet(sheetName, 0);
  newSheet.appendRow(['ID','First','Last','Role','Date','MS Since Epoch']);
  newSheet.setFrozenRows(1);
  
}

function testSms(){
  sendSms('+2349087177882','Test Message. Send John an email if you get this')
  
  
}

function sendSms(number, message) {
  // Get account SID and auth token here:
  //   https://www.twilio.com/user/account
  //////////////////////////////////////////
  return;
  //////////////////////////////////////////
  var accountSid = "ACe774d203ea3cd77da45cd4f1b7afacec";
  var authToken = "91f32de51945e39247fa7d2a6b9e3ba8";
  var url = "https://api.twilio.com/2010-04-01/Accounts/" + accountSid + "/SMS/Messages.json";
  var options = {
    method: "post",
    headers: {
      Authorization: "Basic " + Utilities.base64Encode(accountSid + ":" + authToken)
    },
    payload: {
      // From is one of your Twilio phone numbers
      From: "+18647194988",
      To: number,
      Body: message
    }
  };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response);
}

function nuke(){
  var ss = SpreadsheetApp.getActive();
  var sheets = ss.getSheets();
  sheets[0].clear();
  for (var i = 1; i < sheets.length; i++){
    ss.deleteSheet(sheets[i]);
  }
}

function deleteSheets(){
  var ss = SpreadsheetApp.getActive();
  var sheets = ss.getSheets();
  for (var i = 31; i < sheets.length; i++){
    ss.deleteSheet(sheets[i])
  }
}

  function getInfoByIndex(value){
     //if so check first letter
    var i = 0;
    var firstLetter = value.charAt(0);
    try {
      var indexVar = parseInt(value.slice(1));
      Logger.log(indexVar);
         }
    
    //try to convert remaining into a number
    catch (e){
      Logger.log(e);
      return {first:'unknown', last:'unknown', id:value, role:'unknown',photo:'unknown'};
    }
    if (firstLetter == 'f'){
      var sheetName = 'staff' 
      } else if (firstLetter == 'T'){
        var sheetName = 'tmpParent';
      } else if (firstLetter == 'p' || firstLetter == 'P'){
        var sheetName = 'parentsP';
      }
    if (indexVar == 1){
      indexVar = 600;
    }
    try{
    var data = SpreadsheetApp.openById('1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A').getSheetByName(sheetName).getRange(indexVar, 1, 1, 10).getValues();
    Logger.log(data);
    var personObject = {first:data[i][1], last:data[i][2],id:data[i][3], role:data[i][4], photo:'unknown', type:sheetName};
    Logger.log(personObject);
    return personObject;
    }
    catch(e){
      return {first:'unknown', last:'unknown', id:value, role:'unknown',photo:'unknown',type:'unknown'};
    }
   
  }