#!/bin/sh
#processing script for member files
#member files contain full name, netID
#usage: awk -f cap_parser < *name-netID-file* > *loader.sql*

{	
	if(index($0,"'") >0)
		gsub(/'/,"\\'",$0) 
	if(NF < 4) { FN[NR] = ""; MI[NR] = ""; LN[NR] = ""; netID[NR] = $(NF-1); year[NR] = "" }
	else if(NF == 4) {
		FN[NR] = $1
		MI[NR] = ""
		LN[NR] = $2
		netID[NR] = $(NF-1)
		year[NR] = $NF
	}
	else if(NF == 5) {
		FN[NR] = $1
		MI[NR] = substr($2, 1, 1)
		LN[NR] = $3
		netID[NR] = $(NF-1)
		year[NR] = $NF
	}
	else {
		FN[NR] = $1
		MI[NR] = substr($2, 1, 1)
		LN[NR] = $3 " " $4
		netID[NR] = $(NF-1)
		year[NR] = $NF
	}	
}
END {
	print "INSERT INTO Members(LNAME, FNAME, MI, NETID, CLUB, YEAR) VALUES"
	for(i=1;i<NR;i++) {
		#end=index(email[i], "@")
		#netID[i]=substr(email[i], 1, end-1)
		printf("('%s', '%s', '%s', '%s', 'Cap', %d),\n", LN[i], FN[i], MI[i], netID[i], year[i]);
	}
	#end=index(email[NR], "@")
	#netID[NR]=substr(email[NR], 1, end-1)
	printf("('%s', '%s', '%s', '%s', 'Cap', %d);", LN[NR], FN[NR], MI[NR], netID[NR], year[i]);
}