private static NpgsqlDataReader Get(TeamHttpContext teamContext, string sql, List <string> sortableColumns, DataRequest dataRequest) { dataRequest.SortBy = dataRequest.SortBy.ToUpper(CultureInfo.InvariantCulture).Trim(); dataRequest.SortOrder = dataRequest.SortOrder.ToUpper(CultureInfo.InvariantCulture).Trim(); if (!sortableColumns.Contains(dataRequest.SortBy)) { dataRequest.SortBy = Constants.SORT_BY_ID; } if (dataRequest.SortOrder != "ASC" && dataRequest.SortOrder != "DESC") { dataRequest.SortOrder = "ASC"; } sql += $" order by {dataRequest.SortBy} {dataRequest.SortOrder} "; sql += " limit @limit offset @offset"; PostgresService dl = new PostgresService(); if (dataRequest.HasDateFiltering) { dl.AddDateParam("@startDate", dataRequest.From); dl.AddDateParam("@endDate", dataRequest.To); } dl.AddParam("@blankQuery", dataRequest.IsBlankQuery); dl.AddLikeParam("@query", dataRequest.Query); dl.AddIntParam("@offset", dataRequest.Offset); dl.AddIntParam("@limit", dataRequest.Limit); return(dl.ExecuteSqlReturnReader(Utility.ConnString, sql)); }
internal static async Task <List <LoadDashboard> > GetAllWidgetAsync(TeamHttpContext httpContext) { string userid = httpContext.ContextUserId; List <LoadDashboard> returnValue = new List <LoadDashboard>(); PostgresService postgresService = new PostgresService(); List <UserDashboard> objUserDashboard = new List <UserDashboard>(); using (TeamDbContext dbContext = new TeamDbContext()) { //objUserDashboard = dbContext.UserDashboards.Where(x => x.DashboardUserId == userid && x.Deleted == false).ToList(); //user specific //objUserDashboard = dbContext.UserDashboards.Where(x => x.Deleted == false).ToList(); // For all users objUserDashboard = dbContext.UserDashboards.Where(x => x.Deleted == false && x.DashboardUserPermission.Contains(httpContext.ContextUserId)).ToList(); // For permitted users } if (objUserDashboard.Count != 0) { foreach (var item in objUserDashboard) { List <WidgetRead> objWidgetRead = new List <WidgetRead>(); string connString = Utility.GetConnectionString(item.WidgetConnectionString); using (NpgsqlDataReader sqlData = postgresService.ExecuteSqlReturnReader(connString, item.WidgetQuery)) { while (sqlData.Read()) { objWidgetRead.Add(new WidgetRead { Name = sqlData.GetBlankStringIfNull("name"), Count = sqlData.GetInt32("count") }); } } LoadDashboard newItem = new LoadDashboard { DashboardWidgetName = item.DashboardWidgetName, DashboardWidgetId = item.Id, DashboardWidgetType = item.DashboardChartType, DashbaordWidgetData = objWidgetRead.ToArray() }; returnValue.Add(newItem); } } return(returnValue); }
internal static async Task <List <OnScreenClick> > GetGridDataAsync(TeamHttpContext httpContext, OnScreenClick details) { //string connString = Utility.GetConnectionString("PgAdmin4ConnectionString"); string connString = string.Empty; string userid = httpContext.ContextUserId; //string userid = "Admin"; List <OnScreenClick> returnValue = new List <OnScreenClick>(); PostgresService postgresService = new PostgresService(); List <UserDashboard> objUserDashboard = new List <UserDashboard>(); string querylevel = string.Empty; List <string[]> ResultSet = new List <string[]>(); MatchCollection allMatchResults = null; Regex regex = new Regex(@"@\w*@"); using (TeamDbContext dbContext = new TeamDbContext()) { //objUserDashboard = dbContext.UserDashboards.Where(x => x.DashboardUserId == userid && x.Deleted == false && x.Id == details.ClickedWidgetId).ToList(); // For specific user objUserDashboard = dbContext.UserDashboards.Where(x => x.Deleted == false && x.Id == details.ClickedWidgetId).ToList(); // for all users } if (objUserDashboard.Count != 0) { foreach (var item in objUserDashboard) { switch (details.ClickLevel) { case "L1": connString = Utility.GetConnectionString(item.Level1ConnectionString); querylevel = item.DashbaordQueryL1; allMatchResults = regex.Matches(querylevel); if (allMatchResults.Count > 0) { querylevel = querylevel.Replace(allMatchResults[0].Value, details.ClickedOnValue); } break; case "L2": connString = Utility.GetConnectionString(item.Level2ConnectionString); querylevel = item.DashbaordQueryL2; break; case "L3": connString = Utility.GetConnectionString(item.Level3ConnectionString); querylevel = item.DashbaordQueryL3; break; case "L4": connString = Utility.GetConnectionString(item.Level4ConnectionString); querylevel = item.DashbaordQueryL4; break; } if (details.ClickLevel != "L1") { var dict = details.GridInput.ToDictionary(m => m.Name, m => m.Value); allMatchResults = regex.Matches(querylevel); if (allMatchResults.Count > 0) { foreach (var match in allMatchResults) { dict.TryGetValue(((System.Text.RegularExpressions.Capture)match).Value.Substring(1, ((System.Text.RegularExpressions.Capture)match).Value.Length - 2), out string result); querylevel = querylevel.Replace(match.ToString(), result); } } } NpgsqlDataReader dr = postgresService.ExecuteSqlReturnReader(connString, querylevel); DataTable dtSchema = dr.GetSchemaTable(); DataTable dt = new DataTable(); List <DataColumn> listCols = new List <DataColumn>(); if (dtSchema != null) { foreach (DataRow drow in dtSchema.Rows) { string columnName = Convert.ToString(drow["ColumnName"]); DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"])); column.Unique = (bool)drow["IsUnique"]; column.AutoIncrement = (bool)drow["IsAutoIncrement"]; listCols.Add(column); dt.Columns.Add(column); } } // Read rows from DataReader and populate the DataTable while (dr.Read()) { DataRow dataRow = dt.NewRow(); for (int i = 0; i < listCols.Count; i++) { dataRow[((DataColumn)listCols[i])] = dr[i]; } dt.Rows.Add(dataRow); } string[] columnNames = dt.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray(); ResultSet = dt.Select().Select(drr => drr.ItemArray.Select(x => x.ToString()).ToArray()).ToList(); OnScreenClick newItem = new OnScreenClick { ClickLevel = details.ClickLevel, ClickedWidgetId = details.ClickedWidgetId, ClickedOnValue = details.ClickedOnValue, GridColumns = columnNames, GridData = ResultSet, GridInput = null }; returnValue.Add(newItem); } } return(returnValue); }
internal static async Task <List <LoadWidgets> > GetWidgetAsync(TeamHttpContext httpContext, string widgettype, string widgetname)//, string widgetquery, string widgetquerylevel1, string widgetquerylevel2, string widgetquerylevel3, string widgetquerylevel4) { string userid = httpContext.ContextUserId; List <LoadWidgets> returnValue = new List <LoadWidgets>(); PostgresService postgresService = new PostgresService(); List <UserDashboard> objUserDashboard = new List <UserDashboard>(); using (TeamDbContext dbContext = new TeamDbContext()) { objUserDashboard = dbContext.UserDashboards.Where(x => x.CreatedBy == userid && x.DashboardChartType == widgettype && x.DashboardWidgetName == widgetname && x.Deleted == false).ToList(); } if (objUserDashboard.Count != 0) { foreach (var item in objUserDashboard) { List <WidgetRead> objWidgetRead = new List <WidgetRead>(); string connString = Utility.GetConnectionString(item.WidgetConnectionString); using (NpgsqlDataReader sqlData = postgresService.ExecuteSqlReturnReader(connString, item.WidgetQuery)) { while (sqlData.Read()) { objWidgetRead.Add(new WidgetRead { Name = sqlData.GetBlankStringIfNull("name"), Count = sqlData.GetInt32("count") }); } } LoadWidgets newItem = new LoadWidgets { DashboardWidgetName = item.DashboardWidgetName, WidgetId = item.Id, DashboardChartType = item.DashboardChartType, DashboardUserPermission = item.DashboardUserPermission, DashboardEmailFormat = item.DashboardEmailFormat, WidgetData = objWidgetRead.ToArray(), WidgetConnectionString = item.WidgetConnectionString, WidgetSchedulerType = item.WidgetSchedulerType, WidgetSchedulerEmailIDs = item.WidgetSchedulerEmailIDs, WidgetQuery = item.WidgetQuery, Level1ConnectionString = item.Level1ConnectionString, Level1SchedulerType = item.Level1SchedulerType, L1SchedulerEmailIDs = item.L1SchedulerEmailIDs, DashbaordQueryL1 = item.DashbaordQueryL1, Level2ConnectionString = item.Level2ConnectionString, Level2SchedulerType = item.Level2SchedulerType, L2SchedulerEmailIDs = item.L2SchedulerEmailIDs, DashbaordQueryL2 = item.DashbaordQueryL2, Level3ConnectionString = item.Level3ConnectionString, Level3SchedulerType = item.Level3SchedulerType, L3SchedulerEmailIDs = item.L3SchedulerEmailIDs, DashbaordQueryL3 = item.DashbaordQueryL3, Level4ConnectionString = item.Level4ConnectionString, Level4SchedulerType = item.Level4SchedulerType, L4SchedulerEmailIDs = item.L4SchedulerEmailIDs, DashbaordQueryL4 = item.DashbaordQueryL4, WidgetSendEmail = item.WidgetSendEmail }; returnValue.Add(newItem); } } return(returnValue); }
public static async Task <ResponseBase <List <ReportGrid> > > ViewReport(TeamHttpContext teamHttpContext, int reportId) { try { //string connString = Utility.GetConnectionString("PgAdmin4ConnectionString"); string userid = teamHttpContext.ContextUserId; //string userid = "Admin"; List <ReportGrid> returnValue = new List <ReportGrid>(); PostgresService postgresService = new PostgresService(); List <ReportConfig> objReportConfig = new List <ReportConfig>(); List <string[]> ResultSet = new List <string[]>(); using (TeamDbContext dbContext = new TeamDbContext()) { objReportConfig = dbContext.ReportConfigs.Where(x => x.Id == reportId && x.Deleted == false && x.ReportReqUserId == userid).ToList(); } if (objReportConfig.Count != 0) { foreach (var item in objReportConfig) { string connString = Utility.GetConnectionString(item.ReportConnectionString); NpgsqlDataReader dr = postgresService.ExecuteSqlReturnReader(connString, item.ReportQuery); DataTable dtSchema = dr.GetSchemaTable(); DataTable dt = new DataTable(); List <DataColumn> listCols = new List <DataColumn>(); if (dtSchema != null) { foreach (DataRow drow in dtSchema.Rows) { string columnName = Convert.ToString(drow["ColumnName"]); DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"])); column.Unique = (bool)drow["IsUnique"]; column.AutoIncrement = (bool)drow["IsAutoIncrement"]; listCols.Add(column); dt.Columns.Add(column); } } // Read rows from DataReader and populate the DataTable while (dr.Read()) { DataRow dataRow = dt.NewRow(); for (int i = 0; i < listCols.Count; i++) { dataRow[((DataColumn)listCols[i])] = dr[i]; } dt.Rows.Add(dataRow); } string[] columnNames = dt.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray(); ResultSet = dt.Select().Select(drr => drr.ItemArray.Select(x => x.ToString()).ToArray()).ToList(); ReportGrid newItem = new ReportGrid { ReportId = reportId, ReportName = item.ReportName, GridColumns = columnNames, GridData = ResultSet, IsActive = !(item.Deleted) }; returnValue.Add(newItem); } } return(GetTypedResponse(teamHttpContext, returnValue)); } catch (Exception) { return(null); } }