public static ColumnDef ColumnDef(int pageTemplateId, string columnName)
{
if (pageTemplateId == 0)
{
return(null);
}
ColumnDef columnDef = SessionService.ColumnDefs(pageTemplateId).Where(w => w.ColumnName == columnName).FirstOrDefault();
return(columnDef);
}
public static ColumnDef ColumnDef(int columnDefId)
{
if (SessionService.IsLocal)
{
HttpContext.Current.Session["sec.ColumnDefId" + columnDefId] = null; //xxx
}
if (HttpContext.Current.Session["sec.ColumnDefId" + columnDefId] == null)
{
var pageTemplateId = DataService.GetIntValue("SELECT PageTemplateId FROM ColumnDef WHERE ColumnDefId = " + columnDefId);
var columnDef = SessionService.ColumnDefs(pageTemplateId).Where(w => w.ColumnDefId == columnDefId).FirstOrDefault();
HttpContext.Current.Session["sec.ColumnDefId" + columnDefId] = columnDef;
}
return((ColumnDef)HttpContext.Current.Session["sec.ColumnDefId" + columnDefId]);
}
public static void ClearPageTemplateSessions(int pageTemplateId)
{
HttpContext.Current.Session["sec.GridColumns" + pageTemplateId] = null;
HttpContext.Current.Session["sec.GridSchema" + pageTemplateId] = null;
HttpContext.Current.Session["sec.PageTemplate" + pageTemplateId] = null;
HttpContext.Current.Session["sec.FormLayout" + pageTemplateId] = null;
HttpContext.Current.Session["sec.TableFilterSort" + pageTemplateId] = null;
HttpContext.Current.Session["sec.ColumnDefs" + pageTemplateId] = null;
var items = SessionService.ColumnDefs(pageTemplateId);
foreach (var item in items)
{
HttpContext.Current.Session["sec.ColumnDefId" + item.ColumnDefId] = null;
}
}
public static TableFilterSort TableFilterSort(int pageTemplateId)
{
if (SessionService.IsLocal)
{
HttpContext.Current.Session["sec.TableFilterSort" + pageTemplateId] = null; //xxx
}
if (HttpContext.Current.Session["sec.TableFilterSort" + pageTemplateId] == null)
{
StringBuilder GridColumns = new StringBuilder();
StringBuilder SortColumns = new StringBuilder();
StringBuilder InnerSelect = new StringBuilder();
StringBuilder OuterSelect = new StringBuilder();
StringBuilder InnerJoin = new StringBuilder();
StringBuilder StandardSelect = new StringBuilder();
Dictionary <string, string> FilterMap = new Dictionary <string, string>();
Dictionary <string, string> Sort1Map = new Dictionary <string, string>();
Dictionary <string, string> Sort2Map = new Dictionary <string, string>();
TableFilterSort tfs = new TableFilterSort();
var tableName = TableName(pageTemplateId);
if (tableName.Length == 0)
{
return(tfs);
}
var primaryKey = PrimaryKey(pageTemplateId);
GridColumns.Append(tableName + "." + primaryKey);
InnerSelect.Append(tableName + "." + primaryKey);
OuterSelect.Append(tableName + "." + primaryKey);
var columnDefs = SessionService.ColumnDefs(pageTemplateId);
var referenceIndex = 0;
using (SourceControlEntities Db = new SourceControlEntities())
{
var pageTemplate = SessionService.PageTemplate(pageTemplateId);
// get GridColumns
var gridColumns = Db.GridColumns.Where(w => w.PageTemplateId == pageTemplateId).OrderBy(o => o.SortOrder);
int[] columnDefIds = gridColumns.Select(s => s.ColumnDefId).ToArray();
var gridColumnDefs = columnDefs.Where(w => columnDefIds.Contains(w.ColumnDefId) && !(bool)w.IsPrimary);
foreach (var columnDef in gridColumnDefs)
{
referenceIndex++;
if (columnDef.ElementType == "DropdownCustomOption")
{
FilterMap.Add(columnDef.ColumnName + "_lco", "CustomOption" + referenceIndex + ".OptionText");
GridColumns.Append(string.Format(",{0}_lco", columnDef.ColumnName));
InnerSelect.Append(string.Format(", {0} AS {1}_lco ", "CustomOption" + referenceIndex + ".OptionText", columnDef.ColumnName));
InnerJoin.Append(string.Format(" LEFT JOIN {0} ON {1}.{2} = {3}.{4} AND {5}.ColumnDefId = {6}", "CustomOption CustomOption" + referenceIndex, tableName, columnDef.ColumnName, "CustomOption" + referenceIndex, "OptionValue", "CustomOption" + referenceIndex, columnDef.ColumnDefId));
OuterSelect.Append(string.Format(",MAIN.{0}_lco", columnDef.ColumnName));
// set sort
Sort1Map.Add(columnDef.ColumnName + "_lco", "CustomOption" + referenceIndex + ".OptionText");
Sort2Map.Add(columnDef.ColumnName + "_lco", "MAIN." + columnDef.ColumnName + "_lco");
}
else if (columnDef.LookupTable.Length > 0 && columnDef.TextField.Length > 0 && columnDef.ValueField.Length > 0 && columnDef.ElementType == "DropdownSimple")
{
string lookUpField = "";
string lookUpField_ = "";
string fieldOnly = "";
if (columnDef.TextField.Contains(","))
{
string[] fields = columnDef.TextField.Split(new char[] { ',' });
lookUpField = "ISNULL(" + columnDef.LookupTable + "." + fields[0] + ",'') ";
lookUpField_ = columnDef.LookupTable + "." + fields[0];
fieldOnly = fields[0];
}
else
{
lookUpField = "ISNULL(" + columnDef.LookupTable + "." + columnDef.TextField + ",'') ";
lookUpField_ = columnDef.LookupTable + "." + columnDef.TextField;
fieldOnly = columnDef.TextField;
}
var filterCondition = "CAST(" + tableName + "." + columnDef.ColumnName + " AS varchar(250)) IN (SELECT " + columnDef.ValueField + " FROM " + columnDef.LookupTable + " WHERE " + lookUpField_ + " [PARAM]) ";
FilterMap.Add(columnDef.ColumnName + "_tbl", filterCondition);
GridColumns.Append(string.Format(",{0}_tbl", columnDef.ColumnName));
InnerSelect.Append(string.Format(", {0} AS {1}_tbl, {2}.{3} ", lookUpField, columnDef.ColumnName, tableName, columnDef.ColumnName));
InnerJoin.Append(string.Format(" LEFT JOIN {0} ON {1}.{2} = {3}.{4} ", columnDef.LookupTable, tableName, columnDef.ColumnName, columnDef.LookupTable, columnDef.ValueField));
OuterSelect.Append(string.Format(",MAIN.{0}_tbl, MAIN.{1}", columnDef.ColumnName, columnDef.ColumnName));
// set sort
Sort1Map.Add(columnDef.ColumnName + "_tbl", columnDef.LookupTable + "." + fieldOnly);
Sort2Map.Add(columnDef.ColumnName + "_tbl", "MAIN." + columnDef.ColumnName + "_tbl");
}
else
{
GridColumns.Append("," + columnDef.ColumnName);
//OuterSelect.Append(string.Format(",{0}.{1}", tableName, columnDef.ColumnName));
if (columnDef.DataType == "DATE")
{
OuterSelect.Append(string.Format(",{0}.{1}", tableName, columnDef.ColumnName));
//OuterSelect.Append(",ISNULL(" + tableName + "." + columnDef.ColumnName + ",null) AS " + columnDef.ColumnName);
}
else if (columnDef.DataType == "DATETIME")
{
OuterSelect.Append(string.Format(",{0}.{1}", tableName, columnDef.ColumnName));
//OuterSelect.Append(",ISNULL(" + tableName + "." + columnDef.ColumnName + ",null) AS " + columnDef.ColumnName);
}
else
{
OuterSelect.Append(string.Format(",{0}.{1}", tableName, columnDef.ColumnName));
}
}
}
// get SortColumns
var sortColumns = Db.SortColumns.Where(w => w.PageTemplateId == pageTemplateId).OrderBy(o => o.SortOrder);
foreach (var sortColumn in sortColumns)
{
var columnDef = columnDefs.Where(w => w.ColumnDefId == sortColumn.ColumnDefId).FirstOrDefault();
string ascDesc = sortColumn.SortDir;
if (columnDef.LookupTable.Length > 0 && columnDef.TextField.Length > 0 && columnDef.ValueField.Length > 0)
{
if (SortColumns.Length == 0)
{
SortColumns.Append(string.Format("{0}_ {1}", tableName + "." + columnDef.ColumnName, ascDesc));
}
else
{
SortColumns.Append(string.Format(", {0}_ {1}", tableName + "." + columnDef.ColumnName, ascDesc));
}
if (!InnerJoin.ToString().Contains("LEFT JOIN " + columnDef.LookupTable))
{
InnerJoin.Append(string.Format(" LEFT JOIN {0} ON {1}.{2} = {3}.{4} ", columnDef.LookupTable, tableName, columnDef.ColumnName, columnDef.LookupTable, columnDef.ValueField));
}
}
else
{
if (SortColumns.Length == 0)
{
SortColumns.Append(string.Format("{0} {1}", tableName + "." + columnDef.ColumnName, ascDesc));
}
else
{
SortColumns.Append(string.Format(", {0} {1}", tableName + "." + columnDef.ColumnName, ascDesc));
}
}
}
}
tfs.GridColumns = GridColumns.ToString();
tfs.SortColumns = SortColumns.ToString();;
tfs.InnerSelect = InnerSelect.ToString();
tfs.OuterSelect = OuterSelect.ToString();
tfs.InnerJoin = InnerJoin.ToString();
tfs.FilterMap = FilterMap;
tfs.Sort1Map = Sort1Map;
tfs.Sort2Map = Sort2Map;
HttpContext.Current.Session["sec.TableFilterSort" + pageTemplateId] = tfs;
}
return((TableFilterSort)(HttpContext.Current.Session["sec.TableFilterSort" + pageTemplateId]));
}
public static GridSchemaColumns GetGridSchemaAndColumn(int pageTemplateId)
{
if (SessionService.IsLocal)
{
HttpContext.Current.Session["sec.GridSchemaColumns" + pageTemplateId] = null; //xxx
}
if (HttpContext.Current.Session["sec.GridSchemaColumns" + pageTemplateId] == null)
{
var pageTemplate = SessionService.PageTemplate(pageTemplateId);
var columnDefs = SessionService.ColumnDefs(pageTemplateId);
var tableName = pageTemplate.TableName;
var primaryKey = pageTemplate.PrimaryKey;
StringBuilder sbSchema = new StringBuilder();
StringBuilder sbColumns = new StringBuilder();
StringBuilder sbGridScripts = new StringBuilder();
if (SessionService.DataType(pageTemplateId, primaryKey) == "TEXT" || SessionService.DataType(pageTemplateId, primaryKey) == "GUID")
{
sbSchema.AppendLine(primaryKey + ": { type: \"string\", editable: false }");
}
else
{
sbSchema.AppendLine(primaryKey + ": { type: \"number\", editable: false }");
}
var gridWidth = "";
// checkbox for delete
if (pageTemplate.PageType != "gridonly")
{
sbColumns.AppendLine("{ title: \"<input type='checkbox' value='0' id='chkAll_" + pageTemplateId + "' />\", width: 25, template: '<input type=\"checkbox\" value=\"#= " + primaryKey + " #\" class=\"chk_" + pageTemplateId + "\" />' },");
}
using (SourceControlEntities Db = new SourceControlEntities())
{
var gridColumns = Db.GridColumns.Where(w => w.PageTemplateId == pageTemplateId).OrderBy(o => o.SortOrder);
foreach (var gridColumn in gridColumns)
{
var columnDef = columnDefs.Where(w => w.ColumnDefId == gridColumn.ColumnDefId).FirstOrDefault();
if (columnDef == null)
{
continue;
}
gridWidth = "";
if (columnDef.GridWidth.Length > 0)
{
gridWidth = ", width: \"" + columnDef.GridWidth + "\"";
}
if (columnDef.ElementType == "DropdownCustomOption")
{
sbSchema.AppendLine(",\r\n" + columnDef.ColumnName + "_: { type: \"string\" }");
sbColumns.AppendLine("{ field: \"" + columnDef.ColumnName + "_lco\", title: \"" + columnDef.DisplayName + "\", attributes: { \"style\": \"white-space:nowrap;\" } " + gridWidth + " ");
}
else if (columnDef.ElementType == "DropdownSimple")
{
sbSchema.AppendLine(",\r\n" + columnDef.ColumnName + "_: { type: \"string\" }");
sbColumns.AppendLine("{ field: \"" + columnDef.ColumnName + "_tbl\", title: \"" + columnDef.DisplayName + "\" " + gridWidth + " ");
}
else if (columnDef.ElementType == "DisplayOnly")
{
sbColumns.AppendLine("{ template: \" #= " + columnDef.ColumnName + " #\", title: \"" + columnDef.DisplayName + "\", attributes: { \"style\": \"white-space:nowrap;\" } " + gridWidth + " ");
}
else if (columnDef.DataType == "DATE")
{
sbSchema.AppendLine(",\r\n" + columnDef.ColumnName + ": { type: \"date\", format: \"{0:MM/dd/yyyy}\" }");
sbColumns.AppendLine("{ field: \"" + columnDef.ColumnName + "\", title: \"" + columnDef.DisplayName + "\", attributes: { \"style\": \"white-space:nowrap;\" }, type:\"date\", format:\"{0:MM/dd/yyyy}\" " + gridWidth + " ");
}
else if (columnDef.DataType == "DATETIME")
{
sbSchema.AppendLine(",\r\n" + columnDef.ColumnName + ": { type: \"date\", format: \"{0:MM/dd/yyyy hh:mm tt }\" }");
sbColumns.AppendLine("{ field: \"" + columnDef.ColumnName + "\", title: \"" + columnDef.DisplayName + "\", attributes: { \"style\": \"white-space:nowrap;\" }, type:\"date\", format: \"{0:MM/dd/yyyy hh:mm tt }\" " + gridWidth + " ");
}
else if (columnDef.DataType == "NUMBER")
{
sbSchema.AppendLine(",\r\n" + columnDef.ColumnName + ": { type: \"number\" }");
sbColumns.AppendLine("{ field: \"" + columnDef.ColumnName + "\", title: \"" + columnDef.DisplayName + "\", attributes: { \"style\": \"white-space:nowrap;\" } " + gridWidth + " ");
}
else if (columnDef.DataType == "BOOLEAN")
{
sbSchema.AppendLine(",\r\n" + columnDef.ColumnName + ": { type: \"string\" }");
if (columnDef.ElementType == "CheckboxYesNo")
{
sbColumns.AppendLine("{ field: \"" + columnDef.ColumnName + "\", title: \"" + columnDef.DisplayName + "\", template:\"#= GetBooleanYesNo(" + columnDef.ColumnName + ") #\" " + gridWidth + " ");
}
else
{
sbColumns.AppendLine("{ field: \"" + columnDef.ColumnName + "\", title: \"" + columnDef.DisplayName + "\", template:\"#= GetBooleanTrueFalse(" + columnDef.ColumnName + ") #\" " + gridWidth + " ");
}
}
else if (columnDef.ElementType == "HyperLink")
{
sbSchema.AppendLine(",\r\n" + columnDef.ColumnName + ": { type: \"string\" }");
sbColumns.AppendLine("{ field: \"" + columnDef.ColumnName + "\", title: \"" + columnDef.DisplayName + "\", template:\"#= GetHyperLink(" + columnDef.ColumnName + ") #\", encoded: false, attributes: { \"style\": \"white-space:nowrap;\" } " + gridWidth + " ");
}
else if (columnDef.ElementType == "Textarea")
{
sbSchema.AppendLine(",\r\n" + columnDef.ColumnName + ": { type: \"string\" }");
sbColumns.AppendLine("{ field: \"" + columnDef.ColumnName + "\", title: \"" + columnDef.DisplayName + "\", template:\"#= GetComment(" + columnDef.ColumnName + ") #\" " + gridWidth + " ");
}
else
{
sbSchema.AppendLine(",\r\n" + columnDef.ColumnName + ": { type: \"string\" }");
sbColumns.AppendLine("{ field: \"" + columnDef.ColumnName + "\", title: \"" + columnDef.DisplayName + "\", encoded: false, attributes: { \"style\": \"white-space:nowrap;\" } " + gridWidth + " ");
}
// add multiple checkbox select
if ((bool)columnDef.IsMultiSelect)
{
sbColumns.AppendLine(",filterable: {");
sbColumns.AppendLine(" multi: true,");
sbColumns.AppendLine(" dataSource: {");
sbColumns.AppendLine(" transport: {");
sbColumns.AppendLine(" read: {");
sbColumns.AppendLine(" url: \"Data/GetMultiSelect\",");
sbColumns.AppendLine(" dataType: \"json\",");
sbColumns.AppendLine(" data: {");
sbColumns.AppendLine(" pageTemplateId: \"" + columnDef.PageTemplateId + "\", columnDefId: " + columnDef.ColumnDefId + "");
sbColumns.AppendLine(" }");
sbColumns.AppendLine(" }");
sbColumns.AppendLine(" }");
sbColumns.AppendLine(" },");
sbColumns.AppendLine(" itemTemplate: function(e) {");
sbColumns.AppendLine(" if (e.field == \"all\") {");
sbColumns.AppendLine(" return \"<div><label><strong><input type='checkbox' />Select All</strong></label></div>\";");
sbColumns.AppendLine(" } else {");
sbColumns.AppendLine(" return \"<div><input type='checkbox' name='\" + e.field + \"' value='#=ValueField#'/><span>#= TextField #</span></div>\"");
sbColumns.AppendLine(" }");
sbColumns.AppendLine(" }");
sbColumns.AppendLine("}");
}
sbColumns.Append("},");
}
}
var gridColumns_ = sbColumns.ToString();
if (gridColumns_.Length > 2)
{
gridColumns_ = gridColumns_.Substring(0, gridColumns_.Length - 1);
}
GridSchemaColumns gridSchemaColumns = new GridSchemaColumns {
GridSchema = sbSchema.ToString(), GridColumns = gridColumns_, GridScripts = sbGridScripts.ToString()
};
HttpContext.Current.Session["sec.GridSchemaColumns" + pageTemplateId] = gridSchemaColumns;
}
return((GridSchemaColumns)HttpContext.Current.Session["sec.GridSchemaColumns" + pageTemplateId]);
}
private static void GetLayoutReplacements(int pageTemplateId, string layoutType, ref string layOut, ref FormLayout formLayout)
{
var recordId = "$('#InternalId_" + pageTemplateId + "').val()";
var pageTemplate = SessionService.PageTemplate(pageTemplateId);
var columnDefs = SessionService.ColumnDefs(pageTemplateId);
var columnDefId = columnDefs[0].ColumnDefId.ToString();
foreach (var columnDef in columnDefs)
{
if ((bool)columnDef.IsPrimary) // display only for Primary key
{
Type type = typeof(FormLayout);
var elementType = "Hidden";
var replaceWith = (string)type.InvokeMember(elementType, BindingFlags.InvokeMethod, null, formLayout, new object[] { columnDef });
layOut = layOut.Replace("[" + pageTemplate.TableName + "_" + columnDef.ColumnName + "]", replaceWith);
if (columnDef.ElementType == "DisplayOnly")
{
layOut = layOut.Replace("[" + pageTemplate.TableName + "_" + columnDef.ColumnName + "]", formLayout.DisplayOnly(columnDef));
}
}
else if (columnDef.ElementType == "Note")
{
var linkUpload = " <img src='" + SessionService.VirtualDomain + "\\Images\\plus.png'><a href=\"javascript:AddNote(" + columnDef.PageTemplateId + ", " + columnDef.ColumnDefId + ")\">Add Note</a>";
layOut = layOut.Replace("[" + pageTemplate.TableName + "_" + columnDef.ColumnName + "]", formLayout.Note(columnDef));
layOut = layOut.Replace("[" + pageTemplate.TableName + "_" + columnDef.ColumnName + "LINK]", linkUpload);
}
else if (columnDef.ElementType == "Custom")
{
var elementObject = columnDef.ElementObject.Replace("[PageTemplateId]", pageTemplateId.ToString()).Replace("[ColumnDefId]", columnDefId).Replace("[RecordId]", recordId).Replace("[GT]", ">").Replace("[LT]", "<").Replace("[CL]", ";");
formLayout.DocumentReady.AppendLine(columnDef.ElementDocReady.Replace("[PageTemplateId]", pageTemplateId.ToString()).Replace("[ColumnDefId]", columnDefId).Replace("[RecordId]", recordId)).Replace("[GT]", ">").Replace("[LT]", "<").Replace("[CL]", ";");
var elementLink = columnDef.ElementLabelLink.Replace("[PageTemplateId]", pageTemplateId.ToString()).Replace("[ColumnDefId]", columnDefId).Replace("[RecordId]", recordId).Replace("[GT]", ">").Replace("[LT]", "<").Replace("[CL]", ";");
formLayout.Functions.AppendLine(columnDef.ElementFunction);
layOut = layOut.Replace("[" + pageTemplate.TableName + "_" + columnDef.ColumnName + "LINK]", elementLink);
layOut = layOut.Replace("[" + pageTemplate.TableName + "_" + columnDef.ColumnName + "]", elementObject);
}
else if (columnDef.ElementType == "FileAttachment")
{
string linkUpload = " <img src='" + SessionService.VirtualDomain + "\\Images\\paperclip.png'><a href=\"javascript:UploadFile1(" + columnDef.PageTemplateId + ", " + columnDef.ColumnDefId + ")\">Upload</a><span id='spanUpload" + columnDef.ColumnDefId + "'></span>";
layOut = layOut.Replace("[" + pageTemplate.TableName + "_" + columnDef.ColumnName + "]", formLayout.FileAttachment(columnDef));
layOut = layOut.Replace("[" + pageTemplate.TableName + "_" + columnDef.ColumnName + "LINK]", linkUpload);
}
else
{
if (columnDef.ElementType.Length > 2 || layoutType == "View" || layoutType == "Search")
{
Type type = typeof(FormLayout);
var elementType = columnDef.ElementType;
if ((layoutType == "View" || layoutType == "Search") && !"Textarea:CheckboxTrueFalse:CheckboxYesNo:".Contains(elementType))
{
elementType = "Textbox";
}
var replaceWith = (string)type.InvokeMember(elementType, BindingFlags.InvokeMethod, null, formLayout, new object[] { columnDef });
if (layoutType == "Search")
{
replaceWith = replaceWith.Replace("id='", "id='Search_").Replace("name='", "name='Search_");
}
layOut = layOut.Replace("[" + pageTemplate.TableName + "_" + columnDef.ColumnName + "]", replaceWith);
// set element to span for lookup table fields
if (columnDef.LookupTable.Length > 0 && columnDef.ValueField.Length > 0 && columnDef.TextField.Length > 0)
{
var lookupColumnDefs = SessionService.ColumnDefs(pageTemplate.DbEntityId, columnDef.LookupTable);
foreach (var lookupColumnDef in lookupColumnDefs)
{
elementType = (layoutType == "Search") ? "Textbox" : "Span";
replaceWith = (string)type.InvokeMember(elementType, BindingFlags.InvokeMethod, null, formLayout, new object[] { columnDef.LookupTable, lookupColumnDef });
if (layoutType == "Search")
{
replaceWith = replaceWith.Replace("id='", "id='Search_").Replace("name='", "name='Search_");
}
layOut = layOut.Replace("[" + columnDef.LookupTable + "_" + lookupColumnDef.ColumnName + "]", replaceWith);
}
}
}
}
}
}