LMS System

lms.gs

function onOpen(){
  SpreadsheetApp.getUi().createMenu('Register').addItem('Register', 'register').addToUi();
  
}

function register(){
  var user = Session.getActiveUser();
}

function newEntry(e) {
  //get variables
  Logger.log('Running')
  var range = e.range;
  var user = e.user;
  var user = Session.getActiveUser();
  Logger.log(user);
  var value = range.getValue();
  Logger.log(range);
  var row = range.getRow();
  var amount = 1000;
  var purchase = 'Lunch';
  
  //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);
  value = '*'+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();
  //get person object data
  var personObject = getInfoById(value, amount);
  //place into sheet
  Logger.log(personObject.newBalance);
  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(personObject.newBalance);
  thisSheet.getRange(row, 6).setValue(time);
  thisSheet.getRange(row, 7).setValue(currentMS);
  
  
  //if person was unknown, set bg to red and notify security
  if (isNaN(personObject.newBalance)){
      Logger.log('Unauthorized Entry')
      thisSheet.getRange(row, 1, 1, 7).setBackground('#FF6969');
      SpreadsheetApp.flush();
   
      
  } else {
    //otherwise set bg to green
    thisSheet.getRange(row, 1, 1, 7).setBackground('#DAF7A6');
    SpreadsheetApp.flush();
    
    
    //get the filename and get the file with that name if it exists, otherwise create it
    var fileName = personObject.first+' '+personObject.last+' '+' Transaction History '+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('1kkt-WobX4hkJeZnOQ7cYGu-8LFDxrQoycbc6rNmDGYA');
      var folder = DriveApp.getFolderById('0B3QaE26f2r25dTZXeXlkRkVLTHM');
      var file = oldFile.makeCopy(fileName, folder);
      var timeSS = SpreadsheetApp.open(file);
      var databaseSheet = SpreadsheetApp.openById('1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A').getSheetByName("Staff");
      databaseSheet.getRange(personObject.index, 9).setValue(file.getUrl());
      databaseSheet.getRange(personObject.index, 10).setValue(file.getId());
    }
    
    //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
    }
    catch(err){
      Logger.log(monthName+' not found');
      var timeSheet = timeSS.insertSheet(monthName, 0);
      timeSheet.appendRow(['Date','Time','Purchase','Price','Balance After Purchase','Transaction ID']);
      timeSheet.setFrozenRows(1);
      timeSheet.activate();
        
      
      }
    
    //append log to transaction history
    timeSheet.appendRow([day,time, purchase, amount,personObject.newBalance, currentMS]);
    
    //append logs to log sheets
    var logSS = SpreadsheetApp.openById('1gXcnNi-IJAgS4bThVQ71mhbZNyo1QHy-2Gs-v77oQfs');
    var rowContents = [time,purchase, amount, personObject.first,personObject.last,personObject.role,user,currentMS]
    var logSheetName = monthName+' '+day
    try {
      var logSheet = logSS.getSheetByName(logSheetName);
      logSheet.appendRow(rowContents)
    }
    catch(e){
     var logSheet = logSS.insertSheet(logSheetName, 0);
     logSheet.appendRow(['Time','Purchase','Amount', 'First Name', 'Last Name', 'Role','Cashier', 'Transaction ID']);
      logSheet.setFrozenRows(1);
      logSheet.appendRow(rowContents);
      
      
      
    }
    
      
  }
  
  
}

//find info from the the id and return an object, also updates the balance amount
function getInfoById(id, amount){
 var sheet = SpreadsheetApp.openById('1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A').getSheetByName("Staff");
 var data = sheet.getDataRange().getValues();
  for (var i = 0; i < data.length; i++){
    var tableId = data[i][3];
    if (id == tableId){
      var personObject = {first:data[i][1], last:data[i][2],id:tableId, role:data[i][4], photo:data[i][5], balance:data[i][7], index:i+1};
      
      //if not authorized by a number on ss return unauthorized
      if (isNaN(personObject.balance)){
        personObject.newBalance = 'Not Authorized';
        return personObject;
      }
      Logger.log("amount is")
      Logger.log(amount)
      Logger.log("Current Balance is")
      Logger.log(personObject.balance);
      personObject.newBalance = personObject.balance-amount;
      sheet.getRange(i+1, 8).setValue(personObject.newBalance);
      
      Logger.log('person found')
      return personObject;   
    }   
  }
    return {first:'unknown', last:'unknown', id:id, role:'unknown',photo:'unknown', balance:'Not Authorized',newBalance:'Not Authorized'};
}


  

function testSubmit(){
  var e = {value: 'test', range: SpreadsheetApp.getActiveSheet().getRange(2, 1), user:'john.hinkle@aisabuja.com'};
  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','Balance','Time','Transaction Id']);
  newSheet.setFrozenRows(1);
  
}


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