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 DeptService_SqliteImpl : IDeptService
    {
        public int clearDept()
        {
            int iRetval = -1;
            try
            {
                //sys_perosn_ext
                string sQry = "delete from sys_dept";
                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                iRetval = aCommand.ExecuteNonQuery();

                sQry = "update sqlite_sequence SET seq = 0 where name = 'sys_dept'";//自增长ID为0";
                aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                iRetval = aCommand.ExecuteNonQuery();
                aCommand.Dispose();

                iRetval = 0;
            }
            catch
            {
                iRetval = -1;
            }

            return iRetval;
        }

        /// <summary>
        /// 添加部门
        /// </summary>
        public int AddDept(Dept dept)
        {
            int re = -1;
            SQLiteTransaction tx = null;
            try
            {
                
                tx = _SqliteHelper.sqliteConnection.BeginTransaction();
                string sQry =
                    "INSERT INTO sys_dept (ID,PID,SIMPLENAME,FULLNAME,PIDS,VERSION,NUM,TIPS)" +
                    "values (@var0,@var1,@var2,@var3,@var4,@var5,@var6,@var7)";
                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.Add(new SQLiteParameter("@var4", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var5", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var6", DbType.Int32));
                aCommand.Parameters.Add(new SQLiteParameter("@var7", DbType.String));

                aCommand.Parameters[0].Value = dept.Id;
                aCommand.Parameters[1].Value = dept.Pid;
                aCommand.Parameters[2].Value = dept.Simplename;
                aCommand.Parameters[3].Value = dept.Fullname;
                aCommand.Parameters[4].Value = dept.Pids;
                aCommand.Parameters[5].Value = dept.Version;
                aCommand.Parameters[6].Value = dept.Num;
                aCommand.Parameters[7].Value = dept.Tips;

                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>DataTable</returns>
        public DataTable GetDepts()
        {
            String strSql = "select ID, SIMPLENAME, FULLNAME, TIPS, NUM,PID,PIDS from sys_dept ";
            
            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;

        }
      
    }
}