/// <summary>
/// Creates a relationship between a field and a child view
/// </summary>
/// <param name="fieldId">Id of the field</param>
/// <param name="relatedViewId">Id of the view the field is related to</param>
/// <param name="relateCondition">Conditions to show the related view</param>
/// <param name="shouldReturnToParent">Whether or not the related view returns to parent</param>
public void RelateFieldToView(Guid uniqueId, int relatedViewId, string relateCondition, bool shouldReturnToParent)
{
try
{
Query fieldQuery = db.CreateQuery("update metaFields set [RelatedViewId] = @RelatedViewId, [RelateCondition] = @RelateCondition, [ShouldReturnToparent] = @ShouldReturnToParent where UniqueId = @UniqueId");
fieldQuery.Parameters.Add(new QueryParameter("@RelatedViewId", DbType.Int32, relatedViewId));
fieldQuery.Parameters.Add(new QueryParameter("@RelateCondition", DbType.String, relateCondition));
fieldQuery.Parameters.Add(new QueryParameter("@ShouldReturnToParent", DbType.Boolean, shouldReturnToParent));
fieldQuery.Parameters.Add(new QueryParameter("@UniqueId", DbType.Guid, uniqueId));
Query viewQuery = db.CreateQuery("update metaViews set [IsRelatedView] = @IsRelatedView where [ViewId] = @ViewId");
viewQuery.Parameters.Add(new QueryParameter("@IsRelatedView", DbType.Boolean, true));
viewQuery.Parameters.Add(new QueryParameter("@ViewId", DbType.Int32, relatedViewId));
db.ExecuteNonQuery(fieldQuery);
db.ExecuteNonQuery(viewQuery);
if (relatedViewId != 0)
{
Epi.View view = this.GetViewById(relatedViewId);
ForeignKeyField foreignKeyField;
if (view.IsRelatedView)
{
try
{
foreignKeyField = (ForeignKeyField) view.Fields["FKEY"];
}
catch (System.Exception ex)
{
if (ex != null) // this line is to avoid compiler warning;
{
foreignKeyField = new ForeignKeyField(view);
foreignKeyField.SaveToDb();
}
}
}
}
}
catch (Exception ex)
{
throw new GeneralException("Could not create new relate info", ex);
}
}
/// <summary>
/// Creates a new view in XML metadata
/// </summary>
/// <param name="view">View to be inserted in metadata</param>
public void InsertView(View view)
{
XmlDocument xmlDoc = GetXmlDocument();
XmlNode viewsNode = GetViewsNode();
XmlElement viewElement = xmlDoc.CreateElement("View");
viewsNode.AppendChild(viewElement);
view.ViewElement = viewElement;
XmlAttribute viewId = xmlDoc.CreateAttribute("ViewId");
viewId.Value = GetMaxViewId().ToString();
view.Id = int.Parse(viewId.Value);
viewElement.Attributes.Append(viewId);
XmlAttribute viewNameAttribute = xmlDoc.CreateAttribute("Name");
viewNameAttribute.Value = view.Name;
viewElement.Attributes.Append(viewNameAttribute);
XmlAttribute isRelatedView = xmlDoc.CreateAttribute("IsRelatedView");
isRelatedView.Value = view.IsRelatedView.ToString();
viewElement.Attributes.Append(isRelatedView);
viewElement.AppendChild(xmlDoc.CreateElement("CheckCodeBefore"));
viewElement.AppendChild(xmlDoc.CreateElement("CheckCodeAfter"));
viewElement.AppendChild(xmlDoc.CreateElement("RecordCheckCodeBefore"));
viewElement.AppendChild(xmlDoc.CreateElement("RecordCheckCodeAfter"));
viewElement.AppendChild(xmlDoc.CreateElement("CheckCodeVariableDefinitions"));
viewElement.AppendChild(xmlDoc.CreateElement("Pages"));
viewElement.AppendChild(xmlDoc.CreateElement("Fields"));
//Insert system fields .. RecStatus and UniqueKey
RecStatusField recStatusField = new RecStatusField(view, viewElement);
UniqueKeyField uniqueKeyField = new UniqueKeyField(view, viewElement);
recStatusField.SaveToDb();
uniqueKeyField.SaveToDb();
if (view.IsRelatedView)
{
ForeignKeyField foreignKeyField = new ForeignKeyField(view, viewElement);
foreignKeyField.SaveToDb();
}
Save();
}
/// <summary>
/// Creates a view in a specified project
/// </summary>
/// <old-param name="isrelatedview">Whether or not this view is a related (child) view</old-param>
/// <old-param name="viewname">Name of the view</old-param>
public void InsertView(View view)
{
#region Input Validation
if (view == null)
{
throw new ArgumentNullException("view");
}
#endregion Input Validation
try
{
Query insertQuery = db.CreateQuery("insert into metaViews([Name], [IsRelatedView], [CheckCode], [Width], [Height], [Orientation], [LabelAlign] ) values (@Name, @IsRelatedView, @CheckCode, @Width, @Height, @Orientation, @LabelAlign)");
insertQuery.Parameters.Add(new QueryParameter("@Name", DbType.String, view.Name));
insertQuery.Parameters.Add(new QueryParameter("@IsRelatedView", DbType.Boolean, view.IsRelatedView));
insertQuery.Parameters.Add(new QueryParameter("@CheckCode", DbType.String, view.CheckCode));
insertQuery.Parameters.Add(new QueryParameter("@Width", DbType.Int32, view.PageWidth));
insertQuery.Parameters.Add(new QueryParameter("@Height", DbType.Int32, view.PageHeight));
insertQuery.Parameters.Add(new QueryParameter("@Orientation", DbType.String, view.PageOrientation));
insertQuery.Parameters.Add(new QueryParameter("@LabelAlign", DbType.String, view.PageLabelAlign));
db.ExecuteNonQuery(insertQuery);
view.Id = this.GetMaxViewId();
RecStatusField recStatusField = new RecStatusField(view);
UniqueKeyField uniqueKeyField = new UniqueKeyField(view);
GlobalRecordIdField globalRecordIdField = new GlobalRecordIdField(view);
uniqueKeyField.SaveToDb();
recStatusField.SaveToDb();
globalRecordIdField.SaveToDb();
if (view.IsRelatedView)
{
ForeignKeyField foreignKeyField = new ForeignKeyField(view);
foreignKeyField.SaveToDb();
}
}
catch (Exception ex)
{
throw new GeneralException("Could not create view in the database", ex);
}
}
/// <summary>
/// Transfers the collected data into the new database
/// </summary>
/// <param name="view">A view object</param>
private void CopyCollectedData(View view)
{
//System.Console.WriteLine("Started Copy Data {0}", System.DateTime.Now);
Epi.Data.Services.CollectedDataProvider collectedDataProvider = destinationProject.CollectedData;
Epi.Data.IDbDriver db = collectedDataProvider.GetDatabase();
System.Collections.Generic.List<System.Threading.Thread> ThreadList = new System.Collections.Generic.List<System.Threading.Thread>();
string ConnectionString = db.ConnectionString;
try
{
RaiseEventImportStatus(view.TableName + StringLiterals.ELLIPSIS);
if (collectedDataProvider.TableExists(view.TableName))
collectedDataProvider.DeleteTable(view.TableName);
foreach (Page page in view.Pages)
{
if (collectedDataProvider.TableExists(page.TableName))
collectedDataProvider.DeleteTable(page.TableName);
}
if (!collectedDataProvider.TableExists(view.TableName))
collectedDataProvider.CreateDataTableForView(view, 1);
if (sourceProject.CollectedData.TableExists(view.TableName))
{
// If the view has related views, we need to copy the old UniqueKey values
// so we can do a proper relationship later on. These will get stored in OldUniqueKey.
string viewName = view.Name;
if (view.Name.StartsWith("view") == false)
{
viewName = "view" + viewName;
}
//List<string> relatedViews = sourceProject.Metadata.GetRelatedViewNames(viewName);
//if (relatedViews.Count > 0)
//{
//hasRelatedViews = true;
// TODO: Find some way of changing "NUMERIC" to db-agnostic value; using this
// simple method now just to get this working
//Query alterQuery = db.CreateQuery(
// "alter table " + view.TableName + " add OldUniqueKey " + "NUMERIC");
TableColumn column = new TableColumn("OldUniqueKey", GenericDbColumnType.Int32, true);
db.AddColumn(view.TableName, column);
foreach(Page page in view.Pages)
{
db.AddColumn(page.TableName, column);
}
//}
if (view.IsRelatedView)
{
//Query alterQuery = db.CreateQuery(
// "alter table " + view.TableName + " add OldFKEY " + "NUMERIC");
//db.ExecuteNonQuery(alterQuery);
column = new TableColumn("OldFKEY", GenericDbColumnType.Int32, true);
db.AddColumn(view.TableName, column);
// Create FKEY in MetaFields
ForeignKeyField foreignKeyField;
try
{
foreignKeyField = (ForeignKeyField)view.Fields["FKEY"];
}
catch (System.Exception ex)
{
if (ex != null) // this line is to avoid compiler warning;
{
foreignKeyField = new ForeignKeyField(view);
foreignKeyField.SaveToDb();
}
}
}
int recordCount = 0;
//////////////////////////////////////////////////
if (!string.IsNullOrEmpty(view.TableName))
{
IDataReader reader = sourceProject.CollectedData.GetTableDataReader(view.TableName);
StringBuilder InsertSQL = new StringBuilder();
InsertSQL.Append("Insert Into [");
InsertSQL.Append(view.TableName);
InsertSQL.Append("] (");
view.MustRefreshFieldCollection = true;
foreach (Field field in view.Fields.TableColumnFields)
{
if (field is UniqueKeyField)
{
InsertSQL.Append("[OldUniqueKey],");
break;
}
}
if (view.IsRelatedView)
{
foreach (Field field in view.Fields.TableColumnFields)
{
if (field is ForeignKeyField)
{
InsertSQL.Append("[OldFKEY],");
break;
}
}
}
InsertSQL.Append("[GlobalRecordId]");
//InsertSQL.Remove(InsertSQL.Length - 1, 1);
InsertSQL.Append(")\n");
StringBuilder fieldValues = new StringBuilder();
Epi.Data.Query query = db.CreateQuery("");
while (reader.Read())
{
recordCount++;
if (recordCount % 1000 == 0)
{
Epi.Data.DBReadExecute.ExecuteSQL(ConnectionString, InsertSQL.ToString() + query.GetInsertValue(fieldValues.ToString()), 0);
fieldValues.Length = 0;
RaiseEventImportStatus(string.Format(SharedStrings.IMPORT_PROCESSED_RECORDS_BASE_TABLE, recordCount, view.Name)); // TODO: Make sure to modify this so that it doesn't appear in the log.
RaiseEventProgressBarStep();
}
foreach (Field field in view.Fields.TableColumnFields)
{
if (field is UniqueKeyField)
{
Object fieldValue = reader[field.Name];
string fieldValueString = string.Empty;
fieldValueString = fieldValue.ToString();
fieldValues.Append(fieldValueString);
fieldValues.Append(",");
break;
}
}
foreach (Field field in view.Fields.TableColumnFields)
{
if (field.Name.ToLower().Equals("fkey"))
{
Object fieldValue = reader[field.Name];
string fieldValueString = string.Empty;
fieldValueString = fieldValue.ToString();
fieldValues.Append(fieldValueString);
fieldValues.Append(",");
break;
}
}
foreach (Field field in view.Fields.TableColumnFields)
{
if (field is GlobalRecordIdField)
{
string fieldValueString = Util.InsertInSingleQuotes(System.Guid.NewGuid().ToString());
fieldValues.Append(fieldValueString);
fieldValues.Append(",");
break;
}
}
fieldValues.Remove(fieldValues.Length - 1, 1);
fieldValues.Append(";");
}
// execute the remaining statments
if (string.IsNullOrEmpty(fieldValues.ToString()) == false)
{
Epi.Data.DBReadExecute.ExecuteSQL(ConnectionString, InsertSQL.ToString() + query.GetInsertValue(fieldValues.ToString()), 0);
}
}
//////////////////////////////////////////////////
recordCount = 0;
Epi.Epi2000.View sourceView = null;
foreach (Epi.Epi2000.View epi2000View in sourceProject.Views)
{
if (epi2000View.NameWithoutPrefix.ToLower().Equals(view.Name.ToLower()))
{
sourceView = epi2000View;
break;
}
}
foreach (Page page in view.Pages)
{
recordCount = 0;
if (sourceView == null)
{
Logger.Log(DateTime.Now + ": " + SharedStrings.IMPORT_ERROR_SOURCE_VIEW_NULL);
throw new ApplicationException(SharedStrings.IMPORT_ERROR_SOURCE_VIEW_NULL);
}
IDataReader reader = sourceProject.CollectedData.GetTableDataReader(view.TableName);
string dataTableNames = string.Empty;
string joinClause = "WHERE ";
string fromClause = string.Empty;
if (sourceView.IsWideTableView)
{
foreach (string s in sourceView.TableNames)
{
dataTableNames = dataTableNames + s + ",";
joinClause = joinClause + sourceView.TableNames[0] + ".UniqueKey = " + s + ".UniqueKey AND ";
}
dataTableNames = dataTableNames.TrimEnd(',');
joinClause = joinClause.Remove(joinClause.Length - 4, 4);
fromClause = " FROM " + dataTableNames + " " + joinClause;
string fieldNames = sourceView.TableNames[0] + ".UniqueKey AS UniqueKey,";
foreach (Field field in page.Fields)
{
if (field is IDataField && !(field is GlobalRecordIdField) )
{
fieldNames = fieldNames + "[" + field.Name + "],";
}
}
fieldNames = fieldNames.TrimEnd(',');
string selectCommand = "SELECT " + fieldNames + " " + fromClause;
Query selectQuery = db.CreateQuery(selectCommand);
reader = sourceProject.CollectedData.GetDatabase().ExecuteReader(selectQuery);
}
StringBuilder InsertSQL = new StringBuilder();
InsertSQL.Append("Insert Into [");
InsertSQL.Append(/*view.TableName*/ page.TableName);
InsertSQL.Append("] (");
// Insert Into [TableName] ( [f1], ... [fn])
view.MustRefreshFieldCollection = true;
InsertSQL.Append("[OldUniqueKey],[GlobalRecordId],");
foreach (Field field in page.Fields /*view.Fields.TableColumnFields*/)
{
// Eliminate UniqueKeyFields. They are not inserted explicitly.
if (!(field is UniqueKeyField))
{
if (field is InputFieldWithoutSeparatePrompt || field is InputFieldWithSeparatePrompt)
{
InsertSQL.Append("[");
InsertSQL.Append(field.Name);
InsertSQL.Append("],");
}
}
//else if (/*hasRelatedViews &&*/ field is UniqueKeyField)
//{
// InsertSQL.Append("[OldUniqueKey],");
//}
}
InsertSQL.Remove(InsertSQL.Length - 1, 1);
InsertSQL.Append(")\n");
StringBuilder fieldValues = new StringBuilder();
Epi.Data.Query query = db.CreateQuery("");
while (reader.Read())
{
recordCount++;
if (recordCount % 1000 == 0)
{
Epi.Data.DBReadExecute.ExecuteSQL(ConnectionString, InsertSQL.ToString() + query.GetInsertValue(fieldValues.ToString()), 0);
fieldValues.Length = 0;
RaiseEventImportStatus(string.Format("Processed {0:n0} records on page {1}", recordCount, page.Name)); // TODO: Make sure to modify this so that it doesn't appear in the log.
RaiseEventProgressBarStep();
}
fieldValues = fieldValues.Append(reader["UniqueKey"].ToString());
fieldValues.Append(",");
//fieldValues = fieldValues.Append(Util.InsertInSingleQuotes(System.Guid.NewGuid().ToString()));
fieldValues = fieldValues.Append(Util.InsertInSingleQuotes("temp" + recordCount.ToString()));
fieldValues.Append(",");
foreach (Field field in /*view.Fields.TableColumnFields*/page.Fields)
{
// Eliminate UniqueKeyFields. They are not inserted explicitly.
if (!(field is UniqueKeyField) && !(field is GlobalRecordIdField) && (field is InputFieldWithoutSeparatePrompt || field is InputFieldWithSeparatePrompt))
{
if (!Util.IsEmpty(reader[field.Name]))
{
Object fieldValue = reader[field.Name];
string fieldValueString = string.Empty;
if (field is DateField)
{
fieldValueString = db.FormatDate((DateTime)fieldValue);
}
else if (field is ImageField)
{
//fieldValueString = db.FormatTime((DateTime)fieldValue);
}
else if (field is TimeField)
{
fieldValueString = db.FormatTime((DateTime)fieldValue);
}
else if (field is DateTimeField)
{
fieldValueString = db.FormatDateTime((DateTime)fieldValue);
}
else if ((field is PhoneNumberField) || (field is TableBasedDropDownField) || (field is TextField))
{
fieldValueString = Util.InsertInSingleQuotes(fieldValue.ToString());
}
else if (field is CheckBoxField)
{
bool checkboxValue = bool.Parse(fieldValue.ToString());
if (checkboxValue)
{
fieldValueString = "1";
}
else
{
fieldValueString = "0";
}
}
else if (field is OptionField)
{
try
{
string viewNameWithPrefix = "view" + view.Name;
string tableName = view.TableName;
DataTable fieldsTable = sourceProject.Metadata.GetFieldsAsDataTable(viewNameWithPrefix);
foreach (DataRow fieldRow in fieldsTable.Rows)
{
string fieldName = fieldRow["Name"].ToString();
if (fieldName == field.Name)
{
//if(db.ColumnExists(tableName, fieldName) == false) { break; }
string[] items = fieldRow["Lists"].ToString().Split(';');
for (int i = 0; i < items.Length; i++)
{
if (items[i] == fieldValue.ToString())
{
fieldValueString = i.ToString();
}
}
}
}
}
catch
{
}
}
else
{
fieldValueString = fieldValue.ToString();
}
fieldValues.Append(fieldValueString);
fieldValues.Append(",");
}
else
{
fieldValues.Append("null,");
}
}
else if (field is GlobalRecordIdField)
{
string fieldValueString = Util.InsertInSingleQuotes(System.Guid.NewGuid().ToString());
if (view.IsRelatedView == false)
{
fieldValues.Append(fieldValueString);
}
else
{
fieldValues.Append("''");
}
fieldValues.Append(",");
}
//else if (field is UniqueKeyField/* && hasRelatedViews*/)
//{
// //if (!Util.IsEmpty(reader[field.Name]))
// //{
// Object fieldValue = reader[field.Name];
// string fieldValueString = string.Empty;
// fieldValueString = fieldValue.ToString();
// fieldValues.Append(fieldValueString);
// fieldValues.Append(",");
// //}
//}
}
fieldValues.Remove(fieldValues.Length - 1, 1);
fieldValues.Append(";");
}
// execute the remaining statments
if (string.IsNullOrEmpty(fieldValues.ToString()) == false)
{
Epi.Data.DBReadExecute.ExecuteSQL(ConnectionString, InsertSQL.ToString() + query.GetInsertValue(fieldValues.ToString()), 0);
}
}
UpdatePageTableGUIDs(view);
if (view.IsRelatedView)
{
//Query updateQuery = db.CreateQuery("update " + view.TableName + " set OldFKEY = FKEY");
//db.ExecuteNonQuery(updateQuery);
}
RaiseEventImportStatus(string.Format(SharedStrings.IMPORT_COMPLETE, recordCount));
//System.Console.WriteLine("Finished Copy Data {0}", System.DateTime.Now);
}
}
catch (SqlException se)
{
if (se.Number == 296)
{
throw new GeneralException(string.Format(SharedStrings.IMPORT_ERROR_BAD_DATE_DATA, view.Name));
}
}
finally
{
}
}