internal void SendNotifcations()
{
RecipientsType.ForEach(item =>
{
item.FilttersRecipient.ForEach(r =>
{
QueryReturn = "";
ParamsDynamicTableDataEvent.ForEach(d => {
QueryReturn = r.Filtter.Replace(d.IdParam, d.ValueParam);
});
using (Connection = new SqlConnection(ConectionString))
{
Connection.Open();
#pragma warning disable S3649 // User-provided values should be sanitized before use in SQL statements
using (Command = new SqlCommand(QueryReturn, Connection))
#pragma warning restore S3649 // User-provided values should be sanitized before use in SQL statements
{
MessageBase = item.Message.Message;
TitleMessage = item.Message.TitleMessage;
DataAdapter = new SqlDataAdapter(Command);
DataTable = new DataTable();
DataAdapter.Fill(DataTable);
String table = QueryReturn.ToUpper();
if (table.Contains("FROM"))
{
table = table.Substring(table.LastIndexOf("FROM"));
}
if (table.Contains("WHERE"))
{
table = table.Substring(0, table.LastIndexOf("WHERE"));
}
table = table.Replace("FROM", "").Replace("WHERE", "");
String[] items;
if (table.Contains(","))
{
items = table.Split(',');
table = items[0].Trim();
}
else
{
table = table.Trim();
}
if (table.Contains(" "))
{
int index = QueryReturn.IndexOf(" ");
table = table.Substring(0, index + 1);
}
String valueReplace = "";
foreach (DataRow dtRow in DataTable.Rows)
{
IdTables = SchemaTables.GetColumKeys(ConectionString, Connection.Database, table);
JsonRow = JsonConvert.SerializeObject(dtRow.Table).Replace("[", "").Replace("]", "");
ObjectJson = (JObject)JsonConvert.DeserializeObject(JsonRow);
IdTables.ForEach(i => { ObjectJson.Property(i)?.Remove(); });
JsonRow = ObjectJson.ToString();
item.Message.ConfigMessage.ForEach(c =>
{
valueReplace = "";
if (String.IsNullOrEmpty(c.DefinitiveValue))
{
c.NameColum.ForEach(d =>
{
foreach (var prop in ObjectJson)
{
if (prop.Key.ToUpper().Contains(d.ToUpper()))
{
valueReplace = prop.Value?.ToString();
break;
}
}
});
if (String.IsNullOrEmpty(valueReplace))
{
c.ExpressionRegular.ForEach(f =>
{
Regex = new Regex(f);
MachtExpression = Regex.Match(JsonRow);
if (MachtExpression.Groups.Count > 0)
{
valueReplace = MachtExpression.Groups[0].Value;
}
Regex = null;
MachtExpression = null;
});
}
}
else
{
valueReplace = c.DefinitiveValue;
}
if (!String.IsNullOrEmpty(valueReplace))
{
MessageBase = MessageBase.Replace(c.DinamycParam, valueReplace);
}
});
r.TypeNotification.ForEach(t => {
string errorMessage = "Type Notification not support";
bool sendNotification = false;
Regex = new Regex(t.ExpressionRegularMach);
MachtExpression = Regex.Match(JsonRow);
if (Enum.TryParse(t.TypeNotification, out TypeNotification typeNotification) && !String.IsNullOrEmpty(MachtExpression.Value))
{
sendNotification = Notification.Send(MachtExpression.Value, TitleMessage, MessageBase, t.JsonNotificationConfig, typeNotification, t.Provaider, out errorMessage);
}
if (sendNotification)
{
errorMessage = "message send";
}
else if (String.IsNullOrEmpty(MachtExpression.Value))
{
errorMessage = "no se encontro en la consulta un destinatario";
}
Connection.Insert(new NotificationLog()
{
Destination = MachtExpression.Groups[0].Value,
MessageErrorProvaider = errorMessage,
CreatedById = "NotifyDll",
MessageSend = MessageBase,
NotificationName = NotficationName,
NameRecipientsType = item.RecipientName,
FillterRecipenttype = QueryReturn,
Provaider = t.Provaider,
TypeNotification = t.TypeNotification,
TitleMessage = TitleMessage,
CreatedAt = DateTime.Now,
IsSend = sendNotification
});
//debe enviarse a grabar a la base de datos esta informacion
});
}
MessageBase = null;
TitleMessage = null;
}
}
});
});
}
public List <DynamicQueryParam> GetParamsDynamicTableDataEventQuery(SqlNotificationEventArgs e, String conectionString, String queryExecutePost, SqlConnection connection, String fillterCondition, String tableEvent, String columsNotify, DateTime dateTime)
{
ISchemaTables schemaTables = new SchemaTables();
List <DynamicQueryParam> listKeys = new List <DynamicQueryParam>();
List <String> idTables = schemaTables.GetColumKeys(conectionString, connection.Database, tableEvent);
String colums = "";
int countColum = 0;
idTables.ForEach(c => {
if (countColum == 0 && !columsNotify.Contains(c))
{
colums = c;
}
else if (!columsNotify.Contains(c))
{
colums += "," + c;
}
countColum++;
});
if (String.IsNullOrEmpty(colums))
{
colums += columsNotify;
}
else
{
colums += "," + columsNotify;
}
String query = "";
switch (e.Info)
{
case SqlNotificationInfo.Insert:
query = String.Format("Select Top 1 {0} From {1} where CreatedAt < @datetime Order by CreatedAt desc", colums, tableEvent);
break;
case SqlNotificationInfo.Update:
query = String.Format("Select Top 1 {0} From {1} where UpdatedAt < @datetime Order by UpdatedAt desc", colums, tableEvent);
break;
}
using (connection = new SqlConnection(conectionString))
{
#pragma warning disable S3649 // User-provided values should be sanitized before use in SQL statements
using (SqlCommand command = new SqlCommand(query, connection))
#pragma warning restore S3649 // User-provided values should be sanitized before use in SQL statements
{
SqlParameter parameter = command.Parameters.Add("@datetime", System.Data.SqlDbType.DateTime);
// Set the value.
parameter.Value = dateTime;
command.Notification = null;
connection.Open();
// Execute the command.
using (SqlDataReader reader = command.ExecuteReader())
{
DataTable dataTable = new DataTable();
dataTable.Load(reader);
if (dataTable.Rows.Count > 0)
{
DataRow row = dataTable.Rows[0];
String JsonRow = JsonConvert.SerializeObject(row.Table).Replace("[", "").Replace("]", "");
JObject ObjectJson = (JObject)JsonConvert.DeserializeObject(JsonRow);
foreach (var prop in ObjectJson)
{
if (!String.IsNullOrEmpty(fillterCondition) && fillterCondition.Contains("@" + prop.Key))
{
fillterCondition = fillterCondition.Replace("@" + prop.Key, prop.Value?.ToString());
}
if (!String.IsNullOrEmpty(queryExecutePost) && queryExecutePost.Contains("@" + prop.Key))
{
queryExecutePost = queryExecutePost.Replace("@" + prop.Key, prop.Value?.ToString());
}
if (idTables.Contains(prop.Key))
{
listKeys.Add(new DynamicQueryParam()
{
IdParam = "@" + prop.Key, ValueParam = prop.Value?.ToString()
});
}
}
}
}
if (!String.IsNullOrEmpty(fillterCondition))
{
using (SqlCommand commandValidate = new SqlCommand(fillterCondition, connection))
{
using (SqlDataReader reader = commandValidate.ExecuteReader())
{
DataTable dataTable = new DataTable();
dataTable.Load(reader);
if (dataTable.Rows.Count <= 0)
{
listKeys = null;
}
}
}
}
if (!String.IsNullOrEmpty(queryExecutePost))
#pragma warning disable S3966 // Objects should not be disposed more than once
{
using (connection)
#pragma warning restore S3966 // Objects should not be disposed more than once
{
connection.Open();
using (SqlCommand commandValidate = new SqlCommand(queryExecutePost, connection))
#pragma warning disable S108 // Nested blocks of code should not be left empty
{
}
#pragma warning restore S108 // Nested blocks of code should not be left empty
}
}
}
}
return(listKeys);
}
private void CmbDestination_SelectedValueChanged(object sender, EventArgs e)
{
PnlNortificationType.Controls.Clear();
KeysTable = SchemaTables.GetColumKeys(Constants.CONECTION_STRING, Constants.DB_NAME, CmbDestination.SelectedValue.ToString());
}
public List <DynamicQueryParam> GetParamsDynamicTableDataEvenTable <T>(T entity, String conectionString, SqlConnection connection, String fillterCondition, String queryExecutePost, String tableEvent, String columsNotify, DateTime dateTime) where T : class
{
ISchemaTables schemaTables = new SchemaTables();
List <DynamicQueryParam> listKeys = new List <DynamicQueryParam>();
List <String> idTables = schemaTables.GetColumKeys(conectionString, connection.Database, tableEvent);
String colums = "";
int countColum = 0;
idTables.ForEach(c => {
if (countColum == 0 && !columsNotify.Contains(c))
{
colums = c;
}
else if (!columsNotify.Contains(c))
{
colums += "," + c;
}
countColum++;
});
if (String.IsNullOrEmpty(colums))
{
colums += columsNotify;
}
else
{
colums += "," + columsNotify;
}
JObject ObjectJson = (JObject)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(entity));
foreach (var prop in ObjectJson)
{
if (!String.IsNullOrEmpty(fillterCondition) && fillterCondition.Contains("@" + prop.Key))
{
fillterCondition = fillterCondition.Replace("@" + prop.Key, prop.Value?.ToString());
}
if (!String.IsNullOrEmpty(queryExecutePost) && queryExecutePost.Contains("@" + prop.Key))
{
queryExecutePost = queryExecutePost.Replace("@" + prop.Key, prop.Value?.ToString());
}
if (idTables.Contains(prop.Key))
{
listKeys.Add(new DynamicQueryParam()
{
IdParam = "@" + prop.Key, ValueParam = prop.Value?.ToString()
});
}
}
if (!String.IsNullOrEmpty(fillterCondition))
{
using (connection)
{
connection.Open();
using (SqlCommand commandValidate = new SqlCommand(fillterCondition, connection))
{
using (SqlDataReader reader = commandValidate.ExecuteReader())
{
DataTable dataTable = new DataTable();
dataTable.Load(reader);
if (dataTable.Rows.Count <= 0)
{
listKeys = null;
}
}
}
}
}
if (!String.IsNullOrEmpty(queryExecutePost))
{
#pragma warning disable S3966 // Objects should not be disposed more than once
using (connection)
#pragma warning restore S3966 // Objects should not be disposed more than once
{
connection.Open();
using (SqlCommand commandValidate = new SqlCommand(queryExecutePost, connection))
#pragma warning disable S108 // Nested blocks of code should not be left empty
{
}
#pragma warning restore S108 // Nested blocks of code should not be left empty
}
}
return(listKeys);
}