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
}
});
}
private void btnNewLinkFrom_Click(object sender, EventArgs e)
{
if (enterMainForm.View.CurrentRecordId > 0)
{
View selectedView;
Epi.Windows.Enter.Dialogs.FindRecords findrecords;
if (enterMainForm.View.Project.Views.Count > 1)
{
Epi.Enter.Dialogs.WithinProjectViewSelectionDialog viewSelection = new Epi.Enter.Dialogs.WithinProjectViewSelectionDialog(enterMainForm, enterMainForm.View.Project);
if (viewSelection.ShowDialog() == DialogResult.OK)
{
selectedView = currentView.Project.GetViewById(viewSelection.ViewId);
}
else
{
return;
}
}
else
{
selectedView = currentView;
}
findrecords = new Epi.Windows.Enter.Dialogs.FindRecords(selectedView, enterMainForm, currentView);
DialogResult result = findrecords.ShowDialog();
this.BringToFront();
if (result == DialogResult.OK)
{
Query query = db.CreateQuery("Insert into metaLinks (FromRecordGuid, ToRecordGuid, FromViewId, ToViewId) values (@FromRecordGuid, @ToRecordGuid, @FromViewId, @ToViewId)");
query.Parameters.Add(new QueryParameter("@FromRecordGuid", DbType.StringFixedLength, findrecords.GlobalRecordId.ToString("D").ToLowerInvariant()));
query.Parameters.Add(new QueryParameter("@ToRecordGuid", DbType.StringFixedLength, enterMainForm.View.CurrentGlobalRecordId.ToLowerInvariant()));
query.Parameters.Add(new QueryParameter("@FromViewId", DbType.Int32, selectedView.Id));
query.Parameters.Add(new QueryParameter("@ToViewId", DbType.Int32, enterMainForm.View.Id));
db.ExecuteNonQuery(query);
FillFromNodes();
}
}
else
{
MessageBox.Show(SharedStrings.LINKED_RECS_FIRST_SAVE, SharedStrings.LINKED_RECS_CANT_CREATE, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
///// <summary>
/////
///// </summary>
///// <param name="tableName"></param>
///// <returns></returns>
//public override List<string> GetTableColumnNames(string viewName)
//{
// try
// {
// Query query = db.CreateQuery("select [Name] from " + viewName +
// " where [Type] in ('TEXT', 'TEXTBOX','UPPERCASE','MULTILINE', 'DATE','DATETIME','TIME','PHONENUMBER','NUMBER','COMBO','GROUP','OPTFRAME','AUTOIDNUM','CHECKBOX','IMAGE','YES/NO') ") ;
// DataTable columnsTable = db.Select(query);
// List<string> columnsList = new List<string>();
// foreach (DataRow row in columnsTable.Rows)
// {
// columnsList.Add(row["Name"].ToString());
// }
// return columnsList;
// }
// finally
// {
// }
//}
/// <summary>
/// Inserts a pgm into metaPrograms
/// </summary>
/// <param name="name"></param>
/// <param name="content"></param>
/// <param name="comment"></param>
/// <param name="author"></param>
/// <returns></returns>
//public override void InsertPgm(string name, string content, string comment, string author )
public void InsertPgm(string name, string content, string comment, string author)
{
try
{
Query insertQuery = db.CreateQuery("insert into Programs([Name], [Content], [Comment],[DateCreated],[DateModified], [Author]) " +
"values (@Name, @Content, @Comment,@DateCreated,@DateModified, @Author)");
insertQuery.Parameters.Add(new QueryParameter("@Name", DbType.String, name));
insertQuery.Parameters.Add(new QueryParameter("@Content", DbType.String, content));
insertQuery.Parameters.Add(new QueryParameter("@Comment", DbType.String, comment));
insertQuery.Parameters.Add(new QueryParameter("@DateCreated", DbType.String, System.DateTime.Now.ToShortDateString()));
insertQuery.Parameters.Add(new QueryParameter("@DateModified", DbType.String, System.DateTime.Now.ToShortDateString()));
insertQuery.Parameters.Add(new QueryParameter("@Author", DbType.String, author));
db.ExecuteNonQuery(insertQuery);
}
catch (Exception ex)
{
throw new System.ApplicationException("Could not insert pgm", ex);
}
finally
{
}
}
/// <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);
}
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>
/// 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);
}
private void LabResultActionsRowControl_DeleteRequested(object sender, EventArgs e)
{
MessageBoxResult result = MessageBox.Show("Are you sure you want to delete this lab record?", "Confirm deletion", MessageBoxButton.YesNo, MessageBoxImage.Question);
if (result == MessageBoxResult.Yes)
{
Dialogs.AuthCodeDialog authDialog = new Dialogs.AuthCodeDialog(ContactTracing.Core.Constants.AUTH_CODE);
System.Windows.Forms.DialogResult authResult = authDialog.ShowDialog();
if (authResult == System.Windows.Forms.DialogResult.OK)
{
if (authDialog.IsAuthorized)
{
try
{
if (this.DataContext != null)
{
EpiDataHelper DataHelper = this.DataContext as EpiDataHelper;
if (DataHelper != null)
{
LabResultViewModel r = dg.SelectedItem as LabResultViewModel;
if (r != null)
{
string guid = r.RecordId;
IDbDriver db = DataHelper.Project.CollectedData.GetDatabase();
#region Soft deletion code (Unused)
/* SOFT DELETE */
//int rows = 0;
//string querySyntax = "UPDATE [" + DataHelper.LabForm.TableName + "] SET RecStatus = 0 WHERE [GlobalRecordId] = @GlobalRecordId";
//Query labUpdateQuery = db.CreateQuery(querySyntax);
//labUpdateQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", System.Data.DbType.String, guid));
//rows = db.ExecuteNonQuery(labUpdateQuery);
//CaseViewModel c = DataHelper.GetCaseVM(r.CaseRecordGuid);
//if (rows == 1)
//{
// Core.DbLogger.Log(String.Format(
// "Soft-deleted lab : Case ID = {0}, Case EpiCaseDef = {1}, FLSID = {2}, Lab GUID = {3}",
// c.ID, c.EpiCaseDef, r.FieldLabSpecimenID, r.RecordId));
//}
//else if (rows == 0)
//{
// Core.DbLogger.Log(String.Format(
// "Lab soft-deletion attempted but no lab record found in database : GUID = {0}",
// guid));
//}
//else if (rows > 1)
//{
// Core.DbLogger.Log(String.Format(
// "Lab soft-deletion affected {0} records for GUID {1}. Duplicate labs may be present.",
// rows.ToString(), guid));
//}
#endregion Soft deletion code
/* HARD DELETE */
using (IDbTransaction transaction = db.OpenTransaction())
{
Query deleteQuery = db.CreateQuery("DELETE FROM " + DataHelper.LabForm.TableName + " WHERE GlobalRecordId = @GlobalRecordId");
deleteQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", System.Data.DbType.String, guid));
int rows = db.ExecuteNonQuery(deleteQuery, transaction);
foreach (Epi.Page page in DataHelper.LabForm.Pages)
{
deleteQuery = db.CreateQuery("DELETE FROM " + page.TableName + " WHERE GlobalRecordId = @GlobalRecordId");
deleteQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", System.Data.DbType.String, guid));
db.ExecuteNonQuery(deleteQuery, transaction);
}
try
{
transaction.Commit();
}
catch (Exception ex0)
{
ContactTracing.Core.DbLogger.Log("Lab record deletion failed on commit. Transaction rolled back. Exception: " + ex0.Message);
try
{
transaction.Rollback();
}
catch (Exception ex1)
{
ContactTracing.Core.DbLogger.Log("Lab record deletion failed on commit rollback. Transaction rolled back. Exception: " + ex1.Message);
}
}
CaseViewModel c = DataHelper.GetCaseVM(r.CaseRecordGuid);
DataHelper.PopulateLabRecordsForCase.Execute(c);
db.CloseTransaction(transaction);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(String.Format("An exception occurred while trying to delete a lab record. Message: {0}", ex.Message));//. Case ID: {0}. Please give this message to the application developer.\n{1}", caseVM.ID, ex.Message));
ContactTracing.Core.DbLogger.Log("Lab record deletion failed. Exception: " + ex.Message);
}
}
}
}
}
public void CreateCaseSyncFileStart(ContactTracing.ImportExport.SyncFileFilters filters /*string fileName, bool includeCases, bool includeCaseExposures, bool includeContacts, Epi.ImportExport.Filters.RowFilters filters, bool deIdentifyData, Epi.RecordProcessingScope recordProcessingScope*/)
{
if (IsWaitingOnOtherClients)
{
return;
}
if (String.IsNullOrEmpty(SyncFilePath.Trim()))
{
throw new ArgumentNullException("fileName");
}
bool success = true;
IsDataSyncing = true;
RecordsExported = String.Empty;
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
#region Remove extraneous data
//int rows = 0;
MinorSyncStatus = "Deleting extraneous page table rows...";
IDbDriver db = _project.CollectedData.GetDatabase();
if (db.ToString().ToLower().Contains("sql"))
{
using (IDbTransaction transaction = db.OpenTransaction())
{
foreach (View form in _project.Views)
{
Query formDeleteDuplicateQuery = Database.CreateQuery("WITH cte AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY GlobalRecordId ORDER BY GlobalRecordId) 'RowRank' FROM " + form.TableName + ") " +
"DELETE FROM cte " +
"WHERE RowRank > 1");
Database.ExecuteNonQuery(formDeleteDuplicateQuery, transaction);
foreach (Page page in form.Pages)
{
Query deleteQuery = db.CreateQuery("DELETE FROM " + form.Name + " WHERE GlobalRecordId NOT IN (SELECT GlobalRecordId FROM " + page.TableName + ")");
db.ExecuteNonQuery(deleteQuery, transaction);
//if (rows > 0)
//{
// // report ??
//}
Query pageDeleteQuery = db.CreateQuery("DELETE FROM " + page.TableName + " WHERE GlobalRecordId NOT IN (SELECT GlobalRecordId FROM " + form.Name + ")");
db.ExecuteNonQuery(deleteQuery, transaction);
//if (rows > 0)
//{
// // report ??
//}
Query pageDeleteDuplicateQuery = Database.CreateQuery("WITH cte AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY GlobalRecordId ORDER BY GlobalRecordId) 'RowRank' FROM " + page.TableName + ") " +
"DELETE FROM cte " +
"WHERE RowRank > 1");
Database.ExecuteNonQuery(pageDeleteDuplicateQuery, transaction);
}
}
Query linksDeleteQuery = db.CreateQuery("DELETE FROM metaLinks WHERE ToViewId = @ToViewId AND ToRecordGuid NOT IN (SELECT GlobalRecordId FROM " + ContactForm.TableName + ")");
linksDeleteQuery.Parameters.Add(new QueryParameter("@ToViewId", DbType.Int32, ContactFormId));
db.ExecuteNonQuery(linksDeleteQuery, transaction);
if (db.TableExists("metaHistory"))
{
Query historyDeleteQuery = db.CreateQuery("DELETE FROM metaHistory WHERE ContactGUID NOT IN (SELECT GlobalRecordId FROM " + ContactForm.TableName + ")");
db.ExecuteNonQuery(historyDeleteQuery, transaction);
}
try
{
transaction.Commit();
}
catch (Exception ex0)
{
Epi.Logger.Log(String.Format(DateTime.Now + ": " + "DB cleanup exception Type: {0}", ex0.GetType()));
Epi.Logger.Log(String.Format(DateTime.Now + ": " + "DB cleanup exception Message: {0}", ex0.Message));
Epi.Logger.Log(String.Format(DateTime.Now + ": " + "DB cleanup rollback started..."));
DbLogger.Log("Database cleanup failed on commit. Exception: " + ex0.Message);
try
{
transaction.Rollback();
Epi.Logger.Log(String.Format(DateTime.Now + ": " + "DB cleanup rollback was successful."));
}
catch (Exception ex1)
{
DbLogger.Log("Database cleanup rollback failed. Exception: " + ex1.Message);
}
}
db.CloseTransaction(transaction);
}
}
#endregion // Remove extraneous data
RecordsExported = String.Empty;
IsDataSyncing = true;
IsShowingExportProgress = true;
SendMessageForAwaitAll();
DbLogger.Log(String.Format("Initiated process 'export sync file' - IncludeCasesAndContacts = {0}", IncludeCasesAndContacts));
Task.Factory.StartNew(
() =>
{
success = CreateCaseSyncFile(filters /*fileName, includeCases, includeCaseExposures, includeContacts, filters, deIdentifyData, recordProcessingScope*/);
},
System.Threading.CancellationToken.None, TaskCreationOptions.LongRunning, TaskScheduler.Default).ContinueWith(
delegate
{
SendMessageForUnAwaitAll();
TaskbarProgressState = System.Windows.Shell.TaskbarItemProgressState.None;
MajorProgressValue = 0;
IsDataSyncing = false;
MinorProgressValue = 0;
stopwatch.Stop();
MinorSyncStatus = String.Empty;
if (success)
{
HasExportErrors = false;
MajorSyncStatus = "Finished exporting data to sync file.";
TimeElapsed = "Elapsed time: " + stopwatch.Elapsed.TotalMinutes.ToString("F1") + " minutes.";
DbLogger.Log(String.Format("Completed process 'export sync file' successfully - elapsed time = {0} ms", stopwatch.Elapsed.TotalMilliseconds.ToString()));
}
else
{
HasExportErrors = true;
MajorSyncStatus = "There was a problem exporting the data.";
DbLogger.Log(String.Format("Completed process 'export sync file' with errors"));
}
CommandManager.InvalidateRequerySuggested();
}, TaskScheduler.FromCurrentSynchronizationContext());
}
/// <summary>
/// Executes a SQL query based on a valid SMS message
/// </summary>
/// <param name="updateType">The type of update</param>
/// <param name="message">The SMS message</param>
/// <returns>bool; whether the execution was successful</returns>
public bool ExecuteSmsUpdate(ShortMessage message)
{
#region Input Validation
if (message == null)
{
throw new ArgumentNullException("Message object cannot be null");
}
if (String.IsNullOrEmpty(message.Message))
{
throw new InvalidOperationException("SMS message cannot be empty");
}
string[] parts = message.Message.Split(' ');
if (parts.Length != 3)
{
throw new InvalidOperationException("SMS message must contain three parts.");
}
int updateType;
bool success = int.TryParse(parts[0], out updateType);
if (updateType < 1 || updateType > 3)
{
throw new InvalidOperationException("SMS message update type is invalid. Valid values are 1, 2, and 3");
}
#endregion // Input Validation
bool operationWasSuccessful = true;
int id;
success = int.TryParse(parts[1], out id);
if (success)
{
if (updateType == 1)
{
#region Case Data Update
// do nothing right now
operationWasSuccessful = false;
#endregion Case Data Update
}
else if (updateType == 2)
{
#region Daily Follow-up Update
int status;
success = int.TryParse(parts[2], out status);
if (status >= 0 && status <= 7)
{
// get the database
IDbDriver db = this.CollectedData.GetDatabase();
// get the global record ID for this contact
Query selectQuery = db.CreateQuery("SELECT GlobalRecordId FROM ContactEntryForm WHERE UniqueKey = @Id");
selectQuery.Parameters.Add(new QueryParameter("@Id", System.Data.DbType.Int32, id));
DataTable dt = db.Select(selectQuery);
string guid = dt.Rows[0][0].ToString();
// calculate the window so we know which day column to update
selectQuery = db.CreateQuery("SELECT LastContactDate FROM metaLinks WHERE " +
"ToRecordGuid = @ToRecordGuid AND " +
"FromViewId = @FromViewId AND " +
"ToViewId = @ToViewId");
selectQuery.Parameters.Add(new QueryParameter("@ToRecordGuid", System.Data.DbType.String, guid));
selectQuery.Parameters.Add(new QueryParameter("@FromViewId", System.Data.DbType.Int32, CaseFormId));
selectQuery.Parameters.Add(new QueryParameter("@ToViewId", System.Data.DbType.Int32, ContactFormId));
dt = db.Select(selectQuery);
DateTime lastContactDate = (DateTime)(dt.Rows[0][0]);
DateTime today = DateTime.Today;
TimeSpan ts = today - lastContactDate;
int day = (int)ts.TotalDays; // this should never be a decimal since all dates stored should not have time componenets
// update the right row in metaLinks
Query updateQuery = db.CreateQuery("UPDATE metaLinks SET Day" + day.ToString() + " = @Status WHERE " +
"ToRecordGuid = @ToRecordGuid AND " +
"FromViewId = @FromViewId AND " +
"ToViewId = @ToViewId");
updateQuery.Parameters.Add(new QueryParameter("@Status", DbType.Byte, status));
updateQuery.Parameters.Add(new QueryParameter("@ToRecordGuid", System.Data.DbType.String, guid));
updateQuery.Parameters.Add(new QueryParameter("@FromViewId", System.Data.DbType.Int32, CaseFormId));
updateQuery.Parameters.Add(new QueryParameter("@ToViewId", System.Data.DbType.Int32, ContactFormId));
int records = db.ExecuteNonQuery(updateQuery);
if (records == 1)
{
operationWasSuccessful = true;
}
else
{
operationWasSuccessful = false;
}
// add a changeset message
if (db.ToString().ToLower().Contains("sql"))
{
System.Guid changesetGuid = System.Guid.NewGuid();
string changesetGuidString = changesetGuid.ToString();
DateTime now = DateTime.Now;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(db.ConnectionString + ";Connection Timeout=10"))
{
conn.Open();
System.Data.SqlClient.SqlCommand insertCommand = new System.Data.SqlClient.SqlCommand("INSERT INTO Changesets (ChangesetID, UpdateType, UserID, MACADDR, Description, DestinationRecordID, CheckinDate) VALUES (" +
"@ChangesetID, @UpdateType, @UserID, @MACADDR, @Description, @DestinationRecordID, @CheckinDate)", conn);
insertCommand.Parameters.Add("@ChangesetID", SqlDbType.NVarChar).Value = changesetGuidString;
insertCommand.Parameters.Add("@UpdateType", SqlDbType.Int).Value = 15;
insertCommand.Parameters.Add("@UserID", SqlDbType.NVarChar).Value = "SMS server";
insertCommand.Parameters.Add("@MACADDR", SqlDbType.NVarChar).Value = "SMS";
insertCommand.Parameters.Add("@Description", SqlDbType.NVarChar).Value = "SMS contact status update";
insertCommand.Parameters.Add("@DestinationRecordID", SqlDbType.NVarChar).Value = guid;
insertCommand.Parameters.Add("@CheckinDate", SqlDbType.DateTime2).Value = now;
records = insertCommand.ExecuteNonQuery();
if (records == 1)
{
success = true;
}
}
}
}
#endregion // Daily Follow-up Update
}
else if (updateType == 3)
{
#region Lab Data Update
// do nothing right now
operationWasSuccessful = false;
#endregion Lab Data Update
}
}
return(operationWasSuccessful);
}
/// <summary>
/// Creates a new blank row for a given form's base table and all of its page tables.
/// </summary>
/// <param name="form">The form where the row should be added.</param>
/// <param name="guid">The Guid value to use for the row.</param>
/// <param name="keyValues">The key values to use for custom matching</param>
/// <param name="fkey">The foreign key for the row.</param>
/// <param name="firstSaveId">The user ID of the first person that saved this record.</param>
/// <param name="firstSaveTime">The time when the record was first saved.</param>
/// <param name="lastSaveId">The user ID of the last person that saved this record.</param>
/// <param name="lastSaveTime">The time when the record was last saved.</param>
protected virtual void CreateNewBlankRow(View form, string guid, Dictionary <Field, object> keyValues = null, string fkey = "", string firstSaveId = "", string lastSaveId = "", DateTime?firstSaveTime = null, DateTime?lastSaveTime = null)
{
#region Input Validation
if (string.IsNullOrEmpty(guid))
{
throw new ArgumentNullException("guid");
}
if (form == null)
{
throw new ArgumentNullException("form");
}
#endregion // Input Validation
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
IDbDriver db = DestinationProject.CollectedData.GetDatabase();
StringBuilder sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(db.InsertInEscape(form.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.SPACE);
WordBuilder fields = new WordBuilder(",");
fields.Append("[GlobalRecordId]");
if (!string.IsNullOrEmpty(fkey))
{
fields.Append("[FKEY]");
}
if (!string.IsNullOrEmpty(firstSaveId))
{
fields.Append("[FirstSaveLogonName]");
}
if (!string.IsNullOrEmpty(lastSaveId))
{
fields.Append("[LastSaveLogonName]");
}
if (firstSaveTime.HasValue)
{
fields.Append("[FirstSaveTime]");
}
if (lastSaveTime.HasValue)
{
fields.Append("[LastSaveTime]");
}
sb.Append("(" + fields.ToString() + ")");
sb.Append(" values (");
List <QueryParameter> parameters = new List <QueryParameter>();
WordBuilder values = new WordBuilder(",");
values.Append("'" + guid + "'");
if (!string.IsNullOrEmpty(fkey))
{
values.Append("@FKEY");
parameters.Add(new QueryParameter("@FKEY", DbType.String, fkey));
}
if (!string.IsNullOrEmpty(firstSaveId))
{
values.Append("@FirstSaveLogonName");
parameters.Add(new QueryParameter("@FirstSaveLogonName", DbType.String, firstSaveId));
}
if (!string.IsNullOrEmpty(lastSaveId))
{
values.Append("@LastSaveLogonName");
parameters.Add(new QueryParameter("@LastSaveLogonName", DbType.String, lastSaveId));
}
if (firstSaveTime.HasValue)
{
values.Append("@FirstSaveTime");
parameters.Add(new QueryParameter("@FirstSaveTime", DbType.DateTime, firstSaveTime));
}
if (lastSaveTime.HasValue)
{
values.Append("@LastSaveTime");
parameters.Add(new QueryParameter("@LastSaveTime", DbType.DateTime, lastSaveTime));
}
sb.Append(values.ToString());
sb.Append(") ");
Epi.Data.Query insertQuery = db.CreateQuery(sb.ToString());
insertQuery.Parameters = parameters;
if (DestinationProject.CollectedDataDriver.ToLowerInvariant().Contains("epi.data.office"))
{
IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters);
object obj = command.ExecuteNonQuery();
}
else
{
db.ExecuteNonQuery(insertQuery);
}
//Parallel.ForEach(form.Pages, page =>
foreach (Page page in form.Pages)
{
WordBuilder wbFields = new WordBuilder(",");
WordBuilder wbParams = new WordBuilder(",");
List <QueryParameter> queryParams = new List <QueryParameter>();
wbFields.Add("[GlobalRecordId]");
wbParams.Add("@GlobalRecordId");
queryParams.Add(new QueryParameter("@GlobalRecordId", DbType.String, guid));
foreach (KeyValuePair <Field, object> kvp in keyValues)
{
RenderableField field = kvp.Key as RenderableField;
PackageFieldData fieldData = new PackageFieldData();
fieldData.FieldValue = kvp.Value;
fieldData.FieldName = field.Name;
fieldData.Page = field.Page;
if (field.Page.TableName.Equals(page.TableName))
{
wbFields.Add(db.InsertInEscape(fieldData.FieldName));
wbParams.Add("@" + fieldData.FieldName);
QueryParameter parameter = GetQueryParameterForField(fieldData, form, field.Page);
queryParams.Add(parameter);
}
}
sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(db.InsertInEscape(page.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.SPACE);
sb.Append("(");
sb.Append(wbFields.ToString());
sb.Append(")");
sb.Append(" values (");
sb.Append(wbParams.ToString());
sb.Append(") ");
insertQuery = db.CreateQuery(sb.ToString());
foreach (QueryParameter queryParam in queryParams)
{
insertQuery.Parameters.Add(queryParam);
}
if (DestinationProject.CollectedDataDriver.ToLowerInvariant().Contains("epi.data.office"))
{
IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters);
object obj = command.ExecuteNonQuery();
}
else
{
db.ExecuteNonQuery(insertQuery);
}
}
//);
}
/// <summary>
/// Begins the process of importing records from the data package into the destination form
/// </summary>
/// <param name="form">The form that will receive the data</param>
/// <param name="formNode">The XmlNode representing the form</param>
/// <param name="records">The data to be imported</param>
protected override void ImportRecords(View form, XmlNode formNode, List <PackageFieldData> records)
{
if (!IsUsingCustomMatchkeys) // Calling class should instantiate normal data packager if custom keys aren't used
{
throw new ApplicationException("This class should not be used without custom match keys.");
}
ImportInfo.RecordsAppended.Add(form, 0);
ImportInfo.RecordsUpdated.Add(form, 0);
IDbDriver destinationDb = DestinationProject.CollectedData.GetDatabase();
DataTable destinationKeyTable = new DataTable();
destinationKeyTable.Columns.Add(new DataColumn("GlobalRecordId", typeof(string)));
destinationKeyTable.Columns.Add(new DataColumn("Update", typeof(bool)));
destinationKeyTable.Columns.Add(new DataColumn("KeyDictionary", typeof(Dictionary <Field, object>)));
DataColumn [] primaryKey = new DataColumn[1];
primaryKey[0] = destinationKeyTable.Columns["GlobalRecordId"];
destinationKeyTable.PrimaryKey = primaryKey;
WordBuilder wb = new WordBuilder(",");
wb.Add("t.GlobalRecordId");
foreach (Field field in KeyFields)
{
wb.Add(field.Name);
}
Query selectQuery = destinationDb.CreateQuery("SELECT " + wb.ToString() + " " + form.FromViewSQL);
using (IDataReader keyTableReader = destinationDb.ExecuteReader(selectQuery))
{
while (keyTableReader.Read())
{
string guid = keyTableReader["GlobalRecordId"].ToString();
Dictionary <Field, object> keys = new Dictionary <Field, object>();
foreach (Field field in KeyFields)
{
keys.Add(field, keyTableReader[field.Name]);
}
destinationKeyTable.Rows.Add(guid, true, keys);
}
}
var query = from record in records
group record by record.RecordGUID;
IEnumerable <IEnumerable <PackageFieldData> > fieldDataLists = query as IEnumerable <IEnumerable <PackageFieldData> >;
foreach (IEnumerable <PackageFieldData> fieldDataList in fieldDataLists)
{
PackageFieldData fieldData = fieldDataList.First();
bool found = false;
foreach (DataRow row in destinationKeyTable.Rows)
{
Dictionary <Field, object> keyDictionary = row["KeyDictionary"] as Dictionary <Field, object>;
if (AreKeyFieldDictionariesEqual(keyDictionary, fieldData.KeyValues))
{
found = true;
if (!Update)
{
row["Update"] = false;
}
else
{
ImportInfo.TotalRecordsUpdated++;
ImportInfo.RecordsUpdated[form]++;
}
}
}
if (!found && Append) // no match, this is a new record that must be inserted
{
CreateNewBlankRow(form, fieldData.RecordGUID, fieldData.KeyValues);
ImportInfo.TotalRecordsAppended++;
ImportInfo.RecordsAppended[form]++;
}
}
System.Threading.Thread.Sleep(2000); // give time for DB to update
destinationKeyTable.Clear();
selectQuery = destinationDb.CreateQuery("SELECT " + wb.ToString() + " " + form.FromViewSQL);
using (IDataReader keyTableReader = destinationDb.ExecuteReader(selectQuery))
{
while (keyTableReader.Read())
{
string guid = keyTableReader["GlobalRecordId"].ToString();
Dictionary <Field, object> keys = new Dictionary <Field, object>();
foreach (Field field in KeyFields)
{
keys.Add(field, keyTableReader[field.Name]);
}
destinationKeyTable.Rows.Add(guid, true, keys);
}
}
//Parallel.ForEach(records, rec =>
// TODO: Make this faster (note that Parallel foreach seems to make it worse)
foreach (PackageFieldData rec in records)
{
bool found = false;
string targetGuid = String.Empty;
bool shouldUpdate = true;
foreach (DataRow row in destinationKeyTable.Rows)
{
Dictionary <Field, object> keyDictionary = row["KeyDictionary"] as Dictionary <Field, object>;
if (AreKeyFieldDictionariesEqual(keyDictionary, rec.KeyValues))
{
found = true;
targetGuid = row["GlobalRecordId"].ToString();
shouldUpdate = (bool)row["Update"];
break;
}
}
if (shouldUpdate && found && !String.IsNullOrEmpty(targetGuid) && rec.FieldValue != null && !String.IsNullOrEmpty(rec.FieldValue.ToString()))
{
Query updateQuery = destinationDb.CreateQuery("UPDATE " + rec.Page.TableName + " SET " +
"[" + rec.FieldName + "] = @" + rec.FieldName + " WHERE [GlobalRecordId] = @GlobalRecordId");
QueryParameter fieldParam = GetQueryParameterForField(rec, form, rec.Page);
if (fieldParam != null)
{
updateQuery.Parameters.Add(fieldParam);
updateQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, targetGuid));
int rowsAffected = destinationDb.ExecuteNonQuery(updateQuery);
if (rowsAffected == 0)
{
throw new ApplicationException("No records affected.");
}
else if (rowsAffected > 1)
{
throw new ApplicationException("Too many records affected.");
}
}
}
}
//);
}
public void CreateCaseSyncFileStart(Epi.ImportExport.Filters.RowFilters filters, Epi.RecordProcessingScope recordProcessingScope)
{
if (IsWaitingOnOtherClients)
{
return;
}
if (String.IsNullOrEmpty(SyncFilePath.Trim()))
{
throw new InvalidOperationException();
}
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
bool includeCases = IncludeCasesAndContacts || IncludeCasesOnly;
bool includeCaseExposures = true;
bool includeContacts = IncludeCasesAndContacts;
bool deIdentifyData = DeIdentifyData;
#region Remove extraneous data
int rows = 0;
OverallSyncStatus = "Deleting extraneous page table rows...";
IDbDriver db = _project.CollectedData.GetDatabase();
foreach (View form in _project.Views)
{
foreach (Page page in form.Pages)
{
Query deleteQuery = db.CreateQuery("DELETE FROM " + form.Name + " WHERE GlobalRecordId NOT IN (SELECT GlobalRecordId FROM " + page.TableName + ")");
rows = db.ExecuteNonQuery(deleteQuery);
if (rows > 0)
{
// report ??
}
Query pageDeleteQuery = db.CreateQuery("DELETE FROM " + page.TableName + " WHERE GlobalRecordId NOT IN (SELECT GlobalRecordId FROM " + form.Name + ")");
rows = db.ExecuteNonQuery(deleteQuery);
if (rows > 0)
{
// report ??
}
}
}
Query linksDeleteQuery = db.CreateQuery("DELETE FROM metaLinks WHERE ToViewId = @ToViewId AND ToRecordGuid NOT IN (SELECT GlobalRecordId FROM " + ContactForm.TableName + ")");
linksDeleteQuery.Parameters.Add(new QueryParameter("@ToViewId", DbType.Int32, ContactFormId));
rows = db.ExecuteNonQuery(linksDeleteQuery);
if (db.TableExists("metaHistory"))
{
Query historyDeleteQuery = db.CreateQuery("DELETE FROM metaHistory WHERE ContactGUID NOT IN (SELECT GlobalRecordId FROM " + ContactForm.TableName + ")");
rows = db.ExecuteNonQuery(historyDeleteQuery);
}
#endregion // Remove extraneous data
RecordsExported = String.Empty;
IsDataSyncing = true;
IsShowingExportProgress = true;
var doc = new XmlDocument {
XmlResolver = null
};
SendMessageForAwaitAll();
Task.Factory.StartNew(
() =>
{
doc = CreateCaseSyncFile(includeCases, includeCaseExposures, includeContacts, filters, deIdentifyData, recordProcessingScope);
},
System.Threading.CancellationToken.None, TaskCreationOptions.LongRunning, TaskScheduler.Default).ContinueWith(
delegate
{
try
{
if (!String.IsNullOrEmpty(doc.InnerText))
{
string compressedText = Epi.ImportExport.ImportExportHelper.Zip(doc.OuterXml);
compressedText = "[[EPIINFO7_VHF_CASE_SYNC_FILE__0937]]" + compressedText;
Epi.Configuration.EncryptStringToFile(compressedText, SyncFilePath, "vQ@6L'<J3?)~5=vQnwh(2ic;>.<=dknF&/TZ4Uu!$78", "", "", 1000);
}
}
catch (Exception)
{
// do nothing... if the XML is invalid, we should have already alerted the user in a different method
}
finally
{
SendMessageForUnAwaitAll();
}
TaskbarProgressState = System.Windows.Shell.TaskbarItemProgressState.None;
TaskbarProgressValue = 0;
ProgressValue = 0;
IsDataSyncing = false;
stopwatch.Stop();
SyncStatus = String.Empty;
OverallSyncStatus = "Finished exporting data to sync file. Elapsed time: " + stopwatch.Elapsed.TotalMinutes.ToString("F1") + " minutes.";
}, TaskScheduler.FromCurrentSynchronizationContext());
}
/// <summary>
/// Checks for problems in the source project
/// </summary>
protected override 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");
}
int rows = 0;
foreach (View form in SourceForm.Project.Views)
{
Query selectDistinctQuery = driver.CreateQuery("SELECT GlobalRecordId, COUNT(GlobalRecordId) AS n FROM " + form.TableName + " GROUP BY GlobalRecordId HAVING COUNT(GlobalRecordId) > 1");
rows = driver.Select(selectDistinctQuery).Rows.Count;
if (rows > 0)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE, "101002");
throw new InvalidOperationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE);
}
foreach (Page page in form.Pages)
{
Query deleteQuery = driver.CreateQuery("DELETE FROM " + form.Name + " WHERE GlobalRecordId NOT IN (SELECT GlobalRecordId from " + page.TableName + ")");
rows = driver.ExecuteNonQuery(deleteQuery);
if (rows > 0)
{
// report ??
}
Query pageDeleteQuery = driver.CreateQuery("DELETE FROM " + page.TableName + " WHERE GlobalRecordId NOT IN (SELECT GlobalRecordId from " + form.Name + ")");
rows = driver.ExecuteNonQuery(deleteQuery);
if (rows > 0)
{
// report ??
}
Query selectDistinctPageQuery = driver.CreateQuery("SELECT GlobalRecordId, COUNT(GlobalRecordId) AS n FROM " + page.TableName + " GROUP BY GlobalRecordId HAVING COUNT(GlobalRecordId) > 1");
rows = driver.Select(selectDistinctPageQuery).Rows.Count;
if (rows > 0)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE, "101002");
throw new InvalidOperationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE);
}
}
}
// 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 InvalidOperationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_RELATED_FORM);
}
driver.Dispose();
driver = null;
}
