using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using CameraDb.model;


namespace CameraDb.oracle
{
    public class DeviceOra
    {
        private const string TABLE_NAME = "PG_DEVICE";

        public Device getDeviceByAssetCode(String assetCode)
        {
            try
            {
                String sql = "select * from " + TABLE_NAME + " where ASSETCODE='" + assetCode + "'";
                using (OracleDataReader rdr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringOrderDistributedTransaction,
                    CommandType.Text, sql))
                {
                    while (rdr.Read())
                    {
                        model.Device device = GetDeviceByOracleDataReader(rdr);
                        if (device != null)
                        {
                            return device;
                        }
                    }
                }

                return null;

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public Device getDeviceByIp(String ip) {
            try
            {
                String sql = "select * from " + TABLE_NAME + " where IP='" + ip + "'";
                using (OracleDataReader rdr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringOrderDistributedTransaction,
                    CommandType.Text, sql))
                {
                    while (rdr.Read())
                    {
                        model.Device device = GetDeviceByOracleDataReader(rdr);
                        if (device != null)
                        {
                            return device;
                        }
                    }
                }

                return null;

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public Device getDengByDev(Device _device)
        {
            try
            {
                string partition = _device.PARTITION;
                string devType = "普通荧光灯";
                string positionStr = _device.POSITION.Substring(0, 3);

                String sql = "select * from " + TABLE_NAME + " where partition = '" + partition +
                    "' and devtype = '" + devType + "' and position like '%" + positionStr + "%'";

                using (OracleDataReader rdr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringOrderDistributedTransaction,
                    CommandType.Text, sql))
                {
                    while (rdr.Read())
                    {
                        model.Device device = GetDeviceByOracleDataReader(rdr);
                        if (device != null)
                        {
                            return device;
                        }
                    }
                }

                return null;

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public List<Device> getCameraDevList()
        {
            try
            {
                using (OracleConnection conn = new OracleConnection(OracleHelper.ConnectionStringOrderDistributedTransaction))
                {
                    //String sql = "select * from " + TABLE_NAME + " where DEVTYPE='球机' or DEVTYPE = '枪机' and ACTIVE = 1";
                    //测试用
                    //String sql = "select * from " + TABLE_NAME + " where ip = '10.0.62.1'";
                    //String sql = "select * from " + TABLE_NAME + " where ip = '172.17.1.4'";
                    String sql = "select * from " + TABLE_NAME + " where mome = '移动'";
                    DataTable dt = OracleHelper.ExecuteDataset(conn, CommandType.Text, sql, null).Tables[0];
                    List<Device> deviceList = new List<Device>();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (dt.Rows.Count > 0)
                        {
                            Device device = new Device();
                            if (null != dt.Rows[i]["ID"])
                            {
                                device.ID = Convert.ToInt32(dt.Rows[i]["ID"].ToString());
                            }
                            if (null != dt.Rows[i]["ASSETCODE"])
                            {
                                device.ASSETCODE = dt.Rows[i]["ASSETCODE"].ToString();
                            }
                            if (null != dt.Rows[i]["ASSETNAME"])
                            {
                                device.ASSETNAME = dt.Rows[i]["ASSETNAME"].ToString();
                            }
                            if (null != dt.Rows[i]["DEVCODE"])
                            {
                                device.DEVCODE = dt.Rows[i]["DEVCODE"].ToString();
                            }
                            if (null != dt.Rows[i]["DEVCODE"])
                            {
                                device.DEVCODE = dt.Rows[i]["DEVCODE"].ToString();
                            }
                            if (null != dt.Rows[i]["DEVTYPE"])
                            {
                                device.DEVTYPE = dt.Rows[i]["DEVTYPE"].ToString();
                            }
                            if (null != dt.Rows[i]["IP"])
                            {
                                device.IP = dt.Rows[i]["IP"].ToString();
                            }
                            if (null != dt.Rows[i]["PARTITION"])
                            {
                                device.PARTITION = dt.Rows[i]["PARTITION"].ToString();
                            }
                            if (null != dt.Rows[i]["POSITION"])
                            {
                                device.POSITION = dt.Rows[i]["POSITION"].ToString();
                            }
                            if (null != dt.Rows[i]["ROAD"])
                            {
                                device.ROAD = dt.Rows[i]["ROAD"].ToString();
                            }

                            device.MLUSERID = -1;

                            deviceList.Add(device);
                        }
                    }
                    return deviceList;
                }
            }
            catch (Exception e)
            {
                String ss = e.Message;
                return null;
            }
        }

        private model.Device GetDeviceByOracleDataReader(OracleDataReader rdr)
        {
            string[] colsName;
            int[] colsOrdinal;
            const int colsCount = 15;

            model.Device device = new model.Device();

            colsName = new string[colsCount]{"ID", "ACTIVE", "ASSETBARCODE","ASSETCODE","ASSETNAME",
            "DEVCODE", "DEVTYPE", "IP","ISOPEN","MODBUS","MOME","PARTITION","PIPEGALLERY","POSITION","ROAD"};

            colsOrdinal = new int[colsCount];

            if (GetColumnsOrdinal(rdr, colsName, colsOrdinal) < 0)
            {
                throw (new Exception("Error"));
            }

            for (int i = 0; i < colsCount; i++)
            {
                if (colsOrdinal[i] < 0 || rdr.IsDBNull(colsOrdinal[i]))
                {
                    continue;
                }
                switch (i)
                {
                    case 0:
                        device.ID = rdr.GetInt32(colsOrdinal[i]);
                        break;
                    case 1:
                        device.ACTIVE = rdr.GetInt32(colsOrdinal[i]);
                        break;
                    case 2:
                        device.ASSETBARCODE = rdr.GetString(colsOrdinal[i]);
                        break;
                    case 3:
                        device.ASSETCODE = rdr.GetString(colsOrdinal[i]);
                        break;
                    case 4:
                        device.ASSETNAME = rdr.GetString(colsOrdinal[i]);
                        break;
                    case 5:
                        device.DEVCODE = rdr.GetString(colsOrdinal[i]);
                        break;
                    case 6:
                        device.DEVTYPE = rdr.GetString(colsOrdinal[i]);
                        break;
                    case 7:
                        device.IP = rdr.GetString(colsOrdinal[i]);
                        break;
                    case 8:
                        device.ISOPEN = rdr.GetInt32(colsOrdinal[i]);
                        break;
                    case 9:
                        device.MODBUS = rdr.GetString(colsOrdinal[i]);
                        break;
                    case 10:
                        device.MOME = rdr.GetString(colsOrdinal[i]);
                        break;
                    case 11:
                        device.PARTITION = rdr.GetString(colsOrdinal[i]);
                        break;
                    case 12:
                        device.PIPEGALLERY = rdr.GetString(colsOrdinal[i]);
                        break;
                    case 13:
                        device.POSITION = rdr.GetString(colsOrdinal[i]);
                        break;
                    case 14:
                        device.ROAD = rdr.GetString(colsOrdinal[i]);
                        break;
                }
            }

            return device;
        }

        private int GetColumnsOrdinal(OracleDataReader rdr, string[] colsName, int[] colsOrdinal)
        {
            for (int i = 0; i < colsName.Length; i++)
            {
                colsOrdinal[i] = rdr.GetOrdinal(colsName[i]);
            }

            return 0;
        }

    }
}