Gsmarket/Sources/GS-MarketMG-2018/SysDataLibs/AppEnv.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using SysBaseLibs;
using SysDataLibs.TableClass;
namespace SysDataLibs
{
    public class AppEnv
    {
        /// <summary>
        /// 得到表的一条记录的信息
        /// </summary>
        /// <param name="pcSql">传入的SQL语句</param>
        /// <param name="poTableInfo">要创建的对应的表信息对象</param>
        /// <returns>创建是否成功</returns>
        public static bool GetTableInfo(string pcSql, ITableInfo poTableInfo)
        {
            UserSession loSession = WebLibs.CheckLogin();
            return GetTableInfo(pcSql, poTableInfo, loSession.DBConn);
        }


        public static bool GetTableInfo(string pcSql, ITableInfo poTableInfo, DBConnSql poDBConn)
        {
            string lcError = "";
            return GetTableInfo(pcSql, poTableInfo, poDBConn, ref lcError);
        }

        /// <summary>
        /// 得到表的一条记录的信息
        /// </summary>
        /// <param name="pcSql">传入的SQL语句</param>
        /// <param name="poTableInfo">要创建的对应的表信息对象</param>
        /// <param name="poDBConn">数据库连接对象</param>
        /// <param name="pcErrMsg">创建失败的时候返回的错误信息</param>
        /// <returns>创建是否成功</returns>
        public static bool GetTableInfo(string pcSql, ITableInfo poTableInfo, DBConnSql poDBConn, ref string pcErrMsg)
        {
            bool lbRetVal = false;
            if (pcSql != "" && poTableInfo != null && poDBConn != null && poDBConn.IsOpened)
            {
                try
                {
                    rsQuery loQuery = poDBConn.OpenQuery(pcSql);
                    if (loQuery != null && loQuery.IsOpened && loQuery.RecCount > 0)
                    {
                        loQuery.MoveFirst();
                        poTableInfo.CreateTableInfo(loQuery.CurrentRow);
                        lbRetVal = true;
                    }
                    else
                        pcErrMsg = "传入的SQL语句不合法！";
                }
                catch (Exception e)
                {
                    ThreadLog.LogException(e);
                    pcErrMsg = e.Message;
                }
            }
            else
                pcErrMsg = "传入的参数不合法！";
            return lbRetVal;
        }

        /// <summary>
        /// 执行一个SQL 语句，并将数据跟 sysstatus表数据进行翻译
        /// </summary>
        /// <param name="pcSql">传入的SQL语句</param>
        /// <param name="pcTableName">要翻译的表名</param>
        /// <param name="pcData">返回的XML形式的数据集</param>
        /// <returns>执行成功返回true 否则 false</returns>
        public static bool GetDataOfTranslate(string pcSql, string pcTableName, ref string pcData)
        {
            string pcDataSchema = "";
            string pcErrorMsg = "";
            return GetDataOfTranslate(pcSql, pcTableName, ref pcData, ref pcDataSchema, ref pcErrorMsg);
        }

        public static bool GetDataOfTranslate(string pcSql, string pcTableName, ref string pcData,ref string pcDataSchema)
        {
            string pcErrorMsg = "";
            return GetDataOfTranslate(pcSql, pcTableName, ref pcData, ref pcDataSchema, ref pcErrorMsg);
        }

        /// <summary>
        /// 执行一个SQL 语句，并将数据跟 sysstatus表数据进行翻译
        /// </summary>
        /// <param name="pcSql">传入的SQL语句</param>
        /// <param name="pcTableName">要翻译的表名</param>
        /// <param name="pcData">返回的XML形式的数据集</param>
        /// <param name="pcDataSchema">返回的XMLd数据表现框架</param>
        /// <param name="pcErrorMsg">返回的错误信息</param>
        /// <returns>执行成功返回true 否则 false</returns>
        public static bool GetDataOfTranslate(string pcSql, string pcTableName, ref string pcData, ref string pcDataSchema, ref string pcErrorMsg)
        {
            UserSession loSession = WebLibs.CheckLogin();

            return GetDataOfTranslate(pcSql, pcTableName, loSession.DBConn, ref pcData, ref pcDataSchema, ref pcErrorMsg);
        }

        /// <summary>
        /// 执行一个SQL 语句，并将数据跟 sysstatus表数据进行翻译
        /// </summary>
        /// <param name="pcSql">传入的SQL语句</param>
        /// <param name="pcTableName">要翻译的表名</param>
        /// <param name="poConn">数据库连接对象</param>
        /// <param name="pcData">返回的XML形式的数据集</param>
        /// <param name="pcDataSchema">返回的XMLd数据表现框架</param>
        /// <param name="pcErrorMsg">返回的错误信息</param>
        /// <returns>执行成功返回true 否则 false</returns>
        public static bool GetDataOfTranslate(string pcSql, string pcTableName, DBConnSql poConn, ref string pcData, ref string pcDataSchema, ref string pcErrorMsg)
        {
            bool lbRetVal = false;
            if (pcSql.Trim().Length > 0 && pcTableName.Trim().Length > 0 && poConn != null && poConn.IsOpened)
            {
                try
                {
                    DataSet loDS = poConn.OpenDataSet(pcSql);
                    if (loDS != null && loDS.Tables.Count == 1)
                    {
                        pcData = loDS.GetXml();
                        pcDataSchema = loDS.GetXmlSchema();
                        DataTranslateFromSysStatus(ref pcData, pcTableName, poConn);
                    }
                    lbRetVal = true;
                }
                catch (Exception e)
                {
                    ThreadLog.LogException(e);
                    pcErrorMsg = e.Message;
                    lbRetVal = false;
                }
            }
            return lbRetVal;
        }


        public static bool DataTranslateFromSysStatus(ref string pcDataXML, string pcTableName, DBConnSql poConn)
        {
            bool lbRetVal = false;

            if (UtilStr.UAndT(pcDataXML) == UtilStr.UAndT("<NewDataSet />") || pcDataXML == "")
                return true;
            try
            {
                // ColName,CodeValue,DisplayValue
                string lcSqlTemp = "SELECT " + Sys_Status_info.cColName + "," + Sys_Status_info.cCodeValue + "," + Sys_Status_info.cDisplayValue + " FROM  " + Tn.Sys_Status + " "
                    + " WHERE  " + Sys_Status_info.cTableName + " = '" + pcTableName + "'";

                rsQuery loQuery = poConn.OpenQuery(lcSqlTemp);
                if (loQuery.IsOpened && loQuery.RecCount > 0)
                {
                    rsXmlNode loXmlNode = rsXmlNode.ParseGenericXml(pcDataXML);
                    if (loXmlNode != null && loXmlNode.Nodes.Count > 0)
                    {
                        foreach (rsXmlNode loNode in loXmlNode.Nodes)
                        {
                            loQuery.MoveFirst();
                            for (int i = 0; i < loQuery.RecCount; i++)
                            {
                                string lcColumnId = loQuery.GetString(Sys_Status_info.cColName);
                                lcColumnId = UtilStr.UAndT(lcColumnId);

                                rsXmlNode loColNode = loNode.GetChildNode(lcColumnId);
                                if (loColNode != null)
                                {
                                    string lcValue = loQuery.GetString(Sys_Status_info.cCodeValue);
                                    string lcSValue = loColNode.Value;

                                    if (UtilStr.UAndT(lcSValue) == UtilStr.UAndT(lcValue))
                                    {
                                        string lcDisplay = loQuery.GetString(Sys_Status_info.cDisplayValue);
                                        loColNode.Value = lcDisplay;
                                    }
                                }
                                loQuery.MoveNext();
                            }
                        }
                        pcDataXML = loXmlNode.ToXmlString();
                        lbRetVal = true;
                    }
                }
            }
            catch(Exception e) {
                ThreadLog.LogException(e);
            }
            return lbRetVal;
        }


        /// <param name="pcTranslateFromSysStatusTableName">如果不为空则 再从sysStatus表中再翻译</param>
        /// <returns></returns>
        public static bool GetDataAfterTrans(string pcSql, DBConnSql poConn, List<TransFrame> poList, ref string pcDataXml, ref string pcDataSchema, ref string pcErrorMsg, string pcTranslateFromSysStatusTableName)
        {
            bool lbRetVal = false;
            if (poConn == null)
            {
                return false;
            }
            lbRetVal = poConn.GetData(pcSql, ref pcDataXml, ref  pcDataSchema);
            if (lbRetVal)
            {
                GetDataAfterTrans(ref pcDataXml, poConn, poList);
                if (pcTranslateFromSysStatusTableName.Length > 0)
                {
                    //如果不为空则 再从sysStatus表中再翻译
                    DataTranslateFromSysStatus(ref pcDataXml, pcTranslateFromSysStatusTableName, poConn);
                }
            }
            else
                pcErrorMsg = poConn.ErrorMsg;
            return lbRetVal;
        }

        public static bool GetDataAfterTrans(string pcSql, DBConnSql poConn, List<TransFrame> poList, ref string pcDataXml, ref string pcDataSchema, ref string pcErrorMsg)
        {
            bool lbRetVal = false;
            if (poConn == null)
            {
                return false;
            }
            lbRetVal = poConn.GetData(pcSql, ref pcDataXml, ref  pcDataSchema);
            if (lbRetVal)
            {
                GetDataAfterTrans(ref pcDataXml, poConn, poList);
            }
            else
                pcErrorMsg = poConn.ErrorMsg;
            return lbRetVal;
        }

        // 扩展
        public static bool GetDataAfterTrans(string pcSql, DBConnSql poConn, List<TransFrame> poList, ref string pcDataXml, ref string pcDataSchema, ref string pcErrorMsg, ref int piRecCount)
        {
            bool lbRetVal = false;
            if (poConn == null)
            {
                return false;
            }
            lbRetVal = poConn.GetData(pcSql, ref pcDataXml, ref  pcDataSchema, ref piRecCount);
            if (lbRetVal)
            {
                GetDataAfterTrans(ref pcDataXml, poConn, poList);
            }
            else
                pcErrorMsg = poConn.ErrorMsg;
            return lbRetVal;
        }

        public static bool GetDataAfterTrans(ref string pcDataXml, DBConnSql poConn, List<TransFrame> poList)
        {
            bool lbRetVal = false;
            try
            {
                rsXmlNode loXmlNode = rsXmlNode.ParseGenericXml(pcDataXml);
                if (loXmlNode != null && loXmlNode.Nodes.Count > 0)
                {
                    Dictionary<string, TransFrame> loDesList = new Dictionary<string, TransFrame>();
                    foreach (TransFrame loTF in poList)
                    {
                        if (loTF.DataTransType == DataTransType.Trans)
                        {
                            if (loTF.IsAvail)
                            {
                                if (loTF.CreatQuery(poConn))
                                {
                                    if (loDesList.ContainsKey(loTF.SourceField))
                                    {
                                        loDesList[loTF.SourceField] = loTF;
                                    }
                                    else
                                        loDesList.Add(loTF.SourceField, loTF);
                                }
                            }
                        }
                        else if (loTF.DataTransType == DataTransType.Format)
                        {
                            if (loDesList.ContainsKey(loTF.SourceField))
                            {
                                loDesList[loTF.SourceField] = loTF;
                            }
                            else
                                loDesList.Add(loTF.SourceField, loTF);
                        }
                    }

                    if (loDesList.Count > 0)
                    {
                        foreach (rsXmlNode loNodes in loXmlNode.Nodes)
                        {
                            foreach (string lcKey in loDesList.Keys)
                            {
                                rsXmlNode loNode = loNodes.GetChildNode(lcKey);
                                if (loNode != null)
                                {
                                    loNode.Value = loDesList[lcKey].GetDisplayValue(loNode.Value);
                                }
                            }
                        }
                    }
                    pcDataXml = loXmlNode.ToXmlString();
                }

            }
            catch(Exception e) {
                ThreadLog.LogException(e);
            }

            return lbRetVal;
        }

        public static void MoveRecordSort(string pcTableName, string pcKeyField, string pcKeyFieldCurValue, string pcSortField, bool pbMoveUp)
        {
            UserSession loSession = WebLibs.CheckLogin();
            string lcSql = "select " + pcKeyField + "," + pcSortField + " from " + pcTableName + " order by " + pcSortField;

            rsQuery loQuery = loSession.DBConn.OpenQuery(lcSql);
            if (loQuery != null && loQuery.IsOpened && loQuery.RecCount > 0)
            {
                if (loQuery.GoToRecordByFieldsAndValues(pcKeyField, pcKeyFieldCurValue))
                {
                    int loCurSort = loQuery.GetInt(pcSortField);
                    int liAim = 0;
                    string lcAim = "";
                    if (pbMoveUp)
                    {
                        if (loQuery.RecNo > 0)
                        {
                            loQuery.MovePrev();
                            lcAim = loQuery.GetString(pcKeyField);
                            liAim = loQuery.GetInt(pcSortField);
                        }
                    }
                    else
                    {
                        if (loQuery.RecNo < (loQuery.RecCount - 1))
                        {
                            loQuery.MoveNext();
                            lcAim = loQuery.GetString(pcKeyField);
                            liAim = loQuery.GetInt(pcSortField);
                        }
                    }
                    if (lcAim.Trim().Length > 0)
                    {
                        lcSql = "update  " + pcTableName + " set " + pcSortField + " = " + liAim + "  where  " + pcKeyField + "='" + pcKeyFieldCurValue + "'  ";
                        lcSql += "update  " + pcTableName + " set " + pcSortField + " = " + loCurSort + "  where  " + pcKeyField + "='" + lcAim + "'  ";

                        loSession.DBConn.ExcuteSqlTran(lcSql);
                    }
                }
            }
        }

        public static int GetNextSortNumber(string pcTableName, string pcSortField, DBConnSql poConn)
        {
            int liRetVal = 0;
            string lcSql = " select  " + pcSortField + "  from  " + pcTableName + " order by " + pcSortField + " desc ";
            rsQuery loQuery = poConn.OpenQuery(lcSql);
            if (loQuery != null && loQuery.IsOpened && loQuery.RecCount > 0)
            {
                loQuery.MoveFirst();
                liRetVal = loQuery.GetInt(pcSortField);
                liRetVal++;
            }
            return liRetVal;
        }
        /// <summary>
        /// 判断新插入的键值在表中的是否存在
        /// </summary>
        /// <param name="pcTableName">表名</param>
        /// <param name="pcKeyField">主键字段</param>
        /// <param name="pcKeyValue">主键值</param>
        /// <param name="poConn">数据库连接串</param>
        /// <returns></returns>
        public static bool IsExistPrKey(string pcTableName, string pcKeyField, string pcKeyValue, DBConnSql poConn)
        {
            bool lbRetVal = false;
            string lcSql = " select  " + pcKeyField + " from " + pcTableName + " where " + pcKeyField + "='" + pcKeyValue + "'";
            rsQuery loQuery = poConn.OpenQuery(lcSql);
            if (loQuery != null && loQuery.IsOpened && loQuery.RecCount > 0)
            {
                lbRetVal = true;
            }
            return lbRetVal;
        }

        private static SysSettingObj _SysSettingObj = null;
        public static SysSettingObj SysSetObj
        {
            get
            {
                if (_SysSettingObj == null)
                {
                    _SysSettingObj = new SysSettingObj();
                }
                return _SysSettingObj;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        private static YtDataMappingObj _YtDataMappingObj = null;
        public static YtDataMappingObj YtDataMappingObj
        {
            get
            {
                if (_YtDataMappingObj == null)
                {
                    _YtDataMappingObj = new YtDataMappingObj();
                }
                return _YtDataMappingObj;
            }
        }

        public static string GetTextByID(string pcId, int piIdValue, string pcNameCol, string pcTableId, DBConnSql poConn)
        {
            string lcRetVal = "", lcSql = "";

            lcSql = " SELECT " + pcNameCol + " FROM " + pcTableId + " WHERE " + pcId + " =" + piIdValue;
            rsQuery loQuery = poConn.OpenQuery(lcSql);
            if (loQuery != null && loQuery.IsOpened)
            {
                lcRetVal = loQuery.GetString(pcNameCol);
            }
            return lcRetVal;
        }

        /// <summary>
        /// 传入表名及表的ID名和值、需要的字段名、返回所需字段的(string)值
        /// </summary>
        /// <param name="pcId"></param>
        /// <param name="pcIdValue"></param>
        /// <param name="pcNameCol"></param>
        /// <param name="pcTableId"></param>
        /// <param name="poConn"></param>
        /// <returns></returns>
        public static string GetTextByID(string pcId, string pcIdValue, string pcNameCol, string pcTableId, DBConnSql poConn)
        {
            string lcRetVal = "", lcSql = "";

            lcSql = " SELECT " + pcNameCol + " FROM " + pcTableId + " WHERE " + pcId + " ='" + pcIdValue + "' ";
            rsQuery loQuery = poConn.OpenQuery(lcSql);
            if (loQuery != null && loQuery.IsOpened)
            {
                lcRetVal = loQuery.GetString(pcNameCol);
            }
            return lcRetVal;
        }

        /// <summary>
        /// 传入sql语句 表名 及需要的字段名、返回所需字段的(string)值
        /// </summary>
        public static string GetTextWithSQL(string pcSQL, string pcColName, DBConnSql poConn)
        {
            string lcRetVal = "";
            rsQuery loQuery = poConn.OpenQuery(pcSQL);
            if (loQuery != null && loQuery.IsOpened)
            {
                lcRetVal = loQuery.GetString(pcColName);
            }
            return lcRetVal;
        }

        /// <summary>
        /// 从sysstatus表里得到显示值
        /// </summary>
        /// <param name="pcTable">表名</param>
        /// <param name="pcColumn">列名</param>
        /// <param name="pcVal">值</param>
        /// <returns>显示的值</returns>
        public static string GetShowTextFromSysStatus(string pcTable, string pcColumn, string pcVal, DBConnSql poConn)
        {
            string lcRetVal = "";
            string lcSql = "select " + Sys_Status_info.cDisplayValue + " from " + Tn.Sys_Status
                + " where " + Sys_Status_info.cTableName + " ='" + pcTable
                + "' and " + Sys_Status_info.cColName + "='" + pcColumn
                + "' and " + Sys_Status_info.cCodeValue + "='" + pcVal + "'";
            rsQuery loQuery = poConn.OpenQuery(lcSql);
            if (loQuery != null && loQuery.IsOpened)
            {
                lcRetVal = loQuery.GetString(Sys_Status_info.cDisplayValue);
            }
            return lcRetVal;
        }

        public static decimal GetPreBalance(string pcSellerId, string pcMarketId, DBConnSql poConn)
        {
            decimal lfRetVal = 0;
            if (pcSellerId.Trim().Length > 0 && pcMarketId.Trim().Length > 0 && poConn != null)
            {
                string lcSql = "select ID, PostBalance  from  " + Tn.ReserveCompensateLog + " where SellerID ='" + pcSellerId + "' and MarketID='" + pcMarketId + "'";
                rsQuery loQuery = poConn.OpenQuery(lcSql);
                if (loQuery != null && loQuery.IsOpened && loQuery.RecCount > 0)
                {
                    loQuery.SortBy("ID", false);
                    loQuery.MoveFirst();
                    lfRetVal = loQuery.GetDecimal("PostBalance");
                }
            }
            return lfRetVal;
        }

        /// <summary>
        /// 获取KeyValueByStatus
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="colName"></param>
        /// <param name="dbConn"></param>
        /// <returns></returns>
        public static Dictionary<string,string> GetKeyValueByStatus(string tableName, string colName,  DBConnSql dbConn)
        {
            Dictionary<string, string> loDic = new Dictionary<string, string>();
            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++)
                {
                    loDic.Add(loQuery.GetString(Sys_Status_info.cCodeValue), loQuery.GetString(Sys_Status_info.cDisplayValue));
                    loQuery.MoveNext();
                }
            }
            return loDic;
        }
        /// <summary>
        /// 获取KeyValueByTABLE
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="colName"></param>
        /// <param name="dbConn"></param>
        /// <returns></returns>
        public static Dictionary<string, string> GetKeyValueByTable(string tableName, string DisplayColName, string ValueColName,  DBConnSql dbConn, string poWhereSql="")
        {
            Dictionary<string, string> loDic = new Dictionary<string, string>();
            string lcSql = "SELECT "+ ValueColName + ","+ DisplayColName + " FROM " + tableName;
            if (!string.IsNullOrEmpty(poWhereSql))
            {
                lcSql += " WHERE " + poWhereSql;
            }
            rsQuery loQuery = dbConn.OpenQuery(lcSql);
            if (loQuery.IsOpened && loQuery.RecCount > 0)
            {
                loQuery.MoveFirst();
                for (int i = 0; i < loQuery.RecCount; i++)
                {
                    loDic.Add(loQuery.GetString(ValueColName), loQuery.GetString(DisplayColName));
                    loQuery.MoveNext();
                }
            }
            return loDic;
        }

    }
}