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 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 "";
//}
}
