hinklefoxmail/inventory.gs Secret

## inventory.gs
function createSS() {
  //get Variables
  var user = Session.getActiveUser();
  var thisSheet = SpreadsheetApp.getActiveSheet();
  var data = thisSheet.getDataRange().getValues();
  var template = DriveApp.getFileById('1QuKvHEt85MivyCLHqMWz02kCTterqM-ed81PyiKD404');
  var myDate = getDate();
  var dateAndTime = myDate.dateAndTime;
  var folder = DriveApp.getFolderById('0B3QaE26f2r25M0lUUjQ3V29HdG8');
  var addSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Added Log");
  //search through document for no id
  Logger.log('Begin search for Id');
  for (var i = 0 ; i < data.length; i++){
    var documentId = data[i][8];
    var row = i+1;
    if (documentId){
      continue;
    }

    var item = getInfoByIndex(i, data);
    Logger.log(item);

  if (!item.documentID){
    Logger.log("Need to Create Sheet");
  }
    //creates a new sheet in the folder and updates the old sheet with the info
    var newFile = template.makeCopy(item.brand+' '+item.type, folder)
    thisSheet.getRange(row, 9).setValue(newFile.getId());
    thisSheet.getRange(row, 10).setValue(newFile.getUrl());
    thisSheet.getRange(row, 12).setValue(item.startingQuantity);
    thisSheet.getRange(row, 6).setValue(item.startingQuantity);

    var newSS = SpreadsheetApp.open(newFile);
    var newSheet = newSS.getSheets()[0];
    newSheet.appendRow([dateAndTime, user, user, item.startingQuantity*-1, item.startingQuantity]);


    //Log to added Sheet
    var rowData = [myDate.dateAndTime,user,item.brand+' '+item.type,item.startingQuantity,item.uSD,item.startingQuantity*item.uSD]
    addSheet.appendRow(rowData);

    }

}


function getInfoById(id,type){
  Logger.log(id);
  Logger.log(type);
  //change vars depending on searched thing type
 if (type == 'item'){
 var dataId = '10ZExgJ1SkUu1_eOPf1js2so3KOGZXrl3HA0Q_KIkS30';
 var dataSheetName = 'overview';
 var barRow = 0;
  } else if (type == 'person'){
    var dataId = '1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A';
    var dataSheetName = 'Staff';
    var id = '*'+id+'*';
    var barRow = 3;
  }


  //get ss
 var data = SpreadsheetApp.openById(dataId).getSheetByName(dataSheetName).getDataRange().getValues();
  for (var i = 0; i < data.length; i++){

    //insert barcode check field here
    var tableId = data[i][barRow];
    //If match, do___
    if (id == tableId){
      var myObject = getInfoByIndex(i, data)
      myObject.id = id;
      myObject.kind = type;
      return myObject;
  }


  }
   //Return Object with all values false
  var myObject = {index:-1, id:id, kind:type};
  for (var j = 0; j < data[0].length; j++){
        var propertyName = camelize(data[0][j]);
        myObject[propertyName] = false;
    }
    return myObject;
}


function getInfoByIndex(index, data){
  Logger.log('Getting Info by Index');
  var myObject = {index:index}
  for (var j = 0; j < data[0].length; j++){
    Logger.log(data[0][j])
        var propertyName = camelize(data[0][j]);
        myObject[propertyName] = data[index][j];
    }
      return myObject;
}


function camelize(str) {
  return str.replace(/(?:^\w|[A-Z]|\b\w|\s+)/g, function(match, index) {
    if (+match === 0) return ""; // or if (/\s+/.test(match)) for white spaces
    return index == 0 ? match.toLowerCase() : match.toUpperCase();
  });
}


function checkout(){
  //get vars
  var user = Session.getActiveUser();

  var thisSheet = SpreadsheetApp.getActive().getSheetByName('check out');
  var rawValues = thisSheet.getDataRange().getValues();
  Logger.log(rawValues);
  var taker = rawValues[1][1];
  Logger.log(taker);

  var department = rawValues[1][2];
  var deptName = 'Other'
  for (var i = 0; i < rawValues.length; i++){
    if (department == rawValues[i][5]){
      var deptName = rawValues[i][6];
      Logger.log(deptName);
      break;
    }

  }


  //get the person
  if (!taker || !department){
    SpreadsheetApp.getUi().alert('Incomplete info, checkout not run');
    return;
  } else if (isNaN(taker)){
    var person = {first: taker, last: '(No ID)',dept: deptName};

  } else {
  var person = getInfoById(taker,'person');
  person.dept = deptName;
  Logger.log(person);
    if (!person.first){
      SpreadsheetApp.getUi().alert('Unknown ID, checkout not run. If no id is present, just type the name in for person');
      return;
    }
  }
  var items = [];
  for (var i = 2; i < rawValues.length; i++){

   var barcode = rawValues[i][1];

    //if there is a item
    Logger.log(barcode);
    if (barcode){
      var amountTaken = rawValues[i][2];

      // if amount is missing, it is now 1
      if (!amountTaken){
        amountTaken = 1;
      }
      //update the sheets
      items.push(updateSheets(barcode, person, amountTaken));


      }

    }


  //give prompt that action has been successful
  var prompt = person.first+' '+person.last+' has taken:'
  for (var i = 0; i < items.length; i++){
    prompt = prompt +'\n'+items[i];
  }

  var values = [['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['','']]
  thisSheet.getRange(2, 2, 20, 2).setValues(values);
 SpreadsheetApp.getUi().alert(prompt);


}

function addStock(){
   //get vars
  var user = Session.getActiveUser();
  var thisSheet = SpreadsheetApp.getActive().getSheetByName('add stock');
  var rawValues = thisSheet.getDataRange().getValues();
  var department = 'Added Items';
  var person = {first: user, last:' ', dept: department}

  //get item data from barcode
  var items = [];
  for (var i = 1; i < rawValues.length; i++){

   var barcode = rawValues[i][1];

    //if there is a item
    Logger.log(barcode);
    if (barcode){
      var amountTaken = 0-rawValues[i][2];

      // if amount is missing, it is now 1
      if (!amountTaken){
        amountTaken = -1;
      }
      //update the sheets && find info
      items.push(updateSheets(barcode, person, amountTaken,i));
    }
  }

  //create prompt if action has been succesful
  var prompt = 'The following items have been added to the inventory:'
  for (var i = 0; i < items.length; i++){
    prompt = prompt +'\n'+items[i];
  }

  var values = [['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['','']]
  thisSheet.getRange(2, 2, 30, 2).setValues(values);
 SpreadsheetApp.getUi().alert(prompt);

}

function onOpen(){
  SpreadsheetApp.getUi().createMenu('Add Items').addItem('Add New Items', 'createSS').addItem('Add Stock', 'addStock').addToUi();
  SpreadsheetApp.getUi().createMenu('Checkout').addItem('Checkout', 'checkout').addToUi();
}

function updateSheets(barcode, person, amountTaken){
  //get vars
  var myDate = getDate();
  var user = Session.getActiveUser();

  //find item
  var item = getInfoById(barcode, 'item');
  if (!item.type){
    return (barcode+' is unknown. Please update the inventory');
  }
    item.taken = amountTaken;
    Logger.log(item);
  try{
    var itemSheet = SpreadsheetApp.openById(item.documentID);
  }
  catch(e){
     return (barcode+' is unknown. Please update the inventory');
  }
    var itemName = itemSheet.getName();

    item.remaining = item.currentQuantity - item.taken;
    itemSheet.appendRow([myDate.dateAndTime,person.first+' '+person.last,user,item.taken,item.remaining]);

    //update info on overview sheet
    SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 6).setValue(item.remaining);

  //if items were added...
  if (amountTaken <0){
    SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 4).setValue(item.startingQuantity - item.taken);
    if (!item.totalAdded){
      item.totalAdded = 0;
    }
    SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 12).setValue(item.totalAdded - item.taken);
    var rowData = [myDate.dateAndTime,person.first+' '+person.last,item.brand+' '+item.type,(item.taken*-1),item.uSD,item.taken*item.uSD*-1];
    SpreadsheetApp.getActive().getSheetByName('Added Log').appendRow(rowData);
  } else {
    //if items were removed...
    if (!item.totalRemoved){
      item.totalRemoved = 0;
    }
    SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 11).setValue(item.totalRemoved + item.taken);

    //get school year
    if (myDate.monthNumber < 7){
      var year = myDate.year-1+'-'+myDate.year;
    } else {
      var year = myDate.year+'-'+myDate.year+1;
    }

    //if dept log exists, get it
    var fileName = person.dept+' Inventory Checkout for '+year;
    if (DriveApp.getFilesByName(fileName).hasNext()){
      var deptSS = SpreadsheetApp.open(DriveApp.getFilesByName(fileName).next());
    } else {

      //otherwise make it
      var folder = DriveApp.getFolderById('0B3QaE26f2r25aE1QSXVSY1Z6VHM');
      var template = DriveApp.getFileById('1vvZi_AJq0kH7FN9xCIHskAUkeZrRCmQdWqnyjfEwRP0')
      var newFile = template.makeCopy(fileName, folder);
      deptSS = SpreadsheetApp.open(newFile);
    }

    var deptSheet = deptSS.getSheetByName('Total Used');
    var dataArray = [myDate.dateAndTime,person.first+' '+person.last,item.brand+' '+item.type,item.taken,item.uSD,item.taken*item.uSD]
    //and update
    deptSheet.appendRow(dataArray);

    var monthSheet = deptSS.getSheetByName(myDate.month);
    monthSheet.appendRow(dataArray);
    SpreadsheetApp.getActive().getSheetByName('Master Log').appendRow(dataArray);
  }


    Logger.log(item.remaining);
    SpreadsheetApp.flush();
    var abs = Math.abs(item.taken)
    var prompt = abs+' '+itemName;
    Logger.log(prompt);
    return (prompt);

}

function getDate(){
  //get date objects and variables
  var myDate = {};
  var date = new Date();
  myDate.year = date.getFullYear();
  myDate.monthNumber = date.getMonth();
  var monthArray = new Array();
    monthArray[0] = "Jan";
    monthArray[1] = "Feb";
    monthArray[2] = "Mar";
    monthArray[3] = "Apr";
    monthArray[4] = "May";
    monthArray[5] = "June";
    monthArray[6] = "July";
    monthArray[7] = "Aug";
    monthArray[8] = "Sep";
    monthArray[9] = "Oct";
    monthArray[10] = "Nov";
    monthArray[11] = "Dec";

  var dayArray = ['Sun', 'Mon', 'Tue', 'Wed', 'Th', 'Fri', 'Sat']
  myDate.weekday = dayArray[date.getDay()];
  myDate.month = monthArray[myDate.monthNumber];
  myDate.day = dayArray[date.getDay()]+' '+date.getDate()
  myDate.time = Utilities.formatDate(date, 'GMT+1:00', "HH:mm:ss");
  myDate.now = Date.now();
  myDate.dateAndTime = Utilities.formatDate(date, 'GMT+1:00', "MM/dd/yyyy HH:mm");
  myDate.origin = date;
  return myDate;
}
	function createSS() {
	//get Variables
	var user = Session.getActiveUser();
	var thisSheet = SpreadsheetApp.getActiveSheet();
	var data = thisSheet.getDataRange().getValues();
	var template = DriveApp.getFileById('1QuKvHEt85MivyCLHqMWz02kCTterqM-ed81PyiKD404');
	var myDate = getDate();
	var dateAndTime = myDate.dateAndTime;
	var folder = DriveApp.getFolderById('0B3QaE26f2r25M0lUUjQ3V29HdG8');
	var addSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Added Log");
	//search through document for no id
	Logger.log('Begin search for Id');
	for (var i = 0 ; i < data.length; i++){
	var documentId = data[i][8];
	var row = i+1;
	if (documentId){
	continue;
	}

	var item = getInfoByIndex(i, data);
	Logger.log(item);

	if (!item.documentID){
	Logger.log("Need to Create Sheet");
	}
	//creates a new sheet in the folder and updates the old sheet with the info
	var newFile = template.makeCopy(item.brand+' '+item.type, folder)
	thisSheet.getRange(row, 9).setValue(newFile.getId());
	thisSheet.getRange(row, 10).setValue(newFile.getUrl());
	thisSheet.getRange(row, 12).setValue(item.startingQuantity);
	thisSheet.getRange(row, 6).setValue(item.startingQuantity);

	var newSS = SpreadsheetApp.open(newFile);
	var newSheet = newSS.getSheets()[0];
	newSheet.appendRow([dateAndTime, user, user, item.startingQuantity*-1, item.startingQuantity]);


	//Log to added Sheet
	var rowData = [myDate.dateAndTime,user,item.brand+' '+item.type,item.startingQuantity,item.uSD,item.startingQuantity*item.uSD]
	addSheet.appendRow(rowData);

	}

	}



	function getInfoById(id,type){
	Logger.log(id);
	Logger.log(type);
	//change vars depending on searched thing type
	if (type == 'item'){
	var dataId = '10ZExgJ1SkUu1_eOPf1js2so3KOGZXrl3HA0Q_KIkS30';
	var dataSheetName = 'overview';
	var barRow = 0;
	} else if (type == 'person'){
	var dataId = '1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A';
	var dataSheetName = 'Staff';
	var id = ''+id+'';
	var barRow = 3;
	}


	//get ss
	var data = SpreadsheetApp.openById(dataId).getSheetByName(dataSheetName).getDataRange().getValues();
	for (var i = 0; i < data.length; i++){

	//insert barcode check field here
	var tableId = data[i][barRow];
	//If match, do___
	if (id == tableId){
	var myObject = getInfoByIndex(i, data)
	myObject.id = id;
	myObject.kind = type;
	return myObject;
	}


	}
	//Return Object with all values false
	var myObject = {index:-1, id:id, kind:type};
	for (var j = 0; j < data[0].length; j++){
	var propertyName = camelize(data[0][j]);
	myObject[propertyName] = false;
	}
	return myObject;
	}


	function getInfoByIndex(index, data){
	Logger.log('Getting Info by Index');
	var myObject = {index:index}
	for (var j = 0; j < data[0].length; j++){
	Logger.log(data[0][j])
	var propertyName = camelize(data[0][j]);
	myObject[propertyName] = data[index][j];
	}
	return myObject;
	}


	function camelize(str) {
	return str.replace(/(?:^\w\|[A-Z]\|\b\w\|\s+)/g, function(match, index) {
	if (+match === 0) return ""; // or if (/\s+/.test(match)) for white spaces
	return index == 0 ? match.toLowerCase() : match.toUpperCase();
	});
	}


	function checkout(){
	//get vars
	var user = Session.getActiveUser();

	var thisSheet = SpreadsheetApp.getActive().getSheetByName('check out');
	var rawValues = thisSheet.getDataRange().getValues();
	Logger.log(rawValues);
	var taker = rawValues[1][1];
	Logger.log(taker);

	var department = rawValues[1][2];
	var deptName = 'Other'
	for (var i = 0; i < rawValues.length; i++){
	if (department == rawValues[i][5]){
	var deptName = rawValues[i][6];
	Logger.log(deptName);
	break;
	}

	}


	//get the person
	if (!taker \|\| !department){
	SpreadsheetApp.getUi().alert('Incomplete info, checkout not run');
	return;
	} else if (isNaN(taker)){
	var person = {first: taker, last: '(No ID)',dept: deptName};

	} else {
	var person = getInfoById(taker,'person');
	person.dept = deptName;
	Logger.log(person);
	if (!person.first){
	SpreadsheetApp.getUi().alert('Unknown ID, checkout not run. If no id is present, just type the name in for person');
	return;
	}
	}
	var items = [];
	for (var i = 2; i < rawValues.length; i++){

	var barcode = rawValues[i][1];

	//if there is a item
	Logger.log(barcode);
	if (barcode){
	var amountTaken = rawValues[i][2];

	// if amount is missing, it is now 1
	if (!amountTaken){
	amountTaken = 1;
	}
	//update the sheets
	items.push(updateSheets(barcode, person, amountTaken));



	}

	}


	//give prompt that action has been successful
	var prompt = person.first+' '+person.last+' has taken:'
	for (var i = 0; i < items.length; i++){
	prompt = prompt +'\n'+items[i];
	}

	var values = [['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['','']]
	thisSheet.getRange(2, 2, 20, 2).setValues(values);
	SpreadsheetApp.getUi().alert(prompt);


	}

	function addStock(){
	//get vars
	var user = Session.getActiveUser();
	var thisSheet = SpreadsheetApp.getActive().getSheetByName('add stock');
	var rawValues = thisSheet.getDataRange().getValues();
	var department = 'Added Items';
	var person = {first: user, last:' ', dept: department}

	//get item data from barcode
	var items = [];
	for (var i = 1; i < rawValues.length; i++){

	var barcode = rawValues[i][1];

	//if there is a item
	Logger.log(barcode);
	if (barcode){
	var amountTaken = 0-rawValues[i][2];

	// if amount is missing, it is now 1
	if (!amountTaken){
	amountTaken = -1;
	}
	//update the sheets && find info
	items.push(updateSheets(barcode, person, amountTaken,i));
	}
	}

	//create prompt if action has been succesful
	var prompt = 'The following items have been added to the inventory:'
	for (var i = 0; i < items.length; i++){
	prompt = prompt +'\n'+items[i];
	}

	var values = [['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['','']]
	thisSheet.getRange(2, 2, 30, 2).setValues(values);
	SpreadsheetApp.getUi().alert(prompt);

	}

	function onOpen(){
	SpreadsheetApp.getUi().createMenu('Add Items').addItem('Add New Items', 'createSS').addItem('Add Stock', 'addStock').addToUi();
	SpreadsheetApp.getUi().createMenu('Checkout').addItem('Checkout', 'checkout').addToUi();
	}

	function updateSheets(barcode, person, amountTaken){
	//get vars
	var myDate = getDate();
	var user = Session.getActiveUser();

	//find item
	var item = getInfoById(barcode, 'item');
	if (!item.type){
	return (barcode+' is unknown. Please update the inventory');
	}
	item.taken = amountTaken;
	Logger.log(item);
	try{
	var itemSheet = SpreadsheetApp.openById(item.documentID);
	}
	catch(e){
	return (barcode+' is unknown. Please update the inventory');
	}
	var itemName = itemSheet.getName();

	item.remaining = item.currentQuantity - item.taken;
	itemSheet.appendRow([myDate.dateAndTime,person.first+' '+person.last,user,item.taken,item.remaining]);

	//update info on overview sheet
	SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 6).setValue(item.remaining);

	//if items were added...
	if (amountTaken <0){
	SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 4).setValue(item.startingQuantity - item.taken);
	if (!item.totalAdded){
	item.totalAdded = 0;
	}
	SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 12).setValue(item.totalAdded - item.taken);
	var rowData = [myDate.dateAndTime,person.first+' '+person.last,item.brand+' '+item.type,(item.taken-1),item.uSD,item.takenitem.uSD*-1];
	SpreadsheetApp.getActive().getSheetByName('Added Log').appendRow(rowData);
	} else {
	//if items were removed...
	if (!item.totalRemoved){
	item.totalRemoved = 0;
	}
	SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 11).setValue(item.totalRemoved + item.taken);

	//get school year
	if (myDate.monthNumber < 7){
	var year = myDate.year-1+'-'+myDate.year;
	} else {
	var year = myDate.year+'-'+myDate.year+1;
	}

	//if dept log exists, get it
	var fileName = person.dept+' Inventory Checkout for '+year;
	if (DriveApp.getFilesByName(fileName).hasNext()){
	var deptSS = SpreadsheetApp.open(DriveApp.getFilesByName(fileName).next());
	} else {

	//otherwise make it
	var folder = DriveApp.getFolderById('0B3QaE26f2r25aE1QSXVSY1Z6VHM');
	var template = DriveApp.getFileById('1vvZi_AJq0kH7FN9xCIHskAUkeZrRCmQdWqnyjfEwRP0')
	var newFile = template.makeCopy(fileName, folder);
	deptSS = SpreadsheetApp.open(newFile);
	}

	var deptSheet = deptSS.getSheetByName('Total Used');
	var dataArray = [myDate.dateAndTime,person.first+' '+person.last,item.brand+' '+item.type,item.taken,item.uSD,item.taken*item.uSD]
	//and update
	deptSheet.appendRow(dataArray);

	var monthSheet = deptSS.getSheetByName(myDate.month);
	monthSheet.appendRow(dataArray);
	SpreadsheetApp.getActive().getSheetByName('Master Log').appendRow(dataArray);
	}


	Logger.log(item.remaining);
	SpreadsheetApp.flush();
	var abs = Math.abs(item.taken)
	var prompt = abs+' '+itemName;
	Logger.log(prompt);
	return (prompt);

	}

	function getDate(){
	//get date objects and variables
	var myDate = {};
	var date = new Date();
	myDate.year = date.getFullYear();
	myDate.monthNumber = date.getMonth();
	var monthArray = new Array();
	monthArray[0] = "Jan";
	monthArray[1] = "Feb";
	monthArray[2] = "Mar";
	monthArray[3] = "Apr";
	monthArray[4] = "May";
	monthArray[5] = "June";
	monthArray[6] = "July";
	monthArray[7] = "Aug";
	monthArray[8] = "Sep";
	monthArray[9] = "Oct";
	monthArray[10] = "Nov";
	monthArray[11] = "Dec";

	var dayArray = ['Sun', 'Mon', 'Tue', 'Wed', 'Th', 'Fri', 'Sat']
	myDate.weekday = dayArray[date.getDay()];
	myDate.month = monthArray[myDate.monthNumber];
	myDate.day = dayArray[date.getDay()]+' '+date.getDate()
	myDate.time = Utilities.formatDate(date, 'GMT+1:00', "HH:mm:ss");
	myDate.now = Date.now();
	myDate.dateAndTime = Utilities.formatDate(date, 'GMT+1:00', "MM/dd/yyyy HH:mm");
	myDate.origin = date;
	return myDate;
	}