using System;
|
|
using System.Data.OracleClient;
|
using CCSTrace.CCS.Domain;
|
using NLog;
|
|
namespace CCSTrace.CCS.Function
|
{
|
public class Leach
|
{
|
private static readonly Logger Logger = LogManager.GetCurrentClassLogger();
|
|
private readonly MergeCase _mergeCase = new MergeCase();
|
private readonly OracleConnection _connectionTpc;
|
private readonly OracleTransaction _transaction;
|
private readonly string _traceConnectionString;
|
|
public Leach(OracleConnection conn, OracleTransaction trx, string traceConnection)
|
{
|
_connectionTpc = conn;
|
_transaction = trx;
|
_traceConnectionString = traceConnection;
|
}
|
|
public int LeachCase(Object.EOSEventRecord mNewRec)
|
{
|
switch (SameCase(mNewRec))
|
{
|
case GlobalVariable.Success:
|
return Judge(mNewRec, false);
|
|
case GlobalVariable.CaseTypeChanged:
|
return Judge(mNewRec, true);
|
|
case GlobalVariable.SameCustomer:
|
return GlobalVariable.SameCustomer;
|
|
case GlobalVariable.FailureByDb:
|
return GlobalVariable.FailureByDb;
|
|
case GlobalVariable.CaseTransfer:
|
return GlobalVariable.CaseTransfer;
|
|
default:
|
return GlobalVariable.FailureByDb;
|
}
|
}
|
|
public EventAI.Equipment GetOldEquipment()
|
{
|
return _mergeCase.GetOldEquipment();
|
}
|
|
private int SameCase(Object.EOSEventRecord mNewRec)
|
{
|
string sqlStmt = "SELECT R.ACCEPTNUM,R.CASEID as CASEID,R.IMPORTCASE as IMPORTCASE,E.CASETYPE as CASETYPE FROM EOS.EVENTRECORD R,"
|
+ "EOS.EVENTS E WHERE ";
|
int mResult = -1;
|
int importCase = -1;
|
int tmpCaseId = 0;
|
int caseType = -1;
|
Logger.Info("利用電號或地址檢查是否有已成立之案件.");
|
// 檢查資料庫中此電號或地址是否已成立案件
|
|
if (mNewRec.Meter.Trim().Length != 0)
|
{
|
sqlStmt = sqlStmt + "R.CUSTOMERMETER = '" + mNewRec.Meter+ "' AND E.CASEID = R.CASEID AND E.CASESTATUS <= "
|
+ CCSCaseState.WaitForSponsor;
|
}
|
else if (mNewRec.Addr.Trim().Length != 0)
|
{
|
sqlStmt = sqlStmt + "R.CUSTOMERADDR = '" + mNewRec.Addr+ "' AND E.CASEID = R.CASEID AND E.CASESTATUS <= "
|
+ CCSCaseState.WaitForSponsor;
|
}
|
|
OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
|
OracleDataReader reader = command.ExecuteReader();
|
|
try
|
{
|
if (reader.Read())
|
{
|
importCase = Convert.ToInt32(reader["IMPORTCASE"].ToString());
|
tmpCaseId = Convert.ToInt32(reader["CASEID"].ToString());
|
caseType = Convert.ToInt32(reader["CASETYPE"].ToString());
|
mNewRec.TmpCaseId = tmpCaseId;
|
Logger.Info("案件已成立:" + reader["ACCEPTNUM"].ToString() + " " + tmpCaseId + " " + caseType);
|
}
|
}
|
catch (Exception e)
|
{
|
Logger.Error(e, e.Message);
|
return GlobalVariable.FailureByDb;
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
|
// 非同一用戶重複來電
|
if (tmpCaseId == 0)
|
{
|
return GlobalVariable.Success;
|
}
|
switch (caseType)
|
{
|
case GlobalVariable.OriginalCase:
|
case GlobalVariable.ParentCase:
|
sqlStmt = "SELECT TO_CHAR(CLOSETIME,'YYYY/MM/DD HH24:MI:SS') as CLOSETIME,COUNT(*) as COUNT FROM EOS.EVENTFACILITY " + "WHERE CASEID = "
|
+ tmpCaseId + " GROUP BY CLOSETIME ORDER BY CLOSETIME DESC";
|
break;
|
|
case GlobalVariable.ChildCase:
|
sqlStmt = "SELECT TO_CHAR(CLOSETIME,'YYYY/MM/DD HH24:MI:SS') as CLOSETIME,COUNT(*) as COUNT FROM EOS.EVENTFACILITY "
|
+ "WHERE CASEID = (SELECT PARENTID FROM EOS.MERGECASE WHERE CASEID = " + tmpCaseId + ") GROUP BY CLOSETIME "
|
+ "ORDER BY CLOSETIME DESC";
|
break;
|
|
default:
|
Logger.Error("無法辨別案件種類.");
|
return GlobalVariable.FailureByDb;
|
}
|
Logger.Info("檢查該案件有無切開關紀錄.");
|
|
try
|
{
|
command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
|
reader = command.ExecuteReader();
|
|
if (reader.Read())
|
{
|
// 有開關尚未恢復
|
if (reader["CLOSETIME"] == null)
|
{
|
Logger.Info("有切開關且尚未恢復.");
|
if ((mNewRec.ImportCase== importCase) || (importCase == GlobalVariable.IsImportCase))
|
{
|
mResult = AddCount(tmpCaseId);
|
}
|
else
|
{
|
mResult = GlobalVariable.CaseTypeChanged;
|
}
|
}
|
else
|
{
|
// 開關都已恢復(同用戶須重新受理)
|
Logger.Info("有切開關且已經恢復.");
|
int caseId;
|
|
// 找出案件編號需以母案件編號或該案件編號去找casestatus
|
if (tmpCaseId == 0)
|
{
|
caseId = mNewRec.CaseId;
|
}
|
else
|
{
|
caseId = tmpCaseId;
|
}
|
// 判斷該案件或母案件是否移轉作後續處理
|
if (GetCastStatus(caseId) == (int) CCSCaseState.EventTrasnfer)
|
{
|
return GlobalVariable.CaseTransfer;
|
}
|
else
|
{
|
mResult = GlobalVariable.Success;
|
}
|
}
|
}
|
else
|
{
|
// 未切過開關
|
Logger.Info("無切開關紀錄.");
|
if ((mNewRec.ImportCase== importCase) || (importCase == GlobalVariable.IsImportCase))
|
{
|
mResult = AddCount(tmpCaseId);
|
}
|
else
|
{
|
int caseId;
|
// 找出案件編號需以母案件編號或該案件編號去找casestatus
|
if (tmpCaseId == 0)
|
{
|
caseId = mNewRec.CaseId;
|
}
|
else
|
{
|
caseId = tmpCaseId;
|
|
// 判斷該案件或母案件是否移轉作後續處理
|
}
|
if (GetCastStatus(caseId) == (int) CCSCaseState.EventTrasnfer)
|
{
|
return GlobalVariable.CaseTransfer;
|
}
|
else
|
{
|
mResult = GlobalVariable.CaseTypeChanged;
|
}
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
Logger.Error(e, e.Message);
|
return GlobalVariable.FailureByDb;
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
return mResult;
|
}
|
|
private int AddCount(int mCaseId)
|
{
|
var sqlStmt = "UPDATE EOS.EVENTS SET COUNT = COUNT + 1 WHERE CASEID = " + mCaseId;
|
OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
|
|
try
|
{
|
return command.ExecuteNonQuery() > 0 ? GlobalVariable.SameCustomer : GlobalVariable.FailureByDb;
|
}
|
catch (Exception e)
|
{
|
Logger.Warn(e, e.Message);
|
return GlobalVariable.FailureByDb;
|
}
|
finally
|
{
|
command.Dispose();
|
}
|
}
|
|
private int GetCastStatus(int caseId)
|
{
|
int status = 0;
|
|
var sqlStmt = "SELECT CASESTATUS FROM CCS.EVENTQUERY WHERE CCSID IN (SELECT CCSID FROM CCS.NUM_CONTRAST " + "WHERE CASEID = " + caseId + ")";
|
OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
|
OracleDataReader reader = null;
|
|
try
|
{
|
reader = command.ExecuteReader();
|
|
if (reader.Read())
|
status = Convert.ToInt32(reader["CASESTATUS"].ToString());
|
}
|
catch (Exception e)
|
{
|
Logger.Error(e, e.Message);
|
}
|
finally
|
{
|
if (reader != null)
|
reader.Close();
|
|
command.Dispose();
|
}
|
return status;
|
}
|
|
private int Judge(Object.EOSEventRecord mNewRec, bool changeCaseType)
|
{
|
int mResult = GlobalVariable.FailureByDb;
|
|
if ((mNewRec.Meter.Trim().Length == 0))
|
{
|
return GlobalVariable.NoMeter;
|
}
|
|
/* 已知損壞設備(ONLY FOR 饋線全停才知道損壞設備的FSC) */
|
try
|
{
|
if (mNewRec.Fsc!= 0)
|
{
|
mNewRec.LocateEquipment.Fsc = (GlobalVariable.Breaker);
|
mNewRec.LocateEquipment.Ufid = (mNewRec.Ufid);
|
Logger.Info("饋線全停:" + mNewRec.Fsc+ ", " + mNewRec.Ufid);
|
_mergeCase.Merge(mNewRec, _connectionTpc, _transaction, _traceConnectionString);
|
return GlobalVariable.Success;
|
}
|
}
|
catch (EventAI.TraceLoopException e)
|
{
|
Logger.Warn(e, e.Message);
|
return GlobalVariable.TraceCounts3;
|
}
|
catch (TraceException e)
|
{
|
Logger.Warn(e, e.Message);
|
return GlobalVariable.TraceFailure;
|
}
|
catch (Exception ex)
|
{
|
Logger.Error(ex, ex.Message);
|
return GlobalVariable.FailureByDb;
|
}
|
|
try
|
{
|
Logger.Info("定位損壞設備!");
|
int result = GetEquipment(mNewRec);
|
Logger.Info("FSC:" + mNewRec.Fsc+ ", Ufid:" + mNewRec.Ufid+ ", Tpclid:" + mNewRec.Tpclid+ ", FeederID:" + mNewRec.FdrId);
|
|
switch (result)
|
{ // get fsc,ufid,fdrid of sxfmr by meter
|
case GlobalVariable.Success:
|
if (mNewRec.FdrId== 0)
|
{
|
mResult = GlobalVariable.NoSupplyElc;
|
}
|
else
|
{
|
// 需在未併案前作土木設備設定
|
GetLocateEquipment(mNewRec);
|
|
if (mNewRec.Brief.Length != 0 && mNewRec.Brief.Substring(0, 1).Equals("A"))
|
{ // A類事故原因,做追蹤合併
|
Logger.Info("<A類案件>");
|
_mergeCase.Merge(mNewRec, _connectionTpc, _transaction, _traceConnectionString);
|
}
|
else
|
{
|
Logger.Info("<非A類案件>");
|
}
|
|
if (changeCaseType)
|
{
|
mResult = GlobalVariable.CaseTypeChanged;
|
}
|
else
|
{
|
mResult = GlobalVariable.Success;
|
}
|
}
|
break;
|
|
case GlobalVariable.NoSxfmr:
|
mResult = GlobalVariable.NoSxfmr;
|
break;
|
|
case GlobalVariable.FailureByDb:
|
mResult = GlobalVariable.FailureByDb;
|
break;
|
}
|
}
|
catch (EventAI.TraceLoopException e)
|
{
|
Logger.Warn(e, e.Message);
|
return GlobalVariable.TraceCounts3;
|
}
|
catch (TraceException e)
|
{
|
Logger.Warn(e, e.Message);
|
mResult = GlobalVariable.TraceFailure;
|
}
|
catch (Exception ex1)
|
{
|
Logger.Error(ex1, ex1.Message);
|
mResult = GlobalVariable.FailureByDb;
|
}
|
return mResult;
|
}
|
|
private int GetEquipment(Object.EOSEventRecord mNewRec)
|
{
|
string temp;
|
string mOwnerTpclid;
|
string mGroup;
|
string sqlStmt = "select tran_cord from basedb.meter where metr_numb = '" + mNewRec.Meter+ "'";
|
OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
|
OracleDataReader reader = command.ExecuteReader();
|
|
try
|
{
|
if (reader.Read())
|
{
|
temp = reader["tran_cord"].ToString().Trim();
|
if ((temp.Length == 9) || (temp.Length == 11)) /* 架空變壓器 */
|
{
|
mOwnerTpclid = temp;
|
reader.Close();
|
command.Dispose();
|
mNewRec.Tpclid = mOwnerTpclid;
|
return GetSxfmr(mOwnerTpclid, null, mNewRec); /* Find the Sxfmr */
|
}
|
else
|
{
|
mOwnerTpclid = temp.Substring(0, 11).Trim(); /* 地下變壓器 */
|
mGroup = temp.Substring(14);
|
reader.Close();
|
command.Dispose();
|
mNewRec.Tpclid = mOwnerTpclid + " " + mGroup;
|
return GetSxfmr(mOwnerTpclid, mGroup, mNewRec);
|
/* Find the Sxfmr */
|
}
|
}
|
else
|
{
|
return GlobalVariable.NoSxfmr;
|
}
|
}
|
catch (Exception e)
|
{
|
Logger.Error(e, e.Message);
|
return GlobalVariable.FailureByDb;
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
}
|
|
private int GetSxfmr(string mTpclid, string mGroup, Object.EOSEventRecord mNewRec)
|
{
|
string sqlStmt;
|
int mStatus = GlobalVariable.FailureByDb;
|
|
if (mGroup == null)
|
{
|
sqlStmt = "SELECT UFID,FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = " + GlobalVariable.Sxfmr + " AND UFID IN "
|
+ "(SELECT UFID FROM BASEDB.SXFMR WHERE OWNERTPCLID = '" + mTpclid + "')";
|
}
|
else
|
{
|
sqlStmt = "SELECT UFID,FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = " + GlobalVariable.Sxfmr + " AND UFID IN "
|
+ "(SELECT UFID FROM BASEDB.SXFMR WHERE OWNERTPCLID = '" + mTpclid + "' AND GROUP1 = '" + mGroup + "')";
|
}
|
|
OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
|
OracleDataReader reader = command.ExecuteReader();
|
|
try
|
{
|
if (reader.Read())
|
{
|
mNewRec.Fsc = GlobalVariable.Sxfmr;
|
mNewRec.Ufid = Convert.ToInt32(reader["ufid"].ToString());
|
mNewRec.FdrId = Convert.ToInt32(reader["fdr1"].ToString());
|
mStatus = GlobalVariable.Success;
|
}
|
else
|
mStatus = GetHicustomer(mTpclid, mGroup, mNewRec);
|
}
|
catch (Exception ex)
|
{
|
Logger.Error(ex, ex.Message);
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
return mStatus;
|
}
|
|
private int GetHicustomer(string mTpclid, string mGroup, Object.EOSEventRecord mNewRec)
|
{
|
string sqlStmt;
|
|
if (mGroup == null)
|
{
|
sqlStmt = "SELECT UFID,FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = " + GlobalVariable.Hicustomer + " AND UFID IN "
|
+ "(SELECT UFID FROM BASEDB.HICUSTOMER WHERE OWNERTPCLID = '" + mTpclid + "')";
|
}
|
else
|
{
|
sqlStmt = "SELECT UFID,FDR1 FROM BASEDB.CONNECTIVITY WHERE FSC = " + GlobalVariable.Hicustomer + " AND UFID IN "
|
+ "(SELECT UFID FROM BASEDB.HICUSTOMER WHERE OWNERTPCLID = '" + mTpclid + "' AND GROUP1 = '" + mGroup + "')";
|
}
|
|
OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
|
OracleDataReader reader = command.ExecuteReader();
|
|
try
|
{
|
if (reader.Read())
|
{
|
mNewRec.Fsc = GlobalVariable.Hicustomer;
|
mNewRec.Ufid = Convert.ToInt32(reader["ufid"].ToString());
|
mNewRec.FdrId = Convert.ToInt32(reader["fdr1"].ToString());
|
return GlobalVariable.Success;
|
}
|
else
|
return GlobalVariable.NoSxfmr;
|
}
|
catch (Exception ex)
|
{
|
Logger.Error(ex, ex.Message);
|
return GlobalVariable.FailureByDb;
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
}
|
|
private void GetLocateEquipment(Object.EOSEventRecord mNewRec)
|
{
|
var sqlStmt = "SELECT OWNERFSC as FSC,OWNERUFID as UFID,OWNERTPCLID as TPCLID FROM ";
|
switch (mNewRec.Fsc)
|
{
|
case GlobalVariable.Sxfmr:
|
sqlStmt = sqlStmt + "BASEDB.SXFMR WHERE UFID = " + mNewRec.Ufid;
|
break;
|
|
case GlobalVariable.Hicustomer:
|
sqlStmt = sqlStmt + "BASEDB.HICUSTOMER WHERE UFID = " + mNewRec.Ufid;
|
break;
|
}
|
|
OracleCommand command = new OracleCommand(sqlStmt, _connectionTpc, _transaction);
|
OracleDataReader reader = command.ExecuteReader();
|
|
try
|
{
|
if (reader.Read())
|
{
|
mNewRec.LocateEquipment.Fsc = (Convert.ToInt32(reader["FSC"].ToString()));
|
mNewRec.LocateEquipment.Ufid = (Convert.ToInt32(reader["UFID"].ToString()));
|
mNewRec.LocateEquipment.Tpclid = (reader["TPCLID"].ToString());
|
}
|
}
|
catch (Exception e)
|
{
|
Logger.Error(e, e.Message);
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
}
|
}
|
}
|
