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)
{
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)
{
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 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()));
}