using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Data.OracleClient; using System.Text; namespace CCSTrace.CCS { public delegate void ThreadEndEventHandler(object sender, ThreadEndEvent e); public class ProcessEvent { public event ThreadEndEventHandler ThreadFinish; private CCS.Object.EventRecord m_EventRecord; private CCS.Object.CCSRecord m_CCSRecord; private RecordLog _PLog; private OracleConnection _ConnectionTPC; private OracleTransaction _Trx; private String _traceConnectionString = String.Empty; public ProcessEvent(Object.CCSRecord _Record, OracleConnection _Conn, String _traceConnection) { m_CCSRecord = _Record; _ConnectionTPC = _Conn; _traceConnectionString = _traceConnection; _PLog = new RecordLog(CCS.LocalVariable.CCS_ListPath + _Record.getCCSID() + ".txt"); } public void Run() { bool SendMail = false; try { int FDRID = getFDRID(m_CCSRecord.getMeter()); if (FDRID != 0) //有饋線代號資料的案件才需比對同饋線上有無案件正在處理 { if (CCSMain.ProcessFDR.ContainsKey(FDRID.ToString())) { CCSMain.WaitingCases.Add(new String[] { m_CCSRecord.getCCSID(), FDRID.ToString() }); return; } else CCSMain.ProcessFDR.Add(FDRID.ToString(), null); //將要處理的案件的饋線別加入處理中饋線集合 } /* int TraceCount = this.getTraceCount(m_CCSRecord.getCCSID()); bool TraceError = false; //追蹤次數超過3次，改為非A類不追蹤合併 switch (TraceCount) { case 0: UpdateTraceCount(m_CCSRecord.getCCSID(),true); TraceError = false; break; case 2: UpdateTraceCount(m_CCSRecord.getCCSID(),false); TraceError = true; break; default: UpdateTraceCount(m_CCSRecord.getCCSID(),false); TraceError = false; break; } */ if (this.initialEventRecord(m_CCSRecord.getCCSID())) { CCS.Function.Leach m_Leach = new CCS.Function.Leach(_ConnectionTPC, _Trx, _traceConnectionString, _PLog); // 案件過濾與合併 _PLog.Info("過濾案件..."); int Return_Status = m_Leach.LeachCase(m_EventRecord); switch ( Return_Status ) { case CCS.LocalVariable.Success: _PLog.Info("案件過濾完畢...."); Process(CCS.LocalVariable.Success); break; case CCS.LocalVariable.NoMeter: _PLog.Info("案件過濾完畢,用戶無電號...."); m_EventRecord.setTrace_Finish(CCS.LocalVariable.Trace_Finish); Process(CCS.LocalVariable.NoMeter); break; case CCS.LocalVariable.SameCustomer: _PLog.Info("相同用戶重複來電...."); processSameCustomer(this.m_CCSRecord, this.m_EventRecord); break; case CCS.LocalVariable.NoSupplyElc: _PLog.Warn("該饋線未供電,無法作事故案件追蹤合併...."); m_EventRecord.setTrace_Finish(CCS.LocalVariable.Trace_Finish); Process(CCS.LocalVariable.NoMeter); break; case CCS.LocalVariable.No_Sxfmr: _PLog.Warn("該案件無變壓器...."); m_EventRecord.setTrace_Finish(CCS.LocalVariable.Trace_Finish); Process(CCS.LocalVariable.NoMeter); break; case CCS.LocalVariable.CaseTypeChanged: _PLog.Info("變更案件狀態...."); Process(CCS.LocalVariable.CaseTypeChanged); break; case CCS.LocalVariable.Trace_Failure: _PLog.Error("故障追蹤錯誤，以一般無電號案件方式受理...."); m_EventRecord.setTrace_Finish(CCS.LocalVariable.Trace_Finish); Process(CCS.LocalVariable.NoMeter); break; default: int TraceCount = this.getTraceCount(m_CCSRecord.getCCSID()); if ( TraceCount < 2 ) { if (_Trx.Connection.State.ToString().Equals("Open")) _Trx.Rollback(); _Trx.Dispose(); if (Return_Status == CCS.LocalVariable.CaseTransfer) _PLog.Error("案件已移轉，此事故案件暫不被受理...."); else if (Return_Status == CCS.LocalVariable.Failure_By_DB) _PLog.Error("資料庫錯誤，此事故案件暫不被受理...."); else if (Return_Status == CCS.LocalVariable.FDRLocked) _PLog.Error("饋線被鎖定，此事故案件暫不被受理...."); else _PLog.Error("發生非預期錯誤，此事故案件暫不被受理...."); if (TraceCount == 0) UpdateTraceCount(m_CCSRecord.getCCSID(), true); else UpdateTraceCount(m_CCSRecord.getCCSID(), false); } else { UpdateTraceCount(m_CCSRecord.getCCSID(), false); if (Return_Status == CCS.LocalVariable.Trace_Counts_3) m_EventRecord.setNote(m_EventRecord.getNote() + "(本案用戶供電資料追蹤失敗)"); _PLog.Error("事故案件處理次數3次，仍無法正常處理，以一般無電號案件方式受理...."); m_EventRecord.setTrace_Finish(CCS.LocalVariable.Trace_Finish); Process(CCS.LocalVariable.NoMeter); SendMail = true; } break; /* switch (TraceCount) { case 0: if (_Trx.Connection.State.ToString().Equals("Open")) _Trx.Rollback(); _Trx.Dispose(); if ( Return_Status == CCS.LocalVariable.CaseTransfer ) _PLog.Error("案件已移轉，此事故案件不被受理...."); else if ( Return_Status == CCS.LocalVariable.Failure_By_DB ) _PLog.Error("資料庫錯誤，此事故案件不被受理...."); else if ( Return_Status == CCS.LocalVariable.FDRLocked ) _PLog.Error("饋線被鎖定，此事故案件不被受理...."); else _PLog.Error("此事故案件不被受理...."); UpdateTraceCount(m_CCSRecord.getCCSID(), true); break; case 1: if (_Trx.Connection.State.ToString().Equals("Open")) _Trx.Rollback(); _Trx.Dispose(); UpdateTraceCount(m_CCSRecord.getCCSID(), false); break; default://追蹤次數超過3次，改為非A類不追蹤合併 UpdateTraceCount(m_CCSRecord.getCCSID(), false); if (Return_Status == CCS.LocalVariable.Trace_Counts_3) m_EventRecord.setNote(m_EventRecord.getNote() + "(本案用戶供電資料追蹤失敗)"); _PLog.Error("追蹤次數3次，無法追蹤到故障設備，以一般無電號案件方式受理...."); m_EventRecord.setTrace_Finish(CCS.LocalVariable.Trace_Finish); Process(CCS.LocalVariable.NoMeter); SendMail = true; break; } break; case CCS.LocalVariable.CaseTransfer: if (_Trx.Connection.State.ToString().Equals("Open")) _Trx.Rollback(); _Trx.Dispose(); _PLog.Error("案件已移轉，此事故案件不被受理...."); break; case CCS.LocalVariable.FDRLocked: if (_Trx.Connection.State.ToString().Equals("Open")) _Trx.Rollback(); _Trx.Dispose(); _PLog.Error("饋線被鎖定，此事故案件不被受理...."); break; case CCS.LocalVariable.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 = " + m_CCSRecord.getCCSID()); _PLog.Error("Error = " + e.Message + ". CCSID = " + m_CCSRecord.getCCSID()); if (_Trx != null) { if (_Trx.Connection.State.ToString().Equals("Open")) _Trx.Rollback(); _Trx.Dispose(); } if ( m_EventRecord!= null && m_EventRecord.getFdrID() != 0) CCSMain.ProcessFDR.Remove(m_EventRecord.getFdrID().ToString());//將處理完的案件的饋線別從處理中饋線集合中移除 //throw e; } finally { _PLog.Close(); CCSMain.ProcessCases.Remove(m_CCSRecord.getCCSID()); this.OnThreadFinish(new ThreadEndEvent(_ConnectionTPC)); } if ( SendMail ) new CCS.Function.MailService(_ConnectionTPC).SendMail(m_CCSRecord.getCCSID()); } private void Process(int Kind) { CCS.FinishEvent FinishEvent = new CCS.FinishEvent(); try { if (FinishEvent.Finish(Kind, m_EventRecord, _ConnectionTPC, _Trx, _PLog)) { _PLog.Info("OMS資料更新成功!"); if (UpdateCCSData(m_EventRecord, m_CCSRecord, CCS.LocalVariable.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(); ; CCS.Object.AlarmData alarmClient = new CCS.Object.AlarmData(_ConnectionTPC, _Trx, _PLog); alarmClient.alarm(m_EventRecord.getAcceptNum(), m_EventRecord.getDept()); } 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(CCS.Object.EventRecord m_Record, CCS.Object.CCSRecord m_CCSRecord, int CaseStatus) { _PLog.Info("進行CCS資料更新程序..."); CCS.Object.EventQuery EventQuery = new CCS.Object.EventQuery(); int Status = CaseStatus; // 設定CCSID EventQuery.setCCSID(m_CCSRecord.getCCSID()); // 設定受理時間 EventQuery.setChangeTime(m_CCSRecord.getAcceptTime()); // 設定用戶電號 if (m_CCSRecord.getMeter() != null) { EventQuery.setMeter(m_CCSRecord.getMeter()); } // 如果該案件為子案件,需找出母案件的casestatus來insert eos.eventquery // 若母案件已派工,需一併紀錄預計復電時間等資訊 if (m_Record.getParentID() != 0) { Status = this.getRealCaseStatus(m_Record.getParentID()) == CCS.LocalVariable.WaitForSponsor ? CCS.LocalVariable.EventDespatched : CCS.LocalVariable.WaitForDespatch; if (Status == CCS.LocalVariable.EventDespatched) { setDespatchInfo(m_Record.getParentID(), EventQuery); } } // 若此案件為專案案件升級為母案件,則必須判斷原先案件是否已經派工 if ((m_CCSRecord.getImportCase() == CCS.LocalVariable.isImportCase) && (m_Record.isDespatched())) { Status = CCS.LocalVariable.EventDespatched; setDespatchInfo(m_Record.getCaseID(), EventQuery); } EventQuery.setCaseStatus(Status); // 更新CCS.EventQuery if (!EventQuery.Update(_PLog,_ConnectionTPC,_Trx)) { _PLog.Error("更新ccs.eventquery失敗!"); return false; } else { _PLog.Info("更新ccs.eventquery成功!"); CCS.Object.NumberContrast NumberContrast = new CCS.Object.NumberContrast(); NumberContrast.setAcceptNum(m_Record.getAcceptNum()); NumberContrast.setCaseID(m_Record.getCaseID()); NumberContrast.setCCSID(m_CCSRecord.getCCSID()); // 更新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) { CCS.Function.InitialEventData InitialData = new CCS.Function.InitialEventData(_ConnectionTPC,_PLog); CCS.Object.NumberContrast m_Contrast = new CCS.Object.NumberContrast(); CCS.Object.Dept_Contrast Dept_Contrast = null; // Being Transaction _Trx = _ConnectionTPC.BeginTransaction(); Dept_Contrast = new CCS.Object.Dept_Contrast(_ConnectionTPC, _Trx); m_EventRecord = new CCS.Object.EventRecord(InitialData.getNewCaseID(), _ConnectionTPC, _Trx); m_EventRecord.setAcceptNum(InitialData.getNewAcceptNum()); m_Contrast.setAcceptNum(m_EventRecord.getAcceptNum()); m_Contrast.setCaseID(m_EventRecord.getCaseID()); m_Contrast.setCCSID(m_CCSRecord.getCCSID()); if (!m_Contrast.Insert(_ConnectionTPC, _Trx)) { _PLog.Error("初始化寫入CCS.Num_Contrast時發生錯誤！"); if (_Trx.Connection.State.ToString().Equals("Open")) _Trx.Rollback(); return false; } // 設定用戶姓名 m_EventRecord.setName(m_CCSRecord.getCustomerName()); if (m_CCSRecord.getMeter() != null && m_CCSRecord.getMeter().Length != 0 ) { // 需先找出部門代號,再找到該部門屬於哪一個巡修單位 m_EventRecord.setMeter(m_CCSRecord.getMeter()); int Tmp = Dept_Contrast.getDept_Code(m_CCSRecord.getMeter()); if ( Tmp == -1 ) m_EventRecord.setDept(findoutDeptByAddr(m_CCSRecord, _Trx)); else m_EventRecord.setDept(Tmp); m_EventRecord.setFdrID(getFDRID(m_CCSRecord.getMeter())); } else { // 當無用戶電號時,所找出的部門別不需再去找出屬於哪一個巡修股(findoutDeptByAddr會直接找到相對應的巡修股) int Dept = this.findoutDeptByAddr(m_CCSRecord, _Trx); m_EventRecord.setDept(Dept); } // 設定用戶電話號碼 if (m_CCSRecord.getCustomerTel() != null) { m_EventRecord.setTel(m_CCSRecord.getCustomerTel()); } // 檢查並設定用戶地址 String m_Addr = m_CCSRecord.getADDR_City() + m_CCSRecord.getADDR_Town() + m_CCSRecord.getADDR_Road() + m_CCSRecord.getADDR_Other(); if (m_Addr.Trim().Length == 0) { _PLog.Error("CCS 案件受理程序初始化失敗...地址為空白"); if (_Trx.Connection.State.ToString().Equals("Open")) _Trx.Rollback() ; return false; } m_EventRecord.setAddr(m_Addr); // 設定事故原因 m_EventRecord.setBrief(TransferEventBrief(m_CCSRecord.getEventBrief())); try { System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding(); byte[] source = encoding.GetBytes(m_EventRecord.getBrief()); if (source.Length > 24) { byte[] dest = new byte[24]; for (int i = 0; i < dest.Length; i++) { dest[i] = source[i]; } System.Text.Encoding enc = System.Text.Encoding.ASCII; m_EventRecord.setBrief(enc.GetString(dest)); source = dest = null; } } catch (Exception) { m_EventRecord.setBrief("其他"); } if (m_CCSRecord.getCCSID().StartsWith("A")) m_EventRecord.setLog("APP"); else if (m_CCSRecord.getCCSID().StartsWith("W")) m_EventRecord.setLog("WEB"); else m_EventRecord.setLog("CCS"); m_EventRecord.setAcceptDate(m_CCSRecord.getAcceptTime()); // 設定用戶種類 if (m_CCSRecord.getImportCase() == CCS.LocalVariable.isImportCase) { m_EventRecord.setImportCase(CCS.LocalVariable.isImportCase); m_EventRecord.setLevel(CCS.LocalVariable.A_Level); // default set A level } else { m_EventRecord.setImportCase(CCS.LocalVariable.NotImportCase); m_EventRecord.setLevel(CCS.LocalVariable.No_Level); } // 檢查字串中有無特殊字元' ,若有則取代為" if (m_CCSRecord.getNote() != null) m_EventRecord.setNote(m_CCSRecord.getNote().Replace('\'', '\"')); // 設定回覆姓名及電話 if (m_CCSRecord.getRecallName() != null) { m_EventRecord.setisReCall(CCS.LocalVariable.isReCall); m_EventRecord.setReCallName(m_CCSRecord.getRecallName().Replace('\'', '\"')); m_EventRecord.setReCallTel(m_CCSRecord.getRecallTel()); } else { m_EventRecord.setisReCall(CCS.LocalVariable.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(CCS.Object.EventQuery m_EventQuery) { String SqlStmt = "select ccsid from ccs.eventquery where ccsid='" + m_EventQuery.getCCSID() + "'"; OracleCommand Command = new OracleCommand(SqlStmt, _ConnectionTPC,_Trx); OracleDataReader reader = Command.ExecuteReader(); try { if (reader.Read()) { Command.CommandText = "delete from ccs.eventquery where ccsid='" + m_EventQuery.getCCSID() + "'"; Command.ExecuteNonQuery(); Command.CommandText = "delete from ccs.num_contrast where ccsid='" + m_EventQuery.getCCSID() + "'"; Command.ExecuteNonQuery(); } } catch (Exception ex) { _PLog.Warn("Problems occur during checking process: " + ex.Message); } finally { reader.Close(); Command.Dispose(); } } private int findoutDeptByAddr(CCS.Object.CCSRecord CCSRecord, OracleTransaction _Trx) { CCS.Object.Addr_Contrast Addr_Contrast = new CCS.Object.Addr_Contrast(_ConnectionTPC, _Trx, _PLog); return Addr_Contrast.findDeptID(CCSRecord.getADDR_City(), CCSRecord.getADDR_Town(), CCSRecord.getADDR_Road()); } private String TransferEventBrief(int Code) { String Result = ""; Result = CCS.CCSMain.CCSCodelist.getContent(CCS.LocalVariable.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 CCS.CCSMain.CCSCodelist.getKeyID(CCS.LocalVariable.CCSDept, DeptCode); } private int getRealCaseStatus(int ParentID) { String SqlStmt; int Status = CCS.LocalVariable.WaitForDespatch; SqlStmt = "SELECT CASESTATUS FROM EOS.EVENTS WHERE CASEID = " + ParentID; OracleCommand Command = new OracleCommand(SqlStmt, _ConnectionTPC, _Trx); OracleDataReader 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 m_CaseID, CCS.Object.EventQuery m_EventQry) { String SqlStmt = "select despatchtime,assumefixtime,assumedtime_nth,delaytime,reason,eventlocation from eos.eventdespatch where caseid=" + m_CaseID; OracleCommand Command = null; OracleDataReader reader = null; try { Command = new OracleCommand(SqlStmt, _ConnectionTPC,_Trx); reader = Command.ExecuteReader(); if (reader.Read()) { m_EventQry.setChangeTime(Convert.ToDateTime(reader["despatchtime"]).ToString("yyy/MM/dd HH:mm:ss")); m_EventQry.setAssumedTime(Convert.ToDateTime(reader["assumefixtime"]).ToString("yyy/MM/dd HH:mm:ss")); m_EventQry.setAssumedTime_Nth(Convert.ToDateTime(reader["assumedtime_nth"]).ToString("yyy/MM/dd HH:mm:ss")); m_EventQry.setDelayTimes(Convert.ToInt32(reader["delaytime"].ToString())); m_EventQry.setReason(reader["reason"].ToString()); m_EventQry.setEventLocation(reader["eventlocation"].ToString()); } } catch (Exception) { _PLog.Warn("無法取得母案件派工資訊."); } finally { if (reader != null ) reader.Close(); if (Command != null) Command.Dispose(); } } private void processSameCustomer(CCS.Object.CCSRecord m_CCSRecord, CCS.Object.EventRecord m_EventRec) { _PLog.Info("處理相同用戶重複來電..."); StringBuilder note = null; String SqlStmt = "select note from eos.eventrecord where caseid = " + m_EventRec.getTmpCaseID(); OracleCommand Command = new OracleCommand(SqlStmt, _ConnectionTPC, _Trx); OracleDataReader 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 ((m_EventRec.getNote() != null) && (m_EventRec.getNote().Length != 0)) note.Append(m_EventRec.getAcceptDate() + " " + m_EventRec.getNote()); else note.Append(m_EventRec.getAcceptDate() + " 用戶再次報案"); // 判斷重複來電的用戶最初成立的案件是否是由CCS受理 bool acceptByCCS = false; String SqlStmt1 = "select count(*) as count from ccs.num_contrast c,ccs.eventquery q where c.ccsid=q.ccsid and caseid=" + m_EventRec.getTmpCaseID(); OracleCommand Command1 = new OracleCommand(SqlStmt1, _ConnectionTPC,_Trx); OracleDataReader 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=" + m_EventRec.getTmpCaseID() + " and rownum<2)) where CCSID='" + m_CCSRecord.getCCSID() + "'"; // 若非由CCS受理(OMS,Web)則從EOS的table中尋找先前案件的狀態資料 } else { String accepttime = null, despatchtime = null, sponsortime = null, assumefixtime = null, assumedtime_nth = null, changetime = null, handling = null, reason = null, eventlocation = null; int casestatus = 0, delaytime = 0; OracleCommand Command2 = null; OracleDataReader reader2 = null; try { String 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=" + m_EventRec.getTmpCaseID() + " 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=" + m_EventRec.getTmpCaseID() + ")"; 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(); assumedtime_nth = 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 (despatchtime != null && despatchtime.Length != 0) { changetime = despatchtime; } if (sponsortime != null && sponsortime.Length != 0) { 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('" + assumedtime_nth + "','YYYY/MM/DD HH24:MI:SS'),delaytimes=" + delaytime + ",reason='" + reason + "',eventlocation='" + eventlocation + "' where ccsid='" + m_CCSRecord.getCCSID() + "'"; } catch (Exception e) { Console.WriteLine(e.Message); } finally { if (reader2 != null) reader2.Close(); if (Command2 != null) Command2.Dispose(); } } String updEOSEventRecord = "update eos.eventrecord set note = '" + TrimToLimit(note.ToString(), 127) + "' where caseid = " + m_EventRecord.getTmpCaseID(); String updCCSNum_Contrast = "update ccs.num_contrast set(acceptnum,caseid) = (select acceptnum," + m_EventRec.getTmpCaseID() + " from eos.eventrecord where caseid = " + m_EventRec.getTmpCaseID() + ") where ccsid='" + m_CCSRecord.getCCSID() + "'"; OracleCommand 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 = updCCSNum_Contrast; // 寫入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) { String SqlStmt = "SELECT DATA_LENGTH FROM ALL_TAB_COLUMNS WHERE OWNER = '" + Owner + "' AND TABLE_NAME = '" + TableName + "' AND COLUMN_NAME = '" + FieldName + "'"; OracleCommand Command = new OracleCommand(SqlStmt, _ConnectionTPC, _Trx); OracleDataReader reader = Command.ExecuteReader(); int 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) { string SqlStmt = "SELECT TRAN_CORD FROM BASEDB.METER WHERE METR_NUMB = '" + Meter + "'"; OracleCommand Command = new OracleCommand(SqlStmt, _ConnectionTPC, _Trx); OracleDataReader reader = Command.ExecuteReader(); string TPCLID = ""; int FDRID = 0; String 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) { String SqlStmt = "SELECT TRACECOUNT FROM CCS.TRACECOUNTS WHERE CCSID = '" + CCSID + "'"; OracleCommand Command = new OracleCommand(SqlStmt, _ConnectionTPC, _Trx); OracleDataReader reader = Command.ExecuteReader(); int 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 + "'"; OracleCommand Command = new OracleCommand(SqlStmt, _ConnectionTPC, _Trx); try { int Result = Command.ExecuteNonQuery(); if (Result == 1) return true; } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine(e.StackTrace); } finally { Command.Dispose(); } return false; } } }