using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using System.Data.SqlServerCe;

namespace NVSGClient.Map
{
    public class GISClass
    {
        public class GetService
        {
            public static string connectionString = @"Data Source=|DataDirectory|\App_Data\NVSGDB.sdf";//数据库连接

            public sectorDto[] listAllTopSectors()
            {
                //string sqlStr="SELECT D.LVL, S.SCT_ID, S.SCT_NBR, S.SCT_NAM, S.SCT_PID, S.SCT_DESC,S.CRT_TIME,S.CRT_BY,S.LST_UPD_TIME,S.LST_UPD_BY,S.VRSN_ID, S.SCT_CLR FROM SECTOR S LEFT JOIN DVC_TRV D ON D.ID = S.SCT_ID  where D.LVL=1";
                string sqlStr = "SELECT S.SCT_ID as id,S.SCT_NBR as sectorNumber ,S.SCT_NAM as name,  S.SCT_PID as parentId,  S.SCT_DESC  as description, D.LVL as level,  S.SCT_CLR as sectorColor FROM SECTOR S LEFT JOIN DVC_TRV D ON D.ID = S.SCT_ID  where D.LVL=1";
                using (SqlCeConnection connection = new SqlCeConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        connection.Open();
                        SqlCeDataAdapter command = new SqlCeDataAdapter(sqlStr, connection);
                        command.Fill(ds, "ds");
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }

                    //实体字段集合
                    sectorDto entity = new sectorDto();
                    FieldInfo[] Infos = entity.GetType().GetFields();

                    sectorDto[] Areas = new sectorDto[ds.Tables[0].Rows.Count];
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = ds.Tables[0].Rows[i];                       
                        sectorDto sdto = new sectorDto();
                        foreach (DataColumn dcol in dr.Table.Columns)
                        {
                            foreach (FieldInfo info in Infos)
                            {
                                if (info.Name == dcol.ColumnName)
                                {
                                    if (dr[info.Name] == DBNull.Value) continue;
                                    info.SetValue(sdto, dr[info.Name]);
                                }
                            }
                        }
                        Areas[i] = sdto;
                    }
                    return Areas;
                }
            }

            //#region DataToLogic 数据层数据转变为逻辑层数据
            ///// <summary>
            ///// 数据层数据转变为逻辑数据
            ///// </summary>
            ///// <param name="dr"></param>
            //public void DataToLogic(DataRow dr)
            //{
            //    PropertyInfo[] pInfos = this.GetType().GetProperties();
            //    for (int i = 0; i < pInfos.Length; i++)
            //    {
            //        PropertyInfo pInfo = pInfos[i];
            //        object[] obj = pInfo.GetCustomAttributes(true);

            //        if (obj.Length > 0 && ((DataFieldsAttribute)obj[0]).IsDataFields)
            //        {
            //            if (!dr.Table.Columns.Contains(pInfo.Name)) continue;
            //            if (dr[pInfo.Name] == DBNull.Value) continue;

            //            //值转换
            //            pInfo.SetValue(this, dr[pInfo.Name], null);
            //        }
            //    }
            //}
            //#endregion

            public searchTreeNodeResult[] listAllTreeNodes()
            {
                //string sqlStr ="SELECT ID, NAME, PID, T.LVL, D.DTY_ID, (CASE T.LVL WHEN 4 THEN D.LOCATION ELSE S.LOCATION END) LOCATION,S.SYMB_TYP, S.SCT_CLR FROM DVC_TRV T LEFT JOIN DVC_INF D ON D.DVC_ID = T.ID LEFT JOIN SECTOR S ON S.SCT_ID = T.ID";
                //string sqlStr ="SELECT ID as id, NAME as name, PID as parentId, T.LVL as level,S.SYMB_TYP as symbolType, D.DTY_ID as deviceTypeId, S.SCT_CLR as sectorColor ,D.LOCATION as DeviceLocation,S.LOCATION  as SectorLocation FROM DVC_TRV T  LEFT JOIN DVC_INF D ON D.DVC_ID = T.ID  LEFT JOIN SECTOR S  ON S.SCT_ID = T.ID";
                string sqlStr = "SELECT ID as id, NAME as name, PID as parentId, T.LVL as level,S.SYMB_TYP as symbolType, D.DTY_ID as deviceTypeId, (CASE T.LVL WHEN 4 THEN D.LOCATION ELSE S.LOCATION END)  deviceOrSectorLocation, S.SCT_CLR as sectorColor ,D.LOCATION as DeviceLocation,S.LOCATION  as SectorLocation FROM DVC_TRV T  LEFT JOIN DVC_INF D ON D.DVC_ID = T.ID  LEFT JOIN SECTOR S  ON S.SCT_ID = T.ID";
                using (SqlCeConnection connection = new SqlCeConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        connection.Open();
                        SqlCeDataAdapter command = new SqlCeDataAdapter(sqlStr, connection);
                        command.Fill(ds, "ds");
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }

                    //实体字段集合
                    searchTreeNodeResult entity = new searchTreeNodeResult();
                    FieldInfo[] Infos = entity.GetType().GetFields();

                    searchTreeNodeResult[] dtnrs = new searchTreeNodeResult[ds.Tables[0].Rows.Count];
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = ds.Tables[0].Rows[i];
                        searchTreeNodeResult dtnr = new searchTreeNodeResult();
                        foreach (DataColumn dcol in dr.Table.Columns)
                        {
                            foreach (FieldInfo info in Infos)
                            {
                                if (info.Name == dcol.ColumnName)
                                {
                                    if (dr[info.Name] == DBNull.Value) continue;
                                    info.SetValue(dtnr, dr[info.Name]);
                                }
                            }
                        }
                        dtnrs[i] = dtnr;
                    }
                    return dtnrs;
                }
            }

            public void batchUpdateDeviceAndSectorLocation(updateDeviceAndSectorLocationParam[] DeviceAndSectorLocationParamList)
            {
                //string sqlStr="“UPDATE DVC_INF SET LST_UPD_BY = #lastUpdateBy:VARCHAR#,LOCATION = #location:VARCHAR# WHERE DVC_ID = #id:LONG#”";
                string sqlStr = "SELECT DVC_ID, DVC_NBR, DVC_NAM, DTY_ID, DPV_ID, DGP_ID, SCT_ID, LOCATION, BRD_NAM FROM DVC_INF";
                using (SqlCeConnection connection = new SqlCeConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        connection.Open();
                        SqlCeCommand command = new SqlCeCommand(sqlStr, connection);
                        command.ExecuteNonQuery();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }

            public searchSectorForPeopleCountingResult[] listAllSectorsForPeopleCounting()
            {
                string sqlStr = "SELECT S.SCT_ID as id, S.SCT_NAM as name, S.SCT_PID as parentId, T.LVL as level, S.SYMB_TYP as symbolType, S.LOCATION as location, S.SCT_CLR as sectorColor,  50 as numberOfPeople FROM SECTOR S  JOIN DVC_TRV T ON T.ID = S.SCT_ID";
                using (SqlCeConnection connection = new SqlCeConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        connection.Open();
                        SqlCeDataAdapter command = new SqlCeDataAdapter(sqlStr, connection);
                        command.Fill(ds, "ds");
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }

                    //实体字段集合
                    searchSectorForPeopleCountingResult entity = new searchSectorForPeopleCountingResult();
                    FieldInfo[] Infos = entity.GetType().GetFields();

                    searchSectorForPeopleCountingResult[] dtos = new searchSectorForPeopleCountingResult[ds.Tables[0].Rows.Count];
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = ds.Tables[0].Rows[i];
                        searchSectorForPeopleCountingResult dto = new searchSectorForPeopleCountingResult();
                        foreach (DataColumn dcol in dr.Table.Columns)
                        {
                            foreach (FieldInfo info in Infos)
                            {
                                if (info.Name == dcol.ColumnName)
                                {
                                    if (dr[info.Name] == DBNull.Value) continue;
                                    info.SetValue(dto, dr[info.Name]);
                                }
                            }
                        }
                        dtos[i] = dto;
                    }
                    return dtos;
                }
            }

            public deviceTypeDto[] listAllDeviceTypes()
            {
                //string sqlStr = "SELECT DTY_ID, DTY_NBR, DTY_NAM, DTY_PID,ACT_IDS, NML_IMG_CTNT, FAE_IMG_CTNT, SAE_IMG_CTNT, TAE_IMG_CTNT, UNM_IMG_CTNT, DTY_DESC,LVL,DSPL_ORDR, CRT_TIME, CRT_BY, LST_UPD_TIME, LST_UPD_BY, VRSN_ID from DVC_TYP WHERE 1 = 1";
                string sqlStr = "SELECT DTY_ID as id, DTY_NBR as deviceTypeNumber, DTY_NAM as name, DTY_PID as parentId,ACT_IDS as actionIds, NML_IMG_CTNT as normalImageContent, FAE_IMG_CTNT as firstAEImageContent, SAE_IMG_CTNT as secondAEImageContent, TAE_IMG_CTNT as thirdAEImageContent, UNM_IMG_CTNT as unNormalImageContent, DTY_DESC as description,LVL as level,DSPL_ORDR as displayOrder from DVC_TYP WHERE 1 = 1";
                using (SqlCeConnection connection = new SqlCeConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        connection.Open();
                        SqlCeDataAdapter command = new SqlCeDataAdapter(sqlStr, connection);
                        command.Fill(ds, "ds");
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }

                    //实体字段集合
                    deviceTypeDto entity = new deviceTypeDto();
                    FieldInfo[] Infos = entity.GetType().GetFields();

                    deviceTypeDto[] dtos = new deviceTypeDto[ds.Tables[0].Rows.Count];
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = ds.Tables[0].Rows[i];
                        deviceTypeDto dto = new deviceTypeDto();
                        foreach (DataColumn dcol in dr.Table.Columns)
                        {
                            foreach (FieldInfo info in Infos)
                            {
                                if (info.Name == dcol.ColumnName)
                                {
                                    if (dr[info.Name] == DBNull.Value) continue;
                                    info.SetValue(dto, dr[info.Name]);
                                }
                            }
                        }
                        dtos[i] = dto;
                    }
                    return dtos;

                }
            }
        }

        //public abstract partial class baseModelDto : object, System.ComponentModel.INotifyPropertyChanged
        public abstract partial class baseModelDto
        {
            private DateTime createTime;
            private string createBy;
            private DateTime lastUpdateTime;
            private string lastUpdateBy;
            private long versionId;

            public DateTime getCreateTime()
            {
                return createTime;
            }

            public void setCreateTime(DateTime createTime)
            {
                this.createTime = createTime;
            }

            public string getCreateBy()
            {
                return createBy;
            }

            public void setCreateBy(string createBy)
            {
                this.createBy = createBy;
            }

            public DateTime getLastUpdateTime()
            {
                return lastUpdateTime;
            }

            public void setLastUpdateTime(DateTime lastUpdateTime)
            {
                this.lastUpdateTime = lastUpdateTime;
            }

            public string getLastUpdateBy()
            {
                return lastUpdateBy;
            }

            public void setLastUpdateBy(string lastUpdateBy)
            {
                this.lastUpdateBy = lastUpdateBy;
            }

            public long getVersionId()
            {
                return versionId;
            }

            public void setVersionId(long versionId)
            {
                this.versionId = versionId;
            }
        }

        public partial class updateDeviceAndSectorLocationParam : baseModelDto
        {
            public long id;
            public long level;
            public string location;
            public string symbolType;
            public string sectorColor;

            public long getId()
            {
                return id;
            }

            public void setId(long id)
            {
                this.id = id;
            }

            public long getLevel()
            {
                return level;
            }

            public void setLevel(long level)
            {
                this.level = level;
            }

            public string getLocation()
            {
                return location;
            }

            public void setLocation(string location)
            {
                this.location = location;
            }

            public string getSymbolType()
            {
                return symbolType;
            }

            public void setSymbolType(string symbolType)
            {
                this.symbolType = symbolType;
            }

            public string getSectorColor()
            {
                return sectorColor;
            }

            public void setSectorColor(string sectorColor)
            {
                this.sectorColor = sectorColor;
            }
        }

        public partial class deviceTypeDto : baseModelDto
        {
            public long id;
            private string deviceTypeNumber;
            public string name;
            private long parentId;
            private string actionIds;
            private byte[] normalImageContent;
            private byte[] firstAEImageContent;
            private byte[] secondAEImageContent;
            private byte[] thirdAEImageContent;
            private byte[] unNormalImageContent;
            public string description;//desc;
            public long level;
            private int displayOrder;
        }

        public class searchTreeNodeResult : baseModelDto
        {
            public long id;
            public string name;
            public long parentId;
            public long level;
            public string symbolType;
            public long deviceTypeId;
            //public string deviceOrSectorLocation;
            public string sectorColor;
            public string DeviceLocation;
            public string SectorLocation;

            public string GetDeviceOrSectorLocation() {
                if (level == 4)
                {
                    return DeviceLocation;
                }
                else {
                    return SectorLocation;
                }

            }

            public void  SetDeviceOrSectorLocation(string strValue)
            {
                if (level == 4)
                {
                    DeviceLocation = strValue;
                }
                else
                {
                    SectorLocation = strValue;
                }
            }
        }

        public partial class sectorDto : baseModelDto
        {
            public long id;
            public string sectorNumber;
            public string name;
            public long parentId;
            public string symbolType;
            public string location;
            public string description;
            public bool isSegment;
            public long level;
            public int grade;
            public int sectorType;
            public long topPid;
            public string topSectorNum;
            public string sectorColor;
        }

        public partial class searchSectorForPeopleCountingResult : baseModelDto
        {
            public long id;
            public string name;
            public long parentId;
            public long level;
            public string symbolType;
            public string location;
            public string sectorColor;
            public int numberOfPeople;
        }

    }
}