Gsmarket/Sources/GS-MarketMG-2018/GSMarketSys/Controllers/GetData.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Mvc;
using SysBaseLibs;
using SysDataLibs;
using SysDataLibs.TableClass;

namespace GSMarketSys.Controllers
{
	//获取数据
	public class GetData
	{
		public static GetData Instance { get; } = new GetData();

		#region 为dg_Table（EasyUi）获取数据

		/// <summary>
		/// 根据SQL查询数据（dg_Table）
		/// </summary>
		/// <param name="pcSql"></param>
		/// <param name="dbConn"></param>
		/// <returns></returns>
		public string GetResult(string pcSql, DBConnSql dbConn)
		{
			rsQuery loQuery = dbConn.OpenQuery(pcSql);
			StringBuilder sb = new StringBuilder();
			sb.Append("{\"total\":" + loQuery.RecCount + ",\"rows\":");
			if (loQuery.IsOpened && loQuery.RecCount > 0)
				sb.Append(loQuery.CurrentTable.ToJson("yyyy-MM-dd"));
			else
				sb.Append("[]");
			sb.Append("}");

			return sb.ToString();
		}
	    /// <summary>
	    /// 根据SQL查询数据（dg_Table）
	    /// </summary>
	    /// <param name="pcSql"></param>
	    /// <param name="dbConn"></param>
	    /// <returns></returns>
	    public string GetJsonResult(string pcSql, DBConnSql dbConn)
	    {
	        rsQuery loQuery = dbConn.OpenQuery(pcSql);
	        StringBuilder sb = new StringBuilder();
	       
	        if (loQuery.IsOpened && loQuery.RecCount > 0)
	            sb.Append(loQuery.CurrentTable.ToJson("yyyy-MM-dd"));
	        else
	            sb.Append("[]");

	        return sb.ToString();
	    }
        /// <summary>
        /// 真实分页查询（dg_Table）
        /// </summary>
        /// <param name="pcTableName"></param>
        /// <param name="pcSelectFieldNames"></param>
        /// <param name="pcStrWhere"></param>
        /// <param name="pcOrderBy"></param>
        /// <param name="piPageIndex"></param>
        /// <param name="piPageSize"></param>
        /// <param name="dbConn"></param>
        /// <returns></returns>
        public string GetResult(string pcTableName, string pcSelectFieldNames, string pcStrWhere, string pcOrderBy, int piPageIndex, int piPageSize, DBConnSql dbConn)
		{
			int piRowCount, piPageCount;
			DataSet loDataset = dbConn.GetPageRecords_RowNum(pcTableName, pcSelectFieldNames, pcStrWhere, pcOrderBy, piPageIndex, piPageSize, out piRowCount, out piPageCount);
			DataTable dt = loDataset.Tables[0];
			StringBuilder sb = new StringBuilder();
			sb.Append("{\"total\":" + piRowCount + ",\"rows\":");
			if (dt != null && dt.Rows.Count > 0)
				sb.Append(dt.ToJson("yyyy-MM-dd"));
			else
				sb.Append("[]");
			sb.Append("}");

			return sb.ToString();
		}

		/// <summary>
		/// 根据SQL查询数据,翻译IsLock（dg_Table）
		/// </summary>
		/// <param name="pcSql"></param>
		/// <param name="dbConn"></param>
		/// <returns></returns>
		public string GetResultLockTran(string pcSql, DBConnSql dbConn)
		{
			rsQuery loQuery = dbConn.OpenQuery(pcSql);
			StringBuilder sb = new StringBuilder();
			sb.Append("{\"total\":" + loQuery.RecCount + ",\"rows\":");
			if (loQuery.IsOpened && loQuery.RecCount > 0)
			{
				DataTable dt = loQuery.CurrentTable;
				dt.Columns.Add("LockState", typeof(string));
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					dt.Rows[i]["LockState"] = dt.Rows[i]["IsLock"].ToString() == "0" ? "在用" : "删除";
				}
				sb.Append(loQuery.CurrentTable.ToJson("yyyy-MM-dd"));
			}
			else
				sb.Append("[]");
			sb.Append("}");

			return sb.ToString();
		}

		/// <summary>
		/// 真实分页查询,翻译IsLock（dg_Table）
		/// </summary>
		/// <param name="pcTableName"></param>
		/// <param name="pcSelectFieldNames"></param>
		/// <param name="pcStrWhere"></param>
		/// <param name="pcOrderBy"></param>
		/// <param name="piPageIndex"></param>
		/// <param name="piPageSize"></param>
		/// <param name="dbConn"></param>
		/// <returns></returns>
		public string GetResultLockTran(string pcTableName, string pcSelectFieldNames, string pcStrWhere, string pcOrderBy, int piPageIndex, int piPageSize, DBConnSql dbConn)
		{
			int piRowCount, piPageCount;
			DataSet loDataset = dbConn.GetPageRecords_RowNum(pcTableName, pcSelectFieldNames, pcStrWhere, pcOrderBy, piPageIndex, piPageSize, out piRowCount, out piPageCount);
			DataTable dt = loDataset.Tables[0];
			StringBuilder sb = new StringBuilder();
			sb.Append("{\"total\":" + piRowCount + ",\"rows\":");
			if (dt != null && dt.Rows.Count > 0)
			{
				dt.Columns.Add("LockState", typeof(string));
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					dt.Rows[i]["LockState"] = dt.Rows[i]["IsLock"].ToString() == "0" ? "在用" : "删除";
				}
				sb.Append(dt.ToJson("yyyy-MM-dd"));
			}
			else
				sb.Append("[]");
			sb.Append("}");

			return sb.ToString();
		}

		/// <summary>
		/// SQL语句查询经过系统字典翻译
		/// </summary>
		/// <param name="tableName"></param>
		/// <param name="colName"></param>
		/// <param name="dbConn"></param>
		/// <param name="pcSql"></param>
		/// <returns></returns>
		public string GetResultSysStatesTran(string tableName, string colName, string pcSql, DBConnSql dbConn)
		{
			string lcRetval = GetResult(pcSql, dbConn);
			rsQuery loQuery = dbConn.OpenQuery(
				"SELECT CodeValue,DisplayValue FROM Sys_Status WHERE TableName='" + tableName + "' AND ColName='" + colName +
				"'");
			if (loQuery.IsOpened && loQuery.RecCount > 0)
			{
				loQuery.MoveFirst();
				for (int i = 0; i < loQuery.RecCount; i++)
				{
					string codevalue = loQuery.GetString("CodeValue");
					string code = '"' + colName + '"' + ':' + '"' + codevalue + '"';
					string displayValue = loQuery.GetString("DisplayValue");
					string display = '"' + colName + '"' + ':' + '"' + displayValue + '"';
					if (lcRetval.Contains(code))
						lcRetval = Regex.Replace(lcRetval, code, display);
					loQuery.MoveNext();
				}
			}
			return lcRetval;

		}

		/// <summary>
		/// 真实分页SQL语句查询经过系统字典翻译
		/// </summary>
		/// <param name="pcTableName"></param>
		/// <param name="pcSelectFieldNames"></param>
		/// <param name="pcStrWhere"></param>
		/// <param name="pcOrderBy"></param>
		/// <param name="piPageIndex"></param>
		/// <param name="piPageSize"></param>
		/// <param name="tableName"></param>
		/// <param name="colName"></param>
		/// <param name="dbConn"></param>
		/// <param name="isString">需要翻译的值是否为字符串（false为int类型），默认true，</param>
		/// <returns></returns>
		public string GetResultSysStatesTran(string pcTableName, string pcSelectFieldNames, string pcStrWhere, string pcOrderBy, int piPageIndex, int piPageSize, string tableName, string colName, DBConnSql dbConn,bool isString=true)
		{
			string lcRetval = GetResult(pcTableName,pcSelectFieldNames,pcStrWhere,pcOrderBy,piPageIndex,piPageSize,dbConn);
			rsQuery loQuery = dbConn.OpenQuery(
				"SELECT CodeValue,DisplayValue FROM Sys_Status WHERE TableName='" + tableName + "' AND ColName='" + colName +
				"'");
			if (loQuery.IsOpened && loQuery.RecCount > 0)
			{
				loQuery.MoveFirst();
				for (int i = 0; i < loQuery.RecCount; i++)
				{
					string codevalue = loQuery.GetString("CodeValue");
					string code = '"' + colName + '"' + ':' + (isString?'"'+ codevalue + '"':codevalue)  ;
					string displayValue = loQuery.GetString("DisplayValue");
					string display = '"' + colName + '"' + ':' + '"' + displayValue + '"';
					if (lcRetval.Contains(code)) 
						 lcRetval = Regex.Replace(lcRetval, code, display);
					loQuery.MoveNext();
				}
			}
			return lcRetval;

		}

		/// <summary>
		/// 查询数据翻译（dg_Table）
		/// </summary>
		/// <param name="pcSql">SQL语句</param>
		/// <param name="oldColNames">原列名（多列用"|"分隔，并保证下面类似字段"|"分隔数量相同）</param>
		/// <param name="newColNames">新增列名</param>
		/// <param name="tableNames">要去查询翻译的表名</param>
		/// <param name="colNames">要去查询翻译的列名</param>
		/// <param name="dbConn">数据连接</param>
		/// <param name="whereSqls">查询翻译条件,没有查询条件的填""（可不填，如填需要以"|"分隔""补全【例：""|"1=1"|""】）</param>
		/// <returns></returns>
		public string GetResultTran(string pcSql, string oldColNames, string newColNames, string tableNames, string colNames, DBConnSql dbConn, string whereSqls = "")
		{
			string[] oldColNameArr = oldColNames.Split('|');
			string[] newColNameArr = newColNames.Split('|');
			string[] tableNameArr = tableNames.Split('|');
			string[] colNameArr = colNames.Split('|');
			string[] whereSqlArr = new string[newColNameArr.Length];
			if (string.IsNullOrEmpty(whereSqls))
			{
				for (int i = 0; i < newColNameArr.Length; i++)
				{
					whereSqlArr[i] = "";
				}
			}
			else
				whereSqlArr = whereSqls.Split('|');

			if (newColNameArr.Length != oldColNameArr.Length && newColNameArr.Length != tableNameArr.Length && newColNameArr.Length != colNameArr.Length)
				return "";
			rsQuery loQuery = dbConn.OpenQuery(pcSql);
			StringBuilder sb = new StringBuilder();
			List<string[]> colList = new List<string[]>();
			for (int i = 0; i < newColNameArr.Length; i++)
			{
				colList.Add(new[] { oldColNameArr[i], newColNameArr[i], tableNameArr[i], colNameArr[i], whereSqlArr[i] == "" ? "1=1" : whereSqlArr[i] });
			}
			sb.Append("{\"total\":" + loQuery.RecCount + ",\"rows\":");
			if (loQuery.IsOpened && loQuery.RecCount > 0)
			{
				DataTable dt = loQuery.CurrentTable;
				foreach (string col in newColNameArr)
				{
					dt.Columns.Add(col, typeof(string));
				}

				for (int i = 0; i < dt.Rows.Count; i++)
				{
					foreach (string[] col in colList)
					{
						var r = dt.Rows[i][col[0]];
						if (!(r is int))
						{
							r = "'" + r + "'";
						}
						string lcSql = "SELECT " + col[3] + " FROM " + col[2] + " WHERE " + col[0] + "=" + r + " AND " + col[4];
						rsQuery loQueryTm = dbConn.OpenQuery(lcSql);
						loQueryTm.MoveFirst();
						dt.Rows[i][col[1]] = loQueryTm.CurrentTable.Rows[0][col[3]];
					}
					//dt.Rows[i]["LockState"] = dt.Rows[i]["IsLock"].ToString() == "0" ? "在用" : "删除";
				}
				sb.Append(loQuery.CurrentTable.ToJson("yyyy-MM-dd"));
			}
			else
				sb.Append("[]");
			sb.Append("}");
			return sb.ToString();
		}

		/// <summary>
		/// 真实分页查询数据翻译（dg_Table）
		/// </summary>
		/// <param name="tableName">查询表面</param>
		/// <param name="selectFieldNames">查询字段</param>
		/// <param name="strWhere">查询WHERE条件</param>
		/// <param name="orderBy">查询排序条件</param>
		/// <param name="piPageSize">每页显示条数</param>
		/// <param name="piPageIndex">页码</param>
		/// <param name="oldColNames">原列名（多列用"|"分隔，并保证下面类似字段"|"分隔数量相同）</param>
		/// <param name="newColNames">新增列名</param>
		/// <param name="tableNames">要去查询翻译的表名</param>
		/// <param name="colNames">要去查询翻译的列名</param>
		/// <param name="dbConn">数据连接</param>
		/// <param name="whereSqls">查询翻译条件,没有查询条件的填""（可不填，如填需要以"|"分隔""补全【例：""|"1=1"|""】）</param>
		/// <returns></returns>
		public string GetResultTran(string tableName, string selectFieldNames, string strWhere, string orderBy, int piPageIndex, int piPageSize, string oldColNames, string newColNames, string tableNames, string colNames,DBConnSql dbConn, string whereSqls="")
		{
			string[] oldColNameArr = oldColNames.Split('|');
			string[] newColNameArr = newColNames.Split('|');
			string[] tableNameArr = tableNames.Split('|');
			string[] colNameArr = colNames.Split('|');
			string[] whereSqlArr = new string[newColNameArr.Length];
			if (string.IsNullOrEmpty(whereSqls))
			{
				for (int i = 0; i < newColNameArr.Length; i++)
				{
					whereSqlArr[i] = "";
				}
			}
			else
				whereSqlArr = whereSqls.Split('|');

			if (newColNameArr.Length != oldColNameArr.Length&& newColNameArr.Length != tableNameArr.Length && newColNameArr.Length != colNameArr.Length)
				return "";
			int piRowCount, piPageCount;
			DataSet loDataset = dbConn.GetPageRecords_RowNum(tableName, selectFieldNames, strWhere, orderBy, piPageIndex, piPageSize, out piRowCount, out piPageCount);
			DataTable dt = loDataset.Tables[0];
			StringBuilder sb = new StringBuilder();
			List<string[]> colList = new List<string[]>();
			for (int i = 0; i < newColNameArr.Length; i++)
			{
				colList.Add(new[] { oldColNameArr[i], newColNameArr[i],  tableNameArr[i], colNameArr[i], whereSqlArr[i]==""?"1=1": whereSqlArr[i] });
			}
			sb.Append("{\"total\":" + piRowCount + ",\"rows\":");
			if (dt != null && dt.Rows.Count > 0)
			{
				
				foreach (string col in newColNameArr)
				{
					dt.Columns.Add(col, typeof(string));
				}
				
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					foreach (string[] col in colList)
					{
						var r = dt.Rows[i][col[0]];
						if (!(r is int))
						{
							r = "'" + r + "'";
						}
						string lcSql = "SELECT " + col[3] + " FROM " + col[2] + " WHERE " + col[0] + "=" + r + " AND " + col[4];
						rsQuery loQueryTm = dbConn.OpenQuery(lcSql);
						loQueryTm.MoveFirst();
						dt.Rows[i][col[1]] = loQueryTm.CurrentTable.Rows[0][col[3]];
					}
					//dt.Rows[i]["LockState"] = dt.Rows[i]["IsLock"].ToString() == "0" ? "在用" : "删除";
				}
				sb.Append(dt.ToJson("yyyy-MM-dd"));
			}
			else
				sb.Append("[]");
			sb.Append("}");
			return sb.ToString();
		}


		/// <summary>
		/// 已查询的数据通过系统字典里翻译
		/// </summary>
		/// <param name="tableName">表名</param>
		/// <param name="colName">列名</param>
		/// <param name="pcStr">需要翻译的数据</param>
		/// <param name="dbConn"></param>
		/// <returns></returns>
		public string TranFromSysStates(string tableName, string colName, string pcStr, DBConnSql dbConn)
		{
			rsQuery loQuery = dbConn.OpenQuery(
				"SELECT CodeValue,DisplayValue FROM Sys_Status WHERE TableName='" + tableName + "' AND ColName='" + colName +
				"'");
			string lcRetval = pcStr;
			if (loQuery.IsOpened && loQuery.RecCount > 0)
			{
				loQuery.MoveFirst();
				for (int i = 0; i < loQuery.RecCount; i++)
				{
					string codevalue = loQuery.GetString("CodeValue");
					string code = '"' + colName + '"' + ':' + '"' + codevalue + '"';
					string displayValue = loQuery.GetString("DisplayValue");
					string display = '"' + colName + '"' + ':' + '"' + displayValue + '"';
					if (lcRetval.Contains(code))
						lcRetval = Regex.Replace(pcStr, code, display);
					loQuery.MoveNext();
				}
			}
			return lcRetval;

		}

		#endregion

		#region 拼装【SELECT】 里的【option】

		/// <summary>
		/// 拼组【SELECT】 里的【option】
		/// </summary>
		/// <param name="selValueColName"></param>
		/// <param name="selNameColName"></param>
		/// <param name="tabelName"></param>
		/// <param name="dbConn"></param>
		/// <param name="isLock"></param>
		/// <returns></returns>
		public string GetSelStr(string selValueColName, string selNameColName, string tabelName, DBConnSql dbConn, bool isLock = true)
		{
			string lcSql = "SELECT " + selValueColName + "," + selNameColName + " FROM " + tabelName +
			               " WHERE 1 = 1";
			if (isLock)
				lcSql += " AND IsLock = 0";
			rsQuery loQuery = dbConn.OpenQuery(lcSql);
			StringBuilder sb = new StringBuilder();
			if (loQuery.IsOpened && loQuery.RecCount > 0)
			{
				DataTable dt = loQuery.CurrentTable;
				DataRowCollection drc = dt.Rows;
				for (var i = 0; i < drc.Count; i++)
				{
					sb.Append("<option value=\"" + drc[i][selValueColName] + "\" >" + drc[i][selNameColName] + " </option>");
				}
			}

			return sb.ToString();
		}/// <summary>
	     
	    /// 拼组 Region【SELECT】 里的【option】
        /// </summary>
        /// <param name="selValueColName"></param>
        /// <param name="selNameColName"></param>
        /// <param name="tabelName"></param>
        /// <param name="dbConn"></param>
        /// <param name="isLock"></param>
        /// <returns></returns>
        public string GetRegionSelStr(string selValueColName, string selNameColName, string tabelName, DBConnSql dbConn, bool isLock = true)
		{
			string lcSql = "SELECT RegionID  ,Names ,Depth FROM Regions  WHERE RegionID!='861111    ' AND IsLock = 0 ORDER BY Depth";
			//if (isLock)
			//	lcSql += " AND IsLock = 0";
			rsQuery loQuery = dbConn.OpenQuery(lcSql);
			StringBuilder sb = new StringBuilder();
			if (loQuery.IsOpened && loQuery.RecCount > 0)
			{
				DataTable dt = loQuery.CurrentTable;
				DataRowCollection drc = dt.Rows;
				for (var i = 0; i < drc.Count; i++)
				{
				    var prev = "";
				    for (int j = 0; j < (int)drc[i]["Depth"]; j++)
				    {
				        prev += "--";
				    }
					sb.Append("<option value=\"" + drc[i]["RegionID"] + "\" >"+ prev + drc[i]["Names"] + " </option>");
				}
			}

			return sb.ToString();
		}

		/// <summary>
		/// 带 WHERE 条件拼组【SELECT】 里的【option】
		/// </summary>
		/// <param name="selValueColName"></param>
		/// <param name="selNameColName"></param>
		/// <param name="tabelName"></param>
		/// <param name="whereStr"></param>
		/// <param name="dbConn"></param>
		/// <returns></returns>
		public string GetSelStr(string selValueColName, string selNameColName, string tabelName,
			string whereStr, DBConnSql dbConn)
		{
			string lcSql = "SELECT " + selValueColName + "," + selNameColName + " FROM " + tabelName;
			if (!string.IsNullOrEmpty(whereStr))
				lcSql += " WHERE " + whereStr;
			//if (isLock)
			//	lcSql += " AND IsLock = 0";
			rsQuery loQuery = dbConn.OpenQuery(lcSql);
			StringBuilder sb = new StringBuilder();
			if (loQuery.IsOpened && loQuery.RecCount > 0)
			{
				DataTable dt = loQuery.CurrentTable;
				DataRowCollection drc = dt.Rows;
				for (var i = 0; i < drc.Count; i++)
				{
					sb.Append("<option value=\"" + drc[i][selValueColName] + "\" >" + drc[i][selNameColName] + " </option>");
				}
			}

			return sb.ToString();
		}
        /// <summary>
		/// 带 WHERE 条件拼组【SELECT】 里的【option】
		/// </summary>
		/// <param name="selValueColName"></param>
		/// <param name="selNameColName"></param>
		/// <param name="tabelName"></param>
		/// <param name="whereStr"></param>
		/// <param name="dbConn"></param>
		/// <returns></returns>
		public string GetSelStrLi(string selValueColName, string selNameColName, string tabelName,
            string whereStr, DBConnSql dbConn)
        {
            string lcSql = "SELECT " + selValueColName + "," + selNameColName + " FROM " + tabelName;
            if (!string.IsNullOrEmpty(whereStr))
                lcSql += " WHERE " + whereStr;
            //if (isLock)
            //	lcSql += " AND IsLock = 0";
            rsQuery loQuery = dbConn.OpenQuery(lcSql);
            StringBuilder sb = new StringBuilder();
            if (loQuery.IsOpened && loQuery.RecCount > 0)
            {
                DataTable dt = loQuery.CurrentTable;
                DataRowCollection drc = dt.Rows;
                for (var i = 0; i < drc.Count; i++)
                {
                    sb.Append("<li value=\"" + drc[i][selValueColName] + "\" >" + drc[i][selNameColName] + " </li>");
                }
            }

            return sb.ToString();
        }

        /// <summary>
        /// 带 WHERE 条件拼组【SELECT】 里的【option】
        /// </summary>
        /// <param name="selValueColName"></param>
        /// <param name="selNameColName"></param>
        /// <param name="tabelName"></param>
        /// <param name="whereStr"></param>
        /// <param name="dbConn"></param>
        /// <returns></returns>
        public string GetSelStrDistinct(string selValueColName, string selNameColName, string tabelName,
			string whereStr, DBConnSql dbConn)
		{
			string lcSql = "SELECT distinct " + selValueColName + "," + selNameColName + " FROM " + tabelName;
			if (!string.IsNullOrEmpty(whereStr))
				lcSql += " WHERE " + whereStr;
			//if (isLock)
			//	lcSql += " AND IsLock = 0";
			rsQuery loQuery = dbConn.OpenQuery(lcSql);
			StringBuilder sb = new StringBuilder();
			if (loQuery.IsOpened && loQuery.RecCount > 0)
			{
				DataTable dt = loQuery.CurrentTable;
				DataRowCollection drc = dt.Rows;
				for (var i = 0; i < drc.Count; i++)
				{
					sb.Append("<option value=\"" + drc[i][selValueColName] + "\" >" + drc[i][selNameColName] + " </option>");
				}
			}

			return sb.ToString();
		}

		/// <summary>
		/// 从系统字典中翻译拼组【SELECT】 里的【option】
		/// </summary>
		/// <param name="colName"></param>
		/// <param name="tabelName"></param>
		/// <param name="dbConn"></param>
		/// <returns></returns>
		public string GetSelStr(string colName, string tabelName, DBConnSql dbConn)
		{
			string lcSql = "SELECT CodeValue,DisplayValue FROM Sys_Status WHERE TableName='" + tabelName + "' AND  ColName='" +
						   colName + "'";
			rsQuery loQuery = dbConn.OpenQuery(lcSql);
			StringBuilder sb = new StringBuilder();
			if (loQuery.IsOpened && loQuery.RecCount > 0)
			{
				DataTable dt = loQuery.CurrentTable;
				DataRowCollection drc = dt.Rows;
				for (var i = 0; i < drc.Count; i++)
				{
					sb.Append("<option value=\"" + drc[i]["CodeValue"] + "\" >" + drc[i]["DisplayValue"] + " </option>");
				}
			}

			return sb.ToString();
		}

        private string TranRegionName(int poDepth, string poName)
        {
            string exReg = "";
            for (int i = 0; i < poDepth; i++)
            {
                exReg += "---";
            }
            exReg += poName;
            return exReg;
        }
        public string GetRegionsTreeList(DBConnSql poDbConn,bool isContainRoot = true)
        {
            string lcSql = " select * from Regions where IsLock='0'" + (isContainRoot?"": " and RegionID<>'861111'") +"   ORDER BY Sort ";
            rsQuery loQuery = poDbConn.OpenQuery(lcSql);//
            StringBuilder sb = new StringBuilder();
       
            if (loQuery.IsOpened && loQuery.RecCount > 0)
            {
                DataTable dt = loQuery.CurrentTable;
                DataRowCollection drc = dt.Rows;
                for (var i = 0; i < drc.Count; i++)
                {
                    sb.Append("<option value=\"" + drc[i]["RegionID"] + "\" >" + TranRegionName(Utils.ValI(UtilStr.StrFromObj(drc[i]["Depth"])), loQuery.GetString("Names")) + " </option>");
                    loQuery.MoveNext();
                }
            }
            else
                sb.Append("");
            return sb.ToString();
        }


        #endregion

		public List<SelectItem> GetSelectList(string selValueColName, string selNameColName, string tabelName,
			string whereStr, DBConnSql dbConn)
		{
			List<SelectItem> selectList =new List<SelectItem>();
			string lcSql = "SELECT " + selValueColName + "," + selNameColName + " FROM " + tabelName;
			if (!string.IsNullOrEmpty(whereStr))
				lcSql += " WHERE " + whereStr;
			//if (isLock)
			//	lcSql += " AND IsLock = 0";
			rsQuery loQuery = dbConn.OpenQuery(lcSql);
			StringBuilder sb = new StringBuilder();
			if (loQuery.IsOpened && loQuery.RecCount > 0)
			{
				DataTable dt = loQuery.CurrentTable;
				DataRowCollection drc = dt.Rows;
				
				for (var i = 0; i < drc.Count; i++)
				{
					selectList.Add(new SelectItem()
					{
						id = drc[i][selNameColName] + "",
						text = drc[i][selValueColName] + ""
					});
				}
			}
			return selectList;
		}
		

        /// <summary>
        /// 空字符串转为 int 类型时设置为 0
        /// </summary>
        /// <param name="pcStr"></param>
        /// <returns></returns>
        public string SetIntDefault(string pcStr)
		{
			return string.IsNullOrEmpty(pcStr) ? "0" : pcStr;
		}
		
		/// <summary>
		/// 传入表名及表的ID名 返回最后一个ID值
		/// </summary>
		/// <param name="idName"></param>
		/// <param name="tbName"></param>
		/// <param name="dbConn"></param>
		/// <returns></returns>
		public string GetLastRecId(string idName, string tbName, DBConnSql dbConn)
		{
			string lcRetval = "", lsql = "SELECT " + idName + " FROM " + tbName;
			rsQuery loQuery = dbConn.OpenQuery(lsql);
			if (loQuery.IsOpened && loQuery.RecCount > 0)
				lcRetval = loQuery.RecCount.ToString();
			return lcRetval;
		}

        /// <summary>
        /// 检测数据库是否存在一条记录信息
        /// </summary>
        /// <param name="pcColumn"></param>
        /// <param name="pcColumnValue"></param>
        /// <param name="pcTableName"></param>
        /// <param name="dbConn"></param>
        /// <returns></returns>
	    public bool isCheckExistObj(string pcColumn, string pcColumnValue, string pcTableName, DBConnSql dbConn)
	    {
	        bool lbRetval = false;
	        string lcSql = "SELECT top 1 * FROM " + pcTableName + " where " + pcColumn + " = '" + pcColumnValue + "'";
	        rsQuery loQuery = dbConn.OpenQuery(lcSql);
	        if (loQuery.IsOpened && loQuery.RecCount > 0)
	        {
	            lbRetval = true;
	        }
	        return lbRetval;
	    }

        /// <summary>
        /// 附件操作
        /// </summary>
        /// <param name="type">操作类型</param>
        /// <param name="pcAttacthFileInfo">附件实体</param>
        /// <param name="userSessionInfo">UserSession</param>
        /// <param name="msg">回传信息</param>
        /// <returns></returns>
        public bool AttachFileUpdate(string type, Sys_AttachFiles_info pcAttacthFileInfo,UserSession userSessionInfo, ref string msg)
		{
			if (msg == null) throw new ArgumentNullException(nameof(msg));
			msg = "操作失败";
			Sys_AttachFiles_info t;
			string oldPath;
			bool ltRetval = false;
			HttpFileCollection attachFile = HttpContext.Current.Request.Files;

			switch (type)
			{
				case "Add":
					pcAttacthFileInfo.TimeCreated = DateTime.Now.ToShortDateString();
					if (pcAttacthFileInfo.UpdateFile(attachFile, userSessionInfo))
					{
						ltRetval = true;
						msg = "附件上传成功";
					} 
					break;
				case "Edit":
					t = new Sys_AttachFiles_info(pcAttacthFileInfo.TableId, pcAttacthFileInfo.ColumnId,
						pcAttacthFileInfo.SourceKey, userSessionInfo.DBConn);
					if (pcAttacthFileInfo.UpdateFile(attachFile, userSessionInfo, false))
					{
						try
						{
							oldPath = HttpContext.Current.Request.PhysicalApplicationPath;
							oldPath += t.FilePath + "/" + t.FileName;
							if (!string.IsNullOrEmpty(oldPath))
								System.IO.File.Delete(oldPath);
						}
						catch
						{
							// ignored
						}
						ltRetval = true;
						msg = "附件修改成功";
					}
					break;
				case "Del":
					t = new Sys_AttachFiles_info(pcAttacthFileInfo.TableId, pcAttacthFileInfo.ColumnId,
						pcAttacthFileInfo.SourceKey, userSessionInfo.DBConn);
					oldPath = HttpContext.Current.Request.PhysicalApplicationPath;
					oldPath += t.FilePath + "/" + t.FileName;
					if (userSessionInfo.DBConn.ExcuteSqlTran(pcAttacthFileInfo.DeleteSql()))
					{
						try
						{
							if (!string.IsNullOrEmpty(oldPath))
								System.IO.File.Delete(oldPath);
						}
						catch
						{
							// ignored
						}
						ltRetval = true;
						msg = "附件删除成功";
					}
					else
						msg = "附件删除失败！";
					break;
			}

			return ltRetval;
		}
		/// <summary>
		/// 附件操作
		/// </summary>
		/// <param name="type">操作类型</param>
		/// <param name="pcAttacthFileInfo">附件实体</param>
		/// <param name="userSessionInfo">UserSession</param>
		/// <param name="msg">回传信息</param>
	
		/// <returns></returns>
		public bool AttachFileUpdate2(string type, string base64Str,Sys_AttachFiles_info pcAttacthFileInfo, UserSession userSessionInfo, ref string msg)
		{
			if (msg == null) throw new ArgumentNullException(nameof(msg));
			msg = "操作失败";
			Sys_AttachFiles_info t;
			string oldPath;
			bool ltRetval = false;
			HttpFileCollection attachFile = HttpContext.Current.Request.Files;

			switch (type)
			{
				case "Add":
					pcAttacthFileInfo.TimeCreated = DateTime.Now.ToShortDateString();
					if (pcAttacthFileInfo.UpdateFileBase64(base64Str, userSessionInfo))
					{
						ltRetval = true;
						msg = "附件上传成功";
					}
					break;
				case "Edit":
					t = new Sys_AttachFiles_info(pcAttacthFileInfo.TableId, pcAttacthFileInfo.ColumnId,
						pcAttacthFileInfo.SourceKey, userSessionInfo.DBConn);
					if (pcAttacthFileInfo.UpdateFileBase64(base64Str, userSessionInfo, false))
					{
						try
						{
							oldPath = HttpContext.Current.Request.PhysicalApplicationPath;
							oldPath += t.FilePath + "/" + t.FileName;
							if (!string.IsNullOrEmpty(oldPath))
								System.IO.File.Delete(oldPath);
						}
						catch
						{
							// ignored
						}
						ltRetval = true;
						msg = "附件修改成功";
					}
					break;
				case "Del":
					t = new Sys_AttachFiles_info(pcAttacthFileInfo.TableId, pcAttacthFileInfo.ColumnId,
						pcAttacthFileInfo.SourceKey, userSessionInfo.DBConn);
					oldPath = HttpContext.Current.Request.PhysicalApplicationPath;
					oldPath += t.FilePath + "/" + t.FileName;
					if (userSessionInfo.DBConn.ExcuteSqlTran(pcAttacthFileInfo.DeleteSql()))
					{
						try
						{
							if (!string.IsNullOrEmpty(oldPath))
								System.IO.File.Delete(oldPath);
						}
						catch
						{
							// ignored
						}
						ltRetval = true;
						msg = "附件删除成功";
					}
					else
						msg = "附件删除失败！";
					break;
			}

			return ltRetval;
		}
		/// <summary>
		/// 返回
		/// </summary>
		/// <param name="pcErrorCode"></param>
		/// <returns></returns>
		public string GetBackInfo(string pcErrorCode)
        {
            return SysRebackDisplayInfo.Instance.GetDisplayValue(pcErrorCode);
        }
        /// <summary>
        /// 根据主键查询表对象
        /// </summary>
        /// <param name="pcId">主键值</param>
        /// <param name="pcTableName">表名</param>
        /// <param name="pcColumnName">字段名</param>
        /// <returns></returns>
        public string GetResultById(string pcId, string pcTableName, string pcColumnName)
        {
            string lcResult = "";
            DBConnSql loDbConn = new DBConnSql();
            loDbConn.Open();
            try
            {
                string lcSql = " SELECT * FROM " + pcTableName + " WHERE " + pcColumnName + " = '" + pcId + "'";
                rsQuery loQuery = loDbConn.OpenQuery(lcSql);
                StringBuilder sb = new StringBuilder();
                if (loQuery.IsOpened && loQuery.RecCount > 0)
                    sb.Append(loQuery.CurrentTable.ToJson("yyyy-MM-dd"));
                else
                    sb.Append("[]");
                lcResult = sb.ToString();
            }
            catch
            {
	            // ignored
            }
            finally
            {
	            loDbConn.Close();
	            // ReSharper disable once RedundantAssignment
	            loDbConn = null;
            }
            return lcResult;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="poNumberType"></param>
        /// <param name="dbConn"></param>
        /// <returns></returns>
	    public int GetRandomNextNum(NumberType poNumberType, DBConnSql dbConn)
	    {
	        int liResult = 0;

	        try
	        {
	            IdCollection loSpPars = new IdCollection();
	            SqlSPPar loSpPar = new SqlSPPar("idtype", SqlDbType.SmallInt);
	            loSpPar.ParameterValue = (int)poNumberType;
	            loSpPars.Add(loSpPar);
	            loSpPar = new SqlSPPar("nextid", SqlDbType.Int);
	            loSpPar.IsOutput = true;
	            loSpPars.Add(loSpPar);
	            loSpPar = new SqlSPPar("maxid", SqlDbType.Int);
	            loSpPar.IsOutput = true;
	            loSpPars.Add(loSpPar);

	            if (dbConn.ExecuteStoredProc("sp_ss_reserveuid", loSpPars))
	            {
	                if (loSpPars["maxid"] != null)
	                {
	                    liResult = Convert.ToInt16(((SqlSPPar)loSpPars["maxid"]).ParameterValue);
	                }
	            }
	            else
	            {
	                string ss = dbConn.ErrorMsg;

	            }
	        }
	        catch (Exception err)
	        {
	            ThreadLog.LogException(err);
	        }
	        return liResult;
	    }

	    public ComboTree GetComboTreeNode(rsQuery poQuery, string pcId, string pcParentId, string pcName, string pcRootFilter,
	        string pcDepth = "Depth", string pcDepthVal = "1")
	    {
	        ComboTree tree = new ComboTree();
	        //rsQuery loQuery = dbConn.OpenQuery("SELECT CheckItemId,CheckItemName From CheckItem WHERE IsLock='N'");
	        //rsQuery loQuery = poQuery;
	        poQuery?.FilterBy(pcRootFilter);
	        if (poQuery?.RecCount == 1)
	        {
	            poQuery.MoveFirst();
	            string lcParentId = poQuery.GetInt(pcId) + "";
	            if (string.IsNullOrEmpty(lcParentId))
	            {
	                lcParentId = poQuery.GetString(pcId);
	            }
	            if (string.IsNullOrEmpty(lcParentId))
	            {
	                return null;
	            }
	            tree.id = poQuery.GetString(pcId);
	            tree.text = poQuery.GetString(pcName);
	            DataTable loTable = poQuery.CurrentTable;
	            tree.children = GetComboTrees(loTable, pcId, pcParentId, pcName, lcParentId, pcDepth, pcDepthVal);
	        }

	        return tree;
        }

        public List<ComboTree> GetComboTree(rsQuery poQuery,string pcId,string pcParentId ,string pcName,string pcRootFilter,string pcDepth= "Depth",string pcDepthVal="1")
		{
			ComboTree tree = new ComboTree();
			//rsQuery loQuery = dbConn.OpenQuery("SELECT CheckItemId,CheckItemName From CheckItem WHERE IsLock='N'");
			//rsQuery loQuery = poQuery;
			poQuery?.FilterBy(pcRootFilter);
			if (poQuery?.RecCount == 1)
			{
				poQuery.MoveFirst();
				string lcParentId = poQuery.GetInt(pcId)+"";
				if (string.IsNullOrEmpty(lcParentId))
				{
					lcParentId = poQuery.GetString(pcId);
				}
				if (string.IsNullOrEmpty(lcParentId))
				{
					return null;
				}

				tree.id = poQuery.GetString(pcId);
				tree.text = poQuery.GetString(pcName);
				DataTable loTable = poQuery.CurrentTable;
				tree.children = GetComboTrees(loTable, pcId, pcParentId, pcName, lcParentId,pcDepth, pcDepthVal);
			}

			return tree.children;
		}

		private List<ComboTree> GetComboTrees(DataTable poTable, string pcId, string pcParentId, string pcName,
			string pcParentIdVal, string pcDepth, string pcDepthVal)
		{
			List<ComboTree> trees=new List<ComboTree>();
			try
			{
				var dRows = poTable.Select(pcParentId + "='" + pcParentIdVal.Trim()+"'");
				if (dRows.Length > 0)
				{
					foreach (DataRow row in dRows)
					{
						var lcId = row[pcId].ToString();
						var tree = new ComboTree
						{
							id = lcId,
							text = row[pcName].ToString(),
							children = GetComboTrees(poTable, pcId, pcParentId, pcName, lcId, pcDepth, pcDepthVal)
						};
						string[] closes = pcDepthVal.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
						if (closes.Length > 0)
						{
							if (closes.Contains(row[pcDepth] + ""))
							{
								tree.state = "closed";
							}
						}
						trees.Add(tree);
					}
				}
			}
			catch (Exception e)
			{
				Console.WriteLine(e);
				throw;
			}
			
			return trees;
		}

		#region 图表相关方法
		// 为饼图生成返回的Json格式数据(和其他各种图表有所区别)
		public string GenOutputDataForPie(int nCount, List<Array> listData)
		{
			string strData = string.Empty;

			if ((listData != null) && (listData.Count == 2))
			{
				Array arrayRet = new string[nCount];

				for (int i = 0; i < nCount; i++)
				{
					string strKey = listData[0].GetValue(i).ToString();
					string strVal = listData[1].GetValue(i).ToString();
					double dVal = Convert.ToDouble(strVal);

					// name 和 y 为HighCharts对象属性
					string strRow = "{\"name\":\"" + strKey + "\", \"y\":" + dVal + "}";
					arrayRet.SetValue(strRow, i);
				}

				strData = ArrayToString(arrayRet);
			}

			strData = UtilStr.GetDelimitedStr(strData);

			// 格式为 {"__totalcount":2,"__data":[{"name":"2012/03", "y":554},{"name":"2012/04", "y":5}]}
			string strRet = "{\"__totalcount\":" + nCount + ",\"__data\":" + strData + "}";

			return strRet;
		}

		// 生成返回的Json格式数据,注意此数据格式尽量和图表类型无关,给予前台页面更大自由度
		public string GenOutputData(int nCount, List<Array> listData)
		{
			string strRet = string.Empty;

			if ((listData != null) && (listData.Count > 1))
			{
				// x轴的Categories,注意各项都有双引号,用逗号相隔开
				string strCategories = ArrayToStringEx(listData[0]);
				strCategories = UtilStr.GetDelimitedStr(strCategories);

				// y轴数据列表
				Array arraySeries = new string[listData.Count - 1];
				for (int i = 0; i < listData.Count - 1; i++)
				{
					string strData = ArrayToString(listData[i + 1]);
					strData = UtilStr.GetDelimitedStr(strData);
					arraySeries.SetValue(strData, i);
				}

				// y轴数据列表转换成string
				string strSeries = ArrayToString(arraySeries);
				strSeries = UtilStr.GetDelimitedStr(strSeries);

				// 返回结果
				strRet = "{\"__totalcount\":" + nCount + ",\"__categories\":" +
						 strCategories + ",\"__series\":" + strSeries + "}";
			}

			//strRet = "{\"__totalcount\":3,\"__categories\":[\"2012/02\",\"2012/03\",\"2012/04\"],\"__series\":[[533,924,554],[1,0.99,0.98]]}";
			return strRet;
		}

		// 将一个Array各项用逗号隔开，拼成一个string
		private string ArrayToString(Array paArray)
		{
			string lcStr = "";
			foreach (string lcStr2 in paArray)
			{
				lcStr = lcStr + ((lcStr == "") ? "" : ",") + lcStr2;
			}
			return lcStr;
		}

		// 将一个Array各项左右加双引号,再用逗号隔开，拼成一个string
		private string ArrayToStringEx(Array paArray)
		{
			string lcStr = "";
			foreach (string lcStr2 in paArray)
			{
				lcStr = lcStr + ((lcStr == "") ? "" : ",") + "\"" + lcStr2 + "\"";
			}
			return lcStr;
		}
		#endregion

		public static T DeepCopyByReflect<T>(T obj)
		{
			//如果是字符串或值类型则直接返回
			if (obj is string || obj.GetType().IsValueType) return obj;

			object retval = Activator.CreateInstance(obj.GetType());
			FieldInfo[] fields = obj.GetType()
				.GetFields(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Static);
			foreach (FieldInfo field in fields)
			{
				try
				{
					field.SetValue(retval, DeepCopyByReflect(field.GetValue(obj)));
				}
				catch
				{
					//
				}
			}
			return (T) retval;
		}
	}

    public enum NumberType
    {
		VarietyCategoryNo = 1,  //产品类别编号
	    CheckProjectTypeNo = 2,
	    CheckItemNo = 3
	}

   


}