/// <summary>
/// Generates command test
/// </summary>
protected override void GenerateCommand()
{
WordBuilder command = new WordBuilder();
command.Append((CommandNames.SELECT).ToUpper());
command.Append(txtSelectCriteria.Text.Trim());
CommandText = command.ToString() ;
}
/// <summary>
/// Generates command text
/// </summary>
protected override void GenerateCommand()
{
WordBuilder command = new WordBuilder();
command.Append(CommandNames.SUMMARIZE);
//Append aggregates
foreach (string item in lbxVar.Items)
{
command.Append(item);
}
//Append output table
command.Append(CommandNames.TO);
command.Append(txtOutput.Text.Trim());
//Append strata variables
if (lbxGroupBy.Items.Count > 0)
{
command.Append(CommandNames.STRATAVAR);
command.Append(StringLiterals.EQUAL);
foreach (string item in lbxGroupBy.Items)
{
command.Append(item);
}
}
//Append weighted variables
if (cmbWeight.SelectedItem != null && !string.IsNullOrEmpty(cmbWeight.SelectedItem.ToString()))
{
command.Append(CommandNames.WEIGHTVAR);
command.Append(StringLiterals.EQUAL);
command.Append(cmbWeight.SelectedItem.ToString());
}
CommandText = command.ToString();
}
/// <summary>
/// Method that generates the command
/// </summary>
protected override void GenerateCommand()
{
string expression = txtRecAffected.Text.Trim();
#region preconditions
if (expression.Trim() != "*" && !ValidExpression(expression)) //dcs0 8/1/2008
{
throw new GeneralException(string.Format(SharedStrings.INVALID_EXPRESSION, expression));
}
#endregion Preconditions
WordBuilder command = new WordBuilder();
command.Append(CommandNames.UNDELETE);
//command.Append(txtRecAffected.Text.Trim());
command.Append(expression); // dcs0 9/17/2008
if (cbkRunSilent.Checked)
{
command.Append(CommandNames.RUNSILENT);
}
CommandText = command.ToString();
try
{
this.EpiInterpreter.Parse(CommandText);
}
catch (Exception ex)
{
throw new GeneralException(string.Format(SharedStrings.INVALID_EXPRESSION, expression));
}
}
/// <summary>
/// Generates the command text
/// </summary>
protected override void GenerateCommand()
{
WordBuilder command = new WordBuilder();
command.Append(CommandNames.LIST);
if (lbxVariables.Items.Count > 0)
{
if (cbxAllExcept.Checked)
{
command.Append(StringLiterals.STAR);
command.Append(StringLiterals.SPACE);
command.Append(CommandNames.EXCEPT);
}
foreach (string item in lbxVariables.Items)
{
command.Append(FieldNameNeedsBrackets(item) ? Util.InsertInSquareBrackets(item) : item);
}
}
else
{
command.Append(cmbVar.Text);
}
if (WinUtil.GetSelectedRadioButton(gbxDisplayMode) == rdbGrid)
{
command.Append(CommandNames.GRIDTABLE);
}
else if (WinUtil.GetSelectedRadioButton(gbxDisplayMode) == rdbAllowUpdates)
{
command.Append(CommandNames.UPDATE);
}
CommandText = command.ToString();
}
/// <summary>
/// Gets a count of records in a column
/// </summary>
/// <param name="columnName">Name of the column</param>
/// <returns>Number of records in a column</returns>
public int GetRecordCount(Rule_Context pContext, string columnName)
{
#region Input Validation
if (string.IsNullOrEmpty(columnName))
{
throw new ArgumentNullException("columnName");
}
#endregion Input Validation
WordBuilder queryBuilder = new WordBuilder();
queryBuilder.Append("select count" + Util.InsertInParantheses(columnName));
queryBuilder.Append(GetSqlStatementPartFrom(pContext));
if (Db.ColumnExists(PrimaryTable.TableName, ColumnNames.REC_STATUS))
{
string whereStatement = GetSqlStatementPartWhere();
queryBuilder.Append(whereStatement);
}
else if (!string.IsNullOrEmpty(selectCriteria))
{
string whereClause = " where " + this.selectCriteria;
queryBuilder.Append(whereClause);
}
Query query = Db.CreateQuery(queryBuilder.ToString());
//recast to int before return to remove cast run time error
int result = Int32.Parse((Db.ExecuteScalar(query)).ToString());
return(result);
}
/// <summary>
/// Generates command test
/// </summary>
protected override void GenerateCommand()
{
WordBuilder command = new WordBuilder();
command.Append((CommandNames.SELECT).ToUpperInvariant());
command.Append(txtSelectCriteria.Text.Trim());
CommandText = command.ToString();
}
/// <summary>
/// Returns contents of a table.
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnNames">List of column names to select. Column names should not be bracketed; this method will add brackets.</param>
/// <returns>DataTable</returns>
public virtual DataTable GetTableData(string tableName, List <string> columnNames)
{
WordBuilder wb = new WordBuilder(",");
foreach (string s in columnNames)
{
wb.Add(this.InsertInEscape(s));
}
return(GetTableData(tableName, wb.ToString(), string.Empty));
}
/// <summary>
/// Generates command text
/// </summary>
protected override void GenerateCommand()
{
WordBuilder command = new WordBuilder();
command.Append(CommandNames.DEFINE);
command.Append(txtGroupVar.Text.Trim());
command.Append(CommandNames.GROUPVAR);
foreach (string s in lbxVariables.SelectedItems)
{
command.Append(s);
}
CommandText = command.ToString();
}
/// <summary>
/// Generates command text
/// </summary>
protected override void GenerateCommand()
{
WordBuilder command = new WordBuilder();
command.Append(CommandNames.DEFINE);
command.Append(txtGroupVar.Text.Trim());
command.Append(CommandNames.GROUPVAR);
foreach (string s in lbxVariables.SelectedItems)
{
command.Append(s);
}
CommandText = command.ToString();
}
/// <summary>
/// Generates the command text
/// </summary>
protected override void GenerateCommand()
{
StringBuilder sb = new StringBuilder(CommandNames.HEADER);
sb.Append(StringLiterals.SPACE);
sb.Append(1.ToString());
sb.Append(StringLiterals.SPACE);
if (true)
{
sb.Append(Util.InsertInDoubleQuotes(txtTitle.Text));
WordBuilder wb = new WordBuilder(StringLiterals.COMMA);
if (boldToolStripButton.Checked || italicToolStripButton.Checked || underlineToolStripButton.Checked)
{
sb.Append(StringLiterals.SPACE);
if (boldToolStripButton.Checked)
{
wb.Append("BOLD");
}
if (italicToolStripButton.Checked)
{
wb.Append("ITALIC");
}
if (underlineToolStripButton.Checked)
{
wb.Append("UNDERLINE");
}
sb.Append(Util.InsertInParantheses(wb.ToString()));
}
if (!string.IsNullOrEmpty(textFontColorName) && !string.IsNullOrEmpty(fontSizeToolStripDDL.Text))
{
sb.Append(" TEXTFONT ");
if (!string.IsNullOrEmpty(textFontColorName))
{
sb.Append(textFontColorName.ToUpperInvariant());
}
if (!string.IsNullOrEmpty(fontSizeToolStripDDL.Text))
{
sb.Append(StringLiterals.SPACE);
sb.Append(fontSizeToolStripDDL.Text);
}
}
}
CommandText = sb.ToString();
}
/// <summary>
/// Generate the command text
/// </summary>
protected override void GenerateCommand()
{
WordBuilder command = new WordBuilder();
command.Append(CommandNames.SORT);
foreach (string item in lbxSortVar.Items)
{
command.Append(item.Substring(0, item.Length - 4));;
//Append 'DESCENDING'
if (item.EndsWith(StringLiterals.HYPHEN + StringLiterals.PARANTHESES_CLOSE))
{
command.Append(CommandNames.DESCENDING);
}
}
CommandText = command.ToString();
}
/// <summary>
/// Generate the command text
/// </summary>
protected override void GenerateCommand()
{
WordBuilder command = new WordBuilder();
command.Append(CommandNames.SORT);
foreach (string item in lbxSortVar.Items)
{
command.Append(item.Substring(0,item.Length - 4));;
//Append 'DESCENDING'
if (item.EndsWith(StringLiterals.HYPHEN + StringLiterals.PARANTHESES_CLOSE))
{
command.Append(CommandNames.DESCENDING);
}
}
CommandText = command.ToString();
}
/// <summary>
/// Generates command text
/// </summary>
protected override void GenerateCommand()
{
string expression = txtRecAffected.Text.Trim();
#region preconditions
if (expression.Trim() != "*" && !ValidExpression(expression)) //dcs0 8/1/2008
{
MessageBox.Show(string.Format(SharedStrings.INVALID_EXPRESSION, expression));
CommandText = "";
return;
}
if (EpiInterpreter.Context.CurrentRead.File.Contains(".xlsx") || EpiInterpreter.Context.CurrentRead.File.Contains(".xls") || EpiInterpreter.Context.CurrentRead.File.Contains("FMT=Delimited"))
{
MessageBox.Show("Deletion not supported for this datasource", "DELETE RECORDS");
Close();
throw new GeneralException(string.Format(SharedStrings.INVALID_DATA_SOURCE, "Excel"));
}
#endregion Preconditions
WordBuilder command = new WordBuilder();
command.Append(CommandNames.DELETE);
if (expression.Trim() != "*")
{
command.Append("(");
}
command.Append(expression);
if (expression.Trim() != "*")
{
command.Append(")");
}
if (rdbPermDeletion.Checked)
{
command.Append(CommandNames.PERMANENT);
}
if (cbkRunSilent.Checked)
{
command.Append(CommandNames.RUNSILENT);
}
CommandText = command.ToString();
}
/// <summary>
/// Generates command text
/// </summary>
protected override void GenerateCommand()
{
WordBuilder command = new WordBuilder();
command.Append(CommandNames.ASSIGN);
command.Append(cmbAssignVar.Text);
command.Append("=");
// Fix for defect #20 - prevent the resulting command from having to = signs if the user supplies one by accident.
string expression = txtExpression.Text.Trim();
if (expression.Length > 0)
{
if (expression[0] == '=')
{
expression = expression.Remove(0, 1);
expression = expression.Trim();
}
}
command.Append(expression);
CommandText = command.ToString();
}
/// <summary>
/// AboutEpiInfo load event
/// </summary>
/// <param name="sender">Object that fired the event.</param>
/// <param name="e">.NET supplied event args.</param>
private void AboutEpiInfo_Load(object sender, System.EventArgs e)
{
try
{
ApplicationIdentity appId = new ApplicationIdentity(typeof(Configuration).Assembly);
// App Name
WordBuilder appNameBuilder = new WordBuilder();
appNameBuilder.Add(appId.SuiteName);
appNameBuilder.Add(appId.Version);
lblAppName.Text = appNameBuilder.ToString();
// Release date
lblReleaseDate.Text = appId.VersionReleaseDate;
// Description
txtDescription.Text = Util.GetProductDescription();
}
catch (FileNotFoundException ex)
{
MsgBox.ShowException(ex);
}
}
/// <summary>
/// AboutEpiInfo load event
/// </summary>
/// <param name="sender">Object that fired the event.</param>
/// <param name="e">.NET supplied event args.</param>
private void AboutEpiInfo_Load(object sender, System.EventArgs e)
{
try
{
ApplicationIdentity appId = new ApplicationIdentity(typeof(Configuration).Assembly);
// App Name
WordBuilder appNameBuilder = new WordBuilder();
appNameBuilder.Add("Epi Info\u2122");
appNameBuilder.Add(appId.Version);
lblAppName.Text = appNameBuilder.ToString();
// Release date
lblReleaseDate.Text = appId.VersionReleaseDate;
// Description
txtDescription.Text = Util.GetProductDescription();
}
catch (FileNotFoundException ex)
{
MsgBox.ShowException(ex);
}
}
/// <summary>
/// Generates command text
/// </summary>
protected override void GenerateCommand()
{
WordBuilder command = new WordBuilder();
command.Append(CommandNames.WRITE);
command.Append((rdbReplace.Checked) ? CommandNames.REPLACE : CommandNames.APPEND);
if (cmbOutputFormat.Text.ToUpperInvariant() == "TEXT" || cmbOutputFormat.Text.ToUpperInvariant() == "FLAT ASCII FILE")
{
command.Append("\"TEXT\"");
}
else
{
command.Append("\"Epi7\"");
}
command.Append("{" + txtFileName.Text.Trim() + "}");
command.Append(":");
command.Append(FieldNameNeedsBrackets(cmbDataTable.Text) ? Util.InsertInSquareBrackets(cmbDataTable.Text) : cmbDataTable.Text);
if (lbxVariables.SelectedItems.Count < 1)
{
command.Append(StringLiterals.STAR);
}
else
{
if (cbxAllExcept.Checked)
{
command.Append("* EXCEPT");
}
foreach (string s in lbxVariables.SelectedItems)
{
command.Append(FieldNameNeedsBrackets(s) ? Util.InsertInSquareBrackets(s) : s);
}
}
CommandText = command.ToString();
}
/// <summary>
/// Handles the Click event of the OK button
/// </summary>
/// <param name="sender">Object that fired the event</param>
/// <param name="e">.NET supplied event parameters</param>
private void btnOk_Click(object sender, System.EventArgs e)
{
//Output = "ASSIGN " + cbxAssignVariable.SelectedItem.ToString() + " = " + txtAssignment.Text;
WordBuilder command = new WordBuilder();
command.Append(CommandNames.ASSIGN);
command.Append(cbxAssignVariable.SelectedItem.ToString());
command.Append("=");
// Fix for defect #140 - prevent the resulting command from having two = signs if the user supplies one by accident.
string expression = txtAssignment.Text.Trim();
if (expression.Length > 0)
{
if (expression[0] == '=')
{
expression = expression.Remove(0, 1);
expression = expression.Trim();
}
}
command.Append(expression);
Output = command.ToString();
this.DialogResult = DialogResult.OK;
this.Hide();
}
/// <summary>
/// Processes all of the fields on a given form, page-by-page, except for the fields on the base table.
/// </summary>
/// <param name="sourceView">The source form</param>
/// <param name="destinationView">The destination form</param>
/// <param name="destinationGUIDList">The list of GUIDs that exist in the destination</param>
private void ProcessPages(View sourceView, View destinationView, List<string> destinationGUIDList)
{
for (int i = 0; i < sourceView.Pages.Count; i++)
{
sourceView.LoadFirstRecord();
OnAddStatusMessage(string.Format(ImportExportSharedStrings.PROCESSING_PAGE, (i + 1).ToString(), sourceView.Pages.Count.ToString()));
int recordsInserted = 0;
int recordsUpdated = 0;
Page sourcePage = sourceView.Pages[i];
Page destinationPage = destinationView.Pages[i];
try
{
List<string> fieldsToSkip = new List<string>();
foreach (Field sourceField in sourceView.Fields)
{
bool found = false;
foreach (Field destinationField in destinationView.Fields)
{
if (destinationField.Name.ToLower().Equals(sourceField.Name.ToLower()))
{
found = true;
}
}
if (!found)
{
fieldsToSkip.Add(sourceField.Name);
}
}
if (ColumnsToNull != null && ColumnsToNull.ContainsKey(sourceView.Name))
{
List<string> toNull = ColumnsToNull[sourceView.Name];
foreach (string s in toNull)
{
if (!fieldsToSkip.Contains(s))
{
fieldsToSkip.Add(s);
}
}
}
IDataReader sourceReader = sourceProjectDataDriver.GetTableDataReader(sourcePage.TableName);
while (sourceReader.Read())
{
//if (importWorker.CancellationPending)
//{
// this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "Import cancelled.");
// return;
//}
if (OnCheckForCancellation())
{
OnAddStatusMessage(ImportExportSharedStrings.IMPORT_CANCELLED);
sourceReader.Close();
sourceReader.Dispose();
return;
}
WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA);
WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA);
List<QueryParameter> fieldValueParams = new List<QueryParameter>();
string GUID = sourceReader["GlobalRecordId"].ToString();
if (sourceGUIDs != null && !sourceGUIDs.Contains(GUID))
{
continue;
}
if (destinationGUIDList.Contains(GUID) && update)
{
// UPDATE matching records
string updateHeader = string.Empty;
string whereClause = string.Empty;
fieldValueParams = new List<QueryParameter>();
StringBuilder sb = new StringBuilder();
int columnIndex = 0;
// 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);
int fieldsInQuery = 0;
// Now build the field update statements in 100 field chunks
foreach (RenderableField renderableField in sourcePage.Fields)
{
if (renderableField is GridField || renderableField is GroupField || renderableField is ImageField || fieldsToSkip.Contains(renderableField.Name)) // TODO: Someday, allow image fields
{
continue;
}
else if (renderableField is IDataField)
{
IDataField dataField = (IDataField)renderableField;
if (dataField.FieldType != MetaFieldType.UniqueKey && dataField is RenderableField)
{
columnIndex += 1;
//if (dataField.CurrentRecordValueObject == null)
if (sourceReader[renderableField.Name] == DBNull.Value || string.IsNullOrEmpty(sourceReader[renderableField.Name].ToString()))
{
//sb.Append(SqlKeyWords.NULL);
}
else
{
switch (dataField.FieldType)
{
case MetaFieldType.Date:
case MetaFieldType.DateTime:
case MetaFieldType.Time:
fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.DateTime, Convert.ToDateTime(sourceReader[renderableField.Name])));
break;
case MetaFieldType.Checkbox:
fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Boolean, Convert.ToBoolean(sourceReader[renderableField.Name])));
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("@" + renderableField.Name, DbType.String, sourceReader[renderableField.Name]));
break;
case MetaFieldType.Number:
case MetaFieldType.RecStatus:
case MetaFieldType.YesNo:
fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Single, sourceReader[renderableField.Name]));
break;
case MetaFieldType.GUID:
fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Guid, sourceReader[renderableField.Name]));
break;
case MetaFieldType.Option:
if (optionFieldsAsStrings.Contains(renderableField.Name))
{
fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.String, sourceReader[renderableField.Name]));
}
else
{
fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Int16, sourceReader[renderableField.Name]));
}
break;
case MetaFieldType.Image:
OnAddStatusMessage(string.Format(ImportExportSharedStrings.WARNING_FIELD_NOT_IMPORTED, renderableField.Name));
continue;
default:
throw new ApplicationException(ImportExportSharedStrings.UNRECOGNIZED_FIELD_TYPE);
}
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append(((Epi.INamedObject)dataField).Name);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sb.Append(StringLiterals.EQUAL);
sb.Append(StringLiterals.COMMERCIAL_AT);
sb.Append(((Epi.INamedObject)dataField).Name);
sb.Append(StringLiterals.COMMA);
}
}
if ((columnIndex % 100) == 0 && columnIndex > 0)
{
if (sb.ToString().LastIndexOf(StringLiterals.COMMA).Equals(sb.ToString().Length - 1))
{
sb.Remove(sb.ToString().LastIndexOf(StringLiterals.COMMA), 1);
}
Query updateQuery = destinationProjectDataDriver.CreateQuery(updateHeader + StringLiterals.SPACE + sb.ToString() + StringLiterals.SPACE + whereClause);
updateQuery.Parameters = fieldValueParams;
destinationProjectDataDriver.ExecuteNonQuery(updateQuery);
columnIndex = 0;
sb.Remove(0, sb.ToString().Length);
fieldValueParams.Clear();
}
}
fieldsInQuery++;
}
if (fieldsInQuery == 0)
{
continue;
}
if (sb.Length > 0)
{
if (sb.ToString().LastIndexOf(StringLiterals.COMMA).Equals(sb.ToString().Length - 1))
{
int startIndex = sb.ToString().LastIndexOf(StringLiterals.COMMA);
if (startIndex >= 0)
{
sb.Remove(startIndex, 1);
}
}
Query updateQuery = destinationProjectDataDriver.CreateQuery(updateHeader + StringLiterals.SPACE + sb.ToString() + StringLiterals.SPACE + whereClause);
updateQuery.Parameters = fieldValueParams;
destinationProjectDataDriver.ExecuteNonQuery(updateQuery);
columnIndex = 0;
sb.Remove(0, sb.ToString().Length);
fieldValueParams.Clear();
}
recordsUpdated++;
}
else if (!destinationGUIDList.Contains(GUID) && append)
{
fieldNames.Append("GlobalRecordId");
fieldValues.Append("@GlobalRecordId");
fieldValueParams.Add(new QueryParameter("@GlobalRecordId", DbType.String, GUID));
int fieldsInQuery = 0;
// INSERT unmatched records
foreach (RenderableField renderableField in sourcePage.Fields)
{
if (renderableField is GridField || renderableField is GroupField || fieldsToSkip.Contains(renderableField.Name))
{
continue;
}
else if (renderableField is IDataField)
{
IDataField dataField = (IDataField)renderableField;
if (dataField is UniqueKeyField)
{
continue;
}
else
{
if (sourceReader[renderableField.Name] == DBNull.Value || string.IsNullOrEmpty(sourceReader[renderableField.Name].ToString()))
//if (dataField.CurrentRecordValueObject == null)
{
//fieldValues.Append(" null "); // TODO: Check to make sure we shouldn't be using this
}
else
{
String fieldName = ((Epi.INamedObject)dataField).Name;
//fieldValueParams.Add(dataField.CurrentRecordValueAsQueryParameter);
switch (dataField.FieldType)
{
case MetaFieldType.Date:
case MetaFieldType.DateTime:
case MetaFieldType.Time:
fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.DateTime, Convert.ToDateTime(sourceReader[fieldName])));
break;
case MetaFieldType.Checkbox:
fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.Boolean, Convert.ToBoolean(sourceReader[fieldName])));
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, sourceReader[fieldName]));
break;
case MetaFieldType.Number:
case MetaFieldType.YesNo:
case MetaFieldType.RecStatus:
fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.Single, sourceReader[fieldName]));
break;
case MetaFieldType.GUID:
fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.Guid, sourceReader[fieldName]));
break;
case MetaFieldType.Option:
if (optionFieldsAsStrings.Contains(renderableField.Name))
{
fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.String, sourceReader[fieldName]));
}
else
{
fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Int16, sourceReader[fieldName]));
}
break;
case MetaFieldType.Image:
OnAddStatusMessage(string.Format(ImportExportSharedStrings.WARNING_FIELD_NOT_IMPORTED, renderableField.Name));
continue;
default:
throw new ApplicationException(ImportExportSharedStrings.UNRECOGNIZED_FIELD_TYPE);
}
fieldNames.Append(destinationProjectDataDriver.InsertInEscape(((Epi.INamedObject)dataField).Name));
fieldValues.Append("@" + fieldName);
}
}
}
fieldsInQuery++;
}
if (fieldsInQuery == 0)
{
continue;
}
// 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++;
}
//this.BeginInvoke(new SetProgressBarDelegate(IncrementProgressBarValue), 1);
OnSetProgress(1);
}
sourceReader.Close();
sourceReader.Dispose();
}
catch (Exception ex)
{
OnAddStatusMessage(string.Format(ImportExportSharedStrings.ERROR_WITH_MESSAGE, ex.Message));
}
finally
{
}
if (update && append)
{
OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_PAGE_UPDATED_AND_APPENDED, destinationPage.Name, recordsInserted.ToString(), recordsUpdated.ToString()));
}
else if (update && !append)
{
OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_PAGE_UPDATED, destinationPage.Name, recordsUpdated.ToString()));
}
else if (!update && append)
{
OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_PAGE_APPENDED, destinationPage.Name, recordsInserted.ToString()));
}
}
}
/// <summary>
/// Initiates an export of the data to the specified file.
/// </summary>
public override void Export()
{
DataView dv = this.DataView;
DataTable table = new DataTable(); // dv.ToTable(false);
WordBuilder wb = new WordBuilder(SEPARATOR);
StreamWriter sw = null;
if (IncludeDeletedRecords == false)
{
if (table.Columns.Contains("RecStatus") || table.Columns.Contains("RECSTATUS"))
{
dv.RowFilter = "[RecStatus] > 0";
}
}
try
{
sw = File.CreateText(fileName);
if (ColumnSortOrder != ImportExport.ColumnSortOrder.None || !exportAllFields)
{
table = dv.ToTable(false);
if (view != null)
{
ImportExportHelper.OrderColumns(table, ColumnSortOrder, view);
}
else
{
ImportExportHelper.OrderColumns(table, ColumnSortOrder);
}
List<DataColumn> columnsToRemove = new List<DataColumn>();
foreach (DataColumn dc in table.Columns)
{
bool found = false;
foreach (Epi.Data.TableColumn tc in columnList)
{
if (tc.Name.Equals(dc.ColumnName))
{
found = true;
break;
}
}
if (!found)
{
columnsToRemove.Add(dc);
}
}
foreach (DataColumn dc in columnsToRemove)
{
table.Columns.Remove(dc);
}
}
foreach (DataColumn dc in table.Columns)
{
wb.Add(dc.ColumnName);
}
sw.WriteLine(wb.ToString());
rowsExported = 0;
int totalRows = 0;
//if (useTabOrder || !exportAllFields)
//{
totalRows = table.Rows.Count;
foreach (DataRow row in table.Rows)
{
wb = new WordBuilder(SEPARATOR);
for (int i = 0; i < table.Columns.Count; i++)
{
string rowValue = row[i].ToString().Replace("\r\n", " ");
if (rowValue.Contains(",") || rowValue.Contains("\""))
{
rowValue = rowValue.Replace("\"", "\"\"");
rowValue = Util.InsertIn(rowValue, "\"");
}
wb.Add(rowValue);
}
sw.WriteLine(wb);
rowsExported++;
if (rowsExported % 500 == 0)
{
//this.Dispatcher.BeginInvoke(new SetGadgetStatusHandler(RequestUpdateStatusMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
//RequestUpdateStatusMessage(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
//SetProgressAndStatus(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
OnSetStatusMessageAndProgressCount(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
}
}
//}
//else
//{
// totalRows = dv.Count;
// foreach (DataRowView rowView in dv)
// {
// wb = new WordBuilder(SEPARATOR);
// for (int i = 0; i < table.Columns.Count; i++)
// {
// DataRow row = rowView.Row;
// string rowValue = row[i].ToString().Replace("\r\n", " ");
// if (rowValue.Contains(",") || rowValue.Contains("\""))
// {
// rowValue = rowValue.Replace("\"", "\"\"");
// rowValue = Util.InsertIn(rowValue, "\"");
// }
// wb.Add(rowValue);
// }
// sw.WriteLine(wb);
// rowsExported++;
// if (rowsExported % 500 == 0)
// {
// //this.Dispatcher.BeginInvoke(new SetGadgetStatusHandler(RequestUpdateStatusMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
// //RequestUpdateStatusMessage(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
// SetProgressAndStatus(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
// }
// }
//}
//this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetStatusMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS, rowsExported.ToString()));
OnSetStatusMessage(string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS, rowsExported.ToString()));
}
catch (Exception ex)
{
OnSetStatusMessage(ex.Message);
//this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), ex.Message);
}
finally
{
if (sw != null)
{
sw.Close();
sw.Dispose();
sw = null;
}
//stopWatch.Stop();
//System.Diagnostics.Debug.Print("File I/O Export thread finished in " + stopWatch.Elapsed.ToString());
}
}
/// <summary>
/// Initiates an export of the data to the specified file.
/// </summary>
public override void Export()
{
OnSetStatusMessage(ImportExportSharedStrings.WEB_CSV_EXPORT_CONNECTING);
rowsExported = 0;
WordBuilder wb = new WordBuilder(SEPARATOR);
StreamWriter sw = null;
SurveyManagerService.SurveyAnswerRequest Request = new SurveyManagerService.SurveyAnswerRequest();
Request.Criteria.SurveyId = surveyKey.ToString();
Request.Criteria.UserPublishKey = secToken;
Request.Criteria.OrganizationKey = orgKey;
Request.Criteria.ReturnSizeInfoOnly = true;
SurveyManagerService.SurveyAnswerResponse Result = client.GetSurveyAnswer(Request);
Pages = Result.NumberOfPages;
PageSize = Result.PageSize;
Request.Criteria.ReturnSizeInfoOnly = false;
int count = 0;
List <SurveyManagerService.SurveyAnswerResponse> Results = new List <SurveyManagerService.SurveyAnswerResponse>();
OnSetStatusMessage(ImportExportSharedStrings.WEB_CSV_EXPORT_BUILDING_COLUMN_HEADINGS);
for (int i = 1; i <= Pages; i++)
{
Request.Criteria.PageNumber = i;
Request.Criteria.PageSize = PageSize;
Result = client.GetSurveyAnswer(Request);
Results.Add(Result);
foreach (SurveyManagerService.SurveyAnswerDTO surveyAnswer in Result.SurveyResponseList)
{
if (surveyAnswer.Status == 3)
{
count++;
}
}
}
if (SetMaxProgressBarValue != null)
{
SetMaxProgressBarValue((double)count);
}
List <string> columnHeaders = new List <string>();
foreach (SurveyManagerService.SurveyAnswerResponse R in Results)
{
wfList = ParseXML(R);
foreach (WebFieldData wfData in wfList)
{
if (!columnHeaders.Contains(wfData.FieldName))
{
columnHeaders.Add(wfData.FieldName);
}
else
{
break;
}
}
break;
}
try
{
OnSetStatusMessage(ImportExportSharedStrings.WEB_CSV_EXPORTING);
sw = File.CreateText(fileName);
foreach (string s in columnHeaders)
{
wb.Add(s);
}
sw.WriteLine(wb.ToString());
rowsExported = 0;
foreach (SurveyManagerService.SurveyAnswerResponse R in Results)
{
string currentGUID = string.Empty;
string lastGUID = string.Empty;
wfList = ParseXML(R);
wb = new WordBuilder(SEPARATOR);
foreach (WebFieldData wfData in wfList)
{
currentGUID = wfData.RecordGUID;
if (!string.IsNullOrEmpty(currentGUID) && !string.IsNullOrEmpty(lastGUID) && currentGUID != lastGUID)
{
sw.WriteLine(wb.ToString());
wb = new WordBuilder(SEPARATOR);
OnSetStatusMessageAndProgressCount("", 1);
}
string rowValue = wfData.FieldValue.ToString().Replace("\r\n", " ");
if (rowValue.Contains(",") || rowValue.Contains("\""))
{
rowValue = rowValue.Replace("\"", "\"\"");
rowValue = Util.InsertIn(rowValue, "\"");
}
wb.Add(rowValue);
lastGUID = wfData.RecordGUID;
}
sw.WriteLine(wb.ToString());
}
OnSetStatusMessage(ImportExportSharedStrings.WEB_CSV_EXPORT_COMPLETE);
if (FinishExport != null)
{
FinishExport();
}
}
catch (Exception ex)
{
OnSetStatusMessage(ex.Message);
if (ExportFailed != null)
{
ExportFailed(ex.Message);
}
}
finally
{
// Clean up
if (sw != null)
{
sw.Close();
sw.Dispose();
sw = null;
}
}
}
private void btnOK_Click(object sender, EventArgs e)
{
KeyFields = new List <Field>();
if (lbxFields.SelectedItems.Count == 0)
{
return;
}
try
{
#region Check #1 - Make sure key is unique on parent form
IDbDriver db = Project.CollectedData.GetDatabase();
Query selectQuery = db.CreateQuery("SELECT Count(*) FROM [" + Form.TableName + "]");
int recordCount = (int)db.ExecuteScalar(selectQuery);
WordBuilder wb = new WordBuilder(",");
foreach (Field field in Form.Fields)
{
if (field is RenderableField && lbxFields.SelectedItems.Contains(field.Name))
{
wb.Add(field.Name);
}
}
selectQuery = db.CreateQuery("SELECT DISTINCT " + wb.ToString() + " " + Form.FromViewSQL);
int distinctCount = db.Select(selectQuery).Rows.Count; // probably better way to do this, but unsure if can be made generic... this query is most generic across DB types?
if (distinctCount == recordCount)
{
foreach (Field field in Form.Fields)
{
if (field is RenderableField && lbxFields.SelectedItems.Contains(field.Name))
{
KeyFields.Add(field);
}
}
}
else
{
if (lbxFields.SelectedItems.Count == 1)
{
Epi.Windows.MsgBox.ShowError(String.Format("The selected match key ({0}) is not unique.", lbxFields.SelectedItem.ToString()));
}
else if (lbxFields.SelectedItems.Count > 1)
{
WordBuilder keyFields = new WordBuilder(",");
foreach (string s in lbxFields.SelectedItems)
{
keyFields.Add(s);
}
Epi.Windows.MsgBox.ShowError(String.Format("The selected match key ({0}) is not unique.", keyFields.ToString()));
}
this.DialogResult = System.Windows.Forms.DialogResult.None;
return;
}
#endregion // Check #1 - Make sure key is unique on parent form
// Currently, disable match keys if related forms exist. TODO: Change this later?
foreach (View otherForm in Project.Views)
{
if (otherForm != Form && Epi.ImportExport.ImportExportHelper.IsFormDescendant(otherForm, Form))
{
Epi.Windows.MsgBox.ShowError("Custom match keys cannot be used to package a form that contains child forms.");
this.DialogResult = System.Windows.Forms.DialogResult.None;
return;
}
}
//#region Check #2 - Make sure key exists in other forms in the hierarchy and that it's the same field type
//foreach (View otherForm in Project.Views)
//{
// if (otherForm != Form && Epi.ImportExport.ImportExportHelper.IsFormDescendant(otherForm, Form))
// {
// foreach (Field field in KeyFields)
// {
// if (!otherForm.Fields.Contains(field.Name))
// {
// Epi.Windows.MsgBox.ShowError(
// String.Format(
// "The selected field {0} does not exist in the child form {1}. The keys selected in this dialog must exist across all child forms.",
// field.Name, otherForm.Name));
// this.DialogResult = System.Windows.Forms.DialogResult.None;
// return;
// }
// else
// {
// Field otherField = otherForm.Fields[field.Name];
// if (otherField.FieldType != field.FieldType)
// {
// Epi.Windows.MsgBox.ShowError(
// String.Format(
// "The selected field {0} is implemented as a different field type on child form {1}. The keys selected in this dialog must exist across all child forms and those fields must be of the same field type.",
// field.Name, otherForm.Name));
// this.DialogResult = System.Windows.Forms.DialogResult.None;
// return;
// }
// }
// }
// }
//}
//#endregion // Check #2 - Make sure key exists in other forms in the hierarchy and that it's the same field type
}
catch (Exception ex)
{
Epi.Windows.MsgBox.ShowException(ex);
this.DialogResult = System.Windows.Forms.DialogResult.None;
}
}
/// <summary>
/// Used to generate the list of variables and options for the GadgetParameters object
/// </summary>
protected override void CreateInputVariableList()
{
this.DataFilters = RowFilterControl.DataFilters;
Dictionary <string, string> inputVariableList = new Dictionary <string, string>();
Parameters.ColumnNames = new List <string>();
Parameters.SortVariables = new Dictionary <string, SortOrder>();
Parameters.GadgetTitle = txtTitle.Text;
Parameters.GadgetDescription = txtDesc.Text;
double height = 5000;
double width = 0;
int maxrows = 0;
int maxColumnLength = 0;
bool success = double.TryParse(txtMaxWidth.Text, out width);
if (success)
{
Parameters.Width = width;
}
success = int.TryParse(txtMaxRows.Text, out maxrows);
if (success)
{
Parameters.MaxRows = maxrows;
}
List <string> listFields = new List <string>();
List <string> numerFilterFields = new List <string>();
List <string> denomFilterFields = new List <string>();
if (Parameters.NumerFilter != null)
{
foreach (System.Data.DataRow numerRow in Parameters.NumerFilter.ConditionTable.Rows)
{
string[] fragments = (numerRow["filter"]).ToString().Split(new char[] { '[', ']' });
if (fragments.Length == 3)
{
numerFilterFields.Add(fragments[1]);
}
}
}
if (Parameters.DenomFilter != null)
{
foreach (System.Data.DataRow denomRow in Parameters.DenomFilter.ConditionTable.Rows)
{
string[] fragments = (denomRow["filter"]).ToString().Split(new char[] { '[', ']' });
if (fragments.Length == 3)
{
denomFilterFields.Add(fragments[1]);
}
}
}
numerFilterFields.AddRange(denomFilterFields.ToList <string>());
listFields = numerFilterFields;
listFields.Sort();
if ((Gadget as RatesControl).IsHostedByEnter)
{
if (listFields.Contains("UniqueKey"))
{
listFields.Remove("UniqueKey");
}
listFields.Add("UniqueKey");
}
foreach (string field in listFields)
{
Parameters.ColumnNames.Add(field);
}
Parameters.NumerDistinct = checkBoxNumberatorDistinct.IsChecked.Value;
Parameters.DenomDistinct = checkBoxDenominatorDistinct.IsChecked.Value;
if (lbxSortOrder.Items.Count > 0)
{
foreach (string item in lbxSortOrder.Items)
{
if (!string.IsNullOrEmpty(item))
{
string baseStr = item;
if (baseStr.EndsWith("(ascending)"))
{
baseStr = baseStr.Remove(baseStr.Length - 12);
Parameters.SortVariables.Add(baseStr, SortOrder.Ascending);
}
if (baseStr.EndsWith("(descending)"))
{
baseStr = baseStr.Remove(baseStr.Length - 13);
Parameters.SortVariables.Add(baseStr, SortOrder.Descending);
}
}
}
}
if (string.IsNullOrWhiteSpace(cmbRateMultiplier.Text) == false)
{
Parameters.RateMultiplierString = cmbRateMultiplier.Text;
}
else
{
Parameters.RateMultiplierString = "100";
}
if (cmbNumeratorField.SelectedIndex >= 0)
{
if (cmbNumeratorField.SelectedItem is RatesProperties.FieldInfo)
{
Parameters.NumeratorField = ((RatesProperties.FieldInfo)cmbNumeratorField.SelectedItem).Name;
}
}
if (cmbDenominatorField.SelectedIndex >= 0)
{
if (cmbDenominatorField.SelectedItem is RatesProperties.FieldInfo)
{
Parameters.DenominatorField = ((RatesProperties.FieldInfo)cmbDenominatorField.SelectedItem).Name;
}
}
if (string.IsNullOrWhiteSpace(Parameters.DenominatorField) == false)
{
Parameters.ColumnNames.Add(Parameters.DenominatorField);
}
if (string.IsNullOrWhiteSpace(Parameters.DenominatorField) == false)
{
Parameters.ColumnNames.Add(Parameters.DenominatorField);
}
if (cmbSelectNumeratorAggregateFunction.SelectedIndex >= 0)
{
Parameters.NumeratorAggregator = (string)((AggFxInfo)(cmbSelectNumeratorAggregateFunction.SelectedItem)).Keyword;
}
if (cmbSelectDenominatorAggregateFunction.SelectedIndex >= 0)
{
Parameters.DenominatorAggregator = (string)((AggFxInfo)(cmbSelectDenominatorAggregateFunction.SelectedItem)).Keyword;
}
if (cmbGroupField.SelectedIndex >= 0)
{
Parameters.PrimaryGroupField = cmbGroupField.SelectedItem.ToString();
}
if (cmbSecondaryGroupField.SelectedIndex >= 0)
{
Parameters.SecondaryGroupField = cmbSecondaryGroupField.SelectedItem.ToString();
}
if (StrataGridList.Count >= 1)
{
Grid grid = StrataGridList[0];
SortedDictionary <int, string> sortColumnDictionary = new SortedDictionary <int, string>();
foreach (UIElement element in grid.Children)
{
if (Grid.GetRow(element) == 0 && element is TextBlock)
{
TextBlock txtColumnName = element as TextBlock;
sortColumnDictionary.Add(Grid.GetColumn(element), txtColumnName.Text);
}
}
ColumnOrder = new List <string>();
foreach (KeyValuePair <int, string> kvp in sortColumnDictionary)
{
ColumnOrder.Add(kvp.Value);
}
if (ColumnOrder.Count == listFields.Count || ColumnOrder.Count == (listFields.Count + 1))
{
bool same = true;
foreach (string s in listFields)
{
if (!ColumnOrder.Contains(s))
{
same = false;
}
}
if (same)
{
WordBuilder wb = new WordBuilder("^");
foreach (string s in ColumnOrder)
{
wb.Add(s);
}
inputVariableList.Add("customusercolumnsort", wb.ToString());
}
else
{
ColumnOrder = new List <string>();
}
}
else
{
ColumnOrder = new List <string>();
}
}
Parameters.InputVariableList = inputVariableList;
Parameters.DefaultColor = rctColorDefault.Fill.ToString();
Parameters.UseDefaultColor = defaultColorOption.IsChecked == true;
double doubleValue = -1;
Parameters.Color_L1 = rctColor1.Fill.ToString();
success = double.TryParse(rampEnd01.Text, out doubleValue);
if (success)
{
Parameters.HighValue_L1 = doubleValue;
}
Parameters.Color_L2 = rctColor2.Fill.ToString();
success = double.TryParse(rampStart02.Text, out doubleValue);
if (success)
{
Parameters.LowValue_L2 = doubleValue;
}
success = double.TryParse(rampEnd02.Text, out doubleValue);
if (success)
{
Parameters.HighValue_L2 = doubleValue;
}
Parameters.Color_L3 = rctColor3.Fill.ToString();
success = double.TryParse(rampStart03.Text, out doubleValue);
if (success)
{
Parameters.LowValue_L3 = doubleValue;
}
success = double.TryParse(rampEnd03.Text, out doubleValue);
if (success)
{
Parameters.HighValue_L3 = doubleValue;
}
Parameters.Color_L4 = rctColor4.Fill.ToString();
success = double.TryParse(rampStart04.Text, out doubleValue);
if (success)
{
Parameters.LowValue_L4 = doubleValue;
}
}
/// <summary>
/// Returns contents of a table.
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnNames">List of column names to select. Column names should not be bracketed; this method will add brackets.</param>
/// <returns>DataTable</returns>
public virtual DataTable GetTableData(string tableName, List<string> columnNames)
{
WordBuilder wb = new WordBuilder(",");
foreach (string s in columnNames)
{
wb.Add(this.InsertInEscape(s));
}
return GetTableData(tableName, wb.ToString(), string.Empty);
}
/// <summary>
/// Converts the gadget's output to Html
/// </summary>
/// <returns></returns>
public override string ToHTML(string htmlFileName = "", int count = 0)
{
if (IsCollapsed) return string.Empty;
StringBuilder htmlBuilder = new StringBuilder();
CustomOutputHeading = headerPanel.Text;
CustomOutputDescription = descriptionPanel.Text;
if (CustomOutputHeading == null || (string.IsNullOrEmpty(CustomOutputHeading) && !CustomOutputHeading.Equals("(none)")))
{
htmlBuilder.AppendLine("<h2 class=\"gadgetHeading\">Frequency</h2>");
}
else if (CustomOutputHeading != "(none)")
{
htmlBuilder.AppendLine("<h2 class=\"gadgetHeading\">" + CustomOutputHeading + "</h2>");
}
htmlBuilder.AppendLine("<p class=\"gadgetOptions\"><small>");
htmlBuilder.AppendLine("<em>Frequency variable:</em> <strong>" + cbxField.Text + "</strong>");
htmlBuilder.AppendLine("<br />");
if (cbxFieldWeight.SelectedIndex >= 0)
{
htmlBuilder.AppendLine("<em>Weight variable:</em> <strong>" + cbxFieldWeight.Text + "</strong>");
htmlBuilder.AppendLine("<br />");
}
//if (cbxFieldStrata.SelectedIndex >= 0)
//{
// htmlBuilder.AppendLine("<em>Strata variable:</em> <strong>" + cbxFieldStrata.Text + "</strong>");
// htmlBuilder.AppendLine("<br />");
//}
if (lbxFieldStrata.SelectedItems.Count > 0)
{
WordBuilder wb = new WordBuilder(", ");
foreach (string s in lbxFieldStrata.SelectedItems)
{
wb.Add(s);
}
htmlBuilder.AppendLine("<em>Strata variable(s):</em> <strong>" + wb.ToString() + "</strong>");
htmlBuilder.AppendLine("<br />");
}
htmlBuilder.AppendLine("<em>Include missing:</em> <strong>" + checkboxIncludeMissing.IsChecked.ToString() + "</strong>");
htmlBuilder.AppendLine("<br />");
htmlBuilder.AppendLine("</small></p>");
if (!string.IsNullOrEmpty(CustomOutputDescription))
{
htmlBuilder.AppendLine("<p class=\"gadgetsummary\">" + CustomOutputDescription + "</p>");
}
if (!string.IsNullOrEmpty(messagePanel.Text) && messagePanel.Visibility == Visibility.Visible)
{
htmlBuilder.AppendLine("<p><small><strong>" + messagePanel.Text + "</strong></small></p>");
}
if (!string.IsNullOrEmpty(infoPanel.Text) && infoPanel.Visibility == Visibility.Visible)
{
htmlBuilder.AppendLine("<p><small><strong>" + infoPanel.Text + "</strong></small></p>");
}
foreach (Grid grid in this.StrataGridList)
{
string gridName = grid.Tag.ToString();
string summaryText = "This tables represents the frequency of the field " + cbxField.Text + ". ";
if (!string.IsNullOrEmpty(cbxFieldWeight.Text)) { summaryText += "The field " + cbxFieldWeight.Text + " has been specified as a weight. "; }
if (lbxFieldStrata.SelectedItems.Count > 0) { summaryText += "The frequency data has been stratified. The data in this table is for the strata value " + grid.Tag.ToString() + ". "; }
summaryText += "Each non-heading row in the table represents one of the distinct frequency values. The last row contains the total.";
htmlBuilder.AppendLine("<div style=\"height: 7px;\"></div>");
htmlBuilder.AppendLine("<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" summary=\"" + summaryText + "\">");
if (string.IsNullOrEmpty(CustomOutputCaption))
{
if (lbxFieldStrata.SelectedItems.Count > 0)
{
htmlBuilder.AppendLine("<caption>" + grid.Tag + "</caption>");
}
}
else
{
htmlBuilder.AppendLine("<caption>" + CustomOutputCaption + "</caption>");
}
for (int i = 0; i < grid.RowDefinitions.Count; i++)
{
for (int j = 0; j < grid.ColumnDefinitions.Count; j++)
{
string tableDataTagOpen = "<td>";
string tableDataTagClose = "</td>";
if (i == 0)
{
tableDataTagOpen = "<th>";
tableDataTagClose = "</th>";
}
if (j == 0)
{
htmlBuilder.AppendLine("<tr>");
}
if (j == 0 && i > 0)
{
tableDataTagOpen = "<td class=\"value\">";
}
ColumnDefinition colDef = grid.ColumnDefinitions[j];
if (colDef.Width.Value > 0)
{
IEnumerable<UIElement> elements = grid.Children.Cast<UIElement>().Where(x => Grid.GetRow(x) == i && Grid.GetColumn(x) == j);
TextBlock txt = null;
Rectangle rct = null;
foreach (UIElement element in elements)
{
if (element is TextBlock)
{
txt = element as TextBlock;
}
else if (element is Rectangle)
{
rct = element as Rectangle;
}
}
if (rct != null && j > 0 && i > 0)
{
double barWidth = rct.ActualWidth;
htmlBuilder.AppendLine("<td class=\"value\"><div class=\"percentBar\" style=\"width: " + ((int)barWidth * 2).ToString() + "px;\"></td>");
}
else
{
string value = " ";
if (txt != null)
{
value = txt.Text;
}
if (i == grid.RowDefinitions.Count - 1)
{
htmlBuilder.AppendLine(tableDataTagOpen + "<b>" + value + "</b>" + tableDataTagClose);
}
else
{
htmlBuilder.AppendLine(tableDataTagOpen + value + tableDataTagClose);
}
}
}
if (j >= grid.ColumnDefinitions.Count - 1)
{
htmlBuilder.AppendLine("</tr>");
}
}
}
htmlBuilder.AppendLine("</table>");
}
return htmlBuilder.ToString();
}
/// <summary>
/// Creates a new blank row for a given form's base table and all of its page tables.
/// </summary>
/// <param name="form">The form where the row should be added.</param>
/// <param name="guid">The Guid value to use for the row.</param>
/// <param name="keyValues">The key values to use for custom matching</param>
/// <param name="fkey">The foreign key for the row.</param>
/// <param name="firstSaveId">The user ID of the first person that saved this record.</param>
/// <param name="firstSaveTime">The time when the record was first saved.</param>
/// <param name="lastSaveId">The user ID of the last person that saved this record.</param>
/// <param name="lastSaveTime">The time when the record was last saved.</param>
protected virtual void CreateNewBlankRow(View form, string guid, Dictionary <Field, object> keyValues = null, string fkey = "", string firstSaveId = "", string lastSaveId = "", DateTime?firstSaveTime = null, DateTime?lastSaveTime = null)
{
#region Input Validation
if (string.IsNullOrEmpty(guid))
{
throw new ArgumentNullException("guid");
}
if (form == null)
{
throw new ArgumentNullException("form");
}
#endregion // Input Validation
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
IDbDriver db = DestinationProject.CollectedData.GetDatabase();
StringBuilder sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(db.InsertInEscape(form.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.SPACE);
WordBuilder fields = new WordBuilder(",");
fields.Append("[GlobalRecordId]");
if (!string.IsNullOrEmpty(fkey))
{
fields.Append("[FKEY]");
}
if (!string.IsNullOrEmpty(firstSaveId))
{
fields.Append("[FirstSaveLogonName]");
}
if (!string.IsNullOrEmpty(lastSaveId))
{
fields.Append("[LastSaveLogonName]");
}
if (firstSaveTime.HasValue)
{
fields.Append("[FirstSaveTime]");
}
if (lastSaveTime.HasValue)
{
fields.Append("[LastSaveTime]");
}
sb.Append("(" + fields.ToString() + ")");
sb.Append(" values (");
List <QueryParameter> parameters = new List <QueryParameter>();
WordBuilder values = new WordBuilder(",");
values.Append("'" + guid + "'");
if (!string.IsNullOrEmpty(fkey))
{
values.Append("@FKEY");
parameters.Add(new QueryParameter("@FKEY", DbType.String, fkey));
}
if (!string.IsNullOrEmpty(firstSaveId))
{
values.Append("@FirstSaveLogonName");
parameters.Add(new QueryParameter("@FirstSaveLogonName", DbType.String, firstSaveId));
}
if (!string.IsNullOrEmpty(lastSaveId))
{
values.Append("@LastSaveLogonName");
parameters.Add(new QueryParameter("@LastSaveLogonName", DbType.String, lastSaveId));
}
if (firstSaveTime.HasValue)
{
values.Append("@FirstSaveTime");
parameters.Add(new QueryParameter("@FirstSaveTime", DbType.DateTime, firstSaveTime));
}
if (lastSaveTime.HasValue)
{
values.Append("@LastSaveTime");
parameters.Add(new QueryParameter("@LastSaveTime", DbType.DateTime, lastSaveTime));
}
sb.Append(values.ToString());
sb.Append(") ");
Epi.Data.Query insertQuery = db.CreateQuery(sb.ToString());
insertQuery.Parameters = parameters;
if (DestinationProject.CollectedDataDriver.ToLowerInvariant().Contains("epi.data.office"))
{
IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters);
object obj = command.ExecuteNonQuery();
}
else
{
db.ExecuteNonQuery(insertQuery);
}
//Parallel.ForEach(form.Pages, page =>
foreach (Page page in form.Pages)
{
WordBuilder wbFields = new WordBuilder(",");
WordBuilder wbParams = new WordBuilder(",");
List <QueryParameter> queryParams = new List <QueryParameter>();
wbFields.Add("[GlobalRecordId]");
wbParams.Add("@GlobalRecordId");
queryParams.Add(new QueryParameter("@GlobalRecordId", DbType.String, guid));
foreach (KeyValuePair <Field, object> kvp in keyValues)
{
RenderableField field = kvp.Key as RenderableField;
PackageFieldData fieldData = new PackageFieldData();
fieldData.FieldValue = kvp.Value;
fieldData.FieldName = field.Name;
fieldData.Page = field.Page;
if (field.Page.TableName.Equals(page.TableName))
{
wbFields.Add(db.InsertInEscape(fieldData.FieldName));
wbParams.Add("@" + fieldData.FieldName);
QueryParameter parameter = GetQueryParameterForField(fieldData, form, field.Page);
queryParams.Add(parameter);
}
}
sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(db.InsertInEscape(page.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.SPACE);
sb.Append("(");
sb.Append(wbFields.ToString());
sb.Append(")");
sb.Append(" values (");
sb.Append(wbParams.ToString());
sb.Append(") ");
insertQuery = db.CreateQuery(sb.ToString());
foreach (QueryParameter queryParam in queryParams)
{
insertQuery.Parameters.Add(queryParam);
}
if (DestinationProject.CollectedDataDriver.ToLowerInvariant().Contains("epi.data.office"))
{
IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters);
object obj = command.ExecuteNonQuery();
}
else
{
db.ExecuteNonQuery(insertQuery);
}
}
//);
}
/// <summary>
/// Begins the process of importing records from the data package into the destination form
/// </summary>
/// <param name="form">The form that will receive the data</param>
/// <param name="formNode">The XmlNode representing the form</param>
/// <param name="records">The data to be imported</param>
protected override void ImportRecords(View form, XmlNode formNode, List <PackageFieldData> records)
{
if (!IsUsingCustomMatchkeys) // Calling class should instantiate normal data packager if custom keys aren't used
{
throw new ApplicationException("This class should not be used without custom match keys.");
}
ImportInfo.RecordsAppended.Add(form, 0);
ImportInfo.RecordsUpdated.Add(form, 0);
IDbDriver destinationDb = DestinationProject.CollectedData.GetDatabase();
DataTable destinationKeyTable = new DataTable();
destinationKeyTable.Columns.Add(new DataColumn("GlobalRecordId", typeof(string)));
destinationKeyTable.Columns.Add(new DataColumn("Update", typeof(bool)));
destinationKeyTable.Columns.Add(new DataColumn("KeyDictionary", typeof(Dictionary <Field, object>)));
DataColumn [] primaryKey = new DataColumn[1];
primaryKey[0] = destinationKeyTable.Columns["GlobalRecordId"];
destinationKeyTable.PrimaryKey = primaryKey;
WordBuilder wb = new WordBuilder(",");
wb.Add("t.GlobalRecordId");
foreach (Field field in KeyFields)
{
wb.Add(field.Name);
}
Query selectQuery = destinationDb.CreateQuery("SELECT " + wb.ToString() + " " + form.FromViewSQL);
using (IDataReader keyTableReader = destinationDb.ExecuteReader(selectQuery))
{
while (keyTableReader.Read())
{
string guid = keyTableReader["GlobalRecordId"].ToString();
Dictionary <Field, object> keys = new Dictionary <Field, object>();
foreach (Field field in KeyFields)
{
keys.Add(field, keyTableReader[field.Name]);
}
destinationKeyTable.Rows.Add(guid, true, keys);
}
}
var query = from record in records
group record by record.RecordGUID;
IEnumerable <IEnumerable <PackageFieldData> > fieldDataLists = query as IEnumerable <IEnumerable <PackageFieldData> >;
foreach (IEnumerable <PackageFieldData> fieldDataList in fieldDataLists)
{
PackageFieldData fieldData = fieldDataList.First();
bool found = false;
foreach (DataRow row in destinationKeyTable.Rows)
{
Dictionary <Field, object> keyDictionary = row["KeyDictionary"] as Dictionary <Field, object>;
if (AreKeyFieldDictionariesEqual(keyDictionary, fieldData.KeyValues))
{
found = true;
if (!Update)
{
row["Update"] = false;
}
else
{
ImportInfo.TotalRecordsUpdated++;
ImportInfo.RecordsUpdated[form]++;
}
}
}
if (!found && Append) // no match, this is a new record that must be inserted
{
CreateNewBlankRow(form, fieldData.RecordGUID, fieldData.KeyValues);
ImportInfo.TotalRecordsAppended++;
ImportInfo.RecordsAppended[form]++;
}
}
System.Threading.Thread.Sleep(2000); // give time for DB to update
destinationKeyTable.Clear();
selectQuery = destinationDb.CreateQuery("SELECT " + wb.ToString() + " " + form.FromViewSQL);
using (IDataReader keyTableReader = destinationDb.ExecuteReader(selectQuery))
{
while (keyTableReader.Read())
{
string guid = keyTableReader["GlobalRecordId"].ToString();
Dictionary <Field, object> keys = new Dictionary <Field, object>();
foreach (Field field in KeyFields)
{
keys.Add(field, keyTableReader[field.Name]);
}
destinationKeyTable.Rows.Add(guid, true, keys);
}
}
//Parallel.ForEach(records, rec =>
// TODO: Make this faster (note that Parallel foreach seems to make it worse)
foreach (PackageFieldData rec in records)
{
bool found = false;
string targetGuid = String.Empty;
bool shouldUpdate = true;
foreach (DataRow row in destinationKeyTable.Rows)
{
Dictionary <Field, object> keyDictionary = row["KeyDictionary"] as Dictionary <Field, object>;
if (AreKeyFieldDictionariesEqual(keyDictionary, rec.KeyValues))
{
found = true;
targetGuid = row["GlobalRecordId"].ToString();
shouldUpdate = (bool)row["Update"];
break;
}
}
if (shouldUpdate && found && !String.IsNullOrEmpty(targetGuid) && rec.FieldValue != null && !String.IsNullOrEmpty(rec.FieldValue.ToString()))
{
Query updateQuery = destinationDb.CreateQuery("UPDATE " + rec.Page.TableName + " SET " +
"[" + rec.FieldName + "] = @" + rec.FieldName + " WHERE [GlobalRecordId] = @GlobalRecordId");
QueryParameter fieldParam = GetQueryParameterForField(rec, form, rec.Page);
if (fieldParam != null)
{
updateQuery.Parameters.Add(fieldParam);
updateQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, targetGuid));
int rowsAffected = destinationDb.ExecuteNonQuery(updateQuery);
if (rowsAffected == 0)
{
throw new ApplicationException("No records affected.");
}
else if (rowsAffected > 1)
{
throw new ApplicationException("Too many records affected.");
}
}
}
}
//);
}
/// <summary>
/// Gets all the programs saved in a project
/// </summary>
/// <returns>DataTable containing a list of all programs in a project</returns>
public virtual DataTable GetPgms()
{
try
{
WordBuilder columnNames = new WordBuilder(StringLiterals.COMMA);
columnNames.Append("ProgramId");
columnNames.Append("Name");
columnNames.Append("Content");
columnNames.Append("Comment");
columnNames.Append("DateCreated");
columnNames.Append("DateModified");
columnNames.Append("Author");
Query query = db.CreateQuery("select" + Util.InsertIn(columnNames.ToString(), StringLiterals.SPACE) +
Util.InsertIn("from", StringLiterals.SPACE) + db.InsertInEscape("metaPrograms"));
return db.Select(query);
}
catch (Exception ex)
{
throw new GeneralException("Could not retrieve programs", ex);
}
}
/// <summary>
/// Initiates an export of the data to the specified file.
/// </summary>
public override void Export()
{
OnSetStatusMessage(ImportExportSharedStrings.WEB_CSV_EXPORT_CONNECTING);
rowsExported = 0;
WordBuilder wb = new WordBuilder(SEPARATOR);
StreamWriter sw = null;
Epi.Web.Common.Message.SurveyAnswerRequest Request = new Epi.Web.Common.Message.SurveyAnswerRequest();
Request.Criteria.SurveyId = surveyKey.ToString();
Request.Criteria.UserPublishKey = secToken;
Request.Criteria.OrganizationKey = orgKey;
Request.Criteria.ReturnSizeInfoOnly = true;
Epi.Web.Common.Message.SurveyAnswerResponse Result = client.GetSurveyAnswer(Request);
Pages = Result.NumberOfPages;
PageSize = Result.PageSize;
Request.Criteria.ReturnSizeInfoOnly = false;
int count = 0;
List<SurveyAnswerResponse> Results = new List<SurveyAnswerResponse>();
OnSetStatusMessage(ImportExportSharedStrings.WEB_CSV_EXPORT_BUILDING_COLUMN_HEADINGS);
for (int i = 1; i <= Pages; i++)
{
Request.Criteria.PageNumber = i;
Request.Criteria.PageSize = PageSize;
Result = client.GetSurveyAnswer(Request);
Results.Add(Result);
foreach (Epi.Web.Common.DTO.SurveyAnswerDTO surveyAnswer in Result.SurveyResponseList)
{
if (surveyAnswer.Status == 3)
{
count++;
}
}
}
if (SetMaxProgressBarValue != null)
{
SetMaxProgressBarValue((double)count);
}
List<string> columnHeaders = new List<string>();
foreach (SurveyAnswerResponse R in Results)
{
wfList = ParseXML(R);
foreach (WebFieldData wfData in wfList)
{
if (!columnHeaders.Contains(wfData.FieldName))
{
columnHeaders.Add(wfData.FieldName);
}
else
{
break;
}
}
break;
}
try
{
OnSetStatusMessage(ImportExportSharedStrings.WEB_CSV_EXPORTING);
sw = File.CreateText(fileName);
foreach (string s in columnHeaders)
{
wb.Add(s);
}
sw.WriteLine(wb.ToString());
rowsExported = 0;
foreach (SurveyAnswerResponse R in Results)
{
string currentGUID = string.Empty;
string lastGUID = string.Empty;
wfList = ParseXML(R);
wb = new WordBuilder(SEPARATOR);
foreach (WebFieldData wfData in wfList)
{
currentGUID = wfData.RecordGUID;
if (!string.IsNullOrEmpty(currentGUID) && !string.IsNullOrEmpty(lastGUID) && currentGUID != lastGUID)
{
sw.WriteLine(wb.ToString());
wb = new WordBuilder(SEPARATOR);
OnSetStatusMessageAndProgressCount("", 1);
}
string rowValue = wfData.FieldValue.ToString().Replace("\r\n", " ");
if (rowValue.Contains(",") || rowValue.Contains("\""))
{
rowValue = rowValue.Replace("\"", "\"\"");
rowValue = Util.InsertIn(rowValue, "\"");
}
wb.Add(rowValue);
lastGUID = wfData.RecordGUID;
}
sw.WriteLine(wb.ToString());
}
OnSetStatusMessage(ImportExportSharedStrings.WEB_CSV_EXPORT_COMPLETE);
if (FinishExport != null)
{
FinishExport();
}
}
catch (Exception ex)
{
OnSetStatusMessage(ex.Message);
if (ExportFailed != null)
{
ExportFailed(ex.Message);
}
}
finally
{
// Clean up
if (sw != null)
{
sw.Close();
sw.Dispose();
sw = null;
}
}
}
protected override bool CreateNewRow(IDbConnection conn, View form, XElement record, string guid, string fkey = "", string recStatus = "1", string firstSaveId = "", string lastSaveId = "", DateTime?firstSaveTime = null, DateTime?lastSaveTime = null)
{
SqlConnection connection = conn as SqlConnection;
IDbDriver db = Project.CollectedData.GetDatabase();
StringBuilder sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(db.InsertInEscape(form.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.SPACE);
WordBuilder fields = new WordBuilder(",");
fields.Append("[GlobalRecordId]");
if (!String.IsNullOrEmpty(fkey))
{
fields.Append("[FKEY]");
}
if (!String.IsNullOrEmpty(recStatus))
{
fields.Append("[RecStatus]");
}
if (!String.IsNullOrEmpty(firstSaveId))
{
fields.Append("[FirstSaveLogonName]");
}
if (!String.IsNullOrEmpty(lastSaveId))
{
fields.Append("[LastSaveLogonName]");
}
if (firstSaveTime.HasValue)
{
firstSaveTime = new DateTime(firstSaveTime.Value.Year,
firstSaveTime.Value.Month,
firstSaveTime.Value.Day,
firstSaveTime.Value.Hour,
firstSaveTime.Value.Minute,
firstSaveTime.Value.Second);
fields.Append("[FirstSaveTime]");
}
if (lastSaveTime.HasValue)
{
lastSaveTime = new DateTime(lastSaveTime.Value.Year,
lastSaveTime.Value.Month,
lastSaveTime.Value.Day,
lastSaveTime.Value.Hour,
lastSaveTime.Value.Minute,
lastSaveTime.Value.Second);
fields.Append("[LastSaveTime]");
}
sb.Append("(" + fields.ToString() + ")");
sb.Append(" values (");
List <QueryParameter> parameters = new List <QueryParameter>();
WordBuilder values = new WordBuilder(",");
values.Append("'" + guid + "'");
if (!String.IsNullOrEmpty(fkey))
{
values.Append("@FKEY");
parameters.Add(new QueryParameter("@FKEY", DbType.String, fkey));
}
if (!String.IsNullOrEmpty(recStatus))
{
values.Append("@RecStatus");
parameters.Add(new QueryParameter("@RecStatus", DbType.Int32, Convert.ToInt32(recStatus)));
}
if (!String.IsNullOrEmpty(firstSaveId))
{
values.Append("@FirstSaveLogonName");
parameters.Add(new QueryParameter("@FirstSaveLogonName", DbType.String, firstSaveId));
}
if (!String.IsNullOrEmpty(lastSaveId))
{
values.Append("@LastSaveLogonName");
parameters.Add(new QueryParameter("@LastSaveLogonName", DbType.String, lastSaveId));
}
if (firstSaveTime.HasValue)
{
values.Append("@FirstSaveTime");
parameters.Add(new QueryParameter("@FirstSaveTime", DbType.DateTime, firstSaveTime));
}
if (lastSaveTime.HasValue)
{
values.Append("@LastSaveTime");
parameters.Add(new QueryParameter("@LastSaveTime", DbType.DateTime, lastSaveTime));
}
sb.Append(values.ToString());
sb.Append(") ");
Epi.Data.Query insertQuery = db.CreateQuery(sb.ToString());
insertQuery.Parameters = parameters;
using (SqlTransaction transaction = connection.BeginTransaction("SampleTransaction"))
{
try
{
using (IDbCommand baseTableCommand = GetCommand(insertQuery.SqlStatement, conn, insertQuery.Parameters))
{
baseTableCommand.Transaction = transaction;
object baseObj = baseTableCommand.ExecuteNonQuery();
}
//foreach (Page page in form.Pages)
//{
// sb = new StringBuilder();
// sb.Append(" insert into ");
// sb.Append(db.InsertInEscape(page.TableName));
// sb.Append(StringLiterals.SPACE);
// sb.Append(StringLiterals.SPACE);
// sb.Append("([GlobalRecordId])");
// sb.Append(" values (");
// sb.Append("'" + guid + "'");
// sb.Append(") ");
// insertQuery = db.CreateQuery(sb.ToString());
// using (IDbCommand pageTableCommand = GetCommand(insertQuery.SqlStatement, conn, insertQuery.Parameters))
// {
// pageTableCommand.Transaction = transaction;
// object pageObj = pageTableCommand.ExecuteNonQuery();
// }
//}
foreach (Page page in form.Pages)
{
WordBuilder wbFieldNames = new WordBuilder(", ");
WordBuilder wbParamNames = new WordBuilder(", ");
List <QueryParameter> pageInsertParameters = new List <QueryParameter>();
foreach (RenderableField field in page.Fields)
{
if (field is IDataField && record.Element(field.Name) != null)
{
wbFieldNames.Add(field.Name);
wbParamNames.Add("@" + field.Name);
pageInsertParameters.Add(
GetQueryParameterForField(field,
FormatFieldData(form, field.Name, record.Element(field.Name).Value),
form,
page));
}
}
wbFieldNames.Add("GlobalRecordId");
wbParamNames.Add("@GlobalRecordId");
pageInsertParameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, guid));
Query inserteQuery = db.CreateQuery("INSERT INTO " + page.TableName + " (" + wbFieldNames.ToString() + ") VALUES (" + wbParamNames.ToString() + ")");
foreach (QueryParameter parameter in pageInsertParameters)
{
inserteQuery.Parameters.Add(parameter);
}
using (IDbCommand pageTableCommand = GetCommand(inserteQuery.SqlStatement, conn, inserteQuery.Parameters))
{
pageTableCommand.Transaction = transaction;
object obj = pageTableCommand.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (Exception ex)
{
Epi.Logger.Log(String.Format(DateTime.Now + ": " + "Commit Exception Type: {0}", ex.GetType()));
Epi.Logger.Log(String.Format(DateTime.Now + ": " + "Commit Exception Message: {0}", ex.Message));
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
Epi.Logger.Log(String.Format(DateTime.Now + ": " + "Rollback Exception Type: {0}", ex2.GetType()));
Epi.Logger.Log(String.Format(DateTime.Now + ": " + "Rollback Exception Message: {0}", ex2.Message));
}
return(false);
}
}
return(true);
}
/// <summary>
/// Processes a form's base table
/// </summary>
/// <param name="sourceView">The source form</param>
/// <param name="destinationView">The destination form</param>
/// <param name="destinationGUIDList">The list of GUIDs that exist in the destination</param>
private void ProcessBaseTable(View sourceView, View destinationView, List<string> destinationGUIDList)
{
sourceView.LoadFirstRecord();
OnAddStatusMessage(ImportExportSharedStrings.PROCESSING_BASE_TABLE);
int recordsInserted = 0;
int recordsUpdated = 0;
string sourceTable = sourceView.TableName;
string destinationTable = destinationView.TableName;
optionFieldsAsStrings = new List<string>();
// Check for string-based option fields.
foreach (Field f in destinationView.Fields)
{
if (f is OptionField)
{
OptionField optionField = f as OptionField;
if (optionField != null)
{
DataTable dt = destinationProjectDataDriver.GetTopTwoTable(optionField.Page.TableName);
if (dt.Columns[optionField.Name].DataType.ToString().Equals("System.String", StringComparison.OrdinalIgnoreCase))
{
optionFieldsAsStrings.Add(f.Name);
}
}
}
}
try
{
List<string> newGUIDList = new List<string>();
IDataReader sourceReader = sourceProjectDataDriver.GetTableDataReader(sourceView.TableName);
while (sourceReader.Read())
{
object recordStatus = sourceReader["RECSTATUS"];
QueryParameter paramRecordStatus = new QueryParameter("@RECSTATUS", DbType.Int32, recordStatus);
//if (importWorker.CancellationPending)
//{
// this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "Import cancelled.");
// return;
//}
if (OnCheckForCancellation())
{
OnAddStatusMessage(ImportExportSharedStrings.IMPORT_CANCELLED);
sourceReader.Close();
sourceReader.Dispose();
sourceReader = null;
return;
}
WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA);
WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA);
List<QueryParameter> fieldValueParams = new List<QueryParameter>();
string GUID = sourceReader["GlobalRecordId"].ToString();
if (sourceGUIDs != null && !sourceGUIDs.Contains(GUID))
{
continue;
}
fieldNames.Append("GlobalRecordId");
fieldValues.Append("@GlobalRecordId");
string FKEY = sourceReader["FKEY"].ToString();
QueryParameter paramFkey = new QueryParameter("@FKEY", DbType.String, FKEY); // don't add this yet
QueryParameter paramGUID = new QueryParameter("@GlobalRecordId", DbType.String, GUID);
fieldValueParams.Add(paramGUID);
if (destinationGUIDList.Contains(GUID))
{
if (update)
{
// UPDATE matching records
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(destinationTable));
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);
//if (sourceView.ForeignKeyFieldExists)
if (!string.IsNullOrEmpty(FKEY))
{
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append("FKEY");
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sb.Append(StringLiterals.EQUAL);
sb.Append(StringLiterals.COMMERCIAL_AT);
sb.Append("FKEY");
fieldValueParams.Add(paramFkey);
Query updateQuery = destinationProjectDataDriver.CreateQuery(updateHeader + StringLiterals.SPACE + sb.ToString() + StringLiterals.SPACE + whereClause);
updateQuery.Parameters = fieldValueParams;
destinationProjectDataDriver.ExecuteNonQuery(updateQuery);
sb.Remove(0, sb.ToString().Length);
fieldValueParams.Clear();
recordsUpdated++;
}
}
}
else
{
if (append)
{
if (!string.IsNullOrEmpty(FKEY))
{
fieldNames.Append("FKEY");
fieldValues.Append("@FKEY");
fieldValueParams.Add(paramFkey);
}
fieldNames.Append("RECSTATUS");
fieldValues.Append("@RECSTATUS");
fieldValueParams.Add(paramRecordStatus);
// Concatenate the query clauses into one SQL statement.
StringBuilder sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(destinationProjectDataDriver.InsertInEscape(destinationTable));
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++;
}
}
OnSetProgress(1);
}
sourceReader.Close();
sourceReader.Dispose();
sourceReader = null;
}
catch (Exception ex)
{
OnAddStatusMessage(string.Format(ImportExportSharedStrings.ERROR_WITH_MESSAGE, ex.Message));
}
finally
{
}
if (update && append)
{
OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_TABLE_UPDATED_AND_APPENDED, destinationTable, recordsInserted.ToString(), recordsUpdated.ToString()));
}
else if (update && !append)
{
OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_TABLE_UPDATED, destinationTable, recordsUpdated.ToString()));
}
else if (!update && append)
{
OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_TABLE_APPENDED, destinationTable, recordsInserted.ToString()));
}
}
/// <summary>
/// Converts the gadget's output to Html
/// </summary>
/// <returns></returns>
public override string ToHTML(string htmlFileName = "", int count = 0)
{
if (IsCollapsed) return string.Empty;
StringBuilder htmlBuilder = new StringBuilder();
CustomOutputHeading = headerPanel.Text;
CustomOutputDescription = descriptionPanel.Text;
if (CustomOutputHeading == null || (string.IsNullOrEmpty(CustomOutputHeading) && !CustomOutputHeading.Equals("(none)")))
{
htmlBuilder.AppendLine("<h2 class=\"gadgetHeading\">Crosstabulation</h2>");
}
else if (CustomOutputHeading != "(none)")
{
htmlBuilder.AppendLine("<h2 class=\"gadgetHeading\">" + CustomOutputHeading + "</h2>");
}
htmlBuilder.AppendLine("<p class=\"gadgetOptions\"><small>");
htmlBuilder.AppendLine("<em>Main variable:</em> <strong>" + cbxExposureField.Text + "</strong>");
htmlBuilder.AppendLine("<br />");
htmlBuilder.AppendLine("<em>Crosstab variable:</em> <strong>" + cbxOutcomeField.Text + "</strong>");
htmlBuilder.AppendLine("<br />");
if (cbxFieldWeight.SelectedIndex >= 0)
{
htmlBuilder.AppendLine("<em>Weight variable:</em> <strong>" + cbxFieldWeight.Text + "</strong>");
htmlBuilder.AppendLine("<br />");
}
//if (cbxFieldStrata.SelectedIndex >= 0)
//{
// htmlBuilder.AppendLine("<em>Strata variable:</em> <strong>" + cbxFieldStrata.Text + "</strong>");
// htmlBuilder.AppendLine("<br />");
//}
if (lbxFieldStrata.SelectedItems.Count > 0)
{
WordBuilder wb = new WordBuilder(", ");
foreach (string s in lbxFieldStrata.SelectedItems)
{
wb.Add(s);
}
htmlBuilder.AppendLine("<em>Strata variable:</em> <strong>" + wb.ToString() + "</strong>");
htmlBuilder.AppendLine("<br />");
}
htmlBuilder.AppendLine("<em>Include missing:</em> <strong>" + checkboxIncludeMissing.IsChecked.ToString() + "</strong>");
htmlBuilder.AppendLine("<br />");
htmlBuilder.AppendLine("</small></p>");
if (!string.IsNullOrEmpty(CustomOutputDescription))
{
htmlBuilder.AppendLine("<p class=\"gadgetsummary\">" + CustomOutputDescription + "</p>");
}
if (!string.IsNullOrEmpty(messagePanel.Text) && messagePanel.Visibility == Visibility.Visible)
{
htmlBuilder.AppendLine("<p><small><strong>" + messagePanel.Text + "</strong></small></p>");
}
if (!string.IsNullOrEmpty(infoPanel.Text) && infoPanel.Visibility == Visibility.Visible)
{
htmlBuilder.AppendLine("<p><small><strong>" + infoPanel.Text + "</strong></small></p>");
}
foreach (Grid grid in this.StrataGridList)
{
string gridName = grid.Tag.ToString();
htmlBuilder.AppendLine("<div style=\"height: 7px;\"></div>");
htmlBuilder.AppendLine("<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\">");
htmlBuilder.AppendLine("<caption>" + cbxExposureField.Text + " * " + cbxOutcomeField.Text + "</caption>");
foreach (UIElement control in grid.Children)
{
string value = string.Empty;
int rowNumber = -1;
int columnNumber = -1;
if (control is TextBlock || control is StackPanel)
{
if (control is TextBlock)
{
rowNumber = Grid.GetRow(control);
columnNumber = Grid.GetColumn(control);
value = ((TextBlock)control).Text;
}
else if (control is StackPanel)
{
rowNumber = Grid.GetRow(control);
columnNumber = Grid.GetColumn(control);
value = (((control as StackPanel).Children[0]) as TextBlock).Text;
}
string tableDataTagOpen = "<td>";
string tableDataTagClose = "</td>";
if (rowNumber == 0)
{
tableDataTagOpen = "<th>";
tableDataTagClose = "</th>";
}
if (columnNumber == 0)
{
htmlBuilder.AppendLine("<tr>");
}
if (columnNumber == 0 && rowNumber > 0)
{
tableDataTagOpen = "<td class=\"value\">";
}
string formattedValue = value;
if ((rowNumber == grid.RowDefinitions.Count - 1) || (columnNumber == grid.ColumnDefinitions.Count - 1))
{
formattedValue = "<span class=\"total\">" + value + "</span>";
}
htmlBuilder.AppendLine(tableDataTagOpen + formattedValue + tableDataTagClose);
if (columnNumber >= grid.ColumnDefinitions.Count - 1)
{
htmlBuilder.AppendLine("</tr>");
}
}
}
htmlBuilder.AppendLine("</table>");
// Chi Square
Grid chiSquareGrid = GetStrataChiSquareGrid(grid.Tag.ToString());
htmlBuilder.AppendLine("<p></p>");
htmlBuilder.AppendLine("<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\">");
foreach (UIElement control in chiSquareGrid.Children)
{
if (control is TextBlock)
{
int rowNumber = Grid.GetRow(control);
int columnNumber = Grid.GetColumn(control);
string tableDataTagOpen = "<td>";
string tableDataTagClose = "</td>";
if (rowNumber == 0)
{
tableDataTagOpen = "<th>";
tableDataTagClose = "</th>";
}
if (columnNumber == 0)
{
htmlBuilder.AppendLine("<tr>");
}
string value = ((TextBlock)control).Text;
string formattedValue = value;
htmlBuilder.AppendLine(tableDataTagOpen + formattedValue + tableDataTagClose);
if (columnNumber >= grid.ColumnDefinitions.Count - 1)
{
htmlBuilder.AppendLine("</tr>");
}
}
}
htmlBuilder.AppendLine("</table>");
string disclaimer = GetStrataChiSquareDisclaimer(grid.Tag.ToString()).Text;
if (!string.IsNullOrEmpty(disclaimer))
{
htmlBuilder.AppendLine("<p></p>");
htmlBuilder.AppendLine("<p>" + disclaimer.Replace("<", "<") + "</p>");
}
// End Chi Square
}
foreach (GadgetMatchedPairPanel grid2x2 in this.strata2x2GridList)
{
//string gridName = grid.Tag.ToString();
string gridName = string.Empty;
if (grid2x2.Tag != null)
{
gridName = grid2x2.Tag.ToString();
}
if (!string.IsNullOrEmpty(gridName))
{
htmlBuilder.AppendLine("<h3>" + gridName + "</h3>");
}
htmlBuilder.AppendLine("<div style=\"height: 7px;\"></div>");
htmlBuilder.AppendLine(grid2x2.ToHTML());
htmlBuilder.AppendLine("<p></p>");
}
foreach (UIElement element in panelMain.Children)
{
if (element is GadgetMatchedPairPanel)
{
GadgetMatchedPairPanel stap = element as GadgetMatchedPairPanel;
htmlBuilder.AppendLine("<div style=\"height: 7px;\"></div>");
htmlBuilder.AppendLine(stap.ToHTML());
htmlBuilder.AppendLine("<p></p>");
}
}
return htmlBuilder.ToString();
}
/// <summary>
/// Processes all of the grid fields on a given form
/// </summary>
/// <param name="sourceView">The source form</param>
/// <param name="destinationView">The destination form</param>
private void ProcessGridFields(View sourceView, View destinationView)
{
foreach (GridField gridField in sourceView.Fields.GridFields)
{
List<string> gridGUIDList = new List<string>();
if (destinationView.Fields.GridFields.Contains(gridField.Name))
{
int recordsUpdated = 0;
int recordsInserted = 0;
OnAddStatusMessage(string.Format(ImportExportSharedStrings.PROCESSING_GRID, gridField.Name));
try
{
List<string> gridColumnsToSkip = new List<string>();
string destinationGridTableName = destinationView.Fields.GridFields[gridField.Name].TableName;
GridField destinationGridField = destinationView.Fields.GridFields[gridField.Name];
IDataReader destinationGridTableReader = destinationProjectDataDriver.GetTableDataReader(destinationGridTableName);
foreach (GridColumnBase gridColumn in gridField.Columns)
{
bool found = false;
foreach (GridColumnBase destinationGridColumn in destinationGridField.Columns)
{
if (destinationGridColumn.Name.ToLower().Equals(gridColumn.Name.ToLower()))
{
found = true;
}
}
if (!found)
{
gridColumnsToSkip.Add(gridColumn.Name);
}
}
string gridReference = sourceView.Name + ":" + gridField.Name;
if (GridColumnsToNull != null && GridColumnsToNull.ContainsKey(gridReference))
{
List<string> toNull = GridColumnsToNull[gridReference];
foreach (string s in toNull)
{
if (!gridColumnsToSkip.Contains(s))
{
gridColumnsToSkip.Add(s);
}
}
}
while (destinationGridTableReader.Read())
{
gridGUIDList.Add(destinationGridTableReader["UniqueRowId"].ToString());
}
destinationGridTableReader.Close();
destinationGridTableReader.Dispose();
IDataReader sourceGridTableReader = sourceProjectDataDriver.GetTableDataReader(gridField.TableName);
while (sourceGridTableReader.Read())
{
string GUID = sourceGridTableReader["UniqueRowId"].ToString();
string FKEY = sourceGridTableReader["FKEY"].ToString();
if (sourceGUIDs != null && !sourceGUIDs.Contains(FKEY))
{
continue;
}
if (gridGUIDList.Contains(GUID) && update)
{
int columns = 0;
StringBuilder sb = new StringBuilder();
List<QueryParameter> fieldValueParams = new List<QueryParameter>();
sb.Append("UPDATE " + destinationProjectDataDriver.InsertInEscape(destinationGridTableName) + " SET ");
foreach (GridColumnBase gridColumn in gridField.Columns)
{
object data = sourceGridTableReader[gridColumn.Name];
if (data == null || string.IsNullOrEmpty(data.ToString()) || data == DBNull.Value)
{
continue; // don't update current data with null values (according to product requirements)
}
else if (gridColumnsToSkip.Contains(gridColumn.Name))
{
continue; // don't try and update a grid row that may not exist
}
sb.Append(destinationProjectDataDriver.InsertInEscape(gridColumn.Name));
sb.Append(StringLiterals.EQUAL);
sb.Append("@" + gridColumn.Name);
switch (gridColumn.GridColumnType)
{
case MetaFieldType.Date:
case MetaFieldType.DateTime:
case MetaFieldType.Time:
//sb.Append(destinationProjectDataDriver.FormatDateTime((DateTime)data));
fieldValueParams.Add(new QueryParameter("@" + gridColumn.Name, DbType.DateTime, data));
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:
//sb.Append("'" + data.ToString().Replace("'", "''") + "'");
fieldValueParams.Add(new QueryParameter("@" + gridColumn.Name, DbType.String, data));
break;
case MetaFieldType.Number:
case MetaFieldType.RecStatus:
fieldValueParams.Add(new QueryParameter("@" + gridColumn.Name, DbType.Single, data));
break;
default:
throw new ApplicationException(string.Format(ImportExportSharedStrings.ERROR_GRID_COLUMN, gridColumn.Name));
}
sb.Append(StringLiterals.COMMA);
columns++;
}
if (columns == 0)
{
continue;
}
sb.Length = sb.Length - 1;
sb.Append(" WHERE ");
sb.Append("[UniqueRowId] = ");
sb.Append("'" + GUID + "'");
Query updateQuery = destinationProjectDataDriver.CreateQuery(sb.ToString());
updateQuery.Parameters = fieldValueParams;
destinationProjectDataDriver.ExecuteNonQuery(updateQuery);
recordsUpdated++;
OnSetProgress(1);
//this.BeginInvoke(new SetProgressBarDelegate(IncrementProgressBarValue), 1);
}
else if (!gridGUIDList.Contains(GUID) && append)
{
int columns = 0;
List<QueryParameter> fieldValueParams = new List<QueryParameter>();
WordBuilder fieldNames = new WordBuilder(",");
WordBuilder fieldValues = new WordBuilder(",");
foreach (GridColumnBase gridColumn in gridField.Columns)
{
object data = sourceGridTableReader[gridColumn.Name];
if (data == null || string.IsNullOrEmpty(data.ToString()) || data == DBNull.Value)
{
continue; // don't update current data with null values (according to product requirements)
}
else if (gridColumnsToSkip.Contains(gridColumn.Name))
{
continue; // don't try and update a grid row that may not exist
}
fieldNames.Add(destinationProjectDataDriver.InsertInEscape(gridColumn.Name));
fieldValues.Add("@" + gridColumn.Name);
switch (gridColumn.GridColumnType)
{
case MetaFieldType.Date:
case MetaFieldType.DateTime:
case MetaFieldType.Time:
fieldValueParams.Add(new QueryParameter("@" + gridColumn.Name, DbType.DateTime, data));
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:
fieldValueParams.Add(new QueryParameter("@" + gridColumn.Name, DbType.String, data));
break;
case MetaFieldType.Number:
case MetaFieldType.RecStatus:
fieldValueParams.Add(new QueryParameter("@" + gridColumn.Name, DbType.Single, data));
break;
default:
throw new ApplicationException(string.Format(ImportExportSharedStrings.ERROR_GRID_COLUMN, gridColumn.Name));
}
columns++;
}
if (columns == 0)
{
continue;
}
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO " + destinationProjectDataDriver.InsertInEscape(destinationGridTableName));
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);
OnSetProgress(1);
//this.BeginInvoke(new SetProgressBarDelegate(IncrementProgressBarValue), 1);
recordsInserted++;
}
OnSetProgress(1);
} // end while source data reader reads
sourceGridTableReader.Close();
sourceGridTableReader.Dispose();
if (update && append)
{
OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_GRID_UPDATED_AND_APPENDED, gridField.Name, recordsInserted.ToString(), recordsUpdated.ToString()));
}
else if (update && !append)
{
OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_GRID_UPDATED, gridField.Name, recordsUpdated.ToString()));
}
else if (!update && append)
{
OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_GRID_APPENDED, gridField.Name, recordsInserted.ToString()));
}
}
catch (Exception ex)
{
OnAddStatusMessage(string.Format(ImportExportSharedStrings.ERROR_WITH_MESSAGE, ex.Message));
}
} // end if contains
}
}
/// <summary>
/// Used to generate the list of variables and options for the GadgetParameters object
/// </summary>
protected override void CreateInputVariableList()
{
// Set data filters!
this.DataFilters = RowFilterControl.DataFilters;
Dictionary <string, string> inputVariableList = new Dictionary <string, string>();
Parameters.ColumnNames = new List <string>();
Parameters.SortVariables = new Dictionary <string, SortOrder>();
Parameters.GadgetTitle = txtTitle.Text;
Parameters.GadgetDescription = txtDesc.Text;
double height = 0;
double width = 0;
int maxrows = 0;
int maxColumnLength = 0;
bool success = double.TryParse(txtMaxHeight.Text, out height);
if (success)
{
Parameters.Height = height;
}
success = double.TryParse(txtMaxWidth.Text, out width);
if (success)
{
Parameters.Width = width;
}
success = int.TryParse(txtMaxVarNameLength.Text, out maxColumnLength);
if (success)
{
if (maxColumnLength > 64)
{
maxColumnLength = 64;
}
Parameters.MaxColumnLength = maxColumnLength;
}
success = int.TryParse(txtMaxRows.Text, out maxrows);
if (success)
{
Parameters.MaxRows = maxrows;
}
List <string> listFields = new List <string>();
if (lvVariables.SelectedItems.Count > 0)
{
foreach (FieldInfo fieldInfo in lvVariables.SelectedItems)
{
if (!string.IsNullOrEmpty(fieldInfo.Name))
{
listFields.Add(fieldInfo.Name);
}
}
}
listFields.Sort();
if ((Gadget as LineListControl).IsHostedByEnter)
{
//if (dashboardHelper.IsUsingEpiProject && checkboxAllowUpdates.IsChecked == true)
//{
if (listFields.Contains("UniqueKey"))
{
listFields.Remove("UniqueKey");
}
listFields.Add("UniqueKey");
//}
}
foreach (string field in listFields)
{
Parameters.ColumnNames.Add(field);
}
Parameters.SortColumnsByTabOrder = checkboxTabOrder.IsChecked.Value;
Parameters.UsePromptsForColumnNames = checkboxUsePrompts.IsChecked.Value;
Parameters.ShowColumnHeadings = checkboxColumnHeaders.IsChecked.Value;
Parameters.ShowLineColumn = checkboxLineColumn.IsChecked.Value;
Parameters.ShowNullLabels = checkboxShowNulls.IsChecked.Value;
Parameters.ShowCommentLegalLabels = checkboxListLabels.IsChecked.Value;
if (checkboxUsePrompts.IsChecked == true)
{
inputVariableList.Add("usepromptsforcolumnnames", "true");
}
else
{
inputVariableList.Add("usepromptsforcolumnnames", "false");
}
if (lbxSortOrder.Items.Count > 0)
{
foreach (string item in lbxSortOrder.Items)
{
if (!string.IsNullOrEmpty(item))
{
//string baseStr = item;
//if (baseStr.EndsWith("(ascending)"))
//{
// baseStr = "[" + baseStr.Remove(baseStr.Length - 12) + "] ASC";
//}
//if (baseStr.EndsWith("(descending)"))
//{
// baseStr = "[" + baseStr.Remove(baseStr.Length - 13) + "] DESC";
//}
//inputVariableList.Add(baseStr, "sortfield");
string baseStr = item;
if (baseStr.EndsWith("(ascending)"))
{
baseStr = baseStr.Remove(baseStr.Length - 12);
Parameters.SortVariables.Add(baseStr, SortOrder.Ascending);
}
if (baseStr.EndsWith("(descending)"))
{
baseStr = baseStr.Remove(baseStr.Length - 13);
Parameters.SortVariables.Add(baseStr, SortOrder.Descending);
}
}
}
}
if (cmbGroupField.SelectedIndex >= 0)
{
Parameters.PrimaryGroupField = cmbGroupField.SelectedItem.ToString();
}
if (cmbSecondaryGroupField.SelectedIndex >= 0)
{
Parameters.SecondaryGroupField = cmbSecondaryGroupField.SelectedItem.ToString();
}
if (StrataGridList.Count >= 1)
{
Grid grid = StrataGridList[0];
SortedDictionary <int, string> sortColumnDictionary = new SortedDictionary <int, string>();
foreach (UIElement element in grid.Children)
{
if (Grid.GetRow(element) == 0 && element is TextBlock)
{
TextBlock txtColumnName = element as TextBlock;
//columnOrder.Add(txtColumnName.Text);
sortColumnDictionary.Add(Grid.GetColumn(element), txtColumnName.Text);
}
}
ColumnOrder = new List <string>();
foreach (KeyValuePair <int, string> kvp in sortColumnDictionary)
{
ColumnOrder.Add(kvp.Value);
}
if (ColumnOrder.Count == listFields.Count || ColumnOrder.Count == (listFields.Count + 1))
{
bool same = true;
foreach (string s in listFields)
{
if (!ColumnOrder.Contains(s))
{
same = false;
}
}
if (same)
{
WordBuilder wb = new WordBuilder("^");
foreach (string s in ColumnOrder)
{
wb.Add(s);
}
inputVariableList.Add("customusercolumnsort", wb.ToString());
}
else
{
ColumnOrder = new List <string>();
}
}
else
{
ColumnOrder = new List <string>();
}
}
Parameters.InputVariableList = inputVariableList;
}
/// <summary>
/// Used to generate the list of variables and options for the GadgetParameters object
/// </summary>
protected override void CreateInputVariableList()
{
// Set data filters!
this.DataFilters = RowFilterControl.DataFilters;
//Parameters.ColumnNames used for the "Additional Variables to Display"
Parameters.ColumnNames = new List <string>();
Parameters.KeyColumnNames = new List <string>();
Parameters.SortVariables = new Dictionary <string, SortOrder>();
Parameters.GadgetTitle = txtTitle.Text;
Parameters.GadgetDescription = txtDesc.Text;
double height = 0;
double width = 0;
bool success = double.TryParse(txtMaxHeight.Text, out height);
if (success)
{
Parameters.Height = height;
}
success = double.TryParse(txtMaxWidth.Text, out width);
if (success)
{
Parameters.Width = width;
}
List <string> listFields = new List <string>();
if (lvVariables.SelectedItems.Count > 0)
{
foreach (FieldInfo fieldInfo in lvVariables.SelectedItems)
{
if (!string.IsNullOrEmpty(fieldInfo.Name))
{
listFields.Add(fieldInfo.Name);
}
}
}
listFields.Sort();
foreach (string field in listFields)
{
if (!string.IsNullOrEmpty(field) && !Parameters.KeyColumnNames.Contains(field))
{
Parameters.KeyColumnNames.Add(field);
}
}
List <string> displayListFields = new List <string>();
if (lvDisplayVariables.SelectedItems.Count > 0)
{
foreach (FieldInfo fieldInfo in lvDisplayVariables.SelectedItems)
{
if (!string.IsNullOrEmpty(fieldInfo.Name))
{
displayListFields.Add(fieldInfo.Name);
}
}
}
displayListFields.Sort();
foreach (string field in displayListFields)
{
Parameters.ColumnNames.Add(field);
}
Parameters.SortColumnsByTabOrder = checkboxTabOrder.IsChecked.Value;
Parameters.UseFieldPrompts = checkboxUsePrompts.IsChecked.Value;
Parameters.ShowColumnHeadings = checkboxColumnHeaders.IsChecked.Value;
Parameters.ShowLineColumn = checkboxLineColumn.IsChecked.Value;
Parameters.ShowNullLabels = checkboxShowNulls.IsChecked.Value;
Parameters.ShowCommentLegalLabels = checkboxListLabels.IsChecked.Value;
if (lbxSortOrder.Items.Count > 0)
{
foreach (string item in lbxSortOrder.Items)
{
if (!string.IsNullOrEmpty(item))
{
//string baseStr = item;
//if (baseStr.EndsWith("(ascending)"))
//{
// baseStr = "[" + baseStr.Remove(baseStr.Length - 12) + "] ASC";
//}
//if (baseStr.EndsWith("(descending)"))
//{
// baseStr = "[" + baseStr.Remove(baseStr.Length - 13) + "] DESC";
//}
//inputVariableList.Add(baseStr, "sortfield");
string baseStr = item;
if (baseStr.EndsWith("(ascending)"))
{
baseStr = baseStr.Remove(baseStr.Length - 12);
Parameters.SortVariables.Add(baseStr, SortOrder.Ascending);
}
if (baseStr.EndsWith("(descending)"))
{
baseStr = baseStr.Remove(baseStr.Length - 13);
Parameters.SortVariables.Add(baseStr, SortOrder.Descending);
}
}
}
}
if (cmbGroupField.SelectedIndex >= 0)
{
if (!string.IsNullOrEmpty(cmbGroupField.SelectedItem.ToString()))
{
Parameters.PrimaryGroupField = cmbGroupField.SelectedItem.ToString();
}
}
if (cmbSecondaryGroupField.SelectedIndex >= 0)
{
if (!string.IsNullOrEmpty(cmbSecondaryGroupField.SelectedItem.ToString()))
{
Parameters.SecondaryGroupField = cmbSecondaryGroupField.SelectedItem.ToString();
}
}
if (StrataGridList.Count >= 1)
{
Grid grid = StrataGridList[0];
SortedDictionary <int, string> sortColumnDictionary = new SortedDictionary <int, string>();
foreach (UIElement element in grid.Children)
{
if (Grid.GetRow(element) == 0 && element is TextBlock)
{
TextBlock txtColumnName = element as TextBlock;
//columnOrder.Add(txtColumnName.Text);
sortColumnDictionary.Add(Grid.GetColumn(element), txtColumnName.Text);
}
}
ColumnOrder = new List <string>();
foreach (KeyValuePair <int, string> kvp in sortColumnDictionary)
{
ColumnOrder.Add(kvp.Value);
}
if (ColumnOrder.Count == listFields.Count || ColumnOrder.Count == (listFields.Count + 1))
{
bool same = true;
foreach (string s in listFields)
{
if (!ColumnOrder.Contains(s))
{
same = false;
}
}
if (same)
{
WordBuilder wb = new WordBuilder("^");
foreach (string s in ColumnOrder)
{
wb.Add(s);
}
//inputVariableList.Add("customusercolumnsort", wb.ToString());
Parameters.CustomUserColumnSort.Add(wb.ToString());
}
else
{
ColumnOrder = new List <string>();
}
}
else
{
ColumnOrder = new List <string>();
}
}
//Parameters.InputVariableList = inputVariableList;
}
/// <summary>
/// Builds the command text
/// </summary>
protected override void GenerateCommand()
{
Configuration config = Configuration.GetNewInstance();
KeyValuePairCollection kvPairs = new KeyValuePairCollection();
kvPairs.Delimiter = CharLiterals.SPACE;
if ((cmbYesAs.Text != config.Settings.RepresentationOfYes) || (hasYesAsChanged))
{
kvPairs.Add(new KeyValuePair(ShortHands.YES, Util.InsertInDoubleQuotes(cmbYesAs.Text)));
}
if ((cmbNoAs.Text != config.Settings.RepresentationOfNo) || (hasNoAsChanged))
{
kvPairs.Add(new KeyValuePair(ShortHands.NO, Util.InsertInDoubleQuotes(cmbNoAs.Text)));
}
if ((cmbMissingAs.Text != config.Settings.RepresentationOfMissing) || (hasMissingAsChanged))
{
kvPairs.Add(new KeyValuePair(ShortHands.MISSING, Util.InsertInDoubleQuotes(cmbMissingAs.Text)));
}
if ((cbxGraphics.Checked != config.Settings.ShowGraphics) || (hasShowGraphicChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.FREQGRAPH,
Epi.Util.GetShortHand(cbxGraphics.Checked)));
}
if ((cbxHyperlinks.Checked != config.Settings.ShowHyperlinks) || (hasShowHyperlinkChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.HYPERLINKS,
Epi.Util.GetShortHand(cbxHyperlinks.Checked)));
}
if ((cbxPercents.Checked != config.Settings.ShowPercents) || (hasShowPercentsChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.PERCENTS,
Epi.Util.GetShortHand(cbxPercents.Checked)));
}
if ((cbxSelectCriteria.Checked != config.Settings.ShowSelection) || (hasShowSelectChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.SELECT,
Epi.Util.GetShortHand(cbxSelectCriteria.Checked)));
}
if ((cbxShowPrompt.Checked != config.Settings.ShowCompletePrompt) || (hasShowPromptChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.SHOWPROMPTS,
Epi.Util.GetShortHand(cbxShowPrompt.Checked)));
}
if ((cbxTablesOutput.Checked != config.Settings.ShowTables) || (hasShowTablesChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.TABLES,
Epi.Util.GetShortHand(cbxTablesOutput.Checked)));
}
if ((cbxIncludeMissing.Checked != config.Settings.IncludeMissingValues) || (hasIncludeMissingChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.MISSING,
Epi.Util.GetShortHand(cbxIncludeMissing.Checked)));
}
if (hasStatisticsLevelChanged)
{
StatisticsLevel levelIdSelected = (StatisticsLevel)short.Parse(WinUtil.GetSelectedRadioButton(gbxStatistics).Tag.ToString());
string levelTagSelected = AppData.Instance.GetStatisticsLevelById(levelIdSelected).Tag;
kvPairs.Add(new KeyValuePair(CommandNames.STATISTICS, levelTagSelected));
}
if (hasProcessRecordsChanged)
{
RecordProcessingScope scopeIdSelected = (RecordProcessingScope)short.Parse(WinUtil.GetSelectedRadioButton(gbxProcessRecords).Tag.ToString());
string scopeTagSelected = AppData.Instance.GetRecordProcessessingScopeById(scopeIdSelected).Tag;
kvPairs.Add(new KeyValuePair(CommandNames.PROCESS, scopeTagSelected));
}
WordBuilder command = new WordBuilder();
//Generate command only if there are key value pairs
if (kvPairs.Count > 0)
{
if (!this.isDialogMode)
{
command.Append(CommandNames.SET);
}
command.Append(kvPairs.ToString());
if (!this.isDialogMode)
{
command.Append(" END-SET\n");
}
this.CommandText = command.ToString();
}
else
{
this.CommandText = string.Empty;
}
}
/// <summary>
/// Generates Xml representation of this gadget
/// </summary>
/// <param name="doc">The Xml docment</param>
/// <returns>XmlNode</returns>
public override XmlNode Serialize(XmlDocument doc)
{
CreateInputVariableList();
Dictionary<string, string> inputVariableList = GadgetOptions.InputVariableList;
string freqVar = string.Empty;
string strataVar = string.Empty;
string weightVar = string.Empty;
string sort = string.Empty;
bool allValues = false;
bool showConfLimits = true;
bool showCumulativePercent = true;
bool includeMissing = false;
WordBuilder wb = new WordBuilder(",");
if (inputVariableList.ContainsKey("freqvar"))
{
freqVar = inputVariableList["freqvar"].Replace("<", "<");
}
if (inputVariableList.ContainsKey("stratavar"))
{
strataVar = inputVariableList["stratavar"].Replace("<", "<");
}
if (inputVariableList.ContainsKey("weightvar"))
{
weightVar = inputVariableList["weightvar"].Replace("<", "<");
}
if (inputVariableList.ContainsKey("sort"))
{
sort = inputVariableList["sort"];
}
if (inputVariableList.ContainsKey("allvalues"))
{
allValues = bool.Parse(inputVariableList["allvalues"]);
}
if (inputVariableList.ContainsKey("showconflimits"))
{
showConfLimits = bool.Parse(inputVariableList["showconflimits"]);
}
if (inputVariableList.ContainsKey("showcumulativepercent"))
{
showCumulativePercent = bool.Parse(inputVariableList["showcumulativepercent"]);
}
if (inputVariableList.ContainsKey("includemissing"))
{
includeMissing = bool.Parse(inputVariableList["includemissing"]);
}
int precision = 4;
CustomOutputHeading = headerPanel.Text;
CustomOutputDescription = descriptionPanel.Text;
string xmlString =
"<mainVariable>" + freqVar + "</mainVariable>";
if(GadgetOptions.StrataVariableNames.Count == 1)
{
xmlString = xmlString + "<strataVariable>" + GadgetOptions.StrataVariableNames[0].Replace("<", "<") + "</strataVariable>";
}
else if (GadgetOptions.StrataVariableNames.Count > 1)
{
xmlString = xmlString + "<strataVariables>";
foreach (string strataVariable in this.GadgetOptions.StrataVariableNames)
{
xmlString = xmlString + "<strataVariable>" + strataVariable.Replace("<", "<") + "</strataVariable>";
}
xmlString = xmlString + "</strataVariables>";
}
xmlString = xmlString + "<weightVariable>" + weightVar + "</weightVariable>" +
"<sort>" + sort + "</sort>" +
"<allValues>" + allValues + "</allValues>" +
"<precision>" + precision.ToString() + "</precision>" +
"<showListLabels> </showListLabels>" +
"<useFieldPrompts> </useFieldPrompts>" +
"<columnsToShow>" + wb.ToString() + "</columnsToShow>" +
"<includeMissing>" + includeMissing + "</includeMissing>" +
"<customHeading>" + CustomOutputHeading.Replace("<", "<") + "</customHeading>" +
"<customDescription>" + CustomOutputDescription.Replace("<", "<") + "</customDescription>" +
"<customCaption>" + CustomOutputCaption + "</customCaption>";
xmlString = xmlString + SerializeAnchors();
System.Xml.XmlElement element = doc.CreateElement("frequencyGadget");
element.InnerXml = xmlString;
element.AppendChild(SerializeFilters(doc));
System.Xml.XmlAttribute locationY = doc.CreateAttribute("top");
System.Xml.XmlAttribute locationX = doc.CreateAttribute("left");
System.Xml.XmlAttribute collapsed = doc.CreateAttribute("collapsed");
System.Xml.XmlAttribute type = doc.CreateAttribute("gadgetType");
locationY.Value = Canvas.GetTop(this).ToString("F0");
locationX.Value = Canvas.GetLeft(this).ToString("F0");
collapsed.Value = IsCollapsed.ToString();
type.Value = "EpiDashboard.WordCloudControl";
element.Attributes.Append(locationY);
element.Attributes.Append(locationX);
element.Attributes.Append(collapsed);
element.Attributes.Append(type);
return element;
}
/// <summary>
/// Generates command text
/// </summary>
protected override void GenerateCommand()
{
string expression = txtRecAffected.Text.Trim();
#region preconditions
if (expression.Trim() != "*" && !ValidExpression(expression)) //dcs0 8/1/2008
{
throw new GeneralException(string.Format(SharedStrings.INVALID_EXPRESSION, expression));
}
if (EpiInterpreter.Context.CurrentRead.File.Contains(".xlsx") || EpiInterpreter.Context.CurrentRead.File.Contains(".xls") || EpiInterpreter.Context.CurrentRead.File.Contains("FMT=Delimited"))
{
MessageBox.Show("Deletion not supported for this datasource", "DELETE RECORDS");
Close();
throw new GeneralException(string.Format(SharedStrings.INVALID_DATA_SOURCE, "Excel"));
}
#endregion Preconditions
WordBuilder command = new WordBuilder();
command.Append(CommandNames.DELETE);
if(expression.Trim() != "*") command.Append("(");
command.Append(expression);
if (expression.Trim() != "*") command.Append(")");
if (rdbPermDeletion.Checked)
{
command.Append(CommandNames.PERMANENT);
}
if (cbkRunSilent.Checked)
{
command.Append(CommandNames.RUNSILENT);
}
CommandText = command.ToString();
}
void computeWorker_DoWork(object sender, DoWorkEventArgs e)
{
Result result = new Result();
EpiDataHelper DataHelper = e.Argument as EpiDataHelper;
if (DataHelper != null && DataHelper.Project != null && DataHelper.Project.CollectedData != null)
{
IDbDriver db = DataHelper.Project.CollectedData.GetDatabase();
int total = (from caseVM in DataHelper.CaseCollection
where caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
string format = "P1";
int count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.ConfirmedAcute && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.ConfirmedAcuteCount = count.ToString();
result.ConfirmedAcutePercent = ((double)count / (double)total).ToString(format);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.ConfirmedConvalescent && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.ConfirmedConvalescentCount = count.ToString();
result.ConfirmedConvalescentPercent = ((double)count / (double)total).ToString(format);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.NotCase && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.NegativeCount = count.ToString();
result.NegativePercent = ((double)count / (double)total).ToString(format);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.Indeterminate && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.IndeterminateCount = count.ToString();
result.IndeterminatePercent = ((double)count / (double)total).ToString(format);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.NeedsFollowUpSample && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.NeedsFollowUpCount = count.ToString();
result.NeedsFollowUpPercent = ((double)count / (double)total).ToString(format);
Epi.Fields.RenderableField finalLabClassField = DataHelper.CaseForm.Fields["FinalLabClass"] as Epi.Fields.RenderableField;
Epi.Fields.RenderableField epiCaseDefField = DataHelper.CaseForm.Fields["EpiCaseDef"] as Epi.Fields.RenderableField;
if (finalLabClassField != null && epiCaseDefField != null && finalLabClassField.Page != null && epiCaseDefField.Page != null)
{
string finalLabClassTableName = finalLabClassField.Page.TableName;
string epiCaseClassTableName = epiCaseDefField.Page.TableName;
string queryText = "";
if (db.ToString().ToLower().Contains("sql"))
{
queryText = "select count(*) from " + finalLabClassTableName + " AS crf INNER JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((crf.FinalLabClass = '' OR crf.FinalLabClass is null) AND (crfEpiCaseClass.EpiCaseDef <> '4'))";
}
else
{
queryText = "select count(*) from ((" + finalLabClassTableName + " AS crf) INNER JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId) INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((crf.FinalLabClass = '' OR crf.FinalLabClass is null) AND (crfEpiCaseClass.EpiCaseDef <> '4'))";
}
Query selectQuery = db.CreateQuery(queryText);
count = (int)db.ExecuteScalar(selectQuery);
if (db.ToString().ToLower().Contains("sql"))
{
queryText = "select crfEpiCaseClass.ID from " + finalLabClassTableName + " AS crf INNER JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((crf.FinalLabClass = '' OR crf.FinalLabClass is null) AND (crfEpiCaseClass.EpiCaseDef <> '4'))";
}
else
{
queryText = "select crfEpiCaseClass.ID from ((" + finalLabClassTableName + " AS crf) INNER JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId) INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((crf.FinalLabClass = '' OR crf.FinalLabClass is null) AND (crfEpiCaseClass.EpiCaseDef <> '4'))";
}
selectQuery = db.CreateQuery(queryText);
DataTable dt = db.Select(selectQuery);
WordBuilder wb = new WordBuilder(",");
foreach (DataRow row in dt.Rows)
{
wb.Add(row["ID"].ToString());
}
result.PendingIDs = wb.ToString();
result.PendingCount = count.ToString();
result.PendingPercent = ((double)count / (double)total).ToString(format);
if (db.ToString().ToLower().Contains("sql"))
{
queryText = "select count(*) from CaseInformationForm AS crf LEFT JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId LEFT JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((lrf.GlobalRecordId = '' OR lrf.GlobalRecordId is null) AND (crfEpiCaseClass.EpiCaseDef <> '4') AND crf.RecStatus = 1)";
}
else
{
queryText = "select count(*) from ((CaseInformationForm AS crf) LEFT JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId) LEFT JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((lrf.GlobalRecordId = '' OR lrf.GlobalRecordId is null) AND (crfEpiCaseClass.EpiCaseDef <> '4') AND crf.RecStatus = 1)";
}
selectQuery = db.CreateQuery(queryText);
count = (int)db.ExecuteScalar(selectQuery);
result.NotSampledCount = count.ToString();
result.NotSampledPercent = ((double)count / (double)total).ToString(format);
e.Result = result;
}
else
{
throw new InvalidOperationException("FinalLabClass and EpiCaseDef must both be non-null fields in computeWorker_doWork in LabClassAllPatients.xaml.cs");
}
}
}
/// <summary>
/// Generates command text
/// </summary>
protected override void GenerateCommand()
{
WordBuilder command = new WordBuilder();
command.Append(CommandNames.SUMMARIZE);
//Append aggregates
foreach (string item in lbxVar.Items)
{
command.Append(item);
}
//Append output table
command.Append(CommandNames.TO);
command.Append(txtOutput.Text.Trim());
//Append strata variables
if (lbxGroupBy.Items.Count > 0)
{
command.Append(CommandNames.STRATAVAR);
command.Append(StringLiterals.EQUAL);
foreach (string item in lbxGroupBy.Items)
{
command.Append(item);
}
}
//Append weighted variables
if (cmbWeight.SelectedItem != null && !string.IsNullOrEmpty(cmbWeight.SelectedItem.ToString()))
{
command.Append(CommandNames.WEIGHTVAR);
command.Append(StringLiterals.EQUAL);
command.Append(cmbWeight.SelectedItem.ToString());
}
CommandText = command.ToString();
}
/// <summary>
/// Get Patterns in metaPatterns table.
/// </summary>
/// <returns>DataTable of pattern information from metaPatterns table.</returns>
public DataTable GetPatterns()
{
try
{
WordBuilder columnNames = new WordBuilder(StringLiterals.COMMA);
columnNames.Append("PatternId");
columnNames.Append("DataTypeId");
columnNames.Append("Expression");
columnNames.Append("Mask");
columnNames.Append("FormattedExpression");
Query query = db.CreateQuery("select" + Util.InsertIn(columnNames.ToString(), StringLiterals.SPACE) +
Util.InsertIn( "from", StringLiterals.SPACE) + db.InsertInEscape("metaPatterns"));
return db.Select(query);
}
catch (Exception ex)
{
throw new GeneralException("Could not retrieve patterns", ex);
}
}
/// <summary>
/// Creates a new blank row for a given form's base table and all of its page tables.
/// </summary>
/// <param name="gridField">The grid field where the row should be added.</param>
/// <param name="guid">The Guid value to use for the row.</param>
/// <param name="urid">The unique row id.</param>
/// <param name="fkey">The foreign key for the row.</param>
protected virtual void CreateNewBlankGridRow(GridField gridField, string guid, string urid, string fkey)
{
#region Input Validation
if (string.IsNullOrEmpty(guid)) { throw new ArgumentNullException("guid"); }
if (string.IsNullOrEmpty(urid)) { throw new ArgumentNullException("urid"); }
if (string.IsNullOrEmpty(fkey)) { throw new ArgumentNullException("fkey"); }
if (gridField == null) { throw new ArgumentNullException("gridField"); }
#endregion // Input Validation
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
IDbDriver db = DestinationProject.CollectedData.GetDatabase();
StringBuilder sb = new StringBuilder();
sb.Append("insert into " + db.InsertInEscape(gridField.TableName) + " ");
WordBuilder fields = new WordBuilder(",");
fields.Append("[GlobalRecordId], [UniqueRowId], [FKEY]");
sb.Append("(" + fields.ToString() + ") values (");
WordBuilder values = new WordBuilder(",");
values.Append("'" + guid + "', '" + urid + "', '" + fkey + "'");
sb.Append(values.ToString());
sb.Append(") ");
Epi.Data.Query insertQuery = db.CreateQuery(sb.ToString());
if (DestinationProject.CollectedDataDriver.ToLower().Contains("epi.data.office"))
{
IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters);
object obj = command.ExecuteNonQuery();
}
else
{
db.ExecuteNonQuery(insertQuery);
}
}
/// <summary>
/// Gets table column names for a view.
/// </summary>
/// <param name="viewId">Id of the <see cref="Epi.View"/></param>
/// <returns>string</returns>
public string GetTableColumnNames(int viewId)
{
try
{
Query selectQuery = db.CreateQuery("select [Name] from [metaFields] " +
"where [ViewId] = @ViewID and " +
"[FieldTypeId] not in (2,13,15,20) " +
"order by [Name]");
selectQuery.Parameters.Add(new QueryParameter("@ViewID", DbType.Int32, viewId));
WordBuilder list = new WordBuilder(StringLiterals.COMMA);
DataTable columns = db.Select(selectQuery);
foreach (DataRow row in columns.Rows)
{
list.Add(row[ColumnNames.NAME].ToString());
}
return list.ToString();
}
catch (Exception ex)
{
throw new GeneralException("Could not retrieve table column names", ex);
}
}
/// <summary>
/// Creates a new blank row for a given form's base table and all of its page tables.
/// </summary>
/// <param name="form">The form where the row should be added.</param>
/// <param name="guid">The Guid value to use for the row.</param>
/// <param name="fkey">The foreign key for the row.</param>
/// <param name="firstSaveId">The user ID of the first person that saved this record.</param>
/// <param name="firstSaveTime">The time when the record was first saved.</param>
/// <param name="lastSaveId">The user ID of the last person that saved this record.</param>
/// <param name="lastSaveTime">The time when the record was last saved.</param>
protected virtual void CreateNewBlankRow(View form, string guid, string fkey = "", string firstSaveId = "", string lastSaveId = "", DateTime? firstSaveTime = null, DateTime? lastSaveTime = null)
{
#region Input Validation
if (string.IsNullOrEmpty(guid)) { throw new ArgumentNullException("guid"); }
if (form == null) { throw new ArgumentNullException("form"); }
#endregion // Input Validation
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
IDbDriver db = DestinationProject.CollectedData.GetDatabase();
StringBuilder sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(db.InsertInEscape(form.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.SPACE);
WordBuilder fields = new WordBuilder(",");
fields.Append("[GlobalRecordId]");
if (!string.IsNullOrEmpty(fkey)) { fields.Append("[FKEY]"); }
if (!string.IsNullOrEmpty(firstSaveId)) { fields.Append("[FirstSaveLogonName]"); }
if (!string.IsNullOrEmpty(lastSaveId)) { fields.Append("[LastSaveLogonName]"); }
if (firstSaveTime.HasValue) { fields.Append("[FirstSaveTime]"); }
if (lastSaveTime.HasValue) { fields.Append("[LastSaveTime]"); }
sb.Append("(" + fields.ToString() + ")");
sb.Append(" values (");
List<QueryParameter> parameters = new List<QueryParameter>();
WordBuilder values = new WordBuilder(",");
values.Append("'" + guid + "'");
if (!string.IsNullOrEmpty(fkey))
{
values.Append("@FKEY");
parameters.Add(new QueryParameter("@FKEY", DbType.String, fkey));
}
if (!string.IsNullOrEmpty(firstSaveId))
{
values.Append("@FirstSaveLogonName");
parameters.Add(new QueryParameter("@FirstSaveLogonName", DbType.String, firstSaveId));
}
if (!string.IsNullOrEmpty(lastSaveId))
{
values.Append("@LastSaveLogonName");
parameters.Add(new QueryParameter("@LastSaveLogonName", DbType.String, lastSaveId));
}
if (firstSaveTime.HasValue)
{
values.Append("@FirstSaveTime");
parameters.Add(new QueryParameter("@FirstSaveTime", DbType.DateTime, firstSaveTime));
}
if (lastSaveTime.HasValue)
{
values.Append("@LastSaveTime");
parameters.Add(new QueryParameter("@LastSaveTime", DbType.DateTime, lastSaveTime));
}
sb.Append(values.ToString());
sb.Append(") ");
Epi.Data.Query insertQuery = db.CreateQuery(sb.ToString());
insertQuery.Parameters = parameters;
if (DestinationProject.CollectedDataDriver.ToLower().Contains("epi.data.office"))
{
IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters);
object obj = command.ExecuteNonQuery();
}
else
{
db.ExecuteNonQuery(insertQuery);
}
foreach (Page page in form.Pages)
{
sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(db.InsertInEscape(page.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.SPACE);
sb.Append("([GlobalRecordId])");
sb.Append(" values (");
sb.Append("'" + guid + "'");
sb.Append(") ");
insertQuery = db.CreateQuery(sb.ToString());
if (DestinationProject.CollectedDataDriver.ToLower().Contains("epi.data.office"))
{
IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters);
object obj = command.ExecuteNonQuery();
}
else
{
db.ExecuteNonQuery(insertQuery);
}
}
}
/// <summary>
/// Initiates an export of the data to the specified file.
/// </summary>
public override void Export()
{
DataView dv = this.DataView;
DataTable table = new DataTable(); // dv.ToTable(false);
WordBuilder wb = new WordBuilder(SEPARATOR);
StreamWriter sw = null;
if (IncludeDeletedRecords == false)
{
if (table.Columns.Contains("RecStatus") || table.Columns.Contains("RECSTATUS"))
{
dv.RowFilter = "[RecStatus] > 0";
}
}
try
{
sw = File.CreateText(fileName);
if (ColumnSortOrder != ImportExport.ColumnSortOrder.None || !exportAllFields)
{
table = dv.ToTable(false);
if (view != null)
{
ImportExportHelper.OrderColumns(table, ColumnSortOrder, view);
}
else
{
ImportExportHelper.OrderColumns(table, ColumnSortOrder);
}
List <DataColumn> columnsToRemove = new List <DataColumn>();
foreach (DataColumn dc in table.Columns)
{
bool found = false;
foreach (Epi.Data.TableColumn tc in columnList)
{
if (tc.Name.Equals(dc.ColumnName))
{
found = true;
break;
}
}
if (!found)
{
columnsToRemove.Add(dc);
}
}
foreach (DataColumn dc in columnsToRemove)
{
table.Columns.Remove(dc);
}
}
foreach (DataColumn dc in table.Columns)
{
wb.Add(dc.ColumnName);
}
sw.WriteLine(wb.ToString());
rowsExported = 0;
int totalRows = 0;
//if (useTabOrder || !exportAllFields)
//{
totalRows = table.Rows.Count;
foreach (DataRow row in table.Rows)
{
wb = new WordBuilder(SEPARATOR);
for (int i = 0; i < table.Columns.Count; i++)
{
string rowValue = row[i].ToString().Replace("\r\n", " ");
if (rowValue.Contains(",") || rowValue.Contains("\""))
{
rowValue = rowValue.Replace("\"", "\"\"");
rowValue = Util.InsertIn(rowValue, "\"");
}
wb.Add(rowValue);
}
sw.WriteLine(wb);
rowsExported++;
if (rowsExported % 500 == 0)
{
//this.Dispatcher.BeginInvoke(new SetGadgetStatusHandler(RequestUpdateStatusMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
//RequestUpdateStatusMessage(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
//SetProgressAndStatus(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
OnSetStatusMessageAndProgressCount(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
}
}
//}
//else
//{
// totalRows = dv.Count;
// foreach (DataRowView rowView in dv)
// {
// wb = new WordBuilder(SEPARATOR);
// for (int i = 0; i < table.Columns.Count; i++)
// {
// DataRow row = rowView.Row;
// string rowValue = row[i].ToString().Replace("\r\n", " ");
// if (rowValue.Contains(",") || rowValue.Contains("\""))
// {
// rowValue = rowValue.Replace("\"", "\"\"");
// rowValue = Util.InsertIn(rowValue, "\"");
// }
// wb.Add(rowValue);
// }
// sw.WriteLine(wb);
// rowsExported++;
// if (rowsExported % 500 == 0)
// {
// //this.Dispatcher.BeginInvoke(new SetGadgetStatusHandler(RequestUpdateStatusMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
// //RequestUpdateStatusMessage(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
// SetProgressAndStatus(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported);
// }
// }
//}
//this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetStatusMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS, rowsExported.ToString()));
OnSetStatusMessage(string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS, rowsExported.ToString()));
}
catch (Exception ex)
{
OnSetStatusMessage(ex.Message);
//this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), ex.Message);
}
finally
{
if (sw != null)
{
sw.Close();
sw.Dispose();
sw = null;
}
//stopWatch.Stop();
//System.Diagnostics.Debug.Print("File I/O Export thread finished in " + stopWatch.Elapsed.ToString());
}
}
/// <summary>
/// Begins the process of importing grid data
/// </summary>
/// <param name="gridField">The grid that will receive the data</param>
/// <param name="gridRecords">The data to be imported</param>
/// <param name="destinationGuids">A dictionary of GUIDs in the destination project; the key represents the GUID itself and the value (either true or false) represents whether or not to process that record</param>
protected virtual void ImportGridRecordsToGridTable(GridField gridField, List<PackageFieldData> gridRecords, Dictionary<string, bool> destinationGuids)
{
if (Conn.State != ConnectionState.Open) { Conn.Open(); }
IDbDriver db = DestinationProject.CollectedData.GetDatabase();
if (ResetProgress != null) { ResetProgress(); }
double total = gridRecords.Count;
string lastGuid = string.Empty;
List<string> fieldsInQuery = new List<string>();
WordBuilder setFieldText = new WordBuilder(StringLiterals.COMMA);
List<QueryParameter> fieldValueParams = new List<QueryParameter>();
PackageFieldData lastRecord = new PackageFieldData();
lastRecord.FieldName = "__--LastRecord--__";
lastRecord.RecordGUID = string.Empty;
gridRecords.Add(lastRecord);
for (int i = 0; i < gridRecords.Count; i++)
{
PackageFieldData fieldData = gridRecords[i];
if (i % 200 == 0)
{
if (StatusChanged != null) { StatusChanged(string.Format(UnpackagerStrings.IMPORT_GRID_PROGRESS, (i / total).ToString("P0"))); }
if (UpdateProgress != null) { UpdateProgress((i / total) * 100); }
}
string guid = fieldData.RecordGUID;
bool isLast = fieldData.Equals(lastRecord);
if (isLast || fieldsInQuery.Contains(fieldData.FieldName))
{
// run the update with the fields we currently have...
string updateHeader = string.Empty;
string whereClause = string.Empty;
StringBuilder sb = new StringBuilder();
// Build the Update statement which will be reused
sb.Append(SqlKeyWords.UPDATE);
sb.Append(StringLiterals.SPACE);
sb.Append(db.InsertInEscape(gridField.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(db.InsertInEscape(ColumnNames.UNIQUE_ROW_ID));
sb.Append(StringLiterals.EQUAL);
sb.Append("'");
sb.Append(lastGuid);
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);
if (fieldsInQuery.Count > 0 && fieldValueParams.Count > 0)
{
Query updateQuery = db.CreateQuery(updateHeader + StringLiterals.SPACE + setFieldText.ToString() + StringLiterals.SPACE + whereClause);
updateQuery.Parameters = fieldValueParams;
if (DestinationProject.CollectedDataDriver.ToLower().Contains("epi.data.office"))
{
IDbCommand command = GetCommand(updateQuery.SqlStatement, Conn, updateQuery.Parameters);
object obj = command.ExecuteNonQuery();
}
else
{
db.ExecuteNonQuery(updateQuery);
}
}
setFieldText = new WordBuilder(StringLiterals.COMMA);
fieldValueParams = new List<QueryParameter>();
fieldsInQuery = new List<string>();
if (isLast) { break; }
}
if (destinationGuids.ContainsKey(guid) && destinationGuids[guid] == true)
{
fieldsInQuery.Add(fieldData.FieldName);
//setFieldText.Append(fieldData.FieldName + " = " + "@" + fieldData.FieldName);
QueryParameter parameter = GetQueryParameterForField(fieldData, gridField);
if (parameter != null)
{
setFieldText.Append(fieldData.FieldName + " = " + "@" + fieldData.FieldName);
fieldValueParams.Add(parameter);
}
lastGuid = guid;
}
}
}
/// <summary>
/// Generates the command text
/// </summary>
protected override void GenerateCommand()
{
WordBuilder command = new WordBuilder();
command.Append(CommandNames.GRAPH);
if (listBoxVariables.Items.Count > 0)
{
foreach (string item in listBoxVariables.Items)
{
command.Append(FieldNameNeedsBrackets(item) ? Util.InsertInSquareBrackets(item) : item);
}
}
else
{
command.Append(FieldNameNeedsBrackets(comboBoxMainVariable.Text) ? Util.InsertInSquareBrackets(comboBoxMainVariable.Text) : comboBoxMainVariable.Text);
}
if (comboBoxBarOfEachValueOf.Text != string.Empty)
{
command.Append(string.Format("* {0}", FieldNameNeedsBrackets(comboBoxBarOfEachValueOf.Text) ? Util.InsertInSquareBrackets(comboBoxBarOfEachValueOf.Text) : comboBoxBarOfEachValueOf.Text));
}
if (comboBoxGraphType.Text != string.Empty)
{
if (comboBoxGraphType.Text == "EAR (Early Aberration Reporting)")
{
command.Append("GRAPHTYPE=\"EAR\"");
}
else
{
command.Append(string.Format("GRAPHTYPE=\"{0}\"", comboBoxGraphType.Text));
}
}
if (comboBoxStrataVar.Text != string.Empty)
{
command.Append(string.Format("STRATAVAR={0}", FieldNameNeedsBrackets(comboBoxStrataVar.Text) ? Util.InsertInSquareBrackets(comboBoxStrataVar.Text) : comboBoxStrataVar.Text));
}
if (comboBoxWeightVar.Text != string.Empty)
{
string weightVar = FieldNameNeedsBrackets(comboBoxWeightVar.Text) ? Util.InsertInSquareBrackets(comboBoxWeightVar.Text) : comboBoxWeightVar.Text;
switch (comboBoxShowValueOf.Text)
{
case "Average":
weightVar = string.Format("AVG({0})", weightVar);
break;
case "Count":
weightVar = string.Format("COUNT({0})", weightVar);
break;
case "Sum":
weightVar = string.Format("SUM({0})", weightVar);
break;
case "Minimum":
weightVar = string.Format("MIN({0})", weightVar);
break;
case "Maximum":
weightVar = string.Format("MAX({0})", weightVar);
break;
case "Count %":
weightVar = string.Format("PERCENT({0})", weightVar);
break;
case "Sum %":
weightVar = string.Format("SUMPCT({0})", weightVar);
break;
}
command.Append(string.Format("WEIGHTVAR={0}", weightVar));
}
else
{
if (comboBoxShowValueOf.Text == "Count %")
{
command.Append("WEIGHTVAR=PERCENT()");
}
}
if (textBoxTitle.Text != string.Empty)
{
command.Append(string.Format("TITLETEXT=\"{0}\"", textBoxTitle.Text));
}
if (textBoxXAxisLabel.Text != string.Empty)
{
command.Append(string.Format("XTITLE=\"{0}\"", textBoxXAxisLabel.Text));
}
if (textBoxYAxisLabel.Text != string.Empty)
{
command.Append(string.Format("YTITLE=\"{0}\"", textBoxYAxisLabel.Text));
}
if (comboBoxDateFormat.Text != string.Empty)
{
command.Append(string.Format("DATEFORMAT=\"{0}\"", comboBoxDateFormat.Text));
}
if (comboBoxIntervalType.Text != string.Empty)
{
command.Append(string.Format("INTERVAL=\"{0} {1}\"", textBoxInterval.Text, comboBoxIntervalType.Text));
}
if (startDateTime.Checked)
{
command.Append(string.Format("STARTFROM=\"{0}\"", startDateTime.Value.ToString()));
}
CommandText = command.ToString();
}
/// <summary>
/// Begins the process of importing records from the data package into the destination form
/// </summary>
/// <param name="form">The form that will receive the data</param>
/// <param name="formNode">The XmlNode representing the form</param>
/// <param name="records">The data to be imported</param>
protected virtual void ImportRecords(View form, XmlNode formNode, List<PackageFieldData> records)
{
ImportInfo.RecordsAppended.Add(form, 0);
ImportInfo.RecordsUpdated.Add(form, 0);
IDbDriver destinationDb = DestinationProject.CollectedData.GetDatabase();
Dictionary<string, bool> destinationGuids = new Dictionary<string, bool>();
Dictionary<Dictionary<Field, object>, bool> destinationKeyValues = new Dictionary<Dictionary<Field, object>, bool>();
using (IDataReader baseTableReader = destinationDb.GetTableDataReader(form.TableName))
{
while (baseTableReader.Read())
{
destinationGuids.Add(baseTableReader["GlobalRecordId"].ToString(), true);
}
}
#region Custom Keys
if (this.IsUsingCustomMatchkeys)
{
WordBuilder wb = new WordBuilder(",");
foreach(Field field in KeyFields)
{
wb.Add(field.Name);
}
Query selectQuery = destinationDb.CreateQuery("SELECT " + wb.ToString() + " " + form.FromViewSQL);
using (IDataReader keyTableReader = destinationDb.ExecuteReader(selectQuery))
{
while (keyTableReader.Read())
{
Dictionary<Field, object> keys = new Dictionary<Field, object>();
foreach (Field field in KeyFields)
{
keys.Add(field, keyTableReader[field.Name]);
//destinationKeyValues.Add(keyTableReader["GlobalRecordId"].ToString(), true);
}
destinationKeyValues.Add(keys, true);
}
}
}
#endregion // Custom Keys
foreach (XmlNode recordNode in formNode.SelectSingleNode("Data").ChildNodes)
{
if (recordNode.Name.ToLower().Equals("record"))
{
string guid = string.Empty;
string fkey = string.Empty;
string firstSaveId = string.Empty;
string lastSaveId = string.Empty;
DateTime? firstSaveTime = null;
DateTime? lastSaveTime = null;
foreach (XmlAttribute attrib in recordNode.Attributes)
{
if (attrib.Name.ToLower().Equals("id")) guid = attrib.Value;
if (attrib.Name.ToLower().Equals("fkey")) fkey = attrib.Value;
if (attrib.Name.ToLower().Equals("firstsaveuserid")) firstSaveId = attrib.Value;
if (attrib.Name.ToLower().Equals("lastsaveuserid")) lastSaveId = attrib.Value;
if (attrib.Name.ToLower().Equals("firstsavetime")) firstSaveTime = new DateTime(Convert.ToInt64(attrib.Value));
if (attrib.Name.ToLower().Equals("lastsavetime")) lastSaveTime = new DateTime(Convert.ToInt64(attrib.Value));
}
if (!destinationGuids.ContainsKey(guid))
{
if (Append)
{
destinationGuids.Add(guid, true);
CreateNewBlankRow(form, guid, fkey, firstSaveId, lastSaveId, firstSaveTime, lastSaveTime);
ImportInfo.TotalRecordsAppended++;
ImportInfo.RecordsAppended[form]++;
}
}
else
{
if (!Update)
{
destinationGuids[guid] = false;
}
else
{
ImportInfo.TotalRecordsUpdated++;
ImportInfo.RecordsUpdated[form]++;
}
}
}
}
ImportRecordsToPageTables(form, records, destinationGuids);
}
/// <summary>
/// Helper method for the CreateFromXml method. Loads a list of row filter conditions from XML.
/// </summary>
/// <param name="element">XmlElement to process</param>
private void CreateRowFiltersFromXML(XmlElement element)
{
if (element.Name.ToLower().Equals("rowfilter"))
{
foreach (XmlElement childElement in element.ChildNodes)
{
if (childElement.Name.ToLower().Equals("rowfilterquery"))
{
selectQuery = sourceProject.CollectedData.GetDbDriver().CreateQuery(childElement.InnerText.Replace(">", ">").Replace("<", "<"));
}
else if (childElement.Name.ToLower().Equals("rowfilterparameters"))
{
foreach (XmlElement gcElement in childElement.ChildNodes)
{
System.Data.DbType dbType = System.Data.DbType.String;
string name = "";
object value = null;
foreach (XmlElement ggcElement in gcElement.ChildNodes)
{
switch (ggcElement.Name.ToLower())
{
case "dbtype":
dbType = ((DbType)Int32.Parse(ggcElement.InnerText));
break;
case "name":
name = ggcElement.InnerText;
break;
case "value":
value = ggcElement.InnerText;
break;
}
}
QueryParameter queryParameter = new QueryParameter(name, dbType, value);
if (selectQuery != null)
{
selectQuery.Parameters.Add(queryParameter);
}
}
}
else if (childElement.Name.ToLower().Equals("rowfilterconditions"))
{
List<IRowFilterCondition> conditions = new List<IRowFilterCondition>();
foreach (XmlElement grandChildElement in childElement.ChildNodes)
{
if (grandChildElement.HasAttribute("filterType"))
{
string strType = grandChildElement.Attributes["filterType"].Value + ",Epi.ImportExport";
Type filterType = Type.GetType(strType, false, true);
if (filterType != null)
{
IRowFilterCondition condition = (IRowFilterCondition)Activator.CreateInstance(filterType, new object[] { });
condition.CreateFromXml(grandChildElement);
conditions.Add(condition);
}
}
}
this.rowFilterConditions = conditions;
}
}
}
string filter = String.Empty;
if (this.rowFilterConditions.Count > 0)
{
filter = ImportExportSharedStrings.SCRIPT_FILTERS;
WordBuilder wb = new WordBuilder(" and ");
foreach (IRowFilterCondition rfc in this.rowFilterConditions)
{
wb.Add(rfc.Description);
}
filter = filter + wb.ToString();
CallbackAddStatusMessage(filter);
}
}
/// <summary>
/// Generates a FilterCondition object using primitive type inputs
/// </summary>
/// <param name="columnName">The name of the column on which to filter</param>
/// <param name="rawColumnName">The name of the column on which to filter, without brackets</param>
/// <param name="columnType">The data type of the column on which to filter</param>
/// <param name="friendlyOperand">The friendly operand</param>
/// <param name="friendlyValue">The friendly value</param>
/// <returns>FilterCondition</returns>
public FilterCondition GenerateFilterCondition(string columnName, string rawColumnName, string columnType, string friendlyOperand, string friendlyValue)
{
#region Input Validation
if (string.IsNullOrEmpty(columnName))
{
throw new ApplicationException("Column name cannot be empty.");
}
else if (string.IsNullOrEmpty(friendlyOperand))
{
throw new ApplicationException("Friendly operand cannot be empty.");
}
else if (string.IsNullOrEmpty(columnType))
{
throw new ApplicationException("Column type cannot be empty.");
}
else if (!operandTypes.ContainsKey(friendlyOperand))
{
throw new ApplicationException("Operand not found in dictionary.");
}
#endregion // Input Validation
Configuration config = dashboardHelper.Config;
Epi.Fields.Field field = null;
foreach (DataRow fieldRow in dashboardHelper.FieldTable.Rows)
{
if (fieldRow["columnname"].Equals(rawColumnName))
{
if (fieldRow["epifieldtype"] is Epi.Fields.Field)
{
field = fieldRow["epifieldtype"] as Epi.Fields.Field;
}
break;
}
}
string operand = string.Empty;
string value = string.Empty;
string friendlyCondition = string.Empty;
operand = operandTypes[friendlyOperand];
switch (columnType)
{
case "System.DateTime":
value = "#" + friendlyValue.Trim() + "#";
break;
case "System.String":
if (operand.Equals(operandTypes[SharedStrings.FRIENDLY_OPERATOR_IS_ANY_OF]) ||
operand.Equals(operandTypes[SharedStrings.FRIENDLY_OPERATOR_IS_NOT_ANY_OF]))
{
string[] values = friendlyValue.Split(',');
WordBuilder wb = new WordBuilder(",");
foreach (string str in values)
{
wb.Add("'" + str.Trim().Replace("'", "''") + "'");
}
value = "(" + wb.ToString() + ")";
}
else
{
value = "'" + friendlyValue.Trim().Replace("'", "''") + "'";
if (value.Equals("''") && operand.Equals(operandTypes[SharedStrings.FRIENDLY_OPERATOR_MISSING]))
{
operand = operandTypes[SharedStrings.FRIENDLY_OPERATOR_EQUAL_TO];
}
else if (value.Equals("''") && operand.Equals(operandTypes[SharedStrings.FRIENDLY_OPERATOR_NOT_MISSING]))
{
operand = operandTypes[SharedStrings.FRIENDLY_OPERATOR_NOT_EQUAL_TO];
}
}
break;
case "System.Boolean":
if (friendlyValue.Equals(config.Settings.RepresentationOfYes))
{
value = "1";
}
else
{
value = "0";
}
break;
case "System.Int16":
case "System.Byte":
if (field != null && field is Epi.Fields.YesNoField)
{
if (friendlyValue.Equals(config.Settings.RepresentationOfYes))
{
value = "1";
}
else if (friendlyValue.Equals(config.Settings.RepresentationOfNo))
{
value = "0";
}
else if (friendlyValue.Equals(config.Settings.RepresentationOfMissing))
{
value = string.Empty;
}
}
else
{
value = friendlyValue.Trim();
}
break;
default:
value = friendlyValue.Trim();
break;
}
if (friendlyValue.Equals(config.Settings.RepresentationOfMissing))
{
value = string.Empty;
friendlyValue = string.Empty;
if (friendlyOperand.Equals(SharedStrings.FRIENDLY_OPERATOR_NOT_MISSING) || friendlyOperand.Equals(SharedStrings.FRIENDLY_OPERATOR_NOT_EQUAL_TO))
{
operand = operandTypes[SharedStrings.FRIENDLY_OPERATOR_NOT_MISSING]; // "is not null";
}
else if (friendlyOperand.Equals(SharedStrings.FRIENDLY_OPERATOR_MISSING))
{
operand = operandTypes[SharedStrings.FRIENDLY_OPERATOR_MISSING]; // "is null";
value = string.Empty;
}
else
{
operand = operandTypes[SharedStrings.FRIENDLY_OPERATOR_MISSING]; // "is null";
}
}
if (operand.Equals("like"))
{
value = value.Trim().Replace('*', '%');
}
if (operand.Equals(operandTypes[SharedStrings.FRIENDLY_OPERATOR_NOT_MISSING]) || operand.Equals(operandTypes[SharedStrings.FRIENDLY_OPERATOR_MISSING]))
{
value = string.Empty;
}
if (columnType.Equals("System.DateTime") && !string.IsNullOrEmpty(value))
{
DateTime dt = DateTime.Parse(friendlyValue, System.Globalization.CultureInfo.InvariantCulture);
friendlyValue = dt.ToString("d", System.Globalization.CultureInfo.CurrentCulture);
}
friendlyCondition = string.Format(SharedStrings.FRIENDLY_CONDITION_DATA_FILTER, columnName, friendlyOperand, friendlyValue);
FilterCondition newCondition = new FilterCondition(friendlyCondition, columnName, rawColumnName, columnType, operand, friendlyOperand, value, friendlyValue);
return(newCondition);
}
/// <summary>
/// Used to generate the list of variables and options for the GadgetParameters object
/// </summary>
protected override void CreateInputVariableList()
{
// Set data filters!
this.DataFilters = RowFilterControl.DataFilters;
Dictionary<string, string> inputVariableList = new Dictionary<string, string>();
Parameters.ColumnNames = new List<string>();
Parameters.SortVariables = new Dictionary<string, SortOrder>();
Parameters.GadgetTitle = txtTitle.Text;
Parameters.GadgetDescription = txtDesc.Text;
double height = 0;
double width = 0;
bool success = double.TryParse(txtMaxHeight.Text, out height);
if (success)
{
Parameters.Height = height;
}
success = double.TryParse(txtMaxWidth.Text, out width);
if (success)
{
Parameters.Width = width;
}
List<string> listFields = new List<string>();
if (lvVariables.SelectedItems.Count > 0)
{
foreach (FieldInfo fieldInfo in lvVariables.SelectedItems)
{
if (!string.IsNullOrEmpty(fieldInfo.Name))
{
listFields.Add(fieldInfo.Name);
}
}
}
listFields.Sort();
if ((Gadget as LineListControl).IsHostedByEnter)
{
//if (dashboardHelper.IsUsingEpiProject && checkboxAllowUpdates.IsChecked == true)
//{
if (listFields.Contains("UniqueKey"))
listFields.Remove("UniqueKey");
listFields.Add("UniqueKey");
//}
}
foreach (string field in listFields)
{
Parameters.ColumnNames.Add(field);
}
Parameters.SortColumnsByTabOrder = checkboxTabOrder.IsChecked.Value;
Parameters.UsePromptsForColumnNames = checkboxUsePrompts.IsChecked.Value;
Parameters.ShowColumnHeadings = checkboxColumnHeaders.IsChecked.Value;
Parameters.ShowLineColumn = checkboxLineColumn.IsChecked.Value;
Parameters.ShowNullLabels = checkboxShowNulls.IsChecked.Value;
Parameters.ShowCommentLegalLabels = checkboxListLabels.IsChecked.Value;
if (lbxSortOrder.Items.Count > 0)
{
foreach (string item in lbxSortOrder.Items)
{
if (!string.IsNullOrEmpty(item))
{
//string baseStr = item;
//if (baseStr.EndsWith("(ascending)"))
//{
// baseStr = "[" + baseStr.Remove(baseStr.Length - 12) + "] ASC";
//}
//if (baseStr.EndsWith("(descending)"))
//{
// baseStr = "[" + baseStr.Remove(baseStr.Length - 13) + "] DESC";
//}
//inputVariableList.Add(baseStr, "sortfield");
string baseStr = item;
if (baseStr.EndsWith("(ascending)"))
{
baseStr = baseStr.Remove(baseStr.Length - 12);
Parameters.SortVariables.Add(baseStr, SortOrder.Ascending);
}
if (baseStr.EndsWith("(descending)"))
{
baseStr = baseStr.Remove(baseStr.Length - 13);
Parameters.SortVariables.Add(baseStr, SortOrder.Descending);
}
}
}
}
if (cmbGroupField.SelectedIndex >= 0)
{
if (!string.IsNullOrEmpty(cmbGroupField.SelectedItem.ToString()))
{
Parameters.PrimaryGroupField = cmbGroupField.SelectedItem.ToString();
}
}
if (cmbSecondaryGroupField.SelectedIndex >= 0)
{
if (!string.IsNullOrEmpty(cmbSecondaryGroupField.SelectedItem.ToString()))
{
Parameters.SecondaryGroupField = cmbSecondaryGroupField.SelectedItem.ToString();
}
}
if (StrataGridList.Count >= 1)
{
Grid grid = StrataGridList[0];
SortedDictionary<int, string> sortColumnDictionary = new SortedDictionary<int, string>();
foreach (UIElement element in grid.Children)
{
if (Grid.GetRow(element) == 0 && element is TextBlock)
{
TextBlock txtColumnName = element as TextBlock;
//columnOrder.Add(txtColumnName.Text);
sortColumnDictionary.Add(Grid.GetColumn(element), txtColumnName.Text);
}
}
ColumnOrder = new List<string>();
foreach (KeyValuePair<int, string> kvp in sortColumnDictionary)
{
ColumnOrder.Add(kvp.Value);
}
if (ColumnOrder.Count == listFields.Count || ColumnOrder.Count == (listFields.Count + 1))
{
bool same = true;
foreach (string s in listFields)
{
if (!ColumnOrder.Contains(s))
{
same = false;
}
}
if (same)
{
WordBuilder wb = new WordBuilder("^");
foreach (string s in ColumnOrder)
{
wb.Add(s);
}
inputVariableList.Add("customusercolumnsort", wb.ToString());
}
else
{
ColumnOrder = new List<string>();
}
}
else
{
ColumnOrder = new List<string>();
}
}
Parameters.InputVariableList = inputVariableList;
}
/// <summary>
/// Converts the gadget's output to Html
/// </summary>
/// <returns></returns>
public override string ToHTML(string htmlFileName = "", int count = 0)
{
if (IsCollapsed) return string.Empty;
StringBuilder htmlBuilder = new StringBuilder();
CustomOutputHeading = headerPanel.Text;
CustomOutputDescription = descriptionPanel.Text;
if (CustomOutputHeading == null || (string.IsNullOrEmpty(CustomOutputHeading) && !CustomOutputHeading.Equals("(none)")))
{
htmlBuilder.AppendLine("<h2 class=\"gadgetHeading\">Duplicates List</h2>");
}
else if (CustomOutputHeading != "(none)")
{
htmlBuilder.AppendLine("<h2 class=\"gadgetHeading\">" + CustomOutputHeading + "</h2>");
}
htmlBuilder.AppendLine("<p class=\"gadgetOptions\"><small>");
htmlBuilder.AppendLine("<br />");
if (Parameters.ColumnNames.Count > 0)
{
WordBuilder wb = new WordBuilder(", ");
foreach (string s in Parameters.ColumnNames)
{
wb.Add(s);
}
htmlBuilder.AppendLine("<em>Strata variable(s):</em> <strong>" + wb.ToString() + "</strong>");
htmlBuilder.AppendLine("<br />");
}
htmlBuilder.AppendLine("<br />");
htmlBuilder.AppendLine("</small></p>");
if (!string.IsNullOrEmpty(CustomOutputDescription))
{
htmlBuilder.AppendLine("<p class=\"gadgetsummary\">" + CustomOutputDescription + "</p>");
}
if (!string.IsNullOrEmpty(messagePanel.Text) && messagePanel.Visibility == Visibility.Visible)
{
htmlBuilder.AppendLine("<p><small><strong>" + messagePanel.Text + "</strong></small></p>");
}
if (!string.IsNullOrEmpty(infoPanel.Text) && infoPanel.Visibility == Visibility.Visible)
{
htmlBuilder.AppendLine("<p><small><strong>" + infoPanel.Text + "</strong></small></p>");
}
foreach (Grid grid in this.StrataGridList)
{
string gridName = grid.Tag.ToString();
string summaryText = "This tables represents fields of duplicate values. ";
htmlBuilder.AppendLine("<div style=\"height: 7px;\"></div>");
htmlBuilder.AppendLine("<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" summary=\"" + summaryText + "\">");
if (string.IsNullOrEmpty(CustomOutputCaption))
{
if (Parameters.ColumnNames.Count > 0)
{
htmlBuilder.AppendLine("<caption>" + grid.Tag + "</caption>");
}
}
else
{
htmlBuilder.AppendLine("<caption>" + CustomOutputCaption + "</caption>");
}
double barWidth = 0.0;
foreach (UIElement control in grid.Children)
{
if (control is TextBlock)
{
int rowNumber = Grid.GetRow(control);
int columnNumber = Grid.GetColumn(control);
string tableDataTagOpen = "<td>";
string tableDataTagClose = "</td>";
if (rowNumber == 0)
{
tableDataTagOpen = "<th>";
tableDataTagClose = "</th>";
}
if (columnNumber == 0)
{
if (((double)rowNumber) % 2.0 == 1)
{
htmlBuilder.AppendLine("<tr class=\"altcolor\">");
}
else
{
htmlBuilder.AppendLine("<tr>");
}
}
if (columnNumber == 0 && rowNumber > 0)
{
tableDataTagOpen = "<td class=\"value\">";
}
string value = ((TextBlock)control).Text;
if (string.IsNullOrEmpty(value))
{
value = " ";
}
string formattedValue = value;
if (rowNumber == grid.RowDefinitions.Count - 1)
{
formattedValue = "<span class=\"total\">" + value + "</span>";
}
htmlBuilder.AppendLine(tableDataTagOpen + formattedValue + tableDataTagClose);
if (columnNumber == 2 && rowNumber > 0)
{
barWidth = 0;
double.TryParse(value.Trim().TrimEnd('%').Trim(), out barWidth);
}
if (columnNumber >= grid.ColumnDefinitions.Count - 2)
{
if (rowNumber > 0)
{
htmlBuilder.AppendLine("<td class=\"value\"><div class=\"percentBar\" style=\"width: " + ((int)barWidth * 2).ToString() + "px;\"></td>");
}
else
{
htmlBuilder.AppendLine(tableDataTagOpen + tableDataTagClose);
}
htmlBuilder.AppendLine("</tr>");
}
}
}
htmlBuilder.AppendLine("</table>");
}
return htmlBuilder.ToString();
}
/// <summary>
/// Generates a Query object that can be executed to return only the GlobalRecordId values matching the filter conditions. This method can only be used for Epi Info 7 forms.
/// </summary>
/// <param name="form">The form to process</param>
/// <returns>Query</returns>
public virtual Query GetGuidSelectQuery(View form)
{
// pre
Contract.Requires(form != null);
// post
Contract.Ensures(Contract.Result <Query>() != null);
Contract.Ensures(!String.IsNullOrEmpty(Contract.Result <Query>().SqlStatement));
// assumes
Contract.Assume(DataDriver != null);
if (DataDriver == null)
{
throw new InvalidOperationException();
}
if (form == null)
{
throw new ArgumentNullException("form");
}
string baseTableName = "t";
string fromClause = form.FromViewSQL;
Contract.Assert(!String.IsNullOrEmpty(fromClause));
WordBuilder columns = new WordBuilder(", ");
columns.Append("[" + baseTableName + "].[GlobalRecordId]");
columns.Append("[" + baseTableName + "].[FKEY]");
columns.Append("[" + baseTableName + "].[RECSTATUS]");
if (DataDriver.ColumnExists(baseTableName, "FirstSaveLogonName"))
{
columns.Append("[" + baseTableName + "].[FirstSaveLogonName]");
columns.Append("[" + baseTableName + "].[FirstSaveTime]");
columns.Append("[" + baseTableName + "].[LastSaveLogonName]");
columns.Append("[" + baseTableName + "].[LastSaveTime]");
}
string recStatusComparison = "[" + baseTableName + "].[RECSTATUS] = 1";
if (RecordProcessingScope == Epi.RecordProcessingScope.Both)
{
recStatusComparison = "[" + baseTableName + "].[RECSTATUS] >= 0";
}
else if (RecordProcessingScope == Epi.RecordProcessingScope.Deleted)
{
recStatusComparison = "[" + baseTableName + "].[RECSTATUS] = 0";
}
string fullSql = "SELECT " + columns.ToString() + " " + fromClause + " WHERE " + recStatusComparison + " AND (";
//filterSql.Append(fullSql);
string logicalOperatorString = " AND ";
if (_conditionJoinType == ConditionJoinTypes.Or)
{
logicalOperatorString = " OR ";
}
WordBuilder filterSql = new WordBuilder(logicalOperatorString);
foreach (IRowFilterCondition rowFc in _rowFilterConditions)
{
filterSql.Append(rowFc.Sql);
}
string fSql = fullSql + " " + filterSql.ToString() + ")";
Query selectQuery = DataDriver.CreateQuery(fSql);
Contract.Assert(selectQuery != null);
Contract.Assert(!String.IsNullOrEmpty(selectQuery.SqlStatement));
foreach (IRowFilterCondition rowFc in _rowFilterConditions)
{
selectQuery.Parameters.Add(rowFc.Parameter);
}
return(selectQuery);
}
/// <summary>
/// Used to generate the list of variables and options for the GadgetParameters object
/// </summary>
private void CreateInputVariableList()
{
Dictionary<string, string> inputVariableList = new Dictionary<string, string>();
GadgetOptions.MainVariableName = string.Empty;
GadgetOptions.WeightVariableName = string.Empty;
GadgetOptions.StrataVariableNames = new List<string>();
GadgetOptions.CrosstabVariableName = string.Empty;
List<string> listFields = new List<string>();
if (lbxFields.SelectedItems.Count > 0)
{
foreach (string item in lbxFields.SelectedItems)
{
if (!string.IsNullOrEmpty(item))
{
listFields.Add(item);
}
}
}
listFields.Sort();
if (IsHostedByEnter)
{
//if (dashboardHelper.IsUsingEpiProject && checkboxAllowUpdates.IsChecked == true)
//{
if (listFields.Contains("UniqueKey"))
listFields.Remove("UniqueKey");
listFields.Add("UniqueKey");
//}
}
foreach (string field in listFields)
{
inputVariableList.Add(field, "listfield");
}
if (!inputVariableList.ContainsKey("sortcolumnsbytaborder"))
{
if (checkboxTabOrder.IsChecked == true)
{
inputVariableList.Add("sortcolumnsbytaborder", "true");
}
else
{
inputVariableList.Add("sortcolumnsbytaborder", "false");
}
}
if (!inputVariableList.ContainsKey("usepromptsforcolumnnames"))
{
if (checkboxUsePrompts.IsChecked == true)
{
inputVariableList.Add("usepromptsforcolumnnames", "true");
}
else
{
inputVariableList.Add("usepromptsforcolumnnames", "false");
}
}
if (!inputVariableList.ContainsKey("showcolumnheadings"))
{
if (checkboxColumnHeaders.IsChecked == true)
{
inputVariableList.Add("showcolumnheadings", "true");
}
else
{
inputVariableList.Add("showcolumnheadings", "false");
}
}
if (!inputVariableList.ContainsKey("showlinecolumn"))
{
if (checkboxLineColumn.IsChecked == true)
{
inputVariableList.Add("showlinecolumn", "true");
}
else
{
inputVariableList.Add("showlinecolumn", "false");
}
}
if (!inputVariableList.ContainsKey("shownulllabels"))
{
if (checkboxShowNulls.IsChecked == true)
{
inputVariableList.Add("shownulllabels", "true");
}
else
{
inputVariableList.Add("shownulllabels", "false");
}
}
if (checkboxListLabels.IsChecked == true)
{
GadgetOptions.ShouldShowCommentLegalLabels = true;
}
else
{
GadgetOptions.ShouldShowCommentLegalLabels = false;
}
if (lbxSortFields.Items.Count > 0)
{
foreach (string item in lbxSortFields.Items)
{
if (!string.IsNullOrEmpty(item))
{
string baseStr = item;
if (baseStr.EndsWith("(ascending)"))
{
baseStr = "[" + baseStr.Remove(baseStr.Length - 12) + "] ASC";
}
if (baseStr.EndsWith("(descending)"))
{
baseStr = "[" + baseStr.Remove(baseStr.Length - 13) + "] DESC";
}
inputVariableList.Add(baseStr, "sortfield");
}
}
}
if (cbxGroupField.SelectedIndex >= 0)
{
if (!string.IsNullOrEmpty(cbxGroupField.SelectedItem.ToString()))
{
GadgetOptions.StrataVariableNames.Add(cbxGroupField.SelectedItem.ToString());
}
}
if (StrataGridList.Count >= 1)
{
Grid grid = StrataGridList[0];
SortedDictionary<int, string> sortColumnDictionary = new SortedDictionary<int, string>();
foreach (UIElement element in grid.Children)
{
if (Grid.GetRow(element) == 0 && element is TextBlock)
{
TextBlock txtColumnName = element as TextBlock;
//columnOrder.Add(txtColumnName.Text);
sortColumnDictionary.Add(Grid.GetColumn(element), txtColumnName.Text);
}
}
columnOrder = new List<string>();
foreach (KeyValuePair<int, string> kvp in sortColumnDictionary)
{
columnOrder.Add(kvp.Value);
}
if (columnOrder.Count == listFields.Count || columnOrder.Count == (listFields.Count + 1))
{
bool same = true;
foreach (string s in listFields)
{
if (!columnOrder.Contains(s))
{
same = false;
}
}
if (same)
{
WordBuilder wb = new WordBuilder("^");
foreach (string s in columnOrder)
{
wb.Add(s);
}
inputVariableList.Add("customusercolumnsort", wb.ToString());
}
else
{
columnOrder = new List<string>();
}
}
else
{
columnOrder = new List<string>();
}
}
inputVariableList.Add("maxcolumns", MaxColumns.ToString());
inputVariableList.Add("maxrows", MaxRows.ToString());
GadgetOptions.ShouldIncludeFullSummaryStatistics = false;
GadgetOptions.InputVariableList = inputVariableList;
}
/// <summary>
/// Generates Xml representation of this gadget
/// </summary>
/// <param name="doc">The Xml docment</param>
/// <returns>XmlNode</returns>
public override XmlNode Serialize(XmlDocument doc)
{
CreateInputVariableList();
Dictionary<string, string> inputVariableList = GadgetOptions.InputVariableList;
string freqVar = string.Empty;
string strataVar = string.Empty;
string weightVar = string.Empty;
string sort = string.Empty;
bool allValues = false;
bool showConfLimits = true;
bool showCumulativePercent = true;
bool includeMissing = false;
WordBuilder wb = new WordBuilder(",");
if (lbxColumns.SelectedItems.Contains("Frequency")) wb.Add("1");
if (lbxColumns.SelectedItems.Contains("Percent")) wb.Add("2");
if (lbxColumns.SelectedItems.Contains("Cumulative Percent")) wb.Add("3");
if (lbxColumns.SelectedItems.Contains("95% CI Lower")) wb.Add("4");
if (lbxColumns.SelectedItems.Contains("95% CI Upper")) wb.Add("5");
if (lbxColumns.SelectedItems.Contains("Percent bars")) wb.Add("6");
if (inputVariableList.ContainsKey("freqvar"))
{
freqVar = inputVariableList["freqvar"].Replace("<", "<");
}
if (inputVariableList.ContainsKey("stratavar"))
{
strataVar = inputVariableList["stratavar"].Replace("<", "<");
}
if (inputVariableList.ContainsKey("weightvar"))
{
weightVar = inputVariableList["weightvar"].Replace("<", "<");
}
if (inputVariableList.ContainsKey("sort"))
{
sort = inputVariableList["sort"];
}
if (inputVariableList.ContainsKey("allvalues"))
{
allValues = bool.Parse(inputVariableList["allvalues"]);
}
if (inputVariableList.ContainsKey("showconflimits"))
{
showConfLimits = bool.Parse(inputVariableList["showconflimits"]);
}
if (inputVariableList.ContainsKey("showcumulativepercent"))
{
showCumulativePercent = bool.Parse(inputVariableList["showcumulativepercent"]);
}
if (inputVariableList.ContainsKey("includemissing"))
{
includeMissing = bool.Parse(inputVariableList["includemissing"]);
}
CustomOutputHeading = headerPanel.Text;
CustomOutputDescription = descriptionPanel.Text;
string xmlString =
"<mainVariable>" + freqVar + "</mainVariable>" +
"<strataVariable>" + strataVar + "</strataVariable>" +
"<weightVariable>" + weightVar + "</weightVariable>" +
"<sort>" + sort + "</sort>" +
"<allValues>" + allValues + "</allValues>" +
"<showListLabels>" + checkboxCommentLegalLabels.IsChecked + "</showListLabels>" +
"<useFieldPrompts>" + checkboxUsePrompts.IsChecked.ToString() + "</useFieldPrompts>" +
"<columnsToShow>" + wb.ToString() + "</columnsToShow>" +
"<includeMissing>" + includeMissing + "</includeMissing>" +
"<customHeading>" + CustomOutputHeading.Replace("<", "<") + "</customHeading>" +
"<customDescription>" + CustomOutputDescription.Replace("<", "<") + "</customDescription>" +
"<customCaption>" + CustomOutputCaption + "</customCaption>";
System.Xml.XmlElement element = doc.CreateElement("frequencyGadget");
element.InnerXml = xmlString;
element.AppendChild(SerializeFilters(doc));
System.Xml.XmlAttribute locationY = doc.CreateAttribute("top");
System.Xml.XmlAttribute locationX = doc.CreateAttribute("left");
System.Xml.XmlAttribute collapsed = doc.CreateAttribute("collapsed");
System.Xml.XmlAttribute type = doc.CreateAttribute("gadgetType");
locationY.Value = Canvas.GetTop(this).ToString("F0");
locationX.Value = Canvas.GetLeft(this).ToString("F0");
collapsed.Value = "false"; // currently no way to collapse the gadget, so leave this 'false' for now
type.Value = "Epi.WPF.Dashboard.Gadgets.Analysis.FrequencyControl";
element.Attributes.Append(locationY);
element.Attributes.Append(locationX);
element.Attributes.Append(collapsed);
element.Attributes.Append(type);
return element;
}
/// <summary>
/// Generates Xml representation of this gadget
/// </summary>
/// <param name="doc">The Xml docment</param>
/// <returns>XmlNode</returns>
public override XmlNode Serialize(XmlDocument doc)
{
Dictionary<string, string> inputVariableList = GadgetOptions.InputVariableList;
string groupVar = string.Empty;
if (cbxGroupField.SelectedItem != null)
{
groupVar = cbxGroupField.SelectedItem.ToString().Replace("<", "<");
}
CustomOutputHeading = headerPanel.Text;
//CustomOutputDescription = txtOutputDescription.Text.Replace("<", "<");
CustomOutputDescription = descriptionPanel.Text; //txtOutputDescription.Text.Replace("<", "<");
string xmlString =
"<groupVariable>" + groupVar + "</groupVariable>" +
"<maxRows>" + MaxRows.ToString() + "</maxRows>" +
"<maxColumnNameLength>" + MaxColumnLength.ToString() + "</maxColumnNameLength>" +
"<sortColumnsByTabOrder>" + checkboxTabOrder.IsChecked.ToString() + "</sortColumnsByTabOrder>" +
"<useFieldPrompts>" + checkboxUsePrompts.IsChecked.ToString() + "</useFieldPrompts>" +
"<showListLabels>" + checkboxListLabels.IsChecked + "</showListLabels>" +
"<showLineColumn>" + checkboxLineColumn.IsChecked.ToString() + "</showLineColumn>" +
"<showColumnHeadings>" + checkboxColumnHeaders.IsChecked.ToString() + "</showColumnHeadings>" +
"<showNullLabels>" + checkboxShowNulls.IsChecked.ToString() + "</showNullLabels>" +
//"<alternatingRowColors>" + checkboxAltRowColors.IsChecked.ToString() + "</alternatingRowColors>" +
//"<allowUpdates>" + checkboxAllowUpdates.IsChecked.ToString() + "</allowUpdates>" +
"<customHeading>" + CustomOutputHeading.Replace("<", "<") + "</customHeading>" +
"<customDescription>" + CustomOutputDescription.Replace("<", "<") + "</customDescription>" +
"<customCaption>" + CustomOutputCaption + "</customCaption>";
xmlString = xmlString + SerializeAnchors();
if (inputVariableList.ContainsKey("customusercolumnsort"))
{
string columns = inputVariableList["customusercolumnsort"];
xmlString = xmlString + "<customusercolumnsort>" + columns + "</customusercolumnsort>";
}
else if (columnOrder != null && columnOrder.Count > 0) // when user has re-ordered columns but not refreshed
{
WordBuilder wb = new WordBuilder("^");
for (int i = 0; i < columnOrder.Count; i++)
{
wb.Add(columnOrder[i]);
}
xmlString = xmlString + "<customusercolumnsort>" + wb.ToString() + "</customusercolumnsort>";
}
System.Xml.XmlElement element = doc.CreateElement("lineListGadget");
element.InnerXml = xmlString;
element.AppendChild(SerializeFilters(doc));
System.Xml.XmlAttribute id = doc.CreateAttribute("id");
System.Xml.XmlAttribute locationY = doc.CreateAttribute("top");
System.Xml.XmlAttribute locationX = doc.CreateAttribute("left");
System.Xml.XmlAttribute collapsed = doc.CreateAttribute("collapsed");
System.Xml.XmlAttribute type = doc.CreateAttribute("gadgetType");
id.Value = this.UniqueIdentifier.ToString();
locationY.Value = Canvas.GetTop(this).ToString("F0");
locationX.Value = Canvas.GetLeft(this).ToString("F0");
collapsed.Value = IsCollapsed.ToString();
type.Value = "EpiDashboard.LineListControl";
element.Attributes.Append(locationY);
element.Attributes.Append(locationX);
element.Attributes.Append(collapsed);
element.Attributes.Append(type);
element.Attributes.Append(id);
if (lbxFields.Items.Count > 0 && lbxFields.SelectedItems.Count > 0)
{
string xmlListItemString = string.Empty;
XmlElement listItemElement = doc.CreateElement("listFields");
foreach (string s in lbxFields.SelectedItems)
{
xmlListItemString = xmlListItemString + "<listField>" + s.Replace("<", "<") + "</listField>";
}
listItemElement.InnerXml = xmlListItemString;
element.AppendChild(listItemElement);
}
if (lbxSortFields.Items.Count > 0)
{
string xmlSortString = string.Empty;
XmlElement sortElement = doc.CreateElement("sortFields");
foreach (string s in lbxSortFields.Items)
{
xmlSortString = xmlSortString + "<sortField>" + s.Replace("<", "<") + "</sortField>";
}
sortElement.InnerXml = xmlSortString;
element.AppendChild(sortElement);
}
return element;
}
/// <summary>
/// Generates the command text
/// </summary>
protected override void GenerateCommand()
{
WordBuilder command = new WordBuilder();
command.Append(CommandNames.GRAPH);
if (listBoxVariables.Items.Count > 0)
{
foreach (string item in listBoxVariables.Items)
{
command.Append(FieldNameNeedsBrackets(item) ? Util.InsertInSquareBrackets(item) : item);
}
}
else
{
command.Append(FieldNameNeedsBrackets(comboBoxMainVariable.Text) ? Util.InsertInSquareBrackets(comboBoxMainVariable.Text) : comboBoxMainVariable.Text);
}
if (comboBoxBarOfEachValueOf.Text != string.Empty)
{
command.Append(string.Format("* {0}", FieldNameNeedsBrackets(comboBoxBarOfEachValueOf.Text) ? Util.InsertInSquareBrackets(comboBoxBarOfEachValueOf.Text) : comboBoxBarOfEachValueOf.Text));
}
if (comboBoxGraphType.Text != string.Empty)
{
if (comboBoxGraphType.Text == "EAR (Early Aberration Reporting)")
{
command.Append("GRAPHTYPE=\"EAR\"");
}
else
{
command.Append(string.Format("GRAPHTYPE=\"{0}\"", comboBoxGraphType.Text));
}
}
if (comboBoxStrataVar.Text != string.Empty)
{
command.Append(string.Format("STRATAVAR={0}", FieldNameNeedsBrackets(comboBoxStrataVar.Text) ? Util.InsertInSquareBrackets(comboBoxStrataVar.Text) : comboBoxStrataVar.Text));
}
if (comboBoxWeightVar.Text != string.Empty)
{
string weightVar = FieldNameNeedsBrackets(comboBoxWeightVar.Text) ? Util.InsertInSquareBrackets(comboBoxWeightVar.Text) : comboBoxWeightVar.Text;
switch (comboBoxShowValueOf.Text)
{
case "Average":
weightVar = string.Format("AVG({0})", weightVar);
break;
case "Count":
weightVar = string.Format("COUNT({0})", weightVar);
break;
case "Sum":
weightVar = string.Format("SUM({0})", weightVar);
break;
case "Minimum":
weightVar = string.Format("MIN({0})", weightVar);
break;
case "Maximum":
weightVar = string.Format("MAX({0})", weightVar);
break;
case "Count %":
weightVar = string.Format("PERCENT({0})", weightVar);
break;
case "Sum %":
weightVar = string.Format("SUMPCT({0})", weightVar);
break;
}
command.Append(string.Format("WEIGHTVAR={0}", weightVar));
}
else
{
if (comboBoxShowValueOf.Text == "Count %")
{
command.Append("WEIGHTVAR=PERCENT()");
}
}
if (textBoxTitle.Text != string.Empty)
{
command.Append(string.Format("TITLETEXT=\"{0}\"", textBoxTitle.Text));
}
if (textBoxXAxisLabel.Text != string.Empty)
{
command.Append(string.Format("XTITLE=\"{0}\"", textBoxXAxisLabel.Text));
}
if (textBoxYAxisLabel.Text != string.Empty)
{
command.Append(string.Format("YTITLE=\"{0}\"", textBoxYAxisLabel.Text));
}
if (comboBoxDateFormat.Text != string.Empty)
{
command.Append(string.Format("DATEFORMAT=\"{0}\"", comboBoxDateFormat.Text));
}
if (comboBoxIntervalType.Text != string.Empty)
{
command.Append(string.Format("INTERVAL=\"{0} {1}\"", textBoxInterval.Text, comboBoxIntervalType.Text));
}
if (startDateTime.Checked)
{
command.Append(string.Format("STARTFROM=\"{0}\"", startDateTime.Value.ToString()));
}
CommandText = command.ToString();
}
/// <summary>
/// Processes a form's base table
/// </summary>
/// <param name="result">The web survey result</param>
/// <param name="destinationView">The destination form</param>
/// <param name="destinationGUIDList">The list of GUIDs that exist in the destination</param>
private void ProcessBaseTable(Dictionary<string, Dictionary<string, WebFieldData>> result, View destinationView, List<string> destinationGUIDList)
{
//sourceView.LoadFirstRecord();
this.BeginInvoke(new SetStatusDelegate(SetStatusMessage), "Processing records on base table...");
int recordsInserted = 0;
int recordsUpdated = 0;
//string sourceTable = sourceView.TableName;
string destinationTable = destinationView.TableName;
foreach (KeyValuePair<string,Dictionary<string, WebFieldData>> surveyAnswer in result)
{
QueryParameter paramRecordStatus = new QueryParameter("@RECSTATUS", DbType.Int32, 1);
if (importWorker.CancellationPending)
{
this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "Import cancelled.");
return;
}
WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA);
WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA);
List<QueryParameter> fieldValueParams = new List<QueryParameter>();
fieldNames.Append("GlobalRecordId");
fieldValues.Append("@GlobalRecordId");
string GUID = surveyAnswer.Key; // sourceReader["GlobalRecordId"].ToString();
string FKEY = string.Empty; // sourceReader["FKEY"].ToString(); // FKEY not needed, no related forms to process
QueryParameter paramFkey = new QueryParameter("@FKEY", DbType.String, FKEY); // don't add this yet
QueryParameter paramGUID = new QueryParameter("@GlobalRecordId", DbType.String, GUID);
fieldValueParams.Add(paramGUID);
if (destinationGUIDList.Contains(GUID.ToUpper()))
{
update = true;
append = false;
}
else
{
append = true;
update = false;
}
if (update)
{
// UPDATE matching records
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(destinationTable));
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);
//if (sourceView.ForeignKeyFieldExists)
if (!string.IsNullOrEmpty(FKEY))
{
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append("FKEY");
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sb.Append(StringLiterals.EQUAL);
sb.Append(StringLiterals.COMMERCIAL_AT);
sb.Append("FKEY");
fieldValueParams.Add(paramFkey);
Query updateQuery = destinationProjectDataDriver.CreateQuery(updateHeader + StringLiterals.SPACE + sb.ToString() + StringLiterals.SPACE + whereClause);
updateQuery.Parameters = fieldValueParams;
destinationProjectDataDriver.ExecuteNonQuery(updateQuery);
sb.Remove(0, sb.ToString().Length);
fieldValueParams.Clear();
recordsUpdated++;
}
}
//}
//else
//{
if (append)
{
try
{
if (!string.IsNullOrEmpty(FKEY))
{
fieldNames.Append("FKEY");
fieldValues.Append("@FKEY");
fieldValueParams.Add(paramFkey);
}
fieldNames.Append("RECSTATUS");
fieldValues.Append("@RECSTATUS");
fieldValueParams.Add(paramRecordStatus);
// Concatenate the query clauses into one SQL statement.
StringBuilder sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(destinationProjectDataDriver.InsertInEscape(destinationTable));
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;
System.Diagnostics.Debug.Print(insertQuery.SqlStatement);
destinationProjectDataDriver.ExecuteNonQuery(insertQuery);
foreach (Page page in destinationView.Pages)
{
sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(destinationProjectDataDriver.InsertInEscape(page.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append("([GlobalRecordId])");
sb.Append(" values (");
sb.Append("'" + GUID + "'");
sb.Append(") ");
insertQuery = destinationProjectDataDriver.CreateQuery(sb.ToString());
destinationProjectDataDriver.ExecuteNonQuery(insertQuery);
}
recordsInserted++;
}
catch(Exception ex)
{
throw ex;
}
// }
}
this.BeginInvoke(new SetProgressBarDelegate(IncrementProgressBarValue), 1);
}
//sourceReader.Close();
//sourceReader.Dispose();
//}
//catch (Exception ex)
//{
// this.BeginInvoke(new SetStatusDelegate(AddErrorStatusMessage), ex.Message);
// return false;
//}
//finally
//{
//}
this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "On base table '" + destinationTable + "', " + recordsInserted.ToString() + " record(s) inserted and " + recordsUpdated.ToString() + " record(s) updated.");
}
