public string getdv(string refid, EbObjectType objtype)
{
EbDataVisualization dsobj = null;
if (refid != null)
{
var resultlist = this.ServiceClient.Get <EbObjectParticularVersionResponse>(new EbObjectParticularVersionRequest {
RefId = refid
});
dsobj = EbSerializers.Json_Deserialize(resultlist.Data[0].Json);
dsobj.Status = resultlist.Data[0].Status;
dsobj.VersionNumber = resultlist.Data[0].VersionNumber;
}
return(EbSerializers.Json_Serialize(dsobj));
}
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());
}
