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