#!/usr/bin/python

#
#
# http://help.seapine.com/testtrack/2016.1.0/ttsdk/Default.htm#SDK/Types/CTableColumn.htm

#import httplib
import http.client
import json
import xmltodict

#
# Configure TestTrack SPR database
#
soap_server = "testtrack.eng.trimble.com"
soap_port = 80
soap_cgi = "http://testtrack.eng.trimble.com/cgi-bin/ttsoapcgi.exe"
db_user = "cvs"
db_pass = "cvs"

encoding = "utf-8" # TTpro 2011 default

# Local errors
class MyError(Exception):
  def __init__(self, value):
    self.value = value
  def __str__(self):
    return repr(self.value)



#
# Sends raw XML SOAP request to global 'soap_server' & 'soap_port'
#
def send_soap( request_str ):
  request_str_enc = request_str.encode( encoding )
  #httpsvr = httplib.HTTPConnection( soap_server, soap_port )
  httpsvr = http.client.HTTPConnection( soap_server, soap_port )

  httpsvr.putrequest("POST", soap_cgi )
  httpsvr.putheader("Content-Type", 'text/xml' )
  httpsvr.putheader("Content-Length", str(len(request_str_enc)))
  httpsvr.endheaders()
  httpsvr.send( request_str_enc )
  response = httpsvr.getresponse().read()
  return response


#
# Sends TestTrack Pro login request.
#  global 'db_user', 'db_pass'
# Returns cookie needed for further commands.
#
def soap_login( db_name ):
  request_str = """
<SOAP-ENV:Envelope xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"><SOAP-ENV:Header></SOAP-ENV:Header><SOAP-ENV:Body xmlns:ns1="urn:testtrack-interface"><ns1:DatabaseLogon><dbname>%s</dbname><username>%s</username><password>%s</password></ns1:DatabaseLogon></SOAP-ENV:Body></SOAP-ENV:Envelope>
""" % (db_name, db_user, db_pass)
  resp = send_soap( request_str ).decode("utf-8")
  if resp.find("<Cookie>") > 0:
    cookie = resp[resp.find("<Cookie>")+8:resp.find("</Cookie>")]
  else:
    raise MyError("Logon Error",resp)
  return cookie


#
# Sends TestTrack Pro login request to global soap_server
#  'cookie' is obtained from soap_login()
#
def soap_logout( cookie ):
  request_str = """
<SOAP-ENV:Envelope xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"><SOAP-ENV:Header></SOAP-ENV:Header><SOAP-ENV:Body xmlns:ns1="urn:testtrack-interface"><ns1:DatabaseLogoff><cookie>%s</cookie></ns1:DatabaseLogoff></SOAP-ENV:Body></SOAP-ENV:Envelope>
""" % cookie
  resp = send_soap( request_str ).decode("utf-8")
  if resp.find("<result>0") > 0:
    print("Logout OK")
  else:
    print("Logout Failed...")
    # raise MyError("Logout Error",resp)


#
# Sends TestTrack Pro edit defect request to global soap_server
#  cookie: obtained from soap_login()
#  spr_num: string of SPR # to edit
#
def soap_get_table( cookie, filterStr):
  request_str = """
<SOAP-ENV:Envelope xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"><SOAP-ENV:Header></SOAP-ENV:Header><SOAP-ENV:Body xmlns:ns1="urn:testtrack-interface"><ns1:getRecordListForTable><cookie>%s</cookie><tablename>Defect</tablename><filtername>%s</filtername><columnlist>Record ID</columnlist></ns1:getRecordListForTable></SOAP-ENV:Body></SOAP-ENV:Envelope>
""" % (cookie,filterStr)

  resp = send_soap( request_str )
  return resp.decode( encoding )

def getSPRSummary( db_name, filter_name ):
  cookie = ""
  cookie = soap_login( db_name)

  dataBase = []
  o = xmltodict.parse(soap_get_table(cookie,filter_name))
  defectData = {}
  try:
    records = o['SOAP-ENV:Envelope']['SOAP-ENV:Body']['ttns:getRecordListForTableResponse']['recordlist']['records']['item']
    for j in range(len(records)):
      d = {}
      d['ID']      = records[j]['row']['item'][0]['value']
      d['summary'] = records[j]['row']['item'][1]['value']
      status = records[j]['row']['item'][5]['value']
      d['status']  = status
      
      if("open" in status.lower()):
        dataBase.append(d)
        #if(     (users['stinger'][j]['first'].lower() in status.lower())
        #      and (users['stinger'][j]['last'].lower() in status.lower())
        #      and ("open" in status.lower())):
  except:
    print("No filtered data")

  soap_logout( cookie )
  return(dataBase)

#
# Record SPR and comments to TestTrack database
#
def getSPRTable( db_name ):
  # Get the list of filters
  with open('filters.json', 'r') as f:
    data = json.load(f)

  filters  = []
  sheetNum = []
  for i in range(len(data['filter'])):
    filters.append(data['filter'][i]['TTFilter'])
    sheetNum.append(data['filter'][i]['GSheet'])
  
  with open('users.json', 'r') as f:
    users = json.load(f)
  numStingerUsers = len(users['stinger'])
  numAppUsers = len(users['app'])
  numDriverUsers = len(users['driver'])
  numHardwareUsers = len(users['hardware'])

  cookie = ""
  cookie = soap_login( db_name)

  dataBase = []
  for i in range(len(filters)):
    o = xmltodict.parse(soap_get_table(cookie,filters[i]))
    openNum = 0
    closedNum = 0
    fixedNum = 0
    defectData = {}
    assignedStingerDefects   = [0] * numStingerUsers
    assignedAppDefects       = [0] * numAppUsers
    assignedDriverDefects    = [0] * numDriverUsers
    assignedHardwareDefects  = [0] * numHardwareUsers
    try:
      records = o['SOAP-ENV:Envelope']['SOAP-ENV:Body']['ttns:getRecordListForTableResponse']['recordlist']['records']['item']
      defects = []
      for j in range(len(records)):
        d = {}
        d['ID']      = records[j]['row']['item'][0]['value']
        d['summary'] = records[j]['row']['item'][1]['value']
        status = records[j]['row']['item'][5]['value']
        d['status']  = status

        defects.append(d)
        if("open" in status.lower()):
          openNum = openNum + 1
        elif("closed" in status.lower()):
          closedNum = closedNum + 1
        elif("fixed" in status.lower()):
          fixedNum = fixedNum + 1
        
        for j in range(numStingerUsers):
          if(     (users['stinger'][j]['first'].lower() in status.lower())
              and (users['stinger'][j]['last'].lower() in status.lower())
              and ("open" in status.lower())):
            assignedStingerDefects[j] = assignedStingerDefects[j] + 1

        for j in range(numAppUsers):
          if(     (users['app'][j]['first'].lower() in status.lower())
              and (users['app'][j]['last'].lower() in status.lower())
              and ("open" in status.lower())):
            assignedAppDefects[j] = assignedAppDefects[j] + 1
        
        for j in range(numDriverUsers):
          if(     (users['driver'][j]['first'].lower() in status.lower())
              and (users['driver'][j]['last'].lower() in status.lower())
              and ("open" in status.lower())):
            assignedDriverDefects[j] = assignedDriverDefects[j] + 1
        
        for j in range(numHardwareUsers):
          if(     (users['hardware'][j]['first'].lower() in status.lower())
              and (users['hardware'][j]['last'].lower() in status.lower())
              and ("open" in status.lower())):
            assignedHardwareDefects[j] = assignedHardwareDefects[j] + 1

      print(filters[i],openNum,fixedNum,closedNum)
      defectData['list']   = defects
    except:
      print("No data for filter = %s" % filters[i])

    stingerTeam = []
    for j in range(numStingerUsers):
      d = {}
      d['initials'] = users['stinger'][j]['initials']
      d['sprs']     = assignedStingerDefects[j]
      stingerTeam.append(d)

    appTeam = []
    for j in range(numAppUsers):
      d = {}
      d['initials'] = users['app'][j]['initials']
      d['sprs']     = assignedAppDefects[j]
      appTeam.append(d)

    driverTeam = []
    for j in range(numDriverUsers):
      d = {}
      d['initials'] = users['driver'][j]['initials']
      d['sprs']     = assignedDriverDefects[j]
      driverTeam.append(d)
    
    hardwareTeam = []
    for j in range(numHardwareUsers):
      d = {}
      d['initials'] = users['hardware'][j]['initials']
      d['sprs']     = assignedHardwareDefects[j]
      hardwareTeam.append(d)

    defectData['filter'] = filters[i]
    defectData['GSheet'] = sheetNum[i]
    defectData['open']   = openNum
    defectData['closed'] = closedNum
    defectData['fixed']  = fixedNum
    defectData['stingerTeam']  = stingerTeam
    defectData['appTeam']  = appTeam
    defectData['driverTeam']  = driverTeam
    defectData['hardwareTeam']  = hardwareTeam
    dataBase.append(defectData)

  soap_logout( cookie )
  return(dataBase)


if(__name__ == "__main__"):
  dataBase = getSPRTable('Emerald')
  jsonStr = json.dumps(dataBase)
  jsonOut = json.loads(jsonStr)
  print(json.dumps(jsonOut,indent=4,sort_keys=True))