Merge remote-tracking branch 'origin/usestp'

ulysseskao

2016-05-05 540014a7702a9bae7a3b9c00098671a132e869e8

Merge remote-tracking branch 'origin/usestp'

 CCSTrace.Tests/App_Data/DBConfig.xml

@@ -1,6 +1,6 @@
<?xml version="1.0" encoding="big5"?>

<CCS>
  <DBSetting DataSource="NNTPC" UserId="basedb" Password="BASEDB000" ConnectionCount="3" ShowError="false" />
  <DBSetting DataSource="NNTPC" UserId="basedb" Password="BASEDB000" ConnectionCount="10" ShowError="false" />
  <ThreadSetting maxThreadSize="5" minThreadSize="1" />
</CCS>

 CCSTrace/CCS/CCSMain.cs

@@ -1,13 +1,9 @@
using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Data.OracleClient;

using System.IO;

using System.Linq;

using System.Runtime.CompilerServices;

using System.Threading;

using System.Web;

using System.Xml;

using Amib.Threading;

using CCSTrace.CCS.Domain;

@@ -24,31 +20,31 @@
        private static readonly Logger Logger = LogManager.GetCurrentClassLogger();

        private static CcsMain _instance;



        private readonly int _connectionCount = 1;

        private OracleConnection _mainConn = null;



        private SmartThreadPool _mainjobThreadPool = null;

        private readonly object _syncDbQueue = new object();

        private readonly Queue<OracleConnection> _dbQueue = new Queue<OracleConnection>();

        private readonly List<string> _processingCases = new List<string>();

        private readonly List<KeyValuePair<string, int>> _waitingCases = new List<KeyValuePair<string, int>>();//Record the case when the case's FDR processed by another

        //Record the case when the case's FDR processed by another

        private readonly SortedSet<int> _processingFdr = new SortedSet<int>();

        // private readonly object _syncDbQueue = new object();

        // private readonly Queue<OracleConnection> _dbQueue = new Queue<OracleConnection>();

        private readonly object _syncProcessingCases = new object();

        private readonly object _syncProcessingFdrs = new object();

        private readonly object _syncWaitingCases = new object();



        private readonly List<KeyValuePair<string, int>> _waitingCases = new List<KeyValuePair<string, int>>();

 


        public CcsMain()

        {

            _mainjobThreadPool = new SmartThreadPool();

            // Startup();

        }

        // private readonly int _connectionCount = 1;

        private OracleConnection _mainConn;



        private SmartThreadPool _mainjobThreadPool;



        private IWorkItemsGroup _wigFinsh;

        private IWorkItemsGroup _wigNextJobs;

        private IWorkItemsGroup _wigProcess;





        public string AppDataPath { get; set; }



        public SortedSet<int> ProcessingFdr { get; } = new SortedSet<int>();



        public static CcsMain Instance => _instance ?? (_instance = new CcsMain());



        public List<string> ProcessingCases => _processingCases;



        public List<KeyValuePair<string, int>> WaitingCases => _waitingCases;



        public void Startup()

        {

@@ -56,8 +52,8 @@


            try

            {

                ReadXml();

                Logger.Info("CCSMain ReadXml Complete.");

                ReadConfigXml();

                Logger.Info("CCSMain ReadConfigXml Complete.");



                if (_mainConn == null)

                    _mainConn = CreateConnection();

@@ -66,10 +62,12 @@
                GlobalVariable.CcsCodelist = new CcsCodelist(_mainConn);

                GlobalVariable.EosCodelist = new EosCodelist(_mainConn);



                /*

                for (var i = 0; i < _connectionCount; i++)

                {

                    lock (_syncDbQueue) _dbQueue.Enqueue(CreateConnection());

                }

                */

            }

            catch (Exception e)

            {

@@ -79,7 +77,34 @@


            AddrContrast.Instance.Initialize(_mainConn);

            AlarmData.Instance.Initialize(_mainConn);

            DeptContrast.Instance.Initialize(_mainConn);



            var stpStartInfo = new STPStartInfo

            {

                StartSuspended = true,

                IdleTimeout = GlobalVariable.IdleTimeout*1000,

                MaxWorkerThreads = GlobalVariable.MaxThreadSize,

                MinWorkerThreads = GlobalVariable.MinThreadSize

            };

            _mainjobThreadPool = new SmartThreadPool(stpStartInfo);

            var wigStartInfo = new WIGStartInfo

            {

                FillStateWithArgs = true

            };

            _wigProcess = _mainjobThreadPool.CreateWorkItemsGroup(1, wigStartInfo);

            _wigFinsh = _mainjobThreadPool.CreateWorkItemsGroup(1, wigStartInfo);

            _wigNextJobs = _mainjobThreadPool.CreateWorkItemsGroup(1, wigStartInfo);



            _mainjobThreadPool.Start();

            _wigNextJobs.Start();

            _wigProcess.Start();

            _wigFinsh.Start();



            Logger.Info("Create Thread Pool.");



            // doNextJob

            _wigNextJobs.QueueWorkItem(DoNextJobs);

            /*

            CCSEventRecord ccsEventRecord = GetWaitRecord(_mainConn);



            if (ccsEventRecord != null)

@@ -88,15 +113,7 @@
                lock (_syncDbQueue) conn = _dbQueue.Dequeue();

                ProcessCase(ccsEventRecord, conn);

            }



            STPStartInfo stpStartInfo = new STPStartInfo

            {

                IdleTimeout = GlobalVariable.IdleTimeout*1000,

                MaxWorkerThreads = GlobalVariable.MaxThreadSize,

                MinWorkerThreads = GlobalVariable.MinThreadSize

            };

            _mainjobThreadPool = new SmartThreadPool(stpStartInfo);

            Logger.Info("Create Thread Pool.");

            */

        }



        public void Shutdown()

@@ -108,90 +125,65 @@
            GC.WaitForPendingFinalizers();

        }



        public void AcceptEvent2(CCSEventRecord ccsEventRecord)

        private void DoNextJobs()

        {

            // WorkItemCallback workItemCallback = new WorkItemCallback(this.DoEventWork);

            _mainjobThreadPool.QueueWorkItem(DoEventWork, ccsEventRecord);

        }



        private void DoEventWork(CCSEventRecord state)

        {

            OracleConnection conn;

            lock (_syncDbQueue) conn = _dbQueue.Dequeue();

            Logger.Debug("Enter DoNextJobs");

            var conn = CreateConnection();



            try

            {

                AcceptEvent(state);

                var ccsEventRecord = GetWaitRecord(conn);



                if (ccsEventRecord != null)

                {

                    _wigProcess.QueueWorkItem(ProcessCase, ccsEventRecord);

                }

            }

            finally

            {

                lock (_syncDbQueue) _dbQueue.Enqueue(conn);

                conn?.Close();

                Logger.Debug("Leave DoNextJobs");

            }





        }



        public void AcceptEvent(CCSEventRecord ccsEventRecord)

        {

            /*

            WIGStartInfo wigStartInfo = new WIGStartInfo()

            _mainjobThreadPool.QueueWorkItem(AcceptWebServiceEvent, ccsEventRecord);

        }



        private void AcceptWebServiceEvent(CCSEventRecord ccsEventRecord)

        {

            Logger.Debug("Enter AcceptWebServiceEvent");

            var conn = CreateConnection();



            try

            {

                FillStateWithArgs = true,

            };

            _wig1 = _smartThreadPool.CreateWorkItemsGroup((int)spinCon1.Value, wigStartInfo);

            _wig2 = _smartThreadPool.CreateWorkItemsGroup((int)spinCon2.Value, wigStartInfo);



            */



            int i = 0;

            int reConnectCount = 1;



            while (i <= reConnectCount)

                if (InsertCCSEventRecord(ccsEventRecord, conn))

                {

                    _wigNextJobs.QueueWorkItem(DoNextJobs);

                }

            }

            catch (Exception e)

            {

                try

                {

                    if (InsertCCSEventRecord(ccsEventRecord, _mainConn))

                    {

                        if (_dbQueue.Count > 0)

                        {

                            //將EVETNQUERY的案件狀態改為開始處理

                            CCSEventRecord waitCcsEventRecord = GetWaitRecord(_mainConn);



                            if (waitCcsEventRecord != null)

                            {

                                OracleConnection conn;

                                lock (_syncDbQueue) conn = _dbQueue.Dequeue();

                                ProcessCase(waitCcsEventRecord, conn);

                            }

                        }



                        break;

                    }

                }

                catch (Exception e)

                {

                    Logger.Error(e, e.Message);

                    if (_mainConn.State == ConnectionState.Closed)

                    {

                        i++;

                        if (i > reConnectCount)

                            throw;

                    }

                    else

                        throw;

                }

                Logger.Error(e, e.Message);

            }

            finally

            {

                conn?.Close();

                Logger.Debug("Leave AcceptWebServiceEvent");

            }

        }



        private void ReadXml()



        private void ReadConfigXml()

        {

            XmlReader reader = null;



            try

            {

                string file = Path.Combine(AppDataPath, DbConfigFilename);

                var file = Path.Combine(AppDataPath, DbConfigFilename);

                // 建立 XML 讀取器

                XmlReaderSettings settings = new XmlReaderSettings

                var settings = new XmlReaderSettings

                {

                    IgnoreComments = true, // 不處理註解

                    IgnoreWhitespace = true, // 跳過空白

@@ -205,7 +197,7 @@
                    switch (reader.NodeType)

                    {

                        case XmlNodeType.Element:

                            string localName = reader.LocalName; // 取得標籤名稱

                            var localName = reader.LocalName; // 取得標籤名稱



                            // Step 3: 讀取 FileInfo 標籤的屬性

                            if (localName.Equals("DBSetting"))

@@ -214,7 +206,7 @@
                                    $"Data source={reader["DataSource"]};User Id={reader["UserId"]};Password={reader["Password"]};";

                                GlobalVariable.TraceConnectionString =

                                    $"{reader["UserId"]}/{reader["Password"]}@{reader["DataSource"]}";

                                string token = reader["ConnectionCount"];

                                var token = reader["ConnectionCount"];

                                if (token != null)

                                {

                                    GlobalVariable.MaxConnectionCount = int.Parse(token);

@@ -224,9 +216,10 @@
                                {

                                    GlobalVariable.ShowError = bool.Parse(token);

                                }

                            } else if (localName.Equals("ThreadSetting"))

                            }

                            else if (localName.Equals("ThreadSetting"))

                            {

                                string token = reader["maxThreadSize"];

                                var token = reader["maxThreadSize"];

                                if (token != null)

                                {

                                    GlobalVariable.MaxThreadSize = int.Parse(token);

@@ -252,22 +245,19 @@


        private OracleConnection CreateConnection()

        {

            OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder(GlobalVariable.ConnectionString)

            var builder = new OracleConnectionStringBuilder(GlobalVariable.ConnectionString)

            {

                MaxPoolSize = GlobalVariable.MaxConnectionCount,

                MinPoolSize = 1,

                Pooling = true

            };

            string connectstring = builder.ToString();

            var connectstring = builder.ToString();



            OracleConnection dbConn = new OracleConnection(connectstring);



            var dbConn = new OracleConnection(connectstring);

            dbConn.Open();



            return dbConn;

        }



        [MethodImpl(MethodImplOptions.Synchronized)]

        private bool InsertCCSEventRecord(CCSEventRecord ccsEventRecord, OracleConnection conn)

        {

            OracleTransaction transaction = null;

@@ -330,80 +320,76 @@
            return true;

        }



        private delegate void WorkerThreadHandler();



        private void ProcessCase(CCSEventRecord ccsEventRecord, OracleConnection conn)

        private void ProcessCase(CCSEventRecord ccsEventRecord)

        {

            Logger.Debug("Enter ProcessCase");

            OracleConnection conn = CreateConnection();

            try

            {

                if (conn.State == ConnectionState.Closed)

                    conn.Open();



                ProcessEvent processEvent = new ProcessEvent(ccsEventRecord, conn, GlobalVariable.TraceConnectionString);

                processEvent.ThreadFinish += ThreadEndEventProcess;

                var processEvent = new ProcessEvent();

                processEvent.Run(ccsEventRecord, conn, GlobalVariable.TraceConnectionString);



                ThreadStart threadStart = processEvent.Run;

                Thread thread = new Thread(threadStart);

                thread.Start();

                _wigNextJobs.QueueWorkItem(DoNextJobs);

            }

            catch

            catch (Exception e)

            {

                lock (_syncDbQueue) _dbQueue.Enqueue(conn);

                Logger.Error(e, e.Message);

            }

            finally

            {

                conn?.Close();

            }

            Logger.Debug("Leave ProcessCase");

        }



        private void ThreadEndEventProcess(object sender, ThreadEndEvent e)

        {

            //將EVETNQUERY的案件狀態改為開始處理

            CCSEventRecord waitCcsEventRecord = GetWaitRecord(e.GetConnection());



            try

            {

                if (waitCcsEventRecord != null)

                    ProcessCase(waitCcsEventRecord, e.GetConnection());

            }

            finally 
            {

                lock (_syncDbQueue) _dbQueue.Enqueue(e.GetConnection());

            }

        }



        [MethodImpl(MethodImplOptions.Synchronized)]

        // [MethodImpl(MethodImplOptions.Synchronized)]

        private CCSEventRecord GetWaitRecord(OracleConnection conn)

        {

            string processCcsid = "";

            string ccsid = "";

            var processCcsid = "";

            var ccsid = "";

            CCSEventRecord ccsEventRecord = null;



            foreach (var obj in _waitingCases)

            lock (_syncWaitingCases)

            {

                var ccsId = obj.Key;

                var fdrid = obj.Value;



                if (ProcessingFdr.Contains(fdrid)) //該條饋線仍有案件在處理中

                    processCcsid = processCcsid + "'" + ccsid + "',";

                else

                foreach (var obj in _waitingCases)

                {

                    ccsid = ccsId;

                    _waitingCases.Remove(obj);

                    break;

                    var ccsId = obj.Key;

                    var fdrid = obj.Value;



                    if (ContainProcessingFdr(fdrid)) //該條饋線仍有案件在處理中

                        processCcsid = processCcsid + "'" + ccsid + "',";

                    else

                    {

                        ccsid = ccsId;

                        _waitingCases.Remove(obj);

                        break;

                    }

                }

            }



            if (ccsid.Length == 0)  //沒有因同饋線而在等候中的案件

            if (ccsid.Length == 0) //沒有因同饋線而在等候中的案件

            {

                var sqlStmt = "SELECT Q.CCSID AS CCSID FROM CCS.EVENTQUERY Q,CCS.EVENTRECORD R WHERE Q.CASESTATUS IN (" + 
                    (int) CCSCaseState.EventInitial + "," + 
                var sqlStmt =

                    "SELECT Q.CCSID AS CCSID FROM CCS.EVENTQUERY Q,CCS.EVENTRECORD R WHERE Q.CASESTATUS IN (" +

                    (int) CCSCaseState.EventInitial + "," +

                    (int) CCSCaseState.EventProcess + ")";



                processCcsid = _processingCases.Aggregate(processCcsid, (current, item) => current + "'" + item + "',");

                lock (_processingCases)

                {

                    processCcsid = _processingCases.Aggregate(processCcsid,

                        (current, item) => current + "'" + item + "',");

                }



                if (processCcsid.Length != 0)

                    sqlStmt = sqlStmt + " AND Q.CCSID NOT IN (" + processCcsid.Substring(0, processCcsid.Length - 1) + ")";

                    sqlStmt = sqlStmt + " AND Q.CCSID NOT IN (" + processCcsid.Substring(0, processCcsid.Length - 1) +

                              ")";



                sqlStmt = sqlStmt + " AND Q.CCSID = R.CCSID AND ROWNUM < 2 ORDER BY Q.CHANGETIME";

                OracleCommand command = new OracleCommand(sqlStmt, conn);

                OracleDataReader reader = command.ExecuteReader();

                var command = new OracleCommand(sqlStmt, conn);

                var reader = command.ExecuteReader();



                try

                {

@@ -426,14 +412,14 @@


            if (ccsEventRecord != null)

            {

                CCSEventQuery ccsEventQuery = new CCSEventQuery

                var ccsEventQuery = new CCSEventQuery

                {

                    CcsId = ccsEventRecord.CcsId,

                    CaseStatus = (int) CCSCaseState.EventProcess

                };

                //先將EVETNQUERY的案件狀態改為開始處理



                OracleTransaction transaction = conn.BeginTransaction();

                var transaction = conn.BeginTransaction();



                try

                {

@@ -441,7 +427,10 @@
                    {

                        Logger.Info("更新EVENTQUERY的案件狀態為處理中.(CCSID = " + ccsEventRecord.CcsId + ")");

                        transaction.Commit();

                        _processingCases.Add(ccsEventRecord.CcsId);

                        lock (_syncProcessingCases)

                        {

                            _processingCases.Add(ccsEventRecord.CcsId);

                        }

                    }

                    else

                    {

@@ -463,5 +452,45 @@


            return ccsEventRecord;

        }



        public void AddWaitingCases(string ccsId, int fdrid)

        {

            lock (_syncWaitingCases)

            {

                _waitingCases.Add(new KeyValuePair<string, int>(ccsId, fdrid));

            }

        }



        public void AddProcessingFdr(int fdrid)

        {

            lock (_syncProcessingFdrs)

            {

                _processingFdr.Add(fdrid);

            }

        }



        public bool ContainProcessingFdr(int fdrid)

        {

            lock (_syncProcessingFdrs)

            {

                return _processingFdr.Contains(fdrid);

            }

        }



        public void RemoveProcessingFdr(int fdrId)

        {

            lock (_syncProcessingFdrs)

            {

                _processingFdr.Remove(fdrId);

            }

        }



        public void RemoveProcessingCases(string ccsId)

        {

            lock (_syncProcessingCases)

            {

                _processingCases.Remove(ccsId);

            }

        }

    }

}

 CCSTrace/CCS/FinishEvent.cs

@@ -1,6 +1,7 @@
using System;

using System.Data.OracleClient;

using CCSTrace.CCS.Domain;

using CCSTrace.CCS.Object;

using NLog;



namespace CCSTrace.CCS

@@ -8,28 +9,16 @@
    public class FinishEvent

    {

        private static readonly Logger Logger = LogManager.GetCurrentClassLogger();

        private int _originCaseStatus;



        private bool _upLevel;

        private int _originCaseStatus = 0;

        private Object.EOSEventRecord _eosEventRecord = null;

        private OracleConnection _connectionTpc;

        private OracleTransaction _transaction;



        public FinishEvent()

        public bool Finish(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord, int mKind)

        {

        }



        public bool Finish(int mKind, Object.EOSEventRecord eosEventRecord, OracleConnection conn, OracleTransaction trx)

        {

            _eosEventRecord = eosEventRecord;

            _connectionTpc = conn;

            _transaction = trx;



            Logger.Info("進行OMS資料更新程序...");

            if (eosEventRecord.ParentId == 0)

            {

                // Original Case

                if (!(ProcessSingalCase(eosEventRecord)))

                if (!ProcessSingalCase(conn, trx, eosEventRecord))

                {

                    return false;

                }

@@ -38,7 +27,7 @@
            else

            {

                // Child Case

                if (!(ProcessMergeCase(eosEventRecord)))

                if (!ProcessMergeCase(conn, trx, eosEventRecord))

                {

                    return false;

                }

@@ -46,7 +35,7 @@
            }



            // inert into eos.eventrecord && eos.eventrecord_ex

            OracleCommand command = new OracleCommand(eosEventRecord.GetInsertSqlStmt(), _connectionTpc, _transaction);

            var command = new OracleCommand(eosEventRecord.GetInsertSqlStmt(), conn, trx);

            OracleCommand command1 = null;



            try

@@ -72,7 +61,7 @@
                    /* 當饋線全停時不需注意影響虛擬用戶，影響用戶資料會由實際切開關產生 */

                    if (eosEventRecord.Fsc != GlobalVariable.Breaker)

                    {

                        if (!SetVisualCustomer(eosEventRecord))

                        if (!SetVisualCustomer(conn, trx, eosEventRecord))

                        {

                            Logger.Error("新增影響用戶資料錯誤.");

                            return false;

@@ -80,16 +69,16 @@
                        Logger.Info("新增影響用戶成功!");

                    }



                    _upLevel = false; /* 判斷交辦案件是否升級為母案件 */

                    var upLevel = false;



                    if (!(UpdateImportCase(eosEventRecord)))

                    if (!UpdateImportCase(conn, trx, eosEventRecord, upLevel))

                    {

                        Logger.Error("處理案件等級變更時發生錯誤.");

                        return false;

                    }

                    Logger.Info("處理案件等級變更成功!");



                    if (!(UpdateRecuseOrder(eosEventRecord)))

                    if (!UpdateRecuseOrder(conn, trx, eosEventRecord, upLevel))

                    {

                        Logger.Error("新增搶修順序資料時發生錯誤.");

                        return false;

@@ -98,7 +87,7 @@


                    if (mKind == GlobalVariable.CaseTypeChanged)

                    {

                        if (!DeleteOldCase(eosEventRecord))

                        if (!DeleteOldCase(conn, trx, eosEventRecord))

                        {

                            Logger.Error("案件轉換發生錯誤");

                            return false;

@@ -106,7 +95,7 @@
                        Logger.Info("案件轉換成功!");

                    }



                    if (!SaveCustomerTel(eosEventRecord))

                    if (!SaveCustomerTel(conn, trx, eosEventRecord))

                    {

                        Logger.Error("無法將用戶電話回存回資料庫.");

                        return false;

@@ -115,11 +104,12 @@
                }

                else if (mKind == GlobalVariable.NoMeter)

                {

                    var sqlStmt = "INSERT INTO EOS.RESCUE_ORDER SELECT " + eosEventRecord.CaseId + ",999,COUNT(*) + 1,'',"

                    var sqlStmt = "INSERT INTO EOS.RESCUE_ORDER SELECT " + eosEventRecord.CaseId +

                                  ",999,COUNT(*) + 1,'',"

                                  + eosEventRecord.LocateEquipment.DsUfid + " FROM EOS.RESCUE_ORDER WHERE DSUFID = "

                                  + eosEventRecord.LocateEquipment.DsUfid;



                    command1 = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

                    command1 = new OracleCommand(sqlStmt, conn, trx);

                    if (command1.ExecuteNonQuery() <= 0)

                    {

                        Logger.Error("新增搶修順序資料時發生錯誤.");

@@ -132,7 +122,7 @@


                if (eosEventRecord.ParentId == 0)

                {

                    if (!InsertIntoSri(eosEventRecord))

                    if (!InsertIntoSri(conn, trx, eosEventRecord))

                    {

                        Logger.Error("新增資料庫(OCSDB.SRI)錯誤.");

                        return false;

@@ -154,13 +144,14 @@
            return true;

        }



        private bool ProcessSingalCase(Object.EOSEventRecord eosEventRecord)

        private bool ProcessSingalCase(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)

        {

            var result = false;

            var sqlStmt = "INSERT INTO EOS.EVENTS VALUES(" + eosEventRecord.CaseId + "," + CCSCaseState.WaitForDespatch + ",1,"

            var sqlStmt = "INSERT INTO EOS.EVENTS VALUES(" + eosEventRecord.CaseId + "," + CCSCaseState.WaitForDespatch +

                          ",1,"

                          + GlobalVariable.OriginalCase + ",0)";



            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

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



            try

            {

@@ -180,13 +171,14 @@
            return result;

        }



        private bool ProcessMergeCase(Object.EOSEventRecord eosEventRecord)

        private bool ProcessMergeCase(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)

        {

            // insert eos.events

            var sqlStmt = "INSERT INTO EOS.EVENTS VALUES(" + eosEventRecord.CaseId + "," + CCSCaseState.WaitForDespatch + ",1,"

            var sqlStmt = "INSERT INTO EOS.EVENTS VALUES(" + eosEventRecord.CaseId + "," + CCSCaseState.WaitForDespatch +

                          ",1,"

                          + GlobalVariable.ChildCase + ",0)";



            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

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



            try

            {

@@ -198,7 +190,8 @@
                }



                // insert into eos.mergecase

                sqlStmt = "INSERT INTO EOS.MERGECASE VALUES(" + eosEventRecord.CaseId + ",'" + eosEventRecord.AcceptNum + "'," +

                sqlStmt = "INSERT INTO EOS.MERGECASE VALUES(" + eosEventRecord.CaseId + ",'" + eosEventRecord.AcceptNum +

                          "'," +

                          eosEventRecord.ParentId + ")";



                command.CommandText = sqlStmt;

@@ -224,7 +217,8 @@


                // update parent case data(EOS.EVENTRECORD)

                sqlStmt = "UPDATE EOS.EVENTRECORD SET FSC = " + eosEventRecord.Fsc + ",UFID = " + eosEventRecord.Ufid +

                          " WHERE CASEID in (SELECT CASEID FROM EOS.MERGECASE WHERE PARENTID = " + eosEventRecord.ParentId +

                          " WHERE CASEID in (SELECT CASEID FROM EOS.MERGECASE WHERE PARENTID = " +

                          eosEventRecord.ParentId +

                          ") OR CASEID = "

                          + eosEventRecord.ParentId;



@@ -247,14 +241,14 @@
            return true;

        }



        private bool SetVisualCustomer(Object.EOSEventRecord eosEventRecord)

        private bool SetVisualCustomer(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)

        {

            var sqlStmt = "";

            var customerKind = "C"; // 高壓用戶or一般用戶



            sqlStmt = "SELECT COUNT(*) as COUNT FROM BASEDB.HICUSTOMER WHERE METR_NUMB = '" + eosEventRecord.Meter + "'";

            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

            OracleDataReader reader = command.ExecuteReader();

            var sqlStmt = "SELECT COUNT(*) as COUNT FROM BASEDB.HICUSTOMER WHERE METR_NUMB = '" + eosEventRecord.Meter +

                          "'";

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

            var reader = command.ExecuteReader();



            try

            {

@@ -285,7 +279,7 @@
                          "FROM BASEDB.METER M,BASEDB.MSTADDR A WHERE M.METR_NUMB = A.M_CUST_NO(+) AND M.METR_NUMB = '" +

                          eosEventRecord.Meter + "'";



                OracleCommand command1 = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

                var command1 = new OracleCommand(sqlStmt, conn, trx);

                if (command1.ExecuteNonQuery() >= 0)

                {

                    command1.Dispose();

@@ -294,24 +288,23 @@
                command1.Dispose();

                return false;

            }

            else

            {

                return InsertMutilCustomer(customerKind, eosEventRecord);

            }

            return InsertMutilCustomer(conn, trx, customerKind, eosEventRecord);

        }



        private bool InsertMutilCustomer(string customerKind, Object.EOSEventRecord eosEventRecord)

        private bool InsertMutilCustomer(OracleConnection conn, OracleTransaction trx, string customerKind,

            EOSEventRecord eosEventRecord)

        {

            string sqlStmt = "";

            bool updateCustomer = false;

            string mTpclidGroupId = "";

            string mCustomerKind = "C";

            var sqlStmt = "";

            var updateCustomer = false;

            var mTpclidGroupId = "";

            var mCustomerKind = "C";



            sqlStmt = "SELECT COUNT(*) as COUNT FROM EOS.TMPAFFECTCUSTMS WHERE CASEID = " + eosEventRecord.ParentId +

                      " OR CASEID IN (SELECT CASEID FROM EOS.MERGECASE WHERE PARENTID = " + eosEventRecord.ParentId + ")";

                      " OR CASEID IN (SELECT CASEID FROM EOS.MERGECASE WHERE PARENTID = " + eosEventRecord.ParentId +

                      ")";



            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

            OracleDataReader reader = command.ExecuteReader();

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

            var reader = command.ExecuteReader();



            try

            {

@@ -344,7 +337,7 @@


                try

                {

                    command1 = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

                    command1 = new OracleCommand(sqlStmt, conn, trx);

                    reader = command1.ExecuteReader();



                    if (reader.Read())

@@ -399,10 +392,11 @@
            sqlStmt = "INSERT INTO EOS.TMPAFFECTCUSTMS SELECT " + eosEventRecord.CaseId + ",A.M_NAME,"

                      + "M.TELE_NUMB,M.METR_NUMB,A.M_ADDR,M.TPCLIDGROUPID,M.CUST_TYPE,'" + customerKind + "' "

                      + "FROM (SELECT TELE_NUMB,METR_NUMB,TRAN_CORD as TPCLIDGROUPID,CUST_TYPE FROM BASEDB.METER "

                      + "WHERE TRAN_CORD = (SELECT TRAN_CORD FROM BASEDB.METER WHERE METR_NUMB = '" + eosEventRecord.Meter +

                      + "WHERE TRAN_CORD = (SELECT TRAN_CORD FROM BASEDB.METER WHERE METR_NUMB = '" +

                      eosEventRecord.Meter +

                      "')) M,BASEDB.MSTADDR A WHERE M.METR_NUMB = A.M_CUST_NO(+) ";



            OracleCommand command2 = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

            var command2 = new OracleCommand(sqlStmt, conn, trx);

            if (command2.ExecuteNonQuery() < 0)

            {

                Logger.Error("無法新增新受理案件的影響用戶資料.");

@@ -413,7 +407,7 @@
            return true;

        }



        private bool UpdateImportCase(Object.EOSEventRecord eosEventRecord)

        private bool UpdateImportCase(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord, bool upLevel)

        {

            /* 該案件不為交辦案件 */

            if (eosEventRecord.ImportCase != GlobalVariable.IsImportCase)

@@ -429,8 +423,8 @@


            /* 若母案件已為交辦案件，則不需再作任何更動 */

            var sqlStmt = "SELECT IMPORTCASE FROM EOS.EVENTRECORD WHERE CASEID = " + eosEventRecord.ParentId;

            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

            OracleDataReader reader = command.ExecuteReader();

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

            var reader = command.ExecuteReader();



            try

            {

@@ -452,14 +446,14 @@
            }



            /* 開始進行交辦案件升級動作 */

            _upLevel = true;

            upLevel = true;



            /* 將交辦案件改為母案件 */

            sqlStmt = "UPDATE EOS.EVENTS SET CASETYPE = " + GlobalVariable.ParentCase + ",MERGECASECOUNT = "

                      + "(SELECT MERGECASECOUNT FROM EOS.EVENTS WHERE CASEID = " + eosEventRecord.ParentId +

                      ") WHERE CASEID = " + eosEventRecord.CaseId;



            OracleCommand command1 = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

            var command1 = new OracleCommand(sqlStmt, conn, trx);

            if (command1.ExecuteNonQuery() <= 0)

            {

                Logger.Error("無法將交辦案件改為母案件.");

@@ -480,7 +474,8 @@
            }



            // 將EOS.MERGECASE內的資料修正 1.將原先該母案件下的子案件都改掛在交辦案件下 2.將已INSERT入EOS.MERGECASE內的交辦案件資料改為原先母案件的資料(CaseID,AcceptNum)

            sqlStmt = "UPDATE EOS.MERGECASE SET PARENTID = " + eosEventRecord.CaseId + " WHERE PARENTID = " + eosEventRecord.ParentId;

            sqlStmt = "UPDATE EOS.MERGECASE SET PARENTID = " + eosEventRecord.CaseId + " WHERE PARENTID = " +

                      eosEventRecord.ParentId;



            command1.CommandText = sqlStmt;

            if (command1.ExecuteNonQuery() <= 0)

@@ -502,14 +497,14 @@
            }

            command1.Dispose();



            return UpdateOtherTable(eosEventRecord.ParentId, eosEventRecord.CaseId, eosEventRecord.AcceptNum);

            return UpdateOtherTable(conn, trx, eosEventRecord);

        }



        private bool UpdateOtherTable(int oldCaseId, int newCaseId, string newAcceptNum)

        private bool UpdateOtherTable(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)

        {

            var sqlStmt = "SELECT CASESTATUS FROM EOS.EVENTS WHERE CASEID = " + oldCaseId;

            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

            OracleDataReader reader = command.ExecuteReader();

            var sqlStmt = "SELECT CASESTATUS FROM EOS.EVENTS WHERE CASEID = " + eosEventRecord.ParentId;

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

            var reader = command.ExecuteReader();



            try

            {

@@ -530,7 +525,8 @@
                        break;



                    case (int) CCSCaseState.WaitForSponsor:

                        sqlStmt = "UPDATE EOS.EVENTDESPATCH SET CASEID = " + newCaseId + " WHERE CASEID = " + oldCaseId;

                        sqlStmt = "UPDATE EOS.EVENTDESPATCH SET CASEID = " + eosEventRecord.CaseId + " WHERE CASEID = " +

                                  eosEventRecord.ParentId;



                        command.CommandText = sqlStmt;

                        if (command.ExecuteNonQuery() < 0)

@@ -541,7 +537,7 @@
                        }



                        sqlStmt = "update eos.events set casestatus=" + CCSCaseState.WaitForSponsor + " where caseid=" +

                                  newCaseId;

                                  eosEventRecord.CaseId;

                        command.CommandText = sqlStmt;

                        if (command.ExecuteNonQuery() < 0)

                        {

@@ -550,12 +546,12 @@
                            return false;

                        }



                        _eosEventRecord.IsDespatched = true;

                        eosEventRecord.IsDespatched = true;

                        break;

                }



                sqlStmt = "UPDATE EOS.EVENTS SET CASESTATUS = " + CCSCaseState.WaitForDespatch + " WHERE CASEID = " +

                          oldCaseId;

                          eosEventRecord.ParentId;



                command.CommandText = sqlStmt;

                if (command.ExecuteNonQuery() <= 0)

@@ -565,9 +561,9 @@
                    return false;

                }



                sqlStmt = "UPDATE OCSDB.SRI SET SCENENAME = '" + newAcceptNum +

                sqlStmt = "UPDATE OCSDB.SRI SET SCENENAME = '" + eosEventRecord.AcceptNum +

                          "' WHERE SCENENAME = (SELECT ACCEPTNUM FROM "

                          + "EOS.EVENTRECORD WHERE CASEID = " + oldCaseId + ")";

                          + "EOS.EVENTRECORD WHERE CASEID = " + eosEventRecord.ParentId + ")";



                command.CommandText = sqlStmt;

                if (command.ExecuteNonQuery() < 0)

@@ -577,11 +573,11 @@
                    return false;

                }



                sqlStmt = "SELECT COUNT(*) as COUNT FROM EOS.CRTAPOLOGY WHERE CASEID = " + oldCaseId;

                sqlStmt = "SELECT COUNT(*) as COUNT FROM EOS.CRTAPOLOGY WHERE CASEID = " + eosEventRecord.ParentId;



                command.CommandText = sqlStmt;

                reader = command.ExecuteReader();

                int count = 0;

                var count = 0;



                if (reader.Read())

                {

@@ -592,7 +588,8 @@
                // 已有切過開關

                if (count > 0)

                {

                    sqlStmt = "UPDATE EOS.EVENTFACILITY SET CASEID = " + newCaseId + " WHERE CASEID = " + oldCaseId;

                    sqlStmt = "UPDATE EOS.EVENTFACILITY SET CASEID = " + eosEventRecord.CaseId + " WHERE CASEID = " +

                              eosEventRecord.ParentId;



                    command.CommandText = sqlStmt;

                    if (command.ExecuteNonQuery() < 0)

@@ -602,7 +599,8 @@
                        return false;

                    }



                    sqlStmt = "UPDATE EOS.CRTAPOLOGY SET CASEID = " + newCaseId + " WHERE CASEID = " + oldCaseId;

                    sqlStmt = "UPDATE EOS.CRTAPOLOGY SET CASEID = " + eosEventRecord.CaseId + " WHERE CASEID = " +

                              eosEventRecord.ParentId;



                    command.CommandText = sqlStmt;

                    if (command.ExecuteNonQuery() < 0)

@@ -612,7 +610,8 @@
                        return false;

                    }



                    sqlStmt = "UPDATE EOS.EVENTAFFECT SET CASEID = " + newCaseId + " WHERE CASEID = " + oldCaseId;

                    sqlStmt = "UPDATE EOS.EVENTAFFECT SET CASEID = " + eosEventRecord.CaseId + " WHERE CASEID = " +

                              eosEventRecord.ParentId;



                    command.CommandText = sqlStmt;

                    if (command.ExecuteNonQuery() < 0)

@@ -629,9 +628,9 @@
                    //                    return false;

                    //                }



                    sqlStmt = "UPDATE OCSDB.LOCKFEATURE SET DESCRIPTION = '" + newAcceptNum +

                    sqlStmt = "UPDATE OCSDB.LOCKFEATURE SET DESCRIPTION = '" + eosEventRecord.AcceptNum +

                              "' WHERE DESCRIPTION = (SELECT ACCEPTNUM FROM "

                              + "EOS.EVENTRECORD WHERE CASEID = " + oldCaseId + ")";

                              + "EOS.EVENTRECORD WHERE CASEID = " + eosEventRecord.ParentId + ")";



                    command.CommandText = sqlStmt;

                    if (command.ExecuteNonQuery() < 0)

@@ -653,14 +652,14 @@
            return true;

        }



        private bool UpdateRecuseOrder(Object.EOSEventRecord eosEventRecord)

        private bool UpdateRecuseOrder(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord, bool upLevel)

        {

            int rank = 999;

            int priority = 1;

            var rank = 999;

            var priority = 1;

            OracleCommand command = null;



            /* 單純的子案件或不需升級的交辦案件,不需要更新EOS.RESCUE_ORDER */

            if ((eosEventRecord.ParentId != 0) && !(_upLevel))

            if ((eosEventRecord.ParentId != 0) && !upLevel)

            {

                return true;

            }

@@ -674,14 +673,14 @@
            try

            {

                string sqlStmt;

                if (_upLevel)

                if (upLevel)

                {

                    /* 將原先母案件的編號改為新的母案件編號 */

                    sqlStmt = "UPDATE EOS.RESCUE_ORDER SET CASEID = " + eosEventRecord.CaseId + " WHERE CASEID = " +

                              eosEventRecord.ParentId +

                              " AND DSUFID = " + eosEventRecord.LocateEquipment.DsUfid;



                    command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

                    command = new OracleCommand(sqlStmt, conn, trx);

                    if (command.ExecuteNonQuery() <= 0)

                    {

                        Logger.Error("無法更新EOS.RESCUE_ORDER: 無法將原先母案件的編號改為新的母案件編號.");

@@ -694,8 +693,8 @@
                    /* 找出該饋線的等級 */

                    sqlStmt = "SELECT RANK FROM EOS.IMP_FEEDER WHERE FEEDERID = " + eosEventRecord.FdrId;



                    command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

                    OracleDataReader reader = command.ExecuteReader();

                    command = new OracleCommand(sqlStmt, conn, trx);

                    var reader = command.ExecuteReader();



                    if (reader.Read())

                    {

@@ -756,7 +755,8 @@
                    }



                    /* 將本身資料存入資料庫內 */

                    sqlStmt = "INSERT INTO EOS.RESCUE_ORDER (CASEID,RANK,PRIORITY,DSUFID) VALUES (" + eosEventRecord.CaseId +

                    sqlStmt = "INSERT INTO EOS.RESCUE_ORDER (CASEID,RANK,PRIORITY,DSUFID) VALUES (" +

                              eosEventRecord.CaseId +

                              "," + rank + ","

                              + (priority + 1) + "," + eosEventRecord.LocateEquipment.DsUfid + ")";



@@ -784,30 +784,25 @@
        }



        // 當原先同一用戶在事故受理後又報案，不過從一般案件轉為交辦案件所需作的轉換工作

        private bool DeleteOldCase(Object.EOSEventRecord eosEventRecord)

        private bool DeleteOldCase(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)

        {

            string sqlStmt;

            int caseId = 0; // 原先的事故號碼

            int caseType = GlobalVariable.ChildCase; // 原先的種類

            int count = 0; // 原先的來電次數

            int caseStatus = 0; // 原先的案件狀態

            OracleCommand command = null;



            sqlStmt = "SELECT R.CASEID as CASEID,E.CASETYPE as CASETYPE,E.CASESTATUS as CASESTATUS,E.COUNT as COUNT FROM "

                      + "EOS.EVENTRECORD R,EOS.EVENTS E WHERE R.CASEID = E.CASEID AND R.CUSTOMERMETER = '" +

                      eosEventRecord.Meter + "' AND R.CASEID <> "

                      + eosEventRecord.CaseId + " AND E.CASESTATUS <= " + CCSCaseState.WaitForSponsor;

            var sqlStmt = "SELECT R.CASEID as CASEID,E.CASETYPE as CASETYPE,E.CASESTATUS as CASESTATUS,E.COUNT as COUNT FROM "

                          + "EOS.EVENTRECORD R,EOS.EVENTS E WHERE R.CASEID = E.CASEID AND R.CUSTOMERMETER = '" +

                          eosEventRecord.Meter + "' AND R.CASEID <> "

                          + eosEventRecord.CaseId + " AND E.CASESTATUS <= " + CCSCaseState.WaitForSponsor;



            try

            {

                command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

                OracleDataReader reader = command.ExecuteReader();

                command = new OracleCommand(sqlStmt, conn, trx);

                var reader = command.ExecuteReader();



                var caseId = 0; // 原先的事故號碼

                var count = 0; // 原先的來電次數

                if (reader.Read())

                {

                    caseId = Convert.ToInt32(reader["CASEID"].ToString());

                    caseType = Convert.ToInt32(reader["CASETYPE"].ToString());

                    caseStatus = Convert.ToInt32(reader["CASESTATUS"].ToString());

                    count = Convert.ToInt32(reader["COUNT"].ToString());

                    reader.Close();

                }

@@ -946,26 +941,23 @@
            catch (Exception e)

            {

                Logger.Error(e, e.Message);



                if (command != null)

                    command.Dispose();



                command?.Dispose();

                return false;

            }

            return true;

        }



        private bool InsertIntoSri(Object.EOSEventRecord eosEventRecord)

        private bool InsertIntoSri(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)

        {

            int count = 0;

            OracleCommand command = null;

            var count = 0;

            OracleCommand command;

            OracleDataReader reader = null;



            var sqlStmt = "SELECT COUNT(*) AS COUNT FROM OCSDB.SRI WHERE SCENENAME = '" + eosEventRecord.AcceptNum + "'";



            try

            {

                command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

                command = new OracleCommand(sqlStmt, conn, trx);

                reader = command.ExecuteReader();



                if (reader.Read())

@@ -1008,7 +1000,7 @@
            return true;

        }



        private bool SaveCustomerTel(Object.EOSEventRecord eosEventRecord)

        private bool SaveCustomerTel(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)

        {

            // 無電號資料

            /*

@@ -1031,7 +1023,7 @@
            var sqlStmt = "UPDATE BASEDB.METER SET TELE_NUMB = '" + eosEventRecord.Tel.Trim() + "' WHERE METR_NUMB = '" +

                          eosEventRecord.Meter + "'";



            OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);

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



            try

            {


 CCSTrace/CCS/Function/Leach.cs

@@ -2,6 +2,7 @@


using System.Data.OracleClient;

using CCSTrace.CCS.Domain;

using CCSTrace.CCS.Object;

using NLog;



namespace CCSTrace.CCS.Function

@@ -22,7 +23,7 @@
            _traceConnectionString = traceConnection;

        }



        public int LeachCase(Object.EOSEventRecord mNewRec)

        public int LeachCase(EOSEventRecord mNewRec)

        {

            switch (SameCase(mNewRec))

            {

@@ -51,7 +52,7 @@
            return _mergeCase.GetOldEquipment();

        }



        private int SameCase(Object.EOSEventRecord mNewRec)

        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 ";

@@ -264,7 +265,7 @@
            return status;

        }



        private int Judge(Object.EOSEventRecord mNewRec, bool changeCaseType)

        private int Judge(EOSEventRecord mNewRec, bool changeCaseType)

        {

            int mResult = GlobalVariable.FailureByDb;



@@ -367,7 +368,7 @@
            return mResult;

        }



        private int GetEquipment(Object.EOSEventRecord mNewRec)

        private int GetEquipment(EOSEventRecord mNewRec)

        {

            string temp;

            string mOwnerTpclid;

@@ -417,7 +418,7 @@
            }

        }



        private int GetSxfmr(string mTpclid, string mGroup, Object.EOSEventRecord mNewRec)

        private int GetSxfmr(string mTpclid, string mGroup, EOSEventRecord mNewRec)

        {

            string sqlStmt;

            int mStatus = GlobalVariable.FailureByDb;

@@ -460,7 +461,7 @@
            return mStatus;

        }



        private int GetHicustomer(string mTpclid, string mGroup, Object.EOSEventRecord mNewRec)

        private int GetHicustomer(string mTpclid, string mGroup, EOSEventRecord mNewRec)

        {

            string sqlStmt;



@@ -502,7 +503,7 @@
            }

        }



        private void GetLocateEquipment(Object.EOSEventRecord mNewRec)

        private void GetLocateEquipment(EOSEventRecord mNewRec)

        {

            var sqlStmt = "SELECT OWNERFSC as FSC,OWNERUFID as UFID,OWNERTPCLID as TPCLID FROM ";

            switch (mNewRec.Fsc)


 CCSTrace/CCS/Function/MailService.cs

@@ -17,9 +17,8 @@
        private string _eMailPass;

        private string _toEMail;



        public MailService(OracleConnection conn)

        public MailService()

        {

            InitialSmtpClient(conn);

        }



        public void InitialSmtpClient(OracleConnection conn)

@@ -47,8 +46,7 @@
                //objMail.Body = "測試收件者";

                objMail.IsBodyHtml = true;



                if (objAttFle != null)

                    objMail.Attachments.Add(objAttFle);

                objMail.Attachments.Add(objAttFle);



                _smtpClient.Send(objMail);

                return true;

@@ -61,7 +59,7 @@


        private void GetMailSetting(OracleConnection conn)

        {

            var sqlStmt = "SELECT MAIL_ACCOUNT,MAIL_PASSWORD,MAIL_HOST,MAIL_PORT,WOS_MAIL FROM USRADMIN.SYS_MAIL";

            const string sqlStmt = "SELECT MAIL_ACCOUNT,MAIL_PASSWORD,MAIL_HOST,MAIL_PORT,WOS_MAIL FROM USRADMIN.SYS_MAIL";



            OracleCommand command = new OracleCommand(sqlStmt, conn);

            OracleDataReader reader = command.ExecuteReader();


 CCSTrace/CCS/GlobalVariable.cs

@@ -88,6 +88,6 @@
        public static int MinThreadSize { get; set; } = 1;
        public static int MaxThreadSize { get; set; } = 5;
        public static int MaxConnectionCount { get; set; } = 1;
        public static int IdleTimeout { get; set; } = 10;
        public static int IdleTimeout { get; set; } = 60;
    }
}

 CCSTrace/CCS/Object/DeptContrast.cs

New file
@@ -0,0 +1,137 @@
using System;

using System.Collections;
using System.Collections.Generic;
using System.Data.OracleClient;
using CCSTrace.TPower.DMMS.Model.CCS;
using NLog;

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

        public const string DefaultDept = "市區巡修課";
        public static DeptContrast Instance { get; } = new DeptContrast();

        private List<DEPTCONTRAST> _mTotalData;

        private DeptContrast()
        {
        }

        public int getDept_Code(string mMeter)
        {
            //int Result = CCS.CCSMain.EOSCodelist.getKeyID(CCS.GlobalVariable.Dept, DefaultDept);
            var result = -1;

            try
            {
                for (var i = 0; i < _mTotalData.Count; i++)
                {
                    var tmpData = _mTotalData[i];

                    if (tmpData.MAXMETER == null)
                    {
                        result = Convert.ToInt32(tmpData.DEPTCODE);
                        break;
                    }
                    else
                    {// 一對多(需作比對)
                        var minMeter = Convert.ToInt64(tmpData.MINMETER);
                        var maxMeter = Convert.ToInt64(tmpData.MAXMETER);
                        var custMeter = Convert.ToInt64(mMeter);

                        if ((custMeter > minMeter) && (custMeter < maxMeter))
                        {
                            result = Convert.ToInt32(tmpData.DEPTCODE);
                            break;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Warn(ex, ex.Message);
            }
            return result;
        }

        public int getDept_Code(int scCode, string meter)
        {
            var result = GlobalVariable.EosCodelist.GetKeyId(GlobalVariable.Dept, DefaultDept);
            try
            {
                for (var i = 0; i < _mTotalData.Count; i++)
                {
                    var tmpData = _mTotalData[i];
                    var tmpDept = Convert.ToInt32(tmpData.SCCODE);

                    if (tmpDept == scCode)
                    {
                        // 一對一(無最大最小電號限制)
                        if (tmpData.MAXMETER == null)
                        {
                            result = Convert.ToInt32(tmpData.DEPTCODE);
                            break;
                        }
                        else
                        {// 一對多(需作比對)
                            var minMeter = Convert.ToInt64(tmpData.MINMETER);
                            var maxMeter = Convert.ToInt64(tmpData.MAXMETER);
                            var custMeter = Convert.ToInt64(meter);

                            if ((custMeter > minMeter) && (custMeter < maxMeter))
                            {
                                result = Convert.ToInt32(tmpData.DEPTCODE);
                                break;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Warn(ex, ex.Message);
            }

            return result;
        }

        public void Initialize(OracleConnection conn)
        {
            _mTotalData = new List<DEPTCONTRAST>();

            var sqlStmt = "SELECT SC_CODE,DEPT_CODE,MAX_METER,MIN_METER FROM CCS.DEPT_CONTRAST";
            var command = new OracleCommand(sqlStmt, conn);
            var reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    var deptcontrast = new DEPTCONTRAST
                    {
                        SCCODE = reader["SC_CODE"].ToString(),
                        DEPTCODE = reader["Dept_Code"].ToString(),
                        MAXMETER = reader["Max_Meter"].ToString(),
                        MINMETER = reader["Min_Meter"].ToString()
                    };


                    _mTotalData.Add(deptcontrast);
                }
            }
            catch (Exception e)
            {
                Logger.Warn(e, e.Message);
            }
            finally
            {
                reader?.Close();
                command?.Dispose();
            }
        }
    }
}

 CCSTrace/CCS/Object/Dept_Contrast.cs

File was deleted

 CCSTrace/CCS/ProcessEvent.cs

@@ -10,125 +10,90 @@


namespace CCSTrace.CCS

{

    public delegate void ThreadEndEventHandler(object sender, ThreadEndEvent e);



    public class ProcessEvent

    {

        private static readonly Logger Logger = LogManager.GetCurrentClassLogger();



        private readonly OracleConnection _connectionTpc;

        private readonly CCSEventRecord _mCcsEventRecord;



        private EOSEventRecord _eosEventRecord;

        private readonly string _traceConnectionString;

        private OracleTransaction _trx;



        public ProcessEvent(CCSEventRecord eventRecord, OracleConnection conn, string traceConnection)

        {

            _mCcsEventRecord = eventRecord;

            _connectionTpc = conn;

            _traceConnectionString = traceConnection;

        }



        public event ThreadEndEventHandler ThreadFinish;



        public void Run()

        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(_mCcsEventRecord.Meter);

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



                if (fdrid != 0) //有饋線代號資料的案件才需比對同饋線上有無案件正在處理

                {

                    if (CcsMain.Instance.ProcessingFdr.Contains(fdrid))

                    if (CcsMain.Instance.ContainProcessingFdr(fdrid))

                    {

                        CcsMain.Instance.WaitingCases.Add(new KeyValuePair<string, int>(_mCcsEventRecord.CcsId, fdrid));

                        CcsMain.Instance.AddWaitingCases(ccsEventRecord.CcsId, fdrid);

                        return;

                    }

                    else

                        CcsMain.Instance.ProcessingFdr.Add(fdrid); //將要處理的案件的饋線別加入處理中饋線集合

                        CcsMain.Instance.AddProcessingFdr(fdrid); //將要處理的案件的饋線別加入處理中饋線集合

                }



                /* 
                int TraceCount = this.getTraceCount(m_CCSRecord.CcsId);

                bool TraceError = false;    //追蹤次數超過3次，改為非A類不追蹤合併



                switch (TraceCount)

                eosEventRecord = InitialEventRecord(conn, trx, ccsEventRecord);

                if (eosEventRecord != null)

                {

                    case 0:

                        UpdateTraceCount(m_CCSRecord.CcsId,true);

                        TraceError = false;

                        break;



                    case 2:

                        UpdateTraceCount(m_CCSRecord.CcsId,false);

                        TraceError = true;

                        break;



                    default:

                        UpdateTraceCount(m_CCSRecord.CcsId,false);

                        TraceError = false;

                        break;

                }

                */

                if (InitialEventRecord(_mCcsEventRecord.CcsId))

                {

                    var mLeach = new Leach(_connectionTpc, _trx, _traceConnectionString); // 案件過濾與合併

                    var mLeach = new Leach(conn, trx, GlobalVariable.TraceConnectionString); // 案件過濾與合併

                    Logger.Info("過濾案件...");

                    var returnStatus = mLeach.LeachCase(_eosEventRecord);

                    var returnStatus = mLeach.LeachCase(eosEventRecord);



                    switch (returnStatus)

                    {

                        case GlobalVariable.Success:

                            Logger.Info("案件過濾完畢....");

                            Process(GlobalVariable.Success);

                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.Success);

                            break;



                        case GlobalVariable.NoMeter:

                            Logger.Info("案件過濾完畢,用戶無電號....");

                            _eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;

                            Process(GlobalVariable.NoMeter);

                            eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;

                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);

                            break;



                        case GlobalVariable.SameCustomer:

                            Logger.Info("相同用戶重複來電....");

                            ProcessSameCustomer(_mCcsEventRecord, _eosEventRecord);

                            ProcessSameCustomer(conn, trx, ccsEventRecord, eosEventRecord);

                            break;



                        case GlobalVariable.NoSupplyElc:

                            Logger.Warn("該饋線未供電,無法作事故案件追蹤合併....");

                            _eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;

                            Process(GlobalVariable.NoMeter);

                            eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;

                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);

                            break;



                        case GlobalVariable.NoSxfmr:

                            Logger.Warn("該案件無變壓器....");

                            _eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;

                            Process(GlobalVariable.NoMeter);

                            eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;

                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);

                            break;



                        case GlobalVariable.CaseTypeChanged:

                            Logger.Info("變更案件狀態....");

                            Process(GlobalVariable.CaseTypeChanged);

                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.CaseTypeChanged);

                            break;



                        case GlobalVariable.TraceFailure:

                            Logger.Error("故障追蹤錯誤，以一般無電號案件方式受理....");

                            _eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;

                            Process(GlobalVariable.NoMeter);

                            eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;

                            Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);

                            break;



                        default:

                            var traceCount = GetTraceCount(_mCcsEventRecord.CcsId);

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



                            if (traceCount < 2)

                            {

                                if (_trx.Connection.State == ConnectionState.Open)

                                    _trx.Rollback();

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

                                    trx.Rollback();



                                _trx.Dispose();

                                trx.Dispose();



                                if (returnStatus == GlobalVariable.CaseTransfer)

                                    Logger.Error("案件已移轉，此事故案件暫不被受理....");

@@ -140,166 +105,237 @@
                                    Logger.Error("發生非預期錯誤，此事故案件暫不被受理....");



                                if (traceCount == 0)

                                    UpdateTraceCount(_mCcsEventRecord.CcsId, true);

                                    UpdateTraceCount(conn, trx, ccsEventRecord.CcsId, true);

                                else

                                    UpdateTraceCount(_mCcsEventRecord.CcsId, false);

                                    UpdateTraceCount(conn, trx, ccsEventRecord.CcsId, false);

                            }

                            else

                            {

                                UpdateTraceCount(_mCcsEventRecord.CcsId, false);

                                UpdateTraceCount(conn, trx, ccsEventRecord.CcsId, false);

                                if (returnStatus == GlobalVariable.TraceCounts3)

                                    _eosEventRecord.Note = _eosEventRecord.Note+ "(本案用戶供電資料追蹤失敗)";

                                    eosEventRecord.Note = eosEventRecord.Note+ "(本案用戶供電資料追蹤失敗)";



                                Logger.Error("事故案件處理次數3次，仍無法正常處理，以一般無電號案件方式受理....");

                                _eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;

                                Process(GlobalVariable.NoMeter);

                                eosEventRecord.TraceFinish = GlobalVariable.TraceFinish;

                                Process(conn, trx, ccsEventRecord, eosEventRecord, GlobalVariable.NoMeter);

                                sendMail = true;

                            }

                            break;

                        /*                        switch (TraceCount)

                                                        {

                                                            case 0:

                                                                if (_Trx.Connection.State == ConnectionState.Open)

                                                                    _Trx.Rollback();



                                                                _Trx.Dispose();



                                                                if ( Return_Status == CCS.GlobalVariable.CaseTransfer )

                                                                    _PLog.Error("案件已移轉，此事故案件不被受理....");

                                                                else if ( Return_Status == CCS.GlobalVariable.Failure_By_DB )

                                                                    _PLog.Error("資料庫錯誤，此事故案件不被受理....");

                                                                else if ( Return_Status == CCS.GlobalVariable.FDRLocked )

                                                                    _PLog.Error("饋線被鎖定，此事故案件不被受理....");

                                                                else

                                                                    _PLog.Error("此事故案件不被受理....");



                                                                UpdateTraceCount(m_CCSRecord.CcsId, true);

                                                                break;



                                                            case 1:

                                                                if (_Trx.Connection.State == ConnectionState.Open)

                                                                    _Trx.Rollback();



                                                                _Trx.Dispose();



                                                                UpdateTraceCount(m_CCSRecord.CcsId, false);



                                                                break;



                                                            default://追蹤次數超過3次，改為非A類不追蹤合併

                                                                UpdateTraceCount(m_CCSRecord.CcsId, false);

                                                                if (Return_Status == CCS.GlobalVariable.Trace_Counts_3)

                                                                    m_EventRecord.setNote(m_EventRecord.getNote() + "(本案用戶供電資料追蹤失敗)");



                                                                _PLog.Error("追蹤次數3次，無法追蹤到故障設備，以一般無電號案件方式受理....");

                                                                m_EventRecord.setTrace_Finish(CCS.GlobalVariable.Trace_Finish);

                                                                Process(CCS.GlobalVariable.NoMeter);

                                                                SendMail = true;

                                                                break;

                                                        }



                                                        break;



                                                    case CCS.GlobalVariable.CaseTransfer:

                                                        if (_Trx.Connection.State == ConnectionState.Open)

                                                            _Trx.Rollback();



                                                        _Trx.Dispose();

                                                        _PLog.Error("案件已移轉，此事故案件不被受理....");

                                                        break;



                                                    case CCS.GlobalVariable.FDRLocked:

                                                        if (_Trx.Connection.State == ConnectionState.Open)

                                                            _Trx.Rollback();



                                                        _Trx.Dispose();

                                                        _PLog.Error("饋線被鎖定，此事故案件不被受理....");

                                                        break;



                                                    case CCS.GlobalVariable.Failure_By_DB:

                                                        if (_Trx.Connection.State == ConnectionState.Open)

                                                            _Trx.Rollback();



                                                        _Trx.Dispose();

                                                        _PLog.Error("資料庫錯誤，此事故案件不被受理....");

                                                        break;



                                                    default:

                                                        if (_Trx.Connection.State == ConnectionState.Open)

                                                            _Trx.Rollback();



                                                        _Trx.Dispose();

                                                        _PLog.Error("此事故案件不被受理....");

                                                        break;

                            */

                    }

                }



                if (fdrid != 0)

                    CcsMain.Instance.ProcessingFdr.Remove(fdrid); //將處理完的案件的饋線別從處理中饋線集合中移除

                    CcsMain.Instance.RemoveProcessingFdr(fdrid); //將處理完的案件的饋線別從處理中饋線集合中移除

            }

            catch (Exception e)

            {

                Logger.Error(e, e.Message + ". CCSID = " + _mCcsEventRecord.CcsId);

                Logger.Error(e, e.Message + ". CCSID = " + ccsEventRecord.CcsId);



                if (_trx != null)

                if (trx != null)

                {

                    if (_trx.Connection.State == ConnectionState.Open)

                        _trx.Rollback();

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

                        trx.Rollback();



                    _trx.Dispose();

                    trx.Dispose();

                }



                if (_eosEventRecord != null && _eosEventRecord.FdrId!= 0)

                    CcsMain.Instance.ProcessingFdr.Remove(_eosEventRecord.FdrId); //將處理完的案件的饋線別從處理中饋線集合中移除

                if (eosEventRecord != null && eosEventRecord.FdrId!= 0)

                    CcsMain.Instance.RemoveProcessingFdr(eosEventRecord.FdrId); //將處理完的案件的饋線別從處理中饋線集合中移除

                //throw e;

            }

            finally

            {

                CcsMain.Instance.ProcessingCases.Remove(_mCcsEventRecord.CcsId);

                OnThreadFinish(new ThreadEndEvent(_connectionTpc));

                CcsMain.Instance.RemoveProcessingCases(ccsEventRecord.CcsId);

                // OnThreadFinish(new ThreadEndEvent(_connectionTpc));

            }



            if (sendMail)

                new MailService(_connectionTpc).SendMail(_mCcsEventRecord.CcsId);

            {

                var mailService = new MailService();

                mailService.InitialSmtpClient(conn);

                mailService.SendMail(ccsEventRecord.CcsId);

            }

        }



        private void Process(int kind)

        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(kind, _eosEventRecord, _connectionTpc, _trx))

                if (finishEvent.Finish(conn, trx, eosEventRecord, kind))

                {

                    Logger.Info("OMS資料更新成功!");

                    if (UpdateCcsData(_eosEventRecord, _mCcsEventRecord, (int) CCSCaseState.WaitForDespatch))

                    if (UpdateCcsData(conn, trx, ccsEventRecord, eosEventRecord, (int)CCSCaseState.WaitForDespatch))

                    {

                        Logger.Info("CCS資料更新成功!");

                    }

                    else

                    {

                        if (_trx.Connection.State == ConnectionState.Open)

                            _trx.Rollback();

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

                            trx.Rollback();



                        _trx.Dispose();

                        trx.Dispose();

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

                        return;

                    }



                    // 須等全部動作都正確做完才可作commit的動作

                    _trx.Commit();

                    _trx.Dispose();

                    AlarmData.Instance.Alarm(_connectionTpc, _trx, _eosEventRecord.AcceptNum, _eosEventRecord.Dept);

                    trx.Commit();

                    trx.Dispose();

                    AlarmData.Instance.Alarm(conn, trx, eosEventRecord.AcceptNum, eosEventRecord.Dept);

                }

                else

                {

                    // 有錯誤需要RollBack

                    if (_trx.Connection.State == ConnectionState.Open)

                        _trx.Rollback();

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

                        trx.Rollback();



                    _trx.Dispose();

                    ;

                    trx.Dispose();

                    Logger.Error("OMS資料更新程序發生錯誤!");

                }

                Logger.Info("CCS 案件受理程序結束.");

@@ -311,12 +347,7 @@
            }

        }



        protected virtual void OnThreadFinish(ThreadEndEvent e)

        {

            ThreadFinish?.Invoke(this, e);

        }



        private bool UpdateCcsData(EOSEventRecord eosEventRecord, CCSEventRecord ccsEventRecord, int caseStatus)

        private bool UpdateCcsData(OracleConnection conn, OracleTransaction trx, CCSEventRecord ccsEventRecord, EOSEventRecord eosEventRecord, int caseStatus)

        {

            Logger.Info("進行CCS資料更新程序...");

            var ccsEventQuery = new CCSEventQuery();

@@ -334,23 +365,23 @@
            // 若母案件已派工,需一併紀錄預計復電時間等資訊

            if (eosEventRecord.ParentId!= 0)

            {

                status = GetRealCaseStatus(eosEventRecord.ParentId) == (int) CCSCaseState.WaitForSponsor

                status = GetRealCaseStatus(conn, trx, eosEventRecord.ParentId) == (int) CCSCaseState.WaitForSponsor

                    ? (int) CCSCaseState.EventDespatched

                    : (int) CCSCaseState.WaitForDespatch;

                if (status == (int) CCSCaseState.EventDespatched)

                {

                    SetDespatchInfo(eosEventRecord.ParentId, ccsEventQuery);

                    SetDespatchInfo(conn, trx, eosEventRecord.ParentId, ccsEventQuery);

                }

            }

            // 若此案件為專案案件升級為母案件,則必須判斷原先案件是否已經派工

            if ((ccsEventRecord.ImportCase == GlobalVariable.IsImportCase) && eosEventRecord.IsDespatched)

            {

                status = (int) CCSCaseState.EventDespatched;

                SetDespatchInfo(eosEventRecord.CaseId, ccsEventQuery);

                SetDespatchInfo(conn, trx, eosEventRecord.CaseId, ccsEventQuery);

            }

            ccsEventQuery.CaseStatus = status;

            // 更新CCS.CCSEventQuery

            if (!ccsEventQuery.Update(_connectionTpc, _trx))

            if (!ccsEventQuery.Update(conn, trx))

            {

                Logger.Error("更新ccs.eventquery失敗!");

                return false;

@@ -361,7 +392,7 @@
            numberContrast.SetCaseId(eosEventRecord.CaseId);

            numberContrast.SetCcsid(ccsEventRecord.CcsId);

            // 更新CCS.Num_Contrast

            if (!numberContrast.Update(_connectionTpc, _trx))

            if (!numberContrast.Update(conn, trx))

            {

                Logger.Error("更新ccs.num_contrast失敗!");

                return false;

@@ -370,83 +401,75 @@
            return true;

        }



        private bool InitialEventRecord(string ccsid)

        private EOSEventRecord InitialEventRecord(OracleConnection conn, OracleTransaction trx, CCSEventRecord ccsEventRecord)

        {

            var initialData = new InitialEventData(_connectionTpc);

            var initialData = new InitialEventData(conn);

            var mContrast = new NumberContrast();

            DeptContrast deptContrast = null;



            // Being Transaction

            _trx = _connectionTpc.BeginTransaction();



            deptContrast = new DeptContrast(_connectionTpc, _trx);

            _eosEventRecord = new EOSEventRecord(initialData.GetNewCaseId(), _connectionTpc, _trx)

            EOSEventRecord eosEventRecord = new EOSEventRecord(initialData.GetNewCaseId(), conn, trx)

            {

                AcceptNum = initialData.GetNewAcceptNum()

            };



            mContrast.SetAcceptNum(_eosEventRecord.AcceptNum);

            mContrast.SetCaseId(_eosEventRecord.CaseId);

            mContrast.SetCcsid(_mCcsEventRecord.CcsId);

            mContrast.SetAcceptNum(eosEventRecord.AcceptNum);

            mContrast.SetCaseId(eosEventRecord.CaseId);

            mContrast.SetCcsid(ccsEventRecord.CcsId);



            if (!mContrast.Insert(_connectionTpc, _trx))

            if (!mContrast.Insert(conn, trx))

            {

                Logger.Error("初始化寫入CCS.Num_Contrast時發生錯誤！");



                if (_trx.Connection.State == ConnectionState.Open)

                    _trx.Rollback();

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

                    trx.Rollback();



                return false;

                return null;

            }



            // 設定用戶姓名

            _eosEventRecord.Name = _mCcsEventRecord.CustomerName;

            eosEventRecord.Name = ccsEventRecord.CustomerName;



            if (_mCcsEventRecord.Meter != null && _mCcsEventRecord.Meter.Length != 0)

            if (!string.IsNullOrEmpty(ccsEventRecord.Meter))

            {

                // 需先找出部門代號,再找到該部門屬於哪一個巡修單位

                _eosEventRecord.Meter = _mCcsEventRecord.Meter;

                var tmp = deptContrast.getDept_Code(_mCcsEventRecord.Meter);

                eosEventRecord.Meter = ccsEventRecord.Meter;

                var tmp = DeptContrast.Instance.getDept_Code(ccsEventRecord.Meter);



                if (tmp == -1)

                    _eosEventRecord.Dept = FindoutDeptByAddr(_mCcsEventRecord, _trx);

                else

                    _eosEventRecord.Dept = tmp;

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



                _eosEventRecord.FdrId = GetFdrid(_mCcsEventRecord.Meter);

                eosEventRecord.FdrId = GetFdrid(conn, ccsEventRecord.Meter);

            }

            else

            {

                // 當無用戶電號時,所找出的部門別不需再去找出屬於哪一個巡修股(findoutDeptByAddr會直接找到相對應的巡修股)

                var dept = FindoutDeptByAddr(_mCcsEventRecord, _trx);

                _eosEventRecord.Dept = dept;

                var dept = FindoutDeptByAddr(trx, ccsEventRecord);

                eosEventRecord.Dept = dept;

            }

            // 設定用戶電話號碼

            if (_mCcsEventRecord.CustomerTel != null)

            if (ccsEventRecord.CustomerTel != null)

            {

                _eosEventRecord.Tel = _mCcsEventRecord.CustomerTel;

                eosEventRecord.Tel = ccsEventRecord.CustomerTel;

            }

            // 檢查並設定用戶地址

            var mAddr = _mCcsEventRecord.AddressCity + _mCcsEventRecord.AddressTown + _mCcsEventRecord.AddressRoad +

                        _mCcsEventRecord.AddressOther;

            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();

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

                    trx.Rollback();



                return false;

                return null;

            }



            _eosEventRecord.Addr = mAddr;

            eosEventRecord.Addr = mAddr;

            // 設定事故原因

            _eosEventRecord.Brief = TransferEventBrief(_mCcsEventRecord.EventBrief);

            eosEventRecord.Brief = TransferEventBrief(ccsEventRecord.EventBrief);

            try

            {

                var encoding = new ASCIIEncoding();

                var source = encoding.GetBytes(_eosEventRecord.Brief);

                var source = encoding.GetBytes(eosEventRecord.Brief);



                if (source.Length > 24)

                {

@@ -457,54 +480,54 @@
                    }



                    var enc = Encoding.ASCII;

                    _eosEventRecord.Brief = enc.GetString(dest);

                    eosEventRecord.Brief = enc.GetString(dest);

                    source = dest = null;

                }

            }

            catch (Exception)

            {

                _eosEventRecord.Brief = "其他";

                eosEventRecord.Brief = "其他";

            }



            if (_mCcsEventRecord.CcsId.StartsWith("A"))

                _eosEventRecord.Log = "APP";

            else if (_mCcsEventRecord.CcsId.StartsWith("W"))

                _eosEventRecord.Log = "WEB";

            if (ccsEventRecord.CcsId.StartsWith("A"))

                eosEventRecord.Log = "APP";

            else if (ccsEventRecord.CcsId.StartsWith("W"))

                eosEventRecord.Log = "WEB";

            else

                _eosEventRecord.Log = "CCS";

                eosEventRecord.Log = "CCS";



            _eosEventRecord.AcceptDate = _mCcsEventRecord.AcceptTime;

            eosEventRecord.AcceptDate = ccsEventRecord.AcceptTime;



            // 設定用戶種類

            if (_mCcsEventRecord.ImportCase == GlobalVariable.IsImportCase)

            if (ccsEventRecord.ImportCase == GlobalVariable.IsImportCase)

            {

                _eosEventRecord.ImportCase = GlobalVariable.IsImportCase;

                _eosEventRecord.Level = GlobalVariable.ALevel; // default set A level

                eosEventRecord.ImportCase = GlobalVariable.IsImportCase;

                eosEventRecord.Level = GlobalVariable.ALevel; // default set A level

            }

            else

            {

                _eosEventRecord.ImportCase = GlobalVariable.NotImportCase;

                _eosEventRecord.Level = GlobalVariable.NoLevel;

                eosEventRecord.ImportCase = GlobalVariable.NotImportCase;

                eosEventRecord.Level = GlobalVariable.NoLevel;

            }



            // 檢查字串中有無特殊字元' ,若有則取代為"

            if (_mCcsEventRecord.Note != null)

                _eosEventRecord.Note = _mCcsEventRecord.Note.Replace('\'', '\"');

            if (ccsEventRecord.Note != null)

                eosEventRecord.Note = ccsEventRecord.Note.Replace('\'', '\"');



            // 設定回覆姓名及電話

            if (_mCcsEventRecord.RecallName != null)

            if (ccsEventRecord.RecallName != null)

            {

                _eosEventRecord.IsReCall = GlobalVariable.IsReCall;

                _eosEventRecord.ReCallName = _mCcsEventRecord.RecallName.Replace('\'', '\"');

                _eosEventRecord.ReCallTel = _mCcsEventRecord.RecallTel;

                eosEventRecord.IsReCall = GlobalVariable.IsReCall;

                eosEventRecord.ReCallName = ccsEventRecord.RecallName.Replace('\'', '\"');

                eosEventRecord.ReCallTel = ccsEventRecord.RecallTel;

            }

            else

            {

                _eosEventRecord.IsReCall = GlobalVariable.NotReCall;

                eosEventRecord.IsReCall = GlobalVariable.NotReCall;

            }



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

            return true;

            return eosEventRecord;

        }



        //private bool initialCCSInfo(CCS.Object.CCSEventQuery m_EventQuery, CCS.Object.NumberContrast m_Contrast)

@@ -523,11 +546,11 @@
        //    return true;

        //}



        private void CheckCaseAlreadyExits(CCSEventQuery ccsEventQuery)

        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, _connectionTpc, _trx);

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

            var reader = command.ExecuteReader();



            try

@@ -552,7 +575,7 @@
            }

        }



        private int FindoutDeptByAddr(CCSEventRecord ccsEventRecord, OracleTransaction trx)

        private int FindoutDeptByAddr(OracleTransaction trx, CCSEventRecord ccsEventRecord)

        {

            return AddrContrast.Instance.FindDeptId(ccsEventRecord.AddressCity, ccsEventRecord.AddressTown, ccsEventRecord.AddressRoad);

        }

@@ -575,12 +598,12 @@
            return GlobalVariable.CcsCodelist.GetKeyId(GlobalVariable.CcsDept, deptCode);

        }



        private int GetRealCaseStatus(int parentId)

        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, _connectionTpc, _trx);

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

            var reader = command.ExecuteReader();



            try

@@ -603,17 +626,17 @@
            return status;

        }



        private void SetDespatchInfo(int caseId, CCSEventQuery ccsEventQry)

        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;

            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, _connectionTpc, _trx);

                command = new OracleCommand(sqlStmt, conn, trx);

                reader = command.ExecuteReader();



                if (reader.Read())

@@ -639,13 +662,13 @@
            }

        }



        private void ProcessSameCustomer(CCSEventRecord ccsEventRecord, EOSEventRecord eosEventRec)

        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, _connectionTpc, _trx);

            var sqlStmt = "SELECT NOTE FROM EOS.EVENTRECORD WHERE CASEID = " + eosEventRec.TmpCaseId;

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

            var reader = command.ExecuteReader();



            try

@@ -674,9 +697,9 @@
            // 判斷重複來電的用戶最初成立的案件是否是由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="

            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, _connectionTpc, _trx);

            var command1 = new OracleCommand(sqlStmt1, conn, trx);

            var reader1 = command1.ExecuteReader();



            try

@@ -725,12 +748,27 @@


                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="

                    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, _connectionTpc, _trx);

                    command2 = new OracleCommand(sqlStmt2, conn, trx);

                    reader2 = command2.ExecuteReader();



                    while (reader2.Read())

@@ -782,22 +820,22 @@
            }



            var updEosEventRecord = "UPDATE EOS.EVENTRECORD SET NOTE = '" + TrimToLimit(note.ToString(), 127)

                                    + "' WHERE CASEID = " + _eosEventRecord.TmpCaseId;

                                    + "' 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, _connectionTpc, _trx);

            var exeCommand = new OracleCommand(updCcsEventQuery, conn, trx);

            // 將用戶重複來電案件資料寫回CCS.CCSEventQuery

            if (exeCommand.ExecuteNonQuery() <= 0)

            {

                exeCommand.Dispose();



                if (_trx.Connection.State == ConnectionState.Open)

                    _trx.Rollback();

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

                    trx.Rollback();



                _trx.Dispose();

                trx.Dispose();

                Logger.Error("Fail in write back to ccs.eventquery");

                return;

            }

@@ -808,10 +846,10 @@
            {

                exeCommand.Dispose();



                if (_trx.Connection.State == ConnectionState.Open)

                    _trx.Rollback();

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

                    trx.Rollback();



                _trx.Dispose();

                trx.Dispose();

                Logger.Error("Fail in update eos.eventquery");

                return;

            }

@@ -821,17 +859,17 @@
            if (exeCommand.ExecuteNonQuery() <= 0)

            {

                exeCommand.Dispose();

                if (_trx.Connection.State == ConnectionState.Open)

                    _trx.Rollback();

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

                    trx.Rollback();



                _trx.Dispose();

                trx.Dispose();

                Logger.Error("Fail in insert into ccs.num_contrast");

                return;

            }



            exeCommand.Dispose();

            _trx.Commit();

            _trx.Dispose();

            trx.Commit();

            trx.Dispose();

            Logger.Info("CCS 案件受理程序結束");

        }



@@ -857,11 +895,11 @@
            }

        }



        private int GetFieldLength(string owner, string tableName, string fieldName)

        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, _connectionTpc, _trx);

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

            var reader = command.ExecuteReader();

            var maxLength = -1;



@@ -883,14 +921,13 @@
            return maxLength;

        }



        private int GetFdrid(string meter)

        private int GetFdrid(OracleConnection conn, string meter)

        {

            var sqlStmt = "SELECT TRAN_CORD FROM BASEDB.METER WHERE METR_NUMB = '" + meter + "'";

            var command = new OracleCommand(sqlStmt, _connectionTpc, _trx);

            var command = new OracleCommand(sqlStmt, conn);

            var reader = command.ExecuteReader();

            var tpclid = "";

            var fdrid = 0;

            var group = "";

            try

            {

                if (reader.Read())

@@ -901,17 +938,19 @@


                if (tpclid.Length > 11)

                {

                    group = tpclid.Substring(tpclid.Length - 3);

                    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 = '" +

                        "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 = '" +

                        "SELECT FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = 115 AND UFID = " +

                        "(SELECT UFID FROM BASEDB.SXFMR WHERE OWNERTPCLID = '" +

                        tpclid

                        + "' AND ROWNUM < 2)";

                }

@@ -924,7 +963,8 @@
                else

                {

                    sqlStmt =

                        "SELECT FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = 107 AND UFID = (SELECT UFID FROM BASEDB.HICUSTOMER WHERE OWNERTPCLID = '" +

                        "SELECT FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = 107 AND UFID = " +

                        "(SELECT UFID FROM BASEDB.HICUSTOMER WHERE OWNERTPCLID = '" +

                        tpclid + "' AND ROWNUM < 2)";



                    command.CommandText = sqlStmt;

@@ -947,10 +987,10 @@
            return fdrid;

        }



        private int GetTraceCount(string ccsid)

        private int GetTraceCount(OracleConnection conn, OracleTransaction trx, string ccsid)

        {

            var sqlStmt = "SELECT TRACECOUNT FROM CCS.TRACECOUNTS WHERE CCSID = '" + ccsid + "'";

            var command = new OracleCommand(sqlStmt, _connectionTpc, _trx);

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

            var reader = command.ExecuteReader();

            var count = 0;



@@ -972,7 +1012,7 @@
            return count;

        }



        private bool UpdateTraceCount(string ccsid, bool isNew)

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

        {

            string sqlStmt;



@@ -980,7 +1020,7 @@
                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, _connectionTpc, _trx);

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



            try

            {


 CCSTrace/CCSTrace.csproj

@@ -178,7 +178,7 @@
    <Compile Include="CCS\Object\AlarmData.cs" />

    <Compile Include="CCS\Object\CCSCodelist.cs" />

    <Compile Include="CCS\Object\CCSEventRecord.cs" />

    <Compile Include="CCS\Object\Dept_Contrast.cs" />

    <Compile Include="CCS\Object\DeptContrast.cs" />

    <Compile Include="CCS\Object\EOSCodelist.cs" />

    <Compile Include="CCS\Object\CCSEventQuery.cs" />

    <Compile Include="CCS\Object\EOSEventRecord.cs" />


 CCSTrace/DBConfig.xml

@@ -1,5 +1,5 @@
<?xml version="1.0" encoding="big5" ?>

  <CCS>

    <DBSetting DataSource = "nntpc" UserId = "basedb" Password = "BASEDB000" ConnectionCount = "3" ShowError = "false" />

    <DBSetting DataSource = "nntpc" UserId = "basedb" Password = "BASEDB000" ConnectionCount = "10" ShowError = "false" />

    <ThreadSetting maxThreadSize="2" minThreadSize="1" />

  </CCS>