#!/usr/local/bin/python3.3
import cgi
import cgitb
import pymysql
import datetime
import DAPRfunctions as dapr

#dapr.loadConfiguration()

cookie = dapr.get_cookies()
db = dapr.DBConnection()

cgitb.enable()

if cookie:
    print(cookie)
    fname = cookie["fname"].value
    lname = cookie["lname"].value
    name = " ".join((fname, lname))
    admin = cookie["admin"].value
    uid = cookie["uid"].value
    uname = cookie["uname"].value
    
    
    form = cgi.FieldStorage()
    
    exp = form.getvalue("exname")
    sid2 = form.getvalue("stname")
    des1 = form.getvalue("description1")
    des2 = form.getvalue("description2")
    des3 = form.getvalue("description3")
    doe = form.getvalue("doe")
    
    eid_to_delete = form.getvalue("eid_to_delete")
    
    sid = form.getvalue('sid')
    
    qsname = "select sname from Study where sid = '%s'" % sid
    
    
    if exp == None:
        exp = ""
    if des1 == None:
        des1 = ""
    if des2 == None:
        des2 = ""
    if des3 == None:
        des3 = ""
        
    
    def addExp(exp, sid, des1, des2, des3, doe, uname, date):
        
        query1 = """INSERT INTO Experiment (title, description1, description2, description3,
        exp_date, creator, create_date) VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s")
        ;""" % (exp, des1, des2, des3, doe, uname, date)
        
        db.runInsert(query1)
        
        
        qgeteid = """select eid from Experiment where title = "%s" """ % exp
        (eid,) = db.runQueryFetchOne(qgeteid)
        
        query2 = """insert into StudyExperiment (sid, eid) values ("%s", "%s")""" % (sid, eid)
        db.runInsert(query2)
        
        
    def delExperiment(eid):
        query = """delete from Experiment where eid = ("%s");""" %(eid)
        db.runInsert(query)
                
    if len(exp) != 0 and sid2 != None:
        addExp(exp, sid2, des1, des2, des3, doe, uname, str(datetime.date.today()))
        print("Location: showexperiment.py")
    
    elif eid_to_delete != None:
        delExperiment(eid_to_delete)
        print("Location: showexperiment.py")
    
    else:
        print("""<p class="message" style="color:red ! important;"><b>Field left empty, please fill in all required fields</b></p>""")
        #print("Location: showexperiment.py")

    print("Content-type: text/html\n")
    dapr.phead(name, admin)
    print("""<div class="container">""")
    print("""
     <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js">
    </script>
    <script>
    $(document).ready(function() {
        $(document.getElementById("remove")).on('click', function() { 
            var c = window.confirm("Delete Project? This action CANNOT be undone. All studies, experiments, and BED files within this project WILL be deleted. Continue?");
            if(c == true) {
                var exp = $(".input:checked").parent().siblings().children().html();
                //console.log(exp);
                $.get( "delExperiment.py", {eid:exp})
                .done(function( data ) {
               // console.log( data );
                window.alert("Experiment was successfully deleted. Click OK to be directed to new list of Experiments.")
                location.href='showexperiment.py';
            });}})});
            
            </script>
    """)
    

    if admin == "1" and sid == None:
        print('<p class="lead">All experiments:</p>')
        q1 = """
        select eid, title, e.description1, e.description2, e.description3, 
        e.creator, exp_date, create_date, count(*)
        from Experiment e
        join ExperimentBEDfile using(eid)
        group by eid
        order by create_date desc, title asc;
        """  # get eid, ename, description1,2,3, creator, expdate, createdate, exp#. Admin = 1

        q2 = """
        select eid, title, e.description1, e.description2, e.description3, 
        e.creator, exp_date, create_date, 0
        from Experiment e
        left join ExperimentBEDfile using(eid)
        where bid is NULL
        order by create_date desc, title asc;
        """
        
    elif admin == "1" and sid:
        sname = db.runQuery(qsname)[0][0]
        print('<p class="lead">All experiments in study <b>%s</b>:</p>' % sname)
        q1 = """
        select eid, title, e.description1, e.description2, e.description3, 
        e.creator, exp_date, create_date, count(*)
        from Experiment e
        join ExperimentBEDfile using(eid)
        join StudyExperiment using(eid)
        where sid = "%s"
        group by eid
        order by create_date desc, title asc;
        """ % sid
        
        q2 = """
        select eid, title, e.description1, e.description2, e.description3, 
        e.creator, exp_date, create_date, 0
        from Experiment e
        join StudyExperiment using(eid)
        left join ExperimentBEDfile using(eid)
        where sid = "%s" and bid is NULL
        order by create_date desc, title asc;
        """ % sid
    
    elif admin == "0" and sid == None:
        print('<p class="lead">All experiments:</p>')
        q1 = """
        select eid, title, e.description1, e.description2, e.description3, 
        e.creator, exp_date, create_date, count(*)
        from Experiment e
        join ExperimentBEDfile using(eid)
        group by eid
        order by create_date desc, title asc;
        """ 
        
        q2 = """
        select eid, title, e.description1, e.description2, e.description3, 
        e.creator, exp_date, create_date, 0
        from Experiment e
        left join ExperimentBEDfile using(eid)
        where  bid is NULL
        order by create_date desc, title asc;
        """ 
        
    else:
        sname = db.runQuery(qsname)[0][0]
        print('<p class="lead">All experiments in study <b>%s</b>:</p>' % sname)
        q1 = """
        select eid, title, e.description1, e.description2, e.description3, 
        e.creator, exp_date, create_date, count(*)
        from Experiment e
        join ExperimentBEDfile using(eid)
        join StudyExperiment using(eid)
        where sid = "%s" 
        group by eid
        order by create_date desc, title asc;
        """ % (sid)
        
        q2 = """
        select eid, title, e.description1, e.description2, e.description3, 
        e.creator, exp_date, create_date, 0
        from Experiment e
        join StudyExperiment using(eid)
        left join ExperimentBEDfile using(eid)
        where sid = "%s" and bid is NULL
        order by create_date desc, title asc;
        """ % (sid)
        
    res1 = db.runQuery(q1)
    res2 = db.runQuery(q2)
    
    if len(res1) > 0 or len(res2) > 0:
        print("""
        <table id="example" class="display" cellspacing="0" width="100%">
          <thead>
                 <tr>
                     <th></th>
                     <th>ID</th>
                     <th>Experiment</th>
                     <th>Description1</th>
                     <th>Description2</th>
                     <th>Description3</th>
                     <th>Creator</th>
                     <th>Date of experiment</th>
                     <th>Create date</th>
                     <th># BED files</th>
                   </tr></thead><tbody>"""
                   )
        n1 = len(res1)
        n2 = len(res2)
        for i in range(n1 + n2):
            if i < n1 and n1 > 0:
                row = res1[i]
            else:
                row = res2[i-n1-1]
            print("<tr>")
            print("""<td><input name ="input" class="input" type="checkbox"/></td>""")
            for j in range(len(row)):
                if j == 0 or j == 1:
                    print("""<td><a href="./BEDfunctions.py?exp=%s">%s</a></td>""" % (str(row[0]),str(row[j])))
                else:
                    print("<td>%s</td>" % str(row[j]))
            print("</tr>")
        print("</tbody></table></br></br>")
        
        
    print("""
    <p class="lead">Add a new experiment:</p>
    
    <form class="form-horizontal" method="post" action="./showexperiment.py" enctype="multipart/form-data">
      <div class="form-group">
        <div class="col-xs-8">
          <label for="input-project" class="col-sm-2 control-label">Experiment:</label>
          <div class="col-sm-10">
            <input class="form-control" type="text" placeholder="Please enter experiment name" name="exname">
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-xs-8">
          <label for="selectstudy" class="col-sm-2 control-label">Study:</label>
          <div class="col-sm-10">
          <select class="form-control" name="stname">
            <option>-- Please select a study --</option>
      """)
    
    if admin == "1":
        getsts = """select sid, sname from Study"""
    else:
        getsts = """select sid, sname from Study"""
        #where creator = "%s" """ % uname
    
    stnames = db.runQuery(getsts)
    for row in stnames:
        print("""<option value="%s">%s</option>""" % (row[0], row[1]))
    
    print("""
          </select>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-xs-8">
          <label for="input-des1" class="col-sm-2 control-label">Description 1:</label>
          <div class="col-sm-10">
            <input class="form-control" type="text" name="description1">
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-xs-8">
          <label for="input-des2" class="col-sm-2 control-label">Description 2:</label>
          <div class="col-sm-10">
            <input class="form-control" type="text" name="description2">
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-xs-8">
          <label for="input-des3" class="col-sm-2 control-label">Description 3:</label>
          <div class="col-sm-10">
            <input class="form-control" type="text" name="description3">
          </div>
        </div>
      </div>
      <div class="form-group">
        <div class="col-xs-8">
            <label for="input-doe" class="col-sm-2 control-label">Date of Experiment:</label>
            <div class="col-sm-10">
            <input class="form-control" name="doe" type="text">
        </div>
    </div>
    </div>
    <div>
    </br>
    <input type="submit" value="Add" class="btn btn-lg btn-default">
    </div>
    </form>
    <br/>
    """)
    print("""
    <br/>
    <p class="lead">Delete an Experiment:</p>
    <p><b style="color:red ! important">Note: This CANNOT be undone and it will remove everything within the experiment.</b></p>
    <p><input type="button" value="Delete Experiment" class="btn btn-lg btn-default" id="remove"></p>
    <br/>
    </div>
    """)
    
    
    dapr.ptail()

else:
    print("Location: login.py")
    print("Content-type: text/html\n")