/// <summary>
/// Assigns values to all linked fields based on a code table.
/// </summary>
/// <param name="sender">The control that contains the link references</param>
public void FilterList(object sender, System.EventArgs e)
{
string TestValue = null;
if (sender is ListFieldComboBox)
{
ListFieldComboBox lvcb = (ListFieldComboBox)sender;
TestValue = lvcb.SelectedValue.ToString();
}
else
{
TestValue = ((Control)sender).Text;
}
this.DataSource = null;
Epi.Fields.DDListField field = (Epi.Fields.DDListField)Epi.Windows.Enter.PresentationLogic.ControlFactory.Instance.GetAssociatedField(this);
string SourceTable = field.SourceTable;
string relateField = null;
string displayfield = field.TextColumnName;
foreach (System.Collections.Generic.KeyValuePair <string, int> kvp in field.pairAssociated)
{
relateField = kvp.Key;
break;
}
System.Data.DataTable drv = DBReadExecute.GetDataTable(field.GetProject().FilePath, string.Format("Select * from {0} Where {1} Like '{2}%'", SourceTable, relateField, TestValue));
this.Items.Clear();
this.DataSource = drv;
this.DisplayMember = displayfield;
this.ValueMember = relateField;
this.RefreshItems();
}
/// <summary>
/// Returns a 2x2 table for the tables command
/// </summary>
/// <remarks>The cells are numbered as they are in Epi3</remarks>
/// <param name="outcome"></param>
/// <param name="exposure"></param>
/// <returns>DataTable</returns>
public DataSet GetDataSet2x2(Rule_Context pContext, string exposure, string outcome)
{
DataSet ds = null;
try
{
StringBuilder sb = new StringBuilder("select ");
sb.Append(exposure).Append(" AS [").Append(ColumnNames.EXPOSURE).Append("], [");
sb.Append(outcome).Append("] AS [").Append(ColumnNames.OUTCOME);
sb.Append("], count([").Append(outcome).Append("]) AS [").Append(ColumnNames.COUNT);
sb.Append("] ");
sb.Append(GetSqlStatementPartFrom(pContext));
//if there is no project, there will not be a column Rec Status
if (PrimaryTable.Columns.Contains(ColumnNames.REC_STATUS))
{
sb.Append(ExcludeMissing(GetSqlStatementPartWhere(),
new string[] { exposure, outcome }));
}
//a natively read MDB, and SELECT chosen
else if (!string.IsNullOrEmpty(selectCriteria))
{
string whereClause = " where " + this.selectCriteria;
sb.Append(ExcludeMissing(whereClause, new string[] { exposure, outcome }));
}
sb.Append(" group by [").Append(exposure).Append("], [").Append(outcome);
sb.Append("] order by [").Append(exposure);
sb.Append("], [").Append(outcome).Append("];");
DataTable Table2x2 = DBReadExecute.GetDataTable(pContext.CurrentRead.File, sb.ToString());
//Query query = Db.CreateQuery(sb.ToString());
//DataTable Table2x2 = Db.Select(query);
Table2x2.TableName = "Table2x2";
ds = new DataSet("dsTable2x2");
ds.Tables.Add(Table2x2);
//DataTable distinctOutcomes = DistinctColumn(outcome);
//distinctOutcomes.TableName = "DistinctOutcomes";
//ds.Tables.Add(distinctOutcomes);
}
catch (Exception ex)
{
throw new GeneralException(SharedStrings.UNABLE_CREATE_2X2, ex);
}
return(ds);
}
public void UpdateCurrentRecordNumber()
{
if (this.mView.CurrentRecordId > 0)
{
if (!this.RecordNumberMap.ContainsKey(this.mView.CurrentRecordId))
{
string relatedViewFilter = string.Empty;
if (this.mView.IsRelatedView)
{
relatedViewFilter = " and ";
relatedViewFilter += ColumnNames.FOREIGN_KEY;
relatedViewFilter += StringLiterals.EQUAL + "'" + mView.ForeignKeyField.CurrentRecordValueString + "'";
}
DataTable DT = DBReadExecute.GetDataTable(this.mView.Project.FilePath, "Select Count(*) From [" + this.mView.TableName + "] Where UniqueKey <= " + this.mView.CurrentRecordId.ToString() + relatedViewFilter);
if (DT.Rows.Count > 0)
{
this.RecordNumberMap.Add(this.mView.CurrentRecordId, int.Parse(DT.Rows[0][0].ToString()));
}
}
}
}
/// <summary>
/// performs execution of the READ command
/// </summary>
/// <returns>object</returns>
public override object Execute()
{
object result = null;
DataTable outputTable;
int recordCount = 0;
Context.isReadMode = true;
Context.GroupVariableList = new Dictionary <string, List <string> >(StringComparer.OrdinalIgnoreCase);
if (File.ToUpperInvariant().StartsWith("CONFIG:"))
{
string[] datakey = File.Split(':');
string connectionString = null;
Configuration config = Configuration.GetNewInstance();
for (int i = 0; i < config.RecentDataSources.Count; i++)
{
Epi.DataSets.Config.RecentDataSourceRow row = config.RecentDataSources[i];
if (row.Name.ToUpperInvariant() == datakey[1].ToUpperInvariant())
{
connectionString = Configuration.Decrypt(row.ConnectionString);
break;
}
}
File = connectionString;
}
if (_isSimpleRead)
{
if (Context.CurrentProject == null)
{
if (Context.CurrentRead == null)
{
throw new GeneralException(SharedStrings.NO_CURRENT_PROJECT);
}
else
{
Context.AddConnection("_DB", Context.CurrentRead.File);
outputTable = DBReadExecute.GetDataTable(Context.CurrentRead.File, "Select TOP 2 * from " + Identifier);
recordCount = (int)DBReadExecute.GetScalar(Context.CurrentRead.File, "SELECT COUNT(*) FROM " + Identifier);
File = Context.CurrentRead.File;
IsEpi7ProjectRead = Context.CurrentRead.IsEpi7ProjectRead;
Context.CurrentRead = this;
}
}
else
{
Context.AddConnection("_DB", Context.CurrentProject.CollectedDataConnectionString);
outputTable = DBReadExecute.GetDataTable(Context.CurrentProject.CollectedDataConnectionString, "Select TOP 2 * from " + Context.CurrentProject.views[Identifier].FromViewSQL);
recordCount = (int)DBReadExecute.GetScalar(Context.CurrentProject.CollectedDataConnectionString, "SELECT COUNT(*) FROM " + Identifier);
File = Context.CurrentProject.FilePath;
Context.CurrentRead = this;
IsEpi7ProjectRead = true;
}
}
else
{
Context.AddConnection("_DB", File);
Context.CurrentRead = this;
string[] identifiers = Identifier.Split('.');
StringBuilder identifierBuilder = new StringBuilder();
for (int i = 0; i < identifiers.Length; i++)
{
identifierBuilder.Append("[");
identifierBuilder.Append(identifiers[i]);
identifierBuilder.Append("].");
}
identifierBuilder.Length = identifierBuilder.Length - 1;
if (DBReadExecute.ProjectFileName != "")
{
IsEpi7ProjectRead = true;
Context.CurrentProject = new Project(DBReadExecute.ProjectFileName);
if (Context.CurrentProject.Views.Exists(Identifier))
{
string tableName = Context.CurrentProject.Views[Identifier].TableName;
DataSets.Config.DataDriverDataTable dataDrivers = Configuration.GetNewInstance().DataDrivers;
if (!DBReadExecute.CheckDatabaseTableExistance(DBReadExecute.ParseConnectionString(File), tableName, true))
{
throw new GeneralException(string.Format("The Datatable for [{0}] does NOT exist.\nPlease be sure the Datatable exists before trying to READ.", Identifier));
}
else
{
string query = GetEpi7ProjectRecordCountQuery(tableName);
recordCount = (int)DBReadExecute.GetScalar(File, query);
outputTable = DBReadExecute.GetDataTable(File, "Select TOP 2 * From [" + Context.CurrentProject.Views[Identifier].TableName + "]");
foreach (Page page in Context.CurrentProject.Views[Identifier].Pages)
{
outputTable = JoinTables(outputTable, DBReadExecute.GetDataTable(File, "Select TOP 2 * From [" + page.TableName + "]"));
}
}
}
else
{
outputTable = DBReadExecute.GetDataTable(File, "Select TOP 2 * FROM " + identifierBuilder.ToString());
recordCount = (int)DBReadExecute.GetScalar(File, "SELECT COUNT(*) FROM " + identifierBuilder.ToString());
}
}
else
{
String identifier = identifierBuilder.ToString();
outputTable = DBReadExecute.GetDataTable(File, "Select TOP 2 * from " + identifier);
recordCount = int.Parse(DBReadExecute.GetScalar(File, "SELECT COUNT(*) FROM " + identifier).ToString());
}
}
outputTable.TableName = "Output";
Context.DataTableRefreshNeeded = true;
if (Context.DataSet.Tables.Contains("Output"))
{
Context.DataSet.Tables.Remove("Output");
}
Context.DataSet.Tables.Add(outputTable);
if (Context.DataSet.Tables.Contains("datasource"))
{
Context.DataSet.Tables.Remove("datasource");
}
DataTable datasouceTable = new DataTable("datasource");
foreach (DataColumn column in outputTable.Columns)
{
DataColumn newColumn = new DataColumn(column.ColumnName);
newColumn.DataType = column.DataType;
datasouceTable.Columns.Add(newColumn);
}
Context.DataSet.Tables.Add(datasouceTable);
Context.MemoryRegion.RemoveVariablesInScope(VariableType.DataSource);
Context.ReadDataSource(datasouceTable);
Context.GroupVariableList = new Dictionary <string, List <string> >();
if (Rule_Read.RemoveVariables)
{
if (Context.DataSet.Tables.Contains("variables"))
{
Context.DataSet.Tables.Remove("variables");
Context.MemoryRegion.RemoveVariablesInScope(VariableType.Standard);
}
if (Context.SelectExpression != null)
{
Context.SelectExpression.Clear();
}
if (Context.SubroutineList != null)
{
Context.SubroutineList.Clear();
}
if (Context.Subroutine != null)
{
Context.Subroutine.Clear();
}
Context.SelectString.Length = 0;
Context.SortExpression.Length = 0;
}
else
{
Rule_Read.RemoveVariables = true;
}
Context.SyncVariableAndOutputTable();
Context.ClearParticipatingVariableList();
Context.GetOutput();
result = string.Format("number of records read {0}", recordCount);
Dictionary <string, string> args = new Dictionary <string, string>();
args.Add("COMMANDNAME", CommandNames.READ);
args.Add("FILENAME", File);
args.Add("TABLENAME", Identifier);
args.Add("ROWCOUNT", recordCount.ToString());
Project currentProject = Context.CurrentProject;
if (string.IsNullOrEmpty(File) && currentProject == null)
{
throw new GeneralException(SharedStrings.NO_CURRENT_PROJECT);
}
Context.AnalysisCheckCodeInterface.Display(args);
return(result);
}
private DataTable GetDataTable(string filePath, string identifier)
{
System.Data.DataTable table;
if (filePath.ToUpperInvariant().StartsWith("CONFIG:"))
{
string[] datakey = filePath.Split(':');
string connectionString = null;
Configuration config = Configuration.GetNewInstance();
for (int i = 0; i < config.RecentDataSources.Count; i++)
{
Epi.DataSets.Config.RecentDataSourceRow row = config.RecentDataSources[i];
if (row.Name.ToUpperInvariant() == datakey[1].ToUpperInvariant())
{
connectionString = Configuration.Decrypt(row.ConnectionString);
break;
}
}
filePath = connectionString;
}
string[] Identifiers = identifier.Split('.');
StringBuilder IdentifierBuilder = new StringBuilder();
for (int i = 0; i < Identifiers.Length; i++)
{
IdentifierBuilder.Append("[");
IdentifierBuilder.Append(Identifiers[i]);
IdentifierBuilder.Append("].");
}
IdentifierBuilder.Length = IdentifierBuilder.Length - 1;
if (DBReadExecute.ProjectFileName != "")
{
if (Context.CurrentProject.Views.Exists(identifier))
{
string selectStatement = "Select * From [" + identifier + "]";
table = DBReadExecute.GetDataTable(Context.CurrentRead.File, selectStatement);
foreach (Page page in Context.CurrentProject.Views[identifier].Pages)
{
DataTable pageTable = DBReadExecute.GetDataTable(Context.CurrentRead.File, "Select * From [" + page.TableName + "]");
table = JoinPagesTables(table, pageTable);
}
}
else
{
if (filePath.EndsWith(".prj"))
{
Epi.Data.IDbDriver driver = DBReadExecute.GetDataDriver(filePath);
table = driver.GetTableData(identifier);
DataTable pageTables = DBReadExecute.GetDataTable(driver, "Select DISTINCT PageId FROM metaFields Where DataTableName = '" + identifier + "' AND PageId <> null");
foreach (DataRow row in pageTables.Rows)
{
DataTable pageTable = driver.GetTableData(identifier + row["PageId"]);
table = JoinPagesTables(table, pageTable);
}
}
else
{
table = DBReadExecute.GetDataTable(filePath, "Select * FROM " + IdentifierBuilder.ToString());
}
}
}
else
{
if (filePath.EndsWith(".prj"))
{
Epi.Data.IDbDriver driver = DBReadExecute.GetDataDriver(filePath);
table = driver.GetTableData(identifier);
DataTable pageTables = DBReadExecute.GetDataTable(driver, "Select DISTINCT PageId FROM metaFields Where DataTableName = '" + identifier + "' AND PageId <> null");
foreach (DataRow row in pageTables.Rows)
{
DataTable pageTable = driver.GetTableData(identifier + row["PageId"]);
table = JoinPagesTables(table, pageTable);
}
}
else
{
table = DBReadExecute.GetDataTable(filePath, "Select * from " + IdentifierBuilder.ToString());
}
}
return(table);
}
private void BuildKeyDialog_Load(object sender, EventArgs e)
{
if (SelectedDataSource != null)
{
if (SelectedDataSource is IDbDriver)
{
IDbDriver db = SelectedDataSource as IDbDriver;
//--EI-114
// relatedFields = db.GetTableColumnNames(RelatedTable);
if (RelatedTable.Contains(StringLiterals.SPACE))
{
string pstr = "Select TOP 2 * from [" + RelatedTable + "]";
DataTable relfields = DBReadExecute.GetDataTable(db, pstr);
foreach (DataColumn dc in relfields.Columns)
{
relatedFields.Add(dc.ColumnName);
}
}
else
{
relatedFields = db.GetTableColumnNames(RelatedTable);
}
//---
}
else if (SelectedDataSource is Project)
{
Project project = SelectedDataSource as Project;
if (project.Views.Exists(relatedTable))
{
foreach (Epi.Fields.IField field in project.Views[RelatedTable].Fields)
{
if (!(field is Epi.Fields.LabelField) & !(field is Epi.Fields.CommandButtonField) & !(field is Epi.Fields.PhoneNumberField)//EI-705
& !(field is Epi.Fields.MultilineTextField) & !(field is Epi.Fields.GroupField) & !(field is Epi.Fields.CheckBoxField)
& !(field is Epi.Fields.ImageField) & !(field is Epi.Fields.OptionField) & !(field is Epi.Fields.GridField)
& !(field is Epi.Fields.MirrorField))
{
relatedFields.Add(field.Name);
}
}
}
else
{
relatedFields = project.GetTableColumnNames(RelatedTable);
}
}
if (this.EpiInterpreter.Context.DataSet != null)
{
View CurrentView = null;
bool currentReadIdentifierIsView = false;
if (this.EpiInterpreter.Context.CurrentProject != null)
{
currentReadIdentifierIsView = this.EpiInterpreter.Context.CurrentProject.IsView(this.EpiInterpreter.Context.CurrentRead.Identifier);
}
if (currentReadIdentifierIsView)
{
CurrentView = this.EpiInterpreter.Context.CurrentProject.GetViewByName(this.EpiInterpreter.Context.CurrentRead.Identifier); //EI-705
foreach (DataColumn column in this.EpiInterpreter.Context.DataSet.Tables["Output"].Columns)
{
if ((CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.LabelField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.CommandButtonField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.PhoneNumberField))
& (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.MultilineTextField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.GroupField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.CheckBoxField))
& (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.ImageField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.OptionField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.GridField))
& (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.MirrorField)))
{
currentFields.Add(column.ColumnName);
}
}
}
else
{
foreach (DataColumn column in this.EpiInterpreter.Context.DataSet.Tables["Output"].Columns)
{
currentFields.Add(column.ColumnName);
}
}
}
currentFields.Sort();
}
//rdbCurrentTable.Checked = true;
relatedFields.Sort();
currentFields.Sort();
//cmbAvailableVariables2.DataSource = relatedFields;
lbxRelatedTableFields.DataSource = relatedFields;
//cmbAvailableVariables.DataSource = currentFields;
lbxCurrentTableFields.DataSource = currentFields;
lbxCurrentTableFields.SelectedIndex = -1;
lbxRelatedTableFields.SelectedIndex = -1;
if (CallingDialog == "RELATE")
{
lblBuildKeyInstructions.Text = SharedStrings.BUILDKEY_RELATE_INSTRUCTIONS;
}
else
{
lblBuildKeyInstructions.Text = SharedStrings.BUILDKEY_MERGE_INSTRUCTIONS;
}
this.LoadIsFinished = true;
}
private void BuildKeyDialog_Load(object sender, EventArgs e)
{
if (SelectedDataSource != null)
{
if (SelectedDataSource is IDbDriver)
{
IDbDriver db = SelectedDataSource as IDbDriver;
//--EI-114
// relatedFields = db.GetTableColumnNames(RelatedTable);
if (RelatedTable.Contains(StringLiterals.SPACE))
{
string pstr = "Select TOP 2 * from [" + RelatedTable + "]";
DataTable relfields = DBReadExecute.GetDataTable(db, pstr);
foreach (DataColumn dc in relfields.Columns)
{
relatedFields.Add(dc.ColumnName);
}
}
else
{
relatedFields = db.GetTableColumnNames(RelatedTable);
}
//---
}
else if (SelectedDataSource is Project)
{
Project project = SelectedDataSource as Project;
if (project.Views.Exists(relatedTable))
{
foreach (Epi.Fields.IField field in project.Views[RelatedTable].Fields)
{
if (!(field is Epi.Fields.LabelField) &
!(field is Epi.Fields.CommandButtonField) &
!(field is Epi.Fields.PhoneNumberField) &
!(field is Epi.Fields.MultilineTextField) &
!(field is Epi.Fields.GroupField) &
!(field is Epi.Fields.CheckBoxField) &
!(field is Epi.Fields.ImageField) &
!(field is Epi.Fields.OptionField) &
!(field is Epi.Fields.GridField) &
!(field is Epi.Fields.MirrorField))
{
relatedFields.Add(field.Name);
}
}
}
else
{
relatedFields = project.GetTableColumnNames(RelatedTable);
}
}
currentFields = GetCurrentFields();
}
relatedFields.Sort();
currentFields.Sort();
lbxRelatedTableFields.DataSource = relatedFields;
lbxCurrentTableFields.DataSource = currentFields;
lbxCurrentTableFields.SelectedIndex = -1;
lbxRelatedTableFields.SelectedIndex = -1;
if (CallingDialog == "RELATE")
{
lblBuildKeyInstructions.Text = SharedStrings.BUILDKEY_RELATE_INSTRUCTIONS;
}
else
{
lblBuildKeyInstructions.Text = SharedStrings.BUILDKEY_MERGE_INSTRUCTIONS;
}
this.LoadIsFinished = true;
}
