"properties": {
"qualtrics_connection": {
"title": "Input Connection: Qualtrics ",
"description": "Input Connection: Qualtrics ",
"type": "object",
"properties": {
"connectionProperties": {
"title": "Connection Properties",
"description": "Connection Properties",
"$ref": "http://sap.com/vflow/com.sap.dh.connections.openapi.schema.json",
"sap_vflow_constraints": {
"ui_visibility": [
{
"name": "configurationType",
"value": "Manual"
}
]
}
},
"configurationType": {
"title": "Configuration Type",
"description": "Configuration Type",
"type": "string",
"enum": [
"Configuration Manager",
"Manual"
]
},
"connectionID": {
"title": "Connection ID",
"description": "Connection ID",
"type": "string",
"format": "com.sap.dh.connection.id",
"sap_vflow_valuehelp": {
"url": "/app/datahub-app-connection/connections?connectionTypes=OPENAPI",
"valuepath": "id"
}
}
}
},
"hana_connection": {
"title": "Output Connection: HANA DB",
"description": "Output Connection: HANA DB",
"type": "object",
"properties": {
"configurationType": {
"title": "Configuration Type",
"description": "Configuration Type",
"type": "string",
"enum": [
"Configuration Manager",
"Manual"
]
},
"connectionID": {
"title": "Connection ID",
"description": "Connection ID",
"type": "string",
"format": "com.sap.dh.connection.id",
"sap_vflow_valuehelp": {
"url": "/app/datahub-app-connection/connections?connectionTypes=HANA_DB",
"valuepath": "id",
"displayStyle": "autocomplete"
},
"sap_vflow_constraints": {
"ui_visibility": [
{
"name": "configurationType",
"value": "Configuration Manager"
}
]
}
},
"connectionProperties": {
"title": "Connection Properties",
"description": "Connection Properties",
"$ref": "http://sap.com/vflow/com.sap.dh.connections.openapi.schema.json",
"sap_vflow_constraints": {
"ui_visibility": [
{
"name": "configurationType",
"value": "Manual"
}
]
}
}
}
},
"surveyID": {
"title": "Survey ID",
"type": "string"
},
"userID": {
"title": "User ID",
"type": "string"
},
"script": {
"readOnly": true,
"type": "string",
"sap_vflow_constraints": {
"ui_visibility": false,
"ui_disabled": true
}
},
"load_mode": {
"title": "mode",
"type": "object",
"properties": {
"mode": {
"title": "mode",
"description": "mode",
"type": "string",
"enum": [
"Initial",
"Delta"
]
}
}
},
"HANA_table": {
"title": "Target table name in DWC",
"type": "string"
}
},
import requests
import zipfile
import json
import io
from io import StringIO
import datetime
import time
import pytz
from dateutil import parser
import pandas as pd
import re
from datetime import datetime
from pytz import all_timezones
from hdbcli import dbapi
def openHANAConnection():
hanaConn = api.config.hana_connection['connectionProperties']
hanaConnection = dbapi.connect(address=hanaConn['host'], port=hanaConn['port'], user=hanaConn['user'], password=hanaConn['password'], encrypt='true', sslValidateCertificate="false")
return hanaConnection.cursor()
def closeHANAConnection(cursor):
cursor.close()
def getResponses(mode):
requestCheckProgress = 0
progressStatus = "in progress"
body = {}
body['surveyId'] = surveyId
body['format'] = "csv"
if mode == 'Delta':
import datetime
from pytz import all_timezones
# Get a time zone from Qualtrics
downloadRequestResponse = requests.request("GET", tzUrl, data={}, headers=headers)
timeZone = downloadRequestResponse.json()["result"]["timeZone"]
tz = pytz.timezone(timeZone)
# Current time
now_qlt = datetime.datetime.now(tz)
body['endDate'] = now_qlt.replace(microsecond=0, tzinfo=tz).isoformat()
body['startDate'] = hana_time.replace(microsecond=0, tzinfo=tz).isoformat()
# Creating data export
downloadRequestUrl = baseUrl
downloadRequestPayload = json.dumps(body)
downloadRequestResponse = requests.request("POST", downloadRequestUrl, data=downloadRequestPayload, headers=headers)
progressId = downloadRequestResponse.json()["result"]["id"]
isFile = None
while requestCheckProgress < 100 and progressStatus is not "complete" and isFile is None:
requestCheckUrl = baseUrl + progressId
requestCheckResponse = requests.request("GET", requestCheckUrl, headers=headers)
isFile = (requestCheckResponse.json()["result"]["file"])
if isFile is None:
else:
requestCheckProgress = requestCheckResponse.json()["result"]["percentComplete"]
# Downloading file
requestDownloadUrl = baseUrl + progressId + '/file'
requestDownload = requests.request("GET", requestDownloadUrl, headers=headers, stream=True)
responsesZip = zipfile.ZipFile(io.BytesIO(requestDownload.content))
responsesFiles = responsesZip.namelist()
responseFile = responsesZip.open(responsesFiles[0])
# Prepare data
df = responsesZip.read(responsesFiles[0]).decode("utf-8")
# create pandas dataframe
dfp = pd.read_csv(StringIO(df), sep=",")
#----------------------------#
# Your data preparation code
#----------------------------#
dfp_csv = dfp.to_csv(index=False)
return dfp_csv
restConn = api.config.qualtrics_connection['connectionProperties']
dataCenter = restConn['user']
token = restConn['password']
surveyId = api.config.surveyID
mode = api.config.load_mode['mode']
baseUrl = "https://" + dataCenter + restConn['host'] + "responseexports/"
tzUrl = "https://" + dataCenter + restConn['host'] + "users/" + api.config.userID
headers = {
"content-type": "application/json",
"x-api-token": token
}
if mode == 'Delta':
dbCursor = openHANAConnection()
# Get HANA date
dbCursor.execute('SELECT MAX("EndDate") FROM' + api.config.HANA_table +"')"
# Date parsing for Qualtrics
datetime_str = str(dbCursor.fetchone())
datetime_sbstr = datetime_str[19:-3]
datetime_list_str = datetime_sbstr.split(",")
datatime_list_int = []
for item in datetime_list_str:
datatime_list_int.append(int(item))
years = datatime_list_int[0]
months = datatime_list_int[1]
days = datatime_list_int[2]
hours = datatime_list_int[3]
minutes = datatime_list_int[4]
seconds = datatime_list_int[5]
hana_time = datetime(years, months, days, hours, minutes, seconds)
closeHANAConnection(dbCursor)
dfp = getResponses(mode)
api.send("outData", dfp)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
13 | |
10 | |
10 | |
9 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 |