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(); } }