Create a new version of paste:

// ===========================================================================
// ===========================================================================
// CSV import
// ===========================================================================
// ===========================================================================

$(document).on('knack-scene-render.scene_XXX', function(event, view, record) { 

//Import progress window functions - a super super ugly status display window
  $("<table id='statusOverlay'><tbody><tr><td><b> Import progress Window </b></td></tr></tbody></table>").css({
        "position": "fixed",
        "top": "20%",
        "left": "60%",
        "width": "40%",
        "height": "90%",
        "background-color": "rgba(255,255,0,.7)",
        "z-index": 10000,
        "vertical-align": "top",
        "text-align": "right",
        "color": "#000",
        "font-size": "10px",
    }).appendTo("#kn-scene_344");
 

function updateStatusInfo(myText){ //a function that updates the table display thing above
	$("#statusOverlay > tbody > tr > td").append("<br />"+myText);
};

//In my example, I had to link all imported records to the currently open "Projects" details page:
  var filetext
  var knackProjectID //the knack record identifier for this page's project
  
  //this is a "details" view from which I am extracting the unique front-facing project code, in order to look it up and get the knack back-end record ID
  var projectCode = $("#view_831 > div.kn-details-column.first > div > div > table > tbody > tr.field_70 > td > span").text()
 
  console.log(projectCode);
  
  //All of our projects have different donors too. Budgetlines are connected to a project and to a donor. Projects and budgetlines are not connected directly.
  //However, I can still make a details view to show all the donors associated with all the budget lines associated with the projects.
  //I had to be sure the user didn't try to import a new donor, so this variable will help keep track of that:
  var listOfDonors = [[],[]];
  var currentProjectCurrencyID = ""
  var x = {} //global counter
  var existingBudgetLines //all existing budget lines
  var scenarios
  var initialCurrencyConversionRate //divide by this value to convert from donor currency to USD
  var PPcategories
  
  updateStatusInfo("Looking up project record");
  
  // api to get the project record ID from the project code
			  var api_url = 'https://api.knack.com/v1/scenes/scene_186/views/view_889/records' //Projects table
			  userToken = Knack.getUserToken()
			  // preparing filters
			  var filters = [
				{
				  field: 'field_70',
				  operator: 'is',
				  value: projectCode //find this Project Code record
				}
			  ]
			   
			  // add to URL
			  api_url += '?filters=' + encodeURIComponent(JSON.stringify(filters));
		
			Knack.showSpinner();
			$.ajax({
			  url: api_url
			, type: 'GET'
			, headers: {
						'X-Knack-Application-Id': '56fb8b0fad25a3be79b5054a'
						, "X-Knack-REST-API-KEY": 'knack'
					  , "Authorization": userToken //userToken is a public variable I set on each page load
						}
				, success: function(data) {							//once you successflly get the record
                  console.log(data)	
				  if (data.records.length != 1) {
					  alert("Fatal error! Searched for project code " + projectCode + "which should be unique (1 result). Actually got " + data.length + "results. Is there a duplicate project code?!")
				  } else {
						knackProjectID = data.records["0"].id
					  currentProjectCurrencyID = data.records["0"].field_922_raw["0"].id //Projects can be in different currencies, so are connected to currencies object.
					  console.log("Current project currency ID:") //the API call returns the connection ID (not field text) of the currencies.
					  console.log(currentProjectCurrencyID);
					  initialCurrencyConversionRate = data.records["0"].field_932_raw; //I have to convert these rates to USD as well, though
					  console.log("initial Currency Conversion Rate:")
					  console.log(initialCurrencyConversionRate)
					  if (initialCurrencyConversionRate == "" || initialCurrencyConversionRate == 0){ //if not entered, assume 1
						 updateStatusInfo("<b> No initial currency conversion rate set. Assuming donor contract currency is USD. </b>"); 
						 initialCurrencyConversionRate = 1
						 alert("No initial currency conversion rate has been set for this project. To work out default procurement scenarios, import will assume donor contract currency is USD. If this is incorrect, please first go back to the previous page and specify the donor contract currency.")
					  }
					  console.log("Knack Project ID:")
					  console.log(knackProjectID);
						findExistingBudgetLines();
						updateStatusInfo("Retrieved project record.");  
				  }

				}
				, error: function (request, status, error) {
						handleAjaxError(request, status, error);
						}
			});
			
			
			
	function findExistingBudgetLines(){
		
		var budgetLinesFromDetailsView = $("#view_831 > div.kn-details-column.first > div > div > table > tbody > tr.field_82 > td > span").text()  //this is a details view showing all budget lines
		existingBudgetLines = budgetLinesFromDetailsView.split(',').map(function(item) { //function take off whitespace at start/end and split by , character
			return item.trim();
		}); 
		console.log("Existing budget lines:")
		console.log(existingBudgetLines);
		
		if (existingBudgetLines.length > 1) { // a few friendly notifications
			updateStatusInfo("Some budget lines have already been entered for this project. These will be skipped during the import process.")
		} else if (existingBudgetLines.length == 1 && existingBudgetLines[0] != "ACT") {
			updateStatusInfo("There is already a budget line added to this project (other than the default 'ACT' line). Please ensure the CSV file you want to upload does not have duplicates of lines already on this system.")
		} else {
			updateStatusInfo("Done.");

		}
		updateStatusInfo("Looking up Procurement Scenario Limits"); //we have to set a "default procurement scenario" field based on the value of the line, in USD
		lookupProcurementScenarioLimits();
	}

	function lookupProcurementScenarioLimits(){  //looks up procurement scenario limits. e.g < 200USD = scenario "A" etc
		  // api 
			  var api_url = 'https://api.knack.com/v1/scenes/scene_186/views/view_987/records?sort_field=field_899&sort_order=asc' //Procurement Limits table	
			  userToken = Knack.getUserToken();
			Knack.showSpinner();
			$.ajax({
			  url: api_url
			, type: 'GET'
			, headers: {
						'X-Knack-Application-Id': '56fb8b0fad25a3be79b5054a'
						, "X-Knack-REST-API-KEY": 'knack'
					  , "Authorization": userToken
						}
				, success: function(data) {							//once you successflly get the record	
                 scenarios = data //set variable
				 console.log("Scenarios:")
				 console.log(scenarios);
				 addFileBox();
				}
				, error: function (request, status, error) {
						handleAjaxError(request, status, error);
						}
			});
	}
	

				
function addFileBox(){
	
	$('#view_831').append('<br><p> Please select a CSV file for import: </p>');
	$('#view_831').append('<input type="file" id="myFileBox" name="file" enctype="multipart/form-data" />'); //add an input box
	Knack.hideSpinner()
	
	document.getElementById('myFileBox').addEventListener('change', function(e) { //once a file has been selected, load it up
			console.log("Called fileChanged function");
			var file = myFileBox.files[0];
  			console.log(file);
			var textType = /.+(\.csv)$/ ;
  			console.log(file.type)
			$("#myImportButton").remove();

			if (file.name.match(textType)) {  //check for .csv file extension
				var reader = new FileReader();
				
              
              	reader.readAsText(file);	//load/read this as a text file
				reader.onload = function(e) { //once loaded, do this
                  	fileText = reader.result;    	
                  	processData(fileText);
					console.log("Currency conversion rate as of addFileBox function:")
					console.log(initialCurrencyConversionRate)
				}	
                
			} else {
				alert("File not supported! Only files with a .csv extension can be uploaded here.")
			}
		});
};
 // Here I display the CSV file to the user but with my own "custom" headings, to make sure everything is "matching up". Then I prompt the user to continue.
 function processData(fileText) {

	 Knack.showSpinner();
	 $('#view_831 > table.table-data-omset').remove()  //this gets rid of the display table, in the case that the user changed the file.
    var allTextLines = fileText.split(/\r\n|\n/); //split the CSV file in to rows
    var headers = allTextLines[0].split(','); //split again by comma
	//NOTE: THIS WORKS FOR LEGACY CSV FILES WHERE THE COMMAS IS REALLY THE DELIMITER
	//EXCEL CSV FILES TAKE A DIFFERENT FORMAT, each row as follows:
	//"Field1","Field2","Field3"
	//Modify the above code to suit. I was importing from a program which did just strictly delimit by comma only.
	
   console.log("Headers:")
   console.log(headers)
   
    var lines = [];

    for (var i=1; i<allTextLines.length; i++) { //FOR EACH ROW
        var data = allTextLines[i].split(',');  //data = an individual row
      console.log("Data row extracted:")
	  console.log(data);
        if (data.length == headers.length) { //If the number of fields extracted was equal to the number of header fields (OK)
            var tarr = [];
            for (var j=0; j<headers.length; j++) { //FOR EACH COLUMN
                tarr.push(data[j]); //Add column to second dimension of data array data[j,0], data[j,1] etc...
            }
            lines.push(tarr); //then push that row on to the lines object
        } else { //otherwise, there was a comma within the data itself - show a warning and skip
		if (data.length > 1)	{
			var number = i +1;
			alert("Please check row " + number + " carefully and ensure there is no comma (,) present in any of the CSV cells. Otherwise this row will not be imported.")
			};
		}
    }
    console.log(lines);
   	console.log(lines.length);
   	console.log(lines[0].length);
   
   
   $('#view_831').append('<table class="table table-striped table-bordered table-data-omset"><tbody></tbody></table>') //draw a table
   
   var tableOmset = $('table.table-data-omset');
   
	var tbodyTableOmset = tableOmset.find('tbody');
   
	var trTableOmset = '';
	
	//add custom headers
	
	trTableOmset += '<tr>'  ;    
	trTableOmset += '<td><h4>'+  " x " +'</h4></td>';
		
    	for(var b=0; b<headers.length; b++){
                 
		if (b==0) {
			  trTableOmset += '<td><h4>'+ "Custom header 1" +'</h4></td>';
		  }
		  
		  else if (b==2){
			trTableOmset += '<td><h4>'+ "DONOR CODE" +'</h4></td>';  
		  }		  
		  
		  else if (b==3){
			trTableOmset += '<td><h4>'+ "BUDGET LINE (LAST THREE LETTERS)" +'</h4></td>';  
		  }
		  
		  else if (b==6){
			trTableOmset += '<td><h4>'+ "LINE DESCRIPTION" +'</h4></td>';  
		  }
		  
		  else if (b==10){
			trTableOmset += '<td><h4>'+ "VALUE (donor currency)" +'</h4></td>';  
		  }
		  else {
			trTableOmset += '<td> x </td>';    
		  }
		  
     	}
		
    	trTableOmset += '</tr>' ;
	
	// add headers from CSV

		trTableOmset += '<tr>'  ;    
		trTableOmset += '<td><b>'+ "1" +'</b></td>'; //number first column
		
    	for(var b=0; b<headers.length; b++){ //add headers from CSV file
          

		  
	        if(headers[b] === null || headers[b].length > 40){
    	         headers[b]= 'x';
        	}
          
        	trTableOmset += '<td><b>'+ headers[b] +'</b></td>';
     	}
		
    	trTableOmset += '</tr>' ;
	
 		
	
	//add data, row by row
	for(var a=0; a<lines.length; a++){ //for each line of CSV
		if (lines[a][3]!=""){ //Some of my CSV lines were "header" information and not actual data to import. In that case, column [3] was blank
								// so, if blank, do this:
			currentRow = a + 2 ; //row number (a indexes from 0, excel indexes rows from 1 and has a header)
			trTableOmset += '<tr>'  ;     
			trTableOmset += '<td><b>'+ currentRow +'</b></td>';	//label the row
			for(var b=0; b<lines[0].length; b++){ // for each row
			  
				if(lines[a][b] === null || lines[a][b].length > 80){ //Some of my fields had very long text that I didn't need, so I replaced with X
					 lines[a][b]= 'x';
				}
			  
				trTableOmset += '<td>'+ lines[a][b] +'</td>'; //print
			}
    	trTableOmset += '</tr>' ;
		} else { //if a[3] was just blank
			currentRow = a + 2 ; //corresponding row of the excel spreadsheet
			trTableOmset += '<tr>'  ;     
			trTableOmset += '<td><b>'+ currentRow +'</b></td>';	
			trTableOmset += '<td> Row in CSV is header </td>'; //just add a message in that first cell
			for(var b=1; b<lines[0].length; b++){

			lines[a][b]= '-'; //and a "-" character for the rest of the row
          
        	trTableOmset += '<td>'+ lines[a][b] +'</td>';
			}
			
			
		}
 	}
	tbodyTableOmset.append(trTableOmset); 
	
	var donorField = 2 //One of the columns was my "donor" field. I need to check all donors in the CSV already exist, before importing.
	
	checkDonorCodesExist(lines, donorField)
   
	};
	
	function checkDonorCodesExist(lines, b) {
		Knack.showSpinner();
		var thisDonor;
		x = 0
			for (var a = 0; a<lines.length; a++){  //for each line
				thisDonor = lines[a][b] 			//this is the donor (b is column id passed to this function, not a counter now)

				if ($.inArray(thisDonor, listOfDonors[0],0) == -1 && thisDonor != "-"){  //if not already in list
					listOfDonors[0].push(thisDonor)										// add to list
					x++		//increment
				}
		
			}
			console.log("List of donors:")
			console.log(listOfDonors)
			console.log(x)
			
			$('#view_831').append('<br> <input type="button" value="Looks good, add these lines to project" id="myImportButton"/>')		 //add button
			$('#myImportButton').hide(); //hide it
	
			for (a = 0; a<listOfDonors[0].length; a++) {   //for each donor in list
				
				checkIfInList(listOfDonors[0][a], a)  //check if the donor is in the list... and add if not.
				
			}

			
			Knack.hideSpinner(); 									//hide the "loading" spinny thing
		

			 	

		function checkIfInList(donorCode, a){
				var A = {} //change A to object, so I can access this inside my AJAX
				A = a
				var donorCODE = {}; //use object to endure reference inside AJAX
				donorCODE = donorCode;
				// api 
				var api_url = 'https://api.knack.com/v1/scenes/scene_186/views/view_988/records' //Donor List
				   
				// preparing filters
				var filters = [
					{
					  field: 'field_142',
					  operator: 'is',
					  value: donorCode //search for this donor code
					}
				  ]
				  console.log("Checking for donor code " + donorCode)
					// add to URL
					api_url += '?filters=' + encodeURIComponent(JSON.stringify(filters));
				  
				$.ajax({
					url: api_url
				  , type: "GET"
				  , headers: {
						'X-Knack-Application-Id': '56fb8b0fad25a3be79b5054a'
						, "X-Knack-REST-API-KEY": 'knack'
					  , "Authorization": userToken
					}
				  , success: function(data) {
						console.log("Successful ajax call for donor code. Returned:")
						console.log(data)
						if (data.records.length == 0) { //if doesn't already exist
						var donorName = prompt("Donor code " + donorCODE + " is not in the database. Press OK to add now. Optionally, please enter the name of the donor here ('UNHCR', 'GIZ', etc).")
							if (donorName != null){ //if user didn't click cancel
								addDonorCode(donorName, donorCODE, A);
								Knack.showSpinner();
							}
						} else {
							listOfDonors[1][a] = data.records["0"].id //note the record ID of this donor
							console.log("List of donors:")
							console.log(listOfDonors);
							x--
							showButton(listOfDonors[0][a])
							
						}
					}
						, error: function (request, status, error) {
						handleAjaxError(request, status, error); //custom error function
						}
					})
			}
		
		
	}
	
function addDonorCode(donorName, donorCode,A) {
			var api_url = 'https://api.knack.com/v1/scenes/scene_391/views/view_989/records' //Donor create form
			userToken = Knack.getUserToken();	   
			var dataToPost = ""
					dataToPost = {
					field_142: donorCode,
					field_641: donorName
					};
				   
	$.ajax({
					url: api_url
				  , type: "POST"
				  , headers: {
						'X-Knack-Application-Id': '56fb8b0fad25a3be79b5054a'
						, "X-Knack-REST-API-KEY": 'knack'
					  , "Authorization": userToken
					}
				  , data: dataToPost									  
				  , success: function(data) {
						console.log("Successful ajax post for creating donor. Returned:")
						console.log(data)
						x-- //increment x
						showButton(data.record.field_142);
						listOfDonors[1][A] = data.record.id	//record the record ID from this connected donor object (in position [1,x])
						Knack.hideSpinner();
					}
					, error: function (request, status, error) {
												handleAjaxError(request, status, error);
						Knack.hideSpinner();
						}
					})
		
		
};
	
function showButton(donorCode){
	if (x==0){ //if all donors have been imported/dealt with (x is global var somewhere)
		$("#myImportButton").show() 
		updateStatusInfo("Checking for donor " + donorCode + ".")
		updateStatusInfo("Done checking donors, click button to continue.")
	} else {
		updateStatusInfo("Checking for " + donorCode + ".")
	}
}
		
// ==========================================
// Main import code
// ==========================================

	$("#view_831").on('click',"#myImportButton",function(){
				console.log("Starting import")
				Knack.showSpinner();	
				updateStatusInfo("Starting import");

		var allTextLines = fileText.split(/\r\n|\n/); //split again etc
		var headers = allTextLines[0].split(',');
		var lines = [];

		for (var i=1; i<allTextLines.length; i++) {
			var data = allTextLines[i].split(',');  //data = an individual row
		  console.log("Data row extracted:")

				var tarr = [];
				for (var j=0; j<headers.length; j++) {
					tarr.push(data[j]);
				}
				lines.push(tarr);
		}

	    addBudgetLine1(lines, 0);
	  
		
		
		//add data
		
		function addBudgetLine1(lines, a){
		console.log("Called addBudgetLine1 function. a = " + a)
		console.log ("Lines.length = " + lines.length)
		console.log(lines)
		if (a < lines.length){ //if not finished yet
			console.log("BL is:")
			console.log(lines[a][3]);
			console.log("Line length:")
			console.log(lines[a].length)
			if (lines[a][3]!="" && typeof lines[a][3] != 'undefined'){ //if budgetline code is not blank and line is not blank
			var message = "Importing line " + lines[a][3]
			updateStatusInfo(message) //update my box thing
				var indexOfDonorID = $.inArray(lines[a][2], listOfDonors[0],0)		//search listOfDonors to find record ID				
				var donorID = listOfDonors[1][indexOfDonorID]
				var budgetLineCode = lines[a][3].substr(lines[a][3].length - 3) //last 3 digits

				console.log(budgetLineCode)
					if ($.inArray(budgetLineCode,existingBudgetLines) == -1){ //if not already existing
						addBudgetLine2(budgetLineCode, lines[a][6],lines[a][10],donorID,a,lines);
					} else {
						updateStatusInfo(budgetLineCode + " is already imported. Skipping this row of the CSV file.")
						a++ //increment a
						addBudgetLine1(lines,a)
					}
						
			} else {
				if (typeof lines[a][3] != 'undefined') {
					console.log("Skipped line for a = " + a)
					a++ //increment a
					addBudgetLine1(lines,a)
				} else {
					console.log("Looks like end of file!")
					a++ //increment a
					addBudgetLine1(lines,a)
				}
				
			}
		} else {
			alert("Finished.")
			Knack.hideSpinner();
		}
				
		};

			
			//=====================
			//POST/add budget line function
			//=====================
			
			function addBudgetLine2(budgetLineCode, description, totalAmount, donorID,a,lines){ //last two parameters passed just to callback function1 later
				var api_url = 'https://api.knack.com/v1/scenes/scene_344/views/view_990/records' //"Add Budget Line" form
				var equivalentUSD = totalAmount / initialCurrencyConversionRate
				console.log("Total value (function addBudgetLine2):")
				console.log(totalAmount)
				console.log("Converstion rate:")
				console.log(initialCurrencyConversionRate)
				
				var dataToPost = ""
						dataToPost = {
						field_927: description,
						field_178: knackProjectID,
						field_654: totalAmount, //in donor currency
						field_179: donorID,
						field_82: budgetLineCode,
						field_656: equivalentUSD,
						field_958: currentProjectCurrencyID,
						};
						
					
					
						console.log("Equivalent USD")
						console.log(equivalentUSD)

						for (var k=0; k<scenarios.records.length; k++) {							
								if (Number(equivalentUSD) < Number(scenarios.records[k].field_899_raw)){ //find the scenario limit. If USD value is less than max value for the current scenario...
									dataToPost['field_657'] = scenarios.records[k].field_898; //set scenario field
									dataToPost['field_658'] = scenarios.records[k].field_900; //and an additional related field
									break;
								}	
	

							}
							console.log("Chosen scenario:")
							console.log(dataToPost['field_657'])
		$.ajax({
						url: api_url
					  , type: "POST"
					  , headers: {
							'X-Knack-Application-Id': '56fb8b0fad25a3be79b5054a'
							, "X-Knack-REST-API-KEY": 'knack'
						  , "Authorization": userToken
						  ,"Content-Type":"application/json"
						}
					  , data: JSON.stringify(dataToPost)									  
					  , success: function(data) {
							console.log("Successful added line:")
							console.log(data)
							a++ //increment a
							addBudgetLine1(lines,a) //and go back and repeat
						}
						, error: function (request, status, error) {
							handleAjaxError(request, status, error);
							Knack.hideSpinner();
							}
						})
			
			
		};
				
				
			
		
	});
});