/// <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 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 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>
/// 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 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.ToUpper() == "TEXT" || cmbOutputFormat.Text.ToUpper() == "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>
/// 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>
/// 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>
/// 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>
/// 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 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>
/// 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.ToUpper());
}
if (!string.IsNullOrEmpty(fontSizeToolStripDDL.Text))
{
sb.Append(StringLiterals.SPACE);
sb.Append(fontSizeToolStripDDL.Text);
}
}
}
CommandText = sb.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>
/// 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();
}
public string GetWhereClause(View form)
{
string baseTableName = "t";
string whereClause = String.Empty;
string recStatusComparison = "[" + baseTableName + "].[RECSTATUS] = 1";
if (RecordProcessingScope == Epi.RecordProcessingScope.Both)
{
recStatusComparison = "[" + baseTableName + "].[RECSTATUS] >= 0";
}
else if (RecordProcessingScope == Epi.RecordProcessingScope.Deleted)
{
recStatusComparison = "[" + baseTableName + "].[RECSTATUS] = 0";
}
whereClause = " WHERE " + recStatusComparison;// +" AND (";
string logicalOperatorString = " AND ";
if (_conditionJoinType == ConditionJoinTypes.Or)
{
logicalOperatorString = " OR ";
}
bool hasFilterConditions = false;
WordBuilder filterSql = new WordBuilder(logicalOperatorString);
foreach (IRowFilterCondition rowFc in _rowFilterConditions)
{
hasFilterConditions = true;
filterSql.Append(rowFc.Sql);
}
if (hasFilterConditions)
{
whereClause = whereClause + " AND (" + filterSql + ")";
}
return(whereClause);
}
/// <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>
/// 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>
/// 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.");
}
/// <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();
}
private static void ScanForwardLetters(Letter currentLetter, List<WordBuilder> builderList, WordBuilder previousBuilder)
{
WordBuilder newBuilder = new WordBuilder(previousBuilder);//ShorewoodPool.GetBuilder(previousBuilder);
newBuilder.Append(currentLetter);
builderList.Add(new WordBuilder(newBuilder));//ShorewoodPool.GetBuilder(newBuilder));
if ((newBuilder.CurrentString.Length > Shorewood.normalGameplayRenderer.grid.minimumWordLength))
{
if (!CheckValidWord(newBuilder))
{
//ShorewoodPool.ReturnBuilder(newBuilder);
return;
}
}
if (Shorewood.normalGameplayRenderer.grid.GetLetter(currentLetter.upperRightNeighbor) != Letter.EmptyLetter)
{
ScanForwardLetters(Shorewood.normalGameplayRenderer.grid.GetLetter(currentLetter.upperRightNeighbor), builderList, newBuilder);
}
if (Shorewood.normalGameplayRenderer.grid.GetLetter(currentLetter.rightNeighbor) != Letter.EmptyLetter)
{
ScanForwardLetters(Shorewood.normalGameplayRenderer.grid.GetLetter(currentLetter.rightNeighbor), builderList, newBuilder);
}
if (Shorewood.normalGameplayRenderer.grid.GetLetter(currentLetter.lowerRightNeighbor) != Letter.EmptyLetter)
{
ScanForwardLetters(Shorewood.normalGameplayRenderer.grid.GetLetter(currentLetter.lowerRightNeighbor), builderList, newBuilder);
}
//ShorewoodPool.ReturnBuilder(newBuilder);
return;
}
/// <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>
/// 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>
/// Insert record into table.
/// </summary>
/// <param name="view">View of a table.</param>
/// <returns>Unique key of the record.</returns>
public int SaveRecord(Page page)
{
if (TableExists(page.TableName) == false)
{
return 0;
}
Configuration config = Configuration.GetNewInstance();
try
{
WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA);
WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA);
List<QueryParameter> fieldValueParams = new List<QueryParameter>();
fieldNames.Append("GlobalRecordId");
fieldValues.Append("@GlobalRecordId");
fieldValueParams.Add(page.view.GlobalRecordIdField.CurrentRecordValueAsQueryParameter);
foreach (RenderableField renderableField in page.Fields)
{
if (renderableField is GridField || renderableField is GroupField)
{
continue;
}
else if (renderableField is IDataField)
{
IDataField dataField = (IDataField)renderableField;
if (dataField is UniqueKeyField)
{
continue;
}
else
{
fieldNames.Append(dbDriver.InsertInEscape(((Epi.INamedObject)dataField).Name));
if (dataField.CurrentRecordValueObject == null)
{
fieldValues.Append(" null ");
}
else if (string.IsNullOrEmpty(dataField.CurrentRecordValueObject.ToString()))
{
fieldValues.Append(" null ");
}
else
{
String fieldName = ((Epi.INamedObject)dataField).Name;
fieldValues.Append("@" + fieldName);
fieldValueParams.Add(dataField.CurrentRecordValueAsQueryParameter);
}
}
}
else
{
}
}
StringBuilder sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(dbDriver.InsertInEscape(page.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append(Util.InsertInParantheses(fieldNames.ToString()));
sb.Append(" values (");
sb.Append(fieldValues.ToString());
sb.Append(") ");
Query insertQuery = dbDriver.CreateQuery(sb.ToString());
insertQuery.Parameters = fieldValueParams;
dbDriver.ExecuteNonQuery(insertQuery);
return 0;
}
finally
{
}
}
/// <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>
/// Insert record into table.
/// </summary>
/// <param name="view">View of a table.</param>
/// <param name="reader">Forward-only stream of result sets obtained by executing a command.</param>
public void SaveRecord(View view, IDataReader reader)
{
try
{
bool hasData = false;
WordBuilder fieldNames = new WordBuilder();
WordBuilder fieldValues = new WordBuilder();
fieldNames.Delimitter = StringLiterals.COMMA;
fieldValues.Delimitter = StringLiterals.COMMA;
foreach (Field field in view.Fields.TableColumnFields)
{
// Eliminate UniqueKeyFields. They are not inserted explicitly.
if (!(field is UniqueKeyField))
{
if (!Util.IsEmpty(reader[field.Name]))
{
hasData = true;
fieldNames.Append(dbDriver.InsertInEscape(field.Name));
Object fieldValue = reader[field.Name];
string fieldValueString = string.Empty;
// string fieldValue = reader[field.Name].ToString();
// TODO: Determine if the value is null. If so, no further formatting required.
if (field is DateField)
{
fieldValueString = dbDriver.FormatDate((DateTime)fieldValue);
}
else if (field is TimeField)
{
fieldValueString = dbDriver.FormatTime((DateTime)fieldValue);
}
else if (field is DateTimeField)
{
fieldValueString = dbDriver.FormatDateTime((DateTime)fieldValue);
}
else if ((field is PhoneNumberField) || (field is TableBasedDropDownField) || (field is TextField))
{
fieldValueString = Util.InsertInSingleQuotes(fieldValue.ToString());
}
else if (field is CheckBoxField)
{
bool checkboxValue = bool.Parse(fieldValue.ToString());
if (checkboxValue)
{
fieldValueString = "1";
}
else
{
fieldValueString = "0";
}
}
else
{
fieldValueString = fieldValue.ToString();
}
fieldValues.Append(fieldValueString);
}
}
}
// Concatenate the query clauses into one SQL statement.
string queryString = "insert into ";
queryString += dbDriver.InsertInEscape(view.TableName) + StringLiterals.SPACE;
queryString += Util.InsertInParantheses(fieldNames.ToString());
queryString += StringLiterals.SPACE + "values" + StringLiterals.SPACE;
queryString += Util.InsertInParantheses(fieldValues.ToString());
Query insertQuery = dbDriver.CreateQuery(queryString);
if (hasData)
{
dbDriver.ExecuteNonQuery(insertQuery);
}
}
finally
{
}
}
/// <summary>
/// Insert record into table.
/// </summary>
/// <param name="view">View of a table.</param>
/// <returns>Unique key of the record.</returns>
public int SaveRecord(View view)
{
Configuration config = Configuration.GetNewInstance();
try
{
WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA);
WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA);
List<QueryParameter> fieldValueParams = new List<QueryParameter>();
foreach (IDataField dataField in view.Fields.DataFields)
{
if (dataField is GlobalRecordIdField || dataField is ForeignKeyField)
{
fieldNames.Append(dbDriver.InsertInEscape(((Epi.INamedObject)dataField).Name));
if (dataField.CurrentRecordValueObject == null)
{
fieldValues.Append(" null ");
}
else if (string.IsNullOrEmpty(dataField.CurrentRecordValueObject.ToString()))
{
fieldValues.Append(" null ");
}
else
{
String fieldName = ((Epi.INamedObject)dataField).Name;
fieldValues.Append("@" + fieldName);
fieldValueParams.Add(dataField.CurrentRecordValueAsQueryParameter);
}
}
else
{
continue;
}
}
System.Security.Principal.WindowsIdentity winId = System.Security.Principal.WindowsIdentity.GetCurrent();
String winIdName = winId.Name;
bool hasSaveColumns = dbDriver.ColumnExists(view.TableName, ColumnNames.RECORD_FIRST_SAVE_LOGON_NAME);
StringBuilder sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(dbDriver.InsertInEscape(view.TableName));
sb.Append(StringLiterals.SPACE);
if (hasSaveColumns)
{
sb.Append(Util.InsertInParantheses(fieldNames.ToString() + ", [" + ColumnNames.RECORD_FIRST_SAVE_LOGON_NAME + "], [" + ColumnNames.RECORD_FIRST_SAVE_TIME + "]"));
sb.Append(" values (");
sb.Append(fieldValues.ToString() + ", @" + ColumnNames.RECORD_FIRST_SAVE_LOGON_NAME +", @" + ColumnNames.RECORD_FIRST_SAVE_TIME );
}
else
{
sb.Append(Util.InsertInParantheses(fieldNames.ToString()));
sb.Append(" values (");
sb.Append(fieldValues.ToString());
}
sb.Append(") ");
Query insertQuery = dbDriver.CreateQuery(sb.ToString());
insertQuery.Parameters = fieldValueParams;
if (hasSaveColumns)
{
QueryParameter name = new QueryParameter("@" + ColumnNames.RECORD_FIRST_SAVE_LOGON_NAME, DbType.String, winIdName);
insertQuery.Parameters.Add(name);
QueryParameter time = new QueryParameter("@" + ColumnNames.RECORD_FIRST_SAVE_TIME, DbType.DateTime, DateTime.Now.ToString());
insertQuery.Parameters.Add(time);
}
dbDriver.ExecuteNonQuery(insertQuery);
if (hasSaveColumns)
{
UpdateBaseTable(view);
}
string relatedViewFilter = string.Empty;
if (view.IsRelatedView)
{
relatedViewFilter = " where ";
relatedViewFilter += dbDriver.InsertInEscape(ColumnNames.FOREIGN_KEY);
relatedViewFilter += StringLiterals.EQUAL + "'" + view.ForeignKeyField.CurrentRecordValueString + "'";
}
Query selectQuery = dbDriver.CreateQuery(" select" +
Util.InsertIn("max" + Util.InsertInParantheses(dbDriver.InsertInEscape(ColumnNames.UNIQUE_KEY)), StringLiterals.SPACE) +
"from" + StringLiterals.SPACE + dbDriver.InsertInEscape(view.TableName) +
relatedViewFilter);
foreach (Epi.Page page in view.Pages)
{
this.SaveRecord(page);
}
int recordID;
int.TryParse(dbDriver.ExecuteScalar(selectQuery).ToString(), out recordID);
foreach (GridField grid in view.Fields.GridFields)
{
this.SaveGridRecord(view, recordID, grid, grid.DataSource);
grid.DataSource = null;
}
return recordID;
}
finally
{
}
}
/// <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>
/// Processes a form's base table
/// </summary>
/// <param name="destinationView">The destination form</param>
/// <param name="destinationGUIDList">The list of GUIDs that exist in the destination</param>
private void ProcessBaseTable(View destinationView, List<string> destinationGUIDList)
{
this.BeginInvoke(new SetStatusDelegate(SetStatusMessage), "Processing records on base table...");
int recordsInserted = 0;
int recordsUpdated = 0;
string destinationTable = destinationView.TableName;
try
{
//IDataReader sourceReader = sourceProjectDataDriver.GetTableDataReader(sourceView.TableName);
foreach (string surveyGUID in surveyGUIDs /*Epi.Web.Common.DTO.SurveyAnswerDTO surveyAnswer in result.SurveyResponseList*/)
{
object recordStatus = 1; // no marking for deletion supported at this time.
QueryParameter paramRecordStatus = new QueryParameter("@RECSTATUS", DbType.Int32, recordStatus);
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 = surveyGUID; // surveyAnswer.ResponseId; // 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))
{
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++;
}
}
this.BeginInvoke(new SetProgressBarDelegate(IncrementProgressBarValue), 1);
}
}
catch (Exception ex)
{
this.BeginInvoke(new SetStatusDelegate(AddErrorStatusMessage), ex.Message);
}
finally
{
}
// this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "On page '" + destinationTable + "', " + recordsInserted.ToString() + " record(s) inserted and " + recordsUpdated.ToString() + " record(s) updated.");
}
/// <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.ToLower().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.ToLower().Contains("epi.data.office"))
{
IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters);
object obj = command.ExecuteNonQuery();
}
else
{
db.ExecuteNonQuery(insertQuery);
}
}
//);
}
/// <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>
///
/// </summary>
/// <param name="db"></param>
/// <param name="dataTable"></param>
/// <param name="dRow"></param>
/// <param name="sTableName"></param>
/// <returns></returns>
protected Query CreateInsertQuery(IDbDriver db, DataTable dataTable, DataRow dRow, string sTableName)
{
Query result;
try
{
// Begin Insert clause
StringBuilder sbInsert = new StringBuilder();
WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA);
WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA);
sbInsert.Append("Insert Into ");
sbInsert.Append(Util.InsertIn(dbDriver.InsertInEscape(sTableName), StringLiterals.SPACE));
foreach (DataColumn column in dataTable.Columns)
{
fieldNames.Append(dbDriver.InsertInEscape(column.ColumnName));
if (column.DataType.IsPrimitive)
{
if (dRow[column.ColumnName].ToString().Length == 0)
{
fieldValues.Append("null");
}
else
if (column.DataType.Name == "Boolean")
{
if (dRow[column.ColumnName] != null)
{
if (dRow[column.ColumnName].ToString() == "True")
{
fieldValues.Append("1");
}
else
{
fieldValues.Append("0");
}
}
else
{
fieldValues.Append("null");
}
}
else
{
fieldValues.Append(dRow[column.ColumnName].ToString());
}
}
else
{
if (column.DataType.Name == "Boolean")
{
if (dRow[column.ColumnName] != null)
{
if (dRow[column.ColumnName].ToString() == "True")
{
fieldValues.Append("1");
}
else
{
fieldValues.Append("0");
}
}
else
{
fieldValues.Append("null");
}
}
else
{
fieldValues.Append(Util.InsertInSingleQuotes(dRow[column.ColumnName].ToString()));
}
}
}
sbInsert.Append(Util.InsertInParantheses(fieldNames.ToString()));
sbInsert.Append(Util.InsertIn("Values", StringLiterals.SPACE));
sbInsert.Append(Util.InsertInParantheses(fieldValues.ToString()));
// End Values clause
result = dbDriver.CreateQuery(sbInsert.ToString());
}
catch (Exception ex)
{
throw new GeneralException("Could not create insert query", ex);
}
return result;
}
/// <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>
/// Inserts a grid record if update grid record fails as attempted by SaveGridRecord().
/// </summary>
/// <param name="recordID">Id of current Record.</param>
/// <param name="field">GridField object.</param>
/// <param name="dRow">DataRow of DataTable to save.</param>
/// <param name="sTableName">View name plus field name</param>
/// <returns>Number rows affected.</returns>
private int InsertGridRecord(string globalRecordId, string gridLevelGlobalRecordId, string uniqueRowId, GridField field, DataRow dRow, string sTableName)
{
try
{
StringBuilder sbInsert = new StringBuilder();
WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA);
WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA);
sbInsert.Append("insert into ");
sbInsert.Append(Util.InsertIn(sTableName, StringLiterals.SPACE));
foreach (GridColumnBase column in field.Columns)
{
if (column.Name != ColumnNames.UNIQUE_KEY)
{
fieldNames.Append(dbDriver.InsertInEscape(column.Name));
}
}
sbInsert.Append(Util.InsertInParantheses(fieldNames.ToString()));
sbInsert.Append(Util.InsertIn("values", StringLiterals.SPACE));
foreach (GridColumnBase column in field.Columns)
{
if (column.Name != ColumnNames.UNIQUE_KEY)
{
if (column is NumberColumn)
{
if (dRow[column.Name].ToString().Length == 0)
{
fieldValues.Append("null");
}
else
{
fieldValues.Append(dRow[column.Name].ToString());
}
}
else
{
if (column.Name == ColumnNames.FOREIGN_KEY)
{
fieldValues.Append(Util.InsertInSingleQuotes(globalRecordId));
}
else if (column.Name == ColumnNames.GLOBAL_RECORD_ID)
{
fieldValues.Append(Util.InsertInSingleQuotes(gridLevelGlobalRecordId));
}
else if (column.Name == ColumnNames.UNIQUE_ROW_ID)
{
fieldValues.Append(Util.InsertInSingleQuotes(uniqueRowId));
}
else if (column is TimeColumn
|| column is DateColumn
|| column is DateTimeColumn )
{
string valueString = "null";
if(string.IsNullOrEmpty(dRow[column.Name].ToString()) == false)
{
valueString = Util.InsertInSingleQuotes(dRow[column.Name].ToString());
}
fieldValues.Append(valueString);
}
else if (column is CheckboxColumn)
{
string valueString = "0";
if (dRow[column.Name].ToString().ToLower().Contains("true"))
{
valueString = "1";
}
fieldValues.Append(valueString);
}
else if (column is YesNoColumn)
{
string valueString = "null";
if (dRow[column.Name].ToString().Contains("1"))
{
valueString = "1";
}
else if (dRow[column.Name].ToString().Contains("0"))
{
valueString = "0";
}
fieldValues.Append(valueString);
}
else if (column.Name == ColumnNames.REC_STATUS)
{
fieldValues.Append("1");
}
else
{
fieldValues.Append(Util.InsertInSingleQuotes(dRow[column.Name].ToString()));
}
}
}
}
sbInsert.Append(Util.InsertInParantheses(fieldValues.ToString()));
Query insertQuery = dbDriver.CreateQuery(sbInsert.ToString());
return dbDriver.ExecuteNonQuery(insertQuery);
}
catch (Exception ex)
{
throw new GeneralException("Could not insert grid record", ex);
}
}
/// <summary>
/// Builds the command text
/// </summary>
protected override void GenerateCommand()
{
Configuration config = Configuration.GetNewInstance();
KeyValuePairCollection kvPairs = new KeyValuePairCollection();
kvPairs.Delimiter = CharLiterals.SPACE;
if ((settingsPanel.RepresentationOfYes != config.Settings.RepresentationOfYes) || (hasYesAsChanged))
{
kvPairs.Add(new KeyValuePair(ShortHands.YES, Util.InsertInDoubleQuotes(settingsPanel.RepresentationOfYes)));
}
if ((settingsPanel.RepresentationOfNo != config.Settings.RepresentationOfNo) || (hasNoAsChanged))
{
kvPairs.Add(new KeyValuePair(ShortHands.NO, Util.InsertInDoubleQuotes(settingsPanel.RepresentationOfNo)));
}
if ((settingsPanel.RepresentationOfMissing != config.Settings.RepresentationOfMissing) || (hasMissingAsChanged))
{
kvPairs.Add(new KeyValuePair(ShortHands.MISSING, Util.InsertInDoubleQuotes(settingsPanel.RepresentationOfMissing)));
}
if ((settingsPanel.ShowGraphics != config.Settings.ShowGraphics) || (hasShowGraphicChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.FREQGRAPH,
Epi.Util.GetShortHand(settingsPanel.ShowGraphics)));
}
if ((settingsPanel.ShowHyperlinks != config.Settings.ShowHyperlinks) || (hasShowHyperlinkChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.HYPERLINKS,
Epi.Util.GetShortHand(settingsPanel.ShowHyperlinks)));
}
if ((settingsPanel.ShowPercents != config.Settings.ShowPercents) || (hasShowPercentsChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.PERCENTS,
Epi.Util.GetShortHand(settingsPanel.ShowPercents)));
}
if ((settingsPanel.ShowSelectCriteria != config.Settings.ShowSelection) || (hasShowSelectChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.SELECT,
Epi.Util.GetShortHand(settingsPanel.ShowSelectCriteria)));
}
if ((settingsPanel.ShowPrompt != config.Settings.ShowCompletePrompt) || (hasShowPromptChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.SHOWPROMPTS,
Epi.Util.GetShortHand(settingsPanel.ShowPrompt)));
}
if ((settingsPanel.ShowTablesOutput != config.Settings.ShowTables) || (hasShowTablesChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.TABLES,
Epi.Util.GetShortHand(settingsPanel.ShowTablesOutput)));
}
if ((settingsPanel.ShowIncludeMissing != config.Settings.IncludeMissingValues) || (hasIncludeMissingChanged))
{
kvPairs.Add(new KeyValuePair(CommandNames.MISSING,
Epi.Util.GetShortHand(settingsPanel.ShowIncludeMissing)));
}
if (hasStatisticsLevelChanged)
{
RadioButton rbSelected = settingsPanel.StatisticLevel;
StatisticsLevel levelIdSelected = (StatisticsLevel)short.Parse(rbSelected.Tag.ToString());
string levelTagSelected = AppData.Instance.GetStatisticsLevelById(levelIdSelected).Tag;
kvPairs.Add(new KeyValuePair(CommandNames.STATISTICS, levelTagSelected));
}
if (hasProcessRecordsChanged)
{
RadioButton rbSelected = settingsPanel.ProcessRecords;
RecordProcessingScope scopeIdSelected = (RecordProcessingScope)short.Parse(rbSelected.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>
/// Get Grid field Table Data for current view record.
/// </summary>
/// <param name="view">Current view of project.</param>
/// <param name="field">Grid field</param>
/// <returns>Grid field table contents.</returns>
public System.Data.DataTable GetGridTableData(View view, GridField field)
{
StringBuilder sbSelect = new StringBuilder();
WordBuilder wbSelect = new WordBuilder(StringLiterals.COMMA);
// Begin Select clause
foreach (GridColumnBase column in ((GridField)field).Columns)
{
wbSelect.Append(dbDriver.InsertInEscape(column.Name));
}
sbSelect.Append("select");
sbSelect.Append(StringLiterals.SPACE);
sbSelect.Append(wbSelect.ToString());
// End Select clause
// Begin From clause
sbSelect.Append(Util.InsertIn("from", StringLiterals.SPACE));
sbSelect.Append(dbDriver.InsertInEscape(view.TableName + field.Page.Id.ToString() + field.Name));
sbSelect.Append(Util.InsertIn("where", StringLiterals.SPACE));
sbSelect.Append(dbDriver.InsertInEscape(ColumnNames.FOREIGN_KEY) + StringLiterals.EQUAL + "'" + view.CurrentGlobalRecordId + "'");
// End From clause
Data.Query selectQuery = dbDriver.CreateQuery(sbSelect.ToString());
return dbDriver.Select(selectQuery);
}
/// <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);
}
}
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>
/// 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>
/// 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>
/// 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>
/// 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 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>
/// 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();
}
