public async Task UpdateStatistics()
{
await RunWithDbContextAsync(async _context => {
int total = _context.Groups.Where(g => g.Collection.CollectionId == 1).Count();
int todo = _taskDispacher.CountToDo();
int toreview = _taskDispacher.CountToReview();
int done = await _context
.Groups
.FromSql("SELECT * FROM \"Groups\" AS \"gg\" \n" +
"WHERE\n" +
"(SELECT COUNT(DISTINCT \"FieldTypes\".\"Name\") FROM\n" +
" \"GVSuggestions\"\n" +
" INNER JOIN \"GroupVersions\" ON \"GVSuggestions\".\"GroupVersionForeignKey\" = \"GroupVersions\".\"GroupVersionId\"\n" +
" INNER JOIN \"FieldTypes\" ON \"GVSuggestions\".\"FieldTypeForeignKey\" = \"FieldTypes\".\"FieldTypeId\"\n" +
" WHERE \"GroupVersions\".\"GroupId\" = \"gg\".\"GroupId\"\n" +
" AND \"FieldTypes\".\"Name\" IN('TextBUC', 'TextEnglish', 'TextChinese')\n" +
" AND \"GroupVersions\".\"NextVersionGroupVersionId\" IS NULL\n" +
") >= 3\n" //罗 英 中 全
)
.CountAsync();
int doneBUC = await _context
.Groups
.FromSql("SELECT * FROM \"Groups\" AS \"gg\" \n" +
"WHERE\n" +
"(SELECT COUNT(DISTINCT \"FieldTypes\".\"Name\") FROM\n" +
" \"GVSuggestions\"\n" +
" INNER JOIN \"GroupVersions\" ON \"GVSuggestions\".\"GroupVersionForeignKey\" = \"GroupVersions\".\"GroupVersionId\"\n" +
" INNER JOIN \"FieldTypes\" ON \"GVSuggestions\".\"FieldTypeForeignKey\" = \"FieldTypes\".\"FieldTypeId\"\n" +
" WHERE \"GroupVersions\".\"GroupId\" = \"gg\".\"GroupId\"\n" +
" AND \"FieldTypes\".\"Name\" IN('TextBUC')\n" +
" AND \"GroupVersions\".\"NextVersionGroupVersionId\" IS NULL\n" +
") >= 1\n" //罗
)
.CountAsync();
int doneChinese = await _context
.Groups
.FromSql("SELECT * FROM \"Groups\" AS \"gg\" \n" +
"WHERE\n" +
"(SELECT COUNT(DISTINCT \"FieldTypes\".\"Name\") FROM\n" +
" \"GVSuggestions\"\n" +
" INNER JOIN \"GroupVersions\" ON \"GVSuggestions\".\"GroupVersionForeignKey\" = \"GroupVersions\".\"GroupVersionId\"\n" +
" INNER JOIN \"FieldTypes\" ON \"GVSuggestions\".\"FieldTypeForeignKey\" = \"FieldTypes\".\"FieldTypeId\"\n" +
" WHERE \"GroupVersions\".\"GroupId\" = \"gg\".\"GroupId\"\n" +
" AND \"FieldTypes\".\"Name\" IN('TextChinese')\n" +
" AND \"GroupVersions\".\"NextVersionGroupVersionId\" IS NULL\n" +
") >= 1\n" //中
)
.CountAsync();
int doneEnglish = await _context
.Groups
.FromSql("SELECT * FROM \"Groups\" AS \"gg\" \n" +
"WHERE\n" +
"(SELECT COUNT(DISTINCT \"FieldTypes\".\"Name\") FROM\n" +
" \"GVSuggestions\"\n" +
" INNER JOIN \"GroupVersions\" ON \"GVSuggestions\".\"GroupVersionForeignKey\" = \"GroupVersions\".\"GroupVersionId\"\n" +
" INNER JOIN \"FieldTypes\" ON \"GVSuggestions\".\"FieldTypeForeignKey\" = \"FieldTypes\".\"FieldTypeId\"\n" +
" WHERE \"GroupVersions\".\"GroupId\" = \"gg\".\"GroupId\"\n" +
" AND \"FieldTypes\".\"Name\" IN('TextEnglish')\n" +
" AND \"GroupVersions\".\"NextVersionGroupVersionId\" IS NULL\n" +
") >= 1\n" //英
)
.CountAsync();
int reviewed = done - toreview;
done = done > 0 ? done : 0;
reviewed = reviewed > 0 ? reviewed : 0;
ToDoTotal = total;
Done = done;
DoneBUC = doneBUC;
DoneChinese = doneChinese;
DoneEnglish = doneEnglish;
ReviewTotal = done;
Reviewed = reviewed;
// Top Contributors
((List <Tuple <string, int> >)TopContributors).Clear();
var reader = await RDFacadeExtensions.ExecuteSqlCommandAsync(_context.Database,
"SELECT \"AspNetUsers\".\"Email\", \"AspNetUsers\".\"NickName\",\"cc\".\"Count\" FROM\n" +
"(SELECT COUNT(DISTINCT \"SuggestionId\") AS \"Count\", \"AuthorId\" \n" +
"FROM \"Suggestions\"\n" +
"GROUP BY \"AuthorId\"\n" +
") AS \"cc\" \n" +
"LEFT OUTER JOIN \"AspNetUsers\" ON \"cc\".\"AuthorId\" = \"AspNetUsers\".\"Id\" \n" +
"ORDER BY \"Count\" DESC \n" +
"LIMIT 10 \n"
);
while (reader.DbDataReader.Read())
{
var email = reader.DbDataReader[0].ToString();
var nickname = reader.DbDataReader[1].ToString();
var contrib = reader.DbDataReader[2].ToString();
int contribInt = 0;
Int32.TryParse(contrib, out contribInt);
if (email == "" && nickname == "")
{
((List <Tuple <string, int> >)TopContributors).Add(new Tuple <string, int>("匿名者 (众人)", contribInt));
}
else
{
email = CensorEmail(email);
((List <Tuple <string, int> >)TopContributors).Add(new Tuple <string, int>($"{nickname} ({email})", contribInt));
}
}
reader.Dispose();
});
}