using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using CCSTrace.CCS.Domain;
using CCSTrace.CCS.Function;
using CCSTrace.CCS.Object;
using NLog;

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

        public void Run(CCSEventRecord ccsEventRecord, OracleConnection conn, string traceConnection)
        {
            var sendMail = false;
            EOSEventRecord eosEventRecord = null;

            // Being Transaction
            OracleTransaction trx = conn.BeginTransaction();

            try
            {
                var fdrid = GetFdrid(conn, ccsEventRecord.Meter);

                if (fdrid != 0) //有饋線代號資料的案件才需比對同饋線上有無案件正在處理
                {
                    if (CcsMain.Instance.ContainProcessingFdr(fdrid))
                    {
                        CcsMain.Instance.AddWaitingCases(ccsEventRecord.CcsId, fdrid);
                        return;
                    }
                    else
                        CcsMain.Instance.AddProcessingFdr(fdrid); //將要處理的案件的饋線別加入處理中饋線集合
                }

                eosEventRecord = InitialEventRecord(conn, trx, ccsEventRecord);
                if (eosEventRecord != null)
                {
                    var mLeach = new Leach(conn, trx, GlobalVariable.TraceConnectionString); // 案件過濾與合併
                    Logger.Info("過濾案件...");
                    var returnStatus = mLeach.LeachCase(eosEventRecord);

                    switch (returnStatus)
                    {
                        case GlobalVariable.Success:
                            Logger.Info("案件過濾完畢....");
                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.Success);
                            break;

                        case GlobalVariable.NoMeter:
                            Logger.Info("案件過濾完畢,用戶無電號....");
                            eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;
                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);
                            break;

                        case GlobalVariable.SameCustomer:
                            Logger.Info("相同用戶重複來電....");
                            ProcessSameCustomer(conn, trx, ccsEventRecord, eosEventRecord);
                            break;

                        case GlobalVariable.NoSupplyElc:
                            Logger.Warn("該饋線未供電,無法作事故案件追蹤合併....");
                            eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;
                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);
                            break;

                        case GlobalVariable.NoSxfmr:
                            Logger.Warn("該案件無變壓器....");
                            eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;
                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);
                            break;

                        case GlobalVariable.CaseTypeChanged:
                            Logger.Info("變更案件狀態....");
                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.CaseTypeChanged);
                            break;

                        case GlobalVariable.TraceFailure:
                            Logger.Error("故障追蹤錯誤，以一般無電號案件方式受理....");
                            eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;
                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);
                            break;

                        default:
                            var traceCount = GetTraceCount(conn, trx, ccsEventRecord.CcsId);

                            if (traceCount < 2)
                            {
                                if (trx.Connection.State == ConnectionState.Open)
                                    trx.Rollback();

                                trx.Dispose();

                                if (returnStatus == GlobalVariable.CaseTransfer)
                                    Logger.Error("案件已移轉，此事故案件暫不被受理....");
                                else if (returnStatus == GlobalVariable.FailureByDb)
                                    Logger.Error("資料庫錯誤，此事故案件暫不被受理....");
                                else if (returnStatus == GlobalVariable.FdrLocked)
                                    Logger.Error("饋線被鎖定，此事故案件暫不被受理....");
                                else
                                    Logger.Error("發生非預期錯誤，此事故案件暫不被受理....");

                                if (traceCount == 0)
                                    UpdateTraceCount(conn, trx, ccsEventRecord.CcsId, true);
                                else
                                    UpdateTraceCount(conn, trx, ccsEventRecord.CcsId, false);
                            }
                            else
                            {
                                UpdateTraceCount(conn, trx, ccsEventRecord.CcsId, false);
                                if (returnStatus == GlobalVariable.TraceCounts3)
                                    eosEventRecord.Note = eosEventRecord.Note+ "(本案用戶供電資料追蹤失敗)";

                                Logger.Error("事故案件處理次數3次，仍無法正常處理，以一般無電號案件方式受理....");
                                eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;
                                Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);
                                sendMail = true;
                            }
                            break;
                    }
                }

                if (fdrid != 0)
                    CcsMain.Instance.RemoveProcessingFdr(fdrid); //將處理完的案件的饋線別從處理中饋線集合中移除
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message + ". CCSID = " + ccsEventRecord.CcsId);

                if (trx != null)
                {
                    if (trx.Connection.State == ConnectionState.Open)
                        trx.Rollback();

                    trx.Dispose();
                }

                if (eosEventRecord != null && eosEventRecord.FdrId!= 0)
                    CcsMain.Instance.RemoveProcessingFdr(eosEventRecord.FdrId); //將處理完的案件的饋線別從處理中饋線集合中移除
                //throw e;
            }
            finally
            {
                CcsMain.Instance.RemoveProcessingCases(ccsEventRecord.CcsId);
                // OnThreadFinish(new ThreadEndEvent(_connectionTpc));
            }

            if (sendMail)
            {
                var mailService = new MailService();
                mailService.InitialSmtpClient(conn);
                mailService.SendMail(ccsEventRecord.CcsId);
            }
        }

        public void DoProcessEventRecord(CCSEventRecord ccsEventRecord, OracleConnection conn)
        {
            var sendMail = false;
            EOSEventRecord eosEventRecord = null;

            // Being Transaction
            OracleTransaction trx = conn.BeginTransaction();

            try
            {
                var fdrid = GetFdrid(conn, ccsEventRecord.Meter);

                if (fdrid != 0) //有饋線代號資料的案件才需比對同饋線上有無案件正在處理
                {
                    if (CcsMain.Instance.ContainProcessingFdr(fdrid))
                    {
                        CcsMain.Instance.AddWaitingCases(ccsEventRecord.CcsId, fdrid);
                        return;
                    }
                    else
                        CcsMain.Instance.AddProcessingFdr(fdrid); //將要處理的案件的饋線別加入處理中饋線集合
                }

                eosEventRecord = InitialEventRecord(conn, trx, ccsEventRecord);
                if (eosEventRecord != null)
                {
                    var mLeach = new Leach(conn, trx, GlobalVariable.TraceConnectionString); // 案件過濾與合併
                    Logger.Info("過濾案件...");
                    var returnStatus = mLeach.LeachCase(eosEventRecord);

                    switch (returnStatus)
                    {
                        case GlobalVariable.Success:
                            Logger.Info("案件過濾完畢....");
                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.Success);
                            break;

                        case GlobalVariable.NoMeter:
                            Logger.Info("案件過濾完畢,用戶無電號....");
                            eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;
                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);
                            break;

                        case GlobalVariable.SameCustomer:
                            Logger.Info("相同用戶重複來電....");
                            ProcessSameCustomer(conn, trx, ccsEventRecord, eosEventRecord);
                            break;

                        case GlobalVariable.NoSupplyElc:
                            Logger.Warn("該饋線未供電,無法作事故案件追蹤合併....");
                            eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;
                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);
                            break;

                        case GlobalVariable.NoSxfmr:
                            Logger.Warn("該案件無變壓器....");
                            eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;
                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);
                            break;

                        case GlobalVariable.CaseTypeChanged:
                            Logger.Info("變更案件狀態....");
                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.CaseTypeChanged);
                            break;

                        case GlobalVariable.TraceFailure:
                            Logger.Error("故障追蹤錯誤，以一般無電號案件方式受理....");
                            eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;
                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);
                            break;

                        default:
                            var traceCount = GetTraceCount(conn, trx, ccsEventRecord.CcsId);

                            if (traceCount < 2)
                            {
                                if (trx.Connection.State == ConnectionState.Open)
                                    trx.Rollback();

                                trx.Dispose();

                                if (returnStatus == GlobalVariable.CaseTransfer)
                                    Logger.Error("案件已移轉，此事故案件暫不被受理....");
                                else if (returnStatus == GlobalVariable.FailureByDb)
                                    Logger.Error("資料庫錯誤，此事故案件暫不被受理....");
                                else if (returnStatus == GlobalVariable.FdrLocked)
                                    Logger.Error("饋線被鎖定，此事故案件暫不被受理....");
                                else
                                    Logger.Error("發生非預期錯誤，此事故案件暫不被受理....");

                                if (traceCount == 0)
                                    UpdateTraceCount(conn, trx, ccsEventRecord.CcsId, true);
                                else
                                    UpdateTraceCount(conn, trx, ccsEventRecord.CcsId, false);
                            }
                            else
                            {
                                UpdateTraceCount(conn, trx, ccsEventRecord.CcsId, false);
                                if (returnStatus == GlobalVariable.TraceCounts3)
                                    eosEventRecord.Note = eosEventRecord.Note + "(本案用戶供電資料追蹤失敗)";

                                Logger.Error("事故案件處理次數3次，仍無法正常處理，以一般無電號案件方式受理....");
                                eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;
                                Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);
                                sendMail = true;
                            }
                            break;
                    }
                }

                if (fdrid != 0)
                    CcsMain.Instance.RemoveProcessingFdr(fdrid); //將處理完的案件的饋線別從處理中饋線集合中移除
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message + ". CCSID = " + ccsEventRecord.CcsId);

                if (trx != null)
                {
                    if (trx.Connection.State == ConnectionState.Open)
                        trx.Rollback();

                    trx.Dispose();
                }

                if (eosEventRecord != null && eosEventRecord.FdrId != 0)
                    CcsMain.Instance.RemoveProcessingFdr(eosEventRecord.FdrId); //將處理完的案件的饋線別從處理中饋線集合中移除
                //throw e;
            }
            finally
            {
                CcsMain.Instance.RemoveProcessingCases(ccsEventRecord.CcsId);
                // OnThreadFinish(new ThreadEndEvent(_connectionTpc));
            }

            if (sendMail)
            {
                var mailService = new MailService();
                mailService.InitialSmtpClient(conn);
                mailService.SendMail(ccsEventRecord.CcsId);
            }
        }

        private void Process(OracleConnection conn, OracleTransaction trx,
            CCSEventRecord ccsEventRecord, EOSEventRecord eosEventRecord, int kind)
        {
            var finishEvent = new FinishEvent();

            try
            {
                if (finishEvent.Finish(conn, trx, eosEventRecord, kind))
                {
                    Logger.Info("OMS資料更新成功!");
                    if (UpdateCcsData(conn, trx, ccsEventRecord, eosEventRecord, (int)CCSCaseState.WaitForDespatch))
                    {
                        Logger.Info("CCS資料更新成功!");
                    }
                    else
                    {
                        if (trx.Connection.State == ConnectionState.Open)
                            trx.Rollback();

                        trx.Dispose();
                        Logger.Error("CCS資料更新程序發生錯誤!");
                        return;
                    }

                    // 須等全部動作都正確做完才可作commit的動作
                    trx.Commit();
                    trx.Dispose();
                    AlarmData.Instance.Alarm(conn, trx, eosEventRecord.AcceptNum, eosEventRecord.Dept);
                }
                else
                {
                    // 有錯誤需要RollBack
                    if (trx.Connection.State == ConnectionState.Open)
                        trx.Rollback();

                    trx.Dispose();
                    Logger.Error("OMS資料更新程序發生錯誤!");
                }
                Logger.Info("CCS 案件受理程序結束.");
            }
            catch (Exception e)
            {
                Logger.Warn(e, e.Message);
                throw;
            }
        }

        private bool UpdateCcsData(OracleConnection conn, OracleTransaction trx, CCSEventRecord ccsEventRecord, EOSEventRecord eosEventRecord, int caseStatus)
        {
            Logger.Info("進行CCS資料更新程序...");
            var ccsEventQuery = new CCSEventQuery();
            var status = caseStatus;
            // 設定CCSID
            ccsEventQuery.CcsId = ccsEventRecord.CcsId;
            // 設定受理時間
            ccsEventQuery.ChangeTime = ccsEventRecord.AcceptTime;
            // 設定用戶電號
            if (ccsEventRecord.Meter != null)
            {
                ccsEventQuery.Meter = ccsEventRecord.Meter;
            }
            // 如果該案件為子案件,需找出母案件的casestatus來insert eos.eventquery
            // 若母案件已派工,需一併紀錄預計復電時間等資訊
            if (eosEventRecord.ParentId!= 0)
            {
                status = GetRealCaseStatus(conn, trx, eosEventRecord.ParentId) == (int) CCSCaseState.WaitForSponsor
                    ? (int) CCSCaseState.EventDespatched
                    : (int) CCSCaseState.WaitForDespatch;
                if (status == (int) CCSCaseState.EventDespatched)
                {
                    SetDespatchInfo(conn, trx, eosEventRecord.ParentId, ccsEventQuery);
                }
            }
            // 若此案件為專案案件升級為母案件,則必須判斷原先案件是否已經派工
            if ((ccsEventRecord.ImportCase == GlobalVariable.IsImportCase) && eosEventRecord.IsDespatched)
            {
                status = (int) CCSCaseState.EventDespatched;
                SetDespatchInfo(conn, trx, eosEventRecord.CaseId, ccsEventQuery);
            }
            ccsEventQuery.CaseStatus = status;
            // 更新CCS.CCSEventQuery
            if (!ccsEventQuery.Update(conn, trx))
            {
                Logger.Error("更新ccs.eventquery失敗!");
                return false;
            }
            Logger.Info("更新ccs.eventquery成功!");
            var numberContrast = new NumberContrast();
            numberContrast.SetAcceptNum(eosEventRecord.AcceptNum);
            numberContrast.SetCaseId(eosEventRecord.CaseId);
            numberContrast.SetCcsid(ccsEventRecord.CcsId);
            // 更新CCS.Num_Contrast
            if (!numberContrast.Update(conn, trx))
            {
                Logger.Error("更新ccs.num_contrast失敗!");
                return false;
            }
            Logger.Info("更新ccs.num_contrast成功!");
            return true;
        }

        private EOSEventRecord InitialEventRecord(OracleConnection conn, OracleTransaction trx, CCSEventRecord ccsEventRecord)
        {
            var initialData = new InitialEventData(conn);
            var mContrast = new NumberContrast();

            EOSEventRecord eosEventRecord = new EOSEventRecord(initialData.GetNewCaseId(), conn, trx)
            {
                AcceptNum = initialData.GetNewAcceptNum()
            };

            mContrast.SetAcceptNum(eosEventRecord.AcceptNum);
            mContrast.SetCaseId(eosEventRecord.CaseId);
            mContrast.SetCcsid(ccsEventRecord.CcsId);

            if (!mContrast.Insert(conn, trx))
            {
                Logger.Error("初始化寫入CCS.Num_Contrast時發生錯誤！");

                if (trx.Connection.State == ConnectionState.Open)
                    trx.Rollback();

                return null;
            }

            // 設定用戶姓名
            eosEventRecord.Name = ccsEventRecord.CustomerName;

            if (!string.IsNullOrEmpty(ccsEventRecord.Meter))
            {
                // 需先找出部門代號,再找到該部門屬於哪一個巡修單位
                eosEventRecord.Meter = ccsEventRecord.Meter;
                var tmp = DeptContrast.Instance.getDept_Code(ccsEventRecord.Meter);

                eosEventRecord.Dept = tmp == -1 ? FindoutDeptByAddr(trx, ccsEventRecord) : tmp;

                eosEventRecord.FdrId = GetFdrid(conn, ccsEventRecord.Meter);
            }
            else
            {
                // 當無用戶電號時,所找出的部門別不需再去找出屬於哪一個巡修股(findoutDeptByAddr會直接找到相對應的巡修股)
                var dept = FindoutDeptByAddr(trx, ccsEventRecord);
                eosEventRecord.Dept = dept;
            }
            // 設定用戶電話號碼
            if (ccsEventRecord.CustomerTel != null)
            {
                eosEventRecord.Tel = ccsEventRecord.CustomerTel;
            }
            // 檢查並設定用戶地址
            var mAddr = ccsEventRecord.AddressCity + ccsEventRecord.AddressTown + ccsEventRecord.AddressRoad +
                        ccsEventRecord.AddressOther;

            if (mAddr.Trim().Length == 0)
            {
                Logger.Error("CCS 案件受理程序初始化失敗...地址為空白");

                if (trx.Connection.State == ConnectionState.Open)
                    trx.Rollback();

                return null;
            }

            eosEventRecord.Addr = mAddr;
            // 設定事故原因
            eosEventRecord.Brief = TransferEventBrief(ccsEventRecord.EventBrief);
            try
            {
                var encoding = new ASCIIEncoding();
                var source = encoding.GetBytes(eosEventRecord.Brief);

                if (source.Length > 24)
                {
                    var dest = new byte[24];
                    for (var i = 0; i < dest.Length; i++)
                    {
                        dest[i] = source[i];
                    }

                    var enc = Encoding.ASCII;
                    eosEventRecord.Brief = enc.GetString(dest);
                    source = dest = null;
                }
            }
            catch (Exception)
            {
                eosEventRecord.Brief = "其他";
            }

            if (ccsEventRecord.CcsId.StartsWith("A"))
                eosEventRecord.Log = "APP";
            else if (ccsEventRecord.CcsId.StartsWith("W"))
                eosEventRecord.Log = "WEB";
            else
                eosEventRecord.Log = "CCS";

            eosEventRecord.AcceptDate = ccsEventRecord.AcceptTime;

            // 設定用戶種類
            if (ccsEventRecord.ImportCase == GlobalVariable.IsImportCase)
            {
                eosEventRecord.ImportCase = GlobalVariable.IsImportCase;
                eosEventRecord.Level = GlobalVariable.ALevel; // default set A level
            }
            else
            {
                eosEventRecord.ImportCase = GlobalVariable.NotImportCase;
                eosEventRecord.Level = GlobalVariable.NoLevel;
            }

            // 檢查字串中有無特殊字元' ,若有則取代為"
            if (ccsEventRecord.Note != null)
                eosEventRecord.Note = ccsEventRecord.Note.Replace('\'', '\"');

            // 設定回覆姓名及電話
            if (ccsEventRecord.RecallName != null)
            {
                eosEventRecord.IsReCall = GlobalVariable.IsReCall;
                eosEventRecord.ReCallName = ccsEventRecord.RecallName.Replace('\'', '\"');
                eosEventRecord.ReCallTel = ccsEventRecord.RecallTel;
            }
            else
            {
                eosEventRecord.IsReCall = GlobalVariable.NotReCall;
            }

            Logger.Info("初始化EOS.EVENTRECORD成功!");
            return eosEventRecord;
        }

        //private bool initialCCSInfo(CCS.Object.CCSEventQuery m_EventQuery, CCS.Object.NumberContrast m_Contrast)
        //{
        //    checkCaseAlreadyExits(m_EventQuery);
        //    if (!m_EventQuery.Insert(_PLog, _ConnectionTPC, _Trx))
        //    {
        //        _PLog.Error("初始化寫入CCS.EventQuery時發生錯誤！");
        //        return false;
        //    }
        //    if (!m_Contrast.Insert(_ConnectionTPC, _Trx))
        //    {
        //        _PLog.Error("初始化寫入CCS.Num_Contrast時發生錯誤！");
        //        return false;
        //    }
        //    return true;
        //}

        private void CheckCaseAlreadyExits(OracleConnection conn, OracleTransaction trx, CCSEventQuery ccsEventQuery)
        {
            var sqlStmt = "select ccsid from ccs.eventquery where ccsid='" + ccsEventQuery.CcsId + "'";

            var command = new OracleCommand(sqlStmt, conn, trx);
            var reader = command.ExecuteReader();

            try
            {
                if (reader.Read())
                {
                    command.CommandText = "delete from ccs.eventquery where ccsid='" + ccsEventQuery.CcsId + "'";
                    command.ExecuteNonQuery();

                    command.CommandText = "delete from ccs.num_contrast where ccsid='" + ccsEventQuery.CcsId + "'";
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Warn(ex, "Problems occur during checking process: " + ex.Message);
            }
            finally
            {
                reader.Close();
                command.Dispose();
            }
        }

        private int FindoutDeptByAddr(OracleTransaction trx, CCSEventRecord ccsEventRecord)
        {
            return AddrContrast.Instance.FindDeptId(ccsEventRecord.AddressCity, ccsEventRecord.AddressTown, ccsEventRecord.AddressRoad);
        }

        private string TransferEventBrief(int code)
        {
            var result = GlobalVariable.CcsCodelist.GetContent(GlobalVariable.CcsEventBrief, code);

            //            int MaxLength = this.getFieldLength("EOS", "EVENTRECORD", "EVENTBRIEF");

            //            if ( Result.ToCharArray().Length > MaxLength )
            //                Result.ToCharArray

            return result;
        }

        private int TransferDept(string deptCode)
        {
            // 傳入部門的英文value(CONTENT),傳回相對應的keyid
            return GlobalVariable.CcsCodelist.GetKeyId(GlobalVariable.CcsDept, deptCode);
        }

        private int GetRealCaseStatus(OracleConnection conn, OracleTransaction trx, int parentId)
        {
            var status = (int) CCSCaseState.WaitForDespatch;

            var sqlStmt = "SELECT CASESTATUS FROM EOS.EVENTS WHERE CASEID = " + parentId;
            var command = new OracleCommand(sqlStmt, conn, trx);
            var reader = command.ExecuteReader();

            try
            {
                if (reader.Read())
                {
                    status = Convert.ToInt32(reader["CaseStatus"].ToString());
                }
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
                Logger.Warn("無法取得正確之母案件狀態.");
            }
            finally
            {
                reader.Close();
                command.Dispose();
            }
            return status;
        }

        private void SetDespatchInfo(OracleConnection conn, OracleTransaction trx, int caseId, CCSEventQuery ccsEventQry)
        {
            var sqlStmt = "SELECT DESPATCHTIME,ASSUMEFIXTIME,ASSUMEDTIME_NTH,DELAYTIME,REASON,EVENTLOCATION " +
                          "FROM EOS.EVENTDESPATCH WHERE CASEID=" + caseId;

            OracleCommand command = null;
            OracleDataReader reader = null;

            try
            {
                command = new OracleCommand(sqlStmt, conn, trx);
                reader = command.ExecuteReader();

                if (reader.Read())
                {
                    ccsEventQry.ChangeTime = Convert.ToDateTime(reader["despatchtime"]).ToString("yyy/MM/dd HH:mm:ss");
                    ccsEventQry.AssumedTime = Convert.ToDateTime(reader["assumefixtime"]).ToString("yyy/MM/dd HH:mm:ss");
                    ccsEventQry.AssumedTimeNth =
                        Convert.ToDateTime(reader["assumedtime_nth"]).ToString("yyy/MM/dd HH:mm:ss");
                    ccsEventQry.DelayTimes = Convert.ToInt32(reader["delaytime"].ToString());
                    ccsEventQry.Reason = reader["reason"].ToString();
                    ccsEventQry.EventLocation = reader["eventlocation"].ToString();
                }
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
                Logger.Warn("無法取得母案件派工資訊.");
            }
            finally
            {
                reader?.Close();
                command?.Dispose();
            }
        }

        private void ProcessSameCustomer(OracleConnection conn, OracleTransaction trx, CCSEventRecord ccsEventRecord, EOSEventRecord eosEventRec)
        {
            Logger.Info("處理相同用戶重複來電...");
            StringBuilder note = null;

            var sqlStmt = "SELECT NOTE FROM EOS.EVENTRECORD WHERE CASEID = " + eosEventRec.TmpCaseId;
            var command = new OracleCommand(sqlStmt, conn, trx);
            var reader = command.ExecuteReader();

            try
            {
                if (reader.Read())
                    note = new StringBuilder(reader["note"]?.ToString().Trim() ?? "");
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
            }
            finally
            {
                reader.Close();
                command.Dispose();
            }

            if (!note.ToString().EndsWith("|") && !note.ToString().Trim().Equals(""))
                note.Append(" | ");

            if (!string.IsNullOrEmpty(eosEventRec.Note))
                note.Append(eosEventRec.AcceptDate+ " " + eosEventRec.Note);
            else
                note.Append(eosEventRec.AcceptDate+ " 用戶再次報案");

            // 判斷重複來電的用戶最初成立的案件是否是由CCS受理
            var acceptByCcs = false;

            var sqlStmt1 = "SELECT COUNT(*) AS COUNT FROM CCS.NUM_CONTRAST C,CCS.EVENTQUERY Q WHERE C.CCSID=Q.CCSID AND CASEID="
                           + eosEventRec.TmpCaseId;
            var command1 = new OracleCommand(sqlStmt1, conn, trx);
            var reader1 = command1.ExecuteReader();

            try
            {
                if (reader1.Read())
                {
                    if (Convert.ToInt32(reader1["count"].ToString()) != 0)
                    {
                        acceptByCcs = true;
                    }
                }
            }
            catch (Exception e)
            {
                Logger.Warn(e, e.Message);
            }
            finally
            {
                reader1.Close();
                command1.Dispose();
            }

            string updCcsEventQuery = null;
            // 若先前案件由CCS受理,則直接從ccs.eventquery取出先前的案件狀態資料寫入
            if (acceptByCcs)
            {
                updCcsEventQuery = "UPDATE CCS.EVENTQUERY SET (CASESTATUS,CHANGETIME,HANDLINGSUMMARY,ASSUMEDTIME,ASSUMEDTIME_NTH,DELAYTIMES,REASON,EVENTLOCATION) = (select casestatus,changetime,handlingsummary,assumedtime,assumedtime_nth,delaytimes,reason,eventlocation from ccs.eventquery where ccsid=(select ccsid from ccs.num_contrast where caseid="
                                   + eosEventRec.TmpCaseId+ " AND ROWNUM<2)) WHERE CCSID='" + ccsEventRecord.CcsId + "'";
                // 若非由CCS受理(OMS,Web)則從EOS的table中尋找先前案件的狀態資料
            }
            else
            {
                string accepttime = null,
                    despatchtime = null,
                    sponsortime = null,
                    assumefixtime = null,
                    assumedtimeNth = null,
                    changetime = null,
                    handling = null,
                    reason = null,
                    eventlocation = null;
                int casestatus = 0, delaytime = 0;

                OracleCommand command2 = null;
                OracleDataReader reader2 = null;

                try
                {
                    var sqlStmt2 = "SELECT  1,TO_CHAR(R.ACCEPTTIME,'YYYY/MM/DD HH24:MI:SS') AS ACCEPTTIME," +
                                   "TO_CHAR(D.DESPATCHTIME,'YYYY/MM/DD HH24:MI:SS') AS DESPATCHTIME," +
                                   "TO_CHAR(S.RECONDITIONTIME,'YYYY/MM/DD HH24:MI:SS')SPONSORTIME," +
                                   "E.CASESTATUS,S.HANDLING,TO_CHAR(D.ASSUMEFIXTIME,'YYYY/MM/DD HH24:MI:SS') AS ASSUMEFIXTIME," +
                                   "TO_CHAR(D.ASSUMEDTIME_NTH,'YYYY/MM/DD HH24:MI:SS') AS ASSUMEDTIME_NTH," +
                                   "D.DELAYTIME,D.REASON,D.EVENTLOCATION " +
                                   "FROM EOS.EVENTRECORD R,EOS.EVENTS E,EOS.EVENTDESPATCH D,EOS.EVENTSPONSOR S " +
                                   "WHERE (R.CASEID=E.CASEID) AND (R.CASEID=D.CASEID(+)) AND (R.CASEID=S.CASEID(+)) AND R.CASEID=" + 
                                   eosEventRec.TmpCaseId + " UNION " +
                                   "SELECT  2,TO_CHAR(R.ACCEPTTIME,'YYYY/MM/DD HH24:MI:SS') AS ACCEPTTIME," +
                                   "TO_CHAR(D.DESPATCHTIME,'YYYY/MM/DD HH24:MI:SS') AS DESPATCHTIME," +
                                   "TO_CHAR(S.RECONDITIONTIME,'YYYY/MM/DD HH24:MI:SS')SPONSORTIME," +
                                   "E.CASESTATUS,S.HANDLING,TO_CHAR(D.ASSUMEFIXTIME,'YYYY/MM/DD HH24:MI:SS') AS ASSUMEFIXTIME," +
                                   "TO_CHAR(D.ASSUMEDTIME_NTH,'YYYY/MM/DD HH24:MI:SS') AS ASSUMEDTIME_NTH," +
                                   "D.DELAYTIME,D.REASON,D.EVENTLOCATION " +
                                   "FROM EOS.EVENTRECORD R,EOS.EVENTS E,EOS.EVENTDESPATCH D,EOS.EVENTSPONSOR S " +
                                   "WHERE (R.CASEID=E.CASEID) AND (R.CASEID=D.CASEID(+)) AND (R.CASEID=S.CASEID(+)) AND R.CASEID= " +
                                   "(SELECT PARENTID FROM EOS.MERGECASE WHERE CASEID="
                                   + eosEventRec.TmpCaseId+ ")";

                    command2 = new OracleCommand(sqlStmt2, conn, trx);
                    reader2 = command2.ExecuteReader();

                    while (reader2.Read())
                    {
                        accepttime = reader2["ACCEPTTIME"].ToString();
                        despatchtime = reader2["DESPATCHTIME"].ToString();
                        sponsortime = reader2["SPONSORTIME"].ToString();
                        assumefixtime = reader2["ASSUMEFIXTIME"]?.ToString() ?? "";
                        assumedtimeNth = reader2["ASSUMEDTIME_NTH"]?.ToString() ?? "";
                        handling = reader2["HANDLING"]?.ToString() ?? "";
                        reason = reader2["REASON"]?.ToString() ?? "";
                        casestatus = Convert.ToInt32(reader2["CASESTATUS"].ToString());
                        delaytime = reader2["DELAYTIME"].ToString().Length == 0
                            ? 0
                            : Convert.ToInt32(reader2["DELAYTIME"].ToString());
                        eventlocation = reader2["EVENTLOCATION"]?.ToString() ?? "";
                    }

                    if (accepttime != null)
                    {
                        changetime = accepttime;
                    }
                    if (!string.IsNullOrEmpty(despatchtime))
                    {
                        changetime = despatchtime;
                    }
                    if (!string.IsNullOrEmpty(sponsortime))
                    {
                        changetime = sponsortime;
                    }
                    updCcsEventQuery = "UPDATE CCS.EVENTQUERY SET CASESTATUS=" + casestatus + ",changetime=to_date('" +
                                       changetime
                                       + "','YYYY/MM/DD HH24:MI:SS'),handlingsummary='" + handling +
                                       "',assumedtime=to_date('" + assumefixtime
                                       + "','YYYY/MM/DD HH24:MI:SS'),assumedtime_nth=to_date('" + assumedtimeNth +
                                       "','YYYY/MM/DD HH24:MI:SS'),delaytimes="
                                       + delaytime + ",REASON='" + reason + "',EVENTLOCATION='" + eventlocation +
                                       "' WHERE CCSID='" + ccsEventRecord.CcsId + "'";
                }
                catch (Exception e)
                {
                    Logger.Error(e, e.Message);
                }
                finally
                {
                    reader2?.Close();
                    command2?.Dispose();
                }
            }

            var updEosEventRecord = "UPDATE EOS.EVENTRECORD SET NOTE = '" + TrimToLimit(note.ToString(), 127)
                                    + "' WHERE CASEID = " + eosEventRec.TmpCaseId;
            var updCcsNumContrast = "UPDATE CCS.NUM_CONTRAST SET(acceptnum,caseid) = (SELECT acceptnum," +
                                    eosEventRec.TmpCaseId                                    
                                    + " FROM EOS.EVENTRECORD WHERE CASEID = " + eosEventRec.TmpCaseId +
                                    ") where ccsid='" + ccsEventRecord.CcsId + "'";

            var exeCommand = new OracleCommand(updCcsEventQuery, conn, trx);
            // 將用戶重複來電案件資料寫回CCS.CCSEventQuery
            if (exeCommand.ExecuteNonQuery() <= 0)
            {
                exeCommand.Dispose();

                if (trx.Connection.State == ConnectionState.Open)
                    trx.Rollback();

                trx.Dispose();
                Logger.Error("Fail in write back to ccs.eventquery");
                return;
            }

            exeCommand.CommandText = updEosEventRecord;
            // 將CCS案件備註資料Append到EOS.EOSEventRecord
            if (exeCommand.ExecuteNonQuery() <= 0)
            {
                exeCommand.Dispose();

                if (trx.Connection.State == ConnectionState.Open)
                    trx.Rollback();

                trx.Dispose();
                Logger.Error("Fail in update eos.eventquery");
                return;
            }

            exeCommand.CommandText = updCcsNumContrast;
            // 寫入ccsid <=> caseid 到CCS.Num_Contrast
            if (exeCommand.ExecuteNonQuery() <= 0)
            {
                exeCommand.Dispose();
                if (trx.Connection.State == ConnectionState.Open)
                    trx.Rollback();

                trx.Dispose();
                Logger.Error("Fail in insert into ccs.num_contrast");
                return;
            }

            exeCommand.Dispose();
            trx.Commit();
            trx.Dispose();
            Logger.Info("CCS 案件受理程序結束");
        }

        private string TrimToLimit(string msg, int maxLen)
        {
            try
            {
                if (msg.Length <= 1)
                {
                    return "";
                }
                if (msg.Length <= maxLen)
                {
                    return msg;
                }
                msg = msg.Substring(msg.IndexOf("|", StringComparison.Ordinal) + 1);
                return TrimToLimit(msg, maxLen);
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
                return "";
            }
        }

        private int GetFieldLength(OracleConnection conn, OracleTransaction trx,  string owner, string tableName, string fieldName)
        {
            var sqlStmt = "SELECT DATA_LENGTH FROM ALL_TAB_COLUMNS WHERE OWNER = '" + owner + "' AND TABLE_NAME = '" +
                          tableName + "' AND COLUMN_NAME = '" + fieldName + "'";
            var command = new OracleCommand(sqlStmt, conn, trx);
            var reader = command.ExecuteReader();
            var maxLength = -1;

            try
            {
                if (reader.Read())
                    maxLength = Convert.ToInt32(reader["DATA_LENGTH"].ToString());
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
            }
            finally
            {
                reader.Close();
                command.Dispose();
            }

            return maxLength;
        }

        private int GetFdrid(OracleConnection conn, string meter)
        {
            var sqlStmt = "SELECT TRAN_CORD FROM BASEDB.METER WHERE METR_NUMB = '" + meter + "'";
            var command = new OracleCommand(sqlStmt, conn);
            var reader = command.ExecuteReader();
            var tpclid = "";
            var fdrid = 0;
            try
            {
                if (reader.Read())
                    tpclid = reader["TRAN_CORD"].ToString();

                if (tpclid.Length == 0)
                    return fdrid;

                if (tpclid.Length > 11)
                {
                    var group = tpclid.Substring(tpclid.Length - 3);
                    tpclid = tpclid.Substring(0, 11).Trim();
                    sqlStmt =
                        "SELECT FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = 115 AND UFID = " +
                        "(SELECT UFID FROM BASEDB.SXFMR WHERE OWNERTPCLID = '" +
                        tpclid
                        + "' AND GROUP1='" + group + "' AND ROWNUM < 2)";
                }
                else
                {
                    sqlStmt =
                        "SELECT FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = 115 AND UFID = " +
                        "(SELECT UFID FROM BASEDB.SXFMR WHERE OWNERTPCLID = '" +
                        tpclid
                        + "' AND ROWNUM < 2)";
                }

                command.CommandText = sqlStmt;
                reader = command.ExecuteReader();

                if (reader.Read())
                    fdrid = Convert.ToInt32(reader["FDR1"].ToString());
                else
                {
                    sqlStmt =
                        "SELECT FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = 107 AND UFID = " +
                        "(SELECT UFID FROM BASEDB.HICUSTOMER WHERE OWNERTPCLID = '" +
                        tpclid + "' AND ROWNUM < 2)";

                    command.CommandText = sqlStmt;
                    reader = command.ExecuteReader();

                    if (reader.Read())
                        fdrid = Convert.ToInt32(reader["FDR1"].ToString());
                }
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
            }
            finally
            {
                reader.Close();
                command.Dispose();
            }

            return fdrid;
        }

        private int GetTraceCount(OracleConnection conn, OracleTransaction trx, string ccsid)
        {
            var sqlStmt = "SELECT TRACECOUNT FROM CCS.TRACECOUNTS WHERE CCSID = '" + ccsid + "'";
            var command = new OracleCommand(sqlStmt, conn, trx);
            var reader = command.ExecuteReader();
            var count = 0;

            try
            {
                if (reader.Read())
                    count = Convert.ToInt32(reader["TRACECOUNT"].ToString());
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
            }
            finally
            {
                reader.Close();
                command.Dispose();
            }

            return count;
        }

        private bool UpdateTraceCount(OracleConnection conn, OracleTransaction trx, string ccsid, bool isNew)
        {
            string sqlStmt;

            if (isNew)
                sqlStmt = "INSERT INTO CCS.TRACECOUNTS (CCSID,TRACECOUNT) VALUES ('" + ccsid + "',1)";
            else
                sqlStmt = "UPDATE CCS.TRACECOUNTS SET TRACECOUNT = TRACECOUNT + 1 WHERE CCSID = '" + ccsid + "'";
            var command = new OracleCommand(sqlStmt, conn, trx);

            try
            {
                var result = command.ExecuteNonQuery();

                if (result == 1)
                    return true;
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
            }
            finally
            {
                command.Dispose();
            }
            return false;
        }
    }
}