using System;
|
using System.Data.OracleClient;
|
using CCSTrace.CCS.Domain;
|
using CCSTrace.CCS.Object;
|
using NLog;
|
|
namespace CCSTrace.CCS
|
{
|
public class FinishEvent
|
{
|
private static readonly Logger Logger = LogManager.GetCurrentClassLogger();
|
private int _originCaseStatus;
|
|
|
public bool Finish(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord, int mKind)
|
{
|
Logger.Info("進行OMS資料更新程序...");
|
if (eosEventRecord.ParentId == 0)
|
{
|
// Original Case
|
if (!ProcessSingalCase(conn, trx, eosEventRecord))
|
{
|
return false;
|
}
|
Logger.Info("更新eos.events成功!");
|
}
|
else
|
{
|
// Child Case
|
if (!ProcessMergeCase(conn, trx, eosEventRecord))
|
{
|
return false;
|
}
|
Logger.Info("更新eos.events成功!");
|
}
|
|
// inert into eos.eventrecord && eos.eventrecord_ex
|
var command = new OracleCommand(eosEventRecord.GetInsertSqlStmt(), conn, trx);
|
OracleCommand command1 = null;
|
|
try
|
{
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("儲存資料時發生錯誤: 無法更新eos.eventrecord");
|
command.Dispose();
|
return false;
|
}
|
|
command.CommandText = eosEventRecord.LocateEquipment.GetInsertSqlStmt();
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("儲存資料時發生錯誤: 無法更新eos.eventrecord_ex");
|
command.Dispose();
|
return false;
|
}
|
command.Dispose();
|
|
if ((mKind == GlobalVariable.Success) || (mKind == GlobalVariable.CaseTypeChanged))
|
{
|
/* 當饋線全停時不需注意影響虛擬用戶，影響用戶資料會由實際切開關產生 */
|
if (eosEventRecord.Fsc != GlobalVariable.Breaker)
|
{
|
if (!SetVisualCustomer(conn, trx, eosEventRecord))
|
{
|
Logger.Error("新增影響用戶資料錯誤.");
|
return false;
|
}
|
Logger.Info("新增影響用戶成功!");
|
}
|
|
var upLevel = false;
|
|
if (!UpdateImportCase(conn, trx, eosEventRecord, upLevel))
|
{
|
Logger.Error("處理案件等級變更時發生錯誤.");
|
return false;
|
}
|
Logger.Info("處理案件等級變更成功!");
|
|
if (!UpdateRecuseOrder(conn, trx, eosEventRecord, upLevel))
|
{
|
Logger.Error("新增搶修順序資料時發生錯誤.");
|
return false;
|
}
|
Logger.Info("新增搶修順序成功!");
|
|
if (mKind == GlobalVariable.CaseTypeChanged)
|
{
|
if (!DeleteOldCase(conn, trx, eosEventRecord))
|
{
|
Logger.Error("案件轉換發生錯誤");
|
return false;
|
}
|
Logger.Info("案件轉換成功!");
|
}
|
|
if (!SaveCustomerTel(conn, trx, eosEventRecord))
|
{
|
Logger.Error("無法將用戶電話回存回資料庫.");
|
return false;
|
}
|
Logger.Info("將用戶電話回存回資料庫成功!");
|
}
|
else if (mKind == GlobalVariable.NoMeter)
|
{
|
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, conn, trx);
|
if (command1.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("新增搶修順序資料時發生錯誤.");
|
command1.Dispose();
|
return false;
|
}
|
command1.Dispose();
|
Logger.Info("新增搶修順序成功!");
|
}
|
|
if (eosEventRecord.ParentId == 0)
|
{
|
if (!InsertIntoSri(conn, trx, eosEventRecord))
|
{
|
Logger.Error("新增資料庫(OCSDB.SRI)錯誤.");
|
return false;
|
}
|
Logger.Info("新增資料庫(OCSDB.SRI)成功!");
|
}
|
}
|
catch (Exception e)
|
{
|
Logger.Warn(e, e.Message);
|
command.Dispose();
|
|
command1?.Dispose();
|
|
throw;
|
}
|
|
/** COMMIT的動作須連外面CCS的資料都正確輸入完才作 */
|
return true;
|
}
|
|
private bool ProcessSingalCase(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)
|
{
|
var result = false;
|
var sqlStmt = "INSERT INTO EOS.EVENTS VALUES(" + eosEventRecord.CaseId + "," + CCSCaseState.WaitForDespatch +
|
",1,"
|
+ GlobalVariable.OriginalCase + ",0)";
|
|
var command = new OracleCommand(sqlStmt, conn, trx);
|
|
try
|
{
|
if (command.ExecuteNonQuery() > 0)
|
result = true;
|
else
|
Logger.Error("儲存資料時發生錯誤: 無法insert eos.events");
|
}
|
catch (Exception e)
|
{
|
Logger.Error(e, "儲存資料時發生錯誤: 無法insert eos.events. Error = " + e.Message);
|
}
|
finally
|
{
|
command.Dispose();
|
}
|
return result;
|
}
|
|
private bool ProcessMergeCase(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)
|
{
|
// insert eos.events
|
var sqlStmt = "INSERT INTO EOS.EVENTS VALUES(" + eosEventRecord.CaseId + "," + CCSCaseState.WaitForDespatch +
|
",1,"
|
+ GlobalVariable.ChildCase + ",0)";
|
|
var command = new OracleCommand(sqlStmt, conn, trx);
|
|
try
|
{
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("儲存資料時發生錯誤: 無法insert eos.events");
|
command.Dispose();
|
return false;
|
}
|
|
// insert into eos.mergecase
|
sqlStmt = "INSERT INTO EOS.MERGECASE VALUES(" + eosEventRecord.CaseId + ",'" + eosEventRecord.AcceptNum +
|
"'," +
|
eosEventRecord.ParentId + ")";
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("儲存資料時發生錯誤: 無法insert eos.mergecase");
|
command.Dispose();
|
return false;
|
}
|
|
// update parent case data(EOS.EVENTS)
|
sqlStmt = "UPDATE EOS.EVENTS SET CASETYPE = " + GlobalVariable.ParentCase +
|
",MERGECASECOUNT = MERGECASECOUNT + 1 " + "WHERE CASEID = "
|
+ eosEventRecord.ParentId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("儲存資料時發生錯誤: 無法更改母案件資料");
|
command.Dispose();
|
return false;
|
}
|
|
// 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 +
|
") OR CASEID = "
|
+ eosEventRecord.ParentId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("儲存資料時發生錯誤: 無法update eos.eventrecord(FSC,UFID)");
|
command.Dispose();
|
return false;
|
}
|
command.Dispose();
|
}
|
catch (Exception e)
|
{
|
Logger.Error(e, "儲存資料時發生錯誤: " + e.Message);
|
command.Dispose();
|
return false;
|
}
|
|
return true;
|
}
|
|
private bool SetVisualCustomer(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)
|
{
|
var customerKind = "C"; // 高壓用戶or一般用戶
|
|
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
|
{
|
if (reader.Read())
|
{
|
if (Convert.ToInt32(reader["COUNT"].ToString()) > 0)
|
{
|
customerKind = "H";
|
}
|
}
|
}
|
catch (Exception)
|
{
|
Logger.Error("無法判斷用戶種類.");
|
return false;
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
|
if (eosEventRecord.ParentId == 0)
|
{
|
sqlStmt = "INSERT INTO EOS.TMPAFFECTCUSTMS SELECT " + eosEventRecord.CaseId + ",A.M_NAME,"
|
+ "M.TELE_NUMB,M.METR_NUMB,A.M_ADDR,M.TRAN_CORD,M.CUST_TYPE,'" + customerKind + "' "
|
+
|
"FROM BASEDB.METER M,BASEDB.MSTADDR A WHERE M.METR_NUMB = A.M_CUST_NO(+) AND M.METR_NUMB = '" +
|
eosEventRecord.Meter + "'";
|
|
var command1 = new OracleCommand(sqlStmt, conn, trx);
|
if (command1.ExecuteNonQuery() >= 0)
|
{
|
command1.Dispose();
|
return true;
|
}
|
command1.Dispose();
|
return false;
|
}
|
return InsertMutilCustomer(conn, trx, customerKind, eosEventRecord);
|
}
|
|
private bool InsertMutilCustomer(OracleConnection conn, OracleTransaction trx, string customerKind,
|
EOSEventRecord eosEventRecord)
|
{
|
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 +
|
")";
|
|
var command = new OracleCommand(sqlStmt, conn, trx);
|
var reader = command.ExecuteReader();
|
|
try
|
{
|
if (reader.Read())
|
{
|
if (Convert.ToInt32(reader["COUNT"].ToString()) == 1)
|
{
|
updateCustomer = true; // 需重新修改影響用戶資料(原始母案件的影響用戶數)
|
}
|
}
|
}
|
catch (Exception)
|
{
|
Logger.Error("無法確認是否需重新修改原始母案件之影響用戶資料.");
|
return false;
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
|
OracleCommand command1 = null;
|
|
if (updateCustomer)
|
{
|
/* 找出原始案件的影響用戶資料 */
|
sqlStmt = "SELECT TPCLIDGROUPID,CUSTOMERKIND FROM EOS.TMPAFFECTCUSTMS WHERE CASEID = " +
|
eosEventRecord.ParentId;
|
|
try
|
{
|
command1 = new OracleCommand(sqlStmt, conn, trx);
|
reader = command1.ExecuteReader();
|
|
if (reader.Read())
|
{
|
mTpclidGroupId = reader["TPCLIDGROUPID"].ToString().Trim();
|
mCustomerKind = reader["CUSTOMERKIND"].ToString();
|
}
|
reader.Close();
|
|
/* 若為高壓用戶，用戶數本來就為1人..無須再作修正 */
|
if (mCustomerKind.Equals("C"))
|
{
|
/* 刪除原始案件的既有用戶資料 */
|
sqlStmt = "DELETE EOS.TMPAFFECTCUSTMS WHERE CASEID = " + eosEventRecord.ParentId;
|
|
command1.CommandText = sqlStmt;
|
if (command1.ExecuteNonQuery() < 0)
|
{
|
Logger.Error("無法刪除原始案件的既有用戶資料.");
|
command1.Dispose();
|
return false;
|
}
|
|
/* 將原始案件的影響用戶數改為變壓器的全部用戶 */
|
sqlStmt = "INSERT INTO EOS.TMPAFFECTCUSTMS SELECT " + eosEventRecord.ParentId + ",A.M_NAME,"
|
+ "M.TELE_NUMB,M.METR_NUMB,A.M_ADDR,M.TPCLIDGROUPID,M.CUST_TYPE,'" + mCustomerKind +
|
"' "
|
+
|
"FROM (SELECT TELE_NUMB,METR_NUMB,TRAN_CORD as TPCLIDGROUPID,CUST_TYPE FROM BASEDB.METER " +
|
"WHERE TRAN_CORD = '"
|
+ mTpclidGroupId + "') M,BASEDB.MSTADDR A WHERE M.METR_NUMB = A.M_CUST_NO(+) ";
|
|
command1.CommandText = sqlStmt;
|
if (command1.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("無法將原始案件的影響用戶數改為變壓器的全部用戶.");
|
command1.Dispose();
|
return false;
|
}
|
}
|
command1.Dispose();
|
}
|
catch (Exception e)
|
{
|
Logger.Error(e, "無法更新原始案件影響用戶資料: " + e.Message);
|
command1?.Dispose();
|
return false;
|
}
|
}
|
|
/* 新受理案件的影響用戶資料 */
|
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 +
|
"')) M,BASEDB.MSTADDR A WHERE M.METR_NUMB = A.M_CUST_NO(+) ";
|
|
var command2 = new OracleCommand(sqlStmt, conn, trx);
|
if (command2.ExecuteNonQuery() < 0)
|
{
|
Logger.Error("無法新增新受理案件的影響用戶資料.");
|
command2.Dispose();
|
return false;
|
}
|
command2.Dispose();
|
return true;
|
}
|
|
private bool UpdateImportCase(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord, bool upLevel)
|
{
|
/* 該案件不為交辦案件 */
|
if (eosEventRecord.ImportCase != GlobalVariable.IsImportCase)
|
{
|
return true;
|
}
|
|
/* 該交辦案件為原始案件 */
|
if (eosEventRecord.ParentId == 0)
|
{
|
return true;
|
}
|
|
/* 若母案件已為交辦案件，則不需再作任何更動 */
|
var sqlStmt = "SELECT IMPORTCASE FROM EOS.EVENTRECORD WHERE CASEID = " + eosEventRecord.ParentId;
|
var command = new OracleCommand(sqlStmt, conn, trx);
|
var reader = command.ExecuteReader();
|
|
try
|
{
|
if (reader.Read())
|
{
|
if (Convert.ToInt32(reader["IMPORTCASE"].ToString()) == GlobalVariable.IsImportCase)
|
return true;
|
}
|
}
|
catch (Exception e)
|
{
|
Logger.Error(e, "無法取得母案件之等級." + e.Message);
|
return false;
|
}
|
finally
|
{
|
reader.Close();
|
command.Dispose();
|
}
|
|
/* 開始進行交辦案件升級動作 */
|
upLevel = true;
|
|
/* 將交辦案件改為母案件 */
|
sqlStmt = "UPDATE EOS.EVENTS SET CASETYPE = " + GlobalVariable.ParentCase + ",MERGECASECOUNT = "
|
+ "(SELECT MERGECASECOUNT FROM EOS.EVENTS WHERE CASEID = " + eosEventRecord.ParentId +
|
") WHERE CASEID = " + eosEventRecord.CaseId;
|
|
var command1 = new OracleCommand(sqlStmt, conn, trx);
|
if (command1.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("無法將交辦案件改為母案件.");
|
command1.Dispose();
|
return false;
|
}
|
|
/* 將母案件改為子案件 */
|
sqlStmt = "UPDATE EOS.EVENTS SET CASETYPE = " + GlobalVariable.ChildCase +
|
",MERGECASECOUNT = 0 WHERE CASEID = " + eosEventRecord.ParentId;
|
|
command1.CommandText = sqlStmt;
|
if (command1.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("無法將母案件改為子案件.");
|
command1.Dispose();
|
return false;
|
}
|
|
// 將EOS.MERGECASE內的資料修正 1.將原先該母案件下的子案件都改掛在交辦案件下 2.將已INSERT入EOS.MERGECASE內的交辦案件資料改為原先母案件的資料(CaseID,AcceptNum)
|
sqlStmt = "UPDATE EOS.MERGECASE SET PARENTID = " + eosEventRecord.CaseId + " WHERE PARENTID = " +
|
eosEventRecord.ParentId;
|
|
command1.CommandText = sqlStmt;
|
if (command1.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("無法將原先該母案件下的子案件都改掛在交辦案件下.");
|
command1.Dispose();
|
return false;
|
}
|
|
sqlStmt = "UPDATE EOS.MERGECASE SET (CASEID,ACCEPTNUM) = (SELECT CASEID,ACCEPTNUM FROM EOS.EVENTRECORD WHERE CASEID = "
|
+ eosEventRecord.ParentId + ") WHERE CASEID = " + eosEventRecord.CaseId;
|
|
command1.CommandText = sqlStmt;
|
if (command1.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("無法將已INSERT入EOS.MERGECASE內的交辦案件資料改為原先母案件的資料(CaseID,AcceptNum).");
|
command1.Dispose();
|
return false;
|
}
|
command1.Dispose();
|
|
return UpdateOtherTable(conn, trx, eosEventRecord);
|
}
|
|
private bool UpdateOtherTable(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)
|
{
|
var sqlStmt = "SELECT CASESTATUS FROM EOS.EVENTS WHERE CASEID = " + eosEventRecord.ParentId;
|
var command = new OracleCommand(sqlStmt, conn, trx);
|
var reader = command.ExecuteReader();
|
|
try
|
{
|
if (reader.Read())
|
{
|
_originCaseStatus = Convert.ToInt32(reader["CASESTATUS"].ToString());
|
}
|
reader.Close();
|
|
switch (_originCaseStatus)
|
{
|
case 0:
|
Logger.Error("找不到原始母案件之案件狀態.");
|
command.Dispose();
|
return false;
|
|
case (int) CCSCaseState.WaitForDespatch:
|
break;
|
|
case (int) CCSCaseState.WaitForSponsor:
|
sqlStmt = "UPDATE EOS.EVENTDESPATCH SET CASEID = " + eosEventRecord.CaseId + " WHERE CASEID = " +
|
eosEventRecord.ParentId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() < 0)
|
{
|
Logger.Error("無法更新EOS.EVENTDESPATCH.");
|
command.Dispose();
|
return false;
|
}
|
|
sqlStmt = "update eos.events set casestatus=" + CCSCaseState.WaitForSponsor + " where caseid=" +
|
eosEventRecord.CaseId;
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() < 0)
|
{
|
Logger.Error("無法更新EOS.EVENTS.");
|
command.Dispose();
|
return false;
|
}
|
|
eosEventRecord.IsDespatched = true;
|
break;
|
}
|
|
sqlStmt = "UPDATE EOS.EVENTS SET CASESTATUS = " + CCSCaseState.WaitForDespatch + " WHERE CASEID = " +
|
eosEventRecord.ParentId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("無法更新EOS.Events的CaseStatus.");
|
command.Dispose();
|
return false;
|
}
|
|
sqlStmt = "UPDATE OCSDB.SRI SET SCENENAME = '" + eosEventRecord.AcceptNum +
|
"' WHERE SCENENAME = (SELECT ACCEPTNUM FROM "
|
+ "EOS.EVENTRECORD WHERE CASEID = " + eosEventRecord.ParentId + ")";
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() < 0)
|
{
|
Logger.Error("無法更新OCSDB.SRI.");
|
command.Dispose();
|
return false;
|
}
|
|
sqlStmt = "SELECT COUNT(*) as COUNT FROM EOS.CRTAPOLOGY WHERE CASEID = " + eosEventRecord.ParentId;
|
|
command.CommandText = sqlStmt;
|
reader = command.ExecuteReader();
|
var count = 0;
|
|
if (reader.Read())
|
{
|
count = Convert.ToInt32(reader["COUNT"].ToString());
|
}
|
reader.Close();
|
|
// 已有切過開關
|
if (count > 0)
|
{
|
sqlStmt = "UPDATE EOS.EVENTFACILITY SET CASEID = " + eosEventRecord.CaseId + " WHERE CASEID = " +
|
eosEventRecord.ParentId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() < 0)
|
{
|
Logger.Error("無法更新EOS.EVENTFACILITY.");
|
command.Dispose();
|
return false;
|
}
|
|
sqlStmt = "UPDATE EOS.CRTAPOLOGY SET CASEID = " + eosEventRecord.CaseId + " WHERE CASEID = " +
|
eosEventRecord.ParentId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() < 0)
|
{
|
Logger.Error("無法更新EOS.CRTAPOLOGY.");
|
command.Dispose();
|
return false;
|
}
|
|
sqlStmt = "UPDATE EOS.EVENTAFFECT SET CASEID = " + eosEventRecord.CaseId + " WHERE CASEID = " +
|
eosEventRecord.ParentId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() < 0)
|
{
|
Logger.Error("無法更新EOS.EVENTAFFECT.");
|
command.Dispose();
|
return false;
|
}
|
|
// SqlStmt = "UPDATE EOS.VOICE SET CASEID = " + NewCaseID + " WHERE CASEID = " + OldCaseID;
|
|
// if (CCS.GlobalVariable.Conn.UpdateDB(SqlStmt) < 0) {
|
// Logger.error("無法更新EOS.VOICE.");
|
// return false;
|
// }
|
|
sqlStmt = "UPDATE OCSDB.LOCKFEATURE SET DESCRIPTION = '" + eosEventRecord.AcceptNum +
|
"' WHERE DESCRIPTION = (SELECT ACCEPTNUM FROM "
|
+ "EOS.EVENTRECORD WHERE CASEID = " + eosEventRecord.ParentId + ")";
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() < 0)
|
{
|
Logger.Error("無法更新OCSDB.LOCKFEATURE");
|
command.Dispose();
|
return false;
|
}
|
}
|
command.Dispose();
|
}
|
catch (Exception e)
|
{
|
Logger.Error(e, e.Message);
|
reader.Close();
|
command.Dispose();
|
return false;
|
}
|
return true;
|
}
|
|
private bool UpdateRecuseOrder(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord, bool upLevel)
|
{
|
var rank = 999;
|
var priority = 1;
|
OracleCommand command = null;
|
|
/* 單純的子案件或不需升級的交辦案件,不需要更新EOS.RESCUE_ORDER */
|
if ((eosEventRecord.ParentId != 0) && !upLevel)
|
{
|
return true;
|
}
|
|
/* 該母案件已派工，無搶修順序紀錄 */
|
if (_originCaseStatus > (int) CCSCaseState.WaitForDespatch)
|
{
|
return true;
|
}
|
|
try
|
{
|
string sqlStmt;
|
if (upLevel)
|
{
|
/* 將原先母案件的編號改為新的母案件編號 */
|
sqlStmt = "UPDATE EOS.RESCUE_ORDER SET CASEID = " + eosEventRecord.CaseId + " WHERE CASEID = " +
|
eosEventRecord.ParentId +
|
" AND DSUFID = " + eosEventRecord.LocateEquipment.DsUfid;
|
|
command = new OracleCommand(sqlStmt, conn, trx);
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("無法更新EOS.RESCUE_ORDER: 無法將原先母案件的編號改為新的母案件編號.");
|
command.Dispose();
|
return false;
|
}
|
}
|
else
|
{
|
/* 找出該饋線的等級 */
|
sqlStmt = "SELECT RANK FROM EOS.IMP_FEEDER WHERE FEEDERID = " + eosEventRecord.FdrId;
|
|
command = new OracleCommand(sqlStmt, conn, trx);
|
var reader = command.ExecuteReader();
|
|
if (reader.Read())
|
{
|
rank = Convert.ToInt32(reader["RANK"].ToString());
|
}
|
reader.Close();
|
|
/* 找出同等級饋線中的最大搶修順序號碼 */
|
sqlStmt = "SELECT MAX(PRIORITY) as PRIORITY FROM EOS.RESCUE_ORDER WHERE RANK = " + rank +
|
" AND DSUFID = "
|
+ eosEventRecord.LocateEquipment.DsUfid;
|
|
command.CommandText = sqlStmt;
|
reader = command.ExecuteReader();
|
if (reader.Read())
|
{
|
if (reader["PRIORITY"].ToString().Length != 0)
|
priority = Convert.ToInt32(reader["PRIORITY"].ToString());
|
}
|
reader.Close();
|
|
/* 如果沒找到，找前一RANK的最大的Priority */
|
if (priority == 0 && rank > 0)
|
{
|
sqlStmt = "SELECT MAX(PRIORITY) as PRIORITY FROM EOS.RESCUE_ORDER WHERE RANK = "
|
+ "(SELECT MAX(RANK) FROM EOS.RESCUE_ORDER WHERE RANK < " + rank + " AND DSUFID = "
|
+ eosEventRecord.LocateEquipment.DsUfid + ")";
|
|
command.CommandText = sqlStmt;
|
reader = command.ExecuteReader();
|
|
if (reader.Read())
|
{
|
if (reader["PRIORITY"].ToString().Length != 0)
|
priority = Convert.ToInt32(reader["PRIORITY"].ToString());
|
}
|
reader.Close();
|
}
|
|
/* 如果還是沒找到，就設Priority = 0 (只有當新增案件的Rank為最小時或第一筆案件才可能發生) */
|
if (priority == 0)
|
{
|
// 若不寫清楚會看不懂
|
priority = 0;
|
|
/* 將搶修順序號碼大於等於找出來號碼的都加1 */
|
}
|
sqlStmt = "UPDATE EOS.RESCUE_ORDER SET PRIORITY = PRIORITY + 1 WHERE PRIORITY > " + priority +
|
" AND DSUFID = "
|
+ eosEventRecord.LocateEquipment.DsUfid;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() < 0)
|
{
|
Logger.Error("無法更新EOS.RESCUE_ORDER舊的搶修號碼.");
|
command.Dispose();
|
return false;
|
}
|
|
/* 將本身資料存入資料庫內 */
|
sqlStmt = "INSERT INTO EOS.RESCUE_ORDER (CASEID,RANK,PRIORITY,DSUFID) VALUES (" +
|
eosEventRecord.CaseId +
|
"," + rank + ","
|
+ (priority + 1) + "," + eosEventRecord.LocateEquipment.DsUfid + ")";
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("無法新增EOS.RESCUE_ORDER的資料");
|
command.Dispose();
|
return false;
|
}
|
}
|
|
command.Dispose();
|
}
|
catch (Exception e)
|
{
|
Logger.Error(e, "Problems occue during updating eos.rescue_order: " + e.Message);
|
|
if (command != null)
|
command.Dispose();
|
|
return false;
|
}
|
return true;
|
}
|
|
// 當原先同一用戶在事故受理後又報案，不過從一般案件轉為交辦案件所需作的轉換工作
|
private bool DeleteOldCase(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)
|
{
|
OracleCommand command = null;
|
|
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, conn, trx);
|
var reader = command.ExecuteReader();
|
|
var caseId = 0; // 原先的事故號碼
|
var count = 0; // 原先的來電次數
|
if (reader.Read())
|
{
|
caseId = Convert.ToInt32(reader["CASEID"].ToString());
|
count = Convert.ToInt32(reader["COUNT"].ToString());
|
reader.Close();
|
}
|
else
|
{
|
reader.Close();
|
command.Dispose();
|
Logger.Error("無法找到原先舊的案件.");
|
return false;
|
}
|
|
sqlStmt = "DELETE EOS.EVENTS WHERE CASEID = " + caseId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("刪除原先舊的案件的EOS.EVENTS發生錯誤.");
|
command.Dispose();
|
return false;
|
}
|
|
sqlStmt = "DELETE EOS.EVENTRECORD WHERE CASEID = " + caseId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("刪除原先舊的案件的EOS.EVENTRECORD發生錯誤.");
|
command.Dispose();
|
return false;
|
}
|
|
sqlStmt = "DELETE EOS.EVENTRECORD_EX WHERE CASEID = " + caseId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("刪除原先舊的案件的EOS.EVENTRECORD_EX發生錯誤.");
|
command.Dispose();
|
return false;
|
}
|
|
sqlStmt = "DELETE EOS.TMPAFFECTCUSTMS WHERE CASEID = " + caseId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() < 0)
|
{
|
Logger.Error("刪除原先舊的案件的EOS.TMPAFFECTCUSTMS發生錯誤.");
|
command.Dispose();
|
return false;
|
}
|
|
if (eosEventRecord.ParentId == caseId)
|
{
|
// 原本舊案件為母案件
|
sqlStmt = "SELECT COUNT(*) as COUNT FROM EOS.MERGECASE WHERE PARENTID = " + eosEventRecord.CaseId;
|
|
command.CommandText = sqlStmt;
|
reader = command.ExecuteReader();
|
|
if (reader.Read())
|
{
|
switch (Convert.ToInt32(reader["COUNT"].ToString()))
|
{
|
case 0:
|
Logger.Error("無法找到原先母案件下掛的子案件.");
|
reader.Close();
|
command.Dispose();
|
return false;
|
|
case 1: // 子案件為之前受理的同一事故案件
|
sqlStmt = "UPDATE EOS.EVENTS SET CASETYPE = " + GlobalVariable.OriginalCase +
|
",COUNT = " + (count + 1)
|
+ ",MERGECASECOUNT = 0 WHERE CASEID = " + eosEventRecord.CaseId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("無法更新原先子案件(要提升為母案件)的資料.");
|
reader.Close();
|
command.Dispose();
|
return false;
|
}
|
|
sqlStmt = "DELETE EOS.TMPAFFECTCUSTMS WHERE CUSTOMERMETER <> '" + eosEventRecord.Meter +
|
"' AND CASEID = "
|
+ eosEventRecord.CaseId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() < 0)
|
{
|
Logger.Error("無法刪除原先子案件(要提升為母案件)EOS.TMPAFFECTYCUSTMS的資料.");
|
reader.Close();
|
command.Dispose();
|
return false;
|
}
|
break;
|
|
default:
|
sqlStmt = "UPDATE EOS.EVENTS SET MERGECASECOUNT = MERGECASECOUNT - 1,COUNT = " +
|
(count + 1) + " WHERE CASEID = "
|
+ eosEventRecord.CaseId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() < 0)
|
{
|
Logger.Error("無法更新EOS.EVENTS的資料.");
|
reader.Close();
|
command.Dispose();
|
return false;
|
}
|
break;
|
}
|
reader.Close();
|
}
|
else
|
{
|
reader.Close();
|
command.Dispose();
|
Logger.Error("無法找到原先母案件下掛的子案件.");
|
return false;
|
}
|
}
|
|
sqlStmt = "DELETE EOS.MERGECASE WHERE CASEID = " + caseId;
|
|
command.CommandText = sqlStmt;
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
Logger.Error("刪除EOS.MERGECASE發生錯誤");
|
command.Dispose();
|
return false;
|
}
|
|
command.Dispose();
|
}
|
catch (Exception e)
|
{
|
Logger.Error(e, e.Message);
|
command?.Dispose();
|
return false;
|
}
|
return true;
|
}
|
|
private bool InsertIntoSri(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)
|
{
|
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, conn, trx);
|
reader = command.ExecuteReader();
|
|
if (reader.Read())
|
count = Convert.ToInt32(reader["COUNT"].ToString());
|
}
|
catch (Exception e)
|
{
|
Logger.Error(e, "Problems occur when checking SCENE Data: " + e.Message);
|
return false;
|
}
|
finally
|
{
|
reader?.Close();
|
}
|
|
if (count > 0)
|
{
|
command.Dispose();
|
return true;
|
}
|
|
sqlStmt = "INSERT INTO OCSDB.SRI VALUES('" + eosEventRecord.AcceptNum + "',2,-1)";
|
|
try
|
{
|
command.CommandText = sqlStmt;
|
|
if (command.ExecuteNonQuery() <= 0)
|
return false;
|
}
|
catch (Exception e)
|
{
|
Logger.Warn(e, e.Message);
|
}
|
finally
|
{
|
command.Dispose();
|
}
|
|
return true;
|
}
|
|
private bool SaveCustomerTel(OracleConnection conn, OracleTransaction trx, EOSEventRecord eosEventRecord)
|
{
|
// 無電號資料
|
/*
|
* if (m_Record.getisReCall() == CCS.GlobalVariable.NotReCall) { return true; }
|
*/
|
|
// 沒有回覆電話可供回存
|
if (eosEventRecord.Tel == null || eosEventRecord.Tel.Trim().Length == 0)
|
{
|
return true;
|
}
|
|
// 電話資料過長
|
if (eosEventRecord.Tel.Trim().Length > 24)
|
{
|
Logger.Warn("電話過長號碼，無法回存資料庫.");
|
return true;
|
}
|
|
var sqlStmt = "UPDATE BASEDB.METER SET TELE_NUMB = '" + eosEventRecord.Tel.Trim() + "' WHERE METR_NUMB = '" +
|
eosEventRecord.Meter + "'";
|
|
var command = new OracleCommand(sqlStmt, conn, trx);
|
|
try
|
{
|
if (command.ExecuteNonQuery() <= 0)
|
Logger.Warn("無法將用戶電話回存回資料庫.");
|
}
|
catch (Exception e)
|
{
|
Logger.Warn(e, e.Message);
|
}
|
finally
|
{
|
command.Dispose();
|
}
|
|
return true;
|
}
|
}
|
}
|
