using IOM_cs.irisDb.model;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Reflection;
using System.Windows.Forms;

namespace IOM_cs.irisDb.service.sqliteImpl
{
    public class DeviceService_SqliteImpl : IDeviceService
    {
        /// <summary>
        /// 新增设备
        /// </summary>
        public int AddDevice(Device device)
        {
            int re = -1;
            SQLiteTransaction tx = null;
            try
            {
                
                tx = _SqliteHelper.sqliteConnection.BeginTransaction();
                string sQry =
                    "INSERT INTO sys_device (device_code,ip,port,remarks)" +
                    "values (@var0,@var1,@var2,@var3)";
                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                aCommand.CommandTimeout = 0;
                //绑定事务
                aCommand.Transaction = tx;
                
                aCommand.Parameters.Add(new SQLiteParameter("@var0", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var1", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var2", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var3", DbType.String));

                aCommand.Parameters[0].Value = device.DeviceCode;
                aCommand.Parameters[1].Value = device.Ip;
                aCommand.Parameters[2].Value = device.Port;
                aCommand.Parameters[3].Value = device.Remarks;

                aCommand.ExecuteNonQuery();
                tx.Commit();
                aCommand.Dispose();

                re = 0;
            }
            catch (SQLiteException ex)
            {
                tx.Rollback();
                re = -1;
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return -1;
                }
                MessageBox.Show("EXCEPTION: " + ex.Message);
            }
            finally
            {
                tx.Dispose();
            }
            return re;
        }

        /// <summary>
        /// 编辑
        /// </summary>
        /// <returns></returns>
        public int EditDevice(Int64 id, String deviceCode, String ip, String port, String remarks)
        {
            int iRetval = 0;
            try
            {
                // sys_person表
                string sQry = "UPDATE sys_device set device_code='" + deviceCode + "',ip='" + ip +
                    "',port='" + port + "', remarks = '" + remarks + "' WHERE id=" + id;

                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                aCommand.CommandTimeout = 0;
                aCommand.ExecuteNonQuery();
                aCommand.Dispose();
            }
            catch (SQLiteException exSqlDb)
            {
                iRetval = exSqlDb.ErrorCode;
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return -1;
                }
                MessageBox.Show("Update EXCEPTION: " + exSqlDb.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Update EXCEPTION: " + ex.Message);
                iRetval = -1;
            }

            return iRetval;
        }

       
        /// <summary>
        /// 获取同ip设备
        /// </summary>
        /// <returns></returns>
        public int GetDeviceByIp(string ip)
        {
            int count = 0;
            try
            {
                SQLiteDataReader aReader = null;

                string sQry = "SELECT COUNT(*) FROM sys_device where ip = '" + ip + "'";
                SQLiteCommand cmd = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                cmd.CommandTimeout = 0;
                aReader = cmd.ExecuteReader(CommandBehavior.Default);

                if (aReader.Read())
                {
                    count = aReader.GetInt32(0);
                }
                aReader.Close();
            }
            catch (SQLiteException s)
            {
                MessageBox.Show(s.Message.ToString());
            }

            return count;

        }

        /// <summary>
        /// 获取设备详情
        /// </summary>
        /// <returns></returns>
        public Device GetDetailById(Int64 id)
        {
            Device device = new Device();
            try
            {
                SQLiteDataReader aReader = null;

                string sQry = "SELECT id,device_code,ip,port,remakrs FROM sys_device where id = '" + id + "'";
                SQLiteCommand cmd = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                cmd.CommandTimeout = 0;
                aReader = cmd.ExecuteReader(CommandBehavior.Default);

                if (aReader.Read())
                {
                    if (!aReader.IsDBNull(0)) device.Id = aReader.GetInt32(0);
                    if (!aReader.IsDBNull(1)) device.DeviceCode = aReader.GetString(1);
                    if (!aReader.IsDBNull(2)) device.Ip = aReader.GetString(2);
                    if (!aReader.IsDBNull(3)) device.Port = aReader.GetString(3);
                    if (!aReader.IsDBNull(4)) device.Remarks = aReader.GetString(4);
                }
                aReader.Close();
            }
            catch (SQLiteException s)
            {
                MessageBox.Show(s.Message.ToString());
            }

            return device;

        }
       

        /// <summary>
        /// 删除
        /// </summary>
        public int Delete(Int64 id)
        {
            int iRetval = 0;
            try
            {
                // sys_person表
                string sQry = "DELETE FROM sys_device  WHERE id=" + id;
                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                aCommand.CommandTimeout = 0;
                aCommand.ExecuteNonQuery();
                aCommand.Dispose();

            }
            catch (SqlException exSqlDb)
            {
                iRetval = exSqlDb.ErrorCode;
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return -1;
                }
                MessageBox.Show("Delete EXCEPTION: " + exSqlDb.Message);
            }
            catch (Exception ex)
            {
                iRetval = -1;
                MessageBox.Show("Delete EXCEPTION: " + ex.Message);
            }

            return iRetval;
        }


        /// <summary>
        /// 分批获取设备
        /// </summary>
        /// <param name="baseNum"></param>每次获取人数
        /// <param name="i"></param>本次获取第 baseNum*(i-1) 到第 baseNum*i 条 
        /// <returns></returns>
        public DataTable GetDevices(int baseNum, int i, string searchStr)
        {

            String strSql = "select id, device_code, ip, port, remarks from sys_device ";
            if (searchStr != "")
                strSql += searchStr;

            strSql += "limit " + baseNum * (i - 1) + "," + baseNum;

            SQLiteCommand aCommand = new SQLiteCommand(strSql, _SqliteHelper.sqliteConnection);
            aCommand.CommandTimeout = 0;

            SQLiteDataAdapter adapter = new SQLiteDataAdapter();
            adapter.SelectCommand = aCommand;
            DataTable dt = new DataTable();

            try
            {
                adapter.Fill(dt);                
            }
            catch (SQLiteException exSqlDb)
            {
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return dt;
                }
                MessageBox.Show("GetUsersEXCEPTION: " + exSqlDb.Message);
            }
            catch (Exception ex)
            {
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return dt;
                }
                MessageBox.Show("GetUsersEXCEPTION: " + ex.Message);
            }

            return dt;
        }

        /// <summary>
        /// 获取总数
        /// </summary>
        /// <returns></returns>
        public int GetCount(string searchStr)
        {
            int count = 0;
            try
            {
                SQLiteDataReader aReader = null;

                string sQry = "SELECT COUNT(*) FROM sys_device " + searchStr;

                SQLiteCommand cmd = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                cmd.CommandTimeout = 0;
                aReader = cmd.ExecuteReader(CommandBehavior.Default);

                if (aReader.Read())
                {
                    count = aReader.GetInt32(0);
                }
                aReader.Close();
            }
            catch (SQLiteException s)
            {
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return count;
                }
                MessageBox.Show(s.Message.ToString());
            }

            return count;

        }
      
    }
}