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