using System;

using System.Data.OracleClient;

namespace CCSTrace.CCS.Object
{
    public class CcsRecord
    {
        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 CcsRecord(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 CcsRecord(string ccsid, OracleConnection connectionTpc, SEventLog pLog)
        {
            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)
            {
                pLog.Error("無法取得CCS報案資訊!" + e.Message);
                Console.WriteLine(e.StackTrace);

                if (GlobalVariable.ShowError)
                    pLog.Error(e.StackTrace);

                throw e;
            }
            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, SEventLog pLog)
        {
            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)
                {
                    pLog.Error("CCS 案件受理程序初始化失敗...無法將CCS報案資訊存入資料庫中!");

                    throw new Exception("CCS 案件受理程序初始化失敗...無法將CCS報案資訊存入資料庫中!");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.StackTrace);

                if (GlobalVariable.ShowError)
                    pLog.Error(e.StackTrace);

                pLog.Error("CCS 案件受理程序初始化失敗...無法將CCS報案資訊存入資料庫中!" + e.Message);
                //throw new Exception("CCS 案件受理程序初始化失敗...無法將CCS報案資訊存入資料庫中");
                throw;
            }
            finally
            {
                command.Dispose();
            }
            return true;
        }
    }
}