private static string GetFilterString(NewsStatus newsStatus, int?prodStatusId)
{
var filterString = string.Format("WHERE NewsStatus = {0}", newsStatus.NewsStatusId);
// Geting available production statuses for worker
if (newsStatus.NewsStatusId == 6 || newsStatus.NewsStatusId == 7)
{
if (prodStatusId.HasValue)
{
filterString += string.Format(" AND ProdStatusID = {0}", prodStatusId.Value);
}
// Return all messages if user is administrator
else if (!AdministrationClass.IsAdministrator)
{
//var workerGroups = GetWorkerGroupsIds(AdministrationClass.CurrentWorkerId);
//if (workerGroups.All(g => g != 1))
//{
var workerProdStatuses = GetWorkerProdStatuses(AdministrationClass.CurrentWorkerId);
if (workerProdStatuses.Any())
{
var workerProdStatusesString = workerProdStatuses.Aggregate(string.Empty, (current, prodStatus) => current + ", " + prodStatus).Remove(0, 2);
filterString += " AND (ProdStatusID IN (" + workerProdStatusesString + ") OR ProdStatusID IS NULL)";
}
else
{
filterString += " AND ProdStatusID IS NULL";
}
//}
}
}
// Worker requests
if (newsStatus.NewsStatusId == 9 && !AdministrationClass.IsAdministrator)
{
var fullAccess = AdministrationClass.HasFullAccess(AdministrationClass.Modules.WorkerRequests);
if (!fullAccess)
{
// Get personal worker requests
var workerRequestsGlobalIdsString = GetPersonalWorkerRequestsGlobalIds();
filterString += " AND GlobalID IN (" + workerRequestsGlobalIdsString + ")";
}
}
// Worker tasks
if (newsStatus.NewsStatusId == 11 && !AdministrationClass.IsAdministrator)
{
// Get GlobalIds of personal messages
var globalIdsString = GetPersonalTasksGlobalIds();
filterString += " AND GlobalID IN (" + globalIdsString + ")";
}
return(filterString);
}
public void Fill(int indexFrom, int limiCount, NewsStatus newsStatus, int?prodStatusId)
{
FillNews(indexFrom, limiCount, newsStatus, prodStatusId);
var newsIdsArray = GetNewsIds();
FillComments(newsIdsArray);
var commentsIdsArray = GetCommentsIds();
FillAttachments(newsIdsArray);
FillCommentsAttachments(commentsIdsArray);
}
private void FillNews(int indexFrom, int limitCount, NewsStatus newsStatus, int?prodStatusId)
{
var filter = GetFilterString(newsStatus, prodStatusId);
var sqlCommandText =
@"SELECT NewsID, NewsDate, NewsText, NewsStatus, WorkerID, GlobalID, LastEditing, ProdStatusID
FROM FAIINewsFeed.NewsFeed " + filter +
" ORDER BY LastEditing DESC, NewsDate DESC limit " + indexFrom + "," + limitCount;
_newsAdapter = new MySqlDataAdapter(sqlCommandText, _connectionString);
new MySqlCommandBuilder(_newsAdapter);
try
{
_news = new DataTable();
_newsAdapter.Fill(_news);
}
catch (MySqlException)
{
}
}
private void FillNewsStatuses()
{
_newsStatuses.Clear();
const string sqlCommandText = @"SELECT NewsStatusID, NewsStatusName, NewsStatusColor, NewsStatusGroupID
FROM FAIINewsFeed.NewsStatus";
using (var sqlConn = new MySqlConnection(_connectionString))
{
var sqlCommand = new MySqlCommand(sqlCommandText, sqlConn);
try
{
sqlConn.Open();
using (var sqlReader = sqlCommand.ExecuteReader())
{
while (sqlReader.Read())
{
int newsStatusId;
Int32.TryParse(sqlReader["NewsStatusID"].ToString(), out newsStatusId);
var newsStatusName = sqlReader["NewsStatusName"].ToString();
var newsStatusColor = sqlReader["NewsStatusColor"].ToString();
int newsStatusGroupId;
Int32.TryParse(sqlReader["NewsStatusGroupID"].ToString(), out newsStatusGroupId);
var newsStatus = new NewsStatus(newsStatusId, newsStatusName, newsStatusColor,
newsStatusGroupId);
_newsStatuses.Add(newsStatus);
}
}
}
catch (MySqlException)
{
}
finally
{
sqlConn.Close();
sqlCommand.Dispose();
}
}
}
public int GetNewsCount(NewsStatus newsStatus, int?prodStatusId)
{
var rowsCount = 0;
var filter = GetFilterString(newsStatus, prodStatusId);
var sql = "SELECT COUNT(*) FROM FAIINewsFeed.NewsFeed " + filter;
using (var conn = new MySqlConnection(_connectionString))
{
var cmd = new MySqlCommand(sql, conn);
try
{
conn.Open();
rowsCount = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
catch (MySqlException)
{
}
}
return(rowsCount);
}
//public static IEnumerable<int> GetWorkerGroupsIds(int workerId)
//{
// var workerGroups = new List<int>();
// const string sqlCommandText = @"SELECT DISTINCT WorkerGroupID FROM FAIIStaff.WorkerProfessions
// WHERE WorkerID = @WorkerID";
// using (var sqlConn = new MySqlConnection(App.ConnectionInfo.ConnectionString))
// {
// var sqlCommand = new MySqlCommand(sqlCommandText, sqlConn);
// sqlCommand.Parameters.Add("@WorkerID", MySqlDbType.Int64).Value = workerId;
// try
// {
// sqlConn.Open();
// using (var sqlReader = sqlCommand.ExecuteReader())
// {
// while (sqlReader.Read())
// {
// if (sqlReader.FieldCount != 0 && sqlReader[0] != DBNull.Value)
// {
// var workerGroupId = Convert.ToInt32(sqlReader[0]);
// workerGroups.Add(workerGroupId);
// }
// }
// }
// }
// catch { }
// finally
// {
// sqlConn.Close();
// }
// }
// return workerGroups;
//}
public static int GetNewMessagesCount(NewsStatus newsStatus, int workerId, DateTime lastUpdate)
{
var rowsCount = 0;
var filter = GetFilterString(newsStatus, null);
var latestNewsSqlCommandText = @"SELECT NewsID, WorkerID, NewsDate FROM FAIINewsFeed.NewsFeed as news "
+ filter + " AND news.LastEditing >= @LastUpdate";
var latestNewsTable = new DataTable();
using (var conn = new MySqlConnection(App.ConnectionInfo.ConnectionString))
{
var sqlCommand = new MySqlCommand(latestNewsSqlCommandText, conn);
sqlCommand.Parameters.Add("@LastUpdate", MySqlDbType.DateTime).Value = lastUpdate;
var adapter = new MySqlDataAdapter(sqlCommand);
try
{
adapter.Fill(latestNewsTable);
}
catch (MySqlException)
{
}
finally
{
adapter.Dispose();
sqlCommand.Dispose();
}
}
if (latestNewsTable.Rows.Count == 0)
{
return(0);
}
var latestNewsIds = from newsRow in latestNewsTable.AsEnumerable()
select Convert.ToInt32(newsRow["NewsID"]);
if (!latestNewsIds.Any())
{
return(0);
}
var newsIdsString = string.Empty;
newsIdsString = (latestNewsIds.Cast <object>()
.Aggregate(newsIdsString, (current, newsId) => current + ", " + newsId))
.Remove(0, 2);
var latestCommentsSqlCommandText = @"SELECT COUNT(*) FROM FAIINewsFeed.NewsComments
WHERE NewsID IN (" + newsIdsString + ") " +
"AND CommentDate >= @LastUpdate AND WorkerID != @WorkerID";
using (var conn = new MySqlConnection(App.ConnectionInfo.ConnectionString))
{
var sqlCommand = new MySqlCommand(latestCommentsSqlCommandText, conn);
sqlCommand.Parameters.Add("@LastUpdate", MySqlDbType.DateTime).Value = lastUpdate;
sqlCommand.Parameters.Add("@WorkerID", MySqlDbType.Int64).Value = workerId;
try
{
conn.Open();
rowsCount = Convert.ToInt32(sqlCommand.ExecuteScalar());
}
catch (MySqlException)
{
}
finally
{
conn.Close();
sqlCommand.Dispose();
}
}
var latestNewsCount =
latestNewsTable.AsEnumerable()
.Count(n => n.Field <Int64>("WorkerID") != workerId &&
n.Field <DateTime>("NewsDate") > lastUpdate);
rowsCount += latestNewsCount;
return(rowsCount);
}
