private void RenderUserStatistic()
{
DateTime fromDate = DateTime.Now.AddYears(-20);
if (RadDateFrom.SelectedDate.HasValue)
{
fromDate = RadDateFrom.SelectedDate.Value;
}
DateTime toDate = DateTime.Now.AddDays(1);
if (RadDateTo.SelectedDate.HasValue)
{
toDate = RadDateTo.SelectedDate.Value;
}
bool allUser = ddlType.SelectedValue == "1";
List <UserStatisticAction> include = new List <UserStatisticAction>();
include.Add(new UserStatisticAction()
{
ObjectType = 3, ActionName = "Viewed", Column = 0
});
include.Add(new UserStatisticAction()
{
ObjectType = 4, ActionName = "Viewed", Column = 1
});
include.Add(new UserStatisticAction()
{
ObjectType = 11, ActionName = "Viewed", Column = 2
});
include.Add(new UserStatisticAction()
{
ObjectType = 15, ActionName = "Viewed", Column = 3
});
include.Add(new UserStatisticAction()
{
ObjectType = 24, ActionName = "Viewed", Column = 4
});
include.Add(new UserStatisticAction()
{
ObjectType = 3, ActionName = "Inserted", Column = 5
});
include.Add(new UserStatisticAction()
{
ObjectType = 4, ActionName = "Inserted", Column = 6
});
include.Add(new UserStatisticAction()
{
ObjectType = 11, ActionName = "Inserted", Column = 7
});
include.Add(new UserStatisticAction()
{
ObjectType = 15, ActionName = "Inserted", Column = 8
});
include.Add(new UserStatisticAction()
{
ObjectType = 24, ActionName = "Inserted", Column = 9
});
include.Add(new UserStatisticAction()
{
ObjectType = 25, ActionName = "Inserted", Column = 10
});
include.Add(new UserStatisticAction()
{
ObjectType = 3, ActionName = "Rated", Column = 11
});
include.Add(new UserStatisticAction()
{
ObjectType = 4, ActionName = "Rated", Column = 12
});
include.Add(new UserStatisticAction()
{
ObjectType = 15, ActionName = "Rated", Column = 13
});
include.Add(new UserStatisticAction()
{
ObjectType = 24, ActionName = "Rated", Column = 14
});
StringBuilder sb = new StringBuilder(1000);
var userStatistic = _4screen.CSB.DataAccess.Business.UserStatistic.GetUserStatisticList(txtUser.Text.Trim(), fromDate, toDate, allUser, include);
sb.Append("User;");
foreach (var column in userStatistic.Columns)
{
string name = Helper.GetObjectName(column.ObjectType, false);
string action = "besucht";
if (column.ActionName == "Inserted")
{
action = "erstellt";
}
else if (column.ActionName == "Rated")
{
action = "bewertet";
}
sb.AppendFormat("{0} {1};", name, action);
}
sb.AppendLine();
foreach (var user in userStatistic.Users)
{
sb.Append(user.Nickname + ";");
for (int i = 0; i < userStatistic.Columns.Count; i++)
{
UserStatisticAction action = user.GetAction(i);
if (action != null)
{
sb.AppendFormat("{0};", action.Amount);
}
else
{
sb.Append("0;");
}
}
sb.AppendLine();
}
Response.ContentType = "application/octet-stream";
Response.AddHeader("content-disposition", "attachment; filename=\"userstatistic.csv\"");
Response.Clear();
Response.Write(sb.ToString());
/*byte[] buffer = System.Text.Encoding.GetEncoding("Windows-1252").GetBytes(sb.ToString());
* Response.OutputStream.Write(buffer, 0, buffer.Length);*/
Response.End();
}
internal static UserStatisticList GetUserStatisticList(string nickName, DateTime fromDate, DateTime toDate, bool allUser, List <UserStatisticAction> includeAction)
{
UserStatisticList userStatisticList = new UserStatisticList();
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT hitbl_DataObject_OBJ.USR_Nickname, ISNULL(hitbl_LogObjectActions_LOA.OBJ_Type, 0) AS OBJ_Type, hitbl_LogObjectActions_LOA.Action, COUNT(hitbl_LogObjectActions_LOA.DateInsert) AS Anzahl");
sb.AppendLine("FROM hitbl_LogObjectActions_LOA RIGHT OUTER JOIN hitbl_LogSession_LSE ON hitbl_LogObjectActions_LOA.LSE_SessionID = hitbl_LogSession_LSE.LSE_SessionID RIGHT OUTER JOIN hitbl_DataObject_OBJ ON hitbl_LogSession_LSE.USR_ID = hitbl_DataObject_OBJ.OBJ_ID");
sb.AppendLine("WHERE (hitbl_LogObjectActions_LOA.DateInsert BETWEEN @fromDate AND @toDate)");
if (!string.IsNullOrEmpty(nickName))
{
sb.AppendLine("AND (hitbl_DataObject_OBJ.USR_Nickname LIKE @Nickname)");
}
sb.AppendLine("GROUP BY hitbl_DataObject_OBJ.USR_Nickname, hitbl_LogObjectActions_LOA.OBJ_Type, hitbl_LogObjectActions_LOA.Action");
if (includeAction.Count > 0)
{
sb.Append("HAVING (");
string or = "";
foreach (UserStatisticAction item in includeAction)
{
if (item.ObjectType > 0)
{
sb.AppendFormat("{0} (hitbl_LogObjectActions_LOA.OBJ_Type = {1} AND hitbl_LogObjectActions_LOA.Action = '{2}')", or, item.ObjectType, item.ActionName);
}
else
{
sb.AppendFormat("{0} (hitbl_LogObjectActions_LOA.Action = '{1}')", or, item.ActionName);
}
or = " OR ";
userStatisticList.Columns.Add(item);
}
sb.AppendLine(")");
}
sb.AppendLine("ORDER BY hitbl_DataObject_OBJ.USR_Nickname");
SqlConnection connection = new SqlConnection(Helper.GetSiemeConnectionString());
try
{
SqlCommand command = new SqlCommand(sb.ToString(), connection);
command.CommandType = CommandType.Text;
command.CommandTimeout = 300; // 5 Minuten
command.Parameters.AddWithValue("@fromDate", fromDate);
command.Parameters.AddWithValue("@toDate", toDate);
if (!string.IsNullOrEmpty(nickName))
{
command.Parameters.AddWithValue("@Nickname", nickName);
}
connection.Open();
SqlDataReader sqlReader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (sqlReader.Read())
{
string userName = sqlReader["USR_Nickname"].ToString();
int objectType = Convert.ToInt32(sqlReader["OBJ_Type"]);
string actionName = sqlReader["Action"].ToString();
int anzahl = Convert.ToInt32(sqlReader["Anzahl"]);
if (!allUser && anzahl == 0)
{
continue;
}
string actionKey = string.Format("{0}{1}", objectType, actionName);
int column = userStatisticList.GetColumn(actionKey);
UserStatisticUser user = userStatisticList.GetUser(userName);
if (user == null)
{
user = new UserStatisticUser();
user.Nickname = userName;
userStatisticList.Users.Add(user);
}
if (anzahl > 0)
{
UserStatisticAction action = user.GetAction(actionKey);
if (action == null)
{
action = new UserStatisticAction();
action.ActionName = actionName;
action.ObjectType = objectType;
action.Action = actionKey;
action.Amount = anzahl;
action.Column = column;
user.Actions.Add(action);
}
}
}
sqlReader.Close();
}
finally
{
connection.Close();
}
return(userStatisticList);
}
