' =============================================================
' This script demonstrates a way to use the RunOnce feature to pre-
' populate the case data from an Excel file that is delivered to
' each machine as part of synchronization.
'
' The script discovers most of what it needs to know from the
' data file and its location in the file system.
'
' =============================================================

Const DATA_FILE_NAME = "Sample.xls"
Const DATA_FILE_SHEET = "Sample"

' Get the project name and user id from the script path
On Error Goto ErrorHandler
Dim fso, path, user_folder, project_name, user_id
Set fso = CreateObject("Scripting.FileSystemObject")

path = fso.GetAbsolutePathName(".")
Set user_folder = fso.GetFolder(path)

project_name = GetProjectName(user_folder)
user_id = GetUserId(user_folder)

' Setup a logger that will write to logs shown by the 'Synchronization Status'
Dim logger
Set logger = CreateObject("AppLog.AppLog")
logger.Open("MIV", 100)
logger.Log("RunOnce.mrs - Loading sample for project '" + project_name + "', user ID '" + _
user_id + "', from " + path + "\" + DATA_FILE_NAME, logLevels.LOGLEVEL_INFO)

' Connect to the case DB
Dim case_db, case_rs, records_added
Set case_db = CreateObject("ADODB.Connection")
case_db.Open(GetCaseDbConnectionString(project_name))
Set case_rs = CreateObject("ADODB.Recordset")
case_rs.Open("SELECT * FROM VDATA", case_db)
records_added = 0

' Connect to the sample data
Dim sample_db, sample_rs
Set sample_db = CreateObject("ADODB.Connection")
sample_db.Open(GetSampleDbConnectionString(DATA_FILE_NAME, DATA_FILE_SHEET))

' Get the sample for the current user
Set sample_rs = sample_db.Execute("SELECT * FROM VDATA WHERE LCase(UserId) = '" + LCase(user_id) + "'")

While NOT sample_rs.EOF
Dim respondent_id
respondent_id = sample_rs.Fields["RespondentId"]

' Add the sample if the respondent does not already exist
If IsNewRespondent(case_db, respondent_id) Then
' Add the record
Dim Fields[3], Values[3]
Fields[0] = "Respondent.Serial"
Values[0] = GetNextSerial(case_db)
Fields[1] = "Respondent.Id"
Values[1] = respondent_id
Fields[2] = "DataCollection.Status"
Values[2] = "{scriptstopped}"

case_rs.AddNew(Fields, Values)
records_added = records_added + 1

' TODO - add any additional sample data to the case data
End If

sample_rs.MoveNext()

End While

logger.Log("RunOnce.mrs - " + CText(records_added) + " sample records added successfully", logLevels.LOGLEVEL_INFO)

Exit

ErrorHandler:
logger.Log("RunOnce.mrs - Error loading sample, " + Err.Description)
' ==============================================================

Function GetNextSerial(db)
Dim rs
Set rs = db.Execute("SELECT MAX(Respondent.Serial) FROM VDATA")
If rs.EOF Then
GetNextSerial = 1
Else
GetNextSerial = rs.Fields[0] + 1
End If
End Function

' =============================================================

Function IsNewRespondent(db, respondentId)
Dim rs
Set rs = db.Execute("SELECT Respondent.Id FROM VDATA WHERE LCase(Respondent.Id) = '" + LCase(respondentId) + "'")
IsNewRespondent = rs.EOF
End Function

' =============================================================

Function GetUserId(userFolder)
GetUserId = userFolder.Name
End Function

' =============================================================

Function GetProjectName(userFolder)
GetProjectName = userFolder.ParentFolder.Name
End Function

' =============================================================

Function GetCaseDbConnectionString(projectName)
GetCaseDbConnectionString = MakeString("Provider=mrOleDB.Provider.2;Data Source=mrDataFileDsc;Location=CaseData\", _
projectName, ".ddf;Initial Catalog=CaseData\", projectName, ".mdd")
Debug.Log(GetCaseDbConnectionString)
End Function

' =============================================================

Function GetSampleDbConnectionString(dataFile, sheetName)
GetSampleDbConnectionString = MakeString("Provider=mrOleDB.Provider.2;Data Source=mrADODsc;", _
"Location=""Provider=Microsoft.ACE.OLEDB.12.0;Data Source=", dataFile, _
";Extended Properties=""""Excel 12.0;HDR=YES;IMEX=1"""";"";MR Init Project=", _
sheetName, "$;")
Debug.Log(GetSampleDbConnectionString)
End Function

' =============================================================