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;
        }
    }
}