/// <summary>
/// Parses XML from the web survey
/// </summary>
private void ParseXML(string filePath)
{
_surveyResponses = new Dictionary<string, List<PhoneFieldData>>();
surveyGUIDs = new List<string>();
PhoneFieldData wfData = new PhoneFieldData();
string xmlText;
string password = txtPassword.Text;
string encrypted = System.IO.File.ReadAllText(filePath);
try
{
xmlText = Configuration.DecryptJava(encrypted, password);
}
catch (Exception ex)
{
this.BeginInvoke(new SetStatusDelegate(AddErrorStatusMessage), "Invalid password or sync file.");
return;
}
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
doc.LoadXml(xmlText);
if (doc.ChildNodes[0].Name.ToLower().Equals("surveyresponses"))
{
foreach (XmlElement docElement in doc.ChildNodes[0].ChildNodes)
{
if (docElement.Name.ToLower().Equals("surveyresponse") && docElement.Attributes.Count > 0 && docElement.Attributes[0].Name.ToLower().Equals("surveyresponseid"))
{
string surveyResponseId = docElement.Attributes[0].Value;
if (!surveyGUIDs.Contains(surveyResponseId))
{
surveyGUIDs.Add(surveyResponseId);
}
foreach (XmlElement surveyElement in docElement.ChildNodes)
{
if (surveyElement.Name.ToLower().Equals("page") && surveyElement.Attributes.Count > 0 && surveyElement.Attributes[0].Name.ToLower().Equals("pageid"))
{
List<PhoneFieldData> fieldValues = new List<PhoneFieldData>();
foreach (XmlElement pageElement in surveyElement.ChildNodes)
{
if (pageElement.Name.ToLower().Equals("responsedetail"))
{
string fieldName = string.Empty;
if (pageElement.Attributes.Count > 0)
{
fieldName = pageElement.Attributes[0].Value;
}
object fieldValue = FormatWebFieldData(fieldName, pageElement.InnerText);
wfData = new PhoneFieldData();
wfData.RecordGUID = surveyResponseId;
wfData.Page = Convert.ToInt32(surveyElement.Attributes[0].Value);
wfData.FieldName = fieldName;
wfData.FieldValue = fieldValue;
fieldValues.Add(wfData);
if (_surveyResponses.Keys.Contains(surveyResponseId))
{
_surveyResponses[surveyResponseId].Add(wfData);
}
else
{
List<PhoneFieldData> list = new List<PhoneFieldData>();
list.Add(wfData);
_surveyResponses.Add(surveyResponseId, list);
}
}
}
}
}
}
}
}
}
/// <summary>
/// Processes all of the fields on a given form, page-by-page, except for the fields on the base table.
/// </summary>
/// <param name="destinationView">The destination form</param>
/// <param name="destinationGUIDList">The list of GUIDs that exist in the destination</param>
private void ProcessPages(View destinationView, List<string> destinationGUIDList, string syncFilePath)
{
int recordsProcessed = 0;
foreach (KeyValuePair<string, List<PhoneFieldData>> kvp in _surveyResponses)
{
string recordKey = kvp.Key;
Dictionary<int, List<PhoneFieldData>> pagedFieldDataDictionary = new Dictionary<int, List<PhoneFieldData>>();
for (int i = 0; i < destinationView.Pages.Count; i++)
{
pagedFieldDataDictionary.Add(destinationView.Pages[i].Id, new List<PhoneFieldData>());
}
foreach (PhoneFieldData fieldData in kvp.Value)
{
int pageId = fieldData.Page;
if (fieldData.FieldName.ToLower().Equals("globalrecordid") || destinationView.GetPageById(pageId).Fields.Contains(fieldData.FieldName))
{
pagedFieldDataDictionary[pageId].Add(fieldData);
}
}
for (int i = 0; i < destinationView.Pages.Count; i++)
{
this.BeginInvoke(new SetStatusDelegate(SetStatusMessage), "Processing records on page " + (i + 1).ToString() + " of " + destinationView.Pages.Count.ToString() + "...");
int recordsInserted = 0;
int recordsUpdated = 0;
Page destinationPage = destinationView.Pages[i];
string pageTableName = destinationPage.TableName;
List<PhoneFieldData> pagedFieldDataList = pagedFieldDataDictionary[destinationPage.Id];
Query selectQuery = destinationProjectDataDriver.CreateQuery("SELECT [GlobalRecordId] FROM [" + pageTableName + "]");
IDataReader destReader = destinationProjectDataDriver.ExecuteReader(selectQuery);
destinationGUIDList = new List<string>();
while (destReader.Read())
{
destinationGUIDList.Add(destReader[0].ToString());
}
destReader.Close();
try
{
if (pagedFieldDataList.Count == 0 && append)
{
InsertEmptyPageTableRecord(recordKey, pageTableName);
}
string currentGUID = string.Empty;
string lastGUID = string.Empty;
WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA);
WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA);
List<QueryParameter> fieldValueParams = new List<QueryParameter>();
int fieldsInQuery = 0;
PhoneFieldData wfData = new PhoneFieldData();
wfData.RecordGUID = "__LAST__"; // last field, acts as a flag
pagedFieldDataList.Add(wfData);
List<string> GUIDList = new List<string>();
foreach (PhoneFieldData fieldData in pagedFieldDataList)
{
currentGUID = fieldData.RecordGUID;
if (importWorker.CancellationPending)
{
this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "Import cancelled.");
return;
}
string GUID = fieldData.RecordGUID; // sourceReader["GlobalRecordId"].ToString();
List<string> updatedGUIDs = new List<string>();
if (destinationGUIDList.Contains(GUID) && update)
{
#region UPDATE
currentGUID = fieldData.RecordGUID;
if (importWorker.CancellationPending)
{
this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "Import cancelled.");
return;
}
string updateHeader = string.Empty;
string whereClause = string.Empty;
fieldValueParams = new List<QueryParameter>();
StringBuilder sb = new StringBuilder();
// Build the Update statement which will be reused
sb.Append(SqlKeyWords.UPDATE);
sb.Append(StringLiterals.SPACE);
sb.Append(destinationProjectDataDriver.InsertInEscape(destinationPage.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append(SqlKeyWords.SET);
sb.Append(StringLiterals.SPACE);
updateHeader = sb.ToString();
sb.Remove(0, sb.ToString().Length);
// Build the WHERE caluse which will be reused
sb.Append(SqlKeyWords.WHERE);
sb.Append(StringLiterals.SPACE);
sb.Append(destinationProjectDataDriver.InsertInEscape(ColumnNames.GLOBAL_RECORD_ID));
sb.Append(StringLiterals.EQUAL);
sb.Append("'");
sb.Append(GUID);
sb.Append("'");
whereClause = sb.ToString();
sb.Remove(0, sb.ToString().Length);
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append(fieldData.FieldName);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sb.Append(StringLiterals.EQUAL);
sb.Append(StringLiterals.COMMERCIAL_AT);
sb.Append(fieldData.FieldName);
QueryParameter param = GetQueryParameterForField(fieldData, destinationPage, syncFilePath);
if (param != null)
{
Query updateQuery = destinationProjectDataDriver.CreateQuery(updateHeader + StringLiterals.SPACE + sb.ToString() + StringLiterals.SPACE + whereClause);
updateQuery.Parameters.Add(param);
destinationProjectDataDriver.ExecuteNonQuery(updateQuery);
if (!GUIDList.Contains(GUID))
{
GUIDList.Add(GUID);
recordsUpdated++;
this.BeginInvoke(new SetProgressBarDelegate(IncrementProgressBarValue), 1);
}
}
#endregion // UPDATE
}
else if (!destinationGUIDList.Contains(GUID) && append)
{
#region APPEND
if (!string.IsNullOrEmpty(lastGUID) && lastGUID != currentGUID)
{
// Concatenate the query clauses into one SQL statement.
StringBuilder sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(destinationProjectDataDriver.InsertInEscape(destinationPage.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append(Util.InsertInParantheses(fieldNames.ToString()));
sb.Append(" values (");
sb.Append(fieldValues.ToString());
sb.Append(") ");
Query insertQuery = destinationProjectDataDriver.CreateQuery(sb.ToString());
insertQuery.Parameters = fieldValueParams;
destinationProjectDataDriver.ExecuteNonQuery(insertQuery);
recordsInserted++;
fieldNames = new WordBuilder(StringLiterals.COMMA);
fieldValues = new WordBuilder(StringLiterals.COMMA);
fieldValueParams = new List<QueryParameter>();
fieldsInQuery = 0;
}
lastGUID = fieldData.RecordGUID;
if (lastGUID == "__LAST__")
{
break;
}
if (!fieldNames.Contains("GlobalRecordId"))
{
fieldNames.Append("GlobalRecordId");
fieldValues.Append("@GlobalRecordId");
fieldValueParams.Add(new QueryParameter("@GlobalRecordId", DbType.String, GUID));
fieldsInQuery++;
}
Field dataField = destinationView.Fields[fieldData.FieldName]; // already checked for this above so should never fail...
if (!(
dataField is GroupField ||
dataField is RelatedViewField ||
dataField is UniqueKeyField ||
dataField is RecStatusField ||
dataField is GlobalRecordIdField ||
fieldData.FieldValue == null ||
string.IsNullOrEmpty(fieldData.FieldValue.ToString())
))
{
String fieldName = ((Epi.INamedObject)dataField).Name;
switch (dataField.FieldType)
{
case MetaFieldType.Date:
case MetaFieldType.DateTime:
case MetaFieldType.Time:
fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.DateTime, Convert.ToDateTime(fieldData.FieldValue)));
break;
case MetaFieldType.Checkbox:
fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.Boolean, Convert.ToBoolean(fieldData.FieldValue)));
break;
case MetaFieldType.CommentLegal:
case MetaFieldType.LegalValues:
case MetaFieldType.Codes:
case MetaFieldType.Text:
case MetaFieldType.TextUppercase:
case MetaFieldType.PhoneNumber:
case MetaFieldType.UniqueRowId:
case MetaFieldType.ForeignKey:
case MetaFieldType.GlobalRecordId:
case MetaFieldType.Multiline:
fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.String, fieldData.FieldValue));
break;
case MetaFieldType.Number:
case MetaFieldType.YesNo:
case MetaFieldType.Option:
case MetaFieldType.RecStatus:
fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.Single, fieldData.FieldValue));
break;
case MetaFieldType.Image:
byte[] imageBytes = null;
if (syncFilePath.Contains("EpiInfo\\"))
{
string part1 = syncFilePath.Substring(0, syncFilePath.IndexOf("EpiInfo"));
string part2 = fieldData.FieldValue.ToString().Substring(fieldData.FieldValue.ToString().IndexOf("EpiInfo"), fieldData.FieldValue.ToString().Length - fieldData.FieldValue.ToString().IndexOf("EpiInfo"));
string fileName = part1 + part2;
if (File.Exists(fileName))
{
imageBytes = Util.GetByteArrayFromImagePath(fileName);
}
}
else
{
var devices = new PortableDevices.PortableDeviceCollection();
try
{
devices.Refresh();
}
catch (System.IndexOutOfRangeException)
{
break;
}
if (devices.Count == 0)
{
break;
}
var pd = devices.First();
pd.Connect();
PortableDeviceFolder root = pd.GetContents();
PortableDeviceFolder download =
(from r in ((PortableDeviceFolder)root.Files[0]).Files
where r.Name.Equals("Download")
select r).First() as PortableDeviceFolder;
PortableDeviceFolder epiinfo =
(from r in download.Files
where r.Name.Equals("EpiInfo")
select r).First() as PortableDeviceFolder;
PortableDeviceFolder images =
(from r in epiinfo.Files
where r.Name.Equals("Images")
select r).First() as PortableDeviceFolder;
if (images == null)
{
pd.Disconnect();
break;
}
else
{
try
{
string imageFileName = fieldData.FieldValue.ToString().Substring(fieldData.FieldValue.ToString().IndexOf("Images/")).Split('/')[1].Split('.')[0];
string tempPath = Path.GetTempPath();
PortableDeviceFile existingFile =
(from r in images.Files
where r.Name.Equals(imageFileName)
select r).First() as PortableDeviceFile;
new FileInfo(tempPath + existingFile.Id).Create().Close();
pd.DownloadFile(existingFile, tempPath);
imageBytes = Util.GetByteArrayFromImagePath(tempPath + existingFile.Id);
}
catch (Exception ex)
{
//
}
}
}
if (imageBytes != null)
{
fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.Binary, imageBytes));
}
break;
default:
throw new ApplicationException("Not a supported field type");
}
fieldNames.Append(destinationProjectDataDriver.InsertInEscape(((Epi.INamedObject)dataField).Name));
fieldValues.Append("@" + fieldName);
fieldsInQuery++;
}
#endregion // APPEND
}
this.BeginInvoke(new SetProgressBarDelegate(IncrementProgressBarValue), 1);
}
}
catch (Exception ex)
{
this.BeginInvoke(new SetStatusDelegate(AddErrorStatusMessage), ex.Message);
}
finally
{
}
// this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "On page '" + destinationPage.Name + "', " + recordsInserted.ToString() + " record(s) inserted and " + recordsUpdated.ToString() + " record(s) updated.");
}
recordsProcessed++;
}
this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), recordsProcessed.ToString() + " record(s) processed.");
}
private QueryParameter GetQueryParameterForField(PhoneFieldData fieldData, Page sourcePage, string syncFilePath)
{
Field dataField = destinationView.Fields[fieldData.FieldName];
if (!(
dataField is GroupField ||
dataField is RelatedViewField ||
dataField is UniqueKeyField ||
dataField is RecStatusField ||
dataField is GlobalRecordIdField ||
fieldData.FieldValue == null ||
string.IsNullOrEmpty(fieldData.FieldValue.ToString())
))
{
String fieldName = ((Epi.INamedObject)dataField).Name;
switch (dataField.FieldType)
{
case MetaFieldType.Date:
case MetaFieldType.DateTime:
case MetaFieldType.Time:
return new QueryParameter("@" + fieldName, DbType.DateTime, Convert.ToDateTime(fieldData.FieldValue));
case MetaFieldType.Checkbox:
return new QueryParameter("@" + fieldName, DbType.Boolean, Convert.ToBoolean(fieldData.FieldValue));
case MetaFieldType.CommentLegal:
case MetaFieldType.LegalValues:
case MetaFieldType.Codes:
case MetaFieldType.Text:
case MetaFieldType.TextUppercase:
case MetaFieldType.PhoneNumber:
case MetaFieldType.UniqueRowId:
case MetaFieldType.ForeignKey:
case MetaFieldType.GlobalRecordId:
case MetaFieldType.Multiline:
return new QueryParameter("@" + fieldName, DbType.String, fieldData.FieldValue);
case MetaFieldType.Number:
case MetaFieldType.YesNo:
case MetaFieldType.Option:
case MetaFieldType.RecStatus:
return new QueryParameter("@" + fieldName, DbType.Single, fieldData.FieldValue);
case MetaFieldType.Image:
byte[] imageBytes = null;
if (syncFilePath.Contains("EpiInfo\\"))
{
string part1 = syncFilePath.Substring(0, syncFilePath.IndexOf("EpiInfo"));
string part2 = fieldData.FieldValue.ToString().Substring(fieldData.FieldValue.ToString().IndexOf("EpiInfo"), fieldData.FieldValue.ToString().Length - fieldData.FieldValue.ToString().IndexOf("EpiInfo"));
string fileName = part1 + part2;
if (File.Exists(fileName))
{
imageBytes = Util.GetByteArrayFromImagePath(fileName);
}
}
else
{
imageBytes = null;
var devices = new PortableDevices.PortableDeviceCollection();
try
{
devices.Refresh();
}
catch (System.IndexOutOfRangeException)
{
break;
}
if (devices.Count == 0)
{
break;
}
var pd = devices.First();
pd.Connect();
PortableDeviceFolder root = pd.GetContents();
PortableDeviceFolder download =
(from r in ((PortableDeviceFolder)root.Files[0]).Files
where r.Name.Equals("Download")
select r).First() as PortableDeviceFolder;
PortableDeviceFolder epiinfo =
(from r in download.Files
where r.Name.Equals("EpiInfo")
select r).First() as PortableDeviceFolder;
PortableDeviceFolder images =
(from r in epiinfo.Files
where r.Name.Equals("Images")
select r).First() as PortableDeviceFolder;
if (images == null)
{
pd.Disconnect();
break;
}
else
{
try
{
string imageFileName = fieldData.FieldValue.ToString().Substring(fieldData.FieldValue.ToString().IndexOf("Images/")).Split('/')[1].Split('.')[0];
string tempPath = Path.GetTempPath();
PortableDeviceFile existingFile =
(from r in images.Files
where r.Name.Equals(imageFileName)
select r).First() as PortableDeviceFile;
new FileInfo(tempPath + existingFile.Id).Create().Close();
pd.DownloadFile(existingFile, tempPath);
imageBytes = Util.GetByteArrayFromImagePath(tempPath + existingFile.Id);
}
catch (Exception ex)
{
//
}
}
}
if (imageBytes != null)
{
return new QueryParameter("@" + fieldName, DbType.Binary, imageBytes);
}
else
{
return new QueryParameter("@" + fieldName, DbType.Binary, null);
}
default:
throw new ApplicationException("Not a supported field type");
}
}
return null;
}
