/// <summary>
/// Creates an XmlElement representing an Epi Info 7 view's data.
/// </summary>
/// <param name="xmlDataPackage">The data package xml document that the XmlElement should be added to</param>
/// <param name="form">The form whose data will be serialized</param>
/// <returns>XmlElement; represents the form's data in Xml format, suitable for use in data packaging</returns>
protected XmlElement CreateXmlFormDataElement(XmlDocument xmlDataPackage, View form)
{
#region Input Validation
if (xmlDataPackage == null)
{
throw new ArgumentNullException("xmlDataPackage");
}
if (form == null)
{
throw new ArgumentNullException("form");
}
#endregion // Input Validation
XmlElement data = xmlDataPackage.CreateElement("Data");
if (StatusChanged != null)
{
StatusChanged(string.Format(PackagerStrings.GUID_LIST_SETUP, form.Name));
}
if (ResetProgress != null)
{
ResetProgress();
}
/* This seems like an usual set of steps to just iterate over the data. The problem is that we can't "just
* iterate over the data" - the data is split up page tables, with one table representing one page on the
* form. While a JOIN might be able to bring everything together into one table, it might not - for example,
* if there are >255 fields after the JOIN, an OleDb exception will be thrown.
*
* To get around this issue: The code first iterates over the rows in the BASE TABLE, obtaining the GUID
* values for each. The GUIDs and their corresponding XmlElement go into a dictionary.
*
* Later, each row in each page is iterated over; as the GUIDs for each page table are accessed, the corresponding
* XmlElement is pulled from the dictionary. Field data is added to it for each field that has data. In this
* manner, it doesn't matter that each row is technically accessed out-of-order because they'll still show up
* in-order in the resulting Xml.
*
* Filtering adds another layer of complexity. To filter, a JOIN operation is needed so that the filters can
* be applied across all those tables, since the fields in the filter may be across different tables. The
* RowFilter class provides a way to handle this; we simply get the query from that object and apply it to the
* reader. Only GUIDs that match the filter are added to the dictionary of guids.
*/
// We need to exclude records from child forms that may now be orphaned as a result of a filter applied to the parent
if (form.IsRelatedView && PreviousDistanceFromRoot < CurrentDistanceFromRoot)
{
ParentIdList.Clear();
foreach (KeyValuePair <string, XmlElement> kvp in IdList)
{
ParentIdList.Add(kvp.Key);
}
}
IdList.Clear(); // Very important, this needs to be re-set in case we've already processed a form (this is a class level variable)
if (!ExportInfo.RecordsPackaged.ContainsKey(form))
{
ExportInfo.RecordsPackaged.Add(form, 0);
}
bool filterThisForm = false;
RowFilters filters = null;
Query selectQuery = null;
if (Filters != null && Filters.ContainsKey(form.Name) && Filters[form.Name].Count() > 0)
{
filterThisForm = true;
filters = Filters[form.Name];
selectQuery = filters.GetGuidSelectQuery(form);
}
double totalRecords = 0;
using (IDataReader guidReader = filterThisForm ? SourceProject.CollectedData.GetDatabase().ExecuteReader(selectQuery) : SourceProject.CollectedData.GetDatabase().GetTableDataReader(form.TableName))
{
while (guidReader.Read())
{
string guid = guidReader["GlobalRecordId"].ToString();
string fkey = guidReader["FKEY"].ToString();
string recstatus = guidReader["RECSTATUS"].ToString();
string firstSaveUserId = string.Empty;
DateTime?firstSaveTime = null;
string lastSaveUserId = string.Empty;
DateTime?lastSaveTime = null;
if (guidReader.FieldCount > 3)
{
try
{
firstSaveUserId = guidReader["FirstSaveLogonName"].ToString();
if (guidReader["FirstSaveTime"] != DBNull.Value)
{
firstSaveTime = (DateTime)guidReader["FirstSaveTime"];
}
lastSaveUserId = guidReader["LastSaveLogonName"].ToString();
if (guidReader["LastSaveTime"] != DBNull.Value)
{
lastSaveTime = (DateTime)guidReader["LastSaveTime"];
}
}
catch (IndexOutOfRangeException)
{
// just ignore, probably an older upgraded project
}
}
if (recstatus.Equals("1")) // only include undeleted records
{
if (!form.IsRelatedView || ParentIdList.Contains(fkey))
{
XmlElement record = xmlDataPackage.CreateElement("Record");
XmlAttribute id = xmlDataPackage.CreateAttribute("Id");
id.Value = guid;
record.Attributes.Append(id);
if (!string.IsNullOrEmpty(fkey))
{
XmlAttribute foreignKey = xmlDataPackage.CreateAttribute("Fkey");
foreignKey.Value = fkey;
record.Attributes.Append(foreignKey);
}
if (!string.IsNullOrEmpty(firstSaveUserId))
{
XmlAttribute firstSaveId = xmlDataPackage.CreateAttribute("FirstSaveUserId");
firstSaveId.Value = firstSaveUserId;
record.Attributes.Append(firstSaveId);
}
if (!string.IsNullOrEmpty(lastSaveUserId))
{
XmlAttribute lastSaveId = xmlDataPackage.CreateAttribute("LastSaveUserId");
lastSaveId.Value = lastSaveUserId;
record.Attributes.Append(lastSaveId);
}
if (firstSaveTime.HasValue)
{
XmlAttribute firstSaveDateTime = xmlDataPackage.CreateAttribute("FirstSaveTime");
firstSaveDateTime.Value = firstSaveTime.Value.Ticks.ToString();
record.Attributes.Append(firstSaveDateTime);
}
if (lastSaveTime.HasValue)
{
XmlAttribute lastSaveDateTime = xmlDataPackage.CreateAttribute("LastSaveTime");
lastSaveDateTime.Value = lastSaveTime.Value.Ticks.ToString();
record.Attributes.Append(lastSaveDateTime);
}
IdList.Add(guid, record);
totalRecords++;
ExportInfo.TotalRecordsPackaged++;
ExportInfo.RecordsPackaged[form]++;
}
}
}
}
totalRecords = totalRecords * form.Pages.Count;
int processedRecords = 0;
if (StatusChanged != null)
{
StatusChanged(string.Format(PackagerStrings.ADDING_FIELD_DATA, form.Name));
}
foreach (Page page in form.Pages)
{
using (IDataReader reader = SourceProject.CollectedData.GetDatabase().GetTableDataReader(page.TableName))
{
while (reader.Read())
{
string guid = reader["GlobalRecordId"].ToString();
if (IdList.ContainsKey(guid))
{
XmlElement element = IdList[guid];
foreach (Field field in page.Fields)
{
if (field is IDataField && field is RenderableField && !(field is GridField) && !(FieldsToNull.ContainsKey(form.Name) && FieldsToNull[form.Name].Contains(field.Name)))
{
RenderableField renderableField = field as RenderableField;
if (renderableField != null)
{
XmlElement fieldData = xmlDataPackage.CreateElement("Field");
XmlAttribute name = xmlDataPackage.CreateAttribute("Name");
name.Value = renderableField.Name;
fieldData.Attributes.Append(name);
string value = reader[field.Name].ToString();
if (!string.IsNullOrEmpty(value))
{
if (field is DateTimeField)
{
DateTime dt = Convert.ToDateTime(value);
fieldData.InnerText = dt.Ticks.ToString();
}
else if (field is ImageField)
{
value = Convert.ToBase64String((Byte[])reader[field.Name]);
fieldData.InnerText = value;
}
else
{
fieldData.InnerText = value;
}
}
if (IncludeNullFieldData == false && String.IsNullOrEmpty(fieldData.InnerText))
{
// do nothing, for now...
}
else
{
element.AppendChild(fieldData);
}
data.AppendChild(element);
}
}
}
}
processedRecords++;
double progress = (((double)processedRecords) / ((double)totalRecords)) * 100;
if (UpdateProgress != null)
{
UpdateProgress(progress);
}
}
}
}
foreach (GridField gridField in form.Fields.GridFields)
{
data.AppendChild(CreateXmlGridElement(xmlDataPackage, form, gridField));
ExportInfo.GridsProcessed++;
}
return(data);
}
/// <summary>
/// Creates an XmlElement representing an Epi Info 7 view's data.
/// </summary>
/// <param name="xmlDataPackage">The data package xml document that the XmlElement should be added to</param>
/// <param name="form">The form whose data will be serialized</param>
/// <returns>XmlElement; represents the form's data in Xml format, suitable for use in data packaging</returns>
protected override XmlElement CreateXmlFormDataElement(XmlDocument xmlDataPackage, View form)
{
#region Input Validation
if (xmlDataPackage == null)
{
throw new ArgumentNullException("xmlDataPackage");
}
if (form == null)
{
throw new ArgumentNullException("form");
}
#endregion // Input Validation
XmlElement data = xmlDataPackage.CreateElement("Data");
OnStatusChanged(String.Format("Packaging data for form {0}...", form.Name));
OnResetProgress();
/* This seems like an usual set of steps to just iterate over the data. The problem is that we can't "just
* iterate over the data" - the data is split up page tables, with one table representing one page on the
* form. While a JOIN might be able to bring everything together into one table, it might not - for example,
* if there are >255 fields after the JOIN, an OleDb exception will be thrown.
*
* To get around this issue: The code first iterates over the rows in the BASE TABLE, obtaining the GUID
* values for each. The GUIDs and their corresponding XmlElement go into a dictionary.
*
* Later, each row in each page is iterated over; as the GUIDs for each page table are accessed, the corresponding
* XmlElement is pulled from the dictionary. Field data is added to it for each field that has data. In this
* manner, it doesn't matter that each row is technically accessed out-of-order because they'll still show up
* in-order in the resulting Xml.
*
* Filtering adds another layer of complexity. To filter, a JOIN operation is needed so that the filters can
* be applied across all those tables, since the fields in the filter may be across different tables. The
* RowFilter class provides a way to handle this; we simply get the query from that object and apply it to the
* reader. Only GUIDs that match the filter are added to the dictionary of guids.
*/
// We need to exclude records from child forms that may now be orphaned as a result of a filter applied to the parent
if (form.IsRelatedView && PreviousDistanceFromRoot < CurrentDistanceFromRoot)
{
ParentIdList.Clear();
foreach (KeyValuePair <string, XmlElement> kvp in IdList)
{
ParentIdList.Add(kvp.Key);
}
}
IdList.Clear(); // Very important, this needs to be re-set in case we've already processed a form (this is a class level variable)
if (!ExportInfo.RecordsPackaged.ContainsKey(form))
{
ExportInfo.RecordsPackaged.Add(form, 0);
}
//bool filterThisForm = false;
RowFilters filters = null;
Query selectQuery = null;
IDbDriver db = SourceProject.CollectedData.GetDatabase();
string recStatusClause = String.Empty;
if (Filters != null && Filters.ContainsKey(form.Name) && Filters[form.Name].Count() > 0)
{
//filterThisForm = true;
filters = Filters[form.Name];
filters.RecordProcessingScope = RecordProcessingScope;
selectQuery = filters.GetGuidSelectQuery(form);
List <QueryParameter> paramsToAdd = selectQuery.Parameters;
selectQuery = db.CreateQuery(selectQuery.SqlStatement.Replace("[t].[GlobalRecordId], [t].[FKEY], [t].[RECSTATUS]", "*"));
selectQuery.Parameters = paramsToAdd;
}
else
{
recStatusClause = "RECSTATUS = 1";
if (RecordProcessingScope == Epi.RecordProcessingScope.Both)
{
recStatusClause = "RECSTATUS >= 0";
}
else if (RecordProcessingScope == Epi.RecordProcessingScope.Deleted)
{
recStatusClause = "RECSTATUS = 0";
}
string selectQueryText = "SELECT * " + form.FromViewSQL;
selectQueryText = "SELECT * " + form.FromViewSQL + " WHERE " + recStatusClause;
selectQuery = db.CreateQuery(selectQueryText);
}
double totalRecords = Convert.ToDouble(db.ExecuteScalar(db.CreateQuery("SELECT COUNT(*) FROM " + form.TableName)));
var fieldInclusionList = new List <RenderableField>();
foreach (Field field in form.Fields)
{
if (field is IDataField && field is RenderableField && !(field is GridField) && !(FieldsToNull.ContainsKey(form.Name) && FieldsToNull[form.Name].Contains(field.Name)))
{
var fieldToAdd = field as RenderableField;
if (fieldToAdd != null)
{
fieldInclusionList.Add(fieldToAdd);
}
}
}
int processedRecords = 0;
//using (IDataReader guidReader = db.ExecuteReader(selectQuery))
//using (IDataReader guidReader = filterThisForm ? db.ExecuteReader(selectQuery) : db.GetTableDataReader(form.TableName))
DataTable fullTable = db.Select(selectQuery);
//int lowKey = (int)db.ExecuteScalar(db.CreateQuery("SELECT Min(UniqueKey) FROM " + form.TableName));
//int highKey = (int)db.ExecuteScalar(db.CreateQuery("SELECT Max(UniqueKey) FROM " + form.TableName));
////ProcessRows(fullTable.Select("UniqueKey >= " + lowKey + " AND UniqueKey <= " + (highKey / 4)), form, xmlDataPackage, fieldInclusionList);
string set1 = String.Empty;
//string set2 = String.Empty;
//string set3 = String.Empty;
//string set4 = String.Empty;
//Parallel.Invoke(
// () =>
// {
set1 = ProcessRows(fullTable.Rows, form, xmlDataPackage, fieldInclusionList);
//},
//() =>
//{
// set2 = ProcessRows(fullTable.Select("UniqueKey >= " + (highKey / 4) + " AND UniqueKey < " + (highKey / 2)), form, xmlDataPackage, fieldInclusionList);
//},
//() =>
//{
// set3 = ProcessRows(fullTable.Select("UniqueKey >= " + (highKey / 2) + " AND UniqueKey < " + (highKey / 1.5)), form, xmlDataPackage, fieldInclusionList);
//},
//() =>
//{
// set4 = ProcessRows(fullTable.Select("UniqueKey >= " + (highKey / 1.5) + " AND UniqueKey <= " + highKey), form, xmlDataPackage, fieldInclusionList);
//}
//);
//StringBuilder sb = new StringBuilder();
//foreach (XmlElement element in set1)
//{
// sb.Append(element.OuterXml);
// //data.AppendChild(element);
//}
//foreach (XmlElement element in set2)
//{
// sb.Append(element.OuterXml);
// //data.AppendChild(element);
//}
//foreach (XmlElement element in set3)
//{
// sb.Append(element.OuterXml);
// //data.AppendChild(element);
//}
//foreach (XmlElement element in set4)
//{
// sb.Append(element.OuterXml);
// //data.AppendChild(element);
//}
data.InnerText = set1;
return(data);
foreach (DataRow guidReader in fullTable.Rows)
{
//using(var conn = new System.Data.SqlClient.SqlConnection(db.ConnectionString + ";Connection Timeout=10"))
//{
// conn.Open();
// using (var selectCommand = new System.Data.SqlClient.SqlCommand(selectQueryText, conn))
// {
// using (var guidReader = selectCommand.ExecuteReader())
// {
// while (guidReader.Read())
// {
string guid = guidReader["GlobalRecordId"].ToString();// guidReader.GetString(0); // guidReader["GlobalRecordId"].ToString();
string fkey = guidReader["FKEY"].ToString();
string recstatus = guidReader["RECSTATUS"].ToString();
string firstSaveUserId = String.Empty;
DateTime?firstSaveTime = null;
string lastSaveUserId = String.Empty;
DateTime?lastSaveTime = null;
firstSaveUserId = guidReader["FirstSaveLogonName"].ToString();
if (guidReader["FirstSaveTime"] != DBNull.Value)
{
firstSaveTime = (DateTime)guidReader["FirstSaveTime"];
}
lastSaveUserId = guidReader["LastSaveLogonName"].ToString();
if (guidReader["LastSaveTime"] != DBNull.Value)
{
lastSaveTime = (DateTime)guidReader["LastSaveTime"];
}
if (!form.IsRelatedView || ParentIdList.Contains(fkey))
{
XmlElement record = xmlDataPackage.CreateElement("Record");
XmlAttribute id = xmlDataPackage.CreateAttribute("Id");
id.Value = guid;
record.Attributes.Append(id);
if (!string.IsNullOrEmpty(fkey))
{
XmlAttribute foreignKey = xmlDataPackage.CreateAttribute("Fkey");
foreignKey.Value = fkey;
record.Attributes.Append(foreignKey);
}
if (!string.IsNullOrEmpty(firstSaveUserId))
{
XmlAttribute firstSaveId = xmlDataPackage.CreateAttribute("FirstSaveUserId");
firstSaveId.Value = firstSaveUserId;
record.Attributes.Append(firstSaveId);
}
if (!string.IsNullOrEmpty(lastSaveUserId))
{
XmlAttribute lastSaveId = xmlDataPackage.CreateAttribute("LastSaveUserId");
lastSaveId.Value = lastSaveUserId;
record.Attributes.Append(lastSaveId);
}
if (firstSaveTime.HasValue)
{
XmlAttribute firstSaveDateTime = xmlDataPackage.CreateAttribute("FirstSaveTime");
firstSaveDateTime.Value = firstSaveTime.Value.Ticks.ToString();
record.Attributes.Append(firstSaveDateTime);
}
if (lastSaveTime.HasValue)
{
XmlAttribute lastSaveDateTime = xmlDataPackage.CreateAttribute("LastSaveTime");
lastSaveDateTime.Value = lastSaveTime.Value.Ticks.ToString();
record.Attributes.Append(lastSaveDateTime);
}
if (!String.IsNullOrEmpty(recstatus))
{
XmlAttribute recStatusAttribute = xmlDataPackage.CreateAttribute("RecStatus");
recStatusAttribute.Value = recstatus;
record.Attributes.Append(recStatusAttribute);
}
IdList.Add(guid, record);
ExportInfo.TotalRecordsPackaged++;
ExportInfo.RecordsPackaged[form]++;
foreach (RenderableField field in fieldInclusionList)
{
XmlElement fieldData = xmlDataPackage.CreateElement("Field");
XmlAttribute name = xmlDataPackage.CreateAttribute("Name");
name.Value = field.Name;
fieldData.Attributes.Append(name);
string value = guidReader[field.Name].ToString();
if (!String.IsNullOrEmpty(value))
{
if (field is DateTimeField)
{
DateTime dt = Convert.ToDateTime(value);
fieldData.InnerText = dt.Ticks.ToString();
}
else if (field is ImageField)
{
value = Convert.ToBase64String((Byte[])guidReader[field.Name]);
fieldData.InnerText = value;
}
else if (field is NumberField)
{
value = Convert.ToDouble(value).ToString(System.Globalization.CultureInfo.InvariantCulture);
fieldData.InnerText = value;
}
else
{
fieldData.InnerText = value;
}
}
if (String.IsNullOrEmpty(fieldData.InnerText) && IncludeNullFieldData == false)
{
// do nothing, for now...
}
else
{
record.AppendChild(fieldData);
}
data.AppendChild(record);
}
}
processedRecords++;
double progress = (((double)processedRecords) / ((double)totalRecords)) * 100;
OnProgressChanged(progress);
}
foreach (GridField gridField in form.Fields.GridFields)
{
data.AppendChild(CreateXmlGridElement(xmlDataPackage, form, gridField));
ExportInfo.GridsProcessed++;
}
return(data);
}
