public HttpResponseMessage GetUsersReport([FromBody] RequestUserAudit request) { //var result = GetUserAudit(request.UserId, request.StartDate, request.StartDate); var userVersion = GetUserHistoryVersions(request); var result = new { data = userVersion, //TotalCount = totalSearchCount }; HttpResponseMessage message = Request.CreateResponse(HttpStatusCode.OK, result); return(message); }
private DataTable GetUserHistoryVersions(RequestUserAudit request) { try { DataTable dt = new DataTable(); var query = @"select 'Edit' as Action,concat(uh.firstname , ' ', uh.lastname) as [User Name],uh.username as [User Id], r.RoleName as [User Role], uc.clients as Client, concat(mu.firstname , ' ', mu.lastname) as [Done By], FORMAT(uh.modifieddate,'yyyy-MM-dd HH:MM:ss') as [Date Time] from user_history uh inner join UserRole_history urh on urh.userversion = uh.version and urh.userid = uh.userid inner join Role r on r.roleid = urh.roleid inner join dbo.[user] mu on uh.modifiedUserId = mu.userid inner join ( Select Main.userid,main.userversion, Left(Main.userclient,Len(Main.userclient)-1) As Clients From ( Select distinct ST2.userid, ST2.userVersion, ( Select CONVERT(varchar(50), Rtrim(c.name)) + ', ' AS [text()] From dbo.userclient_history ST1 inner join clients C on ST1.clientid = c.id Where ST1.userid = ST2.userid and ST1.userVersion = ST2.userVersion ORDER BY ST1.userid For XML PATH ('') ) [userclient] From dbo.userclient_history ST2 ) [Main]) uc on uc.userid = urh.userid and uc.userversion = urh.userversion "; var whereQueryString = string.Format(" where uh.userId = '{0}' and ", request.UserId); //if (!string.IsNullOrEmpty(request.UserName)) //{ // whereQueryString += string.Format(" [User Id] = '{0}' and ", request.UserName); //} if (!string.IsNullOrEmpty(Convert.ToString(request.StartDate))) { whereQueryString += string.Format(" uh.modifieddate >= '{0}' and ", Convert.ToDateTime(request.StartDate).ToString("yyyy-MM-dd h:mm tt")); } if (!string.IsNullOrEmpty(Convert.ToString(request.EndDate))) { whereQueryString += string.Format(" uh.modifieddate <= '{0}' and ", Convert.ToDateTime(request.EndDate).AddDays(1).Date.AddSeconds(-1).ToString("yyyy-MM-dd h:mm tt")); } query += whereQueryString; query = query.Substring(0, query.Length - 4); using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand(query, conn)) { cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 300; SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); } } return(dt); } catch (Exception ex) { throw ex; } }