/// <summary>
/// 获取工作指示信息
/// </summary>
/// <param name="workUser"></param>
/// <param name="ft"></param>
/// <param name="pagesize"></param>
/// <param name="page"></param>
/// <param name="orderby"></param>
/// <param name="allRecordCount"></param>
/// <returns></returns>
public DataTable RetrieveGzzs_List(AppUser workUser, FilterTranslator ft, int pagesize, int page, string orderby, out int allRecordCount)
{
string Zssj = ft.GetValue("Zssj");
ft.Remove("Zssj");
ft.Translate();
if (string.IsNullOrEmpty(orderby.Trim()))
{
orderby = "GzzsId desc";
}
;
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
//string sql = "select * from Szgkjc_Gzzs a where UserId=@UserId and ";
string sql = @"select *,stuff((select ','+ZshfrName+'【'+(case when Phone is null then '' else phone end)+'】' from Szgkjc_Gzhf a inner join dbo.Gwtz_Sjml b on a.ZshfrId=b.SjmlID
where a.GzzsId=c.GzzsId for xml path('')),1,1,'') zdhfr
from Szgkjc_Gzzs c where UserId=@UserId and ";
if (!string.IsNullOrEmpty(Zssj))
{
sql += " CONVERT(VARCHAR(50),Zssj,112)=@Zssj and";
sp.Add("@Zssj", DateTime.Parse(Zssj).ToString("yyyyMMdd"));
}
sp.Add("@UserId", workUser.UserID);
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "dt", orderby, pagesize, page, out allRecordCount));
}
public override List <Dictionary <string, object> > GetListData(FilterGroup filter)
{
var db = this.MainDb;
var data = base.GetListData(filter);
foreach (var item in data)
{
try
{
if (!string.IsNullOrEmpty(item["ValueSQL"].ToString()))
{
var sql = item["ValueSQL"].ToString();
if (sql.Contains("@0"))
{
item["Value"] = db.ExecuteScalar <string>(sql, SysContext.WanJiangUserID);
}
else
{
item["Value"] = db.ExecuteScalar <string>(sql);
}
}
else
{
var model = item["ModelName"].ToString();
var filterData = item["FilterData"].ToString();
if (string.IsNullOrEmpty(model))
{
continue;
}
FilterGroup model_filter = new FilterGroup();
if (!string.IsNullOrEmpty(filterData))
{
model_filter = JsonHelper.DeserializeJsonToObject <FilterGroup>(filterData);
}
model_filter = new FastDev.DevDB.Rights.RightsServer(db).AppendDataFilter(model, model_filter);
var whereTranslator = new FilterTranslator();
if (model_filter != null)
{
whereTranslator.Group = model_filter;
}
whereTranslator.Translate();
string sql = whereTranslator.CommandText;
sql = string.IsNullOrEmpty(sql) ? "" : "where " + sql;
sql = "select count(*) from " + model + " " + sql;
item["Value"] = db.ExecuteScalar <string>(sql, whereTranslator.Parms.ToArray());
}
}
catch
{
}
}
return(data);
}
/// <summary>
/// 解析where并返回翻译对象(请确保UI层查询条件与数据库的模型一致,否则请自行进行对where的条件进行参数转化解析)
/// </summary>
/// <param name="where">查询条件where(与数据库模型一致的查询对象的where)</param>
/// <returns></returns>
public static string GetFilterTanslate(string where)
{
string commandText = "";
if (string.IsNullOrEmpty(@where)) return commandText;
FilterTranslator translate = new FilterTranslator();
translate.Group = Utility.JsonHelper.FromJson<FilterGroup>(@where);
translate.Translate();
commandText = FilterParam.AddParameters(translate.CommandText, translate.Parms);
return commandText;
}
public static AjaxResult GetWhere(string groupJSON)
{
var group = new JavaScriptSerializer().Deserialize <FilterGroup>(groupJSON);
var translator = new FilterTranslator(group);
translator.Translate();
return(AjaxResult.Success((object)translator.ToString()));
}
private object GetListDataByFilter <T>(DbContext db, FilterGroup filter)
{
FilterTranslator filterTranslator = new FilterTranslator();
if (filter != null)
{
filterTranslator.Group = filter;
}
filterTranslator.Translate();
string commandText = filterTranslator.CommandText;
commandText = (string.IsNullOrEmpty(commandText) ? "" : ("where " + commandText));
return(db.Fetch <T>(commandText, filterTranslator.Parms.ToArray()));
}
private string GetModelId(DbContext db, string modelName, FilterGroup filter)
{
FilterTranslator filterTranslator = new FilterTranslator();
if (filter != null)
{
filterTranslator.Group = filter;
}
filterTranslator.Translate();
string commandText = filterTranslator.CommandText;
commandText = (string.IsNullOrEmpty(commandText) ? "" : ("where " + commandText));
commandText = string.Format("select ID from {0} ", modelName.ToUpper()) + commandText;
return(db.ExecuteScalar <string>(commandText, filterTranslator.Parms.ToArray()));
}
private object GetPageDataByQuery <T>(DbContext db, QueryDescriptor queryDescriptor_0)
{
string text = null;
string text2 = null;
bool flag = false;
bool flag2 = false;
if (queryDescriptor_0.OrderBy != null && queryDescriptor_0.OrderBy.Any())
{
text = queryDescriptor_0.OrderBy[0].Key;
text2 = ((queryDescriptor_0.OrderBy[0].Order == OrderSequence.ASC) ? "asc" : "desc");
}
if (queryDescriptor_0.PageIndex.HasValue && queryDescriptor_0.PageSize.HasValue)
{
flag2 = true;
if (queryDescriptor_0.PageSize == 0L)
{
queryDescriptor_0.PageSize = 20L;
}
}
if (!text.IsNullOrEmpty())
{
flag = true;
text2 = ((text2.IsNullOrEmpty() || text2.EqualsTo("asc")) ? "asc" : "desc");
}
FilterTranslator filterTranslator = new FilterTranslator();
if (queryDescriptor_0.Condition != null)
{
filterTranslator.Group = queryDescriptor_0.Condition;
}
filterTranslator.Translate();
string commandText = filterTranslator.CommandText;
commandText = (string.IsNullOrEmpty(commandText) ? "" : ("where " + commandText));
if (flag)
{
commandText += string.Format(" order by {0} {1}", text, text2);
}
if (flag2)
{
Page <T> page = db.Page <T>(queryDescriptor_0.PageIndex.Value, queryDescriptor_0.PageSize.Value, commandText, filterTranslator.Parms.ToArray());
return(new PagedData(page.Items, page.TotalItems));
}
List <T> list = db.Fetch <T>(commandText, filterTranslator.Parms.ToArray());
return(new PagedData(list, list.Count));
}
public ActionResult Delete(string model, string[] arg)
{
DbContext dbContext = GetNetDatabase();
try
{
if (arg != null && arg.Any())
{
dbContext.BeginTransaction();
FilterTranslator filterTranslator = new FilterTranslator();
filterTranslator.Group = new FilterGroup();
foreach (string value in arg)
{
filterTranslator.Group.rules.Add(new FilterRule
{
field = "ID",
op = "equal",
value = value
});
}
filterTranslator.Group.op = "or";
filterTranslator.Translate();
if (string.Compare(model, "net_order", true) == 0)
{
dbContext.Delete <net_order>("where " + filterTranslator.CommandText, filterTranslator.Parms.ToArray());
}
dbContext.CompleteTransaction();
}
return(Json(new
{
statusCode = "1"
}));
}
catch (Exception ex)
{
if (arg != null && arg.Any())
{
dbContext.AbortTransaction();
}
return(Json(new
{
statusCode = "3",
message = ex.Message
}));
}
}
/// <summary>
/// 获取报表相关参数
/// </summary>
/// <param name="model"></param>
/// <param name="arg"></param>
/// <returns></returns>
public ReportResult GetReportResult(string model, ReportArg arg)
{
DbContext currentDb = SysContext.GetCurrentDb();
ReportResult reportResult = new ReportResult();
reportModelName = model;
reportModelConfig = ServiceHelper.GetServiceConfig(model);
List <NameValue> list_0 = GetLegendNameValues(currentDb, arg);
List <NameValue> list_1 = GetAxisNameValues(currentDb, arg);
foreach (NameValue item in list_1)
{
reportResult.axis.Add(item.name);
}
foreach (NameValue item2 in list_0)
{
reportResult.legend.Add(item2.name);
object obj = null;
if (!string.IsNullOrEmpty(arg.axisField) && arg.legendType != "pie")
{
List <object> list = new List <object>();
foreach (NameValue item3 in list_1)
{
FilterGroup group = GetDateTimeLegendFilter(currentDb, arg, item2.value, item3.value);
FilterTranslator filterTranslator = new FilterTranslator(group);
filterTranslator.Translate();
list.Add(GetCountValue(currentDb, arg.valueField, arg.valueFieldType, filterTranslator.CommandText, filterTranslator.Parms.ToArray()));
}
obj = list;
}
else
{
FilterGroup group = GetDateTimeLegendFilter(currentDb, arg, item2.value, null);
FilterTranslator filterTranslator = new FilterTranslator(group);
filterTranslator.Translate();
obj = GetCountValue(currentDb, arg.valueField, arg.valueFieldType, filterTranslator.CommandText, filterTranslator.Parms.ToArray());
}
reportResult.series.Add(new
{
name = item2.name,
value = obj
});
}
return(reportResult);
}
/// <summary>
/// 获取菜单的按钮信息
/// </summary>
/// <param name="request"></param>
/// <param name="Count"></param>
/// <returns></returns>
public static string selectwherestring(string sqlwhere)
{
//wher语句
string commandText = "";
//if (!request.Where.IsNullOrEmpty())
//{
//做Where的翻译处理工作
FilterTranslator whereTranslator = new FilterTranslator();
//当前角色规则转化(用户角色in (xxx)中
//反序列化Filter Group JSON,并把角色权限组所在的规则合并
whereTranslator.Group = JsonHelper.FromJson <FilterGroup>(sqlwhere);
//开始翻译sql语句
whereTranslator.Translate();
commandText = FilterParam.AddParameters(whereTranslator.CommandText, whereTranslator.Parms);
// }
return(commandText);
}
/// <summary>
/// 指示回复列表
/// </summary>
/// <param name="workUser"></param>
/// <param name="ft"></param>
/// <param name="pagesize"></param>
/// <param name="page"></param>
/// <param name="orderby"></param>
/// <param name="allRecordCount"></param>
/// <returns></returns>
public DataTable RetrieveZshf_all_List(AppUser workUser, FilterTranslator ft, int pagesize, int page, string orderby, out int allRecordCount)
{
string DataState = ft.GetValue("DataState");
string Zssj = ft.GetValue("Zssj");
string Zshfsj = ft.GetValue("Zshfsj");
ft.Remove("DataState");
ft.Remove("Zssj");
ft.Remove("Zshfsj");
ft.Translate();
if (string.IsNullOrEmpty(orderby.Trim()))
{
orderby = "ZshfId desc";
}
;
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = @"select * from (select a.*,b.Gzzszt,b.GzzsNr,b.ZsrName,b.ZsrPhone,b.ZsrEmail,b.Zssj from Szgkjc_Gzhf a
inner join Szgkjc_Gzzs b on a.GzzsId=b.GzzsId where a.UserId=@UserId) aa where 1=1 and ";
sp.Add("@UserId", workUser.UserID);
if (!string.IsNullOrEmpty(DataState))
{
sql += " DataState=@DataState and";
sp.Add("@DataState", DataState);
}
if (!string.IsNullOrEmpty(Zssj))
{
sql += " CONVERT(VARCHAR(50),Zssj,112)=@Zssj and";
sp.Add("@Zssj", DateTime.Parse(Zssj).ToString("yyyyMMdd"));
}
if (!string.IsNullOrEmpty(Zshfsj))
{
sql += " CONVERT(VARCHAR(50),Zshfsj,112)=@Zshfsj and";
sp.Add("@Zshfsj", DateTime.Parse(Zshfsj).ToString("yyyyMMdd"));
}
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "dt", orderby, pagesize, page, out allRecordCount));
}