Using AskiaAPI to monitor quota
If your quota are rather complicated and you want them to be monitored real time, we have put together an application (the source code is available for you to modify) that you can launch hourly to monitor your quota and report if there are some overrun.
If any response has a frequency different than the expected (as defined by the parameter tolerance), the app logs a message: "Live quota problem: Response '25-34' Expected: 65, found 99".
Make sure you have a properly registered recent version of AskiaAPI.dll in 32 bits (if you don't, ask support and do not forget to unblock it and call regsvr32).
The program takes the following parameters:
Name | Type | Comments |
---|---|---|
fileType | number | indicates the type of file 0 for a QES file, 4 for a legacy SQL file, 5 for a new SQL server file |
qesPath | string | indicates the path of the survey QES |
surveyID | number | indicates the ID of the survey in CCA (only for fileType 4 and 5) |
surveyConnection | string | indicates the connection string to the survey database (only for fileType 4 and 5) |
quotaQuestion | string | indicates the shortcut of the question whose frequencies you want to monitor |
quotaDistribution | string | indicates the frequencies you would like to have in the monitored question (the numbers do not have to add to 100) (as a comma separated string) |
script | string | an optional script indicating which interviews should be selected (eg ??Gender?? = 1) |
tolerance | number | Indicates the tolerance (in number of interviews) between observed and expected |
outputPath | string | Indicates the path of the file where the errors need to be logged |
Example of a call to the application
CheckQuota.exe /fileType:5 /surveyID:6 /surveyConnection:"Provider=sqloledb; Data Source=JEROME-E6530\SQLEXPRESS; Initial Catalog=Surveys;Trusted_Connection=yes" /qesPath:"C:\Qes\Irene\Merge\KG13143.qes" /script:"??S2b?? = 1" /quotaQuestion:"S2" /quotaDistribution:"0,10,10,30,30,10,10" /tolerance:10 /outputPath":c:\qes\Livequota.txt"
Here is the main code (hopefully commented enough) - the whole project is attached in CheckQuota.rar.
Sub Main() '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Read the command line parameters '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim CommandLineArgs As System.Collections.ObjectModel.ReadOnlyCollection(Of String) = My.Application.CommandLineArgs Dim strQesPath As String Dim lSurveyID As Long = 0 Dim strSurveyConnection As String Dim nFileType As Short Dim strScript As String Dim strQuotaQuestion As String Dim strQuotaDistribution As String Dim lTolerance As Long Dim strOutputPath As String Dim nArg As Integer For nArg = 0 To CommandLineArgs.Count - 1 Dim strVerb As String, strComplement As String FindVerb(CommandLineArgs(nArg), strVerb, strComplement) If String.Compare(strVerb, "qesPath", True) = 0 Then strQesPath = strComplement ElseIf String.Compare(strVerb, "surveyID", True) = 0 Then lSurveyID = CLng(strComplement) ElseIf String.Compare(strVerb, "surveyConnection", True) = 0 Then strSurveyConnection = strComplement ElseIf String.Compare(strVerb, "fileType", True) = 0 Then nFileType = CShort(strComplement) ElseIf String.Compare(strVerb, "script", True) = 0 Then strScript = strComplement ElseIf String.Compare(strVerb, "quotaQuestion", True) = 0 Then strQuotaQuestion = strComplement ElseIf String.Compare(strVerb, "quotaDistribution", True) = 0 Then strQuotaDistribution = strComplement ElseIf String.Compare(strVerb, "tolerance", True) = 0 Then lTolerance = CLng(strComplement) ElseIf String.Compare(strVerb, "outputPath", True) = 0 Then strOutputPath = strComplement End If Next nArg '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Check the parameters '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' If strQesPath = "" Then MsgBox("No survey - use /qesPath") Exit Sub End If If strQuotaQuestion = "" Then MsgBox("No question - use /quotaQuestion") Exit Sub End If If strQuotaDistribution = "" Then MsgBox("No quota distribution - use /quotaDistribution") Exit Sub End If '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Initialise the askiaAPI COM control '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim askiaAPi As New ASKIAAPILib.Analyse askiaAPi.FilePath = strQesPath askiaAPi.nSurveyType = nFileType Select Case nFileType Case 0 ' aftQes Case 1 ' aftDimensions Case 2 ' aftQem Case 3 ' aftSurf Case 4 ' aftQesSQL askiaAPi.CcaSurveyID = lSurveyID askiaAPi.CcaSurveyConnection = strSurveyConnection Case 5 ' aftQesSQLSingle, askiaAPi.CcaSurveyID = lSurveyID askiaAPi.CcaSurveyConnection = strSurveyConnection End Select ' Cannot open the QES file? If askiaAPi.Init() <> 0 Then MsgBox("Cannot open survey") Exit Sub End If '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Find the question '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim arrMatches Dim nWhere As Integer = 1 + 16 ' shortcut + Closed question askiaAPi.FindString(strQuotaQuestion, nWhere, True, True, -1, -1, -1, arrMatches) If UBound(arrMatches) < 0 Then MsgBox("Cannot find question " & strQuotaQuestion) Exit Sub End If Dim nQuotaQuestion As Integer = arrMatches(0) '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Create (if needed) a temporary sub-population '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim lSubPopulationID = -1 ' all interviews If strScript <> "" Then askiaAPi.CreateTemporarySubPopulation("tempSP", "", strScript, lSubPopulationID) End If '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get the counts '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get the response list Dim arrNames, nResponseCount, arrCodes As Object askiaAPi.GetResponseListWithoutCount(nQuotaQuestion, nResponseCount, arrNames, arrCodes) ' Prepare the array for selecting the responses Dim arrResponses As Object ReDim arrResponses(nResponseCount - 1) Dim nResponse As Integer For nResponse = 0 To nResponseCount - 1 arrResponses(nResponse) = nResponse Next ' Get the flat counts Dim arrResults, arrBase askiaAPi.DoFlatCounts(nQuotaQuestion, nResponseCount, arrResponses, 0, lSubPopulationID, -1, arrResults, arrBase) '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Compare the observed and expected counts '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim arrData() As String = Split(strQuotaDistribution, ",") Dim lTheoric As Long = 0 Dim lObserved As Long = 0 For nResponse = 0 To UBound(arrData) lTheoric += CLng(arrData(nResponse)) lObserved += CLng(arrResults(nResponse, 0)) Next For nResponse = 0 To UBound(arrData) Dim nTheoric As Long nTheoric = CInt(lObserved / lTheoric * CLng(arrData(nResponse))) If Math.Abs(arrResults(nResponse, 0) - nTheoric) > lTolerance Then ' Log the error Dim fileExists As Boolean = File.Exists(strOutputPath) Using sw As New StreamWriter(File.Open(strOutputPath, FileMode.OpenOrCreate)) sw.WriteLine("Live quota problem Response '" & arrNames(nResponse) & "' Expected: " & nTheoric & ", found " & arrResults(nResponse, 0)) End Using End If Next End Sub Sub FindVerb(ByVal strArg As String, ByRef strVerb As String, ByRef strComplement As String) Select Case Left(strArg, 1) Case "-" strArg = Mid(strArg, 2) Case "/" strArg = Mid(strArg, 2) End Select Dim nPos As Integer nPos = InStr(strArg, ":") If nPos = 0 Then strVerb = strArg strComplement = "" Else strVerb = Left(strArg, nPos - 1) strComplement = Mid(strArg, nPos + 1) End If End Sub