public CreateReportItem(ReportItemBase reportItem)
{
this.Groups = new DBContent().ReportGroups;
this.ReportItem = reportItem;
this.Connections = new DBContent().BasicConnections;
this.Forms = new DBContent().FullForms;
this.Templates = new DBContent().FullTemplates;
if (this.ReportItem == null)
{
this.ReportItem = new ReportItemBase(-1, "", "",
this.Connections.Count() > 0 ? new DBContent().GetConnection(this.Connections[0].ID) : null,
null,
this.Templates.Count() > 0 ? new DBContent().GetTemplate(this.Templates[0].ID): null);
}
//Connections
if (this.ReportItem.ConnectionItem != null)
{
foreach (OptionItem item in this.Connections)
{
item.SetSelected(item.ID == ReportItem.ConnectionItem.ID);
}
}
//Forms
this.Forms.Insert(0, new BasicEntry(-1, "", ""));
if (ReportItem.FormItem != null)
{
foreach (OptionItem item in this.Forms)
{
item.SetSelected(item.ID == ReportItem.FormItem.ID);
}
}
//Templates
if (ReportItem.TemplateItem != null)
{
foreach (OptionItem item in this.Templates)
{
item.SetSelected(item.ID == ReportItem.TemplateItem.ID);
}
}
}
public DisplayReportItem(ReportItemBase reportItem,
Dictionary<string, object> physicalFormValues)
{
this.ReportItem = reportItem;
this.PhysicalFormValues = physicalFormValues;
}
public bool SaveReport(ReportItemBase item, out string errormessage)
{
errormessage = "";
if (item == null)
{
errormessage = "Invalid report.";
return(false);
}
if (String.IsNullOrWhiteSpace(item.GroupName))
{
errormessage = "The report group cannot be blank.";
return(false);
}
if (String.IsNullOrWhiteSpace(item.Name))
{
errormessage = "The report name cannot be blank.";
return(false);
}
if (item.ConnectionItem == null)
{
errormessage = "Connection cannot be null.";
return(false);
}
if (item.TemplateItem == null)
{
errormessage = "Template cannot be null.";
return(false);
}
try
{
string sql = "";
using (var trans = _conn.BeginTransaction())
{
//setup the group ID
int groupID = -1;
sql = "SELECT id FROM [ReportGroup] WHERE name LIKE @groupname";
this.AddParameter("@groupname", item.GroupName);
_reader = this.ExecuteReader(sql);
bool hasRows = false;
while (_reader.Read())
{
hasRows = true;
groupID = int.Parse(_reader[0].ToString());
break;
}
_reader.Close();
if (!hasRows)
{
sql = "INSERT INTO [ReportGroup](name) VALUES (@groupname)";
this.ExecuteNonQuery(sql);
sql = "SELECT id FROM [ReportGroup] WHERE name LIKE @groupname";
_reader = this.ExecuteReader(sql);
while (_reader.Read())
{
groupID = int.Parse(_reader[0].ToString());
}
_reader.Close();
this.ClearParameters();
}
this.AddParameter("@id", item.ID);
this.AddParameter("@name", item.Name);
this.AddParameter("@fk_connection", item.ConnectionItem.ID);
this.AddParameter("@fk_group", groupID);
this.AddParameter("@fk_template", item.TemplateItem.ID);
if (item.FormItem == null)
{
this.AddParameter("@fk_form", DBNull.Value);
}
else
{
this.AddParameter("@fk_form", item.FormItem.ID);
}
if (item.ID > 0)
{
sql = "SELECT id FROM Report WHERE id != @id AND name LIKE @name";
_reader = this.ExecuteReader(sql);
if (_reader.HasRows)
{
errormessage = "A report with that name already exists.";
return(false);
}
_reader.Close();
sql = "UPDATE Report SET name = @name, fk_connection = @fk_connection, fk_group = @fk_group, fk_template = @fk_template, fk_form = @fk_form WHERE id = @id";
}
else
{
sql = "SELECT id FROM Report WHERE name LIKE @name";
_reader = this.ExecuteReader(sql);
if (_reader.HasRows)
{
errormessage = "A report with that name already exists.";
return(false);
}
_reader.Close();
sql = "INSERT INTO Report(name, fk_connection, fk_group, fk_template, fk_form)";
sql += " VALUES(@name, @fk_connection, @fk_group, @fk_template, @fk_form)";
}
this.ExecuteNonQuery(sql);
sql = "DELETE FROM ReportGroup WHERE id NOT IN (SELECT fk_group FROM Report)";
this.ExecuteNonQuery(sql);
sql = "DELETE FROM TagValue WHERE fk_report=@id";
this.ExecuteNonQuery(sql);
int reportingID = -1;
sql = "SELECT id FROM Report WHERE name LIKE @name";
_reader = this.ExecuteReader(sql);
while (_reader.Read())
{
reportingID = int.Parse(_reader[0].ToString());
}
_reader.Close();
this.AddParameter("@id", reportingID);
List <string> tagTypes = new List <string>();
sql = "SELECT type FROM [Tag]";
_reader = this.ExecuteReader(sql);
while (_reader.Read())
{
tagTypes.Add(_reader[0].ToString().ToLower());
}
_reader.Close();
this.AddParameter("@tag", "");
List <SWBaseTag> combined = new List <SWBaseTag>();
if (item.TemplateTags != null)
{
combined.AddRange(item.TemplateTags);
}
if (item.FormTags != null)
{
combined.AddRange(item.FormTags);
}
foreach (SWBaseTag tag in combined)
{
if (!tagTypes.Contains(tag.TagType))
{
sql = "INSERT INTO [Tag](type) VALUES(@tag);";
this.AddParameter("@tag", tag.TagType);
this.ExecuteNonQuery(sql);
tagTypes.Add(tag.TagType);
}
}
this.AddParameter("@tagName", "");
this.AddParameter("@tagValue", "");
this.AddParameter("@itemType", "");
foreach (SWBaseTag tag in combined)
{
sql = "INSERT INTO [TagValue](name, itemType, value, fk_report, fk_tag)";
sql += " VALUES(@tagName, @itemType, @tagValue, @id, ";
sql += " (SELECT id FROM [Tag] WHERE type = @tag))";
this.AddParameter("@tagName", tag.Name);
DBContent.TagValueItemTypes type;
if (item.TemplateTags.Contains(tag))
{
type = DBContent.TagValueItemTypes.Template;
}
else
{
type = DBContent.TagValueItemTypes.Form;
}
this.AddParameter("@itemType", (int)type);
this.AddParameter("@tagValue", tag.Value);
this.AddParameter("@tag", tag.TagType);
this.ExecuteNonQuery(sql);
}
trans.Commit();
}
return(true);
}
catch (Exception ex)
{
errormessage = ex.Message;
}
finally
{
this.CleanUp();
}
return(false);
}
public ReportItemBase GetReport(int id,
out string errormessage)
{
errormessage = "";
ConnectionItem connection = null;
FormItem form = null;
TemplateItem template = null;
List <SWBaseTag> formTags = new List <SWBaseTag>();
List <SWBaseTag> templateTags = new List <SWBaseTag>();
try
{
string sql = "SELECT fk_connection, fk_template, fk_form, [Report].name, [ReportGroup].name FROM [Report]";
sql += " LEFT JOIN [ReportGroup] ON [ReportGroup].id = [Report].fk_group";
sql += " WHERE [Report].ID = " + id;
_reader = this.ExecuteReader(sql);
int connectionID = -1;
int formID = -1;
int templateID = -1;
string reportName = "";
string groupName = "";
if (!_reader.HasRows)
{
_reader.Close();
errormessage = "Invalid report.";
return(null);
}
while (_reader.Read())
{
connectionID = int.Parse(_reader[0].ToString());
templateID = int.Parse(_reader[1].ToString());
if (_reader[2] != DBNull.Value)
{
formID = int.Parse(_reader[2].ToString());
}
reportName = _reader[3].ToString();
groupName = _reader[4].ToString();
break;
}
_reader.Close();
connection = new DBContent().GetConnection(connectionID);
if (connection == null)
{
errormessage = "Invalid connection.";
return(null);
}
form = new DBContent().GetForm(formID);
template = new DBContent().GetTemplate(templateID);
if (template == null)
{
errormessage = "Invalid template.";
return(null);
}
sql = "SELECT [TagValue].itemType, [Tag].type, [TagValue].name, [TagValue].value FROM [Tag]";
sql += " INNER JOIN [TagValue] ON [TagValue].fk_tag = [Tag].id";
sql += " WHERE [TagValue].fk_report = " + id;
_reader = this.ExecuteReader(sql);
while (_reader.Read())
{
int itemType = 0;
if (!int.TryParse(_reader[0].ToString(), out itemType))
{
continue;
}
string type = _reader[1].ToString().ToLower();
string name = _reader[2].ToString().ToLower();
string value = _reader[3].ToString();
SWBaseTag tag = SWBaseTag.GetTag(type);
tag.Name = name;
tag.Value = value;
if (tag == null)
{
continue;
}
switch (itemType)
{
case (int)DBContent.TagValueItemTypes.Form:
if (tag.BaseTagType == SWBaseTag.BaseTagTypes.Form ||
tag.BaseTagType == SWBaseTag.BaseTagTypes.Both)
{
formTags.Add(tag);
}
break;
case (int)DBContent.TagValueItemTypes.Template:
if (tag.BaseTagType == SWBaseTag.BaseTagTypes.Template ||
tag.BaseTagType == SWBaseTag.BaseTagTypes.Both)
{
templateTags.Add(tag);
}
break;
default:
continue;
}
}
ReportItemBase item = new ReportItemBase(id, reportName, groupName, connection, form, template);
foreach (SWBaseTag dbtag in formTags)
{
foreach (SWBaseTag itemTag in item.FormTags)
{
if (dbtag.Name.Equals(itemTag.Name) &&
dbtag.BaseTagType.Equals(itemTag.BaseTagType) &&
dbtag.TagType.Equals(itemTag.TagType))
{
itemTag.Value = dbtag.Value;
break;
}
}
}
foreach (SWBaseTag dbtag in templateTags)
{
foreach (SWBaseTag itemTag in item.TemplateTags)
{
if (dbtag.Name.Equals(itemTag.Name) &&
dbtag.BaseTagType.Equals(itemTag.BaseTagType) &&
dbtag.TagType.Equals(itemTag.TagType))
{
itemTag.Value = dbtag.Value;
break;
}
}
}
return(item);
}
finally
{
this.CleanUp();
}
}