/// <summary>
/// Ignores mappings, displays first 4 columns in a classic NavTable
/// or the first display column in a NavTree if a self-referential FK is present.
/// navtable includes edit and delete action buttons, second control - insert button
/// </summary>
/// <param name="tableName">string</param>
/// <returns>Panel</returns>
public Panel proposeSummaryPanel(string tableName)
{
DataColumnCollection cols = stats.ColumnTypes[tableName];
List <FK> FKs = stats.FKs[tableName];
// a table with more than one self-referential FK is improbable
List <FK> selfRefs = new List <FK>(from FK in FKs where FK.myTable == FK.refTable select FK as FK);
List <string> PKCols = stats.PKs[tableName];
FK selfRefFK = null;
// strict hierarchy structure validation - not nice => no Tree
if (hierarchies.Contains(tableName))
{
selfRefFK = selfRefs.First();
}
List <string> displayColOrder = stats.ColumnsToDisplay[tableName];
Control control;
DataTable controlTab = new DataTable();
List <Control> Controls = new List <Control>();
if (selfRefFK != null) // this will be a NavTree
{
Panel res = new Panel(tableName, 0, PanelTypes.NavTree, new List <Panel>(), new List <IField>(),
new List <Control>(), PKCols);
res.displayAccessRights = 1;
control = new TreeControl(0, new HierarchyNavTable(), PKCols[0], selfRefFK.myColumn,
displayColOrder[0], new List <UserAction> {
UserAction.View
});
Controls.Add(control);
control = new Control(0, null, PKCols, UserAction.Insert);
Controls.Add(control);
res.AddControls(Controls);
return(res);
}
else
{ // a simple NavTable
Panel res = new Panel(tableName, 0, PanelTypes.NavTable, new List <Panel>(), new List <IField>(),
new List <Control>(), PKCols);
res.displayAccessRights = 1;
List <UserAction> actions = new List <UserAction>(new UserAction[] { UserAction.View, UserAction.Delete });
List <string> displayColumns = displayColOrder.GetRange(0, Math.Min(displayColOrder.Count, 4));
List <FK> neededFKs = (from FK fk in FKs where displayColumns.Contains(fk.myColumn) select fk).ToList();
control = new NavTableControl(0, null, PKCols, neededFKs, actions);
control.displayColumns = displayColumns;
Controls.Add(control);
control = new Control(0, null, PKCols, UserAction.Insert);
Controls.Add(control);
res.AddControls(Controls);
return(res);
}
}
/// <summary>
/// fills "data" tables of NavTableControls
/// </summary>
/// <param name="ntc"></param>
/// <param name="schemaOnly"></param>
private void AssignDataForNavTable(NavTableControl ntc, bool schemaOnly)
{
List <string> toSelect = ntc.displayColumns.Union(ntc.PKColNames).ToList();
List <IDbCol> specSelect = new List <IDbCol>();
List <IDbCol> prefixedKeys = new List <IDbCol>();
List <string> neededTables = new List <string>();
// different FKs to the same table - JOIN colision prevention by giving each table an alias
Dictionary <string, int> countingForeignTableUse = new Dictionary <string, int>();
foreach (string col in toSelect)
{
FK correspondingFK = ntc.FKs.Where(x => x.myColumn == col).FirstOrDefault(); // can be at most one
if (correspondingFK is FK && ((FK)correspondingFK).refTable != ntc.panel.tableName) // dont join on itself
{
neededTables.Add(correspondingFK.refTable);
if (!countingForeignTableUse.ContainsKey(correspondingFK.refTable)) // don`t need an alias
{
specSelect.Add(dbe.Col(correspondingFK.refTable,
correspondingFK.displayColumn, col));
countingForeignTableUse[correspondingFK.refTable] = 1;
}
else
{ // use SALT + the count so that it is (hopefully) unique
countingForeignTableUse[correspondingFK.refTable]++;
specSelect.Add(dbe.Col(correspondingFK.refTable + Common.Constants.SALT + countingForeignTableUse[correspondingFK.refTable],
correspondingFK.displayColumn, col));
}
// we must provide the real column value (its a part of the PK) in another column
if (ntc.PKColNames.Contains(col) && correspondingFK is FK)
{
prefixedKeys.Add(dbe.Col(ntc.panel.tableName, col, CC.TABLE_COLUMN_REAL_VALUE_PREFIX + col));
}
}
else
{ // no FK, just use the original column name
specSelect.Add(dbe.Col(ntc.panel.tableName, col, col));
}
}
specSelect.AddRange(prefixedKeys);
FetchMethod fetchAccordingly; // so that we can use the same fetch-code for both Architect and Admin mode
if (schemaOnly)
{
fetchAccordingly = driver.fetchSchema;
}
else
{
fetchAccordingly = driver.fetchAll;
}
if (countingForeignTableUse.Values.Any(x => x > 1))
{
// FK table aliases
// not 100% solution - tables with suffix "1"...
List <IDbJoin> joins = new List <IDbJoin>();
ntc.FKs.Reverse(); // the counter counts down when creating joins in reverse order
foreach (FK fk in ntc.FKs)
{
string alias = fk.refTable +
(countingForeignTableUse[fk.refTable] > 1 ?
Common.Constants.SALT + (countingForeignTableUse[fk.refTable]--).ToString() : "");
joins.Add(dbe.Join(fk, alias));
}
ntc.data = fetchAccordingly("SELECT ", dbe.Cols(specSelect), " FROM ", dbe.Table(ntc.panel.tableName), dbe.Joins(joins));
}
else
{
ntc.data = fetchAccordingly("SELECT ", dbe.Cols(specSelect), " FROM ", dbe.Table(ntc.panel.tableName),
dbe.Joins(ntc.FKs.Where(x => x.refTable != x.myTable).ToList <FK>()));
}
}
protected void SaveButton_Click(object sender, EventArgs e)
{
string panelName = PanelName.Text;
List<string> displayCols = DisplayCols.RetrieveStringData();
List<UserAction> actions = new List<UserAction>();
foreach(string s in actionsControl.RetrieveStringData()){
actions.Add((UserAction)Enum.Parse(typeof(UserAction), s));
}
ValidationResult.Items.Clear();
// validate the proposal
if (panelName == ".")
{
ValidationResult.Items.Add("Give the pannel a name, please.");
}
else if (displayCols.Count == 0) {
ValidationResult.Items.Add("Select at leas one column to display");
}
else if (actions.Count == 0)
{
ValidationResult.Items.Add("Check at least one action users can perform in thie panel, please");
}
else {
ValidationResult.Items.Add("Valid");
// => create the panel and save it
_min.Models.Control c;
List<_min.Models.Control> controls = new List<_min.Models.Control>();
_min.Models.Control insertButton = null;
// insert is a separate button
if (actions.Contains(UserAction.Insert))
{
insertButton = new _min.Models.Control(actPanel.panelId, "Insert", UserAction.Insert);
actions.Remove(UserAction.Insert);
}
// it is a NavTable
if (NavControlType.SelectedValue.EndsWith("Table"))
{
List<FK> neededFKs = (from FK fk in FKs where displayCols.Contains(fk.myColumn) select fk).ToList<FK>();
c = new NavTableControl(actPanel.panelId, new System.Data.DataTable(), mm.Stats.PKs[actPanel.tableName],
neededFKs, actions);
c.displayColumns = displayCols;
}
else { // NavTree
actions.Remove(UserAction.Delete); // cannot use delete in NavTrees
c = new TreeControl(actPanel.panelId, new HierarchyNavTable(), mm.Stats.PKs[actPanel.tableName][0],
hierarchy.myColumn, displayCols[0], actions);
}
controls.Add(c);
if (insertButton != null)
controls.Add(insertButton);
foreach (_min.Models.Control listedControl in controls) {
listedControl.targetPanelId = actPanel.controls[0].targetPanelId;
}
MPanel resPanel = new MPanel(actPanel.tableName, actPanel.panelId,
c is TreeControl ? PanelTypes.NavTree : PanelTypes.NavTable, new List<MPanel>(),
new List<IField>(), controls, actPanel.PKColNames, null, actPanel.parent);
resPanel.panelName = panelName;
actPanel = resPanel;
mm.SysDriver.BeginTransaction();
mm.SysDriver.UpdatePanel(actPanel);
mm.SysDriver.CommitTransaction();
mm.SysDriver.IncreaseVersionNumber();
ValidationResult.Items.Add("Saved");
Response.Redirect(Page.Request.RawUrl);
}
}
/// <summary>
/// fills "data" tables of NavTableControls
/// </summary>
/// <param name="ntc"></param>
/// <param name="schemaOnly"></param>
private void AssignDataForNavTable(NavTableControl ntc, bool schemaOnly)
{
List<string> toSelect = ntc.displayColumns.Union(ntc.PKColNames).ToList();
List<IDbCol> specSelect = new List<IDbCol>();
List<IDbCol> prefixedKeys = new List<IDbCol>();
List<string> neededTables = new List<string>();
// different FKs to the same table - JOIN colision prevention by giving each table an alias
Dictionary<string, int> countingForeignTableUse = new Dictionary<string, int>();
foreach (string col in toSelect)
{
FK correspondingFK = ntc.FKs.Where(x => x.myColumn == col).FirstOrDefault(); // can be at most one
if (correspondingFK is FK && ((FK)correspondingFK).refTable != ntc.panel.tableName) // dont join on itself
{
neededTables.Add(correspondingFK.refTable);
if (!countingForeignTableUse.ContainsKey(correspondingFK.refTable)) // don`t need an alias
{
specSelect.Add(dbe.Col(correspondingFK.refTable,
correspondingFK.displayColumn, col));
countingForeignTableUse[correspondingFK.refTable] = 1;
}
else
{ // use SALT + the count so that it is (hopefully) unique
countingForeignTableUse[correspondingFK.refTable]++;
specSelect.Add(dbe.Col(correspondingFK.refTable + Common.Constants.SALT + countingForeignTableUse[correspondingFK.refTable],
correspondingFK.displayColumn, col));
}
// we must provide the real column value (its a part of the PK) in another column
if (ntc.PKColNames.Contains(col) && correspondingFK is FK) {
prefixedKeys.Add(dbe.Col(ntc.panel.tableName, col, CC.TABLE_COLUMN_REAL_VALUE_PREFIX + col));
}
}
else
{ // no FK, just use the original column name
specSelect.Add(dbe.Col(ntc.panel.tableName, col, col));
}
}
specSelect.AddRange(prefixedKeys);
FetchMethod fetchAccordingly; // so that we can use the same fetch-code for both Architect and Admin mode
if (schemaOnly)
fetchAccordingly = driver.fetchSchema;
else
fetchAccordingly = driver.fetchAll;
if (countingForeignTableUse.Values.Any(x => x > 1))
{
// FK table aliases
// not 100% solution - tables with suffix "1"...
List<IDbJoin> joins = new List<IDbJoin>();
ntc.FKs.Reverse(); // the counter counts down when creating joins in reverse order
foreach (FK fk in ntc.FKs)
{
string alias = fk.refTable +
(countingForeignTableUse[fk.refTable] > 1 ?
Common.Constants.SALT + (countingForeignTableUse[fk.refTable]--).ToString() : "");
joins.Add(dbe.Join(fk, alias));
}
ntc.data = fetchAccordingly("SELECT ", dbe.Cols(specSelect), " FROM ", dbe.Table(ntc.panel.tableName), dbe.Joins(joins));
}
else
{
ntc.data = fetchAccordingly("SELECT ", dbe.Cols(specSelect), " FROM ", dbe.Table(ntc.panel.tableName),
dbe.Joins(ntc.FKs.Where(x => x.refTable != x.myTable).ToList<FK>()));
}
}
/// <summary>
/// Ignores mappings, displays first 4 columns in a classic NavTable
/// or the first display column in a NavTree if a self-referential FK is present.
/// navtable includes edit and delete action buttons, second control - insert button
/// </summary>
/// <param name="tableName">string</param>
/// <returns>Panel</returns>
public Panel proposeSummaryPanel(string tableName)
{
DataColumnCollection cols = stats.ColumnTypes[tableName];
List<FK> FKs = stats.FKs[tableName];
// a table with more than one self-referential FK is improbable
List<FK> selfRefs = new List<FK>(from FK in FKs where FK.myTable == FK.refTable select FK as FK);
List<string> PKCols = stats.PKs[tableName];
FK selfRefFK = null;
// strict hierarchy structure validation - not nice => no Tree
if (hierarchies.Contains(tableName))
selfRefFK = selfRefs.First();
List<string> displayColOrder = stats.ColumnsToDisplay[tableName];
Control control;
DataTable controlTab = new DataTable();
List<Control> Controls = new List<Control>();
if (selfRefFK != null) // this will be a NavTree
{
Panel res = new Panel(tableName, 0, PanelTypes.NavTree, new List<Panel>(), new List<IField>(),
new List<Control>(), PKCols);
res.displayAccessRights = 1;
control = new TreeControl(0, new HierarchyNavTable(), PKCols[0], selfRefFK.myColumn,
displayColOrder[0], new List<UserAction> { UserAction.View });
Controls.Add(control);
control = new Control(0, null, PKCols, UserAction.Insert);
Controls.Add(control);
res.AddControls(Controls);
return res;
}
else
{ // a simple NavTable
Panel res = new Panel(tableName, 0, PanelTypes.NavTable, new List<Panel>(), new List<IField>(),
new List<Control>(), PKCols);
res.displayAccessRights = 1;
List<UserAction> actions = new List<UserAction>(new UserAction[] { UserAction.View, UserAction.Delete });
List<string> displayColumns = displayColOrder.GetRange(0, Math.Min(displayColOrder.Count, 4));
List<FK> neededFKs = (from FK fk in FKs where displayColumns.Contains(fk.myColumn) select fk).ToList();
control = new NavTableControl(0, null, PKCols, neededFKs, actions);
control.displayColumns = displayColumns;
Controls.Add(control);
control = new Control(0, null, PKCols, UserAction.Insert);
Controls.Add(control);
res.AddControls(Controls);
return res;
}
}