Introduction
In the IT office of the company I work for, we realize the management software with which the operational area manages its business. Our customers trust us to work files through call center. The result of these processes leads to the acquisition of information which will then be reported back to the customers.
I was immediately able to verify that the amount of information of customer interest was evolving, it was often asking us to add a binary information, that is a question that could be answered "yes" or "no" or small descriptive information entity.
In this scenario, there is the problem for this information management. Initially, it was saved in a table which collected most of the data received in the input and all the information retrieved during processing, to be returned as output. This table was very heterogeneous, there was registered information of different activities so, according to the request processing, many columns remained unused.
Another reason for the inefficiency was given by the fact that at each request to add the information, also the "flag" type, you had to add a column in the above table, with the result of having an "uncontrolled" growth of the structure and a substantial commitment to the modification of software to manage an additional column.
To solve these problems, I thought to turn the rescue of this information from a horizontal to a vertical structure. I introduced in the database a new structure through which any information requested is considered an attribute applied or not applied to the processing of a file.
Database
The database pattern consists of a table in which the attributes are defined, a table which includes the attributes by category, the types of processing and which categories of attributes are part of it. Other tables are used for the configuration for categories exclusions and exclusions between attributes, and in the end, the real processes associated to the files.
Initially, the database schema provides for a single table which has all the fields related to all kind of files:
The database schema after the restructuring is shown below:
Results
The structure is dynamic and highly configurable. We define the various types of operations for each operation are established the available attributes. The attributes are part of containers called "Categories". You can establish prerequisites and exclusions between categories and between attributes. For example, by selecting the attribute "Paid Invoice" of "Payment" category does not make sense to have available the attributes of the category "Debt", so then the entire category will be hidden. Another case is the exclusion of an attribute incompatible with another. For example, in the category "payment" if the attribute "Paid" is selected, then will disappear attributes "will pay" and "not will pay" and vice versa.
With this solution, to meet the request to add an information to census during processing of a file, we could set up just few tables in the system by inserting some records. The new attribute will be immediately available.
A demo can be seen at: Processing of a file
Using the Code
The main work is done by Web User Control, on the webpage that shows the operation, and the trapdoor called by the WUC. The control works entirely in jQuery. It calls functionality exposed by the trapdoor to add or remove the selected attributes. According to the selection made, the trapdoor returns the list of valid attributes with respect to the exclusion configurations set in the system.
AttributesProcessingWUC
<script>
$(document).ready(function () {
SendRequesProcessings("Initialize", $("#hfidlManager").val(), null, null);
});
function Initialize() {
var activeIndex = parseInt($("#hidAccordionIndex").val());
$("#catalog").accordion({
autoHeight: false,
event: "mousedown",
active: activeIndex,
collapsible: true,
activate: function (event, ui) {
var index = $(this).accordion("option", "active");
$("#hidAccordionIndex").val(index);
}
});
$(".draggable").draggable({
helper: "clone",
cursor: "-webkit-grabbing",
revert: "invalid"
});
$(".droppable").droppable({
drop: function (event, ui) {
window.setTimeout(function () {
handleDropEvent(event, ui);
}, 10);
}
});
$("#ProcTrash").droppable({
accept: '.AttrSel',
tolerance: 'touch',
drop: function (event, ui) {
window.setTimeout(function () {
handleDropOutEvent(event, ui);
}, 10);
}
});
$("#PnlAddAttribute").dialog({
autoOpen: false,
width: 375,
height: 175,
modal: true,
resizable: false,
closeOnEscape: false,
open: function (event, ui) {
$(".ui-dialog-titlebar-close").hide();
},
buttons: {
Salva: function () {
SendRequesProcessings("AddAttribute", $("#hfidlManager").val(), $("#hfIdAttrSel").val(), $("#tbAttributeValue").val());
$.event.trigger('SelectedAttributeDropped', [{ idAttribute: $("#hfIdAttrSel").val(), AttributeValue: $("#tbAttributeValue").val() }]);
$("#tbAttributeValue").val("");
$(this).dialog("close");
},
Annulla: function () {
$(this).dialog("close");
return false;
}
}
});
}
function handleDropEvent(event, ui) {
$("#hfIdAttrSel").val(ui.draggable.attr('idAttribute'));
if (ui.draggable.attr('hasvalue') == 'true') {
$("#ProcAttributeSel").html(ui.draggable.attr('ValueDescription'));
$("#PnlAddAttribute").dialog("open");
}
else {
SendRequesProcessings("AddAttribute", $("#hfidlManager").val(), $("#hfIdAttrSel").val(), null);
$.event.trigger('SelectedAttributeDropped', [{ idAttribute: $("#hfIdAttrSel").val(), AttributeValue: null }]);
}
}
function handleDropOutEvent(event, ui) {
SendRequesProcessings("RemoveAttribute", $("#hfidlManager").val(), ui.draggable.attr('idAttribute'), null);
$.event.trigger('RemovedAttributeDropOut', [{ idAttribute: ui.draggable.attr('idAttribute') }]);
}
function AddCategoryExcluded(idCategory) {
SendRequesProcessings("AddCategoryExcluded", $("#hfidlManager").val(), idCategory, null);
}
function RemoveCategoryExcluded(idCategory) {
SendRequesProcessings("RemoveCategoryExcluded", $("#hfidlManager").val(), idCategory, null);
}
function ClearCategoryExcluded() {
SendRequesProcessings("ClearCategoryExcluded", $("#hfidlManager").val(), null, null);
}
function AddAttributeExcluded(idAttribute) {
SendRequesProcessings("AddAttributeExcluded", $("#hfidlManager").val(), idAttribute, null);
}
function RemoveAttributeExcluded(idAttribute) {
SendRequesProcessings("RemoveAttributeExcluded", $("#hfidlManager").val(), idAttribute, null);
}
function ClearAttributesExcluded() {
SendRequesProcessings("ClearAttributesExcluded", $("#hfidlManager").val(), null, null);
}
function SendRequesProcessings(command, idlManager, idAttribute, AttributeValue) {
var options = {
error: function (msg) {
App.alert({ type: 'danger', icon: 'warning', message: msg.d, place: 'append', closeInSeconds: 5 });
},
type: "POST",
url: "/TrapDoor/FileProcessing/FileProcessingHandler.ashx",
data: "idlManager=" + idlManager + "&command=" + command + "&idAttribute=" + idAttribute + "&AttributeValue=" + AttributeValue,
contentType: "application/json; charset=utf-8",
dataType: "json",
async: false,
success: function (response) {
bindAttributes(response);
}
};
jQuery.ajax(options);
}
function f_checkEnablingFileProc() {
return $('#hfIdAttrSel').val() != "";
}
function bindAttributes(jSonRes) {
$("#hfidlManager").val(jSonRes.idlManager);
var categories = jSonRes.ValidAttributes.AttributesCategories;
$("#CategoriesCurtainConteiner").empty();
$("#CategoriesCurtainConteiner").append("<div id='catalog'>");
$.each(categories, function (idx, obj) {
$("#catalog").append("<h2><a href='#'>" + obj.Description + "</a></h2>");
$("#catalog").append("<div id='divCatAcc" + idx + "' style='overflow: hidden; position: initial;'>");
$("#divCatAcc" + idx).append("<ul id='ulAttr" + idx + "' class='ulCursorClass' >");
$.each(obj.Attributes, function (idxAttr, objAttr) {
$("#ulAttr" + idx).append("<li class='draggable' z-index='10' id='liAttribute" + idx + idxAttr + "' idAttribute='" + objAttr.idAttribute + "' hasvalue='" + objAttr.HasValue + "' ValueDescription='" + objAttr.ValueDescription + "'>" + objAttr.Description + "</li>");
})
});
var SelectedAttributes = jSonRes.AttributesProcessing.SelectedAttributes;
$("#attrSelectedDiv").empty();
$("#attrSelectedDiv").append("<ul id='ulAttrSel' class='droppable ulCursorClass'>");
$.each(SelectedAttributes, function (idxAttrSel, objAttrSel) {
$("#ulAttrSel").append("<li class='draggable AttrSel' id='liAttributeSel" + idxAttrSel + "' idAttribute='" + objAttrSel.idAttribute + "'>" + objAttrSel.Description + "</li>");
});
Initialize();
}
</script>
TrapDoor
public class FileProcessingHandler : IHttpHandler, System.Web.SessionState.IRequiresSessionState
{
FileProcessingManager lManager;
private class jsonResult
{
public int idlManager { get; set; }
public CategoriesAttributes ValidAttributes { get; set; }
public AttributesProc AttributesProcessing { get; set; }
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "application/json; charset=utf-8";
context.Request.InputStream.Position = 0;
NameValueCollection dataPOST = null;
int idlManager = 0;
int idAttribute;
string AttributeValue;
string command;
bool result;
try
{
using (var inputStream = new StreamReader(context.Request.InputStream))
{
dataPOST = HttpUtility.ParseQueryString(HttpUtility.HtmlDecode(inputStream.ReadToEnd().Replace(""", "\\"")));
}
if (dataPOST != null)
{
result = Int32.TryParse(dataPOST["idlManager"], out idlManager);
command = dataPOST["command"];
result = Int32.TryParse(dataPOST["idAttribute"], out idAttribute);
AttributeValue = dataPOST["AttributeValue"];
lManager = (FileProcessingManager)context.Session["lManager"];
if (lManager == null)
lManager = new FileProcessingManager(-1, processTypes.DefaultedInvoice);
switch (command)
{
case "Initialize":
break;
case "AddAttribute":
AddAttribute(idAttribute, AttributeValue);
break;
case "RemoveAttribute":
RemoveAttribute(idAttribute);
break;
case "AddCategoryExcluded":
AddCategoryExcluded(idAttribute);
break;
case "RemoveCategoryExcluded":
RemoveCategoryExcluded(idAttribute);
break;
case "ClearCategoryExcluded":
ClearCategoryExcluded();
break;
case "AddAttributeExcluded":
AddAttributeExcluded(idAttribute);
break;
case "RemoveAttributeExcluded":
RemoveAttributeExcluded(idAttribute);
break;
case "ClearAttributesExcluded":
ClearAttributesExcluded();
break;
default:
break;
}
AttributesProc attributes = lManager.GetSelectedAttributes();
if (attributes.SelectedAttributes.Count <= 0)
{
SelectedAttribute att = new SelectedAttribute();
att.idAttribute = -1;
att.Value = null;
lManager.AddProcessingAttribute(att);
}
context.Session["lManager"] = lManager;
jsonResult JsonResult = new jsonResult();
JsonResult.idlManager = idlManager;
JsonResult.AttributesProcessing = lManager.GetSelectedAttributes();
JsonResult.ValidAttributes = lManager.GetValidAttributes();
context.Response.Write(JsonConvert.SerializeObject(JsonResult));
}
}
catch (Exception)
{
throw;
}
}
FileProcessingManager
public AttributesProc GetSelectedAttributes()
{
DataTable ProcessAttr = DSProcessing.Tables["AttributesProcessing"];
DataTable Attributes = DSProcessing.Tables["Attributes"];
DataTable Categories = DSProcessing.Tables["AttributesCategory"];
var SelectedAttributes =
from procAt in ProcessAttr.AsEnumerable()
join attsel in Attributes.AsEnumerable()
on procAt.Field<int>("Attribute") equals attsel.Field<int>("idAttribute")
join category in Categories.AsEnumerable() on attsel.Field<int>("Category") equals category.Field<int>("idCategory")
select new
{
idAttribute = attsel.Field<int>("idAttribute"),
DescriptionAttribute = attsel.Field<string>("Description"),
CategoryDescription = category.Field<string>("Description"),
HasValue = attsel.Field<bool>("HasValue"),
Value = procAt.Field<string>("Value")
};
AttributesProc result = new AttributesProc();
foreach (var AttributeSel in SelectedAttributes)
{
SelectedAttribute att = new SelectedAttribute();
att.CategoryDescription = AttributeSel.CategoryDescription;
att.idAttribute = AttributeSel.idAttribute;
att.Description = AttributeSel.CategoryDescription + "\\" + AttributeSel.DescriptionAttribute + (AttributeSel.HasValue ? "(" + AttributeSel.Value + ")" : null);
att.HasValue = AttributeSel.HasValue;
att.Value = AttributeSel.Value;
result.SelectedAttributes.Add(att);
}
return result;
}
public CategoriesAttributes GetValidAttributes()
{
DataTable Attributes = DSProcessing.Tables["Attributes"];
DataTable AttributesCategory = DSProcessing.Tables["AttributesCategory"];
DataTable AttrProcessing = DSProcessing.Tables["AttributesProcessing"];
DataTable CategoriesExclusions = DSProcessing.Tables["CategoriesExclusions"];
DataTable AttributesExclusions = DSProcessing.Tables["AttributesExclusions"];
var ValidAttributes =
from attributes in Attributes.AsEnumerable()
join category in AttributesCategory.AsEnumerable() on attributes.Field<int>("Category") equals category.Field<int>("idCategory")
where !(from pat in AttrProcessing.AsEnumerable()
join attsel in Attributes.AsEnumerable()
on pat.Field<int>("Attribute") equals attsel.Field<int>("idAttribute")
join escat in CategoriesExclusions.AsEnumerable()
on attsel.Field<int>("Category") equals escat.Field<int>("Category")
select escat.Field<int>("CategoryExcluded")
).Contains(attributes.Field<int>("Category"))
&& !(from cat_web in this.CategoriesExclusionRequest.CategoryExcluded
select cat_web.idCategory
).Contains(attributes.Field<int>("Category"))
&& !(from pat2 in AttrProcessing.AsEnumerable()
join esat in AttributesExclusions.AsEnumerable() on pat2.Field<int>("Attribute") equals esat.Field<int>("Attribute")
select esat.Field<int>("AttributeExcluded")
).Contains(attributes.Field<int>("idAttribute"))
&& !(from pat3 in AttrProcessing.AsEnumerable()
select pat3.Field<int>("Attribute")
).Contains(attributes.Field<int>("idAttribute"))
&& !(from atr_web in this.AttributesExclusionRequest.AttributesExcluded
select atr_web.idAttribute
).Contains(attributes.Field<int>("idAttribute"))
orderby category.Field<int>("Order"), attributes.Field<int>("idAttribute")
select new
{
CategoryId = attributes.Field<int>("Category"),
DescriptionCategory = category.Field<string>("Description"),
idAttribute = attributes.Field<int>("idAttribute"),
DescriptionAttribute = attributes.Field<string>("Description"),
HasValue = attributes.Field<bool>("HasValue"),
ValueDescription = attributes.Field<string>("ValueDescription")
};
CategoriesAttributes result = new CategoriesAttributes();
int idCategoryAtt = -100;
AttributesCategory Category = null;
foreach (var attributesOk in ValidAttributes)
{
if (attributesOk.CategoryId != idCategoryAtt)
{
Category = new AttributesCategory();
Category.idCategory = attributesOk.CategoryId;
Category.Description = attributesOk.DescriptionCategory;
result.AttributesCategories.Add(Category);
}
idCategoryAtt = attributesOk.CategoryId;
Attribute att = new Attribute();
att.idCategory = attributesOk.CategoryId;
att.idAttribute = attributesOk.idAttribute;
att.Description = attributesOk.DescriptionAttribute;
att.HasValue = attributesOk.HasValue;
att.ValueDescription = attributesOk.ValueDescription;
Category.Attributes.Add(att);
}
return result;
}
