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 Engine(bool auto_data_analysing)
{
AutoDataAnalysing = auto_data_analysing;
this.Db = Fhr.ProductOffice.Models.DbApi.Create();
Dbc = Bot.DbConnection.Create(Fhr.ProductOffice.Models.DbApi.GetProviderConnectionString());
Companies = new Companies(this);
Products = new Products(this);
CompanyPairs = new ProductIdentifier.CompanyPairs(this);
}
//public int c1 = 0;
//public int c2 = 0;
//public System.Diagnostics.Stopwatch sw1 = new System.Diagnostics.Stopwatch();
//public System.Diagnostics.Stopwatch sw2 = new System.Diagnostics.Stopwatch();
//public System.Diagnostics.Stopwatch sw3 = new System.Diagnostics.Stopwatch();
//public System.Diagnostics.Stopwatch sw4 = new System.Diagnostics.Stopwatch();
//public System.Diagnostics.Stopwatch sw5 = new System.Diagnostics.Stopwatch();
//public System.Diagnostics.Stopwatch sw6 = new System.Diagnostics.Stopwatch();
//public System.Diagnostics.Stopwatch sw7 = new System.Diagnostics.Stopwatch();
//public System.Diagnostics.Stopwatch sw8 = new System.Diagnostics.Stopwatch();
//public System.Diagnostics.Stopwatch sw9 = new System.Diagnostics.Stopwatch();
//public System.Diagnostics.Stopwatch sw10 = new System.Diagnostics.Stopwatch();
/// <summary>
/// Valid LinkId is > 0
/// If a product is not linked, its LinkId == null or < 0 (LinkId may be -Id to differ from all other LinkId's);
/// </summary>
/// <param name="dbc"></param>
/// <returns></returns>
static int get_minimal_free_link_id(Cliver.Bot.DbConnection dbc)
{
int?link_id = (int?)dbc[@"SELECT MIN(a.LinkId + 1)
FROM (SELECT LinkId FROM Products WHERE LinkId>0) a LEFT OUTER JOIN (SELECT LinkId FROM Products WHERE LinkId>0) b ON (a.LinkId + 1 = b.LinkId)
WHERE b.LinkId IS NULL"].GetSingleValue();
if (link_id == null)
{
link_id = 1;
}
return((int)link_id);
}
public static JsonResult Index(DataTables.AspNet.Core.IDataTablesRequest request, Cliver.Bot.DbConnection dbc, string from_sql, Field[] fields, bool ignore_first_column_search = true)
{
try
{
from_sql = " " + from_sql;
//turn around as it is unclear how to pass additional parameters
if (!string.IsNullOrWhiteSpace(request.Columns.First().Search.Value))
{
List <string> wheres = new List <string>();
foreach (string f2c in Regex.Split(request.Columns.First().Search.Value, @"\|", RegexOptions.Singleline))
{
Match m = Regex.Match(f2c, @"(?'DbField'.*?)\=(?'SearchValue'.*)", RegexOptions.Singleline);
if (m.Success)
{
string search_value = m.Groups["SearchValue"].Value;
decimal sv;
if (!decimal.TryParse(search_value, out sv))
{
search_value = "'" + search_value + "'";
}
wheres.Add(m.Groups["DbField"].Value + "=" + search_value);
}
m = Regex.Match(f2c, @"(?'DbField'.*?)\s*LIKE\s*(?'SearchValue'.*)", RegexOptions.Singleline);
if (m.Success)
{
string search_value = m.Groups["SearchValue"].Value;
decimal sv;
if (!decimal.TryParse(search_value, out sv))
{
search_value = "'" + search_value + "'";
}
wheres.Add(m.Groups["DbField"].Value + " LIKE " + search_value);
}
}
if (wheres.Count > 0)
{
string select_sql = " WHERE " + string.Join(" AND ", wheres);
from_sql += select_sql;
}
}
int total_count = (int)dbc.Get("SELECT COUNT(ISNULL(" + fields[0].Entity + ", 0))" + from_sql).GetSingleValue();
int filtered_count = total_count;
string where_sql = null;
if (!string.IsNullOrEmpty(request.Search.Value))
{
string search = Regex.Replace(request.Search.Value.ToLower(), @"\'|\%|\\|_", @"\$0", RegexOptions.Compiled | RegexOptions.Singleline);
List <string> conditions = new List <string>();
foreach (Field f in fields.Where(r => r.Searchable))
{
conditions.Add("" + f.Entity + " LIKE '%" + search + "%'");
}
if (conditions.Count > 0)
{
if (from_sql.Contains(" WHERE "))
{
where_sql += " AND ";
}
else
{
where_sql += " WHERE ";
}
where_sql += "(" + string.Join(" OR ", conditions) + @" ESCAPE '\')";
}
}
if (!string.IsNullOrWhiteSpace(where_sql))
{
filtered_count = (int)dbc.Get("SELECT COUNT(ISNULL(" + fields[0].Entity + ", 0))" + from_sql + where_sql).GetSingleValue();
}
Dictionary <string, int> of2nothing = new Dictionary <string, int>();
List <string> ofs = new List <string>();
foreach (var column in request.Columns)
{
if (ignore_first_column_search)
{
ignore_first_column_search = false;
continue;
}
if (column.Sort == null)
{
continue;
}
string f = column.Field;
int n;
if (int.TryParse(f, out n))
{
f = fields[n].Entity;
}
if (column.Sort.Direction == DataTables.AspNet.Core.SortDirection.Ascending)
{
ofs.Add(f);
}
else
{
ofs.Add(f + " DESC");
}
of2nothing[f] = 0;
}
foreach (Field field in fields)
{
if (field.Order == Field.OrderMode.NONE)
{
continue;
}
if (of2nothing.ContainsKey(field.Name))
{
continue;
}
if (field.Order == Field.OrderMode.ASC)
{
ofs.Add(field.Entity);
}
else
{
ofs.Add(field.Entity + " DESC");
}
of2nothing[field.Name] = 0;
}
string order_sql = " ORDER BY ";
if (ofs.Count > 0)
{
order_sql += string.Join(", ", ofs);
}
else
{
order_sql += fields[0].Entity;
}
string fields_sql = string.Join(",", (from r in fields select r.Expression != null ? r.Expression + " AS " + r.Name : r.Name));
string sql = "SELECT " + fields_sql + from_sql + where_sql + order_sql + " OFFSET " + request.Start + " ROWS FETCH NEXT " + request.Length + " ROWS ONLY";
List <object[]> array = new List <object[]>();
using (DbDataReader r = (DbDataReader)dbc.Get(sql).GetReader())
{
string search = null;
if (!string.IsNullOrEmpty(request.Search.Value))
{
search = Regex.Escape(request.Search.Value);
}
while (r.Read())
{
object[] vs = new object[fields.Length];
r.GetValues(vs);
if (search != null)
{
for (int i = 0; i < fields.Length; i++)
{
if (fields[i].Searchable)
{
if (!(vs[i] is System.DBNull))
{
vs[i] = Regex.Replace((string)vs[i], search, @"<span class='match'>$0</span>", RegexOptions.Compiled | RegexOptions.IgnoreCase);
}
}
}
}
array.Add(vs);
}
}
DataTables.AspNet.Mvc5.DataTablesResponse response = DataTables.AspNet.Mvc5.DataTablesResponse.Create(request, total_count, filtered_count, array);
return(new DataTables.AspNet.Mvc5.DataTablesJsonResult(response, JsonRequestBehavior.AllowGet));
}
catch (Exception e)
{
DataTables.AspNet.Mvc5.DataTablesResponse response = DataTables.AspNet.Mvc5.DataTablesResponse.Create(request, e.Message);
return(new DataTables.AspNet.Mvc5.DataTablesJsonResult(response, JsonRequestBehavior.AllowGet));
}
}
public static JsonResult Index(DataTables.AspNet.Core.IDataTablesRequest request, DbConnection connection, string from_sql, Field[] fields, bool ignore_first_column_search = true)
{
Cliver.Bot.DbConnection dbc = Cliver.Bot.DbConnection.CreateFromNativeConnection(connection);
return(Index(request, dbc, from_sql, fields, ignore_first_column_search));
}
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()));
}
