public int GetCountNotifications(
ENotificationStatus status,
NotificationPriorityM priority,
NotificationTypeM type,
string filterText,
int?countInPage = 100,
int?pageNum = null)
{
using (ISQLCommand sqlCmd = _app.SqlWork())
{
sqlCmd.sql = @"SELECT count(*) as cnt FROM notification.view_user_notifications";
sqlCmd.sql += " WHERE 1=1";
string where = GenerateWhereSQL(status, priority, type, filterText);
sqlCmd.sql += where;
sqlCmd.sql += ";";
try
{
return(sqlCmd.ExecuteScalar <int>());
}
catch (Exception ex)
{
throw ex;
}
}
}
private string GenerateWhereSQL(ENotificationStatus status, NotificationPriorityM priority, NotificationTypeM type, string filterText)
{
string result = "";
switch (status)
{
case ENotificationStatus.All:
//command.sql += " AND seen_datetime is null";
break;
case ENotificationStatus.Unreads:
result += " AND seen_datetime is null";
break;
case ENotificationStatus.Favorites:
result += " AND favorite = true";
break;
default:
break;
}
if (priority != null && priority.Gid > 0)
{
result += " AND priority_type_gid = " + priority.Gid.ToString();
}
if (type != null && type.Gid > 0)
{
result += " AND type_gid = " + type.Gid.ToString();
}
if (!string.IsNullOrEmpty(filterText))
{
result += " AND (upper(subject) like '%" + filterText.ToUpper() + "%' OR upper(message) like '%" + filterText.ToUpper() + "%')";
}
return(result);
}
public List <NotificationM> GetNotifications(
ENotificationStatus status,
NotificationPriorityM priority,
NotificationTypeM type,
string filterText,
int?countInPage = 100,
int?pageNum = null)
{
List <NotificationM> outList = new List <NotificationM>();
using (ISQLCommand sqlCmd = _app.SqlWork())
{
sqlCmd.sql = @"SELECT gid, type_gid, type_name, priority_type_gid, priority_name, weight, subject,
message, created, seen_datetime, table_gid, row_gid, alias_text, user_gid, favorite
FROM notification.view_user_notifications";
sqlCmd.sql += " WHERE 1=1";
string where = GenerateWhereSQL(status, priority, type, filterText);
sqlCmd.sql += where;
sqlCmd.sql += " ORDER BY created DESC";
if (pageNum != null)
{
int begin = (pageNum.Value - 1) * 100;
countInPage = countInPage.HasValue ? countInPage.Value : 100;
sqlCmd.sql += string.Format(" LIMIT {0} OFFSET {1};", countInPage, begin);
}
try
{
sqlCmd.ExecuteReader();
while (sqlCmd.CanRead())
{
outList.Add(new NotificationM(
sqlCmd.GetValue <int>("gid"),
sqlCmd.GetValue <int>("type_gid"),
sqlCmd.GetValue <int>("priority_type_gid"),
sqlCmd.GetValue <int>("weight"),
sqlCmd.GetValue <string>("subject"),
sqlCmd.GetValue <string>("message"),
sqlCmd.GetValue <DateTime?>("created"),
sqlCmd.GetValue <DateTime?>("seen_datetime"),
sqlCmd.GetValue <int>("user_gid"),
sqlCmd.GetValue <bool>("favorite"),
new RefObjM()
{
IdObj = sqlCmd.GetValue <int?>("row_gid"), IdTable = sqlCmd.GetValue <int?>("table_gid"), Name = sqlCmd.GetValue <string>("alias_text")
}
));
}
}
catch (Exception ex)
{
throw ex;
}
}
return(outList);
}
public void SetReadedAllNotification(ENotificationStatus status,
NotificationPriorityM priority,
NotificationTypeM type,
string filterText)
{
using (var sqlCmd = PluginInitialization._app.SqlWork())
{
sqlCmd.sql = @"UPDATE notification.notification_users
SET seen_datetime = now()
FROM notification.notifications n
WHERE notification_users.user_gid = " + PluginInitialization._app.user_info.id_user.ToString() +
" AND notification_users.seen_datetime IS NULL AND n.gid = notification_users.notification_gid "
+ GenerateWhereSQL(status, priority, type, filterText);
sqlCmd.ExecuteNonQuery();
}
}
