Exemple #1
0
        public void InsertGroupEmail(SqlConnection con, SqlTransaction tran, BaseGroupEmailParameter param, List <string> listUserFQN, string IDReference)
        {
            StringBuilder sbQueryInsert = new StringBuilder();

            sbQueryInsert.Append("INSERT INTO [dbo].[OutboxEmail] (");
            sbQueryInsert.Append("[From], [To], [Cc], [Bcc], [Subject], [Body]");
            sbQueryInsert.Append(", [Status], [IDEmailTemplate], [CreatedDate]");
            sbQueryInsert.Append(", [IDReference], [SendDate]");
            sbQueryInsert.Append(") VALUES ");

            int userIndex    = 0;
            int userHasEmail = 0;

            using (SqlCommand cmd = con.CreateCommand())
            {
                foreach (string userFQN in listUserFQN)
                {
                    UserADServices svcUserAD = new UserADServices();
                    svcUserAD.AppConfig = this.AppConfig;
                    UserAD userAD = svcUserAD.GetByFQN(userFQN);
                    if (userAD != null)
                    {
                        if (!string.IsNullOrEmpty(userAD.Email))
                        {
                            if (userIndex > 0)
                            {
                                sbQueryInsert.Append(",");
                            }
                            sbQueryInsert.Append("(");
                            sbQueryInsert.Append("@From, @To_" + userIndex.ToString() + ", NULL, NULL, @Subject, @Body");
                            sbQueryInsert.Append(", '0', @IDEmailTemplate, GETDATE()");
                            sbQueryInsert.Append(", @IDReference, NULL");
                            sbQueryInsert.Append(")");

                            cmd.Parameters.Add(this.NewSqlParameter("To_" + userIndex.ToString(), SqlDbType.VarChar, userAD.Email));

                            userIndex++;
                            userHasEmail++;
                        }
                    }
                }

                if (userHasEmail > 0)
                {
                    cmd.Transaction = tran;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sbQueryInsert.ToString();

                    cmd.Parameters.Add(this.NewSqlParameter("From", SqlDbType.VarChar, param.From));
                    cmd.Parameters.Add(this.NewSqlParameter("Subject", SqlDbType.VarChar, param.Subject));
                    cmd.Parameters.Add(this.NewSqlParameter("Body", SqlDbType.VarChar, param.Body));
                    cmd.Parameters.Add(this.NewSqlParameter("IDEmailTemplate", SqlDbType.VarChar, param.IDEmailTemplate));
                    cmd.Parameters.Add(this.NewSqlParameter("IDReference", SqlDbType.VarChar, "CM-" + IDReference));

                    cmd.ExecuteNonQuery();
                }
            }
        }
Exemple #2
0
        public void ReminderInsertOutboxEmail()
        {
            string conStringIIF = this.AppConfig.IIFConnectionString;

            using (SqlConnection con = new SqlConnection(conStringIIF))
            {
                con.Open();

                string querySelect = "SELECT";
                querySelect = querySelect + " [SourceType], [SourceId]";
                querySelect = querySelect + ", [MDocTypeId], [DocumentId], [UserFQN]";
                querySelect = querySelect + ", [Reminder_Subject], [Reminder_Body]";
                querySelect = querySelect + ", [Reminder_IDEmailTemplate], [Reminder_IDReference]";
                querySelect = querySelect + " FROM [dbo].[Vw_PendingReminder]";

                List <Vw_PendingReminder> listReminder = new List <Vw_PendingReminder>();

                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = querySelect;

                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        int indexOf_SourceType               = dr.GetOrdinal("SourceType");
                        int indexOf_SourceId                 = dr.GetOrdinal("SourceId");
                        int indexOf_MDocTypeId               = dr.GetOrdinal("MDocTypeId");
                        int indexOf_DocumentId               = dr.GetOrdinal("DocumentId");
                        int indexOf_UserFQN                  = dr.GetOrdinal("UserFQN");
                        int indexOf_Reminder_Subject         = dr.GetOrdinal("Reminder_Subject");
                        int indexOf_Reminder_Body            = dr.GetOrdinal("Reminder_Body");
                        int indexOf_Reminder_IDEmailTemplate = dr.GetOrdinal("Reminder_IDEmailTemplate");
                        int indexOf_Reminder_IDReference     = dr.GetOrdinal("Reminder_IDReference");

                        while (dr.Read())
                        {
                            Vw_PendingReminder reminder = new Vw_PendingReminder();
                            reminder.SourceType               = dr.GetInt32(indexOf_SourceType);
                            reminder.SourceId                 = dr.GetInt64(indexOf_SourceId);
                            reminder.MDocTypeId               = dr.GetInt32(indexOf_MDocTypeId);
                            reminder.DocumentId               = dr.GetInt64(indexOf_DocumentId);
                            reminder.UserFQN                  = dr.GetString(indexOf_UserFQN);
                            reminder.Reminder_From            = this.AppConfig.SMTPFromEmail;
                            reminder.Reminder_Subject         = dr.GetString(indexOf_Reminder_Subject);
                            reminder.Reminder_Body            = dr.GetString(indexOf_Reminder_Body);
                            reminder.Reminder_IDEmailTemplate = dr.GetString(indexOf_Reminder_IDEmailTemplate);
                            reminder.Reminder_IDReference     = dr.GetString(indexOf_Reminder_IDReference);

                            listReminder.Add(reminder);
                        }
                    }
                }

                foreach (Vw_PendingReminder reminder in listReminder)
                {
                    try
                    {
                        UserADServices svcUserAD = new UserADServices();
                        svcUserAD.AppConfig = this.AppConfig;
                        UserAD userAD = svcUserAD.GetByFQN(reminder.UserFQN);
                        if (userAD != null)
                        {
                            if (!string.IsNullOrEmpty(userAD.Email))
                            {
                                StringBuilder sbQueryInsert = new StringBuilder();
                                sbQueryInsert.Append("INSERT INTO [dbo].[OutboxEmail] (");
                                sbQueryInsert.Append("[From], [To], [Cc], [Bcc], [Subject], [Body]");
                                sbQueryInsert.Append(", [Status], [IDEmailTemplate], [CreatedDate]");
                                sbQueryInsert.Append(", [IDReference], [SendDate]");
                                sbQueryInsert.Append(") VALUES (");
                                sbQueryInsert.Append("@From, @To, NULL, NULL, @Subject, @Body");
                                sbQueryInsert.Append(", 0, @IDEmailTemplate, GETDATE()");
                                sbQueryInsert.Append(", @IDReference, NULL");
                                sbQueryInsert.Append(");");

                                sbQueryInsert.Append("UPDATE [dbo].[WorkflowHistory] SET");
                                sbQueryInsert.Append("[LastReminderDate] = GETDATE()");
                                sbQueryInsert.Append("WHERE [Id] = @SourceId");

                                using (SqlCommand cmd = con.CreateCommand())
                                {
                                    cmd.CommandType = CommandType.Text;
                                    cmd.CommandText = sbQueryInsert.ToString();

                                    cmd.Parameters.Add(this.NewSqlParameter("From", SqlDbType.VarChar, reminder.Reminder_From));
                                    cmd.Parameters.Add(this.NewSqlParameter("To", SqlDbType.VarChar, userAD.Email));
                                    cmd.Parameters.Add(this.NewSqlParameter("Subject", SqlDbType.VarChar, reminder.Reminder_Subject));
                                    cmd.Parameters.Add(this.NewSqlParameter("Body", SqlDbType.VarChar, reminder.Reminder_Body));
                                    cmd.Parameters.Add(this.NewSqlParameter("IDEmailTemplate", SqlDbType.VarChar, reminder.Reminder_IDEmailTemplate));
                                    cmd.Parameters.Add(this.NewSqlParameter("IDReference", SqlDbType.VarChar, reminder.Reminder_IDReference));

                                    cmd.Parameters.Add(this.NewSqlParameter("SourceId", SqlDbType.BigInt, reminder.SourceId));

                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Logger.Error(ex.Message, ex);
                    }
                }
                con.Close();
            }
        }