using System;
|
using System.Data.OracleClient;
|
using System.Text;
|
using CCSTrace.CCS.Domain;
|
using CCSTrace.CCS.Function;
|
using CCSTrace.CCS.Object;
|
using NLog;
|
|
namespace CCSTrace.CCS
|
{
|
public delegate void ThreadEndEventHandler(object sender, ThreadEndEvent e);
|
|
public class ProcessEvent
|
{
|
private static Logger logger = LogManager.GetCurrentClassLogger();
|
|
private readonly OracleConnection _connectionTpc;
|
private readonly CcsRecord _mCcsRecord;
|
|
private EventRecord _mEventRecord;
|
private readonly RecordLog _pLog;
|
private readonly string _traceConnectionString = string.Empty;
|
private OracleTransaction _trx;
|
|
public ProcessEvent(CcsRecord record, OracleConnection conn, string traceConnection)
|
{
|
_mCcsRecord = record;
|
_connectionTpc = conn;
|
_traceConnectionString = traceConnection;
|
_pLog = new RecordLog(GlobalVariable.CcsListPath + record.CcsId + ".txt");
|
}
|
|
public event ThreadEndEventHandler ThreadFinish;
|
|
public void Run()
|
{
|
var sendMail = false;
|
|
try
|
{
|
var fdrid = GetFdrid(_mCcsRecord.Meter);
|
|
if (fdrid != 0) //有饋線代號資料的案件才需比對同饋線上有無案件正在處理
|
{
|
if (CcsMain.ProcessFdr.ContainsKey(fdrid.ToString()))
|
{
|
CcsMain.WaitingCases.Add(new[] {_mCcsRecord.CcsId, fdrid.ToString()});
|
return;
|
}
|
else
|
CcsMain.ProcessFdr.Add(fdrid.ToString(), null); //將要處理的案件的饋線別加入處理中饋線集合
|
}
|
|
/* int TraceCount = this.getTraceCount(m_CCSRecord.CcsId);
|
bool TraceError = false; //追蹤次數超過3次，改為非A類不追蹤合併
|
|
switch (TraceCount)
|
{
|
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(_mCcsRecord.CcsId))
|
{
|
var mLeach = new Leach(_connectionTpc, _trx, _traceConnectionString, _pLog); // 案件過濾與合併
|
_pLog.Info("過濾案件...");
|
var returnStatus = mLeach.LeachCase(_mEventRecord);
|
|
switch (returnStatus)
|
{
|
case GlobalVariable.Success:
|
_pLog.Info("案件過濾完畢....");
|
Process(GlobalVariable.Success);
|
break;
|
|
case GlobalVariable.NoMeter:
|
_pLog.Info("案件過濾完畢,用戶無電號....");
|
_mEventRecord.TraceFinish = GlobalVariable.TraceFinish;
|
Process(GlobalVariable.NoMeter);
|
break;
|
|
case GlobalVariable.SameCustomer:
|
_pLog.Info("相同用戶重複來電....");
|
ProcessSameCustomer(_mCcsRecord, _mEventRecord);
|
break;
|
|
case GlobalVariable.NoSupplyElc:
|
_pLog.Warn("該饋線未供電,無法作事故案件追蹤合併....");
|
_mEventRecord.TraceFinish = GlobalVariable.TraceFinish;
|
Process(GlobalVariable.NoMeter);
|
break;
|
|
case GlobalVariable.NoSxfmr:
|
_pLog.Warn("該案件無變壓器....");
|
_mEventRecord.TraceFinish = GlobalVariable.TraceFinish;
|
Process(GlobalVariable.NoMeter);
|
break;
|
|
case GlobalVariable.CaseTypeChanged:
|
_pLog.Info("變更案件狀態....");
|
Process(GlobalVariable.CaseTypeChanged);
|
break;
|
|
case GlobalVariable.TraceFailure:
|
_pLog.Error("故障追蹤錯誤，以一般無電號案件方式受理....");
|
_mEventRecord.TraceFinish = GlobalVariable.TraceFinish;
|
Process(GlobalVariable.NoMeter);
|
break;
|
|
default:
|
var traceCount = GetTraceCount(_mCcsRecord.CcsId);
|
|
if (traceCount < 2)
|
{
|
if (_trx.Connection.State.ToString().Equals("Open"))
|
_trx.Rollback();
|
|
_trx.Dispose();
|
|
if (returnStatus == GlobalVariable.CaseTransfer)
|
_pLog.Error("案件已移轉，此事故案件暫不被受理....");
|
else if (returnStatus == GlobalVariable.FailureByDb)
|
_pLog.Error("資料庫錯誤，此事故案件暫不被受理....");
|
else if (returnStatus == GlobalVariable.FdrLocked)
|
_pLog.Error("饋線被鎖定，此事故案件暫不被受理....");
|
else
|
_pLog.Error("發生非預期錯誤，此事故案件暫不被受理....");
|
|
if (traceCount == 0)
|
UpdateTraceCount(_mCcsRecord.CcsId, true);
|
else
|
UpdateTraceCount(_mCcsRecord.CcsId, false);
|
}
|
else
|
{
|
UpdateTraceCount(_mCcsRecord.CcsId, false);
|
if (returnStatus == GlobalVariable.TraceCounts3)
|
_mEventRecord.Note = _mEventRecord.Note+ "(本案用戶供電資料追蹤失敗)";
|
|
_pLog.Error("事故案件處理次數3次，仍無法正常處理，以一般無電號案件方式受理....");
|
_mEventRecord.TraceFinish = GlobalVariable.TraceFinish;
|
Process(GlobalVariable.NoMeter);
|
sendMail = true;
|
}
|
break;
|
/* switch (TraceCount)
|
{
|
case 0:
|
if (_Trx.Connection.State.ToString().Equals("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.ToString().Equals("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.ToString().Equals("Open"))
|
_Trx.Rollback();
|
|
_Trx.Dispose();
|
_PLog.Error("案件已移轉，此事故案件不被受理....");
|
break;
|
|
case CCS.GlobalVariable.FDRLocked:
|
if (_Trx.Connection.State.ToString().Equals("Open"))
|
_Trx.Rollback();
|
|
_Trx.Dispose();
|
_PLog.Error("饋線被鎖定，此事故案件不被受理....");
|
break;
|
|
case CCS.GlobalVariable.Failure_By_DB:
|
if (_Trx.Connection.State.ToString().Equals("Open"))
|
_Trx.Rollback();
|
|
_Trx.Dispose();
|
_PLog.Error("資料庫錯誤，此事故案件不被受理....");
|
break;
|
|
default:
|
if (_Trx.Connection.State.ToString().Equals("Open"))
|
_Trx.Rollback();
|
|
_Trx.Dispose();
|
_PLog.Error("此事故案件不被受理....");
|
break;
|
*/
|
}
|
}
|
|
if (fdrid != 0)
|
CcsMain.ProcessFdr.Remove(fdrid.ToString()); //將處理完的案件的饋線別從處理中饋線集合中移除
|
}
|
catch (Exception e)
|
{
|
Console.WriteLine("Error = " + e.Message + ". CCSID = " + _mCcsRecord.CcsId);
|
_pLog.Error("Error = " + e.Message + ". CCSID = " + _mCcsRecord.CcsId);
|
|
if (_trx != null)
|
{
|
if (_trx.Connection.State.ToString().Equals("Open"))
|
_trx.Rollback();
|
|
_trx.Dispose();
|
}
|
|
if (_mEventRecord != null && _mEventRecord.FdrId!= 0)
|
CcsMain.ProcessFdr.Remove(_mEventRecord.FdrId.ToString()); //將處理完的案件的饋線別從處理中饋線集合中移除
|
//throw e;
|
}
|
finally
|
{
|
_pLog.Close();
|
CcsMain.ProcessCases.Remove(_mCcsRecord.CcsId);
|
OnThreadFinish(new ThreadEndEvent(_connectionTpc));
|
}
|
|
if (sendMail)
|
new MailService(_connectionTpc).SendMail(_mCcsRecord.CcsId);
|
}
|
|
private void Process(int kind)
|
{
|
var finishEvent = new FinishEvent();
|
|
try
|
{
|
if (finishEvent.Finish(kind, _mEventRecord, _connectionTpc, _trx, _pLog))
|
{
|
_pLog.Info("OMS資料更新成功!");
|
if (UpdateCcsData(_mEventRecord, _mCcsRecord, (int) CCSCaseState.WaitForDespatch))
|
{
|
_pLog.Info("CCS資料更新成功!");
|
}
|
else
|
{
|
if (_trx.Connection.State.ToString().Equals("Open"))
|
_trx.Rollback();
|
|
_trx.Dispose();
|
_pLog.Error("CCS資料更新程序發生錯誤!");
|
return;
|
}
|
|
// 須等全部動作都正確做完才可作commit的動作
|
_trx.Commit();
|
_trx.Dispose();
|
;
|
var alarmClient = new AlarmData(_connectionTpc, _trx, _pLog);
|
alarmClient.Alarm(_mEventRecord.AcceptNum, _mEventRecord.Dept);
|
}
|
else
|
{
|
// 有錯誤需要RollBack
|
if (_trx.Connection.State.ToString().Equals("Open"))
|
_trx.Rollback();
|
|
_trx.Dispose();
|
;
|
_pLog.Error("OMS資料更新程序發生錯誤!");
|
}
|
_pLog.Info("CCS 案件受理程序結束.");
|
}
|
catch (Exception e)
|
{
|
throw e;
|
}
|
}
|
|
protected virtual void OnThreadFinish(ThreadEndEvent e)
|
{
|
if (ThreadFinish != null)
|
{
|
ThreadFinish(this, e);
|
}
|
}
|
|
private bool UpdateCcsData(EventRecord mRecord, CcsRecord mCcsRecord, int caseStatus)
|
{
|
_pLog.Info("進行CCS資料更新程序...");
|
var eventQuery = new EventQuery();
|
var status = caseStatus;
|
// 設定CCSID
|
eventQuery.CcsId = mCcsRecord.CcsId;
|
// 設定受理時間
|
eventQuery.ChangeTime = mCcsRecord.AcceptTime;
|
// 設定用戶電號
|
if (mCcsRecord.Meter != null)
|
{
|
eventQuery.Meter = mCcsRecord.Meter;
|
}
|
// 如果該案件為子案件,需找出母案件的casestatus來insert eos.eventquery
|
// 若母案件已派工,需一併紀錄預計復電時間等資訊
|
if (mRecord.ParentId!= 0)
|
{
|
status = GetRealCaseStatus(mRecord.ParentId) == (int) CCSCaseState.WaitForSponsor
|
? (int) CCSCaseState.EventDespatched
|
: (int) CCSCaseState.WaitForDespatch;
|
if (status == (int) CCSCaseState.EventDespatched)
|
{
|
SetDespatchInfo(mRecord.ParentId, eventQuery);
|
}
|
}
|
// 若此案件為專案案件升級為母案件,則必須判斷原先案件是否已經派工
|
if ((mCcsRecord.ImportCase == GlobalVariable.IsImportCase) && mRecord.IsDespatched)
|
{
|
status = (int) CCSCaseState.EventDespatched;
|
SetDespatchInfo(mRecord.CaseId, eventQuery);
|
}
|
eventQuery.CaseStatus = status;
|
// 更新CCS.EventQuery
|
if (!eventQuery.Update(_pLog, _connectionTpc, _trx))
|
{
|
_pLog.Error("更新ccs.eventquery失敗!");
|
return false;
|
}
|
_pLog.Info("更新ccs.eventquery成功!");
|
var numberContrast = new NumberContrast();
|
numberContrast.SetAcceptNum(mRecord.AcceptNum);
|
numberContrast.SetCaseId(mRecord.CaseId);
|
numberContrast.SetCcsid(mCcsRecord.CcsId);
|
// 更新CCS.Num_Contrast
|
if (!numberContrast.Update(_connectionTpc, _trx))
|
{
|
_pLog.Error("更新ccs.num_contrast失敗!");
|
return false;
|
}
|
_pLog.Info("更新ccs.num_contrast成功!");
|
return true;
|
}
|
|
private bool InitialEventRecord(string ccsid)
|
{
|
var initialData = new InitialEventData(_connectionTpc, _pLog);
|
var mContrast = new NumberContrast();
|
DeptContrast deptContrast = null;
|
|
// Being Transaction
|
_trx = _connectionTpc.BeginTransaction();
|
|
deptContrast = new DeptContrast(_connectionTpc, _trx);
|
_mEventRecord = new EventRecord(initialData.GetNewCaseId(), _connectionTpc, _trx);
|
_mEventRecord.AcceptNum = initialData.GetNewAcceptNum();
|
|
mContrast.SetAcceptNum(_mEventRecord.AcceptNum);
|
mContrast.SetCaseId(_mEventRecord.CaseId);
|
mContrast.SetCcsid(_mCcsRecord.CcsId);
|
|
if (!mContrast.Insert(_connectionTpc, _trx))
|
{
|
_pLog.Error("初始化寫入CCS.Num_Contrast時發生錯誤！");
|
|
if (_trx.Connection.State.ToString().Equals("Open"))
|
_trx.Rollback();
|
|
return false;
|
}
|
|
// 設定用戶姓名
|
_mEventRecord.Name = _mCcsRecord.CustomerName;
|
|
if (_mCcsRecord.Meter != null && _mCcsRecord.Meter.Length != 0)
|
{
|
// 需先找出部門代號,再找到該部門屬於哪一個巡修單位
|
_mEventRecord.Meter = _mCcsRecord.Meter;
|
var tmp = deptContrast.getDept_Code(_mCcsRecord.Meter);
|
|
if (tmp == -1)
|
_mEventRecord.Dept = FindoutDeptByAddr(_mCcsRecord, _trx);
|
else
|
_mEventRecord.Dept = tmp;
|
|
_mEventRecord.FdrId = GetFdrid(_mCcsRecord.Meter);
|
}
|
else
|
{
|
// 當無用戶電號時,所找出的部門別不需再去找出屬於哪一個巡修股(findoutDeptByAddr會直接找到相對應的巡修股)
|
var dept = FindoutDeptByAddr(_mCcsRecord, _trx);
|
_mEventRecord.Dept = dept;
|
}
|
// 設定用戶電話號碼
|
if (_mCcsRecord.CustomerTel != null)
|
{
|
_mEventRecord.Tel = _mCcsRecord.CustomerTel;
|
}
|
// 檢查並設定用戶地址
|
var mAddr = _mCcsRecord.AddressCity + _mCcsRecord.AddressTown + _mCcsRecord.AddressRoad +
|
_mCcsRecord.AddressOther;
|
|
if (mAddr.Trim().Length == 0)
|
{
|
_pLog.Error("CCS 案件受理程序初始化失敗...地址為空白");
|
|
if (_trx.Connection.State.ToString().Equals("Open"))
|
_trx.Rollback();
|
|
return false;
|
}
|
|
_mEventRecord.Addr = mAddr;
|
// 設定事故原因
|
_mEventRecord.Brief = TransferEventBrief(_mCcsRecord.EventBrief);
|
try
|
{
|
var encoding = new ASCIIEncoding();
|
var source = encoding.GetBytes(_mEventRecord.Brief);
|
|
if (source.Length > 24)
|
{
|
var dest = new byte[24];
|
for (var i = 0; i < dest.Length; i++)
|
{
|
dest[i] = source[i];
|
}
|
|
var enc = Encoding.ASCII;
|
_mEventRecord.Brief = enc.GetString(dest);
|
source = dest = null;
|
}
|
}
|
catch (Exception)
|
{
|
_mEventRecord.Brief = "其他";
|
}
|
|
if (_mCcsRecord.CcsId.StartsWith("A"))
|
_mEventRecord.Log = "APP";
|
else if (_mCcsRecord.CcsId.StartsWith("W"))
|
_mEventRecord.Log = "WEB";
|
else
|
_mEventRecord.Log = "CCS";
|
|
_mEventRecord.AcceptDate = _mCcsRecord.AcceptTime;
|
|
// 設定用戶種類
|
if (_mCcsRecord.ImportCase == GlobalVariable.IsImportCase)
|
{
|
_mEventRecord.ImportCase = GlobalVariable.IsImportCase;
|
_mEventRecord.Level = GlobalVariable.ALevel; // default set A level
|
}
|
else
|
{
|
_mEventRecord.ImportCase = GlobalVariable.NotImportCase;
|
_mEventRecord.Level = GlobalVariable.NoLevel;
|
}
|
|
// 檢查字串中有無特殊字元' ,若有則取代為"
|
if (_mCcsRecord.Note != null)
|
_mEventRecord.Note = _mCcsRecord.Note.Replace('\'', '\"');
|
|
// 設定回覆姓名及電話
|
if (_mCcsRecord.RecallName != null)
|
{
|
_mEventRecord.IsReCall = GlobalVariable.IsReCall;
|
_mEventRecord.ReCallName = _mCcsRecord.RecallName.Replace('\'', '\"');
|
_mEventRecord.ReCallTel = _mCcsRecord.RecallTel;
|
}
|
else
|
{
|
_mEventRecord.IsReCall = GlobalVariable.NotReCall;
|
}
|
|
_pLog.Info("初始化EOS.EVENTRECORD成功!");
|
return true;
|
}
|
|
//private bool initialCCSInfo(CCS.Object.EventQuery m_EventQuery, CCS.Object.NumberContrast m_Contrast)
|
//{
|
// checkCaseAlreadyExits(m_EventQuery);
|
// if (!m_EventQuery.Insert(_PLog, _ConnectionTPC, _Trx))
|
// {
|
// _PLog.Error("初始化寫入CCS.EventQuery時發生錯誤！");
|
// return false;
|
// }
|
// if (!m_Contrast.Insert(_ConnectionTPC, _Trx))
|
// {
|
// _PLog.Error("初始化寫入CCS.Num_Contrast時發生錯誤！");
|
// return false;
|
// }
|
// return true;
|
//}
|
|
private void CheckCaseAlreadyExits(EventQuery mEventQuery)
|
{
|
var sqlStmt = "select ccsid from ccs.eventquery where ccsid='" + mEventQuery.CcsId + "'";
|
|
var command = new OracleCommand(sqlStmt, _connectionTpc, _trx);
|
var reader = command.ExecuteReader();
|
|
try
|
{
|
if (reader.Read())
|
{
|
command.CommandText = "delete from ccs.eventquery where ccsid='" + mEventQuery.CcsId + "'";
|
command.ExecuteNonQuery();
|
|
command.CommandText = "delete from ccs.num_contrast where ccsid='" + mEventQuery.CcsId + "'";
|
command.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
_pLog.Warn("Problems occur during checking process: " + ex.Message);
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
}
|
|
private int FindoutDeptByAddr(CcsRecord ccsRecord, OracleTransaction trx)
|
{
|
var addrContrast = new AddrContrast(_connectionTpc, trx, _pLog);
|
return addrContrast.FindDeptId(ccsRecord.AddressCity, ccsRecord.AddressTown, ccsRecord.AddressRoad);
|
}
|
|
private string TransferEventBrief(int code)
|
{
|
var result = "";
|
result = GlobalVariable.CcsCodelist.GetContent(GlobalVariable.CcsEventBrief, code);
|
|
// int MaxLength = this.getFieldLength("EOS", "EVENTRECORD", "EVENTBRIEF");
|
|
// if ( Result.ToCharArray().Length > MaxLength )
|
// Result.ToCharArray
|
|
return result;
|
}
|
|
private int TransferDept(string deptCode)
|
{
|
// 傳入部門的英文value(CONTENT),傳回相對應的keyid
|
return GlobalVariable.CcsCodelist.GetKeyId(GlobalVariable.CcsDept, deptCode);
|
}
|
|
private int GetRealCaseStatus(int parentId)
|
{
|
string sqlStmt;
|
var status = (int) CCSCaseState.WaitForDespatch;
|
|
sqlStmt = "SELECT CASESTATUS FROM EOS.EVENTS WHERE CASEID = " + parentId;
|
var command = new OracleCommand(sqlStmt, _connectionTpc, _trx);
|
var reader = command.ExecuteReader();
|
|
try
|
{
|
if (reader.Read())
|
{
|
status = Convert.ToInt32(reader["CaseStatus"].ToString());
|
}
|
}
|
catch (Exception)
|
{
|
_pLog.Warn("無法取得正確之母案件狀態.");
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
return status;
|
}
|
|
private void SetDespatchInfo(int mCaseId, EventQuery mEventQry)
|
{
|
var sqlStmt = "select despatchtime,assumefixtime,assumedtime_nth,delaytime,reason,eventlocation from eos.eventdespatch where caseid="
|
+ mCaseId;
|
|
OracleCommand command = null;
|
OracleDataReader reader = null;
|
|
try
|
{
|
command = new OracleCommand(sqlStmt, _connectionTpc, _trx);
|
reader = command.ExecuteReader();
|
|
if (reader.Read())
|
{
|
mEventQry.ChangeTime = Convert.ToDateTime(reader["despatchtime"]).ToString("yyy/MM/dd HH:mm:ss");
|
mEventQry.AssumedTime = Convert.ToDateTime(reader["assumefixtime"]).ToString("yyy/MM/dd HH:mm:ss");
|
mEventQry.AssumedTimeNth =
|
Convert.ToDateTime(reader["assumedtime_nth"]).ToString("yyy/MM/dd HH:mm:ss");
|
mEventQry.DelayTimes = Convert.ToInt32(reader["delaytime"].ToString());
|
mEventQry.Reason = reader["reason"].ToString();
|
mEventQry.EventLocation = reader["eventlocation"].ToString();
|
}
|
}
|
catch (Exception)
|
{
|
_pLog.Warn("無法取得母案件派工資訊.");
|
}
|
finally
|
{
|
if (reader != null)
|
reader.Close();
|
|
if (command != null)
|
command.Dispose();
|
}
|
}
|
|
private void ProcessSameCustomer(CcsRecord mCcsRecord, EventRecord mEventRec)
|
{
|
_pLog.Info("處理相同用戶重複來電...");
|
StringBuilder note = null;
|
|
var sqlStmt = "select note from eos.eventrecord where caseid = " + mEventRec.TmpCaseId;
|
var command = new OracleCommand(sqlStmt, _connectionTpc, _trx);
|
var reader = command.ExecuteReader();
|
|
try
|
{
|
if (reader.Read())
|
note = new StringBuilder(reader["note"] == null ? "" : reader["note"].ToString().Trim());
|
}
|
catch (Exception e)
|
{
|
Console.WriteLine(e.Message);
|
Console.WriteLine(e.StackTrace);
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
|
if (!note.ToString().EndsWith("|") && !note.ToString().Trim().Equals(""))
|
note.Append(" | ");
|
|
if ((mEventRec.Note!= null) && (mEventRec.Note.Length != 0))
|
note.Append(mEventRec.AcceptDate+ " " + mEventRec.Note);
|
else
|
note.Append(mEventRec.AcceptDate+ " 用戶再次報案");
|
|
// 判斷重複來電的用戶最初成立的案件是否是由CCS受理
|
var acceptByCcs = false;
|
|
var sqlStmt1 = "select count(*) as count from ccs.num_contrast c,ccs.eventquery q where c.ccsid=q.ccsid and caseid="
|
+ mEventRec.TmpCaseId;
|
var command1 = new OracleCommand(sqlStmt1, _connectionTpc, _trx);
|
var reader1 = command1.ExecuteReader();
|
|
try
|
{
|
if (reader1.Read())
|
{
|
if (Convert.ToInt32(reader1["count"].ToString()) != 0)
|
{
|
acceptByCcs = true;
|
}
|
}
|
}
|
catch (Exception)
|
{
|
}
|
finally
|
{
|
reader1.Close();
|
command1.Dispose();
|
}
|
|
string updCcsEventQuery = null;
|
// 若先前案件由CCS受理,則直接從ccs.eventquery取出先前的案件狀態資料寫入
|
if (acceptByCcs)
|
{
|
updCcsEventQuery = "update ccs.eventquery set (casestatus,changetime,handlingsummary,assumedtime,assumedtime_nth,delaytimes,reason,eventlocation) = (select casestatus,changetime,handlingsummary,assumedtime,assumedtime_nth,delaytimes,reason,eventlocation from ccs.eventquery where ccsid=(select ccsid from ccs.num_contrast where caseid="
|
+ mEventRec.TmpCaseId+ " and rownum<2)) where CCSID='" + mCcsRecord.CcsId + "'";
|
// 若非由CCS受理(OMS,Web)則從EOS的table中尋找先前案件的狀態資料
|
}
|
else
|
{
|
string accepttime = null,
|
despatchtime = null,
|
sponsortime = null,
|
assumefixtime = null,
|
assumedtimeNth = null,
|
changetime = null,
|
handling = null,
|
reason = null,
|
eventlocation = null;
|
int casestatus = 0, delaytime = 0;
|
|
OracleCommand command2 = null;
|
OracleDataReader reader2 = null;
|
|
try
|
{
|
var sqlStmt2 = "select 1,to_char(r.accepttime,'YYYY/MM/DD HH24:MI:SS') as ACCEPTTIME,to_char(d.despatchtime,'YYYY/MM/DD HH24:MI:SS') as DESPATCHTIME,to_char(s.reconditiontime,'YYYY/MM/DD HH24:MI:SS')SPONSORTIME,e.casestatus,s.handling,to_char(d.assumefixtime,'YYYY/MM/DD HH24:MI:SS') as assumefixtime,to_char(d.assumedtime_nth,'YYYY/MM/DD HH24:MI:SS') as assumedtime_nth,d.delaytime,d.reason,d.eventlocation from eos.eventrecord r,eos.events e,eos.eventdespatch d,eos.eventsponsor s where (r.caseid=e.caseid) and (r.caseid=d.caseid(+)) and (r.caseid=s.caseid(+)) and r.caseid="
|
+ mEventRec.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="
|
+ mEventRec.TmpCaseId+ ")";
|
|
command2 = new OracleCommand(sqlStmt2, _connectionTpc, _trx);
|
reader2 = command2.ExecuteReader();
|
|
while (reader2.Read())
|
{
|
accepttime = reader2["ACCEPTTIME"].ToString();
|
despatchtime = reader2["DESPATCHTIME"].ToString();
|
sponsortime = reader2["SPONSORTIME"].ToString();
|
assumefixtime = reader2["ASSUMEFIXTIME"] == null ? "" : reader2["ASSUMEFIXTIME"].ToString();
|
assumedtimeNth = reader2["ASSUMEDTIME_NTH"] == null ? "" : reader2["ASSUMEDTIME_NTH"].ToString();
|
handling = reader2["HANDLING"] == null ? "" : reader2["HANDLING"].ToString();
|
reason = reader2["REASON"] == null ? "" : reader2["REASON"].ToString();
|
casestatus = Convert.ToInt32(reader2["CASESTATUS"].ToString());
|
delaytime = reader2["DELAYTIME"].ToString().Length == 0
|
? 0
|
: Convert.ToInt32(reader2["DELAYTIME"].ToString());
|
eventlocation = reader2["EVENTLOCATION"] == null ? "" : reader2["EVENTLOCATION"].ToString();
|
}
|
|
if (accepttime != null)
|
{
|
changetime = accepttime;
|
}
|
if (!string.IsNullOrEmpty(despatchtime))
|
{
|
changetime = despatchtime;
|
}
|
if (!string.IsNullOrEmpty(sponsortime))
|
{
|
changetime = sponsortime;
|
}
|
updCcsEventQuery = "update ccs.eventquery set casestatus=" + casestatus + ",changetime=to_date('" +
|
changetime
|
+ "','YYYY/MM/DD HH24:MI:SS'),handlingsummary='" + handling +
|
"',assumedtime=to_date('" + assumefixtime
|
+ "','YYYY/MM/DD HH24:MI:SS'),assumedtime_nth=to_date('" + assumedtimeNth +
|
"','YYYY/MM/DD HH24:MI:SS'),delaytimes="
|
+ delaytime + ",reason='" + reason + "',eventlocation='" + eventlocation +
|
"' where ccsid='" + mCcsRecord.CcsId + "'";
|
}
|
catch (Exception e)
|
{
|
Console.WriteLine(e.Message);
|
}
|
finally
|
{
|
if (reader2 != null)
|
reader2.Close();
|
|
if (command2 != null)
|
command2.Dispose();
|
}
|
}
|
|
var updEosEventRecord = "update eos.eventrecord set note = '" + TrimToLimit(note.ToString(), 127)
|
+ "' where caseid = " + _mEventRecord.TmpCaseId;
|
var updCcsNumContrast = "update ccs.num_contrast set(acceptnum,caseid) = (select acceptnum," +
|
mEventRec.TmpCaseId + " from eos.eventrecord where caseid = " + mEventRec.TmpCaseId+
|
") where ccsid='" + mCcsRecord.CcsId + "'";
|
|
var exeCommand = new OracleCommand(updCcsEventQuery, _connectionTpc, _trx);
|
// 將用戶重複來電案件資料寫回CCS.EventQuery
|
if (exeCommand.ExecuteNonQuery() <= 0)
|
{
|
exeCommand.Dispose();
|
|
if (_trx.Connection.State.ToString().Equals("Open"))
|
_trx.Rollback();
|
|
_trx.Dispose();
|
_pLog.Error("Fail in write back to ccs.eventquery");
|
return;
|
}
|
|
exeCommand.CommandText = updEosEventRecord;
|
// 將CCS案件備註資料Append到EOS.EventRecord
|
if (exeCommand.ExecuteNonQuery() <= 0)
|
{
|
exeCommand.Dispose();
|
|
if (_trx.Connection.State.ToString().Equals("Open"))
|
_trx.Rollback();
|
|
_trx.Dispose();
|
_pLog.Error("Fail in update eos.eventquery");
|
return;
|
}
|
|
exeCommand.CommandText = updCcsNumContrast;
|
// 寫入ccsid <=> caseid 到CCS.Num_Contrast
|
if (exeCommand.ExecuteNonQuery() <= 0)
|
{
|
exeCommand.Dispose();
|
if (_trx.Connection.State.ToString().Equals("Open"))
|
_trx.Rollback();
|
|
_trx.Dispose();
|
_pLog.Error("Fail in insert into ccs.num_contrast");
|
return;
|
}
|
|
exeCommand.Dispose();
|
_trx.Commit();
|
_trx.Dispose();
|
_pLog.Info("CCS 案件受理程序結束");
|
}
|
|
private string TrimToLimit(string msg, int maxLen)
|
{
|
try
|
{
|
if (msg.Length <= 1)
|
{
|
return "";
|
}
|
if (msg.Length <= maxLen)
|
{
|
return msg;
|
}
|
msg = msg.Substring(msg.IndexOf("|") + 1);
|
return TrimToLimit(msg, maxLen);
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine(ex.StackTrace);
|
return "";
|
}
|
}
|
|
private int GetFieldLength(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 reader = command.ExecuteReader();
|
var maxLength = -1;
|
|
try
|
{
|
if (reader.Read())
|
maxLength = Convert.ToInt32(reader["DATA_LENGTH"].ToString());
|
}
|
catch (Exception e)
|
{
|
Console.WriteLine(e.Message);
|
Console.WriteLine(e.StackTrace);
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
|
return maxLength;
|
}
|
|
private int GetFdrid(string meter)
|
{
|
var sqlStmt = "SELECT TRAN_CORD FROM BASEDB.METER WHERE METR_NUMB = '" + meter + "'";
|
var command = new OracleCommand(sqlStmt, _connectionTpc, _trx);
|
var reader = command.ExecuteReader();
|
var tpclid = "";
|
var fdrid = 0;
|
var group = "";
|
try
|
{
|
if (reader.Read())
|
tpclid = reader["TRAN_CORD"].ToString();
|
|
if (tpclid.Length == 0)
|
return fdrid;
|
|
if (tpclid.Length > 11)
|
{
|
group = tpclid.Substring(tpclid.Length - 3);
|
tpclid = tpclid.Substring(0, 11).Trim();
|
sqlStmt =
|
"SELECT FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = 115 AND UFID = (SELECT UFID FROM BASEDB.SXFMR WHERE OWNERTPCLID = '" +
|
tpclid
|
+ "' AND GROUP1='" + group + "' AND ROWNUM < 2)";
|
}
|
else
|
{
|
sqlStmt =
|
"SELECT FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = 115 AND UFID = (SELECT UFID FROM BASEDB.SXFMR WHERE OWNERTPCLID = '" +
|
tpclid
|
+ "' AND ROWNUM < 2)";
|
}
|
|
command.CommandText = sqlStmt;
|
reader = command.ExecuteReader();
|
|
if (reader.Read())
|
fdrid = Convert.ToInt32(reader["FDR1"].ToString());
|
else
|
{
|
sqlStmt =
|
"SELECT FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = 107 AND UFID = (SELECT UFID FROM BASEDB.HICUSTOMER WHERE OWNERTPCLID = '" +
|
tpclid + "' AND ROWNUM < 2)";
|
|
command.CommandText = sqlStmt;
|
reader = command.ExecuteReader();
|
|
if (reader.Read())
|
fdrid = Convert.ToInt32(reader["FDR1"].ToString());
|
}
|
}
|
catch (Exception e)
|
{
|
Console.WriteLine(e.Message);
|
Console.WriteLine(e.StackTrace);
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
|
return fdrid;
|
}
|
|
private int GetTraceCount(string ccsid)
|
{
|
var sqlStmt = "SELECT TRACECOUNT FROM CCS.TRACECOUNTS WHERE CCSID = '" + ccsid + "'";
|
var command = new OracleCommand(sqlStmt, _connectionTpc, _trx);
|
var reader = command.ExecuteReader();
|
var count = 0;
|
|
try
|
{
|
if (reader.Read())
|
count = Convert.ToInt32(reader["TRACECOUNT"].ToString());
|
}
|
catch (Exception e)
|
{
|
Console.WriteLine(e.Message);
|
Console.WriteLine(e.StackTrace);
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
|
return count;
|
}
|
|
private bool UpdateTraceCount(string ccsid, bool isNew)
|
{
|
string sqlStmt;
|
|
if (isNew)
|
sqlStmt = "INSERT INTO CCS.TRACECOUNTS (CCSID,TRACECOUNT) VALUES ('" + ccsid + "',1)";
|
else
|
sqlStmt = "UPDATE CCS.TRACECOUNTS SET TRACECOUNT = TRACECOUNT + 1 WHERE CCSID = '" + ccsid + "'";
|
var command = new OracleCommand(sqlStmt, _connectionTpc, _trx);
|
|
try
|
{
|
var result = command.ExecuteNonQuery();
|
|
if (result == 1)
|
return true;
|
}
|
catch (Exception e)
|
{
|
Console.WriteLine(e.Message);
|
Console.WriteLine(e.StackTrace);
|
}
|
finally
|
{
|
command.Dispose();
|
}
|
return false;
|
}
|
}
|
}
|
