#!/usr/bin/python

# This script will convert a text file containing CSV format of the data
# from Fernando's spreadsheet "Crate & Chassis - Channel Assignments-RO.xlsx"
# and put it into an SQLite database. Since the original spreadsheet has
# many Sheets, it is easiest to convert it using the command-line tool
# xlsx2csv.py that can be downloaded from the internet. Run it like this:
#
#  ./xlsx2csv.py -s 0 Crate\ \&\ Chassis\ -\ Channel\ Assignments-RO.xlsx > tt.csv
#
# Then run this script on the tt.csv file:
#
#  ./tt_csv2db.py tt.csv
#
#
#  refer questions to:
#
#  David Lawrence x5567
#  davidl@jlab.org
#

import sqlite3 as lite
import sys
import os
import getpass
import socket
import itertools
import csv
import re

#from os.path import join, getsize, getatime, getmtime, getctime
#from os import stat
#from pwd import getpwuid
from time import gmtime, localtime, strftime

if len(sys.argv) != 2:
	print "You must supply a CSV file to convert!"
	sys.exit(0);

# Open CSV file and read it all into memory
csvfile = os.path.abspath(sys.argv[1])

# Connect to SQLite DB file, deleting any existing one first
if(os.path.exists('tt.db')):
	os.remove('tt.db')
con = lite.connect('tt.db')

with con:

	# Specify that next cursor should be "dictionary"
	# (i.e. python's hash map) so columns can be indexed
	# by name
	con.row_factory = lite.Row

	# Create Cursor
	cur = con.cursor()
	
	# Some programming notes:
	#
	# We want to use SQLite's built-in "rowid" feature for fast access. However,
	# we also want to use things like "crateid" and "moduleid" as primary
	# keys in the tables to make it easier and clearer when writing queries.
	# SQLite provides a feature by which to make a row an alias for the
	# rowid by making it a primary key of type INTEGER (which is *not* the
	# same as INT!). See the "ROWIDs and the INTEGER PRIMARY KEY" section at the
	# following URL:
	#
	# http://www.sqlite.org/lang_createtable.html
	#

	# Create tables to hold DAQ view info
	cur.execute("CREATE TABLE Crate(crateid INTEGER PRIMARY KEY, name TEXT, area TEXT, rack INT, location TEXT, SN TEXT, Function TEXT, rocid INT)")
	cur.execute("CREATE TABLE Module(moduleid INTEGER PRIMARY KEY, crateid INTEGER, slot INT, type TEXT, SN TEXT)")
	cur.execute("CREATE TABLE Channel(chanid INTEGER PRIMARY KEY, moduleid INTEGER, name TEXT, channel INT, system TEXT, col_name TEXT)")

	# Open and read in CSV file
	with open(csvfile, "r") as fo:
		block = []
		crate_blocks = []
		reader = csv.reader(fo)
		for row in reader:
			if row[0].startswith('--------'):
				if len(block) > 0:
					crate_blocks.append(block)
					block = []
			block.append(row)
					
	Ncrate_blocks = len(crate_blocks)
	print "Found %d crates" %(Ncrate_blocks)

	# Loop over crate blocks
	rocid = 1 # Need to coordinate this with DAQ settings
	for rows in crate_blocks:
		
		# Get crate name from second row
		crate_name = rows[1][1]
		#print "Found crate: %s" %(crate_name)
		vals = crate_name.split('-')
		area = vals[0];
		rack = vals[1];
		location = vals[2];

		# S/N and Function come from 3rd and 6th rows
		serialNum = rows[2][1]
		function = rows[5][1]
		setting = rows[5][2]  # has 1V or 2V for fADC250

		# Make entry int Crate table
		query = "INSERT INTO Crate (name,area,rack,location,SN,Function,rocid) VALUES('%s', '%s', %s, '%s', '%s', '%s', %s)" % (crate_name, area, rack, location,serialNum,function,rocid)
		cur.execute(query)
		crateid = cur.lastrowid
		rocid += 1

		# Make entry for each module. Module type in 8th row while S/N in 9th
		for slot in range(1, 22):
			moduleid = -1
			if slot < len(rows[7]):
				type = rows[7][slot]
				if type=='Fadc125': type = "fADC125" # Correct accidental mispelling in slot 19 in U2-5-BOT
				if len(type) > 0:
					SN = ''
					if slot < len(rows[8]):
						SN = rows[8][slot]
					query = "INSERT INTO Module (crateid,slot,type,SN) VALUES(%s, %s, '%s', '%s')" % (crateid,slot,type,SN)
					cur.execute(query)
					moduleid = cur.lastrowid

					# Loop over channels
					# The number of rows in each sheet differs depending on how many
					# channels are needed for the modules contained. The maximum seems
					# to be 31, though I know of some modules that have had 72. We loop
					# over 256 here just to be safe, exiting the loop once we find a row
					# that does not have an integer in the first cell.
					# One caveat is that some sheets have an extra blank row in the
					# middle so we have to skip those and continue looking for channels
					# on the following rows.
					for i in range(0,256):
						chan_str = rows[10+i][0]
						if len(chan_str) < 1: continue
						try: # stop as soon as we hit a row whose first cell isn't an integer
							channel = int(chan_str)
							chan_name = rows[10+i][slot].strip().strip('"')
							if len(chan_name) < 1 : continue

							query = "INSERT INTO Channel (moduleid,name,channel,system,col_name) VALUES(%s, '%s', %s, '','')" % (moduleid,chan_name,channel)
							cur.execute(query)
							chanid = cur.lastrowid
						except:
							break

					# For fADC125 modules, the spreadsheet only lists P2,P1,P0 for
					# channels. Since these are not integers, the above code doesn't
					# enter anything. This is actually good since we need to modify
					# the names of all the channels and explode them into 24 channels
					# each. Note that both the CDC and FDC cathodes are handled here.
					# The FDC cathodes are replaced further below in the "FDC Cathodes"
					# section in order to keep this section simpler.
					if type=='fADC125':
						for i in range(0,3):
							chan_str = rows[10+i][0]
							if len(chan_str) < 1: continue
							(conn_str,) = re.findall(r'[0-9]+', chan_str)
							connector = int(conn_str)
							if connector<0 or connector>2: break
							for j in range(0,24):
								channel = j + 24*connector
								chan_name = "%s-%d" % (rows[10+i][slot].strip(), j)
								query = "INSERT INTO Channel (moduleid,name,channel,system,col_name) VALUES(%s, '%s', %s,'','')" % (moduleid,chan_name,channel)
								cur.execute(query)
								chanid = cur.lastrowid

					# Similar to above, the FDC wires using F1TDCV3 modules only list
					# P0, P1 as channels. Each of these carries 48 signals. The names
					# of each of these 2 channels has the FDC package, chamber, and
					# wire numbers encoded in them. We go ahead and parse those here
					# to give them useful names.
					if type=='F1TDCV3':
						for i in range(0,2):
							chan_str = rows[10+i][0]
							if len(chan_str) < 1: continue
							(conn_str,) = re.findall(r'[0-9]+', chan_str)
							connector = int(conn_str)
							if connector<0 or connector>1: break
							chan_name = rows[10+i][slot].strip()
							match = re.match(r'FDC([0-9]),C([0-9]),S([0-9]+)\-([0-9]+)', chan_name)
							(package,chamber,wire_first,wire_last) = match.group(1,2,3,4)
							w1 = int(wire_first)
							w2 = int(wire_last)
							for wire in range(w1,w2+1):
								channel = wire-w1 + 48*connector
								chan_name = "FDC-P%s-C%s-W%d" % (package,chamber,wire)
								query = "INSERT INTO Channel (moduleid,name,channel,system,col_name) VALUES(%s, '%s', %s,'','')" % (moduleid,chan_name,channel)
								cur.execute(query)
								chanid = cur.lastrowid
											

	# ---------------------------------------------------------------------------------------
	# Fill in detector indexing
	#
	# At this point, all of the info from the spreadsheet is all entered into the Crate,
	# Module, and Channel tables. We now need to make the individual detector tables
	# that use detector indexing to link to the proper chanid in the Channel table above.
	# We do this by looping over the natural indexes and generating the channel name
	# and use that to get the chanid.
	# ---------------------------------------------------------------------------------------

	#=====================================================================
	# BCAL
	#
	# Modules are numbered 1-48 clockwise starting at 9 o'clock position
	# looking downstream.
	# Layers are numbered 1-4 going outwards from beamline
	# Sectors are numbered 1-4 in the clockwise direction for upstream,
	# counter-clockwise for downstream
	# (n.b. the wiring of lemo connectors should take care to make
	# sector 1,2,... go into fADC250 channel 0,1,... for both upstream
	# and downstream)
	#=====================================================================
	sys.stdout.write("Linking BCAL ....") ; sys.stdout.flush()
	cur.execute("CREATE TABLE BCAL(module INT, layer INT, sector INT, end TEXT, adc_chanid INTEGER, tdc_chanid INTEGER, disc_chanid INTEGER)")
	for module in range(1, 49):
		for layer in range(1, 5):
			for sector in range(1, 5):
				for end in ['U', 'D']:
					daq_chan = (sector-1) + (layer-1)*4;
					(adc_chanid, tdc_chanid, disc_chanid) = (-1, -1, -1)
					try:
						cur.execute("SELECT chanid FROM Channel WHERE name='BCAL-%d-%s-A-%d'" % (module,end,daq_chan))
						(adc_chanid,) = cur.fetchone()
						cur.execute("SELECT chanid FROM Channel WHERE name='BCAL-%d-%s-E-%d'" % (module,end,daq_chan))
						(tdc_chanid,) = cur.fetchone()
						cur.execute("SELECT chanid FROM Channel WHERE name='BCAL-%d-%s-T-%d'" % (module,end,daq_chan))
						(disc_chanid,) = cur.fetchone()
					except:
						pass # Do nothing here

					cur.execute("INSERT INTO BCAL VALUES(%d,%d,%d,'%s',%d,%d,%d)" %(module,layer,sector,end,adc_chanid, tdc_chanid, disc_chanid))
	cur.execute("SELECT count(*) FROM BCAL WHERE adc_chanid>=0") ; (Nbcal_adc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM BCAL WHERE tdc_chanid>=0") ; (Nbcal_tdc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM BCAL WHERE disc_chanid>=0") ; (Nbcal_disc,) = cur.fetchone()
	print " %d adc channels, %d tdc channels, and %d discriminator channels linked." % (Nbcal_adc, Nbcal_tdc, Nbcal_disc)

	#=====================================================================
	# TOF
	#
	# Historically, the TOF numbering scheme was odd in that the center
	# half-length bars were given numbers at the end of the range (41-44)
	# and the full-length bars were sequentially numbered 1-40. At the
	# time of this writing, the TOF design has changed to include 2 half-
	# width bars on either side of the beamline raising the total number
	# of tubes in a single plane from 42 to 44. Since the simulation
	# geometry needs to be updated, I've suggested that the numbering
	# scheme for the tubes be updated to go 1-44 in order of their physical
	# layout. We fill the database assuming this will be the case.
	#
	# Also, the DAQ naming scheme uses "North", "South", "Up" and "Down",
	# whereas the sim-recon scheme uses a "plane" or
	# "orientation" value for whether it is horizontal or vertical and then
	# "north" or "south" to specify the end. The values for "plane"
	# are 0=vertical  1=horizontal to correspond to the sim-recon convention
	#=====================================================================
	sys.stdout.write("Linking TOF ....") ; sys.stdout.flush()
	cur.execute("CREATE TABLE TOF(plane INT, bar INT, end TEXT, adc_chanid INTEGER, tdc_chanid INTEGER, disc_chanid INTEGER)")
	for plane in [0, 1]:
		for end in ['N', 'S']:
			daq_end = end
			if plane==0:
				if end=='N': daq_end='UP'
				else:        daq_end='DW'

			for bar in range(1,45):
				
				(adc_chanid, tdc_chanid, disc_chanid) = (-1, -1, -1)
				try:
					cur.execute("SELECT chanid FROM Channel WHERE name='TOF-%s-A-%d'" % (daq_end,bar))
					(adc_chanid,) = cur.fetchone()
					cur.execute("SELECT chanid FROM Channel WHERE name='TOF-%s-T-%d'" % (daq_end,bar))
					(tdc_chanid,) = cur.fetchone()
					cur.execute("SELECT chanid FROM Channel WHERE name='TOF-%s-%d'" % (daq_end,bar))
					(disc_chanid,) = cur.fetchone()
				except:
					pass # Do nothing here

				cur.execute("INSERT INTO TOF VALUES(%d,%d,'%s',%d,%d,%d)" %(plane,bar,daq_end,adc_chanid, tdc_chanid, disc_chanid))
	cur.execute("SELECT count(*) FROM TOF WHERE adc_chanid>=0") ; (Ntof_adc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM TOF WHERE tdc_chanid>=0") ; (Ntof_tdc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM TOF WHERE disc_chanid>=0") ; (Ntof_disc,) = cur.fetchone()
	print " %d adc channels, %d tdc channels, and %d discriminator channels linked." % (Ntof_adc, Ntof_tdc, Ntof_disc)

	#=====================================================================
	# TAGM
	#
	# The tagger microscope is outfitted for readout such that 95 of the
	# 100 columns have the 5 SiPMs summed in hardware so that there is a
	# single fADC channel for them. The other 5 columns (evenly spaced
	# throughout the microscope region) have all 5 SiPMs read out
	# individually so that they can be used to help tune the beam to be
	# centered along the plane defined by the central fibers. This makes
	# for 95 + (5*5) = 120 TAGM readout channels total.
	#=====================================================================
	sys.stdout.write("Linking TAGM ....") ; sys.stdout.flush()
	cur.execute("CREATE TABLE TAGM(col INT, row INT, adc_chanid INTEGER, tdc_chanid INTEGER, disc_chanid INTEGER)")
	daq_chan = 1
	for col in range (1,101):
		max_row = 1
		if ((col-1)%20)==0: max_row=5
		for row in range (1,max_row+1):
			(adc_chanid, tdc_chanid, disc_chanid) = (-1, -1, -1)
			try:
				cur.execute("SELECT chanid FROM Channel WHERE name='TAGM-A-%d'" % (daq_chan))
				(adc_chanid,) = cur.fetchone()
				cur.execute("SELECT chanid FROM Channel,Module WHERE name='TAGM-T-%d' AND Channel.moduleid=Module.moduleid AND type LIKE '%%TDC%%'" % (daq_chan))
				(tdc_chanid,) = cur.fetchone()
				cur.execute("SELECT chanid FROM Channel,Module WHERE name='TAGM-T-%d' AND Channel.moduleid=Module.moduleid AND type LIKE '%%DISC%%'" % (daq_chan))
				(disc_chanid,) = cur.fetchone()
			except:
				pass # Do nothing here

			if int(adc_chanid)>=0:
				cur.execute("INSERT INTO TAGM VALUES(%d,%d,%d,%d,%d)" %(col, row, adc_chanid, tdc_chanid, disc_chanid))
				daq_chan += 1

	cur.execute("SELECT count(*) FROM TAGM WHERE adc_chanid>=0") ; (Ntagm_adc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM TAGM WHERE tdc_chanid>=0") ; (Ntagm_tdc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM TAGM WHERE disc_chanid>=0") ; (Ntagm_disc,) = cur.fetchone()
	print " %d adc channels, %d tdc channels, and %d discriminator channels linked." % (Ntagm_adc, Ntagm_tdc, Ntagm_disc)

	#=====================================================================
	# TAGH
	#
	# The tagger hodoscope has 233 counters with a single index 1-233
	#=====================================================================
	sys.stdout.write("Linking TAGH ....") ; sys.stdout.flush()
	cur.execute("CREATE TABLE TAGH(id INT, adc_chanid INTEGER, tdc_chanid INTEGER, disc_chanid INTEGER)")
	for id in range (1,234):
		(adc_chanid, tdc_chanid, disc_chanid) = (-1, -1, -1)
		try:
			cur.execute("SELECT chanid FROM Channel WHERE name='TAGH-%d'" % (id))
			(adc_chanid,) = cur.fetchone()
			cur.execute("SELECT chanid FROM Channel,Module WHERE name='TAGH-T-%d' AND Channel.moduleid=Module.moduleid AND type LIKE '%%TDC%%'" % (id))
			(tdc_chanid,) = cur.fetchone()
			cur.execute("SELECT chanid FROM Channel,Module WHERE name='TAGH-T-%d' AND Channel.moduleid=Module.moduleid AND type LIKE '%%DISC%%'" % (id))
			(disc_chanid,) = cur.fetchone()
		except:
			pass # Do nothing here

		cur.execute("INSERT INTO TAGH VALUES(%d,%d,%d,%d)" %(id, adc_chanid, tdc_chanid, disc_chanid))

	cur.execute("SELECT count(*) FROM TAGH WHERE adc_chanid>=0") ; (Ntagh_adc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM TAGH WHERE tdc_chanid>=0") ; (Ntagh_tdc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM TAGH WHERE disc_chanid>=0") ; (Ntagh_disc,) = cur.fetchone()
	print " %d adc channels, %d tdc channels, and %d discriminator channels linked." % (Ntagh_adc, Ntagh_tdc, Ntagh_disc)

	#=====================================================================
	# FCAL
	#
	# The FCAL has the coordinates (in (col, row)) stored in the channel
	# name for each block. Only fADC250s are used so we use the already
	# entered channel names in the Channel table to get the col, row for
	# each. Since the sim-recon code is setup to use the row and col
	# values to index a 2-D array, they define them to run from 0-58.
	# Thus, we add 29 here bring shift them into that range.
	# n.b. Fernando built channels into the system for the 9 non-existent
	# detectors in the beam hole. These 9 extra channels are renamed
	# to "FCAL-unused" here to make it clearer and no corresponding
	# entry in the FCAL table is made.
	#=====================================================================
	sys.stdout.write("Linking FCAL ....") ; sys.stdout.flush()
	cur.execute("CREATE TABLE FCAL(row INT, col INT, adc_chanid INTEGER)")
	cur.execute("SELECT name,chanid FROM Channel WHERE name LIKE 'FCAL (%'")
	rows = cur.fetchall()
	for row in rows:
		chname = row[0]
		adc_chanid = row[1]
		(col, row) = re.findall(r'[\-0-9]+', chname)
		icol = int(col)
		irow = int(row)
		if (irow<=1 and irow>=-1 and icol<=1 and icol>=-1):
			# beamhole
			cur.execute("UPDATE Channel SET name='FCAL-unused' WHERE chanid=%d" % (adc_chanid))
		else:
			# regular block
			cur.execute("INSERT INTO FCAL VALUES(%d,%d,%d)" %(irow+29, icol+29, adc_chanid))

	cur.execute("SELECT count(*) FROM FCAL WHERE adc_chanid>=0") ; (Nfcal_adc,) = cur.fetchone()
	print " %d adc channels linked." % (Nfcal_adc)

	#=====================================================================
	# ST
	#
	# Start Counter has 30 counters numbered 1-30. Each counter
	# is called a "sector", presumably because they are all oriented
	# at different angles.
	#=====================================================================
	sys.stdout.write("Linking ST ....") ; sys.stdout.flush()
	cur.execute("CREATE TABLE ST(sector INT, adc_chanid INTEGER, tdc_chanid INTEGER, disc_chanid INTEGER)")
	for sector in range (1,31):
		(adc_chanid, tdc_chanid, disc_chanid) = (-1, -1, -1)
		try:
			cur.execute("SELECT chanid FROM Channel WHERE name='ST-A-%d'" % (sector))
			(adc_chanid,) = cur.fetchone()
			cur.execute("SELECT chanid FROM Channel,Module WHERE name='ST-T-%d' AND Channel.moduleid=Module.moduleid AND type LIKE '%%TDC%%'" % (sector))
			(tdc_chanid,) = cur.fetchone()
			cur.execute("SELECT chanid FROM Channel,Module WHERE name='ST-T-%d' AND Channel.moduleid=Module.moduleid AND type LIKE '%%DISC%%'" % (sector))
			(disc_chanid,) = cur.fetchone()
		except:
			pass # Do nothing here

		cur.execute("INSERT INTO ST VALUES(%d,%d,%d,%d)" %(sector, adc_chanid, tdc_chanid, disc_chanid))

	cur.execute("SELECT count(*) FROM ST WHERE adc_chanid>=0") ; (Nst_adc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM ST WHERE tdc_chanid>=0") ; (Nst_tdc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM ST WHERE disc_chanid>=0") ; (Nst_disc,) = cur.fetchone()
	print " %d adc channels, %d tdc channels, and %d discriminator channels linked." % (Nst_adc, Nst_tdc, Nst_disc)

	#=====================================================================
	# CDC
	#
	# The CDC numbering is complicated by the fact that a single connector
	# covers multiple layers and some odd number of wires. The spreadsheet
	# has only the multi-pin connector and the board name so we have to get
	# the actual pin assignments from somewhere else. When the spreadsheet
	# is read in, it does make 24 entries for each multi-pin connector
	# so the DAQ side will have names like "CDC-A1-12" where we added the
	# "-12" near the top of this script. The pin assignments file can be
	# found here:
	#  http://argus.phys.uregina.ca/cgi-bin/private/DocDB/ShowDocument?docid=2120
	#
	# It needs to be present in the current working directory when this
	# script is run.
	#=====================================================================
	sys.stdout.write("Linking CDC ....") ; sys.stdout.flush()
	cur.execute("CREATE TABLE CDC(ring INT, straw INT, adc_chanid INTEGER)")

	cdc_pins_file = "CDC_pins_spacedelim.txt"
	with open(cdc_pins_file, "r") as fo:
		for line in fo.readlines():
			vals = line.split()
			board = vals[1]
			for i in range(3,len(vals)):
				pin = i-3
				if vals[i]=="none": continue
				(straw,) = re.findall(r'[0-9]+', vals[i]) # Pull out number part for straw
				(ring_lab,) = re.findall(r'[A-Z]+', vals[i]) # Pull out letter part for ring
				if len(ring_lab)==1: # for single characters, use ASCII code to convert to number
					ring = ord(ring_lab) - ord('A') + 1
				if ring_lab == 'AA': ring=27 # double character labels must be handled specially
				if ring_lab == 'BB': ring=28

				try:
					cur.execute("SELECT chanid FROM Channel WHERE name='CDC-%s-%d'" % (board, pin))
					(adc_chanid,) = cur.fetchone()
					cur.execute("INSERT INTO CDC VALUES(%d,%s,%d)" %(ring, straw, adc_chanid))
				except:
					pass # Do nothing here


	cur.execute("SELECT count(*) FROM CDC WHERE adc_chanid>=0") ; (Ncdc_adc,) = cur.fetchone()
	print " %d adc channels linked." % (Ncdc_adc)

	#=====================================================================
	# PSC
	#
	# Pair Spectrometer Counters are 16 paddles on one side of beamline.
	# They have ADC and TDC readout
	#=====================================================================
	sys.stdout.write("Linking PSC ....") ; sys.stdout.flush()
	cur.execute("CREATE TABLE PSC(id INT, adc_chanid INTEGER, tdc_chanid INTEGER, disc_chanid INTEGER)")
	for id in range (1,17):
		(adc_chanid, tdc_chanid, disc_chanid) = (-1, -1, -1)
		try:
			cur.execute("SELECT chanid FROM Channel WHERE name='PSC-A-%d'" % (id))
			(adc_chanid,) = cur.fetchone()
			cur.execute("SELECT chanid FROM Channel,Module WHERE name='PSC-T-%d' AND Channel.moduleid=Module.moduleid AND type LIKE '%%TDC%%'" % (id))
			(tdc_chanid,) = cur.fetchone()
			cur.execute("SELECT chanid FROM Channel,Module WHERE name='PSC-T-%d' AND Channel.moduleid=Module.moduleid AND type LIKE '%%DISC%%'" % (id))
			(disc_chanid,) = cur.fetchone()
		except:
			pass # Do nothing here

		cur.execute("INSERT INTO PSC VALUES(%d,%d,%d,%d)" %(id, adc_chanid, tdc_chanid, disc_chanid))

	cur.execute("SELECT count(*) FROM PSC WHERE adc_chanid>=0") ; (Npsc_adc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM PSC WHERE tdc_chanid>=0") ; (Npsc_tdc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM PSC WHERE disc_chanid>=0") ; (Npsc_disc,) = cur.fetchone()
	print " %d adc channels, %d tdc channels, and %d discriminator channels linked." % (Npsc_adc, Npsc_tdc, Npsc_disc)

	#=====================================================================
	# PS
	#
	# Pair Spectrometer Hodoscope 145 scintillators on each side of beamline.
	# They have only ADCs. We follow Fernando's convention here of calling
	# one side "A" and the other side "B".
	#=====================================================================
	sys.stdout.write("Linking PS ....") ; sys.stdout.flush()
	cur.execute("CREATE TABLE PS(side TEXT, id INT, adc_chanid INTEGER)")
	for id in range (1,146):
		(adc_chanid, tdc_chanid, disc_chanid) = (-1, -1, -1)
		try:
			cur.execute("SELECT chanid FROM Channel WHERE name='PS-A-%d'" % (id))
			(adc_chanid_A,) = cur.fetchone()
			cur.execute("SELECT chanid FROM Channel WHERE name='PS-B-%d'" % (id))
			(adc_chanid_B,) = cur.fetchone()
		except:
			pass # Do nothing here

		cur.execute("INSERT INTO PS VALUES('%s',%d,%d)" %("A", id, adc_chanid_A))
		cur.execute("INSERT INTO PS VALUES('%s',%d,%d)" %("B", id, adc_chanid_B))

	cur.execute("SELECT count(*) FROM PS WHERE adc_chanid>=0") ; (Npsc_adc,) = cur.fetchone()
	print " %d adc channels linked." % (Npsc_adc)

	#=====================================================================
	# FDC Wires
	#
	# The FDC wires are read out by F1TDCV3 modules. The names in the
	# spreadsheet contain aggregate information representing the wires
	# on a given multi-pin connector. It is much easier to parse that
	# up top to generate names and entries for each wire. Here, we just
	# need to select those from the DB, parse the name for the package,
	# chamber, and wire number, and then make the entry in the FDC_Wires
	# table.
	#=====================================================================
	sys.stdout.write("Linking FDC_Wires ....") ; sys.stdout.flush()
	cur.execute("CREATE TABLE FDC_Wires(package INT, chamber INT, wire INT, tdc_chanid INTEGER, disc_chanid INTEGER)")
	cur.execute("SELECT name,chanid FROM Channel,Module WHERE name LIKE 'FDC-P%-C%-W%' AND Channel.moduleid=Module.moduleid AND type LIKE '%TDC%'")
	rows = cur.fetchall()
	for row in rows:
		chname = row[0]
		tdc_chanid = row[1]
		(package, chamber, wire) = re.findall(r'[0-9]+', chname)
		disc_chanid = -1
		try:
			cur.execute("SELECT chanid FROM Channel,Module WHERE name='%s' AND Channel.moduleid=Module.moduleid AND type LIKE '%%DISC%%'" % (chname))
			(disc_chanid,) = cur.fetchone()
		except:
			pass
		cur.execute("INSERT INTO FDC_Wires VALUES(%s,%s,%s,%d,%d)" %(package, chamber, wire, tdc_chanid, disc_chanid))

	cur.execute("SELECT count(*) FROM FDC_Wires WHERE tdc_chanid>=0") ; (Nfdcw_tdc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM FDC_Wires WHERE disc_chanid>=0") ; (Nfdcw_disc,) = cur.fetchone()
	print " %d tdc channels and %d discriminator channels linked." % (Nfdcw_tdc, Nfdcw_disc)

	#=====================================================================
	# FDC Cathodes
	#
	# The FDC Cathodes are read using fADC125s. In the initial parsing at
	# the top of this script, the fADC125 channels are each exploded
	# from the single multi-pin connector name in the spreadsheet to a
	# set of 24 names, differentiated by a "-X" appended to the end
	# with X being a number 0-23. Here, we need to make an entry in the
	# FDC_Cathodes table for each of these, but also rename the entry
	# in the Channels table to something more appropriate. The current
	# Channel name has the other index parameters encoded in it.
	#=====================================================================
	sys.stdout.write("Linking FDC_Cathodes ....") ; sys.stdout.flush()
	cur.execute("CREATE TABLE FDC_Cathodes(package INT, chamber INT, view TEXT, strip INT, strip_type TEXT, adc_chanid INTEGER)")
	cur.execute("SELECT name,chanid FROM Channel,Module WHERE name LIKE 'FDC%,C%,%-%-%' AND Channel.moduleid=Module.moduleid AND type LIKE '%ADC%'")
	rows = cur.fetchall()
	for row in rows:
		chname = row[0]
		adc_chanid = row[1]
		# This complicated match needs to look for strings like this:
		#   FDC2,C4,D97A-120-19
		# where the "A" could be either an "A" or a "B" and could either
		# appear or not appear after the first strip number and similarly
		# for the last strip number.
		match = re.match(r'FDC([0-9]),C([0-9]),([U,D])([0-9]+)([A,B])?\-([0-9]+)([A,B])?\-([0-9]+)', chname)
		(package, chamber, view, strip_first, strip_half_first, strip_last, strip_half_last, channel) = match.group(1,2,3,4,5,6,7,8)
		strip = int(channel) + int(strip_first)
		# If the first strip is a half strip on the "A" side, then the
		# first 12 strips are also half strips. Alternatively, if the
		# last strip is a half strip on the "A" side, then the last 12
		# strips are also half strips. If they are half strips on the
		# "B" side then all 24 strips are half strips on the "B" side.
		strip_type = "full"
		if strip_half_first is not None and int(channel)<=11: strip_type=strip_half_first
		if strip_half_last  is not None and int(channel)>=12: strip_type=strip_half_last
		cur.execute("INSERT INTO FDC_Cathodes VALUES(%s,%s,'%s',%d,'%s',%d)" %(package, chamber, view, strip, strip_type, adc_chanid))

	cur.execute("SELECT count(*) FROM FDC_Cathodes WHERE adc_chanid>=0") ; (Nfdcc_adc,) = cur.fetchone()
	print " %d adc channels linked." % (Nfdcc_adc)
	

	# ---------------------------------------------------------------------------------------
	# At this point we need to update all entries in the Channel table to
	# fill in the "system" and "col_name" columns. We set this to the table
	# name that has the chanid assigned
	# ---------------------------------------------------------------------------------------

	# Get list of all tables
	cur.execute("SELECT name FROM sqlite_master")
	rows = cur.fetchall()
	for row in rows:
		tablename = row["name"]
		if tablename == "Crate": continue
		if tablename == "Module": continue
		if tablename == "Channel": continue

		# Get column names by selecting just first row
		cur.execute("SELECT * FROM %s LIMIT 1" % (tablename))
		colnames = cur.fetchone().keys()
		chan_cols = []
		for colname in colnames:
			if "_chanid" in colname: chan_cols.append(colname)

		# If no rows in table or no columns with names ending in "_chanid" found, continue
		if len(chan_cols)==0: continue
		
		print "Updating Channel table for system: %s ..." % (tablename)
		cur.execute("SELECT * FROM %s" % (tablename))
		myrows = cur.fetchall()
		for myrow in myrows:
			for col in chan_cols:
				query = "UPDATE Channel SET system='%s',col_name='%s' WHERE chanid=%d" % (tablename,col,myrow[col])
				cur.execute(query)


	print "Done."