using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using Utility.Utils.DbUtils;
using Utility.Utils.ConvertUtils;
using System.Data;
using System.IO;
using Utility.Utils.OfficeUtils;
using Utility.Utils.FileUtils;
using System.Xml;
using System.Text.RegularExpressions;

namespace LayerWebservice
{
    /// <summary>
    /// LayerSynchro 的摘要说明
    /// </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 LayerSynchro : System.Web.Services.WebService
    {

    //    [WebMethod]
    //    public string getDelLayer()
    //    {

    //        string resultStr = "{\"error\":\"true\"}";
    //        DataConvertor dc = new DataConvertor();
    //        string connstr = System.Configuration.ConfigurationManager.AppSettings["connstr"].ToString();
    //        string shapeOwner = System.Configuration.ConfigurationManager.AppSettings["shapeOwner"].ToString();
    //        string LogicOwner = System.Configuration.ConfigurationManager.AppSettings["LogicOwner"].ToString();
    //        string dbtype = System.Configuration.ConfigurationManager.AppSettings["database"].ToString();
    //        string f_table_schema = System.Configuration.ConfigurationManager.AppSettings["f_table_schema"].ToString();
    //        DbOperator dbOpe = new DbOperator(connstr, dbtype);

    //        if (!dbOpe.Connect())
    //        {
    //            return resultStr;
    //        }


    //        string sql = "SELECT a.[f_table_name] as 图层名称 FROM [" + shapeOwner + "].[sde].[SDE_geometry_columns] a,[" + shapeOwner + "].[sde].[SDE_layers] b where  a.f_table_name = b.table_name and a.geometry_type in (1,9,11) and a.f_table_schema='" + f_table_schema + "' and b.layer_config<>'DATA_DICTIONARY'  order by geometry_type";
    //        DataTable dt_sde = dbOpe.Select(sql);
    //        if (dt_sde == null )
    //        {
    //            return resultStr;
    //        }
    //        sql = "SELECT [LAYER_ID] ,[LAYER_NAME] as 图层名称 FROM [" + LogicOwner + "].[GS_LOGIC].[T_LAYERS]";
    //        DataTable dt_logic = dbOpe.Select(sql);
    //        if (dt_logic == null )
    //        {
    //            return resultStr;
    //        }
    //            for (int j = dt_logic.Rows.Count - 1; j >= 0; j--)
    //            {
    //                bool f = true;
    //                for (int i = 0; i < dt_sde.Rows.Count; i++)
    //                {
                       
    //                    if (dt_sde.Rows[i]["图层名称"].ToString() == dt_logic.Rows[j]["图层名称"].ToString())
    //                    {
    //                        f = false;
    //                        break;
    //                    }
    //                }
    //                if (!f)
    //                {
    //                    dt_logic.Rows.RemoveAt(j);
    //                }
    //            }
            
           
    //        dbOpe.Close();

    //        return dc.DataTable2JSON(dt_logic).ToString();
    //    }
       
        public  bool IsNumAndEn(string input)
        {
            string pattern = @"^[A-Za-z0-9]+$";
            Regex regex = new Regex(pattern);
            return regex.IsMatch(input);
        }
    
        public  bool IsNumAndEnAndCh(string input)
        {
            string pattern = @"^[A-Za-z0-9\u4e00-\u9fa5]+$";
            Regex regex = new Regex(pattern);
            return regex.IsMatch(input);
        }

        private const string StrRegex = @"-|;|,|/|(|)|[|]|}|{|%|@|*|!|'";
       
        private const string StrKeyWord = @"select|insert|delete|from|count(|drop table|update|truncate|asc(|mid(|char(|xp_cmdshell|exec master|netlocalgroup administrators|:|net user|""|or|and";
        
        /// <summary>
        /// 检查_sword是否包涵SQL关键字
        /// </summary>
        /// <param name="_sWord">需要检查的字符串</param>
        /// <returns>存在SQL注入关键字时返回 true，否则返回 false</returns>
        public bool CheckKeyWord(string _sWord)
        {
            bool result = false;
            //模式1 : 对应Sql注入的可能关键字
            string[] patten1 = StrKeyWord.Split('|');
            //模式2 : 对应Sql注入的可能特殊符号
            string[] patten2 = StrRegex.Split('|');
            //开始检查 模式1:Sql注入的可能关键字 的注入情况
            foreach (string sqlKey in patten1)
            {
                if (_sWord.IndexOf(" " + sqlKey) >= 0 || _sWord.IndexOf(sqlKey + " ") >= 0)
                {
                    //只要存在一个可能出现Sql注入的参数,则直接退出
                    result = true;
                    break;
                }
            }
            //开始检查 模式1:Sql注入的可能特殊符号 的注入情况
            foreach (string sqlKey in patten2)
            {
                if (_sWord.IndexOf(sqlKey) >= 0)
                {
                    //只要存在一个可能出现Sql注入的参数,则直接退出
                    result = true;
                    break;
                }
            }
            return result;
        }
        public string getLayerAuthor(string dep)
        {
            string lays = "";
            string connstr = System.Configuration.ConfigurationManager.AppSettings["connstr"].ToString();
            string LogicOwner = System.Configuration.ConfigurationManager.AppSettings["LogicOwner"].ToString();
            string dbtype = System.Configuration.ConfigurationManager.AppSettings["database"].ToString();
            DbOperator dbOpe = new DbOperator(connstr, dbtype);

            if (!dbOpe.Connect())
            {
                return "";
            }


            string sql = "select  a.LAYER_NAME as 图层名称,a.LAYER_ID  from [" + LogicOwner + "].[t_layers] a  where a.DEPARTMENT ='" + dep + "' union  " +
            "select b.LAYER_NAME as 图层名称,b.LAYER_ID  from  [" + LogicOwner + "].[t_layers] b  where  b.LEVEL_ID = 1 and b.DEPARTMENT !='" + dep + "' union  " +
            "select f.LAYER_NAME as 图层名称,f.LAYER_ID from  [" + LogicOwner + "].[t_layers] f,[" + LogicOwner + "].[t_resource_authorized] t " +
            "where f.LAYER_ID = t.RELATED_ID and t.APPLY_DEPARTMENT ='" + dep + "' and t.authorized_type_id = '2' " +
            "and t.AUTHORIZED_STATUS = '1' and  f.LEVEL_ID = 2 and f.DEPARTMENT !='" + dep + "'";
            DataTable dt_logic = dbOpe.Select(sql);

            if (dt_logic != null || dt_logic.Rows.Count > 0)
            {
                for (int j = 0; j < dt_logic.Rows.Count; j++)
                {
                    lays += dt_logic.Rows[j]["图层名称"].ToString() + ",";
                }
                lays = lays.Substring(0, lays.Length - 1);
            }
            dbOpe.Close();

            return lays;
        }

        [WebMethod]
        public string getMapAuthor(string serviceName,string dep,string username="",string userdep="")
        {
            if(!IsNumAndEn(serviceName) ||!IsNumAndEnAndCh(dep)||!IsNumAndEnAndCh(username)||!IsNumAndEnAndCh(userdep))
            {
                return "{\"success\":\"false\",\"Message\":\"参数不正确\"}"; 
            }
            
            string relatetab = "";
            string relateLay = "";
            string sqlays = "";
            string result = "";
            string connstr = System.Configuration.ConfigurationManager.AppSettings["connstr"].ToString();
            string LogicOwner = System.Configuration.ConfigurationManager.AppSettings["LogicOwner"].ToString();
            string dbtype = System.Configuration.ConfigurationManager.AppSettings["database"].ToString();
            DbOperator dbOpe = new DbOperator(connstr, dbtype);
            string insSQL = "";
            string service_id = "";
            string resultStr = "";
            if (!dbOpe.Connect())
            {
                return  "{\"success\":\"false\",\"Message\":\"数据库连接失败\"}"; 
            }

            string sql = "SELECT [NAME],[SERVICE_URL],[RELATE_LAYERS],[RELATE_TABLES],[SERVICE_ID]  FROM [" +
                LogicOwner + "].[T_SERVICE] where [TYPE_ID] = 2 and [NAME] = '" + serviceName +
                "' and DEPARTMENT = '" + dep + "'";

            DataTable dt_logic = dbOpe.Select(sql);

            if (dt_logic != null && dt_logic.Rows.Count ==1)
            {
                relatetab = dt_logic.Rows[0]["RELATE_TABLES"].ToString();
                relateLay = dt_logic.Rows[0]["RELATE_LAYERS"].ToString();
                service_id = dt_logic.Rows[0]["SERVICE_ID"].ToString();
                sqlays = getLayerAuthor(dep);
                if (sqlays.Length > 0)
                {
                    insSQL += "INSERT INTO [GS_LOGIC].[T_AUDIT]([AUDIT_TYPE],[RELATE_NAME],[VISIT_DATE],[DEPARTMENT],[USERNAME]) VALUES(1,'"
                                    + serviceName + "',getdate(),'" + userdep + "','" + username + "');";
                    string[] arrSq = sqlays.Split(',');
                    string[] arrRelateTab = relatetab.Split(',');
                    string[] arrRelateLay = relateLay.Split(',');
                    if (arrRelateTab.Length != arrRelateLay.Length)
                    {
                        resultStr = "{\"success\":\"false\",\"Message\":\"数据不匹配,建议重新发布服务\"}";
                    }
                    else
                    {
                        for (int i = 0; i < arrRelateTab.Length; i++)
                        {
                            string r = arrRelateTab[i];
                            foreach (string s in arrSq)
                            {
                                if (r == s)
                                {

                                    insSQL += "INSERT INTO [GS_LOGIC].[T_AUDIT]([AUDIT_TYPE],[RELATE_NAME],[VISIT_DATE],[DEPARTMENT],[USERNAME]) VALUES(2,'"
                                       + r + "',getdate(),'" + userdep + "','" + username + "');";
                                    result += arrRelateLay[i] + ",";
                                    break;
                                }
                            }
                        }
                        if (result.Length > 0)
                        {
                            result = result.Substring(0, result.Length - 1);
                           
                            dbOpe.Update(insSQL.Substring(0, insSQL.Length - 1), ";");
                        }

                        resultStr = "{\"success\":\"true\",\"Message\":\"" + result + "\"}";
                    }
                    
                }
                else
                {
                    resultStr = "{\"success\":\"true\",\"Message\":\"\"}";
                }
            }
            else
            {
                resultStr =  "{\"success\":\"false\",\"Message\":\"未获取该服务的授权\"}"; 
            }

            dbOpe.Close();
            return resultStr;
        }
        
    }
}