using System;

using System.Data.OracleClient;
using CCSTrace.CCS.Domain;
using CCSTrace.CCS.Object;
using NLog;

namespace CCSTrace.CCS.Function
{
    public class Leach
    {
        private static readonly Logger Logger = LogManager.GetCurrentClassLogger();

        private readonly MergeCase _mergeCase = new MergeCase();
        private readonly OracleConnection _connectionTpc;
        private readonly OracleTransaction _transaction;
        private readonly string _traceConnectionString;

        public Leach(OracleConnection conn, OracleTransaction trx, string traceConnection)
        {
            _connectionTpc = conn;
            _transaction = trx;
            _traceConnectionString = traceConnection;
        }

        public int LeachCase(EOSEventRecord mNewRec)
        {
            switch (SameCase(mNewRec))
            {
                case GlobalVariable.Success:
                    return Judge(mNewRec, false);

                case GlobalVariable.CaseTypeChanged:
                    return Judge(mNewRec, true);

                case GlobalVariable.SameCustomer:
                    return GlobalVariable.SameCustomer;

                case GlobalVariable.FailureByDb:
                    return GlobalVariable.FailureByDb;

                case GlobalVariable.CaseTransfer:
                    return GlobalVariable.CaseTransfer;

                default:
                    return GlobalVariable.FailureByDb;
            }
        }

        public EventAI.Equipment GetOldEquipment()
        {
            return _mergeCase.GetOldEquipment();
        }

        private int SameCase(EOSEventRecord mNewRec)
        {
            string sqlStmt = "SELECT R.ACCEPTNUM,R.CASEID as CASEID,R.IMPORTCASE as IMPORTCASE,E.CASETYPE as CASETYPE FROM EOS.EVENTRECORD R,"
                           + "EOS.EVENTS E WHERE ";
            int mResult = -1;
            int importCase = -1;
            int tmpCaseId = 0;
            int caseType = -1;
            Logger.Info("利用電號或地址檢查是否有已成立之案件.");
            // 檢查資料庫中此電號或地址是否已成立案件

            if (mNewRec.Meter.Trim().Length != 0)
            {
                sqlStmt = sqlStmt + "R.CUSTOMERMETER = '" + mNewRec.Meter+ "' AND E.CASEID = R.CASEID AND E.CASESTATUS <= "
                        + CCSCaseState.WaitForSponsor;
            }
            else if (mNewRec.Addr.Trim().Length != 0)
            {
                sqlStmt = sqlStmt + "R.CUSTOMERADDR = '" + mNewRec.Addr+ "' AND E.CASEID = R.CASEID AND E.CASESTATUS <= "
                        + CCSCaseState.WaitForSponsor;
            }

            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
            OracleDataReader reader = command.ExecuteReader();

            try
            {
                if (reader.Read())
                {
                    importCase = Convert.ToInt32(reader["IMPORTCASE"].ToString());
                    tmpCaseId = Convert.ToInt32(reader["CASEID"].ToString());
                    caseType = Convert.ToInt32(reader["CASETYPE"].ToString());
                    mNewRec.TmpCaseId = tmpCaseId;
                    Logger.Info("案件已成立:" + reader["ACCEPTNUM"].ToString() + " " + tmpCaseId + " " + caseType);
                }
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
                return GlobalVariable.FailureByDb;
            }
            finally
            {
                reader.Close();
                command.Dispose();
            }

            // 非同一用戶重複來電
            if (tmpCaseId == 0)
            {
                return GlobalVariable.Success;
            }
            switch (caseType)
            {
                case GlobalVariable.OriginalCase:
                case GlobalVariable.ParentCase:
                    sqlStmt = "SELECT TO_CHAR(CLOSETIME,'YYYY/MM/DD HH24:MI:SS') as CLOSETIME,COUNT(*) as COUNT FROM EOS.EVENTFACILITY " + "WHERE CASEID = "
                            + tmpCaseId + " GROUP BY CLOSETIME ORDER BY CLOSETIME DESC";
                    break;

                case GlobalVariable.ChildCase:
                    sqlStmt = "SELECT TO_CHAR(CLOSETIME,'YYYY/MM/DD HH24:MI:SS') as CLOSETIME,COUNT(*) as COUNT FROM EOS.EVENTFACILITY "
                            + "WHERE CASEID = (SELECT PARENTID FROM EOS.MERGECASE WHERE CASEID = " + tmpCaseId + ") GROUP BY CLOSETIME "
                            + "ORDER BY CLOSETIME DESC";
                    break;

                default:
                    Logger.Error("無法辨別案件種類.");
                    return GlobalVariable.FailureByDb;
            }
            Logger.Info("檢查該案件有無切開關紀錄.");

            try
            {
                command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
                reader = command.ExecuteReader();

                if (reader.Read())
                {
                    // 有開關尚未恢復
                    if (reader["CLOSETIME"] == null)
                    {
                        Logger.Info("有切開關且尚未恢復.");
                        if ((mNewRec.ImportCase== importCase) || (importCase == GlobalVariable.IsImportCase))
                        {
                            mResult = AddCount(tmpCaseId);
                        }
                        else
                        {
                            mResult = GlobalVariable.CaseTypeChanged;
                        }
                    }
                    else
                    {
                        // 開關都已恢復(同用戶須重新受理)
                        Logger.Info("有切開關且已經恢復.");
                        int caseId;

                        // 找出案件編號需以母案件編號或該案件編號去找casestatus
                        if (tmpCaseId == 0)
                        {
                            caseId = mNewRec.CaseId;
                        }
                        else
                        {
                            caseId = tmpCaseId;
                        }
                        // 判斷該案件或母案件是否移轉作後續處理
                        if (GetCastStatus(caseId) == (int) CCSCaseState.EventTrasnfer)
                        {
                            return GlobalVariable.CaseTransfer;
                        }
                        else
                        {
                            mResult = GlobalVariable.Success;
                        }
                    }
                }
                else
                {
                    // 未切過開關
                    Logger.Info("無切開關紀錄.");
                    if ((mNewRec.ImportCase== importCase) || (importCase == GlobalVariable.IsImportCase))
                    {
                        mResult = AddCount(tmpCaseId);
                    }
                    else
                    {
                        int caseId;
                        // 找出案件編號需以母案件編號或該案件編號去找casestatus
                        if (tmpCaseId == 0)
                        {
                            caseId = mNewRec.CaseId;
                        }
                        else
                        {
                            caseId = tmpCaseId;

                            // 判斷該案件或母案件是否移轉作後續處理
                        }
                        if (GetCastStatus(caseId) == (int) CCSCaseState.EventTrasnfer)
                        {
                            return GlobalVariable.CaseTransfer;
                        }
                        else
                        {
                            mResult = GlobalVariable.CaseTypeChanged;
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
                return GlobalVariable.FailureByDb;
            }
            finally
            {
                reader.Close();
                command.Dispose();
            }
            return mResult;
        }

        private int AddCount(int mCaseId)
        {
            var sqlStmt = "UPDATE EOS.EVENTS SET COUNT = COUNT + 1 WHERE CASEID = " + mCaseId;
            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

            try
            {
                return command.ExecuteNonQuery() > 0 ? GlobalVariable.SameCustomer : GlobalVariable.FailureByDb;
            }
            catch (Exception e)
            {
                Logger.Warn(e, e.Message);
                return GlobalVariable.FailureByDb;
            }
            finally
            {
                command.Dispose();
            }
        }

        private int GetCastStatus(int caseId)
        {
            int status = 0;

            var sqlStmt = "SELECT CASESTATUS FROM CCS.EVENTQUERY WHERE CCSID IN (SELECT CCSID FROM CCS.NUM_CONTRAST " + "WHERE CASEID = " + caseId + ")";
            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
            OracleDataReader reader = null;

            try
            {
                reader = command.ExecuteReader();

                if (reader.Read())
                    status = Convert.ToInt32(reader["CASESTATUS"].ToString());
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                command.Dispose();
            }
            return status;
        }

        private int Judge(EOSEventRecord mNewRec, bool changeCaseType)
        {
            int mResult = GlobalVariable.FailureByDb;

            if ((mNewRec.Meter.Trim().Length == 0))
            {
                return GlobalVariable.NoMeter;
            }

            /* 已知損壞設備(ONLY FOR 饋線全停才知道損壞設備的FSC) */
            try
            {
                if (mNewRec.Fsc!= 0)
                {
                    mNewRec.LocateEquipment.Fsc = (GlobalVariable.Breaker);
                    mNewRec.LocateEquipment.Ufid = (mNewRec.Ufid);
                    Logger.Info("饋線全停:" + mNewRec.Fsc+ ", " + mNewRec.Ufid);
                    _mergeCase.Merge(mNewRec, _connectionTpc, _transaction, _traceConnectionString);
                    return GlobalVariable.Success;
                }
            }
            catch (EventAI.TraceLoopException e)
            {
                Logger.Warn(e, e.Message);
                return GlobalVariable.TraceCounts3;
            }
            catch (TraceException e)
            {
                Logger.Warn(e, e.Message);
                return GlobalVariable.TraceFailure;
            }
            catch (Exception ex)
            {
                Logger.Error(ex, ex.Message);
                return GlobalVariable.FailureByDb;
            }

            try
            {
                Logger.Info("定位損壞設備!");
                int result = GetEquipment(mNewRec);
                Logger.Info("FSC:" + mNewRec.Fsc+ ", Ufid:" + mNewRec.Ufid+ ", Tpclid:" + mNewRec.Tpclid+ ", FeederID:" + mNewRec.FdrId);

                switch (result)
                { // get fsc,ufid,fdrid of sxfmr by meter
                    case GlobalVariable.Success:
                        if (mNewRec.FdrId== 0)
                        {
                            mResult = GlobalVariable.NoSupplyElc;
                        }
                        else
                        {
                            // 需在未併案前作土木設備設定
                            GetLocateEquipment(mNewRec);

                            if (mNewRec.Brief.Length != 0 && mNewRec.Brief.Substring(0, 1).Equals("A"))
                            { // A類事故原因,做追蹤合併
                                Logger.Info("<A類案件>");
                                _mergeCase.Merge(mNewRec, _connectionTpc, _transaction, _traceConnectionString);
                            }
                            else
                            {
                                Logger.Info("<非A類案件>");
                            }

                            if (changeCaseType)
                            {
                                mResult = GlobalVariable.CaseTypeChanged;
                            }
                            else
                            {
                                mResult = GlobalVariable.Success;
                            }
                        }
                        break;

                    case GlobalVariable.NoSxfmr:
                        mResult = GlobalVariable.NoSxfmr;
                        break;

                    case GlobalVariable.FailureByDb:
                        mResult = GlobalVariable.FailureByDb;
                        break;
                }
            }
            catch (EventAI.TraceLoopException e)
            {
                Logger.Warn(e, e.Message);
                return GlobalVariable.TraceCounts3;
            }
            catch (TraceException e)
            {
                Logger.Warn(e, e.Message);
                mResult = GlobalVariable.TraceFailure;
            }
            catch (Exception ex1)
            {
                Logger.Error(ex1, ex1.Message);
                mResult = GlobalVariable.FailureByDb;
            }
            return mResult;
        }

        private int GetEquipment(EOSEventRecord mNewRec)
        {
            string temp;
            string mOwnerTpclid;
            string mGroup;
            string sqlStmt = "select tran_cord from basedb.meter where metr_numb = '" + mNewRec.Meter+ "'";
            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
            OracleDataReader reader = command.ExecuteReader();

            try
            {
                if (reader.Read())
                {
                    temp = reader["tran_cord"].ToString().Trim();
                    if ((temp.Length == 9) || (temp.Length == 11)) /* 架空變壓器 */
                    {
                        mOwnerTpclid = temp;
                        reader.Close();
                        command.Dispose();
                        mNewRec.Tpclid = mOwnerTpclid;
                        return GetSxfmr(mOwnerTpclid, null, mNewRec); /* Find the Sxfmr */
                    }
                    else
                    {
                        mOwnerTpclid = temp.Substring(0, 11).Trim(); /* 地下變壓器 */
                        mGroup = temp.Substring(14);
                        reader.Close();
                        command.Dispose();
                        mNewRec.Tpclid = mOwnerTpclid + " " + mGroup;
                        return GetSxfmr(mOwnerTpclid, mGroup, mNewRec);
                        /* Find the Sxfmr */
                    }
                }
                else
                {
                    return GlobalVariable.NoSxfmr;
                }
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
                return GlobalVariable.FailureByDb;
            }
            finally
            {
                reader.Close();
                command.Dispose();
            }
        }

        private int GetSxfmr(string mTpclid, string mGroup, EOSEventRecord mNewRec)
        {
            string sqlStmt;
            int mStatus = GlobalVariable.FailureByDb;

            if (mGroup == null)
            {
                sqlStmt = "SELECT UFID,FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = " + GlobalVariable.Sxfmr + " AND UFID IN "
                        + "(SELECT UFID FROM BASEDB.SXFMR WHERE OWNERTPCLID = '" + mTpclid + "')";
            }
            else
            {
                sqlStmt = "SELECT UFID,FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = " + GlobalVariable.Sxfmr + " AND UFID IN "
                        + "(SELECT UFID FROM BASEDB.SXFMR WHERE OWNERTPCLID = '" + mTpclid + "' AND GROUP1 = '" + mGroup + "')";
            }

            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
            OracleDataReader reader = command.ExecuteReader();

            try
            {
                if (reader.Read())
                {
                    mNewRec.Fsc = GlobalVariable.Sxfmr;
                    mNewRec.Ufid = Convert.ToInt32(reader["ufid"].ToString());
                    mNewRec.FdrId = Convert.ToInt32(reader["fdr1"].ToString());
                    mStatus = GlobalVariable.Success;
                }
                else
                    mStatus = GetHicustomer(mTpclid, mGroup, mNewRec);
            }
            catch (Exception ex)
            {
                Logger.Error(ex, ex.Message);
            }
            finally
            {
                reader.Close();
                command.Dispose();
            }
            return mStatus;
        }

        private int GetHicustomer(string mTpclid, string mGroup, EOSEventRecord mNewRec)
        {
            string sqlStmt;

            if (mGroup == null)
            {
                sqlStmt = "SELECT UFID,FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = " + GlobalVariable.Hicustomer + " AND UFID IN "
                        + "(SELECT UFID FROM BASEDB.HICUSTOMER WHERE OWNERTPCLID = '" + mTpclid + "')";
            }
            else
            {
                sqlStmt = "SELECT UFID,FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = " + GlobalVariable.Hicustomer + " AND UFID IN "
                        + "(SELECT UFID FROM BASEDB.HICUSTOMER WHERE OWNERTPCLID = '" + mTpclid + "' AND GROUP1 = '" + mGroup + "')";
            }

            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
            OracleDataReader reader = command.ExecuteReader();

            try
            {
                if (reader.Read())
                {
                    mNewRec.Fsc = GlobalVariable.Hicustomer;
                    mNewRec.Ufid = Convert.ToInt32(reader["ufid"].ToString());
                    mNewRec.FdrId = Convert.ToInt32(reader["fdr1"].ToString());
                    return GlobalVariable.Success;
                }
                else
                    return GlobalVariable.NoSxfmr;
            }
            catch (Exception ex)
            {
                Logger.Error(ex, ex.Message);
                return GlobalVariable.FailureByDb;
            }
            finally
            {
                reader.Close();
                command.Dispose();
            }
        }

        private void GetLocateEquipment(EOSEventRecord mNewRec)
        {
            var sqlStmt = "SELECT OWNERFSC as FSC,OWNERUFID as UFID,OWNERTPCLID as TPCLID FROM ";
            switch (mNewRec.Fsc)
            {
                case GlobalVariable.Sxfmr:
                    sqlStmt = sqlStmt + "BASEDB.SXFMR WHERE UFID = " + mNewRec.Ufid;
                    break;

                case GlobalVariable.Hicustomer:
                    sqlStmt = sqlStmt + "BASEDB.HICUSTOMER WHERE UFID = " + mNewRec.Ufid;
                    break;
            }

            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
            OracleDataReader reader = command.ExecuteReader();

            try
            {
                if (reader.Read())
                {
                    mNewRec.LocateEquipment.Fsc = (Convert.ToInt32(reader["FSC"].ToString()));
                    mNewRec.LocateEquipment.Ufid = (Convert.ToInt32(reader["UFID"].ToString()));
                    mNewRec.LocateEquipment.Tpclid = (reader["TPCLID"].ToString());
                }
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
            }
            finally
            {
                reader.Close();
                command.Dispose();
            }
        }
    }
}