public static string ToText(this DataTable dt, bool needTitle = true)
        {
            if (dt == null || dt.Rows.Count <= 0)
            {
                return(string.Empty);
            }

            var sbTxt = new StringBuilder();

            if (needTitle && dt.Columns.Count > 0)
            {
                foreach (DataColumn column in dt.Columns)
                {
                    sbTxt.Append(column.ColumnName).Append(StringConst.HT);
                }
                sbTxt.RemoveLastChar(CharConst.HT);
                sbTxt.Append(Environment.NewLine);
            }

            //遍历数据行,将行数据存入 实体对象中,并添加到 泛型集合中list
            foreach (DataRow row in dt.Rows)
            {
                foreach (DataColumn column in dt.Columns)
                {
                    sbTxt.Append(row[column.ColumnName]).Append(StringConst.HT);
                }
                sbTxt.RemoveLastChar(CharConst.HT);
                sbTxt.Append(Environment.NewLine);
            }

            var txt = sbTxt.RemoveLastChar(Environment.NewLine).ToString();

            return(txt);
        }
Пример #2
0
        /// <summary>
        /// 通过DataTable获得CSV格式数据
        /// </summary>
        /// <param name="dataTable">数据表</param>
        /// <param name="c">char</param>
        /// <returns>CSV字符串数据</returns>
        public static string ToCSV(this DataTable dataTable, char c = ',')
        {
            if (dataTable.IsNull())
            {
                return(string.Empty);
            }

            StringBuilder sb = new StringBuilder();

            // 写出表头
            foreach (DataColumn DataColumn in dataTable.Columns)
            {
                sb.AppendFormat("{0}{1}", DataColumn.ColumnName.ToString(), c);
            }
            sb.RemoveLastChar(c.ToString());
            sb.Append("\n");

            // 写出数据
            foreach (DataRowView dataRowView in dataTable.DefaultView)
            {
                foreach (DataColumn DataColumn in dataTable.Columns)
                {
                    sb.AppendFormat("{0}{1}", dataRowView[DataColumn.ColumnName].ToString(), c);
                }
                sb.RemoveLastChar(c.ToString());
                sb.Append("\n");
            }
            return(sb.ToString());
        }
Пример #3
0
        internal static SQLiteCommand InsertCommand <T>(string tableName, T model)
        {
            var toReturn           = new SQLiteCommand();
            var command            = new StringBuilder($"INSERT INTO {tableName} (");
            var filteredProperties = GetFilteredProperties <T>();
            var modelType          = model.GetType();
            var columnNames        = new StringBuilder();
            var columnValues       = new StringBuilder("VALUES (");

            foreach (var prop in filteredProperties)
            {
                columnNames.Append(prop.Name + ",");
                columnValues.Append("$" + prop.Name + ",");

                var propInfo = modelType.GetProperty(prop.Name);
                var value    = propInfo.GetValue(model);
                toReturn.Parameters.AddWithValue("$" + prop.Name, value);
            }

            columnNames.RemoveLastChar();
            columnValues.RemoveLastChar();
            columnNames.Append(")");
            columnValues.Append(");");
            command.Append(columnNames.ToString());
            command.Append(columnValues.ToString());
            toReturn.CommandText = command.ToString();
            return(toReturn);
        }
        /// <summary>
        /// Get a delegate object and use it to generate a entity class.
        /// </summary>
        /// <param name="type"></param>
        /// <param name="typeList"></param>
        /// <returns></returns>
        public static Func <object[], object> BuildDeletgateCreateInstance(Type type, Type[] typeList)
        {
            ConstructorInfo constructor = type.GetConstructor(typeList);

            if (constructor == null)
            {
                if (typeList == null || typeList.Length == 0)
                {
                    throw new Exception($@"未找到类'{type.Name}({type.FullName})'的无参数构造器.");
                }
                else
                {
                    StringBuilder sb = new StringBuilder();
                    foreach (var item in typeList)
                    {
                        sb.Append($@"{item.Name}({item.FullName}),");
                    }

                    sb.RemoveLastChar(',');
                    var msg = $@"未找到类'{type.Name}({type.FullName})'的构造器:参数个数:{typeList.Length},参数类型:{sb}.";
                    throw new Exception(msg);
                }
            }
            ParameterExpression paramExp = Expression.Parameter(typeof(object[]), "args_");

            Expression[] expList = GetExpressionArray(typeList, paramExp);

            NewExpression newExp = Expression.New(constructor, expList);

            Expression <Func <object[], object> > expObj = Expression.Lambda <Func <object[], object> >(newExp, paramExp);

            return(expObj.Compile());
        }
Пример #5
0
        public void RemoveLastCharTest()
        {
            var s = new StringBuilder();
            s.Append("hello");
            s.RemoveLastChar();
            Assert.AreEqual("hell", s.ToString());

            s.Clear();
            Assert.AreEqual("", s.ToString());
            s.RemoveLastChar();
            Assert.AreEqual("", s.ToString());

            s.Append('a');
            s.RemoveLastChar();
            Assert.AreEqual("", s.ToString());
        }
Пример #6
0
 /// <summary>
 /// Hashtable数据转URL字符串
 /// </summary>
 /// <param name="parameters">Hashtable</param>
 /// <returns></returns>
 public static string ToUrlEncode(this Hashtable parameters) {
     if (parameters.IsNull() || parameters.Count == 0) return string.Empty;
     StringBuilder sb = new StringBuilder();
     foreach (string k in parameters.Keys) sb.AppendFormat("{0}={1}&", k.UrlEncode(), parameters[k].ToString().UrlEncode());
     sb.RemoveLastChar("&");
     return sb.ToString();
 }
Пример #7
0
        public override string ToJson()
        {
            var sb = new StringBuilder();
            sb.Append("{");

            foreach (var keyVP in props)
            {
                sb.Append(keyVP.Key.AsJavaScript() + ":");
                if (keyVP.Value is JsBaseObject)
                    sb.Append(((JsBaseObject)keyVP.Value).ToJson() + ",");
                else
                    sb.Append(Json.Encode(keyVP.Value) + ",");
            }

            foreach (var keyVP in rawProps)
            {
                sb.Append(keyVP.Key.AsJavaScript() + ":");
                sb.Append(keyVP.Value + ",");
            }

            if (sb.Length > 1)
                sb.RemoveLastChar();
            sb.Append("}");
            return sb.ToString();
        }
Пример #8
0
        internal static SQLiteCommand UpdateCommand <T>(string tableName, T model)
        {
            var toReturn           = new SQLiteCommand();
            var command            = new StringBuilder($"UPDATE {tableName} SET ");
            var filteredProperties = GetFilteredProperties <T>();
            var modelType          = model.GetType();

            foreach (var prop in filteredProperties)
            {
                var propInfo = modelType.GetProperty(prop.Name);
                var value    = propInfo.GetValue(model);
                command.Append(prop.Name + "=");
                command.Append("$" + prop.Name + ",");
                toReturn.Parameters.AddWithValue("$" + prop.Name, value);
            }

            command.RemoveLastChar();
            var primaryKey = GetPrimaryKeyProperty <T>();
            var pkInfo     = modelType.GetProperty(primaryKey.Name);
            var pkValue    = pkInfo.GetValue(model);

            command.Append($" WHERE {primaryKey.Name} = ${primaryKey.Name}");
            toReturn.Parameters.AddWithValue("$" + primaryKey.Name, pkValue);
            toReturn.CommandText = command.ToString();
            return(toReturn);
        }
Пример #9
0
 [M(O.AggressiveInlining)] private static void ReplaceLastCharIfNotEqual(this StringBuilder sb, char ch)
 {
     if (sb[sb.Length - 1] != ch)
     {
         sb.RemoveLastChar().Append(ch);
     }
 }
Пример #10
0
        private void GenerateConfigurationCode_Click(object sender, EventArgs e)
        {
            TryRun(() =>
            {
                string filePath = filePath1.Text.Trim().移除路径前后引号();
                if (string.IsNullOrEmpty(filePath))
                {
                    MessageBox.Show("路径不能为空");
                    return;
                }
                var dataConfigInfo = new List <ExcelDataConfigInfo>();
                for (int i = 0; i < dgv1.Rows.Count; i++)
                {
                    dataConfigInfo.Add(new ExcelDataConfigInfo()
                    {
                        WorkSheetIndex = i + 1,
                        TitleLine      = Convert.ToInt32(dgv1.Rows[i].Cells[2].Value),
                        TitleColumn    = Convert.ToInt32(dgv1.Rows[i].Cells[3].Value)
                    });
                }

                string fileOutDirectoryName = Path.GetDirectoryName(Path.GetFullPath(filePath));
                var defaultConfigList       = EPPlusHelper.FillExcelDefaultConfig(filePath, fileOutDirectoryName, dataConfigInfo);

                var haveConfig = defaultConfigList.Find(a => a.ClassPropertyList.Count > 0) != null;
                if (!haveConfig)
                {
                    MessageBox.Show("未检测到配置信息");
                    return;
                }

                //将字符串写入文件
                StringBuilder errMsg = new StringBuilder();
                var filePathPrefix   = $@"{fileOutDirectoryName}\{Path.GetFileNameWithoutExtension(filePath)}_Result";

                foreach (var item in defaultConfigList)
                {
                    if (item.ClassPropertyList.Count > 0)
                    {
                        File.WriteAllText($@"{filePathPrefix}_{nameof(item.CrateDataTableSnippe)}_{item.WorkSheetName}.txt", item.CrateDataTableSnippe);
                        File.WriteAllText($@"{filePathPrefix}_{nameof(item.CrateClassSnippe)}_{item.WorkSheetName}.txt", item.CrateClassSnippe);
                    }
                    else
                    {
                        errMsg.Append(item.WorkSheetName + "、");
                    }
                }

                errMsg.RemoveLastChar('、');
                if (errMsg.Length > 0)
                {
                    MessageBox.Show($@"下列工作簿未生成配置项:{errMsg}");
                }
                if (!filePath.GetDirectoryName().Contains(@"\Desktop\"))
                {
                    WinFormHelper.OpenFilePath(filePath.GetDirectoryName());
                }
            });
        }
 public static StringBuilder RemoveLastChar(this StringBuilder value, char c)
 {
     if (value == null)
     {
         throw new System.ArgumentNullException(nameof(value));
     }
     return(value.Length <= 0 ? value : value[value.Length - 1] == c?value.RemoveLastChar() : value);
 }
Пример #12
0
        public void RemoveLastCharTest()
        {
            var s = new StringBuilder();

            s.Append("hello");
            s.RemoveLastChar();
            Assert.AreEqual("hell", s.ToString());

            s.Clear();
            Assert.AreEqual("", s.ToString());
            s.RemoveLastChar();
            Assert.AreEqual("", s.ToString());

            s.Append('a');
            s.RemoveLastChar();
            Assert.AreEqual("", s.ToString());
        }
Пример #13
0
        //#endregion
        /// <summary>
        /// 遍历 get/post 数据
        /// </summary>
        /// <returns>遍历 get/post 数据</returns>
        public static string GetRequest()
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("form:");
            for (int i = 0; i < HttpContext.Current.Request.Form.Count; i++)
            {
                sb.Append(HttpContext.Current.Request.Form.Keys[i].ToString() + "=" + HttpContext.Current.Request.Form[i].ToString() + "&");
            }
            sb.RemoveLastChar("&");
            sb.Append("querystring:");
            for (int i = 0; i < HttpContext.Current.Request.QueryString.Count; i++)
            {
                sb.Append(HttpContext.Current.Request.QueryString.Keys[i].ToString() + "=" + HttpContext.Current.Request.QueryString[i].ToString() + "&");
            }
            sb.RemoveLastChar("&");
            return(sb.ToString());
        }
Пример #14
0
        /// <summary>
        /// LIST参数转为URL参数
        /// </summary>
        /// <param name="param">LIST参数</param>
        /// <returns>URL参数</returns>
        public static string ToUrlEncode(this IList <UrlParameter> param)
        {
            StringBuilder ParameString = new StringBuilder();

            foreach (UrlParameter par in param)
            {
                ParameString.AppendFormat("{0}={1}&", par.ParameterName, par.ParameterValue.UrlEncode());
            }
            ParameString.RemoveLastChar("&");
            return(ParameString.ToString());
        }
Пример #15
0
        /// <summary>
        /// 分页SQL调用方法 只允许主键排序
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <param name="tableName">表名称</param>
        /// <param name="pk">主键</param>
        /// <param name="fieldList">字段列表</param>
        /// <param name="where">where条件 and or 开始</param>
        /// <param name="groupBy">分组条件</param>
        /// <param name="orderBy">排序条件</param>
        /// <returns>分页SQL</returns>
        public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "") {
            PagerSql sql = new PagerSql();
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select ");
            strSql.AppendFormat("count({0}) as total ", pk);
            if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName);
            if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
            if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
            sql.CountSql = strSql.ToString();
            //select * from Student
            //where Id in (
            //select top 10 Id
            //from(select top 3200010 Id from Student order by Id)t
            //order by Id desc)
            //order by Id

            //SELECT * FROM (
            //SELECT TOP 页面容量 * FROM (
            //SELECT TOP 页面容量*当前页码 * FROM
            //表 WHERE 条件 ORDER BY 字段A ASC
            //)AS  TEMPTABLE1 ORDER BY 字段A DESC
            //) AS TEMPTABLE2 ORDER BY 字段A ASC
            StringBuilder orderByExt = new StringBuilder();
            foreach (string order in orderBy.Split(',')) {
                string order2 = order.Trim();
                if (order2.EndsWith(" desc", true, null)) orderByExt.AppendFormat("{0} {1},", order2.Left(order2.Length - 5), "asc");
                else orderByExt.AppendFormat("{0} {1},", order2.EndsWith(" asc", true, null) ? order2.Left(order2.Length - 4) : order2, "desc");
            }
            orderByExt.RemoveLastChar(",");

            strSql.Clear();
            strSql.Append("select ");
            //if (distinct) strSql.Append("distinct ");
            if (pageSize == 1) {
                strSql.AppendFormat("top {0} ", pageSize);
                strSql.AppendFormat("{0} ", fieldList);
                if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName);
                if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
                if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
                if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy);
            } else {
                if (!tableName.IsNullEmpty()) strSql.AppendFormat("{1} from (select top {0} {1} from (select top {3} {1} from {2} ", pageSize, fieldList, tableName, pageSize * pageIndex);
                if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where);
                if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy);
                if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy);
                strSql.AppendFormat(") as Top1 {0}) as Top2 {1} ", "order by " + orderByExt, "order by " + orderBy);
            }
            sql.DataSql = strSql.ToString();

            return sql;
        }
Пример #16
0
        public MvcHtmlString ToHtmlStringOnlyMessages()
        {
            if (IsEmpty) return MvcHtmlString.Empty;

            var sb = new StringBuilder();

            foreach (var error in errorList)
            {
                sb.Append("<span>" + error.ErrorMessage.AsHtml() + "</span><br>");
            }
            sb.RemoveLastChar(4);
            return new MvcHtmlString(sb.ToString());
        }
Пример #17
0
        public override void EmitJoinSql(StringBuilder sql, List<string> withCTE, string indent)
        {
            if (GetJoinView().GetNativeTableRole() != null)
            {
                var sb = new StringBuilder();
                sb.Append("[" + GetJoinView().Name + "](");

                foreach (var col in GetJoinView().GetColumns())
                    //if (col is SchemaQuerySelectColumn)
                    sb.Append("[" + col.GetFullAlias() + "],");
                sb.Append("[__ID__],");
                sb.RemoveLastChar();

                sb.Append(") AS");
                sb.AppendLine("(");
                sb.Append(GetJoinView().GetNativeTableRole().GetSqlText(withCTE, null));
                sb.AppendLine("),");
                withCTE.Add(sb.ToString());
            }
            else
                if (GetJoinView().GetNativeQuery() != null)
            {
                var sb = new StringBuilder();
                sb.Append("[" + GetJoinView().Name + "](");

                foreach (var col in GetJoinView().GetColumns())
                    if (col is SchemaQuerySelectColumn)
                        sb.Append("[" + col.GetFullAlias() + "],");
                sb.Append("[__ID__],");
                sb.RemoveLastChar();

                sb.Append(") AS");
                sb.AppendLine("(");
                sb.Append(GetJoinView().GetNativeQuery().GetSqlText(withCTE));
                sb.AppendLine("),");
                withCTE.Add(sb.ToString());
            }

            sql.AppendLine(indent + "LEFT JOIN " + GetJoinView().Get4PartsTableName() + " AS [" + GetJoinTableFillAlias() + "]");

            foreach (var col in Columns)
                col.EmitJoinSql(sql, withCTE, indent + "  ");

            var keyFieldName = "__ID__";
            if (GetJoinView().GetNativeTable() != null)
                keyFieldName = GetJoinView().GetNativeTable().GetPrimaryKeyColumn().Name;

            sql.AppendLine(indent + "ON [" + GetJoinTableFillAlias() + "].[" + keyFieldName + "] = [" + ParentColumn.GetJoinTableFillAlias() + "].[" + Name + "]");
        }
Пример #18
0
        /// <summary>
        /// IDictionary数据转URL字符串 Join("=","&amp;")
        /// </summary>
        /// <param name="parameters">IDictionary</param>
        /// <param name="split1">分隔符1</param>
        /// <param name="split2">分隔符2</param>
        /// <returns></returns>
        public static string Join(this IDictionary parameters, string split1 = "=", string split2 = "&")
        {
            if (parameters.IsNull() || parameters.Count == 0)
            {
                return(string.Empty);
            }
            StringBuilder sb = new StringBuilder();

            foreach (string k in parameters.Keys)
            {
                sb.AppendFormat("{0}{2}{1}{3}", k, parameters[k].ToString(), split1, split2);
            }
            sb.RemoveLastChar(split2);
            return(sb.ToString());
        }
Пример #19
0
        /// <summary>
        /// IDictionary数据转URL字符串
        /// </summary>
        /// <param name="parameters">IDictionary</param>
        /// <returns></returns>
        public static string ToUrlEncode(this IDictionary parameters)
        {
            if (parameters.IsNull() || parameters.Count == 0)
            {
                return(string.Empty);
            }
            StringBuilder sb = new StringBuilder();

            foreach (string k in parameters.Keys)
            {
                sb.AppendFormat("{0}={1}&", k.UrlEncode(), parameters[k].ToString().UrlEncode());
            }
            sb.RemoveLastChar("&");
            return(sb.ToString());
        }
Пример #20
0
        public MvcHtmlString ToHtmlString()
        {
            if (IsEmpty) return MvcHtmlString.Empty;

            var sb = new StringBuilder();

            foreach (var error in errorList)
            {
                if (string.IsNullOrWhiteSpace(error.ErrorObjectName))
                    sb.Append(@"<span>" + error.ErrorMessage.AsHtml() + "</span><br>");
                else
                    sb.Append(@"<span>""" + error.ErrorObjectName.AsHtml() + @""": " + error.ErrorMessage.AsHtml() + "</span><br>");
            }
            sb.RemoveLastChar(4);
            return new MvcHtmlString(sb.ToString());
        }
Пример #21
0
            private string getParameterStringValue(string name, object obj, bool afterIn = false)
            {
                var temp = obj;

                foreach (var item in name.Splite1WithCount("."))
                {
                    var property = JasmineReflectionCache.Instance.GetItem(obj.GetType()).Properties.GetItemByName(item);

                    if (property == null)
                    {
                        throw new ParameterNotFoundException($"parameter @{name} can not be found by given isntance {obj} ");
                    }

                    temp = property.GetValue(temp);
                }

                if (afterIn)
                {
                    var type = temp.GetType();

                    if (type.CanConvertTo(typeof(IEnumerable)) && type.GetElementType().IsBaseType())
                    {
                        var elementType = type.GetElementType();

                        var builder = new StringBuilder();

                        builder.Append("(");

                        foreach (var item in (IEnumerable)temp)
                        {
                            builder.Append(DefaultBaseTypeConvertor.Instance.ConvertToSqlString(elementType, item))
                            .Append(",");
                        }

                        builder.RemoveLastChar();
                        builder.Append(")");

                        return(builder.ToString());
                    }
                    else
                    {
                        throw new Exception("parameter after keyword 'in' , must implement interface IEnumerable and element type is basetype ");
                    }
                }

                return(DefaultBaseTypeConvertor.Instance.ConvertToSqlString(temp.GetType(), temp));
            }
Пример #22
0
        public static string MakeInsertLeft(string table, params string[] columns)
        {
            var builder = new StringBuilder();

            builder.Append($"Insert Into {table}(");

            foreach (var item in columns)
            {
                builder.Append(item.Replace(".", "_")).Append(",");
            }

            builder.RemoveLastChar();

            builder.Append(") Values ");

            return(builder.ToString());
        }
Пример #23
0
        internal static SQLiteCommand CreateTableCommant <T>(string tableName)
        {
            var toReturn   = new SQLiteCommand();
            var command    = new StringBuilder($"CREATE TABLE IF NOT EXISTS {tableName}(");
            var properties = typeof(T).GetProperties();

            foreach (var prop in properties)
            {
                command.Append(prop.Name + " ");
                command.Append(prop.GetSQLiteType() + ",");
            }

            command.RemoveLastChar();
            command.Append(");");
            toReturn.CommandText = command.ToString();

            return(toReturn);
        }
Пример #24
0
        /// <summary>
        ///  根据 id, Name , parentId 3个字段生成额外字段Depth 和 用于报表排序的Sort字段
        /// </summary>
        /// <param name="tblName"></param>
        /// <param name="rootItemWhere"></param>
        /// <param name="nameFieldName"></param>
        /// <param name="idFiledName"></param>
        /// <param name="parentIdName"></param>
        /// <param name="eachSortFieldLength">每个Depth的长度,默认2. </param>
        /// <param name="reportSortFileTotallength">报表排序字段的总长度,默认为12如果真的要设置,level * Max(Len(主键))</param>
        /// <param name="rearChat">报表排序字段 / 每个Depth字段 小于 指定长度时填充的字符是什么</param>
        /// <param name="otherFiledName"></param>
        /// <returns></returns>
        public static string GetTreeTableReportSql(string tblName, string rootItemWhere, string nameFieldName = "Name", string idFiledName = "Id", string parentIdName = "ParentId", int eachSortFieldLength = 2, int reportSortFileTotallength = 12, char rearChat = ' ', params string[] otherFiledName)
        {
            //该方法基本与GetTreeTableIncludeLevelFieldSql()一样
            string        comma = " ,";
            string        dot   = ".";
            StringBuilder sb1   = new StringBuilder(); //定位成员的字段

            sb1.Append(idFiledName).Append(comma)
            .Append(nameFieldName).Append(comma)
            .Append(parentIdName);
            StringBuilder sb2 = new StringBuilder(); //递归成员的字段

            sb2.Append(tblName).Append(dot).Append(idFiledName).Append(comma)
            .Append(tblName).Append(dot).Append(nameFieldName).Append(comma)
            .Append(tblName).Append(dot).Append(parentIdName);

            string char1 = Enumerable.Repeat(rearChat.ToString(), eachSortFieldLength).Aggregate((current, next) => next + current);
            string char2 = Enumerable.Repeat(rearChat.ToString(), reportSortFileTotallength).Aggregate((current, next) => next + current);

            if (otherFiledName != null && otherFiledName.Length > 0)
            {
                foreach (var item in otherFiledName)
                {
                    sb1.Append(item).Append(comma);
                    sb2.Append(tblName).Append(dot).Append(item).Append(comma);
                }
                sb1.RemoveLastChar(comma.Length);
                sb2.RemoveLastChar(comma.Length);
            }

            string sql = $@"
with cte as( 
    SELECT {sb1} , 1 as Level , CAST( LEFT(LTRIM({idFiledName})+'{char1}',{eachSortFieldLength}) AS VARCHAR(10)) AS 'Depth'
    FROM {tblName} WHERE {rootItemWhere}
    UNION ALL
    SELECT {sb2} , cte.Level+1 as Level , CAST(LTRIM(cte.Depth) + LEFT(LTRIM({tblName}.{idFiledName}) +'{char1}',{eachSortFieldLength})AS VARCHAR(10)) AS 'Depth' 
    FROM cte, {tblName} 
    where cte.{idFiledName} = {tblName}.{parentIdName} 
)
SELECT {sb1} , Level,LEFT(LTRIM(cte.Depth)+'{char2}',{reportSortFileTotallength})  AS 'sort'  FROM cte
ORDER BY sort ,cte.Level";

            return(sql);
        }
Пример #25
0
        public override string ToJson()
        {
            var sb = new StringBuilder();
            sb.Append("[");

            foreach (var obj in props)
            {
                if (obj.isRaw)
                    sb.Append(obj.value.ToString() + ",");
                else
                if (obj.value is JsBaseObject)
                    sb.Append(((JsBaseObject)obj.value).ToJson() + ",");
                else
                    sb.Append(Json.Encode(obj.value) + ",");
            }
            if (sb.Length > 1)
                sb.RemoveLastChar();
            sb.Append("]");
            return sb.ToString();
        }
Пример #26
0
        /// <summary>
        /// 原本的树形表结构是没有Level字段的,通过该方法可以生成level字段
        /// </summary>
        /// <param name="tblName"></param>
        /// <param name="rootItemWhere"></param>
        /// <param name="nameFieldName"></param>
        /// <param name="idFiledName"></param>
        /// <param name="parentIdName"></param>
        /// <param name="otherFiledName"></param>
        /// <returns></returns>
        public static string GetTreeTableIncludeLevelFieldSql(string tblName, string rootItemWhere, string nameFieldName = "Name", string idFiledName = "Id", string parentIdName = "ParentId", params string[] otherFiledName)
        {
            string        comma = " ,";
            string        dot   = ".";
            StringBuilder sb1   = new StringBuilder(); //定位成员的字段

            sb1.Append(idFiledName).Append(comma)
            .Append(nameFieldName).Append(comma)
            .Append(parentIdName);
            StringBuilder sb2 = new StringBuilder(); //递归成员的字段

            sb2.Append(tblName).Append(dot).Append(idFiledName).Append(comma)
            .Append(tblName).Append(dot).Append(nameFieldName).Append(comma)
            .Append(tblName).Append(dot).Append(parentIdName);

            if (otherFiledName != null && otherFiledName.Length > 0)
            {
                foreach (var item in otherFiledName)
                {
                    sb1.Append(item).Append(comma);
                    sb2.Append(tblName).Append(dot).Append(item).Append(comma);
                }
                sb1.RemoveLastChar(comma.Length);
                sb2.RemoveLastChar(comma.Length);
            }

            string sql = $@"
with cte as( 
    SELECT {sb1} , 1 as Level FROM {tblName} WHERE {rootItemWhere}
    UNION ALL
    SELECT {sb2} , cte.Level+1 as Level from cte, {tblName}  
        where cte.{idFiledName} = {tblName}.{parentIdName} 
)
SELECT {sb1} , Level FROM  cte
ORDER BY cte.Level";

            return(sql);
        }
Пример #27
0
        public static void SaveDictionaryList <TKey, TValue>(this IDictionary <TKey, List <TValue> > dictList, string file)
        {
            var sw = new StreamWriter(file);
            var sb = new StringBuilder(255);

            foreach (var key in dictList.Keys)
            {
                sb.Append('"');
                sb.Append(key);
                sb.Append("\",");
                foreach (var val in dictList[key])
                {
                    sb.Append('"');
                    sb.Append(val);
                    sb.Append("\",");
                }
                sb.RemoveLastChar();

                sw.WriteLine(sb.ToString());
                sb.Clear();
            }
            sw.Close();
        }
        protected override IEnumerator <KeyValuePair <string, T> > CreateEnumerator()
        {
            Stack <TernarySearchTreeNode <T> > stack = new Stack <TernarySearchTreeNode <T> >();
            StringBuilder stringBuilder = new StringBuilder(32);

            TernarySearchTreeNode <T> node = root;

            while (true)
            {
                while (node != null)
                {
                    stack.Push(node);
                    node = node.Left;
                }
                if (stack.IsEmpty)
                {
                    yield break;
                }
                node = stack.Peek();
                if (node != null)
                {
                    stringBuilder.Append(node.Char);
                    if (node.IsEos)
                    {
                        yield return(new KeyValuePair <string, T>(stringBuilder.ToString(), node.Value));
                    }
                    stack.Push(null);
                    node = node.Equal;
                }
                else
                {
                    stringBuilder.RemoveLastChar();
                    stack.Pop();
                    node = stack.Pop().Right;
                }
            }
        }
Пример #29
0
 /// <summary>
 /// LIST参数转为URL参数
 /// </summary>
 /// <param name="param">LIST参数</param>
 /// <returns>URL参数</returns>
 public static string GetUrlParameter(List<UrlParameter> param) {
     StringBuilder ParameString = new StringBuilder();
     foreach (UrlParameter par in param) ParameString.AppendFormat("{0}={1}&", par.ParameterName, par.ParameterValue);
     ParameString.RemoveLastChar("&");
     return ParameString.ToString();
 }
Пример #30
0
        public void EmitSaveToLogSql(StringBuilder sql, string id_variable, SchemaTable masterTable, string indent)
        {
            sql.Append(indent + "INSERT [" + GetLogTable().Name + "] (");

            foreach (var col in GetLogTable().Columns)
            {
                if (col.Name == "__lockuser__" || col.Name == "__locksession__" || col.Name == "__locktime__") continue;

                sql.Append("  [" + col.Name + "],");
            }

            sql.RemoveLastChar(1);
            sql.AppendLine(")");
            sql.Append(indent + "SELECT ");
            foreach (var col in GetLogTable().Columns)
            {
                if (col.Name == "__lockuser__" || col.Name == "__locksession__" || col.Name == "__locktime__") continue;
                if (col.Name == "__mastertimestamp__")
                    sql.Append("  @master_timestamp,");
                //sql.Append("  ( SELECT __timestamp__  FROM [" + masterTable.Name + "] WHERE ID=[" + Name + "].[" + GetMasterColumn().Name + "]),");
                else
                    sql.Append("  [" + col.Name + "],");
            }
            sql.RemoveLastChar(1);
            sql.AppendLine(" FROM [" + Name + "] WHERE ID=" + id_variable);

            int detNum = App.Random.Next(1, int.MaxValue / 2);
            foreach (var detail in Details)
            {
                detNum++;
                var detailTable = detail.GetDetailTable();
                sql.AppendLine(indent + "DECLARE @detid" + detNum + " uniqueidentifier");
                sql.AppendLine(indent + "DECLARE curs" + detNum + " CURSOR LOCAL STATIC FOR");
                sql.AppendLine(indent + "SELECT ID FROM [" + detailTable.Name + "] WHERE [" + detailTable.GetMasterColumn().Name + "]=" + id_variable);
                sql.AppendLine(indent + "OPEN curs" + detNum);
                sql.AppendLine(indent + "FETCH NEXT FROM curs" + detNum + " INTO @detid" + detNum);
                sql.AppendLine(indent + "WHILE @@FETCH_STATUS = 0");
                sql.AppendLine(indent + "BEGIN");
                detailTable.EmitSaveToLogSql(sql, "@detid" + detNum, this, indent + "  ");
                sql.AppendLine(indent + "  FETCH NEXT FROM curs" + detNum + " INTO @detid" + detNum);
                sql.AppendLine(indent + "END");
                sql.AppendLine(indent + "CLOSE curs" + detNum);
                sql.AppendLine(indent + "DEALLOCATE curs" + detNum);

            }
        }
Пример #31
0
        public static string Clean(string name, char wordsSeparator = '.')
        {
            if (name.IsNullOrEmpty())
            {
                return(name);
            }

            var array = name.Split(PUNCTUATION_CHARS, StringSplitOptions.RemoveEmptyEntries);

            #region [.phase #1.]
            var firstWholeNumbersWord = default(string);
            var words = new List <(string word, bool isOnlyLetters)>(array.Length);
            foreach (var s in array)
            {
                if (EXCLUDES_WORDS_SET.Contains(s))
                {
                    continue;
                }
                if (int.TryParse(s, out var _))
                {
                    if (firstWholeNumbersWord == null)
                    {
                        firstWholeNumbersWord = s;
                    }
                    continue;
                }

                words.Add((s, s.IsOnlyLetters()));
            }
            if (!words.Any())
            {
                var s = (firstWholeNumbersWord ?? "---");
                words.Add((s, s.IsOnlyLetters()));
            }
            #endregion

            #region [.phase #2.]
            const char SPACE = ' ';

            var sb = new StringBuilder(name.Length);
            var prev_word_isOnlyLetters = true;
            foreach (var t in words)
            {
                if (t.word.TryParseSE(out var se))
                {
                    if (!sb.IsEmpty())
                    {
                        sb.ReplaceLastCharIfNotEqual(wordsSeparator);
                    }
                    sb.Append(se).Append(wordsSeparator);
                    break;
                }

                if (prev_word_isOnlyLetters)   //&& t.isOnlyLetters )
                {
                    sb.AppendFirstCharToUpper(t.word).Append((t.isOnlyLetters ? SPACE : wordsSeparator));
                }
                else
                {
                    sb.Append(t.word).Append(wordsSeparator);
                }
                prev_word_isOnlyLetters = t.isOnlyLetters;
            }
            sb.RemoveLastChar();
            #endregion

            return(sb.ToString());
        }
Пример #32
0
        public void SaveData()
        {
            CheckParams();
            if (CrudMode == CrudMode.View)
            {
                throw new Exception("Запись только на чтение в таблице '" + Table.Name + "', ID='" + ID + "'");
            }
            else
                if (CrudMode == CrudMode.Add)
                {
                    if (MasterTableRow != null)
                    {
                        var masterColumn = Table.GetMasterColumn();
                        if (masterColumn == null)
                            throw new Exception("'В таблице '" + Table.Name + "' отсутствует колонка с ролью '^ВложеннаяТаблица.Мастер'.");

                        this[masterColumn.Name] = MasterTableRow.ID;
                    }
                    if (NewBussinesOperID != null)
                    {
                        var bussinesOperColumn = Table.GetColumnByRole(RoleConst.ВложеннаяТаблица_БизнесОперация);
                        if (bussinesOperColumn == null)
                            throw new Exception("'В таблице '" + Table.Name + "' отсутствует колонка с ролью '^ВложеннаяТаблица.БизнесОперация'.");

                        this[bussinesOperColumn.Name] = NewBussinesOperID;
                    }
                    if (!NewValues.ContainsKey(Table.GetPrimaryKeyColumn().Name))
                        NewValues.Add(Table.GetPrimaryKeyColumn().Name, ID);

                    if (!NewValues.ContainsKey("__changeuser__"))
                        NewValues.Add("__changeuser__", App.UserID);
                    else
                        NewValues["__changeuser__"] = App.UserID;

                    if (!NewValues.ContainsKey("__changetime__"))
                        NewValues.Add("__changetime__", DateTime.Now);

                    if (NewValues.Values.Count > 0)
                    {

                        var sql = new StringBuilder();
                        sql.AppendLine("BEGIN TRAN");

                        sql.AppendLine("INSERT " + Table.Get4PartsTableName() + " (");

                        foreach (var col in Table.Columns)
                        {
                            if (NewValues.ContainsKey(col.Name))
                                sql.AppendLine("  [" + col.Name + "],");
                        }

                        sql.RemoveLastChar(3);
                        sql.AppendLine(")");
                        sql.AppendLine("VALUES (");
                        foreach (var col in Table.Columns)
                        {
                            if (NewValues.ContainsKey(col.Name))
                                if (col.Name == "__changetime__")
                                    sql.AppendLine("GetDate(),");
                                else
                                    sql.AppendLine(NewValues[col.Name].AsSQL() + ",");
                        }
                        sql.RemoveLastChar(3);
                        sql.AppendLine(")");

                        //// __undo__
                        //if (MasterTableRow != null)
                        //{
                        //    var undo_sql = "DELETE FROM [" + Table.Name + "] WHERE ID=" + ID.AsSQL();

                        //    sql.AppendLine("INSERT [__EditSession__]([SessionID],[ParentSessionID],[TableID],[RecordID],[UserID],[UndoSql])");
                        //    sql.AppendLine("VALUES (" + EditSessionID.AsSQL() + "," + MasterTableRow.EditSessionID.AsSQL() + "," + Table.ID.AsSQL() + "," +
                        //                                ID.AsSQL() + "," + App.UserID.AsSQL() + "," + undo_sql.AsSQL() + ")");
                        //}
                        //else
                        //{
                        //    foreach (var detailTable in App.Schema.GetAllDetailTablesSamples())
                        //    {
                        //        sql.AppendLine("DELETE FROM [" + detailTable.Name + "] WHERE [" + detailTable.GetMasterColumn().Name + "]=" + ID.Reverse().AsSQL());
                        //    }
                        //    sql.Append("EXEC __EditSession__Commit ");
                        //    sql.Append("@sessionID=" + EditSessionID.AsSQL() + ",");
                        //    sql.AppendLine("@parentSessionID=NULL");
                        //}
                        sql.AppendLine("COMMIT");

                        if (MasterTableRow != null && MasterTableRow.BeforeSaveChanges != null)
                            MasterTableRow.BeforeSaveChanges(this);

                        if (BeforeSaveChanges != null)
                            BeforeSaveChanges(this);

                        using (var db = App.Schema.SqlDB.GetDbManager())
                        {
                            int updatedRows = db.SetCommand(sql.ToString()).ExecuteNonQuery();
                            if (updatedRows == 0)
                                throw new Exception("Ошибка добавления записи в таблицу '" + Table.Name + "', ID='" + ID + "'");
                        }
                        CrudMode = Buhta.CrudMode.Edit;

                        foreach (var columnName in NewValues.Keys)
                        {
                            if (Values.ContainsKey(columnName))
                                Values[columnName] = NewValues[columnName];
                            else
                                Values.Add(columnName, NewValues[columnName]);
                        }
                        NewValues.Clear();
                    }
                }
                else
                    if (CrudMode == CrudMode.Edit)
                    {
                        if (NewValues.ContainsKey(Table.GetPrimaryKeyColumn().Name))
                            throw new Exception("Запрещено изменять ID в таблице '" + Table.Name + "', ID='" + ID + "'");

                        //var log = new StringBuilder();
                        //var log_col = new StringBuilder();
                        //var log_sel = new StringBuilder();

                        //var logTable = Table.GetLogTable();

                        var sql = new StringBuilder();

                        //if (logTable != null)
                        //{
                        //    sql.AppendLine("BEGIN TRAN");
                        //}

                        if (!NewValues.ContainsKey("__changeuser__"))
                            NewValues.Add("__changeuser__", App.UserID);
                        else
                            NewValues["__changeuser__"] = App.UserID;

                        if (!NewValues.ContainsKey("__changetime__"))
                            NewValues.Add("__changetime__", DateTime.Now);

                        sql.AppendLine("BEGIN TRAN");

                        var updateSql = new StringBuilder();
                        bool needUpdate = false;
                        if (NewValues.Values.Count > 0)
                        {
                            updateSql.AppendLine("UPDATE " + Table.Get4PartsTableName() + " SET");

                            foreach (var col in Table.Columns)
                            {
                                if (col.Name != Table.GetPrimaryKeyColumn().Name && NewValues.ContainsKey(col.Name))
                                {
                                    updateSql.Append("  [" + col.Name + "] = ");
                                    needUpdate = true;
                                    if (col.Name == "__changetime__")
                                        updateSql.Append("GetDate()");
                                    else
                                    {
                                        if (NewValues[col.Name] == null)
                                            updateSql.Append("NULL");
                                        else
                                            updateSql.Append(NewValues[col.Name].AsSQL());
                                    }
                                    updateSql.AppendLine(",");

                                    //if (logTable!=null)
                                    //{
                                    //    log_col.Append("[" + col.Name + "],");
                                    //    log_sel.Append("[" + col.Name + "],");
                                    //}
                                }
                            }

                            updateSql.RemoveLastChar(3);
                            updateSql.AppendLine();
                            updateSql.AppendLine("WHERE [" + Table.GetPrimaryKeyColumn().Name + "]=" + ID.AsSQL());
                        }

                        if (needUpdate)
                            sql.Append(updateSql);
                        //if (MasterTableRow != null)
                        //{
                        //    var undo_sql = new StringBuilder();
                        //    undo_sql.AppendLine("UPDATE [" + Table.Name + "] SET");

                        //    foreach (var col in Table.Columns)
                        //    {
                        //        if (col.Name != "ID" && NewValues.ContainsKey(col.Name))
                        //        {
                        //            undo_sql.Append("  [" + col.Name + "] = ");
                        //            if (Values[col.Name] == null)
                        //                undo_sql.Append("NULL");
                        //            else
                        //                undo_sql.Append(Values[col.Name].AsSQL());
                        //            undo_sql.AppendLine(",");
                        //        }
                        //    }
                        //    undo_sql.RemoveLastChar(3);
                        //    undo_sql.AppendLine();
                        //    undo_sql.AppendLine("WHERE ID=" + ID.AsSQL());

                        //    sql.AppendLine("INSERT [__EditSession__]([SessionID],[ParentSessionID],[TableID],[RecordID],[UserID],[UndoSql])");
                        //    sql.AppendLine("VALUES (" + EditSessionID.AsSQL() + "," + MasterTableRow.EditSessionID.AsSQL() + "," + Table.ID.AsSQL() + "," +
                        //                                ID.AsSQL() + "," + App.UserID.AsSQL() + "," + undo_sql.AsSQL() + ")");

                        //}
                        //else
                        //{
                        //    foreach (var detailTable in App.Schema.GetAllDetailTablesSamples())
                        //    {
                        //        sql.AppendLine("DELETE FROM [" + detailTable.Name + "] WHERE [" + detailTable.GetMasterColumn().Name + "]=" + ID.Reverse().AsSQL());
                        //    }

                        //    sql.Append("EXEC __EditSession__Commit ");
                        //    sql.Append("@sessionID=" + EditSessionID.AsSQL() + ",");
                        //    sql.AppendLine("@parentSessionID=NULL");
                        //}
                        sql.AppendLine("COMMIT");

                        //if (logTable != null)
                        //{
                        //    log_col.Append("["+SchemaTable.logPrefix+""+"]");

                        //    log.AppendLine("INSERT [" + logTable.Name + "](" + log_col.ToString() + ")");
                        //    log.AppendLine("SELECT " + log_sel.ToString() + " FROM [" + Table.Name + "] WHERE ID=" + ID.AsSQL());

                        //    sql.Insert(0, log.ToString());
                        //    sql.AppendLine("COMMIT");
                        //}

                        if (MasterTableRow != null && MasterTableRow.BeforeSaveChanges != null)
                            MasterTableRow.BeforeSaveChanges(this);

                        if (BeforeSaveChanges != null)
                            BeforeSaveChanges(this);

                        using (var db = App.Schema.SqlDB.GetDbManager())
                        {
                            int updatedRows = db.SetCommand(sql.ToString()).ExecuteNonQuery();
                            if (updatedRows == 0)
                                throw new Exception("Ошибка изменения записи в таблице '" + Table.Name + "', ID='" + ID + "'");
                        }

                        if (BeforeSaveChanges != null)
                            BeforeSaveChanges(this);
                        foreach (var columnName in NewValues.Keys)
                        {
                            if (Values.ContainsKey(columnName))
                                Values[columnName] = NewValues[columnName];
                            else
                                Values.Add(columnName, NewValues[columnName]);
                        }
                        NewValues.Clear();
                    }

            if (Table.GetIsProvodkaGenerationExists())
                Table.ExecGenStoredProc(ID);

            if (OnSaveData != null)
                OnSaveData(this, null);
        }
Пример #33
0
        public void EmitOrderBySql(StringBuilder sql)
        {
            var cols = GetAllColumns()
                     .Where(col => col is SchemaQuerySelectColumn && (col as SchemaQuerySelectColumn).OrderBy != QueryOrderBy.None)
                     .OrderBy(col => (col as SchemaQuerySelectColumn).OrderBy).ToList();
            if (cols.Count > 0)
            {
                sql.AppendLine("ORDER BY");
                foreach (var col in cols)
                {
                    if ((col as SchemaQuerySelectColumn).OrderBy.ToString().StartsWith("Desc"))
                        sql.AppendLine("  [" + col.GetFullAlias() + "] DESC,");
                    else
                        sql.AppendLine("  [" + col.GetFullAlias() + "],");
                }
                sql.RemoveLastChar(3);
                sql.AppendLine();
                if (!string.IsNullOrWhiteSpace(RuntimeOffsetRows))
                {
                    sql.AppendLine("OFFSET " + RuntimeOffsetRows + " ROWS");
                }
                if (!string.IsNullOrWhiteSpace(RuntimeFetchRows))
                {
                    if (string.IsNullOrWhiteSpace(RuntimeOffsetRows))
                        throw new Exception("Запрос "+Name.AsSQL()+": не заполнен RuntimeOffsetRow.");
                    sql.AppendLine("FETCH NEXT " + RuntimeFetchRows + " ROWS ONLY");
                }
            }
            else
            {
               if (!string.IsNullOrWhiteSpace(RuntimeOffsetRows) || !string.IsNullOrWhiteSpace(RuntimeFetchRows))
                   throw new Exception("Запрос " + Name.AsSQL() + ": нельзя использовать RuntimeOffsetRows и/или RuntimeFetchRows без указания сортировки в запросе.");

            }
        }
Пример #34
0
        private void CheckTemplateConfiguration_Click(object sender, EventArgs e)
        {
            TryRun(() =>
            {
                var ws1Path = this.filePath1.Text.Trim().移除路径前后引号();
                var ws2Path = this.filePath2.Text.Trim().移除路径前后引号();
                if (string.IsNullOrEmpty(ws1Path))
                {
                    MessageBox.Show("路径1不能为空");
                    return;
                }
                if (string.IsNullOrEmpty(ws2Path))
                {
                    MessageBox.Show("路径2不能为空");
                    return;
                }
                if (ws1Path == ws2Path)
                {
                    MessageBox.Show("比较文件路径一致,无法比较");
                    return;
                }

                var ws1Index_string = this.wsNameOrIndex1.Text.Trim();
                var ws2Index_string = this.wsNameOrIndex2.Text.Trim();

                var ws1TitleLine = Convert.ToInt32(this.TitleLine1.Text.Trim());
                var ws2TitleLine = Convert.ToInt32(this.TitleLine2.Text.Trim());

                var ws1TitleCol = Convert.ToInt32(this.TitleCol1.Text.Trim());
                var ws2TitleCol = Convert.ToInt32(this.TitleCol2.Text.Trim());

                using (var fs1 = new FileStream(ws1Path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                    using (var fs2 = new FileStream(ws2Path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                        using (var excelPackage1 = new ExcelPackage(fs1))
                            using (var excelPackage2 = new ExcelPackage(fs2))
                            {
                                var ws1      = GetWorkSheet(excelPackage1, ws1Index_string);
                                var ws2      = GetWorkSheet(excelPackage2, ws2Index_string);
                                var ws1Props = EPPlusHelper.FillExcelDefaultConfig(ws1, ws1TitleLine, ws1TitleCol).ClassPropertyList;
                                var ws2Props = EPPlusHelper.FillExcelDefaultConfig(ws2, ws2TitleLine, ws2TitleCol).ClassPropertyList;
                                {
                                    StringBuilder sb = new StringBuilder();
                                    AppendCols(ws1Props, ws2Props, sb);
                                    if (sb.Length > 1)
                                    {
                                        MessageBox.Show($@"A与B比较:B未提供列:{sb.RemoveLastChar()}");
                                        return;
                                    }
                                }

                                {
                                    StringBuilder sb = new StringBuilder();
                                    AppendCols(ws2Props, ws1Props, sb);

                                    if (sb.Length > 1)
                                    {
                                        MessageBox.Show($@"A与B比较:B多提供列:{sb.RemoveLastChar()}");
                                        return;
                                    }
                                }

                                MessageBox.Show("A与B比较:内容一致");
                            }
            });
        }
Пример #35
0
        public string GetAttrs()
        {
            var sb = new StringBuilder();
            if (classes.Count > 0 || classes.Count > 0)
            {
                sb.Append(@"class=""");
                foreach (var cls in classes)
                {
                    if (!classes.Contains(cls))
                        sb.Append(cls + " ");
                }
                foreach (var cls in classes)
                    sb.Append(cls + " ");
                sb.RemoveLastChar();
                sb.Append(@""" ");
            }
            if (styles.Keys.Count > 0 || styles.Keys.Count > 0)
            {
                sb.Append(@"style=""");
                foreach (var stl in styles)
                {
                    if (!styles.ContainsKey(stl.Key))
                        sb.Append(stl.Key + ":" + stl.Value + ";");
                }
                foreach (var stl in styles)
                    sb.Append(stl.Key + ":" + stl.Value + ";");
                sb.Append(@""" ");
            }

            foreach (var attr in attrs)
            {
                if (!attrs.ContainsKey(attr.Key))
                    sb.Append(attr.Key + "=" + attr.Value.AsJavaScript() + ";");
            }

            foreach (var attr in attrs)
            {
                sb.Append(attr.Key + "=" + attr.Value.AsJavaScript() + ";");
            }

            return sb.ToString();
        }
Пример #36
0
        void EmitGenerateProvodkaDbKrPartToTempTable(StringBuilder sql, string indent, SchemaTableProvodka prov, DbKrSaldo dbKr, SchemaTable registr, ObservableCollection<SchemaTableProvodkaField> registrFields)
        {
            var masterTable = this;
            var detailTable = prov.OwnerTableOper.TableDetail.GetDetailTable();
            var operID = prov.OwnerTableOper.ID;

            sql.AppendLine(indent + "-- " + dbKr + ", регистр: " + registr.Name.AsSQL() + ",  деталь: " + prov.OwnerTableOper.TableDetail.Name.AsSQL() + ",  операция: '[" + prov.OwnerTableOper.Num + "]  " + prov.OwnerTableOper.Name + "',  проводка: " + prov.Name.AsSQL());
            sql.AppendLine(indent + "INSERT " + registr.GetRegistrTempTableName() + "(");

            sql.AppendLine(indent + "  [" + registr.GetColumnByRole(RoleConst.Таблица_Ключ).Name + "],");
            sql.AppendLine(indent + "  [" + registr.GetColumnByRole(RoleConst.Регистр_ДбКр).Name + "],");
            sql.AppendLine(indent + "  [" + registr.GetColumnByRole(RoleConst.Регистр_КонфигДеталь).Name + "],");
            sql.AppendLine(indent + "  [" + registr.GetColumnByRole(RoleConst.Регистр_КонфигБизнесОперация).Name + "],");
            sql.AppendLine(indent + "  [" + registr.GetColumnByRole(RoleConst.Регистр_КонфигПроводка).Name + "],");

            sql.AppendLine(indent + "  [" + registr.GetColumnByRole(RoleConst.Регистр_Мастер).Name + "],");
            sql.AppendLine(indent + "  [" + registr.GetColumnByRole(RoleConst.Регистр_Деталь).Name + "],");

            foreach (var field in registrFields.Where(field => registr.GetColumnByName(field.RegistrFieldName) != null))
            {
                sql.AppendLine(indent + "  [" + field.RegistrFieldName + "],");
            }

            sql.RemoveLastChar(3);
            sql.AppendLine();

            sql.AppendLine(indent + ")");
            sql.AppendLine(indent + "SELECT");

            sql.AppendLine(indent + "  NewID(),");
            sql.AppendLine(indent + "  '" + dbKr.ToString()[0] + "',");
            sql.AppendLine(indent + "  " + prov.OwnerTableOper.TableDetail.ID.AsSQL() + ",  -- деталь: " + prov.OwnerTableOper.TableDetail.Name);
            sql.AppendLine(indent + "  " + prov.OwnerTableOper.ID.AsSQL() + ",  -- бизнес-операция: " + prov.OwnerTableOper.Name);
            sql.AppendLine(indent + "  " + prov.ID.AsSQL() + ",  -- проводка: " + prov.Name);

            sql.AppendLine(indent + "  @masterID,");
            sql.AppendLine(indent + "  " + detailTable.Get4PartsTableName() + ".[" + detailTable.GetColumnByRole(RoleConst.Таблица_Ключ).Name + "],");

            foreach (var field in registrFields.Where(field => registr.GetColumnByName(field.RegistrFieldName) != null))
            {
                if (field.DataFieldName == "<пусто>")
                    sql.AppendLine(indent + "  NULL,");
                else
                {
                    if (dbKr == DbKrSaldo.Кредит && registr.GetColumnByName(field.RegistrFieldName).ColumnRoles.Contains(RoleConst.Регистр_Мера))
                        sql.AppendLine(indent + "  -" + detailTable.Get4PartsTableName() + ".[" + field.DataFieldName + "],");
                    else
                        sql.AppendLine(indent + "  " + detailTable.Get4PartsTableName() + ".[" + field.DataFieldName + "],");
                }
            }

            sql.RemoveLastChar(3);
            sql.AppendLine();

            sql.AppendLine(indent + "FROM " + detailTable.Get4PartsTableName());
            sql.AppendLine(indent + "WHERE " + detailTable.Get4PartsTableName() + ".[" + detailTable.GetColumnByRole(RoleConst.ВложеннаяТаблица_БизнесОперация).Name + "]=" + operID.AsSQL() + " AND ");
            sql.AppendLine(indent + "      " + detailTable.Get4PartsTableName() + ".[" + detailTable.GetColumnByRole(RoleConst.ВложеннаяТаблица_Мастер).Name + "]=@masterID");
        }
Пример #37
0
        void EmitSaveRegistrFromTempTable(StringBuilder sql, string indent, SchemaTable registrTable)
        {
            sql.Append(indent + "INSERT " + registrTable.Get4PartsTableName() + "(");
            foreach (var col in registrTable.Columns)
                sql.Append("[" + col.Name + "],");
            sql.RemoveLastChar(1);
            sql.Append(") SELECT ");
            foreach (var col in registrTable.Columns)
                sql.Append("[" + col.Name + "],");
            sql.RemoveLastChar(1);
            sql.AppendLine(" FROM " + registrTable.GetRegistrTempTableName());

            sql.Append(indent + "INSERT " + GetProvodkasTable().Get4PartsTableName() + "(tableRecordID,provodkaRecordID,registrID)");
            sql.AppendLine(" SELECT @masterID,[" + registrTable.GetPrimaryKeyColumn().Name + "]," + registrTable.ID.AsSQL() + " FROM " + registrTable.GetRegistrTempTableName());
        }
Пример #38
0
 void EmitCreateRegisterTempTable(StringBuilder sql, string indent, SchemaTable registrTable)
 {
     sql.AppendLine(indent + "CREATE TABLE " + registrTable.GetRegistrTempTableName() + "(");
     foreach (var col in registrTable.Columns)
     {
         sql.AppendLine(indent + "  [" + col.Name + "] " + col.DataType.GetDeclareSql() + ",");
     }
     sql.RemoveLastChar(3);
     sql.AppendLine();
     sql.AppendLine(indent + ")");
 }
Пример #39
0
        public override void EmitJoinSql(StringBuilder sql, List<string> withCTE, string indent)
        {
            if (GetJoinView() is SchemaVirtualTable)
            {
                GetJoinView().EmitJoinSql(sql, withCTE, indent);

            }
            else
            {
                if (GetJoinView().GetNativeTableRole() != null)
                {
                    var sb = new StringBuilder();
                    sb.Append("[" + GetJoinView().Name + "](");

                    foreach (var col in GetJoinView().GetColumns())
                        //if (col is SchemaQuerySelectColumn)
                        sb.Append("[" + col.GetFullAlias() + "],");
                    sb.Append("[__ID__],");
                    sb.RemoveLastChar();

                    sb.Append(") AS");
                    sb.AppendLine("(");
                    sb.Append(GetJoinView().GetNativeTableRole().GetSqlText(withCTE, ParentQuery.RuntimeRecordIDFilter));
                    sb.AppendLine("),");
                    withCTE.Add(sb.ToString());
                }

                if (GetJoinView().GetNativeQuery() != null)
                {
                    var sb = new StringBuilder();
                    sb.Append("[" + GetJoinView().Name + "](");

                    foreach (var col in GetJoinView().GetColumns())
                        if (col is SchemaQuerySelectColumn)
                            sb.Append("[" + col.GetFullAlias() + "],");
                    sb.Append("[__ID__],");
                    sb.RemoveLastChar();

                    sb.Append(") AS");
                    sb.AppendLine("(");
                    sb.Append(GetJoinView().GetNativeQuery().GetSqlText(withCTE));
                    sb.AppendLine("),");
                    withCTE.Add(sb.ToString());
                }

                sql.AppendLine("FROM");
                //sql.AppendLine("  [" + GetJoinView().Name + "] AS [" + GetJoinTableFillAlias() + "]");
                sql.AppendLine("  " + GetJoinView().Get4PartsTableName() + " AS [" + GetJoinTableFillAlias() + "]");

            }

            foreach (var col in Columns)
                col.EmitJoinSql(sql, withCTE, indent + "  ");
        }
Пример #40
0
 //#endregion
 /// <summary>
 /// 遍历 get/post 数据
 /// </summary>
 /// <returns>遍历 get/post 数据</returns>
 public static string GetRequest() {
     StringBuilder sb = new StringBuilder();
     sb.Append("form:");
     for (int i = 0; i < HttpContext.Current.Request.Form.Count; i++) sb.Append(HttpContext.Current.Request.Form.Keys[i].ToString() + "=" + HttpContext.Current.Request.Form[i].ToString() + "&");
     sb.RemoveLastChar("&");
     sb.Append("querystring:");
     for (int i = 0; i < HttpContext.Current.Request.QueryString.Count; i++) sb.Append(HttpContext.Current.Request.QueryString.Keys[i].ToString() + "=" + HttpContext.Current.Request.QueryString[i].ToString() + "&");
     sb.RemoveLastChar("&");
     return sb.ToString();
 }
Пример #41
0
        public string GetSqlText(List<string> withCTE)
        {
            bool cteMode = withCTE != null;  //режим вложенного sql (Common Table Expression)
            if (!cteMode)
                withCTE = new List<string>();

            var withCTESql = new StringBuilder();
            var commentSql = new StringBuilder();
            var selectSql = new StringBuilder();
            var fromSql = new StringBuilder();
            var joinSql = new StringBuilder();
            var whereSql = new StringBuilder();
            var orderbySql = new StringBuilder();

            whereSql.AppendLine("WHERE");

            commentSql.Append("-- запрос: " + Name + ",  ");
            if (!cteMode)
                commentSql.Append("компьютер: " + System.Environment.MachineName);
            commentSql.AppendLine();

            if (RuntimeTop != null)
                selectSql.AppendLine("SELECT TOP " + RuntimeTop);
            else
                selectSql.AppendLine("SELECT");

            rootColumn.EmitSelectSql(selectSql, "  ");

            if (!string.IsNullOrWhiteSpace(RuntimeIntoTableName))
            {
                selectSql.RemoveLastChar(3);
                selectSql.AppendLine();
                selectSql.AppendLine("INTO [" + RuntimeIntoTableName + "] "); // последний пробел не удалять!
            }

            rootColumn.EmitJoinSql(joinSql, withCTE, "");

            selectSql.RemoveLastChar(3);
            selectSql.AppendLine();

            if (!cteMode && withCTE.Count > 0)
            {
                withCTESql.AppendLine("WITH");

                List<string> duplicates = new List<string>();

                foreach (string cteStr in withCTE)
                {
                    if (!duplicates.Contains(cteStr))
                    {
                        withCTESql.AppendLine(cteStr);
                        duplicates.Add(cteStr);
                    }
                }
                withCTESql.RemoveLastChar(5);
                withCTESql.AppendLine();
            }

            if (MasterTableRow != null)  // фильтруем detail-записи
            {
                var masterColumn = GetRootNativeTable().GetMasterColumn();
                if (masterColumn == null)
                    throw new Exception("Запрос '" + Name + "': в таблице " + GetRootNativeTable().Name + " отсутствует колонка с типом '" + new MasterDataType().GetNameDisplay + "'.");
                whereSql.AppendLine("  [" + GetFullAlias() + "].[" + masterColumn.Name + "]=" + MasterTableRow.ID.AsSQL() + " AND");
            }

            if (!string.IsNullOrWhiteSpace(RuntimeLookupSearchFilterString))
            {
                var searchWords = RuntimeLookupSearchFilterString.Split(' ');
                foreach (var word in searchWords)
                {
                    whereSql.Append("(");
                    foreach (var col in GetAllColumns().Where(col => col is SchemaQuerySelectColumn))
                    {
                        whereSql.Append("[" + col.ParentColumn.GetJoinTableFillAlias() + "].");
                        whereSql.Append("[" + col.Name + "]");
                        whereSql.Append(" LIKE '%" + word.AsLIKE() + "%'");
                        whereSql.Append(" OR ");
                    }
                    whereSql.RemoveLastChar(4);
                    whereSql.AppendLine(") AND");
                }
            }
            if (RuntimeRecordIDFilter != null)
            {
                if (RootColumn.GetJoinView().GetNativeTable() != null)
                    whereSql.AppendLine("[" + RootColumn.GetJoinTableFillAlias() + "].[" + RootColumn.GetJoinView().GetNativeTable().GetPrimaryKeyColumn().Name + "]=" + RuntimeRecordIDFilter.AsSQL() + " AND");
                else
                    whereSql.AppendLine("[" + RootColumn.GetJoinTableFillAlias() + "].[__ID__]=" + RuntimeRecordIDFilter.AsSQL() + " AND");
            }

            if (whereSql.Length > "WHERE\n\n".Length)
                whereSql.RemoveLastChar(5);  // убираем 'AND' с переносом строки
            else
                whereSql.Clear();

            EmitOrderBySql(orderbySql);

            var retSql = commentSql.ToString() + withCTESql.ToString() + selectSql.ToString() + fromSql.ToString() + joinSql.ToString() + whereSql.ToString() + orderbySql.ToString();

            #if DEBUG
            File.WriteAllText(@"c:\$\запрос-" + ID.ToString() + "-" + Name.ToString().TranslateToCorrectFileName() + ".txt", retSql);
            #endif

            return retSql;
        }
Пример #42
0
        public void LoadData()
        {
            CheckParams();
            if (CrudMode == CrudMode.View || CrudMode == CrudMode.Edit)
            {
                var sql = new StringBuilder();
                sql.AppendLine("SELECT ");

                foreach (var col in Table.Columns)
                {
                    //if (col.Name != Table.GetPrimaryKeyColumn().Name)
                        sql.AppendLine("  [" + col.Name + "],");
                }
                sql.RemoveLastChar(3);
                sql.AppendLine();
                sql.AppendLine("FROM " + Table.Get4PartsTableName());
                sql.AppendLine("WHERE [" + Table.GetPrimaryKeyColumn().Name + "]=" + ID.AsSQL());

                using (var db = App.Schema.SqlDB.GetDbManager())
                {
                    var dataTable = db.SetCommand(sql.ToString()).ExecuteDataTable();
                    if (dataTable.Rows.Count == 0)
                        throw new Exception("Запись не найдена в таблице " + Table.Get4PartsTableName() + ", " + Table.GetPrimaryKeyColumn().Name + "='" + ID + "'");

                    foreach (var col in Table.Columns)
                    {
                        if (col.Name != Table.GetPrimaryKeyColumn().Name)
                            Values.Add(col.Name, dataTable.Rows[0][col.Name]);
                    }
                }
            }
            if (OnLoadData != null)
                OnLoadData(this, null);
        }
Пример #43
0
        /// <summary>
        /// 分页SQL调用方法 只允许主键排序
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <param name="tableName">表名称</param>
        /// <param name="pk">主键</param>
        /// <param name="fieldList">字段列表</param>
        /// <param name="where">where条件 and or 开始</param>
        /// <param name="groupBy">分组条件</param>
        /// <param name="orderBy">排序条件</param>
        /// <returns>分页SQL</returns>
        public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "")
        {
            PagerSql      sql    = new PagerSql();
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select ");
            strSql.AppendFormat("count({0}) as total ", pk);
            if (!tableName.IsNullEmpty())
            {
                strSql.AppendFormat("from {0} ", tableName);
            }
            if (!where.IsNullEmpty())
            {
                strSql.AppendFormat("where {0} ", where);
            }
            if (!groupBy.IsNullEmpty())
            {
                strSql.AppendFormat("group by {0} ", groupBy);
            }
            sql.CountSql = strSql.ToString();
            //select * from Student
            //where Id in (
            //select top 10 Id
            //from(select top 3200010 Id from Student order by Id)t
            //order by Id desc)
            //order by Id

            //SELECT * FROM (
            //SELECT TOP 页面容量 * FROM (
            //SELECT TOP 页面容量*当前页码 * FROM
            //表 WHERE 条件 ORDER BY 字段A ASC
            //)AS  TEMPTABLE1 ORDER BY 字段A DESC
            //) AS TEMPTABLE2 ORDER BY 字段A ASC
            StringBuilder orderByExt = new StringBuilder();

            foreach (string order in orderBy.Split(','))
            {
                string order2 = order.Trim();
                if (order2.EndsWith(" desc", true, null))
                {
                    orderByExt.AppendFormat("{0} {1},", order2.Left(order2.Length - 5), "asc");
                }
                else
                {
                    orderByExt.AppendFormat("{0} {1},", order2.EndsWith(" asc", true, null) ? order2.Left(order2.Length - 4) : order2, "desc");
                }
            }
            orderByExt.RemoveLastChar(",");

            strSql.Clear();
            strSql.Append("select ");
            //if (distinct) strSql.Append("distinct ");
            if (pageSize == 1)
            {
                strSql.AppendFormat("top {0} ", pageSize);
                strSql.AppendFormat("{0} ", fieldList);
                if (!tableName.IsNullEmpty())
                {
                    strSql.AppendFormat("from {0} ", tableName);
                }
                if (!where.IsNullEmpty())
                {
                    strSql.AppendFormat("where {0} ", where);
                }
                if (!groupBy.IsNullEmpty())
                {
                    strSql.AppendFormat("group by {0} ", groupBy);
                }
                if (!orderBy.IsNullEmpty())
                {
                    strSql.AppendFormat("order by {0} ", orderBy);
                }
            }
            else
            {
                if (!tableName.IsNullEmpty())
                {
                    strSql.AppendFormat("{1} from (select top {0} {1} from (select top {3} {1} from {2} ", pageSize, fieldList, tableName, pageSize * pageIndex);
                }
                if (!where.IsNullEmpty())
                {
                    strSql.AppendFormat("where {0} ", where);
                }
                if (!groupBy.IsNullEmpty())
                {
                    strSql.AppendFormat("group by {0} ", groupBy);
                }
                if (!orderBy.IsNullEmpty())
                {
                    strSql.AppendFormat("order by {0} ", orderBy);
                }
                strSql.AppendFormat(") as Top1 {0}) as Top2 {1} ", "order by " + orderByExt, "order by " + orderBy);
            }
            sql.DataSql = strSql.ToString();

            return(sql);
        }
Пример #44
0
        public static string GetRolesDisplayText(IList<Guid> rolesList)
        {
            if (rolesList == null || rolesList.Count == 0)
                return "";

            var list = new List<SchemaBaseRole>();
            string errorStr = ""; ;
            foreach (var roleID in rolesList)
            {
                if (SchemaBaseRole.Roles.ContainsKey(roleID) && SchemaBaseRole.Roles[roleID] is SchemaBaseRole)
                    list.Add(SchemaBaseRole.Roles[roleID] as SchemaBaseRole);
                else
                    errorStr += ", ?ошибка";
            }
            var sb = new StringBuilder();
            foreach (var tableRole in from role in list orderby role.Position, role.Name select role)
            {
                sb.Append(tableRole.Name + ", ");
            }
            sb.RemoveLastChar(2);
            sb.Append(errorStr);
            return sb.ToString();
        }
Пример #45
0
        public override void EmitJoinSql(StringBuilder sql, List<string> withCTE, string indent)
        {
            var props = Properties as OborotkaProperties;

              //  props.BegDate = new DateTime(2014, 1, 1);
              //  props.EndDate = new DateTime(2014, 1, 31);

            if (props.Registers.Count == 0)
            {
                throw new Exception("Не указан список регистров для оборотной ведомости.");
                return;
            }
            var cte_sql = new StringBuilder();
            cte_sql.AppendLine("[__obortka_temp__](");

            cte_sql.AppendLine("  [Регистр],");
            var table = App.Schema.GetSampleObject<SchemaTable>(props.Registers[0]);
            foreach (var registrCol in table.Columns)
            {
                if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение))
                    cte_sql.AppendLine("  [" + registrCol.Name + "],");
            }
            foreach (var registrCol in table.Columns)
            {
                if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Мера))
                {
                    //if (props.ВхСальдо)
                    //    cte_sql.AppendLine("  0 AS [" + registrCol.Name + "__ВхСальдо],");
                    if (props.Дебет)
                        cte_sql.AppendLine("  [" + registrCol.Name + "__Дебет],");
                    if (props.Кредит)
                        cte_sql.AppendLine("  [" + registrCol.Name + "__Кредит],");
                    if (props.ИсхСальдо)
                        cte_sql.AppendLine("  [" + registrCol.Name + "__ИсхСальдо],");
                    cte_sql.AppendLine("  [" + registrCol.Name + "__before_today],");
                    cte_sql.AppendLine("  [" + registrCol.Name + "__today],");
                }
            }
            //            cte_sql.AppendLine("__ID__) ");
            cte_sql.RemoveLastChar(3);
            cte_sql.AppendLine(")");
            cte_sql.AppendLine("AS ");
            cte_sql.AppendLine("( ");

            int counter = 0;
            foreach (var registerID in props.Registers)
            {
                table = App.Schema.GetSampleObject<SchemaTable>(registerID);
                // первый select - остаток на сегодня (не EndDate), включает все проводки, что есть у данного регистра
                cte_sql.AppendLine("SELECT");
                counter++;

                cte_sql.AppendLine("  " + table.Name.AsSQL() + " AS [Регистр],");
                foreach (var registrCol in table.Columns)
                {
                    if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение))
                        cte_sql.AppendLine("  [" + registrCol.Name + "] AS [" + registrCol.Name + "],");
                }
                foreach (var registrCol in table.Columns)
                {
                    if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Мера))
                    {
                        //if (props.ВхСальдо)
                        //    cte_sql.AppendLine("  0 AS [" + registrCol.Name + "__ВхСальдо],");
                        if (props.Дебет)
                            cte_sql.AppendLine("  0 AS [" + registrCol.Name + "__Дебет],");
                        if (props.Кредит)
                            cte_sql.AppendLine("  0 AS [" + registrCol.Name + "__Кредит],");
                        if (props.ИсхСальдо)
                            cte_sql.AppendLine("  0 AS [" + registrCol.Name + "__ИсхСальдо],");
                        cte_sql.AppendLine("  0 AS [" + registrCol.Name + "__before_today],");
                        cte_sql.AppendLine("  SUM([" + registrCol.Name + "]) AS [" + registrCol.Name + "__today],");
                    }
                }
                cte_sql.RemoveLastChar(3);
                cte_sql.AppendLine();
                cte_sql.AppendLine("FROM " + table.Get4PartsTableName());
                cte_sql.AppendLine("WHERE [" + table.GetColumnByRole(RoleConst.Регистр_ДбКр).Name + "] IN ('Д','К')");
                cte_sql.AppendLine("GROUP BY ");
                foreach (var registrCol in table.Columns)
                {
                    if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение))
                        cte_sql.AppendLine("  [" + registrCol.Name + "],");
                }
                cte_sql.RemoveLastChar(3);
                cte_sql.AppendLine();

            }

            foreach (var registerID in props.Registers)
            {
                table = App.Schema.GetSampleObject<SchemaTable>(registerID);
                if (counter != 0)
                    cte_sql.AppendLine("UNION ALL");
                else
                    counter++;

                cte_sql.AppendLine("SELECT");

                cte_sql.AppendLine("  " + table.Name.AsSQL() + " AS [Регистр],");

                // измерения
                foreach (var registrCol in table.Columns)
                {
                    if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение))
                        cte_sql.AppendLine("  [" + registrCol.Name + "],");
                }
                foreach (var registrCol in table.Columns)
                {
                    if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Мера))
                    {
                        //if (props.ВхСальдо)
                        //    cte_sql.AppendLine("  0 AS [" + registrCol.Name + "__ВхСальдо],");
                        if (props.Дебет)
                            cte_sql.AppendLine("  CASE WHEN [" + table.GetColumnByRole(RoleConst.Регистр_ДбКр).Name + "]='Д' AND [" + table.GetColumnByRole(RoleConst.Регистр_Дата).Name + "] BETWEEN " + props.BegDate.AsSQL() + " AND " + props.EndDate.AsSQL() + " THEN [" + registrCol.Name + "] ELSE 0 END  AS [" + registrCol.Name + "__Дебет],");
                        if (props.Кредит)
                            cte_sql.AppendLine("  CASE WHEN [" + table.GetColumnByRole(RoleConst.Регистр_ДбКр).Name + "]='К' AND [" + table.GetColumnByRole(RoleConst.Регистр_Дата).Name + "] BETWEEN " + props.BegDate.AsSQL() + " AND " + props.EndDate.AsSQL() + " THEN [" + registrCol.Name + "] ELSE 0 END  AS [" + registrCol.Name + "__Кредит],");
                        if (props.ИсхСальдо)
                            cte_sql.AppendLine("  0 AS [" + registrCol.Name + "__ИсхСальдо],");
                        cte_sql.AppendLine("  CASE WHEN [" + table.GetColumnByRole(RoleConst.Регистр_Дата).Name + "] > " + props.EndDate.AsSQL() + " THEN [" + registrCol.Name + "] ELSE 0 END  AS [" + registrCol.Name + "__before_today],");
                        cte_sql.AppendLine("  0 AS [" + registrCol.Name + "__today],");
                        //                        cte_sql.AppendLine("  " + table.Get4PartsTableName() + ".[" + registrCol.Name + "],");
                    }
                }
                cte_sql.RemoveLastChar(3);
                cte_sql.AppendLine();
                cte_sql.AppendLine("FROM "+table.Get4PartsTableName());
                cte_sql.AppendLine("WHERE [" + table.GetColumnByRole(RoleConst.Регистр_ДбКр).Name + "] IN ('Д','К') AND [" + table.GetColumnByRole(RoleConst.Регистр_Дата).Name + "] >= " + props.BegDate.AsSQL());

                //    cte_sql.AppendLine("WHERE [" + table.GetPrimaryKeyColumn().Name + "]=" + recordID.AsSQL());
            }
            cte_sql.AppendLine("),");

            // группировка
            cte_sql.AppendLine("[$Оборотка](");

            table = App.Schema.GetSampleObject<SchemaTable>(props.Registers[0]);
            cte_sql.AppendLine("  [Регистр],");
            foreach (var registrCol in table.Columns)
            {
                if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение))
                    cte_sql.AppendLine("  [" + registrCol.Name + "],");
            }
            foreach (var registrCol in table.Columns)
            {
                if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Мера))
                {
                    if (props.ВхСальдо)
                        cte_sql.AppendLine("  [" + registrCol.Name + " ВхСальдо],");
                    if (props.Дебет)
                        cte_sql.AppendLine("  [" + registrCol.Name + " Дебет],");
                    if (props.Кредит)
                        cte_sql.AppendLine("  [" + registrCol.Name + " Кредит],");
                    if (props.ИсхСальдо)
                        cte_sql.AppendLine("  [" + registrCol.Name + " ИсхСальдо],");
                }
            }
            cte_sql.AppendLine("  __ID__");
            cte_sql.AppendLine(") ");
            cte_sql.AppendLine("AS");
            cte_sql.AppendLine("(");
            cte_sql.AppendLine("SELECT");
            cte_sql.AppendLine("  [Регистр],");

            foreach (var registrCol in table.Columns)
            {
                if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение))
                    cte_sql.AppendLine("  [" + registrCol.Name + "],");
            }
            foreach (var registrCol in table.Columns)
            {
                if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Мера))
                {
                    if (props.ВхСальдо)
                        cte_sql.AppendLine("  SUM([" + registrCol.Name + "__today]-[" + registrCol.Name + "__before_today]-[" + registrCol.Name + "__Дебет]-[" + registrCol.Name + "__Кредит]) AS [" + registrCol.Name + ": ВхСальдо],");
                    if (props.Дебет)
                        cte_sql.AppendLine("  SUM([" + registrCol.Name + "__Дебет]) AS [" + registrCol.Name + ": Дебет],");
                    if (props.Кредит)
                        cte_sql.AppendLine(" -SUM([" + registrCol.Name + "__Кредит]) AS [" + registrCol.Name + ": Кредит],");
                    if (props.ИсхСальдо)
                        cte_sql.AppendLine("  SUM([" + registrCol.Name + "__today]-[" + registrCol.Name + "__before_today]) AS [" + registrCol.Name + ": ИсхСальдо],");
                }
            }
            cte_sql.AppendLine("  NEWID() AS __ID__");
            //cte_sql.RemoveLastChar(3);
            //cte_sql.AppendLine();
            cte_sql.AppendLine("FROM [__obortka_temp__]");
            cte_sql.AppendLine("GROUP BY");

            cte_sql.AppendLine("  [Регистр],");
            foreach (var registrCol in table.Columns)
            {
                if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение))
                    cte_sql.AppendLine("  [" + registrCol.Name + "],");
            }
            cte_sql.RemoveLastChar(3);
            cte_sql.AppendLine();
            cte_sql.AppendLine("HAVING");
            foreach (var registrCol in table.Columns)
            {
                if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Мера))
                {
                    if (props.ВхСальдо)
                        cte_sql.AppendLine("  SUM([" + registrCol.Name + "__today]-[" + registrCol.Name + "__before_today]-[" + registrCol.Name + "__Дебет]+[" + registrCol.Name + "__Кредит])<>0 OR");
                    if (props.Дебет)
                        cte_sql.AppendLine("  SUM([" + registrCol.Name + "__Дебет])<>0 OR");
                    if (props.Кредит)
                        cte_sql.AppendLine("  SUM([" + registrCol.Name + "__Кредит])<>0 OR");
                    if (props.ИсхСальдо)
                        cte_sql.AppendLine("  SUM([" + registrCol.Name + "__today]-[" + registrCol.Name + "__before_today])<>0 OR");
                }
            }

            cte_sql.RemoveLastChar(5);

            cte_sql.AppendLine("),");

            withCTE.Add(cte_sql.ToString());

            sql.AppendLine("FROM [$Оборотка]");
        }