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 <AddEditNewUser> > GetUserAsync(TeamHttpContext httpContext, int userid)
{
string connString = Utility.GetConnectionString("PgAdmin4ConnectionString");
List <AddEditNewUser> returnValue = new List <AddEditNewUser>();
PostgresService postgresService = new PostgresService();
List <User> objUser = new List <User>();
using (TeamDbContext dbContext = new TeamDbContext())
{
if (userid != -1)
{
objUser = dbContext.Users.Where(x => x.Deleted == false && x.Id == userid).ToList();
}
else
{
objUser = dbContext.Users.Where(x => x.Deleted == false && x.UserType != UserType.Admin).ToList();
}
}
if (objUser.Count != 0)
{
foreach (var item in objUser)
{
var userstatus = (item.Status == UserStatus.Active) ? true : false;
AddEditNewUser newItem = new AddEditNewUser
{
UserId = item.UserId,
Password = "******",
UserEmail = item.Email,
TableId = item.Id,
IsActive = userstatus,
IsAddPermission = item.IsAdd,
IsEditPermission = item.IsEdit,
IsDeletePermission = item.IsDelete
};
returnValue.Add(newItem);
}
return(returnValue);
}
else
{
return(null);
}
}
private static int GetDataCount(TeamHttpContext teamContext, string sql, DataRequest pagingRequest)
{
PostgresService dl = new PostgresService();
if (pagingRequest.HasDateFiltering)
{
dl.AddDateParam("@startDate", pagingRequest.From);
dl.AddDateParam("@endDate", pagingRequest.To);
}
dl.AddParam("@blankQuery", pagingRequest.IsBlankQuery);
dl.AddLikeParam("@query", pagingRequest.Query);
dl.AddIntParam("@offset", pagingRequest.Offset);
dl.AddIntParam("@limit", pagingRequest.Limit);
return(dl.ExecuteSqlReturnScalar <int>(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);
}
}
