public HttpResponseMessage Search([FromUri] TicketingSearchParamsDto @params)
{
var emp = _employeeService.GetEmpByLoginName(RequestContext.Principal.Identity.Name);
@params.CurrLoginEmpId = emp.id;
var queryResult = ticketService.GetReportResult(@params);
return(Request.CreateResponse(HttpStatusCode.OK, queryResult));
}
public object GetReportResult(TicketingSearchParamsDto dto)
{
return(ticketingRepo.GetReportResult(dto));
}
public object GetReportResult(TicketingSearchParamsDto param)
{
var totalRecords = new SqlParameter("TotalRecords", System.Data.SqlDbType.Int);
totalRecords.Direction = System.Data.ParameterDirection.Output;
string sql = @"EXEC TICKET.[REPORT] @ticketNo = @ticketNo,
@subject = @subject,
@sourceId = @sourceId,
@teamId = @teamId,
@itemId = @itemId,
@statusId = @statusId,
@subCateId = @subCateId,
@typeId = @typeId,
@cateId = @cateId,
@assignedId = @assignedId,
@start = @start,
@limit = @limit,
@page = @page,
@priority = @priority,
@deptIds = @deptIds,
@completedDateFrom = @completedDateFrom,
@completedDateTo = @completedDateTo,
@dueDateFrom = @dueDateFrom,
@dueDateTo = @dueDateTo,
@requestorId = @requestorId,
@submittedDateFrom = @submittedDateFrom,
@submittedDateTo = @submittedDateTo,
@CurrLoginEmpId = @CurrLoginEmpId,
@SlaId = @SlaId,
@IncludeRemoved = @IncludeRemoved,
@Description = @Description,
@Comment = @Comment,
@RootCauseId = @RootCauseId,
@totalRecords = @totalRecords OUT";
var sqlParams = new List <SqlParameter>()
{
new SqlParameter("@ticketNo", GetSQLParamByString(param.ticketNo)),
new SqlParameter("@subject", GetSQLParamByString(param.subject)),
new SqlParameter("@sourceId", GetSQLParamByInt(param.SourceId)),
new SqlParameter("@priority", GetSQLParamByList(param.Priority)),
new SqlParameter("@itemId", GetSQLParamByList(param.ItemId)),
new SqlParameter("@statusId", GetSQLParamByList(param.StatusId)),
new SqlParameter("@subCateId", GetSQLParamByList(param.SubCateId)),
new SqlParameter("@typeId", GetSQLParamByList(param.TypeId)),
new SqlParameter("@cateId", GetSQLParamByList(param.CateId)),
new SqlParameter("@teamId", GetSQLParamByList(param.TeamId)),
new SqlParameter("@assignedId", GetSQLParamByList(param.assignedId)),
new SqlParameter("@start", GetSQLParamByInt(param.Start)),
new SqlParameter("@limit", GetSQLParamByInt(param.Limit)),
new SqlParameter("@page", GetSQLParamByInt(param.Page)),
new SqlParameter("@deptIds", GetSQLParamByString(param.Depts)),
new SqlParameter("@requestorId", GetSQLParamByInt(param.RequestorId)),
new SqlParameter("@completedDateFrom", GetSQLParamByDateTime(param.CompletedDateFrom)),
new SqlParameter("@completedDateTo", GetSQLParamByDateTime(param.CompletedDateTo)),
new SqlParameter("@dueDateFrom", GetSQLParamByDateTime(param.DueDateFrom)),
new SqlParameter("@dueDateTo", GetSQLParamByDateTime(param.DueDateTo)),
new SqlParameter("@CurrLoginEmpId", GetSQLParamByInt(param.CurrLoginEmpId)),
new SqlParameter("@submittedDateFrom", GetSQLParamByDateTime(param.SubmittedDateFrom)),
new SqlParameter("@submittedDateTo", GetSQLParamByDateTime(param.SubmittedDateTo)),
new SqlParameter("@SlaId", GetSQLParamByList(param.SlaId)),
new SqlParameter("@IncludeRemoved", GetSQLParamByBool(param.IncludeRemoved)),
new SqlParameter("@Description", GetSQLParamByString(param.description)),
new SqlParameter("@Comment", GetSQLParamByString(param.comment)),
new SqlParameter("@RootCauseId", GetSQLParamByList(param.RootCauseId))
};
sqlParams.Add(totalRecords);
var queryResult = SqlQuery <TicketingDto>(sql, sqlParams.ToArray());
return(new { totalRecords = totalRecords.Value, records = queryResult });
}
