#!/usr/bin/env python
# -*- coding: UTF-8 -*-
#Code to import soil data from University of Leuven
#Data is contained in excel sheet, this script should also be used
#as example of excel access and reading

#http://epydoc.sourceforge.net/fields.html

### GLOBAL VARIABLES ####
locFile = '/home/mende012/Profilesmerged.xlsx'
#locFile = '/home/mende012/Profilesmerged.xlsx'
#editorProfile ='doetterl'
editorProfile ='isric-hannes'
shared= True
#outputDB = "dbname = GSM4-20130208 user = postgres"  # --> clean DB
#outputDB = "dbname = GSM4_NoLeuven user = postgres"
testing = True
outputDB = "host = 81.169.159.7 dbname = GSM3 user = postgres password = JDo#711" 
#########################
import xlrd #http://www.python-excel.org/ (location of package that needs to be used)
import os,sys

sys.path.append('../../') #relative path to WOSISAPI from inputDS/CanSIS
from WOSISAPI import *

#/home/mende012/workspace/WOSIS/WOSIS
import datetime
from UI import *
import sys
#import sys
#########   CONN   #############

#oconn = psycopg2.connect(outputDB)
#ocursor = oconn.cursor()


def getHeader(sheetObj):
    '''Function that fetches the header. It assumes that first line is header'''
    headerCol={}
    for cols in range(sheetObj.ncols):
        headerCol[cols]=sheetObj.cell_value(0,cols)
        #headerCol.append(sheetObj.cell_value(0,cols))
    return headerCol
def getLayerRow(sheetObj,profileID):
    #this is mainly for the profile depth explict. We need to get the rows associated with a profile
    returnList=[]
    for rows in range(sheetObj.nrows):
        cellVal=sheetObj.cell_value(rows,0)
        if cellVal==profileID:
            returnList.append(rows)
    return returnList

def main():
    
    wb = xlrd.open_workbook(locFile)
    profileData = wb.sheet_by_index(0)
    profileDepthSheet = wb.sheet_by_index(1)
    headerDic = getHeader(profileData)
    #WOSIS
    wosisDB = WOSIS(outputDB,testing=testing)

    #GETTIND HEADER
    attrIDs={}
    noAttr=[]
    for item in headerDic.values():
        #print item
        header,results = wosisDB.searchAttribute(item)
        #ocursor.execute(""" SELECT * FROM "Attribute"."Attribute" WHERE "Name" ILIKE %s """ , (item,))
        #results=ocursor.fetchall()        
        if results is None:
            noAttr.append(item)
        else:
            attributeId=[]
            for result in results:
                #listValue=[]
                #attrIDs["item"] = listValue
                 attributeId.append(result[0])
            attrIDs[item]=attributeId
    
    #UNCOMMENT WHEN BEING IMPLEMENTED
    #userInterface = UserInterface(wosisDB,attrIDs,"Attribute","Attribute")
    #tmp=userInterface.run()


    #Searching for Profile attributes.
 
    headerProfDic = getHeader(profileDepthSheet)

    #{0: u'ID', 1: u'Depth', 2: u'Texture', 3: u'Inorganic C %', 4: u'organic C %', 5: u'Bulk density g/cm-3', 6: u'Stones %'}
    
    #NOTE: Inorganic C needs to link to propper unit
    attrIDProfile=('ID','Texture','Inorganic C %','organic C %','Bulk density g/cm-3' ,'Stones %')
    

    #NonDB parameters
    #[u'Country', u'Date', u'Position', u'Age of landsurface', u'ID', u'AP_depth']
    #noAttr=[u'Country', u'Date', u'Age of landsurface', u'ID', u'AP_depth']
    attrIDs={
             u'Stones %': [49],
             u'Bulk density g/cm-3': [80],
             u'organic C %': [256],
             u'Inorganic C %': [431],
             u'Texture': [185],
             u'Author': [4], 
             u'Land use': [22], 
             u'Date':[3],
             u'Position':[9],
             u'Parent material': ['254'], # BUG need to be int
             u'Surface cover of rock outcrops': [28], 
             u'Cover of salt at the surface': [41], 
             u'Slope orientation': [21], 
             u'Location': [5], 
             u'Latitude': [123], 
             u'Surface crust thickness': [36], 
             u'Vegetation': [25], 
             u'ELEVATION': [6], 
             u'Period of activity of erosion': [35], 
             u'Slope gradient class': [120], 
             u'Slope form': [18], 
             u'Map sheet number': [7], 
             u'Soil profile description status': [2], 
             u'Longitude': [124], 
             u'Human Influence': [24], 
             u'Major landform': ['8'], #BUG: need to be int
             u'Surface cover of coarse fragments': [30], 
             u'Slope gradient %': [120], 
             u'Bleached sand surface cover': [43], 
             u'Crop': [23], 
             u'Age of landsurface':[27],
             u'Main erosion category': [32],
             u'ID': [306],
             u'AP_depth':[9999]
             }

    #### SEARCH VALUE DESCRIPTOR FOR PROFILE ###########
    valDesDic={}
    for k,v in  attrIDs.items():
        _,valueDes=wosisDB.searchValueDescriptor(v[0])
        try:
            if len(valueDes)>1:
                print "Multiple entrances for value descriptor: %s, %s"  % (k,str(valueDes))
            valDesDic[k] = [valueDes[0][0]]
                
        except:
            valDesDic[k] = None
    #############  END INSERT  #############
    
    ######### SEACH VALUE DESCRIPTOR FOR LAYERS ##############
    
    layerDesDic={}
    for item in  attrIDProfile:
        _,valueDes=wosisDB.searchValueDescriptor(attrIDs[item][0])
        try:
            if len(valueDes)>1:
                print "Multiple entrances for value descriptor: %s, %s"  % (item,str(valueDes))
            layerDesDic[item] = [valueDes[0][0]]
                
        except:
            layerDesDic[item] = None
    ########### END INSERT ####################
    
    
    
    #Starting to loop using header, building a dictionary for Profile.Profile: (Country,Location,)
    ## GET GENERAL DATA #####
    #ocursor.execute(""" SELECT "PublicationId" FROM "Reference"."Publication" WHERE "Title"='Africa Soil Profiles Database version 1.0' """)
    #publicationId = ocursor.fetchone()[0]
    #ocursor.execute(""" SELECT "ReferenceId" FROM "Reference"."Reference" WHERE "TypeReferenceId"=%s""",(publicationId,))
    #referenceId = ocursor.fetchone()[0]
    
    #WorkflowWOSISUtils
    #1) Get or set Publication record
    #2) Link PublicationId to TypeReferenceId
    #3) Create DIF
    
    ### GET GENERAL DATA #####
    #Skype Hannes
    # create two publications with the both names, authors as in the excel document, 
    #title: "soil sampling in the belgian uplands I" and "soil sampling in the belgian uplands II", 
    #we will get titles later on
    
    #print wosisDB.searchPublication("%Leuven%",onlyId=False)
    #print dir(WOSIS)
    #print WOSISUtils.validateISBN("3-528-16419-0")
    #{'Title': 'The Cat in the Hat','ISBN': 'ISO 3166-13-528-16419-0','Publisher': 'Random House','PublicationYear': 1957,'Issue': None,'Author': 'Dr. Seuss'}      
    #wosisDB.insertPublication({'Title': '','ISBN': '3-528-16419-0','Publisher': 'Random House','PublicationYear': 1957,'Issue': None,'Author': 'Dr. Seuss'})
    
    
   
    ###### CREATE DIF ENTRANCE ######'doetterl'
    
    
    difDic = {'Entry_ID':'UoLEUVEN_SOIL_I',
              'Entry_Title':'Soil sampling in the belgian uplands I',
              'Summary':'University Leuven soils','Private':False}
    
    pkDIF=wosisDB.insertDIF(difDic)
   
    ######### END  CREATE #############

    ##### INSERT AUTHORS ######
    #wosisDB.insertPublication(self,p):
    authorList=[]
    for row in xrange(1,profileData.nrows):
        author = profileData.cell_value(row,5)
        if author not in authorList:
            authorList.append(author) 
    pubDic={}
    for author in authorList:
        pkAuth=wosisDB.insertPublication({'Title': "Soil sampling in the belgian uplands I & II",                            
                                   'Publisher': 'Leuven University',
                                   'PublicationYear': 2012,
                                   'Author' : author} ) 
        pubDic[author]=pkAuth                       
                                                
    ########END INSERT ###########3
    
    ############### INSERT REFERENCE TO PUBLICATION #############
    pubRefDic={}
    [pubRefDic.__setitem__(k,wosisDB.insertReferenceToPublication(v)) for k,v in pubDic.items()]
    
    
    #############################################################
    

  
    ######### SEARCH FOR  LEUVEN #########
    _,orgId=wosisDB.searchOrganization('%Leuven%')
    orgId=orgId[0][0]
    
    ##############################################
    
    
    ##### INSERT Organization x Method and adding it to the DB ####
    #headerProfDic # --> {0: u'ID', 1: u'Depth', 2: u'Texture', 3: u'Inorganic C %', 4: u'organic C %', 5: u'Bulk density g/cm-3', 6: u'Stones %'}
    
    methodDic={u'Inorganic C %':[124],u'organic C %':[92], u'Bulk density g/cm-3':[25],u'Texture':[185],u'Stones %':[49]}
    #attProfId={u'Texture':[185],u'Stones %':[49]}
    orgAnaDic={}
    
    #try/except to deal with situation where 'OrganizationId' x 'AnalyticalMethodId' may have been already inserted
    
    for method in methodDic.keys():
            #{'OrganizationId':<int>,AnalyticalMethodId:<int>,Notes:<str>}
        analyticalMethod = methodDic[method][0]
            #FIST SEARCH FOR Key ("OrganizationId", "AnalyticalMethodId") IF NOT THEN ADD
        _,result = wosisDB.searchOrganizationAnalyticalMethodFK(orgId, analyticalMethod,onlyId=True)
        if type(result) is type(None):    
            pkOrgAna = wosisDB.insertOrganizationAnalyticalMethod({'OrganizationId':orgId,'AnalyticalMethodId':analyticalMethod,'Notes':'Need to check if methods are correct'})
            orgAnaDic[method] = pkOrgAna
        else:
            orgAnaDic[method]=result[0][0]
             #{'OrganizationId':<int>,AnalyticalMethodId:<int>,Notes:<str>}
  
   #orgAnaDic={u'Inorganic C %':4915,u'organic C %':4917, u'Bulk density g/cm-3':4916,u'Texture':4914,u'Stones %':4913}
    

    #print orgAnaDic
    
    ##########  END INSERT ###############



    ################## IMPORT DATA ##############################
    
    for row in xrange(1,profileData.nrows):
        #[text:u'Belgium', number:4.6669009781), number:50.6498886, number:3.0, xldate:40990.0, text:u'Doetterl Sebastian', text:u'Eastern central Belgian Loambelt', number:131.0, text:u'117E', text:u'BM', text:u'S', number:1.0, text:u'Northeast', text:u'AA', text:u'Ce', text:u'Pl', empty:'', text:u'UC1', text:u'vYa', text:u'N', text:u'N', text:u'WD', text:u'A', text:u'N', text:u'N', text:u'N', number:2.0, text:u'CV', text:u'117E10', number:30.0]
        #Country:
   
        cntName=profileData.cell_value(row,0)
        _,countryId=wosisDB.searchCountry(name=cntName)
        countryId = countryId[0][0]
        
        #geoPoint
       #print profileData.cell_value(row,1),profileData.cell_value(row,2)
        geoPoint = WOSISUtils.xyToPoint(profileData.cell_value(row,1),profileData.cell_value(row,2))
        #Soil Status
        soilStatus = int(profileData.cell_value(row,3))
        #Date
        try:
            tmp = xlrd.xldate_as_tuple(profileData.cell_value(row,4),wb.datemode)
            dateDT = datetime.datetime(*tmp)
        except:
            dateDT = None
        #Author
        author = profileData.cell_value(row,5)
        # Location
        location = profileData.cell_value(row,6)
        #elevation
        elevation = int(profileData.cell_value(row,7))
        #mapShjee
        mapSheetNum=profileData.cell_value(row,8)
        mapSheetNum=profileData.cell_value(row,8) if len(profileData.cell_value(row,8))>1 else None
        position = profileData.cell_value(row,9)
        slopeForm = profileData.cell_value(row,10)
        slopeGrad = float(profileData.cell_value(row,11))
        slopeOrient = profileData.cell_value(row,12)
        landUse = profileData.cell_value(row,13)
        crop = profileData.cell_value(row,14) if len(profileData.cell_value(row,14))>1 else None
        humanInf = profileData.cell_value(row,15) if len(profileData.cell_value(row,15))>1 else None
        vegetation = profileData.cell_value(row,16) if len(profileData.cell_value(row,16))>1 else None
        parMaterial = profileData.cell_value(row,17) 
  
        ageLandSurf = profileData.cell_value(row,18) 
        surfRock = profileData.cell_value(row,19)
        surfCoarse = profileData.cell_value(row,20)
        erosionCat =  profileData.cell_value(row,21)
        periodAct = profileData.cell_value(row,22)
        crustThick = profileData.cell_value(row,23)
        coverSalt = profileData.cell_value(row,24)
        bleachCov = profileData.cell_value(row,25)
        slopeClass = int(profileData.cell_value(row,26))
        majorLand = profileData.cell_value(row,27)
        profileName = profileData.cell_value(row,28)
        APDepth = profileData.cell_value(row,29)
        #Attribute mapping
        #noAttr=[u'Country', u'Date', u'Age of landsurface', u'ID', u'AP_depth']
        attrValue={
                  u'Soil profile description status':soilStatus,
                  u'Date':dateDT,
                  u'Author':author,
                  u'Location': location,
                  u'ELEVATION': elevation,
                  u'Map sheet number': mapSheetNum,
                  u'Position': position,
                  u'Slope form': slopeForm,
                  u'Slope gradient class':slopeGrad,
                  u'Slope gradient %': slopeGrad,
                  u'Slope orientation':slopeOrient,
                  u'Land use': landUse, 
                  u'Crop': crop,
                  u'Human Influence':humanInf,
                  u'Vegetation': vegetation, 
                  u'Parent material': parMaterial,
                  u'Age of landsurface': ageLandSurf,
                  u'Surface cover of rock outcrops' :  surfRock,
                  u'Surface cover of coarse fragments': surfCoarse,
                  u'Main erosion category': erosionCat,
                  u'Period of activity of erosion': periodAct,
                  u'Surface crust thickness': crustThick,
                  u'Cover of salt at the surface': coverSalt,
                  u'Bleached sand surface cover': bleachCov,
                  u'Slope gradient class':  slopeClass,
                  u'Major landform': majorLand,
                  u'ID': profileName,
                  u'AP_depth' : APDepth}
        
        attrValueLayer = {'ID': [306],
                          'Texture': [185],
                          'Inorganic C %':[431],
                          'organic C %':[256],
                          'Bulk density g/cm-3':[80],
                          'Stones %':[49],
                          'AP_depth': [70]
                          }
#       

        ######## INSERT PROFILE ###########
        #{'CountryId':<int>,'ReferenceId':<int>, 'Location': <geoPoint>,'LocationLoD': <float>}
        profileID=wosisDB.insertProfile({'CountryId':countryId,'ReferenceId':pubRefDic[author],'Location': geoPoint,'LocationLoD':None,'Editor':editorProfile,'Shared':shared})
        
        print "Inserting ProfileID: %s" % profileID
        
        wosisDB.insertProfileMetadata(profileID,shared)
        
        ########## END INSERT ############
       
        ##### INSERT PROFILExDIF ###########
        wosisDB.insertProfileDIF({'ProfileId':profileID,'DIF_Id':pkDIF,'ProfileName':attrValue['ID']})
        ###### END INSERT   ################
         
        #{'ProfileId':<int>,'ValueDescriptorId':<int>,'Value':<str>,'Value','ValueGroup':<int>,'Org','OrganizationAnalyticalMethodId':<int>,LoD:<str>}
        
        
        for attribute in [attribute for attribute in attrValue.keys() if attribute not in ["Location","AP_depth"] ]:
    
            if valDesDic[attribute] is None or attrValue[attribute] is None:
                pass
                #print "ValueDescriptor or Attribute is None"
                #print valDesDic[attribute] , attrValue[attribute]
            else:    
                
                wosisDB.insertProfileAttribute({'ProfileId':profileID,
                      'ValueDescriptorId': valDesDic[attribute][0],
                      'Value': attrValue[attribute],'ValueGroup':None,
                        'OrganizationAnalyticalMethodId':None,'LoD':None})
        
        #### DEALING WITH AP....as a layer
        wosisDB.insertLayerAttribute({'ProfileId':profileID,
                                      'ValueDescriptorId': attrValueLayer['AP_depth'][0],
                                      'Value': 'AP','ValueGroup':None,
                                      'OrganizationAnalyticalMethodId':None,'LoD':None,
                                      'LabNotes':None,'UpperBoundaryCm':0,
                                     'LowerBoundaryCm': attrValue['AP_depth'],'BinaryDataId':None   })
             
        ####################################################
        
        layerRows = getLayerRow(profileDepthSheet,profileName)
        
        for idx,layer in enumerate(layerRows):
            #  [4806, 4807, 4808, 4809, 4810, 4811, 4812, 4813, 4814, 4815, 4816, 4817, 4818, 4819]
            upperDepth = profileDepthSheet.cell_value(layer,1)
            try:
                lowerDepth = profileDepthSheet.cell_value(layerRows[idx+1],1)
            except:
                lowerDepth=None
            
            valuesLayer={}#--> Where the spread sheed values are actually stored
            valuesLayer['ID'] = profileDepthSheet.cell_value(layer,0)
            valuesLayer['Texture'] = profileDepthSheet.cell_value(layer,2)
            valuesLayer['Inorganic C %'] = profileDepthSheet.cell_value(layer,3)
            valuesLayer['organic C %'] = profileDepthSheet.cell_value(layer,4)
            valuesLayer['Bulk density g/cm-3'] =  profileDepthSheet.cell_value(layer,5)
            valuesLayer['Stones %'] =  profileDepthSheet.cell_value(layer,6)
       
            for attribute in [attribute for attribute in attrValueLayer.keys() if attribute not in ["ID","AP_depth"] ]:
                
                #SELECT * FROM "Attribute"."Attribute" WHERE "Name" ILIKE '%horizon%'  ;
                #dealing with AP
                if valuesLayer[attribute] is None or valuesLayer[attribute]=='':
                #if layerDesDic[attribute] is None or attrValueLayer[attribute] is None:
                    pass
                    #print "ValueDescriptor or Attribute is None"
                    #print valDesDic[attribute] , attrValue[attribute]
                else:    
                    wosisDB.insertLayerAttribute({'ProfileId':profileID,'ValueDescriptorId': layerDesDic[attribute][0],
                    'Value': valuesLayer[attribute],'ValueGroup':None,
                    'OrganizationAnalyticalMethodId':orgAnaDic[attribute],'LoD':None,
                    'LabNotes':None,'UpperBoundaryCm':upperDepth ,
                    'LowerBoundaryCm':lowerDepth,'BinaryDataId':None   })
            
        #layer structure --> {attributes:{ID:<value>,'Texture':
        #<value>,'InorganicC':<value>,'OrganicC':<value>,
        #'Bulk':<value>,'Stones':<value>}, }

        #print wosisDB.searchOrgAnalyticalMethod('%unknown%',labName=True,onlyId=False)

if __name__== '__main__':
    main()
    print "Done"