/// <summary>
/// 得到sql字符串
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <returns></returns>
private string GetSqlString(string sql, int type, Guid dbconnId)
{
switch (type)
{
case 0:
return(new DbConnection().GetFieldValue(dbconnId, Wildcard.Filter(sql.FilterSelectSql())));
case 1:
case 2:
try
{
DataTable dt = new DbConnection().GetDataTable(dbconnId, Wildcard.Filter(sql.FilterSelectSql()));
if (dt.Rows.Count == 0)
{
return(string.Empty);
}
StringBuilder sb = new StringBuilder();
sb.Append("<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" class=\"hometable\"><thead><tr>");
foreach (DataColumn dc in dt.Columns)
{
sb.Append("<th>" + dc.ColumnName + "</th>");
}
sb.Append("</tr></thead><tbody>");
foreach (DataRow dr in dt.Rows)
{
sb.Append("<tr>");
foreach (DataColumn dc in dt.Columns)
{
sb.Append("<td>" + dr[dc.ColumnName].ToString() + "</td>");
}
sb.Append("</tr>");
}
sb.Append("</tbody></table>");
return(sb.ToString());
}
catch
{
return(string.Empty);
}
}
return(string.Empty);
}
/// <summary>
/// 得到导出excel数据(不分页的数据)
/// </summary>
/// <param name="programRunModel"></param>
/// <param name="request"></param>
/// <returns></returns>
public DataTable GetExportData(Model.ProgramRun programRunModel, HttpRequest request)
{
DataTable exportDt = new DataTable();
if (null == programRunModel || programRunModel.ProgramExports.Count == 0)
{
return(exportDt);
}
string sql = request.HttpContext.Session.GetString("program_querysql_" + programRunModel.Id.ToString("N"));
if (sql.IsNullOrWhiteSpace())
{
return(exportDt);
}
object obj = Cache.IO.Get("program_queryparameter_" + programRunModel.Id.ToString("N") + "_" + request.HttpContext.Session.Id);
DbParameter[] parameters = null;
if (null != obj)
{
parameters = ((List <DbParameter>)obj).ToArray();
}
DbConnection dbConnection = new DbConnection();
DataTable dt = dbConnection.GetDataTable(programRunModel.ConnId, sql, parameters);
if (dt.Rows.Count == 0)
{
return(exportDt);
}
foreach (var field in programRunModel.ProgramExports)
{
exportDt.Columns.Add(field.ShowTitle.IsNullOrEmpty() ? field.Field : field.ShowTitle, Type.GetType("System.String"));
}
foreach (DataRow dr in dt.Rows)
{
DataRow dr1 = exportDt.NewRow();
foreach (var field in programRunModel.ProgramExports)
{
string value = dt.Columns.Contains(field.Field) ? dr[field.Field].ToString() : string.Empty;
if (!value.IsNullOrWhiteSpace())
{
switch (field.ShowType)
{
case 1: //序号
break;
case 2: //日期时间
if (value.IsDateTime(out DateTime d))
{
value = d.ToString(field.ShowFormat.IsNullOrWhiteSpace() ? "yyyy-MM-dd HH:mm:ss" : field.ShowFormat);
}
break;
case 3: //数字
if (!field.ShowFormat.IsNullOrWhiteSpace())
{
value = value.ToDecimal().ToString(field.ShowFormat);
}
break;
case 4: //数据字典ID显示为标题
value = new Dictionary().GetTitles(value);
break;
case 5: //组织架构显示为名称
value = new Organize().GetNames(value);
break;
case 6: //自定义
value = Wildcard.Filter(field.CustomString, null, dr);
break;
case 7: //人员ID显示为姓名
value = new User().GetNames(value);
break;
}
}
dr1[field.ShowTitle.IsNullOrEmpty() ? field.Field : field.ShowTitle] = value;
}
exportDt.Rows.Add(dr1);
}
return(exportDt);
}
/// <summary>
/// 得到查询数据
/// </summary>
/// <param name="programRunModel"></param>
/// <returns></returns>
public DataTable GetData(Model.ProgramRun programRunModel, HttpRequest request, int size, int number, out int count)
{
DbConnection dbConnection = new DbConnection();
var dbConnModel = dbConnection.Get(programRunModel.ConnId);
if (null == dbConnModel)
{
count = 0;
return(new DataTable());
}
DbconnnectionSql dbconnnectionSql = new DbconnnectionSql(dbConnModel);
string tempSql = Wildcard.Filter(programRunModel.SqlString).Trim1();
bool isProc = tempSql.Trim().StartsWith("exec", StringComparison.CurrentCultureIgnoreCase);//是否是存储过程
#region 组织查询条件
StringBuilder whereBuilder = new StringBuilder();
List <DbParameter> dbParameters = new List <DbParameter>();
if (!isProc)
{
if (!programRunModel.SqlString.ContainsIgnoreCase(" where "))
{
whereBuilder.Append(" WHERE 1=1");
}
foreach (var query in programRunModel.ProgramQueries)
{
string fieldName = query.Field;
string operators = query.Operators;
string controlName = query.ControlName.IsNullOrWhiteSpace() ? "ctl_" + query.Id.ToString("N") : query.ControlName;
string queryValue = request.Form[controlName];
if (queryValue.IsNullOrEmpty())
{
continue;
}
string paramsChar = dbConnModel.ConnType.EqualsIgnoreCase("oracle") ? ":" : "@";
if (query.InputType.In(1, 2, 3, 4))
{
if (queryValue.IsDateTime(out DateTime d))
{
whereBuilder.Append(" AND " + fieldName + operators + paramsChar + fieldName);
dbParameters.Add(dbconnnectionSql.SqlInstance.GetDbParameter(paramsChar + fieldName,
query.InputType.In(1, 2) ? d.Date : d));
}
if (query.InputType.In(2, 4))//日期时间范围
{
string queryValue1 = request.Form[controlName + "1"];
if (queryValue1.IsDateTime(out DateTime d1))
{
string operators1 = string.Empty;
switch (operators)
{
case ">":
operators1 = "<";
break;
case "<":
operators1 = ">";
break;
case ">=":
operators1 = "<"; //因为在查询时日期要加上一天，所以这里是<
break;
case "<=":
operators1 = ">";
break;
}
whereBuilder.Append(" AND " + fieldName + operators1 + paramsChar + fieldName + "1");
dbParameters.Add(dbconnnectionSql.SqlInstance.GetDbParameter(paramsChar + fieldName + "1",
query.InputType.In(1, 2) ? d1.AddDays(1).Date : d1.AddDays(1)));
}
}
}
else
{
switch (operators)
{
case "IN":
whereBuilder.Append(" AND " + fieldName + " IN(" + queryValue.FilterSelectSql() + ")");
break;
case "NOT IN":
whereBuilder.Append(" AND " + fieldName + " NOT IN(" + queryValue.FilterSelectSql() + ")");
break;
case "%LIKE":
whereBuilder.Append(" AND " + fieldName + " LIKE '%" + queryValue.FilterSelectSql() + "'");
break;
case "LIKE%":
whereBuilder.Append(" AND " + fieldName + " LIKE '" + queryValue.FilterSelectSql() + "%'");
break;
case "%LIKE%":
whereBuilder.Append(" AND " + fieldName + " LIKE '%" + queryValue.FilterSelectSql() + "%'");
break;
default:
whereBuilder.Append(" AND " + fieldName + operators + paramsChar + fieldName);
dbParameters.Add(dbconnnectionSql.SqlInstance.GetDbParameter(paramsChar + fieldName, queryValue));
break;
}
}
}
}
else
{
//存储过程直接把获取到的值作为参数
foreach (var query in programRunModel.ProgramQueries)
{
string controlName = query.ControlName.IsNullOrWhiteSpace() ? "ctl_" + query.Id.ToString("N") : query.ControlName;
string queryValue = request.Form[controlName];
whereBuilder.Append("'" + queryValue + "',");
}
}
#endregion
string sidx = request.Forms("sidx");
string sord = request.Forms("sord");
string order = (sidx.IsNullOrWhiteSpace() ? programRunModel.DefaultSort : sidx) + (sord.IsNullOrEmpty() ? "" : " " + sord);
StringBuilder querySql = new StringBuilder();
if (!isProc)
{
if (dbConnModel.ConnType.EqualsIgnoreCase("sqlserver") || dbConnModel.ConnType.EqualsIgnoreCase("oracle"))
{
tempSql = tempSql.Insert(tempSql.IndexOfIgnoreCase("from") - 1, ",ROW_NUMBER() OVER(ORDER BY " + order + ") AS PagerAutoRowNumber ");
}
querySql.Append(tempSql);
querySql.Append(whereBuilder.ToString());
if (dbConnModel.ConnType.EqualsIgnoreCase("mysql") && !order.IsNullOrWhiteSpace())
{
querySql.Append(" ORDER BY " + order);
}
}
else
{
querySql.Append(tempSql);
querySql.Append(tempSql.EndsWith("'") ? "," : " ");
querySql.Append(whereBuilder.ToString().TrimEnd(','));//存储过程直接把查询参数加到后面
}
string querySqlString = querySql.ToString();
//将sql写入SESSION，导出时用
request.HttpContext.Session.SetString("program_querysql_" + programRunModel.Id.ToString("N"), querySqlString);
if (dbParameters.Count > 0)//将SQL参数保存到缓存
{
Cache.IO.Insert("program_queryparameter_" + programRunModel.Id.ToString("N") + "_"
+ request.HttpContext.Session.Id, dbParameters, DateExtensions.Now.AddHours(2));
}
count = 0;
string pagerSql = string.Empty;
if (programRunModel.IsPager == 1 && !isProc)
{
pagerSql = dbconnnectionSql.SqlInstance.GetPaerSql(querySqlString, size, number, out count, dbParameters.ToArray());
}
else if (programRunModel.IsPager == 1 && isProc)
{
//querySql.Append((querySql.ToString().EndsWith("'") ? "," : " ") + size.ToString() + "," + number.ToString());
//querySqlString += (querySqlString.EndsWith("'") ? "," : " ") + size.ToString() + "," + number.ToString();
pagerSql = querySqlString;
}
return(dbConnection.GetDataTable(dbConnModel, programRunModel.IsPager == 1 ? pagerSql : querySqlString, dbParameters.ToArray()));
}
/// <summary>
/// 得到按钮HTML
/// </summary>
/// <param name="programRunModel"></param>
/// <param name="userId">当前登录人员ID，验证权限</param>
/// <param name="menuId">菜单ID(URL中的appid)</param>
/// <param name="localizer">语言包</param>
/// <returns><returns>List[0]工具栏按钮 List[1]常规按钮 List[2]列表按钮</returns></returns>
public List <string> GetButtonHtml(Model.ProgramRun programRunModel, Guid userId, Guid menuId, IStringLocalizer localizer = null)
{
StringBuilder button_toolbar = new StringBuilder();
StringBuilder button_normal = new StringBuilder();
StringBuilder button_list = new StringBuilder();
if (null == programRunModel)
{
return(new List <string>()
{
"", "", ""
});
}
//如果有查询要添加查询按钮
if (programRunModel.ProgramQueries.Any())
{
Guid queryButtonId = "A5678AAB-69D8-40C5-9523-B4882A234975".ToGuid();
if (!programRunModel.ProgramButtons.Exists(p => p.Id == queryButtonId))
{
programRunModel.ProgramButtons.Add(new Model.ProgramButton()
{
Id = queryButtonId,
ButtonName = localizer == null ? "查 询 " : localizer["Query"],
ProgramId = programRunModel.Id,
ClientScript = "query();",
IsValidateShow = 0,
ShowType = 1,
Sort = 0,
Ico = "fa-search"
});
}
}
List <Model.MenuUser> menuusers = new MenuUser().GetAll();
Menu menu = new Menu();
SystemButton systemButton = new SystemButton();
string language = Tools.GetCurrentLanguage();
foreach (var button in programRunModel.ProgramButtons.OrderBy(p => p.Sort))
{
//检查权限
if (1 == button.IsValidateShow)
{
if (!menu.HasUseButton(menuId, button.Id, userId, menuusers))
{
continue;
}
}
string funName = "fun_" + Guid.NewGuid().ToString("N");
string butName = button.ButtonName;
//如果是选择的按钮要从按钮库中获取名称(多语言时)
if (button.ButtonId.HasValue && !Config.Language_Default.IsNullOrWhiteSpace())
{
var systemButtonModel = systemButton.Get(button.ButtonId.Value);
if (null != systemButtonModel)
{
butName = language.Equals("en-US") ? systemButtonModel.Name_en : language.Equals("zh") ? systemButtonModel.Name_zh : systemButtonModel.Name;
}
}
string ico = button.Ico;
if (button.ShowType == 0)//工具栏按钮
{
if (ico.IsFontIco())
{
button_toolbar.Append("<a href=\"javascript:void(0);\" onclick=\"" + funName + "();\">" +
"<i class='fa " + ico + "'></i><label>" + butName + "</label></a>");
}
else
{
if (!ico.IsNullOrWhiteSpace())//图片图标
{
button_toolbar.Append("<a href=\"javascript:void(0);\" onclick=\"" + funName + "();\">" +
"<span style=\"background-image:url(" + ico + ");\">" + butName + "</span></a>");
}
else//没有设置图标
{
button_toolbar.Append("<a href=\"javascript:void(0);\" onclick=\"" + funName + "();\">" +
"<label>" + butName + "</label></a>");
}
}
button_toolbar.Append("<script type='text/javascript'>function " + funName + "(){" + Wildcard.Filter(button.ClientScript) + "}</script>");
}
else if (button.ShowType == 1)//常规按钮
{
button_normal.Append("<button type='button' class='mybutton' style='margin-right:8px;'");
button_normal.Append(" onclick=\"" + funName + "();\"");
button_normal.Append(">");
if (!ico.IsNullOrWhiteSpace())
{
if (ico.IsFontIco())//如果是字体图标
{
button_normal.Append("<i class='fa " + ico + "' style='margin-right:3px;'></i>" + butName);
}
else
{
button_normal.Append("<img src=\"" + ico + "\" style='margin-right:3px;vertical-align:middle;'/>" + butName);
}
}
else
{
button_normal.Append(butName);
}
button_normal.Append("</button>");
button_normal.Append("<script type='text/javascript'>function " + funName + "(){" + Wildcard.Filter(button.ClientScript) + "}</script>");
}
else if (button.ShowType == 2)//列表按钮 不替换通配符，列表按钮涉及运行时数据，在运行时替换。
{
if (ico.IsFontIco())
{
button_list.Append("<a class=\"list\" href=\"javascript:void(0);\" onclick=\"" + button.ClientScript + "\">" +
"<i class='fa " + ico + "'></i>" + butName + "</a>");
}
else
{
if (!ico.IsNullOrWhiteSpace())//图片图标
{
button_list.Append("<a class=\"list\" href=\"javascript:void(0);\" onclick=\"" + button.ClientScript + "\">" +
"<span style=\"background-image:url(" + ico + ");\">" + butName + "</span></a>");
}
else//没有设置图标
{
button_list.Append("<a href=\"javascript:void(0);\" onclick=\"" + button.ClientScript + "\">" +
"<label>" + butName + "</label></a>");
}
}
}
}
return(new List <string>()
{
button_toolbar.ToString(), button_normal.ToString(), button_list.ToString()
});
}
/// <summary>
/// 得到按钮HTML
/// </summary>
/// <param name="menuId">菜单Id</param>
/// <param name="userId">当前用户Id</param>
/// <returns><returns>List[0]工具栏按钮 List[1]常规按钮 List[2]列表按钮</returns></returns>
public List <string> GetButtonHtml(string menuId, Guid userId)
{
StringBuilder button_toolbar = new StringBuilder();
StringBuilder button_normal = new StringBuilder();
StringBuilder button_list = new StringBuilder();
if (!menuId.IsGuid(out Guid menuGuid))
{
return(new List <string>()
{
"", "", ""
});
}
var menuModel = Get(menuGuid);
if (null == menuModel || !menuModel.AppLibraryId.HasValue)
{
return(new List <string>()
{
"", "", ""
});
}
List <Model.MenuUser> menuusers = new MenuUser().GetAll();
if (!HasUse(menuGuid, userId, menuusers, out string source, out string params1))
{
return(new List <string>()
{
"", "", ""
});
}
var buttons = new AppLibraryButton().GetListByApplibraryId(menuModel.AppLibraryId.Value).OrderBy(p => p.Sort);
Menu menu = new Menu();
foreach (var button in buttons)
{
//检查权限
if (true)
{
if (!menu.HasUseButton(menuGuid, button.Id, userId, menuusers))
{
continue;
}
}
string funName = "fun_" + Guid.NewGuid().ToString("N");
string butName = button.Name;
string ico = button.Ico;
if (button.ShowType == 2)//工具栏按钮
{
if (ico.IsFontIco())
{
button_toolbar.Append("<a href=\"javascript:void(0);\" onclick=\"" + funName + "();\">" +
"<i class='fa " + ico + "'></i><label>" + butName + "</label></a>");
}
else
{
if (!ico.IsNullOrWhiteSpace())//图片图标
{
button_toolbar.Append("<a href=\"javascript:void(0);\" onclick=\"" + funName + "();\">" +
"<span style=\"background-image:url(" + ico + ");\">" + butName + "</span></a>");
}
else//没有设置图标
{
button_toolbar.Append("<a href=\"javascript:void(0);\" onclick=\"" + funName + "();\">" +
"<label>" + butName + "</label></a>");
}
}
button_toolbar.Append("<script type='text/javascript'>function " + funName + "(){" + Wildcard.Filter(button.Events) + "}</script>");
}
else if (button.ShowType == 0)//常规按钮
{
button_normal.Append("<button type='button' class='mybutton' style='margin-right:8px;'");
button_normal.Append(" onclick=\"" + funName + "();\"");
button_normal.Append(">");
if (!ico.IsNullOrWhiteSpace())
{
if (ico.IsFontIco())//如果是字体图标
{
button_normal.Append("<i class='fa " + ico + "' style='margin-right:3px;'></i>" + butName);
}
else
{
button_normal.Append("<img src=\"" + ico + "\" style='margin-right:3px;vertical-align:middle;'/>" + butName);
}
}
else
{
button_normal.Append(butName);
}
button_normal.Append("</button>");
button_normal.Append("<script type='text/javascript'>function " + funName + "(){" + Wildcard.Filter(button.Events) + "}</script>");
}
else if (button.ShowType == 1)//列表按钮 不替换通配符，列表按钮涉及运行时数据，在运行时替换。
{
if (ico.IsFontIco())
{
button_list.Append("<a class=\"list\" href=\"javascript:void(0);\" onclick=\"" + button.Events + "\">" +
"<i class='fa " + ico + "'></i>" + butName + "</a>");
}
else
{
if (!ico.IsNullOrWhiteSpace())//图片图标
{
button_list.Append("<a class=\"list\" href=\"javascript:void(0);\" onclick=\"" + button.Events + "\">" +
"<span style=\"background-image:url(" + ico + ");\">" + butName + "</span></a>");
}
else//没有设置图标
{
button_list.Append("<a href=\"javascript:void(0);\" onclick=\"" + button.Events + "\">" +
"<label>" + butName + "</label></a>");
}
}
}
}
return(new List <string>()
{
button_toolbar.ToString(), button_normal.ToString(), button_list.ToString()
});
}