private static Tuple <Response, List <T> > GetPayloadList <T>(ServerRowModel payload)
{
var journals = Utils.PostWebApi("FinanceWebApi", JournalUrl, payload);
Task.WaitAll(journals);
var response = JsonConvert.DeserializeObject <Response>(journals.Result);
var serializedPayload = JsonConvert.SerializeObject(response.payload);
var result = JsonConvert.DeserializeObject <List <T> >(serializedPayload);
return(new Tuple <Response, List <T> >(response, result));
}
private static string CreateGroupBySql(ServerRowModel obj)
{
if (IsDoingGrouping(obj.rowGroupCols, obj.groupKeys))
{
List <string> colsToGroupBy = new List <string>();
int count = obj.groupKeys.Count == 0 ? 0 : obj.groupKeys.Count;
var rowGroupCol = obj.rowGroupCols[count];
colsToGroupBy.Add($"[{rowGroupCol.field}]");
StringBuilder query = new StringBuilder(" group by ");
query.Append(string.Join(",", colsToGroupBy.Select(x => x)) + " ");
return(query.ToString());
}
else
{
// select all columns
return("");
}
}
public static int GetRowCount(ServerRowModel request, DataTable results)
{
if (results.Rows.Count == 0)
{
return(0);
}
int currentLastRow = request.startRow + results.Rows.Count;
if (currentLastRow < request.endRow)
{
return(currentLastRow);
}
else
{
return(-1);
}
}
internal void TaxLotReport()
{
try
{
IReport report = new SpreadSheet();
ServerRowModel layout = new ServerRowModel();
var layoutJSON = GetLayout("Taxlot Status");
layout.gridColDefs = JsonConvert.DeserializeObject <List <GridColDef> >(layoutJSON.ColumnState);
layout.filterModel = JsonConvert.DeserializeObject <ExpandoObject>(layoutJSON.FilterState);
layout.externalFilterModel = JsonConvert.DeserializeObject <ExpandoObject>(layoutJSON.ExternalFilterState);
layout.sortModel = JsonConvert.DeserializeObject <List <SortModel> >(layoutJSON.SortState);
var queryObject = ServerSideRowModelHelper.BuildSqlFromGridLayouts(layout, "tax_lot_status");
//var query = $@"SELECT * FROM tax_lot_status";
var dataTable = sqlHelper.GetDataTable(queryObject.Item1, CommandType.Text, queryObject.Item2.ToArray());
report.Generate(dataTable);
}
catch (Exception ex)
{
throw ex;
}
}
public static Tuple <string, string, List <SqlParameter> > BuildSql(ServerRowModel obj, string from, bool fetchWhere = false)
{
List <SqlParameter> sqlParams = new List <SqlParameter>();
Tuple <string, string, int> whereSql = CreateWhereSql(obj, sqlParams);
if (fetchWhere)
{
return(new Tuple <string, string, List <SqlParameter> >(whereSql.Item1, "", sqlParams));
}
else
{
string selectSql = CreateSelectSql(obj, from);
string fromSql = $" FROM [{from}] ";
string limitSql = CreateLimitSql(obj, sqlParams);
string orderBySql = CreateOrderBySql(obj);
string groupBySql = CreateGroupBySql(obj);
string havingSql = CreateHavingSql(obj, sqlParams, whereSql.Item3);
string message = !string.IsNullOrWhiteSpace(whereSql.Item2) ? whereSql.Item2 : null;
string query = "select * from (" + selectSql + fromSql + whereSql.Item1 + groupBySql + havingSql + ") t " + orderBySql + limitSql;
return(new Tuple <string, string, List <SqlParameter> >(query, message, sqlParams));
}
}
private static string CreateHavingSql(ServerRowModel obj, List <SqlParameter> sqlParams, int index)
{
StringBuilder query = new StringBuilder("");
bool isGrouping = IsDoingGrouping(obj.rowGroupCols, obj.groupKeys);
if (isGrouping)
{
var externalFiltersHavingClause = ExtractExternalFilters(obj, ref sqlParams, index, true);
if (externalFiltersHavingClause.Count > 0)
{
query.Append(" having ");
return(query.Append($"{string.Join(" and ", externalFiltersHavingClause.Select(x => x))}").ToString());
}
else
{
return("");
}
}
else
{
return("");
}
}
public object GetTotalCount(ServerRowModel obj)
{
return(controller.GetTotalCount(obj));
}
public object GetServerSideJournals(ServerRowModel obj)
{
return(controller.serverSideJournals(obj));
}
private static Tuple <string, string, int> CreateWhereSql(ServerRowModel obj, List <SqlParameter> sqlParams)
{
List <string> whereParts = new List <string>();
int index = 0;
StringBuilder message = new StringBuilder();
List <string> duplicateFilterList = new List <string>();
StringBuilder query = new StringBuilder("");
if (obj.groupKeys.Count > 0)
{
foreach (var item in obj.groupKeys)
{
string colName = obj.rowGroupCols[index].id;
sqlParams.Add(new SqlParameter($"{colName}{index}", item));
whereParts.Add($" [{colName}] = @{colName}{index} ");
index++;
}
}
var filterDict = (IDictionary <string, dynamic>)(obj.filterModel);
var externalFilterDict = (IDictionary <string, dynamic>)(obj.externalFilterModel);
var externalFilters = ExtractExternalFilters(obj, ref sqlParams, index);
if (filterDict != null)
{
foreach (var col in filterDict)
{
string columnName = col.Key;
var value = (IDictionary <string, object>)(col.Value);
List <string> filterModelWhereList = new List <string>();
//only extract in grid filters which are not present in external filters. External filters have precedence over internal.
if (!externalFilterDict.Keys.Contains(columnName, StringComparer.CurrentCultureIgnoreCase))
{
index = ExtractInGridFilters(sqlParams, whereParts, index, columnName, value, filterModelWhereList);
}
else
{
duplicateFilterList.Add(columnName);
}
}
}
if (duplicateFilterList.Count > 0 && obj.pageNumber == 1)
{
message.Append($"External filter(s) already present for {string.Join(" and ", duplicateFilterList.Select(x => x))}");
}
if (whereParts.Count > 0)
{
whereParts.AddRange(externalFilters);
query.Append(" where " + string.Join(" and ", whereParts.Where(x => !string.IsNullOrEmpty(x)).Select(x => x)));
return(new Tuple <string, string, int>(query.ToString(), message.ToString(), index));
}
if (externalFilters.Count > 0)
{
query.Append(" where " + string.Join(" and ", externalFilters.Select(x => x)));
return(new Tuple <string, string, int>(query.ToString(), message.ToString(), index));
}
return(new Tuple <string, string, int>(" ", message.ToString(), index));
}
private static string CreateLimitSql(ServerRowModel obj, List <SqlParameter> sqlParams)
{
sqlParams.Add(new SqlParameter("pageNumber", obj.pageNumber));
sqlParams.Add(new SqlParameter("pageSize", obj.pageSize));
return(" OFFSET(@pageNumber -1) * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY");
}
private static string CreateOrderBySql(ServerRowModel obj)
{
var grouping = IsDoingGrouping(obj.rowGroupCols, obj.groupKeys);
List <string> sortParts = new List <string>();
bool groupSortsPresent = false;
int count = obj.groupKeys.Count == 0 ? 0 : obj.groupKeys.Count;
if ((obj.sortModel.Count == obj.rowGroupCols.Count && obj.rowGroupCols.Count > 0 &&
obj.groupKeys.Count == 0))
{
//when the complete group is clicked for sorting. just sort by the root level node initially. Rest of the sorting will be applied as the user drills down.
// do nothing
}
else if (obj.sortModel.Count > 0)
{
var modifiedRowGroupCols = obj.rowGroupCols.Select(x => x.id).ToList();
if (obj.groupKeys.Count == 0)
{
modifiedRowGroupCols.RemoveRange(0, modifiedRowGroupCols.Count);
}
else
{
//modifiedRowGroupCols.RemoveRange(0, count);
modifiedRowGroupCols = modifiedRowGroupCols.Skip(count).Take(1).ToList();
}
foreach (var item in obj.sortModel)
{
if (grouping && modifiedRowGroupCols.IndexOf(item.colId) < 0)
{
//ignore because these groupings are not focused right now
}
else
{
if (!SortByAbsoluteValue(obj.absoluteSorting, item.colId))
{
sortParts.Add($"[{item.colId}] {item.sort}");
}
else
{
sortParts.Add($"ABS([{item.colId}]) {item.sort}");
}
groupSortsPresent = true;
}
}
}
if ((grouping && obj.sortModel.Count == 0) || (!groupSortsPresent && grouping))
{
var rowGroupCol = obj.rowGroupCols[count];
var direction = obj.sortModel.Where(x => x.colId == rowGroupCol.field).FirstOrDefault();
//check if sorting is being performed on an aggreagte column
if (obj.sortModel.Count > 0)
{
foreach (var item in obj.sortModel)
{
if (obj.valueCols.Any(x => x.field == item.colId))
{
if (!SortByAbsoluteValue(obj.absoluteSorting, item.colId))
{
sortParts.Add($"[{item.colId}] {item.sort}");
}
else
{
sortParts.Add($"ABS([{item.colId}]) {item.sort}");
}
}
}
}
if (sortParts.Count == 0)
{
if (direction != null)
{
sortParts.Add($"[{rowGroupCol.field}] {direction.sort}");
}
else
{
sortParts.Add($"[{rowGroupCol.field}]");
}
}
}
if (sortParts.Count > 0)
{
return(" order by " + string.Join(",", sortParts.Select(x => x)));
}
else
{
return(" order by [when] desc");
}
}
public object GetTotalCount(ServerRowModel obj)
{
throw new NotImplementedException();
}
public object serverSideJournals(ServerRowModel obj)
{
throw new NotImplementedException();
}
