private EbDataVisualization getDVObject(EbDataVisualization dvobj) { //DataSourceColumnsResponse columnresp = null; DataSourceColumnsResponse columnresp = this.Redis.Get <DataSourceColumnsResponse>(string.Format("{0}_columns", dvobj.DataSourceRefId)); if (columnresp == null || columnresp.Columns.Count == 0) { columnresp = this.ServiceClient.Get <DataSourceColumnsResponse>(new DataSourceColumnsRequest { RefId = dvobj.DataSourceRefId, TenantAccountId = ViewBag.cid }); } dvobj.AfterRedisGet(this.Redis); var __columns = (columnresp.Columns.Count > 1) ? columnresp.Columns[1] : columnresp.Columns[0]; int _pos = __columns.Count + 100; dvobj.Columns = new DVColumnCollection(); // Add Serial & Checkbox //dvobj.Columns.Add(new DVNumericColumn { Name = "serial", sTitle = "#", Type = DbType.Int64, bVisible = true, sWidth = "10px", Pos = -2 }); //dvobj.Columns.Add(new DVBooleanColumn { Name = "checkbox", sTitle = "checkbox", Type = DbType.Boolean, bVisible = false, sWidth = "10px", Pos = -1 }); foreach (EbDataColumn column in __columns) { DVBaseColumn _col = null; if (column.Type == DbType.String) { _col = new DVStringColumn { Data = column.ColumnIndex, Name = column.ColumnName, sTitle = column.ColumnName, Type = column.Type, bVisible = true, sWidth = "100px", Pos = _pos, ClassName = "tdheight" } } ; else if (column.Type == DbType.Int16 || column.Type == DbType.Int32 || column.Type == DbType.Int64 || column.Type == DbType.Double || column.Type == DbType.Decimal || column.Type == DbType.VarNumeric) { _col = new DVNumericColumn { Data = column.ColumnIndex, Name = column.ColumnName, sTitle = column.ColumnName, Type = column.Type, bVisible = true, sWidth = "100px", Pos = _pos, ClassName = "tdheight dt-body-right" } } ; else if (column.Type == DbType.Boolean) { _col = new DVBooleanColumn { Data = column.ColumnIndex, Name = column.ColumnName, sTitle = column.ColumnName, Type = column.Type, bVisible = true, sWidth = "100px", Pos = _pos, ClassName = "tdheight" } } ; else if (column.Type == DbType.DateTime || column.Type == DbType.Date || column.Type == DbType.Time) { _col = new DVDateTimeColumn { Data = column.ColumnIndex, Name = column.ColumnName, sTitle = column.ColumnName, Type = column.Type, bVisible = true, sWidth = "100px", Pos = _pos, ClassName = "tdheight" } } ; dvobj.Columns.Add(_col); } dvobj.DSColumns = dvobj.Columns; return(dvobj); } }
public DataSourceColumnsResponse Any(DataVisColumnsRequest request) { EbDataVisualization _dV = request.EbDataVisualization; _dV.AfterRedisGet(this.Redis as RedisClient); var _ds = _dV.EbDataSource; string _dsRedisKey = string.Format("{0}_columns", _dV.DataSourceRefId); EbDataSet _dataset = null; bool _isPaged = false; DataSourceColumnsResponse resp = this.Redis.Get <DataSourceColumnsResponse>(_dsRedisKey); if (resp == null || resp.Columns == null || resp.Columns.Count == 0) { resp = new DataSourceColumnsResponse(); resp.Columns = new List <ColumnColletion>(); if (_ds != null) { Log.Info(">>>>>>>>>>>>>>>>>>>>>>>> dscolumns Sql: " + _ds.SqlDecoded()); string _sql = _ds.SqlDecoded().Replace("@and_search", string.Empty).Replace("@orderby", "1"); _isPaged = (_sql.ToLower().Contains("@offset") && _sql.ToLower().Contains("@limit")); var parameters = new List <System.Data.Common.DbParameter>(); if (_isPaged) { parameters.AddRange(new System.Data.Common.DbParameter[] { this.TenantDbFactory.ObjectsDB.GetNewParameter("@limit", System.Data.DbType.Int32, 0), this.TenantDbFactory.ObjectsDB.GetNewParameter("@offset", System.Data.DbType.Int32, 0) }); } if (request.Params != null) { foreach (Dictionary <string, string> param in request.Params) { parameters.Add(this.TenantDbFactory.ObjectsDB.GetNewParameter(string.Format("@{0}", param["name"]), (System.Data.DbType)Convert.ToInt32(param["type"]), param["value"])); } } Log.Info(">>>>>>>>>>>>>>>>>>>>>>>> dscolumns Parameters Added"); try { _dataset = this.TenantDbFactory.ObjectsDB.DoQueries(_sql, parameters.ToArray()); foreach (var dt in _dataset.Tables) { resp.Columns.Add(dt.Columns); } resp.IsPaged = _isPaged; this.Redis.Set <DataSourceColumnsResponse>(_dsRedisKey, resp); } catch (Exception e) { Log.Info(">>>>>>>>>>>>>>>>>>>>>>>> dscolumns e.Message: " + e.Message); this.Redis.Remove(_dsRedisKey); } } } return(resp); }
public DataSourceDataResponse Any(DataVisDataRequest request) { this.Log.Info("data request"); DataSourceDataResponse dsresponse = null; EbDataVisualization _dV = request.EbDataVisualization; //if (request.WhichConsole == "uc") // _dVSet = this.Redis.Get<EbDataVisualizationSet>(request.RefId + request.UserId.ToString()); //else //dc // _dVSet = this.Redis.Get<EbDataVisualizationSet>(request.RefId); _dV.AfterRedisGet(this.Redis as RedisClient); string _sql = null; if (_dV.EbDataSource != null) { StringBuilder _sb = new StringBuilder(); if (request.TFilters != null) { foreach (Dictionary <string, string> _dic in request.TFilters) { var op = _dic["o"]; var col = _dic["c"]; var val = _dic["v"]; if (op == "x*") { _sb.Append(string.Format("LOWER({0})::text LIKE LOWER('{1}%') ", col, val)); } else if (op == "*x") { _sb.Append(string.Format("LOWER({0})::text LIKE LOWER('%{1}') ", col, val)); } else if (op == "*x*") { _sb.Append(string.Format("LOWER({0})::text LIKE LOWER('%{1}%') ", col, val)); } else if (op == "=") { _sb.Append(string.Format("LOWER({0}::text) = LOWER('{1}') ", col, val)); } else { _sb.Append(string.Format("{0} {1} '{2}' ", col, op, val)); } } } string __innerSql = _dV.EbDataSource.SqlDecoded(); string _where = (_sb.Length > 0) ? "WHERE " + string.Join(" AND ", _sb) : string.Empty; string _orderby = (string.IsNullOrEmpty(request.OrderByCol)) ? "1" : string.Format("{0} {1}", request.OrderByCol, ((request.OrderByDir == 2) ? "DESC" : "ASC")); _sql = string.Format("WITH __OUTER99 AS ({0}) SELECT * FROM __OUTER99 {1} {2}", __innerSql, _where, _orderby); this.Log.Info("_ds *****" + _sql); } var parameters = new List <System.Data.Common.DbParameter>(); bool _isPaged = (_sql.ToLower().Contains("@offset") && _sql.ToLower().Contains("@limit")); if (_isPaged) { parameters.AddRange(new System.Data.Common.DbParameter[] { this.TenantDbFactory.ObjectsDB.GetNewParameter("@limit", System.Data.DbType.Int32, request.Length), this.TenantDbFactory.ObjectsDB.GetNewParameter("@offset", System.Data.DbType.Int32, request.Start), }); } if (request.Params != null) { foreach (Dictionary <string, string> param in request.Params) { parameters.Add(this.TenantDbFactory.ObjectsDB.GetNewParameter(string.Format("@{0}", param["name"]), (System.Data.DbType)Convert.ToInt32(param["type"]), param["value"])); } } var _dataset = _dV.DoQueries4DataVis(_sql, this.TenantDbFactory, parameters.ToArray()); //-- int _recordsTotal = 0, _recordsFiltered = 0; if (_isPaged) { Int32.TryParse(_dataset.Tables[0].Rows[0][0].ToString(), out _recordsTotal); Int32.TryParse(_dataset.Tables[0].Rows[0][0].ToString(), out _recordsFiltered); } _recordsTotal = (_recordsTotal > 0) ? _recordsTotal : _dataset.Tables[0].Rows.Count; _recordsFiltered = (_recordsFiltered > 0) ? _recordsFiltered : _dataset.Tables[0].Rows.Count; //-- dsresponse = new DataSourceDataResponse { Draw = request.Draw, Data = (_dataset.Tables.Count > 1) ? _dataset.Tables[1].Rows : _dataset.Tables[0].Rows, RecordsTotal = _recordsTotal, RecordsFiltered = _recordsFiltered }; this.Log.Info("dsresponse*****" + dsresponse.Data); return(dsresponse); }
public IActionResult dvChart(string objid, EbObjectType objtype) { ViewBag.ServiceUrl = this.ServiceClient.BaseUri; var typeArray = typeof(EbDataVisualizationObject).GetTypeInfo().Assembly.GetTypes(); var _jsResult = CSharpToJs.GenerateJs <EbDataVisualizationObject>(BuilderType.DVBuilder, typeArray); ViewBag.Meta = _jsResult.Meta; ViewBag.JsObjects = _jsResult.JsObjects; ViewBag.EbObjectType = _jsResult.EbObjectTypes; if (objid != null) { var resultlist = this.ServiceClient.Get <EbObjectExploreObjectResponse>(new EbObjectExploreObjectRequest { Id = Convert.ToInt32(objid) }); var rlist = resultlist.Data; foreach (var element in rlist) { ObjectLifeCycleStatus[] array = (ObjectLifeCycleStatus[])Enum.GetValues(typeof(ObjectLifeCycleStatus)); List <ObjectLifeCycleStatus> lifeCycle = new List <ObjectLifeCycleStatus>(array); ViewBag.LifeCycle = lifeCycle; ViewBag.IsNew = "false"; ViewBag.ObjectName = element.Name; ViewBag.ObjectDesc = element.Description; ViewBag.Status = element.Status; ViewBag.VersionNumber = element.VersionNumber; ViewBag.Icon = "fa fa-database"; ViewBag.ObjType = (int)objtype; ViewBag.Refid = element.RefId; ViewBag.Majorv = element.Dashboard_Tiles.MajorVersionNumber; ViewBag.Minorv = element.Dashboard_Tiles.MinorVersionNumber; ViewBag.Patchv = element.Dashboard_Tiles.PatchVersionNumber; EbDataVisualization dsobj = null; if (String.IsNullOrEmpty(element.Json_wc) && !String.IsNullOrEmpty(element.Json_lc)) { ViewBag.ReadOnly = true; if (objtype == EbObjectType.TableVisualization) { dsobj = EbSerializers.Json_Deserialize <EbTableVisualization>(element.Json_lc); } else if (objtype == EbObjectType.ChartVisualization) { dsobj = EbSerializers.Json_Deserialize <EbChartVisualization>(element.Json_lc); } } else { ViewBag.ReadOnly = false; if (objtype == EbObjectType.TableVisualization) { dsobj = EbSerializers.Json_Deserialize <EbTableVisualization>(element.Json_wc); } else if (objtype == EbObjectType.ChartVisualization) { dsobj = EbSerializers.Json_Deserialize <EbChartVisualization>(element.Json_wc); } } dsobj.AfterRedisGet(this.Redis); ViewBag.dvObject = dsobj; } } return(View()); }