I'm attempting to store and manage some relational data within Kentico's (Kentico 10) database. I'm having trouble fitting this into Kentico's best practices.
I have the following data:
1. Company - a list of companies
2. PostalCode - a list of international postal codes, their country and their lat/long
3. CompanyPostalCodes - many to many table of postal codes served by each company AND a Boolean flag that describes how the company serves that postal code (The postal codes are service / delivery areas)
I've read the documentation on creating custom modules
and have successfully created a module, classes and UI to manage companies and postal codes.
I've also read through documentation on creating a binding class
and have created one. (I've added the aforementioned Boolean field there too.)
I am stuck at trying to create a UI to manage the many to many relationship between them. Ideally, I would have a way to select multiple postal codes (and key in postal codes that do not yet exist in the PostalCode table) from the Edit Company page. I would also need to be able to set the flag field for each postal code on a given company's edit page. (If this is overly complicated, I could have a tab for postal codes with the flag and another tab for those without.) But I am open to any suggestion on how to manage the relationship in the UI.
Any suggestions?
If your binding object is more than just a reference to two tables (it has fields) then here are a couple options.
First make sure your edit in the company is the ui page type vertical listing, and under that add a "general" page of type edit object, along with your binding UI. Then...
Use the binding UI template, but extend the ui and adjust the listing to point to a custom unigrid xml that has an edit so you can not only add bindings but edit the object.
Do #1 except modify the unigrid to have the extra column for the check box, alter that columns rendering so it returns a check box object (the flag check box) and add a save button to the header (all doable through a ui extender) that loops through the items and saves the company postal with check box)
Have a separate object listing UI page that is an object listing on the binding object, so you have one to create the bindings, another to edit them. Note sometimes you can't select the object because it's a binding object in the properties and you need to click the black arrow next to the drop down and manually type in the class.
Create a completely custom object listing or an extended unigrid and do it however you want.
I've done all 4 personally, #2 is probably the most complex but smoothest management. If your want to pick one you want to go with I can give some samples!
------------------- EDIT -------------------
On second look, #2 you can't really extend the save functionality, i had to go a custom route (#4) to have a UniGrid that had changable fields that could be retrieved on save.
Here's the Code
// ASCX
<!-- DO NOT Arranged or add to the columns without adjusting the back end code, as it references by index -->
<div class="GridContent">
<cms:UniGrid EnableTheming="true" ShowActionsMenu="false" ShowActionsLabel="false" ShowExportMenu="false" ShowObjectMenu="false" runat="server" ID="gridSizes" OrderBy="enabled desc, SizeOrder" AllColumns="SizeID, Enabled, SizeDisplayName, PriceAdjustment, Upcharge, VendorUpcharge" ApplyPageSize="false" PageSize="##ALL##">
<GridActions Enabled="false" />
<GridColumns>
<ug:Column runat="server" Source="SizeID" Caption="SizeID" CssClass="hidden" AllowSorting="false" />
<ug:Column runat="server" Source="Enabled" Caption="Enabled" ExternalSourceName="Enabled" AllowSorting="false" />
<ug:Column runat="server" Source="SizeDisplayName" CssClass="DisplayName" Caption="Size Display Name" AllowSorting="false" />
<ug:Column runat="server" Source="Upcharge" Caption="Upcharge" ExternalSourceName="Upcharge" AllowSorting="false" />
<ug:Column runat="server" Source="VendorUpcharge" Caption="Vendor Upcharge" ExternalSourceName="VendorUpcharge" AllowSorting="false" />
</GridColumns>
<PagerConfig ShowPageSize="false" ShowDirectPageControl="false" PageSizeOptions="##ALL##" runat="server" Visible="false" />
</cms:UniGrid>
</div>
<cms:FormSubmitButton runat="server" ID="btnSaveItems" OnClick="btnSave_Click" />
// Code Behind
private void SetSizesTable()
{
QueryDataParameters parameters = new QueryDataParameters();
parameters.Add("@SkuID", GetSkuID());
int totalRecords = 0;
DataSet ds = YourDataCallHere;
gridSizes.DataSource = ds;
gridSizes.OnExternalDataBound += GridSizes_OnExternalDataBound;
gridSizes.DataBind();
}
private object GridSizes_OnExternalDataBound(object sender, string sourceName, object parameter)
{
// Replace the Enabled and Upcharge with actual controls, this way the user can adjust them
// and then those values retrieved when saved.
switch(sourceName.ToLower())
{
case "enabled":
var enabled = ValidationHelper.GetBoolean(parameter, false);
CheckBox cbxEnabled = new CheckBox();
cbxEnabled.Checked = enabled;
cbxEnabled.TabIndex = 100;
return cbxEnabled;
case "upcharge":
case "vendorupcharge":
var price = ValidationHelper.GetDecimal(parameter, 0);
CMSTextBox txtBox = new CMSTextBox();
txtBox.Text = price.ToString("F2");
txtBox.TabIndex = 1000;
return txtBox;
default:
return parameter;
}
}
// Save logic here
protected void btnSave_Click(object sender, EventArgs e)
{
// Loop through the actual control rows so we can retrieve the values and update.
ControlFinder<GridViewRow> GridViewRowFinder = new ControlFinder<GridViewRow>();
GridViewRowFinder.FindChildControlsRecursive(gridSizes);
bool ErrorOccurred = false;
// Skip the first and last as they are the header / action rows
foreach (GridViewRow RowItem in GridViewRowFinder.FoundControls.Skip(1).Take(GridViewRowFinder.FoundControls.Count()-2))
{
try
{
// Retrieve the values from the controls. These are based on the Cell index so any modification to the
// UniGrid may break this and need updating!
int SizeID = ValidationHelper.GetInteger(((LiteralControl)RowItem.Cells[1].Controls[0]).Text, -1);
bool isChecked = ((CheckBox)RowItem.Cells[2].Controls[0]).Checked;
decimal Upcharge = ValidationHelper.GetDecimal(((CMSTextBox)RowItem.Cells[4].Controls[0]).Text, 0);
decimal VendorUpcharge = ValidationHelper.GetDecimal(((CMSTextBox)RowItem.Cells[5].Controls[0]).Text, 0);
if (Upcharge > 0 || VendorUpcharge > 0)
{
isChecked = true;
}
// Grab any existing Sku Size
var ExistingSkuSize = SkuSizeInfoProvider.GetSkuSizeInfo(GetSkuID(), SizeID);
// Update the Sku Size
if (!isChecked && ExistingSkuSize != null)
{
// Delete existing since unchecked
ExistingSkuSize.Delete();
}
else if (isChecked && ExistingSkuSize == null)
{
// Create new one since it does not exist
SkuSizeInfo newSkuSize = new SkuSizeInfo();
newSkuSize.SkuID = GetSkuID();
newSkuSize.SizeID = SizeID;
newSkuSize.Upcharge = Upcharge;
newSkuSize.VendorUpcharge = VendorUpcharge;
newSkuSize.SkuSizeGuid = Guid.NewGuid();
newSkuSize.SkuSizeLastModified = DateTime.Now;
newSkuSize.Insert();
}
else if (isChecked && (ExistingSkuSize.Upcharge != Upcharge || ExistingSkuSize.VendorUpcharge != VendorUpcharge))
{
// Just update the upcharge
ExistingSkuSize.Upcharge = Upcharge;
ExistingSkuSize.VendorUpcharge = VendorUpcharge;
ExistingSkuSize.Update();
}
} catch(Exception ex)
{
ErrorOccurred = true;
EventLogProvider.LogException("ProductSizes", "UPDATEERROR", ex, additionalMessage: string.Format("Unable to Set/Update the Sku Size for SkuID {0}, this could be because the grid was altered.", GetSkuID()));
}
}
if(ErrorOccurred)
{
AddWarning("An error occured on some items while saving, please check the Event Log.");
} else
{
AddConfirmation("Product Sizes Updated.");
// Force refresh as otherwise it messes up
URLHelper.Redirect(HttpContext.Current.Request.Url.PathAndQuery);
}
}
}