/// <summary>
        /// Creates a CreateCall object and returns it. The CreateCall has all of the data from the
        /// query results.
        /// </summary>
        /// <param name="transactionID"></param>
        /// <param name="meterNo"></param>
        /// <param name="accountNumber"></param>
        /// <returns>CreateCall message that can be placed in the MQ queue</returns>
        public CreateCall CreateCallMessage(string transactionID, string meterNo, string accountNumber)
        {
            if (!_initialized)
            {
                Initialize();
            }

            if (string.IsNullOrEmpty(meterNo))
            {
                _log.ErrorFormat("CreateCall message::meterNo is null or empty for transactionID: {0} and account number: {1}. Ignoring this request.", transactionID, accountNumber);
                return(null);
            }

            CreateCall createCall = new CreateCall(transactionID, meterNo, accountNumber);

            string query = "select * from cispersl where meter_num = '" + meterNo + "'";

            _command = new IPSCommand(query, _connection);

            _log.DebugFormat("Reading cispersl table for meterNo '{0}'", meterNo);
            bool isSuccess = false;

            isSuccess = createCall.QueryForCustomerInfo(_command);

            if (!isSuccess)
            {
                _log.WarnFormat("No data found for meterNo '{0}'", meterNo);
                return(null);
            }

            return(createCall);
        }
        /// <summary>
        /// Method to execute the query to add customer data to the object. The field names
        /// read in this method must be included in the InitializeCommand() method of the factory class.
        /// </summary>
        /// <see cref="InitializeCommand()"/>
        /// <param name="cmd"></param>

        internal bool QueryForCustomerInfo(IPSCommand cmd)
        {
            bool isRow = false;

            try
            {
                using (IPSDataAdapter da = new IPSDataAdapter(cmd))
                {
                    DataTable table = new DataTable();
                    int       nrows = da.Fill(table);

                    foreach (DataRow row in table.Rows)
                    {
                        this.name     = Convert.ToString(row["NAME"]);
                        this.location = Convert.ToString(row["LOCATION"]);
                        if (row["PHONE"] == null)
                        {
                            this.phone = string.Empty;
                        }
                        else
                        {
                            this.phone = Convert.ToString(row["PHONE"]);
                        }

                        if (row["CUST_TYPE"] == null)
                        {
                            this.phone = string.Empty;
                        }
                        else
                        {
                            this.phone = Convert.ToString(row["CUST_TYPE"]);
                        }

                        this.premise       = Convert.ToString(row["PREMISE"]);
                        this.dgroup        = Convert.ToString(row["DGROUP"]);
                        this.accountNumber = Convert.ToString(row["ACCOUNT_NUM"]);
                        this.xfmr          = Convert.ToString(row["XFMR"]);
                        this.custType      = Convert.ToString(row["CUST_TYPE"]);
                        this.meterStatus   = Convert.ToString(row["METER_STATUS"]);
                        this.meterNo       = Convert.ToString(row["METER_NUM"]);
                    }
                    if (nrows >= 1)
                    {
                        isRow = true;
                    }
                }
                StringBuilder sb = new StringBuilder();
                sb.Append("CALLBACK=N;");
                sb.AppendFormat("NAME={0};", this.name);
                sb.AppendFormat("CUST_TYPE={0};", "A");  // InService wants 'A' in this field so it can render the calls on the map as an 'A' per Pratap 3/7/2013
                sb.AppendFormat("LOCATION={0};", this.location);
                sb.AppendFormat("PHONE={0};", this.phone);
                sb.Append("PRIORITY=;");
                sb.AppendFormat("PREMISE={0};", this.premise);
                sb.AppendFormat("METER={0};", this.meterNo);
                sb.Append("AGENCY=OUTAGE;");
                sb.AppendFormat("DGROUP={0};", this.dgroup);
                sb.Append("IS_METER=T;");
                sb.AppendFormat("ACCT={0};", this.accountNumber);
                sb.AppendFormat("XFMR={0}", this.xfmr);
                sb.Append("REMARKS=Call Created By AMI;");

                fieldvalues = sb.ToString();
            }
            catch (IPSDataException o)
            {
                _log.ErrorFormat("Exception in QueryForCustomerInfo() method. Message: {0}", o.Message);
            }

            return(isRow);
        }
        public void processRestoreMessage(string meterNumber, string id, string meterOffDts)
        {
            string query       = "";
            string sName       = "";
            string sPhone      = "";
            string sLocation   = "";
            string sXfmr       = "";
            string sAccountNum = "";
            string sMeterNum   = "";
            long   lPremiseID  = 0;
            int    queryResult = 0;

            string sDate = String.Format("{0:yyyyMMddHHmmss}", System.DateTime.Now).ToString();

            try
            {
                IPSTransaction transaction = _connection.BeginTransaction();

                // query1
                query = "select name, phone, location, xfmr, account_num, meter_num, premise from cispersl where meter_num = '" + meterNumber + "'";
                _log.DebugFormat("processRestoreMessage()::query1 - {0}", query);

                using (IPSCommand cmd = new IPSCommand(query, _connection))
                {
                    using (IPSDataAdapter da = new IPSDataAdapter(cmd))
                    {
                        DataTable table = new DataTable();
                        int       nrows = da.Fill(table);

                        foreach (DataRow row in table.Rows)
                        {
                            sName       = row["name"].ToString();
                            sPhone      = row["phone"].ToString();
                            sLocation   = row["location"].ToString();
                            sXfmr       = row["xfmr"].ToString();
                            sAccountNum = row["account_num"].ToString();
                            sMeterNum   = row["meter_num"].ToString();
                            lPremiseID  = Convert.ToInt64(row["premise"]);
                        }
                    }
                }// query1 end

                int iReqID = 0;
                int iID    = 1;

                //query2
                query = "select n_num from numbr where purpose like 'AMI Unsolicited%' and curent='T'";
                _log.DebugFormat("processRestoreMessage()::query2 - {0}", query);

                using (IPSCommand cmd = new IPSCommand(query, _connection))
                {
                    using (IPSDataAdapter da = new IPSDataAdapter(cmd))
                    {
                        DataTable table = new DataTable();
                        int       nrows = da.Fill(table);

                        foreach (DataRow row in table.Rows)
                        {
                            if (row["n_num"] != System.DBNull.Value)
                            {
                                iID = Convert.ToInt32(row["n_num"]);
                            }
                            else
                            {
                                iID = iID + 1;
                            }
                        }
                    }
                } //query2 end

                String dst_value   = String.Empty;
                String std_value   = String.Empty;
                String is_dst_used = String.Empty;
                String suffix      = String.Empty;

                //query3
                query = "select is_dst_used, dst_name, std_name from time_zone_info";
                _log.DebugFormat("processRestoreMessage()::query3 - {0}", query);

                using (IPSCommand cmd = new IPSCommand(query, _connection))
                {
                    using (IPSDataAdapter da = new IPSDataAdapter(cmd))
                    {
                        DataTable table = new DataTable();
                        int       nrows = da.Fill(table);

                        foreach (DataRow row in table.Rows)
                        {
                            is_dst_used = row["is_dst_used"].ToString();
                            dst_value   = row["dst_name"].ToString();
                            std_value   = row["std_name"].ToString();
                        }
                    }
                }// query3 end

                if (is_dst_used != null)
                {
                    if (is_dst_used == "T")
                    {
                        suffix = dst_value;
                    }
                    else
                    {
                        suffix = std_value;
                    }
                }
                else
                {
                    suffix = dst_value;   //by default
                }

                //query4
                int xfmr_mslink = 0;
                if (!string.IsNullOrEmpty(sXfmr))
                {
                    query = "select mslink from OMS_TRANSFORMER where name = '" + sXfmr + "'";
                    _log.DebugFormat("processRestoreMessage()::query4 - {0}", query);

                    using (IPSCommand cmd = new IPSCommand(query, _connection))
                    {
                        using (IPSDataAdapter da = new IPSDataAdapter(cmd))
                        {
                            DataTable table = new DataTable();
                            int       nrows = da.Fill(table);

                            foreach (DataRow row in table.Rows)
                            {
                                xfmr_mslink = Convert.ToInt32(row["mslink"]);
                            }
                        }
                    }// query4 end
                }

                //query5
                query  = "INSERT INTO AMI_DATA_RESPONSE_DETAILS (METER_NUM, METER_RESPONSE, ";
                query += "METER_RESPONSE_DTS, PROCESSING_COMPLETE, REQUEST_ID, UNSOLICITED_NOTIFICATION_ID, ";
                query += "XFMR_NAME, XFMR_MSLINK) VALUES ( '" + sMeterNum + "', 'Meter On', '" + sDate + suffix + "', 'T', " + iReqID + ", " + iID + ", '" + sXfmr + "' , " + xfmr_mslink + " )";

                _log.DebugFormat("processRestoreMessage()::query5 - {0}", query);

                using (IPSCommand cmd = new IPSCommand(query, _connection))
                {
                    queryResult = cmd.ExecuteNonQuery();
                } //query5 end

                //query6
                query = "update numbr set n_num = n_num + 1 where purpose like 'AMI Request%' and curent='T'";

                _log.DebugFormat("processRestoreMessage()::query6 - {0}", query);

                using (IPSCommand cmd = new IPSCommand(query, _connection))
                {
                    queryResult = cmd.ExecuteNonQuery();
                }
                //query6 end

                //query7
                query = "update numbr set n_num = n_num + 1 where purpose like 'AMI Unsolicited%' and curent='T'";

                _log.DebugFormat("processRestoreMessage()::query7 - {0}", query);

                using (IPSCommand cmd = new IPSCommand(query, _connection))
                {
                    queryResult = cmd.ExecuteNonQuery();
                }
                //query7 end

                transaction.Commit();
            }
            catch (Exception e)
            {
                _log.ErrorFormat("Exception in processRestoreMessage() method. Message: {0}", e.Message);
            }
        }