using System;
|
using System.Data.OracleClient;
|
|
namespace CCSTrace.CCS.Object
|
{
|
public class EventQuery
|
{
|
//private CCS.Function.TransferDate convert = new CCS.Function.TransferDate();
|
|
public string CcsId { get; set; }
|
|
public string Meter { get; set; }
|
|
public int CaseStatus { get; set; }
|
|
public string ChangeTime { get; set; }
|
|
public string HandlingSummary { get; set; }
|
|
public string AssumedTime { get; set; }
|
|
public string AssumedTimeNth { get; set; }
|
|
public int DelayTimes { get; set; } = 0;
|
|
public string Reason { get; set; }
|
|
public string EventLocation { get; set; } = "";
|
|
public bool Insert(RecordLog pLog, OracleConnection conn, OracleTransaction transaction)
|
{
|
string sqlStmt;
|
|
if (!Check())
|
{
|
return false;
|
}
|
|
if (Meter == null)
|
{
|
sqlStmt =
|
$"INSERT INTO CCS.EVENTQUERY (CCSID,CASESTATUS,CHANGETIME,ASSUMEDTIME,ASSUMEDTIME_NTH,DELAYTIMES,REASON,INPUTTIME) values(" +
|
"'" + CcsId
|
+ "'," + CaseStatus + ",to_date('" + ChangeTime + "','yyyy/mm/dd hh24:mi:ss'),to_date('"
|
+ AssumedTime + "','yyyy/mm/dd hh24:mi:ss'),to_date('" + AssumedTimeNth +
|
"','yyyy/mm/dd hh24:mi:ss')," + DelayTimes + ",'" + Reason + "',SYSDATE)";
|
}
|
else
|
{
|
sqlStmt =
|
$"INSERT INTO CCS.EVENTQUERY (CCSID,METER,CASESTATUS,CHANGETIME,ASSUMEDTIME,ASSUMEDTIME_NTH,DELAYTIMES,REASON,INPUTTIME) values(" +
|
"'"
|
+ CcsId + "','" + Meter + "'," + CaseStatus + ",to_date('" + ChangeTime +
|
"','yyyy/mm/dd hh24:mi:ss'),to_date('" + AssumedTime + "','yyyy/mm/dd hh24:mi:ss'),to_date('"
|
+ AssumedTimeNth + "','yyyy/mm/dd hh24:mi:ss')," + DelayTimes + ",'" + Reason + "',SYSDATE)";
|
}
|
|
var command = new OracleCommand(sqlStmt, conn, transaction);
|
|
try
|
{
|
if (command.ExecuteNonQuery() > 0)
|
return true;
|
else
|
return false;
|
}
|
catch (Exception e)
|
{
|
pLog.Error(e.Message);
|
Console.WriteLine(e.StackTrace);
|
return false;
|
}
|
finally
|
{
|
command.Dispose();
|
}
|
}
|
|
public bool Insert(SEventLog pLog, OracleConnection conn, OracleTransaction transaction)
|
{
|
string sqlStmt;
|
|
if (!Check())
|
{
|
return false;
|
}
|
|
if (Meter == null)
|
{
|
sqlStmt =
|
"INSERT INTO CCS.EVENTQUERY (CCSID,CASESTATUS,CHANGETIME,ASSUMEDTIME,ASSUMEDTIME_NTH,DELAYTIMES,REASON,INPUTTIME) values(" +
|
"'" + CcsId
|
+ "'," + CaseStatus + ",to_date('" + ChangeTime + "','yyyy/mm/dd hh24:mi:ss'),to_date('"
|
+ AssumedTime + "','yyyy/mm/dd hh24:mi:ss'),to_date('" + AssumedTimeNth +
|
"','yyyy/mm/dd hh24:mi:ss')," + DelayTimes + ",'" + Reason + "',SYSDATE)";
|
}
|
else
|
{
|
sqlStmt =
|
"INSERT INTO CCS.EVENTQUERY (CCSID,METER,CASESTATUS,CHANGETIME,ASSUMEDTIME,ASSUMEDTIME_NTH,DELAYTIMES,REASON,INPUTTIME) values(" +
|
"'"
|
+ CcsId + "','" + Meter + "'," + CaseStatus + ",to_date('" + ChangeTime +
|
"','yyyy/mm/dd hh24:mi:ss'),to_date('" + AssumedTime + "','yyyy/mm/dd hh24:mi:ss'),to_date('"
|
+ AssumedTimeNth + "','yyyy/mm/dd hh24:mi:ss')," + DelayTimes + ",'" + Reason + "',SYSDATE)";
|
}
|
|
var command = new OracleCommand(sqlStmt, conn, transaction);
|
|
try
|
{
|
if (command.ExecuteNonQuery() > 0)
|
return true;
|
else
|
return false;
|
}
|
catch (Exception e)
|
{
|
pLog.Error(e.Message);
|
Console.WriteLine(e.StackTrace);
|
|
if (GlobalVariable.ShowError)
|
pLog.Error(e.StackTrace);
|
|
throw e;
|
}
|
finally
|
{
|
command.Dispose();
|
}
|
}
|
|
public bool Update(RecordLog pLog, OracleConnection conn, OracleTransaction transaction)
|
{
|
if (!Check())
|
{
|
return false;
|
}
|
|
var sqlStmt = "UPDATE CCS.EVENTQUERY SET CASESTATUS=" + CaseStatus + ",CHANGETIME=to_date('"
|
+ ChangeTime + "','yyyy/mm/dd hh24:mi:ss'),ASSUMEDTIME=to_date('"
|
+ AssumedTime + "','yyyy/mm/dd hh24:mi:ss'),ASSUMEDTIME_NTH=to_date('"
|
+ AssumedTimeNth + "','yyyy/mm/dd hh24:mi:ss'),DELAYTIMES=" + DelayTimes + ",REASON='"
|
+ Reason + "',EVENTLOCATION ='" + EventLocation + "' WHERE CCSID='" + CcsId + "'";
|
|
var command = new OracleCommand(sqlStmt, conn, transaction);
|
|
try
|
{
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
pLog.Error("更新CCS.EVENTQUERY資料失敗.");
|
return false;
|
}
|
}
|
catch (Exception e)
|
{
|
pLog.Error(e.Message);
|
Console.WriteLine(e.StackTrace);
|
return false;
|
}
|
finally
|
{
|
command.Dispose();
|
}
|
|
return true;
|
}
|
|
public bool UpdateCaseStatus(SEventLog pLog, OracleConnection conn, OracleTransaction transaction)
|
{
|
var sqlStmt = "UPDATE CCS.EVENTQUERY SET CASESTATUS = " + CaseStatus + " WHERE CCSID = '" + CcsId + "'";
|
|
var command = new OracleCommand(sqlStmt, conn, transaction);
|
|
try
|
{
|
if (command.ExecuteNonQuery() <= 0)
|
{
|
pLog.Error("更新CCS.EVENTQUERY的CASESTATUS失敗.");
|
return false;
|
}
|
}
|
catch (Exception e)
|
{
|
pLog.Error(e.Message);
|
Console.WriteLine(e.StackTrace);
|
|
if (GlobalVariable.ShowError)
|
pLog.Error(e.StackTrace);
|
|
throw e;
|
}
|
finally
|
{
|
command.Dispose();
|
}
|
|
return true;
|
}
|
|
// Not Null Check
|
private bool Check()
|
{
|
if (CcsId == null)
|
{
|
return false;
|
}
|
|
if (CaseStatus == 0)
|
{
|
return false;
|
}
|
Reason = Reason ?? "";
|
return true;
|
}
|
}
|
}
|
