public string Save(int pageID, int type, string key)
{
string modifyDataSource = string.Empty;
using (var db = new LayAdminEntities())
{
coreDevPage devPage = db.coreDevPage.Find(pageID);
modifyDataSource = string.IsNullOrEmpty(devPage.pageModifySource) ? devPage.pageDataSource : devPage.pageModifySource;
}
return(GridManager.Save(modifyDataSource, type, key));
}
public ActionResult GridPart(int pageID)
{
if (pageID == 0)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
coreDevPage devPage = db.coreDevPage.Find(pageID);
if (devPage == null)
{
return(HttpNotFound());
}
if (string.IsNullOrEmpty(devPage.pageQueryURL))
{
devPage.pageQueryURL = "/PageObject/GetTableData?pageID=" + pageID.ToString();
}
List <coreToolBar> toolBars = db.coreToolBar.Where <coreToolBar>(r => r.toolbarID == devPage.pageToolBarID || r.toolbarID == devPage.pageRowBarID).ToList();
if (devPage.pageToolBarID != null && devPage.pageToolBarID != 0)
{
ViewBag.toolBar = toolBars.Find(r => r.toolbarID == devPage.pageToolBarID).toolbarContent;
}
if (devPage.pageRowBarID != null && devPage.pageRowBarID != 0)
{
ViewBag.rowBar = toolBars.Find(r => r.toolbarID == devPage.pageRowBarID).toolbarContent;
}
//获取toolBarJS
List <coreToolBarEvent> EventList = db.coreToolBarEvent.Where <coreToolBarEvent>(r => r.toolBarID == devPage.pageToolBarID).ToList();
string toolBarJS = string.Empty;
foreach (var item in EventList)
{
item.EventJS = item.EventJS.Replace("@@pageID", devPage.pageID.ToString());
toolBarJS += item.EventJS + "\r\n";
}
ViewBag.toolBarJS = toolBarJS;
ViewBag.Total = devPage.pageColomns.Contains("totalRowText") ? "true" : "false";
List <coreDevPageQuery> fieldsInfo = db.coreDevPageQuery.Where <coreDevPageQuery>(r => r.PAGE_ID == pageID).ToList();
foreach (var item in fieldsInfo)
{
if (devPage.pageSearch.ToLower().Contains("@s" + item.PAGE_FIELD.ToLower()))
{
//string str = "";
//DataTable dt= LayAdminCore.e
}
}
return(View(devPage));
}
public string Delete(int pageID, string key)
{
if (key.IndexOf("[") != 0)
{
key = "[" + key + "]";
}
string modifyDataSource = string.Empty;
using (var db = new LayAdminEntities())
{
coreDevPage devPage = db.coreDevPage.Find(pageID);
modifyDataSource = string.IsNullOrEmpty(devPage.pageModifySource) ? devPage.pageDataSource : devPage.pageModifySource;
}
return(GridManager.Delete(modifyDataSource, key));
}
/// <summary>
///
/// </summary>
/// <param name="pageID"></param>
/// <param name="type">1-添加，2-修改</param>
/// <returns></returns>
public ActionResult FieldPart(int pageID, int type, string key)
{
if (pageID == 0)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
coreDevPage devPage = db.coreDevPage.Find(pageID);
if (devPage == null)
{
return(HttpNotFound());
}
devPage.pageModifyURL += "&type=" + type.ToString();
string whereStr = "";
if (string.IsNullOrEmpty(key))
{
whereStr = devPage.pageWhereExpress;
}
else
{
whereStr = devPage.pageEditWhere;
}
if (type == 2)
{
devPage.pageEdit = devPage.pageEdit.ToLower();
Dictionary <string, string> FieldValues = GridManager.GetFieldPart(devPage.pageDataSource, whereStr.ToLower(), key);
foreach (var item in FieldValues)
{
devPage.pageEdit = devPage.pageEdit.Replace("@@" + item.Key, item.Value);
}
while (devPage.pageEdit.Contains("@sqloption"))
{
string str = devPage.pageEdit.Substring(devPage.pageEdit.IndexOf("@sqloption"));
string strOption = str.Substring(0, str.IndexOf(";") + 1);
string OptionField = str.Substring(10, str.IndexOf("=") - 10);
string strSql = str.Substring(str.IndexOf("=") + 1, str.IndexOf(";") - str.IndexOf("="));
DataSet ds = SqlHelper.ExecuteDataset(DBConfig.DBConnString, CommandType.Text, strSql);
string option = "";
if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow item in ds.Tables[0].Rows)
{
if (FieldValues.Keys.Contains(OptionField))
{
if (item[0].ToString() == FieldValues[OptionField])
{
option += string.Format("<option value = \"{0}\" selected> {1} </option >", item[0], item[1]);
continue;
}
}
option += string.Format("<option value = \"{0}\" > {1} </option >", item[0], item[1]);
}
}
devPage.pageEdit = devPage.pageEdit.Replace(strOption, option);
}
}
else
{
devPage.pageEdit = devPage.pageAdd;
}
return(View(devPage));
}
/// <summary>
/// 获取table数据
/// </summary>
/// <param name="PageID">页面编号</param>
/// <param name="pageIndex">页面索引</param>
/// <param name="limit">每页记录数</param>
/// <param name="key">查询条件(Json)</param>
/// <returns></returns>
public static string GetPageData(int PageID, int pageIndex, int limit, string key)
{
tableData data = new tableData();
string whereStr = "1=1";
string fields = "*";
string orderBy = "";
try
{
List <SqlParameter> sqlParams = new List <SqlParameter>();
using (var db = new LayAdminEntities())
{
coreDevPage devPage = db.coreDevPage.Find(PageID);
if (devPage == null)
{
throw new Exception("页面编号不正确");
}
//取表或试图对应的字段
DataTable table = SqlHelper.ExecuteDataset(DBConfig.DBConnString, CommandType.Text, "select lower(col.name) as FieldName, tp.name FieldType from sys.columns col left join dbo.systypes tp on col.user_type_id = tp.xusertype where object_id in(object_id('" + devPage.pageDataSource + "'))").Tables[0];
if (table == null || table.Rows.Count <= 0)
{
throw new Exception("表或试图不存在字段");
}
//拼接where条件
if (string.IsNullOrEmpty(devPage.pageWhereExpress) == false)
{
whereStr += " and " + devPage.pageWhereExpress;
}
if (key != null)
{
JObject jObj = JsonConvert.DeserializeObject(key) as JObject;
foreach (var item in jObj)
{
if (string.IsNullOrEmpty(item.Value.ToString()) == false)
{
var d = from r in table.AsEnumerable()
where r.Field <string>("FieldName") == item.Key.ToString().ToLower()
select r.Field <string>("FieldType");
if (d.FirstOrDefault().ToString() == "datetime")
{
whereStr += string.Format(" and {0}>=@{1}", item.Key.ToString(), "BEGIN" + item.Key.ToString());
whereStr += string.Format(" and {0}<@{1}", item.Key.ToString(), "END" + item.Key.ToString());
SqlParameter param = new SqlParameter();
param.ParameterName = "@" + "BEGIN" + item.Key.ToString();
param.SqlDbType = GetSqlDbType(d.FirstOrDefault().ToString());
param.Value = item.Value.ToString();
sqlParams.Add(param);
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@" + "END" + item.Key.ToString();
param1.SqlDbType = GetSqlDbType(d.FirstOrDefault().ToString());
param1.Value = DateTime.Parse(item.Value.ToString()).AddDays(1);
sqlParams.Add(param1);
}
else
{
whereStr += string.Format(" and {0}=@{1}", item.Key.ToString(), item.Key.ToString());
SqlParameter param = new SqlParameter();
param.ParameterName = "@" + item.Key.ToString();
param.SqlDbType = GetSqlDbType(d.FirstOrDefault().ToString());
param.Value = item.Value.ToString();
sqlParams.Add(param);
}
}
}
}
//获取查询字段
if (string.IsNullOrEmpty(devPage.pageQueryFields) == false)
{
fields = devPage.pageQueryFields;
}
//获取排序字段，若无排序，则取表的第一个字段倒序
if (string.IsNullOrEmpty(devPage.pageOrderBy) == false)
{
orderBy = devPage.pageOrderBy;
}
else
{
orderBy = table.Rows[0][0].ToString() + " desc";
}
data.count = (int)SqlHelper.ExecuteScalar(DBConfig.DBConnString, CommandType.Text, "select count(1) from " + devPage.pageDataSource + " where " + whereStr + "", sqlParams.ToArray());
DataSet ds = SqlHelper.ExecuteDataset(DBConfig.DBConnString, CommandType.Text, string.Format("SELECT " + fields + " FROM " + devPage.pageDataSource + " where " + whereStr + " ORDER BY " + orderBy + " OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY ", (pageIndex - 1) * limit, limit == 0? data.count : limit), sqlParams.ToArray());
data.data = ds.Tables[0];
}
}
catch (Exception ex)
{
data.code = 1;
data.msg = ex.Message;
}
IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
return(JsonConvert.SerializeObject(data, timeFormat));
}
