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(); } }