public TemplateView(EntryTemplate template)
{
if (template == null)
throw new ArgumentNullException("template");
this.Name = template.Name;
this.AllowCustomFields = template.AllowCustomFields;
this.IconIndex = template.IconIndex;
this.Template = template;
RegisterDataModelEvents();
}
public void RemoveTemplate(EntryTemplate template)
{
if (template == null)
throw new ArgumentNullException("template");
if (!_templates.ContainsItem(template))
throw new ArgumentException("This Database does not contain this template.");
//find any entries in memory that use this template and convert them to custom fields, calls the db also
foreach (var field in template.Fields.ToList())
{
DemoteTemplateField(template, field);
}
var affectedEntries = _entries.Items.Where(e => e.Template == template).ToList();
foreach (var entry in affectedEntries)
{
entry.Template = null;
}
CommitEntryChanges(affectedEntries);
int templateId = _templates[template];
//call database
var purgeCommand = new SqlCommand("sp_DeleteTemplate", _connection) { CommandType = System.Data.CommandType.StoredProcedure };
purgeCommand.Parameters.Add(new SqlParameter("token", "null_token")); //TODO: token will go here
purgeCommand.Parameters.Add(new SqlParameter("template_id", templateId));
purgeCommand.ExecuteNonQuery();
_templates.Remove(template);
}
public void RefreshTemplates()
{
if (!this.Connected)
throw new InvalidOperationException("Cannot refresh the template list because the database is not connected.");
if (!this.Authenticated)
throw new InvalidOperationException("Cannot refresh the template list because the current user is not authenticated with the Database.");
var getListCommand = new SqlCommand("sp_GetAllTemplates", _connection) { CommandType = System.Data.CommandType.StoredProcedure };
getListCommand.Parameters.Add(new SqlParameter("token", "null_token")); //TODO: token will go here
using (var reader = getListCommand.ExecuteReader())
{
HashSet<int> templateIdsInMemory = new HashSet<int>(_templates.Keys);
while (reader.Read())
{
int templateId = (int)reader[0];
if (templateIdsInMemory.Contains(templateId))
templateIdsInMemory.Remove(templateId);
EntryTemplate template;
if (!_templates.TryGetItem(templateId, out template))
{
template = new EntryTemplate();
_templates.Add(templateId, template);
}
//update all fields
template.Name = reader[1] as string;
template.IconIndex = (int)reader[2];
template.AllowCustomFields = (bool)reader[3];
}
//delete templates that were not returned
foreach (var id in templateIdsInMemory)
{
_templates.Remove(id);
}
reader.NextResult();
HashSet<int> templateFieldIdsInMemory = new HashSet<int>(_templateFields.Keys);
while (reader.Read())
{
int templateFieldId = (int)reader[0];
if (templateFieldIdsInMemory.Contains(templateFieldId))
templateFieldIdsInMemory.Remove(templateFieldId);
EntryField templateField;
if (!_templateFields.TryGetItem(templateFieldId, out templateField))
{
//add the template field to the appropriate template
int templateId = (int)reader[1];
EntryTemplate template;
if (!_templates.TryGetItem(templateId, out template))
throw new InvalidOperationException("A template field was returned for a template that is not defined in the database.");
templateField = new EntryField();
template.AddField(templateField);
_templateFields.Add(templateFieldId, templateField);
}
templateField.Name = reader[2] as string;
templateField.EntryType = (EntryFieldType)reader[3];
templateField.DefaultValue = reader[4] as string;
}
//remove fields that were not returned
foreach (int id in templateFieldIdsInMemory)
{
_templateFields.Remove(id);
}
}
}
/// <summary>
/// Saves a template and its field definitions to the database from memory.
/// </summary>
/// <param name="template"></param>
public void CommitTemplate(EntryTemplate template)
{
if (!this.Connected)
throw new InvalidOperationException("Cannot commit the template changes because the database is not connected.");
if (!this.Authenticated)
throw new InvalidOperationException("Cannot commit the template changes because the current user is not authenticated with the Database.");
//create the query first
var query = new XElement("Template",
new XAttribute("id", _templates[template]),
new XAttribute("name", template.Name),
new XAttribute("icon_id", template.IconIndex),
new XAttribute("allow_custom_fields", template.AllowCustomFields),
new XElement("Fields",
from f in template.Fields
select new XElement("Field",
new XAttribute("id", _templateFields.ContainsItem(f) ? _templateFields[f] : -1),
new XAttribute("name", f.Name),
new XAttribute("type", (int)f.EntryType),
new XAttribute("default_value", f.DefaultValue ?? String.Empty))));
//demote any fields that were deleted second
var deletedFields = _templateFields.Items.Where(f => f.Container == template && !template.Fields.Contains(f)).ToList();
foreach (var field in deletedFields)
{
DemoteTemplateField(template, field);
}
//Query the Database
var updateCommand = new SqlCommand("sp_UpdateTemplate", _connection) { CommandType = System.Data.CommandType.StoredProcedure };
updateCommand.Parameters.Add(new SqlParameter("token", "null_token")); //TODO: token will go here
updateCommand.Parameters.Add(new SqlParameter("updateXml", query.ToString()));
//assign IDs from the -1 Fields
using (var reader = updateCommand.ExecuteReader())
{
var unassigned = template.Fields.Where(f => !_templateFields.ContainsItem(f)).ToList();
int index = 0;
while (reader.Read())
{
if (index > (unassigned.Count - 1))
throw new InvalidOperationException("The database returned new IDs that were not expected.");
int id = (int)reader[0];
_templateFields.Add(id, unassigned[index]);
index++;
}
if (index != unassigned.Count)
throw new InvalidOperationException("The database did not return new IDs for all the new template fields.");
}
}
public void AddTemplate(EntryTemplate template)
{
if (template == null)
throw new ArgumentNullException("template");
if (_templates.ContainsItem(template))
throw new ArgumentException("This Database already contains this template.");
var fields = template.Fields.ToList();
var query = new XElement("Template",
new XAttribute("name", template.Name),
new XAttribute("icon_id", template.IconIndex),
new XAttribute("allow_custom_fields", template.AllowCustomFields),
new XElement("Fields",
from f in fields
select new XElement("Field",
new XAttribute("name", f.Name),
new XAttribute("type", (int)f.EntryType),
new XAttribute("default_value", f.DefaultValue ?? String.Empty))));
//Query the Database
var addCommand = new SqlCommand("sp_AddTemplate", _connection) { CommandType = System.Data.CommandType.StoredProcedure };
addCommand.Parameters.Add(new SqlParameter("token", "null_token")); //TODO: token will go here
addCommand.Parameters.Add(new SqlParameter("addXml", query.ToString()));
using (var reader = addCommand.ExecuteReader())
{
//assign IDs to the template
int templateId = -1;
while (reader.Read())
{
templateId = (int)reader[0];
break;
}
if (templateId == -1)
throw new InvalidOperationException("No template ID was returned by the database.");
_templates.Add(templateId, template);
reader.NextResult();
//assign IDs to the template fields
int fieldIndex = 0;
while (reader.Read())
{
if (fieldIndex > fields.Count - 1)
throw new InvalidOperationException("More template field IDs were returned from the database than expected.");
int fieldId = (int)reader[0];
_templateFields.Add(fieldId, fields[fieldIndex]);
fieldIndex++;
}
}
}
private void RefreshClientEntriesInternal(ClientRecord client)
{
//preparation
int clientId = _clients[client];
if (!_clients.TryGetKey(client, out clientId))
throw new ArgumentException("The specified client is not in this database.");
var newEntries = new List<ClientEntry>();
var prevEntryIds = client.Entries.Select(e => _entries[e]).ToList();
//Query the Database
var getListCommand = new SqlCommand("sp_GetClientEntries", _connection) { CommandType = System.Data.CommandType.StoredProcedure };
getListCommand.Parameters.Add(new SqlParameter("token", "null_token")); //TODO: token will go here
getListCommand.Parameters.Add(new SqlParameter("client_id", clientId));
using (var reader = getListCommand.ExecuteReader())
{
//result set 1: entry list for the specified client
while (reader.Read())
{
ClientEntry entry;
int clientEntryDbId = (int)reader[0];
//index the entry if it is new
if (!_entries.TryGetItem(clientEntryDbId, out entry))
{
entry = new ClientEntry();
_entries.Add(clientEntryDbId, entry);
newEntries.Add(entry);
}
//update the properties of the entry, regardless if it existed before
entry.Name = (string)reader[2];
entry.IconIndex = (int)reader[3];
entry.Notes = WebUtility.HtmlDecode((reader[5] as string ?? String.Empty).Replace("<br>", Environment.NewLine));
//set the template, it will get updated later if it doesn't exist yet
//we index the new template right away
EntryTemplate template;
int templateId = (reader[4] == DBNull.Value) ? -1 : (int)reader[4];
if (templateId == -1)
{
template = null; //orphaned record
}
else if (!_templates.TryGetItem(templateId, out template))
{
template = new EntryTemplate();
_templates.Add(templateId, template);
}
entry.Template = template;
}
//result set 2: template definition
reader.NextResult();
while (reader.Read())
{
EntryTemplate template;
int templateDbId = (int)reader[0];
if (!_templates.TryGetItem(templateDbId, out template))
throw new InvalidOperationException("A template was returned that was not part of the first result set.");
//update the properties
template.Name = reader[1] as string;
template.IconIndex = (int)reader[2];
template.AllowCustomFields = (bool)reader[3];
}
//result set 3: fields for the previous templates
reader.NextResult();
while (reader.Read())
{
EntryField templateField;
EntryTemplate template;
int templateFieldId = (int)reader[0];
int templateId = (int)reader[1];
if (!_templates.TryGetItem(templateId, out template))
throw new InvalidOperationException("A template field was returned that belongs to a template that was not returned in the previous result set.");
if (!_templateFields.TryGetItem(templateFieldId, out templateField))
{
templateField = new EntryField();
_templateFields.Add(templateFieldId, templateField);
}
if (!template.Fields.Contains(templateField))
template.AddField(templateField);
//update the properties
templateField.Name = reader[2] as string;
templateField.EntryType = (EntryFieldType)reader[3];
templateField.DefaultValue = reader[4] as string;
}
//TODO: remove any fields that were not returned
//result set 4: field values
reader.NextResult();
while (reader.Read())
{
int fieldValueId = (int)reader[0];
int entryId = (int)reader[1];
ClientEntry entry;
if (!_entries.TryGetItem(entryId, out entry))
throw new InvalidOperationException("A ClientEntry was returned that was not defined in a previous result set.");
int templateFieldId = (reader[2] == DBNull.Value) ? -1 : (int)reader[2];
string entryValue = reader[4] as string;
//indexing of fields values:
// template field values are indexed by TFVR objects in the _templateFieldsById dictionary
// custom field values are indexed by just the EntryField object in the _customFieldsById dictionary
if (templateFieldId == -1)
{
//custom field
string fieldName = reader[3] as string;
EntryField customField;
if (!_customFields.TryGetItem(fieldValueId, out customField))
{
customField = new EntryField() { Name = fieldName }; //todo: type
entry.AddCustomField(customField);
_customFields.Add(fieldValueId, customField);
}
//update the name and value
customField.Name = fieldName;
entry.SetValue(customField, DataSecurity.DecryptStringAES(entryValue));
}
else
{
//find the template EntryField by ID
EntryField templateField;
if (_templateFields.TryGetItem(templateFieldId, out templateField))
{
//save the ID of the field value for saving later
if (!_templateValues.Values.Any(tfvr => tfvr.ClientEntry == entry && tfvr.TemplateField == templateField))
_templateValues.Add(fieldValueId, new TemplateFieldValueRelation() { ClientEntry = entry, TemplateField = templateField });
entry.SetValue(templateField, DataSecurity.DecryptStringAES(entryValue));
}
else
{
throw new InvalidOperationException("A template field was returned that cannot be matched to an existing template.");
}
}
}
//TODO: remove any fields that were not returned
}
//clean up the internal list, and raise events for the UI
var deletedEntryIds = prevEntryIds.Except(_entries.Keys).ToList();
if (deletedEntryIds.Any())
{
var deletedEntries = new List<ClientEntry>();
foreach (var id in deletedEntryIds)
{
var entry = _entries[id];
deletedEntries.Add(entry);
_entries.Remove(entry);
}
client.RemoveEntries(deletedEntries);
}
if (newEntries.Any())
client.AddEntries(newEntries);
}
private void DemoteTemplateField(EntryTemplate template, EntryField field)
{
foreach (var entry in _entries.Items.Where(e => e.Template == template))
{
//transfer the value to the custom field if applicable
var relation = _templateValues.FirstOrDefault(tfvr => tfvr.Value.ClientEntry == entry && tfvr.Value.TemplateField == field);
if (relation.Value != null)
{
var newField = new EntryField() { Name = field.Name, EntryType = field.EntryType };
entry.AddCustomField(newField);
var value = entry.GetOrphanedValue(field);
entry.SetValue(newField, DataSecurity.DecryptStringAES(value));
_templateValues.Remove(relation.Key);
_customFields.Add(relation.Key, newField);
}
}
//call the DB to do the same as above
int fieldId = _templateFields[field];
var purgeCommand = new SqlCommand("sp_DeleteTemplateField", _connection) { CommandType = System.Data.CommandType.StoredProcedure };
purgeCommand.Parameters.Add(new SqlParameter("token", "null_token")); //TODO: token will go here
purgeCommand.Parameters.Add(new SqlParameter("template_field_id", fieldId));
purgeCommand.ExecuteNonQuery();
_templateFields.Remove(fieldId);
}