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 string ColumnName(int columnDefId)
        {
            if (SessionService.IsLocal)
            {
                HttpContext.Current.Session["sec.ColumnName" + columnDefId] = null;                         //xxx
            }
            if (columnDefId == 0)
            {
                return("");
            }
            if (HttpContext.Current.Session["sec.ColumnName" + columnDefId] == null)
            {
                var columnDef = SessionService.ColumnDef(columnDefId);

                HttpContext.Current.Session["sec.ColumnName" + columnDefId] = columnDef != null ? columnDef.ColumnName : "";
            }
            return(HttpContext.Current.Session["sec.ColumnName" + columnDefId].ToString());
        }
        public static PageTemplate PageTemplate(int pageTemplateId)
        {
            if (pageTemplateId == 0)
            {
                return(null);
            }

            HttpContext.Current.Session["sec.PageTemplate" + pageTemplateId] = null; //xxx
            if (HttpContext.Current.Session["sec.PageTemplate" + pageTemplateId] == null)
            {
                using (SourceControlEntities Db = new SourceControlEntities())
                {
                    var pageTemplate  = Db.PageTemplates.Find(pageTemplateId);
                    var pageTemplates = SessionService.PageTemplates(pageTemplate.DbEntityId);
                    var pageTemplate_ = pageTemplates.Where(w => w.PageTemplateId == pageTemplateId).FirstOrDefault();

                    HttpContext.Current.Session["sec.PageTemplate" + pageTemplateId] = pageTemplate_;
                }
            }
            return((PageTemplate)(HttpContext.Current.Session["sec.PageTemplate" + pageTemplateId]));
        }
        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]));
        }
Esempio n. 5
0
        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]);
        }
Esempio n. 6
0
        public static string GetServerSideRecords(int skip, int take, int page, int pageSize, List <GridSort> gridSorts = null, GridFilters filter = null, int pageTemplateId = 0)
        {
            //try
            //{
            var pageTemplate = SessionService.PageTemplate(pageTemplateId);

            string tableName   = "";
            string primaryKey  = "";
            string innerSelect = "";
            string innerJoin   = "";
            string outerSelect = "";
            string sortColumns = "";
            Dictionary <string, string> filterMap = new Dictionary <string, string>();
            Dictionary <string, string> sort1Map  = new Dictionary <string, string>();
            Dictionary <string, string> sort2Map  = new Dictionary <string, string>();

            tableName  = pageTemplate.TableName;
            primaryKey = pageTemplate.PrimaryKey;

            TableFilterSort tfs = SessionService.TableFilterSort(pageTemplateId);

            innerSelect = tfs.InnerSelect;
            innerJoin   = tfs.InnerJoin;
            outerSelect = tfs.OuterSelect;
            sortColumns = tfs.SortColumns;
            filterMap   = tfs.FilterMap;
            sort1Map    = tfs.Sort1Map;
            sort2Map    = tfs.Sort2Map;


            // WHERE
            string whereFilter = (HttpContext.Current.Session["WhereFilter" + pageTemplateId] != null) ? HttpContext.Current.Session["WhereFilter" + pageTemplateId].ToString() : "";

            if ((filter != null && filter.Filters != null))
            {
                string buildFilter  = "";
                string buildFilter2 = "";
                string fldName      = "";
                string oper         = "";
                string fldValue     = "";

                if ((filter != null && (filter.Filters != null && filter.Filters.Count > 0)))
                {
                    HttpContext.Current.Session["totalCount" + pageTemplateId] = null;

                    var filters = filter.Filters;

                    string logic = filter.Logic;

                    for (var i = 0; i < filters.Count; i++)
                    {
                        if (filters[i].Field == null && filters[i].Filters != null)
                        {
                            if (buildFilter2.Length > 0)
                            {
                                buildFilter2 += " AND  ";
                            }

                            buildFilter2 += "(";

                            var andOr = filters[i].Logic;
                            for (var x = 0; x < filters[i].Filters.Count; x++)
                            {
                                fldName       = filters[i].Filters[x].Field;
                                oper          = filters[i].Filters[x].Operator.ToLower();
                                fldValue      = filters[i].Filters[x].Value;
                                buildFilter2 += BuildFilter(fldName, oper, fldValue, filterMap) + " " + andOr + " ";
                            }
                            buildFilter2  = buildFilter2.Substring(0, buildFilter2.Length - 4);
                            buildFilter2 += ") ";

                            continue;
                        }


                        fldName  = filters[i].Field;
                        oper     = filters[i].Operator.ToLower();
                        fldValue = filters[i].Value;

                        buildFilter += BuildFilter(fldName, oper, fldValue, filterMap) + " " + logic + " ";
                    }
                }

                if (buildFilter.Length > 0 || buildFilter2.Length > 0)
                {
                    if (buildFilter.Length > 0)
                    {
                        buildFilter = buildFilter.Substring(0, buildFilter.Length - 4);

                        buildFilter = " (" + buildFilter + ") ";
                    }



                    if (buildFilter2.Length > 0)
                    {
                        if (buildFilter.Length > 0)
                        {
                            buildFilter += " AND  ";
                        }

                        buildFilter += " (" + buildFilter2 + ") ";
                    }
                }

                HttpContext.Current.Session["WhereFilter" + pageTemplateId] = buildFilter;
            }

            //--------- ORDER BY
            if (gridSorts != null && gridSorts.Count > 0)
            {
                HttpContext.Current.Session["SortField2" + pageTemplateId] = null;
                foreach (var gridSort in gridSorts)
                {
                    if ((gridSort.Field.Contains("_lco") || gridSort.Field.Contains("_tbl")) && sort1Map.ContainsKey(gridSort.Field))
                    {
                        HttpContext.Current.Session["SortField1" + pageTemplateId] = sort1Map[gridSort.Field];
                        HttpContext.Current.Session["SortField2" + pageTemplateId] = sort2Map[gridSort.Field];
                        HttpContext.Current.Session["SortDir" + pageTemplateId]    = gridSort.Dir;
                        break;
                    }
                    else
                    {
                        HttpContext.Current.Session["SortField1" + pageTemplateId] = tableName + "." + gridSort.Field;
                        HttpContext.Current.Session["SortDir" + pageTemplateId]    = gridSort.Dir;
                        break;
                    }
                }
            }

            // set default sort
            if (HttpContext.Current.Session["SortField1" + pageTemplateId] == null && sortColumns.Length > 0)
            {
                HttpContext.Current.Session["SortDir" + pageTemplateId]    = "ASC";
                HttpContext.Current.Session["SortField1" + pageTemplateId] = sortColumns;
                HttpContext.Current.Session["SortDir" + pageTemplateId]    = "";
            }

            string orderBy1 = " ORDER BY " + HttpContext.Current.Session["SortField1" + pageTemplateId].ToString() + " " + HttpContext.Current.Session["SortDir" + pageTemplateId].ToString() + " ";

            if (HttpContext.Current.Session["SortField2" + pageTemplateId] == null)
            {
                HttpContext.Current.Session["SortField2" + pageTemplateId] = HttpContext.Current.Session["SortField1" + pageTemplateId].ToString();
            }
            string orderBy2 = " ORDER BY " + HttpContext.Current.Session["SortField2" + pageTemplateId].ToString() + " " + HttpContext.Current.Session["SortDir" + pageTemplateId].ToString() + " ";

            if (orderBy1.Length < 14)
            {
                orderBy1 = "";
                orderBy2 = "";
            }

            // where clase
            string whereClause = "";

            if (HttpContext.Current.Session["WhereFilter" + pageTemplateId] != null && HttpContext.Current.Session["WhereFilter" + pageTemplateId].ToString().Length > 2)
            {
                whereClause = " WHERE " + HttpContext.Current.Session["WhereFilter" + pageTemplateId].ToString() + " ";
            }


            //--------- COUNT()
            var totalCount = 0;

            if (HttpContext.Current.Session["totalCount" + pageTemplateId] == null)
            {
                StringBuilder sbCount = new StringBuilder();
                sbCount.Append("SELECT COUNT(1) ");
                sbCount.Append("FROM " + tableName + " ");
                sbCount.Append(innerJoin + " ");
                sbCount.Append(whereClause + " ");

                var dbEntity = SessionService.DbEntity(pageTemplate.DbEntityId);
                using (TargetEntities Db = new TargetEntities())
                {
                    Db.Database.Connection.ConnectionString = dbEntity.ConnectionString;
                    totalCount = Db.Database.SqlQuery <int>(sbCount.ToString()).FirstOrDefault();
                }
            }
            else
            {
                totalCount = Convert.ToInt32(HttpContext.Current.Session["totalCount" + pageTemplateId]);
            }

            //--------- Build SQL
            string pk = tableName + "." + primaryKey;

            StringBuilder sb = new StringBuilder();

            sb.Append("WITH MAIN AS (");

            // inner query
            sb.Append("SELECT " + innerSelect + " ");
            sb.Append("FROM " + tableName + " ");
            sb.Append(innerJoin + " ");
            sb.Append(whereClause + " ");
            sb.Append(orderBy1 + " ");


            if (take > 0)
            {
                sb.Append("OFFSET " + skip + " ROWS FETCH NEXT " + take + " ROWS ONLY ");
            }
            else
            {
                sb.Append("OFFSET 0 ROWS FETCH NEXT 1000000 ROWS ONLY ");
            }

            sb.Append(") ");

            // outer query
            sb.Append("SELECT " + outerSelect + " FROM " + tableName + " INNER JOIN MAIN ON " + pk + " = MAIN." + primaryKey + " ");
            sb.Append(orderBy2 + " ");

            string sql = sb.ToString();

            if (totalCount == 0)
            {
                return("{\"total\":0,\"data\":[] }");
            }

            var json = "";

            if (take > 0)
            {
                json = "{\"total\":" + totalCount + ",\"data\":" + DataService.GetJsonFromSQL(pageTemplate.DbEntityId, sql) + "}";
            }
            else
            {
                json = DataService.GetJsonFromSQL(pageTemplate.DbEntityId, sql);
            }

            json = json.Replace("\r", "").Replace("\n", "");
            return(json);
            //}
            //catch (Exception ex)
            //{
            //    Helper.LogError(ex.StackTrace);
            //    return "";
            //}
        }
Esempio n. 7
0
        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 = "&nbsp;&nbsp;<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 = "&nbsp;&nbsp;<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);
                            }
                        }
                    }
                }
            }
        }