using System;

using System.Data.OracleClient;
using NLog;

namespace CCSTrace.CCS.Object
{
    public class CCSEventRecord
    {
        private static readonly Logger Logger = LogManager.GetCurrentClassLogger();

        private readonly string _meter;
        private readonly string _customerName;
        private readonly string _customerTel;
        private readonly string _addrCity;
        private readonly string _addrTown;
        private readonly string _addrRoad;
        private readonly string _addrOther;
        private readonly string _recallName;
        private readonly string _recallTel;
        private readonly int _eventBrief;
        private readonly string _acceptTime;
        private readonly string _ccsid;
        private readonly int _importCase;
        private readonly string _dept;
        private readonly int _partHicustomer;
        private readonly string _note;

        public CCSEventRecord(string ccsid, string meter, string customername, string customertel, string addrCity, string addrTown, string addrRoad,
                         string addrOther, string recallname, string recalltel, int eventbrief, string accepttime, int importcase, string dept,
                         int partHicustomer, string note)
        {
            _meter = meter;
            _customerName = customername;
            _customerTel = customertel;
            _addrCity = addrCity;
            _addrTown = addrTown;
            _addrRoad = addrRoad;
            _addrOther = addrOther;
            _recallName = recallname;
            _recallTel = recalltel;
            _eventBrief = eventbrief; ;
            _ccsid = ccsid;
            _importCase = importcase;
            _dept = dept;
            _note = note;
            _partHicustomer = partHicustomer;
            _acceptTime = accepttime;
        }

        public CCSEventRecord(string ccsid, OracleConnection connectionTpc)
        {
            var sqlStmt = "SELECT METER,CUSTOMERNAME,CUSTOMERTEL,ADDR_CITY,ADDR_TOWN,ADDR_ROAD,ADDR_OTHER,RECALLNAME,RECALLTEL,EVENTBRIEF," +
                             "TO_CHAR(ACCEPTTIME,'YYYY/MM/DD HH24:MI:SS') AS ACCEPTTIME,CCSID,IMPORTCASE,DEPT,PARTHICUSTOMER,NOTE " +
                             "FROM CCS.EVENTRECORD WHERE CCSID = '" + ccsid + "'";

            OracleCommand command = new OracleCommand(sqlStmt, connectionTpc);
            OracleDataReader reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    _meter = reader["METER"].ToString();
                    _customerName = reader["CUSTOMERNAME"].ToString();
                    _customerTel = reader["CUSTOMERTEL"].ToString();
                    _addrCity = reader["ADDR_CITY"].ToString();
                    _addrTown = reader["ADDR_TOWN"].ToString();
                    _addrRoad = reader["ADDR_ROAD"].ToString();
                    _addrOther = reader["ADDR_OTHER"].ToString();
                    _recallName = reader["RECALLNAME"].ToString();
                    _recallTel = reader["RECALLTEL"].ToString();
                    _eventBrief = int.Parse(reader["EVENTBRIEF"].ToString());
                    _acceptTime = reader["ACCEPTTIME"].ToString();
                    _ccsid = reader["CCSID"].ToString();
                    _importCase = int.Parse(reader["IMPORTCASE"].ToString());
                    _dept = reader["DEPT"].ToString();
                    _partHicustomer = int.Parse(reader["PARTHICUSTOMER"].ToString());
                    _note = reader["NOTE"].ToString();
                }
            }
            catch (Exception e)
            {
                Logger.Error(e, "無法取得CCS報案資訊!" + e.Message);
                throw;
            }
            finally
            {
                reader.Close();
                command.Dispose();
            }
        }

        public string Meter => _meter;

        public string CustomerName => _customerName;

        public string CustomerTel => _customerTel;

        public string AddressCity => _addrCity;

        public string AddressTown => _addrTown;

        public string AddressRoad => _addrRoad;

        public string AddressOther => _addrOther;

        public string RecallTel => _recallTel;

        public string RecallName => _recallName;

        public int EventBrief => _eventBrief;

        public string AcceptTime => _acceptTime;

        public string CcsId => _ccsid;

        public int ImportCase => _importCase;

        public string Detp => _dept;

        public int PartHicustomer => _partHicustomer;

        public string Note => _note;

        public bool InsertDb(OracleConnection connectionTpc, OracleTransaction transaction)
        {
            var sqlStmt = "INSERT INTO CCS.EVENTRECORD VALUES ('" + _meter + "','" + _customerName + "','" + _customerTel + "','" + _addrCity + "','"
                             + _addrTown + "','" + _addrRoad + "','" + _addrOther + "','" + _recallName + "','" + _recallTel + "',"
                             + _eventBrief + ",TO_DATE('" + _acceptTime + "','YYYY/MM/DD HH24:MI:SS'),'" + _ccsid + "'," + _importCase + ",'" + _dept + "',"
                             + _partHicustomer + ",'" + _note + "')";

            OracleCommand command = new OracleCommand(sqlStmt, connectionTpc, transaction);

            try
            {
                if (command.ExecuteNonQuery() != 1)
                {
                    throw new Exception("CCS 案件受理程序初始化失敗...無法將CCS報案資訊存入資料庫中!");
                }
            }
            catch (Exception e)
            {
                Logger.Error(e, e.Message);
                throw;
            }
            finally
            {
                command.Dispose();
            }
            return true;
        }
    }
}