public DataTable ExecuteGetNullTable(int dbId, string sql)
{
string dbType = Fun.GetDataBaseType();
switch (dbType)
{
case "Sql":
var orderPlace = sql.ToLower().Replace("\r\n", " ").IndexOf(" order ");
if (orderPlace > 1)
{
sql = sql.Substring(0, orderPlace);
}
break;
}
string withStr = "";
string WithStartStr = "-----WithStart-----";
string WithEndStr = "-----WithEnd-----";
int WithStartP = sql.IndexOf(WithStartStr);
int WithEndP = sql.IndexOf(WithEndStr);
if (WithStartP > -1 && WithEndP > -1 && WithEndP > WithStartP)
{
withStr = sql.Substring(WithStartStr.Length, WithEndP - WithStartP - WithStartStr.Length);
sql = sql.Substring(WithEndP + WithEndStr.Length);
}
string sqlStr = withStr + "SELECT * FROM ( " + sql + " ) T WHERE 1<>1";
return(ExecuteSqlToTable(dbId, sqlStr));
}
public int QueryCount(string loginKey, ref ErrorInfo err, string queryCode, string whereJsonStr, IList <QueryPara> paraList, ref string sqlStr)
{
using (DBEntities db = new DBEntities())
{
var query = db.YL_QUERY.SingleOrDefault(x => x.CODE == queryCode);
TQuery ent = Fun.ClassToCopy <YL_QUERY, TQuery>(query);
GlobalUser gu = Global.GetUser(loginKey);
string whereStr = "";
ent.QUERY_CONF = MakeSql(gu, ent.QUERY_CONF, null, whereJsonStr, paraList, ref whereStr, Fun.GetDataBaseType());
string withStr = "";
string WithStartStr = "-----WithStart-----";
string WithEndStr = "-----WithEnd-----";
int WithStartP = ent.QUERY_CONF.IndexOf(WithStartStr);
int WithEndP = ent.QUERY_CONF.IndexOf(WithEndStr);
if (WithStartP > -1 && WithEndP > -1 && WithEndP > WithStartP)
{
withStr = ent.QUERY_CONF.Substring(WithStartStr.Length, WithEndP - WithStartP - WithStartStr.Length);
ent.QUERY_CONF = ent.QUERY_CONF.Substring(WithEndP + WithEndStr.Length);
}
if (!string.IsNullOrEmpty(whereStr))
{
ent.QUERY_CONF = withStr + "SELECT * FROM ( " + ent.QUERY_CONF + " ) TMP where " + whereStr;
}
else
{
ent.QUERY_CONF = withStr + "SELECT * FROM ( " + ent.QUERY_CONF + " ) TMP";
}
var reInt = 0;
try
{
sqlStr = ent.QUERY_CONF;
reInt = ExecuteCount(query.DB_SERVER_ID.Value, ent.QUERY_CONF);
}
catch (Exception e)
{
err.IsError = true;
err.Message = e.Message;
}
return(reInt);
}
}
public DataTable QueryDataTable(string loginKey, ref ErrorInfo err, string queryCode, string orderStr, string whereStr, IList <QueryPara> paraList, ref string sqlStr)
{
DataTable reDt = new DataTable();
using (DBEntities db = new DBEntities())
{
var query = db.YL_QUERY.SingleOrDefault(x => x.CODE == queryCode);
TQuery ent = Fun.ClassToCopy <YL_QUERY, TQuery>(query);
GlobalUser gu = Global.GetUser(loginKey);
ent.QUERY_CONF = MakeSql(gu, ent.QUERY_CONF, orderStr, whereStr, paraList, ref whereStr, Fun.GetDataBaseType());
reDt = ExecuteSqlAll(query.DB_SERVER_ID.Value, ent.QUERY_CONF, orderStr, whereStr, ref sqlStr);
reDt.TableName = "tables1";
if (ent.QueryCfg != null)
{
foreach (var par in ent.QueryCfg)
{
if (reDt.Columns[par.FieldName] != null)
{
if (par.Show)
{
reDt.Columns[par.FieldName].Caption = par.Alias;
//reDt.Columns[par.FieldName].ColumnName = par.Alias;
}
else
{
reDt.Columns.Remove(par.FieldName);
}
}
}
}
return(reDt);
}
}
public DataTable QueryExecute(string loginKey, ref ErrorInfo err, string queryCode, int pageIndex, int pageSize, string orderStr, string whereJsonStr, IList <QueryPara> paraList, ref string sqlStr)
{
DataTable reDt = new DataTable();
GlobalUser gu = Global.GetUser(loginKey);
if (gu == null)
{
err.IsError = true;
err.Message = "用户【" + loginKey + "】登录超时，请重新登录";
return(reDt);
}
using (DBEntities db = new DBEntities())
{
var query = db.YL_QUERY.SingleOrDefault(x => x.CODE == queryCode);
TQuery ent = Fun.ClassToCopy <YL_QUERY, TQuery>(query);
IList <QueryCfg> cfg = JSON.EncodeToEntity <IList <QueryCfg> >(query.QUERY_CFG_JSON);
string whereStr = "";
ent.QUERY_CONF = MakeSql(gu, ent.QUERY_CONF, orderStr, whereJsonStr, paraList, ref whereStr, Fun.GetDataBaseType());
try
{
sqlStr = ent.QUERY_CONF;
reDt = ExecuteSql(ent.QUERY_CONF, pageIndex, pageSize, orderStr, whereStr, query.DB_SERVER_ID.Value, cfg.Select(x => x.FieldName).ToList());
}
catch (Exception e)
{
err.IsError = true;
err.Message = e.Message;
return(reDt);
}
}
reDt.TableName = "tables1";
return(reDt);
}
