/// <summary>
 /// Constructor
 /// </summary>
 public DatabasePoller(DbMessenger messenger, MessageGatewayService service)
     : base(messenger.PollingInterval * 1000)
 {
     this.messenger = messenger;
     this.messageGatewayService = service;
     this.isRunning = false;
 }
 public static void Setup(int testItems) {
     SetTestRepo();
     for(int i=0;i<testItems;i++){
         DbMessenger item=new DbMessenger();
         _testRepo._items.Add(item);
     }
 }
 public static void Setup(DbMessenger item) {
     SetTestRepo();
     _testRepo._items.Add(item);
 }
 /// <summary>
 /// Generates the message id.
 /// </summary>
 /// <param name="messenger">The messenger.</param>
 /// <param name="id">The message id.</param>
 /// <returns></returns>
 public static string GenerateMessageId(DbMessenger messenger, string id)
 {
     return MessagePrefix + GuiHelper.FieldSplitter + messenger.Name + GuiHelper.FieldSplitter + id ;
 }
 /// <summary>
 /// Builds the connection string.
 /// </summary>
 /// <param name="messenger">The messenger.</param>
 /// <returns></returns>
 private static string BuildConnString(DbMessenger messenger)
 {
     if (messenger.RequiredAuth)
     {
         return string.Format("DSN={0};Uid={1};Pwd={2};", messenger.Dsn, messenger.DbUserName, messenger.DbUserPassword);
     }
     else
     {
         return string.Format("DSN={0}", messenger.Dsn);
     }
 }
        /// <summary>
        /// Deletes the message.
        /// </summary>
        /// <param name="messenger">The messenger.</param>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        public static bool DeleteMsg(DbMessenger messenger, string id)
        {
            OdbcConnection dbConnection = null;
            OdbcCommand dbCommand = null;
            try
            {
                string connString = BuildConnString(messenger);
                DataType idColDataType = (DataType)StringEnum.Parse(typeof(DataType), messenger.UniqMsgIdColDataType);
                string sqlCommand = string.Empty;
                sqlCommand = string.Format("DELETE FROM {0} ", messenger.DbTable);

               
                if (idColDataType == DataType.Numeric)
                {
                    sqlCommand += " WHERE {0}={1}";
                }
                else
                {
                    sqlCommand += " WHERE {0}='{1}'";
                }

                sqlCommand = string.Format(sqlCommand, messenger.UniqMsgIdColName, id);
                dbConnection = new OdbcConnection(connString);
                dbConnection.Open();
                dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandText = sqlCommand;

                int rowCount = dbCommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                string msg = string.Format("Messenger [{0}] error, DSN is [{1}]", messenger.Name, messenger.Dsn);
                log.Error(msg, ex);
                return false;
            }
            finally
            {
                if (dbCommand != null)
                {
                    dbCommand.Dispose();
                }

                if (dbConnection != null)
                {
                    dbConnection.Close();
                    dbConnection.Dispose();
                    dbConnection = null;
                }
            }
        }
        /// <summary>
        /// Updates the status.
        /// </summary>
        /// <param name="messenger">The messenger.</param>
        /// <param name="row">The row.</param>
        /// <param name="id">The id.</param>
        /// <param name="status">The status.</param>
        /// <returns></returns>
        public static bool UpdateStatus(DbMessenger messenger, string id, string status)
        {
            OdbcConnection dbConnection = null;
            OdbcCommand dbCommand = null;          
            try
            {
                string connString = BuildConnString(messenger);
                DataType idColDataType = (DataType)StringEnum.Parse(typeof(DataType), messenger.UniqMsgIdColDataType);
                string sqlCommand = string.Empty;
                sqlCommand = string.Format("UPDATE {0} SET {1}='{2}'", messenger.DbTable, messenger.StatusColName, status);

                if (!string.IsNullOrEmpty(messenger.StatusTimestampColName)) 
                {
                    sqlCommand += ", " + messenger.StatusTimestampColName + "=?";
                }

                if (idColDataType == DataType.Numeric)
                {
                    sqlCommand += " WHERE {0}={1}";
                }
                else
                {
                    sqlCommand += " WHERE {0}='{1}'";
                }
                
                sqlCommand = string.Format(sqlCommand, messenger.UniqMsgIdColName, id);
                dbConnection = new OdbcConnection(connString);
                dbConnection.Open();
                dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandText = sqlCommand;

                if (!string.IsNullOrEmpty(messenger.StatusTimestampColName))
                {
                    dbCommand.Parameters.Add("@ts", OdbcType.DateTime);
                    dbCommand.Parameters["@ts"].Value = DateTime.Now;
                }
                int rowCount = dbCommand.ExecuteNonQuery();              
                return true;
            }
            catch (Exception ex)
            {
                string msg = string.Format("Messenger [{0}] error, DSN is [{1}]", messenger.Name, messenger.Dsn);
                log.Error(msg, ex);
                return false;
            }
            finally
            {  
                if (dbCommand != null)
                {
                    dbCommand.Dispose();
                }

                if (dbConnection != null)
                {
                    dbConnection.Close();
                    dbConnection.Dispose();
                    dbConnection = null;
                }
            }
        }
        /// <summary>
        /// Gets the records.
        /// </summary>
        /// <param name="messenger">The messenger.</param>
        /// <returns></returns>
        public static DataTable GetRecords(DbMessenger messenger)
        {
            OdbcConnection dbConnection = null;
            OdbcCommand dbCommand = null;
            OdbcDataReader dbReader = null;
            try
            {
                string connString = BuildConnString(messenger);
                string sqlCommand = string.Format("SELECT * FROM {0}", messenger.DbTable);
                if (!messenger.DeleteAfterSending)
                {
                    sqlCommand = string.Format("{0} WHERE {1}='{2}'", sqlCommand, messenger.StatusColName, messenger.StatusColNewValue);
                }
                dbConnection = new OdbcConnection(connString);
                dbConnection.Open();
                dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandText = sqlCommand;
                dbReader = dbCommand.ExecuteReader();               
                DataTable dt = new DataTable();
                dt.TableName = messenger.DbTable;
                dt.Load(dbReader);
                dbReader.Close();
                return dt;
            }
            catch (Exception ex)
            {
                string msg = string.Format("Messenger [{0}] error, DSN is [{1}]", messenger.Name, messenger.Dsn);
                log.Error(msg, ex);
                throw new SmartGatewayException(msg, ex);
            }
            finally
            {
                if (dbReader != null && !dbReader.IsClosed)
                {                   
                    dbReader.Close();                   
                }
                if (dbCommand != null)
                {
                    dbCommand.Dispose();
                }

                if (dbConnection != null)
                {
                    dbConnection.Close();
                    dbConnection.Dispose();
                    dbConnection = null;
                }
            }
        }
        /// <summary>
        /// Saves the changes.
        /// </summary>
        /// <returns></returns>
        private bool SaveChanges()
        {
            // Validate the name is not empty
            if (!FormHelper.ValidateNotEmpty(txtName, Resources.MsgUniqueNameRequired))
            {
                return false;
            }
            string name = txtName.Text.Trim();
            if (!this.IsUpdate)
            {
                // The messenger name must be unique 
                if (DbMessenger.Exists(m => m.Name.ToLower() == name.ToLower()))
                {
                    FormHelper.ShowError(txtName, string.Format(Resources.MsgMessengerNameAlreadyExists, name));
                    return false;
                }              
            }
            
            if (!FormHelper.ValidateNotEmpty(cboDsn, Resources.MsgDsnRequired))
            {
                return false;
            }

            if (!FormHelper.ValidateNotEmpty(txtDbTable, Resources.MsgDbTableRequired))
            {
                return false;
            }

            if (chkRequireAuthentication.Checked)
            {
                if (!FormHelper.ValidateNotEmpty(txtDbUserName, Resources.MsgDbUserNameRequired))
                {
                    return false;
                }

                if (!FormHelper.ValidateNotEmpty(txtDbPassword, Resources.MsgDbUserPasswordRequired))
                {
                    return false;
                }
            }    


            if (!FormHelper.ValidateNotEmpty(txtUniqMsgIdColName, Resources.MsgUniqMsgIdColNameRequired))
            {
                return false;
            }

            if (!FormHelper.ValidateNotEmpty(txtDestNoColName, Resources.MsgDestNoColNameRequired))
            {
                return false;
            }

            if (!FormHelper.ValidateNotEmpty(txtDestNoColName, Resources.MsgDestNoColNameRequired))
            {
                return false;
            }

            if (!FormHelper.ValidateNotEmpty(txtMsgColName, txtDefaultTextMsg, Resources.MsgMsgColDefaultMsgRequired))
            {
                return false;
            }

            if (chkDeleteAfterSending.Checked)
            {
                if (!FormHelper.ValidateNotEmpty(txtStatusColName, Resources.MsgStatusColNameRequired))
                {
                    return false;
                }              
            }           


            try
            {
                // Save the messenger configuration
                DbMessenger dbMessenger = new DbMessenger();

                if (this.IsUpdate)
                {
                    dbMessenger = DbMessenger.SingleOrDefault(m => m.Name.ToLower() == name.ToLower());
                }
                               
                dbMessenger.Name = name;
                dbMessenger.Description = txtDescription.Text;
                dbMessenger.Dsn = cboDsn.Text;
                dbMessenger.DbTable = txtDbTable.Text;
                dbMessenger.PollingInterval = Convert.ToInt32(npdPollingInterval.Value);
                dbMessenger.RequiredAuth = chkRequireAuthentication.Checked;
                dbMessenger.DbUserName = txtDbUserName.Text;
                dbMessenger.DbUserPassword = txtDbPassword.Text;
                dbMessenger.UniqMsgIdColName = txtUniqMsgIdColName.Text;
                if (radMsgIdDataTypeString.Checked)
                    dbMessenger.UniqMsgIdColDataType = StringEnum.GetStringValue(DataType.String);
                else
                    dbMessenger.UniqMsgIdColDataType = StringEnum.GetStringValue(DataType.Numeric);

                dbMessenger.MsgColName = txtMsgColName.Text;
                dbMessenger.DestNoColName = txtDestNoColName.Text;
                dbMessenger.MsgPriorityColName = txtMsgPriorityColName.Text;
                dbMessenger.MsgAlertColName = txtMsgAlertColName.Text;
                dbMessenger.DefaultMsgPriority = cboDefaultMsgPriority.Text;
                dbMessenger.DefaultTextMsg = txtDefaultTextMsg.Text;
                dbMessenger.DeleteAfterSending = chkDeleteAfterSending.Checked;
                dbMessenger.StatusColName = txtStatusColName.Text;
                dbMessenger.StatusTimestampColName = txtStatusTimestampColName.Text;
                dbMessenger.StatusColNewValue = txtStatusColValue.Text;
                dbMessenger.StatusColUpdateSuccessVal = txtStatusColUpdateSentValue.Text;
                dbMessenger.StatusColUpdateFailedValue = txtStatusColUpdateFailedValue.Text;
                dbMessenger.StatusColUpdateSendingValue = txtStatusColUpdateSendingValue.Text;
                dbMessenger.AutoStart = chkAutoStart.Checked;           


                if (!this.IsUpdate)
                    dbMessenger.Save();
                else
                    dbMessenger.Update();

                if (MessengerAdded != null)
                {
                    // Raise the event
                    MessengerEventHandlerArgs arg = new MessengerEventHandlerArgs(name);
                    this.MessengerAdded.BeginInvoke(this, arg, new AsyncCallback(this.AsyncCallback), null);
                }

                if (MessengerUpdated != null)
                {
                    // Raise the event
                    MessengerEventHandlerArgs arg = new MessengerEventHandlerArgs(name);
                    this.MessengerUpdated.BeginInvoke(this, arg, new AsyncCallback(this.AsyncCallback), null);
                }
            }
            catch (Exception ex)
            {
                FormHelper.ShowError(ex.Message);
                return false;
            }

            // Reset to false
            isFormChanged = false;

            // Show successful save message
            FormHelper.ShowInfo(Resources.MsgMessengerConfigSaved);

            // Return true as saving is successful
            return true;
        }