public ActionResult TableJson([ModelBinder(typeof(DataTables.AspNet.Mvc5.ModelBinder))] DataTables.AspNet.Core.IDataTablesRequest request) { JqueryDataTable.Field[] fields = new JqueryDataTable.Field[] { new JqueryDataTable.Field("Id"), new JqueryDataTable.Field("Name", true), }; JsonResult jr = JqueryDataTable.Index(request, db.Database.Connection, "FROM Companies", fields); List <object[]> cs = (List <object[]>)(((dynamic)jr.Data).Data); Bot.DbConnection dbc = Cliver.Bot.DbConnection.CreateFromNativeConnection(db.Database.Connection); for (int i = 0; i < cs.Count; i++) { object[] fs = cs[i]; DateTime t = Cliver.Bot.DbSettings.Get <DateTime>(dbc, Cliver.ProductIdentifier.SettingsKey.SCOPE, Cliver.ProductIdentifier.SettingsKey.COMPANY + (int)fs[0] + Cliver.ProductIdentifier.SettingsKey.ANALYSIS_TIME); Array.Resize(ref fs, fs.Length + 1); if (t != default(DateTime)) { fs[fs.Length - 1] = t.ToString("yyyy-MM-dd HH:mm:ss"); } else { fs[fs.Length - 1] = "no analysis was done yet"; } cs[i] = fs; } //cs.Insert(0, new object[] { ProductIdentifier.Configuration.NO_COMPANY_DEPENDENT, "-- DEFAULT --", "" }); return(jr); }
public ActionResult TableJson([ModelBinder(typeof(DataTables.AspNet.Mvc5.ModelBinder))] DataTables.AspNet.Core.IDataTablesRequest request) { JqueryDataTable.Field[] fields = new JqueryDataTable.Field[] { new JqueryDataTable.Field("Id"), new JqueryDataTable.Field("UpdateTime"), new JqueryDataTable.Field("ImageUrls", false), new JqueryDataTable.Field("Name", true), new JqueryDataTable.Field("Category"), new JqueryDataTable.Field("Sku", true), new JqueryDataTable.Field("Url"), //new JqueryDataTable.Field("Url", false, 1, "CASE MainProductId<0 THEN Id ELSE MainProductId"), }; string from_sql; string expand_groups_ = request.Columns.ToList()[1].Search.Value; if (expand_groups_ != null && Regex.IsMatch(expand_groups_, @"ExpandGroups\s*=\s*true", RegexOptions.Singleline | RegexOptions.IgnoreCase)) { from_sql = "FROM Products"; } else { from_sql = @"FROM Products INNER JOIN (SELECT MIN(Id) AS Gid FROM Products WHERE MainProductId>=0 AND MainProductId NOT IN (SELECT Id AS Gid FROM Products WHERE MainProductId=Id) GROUP BY MainProductId UNION SELECT Id AS Gid FROM Products WHERE MainProductId=Id UNION SELECT Id AS Gid FROM Products WHERE MainProductId<0 ) a ON a.Gid=Products.Id"; } return(JqueryDataTable.Index(request, db.Database.Connection, from_sql, fields)); }
public ActionResult TableJson([ModelBinder(typeof(DataTables.AspNet.Mvc5.ModelBinder))] DataTables.AspNet.Core.IDataTablesRequest request) { JqueryDataTable.Field[] fields = new JqueryDataTable.Field[] { new JqueryDataTable.Field("Id", false, JqueryDataTable.Field.OrderMode.DESC), new JqueryDataTable.Field("Type"), new JqueryDataTable.Field("Source", true), new JqueryDataTable.Field("Time"), new JqueryDataTable.Field("Value", true), new JqueryDataTable.Field("Details", true) }; JsonResult jr = JqueryDataTable.Index(request, db.Connection, "FROM Messages", fields); foreach (var r in ((dynamic)jr.Data).Data) { if (!String.IsNullOrEmpty(Convert.ToString(r[1]))) { r[1] = ((Cliver.Bot.Log.MessageType)r[1]).ToString(); } if (!String.IsNullOrEmpty(Convert.ToString(r[4]))) { r[4] = PrepareField.Trim(r[4], 100); } if (!String.IsNullOrEmpty(Convert.ToString(r[5]))) { r[5] = PrepareField.Trim(r[5], 50); } } return(jr); }
public ActionResult TableJson([ModelBinder(typeof(DataTables.AspNet.Mvc5.ModelBinder))] DataTables.AspNet.Core.IDataTablesRequest request) { JqueryDataTable.Field[] fields = new JqueryDataTable.Field[] { new JqueryDataTable.Field("Id", false, JqueryDataTable.Field.OrderMode.ASC), new JqueryDataTable.Field("Crawler", true, JqueryDataTable.Field.OrderMode.NONE, "Id"), new JqueryDataTable.Field("State"), new JqueryDataTable.Field("Command"), new JqueryDataTable.Field("_SessionStartTime"), new JqueryDataTable.Field("_LastSessionState"), new JqueryDataTable.Field("_NextStartTime"), new JqueryDataTable.Field("_LastStartTime"), new JqueryDataTable.Field("_LastEndTime"), new JqueryDataTable.Field("RunTimeSpan") }; JsonResult jr = JqueryDataTable.Index(request, db.Connection, "FROM Crawlers", fields); foreach (var r in ((dynamic)jr.Data).Data) { if (!String.IsNullOrEmpty(Convert.ToString(r[2]))) { r[2] = ((Cliver.CrawlerHost.Crawler.State)Convert.ToInt32(r[2])).ToString(); } if (!String.IsNullOrEmpty(Convert.ToString(r[3]))) { r[3] = ((Cliver.CrawlerHost.Crawler.Command)Convert.ToInt32(r[3])).ToString(); } if (!String.IsNullOrEmpty(Convert.ToString(r[5]))) { r[5] = ((Cliver.CrawlerHost.Crawler.SessionState)Convert.ToInt32(r[5])).ToString(); } } return(jr); }
public ActionResult TableJson([ModelBinder(typeof(DataTables.AspNet.Mvc5.ModelBinder))] DataTables.AspNet.Core.IDataTablesRequest request) { string database_ = request.Columns.ToList()[1].Search.Value; if (database_ == null) { throw new Exception("No database specified"); } Match m = Regex.Match(database_, @"Database\s*=\s*(.+)", RegexOptions.Singleline | RegexOptions.IgnoreCase); if (!m.Success) { throw new Exception("No database found: " + database_); } string database = m.Groups[1].Value; Session["Database"] = database; Cliver.Bot.DbConnection dbc = GetDbc(database); if (null == dbc.Get("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'Settings'").GetSingleValue()) { //this way is a plain error message is shown return(Content("No table 'Settings' found in " + database_)); } JqueryDataTable.Field[] fields = new JqueryDataTable.Field[] { new JqueryDataTable.Field("Scope", true), new JqueryDataTable.Field("[Key]", true), new JqueryDataTable.Field("SetTime", false), }; return(JqueryDataTable.Index(request, dbc, "FROM Settings", fields)); }
public ActionResult TableJson([ModelBinder(typeof(DataTables.AspNet.Mvc5.ModelBinder))] DataTables.AspNet.Core.IDataTablesRequest request) { JqueryDataTable.Field[] fields = new JqueryDataTable.Field[] { new JqueryDataTable.Field("Id"), new JqueryDataTable.Field("ParentId"), new JqueryDataTable.Field("Name", true) }; return(JqueryDataTable.Index(request, db.Database.Connection, "FROM LinkCategories", fields)); }
public ActionResult TableJson([ModelBinder(typeof(DataTables.AspNet.Mvc5.ModelBinder))] DataTables.AspNet.Core.IDataTablesRequest request) { JqueryDataTable.Field[] fields = new JqueryDataTable.Field[] { new JqueryDataTable.Field("Id", false, JqueryDataTable.Field.OrderMode.NONE), new JqueryDataTable.Field("Name", true, JqueryDataTable.Field.OrderMode.ASC), new JqueryDataTable.Field("Url", true), new JqueryDataTable.Field("Comment", true), new JqueryDataTable.Field("CrawlerId", true) }; JsonResult jr = JqueryDataTable.Index(request, db.Database.Connection, "FROM Companies", fields); return(jr); }
public ActionResult TableJson2([ModelBinder(typeof(DataTables.AspNet.Mvc5.ModelBinder))] DataTables.AspNet.Core.IDataTablesRequest request) { Bot.DbConnection dbc = Cliver.Bot.DbConnection.CreateFromNativeConnection(db.Database.Connection); //dbc.Get("DROP TABLE [#ProductIdentifierConfig2]").Execute(); string fs = ""; if (db.Companies.Count() > 0) { fs = ", [" + string.Join("] CHAR(2) NOT NULL DEFAULT '', [", db.Companies.Select(c => c.Name)) + "] CHAR(2) NOT NULL DEFAULT '' "; } string sql = @"IF OBJECT_ID('tempdb..[#ProductIdentifierConfig2]') IS NOT NULL DROP TABLE [#ProductIdentifierConfig2] ELSE CREATE TABLE [#ProductIdentifierConfig2] ([C1IdC2Id] NVARCHAR (100) NOT NULL" + fs + ")"; dbc.Get(sql).Execute(); List <string> keys = Cliver.Bot.DbSettings.GetKeys(dbc, Cliver.ProductIdentifier.SettingsKey.SCOPE, Cliver.ProductIdentifier.SettingsKey.CATEGORY_MAP + "%"); foreach (string k in keys) { Match m = Regex.Match(k, @"(\d+),(\d+)$", RegexOptions.Singleline); int c1_i = int.Parse(m.Groups[1].Value); int c2_i = int.Parse(m.Groups[2].Value); string c1_n = db.Companies.Where(c => c.Id == c1_i).Select(c => c.Name).FirstOrDefault(); if (c1_n == null) { continue; } string c2_n = db.Companies.Where(c => c.Id == c2_i).Select(c => c.Name).FirstOrDefault(); if (c2_n == null) { continue; } dbc.Get(@"INSERT INTO [#ProductIdentifierConfig2] (C1IdC2Id,[" + c1_n + "],[" + c2_n + "]) VALUES('" + m.Groups[0].Value + "','+','+')").Execute(); } List <JqueryDataTable.Field> fields = new List <JqueryDataTable.Field> { new JqueryDataTable.Field("C1IdC2Id", false) }; fields.AddRange(from c in db.Companies.ToList() select new JqueryDataTable.Field("[" + c.Name + "]", true, 0, null)); return(JqueryDataTable.Index(request, db.Database.Connection, "FROM #ProductIdentifierConfig2", fields.ToArray())); }
public ContentResult GetJsonForDataTable(JQDTParams param) { try { JqueryDataTable jsonData = null; var camelCaseFormatter = new JsonSerializerSettings(); camelCaseFormatter.ContractResolver = new CamelCasePropertyNamesContractResolver(); if ((param.draw % 2) == 1) { using (StreamReader r = new StreamReader("F:/MyProjects/02 PITS/MyDiary/src/01 Presentation/UI/Mvc/Controllers/Test/dataTable.json")) { string json = r.ReadToEnd(); jsonData = JsonConvert.DeserializeObject <JqueryDataTable>(json, camelCaseFormatter); if (param.length > 0) { jsonData.Data = jsonData.Data .ToList() .Skip(param.start) .Take(param.length) .ToList(); } //jsonData.RecordsFiltered = jsonData.Data.Count(); jsonData.Draw = param.draw; } } else { jsonData = new JqueryDataTable() { Data = new List <Employee>() }; } return(Content(JsonConvert.SerializeObject(jsonData), "application/json")); } catch (Exception ex) { throw; } }
public ActionResult TableJson([ModelBinder(typeof(DataTables.AspNet.Mvc5.ModelBinder))] DataTables.AspNet.Core.IDataTablesRequest request) { try { string table = Cliver.CrawlerHostWatchDog.WatchDog.GetReportsTempTable(db.Connection); JqueryDataTable.Field[] fields = new JqueryDataTable.Field[] { new JqueryDataTable.Field("Source", true), new JqueryDataTable.Field("SourceType", true), new JqueryDataTable.Field("MessageType", true), new JqueryDataTable.Field("Value", true), // new JqueryDataTable.Field("Details", true) }; JsonResult jr = JqueryDataTable.Index(request, db.Connection, "FROM " + table, fields); return(jr); } catch (Exception e) { Errors.Add(e.Message); } return(null); }
public ActionResult TableJson([ModelBinder(typeof(DataTables.AspNet.Mvc5.ModelBinder))] DataTables.AspNet.Core.IDataTablesRequest request) { //JqueryDataTable.Field[] fields = new JqueryDataTable.Field[] { // new JqueryDataTable.Field("Id"), // new JqueryDataTable.Field("LinkCategory", false, "b.Name"), // new JqueryDataTable.Field("CreateTime"), // new JqueryDataTable.Field("Name", true), // new JqueryDataTable.Field("Sku", true), // new JqueryDataTable.Field("Url") //}; //return JqueryDataTable.Index(request, db.Database, "FROM Products a INNER JOIN LinkCategories b ON a.IdentityCategoryId=b.Id", fields); JqueryDataTable.Field[] fields = new JqueryDataTable.Field[] { new JqueryDataTable.Field("Id"), new JqueryDataTable.Field("Category"), new JqueryDataTable.Field("UpdateTime"), new JqueryDataTable.Field("Name", true), new JqueryDataTable.Field("Sku", true), new JqueryDataTable.Field("Url") }; return(JqueryDataTable.Index(request, db.Database.Connection, "FROM Products", fields)); }
public ActionResult TableJson([ModelBinder(typeof(DataTables.AspNet.Mvc5.ModelBinder))] DataTables.AspNet.Core.IDataTablesRequest request) { // string sql = @"SELECT //CASE WHEN a.LinkId>=0 THEN a.LinkId END AS LinkId," // + string.Join(",", (from c in db.Companies select "MAX([" + c.Name + "_ProductId]) AS [" + c.Name + "_ProductId], MAX([" + c.Name + "_ProductName]) AS [" + c.Name + "_ProductName]")) // + @" INTO #ProductLinks FROM ( //SELECT //CASE WHEN LinkId>0 THEN LinkId ELSE -Id END AS LinkId," // + string.Join(",", (from c in db.Companies select "CASE WHEN CompanyId = " + c.Id + " THEN Id END AS [" + c.Name + "_ProductId], CASE WHEN CompanyId = " + c.Id + " THEN Name END AS [" + c.Name + "_ProductName]")) // + @" FROM Products //) a //GROUP BY a.LinkId"; string sql = @"SELECT LinkId," + string.Join(",", (from c in db.Companies select "MAX([" + c.Name + "_ProductName]) AS [" + c.Name + "_ProductName]")) + @" INTO #ProductLinks FROM ( SELECT CASE WHEN LinkId>0 THEN LinkId ELSE -Id END AS LinkId," + string.Join(",", (from c in db.Companies select "CASE WHEN CompanyId = " + c.Id + " THEN Name END AS [" + c.Name + "_ProductName]")) + @" FROM Products ) a GROUP BY a.LinkId"; //SELECT //CASE WHEN a.LinkId>=0 THEN a.LinkId END AS LinkId, MAX(A) AS A, MAX(B) AS B //FROM ( // SELECT // CASE WHEN LinkId>0 THEN LinkId ELSE -Id END AS LinkId, Id AS A, NULL AS B // FROM Products // WHERE CompanyId=1 // UNION // SELECT // CASE WHEN LinkId>0 THEN LinkId ELSE -Id END AS LinkId, NULL, Id // FROM Products // WHERE CompanyId=2 //) a //GROUP BY a.LinkId //SELECT //CASE WHEN a.LinkId>=0 THEN a.LinkId END AS LinkId, MAX(A_ProductId) AS A_ProductId, MAX(A_ProductName) AS A_ProductName, MAX(B_ProductId) AS B_ProductId, MAX(B_ProductName) AS B_ProductName //FROM ( // SELECT // CASE WHEN LinkId>0 THEN LinkId ELSE -Id END AS LinkId, Id AS A_ProductId, Name AS A_ProductName, NULL AS B_ProductId, NULL AS B_ProductName // FROM Products // WHERE CompanyId=1 // UNION // SELECT // CASE WHEN LinkId>0 THEN LinkId ELSE -Id END AS LinkId, NULL, NULL, Id, Name // FROM Products // WHERE CompanyId=2 //) a //GROUP BY a.LinkId //SELECT MAX(a.COUNT_) FROM (SELECT COUNT(Id) AS COUNT_, LinkId FROM Products WHERE LinkId>0 GROUP BY LinkId) a //SELECT * FROM Products WHERE LinkId=(SELECT CASE WHEN LinkId>0 THEN LinkId ELSE -1 END FROM Products WHERE Id=1); Cliver.Bot.DbConnection dbc = Cliver.Bot.DbConnection.CreateFromNativeConnection(db.Database.Connection); dbc.Get(sql).Execute(); List <JqueryDataTable.Field> fields = new List <JqueryDataTable.Field> { new JqueryDataTable.Field("LinkId", false) }; fields.AddRange(from c in db.Companies.ToList() select new JqueryDataTable.Field("[" + c.Name + "_ProductName]", true, 0, null)); return(JqueryDataTable.Index(request, db.Database.Connection, "FROM #ProductLinks", fields.ToArray())); }