Gsmarket/Sources/GS-MarketMG-2018/GSMarketSys/WS/WSGetData.asmx.cs

using SysBaseLibs;
using SysDataLibs;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Xml.Serialization;

namespace GSMarketSys.WS
{
    /// <summary>
    /// WSGetData 的摘要说明
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消注释以下行。 
    // [System.Web.Script.Services.ScriptService]
    public class WSGetData : System.Web.Services.WebService
    {

        public WSGetData()
        {
            //如果使用设计的组件，请取消注释以下行 
            //InitializeComponent(); 
        }

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

        protected string GetLocalTableName(DBConnSql loDBConn, string DSName)
        {
            string strRet = string.Empty;
            if (loDBConn != null & loDBConn.Open() && loDBConn.IsOpened)
            {
                string strSqlCommand = "select TableId from PlatRegDsn where PlatDsName='" + DSName + "'";
                rsQuery loQuery = loDBConn.OpenQuery(strSqlCommand);
                if (loQuery != null && loQuery.IsOpened && loQuery.RecCount > 0)
                {
                    strRet = loQuery.GetString("TableId");
                }
            }
            return strRet;
        }

        [XmlInclude(typeof(QueryOperate))]
        [XmlInclude(typeof(Condition))]

        [WebMethod]
        // 子系统在登录时生成guid，以便子系统实现调用查询的权限控制
        public string GetSubSysData(Condition[] condition_Query, string dSName, string[] dispField, string guid)
        {
            string strJson = string.Empty;
            string strCondition = string.Empty;
            DBConnSql loDBConn = null;

            try
            {
                if (LoginGUID.Instance.GetOne(guid) == null)
                {
                    string strMsg = "GetSubSysData: Invalid guid " + guid;
                    throw new Exception(strMsg);
                }

                if (dispField == null || dispField.Length == 0)
                {
                    throw new Exception("GetSubSysData: Param dispField is null! ");
                }

                if (condition_Query != null)
                {
                    foreach (Condition cnd in condition_Query)
                    {
                        string strQuote = string.Empty;    // 值比较时是否需要加单引号

                        // 左括号
                        if (cnd.BeginLeftTag == 1)
                            strCondition += " (";

                        // string或者日期类型
                        if ((cnd.Operate.FieldType == 0) || (cnd.Operate.FieldType == 1))
                            strQuote = "'";

                        // 比较条件
                        switch (cnd.Operate.OptTag)
                        {
                            case 0:
                                strCondition += cnd.FieldName + " like " + strQuote + "%" + cnd.Operate.FieldValue + "%" + strQuote + " ";
                                break;
                            case 1:
                                strCondition += cnd.FieldName + " like " + strQuote + "%" + cnd.Operate.FieldValue + strQuote + " ";
                                break;
                            case 2:
                                strCondition += cnd.FieldName + " like " + strQuote + cnd.Operate.FieldValue + "%" + strQuote + " ";
                                break;
                            case 3:
                                strCondition += cnd.FieldName + " =" + strQuote + cnd.Operate.FieldValue + strQuote + " ";
                                break;
                            case 4:
                                strCondition += cnd.FieldName + " >=" + strQuote + cnd.Operate.FieldValue + strQuote + " ";
                                break;
                            case 5:
                                strCondition += cnd.FieldName + " <=" + strQuote + cnd.Operate.FieldValue + strQuote + " ";
                                break;
                        }

                        // 右括号
                        if (cnd.EndRightTag == 1)
                            strCondition += ") ";

                        // 逻辑连接在最后，无论是否有右括号
                        switch (cnd.LogicJoin)
                        {
                            case 0:
                                strCondition += " and ";
                                break;
                            case 1:
                                strCondition += " or ";
                                break;
                            case 2:
                                break;
                        }
                    }//foreach
                }

                // 列名
                string strDispFields = string.Empty;
                foreach (string strDispField in dispField)
                {
                    string strDispFieldTrim = strDispField.Trim();
                    if (strDispFieldTrim != "")
                    {
                        if (strDispFields == "")
                            strDispFields += strDispFieldTrim;
                        else
                            strDispFields += "," + strDispFieldTrim;
                    }
                }
                if (strDispFields == "")
                {
                    //strDispFields = "*";
                    throw new Exception("GetSubSysData: Param dispField is empty! ");
                }

                // 进行查询
                loDBConn = new DBConnSql();
                if (loDBConn.Open() && loDBConn.IsOpened)
                {
                    // 本地数据表(视图)名称
                    string strDSName = GetLocalTableName(loDBConn, dSName);
                    if (strDSName == "")
                    {
                        throw new Exception("GetSubSysData: invalid DSName " + dSName);
                    }

                    // 查询语句
                    string strSqlCommand = " select " + strDispFields + " from " + strDSName + " where " + strCondition;
                    SysBaseLibs.ThreadLog.LogInfo("GetSubSysData:" + strSqlCommand);

                    // sql注入检查
                    string strTemp = strSqlCommand.ToUpper();
                    if ((strTemp.IndexOf("DELETE ") >= 0)
                        || (strTemp.IndexOf("INSERT ") >= 0)
                        || (strTemp.IndexOf("UPDATE ") >= 0)
                        || (strTemp.IndexOf("TRUNCATE ") >= 0)
                        || (strTemp.IndexOf("DROP ") >= 0)
                        || (strTemp.IndexOf("ALTER ") >= 0))
                    {
                        throw new Exception("GetSubSysData: sql injection");
                    }

                    rsQuery loQuery = loDBConn.OpenQuery(strSqlCommand);
                    if (loQuery != null && loQuery.IsOpened && loQuery.RecCount > 0)
                    {
                        Array arrayFields = UtilStr.StrToArrayEx(loQuery.AllFields, ",");
                        int nColumnCount = loQuery.Columns.Count;

                        Array allRows = Array.CreateInstance(typeof(string), loQuery.RecCount);
                        // 轮询每一行
                        for (int i = 0; i < loQuery.RecCount; i++)
                        {
                            Array curRow = Array.CreateInstance(typeof(string), nColumnCount);
                            for (int j = 0; j < arrayFields.Length; j++)
                            {
                                // 列名
                                string strField = arrayFields.GetValue(j).ToString();

                                // 在数值两边加上单引号
                                string strVal = loQuery.GetString(strField);
                                strVal = strVal.Replace("'", "");
                                strVal = "'" + strVal + "'";

                                curRow.SetValue(strField + ":" + strVal, j);
                            }

                            // 每一行的格式：   {column1:value1,column2:value2,column3:value3}
                            string strCurRow = ArrayToString(curRow);
                            strCurRow = "{" + strCurRow + "}";

                            allRows.SetValue(strCurRow, i);
                            loQuery.MoveNext();
                        }

                        // 返回格式：{table:[{row1},{row2}]}
                        strJson = ArrayToString(allRows);
                        strJson = "{" + dSName + ":[" + strJson + "]}";
                    }
                }

            }
            catch (Exception err)
            {
                strJson = string.Empty;
                SysBaseLibs.ThreadLog.LogInfo("GetSubSysData:" + err.Message);
            }
            finally
            {
                if (loDBConn != null && loDBConn.IsOpened)
                    loDBConn.Close();
            }

            return strJson;
        }
    }
    [Serializable]
    public class QueryOperate
    {
        public int OptTag;          // 0:like '%value%'     1:like '%value'  2:like 'value%'  3：=  4：>=   5：<=
        public string FieldValue;   // 字段值
        public int FieldType;       // 字段类型：0:string   1:dateTime  2:int   3:decimal
    }

    [Serializable]
    public class Condition
    {
        public int BeginLeftTag;        // 是否加左括号，0：不加    1：加
        public string FieldName;        // 数据字典项名称，可能是某个数据表或视图的字段名称
        public QueryOperate Operate;    // 查询操作符对象
        public int LogicJoin;           // 逻辑连接符，0：and   1：or   2：无逻辑连接
        public int EndRightTag;         // 是否加右括号，0：不加    1：加
    }
}