#!/bin/python
import argparse
from openpyxl import load_workbook
import datetime

#
# Converts a spreadsheet (.xlsx) to a .substitutions file for CAEN SYx527 mainframes.
#

# The spreadsheet is like this
#  | | Crate No | Crate Name | Crate Type | Slot | Chan | Sys | Detector | Element        | comment ... if required
#
#  |S|    00    | HVFTOF1    |    4527    |  08  |  00  | HV  |    PCAL  | SEC1_U_E01     | 
#  |#|    00    | HVFTOF1    |    4527    |  08  |  01  | HV  |    PCAL  | SEC1_U_E02     | Broken. Moved to slot 12, chan 15 
#  |S|    00    | HVFTOF1    |    4527    |  08  |  02  | HV  |    PCAL  | SEC1_U_E03     | 
#  ...
#  ...
#
# Note: The 1st cell in the line should be an S, otherwise the line is ignored. Use # in 1st cell to comment out the line.
#
#
# The resulting .substitutions file is something like this:
#file "db/caenhv.db" {
# pattern { Cr,    CrName,     CrType,  Sl,  Ch,   Sys,  Det,    Element,      CScode,   pwonoff, v0set,  i0set,   trip,    rampup,  rampdn, svmax,   enable}
#      	  {"00",   "HVFTOF1",  "4527", "08", "00", "HV", "PCAL", "SEC1_U_E01", "#C2048", "S2816", "S512", "S1280", "S2560", "S1024", "S768", "S3328", "S256"}
#      	  {"00",   "HVFTOF1",  "4527", "08", "02", "HV", "PCAL", "SEC1_U_E02", "#C2048", "S2817", "S513", "S1281", "S2561", "S1025", "S769", "S3329", "S257"}
#         ...
#         ...


# Here are the command codes from the CAEN manual (taken from old C++ code).

#command_codes = {
#    "NoSupport": 0x00,    # No device support for PV       	     
#    "G_Valid":   0x80,    # Get HV validity                	     
#    "G_HV":      0x81,    # Get HV on/off                  	     
#    "G_Alarm":   0x82,    # Get Chassis alarm status       	     
#    "S_CE":      0x01,    # Set enable/disable             	     
#    "S_DV":      0x02,    # Set demand voltage             	     
#    "S_RDN":     0x03,    # Set ramp down                  	     
#    "S_RUP":     0x04,    # Set ramp up                    	     
#    "S_TC":      0x05,    # Set trip current               	     
#    "S_MVDZ":    0x06,    # Set measured voltage dead-zone 	     
#    "S_MCDZ":    0x07,    # Set measured current dead-zone 	     
#    "S_HV":      0x08,    # Set HV on/off                  	     
#    "S_SOT":     0x09,    # Set samples over threshold     	     
#    # it is actually the time with high current before trip occur
#    "S_PRD":     0x0A,   # Set post ramp delay                  
#    #							     
#    "S_CHHV":    0x0B,    # Set CHANNEL HV on/off                
#    #							     
#    "S_BDHV":    0x0C,    # Set BOARD HV on/off                  
#    "S_VMAX":    0x0D,    # Set MAX CHANNEL VOLATAGE              
#    "S_UVVT":    0x10,    # Under Voltage                  
#    "S_OVVT":    0x11,    # Over Voltage              
#    }

#.... from the old .C code
#command_macros[]= {"pwonoff", "v0set", "i0set", "trip", "rampup", "rampdn","svmax", "enable", NULL};
#command_codes[] = { S_CHHV,    S_DV,    S_TC,    S_PRD,  S_RUP,    S_RDN,   S_VMAX,  S_CE,      -1}; //see defs above
comms            = [   0x0B,    0x02,    0x05,     0x0A,   0x04,     0x03,     0x0D,  0x01]

#lv_command_codes[] = { S_CHHV,    S_DV,    S_TC,    S_PRD,  S_RUP,    S_RDN,   S_VMAX,  S_UVVT, S_OVVT     -1}; //see defs above
lvcomms             = [   0x0B,    0x02,    0x05,     0x0A,   0x04,     0x03,     0x0D,  0x10,   0x11]

hvrows = [];
lvrows=[];

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("xlsxfile",    help="xlsx file")          # Add args and opts
    args = parser.parse_args()                                    # Parse them

    xl2sub(args.xlsxfile)                                         # call function


def xl2sub(xlsxfile):
    book      = load_workbook(filename = xlsxfile)                    # open the book
    sheet     = book.active                                           # get the only sheet

    today     = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") #get the data

    sfilename = xlsxfile.replace('.xlsx','')+'.substitutions'         #remove any .xlsx and tag on .substitutions
    sfile     = open(sfilename,   "w")                                #open for writing

     
    #print the preamble
    print >> sfile, '#'
    print >> sfile, '#This file was autogenerated by the command \"'+__file__+' '+xlsxfile+'\" on: '+today
    print >> sfile, '#To change the HV channels, edit '+xlsxfile+' and rerun the commnd, remake the database and restart the ioc'
    print >> sfile, '#'

    #The spreadsheet cols are:
    #RowType Crate No	Crate Name  	Crate Type	 Slot	Chan	Sys	Detector	Element
    #S	     0	        HVFTOF1	        4527	         8	0	HV	PCAL	        SEC1_U_E01
    #....
    
    #The sub file cols(macro names) are: 
    #Cr,    CrName,  CrType, Sl,     Ch,    Sys,     Det,    Element, CScode, pwonoff, v0set,  i0set,   trip,    rampup,  rampdn, svmax,   enable
    #The 1st 8 come from the spreadsheet. The resta are calculated
    for row in sheet.iter_rows(min_row=sheet.min_row, min_col=sheet.min_column, max_row=sheet.max_row, max_col=sheet.max_column):
        if row[0].value=="S":
            
            #print row
            #print >> sfile, '\t\t{',
            rowstring='\t\t{\"'+str(row[1].value).zfill(2)
            for cell in range(2, 9):
                rowstring+='\", \"'+str(row[cell].value).zfill(2)
            #rowstring+='\", \"#C'+str(row[1].value + row[4].value*256)+'\"'      #crate+(slot<<8)
            print row
            rowstring+='\", \"#C'+format(row[1].value + row[4].value*256,'#06x')+'\"'      #crate+(slot<<8)
            if (row[6].value == 'HV'):
                for cmd in range(0,8):
                    rowstring+=", \"S"+format(row[5].value + 256*comms[cmd],'#06x')+"\""   #chan+(cmd<<8)
                    #print >> sfile, rowstring+'}'
                hvrows.append(rowstring+'}')
            else: #assume LV
                for cmd in range(0,9):
                    rowstring+=", \"S"+format(row[5].value + 256*lvcomms[cmd],'#06x')+"\""   #chan+(cmd<<8)
                    #print >> sfile, rowstring+'}'
                lvrows.append(rowstring+'}')
            
    if len(hvrows)>0:
        print >> sfile, 'file \"db/caenhv.db\" {'
        print >> sfile, '\tpattern { Cr,   CrName,    CrType, Sl,   Ch,   Sys,  Det,    Element,       CScode, pwonoff, v0set,  i0set,   trip,    rampup,  rampdn, svmax,   enable}'
        for i in range(0,len(hvrows)):
            print  >> sfile,hvrows[i];
        print >> sfile, '}'
        print >> sfile, ''
            
    if len(lvrows)>0:
        print >> sfile, 'file \"db/caenlv.db\" {'
        print >> sfile, '\tpattern { Cr,   CrName,    CrType, Sl,   Ch,   Sys,  Det,    Element,       CScode, pwonoff, v0set,  i0set,   trip,    rampup,  rampdn, unvt,   ovvt}'
        for i in range(0,len(lvrows)):
            print  >> sfile,lvrows[i];
        print >> sfile, '}'
        print >> sfile, ''

    #make an ioc startup file template on the basis of the last line in the .xlsx file
    iocfilename = 'st.cmd.'+xlsxfile.replace('.xlsx','')                #remove any .xlsx and tag on .substitutions
    iocfile     = open(iocfilename,   "w")                                #open for writing
    
    print >> iocfile, '#!../../bin/linux-x86/ioccaen'
    print >> iocfile, '#'
    print >> iocfile, '#This file was autogenerated by the command \"'+__file__+' '+xlsxfile+'\" on: '+today
    print >> iocfile, '#Its a skeleton ioc startup file. You probably need to customize it'
    print >> iocfile, ''
    print >> iocfile, '#Crate name: '+row[2].value
    print >> iocfile, ''
    print >> iocfile, '< envPaths'
    print >> iocfile, 'epicsEnvSet(\"IOC\",\"ioccaenhv_'+row[2].value+'\")'
    print >> iocfile, ''
    print >> iocfile, 'cd ${TOP}'
    print >> iocfile, ''
    print >> iocfile, '## Register all support components'
    print >> iocfile, 'dbLoadDatabase("dbd/ioccaen.dbd")'
    print >> iocfile, 'ioccaen_registerRecordDeviceDriver(pdbbase)'
    print >> iocfile, '# call to run sy1527Init()'
    print >> iocfile, 'Init_CAEN()'
    print >> iocfile, ''
    print >> iocfile, '# Start_CAEN - call to run sy1527Start(), sy1527GetMap(), sy1527PrintMap()'
    print >> iocfile, '# Start_CAEN(crNumber, ipaddr or dnsname)'
    print >> iocfile, 'Start_CAEN('+str(row[1].value)+',\"'+row[2].value+'\")'
    print >> iocfile, ''
    print >> iocfile, '## Load record instances'
    print >> iocfile, ''
    print >> iocfile, 'dbLoadRecords(\"$(DEVIOCSTATS)/db/iocAdminSoft.db\", \"IOC=$(IOC)\")'
    print >> iocfile, 'dbLoadRecords(\"db/save_restoreStatus.db\",\"P=${IOC}:\")'
    print >> iocfile, ''
    print >> iocfile, 'dbLoadRecords(\"db/caenchassis.db\",\"CrName='+row[2].value+',CScode=#C'+str(row[1].value)+'\")'
    print >> iocfile, ''
    print >> iocfile, 'dbLoadTemplate(\"db/'+sfilename+'\")'
    print >> iocfile, ''
    print >> iocfile, '# Load additional records and templates here'
    print >> iocfile, ''
    print >> iocfile, ''
    print >> iocfile, ''
    print >> iocfile, ''
    print >> iocfile, 'cd ${TOP}/iocBoot/${IOC}'
    print >> iocfile, ''
    print >> iocfile, '< save_restore.cmd'
    print >> iocfile, 'iocInit()'
        
if __name__ == "__main__": main()  # call main comes at the end: a quirk of python
