Пример #1
0
        /// <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);
        }
Пример #2
0
        /// <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);
        }
Пример #3
0
        /// <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()));
        }
Пример #4
0
        /// <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 ? "查&nbsp;询&nbsp;" : 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()
            });
        }
Пример #5
0
        /// <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()
            });
        }