def index():
    return dict()
# List all the current records in the database
def list():
    highways = db().select(db.closures.ALL, orderby=db.closures.lognum)
    return dict(highways = highways)
# Show details of a single record
def show():
    # Retrieve the requested log entry from the database
    # Assumes the requested entry exists in the db (no error handling yet)
    hwy = db(db.closures.lognum == request.args(0)).select().first()
    return dict(hwy=hwy)
# Display a search form
def search():
    form = FORM(LABEL('ClosureID/Log:',_for='closureid'), INPUT(_name='closureid',_size='7'),INPUT(_name='lognum',_size='3'),XML('&nbsp;&nbsp;&nbsp;'), LABEL('Route',_for='route'), SELECT('','5','55','405','22',_name='route'), XML('&nbsp;&nbsp;&nbsp;'), LABEL('Direction',_for='direction'), SELECT('','N', 'S', 'N/S','E','W','E/W',_name='direction'), BR(),BR(),'Dates:',INPUT(_name='startdate'), '  to:', INPUT(_name='enddate'),'(MMDDYYYY)',BR(),BR(),INPUT(_type='submit'))
    if form.process().accepted:
        session.closureid = form.vars.closureid
        session.lognum = form.vars.lognum
        session.startdate = form.vars.startdate
        session.enddate = form.vars.enddate
        session.route = form.vars.route
        session.direction = form.vars.direction
        redirect(URL('results'))
    return dict(form=form)
# Show the item that was found in the search
def results():
    if (len(session.closureid) != 0):
        hwy = db(db.closures.closureid == session.closureid).select() 
        msg = "Closure ID = " + session.closureid
    elif (len(session.route) != 0):
        hwy = db(db.closures.route == session.route).select() 
        msg = "route = " + session.route
    else: 
        hwy = db(db.closures.startdate >= session.startdate).select() 
        msg = "Start Date >= " + session.startdate
    count = len(hwy)
    # Show the results in table format.  Get the radio call number from supervisor name lookup
    form = FORM(INPUT(_type='submit',_name='btn1',_value='Show History'),XML("  "),
                INPUT(_type='submit',_name='btn2',_value='Show Status Form'),
                TABLE(THEAD(TR(TH(''), TH('DTM',BR(),'Area'), TH('Closure ID/',BR(),'Log No.'),TH('Route & Dir',BR(),'Type of Closure'),TH('Start Date/',BR(),'End Date/',BR(),'Est. Delay'),TH('Facility'),TH('Limits'),TH('Work'),TH('TMP'),TH('Requestor/',BR(),'Radio Call No.'))),
                    [TR(INPUT(_type='checkbox', _name='cb1', _value=row.closureid),
                        TD(row.closureid[0]),TD(row.closureid,HR(),row.lognum), TD(row.route,' ',row.direction,HR(),row.closuretype), TD(row.startdate,HR(),row.enddate,HR(),row.estdelay), TD(row.facility),TD(row.startlocation,HR(),row.endlocation), TD(row.worktype), TD(row.tmpcozeep,BR(),row.tmpdetour), TD(row.supervisor,BR(),db(db.supervisors.name == row.supervisor).select().first().radiocallnum) ) for row in hwy],
                    _border='1', _cellpadding='5'), )
    if form.process().accepted:
        session.cb1 = form.vars.cb1
        session.fields = form.vars
        if (not form.vars.btn1):
            redirect(URL('statuslist'))
        else:
            redirect(URL('showhistory'))
    return dict(msg=msg, count=count, highways=hwy, form=form)

# Show the status box next to each search result
def statuslist():
    msg = DIV("Selected ID's: ") + XML(session.cb1) + DIV("Currently only one status can be updated from the list below.")
    closedItems = []
    if (session.cb1):
        if (type(session.cb1) is str):
            retrieved = db(db.closures.closureid == session.cb1).select().first()
            closedItems.append(retrieved) 
        else:
            for item in session.cb1:
                retrieved = db(db.closures.closureid == item).select().first()
                closedItems.append(retrieved) 

        form = FORM(BR(), 
                INPUT(_type='submit',_value="submit status form"),
                TABLE(THEAD(TR(TH('Closure ID/',BR(),'Log No.'),TH('Route & Dir',BR(),'Type of Closure'),TH('Start Date/',BR(),'End Date/',BR(),'Est. Delay'),TH('Requestor/',BR(),'Radio Call No.'),TH('Status'))),
                    [TR(TD(row.closureid,HR(),row.lognum),TD(row.route,' ',row.direction,HR(),row.closuretype),TD(row.startdate,HR(),row.enddate,HR(),row.estdelay),TD(row.supervisor,BR(),db(db.supervisors.name == row.supervisor).select().first().radiocallnum),TD(LABEL('1097'), INPUT(_type='checkbox', _name='statustype', _value='1097'+row.closureid),LABEL('1098'), INPUT(_type='checkbox', _name='statustype', _value='1098'+row.closureid),LABEL('1022'), INPUT(_type='checkbox', _name='statustype', _value='1022'+row.closureid),BR(), LABEL('Statuser:'),INPUT(_name='statuser',_size='9'))) for row in closedItems],
                    _border='1', _cellpadding='5'), )
        
    else:
        msg = "No items were selected.  Use the checkbox in the lefthand column."
        form = ""
        return dict(msg=msg,form=form)
    
    if form.process().accepted:
        #session.flash = 'Status submit acknowledgement appears here.'
        session.statustype = form.vars.statustype
        session.statuser = form.vars.statuser
        redirect(URL('statusAck'))
    return dict(msg=msg,form=form)

# Diagnostic page- show status update acknowledgement
def statusAck():
    if (session.statustype):
        if (type(session.statustype) is str):
            msg = "You submitted a status update for " + session.statustype[4:] + ": " + session.statustype[0:4] 
#           Perform the update on the database
            # row = db(db.closures.closureid == session.statustype[4:]).select().first()
            # Construct the name of the field to update 
            fieldname = "s"+session.statustype[0:4]+"user"
            db(db.closures.closureid == session.statustype[4:]).update(**{fieldname:session.statuser})
            import datetime 
            now = datetime.datetime.today()
            fieldname = "s"+session.statustype[0:4]+"date"
            db(db.closures.closureid == session.statustype[4:]).update(**{fieldname:now.strftime("%Y%m%d")})
            fieldname = "s"+session.statustype[0:4]+"time"
            db(db.closures.closureid == session.statustype[4:]).update(**{fieldname:now.strftime("%H%M")})
        else:
            msg = "error because only checking one box is allowed."
    else:
        msg = "Error no statustype checkbox was checked"
        
    return dict(msg=msg)
    
def showhistory():
    msg = DIV("Status histories will appear here for the ID's:")+ XML(session.cb1)
    closedItems = []
    page = BR()
    table = BR()
    if (session.cb1):
        if (type(session.cb1) is str):
            retrieved = db(db.closures.closureid == session.cb1).select().first()
            closedItems.append(retrieved) 
        else:
            for item in session.cb1:
                retrieved = db(db.closures.closureid == item).select().first()
                closedItems.append(retrieved) 
        for row in closedItems:
            page += XML("Status History for ") + XML(row.closureid)
            # For now we just display info about the 1097 event.
            # TODO: Check for a non-blank user field to determine if that status is completed.
            table = TABLE(THEAD(TR(TH('Event'),TH('User'),TH('Req Date'),TH('Status Date'))),
                    TR(TD('1097'),TD(row.s1097user),TD(formatDate(row.startdate),' ', formatTime(row.starttime)), TD(formatDate(row.s1097date),' ', formatTime(row.s1097time))),
                    _border='1', _cellpadding='5')
            page += table
    return dict(page=page,table=table)

def formatDate(msg):
    if (msg):
        return msg[4:6]+"/"+msg[6:8]+"/"+msg[0:4]
    else:
        return ""
def formatTime(msg):
    if (msg):
        return msg[0:2]+':'+msg[2:4]
    else:
        return ""
# Create a new record
def submit():
    # Don't name this function 'request' because it creates a name conflict with http.request
    #form = SQLFORM(db.closures)
    hwys = ['','1','5', '22', '55', '57', '73', '74', '91', '133', '142', '241', '261', '405', '605']  
    hournames = ['','00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23']
    closuretypes = ['', 'Lane', 'Full', 'Moving', 'One-Way Traffic', 'Alternating Lanes', 'Traffic Break']
    worktypes = ['', 'Connector', 'Conventional_Hwy', 'Mainline', 'Off Ramp', 'On Ramp', 'Rest Area', 'Surface Street']
    facilities = ['','AC Paving', 'Accident Investigation', 'Attenuator Repair', 'Blasting', 'Bridge Inspection', 'Bridge Work', 'Brush Fire', 'Chip Seal Operation', 'Concrete Pour', 'Core Drilling', 'Crack Seal Operation', 'Curb/Gutter/Sidewalk Work', 'Drainage Cleaning', 'Drainage Inspection', 'Drainage Work', 'Electrical Work', 'Emergency Work', 'Falsework Installation', 'Falsework Removal', 'Fence Work', 'Filming Activity', 'Fog Seal Operation', 'Graffiti Removal', 'Grinding and Paving', 'Grinding Operation', 'Guardrail Repair', 'Guardrail Work', 'Highway Construction', 'K-rail Installation', 'K-rail Removal', 'Landscape Work', 'Litter Removal', 'Maintenance Operation', 'Median Barrier Work', 'Miscellaneous Work', 'Pavement Marker Replacement', 'Pavement Repair', 'Pavement Work', 'Paving Operation', 'Pile Driving', 'Police Investigation', 'Roadway Excavation', 'Roadway Flooding', 'Sewer Work', 'Shoulder Work', 'Sign Work', 'Slab Replacement', 'Slide Removal', 'Slope Clearing', 'Soundwall Work', 'Special Event', 'Spray Operation', 'Striping Operation', 'Survey Work', 'Sweeping Operation', 'Traffic Signal Work', 'Tree Work', 'Utility Work', 'Vegetation Spraying']
    supervisors = ['']  # List of names for the dropdown box
    # Obtain all the supervisor names from the database
    for row in db().select(db.supervisors.ALL):
        supervisors.append(row.name)
    # Build the list of street locations and a hidden cross street lookup table    
    streets = ['']
    streetlookup = []
    for row in db().select(db.streets.ALL, orderby=db.streets.street):
        streets.append(row.street)
        streetlookup.append(row.route + ',' + row.street)

    form = FORM(
                LABEL('*Route',_for='route'), SELECT(hwys,_name='route', _id='routecombo', _onchange='routechanged()', requires=IS_LENGTH(minsize=1,error_message='route cannot be empty')), XML('&nbsp;&nbsp;&nbsp;'), 
           LABEL('*Direction',_for='direction'), SELECT('','N', 'S', 'N/S','E','W','E/W',_name='direction', requires=IS_LENGTH(minsize=1,error_message='direction cannot be empty')), XML('&nbsp;&nbsp;&nbsp;'), 
           LABEL('*Facility',_for='facility'), SELECT(facilities,_name='facility', requires=IS_LENGTH(minsize=1,error_message='facility cannot be empty')), BR(),BR(), 
           TABLE(TR(TD(),TD('*County'),TD('*Location')),TR(TD('BEGIN='),TD(SELECT('ORA',_name='startcounty')),TD(SELECT(streets,_name='startlocation',_id='startlocation')),
           TR(TD('END='),TD(SELECT('ORA',_name='endcounty')),TD(SELECT(streets,_name='endlocation',_id='endlocation'))))),BR(),
           'Date Range:',BR(),
           LABEL('From',_for='startdate'),INPUT(_name='startdate',_size='8'), XML('&nbsp;&nbsp;&nbsp;'), 
           LABEL('to:',_for='enddate'), INPUT(_name='enddate',_size='8'),'(MMDDYYYY)',XML('&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'), 
           LABEL('Times',_for='starttime'),SELECT(hournames,_name='starttime'), 
           LABEL(':',_for='starttimemin'),SELECT('','00','15','30','45','59',_name='starttimemin'), XML('&nbsp;&nbsp;'), 
           LABEL('to:',_for='endtime'),SELECT(hournames,_name='endtime'), 
           LABEL(':',_for='endtimemin'),SELECT('','00','15','30','45','59',_name='endtimemin'), BR(),BR(), 
           TABLE(TR(TD('*Type of Closure'),TD('*Type of Work'),TD('Estimated Delay'),TD('TMP Details')), 
                 TR(TD(SELECT(closuretypes,_name='closuretype',requires=IS_LENGTH(minsize=1,error_message='type of closure cannot be empty'))), 
                    TD(SELECT(worktypes,_name='worktype', requires=IS_LENGTH(minsize=1,error_message='type of work cannot be empty'))), 
                    TD(INPUT(_name='estdelay',_size='4'),'minutes'), 
                    TD(INPUT(_type='checkbox',_name='cozeep'),'CoZeep MaZeep/CHP',BR(), 
                       INPUT(_type='checkbox', _name='detour'),'Detour Available')), 
                 _width='100%' ),
    TABLE(TR(TD('*Supervisor'),TD('Field Rep')), 
                  TR(TD(SELECT(supervisors,_name='supervisor', requires=IS_LENGTH(minsize=1,error_message='supervisor cannot be empty'))),
                     TD(SELECT(supervisors,_name='fieldrep')))),
    TABLE(TR(TD('Meeting Place/CHP Contact'),TD('Reason for Closure'),TD('Additional Remarks / Detour ')), 
                  TR(TD(INPUT(_name='meeting')),TD(INPUT(_name='reason')),TD(INPUT(_name='remarks',_size='40'))) ), BR(), 
            INPUT(_type='submit',_value='Submit Closure'),XML('\n'),SELECT(streetlookup,_name='stlookup', _id='stlookup', _class='hideme')) 
    if form.process().accepted:
        newID = calcNextClosure(form.vars.route)
        # Insert the record into the database
        newrec = db.closures.insert(closureid=newID, lognum='1', route=form.vars.route, direction=form.vars.direction, facility=form.vars.facility, startcounty=form.vars.startcounty, endcounty=form.vars.endcounty, startlocation=form.vars.startlocation, endlocation=form.vars.endlocation, startdate=form.vars.startdate, enddate=form.vars.enddate, starttime=form.vars.starttime+form.vars.starttimemin, endtime=form.vars.endtime+form.vars.endtimemin, closuretype=form.vars.closuretype, worktype=form.vars.worktype, estdelay=form.vars.estdelay, tmpcozeep=form.vars.cozeep, tmpdetour=form.vars.detour, supervisor=form.vars.supervisor, fieldrep=form.vars.fieldrep,  )
        session.flash = 'New lane closure added: ' + newID
        redirect(URL('index.html'))
    return dict(form=form)

# Calculate the closure id to assign to the new closure
def calcNextClosure(routeNum):
    # Retrieve any existing closures on this route
    item = db(db.closures.closureid.startswith('T'+routeNum)).select().last() # Might need to sort these
    if (item != None):
        currID = item.closureid
        lastchar = currID[-1:]  # Get last character of ID
        lastchar = chr(ord(lastchar) + 1) # increment it to next character (need bounds check)
        newID = currID[:-1] + lastchar  # append char to ID
        return newID
    else:
        return 'T'+routeNum+'AA'  # For a non-existing route
