From 94527026b113a1ca05fdee94879fdc32c4471ab8 Mon Sep 17 00:00:00 2001
From: ulysseskao <ulysseskao@gmail.com>
Date: Wed, 04 May 2016 19:49:49 +0800
Subject: [PATCH] add smartthreadpool
---
CCSTrace/CCS/Function/MailService.cs | 8
CCSTrace/CCS/ProcessEvent.cs | 359 +++++++++++-----------
CCSTrace/CCS/FinishEvent.cs | 217 ++++++------
CCSTrace/CCS/CCSMain.cs | 330 +++++++++++---------
CCSTrace.Tests/App_Data/DBConfig.xml | 2
CCSTrace/CCS/GlobalVariable.cs | 2
CCSTrace/DBConfig.xml | 2
7 files changed, 469 insertions(+), 451 deletions(-)
diff --git a/CCSTrace.Tests/App_Data/DBConfig.xml b/CCSTrace.Tests/App_Data/DBConfig.xml
index 493c63f..77ccf65 100644
--- a/CCSTrace.Tests/App_Data/DBConfig.xml
+++ b/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>
\ No newline at end of file
diff --git a/CCSTrace/CCS/CCSMain.cs b/CCSTrace/CCS/CCSMain.cs
index a9a480b..783c7e1 100644
--- a/CCSTrace/CCS/CCSMain.cs
+++ b/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)
{
@@ -80,6 +78,32 @@
AddrContrast.Instance.Initialize(_mainConn);
AlarmData.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 +112,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 +124,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 +196,7 @@
switch (reader.NodeType)
{
case XmlNodeType.Element:
- string localName = reader.LocalName; // 取得標籤名稱
+ var localName = reader.LocalName; // 取得標籤名稱
// Step 3: 讀取 FileInfo 標籤的屬性
if (localName.Equals("DBSetting"))
@@ -214,7 +205,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 +215,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 +244,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 +319,72 @@
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);
}
+ 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 +407,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 +422,10 @@
{
Logger.Info("更新EVENTQUERY的案件狀態為處理中.(CCSID = " + ccsEventRecord.CcsId + ")");
transaction.Commit();
- _processingCases.Add(ccsEventRecord.CcsId);
+ lock (_syncProcessingCases)
+ {
+ _processingCases.Add(ccsEventRecord.CcsId);
+ }
}
else
{
@@ -463,5 +447,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);
+ }
+ }
}
}
\ No newline at end of file
diff --git a/CCSTrace/CCS/FinishEvent.cs b/CCSTrace/CCS/FinishEvent.cs
index 011554a..5fd6565 100644
--- a/CCSTrace/CCS/FinishEvent.cs
+++ b/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,19 @@
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 conn1, OracleTransaction trx1, EOSEventRecord eosEventRecord, int mKind)
{
- }
-
- public bool Finish(int mKind, Object.EOSEventRecord eosEventRecord, OracleConnection conn, OracleTransaction trx)
- {
- _eosEventRecord = eosEventRecord;
- _connectionTpc = conn;
- _transaction = trx;
+ var conn = conn1;
+ var trx = trx1;
Logger.Info("進行OMS資料更新程序...");
if (eosEventRecord.ParentId == 0)
{
// Original Case
- if (!(ProcessSingalCase(eosEventRecord)))
+ if (!ProcessSingalCase(conn, trx, eosEventRecord))
{
return false;
}
@@ -38,7 +30,7 @@
else
{
// Child Case
- if (!(ProcessMergeCase(eosEventRecord)))
+ if (!ProcessMergeCase(conn, trx, eosEventRecord))
{
return false;
}
@@ -46,7 +38,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 +64,7 @@
/* 當饋線全停時不需注意影響虛擬用戶，影響用戶資料會由實際切開關產生 */
if (eosEventRecord.Fsc != GlobalVariable.Breaker)
{
- if (!SetVisualCustomer(eosEventRecord))
+ if (!SetVisualCustomer(conn, trx, eosEventRecord))
{
Logger.Error("新增影響用戶資料錯誤.");
return false;
@@ -80,16 +72,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 +90,7 @@
if (mKind == GlobalVariable.CaseTypeChanged)
{
- if (!DeleteOldCase(eosEventRecord))
+ if (!DeleteOldCase(conn, trx, eosEventRecord))
{
Logger.Error("案件轉換發生錯誤");
return false;
@@ -106,7 +98,7 @@
Logger.Info("案件轉換成功!");
}
- if (!SaveCustomerTel(eosEventRecord))
+ if (!SaveCustomerTel(conn, trx, eosEventRecord))
{
Logger.Error("無法將用戶電話回存回資料庫.");
return false;
@@ -115,11 +107,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 +125,7 @@
if (eosEventRecord.ParentId == 0)
{
- if (!InsertIntoSri(eosEventRecord))
+ if (!InsertIntoSri(conn, trx, eosEventRecord))
{
Logger.Error("新增資料庫(OCSDB.SRI)錯誤.");
return false;
@@ -154,13 +147,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 +174,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 +193,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 +220,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 +244,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 +282,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 +291,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 +340,7 @@
try
{
- command1 = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
+ command1 = new OracleCommand(sqlStmt, conn, trx);
reader = command1.ExecuteReader();
if (reader.Read())
@@ -399,10 +395,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 +410,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 +426,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 +449,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 +477,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 +500,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 +528,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 +540,7 @@
}
sqlStmt = "update eos.events set casestatus=" + CCSCaseState.WaitForSponsor + " where caseid=" +
- newCaseId;
+ eosEventRecord.CaseId;
command.CommandText = sqlStmt;
if (command.ExecuteNonQuery() < 0)
{
@@ -550,12 +549,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 +564,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 +576,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 +591,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 +602,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 +613,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 +631,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 +655,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 +676,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 +696,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 +758,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 +787,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 +944,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 +1003,7 @@
return true;
}
- private bool SaveCustomerTel(Object.EOSEventRecord eosEventRecord)
+ private bool SaveCustomerTel(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)
{
// 無電號資料
/*
@@ -1031,7 +1026,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
{
diff --git a/CCSTrace/CCS/Function/MailService.cs b/CCSTrace/CCS/Function/MailService.cs
index 405297b..a43bc02 100644
--- a/CCSTrace/CCS/Function/MailService.cs
+++ b/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();
diff --git a/CCSTrace/CCS/GlobalVariable.cs b/CCSTrace/CCS/GlobalVariable.cs
index 734de24..1ac1a7d 100644
--- a/CCSTrace/CCS/GlobalVariable.cs
+++ b/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;
}
}
\ No newline at end of file
diff --git a/CCSTrace/CCS/ProcessEvent.cs b/CCSTrace/CCS/ProcessEvent.cs
index 6e08771..66b1e63 100644
--- a/CCSTrace/CCS/ProcessEvent.cs
+++ b/CCSTrace/CCS/ProcessEvent.cs
@@ -10,45 +10,31 @@
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, trx, 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); //將要處理的案件的饋線別加入處理中饋線集合
}
/*
@@ -73,62 +59,64 @@
break;
}
*/
- if (InitialEventRecord(_mCcsEventRecord.CcsId))
+
+ eosEventRecord = InitialEventRecord(conn, trx, ccsEventRecord);
+ if (eosEventRecord != null)
{
- 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,19 +128,19 @@
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;
@@ -236,70 +224,74 @@
}
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)
+ 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 +303,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 +321,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 +348,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 +357,79 @@
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)
+ var deptContrast = new DeptContrast(conn, 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.getDept_Code(ccsEventRecord.Meter);
if (tmp == -1)
- _eosEventRecord.Dept = FindoutDeptByAddr(_mCcsEventRecord, _trx);
+ eosEventRecord.Dept = FindoutDeptByAddr(trx, ccsEventRecord);
else
- _eosEventRecord.Dept = tmp;
+ eosEventRecord.Dept = tmp;
- _eosEventRecord.FdrId = GetFdrid(_mCcsEventRecord.Meter);
+ eosEventRecord.FdrId = GetFdrid(conn, trx, 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 +440,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 +506,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 +535,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 +558,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 +586,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 +622,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 +657,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 +708,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 +780,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 +806,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 +819,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 +855,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,10 +881,10 @@
return maxLength;
}
- private int GetFdrid(string meter)
+ private int GetFdrid(OracleConnection conn, OracleTransaction trx, 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, trx);
var reader = command.ExecuteReader();
var tpclid = "";
var fdrid = 0;
@@ -904,14 +902,16 @@
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 +924,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 +948,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 +973,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 +981,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
{
diff --git a/CCSTrace/DBConfig.xml b/CCSTrace/DBConfig.xml
index ad9b2f1..b1fa877 100644
--- a/CCSTrace/DBConfig.xml
+++ b/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>
\ No newline at end of file
--
Gitblit v0.0.0-SNAPSHOT