private void LoadDropDownLists()
{
IDbDriver db = DataHelper.Project.CollectedData.GetDatabase();
Query selectQuery = db.CreateQuery("SELECT [epidemiologycasedefinition] FROM [codeepicasedef1]");
DataTable dt = db.Select(selectQuery);
cmbEpiCaseClass.Items.Add(String.Empty);
foreach (DataRow row in dt.Rows)
{
cmbEpiCaseClass.Items.Add(row[0].ToString());
}
selectQuery = db.CreateQuery("SELECT [finallabclass] FROM [codefinallabclass1]");
dt = db.Select(selectQuery);
cmbFinalLabClass.Items.Add(String.Empty);
foreach (DataRow row in dt.Rows)
{
cmbFinalLabClass.Items.Add(row[0].ToString());
}
cmbFeverTempSource.Items.Add(String.Empty);
cmbFeverTempSource.Items.Add(Properties.Resources.FeverTempSourceAxillary);
cmbFeverTempSource.Items.Add(Properties.Resources.FeverTempSourceOral);
cmbFeverTempSourceFinal.Items.Add(String.Empty);
cmbFeverTempSourceFinal.Items.Add(Properties.Resources.FeverTempSourceAxillary);
cmbFeverTempSourceFinal.Items.Add(Properties.Resources.FeverTempSourceOral);
}
protected override Query GetFormSelectQuery(View form, bool selectAll = true)
{
IDbDriver db = form.Project.CollectedData.GetDatabase();
string selectQueryText = String.Empty;
Query selectQuery = null;
SyncFileFilters filters = null;
int count = 0;
if (Filters != null && Filters.ContainsKey(form.Name))
{
foreach (Epi.ImportExport.IRowFilterCondition fc in Filters[form.Name])
{
count++;
}
}
if (count > 0)
{
filters = Filters[form.Name];
filters.RecordProcessingScope = Scope;
selectQuery = filters.GetGuidSelectQuery(form);
List <QueryParameter> paramsToAdd = selectQuery.Parameters;
if (selectAll)
{
selectQuery = db.CreateQuery(selectQuery.SqlStatement.Replace("[t].[GlobalRecordId], [t].[FKEY], [t].[RECSTATUS]", "*") + " ORDER BY [t].[GlobalRecordId]");
}
else
{
selectQuery = db.CreateQuery(selectQuery.SqlStatement + " ORDER BY [t].[GlobalRecordId]");
}
selectQuery.Parameters = paramsToAdd;
//selectQuery.Parameters.Add(new QueryParameter("@StartDate", DbType.DateTime, StartDate));
//selectQuery.Parameters.Add(new QueryParameter("@EndDate", DbType.DateTime, EndDate));
}
else
{
string recStatusClause = "RECSTATUS = 1";
if (Scope == Epi.RecordProcessingScope.Both)
{
recStatusClause = "RECSTATUS >= 0";
}
else if (Scope == Epi.RecordProcessingScope.Deleted)
{
recStatusClause = "RECSTATUS = 0";
}
//selectQueryText = "SELECT * " + form.FromViewSQL + " WHERE " + recStatusClause + " AND " + LAST_SAVE_TIME + " ORDER BY [t].[GlobalRecordId]";
selectQueryText = "SELECT * " + form.FromViewSQL + " WHERE " + recStatusClause + " ORDER BY [t].[GlobalRecordId]";
selectQuery = db.CreateQuery(selectQueryText);
//selectQuery.Parameters.Add(new QueryParameter("@StartDate", DbType.DateTime, StartDate));
//selectQuery.Parameters.Add(new QueryParameter("@EndDate", DbType.DateTime, EndDate));
}
return(selectQuery);
}
public static async void Log(string description)
{
if (Database == null || !Database.ToString().ToLower().Contains("sql"))
{
return;
}
Query insertQuery = Database.CreateQuery("INSERT INTO [UpdateLog] (UDate, VhfVer, UserName, MACADDR, Description) VALUES (CURRENT_TIMESTAMP, @VhfVer, @User, @MACADDR, @Description)");
//insertQuery.Parameters.Add(new QueryParameter("@UDate", System.Data.DbType.DateTime2, DateTime.Now));
insertQuery.Parameters.Add(new QueryParameter("@VhfVer", System.Data.DbType.String, _vhfVersion));
insertQuery.Parameters.Add(new QueryParameter("@User", System.Data.DbType.String, _user));
insertQuery.Parameters.Add(new QueryParameter("@MACADDR", System.Data.DbType.String, _macAddress));
insertQuery.Parameters.Add(new QueryParameter("@Description", System.Data.DbType.String, description));
await Task.Factory.StartNew(() =>
{
try
{
int rows = Database.ExecuteNonQuery(insertQuery);
}
catch (Exception)
{
// do nothing, don't let this crash the app
}
});
}
//public static bool Contains(this string source, string toCheck, StringComparison comp)
//{
// Contract.Requires(source != null);
// return source.IndexOf(toCheck, comp) >= 0;
//}
public static string GetCommentLegalLabel(DDLFieldOfCommentLegal commentLegalField, DataRow row, string fieldName, IDbDriver database)
{
if (commentLegalField != null)
{
string phaCode = row[fieldName].ToString();
string columnName = commentLegalField.TextColumnName;
string tableName = commentLegalField.SourceTableName;
Query selectQuery = database.CreateQuery("SELECT * FROM " + tableName); // may be inefficient if called over many contacts, perhaps look at caching this table and passing it in?
DataTable phaTable = database.Select(selectQuery);
foreach (DataRow phaRow in phaTable.Rows)
{
string rowValue = phaRow[0].ToString();
int dashIndex = rowValue.IndexOf("-");
if (dashIndex >= 0 && rowValue.Length >= dashIndex + 1)
{
string rowCode = rowValue.Substring(0, dashIndex);
string rowLabel = rowValue.Substring(dashIndex + 1).Trim();
if (rowCode.Equals(phaCode, StringComparison.OrdinalIgnoreCase))
{
return(rowLabel);
}
}
}
}
return(String.Empty);
}
public Query GenerateInsertQuery(IDbDriver database, int toViewId, int fromViewId)
{
Query insertQuery = database.CreateQuery("INSERT INTO [metaLinks] (FromRecordGuid, ToRecordGuid, FromViewId, ToViewId, [" + ContactTracing.Core.Constants.LAST_CONTACT_DATE_COLUMN_NAME + "], IsEstimatedContactDate, ContactType, RelationshipType) VALUES (" +
"@CurrentCaseGuid, @ContactGuid, @FromViewId, @ToViewId, @LastContactDate, @IsEstimatedContactDate, @ContactType, @Relationship)");
insertQuery.Parameters.Add(new QueryParameter("@CurrentCaseGuid", DbType.String, CaseVM.RecordId));
insertQuery.Parameters.Add(new QueryParameter("@ContactGuid", DbType.String, ContactRecordId));
insertQuery.Parameters.Add(new QueryParameter("@FromViewId", DbType.Int32, fromViewId));
insertQuery.Parameters.Add(new QueryParameter("@ToViewId", DbType.Int32, toViewId));
insertQuery.Parameters.Add(new QueryParameter("@LastContactDate", DbType.DateTime, DateLastContact));
insertQuery.Parameters.Add(new QueryParameter("@IsEstimatedContactDate", DbType.Boolean, IsContactDateEstimated));
if (ContactType.HasValue)
{
insertQuery.Parameters.Add(new QueryParameter("@ContactType", DbType.Byte, ContactType));
}
else
{
insertQuery.Parameters.Add(new QueryParameter("@ContactType", DbType.Byte, DBNull.Value));
}
if (string.IsNullOrEmpty(Relationship))
{
insertQuery.Parameters.Add(new QueryParameter("@Relationship", DbType.String, DBNull.Value));
}
else
{
insertQuery.Parameters.Add(new QueryParameter("@Relationship", DbType.String, Relationship));
}
return(insertQuery);
}
/// <summary>
/// GetDataTable
/// </summary>
/// <param name="IDbDriver">Database object</param>
/// <param name="pSQL">SQL Statement</param>
/// <returns>DataTable of results</returns>
public static DataTable GetDataTable(IDbDriver dbDriver, string pSQL)
{
System.Data.DataTable result = new System.Data.DataTable();
dbDriver.Select(dbDriver.CreateQuery(pSQL), result);
return(result);
}
/// <summary>
/// Create Insert Query
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pDataTable">Data Table</param>
/// <param name="pRow">Row</param>
/// <param name="pTableName">Table Name</param>
/// <returns>Query</returns>
public static Query CreateInsertQuery(string pFileString, DataTable pDataTable, DataRow pRow, string pTableName)
{
const string cs = "";
IDbDriver I = null;
using (System.Data.Common.DbConnection Conn = new System.Data.SqlClient.SqlConnection(cs))
{
Conn.Open();
//System.Data.Common.DbCommand cmd = new
}
return(I.CreateQuery(""));
}
public Query GenerateUpdateQuery(IDbDriver database, int toViewId, int fromViewId)
{
Query updateQuery = database.CreateQuery("UPDATE [metaLinks] SET " +
"[LastContactDate] = @LastContactDate, " +
"[ContactType] = @ContactType, " +
"[RelationshipType] = @RelationshipType, " +
"[IsEstimatedContactDate] = @IsEstimatedContactDate, " +
"[Tentative] = @Tentative " +
"WHERE " +
"[ToViewId] = @ToViewId AND " +
"[FromViewId] = @FromViewId AND " +
"[ToRecordGuid] = @ToRecordGuid AND " +
"[FromRecordGuid] = @FromRecordGuid");
updateQuery.Parameters.Add(new QueryParameter("@LastContactDate", DbType.DateTime, DateLastContact));
if (ContactType.HasValue)
{
updateQuery.Parameters.Add(new QueryParameter("@ContactType", DbType.Byte, ContactType));
}
else
{
updateQuery.Parameters.Add(new QueryParameter("@ContactType", DbType.Byte, DBNull.Value));
}
if (string.IsNullOrEmpty(Relationship))
{
updateQuery.Parameters.Add(new QueryParameter("@RelationshipType", DbType.String, DBNull.Value));
}
else
{
updateQuery.Parameters.Add(new QueryParameter("@RelationshipType", DbType.String, Relationship));
}
updateQuery.Parameters.Add(new QueryParameter("@IsEstimatedContactDate", DbType.Boolean, IsContactDateEstimated));
switch (IsTentative)
{
case true:
updateQuery.Parameters.Add(new QueryParameter("@Tentative", DbType.Byte, 1));
break;
case false:
updateQuery.Parameters.Add(new QueryParameter("@Tentative", DbType.Byte, 0));
break;
}
updateQuery.Parameters.Add(new QueryParameter("@ToViewId", DbType.Int32, toViewId));
updateQuery.Parameters.Add(new QueryParameter("@FromViewId", DbType.Int32, fromViewId));
updateQuery.Parameters.Add(new QueryParameter("@ToRecordGuid", DbType.String, ExposedCaseVM.RecordId));
updateQuery.Parameters.Add(new QueryParameter("@FromRecordGuid", DbType.String, SourceCaseVM.RecordId));
return(updateQuery);
}
public static object GetScalar(string pFileString, string pSQL)
{
object retval = null;
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
retval = driver.ExecuteScalar(driver.CreateQuery(pSQL));
}
return(retval);
}
void computeWorker_DoWork(object sender, DoWorkEventArgs e)
{
Result result = new Result();
EpiDataHelper DataHelper = e.Argument as EpiDataHelper;
if (DataHelper != null && DataHelper.Project != null && DataHelper.Project.CollectedData != null)
{
IDbDriver db = DataHelper.Project.CollectedData.GetDatabase();
DateTime today = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
DataTable labTable = DataHelper.LabTable;
double patientsTested = 0;
double samplesCollected = labTable.Rows.Count;//0;
foreach (CaseViewModel caseVM in DataHelper.CaseCollection)
{
if (caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded)
{
double samplesForCurrentCase = labTable.Select("[FKEY] = '" + caseVM.RecordId + "'").Count();
//samplesCollected = samplesCollected + samplesForCurrentCase;
if (samplesForCurrentCase > 0)
{
patientsTested++;
}
}
}
// Commented below is the old method, which simply counted everyone with lab sample records. Customer noted this needs to change. See new code.
//result.PatientsTested = patientsTested.ToString();
// New code is below.
Epi.Fields.RenderableField finalLabClassField = DataHelper.CaseForm.Fields["FinalLabClass"] as Epi.Fields.RenderableField;
if (finalLabClassField != null && finalLabClassField.Page != null)
{
string finalLabClassTableName = finalLabClassField.Page.TableName;
string queryText = "select distinct lrf.FKEY from " + finalLabClassTableName + " crf INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where (crf.FinalLabClass <> '' AND crf.FinalLabClass is not null)";
Query selectQuery = db.CreateQuery(queryText);
int count = db.Select(selectQuery).Rows.Count;
// TODO: Remove excluded cases
result.PatientsTested = count.ToString();
result.SamplesCollected = samplesCollected.ToString();
}
e.Result = result;
}
}
/// <summary>
/// Execute SQL
/// </summary>
/// <param name="pConnectionString">Connection string</param>
/// <param name="pSQL">SQL statement</param>
/// <param name="pTimeOut">Time out integer</param>
/// <returns>bool</returns>
public static bool ExecuteSQL(string pConnectionString, string pSQL, int pTimeOut)
{
bool result = false;
string connString = ParseConnectionString(pConnectionString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
driver.ExecuteNonQuery(driver.CreateQuery(pSQL));
result = true;
}
return(result);
}
/// <summary>
/// GetDataTable
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSQL">SQL Statement</param>
/// <returns>DataTable of results</returns>
public static DataTable GetDataTable(string pFileString, string pSQL)
{
System.Data.DataTable result = new System.Data.DataTable();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Query query = driver.CreateQuery(pSQL);
driver.Select(query, result);
}
return(result);
}
public Query GenerateDeleteQuery(IDbDriver database, int toViewId, int fromViewId)
{
string queryString = "DELETE * FROM [metaLinks] WHERE " +
"[FromRecordGuid] = @FromRecordGuid AND [ToRecordGuid] = @ToRecordGuid AND [FromViewId] = @FromViewId AND [ToViewId] = @ToViewId";
if (database.ToString().ToLower().Contains("sql"))
{
queryString = "DELETE FROM [metaLinks] WHERE " +
"[FromRecordGuid] = @FromRecordGuid AND [ToRecordGuid] = @ToRecordGuid AND [FromViewId] = @FromViewId AND [ToViewId] = @ToViewId";
}
Query deleteQuery = database.CreateQuery(queryString);
deleteQuery.Parameters.Add(new QueryParameter("@FromRecordGuid", DbType.String, SourceCaseVM.RecordId));
deleteQuery.Parameters.Add(new QueryParameter("@ToRecordGuid", DbType.String, ExposedCaseVM.RecordId));
deleteQuery.Parameters.Add(new QueryParameter("@FromViewId", DbType.Int32, fromViewId));
deleteQuery.Parameters.Add(new QueryParameter("@ToViewId", DbType.Int32, toViewId));
return(deleteQuery);
}
/// <summary>
/// Populate internal translation table using Epi 3.x db
/// </summary>
/// <param name="legacyLanguageDatabasePath"></param>
public void ReadDatabase(string legacyLanguageDatabasePath)
{
//IDbDriver db = DatabaseFactory.CreateDatabaseInstanceByFileExtension(legacyLanguageDatabasePath);
IDbDriverFactory dbFactory = DbDriverFactoryCreator.GetDbDriverFactory(Epi.Configuration.AccessDriver);
OleDbConnectionStringBuilder dbCnnStringBuilder = new OleDbConnectionStringBuilder();
dbCnnStringBuilder.FileName = legacyLanguageDatabasePath;
IDbDriver db = dbFactory.CreateDatabaseObject(dbCnnStringBuilder);
List <string> tableNames = db.GetTableNames();
foreach (string tableName in tableNames)
{
List <string> columnNames = db.GetTableColumnNames(tableName);
if (columnNames.Contains("English") && columnNames.Count == 2)
{
DataTable span = db.Select(db.CreateQuery("select * from " + tableName));
int sourceColumnOrdinal, translationColumnOrdinal;
if (string.Compare(span.Columns[0].ColumnName, "English", true) == 0)
{
sourceColumnOrdinal = 0;
translationColumnOrdinal = 1;
}
else
{
sourceColumnOrdinal = 1;
translationColumnOrdinal = 0;
}
foreach (DataRow row in span.Rows)
{
AddTranslationTableEntry(sourceColumnOrdinal, translationColumnOrdinal, row);
}
}
}
}
/// <summary>
/// Returns the count of tables
/// </summary>
/// <returns></returns>
public int GetRecordCount(Epi2000.View view)
{
try
{
#region Input Validation
if (view == null)
{
throw new System.ArgumentNullException("view");
}
#endregion Input Validation
string qryString = " select count(*) from " + db.InsertInEscape(view.TableNames[0]);
Query query = db.CreateQuery(qryString);
return(Int32.Parse((db.ExecuteScalar(query)).ToString()));
}
catch (Exception ex)
{
throw new ApplicationException("Could not retrieve record count.", ex); //TODO: move to shared strings
}
finally
{
}
}
private void btnOK_Click(object sender, EventArgs e)
{
if (db != null)
{
View sourceView = sourceProject.Views[formName];
string baseTableName = "t";
string fromClause = sourceView.FromViewSQL;
string sql = "SELECT [" + baseTableName + "].[GlobalRecordId] " + fromClause + " WHERE ";
foreach (IRowFilterCondition rowFc in rowFilterConditions)
{
sql = sql + rowFc.Sql;
}
selectQuery = db.CreateQuery(sql);
foreach (IRowFilterCondition rowFc in rowFilterConditions)
{
selectQuery.Parameters.Add(rowFc.Parameter);
}
}
}
void WriteResourcesToDatabase(GlobalizationDataSet ds, IDbDriver db)
{
AppendToLog("Please wait...");
List <TableColumn> columns = new List <TableColumn>();
columns.Add(new TableColumn("AssemblyName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ManifestResourceName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ResourceName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ResourceValue", GenericDbColumnType.StringLong, false));
columns.Add(new TableColumn("ResourceType", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ResourceVersion", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("SourceCultureName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("SourceValue", GenericDbColumnType.StringLong, 255, false));
//columns.Add(new TableColumn("CreationDate", GenericDbColumnType.String, 255, false));
DbDriverInfo dbInfo = new DbDriverInfo();
dbInfo.DBName = "CulturalResources";
dbInfo.DBCnnStringBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder();
dbInfo.DBCnnStringBuilder.ConnectionString = db.ConnectionString;
dbFactory.CreatePhysicalDatabase(dbInfo);
// db.CreateDatabase("CulturalResources");
string tableName = ds.CulturalResources.TableName;
db.CreateTable(tableName, columns);
StringBuilder sb = new StringBuilder();
sb.Append("insert into ");
sb.Append(tableName);
sb.Append(" (");
for (int x = 0; x < columns.Count; x++)
{
sb.Append(columns[x].Name);
if (x < columns.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(") values ");
sb.Append(" (");
for (int x = 0; x < columns.Count; x++)
{
sb.Append("@");
sb.Append(columns[x].Name);
if (x < columns.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(")");
Query insertQuery = db.CreateQuery(sb.ToString());
for (int x = 0; x < columns.Count; x++)
{
insertQuery.Parameters.Add(new QueryParameter("@" + columns[x].Name, DbType.String, columns[x].Name, columns[x].Name));
}
sb.Remove(0, sb.Length);
sb.Append("update [").Append(tableName);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET).Append(StringLiterals.SPACE);
sb.Append("set").Append(StringLiterals.SPACE);
for (int x = 0; x < columns.Count; x++)
{
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append(columns[x].Name);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sb.Append(StringLiterals.EQUAL);
sb.Append("@NewValue").Append(StringLiterals.SPACE);
sb.Append(", ");
}
sb.Append("where ");
for (int x = 0; x < columns.Count; x++)
{
sb.Append(columns[x].Name).Append(StringLiterals.SPACE);
sb.Append(StringLiterals.EQUAL);
sb.Append("@OldValue");
if (columns.Count > 1)
{
sb.Append(" and");
}
}
Query updateQuery = db.CreateQuery(sb.ToString());
for (int x = 0; x < columns.Count; x++)
{
updateQuery.Parameters.Add(new QueryParameter("@NewValue", DbType.String, columns[x].Name, columns[x].Name));
updateQuery.Parameters.Add(new QueryParameter("@OldValue", DbType.String, columns[x].Name, columns[x].Name));
updateQuery.Parameters[1].SourceVersion = DataRowVersion.Original;
}
db.Update(ds.Tables[0], tableName, insertQuery, null);
}
/// <summary>
/// performs execution of the WRITE command
/// </summary>
/// <returns>object</returns>
public override object Execute()
{
object result = null;
Context.AnalysisCheckCodeInterface.ShowWaitDialog("Exporting data...");
//string[] tmp = this.OutTarget.ToString().Split(':');
//string FilePath = null;
//if (tmp.Length <= 2)
//{
// FilePath = tmp[0];
//}
//else
//{
// FilePath = tmp[0] + ":" + tmp[1];
//}
//FilePath = FilePath.Trim().Trim(new char[] { '\'' });
//string TableName;
//if (tmp.Length > 1)
//{
// TableName = tmp[tmp.Length - 1].Replace("]", "").Replace("[", "").Trim().Trim('\'');
//}
//else
//{
// TableName = this.OutTarget;
// FilePath = this.Context.CurrentProject.CollectedDataConnectionString;
//}
CurrentDataTable = this.Context.DataSet.Tables["output"].Clone();
foreach (DataRow row in this.Context.GetOutput(new List <string>()))
{
CurrentDataTable.ImportRow(row);
}
if (this.IdentifierList[0] == "*")
{
for (int i = 0; i < CurrentDataTable.Columns.Count; i++)
{
IVariable var = (IVariable)this.Context.GetVariable(CurrentDataTable.Columns[i].ColumnName);
if (var != null)
{
if (var.VarType != VariableType.Global && var.VarType != VariableType.Permanent)
{
TempVariableList.Add(CurrentDataTable.Columns[i].ColumnName.ToUpperInvariant());
}
}
else
{
TempVariableList.Add(CurrentDataTable.Columns[i].ColumnName.ToUpperInvariant());
}
}
}
else
{
for (int i = 0; i < this.IdentifierList.Length; i++)
{
TempVariableList.Add(this.IdentifierList[i].ToUpperInvariant());
}
}
if (isExceptionList)
{
for (int i = CurrentDataTable.Columns.Count - 1; i > -1; i--)
{
if (TempVariableList.Contains(CurrentDataTable.Columns[i].ColumnName.ToUpperInvariant()))
{
//CurrentDataTable.Columns.Remove(CurrentDataTable.Columns[i]);
}
else
{
if (this.IdentifierList[0] == "*")
{
IVariable var = (IVariable)this.Context.GetVariable(CurrentDataTable.Columns[i].ColumnName);
if (var != null)
{
if (var != null && var.VarType != VariableType.Global && var.VarType != VariableType.Permanent)
{
VariableList.Add(var.Name.ToUpperInvariant());
}
}
}
else
{
VariableList.Add(CurrentDataTable.Columns[i].ColumnName.ToUpperInvariant());
}
}
}
}
else // is NOT an isExceptionList
{
for (int i = 0; i < CurrentDataTable.Columns.Count; i++)
{
if (TempVariableList.Contains(CurrentDataTable.Columns[i].ColumnName.ToUpperInvariant()))
{
VariableList.Add(CurrentDataTable.Columns[i].ColumnName.ToUpperInvariant());
}
else
{
//CurrentDataTable.Columns.Remove(CurrentDataTable.Columns[i]);
}
}
}
try
{
Dictionary <string, List <TableColumn> > WideTableColumns = null;
DataSets.Config.DataDriverDataTable dataDrivers = Configuration.GetNewInstance().DataDrivers;
IDbDriverFactory dbFactory = null;
foreach (DataSets.Config.DataDriverRow dataDriver in dataDrivers)
{
dbFactory = DbDriverFactoryCreator.GetDbDriverFactory(dataDriver.Type);
if (dbFactory.CanClaimConnectionString(FilePath))
{
break;
}
}
OutputDriver = DBReadExecute.GetDataDriver(FilePath, this.isConnectionString);
if (OutputDriver.GetType().Name.Equals("CsvFile", StringComparison.OrdinalIgnoreCase) || this.FileDataFormat.Equals("TEXT", StringComparison.OrdinalIgnoreCase))
{
if (!this.TableName.EndsWith(".txt") && !this.TableName.EndsWith(".csv") && !this.TableName.EndsWith("#csv") && !this.TableName.EndsWith("#txt"))
{
this.TableName = this.TableName + ".csv";
}
}
this.OutTarget = this.FilePath + ":" + this.TableName;
this.curFile = OutputDriver.DataSource;
if (!OutputDriver.CheckDatabaseExistance(FilePath, TableName, this.isConnectionString))
{
DbDriverInfo collectDbInfo = new DbDriverInfo();
Type SqlDriverType = Type.GetType("Epi.Data.SqlServer.SqlDBFactory, Epi.Data.SqlServer");
if (DBReadExecute.DataSource.GetType().AssemblyQualifiedName == SqlDriverType.AssemblyQualifiedName)
{
collectDbInfo.DBCnnStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
}
else
{
collectDbInfo.DBCnnStringBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder();
}
collectDbInfo.DBCnnStringBuilder.ConnectionString = dbFactory.ConvertFileStringToConnectionString(FilePath);
//collectDbInfo.DBCnnStringBuilder = dbFactory.RequestDefaultConnection(dbFactory.FilePath.Trim());
OutputDriver = dbFactory.CreateDatabaseObject(collectDbInfo.DBCnnStringBuilder);
collectDbInfo.DBName = OutputDriver.DbName;
dbFactory.CreatePhysicalDatabase(collectDbInfo);
}
bool?deleteSuccessful = null;
if (this.WriteMode.Equals("REPLACE", StringComparison.OrdinalIgnoreCase) && DBReadExecute.CheckDatabaseTableExistance(FilePath, TableName, this.isConnectionString))
{
deleteSuccessful = OutputDriver.DeleteTable(TableName);
}
List <TableColumn> TableColumns = new List <TableColumn>();
if (!DBReadExecute.CheckDatabaseTableExistance(FilePath, TableName, this.isConnectionString))
{
foreach (DataColumn column in CurrentDataTable.Columns)
{
if (VariableList.Contains(column.ColumnName.ToUpperInvariant()))
{
bool isPermanentVariable = false;
IVariable candidate = Context.MemoryRegion.GetVariable(column.ColumnName);
if (candidate != null && candidate.IsVarType(VariableType.Permanent))
{
isPermanentVariable = true;
}
if (isPermanentVariable == false)
{
TableColumn newTableColumn;
if (column.DataType.ToString() == "System.String")
{
if (column.MaxLength <= 0)
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), int.MaxValue, column.AllowDBNull);
}
else
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull);
}
}
else if (column.DataType.ToString() == "System.Guid")
{
if (column.MaxLength <= 0)
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), int.MaxValue, column.AllowDBNull);
}
else
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull);
}
}
else
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.AllowDBNull);
}
newTableColumn.AllowNull = column.AllowDBNull;
newTableColumn.IsIdentity = column.Unique;
TableColumns.Add(newTableColumn);
}
}
}
if
(
(
(!(OutputDriver.GetType().Name.Equals("AccessDatabase", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Access2007Database", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("ExcelWorkbook", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Excel2007Workbook", StringComparison.OrdinalIgnoreCase)) &&
VariableList.Count <= Max_Number_Columns) ||
OutputDriver.GetType().Name.Equals("SqlDatabase", StringComparison.OrdinalIgnoreCase)
)
)
{
OutputDriver.CreateTable(TableName, TableColumns);
}
else
{
if (OutputDriver.GetType().Name.Equals("ExcelWorkbook", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Excel2007Workbook", StringComparison.OrdinalIgnoreCase))
{
WideTableColumns = this.CreateExcelWideTable(TableColumns);
}
else if (!OutputDriver.GetType().Name.Equals("CsvFile", StringComparison.OrdinalIgnoreCase))
{
WideTableColumns = this.CreateAccessWideTable(TableColumns);
}
}
}
else // check that column name exists in destinationl
{
foreach (string columnName in VariableList)
{
bool isFound = false;
foreach (DataColumn column in CurrentDataTable.Columns)
{
if (column.ColumnName.ToUpperInvariant() == columnName.ToUpperInvariant())
{
isFound = true;
break;
}
}
if (!isFound)
{
TableColumn newTableColumn;
DataColumn column = CurrentDataTable.Columns[columnName];
if (column.DataType.ToString() == "System.String")
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull);
}
else if (column.DataType.ToString() == "System.Guid")
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull);
}
else
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.AllowDBNull);
}
newTableColumn.AllowNull = column.AllowDBNull;
newTableColumn.IsIdentity = column.Unique;
OutputDriver.AddColumn(TableName, newTableColumn);
}
}
if ((OutputDriver.GetType().Name.Equals("AccessDatabase", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Access2007Database", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("ExcelWorkbook", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Excel2007Workbook", StringComparison.OrdinalIgnoreCase)) && VariableList.Count > Max_Number_Columns)
{
foreach (DataColumn column in CurrentDataTable.Columns)
{
if (VariableList.Contains(column.ColumnName.ToUpperInvariant()))
{
bool isPermanentVariable = false;
IVariable candidate = Context.MemoryRegion.GetVariable(column.ColumnName);
if (candidate != null && candidate.IsVarType(VariableType.Permanent))
{
isPermanentVariable = true;
}
if (isPermanentVariable == false)
{
TableColumn newTableColumn;
if (column.DataType.ToString() == "System.String")
{
if (column.MaxLength <= 0)
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), int.MaxValue, column.AllowDBNull);
}
else
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull);
}
}
else if (column.DataType.ToString() == "System.Guid")
{
if (column.MaxLength <= 0)
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), int.MaxValue, column.AllowDBNull);
}
else
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull);
}
}
else
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.AllowDBNull);
}
newTableColumn.AllowNull = column.AllowDBNull;
newTableColumn.IsIdentity = column.Unique;
TableColumns.Add(newTableColumn);
}
}
}
if (OutputDriver.GetType().Name.Equals("ExcelWorkbook", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Excel2007Workbook", StringComparison.OrdinalIgnoreCase))
{
WideTableColumns = this.CreateExcelWideTable(TableColumns);
}
else
{
WideTableColumns = this.CreateAccessWideTable(TableColumns, false);
}
}
}
////APPEND| REPLACE | !Null
//if (this.WriteMode.Equals("REPLACE", StringComparison.OrdinalIgnoreCase))
//{
// WriteMethod = this.ReplaceWrite;
//}
//else
//{
// WriteMethod = this.AppendWrite;
//}
if (OutputDriver.GetType().Name.Equals("CsvFile", StringComparison.OrdinalIgnoreCase) || this.FileDataFormat.Equals("TEXT", StringComparison.OrdinalIgnoreCase))
{
if (TableColumns.Count == 0)
{
foreach (DataColumn column in CurrentDataTable.Columns)
{
if (VariableList.Contains(column.ColumnName.ToUpperInvariant()))
{
bool isPermanentVariable = false;
IVariable candidate = Context.MemoryRegion.GetVariable(column.ColumnName);
if (candidate != null && candidate.IsVarType(VariableType.Permanent))
{
isPermanentVariable = true;
}
if (isPermanentVariable == false)
{
TableColumn newTableColumn;
if (column.DataType.ToString() == "System.String")
{
if (column.MaxLength <= 0)
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), int.MaxValue, column.AllowDBNull);
}
else
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull);
}
}
else if (column.DataType.ToString() == "System.Guid")
{
if (column.MaxLength <= 0)
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), int.MaxValue, column.AllowDBNull);
}
else
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull);
}
}
else
{
newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.AllowDBNull);
}
newTableColumn.AllowNull = column.AllowDBNull;
newTableColumn.IsIdentity = column.Unique;
TableColumns.Add(newTableColumn);
}
}
}
}
this.WriteCSVFile(TableColumns);
}
else if ((OutputDriver.GetType().Name.Equals("AccessDatabase", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Access2007Database", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("ExcelWorkbook", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Excel2007Workbook", StringComparison.OrdinalIgnoreCase)) && VariableList.Count > Max_Number_Columns)
{
this.PopulateTable(WideTableColumns);
}
else
{
DataTable sourceTable = OutputDriver.GetTableData(TableName);
OutputDriver.IsBulkOperation = true; StringBuilder sqlquery = new StringBuilder();
int count = 0;
sqlquery.Append("create table [" + TableName + "] ( ");
foreach (string column in VariableList)
{
string columnName = String.Empty;
if (!column.Contains(".") && !OutputDriver.ColumnExists(TableName, column))
{
columnName = column;
if (count > 0)
{
sqlquery.Append(", ");
}
sqlquery.Append(" [" + columnName + "] " + DBReadExecute.SQLGetType(CurrentDataTable.Columns[column]));
count++;
}
}
sqlquery.Append(" )");
if (count > 0)
{
Query qr = OutputDriver.CreateQuery(sqlquery.ToString());
OutputDriver.ExecuteNonQuery(qr);
}
OutputDriver.IsBulkOperation = false;
//Insert data into table
////Open connection
////Setup Schema
////Loop through records
////Close connection
DataTable WritableTable = CurrentDataTable.Clone();
for (int i = WritableTable.Columns.Count - 1; i > -1; i--)
{
if (WritableTable.Columns[i].DataType == typeof(Guid))
{
WritableTable.Columns[i].DataType = typeof(String);
}
}
for (int i = WritableTable.Columns.Count - 1; i > -1; i--)
{
DataColumn col = WritableTable.Columns[i];
if (!VariableList.Contains(col.ColumnName.ToUpperInvariant()))
{
WritableTable.Columns.Remove(col);
}
}
foreach (DataRow row in CurrentDataTable.Select("", this.Context.SortExpression.ToString()))
{
DataRow newRow = WritableTable.NewRow();
foreach (string column in VariableList)
{
newRow[column] = row[column];
}
WritableTable.Rows.Add(newRow);
}
System.Data.Common.DbDataReader DataReader = WritableTable.CreateDataReader();
DBReadExecute.InsertBulkRows(FilePath, "Select * From [" + TableName + "]", DataReader, SetGadgetStatusHandler);
if (CurrentDataTable.Rows.Count > 0)
{
this.statusMessage = "Export completed successfully, ";
}
else
{
this.statusMessage = "Export was not completed successfully, ";
}
this.statusMessage += CurrentDataTable.Rows.Count.ToString() + " records written.";
DataReader.Close();
}
}
catch (Exception ex)
{
this.statusMessage = "Problems exporting records: " + ex.ToString();
System.Console.Write(ex);
}
finally
{
Context.AnalysisCheckCodeInterface.HideWaitDialog();
}
/*
*
*
*
*
* Comments
* Records deleted in Enter or selected in Analysis are handled as in other Analysis commands.
* Defined variables may be written, allowing the creation of a new Epi Info file that makes the changes permanent.
* Global and permanent variables will not be written unless explicitly specified.
* To write only selected variables, the word EXCEPT may be inserted to indicate all variables except those following EXCEPT.
* If the output file specified does not exist, the WRITE command will attempt to create it.
* Either APPEND or REPLACE must be specified to indicate that an existing file/table by the
* same name will be erased or records will be appended to the existing file/table.
* If some, but not all, of the fields being written match those in an existing file during an APPEND,
* the unmatched fields are added to the output table.
* For Epi 6 REC or Access/EpiInfo table outputs, if there are no records,
* the WRITE command creates a table/file with variable information but no data.
*
* WRITE <METHOD> {<output type>} {<project>:}table {[<variable(s)>]}
* WRITE <METHOD> {<output type>} {<project>:}table * EXCEPT {[<variable(s)>]}
*
* The <METHOD> represents either REPLACE or APPEND
* The <project> represents the path and filename of the output.
* The <variable(s)> represents one or more variable names.
* The <output type> represents the following allowable outputs:
*
* Database Type Specifier Element
*
* Jet "Access 97" "Access 2000"
* "Epi 2000" <path:<table>
* dBase III "dBase III" <path>
* dBase IV "dBase IV" <path>
* dBase 5.0 "dBase 5.0" <path>
* Paradox 3.x "Paradox 3.x" <path>
* Paradox 4.x "Paradox 4.x" <path>
* FoxPro 2.0 "FoxPro 2.0" <path>
* FoxPro 2.5 "FoxPro 2.5" <path>
* FoxPro 2.6 "FoxPro 2.6" <path>
* Excel 3.0 "Excel 3.0" <path>
* Excel 4.0 "Excel 4.0" <path>
* Epi Info 6 "Epi6" <path>
* Text (Delimited) "Text" <path>
*
*/
args.Add("COMMANDNAME", CommandNames.WRITE);
args.Add("WRITEMODE", this.WriteMode);
args.Add("OUTTARGET", this.OutTarget);
args.Add("STATUS", this.statusMessage);
//args.Add("PROGRESST", this.progress.ToString());
//args.Add("ROWCOUNT", CurrentDataTable.Select("", this.Context.SortExpression.ToString()).Length.ToString());
this.Context.AnalysisCheckCodeInterface.Display(args);
return(result);
}
/// <summary>
/// Gets a code table by name
/// </summary>
/// <param name="tableName">Name of code table</param>
/// <returns>Contents of the code table</returns>
//public override DataTable GetCodeTable(string tableName)
public DataTable GetCodeTable(string tableName)
{
Query query = db.CreateQuery("select * from " + tableName);
return(db.Select(query));
}
void computeWorker_DoWork(object sender, DoWorkEventArgs e)
{
Result result = new Result();
EpiDataHelper DataHelper = e.Argument as EpiDataHelper;
Epi.Fields.DateTimeField dtField = DataHelper.LabForm.Fields["DateSampleCollected"] as Epi.Fields.DateTimeField;
if (DataHelper != null && dtField != null)
{
DateTime today = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
int count = IsoCount; // (DataHelper.IsolatedCollectionView as ListCollectionView).Count;//.View.Cast<CaseViewModel>().Count();
result.IsoCurrentT = count.ToString();
count = (from caseVM in DataHelper.CaseCollection
where caseVM.DateDeathCurrentOrFinal.HasValue &&
(caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Confirmed || caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Probable) &&
caseVM.DateDeathCurrentOrFinal.HasValue &&
caseVM.DateDeathCurrentOrFinal.Value.Day == today.Day &&
caseVM.DateDeathCurrentOrFinal.Value.Month == today.Month &&
caseVM.DateDeathCurrentOrFinal.Value.Year == today.Year
select caseVM).Count();
result.NewDeathsT = count.ToString();
count = (from caseVM in DataHelper.CaseCollection
where (caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Confirmed || caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Probable || caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Suspect) &&
caseVM.DateReport.HasValue &&
caseVM.DateReport.Value.Day == today.Day &&
caseVM.DateReport.Value.Month == today.Month &&
caseVM.DateReport.Value.Year == today.Year
select caseVM).Count();
result.NewCasesT = count.ToString();
IDbDriver db = DataHelper.Project.CollectedData.GetDatabase();
string queryText = "select count(*) FROM " + dtField.Page.TableName + " " +
"WHERE [DateSampleCollected] >= @Today";
Query selectQuery = db.CreateQuery(queryText);
selectQuery.Parameters.Add(new QueryParameter("@Today", System.Data.DbType.Date, DateTime.Today));
count = (int)db.ExecuteScalar(selectQuery);
result.PendingT = count.ToString();
#region Yesterday
DateTime yesterday = (DateTime.Today).AddDays(-1);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.DateDeathCurrentOrFinal.HasValue &&
(caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Confirmed || caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Probable) &&
caseVM.DateDeathCurrentOrFinal.HasValue &&
caseVM.DateDeathCurrentOrFinal.Value.Day == yesterday.Day &&
caseVM.DateDeathCurrentOrFinal.Value.Month == yesterday.Month &&
caseVM.DateDeathCurrentOrFinal.Value.Year == yesterday.Year
select caseVM).Count();
result.NewDeathsY = count.ToString();
count = (from caseVM in DataHelper.CaseCollection
where (caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Confirmed || caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Probable || caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Suspect) &&
caseVM.DateReport.HasValue &&
caseVM.DateReport.Value.Day == yesterday.Day &&
caseVM.DateReport.Value.Month == yesterday.Month &&
caseVM.DateReport.Value.Year == yesterday.Year
select caseVM).Count();
result.NewCasesY = count.ToString();
queryText = "select count(*) FROM " + dtField.Page.TableName + " " +
"WHERE [DateSampleCollected] < @Today AND [DateSampleCollected] >= @Yesterday";
selectQuery = db.CreateQuery(queryText);
selectQuery.Parameters.Add(new QueryParameter("@Today", System.Data.DbType.Date, DateTime.Today));
selectQuery.Parameters.Add(new QueryParameter("@Yesterday", System.Data.DbType.Date, yesterday));
count = (int)db.ExecuteScalar(selectQuery);
result.PendingY = count.ToString();
#endregion // Yesterday
e.Result = result;
}
}
private void btnOK_Click(object sender, EventArgs e)
{
KeyFields = new List <Field>();
if (lbxFields.SelectedItems.Count == 0)
{
return;
}
try
{
#region Check #1 - Make sure key is unique on parent form
IDbDriver db = Project.CollectedData.GetDatabase();
Query selectQuery = db.CreateQuery("SELECT Count(*) FROM [" + Form.TableName + "]");
int recordCount = (int)db.ExecuteScalar(selectQuery);
WordBuilder wb = new WordBuilder(",");
foreach (Field field in Form.Fields)
{
if (field is RenderableField && lbxFields.SelectedItems.Contains(field.Name))
{
wb.Add(field.Name);
}
}
selectQuery = db.CreateQuery("SELECT DISTINCT " + wb.ToString() + " " + Form.FromViewSQL);
int distinctCount = db.Select(selectQuery).Rows.Count; // probably better way to do this, but unsure if can be made generic... this query is most generic across DB types?
if (distinctCount == recordCount)
{
foreach (Field field in Form.Fields)
{
if (field is RenderableField && lbxFields.SelectedItems.Contains(field.Name))
{
KeyFields.Add(field);
}
}
}
else
{
if (lbxFields.SelectedItems.Count == 1)
{
Epi.Windows.MsgBox.ShowError(String.Format("The selected match key ({0}) is not unique.", lbxFields.SelectedItem.ToString()));
}
else if (lbxFields.SelectedItems.Count > 1)
{
WordBuilder keyFields = new WordBuilder(",");
foreach (string s in lbxFields.SelectedItems)
{
keyFields.Add(s);
}
Epi.Windows.MsgBox.ShowError(String.Format("The selected match key ({0}) is not unique.", keyFields.ToString()));
}
this.DialogResult = System.Windows.Forms.DialogResult.None;
return;
}
#endregion // Check #1 - Make sure key is unique on parent form
// Currently, disable match keys if related forms exist. TODO: Change this later?
foreach (View otherForm in Project.Views)
{
if (otherForm != Form && Epi.ImportExport.ImportExportHelper.IsFormDescendant(otherForm, Form))
{
Epi.Windows.MsgBox.ShowError("Custom match keys cannot be used to package a form that contains child forms.");
this.DialogResult = System.Windows.Forms.DialogResult.None;
return;
}
}
//#region Check #2 - Make sure key exists in other forms in the hierarchy and that it's the same field type
//foreach (View otherForm in Project.Views)
//{
// if (otherForm != Form && Epi.ImportExport.ImportExportHelper.IsFormDescendant(otherForm, Form))
// {
// foreach (Field field in KeyFields)
// {
// if (!otherForm.Fields.Contains(field.Name))
// {
// Epi.Windows.MsgBox.ShowError(
// String.Format(
// "The selected field {0} does not exist in the child form {1}. The keys selected in this dialog must exist across all child forms.",
// field.Name, otherForm.Name));
// this.DialogResult = System.Windows.Forms.DialogResult.None;
// return;
// }
// else
// {
// Field otherField = otherForm.Fields[field.Name];
// if (otherField.FieldType != field.FieldType)
// {
// Epi.Windows.MsgBox.ShowError(
// String.Format(
// "The selected field {0} is implemented as a different field type on child form {1}. The keys selected in this dialog must exist across all child forms and those fields must be of the same field type.",
// field.Name, otherForm.Name));
// this.DialogResult = System.Windows.Forms.DialogResult.None;
// return;
// }
// }
// }
// }
//}
//#endregion // Check #2 - Make sure key exists in other forms in the hierarchy and that it's the same field type
}
catch (Exception ex)
{
Epi.Windows.MsgBox.ShowException(ex);
this.DialogResult = System.Windows.Forms.DialogResult.None;
}
}
/// <summary>
/// Is version Epi Info 7 Metadata flag.
/// </summary>
/// <param name="db">Database driver</param>
/// <returns>True/False on test of EI7 Metadata.</returns>
public static bool IsEpi7Metadata(IDbDriver db)
{
#region Input Validation
if (db == null)
{
throw new ArgumentNullException("DB");
}
#endregion
try
{
// Open the database and look for dbInfo table, find Epi Version ...
bool isEpi7Metadata = false;
Query query = db.CreateQuery("select [EpiVersion], [Purpose] from metaDBInfo");
DataTable results = db.Select(query);
if (results.Rows.Count > 0)
{
foreach (DataRow row in results.Rows)
{
isEpi7Metadata = (row["EpiVersion"].ToString().Substring(0, 1) == "7") && ((row["Purpose"].ToString() == "0") || (row["Purpose"].ToString() == "1"));
}
}
return isEpi7Metadata;
}
finally
{
}
}
/// <summary>
/// Checks for problems in the source project
/// </summary>
protected void CheckForProblems()
{
IDbDriver driver = SourceProject.CollectedData.GetDatabase();
if (driver == null)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError("Data driver is null.", "999999");
throw new InvalidOperationException("Data driver cannot be null");
}
// Check #1 - Make sure the base table exists and that it has a Global Record Id field, Record status field, and Unique key field.
DataTable dt = driver.GetTableData(SourceForm.TableName, "GlobalRecordId, RECSTATUS, UniqueKey");
if (dt == null)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError("Source table is null.", "999998");
throw new InvalidOperationException("Source table cannot be null");
}
else if (dt.Columns.Count == 0)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError("Source table has zero columns.", "999997");
throw new InvalidOperationException("Source table cannot have zero columns");
}
else if (dt.Columns.Count == 1)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError("Source table has only one column.", "999996");
throw new InvalidOperationException("Source table cannot have only one column");
}
int baseTableRowCount = dt.Rows.Count;
// Check #2a - Make sure GlobalRecordId is a string.
if (!dt.Columns[0].DataType.ToString().Equals("System.String"))
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_INVALID_GUID_COLUMN, "101000");
throw new ApplicationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_INVALID_GUID_COLUMN);
}
// Check #2b - Make sure RECSTATUS is a number
if (!(dt.Columns[1].DataType.ToString().Equals("System.Byte") || dt.Columns[1].DataType.ToString().Equals("System.Int16") || dt.Columns[1].DataType.ToString().Equals("System.Int32")))
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_INVALID_RECSTATUS_COLUMN, "101001");
throw new ApplicationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_INVALID_RECSTATUS_COLUMN);
}
// Check #3 - Make sure GlobalRecordId values haven't been replaced with something that isn't actually a GUID.
// For performance reasons only the first few values are checked.
if (baseTableRowCount >= 1)
{
string value = dt.Rows[0][0].ToString();
System.Guid guid = new Guid(value);
if (baseTableRowCount >= 30)
{
for (int i = 0; i < 30; i++)
{
value = dt.Rows[i][0].ToString();
guid = new Guid(value);
}
}
}
// Check #4a - See if global record ID values are distinct on the base table.
Query selectDistinctQuery = driver.CreateQuery("SELECT DISTINCT [GlobalRecordId] FROM [" + SourceForm.TableName + "]");
DataTable distinctTable = driver.Select(selectDistinctQuery);
if (distinctTable.Rows.Count != baseTableRowCount)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE, "101002");
throw new ApplicationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE);
}
// Check #4b - See if global record ID values are distinct on each page table.
foreach (Page page in SourceForm.Pages)
{
selectDistinctQuery = driver.CreateQuery("SELECT DISTINCT [GlobalRecordId] FROM [" + page.TableName + "]");
distinctTable = driver.Select(selectDistinctQuery);
if (distinctTable.Rows.Count != baseTableRowCount)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE_PAGE, "101003");
throw new ApplicationException(string.Format(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE_PAGE, page.TableName));
}
}
// Check #5 - Make sure RECSTATUS has valid values.
selectDistinctQuery = driver.CreateQuery("SELECT DISTINCT [RecStatus] FROM [" + SourceForm.TableName + "]");
distinctTable = driver.Select(selectDistinctQuery);
foreach (DataRow row in distinctTable.Rows)
{
if (!row[0].ToString().Equals("1") && !row[0].ToString().Equals("0"))
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_RECSTATUS_VALUES_INVALID, "101004");
throw new ApplicationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_RECSTATUS_VALUES_INVALID);
}
}
// Check #7 - Should never get here because the UI should prevent it, but do a check just in case
if (SourceForm.IsRelatedView == true)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_RELATED_FORM, "101005");
throw new ApplicationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_RELATED_FORM);
}
distinctTable = null;
selectDistinctQuery = null;
driver.Dispose();
driver = null;
}
/// <summary>
/// GetDataTable
/// </summary>
/// <param name="IDbDriver">Database object</param>
/// <param name="pSQL">SQL Statement</param>
/// <returns>DataTable of results</returns>
public static DataTable GetDataTable(IDbDriver dbDriver, string pSQL)
{
System.Data.DataTable result = new System.Data.DataTable();
dbDriver.Select(dbDriver.CreateQuery(pSQL), result);
return result;
}
void WriteResourcesToDatabase(GlobalizationDataSet ds, IDbDriver db)
{
AppendToLog("Please wait...");
List<TableColumn> columns = new List<TableColumn>();
columns.Add(new TableColumn("AssemblyName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ManifestResourceName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ResourceName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ResourceValue", GenericDbColumnType.StringLong, false));
columns.Add(new TableColumn("ResourceType", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ResourceVersion", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("SourceCultureName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("SourceValue", GenericDbColumnType.StringLong, 255, false));
//columns.Add(new TableColumn("CreationDate", GenericDbColumnType.String, 255, false));
DbDriverInfo dbInfo = new DbDriverInfo();
dbInfo.DBName = "CulturalResources";
dbInfo.DBCnnStringBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder();
dbInfo.DBCnnStringBuilder.ConnectionString = db.ConnectionString;
dbFactory.CreatePhysicalDatabase(dbInfo);
// db.CreateDatabase("CulturalResources");
string tableName = ds.CulturalResources.TableName;
db.CreateTable(tableName, columns);
StringBuilder sb = new StringBuilder();
sb.Append("insert into ");
sb.Append(tableName);
sb.Append(" (");
for (int x = 0; x < columns.Count; x++)
{
sb.Append(columns[x].Name);
if (x < columns.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(") values ");
sb.Append(" (");
for (int x = 0; x < columns.Count; x++)
{
sb.Append("@");
sb.Append(columns[x].Name);
if (x < columns.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(")");
Query insertQuery = db.CreateQuery(sb.ToString());
for (int x = 0; x < columns.Count; x++)
{
insertQuery.Parameters.Add(new QueryParameter("@" + columns[x].Name, DbType.String, columns[x].Name, columns[x].Name));
}
sb.Remove(0, sb.Length);
sb.Append("update [").Append(tableName);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET).Append(StringLiterals.SPACE);
sb.Append("set").Append(StringLiterals.SPACE);
for (int x = 0; x < columns.Count; x++)
{
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append(columns[x].Name);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sb.Append(StringLiterals.EQUAL);
sb.Append("@NewValue").Append(StringLiterals.SPACE);
sb.Append(", ");
}
sb.Append("where ");
for (int x = 0; x < columns.Count; x++)
{
sb.Append(columns[x].Name).Append(StringLiterals.SPACE);
sb.Append(StringLiterals.EQUAL);
sb.Append("@OldValue");
if (columns.Count > 1)
{
sb.Append(" and");
}
}
Query updateQuery = db.CreateQuery(sb.ToString());
for (int x = 0; x < columns.Count; x++)
{
updateQuery.Parameters.Add(new QueryParameter("@NewValue", DbType.String, columns[x].Name, columns[x].Name));
updateQuery.Parameters.Add(new QueryParameter("@OldValue", DbType.String, columns[x].Name, columns[x].Name));
updateQuery.Parameters[1].SourceVersion = DataRowVersion.Original;
}
db.Update(ds.Tables[0], tableName, insertQuery, null);
}
public static DataTable JoinPageTables(IDbDriver db, View vw)
{
DataTable unfilteredTable = new DataTable();
// Get the form's field count, adding base table fields plus GUID field for each page. If less than 255, use SQL relate; otherwise, >255 exceeds OLE field capacity and we need to use a less efficient method
if (vw.Fields.DataFields.Count + vw.Pages.Count + 5 < 255 && vw.Pages.Count < 15)
{
unfilteredTable = db.Select(db.CreateQuery("SELECT * " + vw.FromViewSQL));
if (unfilteredTable.Columns["RecStatus"] == null && unfilteredTable.Columns["t.RecStatus"] != null)
{
unfilteredTable.Columns["t.RecStatus"].ColumnName = "RecStatus";
}
if (unfilteredTable.Columns.Contains("t.GlobalRecordId"))
{
unfilteredTable.Columns["t.GlobalRecordId"].ColumnName = "GlobalRecordId";
}
foreach (Epi.Page page in vw.Pages)
{
string pageGUIDName = page.TableName + "." + "GlobalRecordId";
if (unfilteredTable.Columns.Contains(pageGUIDName))
{
unfilteredTable.Columns.Remove(pageGUIDName);
}
}
}
else
{
DataTable viewTable = new DataTable();
viewTable = db.GetTableData(vw.TableName, "GlobalRecordId, UniqueKey, RECSTATUS");
viewTable.TableName = vw.TableName;
DataTable relatedTable = new DataTable("relatedTable");
//int total = 0;
//// Get totals for percent completion calculation
//foreach (Epi.Page page in vw.Pages)
//{
// string pageColumnsToSelect = String.Empty;
// foreach (Field field in page.Fields)
// {
// if (field is RenderableField && field is IDataField)
// {
// total++;
// }
// }
//}
//total = total * RecordCount;
//int counter = 0;
foreach (Epi.Page page in vw.Pages)
{
List <string> pageColumnsToSelect = new List <string>();
foreach (Field field in page.Fields)
{
if (field is RenderableField && field is IDataField)
{
pageColumnsToSelect.Add(field.Name);
}
}
pageColumnsToSelect.Add("GlobalRecordId");
DataTable pageTable = db.GetTableData(page.TableName, pageColumnsToSelect);
pageTable.TableName = page.TableName;
foreach (DataColumn dc in pageTable.Columns)
{
if (dc.ColumnName != "GlobalRecordId")
{
viewTable.Columns.Add(dc.ColumnName, dc.DataType);
}
}
//try
//{
// assume GlobalUniqueId column is unique and try to make it the primary key.
DataColumn[] parentPrimaryKeyColumns = new DataColumn[1];
parentPrimaryKeyColumns[0] = viewTable.Columns["GlobalRecordId"];
viewTable.PrimaryKey = parentPrimaryKeyColumns;
//}
//catch (Exception)
//{
//}
foreach (DataRow row in pageTable.Rows)
{
DataRow viewRow = viewTable.Rows.Find(row["GlobalRecordId"].ToString());
viewRow.BeginEdit();
if (viewRow["GlobalRecordId"].ToString().Equals(row["GlobalRecordId"].ToString()))
{
foreach (DataColumn dc in pageTable.Columns)
{
if (dc.ColumnName == "GlobalRecordId")
{
continue;
}
if (row[dc.ColumnName] != DBNull.Value)
{
viewRow[dc.ColumnName] = row[dc];
}
//counter++;
//if (counter % 200 == 0 && inputs != null)
//{
// if (counter > total)
// {
// counter = total;
// }
// inputs.UpdateGadgetProgress(((double)counter / (double)total) * 100);
// if (total == 0)
// {
// inputs.UpdateGadgetStatus(SharedStrings.DASHBOARD_GADGET_STATUS_RELATING_PAGES_NO_PROGRESS);
// }
// else
// {
// inputs.UpdateGadgetStatus(string.Format(SharedStrings.DASHBOARD_GADGET_STATUS_RELATING_PAGES, ((double)counter / (double)total).ToString("P0")));
// }
// if (inputs.IsRequestCancelled())
// {
// return null;
// }
//}
}
}
viewRow.EndEdit();
}
}
unfilteredTable = viewTable;
}
return(unfilteredTable);
}
/// <summary>
/// Finds Epi Version and Purpose of the database.
/// </summary>
/// <param name="db">Abstract data type for database.</param>
/// <returns>Results of test.</returns>
public static bool IsEpi7CollectedData(IDbDriver dbDriver)
{
#region Input Validation
if (dbDriver == null)
{
throw new ArgumentNullException("DB");
}
#endregion
try
{
// Open the database and look for dbInfo table, find Epi Version and purpose ...
bool isEpi7CollectedData = false;
Query query = dbDriver.CreateQuery("select" + Util.InsertIn(dbDriver.InsertInEscape("EpiVersion"), StringLiterals.SPACE) + StringLiterals.COMMA + Util.InsertIn(dbDriver.InsertInEscape("Purpose"), StringLiterals.SPACE) +
"from" + StringLiterals.SPACE + "metaDBInfo");
DataTable results = dbDriver.Select(query);
if (results.Rows.Count > 0)
{
foreach (DataRow row in results.Rows)
{
isEpi7CollectedData = (row["EpiVersion"].ToString().Substring(0, 1) == "7") && ((row["Purpose"].ToString() == "0") || (row["Purpose"].ToString() == "2"));
}
}
return isEpi7CollectedData;
}
finally
{
}
}
protected virtual void WriteLinksData(XmlWriter writer)
{
MinorProgress = 0;
OnMinorProgressChanged();
writer.WriteStartElement("Links");
string selectQueryText = "SELECT * FROM metaLinks";
IDbDriver db = Project.CollectedData.GetDatabase();
Query selectQuery = db.CreateQuery(selectQueryText);
CultureInfo format = CultureInfo.InvariantCulture;
double totalRecords = Convert.ToDouble(db.ExecuteScalar(db.CreateQuery("SELECT COUNT(*) FROM metaLinks")));
double inc = 100 / totalRecords;
bool filter = _contactGuids != null;
string contactFormId = Project.Views[Core.Constants.CONTACT_FORM_NAME].Id.ToString();
using (IDataReader reader = db.ExecuteReader(selectQuery))
{
while (reader.Read())
{
string fromRecordGuid = reader["FromRecordGuid"].ToString();
string toRecordGuid = reader["ToRecordGuid"].ToString();
string toViewId = reader["ToViewId"].ToString();
if (!_includeContacts && toViewId == contactFormId)
{
continue;
}
if (filter && toViewId == contactFormId && !_contactGuids.Contains(toRecordGuid))
{
continue;
}
writer.WriteStartElement("Link");
#region Link Fields
writer.WriteStartElement("FromRecordGuid");
writer.WriteString(fromRecordGuid);
writer.WriteEndElement();
writer.WriteStartElement("ToRecordGuid");
writer.WriteString(toRecordGuid);
writer.WriteEndElement();
writer.WriteStartElement("FromViewId");
writer.WriteString(reader["FromViewId"].ToString());
writer.WriteEndElement();
writer.WriteStartElement("ToViewId");
writer.WriteString(toViewId);
writer.WriteEndElement();
writer.WriteStartElement("LastContactDate");
//writer.WriteString(Convert.ToDateTime(reader["LastContactDate"]).Ticks.ToString());
writer.WriteString(Convert.ToDateTime(reader["LastContactDate"]).ToString(format.DateTimeFormat.ShortDatePattern));
writer.WriteEndElement();
if (!String.IsNullOrEmpty(reader["ContactType"].ToString()))
{
writer.WriteStartElement("ContactType");
writer.WriteString(reader["ContactType"].ToString());
writer.WriteEndElement();
}
if (!String.IsNullOrEmpty(reader["RelationshipType"].ToString()))
{
writer.WriteStartElement("RelationshipType");
writer.WriteString(reader["RelationshipType"].ToString());
writer.WriteEndElement();
}
if (!String.IsNullOrEmpty(reader["Tentative"].ToString()))
{
writer.WriteStartElement("Tentative");
writer.WriteString(reader["Tentative"].ToString());
writer.WriteEndElement();
}
writer.WriteStartElement("IsEstimatedContactDate");
writer.WriteString(reader["IsEstimatedContactDate"].ToString());
writer.WriteEndElement();
for (int i = 1; i <= 21; i++)
{
string dayName = "Day" + i.ToString();
string dayNotesName = dayName + "Notes";
if (!String.IsNullOrEmpty(reader[dayName].ToString()))
{
writer.WriteStartElement(dayName);
writer.WriteString(reader[dayName].ToString());
writer.WriteEndElement();
}
if (!String.IsNullOrEmpty(reader[dayNotesName].ToString()))
{
writer.WriteStartElement(dayNotesName);
writer.WriteString(reader[dayNotesName].ToString());
writer.WriteEndElement();
}
}
writer.WriteStartElement("LinkId");
writer.WriteString(reader["LinkId"].ToString());
writer.WriteEndElement();
#endregion // Link Fields
writer.WriteEndElement();
MinorProgress += inc;
OnMinorProgressChanged();
OnMinorStatusChanged(String.Format("{0} of relationship records exported...", (MinorProgress / 100).ToString("P0")));
}
}
writer.WriteEndElement();
}
protected virtual void WriteFormData(XmlWriter writer, View form)
{
if (form.Fields.DataFields.Count > Core.Constants.EXPORT_FIELD_LIMIT && Project.CollectedData.GetDatabase() is Epi.Data.Office.OleDbDatabase)
{
// OleDB can't handle a SELECT * with a lot of fields, so do page-by-page processing instead
WriteFormPagedData(writer, form);
return;
}
MinorProgress = 0;
OnMinorProgressChanged();
List <string> fieldsToNull = FieldsToNull[form.Name];
writer.WriteStartElement("Form");
writer.WriteAttributeString("Name", form.Name);
writer.WriteAttributeString("Pages", form.Pages.Count.ToString());
writer.WriteAttributeString("IsRelatedForm", form.IsRelatedView.ToString());
WriteFormMetadata(writer, form);
writer.WriteStartElement("Data");
IDbDriver db = Project.CollectedData.GetDatabase();
Query selectQuery = GetFormSelectQuery(form);
List <string> labGuids = null;
OnMinorStatusChanged("Applying filters for " + form.Name + "...");
if (Filters.ContainsKey(Core.Constants.CASE_FORM_NAME) && Filters[Core.Constants.CASE_FORM_NAME].Count() > 0 && _caseGuids != null)
{
if (form.Name.Equals(Core.Constants.CONTACT_FORM_NAME, StringComparison.OrdinalIgnoreCase))
{
_contactGuids = new List <string>();
Query guidSelectQuery = db.CreateQuery("SELECT C.GlobalRecordId, M.FromRecordGuid FROM (ContactEntryForm C INNER JOIN metaLinks M ON C.GlobalRecordId = M.ToRecordGuid) WHERE M.ToViewId = @ToViewId AND M.FromViewId = 1");
guidSelectQuery.Parameters.Add(new QueryParameter("@ToViewId", DbType.Int32, ContactFormId));
using (IDataReader reader = db.ExecuteReader(guidSelectQuery))
{
while (reader.Read())
{
string caseGuid = reader["FromRecordGuid"].ToString();
if (_caseGuids.Contains(caseGuid))
{
_contactGuids.Add(reader["GlobalRecordId"].ToString());
}
}
}
}
else if (form.Name.Equals(Core.Constants.LAB_FORM_NAME))
{
labGuids = new List <string>();
Query guidSelectQuery = db.CreateQuery("SELECT L.GlobalRecordId, L.FKEY FROM (CaseInformationForm C INNER JOIN LaboratoryResultsForm L ON C.GlobalRecordId = L.FKEY)");
using (IDataReader reader = db.ExecuteReader(guidSelectQuery))
{
while (reader.Read())
{
string labGuid = reader["GlobalRecordId"].ToString();
string caseGuid = reader["FKEY"].ToString();
if (_caseGuids.Contains(caseGuid))
{
labGuids.Add(reader["GlobalRecordId"].ToString());
}
}
}
}
}
OnMinorStatusChanged("Getting total row counts for " + form.Name + "...");
string recStatusClause = "RECSTATUS = 1";
if (Scope == Epi.RecordProcessingScope.Both)
{
recStatusClause = "RECSTATUS >= 0";
}
else if (Scope == Epi.RecordProcessingScope.Deleted)
{
recStatusClause = "RECSTATUS = 0";
}
Query countQuery = db.CreateQuery("SELECT COUNT(*) FROM " + form.TableName + " WHERE " + recStatusClause + " AND ((LastSaveTime >= @StartDate AND LastSaveTime <= @EndDate) OR LastSaveTime IS NULL)");
countQuery.Parameters.Add(new QueryParameter("@StartDate", DbType.DateTime, StartDate));
countQuery.Parameters.Add(new QueryParameter("@EndDate", DbType.DateTime, EndDate));
double totalRecords = Convert.ToDouble(db.ExecuteScalar(countQuery));
double inc = 100 / totalRecords;
bool isCaseForm = form.Name.Equals(Core.Constants.CASE_FORM_NAME, StringComparison.OrdinalIgnoreCase);
if (isCaseForm)
{
_caseGuids = new HashSet <string>();
}
using (IDataReader reader = db.ExecuteReader(selectQuery))
{
//int i = 1;
while (reader.Read())
{
string recordGuid = reader["t.GlobalRecordId"].ToString();
string lastSaveTimeStr = String.Empty;
long? lastSaveTimeLong = null;
DateTime?lastSaveTime = null;
if (reader["LastSaveTime"] != DBNull.Value)
{
lastSaveTime = Convert.ToDateTime(reader["LastSaveTime"]);
lastSaveTimeLong = lastSaveTime.Value.Ticks;
lastSaveTimeStr = lastSaveTimeLong.ToString();
if (lastSaveTime < StartDate || lastSaveTime > EndDate)
{
MinorProgress += inc;
OnMinorProgressChanged();
OnMinorStatusChanged(String.Format("{0} of records exported from " + form.Name + "...", (MinorProgress / 100).ToString("P0")));
if (isCaseForm)
{
// we want to add the GUID here so related records (e.g. contacts and labs) don't get excluded because
// their case wasn't in the date range.
_caseGuids.Add(recordGuid);
}
continue;
}
}
if (form.Name.Equals(Core.Constants.CONTACT_FORM_NAME, StringComparison.OrdinalIgnoreCase) && _contactGuids != null && !_contactGuids.Contains(recordGuid))
{
continue;
}
if (form.Name.Equals(Core.Constants.LAB_FORM_NAME, StringComparison.OrdinalIgnoreCase) && labGuids != null && !labGuids.Contains(recordGuid))
{
continue;
}
writer.WriteStartElement("Record");
writer.WriteAttributeString("Id", recordGuid);
writer.WriteAttributeString("FKEY", reader["FKEY"] == DBNull.Value ? String.Empty : reader["FKEY"].ToString());
writer.WriteAttributeString("FirstSaveUserId", reader["FirstSaveLogonName"].ToString());
writer.WriteAttributeString("LastSaveUserId", reader["LastSaveLogonName"].ToString());
if (reader["FirstSaveTime"] != DBNull.Value)
{
writer.WriteAttributeString("FirstSaveTime", Convert.ToDateTime(reader["FirstSaveTime"]).Ticks.ToString());
}
else
{
writer.WriteAttributeString("FirstSaveTime", String.Empty);
}
writer.WriteAttributeString("LastSaveTime", lastSaveTimeStr);
writer.WriteAttributeString("RecStatus", reader["RecStatus"].ToString());
foreach (IDataField dataField in form.Fields.DataFields)
{
RenderableField field = dataField as RenderableField;
if (field == null || dataField is UniqueKeyField || fieldsToNull.Contains(field.Name))
{
continue;
}
else
{
if (reader[field.Name] != DBNull.Value && !String.IsNullOrEmpty(reader[field.Name].ToString()))
{
writer.WriteStartElement(field.Name);
WriteFieldValue(writer, reader, field);
writer.WriteEndElement();
}
}
}
writer.WriteEndElement(); // record
ExportInfo.RecordsPackaged[form]++;
MinorProgress += inc;
OnMinorProgressChanged();
//OnMinorStatusChanged(String.Format("{1} ({0}) of records exported from " + form.Name + "...", i.ToString(), (MinorProgress / 100).ToString("P0")));
OnMinorStatusChanged(String.Format("{0} of records exported from " + form.Name + "...", (MinorProgress / 100).ToString("P0")));
//i++;
}
}
writer.WriteEndElement(); // data element
writer.WriteEndElement(); // form element
}
protected virtual void WriteFormPagedData(XmlWriter writer, View form)
{
MinorProgress = 0;
OnMinorProgressChanged();
List <string> fieldsToNull = FieldsToNull[form.Name];
writer.WriteStartElement("Form");
writer.WriteAttributeString("Name", form.Name);
writer.WriteAttributeString("Pages", form.Pages.Count.ToString());
writer.WriteAttributeString("IsRelatedForm", form.IsRelatedView.ToString());
WriteFormMetadata(writer, form);
writer.WriteStartElement("Data");
IDbDriver db = Project.CollectedData.GetDatabase();
using (OleDbConnection conn = new OleDbConnection(db.ConnectionString))
{
conn.Open();
double totalRecords = -1;
using (OleDbCommand command = new OleDbCommand("SELECT COUNT(*) FROM " + form.TableName, conn))
{
totalRecords = Convert.ToDouble(command.ExecuteScalar());
}
double inc = 100 / totalRecords;
//List<string> guids = new List<string>();
HashSet <string> guids = new HashSet <string>();
Query guidQuery = GetFormSelectQuery(form, false);
using (IDataReader guidReader = db.ExecuteReader(guidQuery))
{
int i = 0;
while (guidReader.Read())
{
string globalRecordId = guidReader["GlobalRecordId"].ToString();
//guids[i] = globalRecordId;
guids.Add(globalRecordId);
i++;
}
}
if (form.Name.Equals(Core.Constants.CASE_FORM_NAME, StringComparison.OrdinalIgnoreCase))
{
_caseGuids = guids;
}
HashSet <string> guidsWritten = new HashSet <string>();
foreach (Page page in form.Pages)
{
Query selectQuery = db.CreateQuery("SELECT * FROM " + page.TableName + " p INNER JOIN " + form.TableName + " f ON p.GlobalRecordId = f.GlobalRecordId");
using (OleDbCommand command = new OleDbCommand(selectQuery.SqlStatement, conn))
{
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string globalRecordId = reader["p.GlobalRecordId"].ToString();
if (!guids.Contains(globalRecordId))
{
continue;
}
string lastSaveTimeStr = String.Empty;
long? lastSaveTimeLong = null;
DateTime?lastSaveTime = null;
if (reader["LastSaveTime"] != DBNull.Value)
{
lastSaveTime = Convert.ToDateTime(reader["LastSaveTime"]);
lastSaveTimeLong = lastSaveTime.Value.Ticks;
lastSaveTimeStr = lastSaveTimeLong.ToString();
if (lastSaveTime < StartDate || lastSaveTime > EndDate)
{
MinorProgress += inc;
OnMinorProgressChanged();
OnMinorStatusChanged(String.Format("{0} exported for " + form.Name + "...", (MinorProgress / 100).ToString("P0")));
continue;
}
}
if (!guidsWritten.Contains(globalRecordId))
{
guidsWritten.Add(globalRecordId);
}
writer.WriteStartElement("Record");
writer.WriteAttributeString("Id", globalRecordId);
writer.WriteAttributeString("FKEY", reader["FKEY"] == DBNull.Value ? String.Empty : reader["FKEY"].ToString());
writer.WriteAttributeString("FirstSaveUserId", reader["FirstSaveLogonName"].ToString());
writer.WriteAttributeString("LastSaveUserId", reader["LastSaveLogonName"].ToString());
if (reader["FirstSaveTime"] != DBNull.Value)
{
writer.WriteAttributeString("FirstSaveTime", Convert.ToDateTime(reader["FirstSaveTime"]).Ticks.ToString());
}
else
{
writer.WriteAttributeString("FirstSaveTime", String.Empty);
}
writer.WriteAttributeString("LastSaveTime", lastSaveTimeStr);
writer.WriteAttributeString("RecStatus", reader["RecStatus"].ToString());
foreach (RenderableField field in page.Fields)
{
if (field == null || fieldsToNull.Contains(field.Name) || !(field is IDataField))
{
continue;
}
else
{
if (reader[field.Name] != DBNull.Value && !String.IsNullOrEmpty(reader[field.Name].ToString()))
{
writer.WriteStartElement(field.Name);
//writer.WriteAttributeString("Name", field.Name);
WriteFieldValue(writer, reader, field);
writer.WriteEndElement();
}
}
}
writer.WriteEndElement();
}
}
}
}
ExportInfo.RecordsPackaged[form] = guidsWritten.Count;
}
writer.WriteEndElement(); // data element
writer.WriteEndElement(); // form element
}
private static void UpdateMetaFields(Project project, string countryName = "")
{
IDbDriver db = project.CollectedData.GetDatabase();
// 1 = text
// 17, 18, 19 = ddl's
Query updateQuery = db.CreateQuery("UPDATE [metaFields] SET FieldTypeId = 1 " +
"WHERE (FieldTypeId = 17 OR FieldTypeId = 18 OR FieldTypeId = 19) " +
"AND (PromptText = @PromptTextDistrict OR PromptText = @PromptTextSC)");
updateQuery.Parameters.Add(new QueryParameter("@PromptTextDistrict", DbType.String, "District:"));
updateQuery.Parameters.Add(new QueryParameter("@PromptTextSC", DbType.String, "Sub-County:"));
int rows = db.ExecuteNonQuery(updateQuery);
if (rows == 0)
{
// shouldn't get here
}
#region Wipe out districts
string querySyntax = "DELETE * FROM [codeDistrictSubCountyList]";
if (db.ToString().ToLower().Contains("sql"))
{
querySyntax = "DELETE FROM [codeDistrictSubCountyList]";
}
Query deleteQuery = db.CreateQuery(querySyntax);
db.ExecuteNonQuery(deleteQuery);
#endregion // Wipe out districts
updateQuery = db.CreateQuery("UPDATE [metaFields] " +
"SET PromptText = 'Viral Hemorrhagic Fever Outbreak Laboratory Diagnostic Specimens and Results Form' " +
"WHERE FieldId = 230 OR FieldId = 590");
rows = db.ExecuteNonQuery(updateQuery);
updateQuery = db.CreateQuery("UPDATE [metaFields] SET [ControlLeftPositionPercentage] = @CLPP WHERE [Name] = @Name");
updateQuery.Parameters.Add(new QueryParameter("@CLPP", DbType.Double, 0.01));
updateQuery.Parameters.Add(new QueryParameter("@Name", DbType.String, "CRFTitle"));
rows = db.ExecuteNonQuery(updateQuery);
updateQuery = db.CreateQuery("UPDATE [metaFields] " +
"SET PromptText = 'Viral Hemorrhagic Fever Contact Information Entry Form' " +
"WHERE FieldId = 345");
rows = db.ExecuteNonQuery(updateQuery);
updateQuery = db.CreateQuery("UPDATE [metaFields] " +
"SET PromptText = @CountryName " +
"WHERE FieldId = 4");
updateQuery.Parameters.Add(new QueryParameter("@CountryName", DbType.String, countryName + " Viral Hemorrhagic Fever Case Investigation Form"));
rows = db.ExecuteNonQuery(updateQuery);
if (rows == 0)
{
// shouldn't get here
}
updateQuery = db.CreateQuery("UPDATE metaPages " +
"SET BackgroundId = 0");
rows = db.ExecuteNonQuery(updateQuery);
if (rows == 0)
{
// shouldn't get here
}
}
/// <summary>
/// Sets the appropriate properties for a given field, based on the specified column conversion information
/// </summary>
/// <param name="field">The Epi Info 7 field</param>
/// <param name="cci">The column conversion information</param>
private void SetFieldProperties(Field field, ColumnConversionInfo cci)
{
if (cci.Prompt == null)
{
cci.Prompt = cci.DestinationColumnName;
}
switch (field.FieldType)
{
case MetaFieldType.Checkbox:
CheckBoxField checkboxField = (CheckBoxField)field;
checkboxField.TabIndex = cci.TabIndex;
checkboxField.IsReadOnly = cci.IsReadOnly;
checkboxField.IsRequired = cci.IsRequired;
checkboxField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.YesNo:
YesNoField yesNoField = (YesNoField)field;
yesNoField.TabIndex = cci.TabIndex;
yesNoField.IsReadOnly = cci.IsReadOnly;
yesNoField.IsRequired = cci.IsRequired;
yesNoField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.Text:
SingleLineTextField textField = (SingleLineTextField)field;
textField.TabIndex = cci.TabIndex;
textField.IsReadOnly = cci.IsReadOnly;
textField.IsRequired = cci.IsRequired;
textField.ShouldRepeatLast = cci.IsRepeatLast;
if (cci.UpperBound is int)
{
textField.MaxLength = (int)cci.UpperBound;
}
break;
case MetaFieldType.Multiline:
MultilineTextField multilineTextField = (MultilineTextField)field;
multilineTextField.TabIndex = cci.TabIndex;
multilineTextField.IsReadOnly = cci.IsReadOnly;
multilineTextField.IsRequired = cci.IsRequired;
multilineTextField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.Date:
DateField dateField = (DateField)field;
dateField.TabIndex = cci.TabIndex;
dateField.IsReadOnly = cci.IsReadOnly;
dateField.IsRequired = cci.IsRequired;
dateField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.DateTime:
DateTimeField dateTimeField = (DateTimeField)field;
dateTimeField.TabIndex = cci.TabIndex;
dateTimeField.IsReadOnly = cci.IsReadOnly;
dateTimeField.IsRequired = cci.IsRequired;
dateTimeField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.Time:
TimeField timeField = (TimeField)field;
timeField.TabIndex = cci.TabIndex;
timeField.IsReadOnly = cci.IsReadOnly;
timeField.IsRequired = cci.IsRequired;
timeField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.Number:
NumberField numberField = (NumberField)field;
numberField.TabIndex = cci.TabIndex;
numberField.IsReadOnly = cci.IsReadOnly;
numberField.IsRequired = cci.IsRequired;
numberField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.LegalValues:
DDLFieldOfLegalValues legalValuesField = (DDLFieldOfLegalValues)field;
legalValuesField.TabIndex = cci.TabIndex;
legalValuesField.IsReadOnly = cci.IsReadOnly;
legalValuesField.IsRequired = cci.IsRequired;
legalValuesField.ShouldRepeatLast = cci.IsRepeatLast;
if (string.IsNullOrEmpty(cci.ListSourceTableName))
{
DataTable dt = new DataTable(cci.SourceColumnName);
dt.Columns.Add(new DataColumn(cci.SourceColumnName, typeof(string)));
// table is blank, so assume user wants to use a SELECT DISTINCT as the value source
Query selectDistinctQuery = sourceDriver.CreateQuery("SELECT DISTINCT [" + cci.SourceColumnName + "] FROM [" + tableName + "]");
IDataReader distinctReader = sourceDriver.ExecuteReader(selectDistinctQuery);
while (distinctReader.Read())
{
dt.Rows.Add(distinctReader[0].ToString());
}
cci.ListSourceTable = dt;
cci.ListSourceTableName = cci.SourceColumnName;
cci.ListSourceTextColumnName = cci.SourceColumnName;
IDbDriver db = project.CollectedData.GetDatabase();
if (!db.TableExists(cci.ListSourceTableName))
{
project.CreateCodeTable(cci.ListSourceTableName, cci.ListSourceTextColumnName);
project.SaveCodeTableData(cci.ListSourceTable, cci.ListSourceTableName, cci.ListSourceTextColumnName);
}
legalValuesField.SourceTableName = cci.ListSourceTableName;
legalValuesField.TextColumnName = cci.ListSourceTextColumnName;
legalValuesField.CodeColumnName = cci.ListSourceTextColumnName;
}
else
{
IDbDriver db = project.CollectedData.GetDatabase();
if (!db.TableExists(cci.ListSourceTableName))
{
project.CreateCodeTable(cci.ListSourceTableName, cci.ListSourceTextColumnName);
string[] columns = new string[1];
columns[0] = cci.ListSourceTextColumnName;
project.InsertCodeTableData(cci.ListSourceTable, cci.ListSourceTableName, columns);
}
legalValuesField.SourceTableName = cci.ListSourceTableName;
legalValuesField.TextColumnName = cci.ListSourceTextColumnName;
legalValuesField.CodeColumnName = cci.ListSourceTextColumnName;
}
break;
default:
throw new ApplicationException("Invalid field type");
//break;
}
double ControlHeightPercentage = 0.0;
double ControlWidthPercentage = 0.0;
if (field is FieldWithSeparatePrompt)
{
FieldWithSeparatePrompt fieldWithPrompt;
fieldWithPrompt = (FieldWithSeparatePrompt)field;
fieldWithPrompt.PromptText = cci.Prompt;
fieldWithPrompt.PromptFont = cci.PromptFont;
fieldWithPrompt.ControlFont = cci.ControlFont;
fieldWithPrompt.PromptLeftPositionPercentage = cci.ControlLeftPosition / 100;
fieldWithPrompt.PromptTopPositionPercentage = cci.ControlTopPosition / 100;
fieldWithPrompt.Name = cci.DestinationColumnName;
fieldWithPrompt.ControlHeightPercentage = ControlHeightPercentage / 100;
fieldWithPrompt.ControlWidthPercentage = ControlWidthPercentage / 100;
fieldWithPrompt.ControlTopPositionPercentage = cci.ControlTopPosition / 100;
fieldWithPrompt.ControlLeftPositionPercentage = (cci.ControlLeftPosition / 100) + 0.090702947845805;
fieldWithPrompt.UpdatePromptPosition();
fieldWithPrompt.UpdateControlPosition();
}
else
{
FieldWithoutSeparatePrompt fieldWithoutPrompt;
fieldWithoutPrompt = (FieldWithoutSeparatePrompt)field;
fieldWithoutPrompt.PromptText = cci.Prompt;
fieldWithoutPrompt.PromptFont = cci.PromptFont;
fieldWithoutPrompt.Name = cci.DestinationColumnName;
fieldWithoutPrompt.ControlHeightPercentage = ControlHeightPercentage / 100;
fieldWithoutPrompt.ControlWidthPercentage = ControlWidthPercentage / 100;
fieldWithoutPrompt.ControlTopPositionPercentage = cci.ControlTopPosition / 100;
fieldWithoutPrompt.ControlLeftPositionPercentage = (cci.ControlLeftPosition / 100) + 0.090702947845805;
fieldWithoutPrompt.UpdateControlPosition();
}
}
protected virtual void WriteFollowUpsData(XmlWriter writer)
{
MinorProgress = 0;
OnMinorProgressChanged();
string selectQueryText = "SELECT * FROM metaHistory";
IDbDriver db = Project.CollectedData.GetDatabase();
CultureInfo format = CultureInfo.InvariantCulture;
if (!db.TableExists("metaHistory"))
{
return;
}
writer.WriteStartElement("ContactFollowUps");
Query selectQuery = db.CreateQuery(selectQueryText);
double totalRecords = Convert.ToDouble(db.ExecuteScalar(db.CreateQuery("SELECT COUNT(*) FROM metaHistory")));
double inc = 100 / totalRecords;
bool filter = _contactGuids != null;
string contactFormId = Project.Views[Core.Constants.CONTACT_FORM_NAME].Id.ToString();
using (IDataReader reader = db.ExecuteReader(selectQuery))
{
while (reader.Read())
{
string contactGuid = reader["ContactGUID"].ToString();
if (filter && !_contactGuids.Contains(contactGuid))
{
continue;
}
writer.WriteStartElement("ContactFollowUp");
#region Followup Fields
writer.WriteStartElement("ContactGUID");
writer.WriteString(contactGuid);
writer.WriteEndElement();
writer.WriteStartElement("FollowUpDate");
writer.WriteString(Convert.ToDateTime(reader["FollowUpDate"]).ToString(format.DateTimeFormat.ShortDatePattern));
writer.WriteEndElement();
if (!String.IsNullOrEmpty(reader["StatusOnDate"].ToString()))
{
writer.WriteStartElement("StatusOnDate");
writer.WriteString(reader["StatusOnDate"].ToString());
writer.WriteEndElement();
}
if (!String.IsNullOrEmpty(reader["Note"].ToString()))
{
writer.WriteStartElement("Note");
writer.WriteString(reader["Note"].ToString());
writer.WriteEndElement();
}
if (!String.IsNullOrEmpty(reader["Temp1"].ToString()))
{
writer.WriteStartElement("Temp1");
writer.WriteString(reader["Temp1"] == DBNull.Value ? String.Empty : Convert.ToDouble(reader["Temp1"]).ToString(System.Globalization.CultureInfo.InvariantCulture));
writer.WriteEndElement();
}
if (!String.IsNullOrEmpty(reader["Temp2"].ToString()))
{
writer.WriteStartElement("Temp2");
writer.WriteString(reader["Temp2"] == DBNull.Value ? String.Empty : Convert.ToDouble(reader["Temp2"]).ToString(System.Globalization.CultureInfo.InvariantCulture));
writer.WriteEndElement();
}
#endregion // Followup Fields
writer.WriteEndElement();
MinorProgress += inc;
OnMinorProgressChanged();
OnMinorStatusChanged(String.Format("{0} of contact tracing records exported...", (MinorProgress / 100).ToString("P0")));
}
}
writer.WriteEndElement();
}
void computeWorker_DoWork(object sender, DoWorkEventArgs e)
{
Result result = new Result();
EpiDataHelper DataHelper = e.Argument as EpiDataHelper;
if (DataHelper != null && DataHelper.Project != null && DataHelper.Project.CollectedData != null)
{
IDbDriver db = DataHelper.Project.CollectedData.GetDatabase();
int total = (from caseVM in DataHelper.CaseCollection
where caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
string format = "P1";
int count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.ConfirmedAcute && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.ConfirmedAcuteCount = count.ToString();
result.ConfirmedAcutePercent = ((double)count / (double)total).ToString(format);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.ConfirmedConvalescent && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.ConfirmedConvalescentCount = count.ToString();
result.ConfirmedConvalescentPercent = ((double)count / (double)total).ToString(format);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.NotCase && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.NegativeCount = count.ToString();
result.NegativePercent = ((double)count / (double)total).ToString(format);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.Indeterminate && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.IndeterminateCount = count.ToString();
result.IndeterminatePercent = ((double)count / (double)total).ToString(format);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.NeedsFollowUpSample && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.NeedsFollowUpCount = count.ToString();
result.NeedsFollowUpPercent = ((double)count / (double)total).ToString(format);
Epi.Fields.RenderableField finalLabClassField = DataHelper.CaseForm.Fields["FinalLabClass"] as Epi.Fields.RenderableField;
Epi.Fields.RenderableField epiCaseDefField = DataHelper.CaseForm.Fields["EpiCaseDef"] as Epi.Fields.RenderableField;
if (finalLabClassField != null && epiCaseDefField != null && finalLabClassField.Page != null && epiCaseDefField.Page != null)
{
string finalLabClassTableName = finalLabClassField.Page.TableName;
string epiCaseClassTableName = epiCaseDefField.Page.TableName;
string queryText = "";
if (db.ToString().ToLower().Contains("sql"))
{
queryText = "select count(*) from " + finalLabClassTableName + " AS crf INNER JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((crf.FinalLabClass = '' OR crf.FinalLabClass is null) AND (crfEpiCaseClass.EpiCaseDef <> '4'))";
}
else
{
queryText = "select count(*) from ((" + finalLabClassTableName + " AS crf) INNER JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId) INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((crf.FinalLabClass = '' OR crf.FinalLabClass is null) AND (crfEpiCaseClass.EpiCaseDef <> '4'))";
}
Query selectQuery = db.CreateQuery(queryText);
count = (int)db.ExecuteScalar(selectQuery);
if (db.ToString().ToLower().Contains("sql"))
{
queryText = "select crfEpiCaseClass.ID from " + finalLabClassTableName + " AS crf INNER JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((crf.FinalLabClass = '' OR crf.FinalLabClass is null) AND (crfEpiCaseClass.EpiCaseDef <> '4'))";
}
else
{
queryText = "select crfEpiCaseClass.ID from ((" + finalLabClassTableName + " AS crf) INNER JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId) INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((crf.FinalLabClass = '' OR crf.FinalLabClass is null) AND (crfEpiCaseClass.EpiCaseDef <> '4'))";
}
selectQuery = db.CreateQuery(queryText);
DataTable dt = db.Select(selectQuery);
WordBuilder wb = new WordBuilder(",");
foreach (DataRow row in dt.Rows)
{
wb.Add(row["ID"].ToString());
}
result.PendingIDs = wb.ToString();
result.PendingCount = count.ToString();
result.PendingPercent = ((double)count / (double)total).ToString(format);
if (db.ToString().ToLower().Contains("sql"))
{
queryText = "select count(*) from CaseInformationForm AS crf LEFT JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId LEFT JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((lrf.GlobalRecordId = '' OR lrf.GlobalRecordId is null) AND (crfEpiCaseClass.EpiCaseDef <> '4') AND crf.RecStatus = 1)";
}
else
{
queryText = "select count(*) from ((CaseInformationForm AS crf) LEFT JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId) LEFT JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((lrf.GlobalRecordId = '' OR lrf.GlobalRecordId is null) AND (crfEpiCaseClass.EpiCaseDef <> '4') AND crf.RecStatus = 1)";
}
selectQuery = db.CreateQuery(queryText);
count = (int)db.ExecuteScalar(selectQuery);
result.NotSampledCount = count.ToString();
result.NotSampledPercent = ((double)count / (double)total).ToString(format);
e.Result = result;
}
else
{
throw new InvalidOperationException("FinalLabClass and EpiCaseDef must both be non-null fields in computeWorker_doWork in LabClassAllPatients.xaml.cs");
}
}
}